[Home] [Help]
225: Hire indicator flag
226: 4-NOV-2005 pganguly 115.79 Fixed bug# 4033041. Commented out
227: archiver code for T4_BOX50.
228: 3-MAR-2006 ssmukher 115.80 Fixed Bug #5041252 .Removed the
229: per_all_assignments_f table check
230: from the select statement in the
231: procedure eoy_archive_data to fetch
232: the CPP/QPP exempt flag
233: from pay_ca_emp_prov_tax_info_f.
562: SELECT ASG.person_id person_id,
563: ASG.assignment_id assignment_id,
564: ASG.effective_end_date effective_end_date
565: FROM
566: per_all_assignments_f ASG
567: WHERE
568: ASG.business_group_id = l_bus_group_id AND
569: asg.assignment_type = 'E' AND
570: ASG.person_id between stperson and endperson AND
591: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
592: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
593: DISTINCT ASG.person_id person_id
594: FROM
595: per_all_assignments_f ASG,
596: pay_assignment_actions paa,
597: pay_payroll_actions ppa
598:
599: WHERE ppa.effective_date between l_period_start
614: --Original query:
615: /* SELECT DISTINCT
616: ASG.person_id person_id
617: FROM
618: per_all_assignments_f ASG,
619: pay_all_payrolls_f PPY
620: WHERE exists
621: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
622: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
647: SELECT ASG.person_id person_id,
648: ASG.assignment_id assignment_id,
649: to_number(SCL.segment1) tax_unit_id,
650: ASG.effective_end_date effective_end_date
651: FROM per_all_assignments_f ASG,
652: hr_soft_coding_keyflex SCL,
653: pay_all_payrolls_f PPY
654: WHERE ASG.business_group_id + 0 = l_bus_group_id
655: AND ASG.person_id between stperson and endperson
663: /* Get the primary assignment for the given person_id */
664:
665: CURSOR c_get_asg_id (p_person_id number) IS
666: SELECT assignment_id
667: from per_all_assignments_f paf
668: where person_id = p_person_id
669: and primary_flag = 'Y'
670: and paf.effective_start_date <= l_period_end
671: and paf.effective_end_date >= l_period_start
679: INDEX (asg PER_ASSIGNMENTS_F_N12)
680: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
681: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
682: paa.assignment_action_id
683: from per_all_assignments_f paf,
684: pay_assignment_actions paa,
685: pay_payroll_actions ppa,
686: pay_action_classifications pac
687: where paf.person_id = p_person_id
706:
707: /* Original Query:
708: select paa.assignment_action_id
709: from pay_assignment_actions paa,
710: per_all_assignments_f paf,
711: pay_payroll_actions ppa,
712: pay_action_classifications pac
713: where paf.person_id = p_person_id
714: and paa.assignment_id = paf.assignment_id
2051: PEOPLE.middle_names,
2052: ASSIGN.organization_id,
2053: ASSIGN.location_id
2054: FROM
2055: per_all_assignments_f ASSIGN,
2056: per_all_people_f PEOPLE
2057: WHERE ASSIGN.assignment_id = p_asgid
2058: and l_date_earned BETWEEN ASSIGN.effective_start_date
2059: AND ASSIGN.effective_end_date
2176: p_jurisdiction varchar2) IS
2177: select /*+ Ordered */
2178: paa.assignment_action_id,
2179: ppa.date_earned
2180: from per_all_assignments_f paf,
2181: pay_assignment_actions paa,
2182: pay_payroll_actions ppa,
2183: pay_action_classifications pac,
2184: pay_action_contexts pac1,
2213: cp_jurisdiction varchar2) is
2214: select paa.assignment_action_id,
2215: ppa.date_earned
2216: from pay_assignment_actions paa,
2217: per_all_assignments_f paf,
2218: per_all_people_f ppf,
2219: pay_payroll_actions ppa,
2220: pay_action_classifications pac,
2221: pay_action_contexts pac1,
2387: INTO
2388: l_aaid
2389: FROM
2390: pay_assignment_actions paa1,
2391: per_all_assignments_f paf2
2392: WHERE
2393: paa1.assignment_id = paf2.assignment_id
2394: and paa1.tax_unit_id = l_tax_unit_id
2395: and (paa1.action_sequence , paf2.person_id) =
2396: (SELECT MAX(paa.action_sequence), paf.person_id
2397: FROM pay_action_classifications pac,
2398: pay_payroll_actions ppa,
2399: pay_assignment_actions paa,
2400: per_all_assignments_f paf1,
2401: per_all_assignments_f paf
2402: WHERE paf.assignment_id = l_asgid
2403: AND paf1.person_id = paf.person_id
2404: AND paa.tax_unit_id = l_tax_unit_id
2397: FROM pay_action_classifications pac,
2398: pay_payroll_actions ppa,
2399: pay_assignment_actions paa,
2400: per_all_assignments_f paf1,
2401: per_all_assignments_f paf
2402: WHERE paf.assignment_id = l_asgid
2403: AND paf1.person_id = paf.person_id
2404: AND paa.tax_unit_id = l_tax_unit_id
2405: AND paa.assignment_id = paf1.assignment_id
3092: end if;
3093: end if;
3094:
3095: BEGIN
3096: /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
3097: SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
3098: decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
3099: INTO l_cpp_exempt_flag,
3100: l_ei_exempt_flag
3793: distinct asg.person_id
3794: from pay_all_payrolls_f ppy,
3795: pay_payroll_actions ppa,
3796: pay_assignment_actions paa,
3797: per_all_assignments_f asg,
3798: pay_payroll_actions ppa1
3799: where ppa1.payroll_action_id = :payroll_action_id
3800: and ppa.effective_date between
3801: fnd_date.canonical_to_date('''||