Query For Pending Salary Transactions

 Query to List Pending Salary Transactions



Below is the Query which helps in listing down the Pending Salary Transactions initiated in the Oracle Cloud HCM System. It gives all the Person, Assignment and Salary changes as Old and New along with Transaction details for a given Date range.    

          SQL: 

WITH perassg as

 ( select papf.person_id, 

   papf.person_number, 

   paam.assignment_id, 

   ppnf.full_name,        

   paam.business_unit_id,

   paam.legal_entity_id,

   paam.organization_id,

   Department.name Department,

   Business_unit.name Business_unit,

   Legal_employer.name Legal_employer,

   pj.name job,

   paam.assignment_number,

   paam.assignment_status_type emp_assgn_sts,

   bpf.manager_id,bpfn.full_name mgr_name,

   lmn.full_name lm_mgr_name

   from   

  (select hauft.organization_id,haouf.attribute1, -- segment dff addition    

      hauft.name

   from hr_org_unit_classifications_f houcf, 

    hr_all_organization_units_f haouf, 

    hr_organization_units_f_tl hauft 

  where haouf.organization_id = houcf.organization_id 

    and haouf.organization_id = hauft.organization_id 

    and haouf.effective_start_date between houcf.effective_start_date and houcf.effective_end_date 

    and hauft.language = 'US'

    and hauft.effective_start_date = haouf.effective_start_date 

    and hauft.effective_end_date = haouf.effective_end_date 

    and houcf.classification_code = 'DEPARTMENT' 

    and :p_end_date between hauft.effective_start_date and hauft.effective_end_date

    ) Department,


  (select hauft.organization_id,

    hauft.name,

    houcf.classification_code

   from hr_org_unit_classifications_f houcf, 

    hr_all_organization_units_f haouf, 

    hr_organization_units_f_tl hauft 

  where haouf.organization_id = houcf.organization_id 

    and haouf.organization_id = hauft.organization_id 

    and haouf.effective_start_date between houcf.effective_start_date and houcf.effective_end_date 

    and hauft.language = 'US'

    and hauft.effective_start_date = haouf.effective_start_date 

    and hauft.effective_end_date = haouf.effective_end_date 

    and houcf.classification_code = 'HCM_LEMP' 

    and :p_end_date between hauft.effective_start_date and hauft.effective_end_date

 ) Legal_employer,

  (select hauft.organization_id ,

    hauft.name,

    houcf.classification_code

   from hr_org_unit_classifications_f houcf, 

    hr_all_organization_units_f haouf, 

    hr_organization_units_f_tl hauft 

  where haouf.organization_id = houcf.organization_id 

    and haouf.organization_id = hauft.organization_id 

    and haouf.effective_start_date between houcf.effective_start_date and houcf.effective_end_date 

    and hauft.language = 'US'

    and hauft.effective_start_date = haouf.effective_start_date 

    and hauft.effective_end_date = haouf.effective_end_date 

    and houcf.classification_code = 'FUN_BUSINESS_UNIT' 

    and :p_end_date between hauft.effective_start_date and hauft.effective_end_date

 ) Business_unit,

   per_jobs pj,

   per_person_names_f ppnf, 

   per_person_names_f bpfn, 

   per_person_names_f lmn, 

   per_assignment_supervisors_f bpf,

   per_assignment_supervisors_f lm,

   per_all_people_f papf,    

   per_all_assignments_m  paam

   where  paam.assignment_type in ('E','C')

   and paam.organization_id = department.organization_id(+)

   and paam.legal_entity_id = Legal_employer.organization_id(+)

   and paam.business_unit_id = Business_unit.organization_id(+)         

   and  ppnf.name_type = 'GLOBAL'

   and  bpfn.name_type = 'GLOBAL' 

   and  lmn.name_type = 'GLOBAL' 

   and  bpfn.person_id = bpf.manager_id

   and  lmn.person_id = lm.manager_id

   and  bpf.manager_type(+) = 'BUS_PROC_MGR'

   and  lm.manager_type(+) = 'LINE_MANAGER'

   and  pj.job_id(+) = paam.job_id

   and  ppnf.person_id = papf.person_id

   and  paam.person_id = bpf.person_id(+)

   and  paam.person_id = lm.person_id(+) --

   and  paam.person_id = papf.person_id

   and  paam.assignment_id = bpf.assignment_id(+)

   and  paam.assignment_id = lm.assignment_id(+) --

   and :p_end_date between pj.effective_start_date(+) and pj.effective_end_date(+)

   and :p_end_date between bpf.effective_start_date(+) and bpf.effective_end_date(+)

   and :p_end_date between lm.effective_start_date(+) and lm.effective_end_date(+)

   and :p_end_date between bpfn.effective_start_date(+) and bpfn.effective_end_date(+)

   and :p_end_date between papf.effective_start_date and papf.effective_end_date   

   and :p_end_date between ppnf.effective_start_date and ppnf.effective_end_date

   and :p_end_date between paam.effective_start_date and paam.effective_end_date   

 )

