Search for:
Feb 8 2021
HRHD – Query to find Worker Resource Details
select hp.party_number
,hp.party_name
,hp.party_type
,hp.ORIG_SYSTEM_REFERENCE
,jrrp.PERSON_NAME
,jrrp.PERSON_FIRST_NAME
,hp.PERSON_FIRST_NAME hp_PERSON_FIRST_NAME
,jrrp.PERSON_LAST_NAME
,hp.PERSON_LAST_NAME hp_PERSON_LAST_NAME
,jrrp.PERSON_TITLE
,hp.PERSON_TITLE hp_PERSON_TITLE
,jrrp.EMAIL_ADDRESS
,hp.EMAIL_ADDRESS hp_EMAIL_ADDRESS
,jrrp.PRIMARY_PHONE_NUMBER
,hp.PRIMARY_PHONE_NUMBER hp_PRIMARY_PHONE_NUMBER
,jrrp.PARTY_ID
,jrrp.START_DATE_ACTIVE
,jrrp.END_DATE_ACTIVE
,jrrr.ROLE_RESOURCE_TYPE
,jrrb.ROLE_ID
,jrrb.ROLE_CODE
,jrrb.ROLE_TYPE_CODE
from HZ_PARTIES hp
,JTF_RS_ROLE_RELATIONS jrrr
,JTF_RS_RESOURCE_PROFILES jrrp
,JTF_RS_ROLES_B jrrb
where jrrr.ROLE_RESOURCE_ID = hp.party_id
and jrrr.role_id = jrrb.role_id
and jrrp.party_id = hp.party_id
Mandeep Gupta 0 Comments
Feb 5 2021
BIP Reports – Query to get pending absence transactions
select distinct module_identifier "ProcessName"
, p.person_number
, n.full_name
, subject
, STATUS
, h.INITIATOR_USER_ID "SubmittedBy" 
, d.submitted_date
, ppnf_sup.full_name "assignees"
, ps.person_number "ApproverPersonNumber"
, ppnf_sup.full_name "PendingApproverName", h.transaction_id
, module_identifier
, object
, object_id
from
hrc_txn_header h, 
hrc_txn_data d,
anc_per_abs_entries abs,
PER_PERSON_SECURED_LIST_V p, 
per_person_names_f n,
PER_ASSIGNMENT_SECURED_LIST_V a,
per_assignment_supervisors_f AssignmentSupervisor,
per_person_names_f_v ppnf_sup,
per_all_people_f ps
where 
h.transaction_id = d.transaction_id  
and object_id= abs.per_absence_entry_id
and abs.person_id = p.person_id
and n.person_id = p.person_id
and a.person_id = p.person_id
and abs.approval_status_cd = 'AWAITING'
and a.assignment_type ='E'
and n.name_type = 'GLOBAL'
and ppnf_sup.name_type = 'GLOBAL'
and a.assignment_status_type = 'ACTIVE'
and ps.person_id = ppnf_sup.person_id
and AssignmentSupervisor.assignment_id = a.assignment_id
and AssignmentSupervisor.manager_id = ppnf_sup.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and sysdate between n.effective_start_date and n.effective_end_date
and sysdate between a.effective_start_date and a.effective_end_date
and sysdate between ps.effective_start_date and ps.effective_end_date
and sysdate between AssignmentSupervisor.effective_start_date and AssignmentSupervisor.effective_end_date
and sysdate between ppnf_sup.effective_start_date and ppnf_sup.effective_end_date
Mandeep Gupta 0 Comments
Feb 5 2021
HRHD – Query to get Service Request Details

Use below query to get service request details from Fusion BIP:

SELECT ssr.sr_id,
ssr.title,
ssr.problem_desc,
to_char(ssr.open_date,’RRRR/MM/DD’) sr_opening_date,
ssr.primary_contact_party_id,
ssr.sr_number,
ssr.status_cd,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.status_cd
AND flv.lookup_type = ‘ORA_SVC_SR_STATUS_CD’
AND flv.LANGUAGE = ‘US’) sr_status_meaning,
ssr.severity_cd,
(SELECT meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.severity_cd
AND flv.lookup_type = ‘ORA_SVC_SR_SEVERITY_CD’
AND flv.LANGUAGE = ‘US’) sr_severity_meaning,
sc.category_name category,
sq.queue_name queue,
to_char(ssr.last_resource_assign_date,’RRRR/MM/DD’) sr_last_resource_assigned_on
FROM svc_service_requests ssr,
svc_queues sq,
svc_categories sc
WHERE ssr.queue_id = sq.queue_id
AND ssr.category_id = sc.category_id
–AND ssr.status_cd in (‘ORA_SVC_NEW’,’ORA_SVC_INPROGRESS’)

