[Home] [Help]
PACKAGE: APPS.PAY_GB_EDI_P46_CAR
Source
1 PACKAGE pay_gb_edi_p46_car AS
2 /* $Header: pygbp46c.pkh 120.4 2008/01/23 14:12:42 apmishra noship $ */
3
4 ----------------------------
5 -- PROCEDURE range_cursor --
6 ----------------------------
7 -- Procedure which returns a varchar2 defining a SQL Statement to select
8 -- all the people in the business group.
9 -- The archiver uses this cursor to split the people into chunks for parallel
10 -- processing.
11 PROCEDURE range_cursor (pactid IN NUMBER,
12 sqlstr out nocopy varchar2);
13
14 -- For bug 6652235
15 ----------------------------
16 -- PROCEDURE range_code for V2 --
17 ----------------------------
18 -- Procedure which returns a varchar2 defining a SQL Statement to select
19 -- all the people in the business group.
20 -- The archiver uses this cursor to split the people into chunks for parallel
21 -- processing.
22 PROCEDURE range_code (pactid IN NUMBER,
23 sqlstr out nocopy varchar2);
24
25
26 ------------------------------
27 -- PROCEDURE create_asg_act --
28 ------------------------------
29 PROCEDURE create_asg_act(pactid IN NUMBER,
30 stperson IN NUMBER,
31 endperson IN NUMBER,
32 chunk IN NUMBER);
33
34 -- For bug 6652235
35 ------------------------------
36 -- PROCEDURE create_asg_act_v2 --
37 ------------------------------
38 PROCEDURE create_asg_act_v2(pactid IN NUMBER,
39 stperson IN NUMBER,
40 endperson IN NUMBER,
41 chunk IN NUMBER);
42
43 ------------------------------
44 -- PROCEDURE archive_code --
45 ------------------------------
46 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE);
47
48 -- For bug 6652235
49 ------------------------------
50 -- PROCEDURE archive_code_v2 --
51 ------------------------------
52 PROCEDURE archive_code_v2(p_assactid IN NUMBER, p_effective_date IN DATE);
53
54 -----------------------------
55 -- Employer Header Cursor --
56 -----------------------------
57 CURSOR c_employer_header IS
58 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
59 'RECEIVER_ID=P', 'INLAND REVENUE',
60 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
61 (legislative_parameters,
62 'TEST'),'Y','1',' '),
63 'URGENT_MARKER=P', decode(pay_gb_eoy_archive.get_parameter
64 (legislative_parameters,
65 'URGENT'),'Y','1',' '),
66 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
67 'FORM_TYPE=P', '11',
68 'FORM_TYPE_MEANING=P', 'P46 Car EDI',
69 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
70 'TAX_DIST_REF=P',
71 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086307 */
72 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
73 'EMPLOYERS_ADDRESS_LINE=P',
74 upper(nvl(substr(hoi.org_information4,1,60),' ')),
75 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' '))
76 FROM pay_payroll_actions pact,
77 hr_organization_information hoi
78 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
79 ('TRANSFER_PAYROLL_ACTION_ID')
80 AND hoi.org_information_context = 'Tax Details References'
81 AND substr(pact.legislative_parameters,
82 instr(pact.legislative_parameters,'TAX_REF=') + 8,
83 instr(pact.legislative_parameters||' ',' ',
84 instr(pact.legislative_parameters,'TAX_REF=')+8)
85 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
86 = hoi.org_information1
87 AND hoi.organization_id = pact.business_group_id;
88
89
90 ------------------------------
91 -- Employee Details Cursor --
92 ------------------------------
93 CURSOR c_emp_details IS
94 SELECT
95 'PERSON_ID=P', pai.action_information1,
96 'ADDRESS_LINE1=P', nvl(max(action_information6), ' '),
97 'ADDRESS_LINE2=P', nvl(max(action_information7), ' '),
98 'ADDRESS_LINE3=P', nvl(max(action_information8), ' '),
99 'COUNTY=P', nvl(max(action_information10), ' '),
100 'FIRST_NAME=P', nvl(max(action_information3), ' '),
101 'LAST_NAME=P', max(action_information2),
102 'NI_NO=P', max(action_information5),
103 'POSTAL_CODE=P', ' ', -- bug 5169434 not to output post code
104 'TITLE=P', nvl(max(action_information4), ' '),
105 'TOWN_OR_CITY=P', nvl(max(action_information9), ' ')
106 FROM pay_assignment_actions act_edi,
107 ----------------------------------------------------------
108 -- Commented out following joins to fix performance issue
109 -- raised in bug 3374673.
110 -- pay_action_interlocks pail,
111 -- pay_assignment_actions act,
112 ----------------------------------------------------------
113 -- Bug 4059844 - SQL Repository Perf
114 -- pay_payroll_actions pact,
115 pay_action_information pai
116 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
117 ('TRANSFER_PAYROLL_ACTION_ID')
118 AND act_edi.assignment_action_id = pai.action_context_id
119 AND pai.action_context_type = 'AAP'
120 AND pai.action_information_category = 'GB P46 CAR EDI EMPLOYEE DETAIL'
121 GROUP BY pai.action_information1
122 ORDER BY 14, 12;
123
124 --------------------------------
125 -- Allocation Details Cursor --
126 --------------------------------
127 CURSOR c_allocation_details IS
128 SELECT distinct
129 'VEHICLE_ALLOCATION_ID=P', pai.action_information2,
130 'ACTION_FLAG=P', pai.action_information1,
131 'VEHICLE_ALLOCATION_EFF_START_DATE=P', pai.action_information3,
132 'VEHICLE_ALLOCATION_EFF_END_DATE=P', pai.action_information4,
133 'VEHICLE_REPOSITORY_ID=P', pai.action_information5,
134 'P46_REPLACED_VEHICLE_ALLOCATION_ID=P', nvl(action_information20, ' '),
135 'P46_REPLACED_VEHICLE_ALLOC_EFF_END_DATE=P', nvl(action_information21, ' '),
136 'P46_SECOND_CAR_FLAG=P', nvl(action_information15, ' '),
137 'CAR_LIST_PRICE=P', action_information6,
138 'PRICE_OF_ACCESSORIES=P', action_information7,
139 'EMPLOYEE_CAPITAL_CONTRIBUTIONS=P', action_information8,
140 'EMPLOYEE_PRIVATE_CONTRIBUTIONS=P', action_information9,
141 'REPLACED_CAR_MAKE_AND_MODEL=P', nvl(action_information18, ' '),
142 'ENGINE_SIZE_OF_CAR_REPLACED=P', nvl(action_information19, ' '),
143 'CAR_MAKE_AND_MODEL=P', action_information16,
144 'ENGINE_SIZE_OF_CAR=P', action_information17,
145 'FUEL_TYPE=P', action_information10,
146 'CO2_EMISSIONS_FIGURE=P', action_information11,
147 'FUEL_FOR_PRIVATE_USE_FLAG=P', action_information12,
148 'DATE_CAR_FIRST_REGISTERED=P', action_information13,
149 'DATE_CAR_FIRST_AVAILABLE=P', nvl(action_information14, ' ')
150 FROM pay_assignment_actions act_edi,
151 per_all_assignments_f asg,
152 pay_action_information pai
153 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
154 ('TRANSFER_PAYROLL_ACTION_ID')
155 AND asg.person_id = pay_magtape_generic.get_parameter_value(
156 'PERSON_ID')
157 AND act_edi.assignment_id = asg.assignment_id
158 AND act_edi.assignment_action_id = pai.action_context_id
159 AND pai.action_context_type = 'AAP'
160 AND pai.action_information_category = 'GB P46 CAR EDI ALLOCATION'
161 ORDER BY 2, 6, 4;
162 --
163
164 -- For bug 6652235
165 -----------------------------
166 -- Employer Header Cursor V2 --
167 -----------------------------
168 CURSOR c_employer_header_v2 IS
169 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
170 'RECEIVER_ID=P', 'HMRC',
171 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
172 (legislative_parameters,
173 'TEST'),'Y','1',' '),
174 'URGENT_MARKER=P', ' ',/*2008*/
175 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
176 'FORM_TYPE=P', '11',
177 'FORM_TYPE_MEANING=P', 'P46 Car EDI',
178 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
179 'TAX_DIST_REF=P',
180 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086307 */
181 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
182 'EMPLOYERS_ADDRESS_LINE=P',
183 upper(nvl(substr(hoi.org_information4,1,60),' ')),
184 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
185 'TAX_YEAR=P', to_char(to_char(to_date(substr(pact.legislative_parameters,
186 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),'YYYY')
187 + decode(sign(to_date(substr(pact.legislative_parameters,
188 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD')
189 -to_date('06-04-'|| to_char(to_date(substr(pact.legislative_parameters,
190 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),
191 'YYYY'),'DD-MM-YYYY')), -1,0,1))
192 FROM pay_payroll_actions pact,
193 hr_organization_information hoi
194 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
195 ('TRANSFER_PAYROLL_ACTION_ID')
196 AND hoi.org_information_context = 'Tax Details References'
197 AND substr(pact.legislative_parameters,
198 instr(pact.legislative_parameters,'TAX_REF=') + 8,
199 instr(pact.legislative_parameters||' ',' ',
200 instr(pact.legislative_parameters,'TAX_REF=')+8)
201 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
202 = hoi.org_information1
203 AND hoi.organization_id = pact.business_group_id;
204
205
206 ------------------------------
207 -- Employee Details Cursor V2 --
208 ------------------------------
209 CURSOR c_emp_details_v2 IS
210 SELECT
211 'ASSIGNMENT_ACTION_ID=C',max(act_edi.assignment_action_id),
212 'PERSON_ID=P', pai.action_information1,
213 'ADDRESS_LINE1=P', nvl(max(action_information6), ' '),
214 'ADDRESS_LINE2=P', nvl(max(action_information7), ' '),
215 'ADDRESS_LINE3=P', nvl(max(action_information8), ' '),
216 'COUNTY=P', nvl(max(action_information10), ' '),
217 'FIRST_NAME=P', nvl(max(action_information3), ' '),
218 'LAST_NAME=P', max(action_information2),
219 'NI_NO=P', max(action_information5),
220 'POSTAL_CODE=P', ' ', -- bug 5169434 not to output post code
221 'TITLE=P', nvl(max(action_information4), ' '),
222 'TOWN_OR_CITY=P', nvl(max(action_information9), ' '),
223 'DOB=P', nvl(max(ACTION_INFORMATION12),' '),
224 'GENDER=P',nvl(max(ACTION_INFORMATION13), ' ')
225 FROM pay_assignment_actions act_edi,
226 ----------------------------------------------------------
227 -- Commented out following joins to fix performance issue
228 -- raised in bug 3374673.
229 -- pay_action_interlocks pail,
230 -- pay_assignment_actions act,
231 ----------------------------------------------------------
232 -- Bug 4059844 - SQL Repository Perf
233 -- pay_payroll_actions pact,
234 pay_action_information pai
235 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
236 ('TRANSFER_PAYROLL_ACTION_ID')
237 AND act_edi.assignment_action_id = pai.action_context_id
238 AND pai.action_context_type = 'AAP'
239 AND pai.action_information_category = 'GB P46 CAR EDI EMPLOYEE DETAIL'
240 GROUP BY pai.action_information1
241 ORDER BY 14, 12;
242
243 --------------------------------
244 -- Allocation Details Cursor V2 --
245 --------------------------------
246 CURSOR c_allocation_details_v2 IS
247 SELECT distinct
248 'VEHICLE_ALLOCATION_ID=P', pai.action_information2,
249 'ACTION_FLAG=P', pai.action_information1,
250 'VEHICLE_ALLOCATION_EFF_START_DATE=P', pai.action_information3,
251 'VEHICLE_ALLOCATION_EFF_END_DATE=P', pai.action_information4,
252 'VEHICLE_REPOSITORY_ID=P', pai.action_information5,
253 'P46_REPLACED_VEHICLE_ALLOCATION_ID=P', nvl(action_information20, ' '),
254 'P46_REPLACED_VEHICLE_ALLOC_EFF_END_DATE=P', nvl(action_information21, ' '),
255 'P46_SECOND_CAR_FLAG=P', nvl(action_information15, ' '),
256 'CAR_LIST_PRICE=P', action_information6,
257 'PRICE_OF_ACCESSORIES=P', action_information7,
258 'EMPLOYEE_CAPITAL_CONTRIBUTIONS=P', action_information8,
259 'EMPLOYEE_PRIVATE_CONTRIBUTIONS=P', action_information9,
260 'REPLACED_CAR_MAKE_AND_MODEL=P', nvl(action_information18, ' '),
261 'ENGINE_SIZE_OF_CAR_REPLACED=P', nvl(action_information19, ' '),
262 'CAR_MAKE_AND_MODEL=P', action_information16,
263 'ENGINE_SIZE_OF_CAR=P', action_information17,
264 'FUEL_TYPE=P', action_information10,
265 'CO2_EMISSIONS_FIGURE=P', action_information11,
266 'FUEL_FOR_PRIVATE_USE_FLAG=P', action_information12,
267 'DATE_CAR_FIRST_REGISTERED=P', action_information13,
268 'DATE_CAR_FIRST_AVAILABLE=P', nvl(action_information14, ' ')
269 FROM pay_assignment_actions act_edi,
270 per_all_assignments_f asg,
271 pay_action_information pai
272 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
273 ('TRANSFER_PAYROLL_ACTION_ID')
274 AND asg.person_id = pay_magtape_generic.get_parameter_value(
275 'PERSON_ID')
276 AND act_edi.assignment_id = asg.assignment_id
277 AND act_edi.assignment_action_id = pai.action_context_id
278 AND pai.action_context_type = 'AAP'
279 AND pai.action_information_category = 'GB P46 CAR EDI ALLOCATION'
280 ORDER BY 2, 6, 4;
281 --
282
283 level_cnt NUMBER;
284
285 END pay_gb_edi_p46_car;