[Home] [Help]
159: SELECT distinct pai_emp.action_information15 organization
160: ,ppf.payroll_id
161: FROM per_time_periods ptp
162: ,pay_action_information pai_emp
163: ,pay_assignment_actions paa1
164: ,pay_action_interlocks lck
165: ,pay_payroll_actions ppa1
166: ,pay_all_payrolls_f ppf
167: WHERE ptp.payroll_id = l_payroll_id
182: SELECT distinct pai_emp.action_information15 organization
183: ,ppf.payroll_id
184: FROM per_time_periods ptp
185: ,pay_action_information pai_emp
186: ,pay_assignment_actions paa1
187: ,pay_payroll_actions ppa1
188: ,pay_all_payrolls_f ppf
189: WHERE ptp.payroll_id = l_payroll_id
190: AND ptp.time_period_id = pai_emp.action_information16
206: SELECT distinct pai_emp.action_information15 organization
207: ,ppf.payroll_id
208: FROM per_time_periods ptp
209: ,pay_action_information pai_emp
210: ,pay_assignment_actions paa1
211: ,pay_action_interlocks lck
212: ,pay_payroll_actions ppa1
213: ,pay_all_payrolls_f ppf
214: WHERE ptp.time_period_id = pai_emp.action_information16
239: SELECT distinct pai_emp.action_information15 organization
240: ,ppf.payroll_id
241: FROM per_time_periods ptp
242: ,pay_action_information pai_emp
243: ,pay_assignment_actions paa1
244: ,pay_payroll_actions ppa1
245: ,pay_all_payrolls_f ppf
246: WHERE ptp.time_period_id = pai_emp.action_information16
247: AND pai_emp.action_context_type = 'AAP'
273: SELECT distinct pai_emp.action_information15 organization
274: ,ppf.payroll_id
275: FROM per_time_periods ptp
276: ,pay_action_information pai_emp
277: ,pay_assignment_actions paa1
278: ,pay_action_interlocks lck
279: ,pay_payroll_actions ppa1
280: ,pay_all_payrolls_f ppf
281: WHERE ptp.time_period_id = pai_emp.action_information16
296: SELECT distinct pai_emp.action_information15 organization
297: ,ppf.payroll_id
298: FROM per_time_periods ptp
299: ,pay_action_information pai_emp
300: ,pay_assignment_actions paa1
301: ,pay_payroll_actions ppa1
302: ,pay_all_payrolls_f ppf
303: WHERE ptp.time_period_id = pai_emp.action_information16
304: AND pai_emp.action_context_type = 'AAP'
319: SELECT distinct pai_emp.action_information15 organization
320: ,ppf.payroll_id
321: FROM per_time_periods ptp
322: ,pay_action_information pai_emp
323: ,pay_assignment_actions paa1
324: ,pay_action_interlocks lck
325: ,pay_payroll_actions ppa1
326: ,pay_all_payrolls_f ppf
327: WHERE ptp.time_period_id = pai_emp.action_information16
348: /*cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
349: SELECT distinct ppf.payroll_id
350: FROM per_time_periods ptp
351: ,pay_action_information pai_emp
352: ,pay_assignment_actions paa1
353: ,pay_action_interlocks lck
354: ,pay_payroll_actions ppa1
355: ,pay_all_payrolls_f ppf
356: WHERE ptp.time_period_id = pai_emp.action_information16
380: cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
381: SELECT distinct ppf.payroll_id
382: FROM per_time_periods ptp
383: ,pay_action_information pai_emp
384: ,pay_assignment_actions paa1
385: ,pay_payroll_actions ppa1
386: ,pay_all_payrolls_f ppf
387: WHERE ptp.time_period_id = pai_emp.action_information16
388: AND pai_emp.action_context_type = 'AAP'
412: cursor csr_org_only_distinct_pyrl(l_organization_id number , l_effective_date date) is
413: SELECT distinct ppf.payroll_id
414: FROM per_time_periods ptp
415: ,pay_action_information pai_emp
416: ,pay_assignment_actions paa1
417: ,pay_action_interlocks lck
418: ,pay_payroll_actions ppa1
419: ,pay_all_payrolls_f ppf
420: WHERE ptp.time_period_id = pai_emp.action_information16
460: ,nvl(pai_emp1.action_information4,0) ytd_deduction
461: FROM per_time_periods ptp
462: ,pay_action_information pai_emp
463: ,pay_action_information pai_emp1
464: ,pay_assignment_actions paa1
465: ,pay_action_interlocks lck
466: ,pay_payroll_actions ppa1
467: ,pay_all_payrolls_f ppf
468: WHERE ptp.payroll_id = l_payroll_id
504: ,nvl(pai_emp1.action_information4,0) ytd_deduction
505: FROM per_time_periods ptp
506: ,pay_action_information pai_emp
507: ,pay_action_information pai_emp1
508: ,pay_assignment_actions paa1
509: ,pay_payroll_actions ppa1
510: ,pay_all_payrolls_f ppf
511: WHERE ptp.payroll_id = l_payroll_id
512: AND ptp.time_period_id = pai_emp.action_information16
537: ,pay_v.numeric_value earn_value
538: ,pai_ele.action_information7
539: FROM pay_action_information pai_ele
540: ,pay_emea_paymnts_action_info_v pay_v
541: ,pay_assignment_actions paa
542: WHERE paa.assignment_action_id = l_assact_id
543: AND paa.payroll_action_id = pai_ele.action_context_id
544: AND pai_ele.action_context_type = 'PA'
545: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
553: ,pet.result_value earn_value
554: ,pai.action_information7
555: FROM
556: pay_action_interlocks lck,
557: pay_assignment_actions paa1,
558: pay_assignment_actions paa2,
559: pay_payroll_actions ppa,
560: pay_action_information pai,
561: pay_emea_payment_values_v pet
554: ,pai.action_information7
555: FROM
556: pay_action_interlocks lck,
557: pay_assignment_actions paa1,
558: pay_assignment_actions paa2,
559: pay_payroll_actions ppa,
560: pay_action_information pai,
561: pay_emea_payment_values_v pet
562: WHERE
585: ,pay_v.numeric_value ded_value
586: ,pai_ele.action_information7
587: FROM pay_action_information pai_ele
588: ,pay_emea_paymnts_action_info_v pay_v
589: ,pay_assignment_actions paa
590: WHERE paa.assignment_action_id= l_assact_id
591: AND paa.payroll_action_id = pai_ele.action_context_id
592: AND pai_ele.action_context_type = 'PA'
593: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
601: ,pet.result_value ded_value
602: ,pai.action_information7
603: FROM
604: pay_action_interlocks lck,
605: pay_assignment_actions paa1,
606: pay_assignment_actions paa2,
607: pay_payroll_actions ppa,
608: pay_action_information pai,
609: pay_emea_payment_values_v pet
602: ,pai.action_information7
603: FROM
604: pay_action_interlocks lck,
605: pay_assignment_actions paa1,
606: pay_assignment_actions paa2,
607: pay_payroll_actions ppa,
608: pay_action_information pai,
609: pay_emea_payment_values_v pet
610: WHERE
632: ,pen.segment2 branch_name
633: ,pen.segment4 account_number
634: ,pen.value pay_amount
635: ,pen.action_context_id
636: ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
637: FROM pay_emp_net_dist_action_info_v pen
638: ,pay_action_interlocks pai
639: ,pay_assignment_actions paa
640: ,pay_payroll_actions ppa
635: ,pen.action_context_id
636: ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
637: FROM pay_emp_net_dist_action_info_v pen
638: ,pay_action_interlocks pai
639: ,pay_assignment_actions paa
640: ,pay_payroll_actions ppa
641: ,pay_pre_payments ppp
642: WHERE pen.action_context_id = l_assact_id
643: AND pen.action_context_id = pai.locking_action_id
655: /*Modifying this cursor for bug 7307152
656: SELECT sum(pay_v.numeric_value)
657: FROM pay_action_information pai_ele
658: ,pay_emea_paymnts_action_info_v pay_v
659: ,pay_assignment_actions paa
660: WHERE paa.payroll_action_id = pai_ele.action_context_id
661: AND pai_ele.action_context_type = 'PA'
662: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
663: AND pai_ele.action_information7 IN ('E')
666: AND pay_v.payment_type NOT IN ('F')
667: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
668: FROM per_time_periods ptp
669: ,pay_action_information pai_emp
670: ,pay_assignment_actions paa1
671: ,pay_action_interlocks lck
672: ,pay_payroll_actions ppa1
673: WHERE ptp.payroll_id = l_payroll_id
674: AND ptp.time_period_id = pai_emp.action_information16
693: AND ptp.end_date = l_effective_date);*/
694: SELECT sum(pet.result_value)
695: FROM pay_action_information pai_ele,
696: pay_emea_payment_values_v pet,
697: pay_assignment_actions paa
698: WHERE paa.payroll_action_id = pai_ele.action_context_id
699: AND pai_ele.action_context_type = 'PA'
700: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
701: AND pai_ele.action_information7 IN ('E')
705: AND pet.input_value_id = pai_ele.action_information3
706: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
707: FROM per_time_periods ptp
708: ,pay_action_information pai_emp
709: ,pay_assignment_actions paa1
710: ,pay_action_interlocks lck
711: ,pay_payroll_actions ppa1
712: WHERE ptp.payroll_id = l_payroll_id
713: AND ptp.time_period_id = pai_emp.action_information16
731: AND paa1.action_status IN ('C','S')
732: AND ptp.end_date = l_effective_date);
733: --CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
734: /*SELECT sum(pay_v.numeric_value)
735: FROM pay_assignment_actions paa
736: ,per_time_periods ptp
737: ,pay_assignment_actions paa1
738: ,pay_action_interlocks lck
739: ,pay_payroll_actions ppa1
733: --CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
734: /*SELECT sum(pay_v.numeric_value)
735: FROM pay_assignment_actions paa
736: ,per_time_periods ptp
737: ,pay_assignment_actions paa1
738: ,pay_action_interlocks lck
739: ,pay_payroll_actions ppa1
740: ,pay_action_information pai_ele
741: ,pay_action_information pai_emp
766: /*
767: select SUM(pay_v.numeric_value)
768: from pay_action_information pai_ele
769: ,pay_emea_paymnts_action_info_v pay_v
770: ,pay_assignment_actions paa
771: where paa.payroll_action_id = pai_ele.action_context_id
772: and pai_ele.action_context_type = 'PA'
773: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
774: and pai_ele.action_information7 in ('E')
778: and paa.assignment_action_id in (
779: select pai_emp.action_context_id arch_assact
780: from per_time_periods ptp
781: ,pay_action_information pai_emp
782: ,pay_assignment_actions paa1
783: ,pay_action_interlocks lck
784: ,pay_payroll_actions ppa1
785: where ptp.payroll_id = l_payroll_id
786: and ptp.time_period_id = pai_emp.action_information16
800: CURSOR csr_SEOO_SPLIT_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
801: select pai_emp.action_context_id arch_assact
802: from per_time_periods ptp
803: ,pay_action_information pai_emp
804: ,pay_assignment_actions paa1
805: ,pay_action_interlocks lck
806: ,pay_payroll_actions ppa1
807: where ptp.payroll_id = l_payroll_id
808: and ptp.time_period_id = pai_emp.action_information16
822: /*Modifying this cursor for bug 7307152
823: select SUM(pay_v.numeric_value)
824: from pay_action_information pai_ele
825: ,pay_emea_paymnts_action_info_v pay_v
826: ,pay_assignment_actions paa
827: where paa.payroll_action_id = pai_ele.action_context_id
828: and pai_ele.action_context_type = 'PA'
829: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
830: and pai_ele.action_information7 in ('E')
834: and paa.assignment_action_id = l_arch_assact;*/
835: select SUM(pet.result_value)
836: from pay_action_information pai_ele
837: ,pay_emea_payment_values_v pet
838: ,pay_assignment_actions paa
839: where paa.payroll_action_id = pai_ele.action_context_id
840: and pai_ele.action_context_type = 'PA'
841: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
842: and pai_ele.action_information7 in ('E')
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')
863: AND pay_v.payment_type NOT IN ('F')
864: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
865: FROM per_time_periods ptp
866: ,pay_action_information pai_emp
867: ,pay_assignment_actions paa1
868: ,pay_action_interlocks lck
869: ,pay_payroll_actions ppa1
870: WHERE ptp.payroll_id = l_payroll_id
871: AND ptp.time_period_id = pai_emp.action_information16
890: AND ptp.end_date = l_effective_date);*/
891: SELECT sum(pet.result_value)
892: FROM pay_action_information pai_ele,
893: pay_emea_payment_values_v pet,
894: pay_assignment_actions paa
895: WHERE paa.payroll_action_id = pai_ele.action_context_id
896: AND pai_ele.action_context_type = 'PA'
897: AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
898: AND pai_ele.action_information7 IN ('D')
902: AND pet.input_value_id = pai_ele.action_information3
903: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
904: FROM per_time_periods ptp
905: ,pay_action_information pai_emp
906: ,pay_assignment_actions paa1
907: ,pay_action_interlocks lck
908: ,pay_payroll_actions ppa1
909: WHERE ptp.payroll_id = l_payroll_id
910: AND ptp.time_period_id = pai_emp.action_information16
928: AND paa1.action_status IN ('C','S')
929: AND ptp.end_date = l_effective_date);
930: --CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
931: /*SELECT sum(pay_v.numeric_value)
932: FROM pay_assignment_actions paa
933: ,per_time_periods ptp
934: ,pay_assignment_actions paa1
935: ,pay_action_interlocks lck
936: ,pay_payroll_actions ppa1
930: --CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
931: /*SELECT sum(pay_v.numeric_value)
932: FROM pay_assignment_actions paa
933: ,per_time_periods ptp
934: ,pay_assignment_actions paa1
935: ,pay_action_interlocks lck
936: ,pay_payroll_actions ppa1
937: ,pay_action_information pai_ele
938: ,pay_action_information pai_emp
962: /*
963: select SUM(pay_v.numeric_value)
964: from pay_action_information pai_ele
965: ,pay_emea_paymnts_action_info_v pay_v
966: ,pay_assignment_actions paa
967: where paa.payroll_action_id = pai_ele.action_context_id
968: and pai_ele.action_context_type = 'PA'
969: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
970: and pai_ele.action_information7 in ('D')
974: and paa.assignment_action_id in (
975: select pai_emp.action_context_id arch_assact
976: from per_time_periods ptp
977: ,pay_action_information pai_emp
978: ,pay_assignment_actions paa1
979: ,pay_action_interlocks lck
980: ,pay_payroll_actions ppa1
981: where ptp.payroll_id = l_payroll_id
982: and ptp.time_period_id = pai_emp.action_information16
995: CURSOR csr_SDOO_SPLIT_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
996: select pai_emp.action_context_id arch_assact
997: from per_time_periods ptp
998: ,pay_action_information pai_emp
999: ,pay_assignment_actions paa1
1000: ,pay_action_interlocks lck
1001: ,pay_payroll_actions ppa1
1002: where ptp.payroll_id = l_payroll_id
1003: and ptp.time_period_id = pai_emp.action_information16
1017: /*Modifying this cursor for bug 7307152
1018: select SUM(pay_v.numeric_value)
1019: from pay_action_information pai_ele
1020: ,pay_emea_paymnts_action_info_v pay_v
1021: ,pay_assignment_actions paa
1022: where paa.payroll_action_id = pai_ele.action_context_id
1023: and pai_ele.action_context_type = 'PA'
1024: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
1025: and pai_ele.action_information7 in ('D')
1029: and paa.assignment_action_id = l_arch_assact;*/
1030: select SUM(pet.result_value)
1031: from pay_action_information pai_ele
1032: ,pay_emea_payment_values_v pet
1033: ,pay_assignment_actions paa
1034: where paa.payroll_action_id = pai_ele.action_context_id
1035: and pai_ele.action_context_type = 'PA'
1036: and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
1037: and pai_ele.action_information7 in ('D')