, Changes AS

( 

    SELECT htd.transaction_id,

ROUND(extractvalue(xmltype('<root>'||htd.data_cache||'</root>'),'root/TRANSACTION/TransCtx/CmpTxnCurSalaryValuesMap/CurSalaryAmount'),extractvalue(xmltype('<root>'||htd.data_cache||'</root>'),'root/TRANSACTION/TransCtx/CmpTxnCurSalaryValuesMap/CurSalaryAmountScale')) as "CURRENT_SALARY_AMOUNT", ROUND(extractvalue(xmltype('<root>'||htd.data_cache||'</root>'),'root/TRANSACTION/TransCtx/CmpTxnNewSalaryValuesMap/NewSalaryAmount'),extractvalue(xmltype('<root>'||htd.data_cache||'</root>'),'root/TRANSACTION/TransCtx/CmpTxnNewSalaryValuesMap/NewSalaryAmountScale')) as "NEW_SALARY_AMOUNT"

 FROM hrc_txn_data   htd 

) 


select DISTINCT emp.full_name,

    emp.Department Emp_Department ,

    emp.Business_unit Emp_Business_Unit,

    emp.Legal_employer Emp_Legal_Employer,

    emp.assignment_number,

    emp.mgr_name,

    emp.lm_mgr_name Emp_LM_Mgr,

    emp.emp_assgn_sts,

    emp.job Emp_Job,

    apprv.full_name approver_name,

    apprv.Department Apprv_Department,    

    apprv.Business_unit Apprv_Business_Unit,

    apprv.Legal_employer Apprv_Legal_Employer,

    apprv.job Apprv_Job,

    initiator.full_name initiator_name,

    initiator.Department initiator_Department,

    initiator.Business_unit initiator_Business_Unit,

    initiator.Legal_employer initiator_Legal_Employer,            

    initiator.job initiator_Job,

    txnh.module_identifier processname,  

    txnh.process_owner,txnh.parent_transaction_id,

    txnh.subject,

    txnh.object,

    txnh.object_id,

    txnd.created_by  initiatedby_name,

    txnd.status,

    wft.assigneesdisplayname,

    txnh.transaction_id,

    wft.outcome,

    txnd.creation_date , 

    wft.workflowpattern,

    wft.approvers,

    wfa.ASSIGNEE,

    trunc(wft.assigneddate),

    wfgm.member,wft.taskid,

    (select distinct assignment_status_type 

     from per_all_assignments_m a, per_all_people_f p

 where a.person_id = p.person_id

 and a.assignment_type in ('E','C')

 and a.assignment_id = (select max(assignment_id) 

      from per_all_assignments_m b 

  where b.person_id = a.person_id  

 and b.assignment_type in ('E','C')

 )

 and :p_end_date between a.effective_start_date and a.effective_end_date   

 and :p_end_date between p.effective_start_date and p.effective_end_date   

 and p.person_number = wfa.ASSIGNEE and rownum < 2 

 ) approver_status,

    wft.percentagecomplete,

    txnd.current_approver,

    txnd.last_update_date,    

    txnd.last_updated_by,

    txnd.status txnstatus,

    txnd.STATE txnstate

    ,chg.CURRENT_SALARY_AMOUNT OldValue

    ,chg.NEW_SALARY_AMOUNT NewValue

