[Home] [Help]
76: to add the missing assignment_id and tax_unit_id
77: check inside the exists clause.
78: 08-DEC-2010 emunisek 115.23 10381064 Modified cursors get_latest_rl1_amend_dtls,
79: get_reprint_rl1_amend_dtls to introduce the Date
80: Check on pay_payroll_actions table used in exists
81: clauses
82: 06-SEP-2011 rgottipa 115.24 10399514 introduced new cursores
83: c_selected_asg_set_range and
84: c_all_asg_range. These will be
127: effective_date,
128: start_date,
129: business_group_id,
130: report_type
131: from pay_payroll_actions ppa
132: where ppa.payroll_action_id = cp_payroll_action_id;
133:
134: ld_end_date DATE;
135: ld_start_date DATE;
209: report_qualifier,
210: report_category,
211: pay_ca_eoy_rl1_amend_reg.get_parameter(
212: 'PRINT_TERM',ppa.legislative_parameters)
213: from pay_payroll_actions ppa
214: where ppa.payroll_action_id = cp_payroll_action_id;
215:
216: ld_end_date DATE;
217: ld_start_date DATE;
341: elsif ln_pre_org_id is not NULL then
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
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
351: and ppa_arch.business_group_id = ppa.business_group_id
352: and ppa_arch.effective_date = ppa.effective_date
360: else
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
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
370: and ppa_arch.business_group_id = ppa.business_group_id
371: and ppa_arch.effective_date = ppa.effective_date
418: ln_set_person_id NUMBER := 0 ;
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;
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;
435: l_state pay_payroll_actions.report_qualifier%type;
436: l_report_format pay_report_format_mappings_f.report_format%type;
437: -- Variables declared for bug 10399514
438: l_print_term varchar2(1);
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;
435: l_state pay_payroll_actions.report_qualifier%type;
436: l_report_format pay_report_format_mappings_f.report_format%type;
437: -- Variables declared for bug 10399514
438: l_print_term varchar2(1);
439:
443: ,cp_effective_date in date) is
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
470: ,cp_effective_date in date) is
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'
507: >= cp_eff_date
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
524: cp_eff_date 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'
548: cp_eff_date 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'
579: paa.assignment_action_id,
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
601: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
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*/
627: paa.assignment_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) =
659: paa.assignment_action_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
681: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
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*/
694: AND (pail.locked_action_id = paa.assignment_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*/
721: 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)
744: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
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*/
774: paa.assignment_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)
797: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
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*/
810: AND (pail.locked_action_id = paa.assignment_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*/
833: CURSOR get_yepp_pact_id(cp_bg_id number,
834: cp_pre number,
835: cp_year date) IS
836: select payroll_action_id
837: from pay_payroll_actions
838: where business_group_id = cp_bg_id
839: and report_type = 'RL1'
840: and report_qualifier = 'CAEOYRL1'
841: and action_type = 'X'
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
1682: and paf.business_group_id = ppa1.business_group_id
1718: parameter_list varchar2) return varchar2
1719: IS
1720: start_ptr number;
1721: end_ptr number;
1722: token_val pay_payroll_actions.legislative_parameters%type;
1723: par_value pay_payroll_actions.legislative_parameters%type;
1724:
1725: BEGIN
1726:
1719: IS
1720: start_ptr number;
1721: end_ptr number;
1722: token_val pay_payroll_actions.legislative_parameters%type;
1723: par_value pay_payroll_actions.legislative_parameters%type;
1724:
1725: BEGIN
1726:
1727: token_val := name||'=';