Search for:
May 24 2021
BIP – Query to get Worker Career Statement
SELECT papf.person_number, ITEM_TEXT2000_1 STATEMENT, ITEM_CLOB_1
FROM HRT_PROFILE_ITEMS ProfileItemPEO,
HRT_PROFILES_VL ProfilePEO,
HRT_PROFILE_TYP_SECTIONS ProfileTypeSectionPEO,
PER_ALL_PEOPLE_F papf
WHERE ProfilePEO.PROFILE_ID= ProfileItemPEO.PROFILE_ID
AND ProfileTypeSectionPEO.SECTION_CONTEXT = 'PERSON_CAREER_STATEMENT'
AND ProfileTypeSectionPEO.BUSINESS_GROUP_ID=ProfilePEO.BUSINESS_GROUP_ID
AND ProfileItemPEO.CONTENT_TYPE_ID=ProfileTypeSectionPEO.CONTENT_TYPE_ID
AND papf.person_id = ProfilePEO.person_id
AND ITEM_TEXT2000_1 IS NOT NULL
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
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 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