[Home] [Help]
129:
130: CURSOR children is
131: SELECT line_id, inventory_item_id, ordered_quantity, item_type_code,
132: shipped_quantity
133: FROM oe_order_lines
134: WHERE header_id = l_header_id
135: AND open_flag = 'Y'
136: AND ((top_model_line_id = p_parent_line_id
137: and line_id <> p_parent_line_id)
512: END IF;
513: END IF;
514: -- Instance Unlocking
515:
516: UPDATE oe_order_lines
517: SET config_header_id = null,
518: config_rev_nbr = null,
519: configuration_id = null,
520: lock_control = lock_control + 1
634: p_ato_line_id IN NUMBER,
635: p_line_id IN NUMBER)
636: IS
637: SELECT line_id
638: FROM oe_order_lines
639: WHERE((top_model_line_id = p_top_model_line_id
640: and line_id <> p_line_id)
641: OR (ato_line_id = p_ato_line_id and
642: line_id <> p_line_id ))
1078:
1079: CURSOR models is
1080: SELECT line_id,config_header_id,config_rev_nbr,
1081: configuration_id,booked_flag
1082: FROM oe_order_lines
1083: WHERE item_type_code = OE_GLOBALS.G_ITEM_MODEL
1084: AND header_id = p_header_id
1085: AND open_flag = 'Y';
1086:
1148: -- re-fetch the value to pass it to lock api.
1149: BEGIN
1150: Select config_rev_nbr
1151: Into l_config_rev_nbr
1152: From oe_order_lines_all
1153: Where line_id = l_line_id;
1154: END;
1155:
1156: l_order_number := OE_SCHEDULE_UTIL.Get_Order_Number(p_header_id);
1224: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1225:
1226: CURSOR order_lines is
1227: SELECT line_id
1228: FROM oe_order_lines
1229: WHERE item_type_code in (OE_GLOBALS.G_ITEM_MODEL,
1230: OE_GLOBALS.G_ITEM_CLASS,
1231: OE_GLOBALS.G_ITEM_KIT)
1232: AND open_flag = 'Y'
1358: returns success/error as status. It calls
1359: send_input_xml : to send the configuration coptions to SPC
1360: parse_output_xml : parse output of SPC to see if
1361: configuration is valid/complete
1362: process_config : to save options in oe_order_lines
1363:
1364: now that we have decided that we will treat invalid
1365: configuration in the same way as incomplete configuration,
1366: the arguments p_validate_flag and p_complete_flag do not
1365: configuration in the same way as incomplete configuration,
1366: the arguments p_validate_flag and p_complete_flag do not
1367: have a lot of meaning. We might remove them for the procedure.
1368: We will save invali as well as incomplete configurations
1369: in oe_order_lines before booking.
1370: After booking we will put model on hold if configuraion
1371: becomes invalid/incomplete.
1372: -----------------------------------------------------------------------*/
1373: PROCEDURE Configurator_Validation
1435: source_document_type_id,order_source_id
1436: INTO l_header_id, l_model_qty, l_booked_flag,
1437: l_upgraded_flag ,l_config_header_id, l_config_rev_nbr,
1438: l_source_document_type_id, l_order_source_id
1439: FROM OE_ORDER_LINES
1440: WHERE line_id = p_model_line_id;
1441: EXCEPTION
1442: WHEN OTHERS THEN
1443: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496: IF l_booked_flag = 'N' THEN
1497:
1498: SELECT count (*)
1499: INTO l_any_insert
1500: FROM OE_ORDER_LINES
1501: WHERE top_model_line_id = p_model_line_id
1502: AND line_id <> p_model_line_id
1503: AND config_header_id IS NULL
1504: AND config_rev_nbr IS NULL;
1512:
1513: BEGIN
1514: SELECT 1
1515: INTO l_any_insert
1516: FROM cz_config_details_v cz, oe_order_lines oe
1517: WHERE oe.line_id = p_model_line_id
1518: AND oe.ordered_quantity <> cz.quantity
1519: AND oe.config_header_id = cz.config_hdr_id
1520: AND oe.config_rev_nbr = cz.config_rev_nbr
1870: l_msg_data = 'Y' THEN
1871:
1872: SELECT component_code
1873: INTO l_model_component
1874: FROM oe_order_lines
1875: WHERE line_id = p_model_line_id;
1876:
1877: l_updated_options_tbl := p_updated_options_tbl;
1878: l_deleted_options_tbl := p_deleted_options_tbl;
1996:
1997: IF p_updated_options_tbl.COUNT > 0 THEN
1998: SELECT ordered_quantity
1999: INTO l_qty
2000: FROM oe_order_lines
2001: WHERE line_id = p_model_line_id;
2002: END IF;
2003:
2004: I :=p_updated_options_tbl.FIRST;
2591: l_count := 0;
2592:
2593: SELECT /* MOAC_SQL_CHANGE */ count(*)
2594: INTO l_count
2595: FROM oe_order_lines_all
2596: WHERE top_model_line_id = p_top_model_line_id
2597: AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
2598: AND open_flag = 'Y'
2599: AND link_to_line_id =
2597: AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
2598: AND open_flag = 'Y'
2599: AND link_to_line_id =
2600: (SELECT line_id
2601: FROM oe_order_lines_all
2602: WHERE top_model_line_id = p_top_model_line_id
2603: AND component_code = l_parent
2604: AND open_flag = 'Y' );
2605:
2689: INTO l_header_id, l_config_hdr_id, l_config_rev_nbr, l_model_qty,
2690: l_configuration_id, l_order_source_id, l_orig_sys_document_ref,
2691: l_orig_sys_line_ref, l_orig_sys_shipment_ref, l_change_sequence,
2692: l_source_document_type_id, l_source_document_id, l_source_document_line_id
2693: FROM OE_ORDER_LINES_ALL
2694: WHERE line_id = p_model_line_id;
2695: EXCEPTION
2696: WHEN OTHERS THEN
2697: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2917:
2918: BEGIN
2919: SELECT ol.component_code, ol.configuration_id
2920: INTO l_component_code, l_configuration_id
2921: FROM oe_order_lines ol, cz_config_details_v cz
2922: WHERE ol.line_id = p_model_line_id
2923: AND cz.component_code = ol.component_code
2924: AND cz.config_hdr_id = ol.config_header_id
2925: AND cz.config_rev_nbr = ol.config_rev_nbr
3787: /* adding component number in this cursor to fix bug 2733667 */
3788:
3789: CURSOR c1 IS
3790: SELECT line_id
3791: FROM OE_ORDER_LINES_ALL
3792: WHERE HEADER_ID = l_header_id AND
3793: TOP_MODEL_LINE_ID = p_top_model_line_id
3794: ORDER BY line_number,shipment_number,nvl(option_number,-1),nvl(component_number,-1);
3795:
3805:
3806: BEGIN
3807: SELECT header_id
3808: INTO l_header_id
3809: FROM oe_order_lines_all
3810: WHERE line_id = p_top_model_line_id;
3811: EXCEPTION
3812: WHEN NO_DATA_FOUND THEN
3813: l_header_id := 0;
3857:
3858:
3859: CURSOR c1 IS
3860: SELECT line_id
3861: FROM OE_ORDER_LINES_ALL
3862: WHERE HEADER_ID = l_header_id AND
3863: TOP_MODEL_LINE_ID = l_top_model_line_id AND
3864: ATO_LINE_ID = p_ato_line_id
3865: ORDER BY line_number,shipment_number,nvl(option_number,-1);
3875:
3876: BEGIN
3877: SELECT top_model_line_id ,header_id
3878: INTO l_top_model_line_id,l_header_id
3879: FROM oe_order_lines_all
3880: WHERE line_id = p_ato_line_id;
3881: EXCEPTION
3882: WHEN NO_DATA_FOUND THEN
3883: l_top_model_line_id := 0;
4035:
4036:
4037: CURSOR c1 IS
4038: SELECT line_id
4039: FROM OE_ORDER_LINES_ALL
4040: WHERE HEADER_ID = l_header_id AND
4041: TOP_MODEL_LINE_ID = l_top_model_line_id AND
4042: LINK_TO_LINE_ID = p_line_id AND
4043: ITEM_TYPE_CODE = OE_GLOBALS.G_ITEM_INCLUDED
4057:
4058: BEGIN
4059: SELECT top_model_line_id ,header_id
4060: INTO l_top_model_line_id,l_header_id
4061: FROM oe_order_lines_all
4062: WHERE line_id = p_line_id;
4063: EXCEPTION
4064: WHEN NO_DATA_FOUND THEN
4065: l_top_model_line_id := 0;
4232: AND be.DISABLE_DATE > p_std_comp_freeze_date
4233: AND be.COMPONENT_ITEM_ID <> be.TOP_ITEM_ID
4234: AND NOT EXISTS
4235: ( SELECT 'X'
4236: FROM oe_order_lines l
4237: WHERE l.top_model_line_id = p_top_model_line_id
4238: AND l.link_to_line_id = l_parent_line_rec.line_id
4239: AND l.component_code = be.component_code
4240: AND l.open_flag = 'Y')
4248: p_std_comp_freeze_date IN DATE)
4249: IS
4250: SELECT
4251: oel.line_id, be.extended_quantity * p_top_model_quantity
4252: FROM oe_order_lines oel, bom_explosions be
4253: WHERE oel.top_model_line_id = p_top_model_line_id
4254: AND oel.link_to_line_id = l_parent_line_rec.line_id
4255: AND oel.item_type_code = 'INCLUDED'
4256: AND be.explosion_type = 'INCLUDED'
4269: p_std_comp_freeze_date IN DATE)
4270: IS
4271: SELECT
4272: l.line_id
4273: FROM oe_order_lines l
4274: WHERE l.link_to_line_id = l_parent_line_rec.line_id
4275: AND l.top_model_line_id = p_top_model_line_id
4276: AND l.item_type_code = 'INCLUDED'
4277: AND l.open_flag = 'Y'
4455:
4456: BEGIN
4457: SELECT max(component_number)
4458: INTO l_component_number
4459: FROM oe_order_lines
4460: WHERE link_to_line_id = p_line_id
4461: AND top_model_line_id = l_parent_line_rec.top_model_line_id;
4462: EXCEPTION
4463: WHEN OTHERS THEN
4546: END IF;
4547:
4548: -- Adding this flag for fulfillment purpose.
4549:
4550: SELECT OE_ORDER_LINES_S.NEXTVAL
4551: INTO l_line_rec.line_id
4552: FROM dual;
4553:
4554: l_component_number := l_component_number + 1;
4606:
4607: -- open cursors only if some records exist to upd/del
4608: SELECT count(*)
4609: INTO l_top_model_quantity
4610: FROM oe_order_lines
4611: WHERE item_type_code = 'INCLUDED'
4612: AND top_model_line_id = l_parent_line_rec.top_model_line_id
4613: AND link_to_line_id = l_parent_line_rec.line_id;
4614:
4760: IF nvl(p_freeze, FALSE) = TRUE OR
4761: l_freeze_macd_kit THEN
4762: -- Update the explosion date on the model line.
4763: BEGIN
4764: UPDATE OE_ORDER_LINES_ALL
4765: set explosion_date = l_explosion_date,
4766: lock_control = lock_control + 1
4767: WHERE line_id = l_parent_line_rec.line_id;
4768: EXCEPTION
4813: IS
4814:
4815: CURSOR comp_number IS
4816: SELECT line_id
4817: FROM oe_order_lines
4818: WHERE link_to_line_id = p_line_id
4819: AND top_model_line_id = p_top_model_line_id;
4820:
4821: l_component_number NUMBER := 0;
4834: EXIT WHEN comp_number%NOTFOUND;
4835:
4836: l_component_number := l_component_number + 1;
4837:
4838: UPDATE oe_order_lines
4839: SET component_number = l_component_number,
4840: lock_control = lock_control + 1
4841: WHERE line_id = l_line_id;
4842:
5625: l_header_id NUMBER := 0;
5626:
5627: CURSOR l_line_csr(l_top_model_line_id NUMBER) IS
5628: SELECT line_id
5629: FROM OE_ORDER_LINES_ALL
5630: WHERE HEADER_ID = l_header_id
5631: AND (TOP_MODEL_LINE_ID = l_top_model_line_id OR
5632: ( LINK_TO_LINE_ID = p_link_to_line_id AND
5633: ITEM_TYPE_CODE = OE_GLOBALS.G_ITEM_INCLUDED AND
5661: END IF;
5662:
5663: SELECT header_id
5664: INTO l_header_id
5665: FROM oe_order_lines_all
5666: WHERE line_id=ll_line_id;
5667:
5668: EXCEPTION
5669: WHEN OTHERS THEN
5791: l_error_code NUMBER;
5792:
5793: CURSOR comp_code_upd IS
5794: SELECT line_id, inventory_item_id, ordered_item, component_code
5795: FROM oe_order_lines
5796: WHERE top_model_line_id = p_top_model_line_id
5797: AND item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
5798: AND open_flag = 'Y'
5799: AND (component_code is null OR
5816: SELECT creation_date, component_code, component_sequence_id,
5817: inventory_item_id, ordered_quantity, ordered_item
5818: INTO l_rev_date, l_component_code, l_model_seq_id,
5819: l_top_item_id, l_exp_quantity, l_model_ordered_item
5820: FROM oe_order_lines
5821: WHERE line_id = p_top_model_line_id;
5822:
5823: EXCEPTION
5824: WHEN OTHERS THEN
5972: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5973: END;
5974:
5975: -- now update the oe table
5976: UPDATE oe_order_lines
5977: SET component_code = l_component_code,
5978: component_sequence_id = l_component_seq_id,
5979: sort_order = l_sort_order,
5980: order_quantity_uom = l_uom_code,
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
6048: FROM oe_order_lines
6049: WHERE line_id = p_line_id;
6050: EXCEPTION
6051: WHEN OTHERS THEN
6052: IF l_debug_level > 0 THEN
6164:
6165: BEGIN
6166: SELECT item_type_code, ato_line_id, ordered_item,inventory_item_id
6167: INTO l_item_type_code, l_ato_line_id, l_ordered_item,l_inv_item_id
6168: FROM oe_order_lines
6169: WHERE line_id = p_line_id;
6170: EXCEPTION
6171: WHEN OTHERS THEN
6172: IF l_debug_level > 0 THEN
6184: option_number||'.'||component_number||'.'||
6185: service_number,'.'),source_type_code
6186: INTO l_config_id,l_config_line_id,l_config_header_id,
6187: l_line_num,l_source_type
6188: FROM oe_order_lines
6189: -- Bug#5026787: Start:- ato_line_id should be used instead of top_model_line_id.
6190: -- WHERE top_model_line_id = p_line_id
6191: WHERE ato_line_id = p_line_id
6192: -- Bug#5026787: End
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
6351: FROM oe_order_lines
6352: WHERE line_id = oe_line_security.g_record.line_id;
6353:
6354: IF l_item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
6355: l_item_type_code = OE_GLOBALS.G_ITEM_SERVICE OR
6376: l_top_model_line_id = l_line_id)
6377: THEN
6378: SELECT count(*)
6379: INTO l_count
6380: FROM OE_ORDER_LINES
6381: WHERE top_model_line_id = oe_line_security.g_record.line_id
6382: AND line_id <> oe_line_security.g_record.line_id
6383: AND header_id = l_header_id;
6384:
6431:
6432: BEGIN
6433: SELECT item_type_code, ato_line_id, ordered_item
6434: INTO l_item_type_code, l_ato_line_id, l_ordered_item
6435: FROM oe_order_lines
6436: WHERE line_id = p_line_id;
6437: EXCEPTION
6438: WHEN OTHERS THEN
6439: IF l_debug_level > 0 THEN
6450: l_valid := 0;
6451:
6452: SELECT distinct 1
6453: INTO l_valid
6454: FROM oe_order_lines_all oel,
6455: mtl_system_items msi
6456: WHERE oel.line_id = p_line_id
6457: AND oel.inventory_item_id = msi.base_item_id
6458: AND msi.inventory_item_id = p_config_item_id;
6500:
6501: /*----------------------------------------------------------------------
6502: Procedure Name : Update_Comp_Seq_Id
6503: Description : API for CTO to do a direct update on
6504: oe_order_lines w/o calling process_order.
6505: -----------------------------------------------------------------------*/
6506:
6507: PROCEDURE Update_Comp_Seq_Id
6508: ( p_line_id IN NUMBER
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
6522: ,last_update_date = sysdate
6523: ,last_updated_by = FND_Global.User_Id
6524: ,last_update_login = FND_Global.Login_Id
6548:
6549: /*----------------------------------------------------------------------
6550: Procedure Name : Update_Visible_Demand_Flag
6551: Description : API for CTO to do a direct update on
6552: oe_order_lines w/o calling process_order.
6553: -----------------------------------------------------------------------*/
6554:
6555: PROCEDURE Update_Visible_Demand_Flag
6556: ( p_ato_line_id IN NUMBER
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
6570: ,last_update_date = sysdate
6571: ,last_updated_by = FND_Global.User_Id
6572: ,last_update_login = FND_Global.Login_Id
6596:
6597: /*----------------------------------------------------------------------
6598: Procedure Name : Update_Mfg_Comp_Seq_Id
6599: Description : API for CTO to do a direct update on
6600: oe_order_lines w/o calling process_order.
6601: -----------------------------------------------------------------------*/
6602:
6603: PROCEDURE Update_Mfg_Comp_Seq_Id
6604: ( p_ato_line_id IN NUMBER
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
6618: ,last_update_date = sysdate
6619: ,last_updated_by = FND_Global.User_Id
6620: ,last_update_login = FND_Global.Login_Id
6644:
6645: /*----------------------------------------------------------------------
6646: Procedure Name : Update_Model_Group_Number
6647: Description : API for CTO to do a direct update on
6648: oe_order_lines w/o calling process_order.
6649: -----------------------------------------------------------------------*/
6650:
6651: PROCEDURE Update_Model_Group_Number
6652: ( p_ato_line_id IN NUMBER
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
6666: ,last_update_date = sysdate
6667: ,last_updated_by = FND_Global.User_Id
6668: ,last_update_login = FND_Global.Login_Id
6692:
6693: /*----------------------------------------------------------------------
6694: Procedure Name : Update_Cto_Columns
6695: Description : API for CTO to do a direct update on
6696: oe_order_lines w/o calling process_order.
6697: -----------------------------------------------------------------------*/
6698:
6699: PROCEDURE Update_Cto_Columns
6700: ( p_ato_line_id IN NUMBER
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
6717: ,program_id = p_program_id
6718: ,program_update_date = p_prog_update_date
6719: ,program_application_id = p_prog_appl_id
6858: END IF;
6859:
6860: SELECT ato_line_id
6861: INTO l_ato_line_id
6862: FROM oe_order_lines
6863: WHERE line_id = p_request_tbl(I).param1;
6864:
6865: IF l_ato_line_id = p_request_tbl(I).param2 THEN
6866:
6982: FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_DECIMAL_RATIO');
6983:
6984: SELECT ordered_item, item_type_code,inventory_item_id
6985: INTO l_ordered_item, l_item_type_code,l_inv_item_id
6986: FROM oe_order_lines
6987: WHERE top_model_line_id = p_top_model_line_id
6988: AND component_code = p_component_code
6989: AND rownum = 1;
6990:
6993: FND_MESSAGE.Set_TOKEN('VALUE',to_char(p_ratio));
6994:
6995: SELECT ordered_item, item_type_code,inventory_item_id
6996: INTO l_ordered_item, l_item_type_code,l_inv_item_id
6997: FROM oe_order_lines
6998: WHERE line_id = p_top_model_line_id;
6999:
7000: FND_MESSAGE.Set_TOKEN('MODEL', nvl(l_ordered_item,l_inv_item_id));
7001: FND_MESSAGE.Set_TOKEN('PTYPECODE', l_item_type_code);
7813: END IF;
7814:
7815: SELECT user_item_description
7816: INTO p_x_child_line_rec.user_item_description
7817: FROM oe_order_lines
7818: WHERE line_id = p_x_child_line_rec.ato_line_id;
7819:
7820: --no need to handle exception here as exception is handled outside
7821:
7860: p_x_child_line_rec.firm_demand_flag,
7861: p_x_child_line_rec.override_atp_date_code,
7862: p_x_child_line_rec.ship_to_contact_id, --bug7041018,7175458
7863: p_x_child_line_rec.intermed_ship_to_org_id --bug 7041018,7175458
7864: FROM oe_order_lines
7865: WHERE line_id = p_x_child_line_rec.ato_line_id;
7866:
7867: IF l_debug_level > 0 THEN
7868: oe_debug_pub.add('2 **SOURCE TYPE ATO '
7942: END IF;
7943:
7944: SELECT item_type_code, model_remnant_flag, pre_exploded_flag
7945: INTO l_item_type_code, l_model_remnant_flag, l_pre_exploded_flag -- DOO Preexploded Kit ER 9339742
7946: FROM oe_order_lines
7947: WHERE line_id = oe_line_security.g_record.line_id;
7948:
7949: IF l_debug_level > 0 THEN -- DOO Preexploded Kit ER 9339742
7950: oe_debug_pub.add('Pre Exploded Flag is : '||l_pre_exploded_flag);
8183:
8184: BEGIN
8185: select 'Y'
8186: into l_serviced_model
8187: from oe_order_lines
8188: where item_type_code = 'SERVICE'
8189: and service_reference_line_id = p_line_tbl(I).top_model_line_id
8190: and service_reference_type_code = 'ORDER'
8191: and rownum = 1;
8341: x_wb_item_type := null;
8342:
8343: SELECT count(*)
8344: INTO l_count
8345: FROM oe_order_lines
8346: WHERE line_category_code <> 'RETURN'
8347: -- the condition below is commented for bug 3441504
8348: -- AND booked_flag = 'Y'
8349: AND (header_id = p_header_id OR
8492: BEGIN
8493:
8494: SELECT nvl(ordered_item,inventory_item_id )
8495: INTO l_message_text
8496: FROM oe_order_lines
8497: WHERE line_id = p_line_id;
8498:
8499: EXCEPTION
8500: WHEN OTHERS THEN
8563: IF p_model_line_rec.split_from_line_id is NOT NULL THEN
8564: BEGIN
8565: SELECT creation_date
8566: INTO l_creation_date
8567: FROM oe_order_lines
8568: WHERE header_id = l_header_id
8569: AND line_set_id = p_model_line_rec.line_set_id
8570: AND split_from_line_id is NULL;
8571: EXCEPTION
8576: OE_Debug_Pub.Add('Getting the data from history for '|| p_model_line_rec.split_from_line_id, 3);
8577: END IF;
8578: SELECT creation_date
8579: INTO l_creation_date
8580: FROM oe_order_lines_history
8581: WHERE header_id = l_header_id
8582: AND line_set_id = p_model_line_rec.line_set_id
8583: AND split_from_line_id is NULL
8584: AND ROWNUM = 1;
8619: SELECT header_id, creation_date,
8620: line_set_id, split_from_line_id
8621: INTO l_header_id, l_creation_date,
8622: l_line_set_id, l_split_from_line_id
8623: FROM oe_order_lines
8624: WHERE line_id = p_model_line_id;
8625:
8626: IF l_split_from_line_id is NOT NULL THEN
8627: BEGIN
8626: IF l_split_from_line_id is NOT NULL THEN
8627: BEGIN
8628: SELECT creation_date
8629: INTO l_creation_date
8630: FROM oe_order_lines
8631: WHERE header_id = l_header_id
8632: AND line_set_id = l_line_set_id
8633: AND split_from_line_id is NULL;
8634: EXCEPTION
8640: OE_Debug_Pub.Add('Getting the data from history for '|| l_split_from_line_id, 3);
8641: END IF;
8642: SELECT creation_date
8643: INTO l_creation_date
8644: FROM oe_order_lines_history
8645: WHERE header_id = l_header_id
8646: AND line_set_id = l_line_set_id
8647: AND split_from_line_id is NULL
8648: AND ROWNUM = 1;
8942: , extended_quantity bom_explosions.extended_quantity%TYPE
8943: , component_code bom_explosions.component_code%TYPE
8944: , PRIMARY_UOM_CODE bom_explosions.PRIMARY_UOM_CODE%TYPE
8945: , sort_order bom_explosions.sort_order%TYPE
8946: , OM_Parent_Line_id oe_order_lines_all.line_id%TYPE
8947: , OM_Parent_Inventory_Item_id oe_order_lines_all.Inventory_Item_id%TYPE
8948: );
8949:
8950:
8943: , component_code bom_explosions.component_code%TYPE
8944: , PRIMARY_UOM_CODE bom_explosions.PRIMARY_UOM_CODE%TYPE
8945: , sort_order bom_explosions.sort_order%TYPE
8946: , OM_Parent_Line_id oe_order_lines_all.line_id%TYPE
8947: , OM_Parent_Inventory_Item_id oe_order_lines_all.Inventory_Item_id%TYPE
8948: );
8949:
8950:
8951: CURSOR C_PreExploded_Kit (c_top_model_line_id IN NUMBER) IS
8948: );
8949:
8950:
8951: CURSOR C_PreExploded_Kit (c_top_model_line_id IN NUMBER) IS
8952: SELECT line_id FROM oe_order_lines_all
8953: WHERE item_type_code = OE_GLOBALS.G_ITEM_INCLUDED
8954: AND top_model_line_id = c_top_model_line_id
8955: AND top_model_line_id <> line_id;
8956:
8990: -- get sets to Bom_Explosion_Rec
8991: CURSOR C_Bill_Seq_id ( p_top_model_line_id IN NUMBER, p_header_id IN NUMBER
8992: , p_validation_org IN NUMBER) IS
8993: SELECT bom.bill_sequence_id, oel.line_id, oel.inventory_item_id
8994: FROM bom_bill_of_materials bom, oe_order_lines_all oel
8995: WHERE bom.ASSEMBLY_ITEM_ID = oel.inventory_item_id
8996: AND oel.top_model_line_id = p_top_model_line_id
8997: AND oel.header_id = p_header_id
8998: AND oel.item_type_code in (OE_GLOBALS.G_ITEM_CLASS,OE_GLOBALS.G_ITEM_KIT,OE_GLOBALS.G_ITEM_MODEL)
8998: AND oel.item_type_code in (OE_GLOBALS.G_ITEM_CLASS,OE_GLOBALS.G_ITEM_KIT,OE_GLOBALS.G_ITEM_MODEL)
8999: AND bom.ORGANIZATION_ID = p_validation_org
9000: AND bom.ALTERNATE_BOM_DESIGNATOR IS NULL;
9001:
9002: -- comp_number is used to set the Component_Number column on OE_Order_Lines_All
9003: -- table for all the INCLUDED item components inside its given immediate parent
9004: CURSOR comp_number (c_parent_line_id IN NUMBER, c_top_model_line_id IN NUMBER, c_header_id IN NUMBER) IS
9005: SELECT line_id
9006: FROM oe_order_lines_all
9002: -- comp_number is used to set the Component_Number column on OE_Order_Lines_All
9003: -- table for all the INCLUDED item components inside its given immediate parent
9004: CURSOR comp_number (c_parent_line_id IN NUMBER, c_top_model_line_id IN NUMBER, c_header_id IN NUMBER) IS
9005: SELECT line_id
9006: FROM oe_order_lines_all
9007: WHERE link_to_line_id = c_parent_line_id
9008: AND top_model_line_id = c_top_model_line_id
9009: AND header_id = c_header_id
9010: AND item_type_code = OE_GLOBALS.G_ITEM_INCLUDED;
9151: -- required validation to be considered but we are safely ignoring
9152: -- them.
9153: --
9154: -- Validating if the EBS BOM definition in BOM_Explosions table
9155: -- and BOM information passed by DOO in OE_order_Lines_All table is same
9156: IF l_included_item_tbl.COUNT <> l_Bom_Explosion_Tbl.COUNT THEN
9157: if l_debug_level > 0 then
9158: oe_debug_pub.add(' The count of Included Items are not same',5);
9159: end if;
9220: if l_debug_level > 0 then
9221: oe_debug_pub.add(' Setting the BOM data in OM for Line : '||l_included_item_tbl(i).line_id,5);
9222: end if;
9223:
9224: UPDATE OE_order_Lines_All
9225: SET Component_Sequence_id = l_Bom_Explosion_Tbl(j).component_sequence_id
9226: , Sort_Order = l_Bom_Explosion_Tbl(j).sort_order
9227: , Component_Code = l_Bom_Explosion_Tbl(j).component_code
9228: , Explosion_Date = l_explosion_date
9293: EXIT WHEN comp_number%NOTFOUND;
9294:
9295: l_component_number := l_component_number + 1;
9296:
9297: UPDATE oe_order_lines
9298: SET component_number = l_component_number,
9299: lock_control = lock_control + 1
9300: WHERE line_id = l_comp_line_id;
9301:
9304: l_component_number := 0;
9305: END LOOP;
9306: END IF;
9307:
9308: UPDATE OE_order_Lines_All
9309: SET Explosion_Date = l_explosion_date
9310: , lock_control = lock_control + 1
9311: WHERE Line_id = l_parent_line_rec.line_id;
9312: