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