[Home] [Help]
PACKAGE: APPS.PAY_CA_T4A_AMEND_MAG
Source
1 PACKAGE pay_ca_t4a_amend_mag AUTHID CURRENT_USER AS
2 /* $Header: pycat4aamag.pkh 120.2 2010/10/29 09:52:45 abraghun noship $ */
3
4 level_cnt NUMBER;
5
6 CURSOR t4a_amend_transmitter_record IS
7 SELECT
8 'TAX_UNIT_ID=C',
9 hoi.organization_id,
10 'PAYROLL_ACTION_ID=P',
11 max(ppa.payroll_action_id),
12 'SBMT_REF_ID=P',
13 to_char(max(ppa.payroll_action_id))
14 FROM
15 hr_organization_information hoi,
16 pay_payroll_actions ppa
17 WHERE hoi.organization_id = pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE')
18 AND hoi.org_information_context = 'Fed Magnetic Reporting'
19 AND ppa.report_type = 'CAEOY_T4A_AMEND_PP'
20 AND hoi.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE'
21 ,ppa.legislative_parameters)
22 AND to_char(ppa.effective_date
23 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
24 AND to_char(ppa.effective_date
25 ,'DD-MM') = '31-12'
26 GROUP BY
27 'TAX_UNIT_ID=C',
28 hoi.organization_id,
29 'PAYROLL_ACTION_ID=P';
30 /**************************************************************************/
31 CURSOR mag_t4a_amend_employer IS
32 SELECT
33 'TAX_UNIT_ID=P',
34 hoi1.organization_id,
35 'TAX_UNIT_NAME=P',
36 fai.value,
37 'PAYROLL_ACTION_ID=P',
38 to_char(max(ppa.payroll_action_id))
39 FROM
40 ff_archive_items fai,
41 ff_database_items fdi,
42 pay_payroll_actions ppa,
43 pay_assignment_actions aa,
44 hr_organization_information hoi1,
45 hr_organization_information hoi2
46 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
47 AND hoi1.org_information11 = aa.tax_unit_id
48 --AND hoi1.organization_id = hoi2.organization_id
49 AND hoi1.org_information11 = hoi2.organization_id
50 AND hoi2.org_information_context = 'Fed Magnetic Reporting'
51 AND hoi1.org_information5 like '%T4A%'
52 AND hoi1.org_information_context = 'Canada Employer Identification'
53 AND ppa.report_type = 'CAEOY_T4A_AMEND_PP'
54 AND to_char(ppa.effective_date
55 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
56 AND to_char(ppa.effective_date
57 ,'DD-MM') = '31-12'
58 AND hoi1.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE'
59 ,ppa.legislative_parameters)
60 AND fdi.user_name = 'CAEOY_EMPLOYER_NAME'
61 AND ppa.payroll_action_id = fai.context1
62 AND fdi.user_entity_id = fai.user_entity_id
63 GROUP BY
64 'TAX_UNIT_ID=P',
65 hoi1.organization_id,
66 'TAX_UNIT_NAME=P',
67 fai.value,
68 'PAYROLL_ACTION_ID=P'
69 ORDER BY
70 fai.value;
71 /**************************************************************************/
72 CURSOR mag_t4a_amend_employee IS
73 SELECT
74 'ASSIGNMENT_ACTION_ID=C',
75 pai.locked_action_id,
76 'ASSIGNMENT_ID=C',
77 paa.assignment_id,
78 'DATE_EARNED=C',
79 fnd_date.date_to_canonical(pay_magtape_generic.date_earned(ppa.effective_date
80 ,paa.assignment_id)),
81 'TRANSFER_ACT_ID=P',
82 pai.locking_action_id
83 FROM
84 per_all_people_f ppf,
85 per_all_assignments_f paf,
86 pay_action_interlocks pai,
87 pay_assignment_actions paa,
88 pay_payroll_actions ppa,
89 pay_assignment_actions paa_arch,
90 pay_payroll_actions ppa_arch
91 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
92 AND paa.payroll_action_id = ppa.payroll_action_id
93 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
94 AND pai.locking_action_id = paa.assignment_action_id
95 AND paf.assignment_id = paa.assignment_id
96 AND ppf.person_id = paf.person_id
97 AND pay_magtape_generic.date_earned(ppa.effective_date
98 ,paa.assignment_id) BETWEEN paf.effective_start_date AND paf.effective_end_date
99 AND pay_magtape_generic.date_earned(ppa.effective_date
100 ,paa.assignment_id) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
101 AND paa_arch.assignment_action_id = pai.locked_action_id
102 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
103 AND ppa_arch.report_type = 'CAEOY_T4A_AMEND_PP'
104 ORDER BY
105 ppf.last_name,
106 ppf.first_name,
107 ppf.middle_names;
108 /**************************************************************************/
109 CURSOR mag_t4a_amend_asg_actions IS
110 SELECT
111 'TRANSFER_ACT_ID=P',
112 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
113 FROM
114 dual;
115 /**************************************************************************/
116 PROCEDURE get_report_parameters(
117 p_pactid IN NUMBER,
118 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
119 p_year_end IN OUT NOCOPY DATE,
120 p_report_type IN OUT NOCOPY VARCHAR2,
121 p_business_group_id IN OUT NOCOPY NUMBER);
122 /**************************************************************************/
123 PROCEDURE range_cursor (
124 p_pactid IN NUMBER,
125 p_sqlstr OUT NOCOPY VARCHAR2);
126 /**************************************************************************/
127 PROCEDURE action_creation(
128 p_pactid IN NUMBER,
129 p_stperson IN NUMBER,
130 p_endperson IN NUMBER,
131 p_chunk IN NUMBER );
132 /**************************************************************************/
133 FUNCTION validate_gre_data (p_trans IN VARCHAR2,
134 p_year IN VARCHAR2) RETURN VARCHAR2;
135 /**************************************************************************/
136 FUNCTION get_parameter(name in VARCHAR2,
137 parameter_list VARCHAR2)
138 RETURN VARCHAR2;
139 /**************************************************************************/
140 FUNCTION get_arch_val(p_context_id IN NUMBER,
141 p_user_name IN VARCHAR2)
142 RETURN VARCHAR2;
143 /**************************************************************************/
144 FUNCTION convert_2_xml(p_data IN VARCHAR2,
145 p_tag IN VARCHAR2,
146 p_datatype IN CHAR DEFAULT 'T',
147 p_format IN VARCHAR2 DEFAULT NULL,
148 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
149 RETURN VARCHAR2;
150 /**************************************************************************/
151 FUNCTION get_t4a_pp_regno(p_pactid IN NUMBER,
152 p_tax_unit_id IN NUMBER,
153 p_pp_regno1 OUT NOCOPY VARCHAR2,
154 p_pp_regno2 OUT NOCOPY VARCHAR2,
155 p_pp_regno3 OUT NOCOPY VARCHAR2)
156 RETURN VARCHAR2;
157 /**************************************************************************/
158
159 PROCEDURE t4a_amend_mag_transmitter;
160
161 PROCEDURE end_of_file;
162
163 PROCEDURE t4a_amend_employer_record;
164
165 PROCEDURE t4a_amend_employer_start;
166
167 PROCEDURE t4a_amend_employee_record;
168
169 END pay_ca_t4a_amend_mag;