[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
383:
384: lStatus := check_bom (pConfigId, pOrgId,lItmBillId);
385: if lStatus = 1 then
386: IF PG_DEBUG <> 0 THEN
387: oe_debug_pub.add('create_bom_ml: ' || ' Config BOM ' || lItmBillId || ' Already exists ' ,1);
388: END IF;
389: return (1);
390: end if;
391:
410: v_ato_line_id = pLineId and pOrgId = CTO_UTILITY_PK.PC_BOM_CURRENT_ORG ) then
411:
412: lConfigBillId := CTO_UTILITY_PK.PC_BOM_BILL_SEQUENCE_ID ;
413:
414: oe_debug_pub.add('create_bom_ml: ' || 'Setting Bill ' || lConfigBillId || ' for org ' || pOrgId
415: || ' for line id ' || pLineId , 1);
416: else
417:
418: select bom_inventory_components_s.nextval
423:
424: xBillId := lConfigBillId;
425:
426: IF PG_DEBUG <> 0 THEN
427: oe_debug_pub.add('create_bom_ml: ' || 'Creating Bill ' || lConfigBillId, 1);
428: END IF;
429:
430:
431:
433: delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
434:
435:
436: IF PG_DEBUG <> 0 THEN
437: oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
438: END IF;
439:
440:
441:
461: */
462:
463:
464: IF PG_DEBUG <> 0 THEN
465: oe_debug_pub.add('create_bom_ml: ' || 'Line Id ' || pLineId ||' has Schedule Ship Date of '||g_SchShpDate, 2);
466: END IF;
467:
468: -- End Bugfix 1912376
469:
473: lStmtNum := 40;
474:
475: -- get oevalidation org
476: IF PG_DEBUG <> 0 THEN
477: oe_debug_pub.add('create_bom_ml: ' || 'Before getting validation org', 2);
478: END IF;
479:
480:
481: /* BUG #1957336 Change for preconfigure bom */
496: */
497:
498:
499: IF PG_DEBUG <> 0 THEN
500: oe_debug_pub.add('create_bom_ml: ' || 'Going to fetch Validation Org ' ,2);
501: END IF;
502:
503:
504: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
513: raise FND_API.G_EXC_ERROR;
514: end if;
515:
516: IF PG_DEBUG <> 0 THEN
517: oe_debug_pub.add('create_bom_ml: ' || 'Validation Org is :' || lOEValidationOrg,2);
518: END IF;
519:
520: lStmtNum := 41;
521:
526: from bom_cto_order_lines bcol
527: where bcol.line_id = lParentAtoLineId;
528:
529: IF PG_DEBUG <> 0 THEN
530: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
531: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
532: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
533: END IF;
534:
527: where bcol.line_id = lParentAtoLineId;
528:
529: IF PG_DEBUG <> 0 THEN
530: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
531: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
532: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
533: END IF;
534:
535: lStmtNum := 42;
528:
529: IF PG_DEBUG <> 0 THEN
530: oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
531: oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
532: oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
533: END IF;
534:
535: lStmtNum := 42;
536: lStatus := get_model_lead_time(
541: lErrBuf);
542:
543: if (lStatus = 0) then
544: IF PG_DEBUG <> 0 THEN
545: oe_debug_pub.add('create_bom_ml: ' || 'Failed in get_model_lead_time. Error Buffer : '||lERrBuf, 1);
546: END IF;
547: raise FND_API.G_EXC_ERROR;
548: else
549: lTotLeadTime := lLeadTime + lTotLeadTime;
552: exit when lLineId = lParentAtoLineId; -- when we reach the top model
553: end loop;
554:
555: IF PG_DEBUG <> 0 THEN
556: oe_debug_pub.add('create_bom_ml: ' || 'Total lead time is: ' || to_char(lTotLeadTime), 1);
557: END IF;
558:
559: xTableName := 'OE_ORDER_LINES ';
560: lStmtNum := 43;
584: when no_data_found then
585: xErrorMessage := ' Error in calculating Estimated Release date ';
586: xMessageName := 'CTO_NO_CALENDAR';
587: IF PG_DEBUG <> 0 THEN
588: oe_debug_pub.add('create_bom_ml: ' || 'Error in stmt # ' || lStmtNum ||' : '|| xErrorMessage, 1);
589: END IF;
590: return(0);
591: end;
592: -- Bugfix 2374246
591: end;
592: -- Bugfix 2374246
593:
594: IF PG_DEBUG <> 0 THEN
595: oe_debug_pub.add ('create_bom_ml: ' || 'Estimated Release Date is : ' || lEstRelDate, 2);
596: END IF;
597: g_EstRelDate := lEstRelDate; -- 2222518
598: IF PG_DEBUG <> 0 THEN
599: oe_debug_pub.add ('create_bom_ml: ' || 'Global Estimated Release Date is : ' || g_EstRelDate, 2);
595: oe_debug_pub.add ('create_bom_ml: ' || 'Estimated Release Date is : ' || lEstRelDate, 2);
596: END IF;
597: g_EstRelDate := lEstRelDate; -- 2222518
598: IF PG_DEBUG <> 0 THEN
599: oe_debug_pub.add ('create_bom_ml: ' || 'Global Estimated Release Date is : ' || g_EstRelDate, 2);
600: END IF; -- 2222518
601:
602: -- b2307936
603: /*---------------------------------------------------------------------------+
615:
616: lOpseqProfile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
617:
618: IF PG_DEBUG <> 0 THEN
619: oe_debug_pub.add ('create_bom_ml: ' || 'Config_inherit_op_seq is ' || lOpseqProfile, 2);
620: END IF;
621:
622: lStmtNum := 80;
623: if lOpseqProfile = 1 then
621:
622: lStmtNum := 80;
623: if lOpseqProfile = 1 then
624: IF PG_DEBUG <> 0 THEN
625: oe_debug_pub.add('create_bom_ml: ' || 'Calling inherit_op_seq_ml with line id ' ||
626: to_char(pLineId) || ' in org ' ||
627: to_char(pOrgId), 1);
628: END IF;
629: lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
628: END IF;
629: lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
630: if lStatus <> 1 then
631: IF PG_DEBUG <> 0 THEN
632: oe_debug_pub.add('create_bom_ml: ' || 'Failed in inherit_op_seq for line id: '|| to_char(pLineId), 1);
633: END IF;
634: return(0);
635: end if;
636: else
861:
862:
863: lCnt := sql%rowcount ;
864: IF PG_DEBUG <> 0 THEN
865: oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
866: END IF;
867:
868:
869:
871: select count(*) into v_zero_qty_count from bom_inventory_comps_interface
872: where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
873:
874:
875: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
876:
877: if( v_zero_qty_count > 0 ) then
878:
879: oe_debug_pub.add( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
875: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
876:
877: if( v_zero_qty_count > 0 ) then
878:
879: oe_debug_pub.add( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
880:
881: select concatenated_segments into v_model_item_name
882: from mtl_system_items_kfv
883: where inventory_item_id = pModelId
1098:
1099: lCnt := sql%rowcount ;
1100:
1101: IF PG_DEBUG <> 0 THEN
1102: oe_debug_pub.add ('create_bom_ml: ' || 'Second -- Inserted ' || lCnt ||' rows',1);
1103: END IF;
1104:
1105: end if; /* end of check lOpseqProfile = 1 */
1106:
1106:
1107:
1108: /* begin Extend Effectivity Dates for Option Items with disable date */
1109:
1110: oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1111:
1112: For debug_rec in debug_cur
1113: Loop
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1110: oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1111:
1112: For debug_rec in debug_cur
1113: Loop
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1117: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1118: oe_debug_pub.add('==================================',1);
1111:
1112: For debug_rec in debug_cur
1113: Loop
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1117: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1118: oe_debug_pub.add('==================================',1);
1119: End Loop;
1112: For debug_rec in debug_cur
1113: Loop
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1117: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1118: oe_debug_pub.add('==================================',1);
1119: End Loop;
1120:
1113: Loop
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1117: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1118: oe_debug_pub.add('==================================',1);
1119: End Loop;
1120:
1121: -- Modified by Renga Kannan on 01/10/06
1114: oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115: oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116: oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1117: oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1118: oe_debug_pub.add('==================================',1);
1119: End Loop;
1120:
1121: -- Modified by Renga Kannan on 01/10/06
1122: -- The logic to find the last window for option item and mandatory comps
1140: and bill_sequence_id = lConfigBillId
1141: and disable_date <> g_futuredate ;
1142:
1143: If PG_DEBUG <> 0 Then
1144: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1145: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1146: End if;
1147:
1148:
1141: and disable_date <> g_futuredate ;
1142:
1143: If PG_DEBUG <> 0 Then
1144: oe_debug_pub.add('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1145: oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1146: End if;
1147:
1148:
1149:
1165: exception
1166: when others then
1167: v_overlap_check := 0 ;
1168: end;
1169: oe_debug_pub.add(' Overlap check = '||v_overlap_check,1);
1170:
1171: if(v_overlap_check = 1) then
1172:
1173: for debug_cur2 in debug_cur1
1171: if(v_overlap_check = 1) then
1172:
1173: for debug_cur2 in debug_cur1
1174: Loop
1175: oe_debug_pub.add(debug_cur2.component_item_id||'-'||debug_cur2.component_sequence_id||'-'||
1176: debug_cur2.operation_seq_num||'-'||to_char(debug_cur2.effectivity_date)
1177: ||'-'||to_char(debug_cur2.disable_date),1);
1178:
1179: end loop;
1199: exception
1200: when others then
1201: null ;
1202: end ;
1203: oe_debug_pub.add('Over lap record count = '||v_t_overlap_src_op_seq_num.count,1);
1204:
1205: if( v_t_overlap_src_op_seq_num.count > 0 ) then
1206: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1207: loop
1205: if( v_t_overlap_src_op_seq_num.count > 0 ) then
1206: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1207: loop
1208: IF PG_DEBUG <> 0 THEN
1209: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1210: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1211: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1212:
1213: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1206: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1207: loop
1208: IF PG_DEBUG <> 0 THEN
1209: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1210: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1211: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1212:
1213: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1214: ' ' || v_t_overlap_src_op_seq_num(i) ||
1209: oe_debug_pub.add (' The following components have overlapping dates ', 1);
1210: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1211: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1212:
1213: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1214: ' ' || v_t_overlap_src_op_seq_num(i) ||
1215: ' ' || v_t_overlap_src_eff_date(i) ||
1216: ' ' || v_t_overlap_src_disable_date(i) ||
1217: ' OVERLAPS ' ||
1261:
1262: lcreate_item := nvl(FND_PROFILE.VALUE('CTO_CONFIG_EXCEPTION'), 1);
1263:
1264: IF PG_DEBUG <> 0 THEN
1265: oe_debug_pub.add ('Config exception profile '||lcreate_item, 1);
1266: END IF;
1267:
1268: open missed_lines(pLineId, lConfigBillId, lEstRelDate ); /* Effectivity dates change */
1269: loop
1277:
1278: BEGIN
1279:
1280: IF PG_DEBUG <> 0 THEN
1281: oe_debug_pub.add('Select missed component details.. ' ,1);
1282: END IF;
1283:
1284:
1285:
1284:
1285:
1286: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1287: IF PG_DEBUG <> 0 THEN
1288: oe_debug_pub.add('Pre configured Item .. ' ,1);
1289: END IF;
1290:
1291: select substrb(msi.concatenated_segments,1,50),
1292: 'Not Available' ,
1298: and bcol.line_id = v_missed_line_id;
1299:
1300: else
1301: IF PG_DEBUG <> 0 THEN
1302: oe_debug_pub.add('Auto configured Item .. ' ,1);
1303: END IF;
1304:
1305: select substrb(msi.concatenated_segments,1,50),
1306: to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'
1320:
1321: lStmtNum := 53;
1322:
1323: IF PG_DEBUG <> 0 THEN
1324: oe_debug_pub.add('Select model.. ' ,1);
1325: END IF;
1326:
1327: select substrb(concatenated_segments,1,50)
1328: into v_model
1332:
1333: lStmtNum := 54;
1334:
1335: IF PG_DEBUG <> 0 THEN
1336: oe_debug_pub.add('Select Org.. ' ,1);
1337: END IF;
1338:
1339: select organization_code
1340: into lOrg_code
1362:
1363: if ( lcreate_item = 1 ) then
1364:
1365: IF PG_DEBUG <> 0 THEN
1366: oe_debug_pub.add ('Warning: The component '||v_missed_item
1367: || ' on Line Number '||v_missed_line_number
1368: || ' in organization ' || lOrg_code
1369: || ' was not included in the configured item''s bill. ',1);
1370: oe_debug_pub.add ('Model Name : '||v_model,1);
1366: oe_debug_pub.add ('Warning: The component '||v_missed_item
1367: || ' on Line Number '||v_missed_line_number
1368: || ' in organization ' || lOrg_code
1369: || ' was not included in the configured item''s bill. ',1);
1370: oe_debug_pub.add ('Model Name : '||v_model,1);
1371: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1372:
1373:
1374:
1367: || ' on Line Number '||v_missed_line_number
1368: || ' in organization ' || lOrg_code
1369: || ' was not included in the configured item''s bill. ',1);
1370: oe_debug_pub.add ('Model Name : '||v_model,1);
1371: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1372:
1373:
1374:
1375:
1394:
1395:
1396: else
1397: IF PG_DEBUG <> 0 THEN
1398: oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
1399: || ' on Line Number '||v_missed_line_number
1400: || ' in organization ' || lOrg_code
1401: || ' could not be included in the configured item''s bill. ',1);
1402: oe_debug_pub.add ('Model Name : '||v_model,1);
1398: oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
1399: || ' on Line Number '||v_missed_line_number
1400: || ' in organization ' || lOrg_code
1401: || ' could not be included in the configured item''s bill. ',1);
1402: oe_debug_pub.add ('Model Name : '||v_model,1);
1403: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1404: END IF;
1405:
1406: /*
1399: || ' on Line Number '||v_missed_line_number
1400: || ' in organization ' || lOrg_code
1401: || ' could not be included in the configured item''s bill. ',1);
1402: oe_debug_pub.add ('Model Name : '||v_model,1);
1403: oe_debug_pub.add ('Order Number : '||v_order_number,1);
1404: END IF;
1405:
1406: /*
1407: l_token(1).token_name := 'OPTION_NAME';
1423: EXCEPTION -- exception for stmt 52 ,53 and 54
1424:
1425: when others then
1426: IF PG_DEBUG <> 0 THEN
1427: oe_debug_pub.add('Others excepn from stmt '||lStmtNum ||':'||sqlerrm);
1428: END IF;
1429: raise fnd_api.g_exc_error;
1430: END ;
1431: end loop;
1436: should end with warning */
1437:
1438: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1439: IF PG_DEBUG <> 0 THEN
1440: oe_debug_pub.add ('Will not go through Hold Logic and Notification as Preconfigured Bom' , 1 );
1441: END IF;
1442:
1443: if missed_lines%ROWCOUNT > 0 then
1444: if ( lcreate_item = 1 ) then
1442:
1443: if missed_lines%ROWCOUNT > 0 then
1444: if ( lcreate_item = 1 ) then
1445: IF PG_DEBUG <> 0 THEN
1446: oe_debug_pub.add ('Create Item profile set to Create and Link Item ' , 1 );
1447: END IF;
1448:
1449: xMessageName := 'CTO_DROP_ITEM_FROM_CONFIG';
1450:
1486: -- g_t_dropped_item_type(v_table_count).MFG_REL_DATE := to_char( lEstRelDate , 'DD-MON-YYYY' ) ;
1487: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1488:
1489: IF PG_DEBUG <> 0 THEN
1490: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1491: END IF;
1492:
1493: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1494:
1493: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1494:
1495: else
1496: IF PG_DEBUG <> 0 THEN
1497: oe_debug_pub.add ('Create Item profile set to Do Not Create Item ' , 1 );
1498: END IF;
1499:
1500: xMessageName := 'CTO_DO_NOT_CREATE_ITEM';
1501:
1535: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1536: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1537:
1538: IF PG_DEBUG <> 0 THEN
1539: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1540: END IF;
1541:
1542: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1543:
1566: -- bugfix 2840801 :
1567: -- Set the global variable gApplyHold to apply hold on config line.
1568:
1569: IF PG_DEBUG <> 0 THEN
1570: oe_debug_pub.add ('Setting the global var gApplyHold to Y');
1571: END IF;
1572:
1573: CTO_CONFIG_BOM_PK.gApplyHold := 'Y';
1574:
1604: where line_id = pLineId ;
1605:
1606: end if;
1607:
1608: oe_debug_pub.add( ' DROPPED ITEM INFO: ' ||
1609: ' Problem Model ' || v_problem_model ||
1610: ' Problem CONFIG ' || v_problem_config ||
1611: ' ERROR ORG ' || v_error_org ||
1612: ' PROBLEM MODEL LINE NUM ' || v_problem_model_line_num
1628: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1629: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1630:
1631: IF PG_DEBUG <> 0 THEN
1632: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1633: END IF;
1634:
1635: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char(fnd_global.conc_request_id) ;
1636:
1639:
1640: /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1641:
1642: IF PG_DEBUG <> 0 THEN
1643: oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1644: END IF;
1645:
1646: v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_CREATED
1647: ,p_inventory_item_id => pModelId
1652:
1653:
1654: if( v_recipient is not null ) then
1655: IF PG_DEBUG <> 0 THEN
1656: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1657: END IF;
1658:
1659: g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1660:
1662:
1663:
1664:
1665: IF PG_DEBUG <> 0 THEN
1666: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1667: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1668: END IF;
1669:
1670: BEGIN
1663:
1664:
1665: IF PG_DEBUG <> 0 THEN
1666: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1667: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1668: END IF;
1669:
1670: BEGIN
1671: -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1682: and p.planner_code = item.planner_code
1683: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1684:
1685:
1686: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1687:
1688:
1689: EXCEPTION
1690: WHEN OTHERS THEN
1688:
1689: EXCEPTION
1690: WHEN OTHERS THEN
1691: IF PG_DEBUG <> 0 THEN
1692: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1693:
1694: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1695:
1696:
1690: WHEN OTHERS THEN
1691: IF PG_DEBUG <> 0 THEN
1692: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1693:
1694: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1695:
1696:
1697: END IF;
1698: END;
1721:
1722: else
1723:
1724: IF PG_DEBUG <> 0 THEN
1725: oe_debug_pub.add ('Not creating Item...');
1726: END IF;
1727:
1728:
1729:
1767: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1768: g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1769:
1770: IF PG_DEBUG <> 0 THEN
1771: oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1772: END IF;
1773:
1774: g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1775:
1778: /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1779:
1780:
1781: IF PG_DEBUG <> 0 THEN
1782: oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1783: END IF;
1784:
1785: v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_NOT_CREATED
1786: ,p_inventory_item_id => pModelId
1791:
1792:
1793: if( v_recipient is not null ) then
1794: IF PG_DEBUG <> 0 THEN
1795: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1796: END IF;
1797:
1798: g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1799:
1803:
1804:
1805:
1806: IF PG_DEBUG <> 0 THEN
1807: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1808: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1809: END IF;
1810:
1811: BEGIN
1804:
1805:
1806: IF PG_DEBUG <> 0 THEN
1807: oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1808: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1809: END IF;
1810:
1811: BEGIN
1812: -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1823: and p.planner_code = item.planner_code
1824: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1825:
1826:
1827: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1828:
1829:
1830: EXCEPTION
1831: WHEN OTHERS THEN
1829:
1830: EXCEPTION
1831: WHEN OTHERS THEN
1832: IF PG_DEBUG <> 0 THEN
1833: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1834:
1835: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1836:
1837:
1831: WHEN OTHERS THEN
1832: IF PG_DEBUG <> 0 THEN
1833: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1834:
1835: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1836:
1837:
1838: END IF;
1839: END;
1849: -- rkaza. bug 4315973. 08/24/2005.
1850: -- Hold ato line for dropped items when profile is set to do not
1851: -- create item. Removed aps_version restriction.
1852:
1853: oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1854: oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1855:
1856: cto_utility_pk.apply_create_config_hold( v_ato_line_id, v_header_id, l_return_status, l_msg_count, l_msg_data ) ;
1857:
1850: -- Hold ato line for dropped items when profile is set to do not
1851: -- create item. Removed aps_version restriction.
1852:
1853: oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1854: oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1855:
1856: cto_utility_pk.apply_create_config_hold( v_ato_line_id, v_header_id, l_return_status, l_msg_count, l_msg_data ) ;
1857:
1858:
1888: EXCEPTION -- exception for stmt 51 and 55
1889:
1890: when others then
1891: IF PG_DEBUG <> 0 THEN
1892: oe_debug_pub.add ('Failed in stmt ' || lStmtNum || ' with error: '||sqlerrm);
1893: END IF;
1894: raise fnd_api.g_exc_error;
1895: END ;
1896:
2042:
2043: lCnt := sql%rowcount ;
2044:
2045: IF PG_DEBUG <> 0 THEN
2046: oe_debug_pub.add ('create_bom_ml: ' || 'Third -- Inserted ' || lCnt ||' rows',1);
2047: END IF;
2048:
2049: xBillId := lConfigBillId;
2050:
2060: xErrorMessage := 'CTOCBOMB:create_bom_ml failed with expected error in stmt '||to_char(lStmtNum);
2061: --xMessageName := 'CTO_CREATE_BOM_ERROR';
2062:
2063: IF PG_DEBUG <> 0 THEN
2064: oe_debug_pub.add ('create_bom_ml: ' || 'create_item::exp error::'||to_char(lStmtNum)||sqlerrm,1);
2065: END IF;
2066:
2067:
2068:
2071:
2072: xBillId := null ;
2073:
2074: IF PG_DEBUG <> 0 THEN
2075: oe_debug_pub.add ('create_bom_ml: ' || 'deleted records from bici ::'||to_char(sql%rowcount) ,1);
2076: END IF;
2077:
2078: return(0);
2079:
2080: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2081: xErrorMessage := 'CTOCBOMB:create_bom_ml failed with unexpected error in stmt '||to_char(lStmtNum);
2082: xMessageName := 'CTO_CREATE_BOM_ERROR';
2083: IF PG_DEBUG <> 0 THEN
2084: oe_debug_pub.add ('create_bom_ml: ' || 'create_item::unexp error::'||to_char(lStmtNum)||sqlerrm,1);
2085: END IF;
2086: return(-1);
2087:
2088:
2089: WHEN OTHERS THEN
2090: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNum)||':'||substrb(sqlerrm,1,150);
2091: xMessageName := 'CTO_CREATE_BOM_ERROR';
2092: IF PG_DEBUG <> 0 THEN
2093: oe_debug_pub.add('create_bom_ml: ' || 'Error: Others excpn in create_bom_ml: ' || sqlerrm);
2094: END IF;
2095: return(-1);
2096:
2097: END create_bom_ml;
2278:
2279: if model_comp_seq_id_arr.count > 0 then
2280: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
2281: loop
2282: oe_debug_pub.add( ' Start Looping ',1);
2283: IF PG_DEBUG <> 0 THEN
2284: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
2285: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
2286: ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
2280: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
2281: loop
2282: oe_debug_pub.add( ' Start Looping ',1);
2283: IF PG_DEBUG <> 0 THEN
2284: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
2285: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
2286: ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
2287:
2288: END IF;
2302: loop
2303:
2304: -- Get all eff and disable dates in asc order
2305: -- 4244576
2306: oe_debug_pub.add( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
2307:
2308: select distinct effectivity_date
2309: BULK COLLECT INTO asc_date_arr
2310: from bom_inventory_comps_interface
2324: if asc_date_arr.count > 0 then
2325: for x1 in asc_date_arr.FIRST..asc_date_arr.LAST
2326: loop
2327: IF PG_DEBUG <> 0 THEN
2328: oe_debug_pub.add ('Date ('||x1||') = '||to_char(asc_date_arr(x1),'DD-MON-YY HH24:MI:SS'),1);
2329: END IF;
2330: end loop;
2331: end if;
2332:
2346: if club_tab_arr.count > 0 then
2347: for x3 in club_tab_arr.FIRST..club_tab_arr.LAST
2348: loop
2349: IF PG_DEBUG <> 0 THEN
2350: oe_debug_pub.add ('ED ('||x3||') = ' ||to_char(club_tab_arr(x3).eff_dt,'DD-MON-YY HH24:MI:SS')||
2351: ' ---- DD ('||x3||') = '|| to_char(club_tab_arr(x3).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2352: END IF;
2353: end loop;
2354: end if;
2369: if club_tab_arr.count > 0 then
2370: for x22 in club_tab_arr.FIRST..club_tab_arr.LAST
2371: loop
2372: IF PG_DEBUG <> 0 THEN
2373: oe_debug_pub.add ('ED ('||x22||') = ' ||to_char(club_tab_arr(x22).eff_dt,'DD-MON-YY HH24:MI:SS')||
2374: ' ---- DD ('||x22||') = '|| to_char(club_tab_arr(x22).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2375: END IF;
2376: end loop;
2377: end if;
2383: dbg_dis_date := d1.dis_date;
2384: dbg_qty := d1.cmp_qty;
2385:
2386: IF PG_DEBUG <> 0 THEN
2387: 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);
2388: END IF;
2389:
2390: end loop;
2391:
2400:
2401:
2402:
2403: IF PG_DEBUG <> 0 THEN
2404: oe_debug_pub.add ('checking for club comp error ', 1 ) ;
2405: END IF;
2406:
2407:
2408:
2451: end loop;
2452:
2453:
2454: IF PG_DEBUG <> 0 THEN
2455: oe_debug_pub.add( 'Going to Raise CTO_CLUB_COMP_ERROR');
2456: oe_debug_pub.add( 'will not populated message CTO_CLUB_COMP_ERROR');
2457: END IF;
2458:
2459: select segment1 into
2452:
2453:
2454: IF PG_DEBUG <> 0 THEN
2455: oe_debug_pub.add( 'Going to Raise CTO_CLUB_COMP_ERROR');
2456: oe_debug_pub.add( 'will not populated message CTO_CLUB_COMP_ERROR');
2457: END IF;
2458:
2459: select segment1 into
2460: l_model_name
2515: if club_tab_arr.count > 0 then
2516: for x5 in club_tab_arr.FIRST..club_tab_arr.LAST
2517: loop
2518: IF PG_DEBUG <> 0 THEN
2519: oe_debug_pub.add ('ED (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).eff_dt,'DD-MON-YY HH24:MI:SS')||
2520: ' -- DD (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).dis_dt,'DD-MON-YY HH24:MI:SS')||
2521: ' -- Qty (' ||x5|| ') = ' ||club_tab_arr(x5).qty,1);
2522: END IF;
2523: end loop;
2730: -- Get item sequence increment
2731: --
2732: p_seq_increment := fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT');
2733: IF PG_DEBUG <> 0 THEN
2734: oe_debug_pub.add('create_bom_data_ml: ' || 'Item Seq Increment::'||to_char(p_seq_increment), 1);
2735: END IF;
2736:
2737: --
2738: -- update item_num of top model
2739: --
2740: p_item_num := p_item_num + p_seq_increment;
2741:
2742: IF PG_DEBUG <> 0 THEN
2743: oe_debug_pub.add('create_bom_data_ml: ' || 'p_item_num::'||to_char(p_item_num), 2);
2744: END IF;
2745:
2746: update bom_inventory_comps_interface
2747: set item_num = p_item_num
2747: set item_num = p_item_num
2748: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
2749:
2750: IF PG_DEBUG <> 0 THEN
2751: oe_debug_pub.add('create_bom_data_ml: ' || 'Updated model row::'||sql%rowcount, 2);
2752: END IF;
2753:
2754: p_item_num := p_item_num + p_seq_increment;
2755:
2752: END IF;
2753:
2754: p_item_num := p_item_num + p_seq_increment;
2755:
2756: oe_debug_pub.add('create_bom_data_ml: ' || 'config bill id ::'|| pConfigBillId , 2);
2757:
2758:
2759: --
2760: -- get bill_sequence_id of top model
2768: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
2769: and organization_id = pOrgId
2770: and alternate_bom_designator is null;
2771:
2772: oe_debug_pub.add('create_bom_data_ml: ' || 'common bill seq id ::'|| p_bill_seq_id , 2);
2773: --
2774: -- call update_item_num procedure with top model
2775: -- this will update item_num for the rest of the items
2776: --
2774: -- call update_item_num procedure with top model
2775: -- this will update item_num for the rest of the items
2776: --
2777: IF PG_DEBUG <> 0 THEN
2778: 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);
2779: END IF;
2780:
2781: update_item_num(
2782: p_bill_seq_id,
2794:
2795: exception
2796: when others then
2797:
2798: oe_debug_pub.add('create_bom_data_ml: ' || SQLERRM ,2);
2799: oe_debug_pub.add('create_bom_data_ml: ' || SQLCODE ,2);
2800:
2801: end ;
2802:
2795: exception
2796: when others then
2797:
2798: oe_debug_pub.add('create_bom_data_ml: ' || SQLERRM ,2);
2799: oe_debug_pub.add('create_bom_data_ml: ' || SQLCODE ,2);
2800:
2801: end ;
2802:
2803:
2801: end ;
2802:
2803:
2804:
2805: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2806: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2807: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2808: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2809: /*-------------------------------------------+
2802:
2803:
2804:
2805: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2806: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2807: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2808: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2809: /*-------------------------------------------+
2810:
2803:
2804:
2805: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2806: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2807: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2808: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2809: /*-------------------------------------------+
2810:
2811: Load BOM_bill_of_materials
2804:
2805: oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2806: oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2807: oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2808: oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2809: /*-------------------------------------------+
2810:
2811: Load BOM_bill_of_materials
2812: +-------------------------------------------*/
2810:
2811: Load BOM_bill_of_materials
2812: +-------------------------------------------*/
2813: IF PG_DEBUG <> 0 THEN
2814: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2815: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2816: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2817: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2818: END IF;
2811: Load BOM_bill_of_materials
2812: +-------------------------------------------*/
2813: IF PG_DEBUG <> 0 THEN
2814: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2815: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2816: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2817: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2818: END IF;
2819:
2812: +-------------------------------------------*/
2813: IF PG_DEBUG <> 0 THEN
2814: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2815: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2816: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2817: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2818: END IF;
2819:
2820:
2813: IF PG_DEBUG <> 0 THEN
2814: oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2815: oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2816: oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2817: oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2818: END IF;
2819:
2820:
2821:
2830:
2831: exception
2832: when others then
2833: IF PG_DEBUG <> 0 THEN
2834: oe_debug_pub.add('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
2835: oe_debug_pub.add('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
2836: g_structure_type_id := 1;
2837:
2838: END IF;
2831: exception
2832: when others then
2833: IF PG_DEBUG <> 0 THEN
2834: oe_debug_pub.add('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
2835: oe_debug_pub.add('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
2836: g_structure_type_id := 1;
2837:
2838: END IF;
2839:
2841:
2842:
2843:
2844: IF PG_DEBUG <> 0 THEN
2845: oe_debug_pub.add('create_bom_data_ml: ' || 'structure_type_id is ' || g_structure_type_id ,2);
2846: END IF;
2847:
2848: end if ;
2849:
2949: and b.organization_id = pOrgId
2950: and b.alternate_bom_designator is NULL;
2951:
2952: IF PG_DEBUG <> 0 THEN
2953: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1 );
2954: END IF;
2955:
2956: /*-----------------------------------------------+
2957: Load Bom_inventory_components
2956: /*-----------------------------------------------+
2957: Load Bom_inventory_components
2958: +----------------------------------------------*/
2959: IF PG_DEBUG <> 0 THEN
2960: oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
2961: END IF;
2962: lStmtNum := 310;
2963: xTableName := 'BOM_INVENTORY_COMPONENTS';
2964: insert into BOM_INVENTORY_COMPONENTS
3104: and msi.organization_id = pOrgId ;
3105:
3106:
3107: IF PG_DEBUG <> 0 THEN
3108: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3109: END IF;
3110:
3111:
3112:
3113: /*-----------------------------------------------+
3114: Populate Substitutes for Mandatory components
3115: +----------------------------------------------*/
3116: IF PG_DEBUG <> 0 THEN
3117: oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
3118: END IF;
3119: lStmtNum := 315;
3120: xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
3121:
3212:
3213:
3214:
3215: IF PG_DEBUG <> 0 THEN
3216: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3217: END IF;
3218:
3219:
3220:
3233: /* -------------------------------------------------------------------------+
3234: Insert into BOM_REFERENCE_DESIGNATORS table
3235: +--------------------------------------------------------------------------*/
3236: IF PG_DEBUG <> 0 THEN
3237: oe_debug_pub.add('create_bom_data_ml: ' || 'Before third insert into bom_reference_designators. ', 2);
3238: END IF;
3239: lStmtNum := 320;
3240: xTableName := 'BOM_REFERENCE_DESIGNATORS';
3241: insert into BOM_REFERENCE_DESIGNATORS
3312: and r.component_sequence_id = abs(ic.model_comp_seq_id) -- previously last_update_login
3313: and nvl(r.acd_type,0) <> 3;
3314:
3315: IF PG_DEBUG <> 0 THEN
3316: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3317: END IF;
3318:
3319:
3320:
3326: prev_comp_item_id := 0;
3327: for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
3328: loop
3329: IF PG_DEBUG <> 0 THEN
3330: oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
3331: ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1),1);
3332: END IF;
3333:
3334:
3362:
3363: -- insert into BOM_REFERENCE_DESIGNATORS for the corresponding model_comp_seq_id
3364: -- if it has not already been inserted.
3365: IF PG_DEBUG <> 0 THEN
3366: oe_debug_pub.add ('club_component_sequence_id is '||club_component_sequence_id, 1);
3367: END if;
3368: IF PG_DEBUG <> 0 THEN
3369: oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
3370: END if;
3365: IF PG_DEBUG <> 0 THEN
3366: oe_debug_pub.add ('club_component_sequence_id is '||club_component_sequence_id, 1);
3367: END if;
3368: IF PG_DEBUG <> 0 THEN
3369: oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
3370: END if;
3371: begin
3372: insert into BOM_REFERENCE_DESIGNATORS
3373: (
3440: and nvl(r.acd_type,0) <> 3;
3441: exception
3442: when others then
3443: IF PG_DEBUG <> 0 THEN
3444: oe_debug_pub.add ('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
3445: END IF;
3446: end;
3447: IF PG_DEBUG <> 0 THEN
3448: oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
3444: oe_debug_pub.add ('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
3445: END IF;
3446: end;
3447: IF PG_DEBUG <> 0 THEN
3448: oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
3449: END if;
3450: end loop; -- 3985173 : end of club_comp_seq cursor loop
3451: prev_comp_item_id := component_item_id_arr(x1); -- 3985173
3452:
3459: end loop;
3460: end if;
3461:
3462: IF PG_DEBUG <> 0 THEN
3463: oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3464: END IF;
3465: -- end 3674833
3466:
3467:
3479: if CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'C' then
3480: -- Call Custom API with details..
3481:
3482: IF PG_DEBUG <> 0 THEN
3483: oe_debug_pub.add ('Prepare data for calling custom hook...');
3484: END IF;
3485:
3486: DECLARE
3487: cursor ctg is
3503: loop
3504: l_catalog_dtls(i).cat_element_name := rec.element_name;
3505: l_catalog_dtls(i).cat_element_value := NULL;
3506: IF PG_DEBUG <> 0 THEN
3507: oe_debug_pub.add ('l_catalog_dtls('||i||').cat_element_name = '||
3508: rec.element_name);
3509: END IF;
3510: i := i+1;
3511: end loop;
3517: l_params.p_item_id := pConfigId;
3518: l_params.p_org_id := pOrgId;
3519:
3520: IF PG_DEBUG <> 0 THEN
3521: oe_debug_pub.add ('Parameter passed: l_params.p_item_id = '||l_params.p_item_id ||
3522: '; l_params.p_org_id = '||l_params.p_org_id );
3523: END IF;
3524:
3525: CTO_CUSTOM_CATALOG_DESC.user_catalog_desc (
3528: x_return_status => l_return_status);
3529:
3530: if( l_return_status = FND_API.G_RET_STS_ERROR ) then
3531: IF PG_DEBUG <> 0 THEN
3532: oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned exp error');
3533: END IF;
3534: RAISE FND_API.G_EXC_ERROR ;
3535:
3536: elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
3534: RAISE FND_API.G_EXC_ERROR ;
3535:
3536: elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
3537: IF PG_DEBUG <> 0 THEN
3538: oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned unexp error');
3539: END IF;
3540: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3541:
3542: end if ;
3542: end if ;
3543:
3544: if l_catalog_dtls.count <> original_count then
3545: IF PG_DEBUG <> 0 THEN
3546: oe_debug_pub.add ('Custom hook did not return same number of elements.'||
3547: 'Original_count='||original_count||
3548: 'New count = '||l_catalog_dtls.count);
3549: END IF;
3550: raise FND_API.G_EXC_ERROR;
3553: for k in l_catalog_dtls.first..l_catalog_dtls.last
3554: loop
3555: if l_catalog_dtls(k).cat_element_value is not null then
3556: IF PG_DEBUG <> 0 THEN
3557: oe_debug_pub.add ('l_catalog_dtls('||k||').cat_element_name = '||
3558: l_catalog_dtls(k).cat_element_name||
3559: '; l_catalog_dtls('||k||').cat_element_value = '||
3560: l_catalog_dtls(k).cat_element_value);
3561: END IF;
3566: set i.element_value = l_catalog_dtls(k).cat_element_value
3567: where i.inventory_item_id = pConfigId
3568: and i.element_name = l_catalog_dtls(k).cat_element_name;
3569: IF PG_DEBUG <> 0 THEN
3570: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3571: END IF;
3572:
3573: end if;
3574: end loop;
3579:
3580: elsif CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'Y' then
3581: lStmtNum := 332;
3582: IF PG_DEBUG <> 0 THEN
3583: oe_debug_pub.add ('Std feature : Rollup lower level model catalog desc to top level');
3584: END IF;
3585: update MTL_DESCR_ELEMENT_VALUES i
3586: set i.element_value =
3587: ( select /*+ ORDERED */
3603: and v.element_name = i.element_name
3604: )
3605: where i.inventory_item_id = pConfigId;
3606: IF PG_DEBUG <> 0 THEN
3607: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3608: END IF;
3609: else
3610:
3611: lStmtNum := 333;
3609: else
3610:
3611: lStmtNum := 333;
3612: IF PG_DEBUG <> 0 THEN
3613: oe_debug_pub.add ('Std feature : DO NOT Rollup lower level model catalog desc to top level');
3614: END IF;
3615: update MTL_DESCR_ELEMENT_VALUES i
3616: set i.element_value =
3617: ( select /*+ ORDERED */
3647: -- end bugfix 2590966
3648: )
3649: where i.inventory_item_id = pConfigId;
3650: IF PG_DEBUG <> 0 THEN
3651: oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3652: END IF;
3653: end if;
3654:
3655: -- end bugfix 2765635
3664: status := bmlupid_update_item_desc(pConfigid,
3665: pOrgId,
3666: xErrorMessage);
3667: IF PG_DEBUG <> 0 THEN
3668: oe_debug_pub.add('create_bom_data_ml: ' || 'bmlupid_update_item_desc returned ' || status,1 );
3669: END IF;
3670:
3671: if status <> 0 then
3672: raise FND_API.G_EXC_ERROR;
3724: when FND_API.G_EXC_ERROR then
3725: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3726: xMessageName := 'CTO_CREATE_BOM_ERROR';
3727: IF PG_DEBUG <> 0 THEN
3728: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xErrorMessage, 1);
3729: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3730: END IF;
3731:
3732: -- Sushant Fixed bug #3374548
3725: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3726: xMessageName := 'CTO_CREATE_BOM_ERROR';
3727: IF PG_DEBUG <> 0 THEN
3728: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xErrorMessage, 1);
3729: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3730: END IF;
3731:
3732: -- Sushant Fixed bug #3374548
3733: /* Clean up bom_inventory_comps_interface */
3739:
3740: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
3741: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3742: IF PG_DEBUG <> 0 THEN
3743: oe_debug_pub.add('Unexpected Error: ' || xErrorMessage);
3744: END IF;
3745:
3746: -- Sushant Fixed bug #3374548
3747: /* Clean up bom_inventory_comps_interface */
3753: WHEN OTHERS THEN
3754: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3755: xMessageName := 'CTO_CREATE_BOM_ERROR';
3756: IF PG_DEBUG <> 0 THEN
3757: oe_debug_pub.add('create_bom_data_ml: ' || 'other Error: ' || xErrorMessage, 1);
3758: END IF;
3759:
3760: -- Sushant Fixed bug #3374548
3761: /* Clean up bom_inventory_comps_interface */
3796: FOR v_update_item_num IN c_update_item_num(p_parent_bill_seq_id)
3797: LOOP
3798:
3799: IF PG_DEBUG <> 0 THEN
3800: oe_debug_pub.add('update_item_num: ' || 'In update loop for item '||to_char(v_update_item_num.component_item_id), 2);
3801: END IF;
3802:
3803: --
3804: -- update item_num of child of this model
3807: set item_num = p_item_num
3808: where current of c_update_item_num;
3809:
3810: IF PG_DEBUG <> 0 THEN
3811: 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);
3812: END IF;
3813:
3814: p_item_num := p_item_num + p_seq_increment;
3815:
3825: and organization_id = p_org_id
3826: and alternate_bom_designator is null;
3827:
3828: IF PG_DEBUG <> 0 THEN
3829: 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);
3830: END IF;
3831:
3832: update_item_num(
3833: p_bill_seq_id,
3837:
3838: EXCEPTION
3839: WHEN NO_DATA_FOUND THEN
3840: IF PG_DEBUG <> 0 THEN
3841: oe_debug_pub.add('update_item_num: ' || 'This component '||to_char(v_update_item_num.component_item_id)||' does not have a BOM', 2);
3842: END IF;
3843:
3844: END;
3845:
4199:
4200: lCnt := sql%rowcount ;
4201:
4202: IF PG_DEBUG <> 0 THEN
4203: 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);
4204: END IF;
4205:
4206:
4207:
4213: select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4214: count(*) into v_zero_qty_count from bom_explosion_temp
4215: where bill_sequence_id = pConfigBillId and component_quantity = 0 ;
4216:
4217: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4218:
4219: if( v_zero_qty_count > 0 ) then
4220:
4221: oe_debug_pub.add( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4217: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4218:
4219: if( v_zero_qty_count > 0 ) then
4220:
4221: oe_debug_pub.add( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4222:
4223:
4224: select concatenated_segments into v_model_item_name
4225: from mtl_system_items_kfv
4410:
4411: lCnt := sql%rowcount ;
4412:
4413: IF PG_DEBUG <> 0 THEN
4414: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'First -- Inserted in BE Temp ' || lCnt ||' Incl Item rows with bill seq id as '|| pConfigBillId,1);
4415: END IF;
4416:
4417:
4418:
4572: , r2.basis_type /* LBM project */
4573: );
4574: lCnt := sql%rowcount ;
4575: IF PG_DEBUG <> 0 THEN
4576: 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);
4577: END IF;
4578: END LOOP;
4579:
4580:
4723: where bill_sequence_id = pConfigBillId;
4724:
4725: lCnt := sql%rowcount ;
4726: IF PG_DEBUG <> 0 THEN
4727: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Final - Inserted in BIC Interface ' || lCnt ||' rows from BET',1);
4728: END IF;
4729:
4730:
4731:
4782: if( v_t_overlap_src_op_seq_num.count > 0 ) then
4783: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
4784: loop
4785: IF PG_DEBUG <> 0 THEN
4786: oe_debug_pub.add (' The following components have overlapping dates ', 1);
4787: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
4788: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
4789: /*
4790: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
4783: for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
4784: loop
4785: IF PG_DEBUG <> 0 THEN
4786: oe_debug_pub.add (' The following components have overlapping dates ', 1);
4787: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
4788: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
4789: /*
4790: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
4791: ' ' || v_t_overlap_src_op_seq_num(i) ||
4786: oe_debug_pub.add (' The following components have overlapping dates ', 1);
4787: oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
4788: ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
4789: /*
4790: oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
4791: ' ' || v_t_overlap_src_op_seq_num(i) ||
4792: ' ' || v_t_overlap_src_eff_date(i) ||
4793: ' ' || v_t_overlap_src_disable_date(i) ||
4794: ' OVERLAPS ' ||
4845: when no_data_found then
4846: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber);
4847: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4848: IF PG_DEBUG <> 0 THEN
4849: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: No data found in inherit_op_seq_ml. Returning 0 ', 1);
4850: END IF;
4851: return(0);
4852:
4853: when FND_API.G_EXC_ERROR then
4853: when FND_API.G_EXC_ERROR then
4854: xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
4855: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4856: IF PG_DEBUG <> 0 THEN
4857: oe_debug_pub.add('create_bom_data_ml: ' || 'Error: ' || xErrorMessage, 1);
4858: END IF;
4859: return(0);
4860:
4861: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
4861: when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
4862: xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
4863: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4864: IF PG_DEBUG <> 0 THEN
4865: oe_debug_pub.add('Error: ' || xErrorMessage);
4866: END IF;
4867: return(0);
4868:
4869: when others then
4869: when others then
4870: xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber)||':'||substrb(sqlerrm,1,150);
4871: xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4872: IF PG_DEBUG <> 0 THEN
4873: oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: Others excpn : '||sqlerrm, 1);
4874: END IF;
4875: return (0);
4876: END inherit_op_seq_ml;
4877:
4894:
4895: xBillId := 0;
4896:
4897: IF PG_DEBUG <> 0 THEN
4898: oe_debug_pub.add('check_bom: ' || 'before check_bom sql::xBillId:: '||to_char(xBillId ), 2);
4899: END IF;
4900:
4901:
4902: select bill_sequence_id
4906: and organization_id = pOrgId
4907: and alternate_bom_designator is null;
4908:
4909: IF PG_DEBUG <> 0 THEN
4910: oe_debug_pub.add('check_bom: ' || 'after check_bom sql::xBillId:: '||to_char(xBillId )||'returning 1', 2);
4911: END IF;
4912:
4913: return(1);
4914:
4915: exception
4916:
4917: when no_data_found then
4918: IF PG_DEBUG <> 0 THEN
4919: oe_debug_pub.add ('check_bom: ' || 'NDF exception for Check BOM::item id '||to_char(pItemId), 1);
4920: END IF;
4921: return(0);
4922:
4923: when others then
4921: return(0);
4922:
4923: when others then
4924: IF PG_DEBUG <> 0 THEN
4925: oe_debug_pub.add ('check_bom: ' || 'Others exception for Check BOM::item id '||to_char(pItemId), 1);
4926: END IF;
4927: cto_msg_pub.cto_message('BOM', 'CTO_CREATE_BOM_ERROR');
4928: return(0);
4929:
4949:
4950: begin
4951:
4952: IF PG_DEBUG <> 0 THEN
4953: oe_debug_pub.add('get_model_lead_time: ' || 'Getting Lead Time for Model: ' || to_char(pModelId), 2);
4954: END IF;
4955: lStmtNum := 100;
4956:
4957: select (ceil(nvl(msi.fixed_lead_time,0)
4961: where inventory_item_id = pModelId
4962: and organization_id = pOrgId;
4963:
4964: IF PG_DEBUG <> 0 THEN
4965: oe_debug_pub.add('get_model_lead_time: ' || 'Lead Time: ' || to_char(pLeadtime), 2);
4966: END IF;
4967:
4968: return 1;
4969:
5025: IF profile_check%NOTFOUND THEN
5026: profile_setting := 'Y';
5027: END IF;
5028: IF PG_DEBUG <> 0 THEN
5029: oe_debug_pub.add ('bmlggpn_get_group_name: ' || ' USE_NAME_ICG_DESC :'|| profile_setting, 2);
5030: END IF;
5031:
5032: if profile_setting = 'Y' then
5033:
5093: INV_GRP_ERROR exception;
5094: begin
5095: lStmtNum := 280;
5096: IF PG_DEBUG <> 0 THEN
5097: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5098: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5099: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5100: END IF;
5101:
5094: begin
5095: lStmtNum := 280;
5096: IF PG_DEBUG <> 0 THEN
5097: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5098: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5099: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5100: END IF;
5101:
5102: select concatenated_segment_delimiter into delimiter
5095: lStmtNum := 280;
5096: IF PG_DEBUG <> 0 THEN
5097: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5098: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5099: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5100: END IF;
5101:
5102: select concatenated_segment_delimiter into delimiter
5103: from fnd_id_flex_structures
5110: where inventory_item_id = item_id
5111: and organization_id = org_id;
5112:
5113: IF PG_DEBUG <> 0 THEN
5114: oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item_catalog_group_id : ' || group_id,2);
5115: END IF;
5116: idx := 0;
5117: cat_value := '';
5118: open cc;
5133: cat_value := cat_value || SUBSTRB(delimiter || e_value,1,
5134: 240-LENGTHB(cat_value));
5135: end if;
5136: IF PG_DEBUG <> 0 THEN
5137: oe_debug_pub.add('bmlupid_update_item_desc: ' || 'cat_value :' || cat_value,1);
5138: END IF;
5139: idx := idx + 1;
5140: end loop;
5141: close cc;
5157: exception
5158: when INV_GRP_ERROR then
5159: err_buf := 'CTOCBOMB: Invalid catalog group for the item ' || item_id || ' status:' || status;
5160: IF PG_DEBUG <> 0 THEN
5161: oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5162: END IF;
5163: cto_msg_pub.cto_message ('BOM', 'CTO_INVALID_CATALOG_GRP');
5164: return(1);
5165:
5165:
5166: when OTHERS then
5167: err_buf := 'CTOCBOMB: ' || lStmtNum ||substrb(SQLERRM,1,150);
5168: IF PG_DEBUG <> 0 THEN
5169: oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5170: END IF;
5171: cto_msg_pub.cto_message ('BOM', 'CTO_CREATE_BOM_ERROR');
5172: return(1);
5173:
5239: -- Get the default adminstrator value from Workflow Attributes.
5240: lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOEXCP',
5241: ItemKey => litem_key,
5242: aname => 'WF_ADMINISTRATOR');
5243: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
5244:
5245: else
5246:
5247: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
5243: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
5244:
5245: else
5246:
5247: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
5248:
5249: END IF;
5250:
5251: lstmt_num := 30 ;
5312: lstmt_num := 90 ;
5313: wf_engine.StartProcess(itemtype=>'CTOEXCP',ItemKey=>litem_key);
5314:
5315:
5316: oe_debug_pub.add( ' done till stmt ' || lstmt_num ) ;
5317:
5318:
5319:
5320: exception
5319:
5320: exception
5321: when others then
5322:
5323: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
5324: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
5325:
5326:
5327: end send_oid_notification ;
5320: exception
5321: when others then
5322:
5323: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
5324: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
5325:
5326:
5327: end send_oid_notification ;
5328: