[Home] [Help]
464: SELECT PAI.object_version_number
465: ,PAI.action_information_id
466: ,PAC.assignment_action_id
467: FROM pay_action_information PAI
468: ,pay_assignment_actions PAC
469: ,pay_payroll_actions PPA
470: WHERE PAI.action_context_id = PAC.assignment_action_id
471: AND PAC.assignment_id = p_assignment_id
472: AND PAC.payroll_action_id = PPA.payroll_action_id
643: AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
644: AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
645: AND EXISTS(SELECT 1
646: FROM pay_jp_pre_tax PPT,
647: pay_assignment_actions PAA,
648: pay_payroll_actions PPA
649: WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
650: AND PAA.assignment_id = PAF.assignment_id
651: AND PPA.business_group_id + 0 = p_business_group_id
658: AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
659: AND NOT EXISTS(
660: SELECT null
661: FROM pay_payroll_actions PPAV,
662: pay_assignment_actions PAAV,
663: pay_action_interlocks PAI
664: WHERE PAI.locked_action_id = PAA.assignment_action_id
665: AND PAAV.assignment_action_id = PAI.locking_action_id
666: AND PPAV.payroll_action_id = PAAV.payroll_action_id
696: AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
697: AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
698: AND EXISTS(SELECT 1
699: FROM pay_jp_pre_tax PPT,
700: pay_assignment_actions PAA,
701: pay_payroll_actions PPA
702: WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
703: AND PAA.assignment_id = PAF.assignment_id
704: AND PPA.business_group_id + 0 = p_business_group_id
711: AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
712: AND NOT EXISTS(
713: SELECT null
714: FROM pay_payroll_actions PPAV,
715: pay_assignment_actions PAAV,
716: pay_action_interlocks PAI
717: WHERE PAI.locked_action_id = PAA.assignment_action_id
718: AND PAAV.assignment_action_id = PAI.locking_action_id
719: AND PPAV.payroll_action_id = PAAV.payroll_action_id
721: ORDER BY PAF.assignment_id;
722: --
723: CURSOR lcu_next_action_id
724: IS
725: SELECT pay_assignment_actions_s.NEXTVAL
726: FROM dual;
727: --
728: -- Local Variables
729: lc_procedure VARCHAR2(200);
906: hr_utility.set_location('Error in '||lc_procedure,999999);
907: RAISE;
908: END assignment_action_code;
909: --
910: PROCEDURE archive_code ( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
911: , p_effective_date IN pay_payroll_actions.effective_date%type
912: )
913: --************************************************************************
914: -- PROCEDURE
935: -- None
936: --************************************************************************/
937: IS
938: --
939: CURSOR lcu_get_assignment_id ( p_assignment_action_id pay_assignment_actions.assignment_action_id%type )
940: IS
941: SELECT assignment_id
942: FROM pay_assignment_actions
943: WHERE assignment_action_id = p_assignment_action_id;
938: --
939: CURSOR lcu_get_assignment_id ( p_assignment_action_id pay_assignment_actions.assignment_action_id%type )
940: IS
941: SELECT assignment_id
942: FROM pay_assignment_actions
943: WHERE assignment_action_id = p_assignment_action_id;
944: --
945: CURSOR lcu_employee_details ( p_assignment_id per_all_assignments_f.assignment_id%TYPE
946: , p_effective_date DATE
977: AND TRUNC(p_effective_date) BETWEEN TRUNC(NVL(PADC.date_from(+),p_effective_date)) AND NVL(PADC.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY')) --bug #9554515
978: AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id) -- #Bug 9569078
979: AND EXISTS(SELECT 1
980: FROM pay_jp_pre_tax PPT,
981: pay_assignment_actions PAA,
982: pay_payroll_actions PPA
983: WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
984: AND PAA.assignment_id = PAF.assignment_id
985: AND PPA.action_type in ('R', 'Q', 'B', 'I')
992: AND TRUNC(NVL(PPS.actual_termination_date,PPA.effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
993: AND NOT EXISTS(
994: SELECT null
995: FROM pay_payroll_actions PPAV,
996: pay_assignment_actions PAAV,
997: pay_action_interlocks PAI
998: WHERE PAI.locked_action_id = PAA.assignment_action_id
999: AND PAAV.assignment_action_id = PAI.locking_action_id
1000: AND PPAV.payroll_action_id = PAAV.payroll_action_id
1035: ,NVL(sum(ppt.itax), 0) withholding_tax
1036: ,NVL(sum(ppt.sp_ltax_shi), 0) muncipal_tax -- Bug 9525922
1037: ,NVL(sum(ppt.sp_ltax_to), 0) prefectural_tax -- Bug 9525922
1038: FROM pay_jp_pre_tax PPT,
1039: pay_assignment_actions PAA,
1040: pay_payroll_actions PPA
1041: WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
1042: AND PPA.business_group_id + 0 = p_business_group_id
1043: AND PAA.assignment_id = p_assignment_id
1049: AND PPT.salary_category = 'TERM'
1050: AND NOT EXISTS(
1051: SELECT null
1052: FROM pay_payroll_actions PPAV,
1053: pay_assignment_actions PAAV,
1054: pay_action_interlocks PAI
1055: WHERE PAI.locked_action_id = PAA.assignment_action_id
1056: AND PAAV.assignment_action_id = PAI.locking_action_id
1057: AND PPAV.payroll_action_id = PAAV.payroll_action_id
1063: SELECT PAA.assignment_action_id
1064: ,PPA.effective_date
1065: ,PPA.date_earned
1066: FROM pay_jp_pre_tax PPT,
1067: pay_assignment_actions PAA,
1068: pay_payroll_actions PPA
1069: WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
1070: AND PPA.business_group_id + 0 = p_business_group_id
1071: AND PAA.assignment_id = p_assignment_id
1077: AND PPT.salary_category = 'TERM'
1078: AND NOT EXISTS(
1079: SELECT null
1080: FROM pay_payroll_actions PPAV,
1081: pay_assignment_actions PAAV,
1082: pay_action_interlocks PAI
1083: WHERE PAI.locked_action_id = PAA.assignment_action_id
1084: AND PAAV.assignment_action_id = PAI.locking_action_id
1085: AND PPAV.payroll_action_id = PAAV.payroll_action_id
1117: CURSOR lcu_prev_archive (p_assignment_id per_all_assignments_f.assignment_id%TYPE)
1118: IS
1119: SELECT 'Y'
1120: FROM pay_action_information PAI
1121: ,pay_assignment_actions PAC
1122: ,pay_payroll_actions PPA
1123: WHERE PAI.action_context_id = PAC.assignment_action_id
1124: AND PAC.assignment_id = p_assignment_id
1125: AND PAC.payroll_action_id = PPA.payroll_action_id
1166: ln_def_balance_id_spc pay_defined_balances.defined_balance_id%TYPE;
1167: ln_term_ded_amt NUMBER;
1168: ln_term_ded_amt_spc NUMBER;
1169: ln_amt NUMBER;
1170: ln_term_ass_act_id pay_assignment_actions.assignment_action_id%TYPE;
1171: --
1172: ld_term_payment_date pay_payroll_actions.effective_date%TYPE;
1173: ld_date_earned pay_payroll_actions.date_earned%TYPE;
1174: ld_start_date DATE;
1505: IS
1506: --
1507: CURSOR lcu_assacts IS
1508: SELECT PAA.assignment_action_id
1509: FROM pay_assignment_actions PAA
1510: ,pay_payroll_actions PPA
1511: WHERE PAA.payroll_action_id = PPA.payroll_action_id
1512: AND PAA.action_status = 'C'
1513: AND PPA.report_type = gc_report_type