[Home] [Help]
545: end if ;
546:
547:
548:
549: oe_debug_pub.add( ' ************* EVALUATE_ATP_ATTRIBUTES *** p_atp_flag ' || p_atp_flag
550: || ' p_atp_components_flag ' || p_atp_components_flag
551: || ' result ' || ' g_atp_flag ' || g_atp_flag
552: || ' v_atp_components_flag ' || v_atp_components_flag , 1 ) ;
553:
589:
590: exception
591: when no_data_found then
592: IF PG_DEBUG <> 0 THEN
593: oe_debug_pub.add('get_cost_group: ' || 'ERROR: Could not fetch the cost_group_id from pjm_project_parameters (NDF)',1);
594: END IF;
595: return(0);
596:
597: end get_cost_group;
707: and oel.inventory_item_id = wfs.primary_item_id
708: and rownum = 1;
709:
710: IF PG_DEBUG <> 0 THEN
711: oe_debug_pub.add ('flow_schedule_exists: ' || 'Flow Schedule Exists!', 1);
712: END IF;
713:
714: return(true); -- Flow Schedule exists
715:
716: exception
717: when no_data_found then
718:
719: IF PG_DEBUG <> 0 THEN
720: oe_debug_pub.add ('flow_schedule_exists: ' || 'Flow Schedules does not exist ', 1);
721: END IF;
722: return(false); -- Flow Schedule does not exist
723:
724: when others then
770: and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_wip
771: and rownum = 1;
772:
773: IF PG_DEBUG <> 0 THEN
774: oe_debug_pub.add ('work_orders_exist: ' || 'Work orders exist! Call wip_holds api', 2);
775: END IF;
776:
777: return(true); -- reservation exists
778:
779: exception
780: when no_data_found then
781:
782: IF PG_DEBUG <> 0 THEN
783: oe_debug_pub.add ('work_orders_exist: ' || 'Work orders do not exist, do not call wip holds api ', 2);
784: END IF;
785: return(false); -- Reservation does not exist
786:
787: when others then
957: and wdd.released_status in ('Y', 'C'); -- Staged [Y], Closed [C]
958:
959: if (lShipConfirmed = 'TRUE' ) then
960: IF PG_DEBUG <> 0 THEN
961: oe_debug_pub.add ('delink_item: ' || 'Error : Config Line has been ship confirmed. Can not delink',1);
962: END IF;
963: xMessageName := 'CTO_DELINK_SHIPPING_ERROR';
964: raise FND_API.G_EXC_ERROR;
965: end if;
966:
967: EXCEPTION
968: when no_data_found then
969: IF PG_DEBUG <> 0 THEN
970: oe_debug_pub.add ('delink_item: ' || 'Config Line does not exist in shipping yet. Delink allowed',2);
971: END IF;
972:
973: END;
974: -- end bugfix 2001824
976:
977: lStmtNumber := 510;
978: lSourceCode := fnd_profile.value('ONT_SOURCE_CODE');
979: IF PG_DEBUG <> 0 THEN
980: oe_debug_pub.add ('delink_item: ' || 'lSourceCode is '||lSourceCode, 2);
981: END IF;
982:
983: --
984: -- Get header id, other info for calling Reservation API
1010: and msi.organization_id = oel.ship_from_org_id
1011: and msi.base_item_id is not NULL;
1012:
1013: IF PG_DEBUG <> 0 THEN
1014: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1015:
1016: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1017:
1018: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1012:
1013: IF PG_DEBUG <> 0 THEN
1014: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1015:
1016: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1017:
1018: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1019:
1020: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1014: oe_debug_pub.add ('delink_item: ' || 'Before WIP parameter check', 2);
1015:
1016: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1017:
1018: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1019:
1020: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1021:
1022: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1016: oe_debug_pub.add ('delink_item: ' || 'Config Line_id is ' || lConfigLineId, 2);
1017:
1018: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1019:
1020: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1021:
1022: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1023: END IF;
1024:
1018: oe_debug_pub.add ('delink_item: ' || 'Config item_id is ' || pConfigId, 2);
1019:
1020: oe_debug_pub.add ('delink_item: ' || 'org id is ' || lOrgId, 2);
1021:
1022: oe_debug_pub.add ('delink_item: ' || 'Sales Order id is ' || lSalesOrderId, 2);
1023: END IF;
1024:
1025: -- begin bugfix 2745590 : Check for routing flag : 1 = Flow Routing
1026:
1037: when no_data_found then
1038: lcfm_routing_flag :=2;
1039: end;
1040: IF PG_DEBUG <> 0 THEN
1041: oe_debug_pub.add ('delink_item: Routing flag ( if 1, flow routing ) = '||to_char(lcfm_routing_flag), 2);
1042: END IF;
1043:
1044: -- Check if flow schedule exist
1045: -- 2784045 : This 'if' condition is decoupled from flow_sch_exist to make the code more efficient
1051: and primary_item_id = pConfigid
1052: and demand_source_type = 2 ;
1053:
1054: IF PG_DEBUG <> 0 THEN
1055: oe_debug_pub.add ('delink_item: Flow Schedule exist ( if > 0 ) = '||to_char(lflow_schedule_exist), 2);
1056: END IF;
1057:
1058: -- Call MRP API unlink_order_line if flow schedule exist and routing is flow_routing
1059: -- 2784045 if ( lcfm_routing_flag = 1 ) and ( lflow_schedule_exist > 0 ) then
1069: );
1070:
1071: if l_status = FND_API.G_RET_STS_ERROR then
1072: IF PG_DEBUG <> 0 THEN
1073: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in MRP_Flow_Schedule_PUB.unlink_order_line with expected error.', 1);
1074: END IF;
1075:
1076: /* Bugfix 2454788 */
1077: raise FND_API.G_EXC_ERROR;
1077: raise FND_API.G_EXC_ERROR;
1078:
1079: elsif l_status = FND_API.G_RET_STS_UNEXP_ERROR then
1080: IF PG_DEBUG <> 0 THEN
1081: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in MRP_Flow_Schedule_PUB.unlink_order_line with unexpected error.', 1);
1082: END IF;
1083: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1084: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1085:
1084: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1085:
1086: elsif l_status = fnd_api.g_ret_sts_success then
1087: IF PG_DEBUG <> 0 THEN
1088: oe_debug_pub.add ('delink_item: ' || 'Removed config line reference from flow schedule', 2);
1089: END IF;
1090: -- Check for l_resv_flag. If it is "N" , set it to "Y" .
1091: -- The Flag is set to Yes, so that we can call the notification later.
1092: If l_resv_flag = 'N' then
1133: xMessageName,
1134: xTableName)
1135: then
1136: IF PG_DEBUG <> 0 THEN
1137: oe_debug_pub.add ('delink_item: ' || 'work orders exist', 2);
1138: END IF;
1139:
1140: -- Added by Renga Kannan on 03/13/2001
1141: -- The Flag is to Yes, So that we can call the notification later in this
1151: x_msg_data => l_msg_data);
1152:
1153: if l_return_status = FND_API.G_RET_STS_ERROR then
1154: IF PG_DEBUG <> 0 THEN
1155: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in wip_so_reservations.respond_to_change_order with expected error.', 1);
1156: END IF;
1157:
1158: /* Bugfix 2454788 */
1159: raise FND_API.G_EXC_ERROR;
1159: raise FND_API.G_EXC_ERROR;
1160:
1161: elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1162: IF PG_DEBUG <> 0 THEN
1163: oe_debug_pub.add ('delink_item: ' || 'Error: Failed in wip_so_reservations.respond_to_change_order with unexpected error.', 1);
1164: END IF;
1165: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1166: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1167: end if;
1167: end if;
1168:
1169: else
1170: IF PG_DEBUG <> 0 THEN
1171: oe_debug_pub.add ('delink_item: ' || 'WOS does not exist', 2);
1172: END IF;
1173: l_return_status := FND_API.G_RET_STS_SUCCESS;
1174: end if;
1175:
1173: l_return_status := FND_API.G_RET_STS_SUCCESS;
1174: end if;
1175:
1176: IF PG_DEBUG <> 0 THEN
1177: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned' || l_return_status, 2);
1178:
1179: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned with msg : ' || l_msg_data, 2);
1180: END IF;
1181:
1175:
1176: IF PG_DEBUG <> 0 THEN
1177: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned' || l_return_status, 2);
1178:
1179: oe_debug_pub.add ('delink_item: ' || 'Resp to SO changes returned with msg : ' || l_msg_data, 2);
1180: END IF;
1181:
1182: lStmtNumber := 518;
1183: if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1199: , p_serial_number => l_dummy_sn
1200: );
1201:
1202: IF PG_DEBUG <> 0 THEN
1203: oe_debug_pub.add ('delink_item: ' || 'After deleting rsv, rsv_id = '||to_char(l_rsv.reservation_id)||', l_status = '||l_status, 2);
1204: END IF;
1205: IF l_status = fnd_api.g_ret_sts_success THEN
1206: IF PG_DEBUG <> 0 THEN
1207: oe_debug_pub.add ('delink_item: ' || 'reservations deleted', 2);
1203: oe_debug_pub.add ('delink_item: ' || 'After deleting rsv, rsv_id = '||to_char(l_rsv.reservation_id)||', l_status = '||l_status, 2);
1204: END IF;
1205: IF l_status = fnd_api.g_ret_sts_success THEN
1206: IF PG_DEBUG <> 0 THEN
1207: oe_debug_pub.add ('delink_item: ' || 'reservations deleted', 2);
1208: END IF;
1209: -- Added by Renga Kannan on 03/13/2001
1210: -- The Flag is to Yes, So that we can call the notification later in this
1211: -- Function.
1213:
1214: ELSE
1215: IF l_msg_count = 1 THEN
1216: IF PG_DEBUG <> 0 THEN
1217: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1218:
1219: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1220: END IF;
1221:
1215: IF l_msg_count = 1 THEN
1216: IF PG_DEBUG <> 0 THEN
1217: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1218:
1219: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1220: END IF;
1221:
1222: CLOSE reservations;
1223:
1226:
1227: ELSE
1228: FOR l_index IN 1..l_msg_count LOOP
1229: IF PG_DEBUG <> 0 THEN
1230: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1231:
1232: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1233: END IF;
1234: END LOOP;
1228: FOR l_index IN 1..l_msg_count LOOP
1229: IF PG_DEBUG <> 0 THEN
1230: oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
1231:
1232: oe_debug_pub.add ('delink_item: ' || 'l_msg_data ='||l_msg_data, 1);
1233: END IF;
1234: END LOOP;
1235:
1236: CLOSE reservations;
1241: END IF;
1242: END LOOP;
1243: CLOSE reservations;
1244: IF PG_DEBUG <> 0 THEN
1245: oe_debug_pub.add ('delink_item: ' || 'After reservations cursor loop', 2);
1246: END IF;
1247:
1248: end if; /* end check l_return_status */
1249:
1289: lStmtNumber := 520;
1290:
1291: IF lcancel_flag <> 'Y' THEN
1292: IF PG_DEBUG <> 0 THEN
1293: oe_debug_pub.add('delink_item: ' || 'The model line is not cancelled',2);
1294: END IF;
1295:
1296:
1297: begin
1317:
1318:
1319: v_aps_version := msc_atp_global.get_aps_version ;
1320:
1321: oe_debug_pub.add('link_config: ' || 'APS version::'|| v_aps_version , 2);
1322:
1323: if( v_aps_version <> 10 ) then
1324:
1325: raise no_data_found ;
1325: raise no_data_found ;
1326:
1327: else
1328:
1329: oe_debug_pub.add('link_config: ' || 'Model Line workflow need not be at WAIT_FOR_CTO as APS version::'|| v_aps_version , 2);
1330:
1331: v_model_line_activity := 'NOT_AT_WAIT_FOR_CTO' ;
1332:
1333:
1341:
1342:
1343: ELSE
1344: IF PG_DEBUG <> 0 THEN
1345: oe_debug_pub.add('delink_item: ' || 'Model Line is cancelled..',2);
1346: END IF;
1347: END IF;
1348:
1349: IF PG_DEBUG <> 0 THEN
1346: END IF;
1347: END IF;
1348:
1349: IF PG_DEBUG <> 0 THEN
1350: oe_debug_pub.add ('delink_item: ' || 'calling OE_CONFIG_UTIL.delink_config',2);
1351: END IF;
1352:
1353: /*------------------------------------------------------+
1354: We are not calling prcess_order api directly because
1377: p_line_id => lConfigLineId,
1378: x_return_status => l_return_status);
1379:
1380: IF PG_DEBUG <> 0 THEN
1381: oe_debug_pub.add ('delink_item: ' || 'oe_config_util.delink_config returned' || l_return_status ,1);
1382: END IF;
1383:
1384: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1385:
1383:
1384: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1385:
1386: IF PG_DEBUG <> 0 THEN
1387: oe_debug_pub.add('delink_item: ' || 'Expected error in OE_CONFIG_UTIL.delink_config procedure....',3);
1388: END IF;
1389:
1390: /* Bugfix 2454788 */
1391: raise FND_API.G_EXC_ERROR;
1392:
1393:
1394: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1395: IF PG_DEBUG <> 0 THEN
1396: oe_debug_pub.add ('delink_item: ' || 'Unexpected Error in oe_config_util.delink_config.');
1397: END IF;
1398:
1399: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1400: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1418: -- update visible demand flag to 'Y' for model and options
1419: --
1420:
1421: IF PG_DEBUG <> 0 THEN
1422: oe_debug_pub.add ('delink_item: ' || 'delink : visible demand flag : before selecting options ', 2);
1423: END IF;
1424:
1425: lStmtNumber := 533;
1426:
1432: --- API was recommended by Rajeev Bellamkonda from OM team. Date 02/02/2001
1433:
1434:
1435: IF PG_DEBUG <> 0 THEN
1436: oe_debug_pub.add ('delink_item: ' || 'l_ml_mo_flag = '|| l_ml_mo_flag, 2);
1437: END IF;
1438:
1439:
1440:
1463: lStmtNumber := 540;
1464: -- What do we do in case of cancellation
1465:
1466:
1467: oe_debug_pub.add( 'model line status ' || v_model_line_activity , 1 ) ;
1468:
1469:
1470:
1471:
1498:
1499: v_aps_version := msc_atp_global.get_aps_version ;
1500:
1501: if( v_aps_version <> 10) then
1502: oe_debug_pub.add('delink_item: ' || 'APS version::'|| v_aps_version , 2);
1503:
1504: if( v_source_Type_code = 'INTERNAL') then
1505:
1506:
1528: end if ;
1529:
1530: else
1531:
1532: oe_debug_pub.add('delink_item: ' || 'APS version::'|| v_aps_version , 2);
1533:
1534: end if; /* reference to get_aps_version */
1535:
1536:
1559: and v_source_type_code = 'INTERNAL' )
1560: THEN
1561:
1562: IF PG_DEBUG <> 0 THEN
1563: oe_debug_pub.add('delink_item: ' || 'Delink Action performed, Need to call notification...',2);
1564: END IF;
1565: lStmtNumber := 600;
1566: SELECT Order_number
1567: INTO l_order_number
1570: WHERE B.line_id = pModelLineId
1571: AND A.Header_id = B.Header_Id;
1572:
1573: IF PG_DEBUG <> 0 THEN
1574: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1575:
1576: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1577:
1578: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1572:
1573: IF PG_DEBUG <> 0 THEN
1574: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1575:
1576: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1577:
1578: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1579: END IF;
1580:
1574: oe_debug_pub.add('delink_item: ' || 'Calling Notification Pkg',2);
1575:
1576: oe_debug_pub.add('delink_item: ' || 'Order Number = '||to_char(l_order_number),3);
1577:
1578: oe_debug_pub.add('delink_item: ' || 'Line id = '||to_char(pModelLineId),3);
1579: END IF;
1580:
1581: pchgtype(1).change_type := CTO_CHANGE_ORDER_PK.DELINK_ACTION;
1582: pchgtype(1).old_value := null;
1595:
1596:
1597: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1598: IF PG_DEBUG <> 0 THEN
1599: oe_debug_pub.add('delink_item: ' || 'Expected error in change_notify procedure....',1);
1600: END IF;
1601:
1602:
1603: /* Bugfix 2454788 */
1604: raise FND_API.G_EXC_ERROR;
1605:
1606: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1607: IF PG_DEBUG <> 0 THEN
1608: oe_debug_pub.add('delink_item: ' || 'Unexpected error occurred in change_notify...',1);
1609: END IF;
1610: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1611: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1612:
1640: (p_line_tbl => p_config_line_arr_delink,
1641: x_return_status => l_return_status_delink);
1642:
1643: IF PG_DEBUG <> 0 THEN
1644: oe_debug_pub.add ('delink_item: ' || 'Return status from MRP_OM_Interface - Delink: '||l_return_status_delink,2);
1645: END IF;
1646:
1647: if l_return_status_delink = FND_API.G_RET_STS_ERROR then
1648: IF PG_DEBUG <> 0 THEN
1645: END IF;
1646:
1647: if l_return_status_delink = FND_API.G_RET_STS_ERROR then
1648: IF PG_DEBUG <> 0 THEN
1649: oe_debug_pub.add ('delink_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with expected error.', 1);
1650: END IF;
1651:
1652: /* Bugfix 2454788 */
1653:
1654: raise FND_API.G_EXC_ERROR;
1655:
1656: elsif l_return_status_delink = FND_API.G_RET_STS_UNEXP_ERROR then
1657: IF PG_DEBUG <> 0 THEN
1658: oe_debug_pub.add ('delink_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with unexpected error.', 1);
1659: END IF;
1660:
1661:
1662: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1671: EXCEPTION
1672:
1673: when NO_DATA_FOUND then
1674: IF PG_DEBUG <> 0 THEN
1675: oe_debug_pub.add('delink_item: ' || 'ERROR: CTOCITMB delink::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
1676: END IF;
1677: if lStmtNumber = 520 then
1678: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1679: xErrorMessage := 'ERROR: CTOCITMB.delink:'||to_char(lStmtNumber)||
1694: p_msg_count => lCount,
1695: p_msg_data => xErrorMessage
1696: );
1697: IF PG_DEBUG <> 0 THEN
1698: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink:' || to_char(lStmtNumber)|| xErrorMessage,1);
1699: END IF;
1700:
1701:
1702: /* Bugfix 2454788 */
1706: fnd_message.parse_encoded( xErrorMessage , v_appl_name, v_error_name ) ;
1707:
1708:
1709: IF PG_DEBUG <> 0 THEN
1710: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: appl_name ' || v_appl_name ,1);
1711: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: error_name ' || v_error_name ,1);
1712: END IF;
1713:
1714:
1707:
1708:
1709: IF PG_DEBUG <> 0 THEN
1710: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: appl_name ' || v_appl_name ,1);
1711: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: error_name ' || v_error_name ,1);
1712: END IF;
1713:
1714:
1715: xErrorMessage := v_error_name;
1719:
1720:
1721: xMessageName := 'CTO_DELINK_ITEM_ERROR' ;
1722: IF PG_DEBUG <> 0 THEN
1723: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xErrorMessage is null ' ,1);
1724: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName is now ' || xMessageName,1);
1725: END IF;
1726:
1727:
1720:
1721: xMessageName := 'CTO_DELINK_ITEM_ERROR' ;
1722: IF PG_DEBUG <> 0 THEN
1723: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xErrorMessage is null ' ,1);
1724: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName is now ' || xMessageName,1);
1725: END IF;
1726:
1727:
1728: end if ;
1728: end if ;
1729:
1730: else
1731: IF PG_DEBUG <> 0 THEN
1732: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink: xMessageName was already set ' || xMessageName,1);
1733: END IF;
1734: end if;
1735:
1736: return(0);
1741: p_msg_count => lCount,
1742: p_msg_data => xErrorMessage
1743: );
1744: IF PG_DEBUG <> 0 THEN
1745: oe_debug_pub.add ('delink_item: ' || 'ERROR: CTOCITMB.delink:' || to_char(lStmtNumber)|| xErrorMessage,1);
1746: END IF;
1747: return(0);
1748:
1749:
1748:
1749:
1750: when OTHERS then
1751: IF PG_DEBUG <> 0 THEN
1752: oe_debug_pub.add('delink_item: ' || 'delink::others::'||sqlerrm, 1);
1753: END IF;
1754: xMessageName := 'CTO_DELINK_ITEM_ERROR';
1755: xErrorMessage := 'ERROR: CTOCITMB.delink:'||to_char(lStmtNumber)||
1756: ':' || substrb(sqlerrm,1,150);
1828:
1829:
1830:
1831: IF PG_DEBUG <> 0 THEN
1832: oe_debug_pub.add ('load_mandatory_comps: ' || ' load mandatory comps ' ,1);
1833: END IF;
1834:
1835: if( pReqDate is not null ) then
1836: IF PG_DEBUG <> 0 THEN
1833: END IF;
1834:
1835: if( pReqDate is not null ) then
1836: IF PG_DEBUG <> 0 THEN
1837: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
1838: END IF;
1839: else
1840: IF PG_DEBUG <> 0 THEN
1841: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed date null ' || pLineId ,1);
1837: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
1838: END IF;
1839: else
1840: IF PG_DEBUG <> 0 THEN
1841: oe_debug_pub.add ('load_mandatory_comps: ' || 'parameters passed date null ' || pLineId ,1);
1842: END IF;
1843: end if ;
1844:
1845:
1855: and oel.ordered_quantity > 0;
1856:
1857: If latpcompflag in ('N' , 'R' ) then -- bugfix 3779636
1858: IF PG_DEBUG <> 0 THEN
1859: 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
1860: END IF;
1861: return(1);
1862: else
1863:
1917: and oel.ordered_quantity > 0;
1918:
1919: if sql%rowcount = 0 then
1920: IF PG_DEBUG <> 0 THEN
1921: oe_debug_pub.add ('load_mandatory_comps: ' || 'Model Row not found',1);
1922: END IF;
1923: raise proc_error;
1924: end if;
1925:
1923: raise proc_error;
1924: end if;
1925:
1926: IF PG_DEBUG <> 0 THEN
1927: oe_debug_pub.add('load_mandatory_comps: ' || 'Model Rows : ' || sql%rowcount, 2);
1928: END IF;
1929:
1930: /*------------------------------------------------+
1931: Insert all oe_order_lines row for this model
2043: -- 3893281
2044: rowcount := SQL%ROWCOUNT;
2045:
2046: IF PG_DEBUG <> 0 THEN
2047: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || rowcount, 2);
2048: END IF;
2049:
2050: END LOOP;
2051:
2050: END LOOP;
2051:
2052: -- 3893281 following code is to debug BET at this point
2053: open debug_bet;
2054: oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
2055: loop
2056: fetch debug_bet
2057: into d_top_bseq_id,
2058: d_bseq_id,
2062: d_line_id,
2063: d_item;
2064: exit when debug_bet%NOTFOUND;
2065: IF PG_DEBUG <> 0 THEN
2066: oe_debug_pub.add (' Top Bill Seq id '||d_top_bseq_id|| ' Bill Seq Id '||d_bseq_id
2067: ||' Org '||d_org_id ||' Sort Order '||d_sort_order
2068: || ' Plan level '||d_plan_level||' Line Id '||d_line_id
2069: || ' Item '||d_item,1);
2070: END IF;
2074:
2075: v_max_level_number := level_number ; /* added by sushant to track max level number */
2076:
2077: IF PG_DEBUG <> 0 THEN
2078: oe_debug_pub.add ('load_mandatory_comps: ' || 'Group_id : '|| xGrpId, 2);
2079: END IF;
2080:
2081:
2082:
2102:
2103: when others then
2104: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2105: IF PG_DEBUG <> 0 THEN
2106: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2107:
2108: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR message : '||sqlerrm,1);
2109: END IF;
2110: return(0);
2104: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2105: IF PG_DEBUG <> 0 THEN
2106: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2107:
2108: oe_debug_pub.add('load_mandatory_comps: ' || 'ERROR message : '||sqlerrm,1);
2109: END IF;
2110: return(0);
2111:
2112: end ;
2117: +-----------------------------------------------------------*/
2118:
2119: level_number := 0;
2120: IF PG_DEBUG <> 0 THEN
2121: oe_debug_pub.add('load_mandatory_comps: ' || 'Explosion loop' , 2);
2122: END IF;
2123:
2124: << Explode_loop >>
2125: LOOP
2265:
2266: -- start bugfix 2425667
2267: lrowcount := SQL%ROWCOUNT;
2268: IF PG_DEBUG <> 0 THEN
2269: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || lrowcount, 2);
2270: END IF;
2271: -- oe_debug_pub.add('Row Count : ' || SQL%ROWCOUNT);
2272: -- end bugfix 2425667
2273:
2267: lrowcount := SQL%ROWCOUNT;
2268: IF PG_DEBUG <> 0 THEN
2269: oe_debug_pub.add ('load_mandatory_comps: ' || 'Row Count : ' || lrowcount, 2);
2270: END IF;
2271: -- oe_debug_pub.add('Row Count : ' || SQL%ROWCOUNT);
2272: -- end bugfix 2425667
2273:
2274:
2275: /* continue exploding atleast till max level number */
2340:
2341:
2342:
2343: IF PG_DEBUG <> 0 THEN
2344: oe_debug_pub.add ('load_mandatory_comps_pds: ' || ' load mandatory comps PDS ' ,1);
2345: END IF;
2346:
2347: if( pReqDate is not null ) then
2348: IF PG_DEBUG <> 0 THEN
2345: END IF;
2346:
2347: if( pReqDate is not null ) then
2348: IF PG_DEBUG <> 0 THEN
2349: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
2350: END IF;
2351: else
2352: IF PG_DEBUG <> 0 THEN
2353: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed date null ' || pLineId ,1);
2349: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed ' || pLineId || ' date ' || to_char( pReqDate ) ,1);
2350: END IF;
2351: else
2352: IF PG_DEBUG <> 0 THEN
2353: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'parameters passed date null ' || pLineId ,1);
2354: END IF;
2355: end if ;
2356:
2357: lStmtNumber := 599;
2364: and oel.ordered_quantity > 0;
2365:
2366: If latpcompflag in ( 'N' , 'R' ) then -- 3779636
2367: IF PG_DEBUG <> 0 THEN
2368: 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
2369: END IF;
2370: return(1);
2371: else
2372:
2415: and oel.ordered_quantity > 0;
2416:
2417: if sql%rowcount = 0 then
2418: IF PG_DEBUG <> 0 THEN
2419: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Model Row not found',1);
2420: END IF;
2421: raise proc_error;
2422: end if;
2423:
2421: raise proc_error;
2422: end if;
2423:
2424: IF PG_DEBUG <> 0 THEN
2425: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'Model Rows : ' || sql%rowcount, 2);
2426: END IF;
2427:
2428: /*------------------------------------------------+
2429: Insert all oe_order_lines row for this model
2483:
2484: rowcount := SQL%ROWCOUNT;
2485:
2486: IF PG_DEBUG <> 0 THEN
2487: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Row Count : ' || rowcount, 2);
2488: END IF;
2489:
2490: END LOOP;
2491:
2491:
2492: v_max_level_number := level_number ;
2493:
2494: IF PG_DEBUG <> 0 THEN
2495: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Group_id : '|| xGrpId, 2);
2496: END IF;
2497:
2498:
2499: begin
2514:
2515: when others then
2516: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2517: IF PG_DEBUG <> 0 THEN
2518: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2519:
2520: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR message : '||sqlerrm,1);
2521: END IF;
2522: return(0);
2516: xErrorMessage := 'ERROR: CTOCITMB:'||to_char(lStmtNumber)||': other error ';
2517: IF PG_DEBUG <> 0 THEN
2518: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR: others exception while executing stmt ' ||to_char(lStmtNumber),1);
2519:
2520: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'ERROR message : '||sqlerrm,1);
2521: END IF;
2522: return(0);
2523:
2524: end ;
2529: +-----------------------------------------------------------*/
2530:
2531: level_number := 0;
2532: IF PG_DEBUG <> 0 THEN
2533: oe_debug_pub.add('load_mandatory_comps_pds: ' || 'Explosion loop' , 2);
2534: END IF;
2535:
2536: << Explode_loop >>
2537: LOOP
2664:
2665:
2666: lrowcount := SQL%ROWCOUNT;
2667: IF PG_DEBUG <> 0 THEN
2668: oe_debug_pub.add ('load_mandatory_comps_pds: ' || 'Row Count : ' || lrowcount, 2);
2669: END IF;
2670:
2671:
2672:
2780:
2781:
2782:
2783: IF PG_DEBUG <> 0 THEN
2784: oe_debug_pub.add ('load_mandatory_components: ' || ' load mandatory components ' ,1);
2785: END IF;
2786:
2787: if( pReqDate is not null ) then
2788: IF PG_DEBUG <> 0 THEN
2785: END IF;
2786:
2787: if( pReqDate is not null ) then
2788: IF PG_DEBUG <> 0 THEN
2789: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
2790: END IF;
2791: else
2792: IF PG_DEBUG <> 0 THEN
2793: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index date null ' || p_model_index ,1);
2789: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
2790: END IF;
2791: else
2792: IF PG_DEBUG <> 0 THEN
2793: oe_debug_pub.add ('load_mandatory_components: ' || 'parameters passed index date null ' || p_model_index ,1);
2794: END IF;
2795: end if ;
2796:
2797:
2806:
2807:
2808: If latpcompflag in ( 'N' , 'R' ) then -- bugfix 3779636
2809: IF PG_DEBUG <> 0 THEN
2810: 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
2811: END IF;
2812: return(1);
2813:
2814: else
2993:
2994: rowcount := SQL%ROWCOUNT;
2995:
2996: IF PG_DEBUG <> 0 THEN
2997: oe_debug_pub.add ('load_mandatory_components: ' || 'Row Count : ' || rowcount, 2);
2998: END IF;
2999:
3000: END LOOP;
3001:
3000: END LOOP;
3001:
3002: -- 3893281 following code is to debug BET at this point
3003: open debug_bet;
3004: oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
3005: loop
3006: fetch debug_bet
3007: into d_top_bseq_id,
3008: d_bseq_id,
3012: d_line_id,
3013: d_item;
3014: exit when debug_bet%NOTFOUND;
3015: IF PG_DEBUG <> 0 THEN
3016: oe_debug_pub.add (' Top Bill Seq id '||d_top_bseq_id|| ' Bill Seq Id '||d_bseq_id
3017: ||' Org '||d_org_id ||' Sort Order '||d_sort_order
3018: || ' Plan level '||d_plan_level||' Line Id '||d_line_id
3019: || ' Item '||d_item,1);
3020: END IF;
3026:
3027: v_max_level_number := level_number ; /* change made by sushant to track max level number */
3028:
3029: IF PG_DEBUG <> 0 THEN
3030: oe_debug_pub.add ('load_mandatory_components: ' || 'Group_id : '|| xGrpId, 2);
3031: END IF;
3032:
3033: delete from bom_explosion_temp
3034: where group_id = lGroupid2;
3060: +-----------------------------------------------------------*/
3061:
3062: level_number := 0;
3063: IF PG_DEBUG <> 0 THEN
3064: oe_debug_pub.add('load_mandatory_components: ' || 'Explosion loop' , 2);
3065: END IF;
3066:
3067: << Explode_loop >>
3068: LOOP
3201: -- standard items
3202: and msi.pick_components_flag <> 'Y' ;
3203:
3204: IF PG_DEBUG <> 0 THEN
3205: oe_debug_pub.add('load_mandatory_components: ' || 'Row Count : ' || SQL%ROWCOUNT);
3206: END IF;
3207:
3208: /* continue exploding atleast till max level number */
3209: IF SQL%ROWCOUNT = 0 AND level_number >= v_max_level_number THEN
3264:
3265:
3266:
3267: IF PG_DEBUG <> 0 THEN
3268: oe_debug_pub.add ('load_mandatory_components_pds: ' || ' load mandatory components PDS ' ,1);
3269: END IF;
3270:
3271: if( pReqDate is not null ) then
3272: IF PG_DEBUG <> 0 THEN
3269: END IF;
3270:
3271: if( pReqDate is not null ) then
3272: IF PG_DEBUG <> 0 THEN
3273: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
3274: END IF;
3275: else
3276: IF PG_DEBUG <> 0 THEN
3277: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index date null ' || p_model_index ,1);
3273: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index ' || p_model_index || ' date ' || to_char( pReqDate ) ,1);
3274: END IF;
3275: else
3276: IF PG_DEBUG <> 0 THEN
3277: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'parameters passed index date null ' || p_model_index ,1);
3278: END IF;
3279: end if ;
3280:
3281:
3288:
3289:
3290: If latpcompflag in ( 'N' , 'R' ) then -- 3779636
3291: IF PG_DEBUG <> 0 THEN
3292: 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
3293: END IF;
3294: return(1);
3295:
3296: else
3438:
3439: rowcount := SQL%ROWCOUNT;
3440:
3441: IF PG_DEBUG <> 0 THEN
3442: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'Row Count : ' || rowcount, 2);
3443: END IF;
3444:
3445: END LOOP;
3446:
3448:
3449: v_max_level_number := level_number ;
3450:
3451: IF PG_DEBUG <> 0 THEN
3452: oe_debug_pub.add ('load_mandatory_components_pds: ' || 'Group_id : '|| xGrpId, 2);
3453: END IF;
3454:
3455: delete from bom_explosion_temp
3456: where group_id = lGroupid2;
3478: +-----------------------------------------------------------*/
3479:
3480: level_number := 0;
3481: IF PG_DEBUG <> 0 THEN
3482: oe_debug_pub.add('load_mandatory_components_pds: ' || 'Explosion loop' , 2);
3483: END IF;
3484:
3485: << Explode_loop >>
3486: LOOP
3610: and msi.bom_item_type in (1,2) /*Model or Option Class */
3611: and msi.pick_components_flag <> 'Y' ;
3612:
3613: IF PG_DEBUG <> 0 THEN
3614: oe_debug_pub.add('load_mandatory_components_pds: ' || 'Row Count : ' || SQL%ROWCOUNT);
3615: END IF;
3616:
3617:
3618: IF SQL%ROWCOUNT = 0 AND level_number >= v_max_level_number THEN
3690:
3691: begin
3692:
3693: IF PG_DEBUG <> 0 THEN
3694: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Entered Get Mandatory Components ',1);
3695: END IF;
3696:
3697:
3698: -- Bugfix 3779636
3701:
3702: l_inv_ctp := fnd_profile.value('INV_CTP');
3703:
3704: IF PG_DEBUG <> 0 THEN
3705: oe_debug_pub.add('Get_Mandatory_Components: INV_CTP ( 4 = PDS ): ' || l_inv_ctp,1);
3706: END IF;
3707:
3708:
3709:
3719:
3720: if( p_ship_set.requested_ship_date.exists(lModelIndex)) then
3721:
3722: IF PG_DEBUG <> 0 THEN
3723: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date exists ',1);
3724: END IF;
3725:
3726:
3727: if( p_ship_set.requested_ship_date(lModelIndex) is not null ) then
3725:
3726:
3727: if( p_ship_set.requested_ship_date(lModelIndex) is not null ) then
3728: IF PG_DEBUG <> 0 THEN
3729: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date exists ' || p_ship_set.requested_ship_date(lModelIndex) ,1);
3730: END IF;
3731:
3732: else
3733:
3731:
3732: else
3733:
3734: IF PG_DEBUG <> 0 THEN
3735: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested ship date is null ' ,1);
3736: END IF;
3737: end if ;
3738:
3739: end if ;
3742:
3743: if( p_ship_set.requested_arrival_date.exists(lModelIndex)) then
3744:
3745: IF PG_DEBUG <> 0 THEN
3746: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date exists ',1);
3747: END IF;
3748:
3749:
3750: if( p_ship_set.requested_arrival_date(lModelIndex) is not null ) then
3748:
3749:
3750: if( p_ship_set.requested_arrival_date(lModelIndex) is not null ) then
3751: IF PG_DEBUG <> 0 THEN
3752: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date exists ' || p_ship_set.requested_arrival_date(lModelIndex) ,1);
3753: END IF;
3754:
3755: else
3756:
3754:
3755: else
3756:
3757: IF PG_DEBUG <> 0 THEN
3758: oe_debug_pub.add('Get_Mandatory_Components: ' || ' requested arrival date is null ' ,1);
3759: END IF;
3760: end if ;
3761:
3762: end if ;
3779: xMessageName,
3780: xTableName);
3781: if lStatus <> 1 then
3782: IF PG_DEBUG <> 0 THEN
3783: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3784: ' Load_mandatory_comps_pds returned with Error ',1);
3785: END IF;
3786: raise proc_error;
3787: end if;
3799: xTableName);
3800:
3801: if lStatus <> 1 then
3802: IF PG_DEBUG <> 0 THEN
3803: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_comps returned with Error ',1);
3804: END IF;
3805:
3806: raise proc_error;
3807: end if;
3815:
3816: else
3817:
3818:
3819: oe_debug_pub.add(' Load_mandatory_comps returned without processing
3820: as model qty is 0 ',1);
3821: return 1 ;
3822:
3823: end if;
3842:
3843:
3844:
3845: IF PG_DEBUG <> 0 THEN
3846: oe_debug_pub.add('Get_Mandatory_Components: ' || ' will be calling load_mandatory_components ' , 2);
3847: END IF;
3848:
3849: v_request_date := nvl( p_ship_set.requested_ship_date(lModelIndex),
3850: p_ship_set.requested_arrival_date(lModelIndex)) ;
3857: -- Bugfix 3779636
3858: if l_inv_ctp = 4 then
3859:
3860: IF PG_DEBUG <> 0 THEN
3861: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3862: ' now calling load_mandatory_components_pds ' , 2);
3863: END IF;
3864:
3865: lStatus := load_mandatory_components_pds(p_ship_set,
3869: xErrorMessage,
3870: xMessageName,
3871: xTableName);
3872: IF PG_DEBUG <> 0 THEN
3873: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3874: 'Returned from load_mandatory_components_pds with '||lStatus, 2);
3875: END IF;
3876:
3877: if lStatus <> 1 then
3875: END IF;
3876:
3877: if lStatus <> 1 then
3878: IF PG_DEBUG <> 0 THEN
3879: oe_debug_pub.add('Get_Mandatory_Components: ' ||
3880: ' Load_mandatory_components_pds returned with Error ',1);
3881: END IF;
3882: raise proc_error;
3883: end if;
3887:
3888:
3889:
3890: IF PG_DEBUG <> 0 THEN
3891: oe_debug_pub.add('Get_Mandatory_Components: ' || ' now calling load_mandatory_components ' , 2);
3892: END IF;
3893:
3894:
3895:
3900: xErrorMessage,
3901: xMessageName,
3902: xTableName);
3903: IF PG_DEBUG <> 0 THEN
3904: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Returned from load_mand_comps with '||lStatus, 2);
3905: END IF;
3906: if lStatus <> 1 then
3907: IF PG_DEBUG <> 0 THEN
3908: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_components returned with Error ',1);
3904: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Returned from load_mand_comps with '||lStatus, 2);
3905: END IF;
3906: if lStatus <> 1 then
3907: IF PG_DEBUG <> 0 THEN
3908: oe_debug_pub.add('Get_Mandatory_Components: ' || ' Load_mandatory_components returned with Error ',1);
3909: END IF;
3910: raise proc_error;
3911: end if;
3912:
3919:
3920: else
3921:
3922:
3923: oe_debug_pub.add(' Load_mandatory_components returned without processing
3924: as model qty is 0 ',1);
3925: return 1 ;
3926:
3927: end if;
3951: where group_id = lGrpId
3952: and ( Line_id is null or top_bill_sequence_id = -1) ; /* BugFix 2897132 */
3953:
3954: IF PG_DEBUG <> 0 THEN
3955: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' Lines exploded ' || chk, 1);
3956: END IF;
3957:
3958: lStmtNumber := 730;
3959:
3957:
3958: lStmtNumber := 730;
3959:
3960: IF PG_DEBUG <> 0 THEN
3961: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Going to Print Mandatory Comps ', 1);
3962: END IF;
3963:
3964:
3965: i := 1;
3972:
3973:
3974: lStmtNumber := 740;
3975: IF PG_DEBUG <> 0 THEN
3976: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' Before calling MRP API::i='||to_char(i),1);
3977: END IF;
3978: MRP_ATP_PVT.assign_Atp_input_rec(p_ship_set,
3979: lModelIndex,
3980: x_smc_rec,
3979: lModelIndex,
3980: x_smc_rec,
3981: lStatus );
3982: IF PG_DEBUG <> 0 THEN
3983: oe_debug_pub.add ('Get_Mandatory_Components: ' || ' MRP API returns with '||lStatus, 2);
3984: END IF;
3985:
3986: if lStatus <> FND_API.G_RET_STS_SUCCESS then
3987: xErrorMessage := 'assign_Atp_input_rec returned with Error';
4001: x_smc_rec.attribute_01(i) := nxtrec.wip_supply_type; -- bugfix 3254039
4002:
4003:
4004: IF PG_DEBUG <> 0 THEN
4005: oe_debug_pub.add('Get_Mandatory_Components: ' || 'Mand comp ' || i ||
4006: ' inv ' || x_smc_rec.inventory_item_id(i) ||
4007: ' eq ' || x_smc_rec.quantity_ordered(i) ||
4008: ' id ' || x_smc_rec.identifier(i) ||
4009: ' wip supply type ' || x_smc_rec.attribute_01(i) , 1 ) ; -- bugfix 3254039
4017: end loop;
4018:
4019:
4020: IF PG_DEBUG <> 0 THEN
4021: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Done Print Mandatory Comps ', 1);
4022: END IF;
4023:
4024:
4025: /*------------------------------------+
4040: xErrorMessage := 'CTOCITMB :'|| to_char(lStmtNumber) || ': ' || xErrorMessage;
4041: return(0);
4042: when others then
4043: IF PG_DEBUG <> 0 THEN
4044: oe_debug_pub.add ('Get_Mandatory_Components: ' || 'Others exception is get_mand_comp::'||substrb(sqlerrm,1,150));
4045: END IF;
4046: return(0);
4047:
4048: end Get_Mandatory_Components;
4166: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
4167: */
4168:
4169:
4170: oe_debug_pub.add('Create_Item: ' || 'p_mode ' || p_mode ,2);
4171:
4172: if( p_mode = 'PRECONFIG' ) then
4173:
4174: lValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
4175:
4176: else
4177:
4178: IF PG_DEBUG <> 0 THEN
4179: oe_debug_pub.add('Create_Item: ' || 'Before getting validation org',2);
4180: END IF;
4181:
4182: /*
4183: BUG:3484511
4193:
4194:
4195:
4196: IF PG_DEBUG <> 0 THEN
4197: oe_debug_pub.add('Create_Item: ' || 'Going to fetch Validation Org ' ,2);
4198: END IF;
4199:
4200:
4201: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
4211: raise FND_API.G_EXC_ERROR;
4212: end if;
4213:
4214: IF PG_DEBUG <> 0 THEN
4215: oe_debug_pub.add('Create_Item: ' || 'Validation Org is :' || lValidationOrg,2);
4216: END IF;
4217:
4218:
4219: --
4221: -- in all orgs. If it is a new item, create a new item in all orgs
4222: --
4223:
4224: IF PG_DEBUG <> 0 THEN
4225: oe_debug_pub.add('Create_Item: ' || 'config_item_id::'||to_char(pConfigId),2);
4226: END IF;
4227:
4228: IF (pConfigId is NULL) THEN
4229:
4251: raise FND_API.G_EXC_ERROR;
4252: end if;
4253:
4254: IF PG_DEBUG <> 0 THEN
4255: oe_debug_pub.add('Create_Item: ' || 'config Segment is : ' || lConfigSegName,2 );
4256:
4257: oe_debug_pub.add('Create_Item: ' || 'Number Method is : ' || lNumberMethod,2 );
4258: END IF;
4259:
4253:
4254: IF PG_DEBUG <> 0 THEN
4255: oe_debug_pub.add('Create_Item: ' || 'config Segment is : ' || lConfigSegName,2 );
4256:
4257: oe_debug_pub.add('Create_Item: ' || 'Number Method is : ' || lNumberMethod,2 );
4258: END IF;
4259:
4260: /* BUG #2172057 Change for preconfigure bom by Sushant Sawant */
4261: if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
4264: lNumberMethod := 1 ;
4265: end if;
4266:
4267: IF PG_DEBUG <> 0 THEN
4268: oe_debug_pub.add('Create_Item: ' || 'Number Method Changed due to preconfigure bom restriction of append with sequence number: ' ||
4269: lNumberMethod,2 );
4270: END IF;
4271:
4272: end if ;
4428: param4 => NULL,
4429: param5 => NULL
4430: );
4431: IF PG_DEBUG <> 0 THEN
4432: oe_debug_pub.add( 'Precfg case - Custom Item number is ' || new_item_num ,2 );
4433: END IF;
4434: else
4435: new_item_num := BOMPCFGI.user_item_number(pLineId);
4436: IF PG_DEBUG <> 0 THEN
4433: END IF;
4434: else
4435: new_item_num := BOMPCFGI.user_item_number(pLineId);
4436: IF PG_DEBUG <> 0 THEN
4437: oe_debug_pub.add('Create_Item: ' || 'Item number is ' || new_item_num ,2 );
4438: END IF;
4439: end if;
4440: end if; -- End check for number method
4441:
4458: seg(11), seg(12), seg(13), seg(14), seg(15),
4459: seg(16), seg(17), seg(18), seg(19), seg(20)) = 1
4460: then
4461: IF PG_DEBUG <> 0 THEN
4462: oe_debug_pub.add( 'Create_Item: '|| 'generating unique name' ,2 );
4463: END IF;
4464: select to_char(mtl_system_items_B_S.nextval)
4465: into lNextNum
4466: from dual;
4473: for nxt_rec in lDupItem
4474: loop
4475:
4476: IF PG_DEBUG <> 0 THEN
4477: oe_debug_pub.add('Create_Item: ' || 'generating unique name' ,2 );
4478: END IF;
4479: select to_char(mtl_system_items_B_S.nextval)
4480: into lNextNum
4481: from dual;
4484: end loop;
4485: */
4486:
4487: IF PG_DEBUG <> 0 THEN
4488: oe_debug_pub.add('Create_Item: ' || 'item numbering segment is '|| app_col_ind ,2 );
4489:
4490: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4491: END IF;
4492:
4486:
4487: IF PG_DEBUG <> 0 THEN
4488: oe_debug_pub.add('Create_Item: ' || 'item numbering segment is '|| app_col_ind ,2 );
4489:
4490: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4491: END IF;
4492:
4493: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4494:
4492:
4493: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4494:
4495: IF PG_DEBUG <> 0 THEN
4496: oe_debug_pub.add('Create_Item: ' || 'new pConfigId is '||to_char(pConfigId),2);
4497: END IF;
4498:
4499: ELSE
4500: -- config id is populated in config_item_id
4545: from mtl_system_items msi
4546: where inventory_item_id = pConfigId;
4547:
4548: IF PG_DEBUG <> 0 THEN
4549: oe_debug_pub.add('Create_Item: ' || 'matched pConfigId is '||to_char(pConfigId),2);
4550: END IF;
4551:
4552: END IF; -- matched/new item
4553:
4563: --xTableName := 'MTL_SYSTEM_ITEMS';
4564: lStmtNumber := 80;
4565:
4566: /* need to add attribute controlled statement for preconfigured item */
4567: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4568:
4569: if( p_mode = 'AUTOCONFIG' OR pLineId <> v_ato_line_id ) then
4570:
4571: oe_debug_pub.add('Create_Item: ' || 'came into AUTOCONFIG ' ,2);
4567: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4568:
4569: if( p_mode = 'AUTOCONFIG' OR pLineId <> v_ato_line_id ) then
4570:
4571: oe_debug_pub.add('Create_Item: ' || 'came into AUTOCONFIG ' ,2);
4572: /*
4573: tracking_quantity_ind TRACK,
4574: 4 ont_pricing_qty_source PRCQTY, approval_status
4575: */
5255:
5256:
5257:
5258: IF PG_DEBUG <> 0 THEN
5259: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5260:
5261: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sql%rowcount||' rows in mtl_system_items_b',2);
5262: END IF;
5263: -- added as part of bugfix 1811007
5257:
5258: IF PG_DEBUG <> 0 THEN
5259: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5260:
5261: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sql%rowcount||' rows in mtl_system_items_b',2);
5262: END IF;
5263: -- added as part of bugfix 1811007
5264:
5265:
5266:
5267:
5268: else
5269:
5270: oe_debug_pub.add('Create_Item: ' || 'going to call create_precon figured item ' || ' line ' || pLineId ||
5271: ' Model ' || pModelId ||
5272: ' Config ' || pConfigId ||
5273: ' item type ' || lItemType ,2);
5274:
5282:
5283: lStmtNumber := 81;
5284:
5285: IF PG_DEBUG <> 0 THEN
5286: oe_debug_pub.add('Create_Item: ' || 'Opening cursor c_get_org_id.. ' ,3);
5287: END IF;
5288:
5289: BEGIN
5290: FOR v_get_org_id in c_get_org_id
5289: BEGIN
5290: FOR v_get_org_id in c_get_org_id
5291: LOOP
5292: IF PG_DEBUG <> 0 THEN
5293: oe_debug_pub.add('Create_Item: ' || 'Fetched Org Id '|| v_get_org_id.src_org_id,3);
5294: END IF;
5295: fnd_attached_documents2_pkg.copy_attachments (
5296: X_from_entity_name => 'MTL_SYSTEM_ITEMS',
5297: X_from_pk1_value => v_get_org_id.src_org_id,
5312: X_request_id => fnd_global.USER_ID,
5313: X_automatically_added_flag => NULL
5314: );
5315: IF PG_DEBUG <> 0 THEN
5316: oe_debug_pub.add('Create_Item: ' || 'Done copy_attachment ',3);
5317: END IF;
5318: END LOOP;
5319: END;
5320: -- End bugfix 2157740
5328: -- This is done to prevent calculation of upper level config's if lower level
5329: -- configs have errored out.
5330:
5331: IF PG_DEBUG <> 0 THEN
5332: oe_debug_pub.add ('Create_Item: ' || 'gWtStatus = '||CTO_CONFIG_ITEM_PK.gWtStatus|| ' and gVolStatus = '||CTO_CONFIG_ITEM_PK.gVolStatus,1);
5333: END IF;
5334:
5335: if CTO_CONFIG_ITEM_PK.gWtStatus = 0 or -- 0 means success
5336: CTO_CONFIG_ITEM_PK.gVolStatus = 0
5369: -- Eg. C_OPTION 7 (kg) and C_OPTION 9 (Lbs).
5370:
5371: IF o_weight_uom IS NULL THEN
5372: IF PG_DEBUG <> 0 THEN
5373: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM is null for the base model.',2);
5374:
5375: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5376: END IF;
5377:
5371: IF o_weight_uom IS NULL THEN
5372: IF PG_DEBUG <> 0 THEN
5373: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM is null for the base model.',2);
5374:
5375: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5376: END IF;
5377:
5378: begin
5379: select uom_code
5404: -- Eg. C_OPTION 7 (cubic meter) and C_OPTION 9 (cubic feet).
5405:
5406: IF o_volume_uom IS NULL THEN
5407: IF PG_DEBUG <> 0 THEN
5408: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM is null for the base model.',2);
5409:
5410: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5411: END IF;
5412:
5406: IF o_volume_uom IS NULL THEN
5407: IF PG_DEBUG <> 0 THEN
5408: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM is null for the base model.',2);
5409:
5410: oe_debug_pub.add('Create_Item: ' || '=>Trying to get the base UOM ...',2);
5411: END IF;
5412:
5413: begin
5414: select uom_code
5443: -- the wt or vol for the top level config.
5444:
5445:
5446: IF PG_DEBUG <> 0 THEN
5447: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5448:
5449: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5450:
5451: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5445:
5446: IF PG_DEBUG <> 0 THEN
5447: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5448:
5449: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5450:
5451: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5452:
5453: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5447: oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
5448:
5449: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5450:
5451: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5452:
5453: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5454:
5455: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5449: oe_debug_pub.add('Create_Item: ' || '=>Line Id : '||pLineId,2);
5450:
5451: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5452:
5453: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5454:
5455: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5456: END IF;
5457:
5451: oe_debug_pub.add('Create_Item: ' || '=>OE Validn Orgn Id : '||lValidationOrg,2);
5452:
5453: oe_debug_pub.add('Create_Item: ' || '=>Weight UOM: '||o_weight_uom,2);
5454:
5455: oe_debug_pub.add('Create_Item: ' || '=>Volume UOM: '||o_volume_uom,2);
5456: END IF;
5457:
5458: Ato_Weight_Volume(
5459: p_ato_line_id => pLineId,
5469: raise FND_API.G_EXC_ERROR;
5470: end if;
5471:
5472: IF PG_DEBUG <> 0 THEN
5473: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5474:
5475: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5476:
5477: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5471:
5472: IF PG_DEBUG <> 0 THEN
5473: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5474:
5475: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5476:
5477: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5478: END IF;
5479:
5473: oe_debug_pub.add('Create_Item: ' || 'Successfully executed Ato_Weight_Volume API with following outputs..',2);
5474:
5475: oe_debug_pub.add('Create_Item: ' || '=>Weight : '||o_weight||' '||o_weight_uom,2);
5476:
5477: oe_debug_pub.add('Create_Item: ' || '=>Volume : '||o_volume||' '||o_volume_uom,2);
5478: END IF;
5479:
5480:
5481: -- Update the config's weight and volume.
5531:
5532: -- end bugfix 1811007
5533:
5534:
5535: oe_debug_pub.add('Create_Item: ' || 'going to insert:mtl_system_items_tl',2);
5536:
5537: /*----------------------------------------+
5538: R11.5 MLS
5539: +----------------------------------------*/
5597: and t.language = l.language_code );
5598:
5599:
5600: IF PG_DEBUG <> 0 THEN
5601: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_tl',2);
5602: END IF;
5603:
5604:
5605:
5623: -- call API to copy sourcing rules from model item
5624: -- to config item
5625: --
5626: IF PG_DEBUG <> 0 THEN
5627: 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);
5628: END IF;
5629: CTO_UTILITY_PK.Create_Sourcing_Rules(
5630: pModelItemId => pModelId,
5631: pConfigId => pConfigId,
5635: x_msg_data => xMsgData);
5636:
5637: IF (lReturnStatus = fnd_api.G_RET_STS_ERROR) THEN
5638: IF PG_DEBUG <> 0 THEN
5639: oe_debug_pub.add ('Create_Item: ' || 'Create_Sourcing_Rules returned with expected error.');
5640: END IF;
5641: raise FND_API.G_EXC_ERROR;
5642:
5643: ELSIF (lReturnStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
5641: raise FND_API.G_EXC_ERROR;
5642:
5643: ELSIF (lReturnStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
5644: IF PG_DEBUG <> 0 THEN
5645: oe_debug_pub.add ('Create_Item: ' || 'Create_Sourcing_Rules returned with unexp error.');
5646: END IF;
5647: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5648:
5649: END IF;
5662: pConfigId => pConfigId);
5663:
5664: IF lStatus <> 1 THEN
5665: IF PG_DEBUG <> 0 THEN
5666: oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Order_Lines function',1);
5667: END IF;
5668: raise FND_API.G_EXC_ERROR;
5669: END IF;
5670:
5681: pConfigId => pConfigId);
5682:
5683: IF lStatus <> 1 THEN
5684: IF PG_DEBUG <> 0 THEN
5685: oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Src_Orgs function',1);
5686: END IF;
5687: raise FND_API.G_EXC_ERROR;
5688: END IF;
5689:
5713: p_mode );
5714:
5715: if lStatus <> 1 then
5716: IF PG_DEBUG <> 0 THEN
5717: oe_debug_pub.add ('Create_Item: ' || 'Failed in create_data function',1);
5718: END IF;
5719: raise FND_API.G_EXC_ERROR;
5720: end if;
5721:
5719: raise FND_API.G_EXC_ERROR;
5720: end if;
5721:
5722: IF PG_DEBUG <> 0 THEN
5723: oe_debug_pub.add ('Create_Item: ' || 'Success in create_data function',1);
5724: END IF;
5725:
5726: return(1);
5727:
5728: EXCEPTION
5729:
5730: WHEN NO_DATA_FOUND THEN
5731: IF PG_DEBUG <> 0 THEN
5732: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::ndf::'||to_char(lStmtNumber)||sqlerrm,1);
5733: END IF;
5734: CTO_MSG_PUB.Count_And_Get(
5735: p_msg_count => xMsgCount,
5736: p_msg_data => xMsgData
5738: return(0);
5739:
5740: WHEN FND_API.G_EXC_ERROR THEN
5741: IF PG_DEBUG <> 0 THEN
5742: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::exp error::'||to_char(lStmtNumber)||sqlerrm,1);
5743: END IF;
5744: CTO_MSG_PUB.Count_And_Get(
5745: p_msg_count => xMsgCount,
5746: p_msg_data => xMsgData
5748: return(0);
5749:
5750: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5751: IF PG_DEBUG <> 0 THEN
5752: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::unexp error::'||to_char(lStmtNumber)||sqlerrm,1);
5753: END IF;
5754: CTO_MSG_PUB.Count_And_Get(
5755: p_msg_count => xMsgCount,
5756: p_msg_data => xMsgData
5758: return(0);
5759:
5760: WHEN OTHERS THEN
5761: IF PG_DEBUG <> 0 THEN
5762: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item::others::'||to_char(lStmtNumber)||sqlerrm,1);
5763: END IF;
5764: CTO_MSG_PUB.Count_And_Get(
5765: p_msg_count => xMsgCount,
5766: p_msg_data => xMsgData
5886: status := 0;
5887:
5888:
5889: IF PG_DEBUG <> 0 THEN
5890: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with p_ato_line_id = '||p_ato_line_id,1);
5891: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with pConfigId = '||pConfigId,1);
5892:
5893: END IF;
5894:
5887:
5888:
5889: IF PG_DEBUG <> 0 THEN
5890: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with p_ato_line_id = '||p_ato_line_id,1);
5891: oe_debug_pub.add ('Create_Item: ' || 'Inside Ato_Weight_Volume API with pConfigId = '||pConfigId,1);
5892:
5893: END IF;
5894:
5895: lStmtNumber := 1205;
5899:
5900: if CTO_CONFIG_ITEM_PK.gWtStatus = 0 then
5901:
5902: IF PG_DEBUG <> 0 THEN
5903: oe_debug_pub.add ('Create_Item: ' || '===>weight_uom = '||weight_uom,1);
5904: END IF;
5905:
5906: FOR aw IN ato_weight(p_ato_line_id)
5907: loop
5906: FOR aw IN ato_weight(p_ato_line_id)
5907: loop
5908:
5909: IF PG_DEBUG <> 0 THEN
5910: oe_debug_pub.add ('Create_Item: ' || '===>aw.weight = '||aw.weight||' uom = '||aw.uom||' for item_id '||aw.inventory_item_id,1);
5911: END IF;
5912: -- Bugfix 2301167: Added item_id as a parameter so that item specific conversions take precedence if defined.
5913: lweight := CTO_UTILITY_PK.convert_uom(
5914: from_uom => aw.uom,
5916: quantity => aw.weight,
5917: item_id => aw.inventory_item_id);
5918: if lweight = 0 then
5919: IF PG_DEBUG <> 0 THEN
5920: oe_debug_pub.add ('Create_Item: ' || '===>convert_uom returned 0(error). Not calculating weight. Check if all conversions have been defined.', 1);
5921: END IF;
5922: -- we don't want to update the weight of the config incorrectly, so set it to 0.
5923: weight := 0;
5924:
5933: end if;
5934:
5935:
5936: IF PG_DEBUG <> 0 THEN
5937: oe_debug_pub.add ('Create_Item: ' || '---------------------',1);
5938: END IF;
5939:
5940: lStmtNumber := 1210;
5941:
5944:
5945: if CTO_CONFIG_ITEM_PK.gVolStatus = 0 then
5946:
5947: IF PG_DEBUG <> 0 THEN
5948: oe_debug_pub.add ('Create_Item: ' || '===>volume_uom = '||volume_uom,1);
5949: END IF;
5950:
5951: FOR av IN ato_volume(p_ato_line_id)
5952: loop
5950:
5951: FOR av IN ato_volume(p_ato_line_id)
5952: loop
5953: IF PG_DEBUG <> 0 THEN
5954: oe_debug_pub.add ('Create_Item: ' || '===>av.volume = '||av.volume||' uom = '||av.uom||' for item_id '||av.inventory_item_id,1);
5955: END IF;
5956: -- Bugfix 2301167: Added item_id as a parameter so that item specific conversions take precedence if defined.
5957: lvolume := CTO_UTILITY_PK.convert_uom(
5958: from_uom => av.uom,
5960: quantity => av.volume,
5961: item_id => av.inventory_item_id);
5962: if lvolume = 0 then
5963: IF PG_DEBUG <> 0 THEN
5964: oe_debug_pub.add ('Create_Item: ' || '===>convert_uom returned 0(error). Not calculating volume. Check if all conversions have been defined.', 1);
5965: END IF;
5966: -- we don't want to update the volume of the config incorrectly, so set it to 0.
5967: volume := 0;
5968:
5978:
5979: EXCEPTION
5980: WHEN OTHERS THEN
5981: IF PG_DEBUG <> 0 THEN
5982: oe_debug_pub.add ('Create_Item: ' || 'ERROR: Ato_Weight_Volume::others::'||to_char(lStmtNumber)||':-'||sqlerrm,1);
5983: END IF;
5984: CTO_MSG_PUB.Count_And_Get(
5985: p_msg_count => lMsgCount,
5986: p_msg_data => lMsgData
6153: */
6154:
6155:
6156: IF PG_DEBUG <> 0 THEN
6157: oe_debug_pub.add('Create_Item: ' || 'Going to fetch Validation Org ' ,2);
6158: END IF;
6159:
6160:
6161: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
6166: end if ;
6167:
6168:
6169: IF PG_DEBUG <> 0 THEN
6170: oe_debug_pub.add('Create_Item: ' || 'After getting validation org',2);
6171: END IF;
6172:
6173: if lVAlidationOrg = -99 then -- bugfix 2646849
6174: cto_msg_pub.cto_message('BOM','CTO_VALIDATION_ORG_NOT_SET');
6183:
6184: lStmtNumber := 205;
6185:
6186: IF PG_DEBUG <> 0 THEN
6187: oe_debug_pub.add ('Create_Item: ' || 'Validation Org is :' || lValidationOrg ,2);
6188: END IF;
6189:
6190: insert into MTL_PENDING_ITEM_STATUS (
6191: inventory_item_id,
6231:
6232:
6233:
6234: IF PG_DEBUG <> 0 THEN
6235: oe_debug_pub.add('Create_Item: ' || 'after insert:MTL_PENDING_ITEM_STATUS',2);
6236: END IF;
6237:
6238: /*-------------------------------------------+
6239: Insert Item revision information
6298:
6299:
6300:
6301: IF PG_DEBUG <> 0 THEN
6302: oe_debug_pub.add('Create_Item_Data: ' || 'after insert:MTL_ITEM_REVISIONS',2);
6303: END IF;
6304:
6305: EXCEPTION
6306: when NON_KEY_PRESERVED_ERROR then
6305: EXCEPTION
6306: when NON_KEY_PRESERVED_ERROR then
6307:
6308: IF PG_DEBUG <> 0 THEN
6309: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6310: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6311: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6312: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6313: END IF;
6306: when NON_KEY_PRESERVED_ERROR then
6307:
6308: IF PG_DEBUG <> 0 THEN
6309: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6310: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6311: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6312: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6313: END IF;
6314:
6307:
6308: IF PG_DEBUG <> 0 THEN
6309: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6310: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6311: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6312: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6313: END IF;
6314:
6315: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6308: IF PG_DEBUG <> 0 THEN
6309: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. Hence, calling populate_item_revision.. ',2);
6310: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Model ' || pModelId ,2);
6311: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Line ' || pLineId ,2);
6312: oe_debug_pub.add('Create_item_data: NON_KEY_PRESERVED_ERROR exception. model Config ' || pConfigId ,2);
6313: END IF;
6314:
6315: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6316:
6314:
6315: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus);
6316:
6317: IF (xReturnStatus <> fnd_api.G_RET_STS_SUCCESS) THEN
6318: oe_debug_pub.add('Create_item_data : failed in populate_item_revision : '||substrb(sqlerrm,1,60),2);
6319: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6320:
6321: ELSE
6322: IF PG_DEBUG <> 0 THEN
6319: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6320:
6321: ELSE
6322: IF PG_DEBUG <> 0 THEN
6323: oe_debug_pub.add('Create_item_data: done populate_item_revision successfully.',2);
6324: END IF;
6325: END IF;
6326:
6327: when OTHERS then
6324: END IF;
6325: END IF;
6326:
6327: when OTHERS then
6328: oe_debug_pub.add('Create_item_data: Failed while inserting into MTL_ITEM_REVISIONS: '||substrb(sqlerrm,1,60),2);
6329: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6330: END;*/
6331:
6332: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus); --Bugfix 6063990
6331:
6332: populate_item_revision (pConfigId, pModelId, pLineId, xReturnStatus); --Bugfix 6063990
6333:
6334: IF (xReturnStatus <> fnd_api.G_RET_STS_SUCCESS) THEN
6335: oe_debug_pub.add('Create_item_data : failed in populate_item_revision : '||substrb(sqlerrm,1,60),2);
6336: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6337:
6338: ELSE
6339: IF PG_DEBUG <> 0 THEN
6336: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6337:
6338: ELSE
6339: IF PG_DEBUG <> 0 THEN
6340: oe_debug_pub.add('Create_item_data: done populate_item_revision successfully.',2);
6341: END IF;
6342: END IF;
6343: --End Bugfix 6063990.
6344:
6410: if( l_rt_cicd_summary.cost_organization_id.count > 0 ) then
6411: for i in l_rt_cicd_summary.cost_organization_id.first..l_rt_cicd_summary.cost_organization_id.last
6412: loop
6413:
6414: oe_debug_pub.add( i || ') ' || 'Cost Header Info: ' ||
6415: ' cst org ' || l_rt_cicd_summary.cost_organization_id(i) ||
6416: ' cst id ' || l_rt_cicd_summary.cost_type_id(i) ||
6417: ' m cost ' || l_rt_cicd_summary.material_cost(i) ||
6418: ' moh cost ' || l_rt_cicd_summary.material_overhead_cost(i) ||
6427: + l_rt_cicd_summary.overhead_cost(i) ;
6428:
6429:
6430:
6431: oe_debug_pub.add( ' item cost ' || l_rt_cicd_summary.item_cost(i) , 1 );
6432:
6433:
6434: end loop ;
6435:
6436:
6437: else
6438:
6439:
6440: oe_debug_pub.add( ' no new item cost records for ' || pConfigId , 1 );
6441:
6442: end if;
6443:
6444: /*-------------------------------------------------------+
6576: lCnt := sql%rowcount;
6577: --Bugfix 6717614
6578:
6579: IF PG_DEBUG <> 0 THEN
6580: oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert:CST_ITEM_COSTS inserted '|| to_char(lCnt),2);
6581: END IF;
6582:
6583:
6584: if( l_rt_cicd_summary.cost_type_id.count> 0 ) then
6602: and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
6603:
6604:
6605: IF PG_DEBUG <> 0 THEN
6606: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
6607: END IF;
6608: else
6609:
6610: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
6606: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
6607: END IF;
6608: else
6609:
6610: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
6611:
6612: end if;
6613:
6614:
6630: exception
6631: when no_data_found then
6632:
6633: IF PG_DEBUG <> 0 THEN
6634: oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
6635: oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
6636: END IF;
6637:
6638: l_cto_cost_type_id := 7 ;
6631: when no_data_found then
6632:
6633: IF PG_DEBUG <> 0 THEN
6634: oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
6635: oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
6636: END IF;
6637:
6638: l_cto_cost_type_id := 7 ;
6639:
6642: from cst_cost_types
6643: where cost_type_id = l_cto_cost_type_id ;
6644:
6645: IF PG_DEBUG <> 0 THEN
6646: oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || l_cto_cost_type_id ||
6647: ' has cost_type = -' || v_cto_cost_type_name || '- delimiter(-cost-)' ,2);
6648: END IF;
6649: exception
6650: when no_data_found then
6648: END IF;
6649: exception
6650: when no_data_found then
6651: IF PG_DEBUG <> 0 THEN
6652: oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
6653: END IF;
6654: cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
6655: raise FND_API.G_EXC_ERROR;
6656: when others then
6792:
6793:
6794:
6795: IF PG_DEBUG <> 0 THEN
6796: oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert UD cost into CTO cost type inserted '|| to_char(sql%rowcount),2);
6797:
6798: END IF;
6799:
6800:
6819: and cost_type_id = l_cto_cost_type_id ;
6820:
6821:
6822: IF PG_DEBUG <> 0 THEN
6823: oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
6824: END IF;
6825:
6826: else
6827:
6824: END IF;
6825:
6826: else
6827:
6828: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
6829:
6830: end if;
6831:
6832:
6952:
6953:
6954:
6955: IF PG_DEBUG <> 0 THEN
6956: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_item_cost_details inserted '||to_char(sql%rowcount),2);
6957: END IF;
6958:
6959:
6960:
7078:
7079:
7080:
7081: IF PG_DEBUG <> 0 THEN
7082: oe_debug_pub.add('Create_Item: ' || 'after insert UD cost into CICD for CTO cost type inserted '||to_char(sql%rowcount),2);
7083: END IF;
7084:
7085:
7086: --Bugfix 6717614
7085:
7086: --Bugfix 6717614
7087: if lCnt = 0 then
7088: IF PG_DEBUG <> 0 THEN
7089: oe_debug_pub.add('Create_Item: ' || 'No need to populate csc and cec as no new record inserted in cic and cicd',2);
7090: END IF;
7091: else
7092:
7093: --begin Bugfix 6363308
7092:
7093: --begin Bugfix 6363308
7094: lStmtNumber := 231;
7095: IF PG_DEBUG <> 0 THEN
7096: oe_debug_pub.add('Create_Item: ' || 'going to populate data in csc and cec if frozen cost of config is not zero',2);
7097: END IF;
7098:
7099: OPEN get_orgs_with_frozen_cost(pconfigId);
7100: loop
7103: , v_item_cost_frozen;
7104: EXIT WHEN get_orgs_with_frozen_cost%NOTFOUND;
7105:
7106: IF PG_DEBUG <> 0 THEN
7107: oe_debug_pub.add('Create_Item: ' || 'fetched org '||to_char(v_organization_id),2);
7108: oe_debug_pub.add('Create_Item: ' || 'frozen cost for config '||to_char(pConfigId) || 'is: ' || to_char(v_item_cost_frozen),2);
7109: END IF;
7110:
7111: IF (v_item_cost_frozen <> 0) THEN
7104: EXIT WHEN get_orgs_with_frozen_cost%NOTFOUND;
7105:
7106: IF PG_DEBUG <> 0 THEN
7107: oe_debug_pub.add('Create_Item: ' || 'fetched org '||to_char(v_organization_id),2);
7108: oe_debug_pub.add('Create_Item: ' || 'frozen cost for config '||to_char(pConfigId) || 'is: ' || to_char(v_item_cost_frozen),2);
7109: END IF;
7110:
7111: IF (v_item_cost_frozen <> 0) THEN
7112:
7111: IF (v_item_cost_frozen <> 0) THEN
7112:
7113: lStmtNumber := 232;
7114: IF PG_DEBUG <> 0 THEN
7115: oe_debug_pub.add('Create_Item: ' || 'Came inside if. v_item_cost_frozen is not zero',2);
7116: oe_debug_pub.add('Create_Item: ' || 'Fetching value from sequence',2);
7117: END IF;
7118:
7119: Select cst_lists_s.nextval
7112:
7113: lStmtNumber := 232;
7114: IF PG_DEBUG <> 0 THEN
7115: oe_debug_pub.add('Create_Item: ' || 'Came inside if. v_item_cost_frozen is not zero',2);
7116: oe_debug_pub.add('Create_Item: ' || 'Fetching value from sequence',2);
7117: END IF;
7118:
7119: Select cst_lists_s.nextval
7120: into l_cost_update
7128: AND INVENTORY_ITEM_ID = pConfigId
7129: AND COST_TYPE_ID = 1;
7130:
7131: IF PG_DEBUG <> 0 THEN
7132: oe_debug_pub.add('Create_Item: ' || 'Updated cost_update_id to value ' || to_char(l_cost_update),2);
7133: END IF;
7134:
7135: lStmtNumber := 234;
7136: IF PG_DEBUG <> 0 THEN
7133: END IF;
7134:
7135: lStmtNumber := 234;
7136: IF PG_DEBUG <> 0 THEN
7137: oe_debug_pub.add('Create_Item: ' || 'Inserting records in csc and cec',2);
7138: END IF;
7139:
7140: INSERT INTO cst_standard_costs
7141: (cost_update_id,
7164: AND INVENTORY_ITEM_ID = pConfigId
7165: AND COST_TYPE_ID = 1;
7166:
7167: IF PG_DEBUG <> 0 THEN
7168: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_standard_costs ' || sql%rowcount ,2);
7169: END IF;
7170:
7171: lStmtNumber := 235;
7172:
7198: AND COST_TYPE_ID = 1
7199: GROUP BY cost_element_id;
7200:
7201: IF PG_DEBUG <> 0 THEN
7202: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_elemental_costs ' || sql%rowcount ,2);
7203: END IF;
7204: END IF; -- v_item_cost_frozen <> 0
7205: end loop;
7206:
7265: );
7266:
7267: IF (l_layer_id = 0) THEN
7268: IF PG_DEBUG <> 0 THEN
7269: oe_debug_pub.add('Create_Item: ' || 'CST function create_layer returned with error '||to_char(x_err_num)||', '||x_msg_name||', '||
7270: lMsgData||'for '||to_char(v_layer.org_id)||', '||to_char(v_layer.cost_group_id),1);
7271: END IF;
7272: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7273: ELSE
7271: END IF;
7272: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7273: ELSE
7274: IF PG_DEBUG <> 0 THEN
7275: oe_debug_pub.add('Create_Item: ' || 'Inserted row into cql for '||to_char(l_layer_id)||', '||to_char(v_layer.org_id)||', '||
7276: to_char(v_layer.cost_group_id),1);
7277: END IF;
7278: END IF;
7279:
7280: END LOOP;
7281: END IF; /* End bugfix 1573941 */
7282:
7283: IF PG_DEBUG <> 0 THEN
7284: oe_debug_pub.add('Create_Item: ' || 'after insert:cst_quantity_layers ',2);
7285: END IF;
7286:
7287:
7288: /*--------------------------------------------------------+
7338: where inventory_item_id = pConfigId
7339: and organization_id = lValidationOrg);
7340:
7341: IF PG_DEBUG <> 0 THEN
7342: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_DESCR_ELEMENT_VALUES',2);
7343: END IF;
7344:
7345:
7346: /*--------------------------------------+
7413:
7414:
7415:
7416: IF PG_DEBUG <> 0 THEN
7417: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES',2);
7418: END IF;
7419:
7420: -- Start Bugfix 2197842
7421:
7474:
7475:
7476:
7477: IF PG_DEBUG <> 0 THEN
7478: oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES FOR DEFAULT CATEGORIES',2);
7479: END IF;
7480:
7481: -- End Bugfix 2197842
7482:
7534:
7535:
7536:
7537: IF PG_DEBUG <> 0 THEN
7538: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_related_items',2);
7539: END IF;
7540:
7541: /*--------------------------------------------------+
7542: Copy substitute inventories
7644:
7645:
7646:
7647: IF PG_DEBUG <> 0 THEN
7648: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_item_sub_inventories',2);
7649: END IF;
7650:
7651: /*--------------------------------------+
7652: Copy secondary locators
7704:
7705:
7706:
7707: IF PG_DEBUG <> 0 THEN
7708: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_secondary_locators',2);
7709: END IF;
7710:
7711: /*----------------------------------------+
7712: Copy cross references
7822: ,NULL --ATTRIBUTE_CATEGORY
7823: );
7824:
7825: IF PG_DEBUG <> 0 THEN
7826: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_b',2);
7827: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_b'||sql%rowcount,2);
7828:
7829: END IF;
7830:
7823: );
7824:
7825: IF PG_DEBUG <> 0 THEN
7826: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_b',2);
7827: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_b'||sql%rowcount,2);
7828:
7829: END IF;
7830:
7831: INSERT INTO mtl_cross_references_tl (
7863:
7864:
7865:
7866: IF PG_DEBUG <> 0 THEN
7867: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_tl',2);
7868: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_tl'||sql%rowcount,2);
7869:
7870: END IF;
7871:
7864:
7865:
7866: IF PG_DEBUG <> 0 THEN
7867: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_tl',2);
7868: oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_tl'||sql%rowcount,2);
7869:
7870: END IF;
7871:
7872:
7926: and organization_id = bcso.organization_id);
7927:
7928:
7929: IF PG_DEBUG <> 0 THEN
7930: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_sub_defaults',2);
7931: END IF;
7932:
7933: -- end 2461574
7934: -- start 2786934
7985:
7986:
7987:
7988: IF PG_DEBUG <> 0 THEN
7989: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_loc_defaults',2);
7990: END IF;
7991:
7992: -- end 2786934
7993:
8026:
8027:
8028: IF PG_DEBUG <> 0 THEN
8029:
8030: oe_debug_pub.add('Create_Item: ' || 'inserted '||sql%rowcount||' in mtl_abc_assignments',2);
8031:
8032: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_abc_assignments',2);
8033: END IF;
8034:
8028: IF PG_DEBUG <> 0 THEN
8029:
8030: oe_debug_pub.add('Create_Item: ' || 'inserted '||sql%rowcount||' in mtl_abc_assignments',2);
8031:
8032: oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_abc_assignments',2);
8033: END IF;
8034:
8035: --end bugfix 2828588
8036:
8040: EXCEPTION
8041:
8042: WHEN NO_DATA_FOUND THEN
8043: IF PG_DEBUG <> 0 THEN
8044: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::ndf::'||to_char(lStmtNumber)||sqlerrm,1);
8045: END IF;
8046: CTO_MSG_PUB.Count_And_Get(
8047: p_msg_count => lMsgCount,
8048: p_msg_data => lMsgData
8050: return(0);
8051:
8052: WHEN FND_API.G_EXC_ERROR THEN
8053: IF PG_DEBUG <> 0 THEN
8054: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::exp error::'||to_char(lStmtNumber)||sqlerrm,1);
8055: END IF;
8056: CTO_MSG_PUB.Count_And_Get(
8057: p_msg_count => lMsgCount,
8058: p_msg_data => lMsgData
8060: return(0);
8061:
8062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8063: IF PG_DEBUG <> 0 THEN
8064: oe_debug_pub.add ('Create_Item: ' || 'ERROR: create_item_data::unexp error::'||to_char(lStmtNumber)||sqlerrm,1);
8065: END IF;
8066: CTO_MSG_PUB.Count_And_Get(
8067: p_msg_count => lMsgCount,
8068: p_msg_data => lMsgData
8071:
8072:
8073: WHEN OTHERS THEN
8074: IF PG_DEBUG <> 0 THEN
8075: oe_debug_pub.add ('Create_Item: ' || 'create_item_data::others::'||to_char(lStmtNumber)||sqlerrm,1);
8076: END IF;
8077: CTO_MSG_PUB.Count_And_Get(
8078: p_msg_count => lMsgCount,
8079: p_msg_data => lMsgData
8177: x_config_line_id => l_config_line_id,
8178: x_config_item_id => l_config_id) = TRUE)
8179: THEN
8180: IF PG_DEBUG <> 0 THEN
8181: oe_debug_pub.add('link_item: ' || 'Config Line Exists, do not link', 1);
8182: END IF;
8183: return(1);
8184: END IF;
8185:
8184: END IF;
8185:
8186:
8187: IF PG_DEBUG <> 0 THEN
8188: oe_debug_pub.add ('link_item: ' || 'calling oe_line_util.query_row , 2');
8189: END IF;
8190:
8191: Select concatenated_segments
8192: into l_model_tab(1).ordered_item
8203: --
8204: -- selecting all rows to be updated into l_upd_line_tbl
8205: --
8206: IF PG_DEBUG <> 0 THEN
8207: oe_debug_pub.add ('link_item: ' || 'link : visible demand flag : before selecting options ', 2);
8208: END IF;
8209:
8210:
8211: /* BUG#2234858 Sushant added this for Drop Ship project
8212: ** Need to provide this functionality for non drop shipped items only
8213: */
8214:
8215: IF PG_DEBUG <> 0 THEN
8216: oe_debug_pub.add('link_item: ' || 'Before visible demand flag souce type code = '||l_line_rec.source_type_code,1);
8217: END IF;
8218: if( l_line_rec.source_type_code = 'INTERNAL') then
8219:
8220:
8225: -- The previous process order API is replaced with this update statement for performance purpose.
8226:
8227:
8228: IF PG_DEBUG <> 0 THEN
8229: oe_debug_pub.add('link_item: ' || 'Before updating visible demand Flag...',1);
8230:
8231: oe_debug_pub.add('link_item: ' || ' ATO line id = '||to_char(plineid),1);
8232: END IF;
8233:
8227:
8228: IF PG_DEBUG <> 0 THEN
8229: oe_debug_pub.add('link_item: ' || 'Before updating visible demand Flag...',1);
8230:
8231: oe_debug_pub.add('link_item: ' || ' ATO line id = '||to_char(plineid),1);
8232: END IF;
8233:
8234: UPDATE OE_ORDER_LINES_ALL
8235: SET visible_demand_flag = 'N'
8235: SET visible_demand_flag = 'N'
8236: WHERE ato_line_id = pLineId;
8237:
8238: IF PG_DEBUG <> 0 THEN
8239: oe_debug_pub.add('link_item: ' || 'after updating visible demand flag..',1);
8240:
8241: oe_debug_pub.add('link_item: ' || 'No of rows updated = '||sql%rowcount,1);
8242: END IF;
8243:
8237:
8238: IF PG_DEBUG <> 0 THEN
8239: oe_debug_pub.add('link_item: ' || 'after updating visible demand flag..',1);
8240:
8241: oe_debug_pub.add('link_item: ' || 'No of rows updated = '||sql%rowcount,1);
8242: END IF;
8243:
8244: -- End of adition on 04/04/2001
8245:
8329: l_model_tab(1).dep_plan_required_flag := l_line_rec.dep_plan_required_flag;
8330:
8331:
8332: IF PG_DEBUG <> 0 THEN
8333: oe_debug_pub.add ('link_item: ' || 'linking item ' || l_model_tab(1).ordered_item, 2);
8334: END IF;
8335:
8336: lStmtNumber := 400;
8337: /*
8357:
8358: lStmtNumber := 410;
8359:
8360: IF PG_DEBUG <> 0 THEN
8361: oe_debug_pub.add ('link_item: ' || 'calling oe_order_grp.Process_order', 2);
8362: END IF;
8363: -- Change for MOAC
8364: -- Changing the public process order API to group API
8365: oe_order_grp.Process_Order (
8391: x_action_request_tbl => l_action_request_tbl
8392: );
8393:
8394: IF PG_DEBUG <> 0 THEN
8395: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_return_status, 1);
8396:
8397: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_msg_data, 2);
8398: END IF;
8399:
8393:
8394: IF PG_DEBUG <> 0 THEN
8395: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_return_status, 1);
8396:
8397: oe_debug_pub.add ('link_item: ' || 'Process_order returned ' || l_msg_data, 2);
8398: END IF;
8399:
8400: if (l_return_status = FND_API.G_RET_STS_ERROR) then
8401: IF PG_DEBUG <> 0 THEN
8398: END IF;
8399:
8400: if (l_return_status = FND_API.G_RET_STS_ERROR) then
8401: IF PG_DEBUG <> 0 THEN
8402: oe_debug_pub.add ('link_item: ' || 'Process_order returned expected error :'||l_msg_data,1);
8403: END IF;
8404: raise FND_API.G_EXC_ERROR;
8405:
8406: elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
8404: raise FND_API.G_EXC_ERROR;
8405:
8406: elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
8407: IF PG_DEBUG <> 0 THEN
8408: oe_debug_pub.add ('link_item: ' || 'Process_order returned unexpected error :'||l_msg_data,1);
8409: END IF;
8410: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8411:
8412: end if;
8493:
8494: elsif(l_stat =0) then
8495: -- do nothing
8496: IF PG_DEBUG <> 0 THEN
8497: oe_debug_pub.add ('link_item: ' || 'no model line present in bcod table to create a CID,not an error', 3);
8498: END IF;
8499:
8500: end if;
8501:
8519: index_counter_link:= 1;
8520: p_config_line_arr_link(index_counter_link) := l_config_line_id ;
8521:
8522: IF PG_DEBUG <> 0 THEN
8523: oe_debug_pub.add('link_item: ' || 'Start MRP package for link', 1);
8524: END IF;
8525:
8526: MRP_OM_API_PK.MRP_OM_Interface
8527: (p_line_tbl => p_config_line_arr_link,
8527: (p_line_tbl => p_config_line_arr_link,
8528: x_return_status => l_return_status_link);
8529:
8530: IF PG_DEBUG <> 0 THEN
8531: oe_debug_pub.add ('link_item: ' || 'Return status from MRP_OM_Interface - Link: '||l_return_status_link,2);
8532: END IF;
8533:
8534: if l_return_status_link = FND_API.G_RET_STS_ERROR then
8535: IF PG_DEBUG <> 0 THEN
8532: END IF;
8533:
8534: if l_return_status_link = FND_API.G_RET_STS_ERROR then
8535: IF PG_DEBUG <> 0 THEN
8536: oe_debug_pub.add ('link_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with expected error.', 1);
8537: END IF;
8538: raise FND_API.G_EXC_ERROR;
8539:
8540: elsif l_return_status_link = FND_API.G_RET_STS_UNEXP_ERROR then
8538: raise FND_API.G_EXC_ERROR;
8539:
8540: elsif l_return_status_link = FND_API.G_RET_STS_UNEXP_ERROR then
8541: IF PG_DEBUG <> 0 THEN
8542: oe_debug_pub.add ('link_item: ' || 'Failed in MRP_OM_API_PK.mrp_om_interface with unexpected error.', 1);
8543: END IF;
8544: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8545: end if;
8546:
8554: --
8555:
8556: if (CTO_CONFIG_BOM_PK.gApplyHold = 'Y') then
8557: IF PG_DEBUG <> 0 THEN
8558: oe_debug_pub.add ('CTOCITMB:Need to put hold on config line '||l_config_line_id);
8559: END IF;
8560:
8561: /* Put the config line on Hold. But first, check to see if a hold already exists
8562: on this line */
8570: ,x_msg_data => l_x_error_msg);
8571:
8572: IF (l_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
8573: IF PG_DEBUG <> 0 THEN
8574: oe_debug_pub.add('CTOCITMB:Failed in Check Holds with expected error.' ,1);
8575: END IF;
8576: raise FND_API.G_EXC_ERROR;
8577:
8578: ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8576: raise FND_API.G_EXC_ERROR;
8577:
8578: ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8579: IF PG_DEBUG <> 0 THEN
8580: oe_debug_pub.add('CTOCITMB:Failed in Check Holds with unexpected error.' ,1);
8581: END IF;
8582: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8583:
8584: ELSE
8582: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8583:
8584: ELSE
8585: IF PG_DEBUG <> 0 THEN
8586: oe_debug_pub.add('CTOCITMB:Success in Check Holds.' ,1);
8587: END IF;
8588: if l_x_hold_result_out = FND_API.G_FALSE then
8589:
8590:
8599:
8600: v_hold_id := 55 ;
8601:
8602: IF PG_DEBUG <> 0 THEN
8603: oe_debug_pub.add('CTOCITMB: Going to apply normal hold .' ,1);
8604: END IF;
8605:
8606: else
8607:
8607:
8608: v_hold_id := 61 ; /* New Hold Id for Create Supply Activity Hold , Should be changed from 1063 to 61 after Gayatri gives the script*/
8609:
8610: IF PG_DEBUG <> 0 THEN
8611: oe_debug_pub.add('CTOCITMB: Going to apply create supply activity hold .' ,1);
8612: END IF;
8613:
8614: end if;
8615:
8616:
8617:
8618:
8619: IF PG_DEBUG <> 0 THEN
8620: oe_debug_pub.add('CTOCITMB:Calling OM api to apply hold.' ,1);
8621: END IF;
8622:
8623: l_hold_source_rec.hold_entity_code := 'O';
8624: l_hold_source_rec.hold_id := v_hold_id ;
8634: , x_msg_data => l_msg_data);
8635:
8636: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
8637: IF PG_DEBUG <> 0 THEN
8638: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with expected error.' ,1);
8639: END IF;
8640: raise FND_API.G_EXC_ERROR;
8641:
8642: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8640: raise FND_API.G_EXC_ERROR;
8641:
8642: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
8643: IF PG_DEBUG <> 0 THEN
8644: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with unexpected error.' ,1);
8645: END IF;
8646: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8647: END IF;
8648:
8646: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8647: END IF;
8648:
8649: IF PG_DEBUG <> 0 THEN
8650: oe_debug_pub.add('CTOCITMB: An Exception Hold applied to config line.' ,1);
8651: END IF;
8652: cto_msg_pub.cto_message('BOM','CTO_ORDER_LINE_EXCPN_HOLD');
8653:
8654: else
8652: cto_msg_pub.cto_message('BOM','CTO_ORDER_LINE_EXCPN_HOLD');
8653:
8654: else
8655: IF PG_DEBUG <> 0 THEN
8656: oe_debug_pub.add('CTOCITMB: Line is already on Hold.' ,1);
8657: END IF;
8658: end if;
8659: END IF;
8660: end if;
8668: EXCEPTION
8669:
8670: WHEN NO_DATA_FOUND THEN
8671: IF PG_DEBUG <> 0 THEN
8672: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::ndf::'||to_char(lStmtNumber)||'::'||sqlerrm, 1);
8673: END IF;
8674: CTO_MSG_PUB.Count_And_Get(
8675: p_msg_count => l_Msg_Count,
8676: p_msg_data => l_Msg_Data
8678: return(0);
8679:
8680: WHEN FND_API.G_EXC_ERROR THEN
8681: IF PG_DEBUG <> 0 THEN
8682: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
8683: END IF;
8684: CTO_MSG_PUB.Count_And_Get(
8685: p_msg_count => l_Msg_Count,
8686: p_msg_data => l_Msg_Data
8688: return(0);
8689:
8690: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8691: IF PG_DEBUG <> 0 THEN
8692: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
8693: END IF;
8694: CTO_MSG_PUB.Count_And_Get(
8695: p_msg_count => l_Msg_Count,
8696: p_msg_data => l_Msg_Data
8698: return(0);
8699:
8700: WHEN OTHERS THEN
8701: IF PG_DEBUG <> 0 THEN
8702: oe_debug_pub.add ('link_item: ' || 'ERROR: link_item::others::'||to_char(lStmtNumber)||'::'||sqlerrm, 1);
8703: END IF;
8704: CTO_MSG_PUB.Count_And_Get(
8705: p_msg_count => l_Msg_Count,
8706: p_msg_data => l_Msg_Data
8766:
8767: BEGIN
8768:
8769: IF PG_DEBUG <> 0 THEN
8770: oe_debug_pub.add( 'Check_dup_item: '|| 'Checking for duplicate item name.. ' ,2 );
8771: END IF;
8772:
8773: sql_str := 'select 1 from mtl_system_items msi where 1=1 ';
8774:
8853: end if;
8854:
8855:
8856: IF PG_DEBUG <> 0 THEN
8857: oe_debug_pub.add( 'Check_dup_item: '||'sql_str : '||sql_str, 3);
8858: END IF;
8859:
8860: lcursor := dbms_sql.open_cursor;
8861:
8929: dbms_sql.close_cursor(lcursor);
8930:
8931: if rows_processed > 0 then
8932: IF PG_DEBUG <> 0 THEN
8933: oe_debug_pub.add( 'Check_dup_item: '||'Duplicate item name found.');
8934: END IF;
8935: return 1;
8936: else
8937: IF PG_DEBUG <> 0 THEN
8934: END IF;
8935: return 1;
8936: else
8937: IF PG_DEBUG <> 0 THEN
8938: oe_debug_pub.add( 'Check_dup_item: '||'Duplicate item name NOT found.');
8939: END IF;
8940: return 0;
8941: end if;
8942:
8961:
8962: stmtnum := 1;
8963:
8964: IF PG_DEBUG <> 0 THEN --Bugfix 6063990
8965: oe_debug_pub.add('Inside populate_item_revision');
8966: END IF;
8967: --sql_stmt := 'SELECT MTL_ITEM_REVISIONS_B_S.nextval FROM dual';
8968:
8969: --IF PG_DEBUG <> 0 THEN
8966: END IF;
8967: --sql_stmt := 'SELECT MTL_ITEM_REVISIONS_B_S.nextval FROM dual';
8968:
8969: --IF PG_DEBUG <> 0 THEN
8970: -- oe_debug_pub.add(sql_stmt,3);
8971: --END IF;
8972:
8973: --EXECUTE IMMEDIATE sql_stmt INTO x_item_rev_seq ;
8974:
8972:
8973: --EXECUTE IMMEDIATE sql_stmt INTO x_item_rev_seq ;
8974:
8975: --IF PG_DEBUG <> 0 THEN
8976: -- oe_debug_pub.add(' x_item_rev_seq = '|| x_item_rev_seq ,1);
8977:
8978: -- END IF;
8979:
8980: --3340844 joing to mp2 and distinct has been removed
9026:
9027:
9028:
9029: /*IF PG_DEBUG <> 0 THEN
9030: oe_debug_pub.add(sql_stmt,3);
9031: END IF;*/ --Bugfix 6063990
9032:
9033: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId, x_item_rev_seq, pModelId, pLineId, pConfigId;
9034: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pModelId, pLineId, pConfigId;
9034: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pModelId, pLineId, pConfigId;
9035: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, gUserId, gUserId, gLoginId,pConfigId,pConfigId; Bugfix 6063990
9036:
9037: IF PG_DEBUG <> 0 THEN
9038: oe_debug_pub.add('Inserted into mtl_item_revisions_b for item ' || pConfigId || ' rows ' || SQL%ROWCOUNT );
9039: END IF;
9040:
9041:
9042:
9099:
9100:
9101:
9102: /*IF PG_DEBUG <> 0 THEN
9103: oe_debug_pub.add(sql_stmt,3);
9104: END IF;*/ --Bugfix 6063990
9105:
9106: --EXECUTE IMMEDIATE sql_stmt USING pConfigId, x_item_rev_seq, gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId, x_item_rev_seq;
9107:
9107:
9108: --EXECUTE IMMEDIATE sql_stmt USING pConfigId,gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId,pConfigId; Bugfix 6063990
9109:
9110: IF PG_DEBUG <> 0 THEN
9111: oe_debug_pub.add('Inserted into mtl_system_items_tl.');
9112: END IF;
9113:
9114: EXCEPTION
9115: when OTHERS then
9112: END IF;
9113:
9114: EXCEPTION
9115: when OTHERS then
9116: oe_debug_pub.add ('Failed in dynamic query : '||sqlerrm);
9117: xReturnStatus := fnd_api.G_RET_STS_UNEXP_ERROR;
9118:
9119: END populate_item_revision;
9120:
9130:
9131:
9132: BEGIN
9133:
9134: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9135:
9136:
9137: select substr( attribute_name, instr( attribute_name, '.' )+ 1 ) , control_level
9138: BULK COLLECT
9141: where control_level = 1 ;
9142:
9143:
9144: if( get_attribute_control( 'atp_flag' ) = 1 ) then
9145: oe_debug_pub.add( 'ATP flag is master controlled ' , 1 );
9146: else
9147: oe_debug_pub.add( 'ATP flag is org controlled ' , 1 );
9148:
9149: end if;
9143:
9144: if( get_attribute_control( 'atp_flag' ) = 1 ) then
9145: oe_debug_pub.add( 'ATP flag is master controlled ' , 1 );
9146: else
9147: oe_debug_pub.add( 'ATP flag is org controlled ' , 1 );
9148:
9149: end if;
9150:
9151: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9148:
9149: end if;
9150:
9151: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9152: oe_debug_pub.add( 'ATP components flag is master controlled ' , 1 );
9153: else
9154: oe_debug_pub.add( 'ATP components flag is org controlled ' , 1 );
9155:
9156: end if;
9150:
9151: if( get_attribute_control( 'atp_components_flag' ) = 1 ) then
9152: oe_debug_pub.add( 'ATP components flag is master controlled ' , 1 );
9153: else
9154: oe_debug_pub.add( 'ATP components flag is org controlled ' , 1 );
9155:
9156: end if;
9157:
9158:
9156: end if;
9157:
9158:
9159: if( get_attribute_control( 'market_price' ) = 1 ) then
9160: oe_debug_pub.add( 'market_price is master controlled ' , 1 );
9161: else
9162: oe_debug_pub.add( 'market_price flag is org controlled ' , 1 );
9163:
9164: end if;
9158:
9159: if( get_attribute_control( 'market_price' ) = 1 ) then
9160: oe_debug_pub.add( 'market_price is master controlled ' , 1 );
9161: else
9162: oe_debug_pub.add( 'market_price flag is org controlled ' , 1 );
9163:
9164: end if;
9165:
9166:
9165:
9166:
9167:
9168:
9169: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9170:
9171: insert into mtl_system_items_b
9172: (inventory_item_id,
9173: organization_id,