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