[Home] [Help]
338:
339: lv_sql_string :=
340: 'select distinct paf.person_id
341: from pay_payroll_actions ppa_arch,
342: pay_assignment_actions paa_arch,
343: per_assignments_f paf,
344: pay_payroll_actions ppa
345: where paa_arch.assignment_id = paf.assignment_id
346: and ppa.payroll_action_id = :p_payroll_action_id
356:
357: lv_sql_string :=
358: 'select distinct paf.person_id
359: from pay_payroll_actions ppa_arch,
360: pay_assignment_actions paa_arch,
361: per_assignments_f paf,
362: pay_payroll_actions ppa
363: where paa_arch.assignment_id = paf.assignment_id
364: and ppa.payroll_action_id = :p_payroll_action_id
421: l_state pay_payroll_actions.report_qualifier%type;
422: l_report_format pay_report_format_mappings_f.report_format%type;
423: -- Variables declared for bug 10399514
424:
425: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
426: lv_employee_number per_people_f.employee_number%type;
427: lv_message varchar2(300):= null; --#5046003 Increased size from 100 to 300
428: lv_full_name per_people_f.full_name%type;
429: lv_record_name varchar2(100);
426: lv_employee_number per_people_f.employee_number%type;
427: lv_message varchar2(300):= null; --#5046003 Increased size from 100 to 300
428: lv_full_name per_people_f.full_name%type;
429: lv_record_name varchar2(100);
430: ln_serial_number pay_assignment_actions.serial_number%TYPE;
431: ln_gre_id NUMBER;
432: ln_gre_parameter NUMBER;
433: lv_ppr_report_type varchar2(50);
434:
441: select distinct paf.person_id
442: from hr_assignment_set_amendments asgset,
443: per_assignments_f paf,
444: pay_payroll_actions ppa,
445: pay_assignment_actions paa
446: where asgset.assignment_set_id = cp_asg_set
447: and asgset.include_or_exclude = 'I'
448: and paf.assignment_id = asgset.assignment_id
449: and paf.person_id between cp_start_person
466: select distinct paf.person_id
467: from hr_assignment_set_amendments asgset,
468: per_assignments_f paf,
469: pay_payroll_actions ppa,
470: pay_assignment_actions paa,
471: pay_population_ranges ppr
472: where asgset.assignment_set_id = cp_asg_set
473: and asgset.include_or_exclude = 'I'
474: and paf.assignment_id = asgset.assignment_id
501: AND hoi.org_information_context = 'Canada Employer Identification'
502: AND hoi.org_information5 in ('T4A/RL1','T4A/RL2')
503: AND exists ( SELECT 1
504: FROM pay_payroll_actions ppa ,
505: pay_assignment_actions paa
506: WHERE ppa.report_type = 'CAEOY_T4A_AMEND_PP'
507: AND ppa.report_qualifier = 'CAEOY_T4A_AMEND_PPQ'
508: AND ppa.business_group_id = cp_bg_id
509: AND ppa.effective_date = cp_eff_date
516: cp_start_person number,
517: cp_end_person number) is
518: select distinct paa_arch.serial_number
519: from pay_payroll_actions ppa_arch,
520: pay_assignment_actions paa_arch
521: where ppa_arch.business_group_id = cp_bg_id
522: and ppa_arch.report_type = 'CAEOY_T4A_AMEND_PP'
523: AND ppa_arch.report_qualifier = 'CAEOY_T4A_AMEND_PPQ'
524: and ppa_arch.effective_date = cp_eff_date
537: cp_start_person number,
538: cp_end_person number) is
539: select distinct paa_arch.serial_number
540: from pay_payroll_actions ppa_arch,
541: pay_assignment_actions paa_arch,
542: pay_population_ranges ppr
543: where ppa_arch.business_group_id = cp_bg_id
544: and ppa_arch.report_type = 'CAEOY_T4A_AMEND_PP'
545: AND ppa_arch.report_qualifier = 'CAEOY_T4A_AMEND_PPQ'
566: paa.assignment_id,
567: paa.assignment_action_id,
568: paa.tax_unit_id
569: from pay_payroll_actions ppa,
570: pay_assignment_actions paa
571: where paa.serial_number = to_char(cp_person_id)
572: and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
573: and ppa.business_group_id = ln_business_group_id
574: and paa.action_status = 'C'
576: and ppa.effective_date = cp_effective_date
577: and ppa.report_type = 'CAEOY_T4A_AMEND_PP'
578: and not exists (select 1
579: from pay_action_interlocks pai,
580: pay_assignment_actions paa1,
581: pay_payroll_actions ppa1
582: where pai.locked_action_id = paa.assignment_action_id
583: and pai.locking_action_id = paa1.assignment_action_id
584: and paa1.payroll_action_id = ppa1.payroll_action_id
598: paa.assignment_action_id,
599: paa.tax_unit_id,
600: paf.effective_end_date
601: from pay_payroll_actions ppa,
602: pay_assignment_actions paa,
603: per_assignments_f paf
604: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
605: and paa.assignment_id = paf.assignment_id
606: and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
612: and not exists
613: ( SELECT pail.locked_action_id
614: FROM pay_action_interlocks pail,
615: pay_payroll_actions pact,
616: pay_assignment_actions passt
617: WHERE pact.report_type IN ('PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP') -- #10352779
618: AND pact.payroll_action_id = passt.payroll_action_id
619: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
620: AND passt.assignment_action_id = pail.locking_action_id
637: paa.assignment_id,
638: paa.assignment_action_id,
639: paa.tax_unit_id
640: from pay_payroll_actions ppa,
641: pay_assignment_actions paa
642: where paa.serial_number = to_char(cp_person_id)
643: and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
644: and ppa.business_group_id = ln_business_group_id
645: and paa.action_status = 'C'
661: paa.assignment_action_id,
662: paa.tax_unit_id,
663: paf.effective_end_date
664: from pay_payroll_actions ppa,
665: pay_assignment_actions paa,
666: per_assignments_f paf
667: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
668: and paa.assignment_id = paf.assignment_id
669: and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
675: and exists
676: ( SELECT pail.locked_action_id
677: FROM pay_action_interlocks pail,
678: pay_payroll_actions pact,
679: pay_assignment_actions passt
680: WHERE pact.report_type = 'PAYCAT4AAMPDF'
681: AND pact.payroll_action_id = passt.payroll_action_id
682: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
683: AND passt.assignment_action_id = pail.locking_action_id
692: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
693: /* Added not exists condition for # 10323048 */
694: and not exists (select 1
695: from pay_payroll_actions ppa2,
696: pay_assignment_actions paa2
697: where ppa2.payroll_action_id = paa2.payroll_action_id
698: and ppa2.report_type = 'CAEOY_T4A_AMEND_PP'
699: and paa2.assignment_id = paa.assignment_id
700: and paa2.tax_unit_id = paa.tax_unit_id -- Added this condition #10328209
713: paa.assignment_action_id,
714: paa.tax_unit_id,
715: paf.effective_end_date
716: from pay_payroll_actions ppa,
717: pay_assignment_actions paa,
718: per_assignments_f paf,
719: per_periods_of_service pds
720: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
721: and paa.assignment_id = paf.assignment_id
728: and not exists
729: ( SELECT pail.locked_action_id
730: FROM pay_action_interlocks pail,
731: pay_payroll_actions pact,
732: pay_assignment_actions passt
733: WHERE pact.report_type IN ('PAYCAT4AAMPDF','CAEOY_T4A_AMEND_PP') -- #10352779
734: AND pact.payroll_action_id = passt.payroll_action_id
735: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
736: AND passt.assignment_action_id = pail.locking_action_id
756: paa.assignment_action_id,
757: paa.tax_unit_id,
758: paf.effective_end_date
759: from pay_payroll_actions ppa,
760: pay_assignment_actions paa,
761: per_assignments_f paf,
762: per_periods_of_service pds
763: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
764: and paa.assignment_id = paf.assignment_id
771: and exists
772: ( SELECT pail.locked_action_id
773: FROM pay_action_interlocks pail,
774: pay_payroll_actions pact,
775: pay_assignment_actions passt
776: WHERE pact.report_type = 'PAYCAT4AAMPDF'
777: AND pact.payroll_action_id = passt.payroll_action_id
778: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
779: AND passt.assignment_action_id = pail.locking_action_id
788: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
789: /* Added not exists condition for # 10323048 */
790: and not exists (select 1
791: from pay_payroll_actions ppa2,
792: pay_assignment_actions paa2
793: where ppa2.payroll_action_id = paa2.payroll_action_id
794: and ppa2.report_type = 'CAEOY_T4A_AMEND_PP'
795: and paa2.assignment_id = paa.assignment_id
796: and paa2.tax_unit_id = paa.tax_unit_id -- Added this condition #10328209
942: 'ASSIGNMENT',
943: ln_primary_assignment_id,
944: l_effective_end_date),'Y') = 'Y' then
945:
946: select pay_assignment_actions_s.nextval
947: into ln_t4a_amend_reg_asg_action
948: from dual;
949:
950: hr_utility.trace('New T4A Amend Action = ' ||
949:
950: hr_utility.trace('New T4A Amend Action = ' ||
951: to_char(ln_t4a_amend_reg_asg_action));
952:
953: /* Insert into pay_assignment_actions. */
954: hr_nonrun_asact.insact(ln_t4a_amend_reg_asg_action
955: ,ln_primary_assignment_id
956: ,p_payroll_action_id
957: ,p_chunk
972: trim(pay_ca_archive_utils.get_archive_value
973: (ln_asg_act_to_lock ,
974: 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS'));
975:
976: update pay_assignment_actions aa
977: set aa.serial_number = ln_serial_number
978: where aa.assignment_action_id = ln_t4a_amend_reg_asg_action;
979: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,1,14));
980: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,15,14));
1098: 'PRINT',
1099: 'ASSIGNMENT',
1100: ln_primary_assignment_id,
1101: l_effective_end_date),'Y') = 'Y' then
1102: select pay_assignment_actions_s.nextval
1103: into ln_t4a_amend_reg_asg_action
1104: from dual;
1105:
1106: hr_utility.trace('New T4A Amend Action = ' ||
1105:
1106: hr_utility.trace('New T4A Amend Action = ' ||
1107: to_char(ln_t4a_amend_reg_asg_action));
1108:
1109: /* Insert into pay_assignment_actions. */
1110: hr_nonrun_asact.insact(ln_t4a_amend_reg_asg_action
1111: ,ln_primary_assignment_id
1112: ,p_payroll_action_id
1113: ,p_chunk
1128: trim(pay_ca_archive_utils.get_archive_value
1129: (ln_asg_act_to_lock ,
1130: 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS'));
1131:
1132: update pay_assignment_actions aa
1133: set aa.serial_number = ln_serial_number
1134: where aa.assignment_action_id = ln_t4a_amend_reg_asg_action;
1135: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,1,14));
1136: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,15,14));
1236: /* Create an assignment action for this person */
1237: hr_utility.trace('get_latest_t4a_amend_dtls found ');
1238: hr_utility.trace('Report Type: '||lv_report_type);
1239:
1240: select pay_assignment_actions_s.nextval
1241: into ln_t4a_amend_reg_asg_action
1242: from dual;
1243:
1244: hr_utility.trace('New T4A Amend Action = ' ||
1243:
1244: hr_utility.trace('New T4A Amend Action = ' ||
1245: to_char(ln_t4a_amend_reg_asg_action));
1246:
1247: /* Insert into pay_assignment_actions. */
1248: hr_nonrun_asact.insact(ln_t4a_amend_reg_asg_action
1249: ,ln_primary_assignment_id
1250: ,p_payroll_action_id
1251: ,p_chunk
1264: ***********************************************************/
1265: ln_serial_number := lpad(to_char(ln_asg_act_to_lock),14,0)||
1266: lpad(to_char(ln_yepp_pact_id),14,0);
1267:
1268: update pay_assignment_actions aa
1269: set aa.serial_number = ln_serial_number
1270: where aa.assignment_action_id = ln_t4a_amend_reg_asg_action;
1271:
1272: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1343: hr_utility.trace('Report Type: '||lv_report_type);
1344:
1345: /* Create an assignment action for this person */
1346:
1347: select pay_assignment_actions_s.nextval
1348: into ln_t4a_amend_reg_asg_action
1349: from dual;
1350:
1351: hr_utility.trace('New T4A Amend Action = ' || ln_t4a_amend_reg_asg_action);
1349: from dual;
1350:
1351: hr_utility.trace('New T4A Amend Action = ' || ln_t4a_amend_reg_asg_action);
1352:
1353: /* Insert into pay_assignment_actions. */
1354: hr_nonrun_asact.insact(ln_t4a_amend_reg_asg_action
1355: ,ln_primary_assignment_id
1356: ,p_payroll_action_id
1357: ,p_chunk
1373:
1374: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1375: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
1376:
1377: update pay_assignment_actions aa
1378: set aa.serial_number = ln_serial_number
1379: where aa.assignment_action_id = ln_t4a_amend_reg_asg_action;
1380:
1381: end if; -- lv_amend_flag = 'Y' condition
1643: hr_all_organization_units hou1,
1644: hr_locations_all loc,
1645: per_all_people_f ppf,
1646: per_all_assignments_f paf,
1647: pay_assignment_actions paa1,
1648: pay_payroll_actions ppa1
1649: where ppa1.payroll_action_id = :p_payroll_action_id
1650: and paa1.payroll_action_id = ppa1.payroll_action_id
1651: and paa1.assignment_id = paf.assignment_id