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