[Home] [Help]
PACKAGE BODY: APPS.ZX_P2P_DEF_AP_PREUPG
Source
1 PACKAGE BODY ZX_P2P_DEF_AP_PREUPG AS
2 /* $Header: zxappreupgb.pls 120.0 2006/04/05 12:14:01 asengupt noship $ */
3
4 PG_DEBUG CONSTANT VARCHAR(1) := 'Y';
5 ID_CLASH VARCHAR2(1) default NULL;
6
7 l_multi_org_flag FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
8 l_org_id NUMBER(15);
9
10
11 /*===========================================================================+
12 | Procedure : OU_EXTRACT |
13 | |
14 | |
15 | Description: This procedure is a part of party tax |
16 | profile migration which does the data |
17 | migration for Operating Unit details. |
18 | |
19 | |
20 | ARGUMENTS : |
21 | |
22 | |
23 | NOTES : Handle case for Non-Multi Org Environments |
24 | |
25 | |
26 | MODIFICATION HISTORY |
27 | |
28 | 06-Mar-06 Arnab Sengupta Created. |
29 | |
30 | |
31 +===========================================================================*/
32
33 PROCEDURE OU_EXTRACT(p_party_id in NUMBER) IS
34 BEGIN
35
36 NULL;
37 EXCEPTION
38 WHEN OTHERS THEN
39 arp_util_tax.debug('Exception: Error Occurred during Operating Units Extract in PTP'||SQLERRM );
40
41 END;
42
43 /*===========================================================================+
44 | Procedure : load_results_for_ap |
45 | |
46 | |
47 | Description: This procedure is used to load data |
48 | into zx_update_criteria_results |
49 | which is the driving table for rates data load |
50 | |
51 | |
52 | ARGUMENTS : |
53 | |
54 | |
55 | MODIFICATION HISTORY |
56 | |
57 | 06-Mar-06 Arnab Sengupta Created. |
58 | |
59 | |
60 +===========================================================================*/
61 PROCEDURE load_results_for_ap (p_tax_id NUMBER) AS
62 BEGIN
63 NULL;
64 END load_results_for_ap;
65
66 /*===========================================================================+
67 | PROCEDURE |
68 | migrate_normal_tax_codes |
69 | |
70 | DESCRIPTION |
71 | This routine processes AP normal Tax codes and inserts appropriate |
72 | data into the following zx base tables. |
73 | ZX_RATES_B |
74 | ZX_RATES_TL |
75 | ZX_ACCOUNTS |
76 | |
77 | SCOPE - PUBLIC |
78 | |
79 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
80 | |
81 | CALLED FROM |
82 | migrate_ap_tax_codes_setup |
83 | NOTES |
84 | |
85 | MODIFICATION HISTORY |
86 | 24-Dec-03 Srinivas Lokam Created. |
87 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions |
88 | in SELECT statements for handling |
89 | SYNC process. |
90 |==========================================================================*/
91
92 PROCEDURE Migrate_Normal_Tax_Codes(p_tax_id IN NUMBER DEFAULT NULL) IS
93 BEGIN
94 null;
95
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 IF PG_DEBUG = 'Y' THEN
100 arp_util_tax.debug('EXCEPTION: Migrate_normal_tax_codes ');
101 arp_util_tax.debug(sqlerrm);
102 arp_util_tax.debug('Migrate_Normal_Tax_Codes(-)');
103 END IF;
104 --app_exception.raise_exception;
105
106
107 END migrate_normal_tax_codes;
108
109
110
111 /*===========================================================================+
112 | PROCEDURE |
113 | migrate_assign_offset_codes |
114 | |
115 | DESCRIPTION |
116 | This routine processes assigned OFFSET Tax codes and inserts |
117 | appropriate data into the following zx base tables. |
118 | ZX_RATES_B |
119 | ZX_RATES_TL |
120 | ZX_ACCOUNTS |
121 | |
122 | SCOPE - PUBLIC |
123 | |
124 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
125 | |
126 | CALLED FROM |
127 | migrate_ap_tax_codes_setup |
128 | NOTES |
129 | |
130 | MODIFICATION HISTORY |
131 | 24-Dec-03 Srinivas Lokam Created. |
132 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions |
133 | in SELECT statements for handling |
134 | SYNC process. |
135 | |
136 |==========================================================================*/
137
138
139
140 PROCEDURE migrate_assign_offset_codes(p_tax_id IN NUMBER DEFAULT NULL) IS
141 BEGIN
142
143 NULL;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 IF PG_DEBUG = 'Y' THEN
148 arp_util_tax.debug('EXCEPTION: Migrate_assign_offset_codes ');
149 arp_util_tax.debug(sqlerrm);
150 arp_util_tax.debug('Migrate_Assign_Offset_Codes(-)');
151 END IF;
152 --app_exception.raise_exception;
153
154 END migrate_assign_offset_codes;
155
156 /*===========================================================================+
157 | PROCEDURE |
158 | migrate_unassign_offset_codes |
159 | |
160 | DESCRIPTION |
161 | This routine processes unassigned OFFSET Tax codes and inserts |
162 | appropriate data into the following zx base tables. |
163 | ZX_RATES_B |
164 | ZX_RATES_TL |
165 | ZX_ACCOUNTS |
166 | |
167 | SCOPE - PUBLIC |
168 | |
169 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
170 | |
171 | CALLED FROM |
172 | migrate_ap_tax_codes_setup |
173 | NOTES |
174 | |
175 | MODIFICATION HISTORY |
176 | 24-Dec-03 Srinivas Lokam Created. |
177 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions |
178 | in SELECT statements for handling |
179 | SYNC process. |
180 | |
181 |==========================================================================*/
182
183
184 PROCEDURE migrate_unassign_offset_codes(p_tax_id IN NUMBER DEFAULT NULL) IS
185 BEGIN
186 null;
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 IF PG_DEBUG = 'Y' THEN
191 arp_util_tax.debug('EXCEPTION: Migrate_unassign_offset_codes ');
192 arp_util_tax.debug(sqlerrm);
193 arp_util_tax.debug('Migrate_UnAssign_Offset_Codes(-)');
194 END IF;
195 --app_exception.raise_exception;
196 END migrate_unassign_offset_codes;
197
198 /*===========================================================================+
199 | PROCEDURE |
200 | migrate_recovery_rates |
201 | |
202 | DESCRIPTION |
203 | This routine processes distinct recovery rates,inserts appropriate |
204 | data into the following zx base tables. |
205 | ZX_RATES_B |
206 | ZX_RATES_TL |
207 | ZX_ACCOUNTS |
208 | |
209 | SCOPE - PUBLIC |
210 | |
211 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
212 | |
213 | CALLED FROM |
214 | migrate_ap_tax_codes_setup |
215 | NOTES |
216 | |
217 | MODIFICATION HISTORY |
218 | 24-Dec-03 Srinivas Lokam Created. |
219 | 30-Jan-04 Srinivas Lokam Added INPUT parameters,AND conditions |
220 | in SELECT statements for handling |
221 | SYNC process. |
222 | |
223 |==========================================================================*/
224
225
226 PROCEDURE migrate_recovery_rates(p_tax_id IN NUMBER DEFAULT NULL) IS
227 BEGIN
228
229 null;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 IF PG_DEBUG = 'Y' THEN
234 arp_util_tax.debug('EXCEPTION: Migrate_recovery_rates ');
235 arp_util_tax.debug(sqlerrm);
236 arp_util_tax.debug('Migrate_Recovery_Rates(-)');
237 END IF;
238 --app_exception.raise_exception;
239 END migrate_recovery_rates;
240
241
242 /*===========================================================================+
243 | PROCEDURE |
244 | migrate_disabled_tax_codes |
245 | |
246 | DESCRIPTION |
247 | This routine is used to migrate disabled tax codes with overlapping |
248 | into zx_rates_b |
249 | |
250 | SCOPE - PUBLIC |
251 | |
252 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
253 | |
254 | CALLED FROM |
255 | migrate_disabled_tax_codes |
256 | NOTES |
257 | |
258 | MODIFICATION HISTORY |
259 | 30-Sep-05 Arnab Sengupta Created. |
260 | |
261 |==========================================================================*/
262
263 PROCEDURE migrate_disabled_tax_codes(p_tax_id IN NUMBER DEFAULT NULL) IS
264
268 l_max_end_date date;
265 TYPE tax_id_table is table of ap_tax_codes_all.tax_id%TYPE index by BINARY_INTEGER;
266 tax_id_tab tax_id_table ;
267 l_min_start_date date;
269
270
271
272 /*The purpose of the following cursor is to pick up data sets in which tax codes are disabled
273 and these records have identical org_id set_of_books_id and name but differ only in their
274 effective from and effective to dates .These date ranges however overlap*/
275
276 /* Sample Data Set
277
278 ORG_ID SOB NAME START_DATE INACTIVE_DATE ENABLED_FLAG TAX_RATE
279 ====== === ==== ========== ============= ============ ========
280 204 1 CA-Sales 04-JAN-51 07-JAN-51 N 0
281 204 1 CA-Sales NULL NULL N 10
282 204 1 CA-Sales 01-JAN-51 11-JAN-51 N 15
283
284 Records 1 and 3 are a case of overlap */
285
286
287
288
289 CURSOR tax_id_csr
290 IS
291 select aptax2.tax_id tax_id
292 from
293 (
294 select DISTINCT org_id,set_of_books_id,name
295 from ap_tax_codes_all a
296 where a.enabled_flag = 'N'
297 and exists
298 (
299 select 1 from ap_tax_codes_all b
300 where a.org_id = b.org_id
301 and a.set_of_books_id = b.set_of_books_id
302 and a.name = b.name
303 and
304 ( ( Nvl(a.START_DATE,l_min_start_date) > Nvl(b.START_DATE,l_min_start_date)
305 and Nvl(a.INACTIVE_DATE,l_max_end_date) < Nvl(b.INACTIVE_DATE,l_max_end_date))
306
307 or ( Nvl(a.START_DATE,l_min_start_date) < Nvl(b.START_DATE,l_min_start_date)
308 and Nvl(a.INACTIVE_DATE,l_max_end_date) > Nvl(b.START_DATE,l_max_end_date)
309 and Nvl(a.INACTIVE_DATE,l_max_end_date) <Nvl(b.INACTIVE_DATE,l_max_end_date))
310
311 or ( Nvl(a.START_DATE,l_min_start_date) > Nvl(b.START_DATE,l_min_start_date)
312 and Nvl(a.START_DATE,l_min_start_date) <Nvl(b.INACTIVE_DATE,l_max_end_date)
313 and Nvl(a.INACTIVE_DATE,l_max_end_date) >Nvl(b.INACTIVE_DATE,l_max_end_date))
314 )
315 and b.enabled_flag = 'N'
316 )
317 and exists
318 (select c.org_id,c.set_of_books_id,c.name ,count(c.org_id) from ap_tax_codes_all c
319 where a.org_id = c.org_id
320 and a.set_of_books_id = c.set_of_books_id
321 and a.name = c.name
322 group by c.org_id,c.set_of_books_id,c.name
323 having count(c.org_id) > 1)
324 )
325 aptax1,
326 ap_tax_codes_all aptax2
327 where
328 aptax1.org_id = aptax2.org_id
329 and aptax1.set_of_books_id = aptax2.set_of_books_id
330 and aptax1. name = aptax2. name
331 ;
332
333
334 BEGIN
335 NULL;
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 IF PG_DEBUG = 'Y' THEN
340 arp_util_tax.debug('EXCEPTION: Migrate_disabled_tax_codes ');
341 arp_util_tax.debug(sqlerrm);
342 arp_util_tax.debug('Migrate_disabled_Tax_Codes(-)');
343 END IF;
344 --app_exception.raise_exception;
345
346
347 END migrate_disabled_tax_codes;
348
349 /*===========================================================================+
350 | Procedure : PRE_UPGRADE_WRAPPER |
351 | |
352 | |
353 | Description: This is the wrapper procedure for populating |
354 | the relevant zx entities that ap would require |
355 | during their pre upgrade run |
356 | |
357 | |
358 | ARGUMENTS : |
359 | |
360 | |
361 | |
362 | MODIFICATION HISTORY |
363 | |
364 | 06-Mar-06 Arnab Sengupta Created. |
365 | |
366 | |
367 +===========================================================================*/
368
369
370 PROCEDURE pre_upgrade_wrapper
371 IS
372 BEGIN
373
374 NULL;
375
376 EXCEPTION
377 WHEN OTHERS THEN
378
379 arp_util_tax.debug('EXCEPTION: pre_upgrade_wrapper ');
380 arp_util_tax.debug(sqlerrm);
381 arp_util_tax.debug('pre_upgrade_wrapper(-)');
382 END pre_upgrade_wrapper;
383
384 /*===========================================================================+
385 | Procedure : PRE_UPGRADE_WRAPPER |
386 | |
387 | |
388 | Description: This is the wrapper procedure for syching up |
389 | the relevant rates entities |
390 | |
391 | |
392 | |
393 | ARGUMENTS : |
394 | |
395 | |
396 | |
397 | MODIFICATION HISTORY |
398 | |
399 | 06-Mar-06 Arnab Sengupta Created. |
400 | |
401 | |
402 +===========================================================================*/
403 PROCEDURE rates_sync_wrapper(p_tax_id IN NUMBER DEFAULT NULL)
404 IS
405 BEGIN
406
407 NULL;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411
412 arp_util_tax.debug('EXCEPTION: pre_upgrade_wrapper ');
413 arp_util_tax.debug(sqlerrm);
414 arp_util_tax.debug('pre_upgrade_wrapper(-)');
415
416
417 END;
418
419 /*Constructor Code*/
420 BEGIN
421 BEGIN
422
423 SELECT 'Y' INTO ID_CLASH FROM DUAL
424 WHERE EXISTS (select 1
425 from ap_tax_codes_all,
426 ar_vat_tax_all_b
427 where tax_id = vat_tax_id);
428 EXCEPTION
429 WHEN no_data_found THEN
430 arp_util_tax.debug('No data found exception encountered for tax definition in constructor :'||sqlerrm);
431
432 WHEN OTHERS THEN
433 arp_util_tax.debug('Exception in Constructor for AP tax definition :'||sqlerrm);
434 END;
435
436 BEGIN
437
438 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
439 FND_PRODUCT_GROUPS;
440
441 IF L_MULTI_ORG_FLAG = 'N' THEN
442
443 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
444
445 IF L_ORG_ID IS NULL THEN
446 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
447 END IF;
448 ELSE
449 L_ORG_ID := NULL;
450 END IF;
451
452 EXCEPTION
453 WHEN no_data_found THEN
454 arp_util_tax.debug('No data found exception encountered for tax definition in constructor :'||sqlerrm);
455
456 WHEN others THEN
457 arp_util_tax.debug('Exception in Constructor for AP tax definition :'||sqlerrm);
458 END;
459
460
461
462 END ZX_P2P_DEF_AP_PREUPG;