[Home] [Help]
PACKAGE BODY: APPS.INV_CONSUMPTION_ADVICE_PROC
Source
1 PACKAGE BODY INV_CONSUMPTION_ADVICE_PROC AS
2 -- $Header: INVRCADB.pls 120.20.12010000.3 2008/10/16 01:37:20 pdong ship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVRCADB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Create Consumption Advice Concurrent Program |
13 --| |
14 --| PROCEDURE LIST |
15 --| Update_Consumption |
16 --| Consumption_Txn_Manager |
17 --| Load Consumption |
18 --| Load_Summarized_Quantity |
19 --| Delete Record |
20 --| Batch Allocation |
21 --| Submit Worker |
22 --| Wait_For_All_Workers |
23 --| Wait_For_Worker |
24 --| Has_Worker_Completed |
25 --| Generate_Batch_Id |
26 --| Generate_Log |
27 --| Log |
28 --| Log_Initialize |
29 --| Consumption_Txn_Worker |
30 --| |
31 --| HISTORY |
32 --| 11/29/02 David Herring Created procedure |
33 --| 09/09/03 Vanessa To Modified for consumption advice error |
34 --| reporting. Store error message in the |
35 --| MCT table. |
36 --| 10/20/05 kdevadas Modified proc consumption_txn_manager |
37 --| to fix a performance issue : Bug 4863365 |
38 --| 13/01/06 myerrams Modified for Bug 4723164 |
39 --| 23/01/06 kdevadas Modified proc consumption_txn_manager to |
40 --| TO prevent release duplication WHEN |
41 --| consumption advice concurrent program IS |
42 --| RUN parallely - Bug 4574159 |
43 --| 30-Jan-06 kdevadas Modified proc. consumption_txn_worker |
44 --| to change the EXIT condition for the | |
45 --| worker. Bug 5006151 |
46 --| 15-Feb-06 kdevadas Profile option changes. |
47 --| Bug 4599072 |
48 --| 07-Mar-06 kdevadas BLANKET_PRICE and PO_DISTRIBUTION_ID |
49 --| columns added to MCT - Bug 4969421 |
50 --| 03-Apr-06 kdevadas Bug 5113064 - 11.5.10 CU Fix FP |
51 --| 08-May-06 kdevadas Bug 5210850 - 11.5.10 Regression Fix FP |
52 --| 17-May-06 kdevadas Bug 5230913 - |
53 --| PO VENDORS reference removed |
54 --| 08-Aug-06 kdevadas New column (INTERFACE_DISTRIBUTION_REF) |
55 --| added to PO_DIST_INTERFACE - Bug 5373370 |
56 --| 06-Nov-06 kdevadas Tax joins and price joins |
57 --| removed in Update_Po_Dist - Bug 5604129 |
58 --| 24-Apr-07 kdevadas Perfomance fix changes - Bug 5104057 |
59 --| 30-Jan-2008 sabghosh two different insert for different
60 --| global_agreement_flag bug - bug 6388514 |
61 --+========================================================================
62
63 --===================
64 -- GLOBALS
65 --===================
66
67 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_CONSUMPTION_ADVICE_PROC';
68 g_user_id NUMBER := FND_PROFILE.value('USER_ID');
69 g_resp_id NUMBER := FND_PROFILE.value('RESP_ID');
70 g_appl_id NUMBER;
71 -- Bug 5092489, commented becasue not used
72 --g_pgm_appl_id NUMBER;
73 g_log_level NUMBER := NULL;
74 g_log_mode VARCHAR2(3) := 'OFF';
75
76 TYPE g_cons_date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
77 TYPE g_cons_varchar_tbl_type IS TABLE OF VARCHAR2(24) INDEX BY BINARY_INTEGER;
78 TYPE g_cons_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
79 TYPE g_request_tbl_type IS TABLE OF NUMBER
80 INDEX BY BINARY_INTEGER;
81
82
83 G_SLEEP_TIME NUMBER := 15;
84 g_batch_size NUMBER := 1000;
85 g_max_workers NUMBER := 1;
86 -- Bug 5092489, commented becasue not used
87 --g_unit_test_mode BOOLEAN := FALSE;
88 --g_org_id NUMBER := FND_PROFILE.value('ORG_ID');
89 g_submit_failure_exc EXCEPTION;
90 g_request_id NUMBER ;
91 --===================
92 -- PRIVATE PROCEDURES
93 --===================
94 /* Bug 4969421 Starts here*/
95 --========================================================================
96 -- PROCEDURE : Update_PO_Distrubution_Id PRIVATE
97 -- PARAMETERS : -
98 -- COMMENT : Update mtl_consumption_transactions table with the
99 -- po_distribution_id and the blanket_price for all the
100 -- processed transactions
101 --========================================================================
102 PROCEDURE update_po_distrubution_id
103 IS
104 l_transaction_id NUMBER;
105 l_po_distribution_id NUMBER;
106 l_consumption_release_id NUMBER;
107 l_consumption_po_header_id NUMBER;
108 l_blanket_price NUMBER ;
109 l_interface_distribution_ref VARCHAR2(240);
110 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
111
112 CURSOR txn_csr_type IS
113 SELECT
114 mct.transaction_id
115 , mct.po_distribution_id
116 , mct.consumption_release_id
117 , mct.consumption_po_header_id
118 /* Bug 5373370 - Start */
119 , mct.interface_distribution_ref
120 /* Bug 5373370 - End */
121 FROM
122 MTL_CONSUMPTION_TRANSACTIONS mct
123 WHERE
124 ( mct.blanket_price IS NULL OR mct.po_distribution_id IS NULL )
125 AND mct.net_qty > 0
126 AND mct.request_id = g_request_id
127 AND mct.consumption_processed_flag = 'Y';
128
129 BEGIN
130
131 IF (l_debug = 1)
132 THEN
133 INV_LOG_UTIL.trace
134 ( '>> Update po_distrubution_id','INV_CONSUMPTION_ADVICE_PROC'
135 , 9
136 );
137 END IF;
138 OPEN txn_csr_type;
139 LOOP
140 l_po_distribution_id := NULL ;
141 l_consumption_release_id := NULL;
142 l_consumption_po_header_id := NULL;
143 l_transaction_id := NULL ;
144 l_blanket_price := NULL ;
145 l_interface_distribution_ref := NULL;
146
147 FETCH txn_csr_type
148 INTO
149 l_transaction_id
150 , l_po_distribution_id
151 , l_consumption_release_id
152 , l_consumption_po_header_id
153 , l_interface_distribution_ref ;
154
155 EXIT WHEN txn_csr_type%NOTFOUND;
156
157 IF (l_debug = 1)
158 THEN
159 INV_LOG_UTIL.trace
160 ( 'Transaction_id: '||l_transaction_id
161 , 9
162 );
163 INV_LOG_UTIL.trace
164 ( 'Consumption_Release_Id: '||l_consumption_release_id
165 , 9
166 );
167 INV_LOG_UTIL.trace
168 ( 'Consumption_Po_Header_Id: '||l_consumption_po_header_id
169 , 9
170 );
171 END IF;
172
173 IF l_po_distribution_id IS NULL
174 THEN
175 BEGIN
176 IF l_consumption_release_id IS NOT NULL
177 THEN
178 -- Bug 5092489, Query modified to avoid use of MMT
179 SELECT pod.po_distribution_id
180 INTO l_po_distribution_id
181 FROM
182 MTL_CONSUMPTION_TRANSACTIONS mct,
183 --MTL_MATERIAL_TRANSACTIONS mmt,
184 po_line_locations_all poll,
185 po_distributions_all pod,
186 po_lines_all pol
187 WHERE mct.transaction_id = l_transaction_id
188 AND mct.consumption_processed_flag = 'Y'
189 AND mct.inventory_item_id = pol.item_id
190 AND mct.transaction_source_id = pol.po_header_id
191 AND poll.po_line_id = pol.po_line_id
192 AND poll.po_header_id = pol.po_header_id
193 AND poll.po_release_id = mct.consumption_release_id
194 AND poll.shipment_type = 'BLANKET'
195 AND poll.line_location_id = pod.line_location_id
196 AND pod.po_release_id = mct.consumption_release_id
197 AND pod.po_header_id = poll.po_header_id
198 /* Bug 5604129 - Start */
199 --AND NVL(mct.tax_code_id, -1) = NVL(poll.TAX_CODE_ID, -1)
200 /* Bug 5373370 - Start */
201 /*AND ( NVL(mct.RECOVERABLE_TAX,0) = NVL(pod.RECOVERABLE_TAX,0)
202 OR ( NVL(mct.RECOVERABLE_TAX,0) =
203 NVL(pod.RECOVERABLE_TAX,0) / pod.quantity_ordered ) )
204 AND ( NVL(mct.NON_RECOVERABLE_TAX,0) = NVL(pod.NONRECOVERABLE_TAX,0)
205 OR ( NVL(mct.NON_RECOVERABLE_TAX,0) =
206 NVL(pod.NONRECOVERABLE_TAX,0) / pod.quantity_ordered ) )*/
207 AND mct.interface_distribution_ref = pod.interface_distribution_ref
208 /* Buf 5373370 - End*/
209 --AND NVL(mct.TAX_RECOVERY_RATE,0) = NVL(pod.RECOVERY_RATE,0)
210 AND mct.CHARGE_ACCOUNT_ID = pod.CODE_COMBINATION_ID
211 AND mct.VARIANCE_ACCOUNT_ID = pod.VARIANCE_ACCOUNT_ID
212 AND mct.accrual_account_id = pod.ACCRUAL_ACCOUNT_ID
213 AND poll.price_override = mct.blanket_price
214 AND ROWNUM = 1;
215 /* Bug 5604129 - End */
216
217 END IF; -- po release
218
219 IF l_consumption_po_header_id IS NOT NULL
220 THEN
221 -- Bug 5092489, Query modified to avoid use of MMT
222 SELECT pod.po_distribution_id
223 INTO l_po_distribution_id
224 FROM
225 MTL_CONSUMPTION_TRANSACTIONS mct,
226 --MTL_MATERIAL_TRANSACTIONS mmt,
227 po_line_locations_all poll,
228 po_distributions_all pod,
229 po_lines_all pol
230 WHERE mct.transaction_id = l_transaction_id
231 AND mct.consumption_processed_flag = 'Y'
232 AND mct.inventory_item_id = pol.item_id
233 AND pol.from_header_id = mct.transaction_source_id
234 AND pol.po_header_id = mct.consumption_po_header_id
235 AND poll.po_line_id = pol.po_line_id
236 AND poll.po_header_id = pol.po_header_id
237 AND poll.shipment_type = 'STANDARD'
238 AND poll.line_location_id = pod.line_location_id
239 AND pod.po_header_id = mct.consumption_po_header_id
240
241 /* Bug 5604129 - Start */
242 --AND NVL(mct.tax_code_id, -1) = NVL(poll.TAX_CODE_ID, -1)
243 /* Bug 5373370 - Start */
244 /*AND ( NVL(mct.RECOVERABLE_TAX,0) = NVL(pod.RECOVERABLE_TAX,0)
245 OR ( NVL(mct.RECOVERABLE_TAX,0) =
246 NVL(pod.RECOVERABLE_TAX,0) / pod.quantity_ordered ) )
247 AND ( NVL(mct.NON_RECOVERABLE_TAX,0) = NVL(pod.NONRECOVERABLE_TAX,0)
248 OR ( NVL(mct.NON_RECOVERABLE_TAX,0) =
249 NVL(pod.NONRECOVERABLE_TAX,0) / pod.quantity_ordered ) )*/
250 AND mct.interface_distribution_ref = pod.interface_distribution_ref
251 /* Bug 5373370 - End*/
252 --AND NVL(mct.TAX_RECOVERY_RATE,0) = NVL(pod.RECOVERY_RATE,0)
253 AND mct.CHARGE_ACCOUNT_ID = pod.CODE_COMBINATION_ID
254 AND mct.VARIANCE_ACCOUNT_ID = pod.VARIANCE_ACCOUNT_ID
255 AND mct.accrual_ACCOUNT_ID = pod.ACCRUAL_ACCOUNT_ID
256 --AND poll.price_override = mct.blanket_price
257 AND ROWNUM = 1;
258 /* Bug 5604129 - End */
259
260 END IF; -- po header id
261
262 IF (l_debug = 1)
263 THEN
264 INV_LOG_UTIL.trace
265 ( 'Updating MTL_CONSUMPTION_TRANSACTIONS with po_distrubution_id '
266 ||l_po_distribution_id
267 , 9
268 );
269 END IF;
270
271 UPDATE MTL_CONSUMPTION_TRANSACTIONS
272 SET po_distribution_id = l_po_distribution_id
273 WHERE transaction_id = l_transaction_id ;
274
275 COMMIT;
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 IF txn_csr_type%ISOPEN THEN
280 CLOSE txn_csr_type;
281 END IF;
282
283 IF (l_debug = 1) THEN
284 INV_LOG_UTIL.trace
285 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
286 , 9
287 );
288 END IF;
289
290 WHEN TOO_MANY_ROWS THEN
291 IF txn_csr_type%ISOPEN THEN
292 CLOSE txn_csr_type;
293 END IF;
294
295 IF (l_debug = 1) THEN
296 INV_LOG_UTIL.trace
297 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
298 , 9
299 );
300 END IF;
301
302 END ;
303
304 END IF;
305
306 END LOOP;
307
308 CLOSE txn_csr_type;
309
310 IF (l_debug = 1)
311 THEN
312 INV_LOG_UTIL.trace
313 ( '<< Update po_distrubution_id','INV_CONSUMPTION_ADVICE_PROC'
314 , 9
315 );
316 END IF;
317
318 END update_po_distrubution_id ;
319 /* Bug 4969421 Ends here*/
320
321 --========================================================================
322 -- PROCEDURE : Update_Consumption PRIVATE
323 -- PARAMETERS:
324 -- p_consumption_po_header_id PO Header Id
325 -- p_consumption_release_id Release id
326 -- p_error_code Error code if any
327 -- p_batch_id batch id from concurrent pgm
328 -- p_consumption_processed_flag E if error,else Y
329 -- p_accrual_account_id Accrual account
330 -- p_variance_account_id Variance account
331 -- p_charg_account_id Charge account
332 -- p_transaction_date Date of transaction
333 -- COMMENT : Update mtl_consumption_transactions table
334 -- This procedure is called by the Create_Consumption_Advice
335 -- procedures after creation of the
336 -- document. Update the table with the appropriate release
337 -- info or the po_header info.
338 --========================================================================
339 PROCEDURE Update_Consumption
340 ( p_consumption_po_header_id IN NUMBER
341 , p_consumption_release_id IN NUMBER
342 , p_error_code IN VARCHAR2
343 , p_batch_id IN NUMBER
344 , p_transaction_source_id IN NUMBER
345 , p_consumption_processed_flag IN VARCHAR2
346 , p_accrual_account_id IN NUMBER
347 , p_variance_account_id IN NUMBER
348 , p_charge_account_id IN NUMBER
349 , p_transaction_date IN DATE
350 , p_global_rate_type IN VARCHAR2
351 , p_global_rate IN NUMBER
352 , p_vendor_site_id IN NUMBER
353 )
354 IS
355 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
356 l_blanket_po_number VARCHAR2(20) := NULL;
357 l_error_explanation VARCHAR2(240) := NULL;
358 l_transaction_date DATE;
359
360 l_count NUMBER;
361
362 --Bug 5113064
363 l_consumption_processed_flag VARCHAR2(1);
364
365 BEGIN
366
367 IF (l_debug = 1)
368 THEN
369 INV_LOG_UTIL.trace
370 ( '>> Update Consumption','INV_CONSUMPTION_ADVICE_PROC'
371 , 9
372 );
373 END IF;
374
375
376 --
377 -- Check for consumption advice processing errors. If there is one, get the
378 -- translated message text and store it in the consumption transaction.
379 --
380 IF (p_consumption_processed_flag = 'E')
381 THEN
382 IF (p_error_code = 'INV_SUP_CONS_NO_BPO_EXISTS')
383 THEN
384 BEGIN
385 SELECT segment1
386 INTO l_blanket_po_number
387 FROM po_headers_all
388 WHERE po_header_id = p_consumption_po_header_id;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 NULL;
393 END;
394 FND_MESSAGE.Set_Name('INV', 'INV_SUP_CONS_NO_BPO_EXISTS');
395 FND_MESSAGE.Set_Token('BLANKET_PO_NUMBER', l_blanket_po_number);
396 ELSE
397 FND_MESSAGE.Set_Name('INV', p_error_code);
398 END IF;
399 l_error_explanation := SUBSTRB(FND_MESSAGE.Get, 1, 240);
400 END IF;
401
402 IF (l_debug = 1)
403 THEN
404 INV_LOG_UTIL.trace
405 ( ' consumption_po_header_id : '||p_consumption_po_header_id,'INV_CONSUMPTION_ADVICE_PROC'
406 , 9
407 );
408 END IF;
409
410 IF (l_debug = 1)
411 THEN
412 INV_LOG_UTIL.trace
413 ( ' consumption_release_id : '||p_consumption_release_id,'INV_CONSUMPTION_ADVICE_PROC'
414 , 9
415 );
416 END IF;
417
418 IF (l_debug = 1)
419 THEN
420 INV_LOG_UTIL.trace
421 ( ' consumption_processed_flag : '||p_consumption_processed_flag,'INV_CONSUMPTION_ADVICE_PROC'
422 , 9
423 );
424 END IF;
425
426 IF (l_debug = 1)
427 THEN
428 INV_LOG_UTIL.trace
429 ( ' error_code : '||p_error_code,'INV_CONSUMPTION_ADVICE_PROC'
430 , 9
431 );
432 END IF;
433
434 IF (l_debug = 1)
435 THEN
436 INV_LOG_UTIL.trace
437 ( ' charge_account_id : '||p_charge_account_id,'INV_CONSUMPTION_ADVICE_PROC'
438 , 9
439 );
440 END IF;
441
442 IF (l_debug = 1)
443 THEN
444 INV_LOG_UTIL.trace
445 ( ' variance_account_id : '||p_variance_account_id,'INV_CONSUMPTION_ADVICE_PROC'
446 , 9
447 );
448 END IF;
449
450 IF (l_debug = 1)
451 THEN
452 INV_LOG_UTIL.trace
453 ( ' transaction_source_id : '||p_transaction_source_id,'INV_CONSUMPTION_ADVICE_PROC'
454 , 9
455 );
456 END IF;
457
458
459 IF (l_debug = 1)
460 THEN
461 INV_LOG_UTIL.trace
462 ( ' distribution_account_id : '||p_accrual_account_id,'INV_CONSUMPTION_ADVICE_PROC'
463 , 9
464 );
465 END IF;
466
467 IF (l_debug = 1)
468 THEN
469 INV_LOG_UTIL.trace
470 ( ' transaction_date : '||p_transaction_date,'INV_CONSUMPTION_ADVICE_PROC'
471 , 9
472 );
473 END IF;
474
475 --Bug 5113064, avoid possibility of null value of consumption_processed_flag
476 l_consumption_processed_flag := p_consumption_processed_flag;
477 IF (l_consumption_processed_flag IS NULL) THEN
478 l_consumption_processed_flag := 'N';
479 END IF;
480
481 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
482 THEN
483 -- Bug 5092489, Query modified to eliminate use of MMT in subquery
484 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
485 SET mct.consumption_po_header_id = p_consumption_po_header_id
486 , mct.consumption_release_id = p_consumption_release_id
487 , mct.consumption_processed_flag = l_consumption_processed_flag
488 , mct.error_code = p_error_code
489 WHERE mct.batch_id = p_batch_id
490 AND mct.charge_account_id = p_charge_account_id
491 AND mct.variance_account_id = p_variance_account_id
492 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
493 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
494 AND mct.consumption_processed_flag IN ('N', 'E')
495 AND mct.transaction_source_id = p_transaction_source_id
496 AND mct.accrual_account_id = p_accrual_account_id
497 AND mct.owning_organization_id = p_vendor_site_id
498 AND ((NVL(mct.global_agreement_flag,'N') = 'Y'
499 AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date))
500 OR (NVL(mct.global_agreement_flag,'N') = 'N'));
501 /* (SELECT transaction_id
502 FROM MTL_MATERIAL_TRANSACTIONS
503 WHERE transaction_source_id = p_transaction_source_id
504 AND distribution_account_id = p_accrual_account_id
505 AND owning_organization_id = p_vendor_site_id
506 AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
507 AND inventory_item_id IN
508 (SELECT inventory_item_id
509 FROM MTL_CONSUMPTION_TXN_TEMP
510 WHERE transaction_source_id = p_transaction_source_id));
511 */
512 -- Bug 5092489, Query modified to eliminate use of MMT in subquery
513 /* UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
514 SET mct.consumption_po_header_id = p_consumption_po_header_id
515 , mct.consumption_release_id = p_consumption_release_id
516 , mct.consumption_processed_flag = l_consumption_processed_flag
517 , mct.error_code = p_error_code
518 WHERE mct.batch_id = p_batch_id
519 AND mct.charge_account_id = p_charge_account_id
520 AND mct.variance_account_id = p_variance_account_id
521 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
522 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
523 AND mct.consumption_processed_flag IN ('N', 'E')
524 AND NVL(mct.global_agreement_flag,'N') = 'N'
525 AND mct.transaction_source_id = p_transaction_source_id
526 AND mct.accrual_account_id = p_accrual_account_id
527 AND mct.owning_organization_id = p_vendor_site_id;
528 /*
529 AND transaction_id IN
530 (SELECT transaction_id
531 FROM MTL_MATERIAL_TRANSACTIONS
532 WHERE transaction_source_id = p_transaction_source_id
533 AND owning_organization_id = p_vendor_site_id
534 AND distribution_account_id = p_accrual_account_id
535 AND inventory_item_id IN
536 (SELECT inventory_item_id
537 FROM MTL_CONSUMPTION_TXN_TEMP
538 WHERE transaction_source_id = p_transaction_source_id));
539 */
540
541 ELSE
542 -- Bug 5092489, Query modified to eliminate use of MMT in subquery
543 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
544 SET mct.consumption_po_header_id = p_consumption_po_header_id
545 , mct.consumption_release_id = p_consumption_release_id
546 , mct.consumption_processed_flag = l_consumption_processed_flag
547 , mct.error_code = p_error_code
548 , mct.error_explanation = l_error_explanation
549 WHERE mct.batch_id = p_batch_id
550 AND mct.charge_account_id = p_charge_account_id
551 AND mct.variance_account_id = p_variance_account_id
552 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
553 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
554 AND mct.consumption_processed_flag IN ('N', 'E')
555 AND mct.transaction_source_id = p_transaction_source_id
556 AND mct.accrual_account_id = p_accrual_account_id
557 AND mct.owning_organization_id = p_vendor_site_id
558 AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date);
559
560 /*
561 AND transaction_id IN
562 (SELECT transaction_id
563 FROM MTL_MATERIAL_TRANSACTIONS
564 WHERE transaction_source_id = p_transaction_source_id
565 AND distribution_account_id = p_accrual_account_id
566 AND owning_organization_id = p_vendor_site_id
567 AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
568 AND inventory_item_id IN
569 (SELECT inventory_item_id
570 FROM MTL_CONSUMPTION_TXN_TEMP
571 WHERE transaction_source_id = p_transaction_source_id));
572 */
573 END IF;
574
575 UPDATE MTL_CONSUMPTION_TRANSACTIONS
576 SET consumption_po_header_id = p_consumption_po_header_id
577 , consumption_release_id = p_consumption_release_id
578 , consumption_processed_flag = l_consumption_processed_flag
579 , error_code = p_error_code
580 , error_explanation = l_error_explanation
581 WHERE parent_transaction_id IN
582 (SELECT mct.transaction_id
583 FROM MTL_CONSUMPTION_TRANSACTIONS mct
584 WHERE mct.batch_id = p_batch_id
585 AND mct.charge_account_id = p_charge_account_id
586 AND mct.variance_account_id = p_variance_account_id
587 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
588 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
589 AND mct.transaction_source_id = p_transaction_source_id
590 AND mct.accrual_account_id = p_accrual_account_id
591 AND mct.owning_organization_id = p_vendor_site_id
592 )
593 AND consumption_processed_flag IN ('N', 'E');
594
595 IF (l_debug = 1)
596 THEN
597 INV_LOG_UTIL.trace
598 ( '<< Update Consumption','INV_CONSUMPTION_ADVICE_PROC'
599 , 9
600 );
601 END IF;
602
603 END Update_Consumption;
604
605
606 --========================================================================
607 -- PROCEDURE : Load_Combination PRIVATE
608 -- COMMENT : This procedure will load all the records of a context batch
609 -- from MTL_CONSUMPTION_TRANSACTIONS to
610 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
611 -- If the batch_id passed is -1 then the call is from the
612 -- manager in which case all records with a
613 -- processed_consumption_flag are loaded.
614 -- The insert statement will also be selective by the
615 -- input parameters p_txn_s_id, p_item_id and p_org_id
616 --=========================================================================
617 PROCEDURE load_combination
618 ( p_batch_id IN NUMBER
619 , p_vendor_id IN NUMBER
620 , p_vendor_site_id IN NUMBER
621 , p_inventory_item_id IN NUMBER
622 , p_organization_id IN NUMBER
623 )
624 IS
625 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
626 l_count NUMBER;
627 BEGIN
628
629 IF (l_debug = 1)
630 THEN
631 INV_LOG_UTIL.trace
632 ( '>> Load Combination(p_batch_id):'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
633 , 9
634 );
635 END IF;
636
637 -- Insert records to the temp table that do belong to
638 -- a global agreement
639
640 -- Insert records to the temp table
641 -- Group the records by use of the distinct clause
642 -- Bug 5092489. Query modified to eliminate use of MMT and DISTINCT clause
643 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
644 ( mctt.transaction_source_id
645 , mctt.inventory_item_id
646 , mctt.organization_id
647 , mctt.owning_organization_id
648 /* Bug 4969421 Starts here*/
649 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
650 , mctt.transaction_cost
651 /* Bug 4969421 Ends here*/
652 , mctt.batch_id
653 , mctt.tax_code_id
654 , mctt.tax_rate
655 , mctt.recoverable_tax
656 , mctt.non_recoverable_tax
657 , mctt.tax_recovery_rate
658 , mctt.accrual_account_id
659 , mctt.charge_account_id
660 , mctt.variance_account_id
661 , mctt.rate_type
662 , mctt.rate
663 , mctt.transaction_date
664 , mctt.global_agreement_flag
665 , mctt.net_qty
666 )
667 SELECT
668 mct.transaction_source_id
669 , mct.inventory_item_id
670 , mct.organization_id
671 , mct.owning_organization_id
672 , mct.blanket_price
673 /* Bug 4969421 Ends here*/
674 , p_batch_id
675 , NVL(mct.tax_code_id,-1)
676 , NVL(mct.tax_rate,-1)
677 , NVL(mct.recoverable_tax,0)
678 , NVL(mct.non_recoverable_tax,0)
679 , NVL(mct.tax_recovery_rate,0)
680 , mct.accrual_account_id
681 , mct.charge_account_id
682 , mct.variance_account_id
683 , NVL(mct.rate_type,'##')
684 , NVL(mct.rate,-1)
685 , TRUNC(mct.transaction_date)
686 , mct.global_agreement_flag
687 , SUM(mct.net_qty)
688 FROM
689 MTL_CONSUMPTION_TRANSACTIONS mct
690 , po_vendor_sites_all pvsa
691 WHERE mct.owning_organization_id = pvsa.vendor_site_id
692 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
693 AND mct.owning_organization_id =
694 NVL(p_vendor_site_id,mct.owning_organization_id)
695 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
696 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
697 AND mct.consumption_processed_flag IN ('N', 'E')
698 AND mct.batch_id = p_batch_id
699 GROUP BY
700 mct.transaction_source_id
701 , mct.inventory_item_id
702 , mct.organization_id
703 , mct.owning_organization_id
704 , mct.blanket_price
705 , mct.tax_code_id
706 , mct.tax_rate
707 , mct.recoverable_tax
708 , mct.non_recoverable_tax
709 , mct.tax_recovery_rate
710 , mct.accrual_account_id
711 , mct.charge_account_id
712 , mct.variance_account_id
713 , mct.rate_type
714 , mct.rate
715 , mct.global_agreement_flag
716 , TRUNC(mct.transaction_date)
717 ;
718
719 /* SELECT DISTINCT
720 mmt.transaction_source_id
721 , mmt.inventory_item_id
722 , mmt.organization_id
723 , mmt.owning_organization_id
724 /* Bug 4969420 Starts here*/
725 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
726 --, mmt.transaction_cost
727 /* , mct.blanket_price
728 /* Bug 4969420 Ends here*/
729 /* , p_batch_id
730 , NVL(mct.tax_code_id,-1)
731 , NVL(mct.tax_rate,-1)
732 , NVL(mct.recoverable_tax,0)
733 , NVL(mct.non_recoverable_tax,0)
734 , NVL(mct.tax_recovery_rate,0)
735 , mmt.distribution_account_id
736 , mct.charge_account_id
737 , mct.variance_account_id
738 , NVL(mct.rate_type,'##')
739 , NVL(mct.rate,-1)
740 , TRUNC(mmt.transaction_date)
741 , mct.global_agreement_flag
742 FROM
743 MTL_CONSUMPTION_TRANSACTIONS mct
744 , MTL_MATERIAL_TRANSACTIONS mmt
745 , po_vendor_sites_all pvsa
746 WHERE mct.transaction_id = mmt.transaction_id
747 AND mmt.owning_organization_id = pvsa.vendor_site_id
748 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
749 AND mmt.owning_organization_id =
750 NVL(p_vendor_site_id,mmt.owning_organization_id)
751 AND mmt.inventory_item_id = NVL(p_inventory_item_id,mmt.inventory_item_id)
752 AND mmt.organization_id = NVL(p_organization_id,mmt.organization_id)
753 AND mct.consumption_processed_flag IN ('N', 'E')
754 AND mmt.transaction_type_id = 74
755 AND mct.batch_id = p_batch_id;
756 */
757
758 select count(*) into l_count
759 from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
760
761 IF (l_debug = 1)
762 THEN
763 INV_LOG_UTIL.trace
764 ( 'temp table count: ' || l_count ,'INV_CONSUMPTION_ADVICE_PROC'
765 , 9
766 );
767
768 INV_LOG_UTIL.trace
769 ( '<< Load Combination','INV_CONSUMPTION_ADVICE_PROC'
770 , 9
771 );
772 END IF;
773 EXCEPTION
774
775 WHEN OTHERS THEN
776 IF (l_debug = 1) THEN
777 INV_LOG_UTIL.trace
778 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
779 , 9
780 );
781 END IF;
782
783 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_COM');
784 FND_MSG_PUB.ADD;
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786
787 END load_combination;
788
789
790 --========================================================================
791 -- PROCEDURE : Load_Combination_prf PRIVATE
792 -- COMMENT : This procedure will load all the records of a context batch
793 -- from MTL_CONSUMPTION_TRANSACTIONS to
794 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
795 -- If the batch_id passed is -1 then the call is from the
796 -- manager in which case all records with a
797 -- processed_consumption_flag are loaded.
798 -- The insert statement will also be selective by the
799 -- input parameters p_txn_s_id, p_item_id and p_org_id
800 --=========================================================================
801 PROCEDURE load_combination_prf
802 ( p_batch_id IN NUMBER
803 , p_vendor_id IN NUMBER
804 , p_vendor_site_id IN NUMBER
805 , p_inventory_item_id IN NUMBER
806 , p_organization_id IN NUMBER
807 )
808 IS
809 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
810 l_count NUMBER;
811 BEGIN
812
813 IF (l_debug = 1)
814 THEN
815 INV_LOG_UTIL.trace
816 ( '>> Load Combination_prf(p_batch_id)'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
817 , 9
818 );
819 END IF;
820
821 --Bug 5092489. Modified insert statement to eliminate use of MMT and DISTINCT clause
822 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
823 ( mctt.transaction_source_id
824 , mctt.inventory_item_id
825 , mctt.organization_id
826 , mctt.owning_organization_id
827 /* Bug 4969421 Starts here*/
828 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
829 , mctt.transaction_cost
830 /* Bug 4969421 Ends here*/
831 , mctt.batch_id
832 , mctt.tax_code_id
833 , mctt.tax_rate
834 , mctt.recoverable_tax
835 , mctt.non_recoverable_tax
836 , mctt.tax_recovery_rate
837 , mctt.accrual_account_id
838 , mctt.charge_account_id
839 , mctt.variance_account_id
840 , mctt.rate_type
841 , mctt.rate
842 , mctt.transaction_date
843 , mctt.global_agreement_flag
844 , mctt.net_qty
845 )
846 SELECT
847 mct.transaction_source_id
848 , mct.inventory_item_id
849 , mct.organization_id
850 , mct.owning_organization_id
851 , mct.blanket_price
852 , p_batch_id
853 , NVL(mct.tax_code_id,-1)
854 , NVL(mct.tax_rate,-1)
855 , NVL(mct.recoverable_tax,0)
856 , NVL(mct.non_recoverable_tax,0)
857 , NVL(mct.tax_recovery_rate,0)
858 , mct.accrual_account_id
859 , mct.charge_account_id
860 , mct.variance_account_id
861 , NVL(mct.rate_type,'##')
862 , NVL(mct.rate,-1)
863 /* Start Bug 6388514 Splitting the deode function into two different INSERT into MCT */
864 --, DECODE(mct.global_agreement_flag, 'Y', TRUNC(mct.transaction_date),'N', TRUNC(MAX(mct.transaction_date)))
865 , TRUNC(mct.transaction_date)
866 /* End Bug 6388514 */
867 , mct.global_agreement_flag
868 , SUM(mct.net_qty)
869 FROM
870 MTL_CONSUMPTION_TRANSACTIONS mct
871 , po_vendor_sites_all pvsa
872 WHERE mct.owning_organization_id = pvsa.vendor_site_id
873 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
874 AND mct.owning_organization_id =
875 NVL(p_vendor_site_id,mct.owning_organization_id)
876 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
877 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
878 AND mct.consumption_processed_flag IN ('N','E')
879 AND mct.batch_id = p_batch_id
880 /* Start Bug 6388514 */
881 AND mct.global_agreement_flag = 'Y'
882 /* End Bug 6388514 */
883 GROUP BY
884 mct.transaction_source_id
885 , mct.inventory_item_id
886 , mct.organization_id
887 , mct.owning_organization_id
888 , mct.blanket_price
889 , mct.tax_code_id
890 , mct.tax_rate
891 , mct.recoverable_tax
892 , mct.non_recoverable_tax
893 , mct.tax_recovery_rate
894 , mct.accrual_account_id
895 , mct.charge_account_id
896 , mct.variance_account_id
897 , mct.rate_type
898 , mct.rate
899 , mct.global_agreement_flag
900 , TRUNC(mct.transaction_date)
901 ;
902
903 /*
904 -- Insert records to the temp table that do belong to
905 -- a global agreement
906 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
907 ( mctt.transaction_source_id
908 , mctt.inventory_item_id
909 , mctt.organization_id
910 , mctt.owning_organization_id
911 /* Bug 4649230 Starts here*/
912 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
913 /* , mctt.transaction_cost
914 /* Bug 4649230 Ends here*/
915 /* , mctt.batch_id
916 , mctt.tax_code_id
917 , mctt.tax_rate
918 , mctt.recoverable_tax
919 , mctt.non_recoverable_tax
920 , mctt.tax_recovery_rate
921 , mctt.accrual_account_id
922 , mctt.charge_account_id
923 , mctt.variance_account_id
924 , mctt.rate_type
925 , mctt.rate
926 , mctt.transaction_date
927 , mctt.global_agreement_flag
928 )
929 SELECT DISTINCT
930 mmt.transaction_source_id
931 , mmt.inventory_item_id
932 , mmt.organization_id
933 , mmt.owning_organization_id
934 /* Bug 4969421 Starts here*/
935 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
936 --, mmt.transaction_cost
937 /* , mct.blanket_price
938 /* Bug 4649230 Ends here*/
939 /* , p_batch_id
940 , NVL(mct.tax_code_id,-1)
941 , NVL(mct.tax_rate,-1)
942 , NVL(mct.recoverable_tax,0)
943 , NVL(mct.non_recoverable_tax,0)
944 , NVL(mct.tax_recovery_rate,0)
945 , mmt.distribution_account_id
946 , mct.charge_account_id
947 , mct.variance_account_id
948 , NVL(mct.rate_type,'##')
949 , NVL(mct.rate,-1)
950 , TRUNC(mmt.transaction_date)
951 , mct.global_agreement_flag
952 FROM
953 MTL_CONSUMPTION_TRANSACTIONS mct
954 , MTL_MATERIAL_TRANSACTIONS mmt
955 , po_vendor_sites_all pvsa
956 WHERE mct.transaction_id = mmt.transaction_id
957 AND mmt.owning_organization_id = pvsa.vendor_site_id
958 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
959 AND mmt.owning_organization_id =
960 NVL(p_vendor_site_id,mmt.owning_organization_id)
961 AND mmt.inventory_item_id = NVL(p_inventory_item_id,mmt.inventory_item_id)
962 AND mmt.organization_id = NVL(p_organization_id,mmt.organization_id)
963 AND mct.global_agreement_flag = 'Y'
964 AND mct.consumption_processed_flag IN ('N','E')
965 AND mmt.transaction_type_id = 74
966 AND mct.batch_id = p_batch_id;
967
968
969 -- Insert records to the temp table that do belong to
970 -- a global agreement
971
972 INSERT INTO mtl_consumption_txn_temp mctt
973 ( mctt.transaction_source_id
974 , mctt.inventory_item_id
975 , mctt.organization_id
976 , mctt.owning_organization_id
977 /* Bug 4969421 Starts here*/
978 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
979 /* , mctt.transaction_cost
980 /* Bug 4969420 Ends here*/
981 /* , mctt.batch_id
982 , mctt.tax_code_id
983 , mctt.tax_rate
984 , mctt.recoverable_tax
985 , mctt.non_recoverable_tax
986 , mctt.tax_recovery_rate
987 , mctt.accrual_account_id
988 , mctt.charge_account_id
989 , mctt.variance_account_id
990 , mctt.rate_type
991 , mctt.rate
992 , mctt.global_agreement_flag
993 )
994 SELECT DISTINCT
995 mmt.transaction_source_id
996 , mmt.inventory_item_id
997 , mmt.organization_id
998 , mmt.owning_organization_id
999 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
1000 --, mmt.transaction_cost
1001 /* , mct.blanket_price
1002 /* Bug 4969420 Ends here*/
1003 /* , p_batch_id
1004 , NVL(mct.tax_code_id,-1)
1005 , NVL(mct.tax_rate,-1)
1006 , NVL(mct.recoverable_tax,0)
1007 , NVL(mct.non_recoverable_tax,0)
1008 , NVL(mct.tax_recovery_rate,0)
1009 , mmt.distribution_account_id
1010 , mct.charge_account_id
1011 , mct.variance_account_id
1012 , NVL(mct.rate_type,'##')
1013 , NVL(mct.rate,-1)
1014 , mct.global_agreement_flag
1015 FROM
1016 MTL_CONSUMPTION_TRANSACTIONS mct
1017 , MTL_MATERIAL_TRANSACTIONS mmt
1018 , po_vendor_sites_all pvsa
1019 WHERE mct.transaction_id = mmt.transaction_id
1020 AND mmt.owning_organization_id = pvsa.vendor_site_id
1021 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
1022 AND mmt.owning_organization_id =
1023 NVL(p_vendor_site_id,mmt.owning_organization_id)
1024 AND mmt.inventory_item_id = NVL(p_inventory_item_id,mmt.inventory_item_id)
1025 AND mmt.organization_id = NVL(p_organization_id,mmt.organization_id)
1026 AND mct.global_agreement_flag = 'N'
1027 AND mct.consumption_processed_flag IN ('N', 'E')
1028 AND mmt.transaction_type_id = 74
1029 AND mct.batch_id = p_batch_id;
1030 */
1031 /* Start Bug 6388514 INSERT into MCT for global_agreement_flag = 'N' */
1032 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
1033 ( mctt.transaction_source_id
1034 , mctt.inventory_item_id
1035 , mctt.organization_id
1036 , mctt.owning_organization_id
1037 , mctt.transaction_cost
1038 , mctt.batch_id
1039 , mctt.tax_code_id
1040 , mctt.tax_rate
1041 , mctt.recoverable_tax
1042 , mctt.non_recoverable_tax
1043 , mctt.tax_recovery_rate
1044 , mctt.accrual_account_id
1045 , mctt.charge_account_id
1046 , mctt.variance_account_id
1047 , mctt.rate_type
1048 , mctt.rate
1049 , mctt.global_agreement_flag
1050 , mctt.net_qty
1051 )
1052 SELECT
1053 mct.transaction_source_id
1054 , mct.inventory_item_id
1055 , mct.organization_id
1056 , mct.owning_organization_id
1057 , mct.blanket_price
1058 , p_batch_id
1059 , NVL(mct.tax_code_id,-1)
1060 , NVL(mct.tax_rate,-1)
1061 , NVL(mct.recoverable_tax,0)
1062 , NVL(mct.non_recoverable_tax,0)
1063 , NVL(mct.tax_recovery_rate,0)
1064 , mct.accrual_account_id
1065 , mct.charge_account_id
1066 , mct.variance_account_id
1067 , NVL(mct.rate_type,'##')
1068 , NVL(mct.rate,-1)
1069 , mct.global_agreement_flag
1070 , SUM(mct.net_qty)
1071 FROM
1072 MTL_CONSUMPTION_TRANSACTIONS mct
1073 , po_vendor_sites_all pvsa
1074 WHERE mct.owning_organization_id = pvsa.vendor_site_id
1075 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
1076 AND mct.owning_organization_id =
1077 NVL(p_vendor_site_id,mct.owning_organization_id)
1078 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
1079 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
1080 AND mct.consumption_processed_flag IN ('N','E')
1081 AND mct.batch_id = p_batch_id
1082 AND mct.global_agreement_flag = 'N'
1083 GROUP BY
1084 mct.transaction_source_id
1085 , mct.inventory_item_id
1086 , mct.organization_id
1087 , mct.owning_organization_id
1088 , mct.blanket_price
1089 , mct.tax_code_id
1090 , mct.tax_rate
1091 , mct.recoverable_tax
1092 , mct.non_recoverable_tax
1093 , mct.tax_recovery_rate
1094 , mct.accrual_account_id
1095 , mct.charge_account_id
1096 , mct.variance_account_id
1097 , mct.rate_type
1098 , mct.rate
1099 , mct.global_agreement_flag
1100 ;
1101 /* End Bug 6388514 */
1102 -- Set the transaction date of each group to the latest date
1103
1104 --UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
1105 --SET mctt.transaction_date =
1106 -- (SELECT
1107 -- TRUNC(MAX(mct.transaction_date))
1108 -- FROM
1109 -- --MTL_MATERIAL_TRANSACTIONS mmt
1110 -- MTL_CONSUMPTION_TRANSACTIONS mct
1111 -- WHERE --mct.transaction_id = mmt.transaction_id AND
1112 -- mct.transaction_source_id = mctt.transaction_source_id
1113 -- AND mct.inventory_item_id = mctt.inventory_item_id
1114 -- AND mct.organization_id = mctt.organization_id
1115 -- /* Bug 4969420 Starts here*/
1116 -- /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
1117 -- --AND mmt.transaction_cost = mctt.transaction_cost
1118 -- AND mct.blanket_price = mctt.transaction_cost
1119 -- /* Bug 4969420 Ends here*/
1120 -- AND NVL(mct.tax_code_id,-1) = NVL(mctt.tax_code_id,-1)
1121 -- AND NVL(mct.recoverable_tax,0) = NVL(mctt.recoverable_tax,0)
1122 -- AND NVL(mct.non_recoverable_tax,0) = NVL(mctt.non_recoverable_tax,0)
1123 -- AND NVL(mct.tax_recovery_rate,0) = NVL(mctt.tax_recovery_rate,0)
1124 -- AND NVL(mct.rate,-1) = NVL(mctt.rate,-1)
1125 -- AND mct.accrual_account_id = mctt.accrual_account_id
1126 -- AND mct.charge_account_id = mctt.charge_account_id
1127 -- AND mct.variance_account_id = mctt.variance_account_id
1128 -- AND mct.global_agreement_flag = 'N'
1129 -- AND mct.consumption_processed_flag IN ('N', 'E'))
1130 --WHERE mctt.transaction_date IS NULL;
1131
1132 /* Start bug 6388514 Update transaction_date in MCTT */
1133 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
1134 SET mctt.transaction_date =
1135 (SELECT
1136 TRUNC(MAX(mct.transaction_date))
1137 FROM
1138 --MTL_MATERIAL_TRANSACTIONS mmt
1139 MTL_CONSUMPTION_TRANSACTIONS mct
1140 WHERE --mct.transaction_id = mmt.transaction_id AND
1141 mct.transaction_source_id = mctt.transaction_source_id
1142 AND mct.inventory_item_id = mctt.inventory_item_id
1143 AND mct.organization_id = mctt.organization_id
1144 /* Bug 4969420 Starts here*/
1145 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
1146 --AND mmt.transaction_cost = mctt.transaction_cost
1147 AND mct.blanket_price = mctt.transaction_cost
1148 /* Bug 4969420 Ends here*/
1149 AND NVL(mct.tax_code_id,-1) = NVL(mctt.tax_code_id,-1)
1150 AND NVL(mct.recoverable_tax,0) = NVL(mctt.recoverable_tax,0)
1151 AND NVL(mct.non_recoverable_tax,0) = NVL(mctt.non_recoverable_tax,0)
1152 AND NVL(mct.tax_recovery_rate,0) = NVL(mctt.tax_recovery_rate,0)
1153 AND NVL(mct.rate,-1) = NVL(mctt.rate,-1)
1154 AND mct.accrual_account_id = mctt.accrual_account_id
1155 AND mct.charge_account_id = mctt.charge_account_id
1156 AND mct.variance_account_id = mctt.variance_account_id
1157 AND mct.global_agreement_flag = 'N'
1158 AND mct.consumption_processed_flag IN ('N', 'E'))
1159 WHERE mctt.transaction_date IS NULL;
1160 /* End bug 6388514 */
1161
1162 select count(*) into l_count
1163 from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
1164
1165 IF (l_debug = 1)
1166 THEN
1167 INV_LOG_UTIL.trace
1168 ( 'temp table count: ' || l_count ,'INV_CONSUMPTION_ADVICE_PROC'
1169 , 9
1170 );
1171
1172 INV_LOG_UTIL.trace
1173 ( '<< Load Combination_prf','INV_CONSUMPTION_ADVICE_PROC'
1174 , 9
1175 );
1176 END IF;
1177
1178 EXCEPTION
1179
1180 WHEN OTHERS THEN
1181 IF (l_debug = 1) THEN
1182 INV_LOG_UTIL.trace
1183 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
1184 , 9
1185 );
1186 END IF;
1187
1188 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_COM');
1189 FND_MSG_PUB.ADD;
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191
1192 END load_combination_prf;
1193
1194
1195
1196
1197 --========================================================================
1198 -- PROCEDURE : Validate_Blanket PRIVATE
1199 -- PARAMETERS : p_batch_id Batch Id
1200 -- COMMENT : Populates the valid_flag if the blanket is valid
1201 --========================================================================
1202 PROCEDURE validate_blanket
1203 ( p_batch_id NUMBER)
1204 IS
1205
1206 --=================
1207 -- CURSORS
1208 --=================
1209
1210 CURSOR valid_csr_type IS
1211 SELECT DISTINCT
1212 transaction_source_id
1213 , inventory_item_id
1214 , owning_organization_id
1215 FROM
1216 mtl_consumption_txn_temp
1217 WHERE batch_id=p_batch_id;
1218
1219 --=================
1220 -- VARIABLES
1221 --=================
1222
1223 l_count NUMBER;
1224 l_header_id NUMBER;
1225 l_item_id NUMBER;
1226 l_owning_organization_id NUMBER;
1227 l_org_id NUMBER;
1228 l_valid_flag VARCHAR2(1);
1229 l_debug NUMBER :=
1230 NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1231 l_error_msg VARCHAR2(100) := 'INV_SUP_CONS_NO_BPO_EXISTS';
1232 l_error_explanation VARCHAR2(240) := NULL;
1233 l_blanket_po_number VARCHAR2(20) := NULL;
1234
1235
1236 BEGIN
1237
1238 IF (l_debug = 1)
1239 THEN
1240 INV_LOG_UTIL.trace
1241 ( '>> Validate Blanket','INV_CONSUMPTION_ADVICE_PROC'
1242 , 9
1243 );
1244 END IF;
1245
1246 OPEN valid_csr_type;
1247 LOOP
1248 FETCH valid_csr_type
1249 INTO l_header_id
1250 ,l_item_id
1251 ,l_owning_organization_id;
1252
1253 IF valid_csr_type%NOTFOUND
1254 THEN
1255 EXIT;
1256 END IF;
1257
1258 -- Check to see if the blanket is still valid
1259
1260 SELECT
1261 COUNT(1)
1262 INTO
1263 l_count
1264 FROM
1265 po_headers_all poh
1266 , po_lines_all pol
1267 WHERE poh.po_header_id = pol.po_header_id
1268 AND (TRUNC(NVL(poh.start_date,SYSDATE -1)) <= TRUNC(SYSDATE))
1269 AND (TRUNC(NVL(poh.end_date,SYSDATE +1)) >= TRUNC(SYSDATE))
1270 AND (TRUNC(NVL(pol.expiration_date,SYSDATE )) >= TRUNC(SYSDATE))
1271 AND poh.approved_flag = 'Y'
1272 AND NVL(poh.frozen_flag, 'N') = 'N'
1273 AND (NVL(poh.cancel_flag,'N') = 'N'
1274 OR NVL(pol.cancel_flag,'N') = 'N')
1275 AND NVL(pol.closed_code,'OPEN') = 'OPEN'
1276 AND poh.po_header_id = l_header_id
1277 AND pol.item_id = l_item_id;
1278
1279 l_org_id :=
1280 INV_THIRD_PARTY_STOCK_UTIL.get_org_id(l_owning_organization_id);
1281
1282 IF l_count > 0
1283 THEN
1284
1285 -- If it is a Global agreement, mark as valid_flag = 'G'
1286 IF INV_PO_THIRD_PARTY_STOCK_MDTR.is_global(l_header_id)
1287 THEN
1288 l_valid_flag := 'G';
1289 ELSE
1290 l_valid_flag := 'Y';
1291 END IF;
1292
1293 UPDATE mtl_consumption_txn_temp
1294 SET valid_flag = l_valid_flag
1295 ,org_id = l_org_id
1296 WHERE transaction_source_id = l_header_id
1297 AND inventory_item_id = l_item_id
1298 AND valid_flag IS NULL;
1299 INV_LOG_UTIL.trace
1300 ( 'VALID line : item - '
1301 ||l_item_id || ', source - '|| l_header_id || ', org - '|| l_org_id,
1302 'INV_CONSUMPTION_ADVICE_PROC'
1303 , 9
1304 );
1305
1306 ELSE
1307
1308 UPDATE mtl_consumption_txn_temp
1309 SET valid_flag = 'N'
1310 ,org_id = l_org_id
1311 WHERE transaction_source_id = l_header_id
1312 AND inventory_item_id = l_item_id
1313 AND valid_flag IS NULL;
1314
1315 /* bug 5113064 - Start */
1316 /* delete all records in MCT_TEMP that have valid flag as 'N' */
1317 /* Also update MCT records with error status and message */
1318 /*Bug 5092489. Query is modified to eliminate use of MMT. */
1319 UPDATE mtl_consumption_transactions SET
1320 consumption_processed_flag = 'E'
1321 , error_code = l_error_msg
1322 WHERE transaction_source_id = l_header_id
1323 AND inventory_item_id = l_item_id
1324 AND owning_organization_id = l_owning_organization_id
1325 AND consumption_processed_flag IN ('N','E')
1326 AND batch_id = p_batch_id;
1327 /*WHERE transaction_id IN
1328 (SELECT transaction_id FROM mtl_material_transactions
1329 WHERE transaction_source_id = l_header_id
1330 AND inventory_item_id = l_item_id
1331 AND owning_organization_id = l_owning_organization_id)
1332 AND consumption_processed_flag IN ('N','E')
1333 AND batch_id = p_batch_id;
1334 */
1335
1336 DELETE FROM mtl_consumption_txn_temp
1337 WHERE valid_flag = 'N'
1338 AND org_id = l_org_id
1339 AND transaction_source_id = l_header_id
1340 AND inventory_item_id = l_item_id;
1341 /* bug 5113064 - End */
1342 INV_LOG_UTIL.trace
1343 ( '**** INVALID line : item - '
1344 ||l_item_id || ', source - '|| l_header_id || ', org - '|| l_org_id,
1345 'INV_CONSUMPTION_ADVICE_PROC'
1346 , 9
1347 );
1348
1349 END IF;
1350
1351 END LOOP;
1352
1353 CLOSE valid_csr_type;
1354
1355 IF (l_debug = 1)
1356 THEN
1357 INV_LOG_UTIL.trace
1358 ( '<< Validate Blanket','INV_CONSUMPTION_ADVICE_PROC'
1359 , 9
1360 );
1361 END IF;
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 IF valid_csr_type% ISOPEN
1366 THEN
1367 CLOSE valid_csr_type;
1368 END IF;
1369 END validate_blanket;
1370
1371 --========================================================================
1372 -- PROCEDURE : Load_Interface_Tables PRIVATE
1373 -- PARAMETERS: p_transaction_source_id Material transaction id
1374 -- p_batch_id Batch id
1375 -- COMMENT : This procedure is called from the worker. It loads
1376 -- : unprocessed summarized change of ownership transactions
1377 -- : from the consumption temp table MTL_CONSUMPTION_TXN_TEMP
1378 -- : into the PO interface tables in preperation for the
1379 -- : creation of either a consumption advice or standard PO
1380 -- : Once this is done the autocreate procedure is called
1381 -- : to process the interface tables and create the release.
1382 -- : The corresponding records are updated with the result,
1383 -- : whether success or failure. If success the last billing date
1384 -- : of the associated asl_id is also updated.
1385 -- CHANGE : Added secondary quantity in the interface table insert.
1386 --========================================================================
1387
1388 PROCEDURE load_interface_tables
1389 ( p_batch_id IN NUMBER
1390 , x_return_status OUT NOCOPY VARCHAR2
1391 )
1392
1393 IS
1394
1395 --=================
1396 -- VARIABLES
1397 --=================
1398
1399 l_header_id NUMBER;
1400 l_transaction_source_id NUMBER;
1401 l_interface_header_id NUMBER;
1402 l_interface_line_id NUMBER;
1403 l_item_id NUMBER;
1404 l_organization_id NUMBER;
1405 l_quantity NUMBER;
1406 l_po_price NUMBER;
1407 l_date DATE;
1408 l_vendor_site_id NUMBER;
1409 l_blanket_id NUMBER;
1410 l_lines NUMBER;
1411 l_org_id NUMBER;
1412 l_user NUMBER;
1413 l_document_type_code VARCHAR2(30);
1414 -- Bug 5092489. Commented because not used.
1415 --l_document_subtype VARCHAR2(30);
1416 l_location_id NUMBER;
1417 -- Bug 5092489. Commented because not used.
1418 --l_ship_to_location NUMBER;
1419 l_bill_to_location NUMBER;
1420
1421 l_vendor_ship_to_location NUMBER;
1422 l_vendor_bill_to_location NUMBER;
1423 l_recoverable_tax NUMBER;
1424 l_nonrecoverable_tax NUMBER;
1425 l_recovery_rate NUMBER;
1426 l_accrual_account_id NUMBER;
1427 l_charge_account_id NUMBER;
1428 l_variance_account_id NUMBER;
1429 l_tax_code_id NUMBER;
1430 -- Bug 5092489. Commented because not used.
1431 --l_description VARCHAR2(100);
1432 --l_category_id NUMBER;
1433 l_owning_organization_id NUMBER;
1434 l_document_id NUMBER;
1435 l_error_code NUMBER;
1436 l_vendor_id NUMBER;
1437 l_rate NUMBER;
1438 -- Bug 5092489. Commented because not used.
1439 --l_coa_id NUMBER;
1440 l_rate_type VARCHAR2(30);
1441 l_global_rate_type VARCHAR2(30);
1442 l_global_rate NUMBER;
1443 l_global_rate_date DATE;
1444 l_valid_flag VARCHAR2(1);
1445 l_po_num_code VARCHAR2(25);
1446 l_consumption_po_header_id NUMBER;
1447 l_consumption_release_id NUMBER;
1448 l_return_status VARCHAR2(1);
1449 l_archive_status VARCHAR2(1);
1450 l_archive_type VARCHAR2(30);
1451 l_archive_subtype VARCHAR2(30);
1452 l_msg_data VARCHAR2(2000);
1453 l_document_number VARCHAR2(30);
1454 l_current_org_id NUMBER;
1455 l_error_msg VARCHAR2(30);
1456 l_consumption_processed_flag VARCHAR2(1);
1457 -- Bug 5092489. Commented because not used.
1458 --l_po_line_id NUMBER;
1459 --l_from_uom_code VARCHAR2(25);
1460 --l_to_uom_code VARCHAR2(25);
1461 l_primary_uom VARCHAR2(25);
1462 l_purchasing_uom VARCHAR2(25);
1463 l_pay_on_flag VARCHAR2(25);
1464 l_pay_on_code VARCHAR2(25);
1465 -- Bug 5092489. Commented because not used.
1466 --l_conv_rate NUMBER;
1467 l_conv_qty NUMBER;
1468 -- Bug 5092489. Commented because not used.
1469 --l_uom_rate NUMBER;
1470 l_asl_id NUMBER;
1471 l_appl_id NUMBER;
1472 l_api_version NUMBER;
1473 -- Bug 5092489. Commented because not used.
1474 --l_precision NUMBER;
1475 l_debug NUMBER :=
1476 NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1477 l_vendor_name VARCHAR2(240);
1478 l_vendor_site VARCHAR2(15);
1479 l_transaction_date DATE;
1480 l_currency_code VARCHAR2(15);
1481 -- Bug 5092489. Commented because not used.
1482 --l_fin_curr_code VARCHAR2(15);
1483 l_func_po_price NUMBER;
1484 l_profile_option VARCHAR2(1);
1485
1486 --l_location_id_OU NUMBER ;
1487 TYPE g_asl_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1488 l_asl_cons_tab g_asl_tbl_type;
1489 l_curr_asl_index NUMBER := 0;
1490
1491 /* INVCONV */
1492 l_secondary_quantity NUMBER;
1493 l_secondary_uom VARCHAR2(50);
1494 -- Variables Defined for the fix of Bug 3959073
1495 l_inv_org_location NUMBER;
1496 l_header_ship_to_location NUMBER;
1497 /* Bug 4969420 Starts here*/
1498 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
1499 /* This would be passed to the po_lines_interface */
1500 l_blanket_price NUMBER;
1501 /* Bug 4969420 Ends here */
1502
1503 /* bug 5373370 - Start */
1504 l_dist_interface_header_id NUMBER;
1505 /* bug 5373370 - End */
1506
1507
1508 --=================
1509 -- CURSORS
1510 --=================
1511
1512 -- Cursor to get the header info to insert into the
1513 -- po_headers_interface table.
1514
1515 CURSOR header_csr_type IS
1516 SELECT DISTINCT
1517 transaction_source_id
1518 , valid_flag
1519 , org_id
1520 , accrual_account_id
1521 , charge_account_id
1522 , variance_account_id
1523 , rate_type
1524 /* bug 5210850 - Start */
1525 --, TRUNC(transaction_date)
1526 , DECODE(global_agreement_flag,'Y',TRUNC(transaction_date),
1527 DECODE(l_profile_option,'N', NULL,TRUNC(transaction_date)) )
1528 /* bug 5210850 - End */
1529 , rate
1530 , owning_organization_id
1531 , currency_code
1532 FROM
1533 mtl_consumption_txn_temp
1534 WHERE batch_id = p_batch_id;
1535
1536 -- Cursor to get the line info to insert into the
1537 -- po_lines_interface table for the corresponding header that is inserted.
1538
1539 CURSOR line_csr_type IS
1540 SELECT
1541 inventory_item_id
1542 , organization_id
1543 , net_qty
1544 , secondary_net_qty /* INVCONV */
1545 , transaction_cost -- This is the blanket_price from MCT Bug 4969421
1546 , TRUNC(transaction_date)
1547 , NVL(tax_code_id,-1)
1548 , rate
1549 , owning_organization_id
1550 , recoverable_tax
1551 , non_recoverable_tax
1552 , tax_recovery_rate
1553 , asl_id
1554 FROM
1555 mtl_consumption_txn_temp
1556 WHERE transaction_source_id = l_blanket_id
1557 AND valid_flag = l_valid_flag
1558 AND TRUNC(transaction_date) = NVL(l_transaction_date, TRUNC(transaction_date))
1559 AND rate_type = NVL(l_global_rate_type,'##')
1560 AND rate = NVL(l_global_rate,-1)
1561 AND owning_organization_id = l_vendor_site_id
1562 AND accrual_account_id = l_accrual_account_id
1563 AND charge_account_id = l_charge_account_id
1564 AND variance_account_id = l_variance_account_id;
1565
1566 /* INVCONV cursor to get secondary unit of measure for the item*/
1567 CURSOR cr_get_sec_uom IS
1568 SELECT m.unit_of_measure
1569 FROM mtl_system_items i,
1570 mtl_units_of_measure m
1571 WHERE i.inventory_item_id = l_item_id
1572 AND i.organization_id = l_organization_id
1573 AND i.secondary_uom_code = m.uom_code;
1574
1575 BEGIN
1576
1577 IF (l_debug = 1)
1578 THEN
1579 INV_LOG_UTIL.trace
1580 ( '>> Load Interface Tables','INV_CONSUMPTION_ADVICE_PROC'
1581 , 9
1582 );
1583 END IF;
1584
1585 x_return_status := FND_API.G_RET_STS_SUCCESS;
1586 l_user := FND_PROFILE.value('USER_ID');
1587 l_org_id := FND_PROFILE.value('ORG_ID');
1588 l_return_status := x_return_status;
1589
1590 /* bug 5200436 - Start */
1591 l_profile_option := NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N');
1592 /* bug 5200436 - End */
1593
1594 l_curr_asl_index := l_asl_cons_tab.COUNT;
1595
1596 INV_CONSUMPTION_ADVICE_PROC.validate_blanket(p_batch_id);
1597
1598 l_document_type_code := 'PO';
1599
1600 -- Get the header info
1601
1602 OPEN header_csr_type;
1603 LOOP
1604 FETCH header_csr_type
1605 INTO
1606 l_blanket_id
1607 , l_valid_flag
1608 , l_current_org_id
1609 , l_accrual_account_id
1610 , l_charge_account_id
1611 , l_variance_account_id
1612 , l_global_rate_type
1613 , l_global_rate_date
1614 , l_global_rate
1615 , l_vendor_site_id
1616 , l_currency_code;
1617
1618 EXIT WHEN header_csr_type%NOTFOUND;
1619
1620 IF l_valid_flag = 'N'
1621 THEN
1622 l_error_msg := 'INV_SUP_CONS_NO_BPO_EXISTS';
1623
1624 IF (l_debug = 1)
1625 THEN
1626 INV_LOG_UTIL.trace
1627 ( '>> No valid BPO:'||l_blanket_id,'INV_CONSUMPTION_ADVICE_PROC'
1628 , 9
1629 );
1630 END IF;
1631
1632 INV_CONSUMPTION_ADVICE_PROC.update_consumption
1633 (p_consumption_po_header_id => NULL
1634 ,p_consumption_release_id => NULL
1635 ,p_error_code => l_error_msg
1636 ,p_batch_id => p_batch_id
1637 ,p_transaction_source_id => l_blanket_id
1638 ,p_consumption_processed_flag => 'E'
1639 ,p_accrual_account_id => l_accrual_account_id
1640 ,p_charge_account_id => l_charge_account_id
1641 ,p_variance_account_id => l_variance_account_id
1642 ,p_transaction_date => l_global_rate_date
1643 ,p_global_rate_type => l_global_rate_type
1644 ,p_global_rate => l_global_rate
1645 ,p_vendor_site_id => l_vendor_site_id);
1646
1647 l_error_msg := NULL;
1648
1649 ELSE -- Valid Blanket
1650
1651 -- If the operating unit from which the concurrent pgm is run is
1652 -- different than the operating unit of the summarized transaction,
1653 -- set the OU context to be the OU of summarized transactions.
1654
1655 IF l_org_id <> l_current_org_id
1656 THEN
1657
1658 INV_THIRD_PARTY_STOCK_UTIL.set_ou_context
1659 ( p_org_id => l_current_org_id
1660 --, p_vendor_site_id => l_vendor_site_id
1661 --, x_appl_id => l_appl_id
1662 );
1663 --MO_GLOBAL.Init('PO');
1664 MO_GLOBAL.set_policy_context('S',l_current_org_id);
1665 ELSE
1666 --MO_GLOBAL.Init('PO');
1667 MO_GLOBAL.set_policy_context('S',l_org_id);
1668 END IF;
1669
1670
1671 -- Bug 5092489. Query modified.
1672 SELECT
1673 -- NVL(povs.ship_to_location_id,pov.ship_to_location_id)
1674 -- , NVL(povs.bill_to_location_id,pov.bill_to_location_id)
1675 /* fix for bug 5230913 - Start */
1676 povs.ship_to_location_id
1677 , povs.bill_to_location_id
1678 , pov.vendor_id --Bug 4723164
1679 /* fix for bug 5230913 - End */
1680 INTO
1681 l_vendor_ship_to_location
1682 , l_vendor_bill_to_location
1683 , l_vendor_id --Bug 4723164
1684 FROM
1685 po_vendor_sites_all povs
1686 , po_vendors pov
1687 WHERE povs.vendor_id = pov.vendor_id
1688 AND povs.vendor_site_id = l_vendor_site_id;
1689
1690 -- Bug 5092489. Query modified
1691 /* SELECT
1692 glc.PRECISION
1693 , glc.currency_code
1694 , fsp.ship_to_location_id
1695 , fsp.bill_to_location_id
1696 INTO
1697 l_precision
1698 , l_fin_curr_code
1699 , l_ship_to_location
1700 , l_bill_to_location
1701 FROM
1702 financials_system_params_all fsp
1703 , gl_sets_of_books glb
1704 , gl_currencies glc
1705 WHERE fsp.set_of_books_id = glb.set_of_books_id
1706 AND glb.currency_code = glc.currency_code
1707 AND NVL(fsp.org_id,-99) = NVL(l_current_org_id,-99);
1708 */
1709 SELECT
1710 fsp.bill_to_location_id
1711 INTO
1712 l_bill_to_location
1713 FROM
1714 financials_system_params_all fsp
1715 WHERE NVL(fsp.org_id,-99) = NVL(l_current_org_id,-99);
1716
1717 SELECT
1718 user_defined_po_num_code
1719 INTO
1720 l_po_num_code
1721 FROM
1722 po_system_parameters_all
1723 WHERE NVL(org_id,-99) = NVL(l_current_org_id,-99);
1724
1725 IF l_po_num_code <> 'AUTOMATIC'
1726 THEN
1727 SELECT
1728 segment1
1729 INTO
1730 l_document_number
1731 FROM
1732 po_headers_all
1733 WHERE po_header_id = l_blanket_id;
1734
1735 ELSE
1736 l_document_number := NULL;
1737 END IF;
1738
1739 SELECT
1740 po_headers_interface_s.NEXTVAL
1741 INTO
1742 l_interface_header_id
1743 FROM
1744 DUAL;
1745
1746 l_transaction_date := l_global_rate_date;
1747
1748 IF (l_valid_flag = 'G') AND (NVL(l_global_rate,-1) = -1)
1749 THEN
1750 l_global_rate_type := NULL;
1751 l_global_rate_date := NULL;
1752 l_global_rate := NULL;
1753 END IF;
1754
1755 -- Check the vendor sites for pay on code
1756
1757 SELECT
1758 pay_on_code
1759 INTO
1760 l_pay_on_code
1761 FROM
1762 po_vendor_sites_all
1763 WHERE vendor_site_id = l_vendor_site_id;
1764
1765 IF NVL(l_pay_on_code,'NONE') IN ('RECEIPT_AND_USE','USE')
1766 THEN
1767 l_pay_on_flag := 'USE';
1768 ELSE
1769 l_pay_on_flag := NULL;
1770 END IF;
1771
1772 --l_ship_to_location := NVL(l_vendor_ship_to_location,l_ship_to_location);
1773 l_bill_to_location := NVL(l_vendor_bill_to_location,l_bill_to_location);
1774
1775 --l_location_id_OU := NULL ;
1776 l_location_id :=
1777 INV_THIRD_PARTY_STOCK_UTIL.get_location(l_current_org_id);
1778
1779 --l_location_id_OU := l_location_id ;
1780
1781 INSERT INTO po_headers_interface
1782 ( interface_header_id
1783 , interface_source_code
1784 , batch_id
1785 , document_type_code
1786 , document_subtype
1787 , document_num
1788 , vendor_id
1789 , vendor_site_id
1790 , agent_id
1791 , currency_code
1792 , rate_type_code
1793 , rate_date
1794 , rate
1795 , ship_to_location_id
1796 , bill_to_location_id
1797 , terms_id
1798 , fob
1799 , pay_on_code
1800 , freight_terms
1801 , min_release_amount
1802 , creation_date
1803 , created_by
1804 , group_code
1805 , action
1806 , org_id
1807 )
1808 SELECT
1809 l_interface_header_id
1810 , 'CONSUMPTION_ADVICE'
1811 , p_batch_id
1812 , l_document_type_code
1813 , DECODE(l_valid_flag,'Y','RELEASE','STANDARD')
1814 , DECODE(l_valid_flag,'Y',segment1,NULL)
1815 , DECODE(l_valid_flag,'G',l_vendor_id,vendor_id) --Bug 4723164
1816 , DECODE(l_valid_flag,'G',l_vendor_site_id,vendor_site_id) --Bug 4723164
1817 , agent_id
1818 , currency_code
1819 , DECODE(l_valid_flag,'G',l_global_rate_type,rate_type)
1820 , DECODE(l_valid_flag,'G',l_global_rate_date,rate_date)
1821 , DECODE(l_valid_flag,'G',l_global_rate,rate)
1822 --, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),ship_to_location_id)
1823 -- Bug Fix for 3959073
1824 -- Ship To Location to be set at the Header of the Consumption Advice should
1825 -- be taken from the BPA, regardless if the BPA is local or global
1826 , ship_to_location_id
1827 , DECODE(l_valid_flag,'G',NVL(l_bill_to_location,l_location_id),bill_to_location_id)
1828 , terms_id
1829 , fob_lookup_code
1830 , l_pay_on_flag
1831 , freight_terms_lookup_code
1832 , min_release_amount
1833 , SYSDATE
1834 , l_user
1835 , 'DEFAULT'
1836 , 'NEW'
1837 , l_current_org_id
1838 FROM
1839 po_headers_all
1840 WHERE po_header_id = l_blanket_id;
1841
1842
1843 -- Now fetch info. related to the lines that we need to insert
1844
1845 OPEN line_csr_type;
1846 LOOP
1847 FETCH line_csr_type
1848 INTO
1849 l_item_id
1850 , l_organization_id
1851 , l_quantity
1852 , l_secondary_quantity -- INVCONV
1853 , l_po_price
1854 , l_date
1855 , l_tax_code_id
1856 , l_rate
1857 , l_owning_organization_id
1858 , l_recoverable_tax
1859 , l_nonrecoverable_tax
1860 , l_recovery_rate
1861 , l_asl_id;
1862
1863 /* Bug 4969421 Starts here*/
1864 /*We use the blanket_price (from MCT) instead of the transaction_cost from MMT*/
1865 /*l_po_price was initially gettting the transaction cost from MMT.*/
1866 /*po_price now stores the blanket_price (PO currency) in MCT */
1867 /*l_blanket_price would be used to populate po_lines_interface. No conversions required */
1868 /*as blanket_price is in PO currency */
1869 l_blanket_price := l_po_price ;
1870 /* Bug 4969421 Ends here */
1871
1872
1873 EXIT WHEN line_csr_type%NOTFOUND;
1874
1875 IF (l_debug = 1)
1876 THEN
1877 INV_LOG_UTIL.trace
1878 ( '>> Load Interface Tables'||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
1879 , 9
1880 );
1881 END IF;
1882
1883 l_asl_cons_tab(l_curr_asl_index) := l_asl_id;
1884 l_curr_asl_index := l_curr_asl_index+1;
1885
1886 IF (l_debug = 1)
1887 THEN
1888 INV_LOG_UTIL.trace
1889 ( '>> Load Interface Tables:','INV_CONSUMPTION_ADVICE_PROC'
1890 , 9
1891 );
1892 END IF;
1893
1894 -- Bug 5092489. Retrival of po_line_id is commented
1895 -- because not used.
1896 SELECT
1897 unit_meas_lookup_code
1898 --, po_line_id
1899 INTO
1900 l_purchasing_uom
1901 --, l_po_line_id
1902 FROM po_lines_all
1903 WHERE po_header_id = l_blanket_id
1904 AND item_id = l_item_id
1905 AND ROWNUM = 1;
1906
1907 l_primary_uom := INV_THIRD_PARTY_STOCK_UTIL.get_primary_uom
1908 ( p_inventory_item_id=> l_item_id
1909 , p_organization_id => l_organization_id
1910 );
1911 IF (l_debug = 1)
1912 THEN
1913 INV_LOG_UTIL.trace
1914 ( '>> Load Interface Tables_prf(UOM):'||l_primary_uom,'INV_CONSUMPTION_ADVICE_PROC'
1915 , 9
1916 );
1917 END IF;
1918
1919 -- If the primary UOM of the item is different than the purchasing
1920 -- UOM, convert the quantity to purchasing UOM.
1921
1922 IF l_primary_uom <> NVL(l_purchasing_uom,l_primary_uom)
1923 THEN
1924 IF (l_debug = 1)
1925 THEN
1926 INV_LOG_UTIL.trace
1927 ( '>> Load Interface Tables_prf:(UOM differ)','INV_CONSUMPTION_ADVICE_PROC'
1928 , 9
1929 );
1930 INV_LOG_UTIL.trace
1931 ( 'Primary,Purchasing UOM is '||l_primary_uom||' '||l_purchasing_uom,'INV_CONSUMPTION_ADVICE_PROC'
1932 , 9
1933 );
1934 END IF;
1935
1936 l_conv_qty := INV_CONVERT.inv_um_convert
1937 ( item_id => l_item_id
1938 , PRECISION => 5
1939 , from_quantity => l_quantity
1940 , from_unit => NULL
1941 , to_unit => NULL
1942 , from_name => l_primary_uom
1943 , to_name => l_purchasing_uom
1944 );
1945
1946 IF l_conv_qty < 0
1947 THEN
1948 l_error_msg := 'INV_CONS_SUP_NO_UOM_CONV';
1949 END IF;
1950
1951 /* Bug 5092489. Commented because not used in code
1952 INV_THIRD_PARTY_STOCK_UTIL.Get_Vendor_Info
1953 ( p_vendor_site_id => l_vendor_site_id
1954 , x_vendor_name => l_vendor_name
1955 , x_vendor_site_code => l_vendor_site
1956 );
1957 IF (l_debug = 1)
1958 THEN
1959 INV_LOG_UTIL.trace
1960 ( '>> UOM conversion '||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
1961 , 9
1962 );
1963 END IF;
1964
1965 l_from_uom_code :=
1966 INV_THIRD_PARTY_STOCK_UTIL.get_uom_code
1967 ( p_unit_of_measure => l_primary_uom
1968 , p_vendor_name => l_vendor_name
1969 , p_vendor_site_code => l_vendor_site
1970 );
1971
1972 l_to_uom_code :=
1973 INV_THIRD_PARTY_STOCK_UTIL.get_uom_code
1974 ( p_unit_of_measure => l_purchasing_uom
1975 , p_vendor_name => l_vendor_name
1976 , p_vendor_site_code => l_vendor_site
1977 );
1978
1979
1980 IF (l_debug = 1)
1981 THEN
1982 INV_LOG_UTIL.trace
1983 ( '>> From UOM '||l_from_uom_code,'INV_CONSUMPTION_ADVICE_PROC'
1984 , 9
1985 );
1986 INV_LOG_UTIL.trace
1987 ( '>> To UOM '||l_to_uom_code,'INV_CONSUMPTION_ADVICE_PROC'
1988 , 9
1989 );
1990 END IF;
1991
1992 INV_CONVERT.inv_um_conversion
1993 (item_id => l_item_id
1994 ,from_unit => l_from_uom_code
1995 ,to_unit => l_to_uom_code
1996 ,uom_rate => l_uom_rate
1997 );
1998
1999 IF (l_debug = 1)
2000 THEN
2001 INV_LOG_UTIL.trace
2002 ( '>>UOM Rate '||l_uom_rate,'INV_CONSUMPTION_ADVICE_PROC'
2003 , 9
2004 );
2005 INV_LOG_UTIL.trace
2006 ( '>> Qty '||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
2007 , 9
2008 );
2009 END IF;
2010
2011 IF l_uom_rate IS NULL OR l_conv_qty < 0
2012 THEN
2013 l_error_msg := 'INV_CONS_SUP_NO_UOM_CONV';
2014 END IF;
2015 */
2016 -- The PO price should be unit price against purchasing UOM; hence
2017 -- convert to unit price for purchasing UOM
2018 /* conversion not reqd anymore - Bug 4969421 */
2019 /*
2020 l_po_price := l_po_price / NVL(l_uom_rate,1);
2021
2022 IF (l_debug = 1)
2023 THEN
2024 INV_LOG_UTIL.trace
2025 ( '>> Load Interface Tables_prf(Price):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
2026 , 9
2027 );
2028 END IF;
2029 */
2030 ELSE
2031 l_conv_qty := l_quantity;
2032 END IF;
2033
2034 -- INVCONV retrive secondary unit of measure for the item
2035 -- if its not null that means item is tracked in dual units
2036 -- from the quantity
2037 IF l_secondary_quantity IS NOT NULL THEN
2038 OPEN cr_get_sec_uom;
2039 FETCH cr_get_sec_uom INTO l_secondary_uom;
2040 IF (cr_get_sec_uom%NOTFOUND) THEN
2041 CLOSE cr_get_sec_uom;
2042 l_secondary_quantity := NULL;
2043 ELSE
2044 CLOSE cr_get_sec_uom;
2045 END IF;
2046 END IF ;
2047
2048 IF (l_debug = 1)
2049 THEN
2050 INV_LOG_UTIL.trace
2051 ( '>> Outside Loop(Qty):'||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
2052 , 9
2053 );
2054 INV_LOG_UTIL.trace
2055 ( '>> Outside Loop(Price):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
2056 , 9
2057 );
2058 INV_LOG_UTIL.trace
2059 ( '>> Outside Loop(error):'||l_error_msg,'INV_CONSUMPTION_ADVICE_PROC'
2060 , 9
2061 );
2062 END IF;
2063
2064 IF l_error_msg IS NOT NULL
2065 THEN
2066 l_consumption_release_id := NULL;
2067 l_consumption_po_header_id := NULL;
2068 l_consumption_processed_flag := 'E';
2069 EXIT;
2070 END IF;
2071
2072 /* conversion not reqd anymore - Bug 4969421 */
2073 /*
2074
2075 -- If the blanket is in foreign currency, convert the unit price to
2076 -- currency of the original blanket. THe unit price from MMT is
2077 -- in functional currency.
2078
2079 IF NVL(l_rate,-1) = -1
2080 THEN
2081 l_po_price := l_po_price - NVL(l_nonrecoverable_tax,0);
2082 IF (l_debug = 1)
2083 THEN
2084 INV_LOG_UTIL.trace
2085 ( '>> PO Price (no conv):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
2086 , 9
2087 );
2088 END IF;
2089 ELSE
2090
2091 l_func_po_price := l_po_price - NVL(l_nonrecoverable_tax,0);
2092
2093 IF (l_debug = 1)
2094 THEN
2095 INV_LOG_UTIL.trace
2096 ( '>> PO Price (conv):'||l_po_price||' '||l_nonrecoverable_tax,'INV_CONSUMPTION_ADVICE_PROC'
2097 , 9
2098 );
2099 END IF;
2100
2101 SELECT
2102 DECODE(NVL(fc.minimum_accountable_unit,0), 0,
2103 ROUND((l_func_po_price*l_conv_qty)* (1/ABS(l_rate))/l_conv_qty,
2104 NVL(fc.extended_precision,fc.PRECISION)),
2105 ROUND(l_func_po_price* l_conv_qty/fc.minimum_accountable_unit) *
2106 fc.minimum_accountable_unit*(1/ABS(l_rate))/l_conv_qty)
2107 INTO
2108 l_po_price
2109 FROM
2110 fnd_currencies fc
2111 WHERE fc.currency_code = NVL(l_currency_code,l_fin_curr_code);
2112
2113 IF (l_debug = 1)
2114 THEN
2115 INV_LOG_UTIL.trace
2116 ( '>> PO Price (fnd_curr):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
2117 , 9
2118 );
2119 END IF;
2120 END IF;
2121 */
2122 -- Bug Fix for 3959073
2123 -- Getting the ship to location set at the PO Header of the Blanket Agreement
2124 SELECT
2125 ship_to_location_id
2126 INTO
2127 l_header_ship_to_location
2128 FROM
2129 po_headers_all
2130 WHERE po_header_id = l_blanket_id;
2131
2132 -- Bug Fix for 3959073
2133 -- Getting the location of the Inventory Organization
2134 l_inv_org_location:= INV_THIRD_PARTY_STOCK_UTIL.get_location(l_organization_id);
2135 IF (l_debug = 1)
2136 THEN
2137 INV_LOG_UTIL.trace
2138 ( '>> Location :'||l_location_id,'INV_CONSUMPTION_ADVICE_PROC'
2139 , 9
2140 );
2141 INV_LOG_UTIL.trace
2142 ( '>> Blanket /Item :'||l_blanket_id||' '||l_item_id,'INV_CONSUMPTION_ADVICE_PROC'
2143 , 9
2144 );
2145 INV_LOG_UTIL.trace
2146 ( '>> Owning org is :'||l_owning_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
2147 , 9
2148 );
2149 END IF;
2150
2151 SELECT PO_LINES_INTERFACE_S.NEXTVAL
2152 INTO l_interface_line_id
2153 FROM DUAL;
2154
2155 /* Bug 7231720 If the Profile Option PO: Automatic Document Sourcing is set to "Yes" then
2156 the latest valid blanket must be picked even if the blanket is not present in the Approved
2157 Supplier List. There is no check for the presence of a record in the table po_asl_documents if
2158 the Profile Option PO: Automatic Document Sourcing is set to "No". The blanket is picked up
2159 from MMT.TRANSACTION_SOURCE_ID when the profile is set to "No" */
2160
2161 IF (nvl(fnd_profile.value('PO_AUTO_SOURCE_DOC'),'N') = 'Y') THEN
2162
2163 INSERT INTO po_lines_interface
2164 ( interface_header_id
2165 , interface_line_id
2166 , line_num
2167 , line_type_id
2168 , item_id
2169 , item_description
2170 , category_id
2171 , unit_of_measure
2172 , quantity
2173 , vendor_product_num
2174 , unit_price
2175 , ship_to_organization_id
2176 , ship_to_location_id
2177 , need_by_date
2178 , promised_date
2179 , creation_date
2180 , created_by
2181 , tax_code_id
2182 , from_header_id
2183 , from_line_id
2184 , closed_date
2185 , closed_by
2186 , receive_close_tolerance
2187 , closed_code
2188 , closed_reason
2189 , secondary_quantity --/* INVCONV
2190 , secondary_unit_of_measure --/* INVCONV
2191 )
2192 SELECT
2193 l_interface_header_id
2194 , l_interface_line_id
2195 , DECODE(l_valid_flag,'Y',line_num,NULL)
2196 , line_type_id
2197 , l_item_id
2198 , item_description
2199 , category_id
2200 , unit_meas_lookup_code
2201 , l_conv_qty
2202 , vendor_product_num
2203 ,l_blanket_price
2204 , l_organization_id
2205 , NVL(l_inv_org_location,l_header_ship_to_location)
2206 , l_date
2207 , SYSDATE
2208 , SYSDATE
2209 , l_user
2210 , DECODE(l_tax_code_id,-1,NULL,l_tax_code_id)
2211 , DECODE(l_valid_flag,'G',l_blanket_id,NULL)
2212 , DECODE(l_valid_flag,'G',po_line_id,NULL)
2213 , SYSDATE
2214 , l_user
2215 , 100
2216 , 'CLOSED'
2217 , 'Consumption Advice'
2218 , l_secondary_quantity --/* INVCONV
2219 , l_secondary_uom --/* INVCONV
2220 FROM
2221 po_lines_all pla
2222 WHERE po_header_id = l_blanket_id
2223 AND item_id = l_item_id
2224 and ROWNUM = 1;
2225
2226 ELSE /* Profile Option PO: Automatic Document Sourcing is set to "No" */
2227
2228 INSERT INTO po_lines_interface
2229 ( interface_header_id
2230 , interface_line_id
2231 , line_num
2232 , line_type_id
2233 , item_id
2234 , item_description
2235 , category_id
2236 , unit_of_measure
2237 , quantity
2238 , vendor_product_num
2239 , unit_price
2240 , ship_to_organization_id
2241 , ship_to_location_id
2242 , need_by_date
2243 , promised_date
2244 , creation_date
2245 , created_by
2246 , tax_code_id
2247 , from_header_id
2248 , from_line_id
2249 , closed_date
2250 , closed_by
2251 , receive_close_tolerance
2252 , closed_code
2253 , closed_reason
2254 , secondary_quantity --/* INVCONV
2255 , secondary_unit_of_measure --/* INVCONV
2256 )
2257 SELECT
2258 l_interface_header_id
2259 , l_interface_line_id
2260 , DECODE(l_valid_flag,'Y',line_num,NULL)
2261 , line_type_id
2262 , l_item_id
2263 , item_description
2264 , category_id
2265 , unit_meas_lookup_code
2266 , l_conv_qty
2267 , vendor_product_num
2268 /* Bug 4969421 Starts here*/
2269 /*We use the blanket_price (from MCT) instead of the transaction_cost from MMT*/
2270 --, NVL(l_po_price,l_func_po_price)
2271 ,l_blanket_price
2272 /*Bug 4969421 Ends here */
2273 , l_organization_id
2274 --, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),l_location_id)
2275 -- Bug Fix for 3959073
2276 -- Ship To Location to be set at the Shipment Line of the Consumption Advice
2277 -- should be taken from the Inventory Organization. If the location is not
2278 -- defined for the Inventory Organization, then take the Ship To Location
2279 -- from the Header of the current Consumption Advice, i.e., from the Header
2280 -- of the BPA.
2281 , NVL(l_inv_org_location,l_header_ship_to_location)
2282 , l_date
2283 , SYSDATE
2284 , SYSDATE
2285 , l_user
2286 , DECODE(l_tax_code_id,-1,NULL,l_tax_code_id)
2287 , DECODE(l_valid_flag,'G',l_blanket_id,NULL)
2288 , DECODE(l_valid_flag,'G',po_line_id,NULL)
2289 , SYSDATE
2290 , l_user
2291 , 100
2292 , 'CLOSED'
2293 , 'Consumption Advice'
2294 , l_secondary_quantity --/* INVCONV
2295 , l_secondary_uom --/* INVCONV
2296 FROM
2297 po_lines_all pla
2298 WHERE po_header_id = l_blanket_id
2299 AND item_id = l_item_id
2300
2301 /* Start Bug 6388514 If there are two lines on a blanket with the same item/price, etc,
2302 the line specified on the ASL should be picked */
2303 -- AND ROWNUM = 1;
2304 AND EXISTS
2305 (SELECT 1
2306 FROM po_asl_documents
2307 WHERE asl_id = l_asl_id
2308 AND document_line_id = pla.po_line_id
2309 AND document_header_id = l_blanket_id
2310 AND using_organization_id in (-1, l_organization_id)); -- Bug 7165111 : Added -1 for global ASL
2311 /* End Bug 6388514 */
2312
2313 END IF;
2314
2315 /* Bug 7231720 Ends */
2316
2317 SELECT
2318 vendor_id
2319 INTO
2320 l_vendor_id
2321 FROM
2322 po_vendor_sites_all
2323 WHERE vendor_site_id = l_owning_organization_id;
2324
2325 IF (l_debug = 1)
2326 THEN
2327 INV_LOG_UTIL.trace
2328 ( 'Populated Lines Interface','INV_CONSUMPTION_ADVICE_PROC'
2329 , 9
2330 );
2331 END IF;
2332
2333 /* Bug 5373370 - Start */
2334 SELECT
2335 PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
2336 INTO
2337 l_dist_interface_header_id
2338 FROM DUAL;
2339
2340 IF (l_debug = 1)
2341 THEN
2342 INV_LOG_UTIL.trace
2343 ( '>> interface_header_id :'|| l_dist_interface_header_id,'INV_CONSUMPTION_ADVICE_PROC'
2344 , 9
2345 );
2346 END IF ;
2347
2348 /* Bug 5373370 - End */
2349
2350
2351 INSERT INTO po_distributions_interface
2352 ( interface_header_id
2353 , interface_line_id
2354 , interface_distribution_id
2355 , quantity_ordered
2356 , charge_account_id
2357 , accrual_account_id
2358 , variance_account_id
2359 , deliver_to_location_id
2360 , destination_organization_id
2361 , recoverable_Tax
2362 , nonrecoverable_Tax
2363 , recovery_rate
2364 , creation_date
2365 , created_by
2366 , destination_type_code
2367 , rate
2368 , rate_date
2369 /* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
2370 , INTERFACE_DISTRIBUTION_REF
2371 /* Bug fix 5373370 - End */
2372 )
2373 SELECT
2374 l_interface_header_id
2375 , l_interface_line_id
2376 , l_dist_interface_header_id -- bug 5373370
2377 , l_conv_qty
2378 , l_charge_account_id
2379 , l_accrual_account_id
2380 , l_variance_account_id
2381 --, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),l_location_id)
2382 -- Bug Fix for 3959073
2383 -- Ship To Location to be set at the Distribution Line of the Consumption
2384 -- Advice should be taken from the Inventory Organization. If the location
2385 -- is not defined for the Inventory Organization, then take the Ship To
2386 -- Location from the Header of the current Consumption Advice, i.e.,
2387 -- from the Header of the BPA.
2388 , NVL(l_inv_org_location,l_header_ship_to_location)
2389 , l_organization_id
2390 , (l_recoverable_tax*l_conv_qty)
2391 , (l_nonrecoverable_tax*l_conv_qty)
2392 , l_recovery_rate
2393 , SYSDATE
2394 , l_user
2395 , 'INVENTORY'
2396 , DECODE(l_valid_flag,'G',l_global_rate,DECODE(l_rate,-1,NULL,l_rate))
2397 , DECODE(l_valid_flag,'G',l_global_rate_date
2398 ,DECODE(l_rate,-1,NULL,l_date))
2399 /* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
2400 , TO_CHAR(l_dist_interface_header_id)
2401 /* Bug fix 5373370 - End */
2402 FROM DUAL;
2403
2404
2405 /* Bug 5373370 - Start */
2406 /* The distribution_interface_ref that is inserted into
2407 PO_DISTRIBUTIONS_INTERFACE is inserted into MCT. This column
2408 will later be joined to PO_DISTRIBUTIONS_ALL to fetch the
2409 PO_DISTRIBUTION_ID */
2410
2411 /* MMT no longer used in this query */
2412
2413 UPDATE mtl_consumption_transactions
2414 SET interface_distribution_ref = TO_CHAR(l_dist_interface_header_id)
2415 WHERE transaction_id IN
2416 ( SELECT mct.transaction_id
2417 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2418 WHERE mct.consumption_processed_flag IN ('N','E')
2419 AND mct.inventory_item_id = l_item_id
2420 AND mct.transaction_source_id = l_blanket_id
2421 AND mct.blanket_price = l_blanket_price
2422 AND NVL(mct.recoverable_tax,0) = l_recoverable_tax
2423 AND NVL(mct.non_recoverable_tax,0) = l_nonrecoverable_tax
2424 AND mct.charge_account_id = l_charge_account_id
2425 AND mct.variance_account_id = l_variance_account_id
2426 AND mct.ACCRUAL_ACCOUNT_ID = l_accrual_account_id
2427 AND NVL(mct.tax_recovery_rate,0) = l_recovery_rate
2428 AND NVL(mct.tax_code_id,-1) = l_tax_code_id
2429 AND mct.batch_id = p_batch_id
2430 );
2431
2432 /* Bug 5373370 - End */
2433
2434
2435 IF (l_debug = 1)
2436 THEN
2437 INV_LOG_UTIL.trace
2438 ( 'Populated Distributions Interface','INV_CONSUMPTION_ADVICE_PROC'
2439 , 9
2440 );
2441 END IF;
2442 END LOOP; -- line
2443
2444 CLOSE line_csr_type;
2445
2446 -- Call the document sourcing to create a release or a std PO
2447
2448 IF l_error_msg IS NULL
2449 THEN
2450
2451 IF (l_debug = 1)
2452 THEN
2453 INV_LOG_UTIL.trace
2454 ( '>> Create Documents ','INV_CONSUMPTION_ADVICE_PROC'
2455 , 9
2456 );
2457 END IF;
2458
2459 IF (l_debug = 1)
2460 THEN
2461 INV_LOG_UTIL.trace
2462 ( 'Batch Id : '||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
2463 , 9
2464 );
2465 END IF;
2466
2467 IF (l_debug = 1)
2468 THEN
2469 INV_LOG_UTIL.trace
2470 ( 'Document Id : '||l_document_id,'INV_CONSUMPTION_ADVICE_PROC'
2471 , 9
2472 );
2473 END IF;
2474
2475 IF (l_debug = 1)
2476 THEN
2477 INV_LOG_UTIL.trace
2478 ( 'Document Number : '||l_document_number,'INV_CONSUMPTION_ADVICE_PROC'
2479 , 9
2480 );
2481 END IF;
2482
2483 IF (l_debug = 1)
2484 THEN
2485 INV_LOG_UTIL.trace
2486 ( 'Line : '||l_lines,'INV_CONSUMPTION_ADVICE_PROC'
2487 , 9
2488 );
2489 END IF;
2490
2491 INV_PO_THIRD_PARTY_STOCK_MDTR.create_documents
2492 ( p_batch_id => p_batch_id
2493 , p_document_id => l_document_id
2494 , p_document_number => l_document_number
2495 , p_line => l_lines
2496 , x_error_code => l_error_code
2497 );
2498
2499 IF (l_debug = 1)
2500 THEN
2501 INV_LOG_UTIL.trace
2502 ( 'Error Code : '||l_error_code,'INV_CONSUMPTION_ADVICE_PROC'
2503 , 9
2504 );
2505 END IF;
2506
2507 IF (l_debug = 1)
2508 THEN
2509 INV_LOG_UTIL.trace
2510 ( '<< Create Documents ','INV_CONSUMPTION_ADVICE_PROC'
2511 , 9
2512 );
2513 END IF;
2514
2515
2516 IF l_error_code = 1
2517 THEN
2518
2519
2520 -- Update the ASL entry with the last billing date
2521
2522 FOR v_counter IN l_asl_cons_tab.FIRST .. l_asl_cons_tab.LAST
2523 LOOP
2524 l_asl_id := l_asl_cons_tab(v_counter);
2525
2526 IF (l_debug = 1)
2527 THEN
2528 INV_LOG_UTIL.trace
2529 ( '>> Update ASL ' ,'INV_CONSUMPTION_ADVICE_PROC'
2530 , 9
2531 );
2532 END IF;
2533
2534 IF (l_debug = 1)
2535 THEN
2536 INV_LOG_UTIL.trace
2537 ( ' ASL_ID : '||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
2538 , 9
2539 );
2540 END IF;
2541
2542 INV_PO_THIRD_PARTY_STOCK_MDTR.update_asl
2543 (p_asl_id => l_asl_id);
2544
2545 END LOOP;
2546
2547 IF (l_debug = 1)
2548 THEN
2549 INV_LOG_UTIL.trace
2550 ( '<< Update ASL ','INV_CONSUMPTION_ADVICE_PROC'
2551 , 9
2552 );
2553 END IF;
2554
2555 -- If a Release was created, populate release id
2556
2557 IF l_valid_flag = 'Y' THEN
2558
2559 l_consumption_release_id := l_document_id;
2560 l_consumption_po_header_id := NULL;
2561 l_error_msg := NULL;
2562 l_consumption_processed_flag := 'Y';
2563 ELSE -- GA, Standard PO was created
2564
2565 l_consumption_release_id := NULL;
2566 l_consumption_po_header_id := l_document_id;
2567 l_error_msg :=NULL;
2568 l_consumption_processed_flag := 'Y';
2569 END IF;
2570
2571 IF NVL(l_valid_flag,'N') = 'Y'
2572 THEN
2573 l_archive_subtype := 'BLANKET';
2574 l_archive_type := 'RELEASE';
2575 ELSIF NVL(l_valid_flag,'N') = 'G'
2576 THEN
2577 l_archive_subtype := 'STANDARD';
2578 l_archive_type := 'PO';
2579 END IF;
2580
2581 l_api_version := 1.0;
2582
2583 IF (l_debug = 1)
2584 THEN
2585 INV_LOG_UTIL.trace
2586 ( '>> Archive PO ','INV_CONSUMPTION_ADVICE_PROC'
2587 , 9
2588 );
2589 END IF;
2590
2591 IF (l_debug = 1)
2592 THEN
2593 INV_LOG_UTIL.trace
2594 ( ' api version : '||l_api_version,'INV_CONSUMPTION_ADVICE_PROC'
2595 , 9
2596 );
2597 END IF;
2598
2599 IF (l_debug = 1)
2600 THEN
2601 INV_LOG_UTIL.trace
2602 ( ' document id : '||l_document_id,'INV_CONSUMPTION_ADVICE_PROC'
2603 , 9
2604 );
2605 END IF;
2606
2607 IF (l_debug = 1)
2608 THEN
2609 INV_LOG_UTIL.trace
2610 ( ' document_type : '||l_archive_type,'INV_CONSUMPTION_ADVICE_PROC'
2611 , 9
2612 );
2613 END IF;
2614
2615 IF (l_debug = 1)
2616 THEN
2617 INV_LOG_UTIL.trace
2618 ( ' document subtype : '||l_archive_subtype,'INV_CONSUMPTION_ADVICE_PROC'
2619 , 9
2620 );
2621 END IF;
2622
2623 INV_PO_THIRD_PARTY_STOCK_MDTR.archive_po
2624 ( p_api_version => l_api_version
2625 , p_document_id => l_document_id
2626 , p_document_type => l_archive_type
2627 , p_document_subtype => l_archive_subtype
2628 , x_return_status => l_archive_status
2629 , x_msg_data => l_msg_data
2630 );
2631
2632 IF (l_debug = 1)
2633 THEN
2634 INV_LOG_UTIL.trace
2635 ( ' return status : '||l_archive_status,'INV_CONSUMPTION_ADVICE_PROC'
2636 , 9
2637 );
2638 END IF;
2639
2640 IF (l_debug = 1)
2641 THEN
2642 INV_LOG_UTIL.trace
2643 ( ' msg data : '||l_msg_data,'INV_CONSUMPTION_ADVICE_PROC'
2644 , 9
2645 );
2646 END IF;
2647
2648 IF (l_debug = 1)
2649 THEN
2650 INV_LOG_UTIL.trace
2651 ( '<< Archive PO : ','INV_CONSUMPTION_ADVICE_PROC'
2652 , 9
2653 );
2654 END IF;
2655
2656
2657 IF l_archive_status <> FND_API.G_RET_STS_SUCCESS
2658 THEN
2659 l_error_msg := 'INV_SUP_CONS_ARCHIVING_FAIL';
2660 END IF;
2661
2662 ELSE
2663
2664 -- autocreate returned error
2665 l_consumption_release_id := NULL;
2666 l_consumption_po_header_id :=NULL;
2667 l_error_msg := 'INV_SUP_CONS_AUTO_CREATE_FAIL';
2668 l_consumption_processed_flag := 'E';
2669 END IF;
2670 END IF;
2671
2672 INV_CONSUMPTION_ADVICE_PROC.Update_Consumption
2673 (p_consumption_po_header_id => l_consumption_po_header_id
2674 ,p_consumption_release_id => l_consumption_release_id
2675 ,p_error_code => l_error_msg
2676 ,p_batch_id => p_batch_id
2677 ,p_transaction_source_id => l_blanket_id
2678 ,p_consumption_processed_flag => l_consumption_processed_flag
2679 ,p_accrual_account_id => l_accrual_account_id
2680 ,p_charge_account_id => l_charge_account_id
2681 ,p_variance_account_id => l_variance_account_id
2682 ,p_transaction_date => l_transaction_date
2683 ,p_global_rate_type => l_global_rate_type
2684 ,p_global_rate => l_global_rate
2685 ,p_vendor_site_id => l_vendor_site_id);
2686
2687 IF (l_debug = 1)
2688 THEN
2689 INV_LOG_UTIL.trace
2690 ('Release created :'||l_consumption_release_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2691 INV_LOG_UTIL.trace
2692 ('PO created :'||l_consumption_po_header_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2693 INV_LOG_UTIL.trace
2694 ('Batch :'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2695 INV_LOG_UTIL.trace
2696 ('Blanket is :'||l_blanket_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2697 INV_LOG_UTIL.trace
2698 ('Site is :'||l_vendor_site_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2699 INV_LOG_UTIL.trace
2700 ('Txn Date is :'||l_transaction_date,'INV_CONSUMPTION_ADVICE_PROC',9);
2701 END IF;
2702
2703 l_error_msg := NULL;
2704 l_consumption_processed_flag := NULL;
2705
2706 END IF;
2707
2708 END LOOP; -- header
2709
2710 CLOSE header_csr_type;
2711
2712 IF (l_debug = 1)
2713 THEN
2714 INV_LOG_UTIL.trace
2715 ( '<< Load Interface Tables','INV_CONSUMPTION_ADVICE_PROC'
2716 , 9
2717 );
2718 END IF;
2719
2720 EXCEPTION
2721
2722 WHEN OTHERS THEN
2723
2724 IF header_csr_type% ISOPEN
2725 THEN
2726 CLOSE header_csr_type;
2727 END IF;
2728
2729 IF line_csr_type% ISOPEN
2730 THEN
2731 CLOSE line_csr_type;
2732 END IF;
2733
2734 IF (l_debug = 1) THEN
2735 INV_LOG_UTIL.trace
2736 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2737 , 9
2738 );
2739 END IF;
2740
2741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742
2743 END load_interface_tables;
2744
2745
2746 --========================================================================
2747 -- PROCEDURE : Load_Summarized_Quantity PRIVATE
2748 -- COMMENT : This procedure summarizes records in
2749 -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2750 -- transaction_source_id, inventory_item_id, organization_id
2751 -- transaction cost, tax code id, accrual account, variance
2752 -- account, charge account
2753 -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2754 -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2755 -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2756 -- for inventory convergence project.
2757 --=========================================================================
2758 PROCEDURE load_summarized_quantity
2759 ( p_txn_source_tab IN g_cons_tbl_type
2760 , p_inventory_item_tab IN g_cons_tbl_type
2761 , p_organization_tab IN g_cons_tbl_type
2762 , p_own_org_tab IN g_cons_tbl_type
2763 , p_transaction_cost_tab IN g_cons_tbl_type
2764 , p_tax_code_tab IN g_cons_tbl_type
2765 , p_rec_tax_tab IN g_cons_tbl_type
2766 , p_non_rec_tax_tab IN g_cons_tbl_type
2767 , p_accrual_account_tab IN g_cons_tbl_type
2768 , p_charge_account_tab IN g_cons_tbl_type
2769 , p_variance_account_tab IN g_cons_tbl_type
2773 , p_batch_id IN NUMBER
2770 , p_date_tab IN g_cons_date_tbl_type
2771 , p_rate_tab IN g_cons_tbl_type
2772 , p_rate_type_tab IN g_cons_varchar_tbl_type
2774 , p_tax_rec_rate_tab IN g_cons_tbl_type -- Bug 4969421
2775 )
2776 IS
2777 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2778 -- Bug 5092489, commenting as not used
2779 --l_txn_count NUMBER;
2780 --l_txn_first NUMBER;
2781 --l_txn_last NUMBER;
2782 --l_txn_ct NUMBER;
2783
2784 BEGIN
2785
2786 IF (l_debug = 1)
2787 THEN
2788 INV_LOG_UTIL.trace
2789 ( '>> Load Summarized Quantity','INV_CONSUMPTION_ADVICE_PROC'
2790 , 9
2791 );
2792 END IF;
2793
2794 -- Bug 5092489, commenting as not used
2795 /*l_txn_first := p_txn_source_tab.FIRST;
2796 l_txn_last := p_txn_source_tab.LAST;
2797 l_txn_ct := p_txn_source_tab.COUNT;
2798
2799 IF l_txn_first IS NULL
2800 THEN
2801 l_txn_first :=0;
2802 END IF;
2803
2804 IF l_txn_last IS NULL
2805 THEN
2806 l_txn_last :=0;
2807 END IF;
2808
2809 IF (l_debug = 1)
2810 THEN
2811 INV_LOG_UTIL.trace
2812 ( 'First is :'||l_txn_first,'INV_CONSUMPTION_ADVICE_PROC'
2813 , 9
2814 );
2815 INV_LOG_UTIL.trace
2816 ( 'Last is :'||l_txn_ct,'INV_CONSUMPTION_ADVICE_PROC'
2817 , 9
2818 );
2819 END IF;
2820 */
2821
2822 IF (l_debug = 1)
2823 THEN
2824 INV_LOG_UTIL.trace
2825 ( 'Last is :'||p_txn_source_tab.COUNT,'INV_CONSUMPTION_ADVICE_PROC'
2826 , 9
2827 );
2828 END IF;
2829
2830 -- Use the bulk update to summarize the net quantity for
2831 -- the current batch. The net quantity takes into account
2832 -- any corrections that were made to the transaction quantity
2833 -- by the user.
2834
2835 IF p_txn_source_tab.COUNT > 0
2836 THEN
2837
2838 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2839 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2840 SET (mctt.net_qty,mctt.secondary_net_qty) =
2841 (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2842
2843 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2844 --, MTL_MATERIAL_TRANSACTIONS mmt
2845 WHERE --mmt.transaction_id = mct.transaction_id AND
2846 mct.transaction_source_id = p_txn_source_tab(i)
2847 AND mct.inventory_item_id = p_inventory_item_tab(i)
2848 AND mct.organization_id = p_organization_tab(i)
2849 AND mct.owning_organization_id = p_own_org_tab(i)
2850 /* Bug 4969420 Starts here*/
2851 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
2852 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
2853 AND mct.blanket_price = p_transaction_cost_tab(i)
2854 /* Bug 4969421 Ends here*/
2855 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
2856 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
2857 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
2858 AND mct.accrual_account_id = p_accrual_account_tab(i)
2859 AND mct.charge_account_id = p_charge_account_tab(i)
2860 AND mct.variance_account_id = p_variance_account_tab(i)
2861 AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
2862 AND NVL(mct.rate,-1) = p_rate_tab(i)
2863 AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
2864 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2865 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2866 /*Bug 4969421 - Ends here */
2867 AND mct.batch_id = p_batch_id
2868 AND mct.consumption_processed_flag IN ('N', 'E'))
2869 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
2870 AND mctt.inventory_item_id = p_inventory_item_tab(i)
2871 AND mctt.organization_id = p_organization_tab(i)
2872 AND mctt.owning_organization_id = p_own_org_tab(i)
2873 AND mctt.transaction_cost = p_transaction_cost_tab(i)
2874 AND mctt.tax_code_id = p_tax_code_tab(i)
2875 AND mctt.recoverable_tax = p_rec_tax_tab(i)
2876 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
2877 AND mctt.accrual_account_id = p_accrual_account_tab(i)
2878 AND mctt.charge_account_id = p_charge_account_tab(i)
2879 AND mctt.variance_account_id = p_variance_account_tab(i)
2880 AND mctt.transaction_date = TRUNC(p_date_tab(i))
2881 AND mctt.rate = p_rate_tab(i)
2882 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2883 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2884 /*Bug 4969421 - Ends here */
2885 AND mctt.rate_type = p_rate_type_tab(i);
2886
2887
2888 ELSE
2889 IF (l_debug = 1)
2890 THEN
2891 INV_LOG_UTIL.trace
2892 ( '<< Load Summarized Quantity null index','INV_CONSUMPTION_ADVICE_PROC' , 9
2893 );
2894 END IF;
2895 END IF;
2896
2897 IF (l_debug = 1)
2898 THEN
2899 INV_LOG_UTIL.trace
2900 ( '<< Load Summarized Quantity','INV_CONSUMPTION_ADVICE_PROC'
2901 , 9
2902 );
2903 END IF;
2904
2905 EXCEPTION
2906
2907 WHEN OTHERS THEN
2908 IF (l_debug = 1) THEN
2909 INV_LOG_UTIL.trace
2913 END IF;
2910 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2911 , 9
2912 );
2914
2915 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_SUM');
2916 FND_MSG_PUB.ADD;
2917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2918
2919 END load_summarized_quantity;
2920
2921 --========================================================================
2922 -- PROCEDURE : Load_Summarized_Quantity_prf PRIVATE
2923 -- COMMENT : This procedure summarizes records in
2924 -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2925 -- transaction_source_id, inventory_item_id, organization_id
2926 -- transaction cost, tax code id, accrual account, variance
2927 -- account, charge account
2928 -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2929 -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2930 -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2931 -- for inventory convergence project.
2932 --=========================================================================
2933 PROCEDURE load_summarized_quantity_prf
2934 ( p_txn_source_tab IN g_cons_tbl_type
2935 , p_inventory_item_tab IN g_cons_tbl_type
2936 , p_organization_tab IN g_cons_tbl_type
2937 , p_own_org_tab IN g_cons_tbl_type
2938 , p_transaction_cost_tab IN g_cons_tbl_type
2939 , p_tax_code_tab IN g_cons_tbl_type
2940 , p_rec_tax_tab IN g_cons_tbl_type
2941 , p_non_rec_tax_tab IN g_cons_tbl_type
2942 , p_accrual_account_tab IN g_cons_tbl_type
2943 , p_charge_account_tab IN g_cons_tbl_type
2944 , p_variance_account_tab IN g_cons_tbl_type
2945 , p_date_tab IN g_cons_date_tbl_type
2946 , p_rate_tab IN g_cons_tbl_type
2947 , p_rate_type_tab IN g_cons_varchar_tbl_type
2948 , p_batch_id IN NUMBER
2949 , p_tax_rec_rate_tab IN g_cons_tbl_type -- Bug 4969421
2950 )
2951 IS
2952 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2953 -- Bug 5092489, commenting as not used
2954 --l_txn_count NUMBER;
2955 --l_txn_first NUMBER;
2956 --l_txn_last NUMBER;
2957 --l_txn_ct NUMBER;
2958 BEGIN
2959
2960 IF (l_debug = 1)
2961 THEN
2962 INV_LOG_UTIL.trace
2963 ( '>> Load Summarized Quantity Prf','INV_CONSUMPTION_ADVICE_PROC'
2964 , 9
2965 );
2966 END IF;
2967
2968 -- Bug 5092489, commenting as not used
2969 /*l_txn_first := p_txn_source_tab.FIRST;
2970 l_txn_last := p_txn_source_tab.LAST;
2971 l_txn_ct := p_txn_source_tab.COUNT;
2972
2973 IF l_txn_first IS NULL
2974 THEN
2975 l_txn_first :=0;
2976 END IF;
2977
2978 IF l_txn_last IS NULL
2979 THEN
2980 l_txn_last :=0;
2981 END IF;
2982
2983 IF (l_debug = 1)
2984 THEN
2985 INV_LOG_UTIL.trace
2986 ( 'First is :'||l_txn_first,'INV_CONSUMPTION_ADVICE_PROC'
2987 , 9
2988 );
2989 INV_LOG_UTIL.trace
2990 ( 'Last is :'||l_txn_last||' '||l_txn_ct,'INV_CONSUMPTION_ADVICE_PROC'
2991 , 9
2992 );
2993 END IF;
2994 */
2995
2996 IF (l_debug = 1)
2997 THEN
2998 INV_LOG_UTIL.trace
2999 ( 'Last is :'||p_txn_source_tab.COUNT,'INV_CONSUMPTION_ADVICE_PROC'
3000 , 9
3001 );
3002 END IF;
3003
3004 -- Use the bulk update to summarize the net quantity for
3005 -- the current batch. The net quantity takes into account
3006 -- any corrections that were made to the transaction quantity
3007 -- by the user.
3008
3009 IF p_txn_source_tab.COUNT > 0
3010 THEN
3011
3012 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
3013 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3014 SET (mctt.net_qty,mctt.secondary_net_qty) =
3015 (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
3016
3017 FROM MTL_CONSUMPTION_TRANSACTIONS mct
3018 --, MTL_MATERIAL_TRANSACTIONS mmt
3019 WHERE --mmt.transaction_id = mct.transaction_id AND
3020 mct.transaction_source_id = p_txn_source_tab(i)
3021 AND mct.inventory_item_id = p_inventory_item_tab(i)
3022 AND mct.organization_id = p_organization_tab(i)
3023 AND mct.owning_organization_id = p_own_org_tab(i)
3024 /* Bug 4969420 Starts here*/
3025 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
3026 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
3027 AND mct.blanket_price = p_transaction_cost_tab(i)
3028 /* Bug 4969421 Ends here*/
3029 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
3030 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
3031 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
3032 AND mct.accrual_account_id = p_accrual_account_tab(i)
3033 AND mct.charge_account_id = p_charge_account_tab(i)
3034 AND mct.variance_account_id = p_variance_account_tab(i)
3035 AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
3036 AND NVL(mct.rate,-1) = p_rate_tab(i)
3037 AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
3038 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
3039 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
3040 /*Bug 4969421 - Ends here */
3041 AND mct.batch_id = p_batch_id
3042 AND mct.consumption_processed_flag IN ('N', 'E'))
3043 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
3044 AND mctt.inventory_item_id = p_inventory_item_tab(i)
3045 AND mctt.organization_id = p_organization_tab(i)
3046 AND mctt.owning_organization_id = p_own_org_tab(i)
3047 AND mctt.transaction_cost = p_transaction_cost_tab(i)
3048 AND mctt.tax_code_id = p_tax_code_tab(i)
3049 AND mctt.recoverable_tax = p_rec_tax_tab(i)
3050 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
3051 AND mctt.accrual_account_id = p_accrual_account_tab(i)
3052 AND mctt.charge_account_id = p_charge_account_tab(i)
3053 AND mctt.variance_account_id = p_variance_account_tab(i)
3054 AND mctt.transaction_date = TRUNC(p_date_tab(i))
3055 AND mctt.rate = p_rate_tab(i)
3056 AND mctt.rate_type = p_rate_type_tab(i)
3057 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
3058 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
3059 /*Bug 4969421 - Ends here */
3060 AND mctt.global_agreement_flag = 'Y';
3061
3062 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
3063 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3064 SET mctt.net_qty =
3065 (SELECT SUM(mct.net_qty)
3066 FROM MTL_CONSUMPTION_TRANSACTIONS mct
3067 --, MTL_MATERIAL_TRANSACTIONS mmt
3068 WHERE --mmt.transaction_id = mct.transaction_id AND
3069 mct.transaction_source_id = p_txn_source_tab(i)
3070 AND mct.inventory_item_id = p_inventory_item_tab(i)
3071 AND mct.organization_id = p_organization_tab(i)
3072 AND mct.owning_organization_id = p_own_org_tab(i)
3073 /* Bug 4969420 Starts here*/
3074 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
3075 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
3076 AND mct.blanket_price = p_transaction_cost_tab(i)
3077 /* Bug 4969421 Ends here*/
3078 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
3079 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
3080 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
3081 AND mct.accrual_account_id = p_accrual_account_tab(i)
3082 AND mct.charge_account_id = p_charge_account_tab(i)
3083 AND mct.variance_account_id = p_variance_account_tab(i)
3084 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
3085 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
3086 /*Bug 4969421 - Ends here */
3087 AND mct.batch_id = p_batch_id
3088 AND mct.consumption_processed_flag IN ('N', 'E'))
3089 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
3090 AND mctt.inventory_item_id = p_inventory_item_tab(i)
3091 AND mctt.organization_id = p_organization_tab(i)
3092 AND mctt.owning_organization_id = p_own_org_tab(i)
3093 AND mctt.transaction_cost = p_transaction_cost_tab(i)-- mctt.transaction_cost is the blanket_price from MCT
3094 AND mctt.tax_code_id = p_tax_code_tab(i)
3095 AND mctt.recoverable_tax = p_rec_tax_tab(i)
3096 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
3097 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
3098 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
3099 /*Bug 4969421 - Ends here */
3100 AND mctt.accrual_account_id = p_accrual_account_tab(i)
3101 AND mctt.charge_account_id = p_charge_account_tab(i)
3102 AND mctt.variance_account_id = p_variance_account_tab(i)
3103 AND mctt.global_agreement_flag = 'N';
3104 ELSE
3105 IF (l_debug = 1)
3106 THEN
3107 INV_LOG_UTIL.trace
3108 ( '<< Load Summarized Quantity null index','INV_CONSUMPTION_ADVICE_PROC' , 9
3109 );
3110 END IF;
3111 END IF;
3112
3113 IF (l_debug = 1)
3114 THEN
3115 INV_LOG_UTIL.trace
3116 ( '<< Load Summarized Quantity_prf','INV_CONSUMPTION_ADVICE_PROC'
3117 , 9
3118 );
3119 END IF;
3120
3121 EXCEPTION
3122
3123 WHEN OTHERS THEN
3124 IF (l_debug = 1) THEN
3125 INV_LOG_UTIL.trace
3126 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
3127 , 9
3128 );
3129 END IF;
3130
3131 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_SUM');
3132 FND_MSG_PUB.ADD;
3133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134
3135 END load_summarized_quantity_prf;
3136
3137 --========================================================================
3138 -- PROCEDURE : Delete Record PRIVATE
3139 -- COMMENT : If the billing date for the current asl entry
3140 -- : has not elapsed yet then the associated
3141 -- : change of ownership transactions held in
3142 -- : MTL_CONSUMPTION_TRANSACTIONS should not be
3143 -- : processed yet. The current record slipped
3144 -- : into this loop for that reason and should
3145 -- : therefore be deleted from the current batch
3146
3147 --=========================================================================
3148 PROCEDURE delete_record
3149 ( p_txn_source_id IN NUMBER
3150 , p_inventory_item_id IN NUMBER
3151 , p_organization_id IN NUMBER
3152 , p_own_org_id IN NUMBER
3153 , p_price IN NUMBER
3154 , p_tax_code_id IN NUMBER
3155 , p_rec_tax_id IN NUMBER
3159 , p_variance_account_id IN NUMBER
3156 , p_non_rec_tax_id IN NUMBER
3157 , p_accrual_account_id IN NUMBER
3158 , p_charge_account_id IN NUMBER
3160 , p_date IN DATE
3161 , p_rate IN NUMBER
3162 , p_rate_type IN VARCHAR
3163 )
3164 IS
3165 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3166 BEGIN
3167
3168 IF (l_debug = 1)
3169 THEN
3170 INV_LOG_UTIL.trace
3171 ( '>> Delete Record','INV_CONSUMPTION_ADVICE_PROC'
3172 , 9
3173 );
3174 END IF;
3175
3176 DELETE FROM mtl_consumption_txn_temp mctt
3177 WHERE mctt.transaction_source_id = p_txn_source_id
3178 AND mctt.inventory_item_id = p_inventory_item_id
3179 AND mctt.organization_id = p_organization_id
3180 AND mctt.owning_organization_id = p_own_org_id
3181 AND mctt.transaction_cost = p_price
3182 AND mctt.tax_code_id = p_tax_code_id
3183 AND mctt.recoverable_tax = p_rec_tax_id
3184 AND mctt.non_recoverable_tax = p_non_rec_tax_id
3185 AND mctt.accrual_account_id = p_accrual_account_id
3186 AND mctt.charge_account_id = p_charge_account_id
3187 AND mctt.variance_account_id = p_variance_account_id
3188 AND mctt.transaction_date = TRUNC(p_date)
3189 AND mctt.rate = p_rate
3190 AND mctt.rate_type = p_rate_type;
3191
3192 -- Bug 5092489. Query modified for better performance
3193 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
3194 SET mct.batch_id = NULL
3195 WHERE mct.transaction_source_id = p_txn_source_id
3196 AND mct.inventory_item_id = p_inventory_item_id
3197 AND mct.organization_id = p_organization_id
3198 AND mct.owning_organization_id = p_own_org_id
3199 AND mct.accrual_account_id = p_accrual_account_id
3200 /* Bug 4969420 Starts here*/
3201 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
3202 --AND mmt.transaction_cost = p_price
3203 -- AND mct.blanket_price = p_price
3204 /* Bug 4969420 Ends here*/
3205 AND TRUNC(mct.transaction_date) = TRUNC(p_date)
3206 AND NVL(mct.tax_code_id,-1) = p_tax_code_id
3207 AND NVL(mct.recoverable_tax,0) = p_rec_tax_id
3208 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_id
3209 AND mct.charge_account_id = p_charge_account_id
3210 AND mct.variance_account_id = p_variance_account_id
3211 AND NVL(mct.rate,-1) = p_rate
3212 AND NVL(mct.rate_type,'##') = p_rate_type
3213 AND mct.consumption_processed_flag IN ('N', 'E');
3214
3215 IF (l_debug = 1)
3216 THEN
3217 INV_LOG_UTIL.trace
3218 ( '<< Delete Record','INV_CONSUMPTION_ADVICE_PROC'
3219 , 9
3220 );
3221 END IF;
3222
3223 EXCEPTION
3224
3225 WHEN OTHERS THEN
3226 IF (l_debug = 1) THEN
3227 INV_LOG_UTIL.trace
3228 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
3229 , 9
3230 );
3231 END IF;
3232
3233 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_DEL_REC');
3234 FND_MSG_PUB.ADD;
3235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3236
3237 END delete_record;
3238
3239 --========================================================================
3240 -- PROCEDURE : Log_Initialize PRIVATE
3241 -- COMMENT : Initializes the log facility. It should be called from
3242 -- the top level procedure of each concurrent program
3243 --========================================================================
3244 PROCEDURE Log_Initialize
3245 IS
3246 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3247 BEGIN
3248
3249 IF (l_debug = 1)
3250 THEN
3251 INV_LOG_UTIL.trace
3252 ( '>> Log Initialize','INV_CONSUMPTION_ADVICE_PROC'
3253 , 9
3254 );
3255 END IF;
3256
3257 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
3258 IF g_log_level IS NULL THEN
3259 g_log_mode := 'OFF';
3260 ELSE
3261 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
3262 g_log_mode := 'SRS';
3263 ELSE
3264 g_log_mode := 'SQL';
3265 END IF;
3266 END IF;
3267
3268 IF (l_debug = 1)
3269 THEN
3270 INV_LOG_UTIL.trace
3271 ( '<< Log Initialize','INV_CONSUMPTION_ADVICE_PROC'
3272 , 9
3273 );
3274 END IF;
3275
3276 END Log_Initialize;
3277
3278 --========================================================================
3279 -- PROCEDURE : Log PRIVATE
3280 -- PARAMETERS: p_level IN 2
3281 -- -- G_LOG_PROCEDURE
3282 -- p_msg IN message to be print on the log
3283 -- file
3284 -- COMMENT : Add an entry to the log
3285 --=======================================================================--
3286 PROCEDURE LOG
3287 ( p_priority IN NUMBER
3288 , p_msg IN VARCHAR2
3289 )
3290 IS
3291 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3292 BEGIN
3293
3294 IF (l_debug = 1)
3295 THEN
3296 INV_LOG_UTIL.trace
3297 ( '>> Log','INV_CONSUMPTION_ADVICE_PROC'
3298 , 9
3299 );
3300 END IF;
3301
3302 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
3303 THEN
3304 IF g_log_mode = 'SQL'
3305 THEN
3306 -- SQL*Plus session: uncomment the next line during unit test
3307 -- DBMS_OUTPUT.put_line(p_msg);
3308 NULL;
3309 ELSE
3310 -- Concurrent request
3311 FND_FILE.put_line
3312 ( FND_FILE.LOG
3313 , p_msg
3314 );
3315 END IF;
3316 END IF;
3317
3318 IF (l_debug = 1)
3319 THEN
3320 INV_LOG_UTIL.trace
3321 ( '<< Log','INV_CONSUMPTION_ADVICE_PROC'
3322 , 9
3323 );
3324 END IF;
3325
3326 EXCEPTION
3327 WHEN OTHERS THEN
3328 NULL;
3329 END LOG;
3330
3331 --========================================================================
3332 -- FUNCTION : Generate_Log PRIVATE
3333 -- PARAMETERS: None
3334 -- RETURNS : NUMBER
3335 -- COMMENT : This procedure is called when there are errors
3336 -- : in creating the consumption advice.
3337 -- : It populates a log file with the triansaction ids
3338 -- : of records that have failed. The user can view
3339 -- : this information through the log of an application request
3340 --=========================================================================
3341 PROCEDURE generate_log
3342 ( p_batch_id IN NUMBER
3343 )
3344 IS
3345
3346 --=================
3347 -- CURSORS
3348 --=================
3349
3350 CURSOR con_ad_err_cur IS
3351 SELECT mct.transaction_id
3352 , mct.error_code
3353 FROM MTL_CONSUMPTION_TRANSACTIONS mct
3354 WHERE mct.consumption_processed_flag = 'E'
3355 AND mct.batch_id = p_batch_id;
3356
3357 --=================
3358 -- VARIABLES
3359 --=================
3360
3361 l_error_code VARCHAR2(10);
3362 l_txn_id NUMBER;
3363 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3364
3365
3366 BEGIN
3367
3368 IF (l_debug = 1)
3369 THEN
3370 INV_LOG_UTIL.trace
3371 ( '>> Generate Log','INV_CONSUMPTION_ADVICE_PROC'
3372 , 9
3373 );
3374 END IF;
3375
3376 INV_CONSUMPTION_ADVICE_PROC.LOG
3377 ( INV_CONSUMPTION_ADVICE_PROC.G_LOG_PROCEDURE
3378 , '< Consumption Advice Error'
3379 );
3380
3381 OPEN con_ad_err_cur;
3382 LOOP
3383 FETCH con_ad_err_cur
3384 INTO
3385 l_txn_id
3386 ,l_error_code;
3387
3388 IF con_ad_err_cur%NOTFOUND THEN
3389 EXIT;
3390 END IF;
3391
3392 END LOOP;
3393
3394 CLOSE con_ad_err_cur;
3395
3396 INV_CONSUMPTION_ADVICE_PROC.LOG
3397 ( INV_CONSUMPTION_ADVICE_PROC.G_LOG_PROCEDURE
3398 , '> Consumption Advice Error'
3399 );
3400
3401 IF (l_debug = 1)
3402 THEN
3403 INV_LOG_UTIL.trace
3404 ( '<< Generate Log','INV_CONSUMPTION_ADVICE_PROC'
3405 , 9
3406 );
3407 END IF;
3408
3409
3410 END generate_log;
3411
3412 --========================================================================
3413 -- PROCEDURE : Consumption_Txn_Worker PRIVATE
3414 -- COMMENT : This procedure will copy all the records of a context batch
3415 -- from MTL_CONSUMPTION_TRANSACTIONS to
3416 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
3417 -- summarize the net quantity and call the create consumption
3418 -- advice procedure
3419 --=========================================================================
3420 PROCEDURE consumption_txn_worker
3421 ( p_batch_id IN NUMBER
3422 )
3423 IS
3424
3425 --=================
3426 -- CURSORS
3427 --=================
3428
3429 CURSOR cons_temp_csr_type IS
3430 SELECT mctt.transaction_source_id
3431 , mctt.inventory_item_id
3432 , mctt.organization_id
3433 , mctt.owning_organization_id
3434 , mctt.transaction_cost-- This is the blanket_price from MCT Bug 4969421
3435 , mctt.tax_code_id
3436 , mctt.recoverable_tax
3437 , mctt.non_recoverable_tax
3438 , mctt.accrual_account_id
3439 , mctt.charge_account_id
3440 , mctt.variance_account_id
3441 , mctt.rate
3442 , mctt.rate_type
3443 , mctt.transaction_date
3444 , mctt.tax_recovery_rate -- Bug 4969420
3445 FROM MTL_CONSUMPTION_TXN_TEMP mctt
3446 /* bug 5113064 - Start */
3447 /* filter just for the given batch */
3448 WHERE batch_id =p_batch_id ;
3449 /* bug 5113064 - End*/
3450
3451 --=================
3452 -- LOCAL VARIABLES
3453 --=================
3454
3455 l_current_cons_index BINARY_INTEGER := 0;
3456 l_empty_cons_tab g_cons_tbl_type;
3457 l_empty_date_cons_tab g_cons_date_tbl_type;
3458 l_empty_varchar_cons_tab g_cons_varchar_tbl_type;
3459 l_txn_source_tab g_cons_tbl_type;
3460 l_item_tab g_cons_tbl_type;
3461 l_org_tab g_cons_tbl_type;
3462 l_owning_org_tab g_cons_tbl_type;
3463 l_price_tab g_cons_tbl_type;
3464 l_tax_code_tab g_cons_tbl_type;
3465 l_rec_tax_tab g_cons_tbl_type;
3466 l_non_rec_tax_tab g_cons_tbl_type;
3467 l_accrual_account_tab g_cons_tbl_type;
3468 l_charge_account_tab g_cons_tbl_type;
3469 l_variance_account_tab g_cons_tbl_type;
3470 l_rate_tab g_cons_tbl_type;
3471 l_rate_type_tab g_cons_varchar_tbl_type;
3472 l_tax_rec_rate_tab g_cons_tbl_type; -- Bug 4969421
3473 l_date_tab g_cons_date_tbl_type;
3474 -- Bug 5092489. commented becasue not used
3475 --l_last_billing_date DATE;
3476 --l_next_billing_date DATE;
3477 --l_con_bill_cycle NUMBER;
3478 l_bulk_count NUMBER := 0;
3479 l_loop_count NUMBER := 0;
3480 l_vendor_id NUMBER;
3481 l_vendor_site_id NUMBER;
3482 -- Bug 5092489. commented becasue not used
3483 --l_con_from_sup_flag NUMBER;
3484 --l_enable_vmi_flag NUMBER;
3485 l_document_header_id NUMBER;
3486 l_document_line_id NUMBER;
3487 l_bill_date_elapsed NUMBER := 0;
3488 l_batch_id NUMBER;
3489 l_return_status VARCHAR2(24);
3490 l_count NUMBER;
3491 l_asl_id NUMBER;
3492 l_vendor_product_num VARCHAR2(25);
3493 l_purchasing_uom VARCHAR2(25);
3494 l_organization_id NUMBER := 0;
3495 l_current_txn_source_id NUMBER := 0;
3496 l_inventory_item_id NUMBER := 0;
3497 l_debug NUMBER :=
3498 NVL(FND_PROFILE.VALUE
3499 ('INV_DEBUG_TRACE'),0);
3500
3501
3502 BEGIN
3503
3504 IF (l_debug = 1)
3505 THEN
3506 INV_LOG_UTIL.trace
3507 ( '>> Consumption Txn Worker(p_batch_id)'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
3508 , 9
3509 );
3510 END IF;
3511
3512 -- Call procedure to load MTL_CONSUMPTION_TXNS_TEMP
3513 -- from MTL_CONSUMPTION_TRANSACTIONS
3514 -- with records that belong to the specified batch
3515
3516 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
3517 THEN
3518 INV_CONSUMPTION_ADVICE_PROC.load_combination_prf
3519 (p_batch_id => p_batch_id
3520 ,p_vendor_id => NULL
3521 ,p_vendor_site_id => NULL
3522 ,p_inventory_item_id => NULL
3523 ,p_organization_id => NULL);
3524 ELSE
3525 INV_CONSUMPTION_ADVICE_PROC.load_combination
3526 (p_batch_id => p_batch_id
3527 ,p_vendor_id => NULL
3528 ,p_vendor_site_id => NULL
3529 ,p_inventory_item_id => NULL
3530 ,p_organization_id => NULL);
3531
3532 END IF;
3533
3534 -- open cursor
3535
3536 IF NOT cons_temp_csr_type%ISOPEN
3537 THEN
3538 OPEN cons_temp_csr_type;
3539 END IF;
3540
3541
3542 LOOP
3543
3544 l_txn_source_tab := l_empty_cons_tab;
3545 l_item_tab := l_empty_cons_tab;
3546 l_org_tab := l_empty_cons_tab;
3547 l_owning_org_tab := l_empty_cons_tab;
3548 l_price_tab := l_empty_cons_tab;
3549 l_tax_code_tab := l_empty_cons_tab;
3550 l_rec_tax_tab := l_empty_cons_tab;
3551 l_non_rec_tax_tab := l_empty_cons_tab;
3552 l_accrual_account_tab := l_empty_cons_tab;
3553 l_charge_account_tab := l_empty_cons_tab;
3554 l_variance_account_tab := l_empty_cons_tab;
3555 l_rate_tab := l_empty_cons_tab;
3556 l_rate_type_tab := l_empty_varchar_cons_tab;
3557 l_date_tab := l_empty_date_cons_tab;
3558 l_batch_id := p_batch_id;
3559 l_tax_rec_rate_tab := l_empty_cons_tab; -- Bug 4969421
3560
3561 -- Bulk population of pl/sql table
3562
3563 FETCH cons_temp_csr_type
3564 BULK COLLECT INTO l_txn_source_tab
3565 ,l_item_tab
3566 ,l_org_tab
3567 ,l_owning_org_tab
3568 ,l_price_tab
3569 ,l_tax_code_tab
3570 ,l_rec_tax_tab
3571 ,l_non_rec_tax_tab
3572 ,l_accrual_account_tab
3573 ,l_charge_account_tab
3574 ,l_variance_account_tab
3575 ,l_rate_tab
3576 ,l_rate_type_tab
3577 ,l_date_tab
3578 ,l_tax_rec_rate_tab -- Bug 4969420
3579 LIMIT 1000;
3580
3581 /* But 5006151 - Start */
3582 /* When the number of records in the MTL_CONSUMPTION_TXN_TEMP table is a
3583 multiple of 1000, the worker does not exit correctly with the check
3584 'EXIT WHEN l_loop_count < 1000' as l_loop_count equals '1000'.
3585 The following check would ensure that the worker exits correctly */
3586 EXIT WHEN l_bulk_count = cons_temp_csr_type%ROWCOUNT;
3587 /* Bug 5006151 - End */
3588
3589 -- Summarize the net quantity of all records in
3590 -- MTL_CONSUMPTION_TRANSACTIONS associated with
3591 -- records in MTL_CONSUMPTION_TXNS_TEMP.
3592 -- Record the result in MTL_CONSUMPTION_TXNS_TEMP.
3593
3594 /* BUg 5092489. Following IF block is commented becasue quantity is populated during insert
3598 (p_txn_source_tab => l_txn_source_tab
3595 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
3596 THEN
3597 INV_CONSUMPTION_ADVICE_PROC.load_summarized_quantity_prf
3599 ,p_inventory_item_tab => l_item_tab
3600 ,p_organization_tab => l_org_tab
3601 ,p_own_org_tab => l_owning_org_tab
3602 ,p_transaction_cost_tab => l_price_tab -- blanket_price from MCT Bug 4969421
3603 ,p_tax_code_tab => l_tax_code_tab
3604 ,p_rec_tax_tab => l_rec_tax_tab
3605 ,p_non_rec_tax_tab => l_non_rec_tax_tab
3606 ,p_accrual_account_tab => l_accrual_account_tab
3607 ,p_charge_account_tab => l_charge_account_tab
3608 ,p_variance_account_tab => l_variance_account_tab
3609 ,p_rate_tab => l_rate_tab
3610 ,p_rate_type_tab => l_rate_type_tab
3611 ,p_date_tab => l_date_tab
3612 ,p_batch_id => l_batch_id
3613 ,p_tax_rec_rate_tab => l_tax_rec_rate_tab); -- Bug 4969421
3614 ELSE
3615 INV_CONSUMPTION_ADVICE_PROC.load_summarized_quantity
3616 (p_txn_source_tab => l_txn_source_tab
3617 ,p_inventory_item_tab => l_item_tab
3618 ,p_organization_tab => l_org_tab
3619 ,p_own_org_tab => l_owning_org_tab
3620 ,p_transaction_cost_tab => l_price_tab -- blanket_price from MCT Bug 4969421
3621 ,p_tax_code_tab => l_tax_code_tab
3622 ,p_rec_tax_tab => l_rec_tax_tab
3623 ,p_non_rec_tax_tab => l_non_rec_tax_tab
3624 ,p_accrual_account_tab => l_accrual_account_tab
3625 ,p_charge_account_tab => l_charge_account_tab
3626 ,p_variance_account_tab => l_variance_account_tab
3627 ,p_rate_tab => l_rate_tab
3628 ,p_rate_type_tab => l_rate_type_tab
3629 ,p_date_tab => l_date_tab
3630 ,p_batch_id => l_batch_id
3631 ,p_tax_rec_rate_tab => l_tax_rec_rate_tab); -- Bug 4969421
3632 END IF;
3633 */
3634
3635 -- The following loop removes records from the temp table
3636 -- if they are not candidates to populate the ensuing
3637 -- Consumption Advice document.
3638 -- The criteria for this decision being the
3639 -- state of their billing cycle
3640
3641 l_loop_count := cons_temp_csr_type%ROWCOUNT - l_bulk_count;
3642
3643 IF (l_debug = 1)
3644 THEN
3645 INV_LOG_UTIL.trace
3646 ( 'Consumption Txn Worker(l_loop_count)'||l_loop_count,'INV_CONSUMPTION_ADVICE_PROC'
3647 , 9
3648 );
3649 END IF;
3650
3651 FOR i IN 1..l_loop_count
3652 LOOP
3653
3654 IF l_txn_source_tab(i) <> l_current_txn_source_id OR
3655 l_org_tab(i) <> l_organization_id OR
3656 l_item_tab(i) <> l_inventory_item_id THEN
3657
3658 l_current_txn_source_id := l_txn_source_tab(i);
3659 l_organization_id := l_org_tab(i);
3660 l_inventory_item_id := l_item_tab(i);
3661
3662 IF (l_debug = 1)
3663 THEN
3664 INV_LOG_UTIL.trace
3665 ( 'Consumption Worker(l_current_txn_source_id)'||l_current_txn_source_id,'INV_CONSUMPTION_ADVICE_PROC'
3666 , 9
3667 );
3668 INV_LOG_UTIL.trace
3669 ( 'Cons Worker(l_organization_id)'||l_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
3670 , 9
3671 );
3672 INV_LOG_UTIL.trace
3673 ( 'Cons Worker(l_inventory_item_id)'||l_inventory_item_id,'INV_CONSUMPTION_ADVICE_PROC'
3674 , 9
3675 );
3676 END IF;
3677
3678 -- Find the vendor location information
3679 -- Bug 5092489. Query modified
3680 SELECT pvsa.vendor_id
3681 INTO l_vendor_id
3682 FROM po_vendor_sites_all pvsa
3683 WHERE pvsa.vendor_site_id = l_owning_org_tab(i)
3684 AND ROWNUM = 1;
3685
3686 IF (l_debug = 1)
3687 THEN
3688 INV_LOG_UTIL.trace
3689 ( 'Consumption Txn Worker(l_vendor_id)'||l_vendor_id,'INV_CONSUMPTION_ADVICE_PROC'
3690 , 9
3691 );
3692 END IF;
3693
3694 -- Find the billing cycle for each record in the temp table
3695 -- if no asl_id is found then the org id is updated to -1
3696 -- however in such cases we still process the transaction
3697 -- therefore the following line is required
3698
3699 INV_PO_THIRD_PARTY_STOCK_MDTR.get_asl_info
3700 (p_item_id => l_item_tab(i)
3701 ,p_vendor_id => l_vendor_id
3702 ,p_vendor_site_id => l_owning_org_tab(i)
3703 ,p_using_organization_id => l_organization_id
3704 ,x_asl_id => l_asl_id
3705 ,x_vendor_product_num => l_vendor_product_num
3706 ,x_purchasing_uom => l_purchasing_uom);
3707
3708 IF (l_debug = 1)
3709 THEN
3710 INV_LOG_UTIL.trace
3711 ( 'Consumption Txn Worker(l_asl_id)'||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
3712 , 9
3713 );
3714 END IF;
3715
3716 -- Update the temp table with the currency of the blanket
3717
3718 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3719 SET mctt.currency_code = (SELECT poa.currency_code
3720 FROM po_headers_all poa
3721 WHERE poa.po_header_id
3722 = l_txn_source_tab(i))
3723 ,mctt.asl_id = l_asl_id
3724 WHERE mctt.transaction_source_id = l_txn_source_tab(i)
3725 AND mctt.organization_id = l_org_tab(i)
3726 AND mctt.inventory_item_id = l_item_tab(i);
3727
3728 END IF;
3729
3730 -- Even if no ASL_ID is returned then continue and process the record
3731
3732 IF l_asl_id IS NULL
3733 THEN
3734
3735 l_bill_date_elapsed := 0;
3736
3737 ELSE
3738
3739 INV_PO_THIRD_PARTY_STOCK_MDTR.get_elapsed_info
3740 (p_org_id => l_org_tab(i)
3741 ,p_asl_id => l_asl_id
3742 ,x_bill_date_elapsed => l_bill_date_elapsed);
3743
3744 IF (l_debug = 1)
3745 THEN
3746 INV_LOG_UTIL.trace
3747 ( 'Consumption Txn Worker(l_elapsed)'||l_bill_date_elapsed,'INV_CONSUMPTION_ADVICE_PROC'
3748 , 9
3749 );
3750 INV_LOG_UTIL.trace
3751 ( 'Consumption Txn Worker(l_org_tab)'||l_org_tab(i),'INV_CONSUMPTION_ADVICE_PROC'
3752 , 9
3753 );
3754 END IF;
3755
3756 END IF;
3757
3758 -- If the billing cycle has not elapsed yet then
3759 -- delete that record from the temp table
3760 -- This will have the effect of deferring the processing
3761 -- of the record until it's billing cycle has elapsed
3762
3763 IF l_bill_date_elapsed = 1 THEN
3764
3765 -- delete the record from the temporary table
3766 IF (l_debug = 1)
3767 THEN
3768 INV_LOG_UTIL.trace
3769 ( 'Consumption Txn Worker:Inside delete record','INV_CONSUMPTION_ADVICE_PROC'
3770 , 9
3771 );
3772 END IF;
3773
3774 INV_CONSUMPTION_ADVICE_PROC.delete_record
3775 (p_txn_source_id => l_txn_source_tab(i)
3776 ,p_inventory_item_id => l_item_tab(i)
3777 ,p_organization_id => l_org_tab(i)
3778 ,p_own_org_id => l_owning_org_tab(i)
3779 ,p_price => l_price_tab(i)
3780 ,p_tax_code_id => l_tax_code_tab(i)
3781 ,p_rec_tax_id => l_rec_tax_tab(i)
3782 ,p_non_rec_tax_id => l_non_rec_tax_tab(i)
3783 ,p_accrual_account_id => l_accrual_account_tab(i)
3784 ,p_charge_account_id => l_charge_account_tab(i)
3785 ,p_variance_account_id => l_variance_account_tab(i)
3786 ,p_rate => l_rate_tab(i)
3787 ,p_rate_type => l_rate_type_tab(i)
3788 ,p_date => l_date_tab(i));
3789
3790 END IF;
3791
3792 END LOOP;
3793 l_bulk_count := cons_temp_csr_type%ROWCOUNT;
3794
3795 IF (l_debug = 1)
3796 THEN
3797 INV_LOG_UTIL.trace
3798 ( 'Consumption Txn Worker(l_bulk_count)'||l_bulk_count,'INV_CONSUMPTION_ADVICE_PROC'
3799 , 9
3800 );
3801 END IF;
3802 EXIT WHEN l_loop_count < 1000;
3803
3804 END LOOP;
3805
3806 CLOSE cons_temp_csr_type;
3807
3808 -- Call the Load Interface Tables procedure to
3809 -- Load the PO interface tables and call the autocreate procedure
3810
3811 /* Bug 4599072 - Start */
3812 /* The Load_Interface_Tables_prf procedure will no longer be used */
3813
3814 INV_CONSUMPTION_ADVICE_PROC.load_interface_tables
3815 (p_batch_id => l_batch_id
3816 ,x_return_status => l_return_status);
3817 /* Bug 4599072 - Start */
3818 /* bug 5113064 - Start */
3819 /* clean up invalid records in MCT */
3820 UPDATE mtl_consumption_transactions
3821 SET batch_id = NULL
3822 WHERE batch_id = p_batch_id
3823 AND consumption_processed_flag IN ('N','E')
3824 AND consumption_po_header_id IS NULL
3825 AND consumption_release_id IS NULL;
3826 /* clean up MCT - end */
3827 /* bug 5113064 - Start */
3828
3829 IF l_return_status <> 'S' THEN
3830
3831 INV_CONSUMPTION_ADVICE_PROC.generate_log(l_batch_id);
3832
3833 END IF;
3834
3835 IF (l_debug = 1)
3836 THEN
3837 INV_LOG_UTIL.trace
3838 ( '<< Consumption Txn Worker','INV_CONSUMPTION_ADVICE_PROC'
3839 , 9
3840 );
3841 END IF;
3842
3843 END consumption_txn_worker;
3844
3845 --========================================================================
3846 -- FUNCTION : Generate_Batch_Id PRIVATE
3847 -- PARAMETERS: None
3848 -- RETURNS : NUMBER
3849 -- COMMENT : This function returns the next batch id to be assigned to
3850 -- the records in MTL_CONSUMPTION_TRANSACTIONS
3851 --=========================================================================
3852 FUNCTION generate_batch_id
3853 RETURN NUMBER
3854 IS
3855 l_batch_id NUMBER;
3856 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3857 BEGIN
3858
3859 IF (l_debug = 1)
3860 THEN
3861 INV_LOG_UTIL.trace
3862 ( '>> Generate Batch Id','INV_CONSUMPTION_ADVICE_PROC'
3863 , 9
3864 );
3865 END IF;
3866
3867 -- Generate sequence that will become the new batch id
3868
3869 SELECT mtl_third_party_cp_s.NEXTVAL
3870 INTO l_batch_id
3871 FROM dual;
3872
3873 RETURN l_batch_id;
3874
3875 IF (l_debug = 1)
3876 THEN
3877 INV_LOG_UTIL.trace
3878 ( '<< Generate Batch Id','INV_CONSUMPTION_ADVICE_PROC'
3879 , 9
3880 );
3881 END IF;
3882
3883 END generate_batch_id;
3884
3885 --========================================================================
3886 -- FUNCTION : Has_Worker_Completed PRIVATE
3887 -- PARAMETERS: p_request_id IN NUMBER
3888 -- RETURNS : BOOLEAN
3889 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
3890 -- has completed; FALSE otherwise
3891 --=========================================================================
3892 FUNCTION has_worker_completed
3893 ( p_request_id IN NUMBER
3894 )
3895 RETURN BOOLEAN
3896 IS
3897 l_count NUMBER;
3898 l_result BOOLEAN;
3899 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3900 BEGIN
3901
3902 IF (l_debug = 1)
3903 THEN
3904 INV_LOG_UTIL.trace
3905 ( '>> Has Worker Completed','INV_CONSUMPTION_ADVICE_PROC'
3906 , 9
3907 );
3908 END IF;
3909
3910 SELECT COUNT(*)
3911 INTO l_count
3912 FROM fnd_concurrent_requests
3913 WHERE request_id = p_request_id
3914 AND phase_code = 'C';
3915
3916 IF l_count = 1 THEN
3917 l_result := TRUE;
3918 ELSE
3919 l_result := FALSE;
3920 END IF;
3921
3922 RETURN l_result;
3923
3924 IF (l_debug = 1)
3925 THEN
3926 INV_LOG_UTIL.trace
3927 ( '<< Has Worker Completed','INV_CONSUMPTION_ADVICE_PROC'
3928 , 9
3929 );
3930 END IF;
3931
3932 END has_worker_completed;
3933
3934 --========================================================================
3935 -- PROCEDURE : Wait_For_Worker PRIVATE
3936 -- PARAMETERS: p_workers IN workers' request ID
3937 -- x_worker_idx OUT position in p_workers of the
3938 -- completed worked
3939 -- COMMENT : This procedure polls the submitted workers and suspend
3940 -- the program till the completion of one of them; it returns
3941 -- the completed worker through x_worker_idx
3942 --=========================================================================
3943 PROCEDURE wait_for_worker
3944 ( p_workers IN g_request_tbl_type
3945 , x_worker_idx OUT NOCOPY BINARY_INTEGER
3946 )
3947 IS
3948 l_done BOOLEAN;
3949 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3950 BEGIN
3951
3952 IF (l_debug = 1)
3953 THEN
3954 INV_LOG_UTIL.trace
3955 ( '>> Wait For Worker','INV_CONSUMPTION_ADVICE_PROC'
3956 , 9
3957 );
3958 END IF;
3959
3960 l_done := FALSE;
3961
3962 WHILE (NOT l_done) LOOP
3963
3964 FOR l_Idx IN 1..p_workers.COUNT LOOP
3965
3966 IF INV_CONSUMPTION_ADVICE_PROC.has_worker_completed(p_workers(l_Idx))
3967 THEN
3968 l_done := TRUE;
3969 x_worker_idx := l_Idx;
3970 EXIT;
3971 END IF;
3972
3973 END LOOP;
3974
3975 IF (NOT l_done) THEN
3976 DBMS_LOCK.sleep(G_SLEEP_TIME);
3977 END IF;
3978
3979 END LOOP;
3980
3981 IF (l_debug = 1)
3982 THEN
3983 INV_LOG_UTIL.trace
3984 ( '<< Wait For Worker','INV_CONSUMPTION_ADVICE_PROC'
3985 , 9
3986 );
3987 END IF;
3988
3989 END wait_for_worker;
3990
3991
3992 --========================================================================
3993 -- PROCEDURE : Wait_For_All_Workers PRIVATE
3994 -- PARAMETERS: p_workers IN workers' request ID
3995 -- COMMENT : This procedure polls the submitted workers and suspend
3996 -- the program till the completion of all of them.
3997 --=========================================================================
3998 PROCEDURE wait_for_all_workers
3999 ( p_workers IN g_request_tbl_type
4000 )
4001 IS
4002 l_done BOOLEAN;
4003 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4004 BEGIN
4005
4006 IF (l_debug = 1)
4007 THEN
4008 INV_LOG_UTIL.trace
4009 ( '>> Wait For All Workers','INV_CONSUMPTION_ADVICE_PROC'
4010 , 9
4011 );
4012 END IF;
4013
4014 l_done := FALSE;
4015
4016 WHILE (NOT l_done) LOOP
4017
4018 l_done := TRUE;
4019
4020 FOR l_Idx IN 1..p_workers.COUNT LOOP
4021
4022 IF NOT
4023 INV_CONSUMPTION_ADVICE_PROC.has_worker_completed(p_workers(l_Idx))
4024 THEN
4025 l_done := FALSE;
4026 EXIT;
4027 END IF;
4028
4029 END LOOP;
4030
4031 IF (NOT l_done) THEN
4032 DBMS_LOCK.sleep(G_SLEEP_TIME);
4033 END IF;
4034
4035 END LOOP;
4036
4037 IF (l_debug = 1)
4038 THEN
4039 INV_LOG_UTIL.trace
4040 ( '<< Wait For All Workers','INV_CONSUMPTION_ADVICE_PROC'
4041 , 9
4042 );
4043 END IF;
4044
4045 END wait_for_all_workers;
4046
4047
4048 --========================================================================
4049 -- PROCEDURE : Submit_Worker PRIVATE
4050 -- PARAMETERS: p_organization_id IN an organization
4051 -- p_set_process_id IN Set process ID
4052 -- x_workers IN OUT NOCOPY workers' request ID
4053 -- p_request_count IN max worker number
4054 -- COMMENT : This procedure submits the Worker concurrent program.
4055 -- Before submitting the request, it verifies that there are
4056 -- enough workers available and wait for the completion of one
4057 -- if necessary.
4058 -- The list of workers' request ID is updated.
4059 --=========================================================================
4060 PROCEDURE submit_worker
4061 ( p_batch_id IN NUMBER
4062 , p_request_count IN NUMBER
4063 , x_workers IN OUT NOCOPY g_request_tbl_type
4064 )
4065 IS
4066 l_worker_idx BINARY_INTEGER;
4067 l_request_id NUMBER;
4068 l_org_name VARCHAR2(60) := NULL;
4069 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4070 BEGIN
4071
4072 IF (l_debug = 1)
4073 THEN
4074 INV_LOG_UTIL.trace
4075 ( '>> Submit Worker','INV_CONSUMPTION_ADVICE_PROC'
4076 , 9
4077 );
4078 END IF;
4079
4080 IF x_workers.COUNT < p_request_count THEN
4081 -- number of workers submitted so far does not exceed the maximum
4082 -- number of workers allowed
4083 l_worker_idx := x_workers.COUNT + 1;
4084 ELSE
4085 -- need to wait for a submitted worker to finish
4086 INV_CONSUMPTION_ADVICE_PROC.wait_for_worker
4087 ( p_workers => x_workers
4088 , x_worker_idx => l_worker_idx
4089 );
4090 END IF;
4091 --bug7357385 start
4092 IF NOT FND_REQUEST.Set_Options
4093 (
4094 protected => 'YES'
4095 )
4096 --bug7357385 end
4097 THEN
4098 RAISE g_submit_failure_exc;
4099 END IF;
4100
4101 x_workers(l_worker_idx) := FND_REQUEST.submit_request
4102 ( application =>'INV'
4103 , program =>'INVCTXCW'
4104 , description => l_org_name
4105 , argument1 => p_batch_id
4106 );
4107
4108 IF x_workers(l_worker_idx) = 0 THEN
4109 RAISE g_submit_failure_exc;
4110 END IF;
4111
4112 COMMIT;
4113
4114 IF (l_debug = 1)
4115 THEN
4116 INV_LOG_UTIL.trace
4117 ( '<< Submit Worker','INV_CONSUMPTION_ADVICE_PROC'
4118 , 9
4119 );
4120 END IF;
4121
4122 END submit_worker;
4123
4124 --========================================================================
4125 -- PROCEDURE : Batch Allocation PRIVATE
4126 -- COMMENT : This procedure will be called exclusively from the manager.
4127 -- It divides candidate unprocessed records into batches.
4128 -- Once the contents of a batch are established the
4129 -- associated records in MTL_CONSUMPTION_TRANSACTIONS are
4130 -- updated with a batch id and a concurrent program submitted.
4131 -- If there are more candidate unprocessed records then further
4132 -- batches are established and concurrent programs submitted.
4133 -- Thus for large numbers of records concurrency can be achieved.
4134 -- The precedure ends and contol is passed back to the manager
4135 -- when all the submitted concurrent programs have completed.
4136 --=========================================================================
4137 PROCEDURE batch_allocation
4138 ( p_batch_size IN NUMBER
4139 , p_max_workers IN NUMBER
4140 )
4141 IS
4142
4143 --================
4144 -- CURSORS
4145 --================
4146
4147 CURSOR cons_temp_csr_type IS
4148 SELECT mctt.transaction_source_id
4149 , mctt.inventory_item_id
4150 , mctt.organization_id
4151 , mctt.transaction_cost-- blanket_price from MCT Bug 4969421
4152 , mctt.tax_code_id
4153 , mctt.accrual_account_id
4154 , mctt.charge_account_id
4155 , mctt.variance_account_id
4156 , mctt.rate
4157 , mctt.rate_type
4158 , mctt.transaction_date
4159 FROM MTL_CONSUMPTION_TXN_TEMP mctt
4160 ORDER BY mctt.transaction_source_id
4161 , mctt.organization_id;
4162
4163 --=================
4164 -- LOCAL VARIABLES
4165 --=================
4166
4167 l_previous_cons_index BINARY_INTEGER := 1;
4168 l_current_cons_index BINARY_INTEGER := 1;
4169 l_next_cons_index BINARY_INTEGER := 1;
4170 l_empty_cons_tab g_cons_tbl_type;
4171 l_empty_varchar_cons_tab g_cons_varchar_tbl_type;
4172 l_empty_date_cons_tab g_cons_date_tbl_type;
4173 l_txn_source_tab g_cons_tbl_type;
4174 l_item_tab g_cons_tbl_type;
4175 l_org_tab g_cons_tbl_type;
4176 l_price_tab g_cons_tbl_type;
4177 l_tax_code_tab g_cons_tbl_type;
4178 l_accrual_account_tab g_cons_tbl_type;
4179 l_charge_account_tab g_cons_tbl_type;
4180 l_variance_account_tab g_cons_tbl_type;
4181 l_rate_tab g_cons_tbl_type;
4182 l_rate_type_tab g_cons_varchar_tbl_type;
4183 l_date_tab g_cons_date_tbl_type;
4184 l_batch_id NUMBER;
4185 l_current_batch_id NUMBER;
4186 l_batch_size NUMBER;
4187 l_batch_count NUMBER := 1;
4188 l_group_size NUMBER;
4189 l_group_count NUMBER := 1;
4190 l_remain_batch_count NUMBER;
4191 l_workers_tbl g_request_tbl_type;
4192 l_count NUMBER;
4193 l_max_workers NUMBER;
4194 l_org_id NUMBER;
4195 l_new_batch VARCHAR2(1) := 'Y';
4196 l_debug NUMBER := NVL(FND_PROFILE.VALUE
4197 ('INV_DEBUG_TRACE'),0);
4198 l_debug_txn_id NUMBER;
4199
4200
4201 BEGIN
4202
4203 IF (l_debug = 1)
4204 THEN
4205 INV_LOG_UTIL.trace
4206 ( '>> Batch Allocation','INV_CONSUMPTION_ADVICE_PROC'
4207 , 9
4208 );
4209 END IF;
4210
4211 -- open cursor
4212
4213 IF NOT cons_temp_csr_type%ISOPEN
4214 THEN
4215 OPEN cons_temp_csr_type;
4216 END IF;
4217
4218 -- clear the pl/sql table before use
4219
4220 l_txn_source_tab := l_empty_cons_tab;
4221 l_item_tab := l_empty_cons_tab;
4222 l_org_tab := l_empty_cons_tab;
4223 l_price_tab := l_empty_cons_tab;
4224 l_tax_code_tab := l_empty_cons_tab;
4225 l_accrual_account_tab := l_empty_cons_tab;
4226 l_charge_account_tab := l_empty_cons_tab;
4227 l_variance_account_tab := l_empty_cons_tab;
4228 l_rate_tab := l_empty_cons_tab;
4229 l_rate_type_tab := l_empty_varchar_cons_tab;
4230 l_date_tab := l_empty_date_cons_tab;
4231
4232
4233 IF p_batch_size IS NOT NULL THEN
4234 l_batch_size := p_batch_size;
4235 ELSE
4236 l_batch_size := g_batch_size;
4237 END IF;
4238
4239 IF p_max_workers IS NOT NULL THEN
4240 l_max_workers := p_max_workers;
4241 ELSE
4242 l_max_workers := g_max_workers;
4243 END IF;
4244
4245 IF (l_debug = 1)
4246 THEN
4247 INV_LOG_UTIL.trace
4248 ( '>> Batch Allocation(p_batch_size)'||p_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4249 , 9
4250 );
4251 INV_LOG_UTIL.trace
4252 ( '>> Batch Allocation(l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4253 , 9
4254 );
4255 INV_LOG_UTIL.trace
4256 ( '>> Batch Allocation(l_max_workers)'||l_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4257 , 9
4258 );
4259 INV_LOG_UTIL.trace
4260 ( '>> Batch Allocation(p_max_workers)'||p_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4261 , 9
4262 );
4263 END IF;
4264
4265 -- population of pl/sql table
4266
4267 FETCH cons_temp_csr_type
4268 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1)
4269 ,l_item_tab(l_item_tab.COUNT+1)
4270 ,l_org_tab(l_org_tab.COUNT+1)
4271 ,l_price_tab(l_price_tab.COUNT+1)
4272 ,l_tax_code_tab(l_tax_code_tab.COUNT+1)
4273 ,l_accrual_account_tab(l_accrual_account_tab.COUNT+1)
4274 ,l_charge_account_tab(l_charge_account_tab.COUNT+1)
4275 ,l_variance_account_tab(l_variance_account_tab.COUNT+1)
4276 ,l_rate_tab(l_rate_tab.COUNT+1)
4277 ,l_rate_type_tab(l_rate_type_tab.COUNT+1)
4278 ,l_date_tab(l_date_tab.COUNT+1);
4279
4280
4281 WHILE cons_temp_csr_type%FOUND
4282 LOOP
4283
4284 FETCH cons_temp_csr_type
4285 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1)
4286 ,l_item_tab(l_item_tab.COUNT+1)
4287 ,l_org_tab(l_org_tab.COUNT+1)
4288 ,l_price_tab(l_price_tab.COUNT+1)
4289 ,l_tax_code_tab(l_tax_code_tab.COUNT+1)
4290 ,l_accrual_account_tab(l_accrual_account_tab.COUNT+1)
4291 ,l_charge_account_tab(l_charge_account_tab.COUNT+1)
4292 ,l_variance_account_tab(l_variance_account_tab.COUNT+1)
4293 ,l_rate_tab(l_rate_tab.COUNT+1)
4294 ,l_rate_type_tab(l_rate_type_tab.COUNT+1)
4295 ,l_date_tab(l_date_tab.COUNT+1);
4296
4297
4298 END LOOP;
4299
4300 CLOSE cons_temp_csr_type;
4301
4302 -- Allocate a batch id to records in MTL_CONSUMPTION_TRANSACTIONS
4303 -- A new batch is started if the current batch is full or if the
4304 -- number of summarized records in the next batch exceeds
4305 -- the size remaining in the current batch. The only exception to
4306 -- this rule is when the first record a new batch is being
4307 -- considered. If in that case the blanket size > batch size then
4308 -- the current batch is used.
4309
4310 l_current_batch_id := generate_batch_id();
4311 l_remain_batch_count := l_batch_size;
4312 l_current_cons_index := l_txn_source_tab.FIRST;
4313
4314 IF (l_debug = 1)
4315 THEN
4316 INV_LOG_UTIL.trace
4317 ( '>> Batch Alloc (l_current_batch_id)'||l_current_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4318 , 9
4319 );
4320 INV_LOG_UTIL.trace
4321 ( '>> Batch Alloc (l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4322 , 9
4323 );
4324 INV_LOG_UTIL.trace
4325 ( '>> Batch Alloc (l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4326 , 9
4327 );
4328 END IF;
4329
4330 -- Query how many summarized records have been loaded into
4331 -- in the temporary table MTL_CONSUMPTION_TXN_TEMP for the
4332 -- current blanket and set the group size accordingly
4333
4334 SELECT
4335 COUNT(*)
4336 INTO
4337 l_group_size
4338 FROM
4339 mtl_consumption_txn_temp mctt
4340 WHERE mctt.transaction_source_id = l_txn_source_tab(l_current_cons_index);
4341
4342 LOOP
4343
4344 l_group_count := l_group_count + 1;
4345
4346 IF (l_debug = 1)
4347 THEN
4348 INV_LOG_UTIL.trace
4349 ( '>> Batch Alloc(l_group_count)'||l_group_count,'INV_CONSUMPTION_ADVICE_PROC'
4350 , 9
4351 );
4352 INV_LOG_UTIL.trace
4353 ( '>> Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4354 , 9
4355 );
4356 END IF;
4357
4358 -- If the last record of the current blanket
4359 -- has been reached then reset the associate parameters
4360 -- for the next blanket in the batch
4361
4362 l_next_cons_index := l_current_cons_index + 1;
4363
4364 IF (l_debug = 1)
4365 THEN
4366 INV_LOG_UTIL.trace
4367 ( '>> Batch Alloc(l_next_cons_index)'||l_next_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4368 , 9
4369 );
4370 INV_LOG_UTIL.trace
4371 ( '>> Batch Alloc(l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4372 , 9
4373 );
4374 END IF;
4375
4376 IF l_group_count > l_group_size
4377 AND l_current_cons_index < l_txn_source_tab.LAST THEN
4378
4379 -- Find the number of summarized records in the next blanket
4380
4381 SELECT
4382 COUNT(*)
4383 INTO
4384 l_group_size
4385 FROM
4386 mtl_consumption_txn_temp mctt
4387 WHERE mctt.transaction_source_id =
4388 l_txn_source_tab(l_next_cons_index);
4389
4390 l_group_count := 1;
4391
4392 l_remain_batch_count := l_batch_size - l_batch_count;
4393 l_new_batch := 'N';
4394
4395 IF (l_debug = 1)
4396 THEN
4397 INV_LOG_UTIL.trace
4398 ( '>> Batch Alloc....group_count>group_size' ,'INV_CONSUMPTION_ADVICE_PROC'
4399 , 9
4400 );
4401 INV_LOG_UTIL.trace
4402 ( '>> Batch Alloc (l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4403 , 9
4404 );
4405 INV_LOG_UTIL.trace
4406 ( '>> Batch Alloc (l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4407 , 9
4408 );
4409 INV_LOG_UTIL.trace
4410 ( '>> Batch Alloc (l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4411 , 9
4412 );
4413 INV_LOG_UTIL.trace
4414 ( '>> Batch Alloc (l_batch_ct)'||l_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4415 , 9
4416 );
4417 INV_LOG_UTIL.trace
4418 ( '>> Batch Alloc....' ,'INV_CONSUMPTION_ADVICE_PROC'
4419 , 9
4420 );
4421 END IF;
4422
4423 END IF;
4424
4425 -- If the last record of the current batch has been reached
4426 -- OR the record count for the next
4427 -- blanket exceeds the remaining batch size
4428 -- then move to the next batch
4429 -- unless the batch count is 1 in which case continue
4430 -- with the current batch id
4431
4432 IF l_batch_count = l_batch_size
4433 OR (l_group_size > l_remain_batch_count AND l_new_batch = 'N')
4434 OR l_current_cons_index = l_txn_source_tab.LAST
4435 THEN
4436
4437 --IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
4438 --THEN
4439 FORALL i IN l_previous_cons_index..l_current_cons_index
4440 --Bug 5092489, Query modified to eliminate use of MMT
4441 UPDATE
4442 MTL_CONSUMPTION_TRANSACTIONS mct
4443 SET mct.batch_id = l_current_batch_id
4444 /* request id stamped to MCT - bug 5200436 - Start*/
4445 , mct.request_id = g_request_id
4446 WHERE mct.transaction_source_id = l_txn_source_tab(i)
4447 AND mct.batch_id = -1
4448 AND mct.consumption_processed_flag IN ('N','E')
4449 AND mct.inventory_item_id = l_item_tab(i)
4450 AND mct.organization_id = l_org_tab(i)
4451 AND mct.blanket_price = l_price_tab(i)
4452 AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
4453 AND NVL(mct.accrual_account_id,-1) =
4454 NVL(l_accrual_account_tab(i),-1)
4455 AND NVL(mct.charge_account_id,-1) =
4456 NVL(l_charge_account_tab(i),-1)
4457 AND NVL(mct.variance_account_id,-1) =
4458 NVL(l_variance_account_tab(i),-1)
4459 AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
4460 AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##');
4461 /* bug 5200436 - End*/
4462 /*
4463 WHERE mct.transaction_id IN
4464 (SELECT
4465 mmt.transaction_id
4466 FROM
4467 mtl_material_transactions mmt
4468 WHERE mmt.transaction_source_id = l_txn_source_tab(i)
4469 AND mmt.inventory_item_id = l_item_tab(i)
4470 AND mmt.organization_id = l_org_tab(i)
4471 /* Bug 4969421 Starts here*/
4472 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
4473 --AND mmt.transaction_cost = l_price_tab(i)
4474 /* AND mct.blanket_price = l_price_tab(i)
4475 /* Bug 4969420 Ends here*/
4476 /* AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
4477 AND NVL(mmt.distribution_account_id,-1) =
4478 NVL(l_accrual_account_tab(i),-1)
4479 AND NVL(mct.charge_account_id,-1) =
4480 NVL(l_charge_account_tab(i),-1)
4481 AND NVL(mct.variance_account_id,-1) =
4482 NVL(l_variance_account_tab(i),-1)
4483 AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
4484 AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##')
4485 AND mct.batch_id = -1
4486 AND mct.consumption_processed_flag IN ('N','E'));
4487 */
4488 --ELSE
4489
4490 --FORALL i IN l_previous_cons_index..l_current_cons_index
4491 -- --Bug 5092489, Query modified to eliminate use of MMT
4492 -- UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
4493 -- SET mct.batch_id = l_current_batch_id
4494 -- /* request id stamped to MCT - bug 5200436 - Start*/
4495 -- , mct.request_id = g_request_id
4496 -- WHERE mct.transaction_source_id = l_txn_source_tab(i)
4497 -- AND mct.consumption_processed_flag IN ('N','E')
4498 -- AND mct.inventory_item_id = l_item_tab(i)
4499 -- AND mct.organization_id = l_org_tab(i)
4500 -- AND mct.blanket_price = l_price_tab(i)
4501 -- AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
4502 -- AND NVL(mct.accrual_account_id,-1) =
4503 -- NVL(l_accrual_account_tab(i),-1)
4504 -- AND NVL(mct.charge_account_id,-1) =
4505 -- NVL(l_charge_account_tab(i),-1)
4506 -- AND NVL(mct.variance_account_id,-1) =
4507 -- NVL(l_variance_account_tab(i),-1)
4508 -- AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
4509 -- AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##')
4510 -- AND mct.batch_id = -1;
4511
4512 /* bug 5200436 - End*/
4513 /* WHERE mct.transaction_id IN
4514 (SELECT
4515 mmt.transaction_id
4516 FROM
4517 MTL_MATERIAL_TRANSACTIONS mmt
4518 WHERE mmt.transaction_source_id = l_txn_source_tab(i)
4519 AND mmt.inventory_item_id = l_item_tab(i)
4520 AND mmt.organization_id = l_org_tab(i)
4521 --AND mmt.transaction_cost = l_price_tab(i)
4522 AND mct.blanket_price = l_price_tab(i)
4523
4524 AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
4525 AND NVL(mmt.distribution_account_id,-1) =
4526 NVL(l_accrual_account_tab(i),-1)
4527 AND NVL(mct.charge_account_id,-1) =
4528 NVL(l_charge_account_tab(i),-1)
4529 AND NVL(mct.variance_account_id,-1) =
4530 NVL(l_variance_account_tab(i),-1)
4531 AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
4532 AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##')
4533 AND TRUNC(mmt.transaction_date) = TRUNC(l_date_tab(i))
4534 AND mct.batch_id = -1
4535 --AND mct.consumption_processed_flag <> 'Y');
4536 --Bug 5113064
4537 AND mct.consumption_processed_flag IN ('N', 'E'));
4538 */
4539
4540 --END IF;
4541
4542 -- Bug 5092489. l_previous_cons_index has to be set as l_current_cons_index
4543 l_previous_cons_index := l_current_cons_index+1;
4544
4545 -- Update the table MTL_CONSUMPTION_TRANSACTIONS with the
4546 -- current batch id for Transfer to consigned txns.
4547
4548
4549 UPDATE
4550 MTL_CONSUMPTION_TRANSACTIONS mct
4551 SET mct.batch_id = l_current_batch_id
4552 WHERE mct.parent_transaction_id IN
4553 (SELECT
4554 mct_in.transaction_id
4555 FROM
4556 MTL_CONSUMPTION_TRANSACTIONS mct_in
4557 WHERE mct_in.batch_id = l_current_batch_id
4558 --AND mct.consumption_processed_flag <> 'Y');
4559 --Bug 5092489
4560 AND mct_in.consumption_processed_flag IN ('N', 'E'))
4561 AND mct.consumption_processed_flag IN ('N', 'E');
4562
4563 -- Call concurrent worker
4564
4565 INV_CONSUMPTION_ADVICE_PROC.submit_worker( l_current_batch_id
4566 , l_max_workers
4567 , l_workers_tbl);
4568
4569 -- the size of the group can only be a maximum of the batch size
4570
4571 IF (l_debug = 1)
4572 THEN
4573 INV_LOG_UTIL.trace
4574 ( 'After submit worker'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4575 , 9
4576 );
4577 INV_LOG_UTIL.trace
4578 ( 'Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4579 , 9
4580 );
4581 INV_LOG_UTIL.trace
4582 ( 'Batch Alloc(l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4583 , 9
4584 );
4585 END IF;
4586
4587 IF l_group_size > l_batch_size THEN
4588 l_group_size := l_group_size - l_batch_count;
4589 END IF;
4590
4591 IF (l_debug = 1)
4592 THEN
4593 INV_LOG_UTIL.trace
4594 ( 'Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4595 , 9
4596 );
4597 END IF;
4598
4599 --l_group_count :=0;
4600
4601 -- Reset Batch Variables
4602
4603 l_new_batch := 'Y';
4604 l_current_batch_id := generate_batch_id();
4605 l_batch_count := 0;
4606 l_remain_batch_count := l_batch_size;
4607 l_group_count :=0;
4608
4609 IF (l_debug = 1)
4610 THEN
4611 INV_LOG_UTIL.trace
4612 ( 'Batch Alloc(l_current_batch_id)'||l_current_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4613 , 9
4614 );
4615 INV_LOG_UTIL.trace
4616 ( 'Batch Alloc(l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4617 , 9
4618 );
4619 END IF;
4620
4621 END IF;
4622
4623 -- If the last record in MMTT is reached then stop
4624 EXIT WHEN l_current_cons_index = l_txn_source_tab.LAST;
4625
4626 l_current_cons_index := l_txn_source_tab.NEXT(l_current_cons_index);
4627
4628 l_batch_count := l_batch_count + 1;
4629
4630 IF (l_debug = 1)
4631 THEN
4632 INV_LOG_UTIL.trace
4633 ( 'Batch Alloc(l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4634 , 9
4635 );
4636 INV_LOG_UTIL.trace
4637 ( 'Batch Alloc(l_batch_count)'||l_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4638 , 9
4639 );
4640 END IF;
4641
4642 END LOOP;
4643
4644 -- Return control when all concurrent programs have completed
4645
4646 INV_CONSUMPTION_ADVICE_PROC.wait_for_all_workers
4647 ( p_workers => l_workers_tbl
4648 );
4649
4650 IF (l_debug = 1)
4651 THEN
4652 INV_LOG_UTIL.trace
4653 ( '<< Batch Allocation','INV_CONSUMPTION_ADVICE_PROC'
4654 , 9
4655 );
4656 END IF;
4657
4658
4659 EXCEPTION
4660
4661 WHEN OTHERS THEN
4662 IF (l_debug = 1) THEN
4663 INV_LOG_UTIL.trace
4664 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
4665 , 9
4666 );
4667 END IF;
4668
4669 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_BCH_ALL');
4670 FND_MSG_PUB.ADD;
4671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4672
4673 END batch_allocation;
4674
4675 --========================================================================
4676 -- PROCEDURE : Consumption_Txn_Manager PUBLIC
4677 -- COMMENT : This procedure will assign each unprocessed record in
4678 -- MTL_CONSUMPTION_TRANSACTIONS to a batch and then call the
4679 -- Consumption_Transaction_Worker for that batch. The manager
4680 -- will continue until all records
4681 -- in MTL_CONSUMPTION_TRANSACTIONS
4682 -- have been assigned to a batch.
4683 --=========================================================================
4684 PROCEDURE consumption_txn_manager
4685 ( p_batch_size IN NUMBER
4686 , p_max_workers IN NUMBER
4687 , p_vendor_id IN NUMBER
4688 , p_vendor_site_id IN NUMBER
4689 , p_inventory_item_id IN NUMBER
4690 , p_organization_id IN NUMBER
4691 )
4692 IS
4693
4694 -- Bug , - Creating REF cursors to associate with
4695 -- appropriate queries at run time and avoid NVL checks
4696 -- Start
4697 -- ======================
4698 -- Dynamic Cursor Variable
4699 -- =======================
4700 /* Bug 5092489. Commented as not used
4701 TYPE blanket_csr_type IS REF CURSOR;
4702 l_blanket_csr blanket_csr_type;
4703
4704 --Bug 4863365 - End
4705
4706
4707 --=================
4708 -- VARIABLES
4709 --=================
4710
4711 l_agreement_flag VARCHAR2(1);
4712 l_transaction_source_id NUMBER;
4713 l_current_cons_index BINARY_INTEGER := 0;
4714 l_current_index BINARY_INTEGER;
4715 l_empty_cons_tab g_cons_tbl_type;
4716 l_txn_source_tab g_cons_tbl_type;
4717 l_org_tab g_cons_tbl_type;
4718 l_blanket_query VARCHAR2(15000); -- Bug 4666585
4719 */
4720 l_count NUMBER;
4721 l_batch_id NUMBER := -1;
4722 l_debug NUMBER := NVL(FND_PROFILE.VALUE
4723 ('INV_DEBUG_TRACE'),0);
4724
4725
4726
4727 BEGIN
4728
4729 IF (l_debug = 1)
4730 THEN
4731 INV_LOG_UTIL.trace
4732 ( '>> Consumption Txn Manager','INV_CONSUMPTION_ADVICE_PROC'
4733 , 9
4734 );
4735 INV_LOG_UTIL.trace
4736 ( '>> p_batch_size: '|| p_batch_size
4737 , 9
4738 );
4739 INV_LOG_UTIL.trace
4740 ( '>> p_max_workers: '||p_max_workers
4741 , 9
4742 );
4743 INV_LOG_UTIL.trace
4744 ( '>> p_vendor_id: '||p_vendor_id
4745 , 9
4746 );
4747 INV_LOG_UTIL.trace
4748 ( '>> p_vendor_site_id: '||p_vendor_site_id
4749 , 9
4750 );
4751 INV_LOG_UTIL.trace
4752 ( '>> p_inventory_item_id: '||p_inventory_item_id
4753 , 9
4754 );
4755 INV_LOG_UTIL.trace
4756 ( '>> p_organization_id: '||p_organization_id
4757 , 9
4758 );
4759 END IF;
4760
4761 g_request_id := NULL;
4762 g_request_id :=
4763 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID') ) ;
4764
4765 IF (l_debug = 1)
4766 THEN
4767 INV_LOG_UTIL.trace
4768 ( '>> g_request_id => '|| g_request_id
4769 , 9
4770 );
4771 END IF;
4772
4773
4774 -- Mark all the records that are to be processed
4775
4776 /* Bug 4945892 - Start */
4777 /* Update modified to remove check for consumption_processed_flag */
4778 /* When consumption advice program is run , the manager populates
4779 the null batch_ids to -1. A parallely running consumption advice program
4780 will NOT pick up these 'marked' records. Prevents release duplication */
4781 -- Bug 5092489. Following query is commented in included with other query.
4782 --UPDATE
4783 -- MTL_CONSUMPTION_TRANSACTIONS
4784 --SET
4785 -- batch_id = l_batch_id
4786 --/* bug 5200436 - request_id stamped in batch_allocation */
4787 --, request_id = g_request_id
4788 --WHERE
4789 -- batch_id IS NULL
4790 -- AND NVL(net_qty,0) > 0
4791 -- -- Bug: 5092489. Following clause added for fast searching of MCT.
4792 -- AND consumption_processed_flag IN ('N', 'E') ;
4793
4794 /* Bug 4945892 - End */
4795
4796 -- Bug 5092489. Following query is modified to check (net_qty>0) and (mct.batch_id IS NULL)
4797 SELECT
4798 COUNT(*)
4799 INTO
4800 l_count
4801 FROM
4802 MTL_CONSUMPTION_TRANSACTIONS mct
4803 , MTL_MATERIAL_TRANSACTIONS mmt
4804 , po_vendor_sites_all pvsa
4805 WHERE mct.transaction_id = mmt.transaction_id
4806 AND mmt.owning_organization_id = pvsa.vendor_site_id
4807 AND pvsa.vendor_id = NVL(p_vendor_id, pvsa.vendor_id)
4808 AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL)
4809 And NVL(net_qty,0) > 0
4810 AND mmt.owning_organization_id =
4811 NVL(p_vendor_site_id, mmt.owning_organization_id)
4812 AND mmt.organization_id = NVL(p_organization_id, mmt.organization_id)
4813 AND mmt.inventory_item_id = NVL(p_inventory_item_id, mmt.inventory_item_id)
4814 AND consumption_processed_flag IN ('N', 'E')
4815 AND mmt.transaction_type_id = 74
4816 AND mmt.transaction_action_id = 6
4817 AND mmt.transaction_source_type_id = 1;
4818
4819 IF (l_debug = 1)
4820 THEN
4821 INV_LOG_UTIL.trace
4822 ( '<< CA Mgr (l_count):'||l_count,'INV_CONSUMPTION_ADVICE_PROC'
4823 , 9
4824 );
4825 INV_LOG_UTIL.trace
4826 ( '<< CA Mgr(batch_id):'||l_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4827 , 9
4828 );
4829 END IF;
4830
4831 IF l_count > 0 THEN
4832
4833 --Bug 5092489: New condition added
4834 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
4835 SET (mct.batch_id, mct.transaction_source_id, mct.inventory_item_id,
4836 mct.accrual_account_id, mct.organization_id,
4837 mct.owning_organization_id, mct.transaction_date)
4838 = (SELECT
4839 l_batch_id, mmt.transaction_source_id,
4840 mmt.inventory_item_id, mmt.distribution_account_id,
4841 mmt.organization_id, mmt.owning_organization_id,
4842 mmt.transaction_date
4843 FROM mtl_material_transactions mmt,po_vendor_sites_all pvsa
4844 WHERE mct.transaction_id = mmt.transaction_id
4845 AND mmt.owning_organization_id = pvsa.vendor_site_id
4846 AND (p_vendor_id IS NULL OR pvsa.vendor_id = p_vendor_id)
4847 AND (p_vendor_site_id IS NULL OR pvsa.vendor_site_id =
4848 p_vendor_site_id)
4849 AND (p_organization_id IS NULL OR mmt.organization_id = p_organization_id)
4850 AND (p_inventory_item_id IS NULL OR mmt.inventory_item_id = p_inventory_item_id)
4851 )
4852 WHERE mct.consumption_processed_flag IN ('N', 'E')
4853 AND NVL(net_qty,0) > 0
4854 AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL);
4855
4856
4857 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
4858 SET (mct.global_agreement_flag) =
4859 (SELECT NVL(global_agreement_flag,'N') FROM po_headers_all
4860 WHERE po_header_id = mct.transaction_source_id)
4861 WHERE mct.consumption_processed_flag IN ('N', 'E')
4862 AND mct.batch_id = l_batch_id;
4863 /* Bug: 5092489 Following code block is commented.
4864 -- CONDITIONAL CHECKS
4865 -- Four parameters p_vendor_id, p_vendor_site_id, p_organization_id,
4866 -- p_inventory_item_id each with two values ( NULL or NOT NULL)
4867 -- So, 16 conditional checks
4868
4869
4870 --Case 1
4871 IF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NOT NULL)
4872 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NOT NULL)
4873 THEN
4874
4875 l_blanket_query := 'SELECT DISTINCT
4876 mmt.transaction_source_id
4877 FROM
4878 mtl_consumption_transactions mct
4879 , mtl_material_transactions mmt
4880 , po_vendor_sites_all pvsa
4881 WHERE mct.transaction_id = mmt.transaction_id
4882 AND mmt.owning_organization_id = pvsa.vendor_site_id
4883 --AND mct.consumption_processed_flag <>''Y''
4884 --Bug 5113064
4885 AND mct.consumption_processed_flag IN (''N'', ''E'')
4886 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
4887 ' AND mmt.organization_id = '|| p_organization_id ||
4888 ' AND mmt.inventory_item_id = '|| p_inventory_item_id ;
4889
4890
4891 --Case 2
4892 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NOT NULL)
4893 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NULL)
4894 THEN
4895
4896 l_blanket_query := 'SELECT DISTINCT
4897 mmt.transaction_source_id
4898 FROM
4899 mtl_consumption_transactions mct
4900 , mtl_material_transactions mmt
4901 , po_vendor_sites_all pvsa
4902 WHERE mct.transaction_id = mmt.transaction_id
4903 AND mmt.owning_organization_id = pvsa.vendor_site_id
4904 --AND mct.consumption_processed_flag <> ''Y''
4905 --Bug 5113064
4906 AND mct.consumption_processed_flag IN (''N'', ''E'')
4907 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
4908 ' AND mmt.organization_id = '|| p_organization_id ;
4909
4910
4911 --Case 3
4912 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NOT NULL)
4913 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NOT NULL)
4914 THEN
4915
4916 l_blanket_query := 'SELECT DISTINCT
4917 mmt.transaction_source_id
4918 FROM
4919 mtl_consumption_transactions mct
4920 , mtl_material_transactions mmt
4921 , po_vendor_sites_all pvsa
4922 WHERE mct.transaction_id = mmt.transaction_id
4923 AND mmt.owning_organization_id = pvsa.vendor_site_id
4924 --AND mct.consumption_processed_flag <> ''Y''
4925 --Bug 5113064
4926 AND mct.consumption_processed_flag IN (''N'', ''E'')
4927 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
4928 ' AND mmt.inventory_item_id = '|| p_inventory_item_id;
4929
4930
4931 --Case 4
4932 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NOT NULL)
4933 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NULL)
4934 THEN
4935
4936 l_blanket_query := 'SELECT DISTINCT
4937 mmt.transaction_source_id
4938 FROM
4939 mtl_consumption_transactions mct
4940 , mtl_material_transactions mmt
4941 , po_vendor_sites_all pvsa
4942 WHERE mct.transaction_id = mmt.transaction_id
4943 AND mmt.owning_organization_id = pvsa.vendor_site_id
4944 --AND mct.consumption_processed_flag <> ''Y''
4945 --Bug 5113064
4946 AND mct.consumption_processed_flag IN (''N'', ''E'')
4947 AND mmt.owning_organization_id = '|| p_vendor_site_id;
4948
4949
4950 --Case 5
4951 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NULL)
4952 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NOT NULL)
4953 THEN
4954
4955 l_blanket_query := 'SELECT DISTINCT
4956 mmt.transaction_source_id
4957 FROM
4958 mtl_consumption_transactions mct
4959 , mtl_material_transactions mmt
4960 , po_vendor_sites_all pvsa
4961 WHERE mct.transaction_id = mmt.transaction_id
4962 AND mmt.owning_organization_id = pvsa.vendor_site_id
4963 --AND mct.consumption_processed_flag <> ''Y''
4964 --Bug 5113064
4965 AND mct.consumption_processed_flag IN (''N'', ''E'')
4966 AND mmt.organization_id = '|| p_organization_id ||
4967 ' AND mmt.inventory_item_id = '|| p_inventory_item_id;
4968
4969
4970 --Case 6
4971 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NULL)
4972 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NULL)
4973 THEN
4974
4975 l_blanket_query := 'SELECT DISTINCT
4976 mmt.transaction_source_id
4977 FROM
4978 mtl_consumption_transactions mct
4979 , mtl_material_transactions mmt
4980 , po_vendor_sites_all pvsa
4981 WHERE mct.transaction_id = mmt.transaction_id
4982 AND mmt.owning_organization_id = pvsa.vendor_site_id
4983 --AND mct.consumption_processed_flag <> ''Y''
4984 --Bug 5113064
4985 AND mct.consumption_processed_flag IN (''N'', ''E'')
4986 AND mmt.organization_id = '|| p_organization_id;
4987
4988
4989 --Case 7
4990 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NULL)
4991 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NOT NULL)
4992 THEN
4993
4994 l_blanket_query := 'SELECT DISTINCT
4995 mmt.transaction_source_id
4996 FROM
4997 mtl_consumption_transactions mct
4998 , mtl_material_transactions mmt
4999 , po_vendor_sites_all pvsa
5000 WHERE mct.transaction_id = mmt.transaction_id
5001 AND mmt.owning_organization_id = pvsa.vendor_site_id
5002 --AND mct.consumption_processed_flag <> ''Y''
5003 --Bug 5113064
5004 AND mct.consumption_processed_flag IN (''N'', ''E'')
5005 AND mmt.inventory_item_id = '|| p_inventory_item_id;
5006
5007
5008 --Case 8
5009 ELSIF (p_vendor_id IS NULL) AND (p_vendor_site_id IS NULL)
5010 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NULL)
5011 THEN
5012
5013 l_blanket_query := 'SELECT DISTINCT
5014 mmt.transaction_source_id
5015 FROM
5016 mtl_consumption_transactions mct
5017 , mtl_material_transactions mmt
5018 , po_vendor_sites_all pvsa
5019 WHERE mct.transaction_id = mmt.transaction_id
5020 AND mmt.owning_organization_id = pvsa.vendor_site_id
5021 --AND mct.consumption_processed_flag <> ''Y''
5022 --Bug 5113064
5023 AND mct.consumption_processed_flag IN (''N'', ''E'') ';
5024
5025
5026 --Case 9
5027 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NOT NULL)
5028 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NOT NULL)
5029 THEN
5030
5031 l_blanket_query := 'SELECT DISTINCT
5032 mmt.transaction_source_id
5033 FROM
5034 mtl_consumption_transactions mct
5035 , mtl_material_transactions mmt
5036 , po_vendor_sites_all pvsa
5037 WHERE mct.transaction_id = mmt.transaction_id
5038 AND mmt.owning_organization_id = pvsa.vendor_site_id
5039 AND pvsa.vendor_id = '|| p_vendor_id ||
5040 --' AND mct.consumption_processed_flag <> ''Y''
5041 --Bug 5113064
5042 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5043 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
5044 ' AND mmt.organization_id = '|| p_organization_id ||
5045 ' AND mmt.inventory_item_id = '|| p_inventory_item_id;
5046
5047
5048 --Case 10
5049 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NOT NULL)
5050 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NULL)
5051 THEN
5052
5053 l_blanket_query := 'SELECT DISTINCT
5054 mmt.transaction_source_id
5055 FROM
5056 mtl_consumption_transactions mct
5057 , mtl_material_transactions mmt
5058 , po_vendor_sites_all pvsa
5059 WHERE mct.transaction_id = mmt.transaction_id
5060 AND mmt.owning_organization_id = pvsa.vendor_site_id
5061 AND pvsa.vendor_id = ' || p_vendor_id ||
5062 --' AND mct.consumption_processed_flag <> ''Y''
5063 --Bug 5113064
5064 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5065 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
5066 ' AND mmt.organization_id = '|| p_organization_id;
5067
5068
5069 --Case 11
5070 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NOT NULL)
5071 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NOT NULL)
5072 THEN
5073
5074 l_blanket_query := 'SELECT DISTINCT
5075 mmt.transaction_source_id
5076 FROM
5077 mtl_consumption_transactions mct
5078 , mtl_material_transactions mmt
5079 , po_vendor_sites_all pvsa
5080 WHERE mct.transaction_id = mmt.transaction_id
5081 AND mmt.owning_organization_id = pvsa.vendor_site_id
5082 AND pvsa.vendor_id = '|| p_vendor_id ||
5083 --' AND mct.consumption_processed_flag <> ''Y''
5084 --Bug 5113064
5085 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5086 AND mmt.owning_organization_id = '|| p_vendor_site_id ||
5087 ' AND mmt.inventory_item_id = '|| p_inventory_item_id;
5088
5089
5090 --Case 12
5091 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NOT NULL)
5092 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NULL)
5093 THEN
5094
5095 l_blanket_query := 'SELECT DISTINCT
5096 mmt.transaction_source_id
5097 FROM
5098 mtl_consumption_transactions mct
5099 , mtl_material_transactions mmt
5100 , po_vendor_sites_all pvsa
5101 WHERE mct.transaction_id = mmt.transaction_id
5102 AND mmt.owning_organization_id = pvsa.vendor_site_id
5103 AND pvsa.vendor_id = '|| p_vendor_id ||
5104 --' AND mct.consumption_processed_flag <> ''Y''
5105 --Bug 5113064
5106 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5107 AND mmt.owning_organization_id = '|| p_vendor_site_id;
5108
5109
5110 --Case 13
5111 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NULL)
5112 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NOT NULL)
5113 THEN
5114
5115 l_blanket_query := 'SELECT DISTINCT
5116 mmt.transaction_source_id
5117 FROM
5118 mtl_consumption_transactions mct
5119 , mtl_material_transactions mmt
5120 , po_vendor_sites_all pvsa
5121 WHERE mct.transaction_id = mmt.transaction_id
5122 AND mmt.owning_organization_id = pvsa.vendor_site_id
5123 AND pvsa.vendor_id = '|| p_vendor_id ||
5124 --' AND mct.consumption_processed_flag <> ''Y''
5125 --Bug 5113064
5126 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5127 AND mmt.organization_id = '|| p_organization_id ||
5128 ' AND mmt.inventory_item_id = '|| p_inventory_item_id;
5129
5130
5131 --Case 14
5132 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NULL)
5133 AND (p_organization_id IS NOT NULL) AND (p_inventory_item_id IS NULL)
5134 THEN
5135
5136 l_blanket_query := 'SELECT DISTINCT
5137 mmt.transaction_source_id
5138 FROM
5139 mtl_consumption_transactions mct
5140 , mtl_material_transactions mmt
5141 , po_vendor_sites_all pvsa
5142 WHERE mct.transaction_id = mmt.transaction_id
5143 AND mmt.owning_organization_id = pvsa.vendor_site_id
5144 AND pvsa.vendor_id = '|| p_vendor_id ||
5145 --' AND mct.consumption_processed_flag <> ''Y''
5146 --Bug 5113064
5147 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5148 AND mmt.organization_id = '|| p_organization_id;
5149
5150
5151 --Case 15
5152 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NULL)
5153 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NOT NULL)
5154 THEN
5155
5156 l_blanket_query := 'SELECT DISTINCT
5157 mmt.transaction_source_id
5158 FROM
5159 mtl_consumption_transactions mct
5160 , mtl_material_transactions mmt
5161 , po_vendor_sites_all pvsa
5162 WHERE mct.transaction_id = mmt.transaction_id
5163 AND mmt.owning_organization_id = pvsa.vendor_site_id
5164 AND pvsa.vendor_id = '|| p_vendor_id ||
5165 --' AND mct.consumption_processed_flag <> ''Y''
5166 --Bug 5113064
5167 ' AND mct.consumption_processed_flag IN (''N'', ''E'')
5168 AND mmt.inventory_item_id = '|| p_inventory_item_id;
5169
5170
5171 --Case 16
5172 ELSIF (p_vendor_id IS NOT NULL) AND (p_vendor_site_id IS NULL)
5173 AND (p_organization_id IS NULL) AND (p_inventory_item_id IS NULL)
5174 THEN
5175
5176 l_blanket_query := 'SELECT DISTINCT
5177 mmt.transaction_source_id
5178 FROM
5179 mtl_consumption_transactions mct
5180 , mtl_material_transactions mmt
5181 , po_vendor_sites_all pvsa
5182 WHERE mct.transaction_id = mmt.transaction_id
5183 AND mmt.owning_organization_id = pvsa.vendor_site_id
5184 AND pvsa.vendor_id = '|| p_vendor_id ||
5185 --' AND mct.consumption_processed_flag <> ''Y'' ';
5186 --Bug 5113064
5187 ' AND mct.consumption_processed_flag IN (''N'', ''E'') ';
5188
5189 END IF;
5190
5191
5192 IF NOT l_blanket_csr%ISOPEN
5193 THEN
5194 OPEN l_blanket_csr FOR l_blanket_query;
5195 END IF;
5196
5197 -- clear the pl/sql table before use
5198
5199 l_txn_source_tab := l_empty_cons_tab;
5200
5201 FETCH l_blanket_csr
5202 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1);
5203
5204 WHILE l_blanket_csr%FOUND
5205 LOOP
5206 FETCH l_blanket_csr
5207 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1);
5208 END LOOP;
5209
5210 CLOSE l_blanket_csr;
5211
5212 -- Bug 4863365 - End
5213
5214
5215
5216 l_current_cons_index := l_txn_source_tab.FIRST;
5217
5218 LOOP
5219
5220 l_transaction_source_id := l_txn_source_tab(l_current_cons_index);
5221
5222 IF (l_debug = 1)
5223 THEN
5224 INV_LOG_UTIL.trace
5225 ( '<< CA Mgr(l_transaction_source_id)'||l_transaction_source_id,'INV_CONSUMPTION_ADVICE_PROC'
5226 , 9
5227 );
5228 END IF;
5229
5230 INV_PO_THIRD_PARTY_STOCK_MDTR.indicate_global
5231 (p_transaction_source_id => l_transaction_source_id
5232 ,x_global_agreement_flag => l_agreement_flag);
5233
5234 -- Bug: 5092489, statement is modified to include three new columns,
5235 -- mct.transaction_source_id and mct.inventory_item_id, mct.accrual_account_id
5236 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
5237 SET mct.global_agreement_flag = l_agreement_flag
5238 WHERE mct.transaction_source_id = l_transaction_source_id
5239 AND mct.consumption_processed_flag IN ('N', 'E') ;
5240
5241 EXIT WHEN l_current_cons_index = l_txn_source_tab.LAST;
5242
5243 l_current_cons_index := l_txn_source_tab.NEXT(l_current_cons_index);
5244
5245 IF (l_debug = 1)
5246 THEN
5247 INV_LOG_UTIL.trace
5248 ( '<< CA Mgr(l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
5249 , 9
5250 );
5251 END IF;
5252
5253 END LOOP;
5254
5255
5256 -- Call procedure to load MTL_CONSUMPTION_TXNS_TEMP
5257 -- with unprocessed data from MTL_CONSUMPTION_TRANSACTIONS
5258
5259 IF (l_debug = 1)
5260 THEN
5261 INV_LOG_UTIL.trace
5262 ( 'Before load_comb(batch_id):'||l_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
5263 , 9
5264 );
5265 INV_LOG_UTIL.trace
5266 ( 'Before load_comb(vendor_id):'||p_vendor_id,'INV_CONSUMPTION_ADVICE_PROC'
5267 , 9
5268 );
5269 INV_LOG_UTIL.trace
5270 ( 'Before load_comb(vendor_site_id):'||p_vendor_site_id,'INV_CONSUMPTION_ADVICE_PROC'
5271 , 9
5272 );
5273 INV_LOG_UTIL.trace
5274 ( 'Before load_comb(item_id):'||p_inventory_item_id,'INV_CONSUMPTION_ADVICE_PROC'
5275 , 9
5276 );
5277 INV_LOG_UTIL.trace
5278 ( 'Before load_comb(orgn):'||p_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
5279 , 9
5280 );
5281 END IF;
5282 */
5283 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
5284 THEN
5285 INV_CONSUMPTION_ADVICE_PROC.load_combination_prf
5286 (p_batch_id => l_batch_id
5287 ,p_vendor_id => p_vendor_id
5288 ,p_vendor_site_id => p_vendor_site_id
5289 ,p_inventory_item_id => p_inventory_item_id
5290 ,p_organization_id => p_organization_id);
5291 ELSE
5292 INV_CONSUMPTION_ADVICE_PROC.load_combination
5293 (p_batch_id => l_batch_id
5294 ,p_vendor_id => p_vendor_id
5295 ,p_vendor_site_id => p_vendor_site_id
5296 ,p_inventory_item_id => p_inventory_item_id
5297 ,p_organization_id => p_organization_id);
5298
5299 END IF;
5300
5301 -- Call procedure to assign a batch_id to unprocessed data
5302 -- in MTL_CONSUMPTION_TRANSACTIONS and call the worker to
5303 -- process a batch
5304
5305 IF (l_debug = 1)
5306 THEN
5307 INV_LOG_UTIL.trace
5308 ( 'Calling Batch Allocation(size) '||p_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
5309 , 9
5310 );
5311 INV_LOG_UTIL.trace
5312 ( 'Workers '||p_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
5313 , 9
5314 );
5315 END IF;
5316
5317 INV_CONSUMPTION_ADVICE_PROC.batch_allocation
5318 (p_batch_size => p_batch_size
5319 ,p_max_workers => p_max_workers);
5320
5321 IF (l_debug = 1)
5322 THEN
5323 INV_LOG_UTIL.trace
5324 ( 'Calling update_po_distrubution_id '
5325 , 9
5326 );
5327 END IF;
5328
5329 update_po_distrubution_id ;
5330 END IF;
5331
5332
5333 IF (l_debug = 1)
5334 THEN
5335 INV_LOG_UTIL.trace
5336 ( '<< Consumption Txn Manager','INV_CONSUMPTION_ADVICE_PROC'
5337 , 9
5338 );
5339 END IF;
5340
5341 EXCEPTION
5342
5343 WHEN OTHERS THEN
5344 IF (l_debug = 1) THEN
5345 INV_LOG_UTIL.trace
5346 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
5347 , 9
5348 );
5349 END IF;
5350
5351 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_DEL_REC');
5352 FND_MSG_PUB.ADD;
5353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5354
5355 END consumption_txn_manager;
5356
5357 END INV_CONSUMPTION_ADVICE_PROC;