[Home] [Help]
PACKAGE BODY: APPS.INV_THIRD_PARTY_STOCK_PVT
Source
1 PACKAGE BODY INV_THIRD_PARTY_STOCK_PVT AS
2 -- $Header: INVVTPSB.pls 120.23.12010000.2 2008/07/29 12:55:46 ptkumar ship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVVTPSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Consignment Financial Document API |
13 --| |
14 --| PROCEDURE LIST |
15 --| Record_Consumption |
16 --| Populate_Cost_Details |
17 --| Get_Conversion_Rate |
18 --| ReSet_OU_Context |
19 --| Get_PO_Info |
20 --| Get_Account |
21 --| Get_Consumed_Amt |
22 --| Get_Total_Blanket_Amt |
23 --| Process_Financial_Info |
24 --| |
25 --| HISTORY |
26 --| 10/01/02 Prabha Seshadri Created Finacial Document API |
27 --| Jul-29 rajkrish consigned error rpt |
28 --| 07-Mar-06 kdevadas BLANKET_PRICE and PO_DISTRIBUTION_ID |
29 --| columns added to MCT.PO price returned |
30 --| by get_break_price is inserted INTO |
31 --| MTL_CONSUMPTION_TRANSACTIONS-Bug 4969421 |
32 --| 22-May-06 kdevadas Delete from ZX_TRX_HEADERS_GT before |
33 --| insertion. This prevents 'Unique |
34 --| Constraint Violated' error- Bug 5084307 |
35 --| 18-Jul-06 kdevadas Get_Consumed_Amt procedure changed |
36 --| to use mct.blanket_price rather than |
37 --| mmt.transaction_cost - Bug 5395579 |
38 --| 28-Aug-06 kdevadas Delete before inserting into ZX_LINES |
39 --| and ZX_DISTRIBUTIONS - Bug 5488006 |
40 --| 14-Sep-06 kdevadas Changed cursor in Calculate_Tax to fetch |
41 --| tax_rate and tax_rec_rate - Bug 5530358 |
42 --+========================================================================
43
44 --===================
45 -- GLOBALS
46 --===================
47
48 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_THIRD_PARTY_STOCK_PVT';
49 g_user_id NUMBER := FND_PROFILE.value('USER_ID');
50 g_resp_id NUMBER := FND_PROFILE.value('RESP_ID');
51 g_pgm_appl_id NUMBER := FND_PROFILE.value('RESP_APPL_ID');
52
53 TYPE ctx_value_rec_type IS RECORD (org_id NUMBER, resp_id NUMBER);
54 TYPE ctx_tbl_type IS TABLE OF ctx_value_rec_type INDEX BY BINARY_INTEGER;
55 g_context_tbl ctx_tbl_type;
56
57
58 g_error_code VARCHAR2(35) ;
59 g_calling_action VARCHAR2(1) ;
60 g_po_header_id NUMBER ;
61 g_purchasing_uom VARCHAR2(25);
62 g_primary_uom VARCHAR2(25);
63
64 --===================
65 -- PROCEDURES AND FUNCTIONS
66 --===================
67
68 --========================================================================
69 -- PROCEDURE : Record_Consumption PRIVATE
70 -- PARAMETERS: p_mtl_transaction_id Material transaction id
71 -- p_rct_transaction_id Txn Id receipt side
72 -- p_transaction_source_type_id Txn Src Type
73 -- p_transaction_source_id Txn source
74 -- p_transaction_quantity Txn Qty
75 -- p_tax_code_id Tax code
76 -- p_tax_rate Tax Rate
77 -- p_recoverable_tax Recoverable Tax
78 -- p_non_recoverable_tax Non Recoverable Tax
79 -- p_rate Exchange rate
80 -- p_rate_type Exchange Rate type
81 -- p_charge_account_id Charge account
82 -- p_po_price Unit Price -- Bug 4969420
83 -- p_secondary_transaction_qty Secondary Txn Qty /*INVCONV*/
84
85 -- COMMENT : Procedure to insert the consignment transactions, when
86 -- consumption takes place.Invoked by Process_Financial_Info
87 -- Changes : INVCONV added a new parameter p_secondary_transaction_qty
88 -- to support process features.
89 --========================================================================
90 PROCEDURE Record_Consumption
91 ( p_mtl_transaction_id IN NUMBER
92 , p_transaction_source_type_id IN NUMBER
93 , p_transaction_action_id IN NUMBER
94 , p_transaction_source_id IN NUMBER
95 , p_transaction_quantity IN NUMBER
96 , p_tax_code_id IN NUMBER
97 , p_tax_rate IN NUMBER
98 , p_tax_recovery_rate IN NUMBER
99 , p_recoverable_tax IN NUMBER
100 , p_non_recoverable_tax IN NUMBER
101 , p_rate IN NUMBER
102 , p_rate_type IN VARCHAR2
103 , p_charge_account_id IN NUMBER
104 , p_variance_account_id IN NUMBER
105 /*bug 4969420 - Start*/
106 /* Storing the unit price in MCT */
107 , p_unit_price IN NUMBER
108 /* bug 4969420 - End */
109 , p_secondary_transaction_qty IN NUMBER DEFAULT NULL
110 )
111 IS
112 l_net_qty NUMBER;
113 l_parent_transaction NUMBER;
114 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
115 l_secondary_net_qty NUMBER; /* INVCONV */
116
117 BEGIN
118
119 IF (l_debug = 1)
120 THEN
121 INV_LOG_UTIL.trace
122 ( '>> Entering Record Consumption','INV_THIRD_PARTY_STOCK_PVT'
123 , 9
124 );
125 END IF;
126
127 -- For a correction transaction, update the net_qty of the parent txn
128 -- by subtracting the transaction quantity of the correction.
129 -- After updating the net qty of the parent, make the net_qty null
130 -- for the correction transaction. The summarization API will not
131 -- pick up the correction transaction for summarization .
132
133 -- INVCONV Similarly update the secondary quantity of the parent.
134
135 IF (p_transaction_source_type_id=13) AND (p_transaction_action_id = 6)
136 AND (p_transaction_source_id IS NOT NULL)
137 THEN
138 UPDATE mtl_consumption_transactions
139 SET net_qty = (net_qty - ABS(p_transaction_quantity)),
140 secondary_net_qty = (secondary_net_qty - ABS(p_secondary_transaction_qty)) /* INVCONV */
141 WHERE transaction_id = p_transaction_source_id;
142
143 l_net_qty := NULL;
144 l_secondary_net_qty := NULL; /* INVCONV */
145 l_parent_transaction := p_transaction_source_id;
146 ELSE
147 l_net_qty := p_transaction_quantity;
148 l_secondary_net_qty := p_secondary_transaction_qty; /* INVCONV */
149 END IF;
150
151
152 INSERT INTO mtl_consumption_transactions
153 ( transaction_id
154 , consumption_processed_flag
155 , net_qty
156 , tax_code_id
157 , tax_rate
158 , tax_recovery_rate
159 , recoverable_tax
160 , non_recoverable_tax
161 , rate
162 , rate_type
163 , charge_account_id
164 , variance_account_id
165 , parent_transaction_id
166 , created_by
167 , creation_date
168 , last_updated_by
169 , last_update_date
170 , last_update_login
171 /* Bug 4969420 - Start */
172 , blanket_price
173 /* Bug 4969420 - End */
174 , secondary_net_qty /* INVCONV */
175 )
176 VALUES
177 ( p_mtl_transaction_id
178 , 'N'
179 , l_net_qty
180 , NVL(p_tax_code_id,-1)
181 , p_tax_rate
182 , p_tax_recovery_rate
183 , p_recoverable_tax
184 , p_non_recoverable_tax
185 , p_rate
186 , p_rate_type
187 , p_charge_account_id
188 , p_variance_account_id
189 , l_parent_transaction
190 , FND_GLOBAL.user_id
191 , SYSDATE
192 , FND_GLOBAL.user_id
193 , SYSDATE
194 , FND_GLOBAL.login_id
195 /* Bug 4969420 - Start */
196 , p_unit_price
197 /* Bug 4969420 - End */
198 , l_secondary_net_qty /* INVCONV */
199 );
200
201 IF (l_debug = 1)
202 THEN
203 INV_LOG_UTIL.trace
204 ( '<< Exiting Record Consumption','INV_THIRD_PARTY_STOCK_PVT'
205 , 9
206 );
207 END IF;
208
209 END Record_Consumption;
210
211
212 --========================================================================
213 -- PROCEDURE : Populate_Cost_Details PRIVATE
214 -- PARAMETERS: p_mtl_transaction_id Material transaction id
215 -- p_rct_transaction_id Mtl Receipt transaction
216 -- p_transaction_source_type_id Source Type Id
217 -- p_transaction_action_id Transaction Action
218 -- p_organization_id Organization id
219 -- p_inventory_item_id Item id
220 -- p_po_price Price
221 -- COMMENT : Procedure to insert into MTL_CST_TXN_COST_DETAILS table
222 -- Invoked by Process_Financial_Info
223 --========================================================================
224 PROCEDURE Populate_Cost_Details
225 ( p_mtl_transaction_id IN NUMBER
226 , p_rct_transaction_id IN NUMBER
227 , p_transaction_source_type_id IN NUMBER
228 , p_transaction_action_id IN NUMBER
229 , p_organization_id IN NUMBER
230 , p_inventory_item_id IN NUMBER
231 , p_po_price IN NUMBER
232 )
233 IS
234 l_rowid VARCHAR2(2000);
235 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
236 BEGIN
237
238 -- Call to insert data in costing table.
239 -- Do the insert only for implicit transactions. In case of explicit
240 -- transactions, the TM will do the insert.
241
242 IF (l_debug = 1)
243 THEN
244 INV_LOG_UTIL.trace
245 ( '>> Entering Populate Cost Details','INV_THIRD_PARTY_STOCK_PVT'
246 , 9
247 );
248 END IF;
249
250 IF (p_transaction_action_id <> 6)
251 THEN
252
253 MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row
254 ( x_rowid => l_rowid
255 , x_transaction_id => p_rct_transaction_id
256 , x_organization_id => p_organization_id
257 , x_cost_element_id => 1 --material cost
258 , x_level_type => 1 --current level
259 , x_last_update_date => SYSDATE
260 , x_last_updated_by => FND_GLOBAL.user_id
261 , x_creation_date => SYSDATE
262 , x_created_by => FND_GLOBAL.user_id
263 , x_inventory_item_id => p_inventory_item_id
264 , x_transaction_cost => p_po_price
265 , x_new_average_cost => NULL
266 , x_percentage_change => NULL
267 , x_value_change => NULL
268 );
269
270 END IF;
271
272 IF (l_debug = 1)
273 THEN
274 INV_LOG_UTIL.trace
275 ( '<< Exiting Populate Cost Details','INV_THIRD_PARTY_STOCK_PVT'
276 , 9
277 );
278 END IF;
279
280 END Populate_Cost_Details;
281
282
283 --========================================================================
284 -- PROCEDURE : Get_Conversion_Rate PRIVATE
285 -- PARAMETERS: p_set_of_books_id SOB
286 -- p_from_currency from currency
287 -- p_to_currency to currency
288 -- p_conversion_date conversion Date
289 -- p_conversion_type conversion type
290 -- p_amount amount to be converted
291 -- p_user_rate user rate
292 -- x_converted_amount converted amount
293 -- x_conversion_rate exchange rate used for conversion
294 -- COMMENT : This procedure returns the exchange rate if the currency_code
295 -- in the blanket is different than the functional currency
296 --========================================================================
297 PROCEDURE Get_Conversion_Rate
298 ( p_set_of_books_Id IN NUMBER
299 , p_from_currency IN VARCHAR2
300 , p_to_currency IN VARCHAR2
301 , p_conversion_date IN DATE
302 , p_conversion_type IN VARCHAR2
303 , p_amount IN NUMBER
304 , p_user_rate IN NUMBER
305 , p_vendor_name IN VARCHAR2
306 , p_vendor_site IN VARCHAR2
307 , p_quantity IN NUMBER
308 , x_converted_amount OUT NOCOPY NUMBER
309 , x_conversion_rate OUT NOCOPY NUMBER
310 )
311 IS
312 l_denominator NUMBER;
313 l_numerator NUMBER;
314 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
315
316 BEGIN
317 -- Call to the GL API to get the rate
318
319 IF (l_debug = 1)
320 THEN
321 INV_LOG_UTIL.trace
322 ( '>> Entering Get Conversion Rate','INV_THIRD_PARTY_STOCK_PVT'
323 , 9
324 );
325 END IF;
326
327 GL_CURRENCY_API.convert_closest_amount
328 ( x_from_currency => p_from_currency
329 , x_to_currency => p_to_currency
330 , x_conversion_date => p_conversion_date
331 , x_conversion_type => p_conversion_type
332 , x_amount => p_amount
333 , x_user_rate => p_user_rate
334 , x_max_roll_days => -1
335 , x_converted_amount => x_converted_amount
336 , x_denominator => l_denominator
337 , x_numerator => l_numerator
338 , x_rate => x_conversion_rate
339 );
340
341 SELECT
342 DECODE(NVL(fc.minimum_accountable_unit,0), 0,
343 (p_amount*p_quantity)* x_conversion_rate/p_quantity,
344 (p_amount* p_quantity/fc.minimum_accountable_unit) *
345 fc.minimum_accountable_unit*x_conversion_rate/p_quantity)
346 INTO
347 x_converted_amount
348 FROM
349 fnd_currencies fc
350 WHERE fc.currency_code = p_from_currency;
351
352
353 IF (l_debug = 1)
354 THEN
355 INV_LOG_UTIL.trace
356 ( '<< Exiting Get Conversion Rate','INV_THIRD_PARTY_STOCK_PVT'
357 , 9
358 );
359 END IF;
360
361 EXCEPTION
362 WHEN GL_CURRENCY_API.no_rate THEN
363 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_GL_API_NO_RATE');
364 FND_MESSAGE.Set_Token('SuppName',p_vendor_name);
365 FND_MESSAGE.Set_Token('SiteCode',p_vendor_site);
366 FND_MSG_PUB.ADD;
367 g_error_code := 'INV_CONS_SUP_GL_API_NO_RATE' ;
368 RAISE FND_API.G_EXC_ERROR;
369
370 END Get_Conversion_Rate;
371
372 --========================================================================
373 -- PROCEDURE : Reset_OU_Context PRIVATE
374 -- PARAMETERS:
375 -- COMMENT : Reset the OU context to be the same as when TM invoked
376 -- Process_FInancial_Info
377 --========================================================================
378
379 PROCEDURE Reset_OU_Context
380 IS
381 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
382 BEGIN
383
384 IF (l_debug = 1)
385 THEN
386 INV_LOG_UTIL.trace
387 ( '>> Entering Reset OU ','INV_THIRD_PARTY_STOCK_PVT'
388 , 9
389 );
390 END IF;
391
392 FND_GLOBAL.apps_initialize(g_user_id,g_resp_id,g_pgm_appl_id);
393
394 IF (l_debug = 1)
395 THEN
396 INV_LOG_UTIL.trace
397 ( '<< Exiting Reset OU ','INV_THIRD_PARTY_STOCK_PVT'
398 , 9
399 );
400 END IF;
401
402 END Reset_OU_Context;
403
404 --========================================================================
405 -- PROCEDURE : Calculate_Tax PRIVATE
406 -- PARAMETERS:
407 -- p_header_id PO Header Id
408 -- p_line_id PO Line Id
409 -- p_org_id Operating Unit
410 -- p_item_id Item
411 -- p_need_by_date Consumption Date
412 -- p_ship_to_organization Inventory Organization
413 -- p_account_id Accrual account
414 -- p_tax_code_id Tax code id from PO Lines
415 -- p_transaction_quantity Transaction Qty
416 -- p_po_price PO price
417 -- p_vendor_name Vendor
418 -- p_vendor_site Site
419 -- x_tax_rate Tax rate
420 -- x_tax_recovery_rate Recovery rate
421 -- x_recoverable_Tax Recoverable tax
422 -- x_nonrecoverable_tax Non recoverable tax
423 -- COMMENT : Return the recoverable and nonrecoverable tax
424 --========================================================================
425
426 PROCEDURE Calculate_Tax
427 ( p_header_id IN NUMBER
428 , p_line_id IN NUMBER
429 , p_org_id IN NUMBER
430 , p_item_id IN NUMBER
431 , p_need_by_date IN DATE
432 , p_ship_to_organization_id IN NUMBER
433 , p_account_id IN NUMBER
434 , p_tax_code_id IN OUT NOCOPY NUMBER
435 , p_transaction_quantity IN NUMBER
436 , p_po_price IN NUMBER
437 , p_vendor_name IN VARCHAR2
438 , p_vendor_site IN VARCHAR2
439 , p_uom_code IN VARCHAR2
440 , p_transaction_id IN NUMBER
441 , p_transaction_date IN DATE
442 , p_global_flag IN VARCHAR2
443 , x_tax_rate OUT NOCOPY NUMBER
444 , x_tax_recovery_rate OUT NOCOPY NUMBER
445 , x_recoverable_tax OUT NOCOPY NUMBER
446 , x_nonrecoverable_tax OUT NOCOPY NUMBER
447 )
448 IS
449 x_header_id NUMBER;
450 x_line_id NUMBER;
451 x_shipment_id NUMBER ;
452 l_counter NUMBER;
453 l_tax_code_id NUMBER;
454 i NUMBER := 0;
455 l_ship_to_location_id NUMBER;
456 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
457 l_precision NUMBER;
458 l_return_status VARCHAR2(1);
459 l_msg_count NUMBER;
460 l_msg_data VARCHAR2(2000);
461 l_vendor_id NUMBER;
462 l_vendor_site_id NUMBER;
463 l_application_id NUMBER;
464 l_entity_code VARCHAR2(25);
465 l_event_class_code VARCHAR2(25);
466 l_event_type_code VARCHAR2(25);
467 l_vendor_party_id NUMBER;
468 l_vendor_site_party_id NUMBER;
469 /* bug 5081702 Start */
470 l_vendor_org_id NUMBER; -- OU of vendor site
471 l_rate_type VARCHAR2(25);
472 l_legal_entity_id NUMBER ;
473 l_set_of_books_id NUMBER ;
474 l_ship_from_location_id NUMBER;
475 /* bug 5081702 End */
476
477 --Rajesh ETax
478 CURSOR tax_csr_type_nrec IS
479 SELECT
480 SUM(NVL( rec_nrec_tax_amt,0))
481 FROM
482 zx_rec_nrec_dist_gt
483 WHERE application_id = 201
484 AND entity_code = l_entity_code
485 AND trx_id = p_header_id
486 AND event_class_code = l_event_class_code
487 AND NVL(recoverable_flag,'N') = 'N' ;
488
489 CURSOR tax_csr_type_rec IS
490 SELECT
491 SUM(NVL( rec_nrec_tax_amt,0))
492 FROM
493 zx_rec_nrec_dist_gt
494 WHERE application_id = 201
495 AND entity_code = l_entity_code
496 AND trx_id = p_header_id
497 AND event_class_code = l_event_class_code
498 AND NVL(recoverable_flag,'N') = 'Y' ;
499
500 /* Bug 5530358 - Start */
501 /* Tax rate fetched from the tax tables */
502 CURSOR tax_csr_type_rate IS
503 SELECT
504 rec_nrec_rate
505 , tax_rate
506 FROM
507 zx_rec_nrec_dist_gt
508 WHERE application_id = 201
509 AND entity_code = l_entity_code
510 AND trx_id = p_header_id
511 AND event_class_code = l_event_class_code
512 AND NVL(recoverable_flag,'N') = 'N' ;
513 /* Bug 5530358 - End */
514
515 BEGIN
516
517 IF (l_debug = 1)
518 THEN
519 INV_LOG_UTIL.trace
520 ( 'Entering Into calculate tax '
521 , 9
522 );
523 END IF;
524
525 l_ship_to_location_id :=
526 INV_THIRD_PARTY_STOCK_UTIL.get_location(p_ship_to_organization_id);
527
528 SELECT application_id
529 INTO l_application_id
530 FROM fnd_application
531 WHERE application_short_name = 'PO';
532
533 IF (l_debug = 1)
534 THEN
535 INV_LOG_UTIL.trace
536 ( 'l_application_id => ' || l_application_id
537 , 9
538 );
539 END IF;
540 IF (l_debug = 1)
541 THEN
542 INV_LOG_UTIL.trace
543 ( 'l_ship_to_location_id => '|| l_ship_to_location_id
544 , 9
545 );
546 END IF;
547
548 -- The quantity that is being passed to the tax engine is always 1
549 -- This is because the po price in MMT is stored as unit_price. So
550 -- tax is calculated for the unit price. When creating the release,
551 -- the total amount is calculated by unit_price times quantity
552
553 IF NVL(p_global_flag,'N') = 'Y'
554 THEN
555 l_entity_code := 'PURCHASE_ORDER';
556 l_event_class_code := 'PO_PA';
557 l_event_type_code := 'PO_PA_CREATED';
558 ELSE
559 l_entity_code := 'RELEASE' ;
560 l_event_class_code := 'RELEASE';
561 l_event_type_code := 'RELEASE_CREATED';
562
563 END IF;
564
565 SELECT vendor_id
566 , vendor_site_id
567 INTO l_vendor_id
568 , l_vendor_site_id
569 FROM po_headers_all
570 WHERE po_header_id = p_header_id;
571
572 SELECT pov.party_id
573 , povs.party_site_id
574 INTO l_vendor_party_id
575 , l_vendor_site_party_id
576 FROM
577 po_vendors pov
578 , po_vendor_sites_all povs
579 WHERE pov.vendor_id = povs.vendor_id
580 AND povs.vendor_site_id = l_vendor_site_id
581 AND povs.vendor_id = l_vendor_id;
582
583 /* bug 5081702 Start - Insert OU of vendor site id */
584 SELECT hzps.location_id
585 INTO
586 l_ship_from_location_id
587 FROM
588 hz_party_sites hzps
589 WHERE
590 hzps.party_site_id = l_vendor_site_party_id;
591
592 l_vendor_org_id :=
593 INV_THIRD_PARTY_STOCK_UTIL.get_org_id(l_vendor_site_id);
594 l_legal_entity_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU
595 (l_vendor_org_id);
596
597 SELECT set_of_books_id
598 INTO l_set_of_books_id
599 FROM hr_operating_units
600 WHERE organization_id = l_vendor_org_id ;
601 /* bug 5081702 End */
602
603
604
605 IF (l_debug = 1)
606 THEN
607 INV_LOG_UTIL.trace
608 ( 'l_vendor_party_id => ' || l_vendor_party_id
609 , 9
610 );
611 INV_LOG_UTIL.trace
612 ( 'l_vendor_site_party_id => '|| l_vendor_site_party_id
613 , 9
614 );
615 INV_LOG_UTIL.trace
616 ( 'rajesh p_header_id => '|| p_header_id
617 , 9
618 );
619 INV_LOG_UTIL.trace
620 ( 'icx_session_id = > '|| FND_GLOBAL.session_id
621 , 9
622 );
623 INV_LOG_UTIL.trace
624 ( 'p_global_flag => '|| p_global_flag
625 , 9
626 );
627 INV_LOG_UTIL.trace
628 ( '*** Start inserting into GT tables '
629 , 9
630 );
631
632 INV_LOG_UTIL.trace
633 ( 'l_event_type_code => '|| l_event_type_code
634 , 9
635 );
636 INV_LOG_UTIL.trace
637 ( 'l_entity_code => '|| l_entity_code
638 , 9
639 );
640 INV_LOG_UTIL.trace
641 ( 'l_event_class_code => ' || l_event_class_code
642 , 9
643 );
644 INV_LOG_UTIL.trace
645 ( 'l_vendor_org_id => '|| l_vendor_org_id
646 , 9
647 );
648 INV_LOG_UTIL.trace
649 ( 'l_legal_entity_id => ' || l_legal_entity_id
650 , 9
651 );
652 INV_LOG_UTIL.trace
653 ( 'l_set_of_books_id => '|| l_set_of_books_id
654 , 9
655 );
656 END IF;
657
658 /* bug 5081702 Start*/
659 /* get the rate type from the OU for global blanket*/
660 IF p_global_flag = 'Y'
661 THEN
662 SELECT
663 default_rate_type
664 INTO
665 l_rate_type
666 FROM
667 po_system_parameters_all
668 WHERE NVL(org_id,-99) = NVL(p_org_id,-99);
669 END IF ;
670 /* bug 5081702 End*/
671
672 INV_LOG_UTIL.trace
673 ( 'l_rate_type => '|| l_rate_type
674 , 9
675 );
676
677 --Rajesh start inserting GT
678
679 INV_LOG_UTIL.trace
680 ( 'Etax: clearing existing records from GT '
681 , 9
682 );
683 /* 5084307 - Start */
684 /* delete from ZX_TRX_HEADERS_GT if records already exist to
685 to prevent duplicate records from being entered */
686 /*bug#7120486 delete statement is moved at end after calling ebtax API */
687 /* 5084307 - end */
688
689 /*5488006 - Start */
690 /* delete from ZX_TRANSACTION_LINES_GT and ZX_ITM_DISTRIBUTIONS_GT
691 before inserting records to prevent duplicate insertion of records -
692 this caused errors in tax calculation */
693 /*bug#7120486 delete statement is moved at end after calling ebtax API */
694
695 /*5488006 - End */
696
697
698 INSERT INTO ZX_TRX_HEADERS_GT
699 ( internal_organization_id
700 , application_id
701 , entity_code
702 , event_class_code
703 , tax_event_type_code
704 , event_type_code
705 , trx_id
706 , trx_date
707 , trx_currency_code
708 , currency_conversion_date
709 , currency_conversion_rate
710 , currency_conversion_type
711 , PRECISION
712 , legal_entity_id
713 , quote_flag
714 , ledger_id
715 , rounding_ship_from_party_id
716 /*
717 , rounding_ship_to_party_id
718 , ship_third_pty_acct_id
719 , ship_third_pty_acct_site_id
720 , bill_third_pty_acct_id
721 , bill_third_pty_acct_site_id
722 */
723 , provnl_tax_determination_date
724 , document_sub_type
725 , trx_number
726 , icx_session_id
727 )
728 SELECT
729 /* bug 5081702 Start*/
730 --poh.org_id
731 NVL(l_vendor_org_id, poh.org_id)
732 , l_application_id
733 , l_entity_code
734 , l_event_class_code
735 , 'PURCHASE TRANSACTION'
736 , l_event_type_code
737 , poh.po_header_id
738 , p_transaction_date
739 , poh.currency_code
740 --, poh.rate_date
741 , NVL(p_transaction_date, poh.rate_date)
742 , poh.rate
743 --, poh.rate_type
744 , NVL(l_rate_type, poh.rate_type)
745 , fc.PRECISION
746 --, ood.legal_entity
747 , l_legal_entity_id
748 , 'Y'
749 --, ood.set_of_books_id
750 , l_set_of_books_id
751 , l_vendor_party_id
752 /*
753 , poh.vendor_id
754 , poh.vendor_id
755 , poh.vendor_site_id
756 , poh.vendor_id
757 , poh.vendor_site_id
758 */
759 , p_transaction_date
760 , poh.type_lookup_code
761 , poh.segment1
762 , FND_GLOBAL.session_id
763 FROM
764 po_headers_all poh
765 , fnd_currencies fc
766 WHERE poh.currency_code = fc.currency_code
767 AND poh.po_header_id = p_header_id;
768
769
770 /*bug 5081702 End */
771
772 INSERT INTO ZX_TRANSACTION_LINES_GT
773 ( application_id
774 , entity_code
775 , event_class_code
776 , trx_level_type
777 , line_level_action
778 , line_amt
779 , trx_line_gl_date
780 , line_amt_includes_tax_flag
781 , trx_line_quantity
782 , uom_code
783 --, ship_to_party_id
784 , ship_from_party_id
785 , ship_from_party_site_id
786 , unit_price
787 , trx_line_type
788 , trx_line_date
789 , product_id
790 , ship_to_location_id
791 , trx_id
792 , trx_line_id
793 , line_class
794 , product_org_id
795 -- , bill_to_party_site_id
796 , BILL_TO_LOCATION_ID
797 , ship_from_location_id
798 )
799 SELECT
800 l_application_id
801 , l_entity_code
802 , l_event_class_code
803 , 'SHIPMENT'
804 , 'CREATE'
805 , p_po_price
806 , p_transaction_date
807 , 'N'
808 , 1
809 , p_uom_code
810 --, p_ship_to_organization_id
811 , l_vendor_party_id
812 , l_vendor_site_party_id
813 , p_po_price
814 , 'ITEM'
815 , p_transaction_date
816 , p_item_id
817 , l_ship_to_location_id
818 , po_header_id
819 , p_transaction_id
820 , 'INVOICE'
821 , p_ship_to_organization_id
822 -- , l_vendor_site_id
823 , bill_to_location_id
824 , l_ship_from_location_id
825 FROM po_headers_all
826 WHERE po_header_id = p_header_id;
827
828 INSERT INTO ZX_ITM_DISTRIBUTIONS_GT
829 ( application_id
830 , entity_code
831 , event_class_code
832 -- , event_type_code
833 , trx_id
834 , trx_level_type
835 , dist_level_action
836 , trx_line_dist_date
837 , trx_line_dist_amt
838 , trx_line_dist_qty
839 , trx_line_quantity
840 , trx_line_id
841 , trx_line_dist_id
842 )
843 VALUES
844 ( l_application_id
845 , l_entity_code
846 , l_event_class_code
847 -- , l_event_type_code
848 , p_header_id
849 , 'SHIPMENT'
850 , 'CREATE'
851 , p_transaction_date
852 , p_po_price
853 , 1
854 , 1
855 , p_transaction_id
856 , p_transaction_id
857 );
858
859
860 -- End Rajesh
861 IF (l_debug = 1)
862 THEN
863 INV_LOG_UTIL.trace
864 ( 'Inserted into Tax Global temp tables','INV_THIRD_PARTY_STOCK_PVT'
865 , 9
866 );
867 INV_LOG_UTIL.trace
868 ( 'Calling INV_AP_TAX_ENGINE_MDTR.Calculate_Tax'
869 , 9
870 );
871
872 END IF;
873
874
875 INV_AP_TAX_ENGINE_MDTR.Calculate_Tax
876 ( x_return_status => l_return_status
877 , x_msg_count => l_msg_count
878 , x_msg_data => l_msg_data
879 );
880
881 IF (l_debug = 1)
882 THEN
883 INV_LOG_UTIL.trace
884 ( ' returned '||l_return_status,'INV_THIRD_PARTY_STOCK_PVT'
885 , 9
886 );
887 INV_LOG_UTIL.trace
888 ( 'l_msg_count => '|| l_msg_count
889 , 9
890 );
891 INV_LOG_UTIL.trace
892 ( 'l_msg_data => '|| l_msg_data
893 , 9
894 );
895
896
897 END IF;
898
899 IF l_return_status = FND_API.G_RET_STS_SUCCESS
900 THEN
901
902 OPEN tax_csr_type_nrec;
903 LOOP
904 FETCH tax_csr_type_nrec INTO
905 x_nonrecoverable_tax;
906
907 IF tax_csr_type_nrec%NOTFOUND
908 THEN
909 EXIT;
910 END IF;
911 END LOOP;
912 CLOSE tax_csr_type_nrec;
913
914 -- Recoverable tax
915 OPEN tax_csr_type_rec;
916 LOOP
917 FETCH tax_csr_type_rec INTO
918 x_recoverable_tax;
919
920 IF tax_csr_type_rec%NOTFOUND
921 THEN
922 EXIT;
923 END IF;
924 END LOOP;
925 CLOSE tax_csr_type_rec;
926
927 /* Bug 5530358 - Start */
928 /* tax rate was stored in tax_recovery_rate column.
929 The cursor stores the tax values in the correct columns */
930 OPEN tax_csr_type_rate;
931 LOOP
932 FETCH tax_csr_type_rate
933 INTO
934 x_tax_recovery_rate
935 , x_tax_rate;
936 /* Bug 5530358 - End */
937
938 IF tax_csr_type_rate%NOTFOUND
939 THEN
940 EXIT;
941 END IF;
942 END LOOP;
943 CLOSE tax_csr_type_rate;
944
945 -- rajesh after loop
946 IF (l_debug = 1)
947 THEN
948 INV_LOG_UTIL.trace
949 ( 'x_nonrecoverable_tax is '||x_nonrecoverable_tax,'INV_THIRD_PARTY_STOCK_PVT'
950 , 9
951 );
952 INV_LOG_UTIL.trace
953 ( 'x_recoverable_tax is '||x_recoverable_tax,'INV_THIRD_PARTY_STOCK_PVT'
954 , 9
955 );
956 INV_LOG_UTIL.trace
957 ( 'x_tax_rate is '||x_tax_rate,'INV_THIRD_PARTY_STOCK_PVT'
958 , 9
959 );
960 INV_LOG_UTIL.trace
961 ( 'Return status is '||l_return_status,'INV_THIRD_PARTY_STOCK_PVT'
962 , 9
963 );
964 END IF;
965
966 /*Fixed for bug#7120486
967 Based on ebTax team input calling program should clear GT tables when
968 processing line by line. GT tables are cleared automatically when
969 rollback or commit is issues but when more than one lines are processed in
970 one transaction then the GT tables still have previous record hence it tries to
971 calculate tax for previous line again which result in unique constraint violation
972 in ebTax table.
973 we have to clear following tables :
974 ZX_TRX_HEADERS_GT
975 ZX_TRANSACTION_LINES_GT
976 ZX_ITM_DISTRIBUTIONS_GT
977 */
978
979 DELETE FROM ZX_TRX_HEADERS_GT
980 WHERE APPLICATION_ID = l_application_id
981 AND ENTITY_CODE = l_entity_code
982 AND EVENT_CLASS_CODE = l_event_class_code
983 AND TRX_ID = p_header_id;
984
985 DELETE FROM ZX_TRANSACTION_LINES_GT
986 WHERE APPLICATION_ID = l_application_id
987 AND ENTITY_CODE = l_entity_code
988 AND EVENT_CLASS_CODE = l_event_class_code
989 AND TRX_ID = p_header_id;
990
991 DELETE FROM ZX_ITM_DISTRIBUTIONS_GT
992 WHERE APPLICATION_ID = l_application_id
993 AND ENTITY_CODE = l_entity_code
994 AND EVENT_CLASS_CODE = l_event_class_code
995 AND TRX_ID = p_header_id;
996
997 /*Fix#7120486 end */
998
999 /*5488006 - Start */
1000 DELETE FROM zx_rec_nrec_dist_gt
1001 WHERE application_id = 201
1002 AND entity_code = l_entity_code
1003 AND trx_id = p_header_id
1004 AND event_class_code = l_event_class_code ;
1005 /*5488006 - End */
1006
1007
1008 ELSE
1009 IF (l_debug = 1)
1010 THEN
1011 INV_LOG_UTIL.trace
1012 ( 'eBtax returned failure','INV_THIRD_PARTY_STOCK_PVT'
1013 , 9
1014 );
1015 END IF;
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 IF (l_debug = 1)
1022 THEN
1023 INV_LOG_UTIL.trace
1024 ( 'NO_DATA_FOUND exception','INV Calculate_tax'
1025 , 9
1026 );
1027 END IF;
1028
1029 IF tax_csr_type_rec%ISOPEN
1030 THEN
1031 CLOSE tax_csr_type_rec;
1032 END IF;
1033 IF tax_csr_type_nrec%ISOPEN
1034 THEN
1035 CLOSE tax_csr_type_nrec;
1036 END IF;
1037 IF tax_csr_type_rate%ISOPEN
1038 THEN
1039 CLOSE tax_csr_type_rate;
1040 END IF;
1041
1042 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_TAX_SETUP');
1043 FND_MESSAGE.Set_Token('SuppName',p_vendor_name);
1044 FND_MESSAGE.Set_Token('SiteCode',p_vendor_site);
1045 FND_MSG_PUB.ADD;
1046 g_error_code := 'INV_CONS_SUP_NO_TAX_SETUP' ;
1047 RAISE FND_API.G_EXC_ERROR;
1048
1049 WHEN OTHERS THEN
1050 INV_LOG_UTIL.trace
1051 ( 'SQLERRM '||SQLERRM|| ' SQLCODE '||SQLCODE ,'INV_THIRD_PARTY_STOCK_PVT'
1052 , 9
1053 );
1054
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056
1057 END Calculate_Tax;
1058
1059 --========================================================================
1060 -- FUNCTION : Get_Consumed_Amt PRIVATE
1061 -- PARAMETERS : p_header_id PO Header
1062 -- : p_rate Exchange rate if foreign blanket
1063 -- : p_currency_flag Flag to indicate foreign blanket
1064 -- COMMENT : This function sums up all the consumption amounts
1065 -- in MCT that are not processed
1066 --========================================================================
1067 FUNCTION Get_Consumed_Amt
1068 ( p_header_id IN NUMBER
1069 , p_rate IN NUMBER
1070 , p_currency_flag IN VARCHAR2
1071 ) RETURN NUMBER
1072 IS
1073
1074 --=================
1075 -- VARIABLES
1076 --=================
1077
1078 l_item_id NUMBER;
1079 l_uom_code VARCHAR2(25);
1080 l_primary_uom VARCHAR2(25);
1081 l_organization_id NUMBER;
1082 l_purchasing_uom VARCHAR2(25);
1083 l_conv_qty NUMBER;
1084 l_total_cons_qty NUMBER;
1085 l_primary_qty NUMBER;
1086 l_unit_price NUMBER;
1087 l_debug NUMBER ;
1088
1089 --=================
1090 -- CURSORS
1091 --=================
1092 CURSOR cons_csr_type IS
1093 SELECT
1094 mmt.inventory_item_id
1095 , mmt.organization_id
1096 /* Bug 5395579 - Start */
1097 /* mct.blanket_price is used for getting the consumed amt.
1098 Using mmt.transaction_cost will multiply the consumed amt by the function
1099 UOM if the pur UOM and function UOM are different*/
1100 --, mmt.transaction_cost
1101 , mct.blanket_price
1102 , SUM(mct.net_qty)
1103 FROM
1104 mtl_material_transactions mmt
1105 , mtl_consumption_transactions mct
1106 WHERE mmt.transaction_id = mct.transaction_id
1107 AND mct.consumption_processed_flag IN ('N','E')
1108 AND mmt.transaction_source_type_id = 1
1109 AND mmt.transaction_action_id = 6
1110 AND mmt.transaction_source_id = p_header_id
1111 GROUP BY mmt.inventory_item_id,mmt.organization_id,mct.blanket_price;--mmt.transaction_cost;
1112
1113 /* Bug 5395579 - End */
1114
1115 CURSOR uom_csr_type IS
1116 SELECT
1117 pol.unit_meas_lookup_code
1118 FROM
1119 po_lines_all pol
1120 WHERE pol.po_header_id = p_header_id
1121 AND pol.item_id = l_item_id
1122 AND ROWNUM = 1;
1123
1124
1125 BEGIN
1126
1127 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1128
1129 IF (l_debug = 1)
1130 THEN
1131 INV_LOG_UTIL.trace
1132 ( '>> Entering Get Consumed Amt','INV_THIRD_PARTY_STOCK_PVT'
1133 , 9
1134 );
1135 END IF;
1136
1137 -- Compute the total amount of consumption txns with processed
1138 -- flag of 'N'. Since the get_total API from PO only
1139 -- takes into account the released amounts , we need to compute
1140 -- the transactions that are not yet released, but waiting to be
1141 -- run to create the consumption advice.
1142
1143 OPEN cons_csr_type;
1144 LOOP
1145 FETCH cons_csr_type
1146 INTO
1147 l_item_id
1148 , l_organization_id
1149 , l_unit_price
1150 , l_primary_qty;
1151
1152 IF cons_csr_type%NOTFOUND
1153 THEN
1154 EXIT;
1155 END IF;
1156
1157 OPEN uom_csr_type;
1158 LOOP
1159 FETCH uom_csr_type
1160 INTO
1161 l_purchasing_uom;
1162
1163 IF uom_csr_type%NOTFOUND
1164 THEN
1165 EXIT;
1166 END IF;
1167
1168 l_primary_uom := INV_THIRD_PARTY_STOCK_UTIL.Get_Primary_UOM
1169 ( p_inventory_item_id=> l_item_id
1170 , p_organization_id => l_organization_id
1171 );
1172
1173 IF l_primary_uom <> NVL(l_purchasing_uom,l_primary_uom)
1174 THEN
1175 -- Convert the qty to purchasing UOM, the UOM's are different
1176
1177 IF (l_debug = 1)
1178 THEN
1179 INV_LOG_UTIL.trace
1180 ( 'l_primary_uom => '|| l_primary_uom, NULL
1181 , 9
1182 );
1183 INV_LOG_UTIL.trace
1184 ( 'l_purchasing_uom => '|| l_purchasing_uom, NULL
1185 , 9
1186 );
1187
1188
1189 INV_LOG_UTIL.trace
1190 ( '>> UOM is different','INV_THIRD_PARTY_STOCK_PVT'
1191 , 9
1192 );
1193 END IF;
1194
1195 l_conv_qty := INV_CONVERT.inv_um_convert
1196 ( item_id => l_item_id
1197 , PRECISION => 5
1198 , from_quantity => l_primary_qty
1199 , from_unit => NULL
1200 , to_unit => NULL
1201 , from_name => l_primary_uom
1202 , to_name => l_purchasing_uom
1203 );
1204
1205 IF l_conv_qty IS NULL OR l_conv_qty < 0
1206 THEN
1207 l_conv_qty := 0;
1208 END IF;
1209 ELSE
1210 -- UOM is the same;no conversion required
1211 l_conv_qty := l_primary_qty;
1212 END IF;
1213
1214 END LOOP;
1215 CLOSE uom_csr_type;
1216
1217 l_total_cons_qty := NVL(l_conv_qty,0)*NVL(l_unit_price,0)+
1218 NVL(l_total_cons_qty,0);
1219 IF (l_debug = 1)
1220 THEN
1221 INV_LOG_UTIL.trace
1222 ( '>>Total qty from MCT:'||l_total_cons_qty,'INV_THIRD_PARTY_STOCK_PVT'
1223 , 9
1224 );
1225 END IF;
1226 END LOOP;
1227 CLOSE cons_csr_type;
1228
1229 -- If the blanket currency is not the same as functional currency,
1230 -- do the currency conversion.
1231
1232 IF NVL(p_currency_flag,'N') = 'Y'
1233 THEN
1234 l_total_cons_qty := (l_total_cons_qty/NVL(p_rate,1));
1235
1236 IF (l_debug = 1)
1237 THEN
1238 INV_LOG_UTIL.trace
1239 ( '>>Total converted:'||l_total_cons_qty,'INV_THIRD_PARTY_STOCK_PVT'
1240 , 9
1241 );
1242 END IF;
1243 END IF;
1244
1245 IF (l_debug = 1)
1246 THEN
1247 INV_LOG_UTIL.trace
1248 ( '<< Exiting Get Consumed Amt','INV_THIRD_PARTY_STOCK_PVT'
1249 , 9
1250 );
1251 END IF;
1252
1253 RETURN l_total_cons_qty;
1254
1255 EXCEPTION
1256
1257 WHEN NO_DATA_FOUND THEN
1258 RETURN 0;
1259
1260 WHEN OTHERS THEN
1261 RETURN 0;
1262
1263 END Get_Consumed_Amt;
1264
1265 --========================================================================
1266 -- PROCEDURE : Get_Total_Blanket_Amt PRIVATE
1267 -- PARAMETERS : p_po_header_id PO Header
1268 -- : p_exchange_rate Exch. rate if blanket is in diff. curreny
1269 -- than the functional currency.
1270 -- : p_ccode_flag Flag to indicate it is foreign curr. blanket
1271 -- : x_released_amt Released amount against the blanket
1272 -- : x_consumed_amt Consumed amount against the blanket
1273 -- : x_amount_limit Amount limit for the blanket
1274 -- COMMENT : This procedure returns the released amt for the blanket
1275 -- the consumption amounts in MCT that are not processed and
1276 -- the blanket total amount.
1277 --========================================================================
1278 PROCEDURE Get_Total_Blanket_Amt
1279 ( p_po_header_id IN NUMBER
1280 , p_object IN VARCHAR2
1281 , p_exchange_rate IN NUMBER
1282 , p_ccode_flag IN VARCHAR2
1283 , x_released_amt OUT NOCOPY NUMBER
1284 , x_consumed_amt OUT NOCOPY NUMBER
1285 , x_amount_limit OUT NOCOPY NUMBER
1286 )
1287 IS
1288 l_total_amt NUMBER;
1289 l_debug NUMBER ;
1290 BEGIN
1291
1292 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1293
1294 IF (l_debug = 1)
1295 THEN
1296 INV_LOG_UTIL.trace
1297 ( '>> Entering Get Total Blanket Amt','INV_THIRD_PARTY_STOCK_PVT'
1298 , 9
1299 );
1300 END IF;
1301
1302 SELECT
1303 NVL(blanket_total_amount,0)
1304 INTO
1305 l_total_amt
1306 FROM
1307 po_headers_all
1308 WHERE po_header_id = p_po_header_id;
1309
1310 IF l_total_amt > 0
1311 THEN
1312
1313 -- Get the released amounts for the blanket
1314
1315 x_released_amt := INV_PO_THIRD_PARTY_STOCK_MDTR.get_total
1316 ( p_object_type => NVL(p_object,'B')
1317 , p_header_id => p_po_header_id
1318 );
1319
1320 IF (l_debug = 1)
1321 THEN
1322 INV_LOG_UTIL.trace
1323 ( '>> Released Amt:'||x_released_amt,'INV_THIRD_PARTY_STOCK_PVT'
1324 , 9
1325 );
1326 END IF;
1327
1328 -- Get the consumed amounts for the blanket from MCT
1329 x_consumed_amt := INV_THIRD_PARTY_STOCK_PVT.get_consumed_amt
1330 ( p_header_id => p_po_header_id
1331 , p_rate => p_exchange_rate
1332 , p_currency_flag => p_ccode_flag
1333 );
1334
1335 IF (l_debug = 1)
1336 THEN
1337 INV_LOG_UTIL.trace
1338 ( '>> COnsumed Amt:'||x_consumed_amt,'INV_THIRD_PARTY_STOCK_PVT'
1339 , 9
1340 );
1341 END IF;
1342
1343 x_amount_limit := l_total_amt;
1344
1345 ELSE
1346 IF (l_debug = 1)
1347 THEN
1348 INV_LOG_UTIL.trace
1349 ( '>> Blanket does not have amount limit','INV_THIRD_PARTY_STOCK_PVT'
1350 , 9
1351 );
1352 END IF;
1353
1354 x_released_amt := 0;
1355 x_consumed_amt := 0;
1356 x_amount_limit := 0;
1357
1358 END IF;
1359
1360 IF (l_debug = 1)
1361 THEN
1362 INV_LOG_UTIL.trace
1363 ( '<< Exiting Get Total BLanket Amt','INV_THIRD_PARTY_STOCK_PVT'
1364 , 9
1365 );
1366 END IF;
1367
1368 END Get_Total_Blanket_Amt;
1369
1370
1371 --========================================================================
1372 -- PROCEDURE : Get_PO_Info PRIVATE
1373 -- PARAMETERS: p_mtl_transaction_id Material transaction id
1374 -- p_transaction_source_type_id Txn source Type
1375 -- p_inventory_item_id item
1376 -- p_owning_organization_id owning organization
1377 -- p_organization_id Inv. organization
1378 -- p_transaction_quantity Transaction Quantity
1379 -- p_transaction_source_id Txn source
1380 -- p_account_id Accrual account
1381 -- p_item_revision Revision
1382 -- x_po_price PO price
1383 -- x_tax_code_id Tax code
1384 -- x_tax_rate Tax Rate
1385 -- x_tax_recovery_rate Recovery Rate
1386 -- x_recoverable_tax Recoverable Tax
1387 -- x_non_recoverable_tax Non recoverable Tax
1388 -- x_rate Exchange Rate
1389 -- x_rate_type Exchange Rate Type
1390 -- x_unit_price Unit Price -- Bug 4969420
1391 -- COMMENT : This procedure invokes the PO price break procedure to
1392 -- calculate the price for consigned transactions.It also
1393 -- returns the PO header id for the sourced blanket
1394 --========================================================================
1395 PROCEDURE Get_PO_Info
1396 ( p_mtl_transaction_id IN NUMBER
1397 , p_transaction_source_type_id IN NUMBER
1398 , p_transaction_action_id IN NUMBER
1399 , p_inventory_item_id IN NUMBER
1400 , p_owning_organization_id IN NUMBER
1401 , p_xfr_owning_organization_id IN NUMBER
1402 , p_organization_id IN NUMBER
1403 , p_transaction_quantity IN NUMBER
1404 , p_transaction_source_id IN OUT NOCOPY NUMBER
1405 , p_transaction_date IN DATE
1406 , p_account_id IN NUMBER
1407 , p_item_revision VARCHAR2 DEFAULT NULL
1408 , x_po_price OUT NOCOPY NUMBER
1409 , x_tax_code_id OUT NOCOPY NUMBER
1410 , x_tax_rate OUT NOCOPY NUMBER
1411 , x_tax_recovery_rate OUT NOCOPY NUMBER
1412 , x_recoverable_tax OUT NOCOPY NUMBER
1413 , x_non_recoverable_tax OUT NOCOPY NUMBER
1414 , x_rate OUT NOCOPY NUMBER
1415 , x_rate_type OUT NOCOPY VARCHAR2
1416 , x_rate_date OUT NOCOPY DATE
1417 , x_currency_code OUT NOCOPY VARCHAR2
1418 /* bug 4969420 Start */
1419 -- The unit price non-inclusive of recoverable,non-recoverable taxes and the conversions
1420 , x_unit_price OUT NOCOPY NUMBER
1421 /* bug 4969420 End */
1422 )
1423 IS
1424 l_po_price NUMBER;
1425 l_cum_flag BOOLEAN;
1426 l_line_location_id NUMBER;
1427 l_price_break_code VARCHAR2(25);
1428 l_currency_code VARCHAR2(15);
1429 l_item_rev VARCHAR2(3);
1430 l_vendor_site_id NUMBER;
1431 l_document_header_id NUMBER;
1432 l_document_type_code VARCHAR2(25);
1433 l_document_line_num NUMBER;
1434 l_vendor_contact_id NUMBER;
1435 l_vendor_product_num VARCHAR2(25);
1436 l_purchasing_uom VARCHAR2(25);
1437 l_primary_uom VARCHAR2(25);
1438 l_from_uom_code VARCHAR2(25);
1439 l_to_uom_code VARCHAR2(25);
1440 l_multi_org VARCHAR2(1);
1441 l_transaction_source_id NUMBER;
1442 l_document_line_id NUMBER;
1443 l_header_id NUMBER;
1444 l_sob_id NUMBER;
1445 l_conv_price NUMBER;
1446 l_func_currency VARCHAR2(25);
1447 l_conv_type VARCHAR2(25);
1448 l_user_rate NUMBER;
1449 l_rate NUMBER;
1450 l_precision NUMBER;
1451 l_tax_code_id NUMBER;
1452 l_recoverable_tax NUMBER;
1453 l_nonrecoverable_tax NUMBER;
1454 l_org_id NUMBER;
1455 l_tax_rate NUMBER;
1456 l_rate_date DATE;
1457 l_conv_qty NUMBER;
1458 l_uom_rate NUMBER;
1459 l_primary_quantity NUMBER;
1460 l_ship_to_location_id NUMBER;
1461 l_released_amt NUMBER;
1462 l_consumed_amt NUMBER;
1463 l_ccode_flag VARCHAR2(1);
1464 l_bkt_amt_limit NUMBER;
1465 l_purch_uom_price NUMBER;
1466 l_global_flag VARCHAR2(1);
1467 l_object VARCHAR2(1);
1468 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1469 l_vendor_name VARCHAR2(240);
1470 l_vendor_site VARCHAR2(15);
1471 /* price break api change uptake - Bug 5076263 - Start*/
1472 l_api_version NUMBER := 1.0;
1473 l_base_unit_price NUMBER;
1474 l_price_break_id NUMBER;
1475 l_return_status VARCHAR2(1);
1476 l_vendor_id NUMBER;
1477 l_category_id NUMBER;
1478 l_line_type_id NUMBER;
1479 l_supplier_item_num VARCHAR2(25);
1480 /* price break api change uptake - Bug 5076263 - End */
1481 l_calculate_tax_global VARCHAR2(1) ;
1482 BEGIN
1483
1484 IF (l_debug = 1)
1485 THEN
1486 INV_LOG_UTIL.trace
1487 ( '>> Entering Get PO Info ','INV_THIRD_PARTY_STOCK_PVT'
1488 , 9
1489 );
1490 END IF;
1491 g_po_header_id := NULL;
1492
1493 IF (p_transaction_source_type_id=13) AND (p_transaction_action_id = 6)
1494 THEN
1495 -- For correction transaction
1496 -- Get the price from the parent transaction
1497
1498 SELECT
1499 transaction_cost
1500 , transaction_source_id
1501 INTO
1502 l_po_price
1503 , l_header_id
1504 FROM
1505 MTL_MATERIAL_TRANSACTIONS
1506 WHERE transaction_id = p_transaction_source_id;
1507
1508 ELSE
1509
1510 -- For a Transfer to regular stock, the blanket line_id is stored in
1511 -- txn source id column. We use this info to get the po_header_id,
1512 -- price etc and when we return PO info to TM, we update
1513 -- transaction_source_id with the po header id
1514
1515 l_vendor_site_id := p_owning_organization_id;
1516 l_org_id := INV_THIRD_PARTY_STOCK_UTIL.Get_Org_id
1517 ( l_vendor_site_id
1518 );
1519
1520 INV_THIRD_PARTY_STOCK_UTIL.Get_Vendor_Info
1521 ( p_vendor_site_id => l_vendor_site_id
1522 , x_vendor_name => l_vendor_name
1523 , x_vendor_site_code => l_vendor_site
1524 );
1525
1526
1527 l_document_line_id := p_transaction_source_id;
1528
1529 -- This is possibly a call from TM for an implicit transaction
1530 -- Hence , retrieve the blanket info
1531
1532 IF (p_transaction_source_id IS NULL) OR
1533 (p_transaction_action_id<>6)
1534 THEN
1535 IF (l_debug = 1)
1536 THEN
1537 INV_LOG_UTIL.trace('Implicit Txn ','INV_THIRD_PARTY_STOCK_PVT',9);
1538 END IF;
1539
1540 INV_PO_THIRD_PARTY_STOCK_MDTR.Get_Blanket_Number
1541 ( p_inventory_item_id => p_inventory_item_id
1542 , p_item_revision => p_item_revision
1543 , p_vendor_site_id => l_vendor_site_id
1544 , p_organization_id => p_organization_id
1545 , p_transaction_date => TRUNC(p_transaction_date)
1546 , x_document_header_id => l_header_id
1547 , x_document_line_id => l_document_line_id
1548 , x_global_flag => l_global_flag
1549 );
1550
1551 IF (l_debug = 1)
1552 THEN
1553 INV_LOG_UTIL.trace
1554 ( 'Blanket Header and line are:'||l_header_id||' '||l_document_line_id
1555 , 'INV_THIRD_PARTY_STOCK_PVT'
1556 , 9
1557 );
1558
1559 INV_LOG_UTIL.trace
1560 ( 'Global flag is l_global_flag: '||l_global_flag
1561 , 'INV_THIRD_PARTY_STOCK_PVT'
1562 , 9
1563 );
1564 END IF;
1565
1566 -- The following assignment is used for the Consigned error rpt
1567 g_po_header_id := l_header_id ;
1568
1569 -- There is no valid blanket aggrement, hence raise error
1570
1571 IF l_header_id IS NULL
1572 THEN
1573 IF NVL(l_global_flag,'N') = 'Y'
1574 THEN
1575 FND_MESSAGE.Set_Name('INV','INV_CONS_SUP_MANUAL_NUM_CODE');
1576 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
1577 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
1578 FND_MSG_PUB.ADD;
1579 g_error_code := 'INV_CONS_SUP_MANUAL_NUM_CODE' ;
1580 RAISE FND_API.G_EXC_ERROR;
1581 ELSE
1582 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_BPO_EXISTS');
1583 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
1584 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
1585 FND_MSG_PUB.ADD;
1586 g_error_code := 'INV_CONS_SUP_NO_BPO_EXISTS' ;
1587 RAISE FND_API.G_EXC_ERROR;
1588 END IF;
1589 END IF;
1590
1591 END IF;
1592
1593 -- Check to see if there are cumulative discounts defined
1594 -- at the PO line level
1595
1596 SELECT
1597 price_break_lookup_code
1598 , po_line_id
1599 , po_header_id
1600 , tax_code_id
1601 , unit_meas_lookup_code
1602 /* Bug 5076263 - category_id to be passed to get_break_price API - Start*/
1603 , category_id
1604 , line_type_id
1605 , vendor_product_num
1606 /* Bug 5076263 - category_id to be passed to get_break_price API - End*/
1607 INTO
1608 l_price_break_code
1609 , l_document_line_id
1610 , l_header_id
1611 , l_tax_code_id
1612 , l_purchasing_uom
1613 /* Bug 5076263 - category_id, line_type_id to be passed to get_break_price API - Start*/
1614 , l_category_id
1615 , l_line_type_id
1616 , l_supplier_item_num
1617 /* Bug 5076263 - category_id, line_type_id to be passed to get_break_price API - End*/
1618 FROM
1619 po_lines_all
1620 WHERE po_line_id = l_document_line_id;
1621
1622 /* Bug 5076263 - currency_code, vendor_id to be passed to get_break_price API - Start*/
1623 SELECT
1624 currency_code
1625 , vendor_id
1626 INTO
1627 l_currency_code
1628 , l_vendor_id
1629 FROM
1630 po_headers_all
1631 WHERE po_header_id = l_header_id;
1632 /* Bug 5076263 - currency_code, vendor_id to be passed to get_break_price API - End*/
1633
1634 -- Get the primary UOM for the item and check if it same as
1635 -- Purchasing UOM; if not, convert the qty in Purchasing UOM
1636 -- to pass the qty to the Price Break
1637
1638 l_primary_uom := INV_THIRD_PARTY_STOCK_UTIL.Get_Primary_UOM
1639 ( p_inventory_item_id=> p_inventory_item_id
1640 , p_organization_id => p_organization_id
1641 );
1642
1643 l_primary_quantity := p_transaction_quantity;
1644
1645 IF l_primary_uom <> NVL(l_purchasing_uom,l_primary_uom)
1646 THEN
1647 IF (l_debug = 1)
1648 THEN
1649 INV_LOG_UTIL.trace
1650 ( 'l_primary_uom => '|| l_primary_uom, NULL
1651 , 9
1652 );
1653 INV_LOG_UTIL.trace
1654 ( 'l_purchasing_uom => '|| l_purchasing_uom, NULL
1655 , 9
1656 );
1657 END IF;
1658
1659 g_primary_uom := l_primary_uom;
1660 g_purchasing_uom := l_purchasing_uom;
1661
1662
1663 -- Convert the qty to purchasing UOM
1664 l_conv_qty := INV_CONVERT.inv_um_convert
1665 ( item_id => p_inventory_item_id
1666 , PRECISION => 5
1667 , from_quantity => l_primary_quantity
1668 , from_unit => NULL
1669 , to_unit => NULL
1670 , from_name => l_primary_uom
1671 , to_name => l_purchasing_uom
1672 );
1673
1674 -- If there is no conversion, error out
1675
1676 IF l_conv_qty IS NULL OR l_conv_qty < 0
1677 THEN
1678 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_UOM_CONV');
1679 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
1680 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
1681 FND_MSG_PUB.ADD;
1682 g_error_code := 'INV_CONS_SUP_NO_UOM_CONV' ;
1683 RAISE FND_API.G_EXC_ERROR;
1684 END IF;
1685
1686 l_from_uom_code := INV_THIRD_PARTY_STOCK_UTIL.Get_UOM_Code
1687 ( p_unit_of_measure => l_primary_uom
1688 , p_vendor_name => l_vendor_name
1689 , p_vendor_site_code => l_vendor_site
1690 );
1691
1692 l_to_uom_code := INV_THIRD_PARTY_STOCK_UTIL.Get_UOM_Code
1693 ( p_unit_of_measure => l_purchasing_uom
1694 , p_vendor_name => l_vendor_name
1695 , p_vendor_site_code => l_vendor_site
1696 );
1697
1698 INV_CONVERT.inv_um_conversion
1699 ( item_id => p_inventory_item_id
1700 , from_unit => l_from_uom_code
1701 , to_unit => l_to_uom_code
1702 , uom_rate => l_uom_rate
1703 );
1704
1705 -- If there is no conversion rate, error out
1706
1707 IF l_uom_rate IS NULL OR l_uom_rate < 0
1708 THEN
1709 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_UOM_CONV');
1710 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
1711 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
1712 FND_MSG_PUB.ADD;
1713 g_error_code := 'INV_CONS_SUP_NO_UOM_CONV' ;
1714 RAISE FND_API.G_EXC_ERROR;
1715 END IF;
1716
1717 ELSE -- Both UOM are same;no conversion is required
1718
1719 l_to_uom_code := INV_THIRD_PARTY_STOCK_UTIL.Get_UOM_Code
1720 ( p_unit_of_measure => l_purchasing_uom
1721 , p_vendor_name => l_vendor_name
1722 , p_vendor_site_code => l_vendor_site
1723 );
1724
1725 l_conv_qty := l_primary_quantity;
1726 l_uom_rate := 1;
1727
1728 END IF;
1729
1730 IF l_price_break_code = 'CUMULATIVE'
1731 THEN
1732 l_cum_flag := TRUE;
1733 ELSE
1734 l_cum_flag := FALSE;
1735 END IF;
1736
1737 l_ship_to_location_id :=
1738 INV_THIRD_PARTY_STOCK_UTIL.get_location(p_organization_id);
1739
1740 -- If the transaction type is 'Transfer to regular stock',
1741 -- call the price break API to calculate the PO price.
1742 /* get break price API change updtake - Bug 5076263 */
1743 /* get_break_price API has changed */
1744 /* INV_PO_THIRD_PARTY_STOCK_MDTR.get_break_price
1745 ( p_order_quantity => l_conv_qty
1746 , p_ship_to_org => p_organization_id
1747 , p_ship_to_loc => NVL(l_ship_to_location_id,p_organization_id)
1748 , p_po_line_id => l_document_line_id
1749 , p_cum_flag => l_cum_flag
1750 , p_need_by_date => p_transaction_date
1751 , p_line_location_id => l_line_location_id
1752 , x_po_price => l_po_price
1753 );
1754 */
1755 IF (l_debug = 1)
1756 THEN
1757 INV_LOG_UTIL.trace
1758 ( 'Before Price break the Price is '||l_po_price
1759 , 'INV_THIRD_PARTY_STOCK_PVT'
1760 , 9
1761 );
1762 END IF;
1763
1764 INV_PO_THIRD_PARTY_STOCK_MDTR.get_break_price
1765 ( p_api_version => l_api_version
1766 , p_order_quantity => l_conv_qty
1767 , p_ship_to_org => p_organization_id
1768 , p_ship_to_loc => NVL(l_ship_to_location_id,p_organization_id)
1769 , p_po_line_id => l_document_line_id
1770 , p_cum_flag => l_cum_flag
1771 , p_need_by_date => p_transaction_date
1772 , p_line_location_id => l_line_location_id
1773 , p_contract_id => NULL
1774 , p_org_id => l_org_id
1775 , p_supplier_id => l_vendor_id
1776 , p_supplier_site_id => l_vendor_site_id
1777 , p_creation_date => p_transaction_date
1778 , p_order_header_id => NULL
1779 , p_order_line_id => NULL
1780 , p_line_type_id => l_line_type_id
1781 , p_item_revision => l_item_rev
1782 , p_item_id => p_inventory_item_id
1783 , p_category_id => l_category_id
1784 , p_supplier_item_num => l_supplier_item_num
1785 , p_uom => l_purchasing_uom
1786 , p_in_price => NULL
1787 , p_currency_code => l_currency_code
1788 , x_base_unit_price => l_base_unit_price
1789 , x_price_break_id => l_price_break_id
1790 , x_price => l_po_price
1791 , x_return_status => l_return_status
1792 );
1793 /* get break price API change updtake - Bug 5076263 - End */
1794
1795 IF (l_debug = 1)
1796 THEN
1797 INV_LOG_UTIL.trace
1798 ( 'After Price break the Price is '||l_po_price
1799 , 'INV_THIRD_PARTY_STOCK_PVT'
1800 , 9
1801 );
1802 END IF;
1803
1804 /* Bug 4969420 Start*/
1805 /* Storing Unit price */
1806 x_unit_price := l_po_price ;
1807 /* Bug 4969420 - End*/
1808
1809 IF l_po_price IS NULL
1810 THEN
1811 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_BPO_EXISTS');
1812 FND_MSG_PUB.ADD;
1813 RAISE FND_API.G_EXC_ERROR;
1814 END IF;
1815
1816 -- Call the tax API to calculate nonrecoverable tax
1817
1818 x_tax_code_id := l_tax_code_id;
1819
1820 l_calculate_tax_global := NULL ;
1821 IF INV_PO_THIRD_PARTY_STOCK_MDTR.is_global(l_header_id)
1822 THEN
1823 l_calculate_tax_global := 'Y' ;
1824 ELSE
1825 l_calculate_tax_global := 'N' ;
1826 END IF;
1827
1828 INV_THIRD_PARTY_STOCK_PVT.Calculate_Tax
1829 ( p_header_id => l_header_id
1830 , p_line_id => l_document_line_id
1831 , p_org_id => l_org_id
1832 , p_item_id => p_inventory_item_id
1833 , p_need_by_date => p_transaction_date
1834 , p_ship_to_organization_id => p_organization_id
1835 , p_account_id => p_account_id
1836 , p_tax_code_id => x_tax_code_id
1837 , p_transaction_quantity => l_conv_qty
1838 , p_po_price => l_po_price
1839 , p_vendor_name => l_vendor_name
1840 , p_vendor_site => l_vendor_site
1841 , p_transaction_id => p_mtl_transaction_id
1842 , p_uom_code => l_to_uom_code
1843 , p_transaction_date => p_transaction_date
1844 , p_global_flag => l_calculate_tax_global
1845 , x_tax_rate => x_tax_rate
1846 , x_tax_recovery_rate => x_tax_recovery_rate
1847 , x_recoverable_tax => x_recoverable_tax
1848 , x_nonrecoverable_tax => x_non_recoverable_Tax
1849 );
1850
1851 -- x_recoverable_tax := 0;
1852 -- x_non_recoverable_tax :=0;
1853
1854 IF (l_debug = 1)
1855 THEN
1856 INV_LOG_UTIL.trace
1857 ( 'x_non_recoverable_tax is '||x_non_recoverable_tax
1858 , 9
1859 );
1860 END IF;
1861
1862 l_po_price := l_po_price + NVL(x_non_recoverable_tax,0);
1863
1864 IF (l_debug = 1)
1865 THEN
1866 INV_LOG_UTIL.trace
1867 ( 'Price after tax calc is '||l_po_price
1868 , 'INV_THIRD_PARTY_STOCK_PVT'
1869 , 9
1870 );
1871 END IF;
1872
1873 -- Convert the unit po price from purchasing UOM to primary UOM. THe
1874 -- unit price stored in MMT is based on the unit for primary UOM
1875
1876 l_purch_uom_price := l_po_price * ABS(l_conv_qty);
1877 l_po_price := l_po_price * l_uom_rate;
1878
1879 IF (l_debug = 1)
1880 THEN
1881 INV_LOG_UTIL.trace
1882 ( 'conversion rate for UOM is '||l_uom_rate
1883 , 'INV_THIRD_PARTY_STOCK_PVT'
1884 , 9
1885 );
1886
1887 INV_LOG_UTIL.trace
1888 ( 'Price after conversion of UOM is '||l_po_price
1889 , 'INV_THIRD_PARTY_STOCK_PVT'
1890 , 9
1891 );
1892 END IF;
1893
1894 -- Get the functional currency
1895
1896 SELECT
1897 fsp.set_of_books_id
1898 , glb.currency_code
1899 , glc.PRECISION
1900 INTO
1901 l_sob_id
1902 , l_func_currency
1903 , l_precision
1904 FROM
1905 financials_system_params_all fsp
1906 , gl_sets_of_books glb
1907 , gl_currencies glc
1908 WHERE fsp.set_of_books_id = glb.set_of_books_id
1909 AND glb.currency_code = glc.currency_code
1910 AND NVL(fsp.org_id,-99) = NVL(l_org_id,-99);
1911
1912 -- Get the currency code from the blanket PO
1913
1914 SELECT
1915 currency_code
1916 , rate_type
1917 , rate
1918 , rate_date
1919 INTO
1920 l_currency_code
1921 , l_conv_type
1922 , l_user_rate
1923 , l_rate_date
1924 FROM
1925 po_headers_all
1926 WHERE po_header_id = l_header_id;
1927
1928 -- If the currency code of the blanket is different than the
1929 -- functional currency, convert to functional currency since
1930 -- the price that is stored in MMT is in fucntional currency.
1931
1932 IF l_func_currency <> NVL(l_currency_code,l_func_currency)
1933 THEN
1934 -- IF it is a global agreement, get the conversion rate from
1935 -- the Purchasing options.
1936
1937 IF (l_debug = 1)
1938 THEN
1939 INV_LOG_UTIL.trace('Curr. conv ','INV_THIRD_PARTY_STOCK_PVT',9);
1940 END IF;
1941
1942 IF INV_PO_THIRD_PARTY_STOCK_MDTR.is_global(l_header_id)
1943 THEN
1944
1945 l_object := 'G';
1946 l_rate_date := p_transaction_date;
1947
1948 IF (l_debug = 1)
1949 THEN
1950 INV_LOG_UTIL.trace('Blanket is GA','INV_THIRD_PARTY_STOCK_PVT',9);
1951 END IF;
1952
1953 SELECT
1954 default_rate_type
1955 INTO
1956 l_conv_type
1957 FROM
1958 po_system_parameters_all
1959 WHERE NVL(org_id,-99) = NVL(l_org_id,-99);
1960
1961 IF l_conv_type IS NULL
1962 THEN
1963 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_RATE_SETUP');
1964 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
1965 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
1966 FND_MSG_PUB.ADD;
1967 g_error_code := 'INV_CONS_SUP_NO_RATE_SETUP';
1968 RAISE FND_API.G_EXC_ERROR;
1969 END IF;
1970
1971 END IF;
1972
1973 IF (l_debug = 1)
1974 THEN
1975 INV_LOG_UTIL.trace
1976 ( '>> Rate date and type are: '||l_conv_type||' '||l_rate_date||
1977 l_currency_code||' '||l_func_currency
1978 , 'INV_THIRD_PARTY_STOCK_PVT'
1979 , 9
1980 );
1981 END IF;
1982
1983 INV_THIRD_PARTY_STOCK_PVT.Get_Conversion_Rate
1984 ( p_set_of_books_id => l_sob_id
1985 , p_from_currency => l_currency_code
1986 , p_to_currency => l_func_currency
1987 , p_conversion_date => NVL(l_rate_date,SYSDATE)
1988 , p_conversion_type => l_conv_type
1989 , p_amount => l_po_price
1990 , p_user_rate => l_user_rate
1991 , p_vendor_name => l_vendor_name
1992 , p_vendor_site => l_vendor_site
1993 , p_quantity => p_transaction_quantity
1994 , x_converted_amount => l_conv_price
1995 , x_conversion_rate => l_rate
1996 );
1997
1998 l_po_price := l_conv_price;
1999 l_ccode_flag := 'Y';
2000
2001 IF (l_debug = 1)
2002 THEN
2003 INV_LOG_UTIL.trace
2004 ( '>> PO price as OUT :'||l_po_price
2005 , 'INV_THIRD_PARTY_STOCK_PVT'
2006 , 9
2007 );
2008 END IF;
2009
2010 END IF;
2011
2012 -- Get the total blanket amount from the PO if entered
2013
2014 INV_THIRD_PARTY_STOCK_PVT.Get_Total_Blanket_Amt
2015 ( p_po_header_id => l_header_id
2016 , p_object => NVL(l_object,'B')
2017 , p_exchange_rate => l_rate
2018 , p_ccode_flag => NVL(l_ccode_flag,'N')
2019 , x_released_amt => l_released_amt
2020 , x_consumed_amt => l_consumed_amt
2021 , x_amount_limit => l_bkt_amt_limit
2022 );
2023
2024 IF (l_debug = 1)
2025 THEN
2026 INV_LOG_UTIL.trace
2027 ( '>> Amount limit:'||l_bkt_amt_limit
2028 ||'>> Released Amount: '||l_released_amt
2029 ||'>> Consumed Amount: '||l_consumed_amt
2030 ||'>> PO Price: '||l_purch_uom_price
2031 , 'INV_THIRD_PARTY_STOCK_PVT'
2032 , 9
2033 );
2034 END IF;
2035
2036 -- If there is a amount limit specified in the blanket for the PO,
2037 -- check needs to be made to verify if the amount of the consumption
2038 -- exceeds the released_amount plus the txns in MCT for which there
2039 -- is no consumption advice created . If the validation fails, we need
2040 -- to fail the transaction and return the error message to TM.
2041
2042 IF (NVL(l_bkt_amt_limit,0) > 0) AND
2043 (NVL(l_released_amt,0)+NVL(l_consumed_amt,0)+
2044 NVL(l_purch_uom_price,0) > l_bkt_amt_limit)
2045 THEN
2046 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_AMT_AGREED_FAIL');
2047 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
2048 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
2049 FND_MSG_PUB.ADD;
2050 g_error_code := 'INV_CONS_SUP_AMT_AGREED_FAIL' ;
2051 RAISE FND_API.G_EXC_ERROR;
2052 END IF;
2053 END IF;
2054
2055 x_po_price := l_po_price;
2056 p_transaction_source_id := l_header_id;
2057 x_rate_type := l_conv_type;
2058 x_rate := l_rate;
2059 x_currency_code := l_currency_code;
2060 x_rate_date := l_rate_date;
2061
2062
2063 IF (l_debug = 1)
2064 THEN
2065 INV_LOG_UTIL.trace
2066 ( '<< Exiting Get PO Info','INV_THIRD_PARTY_STOCK_PVT'
2067 , 9
2068 );
2069 END IF;
2070
2071
2072 END Get_PO_Info;
2073
2074
2075 --========================================================================
2076 -- PROCEDURE : Get_Account PRIVATE
2077 -- PARAMETERS: p_mtl_transaction_id Material transaction id
2078 -- p_transaction_source_type_id Txn Source Type
2079 -- p_transaction_action_id Txn action
2080 -- p_transaction_source_id Txn source
2081 -- p_inventory_item_id item
2082 -- p_owning_organization_id owning organization
2083 -- p_xfr_owning_organization_id Transfer owning organization
2084 -- p_organization_id Inv. organization
2085 -- p_vendor_id Vendor Id
2086 -- x_accrual_account_id Accrual account
2087 -- x_charge_account_id Charge account
2088 -- x_variance_account_id Variance account
2089 -- COMMENT : Get the accounts.
2090 --========================================================================
2091 PROCEDURE Get_Account
2092 ( p_mtl_transaction_id IN NUMBER
2093 , p_transaction_source_type_id IN NUMBER
2094 , p_transaction_action_id IN NUMBER
2095 , p_transaction_source_id IN NUMBER
2096 , p_inventory_item_id IN NUMBER
2097 , p_owning_organization_id IN NUMBER
2098 , p_xfr_owning_organization_id IN NUMBER
2099 , p_organization_id IN NUMBER
2100 , p_vendor_id IN NUMBER
2101 , x_accrual_account_id OUT NOCOPY NUMBER
2102 , x_charge_account_id OUT NOCOPY NUMBER
2103 , x_variance_account_id OUT NOCOPY NUMBER
2104 )
2105 IS
2106 l_coa_id NUMBER;
2107 l_vendor_site_id NUMBER;
2108 l_transaction_source_id NUMBER;
2109 l_charge_success BOOLEAN := TRUE;
2110 l_budget_success BOOLEAN := TRUE;
2111 l_accrual_success BOOLEAN := TRUE;
2112 l_variance_success BOOLEAN := TRUE;
2113 l_bom_resource_id NUMBER;
2114 l_bom_cost_element_id NUMBER;
2115 l_category_id NUMBER;
2116 l_destination_type_code VARCHAR2(50) := 'INVENTORY';
2117 l_deliver_to_location_id NUMBER;
2118 l_destination_organization_id NUMBER ;
2119 l_destination_subinventory VARCHAR2(50):= NULL;
2120 l_expenditure_type VARCHAR2(50):= NULL;
2121 l_expenditure_organization_id NUMBER := NULL;
2122 l_expenditure_item_date DATE;
2123 l_item_id NUMBER ;
2124 l_line_type_id NUMBER ;
2125 l_result_billable_flag VARCHAR2(50) :=NULL;
2126 l_agent_id NUMBER :=NULL;
2127 l_project_id NUMBER;
2128 l_from_type_lookup_code VARCHAR2(50);
2129 l_from_header_id NUMBER;
2130 l_from_line_id NUMBER;
2131 l_task_id NUMBER;
2132 l_deliver_to_person_id NUMBER;
2133 l_type_lookup_code VARCHAR2(50) := 'BLANKET';
2134 l_vendor_id NUMBER ;
2135 l_wip_entity_id NUMBER;
2136 l_wip_entity_type VARCHAR2(50);
2137 l_wip_line_id NUMBER;
2138 l_wip_repetitive_schedule_id NUMBER;
2139 l_wip_operation_seq_num NUMBER;
2140 l_wip_resource_seq_num NUMBER;
2141 l_po_encumberance_flag VARCHAR2(50);
2142 l_gl_encumbered_date DATE;
2143 x_code_combination_id NUMBER;
2144 x_budget_account_id NUMBER;
2145 l_award_id NUMBER DEFAULT NULL ;
2146 l_charge_account_flex VARCHAR2(2000);
2147 l_budget_account_flex VARCHAR2(2000);
2148 l_accrual_account_flex VARCHAR2(2000);
2149 l_variance_account_flex VARCHAR2(2000);
2150 l_charge_account_desc VARCHAR2(2000);
2151 l_budget_account_desc VARCHAR2(2000);
2152 l_accrual_account_desc VARCHAR2(2000);
2153 l_variance_account_desc VARCHAR2(2000);
2154 l_charge_field_name VARCHAR2(60);
2155 l_budget_field_name VARCHAR2(60);
2156 l_accrual_field_name VARCHAR2(60);
2157 l_variance_field_name VARCHAR2(60);
2158 l_charge_desc_field_name VARCHAR2(60);
2159 l_budget_desc_field_name VARCHAR2(60);
2160 l_accrual_desc_field_name VARCHAR2(60);
2161 l_variance_desc_field_name VARCHAR2(60);
2162 l_progress VARCHAR2(3) := '001';
2163 l_new_ccid NUMBER;
2164 l_ccid_returned BOOLEAN := FALSE;
2165 l_header_att1 VARCHAR2(150) := NULL;
2166 l_header_att2 VARCHAR2(150) := NULL;
2167 l_header_att3 VARCHAR2(150) := NULL;
2168 l_header_att4 VARCHAR2(150) := NULL;
2169 l_header_att5 VARCHAR2(150) := NULL;
2170 l_header_att6 VARCHAR2(150) := NULL;
2171 l_header_att7 VARCHAR2(150) := NULL;
2172 l_header_att8 VARCHAR2(150) := NULL;
2173 l_header_att9 VARCHAR2(150) := NULL;
2174 l_header_att10 VARCHAR2(150) := NULL;
2175 l_header_att11 VARCHAR2(150) := NULL;
2176 l_header_att12 VARCHAR2(150) := NULL;
2177 l_header_att13 VARCHAR2(150) := NULL;
2178 l_header_att14 VARCHAR2(150) := NULL;
2179 l_header_att15 VARCHAR2(150) := NULL;
2180 l_line_att1 VARCHAR2(150) := NULL;
2181 l_line_att2 VARCHAR2(150) := NULL;
2182 l_line_att3 VARCHAR2(150) := NULL;
2183 l_line_att4 VARCHAR2(150) := NULL;
2184 l_line_att5 VARCHAR2(150) := NULL;
2185 l_line_att6 VARCHAR2(150) := NULL;
2186 l_line_att7 VARCHAR2(150) := NULL;
2187 l_line_att8 VARCHAR2(150) := NULL;
2188 l_line_att9 VARCHAR2(150) := NULL;
2189 l_line_att10 VARCHAR2(150) := NULL;
2190 l_line_att11 VARCHAR2(150) := NULL;
2191 l_line_att12 VARCHAR2(150) := NULL;
2192 l_line_att13 VARCHAR2(150) := NULL;
2193 l_line_att14 VARCHAR2(150) := NULL;
2194 l_line_att15 VARCHAR2(150) := NULL;
2195 l_fb_error_msg VARCHAR2(2000);
2196 wf_itemkey VARCHAR2(80) := NULL;
2197 po_encumberance_flag VARCHAR2(2) := 'N';
2198 l_new_ccid_generated BOOLEAN := FALSE;
2199 l_shipment_att1 VARCHAR2(150);
2200 l_shipment_att2 VARCHAR2(150);
2201 l_shipment_att3 VARCHAR2(150) ;
2202 l_shipment_att4 VARCHAR2(150) ;
2203 l_shipment_att5 VARCHAR2(150) ;
2204 l_shipment_att6 VARCHAR2(150) ;
2205 l_shipment_att7 VARCHAR2(150) ;
2206 l_shipment_att8 VARCHAR2(150) ;
2207 l_shipment_att9 VARCHAR2(150) ;
2208 l_shipment_att10 VARCHAR2(150) ;
2209 l_shipment_att11 VARCHAR2(150) ;
2210 l_shipment_att12 VARCHAR2(150) ;
2211 l_shipment_att13 VARCHAR2(150) ;
2212 l_shipment_att14 VARCHAR2(150) ;
2213 l_shipment_att15 VARCHAR2(150) ;
2214 l_distribution_att1 VARCHAR2(150) ;
2215 l_distribution_att2 VARCHAR2(150) ;
2216 l_distribution_att3 VARCHAR2(150) ;
2217 l_distribution_att4 VARCHAR2(150) ;
2218 l_distribution_att5 VARCHAR2(150) ;
2219 l_distribution_att6 VARCHAR2(150) ;
2220 l_distribution_att7 VARCHAR2(150);
2221 l_distribution_att8 VARCHAR2(150);
2222 l_distribution_att9 VARCHAR2(150);
2223 l_distribution_att10 VARCHAR2(150) ;
2224 l_distribution_att11 VARCHAR2(150) ;
2225 l_distribution_att12 VARCHAR2(150) ;
2226 l_distribution_att13 VARCHAR2(150) ;
2227 l_distribution_att14 VARCHAR2(150) ;
2228 l_distribution_att15 VARCHAR2(150) ;
2229 l_accrual_account_id NUMBER;
2230 l_variance_account_id NUMBER;
2231 l_charge_account_id NUMBER;
2232 l_debug NUMBER ;
2233 l_vendor_name VARCHAR2(240);
2234 l_vendor_site VARCHAR2(15);
2235
2236 BEGIN
2237
2238 l_transaction_source_id := p_transaction_source_id;
2239 l_destination_organization_id := p_organization_id;
2240 l_item_id := p_inventory_item_id;
2241 l_vendor_site_id := p_owning_organization_id;
2242 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2243
2244 IF (l_debug = 1)
2245 THEN
2246 INV_LOG_UTIL.trace
2247 ( '>> Entering Get Account','INV_THIRD_PARTY_STOCK_PVT'
2248 , 9
2249 );
2250 END IF;
2251
2252 IF (p_transaction_source_type_id=13) AND (p_transaction_action_id = 6)
2253 THEN
2254 -- Get the account from the parent transaction
2255 SELECT
2256 distribution_account_id
2257 INTO
2258 l_accrual_account_id
2259 FROM
2260 mtl_material_transactions
2261 WHERE transaction_id = p_transaction_source_id;
2262 ELSE
2263 -- Get the chart of accounts id to pass to the Accounting engine
2264
2265 SELECT
2266 gl.chart_of_accounts_id
2267 INTO
2268 l_coa_id
2269 FROM
2270 hr_organization_information hoi
2271 , hr_all_organization_units hou
2272 , gl_sets_of_books gl
2273 WHERE hoi.organization_id = hou.organization_id
2274 AND hoi.org_information1 = TO_CHAR(gl.set_of_books_id)
2275 AND hoi.org_information_context='Accounting Information'
2276 AND hoi.organization_id = p_organization_id;
2277
2278 -- Call PO Account Generator to generate accrual account
2279
2280 IF (l_debug = 1)
2281 THEN
2282 INV_LOG_UTIL.trace('Got coaid ','INV_THIRD_PARTY_STOCK_PVT',9);
2283 END IF;
2284
2285 INV_PO_THIRD_PARTY_STOCK_MDTR.Generate_Account
2286 ( p_charge_success => l_charge_success
2287 , p_budget_success => l_budget_success
2288 , p_accrual_success => l_accrual_success
2289 , p_variance_success => l_variance_success
2290 , p_code_combination_id => x_code_combination_id
2291 , p_charge_account_id => l_charge_account_id
2292 , p_budget_account_id => x_budget_account_id
2293 , p_accrual_account_id => l_accrual_account_id
2294 , p_variance_account_id => l_variance_account_id
2295 , p_charge_account_flex => l_charge_account_flex
2296 , p_budget_account_flex => l_budget_account_flex
2297 , p_accrual_account_flex => l_accrual_account_flex
2298 , p_variance_account_flex => l_variance_account_flex
2299 , p_charge_account_desc => l_charge_account_desc
2300 , p_budget_account_desc => l_budget_account_desc
2301 , p_accrual_account_desc => l_accrual_account_desc
2302 , p_variance_account_desc => l_variance_account_desc
2303 , p_coa_id => l_coa_id
2304 , p_bom_resource_id => l_bom_resource_id
2305 , p_bom_cost_element_id => l_bom_cost_element_id
2306 , p_category_id => l_category_id
2307 , p_destination_type_code => l_destination_type_code
2308 , p_deliver_to_location_id => l_deliver_to_location_id
2309 , p_destination_organization_id => l_destination_organization_id
2310 , p_destination_subinventory => l_destination_subinventory
2311 , p_expenditure_type => l_expenditure_type
2312 , p_expenditure_organization_id => l_expenditure_organization_id
2313 , p_expenditure_item_date => l_expenditure_item_date
2314 , p_item_id => l_item_id
2315 , p_line_type_id => l_line_type_id
2316 , p_result_billable_flag => l_result_billable_flag
2317 , p_agent_id => l_agent_id
2318 , p_project_id => l_project_id
2319 , p_from_type_lookup_code => l_from_type_lookup_code
2320 , p_from_header_id => l_from_header_id
2321 , p_from_line_id => l_from_line_id
2322 , p_task_id => l_task_id
2323 , p_deliver_to_person_id => l_deliver_to_person_id
2324 , p_type_lookup_code => l_type_lookup_code
2325 , p_vendor_id => p_vendor_id
2326 , p_wip_entity_id => l_wip_entity_id
2327 , p_wip_entity_type => l_wip_entity_type
2328 , p_wip_line_id => l_wip_line_id
2329 , p_wip_repetitive_schedule_id => l_wip_repetitive_schedule_id
2330 , p_wip_operation_seq_num => l_wip_operation_seq_num
2331 , p_wip_resource_seq_num => l_wip_resource_seq_num
2332 , p_po_encumberance_flag => l_po_encumberance_flag
2333 , p_gl_encumbered_date => l_gl_encumbered_date
2334 , p_wf_itemkey => wf_itemkey
2335 , p_new_combination => l_new_ccid_generated
2336 , p_header_att1 => l_header_att1
2337 , p_header_att2 => l_header_att2
2338 , p_header_att3 => l_header_att3
2339 , p_header_att4 => l_header_att4
2340 , p_header_att5 => l_header_att5
2341 , p_header_att6 => l_header_att6
2342 , p_header_att7 => l_header_att7
2343 , p_header_att8 => l_header_att8
2344 , p_header_att9 => l_header_att9
2345 , p_header_att10 => l_header_att10
2346 , p_header_att11 => l_header_att11
2347 , p_header_att12 => l_header_att12
2348 , p_header_att13 => l_header_att13
2349 , p_header_att14 => l_header_att14
2350 , p_header_att15 => l_header_att15
2351 , p_line_att1 => l_line_att1
2352 , p_line_att2 => l_line_att2
2353 , p_line_att3 => l_line_att3
2354 , p_line_att4 => l_line_att4
2355 , p_line_att5 => l_line_att5
2356 , p_line_att6 => l_line_att6
2357 , p_line_att7 => l_line_att7
2358 , p_line_att8 => l_line_att8
2359 , p_line_att9 => l_line_att9
2360 , p_line_att10 => l_line_att10
2361 , p_line_att11 => l_line_att11
2362 , p_line_att12 => l_line_att12
2363 , p_line_att13 => l_line_att13
2364 , p_line_att14 => l_line_att14
2365 , p_line_att15 => l_line_att15
2366 , p_shipment_att1 => l_shipment_att1
2367 , p_shipment_att2 => l_shipment_att2
2368 , p_shipment_att3 => l_shipment_att3
2369 , p_shipment_att4 => l_shipment_att4
2370 , p_shipment_att5 => l_shipment_att5
2371 , p_shipment_att6 => l_shipment_att6
2372 , p_shipment_att7 => l_shipment_att7
2373 , p_shipment_att8 => l_shipment_att8
2374 , p_shipment_att9 => l_shipment_att9
2375 , p_shipment_att10 => l_shipment_att10
2376 , p_shipment_att11 => l_shipment_att11
2377 , p_shipment_att12 => l_shipment_att12
2378 , p_shipment_att13 => l_shipment_att13
2379 , p_shipment_att14 => l_shipment_att14
2380 , p_shipment_att15 => l_shipment_att15
2381 , p_distribution_att1 => l_distribution_att1
2382 , p_distribution_att2 => l_distribution_att2
2383 , p_distribution_att3 => l_distribution_att3
2384 , p_distribution_att4 => l_distribution_att4
2385 , p_distribution_att5 => l_distribution_att5
2386 , p_distribution_att6 => l_distribution_att6
2387 , p_distribution_att7 => l_distribution_att7
2388 , p_distribution_att8 => l_distribution_att8
2389 , p_distribution_att9 => l_distribution_att9
2390 , p_distribution_att10 => l_distribution_att10
2391 , p_distribution_att11 => l_distribution_att11
2392 , p_distribution_att12 => l_distribution_att12
2393 , p_distribution_att13 => l_distribution_att13
2394 , p_distribution_att14 => l_distribution_att14
2395 , p_distribution_att15 => l_distribution_att15
2396 , p_fb_error_msg => l_fb_error_msg
2397 , p_Award_id => l_award_id
2398 , p_vendor_site_id => l_vendor_site_id
2399 );
2400 END IF;
2401
2402 x_charge_account_id := l_charge_account_id;
2403 x_accrual_account_id := l_accrual_account_id;
2404 x_variance_account_id := l_variance_account_id;
2405
2406 IF (l_debug = 1)
2407 THEN
2408 INV_LOG_UTIL.trace
2409 ( 'acct generated is'||x_accrual_account_id
2410 , 'INV_THIRD_PARTY_STOCK_PVT'
2411 , 9
2412 );
2413 END IF;
2414
2415 IF (l_fb_error_msg IS NOT NULL)
2416 THEN
2417 SELECT
2418 pov.vendor_name
2419 , povs.vendor_site_code
2420 INTO
2421 l_vendor_name
2422 , l_vendor_site
2423 FROM
2424 po_vendors pov
2425 , po_vendor_sites_all povs
2426 WHERE pov.vendor_id = povs.vendor_id
2427 AND pov.vendor_id = p_vendor_id
2428 AND povs.vendor_site_id = l_vendor_site_id;
2429
2430 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_GEN_ACCT');
2431 FND_MESSAGE.Set_Token('SuppName',l_vendor_name);
2432 FND_MESSAGE.Set_Token('SiteCode',l_vendor_site);
2433 FND_MSG_PUB.ADD;
2434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2435
2436 END IF;
2437
2438
2439 EXCEPTION
2440 WHEN OTHERS THEN
2441 g_error_code := 'INV_CONS_SUP_GEN_ACCT' ;
2442
2443 IF (l_debug = 1)
2444 THEN
2445 INV_LOG_UTIL.trace
2446 ( 'OTHERS error - Get_Account' ,
2447 'INV_THIRD_PARTY_STOCK_PVT'
2448 , 9
2449 );
2450 END IF;
2451 RAISE;
2452
2453 END Get_Account;
2454
2455
2456 --========================================================================
2457 -- PROCEDURE : Process_Financial_Info PUBLIC
2458 -- PARAMETERS: p_mtl_transaction_id Material transaction id
2459 -- p_transaction_source_type_id Txn source Type
2460 -- p_transaction_action_id Txn action
2461 -- p_inventory_item_id item
2462 -- p_owning_organization_id owning organization
2463 -- p_xfr_owning_organization_id transfer owning organization
2464 -- p_organization_id Inv. organization
2465 -- p_transaction_quantity Transaction Quantity
2466 -- p_transaction_source_id Txn source
2467 -- p_item_revision Revision
2468 -- x_po_price PO price
2469 -- x_account_id Account
2470 -- x_rate Exchange Rate
2471 -- x_rate_type Exchange Rate type
2472 -- x_rate_date Exchange rate date
2473 -- x_currency_code Currency Code
2474 -- x_message_count
2475 -- x_message_data
2476 -- x_return_status status
2477 -- p_secondary_transaction_qty Transaction Quantity
2478 -- in Secondary UOM
2479 -- COMMENT : Process Finanical information for consigned transactions
2480 -- This procedure is invoked by the Inventory TM when
2481 -- processing consigned transactions.
2482 -- CHANGE : INVCONV START PBAMB
2483 -- Added a new parameter p_secondary_transaction_qty
2484 -- to support process attributes for Inventory Convergence
2485 --========================================================================
2486 PROCEDURE Process_Financial_Info
2487 ( p_mtl_transaction_id IN NUMBER
2488 , p_rct_transaction_id IN NUMBER
2489 , p_transaction_source_type_id IN NUMBER
2490 , p_transaction_action_id IN NUMBER
2491 , p_inventory_item_id IN NUMBER
2492 , p_owning_organization_id IN NUMBER
2493 , p_xfr_owning_organization_id IN NUMBER
2494 , p_organization_id IN NUMBER
2495 , p_transaction_quantity IN NUMBER
2496 , p_transaction_date IN DATE
2497 , p_transaction_source_id IN OUT NOCOPY NUMBER
2498 , p_item_revision IN VARCHAR2
2499 , x_po_price OUT NOCOPY NUMBER
2500 , x_account_id OUT NOCOPY NUMBER
2501 , x_rate OUT NOCOPY NUMBER
2502 , x_rate_type OUT NOCOPY VARCHAR2
2503 , x_rate_date OUT NOCOPY DATE
2504 , x_currency_code OUT NOCOPY VARCHAR2
2505 , x_msg_count OUT NOCOPY NUMBER
2506 , x_msg_data OUT NOCOPY VARCHAR2
2507 , x_return_status OUT NOCOPY VARCHAR2
2508 , p_secondary_transaction_qty IN NUMBER -- INVCONV
2509 )
2510 IS
2511 l_transaction_source_id NUMBER;
2512 l_po_header_id NUMBER;
2513 l_vendor_site_id NUMBER;
2514 l_tax_code_id NUMBER;
2515 l_tax_rate NUMBER;
2516 l_tax_recovery_rate NUMBER;
2517 l_recoverable_tax NUMBER;
2518 l_non_recoverable_tax NUMBER;
2519 l_rate NUMBER;
2520 l_rate_type VARCHAR2(30);
2521 l_rate_date DATE;
2522 l_currency_code VARCHAR2(25);
2523 l_charge_account_id NUMBER;
2524 l_variance_account_id NUMBER;
2525 l_org_id NUMBER;
2526 l_vendor_id NUMBER;
2527 l_appl_id NUMBER;
2528 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2529 /* bug 4969420 - start */
2530 /*unit price to be stored in MCT*/
2531 l_unit_price NUMBER;
2532 /* bug 4969420 - end */
2533
2534 BEGIN
2535
2536 x_return_status := FND_API.G_RET_STS_SUCCESS;
2537 l_transaction_source_id := p_transaction_source_id;
2538 l_po_header_id := p_transaction_source_id;
2539
2540 -- If the transaction type is a 'Transfer to regular stock correction'
2541 -- the transfer owning organization is the vendor site.
2542 -- In all other cases, for implicit and explicit 'Transfer to regular
2543 -- stock' transactions, owning_organization_id is the vendor site id.
2544
2545 g_error_code := NULL ;
2546 g_po_header_id := NULL ;
2547
2548 IF (l_debug = 1)
2549 THEN
2550 INV_LOG_UTIL.trace('Call from TM >>',
2551 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT',9);
2552 INV_LOG_UTIL.trace('g_calling_action => '||g_calling_action ,9 );
2553 INV_LOG_UTIL.trace('g_error_code => '|| g_error_code,9);
2554 END IF;
2555
2556
2557 IF (p_transaction_source_type_id=13) AND (p_transaction_action_id = 6)
2558 THEN
2559 l_vendor_site_id := p_xfr_owning_organization_id;
2560 ELSE
2561 l_vendor_site_id := p_owning_organization_id;
2562 END IF;
2563
2564 l_org_id := INV_THIRD_PARTY_STOCK_UTIL.Get_Org_Id(l_vendor_site_id);
2565
2566 /* Commented the code for Bug# 7175819 to prevent context switching */
2567 /* INV_THIRD_PARTY_STOCK_UTIL.Set_OU_Context
2568 ( p_org_id => l_org_id);
2569
2570 g_pgm_appl_id := l_appl_id; */
2571
2572 -- Get the vendor id
2573
2574 SELECT
2575 vendor_id
2576 INTO
2577 l_vendor_id
2578 FROM
2579 po_vendor_sites_all
2580 WHERE vendor_site_id = l_vendor_site_id;
2581
2582 -- INVCONV get accoutn for process organiztions from OPM setup.
2583 /*????IF INV_GMI_RSV_BRANCH.Is_Org_Process_Org(l_org_id) THEN
2584 NULL; --INVCONV ???? this will be replaced with the new call of OPM API.
2585 ELSE */
2586 INV_THIRD_PARTY_STOCK_PVT.Get_Account
2587 ( p_mtl_transaction_id => p_mtl_transaction_id
2588 , p_transaction_source_type_id => p_transaction_source_type_id
2589 , p_transaction_action_id => p_transaction_action_id
2590 , p_transaction_source_id => l_transaction_source_id
2591 , p_inventory_item_id => p_inventory_item_id
2592 , p_owning_organization_id => p_owning_organization_id
2593 , p_xfr_owning_organization_id => p_xfr_owning_organization_id
2594 , p_organization_id => p_organization_id
2595 , p_vendor_id => l_vendor_id
2596 , x_accrual_account_id => x_account_id
2597 , x_charge_account_id => l_charge_account_id
2598 , x_variance_account_id => l_variance_account_id
2599 );
2600 ----END IF;
2601
2602 IF (l_debug = 1)
2603 THEN
2604 INV_LOG_UTIL.trace('Account Generated>>','INV_THIRD_PARTY_STOCK_PVT',9);
2605 END IF;
2606
2607 -- Call to get the PO price, po_header_id.
2608
2609 INV_THIRD_PARTY_STOCK_PVT.Get_PO_Info
2610 ( p_mtl_transaction_id => p_mtl_transaction_id
2611 , p_transaction_source_type_id => p_transaction_source_type_id
2612 , p_transaction_action_id => p_transaction_action_id
2613 , p_inventory_item_id => p_inventory_item_id
2614 , p_owning_organization_id => p_owning_organization_id
2615 , p_xfr_owning_organization_id => p_xfr_owning_organization_id
2616 , p_organization_id => p_organization_id
2617 , p_transaction_quantity => ABS(p_transaction_quantity)
2618 , p_transaction_source_id => l_po_header_id
2619 , p_transaction_date => p_transaction_date
2620 , p_account_id => x_account_id
2621 , p_item_revision => p_item_revision
2622 , x_po_price => x_po_price
2623 , x_tax_code_id => l_tax_code_id
2624 , x_tax_rate => l_tax_rate
2625 , x_tax_recovery_rate => l_tax_recovery_rate
2626 , x_recoverable_tax => l_recoverable_tax
2627 , x_non_recoverable_tax => l_non_recoverable_tax
2628 , x_rate => l_rate
2629 , x_rate_type => l_rate_type
2630 , x_rate_date => l_rate_date
2631 , x_currency_code => l_currency_code
2632 /* bug 4969420 - fetching the unit price - start */
2633 , x_unit_price => l_unit_price
2634 /* bug 4969420 - end */
2635 );
2636
2637 IF (l_debug = 1)
2638 THEN
2639 INV_LOG_UTIL.trace('PO Info fetched >>','INV_THIRD_PARTY_STOCK_PVT',9);
2640 END IF;
2641
2642 -- Call to insert to consumptions table.
2643 IF (l_debug = 1)
2644 THEN
2645 INV_LOG_UTIL.trace('g_calling_action => '|| g_calling_action ,9);
2646 END IF;
2647
2648 IF g_calling_action IS NULL OR
2649 g_calling_action <> 'D'
2650 THEN
2651 IF (l_debug = 1)
2652 THEN
2653 INV_LOG_UTIL.trace('Record_Consumption' ,
2654 'INV_THIRD_PARTY_STOCK_PVT ',9);
2655 END IF;
2656
2657 /* INVCONV START PBAMB - Passing a new parameter p_secondary_transaction_qty to
2658 Record Consumption procedure to support process attributes for inventory convergence project*/
2659 INV_THIRD_PARTY_STOCK_PVT.Record_Consumption
2660 ( p_mtl_transaction_id => p_mtl_transaction_id
2661 , p_transaction_source_type_id => p_transaction_source_type_id
2662 , p_transaction_action_id => p_transaction_action_id
2663 , p_transaction_source_id => l_transaction_source_id
2664 , p_transaction_quantity => ABS(p_transaction_quantity)
2665 , p_secondary_transaction_qty => ABS(p_secondary_transaction_qty) /* INVCONV */
2666 , p_tax_code_id => l_tax_code_id
2667 , p_tax_rate => l_tax_rate
2668 , p_tax_recovery_rate => l_tax_recovery_rate
2669 , p_recoverable_tax => l_recoverable_tax
2670 , p_non_recoverable_tax => l_non_recoverable_tax
2671 , p_rate => l_rate
2672 , p_rate_type => l_rate_type
2673 , p_charge_account_id => l_charge_account_id
2674 , p_variance_account_id => l_variance_account_id
2675 /* bug 4969420 - start */
2676 /* passing unit price to Record Consumption */
2677 , p_unit_price => l_unit_price
2678 /* bug 4969420 start */
2679 );
2680
2681 IF (l_debug = 1)
2682 THEN
2683 INV_LOG_UTIL.trace('Inserted to MCT>>','INV_THIRD_PARTY_STOCK_PVT',9);
2684 END IF;
2685
2686 -- Populate the costing table with the details of the txn.
2687 IF (l_debug = 1)
2688 THEN
2689 INV_LOG_UTIL.trace('Populate_Cost_Dtl',
2690 'INV_THIRD_PARTY_STOCK_PVT ',9);
2691 END IF;
2692
2693 -- INVCONV do not populate cost details for process organiztions.
2694 /* ????IF INV_GMI_RSV_BRANCH.Is_Org_Process_Org(l_org_id) THEN
2695 NULL;
2696 ELSE */
2697 INV_THIRD_PARTY_STOCK_PVT.Populate_Cost_Details
2698 ( p_mtl_transaction_id => p_mtl_transaction_id
2699 , p_rct_transaction_id => p_rct_transaction_id
2700 , p_transaction_source_type_id => p_transaction_source_type_id
2701 , p_transaction_action_id => p_transaction_action_id
2702 , p_organization_id => p_organization_id
2703 , p_inventory_item_id => p_inventory_item_id
2704 , p_po_price => x_po_price
2705 );
2706 --END IF;
2707
2708 IF (l_debug = 1)
2709 THEN
2710 INV_LOG_UTIL.trace('Populated CST tbl>>','INV_THIRD_PARTY_STOCK_PVT',9);
2711 END IF;
2712 END IF ; -- Diagnostics
2713
2714 p_transaction_source_id := l_po_header_id;
2715 x_rate := l_rate;
2716 x_rate_type := l_rate_type;
2717 x_currency_code := l_currency_code;
2718 x_rate_date := l_rate_date;
2719
2720 -- The following assignment is used for Consigned Inv error rpt
2721 g_po_header_id := l_po_header_id ;
2722
2723 -- After all the processing is complete, reset the OU context
2724 -- to the original value when the TM invoked the procedure.
2725 -- Reset_OU_Context;
2726
2727 IF (l_debug = 1)
2728 THEN
2729 INV_LOG_UTIL.trace
2730 ( '<< Exiting Process Financial Info ','INV_THIRD_PARTY_STOCK_PVT'
2731 , 9
2732 );
2733 END IF;
2734
2735 EXCEPTION
2736
2737 WHEN FND_API.G_EXC_ERROR THEN
2738 -- Reset_OU_Context;
2739 x_return_status := FND_API.G_RET_STS_ERROR;
2740 -- Get message count and data
2741 FND_MSG_PUB.Count_And_Get
2742 ( p_count => x_msg_count
2743 , p_data => x_msg_data
2744 );
2745 IF (l_debug = 1)
2746 THEN
2747 INV_LOG_UTIL.trace
2748 ( '<< FND_API.G_EXC_ERROR - Process_fin Original API ',
2749 'INV_THIRD_PARTY_STOCK_PVT'
2750 , 9
2751 );
2752 END IF;
2753
2754
2755 WHEN OTHERS THEN
2756 -- Reset_OU_Context;
2757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2758 -- Get message count and data
2759 FND_MSG_PUB.Count_And_Get
2760 ( p_count => x_msg_count
2761 , p_data => x_msg_data
2762 );
2763 IF (l_debug = 1)
2764 THEN
2765 INV_LOG_UTIL.trace
2766 ( '<< OTHERS error - Process_fin Original API ',
2767 'INV_THIRD_PARTY_STOCK_PVT'
2768 , 9
2769 );
2770 INV_LOG_UTIL.trace
2771 ( '<< OTHERS error - Process_fin Original API ',
2772 'INV_THIRD_PARTY_STOCK_PVT'
2773 , 9
2774 );
2775 END IF;
2776
2777
2778 END Process_Financial_Info;
2779 /* INVCONV END */
2780
2781
2782 --========================================================================
2783 -- PROCEDURE : Process_Financial_Info OVERLOAD API
2784 -- PARAMETERS: p_mtl_transaction_id Material transaction id issue
2785 -- p_rct_transaction_id Material transaction rct side
2786 -- p_transaction_source_type_id Txn source Type
2787 -- p_transaction_action_id Txn action
2788 -- p_inventory_item_id item
2789 -- p_owning_organization_id owning organization
2790 -- p_owning_tp_type owning tp type
2791 -- p_organization_id Inv. organization
2792 -- p_transaction_quantity Transaction Quantity
2793 -- p_transaction_source_id Txn source
2794 -- p_item_revision Revision
2795 -- x_po_price PO price
2796 -- x_account_id Accrual Account
2797 -- x_rate Exchange Rate
2798 -- x_rate_type Exchange Rate type
2799 -- x_rate_date Exchange rate date
2800 -- x_currency_code Currency Code
2801 -- x_message_count
2802 -- x_message_data
2803 -- x_return_status status
2804 -- COMMENT : This procedure will be used by the
2805 -- INV Consigned Inventory Diagnostics program
2806 -- This procedure will inturn invoke the process_financial_info
2807 -- to validate the moqd data waiting for ownership transfer
2808 -- transaction process.
2809 -- The process_financial_info API will also be modified
2810 -- to make sure that it does not insert/update
2811 -- any records as such and just perform and return
2812 -- the validation results
2813 --========================================================================
2814 PROCEDURE Process_Financial_Info
2815 ( p_mtl_transaction_id IN NUMBER
2816 , p_rct_transaction_id IN NUMBER
2817 , p_transaction_source_type_id IN NUMBER
2818 , p_transaction_action_id IN NUMBER
2819 , p_inventory_item_id IN NUMBER
2820 , p_owning_organization_id IN NUMBER
2821 , p_xfr_owning_organization_id IN NUMBER
2822 , p_organization_id IN NUMBER
2823 , p_transaction_quantity IN NUMBER
2824 , p_transaction_date IN DATE
2825 , p_transaction_source_id IN OUT NOCOPY NUMBER
2826 , p_item_revision IN VARCHAR2 DEFAULT NULL
2827 , p_calling_action IN VARCHAR2
2828 , x_po_price OUT NOCOPY NUMBER
2829 , x_account_id OUT NOCOPY NUMBER
2830 , x_rate OUT NOCOPY NUMBER
2831 , x_rate_type OUT NOCOPY VARCHAR2
2832 , x_rate_date OUT NOCOPY DATE
2833 , x_currency_code OUT NOCOPY VARCHAR2
2834 , x_msg_count OUT NOCOPY NUMBER
2835 , x_msg_data OUT NOCOPY VARCHAR2
2836 , x_return_status OUT NOCOPY VARCHAR2
2837 , x_error_code OUT NOCOPY VARCHAR2
2838 , x_po_header_id OUT NOCOPY NUMBER
2839 , x_purchasing_UOM OUT NOCOPY VARCHAR2
2840 , x_primary_UOM OUT NOCOPY VARCHAR2
2841 )
2842 IS
2843
2844 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2845 l_secondary_transaction_qty NUMBER := NULL;
2846
2847 BEGIN
2848 IF (l_debug = 1)
2849 THEN
2850 INV_LOG_UTIL.trace
2851 ( '>> IN Process Financial Info - Diagnostics ',
2852 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT'
2853 , 9
2854 );
2855 INV_LOG_UTIL.trace
2856 ( 'p_calling_action => ' || p_calling_action ,'INV_THIRD_PARTY_STOCK_PVT'
2857 , 9
2858 );
2859 END IF;
2860
2861 x_return_status := FND_API.G_RET_STS_SUCCESS;
2862 x_error_code := NULL;
2863 g_calling_action := p_calling_action ;
2864 g_error_code := NULL;
2865 g_po_header_id := NULL ;
2866 x_po_header_id := NULL ;
2867 x_purchasing_UOM := NULL ;
2868 x_primary_UOM := NULL;
2869 g_purchasing_uom := NULL ;
2870 g_primary_uom := NULL ;
2871
2872 -- Call the original API that was released in 11.5.9
2873
2874 IF (l_debug = 1)
2875 THEN
2876 INV_LOG_UTIL.trace
2877 ( 'Calling main Process_Financial_Info ' ,
2878 'from Overloaded API '
2879 , 9
2880 );
2881 END IF;
2882
2883
2884 INV_THIRD_PARTY_STOCK_PVT.Process_Financial_Info
2885 ( p_mtl_transaction_id => p_mtl_transaction_id
2886 , p_rct_transaction_id => p_rct_transaction_id
2887 , p_transaction_source_type_id => p_transaction_source_type_id
2888 , p_transaction_action_id => p_transaction_action_id
2889 , p_inventory_item_id => p_inventory_item_id
2890 , p_owning_organization_id => p_owning_organization_id
2891 , p_xfr_owning_organization_id => p_xfr_owning_organization_id
2892 , p_organization_id => p_organization_id
2893 , p_transaction_quantity => p_transaction_quantity
2894 , p_transaction_date => p_transaction_date
2895 , p_transaction_source_id => p_transaction_source_id
2896 , p_item_revision => p_item_revision
2897 , p_secondary_transaction_qty => l_secondary_transaction_qty
2898 , x_po_price => x_po_price
2899 , x_account_id => x_account_id
2900 , x_rate => x_rate
2901 , x_rate_type => x_rate_type
2902 , x_rate_date => x_rate_date
2903 , x_currency_code => x_currency_code
2904 , x_msg_count => x_msg_count
2905 , x_msg_data => x_msg_data
2906 , x_return_status => x_return_status
2907 );
2908
2909 x_error_code := g_error_code ;
2910 x_po_header_id := g_po_header_id ;
2911 x_purchasing_UOM := g_purchasing_uom ;
2912 x_primary_UOM := g_primary_uom ;
2913
2914 IF (l_debug = 1)
2915 THEN
2916 INV_LOG_UTIL.trace
2917 ( '<< Out of main Process_Financial_Info '|| x_return_status ,
2918 'from Overloaded API '
2919 , 9
2920 );
2921 INV_LOG_UTIL.trace
2922 ( 'x_return_status => '|| x_return_status ,'INV_THIRD_PARTY_STOCK_PVT'
2923 , 9);
2924 INV_LOG_UTIL.trace
2925 ( 'x_error_code =>' || x_error_code ,'INV_THIRD_PARTY_STOCK_PVT'
2926 , 9);
2927
2928 INV_LOG_UTIL.trace
2929 ( 'x_purchasing_UOM=> ' || x_purchasing_UOM ,'INV_THIRD_PARTY_STOCK_PVT'
2930 , 9);
2931 INV_LOG_UTIL.trace
2932 ( 'x_primary_UOM=> ' || x_primary_UOM ,'INV_THIRD_PARTY_STOCK_PVT'
2933 , 9);
2934 INV_LOG_UTIL.trace
2935 ( 'x_po_header_id=> ' || x_po_header_id ,'INV_THIRD_PARTY_STOCK_PVT'
2936 , 9);
2937 END IF;
2938
2939 x_error_code := g_error_code ;
2940
2941 IF x_return_status = fnd_api.g_ret_sts_error
2942 THEN
2943 RAISE fnd_api.g_exc_error;
2944 END IF ;
2945
2946 IF x_return_status = fnd_api.g_ret_sts_unexp_error
2947 THEN
2948 RAISE fnd_api.g_exc_unexpected_error;
2949 END IF;
2950
2951 IF (l_debug = 1)
2952 THEN
2953 INV_LOG_UTIL.trace
2954 ( '<< OUT Process Financial Info - Diagnostics ',
2955 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT'
2956 , 9
2957 );
2958 INV_LOG_UTIL.trace
2959 ( 'x_return_status => '|| x_return_status ,'INV_THIRD_PARTY_STOCK_PVT'
2960 , 9);
2961 INV_LOG_UTIL.trace
2962 ( 'x_error_code =>' || x_error_code ,'INV_THIRD_PARTY_STOCK_PVT'
2963 , 9);
2964 END IF;
2965 g_po_header_id := NULL ;
2966
2967 EXCEPTION
2968
2969 WHEN FND_API.G_EXC_ERROR THEN
2970 -- Reset_OU_Context;
2971 x_return_status := FND_API.G_RET_STS_ERROR;
2972 -- Get message count and data
2973 FND_MSG_PUB.Count_And_Get
2974 ( p_count => x_msg_count
2975 , p_data => x_msg_data
2976 );
2977
2978 IF (l_debug = 1)
2979 THEN
2980 INV_LOG_UTIL.trace
2981 ( 'SQLERRM=> ' || SQLERRM ,
2982 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT' , 9);
2983 END IF;
2984
2985 WHEN fnd_api.g_exc_unexpected_error THEN
2986 -- Reset_OU_Context;
2987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2988 -- Get message count and data
2989 FND_MSG_PUB.Count_And_Get
2990 ( p_count => x_msg_count
2991 , p_data => x_msg_data
2992 );
2993
2994 IF (l_debug = 1)
2995 THEN
2996 INV_LOG_UTIL.trace
2997 ( 'SQLERRM=> ' || SQLERRM ,
2998 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT' , 9);
2999 END IF;
3000
3001
3002 WHEN OTHERS THEN
3003 -- Reset_OU_Context;
3004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3005 -- Get message count and data
3006 FND_MSG_PUB.Count_And_Get
3007 ( p_count => x_msg_count
3008 , p_data => x_msg_data
3009 );
3010 IF (l_debug = 1)
3011 THEN
3012 INV_LOG_UTIL.trace
3013 ( 'SQLERRM=> ' || SQLERRM ,
3014 'INVVTPSB: INV_THIRD_PARTY_STOCK_PVT' , 9);
3015 END IF;
3016
3017
3018 END Process_Financial_Info;
3019
3020 END INV_THIRD_PARTY_STOCK_PVT;