[Home] [Help]
239: INSTR(legislative_parameters,' ',
240: INSTR(legislative_parameters,p_token))
241: - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
242: business_group_id
243: FROM pay_payroll_actions
244: WHERE payroll_action_id = p_pact_id;
245:
246: l_business_group_id VARCHAR2(20);
247: l_token_value VARCHAR2(50);
498: IS
499:
500: CURSOR csr_archive_effective_date(pactid NUMBER) IS
501: SELECT effective_date
502: FROM pay_payroll_actions
503: WHERE payroll_action_id = pactid;
504:
505: CURSOR csr_input_value_id(p_element_name CHAR,
506: p_value_name CHAR) IS
727:
728: CURSOR cur_payroll_assg_action is
729: select paa.assignment_action_id pay_assg_act_id
730: from pay_assignment_actions paa,
731: pay_payroll_actions ppa
732: where paa.assignment_id in (select assignment_id
733: from per_all_assignments_f
734: where person_id = p_person_id
735: )
763:
764: CURSOR payroll_asg_action is
765: select max(paa.assignment_action_id)
766: from pay_assignment_actions paa,
767: pay_payroll_actions ppa
768: where paa.assignment_id in (select assignment_id
769: from per_all_assignments_f
770: where person_id = p_person_id
771: )
842: null,
843: null))
844: FROM pay_action_contexts pac,
845: pay_assignment_actions pas,
846: pay_payroll_actions ppa
847: WHERE substr(pac.Context_Value,1,4) = p_context_value
848: AND pac.assignment_id in (select papf.assignment_id
849: from per_all_assignments_f papf
850: where papf.person_id = p_person_id
859:
860: CURSOR cur_get_prev_run_seq is
861: select paa.action_sequence
862: from pay_assignment_actions paa,
863: pay_payroll_actions ppa,
864: pay_action_interlocks pai,
865: pay_assignment_actions paa1
866: where paa1.source_action_id = p_last_p45_act
867: and pai.locking_action_id = paa1.assignment_action_id
1279: , pay_balance_feeds_f FEED
1280: , pay_run_results RR
1281: , pay_assignment_actions ASSACT
1282: , pay_assignment_actions BAL_ASSACT
1283: , pay_payroll_actions PACT
1284: , pay_payroll_actions BACT
1285: , per_time_periods PPTP
1286: , per_time_periods BPTP
1287: , pay_run_results PROCESS_RR
1280: , pay_run_results RR
1281: , pay_assignment_actions ASSACT
1282: , pay_assignment_actions BAL_ASSACT
1283: , pay_payroll_actions PACT
1284: , pay_payroll_actions BACT
1285: , per_time_periods PPTP
1286: , per_time_periods BPTP
1287: , pay_run_results PROCESS_RR
1288: , pay_run_result_values PROCESS
1341: -- in the same tax year for which the archive is run
1342: /*CURSOR Cur_Act_Contexts IS
1343: SELECT pac.Context_ID,pac.Context_Value,pac.Assignment_action_id
1344: FROM pay_action_contexts pac,pay_assignment_actions pas,
1345: pay_payroll_actions ppa,pay_payroll_actions appa
1346: WHERE pac.Context_Value = 'IE_A'
1347: AND pac.assignment_id = p_assignment_id
1348: AND pas.assignment_action_id = pac.assignment_action_id
1349: AND ppa.payroll_action_id = pas.payroll_action_id
1365: -- the previous P45 archive.
1366: CURSOR cur_get_prev_run_seq is
1367: select paa.action_sequence
1368: from pay_assignment_actions paa,
1369: pay_payroll_actions ppa,
1370: pay_action_interlocks pai,
1371: pay_assignment_actions paa1
1372: where paa1.source_action_id = p_last_p45_action
1373: and pai.locking_action_id = paa1.assignment_action_id
1400: null,
1401: null))
1402: FROM pay_action_contexts pac,
1403: pay_assignment_actions pas,
1404: pay_payroll_actions ppa
1405: WHERE pac.Context_Value like 'IE_A%'
1406: AND pac.assignment_id in (select papf.assignment_id
1407: from per_all_assignments_f papf
1408: where papf.person_id = p_person_id
2010: select act_inf.action_information3
2011: from pay_assignment_actions paa_run,
2012: pay_action_interlocks pai,
2013: pay_assignment_actions paa,
2014: pay_payroll_actions ppa,
2015: pay_action_information act_inf
2016: where ppa.payroll_action_id = paa.payroll_action_id
2017: and ppa.report_type = 'P45'
2018: and ppa.report_qualifier = 'IE'
2228: from pay_run_result_values prr,
2229: pay_run_results pr,
2230: pay_input_values_f piv,
2231: pay_assignment_actions pas,
2232: pay_payroll_actions ppa
2233: where pas.assignment_id in (select assignment_id
2234: from per_all_assignments_f
2235: where person_id = p_person_id)
2236: and pas.tax_unit_id = g_paye_ref
2265: select act_inf.action_information3
2266: from pay_assignment_actions paa_run,
2267: pay_action_interlocks pai,
2268: pay_assignment_actions paa,
2269: pay_payroll_actions ppa,
2270: pay_action_information act_inf
2271: where ppa.payroll_action_id = paa.payroll_action_id
2272: and ppa.report_type = 'P45'
2273: and ppa.report_qualifier = 'IE'
2542: hrl.address_line_3 employer_tax_addr3,
2543: hrl.telephone_number_1 employer_tax_ref_phone
2544: --
2545: FROM pay_all_payrolls_f ppf,
2546: pay_payroll_actions pact,
2547: hr_organization_information org,
2548: hr_soft_coding_keyflex flex,
2549: hr_organization_units hou,
2550: hr_locations_all hrl
2641: pact.effective_date effective_date
2642: FROM pay_assignment_actions paa,
2643: pay_action_interlocks pai,
2644: pay_assignment_actions paa_arc,
2645: pay_payroll_actions pact
2646: WHERE pai.locked_action_id = paa.assignment_action_id
2647: AND pai.locking_action_id = paa_arc.assignment_action_id
2648: AND paa_arc.payroll_action_id = p_pact_id
2649: AND paa.payroll_action_id = pact.payroll_action_id
2897: else
2898: */
2899: sqlstr := 'SELECT DISTINCT person_id
2900: FROM per_people_f ppf,
2901: pay_payroll_actions ppa
2902: WHERE ppa.payroll_action_id = :payroll_action_id
2903: AND ppa.business_group_id +0= ppf.business_group_id
2904: ORDER BY ppf.person_id';
2905: hr_utility.set_location('Leaving ' || l_proc,40);
2930: as1.period_of_service_id period_of_service_id
2931: FROM --per_periods_of_service ppos,
2932: per_all_assignments_f as1,
2933: pay_assignment_actions act,
2934: pay_payroll_actions appa,
2935: pay_action_interlocks pai,
2936: pay_assignment_actions act1,
2937: pay_payroll_actions appa2
2938: WHERE /*appa.consolidation_set_id = p_consolidation_id*/
2933: pay_assignment_actions act,
2934: pay_payroll_actions appa,
2935: pay_action_interlocks pai,
2936: pay_assignment_actions act1,
2937: pay_payroll_actions appa2
2938: WHERE /*appa.consolidation_set_id = p_consolidation_id*/
2939: act.tax_unit_id = p_paye_ref
2940: AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
2941: AND as1.person_id BETWEEN stperson AND endperson
2990: AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
2991: max(appa4.payroll_action_id)
2992: FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
2993: pay_assignment_actions act2,
2994: pay_payroll_actions appa4
2995: WHERE /*ppp.assignment_action_id=act2.assignment_action_id
2996: AND*/ act2.assignment_id = act.assignment_id
2997: AND act2.action_status = 'C'
2998: AND appa4.payroll_action_id = act2.payroll_action_id
3001: -- bug 5597735, change the not exists clause.
3002: -- refer bug 5233518 for more details.
3003: AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3004: from pay_assignment_actions act3,
3005: pay_payroll_actions appa3,
3006: pay_action_interlocks pai, --bug 4208273
3007: pay_assignment_actions act2, --bug 4208273
3008: pay_payroll_actions appa4 --bug 4208273
3009: where pai.locked_action_id= act3.assignment_action_id
3004: from pay_assignment_actions act3,
3005: pay_payroll_actions appa3,
3006: pay_action_interlocks pai, --bug 4208273
3007: pay_assignment_actions act2, --bug 4208273
3008: pay_payroll_actions appa4 --bug 4208273
3009: where pai.locked_action_id= act3.assignment_action_id
3010: and pai.locking_action_id=act2.assignment_action_id
3011: and act3.action_sequence >= act1.action_sequence --bug 4193738
3012: and act3.assignment_id in (select distinct paaf.assignment_id
3166: paa.date_earned date_earned,
3167: ptp.time_period_id time_period_id
3168: FROM pay_action_interlocks pre,
3169: pay_action_interlocks pay,
3170: pay_payroll_actions paa,
3171: pay_payroll_actions ppaa,
3172: pay_assignment_actions assact,
3173: pay_assignment_actions passact,
3174: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3167: ptp.time_period_id time_period_id
3168: FROM pay_action_interlocks pre,
3169: pay_action_interlocks pay,
3170: pay_payroll_actions paa,
3171: pay_payroll_actions ppaa,
3172: pay_assignment_actions assact,
3173: pay_assignment_actions passact,
3174: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3175: WHERE pre.locked_action_id = pay.locking_action_id
3191: /*CURSOR cur_child_pay_action(p_assignment_id NUMBER,
3192: p_date_earned DATE)is
3193: SELECT max(paa.assignment_action_id)
3194: FROM pay_assignment_actions paa,
3195: pay_payroll_actions ppa
3196: where paa.assignment_id = p_assignment_id
3197: AND paa.payroll_action_id = ppa.payroll_action_id
3198: AND ppa.date_earned =p_date_earned
3199: AND ppa.action_type in ('R','Q')
3206: SELECT /*+ USE_NL(paa, ppa) */
3207: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3208: paa.assignment_action_id),16))
3209: FROM pay_assignment_actions paa,
3210: pay_payroll_actions ppa
3211: WHERE paa.payroll_action_id = ppa.payroll_action_id
3212: AND paa.assignment_id in (select assignment_id
3213: from per_all_assignments_f
3214: where person_id = p_person_id
3224: CURSOR cur_get_latest_p45(p_pact_id NUMBER,
3225: p_person_id NUMBER
3226: ) IS
3227: SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3228: FROM pay_payroll_actions ppa_p45,
3229: pay_assignment_actions p45_src,
3230: pay_action_information pai_p45,
3231: pay_assignment_actions paa_src
3232: WHERE ppa_p45.action_type = 'X'
3246: -- Cursor to fetch action context id of P45 for previous period of service.
3247: CURSOR cur_get_last_p45(p_person_id NUMBER,p_termination_date DATE,p_pact NUMBER) IS
3248: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3249: paa.assignment_action_id),16))
3250: FROM pay_payroll_actions ppa,
3251: pay_assignment_actions paa,
3252: pay_action_information pai
3253: WHERE paa.assignment_action_id = pai.action_context_id
3254: AND pai.action_information_category = 'IE P45 INFORMATION'
3562: AS
3563:
3564: /* Split the cursor to 2 cursors to improve the performace.new parameter is added to reduce the number of
3565: tables involved to 2 from 5 (5005788) */
3566: CURSOR csr_get_def_bal(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
3567: ,p_balance_name pay_balance_types.balance_name%TYPE
3568: ) IS
3569: SELECT pai1.action_information2
3570: FROM pay_action_information pai1
3699: ORDER BY pai_iep45.action_information2;
3700: */
3701: -- The above cursor has been modified as specified below.
3702: CURSOR cur_p45_details(
3703: c_p45_arch_id pay_payroll_actions.payroll_action_id%TYPE) IS
3704: SELECT paa.assignment_id assignment_id
3705: ,paa.assignment_action_id
3706: ,pai_iep45.action_information2 supp_flag
3707: ,decode(ptp.period_type,'Lunar Month','W',decode(instr(ptp.period_type,'Week'),0,'M','W')) freq
3813: AND emp_supp.pay_period =p_period
3814: AND emp_supp.period_frequency = decode(p_freq,'M','Monthly','Weekly');
3815: --
3816: CURSOR cur_p30_start_date(
3817: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
3818: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
3819: FROM pay_assignment_actions paa_p30,
3820: pay_action_interlocks pai_p30,
3821: pay_assignment_actions paa_arc,
3818: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
3819: FROM pay_assignment_actions paa_p30,
3820: pay_action_interlocks pai_p30,
3821: pay_assignment_actions paa_arc,
3822: pay_payroll_actions ppa_arc
3823: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
3824: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
3825: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
3826: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
3825: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
3826: AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
3827: --
3828: CURSOR cur_employer_address(
3829: c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
3830: SELECT substr(pai.action_information5,1,30) employer_tax_addr1
3831: ,substr(pai.action_information6,1,30) employer_tax_addr2
3832: ,substr(pai.action_information7,1,30) employer_tax_addr3
3833: ,substr(pai.action_information26,1,30) employer_tax_contact