from 

 perassg emp,     

 perassg apprv,

 perassg initiator,

 Changes chg,

 fusion.hrc_txn_header txnh,

 fusion.hrc_txn_data txnd,

 fusion.hrc_txn_error txne,

 FA_FUSION_SOAINFRA.WFAPPROVALGROUPS wfg,

 fa_fusion_soainfra.wfapprovalgroupmembers wfgm,

 fa_fusion_soainfra.wfassignee wfa,

 fa_fusion_soainfra.wftask wft


where 

    (CASE txnh.object 

 WHEN 'CMP_SALARY' THEN (select distinct sal.assignment_id 

 from  cmp_salary sal 

 where assignment_id = txnh.object_id )

 WHEN 'PER_ALL_ASSIGNMENTS_M' THEN emp.assignment_id

 END) = txnh.object_id      

 and txnh.subject_id = emp.person_id

 and initiator.person_number(+) = txnd.created_by 

 and apprv.person_number(+) = wfa.assignee 

 and chg.transaction_id(+) = txnh.transaction_id 

 and txnh.transaction_id = txnd.transaction_id

 and txnh.transaction_id = txne.transaction_id(+)

 and wfgm.approvalgroupid(+) = wfg.approvalgroupid

 and wfg.approvalgroupname(+) = wft.assigneesdisplayname

 and wft.TASKID = wfa.TASKID 

 and wft.identificationkey(+) = to_char(txnh.transaction_id)

 and txnd.creation_date >= :p_start_date 

 and txnd.creation_date <= :p_end_date+1

 and txnh.transaction_id = nvl(:p_trx_id,txnh.transaction_id)          


Comments

Post a Comment

Popular posts from this blog

Oracle Cloud HCM Open Ended Absences Update

  Oracle Cloud HCM Open Ended Absences       In Oracle Cloud HCM, when there exist open ended absences like Maternity and Sickness and Employee is terminated, it is seen that there is no end date updated to these open absences as of Termination Date. This leads to ambiguity in reporting and payroll To address this issue, worked on an automation process which will pick such records and put an end date same as the termination date.   Design Steps   1.       Build a BIP report to list the Open ended absences for Terminated employees 2.      Write the Bursting definition to feed the BIP report output to Extract 3.      Desing the ETF template to build the HDL Template for End dating Open Absence 4.      Design a HCM Extract that will take the output fed from BIP Report and Auto Loads as   HDL process 5.   ...
Read more

Oracle HCM Absence Orphan Records

Image
Sometimes We come across a less known Error while submitting an Absence in Oracle Cloud HCM with an Error Code ANC-3405194 Likely caused when  Update of Accrual plan is done after an accrual process has already run till a date in future. OR  Entering accrual via HDL in a previous plan period and missing to run calculate accrual. This is Resolved when we Delete the Orphaned records found by Below Query Query to identify the Orphan Absence Records in the System SELECT  per_accrual_entry_dtl_id,         ppl.person_number       ,         ass.assignment_number   ,         plans_tl.NAME           ,         TYPE                    ,         procd_date FROM    fusion.anc_per_acrl_entry_dtls acrl_dtls ,         fusion.per_all_people_f ppl  ...
Read more
Image
           Deploying an AI agent on Redwood Page                                    1.     N avigate to the AI Agent Studio from the Tools Menu             2.    Select any available delivered AI agent and Copy it to be used by    your business.                         3.     Run it to see all the Profiles and Config are good.         4.     Publish the AI Agent              5.       Navigate to My Client Groups -> Guided Journeys      6.       Create New Journey      7.       Create Draf...
Read more