[Home] [Help]
133: not archived by YEPP then return
134: amendment flag 'Y'.
135: 04-DEC-2003 PGanguly 115.50 Fixed the bug# 3298050. Changed the
136: cursor c_eoy_gre so that it checks
137: for data in the pay_assignment_actions
138: and pay_payroll_actions via EXIST
139: clause rather than direct join. Also
140: removed the tax_unit_id from the
141: select clause as this cursor selects
511: that are created. */
512:
513: l_payroll_action_created boolean := false;
514: l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
515: l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
516:
517: /* Variable holding the balance to be tested. */
518:
519: l_defined_balance_id pay_defined_balances.defined_balance_id%type;
550: l_quarter_end date;
551: l_year_start date;
552: l_year_end date;
553: lockingactid number;
554: l_primary_asg pay_assignment_actions.assignment_id%type;
555: l_legislative_parameters varchar2(240);
556:
557:
558: /* For Year End Preprocess we have to archive the assignments
570: ASG.person_id between stperson and endperson AND
571: EXISTS
572: (SELECT 1
573: FROM pay_payroll_actions ppa,
574: pay_assignment_actions paa
575: WHERE
576: ppa.business_group_id = l_bus_group_id AND
577: ppa.payroll_action_id = paa.payroll_action_id AND
578: ppa.action_type in ('R','Q','V','B','I') AND
587:
588: CURSOR c_eoy_gre IS
589: SELECT /*+ Ordered
590: INDEX (asg PER_ASSIGNMENTS_F_N12)
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,
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
600: and l_period_end
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) */
623: /* 'x'
624: from pay_payroll_actions ppa,
625: pay_assignment_actions paa
621: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
622: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
623: /* 'x'
624: from pay_payroll_actions ppa,
625: pay_assignment_actions paa
626: where ppa.effective_date between l_period_start
627: and l_period_end
628: and ppa.action_type in ('R','Q','V','B','I')
629: and ppa.action_status = 'C'
676: on person_id. Bug#3267520 */
677: CURSOR c_get_latest_asg(p_person_id number ) IS
678: select /*+ Ordered
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,
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
688: and paa.assignment_id = paf.assignment_id
705:
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
920: (ln_no_gross_earnings <> 0)) then
921:
922: /* Create the assignment action to archive T4 details */
923:
924: select pay_assignment_actions_s.nextval
925: into lockingactid
926: from dual;
927:
928: /* Insert into pay_assignment_actions. */
924: select pay_assignment_actions_s.nextval
925: into lockingactid
926: from dual;
927:
928: /* Insert into pay_assignment_actions. */
929:
930: hr_utility.trace('creating assignment action');
931:
932: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
937: to do an additional check against the assignment table */
938:
939: hr_utility.trace('updating assignment action');
940:
941: update pay_assignment_actions aa
942: set aa.serial_number = to_char(l_person_id)
943: where aa.assignment_action_id = lockingactid;
944:
945: hr_utility.trace('Created Assignment action'||
1858: l_step number := 0;
1859:
1860: cursor c_get_min_chunk is
1861: select min(paa.chunk_number)
1862: from pay_assignment_actions paa
1863: where paa.payroll_action_id = p_payroll_action_id;
1864: begin
1865: open c_get_min_chunk;
1866: fetch c_get_min_chunk into g_min_chunk;
1893:
1894: PROCEDURE eoy_archive_data(p_assactid in number,
1895: p_effective_date in date) IS
1896:
1897: l_aaid pay_assignment_actions.assignment_action_id%type;
1898: l_aaseq pay_assignment_actions.action_sequence%type;
1899: l_asgid pay_assignment_actions.assignment_id%type;
1900: l_date_earned date;
1901: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1894: PROCEDURE eoy_archive_data(p_assactid in number,
1895: p_effective_date in date) IS
1896:
1897: l_aaid pay_assignment_actions.assignment_action_id%type;
1898: l_aaseq pay_assignment_actions.action_sequence%type;
1899: l_asgid pay_assignment_actions.assignment_id%type;
1900: l_date_earned date;
1901: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1902: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1895: p_effective_date in date) IS
1896:
1897: l_aaid pay_assignment_actions.assignment_action_id%type;
1898: l_aaseq pay_assignment_actions.action_sequence%type;
1899: l_asgid pay_assignment_actions.assignment_id%type;
1900: l_date_earned date;
1901: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1902: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1903: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1900: l_date_earned date;
1901: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1902: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1903: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1904: l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1905: l_business_group_id pay_assignment_actions.tax_unit_id%type;
1906: l_year_start date;
1907: l_year_end date;
1908: l_context_no number := 60;
1901: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
1902: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
1903: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
1904: l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1905: l_business_group_id pay_assignment_actions.tax_unit_id%type;
1906: l_year_start date;
1907: l_year_end date;
1908: l_context_no number := 60;
1909: l_count number := 0;
2150: and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
2151:
2152: CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2153: select ppa.report_type
2154: from pay_payroll_actions ppa,pay_assignment_actions paa
2155: where paa.assignment_action_id = cp_locked_actid
2156: and ppa.payroll_action_id = paa.payroll_action_id;
2157:
2158: CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) 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,
2185: ff_contexts fc
2212: cp_tax_unit_id number,
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,
2295: l_chunk,
2296: l_payroll_action_id,
2297: lv_serial_number
2298: FROM
2299: pay_assignment_actions aa
2300: WHERE
2301: aa.assignment_action_id = p_assactid;
2302:
2303: l_year_start := trunc(p_effective_date, 'Y');
2386: paa1.assignment_action_id
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
3785: if l_eoy_tax_unit_id <> -99999 then
3786:
3787: sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
3788: PPA PAY_PAYROLL_ACTIONS_N51,
3789: PAA PAY_ASSIGNMENT_ACTIONS_N50,
3790: ASG PER_ASSIGNMENTS_F_PK,
3791: PPA1 PAY_PAYROLL_ACTIONS_PK)
3792: USE_NL(PPY, PPA, PAA, ASG, PPA1) */
3793: distinct asg.person_id
3792: USE_NL(PPY, PPA, PAA, ASG, PPA1) */
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