[Home] [Help]
159: /* Modifyig the cursor for performance issue for Bug 7632337 */
160: /*CURSOR csr_get_orgs_for_payroll (l_payroll_id number , l_effective_date date) is
161: SELECT distinct pai_emp.action_information15 organization
162: ,ppf.payroll_id
163: FROM per_time_periods ptp
164: ,pay_action_information pai_emp
165: ,pay_assignment_actions paa1
166: ,pay_action_interlocks lck
167: ,pay_payroll_actions ppa1
182:
183: CURSOR csr_get_orgs_for_payroll (l_payroll_id number , l_effective_date date) is
184: SELECT distinct pai_emp.action_information15 organization
185: ,ppf.payroll_id
186: FROM per_time_periods ptp
187: ,pay_action_information pai_emp
188: ,pay_assignment_actions paa1
189: ,pay_payroll_actions ppa1
190: ,pay_all_payrolls_f ppf
205: /* Modifyig the cursor for performance issue for Bug 7632337 */
206: /*cursor csr_condition_one(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
207: SELECT distinct pai_emp.action_information15 organization
208: ,ppf.payroll_id
209: FROM per_time_periods ptp
210: ,pay_action_information pai_emp
211: ,pay_assignment_actions paa1
212: ,pay_action_interlocks lck
213: ,pay_payroll_actions ppa1
238: from sys.dual) ; */
239: cursor csr_condition_one(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
240: SELECT distinct pai_emp.action_information15 organization
241: ,ppf.payroll_id
242: FROM per_time_periods ptp
243: ,pay_action_information pai_emp
244: ,pay_assignment_actions paa1
245: ,pay_payroll_actions ppa1
246: ,pay_all_payrolls_f ppf
271: /* Modifyig the cursor for performance issue for Bug 7632337 */
272: /*cursor csr_condition_two(l_organization_id number , l_effective_date date) is
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
293: AND pai_emp.action_information2 = l_organization_id; */
294: cursor csr_condition_two(l_organization_id number , l_effective_date date) is
295: SELECT distinct pai_emp.action_information15 organization
296: ,ppf.payroll_id
297: FROM per_time_periods ptp
298: ,pay_action_information pai_emp
299: ,pay_assignment_actions paa1
300: ,pay_payroll_actions ppa1
301: ,pay_all_payrolls_f ppf
316: /* It looks like dummy cursor as it has conditions 1=2 */
317: /*cursor csr_condition_three(l_effective_date date) is
318: SELECT distinct pai_emp.action_information15 organization
319: ,ppf.payroll_id
320: FROM per_time_periods ptp
321: ,pay_action_information pai_emp
322: ,pay_assignment_actions paa1
323: ,pay_action_interlocks lck
324: ,pay_payroll_actions ppa1
344: /* CURSOR TO GET PAYROLL IDS DISTINCT */
345: /* Modifyig the cursor for performance issue for Bug 7632337 */
346: /*cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
347: SELECT distinct ppf.payroll_id
348: FROM per_time_periods ptp
349: ,pay_action_information pai_emp
350: ,pay_assignment_actions paa1
351: ,pay_action_interlocks lck
352: ,pay_payroll_actions ppa1
376: union select to_char(nvl(l_organization_id,l_parent_id))
377: from sys.dual) ; */
378: cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
379: SELECT distinct ppf.payroll_id
380: FROM per_time_periods ptp
381: ,pay_action_information pai_emp
382: ,pay_assignment_actions paa1
383: ,pay_payroll_actions ppa1
384: ,pay_all_payrolls_f ppf
408: from sys.dual) ;
409: /* CURSOR TO GET PAYROLL IDS DISTINCT FOR ORGANIZATION */
410: cursor csr_org_only_distinct_pyrl(l_organization_id number , l_effective_date date) is
411: SELECT distinct ppf.payroll_id
412: FROM per_time_periods ptp
413: ,pay_action_information pai_emp
414: ,pay_assignment_actions paa1
415: ,pay_action_interlocks lck
416: ,pay_payroll_actions ppa1
455: ,pai_emp1.action_information11
456: ,ppf.payroll_name
457: ,nvl(pai_emp1.action_information13,0) ytd_earning
458: ,nvl(pai_emp1.action_information4,0) ytd_deduction
459: FROM per_time_periods ptp
460: ,pay_action_information pai_emp
461: ,pay_action_information pai_emp1
462: ,pay_assignment_actions paa1
463: ,pay_action_interlocks lck
499: ,pai_emp1.action_information11
500: ,ppf.payroll_name
501: ,nvl(pai_emp1.action_information13,0) ytd_earning
502: ,nvl(pai_emp1.action_information4,0) ytd_deduction
503: FROM per_time_periods ptp
504: ,pay_action_information pai_emp
505: ,pay_action_information pai_emp1
506: ,pay_assignment_actions paa1
507: ,pay_payroll_actions ppa1
659: AND pay_v.action_context_id = paa.assignment_action_id
660: AND pay_v.narrative = pai_ele.action_information4
661: AND pay_v.payment_type NOT IN ('F')
662: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
663: FROM per_time_periods ptp
664: ,pay_action_information pai_emp
665: ,pay_assignment_actions paa1
666: ,pay_action_interlocks lck
667: ,pay_payroll_actions ppa1
690: AND pay_v.action_context_id = paa.assignment_action_id
691: AND pay_v.narrative = pai_ele.action_information4
692: AND pay_v.payment_type NOT IN ('F')
693: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
694: FROM per_time_periods ptp
695: ,pay_action_information pai_emp
696: ,pay_assignment_actions paa1
697: ,pay_action_interlocks lck
698: ,pay_payroll_actions ppa1
721: CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
722: --*******Fixed during performance bugs*****
723: SELECT sum(pay_v.numeric_value)
724: FROM pay_assignment_actions paa
725: ,per_time_periods ptp
726: ,pay_assignment_actions paa1
727: ,pay_action_interlocks lck
728: ,pay_payroll_actions ppa1
729: ,pay_action_information pai_ele
754: */
755:
756: CURSOR csr_seoo_split_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
757: select pai_emp.action_context_id arch_assact
758: from per_time_periods ptp
759: ,pay_action_information pai_emp
760: ,pay_assignment_actions paa1
761: ,pay_action_interlocks lck
762: ,pay_payroll_actions ppa1
805: AND pay_v.action_context_id = paa.assignment_action_id
806: AND pay_v.narrative = pai_ele.action_information4
807: AND pay_v.payment_type NOT IN ('F')
808: AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
809: FROM per_time_periods ptp
810: ,pay_action_information pai_emp
811: ,pay_assignment_actions paa1
812: ,pay_action_interlocks lck
813: ,pay_payroll_actions ppa1
836: CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
837: --******* Fixed during performance bugs*******
838: SELECT sum(pay_v.numeric_value)
839: FROM pay_assignment_actions paa
840: ,per_time_periods ptp
841: ,pay_assignment_actions paa1
842: ,pay_action_interlocks lck
843: ,pay_payroll_actions ppa1
844: ,pay_action_information pai_ele
869: */
870:
871: CURSOR csr_sdoo_split_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
872: select pai_emp.action_context_id arch_assact
873: from per_time_periods ptp
874: ,pay_action_information pai_emp
875: ,pay_assignment_actions paa1
876: ,pay_action_interlocks lck
877: ,pay_payroll_actions ppa1