[Home] [Help]
158: SELECT distinct pai_emp.action_information15 organization
159: ,ppf.payroll_id
160: FROM per_time_periods ptp
161: ,pay_action_information pai_emp
162: ,pay_assignment_actions paa1
163: ,pay_action_interlocks lck
164: ,pay_payroll_actions ppa1
165: ,pay_all_payrolls_f ppf
166: WHERE ptp.payroll_id = l_payroll_id
181: SELECT distinct pai_emp.action_information15 organization
182: ,ppf.payroll_id
183: FROM per_time_periods ptp
184: ,pay_action_information pai_emp
185: ,pay_assignment_actions paa1
186: ,pay_action_interlocks lck
187: ,pay_payroll_actions ppa1
188: ,pay_all_payrolls_f ppf
189: WHERE ptp.time_period_id = pai_emp.action_information16
214: SELECT distinct pai_emp.action_information15 organization
215: ,ppf.payroll_id
216: FROM per_time_periods ptp
217: ,pay_action_information pai_emp
218: ,pay_assignment_actions paa1
219: ,pay_action_interlocks lck
220: ,pay_payroll_actions ppa1
221: ,pay_all_payrolls_f ppf
222: WHERE ptp.time_period_id = pai_emp.action_information16
236: SELECT distinct pai_emp.action_information15 organization
237: ,ppf.payroll_id
238: FROM per_time_periods ptp
239: ,pay_action_information pai_emp
240: ,pay_assignment_actions paa1
241: ,pay_action_interlocks lck
242: ,pay_payroll_actions ppa1
243: ,pay_all_payrolls_f ppf
244: WHERE ptp.time_period_id = pai_emp.action_information16
258: cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
259: SELECT distinct ppf.payroll_id
260: FROM per_time_periods ptp
261: ,pay_action_information pai_emp
262: ,pay_assignment_actions paa1
263: ,pay_action_interlocks lck
264: ,pay_payroll_actions ppa1
265: ,pay_all_payrolls_f ppf
266: WHERE ptp.time_period_id = pai_emp.action_information16
291: cursor csr_org_only_distinct_pyrl(l_organization_id number , l_effective_date date) is
292: SELECT distinct ppf.payroll_id
293: FROM per_time_periods ptp
294: ,pay_action_information pai_emp
295: ,pay_assignment_actions paa1
296: ,pay_action_interlocks lck
297: ,pay_payroll_actions ppa1
298: ,pay_all_payrolls_f ppf
299: WHERE ptp.time_period_id = pai_emp.action_information16
338: ,nvl(pai_emp1.action_information4,0) ytd_deduction
339: FROM per_time_periods ptp
340: ,pay_action_information pai_emp
341: ,pay_action_information pai_emp1
342: ,pay_assignment_actions paa1
343: ,pay_action_interlocks lck
344: ,pay_payroll_actions ppa1
345: ,pay_all_payrolls_f ppf
346: WHERE ptp.payroll_id = l_payroll_id
372: ,pay_v.numeric_value earn_value
373: ,pai_ele.action_information7
374: FROM pay_action_information pai_ele
375: ,pay_emea_paymnts_action_info_v pay_v
376: ,pay_assignment_actions paa
377: WHERE paa.assignment_action_id = l_assact_id
378: AND paa.payroll_action_id = pai_ele.action_context_id
379: AND pai_ele.action_context_type = 'PA'
380: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
388: ,pet.result_value earn_value
389: ,pai.action_information7
390: FROM
391: pay_action_interlocks lck,
392: pay_assignment_actions paa1,
393: pay_assignment_actions paa2,
394: pay_payroll_actions ppa,
395: pay_action_information pai,
396: pay_emea_payment_values_v pet
389: ,pai.action_information7
390: FROM
391: pay_action_interlocks lck,
392: pay_assignment_actions paa1,
393: pay_assignment_actions paa2,
394: pay_payroll_actions ppa,
395: pay_action_information pai,
396: pay_emea_payment_values_v pet
397: WHERE
420: ,pay_v.numeric_value ded_value
421: ,pai_ele.action_information7
422: FROM pay_action_information pai_ele
423: ,pay_emea_paymnts_action_info_v pay_v
424: ,pay_assignment_actions paa
425: WHERE paa.assignment_action_id= l_assact_id
426: AND paa.payroll_action_id = pai_ele.action_context_id
427: AND pai_ele.action_context_type = 'PA'
428: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
436: ,pet.result_value ded_value
437: ,pai.action_information7
438: FROM
439: pay_action_interlocks lck,
440: pay_assignment_actions paa1,
441: pay_assignment_actions paa2,
442: pay_payroll_actions ppa,
443: pay_action_information pai,
444: pay_emea_payment_values_v pet
437: ,pai.action_information7
438: FROM
439: pay_action_interlocks lck,
440: pay_assignment_actions paa1,
441: pay_assignment_actions paa2,
442: pay_payroll_actions ppa,
443: pay_action_information pai,
444: pay_emea_payment_values_v pet
445: WHERE
467: ,pen.segment2 branch_name
468: ,pen.segment4 account_number
469: ,pen.value pay_amount
470: ,pen.action_context_id
471: ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
472: FROM pay_emp_net_dist_action_info_v pen
473: ,pay_action_interlocks pai
474: ,pay_assignment_actions paa
475: ,pay_payroll_actions ppa
470: ,pen.action_context_id
471: ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
472: FROM pay_emp_net_dist_action_info_v pen
473: ,pay_action_interlocks pai
474: ,pay_assignment_actions paa
475: ,pay_payroll_actions ppa
476: ,pay_pre_payments ppp
477: WHERE pen.action_context_id = l_assact_id
478: AND pen.action_context_id = pai.locking_action_id
490: /*Modifying this cursor for bug 7307152
491: SELECT sum(pay_v.numeric_value)
492: FROM pay_action_information pai_ele
493: ,pay_emea_paymnts_action_info_v pay_v
494: ,pay_assignment_actions paa
495: WHERE paa.payroll_action_id = pai_ele.action_context_id
496: AND pai_ele.action_context_type = 'PA'
497: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
498: AND pai_ele.action_information7 IN ('E')
501: AND pay_v.payment_type NOT IN ('F')
502: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
503: FROM per_time_periods ptp
504: ,pay_action_information pai_emp
505: ,pay_assignment_actions paa1
506: ,pay_action_interlocks lck
507: ,pay_payroll_actions ppa1
508: WHERE ptp.payroll_id = l_payroll_id
509: AND ptp.time_period_id = pai_emp.action_information16
528: AND ptp.end_date = l_effective_date);*/
529: SELECT sum(pet.result_value)
530: FROM pay_action_information pai_ele,
531: pay_emea_payment_values_v pet,
532: pay_assignment_actions paa
533: WHERE paa.payroll_action_id = pai_ele.action_context_id
534: AND pai_ele.action_context_type = 'PA'
535: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
536: AND pai_ele.action_information7 IN ('E')
540: AND pet.input_value_id = pai_ele.action_information3
541: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
542: FROM per_time_periods ptp
543: ,pay_action_information pai_emp
544: ,pay_assignment_actions paa1
545: ,pay_action_interlocks lck
546: ,pay_payroll_actions ppa1
547: WHERE ptp.payroll_id = l_payroll_id
548: AND ptp.time_period_id = pai_emp.action_information16
566: AND paa1.action_status = 'C'
567: AND ptp.end_date = l_effective_date);
568: --CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
569: /*SELECT sum(pay_v.numeric_value)
570: FROM pay_assignment_actions paa
571: ,per_time_periods ptp
572: ,pay_assignment_actions paa1
573: ,pay_action_interlocks lck
574: ,pay_payroll_actions ppa1
568: --CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
569: /*SELECT sum(pay_v.numeric_value)
570: FROM pay_assignment_actions paa
571: ,per_time_periods ptp
572: ,pay_assignment_actions paa1
573: ,pay_action_interlocks lck
574: ,pay_payroll_actions ppa1
575: ,pay_action_information pai_ele
576: ,pay_action_information pai_emp
601: /*
602: select SUM(pay_v.numeric_value)
603: from pay_action_information pai_ele
604: ,pay_emea_paymnts_action_info_v pay_v
605: ,pay_assignment_actions paa
606: where paa.payroll_action_id = pai_ele.action_context_id
607: and pai_ele.action_context_type = 'PA'
608: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
609: and pai_ele.action_information7 in ('E')
613: and paa.assignment_action_id in (
614: select pai_emp.action_context_id arch_assact
615: from per_time_periods ptp
616: ,pay_action_information pai_emp
617: ,pay_assignment_actions paa1
618: ,pay_action_interlocks lck
619: ,pay_payroll_actions ppa1
620: where ptp.payroll_id = l_payroll_id
621: and ptp.time_period_id = pai_emp.action_information16
635: CURSOR csr_SEOO_SPLIT_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
636: select pai_emp.action_context_id arch_assact
637: from per_time_periods ptp
638: ,pay_action_information pai_emp
639: ,pay_assignment_actions paa1
640: ,pay_action_interlocks lck
641: ,pay_payroll_actions ppa1
642: where ptp.payroll_id = l_payroll_id
643: and ptp.time_period_id = pai_emp.action_information16
657: /*Modifying this cursor for bug 7307152
658: select SUM(pay_v.numeric_value)
659: from pay_action_information pai_ele
660: ,pay_emea_paymnts_action_info_v pay_v
661: ,pay_assignment_actions paa
662: where paa.payroll_action_id = pai_ele.action_context_id
663: and pai_ele.action_context_type = 'PA'
664: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
665: and pai_ele.action_information7 in ('E')
669: and paa.assignment_action_id = l_arch_assact;*/
670: select SUM(pet.result_value)
671: from pay_action_information pai_ele
672: ,pay_emea_payment_values_v pet
673: ,pay_assignment_actions paa
674: where paa.payroll_action_id = pai_ele.action_context_id
675: and pai_ele.action_context_type = 'PA'
676: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
677: and pai_ele.action_information7 in ('E')
687: /*Modifying this cursor for bug 7307152
688: SELECT sum(pay_v.numeric_value)
689: FROM pay_action_information pai_ele
690: ,pay_emea_paymnts_action_info_v pay_v
691: ,pay_assignment_actions paa
692: WHERE paa.payroll_action_id = pai_ele.action_context_id
693: AND pai_ele.action_context_type = 'PA'
694: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
695: AND pai_ele.action_information7 IN ('D')
698: AND pay_v.payment_type NOT IN ('F')
699: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
700: FROM per_time_periods ptp
701: ,pay_action_information pai_emp
702: ,pay_assignment_actions paa1
703: ,pay_action_interlocks lck
704: ,pay_payroll_actions ppa1
705: WHERE ptp.payroll_id = l_payroll_id
706: AND ptp.time_period_id = pai_emp.action_information16
725: AND ptp.end_date = l_effective_date);*/
726: SELECT sum(pet.result_value)
727: FROM pay_action_information pai_ele,
728: pay_emea_payment_values_v pet,
729: pay_assignment_actions paa
730: WHERE paa.payroll_action_id = pai_ele.action_context_id
731: AND pai_ele.action_context_type = 'PA'
732: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
733: AND pai_ele.action_information7 IN ('D')
737: AND pet.input_value_id = pai_ele.action_information3
738: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
739: FROM per_time_periods ptp
740: ,pay_action_information pai_emp
741: ,pay_assignment_actions paa1
742: ,pay_action_interlocks lck
743: ,pay_payroll_actions ppa1
744: WHERE ptp.payroll_id = l_payroll_id
745: AND ptp.time_period_id = pai_emp.action_information16
763: AND paa1.action_status = 'C'
764: AND ptp.end_date = l_effective_date);
765: --CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
766: /*SELECT sum(pay_v.numeric_value)
767: FROM pay_assignment_actions paa
768: ,per_time_periods ptp
769: ,pay_assignment_actions paa1
770: ,pay_action_interlocks lck
771: ,pay_payroll_actions ppa1
765: --CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
766: /*SELECT sum(pay_v.numeric_value)
767: FROM pay_assignment_actions paa
768: ,per_time_periods ptp
769: ,pay_assignment_actions paa1
770: ,pay_action_interlocks lck
771: ,pay_payroll_actions ppa1
772: ,pay_action_information pai_ele
773: ,pay_action_information pai_emp
797: /*
798: select SUM(pay_v.numeric_value)
799: from pay_action_information pai_ele
800: ,pay_emea_paymnts_action_info_v pay_v
801: ,pay_assignment_actions paa
802: where paa.payroll_action_id = pai_ele.action_context_id
803: and pai_ele.action_context_type = 'PA'
804: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
805: and pai_ele.action_information7 in ('D')
809: and paa.assignment_action_id in (
810: select pai_emp.action_context_id arch_assact
811: from per_time_periods ptp
812: ,pay_action_information pai_emp
813: ,pay_assignment_actions paa1
814: ,pay_action_interlocks lck
815: ,pay_payroll_actions ppa1
816: where ptp.payroll_id = l_payroll_id
817: and ptp.time_period_id = pai_emp.action_information16
830: CURSOR csr_SDOO_SPLIT_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
831: select pai_emp.action_context_id arch_assact
832: from per_time_periods ptp
833: ,pay_action_information pai_emp
834: ,pay_assignment_actions paa1
835: ,pay_action_interlocks lck
836: ,pay_payroll_actions ppa1
837: where ptp.payroll_id = l_payroll_id
838: and ptp.time_period_id = pai_emp.action_information16
852: /*Modifying this cursor for bug 7307152
853: select SUM(pay_v.numeric_value)
854: from pay_action_information pai_ele
855: ,pay_emea_paymnts_action_info_v pay_v
856: ,pay_assignment_actions paa
857: where paa.payroll_action_id = pai_ele.action_context_id
858: and pai_ele.action_context_type = 'PA'
859: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
860: and pai_ele.action_information7 in ('D')
864: and paa.assignment_action_id = l_arch_assact;*/
865: select SUM(pet.result_value)
866: from pay_action_information pai_ele
867: ,pay_emea_payment_values_v pet
868: ,pay_assignment_actions paa
869: where paa.payroll_action_id = pai_ele.action_context_id
870: and pai_ele.action_context_type = 'PA'
871: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
872: and pai_ele.action_information7 in ('D')