[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_SO_MDTR
Source
1 PACKAGE BODY INV_MGD_MVT_SO_MDTR AS
2 -- $Header: INVSMDRB.pls 120.15.12020000.2 2013/02/18 07:10:40 abhissri ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVSMDRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of INV_MGD_MVT_SO_MDTR |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_SO_Transactions |
16 --| Get_TwoLeOneCntry_Txns |
17 --| Get_Triangulation_Txns |
18 --| Get_SO_Details |
19 --| Get_KIT_SO_Details |
20 --| Update_SO_Transactions |
21 --| Update_KIT_SO_Transactions |
22 --| Get_IO_Details |
23 --| Get_KIT_Status |
24 --| |
25 --| HISTORY |
26 --+=======================================================================
27
28 --===================
29 -- CONSTANTS
30 --===================
31 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_SO_MDTR.';
32
33 --===================
34 -- PRIVATE PROCEDURES
35 --===================
36
37 --========================================================================
38 -- PROCEDURE : Get_SO_Transactions PRIVATE
39 -- PARAMETERS: so_crsr REF cursor
40 -- x_return_status return status
41 -- p_start_date Transaction start date
42 -- p_end_date Transaction end date
43 -- COMMENT :
44 -- This opens the cursor for SO and returns the cursor.
45 --========================================================================
46
47 PROCEDURE Get_SO_Transactions
48 ( so_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.soCurTyp
49 , p_movement_transaction IN
50 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
51 , p_start_date IN DATE
52 , p_end_date IN DATE
53 , x_return_status OUT NOCOPY VARCHAR2
54 )
55 IS
56 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_SO_Transactions';
57 BEGIN
58 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
59 THEN
60 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
61 , G_MODULE_NAME || l_procedure_name || '.begin'
62 ,'enter procedure'
63 );
64 END IF;
65
66 x_return_status := 'Y';
67
68 IF so_crsr%ISOPEN THEN
69 CLOSE so_crsr;
70 END IF;
71
72 --Fix bug 2976193 remove hr_locations_all
73 --Fix bug 3506597, replace confirm_date with initial_pickup_date
74 --Fix bug 3624099, add hints according to Performance team's suggestion
75 --Fix perf bug 4912552, use hr_organization_information to replace
76 --org_organization_definitions according to proposal from INV
77 --karthik.gnanamurthy, because inventory organization is already existing
78 --in rcv_transactions, so it's not required to validate the organization
79 --again in mtl_parameters or hr_all_organization_units as OOD does
80 IF NVL(p_movement_transaction.creation_method,'A') = 'A'
81 THEN
82 IF NVL(p_movement_transaction.document_source_type,'SO') = 'IO'
83 THEN
84 OPEN so_crsr FOR
85 SELECT
86 wdd.delivery_detail_id picking_line_detail_id
87 , wdd.organization_id warehouse_id
88 , ol.ship_to_org_id ultimate_ship_to_id
89 , wnd.initial_pickup_date date_closed
90 , ol.line_id
91 , oh.order_number
92 , ras.bill_to_site_use_id
93 , ol.item_type_code
94 , ol.link_to_line_id
95 FROM
96 WSH_NEW_DELIVERIES_OB_GRP_V wnd
97 , wsh_delivery_assignments_v wda
98 , WSH_DELIVERY_DETAILS_OB_GRP_V wdd
99 , hr_organization_information hoi
100 , OE_ORDER_LINES_ALL ol
101 , OE_ORDER_HEADERS_ALL oh
102 , HZ_CUST_SITE_USES_ALL ras
103 WHERE wnd.delivery_id = wda.delivery_id
104 AND wda.delivery_detail_id = wdd.delivery_detail_id
105 AND wdd.source_line_id = ol.line_id
106 AND ol.header_id = oh.header_id
107 AND wdd.source_code = 'OE'
108 AND wnd.organization_id = hoi.organization_id --fix perf bug 4912552
109 AND hoi.org_information_context = 'Accounting Information'
110 AND ol.ship_to_org_id = ras.site_use_id
111 --Bugfix 16214456: OE schema is getting obsoleted.
112 --AND OE_INSTALL.Get_Active_Product = 'ONT'
113 AND oh.order_source_id = 10
114 AND wdd.shipped_quantity > 0
115 AND wnd.status_code in ('IT','CL')
116 AND wdd.mvt_stat_status in ('NEW','MODIFIED')
117 --AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
118 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
119 AND wnd.initial_pickup_date between p_start_date and p_end_date;
120 ELSE
121 OPEN so_crsr FOR
122 SELECT
123 wdd.delivery_detail_id picking_line_detail_id
124 , wdd.organization_id warehouse_id
125 , ol.ship_to_org_id ultimate_ship_to_id
126 , wnd.initial_pickup_date date_closed
127 , ol.line_id
128 , oh.order_number
129 , ras.bill_to_site_use_id
130 , ol.item_type_code
131 , ol.link_to_line_id
132 FROM
133 WSH_NEW_DELIVERIES_OB_GRP_V wnd
134 , wsh_delivery_assignments_v wda
135 , WSH_DELIVERY_DETAILS_OB_GRP_V wdd
136 , hr_organization_information hoi
137 , OE_ORDER_LINES_ALL ol
138 , OE_ORDER_HEADERS_ALL oh
139 , HZ_CUST_SITE_USES_ALL ras
140 WHERE wnd.delivery_id = wda.delivery_id
141 AND wda.delivery_detail_id = wdd.delivery_detail_id
142 AND wdd.source_line_id = ol.line_id
143 AND ol.header_id = oh.header_id
144 AND wdd.source_code = 'OE'
145 AND wnd.organization_id = hoi.organization_id --fix perf bug2812364
146 AND hoi.org_information_context = 'Accounting Information'
147 AND ol.ship_to_org_id = ras.site_use_id
148 --Bugfix 16214456: OE schema is getting obsoleted.
149 --AND OE_INSTALL.Get_Active_Product = 'ONT'
150 AND wdd.shipped_quantity > 0
151 AND wnd.status_code in ('IT','CL')
152 AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORDISP')
153 --AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
154 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
155 AND wnd.initial_pickup_date between p_start_date and p_end_date;
156 END IF;
157 ELSE
158 OPEN so_crsr FOR
159 SELECT
160 wdd.delivery_detail_id picking_line_detail_id
161 , wdd.organization_id warehouse_id
162 , ol.ship_to_org_id ultimate_ship_to_id
163 , wnd.initial_pickup_date date_closed
164 , ol.line_id
165 , oh.order_number
166 , ras.bill_to_site_use_id
167 , ol.item_type_code
168 , ol.link_to_line_id
169 FROM
170 WSH_NEW_DELIVERIES_OB_GRP_V wnd
171 , wsh_delivery_assignments_v wda
172 , WSH_DELIVERY_DETAILS_OB_GRP_V wdd
173 , OE_ORDER_LINES_ALL ol
174 , OE_ORDER_HEADERS_ALL oh
175 , HZ_CUST_SITE_USES_ALL ras
176 , hr_organization_information hoi
177 WHERE wnd.delivery_id = wda.delivery_id
178 AND wda.delivery_detail_id = wdd.delivery_detail_id
179 AND wdd.source_line_id = ol.line_id
180 AND ol.header_id = oh.header_id
181 AND wdd.source_code = 'OE'
182 AND wnd.organization_id = hoi.organization_id --fix perf bug2812364
183 AND hoi.org_information_context = 'Accounting Information'
184 AND ol.ship_to_org_id = ras.site_use_id
185 --Bugfix 16214456: OE schema is getting obsoleted.
186 --AND OE_INSTALL.Get_Active_Product = 'ONT'
187 AND wdd.shipped_quantity > 0
188 AND wnd.status_code in ('IT','CL')
189 AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORDISP')
190 --AND ol.item_type_code <> 'INCLUDED' --Fix bug4185582
191 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
192 AND wnd.initial_pickup_date is NOT NULL
193 AND wnd.name = p_movement_transaction.shipment_reference;
194 END IF;
195
196 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
197 THEN
198 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
199 , G_MODULE_NAME || l_procedure_name || '.end'
200 ,'exit procedure'
201 );
202 END IF;
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 x_return_status := 'N';
207 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
208 THEN
209 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
210 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
211 , 'Exception'
212 );
213 END IF;
214 WHEN OTHERS THEN
215 x_return_status := 'N';
216 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
217 THEN
218 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
219 , G_MODULE_NAME || l_procedure_name||'. Others exception'
220 , 'Exception'
221 );
222 END IF;
223
224 END Get_SO_Transactions;
225
226 --========================================================================
227 -- PROCEDURE : Get_Triangulation_Txns PRIVATE
228 -- PARAMETERS: sot_crsr REF cursor
229 -- x_return_status return status
230 -- p_start_date Transaction start date
231 -- p_end_date Transaction end date
232 -- COMMENT :
233 -- Get SO transactions for the legal entity which initiates
234 -- this SO (not pick release side). This will be used to
235 -- create Arrival transaction for this legal entity in case
236 -- of invoice based triangulation mode. The dispatch record
237 -- will be picked by the regular Get_SO_Transaction
238 --========================================================================
239
240 PROCEDURE Get_Triangulation_Txns
241 ( sot_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.soCurTyp
242 , p_movement_transaction IN
243 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
244 , p_start_date IN DATE
245 , p_end_date IN DATE
246 , x_return_status OUT NOCOPY VARCHAR2
247 )
248 IS
249 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Triangulation_Txns';
250 BEGIN
251 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
252 THEN
253 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
254 , G_MODULE_NAME || l_procedure_name || '.begin'
255 ,'enter procedure'
256 );
257 END IF;
258
259 x_return_status := 'Y';
260
261 IF sot_crsr%ISOPEN THEN
262 CLOSE sot_crsr;
263 END IF;
264
265 --Fix performance bug2812364, remove table oe_order_headers_all and
266 --and hz_cust_site_uses_all
267 --R12 Legal entity uptake, replace hr_operating_units base tables with XLE package
268 --because this view is not existed anymore
269 --Fix bug 5443301, replace ol.sold_from_org_id with ol.org_id
270 --org_id is the correct column to get operating unit
271 IF NVL(p_movement_transaction.creation_method,'A') = 'A'
272 THEN
273 OPEN sot_crsr FOR
274 SELECT
275 wdd.delivery_detail_id picking_line_detail_id
276 , wdd.organization_id warehouse_id
277 , ol.ship_to_org_id ultimate_ship_to_id
278 , wnd.initial_pickup_date date_closed
279 , ol.line_id
280 , wdd.source_header_number
281 , ol.item_type_code
282 , ol.link_to_line_id
283 FROM
284 WSH_NEW_DELIVERIES_OB_GRP_V wnd
285 , wsh_delivery_assignments_v wda
286 , WSH_DELIVERY_DETAILS_OB_GRP_V wdd
287 , OE_ORDER_LINES_ALL ol
288 , hr_organization_information hoi /*Bug 8467743*/
289 WHERE wnd.delivery_id = wda.delivery_id
290 AND wda.delivery_detail_id = wdd.delivery_detail_id
291 AND wdd.source_line_id = ol.line_id
292 AND wdd.source_code = 'OE'
293 --Bugfix 16214456: OE schema is getting obsoleted.
294 --AND OE_INSTALL.Get_Active_Product = 'ONT'
295 AND wdd.shipped_quantity > 0
296 AND ol.order_source_id <> 10
297 AND wnd.status_code in ('IT','CL')
298 AND wdd.mvt_stat_status in ('NEW','MODIFIED','FORARVL')
299 AND wnd.initial_pickup_date between p_start_date and p_end_date
300 AND hoi.org_information_context = 'Operating Unit Information' /*Bug 8467743*/
301 AND hoi.organization_id = nvl(ol.org_id,ol.sold_from_org_id) /*Bug 8467743*/
302 --AND p_movement_transaction.entity_org_id =TO_NUMBER(hoi.org_information2); /*Bug 8467743*/
303 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id); /* Bug 12778658 */
304 /* XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
305 ('SOLD_TO', ol.sold_to_org_id
306 , null, null, ol.org_id);*/
307 END IF;
308
309 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
310 THEN
311 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
312 , G_MODULE_NAME || l_procedure_name || '.end'
313 ,'exit procedure'
314 );
315 END IF;
316
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 x_return_status := 'N';
320 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
321 THEN
322 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
323 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
324 , 'Exception'
325 );
326 END IF;
327 WHEN OTHERS THEN
328 x_return_status := 'N';
329 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
330 THEN
331 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
332 , G_MODULE_NAME || l_procedure_name||'. Others exception'
333 , 'Exception'
334 );
335 END IF;
336
337 END Get_Triangulation_Txns;
338
339 --========================================================================
340 -- PROCEDURE : Get_TwoLeOneCntry_Txns PRIVATE
341 -- PARAMETERS: sot_crsr REF cursor
342 -- x_return_status return status
343 -- p_start_date Transaction start date
344 -- p_end_date Transaction end date
345 -- COMMENT :
346 --========================================================================
347
348 PROCEDURE Get_TwoLeOneCntry_Txns
349 ( sot_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.soCurTyp
350 , p_movement_transaction IN
351 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
352 , p_start_date IN DATE
353 , p_end_date IN DATE
354 , x_return_status OUT NOCOPY VARCHAR2
355 )
356 IS
357 l_le_location VARCHAR2(80);
358
359 BEGIN
360 null;
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 x_return_status := 'N';
365 WHEN OTHERS THEN
366 x_return_status := 'N';
367
368 END Get_TwoLeOneCntry_Txns;
369
370
371 --========================================================================
372 -- PROCEDURE : Get_SO_Details PRIVATE
373 -- PARAMETERS: x_return_status return status
374 -- p_movement_transaction movement transaction record
375 -- COMMENT : Get all the additional data required for PO
376 --========================================================================
377
378 PROCEDURE Get_SO_Details
379 ( x_movement_transaction IN OUT NOCOPY
380 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
381 , x_return_status OUT NOCOPY VARCHAR2
382 )
383
384 IS
385 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
386 l_unit_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
387 l_qty_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
388 l_currency_code OE_ORDER_HEADERS_ALL.Transactional_Curr_Code%TYPE;
389 l_error_code NUMBER;
390 l_return_status VARCHAR2(1);
391 l_item_type_code oe_order_lines_all.item_type_code%TYPE;
392 l_order_uom OE_ORDER_LINES_ALL.order_Quantity_Uom%TYPE;
393 l_uom_conv_rate NUMBER;
394 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_SO_Details';
395
396 --Fix bug 3318761, replace wsh_shipping_details_v with wdd and wnd tables
397 --For performance reason, calculate item cost separately
398 --Timezone support,not select transaction date here again
399
400 --Fix bug 5443301, replace oola.sold_from_org_id with oola.org_id
401 --org_id is the correct column to get operating unit and
402 --oola.sold_from_org_id is no more populated by OM
403 CURSOR so_details IS
404 SELECT
405 --oola.ship_to_org_id
406 wdd.fob_code
407 , NVL(wdd.ship_method_code,'3')
408 , wdd.delivery_detail_id
409 --, oola.line_id
410 , ooha.header_id
411 , ooha.order_number
412 , oola.line_number
413 , wdd.organization_id
414 --, oola.sold_from_org_id
415 , oola.org_id
416 , wdd.delivery_detail_id
417 , wdd.shipped_quantity
418 , wdd.mvt_stat_status
419 , wdd.movement_id
420 , ooha.sold_to_org_id
421 , nvl(ooha.invoice_to_org_id,ooha.sold_to_org_id)
422 , ooha.sold_to_org_id
423 , wdd.requested_quantity_uom
424 , wdd.inventory_item_id
425 , si.description
426 , si.primary_uom_code
427 , ooha.transactional_curr_code
428 , ooha.conversion_type_code
429 , ooha.conversion_rate
430 , ooha.conversion_rate_date
431 , oola.unit_selling_price
432 , oola.orig_sys_line_ref
433 , ooha.orig_sys_document_ref
434 , ooha.order_source_id
435 , rac.party_name
436 --Bugfix 9676611: customer_number should come from HZ_CUST_ACCOUNTS
437 --, rac.party_number
438 , hzc.account_number
439 , substrb(rac.province,1,30)
440 , wnd.name
441 , oola.item_type_code
442 , oola.order_quantity_uom
443 FROM
444 OE_ORDER_HEADERS_ALL ooha
445 , OE_ORDER_LINES_ALL oola
446 , wsh_delivery_details_ob_grp_v wdd
447 , wsh_new_deliveries_ob_grp_v wnd
448 , wsh_delivery_assignments_v wda
449 , HZ_PARTIES rac
450 , HZ_CUST_ACCOUNTS hzc
451 , MTL_SYSTEM_ITEMS si
452 WHERE wnd.delivery_id = wda.delivery_id
453 AND wda.delivery_detail_id = wdd.delivery_detail_id
454 AND ooha.header_id = oola.header_id
455 AND oola.line_id = wdd.source_line_id
456 AND rac.party_id = hzc.party_id
457 AND ooha.sold_to_org_id = hzc.cust_account_id
458 AND wdd.inventory_item_id = si.inventory_item_id
459 AND wdd.organization_id = si.organization_id
460 AND wdd.delivery_detail_id = x_movement_transaction.picking_line_detail_id;
461
462 CURSOR c_item_cost IS
463 SELECT
464 item_cost
465 FROM
466 CST_ITEM_COSTS_FOR_GL_VIEW
467 WHERE organization_id = x_movement_transaction.organization_id
468 AND inventory_item_id = x_movement_transaction.inventory_item_id;
469 BEGIN
470 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
471 THEN
472 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
473 , G_MODULE_NAME || l_procedure_name || '.begin'
474 ,'enter procedure'
475 );
476 END IF;
477
478 x_return_status := 'Y';
479
480 OPEN so_details;
481 FETCH so_details INTO
482 --x_movement_transaction.ship_to_site_use_id
483 x_movement_transaction.delivery_terms
484 , x_movement_transaction.transport_mode
485 , x_movement_transaction.picking_line_id
486 -- , x_movement_transaction.order_line_id
487 , x_movement_transaction.order_header_id
488 , x_movement_transaction.order_number
489 , x_movement_transaction.line_number
490 , x_movement_transaction.organization_id
491 , l_shipment_transaction.so_org_id
492 , x_movement_transaction.picking_line_detail_id
493 , x_movement_transaction.transaction_quantity
494 , l_shipment_transaction.mvt_stat_status
495 , x_movement_transaction.movement_id
496 , x_movement_transaction.ship_to_customer_id
497 , x_movement_transaction.bill_to_site_use_id
498 , x_movement_transaction.bill_to_customer_id
499 , x_movement_transaction.transaction_uom_code
500 , x_movement_transaction.inventory_item_id
501 , x_movement_transaction.item_description
502 , x_movement_transaction.primary_uom_code
503 , x_movement_transaction.currency_code
504 , x_movement_transaction.currency_conversion_type
505 , x_movement_transaction.currency_conversion_rate
506 , x_movement_transaction.currency_conversion_date
507 , l_unit_selling_price
508 , l_shipment_transaction.req_line_num
509 , l_shipment_transaction.req_num
510 , l_shipment_transaction.order_source_id
511 , x_movement_transaction.customer_name
512 , x_movement_transaction.customer_number
513 , x_movement_transaction.area
514 , x_movement_transaction.shipment_reference
515 , l_item_type_code
516 , l_order_uom;
517
518 IF so_details%NOTFOUND THEN
519 CLOSE so_details;
520 x_return_status := 'N';
521
522 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
523 THEN
524 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
525 , G_MODULE_NAME || l_procedure_name || '.end return N'
526 ,'exit procedure'
527 );
528 END IF;
529 RETURN;
530 END IF;
531 CLOSE so_details;
532
533 IF (x_movement_transaction.movement_id IS NOT NULL)
534 AND (NVL(l_shipment_transaction.mvt_stat_status,'NEW')='MODIFIED')
535 THEN
536 x_movement_transaction.movement_type := 'DA';
537 ELSE
538 x_movement_transaction.movement_type := 'D';
539 END IF;
540
541 x_movement_transaction.document_source_type := 'SO';
542 x_movement_transaction.transaction_nature := '11';
543
544 --Change in R12, origin country should be same as of the shipping warehouse
545 x_movement_transaction.origin_territory_code :=
546 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
547 (p_warehouse_id => x_movement_transaction.organization_id);
548 --x_movement_transaction.dispatch_territory_code;
549
550 IF x_movement_transaction.currency_code IS NULL
551 THEN
552 x_movement_transaction.currency_code :=
553 x_movement_transaction.gl_currency_code;
554 END IF;
555
556 --Get document unit price for CTO item
557 IF l_item_type_code = 'CONFIG'
558 THEN
559 --Call BOM procedure to get unit selling price
560 CTO_PUBLIC_UTILITY_PK.Get_Selling_Price
561 ( p_config_line_id => x_movement_transaction.order_line_id
562 , x_unit_selling_price => l_unit_selling_price
563 , x_qty_selling_price => l_qty_selling_price
564 , x_currency_code => l_currency_code
565 , x_return_status => l_return_status
566 , x_error_code => l_error_code
567 );
568 END IF;
569
570 --SO order uom maynot be same as shipped qty uom,thus when calculate document
571 --line ext value, we need to consider uom conversion
572 IF x_movement_transaction.transaction_uom_code <> l_order_uom
573 THEN
574 INV_CONVERT.Inv_Um_Conversion
575 ( from_unit => x_movement_transaction.transaction_uom_code
576 , to_unit => l_order_uom
577 , item_id => x_movement_transaction.inventory_item_id
578 , uom_rate => l_uom_conv_rate
579 );
580 ELSE
581 l_uom_conv_rate := 1;
582 END IF;
583
584 --Set document unit price and document line value
585 --This unit price would be for each transaction uom
586 x_movement_transaction.document_unit_price :=
587 NVL(l_unit_selling_price,0) * l_uom_conv_rate;
588 x_movement_transaction.document_line_ext_value :=
589 x_movement_transaction.document_unit_price *
590 NVL(x_movement_transaction.transaction_quantity,0);
591
592 --
593 -- bug 12844667
594 -- For process Items use the OPM API to derive costs
595 --
596 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(x_movement_transaction.organization_id) = TRUE) THEN
597 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
598 ( p_org_id => x_movement_transaction.organization_id
599 , p_item_id => x_movement_transaction.inventory_item_id
600 , p_transaction_date => x_movement_transaction.transaction_date
601 );
602
603 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
604 THEN
605 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
606 , G_MODULE_NAME || l_procedure_name
607 ,'item_cost from OPM:' || x_movement_transaction.item_cost
608 );
609 END IF;
610
611 ELSE
612 --Get item cost for regular item
613 OPEN c_item_cost;
614 FETCH c_item_cost INTO
615 x_movement_transaction.item_cost;
616 CLOSE c_item_cost;
617
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
619 THEN
620 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
621 , G_MODULE_NAME || l_procedure_name
622 ,'item_cost from discrete:' || x_movement_transaction.item_cost
623 );
624 END IF;
625 END IF;
626
627 /*-- If the sales order is a OPM sales order, get the item cost
628 -- that is related to the OPM.
629 IF (INV_MGD_MVT_UTILS_PKG.Is_Line_A_Process_Line
630 (p_organization_id => x_movement_transaction.organization_id
631 ,p_inventory_item_id => x_movement_transaction.inventory_item_id))
632 THEN
633 INV_MGD_MVT_UTILS_PKG.Get_OPM_Item_Cost
634 ( p_inventory_item_id => x_movement_transaction.inventory_item_id
635 , p_organization_id => x_movement_transaction.organization_id
636 , p_transaction_date => x_movement_transaction.transaction_date
637 , x_item_cost => x_movement_transaction.item_cost
638 , x_currency_code => l_opm_curr_code
639 , x_return_status => l_opm_return_status
640 , x_msg_count => l_opm_msg_count
641 , x_msg_data => l_opm_msg_data
642 );
643 END IF;*/
644
645 -- IF order category is of type P then it is an IO source type
646 IF l_shipment_transaction.order_source_id = 10
647 THEN
648 x_movement_transaction.document_source_type := 'IO';
649
650 Get_IO_Details
651 ( x_movement_transaction => x_movement_transaction
652 , x_return_status => x_return_status
653 );
654 END IF;
655
656 --Find out the operating unit where this SO is shipped
657 IF x_movement_transaction.organization_id IS NOT NULL
658 THEN
659 SELECT
660 TO_NUMBER(HOI2.ORG_INFORMATION3)
661 INTO
662 l_shipment_transaction.org_id
663 FROM
664 HR_ORGANIZATION_INFORMATION HOI1
665 , HR_ORGANIZATION_INFORMATION HOI2
666 , MTL_PARAMETERS MP
667 WHERE MP.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
668 AND MP.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
669 AND HOI1.ORG_INFORMATION1 = 'INV'
670 AND HOI1.ORG_INFORMATION2 = 'Y'
671 AND HOI1.ORG_INFORMATION_CONTEXT = 'CLASS'
672 AND HOI2.ORG_INFORMATION_CONTEXT = 'Accounting Information'
673 AND mp.organization_id = x_movement_transaction.organization_id;
674 END IF;
675
676 IF ((l_shipment_transaction.org_id <> l_shipment_transaction.so_org_id)
677 AND NVL(l_shipment_transaction.org_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
678 AND NVL(l_shipment_transaction.so_org_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
679 THEN
680 x_movement_transaction.triangulation_country_code :=
681 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
682 (p_warehouse_id => l_shipment_transaction.so_org_id);
683
684 x_movement_transaction.triangulation_country_eu_code :=
685 INV_MGD_MVT_UTILS_PKG.Convert_Territory_Code
686 (x_movement_transaction.triangulation_country_code);
687 END IF;
688
689 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
690 THEN
691 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
692 , G_MODULE_NAME || l_procedure_name || '.end'
693 ,'exit procedure'
694 );
695 END IF;
696 EXCEPTION
697 WHEN NO_DATA_FOUND THEN
698 x_return_status := 'N';
699 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
700 THEN
701 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
702 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
703 , 'Exception'
704 );
705 END IF;
706 WHEN OTHERS THEN
707 x_return_status := 'N';
708 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
709 THEN
710 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
711 , G_MODULE_NAME || l_procedure_name||'. Others exception'
712 , 'Exception'
713 );
714 END IF;
715
716 END Get_SO_Details;
717
718 --========================================================================
719 -- PROCEDURE : Get_KIT_SO_Details PRIVATE
720 -- PARAMETERS: x_movement_transaction movement transaction record
721 -- p_link_to_line_id parent line id
722 -- COMMENT : Get all the additional data required for KIT SO
723 --========================================================================
724 PROCEDURE Get_KIT_SO_Details
725 ( p_link_to_line_id IN VARCHAR2
726 , x_movement_transaction IN OUT NOCOPY
727 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
728 )
729 IS
730 l_unit_selling_price OE_ORDER_LINES_ALL.Unit_Selling_Price%TYPE;
731 l_order_uom OE_ORDER_LINES_ALL.order_Quantity_Uom%TYPE;
732 l_uom_conv_rate NUMBER;
733 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_KIT_SO_Details';
734
735 --Get line information for kit item line
736 CURSOR c_kit_line IS
737 SELECT
738 oola.line_number
739 , oola.unit_selling_price
740 , NVL(oola.shipped_quantity, oola.fulfilled_quantity)
741 , oola.order_quantity_uom
742 , oola.order_quantity_uom
743 , oola.ship_from_org_id
744 , oola.inventory_item_id
745 , msi.description
746 , msi.primary_uom_code
747 FROM
748 oe_order_lines_all oola
749 , mtl_system_items msi
750 WHERE oola.inventory_item_id = msi.inventory_item_id
751 AND oola.ship_from_org_id = msi.organization_id
752 AND line_id = p_link_to_line_id;
753 BEGIN
754 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
755 THEN
756 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
757 , G_MODULE_NAME || l_procedure_name || '.begin'
758 ,'enter procedure'
759 );
760 END IF;
761
762 OPEN c_kit_line;
763 FETCH c_kit_line INTO
764 x_movement_transaction.line_number
765 , l_unit_selling_price
766 , x_movement_transaction.transaction_quantity
767 , x_movement_transaction.transaction_uom_code
768 , l_order_uom
769 , x_movement_transaction.organization_id
770 , x_movement_transaction.inventory_item_id
771 , x_movement_transaction.item_description
772 , x_movement_transaction.primary_uom_code;
773
774 CLOSE c_kit_line;
775
776 --SO order uom maynot be same as shipped qty uom,thus when calculate document
777 --line ext value, we need to consider uom conversion
778 IF x_movement_transaction.transaction_uom_code <> l_order_uom
779 THEN
780 INV_CONVERT.Inv_Um_Conversion
781 ( from_unit => x_movement_transaction.transaction_uom_code
782 , to_unit => l_order_uom
783 , item_id => x_movement_transaction.inventory_item_id
784 , uom_rate => l_uom_conv_rate
785 );
786 ELSE
787 l_uom_conv_rate := 1;
788 END IF;
789
790 --Set document unit price and document line value
791 --This unit price would be for each transaction uom
792 x_movement_transaction.document_unit_price :=
793 NVL(l_unit_selling_price,0) * l_uom_conv_rate;
794 x_movement_transaction.document_line_ext_value :=
795 x_movement_transaction.document_unit_price *
796 NVL(x_movement_transaction.transaction_quantity,0);
797
798 --Create record for parent kit, so set line id to parent line id
799 x_movement_transaction.order_line_id := p_link_to_line_id;
800
801 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
802 THEN
803 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
804 , G_MODULE_NAME || l_procedure_name || '.end'
805 ,'exit procedure'
806 );
807 END IF;
808 EXCEPTION
809 WHEN NO_DATA_FOUND THEN
810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
811 THEN
812 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
813 , G_MODULE_NAME || l_procedure_name
814 ,'when no data found exception'
815 );
816 END IF;
817
818 WHEN OTHERS THEN
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
823 ,'when others exception'
824 );
825 END IF;
826
827 END Get_KIT_SO_Details;
828
829
830 --========================================================================
831 -- PROCEDURE : Get_IO_Details PRIVATE
832 -- PARAMETERS: x_return_status return status
833 -- p_movement_transaction movement transaction record
834 -- COMMENT : Get all the additional data required for IO
835 --========================================================================
836
837 PROCEDURE Get_IO_Details
838 ( x_movement_transaction IN OUT NOCOPY
839 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
840 , x_return_status OUT NOCOPY VARCHAR2
841 )
842
843 IS
844 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_IO_Details';
845
846 --Get requisition header and line id
847 CURSOR l_Get_Req_Info IS
848 SELECT
849 source_document_id
850 , source_document_line_id
851 FROM
852 oe_order_lines_all oola
853 , po_requisition_headers_all prha
854 WHERE prha.requisition_header_id = oola.source_document_id
855 AND line_id = x_movement_transaction.order_line_id;
856
857 CURSOR l_io_organization IS
858 SELECT
859 source_organization_id
860 , destination_organization_id
861 FROM
862 po_requisition_lines_all
863 WHERE
864 requisition_line_id = x_movement_transaction.requisition_line_id;
865 BEGIN
866 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
867 THEN
868 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
869 , G_MODULE_NAME || l_procedure_name || '.begin'
870 ,'enter procedure'
871 );
872 END IF;
873
874 x_return_status := 'Y';
875
876 OPEN l_Get_Req_Info;
877 FETCH l_Get_Req_Info INTO
878 x_movement_transaction.requisition_header_id
879 , x_movement_transaction.requisition_line_id;
880
881 IF l_Get_Req_Info%NOTFOUND
882 THEN
883 x_return_status := 'N';
884 END IF;
885
886 CLOSE l_Get_Req_Info;
887
888 IF x_movement_transaction.requisition_line_id IS NOT NULL
889 THEN
890 OPEN l_io_organization;
891 FETCH l_io_organization INTO
892 x_movement_transaction.from_organization_id
893 , x_movement_transaction.to_organization_id;
894
895 IF l_io_organization%NOTFOUND
896 THEN
897 x_return_status := 'N';
898 END IF;
899
900 CLOSE l_io_organization;
901 END IF;
902
903 x_movement_transaction.invoice_id := null;
904 x_movement_transaction.invoice_batch_id := null;
905 x_movement_transaction.invoice_line_ext_value := null;
906 x_movement_transaction.invoice_quantity := null;
907 x_movement_transaction.invoice_unit_price := null;
908 x_movement_transaction.invoice_line_ext_value := null;
909 x_movement_transaction.invoice_line_reference := null;
910 x_movement_transaction.customer_trx_line_id := null;
911 x_movement_transaction.financial_document_flag := 'NOT_REQUIRED';
912 x_movement_transaction.document_source_type := 'IO';
913
914 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
915 THEN
916 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
917 , G_MODULE_NAME || l_procedure_name || '.end'
918 ,'exit procedure'
919 );
920 END IF;
921
922 EXCEPTION
923 WHEN NO_DATA_FOUND THEN
924 x_return_status := 'N';
925 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
926 THEN
927 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
928 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
929 , 'Exception'
930 );
931 END IF;
932 WHEN OTHERS THEN
933 x_return_status := 'N';
934 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
935 THEN
936 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
937 , G_MODULE_NAME || l_procedure_name||'. Others exception'
938 , 'Exception'
939 );
940 END IF;
941
942 END Get_IO_Details;
943
944 --========================================================================
945 -- PROCEDURE : Update_SO_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_SO_Transactions
952 ( p_movement_transaction IN
953 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
954 , p_status IN VARCHAR2
955 , x_return_status OUT NOCOPY VARCHAR2
956 )
957 IS
958 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
959 l_mvt_stat_status wsh_delivery_details.mvt_stat_status%TYPE;
960 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_SO_Transactions';
961 BEGIN
962 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
963 THEN
964 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
965 , G_MODULE_NAME || l_procedure_name || '.begin'
966 ,'enter procedure'
967 );
968 END IF;
969
970 x_return_status := 'Y';
971
972 --Find out the current status
973 SELECT mvt_stat_status
974 INTO l_mvt_stat_status
975 FROM wsh_delivery_details_ob_grp_v
976 WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
977
978 -- Update the transaction table
979 IF l_mvt_stat_status = 'NEW'
980 THEN
981 IF p_status = 'ARRIVALPROCESSED'
982 THEN
983 --cross legal entity SO, the arrival is already created,so set the status
984 --to "FORDISP" to be picked up again when run processor in other legal
985 --entity
986 UPDATE wsh_delivery_details
987 SET mvt_stat_status = 'FORDISP'
988 , movement_id = p_movement_transaction.movement_id
989 WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
990 ELSIF p_status = 'DISPPROCESSED'
991 THEN
992 --cross legal entity SO, the dispatch is already created,so set the status
993 --to "FORARVL" to be picked up again when run processor in other legal
994 --entity
995 UPDATE wsh_delivery_details
996 SET mvt_stat_status = 'FORARVL'
997 , movement_id = p_movement_transaction.movement_id
998 WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
999 ELSE
1000 --Regular SO
1001 UPDATE wsh_delivery_details
1002 SET mvt_stat_status = 'PROCESSED'
1003 , movement_id = p_movement_transaction.movement_id
1004 WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
1005 END IF;
1006 ELSE
1007 UPDATE wsh_delivery_details
1008 SET mvt_stat_status = 'PROCESSED'
1009 , movement_id = p_movement_transaction.movement_id
1010 WHERE delivery_detail_id = p_movement_transaction.picking_line_detail_id;
1011 END IF;
1012
1013 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1014 THEN
1015 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1016 , G_MODULE_NAME || l_procedure_name || '.end'
1017 ,'exit procedure'
1018 );
1019 END IF;
1020
1021 EXCEPTION
1022 WHEN NO_DATA_FOUND THEN
1023 x_return_status := 'N';
1024 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1025 THEN
1026 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1027 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1028 , 'Exception'
1029 );
1030 END IF;
1031 WHEN OTHERS THEN
1032 x_return_status := 'N';
1033 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1034 THEN
1035 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1036 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1037 , 'Exception'
1038 );
1039 END IF;
1040
1041 END Update_SO_Transactions;
1042
1043 --========================================================================
1044 -- PROCEDURE : Update_KIT_SO_Transactions PRIVATE
1045 -- PARAMETERS: x_return_status return status
1046 -- p_movement_transaction movement transaction record
1047 -- COMMENT : Update the status of the transaction record to PROCESSED
1048 --========================================================================
1049
1050 PROCEDURE Update_KIT_SO_Transactions
1051 ( p_movement_id IN NUMBER
1052 , p_delivery_detail_id IN NUMBER
1053 , p_link_to_line_id IN NUMBER
1054 , p_status IN VARCHAR2
1055 , x_return_status OUT NOCOPY VARCHAR2
1056 )
1057 IS
1058 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
1059 l_mvt_stat_status wsh_delivery_details.mvt_stat_status%TYPE;
1060 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_KIT_SO_Transactions';
1061 BEGIN
1062 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1063 THEN
1064 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1065 , G_MODULE_NAME || l_procedure_name || '.begin'
1066 ,'enter procedure'
1067 );
1068 END IF;
1069
1070 x_return_status := 'Y';
1071
1072 --Find out the current status
1073 SELECT mvt_stat_status
1074 INTO l_mvt_stat_status
1075 FROM wsh_delivery_details_ob_grp_v
1076 WHERE delivery_detail_id = p_delivery_detail_id;
1077
1078 -- Update the transaction table
1079 IF l_mvt_stat_status = 'NEW'
1080 THEN
1081 IF p_status = 'ARRIVALPROCESSED'
1082 THEN
1083 --cross legal entity SO, the arrival is already created,so set the status
1084 --to "FORDISP" to be picked up again when run processor in other legal
1085 --entity
1086 UPDATE wsh_delivery_details
1087 SET mvt_stat_status = 'FORDISP'
1088 , movement_id = p_movement_id
1089 WHERE source_line_id IN (SELECT line_id --fix bug 4185582
1090 FROM oe_order_lines_all
1091 WHERE link_to_line_id = p_link_to_line_id);
1092 ELSIF p_status = 'DISPPROCESSED'
1093 THEN
1094 --cross legal entity SO, the dispatch is already created,so set the status
1095 --to "FORARVL" to be picked up again when run processor in other legal
1096 --entity
1097 UPDATE wsh_delivery_details
1098 SET mvt_stat_status = 'FORARVL'
1099 , movement_id = p_movement_id
1100 WHERE source_line_id IN (SELECT line_id --fix bug 4185582
1101 FROM oe_order_lines_all
1102 WHERE link_to_line_id = p_link_to_line_id);
1103 ELSE
1104 --Regular SO
1105 UPDATE wsh_delivery_details
1106 SET mvt_stat_status = 'PROCESSED'
1107 , movement_id = p_movement_id
1108 WHERE source_line_id IN (SELECT line_id --fix bug 4185582
1109 FROM oe_order_lines_all
1110 WHERE link_to_line_id = p_link_to_line_id);
1111 END IF;
1112 ELSE
1113 UPDATE wsh_delivery_details
1114 SET mvt_stat_status = 'PROCESSED'
1115 , movement_id = p_movement_id
1116 WHERE source_line_id IN (SELECT line_id --fix bug 4185582
1117 FROM oe_order_lines_all
1118 WHERE link_to_line_id = p_link_to_line_id);
1119 END IF;
1120
1121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1122 THEN
1123 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1124 , G_MODULE_NAME || l_procedure_name || '.end'
1125 ,'exit procedure'
1126 );
1127 END IF;
1128
1129 EXCEPTION
1130 WHEN NO_DATA_FOUND THEN
1131 x_return_status := 'N';
1132 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1133 THEN
1134 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1135 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1136 , 'Exception'
1137 );
1138 END IF;
1139 WHEN OTHERS THEN
1140 x_return_status := 'N';
1141 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1142 THEN
1143 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1144 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1145 , 'Exception'
1146 );
1147 END IF;
1148
1149 END Update_KIT_SO_Transactions;
1150
1151
1152 --========================================================================
1153 -- FUNCTION : Get_KIT_Status
1154 -- PARAMETERS: p_delivery_detail_id
1155 -- COMMENT : Function that returns the status of a movement kit record
1156 -- if a movement record for kit has been created, the status
1157 -- returned is 'Y', otherwise return 'N'
1158 --=========================================================================
1159 FUNCTION Get_KIT_Status
1160 ( p_delivery_detail_id IN NUMBER
1161 )
1162 RETURN VARCHAR2
1163 IS
1164 l_kit_status VARCHAR2(1);
1165 l_mvt_status VARCHAR2(30);
1166 l_function_name CONSTANT VARCHAR2(30) := 'Get_KIT_Status';
1167
1168 CURSOR l_kit_processed
1169 IS
1170 SELECT
1171 mvt_stat_status
1172 FROM
1173 wsh_delivery_details
1174 WHERE
1175 delivery_detail_id = p_delivery_detail_id;
1176 BEGIN
1177 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1178 THEN
1179 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1180 , G_MODULE_NAME || l_function_name || '.begin'
1181 ,'enter procedure'
1182 );
1183 END IF;
1184
1185 OPEN l_kit_processed;
1186 FETCH l_kit_processed INTO
1187 l_mvt_status;
1188 CLOSE l_kit_processed;
1189
1190 --if mvt status is in 'PROCESSED' or 'FORARVL' (for SO triangulation)
1191 --then a kit record has been created, set the status to 'Y'
1192 IF l_mvt_status IN ('PROCESSED', 'FORARVL')
1193 THEN
1194 l_kit_status := 'Y';
1195 ELSE
1196 l_kit_status := 'N';
1197 END IF;
1198
1199 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1200 THEN
1201 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1202 , G_MODULE_NAME || l_function_name || '.end'
1203 ,'exit procedure'
1204 );
1205 END IF;
1206
1207 RETURN (l_kit_status);
1208
1209 EXCEPTION
1210 WHEN NO_DATA_FOUND THEN
1211 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1212 THEN
1213 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1214 , G_MODULE_NAME || l_function_name||'. No data found exception'
1215 , 'Exception'
1216 );
1217 END IF;
1218 RETURN null;
1219 WHEN TOO_MANY_ROWS
1220 THEN
1221 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1222 THEN
1223 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1224 , G_MODULE_NAME || l_function_name||'. too many rows exception'
1225 , 'Exception'
1226 );
1227 END IF;
1228 RETURN null;
1229 WHEN OTHERS THEN
1230 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1231 THEN
1232 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1233 , G_MODULE_NAME || l_function_name||'. Others exception'
1234 , 'Exception'
1235 );
1236 END IF;
1237 RETURN null;
1238 END Get_KIT_Status;
1239
1240 --========================================================================
1241 -- FUNCTION : Get_KIT_Triangulation_Status
1242 -- PARAMETERS: p_delivery_detail_id
1243 -- COMMENT : Function that returns the status of a movement kit record
1244 -- if a movement record for kit has been created, the status
1245 -- returned is 'Y', otherwise return 'N'
1246 --=========================================================================
1247 FUNCTION Get_KIT_Triangulation_Status
1248 ( p_delivery_detail_id IN NUMBER
1249 )
1250 RETURN VARCHAR2
1251 IS
1252 l_kit_status VARCHAR2(1);
1253 l_mvt_status VARCHAR2(30);
1254 l_api_name CONSTANT VARCHAR2(30) := 'Get_KIT_Triangulation_Status';
1255
1256 CURSOR l_kit_processed
1257 IS
1258 SELECT
1259 mvt_stat_status
1260 FROM
1261 wsh_delivery_details
1262 WHERE
1263 delivery_detail_id = p_delivery_detail_id;
1264 BEGIN
1265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1266 THEN
1267 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1268 , G_MODULE_NAME || l_api_name || '.begin'
1269 ,'enter procedure'
1270 );
1271 END IF;
1272
1273 OPEN l_kit_processed;
1274 FETCH l_kit_processed INTO
1275 l_mvt_status;
1276 CLOSE l_kit_processed;
1277
1278 --if mvt status is in 'PROCESSED' or 'FORDISP' (for SO triangulation)
1279 --then a kit record has been created, set the status to 'Y'
1280 IF l_mvt_status IN ('PROCESSED', 'FORDISP')
1281 THEN
1282 l_kit_status := 'Y';
1283 ELSE
1284 l_kit_status := 'N';
1285 END IF;
1286
1287 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1288 THEN
1289 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1290 , G_MODULE_NAME || l_api_name || '.end'
1291 ,'exit procedure'
1292 );
1293 END IF;
1294
1295 RETURN (l_kit_status);
1296
1297 EXCEPTION
1298 WHEN NO_DATA_FOUND THEN
1299 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1300 THEN
1301 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1302 , G_MODULE_NAME || l_api_name||'. No data found exception'
1303 , 'Exception'
1304 );
1305 END IF;
1306 RETURN null;
1307 WHEN TOO_MANY_ROWS
1308 THEN
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_api_name||'. too many rows exception'
1313 , 'Exception'
1314 );
1315 END IF;
1316 RETURN null;
1317 WHEN OTHERS THEN
1318 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1319 THEN
1320 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1321 , G_MODULE_NAME || l_api_name||'. Others exception'
1322 , 'Exception'
1323 );
1324 END IF;
1325 RETURN null;
1326 END Get_KIT_Triangulation_Status;
1327
1328 END INV_MGD_MVT_SO_MDTR;