[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
578: that are created. */
579:
580: l_payroll_action_created boolean := false;
581: l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
582: l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
583:
584: /* Variable holding the balance to be tested. */
585:
586: l_defined_balance_id pay_defined_balances.defined_balance_id%type;
625: l_quarter_end date;
626: l_year_start date;
627: l_year_end date;
628: lockingactid number;
629: l_primary_asg pay_assignment_actions.assignment_id%type;
630: l_legislative_parameters varchar2(240);
631:
632:
633: /* For Year End Preprocess we have to archive the assignments
645: ASG.person_id between stperson and endperson AND
646: EXISTS
647: (SELECT 1
648: FROM pay_payroll_actions ppa,
649: pay_assignment_actions paa
650: WHERE
651: ppa.business_group_id = l_bus_group_id AND
652: ppa.payroll_action_id = paa.payroll_action_id AND
653: ppa.action_type in ('R','Q','V','B','I') AND
662:
663: CURSOR c_eoy_gre IS
664: SELECT /*+ Ordered
665: INDEX (asg PER_ASSIGNMENTS_F_N12)
666: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
667: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
668: DISTINCT ASG.person_id person_id
669: FROM
670: per_all_assignments_f ASG,
667: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
668: DISTINCT ASG.person_id person_id
669: FROM
670: per_all_assignments_f ASG,
671: pay_assignment_actions paa,
672: pay_payroll_actions ppa
673:
674: WHERE ppa.effective_date between l_period_start
675: and l_period_end
694: SELECT /*+ leading(ppr,asg,paa) */ --Modified for bug 13615110
695: DISTINCT ASG.person_id person_id
696: FROM
697: per_all_assignments_f ASG,
698: pay_assignment_actions paa,
699: pay_payroll_actions ppa,
700: pay_population_ranges ppr
701: WHERE ppa.effective_date between l_period_start
702: and l_period_end
722: FROM
723: per_all_assignments_f ASG,
724: pay_all_payrolls_f PPY
725: WHERE exists
726: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
727: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
728: /* 'x'
729: from pay_payroll_actions ppa,
730: pay_assignment_actions paa
726: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
727: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
728: /* 'x'
729: from pay_payroll_actions ppa,
730: pay_assignment_actions paa
731: where ppa.effective_date between l_period_start
732: and l_period_end
733: and ppa.action_type in ('R','Q','V','B','I')
734: and ppa.action_status = 'C'
781: on person_id. Bug#3267520 */
782: CURSOR c_get_latest_asg(p_person_id number ) IS
783: select /*+ Ordered
784: INDEX (asg PER_ASSIGNMENTS_F_N12)
785: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
786: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
787: paa.assignment_action_id
788: from per_all_assignments_f paf,
789: pay_assignment_actions paa,
785: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
786: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
787: paa.assignment_action_id
788: from per_all_assignments_f paf,
789: pay_assignment_actions paa,
790: pay_payroll_actions ppa,
791: pay_action_classifications pac
792: where paf.person_id = p_person_id
793: and paa.assignment_id = paf.assignment_id
810:
811:
812: /* Original Query:
813: select paa.assignment_action_id
814: from pay_assignment_actions paa,
815: per_all_assignments_f paf,
816: pay_payroll_actions ppa,
817: pay_action_classifications pac
818: where paf.person_id = p_person_id
1078: (ln_no_gross_earnings <> 0)) then
1079:
1080: /* Create the assignment action to archive T4 details */
1081:
1082: select pay_assignment_actions_s.nextval
1083: into lockingactid
1084: from dual;
1085:
1086: /* Insert into pay_assignment_actions. */
1082: select pay_assignment_actions_s.nextval
1083: into lockingactid
1084: from dual;
1085:
1086: /* Insert into pay_assignment_actions. */
1087:
1088: hr_utility.trace('creating assignment action');
1089:
1090: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1095: to do an additional check against the assignment table */
1096:
1097: hr_utility.trace('updating assignment action');
1098:
1099: update pay_assignment_actions aa
1100: set aa.serial_number = to_char(l_person_id)
1101: where aa.assignment_action_id = lockingactid;
1102:
1103: hr_utility.trace('Created Assignment action'||
2076: l_step number := 0;
2077:
2078: cursor c_get_min_chunk is
2079: select min(paa.chunk_number)
2080: from pay_assignment_actions paa
2081: where paa.payroll_action_id = p_payroll_action_id;
2082: begin
2083: open c_get_min_chunk;
2084: fetch c_get_min_chunk into g_min_chunk;
2111:
2112: PROCEDURE eoy_archive_data(p_assactid in number,
2113: p_effective_date in date) IS
2114:
2115: l_aaid pay_assignment_actions.assignment_action_id%type;
2116: l_aaseq pay_assignment_actions.action_sequence%type;
2117: l_asgid pay_assignment_actions.assignment_id%type;
2118: l_date_earned date;
2119: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2112: PROCEDURE eoy_archive_data(p_assactid in number,
2113: p_effective_date in date) IS
2114:
2115: l_aaid pay_assignment_actions.assignment_action_id%type;
2116: l_aaseq pay_assignment_actions.action_sequence%type;
2117: l_asgid pay_assignment_actions.assignment_id%type;
2118: l_date_earned date;
2119: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2120: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
2113: p_effective_date in date) IS
2114:
2115: l_aaid pay_assignment_actions.assignment_action_id%type;
2116: l_aaseq pay_assignment_actions.action_sequence%type;
2117: l_asgid pay_assignment_actions.assignment_id%type;
2118: l_date_earned date;
2119: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2120: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
2121: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
2118: l_date_earned date;
2119: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2120: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
2121: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
2122: l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2123: l_business_group_id pay_assignment_actions.tax_unit_id%type;
2124: l_year_start date;
2125: l_year_end date;
2126: l_context_no number := 60;
2119: l_user_entity_name_tab pay_ca_eoy_archive.char240_data_type_table;
2120: l_balance_type_tab pay_ca_eoy_archive.char240_data_type_table;
2121: l_user_entity_value_tab pay_ca_eoy_archive.char240_data_type_table;
2122: l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2123: l_business_group_id pay_assignment_actions.tax_unit_id%type;
2124: l_year_start date;
2125: l_year_end date;
2126: l_context_no number := 60;
2127: l_count number := 0;
2380: fed (directly or indirctly) */
2381:
2382: cursor c_balance_feed_info (p_balance_name varchar2,p_each_asgid number) is
2383: select nvl(pet.element_information20,'NOT FOUND'),pbtl.balance_name,rv.result_value
2384: from pay_assignment_actions pa,
2385: pay_payroll_actions ppa,
2386: per_all_assignments_f paf,
2387: pay_element_types_f pet,
2388: pay_run_results r,
2439: and fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
2440:
2441: CURSOR c_get_fapp_lkd_actid_rtype(cp_locked_actid number) IS
2442: select ppa.report_type
2443: from pay_payroll_actions ppa,pay_assignment_actions paa
2444: where paa.assignment_action_id = cp_locked_actid
2445: and ppa.payroll_action_id = paa.payroll_action_id;
2446:
2447: CURSOR c_get_fapp_locked_action_id(cp_locking_act_id number) IS
2466: select /*+ Ordered */
2467: paa.assignment_action_id,
2468: ppa.date_earned
2469: from per_all_assignments_f paf,
2470: pay_assignment_actions paa,
2471: pay_payroll_actions ppa,
2472: pay_action_classifications pac,
2473: pay_action_contexts pac1,
2474: ff_contexts fc
2501: cp_tax_unit_id number,
2502: cp_jurisdiction varchar2) is
2503: select paa.assignment_action_id,
2504: ppa.date_earned
2505: from pay_assignment_actions paa,
2506: per_all_assignments_f paf,
2507: per_all_people_f ppf,
2508: pay_payroll_actions ppa,
2509: pay_action_classifications pac,
2601: l_chunk,
2602: l_payroll_action_id,
2603: lv_serial_number
2604: FROM
2605: pay_assignment_actions aa
2606: WHERE
2607: aa.assignment_action_id = p_assactid;
2608:
2609: l_year_start := trunc(p_effective_date, 'Y');
2692: paa1.assignment_action_id
2693: INTO
2694: l_aaid
2695: FROM
2696: pay_assignment_actions paa1,
2697: per_all_assignments_f paf2
2698: WHERE
2699: paa1.assignment_id = paf2.assignment_id
2700: and paa1.tax_unit_id = l_tax_unit_id
2701: and (paa1.action_sequence , paf2.person_id) =
2702: (SELECT MAX(paa.action_sequence), paf.person_id
2703: FROM pay_action_classifications pac,
2704: pay_payroll_actions ppa,
2705: pay_assignment_actions paa,
2706: per_all_assignments_f paf1,
2707: per_all_assignments_f paf
2708: WHERE paf.assignment_id = l_asgid
2709: AND paf1.person_id = paf.person_id
4412: if l_eoy_tax_unit_id <> -99999 then
4413:
4414: sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
4415: PPA PAY_PAYROLL_ACTIONS_N51,
4416: PAA PAY_ASSIGNMENT_ACTIONS_N50,
4417: ASG PER_ASSIGNMENTS_F_PK,
4418: PPA1 PAY_PAYROLL_ACTIONS_PK)
4419: USE_NL(PPY, PPA, PAA, ASG, PPA1) */
4420: distinct asg.person_id
4419: USE_NL(PPY, PPA, PAA, ASG, PPA1) */
4420: distinct asg.person_id
4421: from pay_all_payrolls_f ppy,
4422: pay_payroll_actions ppa,
4423: pay_assignment_actions paa,
4424: per_all_assignments_f asg,
4425: pay_payroll_actions ppa1
4426: where ppa1.payroll_action_id = :payroll_action_id
4427: and ppa.effective_date between