[Home] [Help]
326:
327: lv_sql_string :=
328: 'select distinct paf.person_id
329: from pay_payroll_actions ppa_arch,
330: pay_assignment_actions paa_arch,
331: per_assignments_f paf,
332: pay_payroll_actions ppa
333: where paa_arch.assignment_id = paf.assignment_id
334: and ppa.payroll_action_id = :payroll_action_id
345:
346: lv_sql_string :=
347: 'select distinct paf.person_id
348: from pay_payroll_actions ppa_arch,
349: pay_assignment_actions paa_arch,
350: per_assignments_f paf,
351: pay_payroll_actions ppa
352: where paa_arch.assignment_id = paf.assignment_id
353: and ppa.payroll_action_id = :payroll_action_id
401: ln_asg_set NUMBER := 0 ;
402: lv_print varchar2(10);
403:
404: lv_report_type pay_payroll_actions.report_type%TYPE ;
405: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
406:
407: lv_serial_number VARCHAR2(30);
408: lv_employee_number per_people_f.employee_number%type;
409: lv_message varchar2(100):= null;
408: lv_employee_number per_people_f.employee_number%type;
409: lv_message varchar2(100):= null;
410: lv_full_name per_people_f.full_name%type;
411: lv_record_name varchar2(100);
412: ln_serial_number pay_assignment_actions.serial_number%TYPE;
413: lv_ppr_report_type varchar2(50);
414:
415: -- Variables declared for bug 10399514
416: l_person_on boolean ;
427: select distinct paf.person_id
428: from hr_assignment_set_amendments asgset,
429: per_assignments_f paf,
430: pay_payroll_actions ppa_arch,
431: pay_assignment_actions paa_arch
432: where asgset.assignment_set_id = cp_asg_set
433: and asgset.include_or_exclude = 'I'
434: and paf.assignment_id = asgset.assignment_id
435: and paf.person_id between cp_start_person
454: select distinct paf.person_id
455: from hr_assignment_set_amendments asgset,
456: per_assignments_f paf,
457: pay_payroll_actions ppa_arch,
458: pay_assignment_actions paa_arch,
459: pay_population_ranges ppr
460: where asgset.assignment_set_id = cp_asg_set
461: and asgset.include_or_exclude = 'I'
462: and paf.assignment_id = asgset.assignment_id
491: AND hoi.org_information_context = 'Prov Reporting Est'
492: AND hoi.org_information4 = 'P02'
493: AND exists ( SELECT 1
494: FROM pay_payroll_actions ppa ,
495: pay_assignment_actions paa
496: WHERE ppa.report_type = 'CAEOY_RL2_AMEND_PP'
497: AND ppa.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
498: AND ppa.business_group_id = cp_bg_id
499: AND ppa.effective_date = cp_eff_date
507: cp_start_person number,
508: cp_end_person number) is
509: select distinct paa_arch.serial_number
510: from pay_payroll_actions ppa_arch,
511: pay_assignment_actions paa_arch
512: where ppa_arch.business_group_id = cp_bg_id
513: and ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
514: AND ppa_arch.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
515: and ppa_arch.effective_date = cp_eff_date
529: cp_start_person number,
530: cp_end_person number) is
531: select distinct paa_arch.serial_number
532: from pay_payroll_actions ppa_arch,
533: pay_assignment_actions paa_arch,
534: pay_population_ranges ppr
535: where ppa_arch.business_group_id = cp_bg_id
536: and ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
537: AND ppa_arch.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
560: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
561: ppa.legislative_parameters),
562: paf.effective_end_date
563: from pay_payroll_actions ppa,
564: pay_assignment_actions paa,
565: per_assignments_f paf
566: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
567: and paa.assignment_id = paf.assignment_id
568: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
582: AND not exists
583: ( SELECT pail.locked_action_id
584: FROM pay_action_interlocks pail,
585: pay_payroll_actions pact,
586: pay_assignment_actions passt
587: WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL2AMPDF','PAYCARL2AMPDF','PYRL2PRAMEND'),'CAEOY_RL2_AMEND_PP')/*Added for Bug#5046010*/
588: AND pact.payroll_action_id = passt.payroll_action_id
589: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
590: AND passt.assignment_action_id = pail.locking_action_id
608: paa.assignment_action_id,
609: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
610: ppa.legislative_parameters)
611: from pay_payroll_actions ppa,
612: pay_assignment_actions paa
613: where paa.serial_number = to_char(cp_person_id)
614: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
615: ppa.legislative_parameters) =
616: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
640: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
641: ppa.legislative_parameters),
642: paf.effective_end_date
643: from pay_payroll_actions ppa,
644: pay_assignment_actions paa,
645: per_assignments_f paf
646: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
647: and paa.assignment_id = paf.assignment_id
648: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
662: and exists
663: ( SELECT pail.locked_action_id
664: FROM pay_action_interlocks pail,
665: pay_payroll_actions pact,
666: pay_assignment_actions passt
667: WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL2AMPDF','PAYCARL2AMPDF','PYRL2PRAMEND')
668: AND pact.payroll_action_id = passt.payroll_action_id
669: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
670: AND passt.assignment_action_id = pail.locking_action_id
675: OR paa.assignment_action_id < passt.assignment_action_id))
676: and ((not exists
677: ( SELECT 1
678: FROM pay_payroll_actions ppa2,
679: pay_assignment_actions paa2
680: WHERE ppa2.report_type = 'CAEOY_RL2_AMEND_PP'
681: AND ppa2.payroll_action_id = paa2.payroll_action_id
682: AND paa2.assignment_action_id > paa.assignment_action_id
683: AND pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa2.legislative_parameters)
703: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
704: ppa.legislative_parameters),
705: paf.effective_end_date
706: from pay_payroll_actions ppa,
707: pay_assignment_actions paa,
708: per_assignments_f paf,
709: per_periods_of_service pds
710: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
711: and paa.assignment_id = paf.assignment_id
726: AND not exists
727: ( SELECT pail.locked_action_id
728: FROM pay_action_interlocks pail,
729: pay_payroll_actions pact,
730: pay_assignment_actions passt
731: WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL2AMPDF','PAYCARL2AMPDF','PYRL2PRAMEND'),'CAEOY_RL2_AMEND_PP')/*Added for Bug#5046010*/
732: AND pact.payroll_action_id = passt.payroll_action_id
733: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
734: AND passt.assignment_action_id = pail.locking_action_id
755: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
756: ppa.legislative_parameters),
757: paf.effective_end_date
758: from pay_payroll_actions ppa,
759: pay_assignment_actions paa,
760: per_assignments_f paf,
761: per_periods_of_service pds
762: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
763: and paa.assignment_id = paf.assignment_id
778: and exists
779: ( SELECT pail.locked_action_id
780: FROM pay_action_interlocks pail,
781: pay_payroll_actions pact,
782: pay_assignment_actions passt
783: WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL2AMPDF','PAYCARL2AMPDF','PYRL2PRAMEND')
784: AND pact.payroll_action_id = passt.payroll_action_id
785: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
786: AND passt.assignment_action_id = pail.locking_action_id
791: OR paa.assignment_action_id < passt.assignment_action_id))
792: and ((not exists
793: ( SELECT 1
794: FROM pay_payroll_actions ppa2,
795: pay_assignment_actions paa2
796: WHERE ppa2.report_type = 'CAEOY_RL2_AMEND_PP'
797: AND ppa2.payroll_action_id = paa2.payroll_action_id
798: AND paa2.assignment_action_id > paa.assignment_action_id
799: AND pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',ppa2.legislative_parameters)
840: and jurisdiction_code = 'QC';
841:
842: CURSOR c_paa_update_check (cp_locking_asg_act_id number) IS
843: select assignment_action_id from
844: pay_assignment_actions where
845: assignment_action_id = cp_locking_asg_act_id;
846:
847: lv_gross_earn_value varchar2(30);
848: lv_jurisdiction varchar2(10);
850: lv_prov_amend_flag varchar2(5);
851: ln_yepp_pact_id number;
852: ln_pre_id_null number;
853: ln_iteration number := 0;
854: l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
855: l_effective_end_date date; -- Added for the bug 15886428
856:
857: BEGIN
858:
948: ln_primary_assignment_id,
949: l_effective_end_date),'Y') = 'Y' then
950: /* Create an assignment action for this person */
951:
952: select pay_assignment_actions_s.nextval
953: into ln_rl2_amend_reg_asg_action
954: from dual;
955:
956: hr_utility.trace('New RL2 Amend Action = ' ||
955:
956: hr_utility.trace('New RL2 Amend Action = ' ||
957: to_char(ln_rl2_amend_reg_asg_action));
958:
959: /* Insert into pay_assignment_actions. */
960: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
961: ln_primary_assignment_id,
962: p_payroll_action_id,
963: p_chunk,
975: ln_serial_number := 'QC'||
976: lpad(to_char(ln_asg_act_to_lock),14,0)||
977: lpad(to_char(ln_yepp_pact_id),14,0);
978:
979: update pay_assignment_actions aa
980: set aa.serial_number = ln_serial_number
981: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
982:
983: hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
1109: /* Create an assignment action for this person */
1110: hr_utility.trace('get_all_rl2_amend_dtls found ');
1111: hr_utility.trace('Report Type: '||lv_report_type);
1112:
1113: select pay_assignment_actions_s.nextval
1114: into ln_rl2_amend_reg_asg_action
1115: from dual;
1116:
1117: hr_utility.trace('New RL2 Amend Action = ' ||
1116:
1117: hr_utility.trace('New RL2 Amend Action = ' ||
1118: to_char(ln_rl2_amend_reg_asg_action));
1119:
1120: /* Insert into pay_assignment_actions. */
1121: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
1122: ln_primary_assignment_id,
1123: p_payroll_action_id,
1124: p_chunk,
1136: ln_serial_number := 'QC'||
1137: lpad(to_char(ln_asg_act_to_lock),14,0)||
1138: lpad(to_char(ln_yepp_pact_id),14,0);
1139:
1140: update pay_assignment_actions aa
1141: set aa.serial_number = ln_serial_number
1142: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
1143:
1144: hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
1212: hr_utility.trace('Report Type: '||lv_report_type);
1213:
1214: /* Create an assignment action for this person */
1215:
1216: select pay_assignment_actions_s.nextval
1217: into ln_rl2_amend_reg_asg_action
1218: from dual;
1219:
1220: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
1218: from dual;
1219:
1220: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
1221:
1222: /* Insert into pay_assignment_actions. */
1223: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
1224: ln_primary_assignment_id,
1225: p_payroll_action_id,
1226: p_chunk,
1237: hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
1238: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1239: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
1240:
1241: update pay_assignment_actions aa
1242: set aa.serial_number = ln_serial_number
1243: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
1244:
1245: end if; -- lv_amend_flag = 'Y' condition
1325: ln_primary_assignment_id,
1326: l_effective_end_date),'Y') = 'Y' then
1327: /* Create an assignment action for this person */
1328:
1329: select pay_assignment_actions_s.nextval
1330: into ln_rl2_amend_reg_asg_action
1331: from dual;
1332: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
1333:
1330: into ln_rl2_amend_reg_asg_action
1331: from dual;
1332: hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
1333:
1334: /* Insert into pay_assignment_actions. */
1335: hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action
1336: ,ln_primary_assignment_id
1337: ,p_payroll_action_id
1338: ,p_chunk
1348: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
1349: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1350: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
1351:
1352: update pay_assignment_actions aa
1353: set aa.serial_number = ln_serial_number
1354: where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
1355: end if; -- pay_us_employee_payslip_web.get_doc_eit
1356:
1611: hr_all_organization_units hou,
1612: hr_locations_all loc,
1613: per_all_people_f ppf,
1614: per_all_assignments_f paf,
1615: pay_assignment_actions paa1,
1616: pay_payroll_actions ppa1
1617: where ppa1.payroll_action_id = :pactid
1618: and paa1.payroll_action_id = ppa1.payroll_action_id
1619: and paa1.assignment_id = paf.assignment_id