[Home] [Help]
342:
343: lv_sql_string :=
344: 'select distinct paf.person_id
345: from pay_payroll_actions ppa_arch,
346: pay_assignment_actions paa_arch,
347: per_assignments_f paf,
348: pay_payroll_actions ppa
349: where paa_arch.assignment_id = paf.assignment_id
350: and ppa.payroll_action_id = :payroll_action_id
361:
362: lv_sql_string :=
363: 'select distinct paf.person_id
364: from pay_payroll_actions ppa_arch,
365: pay_assignment_actions paa_arch,
366: per_assignments_f paf,
367: pay_payroll_actions ppa
368: where paa_arch.assignment_id = paf.assignment_id
369: and ppa.payroll_action_id = :payroll_action_id
419: ln_asg_set NUMBER := 0 ;
420: lv_print varchar2(10);
421:
422: lv_report_type pay_payroll_actions.report_type%TYPE ;
423: ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
424:
425: lv_serial_number VARCHAR2(30);
426: lv_employee_number per_people_f.employee_number%type;
427: lv_message varchar2(100):= null;
426: lv_employee_number per_people_f.employee_number%type;
427: lv_message varchar2(100):= null;
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: lv_ppr_report_type varchar2(50);
432: -- Variables declared for bug 10399514
433: l_person_on boolean ;
434: l_report_cat pay_payroll_actions.report_category%type;
444: select distinct paf.person_id
445: from hr_assignment_set_amendments asgset,
446: per_assignments_f paf,
447: pay_payroll_actions ppa_arch,
448: pay_assignment_actions paa_arch
449: where asgset.assignment_set_id = cp_asg_set
450: and asgset.include_or_exclude = 'I'
451: and paf.assignment_id = asgset.assignment_id
452: and paf.person_id between cp_start_person
471: select distinct paf.person_id
472: from hr_assignment_set_amendments asgset,
473: per_assignments_f paf,
474: pay_payroll_actions ppa_arch,
475: pay_assignment_actions paa_arch,
476: pay_population_ranges ppr
477: where asgset.assignment_set_id = cp_asg_set
478: and asgset.include_or_exclude = 'I'
479: and paf.assignment_id = asgset.assignment_id
508: AND hoi.org_information_context = 'Prov Reporting Est'
509: AND hoi.org_information4 = 'P01'
510: AND exists ( SELECT 1
511: FROM pay_payroll_actions ppa ,
512: pay_assignment_actions paa
513: WHERE ppa.report_type = 'CAEOY_RL1_AMEND_PP'
514: AND ppa.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
515: AND ppa.business_group_id = cp_bg_id
516: AND ppa.effective_date = cp_eff_date
525: cp_start_person number,
526: cp_end_person number) is
527: select distinct paa_arch.serial_number
528: from pay_payroll_actions ppa_arch,
529: pay_assignment_actions paa_arch
530: where ppa_arch.business_group_id = cp_bg_id
531: and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
532: AND ppa_arch.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
533: and ppa_arch.effective_date = cp_eff_date
549: cp_start_person number,
550: cp_end_person number) is
551: select distinct paa_arch.serial_number
552: from pay_payroll_actions ppa_arch,
553: pay_assignment_actions paa_arch,
554: pay_population_ranges ppr
555: where ppa_arch.business_group_id = cp_bg_id
556: and ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
557: AND ppa_arch.report_qualifier = 'CAEOY_RL1_AMEND_PPQ'
580: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
581: ppa.legislative_parameters),
582: paf.effective_end_date
583: from pay_payroll_actions ppa,
584: pay_assignment_actions paa,
585: per_assignments_f paf
586: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
587: and paa.assignment_id = paf.assignment_id
588: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
602: AND not exists
603: ( SELECT pail.locked_action_id
604: FROM pay_action_interlocks pail,
605: pay_payroll_actions pact,
606: pay_assignment_actions passt
607: WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND'),'CAEOY_RL1_AMEND_PP')/*Added for Bug#5046006*/
608: AND pact.payroll_action_id = passt.payroll_action_id
609: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
610: AND passt.assignment_action_id = pail.locking_action_id
628: paa.assignment_action_id,
629: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
630: ppa.legislative_parameters)
631: from pay_payroll_actions ppa,
632: pay_assignment_actions paa
633: where paa.serial_number = to_char(cp_person_id)
634: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
635: ppa.legislative_parameters) =
636: nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
660: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
661: ppa.legislative_parameters),
662: paf.effective_end_date
663: from pay_payroll_actions ppa,
664: pay_assignment_actions paa,
665: per_assignments_f paf
666: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
667: and paa.assignment_id = paf.assignment_id
668: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
682: and exists
683: ( SELECT pail.locked_action_id
684: FROM pay_action_interlocks pail,
685: pay_payroll_actions pact,
686: pay_assignment_actions passt
687: WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND')
688: AND pact.payroll_action_id = passt.payroll_action_id
689: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
690: AND passt.assignment_action_id = pail.locking_action_id
695: OR paa.assignment_action_id < passt.assignment_action_id))
696: and ((not exists
697: ( SELECT 1
698: FROM pay_payroll_actions ppa2,
699: pay_assignment_actions paa2
700: WHERE ppa2.report_type = 'CAEOY_RL1_AMEND_PP'
701: AND ppa2.payroll_action_id = paa2.payroll_action_id
702: AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
703: AND paa2.assignment_action_id > paa.assignment_action_id
722: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
723: ppa.legislative_parameters),
724: paf.effective_end_date
725: from pay_payroll_actions ppa,
726: pay_assignment_actions paa,
727: per_assignments_f paf,
728: per_periods_of_service pds
729: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
730: and paa.assignment_id = paf.assignment_id
745: AND not exists
746: ( SELECT pail.locked_action_id
747: FROM pay_action_interlocks pail,
748: pay_payroll_actions pact,
749: pay_assignment_actions passt
750: WHERE pact.report_type in (decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND'),'CAEOY_RL1_AMEND_PP')/*Added for Bug#5046006*/
751: AND pact.payroll_action_id = passt.payroll_action_id
752: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
753: AND passt.assignment_action_id = pail.locking_action_id
775: pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
776: ppa.legislative_parameters),
777: paf.effective_end_date
778: from pay_payroll_actions ppa,
779: pay_assignment_actions paa,
780: per_assignments_f paf,
781: per_periods_of_service pds
782: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
783: and paa.assignment_id = paf.assignment_id
798: and exists
799: ( SELECT pail.locked_action_id
800: FROM pay_action_interlocks pail,
801: pay_payroll_actions pact,
802: pay_assignment_actions passt
803: WHERE pact.report_type = decode(lv_ppr_report_type,'PAYCARL1AMPDF','PAYCARL1AMPDF','PYRL1PRAMEND')
804: AND pact.payroll_action_id = passt.payroll_action_id
805: AND pact.effective_date = cp_effective_date /*Added for 10381064*/
806: AND passt.assignment_action_id = pail.locking_action_id
811: OR paa.assignment_action_id < passt.assignment_action_id))
812: and ((not exists
813: ( SELECT 1
814: FROM pay_payroll_actions ppa2,
815: pay_assignment_actions paa2
816: WHERE ppa2.report_type = 'CAEOY_RL1_AMEND_PP'
817: AND ppa2.payroll_action_id = paa2.payroll_action_id
818: AND ppa2.effective_date = cp_effective_date /*Added for 10381064*/
819: AND paa2.assignment_action_id > paa.assignment_action_id
870: where user_name = cp_user_name;
871:
872: CURSOR c_paa_update_check (cp_locking_asg_act_id number) IS
873: select assignment_action_id from
874: pay_assignment_actions where
875: assignment_action_id = cp_locking_asg_act_id;
876:
877: lv_gross_earn_value varchar2(30);
878: lv_jurisdiction varchar2(10);
882: ln_yepp_pact_id number;
883: ln_pre_id_null number;
884: ln_iteration number := 0;
885: lv_flag_count number := 0;
886: l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
887: l_effective_end_date date; --Added for bug 15886428
888:
889: BEGIN
890:
984: 'PRINT',
985: 'ASSIGNMENT',
986: ln_primary_assignment_id,
987: l_effective_end_date),'Y') = 'Y' then
988: select pay_assignment_actions_s.nextval
989: into ln_rl1_amend_reg_asg_action
990: from dual;
991:
992: hr_utility.trace('New RL1 Amend Action = ' ||
1002: /* Create an assignment action for this person */
1003: hr_utility.trace('get_latest_rl1_amend_dtls found ');
1004: hr_utility.trace('Report Type: '||lv_report_type);
1005:
1006: /* Insert into pay_assignment_actions. */
1007: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
1008: ,ln_primary_assignment_id
1009: ,p_payroll_action_id
1010: ,p_chunk
1022: ln_serial_number := lv_prov_of_emp||
1023: lpad(to_char(ln_asg_act_to_lock),14,0)||
1024: lpad(to_char(ln_yepp_pact_id),14,0);
1025:
1026: update pay_assignment_actions aa
1027: set aa.serial_number = ln_serial_number
1028: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
1029:
1030: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
1170: /* Create an assignment action for this person */
1171: hr_utility.trace('get_recent_rl1_amend_dtls found ');
1172: hr_utility.trace('Report Type: '||lv_report_type);
1173:
1174: select pay_assignment_actions_s.nextval
1175: into ln_rl1_amend_reg_asg_action
1176: from dual;
1177:
1178: hr_utility.trace('New RL1 Amend Action = ' ||
1177:
1178: hr_utility.trace('New RL1 Amend Action = ' ||
1179: to_char(ln_rl1_amend_reg_asg_action));
1180:
1181: /* Insert into pay_assignment_actions. */
1182: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
1183: ,ln_primary_assignment_id
1184: ,p_payroll_action_id
1185: ,p_chunk
1192: ln_serial_number := lv_prov_of_emp||
1193: lpad(to_char(ln_asg_act_to_lock),14,0)||
1194: lpad(to_char(ln_yepp_pact_id),14,0);
1195:
1196: update pay_assignment_actions aa
1197: set aa.serial_number = ln_serial_number
1198: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
1199:
1200: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
1275: hr_utility.trace('Report Type: '||lv_report_type);
1276:
1277: /* Create an assignment action for this person */
1278:
1279: select pay_assignment_actions_s.nextval
1280: into ln_rl1_amend_reg_asg_action
1281: from dual;
1282: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
1283:
1280: into ln_rl1_amend_reg_asg_action
1281: from dual;
1282: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
1283:
1284: /* Insert into pay_assignment_actions. */
1285: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
1286: ,ln_primary_assignment_id
1287: ,p_payroll_action_id
1288: ,p_chunk
1297: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
1298: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1299: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
1300:
1301: update pay_assignment_actions aa
1302: set aa.serial_number = ln_serial_number
1303: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
1304:
1305: end if; -- lv_amend_flag = 'Y' condition
1394: ln_primary_assignment_id,
1395: l_effective_end_date),'Y') = 'Y' then
1396: /* Create an assignment action for this person */
1397:
1398: select pay_assignment_actions_s.nextval
1399: into ln_rl1_amend_reg_asg_action
1400: from dual;
1401: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
1402:
1399: into ln_rl1_amend_reg_asg_action
1400: from dual;
1401: hr_utility.trace('New RL1 Amend Action = ' || ln_rl1_amend_reg_asg_action);
1402:
1403: /* Insert into pay_assignment_actions. */
1404: hr_nonrun_asact.insact(ln_rl1_amend_reg_asg_action
1405: ,ln_primary_assignment_id
1406: ,p_payroll_action_id
1407: ,p_chunk
1417: hr_utility.trace('lv_prov_of_emp: '||substr(ln_serial_number,1,2));
1418: hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
1419: hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
1420:
1421: update pay_assignment_actions aa
1422: set aa.serial_number = ln_serial_number
1423: where aa.assignment_action_id = ln_rl1_amend_reg_asg_action;
1424:
1425: end if; --pay_us_employee_payslip_web.get_doc_eit
1673: hr_all_organization_units hou,
1674: hr_locations_all loc,
1675: per_all_people_f ppf,
1676: per_all_assignments_f paf,
1677: pay_assignment_actions paa1,
1678: pay_payroll_actions ppa1
1679: where ppa1.payroll_action_id = :pactid
1680: and paa1.payroll_action_id = ppa1.payroll_action_id
1681: and paa1.assignment_id = paf.assignment_id