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