[Home] [Help]
481: /* Group2 - Payroll Elements */
482: /* Fetch payroll elements from Org level Setup*/
483: CURSOR csr_payroll_elements IS
484: SELECT DISTINCT pap.payroll_name payroll_name,
485: fnd_number.canonical_to_number(hoi.org_information3) element_id,
486: nvl(petl.reporting_name,petl.element_name) element_name,
487: get_cost_alloc_key_flex(hoi.org_information3) exp_cat_code
488: FROM hr_organization_information hoi
489: ,hr_organization_units hou
492: ,pay_element_types_f pet
493: WHERE hoi.org_information_context = 'PER_CNAO_ORG_INFO'
494: AND hou.organization_id = hoi.organization_id
495: AND hou.business_group_id = g_bg_id
496: AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information2)
497: AND EXISTS (SELECT 1
498: FROM per_assignments_f paf
499: WHERE paf.payroll_id = pap.payroll_id
500: and paf.effective_end_date >= g_start_date
505: WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
506: AND scl.segment1 = hou.organization_id
507: AND paf.business_group_id = g_bg_id
508: )
509: AND petl.element_type_id = fnd_number.canonical_to_number(hoi.org_information3)
510: AND pet.element_type_id = petl.element_type_id
511: AND petl.language = userenv('LANG')
512: AND hou.date_from <= g_end_date
513: AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD')) >= g_start_date
516: AND g_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
517: AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date
518: UNION
519: SELECT DISTINCT pap.payroll_name payroll_name,
520: fnd_number.canonical_to_number(hoi.org_information2) element_id,
521: nvl(PBT_TL.REPORTING_NAME,PBT_TL.BALANCE_NAME) element_name,
522: 'X' exp_cat_code
523: FROM hr_organization_information hoi
524: ,hr_organization_units hou
526: ,PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
527: WHERE hoi.org_information_context = 'PER_CNAO_BAL_INFO'
528: AND hou.organization_id = hoi.organization_id
529: AND hou.business_group_id = g_bg_id
530: AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information4)
531: AND EXISTS (SELECT 1
532: FROM per_assignments_f paf
533: WHERE paf.payroll_id = pap.payroll_id
534: and paf.effective_end_date >= g_start_date
539: WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
540: AND scl.segment1 = hou.organization_id
541: AND paf.business_group_id = g_bg_id
542: )
543: AND PBT_TL.balance_type_id = fnd_number.canonical_to_number(hoi.org_information2)
544: AND PBT.balance_type_id = PBT_TL.balance_type_id
545: AND PBT_TL.language = userenv('LANG')
546: AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
547:
873: pname.payroll_name pay_name,
874: to_char(p_effective_date,'YYYY') pyear,
875: to_number(to_char(p_effective_date,'MM')) pnum,
876: pet.element_type_id eid,
877: trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
878: from pay_action_information pai,
879: (select pap.payroll_name
880: from pay_all_payrolls_f pap
881: where pap.payroll_id = p_payroll_id
915: pname.payroll_name pay_name,
916: to_char(p_effective_date,'YYYY') pyear,
917: to_number(to_char(p_effective_date,'MM')) pnum,
918: pbt.balance_type_id eid,
919: trim(to_char(fnd_number.canonical_to_number(pai.action_information6),'999999990.99')) current_amount
920: from pay_action_information pai,
921: (select pap.payroll_name
922: from pay_all_payrolls_f pap
923: where pap.payroll_id = p_payroll_id
966: pname.payroll_name pay_name,
967: to_char(p_effective_date,'YYYY') pyear,
968: to_number(to_char(p_effective_date,'MM')) pnum,
969: pbt.balance_type_id eid,
970: trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
971: from pay_action_information pai,
972: (select pap.payroll_name
973: from pay_all_payrolls_f pap
974: where pap.payroll_id = p_payroll_id