[Home] [Help]
PACKAGE BODY: APPS.ZX_PO_REC_PKG
Source
1 PACKAGE BODY ZX_PO_REC_PKG AS
2 /* $Header: zxpotrxpoprecb.pls 120.14.12020000.2 2012/07/23 13:01:03 srajapar noship $ */
3
4 pg_po_header_id_tab po_header_id_tbl;
5 pg_po_line_id_tab po_line_id_tbl;
6 pg_po_dist_id_tab po_dist_id_tbl;
7 pg_tax_code_id_tab tax_code_id_tbl;
8 pg_trx_date_tab trx_date_tbl;
9 pg_code_combination_id_tab code_combination_id_tbl;
10 pg_vendor_id_tab vendor_id_tbl;
11 pg_tax_rec_override_flag_tab tax_recovery_override_flag_tbl;
12 pg_tax_recovery_rate_tab tax_recovery_rate_tbl;
13 pg_vendor_site_id_tab vendor_site_id_tbl;
14 pg_item_id_tab inv_org_id_tbl;
15 pg_inv_org_id_tab item_id_tbl;
16 pg_chart_of_accounts_id_tab chart_of_accounts_id_tbl;
17 pg_tc_tax_recovery_rule_id_tab tc_tax_recovery_rule_id_tbl;
18 pg_tc_tax_recovery_rate_tab tc_tax_recovery_rate_tbl;
19 pg_vendor_type_lookup_code_tab vendor_type_lookup_code_tbl;
20
21
22 -- pg_get_tax_recovery_rate_tab get_rec_rate_tbl;
23 l_rec_rate number;
24 TYPE get_rec_rate_tbl1 IS TABLE OF PO_DISTRIBUTIONS_ALL.RECOVERY_RATE%TYPE INDEX BY BINARY_INTEGER;
25 pg_get_tax_recovery_rate_tab1 get_rec_rate_tbl1;
26
27 C_LINES_PER_INSERT CONSTANT NUMBER := 5000;
28 i NUMBER;
29 PG_DEBUG varchar2(1) :='Y';
30
31 PROCEDURE get_rec_info(
32 p_start_rowid IN ROWID,
33 p_end_rowid IN ROWID) IS
34
35 CURSOR pod_rec_rate IS
36 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, pol, poll, atc,atg, atc1, pov) */
37 NVL(poll.po_release_id, poh.po_header_id),
38 pod.line_location_id,
39 pod.po_distribution_id,
40 NVL(atg.tax_code_id,atc.tax_id),
41 poh.last_update_date,
42 pod.code_combination_id,
43 poh.vendor_id,
44 pod.tax_recovery_override_flag,
45 pod.recovery_rate,
46 poh.vendor_site_id,
47 pol.item_id,
48 poh.inventory_organization_id,
49 poh.chart_of_accounts_id,
50 atc1.tax_recovery_rule_id,
51 atc1.tax_recovery_rate,
52 pov.vendor_type_lookup_code
53 FROM
54 ( SELECT /*+ ROWID(poh) NO_MERGE swap_join_inputs(fsp) swap_join_inputs(lgr)
55 INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
56 poh.po_header_id, poh.last_update_date,poh.vendor_id,poh.vendor_site_id,
57 fsp.set_of_books_id, fsp.org_id, fsp.inventory_organization_id,
58 lgr.chart_of_accounts_id
59 FROM po_headers_all poh,
60 financials_system_params_all fsp,
61 xla_upgrade_dates upd,
62 gl_ledgers lgr
63 WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
64 AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
65 AND NVL(poh.org_id, -99) = NVL(fsp.org_id, -99)
66 AND upd.ledger_id = fsp.set_of_books_id
67 AND (NVL(poh.closed_code, 'OPEN') = 'OPEN' OR
68 trunc(poh.last_update_date) >= upd.start_date
69 )
70 AND lgr.ledger_id = fsp.set_of_books_id
71 ) poh,
72 po_distributions_all pod,
73 po_line_locations_all poll,
74 po_lines_all pol,
75 ap_tax_codes_all atc,
76 ar_tax_group_codes_all atg,
77 ap_tax_codes_all atc1,
78 po_vendors pov
79 WHERE poh.po_header_id = pod.po_header_id
80 AND pol.po_header_id = poll.po_header_id
81 AND pol.po_line_id = poll.po_line_id
82 AND poll.po_header_id = pod.po_header_id
83 AND poll.po_line_id = pod.po_line_id
84 AND poll.line_location_id = pod.line_location_id
85 AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
86 AND poll.tax_code_id = atc.tax_id(+)
87 AND poll.tax_code_id = atg.tax_group_id(+)
88 --Bug 8352135
89 AND atg.start_date <= poll.last_update_date
90 AND (atg.end_date >= poll.last_update_date OR atg.end_date IS NULL)
91 AND atg.ENABLED_FLAG='Y'
92 AND atc.tax_type = 'TAX_GROUP'
93 AND pod.recovery_rate IS NULL
94 AND atc1.tax_id(+) = atg.tax_code_id
95 AND atc1.enabled_flag(+) = 'Y'
96 AND pov.vendor_id = poh.vendor_id;
97
98 l_count NUMBER;
99 l_tax_rate_id NUMBER;
100
101 BEGIN
102
103 OPEN pod_rec_rate;
104 LOOP
105 FETCH pod_rec_rate BULK COLLECT INTO
106 pg_po_header_id_tab,
107 pg_po_line_id_tab,
108 pg_po_dist_id_tab,
109 pg_tax_code_id_tab,
110 pg_trx_date_tab,
111 pg_code_combination_id_tab,
112 pg_vendor_id_tab,
113 pg_tax_rec_override_flag_tab,
114 pg_tax_recovery_rate_tab,
115 pg_vendor_site_id_tab,
116 pg_item_id_tab,
117 pg_inv_org_id_tab,
118 pg_chart_of_accounts_id_tab,
119 pg_tc_tax_recovery_rule_id_tab,
120 pg_tc_tax_recovery_rate_tab,
121 pg_vendor_type_lookup_code_tab
122 LIMIT C_LINES_PER_INSERT;
123
124 l_count := pg_tax_code_id_tab.COUNT;
125 IF l_count > 0 THEN
126 FOR i IN 1 .. l_count LOOP
127 ZX_TAX_RECOVERY_PKG.get_default_rate(
128 p_tax_code => NULL,
129 p_tax_id => PG_TAX_CODE_ID_TAB(i),
130 p_tax_date => PG_TRX_DATE_TAB(i),
131 p_code_combination_id => PG_CODE_COMBINATION_ID_TAB(i),
132 p_vendor_id => PG_VENDOR_ID_TAB(i),
133 p_distribution_id => null,
134 p_tax_user_override_flag => PG_TAX_REC_OVERRIDE_FLAG_TAB(i),
135 p_user_tax_recovery_rate => PG_TAX_RECOVERY_RATE_TAB(i),
136 p_concatenated_segments => '',
137 p_vendor_site_id => PG_VENDOR_SITE_ID_TAB(i),
138 p_inventory_item_id => PG_ITEM_ID_TAB(i),
139 p_item_org_id => PG_ITEM_ID_TAB(i),
140 APPL_SHORT_NAME => 'PO',
141 FUNC_SHORT_NAME => '',
142 p_calling_sequence => 'PO_MIG',
143 p_chart_of_accounts_id => pg_chart_of_accounts_id_tab(i),
144 p_tc_tax_recovery_rule_id => pg_tc_tax_recovery_rule_id_tab(i),
145 p_tc_tax_recovery_rate => pg_tc_tax_recovery_rate_tab(i),
146 p_vendor_type_lookup_code => pg_vendor_type_lookup_code_tab(i),
147 p_tax_recovery_rate => l_rec_rate );
148
149 pg_get_tax_recovery_rate_tab(i) := l_rec_rate;
150 BEGIN
151 SELECT TAX_RATE_ID INTO l_tax_rate_id
152 FROM ZX_RATES_B
153 WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
154 AND record_type_code='MIGRATED';
155 EXCEPTION
156 WHEN OTHERS THEN
157 NULL;
158 END;
159
160 IF (pg_tax_code_id_tab(i) <> l_tax_rate_id) and l_tax_rate_id is NOT NULL THEN
161 pg_tax_code_id_tab(i) := l_tax_rate_id;
162 END IF;
163
164 END LOOP;
165
166 insert_rec_info;
167 COMMIT;
168 ELSE
169 --Bug 7715015
170 IF pod_rec_rate%ISOPEN THEN
171 CLOSE pod_rec_rate;
172 END IF;
173 EXIT;
174 END IF;
175
176 IF pod_rec_rate%NOTFOUND THEN
177 --Bug 7715015
178 IF pod_rec_rate%ISOPEN THEN
179 CLOSE pod_rec_rate;
180 END IF;
181 EXIT;
182 END IF;
183 END LOOP;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 IF pod_rec_rate%ISOPEN THEN
188 CLOSE pod_rec_rate;
189 END IF;
190 RAISE;
191
192 END get_rec_info;
193
194 PROCEDURE INSERT_REC_INFO
195 IS
196
197 l_count number;
198
199 BEGIN
200
201 -- To handle re-runnability, truncate the table first
202
203 -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ZX.ZX_PO_REC_DIST';
204
205 l_count := NVL(PG_TAX_CODE_ID_TAB.COUNT, 0);
206
207 FORALL i IN 1 .. l_count
208 INSERT INTO ZX_PO_REC_DIST
209 ( PO_HEADER_ID,
210 PO_LINE_LOCATION_ID,
211 PO_DISTRIBUTION_ID,
212 REC_RATE,
213 TAX_RATE_ID,
214 CREATED_BY ,
215 CREATION_DATE ,
216 LAST_UPDATED_BY ,
217 LAST_UPDATE_DATE ,
218 LAST_UPDATE_LOGIN
219 )
220 VALUES
221 (
222 PG_PO_HEADER_ID_TAB(i),
223 PG_PO_LINE_ID_TAB(i),
224 PG_PO_DIST_ID_TAB(i),
225 pg_get_tax_recovery_rate_tab(i),
226 PG_TAX_CODE_ID_TAB(i),
227 1,
228 SYSDATE,
229 1,
230 SYSDATE,
231 1 );
232
233 /* IF PG_DEBUG = 'Y' THEN
234 dbms_output.put_line('after call to PO_REC_DIST_TBL ');
235 END IF; */
236
237 END INSERT_REC_INFO;
238
239 PROCEDURE INIT_REC_GT_TABLES IS
240 BEGIN
241
242 -- PG_PO_HEADER_ID_TAB.DELETE;
243 -- PG_PO_LINE_ID_TAB.DELETE;
244 -- PG_PO_DIST_ID_TAB.DELETE;
245
246 PG_TAX_CODE_ID_TAB.DELETE;
247 PG_TRX_DATE_TAB.DELETE;
248 PG_CODE_COMBINATION_ID_TAB.DELETE;
249 PG_VENDOR_ID_TAB.DELETE;
250 PG_TAX_REC_OVERRIDE_FLAG_TAB.DELETE;
251 PG_TAX_RECOVERY_RATE_TAB.DELETE;
252 PG_VENDOR_SITE_ID_TAB.DELETE;
253 PG_ITEM_ID_TAB.DELETE;
254 PG_INV_ORG_ID_TAB.DELETE;
255 -- pg_get_tax_recovery_rate_tab.DELETE;
256
257 END INIT_REC_GT_TABLES;
258
259 PROCEDURE get_rec_info(
260 p_upg_trx_info_rec IN ZX_ON_FLY_TRX_UPGRADE_PKG.zx_upg_trx_info_rec_type,
261 x_return_status OUT NOCOPY VARCHAR2) AS
262
263 CURSOR pod_rec_rate_po IS
264 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poll,pol, atc,atg, atc1, pov)
265 INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
266 poh.po_header_id,
267 pod.line_location_id,
268 pod.po_distribution_id,
269 NVL(atg.tax_code_id, atc.tax_id),
270 poh.last_update_date,
271 pod.code_combination_id,
272 poh.vendor_id,
273 pod.tax_recovery_override_flag,
274 pod.recovery_rate,
275 poh.vendor_site_id,
276 pol.item_id,
277 fsp.inventory_organization_id,
278 lgr.chart_of_accounts_id,
279 atc1.tax_recovery_rule_id,
280 atc1.tax_recovery_rate,
281 pov.vendor_type_lookup_code
282 FROM po_headers_all poh,
283 po_distributions_all pod,
284 po_line_locations_all poll,
285 po_lines_all pol,
286 ap_tax_codes_all atc,
287 ar_tax_group_codes_all atg,
288 ap_tax_codes_all atc1,
289 po_vendors pov,
290 financials_system_params_all fsp,
291 gl_ledgers lgr
292 WHERE poh.po_header_id = p_upg_trx_info_rec.trx_id
293 AND pod.po_header_id = poh.po_header_id
294 AND pod.recovery_rate IS NULL
295 AND poll.po_header_id = pod.po_header_id
296 AND poll.po_line_id = pod.po_line_id
297 AND poll.line_location_id = pod.line_location_id
298 AND pol.po_header_id = poll.po_header_id
299 AND pol.po_line_id = poll.po_line_id
300 AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
301 AND poll.tax_code_id = atc.tax_id
302 AND poll.tax_code_id = atg.tax_group_id
303 AND atc.tax_type = 'TAX_GROUP'
304 AND atc1.tax_id = atg.tax_code_id
305 AND atc1.enabled_flag = 'Y'
306 AND pov.vendor_id = poh.vendor_id
307 AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
308 AND lgr.ledger_id = fsp.set_of_books_id;
309
310 CURSOR pod_rec_rate_release IS
311 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poh, pol, atc,atg, atc1, pov)
312 INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
313 poll.po_release_id,
314 pod.line_location_id,
315 pod.po_distribution_id,
316 NVL(atg.tax_code_id, atc.tax_id),
317 poh.last_update_date,
318 pod.code_combination_id,
319 poh.vendor_id,
320 pod.tax_recovery_override_flag,
321 pod.recovery_rate,
322 poh.vendor_site_id,
323 pol.item_id,
324 fsp.inventory_organization_id,
325 lgr.chart_of_accounts_id,
326 atc1.tax_recovery_rule_id,
327 atc1.tax_recovery_rate,
328 pov.vendor_type_lookup_code
329 FROM po_line_locations_all poll,
330 po_distributions_all pod,
331 po_headers_all poh,
332 po_lines_all pol,
333 ap_tax_codes_all atc,
334 ar_tax_group_codes_all atg,
335 ap_tax_codes_all atc1,
336 po_vendors pov,
337 financials_system_params_all fsp,
338 gl_ledgers lgr
339 WHERE poll.po_release_id = p_upg_trx_info_rec.trx_id
340 AND pod.po_header_id = poll.po_header_id
341 AND pod.po_line_id = poll.po_line_id
342 AND pod.line_location_id = poll.line_location_id
343 AND pod.recovery_rate IS NULL
344 AND poh.po_header_id = poll.po_header_id
345 AND pol.po_header_id = poll.po_header_id
346 AND pol.po_line_id = poll.po_line_id
347 AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
348 AND poll.tax_code_id = atc.tax_id
349 AND poll.tax_code_id = atg.tax_group_id
350 AND atc.tax_type = 'TAX_GROUP'
351 AND atc1.tax_id = atg.tax_code_id
352 AND atc1.enabled_flag = 'Y'
353 AND pov.vendor_id = poh.vendor_id
354 AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
355 AND lgr.ledger_id = fsp.set_of_books_id;
356
357 l_count NUMBER;
358 l_tax_rate_id NUMBER;
359
360 BEGIN
361
362 IF p_upg_trx_info_rec.entity_code = 'PURCHASE_ORDER' THEN
363 OPEN pod_rec_rate_po;
364 LOOP
365 FETCH pod_rec_rate_po BULK COLLECT INTO
366 pg_po_header_id_tab,
367 pg_po_line_id_tab,
368 pg_po_dist_id_tab,
369 pg_tax_code_id_tab,
370 pg_trx_date_tab,
371 pg_code_combination_id_tab,
372 pg_vendor_id_tab,
373 pg_tax_rec_override_flag_tab,
374 pg_tax_recovery_rate_tab,
375 pg_vendor_site_id_tab,
376 pg_item_id_tab,
377 pg_inv_org_id_tab,
378 pg_chart_of_accounts_id_tab,
379 pg_tc_tax_recovery_rule_id_tab,
380 pg_tc_tax_recovery_rate_tab,
381 pg_vendor_type_lookup_code_tab
382 LIMIT C_LINES_PER_INSERT;
383
384 l_count := pg_tax_code_id_tab.COUNT;
385 IF l_count > 0 THEN
386 FOR i IN 1 .. l_count LOOP
387 ZX_TAX_RECOVERY_PKG.get_default_rate(
388 p_tax_code => NULL,
389 p_tax_id => PG_TAX_CODE_ID_TAB(i),
390 p_tax_date => PG_TRX_DATE_TAB(i),
391 p_code_combination_id => PG_CODE_COMBINATION_ID_TAB(i),
392 p_vendor_id => PG_VENDOR_ID_TAB(i),
393 p_distribution_id => null,
394 p_tax_user_override_flag => PG_TAX_REC_OVERRIDE_FLAG_TAB(i),
395 p_user_tax_recovery_rate => PG_TAX_RECOVERY_RATE_TAB(i),
396 p_concatenated_segments => '',
397 p_vendor_site_id => PG_VENDOR_SITE_ID_TAB(i),
398 p_inventory_item_id => PG_ITEM_ID_TAB(i),
399 p_item_org_id => PG_ITEM_ID_TAB(i),
400 APPL_SHORT_NAME => 'PO',
401 FUNC_SHORT_NAME => '',
402 p_calling_sequence => 'PO_MIG',
403 p_chart_of_accounts_id => pg_chart_of_accounts_id_tab(i),
404 p_tc_tax_recovery_rule_id => pg_tc_tax_recovery_rule_id_tab(i),
405 p_tc_tax_recovery_rate => pg_tc_tax_recovery_rate_tab(i),
406 p_vendor_type_lookup_code => pg_vendor_type_lookup_code_tab(i),
407 p_tax_recovery_rate => l_rec_rate );
408
409 pg_get_tax_recovery_rate_tab(i) := l_rec_rate;
410 BEGIN
411 SELECT TAX_RATE_ID INTO l_tax_rate_id
412 FROM ZX_RATES_B
413 WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
414 AND record_type_code='MIGRATED';
415 EXCEPTION
416 WHEN OTHERS THEN
417 NULL;
418 END;
419
420 IF (pg_tax_code_id_tab(i) <> l_tax_rate_id) and l_tax_rate_id is NOT NULL THEN
421 pg_tax_code_id_tab(i) := l_tax_rate_id;
422 END IF;
423
424 END LOOP;
425
426 insert_rec_info;
427 COMMIT;
428 ELSE
429 CLOSE pod_rec_rate_po;
430 EXIT;
431 END IF;
432
433 IF pod_rec_rate_po%NOTFOUND THEN
434 CLOSE pod_rec_rate_po;
435 EXIT;
436 END IF;
437 END LOOP;
438
439 ELSIF p_upg_trx_info_rec.entity_code = 'RELEASE' THEN
440 OPEN pod_rec_rate_release;
441 LOOP
442 FETCH pod_rec_rate_release BULK COLLECT INTO
443 pg_po_header_id_tab,
444 pg_po_line_id_tab,
445 pg_po_dist_id_tab,
446 pg_tax_code_id_tab,
447 pg_trx_date_tab,
448 pg_code_combination_id_tab,
449 pg_vendor_id_tab,
450 pg_tax_rec_override_flag_tab,
451 pg_tax_recovery_rate_tab,
452 pg_vendor_site_id_tab,
453 pg_item_id_tab,
454 pg_inv_org_id_tab,
455 pg_chart_of_accounts_id_tab,
456 pg_tc_tax_recovery_rule_id_tab,
457 pg_tc_tax_recovery_rate_tab,
458 pg_vendor_type_lookup_code_tab
459 LIMIT C_LINES_PER_INSERT;
460
461 l_count := pg_tax_code_id_tab.COUNT;
462
463 IF l_count > 0 THEN
464 FOR i IN 1 .. l_count LOOP
465 ZX_TAX_RECOVERY_PKG.get_default_rate(
466 p_tax_code => NULL,
467 p_tax_id => PG_TAX_CODE_ID_TAB(i),
468 p_tax_date => PG_TRX_DATE_TAB(i),
469 p_code_combination_id => PG_CODE_COMBINATION_ID_TAB(i),
470 p_vendor_id => PG_VENDOR_ID_TAB(i),
471 p_distribution_id => null,
472 p_tax_user_override_flag => PG_TAX_REC_OVERRIDE_FLAG_TAB(i),
473 p_user_tax_recovery_rate => PG_TAX_RECOVERY_RATE_TAB(i),
474 p_concatenated_segments => '',
475 p_vendor_site_id => PG_VENDOR_SITE_ID_TAB(i),
476 p_inventory_item_id => PG_ITEM_ID_TAB(i),
477 p_item_org_id => PG_ITEM_ID_TAB(i),
478 APPL_SHORT_NAME => 'PO',
479 FUNC_SHORT_NAME => '',
480 p_calling_sequence => 'PO_MIG',
481 p_chart_of_accounts_id => pg_chart_of_accounts_id_tab(i),
482 p_tc_tax_recovery_rule_id => pg_tc_tax_recovery_rule_id_tab(i),
483 p_tc_tax_recovery_rate => pg_tc_tax_recovery_rate_tab(i),
484 p_vendor_type_lookup_code => pg_vendor_type_lookup_code_tab(i),
485 p_tax_recovery_rate => l_rec_rate );
486
487 pg_get_tax_recovery_rate_tab(i) := l_rec_rate;
488 BEGIN
489 SELECT TAX_RATE_ID INTO l_tax_rate_id
490 FROM ZX_RATES_B
491 WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
492 AND record_type_code='MIGRATED';
493 EXCEPTION
494 WHEN OTHERS THEN
495 NULL;
496 END;
497
498 IF (pg_tax_code_id_tab(i) <> l_tax_rate_id) and l_tax_rate_id is NOT NULL THEN
499 pg_tax_code_id_tab(i) := l_tax_rate_id;
500 END IF;
501 END LOOP;
502
503 insert_rec_info;
504 COMMIT;
505 ELSE
506 CLOSE pod_rec_rate_release;
507 EXIT;
508 END IF;
509
510 IF pod_rec_rate_po%NOTFOUND THEN
511 CLOSE pod_rec_rate_release;
512 EXIT;
513 END IF;
514 END LOOP;
515 END IF;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 IF pod_rec_rate_po%ISOPEN THEN
520 CLOSE pod_rec_rate_po;
521 END IF;
522
523 IF pod_rec_rate_release%ISOPEN THEN
524 CLOSE pod_rec_rate_release;
525 END IF;
526 RAISE;
527
528 END get_rec_info;
529
530 PROCEDURE get_rec_info(
531 x_return_status OUT NOCOPY VARCHAR2) AS
532
533 CURSOR pod_rec_rate IS
534 SELECT /*+ ORDERED NO_EXPAND use_nl(pod,poll,pol, atc,atg, atc1, pov)
535 INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
536 poh.po_header_id,
537 pod.line_location_id,
538 pod.po_distribution_id,
539 NVL(atg.tax_code_id,atc.tax_id),
540 poh.last_update_date,
541 pod.code_combination_id,
542 poh.vendor_id,
543 pod.tax_recovery_override_flag,
544 pod.recovery_rate,
545 poh.vendor_site_id,
546 pol.item_id,
547 fsp.inventory_organization_id,
548 lgr.chart_of_accounts_id,
549 atc1.tax_recovery_rule_id,
550 atc1.tax_recovery_rate,
551 pov.vendor_type_lookup_code
552 FROM (select distinct other_doc_application_id, other_doc_trx_id
553 from ZX_VALIDATION_ERRORS_GT
554 where other_doc_application_id = 201
555 and other_doc_entity_code = 'PURCHASE_ORDER'
556 and other_doc_event_class_code = 'PO_PA'
557 ) zxvalerr,
558 po_headers_all poh,
559 po_distributions_all pod,
560 po_line_locations_all poll,
561 po_lines_all pol,
562 ap_tax_codes_all atc,
563 ar_tax_group_codes_all atg,
564 ap_tax_codes_all atc1,
565 po_vendors pov,
566 financials_system_params_all fsp,
567 gl_ledgers lgr
568 WHERE poh.po_header_id = zxvalerr.other_doc_trx_id
569 AND pod.po_header_id = poh.po_header_id
570 AND pod.recovery_rate IS NULL
571 AND poll.po_header_id = pod.po_header_id
572 AND poll.po_line_id = pod.po_line_id
573 AND poll.line_location_id = pod.line_location_id
574 AND pol.po_header_id = poll.po_header_id
575 AND pol.po_line_id = poll.po_line_id
576 AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
577 AND poll.tax_code_id = atc.tax_id
578 AND poll.tax_code_id = atg.tax_group_id
579 AND atc.tax_type = 'TAX_GROUP'
580 AND atc1.tax_id = atg.tax_code_id
581 AND atc1.enabled_flag = 'Y'
582 AND pov.vendor_id = poh.vendor_id
583 AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
584 AND lgr.ledger_id = fsp.set_of_books_id
585 UNION
586 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poh, pol, atc,atg, atc1, pov)
587 INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
588 poll.po_release_id,
589 pod.line_location_id,
590 pod.po_distribution_id,
591 NVL(atg.tax_code_id,atc.tax_id),
592 poh.last_update_date,
593 pod.code_combination_id,
594 poh.vendor_id,
595 pod.tax_recovery_override_flag,
596 pod.recovery_rate,
597 poh.vendor_site_id,
598 pol.item_id,
599 fsp.inventory_organization_id,
600 lgr.chart_of_accounts_id,
601 atc1.tax_recovery_rule_id,
602 atc1.tax_recovery_rate,
603 pov.vendor_type_lookup_code
604 FROM (select distinct other_doc_application_id, other_doc_trx_id
605 from ZX_VALIDATION_ERRORS_GT
606 where other_doc_application_id = 201
607 and other_doc_entity_code = 'RELEASE'
608 and other_doc_event_class_code = 'RELEASE'
609 ) zxvalerr,
610 po_line_locations_all poll,
611 po_headers_all poh,
612 po_distributions_all pod,
613 po_lines_all pol,
614 ap_tax_codes_all atc,
615 ar_tax_group_codes_all atg,
616 ap_tax_codes_all atc1,
617 po_vendors pov,
618 financials_system_params_all fsp,
619 gl_ledgers lgr
620 WHERE poll.po_release_id = zxvalerr.other_doc_trx_id
621 AND pod.po_header_id = poll.po_header_id
622 AND pod.po_line_id = poll.po_line_id
623 AND pod.line_location_id = poll.line_location_id
624 AND pod.recovery_rate IS NULL
625 AND poh.po_header_id = poll.po_header_id
626 AND pol.po_header_id = poll.po_header_id
627 AND pol.po_line_id = poll.po_line_id
628 AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
629 AND poll.tax_code_id = atc.tax_id
630 AND poll.tax_code_id = atg.tax_group_id
631 AND atc.tax_type = 'TAX_GROUP'
632 AND atc1.tax_id = atg.tax_code_id
633 AND atc1.enabled_flag = 'Y'
634 AND pov.vendor_id = poh.vendor_id
635 AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
636 AND lgr.ledger_id = fsp.set_of_books_id;
637
638 l_count NUMBER;
639 l_tax_rate_id NUMBER;
640
641 BEGIN
642
643 -- calculate recovery rate for Purchase Order
644 --
645 OPEN pod_rec_rate;
646 LOOP
647 FETCH pod_rec_rate BULK COLLECT INTO
648 pg_po_header_id_tab,
649 pg_po_line_id_tab,
650 pg_po_dist_id_tab,
651 pg_tax_code_id_tab,
652 pg_trx_date_tab,
653 pg_code_combination_id_tab,
654 pg_vendor_id_tab,
655 pg_tax_rec_override_flag_tab,
656 pg_tax_recovery_rate_tab,
657 pg_vendor_site_id_tab,
658 pg_item_id_tab,
659 pg_inv_org_id_tab,
660 pg_chart_of_accounts_id_tab,
661 pg_tc_tax_recovery_rule_id_tab,
662 pg_tc_tax_recovery_rate_tab,
663 pg_vendor_type_lookup_code_tab
664 LIMIT C_LINES_PER_INSERT;
665
666 l_count := pg_tax_code_id_tab.COUNT;
667 IF l_count > 0 THEN
668 FOR i IN 1 .. l_count LOOP
669 ZX_TAX_RECOVERY_PKG.get_default_rate(
670 p_tax_code => NULL,
671 p_tax_id => PG_TAX_CODE_ID_TAB(i),
672 p_tax_date => PG_TRX_DATE_TAB(i),
673 p_code_combination_id => PG_CODE_COMBINATION_ID_TAB(i),
674 p_vendor_id => PG_VENDOR_ID_TAB(i),
675 p_distribution_id => null,
676 p_tax_user_override_flag => PG_TAX_REC_OVERRIDE_FLAG_TAB(i),
677 p_user_tax_recovery_rate => PG_TAX_RECOVERY_RATE_TAB(i),
678 p_concatenated_segments => '',
679 p_vendor_site_id => PG_VENDOR_SITE_ID_TAB(i),
680 p_inventory_item_id => PG_ITEM_ID_TAB(i),
681 p_item_org_id => PG_ITEM_ID_TAB(i),
682 APPL_SHORT_NAME => 'PO',
683 FUNC_SHORT_NAME => '',
684 p_calling_sequence => 'PO_MIG',
685 p_chart_of_accounts_id => pg_chart_of_accounts_id_tab(i),
686 p_tc_tax_recovery_rule_id => pg_tc_tax_recovery_rule_id_tab(i),
687 p_tc_tax_recovery_rate => pg_tc_tax_recovery_rate_tab(i),
688 p_vendor_type_lookup_code => pg_vendor_type_lookup_code_tab(i),
689 p_tax_recovery_rate => l_rec_rate );
690
691 pg_get_tax_recovery_rate_tab(i) := l_rec_rate;
692 BEGIN
693 SELECT TAX_RATE_ID INTO l_tax_rate_id
694 FROM ZX_RATES_B
695 WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
696 AND record_type_code='MIGRATED';
697 EXCEPTION
698 WHEN OTHERS THEN
699 NULL;
700 END;
701
702 IF (pg_tax_code_id_tab(i) <> l_tax_rate_id) and l_tax_rate_id is NOT NULL THEN
703 pg_tax_code_id_tab(i) := l_tax_rate_id;
704 END IF;
705
706 END LOOP;
707
708 insert_rec_info;
709 -- COMMIT;
710 ELSE
711 CLOSE pod_rec_rate;
712 EXIT;
713 END IF;
714
715 IF pod_rec_rate%NOTFOUND THEN
716 CLOSE pod_rec_rate;
717 EXIT;
718 END IF;
719 END LOOP;
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 IF pod_rec_rate%ISOPEN THEN
724 CLOSE pod_rec_rate;
725 END IF;
726 RAISE;
727
728 END get_rec_info;
729
730 end ZX_PO_REC_PKG;