[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_PO_MDTR
Source
4 --| Copyright (c) 1998 Oracle Corporation |
1 PACKAGE BODY INV_MGD_MVT_PO_MDTR AS
2 -- $Header: INVPMDRB.pls 120.23 2012/01/05 13:13:43 ntungare ship $
3 --+=======================================================================+
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVPMDRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of INV_MGD_MVT_PO_MDTR |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_PO_Transactions |
16 --| Get_Dropship_SO_Line |
17 --| Get_PO_Details |
18 --| Get_Dropshipment_Details |
19 --| Update_PO_Transactions |
20 --| Get_RTV_Transactions |
21 --| Get_Blanket_Info |
22 --| Get_RMA_Transactions |
23 --| Get_RMA_Details |
24 --| Get_Parent_Mvt |
25 --| Get_IO_Arrival_Txn |
26 --| Get_IO_Arrival_Details |
27 --| |
28 --| HISTORY |
29 --| 16/04/2007 Neelam Soni Bug 5920143. Added support for Include |
30 --| Establishments. |
31 --| 25-Jun-08 kdevadas Bug 6839063 - Modified the cursor in |
32 --| Get_RMA_Details to fetch the |
33 --| ship_to_org_id from the line details |
34 --| rather than the header. The cursor was |
35 --| always picking the default ship_to in |
36 --| the header which is incorrect |
37 --| 05-Aug-08 Ajmittal Bug 7165989 - Movement Statistics RMA |
38 --| Triangulation uptake. |
39 --| Modified procs:Update_PO_transaction, |
40 --| Get_RMA_Details, Get_RMA_Transaction |
41 --| Changed check condition in Update_PO_Txn. |
42 --+=======================================================================+
43
44 --===================
45 -- CONSTANTS
46 --===================
47 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_PO_MDTR.';
48
49
50 --===================
51 -- PRIVATE PROCEDURES
52 --===================
53
54 --========================================================================
55 -- PROCEDURE : Get_PO_Transactions PRIVATE
56 -- PARAMETERS: po_crsr REF cursor
57 -- x_return_status return status
58 -- p_start_date Transaction start date
59 -- p_end_date Transaction end date
60 -- COMMENT :
61 -- This opens the cursor for PO and returns the cursor.
62 --========================================================================
63
64 PROCEDURE Get_PO_Transactions
65 ( po_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.poCurTyp
66 , p_movement_transaction IN
67 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
68 , p_start_date IN DATE
69 , p_end_date IN DATE
70 , x_return_status OUT NOCOPY VARCHAR2
71 )
72 IS
73 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_PO_Transactions';
74
75 BEGIN
76 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
77 THEN
78 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
79 , G_MODULE_NAME || l_procedure_name || '.begin'
80 ,'enter procedure'
81 );
82 END IF;
83
84 x_return_status := 'Y';
85
86 IF po_crsr%ISOPEN THEN
87 CLOSE po_crsr;
88 END IF;
89
90 --Fix performance bug 4912552, use hr_organization_information to replace
91 --org_organization_definitions according to proposal from INV
92 --karthik.gnanamurthy, because inventory organization is already existing
96
93 --in rcv_transactions, so it's not required to validate the organization
94 --again in mtl_parameters or hr_all_organization_units as OOD does
95 IF NVL(p_movement_transaction.creation_method,'A') = 'A' THEN
97 OPEN po_crsr FOR
98 SELECT
99 rcv.transaction_id
100 , rcv.parent_transaction_id
101 , rcv.transaction_type
102 , rcv.po_header_id
103 , rcv.po_line_id
104 , rcv.po_line_location_id
105 , rcv.source_document_code
106 , rcv.vendor_site_id
107 , rcv.transaction_date
108 , rcv.organization_id
109 , rcv.subinventory
110 --Bugfix 13556992: Getting additional information.
111 , rcv.shipment_header_id
112 , rcv.shipment_line_id
113 FROM
114 RCV_TRANSACTIONS rcv
115 , hr_organization_information hoi
116 WHERE rcv.organization_id = hoi.organization_id
117 AND hoi.org_information_context = 'Accounting Information'
118 AND rcv.mvt_stat_status = 'NEW'
119 AND (rcv.transaction_type IN ('RECEIVE','RETURN TO VENDOR','MATCH')
120 OR (rcv.transaction_type = 'CORRECT'
121 AND rcv.destination_type_code = 'RECEIVING'))
122 AND rcv.source_document_code = 'PO'
123 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
124 AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
125 ORDER BY rcv.transaction_id;
126 ELSE
127 OPEN po_crsr FOR
128 SELECT
129 rcv.transaction_id
130 , rcv.parent_transaction_id
131 , rcv.transaction_type
132 , rcv.po_header_id
133 , rcv.po_line_id
134 , rcv.po_line_location_id
135 , rcv.source_document_code
136 , rcv.vendor_site_id
137 , rcv.transaction_date
138 , rcv.organization_id
139 , rcv.subinventory
140 --Bugfix 13556992: Getting additional information.
141 , rcv.shipment_header_id
142 , rcv.shipment_line_id
143 FROM
144 RCV_TRANSACTIONS rcv
145 ,RCV_SHIPMENT_HEADERS rsh
146 ,hr_organization_information hoi
147 WHERE rcv.shipment_header_id = rsh.shipment_header_id
148 AND rcv.organization_id = hoi.organization_id
149 AND hoi.org_information_context = 'Accounting Information'
150 AND rsh.ship_to_org_id = hoi.organization_id
151 AND rcv.mvt_stat_status = 'NEW'
152 AND (rcv.transaction_type IN ('RECEIVE','RETURN TO VENDOR','MATCH')
153 OR (rcv.transaction_type = 'CORRECT'
154 AND rcv.destination_type_code = 'RECEIVING'))
155 AND rcv.source_document_code = 'PO'
156 AND rsh.receipt_num = p_movement_transaction.receipt_num
157 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
158 AND rcv.organization_id = p_movement_transaction.organization_id;
159 END IF;
160
161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
162 THEN
163 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
164 , G_MODULE_NAME || l_procedure_name || '.end'
165 ,'exit procedure'
166 );
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 x_return_status := 'N';
172 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
173 THEN
174 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
175 , G_MODULE_NAME || l_procedure_name||'. Others exception'
176 , 'Exception'
177 );
178 END IF;
179
180 END Get_PO_Transactions;
181
182 --========================================================================
183 -- PROCEDURE : Get_Dropship_SO_Line PRIVATE
184 -- PARAMETERS: p_movement_transaction movement transaction record
185 -- x_drop_ship_source_id
186 -- x_destination_org_id
187 -- COMMENT : Get drop ship so line
188 --========================================================================
189 PROCEDURE Get_Dropship_SO_Line
190 (x_movement_transaction IN OUT NOCOPY
191 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
192 , x_drop_ship_source_id OUT NOCOPY NUMBER
193 , x_destination_org_id OUT NOCOPY NUMBER
194 )
195 IS
196 l_count NUMBER;
197 l_rt_seq NUMBER;
198 l_so_seq NUMBER;
199 l_rcv_transaction_id NUMBER;
200 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Dropship_SO_Line';
201
202 --Check if it's a dropship, and if it's a partial receipt dropship
203 CURSOR l_drpshp_count IS
204 SELECT
205 count(*)
206 FROM
207 oe_drop_ship_sources
208 WHERE po_header_id = x_movement_transaction.po_header_id
209 AND po_line_id = x_movement_transaction.po_line_id
210 AND line_location_id = x_movement_transaction.po_line_location_id
211 GROUP BY line_location_id;
212
213 --Sort rcv transaction id for drop ship
214 CURSOR l_rt IS
215 SELECT
216 transaction_id
217 FROM
218 rcv_transactions
219 WHERE po_header_id = x_movement_transaction.po_header_id
220 AND po_line_id = x_movement_transaction.po_line_id
221 AND po_line_location_id = x_movement_transaction.po_line_location_id
222 AND transaction_type = 'RECEIVE'
223 ORDER BY transaction_id;
224
225 CURSOR l_drpshp_om IS
226 SELECT
227 po_header_id
228 , po_line_id
229 , header_id
230 , line_id
231 , drop_ship_source_id
232 , destination_organization_id
233 FROM
234 OE_DROP_SHIP_SOURCES
235 WHERE po_header_id = x_movement_transaction.po_header_id
236 AND po_line_id = x_movement_transaction.po_line_id
237 AND line_location_id = x_movement_transaction.po_line_location_id
238 ORDER BY line_id;
239
240 BEGIN
244 , G_MODULE_NAME || l_procedure_name || '.begin'
241 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
242 THEN
243 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
245 ,'enter procedure'
246 );
247 END IF;
248
249 --Process dropship info
250 OPEN l_drpshp_count;
251 FETCH l_drpshp_count
252 INTO l_count;
253
254 IF l_drpshp_count%NOTFOUND
255 THEN
256 --not a dropship
257 x_drop_ship_source_id := null;
258 ELSE
259 IF l_count = 1
260 THEN
261 --regular dropship, fetch so header id and line id
262 OPEN l_drpshp_om;
263 FETCH l_drpshp_om
264 INTO
265 x_movement_transaction.po_header_id
266 , x_movement_transaction.po_line_id
267 , x_movement_transaction.order_header_id
268 , x_movement_transaction.order_line_id
269 , x_drop_ship_source_id
270 , x_destination_org_id;
271 CLOSE l_drpshp_om;
272 ELSIF l_count > 1
273 THEN
274 --dropship with multiple receipts
275 l_rt_seq := 0;
276 OPEN l_rt;
277 LOOP
278 FETCH l_rt INTO
279 l_rcv_transaction_id;
280
281 l_rt_seq := l_rt_seq + 1;
282
283 IF l_rcv_transaction_id = x_movement_transaction.rcv_transaction_id
284 THEN
285 EXIT;
286 --Fix bug 5060410, incase no match found, exit anyway to avoice endless loop
287 ELSIF l_rt_seq = l_count
288 THEN
289 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
290 THEN
291 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
292 , G_MODULE_NAME
293 || '.no rt id matched - data problem - exit loop anyway'
294 ,'data has problem');
295 END IF;
296
297 EXIT;
298 END IF;
299 --EXIT WHEN l_rcv_transaction_id = x_movement_transaction.rcv_transaction_id;
300 END LOOP;
301 CLOSE l_rt;
302
303 --SO order line loop
304 l_so_seq := 0;
305 OPEN l_drpshp_om;
306 LOOP
307 FETCH l_drpshp_om
308 INTO
309 x_movement_transaction.po_header_id
310 , x_movement_transaction.po_line_id
311 , x_movement_transaction.order_header_id
312 , x_movement_transaction.order_line_id
313 , x_drop_ship_source_id
314 , x_destination_org_id;
315
316 l_so_seq := l_so_seq + 1;
317 EXIT WHEN l_so_seq = l_rt_seq;
318 END LOOP;
319 CLOSE l_drpshp_om;
320 END IF;
321 END IF;
322 CLOSE l_drpshp_count;
323
324 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
325 THEN
326 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
327 , G_MODULE_NAME || l_procedure_name || '.end'
331
328 ,'exit procedure'
329 );
330 END IF;
332 EXCEPTION
333 WHEN OTHERS THEN
334 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
335 THEN
336 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
337 , G_MODULE_NAME || l_procedure_name||'. Others exception'
338 , 'Exception'
339 );
340 END IF;
341 END Get_Dropship_SO_Line;
342
343 --========================================================================
347 -- COMMENT : Get all the additional data required for PO
344 -- PROCEDURE : Get_PO_Details PRIVATE
345 -- PARAMETERS: x_return_status return status
346 -- p_movement_transaction movement transaction record
348 --========================================================================
349
350 PROCEDURE Get_PO_Details
351 ( p_stat_typ_transaction IN
352 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
353 , x_movement_transaction IN OUT NOCOPY
354 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
355 , x_return_status OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
359 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
360 l_rtv_code VARCHAR2(2);
361 l_rtv_eu_code VARCHAR2(3);
362 l_arrival_code VARCHAR2(3);
363 l_arrival_eu_code VARCHAR2(3);
364 l_parent_period_name VARCHAR2(15);
365 l_parent_id NUMBER;
366 l_correct_qty NUMBER;
367 l_correct_parimary_qty NUMBER;
368 l_drop_ship_source_id NUMBER;
369 l_destination_org_id NUMBER;
370
371 l_source_unit_measure VARCHAR2(25);
372 l_source_uom_code VARCHAR2(3);
373 l_unit_price NUMBER;
374 l_uom_conv_rate NUMBER;
375 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_PO_Details';
376
377 CURSOR po_details IS
378 SELECT
379 po.po_header_id
380 , po.transaction_type
381 , po.transaction_id
382 , po.parent_transaction_id
383 , po.movement_id
384 , po.po_line_id
385 , po.po_line_location_id
386 , po.organization_id
387 , po.currency_code
388 , po.currency_conversion_type
389 , po.currency_conversion_rate
390 , po.currency_conversion_date
391 , poh.vendor_id
392 , poh.vendor_site_id
393 , po.shipment_header_id
394 , po.shipment_line_id
395 , po.invoice_id
396 , rsl.item_id
397 , rsl.item_description
398 , po.uom_code
399 , po.source_doc_unit_of_measure
400 , po.quantity
401 , po.primary_quantity
402 --, nvl(cst.item_cost,0)
403 , poh.fob_lookup_code
404 , poh.ship_to_location_id
405 , NVL(po.po_unit_price,0)
406 , po.country_of_origin_code
407 , po.requisition_line_id
408 , NVL(rsh.freight_carrier_code,'3')
409 , po.po_release_id
410 , poh.type_lookup_code
411 , po.consigned_flag
412 FROM
413 RCV_TRANSACTIONS po
414 , RCV_SHIPMENT_HEADERS rsh
415 , RCV_SHIPMENT_LINES rsl
416 , PO_HEADERS_ALL poh
417 , PO_LINES_ALL pol
418 --, CST_ITEM_COSTS_FOR_GL_VIEW cst
419 WHERE po.shipment_header_id = rsh.shipment_header_id
420 AND rsh.shipment_header_id = rsl.shipment_header_id
421 AND po.shipment_line_id = rsl.shipment_line_id
422 AND po.po_line_id = pol.po_line_id
423 AND poh.po_header_id = pol.po_header_id
424 --AND rsl.to_organization_id = cst.organization_id (+)
425 --AND rsl.item_id = cst.inventory_item_id (+)
426 AND po.transaction_id = x_movement_transaction.rcv_transaction_id;
427
428 --Fix bug 4238031 get uom code for po document
429 CURSOR l_uom IS
430 SELECT
431 muc.uom_code
432 FROM
433 MTL_UOM_CONVERSIONS_VIEW muc
434 WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
435 AND muc.organization_id = x_movement_transaction.organization_id
436 AND muc.unit_of_measure = l_source_unit_measure;
437
438 --Fix bug 4207119
439 CURSOR c_item_cost IS
440 SELECT
441 item_cost
442 FROM
443 CST_ITEM_COSTS_FOR_GL_VIEW
444 WHERE organization_id = x_movement_transaction.organization_id
445 AND inventory_item_id = x_movement_transaction.inventory_item_id;
446
447 CURSOR l_adj IS
448 SELECT
449 period_name
450 FROM
451 MTL_MOVEMENT_STATISTICS
452 WHERE movement_id = l_parent_id;
453
454 --Fix bug 2412655, PO correction transaction
455 --Curor to get corrected quantity
456 CURSOR l_correct_quantity IS
457 SELECT
458 SUM(quantity)
459 , SUM(primary_quantity)
460 FROM
461 rcv_transactions
462 WHERE parent_transaction_id = x_movement_transaction.rcv_transaction_id
463 AND mvt_stat_status = 'NEW'
464 AND transaction_type = 'CORRECT';
465
466 BEGIN
467 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
468 THEN
469 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
470 , G_MODULE_NAME || l_procedure_name || '.begin'
471 ,'enter procedure'
472 );
473 END IF;
474
475 l_stat_typ_transaction := p_stat_typ_transaction;
476 x_return_status := 'Y';
477
478 OPEN po_details;
479 FETCH po_details INTO
480 x_movement_transaction.po_header_id
481 , l_receipt_transaction.transaction_type
482 , x_movement_transaction.rcv_transaction_id
483 , l_receipt_transaction.parent_transaction_id
484 , x_movement_transaction.movement_id
485 , x_movement_transaction.po_line_id
486 , x_movement_transaction.po_line_location_id
487 , x_movement_transaction.organization_id
488 , x_movement_transaction.currency_code
489 , x_movement_transaction.currency_conversion_type
490 , x_movement_transaction.currency_conversion_rate
491 , x_movement_transaction.currency_conversion_date
492 , x_movement_transaction.vendor_id
493 , x_movement_transaction.vendor_site_id
494 , x_movement_transaction.shipment_header_id
498 , x_movement_transaction.item_description
495 , x_movement_transaction.shipment_line_id
496 , x_movement_transaction.invoice_id
497 , x_movement_transaction.inventory_item_id
499 , x_movement_transaction.transaction_uom_code
500 , l_source_unit_measure
501 , x_movement_transaction.transaction_quantity
502 , x_movement_transaction.primary_quantity
503 --, x_movement_transaction.item_cost
504 , x_movement_transaction.delivery_terms
505 , x_movement_transaction.ship_to_site_use_id
506 , l_unit_price
507 , x_movement_transaction.origin_territory_code
508 , x_movement_transaction.requisition_line_id
509 , x_movement_transaction.transport_mode
510 , x_movement_transaction.release_id
511 , x_movement_transaction.type_lookup_code
512 , x_movement_transaction.consigned_flag;
513
514 IF po_details%NOTFOUND
515 THEN
516 CLOSE po_details;
517 x_return_status := 'N';
518
519 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
520 THEN
521 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
522 , G_MODULE_NAME || l_procedure_name || '.end return N'
523 ,'exit procedure'
524 );
525 END IF;
526 RETURN;
527 END IF;
528 CLOSE po_details;
529
530 --
531 -- bug 12844667
532 -- For process Items use the OPM API to derive costs
533 --
534 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(x_movement_transaction.organization_id) = TRUE) THEN
535 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
536 ( p_org_id => x_movement_transaction.organization_id
537 , p_item_id => x_movement_transaction.inventory_item_id
538 , p_transaction_date => x_movement_transaction.transaction_date
539 );
540
541 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
542 THEN
543 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
544 , G_MODULE_NAME || l_procedure_name
545 ,'item_cost from OPM:' || x_movement_transaction.item_cost
546 );
547 END IF;
548 ELSE
549 --Get item cost fix bug 4207119
550 OPEN c_item_cost;
551 FETCH c_item_cost INTO
552 x_movement_transaction.item_cost;
553 CLOSE c_item_cost;
554
555 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
556 THEN
557 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
558 , G_MODULE_NAME || l_procedure_name
559 ,'item_cost from discrete:' || x_movement_transaction.item_cost
560 );
561 END IF;
562 END IF;
563
564 --Fix bug 2412655 PO correction transaction, open correct quantity cursor
565 OPEN l_correct_quantity;
566 FETCH l_correct_quantity
567 INTO
568 l_correct_qty
569 , l_correct_parimary_qty;
570
571 IF l_correct_quantity%NOTFOUND
572 THEN
573 l_correct_qty := 0;
574 l_correct_parimary_qty := 0;
575 CLOSE l_correct_quantity;
576 END IF;
577 CLOSE l_correct_quantity;
578
579 x_movement_transaction.transaction_quantity :=
580 x_movement_transaction.transaction_quantity + NVL(l_correct_qty,0);
581 x_movement_transaction.primary_quantity :=
582 x_movement_transaction.primary_quantity + NVL(l_correct_parimary_qty,0);
583
584 --Get source document uom code
585 OPEN l_uom;
586 FETCH l_uom INTO
587 l_source_uom_code;
588
589 IF l_uom%NOTFOUND
590 THEN
591 l_source_uom_code := x_movement_transaction.transaction_uom_code;
592 END IF;
593 CLOSE l_uom;
594
595 --PO source uom maynot be same as received qty uom,thus when calculate document
596 --line ext value, we need to consider uom conversion
597 IF x_movement_transaction.transaction_uom_code <> l_source_uom_code
598 THEN
599 INV_CONVERT.Inv_Um_Conversion
600 ( from_unit => x_movement_transaction.transaction_uom_code
601 , to_unit => l_source_uom_code
602 , item_id => x_movement_transaction.inventory_item_id
603 , uom_rate => l_uom_conv_rate
604 );
605 ELSE
606 l_uom_conv_rate := 1;
607 END IF;
608
609 --Set document unit price and document line value
610 --This unit price will be for each transaction uom
611 x_movement_transaction.document_unit_price :=
612 NVL(l_unit_price,0) * l_uom_conv_rate;
613 x_movement_transaction.document_line_ext_value :=
614 x_movement_transaction.document_unit_price *
615 NVL(x_movement_transaction.transaction_quantity,0);
616
617 x_movement_transaction.movement_type :='A';
618 x_movement_transaction.document_source_type :='PO';
619
620 IF x_movement_transaction.currency_code IS NULL THEN
621 x_movement_transaction.currency_code :=
622 l_stat_typ_transaction.gl_currency_code;
623 END IF;
624
625 INV_MGD_MVT_UTILS_PKG.Get_Vendor_Info
626 (x_movement_transaction => x_movement_transaction);
627
628 --Get drop ship info (bug 3788843, 5060410)
629 IF l_receipt_transaction.transaction_type = 'RECEIVE'
630 THEN
631 Get_Dropship_SO_Line
632 (x_movement_transaction => x_movement_transaction
633 , x_drop_ship_source_id => l_drop_ship_source_id
634 , x_destination_org_id => l_destination_org_id
635 );
636 END IF;
637
638 --Consigned support
639 IF x_movement_transaction.consigned_flag = 'Y'
640 THEN
641 x_movement_transaction.transaction_nature := '12';
642 ELSE
646 IF (l_receipt_transaction.transaction_type = 'RETURN TO VENDOR')
643 x_movement_transaction.transaction_nature := '11';
644 END IF;
645
647 THEN
648 x_movement_transaction.movement_type := 'D';
649 x_movement_transaction.transaction_nature := '21'; --for both consign and non consign
650 x_movement_transaction.document_source_type := 'RTV';
651 x_movement_transaction.movement_id := null;
652 l_rtv_code := x_movement_transaction.dispatch_territory_code;
653 x_movement_transaction.dispatch_territory_code :=
654 x_movement_transaction.destination_territory_code ;
655 x_movement_transaction.destination_territory_code := l_rtv_code;
656 l_rtv_eu_code := x_movement_transaction.dispatch_territory_eu_code;
657 x_movement_transaction.dispatch_territory_eu_code :=
658 x_movement_transaction.destination_territory_eu_code ;
659 x_movement_transaction.destination_territory_eu_code := l_rtv_eu_code;
660 ELSIF (l_receipt_transaction.transaction_type = 'CORRECT')
661 THEN
662 --Processor process here, the correction transactions are
663 --in a different period from the parent transaction and the
664 --parent transaction are closed already, so the correction
665 --transactions created here should be of movement type 'DA'
666 --or 'AA' depend on the quantity
667 IF x_movement_transaction.transaction_quantity < 0
668 THEN
669 x_movement_transaction.movement_type := 'DA';
670 x_movement_transaction.transaction_quantity :=
671 abs(x_movement_transaction.transaction_quantity);
672 x_movement_transaction.primary_quantity :=
673 abs(x_movement_transaction.primary_quantity);
674 ELSE
675 x_movement_transaction.movement_type := 'AA';
676 END IF;
677
678 --This assignment is used in create movment statistics to keep the parent mvt id
679 x_movement_transaction.movement_id := x_movement_transaction.parent_movement_id;
680 ELSE
681 --regular PO
682 x_movement_transaction.movement_type := 'A';
683 END IF;
684
685 IF NVL(l_drop_ship_source_id , FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
686 THEN
687 x_movement_transaction.movement_type := 'A';
688 x_movement_transaction.document_source_type := 'PO';
689 x_movement_transaction.organization_id := l_destination_org_id;
690 x_movement_transaction.transaction_nature := '17';
691 --Bug:5920143. Triangulation Country and Origin Country are assigned with
692 --proper values for a logical PO Arrival record.
693 x_movement_transaction.triangulation_country_code :=
694 x_movement_transaction.destination_territory_code;
695 IF (x_movement_transaction.origin_territory_code IS null )
696 THEN
697 x_movement_transaction.origin_territory_code :=
698 x_movement_transaction.dispatch_territory_code;
699 END IF;
700 -- If it is a ESL transaction set the drop shipment flag for ESL
701 IF UPPER(x_movement_transaction.stat_type) = 'ESL'
702 THEN
703 x_movement_transaction.esl_drop_shipment_code := 1;
704 END IF;
705
706 x_movement_transaction.document_line_ext_value :=
707 abs(x_movement_transaction.document_line_ext_value);
708 END IF;
709
710 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
711 THEN
712 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
713 , G_MODULE_NAME || l_procedure_name || '.end'
714 ,'exit procedure'
715 );
716 END IF;
717 EXCEPTION
718 WHEN OTHERS THEN
719 x_return_status := 'N';
720 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
721 THEN
722 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
723 , G_MODULE_NAME || l_procedure_name||'. Others exception'
724 , 'Exception'
725 );
726 END IF;
727
728 END Get_PO_Details;
729
730 --========================================================================
731 -- PROCEDURE : Get_DropShipment_Details PRIVATE
732 -- PARAMETERS: x_return_status return status
733 -- p_movement_transaction movement transaction record
734 -- COMMENT : Get all the additional data required for PO
735 --========================================================================
736
737 PROCEDURE Get_DropShipment_Details
738 ( p_stat_typ_transaction IN
739 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
740 , x_movement_transaction IN OUT NOCOPY
741 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
742 , x_return_status OUT NOCOPY VARCHAR2
743 )
744 IS
745 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
746 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
747 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
748 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_DropShipment_Details';
749
750 CURSOR l_drpshp_so_om IS
751 SELECT
752 oola.ship_to_org_id
753 , ooha.fob_point_code
754 , NVL(ooha.freight_terms_code, '3')
755 , oola.line_id
756 , ooha.header_id
757 , ooha.order_number
758 , oola.line_number
759 , oola.ship_from_org_id --keep the organization_id from drop ship PO, but get into new variable
760 , oola.sold_from_org_id
761 -- , oola.shipped_quantity
762 , nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
763 , ooha.sold_to_org_id
764 , oola.order_quantity_uom
765 , oola.inventory_item_id
766 , si.description
767 , si.primary_uom_code
768 , ooha.transactional_curr_code
769 , ooha.conversion_type_code
770 , ooha.conversion_rate
771 , ooha.conversion_rate_date
772 --, nvl(cst.item_cost,0)
776 , ooha.orig_sys_document_ref
773 , NVL(oola.unit_selling_price,0)
774 , abs(nvl(oola.unit_selling_price,0) * nvl(oola.shipped_quantity,0)) doc_line_ext
775 , oola.orig_sys_line_ref
777 , rac.party_name
778 --Bugfix 9676611: customer_number should come from HZ_CUST_ACCOUNTS
779 --, rac.party_number
780 , hzc.account_number
781 FROM
782 OE_ORDER_HEADERS_ALL ooha
783 , OE_ORDER_LINES_ALL oola
784 , HZ_PARTIES rac
785 , HZ_CUST_ACCOUNTS hzc
786 , MTL_SYSTEM_ITEMS si
787 --, CST_ITEM_COSTS_FOR_GL_VIEW cst
788 WHERE ooha.header_id = oola.header_id
789 AND oola.inventory_item_id = si.inventory_item_id
790 AND oola.ship_from_org_id = si.organization_id
791 AND rac.party_id = hzc.party_id
792 AND ooha.sold_to_org_id = hzc.cust_account_id
793 --AND oola.ship_from_org_id = cst.organization_id(+)
794 --AND oola.inventory_item_id = cst.inventory_item_id(+)
795 AND oola.line_id = x_movement_transaction.order_line_id;
796
797 --Fix bug 4207119
798 /*Commenting as part of bugfix 12844667. Removing the join.
799 CURSOR c_item_cost IS
800 SELECT
801 cst.item_cost
802 FROM
803 CST_ITEM_COSTS_FOR_GL_VIEW cst
804 , oe_order_lines_all oola
805 WHERE cst.organization_id = oola.ship_from_org_id
806 AND cst.inventory_item_id = oola.inventory_item_id
807 AND oola.line_id = x_movement_transaction.order_line_id;
808 */
809
810 l_ship_from_org_id NUMBER;
811
812 CURSOR c_item_cost IS
813 SELECT
814 cst.item_cost
815 FROM
816 CST_ITEM_COSTS_FOR_GL_VIEW cst
817 WHERE cst.organization_id = l_ship_from_org_id
818 AND cst.inventory_item_id = x_movement_transaction.inventory_item_id;
819
820 BEGIN
821 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
822 THEN
823 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
824 , G_MODULE_NAME || l_procedure_name || '.begin'
825 ,'enter procedure'
826 );
827 END IF;
828
829 l_stat_typ_transaction := p_stat_typ_transaction;
830 x_return_status := 'Y';
831
832 OPEN l_drpshp_so_om;
833 FETCH l_drpshp_so_om INTO
834 x_movement_transaction.ship_to_site_use_id
835 , x_movement_transaction.delivery_terms
836 , x_movement_transaction.transport_mode
837 , x_movement_transaction.order_line_id
838 , x_movement_transaction.order_header_id
839 , x_movement_transaction.order_number
840 , x_movement_transaction.line_number
841 -- , x_movement_transaction.organization_id
842 -- bug 12844667
843 , l_ship_from_org_id
844 , l_shipment_transaction.org_id
845 -- , x_movement_transaction.shipped_quantity
846 , x_movement_transaction.bill_to_site_use_id
847 , x_movement_transaction.bill_to_customer_id
848 , x_movement_transaction.transaction_uom_code
849 , x_movement_transaction.inventory_item_id
850 , x_movement_transaction.item_description
851 , x_movement_transaction.primary_uom_code
852 , x_movement_transaction.currency_code
853 , x_movement_transaction.currency_conversion_type
854 , x_movement_transaction.currency_conversion_rate
855 , x_movement_transaction.currency_conversion_date
856 --, x_movement_transaction.item_cost
857 , x_movement_transaction.document_unit_price
858 , x_movement_transaction.document_line_ext_value
859 , l_shipment_transaction.req_line_num
860 , l_shipment_transaction.req_num
861 , x_movement_transaction.customer_name
862 , x_movement_transaction.customer_number;
863
864 IF l_drpshp_so_om%NOTFOUND
865 THEN
866 CLOSE l_drpshp_so_om;
867 x_return_status := 'N';
868
869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
870 THEN
871 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
872 , G_MODULE_NAME || l_procedure_name || '.end return N'
873 ,'exit procedure'
874 );
875 END IF;
876 RETURN;
877 ELSE
878 CLOSE l_drpshp_so_om;
879 END IF;
880
881 --
882 -- bug 12844667
883 -- For process Items use the OPM API to derive costs
884 --
885 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(l_ship_from_org_id) = TRUE) THEN
886 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
887 ( p_org_id => l_ship_from_org_id
888 , p_item_id => x_movement_transaction.inventory_item_id
889 , p_transaction_date => x_movement_transaction.transaction_date
890 );
891
892 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
893 THEN
894 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
895 , G_MODULE_NAME || l_procedure_name
896 ,'item_cost from OPM:' || x_movement_transaction.item_cost
897 );
898 END IF;
899
900 ELSE
901 --Get item cost fix bug 4207119
902 OPEN c_item_cost;
903 FETCH c_item_cost INTO
904 x_movement_transaction.item_cost;
905 CLOSE c_item_cost;
906
907 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
908 THEN
909 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
910 , G_MODULE_NAME || l_procedure_name
911 ,'item_cost from discrete:' || x_movement_transaction.item_cost
912 );
913 END IF;
917 x_movement_transaction.document_source_type := 'SO';
914 END IF;
915
916 x_movement_transaction.movement_type := 'D';
918
919 x_movement_transaction.transaction_nature := '17';
920
921 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
922 THEN
923 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
924 , G_MODULE_NAME || l_procedure_name || '.end'
925 ,'exit procedure'
926 );
927 END IF;
928 EXCEPTION
929 WHEN OTHERS THEN
930 x_return_status := 'N';
931
932 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
933 THEN
934 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
935 , G_MODULE_NAME || l_procedure_name||'. Others exception'
936 , 'Exception'
937 );
938 END IF;
939
940 END Get_DropShipment_Details;
941
942
943
944 --========================================================================
945 -- PROCEDURE : Update_PO_Transactions PRIVATE
946 -- PARAMETERS: x_return_status return status
947 -- p_movement_transaction movement transaction record
948 -- COMMENT : Update the status of the transaction record to PROCESSED
949 --========================================================================
950
951 PROCEDURE Update_PO_Transactions
952 ( p_movement_transaction IN
953 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
954 , p_mvt_stat_status IN RCV_TRANSACTIONS.mvt_stat_status%TYPE /*Bug 7165989 */
955 , x_return_status OUT NOCOPY VARCHAR2
956 )
957 IS
958 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
959 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_PO_Transactions';
960 BEGIN
961 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
962 THEN
963 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
964 , G_MODULE_NAME || l_procedure_name || '.begin'
965 ,'enter procedure'
966 );
967 END IF;
968
969 x_return_status := 'Y';
970
971 -- Update the transaction table
972 /* 7165989 - Update mvt_stat_status in RCV_TRANSACTIONS based of the records */
973 /* created for the RMA triangulation. Any non-RMA triangulation should be stamped*/
974 /* with PROCESSED status*/
975 IF (p_mvt_stat_status is NULL OR (p_mvt_stat_status <> 'FORDISP'
976 AND p_mvt_stat_status <> 'FORARVL') )
977 THEN
978
979 UPDATE RCV_TRANSACTIONS
980 SET mvt_stat_status = 'PROCESSED'
981 , movement_id = p_movement_transaction.movement_id
982 WHERE transaction_id = p_movement_transaction.rcv_transaction_id;
983 ELSE
984 UPDATE RCV_TRANSACTIONS
985 SET mvt_stat_status = p_mvt_stat_status
986 , movement_id = p_movement_transaction.movement_id
987 WHERE transaction_id = p_movement_transaction.rcv_transaction_id;
988 END IF;
989
990 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
991 THEN
992 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
993 , G_MODULE_NAME || l_procedure_name || '.end'
994 ,'exit procedure'
995 );
996 END IF;
997 EXCEPTION
998 WHEN OTHERS THEN
999 x_return_status := 'N';
1000 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1001 THEN
1002 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1003 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1004 , 'Exception'
1005 );
1006 END IF;
1007
1008 END Update_PO_Transactions;
1009
1010 --========================================================================
1011 -- PROCEDURE : Get_RTV_Transactions PRIVATE
1012 -- PARAMETERS: rtv_crsr REF cursor
1013 -- x_return_status return status
1014 -- COMMENT :
1015 -- This opens the cursor for RTV and returns the cursor.
1016 --========================================================================
1017
1018 PROCEDURE Get_RTV_Transactions
1019 ( rtv_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.rtvCurTyp
1020 , p_parent_id IN NUMBER
1021 , x_return_status OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_RTV_Transactions';
1025 BEGIN
1026 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1027 THEN
1028 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1029 , G_MODULE_NAME || l_procedure_name || '.begin'
1030 ,'enter procedure'
1031 );
1032 END IF;
1033
1034 x_return_status := 'Y';
1035
1036 IF rtv_crsr%ISOPEN THEN
1037 CLOSE rtv_crsr;
1038 END IF;
1039
1040 OPEN rtv_crsr FOR
1041 SELECT NVL(vendor_site_id,null)
1042 , NVL(parent_transaction_id,null)
1043 , transaction_type
1044 FROM RCV_TRANSACTIONS
1045 WHERE transaction_id = p_parent_id;
1046
1047 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1048 THEN
1049 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1050 , G_MODULE_NAME || l_procedure_name || '.end'
1051 ,'exit procedure'
1052 );
1053 END IF;
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 x_return_status := 'N';
1057 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1058 THEN
1059 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1060 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1061 , 'Exception'
1062 );
1063 END IF;
1064
1068 --========================================================================
1065 END Get_RTV_Transactions;
1066
1067
1069 -- PROCEDURE : Get_Blanket_Info PUBLIC
1070 -- PARAMETERS: p_movement_transaction IN Movement Statistics Record
1071 -- x_movement_transaction OUT Movement Statistics Record
1072 -- COMMENT : Procedure to populate the Blanket PO Info
1073 --=========================================================================
1074
1075 PROCEDURE Get_Blanket_Info
1076 ( x_movement_transaction IN OUT NOCOPY
1077 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1078 )
1079 IS
1080 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1081 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Blanket_Info';
1082
1083 CURSOR l_bpo
1084 IS
1085 SELECT
1086 po.po_release_id
1087 , po.po_line_location_id
1088 , poh.type_lookup_code
1089 FROM
1090 RCV_TRANSACTIONS po
1091 , PO_HEADERS_ALL poh
1092 WHERE po.po_header_id = poh.po_header_id
1093 AND po.transaction_id = x_movement_transaction.rcv_transaction_id;
1094
1095 BEGIN
1096 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1097 THEN
1098 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1099 , G_MODULE_NAME || l_procedure_name || '.begin'
1100 ,'enter procedure'
1101 );
1102 END IF;
1103
1104 l_movement_transaction := x_movement_transaction;
1105
1106 OPEN l_bpo;
1107 FETCH l_bpo
1108 INTO
1109 x_movement_transaction.release_id
1110 , x_movement_transaction.po_line_location_id
1111 , x_movement_transaction.type_lookup_code;
1112 CLOSE l_bpo;
1113
1114 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1115 THEN
1116 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1117 , G_MODULE_NAME || l_procedure_name || '.end'
1118 ,'exit procedure'
1119 );
1120 END IF;
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 x_movement_transaction := l_movement_transaction;
1124 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1125 THEN
1126 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1127 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1128 , 'Exception'
1129 );
1130 END IF;
1131
1132 END Get_Blanket_Info;
1133
1134
1135 --========================================================================
1136 -- PROCEDURE : Get_RMA_Transactions PRIVATE
1137 -- PARAMETERS: rma_crsr REF cursor
1138 -- x_return_status return status
1139 -- p_start_date Transaction start date
1140 -- p_end_date Transaction end date
1141 -- COMMENT :
1142 -- This opens the cursor for RMA and returns the cursor.
1143 --========================================================================
1144
1145 PROCEDURE Get_RMA_Transactions
1146 ( rma_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.poCurTyp
1147 , p_movement_transaction IN
1148 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1149 , p_start_date IN DATE
1150 , p_end_date IN DATE
1151 , x_return_status OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_RMA_Transactions';
1155 BEGIN
1156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1157 THEN
1158 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1159 , G_MODULE_NAME || l_procedure_name || '.begin'
1160 ,'enter procedure'
1161 );
1162 END IF;
1163
1164 x_return_status := 'Y';
1165
1166 IF rma_crsr%ISOPEN THEN
1167 CLOSE rma_crsr;
1168 END IF;
1169
1170 --Fix performance bug 4912552, use hr_organization_information to replace
1171 --org_organization_definitions according to proposal from INV
1172 --karthik.gnanamurthy, because inventory organization is already existing
1173 --in rcv_transactions, so it's not required to validate the organization
1174 --again in mtl_parameters or hr_all_organization_units as OOD does
1175 --Fix bug 5437773, replace oola.sold_from_org_id with oola.org_id
1176 --org_id is the correct column to get operating unit
1177
1178 IF NVL(p_movement_transaction.creation_method,'A') = 'A' THEN
1179 --Fix bug3057775. Pick up RMA at the LE where this RMA is created
1180 --when the receipt LE is different from creating LE. This is for
1181 --invoice based triangulation mode.
1182 --R12 Legal entity new data model uptake, replace hr_operating_units base tables
1183 --with XLE package, because this view is not existed anymore
1184 OPEN rma_crsr FOR
1185 SELECT
1186 rcv.transaction_id
1187 , rcv.parent_transaction_id
1188 , rcv.transaction_type
1189 , rcv.source_document_code
1190 , rcv.customer_site_id
1191 , rcv.oe_order_header_id
1192 , rcv.oe_order_line_id
1193 , rcv.transaction_date
1194 , rcv.organization_id
1195 , rcv.subinventory
1196 , rcv.mvt_stat_status -- 7165989
1197 FROM
1198 RCV_TRANSACTIONS rcv
1199 , oe_order_lines_all oola
1200 , hr_organization_information hoi /*Bug 8467743*/
1201 WHERE rcv.oe_order_line_id = oola.line_id
1202 AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
1203 AND rcv.transaction_type IN ('DELIVER')
1204 AND rcv.source_document_code = 'RMA'
1205 AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
1206 AND hoi.org_information_context = 'Operating Unit Information' /*Bug 8467743*/
1210 /*AND p_movement_transaction.entity_org_id =
1207 AND hoi.organization_id = nvl(oola.org_id,oola.sold_from_org_id) /*Bug 8467743*/
1208 --AND p_movement_transaction.entity_org_id =TO_NUMBER(hoi.org_information2) /*Bug 8467743*/ /*Bug 12900798*/
1209 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /*Bug 12900798*/
1211 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
1212 ('SOLD_TO', oola.sold_to_org_id
1213 , null, null, oola.org_id)*/
1214 UNION
1215 SELECT --regular case: receipt LE is same as creating LE
1216 rcv.transaction_id
1217 , rcv.parent_transaction_id
1218 , rcv.transaction_type
1219 , rcv.source_document_code
1220 , rcv.customer_site_id
1221 , rcv.oe_order_header_id
1222 , rcv.oe_order_line_id
1223 , rcv.transaction_date
1224 , rcv.organization_id
1225 , rcv.subinventory
1226 , rcv.mvt_stat_status -- 7165989
1227 FROM
1228 RCV_TRANSACTIONS rcv
1229 , hr_organization_information hoi
1230 WHERE rcv.organization_id = hoi.organization_id
1231 AND hoi.org_information_context = 'Accounting Information'
1232 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
1233 AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
1234 AND rcv.transaction_type IN ('DELIVER')
1235 AND rcv.source_document_code = 'RMA'
1236 AND rcv.transaction_date BETWEEN p_start_date AND p_end_date;
1237 ELSE
1238 OPEN rma_crsr FOR
1239 SELECT
1240 rcv.transaction_id
1241 , rcv.parent_transaction_id
1242 , rcv.transaction_type
1243 , rcv.source_document_code
1244 , rcv.customer_site_id
1245 , rcv.oe_order_header_id
1246 , rcv.oe_order_line_id
1247 , rcv.transaction_date
1248 , rcv.organization_id
1249 , rcv.subinventory
1250 , rcv.mvt_stat_status -- 7165989
1251 FROM
1252 RCV_TRANSACTIONS rcv
1253 , RCV_SHIPMENT_HEADERS rsh
1254 , oe_order_lines_all oola
1255 , hr_organization_information hoi /*Bug 8467743*/
1256 WHERE rcv.shipment_header_id = rsh.shipment_header_id
1257 AND rcv.oe_order_line_id = oola.line_id
1258 AND rsh.receipt_num = p_movement_transaction.receipt_num
1259 AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
1260 AND rcv.transaction_type IN ('DELIVER')
1261 AND rcv.source_document_code = 'RMA'
1262 AND hoi.org_information_context = 'Operating Unit Information' /*Bug 8467743*/
1263 AND hoi.organization_id = nvl(oola.org_id,oola.sold_from_org_id) /*Bug 8467743*/
1264 --AND p_movement_transaction.entity_org_id =TO_NUMBER(hoi.org_information2) /*Bug 8467743*/ /*Bug 12900798*/
1265 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /*Bug 12900798*/
1266 /*AND p_movement_transaction.entity_org_id =
1267 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
1268 ('SOLD_TO', oola.sold_to_org_id
1269 , null, null, oola.org_id)*/
1270 UNION
1271 SELECT
1272 rcv.transaction_id
1273 , rcv.parent_transaction_id
1274 , rcv.transaction_type
1275 , rcv.source_document_code
1276 , rcv.customer_site_id
1277 , rcv.oe_order_header_id
1278 , rcv.oe_order_line_id
1279 , rcv.transaction_date
1280 , rcv.organization_id
1281 , rcv.subinventory
1282 , rcv.mvt_stat_status -- 7165989
1283 FROM
1284 RCV_TRANSACTIONS rcv
1285 , RCV_SHIPMENT_HEADERS rsh
1286 , hr_organization_information hoi
1287 WHERE rcv.shipment_header_id = rsh.shipment_header_id
1288 AND rcv.organization_id = hoi.organization_id
1289 AND hoi.org_information_context = 'Accounting Information'
1290 AND rsh.ship_to_org_id = hoi.organization_id
1291 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
1292 AND rsh.receipt_num = p_movement_transaction.receipt_num
1293 AND rcv.organization_id = p_movement_transaction.organization_id
1294 AND rcv.mvt_stat_status IN ('NEW', 'FORDISP', 'FORARVL') -- 7165989 Changes for RMA triangulation
1295 AND rcv.transaction_type IN ('DELIVER')
1296 AND rcv.source_document_code = 'RMA';
1297 END IF;
1298
1299 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1300 THEN
1301 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1302 , G_MODULE_NAME || l_procedure_name || '.end'
1303 ,'exit procedure'
1304 );
1305 END IF;
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 x_return_status := 'N';
1309 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1310 THEN
1311 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1312 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1313 , 'Exception'
1314 );
1315 END IF;
1316
1317 END Get_RMA_Transactions;
1318
1319 --========================================================================
1320 -- PROCEDURE : Get_RMA_Details PRIVATE
1321 -- PARAMETERS: x_return_status return status
1322 -- p_movement_transaction movement transaction record
1323 -- COMMENT : Get all the additional data required for RMA
1324 --========================================================================
1325
1326 PROCEDURE Get_RMA_Details
1327 ( p_stat_typ_transaction IN
1328 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1329 , x_movement_transaction IN OUT NOCOPY
1330 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1331 , x_return_status OUT NOCOPY VARCHAR2
1332 )
1333 IS
1334 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
1338 l_unit_price NUMBER;
1335 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1336 l_order_uom OE_ORDER_LINES_ALL.Pricing_Quantity_Uom%TYPE;
1337 l_uom_conv_rate NUMBER;
1339 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_RMA_Details';
1340
1341 /*bug 8435314 Add logic for config Item in RMA*/
1342 l_qty_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
1343 l_currency_code OE_ORDER_HEADERS_ALL.Transactional_Curr_Code%TYPE;
1344 l_error_code NUMBER;
1345 l_return_status VARCHAR2(1);
1346 l_item_type_code OE_ORDER_LINES_ALL.Item_Type_Code%TYPE;
1347 l_So_line_Id NUMBER;
1348 CURSOR l_rma_config IS
1349 SELECT DISTINCT 'CONFIG' FROM mtl_system_items
1350 WHERE inventory_item_id=x_movement_transaction.inventory_item_id
1351 AND auto_created_config_flag='Y'
1352 AND base_item_id IS NOT null;
1353 /*End bug 8435314 */
1354
1355 CURSOR rma_details IS
1356 SELECT
1357 po.transaction_id
1358 , po.organization_id
1359 , abs(po.quantity)
1360 , po.uom_code
1361 --, po.transaction_date timezone support do not populate again
1362 , abs(po.primary_quantity)
1363 , rsl.item_id
1364 , rsl.item_description
1365 --, si.description
1366 --, nvl(cst.item_cost,0)
1367 , ooha.fob_point_code
1368 , NVL(abs(oola.unit_selling_price),0)
1369 --, po.oe_order_header_id
1370 --, po.oe_order_line_id
1371 --, ooha.ship_to_org_id
1372 , oola.ship_to_org_id /* bug 6839063 - line details are used instead of header details */
1373 , ooha.sold_to_org_id
1374 , nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
1375 , ooha.transactional_curr_code
1376 , ooha.conversion_type_code
1377 , ooha.conversion_rate
1378 , ooha.conversion_rate_date
1379 , rsl.shipment_header_id
1380 , rsl.shipment_line_id
1381 , ooha.org_id
1382 , oola.order_quantity_uom
1383 , ooha.sold_from_org_id -- 7165989
1384 , oola.return_attribute2 -- 8435314
1385 FROM
1386 RCV_TRANSACTIONS po
1387 , RCV_SHIPMENT_HEADERS rsh
1388 , RCV_SHIPMENT_LINES rsl
1389 , OE_ORDER_HEADERS_ALL ooha
1390 , OE_ORDER_LINES_ALL oola
1391 --, MTL_SYSTEM_ITEMS si
1392 --, CST_ITEM_COSTS_FOR_GL_VIEW cst
1393 WHERE po.shipment_header_id = rsh.shipment_header_id
1394 AND rsh.shipment_header_id = rsl.shipment_header_Id
1395 AND po.shipment_line_id = rsl.shipment_line_id
1396 AND po.oe_order_header_id = ooha.header_id
1397 AND ooha.header_id = oola.header_id
1398 AND po.oe_order_line_id = oola.line_id
1399 --AND rsh.organization_id = si.organization_id
1400 --AND rsl.item_id = si.inventory_item_id
1401 --AND si.organization_id = cst.organization_id (+)
1402 --AND si.inventory_item_id = cst.inventory_item_id (+)
1403 AND po.transaction_id = x_movement_transaction.rcv_transaction_id;
1404
1405 --Fix bug 4207119
1406 CURSOR c_item_cost IS
1407 SELECT
1408 item_cost
1409 FROM
1410 CST_ITEM_COSTS_FOR_GL_VIEW
1411 WHERE organization_id = x_movement_transaction.organization_id
1412 AND inventory_item_id = x_movement_transaction.inventory_item_id;
1413
1414 /*CURSOR l_uom IS
1415 SELECT
1416 muc.uom_code
1417 FROM
1418 MTL_UOM_CONVERSIONS_VIEW muc
1419 WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
1420 AND muc.organization_id = x_movement_transaction.organization_id
1421 AND muc.unit_of_measure = l_receipt_transaction.primary_unit_of_measure;*/
1422
1423 BEGIN
1424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1425 THEN
1426 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1427 , G_MODULE_NAME || l_procedure_name || '.begin'
1428 ,'enter procedure'
1429 );
1430 END IF;
1431
1432 l_stat_typ_transaction := p_stat_typ_transaction;
1433 x_return_status := 'Y';
1434
1435 OPEN rma_details;
1436 FETCH rma_details INTO
1437 x_movement_transaction.rcv_transaction_id
1438 , x_movement_transaction.organization_id
1439 , x_movement_transaction.transaction_quantity
1440 , x_movement_transaction.transaction_uom_code
1441 --, x_movement_transaction.transaction_date
1442 , x_movement_transaction.primary_quantity
1443 , x_movement_transaction.inventory_item_id
1444 , x_movement_transaction.item_description
1445 --, x_movement_transaction.item_cost
1446 , x_movement_transaction.delivery_terms
1447 , l_unit_price
1448 --, x_movement_transaction.order_header_id
1449 --, x_movement_transaction.order_line_id
1450 , x_movement_transaction.ship_to_site_use_id
1451 , x_movement_transaction.ship_to_customer_id
1452 , x_movement_transaction.bill_to_site_use_id
1453 , x_movement_transaction.currency_code
1454 , x_movement_transaction.currency_conversion_type
1455 , x_movement_transaction.currency_conversion_rate
1456 , x_movement_transaction.currency_conversion_date
1457 , x_movement_transaction.shipment_header_id
1458 , x_movement_transaction.shipment_line_id
1459 , x_movement_transaction.org_id
1460 , l_order_uom
1461 , x_movement_transaction.sold_from_org_id -- 7165989
1462 , l_So_line_Id;--8435314
1463
1464 IF rma_details%NOTFOUND THEN
1465 CLOSE rma_details;
1466 x_return_status := 'N';
1467
1468 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1469 THEN
1470 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1471 , G_MODULE_NAME || l_procedure_name || '.end return N'
1472 ,'exit procedure'
1476 END IF;
1473 );
1474 END IF;
1475 RETURN;
1477
1478 CLOSE rma_details;
1479
1480 --
1481 -- bug 12844667
1482 -- For process Items use the OPM API to derive costs
1483 --
1484 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(x_movement_transaction.organization_id) = TRUE) THEN
1485 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
1486 ( p_org_id => x_movement_transaction.organization_id
1487 , p_item_id => x_movement_transaction.inventory_item_id
1488 , p_transaction_date => x_movement_transaction.transaction_date
1489 );
1490 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1491 THEN
1492 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1493 , G_MODULE_NAME || l_procedure_name
1494 ,'item_cost from OPM:' || x_movement_transaction.item_cost
1495 );
1496 END IF;
1497 ELSE
1498 --Get item cost fix bug 4207119
1499 OPEN c_item_cost;
1500 FETCH c_item_cost INTO
1501 x_movement_transaction.item_cost;
1502 CLOSE c_item_cost;
1503
1504 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1505 THEN
1506 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1507 , G_MODULE_NAME || l_procedure_name
1508 ,'item_cost from discrete:' || x_movement_transaction.item_cost
1509 );
1510 END IF;
1511 END IF;
1512
1513 /*OPEN l_uom;
1514 FETCH l_uom INTO
1515 x_movement_transaction.transaction_uom_code;
1516
1517 IF l_uom%NOTFOUND THEN
1518 x_movement_transaction.transaction_uom_code :=
1519 substrb(l_receipt_transaction.primary_unit_of_measure,1,3);
1520 END IF;
1521
1522 CLOSE l_uom;*/
1523
1524 --SO order uom maynot be same as receipt qty uom,thus when calculate document
1525 --line ext value, we need to consider uom conversion
1526 /*bug 8435314 Check for config Item*/
1527 FND_FILE.put_line(FND_FILE.log, 'x_movement_transaction.order_line_id 1 is : '||x_movement_transaction.order_line_id);
1528 Open l_rma_config;
1529 Fetch l_rma_config into l_item_type_code;
1530 Close l_rma_config;
1531 --Get document unit price for CTO item
1532 FND_FILE.put_line(FND_FILE.log, 'The Item is : '||X_movement_transaction.inventory_item_id || ' '||l_item_type_code);
1533 FND_FILE.put_line(FND_FILE.log, 'l_unit_price 1 is : '||l_unit_price);
1534 IF l_item_type_code = 'CONFIG'
1535 THEN
1536 --Call BOM procedure to get unit selling price
1537 FND_FILE.put_line(FND_FILE.log, 'l_So_line_Id 1 is : '||l_So_line_Id);
1538 CTO_PUBLIC_UTILITY_PK.Get_Selling_Price
1539 ( p_config_line_id => l_So_line_Id
1540 , x_unit_selling_price => l_unit_price
1541 , x_qty_selling_price => l_qty_selling_price
1542 , x_currency_code => l_currency_code
1543 , x_return_status => l_return_status
1544 , x_error_code => l_error_code
1545 );
1546 FND_FILE.put_line(FND_FILE.log, 'l_unit_price 2 is : '||l_unit_price);
1547 END IF;
1548 /*End bug 8435314 */
1549 IF x_movement_transaction.transaction_uom_code <> l_order_uom
1550 THEN
1551 INV_CONVERT.Inv_Um_Conversion
1552 ( from_unit => x_movement_transaction.transaction_uom_code
1553 , to_unit => l_order_uom
1554 , item_id => x_movement_transaction.inventory_item_id
1555 , uom_rate => l_uom_conv_rate
1556 );
1557 ELSE
1558 l_uom_conv_rate := 1;
1559 END IF;
1560
1561 --Set document unit price and document line value
1562 --This unit price would be for each transaction uom
1563 x_movement_transaction.document_unit_price :=
1564 NVL(l_unit_price,0) * l_uom_conv_rate;
1565 x_movement_transaction.document_line_ext_value :=
1566 abs(x_movement_transaction.document_unit_price *
1567 x_movement_transaction.transaction_quantity);
1568
1569 IF x_movement_transaction.currency_code IS NULL THEN
1570 x_movement_transaction.currency_code :=
1571 l_stat_typ_transaction.gl_currency_code;
1572 END IF;
1573
1574 x_movement_transaction.movement_type := 'A';
1575 x_movement_transaction.document_source_type := 'RMA';
1576 -- x_movement_transaction.currency_conversion_rate := 1;
1577 x_movement_transaction.transaction_nature := '20';
1578
1579 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1580 THEN
1581 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1582 , G_MODULE_NAME || l_procedure_name || '.end'
1583 ,'exit procedure'
1584 );
1585 END IF;
1586 EXCEPTION
1587 WHEN NO_DATA_FOUND THEN
1588 x_return_status := 'N';
1589 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1590 THEN
1591 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1592 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1593 , 'Exception'
1594 );
1595 END IF;
1596 WHEN OTHERS THEN
1597 x_return_status := 'N';
1598 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1599 THEN
1600 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1601 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1602 , 'Exception'
1603 );
1604 END IF;
1605
1606 END Get_RMA_Details;
1607
1608 --========================================================================
1609 -- PROCEDURE : Get_Parent_Mvt PRIVATE
1613 -- x_movement_status movement status
1610 -- PARAMETERS: p_rcv_transaction_id transaction id
1611 -- p_movement_transaction movement transaction record
1612 -- x_movement_id movement id
1614 -- x_source_type document source type
1615 -- COMMENT : Get movement id, movement status and source type of given
1616 -- transaction id
1617 --========================================================================
1618 PROCEDURE Get_Parent_Mvt
1619 ( p_movement_transaction IN
1620 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1621 , p_rcv_transaction_id IN NUMBER
1622 , x_movement_id OUT NOCOPY NUMBER
1623 , x_movement_status OUT NOCOPY VARCHAR2
1624 , x_source_type OUT NOCOPY VARCHAR2
1625 )
1626 IS
1627 BEGIN
1628 SELECT
1629 movement_id
1630 , movement_status
1631 , document_source_type
1632 INTO
1633 x_movement_id
1634 , x_movement_status
1635 , x_source_type
1636 FROM
1637 mtl_movement_statistics
1638 WHERE usage_type = p_movement_transaction.usage_type
1639 AND stat_type = p_movement_transaction.stat_type
1640 AND zone_code = p_movement_transaction.zone_code
1641 AND entity_org_id = p_movement_transaction.entity_org_id
1642 AND rcv_transaction_id = p_rcv_transaction_id;
1643
1644 EXCEPTION
1645 WHEN OTHERS THEN
1646 x_movement_id := null;
1647 x_movement_status := null;
1648 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1649 THEN
1650 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1651 , G_MODULE_NAME ||'Get_Parent_Mvt'||'. Others exception'
1652 , 'Exception'
1653 );
1654 END IF;
1655
1656 END Get_Parent_Mvt;
1657
1658 --========================================================================
1659 -- PROCEDURE : Get_IO_Arrival_Txn PRIVATE
1660 -- PARAMETERS: io_arrival_crsr REF cursor
1661 -- x_return_status return status
1662 -- p_start_date Transaction start date
1663 -- p_end_date Transaction end date
1664 -- COMMENT :
1665 -- This opens the cursor for IO arrival and returns the cursor.
1666 --========================================================================
1667
1668 PROCEDURE Get_IO_Arrival_Txn
1669 ( io_arrival_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.poCurTyp
1670 , p_movement_transaction IN
1671 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1672 , p_start_date IN DATE
1673 , p_end_date IN DATE
1674 , x_return_status OUT NOCOPY VARCHAR2
1675 )
1676 IS
1677 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_IO_Arrival_Txn';
1678 BEGIN
1679 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1680 THEN
1681 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1682 , G_MODULE_NAME || l_procedure_name || '.begin'
1683 ,'enter procedure'
1684 );
1685 END IF;
1686
1687 x_return_status := 'Y';
1688
1689 IF io_arrival_crsr%ISOPEN THEN
1690 CLOSE io_arrival_crsr;
1691 END IF;
1692
1693 --Fix performance bug 4912552, use hr_organization_information to replace
1694 --org_organization_definitions according to proposal from INV
1695 --karthik.gnanamurthy, because inventory organization is already existing
1696 --in rcv_transactions, so it's not required to validate the organization
1697 --again in mtl_parameters or hr_all_organization_units as OOD does
1698
1699 IF NVL(p_movement_transaction.creation_method,'A') = 'A'
1700 THEN
1701 --Fix bug 3364811, move order lines and delivery table out of
1702 --io_arrival_crsr so that no duplicate rcv transactions picked
1703
1704 --Bugfix 9839689: Added distinct.
1705
1706 OPEN io_arrival_crsr FOR
1707 SELECT distinct
1708 rcv.transaction_id
1709 , rcv.transaction_date
1710 , rcv.organization_id
1711 , rcv.subinventory
1712 , rcv.requisition_line_id
1713 , prha.segment1
1714 , rcv.oe_order_line_id /* Added for bug 9024785*/
1715 FROM
1716 rcv_transactions rcv
1717 , po_requisition_lines_all prla
1718 , po_requisition_headers_all prha
1719 , oe_order_headers_all orha
1720 , hr_organization_information hoi
1721 WHERE rcv.requisition_line_id = prla.requisition_line_id
1722 AND prla.requisition_header_id = prha.requisition_header_id
1723 AND prha.requisition_header_id = orha.source_document_id
1724 AND orha.order_source_id = 10 --oe_order_sources tbl
1725 AND orha.orig_sys_document_ref = prha.segment1
1726 AND rcv.organization_id = hoi.organization_id
1727 AND hoi.org_information_context = 'Accounting Information'
1728 AND rcv.mvt_stat_status = 'NEW'
1729 AND rcv.transaction_type = 'RECEIVE'
1730 AND NVL(rcv.source_document_code,'REQ') = 'REQ'
1731 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
1732 AND rcv.transaction_date BETWEEN p_start_date AND p_end_date
1733 ORDER BY rcv.transaction_id;
1734 END IF;
1735
1736 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1737 THEN
1738 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1739 , G_MODULE_NAME || l_procedure_name || '.end'
1740 ,'exit procedure'
1741 );
1742 END IF;
1743 EXCEPTION
1744 WHEN NO_DATA_FOUND THEN
1745 x_return_status := 'N';
1746 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1747 THEN
1748 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1749 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1753 WHEN OTHERS THEN
1750 , 'Exception'
1751 );
1752 END IF;
1754 x_return_status := 'N';
1755 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1756 THEN
1757 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1758 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1759 , 'Exception'
1760 );
1761 END IF;
1762
1763 END Get_IO_Arrival_Txn;
1764
1765 --========================================================================
1766 -- PROCEDURE : Get_IO_Arrival_Details PRIVATE
1767 -- PARAMETERS: x_return_status return status
1768 -- p_movement_transaction movement transaction record
1769 -- COMMENT : Get all the additional data required for IO Arrival
1770 --========================================================================
1771
1772 PROCEDURE Get_IO_Arrival_Details
1773 ( x_movement_transaction IN OUT NOCOPY
1774 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1775 , x_return_status OUT NOCOPY VARCHAR2
1776 )
1777 IS
1778 l_unit_of_measure rcv_transactions.unit_of_measure%TYPE;
1779 l_transport_mode mtl_movement_statistics.transport_mode%TYPE;
1780 l_document_unit_price mtl_movement_statistics.document_unit_price%TYPE;
1781 l_item_type_code OE_ORDER_LINES_ALL.Item_Type_Code%TYPE;
1782 l_mvt_stat_status rcv_transactions.mvt_stat_status%TYPE;
1783
1784 l_unit_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
1785 l_qty_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
1786 l_currency_code OE_ORDER_HEADERS_ALL.Transactional_Curr_Code%TYPE;
1787 l_error_code NUMBER;
1788 l_return_status VARCHAR2(1);
1789 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_IO_Arrival_Details';
1790
1791 CURSOR io_po_details IS
1792 SELECT
1793 rcv.transaction_id
1794 , rcv.organization_id
1795 , rcv.movement_id
1796 , rcv.mvt_stat_status
1797 , rcv.currency_code
1798 , rcv.currency_conversion_type
1799 , rcv.currency_conversion_rate
1800 , rcv.currency_conversion_date
1801 , rcv.shipment_header_id
1802 , rcv.shipment_line_id
1803 , rsl.item_id
1804 , rsl.item_description
1805 , rcv.unit_of_measure
1806 , rcv.quantity
1807 , rcv.primary_quantity
1808 --, nvl(cst.item_cost,0)
1809 , NVL(rcv.po_unit_price,0)
1810 , rcv.country_of_origin_code
1811 , NVL(rsh.freight_carrier_code,'3')
1812 FROM
1813 RCV_TRANSACTIONS rcv
1814 , RCV_SHIPMENT_HEADERS rsh
1815 , RCV_SHIPMENT_LINES rsl
1816 --, CST_ITEM_COSTS_FOR_GL_VIEW cst
1817 WHERE rcv.shipment_header_id = rsh.shipment_header_id
1818 AND rsh.shipment_header_id = rsl.shipment_header_id
1819 AND rcv.shipment_line_id = rsl.shipment_line_id
1820 --AND rsl.to_organization_id = cst.organization_id (+)
1821 --AND rsl.item_id = cst.inventory_item_id (+)
1822 AND rcv.transaction_id = x_movement_transaction.rcv_transaction_id;
1823
1824 CURSOR io_so_details IS
1828 , NVL(wdd.ship_method_code,'3') transport_mode
1825 SELECT
1826 oola.ship_to_org_id ship_to_site_use_id
1827 , wdd.fob_code delivery_terms
1829 --, to_number(NULL) picking_line_id
1830 , oola.line_id
1831 , ooha.header_id
1832 , ooha.order_number
1833 , oola.line_number
1834 , ooha.sold_to_org_id ship_to_customer_id
1835 , nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id) bill_to_site_use_id
1836 , ooha.sold_to_org_id bill_to_customer_id
1837 , NVL(oola.unit_selling_price,0) doc_unit_price
1838 , oola.source_document_id req_hd_id
1839 , oola.source_document_line_id req_ln_id
1840 --, to_number(NULL) pick_slip_ref
1841 , rac.party_name cust_name
1842 --Bugfix 9676611: customer_number should come from HZ_CUST_ACCOUNTS
1843 --, rac.party_number cust_number
1844 , hzc.account_number cust_number
1845 , substrb(rac.province,1,30) area
1846 , wnd.name shipment_reference
1847 , oola.item_type_code
1848 FROM
1849 WSH_NEW_DELIVERIES_OB_GRP_V wnd
1850 , wsh_delivery_assignments_v wda
1851 , WSH_DELIVERY_DETAILS_OB_GRP_V wdd
1852 , OE_ORDER_HEADERS_ALL ooha
1853 , OE_ORDER_LINES_ALL oola
1854 , HZ_PARTIES rac
1855 , HZ_CUST_ACCOUNTS hzc
1856 WHERE wnd.delivery_id = wda.delivery_id
1857 AND wda.delivery_detail_id = wdd.delivery_detail_id
1858 AND wdd.source_line_id = oola.line_id
1859 AND ooha.header_id = oola.header_id
1860 AND oola.line_id = wdd.source_line_id
1861 AND oola.header_id = wdd.source_header_id
1865
1862 AND rac.party_id = hzc.party_id
1863 AND ooha.sold_to_org_id = hzc.cust_account_id
1864 AND wdd.delivery_detail_id = x_movement_transaction.picking_line_detail_id;
1866 CURSOR l_uom IS
1867 SELECT
1868 muc.uom_code
1869 FROM
1870 MTL_UOM_CONVERSIONS_VIEW muc
1871 WHERE muc.inventory_item_id= x_movement_transaction.inventory_item_id
1872 AND muc.organization_id = x_movement_transaction.organization_id
1873 AND muc.unit_of_measure = l_unit_of_measure;
1874
1875 --Fix bug 4207119
1876 CURSOR c_item_cost IS
1877 SELECT
1878 item_cost
1879 FROM
1880 CST_ITEM_COSTS_FOR_GL_VIEW
1881 WHERE organization_id = x_movement_transaction.organization_id
1882 AND inventory_item_id = x_movement_transaction.inventory_item_id;
1883
1884 BEGIN
1885 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1886 THEN
1887 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1888 , G_MODULE_NAME || l_procedure_name || '.begin'
1889 ,'enter procedure'
1890 );
1891 END IF;
1892
1893 x_return_status := 'Y';
1894
1895 --Get IO receiving details
1896 OPEN io_po_details;
1897 FETCH io_po_details INTO
1898 x_movement_transaction.rcv_transaction_id
1899 , x_movement_transaction.organization_id
1900 , x_movement_transaction.movement_id
1901 , l_mvt_stat_status
1902 , x_movement_transaction.currency_code
1903 , x_movement_transaction.currency_conversion_type
1904 , x_movement_transaction.currency_conversion_rate
1905 , x_movement_transaction.currency_conversion_date
1906 , x_movement_transaction.shipment_header_id
1907 , x_movement_transaction.shipment_line_id
1908 , x_movement_transaction.inventory_item_id
1909 , x_movement_transaction.item_description
1910 , l_unit_of_measure
1911 , x_movement_transaction.transaction_quantity
1912 , x_movement_transaction.primary_quantity
1913 --, x_movement_transaction.item_cost
1914 , x_movement_transaction.document_unit_price
1915 , x_movement_transaction.origin_territory_code
1916 , x_movement_transaction.transport_mode;
1917
1918 IF io_po_details%NOTFOUND
1919 THEN
1920 CLOSE io_po_details;
1921 x_return_status := 'N';
1922
1923 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1924 THEN
1925 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1926 , G_MODULE_NAME || l_procedure_name || '.end return N at io_po_details'
1927 ,'exit procedure'
1928 );
1929 END IF;
1930 RETURN;
1931 END IF;
1932 CLOSE io_po_details;
1933
1934 --
1935 -- bug 12844667
1936 -- For process Items use the OPM API to derive costs
1937 --
1938 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(x_movement_transaction.organization_id) = TRUE) THEN
1939 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
1940 ( p_org_id => x_movement_transaction.organization_id
1941 , p_item_id => x_movement_transaction.inventory_item_id
1942 , p_transaction_date => x_movement_transaction.transaction_date
1943 );
1944
1945 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1946 THEN
1947 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1948 , G_MODULE_NAME || l_procedure_name
1949 ,'item_cost from OPM:' || x_movement_transaction.item_cost
1950 );
1951 END IF;
1952 ELSE
1953 --Get item cost fix bug 4207119
1954 OPEN c_item_cost;
1955 FETCH c_item_cost INTO
1956 x_movement_transaction.item_cost;
1957 CLOSE c_item_cost;
1958
1959 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1960 THEN
1961 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1962 , G_MODULE_NAME || l_procedure_name
1963 ,'item_cost from discrete:' || x_movement_transaction.item_cost
1964 );
1965 END IF;
1966 END IF;
1967
1968 OPEN l_uom;
1969 FETCH l_uom INTO
1970 x_movement_transaction.transaction_uom_code;
1971
1972 IF l_uom%NOTFOUND THEN
1973 x_movement_transaction.transaction_uom_code := substrb(l_unit_of_measure,1,3);
1974 END IF;
1975 CLOSE l_uom;
1976
1977 IF x_movement_transaction.currency_code IS NULL
1978 THEN
1979 x_movement_transaction.currency_code := x_movement_transaction.gl_currency_code;
1980 END IF;
1981
1982 --Get IO sales order details
1983 OPEN io_so_details;
1984 FETCH io_so_details INTO
1985 x_movement_transaction.ship_to_site_use_id
1986 , x_movement_transaction.delivery_terms
1987 , l_transport_mode
1988 --, x_movement_transaction.picking_line_id
1989 , x_movement_transaction.order_line_id
1990 , x_movement_transaction.order_header_id
1991 , x_movement_transaction.order_number
1992 , x_movement_transaction.line_number
1993 , x_movement_transaction.ship_to_customer_id
1994 , x_movement_transaction.bill_to_site_use_id
1995 , x_movement_transaction.bill_to_customer_id
1996 , l_document_unit_price
1997 , x_movement_transaction.requisition_header_id
1998 , x_movement_transaction.requisition_line_id
1999 --, x_movement_transaction.pick_slip_reference
2000 , x_movement_transaction.customer_name
2001 , x_movement_transaction.customer_number
2005
2002 , x_movement_transaction.area --the area for receiving side
2003 , x_movement_transaction.shipment_reference
2004 , l_item_type_code;
2006 IF io_so_details%NOTFOUND
2007 THEN
2008 CLOSE io_so_details;
2009 x_return_status := 'N';
2010
2011 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2012 THEN
2013 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2014 , G_MODULE_NAME || l_procedure_name || '.end return N at io_so_details'
2015 ,'exit procedure'
2016 );
2017 END IF;
2018 RETURN;
2019 END IF;
2020 CLOSE io_so_details;
2021
2022 IF (x_movement_transaction.movement_id IS NOT NULL)
2023 AND (NVL(l_mvt_stat_status,'NEW')='MODIFIED')
2024 THEN
2025 x_movement_transaction.movement_type := 'AA';
2026 ELSE
2027 x_movement_transaction.movement_type := 'A';
2028 END IF;
2029
2030 x_movement_transaction.transaction_nature := '10';
2031
2032 IF x_movement_transaction.origin_territory_code IS NULL
2033 THEN
2034 x_movement_transaction.origin_territory_code :=
2035 x_movement_transaction.dispatch_territory_code;
2036 END IF;
2037
2038 --Doc unit price and line ext value
2039 x_movement_transaction.document_unit_price := x_movement_transaction.item_cost;
2040 x_movement_transaction.document_line_ext_value := x_movement_transaction.document_unit_price *
2041 x_movement_transaction.transaction_quantity;
2042 --Get IO details
2043 INV_MGD_MVT_SO_MDTR.Get_IO_Details
2044 ( x_movement_transaction => x_movement_transaction
2045 , x_return_status => x_return_status
2046 );
2047
2048 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2049 THEN
2050 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2051 , G_MODULE_NAME || l_procedure_name || '.end'
2052 ,'exit procedure'
2053 );
2054 END IF;
2055 EXCEPTION
2056 WHEN NO_DATA_FOUND THEN
2057 x_return_status := 'N';
2058 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2059 THEN
2060 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2061 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
2062 , 'Exception'
2063 );
2064 END IF;
2065 WHEN OTHERS THEN
2066 x_return_status := 'N';
2067 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2068 THEN
2069 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2070 , G_MODULE_NAME || l_procedure_name||'. Others exception'
2071 , 'Exception'
2072 );
2073 END IF;
2074
2075 END Get_IO_Arrival_Details;
2076
2077 END INV_MGD_MVT_PO_MDTR;