[Home] [Help]
PACKAGE BODY: APPS.MRP_EXPL_STD_MANDATORY
Source
1 PACKAGE BODY MRP_EXPL_STD_MANDATORY AS
2 /* $Header: MRPDSODB.pls 120.7 2007/12/07 11:54:34 rsyadav ship $ */
3
4 G_MRP_BACKWARD_PRF varchar2(1) := nvl(FND_PROFILE.VALUE('MRP_NEW_PLANNER_BACK_COMPATIBILITY'),'Y');
5 G_BOM_GREATER_THAN_EQUAL_J NUMBER := 1;
6
7 FUNCTION explode_line(pLineId in number,
8 p_wip_demand_exists in number,
9 pLRN in number,
10 pITEM_TYPE_CODE in varchar2,
11 xErrorMessage out NOCOPY varchar2,
12 xMessageName out NOCOPY varchar2,
13 xTableName out NOCOPY varchar2)
14 RETURN INTEGER IS
15 lStatus integer;
16 i number;
17
18 temp number := null;
19 temp1 date := null;
20 l_component_ship_date date;
21
22 p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
23 l_smc_table MRP_ATP_PUB.ATP_Rec_Typ;
24
25 lv_temp_sql_stmt VARCHAR2(2000);
26 lv_wip_demand_exists_flag NUMBER;
27
28 BEGIN
29 SELECT
30 oel.inventory_item_id,
31 oel.ship_from_org_id,
32 oel.org_id,
33 oel.line_id,
34 oel.header_id,
35 oel.ordered_quantity, /* Always explode using ordered quantity, use lv_wip_demand_exists to manipulate quantities*/
36 oel.order_quantity_uom,
37 nvl(oel.schedule_ship_date,oel.request_date),
38 oel.demand_class_code,
39 temp, -- calling module
40 temp, -- customer_id
41 temp, -- customer_site_id
42 temp, -- destination_time_zone
43 oel.schedule_arrival_date,
44 temp1, -- latest acceptable_date
45 oel.delivery_lead_time , -- delivery lead time
46 temp, -- Freight_Carrier
47 temp, -- Ship_Method
48 temp, --Ship_Set_Name
49 temp, -- Arrival_Set_Name
50 1, -- Override_Flag
51 temp, -- Action
52 temp1, -- Ship_date
53 temp, -- available_quantity
54 temp, -- requested_date_quantity
55 temp1, -- group_ship_date
56 temp1, -- group_arrival_date
57 temp, -- vendor_id
58 temp, -- vendor_site_id
59 temp, -- insert_flag
60 temp, -- error_code
61 temp -- Message
62 BULK COLLECT INTO
63 p_atp_table.Inventory_Item_Id ,
64 p_atp_table.Source_Organization_Id ,
65 p_atp_table.Organization_id ,
66 p_atp_table.Identifier ,
67 p_atp_table.Demand_Source_Header_Id ,
68 p_atp_table.Quantity_Ordered ,
69 p_atp_table.Quantity_UOM ,
70 p_atp_table.Requested_Ship_Date ,
71 p_atp_table.Demand_Class ,
72 p_atp_table.Calling_Module ,
73 p_atp_table.Customer_Id ,
74 p_atp_table.Customer_Site_Id ,
75 p_atp_table.Destination_Time_Zone ,
76 p_atp_table.Requested_Arrival_Date ,
77 p_atp_table.Latest_Acceptable_Date ,
78 p_atp_table.Delivery_Lead_Time ,
79 p_atp_table.Freight_Carrier ,
80 p_atp_table.Ship_Method ,
81 p_atp_table.Ship_Set_Name ,
82 p_atp_table.Arrival_Set_Name ,
83 p_atp_table.Override_Flag ,
84 p_atp_table.Action ,
85 p_atp_table.Ship_Date ,
86 p_atp_table.Available_Quantity ,
87 p_atp_table.Requested_Date_Quantity ,
88 p_atp_table.Group_Ship_Date ,
89 p_atp_table.Group_Arrival_Date ,
90 p_atp_table.Vendor_Id ,
91 p_atp_table.Vendor_Site_Id ,
92 p_atp_table.Insert_Flag ,
93 p_atp_table.Error_Code ,
94 p_atp_table.Message
95 FROM oe_order_lines_all oel
96 WHERE oel.line_id = pLineId;
97
98
99 /* Passing NULL for source_organization_id and inventory_item_id so that CTO
100 determines the BOMs, if any, for the Model and it's option Class(es) and
101 explodes them */
102
103 lstatus := CTO_CONFIG_ITEM_PK.Get_Mandatory_Components(
104 p_atp_table, --p_ship_set in MRP_ATP_PUB.ATP_Rec_Typ
105 NULL,
106 NULL,
107 l_smc_table, --p_sm_rec out MRP_ATP_PUB.ATP_Rec_Typ
108 xErrorMessage,
109 xMessageName,
110 xTableName );
111
112 IF lstatus = 0 THEN
113 return (0);
114 END IF;
115
116 lv_wip_demand_exists_flag := 0; --initialize it to 0 every time
117
118 IF l_smc_table.inventory_item_id.EXISTS(1) THEN
119 l_component_ship_date := MRP_CALENDAR.date_offset(
120 p_atp_table.Source_Organization_id(1),
121 1,/* Daily Bucket */
122 p_atp_table.Requested_Ship_Date(1),
123 -(NVL(l_smc_table.atp_lead_time(1),0)));
124
125
126 /* Bug 2550996 - Check if component demand exists for component in wip...if it
127 does we will set the primary qty to 0 in mrp_derived_so_demands -- Removed this code*/
128
129 /*---------------------------------------------------------------------------------------+
130 | If the job for the config Item exists, get that the value of wip_job_demand existsr |
131 | from the arguments passed |
132 | For Models lv_wip_demand_exists_flag will be always taken as 0 |
133 +---------------------------------------------------------------------------------------*/
134 IF (p_wip_demand_exists <> -1) THEN
135 lv_wip_demand_exists_flag := p_wip_demand_exists;
136 END IF;
137
138 FORALL i in l_smc_table.inventory_item_id.FIRST .. l_smc_table.inventory_item_id.LAST
139 INSERT INTO MRP_DERIVED_SO_DEMANDS
140 (INVENTORY_ITEM_ID,
141 ORGANIZATION_ID,
142 PRIMARY_UOM_QUANTITY,
143 RESERVATION_TYPE,
144 RESERVATION_QUANTITY,
145 DEMAND_SOURCE_TYPE,
146 DEMAND_HEADER_ID,
147 COMPLETED_QUANTITY,
148 SUBINVENTORY,
149 DEMAND_CLASS,
150 REQUIREMENT_DATE,
151 DEMAND_SOURCE_LINE,
152 DEMAND_SOURCE_DELIVERY,
153 PARENT_DEMAND_ID,
154 DEMAND_ID,
155 SALES_CONTACT,
156 REFRESH_NUMBER
157 )
158 VALUES(
159 l_smc_table.inventory_item_id(i),
160 p_atp_table.Source_Organization_Id(1),
161 DECODE(lv_wip_demand_exists_flag,0,
162 decode(pITEM_TYPE_CODE,'MODEL',
163 NVL(inv_decimals_pub.get_primary_quantity(
164 p_atp_table.Source_Organization_Id(1),
165 l_smc_table.inventory_item_id(i),
166 p_atp_table.Quantity_UOM(1),
167 l_smc_table.quantity_ordered(i)),
168 l_smc_table.quantity_ordered(i)),
169 decode(to_number(l_smc_table.attribute_01(i)),6,
170 decode(G_MRP_BACKWARD_PRF,'N',0,
171 NVL(inv_decimals_pub.get_primary_quantity(
172 p_atp_table.Source_Organization_Id(1),
173 l_smc_table.inventory_item_id(i),
174 p_atp_table.Quantity_UOM(1),
175 l_smc_table.quantity_ordered(i)),
176 l_smc_table.quantity_ordered(i))),
177 NVL(inv_decimals_pub.get_primary_quantity(
178 p_atp_table.Source_Organization_Id(1),
179 l_smc_table.inventory_item_id(i),
180 p_atp_table.Quantity_UOM(1),
181 l_smc_table.quantity_ordered(i)),
182 l_smc_table.quantity_ordered(i))
183 )),
184 0),
185 1, /*Reservation Type*/
186 0, /*Reservation Quantity*/
187 2, /*DEMAND_SOURCE_TYPE*/
188 p_atp_table.Demand_Source_Header_Id(1),
189 0, /*Completed Quantity*/
190 TO_CHAR(NULL), /*subinventory*/
191 p_atp_table.Demand_Class(1),
192 l_component_ship_date,
193 TO_CHAR(l_smc_table.identifier(i)),
194 TO_CHAR(NULL), /*demand source delivery*/
195 TO_NUMBER(NULL), /*parent demand id*/
196 l_smc_table.identifier(i), /*demand id */
197 to_char(null), /* Sales_rep */
198 pLRN /* Refresh number, -1 means complete refresh */
199 );
200 END IF;
201
202 COMMIT;
203 return(1);
204
205 exception
206 when others then
207 return (0);
208
209 END explode_line;
210
211
212 FUNCTION Explode_ATO_SM_COMPS(p_lrn IN NUMBER) RETURN INTEGER IS
213
214 lErrorMessage varchar2(100);
215 lMessageName varchar2(100);
216 lTableName varchar2(100);
217
218 pLineId number;
219 temp number;
220
221 p_get_oe_record GET_OE_Rec_Typ;
222
223 lv_offset_ship_date date;
224 lv_wip_demand_exists number;
225 lv_item_id number;
226 lv_organization_id number;
227 lv_wip_supply_type number := 0;
228 lv_lrn number;
229 lv_ato_item_shipped number;
230
231 pATOLineID number;
232 pItemCode varchar2(10);
233
234 lv_cursor_stmt VARCHAR2(5000);
235 lv_sql_stmt VARCHAR2(2000);
236 lv_sql_stmt1 VARCHAR2(2000);
237 lv_mrp_schema VARCHAR2(30);
238
239 TYPE CurTyp IS REF CURSOR;
240 Order_Line_Cur CurTyp;
241 lv_config number := 0;
242 CTO_BOM_NOT_FOUND EXCEPTION;
243
244 lv_bom_version NUMBER;
245 lv_bom_table VARCHAR2(30);
246 lv_comp_table VARCHAR2(30);
247
248 BEGIN
249
250 SELECT a.oracle_username
251 INTO lv_mrp_schema
252 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
253 WHERE a.oracle_id = b.oracle_id
254 and b.application_id= 704;
255
256 lv_sql_stmt:= 'select MRP_CL_FUNCTION.CHECK_BOM_VER from dual';
257
258 EXECUTE IMMEDIATE lv_sql_stmt
259 INTO lv_bom_version;
260
261 IF lv_bom_version = G_BOM_GREATER_THAN_EQUAL_J THEN
262 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '11510 or greater source');
263 lv_comp_table:='bom_components_b';
264 lv_bom_table:='bom_structures_b';
265 ELSE
266 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Pre 11510 source');
267 lv_comp_table:='bom_inventory_components';
268 lv_bom_table:='bom_bill_of_materials';
269 END IF;
270
271 BEGIN
272 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_mrp_schema||'.MRP_DERIVED_SO_DEMANDS';
273 EXECUTE IMMEDIATE lv_sql_stmt;
274
275 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Value of p_lrn is :'||p_lrn);
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, sqlerrm);
280 END;
281
282 IF (p_lrn <> -1) THEN --- net change collection of Sales orders
283 lv_cursor_stmt :=
284 ' SELECT /*+ index(oel oe_odr_lines_sn_n1) */ oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, '
285 ||' oel.INVENTORY_ITEM_ID, oel.organization_id '
286 ||' FROM MRP_SN_ODR_LINES oel '
287 ||' WHERE ato_line_id is not null'
288 ||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
289 ||' AND ( oel.rn > :p_lrn ) '
290 ||' UNION ALL'
291 ||' SELECT /*+ index(mr mtl_reservations_sn_n1) */ oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, '
292 ||' oel.INVENTORY_ITEM_ID, oel.organization_id '
293 ||' FROM MRP_SN_ODR_LINES oel, '
294 ||' MRP_SN_MTL_RESERVATIONS mr '
295 ||' WHERE ato_line_id is not null'
296 ||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
297 ||' AND mr.DEMAND_SOURCE_LINE_ID = oel.LINE_ID '
298 ||' AND ( mr.RN > :p_lrn AND oel.RN <= :p_lrn) '
299 ||' ORDER BY organization_id ';
300
301 OPEN Order_Line_Cur for lv_cursor_stmt USING p_lrn, p_lrn, p_lrn;
302
303 ELSE
304 ---Complete refresh of the Sales Orders
305 lv_cursor_stmt :=
306 ' SELECT UNIQUE oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, oel.INVENTORY_ITEM_ID, oel.organization_id '
307 ||' FROM MRP_SN_ODR_LINES oel '
308 ||' WHERE ato_line_id is not null'
309 ||' AND ordered_quantity > NVL(shipped_quantity,0) '
310 ||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
311 ||' ORDER BY oel.organization_id ';
312
313 OPEN Order_Line_Cur for lv_cursor_stmt;
314
315 END IF;
316
317 LOOP
318 FETCH Order_Line_Cur INTO
319 pLineId ,pATOLineId, pItemCode,lv_lrn,lv_item_id,lv_organization_id;
320 EXIT WHEN Order_Line_Cur%NOTFOUND;
321
322 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Value of p_lrn is :'||p_lrn);
323 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Value of lv_lrn is :'||lv_lrn);
324 IF (p_lrn = -1) THEN
325 lv_lrn := 0;
326 END IF;
327
328 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Value of lv_lrn is :'||lv_lrn);
329
330 lv_wip_demand_exists := 0;
331
332 BEGIN
333
334 lv_sql_stmt1 := ' SELECT 1 '
335 ||' FROM mtl_reservations '
336 ||' WHERE demand_source_line_id = :pLineId '
337 ||' AND SUPPLY_SOURCE_TYPE_ID in (5,13) '
338 ||' AND rownum = 1 ';
339
340 EXECUTE IMMEDIATE lv_sql_stmt1
341 INTO lv_wip_demand_exists
342 USING pLineId;
343
344 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' Job Exists for line id :'||pLineId );
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 NULL;
349 WHEN OTHERS THEN
350 NULL;
351 END;
352
353 IF lv_wip_demand_exists = 0 THEN
354
355 BEGIN
356
357 lv_sql_stmt1 := ' SELECT 1 '
358 ||' FROM WIP_FLOW_SCHEDULES '
359 ||' WHERE demand_source_line = to_char(:pLineId) '
360 ||' AND SCHEDULED_FLAG = 1 '
361 ||' AND (STATUS = 1 OR (STATUS = 2 AND QUANTITY_COMPLETED > 0)) '
362 ||' AND rownum = 1 ';
363
364 EXECUTE IMMEDIATE lv_sql_stmt1
365 INTO lv_wip_demand_exists
366 USING pLineId;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 NULL;
371 WHEN OTHERS THEN
372 NULL;
373 END;
374
375 END IF;
376
377
378 IF (pItemCode = 'CONFIG') THEN
379
380 SELECT
381 oel.inventory_item_id,
382 oel.ship_from_org_id,
383 oel.org_id,
384 oel.line_id,
385 oel.header_id,
386 decode(NVL(oel.shipped_quantity,0),0,oel.ordered_quantity,0),
387 oel.order_quantity_uom,
388 nvl(oel.schedule_ship_date,oel.request_date),
389 oel.demand_class_code,
390 nvl(oel.mfg_lead_time,0),
391 oel.ITEM_TYPE_CODE
392 BULK COLLECT INTO
393 p_get_oe_record.Inventory_Item_Id ,
394 p_get_oe_record.Source_Organization_Id ,
395 p_get_oe_record.Organization_id ,
396 p_get_oe_record.Identifier ,
397 p_get_oe_record.Demand_Source_Header_Id ,
398 p_get_oe_record.Quantity_Ordered ,
399 p_get_oe_record.Quantity_UOM ,
400 p_get_oe_record.Requested_Ship_Date ,
401 p_get_oe_record.Demand_Class ,
402 p_get_oe_record.mfg_lead_time,
403 p_get_oe_record.ITEM_TYPE_CODE
404 FROM oe_order_lines_all oel
405 WHERE oel.ato_line_id = pATOLineId
406 AND oel.line_id <> pLineId;
407
408 IF p_get_oe_record.inventory_item_id.EXISTS(1) THEN
409
410 BEGIN
411
412 FOR i in p_get_oe_record.inventory_item_id.FIRST..p_get_oe_record.inventory_item_id.LAST LOOP
413
414 IF p_get_oe_record.ITEM_TYPE_CODE(i) = 'OPTION' THEN
415
416 BEGIN
417
418 lv_sql_stmt1 := 'select NVL(bic.wip_supply_type, msi.wip_supply_type) '
419 ||' from ' || lv_comp_table || ' bic, ' || lv_bom_table || ' bbom '
420 ||', mtl_system_items msi'
421 ||' where bbom.assembly_item_id = :lv_item_id '
422 ||' and bbom.organization_id = :Source_Organization_Id '
423 ||' and bbom.alternate_bom_designator IS NULL '
424 ||' and bic.bill_sequence_id = bbom.common_bill_sequence_id '
425 ||' and bic.component_item_id = :Inventory_Item_Id '
426 ||' and msi.inventory_item_id = bic.component_item_id '
427 ||' and msi.organization_id = bbom.organization_id '
428 ||' and rownum = 1 ';
429
430 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql statement : ' || lv_sql_stmt1);
431
432 EXECUTE IMMEDIATE lv_sql_stmt1
433 INTO lv_wip_supply_type
434 USING lv_item_id,
435 p_get_oe_record.Source_Organization_Id(i), p_get_oe_record.Inventory_Item_Id(i);
436
437 EXCEPTION
438 WHEN NO_DATA_FOUND THEN
439
440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '========================================');
441 FND_MESSAGE.SET_NAME('MSC', 'MSC_CONFIG_BOM_ORG_ERR');
442 FND_MESSAGE.SET_TOKEN('ITEM_ID', to_char(lv_item_id));
443 FND_MESSAGE.SET_TOKEN('ORG_ID', to_char(p_get_oe_record.Source_Organization_Id(i)));
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, FND_MESSAGE.GET);
445 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '========================================');
446 RAISE CTO_BOM_NOT_FOUND;
447
448 WHEN OTHERS THEN
449 NULL;
450 END;
451
452 END IF;
453
454 lv_offset_ship_date := MRP_CALENDAR.date_offset(
455 p_get_oe_record.Source_Organization_id(1),
456 1,/* Daily Bucket */
457 p_get_oe_record.Requested_Ship_Date(1),
458 -(p_get_oe_record.mfg_lead_time(i) ) );
459
460 -- BUG 5211838
461 -- We no longer the derive the sales order demand for ATO Model and
462 -- Option Class on which a Configured Item is based.
463 -- This removes the double demand for ATO model/Option Class after
464 -- a configured item is created.
465 -- This helps an user doing ATP on ATO Model/ Option Class.
466 -- The user should ensure that he runs the source MPS plan before
467 -- data collection.
468
469 INSERT INTO MRP_DERIVED_SO_DEMANDS
470 (INVENTORY_ITEM_ID,
471 ORGANIZATION_ID,
472 PRIMARY_UOM_QUANTITY,
473 RESERVATION_TYPE,
474 RESERVATION_QUANTITY,
475 DEMAND_SOURCE_TYPE,
476 DEMAND_HEADER_ID,
477 COMPLETED_QUANTITY,
478 SUBINVENTORY,
479 DEMAND_CLASS,
480 REQUIREMENT_DATE,
481 DEMAND_SOURCE_LINE,
482 DEMAND_SOURCE_DELIVERY,
483 PARENT_DEMAND_ID,
484 DEMAND_ID,
485 SALES_CONTACT,
486 REFRESH_NUMBER
487 )
488 VALUES(
489 p_get_oe_record.inventory_item_id(i),
490 p_get_oe_record.Source_Organization_Id(i),
491 decode(lv_wip_demand_exists,0,
492 decode(p_get_oe_record.ITEM_TYPE_CODE(i),
493 'MODEL', 0,
494 'CLASS', 0,
495 decode(lv_wip_supply_type,
496 6, decode(G_MRP_BACKWARD_PRF,
497 'N',0,
498 NVL(inv_decimals_pub.get_primary_quantity(
499 p_get_oe_record.Source_Organization_Id(i),
500 p_get_oe_record.inventory_item_id(i),
501 p_get_oe_record.Quantity_UOM(i),
502 p_get_oe_record.quantity_ordered(i)),
503 p_get_oe_record.quantity_ordered(i))),
504 NVL(inv_decimals_pub.get_primary_quantity(
505 p_get_oe_record.Source_Organization_Id(i),
506 p_get_oe_record.inventory_item_id(i),
507 p_get_oe_record.Quantity_UOM(i),
508 p_get_oe_record.quantity_ordered(i)),
509 p_get_oe_record.quantity_ordered(i)))),
510 0),
511 1, /*Reservation Type*/
512 0, /*Reservation Quantity*/
513 2, /*DEMAND_SOURCE_TYPE*/
514 p_get_oe_record.Demand_Source_Header_Id(i),
515 0, /*Completed Quantity*/
516 TO_CHAR(NULL), /*subinventory*/
517 p_get_oe_record.Demand_Class(i),
518 lv_offset_ship_date,
519 TO_CHAR(p_get_oe_record.Identifier(i)),
520 TO_CHAR(NULL), /*demand source delivery*/
521 TO_NUMBER(NULL), /*parent demand id*/
522 p_get_oe_record.Identifier(i), /*demand id */
523 to_char(null), /* Sales_rep */
524 lv_lrn /* Refresh number, -1 means complete refresh */
525 );
526
527 lv_wip_supply_type := 0; /* re-setting value */
528
529 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Inserting row for :' || p_get_oe_record.Identifier(i) );
530
531 /* explode the model from the Config */
532 IF (p_get_oe_record.ITEM_TYPE_CODE(i) = 'MODEL') THEN
533
534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding the model of the config');
535 /* If the wip job of the Config has been created,
536 explode the model and bring all the SMC's under it as quantity = 0 (including phantom) .
537 Also if the S.O has been shipped get the SMC's with qty = 0 */
538 /* lv_wip_demand_exists controls the quantity in the explode_line function , if 1 then quantities go as 0*/
539 IF (p_get_oe_record.quantity_ordered(i) = 0 ) THEN
540 lv_wip_demand_exists := 1;
541 END IF;
542
543 temp:= explode_line(p_get_oe_record.Identifier(i),
544 lv_wip_demand_exists,
545 lv_lrn,
546 pItemCode,
547 lErrorMessage,
548 lMessageName,
549 lTableName);
550
551 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding the model for the config is completed.');
552 END IF;
553
554 END LOOP;
555
556 EXCEPTION
557 WHEN CTO_BOM_NOT_FOUND THEN
558 lv_config := 1;
559 WHEN OTHERS THEN
560 NULL;
561
562 END;
563
564 END IF;
565
566 ELSIF (pItemCode = 'STANDARD' ) THEN
567 /* Explode the ATO Item to get the SMC's under it*/
568 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding the ATO Item: Line_id = '||pLineId);
569
570 /* If the ATO item is shipped, get 1 into lv_ato_item_shipped,
571 else get the value of 0 */
572 SELECT decode(NVL(oel.shipped_quantity,0),0,0,1)
573 INTO lv_ato_item_shipped
574 FROM oe_order_lines_all oel
575 WHERE oel.line_id = pLineId;
576
577 /* If the ATO item is shipped then get the SMC's qty = 0 */
578 IF (lv_ato_item_shipped = 1) THEN
579 lv_wip_demand_exists := 1;
580 END IF;
581
582 temp:= explode_line(pLineId,
583 lv_wip_demand_exists,
584 lv_lrn,
585 pItemCode,
586 lErrorMessage,
587 lMessageName,
588 lTableName);
589
590 ELSIF (pItemCode = 'MODEL' ) THEN
591
592 lv_wip_demand_exists := -1;
593 /* Explode the Regular ATO Model to get the SMC's under it*/
594 temp:= explode_line(pLineId,
595 lv_wip_demand_exists,
596 lv_lrn,
597 pItemCode,
598 lErrorMessage,
599 lMessageName,
600 lTableName);
601
602 END IF;
603
604 END LOOP;
605
606 CLOSE Order_Line_Cur;
607
608 if lv_config <> 0 then
609 return (2);
610 else
611 return(0);
612 end if;
613
614 exception
615 when others then
616 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, SQLERRM);
617 return(1);
618
619 END Explode_ATO_SM_COMPS;
620
621 PROCEDURE LOG_ERROR(pBUFF IN VARCHAR2)
622 IS
623 BEGIN
624
625 -- add a line of text to the log file if MRP:Debug profile is set
626 IF (G_MRP_DEBUG = 'Y') THEN
627 FND_FILE.PUT_LINE(FND_FILE.LOG, pBUFF);
628 END IF;
629
630 END LOG_ERROR;
631
632
633 END MRP_EXPL_STD_MANDATORY;