[Home] [Help]
27: | Date operations make a disabled item effective and increases the
28: | component usage in configured item.
29: |
30: | sbhaskar 16-JUN-01 Bugfix 1835357
31: | Replaced fnd_file calls with oe_debug_pub
32: |
33: | ksarkar 19-JUL-01 Bugfix 1845141
34: | mtl_system_items_tl is not getting updated with correct description .
35: | Added message in lines 1486-87 and 1669-70 for better understanding of
385:
386: lStatus := check_bom (pConfigId, pOrgId,lItmBillId);
387: if lStatus = 1 then
388: IF PG_DEBUG <> 0 THEN
389: oe_debug_pub.add('create_bom_ml: ' || ' Config BOM ' || lItmBillId || ' Already exists ' ,1);
390: END IF;
391: return (1);
392: end if;
393:
412: v_ato_line_id = pLineId and pOrgId = CTO_UTILITY_PK.PC_BOM_CURRENT_ORG ) then
413:
414: lConfigBillId := CTO_UTILITY_PK.PC_BOM_BILL_SEQUENCE_ID ;
415:
416: oe_debug_pub.add('create_bom_ml: ' || 'Setting Bill ' || lConfigBillId || ' for org ' || pOrgId
417: || ' for line id ' || pLineId , 1);
418: else
419:
420: select bom_inventory_components_s.nextval
425:
426: xBillId := lConfigBillId;
427:
428: IF PG_DEBUG <> 0 THEN
429: oe_debug_pub.add('create_bom_ml: ' || 'Creating Bill ' || lConfigBillId, 1);
430: END IF;
431:
432:
433:
435: delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
436:
437:
438: IF PG_DEBUG <> 0 THEN
439: oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
440: END IF;
441:
442:
443:
463: */
464:
465:
466: IF PG_DEBUG <> 0 THEN
467: oe_debug_pub.add('create_bom_ml: ' || 'Line Id ' || pLineId ||' has Schedule Ship Date of '||g_SchShpDate, 2);
468: END IF;
469:
470: -- End Bugfix 1912376
471:
475: lStmtNum := 40;
476:
477: -- get oevalidation org
478: IF PG_DEBUG <> 0 THEN
479: oe_debug_pub.add('create_bom_ml: ' || 'Before getting validation org', 2);
480: END IF;
481:
482:
483: /* BUG #1957336 Change for preconfigure bom */
498: */
499:
500:
501: IF PG_DEBUG <> 0 THEN
502: oe_debug_pub.add('create_bom_ml: ' || 'Going to fetch Validation Org ' ,2);
503: END IF;
504:
505:
506: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
515: raise FND_API.G_EXC_ERROR;
516: end if;
517:
518: IF PG_DEBUG <> 0 THEN
519: oe_debug_pub.add('create_bom_ml: ' || 'Validation Org is :' || lOEValidationOrg,2);
520: END IF;
521:
522: lStmtNum := 41;
523:
528: from bom_cto_order_lines bcol
529: where bcol.line_id = lParentAtoLineId;
530:
531: IF PG_DEBUG <> 0 THEN
532: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
533: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
534: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
535: END IF;
536:
529: where bcol.line_id = lParentAtoLineId;
530:
531: IF PG_DEBUG <> 0 THEN
532: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
533: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
534: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
535: END IF;
536:
537: lStmtNum := 42;
530:
531: IF PG_DEBUG <> 0 THEN
532: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
533: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
534: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
535: END IF;
536:
537: lStmtNum := 42;
538: lStatus := get_model_lead_time(
543: lErrBuf);
544:
545: if (lStatus = 0) then
546: IF PG_DEBUG <> 0 THEN
547: oe_debug_pub.add('create_bom_ml: ' || 'Failed in get_model_lead_time. Error Buffer : '||lERrBuf, 1);
548: END IF;
549: raise FND_API.G_EXC_ERROR;
550: else
551: lTotLeadTime := lLeadTime + lTotLeadTime;
554: exit when lLineId = lParentAtoLineId; -- when we reach the top model
555: end loop;
556:
557: IF PG_DEBUG <> 0 THEN
558: oe_debug_pub.add('create_bom_ml: ' || 'Total lead time is: ' || to_char(lTotLeadTime), 1);
559: END IF;
560:
561: xTableName := 'OE_ORDER_LINES ';
562: lStmtNum := 43;
586: when no_data_found then
587: xErrorMessage := ' Error in calculating Estimated Release date ';
588: xMessageName := 'CTO_NO_CALENDAR';
589: IF PG_DEBUG <> 0 THEN
590: oe_debug_pub.add('create_bom_ml: ' || 'Error in stmt # ' || lStmtNum ||' : '|| xErrorMessage, 1);
591: END IF;
592: return(0);
593: end;
594: -- Bugfix 2374246
593: end;
594: -- Bugfix 2374246
595:
596: IF PG_DEBUG <> 0 THEN
597: oe_debug_pub.add ('create_bom_ml: ' || 'Estimated Release Date is : ' || lEstRelDate, 2);
598: END IF;
599: g_EstRelDate := lEstRelDate; -- 2222518
600: IF PG_DEBUG <> 0 THEN
601: oe_debug_pub.add ('create_bom_ml: ' || 'Global Estimated Release Date is : ' || g_EstRelDate, 2);
597: oe_debug_pub.add ('create_bom_ml: ' || 'Estimated Release Date is : ' || lEstRelDate, 2);
598: END IF;
599: g_EstRelDate := lEstRelDate; -- 2222518
600: IF PG_DEBUG <> 0 THEN
601: oe_debug_pub.add ('create_bom_ml: ' || 'Global Estimated Release Date is : ' || g_EstRelDate, 2);
602: END IF; -- 2222518
603:
604: -- b2307936
605: /*---------------------------------------------------------------------------+
617:
618: lOpseqProfile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
619:
620: IF PG_DEBUG <> 0 THEN
621: oe_debug_pub.add ('create_bom_ml: ' || 'Config_inherit_op_seq is ' || lOpseqProfile, 2);
622: END IF;
623:
624: lStmtNum := 80;
625: if lOpseqProfile = 1 then
623:
624: lStmtNum := 80;
625: if lOpseqProfile = 1 then
626: IF PG_DEBUG <> 0 THEN
627: oe_debug_pub.add('create_bom_ml: ' || 'Calling inherit_op_seq_ml with line id ' ||
628: to_char(pLineId) || ' in org ' ||
629: to_char(pOrgId), 1);
630: END IF;
631: lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
630: END IF;
631: lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
632: if lStatus <> 1 then
633: IF PG_DEBUG <> 0 THEN
634: oe_debug_pub.add('create_bom_ml: ' || 'Failed in inherit_op_seq for line id: '|| to_char(pLineId), 1);
635: END IF;
636: return(0);
637: end if;
638: else
863:
864:
865: lCnt := sql%rowcount ;
866: IF PG_DEBUG <> 0 THEN
867: oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
868: END IF;
869:
870:
871:
873: select count(*) into v_zero_qty_count from bom_inventory_comps_interface
874: where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
875:
876:
877: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
878:
879: if( v_zero_qty_count > 0 ) then
880:
881: oe_debug_pub.add( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
877: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
878:
879: if( v_zero_qty_count > 0 ) then
880:
881: oe_debug_pub.add( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
882:
883: select concatenated_segments into v_model_item_name
884: from mtl_system_items_kfv
885: where inventory_item_id = pModelId
922:
923: test_var := sql%rowcount;
924:
925: IF PG_DEBUG <> 0 THEN
926: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to future date = '||test_var,1);
927: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
928: END IF;
929: --Bug 7418622.FP for 7154767
930:
923: test_var := sql%rowcount;
924:
925: IF PG_DEBUG <> 0 THEN
926: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to future date = '||test_var,1);
927: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
928: END IF;
929: --Bug 7418622.FP for 7154767
930:
931:
1123:
1124: lCnt := sql%rowcount ;
1125:
1126: IF PG_DEBUG <> 0 THEN
1127: oe_debug_pub.add ('create_bom_ml: ' || 'Second -- Inserted ' || lCnt ||' rows',1);
1128: END IF;
1129:
1130: end if; /* end of check lOpseqProfile = 1 */
1131:
1131:
1132:
1133: /* begin Extend Effectivity Dates for Option Items with disable date */
1134:
1135: oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1136:
1137: For debug_rec in debug_cur
1138: Loop
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1135: oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1136:
1137: For debug_rec in debug_cur
1138: Loop
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1140: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1141: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1142: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1143: oe_debug_pub.add('==================================',1);
1136:
1137: For debug_rec in debug_cur
1138: Loop
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1140: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1141: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1142: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1143: oe_debug_pub.add('==================================',1);
1144: End Loop;
1137: For debug_rec in debug_cur
1138: Loop
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1140: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1141: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1142: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1143: oe_debug_pub.add('==================================',1);
1144: End Loop;
1145:
1138: Loop
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1140: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1141: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1142: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1143: oe_debug_pub.add('==================================',1);
1144: End Loop;
1145:
1146: -- Modified by Renga Kannan on 01/10/06
1139: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1140: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1141: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1142: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1143: oe_debug_pub.add('==================================',1);
1144: End Loop;
1145:
1146: -- Modified by Renga Kannan on 01/10/06
1147: -- The logic to find the last window for option item and mandatory comps
1166: and bill_sequence_id = lConfigBillId
1167: and disable_date <> g_futuredate ;
1168:
1169: If PG_DEBUG <> 0 Then
1170: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1171: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1172: End if;*/
1173:
1174:
1167: and disable_date <> g_futuredate ;
1168:
1169: If PG_DEBUG <> 0 Then
1170: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1171: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1172: End if;*/
1173:
1174:
1175:
1191: exception
1192: when others then
1193: v_overlap_check := 0 ;
1194: end;
1195: oe_debug_pub.add(' Overlap check = '||v_overlap_check,1);
1196:
1197: if(v_overlap_check = 1) then
1198:
1199: for debug_cur2 in debug_cur1
1197: if(v_overlap_check = 1) then
1198:
1199: for debug_cur2 in debug_cur1
1200: Loop
1201: oe_debug_pub.add(debug_cur2.component_item_id||'-'||debug_cur2.component_sequence_id||'-'||
1202: debug_cur2.operation_seq_num||'-'||to_char(debug_cur2.effectivity_date)
1203: ||'-'||to_char(debug_cur2.disable_date),1);
1204:
1205: end loop;
1225: exception
1226: when others then
1227: null ;
1228: end ;
1229: oe_debug_pub.add('Over lap record count = '||v_t_overlap_src_op_seq_num.count,1);
1230:
1231: if( v_t_overlap_src_op_seq_num.count > 0 ) then
1232: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1233: loop
1231: if( v_t_overlap_src_op_seq_num.count > 0 ) then
1232: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1233: loop
1234: IF PG_DEBUG <> 0 THEN
1235: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1236: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1237: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1238:
1239: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1232: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1233: loop
1234: IF PG_DEBUG <> 0 THEN
1235: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1236: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1237: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1238:
1239: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1240: ' ' || v_t_overlap_src_op_seq_num(i) ||
1235: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1236: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1237: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1238:
1239: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1240: ' ' || v_t_overlap_src_op_seq_num(i) ||
1241: ' ' || v_t_overlap_src_eff_date(i) ||
1242: ' ' || v_t_overlap_src_disable_date(i) ||
1243: ' OVERLAPS ' ||
1287:
1288: lcreate_item := nvl(FND_PROFILE.VALUE('CTO_CONFIG_EXCEPTION'), 1);
1289:
1290: IF PG_DEBUG <> 0 THEN
1291: oe_debug_pub.add ('Config exception profile '||lcreate_item, 1);
1292: END IF;
1293:
1294: open missed_lines(pLineId, lConfigBillId, lEstRelDate ); /* Effectivity dates change */
1295: loop
1303:
1304: BEGIN
1305:
1306: IF PG_DEBUG <> 0 THEN
1307: oe_debug_pub.add('Select missed component details.. ' ,1);
1308: END IF;
1309:
1310:
1311:
1310:
1311:
1312: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1313: IF PG_DEBUG <> 0 THEN
1314: oe_debug_pub.add('Pre configured Item .. ' ,1);
1315: END IF;
1316:
1317: select substrb(msi.concatenated_segments,1,50),
1318: 'Not Available' ,
1324: and bcol.line_id = v_missed_line_id;
1325:
1326: else
1327: IF PG_DEBUG <> 0 THEN
1328: oe_debug_pub.add('Auto configured Item .. ' ,1);
1329: END IF;
1330:
1331: select substrb(msi.concatenated_segments,1,50),
1332: to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'
1346:
1347: lStmtNum := 53;
1348:
1349: IF PG_DEBUG <> 0 THEN
1350: oe_debug_pub.add('Select model.. ' ,1);
1351: END IF;
1352:
1353: select substrb(concatenated_segments,1,50)
1354: into v_model
1358:
1359: lStmtNum := 54;
1360:
1361: IF PG_DEBUG <> 0 THEN
1362: oe_debug_pub.add('Select Org.. ' ,1);
1363: END IF;
1364:
1365: select organization_code
1366: into lOrg_code
1388:
1389: if ( lcreate_item = 1 ) then
1390:
1391: IF PG_DEBUG <> 0 THEN
1392: oe_debug_pub.add ('Warning: The component '||v_missed_item
1393: || ' on Line Number '||v_missed_line_number
1394: || ' in organization ' || lOrg_code
1395: || ' was not included in the configured item''s bill. ',1);
1396: oe_debug_pub.add ('Model Name : '||v_model,1);
1392: oe_debug_pub.add ('Warning: The component '||v_missed_item
1393: || ' on Line Number '||v_missed_line_number
1394: || ' in organization ' || lOrg_code
1395: || ' was not included in the configured item''s bill. ',1);
1396: oe_debug_pub.add ('Model Name : '||v_model,1);
1397: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1398:
1399:
1400:
1393: || ' on Line Number '||v_missed_line_number
1394: || ' in organization ' || lOrg_code
1395: || ' was not included in the configured item''s bill. ',1);
1396: oe_debug_pub.add ('Model Name : '||v_model,1);
1397: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1398:
1399:
1400:
1401:
1420:
1421:
1422: else
1423: IF PG_DEBUG <> 0 THEN
1424: oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
1425: || ' on Line Number '||v_missed_line_number
1426: || ' in organization ' || lOrg_code
1427: || ' could not be included in the configured item''s bill. ',1);
1428: oe_debug_pub.add ('Model Name : '||v_model,1);
1424: oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
1425: || ' on Line Number '||v_missed_line_number
1426: || ' in organization ' || lOrg_code
1427: || ' could not be included in the configured item''s bill. ',1);
1428: oe_debug_pub.add ('Model Name : '||v_model,1);
1429: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1430: END IF;
1431:
1432: /*
1425: || ' on Line Number '||v_missed_line_number
1426: || ' in organization ' || lOrg_code
1427: || ' could not be included in the configured item''s bill. ',1);
1428: oe_debug_pub.add ('Model Name : '||v_model,1);
1429: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1430: END IF;
1431:
1432: /*
1433: l_token(1).token_name := 'OPTION_NAME';
1449: EXCEPTION -- exception for stmt 52 ,53 and 54
1450:
1451: when others then
1452: IF PG_DEBUG <> 0 THEN
1453: oe_debug_pub.add('Others excepn from stmt '||lStmtNum ||':'||sqlerrm);
1454: END IF;
1455: raise fnd_api.g_exc_error;
1456: END ;
1457: end loop;
1462: should end with warning */
1463:
1464: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1465: IF PG_DEBUG <> 0 THEN
1466: oe_debug_pub.add ('Will not go through Hold Logic and Notification as Preconfigured Bom' , 1 );
1467: END IF;
1468:
1469: if missed_lines%ROWCOUNT > 0 then
1470: if ( lcreate_item = 1 ) then
1468:
1469: if missed_lines%ROWCOUNT > 0 then
1470: if ( lcreate_item = 1 ) then
1471: IF PG_DEBUG <> 0 THEN
1472: oe_debug_pub.add ('Create Item profile set to Create and Link Item ' , 1 );
1473: END IF;
1474:
1475: xMessageName := 'CTO_DROP_ITEM_FROM_CONFIG';
1476:
1512: -- g_t_dropped_item_type(v_table_count).MFG_REL_DATE := to_char( lEstRelDate , 'DD-MON-YYYY' ) ;
1513: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1514:
1515: IF PG_DEBUG <> 0 THEN
1516: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1517: END IF;
1518:
1519: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1520:
1519: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1520:
1521: else
1522: IF PG_DEBUG <> 0 THEN
1523: oe_debug_pub.add ('Create Item profile set to Do Not Create Item ' , 1 );
1524: END IF;
1525:
1526: xMessageName := 'CTO_DO_NOT_CREATE_ITEM';
1527:
1561: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1562: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1563:
1564: IF PG_DEBUG <> 0 THEN
1565: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1566: END IF;
1567:
1568: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1569:
1592: -- bugfix 2840801 :
1593: -- Set the global variable gApplyHold to apply hold on config line.
1594:
1595: IF PG_DEBUG <> 0 THEN
1596: oe_debug_pub.add ('Setting the global var gApplyHold to Y');
1597: END IF;
1598:
1599: CTO_CONFIG_BOM_PK.gApplyHold := 'Y';
1600:
1630: where line_id = pLineId ;
1631:
1632: end if;
1633:
1634: oe_debug_pub.add( ' DROPPED ITEM INFO: ' ||
1635: ' Problem Model ' || v_problem_model ||
1636: ' Problem CONFIG ' || v_problem_config ||
1637: ' ERROR ORG ' || v_error_org ||
1638: ' PROBLEM MODEL LINE NUM ' || v_problem_model_line_num
1654: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1655: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1656:
1657: IF PG_DEBUG <> 0 THEN
1658: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1659: END IF;
1660:
1661: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char(fnd_global.conc_request_id) ;
1662:
1665:
1666: /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1667:
1668: IF PG_DEBUG <> 0 THEN
1669: oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1670: END IF;
1671:
1672: v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_CREATED
1673: ,p_inventory_item_id => pModelId
1678:
1679:
1680: if( v_recipient is not null ) then
1681: IF PG_DEBUG <> 0 THEN
1682: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1683: END IF;
1684:
1685: g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1686:
1688:
1689:
1690:
1691: IF PG_DEBUG <> 0 THEN
1692: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1693: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1694: END IF;
1695:
1696: BEGIN
1689:
1690:
1691: IF PG_DEBUG <> 0 THEN
1692: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1693: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1694: END IF;
1695:
1696: BEGIN
1697: -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1708: and p.planner_code = item.planner_code
1709: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1710:
1711:
1712: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1713:
1714:
1715: EXCEPTION
1716: WHEN OTHERS THEN
1714:
1715: EXCEPTION
1716: WHEN OTHERS THEN
1717: IF PG_DEBUG <> 0 THEN
1718: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1719:
1720: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1721:
1722:
1716: WHEN OTHERS THEN
1717: IF PG_DEBUG <> 0 THEN
1718: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1719:
1720: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1721:
1722:
1723: END IF;
1724: END;
1747:
1748: else
1749:
1750: IF PG_DEBUG <> 0 THEN
1751: oe_debug_pub.add ('Not creating Item...');
1752: END IF;
1753:
1754:
1755:
1793: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1794: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1795:
1796: IF PG_DEBUG <> 0 THEN
1797: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1798: END IF;
1799:
1800: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1801:
1804: /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1805:
1806:
1807: IF PG_DEBUG <> 0 THEN
1808: oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1809: END IF;
1810:
1811: v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_NOT_CREATED
1812: ,p_inventory_item_id => pModelId
1817:
1818:
1819: if( v_recipient is not null ) then
1820: IF PG_DEBUG <> 0 THEN
1821: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1822: END IF;
1823:
1824: g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1825:
1829:
1830:
1831:
1832: IF PG_DEBUG <> 0 THEN
1833: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1834: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1835: END IF;
1836:
1837: BEGIN
1830:
1831:
1832: IF PG_DEBUG <> 0 THEN
1833: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1834: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1835: END IF;
1836:
1837: BEGIN
1838: -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1849: and p.planner_code = item.planner_code
1850: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1851:
1852:
1853: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1854:
1855:
1856: EXCEPTION
1857: WHEN OTHERS THEN
1855:
1856: EXCEPTION
1857: WHEN OTHERS THEN
1858: IF PG_DEBUG <> 0 THEN
1859: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1860:
1861: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1862:
1863:
1857: WHEN OTHERS THEN
1858: IF PG_DEBUG <> 0 THEN
1859: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1860:
1861: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1862:
1863:
1864: END IF;
1865: END;
1875: -- rkaza. bug 4315973. 08/24/2005.
1876: -- Hold ato line for dropped items when profile is set to do not
1877: -- create item. Removed aps_version restriction.
1878:
1879: oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1880: oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1881:
1882: cto_utility_pk.apply_create_config_hold( v_ato_line_id, v_header_id, l_return_status, l_msg_count, l_msg_data ) ;
1883:
1876: -- Hold ato line for dropped items when profile is set to do not
1877: -- create item. Removed aps_version restriction.
1878:
1879: oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1880: oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1881:
1882: cto_utility_pk.apply_create_config_hold( v_ato_line_id, v_header_id, l_return_status, l_msg_count, l_msg_data ) ;
1883:
1884:
1914: EXCEPTION -- exception for stmt 51 and 55
1915:
1916: when others then
1917: IF PG_DEBUG <> 0 THEN
1918: oe_debug_pub.add ('Failed in stmt ' || lStmtNum || ' with error: '||sqlerrm);
1919: END IF;
1920: raise fnd_api.g_exc_error;
1921: END ;
1922:
2068:
2069: lCnt := sql%rowcount ;
2070:
2071: IF PG_DEBUG <> 0 THEN
2072: oe_debug_pub.add ('create_bom_ml: ' || 'Third -- Inserted ' || lCnt ||' rows',1);
2073: END IF;
2074:
2075: xBillId := lConfigBillId;
2076:
2086: xErrorMessage := 'CTOCBOMB:create_bom_ml failed with expected error in stmt '||to_char(lStmtNum);
2087: --xMessageName := 'CTO_CREATE_BOM_ERROR';
2088:
2089: IF PG_DEBUG <> 0 THEN
2090: oe_debug_pub.add ('create_bom_ml: ' || 'create_item::exp error::'||to_char(lStmtNum)||sqlerrm,1);
2091: END IF;
2092:
2093:
2094:
2097:
2098: --Bugfix 11056452
2099: lCnt := sql%rowcount;
2100: IF PG_DEBUG <> 0 THEN
2101: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
2102: END IF;
2103:
2104: delete from bom_bill_of_mtls_interface
2105: where bill_sequence_id = xBillId;
2105: where bill_sequence_id = xBillId;
2106:
2107: lCnt := sql%rowcount;
2108: IF PG_DEBUG <> 0 THEN
2109: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
2110: END IF;
2111:
2112: xBillId := null ;
2113:
2111:
2112: xBillId := null ;
2113:
2114: IF PG_DEBUG <> 0 THEN
2115: oe_debug_pub.add ('create_bom_ml: ' || 'deleted records from bici ::'||to_char(sql%rowcount) ,1);
2116: END IF;
2117:
2118: return(0);
2119:
2120: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2121: xErrorMessage := 'CTOCBOMB:create_bom_ml failed with unexpected error in stmt '||to_char(lStmtNum);
2122: xMessageName := 'CTO_CREATE_BOM_ERROR';
2123: IF PG_DEBUG <> 0 THEN
2124: oe_debug_pub.add ('create_bom_ml: ' || 'create_item::unexp error::'||to_char(lStmtNum)||sqlerrm,1);
2125: END IF;
2126: return(-1);
2127:
2128:
2129: WHEN OTHERS THEN
2130: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNum)||':'||substrb(sqlerrm,1,150);
2131: xMessageName := 'CTO_CREATE_BOM_ERROR';
2132: IF PG_DEBUG <> 0 THEN
2133: oe_debug_pub.add('create_bom_ml: ' || 'Error: Others excpn in create_bom_ml: ' || sqlerrm);
2134: END IF;
2135: return(-1);
2136:
2137: END create_bom_ml;
2321:
2322: if model_comp_seq_id_arr.count > 0 then
2323: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
2324: loop
2325: oe_debug_pub.add( ' Start Looping ',1);
2326: IF PG_DEBUG <> 0 THEN
2327: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
2328: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
2329: ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
2323: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
2324: loop
2325: oe_debug_pub.add( ' Start Looping ',1);
2326: IF PG_DEBUG <> 0 THEN
2327: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
2328: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
2329: ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
2330:
2331: END IF;
2345: loop
2346:
2347: -- Get all eff and disable dates in asc order
2348: -- 4244576
2349: oe_debug_pub.add( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
2350:
2351: select distinct effectivity_date
2352: BULK COLLECT INTO asc_date_arr
2353: from bom_inventory_comps_interface
2367: if asc_date_arr.count > 0 then
2368: for x1 in asc_date_arr.FIRST..asc_date_arr.LAST
2369: loop
2370: IF PG_DEBUG <> 0 THEN
2371: oe_debug_pub.add ('Date ('||x1||') = '||to_char(asc_date_arr(x1),'DD-MON-YY HH24:MI:SS'),1);
2372: END IF;
2373: end loop;
2374: end if;
2375:
2389: if club_tab_arr.count > 0 then
2390: for x3 in club_tab_arr.FIRST..club_tab_arr.LAST
2391: loop
2392: IF PG_DEBUG <> 0 THEN
2393: oe_debug_pub.add ('ED ('||x3||') = ' ||to_char(club_tab_arr(x3).eff_dt,'DD-MON-YY HH24:MI:SS')||
2394: ' ---- DD ('||x3||') = '|| to_char(club_tab_arr(x3).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2395: END IF;
2396: end loop;
2397: end if;
2429: if club_tab_arr.count > 0 then
2430: for x22 in club_tab_arr.FIRST..club_tab_arr.LAST
2431: loop
2432: IF PG_DEBUG <> 0 THEN
2433: oe_debug_pub.add ('ED ('||x22||') = ' ||to_char(club_tab_arr(x22).eff_dt,'DD-MON-YY HH24:MI:SS')||
2434: ' ---- DD ('||x22||') = '|| to_char(club_tab_arr(x22).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2435: END IF;
2436: end loop;
2437: end if;
2443: dbg_dis_date := d1.dis_date;
2444: dbg_qty := d1.cmp_qty;
2445:
2446: IF PG_DEBUG <> 0 THEN
2447: oe_debug_pub.add( 'ED '||to_char(dbg_eff_date,'DD-MON-YY HH24:MI:SS')||' DD '||to_char(dbg_dis_date,'DD-MON-YY HH24:MI:SS')||' Qty '||dbg_qty||' Basis Type = '||d1.basis_type);
2448: END IF;
2449:
2450: end loop;
2451:
2460:
2461:
2462:
2463: IF PG_DEBUG <> 0 THEN
2464: oe_debug_pub.add ('checking for club comp error ', 1 ) ;
2465: END IF;
2466:
2467:
2468:
2511: end loop;
2512:
2513:
2514: IF PG_DEBUG <> 0 THEN
2515: oe_debug_pub.add( 'Going to Raise CTO_CLUB_COMP_ERROR');
2516: oe_debug_pub.add( 'will not populated message CTO_CLUB_COMP_ERROR');
2517: END IF;
2518:
2519: select segment1 into
2512:
2513:
2514: IF PG_DEBUG <> 0 THEN
2515: oe_debug_pub.add( 'Going to Raise CTO_CLUB_COMP_ERROR');
2516: oe_debug_pub.add( 'will not populated message CTO_CLUB_COMP_ERROR');
2517: END IF;
2518:
2519: select segment1 into
2520: l_model_name
2575: if club_tab_arr.count > 0 then
2576: for x5 in club_tab_arr.FIRST..club_tab_arr.LAST
2577: loop
2578: IF PG_DEBUG <> 0 THEN
2579: oe_debug_pub.add ('ED (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).eff_dt,'DD-MON-YY HH24:MI:SS')||
2580: ' -- DD (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).dis_dt,'DD-MON-YY HH24:MI:SS')||
2581: ' -- Qty (' ||x5|| ') = ' ||club_tab_arr(x5).qty,1);
2582: END IF;
2583: end loop;
2790: -- Get item sequence increment
2791: --
2792: p_seq_increment := fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT');
2793: IF PG_DEBUG <> 0 THEN
2794: oe_debug_pub.add('create_bom_data_ml: ' || 'Item Seq Increment::'||to_char(p_seq_increment), 1);
2795: END IF;
2796:
2797: --
2798: -- update item_num of top model
2799: --
2800: p_item_num := p_item_num + p_seq_increment;
2801:
2802: IF PG_DEBUG <> 0 THEN
2803: oe_debug_pub.add('create_bom_data_ml: ' || 'p_item_num::'||to_char(p_item_num), 2);
2804: END IF;
2805:
2806: update bom_inventory_comps_interface
2807: set item_num = p_item_num
2807: set item_num = p_item_num
2808: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
2809:
2810: IF PG_DEBUG <> 0 THEN
2811: oe_debug_pub.add('create_bom_data_ml: ' || 'Updated model row::'||sql%rowcount, 2);
2812: END IF;
2813:
2814: p_item_num := p_item_num + p_seq_increment;
2815:
2812: END IF;
2813:
2814: p_item_num := p_item_num + p_seq_increment;
2815:
2816: oe_debug_pub.add('create_bom_data_ml: ' || 'config bill id ::'|| pConfigBillId , 2);
2817:
2818:
2819: --
2820: -- get bill_sequence_id of top model
2828: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
2829: and organization_id = pOrgId
2830: and alternate_bom_designator is null;
2831:
2832: oe_debug_pub.add('create_bom_data_ml: ' || 'common bill seq id ::'|| p_bill_seq_id , 2);
2833: --
2834: -- call update_item_num procedure with top model
2835: -- this will update item_num for the rest of the items
2836: --
2834: -- call update_item_num procedure with top model
2835: -- this will update item_num for the rest of the items
2836: --
2837: IF PG_DEBUG <> 0 THEN
2838: oe_debug_pub.add('create_bom_data_ml: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
2839: END IF;
2840:
2841: update_item_num(
2842: p_bill_seq_id,
2854:
2855: exception
2856: when others then
2857:
2858: oe_debug_pub.add('create_bom_data_ml: ' || SQLERRM ,2);
2859: oe_debug_pub.add('create_bom_data_ml: ' || SQLCODE ,2);
2860:
2861: end ;
2862:
2855: exception
2856: when others then
2857:
2858: oe_debug_pub.add('create_bom_data_ml: ' || SQLERRM ,2);
2859: oe_debug_pub.add('create_bom_data_ml: ' || SQLCODE ,2);
2860:
2861: end ;
2862:
2863:
2861: end ;
2862:
2863:
2864:
2865: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2866: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2867: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2868: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2869: /*-------------------------------------------+
2862:
2863:
2864:
2865: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2866: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2867: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2868: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2869: /*-------------------------------------------+
2870:
2863:
2864:
2865: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2866: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2867: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2868: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2869: /*-------------------------------------------+
2870:
2871: Load BOM_bill_of_materials
2864:
2865: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2866: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2867: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2868: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2869: /*-------------------------------------------+
2870:
2871: Load BOM_bill_of_materials
2872: +-------------------------------------------*/
2870:
2871: Load BOM_bill_of_materials
2872: +-------------------------------------------*/
2873: IF PG_DEBUG <> 0 THEN
2874: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2875: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2876: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2877: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2878: END IF;
2871: Load BOM_bill_of_materials
2872: +-------------------------------------------*/
2873: IF PG_DEBUG <> 0 THEN
2874: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2875: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2876: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2877: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2878: END IF;
2879:
2872: +-------------------------------------------*/
2873: IF PG_DEBUG <> 0 THEN
2874: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2875: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2876: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2877: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2878: END IF;
2879:
2880:
2873: IF PG_DEBUG <> 0 THEN
2874: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2875: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2876: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2877: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2878: END IF;
2879:
2880:
2881:
2890:
2891: exception
2892: when others then
2893: IF PG_DEBUG <> 0 THEN
2894: oe_debug_pub.add('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
2895: oe_debug_pub.add('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
2896: g_structure_type_id := 1;
2897:
2898: END IF;
2891: exception
2892: when others then
2893: IF PG_DEBUG <> 0 THEN
2894: oe_debug_pub.add('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
2895: oe_debug_pub.add('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
2896: g_structure_type_id := 1;
2897:
2898: END IF;
2899:
2901:
2902:
2903:
2904: IF PG_DEBUG <> 0 THEN
2905: oe_debug_pub.add('create_bom_data_ml: ' || 'structure_type_id is ' || g_structure_type_id ,2);
2906: END IF;
2907:
2908: end if ;
2909:
3017: and b.organization_id = pOrgId
3018: and b.alternate_bom_designator is NULL;
3019:
3020: IF PG_DEBUG <> 0 THEN
3021: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1 );
3022: END IF;
3023:
3024: /*-----------------------------------------------+
3025: Load Bom_inventory_components
3024: /*-----------------------------------------------+
3025: Load Bom_inventory_components
3026: +----------------------------------------------*/
3027: IF PG_DEBUG <> 0 THEN
3028: oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
3029: END IF;
3030: lStmtNum := 310;
3031: xTableName := 'BOM_INVENTORY_COMPONENTS';
3032: insert into BOM_INVENTORY_COMPONENTS
3180: and msi.organization_id = pOrgId ;
3181:
3182:
3183: IF PG_DEBUG <> 0 THEN
3184: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3185: END IF;
3186:
3187:
3188:
3189: /*-----------------------------------------------+
3190: Populate Substitutes for Mandatory components
3191: +----------------------------------------------*/
3192: IF PG_DEBUG <> 0 THEN
3193: oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
3194: END IF;
3195: lStmtNum := 315;
3196: xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
3197:
3288:
3289:
3290:
3291: IF PG_DEBUG <> 0 THEN
3292: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3293: END IF;
3294:
3295:
3296:
3309: /* -------------------------------------------------------------------------+
3310: Insert into BOM_REFERENCE_DESIGNATORS table
3311: +--------------------------------------------------------------------------*/
3312: IF PG_DEBUG <> 0 THEN
3313: oe_debug_pub.add('create_bom_data_ml: ' || 'Before third insert into bom_reference_designators. ', 2);
3314: END IF;
3315: lStmtNum := 320;
3316: xTableName := 'BOM_REFERENCE_DESIGNATORS';
3317: insert into BOM_REFERENCE_DESIGNATORS
3396: and r.component_sequence_id = abs(ic.model_comp_seq_id) -- previously last_update_login
3397: and nvl(r.acd_type,0) <> 3;
3398:
3399: IF PG_DEBUG <> 0 THEN
3400: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3401: END IF;
3402:
3403:
3404:
3410: prev_comp_item_id := 0;
3411: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
3412: loop
3413: IF PG_DEBUG <> 0 THEN
3414: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
3415: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1),1);
3416: END IF;
3417:
3418:
3446:
3447: -- insert into BOM_REFERENCE_DESIGNATORS for the corresponding model_comp_seq_id
3448: -- if it has not already been inserted.
3449: IF PG_DEBUG <> 0 THEN
3450: oe_debug_pub.add ('club_component_sequence_id is '||club_component_sequence_id, 1);
3451: END if;
3452: IF PG_DEBUG <> 0 THEN
3453: oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
3454: END if;
3449: IF PG_DEBUG <> 0 THEN
3450: oe_debug_pub.add ('club_component_sequence_id is '||club_component_sequence_id, 1);
3451: END if;
3452: IF PG_DEBUG <> 0 THEN
3453: oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
3454: END if;
3455: begin
3456: insert into BOM_REFERENCE_DESIGNATORS
3457: (
3532: and nvl(r.acd_type,0) <> 3;
3533: exception
3534: when others then
3535: IF PG_DEBUG <> 0 THEN
3536: oe_debug_pub.add ('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
3537: END IF;
3538: end;
3539: IF PG_DEBUG <> 0 THEN
3540: oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
3536: oe_debug_pub.add ('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
3537: END IF;
3538: end;
3539: IF PG_DEBUG <> 0 THEN
3540: oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
3541: END if;
3542: end loop; -- 3985173 : end of club_comp_seq cursor loop
3543: prev_comp_item_id := component_item_id_arr(x1); -- 3985173
3544:
3551: end loop;
3552: end if;
3553:
3554: IF PG_DEBUG <> 0 THEN
3555: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3556: END IF;
3557: -- end 3674833
3558:
3559:
3571: if CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'C' then
3572: -- Call Custom API with details..
3573:
3574: IF PG_DEBUG <> 0 THEN
3575: oe_debug_pub.add ('Prepare data for calling custom hook...');
3576: END IF;
3577:
3578: DECLARE
3579: cursor ctg is
3595: loop
3596: l_catalog_dtls(i).cat_element_name := rec.element_name;
3597: l_catalog_dtls(i).cat_element_value := NULL;
3598: IF PG_DEBUG <> 0 THEN
3599: oe_debug_pub.add ('l_catalog_dtls('||i||').cat_element_name = '||
3600: rec.element_name);
3601: END IF;
3602: i := i+1;
3603: end loop;
3609: l_params.p_item_id := pConfigId;
3610: l_params.p_org_id := pOrgId;
3611:
3612: IF PG_DEBUG <> 0 THEN
3613: oe_debug_pub.add ('Parameter passed: l_params.p_item_id = '||l_params.p_item_id ||
3614: '; l_params.p_org_id = '||l_params.p_org_id );
3615: END IF;
3616:
3617: CTO_CUSTOM_CATALOG_DESC.user_catalog_desc (
3620: x_return_status => l_return_status);
3621:
3622: if( l_return_status = FND_API.G_RET_STS_ERROR ) then
3623: IF PG_DEBUG <> 0 THEN
3624: oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned exp error');
3625: END IF;
3626: RAISE FND_API.G_EXC_ERROR ;
3627:
3628: elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
3626: RAISE FND_API.G_EXC_ERROR ;
3627:
3628: elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
3629: IF PG_DEBUG <> 0 THEN
3630: oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned unexp error');
3631: END IF;
3632: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3633:
3634: end if ;
3634: end if ;
3635:
3636: if l_catalog_dtls.count <> original_count then
3637: IF PG_DEBUG <> 0 THEN
3638: oe_debug_pub.add ('Custom hook did not return same number of elements.'||
3639: 'Original_count='||original_count||
3640: 'New count = '||l_catalog_dtls.count);
3641: END IF;
3642: raise FND_API.G_EXC_ERROR;
3645: for k in l_catalog_dtls.first..l_catalog_dtls.last
3646: loop
3647: if l_catalog_dtls(k).cat_element_value is not null then
3648: IF PG_DEBUG <> 0 THEN
3649: oe_debug_pub.add ('l_catalog_dtls('||k||').cat_element_name = '||
3650: l_catalog_dtls(k).cat_element_name||
3651: '; l_catalog_dtls('||k||').cat_element_value = '||
3652: l_catalog_dtls(k).cat_element_value);
3653: END IF;
3658: set i.element_value = l_catalog_dtls(k).cat_element_value
3659: where i.inventory_item_id = pConfigId
3660: and i.element_name = l_catalog_dtls(k).cat_element_name;
3661: IF PG_DEBUG <> 0 THEN
3662: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3663: END IF;
3664:
3665: end if;
3666: end loop;
3671:
3672: elsif CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'Y' then
3673: lStmtNum := 332;
3674: IF PG_DEBUG <> 0 THEN
3675: oe_debug_pub.add ('Std feature : Rollup lower level model catalog desc to top level');
3676: END IF;
3677: update MTL_DESCR_ELEMENT_VALUES i
3678: set i.element_value =
3679: ( select /*+ ORDERED */
3699: and v.element_name = i.element_name
3700: )
3701: where i.inventory_item_id = pConfigId;
3702: IF PG_DEBUG <> 0 THEN
3703: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3704: END IF;
3705: else
3706:
3707: lStmtNum := 333;
3705: else
3706:
3707: lStmtNum := 333;
3708: IF PG_DEBUG <> 0 THEN
3709: oe_debug_pub.add ('Std feature : DO NOT Rollup lower level model catalog desc to top level');
3710: END IF;
3711: update MTL_DESCR_ELEMENT_VALUES i
3712: set i.element_value =
3713: ( select /*+ ORDERED */
3747: -- end bugfix 2590966
3748: )
3749: where i.inventory_item_id = pConfigId;
3750: IF PG_DEBUG <> 0 THEN
3751: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3752: END IF;
3753: end if;
3754:
3755: -- end bugfix 2765635
3764: status := bmlupid_update_item_desc(pConfigid,
3765: pOrgId,
3766: xErrorMessage);
3767: IF PG_DEBUG <> 0 THEN
3768: oe_debug_pub.add('create_bom_data_ml: ' || 'bmlupid_update_item_desc returned ' || status,1 );
3769: END IF;
3770:
3771: if status <> 0 then
3772: raise FND_API.G_EXC_ERROR;
3785: and organization_id = pOrgId
3786: and alternate_bom_designator is NULL;
3787:
3788: IF PG_DEBUG <> 0 THEN
3789: oe_debug_pub.add('create_bom_data_ml: ' || 'before copying attachments l_from_sequence_id = ' || l_from_sequence_id,1 );
3790: END IF;
3791:
3792: lStmtNum := 370;
3793: fnd_attached_documents2_pkg.copy_attachments(
3819:
3820: --Bugfix 11056452
3821: lCnt := sql%rowcount;
3822: IF PG_DEBUG <> 0 THEN
3823: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
3824: END IF;
3825:
3826: delete from bom_bill_of_mtls_interface
3827: where bill_sequence_id = pConfigBillId;
3827: where bill_sequence_id = pConfigBillId;
3828:
3829: lCnt := sql%rowcount;
3830: IF PG_DEBUG <> 0 THEN
3831: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
3832: END IF;
3833:
3834: return(1);
3835:
3844:
3845: --Bugfix 11056452
3846: lCnt := sql%rowcount;
3847: IF PG_DEBUG <> 0 THEN
3848: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
3849: END IF;
3850:
3851: delete from bom_bill_of_mtls_interface
3852: where bill_sequence_id = pConfigBillId;
3852: where bill_sequence_id = pConfigBillId;
3853:
3854: lCnt := sql%rowcount;
3855: IF PG_DEBUG <> 0 THEN
3856: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
3857: END IF;
3858:
3859: return(0);
3860:
3861: when FND_API.G_EXC_ERROR then
3862: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3863: xMessageName := 'CTO_CREATE_BOM_ERROR';
3864: IF PG_DEBUG <> 0 THEN
3865: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xErrorMessage, 1);
3866: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3867: END IF;
3868:
3869: -- Sushant Fixed bug #3374548
3862: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3863: xMessageName := 'CTO_CREATE_BOM_ERROR';
3864: IF PG_DEBUG <> 0 THEN
3865: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xErrorMessage, 1);
3866: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3867: END IF;
3868:
3869: -- Sushant Fixed bug #3374548
3870: /* Clean up bom_inventory_comps_interface */
3873:
3874: --Bugfix 11056452
3875: lCnt := sql%rowcount;
3876: IF PG_DEBUG <> 0 THEN
3877: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
3878: END IF;
3879:
3880: delete from bom_bill_of_mtls_interface
3881: where bill_sequence_id = pConfigBillId;
3881: where bill_sequence_id = pConfigBillId;
3882:
3883: lCnt := sql%rowcount;
3884: IF PG_DEBUG <> 0 THEN
3885: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
3886: END IF;
3887:
3888:
3889: return(0);
3890:
3891: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
3892: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3893: IF PG_DEBUG <> 0 THEN
3894: oe_debug_pub.add('Unexpected Error: ' || xErrorMessage);
3895: END IF;
3896:
3897: -- Sushant Fixed bug #3374548
3898: /* Clean up bom_inventory_comps_interface */
3901:
3902: --Bugfix 11056452
3903: lCnt := sql%rowcount;
3904: IF PG_DEBUG <> 0 THEN
3905: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
3906: END IF;
3907:
3908: delete from bom_bill_of_mtls_interface
3909: where bill_sequence_id = pConfigBillId;
3909: where bill_sequence_id = pConfigBillId;
3910:
3911: lCnt := sql%rowcount;
3912: IF PG_DEBUG <> 0 THEN
3913: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
3914: END IF;
3915:
3916: return(0);
3917:
3918: WHEN OTHERS THEN
3919: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3920: xMessageName := 'CTO_CREATE_BOM_ERROR';
3921: IF PG_DEBUG <> 0 THEN
3922: oe_debug_pub.add('create_bom_data_ml: ' || 'other Error: ' || xErrorMessage, 1);
3923: END IF;
3924:
3925: -- Sushant Fixed bug #3374548
3926: /* Clean up bom_inventory_comps_interface */
3929:
3930: --Bugfix 11056452
3931: lCnt := sql%rowcount;
3932: IF PG_DEBUG <> 0 THEN
3933: oe_debug_pub.add('Deleted from bici, rows::'||lCnt);
3934: END IF;
3935:
3936: delete from bom_bill_of_mtls_interface
3937: where bill_sequence_id = pConfigBillId;
3937: where bill_sequence_id = pConfigBillId;
3938:
3939: lCnt := sql%rowcount;
3940: IF PG_DEBUG <> 0 THEN
3941: oe_debug_pub.add('Deleted from bmi, rows::'||lCnt);
3942: END IF;
3943:
3944: return(0);
3945:
3975: FOR v_update_item_num IN c_update_item_num(p_parent_bill_seq_id)
3976: LOOP
3977:
3978: IF PG_DEBUG <> 0 THEN
3979: oe_debug_pub.add('update_item_num: ' || 'In update loop for item '||to_char(v_update_item_num.component_item_id), 2);
3980: END IF;
3981:
3982: --
3983: -- update item_num of child of this model
3986: set item_num = p_item_num
3987: where current of c_update_item_num;
3988:
3989: IF PG_DEBUG <> 0 THEN
3990: oe_debug_pub.add('update_item_num: ' || 'Updated item '||to_char(v_update_item_num.component_item_id)|| ' with item num '||to_char(p_item_num), 2);
3991: END IF;
3992:
3993: p_item_num := p_item_num + p_seq_increment;
3994:
4004: and organization_id = p_org_id
4005: and alternate_bom_designator is null;
4006:
4007: IF PG_DEBUG <> 0 THEN
4008: oe_debug_pub.add('update_item_num: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
4009: END IF;
4010:
4011: update_item_num(
4012: p_bill_seq_id,
4016:
4017: EXCEPTION
4018: WHEN NO_DATA_FOUND THEN
4019: IF PG_DEBUG <> 0 THEN
4020: oe_debug_pub.add('update_item_num: ' || 'This component '||to_char(v_update_item_num.component_item_id)||' does not have a BOM', 2);
4021: END IF;
4022:
4023: END;
4024:
4382:
4383: lCnt := sql%rowcount ;
4384:
4385: IF PG_DEBUG <> 0 THEN
4386: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Second -- Inserted in BE Temp ' || lCnt ||' Option item/Option class rows with bill seq id as '|| pConfigBillId,1);
4387: END IF;
4388:
4389: select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4390: count(*) into v_zero_qty_count from bom_explosion_temp
4389: select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4390: count(*) into v_zero_qty_count from bom_explosion_temp
4391: where bill_sequence_id = pConfigBillId and component_quantity = 0 ;
4392:
4393: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4394:
4395: if( v_zero_qty_count > 0 ) then
4396: oe_debug_pub.add( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4397:
4392:
4393: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4394:
4395: if( v_zero_qty_count > 0 ) then
4396: oe_debug_pub.add( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4397:
4398: select concatenated_segments into v_model_item_name
4399: from mtl_system_items_kfv
4400: where inventory_item_id = pModelId
4447:
4448: test_var := sql%rowcount;
4449:
4450: IF PG_DEBUG <> 0 THEN
4451: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to future date = '||test_var,1);
4452: oe_debug_pub.add('Create_bom_ml: pconfigBillId = '||to_char(pConfigBillid),1);
4453: END IF;
4454: --Bug 7418622.FP for 7154767
4455:
4448: test_var := sql%rowcount;
4449:
4450: IF PG_DEBUG <> 0 THEN
4451: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to future date = '||test_var,1);
4452: oe_debug_pub.add('Create_bom_ml: pconfigBillId = '||to_char(pConfigBillid),1);
4453: END IF;
4454: --Bug 7418622.FP for 7154767
4455:
4456: /* end Extend Effectivity Dates for Option Items with disable date */
4598:
4599: lCnt := sql%rowcount ;
4600:
4601: IF PG_DEBUG <> 0 THEN
4602: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'First -- Inserted in BE Temp ' || lCnt ||' Incl Item rows with bill seq id as '|| pConfigBillId,1);
4603: END IF;
4604:
4605: lStmtNumber := 530;
4606:
4693:
4694: -- FOR r1 in c_model_oc_oi_rows(pConfigBillId) LOOP
4695: -- IF r1.operation_seq_num = 1 AND instr(r1.component_code,'-',1,2)<>0 THEN
4696: -- IF PG_DEBUG <> 0 THEN -- 13079222
4697: -- oe_debug_pub.add ('Component Code: ' || r1.component_code,1);
4698: -- END IF;
4699: -- UPDATE bom_explosion_temp bet
4700: -- SET bet.operation_seq_num = (
4701: -- SELECT /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4712:
4713: FOR r1 in c_model_oc_oi_rows(pConfigBillId) LOOP
4714: IF r1.operation_seq_num = 1 AND instr(r1.component_code,'-',1,2)<>0 THEN
4715: IF PG_DEBUG <> 0 THEN -- 13079222
4716: oe_debug_pub.add ('Component Code: ' || r1.component_code,1);
4717: END IF;
4718:
4719: begin
4720:
4720:
4721: l_bet_op_seq := null;
4722:
4723: IF PG_DEBUG <> 0 THEN
4724: oe_debug_pub.add ('Inside begin for comp code:' || r1.component_code);
4725: END IF;
4726:
4727: lStmtNumber := 531;
4728: SELECT DISTINCT nvl(operation_seq_num,1) INTO l_bet_op_seq FROM (
4737: AND top_bill_sequence_id = pConfigBillId)
4738: WHERE rnk = 1;
4739:
4740: IF PG_DEBUG <> 0 THEN
4741: oe_debug_pub.add ('Inside begin:: l_bet_op_seq:' || l_bet_op_seq);
4742: END IF;
4743:
4744: exception
4745: when no_data_found then
4744: exception
4745: when no_data_found then
4746:
4747: IF PG_DEBUG <> 0 THEN
4748: oe_debug_pub.add ('Inside NDF exception for comp code:' || r1.component_code);
4749: END IF;
4750:
4751: begin
4752: lStmtNumber := 532;
4760: AND top_bill_sequence_id = pConfigBillId)
4761: WHERE rnk = 1;
4762:
4763: IF PG_DEBUG <> 0 THEN
4764: oe_debug_pub.add ('Inside NDF exception:: l_bet_op_seq:' || l_bet_op_seq);
4765: END IF;
4766:
4767: exception
4768: when no_data_found then
4767: exception
4768: when no_data_found then
4769:
4770: IF PG_DEBUG <> 0 THEN
4771: oe_debug_pub.add('NDF exception inside NDF exception for comp code:' || r1.component_code);
4772: END IF;
4773:
4774: l_bet_op_seq := r1.operation_seq_num;
4775: end;
4776:
4777: end;
4778:
4779: IF PG_DEBUG <> 0 THEN -- 13079222
4780: oe_debug_pub.add ('Operation_sequence num : ' || l_bet_op_seq,1);
4781: END IF;
4782:
4783: lStmtNumber := 533;
4784: UPDATE bom_explosion_temp bet
4899: , r2.basis_type /* LBM project */
4900: );
4901: lCnt := sql%rowcount ;
4902: IF PG_DEBUG <> 0 THEN
4903: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'INSIDE Loop : Inserted in BE Temp ' || lCnt ||' manadatory item rows with bill seq id as '|| pConfigBillId,1);
4904: END IF;
4905: END LOOP;
4906:
4907:
5050: where bill_sequence_id = pConfigBillId;
5051:
5052: lCnt := sql%rowcount ;
5053: IF PG_DEBUG <> 0 THEN
5054: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Final - Inserted in BIC Interface ' || lCnt ||' rows from BET',1);
5055: END IF;
5056:
5057: /* begin Check for Overlapping Effectivity Dates */
5058: v_overlap_check := 0 ;
5071: v_overlap_check := 0 ;
5072: end;
5073:
5074: IF PG_DEBUG <> 0 THEN
5075: oe_debug_pub.add(' Overlap check = '||v_overlap_check,1);
5076: END IF;
5077:
5078: if(v_overlap_check = 1) then
5079:
5078: if(v_overlap_check = 1) then
5079:
5080: for debug_cur2 in debug_cur1
5081: Loop
5082: oe_debug_pub.add(debug_cur2.component_item_id||'-'||debug_cur2.component_sequence_id||'-'||
5083: debug_cur2.operation_seq_num||'-'||to_char(debug_cur2.effectivity_date)
5084: ||'-'||to_char(debug_cur2.disable_date),1);
5085:
5086: end loop;
5110: if( v_t_overlap_src_op_seq_num.count > 0 ) then
5111: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
5112: loop
5113: IF PG_DEBUG <> 0 THEN
5114: oe_debug_pub.add (' The following components have overlapping dates ', 1);
5115: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
5116: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
5117:
5118: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
5111: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
5112: loop
5113: IF PG_DEBUG <> 0 THEN
5114: oe_debug_pub.add (' The following components have overlapping dates ', 1);
5115: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
5116: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
5117:
5118: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
5119: ' ' || v_t_overlap_src_op_seq_num(i) ||
5114: oe_debug_pub.add (' The following components have overlapping dates ', 1);
5115: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
5116: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
5117:
5118: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
5119: ' ' || v_t_overlap_src_op_seq_num(i) ||
5120: ' ' || v_t_overlap_src_eff_date(i) ||
5121: ' ' || v_t_overlap_src_disable_date(i) ||
5122: ' OVERLAPS ' ||
5157: when no_data_found then
5158: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber);
5159: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
5160: IF PG_DEBUG <> 0 THEN
5161: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: No data found in inherit_op_seq_ml. Returning 0. Stmt:' || lStmtNumber, 1);
5162: END IF;
5163: return(0);
5164:
5165: when FND_API.G_EXC_ERROR then
5165: when FND_API.G_EXC_ERROR then
5166: xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
5167: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
5168: IF PG_DEBUG <> 0 THEN
5169: oe_debug_pub.add('create_bom_data_ml: ' || 'Error: ' || xErrorMessage, 1);
5170: END IF;
5171: return(0);
5172:
5173: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
5173: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
5174: xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
5175: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
5176: IF PG_DEBUG <> 0 THEN
5177: oe_debug_pub.add('Error: ' || xErrorMessage);
5178: END IF;
5179: return(0);
5180:
5181: when others then
5181: when others then
5182: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber)||':'||substrb(sqlerrm,1,150);
5183: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
5184: IF PG_DEBUG <> 0 THEN
5185: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: Others excpn : '||sqlerrm, 1);
5186: END IF;
5187: return (0);
5188: END inherit_op_seq_ml;
5189:
5206:
5207: xBillId := 0;
5208:
5209: IF PG_DEBUG <> 0 THEN
5210: oe_debug_pub.add('check_bom: ' || 'before check_bom sql::xBillId:: '||to_char(xBillId ), 2);
5211: END IF;
5212:
5213:
5214: select bill_sequence_id
5218: and organization_id = pOrgId
5219: and alternate_bom_designator is null;
5220:
5221: IF PG_DEBUG <> 0 THEN
5222: oe_debug_pub.add('check_bom: ' || 'after check_bom sql::xBillId:: '||to_char(xBillId )||'returning 1', 2);
5223: END IF;
5224:
5225: return(1);
5226:
5227: exception
5228:
5229: when no_data_found then
5230: IF PG_DEBUG <> 0 THEN
5231: oe_debug_pub.add ('check_bom: ' || 'NDF exception for Check BOM::item id '||to_char(pItemId), 1);
5232: END IF;
5233: return(0);
5234:
5235: when others then
5233: return(0);
5234:
5235: when others then
5236: IF PG_DEBUG <> 0 THEN
5237: oe_debug_pub.add ('check_bom: ' || 'Others exception for Check BOM::item id '||to_char(pItemId), 1);
5238: END IF;
5239: cto_msg_pub.cto_message('BOM', 'CTO_CREATE_BOM_ERROR');
5240: return(0);
5241:
5261:
5262: begin
5263:
5264: IF PG_DEBUG <> 0 THEN
5265: oe_debug_pub.add('get_model_lead_time: ' || 'Getting Lead Time for Model: ' || to_char(pModelId), 2);
5266: END IF;
5267: lStmtNum := 100;
5268:
5269: select (ceil(nvl(msi.fixed_lead_time,0)
5273: where inventory_item_id = pModelId
5274: and organization_id = pOrgId;
5275:
5276: IF PG_DEBUG <> 0 THEN
5277: oe_debug_pub.add('get_model_lead_time: ' || 'Lead Time: ' || to_char(pLeadtime), 2);
5278: END IF;
5279:
5280: return 1;
5281:
5337: IF profile_check%NOTFOUND THEN
5338: profile_setting := 'Y';
5339: END IF;
5340: IF PG_DEBUG <> 0 THEN
5341: oe_debug_pub.add ('bmlggpn_get_group_name: ' || ' USE_NAME_ICG_DESC :'|| profile_setting, 2);
5342: END IF;
5343:
5344: if profile_setting = 'Y' then
5345:
5405: INV_GRP_ERROR exception;
5406: begin
5407: lStmtNum := 280;
5408: IF PG_DEBUG <> 0 THEN
5409: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5410: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5411: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5412: END IF;
5413:
5406: begin
5407: lStmtNum := 280;
5408: IF PG_DEBUG <> 0 THEN
5409: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5410: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5411: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5412: END IF;
5413:
5414: select concatenated_segment_delimiter into delimiter
5407: lStmtNum := 280;
5408: IF PG_DEBUG <> 0 THEN
5409: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5410: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5411: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5412: END IF;
5413:
5414: select concatenated_segment_delimiter into delimiter
5415: from fnd_id_flex_structures
5422: where inventory_item_id = item_id
5423: and organization_id = org_id;
5424:
5425: IF PG_DEBUG <> 0 THEN
5426: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item_catalog_group_id : ' || group_id,2);
5427: END IF;
5428: idx := 0;
5429: cat_value := '';
5430: open cc;
5445: cat_value := cat_value || SUBSTRB(delimiter || e_value,1,
5446: 240-LENGTHB(cat_value));
5447: end if;
5448: IF PG_DEBUG <> 0 THEN
5449: oe_debug_pub.add('bmlupid_update_item_desc: ' || 'cat_value :' || cat_value,1);
5450: END IF;
5451: idx := idx + 1;
5452: end loop;
5453: close cc;
5469: exception
5470: when INV_GRP_ERROR then
5471: err_buf := 'CTOCBOMB: Invalid catalog group for the item ' || item_id || ' status:' || status;
5472: IF PG_DEBUG <> 0 THEN
5473: oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5474: END IF;
5475: cto_msg_pub.cto_message ('BOM', 'CTO_INVALID_CATALOG_GRP');
5476: return(1);
5477:
5477:
5478: when OTHERS then
5479: err_buf := 'CTOCBOMB: ' || lStmtNum ||substrb(SQLERRM,1,150);
5480: IF PG_DEBUG <> 0 THEN
5481: oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5482: END IF;
5483: cto_msg_pub.cto_message ('BOM', 'CTO_CREATE_BOM_ERROR');
5484: return(1);
5485:
5551: -- Get the default adminstrator value from Workflow Attributes.
5552: lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOEXCP',
5553: ItemKey => litem_key,
5554: aname => 'WF_ADMINISTRATOR');
5555: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
5556:
5557: else
5558:
5559: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
5555: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
5556:
5557: else
5558:
5559: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
5560:
5561: END IF;
5562:
5563: lstmt_num := 30 ;
5624: lstmt_num := 90 ;
5625: wf_engine.StartProcess(itemtype=>'CTOEXCP',ItemKey=>litem_key);
5626:
5627:
5628: oe_debug_pub.add( ' done till stmt ' || lstmt_num ) ;
5629:
5630:
5631:
5632: exception
5631:
5632: exception
5633: when others then
5634:
5635: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
5636: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
5637:
5638:
5639: end send_oid_notification ;
5632: exception
5633: when others then
5634:
5635: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
5636: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
5637:
5638:
5639: end send_oid_notification ;
5640: