[Home] [Help]
PACKAGE BODY: APPS.INV_CONSUMPTION_ADVICE_PROC
Source
1 PACKAGE BODY INV_CONSUMPTION_ADVICE_PROC AS
2 -- $Header: INVRCADB.pls 120.29.12020000.5 2013/01/23 06:31:00 ksaripal 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
167 INV_LOG_UTIL.trace
164 ( 'Consumption_Release_Id: '||l_consumption_release_id
165 , 9
166 );
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 , mct.error_explanation = l_error_explanation -- Bug 12679286
490 WHERE mct.batch_id = p_batch_id
491 AND mct.charge_account_id = p_charge_account_id
492 AND mct.variance_account_id = p_variance_account_id
493 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
494 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
495 AND mct.consumption_processed_flag IN ('N', 'E')
496 AND mct.transaction_source_id = p_transaction_source_id
497 AND mct.accrual_account_id = p_accrual_account_id
498 AND mct.owning_organization_id = p_vendor_site_id
499 AND ((NVL(mct.global_agreement_flag,'N') = 'Y'
500 AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date))
501 OR (NVL(mct.global_agreement_flag,'N') = 'N'));
502
503 -- Deleted unused commented code as part of bug 11900144
504 ELSE
505 -- Bug 5092489, Query modified to eliminate use of MMT in subquery
506 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
507 SET mct.consumption_po_header_id = p_consumption_po_header_id
508 , mct.consumption_release_id = p_consumption_release_id
509 , mct.consumption_processed_flag = l_consumption_processed_flag
510 , mct.error_code = p_error_code
511 , mct.error_explanation = l_error_explanation
512 WHERE mct.batch_id = p_batch_id
513 AND mct.charge_account_id = p_charge_account_id
514 AND mct.variance_account_id = p_variance_account_id
515 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
516 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
517 AND mct.consumption_processed_flag IN ('N', 'E')
518 AND mct.transaction_source_id = p_transaction_source_id
519 AND mct.accrual_account_id = p_accrual_account_id
520 AND mct.owning_organization_id = p_vendor_site_id
521 AND TRUNC(mct.transaction_date) = TRUNC(p_transaction_date);
522
523 /*
524 AND transaction_id IN
525 (SELECT transaction_id
526 FROM MTL_MATERIAL_TRANSACTIONS
527 WHERE transaction_source_id = p_transaction_source_id
528 AND distribution_account_id = p_accrual_account_id
529 AND owning_organization_id = p_vendor_site_id
530 AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
531 AND inventory_item_id IN
532 (SELECT inventory_item_id
533 FROM MTL_CONSUMPTION_TXN_TEMP
534 WHERE transaction_source_id = p_transaction_source_id));
535 */
536 END IF;
537
538 UPDATE MTL_CONSUMPTION_TRANSACTIONS
539 SET consumption_po_header_id = p_consumption_po_header_id
540 , consumption_release_id = p_consumption_release_id
541 , consumption_processed_flag = l_consumption_processed_flag
542 , error_code = p_error_code
543 , error_explanation = l_error_explanation
544 WHERE parent_transaction_id IN
545 (SELECT mct.transaction_id
546 FROM MTL_CONSUMPTION_TRANSACTIONS mct
547 WHERE mct.batch_id = p_batch_id
548 AND mct.charge_account_id = p_charge_account_id
549 AND mct.variance_account_id = p_variance_account_id
550 AND NVL(mct.rate_type,'##') = NVL(p_global_rate_type,'##')
551 AND NVL(mct.rate,-1) = NVL(p_global_rate,-1)
552 AND mct.transaction_source_id = p_transaction_source_id
553 AND mct.accrual_account_id = p_accrual_account_id
554 AND mct.owning_organization_id = p_vendor_site_id
555 )
556 AND consumption_processed_flag IN ('N', 'E');
557
558 IF (l_debug = 1)
559 THEN
560 INV_LOG_UTIL.trace
561 ( '<< Update Consumption','INV_CONSUMPTION_ADVICE_PROC'
562 , 9
563 );
564 END IF;
565
566 END Update_Consumption;
567
568
569 --========================================================================
570 -- PROCEDURE : Load_Combination PRIVATE
571 -- COMMENT : This procedure will load all the records of a context batch
572 -- from MTL_CONSUMPTION_TRANSACTIONS to
573 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
574 -- If the batch_id passed is -1 then the call is from the
575 -- manager in which case all records with a
576 -- processed_consumption_flag are loaded.
577 -- The insert statement will also be selective by the
578 -- input parameters p_txn_s_id, p_item_id and p_org_id
579 --=========================================================================
580 PROCEDURE load_combination
581 ( p_batch_id IN NUMBER
582 , p_vendor_id IN NUMBER
583 , p_vendor_site_id IN NUMBER
584 , p_inventory_item_id IN NUMBER
585 , p_organization_id IN NUMBER
586 )
587 IS
588 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
589 l_count NUMBER;
590 BEGIN
591
592 IF (l_debug = 1)
593 THEN
594 INV_LOG_UTIL.trace
595 ( '>> Load Combination(p_batch_id):'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
596 , 9
597 );
598 END IF;
599
600 -- Insert records to the temp table that do belong to
601 -- a global agreement
602
603 -- Insert records to the temp table
604 -- Group the records by use of the distinct clause
605 -- Bug 5092489. Query modified to eliminate use of MMT and DISTINCT clause
606 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
607 ( mctt.transaction_source_id
608 , mctt.inventory_item_id
609 , mctt.organization_id
610 , mctt.owning_organization_id
611 /* Bug 4969421 Starts here*/
612 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
613 , mctt.transaction_cost
614 /* Bug 4969421 Ends here*/
615 , mctt.batch_id
616 , mctt.tax_code_id
617 , mctt.tax_rate
618 , mctt.recoverable_tax
619 , mctt.non_recoverable_tax
620 , mctt.tax_recovery_rate
621 , mctt.accrual_account_id
622 , mctt.charge_account_id
623 , mctt.variance_account_id
624 , mctt.rate_type
625 , mctt.rate
626 , mctt.transaction_date
627 , mctt.global_agreement_flag
628 , mctt.net_qty
629 /* Bug 11900144. Addition of po_line_id */
630 , mctt.po_line_id
631 )
632 SELECT
633 mct.transaction_source_id
634 , mct.inventory_item_id
635 , mct.organization_id
636 , mct.owning_organization_id
637 , mct.blanket_price
638 /* Bug 4969421 Ends here*/
639 , p_batch_id
640 , NVL(mct.tax_code_id,-1)
641 , NVL(mct.tax_rate,-1)
642 , NVL(mct.recoverable_tax,0)
643 , NVL(mct.non_recoverable_tax,0)
644 , NVL(mct.tax_recovery_rate,0)
645 , mct.accrual_account_id
646 , mct.charge_account_id
647 , mct.variance_account_id
648 , NVL(mct.rate_type,'##')
649 , NVL(mct.rate,-1)
650 , TRUNC(mct.transaction_date)
651 , mct.global_agreement_flag
652 , SUM(mct.net_qty)
653 /* Bug 11900144. Addition of po_line_id */
654 , mct.po_line_id
655 FROM
656 MTL_CONSUMPTION_TRANSACTIONS mct
657 , po_vendor_sites_all pvsa
658 WHERE mct.owning_organization_id = pvsa.vendor_site_id
659 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
660 AND mct.owning_organization_id =
661 NVL(p_vendor_site_id,mct.owning_organization_id)
662 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
663 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
664 AND mct.consumption_processed_flag IN ('N', 'E')
665 AND mct.batch_id = p_batch_id
666 GROUP BY
667 mct.transaction_source_id
668 , mct.inventory_item_id
669 , mct.organization_id
670 , mct.owning_organization_id
671 , mct.blanket_price
672 /* Bug 11900144. Addition of po_line_id */
673 , mct.po_line_id
674 , mct.tax_code_id
675 , mct.tax_rate
676 , mct.recoverable_tax
677 , mct.non_recoverable_tax
678 , mct.tax_recovery_rate
679 , mct.accrual_account_id
680 , mct.charge_account_id
681 , mct.variance_account_id
685 , TRUNC(mct.transaction_date)
682 , mct.rate_type
683 , mct.rate
684 , mct.global_agreement_flag
686 ;
687
688 -- Deleted unused commented code as part of bug 11900144
689
690 select count(*) into l_count
691 from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
692
693 IF (l_debug = 1)
694 THEN
695 INV_LOG_UTIL.trace
696 ( 'temp table count: ' || l_count ,'INV_CONSUMPTION_ADVICE_PROC'
697 , 9
698 );
699
700 INV_LOG_UTIL.trace
701 ( '<< Load Combination','INV_CONSUMPTION_ADVICE_PROC'
702 , 9
703 );
704 END IF;
705 EXCEPTION
706
707 WHEN OTHERS THEN
708 IF (l_debug = 1) THEN
709 INV_LOG_UTIL.trace
710 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
711 , 9
712 );
713 END IF;
714
715 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_COM');
716 FND_MSG_PUB.ADD;
717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
718
719 END load_combination;
720
721
722 --========================================================================
723 -- PROCEDURE : Load_Combination_prf PRIVATE
724 -- COMMENT : This procedure will load all the records of a context batch
725 -- from MTL_CONSUMPTION_TRANSACTIONS to
726 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
727 -- If the batch_id passed is -1 then the call is from the
728 -- manager in which case all records with a
729 -- processed_consumption_flag are loaded.
730 -- The insert statement will also be selective by the
731 -- input parameters p_txn_s_id, p_item_id and p_org_id
732 --=========================================================================
733 PROCEDURE load_combination_prf
734 ( p_batch_id IN NUMBER
735 , p_vendor_id IN NUMBER
736 , p_vendor_site_id IN NUMBER
737 , p_inventory_item_id IN NUMBER
738 , p_organization_id IN NUMBER
739 )
740 IS
741 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
742 l_count NUMBER;
743 BEGIN
744
745 IF (l_debug = 1)
746 THEN
747 INV_LOG_UTIL.trace
748 ( '>> Load Combination_prf(p_batch_id)'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
749 , 9
750 );
751 END IF;
752
753 --Bug 5092489. Modified insert statement to eliminate use of MMT and DISTINCT clause
754 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
755 ( mctt.transaction_source_id
756 , mctt.inventory_item_id
757 , mctt.organization_id
758 , mctt.owning_organization_id
759 /* Bug 4969421 Starts here*/
760 /* We pass the blanket_price (from MCT) instead of the transaction_cost from MMT */
761 , mctt.transaction_cost
762 /* Bug 4969421 Ends here*/
763 , mctt.batch_id
764 , mctt.tax_code_id
765 , mctt.tax_rate
766 , mctt.recoverable_tax
767 , mctt.non_recoverable_tax
768 , mctt.tax_recovery_rate
769 , mctt.accrual_account_id
770 , mctt.charge_account_id
771 , mctt.variance_account_id
772 , mctt.rate_type
773 , mctt.rate
774 , mctt.transaction_date
775 , mctt.global_agreement_flag
776 , mctt.net_qty
777 /* Bug 11900144. Addition of po_line_id */
778 , mctt.po_line_id
779 )
780 SELECT
781 mct.transaction_source_id
782 , mct.inventory_item_id
783 , mct.organization_id
784 , mct.owning_organization_id
785 , mct.blanket_price
786 , p_batch_id
787 , NVL(mct.tax_code_id,-1)
788 , NVL(mct.tax_rate,-1)
789 , NVL(mct.recoverable_tax,0)
790 , NVL(mct.non_recoverable_tax,0)
791 , NVL(mct.tax_recovery_rate,0)
792 , mct.accrual_account_id
793 , mct.charge_account_id
794 , mct.variance_account_id
795 , NVL(mct.rate_type,'##')
796 , NVL(mct.rate,-1)
797 /* Start Bug 6388514 Splitting the deode function into two different INSERT into MCT */
798 --, DECODE(mct.global_agreement_flag, 'Y', TRUNC(mct.transaction_date),'N', TRUNC(MAX(mct.transaction_date)))
799 , TRUNC(mct.transaction_date)
800 /* End Bug 6388514 */
801 , mct.global_agreement_flag
802 , SUM(mct.net_qty)
803 /* Bug 11900144. Addition of po_line_id */
804 , mct.po_line_id
805 FROM
806 MTL_CONSUMPTION_TRANSACTIONS mct
807 , po_vendor_sites_all pvsa
808 WHERE mct.owning_organization_id = pvsa.vendor_site_id
809 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
810 AND mct.owning_organization_id =
811 NVL(p_vendor_site_id,mct.owning_organization_id)
812 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
813 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
814 AND mct.consumption_processed_flag IN ('N','E')
815 AND mct.batch_id = p_batch_id
816 /* Start Bug 6388514 */
817 AND mct.global_agreement_flag = 'Y'
818 /* End Bug 6388514 */
819 GROUP BY
820 mct.transaction_source_id
821 , mct.inventory_item_id
822 , mct.organization_id
823 , mct.owning_organization_id
824 , mct.blanket_price
825 /* Bug 11900144. Addition of po_line_id */
826 , mct.po_line_id
827 , mct.tax_code_id
828 , mct.tax_rate
829 , mct.recoverable_tax
830 , mct.non_recoverable_tax
831 , mct.tax_recovery_rate
832 , mct.accrual_account_id
833 , mct.charge_account_id
834 , mct.variance_account_id
835 , mct.rate_type
836 , mct.rate
837 , mct.global_agreement_flag
838 , TRUNC(mct.transaction_date)
839 ;
840
841 -- Deleted unused commented code as part of bug 11900144
842
843 /* Start Bug 6388514 INSERT into MCT for global_agreement_flag = 'N' */
844 INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
845 ( mctt.transaction_source_id
846 , mctt.inventory_item_id
847 , mctt.organization_id
848 , mctt.owning_organization_id
849 , mctt.transaction_cost
850 , mctt.batch_id
851 , mctt.tax_code_id
852 , mctt.tax_rate
853 , mctt.recoverable_tax
854 , mctt.non_recoverable_tax
855 , mctt.tax_recovery_rate
856 , mctt.accrual_account_id
857 , mctt.charge_account_id
858 , mctt.variance_account_id
859 , mctt.rate_type
860 , mctt.rate
861 , mctt.global_agreement_flag
862 , mctt.net_qty
863 /* Bug 11900144. Addition of po_line_id */
864 , mctt.po_line_id
865 )
866 SELECT
867 mct.transaction_source_id
868 , mct.inventory_item_id
869 , mct.organization_id
870 , mct.owning_organization_id
871 , mct.blanket_price
872 , p_batch_id
873 , NVL(mct.tax_code_id,-1)
874 , NVL(mct.tax_rate,-1)
875 , NVL(mct.recoverable_tax,0)
876 , NVL(mct.non_recoverable_tax,0)
877 , NVL(mct.tax_recovery_rate,0)
878 , mct.accrual_account_id
879 , mct.charge_account_id
880 , mct.variance_account_id
881 , NVL(mct.rate_type,'##')
882 , NVL(mct.rate,-1)
883 , mct.global_agreement_flag
884 , SUM(mct.net_qty)
885 /* Bug 11900144. Addition of po_line_id */
886 , mct.po_line_id
887 FROM
888 MTL_CONSUMPTION_TRANSACTIONS mct
889 , po_vendor_sites_all pvsa
890 WHERE mct.owning_organization_id = pvsa.vendor_site_id
891 AND pvsa.vendor_id = NVL(p_vendor_id,pvsa.vendor_id)
892 AND mct.owning_organization_id =
893 NVL(p_vendor_site_id,mct.owning_organization_id)
894 AND mct.inventory_item_id = NVL(p_inventory_item_id,mct.inventory_item_id)
895 AND mct.organization_id = NVL(p_organization_id,mct.organization_id)
896 AND mct.consumption_processed_flag IN ('N','E')
897 AND mct.batch_id = p_batch_id
898 AND mct.global_agreement_flag = 'N'
899 GROUP BY
900 mct.transaction_source_id
901 , mct.inventory_item_id
902 , mct.organization_id
903 , mct.owning_organization_id
904 , mct.blanket_price
905 /* Bug 11900144. Addition of po_line_id */
906 , mct.po_line_id
907 , mct.tax_code_id
908 , mct.tax_rate
909 , mct.recoverable_tax
910 , mct.non_recoverable_tax
911 , mct.tax_recovery_rate
912 , mct.accrual_account_id
913 , mct.charge_account_id
914 , mct.variance_account_id
915 , mct.rate_type
916 , mct.rate
917 , mct.global_agreement_flag
918 ;
919 /* End Bug 6388514 */
920
921 /* Start bug 6388514 Update transaction_date in MCTT */
922 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
923 SET mctt.transaction_date =
924 (SELECT
925 TRUNC(MAX(mct.transaction_date))
926 FROM
927 --MTL_MATERIAL_TRANSACTIONS mmt
928 MTL_CONSUMPTION_TRANSACTIONS mct
929 WHERE --mct.transaction_id = mmt.transaction_id AND
930 mct.transaction_source_id = mctt.transaction_source_id
931 AND mct.inventory_item_id = mctt.inventory_item_id
932 AND mct.organization_id = mctt.organization_id
933 /* Bug 4969420 Starts here*/
934 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
935 --AND mmt.transaction_cost = mctt.transaction_cost
936 AND mct.blanket_price = mctt.transaction_cost
937 /* Bug 4969420 Ends here*/
938 /* Bug 11900144. Addition of po_line_id clause */
939 AND mct.po_line_id=mctt.po_line_id
940 AND NVL(mct.tax_code_id,-1) = NVL(mctt.tax_code_id,-1)
941 AND NVL(mct.recoverable_tax,0) = NVL(mctt.recoverable_tax,0)
942 AND NVL(mct.non_recoverable_tax,0) = NVL(mctt.non_recoverable_tax,0)
943 AND NVL(mct.tax_recovery_rate,0) = NVL(mctt.tax_recovery_rate,0)
944 AND NVL(mct.rate,-1) = NVL(mctt.rate,-1)
945 AND mct.accrual_account_id = mctt.accrual_account_id
946 AND mct.charge_account_id = mctt.charge_account_id
947 AND mct.variance_account_id = mctt.variance_account_id
948 AND mct.global_agreement_flag = 'N'
949 AND mct.consumption_processed_flag IN ('N', 'E'))
950 WHERE mctt.transaction_date IS NULL;
951 /* End bug 6388514 */
952
953 select count(*) into l_count
954 from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
955
956 IF (l_debug = 1)
957 THEN
958 INV_LOG_UTIL.trace
959 ( 'temp table count: ' || l_count ,'INV_CONSUMPTION_ADVICE_PROC'
960 , 9
961 );
962
963 INV_LOG_UTIL.trace
964 ( '<< Load Combination_prf','INV_CONSUMPTION_ADVICE_PROC'
965 , 9
966 );
967 END IF;
968
969 EXCEPTION
970
971 WHEN OTHERS THEN
972 IF (l_debug = 1) THEN
973 INV_LOG_UTIL.trace
974 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
975 , 9
976 );
977 END IF;
978
979 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_COM');
980 FND_MSG_PUB.ADD;
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982
983 END load_combination_prf;
984
985
986
987
988 --========================================================================
989 -- PROCEDURE : Validate_Blanket PRIVATE
990 -- PARAMETERS : p_batch_id Batch Id
991 -- COMMENT : Populates the valid_flag if the blanket is valid
992 --========================================================================
993 PROCEDURE validate_blanket
994 ( p_batch_id NUMBER)
995 IS
996
997 --=================
998 -- CURSORS
999 --=================
1000
1001 CURSOR valid_csr_type IS
1002 SELECT DISTINCT
1003 transaction_source_id
1004 , inventory_item_id
1005 , owning_organization_id
1006 FROM
1007 mtl_consumption_txn_temp
1008 WHERE batch_id=p_batch_id;
1009
1010 --=================
1011 -- VARIABLES
1012 --=================
1013
1014 l_count NUMBER;
1015 l_header_id NUMBER;
1016 l_item_id NUMBER;
1017 l_owning_organization_id NUMBER;
1018 l_org_id NUMBER;
1019 l_valid_flag VARCHAR2(1);
1020 l_debug NUMBER :=
1021 NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1022 l_error_msg VARCHAR2(100) := 'INV_SUP_CONS_NO_BPO_EXISTS';
1023 l_error_explanation VARCHAR2(240) := NULL;
1024 l_blanket_po_number VARCHAR2(20) := NULL;
1025
1026
1027 BEGIN
1028
1029 IF (l_debug = 1)
1030 THEN
1031 INV_LOG_UTIL.trace
1032 ( '>> Validate Blanket','INV_CONSUMPTION_ADVICE_PROC'
1033 , 9
1034 );
1035 END IF;
1036
1037 OPEN valid_csr_type;
1038 LOOP
1039 FETCH valid_csr_type
1040 INTO l_header_id
1041 ,l_item_id
1042 ,l_owning_organization_id;
1043
1044 IF valid_csr_type%NOTFOUND
1045 THEN
1046 EXIT;
1047 END IF;
1048
1049 -- Check to see if the blanket is still valid
1050
1051 SELECT
1052 COUNT(1)
1053 INTO
1054 l_count
1055 FROM
1056 po_headers_all poh
1057 , po_lines_all pol
1058 WHERE poh.po_header_id = pol.po_header_id
1059 AND (TRUNC(NVL(poh.start_date,SYSDATE -1)) <= TRUNC(SYSDATE))
1060 AND (TRUNC(NVL(poh.end_date+NVL(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0),SYSDATE +1)) >= TRUNC(SYSDATE)) -- Bug 8397146
1061 AND (TRUNC(NVL(pol.expiration_date,SYSDATE )) >= TRUNC(SYSDATE))
1062 AND poh.approved_flag = 'Y'
1063 AND NVL(poh.frozen_flag, 'N') = 'N'
1064 AND (NVL(poh.cancel_flag,'N') = 'N'
1065 OR NVL(pol.cancel_flag,'N') = 'N')
1066 AND NVL(pol.closed_code,'OPEN') = 'OPEN'
1067 AND poh.po_header_id = l_header_id
1068 AND pol.item_id = l_item_id;
1069
1070 l_org_id :=
1071 INV_THIRD_PARTY_STOCK_UTIL.get_org_id(l_owning_organization_id);
1072
1073 IF l_count > 0
1074 THEN
1075
1076 -- If it is a Global agreement, mark as valid_flag = 'G'
1077 IF INV_PO_THIRD_PARTY_STOCK_MDTR.is_global(l_header_id)
1078 THEN
1079 l_valid_flag := 'G';
1080 ELSE
1081 l_valid_flag := 'Y';
1082 END IF;
1083
1084 /* Bug 13843698. Added owning org id in the below where clause */
1085 UPDATE mtl_consumption_txn_temp
1086 SET valid_flag = l_valid_flag
1087 ,org_id = l_org_id
1088 WHERE transaction_source_id = l_header_id
1089 AND inventory_item_id = l_item_id
1090 AND owning_organization_id = l_owning_organization_id
1091 AND valid_flag IS NULL;
1092 INV_LOG_UTIL.trace
1093 ( 'VALID line : item - '
1094 ||l_item_id || ', source - '|| l_header_id || ', org - '|| l_org_id,
1095 'INV_CONSUMPTION_ADVICE_PROC'
1096 , 9
1097 );
1098
1099 ELSE
1100
1101 /* Bug 13843698. Added owning org id in the below where clause */
1102 UPDATE mtl_consumption_txn_temp
1103 SET valid_flag = 'N'
1104 ,org_id = l_org_id
1105 WHERE transaction_source_id = l_header_id
1106 AND inventory_item_id = l_item_id
1107 AND owning_organization_id = l_owning_organization_id
1108 AND valid_flag IS NULL;
1109
1110 /* bug 5113064 - Start */
1111 /* delete all records in MCT_TEMP that have valid flag as 'N' */
1112 /* Also update MCT records with error status and message */
1113 /*Bug 5092489. Query is modified to eliminate use of MMT. */
1114 UPDATE mtl_consumption_transactions SET
1115 consumption_processed_flag = 'E'
1116 , error_code = l_error_msg
1117 WHERE transaction_source_id = l_header_id
1118 AND inventory_item_id = l_item_id
1119 AND owning_organization_id = l_owning_organization_id
1120 AND consumption_processed_flag IN ('N','E')
1121 AND batch_id = p_batch_id;
1122 /*WHERE transaction_id IN
1123 (SELECT transaction_id FROM mtl_material_transactions
1124 WHERE transaction_source_id = l_header_id
1125 AND inventory_item_id = l_item_id
1126 AND owning_organization_id = l_owning_organization_id)
1127 AND consumption_processed_flag IN ('N','E')
1128 AND batch_id = p_batch_id;
1129 */
1130
1131 DELETE FROM mtl_consumption_txn_temp
1132 WHERE valid_flag = 'N'
1133 AND org_id = l_org_id
1134 AND transaction_source_id = l_header_id
1135 AND inventory_item_id = l_item_id;
1136 /* bug 5113064 - End */
1137 INV_LOG_UTIL.trace
1138 ( '**** INVALID line : item - '
1139 ||l_item_id || ', source - '|| l_header_id || ', org - '|| l_org_id,
1140 'INV_CONSUMPTION_ADVICE_PROC'
1141 , 9
1142 );
1143
1144 END IF;
1145
1146 END LOOP;
1147
1148 CLOSE valid_csr_type;
1149
1150 IF (l_debug = 1)
1151 THEN
1152 INV_LOG_UTIL.trace
1153 ( '<< Validate Blanket','INV_CONSUMPTION_ADVICE_PROC'
1154 , 9
1155 );
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 IF valid_csr_type% ISOPEN
1161 THEN
1162 CLOSE valid_csr_type;
1163 END IF;
1164 END validate_blanket;
1165
1166 --========================================================================
1167 -- PROCEDURE : Load_Interface_Tables PRIVATE
1168 -- PARAMETERS: p_transaction_source_id Material transaction id
1169 -- p_batch_id Batch id
1170 -- COMMENT : This procedure is called from the worker. It loads
1171 -- : unprocessed summarized change of ownership transactions
1172 -- : from the consumption temp table MTL_CONSUMPTION_TXN_TEMP
1173 -- : into the PO interface tables in preperation for the
1174 -- : creation of either a consumption advice or standard PO
1175 -- : Once this is done the autocreate procedure is called
1176 -- : to process the interface tables and create the release.
1177 -- : The corresponding records are updated with the result,
1178 -- : whether success or failure. If success the last billing date
1179 -- : of the associated asl_id is also updated.
1180 -- CHANGE : Added secondary quantity in the interface table insert.
1181 --========================================================================
1182
1183 PROCEDURE load_interface_tables
1184 ( p_batch_id IN NUMBER
1185 , x_return_status OUT NOCOPY VARCHAR2
1186 )
1187
1188 IS
1189
1190 --=================
1191 -- VARIABLES
1192 --=================
1193
1194 l_header_id NUMBER;
1195 l_transaction_source_id NUMBER;
1196 l_interface_header_id NUMBER;
1197 l_interface_line_id NUMBER;
1198 l_item_id NUMBER;
1199 l_organization_id NUMBER;
1200 l_quantity NUMBER;
1201 l_po_price NUMBER;
1202 l_date DATE;
1203 l_vendor_site_id NUMBER;
1204 l_blanket_id NUMBER;
1205 l_lines NUMBER;
1206 l_org_id NUMBER;
1207 l_user NUMBER;
1208 l_document_type_code VARCHAR2(30);
1209 -- Bug 5092489. Commented because not used.
1210 --l_document_subtype VARCHAR2(30);
1211 l_location_id NUMBER;
1212 -- Bug 5092489. Commented because not used.
1213 --l_ship_to_location NUMBER;
1214 l_bill_to_location NUMBER;
1215
1216 l_vendor_ship_to_location NUMBER;
1217 l_vendor_bill_to_location NUMBER;
1218 l_recoverable_tax NUMBER;
1219 l_nonrecoverable_tax NUMBER;
1220 l_recovery_rate NUMBER;
1221 l_accrual_account_id NUMBER;
1222 l_charge_account_id NUMBER;
1223 l_variance_account_id NUMBER;
1224 l_tax_code_id NUMBER;
1225 -- Bug 5092489. Commented because not used.
1229 l_document_id NUMBER;
1226 --l_description VARCHAR2(100);
1227 --l_category_id NUMBER;
1228 l_owning_organization_id NUMBER;
1230 l_error_code NUMBER;
1231 l_vendor_id NUMBER;
1232 l_rate NUMBER;
1233 -- Bug 5092489. Commented because not used.
1234 --l_coa_id NUMBER;
1235 l_rate_type VARCHAR2(30);
1236 l_global_rate_type VARCHAR2(30);
1237 l_global_rate NUMBER;
1238 l_global_rate_date DATE;
1239 l_valid_flag VARCHAR2(1);
1240 l_po_num_code VARCHAR2(25);
1241 l_consumption_po_header_id NUMBER;
1242 l_consumption_release_id NUMBER;
1243 l_return_status VARCHAR2(1);
1244 l_archive_status VARCHAR2(1);
1245 l_archive_type VARCHAR2(30);
1246 l_archive_subtype VARCHAR2(30);
1247 l_msg_data VARCHAR2(2000);
1248 l_document_number VARCHAR2(30);
1249 l_current_org_id NUMBER;
1250 l_error_msg VARCHAR2(30);
1251 l_consumption_processed_flag VARCHAR2(1);
1252 -- Bug 5092489. Commented because not used.
1253 --l_po_line_id NUMBER;
1254 --l_from_uom_code VARCHAR2(25);
1255 --l_to_uom_code VARCHAR2(25);
1256 l_primary_uom VARCHAR2(25);
1257 l_purchasing_uom VARCHAR2(25);
1258 l_pay_on_flag VARCHAR2(25);
1259 l_pay_on_code VARCHAR2(25);
1260 -- Bug 5092489. Commented because not used.
1261 --l_conv_rate NUMBER;
1262 l_conv_qty NUMBER;
1263 -- Bug 5092489. Commented because not used.
1264 --l_uom_rate NUMBER;
1265 l_asl_id NUMBER;
1266 l_appl_id NUMBER;
1267 l_api_version NUMBER;
1268 -- Bug 5092489. Commented because not used.
1269 --l_precision NUMBER;
1270 l_debug NUMBER :=
1271 NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1272 l_vendor_name VARCHAR2(240);
1273 l_vendor_site VARCHAR2(15);
1274 l_transaction_date DATE;
1275 l_currency_code VARCHAR2(15);
1276 -- Bug 5092489. Commented because not used.
1277 --l_fin_curr_code VARCHAR2(15);
1278 l_func_po_price NUMBER;
1279 l_profile_option VARCHAR2(1);
1280
1281 --l_location_id_OU NUMBER ;
1282 TYPE g_asl_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1283 l_asl_cons_tab g_asl_tbl_type;
1284 l_curr_asl_index NUMBER := 0;
1285
1286 /* INVCONV */
1287 l_secondary_quantity NUMBER;
1288 l_secondary_uom VARCHAR2(50);
1289 -- Variables Defined for the fix of Bug 3959073
1290 l_inv_org_location NUMBER;
1291 l_header_ship_to_location NUMBER;
1292 /* Bug 4969420 Starts here*/
1293 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
1294 /* This would be passed to the po_lines_interface */
1295 l_blanket_price NUMBER;
1296 /* Bug 4969420 Ends here */
1297
1298 /* Bug 11900144. Addition of po_line_id */
1299 l_po_line_id NUMBER;
1300
1301 /* bug 5373370 - Start */
1302 l_dist_interface_header_id NUMBER;
1303 /* bug 5373370 - End */
1304
1305
1306 --=================
1307 -- CURSORS
1308 --=================
1309
1310 -- Cursor to get the header info to insert into the
1311 -- po_headers_interface table.
1312
1313 CURSOR header_csr_type IS
1314 SELECT DISTINCT
1315 transaction_source_id
1316 , valid_flag
1317 , org_id
1318 , accrual_account_id
1319 , charge_account_id
1320 , variance_account_id
1321 , rate_type
1322 /* bug 5210850 - Start */
1323 --, TRUNC(transaction_date)
1324 , DECODE(global_agreement_flag,'Y',TRUNC(transaction_date),
1325 DECODE(l_profile_option,'N', NULL,TRUNC(transaction_date)) )
1326 /* bug 5210850 - End */
1327 , rate
1328 , owning_organization_id
1329 , currency_code
1330 FROM
1331 mtl_consumption_txn_temp
1332 WHERE batch_id = p_batch_id;
1333
1334 -- Cursor to get the line info to insert into the
1335 -- po_lines_interface table for the corresponding header that is inserted.
1336
1337 CURSOR line_csr_type IS
1338 SELECT
1339 inventory_item_id
1340 , organization_id
1341 , net_qty
1342 , secondary_net_qty /* INVCONV */
1343 , transaction_cost -- This is the blanket_price from MCT Bug 4969421
1344 , TRUNC(transaction_date)
1345 , NVL(tax_code_id,-1)
1346 , rate
1347 , owning_organization_id
1348 , recoverable_tax
1349 , non_recoverable_tax
1350 , tax_recovery_rate
1351 , asl_id
1352 /* Bug 11900144. Addition of po_line_id */
1353 , po_line_id
1354 FROM
1355 mtl_consumption_txn_temp
1356 WHERE transaction_source_id = l_blanket_id
1357 AND valid_flag = l_valid_flag
1358 AND TRUNC(transaction_date) = NVL(l_transaction_date, TRUNC(transaction_date))
1359 AND rate_type = NVL(l_global_rate_type,'##')
1360 AND rate = NVL(l_global_rate,-1)
1361 AND owning_organization_id = l_vendor_site_id
1362 AND accrual_account_id = l_accrual_account_id
1363 AND charge_account_id = l_charge_account_id
1364 AND variance_account_id = l_variance_account_id;
1365
1366 /* INVCONV cursor to get secondary unit of measure for the item*/
1367 CURSOR cr_get_sec_uom IS
1368 SELECT m.unit_of_measure
1369 FROM mtl_system_items i,
1370 mtl_units_of_measure m
1371 WHERE i.inventory_item_id = l_item_id
1372 AND i.organization_id = l_organization_id
1373 AND i.secondary_uom_code = m.uom_code;
1374
1375 BEGIN
1376
1377 IF (l_debug = 1)
1378 THEN
1379 INV_LOG_UTIL.trace
1380 ( '>> Load Interface Tables','INV_CONSUMPTION_ADVICE_PROC'
1381 , 9
1382 );
1383 END IF;
1384
1385 x_return_status := FND_API.G_RET_STS_SUCCESS;
1386 l_user := FND_PROFILE.value('USER_ID');
1387 l_org_id := FND_PROFILE.value('ORG_ID');
1388 l_return_status := x_return_status;
1389
1390 /* bug 5200436 - Start */
1391 l_profile_option := NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N');
1392 /* bug 5200436 - End */
1393
1394 l_curr_asl_index := l_asl_cons_tab.COUNT;
1395
1396 INV_CONSUMPTION_ADVICE_PROC.validate_blanket(p_batch_id);
1397
1398 l_document_type_code := 'PO';
1399
1400 -- Get the header info
1401
1402 OPEN header_csr_type;
1403 LOOP
1404 FETCH header_csr_type
1405 INTO
1406 l_blanket_id
1407 , l_valid_flag
1408 , l_current_org_id
1409 , l_accrual_account_id
1410 , l_charge_account_id
1411 , l_variance_account_id
1412 , l_global_rate_type
1413 , l_global_rate_date
1414 , l_global_rate
1415 , l_vendor_site_id
1416 , l_currency_code;
1417
1418 EXIT WHEN header_csr_type%NOTFOUND;
1419
1420 IF l_valid_flag = 'N'
1421 THEN
1422 l_error_msg := 'INV_SUP_CONS_NO_BPO_EXISTS';
1423
1424 IF (l_debug = 1)
1425 THEN
1426 INV_LOG_UTIL.trace
1427 ( '>> No valid BPO:'||l_blanket_id,'INV_CONSUMPTION_ADVICE_PROC'
1428 , 9
1429 );
1430 END IF;
1431
1432 INV_CONSUMPTION_ADVICE_PROC.update_consumption
1433 (p_consumption_po_header_id => NULL
1434 ,p_consumption_release_id => NULL
1435 ,p_error_code => l_error_msg
1436 ,p_batch_id => p_batch_id
1437 ,p_transaction_source_id => l_blanket_id
1438 ,p_consumption_processed_flag => 'E'
1439 ,p_accrual_account_id => l_accrual_account_id
1440 ,p_charge_account_id => l_charge_account_id
1441 ,p_variance_account_id => l_variance_account_id
1442 ,p_transaction_date => l_global_rate_date
1443 ,p_global_rate_type => l_global_rate_type
1444 ,p_global_rate => l_global_rate
1445 ,p_vendor_site_id => l_vendor_site_id);
1446
1447 l_error_msg := NULL;
1448
1449 ELSE -- Valid Blanket
1450
1451 -- If the operating unit from which the concurrent pgm is run is
1452 -- different than the operating unit of the summarized transaction,
1456 IF NVL(l_org_id,-999) <> l_current_org_id
1453 -- set the OU context to be the OU of summarized transactions.
1454
1455 /* Bug 12669942. Added NVL condition for l_org_id */
1457 THEN
1458 INV_THIRD_PARTY_STOCK_UTIL.set_ou_context
1459 ( p_org_id => l_current_org_id
1460 );
1461 MO_GLOBAL.set_policy_context('S',l_current_org_id);
1462 ELSE
1463 MO_GLOBAL.set_policy_context('S',l_org_id);
1464 END IF;
1465
1466 -- Bug 5092489. Query modified.
1467 SELECT
1468 -- NVL(povs.ship_to_location_id,pov.ship_to_location_id)
1469 -- , NVL(povs.bill_to_location_id,pov.bill_to_location_id)
1470 /* fix for bug 5230913 - Start */
1471 povs.ship_to_location_id
1472 , povs.bill_to_location_id
1473 , pov.vendor_id --Bug 4723164
1474 /* fix for bug 5230913 - End */
1475 INTO
1476 l_vendor_ship_to_location
1477 , l_vendor_bill_to_location
1478 , l_vendor_id --Bug 4723164
1479 FROM
1480 po_vendor_sites_all povs
1481 , po_vendors pov
1482 WHERE povs.vendor_id = pov.vendor_id
1483 AND povs.vendor_site_id = l_vendor_site_id;
1484
1485 -- Bug 5092489. Query modified
1486 /* SELECT
1487 glc.PRECISION
1488 , glc.currency_code
1489 , fsp.ship_to_location_id
1490 , fsp.bill_to_location_id
1491 INTO
1492 l_precision
1493 , l_fin_curr_code
1494 , l_ship_to_location
1495 , l_bill_to_location
1496 FROM
1497 financials_system_params_all fsp
1498 , gl_sets_of_books glb
1499 , gl_currencies glc
1500 WHERE fsp.set_of_books_id = glb.set_of_books_id
1501 AND glb.currency_code = glc.currency_code
1502 AND NVL(fsp.org_id,-99) = NVL(l_current_org_id,-99);
1503 */
1504 SELECT
1505 fsp.bill_to_location_id
1506 INTO
1507 l_bill_to_location
1508 FROM
1509 financials_system_params_all fsp
1510 WHERE NVL(fsp.org_id,-99) = NVL(l_current_org_id,-99);
1511
1512 SELECT
1513 user_defined_po_num_code
1514 INTO
1515 l_po_num_code
1516 FROM
1517 po_system_parameters_all
1518 WHERE NVL(org_id,-99) = NVL(l_current_org_id,-99);
1519
1520 IF l_po_num_code <> 'AUTOMATIC'
1521 THEN
1522 SELECT
1523 segment1
1524 INTO
1525 l_document_number
1526 FROM
1527 po_headers_all
1528 WHERE po_header_id = l_blanket_id;
1529
1530 ELSE
1531 l_document_number := NULL;
1532 END IF;
1533
1534 SELECT
1535 po_headers_interface_s.NEXTVAL
1536 INTO
1537 l_interface_header_id
1538 FROM
1539 DUAL;
1540
1541 l_transaction_date := l_global_rate_date;
1542
1543 IF (l_valid_flag = 'G') AND (NVL(l_global_rate,-1) = -1)
1544 THEN
1545 l_global_rate_type := NULL;
1546 l_global_rate_date := NULL;
1547 l_global_rate := NULL;
1548 END IF;
1549
1550 -- Check the vendor sites for pay on code
1551
1552 SELECT
1553 pay_on_code
1554 INTO
1555 l_pay_on_code
1556 FROM
1557 po_vendor_sites_all
1558 WHERE vendor_site_id = l_vendor_site_id;
1559
1560 IF NVL(l_pay_on_code,'NONE') IN ('RECEIPT_AND_USE','USE')
1561 THEN
1562 l_pay_on_flag := 'USE';
1563 ELSE
1564 l_pay_on_flag := NULL;
1565 END IF;
1566
1567 --l_ship_to_location := NVL(l_vendor_ship_to_location,l_ship_to_location);
1568 l_bill_to_location := NVL(l_vendor_bill_to_location,l_bill_to_location);
1569
1570 --l_location_id_OU := NULL ;
1571 l_location_id :=
1572 INV_THIRD_PARTY_STOCK_UTIL.get_location(l_current_org_id);
1573
1574 --l_location_id_OU := l_location_id ;
1575
1576 INSERT INTO po_headers_interface
1577 ( interface_header_id
1578 , interface_source_code
1579 , batch_id
1580 , document_type_code
1581 , document_subtype
1582 , document_num
1583 , vendor_id
1584 , vendor_site_id
1585 , agent_id
1586 , currency_code
1587 , rate_type_code
1588 , rate_date
1589 , rate
1590 , ship_to_location_id
1591 , bill_to_location_id
1592 , terms_id
1593 , fob
1594 , pay_on_code
1595 , freight_terms
1596 , min_release_amount
1597 , creation_date
1598 , created_by
1599 , group_code
1600 , action
1601 , org_id
1602 )
1603 SELECT
1604 l_interface_header_id
1605 , 'CONSUMPTION_ADVICE'
1606 , p_batch_id
1607 , l_document_type_code
1608 , DECODE(l_valid_flag,'Y','RELEASE','STANDARD')
1609 , DECODE(l_valid_flag,'Y',segment1,NULL)
1610 , DECODE(l_valid_flag,'G',l_vendor_id,vendor_id) --Bug 4723164
1611 , DECODE(l_valid_flag,'G',l_vendor_site_id,vendor_site_id) --Bug 4723164
1612 , agent_id
1613 , currency_code
1614 , DECODE(l_valid_flag,'G',l_global_rate_type,rate_type)
1615 , DECODE(l_valid_flag,'G',l_global_rate_date,rate_date)
1616 , DECODE(l_valid_flag,'G',l_global_rate,rate)
1620 -- be taken from the BPA, regardless if the BPA is local or global
1617 --, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),ship_to_location_id)
1618 -- Bug Fix for 3959073
1619 -- Ship To Location to be set at the Header of the Consumption Advice should
1621 , ship_to_location_id
1622 , DECODE(l_valid_flag,'G',NVL(l_bill_to_location,l_location_id),bill_to_location_id)
1623 , terms_id
1624 , fob_lookup_code
1625 , l_pay_on_flag
1626 , freight_terms_lookup_code
1627 , min_release_amount
1628 , SYSDATE
1629 , l_user
1630 , 'DEFAULT'
1631 , 'NEW'
1632 , l_current_org_id
1633 FROM
1634 po_headers_all
1635 WHERE po_header_id = l_blanket_id;
1636
1637
1638 -- Now fetch info. related to the lines that we need to insert
1639
1640 OPEN line_csr_type;
1641 LOOP
1642 FETCH line_csr_type
1643 INTO
1644 l_item_id
1645 , l_organization_id
1646 , l_quantity
1647 , l_secondary_quantity -- INVCONV
1648 , l_po_price
1649 , l_date
1650 , l_tax_code_id
1651 , l_rate
1652 , l_owning_organization_id
1653 , l_recoverable_tax
1654 , l_nonrecoverable_tax
1655 , l_recovery_rate
1656 , l_asl_id
1657 /* Bug 11900144. Addition of po_line_id */
1658 , l_po_line_id
1659 ;
1660
1661 /* Bug 4969421 Starts here*/
1662 /*We use the blanket_price (from MCT) instead of the transaction_cost from MMT*/
1663 /*l_po_price was initially gettting the transaction cost from MMT.*/
1664 /*po_price now stores the blanket_price (PO currency) in MCT */
1665 /*l_blanket_price would be used to populate po_lines_interface. No conversions required */
1666 /*as blanket_price is in PO currency */
1667 l_blanket_price := l_po_price ;
1668 /* Bug 4969421 Ends here */
1669
1670
1671 EXIT WHEN line_csr_type%NOTFOUND;
1672
1673 IF (l_debug = 1)
1674 THEN
1675 INV_LOG_UTIL.trace
1676 ( '>> Load Interface Tables'||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
1677 , 9
1678 );
1679 END IF;
1680
1681 l_asl_cons_tab(l_curr_asl_index) := l_asl_id;
1682 l_curr_asl_index := l_curr_asl_index+1;
1683
1684 IF (l_debug = 1)
1685 THEN
1686 INV_LOG_UTIL.trace
1687 ( '>> Load Interface Tables:','INV_CONSUMPTION_ADVICE_PROC'
1688 , 9
1689 );
1690 END IF;
1691
1692 -- Bug 5092489. Retrival of po_line_id is commented
1693 -- because not used.
1694 SELECT
1695 unit_meas_lookup_code
1696 --, po_line_id
1697 INTO
1698 l_purchasing_uom
1699 --, l_po_line_id
1700 FROM po_lines_all
1701 WHERE po_header_id = l_blanket_id
1702 AND item_id = l_item_id
1703 AND po_line_id = l_po_line_id --bug 12580131,
1704 AND ROWNUM = 1;
1705
1706 l_primary_uom := INV_THIRD_PARTY_STOCK_UTIL.get_primary_uom
1707 ( p_inventory_item_id=> l_item_id
1708 , p_organization_id => l_organization_id
1709 );
1710 IF (l_debug = 1)
1711 THEN
1712 INV_LOG_UTIL.trace
1713 ( '>> Load Interface Tables_prf(UOM):'||l_primary_uom,'INV_CONSUMPTION_ADVICE_PROC'
1714 , 9
1715 );
1716 END IF;
1717
1718 -- If the primary UOM of the item is different than the purchasing
1719 -- UOM, convert the quantity to purchasing UOM.
1720
1721 IF l_primary_uom <> NVL(l_purchasing_uom,l_primary_uom)
1722 THEN
1723 IF (l_debug = 1)
1724 THEN
1725 INV_LOG_UTIL.trace
1726 ( '>> Load Interface Tables_prf:(UOM differ)','INV_CONSUMPTION_ADVICE_PROC'
1727 , 9
1728 );
1729 INV_LOG_UTIL.trace
1730 ( 'Primary,Purchasing UOM is '||l_primary_uom||' '||l_purchasing_uom,'INV_CONSUMPTION_ADVICE_PROC'
1731 , 9
1732 );
1733 END IF;
1734
1735 l_conv_qty := INV_CONVERT.inv_um_convert
1736 ( item_id => l_item_id
1737 , PRECISION => 5
1738 , from_quantity => l_quantity
1739 , from_unit => NULL
1740 , to_unit => NULL
1741 , from_name => l_primary_uom
1742 , to_name => l_purchasing_uom
1743 );
1744
1745 IF l_conv_qty < 0
1746 THEN
1747 l_error_msg := 'INV_CONS_SUP_NO_UOM_CONV';
1748 END IF;
1749
1750 /* Bug 5092489. Commented because not used in code
1751 INV_THIRD_PARTY_STOCK_UTIL.Get_Vendor_Info
1752 ( p_vendor_site_id => l_vendor_site_id
1753 , x_vendor_name => l_vendor_name
1754 , x_vendor_site_code => l_vendor_site
1755 );
1756 IF (l_debug = 1)
1757 THEN
1758 INV_LOG_UTIL.trace
1759 ( '>> UOM conversion '||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
1760 , 9
1761 );
1762 END IF;
1763
1764 l_from_uom_code :=
1765 INV_THIRD_PARTY_STOCK_UTIL.get_uom_code
1769 );
1766 ( p_unit_of_measure => l_primary_uom
1767 , p_vendor_name => l_vendor_name
1768 , p_vendor_site_code => l_vendor_site
1770
1771 l_to_uom_code :=
1772 INV_THIRD_PARTY_STOCK_UTIL.get_uom_code
1773 ( p_unit_of_measure => l_purchasing_uom
1774 , p_vendor_name => l_vendor_name
1775 , p_vendor_site_code => l_vendor_site
1776 );
1777
1778
1779 IF (l_debug = 1)
1780 THEN
1781 INV_LOG_UTIL.trace
1782 ( '>> From UOM '||l_from_uom_code,'INV_CONSUMPTION_ADVICE_PROC'
1783 , 9
1784 );
1785 INV_LOG_UTIL.trace
1786 ( '>> To UOM '||l_to_uom_code,'INV_CONSUMPTION_ADVICE_PROC'
1787 , 9
1788 );
1789 END IF;
1790
1791 INV_CONVERT.inv_um_conversion
1792 (item_id => l_item_id
1793 ,from_unit => l_from_uom_code
1794 ,to_unit => l_to_uom_code
1795 ,uom_rate => l_uom_rate
1796 );
1797
1798 IF (l_debug = 1)
1799 THEN
1800 INV_LOG_UTIL.trace
1801 ( '>>UOM Rate '||l_uom_rate,'INV_CONSUMPTION_ADVICE_PROC'
1802 , 9
1803 );
1804 INV_LOG_UTIL.trace
1805 ( '>> Qty '||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
1806 , 9
1807 );
1808 END IF;
1809
1810 IF l_uom_rate IS NULL OR l_conv_qty < 0
1811 THEN
1812 l_error_msg := 'INV_CONS_SUP_NO_UOM_CONV';
1813 END IF;
1814 */
1815 -- The PO price should be unit price against purchasing UOM; hence
1816 -- convert to unit price for purchasing UOM
1817 /* conversion not reqd anymore - Bug 4969421 */
1818 /*
1819 l_po_price := l_po_price / NVL(l_uom_rate,1);
1820
1821 IF (l_debug = 1)
1822 THEN
1823 INV_LOG_UTIL.trace
1824 ( '>> Load Interface Tables_prf(Price):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
1825 , 9
1826 );
1827 END IF;
1828 */
1829 ELSE
1830 l_conv_qty := l_quantity;
1831 END IF;
1832
1833 -- INVCONV retrive secondary unit of measure for the item
1834 -- if its not null that means item is tracked in dual units
1835 -- from the quantity
1836 IF l_secondary_quantity IS NOT NULL THEN
1837 OPEN cr_get_sec_uom;
1838 FETCH cr_get_sec_uom INTO l_secondary_uom;
1839 IF (cr_get_sec_uom%NOTFOUND) THEN
1840 CLOSE cr_get_sec_uom;
1841 l_secondary_quantity := NULL;
1842 ELSE
1843 CLOSE cr_get_sec_uom;
1844 END IF;
1845 END IF ;
1846
1847 IF (l_debug = 1)
1848 THEN
1849 INV_LOG_UTIL.trace
1850 ( '>> Outside Loop(Qty):'||l_conv_qty,'INV_CONSUMPTION_ADVICE_PROC'
1851 , 9
1852 );
1853 INV_LOG_UTIL.trace
1854 ( '>> Outside Loop(Price):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
1855 , 9
1856 );
1857 INV_LOG_UTIL.trace
1858 ( '>> Outside Loop(error):'||l_error_msg,'INV_CONSUMPTION_ADVICE_PROC'
1859 , 9
1860 );
1861 END IF;
1862
1863 IF l_error_msg IS NOT NULL
1864 THEN
1865 l_consumption_release_id := NULL;
1866 l_consumption_po_header_id := NULL;
1867 l_consumption_processed_flag := 'E';
1868 EXIT;
1869 END IF;
1870
1871 /* conversion not reqd anymore - Bug 4969421 */
1872 /*
1873
1874 -- If the blanket is in foreign currency, convert the unit price to
1875 -- currency of the original blanket. THe unit price from MMT is
1876 -- in functional currency.
1877
1878 IF NVL(l_rate,-1) = -1
1879 THEN
1880 l_po_price := l_po_price - NVL(l_nonrecoverable_tax,0);
1881 IF (l_debug = 1)
1882 THEN
1883 INV_LOG_UTIL.trace
1884 ( '>> PO Price (no conv):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
1885 , 9
1886 );
1887 END IF;
1888 ELSE
1889
1890 l_func_po_price := l_po_price - NVL(l_nonrecoverable_tax,0);
1891
1892 IF (l_debug = 1)
1893 THEN
1894 INV_LOG_UTIL.trace
1895 ( '>> PO Price (conv):'||l_po_price||' '||l_nonrecoverable_tax,'INV_CONSUMPTION_ADVICE_PROC'
1896 , 9
1897 );
1898 END IF;
1899
1900 SELECT
1901 DECODE(NVL(fc.minimum_accountable_unit,0), 0,
1902 ROUND((l_func_po_price*l_conv_qty)* (1/ABS(l_rate))/l_conv_qty,
1903 NVL(fc.extended_precision,fc.PRECISION)),
1904 ROUND(l_func_po_price* l_conv_qty/fc.minimum_accountable_unit) *
1905 fc.minimum_accountable_unit*(1/ABS(l_rate))/l_conv_qty)
1906 INTO
1907 l_po_price
1908 FROM
1909 fnd_currencies fc
1910 WHERE fc.currency_code = NVL(l_currency_code,l_fin_curr_code);
1911
1912 IF (l_debug = 1)
1913 THEN
1914 INV_LOG_UTIL.trace
1915 ( '>> PO Price (fnd_curr):'||l_po_price,'INV_CONSUMPTION_ADVICE_PROC'
1916 , 9
1917 );
1918 END IF;
1919 END IF;
1920 */
1921 -- Bug Fix for 3959073
1922 -- Getting the ship to location set at the PO Header of the Blanket Agreement
1923 SELECT
1924 ship_to_location_id
1925 INTO
1926 l_header_ship_to_location
1927 FROM
1928 po_headers_all
1929 WHERE po_header_id = l_blanket_id;
1930
1931 -- Bug Fix for 3959073
1932 -- Getting the location of the Inventory Organization
1933 l_inv_org_location:= INV_THIRD_PARTY_STOCK_UTIL.get_location(l_organization_id);
1934 IF (l_debug = 1)
1935 THEN
1936 INV_LOG_UTIL.trace
1937 ( '>> Location :'||l_location_id,'INV_CONSUMPTION_ADVICE_PROC'
1938 , 9
1939 );
1940 INV_LOG_UTIL.trace
1941 ( '>> Blanket /Item :'||l_blanket_id||' '||l_item_id,'INV_CONSUMPTION_ADVICE_PROC'
1942 , 9
1943 );
1944 INV_LOG_UTIL.trace
1945 ( '>> Owning org is :'||l_owning_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
1946 , 9
1947 );
1948 END IF;
1949
1950 SELECT PO_LINES_INTERFACE_S.NEXTVAL
1951 INTO l_interface_line_id
1952 FROM DUAL;
1953
1954 /* Bug 7231720 If the Profile Option PO: Automatic Document Sourcing is set to "Yes" then
1955 the latest valid blanket must be picked even if the blanket is not present in the Approved
1956 Supplier List. There is no check for the presence of a record in the table po_asl_documents if
1957 the Profile Option PO: Automatic Document Sourcing is set to "No". The blanket is picked up
1958 from MMT.TRANSACTION_SOURCE_ID when the profile is set to "No" */
1959
1960 /* Bug 11900144. Commented the below if condition as line details are taken from MCT,
1961 separate insert statements based on profile value are not required */
1962 --IF (nvl(fnd_profile.value('PO_AUTO_SOURCE_DOC'),'N') = 'Y') THEN
1963
1964 INSERT INTO po_lines_interface
1965 ( interface_header_id
1966 , interface_line_id
1967 , line_num
1968 , line_type_id
1969 , item_id
1970 , item_description
1971 , category_id
1972 , unit_of_measure
1973 , quantity
1974 , vendor_product_num
1975 , unit_price
1976 , ship_to_organization_id
1977 , ship_to_location_id
1978 , need_by_date
1979 , promised_date
1980 , creation_date
1981 , created_by
1982 , tax_code_id
1983 , from_header_id
1984 , from_line_id
1985 , closed_date
1986 , closed_by
1987 , receive_close_tolerance
1988 , closed_code
1989 , closed_reason
1990 , secondary_quantity --/* INVCONV
1991 , secondary_unit_of_measure --/* INVCONV
1992 )
1993 SELECT
1994 l_interface_header_id
1995 , l_interface_line_id
1996 , DECODE(l_valid_flag,'Y',line_num,NULL)
1997 , line_type_id
1998 , l_item_id
2002 , l_conv_qty
1999 , item_description
2000 , category_id
2001 , unit_meas_lookup_code
2003 , vendor_product_num
2004 ,l_blanket_price
2005 , l_organization_id
2006 , NVL(l_inv_org_location,l_header_ship_to_location)
2007 , l_date
2008 , SYSDATE
2009 , SYSDATE
2010 , l_user
2011 , DECODE(l_tax_code_id,-1,NULL,l_tax_code_id)
2012 , DECODE(l_valid_flag,'G',l_blanket_id,NULL)
2013 , DECODE(l_valid_flag,'G',po_line_id,NULL)
2014 , SYSDATE
2015 , l_user
2016 , 100
2017 , 'OPEN'
2018 , 'Consumption Advice'
2019 , l_secondary_quantity --/* INVCONV
2020 , l_secondary_uom --/* INVCONV
2021 FROM
2022 po_lines_all pla
2023 WHERE po_header_id = l_blanket_id
2024 AND item_id = l_item_id
2025 AND po_line_id=l_po_line_id;
2026 --Bug 11900144. removed rownum condition and added po_line_id
2027
2028 -- Deleted unused commented code as part of bug 11900144
2029
2030 SELECT
2031 vendor_id
2032 INTO
2033 l_vendor_id
2034 FROM
2035 po_vendor_sites_all
2036 WHERE vendor_site_id = l_owning_organization_id;
2037
2038 IF (l_debug = 1)
2039 THEN
2040 INV_LOG_UTIL.trace
2041 ( 'Populated Lines Interface','INV_CONSUMPTION_ADVICE_PROC'
2042 , 9
2043 );
2044 END IF;
2045
2046 /* Bug 5373370 - Start */
2047 SELECT
2048 PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
2049 INTO
2050 l_dist_interface_header_id
2051 FROM DUAL;
2052
2053 IF (l_debug = 1)
2054 THEN
2055 INV_LOG_UTIL.trace
2056 ( '>> interface_header_id :'|| l_dist_interface_header_id,'INV_CONSUMPTION_ADVICE_PROC'
2057 , 9
2058 );
2059 END IF ;
2060
2061 /* Bug 5373370 - End */
2062
2063
2064 INSERT INTO po_distributions_interface
2065 ( interface_header_id
2066 , interface_line_id
2067 , interface_distribution_id
2068 , quantity_ordered
2069 , charge_account_id
2070 , accrual_account_id
2071 , variance_account_id
2072 , deliver_to_location_id
2073 , destination_organization_id
2074 , recoverable_Tax
2075 , nonrecoverable_Tax
2076 , recovery_rate
2077 , creation_date
2078 , created_by
2079 , destination_type_code
2080 , rate
2081 , rate_date
2082 /* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
2083 , INTERFACE_DISTRIBUTION_REF
2084 /* Bug fix 5373370 - End */
2085 )
2086 SELECT
2087 l_interface_header_id
2088 , l_interface_line_id
2089 , l_dist_interface_header_id -- bug 5373370
2090 , l_conv_qty
2091 , l_charge_account_id
2092 , l_accrual_account_id
2093 , l_variance_account_id
2094 --, DECODE(l_valid_flag,'G',NVL(l_ship_to_location,l_location_id),l_location_id)
2095 -- Bug Fix for 3959073
2096 -- Ship To Location to be set at the Distribution Line of the Consumption
2097 -- Advice should be taken from the Inventory Organization. If the location
2098 -- is not defined for the Inventory Organization, then take the Ship To
2099 -- Location from the Header of the current Consumption Advice, i.e.,
2100 -- from the Header of the BPA.
2101 , NVL(l_inv_org_location,l_header_ship_to_location)
2102 , l_organization_id
2103 , (l_recoverable_tax*l_conv_qty)
2104 , (l_nonrecoverable_tax*l_conv_qty)
2105 , l_recovery_rate
2106 , SYSDATE
2107 , l_user
2108 , 'INVENTORY'
2109 , DECODE(l_valid_flag,'G',l_global_rate,DECODE(l_rate,-1,NULL,l_rate))
2110 , DECODE(l_valid_flag,'G',l_global_rate_date
2111 ,DECODE(l_rate,-1,NULL,l_date))
2112 /* Bug fix 5373370 - new column addded to po_dist_interface - Start*/
2113 , TO_CHAR(l_dist_interface_header_id)
2114 /* Bug fix 5373370 - End */
2115 FROM DUAL;
2116
2117
2118 /* Bug 5373370 - Start */
2119 /* The distribution_interface_ref that is inserted into
2120 PO_DISTRIBUTIONS_INTERFACE is inserted into MCT. This column
2121 will later be joined to PO_DISTRIBUTIONS_ALL to fetch the
2122 PO_DISTRIBUTION_ID */
2123
2124 /* MMT no longer used in this query */
2125
2126 UPDATE mtl_consumption_transactions
2127 SET interface_distribution_ref = TO_CHAR(l_dist_interface_header_id)
2128 WHERE transaction_id IN
2129 ( SELECT mct.transaction_id
2130 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2131 WHERE mct.consumption_processed_flag IN ('N','E')
2132 AND mct.inventory_item_id = l_item_id
2133 AND mct.transaction_source_id = l_blanket_id
2134 AND mct.blanket_price = l_blanket_price
2135 AND mct.organization_id = l_organization_id -- bug 14758693
2136 AND NVL(mct.recoverable_tax,0) = l_recoverable_tax
2137 AND NVL(mct.non_recoverable_tax,0) = l_nonrecoverable_tax
2138 AND mct.charge_account_id = l_charge_account_id
2139 AND mct.variance_account_id = l_variance_account_id
2140 AND mct.ACCRUAL_ACCOUNT_ID = l_accrual_account_id
2141 AND NVL(mct.tax_recovery_rate,0) = l_recovery_rate
2142 AND NVL(mct.tax_code_id,-1) = l_tax_code_id
2143 AND mct.batch_id = p_batch_id
2144 );
2145
2146 /* Bug 5373370 - End */
2147
2148
2149 IF (l_debug = 1)
2150 THEN
2151 INV_LOG_UTIL.trace
2152 ( 'Populated Distributions Interface','INV_CONSUMPTION_ADVICE_PROC'
2153 , 9
2154 );
2155 END IF;
2156 END LOOP; -- line
2157
2158 CLOSE line_csr_type;
2159
2160 -- Call the document sourcing to create a release or a std PO
2161
2162 IF l_error_msg IS NULL
2163 THEN
2164
2165 IF (l_debug = 1)
2166 THEN
2167 INV_LOG_UTIL.trace
2168 ( '>> Create Documents ','INV_CONSUMPTION_ADVICE_PROC'
2169 , 9
2170 );
2171 END IF;
2172
2173 IF (l_debug = 1)
2174 THEN
2175 INV_LOG_UTIL.trace
2176 ( 'Batch Id : '||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
2177 , 9
2178 );
2179 END IF;
2180
2181 IF (l_debug = 1)
2182 THEN
2183 INV_LOG_UTIL.trace
2184 ( 'Document Id : '||l_document_id,'INV_CONSUMPTION_ADVICE_PROC'
2185 , 9
2186 );
2187 END IF;
2188
2189 IF (l_debug = 1)
2190 THEN
2191 INV_LOG_UTIL.trace
2192 ( 'Document Number : '||l_document_number,'INV_CONSUMPTION_ADVICE_PROC'
2193 , 9
2194 );
2195 END IF;
2196
2197 IF (l_debug = 1)
2198 THEN
2199 INV_LOG_UTIL.trace
2200 ( 'Line : '||l_lines,'INV_CONSUMPTION_ADVICE_PROC'
2201 , 9
2202 );
2203 END IF;
2204
2205 INV_PO_THIRD_PARTY_STOCK_MDTR.create_documents
2206 ( p_batch_id => p_batch_id
2207 , p_document_id => l_document_id
2208 , p_document_number => l_document_number
2209 , p_line => l_lines
2210 , x_error_code => l_error_code
2211 );
2212
2213 IF (l_debug = 1)
2214 THEN
2215 INV_LOG_UTIL.trace
2216 ( 'Error Code : '||l_error_code,'INV_CONSUMPTION_ADVICE_PROC'
2217 , 9
2218 );
2219 END IF;
2220
2221 IF (l_debug = 1)
2222 THEN
2223 INV_LOG_UTIL.trace
2224 ( '<< Create Documents ','INV_CONSUMPTION_ADVICE_PROC'
2225 , 9
2226 );
2227 END IF;
2228
2229
2230 IF l_error_code = 1
2231 THEN
2232
2233
2234 -- Update the ASL entry with the last billing date
2235
2236 FOR v_counter IN l_asl_cons_tab.FIRST .. l_asl_cons_tab.LAST
2237 LOOP
2238 l_asl_id := l_asl_cons_tab(v_counter);
2239
2240 IF (l_debug = 1)
2241 THEN
2242 INV_LOG_UTIL.trace
2243 ( '>> Update ASL ' ,'INV_CONSUMPTION_ADVICE_PROC'
2244 , 9
2245 );
2246 END IF;
2247
2248 IF (l_debug = 1)
2249 THEN
2250 INV_LOG_UTIL.trace
2251 ( ' ASL_ID : '||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
2252 , 9
2253 );
2254 END IF;
2255
2256 INV_PO_THIRD_PARTY_STOCK_MDTR.update_asl
2257 (p_asl_id => l_asl_id);
2258
2259 END LOOP;
2260
2261 IF (l_debug = 1)
2262 THEN
2263 INV_LOG_UTIL.trace
2264 ( '<< Update ASL ','INV_CONSUMPTION_ADVICE_PROC'
2265 , 9
2266 );
2267 END IF;
2268
2269 -- If a Release was created, populate release id
2270
2274 l_consumption_po_header_id := NULL;
2271 IF l_valid_flag = 'Y' THEN
2272
2273 l_consumption_release_id := l_document_id;
2275 l_error_msg := NULL;
2276 l_consumption_processed_flag := 'Y';
2277 ELSE -- GA, Standard PO was created
2278
2279 l_consumption_release_id := NULL;
2280 l_consumption_po_header_id := l_document_id;
2281 l_error_msg :=NULL;
2282 l_consumption_processed_flag := 'Y';
2283 END IF;
2284
2285 IF NVL(l_valid_flag,'N') = 'Y'
2286 THEN
2287 l_archive_subtype := 'BLANKET';
2288 l_archive_type := 'RELEASE';
2289 ELSIF NVL(l_valid_flag,'N') = 'G'
2290 THEN
2291 l_archive_subtype := 'STANDARD';
2292 l_archive_type := 'PO';
2293 END IF;
2294
2295 l_api_version := 1.0;
2296
2297 IF (l_debug = 1)
2298 THEN
2299 INV_LOG_UTIL.trace
2300 ( '>> Archive PO ','INV_CONSUMPTION_ADVICE_PROC'
2301 , 9
2302 );
2303 END IF;
2304
2305 IF (l_debug = 1)
2306 THEN
2307 INV_LOG_UTIL.trace
2308 ( ' api version : '||l_api_version,'INV_CONSUMPTION_ADVICE_PROC'
2309 , 9
2310 );
2311 END IF;
2312
2313 IF (l_debug = 1)
2314 THEN
2315 INV_LOG_UTIL.trace
2316 ( ' document id : '||l_document_id,'INV_CONSUMPTION_ADVICE_PROC'
2317 , 9
2318 );
2319 END IF;
2320
2321 IF (l_debug = 1)
2322 THEN
2323 INV_LOG_UTIL.trace
2324 ( ' document_type : '||l_archive_type,'INV_CONSUMPTION_ADVICE_PROC'
2325 , 9
2326 );
2327 END IF;
2328
2329 IF (l_debug = 1)
2330 THEN
2331 INV_LOG_UTIL.trace
2332 ( ' document subtype : '||l_archive_subtype,'INV_CONSUMPTION_ADVICE_PROC'
2333 , 9
2334 );
2335 END IF;
2336
2337 INV_PO_THIRD_PARTY_STOCK_MDTR.archive_po
2338 ( p_api_version => l_api_version
2339 , p_document_id => l_document_id
2340 , p_document_type => l_archive_type
2341 , p_document_subtype => l_archive_subtype
2342 , x_return_status => l_archive_status
2343 , x_msg_data => l_msg_data
2344 );
2345
2346 IF (l_debug = 1)
2347 THEN
2348 INV_LOG_UTIL.trace
2349 ( ' return status : '||l_archive_status,'INV_CONSUMPTION_ADVICE_PROC'
2350 , 9
2351 );
2352 END IF;
2353
2354 IF (l_debug = 1)
2355 THEN
2356 INV_LOG_UTIL.trace
2357 ( ' msg data : '||l_msg_data,'INV_CONSUMPTION_ADVICE_PROC'
2358 , 9
2359 );
2360 END IF;
2361
2362 IF (l_debug = 1)
2363 THEN
2364 INV_LOG_UTIL.trace
2365 ( '<< Archive PO : ','INV_CONSUMPTION_ADVICE_PROC'
2366 , 9
2367 );
2368 END IF;
2369
2370
2371 IF l_archive_status <> FND_API.G_RET_STS_SUCCESS
2372 THEN
2373 l_error_msg := 'INV_SUP_CONS_ARCHIVING_FAIL';
2374 END IF;
2375
2376 ELSE
2377
2378 -- autocreate returned error
2379 l_consumption_release_id := NULL;
2380 l_consumption_po_header_id :=NULL;
2381 l_error_msg := 'INV_SUP_CONS_AUTO_CREATE_FAIL';
2382 l_consumption_processed_flag := 'E';
2383 END IF;
2384 END IF;
2385
2386 INV_CONSUMPTION_ADVICE_PROC.Update_Consumption
2387 (p_consumption_po_header_id => l_consumption_po_header_id
2388 ,p_consumption_release_id => l_consumption_release_id
2389 ,p_error_code => l_error_msg
2390 ,p_batch_id => p_batch_id
2391 ,p_transaction_source_id => l_blanket_id
2392 ,p_consumption_processed_flag => l_consumption_processed_flag
2396 ,p_transaction_date => l_transaction_date
2393 ,p_accrual_account_id => l_accrual_account_id
2394 ,p_charge_account_id => l_charge_account_id
2395 ,p_variance_account_id => l_variance_account_id
2397 ,p_global_rate_type => l_global_rate_type
2398 ,p_global_rate => l_global_rate
2399 ,p_vendor_site_id => l_vendor_site_id);
2400
2401 IF (l_debug = 1)
2402 THEN
2403 INV_LOG_UTIL.trace
2404 ('Release created :'||l_consumption_release_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2405 INV_LOG_UTIL.trace
2406 ('PO created :'||l_consumption_po_header_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2407 INV_LOG_UTIL.trace
2408 ('Batch :'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2409 INV_LOG_UTIL.trace
2410 ('Blanket is :'||l_blanket_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2411 INV_LOG_UTIL.trace
2412 ('Site is :'||l_vendor_site_id,'INV_CONSUMPTION_ADVICE_PROC',9);
2413 INV_LOG_UTIL.trace
2414 ('Txn Date is :'||l_transaction_date,'INV_CONSUMPTION_ADVICE_PROC',9);
2415 END IF;
2416
2417 l_error_msg := NULL;
2418 l_consumption_processed_flag := NULL;
2419
2420 END IF;
2421
2422 END LOOP; -- header
2423
2424 CLOSE header_csr_type;
2425
2426 IF (l_debug = 1)
2427 THEN
2428 INV_LOG_UTIL.trace
2429 ( '<< Load Interface Tables','INV_CONSUMPTION_ADVICE_PROC'
2430 , 9
2431 );
2432 END IF;
2433
2434 EXCEPTION
2435
2436 WHEN OTHERS THEN
2437
2438 IF header_csr_type% ISOPEN
2439 THEN
2440 CLOSE header_csr_type;
2441 END IF;
2442
2443 IF line_csr_type% ISOPEN
2444 THEN
2445 CLOSE line_csr_type;
2446 END IF;
2447
2448 IF (l_debug = 1) THEN
2449 INV_LOG_UTIL.trace
2450 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2451 , 9
2452 );
2453 END IF;
2454
2455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2456
2457 END load_interface_tables;
2458
2459
2460 --========================================================================
2461 -- PROCEDURE : Load_Summarized_Quantity PRIVATE
2462 -- COMMENT : This procedure summarizes records in
2463 -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2464 -- transaction_source_id, inventory_item_id, organization_id
2465 -- transaction cost, tax code id, accrual account, variance
2466 -- account, charge account
2467 -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2468 -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2469 -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2470 -- for inventory convergence project.
2471 --=========================================================================
2472 PROCEDURE load_summarized_quantity
2473 ( p_txn_source_tab IN g_cons_tbl_type
2474 , p_inventory_item_tab IN g_cons_tbl_type
2475 , p_organization_tab IN g_cons_tbl_type
2476 , p_own_org_tab IN g_cons_tbl_type
2477 , p_transaction_cost_tab IN g_cons_tbl_type
2478 , p_tax_code_tab IN g_cons_tbl_type
2479 , p_rec_tax_tab IN g_cons_tbl_type
2480 , p_non_rec_tax_tab IN g_cons_tbl_type
2481 , p_accrual_account_tab IN g_cons_tbl_type
2482 , p_charge_account_tab IN g_cons_tbl_type
2483 , p_variance_account_tab IN g_cons_tbl_type
2484 , p_date_tab IN g_cons_date_tbl_type
2485 , p_rate_tab IN g_cons_tbl_type
2486 , p_rate_type_tab IN g_cons_varchar_tbl_type
2487 , p_batch_id IN NUMBER
2488 , p_tax_rec_rate_tab IN g_cons_tbl_type -- Bug 4969421
2489 )
2490 IS
2491 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2492 -- Bug 5092489, commenting as not used
2493 --l_txn_count NUMBER;
2494 --l_txn_first NUMBER;
2495 --l_txn_last NUMBER;
2496 --l_txn_ct NUMBER;
2497
2498 BEGIN
2499
2500 IF (l_debug = 1)
2501 THEN
2502 INV_LOG_UTIL.trace
2503 ( '>> Load Summarized Quantity','INV_CONSUMPTION_ADVICE_PROC'
2504 , 9
2505 );
2506 END IF;
2507
2508 -- Bug 5092489, commenting as not used
2509 /*l_txn_first := p_txn_source_tab.FIRST;
2510 l_txn_last := p_txn_source_tab.LAST;
2511 l_txn_ct := p_txn_source_tab.COUNT;
2512
2513 IF l_txn_first IS NULL
2514 THEN
2515 l_txn_first :=0;
2516 END IF;
2517
2518 IF l_txn_last IS NULL
2519 THEN
2520 l_txn_last :=0;
2521 END IF;
2522
2523 IF (l_debug = 1)
2524 THEN
2525 INV_LOG_UTIL.trace
2526 ( 'First is :'||l_txn_first,'INV_CONSUMPTION_ADVICE_PROC'
2527 , 9
2528 );
2529 INV_LOG_UTIL.trace
2530 ( 'Last is :'||l_txn_ct,'INV_CONSUMPTION_ADVICE_PROC'
2531 , 9
2532 );
2533 END IF;
2534 */
2535
2536 IF (l_debug = 1)
2537 THEN
2538 INV_LOG_UTIL.trace
2539 ( 'Last is :'||p_txn_source_tab.COUNT,'INV_CONSUMPTION_ADVICE_PROC'
2540 , 9
2541 );
2542 END IF;
2543
2544 -- Use the bulk update to summarize the net quantity for
2545 -- the current batch. The net quantity takes into account
2546 -- any corrections that were made to the transaction quantity
2547 -- by the user.
2548
2552 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2549 IF p_txn_source_tab.COUNT > 0
2550 THEN
2551
2553 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2554 SET (mctt.net_qty,mctt.secondary_net_qty) =
2555 (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2556
2557 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2558 --, MTL_MATERIAL_TRANSACTIONS mmt
2559 WHERE --mmt.transaction_id = mct.transaction_id AND
2560 mct.transaction_source_id = p_txn_source_tab(i)
2561 AND mct.inventory_item_id = p_inventory_item_tab(i)
2562 AND mct.organization_id = p_organization_tab(i)
2563 AND mct.owning_organization_id = p_own_org_tab(i)
2564 /* Bug 4969420 Starts here*/
2565 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
2566 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
2567 AND mct.blanket_price = p_transaction_cost_tab(i)
2568 /* Bug 4969421 Ends here*/
2569 /* Bug 11900144. Addition of po_line_id */
2570 AND mct.po_line_id=mctt.po_line_id
2571 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
2572 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
2573 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
2574 AND mct.accrual_account_id = p_accrual_account_tab(i)
2575 AND mct.charge_account_id = p_charge_account_tab(i)
2576 AND mct.variance_account_id = p_variance_account_tab(i)
2577 AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
2578 AND NVL(mct.rate,-1) = p_rate_tab(i)
2579 AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
2580 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2581 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2582 /*Bug 4969421 - Ends here */
2583 AND mct.batch_id = p_batch_id
2584 AND mct.consumption_processed_flag IN ('N', 'E'))
2585 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
2586 AND mctt.inventory_item_id = p_inventory_item_tab(i)
2587 AND mctt.organization_id = p_organization_tab(i)
2588 AND mctt.owning_organization_id = p_own_org_tab(i)
2589 AND mctt.transaction_cost = p_transaction_cost_tab(i)
2590 AND mctt.tax_code_id = p_tax_code_tab(i)
2591 AND mctt.recoverable_tax = p_rec_tax_tab(i)
2592 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
2593 AND mctt.accrual_account_id = p_accrual_account_tab(i)
2594 AND mctt.charge_account_id = p_charge_account_tab(i)
2595 AND mctt.variance_account_id = p_variance_account_tab(i)
2596 AND mctt.transaction_date = TRUNC(p_date_tab(i))
2597 AND mctt.rate = p_rate_tab(i)
2598 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2599 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2600 /*Bug 4969421 - Ends here */
2601 AND mctt.rate_type = p_rate_type_tab(i);
2602
2603
2604 ELSE
2605 IF (l_debug = 1)
2606 THEN
2607 INV_LOG_UTIL.trace
2608 ( '<< Load Summarized Quantity null index','INV_CONSUMPTION_ADVICE_PROC' , 9
2609 );
2610 END IF;
2611 END IF;
2612
2613 IF (l_debug = 1)
2614 THEN
2615 INV_LOG_UTIL.trace
2616 ( '<< Load Summarized Quantity','INV_CONSUMPTION_ADVICE_PROC'
2617 , 9
2618 );
2619 END IF;
2620
2621 EXCEPTION
2622
2623 WHEN OTHERS THEN
2624 IF (l_debug = 1) THEN
2625 INV_LOG_UTIL.trace
2626 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2627 , 9
2628 );
2629 END IF;
2630
2631 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_SUM');
2632 FND_MSG_PUB.ADD;
2633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2634
2635 END load_summarized_quantity;
2636
2637 --========================================================================
2638 -- PROCEDURE : Load_Summarized_Quantity_prf PRIVATE
2639 -- COMMENT : This procedure summarizes records in
2640 -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2641 -- transaction_source_id, inventory_item_id, organization_id
2642 -- transaction cost, tax code id, accrual account, variance
2643 -- account, charge account
2644 -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2645 -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2646 -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2647 -- for inventory convergence project.
2648 --=========================================================================
2649 PROCEDURE load_summarized_quantity_prf
2650 ( p_txn_source_tab IN g_cons_tbl_type
2651 , p_inventory_item_tab IN g_cons_tbl_type
2652 , p_organization_tab IN g_cons_tbl_type
2653 , p_own_org_tab IN g_cons_tbl_type
2654 , p_transaction_cost_tab IN g_cons_tbl_type
2655 , p_tax_code_tab IN g_cons_tbl_type
2656 , p_rec_tax_tab IN g_cons_tbl_type
2657 , p_non_rec_tax_tab IN g_cons_tbl_type
2658 , p_accrual_account_tab IN g_cons_tbl_type
2659 , p_charge_account_tab IN g_cons_tbl_type
2660 , p_variance_account_tab IN g_cons_tbl_type
2661 , p_date_tab IN g_cons_date_tbl_type
2662 , p_rate_tab IN g_cons_tbl_type
2663 , p_rate_type_tab IN g_cons_varchar_tbl_type
2664 , p_batch_id IN NUMBER
2665 , p_tax_rec_rate_tab IN g_cons_tbl_type -- Bug 4969421
2666 )
2667 IS
2668 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2669 -- Bug 5092489, commenting as not used
2670 --l_txn_count NUMBER;
2671 --l_txn_first NUMBER;
2672 --l_txn_last NUMBER;
2673 --l_txn_ct NUMBER;
2674 BEGIN
2675
2676 IF (l_debug = 1)
2677 THEN
2678 INV_LOG_UTIL.trace
2679 ( '>> Load Summarized Quantity Prf','INV_CONSUMPTION_ADVICE_PROC'
2683
2680 , 9
2681 );
2682 END IF;
2684 -- Bug 5092489, commenting as not used
2685 /*l_txn_first := p_txn_source_tab.FIRST;
2686 l_txn_last := p_txn_source_tab.LAST;
2687 l_txn_ct := p_txn_source_tab.COUNT;
2688
2689 IF l_txn_first IS NULL
2690 THEN
2691 l_txn_first :=0;
2692 END IF;
2693
2694 IF l_txn_last IS NULL
2695 THEN
2696 l_txn_last :=0;
2697 END IF;
2698
2699 IF (l_debug = 1)
2700 THEN
2701 INV_LOG_UTIL.trace
2702 ( 'First is :'||l_txn_first,'INV_CONSUMPTION_ADVICE_PROC'
2703 , 9
2704 );
2705 INV_LOG_UTIL.trace
2706 ( 'Last is :'||l_txn_last||' '||l_txn_ct,'INV_CONSUMPTION_ADVICE_PROC'
2707 , 9
2708 );
2709 END IF;
2710 */
2711
2712 IF (l_debug = 1)
2713 THEN
2714 INV_LOG_UTIL.trace
2715 ( 'Last is :'||p_txn_source_tab.COUNT,'INV_CONSUMPTION_ADVICE_PROC'
2716 , 9
2717 );
2718 END IF;
2719
2720 -- Use the bulk update to summarize the net quantity for
2721 -- the current batch. The net quantity takes into account
2722 -- any corrections that were made to the transaction quantity
2723 -- by the user.
2724
2725 IF p_txn_source_tab.COUNT > 0
2726 THEN
2727
2728 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2729 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2730 SET (mctt.net_qty,mctt.secondary_net_qty) =
2731 (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2732
2733 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2734 --, MTL_MATERIAL_TRANSACTIONS mmt
2735 WHERE --mmt.transaction_id = mct.transaction_id AND
2736 mct.transaction_source_id = p_txn_source_tab(i)
2737 AND mct.inventory_item_id = p_inventory_item_tab(i)
2738 AND mct.organization_id = p_organization_tab(i)
2739 AND mct.owning_organization_id = p_own_org_tab(i)
2740 /* Bug 4969420 Starts here*/
2741 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
2742 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
2743 AND mct.blanket_price = p_transaction_cost_tab(i)
2744 /* Bug 4969421 Ends here*/
2745 /* Bug 11900144. Addition of po_line_id */
2746 AND mct.po_line_id=mctt.po_line_id
2747 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
2748 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
2749 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
2750 AND mct.accrual_account_id = p_accrual_account_tab(i)
2751 AND mct.charge_account_id = p_charge_account_tab(i)
2752 AND mct.variance_account_id = p_variance_account_tab(i)
2753 AND TRUNC(mct.transaction_date) = TRUNC(p_date_tab(i))
2754 AND NVL(mct.rate,-1) = p_rate_tab(i)
2755 AND NVL(mct.rate_type,'##') = p_rate_type_tab(i)
2756 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2757 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2758 /*Bug 4969421 - Ends here */
2759 AND mct.batch_id = p_batch_id
2760 AND mct.consumption_processed_flag IN ('N', 'E'))
2761 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
2762 AND mctt.inventory_item_id = p_inventory_item_tab(i)
2763 AND mctt.organization_id = p_organization_tab(i)
2764 AND mctt.owning_organization_id = p_own_org_tab(i)
2765 AND mctt.transaction_cost = p_transaction_cost_tab(i)
2766 AND mctt.tax_code_id = p_tax_code_tab(i)
2767 AND mctt.recoverable_tax = p_rec_tax_tab(i)
2768 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
2769 AND mctt.accrual_account_id = p_accrual_account_tab(i)
2770 AND mctt.charge_account_id = p_charge_account_tab(i)
2771 AND mctt.variance_account_id = p_variance_account_tab(i)
2772 AND mctt.transaction_date = TRUNC(p_date_tab(i))
2773 AND mctt.rate = p_rate_tab(i)
2774 AND mctt.rate_type = p_rate_type_tab(i)
2775 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2776 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2777 /*Bug 4969421 - Ends here */
2778 AND mctt.global_agreement_flag = 'Y';
2779
2780 FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2781 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2782 SET mctt.net_qty =
2783 (SELECT SUM(mct.net_qty)
2784 FROM MTL_CONSUMPTION_TRANSACTIONS mct
2785 --, MTL_MATERIAL_TRANSACTIONS mmt
2786 WHERE --mmt.transaction_id = mct.transaction_id AND
2787 mct.transaction_source_id = p_txn_source_tab(i)
2788 AND mct.inventory_item_id = p_inventory_item_tab(i)
2789 AND mct.organization_id = p_organization_tab(i)
2790 AND mct.owning_organization_id = p_own_org_tab(i)
2791 /* Bug 4969420 Starts here*/
2792 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
2793 --AND mmt.transaction_cost = p_transaction_cost_tab(i)
2794 AND mct.blanket_price = p_transaction_cost_tab(i)
2795 /* Bug 4969421 Ends here*/
2796 /* Bug 11900144. Addition of po_line_id */
2797 AND mct.po_line_id=mctt.po_line_id
2798 AND NVL(mct.tax_code_id,-1) = p_tax_code_tab(i)
2799 AND NVL(mct.recoverable_tax,0) = p_rec_tax_tab(i)
2800 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_tab(i)
2801 AND mct.accrual_account_id = p_accrual_account_tab(i)
2802 AND mct.charge_account_id = p_charge_account_tab(i)
2803 AND mct.variance_account_id = p_variance_account_tab(i)
2804 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2805 AND NVL(mct.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2806 /*Bug 4969421 - Ends here */
2807 AND mct.batch_id = p_batch_id
2808 AND mct.consumption_processed_flag IN ('N', 'E'))
2809 WHERE mctt.transaction_source_id = p_txn_source_tab(i)
2813 AND mctt.transaction_cost = p_transaction_cost_tab(i)-- mctt.transaction_cost is the blanket_price from MCT
2810 AND mctt.inventory_item_id = p_inventory_item_tab(i)
2811 AND mctt.organization_id = p_organization_tab(i)
2812 AND mctt.owning_organization_id = p_own_org_tab(i)
2814 AND mctt.tax_code_id = p_tax_code_tab(i)
2815 AND mctt.recoverable_tax = p_rec_tax_tab(i)
2816 AND mctt.non_recoverable_tax = p_non_rec_tax_tab(i)
2817 /* Bug 4969421 - Starts here - new check included for tax recovery rate */
2818 AND NVL(mctt.tax_recovery_rate,0) =NVL(p_tax_rec_rate_tab(i),0)
2819 /*Bug 4969421 - Ends here */
2820 AND mctt.accrual_account_id = p_accrual_account_tab(i)
2821 AND mctt.charge_account_id = p_charge_account_tab(i)
2822 AND mctt.variance_account_id = p_variance_account_tab(i)
2823 AND mctt.global_agreement_flag = 'N';
2824 ELSE
2825 IF (l_debug = 1)
2826 THEN
2827 INV_LOG_UTIL.trace
2828 ( '<< Load Summarized Quantity null index','INV_CONSUMPTION_ADVICE_PROC' , 9
2829 );
2830 END IF;
2831 END IF;
2832
2833 IF (l_debug = 1)
2834 THEN
2835 INV_LOG_UTIL.trace
2836 ( '<< Load Summarized Quantity_prf','INV_CONSUMPTION_ADVICE_PROC'
2837 , 9
2838 );
2839 END IF;
2840
2841 EXCEPTION
2842
2843 WHEN OTHERS THEN
2844 IF (l_debug = 1) THEN
2845 INV_LOG_UTIL.trace
2846 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2847 , 9
2848 );
2849 END IF;
2850
2851 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_LD_SUM');
2852 FND_MSG_PUB.ADD;
2853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2854
2855 END load_summarized_quantity_prf;
2856
2857 --========================================================================
2858 -- PROCEDURE : Delete Record PRIVATE
2859 -- COMMENT : If the billing date for the current asl entry
2860 -- : has not elapsed yet then the associated
2861 -- : change of ownership transactions held in
2862 -- : MTL_CONSUMPTION_TRANSACTIONS should not be
2863 -- : processed yet. The current record slipped
2864 -- : into this loop for that reason and should
2865 -- : therefore be deleted from the current batch
2866
2867 --=========================================================================
2868 PROCEDURE delete_record
2869 ( p_txn_source_id IN NUMBER
2870 , p_inventory_item_id IN NUMBER
2871 , p_organization_id IN NUMBER
2872 , p_own_org_id IN NUMBER
2873 , p_price IN NUMBER
2874 , p_tax_code_id IN NUMBER
2875 , p_rec_tax_id IN NUMBER
2876 , p_non_rec_tax_id IN NUMBER
2877 , p_accrual_account_id IN NUMBER
2878 , p_charge_account_id IN NUMBER
2879 , p_variance_account_id IN NUMBER
2880 , p_date IN DATE
2881 , p_rate IN NUMBER
2882 , p_rate_type IN VARCHAR
2883 )
2884 IS
2885 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2886 BEGIN
2887
2888 IF (l_debug = 1)
2889 THEN
2890 INV_LOG_UTIL.trace
2891 ( '>> Delete Record','INV_CONSUMPTION_ADVICE_PROC'
2892 , 9
2893 );
2894 END IF;
2895
2896 DELETE FROM mtl_consumption_txn_temp mctt
2897 WHERE mctt.transaction_source_id = p_txn_source_id
2898 AND mctt.inventory_item_id = p_inventory_item_id
2899 AND mctt.organization_id = p_organization_id
2900 AND mctt.owning_organization_id = p_own_org_id
2901 AND mctt.transaction_cost = p_price
2902 AND mctt.tax_code_id = p_tax_code_id
2903 AND mctt.recoverable_tax = p_rec_tax_id
2904 AND mctt.non_recoverable_tax = p_non_rec_tax_id
2905 AND mctt.accrual_account_id = p_accrual_account_id
2906 AND mctt.charge_account_id = p_charge_account_id
2907 AND mctt.variance_account_id = p_variance_account_id
2908 AND mctt.transaction_date = TRUNC(p_date)
2909 AND mctt.rate = p_rate
2910 AND mctt.rate_type = p_rate_type;
2911
2912 -- Bug 5092489. Query modified for better performance
2913 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
2914 SET mct.batch_id = NULL
2915 WHERE mct.transaction_source_id = p_txn_source_id
2916 AND mct.inventory_item_id = p_inventory_item_id
2917 AND mct.organization_id = p_organization_id
2918 AND mct.owning_organization_id = p_own_org_id
2919 AND mct.accrual_account_id = p_accrual_account_id
2920 /* Bug 4969420 Starts here*/
2921 /* We use the blanket_price (from MCT) instead of the transaction_cost from MMT */
2922 --AND mmt.transaction_cost = p_price
2923 -- AND mct.blanket_price = p_price
2924 /* Bug 4969420 Ends here*/
2925 AND TRUNC(mct.transaction_date) = TRUNC(p_date)
2926 AND NVL(mct.tax_code_id,-1) = p_tax_code_id
2927 AND NVL(mct.recoverable_tax,0) = p_rec_tax_id
2928 AND NVL(mct.non_recoverable_tax,0) = p_non_rec_tax_id
2929 AND mct.charge_account_id = p_charge_account_id
2930 AND mct.variance_account_id = p_variance_account_id
2931 AND NVL(mct.rate,-1) = p_rate
2932 AND NVL(mct.rate_type,'##') = p_rate_type
2933 AND mct.consumption_processed_flag IN ('N', 'E');
2934
2935 IF (l_debug = 1)
2936 THEN
2937 INV_LOG_UTIL.trace
2938 ( '<< Delete Record','INV_CONSUMPTION_ADVICE_PROC'
2939 , 9
2940 );
2941 END IF;
2942
2943 EXCEPTION
2944
2945 WHEN OTHERS THEN
2946 IF (l_debug = 1) THEN
2947 INV_LOG_UTIL.trace
2948 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
2949 , 9
2950 );
2951 END IF;
2952
2953 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_DEL_REC');
2954 FND_MSG_PUB.ADD;
2955 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2956
2960 -- PROCEDURE : Log_Initialize PRIVATE
2957 END delete_record;
2958
2959 --========================================================================
2961 -- COMMENT : Initializes the log facility. It should be called from
2962 -- the top level procedure of each concurrent program
2963 --========================================================================
2964 PROCEDURE Log_Initialize
2965 IS
2966 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2967 BEGIN
2968
2969 IF (l_debug = 1)
2970 THEN
2971 INV_LOG_UTIL.trace
2972 ( '>> Log Initialize','INV_CONSUMPTION_ADVICE_PROC'
2973 , 9
2974 );
2975 END IF;
2976
2977 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
2978 IF g_log_level IS NULL THEN
2979 g_log_mode := 'OFF';
2980 ELSE
2981 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
2982 g_log_mode := 'SRS';
2983 ELSE
2984 g_log_mode := 'SQL';
2985 END IF;
2986 END IF;
2987
2988 IF (l_debug = 1)
2989 THEN
2990 INV_LOG_UTIL.trace
2991 ( '<< Log Initialize','INV_CONSUMPTION_ADVICE_PROC'
2992 , 9
2993 );
2994 END IF;
2995
2996 END Log_Initialize;
2997
2998 --========================================================================
2999 -- PROCEDURE : Log PRIVATE
3000 -- PARAMETERS: p_level IN 2
3001 -- -- G_LOG_PROCEDURE
3002 -- p_msg IN message to be print on the log
3003 -- file
3004 -- COMMENT : Add an entry to the log
3005 --=======================================================================--
3006 PROCEDURE LOG
3007 ( p_priority IN NUMBER
3008 , p_msg IN VARCHAR2
3009 )
3010 IS
3011 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3012 BEGIN
3013
3014 IF (l_debug = 1)
3015 THEN
3016 INV_LOG_UTIL.trace
3017 ( '>> Log','INV_CONSUMPTION_ADVICE_PROC'
3018 , 9
3019 );
3020 END IF;
3021
3022 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
3023 THEN
3024 IF g_log_mode = 'SQL'
3025 THEN
3026 -- SQL*Plus session: uncomment the next line during unit test
3027 -- DBMS_OUTPUT.put_line(p_msg);
3028 NULL;
3029 ELSE
3030 -- Concurrent request
3031 FND_FILE.put_line
3032 ( FND_FILE.LOG
3033 , p_msg
3034 );
3035 END IF;
3036 END IF;
3037
3038 IF (l_debug = 1)
3039 THEN
3040 INV_LOG_UTIL.trace
3041 ( '<< Log','INV_CONSUMPTION_ADVICE_PROC'
3042 , 9
3043 );
3044 END IF;
3045
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 NULL;
3049 END LOG;
3050
3051 --========================================================================
3052 -- FUNCTION : Generate_Log PRIVATE
3053 -- PARAMETERS: None
3054 -- RETURNS : NUMBER
3055 -- COMMENT : This procedure is called when there are errors
3056 -- : in creating the consumption advice.
3057 -- : It populates a log file with the triansaction ids
3058 -- : of records that have failed. The user can view
3059 -- : this information through the log of an application request
3060 --=========================================================================
3061 PROCEDURE generate_log
3062 ( p_batch_id IN NUMBER
3063 )
3064 IS
3065
3066 --=================
3067 -- CURSORS
3068 --=================
3069
3070 CURSOR con_ad_err_cur IS
3071 SELECT mct.transaction_id
3072 , mct.error_code
3073 FROM MTL_CONSUMPTION_TRANSACTIONS mct
3074 WHERE mct.consumption_processed_flag = 'E'
3075 AND mct.batch_id = p_batch_id;
3076
3077 --=================
3078 -- VARIABLES
3079 --=================
3080
3081 l_error_code VARCHAR2(10);
3082 l_txn_id NUMBER;
3083 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3084
3085
3086 BEGIN
3087
3088 IF (l_debug = 1)
3089 THEN
3090 INV_LOG_UTIL.trace
3091 ( '>> Generate Log','INV_CONSUMPTION_ADVICE_PROC'
3092 , 9
3093 );
3094 END IF;
3095
3096 INV_CONSUMPTION_ADVICE_PROC.LOG
3097 ( INV_CONSUMPTION_ADVICE_PROC.G_LOG_PROCEDURE
3098 , '< Consumption Advice Error'
3099 );
3100
3101 OPEN con_ad_err_cur;
3102 LOOP
3103 FETCH con_ad_err_cur
3104 INTO
3105 l_txn_id
3106 ,l_error_code;
3107
3108 IF con_ad_err_cur%NOTFOUND THEN
3109 EXIT;
3110 END IF;
3111
3112 END LOOP;
3113
3114 CLOSE con_ad_err_cur;
3115
3116 INV_CONSUMPTION_ADVICE_PROC.LOG
3117 ( INV_CONSUMPTION_ADVICE_PROC.G_LOG_PROCEDURE
3118 , '> Consumption Advice Error'
3119 );
3120
3121 IF (l_debug = 1)
3122 THEN
3123 INV_LOG_UTIL.trace
3124 ( '<< Generate Log','INV_CONSUMPTION_ADVICE_PROC'
3125 , 9
3126 );
3127 END IF;
3128
3129
3130 END generate_log;
3131
3132
3133 --Added procedure populate_po_line_id as part of bug 11900144
3134 PROCEDURE populate_po_line_id
3135 IS
3136
3137 --declare the variable types for bulk collect
3138 type t_transaction_id IS TABLE OF mTL_material_TRANSACTIONS.transaction_id%type;
3139 type t_transaction_source_id IS TABLE OF mTL_material_TRANSACTIONS.transaction_source_id%type;
3143 type t_organization_id IS TABLE OF mTL_material_TRANSACTIONS.organization_id%type;
3140 type t_inventory_item_id IS TABLE OF mTL_material_TRANSACTIONS.inventory_item_id%type;
3141 type t_item_revision IS TABLE OF mTL_material_TRANSACTIONS.revision%type;
3142 type t_OWNING_ORGANIZATION_ID IS TABLE OF mTL_material_TRANSACTIONS.OWNING_ORGANIZATION_ID%type;
3144 type t_transaction_date IS TABLE OF mTL_material_TRANSACTIONS.transaction_date%type;
3145
3146 --declare the local variables
3147 l_transaction_id t_transaction_id;
3148 l_transaction_source_id t_transaction_source_id;
3149 l_inventory_item_id t_inventory_item_id;
3150 l_item_revision t_item_revision;
3151 l_vendor_site_id t_OWNING_ORGANIZATION_ID;
3152 l_organization_id t_organization_id;
3153 l_transaction_date t_transaction_date;
3154 l_header_id po_lines_all.po_header_id%TYPE;
3155 l_document_line_id po_lines_all.po_line_id%TYPE;
3156 l_global_flag varchar(2);
3157
3158 CURSOR csr_null_po_line_id IS
3159 SELECT mmt.transaction_id ,
3160 mmt.TRANSACTION_SOURCE_ID ,
3161 mmt.inventory_item_id ,
3162 mmt.REVISION ,
3163 mmt.OWNING_ORGANIZATION_ID ,
3164 mmt.ORGANIZATION_ID ,
3165 mmt.TRANSACTION_DATE
3166 FROM MTL_CONSUMPTION_TRANSACTIONS mct ,
3167 MTL_MATERIAL_TRANSACTIONS mmt
3168 WHERE mmt.transaction_id = mct.transaction_id
3169 AND consumption_processed_flag IN ( 'N',
3170 'E')
3171 AND PO_LINE_ID IS NULL ;
3172
3173 BEGIN
3174 INV_LOG_UTIL.trace('Starting datafix for missing po_line_id','INV_CONSUMPTION_ADVICE_PROC',9);
3175 OPEN csr_null_po_line_id ;
3176 LOOP -- loop through each bulk of 100 records
3177 FETCH csr_null_po_line_id
3178 BULK COLLECT
3179 INTO l_transaction_id,
3180 l_transaction_source_id,
3181 l_inventory_item_id,
3182 l_item_revision,
3183 l_vendor_site_id,
3184 l_organization_id,
3185 l_transaction_date
3186 LIMIT 100;
3187 EXIT WHEN l_transaction_id.Count = 0;
3188
3189 INV_LOG_UTIL.trace('there are '||l_inventory_item_id.Count ||' more unprocessed record(s) with missing po_line_id','INV_CONSUMPTION_ADVICE_PROC',9);
3190 IF(l_inventory_item_id.Count >0) THEN
3191
3192 FOR i IN l_inventory_item_id.first .. l_inventory_item_id.last
3193 LOOP -- loop through each transaction in the bulk
3194 INV_LOG_UTIL.trace(l_transaction_source_id(i)
3195 || ' , '
3196 || l_inventory_item_id(i)
3197 || ' , '
3198 || l_item_revision(i)
3199 || ' , '
3200 || l_vendor_site_id(i)
3201 || ' , '
3202 || l_organization_id(i)
3203 || ' , '
3204 || l_transaction_date(i),'INV_CONSUMPTION_ADVICE_PROC',9 );
3205
3206 INV_PO_THIRD_PARTY_STOCK_MDTR.Get_Blanket_Number
3207 ( p_inventory_item_id => l_inventory_item_id(i)
3208 , p_item_revision => l_item_revision(i)
3209 , p_vendor_site_id => l_vendor_site_id(i)
3210 , p_organization_id => l_organization_id(i)
3211 , p_transaction_date =>trunc(l_transaction_date(i))
3212 , x_document_header_id => l_header_id
3213 , x_document_line_id => l_document_line_id
3214 , x_global_flag => l_global_flag
3215 );
3216
3217 INV_LOG_UTIL.trace( l_header_id
3218 || ' , '
3219 || l_document_line_id
3220 || ' , '
3221 || l_global_flag
3222 || ' , '
3223 || l_transaction_id(i),'INV_CONSUMPTION_ADVICE_PROC',9
3224 );
3225 -- start of bug 9539634
3226 IF l_transaction_source_id(i) <> l_header_id THEN
3227 -- if the derived po_line_id does not belong to the blanket header of the transaction
3228 l_document_line_id := null;
3229 INV_LOG_UTIL.trace('fetched blanket header is not equal to that of the transaction','INV_CONSUMPTION_ADVICE_PROC',9);
3230 BEGIN
3231 -- Since now the current eligible blanket has changed to newer one
3232 -- fetching the po_line of the blanket which was associated with the transaction.
3233 -- If there was more than one line for the same item in the old blanket
3234 -- we take the 1st one only.In case of exception we set it back to null.
3235 -- If po_line_id remains null , it will not be processed at all and manual datafix
3236 -- needs to be applied to resolve this extreemly corner case.
3237 select po_line_id
3238 into l_document_line_id
3239 from po_lines_All
3240 where po_header_id = l_transaction_source_id(i)
3241 and item_id = l_inventory_item_id(i)
3242 and rownum = 1 ;
3243 INV_LOG_UTIL.trace('Derived po_line_id is '||l_document_line_id,'INV_CONSUMPTION_ADVICE_PROC',9);
3244 EXCEPTION
3245 WHEN OTHERS THEN
3249 -- End of bug 9539634
3246 INV_LOG_UTIL.trace('Unable to derive po_line_id , setting back to null ','INV_CONSUMPTION_ADVICE_PROC',9);
3247 END;
3248 END IF;
3250 /* update the po line id and make it ready to be processed */
3251 UPDATE mtl_consumption_transactions mct
3252 SET batch_id = NULL,
3253 po_line_id =l_document_line_id
3254 WHERE consumption_processed_flag in ('N','E')
3255 AND transaction_id = l_transaction_id(i)
3256 AND po_line_id IS NULL;
3257
3258 IF(SQL%ROWCOUNT = 1) THEN
3259 INV_LOG_UTIL.trace('Updated the Trx id '||l_transaction_id(i)||' with po_line_id '||l_document_line_id ,'INV_CONSUMPTION_ADVICE_PROC',9);
3260 END IF;
3261
3262 END LOOP; -- loop through each transaction in the bulk
3263 END IF;
3264 END LOOP; -- loop through each bul of 100 records
3265 EXCEPTION WHEN OTHERS THEN
3266 INV_LOG_UTIL.trace('Something went wrong with populate_po_line_id still proceeding with consumption advice','INV_CONSUMPTION_ADVICE_PROC',9);
3267 END populate_po_line_id;
3268
3269
3270 --========================================================================
3271 -- PROCEDURE : Consumption_Txn_Worker PRIVATE
3272 -- COMMENT : This procedure will copy all the records of a context batch
3273 -- from MTL_CONSUMPTION_TRANSACTIONS to
3274 -- MTL_CONSUMPTION_TRANSACTIONS_TEMP
3275 -- summarize the net quantity and call the create consumption
3276 -- advice procedure
3277 --=========================================================================
3278 PROCEDURE consumption_txn_worker
3279 ( p_batch_id IN NUMBER
3280 )
3281 IS
3282
3283 --=================
3284 -- CURSORS
3285 --=================
3286
3287 CURSOR cons_temp_csr_type IS
3288 SELECT mctt.transaction_source_id
3289 , mctt.inventory_item_id
3290 , mctt.organization_id
3291 , mctt.owning_organization_id
3292 , mctt.transaction_cost-- This is the blanket_price from MCT Bug 4969421
3293 , mctt.tax_code_id
3294 , mctt.recoverable_tax
3295 , mctt.non_recoverable_tax
3296 , mctt.accrual_account_id
3297 , mctt.charge_account_id
3298 , mctt.variance_account_id
3299 , mctt.rate
3300 , mctt.rate_type
3301 , mctt.transaction_date
3302 , mctt.tax_recovery_rate -- Bug 4969420
3303 FROM MTL_CONSUMPTION_TXN_TEMP mctt
3304 /* bug 5113064 - Start */
3305 /* filter just for the given batch */
3306 WHERE batch_id =p_batch_id ;
3307 /* bug 5113064 - End*/
3308
3309 --=================
3310 -- LOCAL VARIABLES
3311 --=================
3312
3313 l_current_cons_index BINARY_INTEGER := 0;
3314 l_empty_cons_tab g_cons_tbl_type;
3315 l_empty_date_cons_tab g_cons_date_tbl_type;
3316 l_empty_varchar_cons_tab g_cons_varchar_tbl_type;
3317 l_txn_source_tab g_cons_tbl_type;
3318 l_item_tab g_cons_tbl_type;
3319 l_org_tab g_cons_tbl_type;
3320 l_owning_org_tab g_cons_tbl_type;
3321 l_price_tab g_cons_tbl_type;
3322 l_tax_code_tab g_cons_tbl_type;
3323 l_rec_tax_tab g_cons_tbl_type;
3324 l_non_rec_tax_tab g_cons_tbl_type;
3325 l_accrual_account_tab g_cons_tbl_type;
3326 l_charge_account_tab g_cons_tbl_type;
3327 l_variance_account_tab g_cons_tbl_type;
3328 l_rate_tab g_cons_tbl_type;
3329 l_rate_type_tab g_cons_varchar_tbl_type;
3330 l_tax_rec_rate_tab g_cons_tbl_type; -- Bug 4969421
3331 l_date_tab g_cons_date_tbl_type;
3332 -- Bug 5092489. commented becasue not used
3333 --l_last_billing_date DATE;
3334 --l_next_billing_date DATE;
3335 --l_con_bill_cycle NUMBER;
3336 l_bulk_count NUMBER := 0;
3337 l_loop_count NUMBER := 0;
3338 l_vendor_id NUMBER;
3339 l_vendor_site_id NUMBER;
3340 -- Bug 5092489. commented becasue not used
3341 --l_con_from_sup_flag NUMBER;
3342 --l_enable_vmi_flag NUMBER;
3343 l_document_header_id NUMBER;
3344 l_document_line_id NUMBER;
3345 l_bill_date_elapsed NUMBER := 0;
3346 l_batch_id NUMBER;
3347 l_return_status VARCHAR2(24);
3348 l_count NUMBER;
3349 l_asl_id NUMBER;
3350 l_vendor_product_num VARCHAR2(25);
3351 l_purchasing_uom VARCHAR2(25);
3352 l_organization_id NUMBER := 0;
3353 l_current_txn_source_id NUMBER := 0;
3354 l_inventory_item_id NUMBER := 0;
3355 l_debug NUMBER :=
3356 NVL(FND_PROFILE.VALUE
3357 ('INV_DEBUG_TRACE'),0);
3358
3359
3360 BEGIN
3361
3362 IF (l_debug = 1)
3363 THEN
3364 INV_LOG_UTIL.trace
3365 ( '>> Consumption Txn Worker(p_batch_id)'||p_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
3366 , 9
3367 );
3368 END IF;
3369
3370 -- Call procedure to load MTL_CONSUMPTION_TXNS_TEMP
3371 -- from MTL_CONSUMPTION_TRANSACTIONS
3372 -- with records that belong to the specified batch
3373
3374 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
3375 THEN
3379 ,p_vendor_site_id => NULL
3376 INV_CONSUMPTION_ADVICE_PROC.load_combination_prf
3377 (p_batch_id => p_batch_id
3378 ,p_vendor_id => NULL
3380 ,p_inventory_item_id => NULL
3381 ,p_organization_id => NULL);
3382 ELSE
3383 INV_CONSUMPTION_ADVICE_PROC.load_combination
3384 (p_batch_id => p_batch_id
3385 ,p_vendor_id => NULL
3386 ,p_vendor_site_id => NULL
3387 ,p_inventory_item_id => NULL
3388 ,p_organization_id => NULL);
3389
3390 END IF;
3391
3392 -- open cursor
3393
3394 IF NOT cons_temp_csr_type%ISOPEN
3395 THEN
3396 OPEN cons_temp_csr_type;
3397 END IF;
3398
3399
3400 LOOP
3401
3402 l_txn_source_tab := l_empty_cons_tab;
3403 l_item_tab := l_empty_cons_tab;
3404 l_org_tab := l_empty_cons_tab;
3405 l_owning_org_tab := l_empty_cons_tab;
3406 l_price_tab := l_empty_cons_tab;
3407 l_tax_code_tab := l_empty_cons_tab;
3408 l_rec_tax_tab := l_empty_cons_tab;
3409 l_non_rec_tax_tab := l_empty_cons_tab;
3410 l_accrual_account_tab := l_empty_cons_tab;
3411 l_charge_account_tab := l_empty_cons_tab;
3412 l_variance_account_tab := l_empty_cons_tab;
3413 l_rate_tab := l_empty_cons_tab;
3414 l_rate_type_tab := l_empty_varchar_cons_tab;
3415 l_date_tab := l_empty_date_cons_tab;
3416 l_batch_id := p_batch_id;
3417 l_tax_rec_rate_tab := l_empty_cons_tab; -- Bug 4969421
3418
3419 -- Bulk population of pl/sql table
3420
3421 FETCH cons_temp_csr_type
3422 BULK COLLECT INTO l_txn_source_tab
3423 ,l_item_tab
3424 ,l_org_tab
3425 ,l_owning_org_tab
3426 ,l_price_tab
3427 ,l_tax_code_tab
3428 ,l_rec_tax_tab
3429 ,l_non_rec_tax_tab
3430 ,l_accrual_account_tab
3431 ,l_charge_account_tab
3432 ,l_variance_account_tab
3433 ,l_rate_tab
3434 ,l_rate_type_tab
3435 ,l_date_tab
3436 ,l_tax_rec_rate_tab -- Bug 4969420
3437 LIMIT 1000;
3438
3439 /* But 5006151 - Start */
3440 /* When the number of records in the MTL_CONSUMPTION_TXN_TEMP table is a
3441 multiple of 1000, the worker does not exit correctly with the check
3442 'EXIT WHEN l_loop_count < 1000' as l_loop_count equals '1000'.
3443 The following check would ensure that the worker exits correctly */
3444 EXIT WHEN l_bulk_count = cons_temp_csr_type%ROWCOUNT;
3445 /* Bug 5006151 - End */
3446
3447 -- Summarize the net quantity of all records in
3448 -- MTL_CONSUMPTION_TRANSACTIONS associated with
3449 -- records in MTL_CONSUMPTION_TXNS_TEMP.
3450 -- Record the result in MTL_CONSUMPTION_TXNS_TEMP.
3451
3452 /* BUg 5092489. Following IF block is commented becasue quantity is populated during insert
3453 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
3454 THEN
3455 INV_CONSUMPTION_ADVICE_PROC.load_summarized_quantity_prf
3456 (p_txn_source_tab => l_txn_source_tab
3457 ,p_inventory_item_tab => l_item_tab
3458 ,p_organization_tab => l_org_tab
3459 ,p_own_org_tab => l_owning_org_tab
3460 ,p_transaction_cost_tab => l_price_tab -- blanket_price from MCT Bug 4969421
3461 ,p_tax_code_tab => l_tax_code_tab
3462 ,p_rec_tax_tab => l_rec_tax_tab
3463 ,p_non_rec_tax_tab => l_non_rec_tax_tab
3464 ,p_accrual_account_tab => l_accrual_account_tab
3465 ,p_charge_account_tab => l_charge_account_tab
3466 ,p_variance_account_tab => l_variance_account_tab
3467 ,p_rate_tab => l_rate_tab
3468 ,p_rate_type_tab => l_rate_type_tab
3469 ,p_date_tab => l_date_tab
3470 ,p_batch_id => l_batch_id
3471 ,p_tax_rec_rate_tab => l_tax_rec_rate_tab); -- Bug 4969421
3472 ELSE
3473 INV_CONSUMPTION_ADVICE_PROC.load_summarized_quantity
3474 (p_txn_source_tab => l_txn_source_tab
3475 ,p_inventory_item_tab => l_item_tab
3476 ,p_organization_tab => l_org_tab
3477 ,p_own_org_tab => l_owning_org_tab
3478 ,p_transaction_cost_tab => l_price_tab -- blanket_price from MCT Bug 4969421
3479 ,p_tax_code_tab => l_tax_code_tab
3480 ,p_rec_tax_tab => l_rec_tax_tab
3481 ,p_non_rec_tax_tab => l_non_rec_tax_tab
3482 ,p_accrual_account_tab => l_accrual_account_tab
3483 ,p_charge_account_tab => l_charge_account_tab
3484 ,p_variance_account_tab => l_variance_account_tab
3485 ,p_rate_tab => l_rate_tab
3486 ,p_rate_type_tab => l_rate_type_tab
3487 ,p_date_tab => l_date_tab
3488 ,p_batch_id => l_batch_id
3489 ,p_tax_rec_rate_tab => l_tax_rec_rate_tab); -- Bug 4969421
3490 END IF;
3491 */
3492
3493 -- The following loop removes records from the temp table
3494 -- if they are not candidates to populate the ensuing
3495 -- Consumption Advice document.
3496 -- The criteria for this decision being the
3497 -- state of their billing cycle
3498
3499 l_loop_count := cons_temp_csr_type%ROWCOUNT - l_bulk_count;
3500
3501 IF (l_debug = 1)
3502 THEN
3503 INV_LOG_UTIL.trace
3504 ( 'Consumption Txn Worker(l_loop_count)'||l_loop_count,'INV_CONSUMPTION_ADVICE_PROC'
3505 , 9
3506 );
3507 END IF;
3508
3512 IF l_txn_source_tab(i) <> l_current_txn_source_id OR
3509 FOR i IN 1..l_loop_count
3510 LOOP
3511
3513 l_org_tab(i) <> l_organization_id OR
3514 l_item_tab(i) <> l_inventory_item_id THEN
3515
3516 l_current_txn_source_id := l_txn_source_tab(i);
3517 l_organization_id := l_org_tab(i);
3518 l_inventory_item_id := l_item_tab(i);
3519
3520 IF (l_debug = 1)
3521 THEN
3522 INV_LOG_UTIL.trace
3523 ( 'Consumption Worker(l_current_txn_source_id)'||l_current_txn_source_id,'INV_CONSUMPTION_ADVICE_PROC'
3524 , 9
3525 );
3526 INV_LOG_UTIL.trace
3527 ( 'Cons Worker(l_organization_id)'||l_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
3528 , 9
3529 );
3530 INV_LOG_UTIL.trace
3531 ( 'Cons Worker(l_inventory_item_id)'||l_inventory_item_id,'INV_CONSUMPTION_ADVICE_PROC'
3532 , 9
3533 );
3534 END IF;
3535
3536 -- Find the vendor location information
3537 -- Bug 5092489. Query modified
3538 SELECT pvsa.vendor_id
3539 INTO l_vendor_id
3540 FROM po_vendor_sites_all pvsa
3541 WHERE pvsa.vendor_site_id = l_owning_org_tab(i)
3542 AND ROWNUM = 1;
3543
3544 IF (l_debug = 1)
3545 THEN
3546 INV_LOG_UTIL.trace
3547 ( 'Consumption Txn Worker(l_vendor_id)'||l_vendor_id,'INV_CONSUMPTION_ADVICE_PROC'
3548 , 9
3549 );
3550 END IF;
3551
3552 -- Find the billing cycle for each record in the temp table
3553 -- if no asl_id is found then the org id is updated to -1
3554 -- however in such cases we still process the transaction
3555 -- therefore the following line is required
3556
3557 INV_PO_THIRD_PARTY_STOCK_MDTR.get_asl_info
3558 (p_item_id => l_item_tab(i)
3559 ,p_vendor_id => l_vendor_id
3560 ,p_vendor_site_id => l_owning_org_tab(i)
3561 ,p_using_organization_id => l_organization_id
3562 ,x_asl_id => l_asl_id
3563 ,x_vendor_product_num => l_vendor_product_num
3564 ,x_purchasing_uom => l_purchasing_uom);
3565
3566 IF (l_debug = 1)
3567 THEN
3568 INV_LOG_UTIL.trace
3569 ( 'Consumption Txn Worker(l_asl_id)'||l_asl_id,'INV_CONSUMPTION_ADVICE_PROC'
3570 , 9
3571 );
3572 END IF;
3573
3574 -- Update the temp table with the currency of the blanket
3575
3576 UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3577 SET mctt.currency_code = (SELECT poa.currency_code
3578 FROM po_headers_all poa
3579 WHERE poa.po_header_id
3580 = l_txn_source_tab(i))
3581 ,mctt.asl_id = l_asl_id
3582 WHERE mctt.transaction_source_id = l_txn_source_tab(i)
3583 AND mctt.organization_id = l_org_tab(i)
3584 AND mctt.inventory_item_id = l_item_tab(i);
3585
3586 END IF;
3587
3588 -- Even if no ASL_ID is returned then continue and process the record
3589
3590 IF l_asl_id IS NULL
3591 THEN
3592
3593 l_bill_date_elapsed := 0;
3594
3595 ELSE
3596
3597 INV_PO_THIRD_PARTY_STOCK_MDTR.get_elapsed_info
3598 (p_org_id => l_org_tab(i)
3599 ,p_asl_id => l_asl_id
3600 ,x_bill_date_elapsed => l_bill_date_elapsed);
3601
3602 IF (l_debug = 1)
3603 THEN
3604 INV_LOG_UTIL.trace
3605 ( 'Consumption Txn Worker(l_elapsed)'||l_bill_date_elapsed,'INV_CONSUMPTION_ADVICE_PROC'
3606 , 9
3607 );
3608 INV_LOG_UTIL.trace
3609 ( 'Consumption Txn Worker(l_org_tab)'||l_org_tab(i),'INV_CONSUMPTION_ADVICE_PROC'
3610 , 9
3611 );
3612 END IF;
3613
3614 END IF;
3615
3616 -- If the billing cycle has not elapsed yet then
3617 -- delete that record from the temp table
3618 -- This will have the effect of deferring the processing
3619 -- of the record until it's billing cycle has elapsed
3620
3621 IF l_bill_date_elapsed = 1 THEN
3622
3623 -- delete the record from the temporary table
3624 IF (l_debug = 1)
3625 THEN
3626 INV_LOG_UTIL.trace
3627 ( 'Consumption Txn Worker:Inside delete record','INV_CONSUMPTION_ADVICE_PROC'
3628 , 9
3629 );
3630 END IF;
3631
3632 INV_CONSUMPTION_ADVICE_PROC.delete_record
3633 (p_txn_source_id => l_txn_source_tab(i)
3634 ,p_inventory_item_id => l_item_tab(i)
3635 ,p_organization_id => l_org_tab(i)
3636 ,p_own_org_id => l_owning_org_tab(i)
3637 ,p_price => l_price_tab(i)
3638 ,p_tax_code_id => l_tax_code_tab(i)
3639 ,p_rec_tax_id => l_rec_tax_tab(i)
3640 ,p_non_rec_tax_id => l_non_rec_tax_tab(i)
3641 ,p_accrual_account_id => l_accrual_account_tab(i)
3642 ,p_charge_account_id => l_charge_account_tab(i)
3643 ,p_variance_account_id => l_variance_account_tab(i)
3644 ,p_rate => l_rate_tab(i)
3645 ,p_rate_type => l_rate_type_tab(i)
3646 ,p_date => l_date_tab(i));
3647
3648 END IF;
3649
3650 END LOOP;
3651 l_bulk_count := cons_temp_csr_type%ROWCOUNT;
3652
3653 IF (l_debug = 1)
3654 THEN
3655 INV_LOG_UTIL.trace
3659 END IF;
3656 ( 'Consumption Txn Worker(l_bulk_count)'||l_bulk_count,'INV_CONSUMPTION_ADVICE_PROC'
3657 , 9
3658 );
3660 EXIT WHEN l_loop_count < 1000;
3661
3662 END LOOP;
3663
3664 CLOSE cons_temp_csr_type;
3665
3666 -- Call the Load Interface Tables procedure to
3667 -- Load the PO interface tables and call the autocreate procedure
3668
3669 /* Bug 4599072 - Start */
3670 /* The Load_Interface_Tables_prf procedure will no longer be used */
3671
3672 INV_CONSUMPTION_ADVICE_PROC.load_interface_tables
3673 (p_batch_id => l_batch_id
3674 ,x_return_status => l_return_status);
3675 /* Bug 4599072 - Start */
3676 /* bug 5113064 - Start */
3677 /* clean up invalid records in MCT */
3678 UPDATE mtl_consumption_transactions
3679 SET batch_id = NULL
3680 WHERE batch_id = p_batch_id
3681 AND consumption_processed_flag IN ('N','E')
3682 AND consumption_po_header_id IS NULL
3683 AND consumption_release_id IS NULL;
3684 /* clean up MCT - end */
3685 /* bug 5113064 - Start */
3686
3687 IF l_return_status <> 'S' THEN
3688
3689 INV_CONSUMPTION_ADVICE_PROC.generate_log(l_batch_id);
3690
3691 END IF;
3692
3693 IF (l_debug = 1)
3694 THEN
3695 INV_LOG_UTIL.trace
3696 ( '<< Consumption Txn Worker','INV_CONSUMPTION_ADVICE_PROC'
3697 , 9
3698 );
3699 END IF;
3700
3701 END consumption_txn_worker;
3702
3703 --========================================================================
3704 -- FUNCTION : Generate_Batch_Id PRIVATE
3705 -- PARAMETERS: None
3706 -- RETURNS : NUMBER
3707 -- COMMENT : This function returns the next batch id to be assigned to
3708 -- the records in MTL_CONSUMPTION_TRANSACTIONS
3709 --=========================================================================
3710 FUNCTION generate_batch_id
3711 RETURN NUMBER
3712 IS
3713 l_batch_id NUMBER;
3714 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3715 BEGIN
3716
3717 IF (l_debug = 1)
3718 THEN
3719 INV_LOG_UTIL.trace
3720 ( '>> Generate Batch Id','INV_CONSUMPTION_ADVICE_PROC'
3721 , 9
3722 );
3723 END IF;
3724
3725 -- Generate sequence that will become the new batch id
3726
3727 /* Bug 11822877. Changed the sequence value from mtl_third_party_cp_s
3728 to be in synchronization with PO headers sequence */
3729 SELECT po_headers_interface_s.nextval
3730 INTO l_batch_id
3731 FROM dual;
3732
3733 RETURN l_batch_id;
3734
3735 IF (l_debug = 1)
3736 THEN
3737 INV_LOG_UTIL.trace
3738 ( '<< Generate Batch Id','INV_CONSUMPTION_ADVICE_PROC'
3739 , 9
3740 );
3741 END IF;
3742
3743 END generate_batch_id;
3744
3745 --========================================================================
3746 -- FUNCTION : Has_Worker_Completed PRIVATE
3747 -- PARAMETERS: p_request_id IN NUMBER
3748 -- RETURNS : BOOLEAN
3749 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
3750 -- has completed; FALSE otherwise
3751 --=========================================================================
3752 FUNCTION has_worker_completed
3753 ( p_request_id IN NUMBER
3754 )
3755 RETURN BOOLEAN
3756 IS
3757 l_count NUMBER;
3758 l_result BOOLEAN;
3759 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3760 BEGIN
3761
3762 IF (l_debug = 1)
3763 THEN
3764 INV_LOG_UTIL.trace
3765 ( '>> Has Worker Completed','INV_CONSUMPTION_ADVICE_PROC'
3766 , 9
3767 );
3768 END IF;
3769
3770 SELECT COUNT(*)
3771 INTO l_count
3772 FROM fnd_concurrent_requests
3773 WHERE request_id = p_request_id
3774 AND phase_code = 'C';
3775
3776 IF l_count = 1 THEN
3777 l_result := TRUE;
3778 ELSE
3779 l_result := FALSE;
3780 END IF;
3781
3782 RETURN l_result;
3783
3784 IF (l_debug = 1)
3785 THEN
3786 INV_LOG_UTIL.trace
3787 ( '<< Has Worker Completed','INV_CONSUMPTION_ADVICE_PROC'
3788 , 9
3789 );
3790 END IF;
3791
3792 END has_worker_completed;
3793
3794 --========================================================================
3795 -- PROCEDURE : Wait_For_Worker PRIVATE
3796 -- PARAMETERS: p_workers IN workers' request ID
3797 -- x_worker_idx OUT position in p_workers of the
3798 -- completed worked
3799 -- COMMENT : This procedure polls the submitted workers and suspend
3800 -- the program till the completion of one of them; it returns
3801 -- the completed worker through x_worker_idx
3802 --=========================================================================
3803 PROCEDURE wait_for_worker
3804 ( p_workers IN g_request_tbl_type
3805 , x_worker_idx OUT NOCOPY BINARY_INTEGER
3806 )
3807 IS
3808 l_done BOOLEAN;
3809 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3810 BEGIN
3811
3812 IF (l_debug = 1)
3813 THEN
3814 INV_LOG_UTIL.trace
3815 ( '>> Wait For Worker','INV_CONSUMPTION_ADVICE_PROC'
3816 , 9
3817 );
3818 END IF;
3819
3820 l_done := FALSE;
3821
3822 WHILE (NOT l_done) LOOP
3823
3824 FOR l_Idx IN 1..p_workers.COUNT LOOP
3825
3826 IF INV_CONSUMPTION_ADVICE_PROC.has_worker_completed(p_workers(l_Idx))
3827 THEN
3828 l_done := TRUE;
3829 x_worker_idx := l_Idx;
3830 EXIT;
3831 END IF;
3832
3836 DBMS_LOCK.sleep(G_SLEEP_TIME);
3833 END LOOP;
3834
3835 IF (NOT l_done) THEN
3837 END IF;
3838
3839 END LOOP;
3840
3841 IF (l_debug = 1)
3842 THEN
3843 INV_LOG_UTIL.trace
3844 ( '<< Wait For Worker','INV_CONSUMPTION_ADVICE_PROC'
3845 , 9
3846 );
3847 END IF;
3848
3849 END wait_for_worker;
3850
3851
3852 --========================================================================
3853 -- PROCEDURE : Wait_For_All_Workers PRIVATE
3854 -- PARAMETERS: p_workers IN workers' request ID
3855 -- COMMENT : This procedure polls the submitted workers and suspend
3856 -- the program till the completion of all of them.
3857 --=========================================================================
3858 PROCEDURE wait_for_all_workers
3859 ( p_workers IN g_request_tbl_type
3860 )
3861 IS
3862 l_done BOOLEAN;
3863 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3864 BEGIN
3865
3866 IF (l_debug = 1)
3867 THEN
3868 INV_LOG_UTIL.trace
3869 ( '>> Wait For All Workers','INV_CONSUMPTION_ADVICE_PROC'
3870 , 9
3871 );
3872 END IF;
3873
3874 l_done := FALSE;
3875
3876 WHILE (NOT l_done) LOOP
3877
3878 l_done := TRUE;
3879
3880 FOR l_Idx IN 1..p_workers.COUNT LOOP
3881
3882 IF NOT
3883 INV_CONSUMPTION_ADVICE_PROC.has_worker_completed(p_workers(l_Idx))
3884 THEN
3885 l_done := FALSE;
3886 EXIT;
3887 END IF;
3888
3889 END LOOP;
3890
3891 IF (NOT l_done) THEN
3892 DBMS_LOCK.sleep(G_SLEEP_TIME);
3893 END IF;
3894
3895 END LOOP;
3896
3897 IF (l_debug = 1)
3898 THEN
3899 INV_LOG_UTIL.trace
3900 ( '<< Wait For All Workers','INV_CONSUMPTION_ADVICE_PROC'
3901 , 9
3902 );
3903 END IF;
3904
3905 END wait_for_all_workers;
3906
3907
3908 --========================================================================
3909 -- PROCEDURE : Submit_Worker PRIVATE
3910 -- PARAMETERS: p_organization_id IN an organization
3911 -- p_set_process_id IN Set process ID
3912 -- x_workers IN OUT NOCOPY workers' request ID
3913 -- p_request_count IN max worker number
3914 -- COMMENT : This procedure submits the Worker concurrent program.
3915 -- Before submitting the request, it verifies that there are
3916 -- enough workers available and wait for the completion of one
3917 -- if necessary.
3918 -- The list of workers' request ID is updated.
3919 --=========================================================================
3920 PROCEDURE submit_worker
3921 ( p_batch_id IN NUMBER
3922 , p_request_count IN NUMBER
3923 , x_workers IN OUT NOCOPY g_request_tbl_type
3924 )
3925 IS
3926 l_worker_idx BINARY_INTEGER;
3927 l_request_id NUMBER;
3928 l_org_name VARCHAR2(60) := NULL;
3929 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3930 BEGIN
3931
3932 IF (l_debug = 1)
3933 THEN
3934 INV_LOG_UTIL.trace
3935 ( '>> Submit Worker','INV_CONSUMPTION_ADVICE_PROC'
3936 , 9
3937 );
3938 END IF;
3939
3940 IF x_workers.COUNT < p_request_count THEN
3941 -- number of workers submitted so far does not exceed the maximum
3942 -- number of workers allowed
3943 l_worker_idx := x_workers.COUNT + 1;
3944 ELSE
3945 -- need to wait for a submitted worker to finish
3946 INV_CONSUMPTION_ADVICE_PROC.wait_for_worker
3947 ( p_workers => x_workers
3948 , x_worker_idx => l_worker_idx
3949 );
3950 END IF;
3951 --bug7357385 start
3952 IF NOT FND_REQUEST.Set_Options
3953 (
3954 protected => 'YES'
3955 )
3956 --bug7357385 end
3957 THEN
3958 RAISE g_submit_failure_exc;
3959 END IF;
3960
3961 x_workers(l_worker_idx) := FND_REQUEST.submit_request
3962 ( application =>'INV'
3963 , program =>'INVCTXCW'
3964 , description => l_org_name
3965 , argument1 => p_batch_id
3966 );
3967
3968 IF x_workers(l_worker_idx) = 0 THEN
3969 RAISE g_submit_failure_exc;
3970 END IF;
3971
3972 COMMIT;
3973
3974 IF (l_debug = 1)
3975 THEN
3976 INV_LOG_UTIL.trace
3977 ( '<< Submit Worker','INV_CONSUMPTION_ADVICE_PROC'
3978 , 9
3979 );
3980 END IF;
3981
3982 END submit_worker;
3983
3984 --========================================================================
3985 -- PROCEDURE : Batch Allocation PRIVATE
3986 -- COMMENT : This procedure will be called exclusively from the manager.
3987 -- It divides candidate unprocessed records into batches.
3988 -- Once the contents of a batch are established the
3989 -- associated records in MTL_CONSUMPTION_TRANSACTIONS are
3990 -- updated with a batch id and a concurrent program submitted.
3991 -- If there are more candidate unprocessed records then further
3992 -- batches are established and concurrent programs submitted.
3993 -- Thus for large numbers of records concurrency can be achieved.
3994 -- The precedure ends and contol is passed back to the manager
3995 -- when all the submitted concurrent programs have completed.
3999 , p_max_workers IN NUMBER
3996 --=========================================================================
3997 PROCEDURE batch_allocation
3998 ( p_batch_size IN NUMBER
4000 )
4001 IS
4002
4003 --================
4004 -- CURSORS
4005 --================
4006
4007 CURSOR cons_temp_csr_type IS
4008 SELECT mctt.transaction_source_id
4009 , mctt.inventory_item_id
4010 , mctt.organization_id
4011 , mctt.transaction_cost-- blanket_price from MCT Bug 4969421
4012 , mctt.tax_code_id
4013 , mctt.accrual_account_id
4014 , mctt.charge_account_id
4015 , mctt.variance_account_id
4016 , mctt.rate
4017 , mctt.rate_type
4018 , mctt.transaction_date
4019 FROM MTL_CONSUMPTION_TXN_TEMP mctt
4020 ORDER BY mctt.transaction_source_id
4021 , mctt.organization_id;
4022
4023 --=================
4024 -- LOCAL VARIABLES
4025 --=================
4026
4027 l_previous_cons_index BINARY_INTEGER := 1;
4028 l_current_cons_index BINARY_INTEGER := 1;
4029 l_next_cons_index BINARY_INTEGER := 1;
4030 l_empty_cons_tab g_cons_tbl_type;
4031 l_empty_varchar_cons_tab g_cons_varchar_tbl_type;
4032 l_empty_date_cons_tab g_cons_date_tbl_type;
4033 l_txn_source_tab g_cons_tbl_type;
4034 l_item_tab g_cons_tbl_type;
4035 l_org_tab g_cons_tbl_type;
4036 l_price_tab g_cons_tbl_type;
4037 l_tax_code_tab g_cons_tbl_type;
4038 l_accrual_account_tab g_cons_tbl_type;
4039 l_charge_account_tab g_cons_tbl_type;
4040 l_variance_account_tab g_cons_tbl_type;
4041 l_rate_tab g_cons_tbl_type;
4042 l_rate_type_tab g_cons_varchar_tbl_type;
4043 l_date_tab g_cons_date_tbl_type;
4044 l_batch_id NUMBER;
4045 l_current_batch_id NUMBER;
4046 l_batch_size NUMBER;
4047 l_batch_count NUMBER := 1;
4048 l_group_size NUMBER;
4049 l_group_count NUMBER := 1;
4050 l_remain_batch_count NUMBER;
4051 l_workers_tbl g_request_tbl_type;
4052 l_count NUMBER;
4053 l_max_workers NUMBER;
4054 l_org_id NUMBER;
4055 l_new_batch VARCHAR2(1) := 'Y';
4056 l_debug NUMBER := NVL(FND_PROFILE.VALUE
4057 ('INV_DEBUG_TRACE'),0);
4058 l_debug_txn_id NUMBER;
4059
4060
4061 BEGIN
4062
4063 IF (l_debug = 1)
4064 THEN
4065 INV_LOG_UTIL.trace
4066 ( '>> Batch Allocation','INV_CONSUMPTION_ADVICE_PROC'
4067 , 9
4068 );
4069 END IF;
4070
4071 -- open cursor
4072
4073 IF NOT cons_temp_csr_type%ISOPEN
4074 THEN
4075 OPEN cons_temp_csr_type;
4076 END IF;
4077
4078 -- clear the pl/sql table before use
4079
4080 l_txn_source_tab := l_empty_cons_tab;
4081 l_item_tab := l_empty_cons_tab;
4082 l_org_tab := l_empty_cons_tab;
4083 l_price_tab := l_empty_cons_tab;
4084 l_tax_code_tab := l_empty_cons_tab;
4085 l_accrual_account_tab := l_empty_cons_tab;
4086 l_charge_account_tab := l_empty_cons_tab;
4087 l_variance_account_tab := l_empty_cons_tab;
4088 l_rate_tab := l_empty_cons_tab;
4089 l_rate_type_tab := l_empty_varchar_cons_tab;
4090 l_date_tab := l_empty_date_cons_tab;
4091
4092
4093 IF p_batch_size IS NOT NULL THEN
4094 l_batch_size := p_batch_size;
4095 ELSE
4096 l_batch_size := g_batch_size;
4097 END IF;
4098
4099 IF p_max_workers IS NOT NULL THEN
4100 l_max_workers := p_max_workers;
4101 ELSE
4102 l_max_workers := g_max_workers;
4103 END IF;
4104
4105 IF (l_debug = 1)
4106 THEN
4107 INV_LOG_UTIL.trace
4108 ( '>> Batch Allocation(p_batch_size)'||p_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4109 , 9
4110 );
4111 INV_LOG_UTIL.trace
4112 ( '>> Batch Allocation(l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4113 , 9
4114 );
4115 INV_LOG_UTIL.trace
4116 ( '>> Batch Allocation(l_max_workers)'||l_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4117 , 9
4118 );
4119 INV_LOG_UTIL.trace
4120 ( '>> Batch Allocation(p_max_workers)'||p_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4121 , 9
4122 );
4123 END IF;
4124
4125 -- population of pl/sql table
4126
4127 FETCH cons_temp_csr_type
4128 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1)
4129 ,l_item_tab(l_item_tab.COUNT+1)
4130 ,l_org_tab(l_org_tab.COUNT+1)
4131 ,l_price_tab(l_price_tab.COUNT+1)
4132 ,l_tax_code_tab(l_tax_code_tab.COUNT+1)
4133 ,l_accrual_account_tab(l_accrual_account_tab.COUNT+1)
4134 ,l_charge_account_tab(l_charge_account_tab.COUNT+1)
4135 ,l_variance_account_tab(l_variance_account_tab.COUNT+1)
4136 ,l_rate_tab(l_rate_tab.COUNT+1)
4137 ,l_rate_type_tab(l_rate_type_tab.COUNT+1)
4138 ,l_date_tab(l_date_tab.COUNT+1);
4139
4140
4141 WHILE cons_temp_csr_type%FOUND
4142 LOOP
4143
4144 FETCH cons_temp_csr_type
4145 INTO l_txn_source_tab(l_txn_source_tab.COUNT+1)
4146 ,l_item_tab(l_item_tab.COUNT+1)
4147 ,l_org_tab(l_org_tab.COUNT+1)
4148 ,l_price_tab(l_price_tab.COUNT+1)
4149 ,l_tax_code_tab(l_tax_code_tab.COUNT+1)
4153 ,l_rate_tab(l_rate_tab.COUNT+1)
4150 ,l_accrual_account_tab(l_accrual_account_tab.COUNT+1)
4151 ,l_charge_account_tab(l_charge_account_tab.COUNT+1)
4152 ,l_variance_account_tab(l_variance_account_tab.COUNT+1)
4154 ,l_rate_type_tab(l_rate_type_tab.COUNT+1)
4155 ,l_date_tab(l_date_tab.COUNT+1);
4156
4157
4158 END LOOP;
4159
4160 CLOSE cons_temp_csr_type;
4161
4162 -- Allocate a batch id to records in MTL_CONSUMPTION_TRANSACTIONS
4163 -- A new batch is started if the current batch is full or if the
4164 -- number of summarized records in the next batch exceeds
4165 -- the size remaining in the current batch. The only exception to
4166 -- this rule is when the first record a new batch is being
4167 -- considered. If in that case the blanket size > batch size then
4168 -- the current batch is used.
4169
4170 l_current_batch_id := generate_batch_id();
4171 l_remain_batch_count := l_batch_size;
4172 l_current_cons_index := l_txn_source_tab.FIRST;
4173
4174 IF (l_debug = 1)
4175 THEN
4176 INV_LOG_UTIL.trace
4177 ( '>> Batch Alloc (l_current_batch_id)'||l_current_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4178 , 9
4179 );
4180 INV_LOG_UTIL.trace
4181 ( '>> Batch Alloc (l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4182 , 9
4183 );
4184 INV_LOG_UTIL.trace
4185 ( '>> Batch Alloc (l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4186 , 9
4187 );
4188 END IF;
4189
4190 -- Query how many summarized records have been loaded into
4191 -- in the temporary table MTL_CONSUMPTION_TXN_TEMP for the
4192 -- current blanket and set the group size accordingly
4193
4194 SELECT
4195 COUNT(*)
4196 INTO
4197 l_group_size
4198 FROM
4199 mtl_consumption_txn_temp mctt
4200 WHERE mctt.transaction_source_id = l_txn_source_tab(l_current_cons_index);
4201
4202 LOOP
4203
4204 l_group_count := l_group_count + 1;
4205
4206 IF (l_debug = 1)
4207 THEN
4208 INV_LOG_UTIL.trace
4209 ( '>> Batch Alloc(l_group_count)'||l_group_count,'INV_CONSUMPTION_ADVICE_PROC'
4210 , 9
4211 );
4212 INV_LOG_UTIL.trace
4213 ( '>> Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4214 , 9
4215 );
4216 END IF;
4217
4218 -- If the last record of the current blanket
4219 -- has been reached then reset the associate parameters
4220 -- for the next blanket in the batch
4221
4222 l_next_cons_index := l_current_cons_index + 1;
4223
4224 IF (l_debug = 1)
4225 THEN
4226 INV_LOG_UTIL.trace
4227 ( '>> Batch Alloc(l_next_cons_index)'||l_next_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4228 , 9
4229 );
4230 INV_LOG_UTIL.trace
4231 ( '>> Batch Alloc(l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4232 , 9
4233 );
4234 END IF;
4235
4236 IF l_group_count > l_group_size
4237 AND l_current_cons_index < l_txn_source_tab.LAST THEN
4238
4239 -- Find the number of summarized records in the next blanket
4240
4241 SELECT
4242 COUNT(*)
4243 INTO
4244 l_group_size
4245 FROM
4246 mtl_consumption_txn_temp mctt
4247 WHERE mctt.transaction_source_id =
4248 l_txn_source_tab(l_next_cons_index);
4249
4250 l_group_count := 1;
4251
4252 l_remain_batch_count := l_batch_size - l_batch_count;
4253 l_new_batch := 'N';
4254
4255 IF (l_debug = 1)
4256 THEN
4257 INV_LOG_UTIL.trace
4258 ( '>> Batch Alloc....group_count>group_size' ,'INV_CONSUMPTION_ADVICE_PROC'
4259 , 9
4260 );
4261 INV_LOG_UTIL.trace
4262 ( '>> Batch Alloc (l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4263 , 9
4264 );
4265 INV_LOG_UTIL.trace
4266 ( '>> Batch Alloc (l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4267 , 9
4268 );
4269 INV_LOG_UTIL.trace
4270 ( '>> Batch Alloc (l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4271 , 9
4272 );
4273 INV_LOG_UTIL.trace
4274 ( '>> Batch Alloc (l_batch_ct)'||l_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4275 , 9
4276 );
4277 INV_LOG_UTIL.trace
4278 ( '>> Batch Alloc....' ,'INV_CONSUMPTION_ADVICE_PROC'
4279 , 9
4280 );
4281 END IF;
4282
4283 END IF;
4284
4285 -- If the last record of the current batch has been reached
4286 -- OR the record count for the next
4287 -- blanket exceeds the remaining batch size
4288 -- then move to the next batch
4289 -- unless the batch count is 1 in which case continue
4290 -- with the current batch id
4291
4292 IF l_batch_count = l_batch_size
4293 OR (l_group_size > l_remain_batch_count AND l_new_batch = 'N')
4294 OR l_current_cons_index = l_txn_source_tab.LAST
4295 THEN
4296
4297 --IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
4298 --THEN
4299 FORALL i IN l_previous_cons_index..l_current_cons_index
4300 --Bug 5092489, Query modified to eliminate use of MMT
4301 UPDATE
4302 MTL_CONSUMPTION_TRANSACTIONS mct
4303 SET mct.batch_id = l_current_batch_id
4304 /* request id stamped to MCT - bug 5200436 - Start*/
4308 AND mct.consumption_processed_flag IN ('N','E')
4305 , mct.request_id = g_request_id
4306 WHERE mct.transaction_source_id = l_txn_source_tab(i)
4307 AND mct.batch_id = -1
4309 AND mct.inventory_item_id = l_item_tab(i)
4310 AND mct.organization_id = l_org_tab(i)
4311 AND mct.blanket_price = l_price_tab(i)
4312 AND NVL(mct.tax_code_id,-1) = NVL(l_tax_code_tab(i),-1)
4313 AND NVL(mct.accrual_account_id,-1) =
4314 NVL(l_accrual_account_tab(i),-1)
4315 AND NVL(mct.charge_account_id,-1) =
4316 NVL(l_charge_account_tab(i),-1)
4317 AND NVL(mct.variance_account_id,-1) =
4318 NVL(l_variance_account_tab(i),-1)
4319 AND NVL(mct.rate,-1) = NVL(l_rate_tab(i),-1)
4320 AND NVL(mct.rate_type,'##') = NVL(l_rate_type_tab(i), '##');
4321 /* bug 5200436 - End*/
4322
4323 -- Deleted unused commented code as part of bug 11900144
4324
4325 -- Bug 5092489. l_previous_cons_index has to be set as l_current_cons_index
4326 l_previous_cons_index := l_current_cons_index+1;
4327
4328 -- Update the table MTL_CONSUMPTION_TRANSACTIONS with the
4329 -- current batch id for Transfer to consigned txns.
4330
4331
4332 UPDATE
4333 MTL_CONSUMPTION_TRANSACTIONS mct
4334 SET mct.batch_id = l_current_batch_id
4335 WHERE mct.parent_transaction_id IN
4336 (SELECT
4337 mct_in.transaction_id
4338 FROM
4339 MTL_CONSUMPTION_TRANSACTIONS mct_in
4340 WHERE mct_in.batch_id = l_current_batch_id
4341 --AND mct.consumption_processed_flag <> 'Y');
4342 --Bug 5092489
4343 AND mct_in.consumption_processed_flag IN ('N', 'E'))
4344 AND mct.consumption_processed_flag IN ('N', 'E');
4345
4346 -- Call concurrent worker
4347
4348 INV_CONSUMPTION_ADVICE_PROC.submit_worker( l_current_batch_id
4349 , l_max_workers
4350 , l_workers_tbl);
4351
4352 -- the size of the group can only be a maximum of the batch size
4353
4354 IF (l_debug = 1)
4355 THEN
4356 INV_LOG_UTIL.trace
4357 ( 'After submit worker'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4358 , 9
4359 );
4360 INV_LOG_UTIL.trace
4361 ( 'Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4362 , 9
4363 );
4364 INV_LOG_UTIL.trace
4365 ( 'Batch Alloc(l_batch_size)'||l_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4366 , 9
4367 );
4368 END IF;
4369
4370 IF l_group_size > l_batch_size THEN
4371 l_group_size := l_group_size - l_batch_count;
4372 END IF;
4373
4374 IF (l_debug = 1)
4375 THEN
4376 INV_LOG_UTIL.trace
4377 ( 'Batch Alloc(l_group_size)'||l_group_size,'INV_CONSUMPTION_ADVICE_PROC'
4378 , 9
4379 );
4380 END IF;
4381
4382 --l_group_count :=0;
4383
4384 -- Reset Batch Variables
4385
4386 l_new_batch := 'Y';
4387 l_current_batch_id := generate_batch_id();
4388 l_batch_count := 0;
4389 l_remain_batch_count := l_batch_size;
4390 l_group_count :=0;
4391
4392 IF (l_debug = 1)
4393 THEN
4394 INV_LOG_UTIL.trace
4395 ( 'Batch Alloc(l_current_batch_id)'||l_current_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4396 , 9
4397 );
4398 INV_LOG_UTIL.trace
4399 ( 'Batch Alloc(l_remain_batch_count)'||l_remain_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4400 , 9
4401 );
4402 END IF;
4403
4404 END IF;
4405
4406 -- If the last record in MMTT is reached then stop
4407 EXIT WHEN l_current_cons_index = l_txn_source_tab.LAST;
4408
4409 l_current_cons_index := l_txn_source_tab.NEXT(l_current_cons_index);
4410
4411 l_batch_count := l_batch_count + 1;
4412
4413 IF (l_debug = 1)
4414 THEN
4415 INV_LOG_UTIL.trace
4416 ( 'Batch Alloc(l_current_cons_index)'||l_current_cons_index,'INV_CONSUMPTION_ADVICE_PROC'
4417 , 9
4418 );
4419 INV_LOG_UTIL.trace
4420 ( 'Batch Alloc(l_batch_count)'||l_batch_count,'INV_CONSUMPTION_ADVICE_PROC'
4421 , 9
4422 );
4423 END IF;
4424
4425 END LOOP;
4426
4427 -- Return control when all concurrent programs have completed
4428
4429 INV_CONSUMPTION_ADVICE_PROC.wait_for_all_workers
4430 ( p_workers => l_workers_tbl
4431 );
4432
4433 IF (l_debug = 1)
4434 THEN
4435 INV_LOG_UTIL.trace
4436 ( '<< Batch Allocation','INV_CONSUMPTION_ADVICE_PROC'
4437 , 9
4438 );
4439 END IF;
4440
4441
4442 EXCEPTION
4443
4444 WHEN OTHERS THEN
4445 IF (l_debug = 1) THEN
4446 INV_LOG_UTIL.trace
4447 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
4448 , 9
4449 );
4450 END IF;
4451
4452 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_BCH_ALL');
4453 FND_MSG_PUB.ADD;
4454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4455
4456 END batch_allocation;
4457
4458 --========================================================================
4459 -- PROCEDURE : Consumption_Txn_Manager PUBLIC
4460 -- COMMENT : This procedure will assign each unprocessed record in
4461 -- MTL_CONSUMPTION_TRANSACTIONS to a batch and then call the
4465 -- have been assigned to a batch.
4462 -- Consumption_Transaction_Worker for that batch. The manager
4463 -- will continue until all records
4464 -- in MTL_CONSUMPTION_TRANSACTIONS
4466 --=========================================================================
4467 PROCEDURE consumption_txn_manager
4468 ( p_batch_size IN NUMBER
4469 , p_max_workers IN NUMBER
4470 , p_vendor_id IN NUMBER
4471 , p_vendor_site_id IN NUMBER
4472 , p_inventory_item_id IN NUMBER
4473 , p_organization_id IN NUMBER
4474 )
4475 IS
4476
4477 -- Bug , - Creating REF cursors to associate with
4478 -- appropriate queries at run time and avoid NVL checks
4479 -- Start
4480 -- ======================
4481 -- Dynamic Cursor Variable
4482 -- =======================
4483 /* Bug 5092489. Commented as not used
4484 TYPE blanket_csr_type IS REF CURSOR;
4485 l_blanket_csr blanket_csr_type;
4486
4487 --Bug 4863365 - End
4488
4489
4490 --=================
4491 -- VARIABLES
4492 --=================
4493
4494 l_agreement_flag VARCHAR2(1);
4495 l_transaction_source_id NUMBER;
4496 l_current_cons_index BINARY_INTEGER := 0;
4497 l_current_index BINARY_INTEGER;
4498 l_empty_cons_tab g_cons_tbl_type;
4499 l_txn_source_tab g_cons_tbl_type;
4500 l_org_tab g_cons_tbl_type;
4501 l_blanket_query VARCHAR2(15000); -- Bug 4666585
4502 */
4503 l_count NUMBER;
4504 l_batch_id NUMBER := -1;
4505 l_debug NUMBER := NVL(FND_PROFILE.VALUE
4506 ('INV_DEBUG_TRACE'),0);
4507
4508
4509
4510 BEGIN
4511
4512 IF (l_debug = 1)
4513 THEN
4514 INV_LOG_UTIL.trace
4515 ( '>> Consumption Txn Manager','INV_CONSUMPTION_ADVICE_PROC'
4516 , 9
4517 );
4518 INV_LOG_UTIL.trace
4519 ( '>> p_batch_size: '|| p_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4520 , 9
4521 );
4522 INV_LOG_UTIL.trace
4523 ( '>> p_max_workers: '||p_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4524 , 9
4525 );
4526 INV_LOG_UTIL.trace
4527 ( '>> p_vendor_id: '||p_vendor_id,'INV_CONSUMPTION_ADVICE_PROC'
4528 , 9
4529 );
4530 INV_LOG_UTIL.trace
4531 ( '>> p_vendor_site_id: '||p_vendor_site_id,'INV_CONSUMPTION_ADVICE_PROC'
4532 , 9
4533 );
4534 INV_LOG_UTIL.trace
4535 ( '>> p_inventory_item_id: '||p_inventory_item_id,'INV_CONSUMPTION_ADVICE_PROC'
4536 , 9
4537 );
4538 INV_LOG_UTIL.trace
4539 ( '>> p_organization_id: '||p_organization_id,'INV_CONSUMPTION_ADVICE_PROC'
4540 , 9
4541 );
4542
4543 END IF;
4544
4545 g_request_id := NULL;
4546 g_request_id :=
4547 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID') ) ;
4548
4549 IF (l_debug = 1)
4550 THEN
4551 INV_LOG_UTIL.trace
4552 ( '>> g_request_id => '|| g_request_id
4553 , 9
4554 );
4555 END IF;
4556
4557 -- Bug 11900144
4558 INV_CONSUMPTION_ADVICE_PROC.populate_po_line_id();
4559
4560 /* Bug 14036045. Performance issue. Modified below query and checking whether record exists or not.*/
4561 begin
4562 select 1 into l_count from dual
4563 where exists(select /*+ no_unnest */ 1 from MTL_CONSUMPTION_TRANSACTIONS mct
4564 where consumption_processed_flag IN ('N', 'E')
4565 AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL) AND NVL(net_qty,0) > 0
4566 and exists(select /*+ no_unnest */ 1 from MTL_MATERIAL_TRANSACTIONS mmt
4567 where mct.transaction_id = mmt.transaction_id
4568 AND mmt.owning_organization_id = NVL(p_vendor_site_id, mmt.owning_organization_id)
4569 AND mmt.organization_id = NVL(p_organization_id, mmt.organization_id)
4570 AND mmt.inventory_item_id = NVL(p_inventory_item_id, mmt.inventory_item_id)
4571 AND mmt.transaction_type_id = 74
4572 AND mmt.transaction_action_id = 6
4573 AND mmt.transaction_source_type_id = 1
4574 and exists(select /*+ no_unnest */ 1 from po_vendor_sites_all pvsa
4575 where mmt.owning_organization_id = pvsa.vendor_site_id
4576 and pvsa.vendor_id = NVL(p_vendor_id, pvsa.vendor_id)))) ;
4577 exception
4578 when no_data_found then
4579 l_count := 0;
4580 end;
4581
4582 IF (l_debug = 1)
4583 THEN
4584 INV_LOG_UTIL.trace
4585 ( '<< CA Mgr , l_count exists :'||l_count,'INV_CONSUMPTION_ADVICE_PROC'
4586 , 9
4587 );
4588 INV_LOG_UTIL.trace
4589 ( '<< CA Mgr(batch_id):'||l_batch_id,'INV_CONSUMPTION_ADVICE_PROC'
4590 , 9
4591 );
4592 END IF;
4593
4594 IF l_count > 0 THEN
4595
4596 --Bug 5092489: New condition added
4597 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
4598 SET (mct.batch_id, mct.transaction_source_id, mct.inventory_item_id,
4599 mct.accrual_account_id, mct.organization_id,
4600 mct.owning_organization_id, mct.transaction_date)
4601 = (SELECT
4602 l_batch_id, mmt.transaction_source_id,
4603 mmt.inventory_item_id, mmt.distribution_account_id,
4604 mmt.organization_id, mmt.owning_organization_id,
4605 mmt.transaction_date
4606 FROM mtl_material_transactions mmt,po_vendor_sites_all pvsa
4607 WHERE mct.transaction_id = mmt.transaction_id
4608 AND mmt.owning_organization_id = pvsa.vendor_site_id
4612 AND (p_organization_id IS NULL OR mmt.organization_id = p_organization_id)
4609 AND (p_vendor_id IS NULL OR pvsa.vendor_id = p_vendor_id)
4610 AND (p_vendor_site_id IS NULL OR pvsa.vendor_site_id =
4611 p_vendor_site_id)
4613 AND (p_inventory_item_id IS NULL OR mmt.inventory_item_id = p_inventory_item_id)
4614 )
4615 WHERE mct.consumption_processed_flag IN ('N', 'E')
4616 AND NVL(net_qty,0) > 0
4617 AND ( mct.batch_id = l_batch_id OR mct.batch_id IS NULL)
4618 AND mct.po_line_id IS NOT NULL; --bug 11900144
4619
4620
4621 UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
4622 SET (mct.global_agreement_flag) =
4623 (SELECT NVL(global_agreement_flag,'N') FROM po_headers_all
4624 WHERE po_header_id = mct.transaction_source_id)
4625 WHERE mct.consumption_processed_flag IN ('N', 'E')
4626 AND mct.batch_id = l_batch_id;
4627
4628 -- Deleted unused commeneted code as part of bug 11900144
4629
4630 IF NVL(FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_GROUPING'),'N') = 'N'
4631 THEN
4632 INV_CONSUMPTION_ADVICE_PROC.load_combination_prf
4633 (p_batch_id => l_batch_id
4634 ,p_vendor_id => p_vendor_id
4635 ,p_vendor_site_id => p_vendor_site_id
4636 ,p_inventory_item_id => p_inventory_item_id
4637 ,p_organization_id => p_organization_id);
4638 ELSE
4639 INV_CONSUMPTION_ADVICE_PROC.load_combination
4640 (p_batch_id => l_batch_id
4641 ,p_vendor_id => p_vendor_id
4642 ,p_vendor_site_id => p_vendor_site_id
4643 ,p_inventory_item_id => p_inventory_item_id
4644 ,p_organization_id => p_organization_id);
4645
4646 END IF;
4647
4648 -- Call procedure to assign a batch_id to unprocessed data
4649 -- in MTL_CONSUMPTION_TRANSACTIONS and call the worker to
4650 -- process a batch
4651
4652 IF (l_debug = 1)
4653 THEN
4654 INV_LOG_UTIL.trace
4655 ( 'Calling Batch Allocation(size) '||p_batch_size,'INV_CONSUMPTION_ADVICE_PROC'
4656 , 9
4657 );
4658 INV_LOG_UTIL.trace
4659 ( 'Workers '||p_max_workers,'INV_CONSUMPTION_ADVICE_PROC'
4660 , 9
4661 );
4662 END IF;
4663
4664 INV_CONSUMPTION_ADVICE_PROC.batch_allocation
4665 (p_batch_size => p_batch_size
4666 ,p_max_workers => p_max_workers);
4667
4668 IF (l_debug = 1)
4669 THEN
4670 INV_LOG_UTIL.trace
4671 ( 'Calling update_po_distrubution_id '
4672 , 9
4673 );
4674 END IF;
4675
4676 update_po_distrubution_id ;
4677 END IF;
4678
4679
4680 IF (l_debug = 1)
4681 THEN
4682 INV_LOG_UTIL.trace
4683 ( '<< Consumption Txn Manager','INV_CONSUMPTION_ADVICE_PROC'
4684 , 9
4685 );
4686 END IF;
4687
4688 EXCEPTION
4689
4690 WHEN OTHERS THEN
4691 IF (l_debug = 1) THEN
4692 INV_LOG_UTIL.trace
4693 ( SQLCODE || ' : ' || SQLERRM ,'INV_CONSUMPTION_ADVICE_PROC'
4694 , 9
4695 );
4696 END IF;
4697
4698 FND_MESSAGE.set_name('INV', 'INV_CONS_SUP_DEL_REC');
4699 FND_MSG_PUB.ADD;
4700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4701
4702 END consumption_txn_manager;
4703
4704 END INV_CONSUMPTION_ADVICE_PROC;