Mandeep Gupta 0 Comments
Feb 5 2021
Reports (BIP) – Query to extract Person EIT Data
select papf.person_number ,
ppeif.PEI_INFORMATION1,
ppeif.PEI_INFORMATION2,
ppeif.PEI_INFORMATION3,
to_char(ppeif.PEI_INFORMATION_DATE1,'yyyy/mm/dd')date_from,
to_char(ppeif.PEI_INFORMATION_DATE2,'yyyy/mm/dd')date_to
from per_all_people_f papf,
PER_PEOPLE_EXTRA_INFO_F_V ppeif
where 1=1
and papf.person_id = ppeif.person_id
AND ppeif.INFORMATION_TYPE = 'XYZ Test Results'
AND ppeif.PEI_INFORMATION_CATEGORY = 'XYZ Test Results'
Mandeep Gupta 0 Comments
Feb 4 2021
Reports (BIP) – Query to get Adjustments in Absences

select papf.person_number,
ppnf.full_name,
absence_plan.name ,
apaed1.value,
apaed1.type,
to_char(apaed1.procd_date,’DD-MON-YYYY’) processed_date,
apaed1.created_by
from anc_per_acrl_entry_dtls apaed1,
ANC_PER_PLAN_ENROLLMENT apaed,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT aapf.absence_plan_id, aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = ‘A’ — added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = ‘US’
) absence_plan
where apaed.plan_id = absence_plan.absence_plan_id
and apaed.plan_id = apaed1.pl_id
AND ppnf.name_type = ‘GLOBAL’
AND ppnf.person_id = apaed.person_id
AND ppnf.person_id = apaed1.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
AND apaed.person_id = papf.person_id
AND apaed1.person_id = papf.person_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date
–AND UPPER(absence_plan.name) NOT LIkE ‘%SICK%’
AND apaed1.type = ‘ADJOTH’
–and apaed1.created_by not like ‘FUSION_APPS_HCM_ESS_LOADER_APPID’
ORDER BY papf.person_number,absence_plan.name

Mandeep Gupta 0 Comments
Feb 3 2021
Reports (BIP) – Query to get Legislative Data group associated with Legal Entity

select ple.name legal_employer
, pldgt.name legislative_data_group
from per_legislative_data_groups pldg
,per_legislative_data_groups_tl pldgt
,Hr_Organization_Information_F Psu_Inf
,per_legal_employers ple
where 1=1
and pldg.legislative_data_group_id = pldgt.legislative_data_group_id
and pldgt.language = ‘US’
and psu_inf.Org_Information_Context = ‘PER_PSU_PAYROLL_INFO’
and psu_inf.Org_Information1 = pldgt.legislative_data_group_id
and Psu_Inf.Organization_Id = ple.Organization_Id
and TRUNC(sysdate) BETWEEN ple.effective_start_date AND ple.effective_end_date

Mandeep Gupta 0 Comments
Jan 29 2021
Reports (BIP) – Organization classifications in Fusion

Fusion supports multiple organization types like Legal Employer, Business Units, Division, Departments etc. Below are the main classification codes used for organizations in Fusion:

Division – HCM_DIVISION

Department – DEPARTMENT

Legal Employer – HCM_LEMP

Business Unit – FUN_BUSINESS_UNIT

All the available classifications can be found in below table:

select classification_code
,classification_name
,description
from hr_org_classifications_tl hoct
where language = 'US'

Example :- Query to get divisions list:

select haou.name,
houcf.classification_code,
houcf.status
from hr_all_organization_units haou,
hr_org_unit_classifications_f houcf
where haou.ORGANIZATION_ID = houcf.ORGANIZATION_ID
--and houcf.STATUS ='A'
and houcf.CLASSIFICATION_CODE ='HCM_DIVISION'
and trunc(sysdate)between houcf.effective_start_date and houcf.effective_end_date
order by Name
Mandeep Gupta 0 Comments
Jan 7 2021
Reports (BIP) – Query to get UCM Content ID of an absence attachment

Use below query to get absence attachment id of an absence from UCM:

SELECT papf.person_number
,type.name absence_type
,fdt.dm_document_id ucm_content_id
,fdt.file_name
,to_char(apae.start_date,'DD-Mon-RRRR', 'nls_date_language=American') absence_start_date
,to_char(apae.end_date,'DD-Mon-RRRR', 'nls_date_language=American') absence_end_date
,apae.source
FROM fnd_attached_documents fad
,fnd_documents_tl fdt
,anc_per_abs_entries apae
,anc_absence_types_vl type
,per_all_people_f papf
WHERE 1 = 1
AND fad.entity_name ='ANC_ATTACHMENT'
AND fad.pk1_value = apae.per_absence_entry_id
AND fdt.document_id = fad.document_id
AND fdt.language = userenv('LANG')
AND apae.absence_type_id = type.absence_type_id
--AND type.name = 'Sick Leave'
AND papf.person_id = apae.person_id
-- AND papf.person_number in (:PERSON_NUMBER)
AND trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
Mandeep Gupta 0 Comments
Jan 6 2021
Reports (BIP) – Query to extract Worker disability details

Below query can be used to extract disability details of a worker:

