Search for:
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 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
Jan 14 2021
REST API – Sample Payloads (Part – 1)

Sample payload for adding DFF attribute value for absence entries:

  1. Sample Rest API Payload for adding dff value

url will be same :

https://abc_test.oraclecloud.com/hcmRestApi/resources/11.13.18.02/absences/

{

    “personNumber”: “999”,

    “employer”: “ABC Test Employer“,

    “absenceType”: “Sick Leave”,

    “startDateDuration”: “1”,

    “startDate”: “2019-03-28”,

    “startTime”: “08:00”,

    “endDate”: “2019-03-28”,

    “endTime”: “17:00”,

    “absenceStatusCd”: “SUBMITTED”,

    “diseaseCode”:”https://google.com”,

    “absenceRecordingDFF”: [

        {

            “__FLEX_Context”: null,

            “preapprovaltaken”: “N”

        }

    ]

}

In above example dff preapprovaltaken is enabled at global level and not at any context.

Below is another example where context is enabled

{

                “personNumber”: “999”,

                “employer”: ” ABC Test Employer “,

                “absenceType”: “Maternity Leave”,

                “startDateDuration”:”1″,

                “startDate”: “2019-10-08”,

                “startTime”: “08:00”,

                “endDate”: “2019-10-08”,

                “endTime”: “17:00”,

                “absenceStatusCd”: “SUBMITTED”,

                “absenceReason”:”ML – For Adoption”,

                “absenceRecordingDFF”: [{

                                                “administratorComments” : null,

                                               “__FLEX_Context” : “300000089749943”,

                                              “dateOfAdoption” : “2019-10-22”

                                               }

                                         ]

}

This context value can be retrieved from below query:

SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
FROM fnd_descr_flex_contexts_vl
WHERE UPPER(DESCRIPTIVE_FLEXFIELD_NAME) LIKE ‘ANC_PER_ABS_ENTRIES_DFF’

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