1: PACKAGE BODY pay_ca_eoy_rl2_amend_arch AS
2: /* $Header: pycarl2ca.pkb 120.6 2012/01/19 08:40:55 rpahune ship $ */
3: /*
4: ******************************************************************
5: * *
16: * 500 Oracle Parkway, Redwood City, CA, 94065. *
17: * *
18: ******************************************************************
19:
20: Name : pay_ca_eoy_rl2_amend_arch
21:
22: Description : This procedure is used by RL2 Amendment PreProcess
23: to archive data for RL2 Amendment Paper Report.
24:
41: c_selected_asg_set CURSOR, if
42: RANGE_PERSON_ID is enabled.
43: ****************************************************************************/
44:
45: gv_package VARCHAR2(100) := 'pay_ca_eoy_rl2_amend_arch';
46: gv_procedure_name VARCHAR2(100);
47:
48:
49: /*****************************************************************************
64: ,p_asg_set out nocopy number
65: ,p_year out nocopy varchar2)
66: IS
67: cursor c_payroll_Action_info (cp_payroll_action_id in number) is
68: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
69: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
70: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
71: 'PER_ID',ppa.legislative_parameters)),
72: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
66: IS
67: cursor c_payroll_Action_info (cp_payroll_action_id in number) is
68: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
69: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
70: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
71: 'PER_ID',ppa.legislative_parameters)),
72: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
73: 'SSN',ppa.legislative_parameters)),
74: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
68: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
69: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
70: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
71: 'PER_ID',ppa.legislative_parameters)),
72: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
73: 'SSN',ppa.legislative_parameters)),
74: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
75: 'ASG_SET',ppa.legislative_parameters)),
76: to_char(effective_date,'YYYY') ,
70: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
71: 'PER_ID',ppa.legislative_parameters)),
72: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
73: 'SSN',ppa.legislative_parameters)),
74: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
75: 'ASG_SET',ppa.legislative_parameters)),
76: to_char(effective_date,'YYYY') ,
77: effective_date,
78: start_date,
141: ,p_state out nocopy varchar2
142: ,p_report_cat out nocopy varchar2)
143: IS
144: cursor c_payroll_Action_info (cp_payroll_action_id in number) is
145: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
146: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
147: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
148: 'PER_ID',ppa.legislative_parameters)),
149: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
143: IS
144: cursor c_payroll_Action_info (cp_payroll_action_id in number) is
145: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
146: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
147: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
148: 'PER_ID',ppa.legislative_parameters)),
149: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
150: 'SSN',ppa.legislative_parameters)),
151: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
145: select to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
146: 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
147: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
148: 'PER_ID',ppa.legislative_parameters)),
149: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
150: 'SSN',ppa.legislative_parameters)),
151: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
152: 'ASG_SET',ppa.legislative_parameters)),
153: to_char(effective_date,'YYYY') ,
147: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
148: 'PER_ID',ppa.legislative_parameters)),
149: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
150: 'SSN',ppa.legislative_parameters)),
151: to_number(pay_ca_eoy_rl2_amend_arch.get_parameter(
152: 'ASG_SET',ppa.legislative_parameters)),
153: to_char(effective_date,'YYYY') ,
154: effective_date,
155: start_date,
379: per_all_assignments_f paf,
380: pay_assignment_actions paa,
381: pay_payroll_actions ppa
382: where
383: (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
384: ppa.legislative_parameters) = to_char(ln_pre_org_id)
385: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
386: ppa.legislative_parameters) = to_char(ln_pre_org_id))
387: AND asgset.assignment_set_id = cp_asg_set
381: pay_payroll_actions ppa
382: where
383: (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
384: ppa.legislative_parameters) = to_char(ln_pre_org_id)
385: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
386: ppa.legislative_parameters) = to_char(ln_pre_org_id))
387: AND asgset.assignment_set_id = cp_asg_set
388: and asgset.include_or_exclude = 'I'
389: and paf.assignment_id = asgset.assignment_id
391: and cp_end_person
392: and ppa.report_type IN ('RL2', 'CAEOY_RL2_AMEND_PP')
393: and to_char(ppa.effective_date,'YYYY') = lv_year
394: and ppa.business_group_id+0 = ln_business_group_id
395: -- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
396: -- ppa.legislative_parameters) = to_char(ln_pre_org_id)
397: and paa.payroll_action_id = ppa.payroll_action_id
398: and paa.action_status = 'C'
399: and paf.person_id = to_number(paa.serial_number);
409: pay_assignment_actions paa,
410: pay_payroll_actions ppa,
411: pay_population_ranges ppr
412: where
413: (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
414: ppa.legislative_parameters) = to_char(ln_pre_org_id)
415: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
416: ppa.legislative_parameters) = to_char(ln_pre_org_id))
417: AND asgset.assignment_set_id = cp_asg_set
411: pay_population_ranges ppr
412: where
413: (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
414: ppa.legislative_parameters) = to_char(ln_pre_org_id)
415: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
416: ppa.legislative_parameters) = to_char(ln_pre_org_id))
417: AND asgset.assignment_set_id = cp_asg_set
418: and asgset.include_or_exclude = 'I'
419: and paf.assignment_id = asgset.assignment_id
423: AND ppr.person_id = paf.person_id
424: and ppa.report_type IN ('RL2', 'CAEOY_RL2_AMEND_PP')
425: and to_char(ppa.effective_date,'YYYY') = lv_year
426: and ppa.business_group_id+0 = ln_business_group_id
427: -- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
428: -- ppa.legislative_parameters) = to_char(ln_pre_org_id)
429: and paa.payroll_action_id = ppa.payroll_action_id
430: and paa.action_status = 'C'
431: and paf.person_id = to_number(paa.serial_number);
444: -- Added for bug 10399514
445: paa.assignment_action_id
446: from pay_payroll_actions ppa,
447: pay_assignment_actions paa
448: where (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
449: ppa.legislative_parameters) = to_char(ln_pre_org_id)
450: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
451: ppa.legislative_parameters) = to_char(ln_pre_org_id))
452: AND to_number(paa.serial_number) = cp_person_id
446: from pay_payroll_actions ppa,
447: pay_assignment_actions paa
448: where (pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
449: ppa.legislative_parameters) = to_char(ln_pre_org_id)
450: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
451: ppa.legislative_parameters) = to_char(ln_pre_org_id))
452: AND to_number(paa.serial_number) = cp_person_id
453: -- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
454: -- ppa.legislative_parameters) = to_char(cp_pre_org_id)
449: ppa.legislative_parameters) = to_char(ln_pre_org_id)
450: OR pay_ca_eoy_rl2_amend_arch.get_parameter('TRANSMITTER_PRE',
451: ppa.legislative_parameters) = to_char(ln_pre_org_id))
452: AND to_number(paa.serial_number) = cp_person_id
453: -- and pay_ca_eoy_rl2_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
454: -- ppa.legislative_parameters) = to_char(cp_pre_org_id)
455: and paa.action_status = 'C'
456: and ppa.business_group_id+0 = ln_business_group_id
457: and ppa.payroll_action_id = paa.payroll_action_id
713:
714: Begin
715: null;
716:
717: end pay_ca_eoy_rl2_amend_arch;