SELECT papf.person_id
,papf.person_number
,ppnf.full_name
,to_char(pdf.effective_start_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_st_dt
,to_char(pdf.effective_end_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_end_dt
,pdf.legislation_code
,pdf.disability_code
,(select pou.name from per_organization_units pou
where pou.organization_id = pdf.organization_id) disability_org_name
,to_char(pdf.registration_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_date
,to_char(pdf.registration_exp_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_exp_date
,to_char(pdf.assessment_due_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_assessment_due_date
,pdf.category
,pdf.description
,pdf.degree
,pdf.quota_fte
,pdf.reason
FROM PER_DISABILITIES_F pdf
,PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
WHERE papf.person_id = pdf.person_id
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
Mandeep Gupta 0 Comments
Jan 6 2021
Reports (BIP) – Query to get assigned payroll details

Use the below query to extract assigned payroll details from Fusion HCM:

SELECT papf.person_id
	  ,papf.person_number
	  ,ppnf.full_name
	  ,papd.payroll_id
	  ,papf_pay.payroll_name
	  ,prrd.payroll_relationship_number
	  ,to_char(prrd.start_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_start_date
	  ,to_char(prrd.end_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_end_date
	  ,to_char(papd.FSED,'DD-Mon-RRRR', 'nls_date_language=American') fsed
	  ,to_char(papd.FINC,'DD-Mon-RRRR', 'nls_date_language=American') finc
	  ,to_char(papd.LSPD,'DD-Mon-RRRR', 'nls_date_language=American') lspd
	  ,to_char(papd.LSED,'DD-Mon-RRRR', 'nls_date_language=American') lsed
	  ,paam.assignment_number
	  ,prgf.time_card_req
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND papf.person_number = '11111'
 ORDER BY 2,3
Mandeep Gupta 0 Comments
Jan 1 2021
Reports (BIP) – Implementing Digital Signature in PDF BIP reports

Oracle Fusion reports and analytics provides the capability of implementing digital signatures with in BIP. However there are some limitations:

  • Only one digital signature can be used across all reports.
  • For a report with multiple templates, the same signature will be applicable for all the templates.
  • Digital signature can be tested only using the BIP scheduler.

Follow the below steps to implement digital signatures:

  1. Get your pfx file (digital signature file). Fusion currently supports pfx and p12 extensions only.
  2. Login to reports and analytics using below url:

https://xxyxxmz.ea2.oraclecloud.com/xmlpserver

3. Navigate to Administration -> System Maintenance -> Upload Center

4. Choose the signature file under Upload File and choose the File Type as ‘Digital Signature’ and click on ‘Upload’ button:

5. Click on Return and Navigate to Security Center -> Digital Signature

Choose the *Digital ID File and provide the value * Password and choose the roles required and click on Apply button:

6. Go to the report properties -> Formatting and choose the value ‘True’ for Enable Digital Signature attribute. Choose the other values as required:

7. Click ‘Ok’ and save the changes.

8. Schedule the report to test the digital signature:

9. Open the received output and verify the digital signature:

Mandeep Gupta 0 Comments
Dec 31 2020
Reports (BIP) – Customizing a standard BIP report

Many a times, consultants are required to modify the standard BIP reports. This is very much applicable in case of workflow notifications which are based on BIP reports. Both data model as well report can be customized to include/exclude any additional fields.

Follow the below steps:

  1. Login to your pod using below url:

https://xxzzaa.fa.bb.oraclecloud.com/xmlpserver/

(note the xmlpserver in the end).

2. Then navigate to /Shared Folders/Human Capital Management/Folder

3. Click on More and you will see Customize option. Click on Customize:

This will create a new report under:

/Shared Folders/Custom/Human Capital Management/Folder (notice the change in path).

You can upload your customized templates here.

Click on edit and choose the data model from standard path.

Similar approach will be followed for data model customizations as well.

In order to display the changes asap, please configure the below profile option for global scope:

BIP_CLIENT_REFRESH_TIME – Oracle Middleware Extensions for Applications – Application Core

Provide a value of 15 min.

Check below document for more details:

https://docs.oracle.com/en/cloud/saas/applications-common/20b/facia/approval-management.html#FACIA3819777

Mandeep Gupta 0 Comments
Dec 31 2020
Reports (BIP) – Tips/ Shortcuts
  • To display the date in DD-Mon-RRRR format, please use below in your SQL query:

to_char(date_of_birth,’DD-Mon-RRRR’, ‘nls_date_language=American’)

  • Use below condition to get current user id:

fnd_global.USER_GUID

AND PP.PERSON_ID = PU.PERSON_ID
AND PU.USER_GUID = FND_GLOBAL.USER_GUID

— Use below function to convert the amount in Arabic:

<?xdoxslt:toCheckNumber(‘ar-SA’, TOTAL, ‘AED’,’CASE_UPPER’)?>

— Using LISTAGG to show multiple rows into a single cell:

select listagg(papf.person_number,';') within group (order by person_number) from per_all_people_f papf
where rownum <=5

— Getting Person Id of logged in Person:

HRC_SESSION_UTIL.GET_USER_PERSONID

— Get UDT value in BIP:

ff_user_tables_pkg.get_table_value
Mandeep Gupta 0 Comments