[Home] [Help]
393: l_ato_line_id number;
394: i number;
395: begin
396: IF PG_DEBUG <> 0 THEN
397: oe_debug_pub.add('update_wt_vol_tbl: p_tbl_type:' || p_tbl_type);
398: oe_debug_pub.add('update_wt_vol_tbl: p_line_id:' || p_line_id);
399: END IF;
400:
401: if p_tbl_type = 1 then
394: i number;
395: begin
396: IF PG_DEBUG <> 0 THEN
397: oe_debug_pub.add('update_wt_vol_tbl: p_tbl_type:' || p_tbl_type);
398: oe_debug_pub.add('update_wt_vol_tbl: p_line_id:' || p_line_id);
399: END IF;
400:
401: if p_tbl_type = 1 then
402: --Mark all parents of this line as not eligible for processing.
422: i := g_wt_tbl.first;
423: while i is not null
424: loop
425: IF PG_DEBUG <> 0 THEN
426: oe_debug_pub.add('update_wt_vol_tbl: Index' || i || ':' || g_wt_tbl(i));
427: END IF;
428: i := g_wt_tbl.next(i);
429: end loop;
430: END IF;
453: i := g_vol_tbl.first;
454: while i is not null
455: loop
456: IF PG_DEBUG <> 0 THEN
457: oe_debug_pub.add('update_wt_vol_tbl: Index' || i || ':' || g_vol_tbl(i));
458: END IF;
459: i := g_vol_tbl.next(i);
460: end loop;
461: END IF;
620: end if ;
621:
622:
623:
624: oe_debug_pub.add( ' ************* EVALUATE_ATP_ATTRIBUTES *** p_atp_flag ' || p_atp_flag
625: || ' p_atp_components_flag ' || p_atp_components_flag
626: || ' result ' || ' g_atp_flag ' || g_atp_flag
627: || ' v_atp_components_flag ' || v_atp_components_flag , 1 ) ;
628:
664:
665: exception
666: when no_data_found then
667: IF PG_DEBUG <> 0 THEN
668: oe_debug_pub.add('get_cost_group: ' || 'ERROR: Could not fetch the cost_group_id from pjm_project_parameters (NDF)',1);
669: END IF;
670: return(0);
671:
672: end get_cost_group;
782: and oel.inventory_item_id = wfs.primary_item_id
783: and rownum = 1;
784:
785: IF PG_DEBUG <> 0 THEN
786: oe_debug_pub.add ('flow_schedule_exists: ' || 'Flow Schedule Exists!', 1);
787: END IF;
788:
789: return(true); -- Flow Schedule exists
790:
791: exception
792: when no_data_found then
793:
794: IF PG_DEBUG <> 0 THEN
795: oe_debug_pub.add ('flow_schedule_exists: ' || 'Flow Schedules does not exist ', 1);
796: END IF;
797: return(false); -- Flow Schedule does not exist
798:
799: when others then
845: and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_wip
846: and rownum = 1;
847:
848: IF PG_DEBUG <> 0 THEN
849: oe_debug_pub.add ('work_orders_exist: ' || 'Work orders exist! Call wip_holds api', 2);
850: END IF;
851:
852: return(true); -- reservation exists
853:
854: exception
855: when no_data_found then
856:
857: IF PG_DEBUG <> 0 THEN
858: oe_debug_pub.add ('work_orders_exist: ' || 'Work orders do not exist, do not call wip holds api ', 2);
859: END IF;
860: return(false); -- Reservation does not exist
861:
862: when others then
1036: and wdd.released_status in ('Y', 'C'); -- Staged [Y], Closed [C]
1037:
1038: if (lShipConfirmed = 'TRUE' ) then
1039: IF PG_DEBUG <> 0 THEN
1040: oe_debug_pub.add ('delink_item: ' || 'Error : Config Line has been ship confirmed. Can not delink',1);
1041: END IF;
1042: xMessageName := 'CTO_DELINK_SHIPPING_ERROR';
1043: raise FND_API.G_EXC_ERROR;
1044: end if;
1045:
1046: EXCEPTION
1047: when no_data_found then
1048: IF PG_DEBUG <> 0 THEN
1049: oe_debug_pub.add ('delink_item: ' || 'Config Line does not exist in shipping yet. Delink allowed',2);
1050: END IF;
1051:
1052: END;
1053: -- end bugfix 2001824
1055:
1056: lStmtNumber := 510;
1057: lSourceCode := fnd_profile.value('ONT_SOURCE_CODE');
1058: IF PG_DEBUG <> 0 THEN
1059: oe_debug_pub.add ('delink_item: ' || 'lSourceCode is '||lSourceCode, 2);
1060: END IF;
1061:
1062: --
1063: -- Get header id, other info for calling Reservation API
1089: and msi.organization_id = oel.ship_from_org_id
1090: and msi.base_item_id is not NULL;
1091:
1092: IF PG_DEBUG <> 0 THEN
1093: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1094:
1095: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1096:
1097: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1091:
1092: IF PG_DEBUG <> 0 THEN
1093: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1094:
1095: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1096:
1097: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1098:
1099: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1093: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1094:
1095: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1096:
1097: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1098:
1099: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1100:
1101: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1095: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1096:
1097: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1098:
1099: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1100:
1101: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1102: END IF;
1103:
1097: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1098:
1099: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1100:
1101: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1102: END IF;
1103:
1104: -- begin bugfix 2745590 : Check for routing flag : 1 = Flow Routing
1105:
1116: when no_data_found then
1117: lcfm_routing_flag :=2;
1118: end;
1119: IF PG_DEBUG <> 0 THEN
1120: oe_debug_pub.add ('delink_item: Routing flag ( if 1, flow routing ) = '||to_char(lcfm_routing_flag), 2);
1121: END IF;
1122:
1123: -- Check if flow schedule exist
1124: -- 2784045 : This 'if' condition is decoupled from flow_sch_exist to make the code more efficient
1130: and primary_item_id = pConfigid
1131: and demand_source_type = 2 ;
1132:
1133: IF PG_DEBUG <> 0 THEN
1134: oe_debug_pub.add ('delink_item: Flow Schedule exist ( if > 0 ) = '||to_char(lflow_schedule_exist), 2);
1135: END IF;
1136:
1137: -- Call MRP API unlink_order_line if flow schedule exist and routing is flow_routing
1138: -- 2784045 if ( lcfm_routing_flag = 1 ) and ( lflow_schedule_exist > 0 ) then
1148: );
1149:
1150: if l_status = FND_API.G_RET_STS_ERROR then
1151: IF PG_DEBUG <> 0 THEN
1152: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in MRP_Flow_Schedule_PUB.unlink_order_line with expected error.', 1);
1153: END IF;
1154:
1155: /* Bugfix 2454788 */
1156: raise FND_API.G_EXC_ERROR;
1156: raise FND_API.G_EXC_ERROR;
1157:
1158: elsif l_status = FND_API.G_RET_STS_UNEXP_ERROR then
1159: IF PG_DEBUG <> 0 THEN
1160: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in MRP_Flow_Schedule_PUB.unlink_order_line with unexpected error.', 1);
1161: END IF;
1162: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1163: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1164:
1163: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1164:
1165: elsif l_status = fnd_api.g_ret_sts_success then
1166: IF PG_DEBUG <> 0 THEN
1167: oe_debug_pub.add ('delink_item: ' || 'Removed config line reference from flow schedule', 2);
1168: END IF;
1169: -- Check for l_resv_flag. If it is "N" , set it to "Y" .
1170: -- The Flag is set to Yes, so that we can call the notification later.
1171: If l_resv_flag = 'N' then
1212: xMessageName,
1213: xTableName)
1214: then
1215: IF PG_DEBUG <> 0 THEN
1216: oe_debug_pub.add ('delink_item: ' || 'work orders exist', 2);
1217: END IF;
1218:
1219: -- Added by Renga Kannan on 03/13/2001
1220: -- The Flag is to Yes, So that we can call the notification later in this
1230: x_msg_data => l_msg_data);
1231:
1232: if l_return_status = FND_API.G_RET_STS_ERROR then
1233: IF PG_DEBUG <> 0 THEN
1234: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in wip_so_reservations.respond_to_change_order with expected error.', 1);
1235: END IF;
1236:
1237: /* Bugfix 2454788 */
1238: raise FND_API.G_EXC_ERROR;
1238: raise FND_API.G_EXC_ERROR;
1239:
1240: elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1241: IF PG_DEBUG <> 0 THEN
1242: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in wip_so_reservations.respond_to_change_order with unexpected error.', 1);
1243: END IF;
1244: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1245: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1246: end if;
1246: end if;
1247:
1248: else
1249: IF PG_DEBUG <> 0 THEN
1250: oe_debug_pub.add ('delink_item: ' || 'WOS does not exist', 2);
1251: END IF;
1252: l_return_status := FND_API.G_RET_STS_SUCCESS;
1253: end if;
1254:
1252: l_return_status := FND_API.G_RET_STS_SUCCESS;
1253: end if;
1254:
1255: IF PG_DEBUG <> 0 THEN
1256: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned' || l_return_status, 2);
1257:
1258: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned with msg : ' || l_msg_data, 2);
1259: END IF;
1260:
1254:
1255: IF PG_DEBUG <> 0 THEN
1256: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned' || l_return_status, 2);
1257:
1258: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned with msg : ' || l_msg_data, 2);
1259: END IF;
1260:
1261: lStmtNumber := 518;
1262: if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1278: , p_serial_number => l_dummy_sn
1279: );
1280:
1281: IF PG_DEBUG <> 0 THEN
1282: oe_debug_pub.add ('delink_item: ' || 'After deleting rsv, rsv_id = '||to_char(l_rsv.reservation_id)||', l_status = '||l_status, 2);
1283: END IF;
1284: IF l_status = fnd_api.g_ret_sts_success THEN
1285: IF PG_DEBUG <> 0 THEN
1286: oe_debug_pub.add ('delink_item: ' || 'reservations deleted', 2);
1282: oe_debug_pub.add ('delink_item: ' || 'After deleting rsv, rsv_id = '||to_char(l_rsv.reservation_id)||', l_status = '||l_status, 2);
1283: END IF;
1284: IF l_status = fnd_api.g_ret_sts_success THEN
1285: IF PG_DEBUG <> 0 THEN
1286: oe_debug_pub.add ('delink_item: ' || 'reservations deleted', 2);
1287: END IF;
1288: -- Added by Renga Kannan on 03/13/2001
1289: -- The Flag is to Yes, So that we can call the notification later in this
1290: -- Function.
1292:
1293: ELSE
1294: IF l_msg_count = 1 THEN
1295: IF PG_DEBUG <> 0 THEN
1296: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1297:
1298: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1299: END IF;
1300:
1294: IF l_msg_count = 1 THEN
1295: IF PG_DEBUG <> 0 THEN
1296: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1297:
1298: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1299: END IF;
1300:
1301: CLOSE reservations;
1302:
1305:
1306: ELSE
1307: FOR l_index IN 1..l_msg_count LOOP
1308: IF PG_DEBUG <> 0 THEN
1309: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1310:
1311: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1312: END IF;
1313: END LOOP;
1307: FOR l_index IN 1..l_msg_count LOOP
1308: IF PG_DEBUG <> 0 THEN
1309: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1310:
1311: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1312: END IF;
1313: END LOOP;
1314:
1315: CLOSE reservations;
1320: END IF;
1321: END LOOP;
1322: CLOSE reservations;
1323: IF PG_DEBUG <> 0 THEN
1324: oe_debug_pub.add ('delink_item: ' || 'After reservations cursor loop', 2);
1325: END IF;
1326:
1327: end if; /* end check l_return_status */
1328:
1368: lStmtNumber := 520;
1369:
1370: IF lcancel_flag <> 'Y' THEN
1371: IF PG_DEBUG <> 0 THEN
1372: oe_debug_pub.add('delink_item: ' || 'The model line is not cancelled',2);
1373: END IF;
1374:
1375:
1376: begin
1396:
1397:
1398: v_aps_version := msc_atp_global.get_aps_version ;
1399:
1400: oe_debug_pub.add('link_config: ' || 'APS version::'|| v_aps_version , 2);
1401:
1402: if( v_aps_version <> 10 ) then
1403:
1404: raise no_data_found ;
1404: raise no_data_found ;
1405:
1406: else
1407:
1408: oe_debug_pub.add('link_config: ' || 'Model Line workflow need not be at WAIT_FOR_CTO as APS version::'|| v_aps_version , 2);
1409:
1410: v_model_line_activity := 'NOT_AT_WAIT_FOR_CTO' ;
1411:
1412:
1420:
1421:
1422: ELSE
1423: IF PG_DEBUG <> 0 THEN
1424: oe_debug_pub.add('delink_item: ' || 'Model Line is cancelled..',2);
1425: END IF;
1426: END IF;
1427:
1428: IF PG_DEBUG <> 0 THEN
1425: END IF;
1426: END IF;
1427:
1428: IF PG_DEBUG <> 0 THEN
1429: oe_debug_pub.add ('delink_item: ' || 'calling OE_CONFIG_UTIL.delink_config',2);
1430: END IF;
1431:
1432: /*------------------------------------------------------+
1433: We are not calling prcess_order api directly because
1456: p_line_id => lConfigLineId,
1457: x_return_status => l_return_status);
1458:
1459: IF PG_DEBUG <> 0 THEN
1460: oe_debug_pub.add ('delink_item: ' || 'oe_config_util.delink_config returned' || l_return_status ,1);
1461: END IF;
1462:
1463: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1464:
1462:
1463: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1464:
1465: IF PG_DEBUG <> 0 THEN
1466: oe_debug_pub.add('delink_item: ' || 'Expected error in OE_CONFIG_UTIL.delink_config procedure....',3);
1467: END IF;
1468:
1469: /* Bugfix 2454788 */
1470: raise FND_API.G_EXC_ERROR;
1471:
1472:
1473: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1474: IF PG_DEBUG <> 0 THEN
1475: oe_debug_pub.add ('delink_item: ' || 'Unexpected Error in oe_config_util.delink_config.');
1476: END IF;
1477:
1478: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1479: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1497: -- update visible demand flag to 'Y' for model and options
1498: --
1499:
1500: IF PG_DEBUG <> 0 THEN
1501: oe_debug_pub.add ('delink_item: ' || 'delink : visible demand flag : before selecting options ', 2);
1502: END IF;
1503:
1504: lStmtNumber := 533;
1505:
1511: --- API was recommended by Rajeev Bellamkonda from OM team. Date 02/02/2001
1512:
1513:
1514: IF PG_DEBUG <> 0 THEN
1515: oe_debug_pub.add ('delink_item: ' || 'l_ml_mo_flag = '|| l_ml_mo_flag, 2);
1516: END IF;
1517:
1518:
1519:
1530: --
1531: FND_PROFILE.Get('ONT_BYPASS_ATP', l_bypassatp_profile_value);
1532:
1533: IF PG_DEBUG <> 0 THEN
1534: oe_debug_pub.add('delink_item: Value of the profile ONT_BYPASS_ATP :' ||l_bypassatp_profile_value ,3);
1535: END IF;
1536:
1537: IF (NVL(l_bypassatp_profile_value,'N') <> 'Y') THEN
1538: UPDATE OE_ORDER_LINES_ALL
1555: lStmtNumber := 540;
1556: -- What do we do in case of cancellation
1557:
1558:
1559: oe_debug_pub.add( 'model line status ' || v_model_line_activity , 1 ) ;
1560:
1561:
1562:
1563:
1590:
1591: v_aps_version := msc_atp_global.get_aps_version ;
1592:
1593: if( v_aps_version <> 10) then
1594: oe_debug_pub.add('delink_item: ' || 'APS version::'|| v_aps_version , 2);
1595:
1596: if( v_source_Type_code = 'INTERNAL') then
1597:
1598:
1620: end if ;
1621:
1622: else
1623:
1624: oe_debug_pub.add('delink_item: ' || 'APS version::'|| v_aps_version , 2);
1625:
1626: end if; /* reference to get_aps_version */
1627:
1628:
1651: and v_source_type_code = 'INTERNAL' )
1652: THEN
1653:
1654: IF PG_DEBUG <> 0 THEN
1655: oe_debug_pub.add('delink_item: ' || 'Delink Action performed, Need to call notification...',2);
1656: END IF;
1657: lStmtNumber := 600;
1658: SELECT Order_number
1659: INTO l_order_number
1662: WHERE B.line_id = pModelLineId
1663: AND A.Header_id = B.Header_Id;
1664:
1665: IF PG_DEBUG <> 0 THEN
1666: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1667:
1668: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1669:
1670: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1664:
1665: IF PG_DEBUG <> 0 THEN
1666: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1667:
1668: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1669:
1670: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1671: END IF;
1672:
1666: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1667:
1668: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1669:
1670: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1671: END IF;
1672:
1673: pchgtype(1).change_type := CTO_CHANGE_ORDER_PK.DELINK_ACTION;
1674: pchgtype(1).old_value := null;
1688:
1689:
1690: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1691: IF PG_DEBUG <> 0 THEN
1692: oe_debug_pub.add('delink_item: ' || 'Expected error in change_notify procedure....',1);
1693: END IF;
1694:
1695:
1696: /* Bugfix 2454788 */
1697: raise FND_API.G_EXC_ERROR;
1698:
1699: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1700: IF PG_DEBUG <> 0 THEN
1701: oe_debug_pub.add('delink_item: ' || 'Unexpected error occurred in change_notify...',1);
1702: END IF;
1703: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1704: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1705:
1733: (p_line_tbl => p_config_line_arr_delink,
1734: x_return_status => l_return_status_delink);
1735:
1736: IF PG_DEBUG <> 0 THEN
1737: oe_debug_pub.add ('delink_item: ' || 'Return status from MRP_OM_Interface - Delink: '||l_return_status_delink,2);
1738: END IF;
1739:
1740: if l_return_status_delink = FND_API.G_RET_STS_ERROR then
1741: IF PG_DEBUG <> 0 THEN
1738: END IF;
1739:
1740: if l_return_status_delink = FND_API.G_RET_STS_ERROR then
1741: IF PG_DEBUG <> 0 THEN
1742: oe_debug_pub.add ('delink_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with expected error.', 1);
1743: END IF;
1744:
1745: /* Bugfix 2454788 */
1746:
1747: raise FND_API.G_EXC_ERROR;
1748:
1749: elsif l_return_status_delink = FND_API.G_RET_STS_UNEXP_ERROR then
1750: IF PG_DEBUG <> 0 THEN
1751: oe_debug_pub.add ('delink_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with unexpected error.', 1);
1752: END IF;
1753:
1754:
1755: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1764: EXCEPTION
1765:
1766: when NO_DATA_FOUND then
1767: IF PG_DEBUG <> 0 THEN
1768: oe_debug_pub.add('delink_item: ' || 'ERROR: CTOCITMB delink::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
1769: END IF;
1770: if lStmtNumber = 520 then
1771: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1772: xErrorMessage := 'ERROR: CTOCITMB.delink:'||to_char(lStmtNumber)||
1787: p_msg_count => lCount,
1788: p_msg_data => xErrorMessage
1789: );
1790: IF PG_DEBUG <> 0 THEN
1791: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink:' || to_char(lStmtNumber)|| xErrorMessage,1);
1792: END IF;
1793:
1794:
1795: /* Bugfix 2454788 */
1799: fnd_message.parse_encoded( xErrorMessage , v_appl_name, v_error_name ) ;
1800:
1801:
1802: IF PG_DEBUG <> 0 THEN
1803: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: appl_name ' || v_appl_name ,1);
1804: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: error_name ' || v_error_name ,1);
1805: END IF;
1806:
1807:
1800:
1801:
1802: IF PG_DEBUG <> 0 THEN
1803: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: appl_name ' || v_appl_name ,1);
1804: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: error_name ' || v_error_name ,1);
1805: END IF;
1806:
1807:
1808: xErrorMessage := v_error_name;
1812:
1813:
1814: xMessageName := 'CTO_DELINK_ITEM_ERROR' ;
1815: IF PG_DEBUG <> 0 THEN
1816: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xErrorMessage is null ' ,1);
1817: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName is now ' || xMessageName,1);
1818: END IF;
1819:
1820:
1813:
1814: xMessageName := 'CTO_DELINK_ITEM_ERROR' ;
1815: IF PG_DEBUG <> 0 THEN
1816: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xErrorMessage is null ' ,1);
1817: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName is now ' || xMessageName,1);
1818: END IF;
1819:
1820:
1821: end if ;
1821: end if ;
1822:
1823: else
1824: IF PG_DEBUG <> 0 THEN
1825: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName was already set ' || xMessageName,1);
1826: END IF;
1827: end if;
1828:
1829: return(0);
1834: p_msg_count => lCount,
1835: p_msg_data => xErrorMessage
1836: );
1837: IF PG_DEBUG <> 0 THEN
1838: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink:' || to_char(lStmtNumber)|| xErrorMessage,1);
1839: END IF;
1840: return(0);
1841:
1842:
1841:
1842:
1843: when OTHERS then
1844: IF PG_DEBUG <> 0 THEN
1845: oe_debug_pub.add('delink_item: ' || 'delink::others::'||sqlerrm, 1);
1846: END IF;
1847: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1848: xErrorMessage := 'ERROR: CTOCITMB.delink:'||to_char(lStmtNumber)||
1849: ':' || substrb(sqlerrm,1,150);
1921:
1922:
1923:
1924: IF PG_DEBUG <> 0 THEN
1925: oe_debug_pub.add ('load_mandatory_comps: ' || ' load mandatory comps ' ,1);
1926: END IF;
1927:
1928: if( pReqDate is not null ) then
1929: IF PG_DEBUG <> 0 THEN
1926: END IF;
1927:
1928: if( pReqDate is not null ) then
1929: IF PG_DEBUG <> 0 THEN
1930: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
1931: END IF;
1932: else
1933: IF PG_DEBUG <> 0 THEN
1934: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed date null ' || pLineId ,1);
1930: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
1931: END IF;
1932: else
1933: IF PG_DEBUG <> 0 THEN
1934: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed date null ' || pLineId ,1);
1935: END IF;
1936: end if ;
1937:
1938:
1948: and oel.ordered_quantity > 0;
1949:
1950: If latpcompflag in ('N' , 'R' ) then -- bugfix 3779636
1951: IF PG_DEBUG <> 0 THEN
1952: oe_debug_pub.add ('load_mandatory_comps: ' || 'Exiting Load Mandatory Comps at model level as ATP Component flag is N or R ',1); -- bugfix 3779636
1953: END IF;
1954: return(1);
1955: else
1956:
2010: and oel.ordered_quantity > 0;
2011:
2012: if sql%rowcount = 0 then
2013: IF PG_DEBUG <> 0 THEN
2014: oe_debug_pub.add ('load_mandatory_comps: ' || 'Model Row not found',1);
2015: END IF;
2016: raise proc_error;
2017: end if;
2018:
2016: raise proc_error;
2017: end if;
2018:
2019: IF PG_DEBUG <> 0 THEN
2020: oe_debug_pub.add('load_mandatory_comps: ' || 'Model Rows : ' || sql%rowcount, 2);
2021: END IF;
2022:
2023: /*------------------------------------------------+
2024: Insert all oe_order_lines row for this model
2136: -- 3893281
2137: rowcount := SQL%ROWCOUNT;
2138:
2139: IF PG_DEBUG <> 0 THEN
2140: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || rowcount, 2);
2141: END IF;
2142:
2143: END LOOP;
2144:
2143: END LOOP;
2144:
2145: -- 3893281 following code is to debug BET at this point
2146: open debug_bet;
2147: oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
2148: loop
2149: fetch debug_bet
2150: into d_top_bseq_id,
2151: d_bseq_id,
2155: d_line_id,
2156: d_item;
2157: exit when debug_bet%NOTFOUND;
2158: IF PG_DEBUG <> 0 THEN
2159: oe_debug_pub.add (' Top Bill Seq id '||d_top_bseq_id|| ' Bill Seq Id '||d_bseq_id
2160: ||' Org '||d_org_id ||' Sort Order '||d_sort_order
2161: || ' Plan level '||d_plan_level||' Line Id '||d_line_id
2162: || ' Item '||d_item,1);
2163: END IF;
2167:
2168: v_max_level_number := level_number ; /* added by sushant to track max level number */
2169:
2170: IF PG_DEBUG <> 0 THEN
2171: oe_debug_pub.add ('load_mandatory_comps: ' || 'Group_id : '|| xGrpId, 2);
2172: END IF;
2173:
2174:
2175:
2195:
2196: when others then
2197: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2198: IF PG_DEBUG <> 0 THEN
2199: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2200:
2201: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR message : '||sqlerrm,1);
2202: END IF;
2203: return(0);
2197: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2198: IF PG_DEBUG <> 0 THEN
2199: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2200:
2201: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR message : '||sqlerrm,1);
2202: END IF;
2203: return(0);
2204:
2205: end ;
2210: +-----------------------------------------------------------*/
2211:
2212: level_number := 0;
2213: IF PG_DEBUG <> 0 THEN
2214: oe_debug_pub.add('load_mandatory_comps: ' || 'Explosion loop' , 2);
2215: END IF;
2216:
2217: << Explode_loop >>
2218: LOOP
2358:
2359: -- start bugfix 2425667
2360: lrowcount := SQL%ROWCOUNT;
2361: IF PG_DEBUG <> 0 THEN
2362: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || lrowcount, 2);
2363: END IF;
2364: -- oe_debug_pub.add('Row Count : ' || SQL%ROWCOUNT);
2365: -- end bugfix 2425667
2366:
2360: lrowcount := SQL%ROWCOUNT;
2361: IF PG_DEBUG <> 0 THEN
2362: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || lrowcount, 2);
2363: END IF;
2364: -- oe_debug_pub.add('Row Count : ' || SQL%ROWCOUNT);
2365: -- end bugfix 2425667
2366:
2367:
2368: /* continue exploding atleast till max level number */
2433:
2434:
2435:
2436: IF PG_DEBUG <> 0 THEN
2437: oe_debug_pub.add ('load_mandatory_comps_pds: ' || ' load mandatory comps PDS ' ,1);
2438: END IF;
2439:
2440: if( pReqDate is not null ) then
2441: IF PG_DEBUG <> 0 THEN
2438: END IF;
2439:
2440: if( pReqDate is not null ) then
2441: IF PG_DEBUG <> 0 THEN
2442: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
2443: END IF;
2444: else
2445: IF PG_DEBUG <> 0 THEN
2446: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed date null ' || pLineId ,1);
2442: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
2443: END IF;
2444: else
2445: IF PG_DEBUG <> 0 THEN
2446: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed date null ' || pLineId ,1);
2447: END IF;
2448: end if ;
2449:
2450: lStmtNumber := 599;
2457: and oel.ordered_quantity > 0;
2458:
2459: If latpcompflag in ( 'N' , 'R' ) then -- 3779636
2460: IF PG_DEBUG <> 0 THEN
2461: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Exiting Load Mandatory Comps PDS at model level as ATP Component flag is N or R',1); -- 3779636
2462: END IF;
2463: return(1);
2464: else
2465:
2508: and oel.ordered_quantity > 0;
2509:
2510: if sql%rowcount = 0 then
2511: IF PG_DEBUG <> 0 THEN
2512: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Model Row not found',1);
2513: END IF;
2514: raise proc_error;
2515: end if;
2516:
2514: raise proc_error;
2515: end if;
2516:
2517: IF PG_DEBUG <> 0 THEN
2518: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'Model Rows : ' || sql%rowcount, 2);
2519: END IF;
2520:
2521: /*------------------------------------------------+
2522: Insert all oe_order_lines row for this model
2576:
2577: rowcount := SQL%ROWCOUNT;
2578:
2579: IF PG_DEBUG <> 0 THEN
2580: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Row Count : ' || rowcount, 2);
2581: END IF;
2582:
2583: END LOOP;
2584:
2584:
2585: v_max_level_number := level_number ;
2586:
2587: IF PG_DEBUG <> 0 THEN
2588: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Group_id : '|| xGrpId, 2);
2589: END IF;
2590:
2591:
2592: begin
2607:
2608: when others then
2609: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2610: IF PG_DEBUG <> 0 THEN
2611: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2612:
2613: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR message : '||sqlerrm,1);
2614: END IF;
2615: return(0);
2609: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2610: IF PG_DEBUG <> 0 THEN
2611: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2612:
2613: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR message : '||sqlerrm,1);
2614: END IF;
2615: return(0);
2616:
2617: end ;
2622: +-----------------------------------------------------------*/
2623:
2624: level_number := 0;
2625: IF PG_DEBUG <> 0 THEN
2626: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'Explosion loop' , 2);
2627: END IF;
2628:
2629: << Explode_loop >>
2630: LOOP
2757:
2758:
2759: lrowcount := SQL%ROWCOUNT;
2760: IF PG_DEBUG <> 0 THEN
2761: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Row Count : ' || lrowcount, 2);
2762: END IF;
2763:
2764:
2765:
2873:
2874:
2875:
2876: IF PG_DEBUG <> 0 THEN
2877: oe_debug_pub.add ('load_mandatory_components: ' || ' load mandatory components ' ,1);
2878: END IF;
2879:
2880: if( pReqDate is not null ) then
2881: IF PG_DEBUG <> 0 THEN
2878: END IF;
2879:
2880: if( pReqDate is not null ) then
2881: IF PG_DEBUG <> 0 THEN
2882: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
2883: END IF;
2884: else
2885: IF PG_DEBUG <> 0 THEN
2886: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index date null ' || p_model_index ,1);
2882: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
2883: END IF;
2884: else
2885: IF PG_DEBUG <> 0 THEN
2886: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index date null ' || p_model_index ,1);
2887: END IF;
2888: end if ;
2889:
2890:
2899:
2900:
2901: If latpcompflag in ( 'N' , 'R' ) then -- bugfix 3779636
2902: IF PG_DEBUG <> 0 THEN
2903: oe_debug_pub.add ('load_mandatory_components: ' || 'Exiting Load Mandatory Components at model level as ATP Component flag is N or R ',1); -- bugfix 3779636
2904: END IF;
2905: return(1);
2906:
2907: else
3086:
3087: rowcount := SQL%ROWCOUNT;
3088:
3089: IF PG_DEBUG <> 0 THEN
3090: oe_debug_pub.add ('load_mandatory_components: ' || 'Row Count : ' || rowcount, 2);
3091: END IF;
3092:
3093: END LOOP;
3094:
3093: END LOOP;
3094:
3095: -- 3893281 following code is to debug BET at this point
3096: open debug_bet;
3097: oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
3098: loop
3099: fetch debug_bet
3100: into d_top_bseq_id,
3101: d_bseq_id,
3105: d_line_id,
3106: d_item;
3107: exit when debug_bet%NOTFOUND;
3108: IF PG_DEBUG <> 0 THEN
3109: oe_debug_pub.add (' Top Bill Seq id '||d_top_bseq_id|| ' Bill Seq Id '||d_bseq_id
3110: ||' Org '||d_org_id ||' Sort Order '||d_sort_order
3111: || ' Plan level '||d_plan_level||' Line Id '||d_line_id
3112: || ' Item '||d_item,1);
3113: END IF;
3119:
3120: v_max_level_number := level_number ; /* change made by sushant to track max level number */
3121:
3122: IF PG_DEBUG <> 0 THEN
3123: oe_debug_pub.add ('load_mandatory_components: ' || 'Group_id : '|| xGrpId, 2);
3124: END IF;
3125:
3126: delete from bom_explosion_temp
3127: where group_id = lGroupid2;
3153: +-----------------------------------------------------------*/
3154:
3155: level_number := 0;
3156: IF PG_DEBUG <> 0 THEN
3157: oe_debug_pub.add('load_mandatory_components: ' || 'Explosion loop' , 2);
3158: END IF;
3159:
3160: << Explode_loop >>
3161: LOOP
3294: -- standard items
3295: and msi.pick_components_flag <> 'Y' ;
3296:
3297: IF PG_DEBUG <> 0 THEN
3298: oe_debug_pub.add('load_mandatory_components: ' || 'Row Count : ' || SQL%ROWCOUNT);
3299: END IF;
3300:
3301: /* continue exploding atleast till max level number */
3302: IF SQL%ROWCOUNT = 0 AND level_number >= v_max_level_number THEN
3357:
3358:
3359:
3360: IF PG_DEBUG <> 0 THEN
3361: oe_debug_pub.add ('load_mandatory_components_pds: ' || ' load mandatory components PDS ' ,1);
3362: END IF;
3363:
3364: if( pReqDate is not null ) then
3365: IF PG_DEBUG <> 0 THEN
3362: END IF;
3363:
3364: if( pReqDate is not null ) then
3365: IF PG_DEBUG <> 0 THEN
3366: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
3367: END IF;
3368: else
3369: IF PG_DEBUG <> 0 THEN
3370: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index date null ' || p_model_index ,1);
3366: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
3367: END IF;
3368: else
3369: IF PG_DEBUG <> 0 THEN
3370: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index date null ' || p_model_index ,1);
3371: END IF;
3372: end if ;
3373:
3374:
3381:
3382:
3383: If latpcompflag in ( 'N' , 'R' ) then -- 3779636
3384: IF PG_DEBUG <> 0 THEN
3385: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'Exiting Load Mandatory Components at model level as ATP Component flag is N or R',1); -- 3779636
3386: END IF;
3387: return(1);
3388:
3389: else
3531:
3532: rowcount := SQL%ROWCOUNT;
3533:
3534: IF PG_DEBUG <> 0 THEN
3535: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'Row Count : ' || rowcount, 2);
3536: END IF;
3537:
3538: END LOOP;
3539:
3541:
3542: v_max_level_number := level_number ;
3543:
3544: IF PG_DEBUG <> 0 THEN
3545: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'Group_id : '|| xGrpId, 2);
3546: END IF;
3547:
3548: delete from bom_explosion_temp
3549: where group_id = lGroupid2;
3571: +-----------------------------------------------------------*/
3572:
3573: level_number := 0;
3574: IF PG_DEBUG <> 0 THEN
3575: oe_debug_pub.add('load_mandatory_components_pds: ' || 'Explosion loop' , 2);
3576: END IF;
3577:
3578: << Explode_loop >>
3579: LOOP
3703: and msi.bom_item_type in (1,2) /*Model or Option Class */
3704: and msi.pick_components_flag <> 'Y' ;
3705:
3706: IF PG_DEBUG <> 0 THEN
3707: oe_debug_pub.add('load_mandatory_components_pds: ' || 'Row Count : ' || SQL%ROWCOUNT);
3708: END IF;
3709:
3710:
3711: IF SQL%ROWCOUNT = 0 AND level_number >= v_max_level_number THEN
3783:
3784: begin
3785:
3786: IF PG_DEBUG <> 0 THEN
3787: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Entered Get Mandatory Components ',1);
3788: END IF;
3789:
3790:
3791: -- Bugfix 3779636
3794:
3795: l_inv_ctp := fnd_profile.value('INV_CTP');
3796:
3797: IF PG_DEBUG <> 0 THEN
3798: oe_debug_pub.add('Get_Mandatory_Components: INV_CTP ( 4 = PDS ): ' || l_inv_ctp,1);
3799: END IF;
3800:
3801:
3802:
3812:
3813: if( p_ship_set.requested_ship_date.exists(lModelIndex)) then
3814:
3815: IF PG_DEBUG <> 0 THEN
3816: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date exists ',1);
3817: END IF;
3818:
3819:
3820: if( p_ship_set.requested_ship_date(lModelIndex) is not null ) then
3818:
3819:
3820: if( p_ship_set.requested_ship_date(lModelIndex) is not null ) then
3821: IF PG_DEBUG <> 0 THEN
3822: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date exists ' || p_ship_set.requested_ship_date(lModelIndex) ,1);
3823: END IF;
3824:
3825: else
3826:
3824:
3825: else
3826:
3827: IF PG_DEBUG <> 0 THEN
3828: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date is null ' ,1);
3829: END IF;
3830: end if ;
3831:
3832: end if ;
3835:
3836: if( p_ship_set.requested_arrival_date.exists(lModelIndex)) then
3837:
3838: IF PG_DEBUG <> 0 THEN
3839: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date exists ',1);
3840: END IF;
3841:
3842:
3843: if( p_ship_set.requested_arrival_date(lModelIndex) is not null ) then
3841:
3842:
3843: if( p_ship_set.requested_arrival_date(lModelIndex) is not null ) then
3844: IF PG_DEBUG <> 0 THEN
3845: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date exists ' || p_ship_set.requested_arrival_date(lModelIndex) ,1);
3846: END IF;
3847:
3848: else
3849:
3847:
3848: else
3849:
3850: IF PG_DEBUG <> 0 THEN
3851: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date is null ' ,1);
3852: END IF;
3853: end if ;
3854:
3855: end if ;
3872: xMessageName,
3873: xTableName);
3874: if lStatus <> 1 then
3875: IF PG_DEBUG <> 0 THEN
3876: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3877: ' Load_mandatory_comps_pds returned with Error ',1);
3878: END IF;
3879: raise proc_error;
3880: end if;
3892: xTableName);
3893:
3894: if lStatus <> 1 then
3895: IF PG_DEBUG <> 0 THEN
3896: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_comps returned with Error ',1);
3897: END IF;
3898:
3899: raise proc_error;
3900: end if;
3908:
3909: else
3910:
3911:
3912: oe_debug_pub.add(' Load_mandatory_comps returned without processing
3913: as model qty is 0 ',1);
3914: return 1 ;
3915:
3916: end if;
3935:
3936:
3937:
3938: IF PG_DEBUG <> 0 THEN
3939: oe_debug_pub.add('Get_Mandatory_Components: ' || ' will be calling load_mandatory_components ' , 2);
3940: END IF;
3941:
3942: v_request_date := nvl( p_ship_set.requested_ship_date(lModelIndex),
3943: p_ship_set.requested_arrival_date(lModelIndex)) ;
3950: -- Bugfix 3779636
3951: if l_inv_ctp = 4 then
3952:
3953: IF PG_DEBUG <> 0 THEN
3954: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3955: ' now calling load_mandatory_components_pds ' , 2);
3956: END IF;
3957:
3958: lStatus := load_mandatory_components_pds(p_ship_set,
3962: xErrorMessage,
3963: xMessageName,
3964: xTableName);
3965: IF PG_DEBUG <> 0 THEN
3966: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3967: 'Returned from load_mandatory_components_pds with '||lStatus, 2);
3968: END IF;
3969:
3970: if lStatus <> 1 then
3968: END IF;
3969:
3970: if lStatus <> 1 then
3971: IF PG_DEBUG <> 0 THEN
3972: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3973: ' Load_mandatory_components_pds returned with Error ',1);
3974: END IF;
3975: raise proc_error;
3976: end if;
3980:
3981:
3982:
3983: IF PG_DEBUG <> 0 THEN
3984: oe_debug_pub.add('Get_Mandatory_Components: ' || ' now calling load_mandatory_components ' , 2);
3985: END IF;
3986:
3987:
3988:
3993: xErrorMessage,
3994: xMessageName,
3995: xTableName);
3996: IF PG_DEBUG <> 0 THEN
3997: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Returned from load_mand_comps with '||lStatus, 2);
3998: END IF;
3999: if lStatus <> 1 then
4000: IF PG_DEBUG <> 0 THEN
4001: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_components returned with Error ',1);
3997: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Returned from load_mand_comps with '||lStatus, 2);
3998: END IF;
3999: if lStatus <> 1 then
4000: IF PG_DEBUG <> 0 THEN
4001: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_components returned with Error ',1);
4002: END IF;
4003: raise proc_error;
4004: end if;
4005:
4012:
4013: else
4014:
4015:
4016: oe_debug_pub.add(' Load_mandatory_components returned without processing
4017: as model qty is 0 ',1);
4018: return 1 ;
4019:
4020: end if;
4044: where group_id = lGrpId
4045: and ( Line_id is null or top_bill_sequence_id = -1) ; /* BugFix 2897132 */
4046:
4047: IF PG_DEBUG <> 0 THEN
4048: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' Lines exploded ' || chk, 1);
4049: END IF;
4050:
4051: lStmtNumber := 730;
4052:
4050:
4051: lStmtNumber := 730;
4052:
4053: IF PG_DEBUG <> 0 THEN
4054: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Going to Print Mandatory Comps ', 1);
4055: END IF;
4056:
4057:
4058: i := 1;
4065:
4066:
4067: lStmtNumber := 740;
4068: IF PG_DEBUG <> 0 THEN
4069: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' Before calling MRP API::i='||to_char(i),1);
4070: END IF;
4071: MRP_ATP_PVT.assign_Atp_input_rec(p_ship_set,
4072: lModelIndex,
4073: x_smc_rec,
4072: lModelIndex,
4073: x_smc_rec,
4074: lStatus );
4075: IF PG_DEBUG <> 0 THEN
4076: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' MRP API returns with '||lStatus, 2);
4077: END IF;
4078:
4079: if lStatus <> FND_API.G_RET_STS_SUCCESS then
4080: xErrorMessage := 'assign_Atp_input_rec returned with Error';
4094: x_smc_rec.attribute_01(i) := nxtrec.wip_supply_type; -- bugfix 3254039
4095:
4096:
4097: IF PG_DEBUG <> 0 THEN
4098: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Mand comp ' || i ||
4099: ' inv ' || x_smc_rec.inventory_item_id(i) ||
4100: ' eq ' || x_smc_rec.quantity_ordered(i) ||
4101: ' id ' || x_smc_rec.identifier(i) ||
4102: ' wip supply type ' || x_smc_rec.attribute_01(i) , 1 ) ; -- bugfix 3254039
4110: end loop;
4111:
4112:
4113: IF PG_DEBUG <> 0 THEN
4114: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Done Print Mandatory Comps ', 1);
4115: END IF;
4116:
4117:
4118: /*------------------------------------+
4133: xErrorMessage := 'CTOCITMB :'|| to_char(lStmtNumber) || ': ' || xErrorMessage;
4134: return(0);
4135: when others then
4136: IF PG_DEBUG <> 0 THEN
4137: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Others exception is get_mand_comp::'||substrb(sqlerrm,1,150));
4138: END IF;
4139: return(0);
4140:
4141: end Get_Mandatory_Components;
4261: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
4262: */
4263:
4264:
4265: oe_debug_pub.add('Create_Item: ' || 'p_mode ' || p_mode ,2);
4266:
4267: if( p_mode = 'PRECONFIG' ) then
4268:
4269: lValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
4270:
4271: else
4272:
4273: IF PG_DEBUG <> 0 THEN
4274: oe_debug_pub.add('Create_Item: ' || 'Before getting validation org',2);
4275: END IF;
4276:
4277: /*
4278: BUG:3484511
4288:
4289:
4290:
4291: IF PG_DEBUG <> 0 THEN
4292: oe_debug_pub.add('Create_Item: ' || 'Going to fetch Validation Org ' ,2);
4293: END IF;
4294:
4295:
4296: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
4306: raise FND_API.G_EXC_ERROR;
4307: end if;
4308:
4309: IF PG_DEBUG <> 0 THEN
4310: oe_debug_pub.add('Create_Item: ' || 'Validation Org is :' || lValidationOrg,2);
4311: END IF;
4312:
4313:
4314: --
4316: -- in all orgs. If it is a new item, create a new item in all orgs
4317: --
4318:
4319: IF PG_DEBUG <> 0 THEN
4320: oe_debug_pub.add('Create_Item: ' || 'config_item_id::'||to_char(pConfigId),2);
4321: END IF;
4322:
4323: IF (pConfigId is NULL) THEN
4324:
4346: raise FND_API.G_EXC_ERROR;
4347: end if;
4348:
4349: IF PG_DEBUG <> 0 THEN
4350: oe_debug_pub.add('Create_Item: ' || 'config Segment is : ' || lConfigSegName,2 );
4351:
4352: oe_debug_pub.add('Create_Item: ' || 'Number Method is : ' || lNumberMethod,2 );
4353: END IF;
4354:
4348:
4349: IF PG_DEBUG <> 0 THEN
4350: oe_debug_pub.add('Create_Item: ' || 'config Segment is : ' || lConfigSegName,2 );
4351:
4352: oe_debug_pub.add('Create_Item: ' || 'Number Method is : ' || lNumberMethod,2 );
4353: END IF;
4354:
4355: /* BUG #2172057 Change for preconfigure bom by Sushant Sawant */
4356: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
4359: lNumberMethod := 1 ;
4360: end if;
4361:
4362: IF PG_DEBUG <> 0 THEN
4363: oe_debug_pub.add('Create_Item: ' || 'Number Method Changed due to preconfigure bom restriction of append with sequence number: ' ||
4364: lNumberMethod,2 );
4365: END IF;
4366:
4367: end if ;
4523: param4 => NULL,
4524: param5 => NULL
4525: );
4526: IF PG_DEBUG <> 0 THEN
4527: oe_debug_pub.add( 'Precfg case - Custom Item number is ' || new_item_num ,2 );
4528: END IF;
4529: else
4530: new_item_num := BOMPCFGI.user_item_number(pLineId);
4531: IF PG_DEBUG <> 0 THEN
4528: END IF;
4529: else
4530: new_item_num := BOMPCFGI.user_item_number(pLineId);
4531: IF PG_DEBUG <> 0 THEN
4532: oe_debug_pub.add('Create_Item: ' || 'Item number is ' || new_item_num ,2 );
4533: END IF;
4534: end if;
4535: end if; -- End check for number method
4536:
4553: seg(11), seg(12), seg(13), seg(14), seg(15),
4554: seg(16), seg(17), seg(18), seg(19), seg(20)) = 1
4555: then
4556: IF PG_DEBUG <> 0 THEN
4557: oe_debug_pub.add( 'Create_Item: '|| 'generating unique name' ,2 );
4558: END IF;
4559: select to_char(mtl_system_items_B_S.nextval)
4560: into lNextNum
4561: from dual;
4568: for nxt_rec in lDupItem
4569: loop
4570:
4571: IF PG_DEBUG <> 0 THEN
4572: oe_debug_pub.add('Create_Item: ' || 'generating unique name' ,2 );
4573: END IF;
4574: select to_char(mtl_system_items_B_S.nextval)
4575: into lNextNum
4576: from dual;
4579: end loop;
4580: */
4581:
4582: IF PG_DEBUG <> 0 THEN
4583: oe_debug_pub.add('Create_Item: ' || 'item numbering segment is '|| app_col_ind ,2 );
4584:
4585: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4586: END IF;
4587:
4581:
4582: IF PG_DEBUG <> 0 THEN
4583: oe_debug_pub.add('Create_Item: ' || 'item numbering segment is '|| app_col_ind ,2 );
4584:
4585: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4586: END IF;
4587:
4588: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4589:
4587:
4588: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4589:
4590: IF PG_DEBUG <> 0 THEN
4591: oe_debug_pub.add('Create_Item: ' || 'new pConfigId is '||to_char(pConfigId),2);
4592: END IF;
4593:
4594: ELSE
4595: -- config id is populated in config_item_id
4640: from mtl_system_items msi
4641: where inventory_item_id = pConfigId;
4642:
4643: IF PG_DEBUG <> 0 THEN
4644: oe_debug_pub.add('Create_Item: ' || 'matched pConfigId is '||to_char(pConfigId),2);
4645: END IF;
4646:
4647: END IF; -- matched/new item
4648:
4658: --xTableName := 'MTL_SYSTEM_ITEMS';
4659: lStmtNumber := 80;
4660:
4661: /* need to add attribute controlled statement for preconfigured item */
4662: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4663:
4664: if( p_mode = 'AUTOCONFIG' OR pLineId <> v_ato_line_id ) then
4665:
4666: oe_debug_pub.add('Create_Item: ' || 'came into AUTOCONFIG ' ,2);
4662: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4663:
4664: if( p_mode = 'AUTOCONFIG' OR pLineId <> v_ato_line_id ) then
4665:
4666: oe_debug_pub.add('Create_Item: ' || 'came into AUTOCONFIG ' ,2);
4667: /*
4668: tracking_quantity_ind TRACK,
4669: 4 ont_pricing_qty_source PRCQTY, approval_status
4670: */
5393:
5394: sqlcnt := sql%rowcount; -- Added for bug 8305535
5395:
5396: IF PG_DEBUG <> 0 THEN
5397: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5398:
5399: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sqlcnt||' rows in mtl_system_items_b',2);
5400: END IF;
5401: -- added as part of bugfix 1811007
5395:
5396: IF PG_DEBUG <> 0 THEN
5397: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5398:
5399: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sqlcnt||' rows in mtl_system_items_b',2);
5400: END IF;
5401: -- added as part of bugfix 1811007
5402:
5403: --Start Bugfix 8305535
5402:
5403: --Start Bugfix 8305535
5404: if ( sqlcnt > 0) then
5405: IF PG_DEBUG <> 0 THEN
5406: oe_debug_pub.add('Create_Item: ' || 'Going to insert in pl/sql table for project Genesis',2);
5407: END IF;
5408:
5409: l_ind_cnt := CTO_MSUTIL_PUB.cfg_tbl_var.count;
5410: CTO_MSUTIL_PUB.cfg_tbl_var(l_ind_cnt + 1) := pConfigId;
5414:
5415:
5416: else
5417:
5418: oe_debug_pub.add('Create_Item: ' || 'going to call create_precon figured item ' || ' line ' || pLineId ||
5419: ' Model ' || pModelId ||
5420: ' Config ' || pConfigId ||
5421: ' item type ' || lItemType ,2);
5422:
5430:
5431: lStmtNumber := 81;
5432:
5433: IF PG_DEBUG <> 0 THEN
5434: oe_debug_pub.add('Create_Item: ' || 'Opening cursor c_get_org_id.. ' ,3);
5435: END IF;
5436:
5437: BEGIN
5438: FOR v_get_org_id in c_get_org_id
5437: BEGIN
5438: FOR v_get_org_id in c_get_org_id
5439: LOOP
5440: IF PG_DEBUG <> 0 THEN
5441: oe_debug_pub.add('Create_Item: ' || 'Fetched Org Id '|| v_get_org_id.src_org_id,3);
5442: END IF;
5443: fnd_attached_documents2_pkg.copy_attachments (
5444: X_from_entity_name => 'MTL_SYSTEM_ITEMS',
5445: X_from_pk1_value => v_get_org_id.src_org_id,
5460: X_request_id => fnd_global.USER_ID,
5461: X_automatically_added_flag => NULL
5462: );
5463: IF PG_DEBUG <> 0 THEN
5464: oe_debug_pub.add('Create_Item: ' || 'Done copy_attachment ',3);
5465: END IF;
5466: END LOOP;
5467: END;
5468: -- End bugfix 2157740
5478:
5479: --Bugfix 9223554: Will not use these global variables.
5480: /*
5481: IF PG_DEBUG <> 0 THEN
5482: oe_debug_pub.add ('Create_Item: ' || 'gWtStatus = '||CTO_CONFIG_ITEM_PK.gWtStatus|| ' and gVolStatus = '||CTO_CONFIG_ITEM_PK.gVolStatus,1);
5483: END IF;
5484:
5485: if CTO_CONFIG_ITEM_PK.gWtStatus = 0 or -- 0 means success
5486: CTO_CONFIG_ITEM_PK.gVolStatus = 0
5525: -- Eg. C_OPTION 7 (kg) and C_OPTION 9 (Lbs).
5526:
5527: IF o_weight_uom IS NULL THEN
5528: IF PG_DEBUG <> 0 THEN
5529: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM is null for the base model.',2);
5530:
5531: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5532: END IF;
5533:
5527: IF o_weight_uom IS NULL THEN
5528: IF PG_DEBUG <> 0 THEN
5529: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM is null for the base model.',2);
5530:
5531: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5532: END IF;
5533:
5534: begin
5535: select uom_code
5560: -- Eg. C_OPTION 7 (cubic meter) and C_OPTION 9 (cubic feet).
5561:
5562: IF o_volume_uom IS NULL THEN
5563: IF PG_DEBUG <> 0 THEN
5564: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM is null for the base model.',2);
5565:
5566: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5567: END IF;
5568:
5562: IF o_volume_uom IS NULL THEN
5563: IF PG_DEBUG <> 0 THEN
5564: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM is null for the base model.',2);
5565:
5566: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5567: END IF;
5568:
5569: begin
5570: select uom_code
5599: -- the wt or vol for the top level config.
5600:
5601:
5602: IF PG_DEBUG <> 0 THEN
5603: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5604:
5605: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5606:
5607: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5601:
5602: IF PG_DEBUG <> 0 THEN
5603: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5604:
5605: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5606:
5607: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5608:
5609: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5603: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5604:
5605: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5606:
5607: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5608:
5609: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5610:
5611: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5605: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5606:
5607: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5608:
5609: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5610:
5611: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5612: END IF;
5613:
5607: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5608:
5609: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5610:
5611: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5612: END IF;
5613:
5614: Ato_Weight_Volume(
5615: p_ato_line_id => pLineId,
5625: raise FND_API.G_EXC_ERROR;
5626: end if;
5627:
5628: IF PG_DEBUG <> 0 THEN
5629: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5630:
5631: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5632:
5633: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5627:
5628: IF PG_DEBUG <> 0 THEN
5629: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5630:
5631: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5632:
5633: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5634: END IF;
5635:
5629: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5630:
5631: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5632:
5633: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5634: END IF;
5635:
5636:
5637: -- Update the config's weight and volume.
5687:
5688: -- end bugfix 1811007
5689:
5690:
5691: oe_debug_pub.add('Create_Item: ' || 'going to insert:mtl_system_items_tl',2);
5692:
5693: /*----------------------------------------+
5694: R11.5 MLS
5695: +----------------------------------------*/
5756: and t.language = l.language_code );
5757:
5758:
5759: IF PG_DEBUG <> 0 THEN
5760: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_tl',2);
5761: END IF;
5762:
5763:
5764:
5782: -- call API to copy sourcing rules from model item
5783: -- to config item
5784: --
5785: IF PG_DEBUG <> 0 THEN
5786: oe_debug_pub.add ('Create_Item: ' || 'Copying src rule for cfg item '||to_char(pConfigId)||' in org '||to_char(v_src_rule.rcv_org_id), 2);
5787: END IF;
5788: CTO_UTILITY_PK.Create_Sourcing_Rules(
5789: pModelItemId => pModelId,
5790: pConfigId => pConfigId,
5794: x_msg_data => xMsgData);
5795:
5796: IF (lReturnStatus = fnd_api.G_RET_STS_ERROR) THEN
5797: IF PG_DEBUG <> 0 THEN
5798: oe_debug_pub.add ('Create_Item: ' || 'Create_Sourcing_Rules returned with expected error.');
5799: END IF;
5800: raise FND_API.G_EXC_ERROR;
5801:
5802: ELSIF (lReturnStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
5800: raise FND_API.G_EXC_ERROR;
5801:
5802: ELSIF (lReturnStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
5803: IF PG_DEBUG <> 0 THEN
5804: oe_debug_pub.add ('Create_Item: ' || 'Create_Sourcing_Rules returned with unexp error.');
5805: END IF;
5806: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5807:
5808: END IF;
5821: pConfigId => pConfigId);
5822:
5823: IF lStatus <> 1 THEN
5824: IF PG_DEBUG <> 0 THEN
5825: oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Order_Lines function',1);
5826: END IF;
5827: raise FND_API.G_EXC_ERROR;
5828: END IF;
5829:
5840: pConfigId => pConfigId);
5841:
5842: IF lStatus <> 1 THEN
5843: IF PG_DEBUG <> 0 THEN
5844: oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Src_Orgs function',1);
5845: END IF;
5846: raise FND_API.G_EXC_ERROR;
5847: END IF;
5848:
5872: p_mode );
5873:
5874: if lStatus <> 1 then
5875: IF PG_DEBUG <> 0 THEN
5876: oe_debug_pub.add ('Create_Item: ' || 'Failed in create_data function',1);
5877: END IF;
5878: raise FND_API.G_EXC_ERROR;
5879: end if;
5880:
5878: raise FND_API.G_EXC_ERROR;
5879: end if;
5880:
5881: IF PG_DEBUG <> 0 THEN
5882: oe_debug_pub.add ('Create_Item: ' || 'Success in create_data function',1);
5883: END IF;
5884:
5885: return(1);
5886:
5887: EXCEPTION
5888:
5889: WHEN NO_DATA_FOUND THEN
5890: IF PG_DEBUG <> 0 THEN
5891: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::ndf::'||to_char(lStmtNumber)||sqlerrm,1);
5892: END IF;
5893: CTO_MSG_PUB.Count_And_Get(
5894: p_msg_count => xMsgCount,
5895: p_msg_data => xMsgData
5897: return(0);
5898:
5899: WHEN FND_API.G_EXC_ERROR THEN
5900: IF PG_DEBUG <> 0 THEN
5901: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::exp error::'||to_char(lStmtNumber)||sqlerrm,1);
5902: END IF;
5903: CTO_MSG_PUB.Count_And_Get(
5904: p_msg_count => xMsgCount,
5905: p_msg_data => xMsgData
5907: return(0);
5908:
5909: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5910: IF PG_DEBUG <> 0 THEN
5911: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::unexp error::'||to_char(lStmtNumber)||sqlerrm,1);
5912: END IF;
5913: CTO_MSG_PUB.Count_And_Get(
5914: p_msg_count => xMsgCount,
5915: p_msg_data => xMsgData
5917: return(0);
5918:
5919: WHEN OTHERS THEN
5920: IF PG_DEBUG <> 0 THEN
5921: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::others::'||to_char(lStmtNumber)||sqlerrm,1);
5922: END IF;
5923: CTO_MSG_PUB.Count_And_Get(
5924: p_msg_count => xMsgCount,
5925: p_msg_data => xMsgData
6045: status := 0;
6046:
6047:
6048: IF PG_DEBUG <> 0 THEN
6049: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with p_ato_line_id = '||p_ato_line_id,1);
6050: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with pConfigId = '||pConfigId,1);
6051:
6052: END IF;
6053:
6046:
6047:
6048: IF PG_DEBUG <> 0 THEN
6049: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with p_ato_line_id = '||p_ato_line_id,1);
6050: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with pConfigId = '||pConfigId,1);
6051:
6052: END IF;
6053:
6054: lStmtNumber := 1205;
6060: --if CTO_CONFIG_ITEM_PK.gWtStatus = 0 then
6061: if not g_wt_tbl.exists(p_ato_line_id) then
6062:
6063: IF PG_DEBUG <> 0 THEN
6064: oe_debug_pub.add ('Create_Item: ' || '===>weight_uom = '||weight_uom,1);
6065: END IF;
6066:
6067: FOR aw IN ato_weight(p_ato_line_id)
6068: loop
6067: FOR aw IN ato_weight(p_ato_line_id)
6068: loop
6069:
6070: IF PG_DEBUG <> 0 THEN
6071: oe_debug_pub.add ('Create_Item: ' || '===>aw.weight = '||aw.weight||' uom = '||aw.uom||' for item_id '||aw.inventory_item_id,1);
6072: END IF;
6073: -- Bugfix 2301167: Added item_id as a parameter so that item specific conversions take precedence if defined.
6074: lweight := CTO_UTILITY_PK.convert_uom(
6075: from_uom => aw.uom,
6078: item_id => aw.inventory_item_id);
6079: --Bugfix 9214765: Changing the if condition
6080: --if lweight = 0 then
6081: IF PG_DEBUG <> 0 THEN
6082: oe_debug_pub.add ('Create_Item: lweight:' || lweight, 1);
6083: END IF;
6084:
6085: if lweight = -99999 then
6086: IF PG_DEBUG <> 0 THEN
6083: END IF;
6084:
6085: if lweight = -99999 then
6086: IF PG_DEBUG <> 0 THEN
6087: oe_debug_pub.add ('Create_Item: ' || '===>convert_uom returned -99999(error). Not calculating weight. Check if all conversions have been defined.', 1);
6088: END IF;
6089: -- we don't want to update the weight of the config incorrectly, so set it to 0.
6090: weight := 0;
6091:
6105: end if;
6106:
6107:
6108: IF PG_DEBUG <> 0 THEN
6109: oe_debug_pub.add ('Create_Item: ' || '---------------------',1);
6110: END IF;
6111:
6112: lStmtNumber := 1210;
6113:
6118: --if CTO_CONFIG_ITEM_PK.gVolStatus = 0 then
6119: if not g_vol_tbl.exists(p_ato_line_id) then
6120:
6121: IF PG_DEBUG <> 0 THEN
6122: oe_debug_pub.add ('Create_Item: ' || '===>volume_uom = '||volume_uom,1);
6123: END IF;
6124:
6125: FOR av IN ato_volume(p_ato_line_id)
6126: loop
6124:
6125: FOR av IN ato_volume(p_ato_line_id)
6126: loop
6127: IF PG_DEBUG <> 0 THEN
6128: oe_debug_pub.add ('Create_Item: ' || '===>av.volume = '||av.volume||' uom = '||av.uom||' for item_id '||av.inventory_item_id,1);
6129: END IF;
6130: -- Bugfix 2301167: Added item_id as a parameter so that item specific conversions take precedence if defined.
6131: lvolume := CTO_UTILITY_PK.convert_uom(
6132: from_uom => av.uom,
6135: item_id => av.inventory_item_id);
6136: --Bugfix 9214765: Changing the if condition
6137: --if lvolume = 0 then
6138: IF PG_DEBUG <> 0 THEN
6139: oe_debug_pub.add ('Create_Item: lvolume:' || lvolume, 1);
6140: END IF;
6141:
6142: if lvolume = -99999 then
6143: IF PG_DEBUG <> 0 THEN
6140: END IF;
6141:
6142: if lvolume = -99999 then
6143: IF PG_DEBUG <> 0 THEN
6144: oe_debug_pub.add ('Create_Item: ' || '===>convert_uom returned -99999(error). Not calculating volume. Check if all conversions have been defined.', 1);
6145: END IF;
6146: -- we don't want to update the volume of the config incorrectly, so set it to 0.
6147: volume := 0;
6148:
6163:
6164: EXCEPTION
6165: WHEN OTHERS THEN
6166: IF PG_DEBUG <> 0 THEN
6167: oe_debug_pub.add ('Create_Item: ' || 'ERROR: Ato_Weight_Volume::others::'||to_char(lStmtNumber)||':-'||sqlerrm,1);
6168: END IF;
6169: CTO_MSG_PUB.Count_And_Get(
6170: p_msg_count => lMsgCount,
6171: p_msg_data => lMsgData
6338: */
6339:
6340:
6341: IF PG_DEBUG <> 0 THEN
6342: oe_debug_pub.add('Create_Item: ' || 'Going to fetch Validation Org ' ,2);
6343: END IF;
6344:
6345:
6346: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
6351: end if ;
6352:
6353:
6354: IF PG_DEBUG <> 0 THEN
6355: oe_debug_pub.add('Create_Item: ' || 'After getting validation org',2);
6356: END IF;
6357:
6358: if lVAlidationOrg = -99 then -- bugfix 2646849
6359: cto_msg_pub.cto_message('BOM','CTO_VALIDATION_ORG_NOT_SET');
6368:
6369: lStmtNumber := 205;
6370:
6371: IF PG_DEBUG <> 0 THEN
6372: oe_debug_pub.add ('Create_Item: ' || 'Validation Org is :' || lValidationOrg ,2);
6373: END IF;
6374:
6375: insert into MTL_PENDING_ITEM_STATUS (
6376: inventory_item_id,
6416:
6417:
6418:
6419: IF PG_DEBUG <> 0 THEN
6420: oe_debug_pub.add('Create_Item: ' || 'after insert:MTL_PENDING_ITEM_STATUS',2);
6421: END IF;
6422:
6423: /*-------------------------------------------+
6424: Insert Item revision information
6483:
6484:
6485:
6486: IF PG_DEBUG <> 0 THEN
6487: oe_debug_pub.add('Create_Item_Data: ' || 'after insert:MTL_ITEM_REVISIONS',2);
6488: END IF;
6489:
6490: EXCEPTION
6491: when NON_KEY_PRESERVED_ERROR then
6490: EXCEPTION
6491: when NON_KEY_PRESERVED_ERROR then
6492:
6493: IF PG_DEBUG <> 0 THEN
6494: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6495: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6496: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6497: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6498: END IF;
6491: when NON_KEY_PRESERVED_ERROR then
6492:
6493: IF PG_DEBUG <> 0 THEN
6494: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6495: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6496: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6497: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6498: END IF;
6499:
6492:
6493: IF PG_DEBUG <> 0 THEN
6494: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6495: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6496: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6497: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6498: END IF;
6499:
6500: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6493: IF PG_DEBUG <> 0 THEN
6494: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6495: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6496: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6497: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6498: END IF;
6499:
6500: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6501:
6499:
6500: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6501:
6502: IF (xReturnStatus <> fnd_api.G_RET_STS_SUCCESS) THEN
6503: oe_debug_pub.add('Create_item_data : failed in populate_item_revision : '||substrb(sqlerrm,1,60),2);
6504: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6505:
6506: ELSE
6507: IF PG_DEBUG <> 0 THEN
6504: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6505:
6506: ELSE
6507: IF PG_DEBUG <> 0 THEN
6508: oe_debug_pub.add('Create_item_data: done populate_item_revision successfully.',2);
6509: END IF;
6510: END IF;
6511:
6512: when OTHERS then
6509: END IF;
6510: END IF;
6511:
6512: when OTHERS then
6513: oe_debug_pub.add('Create_item_data: Failed while inserting into MTL_ITEM_REVISIONS: '||substrb(sqlerrm,1,60),2);
6514: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6515: END;*/
6516:
6517: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus); --Bugfix 6063990
6516:
6517: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus); --Bugfix 6063990
6518:
6519: IF (xReturnStatus <> fnd_api.G_RET_STS_SUCCESS) THEN
6520: oe_debug_pub.add('Create_item_data : failed in populate_item_revision : '||substrb(sqlerrm,1,60),2);
6521: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6522:
6523: ELSE
6524: IF PG_DEBUG <> 0 THEN
6521: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6522:
6523: ELSE
6524: IF PG_DEBUG <> 0 THEN
6525: oe_debug_pub.add('Create_item_data: done populate_item_revision successfully.',2);
6526: END IF;
6527: END IF;
6528: --End Bugfix 6063990.
6529:
6595: if( l_rt_cicd_summary.cost_organization_id.count > 0 ) then
6596: for i in l_rt_cicd_summary.cost_organization_id.first..l_rt_cicd_summary.cost_organization_id.last
6597: loop
6598:
6599: oe_debug_pub.add( i || ') ' || 'Cost Header Info: ' ||
6600: ' cst org ' || l_rt_cicd_summary.cost_organization_id(i) ||
6601: ' cst id ' || l_rt_cicd_summary.cost_type_id(i) ||
6602: ' m cost ' || l_rt_cicd_summary.material_cost(i) ||
6603: ' moh cost ' || l_rt_cicd_summary.material_overhead_cost(i) ||
6612: + l_rt_cicd_summary.overhead_cost(i) ;
6613:
6614:
6615:
6616: oe_debug_pub.add( ' item cost ' || l_rt_cicd_summary.item_cost(i) , 1 );
6617:
6618:
6619: end loop ;
6620:
6621:
6622: else
6623:
6624:
6625: oe_debug_pub.add( ' no new item cost records for ' || pConfigId , 1 );
6626:
6627: end if;
6628:
6629: /*-------------------------------------------------------+
6761: lCnt := sql%rowcount;
6762: --Bugfix 6717614
6763:
6764: IF PG_DEBUG <> 0 THEN
6765: oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert:CST_ITEM_COSTS inserted '|| to_char(lCnt),2);
6766: END IF;
6767:
6768:
6769: if( l_rt_cicd_summary.cost_type_id.count> 0 ) then
6787: and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
6788:
6789:
6790: IF PG_DEBUG <> 0 THEN
6791: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
6792: END IF;
6793: else
6794:
6795: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
6791: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
6792: END IF;
6793: else
6794:
6795: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
6796:
6797: end if;
6798:
6799:
6815: exception
6816: when no_data_found then
6817:
6818: IF PG_DEBUG <> 0 THEN
6819: oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
6820: oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
6821: END IF;
6822:
6823: l_cto_cost_type_id := 7 ;
6816: when no_data_found then
6817:
6818: IF PG_DEBUG <> 0 THEN
6819: oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
6820: oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
6821: END IF;
6822:
6823: l_cto_cost_type_id := 7 ;
6824:
6827: from cst_cost_types
6828: where cost_type_id = l_cto_cost_type_id ;
6829:
6830: IF PG_DEBUG <> 0 THEN
6831: oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || l_cto_cost_type_id ||
6832: ' has cost_type = -' || v_cto_cost_type_name || '- delimiter(-cost-)' ,2);
6833: END IF;
6834: exception
6835: when no_data_found then
6833: END IF;
6834: exception
6835: when no_data_found then
6836: IF PG_DEBUG <> 0 THEN
6837: oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
6838: END IF;
6839: cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
6840: raise FND_API.G_EXC_ERROR;
6841: when others then
6977:
6978:
6979:
6980: IF PG_DEBUG <> 0 THEN
6981: oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert UD cost into CTO cost type inserted '|| to_char(sql%rowcount),2);
6982:
6983: END IF;
6984:
6985:
7004: and cost_type_id = l_cto_cost_type_id ;
7005:
7006:
7007: IF PG_DEBUG <> 0 THEN
7008: oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
7009: END IF;
7010:
7011: else
7012:
7009: END IF;
7010:
7011: else
7012:
7013: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
7014:
7015: end if;
7016:
7017:
7137:
7138:
7139:
7140: IF PG_DEBUG <> 0 THEN
7141: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_item_cost_details inserted '||to_char(sql%rowcount),2);
7142: END IF;
7143:
7144:
7145:
7263:
7264:
7265:
7266: IF PG_DEBUG <> 0 THEN
7267: oe_debug_pub.add('Create_Item: ' || 'after insert UD cost into CICD for CTO cost type inserted '||to_char(sql%rowcount),2);
7268: END IF;
7269:
7270:
7271: --Bugfix 6717614
7270:
7271: --Bugfix 6717614
7272: if lCnt = 0 then
7273: IF PG_DEBUG <> 0 THEN
7274: oe_debug_pub.add('Create_Item: ' || 'No need to populate csc and cec as no new record inserted in cic and cicd',2);
7275: END IF;
7276: else
7277:
7278: --begin Bugfix 6363308
7277:
7278: --begin Bugfix 6363308
7279: lStmtNumber := 231;
7280: IF PG_DEBUG <> 0 THEN
7281: oe_debug_pub.add('Create_Item: ' || 'going to populate data in csc and cec if frozen cost of config is not zero',2);
7282: END IF;
7283:
7284: OPEN get_orgs_with_frozen_cost(pconfigId);
7285: loop
7288: , v_item_cost_frozen;
7289: EXIT WHEN get_orgs_with_frozen_cost%NOTFOUND;
7290:
7291: IF PG_DEBUG <> 0 THEN
7292: oe_debug_pub.add('Create_Item: ' || 'fetched org '||to_char(v_organization_id),2);
7293: oe_debug_pub.add('Create_Item: ' || 'frozen cost for config '||to_char(pConfigId) || 'is: ' || to_char(v_item_cost_frozen),2);
7294: END IF;
7295:
7296: IF (v_item_cost_frozen <> 0) THEN
7289: EXIT WHEN get_orgs_with_frozen_cost%NOTFOUND;
7290:
7291: IF PG_DEBUG <> 0 THEN
7292: oe_debug_pub.add('Create_Item: ' || 'fetched org '||to_char(v_organization_id),2);
7293: oe_debug_pub.add('Create_Item: ' || 'frozen cost for config '||to_char(pConfigId) || 'is: ' || to_char(v_item_cost_frozen),2);
7294: END IF;
7295:
7296: IF (v_item_cost_frozen <> 0) THEN
7297:
7296: IF (v_item_cost_frozen <> 0) THEN
7297:
7298: lStmtNumber := 232;
7299: IF PG_DEBUG <> 0 THEN
7300: oe_debug_pub.add('Create_Item: ' || 'Came inside if. v_item_cost_frozen is not zero',2);
7301: oe_debug_pub.add('Create_Item: ' || 'Fetching value from sequence',2);
7302: END IF;
7303:
7304: Select cst_lists_s.nextval
7297:
7298: lStmtNumber := 232;
7299: IF PG_DEBUG <> 0 THEN
7300: oe_debug_pub.add('Create_Item: ' || 'Came inside if. v_item_cost_frozen is not zero',2);
7301: oe_debug_pub.add('Create_Item: ' || 'Fetching value from sequence',2);
7302: END IF;
7303:
7304: Select cst_lists_s.nextval
7305: into l_cost_update
7313: AND INVENTORY_ITEM_ID = pConfigId
7314: AND COST_TYPE_ID = 1;
7315:
7316: IF PG_DEBUG <> 0 THEN
7317: oe_debug_pub.add('Create_Item: ' || 'Updated cost_update_id to value ' || to_char(l_cost_update),2);
7318: END IF;
7319:
7320: lStmtNumber := 234;
7321: IF PG_DEBUG <> 0 THEN
7318: END IF;
7319:
7320: lStmtNumber := 234;
7321: IF PG_DEBUG <> 0 THEN
7322: oe_debug_pub.add('Create_Item: ' || 'Inserting records in csc and cec',2);
7323: END IF;
7324:
7325: INSERT INTO cst_standard_costs
7326: (cost_update_id,
7349: AND INVENTORY_ITEM_ID = pConfigId
7350: AND COST_TYPE_ID = 1;
7351:
7352: IF PG_DEBUG <> 0 THEN
7353: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_standard_costs ' || sql%rowcount ,2);
7354: END IF;
7355:
7356: lStmtNumber := 235;
7357:
7383: AND COST_TYPE_ID = 1
7384: GROUP BY cost_element_id;
7385:
7386: IF PG_DEBUG <> 0 THEN
7387: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_elemental_costs ' || sql%rowcount ,2);
7388: END IF;
7389: END IF; -- v_item_cost_frozen <> 0
7390: end loop;
7391:
7450: );
7451:
7452: IF (l_layer_id = 0) THEN
7453: IF PG_DEBUG <> 0 THEN
7454: oe_debug_pub.add('Create_Item: ' || 'CST function create_layer returned with error '||to_char(x_err_num)||', '||x_msg_name||', '||
7455: lMsgData||'for '||to_char(v_layer.org_id)||', '||to_char(v_layer.cost_group_id),1);
7456: END IF;
7457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7458: ELSE
7456: END IF;
7457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7458: ELSE
7459: IF PG_DEBUG <> 0 THEN
7460: oe_debug_pub.add('Create_Item: ' || 'Inserted row into cql for '||to_char(l_layer_id)||', '||to_char(v_layer.org_id)||', '||
7461: to_char(v_layer.cost_group_id),1);
7462: END IF;
7463: END IF;
7464:
7465: END LOOP;
7466: END IF; /* End bugfix 1573941 */
7467:
7468: IF PG_DEBUG <> 0 THEN
7469: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_quantity_layers ',2);
7470: END IF;
7471:
7472:
7473: /*--------------------------------------------------------+
7523: where inventory_item_id = pConfigId
7524: and organization_id = lValidationOrg);
7525:
7526: IF PG_DEBUG <> 0 THEN
7527: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_DESCR_ELEMENT_VALUES',2);
7528: END IF;
7529:
7530:
7531: /*--------------------------------------+
7598:
7599:
7600:
7601: IF PG_DEBUG <> 0 THEN
7602: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES',2);
7603: END IF;
7604:
7605: -- Start Bugfix 2197842
7606:
7659:
7660:
7661:
7662: IF PG_DEBUG <> 0 THEN
7663: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES FOR DEFAULT CATEGORIES',2);
7664: END IF;
7665:
7666: -- End Bugfix 2197842
7667:
7719:
7720:
7721:
7722: IF PG_DEBUG <> 0 THEN
7723: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_related_items',2);
7724: END IF;
7725:
7726: /*--------------------------------------------------+
7727: Copy substitute inventories
7829:
7830:
7831:
7832: IF PG_DEBUG <> 0 THEN
7833: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_item_sub_inventories',2);
7834: END IF;
7835:
7836: /*--------------------------------------+
7837: Copy secondary locators
7889:
7890:
7891:
7892: IF PG_DEBUG <> 0 THEN
7893: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_secondary_locators',2);
7894: END IF;
7895:
7896: /*----------------------------------------+
7897: Copy cross references
8007: ,NULL --ATTRIBUTE_CATEGORY
8008: );
8009:
8010: IF PG_DEBUG <> 0 THEN
8011: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_b',2);
8012: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_b'||sql%rowcount,2);
8013:
8014: END IF;
8015:
8008: );
8009:
8010: IF PG_DEBUG <> 0 THEN
8011: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_b',2);
8012: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_b'||sql%rowcount,2);
8013:
8014: END IF;
8015:
8016: INSERT INTO mtl_cross_references_tl (
8048:
8049:
8050:
8051: IF PG_DEBUG <> 0 THEN
8052: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_tl',2);
8053: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_tl'||sql%rowcount,2);
8054:
8055: END IF;
8056:
8049:
8050:
8051: IF PG_DEBUG <> 0 THEN
8052: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_tl',2);
8053: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_tl'||sql%rowcount,2);
8054:
8055: END IF;
8056:
8057:
8111: and organization_id = bcso.organization_id);
8112:
8113:
8114: IF PG_DEBUG <> 0 THEN
8115: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_sub_defaults',2);
8116: END IF;
8117:
8118: -- end 2461574
8119: -- start 2786934
8170:
8171:
8172:
8173: IF PG_DEBUG <> 0 THEN
8174: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_loc_defaults',2);
8175: END IF;
8176:
8177: -- end 2786934
8178:
8211:
8212:
8213: IF PG_DEBUG <> 0 THEN
8214:
8215: oe_debug_pub.add('Create_Item: ' || 'inserted '||sql%rowcount||' in mtl_abc_assignments',2);
8216:
8217: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_abc_assignments',2);
8218: END IF;
8219:
8213: IF PG_DEBUG <> 0 THEN
8214:
8215: oe_debug_pub.add('Create_Item: ' || 'inserted '||sql%rowcount||' in mtl_abc_assignments',2);
8216:
8217: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_abc_assignments',2);
8218: END IF;
8219:
8220: --end bugfix 2828588
8221:
8237:
8238: BEGIN
8239: FOR cust_id_xref_rec IN cust_id_xref_cur LOOP
8240: IF PG_DEBUG <> 0 THEN
8241: oe_debug_pub.add('Create_Item: '||'Customer Item Id:'|| cust_id_xref_rec.customer_item_id, 5);
8242: oe_debug_pub.add('Create_Item: '||'Rank:'|| cust_id_xref_rec.pref_number, 5);
8243: END IF;
8244:
8245: INSERT
8238: BEGIN
8239: FOR cust_id_xref_rec IN cust_id_xref_cur LOOP
8240: IF PG_DEBUG <> 0 THEN
8241: oe_debug_pub.add('Create_Item: '||'Customer Item Id:'|| cust_id_xref_rec.customer_item_id, 5);
8242: oe_debug_pub.add('Create_Item: '||'Rank:'|| cust_id_xref_rec.pref_number, 5);
8243: END IF;
8244:
8245: INSERT
8246: INTO mtl_customer_item_xrefs
8310: END LOOP;
8311: END;
8312:
8313: IF PG_DEBUG <> 0 THEN
8314: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_customer_item_xrefs',2);
8315: END IF;
8316: -- End Bugfix 9288619
8317:
8318:
8321: EXCEPTION
8322:
8323: WHEN NO_DATA_FOUND THEN
8324: IF PG_DEBUG <> 0 THEN
8325: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::ndf::'||to_char(lStmtNumber)||sqlerrm,1);
8326: END IF;
8327: CTO_MSG_PUB.Count_And_Get(
8328: p_msg_count => lMsgCount,
8329: p_msg_data => lMsgData
8331: return(0);
8332:
8333: WHEN FND_API.G_EXC_ERROR THEN
8334: IF PG_DEBUG <> 0 THEN
8335: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::exp error::'||to_char(lStmtNumber)||sqlerrm,1);
8336: END IF;
8337: CTO_MSG_PUB.Count_And_Get(
8338: p_msg_count => lMsgCount,
8339: p_msg_data => lMsgData
8341: return(0);
8342:
8343: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8344: IF PG_DEBUG <> 0 THEN
8345: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::unexp error::'||to_char(lStmtNumber)||sqlerrm,1);
8346: END IF;
8347: CTO_MSG_PUB.Count_And_Get(
8348: p_msg_count => lMsgCount,
8349: p_msg_data => lMsgData
8352:
8353:
8354: WHEN OTHERS THEN
8355: IF PG_DEBUG <> 0 THEN
8356: oe_debug_pub.add ('Create_Item: ' || 'create_item_data::others::'||to_char(lStmtNumber)||sqlerrm,1);
8357: END IF;
8358: CTO_MSG_PUB.Count_And_Get(
8359: p_msg_count => lMsgCount,
8360: p_msg_data => lMsgData
8463: x_config_line_id => l_config_line_id,
8464: x_config_item_id => l_config_id) = TRUE)
8465: THEN
8466: IF PG_DEBUG <> 0 THEN
8467: oe_debug_pub.add('link_item: ' || 'Config Line Exists, do not link', 1);
8468: END IF;
8469: return(1);
8470: END IF;
8471:
8470: END IF;
8471:
8472:
8473: IF PG_DEBUG <> 0 THEN
8474: oe_debug_pub.add ('link_item: ' || 'calling oe_line_util.query_row , 2');
8475: END IF;
8476:
8477: Select concatenated_segments
8478: into l_model_tab(1).ordered_item
8489: --
8490: -- selecting all rows to be updated into l_upd_line_tbl
8491: --
8492: IF PG_DEBUG <> 0 THEN
8493: oe_debug_pub.add ('link_item: ' || 'link : visible demand flag : before selecting options ', 2);
8494: END IF;
8495:
8496:
8497: /* BUG#2234858 Sushant added this for Drop Ship project
8498: ** Need to provide this functionality for non drop shipped items only
8499: */
8500:
8501: IF PG_DEBUG <> 0 THEN
8502: oe_debug_pub.add('link_item: ' || 'Before visible demand flag souce type code = '||l_line_rec.source_type_code,1);
8503: END IF;
8504: if( l_line_rec.source_type_code = 'INTERNAL') then
8505:
8506:
8511: -- The previous process order API is replaced with this update statement for performance purpose.
8512:
8513:
8514: IF PG_DEBUG <> 0 THEN
8515: oe_debug_pub.add('link_item: ' || 'Before updating visible demand Flag...',1);
8516:
8517: oe_debug_pub.add('link_item: ' || ' ATO line id = '||to_char(plineid),1);
8518: END IF;
8519:
8513:
8514: IF PG_DEBUG <> 0 THEN
8515: oe_debug_pub.add('link_item: ' || 'Before updating visible demand Flag...',1);
8516:
8517: oe_debug_pub.add('link_item: ' || ' ATO line id = '||to_char(plineid),1);
8518: END IF;
8519:
8520: UPDATE OE_ORDER_LINES_ALL
8521: SET visible_demand_flag = 'N'
8521: SET visible_demand_flag = 'N'
8522: WHERE ato_line_id = pLineId;
8523:
8524: IF PG_DEBUG <> 0 THEN
8525: oe_debug_pub.add('link_item: ' || 'after updating visible demand flag..',1);
8526:
8527: oe_debug_pub.add('link_item: ' || 'No of rows updated = '||sql%rowcount,1);
8528: END IF;
8529:
8523:
8524: IF PG_DEBUG <> 0 THEN
8525: oe_debug_pub.add('link_item: ' || 'after updating visible demand flag..',1);
8526:
8527: oe_debug_pub.add('link_item: ' || 'No of rows updated = '||sql%rowcount,1);
8528: END IF;
8529:
8530: -- End of adition on 04/04/2001
8531:
8555: --
8556: FND_PROFILE.Get('ONT_BYPASS_ATP', l_bypassatp_profile_value);
8557:
8558: IF PG_DEBUG <> 0 THEN
8559: oe_debug_pub.add('link_item: Value of the profile ONT_BYPASS_ATP :' ||l_bypassatp_profile_value ,3);
8560: END IF;
8561:
8562: IF (NVL(l_bypassatp_profile_value,'N') <> 'Y') THEN
8563: l_model_tab(1).visible_demand_flag := 'Y';
8630: l_model_tab(1).dep_plan_required_flag := l_line_rec.dep_plan_required_flag;
8631:
8632:
8633: IF PG_DEBUG <> 0 THEN
8634: oe_debug_pub.add ('link_item: ' || 'linking item ' || l_model_tab(1).ordered_item, 2);
8635: END IF;
8636:
8637: lStmtNumber := 400;
8638: /*
8658:
8659: lStmtNumber := 410;
8660:
8661: IF PG_DEBUG <> 0 THEN
8662: oe_debug_pub.add ('link_item: ' || 'calling oe_order_grp.Process_order', 2);
8663: END IF;
8664: -- Change for MOAC
8665: -- Changing the public process order API to group API
8666: oe_order_grp.Process_Order (
8692: x_action_request_tbl => l_action_request_tbl
8693: );
8694:
8695: IF PG_DEBUG <> 0 THEN
8696: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_return_status, 1);
8697:
8698: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_msg_data, 2);
8699: END IF;
8700:
8694:
8695: IF PG_DEBUG <> 0 THEN
8696: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_return_status, 1);
8697:
8698: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_msg_data, 2);
8699: END IF;
8700:
8701: if (l_return_status = FND_API.G_RET_STS_ERROR) then
8702: IF PG_DEBUG <> 0 THEN
8699: END IF;
8700:
8701: if (l_return_status = FND_API.G_RET_STS_ERROR) then
8702: IF PG_DEBUG <> 0 THEN
8703: oe_debug_pub.add ('link_item: ' || 'Process_order returned expected error :'||l_msg_data,1);
8704: END IF;
8705: raise FND_API.G_EXC_ERROR;
8706:
8707: elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
8705: raise FND_API.G_EXC_ERROR;
8706:
8707: elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
8708: IF PG_DEBUG <> 0 THEN
8709: oe_debug_pub.add ('link_item: ' || 'Process_order returned unexpected error :'||l_msg_data,1);
8710: END IF;
8711: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8712:
8713: end if;
8794:
8795: elsif(l_stat =0) then
8796: -- do nothing
8797: IF PG_DEBUG <> 0 THEN
8798: oe_debug_pub.add ('link_item: ' || 'no model line present in bcod table to create a CID,not an error', 3);
8799: END IF;
8800:
8801: end if;
8802:
8820: index_counter_link:= 1;
8821: p_config_line_arr_link(index_counter_link) := l_config_line_id ;
8822:
8823: IF PG_DEBUG <> 0 THEN
8824: oe_debug_pub.add('link_item: ' || 'Start MRP package for link', 1);
8825: END IF;
8826:
8827: MRP_OM_API_PK.MRP_OM_Interface
8828: (p_line_tbl => p_config_line_arr_link,
8828: (p_line_tbl => p_config_line_arr_link,
8829: x_return_status => l_return_status_link);
8830:
8831: IF PG_DEBUG <> 0 THEN
8832: oe_debug_pub.add ('link_item: ' || 'Return status from MRP_OM_Interface - Link: '||l_return_status_link,2);
8833: END IF;
8834:
8835: if l_return_status_link = FND_API.G_RET_STS_ERROR then
8836: IF PG_DEBUG <> 0 THEN
8833: END IF;
8834:
8835: if l_return_status_link = FND_API.G_RET_STS_ERROR then
8836: IF PG_DEBUG <> 0 THEN
8837: oe_debug_pub.add ('link_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with expected error.', 1);
8838: END IF;
8839: raise FND_API.G_EXC_ERROR;
8840:
8841: elsif l_return_status_link = FND_API.G_RET_STS_UNEXP_ERROR then
8839: raise FND_API.G_EXC_ERROR;
8840:
8841: elsif l_return_status_link = FND_API.G_RET_STS_UNEXP_ERROR then
8842: IF PG_DEBUG <> 0 THEN
8843: oe_debug_pub.add ('link_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with unexpected error.', 1);
8844: END IF;
8845: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8846: end if;
8847:
8855: --
8856:
8857: if (CTO_CONFIG_BOM_PK.gApplyHold = 'Y') then
8858: IF PG_DEBUG <> 0 THEN
8859: oe_debug_pub.add ('CTOCITMB:Need to put hold on config line '||l_config_line_id);
8860: END IF;
8861:
8862: /* Put the config line on Hold. But first, check to see if a hold already exists
8863: on this line */
8871: ,x_msg_data => l_x_error_msg);
8872:
8873: IF (l_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
8874: IF PG_DEBUG <> 0 THEN
8875: oe_debug_pub.add('CTOCITMB:Failed in Check Holds with expected error.' ,1);
8876: END IF;
8877: raise FND_API.G_EXC_ERROR;
8878:
8879: ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8877: raise FND_API.G_EXC_ERROR;
8878:
8879: ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8880: IF PG_DEBUG <> 0 THEN
8881: oe_debug_pub.add('CTOCITMB:Failed in Check Holds with unexpected error.' ,1);
8882: END IF;
8883: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8884:
8885: ELSE
8883: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8884:
8885: ELSE
8886: IF PG_DEBUG <> 0 THEN
8887: oe_debug_pub.add('CTOCITMB:Success in Check Holds.' ,1);
8888: END IF;
8889: if l_x_hold_result_out = FND_API.G_FALSE then
8890:
8891:
8900:
8901: v_hold_id := 55 ;
8902:
8903: IF PG_DEBUG <> 0 THEN
8904: oe_debug_pub.add('CTOCITMB: Going to apply normal hold .' ,1);
8905: END IF;
8906:
8907: else
8908:
8908:
8909: v_hold_id := 61 ; /* New Hold Id for Create Supply Activity Hold , Should be changed from 1063 to 61 after Gayatri gives the script*/
8910:
8911: IF PG_DEBUG <> 0 THEN
8912: oe_debug_pub.add('CTOCITMB: Going to apply create supply activity hold .' ,1);
8913: END IF;
8914:
8915: end if;
8916:
8917:
8918:
8919:
8920: IF PG_DEBUG <> 0 THEN
8921: oe_debug_pub.add('CTOCITMB:Calling OM api to apply hold.' ,1);
8922: END IF;
8923:
8924: l_hold_source_rec.hold_entity_code := 'O';
8925: l_hold_source_rec.hold_id := v_hold_id ;
8935: , x_msg_data => l_msg_data);
8936:
8937: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
8938: IF PG_DEBUG <> 0 THEN
8939: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with expected error.' ,1);
8940: END IF;
8941: raise FND_API.G_EXC_ERROR;
8942:
8943: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8941: raise FND_API.G_EXC_ERROR;
8942:
8943: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8944: IF PG_DEBUG <> 0 THEN
8945: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with unexpected error.' ,1);
8946: END IF;
8947: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8948: END IF;
8949:
8947: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8948: END IF;
8949:
8950: IF PG_DEBUG <> 0 THEN
8951: oe_debug_pub.add('CTOCITMB: An Exception Hold applied to config line.' ,1);
8952: END IF;
8953: cto_msg_pub.cto_message('BOM','CTO_ORDER_LINE_EXCPN_HOLD');
8954:
8955: else
8953: cto_msg_pub.cto_message('BOM','CTO_ORDER_LINE_EXCPN_HOLD');
8954:
8955: else
8956: IF PG_DEBUG <> 0 THEN
8957: oe_debug_pub.add('CTOCITMB: Line is already on Hold.' ,1);
8958: END IF;
8959: end if;
8960: END IF;
8961: end if;
8969: EXCEPTION
8970:
8971: WHEN NO_DATA_FOUND THEN
8972: IF PG_DEBUG <> 0 THEN
8973: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::ndf::'||to_char(lStmtNumber)||'::'||sqlerrm, 1);
8974: END IF;
8975: CTO_MSG_PUB.Count_And_Get(
8976: p_msg_count => l_Msg_Count,
8977: p_msg_data => l_Msg_Data
8979: return(0);
8980:
8981: WHEN FND_API.G_EXC_ERROR THEN
8982: IF PG_DEBUG <> 0 THEN
8983: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
8984: END IF;
8985: CTO_MSG_PUB.Count_And_Get(
8986: p_msg_count => l_Msg_Count,
8987: p_msg_data => l_Msg_Data
8989: return(0);
8990:
8991: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8992: IF PG_DEBUG <> 0 THEN
8993: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
8994: END IF;
8995: CTO_MSG_PUB.Count_And_Get(
8996: p_msg_count => l_Msg_Count,
8997: p_msg_data => l_Msg_Data
8999: return(0);
9000:
9001: WHEN OTHERS THEN
9002: IF PG_DEBUG <> 0 THEN
9003: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::others::'||to_char(lStmtNumber)||'::'||sqlerrm, 1);
9004: END IF;
9005: CTO_MSG_PUB.Count_And_Get(
9006: p_msg_count => l_Msg_Count,
9007: p_msg_data => l_Msg_Data
9067:
9068: BEGIN
9069:
9070: IF PG_DEBUG <> 0 THEN
9071: oe_debug_pub.add( 'Check_dup_item: '|| 'Checking for duplicate item name.. ' ,2 );
9072: END IF;
9073:
9074: sql_str := 'select 1 from mtl_system_items msi where 1=1 ';
9075:
9154: end if;
9155:
9156:
9157: IF PG_DEBUG <> 0 THEN
9158: oe_debug_pub.add( 'Check_dup_item: '||'sql_str : '||sql_str, 3);
9159: END IF;
9160:
9161: lcursor := dbms_sql.open_cursor;
9162:
9230: dbms_sql.close_cursor(lcursor);
9231:
9232: if rows_processed > 0 then
9233: IF PG_DEBUG <> 0 THEN
9234: oe_debug_pub.add( 'Check_dup_item: '||'Duplicate item name found.');
9235: END IF;
9236: return 1;
9237: else
9238: IF PG_DEBUG <> 0 THEN
9235: END IF;
9236: return 1;
9237: else
9238: IF PG_DEBUG <> 0 THEN
9239: oe_debug_pub.add( 'Check_dup_item: '||'Duplicate item name NOT found.');
9240: END IF;
9241: return 0;
9242: end if;
9243:
9262:
9263: stmtnum := 1;
9264:
9265: IF PG_DEBUG <> 0 THEN --Bugfix 6063990
9266: oe_debug_pub.add('Inside populate_item_revision');
9267: END IF;
9268: --sql_stmt := 'SELECT MTL_ITEM_REVISIONS_B_S.nextval FROM dual';
9269:
9270: --IF PG_DEBUG <> 0 THEN
9267: END IF;
9268: --sql_stmt := 'SELECT MTL_ITEM_REVISIONS_B_S.nextval FROM dual';
9269:
9270: --IF PG_DEBUG <> 0 THEN
9271: -- oe_debug_pub.add(sql_stmt,3);
9272: --END IF;
9273:
9274: --EXECUTE IMMEDIATE sql_stmt INTO x_item_rev_seq ;
9275:
9273:
9274: --EXECUTE IMMEDIATE sql_stmt INTO x_item_rev_seq ;
9275:
9276: --IF PG_DEBUG <> 0 THEN
9277: -- oe_debug_pub.add(' x_item_rev_seq = '|| x_item_rev_seq ,1);
9278:
9279: -- END IF;
9280:
9281: --3340844 joing to mp2 and distinct has been removed
9327:
9328:
9329:
9330: /*IF PG_DEBUG <> 0 THEN
9331: oe_debug_pub.add(sql_stmt,3);
9332: END IF;*/ --Bugfix 6063990
9333:
9334: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId, x_item_rev_seq, pModelId, pLineId, pConfigId;
9335: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pModelId, pLineId, pConfigId;
9335: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pModelId, pLineId, pConfigId;
9336: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pConfigId,pConfigId; Bugfix 6063990
9337:
9338: IF PG_DEBUG <> 0 THEN
9339: oe_debug_pub.add('Inserted into mtl_item_revisions_b for item ' || pConfigId || ' rows ' || SQL%ROWCOUNT );
9340: END IF;
9341:
9342:
9343:
9400:
9401:
9402:
9403: /*IF PG_DEBUG <> 0 THEN
9404: oe_debug_pub.add(sql_stmt,3);
9405: END IF;*/ --Bugfix 6063990
9406:
9407: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, x_item_rev_seq, gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId, x_item_rev_seq;
9408:
9408:
9409: --EXECUTE IMMEDIATE sql_stmt USING pConfigId,gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId,pConfigId; Bugfix 6063990
9410:
9411: IF PG_DEBUG <> 0 THEN
9412: oe_debug_pub.add('Inserted into mtl_system_items_tl.');
9413: END IF;
9414:
9415: EXCEPTION
9416: when OTHERS then
9413: END IF;
9414:
9415: EXCEPTION
9416: when OTHERS then
9417: oe_debug_pub.add ('Failed in dynamic query : '||sqlerrm);
9418: xReturnStatus := fnd_api.G_RET_STS_UNEXP_ERROR;
9419:
9420: END populate_item_revision;
9421:
9433: sqlcnt1 number; --Bugfix 8305535
9434:
9435: BEGIN
9436:
9437: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9438:
9439:
9440: select substr( attribute_name, instr( attribute_name, '.' )+ 1 ) , control_level
9441: BULK COLLECT
9444: where control_level = 1 ;
9445:
9446:
9447: if( get_attribute_control( 'atp_flag' ) = 1 ) then
9448: oe_debug_pub.add( 'ATP flag is master controlled ' , 1 );
9449: else
9450: oe_debug_pub.add( 'ATP flag is org controlled ' , 1 );
9451:
9452: end if;
9446:
9447: if( get_attribute_control( 'atp_flag' ) = 1 ) then
9448: oe_debug_pub.add( 'ATP flag is master controlled ' , 1 );
9449: else
9450: oe_debug_pub.add( 'ATP flag is org controlled ' , 1 );
9451:
9452: end if;
9453:
9454: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9451:
9452: end if;
9453:
9454: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9455: oe_debug_pub.add( 'ATP components flag is master controlled ' , 1 );
9456: else
9457: oe_debug_pub.add( 'ATP components flag is org controlled ' , 1 );
9458:
9459: end if;
9453:
9454: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9455: oe_debug_pub.add( 'ATP components flag is master controlled ' , 1 );
9456: else
9457: oe_debug_pub.add( 'ATP components flag is org controlled ' , 1 );
9458:
9459: end if;
9460:
9461:
9459: end if;
9460:
9461:
9462: if( get_attribute_control( 'market_price' ) = 1 ) then
9463: oe_debug_pub.add( 'market_price is master controlled ' , 1 );
9464: else
9465: oe_debug_pub.add( 'market_price flag is org controlled ' , 1 );
9466:
9467: end if;
9461:
9462: if( get_attribute_control( 'market_price' ) = 1 ) then
9463: oe_debug_pub.add( 'market_price is master controlled ' , 1 );
9464: else
9465: oe_debug_pub.add( 'market_price flag is org controlled ' , 1 );
9466:
9467: end if;
9468:
9469:
9468:
9469:
9470:
9471:
9472: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9473:
9474: insert into mtl_system_items_b
9475: (inventory_item_id,
9476: organization_id,
10194: --Start Bugfix 8305535
10195: sqlcnt1 := sql%rowcount;
10196: if ( sqlcnt1 > 0) then
10197: IF PG_DEBUG <> 0 THEN
10198: oe_debug_pub.add('Create_Item: ' || 'Going to insert in pl/sql table for project Genesis',2);
10199: END IF;
10200: l_ind_cnt := CTO_MSUTIL_PUB.cfg_tbl_var.count;
10201: CTO_MSUTIL_PUB.cfg_tbl_var(l_ind_cnt + 1) := p_config_id;
10202: end if;