[Home] [Help]
1: PACKAGE BODY OE_CONFIG_UTIL AS
2: /* $Header: OEXUCFGB.pls 120.45.12020000.3 2013/03/15 12:08:32 sahvivek ship $ */
3:
4: -- Global constant holding the package name
5: G_PKG_NAME CONSTANT VARCHAR2(30):='Oe_Config_Util';
1: PACKAGE BODY OE_CONFIG_UTIL AS
2: /* $Header: OEXUCFGB.pls 120.45.12020000.3 2013/03/15 12:08:32 sahvivek ship $ */
3:
4: -- Global constant holding the package name
5: G_PKG_NAME CONSTANT VARCHAR2(30):='Oe_Config_Util';
6:
7: /*-----------------------------------------------------------------
8: forward declarations
9: ------------------------------------------------------------------*/
149: --
150: BEGIN
151:
152: IF l_debug_level > 0 THEN
153: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.CASCADE_CHANGES' , 1);
154: oe_debug_pub.add('MODEL OLD QTY: ' ||L_OLD_QUANTITY , 2 );
155: oe_debug_pub.add('MODEL NEW QTY: ' ||L_NEW_QUANTITY , 2 );
156: END IF;
157:
221: END LOOP;
222:
223: IF l_line_count = 0 THEN
224: IF l_debug_level > 0 THEN
225: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.CASCADE_CHANGES' , 1);
226: oe_debug_pub.add('NO ROWS TO CASCADE' , 2 );
227: END IF;
228: RETURN;
229: END IF;
433:
434: -- Set the Recursive Call Constant to 'Y' to avoid the recursive
435: -- call to cascading while making this call to process order
436:
437: OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'Y';
438:
439: -- Set the Validate_config Call Constant to 'N' to avoid the validation
440:
441: OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'N';
473: oe_debug_pub.add('L_RETURN_STATUS IS ' || L_RETURN_STATUS , 1);
474: END IF;
475:
476: -- Resetting the Recursive Call Constant to 'N'
477: OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'N';
478:
479: -- Resetting the Validation Constant to 'Y'
480: OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'Y';
481:
554:
555: x_return_status := l_return_status;
556:
557: IF l_debug_level > 0 THEN
558: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.CASCADE_CHANGES' , 1);
559: END IF;
560:
561: EXCEPTION
562: WHEN OTHERS THEN
770: -- Resetting the Change Configuration flag 'Y'
771: OE_GLOBALS.G_CHANGE_CFG_FLAG := 'Y';
772:
773: -- Resetting the Recursive Call Constant to 'N'
774: OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG := 'N';
775:
776: -- ReSet recursion mode.
777: --OE_GLOBALS.G_RECURSION_MODE := 'N';
778:
1032: IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1033: THEN
1034: oe_msg_pub.Add_Exc_Msg
1035: ( G_PKG_NAME
1036: , 'Oe_Config_Util'
1037: );
1038: END IF;
1039:
1040: -- Get message count and data
1102: l_configuration_id,l_booked_flag;
1103:
1104: EXIT WHEN MODELS%NOTFOUND;
1105:
1106: Oe_Config_Util.validate_configuration
1107: ( p_model_line_id => l_line_id,
1108: p_validate_flag => 'Y',
1109: p_complete_flag => 'Y',
1110: p_caller => 'BOOKING',
1237: --
1238: BEGIN
1239:
1240: IF l_debug_level > 0 THEN
1241: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.FREEZE_INC_ITEMS_FOR_ORDER' , 1);
1242: END IF;
1243:
1244: OPEN order_lines;
1245:
1247: FETCH order_lines
1248: into l_line_id;
1249: EXIT WHEN ORDER_LINES%NOTFOUND;
1250:
1251: l_return_status := Oe_Config_Util.Process_included_items
1252: ( p_line_id => l_line_id
1253: ,p_freeze => TRUE);
1254:
1255: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1261:
1262: IF l_debug_level > 0 THEN
1263: oe_debug_pub.add('LEAVING FREEZE_INC_ITEMS_FOR_ORDER:'
1264: || L_RETURN_STATUS , 2 );
1265: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.FREEZE_INC_ITEMS_FOR_ORDER' , 1);
1266: END IF;
1267: RETURN l_return_status;
1268: END Freeze_Inc_Items_for_Order;
1269:
1283: --
1284: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1285: --
1286: BEGIN
1287: Oe_Config_Util.Validate_Configuration
1288: (p_model_line_id => p_model_line_id,
1289: x_valid_config => l_valid_config,
1290: x_complete_config => l_complete_config,
1291: x_return_status => l_return_status);
1452: IF ( l_upgraded_flag = 'Y' OR
1453: l_upgraded_flag = 'P' ) AND
1454: (l_config_header_id is null AND
1455: l_config_rev_nbr is null) AND
1456: Oe_Config_Util.G_Upgraded_Flag = 'N'
1457: THEN
1458: IF l_debug_level > 0 THEN
1459: oe_debug_pub.add('UPGRADE THE CONFIGURAION' , 2 );
1460: END IF;
1458: IF l_debug_level > 0 THEN
1459: oe_debug_pub.add('UPGRADE THE CONFIGURAION' , 2 );
1460: END IF;
1461:
1462: Oe_Config_Util.G_Upgraded_Flag := 'Y';
1463:
1464: Configurator_Validation
1465: (p_model_line_id => p_model_line_id,
1466: x_valid_config => l_valid_config,
2670: --
2671: BEGIN
2672:
2673: IF l_debug_level > 0 THEN
2674: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.VALIDATE_CONFIGURATION' , 1);
2675: END IF;
2676:
2677:
2678: -- No Copy Changes. Initialized return status to success
2803:
2804: OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
2805:
2806: IF l_debug_level > 0 THEN
2807: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.VALIDATE_CONFIGURATION' , 1);
2808: END IF;
2809: EXCEPTION
2810:
2811: WHEN FND_API.G_EXC_ERROR THEN
2897: --
2898: BEGIN
2899:
2900: IF l_debug_level > 0 THEN
2901: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.SEND_INPUT_XML' , 1);
2902: oe_debug_pub.add('UIMANAGER URL: ' || L_URL , 2 );
2903: END IF;
2904:
2905:
3190: x_out_xml_msg := l_long_xml;
3191: x_return_status := l_return_status;
3192:
3193: IF l_debug_level > 0 THEN
3194: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.SEND_INPUT_XML' , 1);
3195: END IF;
3196:
3197: EXCEPTION
3198: WHEN FND_API.G_EXC_ERROR THEN
3290: --
3291: BEGIN
3292:
3293: IF l_debug_level > 0 THEN
3294: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.CREATE_HDR_XML' , 1);
3295: END IF;
3296:
3297: OE_LINE_UTIL.Query_Row(p_line_id => p_model_line_id
3298: ,x_line_rec => l_model_line_rec);
3383: ELSE
3384: param_value(18) := 'FALSE';
3385: END IF;
3386:
3387: OE_Config_Util.Get_Config_Effective_Date
3388: ( p_model_line_rec => l_model_line_rec
3389: ,x_old_behavior => l_old_behavior
3390: ,x_config_effective_date => l_config_effective_date
3391: ,x_frozen_model_bill => l_frozen_model_bill);
3749: x_valid_config := nvl(l_valid_config, 'FALSE');
3750:
3751:
3752: IF l_debug_level > 0 THEN
3753: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.PARSE_OUTPUT_XML' , 1);
3754: END IF;
3755:
3756: EXCEPTION
3757: WHEN FND_API.G_EXC_ERROR THEN
3798: --
3799: BEGIN
3800:
3801: IF l_debug_level > 0 THEN
3802: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.QUERY_OPTIONS' , 1);
3803: oe_debug_pub.add('SEND_CANCEL_LINES: '|| P_SEND_CANCEL_LINES , 3 );
3804: END IF;
3805:
3806: BEGIN
3833: END IF;
3834: END LOOP;
3835:
3836: IF l_debug_level > 0 THEN
3837: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.QUERY_OPTIONS' , 1);
3838: END IF;
3839:
3840: END;
3841:
3868: --
3869: BEGIN
3870:
3871: IF l_debug_level > 0 THEN
3872: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.QUERY_ATO_OPTIONS' , 1);
3873: oe_debug_pub.add('SEND_CANCEL_LINES: '|| P_SEND_CANCEL_LINES , 3 );
3874: END IF;
3875:
3876: BEGIN
3905:
3906: END LOOP;
3907:
3908: IF l_debug_level > 0 THEN
3909: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.QUERY_ATO_OPTIONS' , 1);
3910: END IF;
3911:
3912: EXCEPTION
3913: WHEN OTHERS THEN
3939: --
3940: BEGIN
3941:
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.EXPLODE' , 1);
3944: oe_debug_pub.add(' EXPLOSION TYPE ' || P_STDCOMPFLAG , 2 );
3945: END IF;
3946:
3947: BOMPNORD.Bmxporder_Explode_For_Order(
3988:
3989:
3990: x_return_status := FND_API.G_RET_STS_SUCCESS;
3991: IF l_debug_level > 0 THEN
3992: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.EXPLODE' , 1);
3993: END IF;
3994:
3995: EXCEPTION
3996: WHEN FND_API.G_EXC_ERROR THEN
4099:
4100: END LOOP;
4101:
4102: IF l_debug_level > 0 THEN
4103: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.QUERY_INCLUDED_ITEMS' , 1);
4104: END IF;
4105:
4106: EXCEPTION
4107: WHEN OTHERS THEN
4289: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4290: --
4291: BEGIN
4292:
4293: Print_Time('Entering oe_config_util.process_included_items');
4294:
4295: l_parent_line_rec := p_line_rec;
4296:
4297: IF p_line_rec.line_id <> FND_API.G_MISS_NUM AND
4824: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4825: --
4826: BEGIN
4827: IF l_debug_level > 0 THEN
4828: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_COMPONENT_NUMBER' , 1);
4829: END IF;
4830:
4831: OPEN comp_number;
4832: LOOP
4844:
4845: x_return_status := FND_API.G_RET_STS_SUCCESS;
4846:
4847: IF l_debug_level > 0 THEN
4848: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_COMPONENT_NUMBER' , 1);
4849: END IF;
4850:
4851: EXCEPTION
4852: WHEN OTHERS THEN
5640: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5641: --
5642: BEGIN
5643: IF l_debug_level > 0 THEN
5644: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.QUERY_CONFIG' , 1);
5645: END IF;
5646:
5647: l_top_model_line_id := p_top_model_line_id;
5648:
5781: ('MASTER_ORGANIZATION_ID');
5782: l_group_id NUMBER := null;
5783: l_session_id NUMBER := 0;
5784: l_levels NUMBER := 60;
5785: l_stdcompflag VARCHAR2(10) := Oe_Config_Util.OE_BMX_ALL_COMPS;
5786: l_exp_quantity NUMBER;
5787: l_top_item_id NUMBER;
5788: l_num_lines NUMBER := 0;
5789: l_model_ordered_item VARCHAR2(2000);
5807: --
5808: BEGIN
5809:
5810: IF l_debug_level > 0 THEN
5811: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.COMPLETE_CONFIGURATION' , 1);
5812: END IF;
5813:
5814: BEGIN
5815:
5835: oe_debug_pub.add('ORG ID: '|| L_VALIDATION_ORG , 2 );
5836: END IF;
5837:
5838:
5839: OE_CONFIG_UTIL.Explode
5840: ( p_validation_org => l_validation_org
5841: , p_group_id => l_group_id
5842: , p_session_id => l_session_id
5843: , p_levels => l_levels
5909: INTO l_component_code, l_component_seq_id, l_sort_order,
5910: l_uom_code
5911: FROM bom_explosions
5912: WHERE component_item_id = line_rec.inventory_item_id
5913: AND explosion_type = Oe_Config_Util.OE_BMX_ALL_COMPS
5914: AND top_bill_sequence_id = l_model_seq_id
5915: AND effectivity_date <= l_rev_date
5916: AND disable_date > l_rev_date
5917: AND organization_id = l_validation_org
5927: INTO l_component_code, l_component_seq_id, l_sort_order,
5928: l_uom_code
5929: FROM bom_explosions
5930: WHERE component_item_id = line_rec.inventory_item_id
5931: AND explosion_type = Oe_Config_Util.OE_BMX_ALL_COMPS
5932: AND top_bill_sequence_id = l_model_seq_id
5933: AND effectivity_date <= l_rev_date
5934: AND disable_date > l_rev_date
5935: AND organization_id = l_validation_org;
6039: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6040: --
6041: BEGIN
6042: IF l_debug_level > 0 THEN
6043: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.MATCH_AND_RESERVE' , 1);
6044: END IF;
6045: BEGIN
6046: SELECT top_model_line_id, ordered_quantity, order_quantity_uom
6047: INTO l_top_model_line_id, l_ordered_quantity, l_order_quantity_uom
6102: END IF; -- if match found
6103:
6104: x_return_status := l_return_status;
6105: IF l_debug_level > 0 THEN
6106: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.MATCH_AND_RESERVE' , 1);
6107: END IF;
6108:
6109: EXCEPTION
6110: WHEN NO_DATA_FOUND THEN
6297: oe_msg_pub.add;
6298: END IF;
6299:
6300: IF l_debug_level > 0 THEN
6301: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.DELINK_CONFIG' , 1);
6302: END IF;
6303:
6304: x_return_status := l_return_status;
6305:
6342: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6343: --
6344: BEGIN
6345: IF l_debug_level > 0 THEN
6346: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.PART_OF_CONFIGURATION' , 1);
6347: END IF;
6348:
6349: SELECT item_type_code,header_id, top_model_line_id, line_id
6350: INTO l_item_type_code,l_header_id, l_top_model_line_id, l_line_id
6425: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6426: --
6427: BEGIN
6428: IF l_debug_level > 0 THEN
6429: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.LINK_CONFIG' , 1);
6430: END IF;
6431:
6432: BEGIN
6433: SELECT item_type_code, ato_line_id, ordered_item
6477: oe_msg_pub.add;
6478: END IF;
6479:
6480: IF l_debug_level > 0 THEN
6481: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.LINK_CONFIG' , 1);
6482: END IF;
6483:
6484: x_return_status := l_return_status;
6485:
6513: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6514: --
6515: BEGIN
6516: IF l_debug_level > 0 THEN
6517: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_COMP_SEQ_ID' , 1);
6518: END IF;
6519:
6520: UPDATE oe_order_lines
6521: SET component_sequence_id = p_comp_seq_id
6527:
6528: x_return_status := FND_API.G_RET_STS_SUCCESS;
6529:
6530: IF l_debug_level > 0 THEN
6531: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_COMP_SEQ_ID' , 1);
6532: END IF;
6533:
6534: EXCEPTION
6535: WHEN no_data_found THEN
6561: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6562: --
6563: BEGIN
6564: IF l_debug_level > 0 THEN
6565: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_VISIBLE_DEMAND_FLAG' , 1);
6566: END IF;
6567:
6568: UPDATE oe_order_lines
6569: SET visible_demand_flag = p_visible_demand_flag
6575:
6576: x_return_status := FND_API.G_RET_STS_SUCCESS;
6577:
6578: IF l_debug_level > 0 THEN
6579: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_VISIBLE_DEMAND_FLAG' , 1);
6580: END IF;
6581:
6582: EXCEPTION
6583: WHEN no_data_found THEN
6609: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6610: --
6611: BEGIN
6612: IF l_debug_level > 0 THEN
6613: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_MFG_COMP_SEQ_ID' , 1);
6614: END IF;
6615:
6616: UPDATE oe_order_lines_all
6617: SET mfg_component_sequence_id = p_mfg_comp_seq_id
6623:
6624: x_return_status := FND_API.G_RET_STS_SUCCESS;
6625:
6626: IF l_debug_level > 0 THEN
6627: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_MFG_COMP_SEQ_ID' , 1);
6628: END IF;
6629:
6630: EXCEPTION
6631: WHEN no_data_found THEN
6657: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6658: --
6659: BEGIN
6660: IF l_debug_level > 0 THEN
6661: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_MODEL_GROUP_NUMBER' , 1);
6662: END IF;
6663:
6664: UPDATE oe_order_lines
6665: SET model_group_number = p_model_group_number
6671:
6672: x_return_status := FND_API.G_RET_STS_SUCCESS;
6673:
6674: IF l_debug_level > 0 THEN
6675: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_MODEL_GROUP_NUMBER' , 1);
6676: END IF;
6677:
6678: EXCEPTION
6679: WHEN no_data_found THEN
6708: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6709: --
6710: BEGIN
6711: IF l_debug_level > 0 THEN
6712: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.UPDATE_CTO_COLUMNS' , 1);
6713: END IF;
6714:
6715: UPDATE oe_order_lines
6716: SET request_id = p_request_id
6725:
6726: x_return_status := FND_API.G_RET_STS_SUCCESS;
6727:
6728: IF l_debug_level > 0 THEN
6729: oe_debug_pub.add('EXITING OE_CONFIG_UTIL.UPDATE_CTO_COLUMNS' , 1);
6730: END IF;
6731:
6732: EXCEPTION
6733: WHEN no_data_found THEN
7286:
7287: ---------------- dff -------------------------------------------
7288:
7289: IF G_COPY_MODEL_DFF = 'Y' /* Bug # 5036404 */
7290: -- OE_CONFIG_UTIL.G_CONFIG_UI_USED = 'Y'
7291: THEN
7292:
7293: IF l_debug_level > 0 THEN
7294: oe_debug_pub.add('COPYING DFF TO CHILD' , 1);
7954: x_result := 0;
7955: RETURN;
7956: END IF;
7957:
7958: IF OE_CONFIG_UTIL.CASCADE_CHANGES_FLAG = 'Y' OR
7959: OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG = 'N' OR
7960: l_model_remnant_flag = 'Y' OR
7961: -- DOO Preexploded Kit ER 9339742
7962: l_pre_exploded_flag = 'Y' OR
8828: --
8829: BEGIN
8830:
8831: IF l_debug_level > 0 THEN
8832: oe_debug_pub.add('ENTERING OE_CONFIG_UTIL.Unlock_Config' , 1);
8833: END IF;
8834:
8835:
8836: OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
8883:
8884: END IF;
8885:
8886: IF l_debug_level > 0 THEN
8887: oe_debug_pub.add('Exiting OE_CONFIG_UTIL.Unlock_Config' || x_return_status, 1);
8888: END IF;
8889:
8890: EXCEPTION
8891:
8890: EXCEPTION
8891:
8892: WHEN OTHERS THEN
8893: IF l_debug_level > 0 THEN
8894: oe_debug_pub.add('ERROR IN OE_CONFIG_UTIL.Unlock_Config'|| SQLERRM , 1);
8895: END IF;
8896: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8897: END Unlock_Config;
8898:
9013: --
9014: BEGIN
9015:
9016: if l_debug_level > 0 then
9017: oe_debug_pub.add(' Entering OE_Config_Util.Process_Pre_Exploded_Kits',1);
9018: end if;
9019:
9020: x_return_status := l_return_status;
9021:
9312:
9313: x_return_status := l_return_status;
9314:
9315: if l_debug_level > 0 then
9316: oe_debug_pub.add(' Exiting OE_Config_Util.Process_Pre_Exploded_Kits',1);
9317: end if;
9318:
9319: EXCEPTION
9320: WHEN FND_API.G_EXC_ERROR THEN
9330: );
9331: END IF;
9332: END Process_Pre_Exploded_Kits;
9333:
9334: END OE_CONFIG_UTIL;