[Home] [Help]
1: PACKAGE BODY AHL_LTP_MTL_REQ_PVT AS
2: /* $Header: AHLVLMRB.pls 120.5.12020000.2 2012/12/10 13:48:28 shnatu ship $ */
3:
4: -----------------------
5: -- Declare Constants --
3:
4: -----------------------
5: -- Declare Constants --
6: -----------------------
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_LTP_MTL_REQ_PVT';
8:
9: G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_MTL_REQ_PVT';
10:
11: G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
5: -- Declare Constants --
6: -----------------------
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_LTP_MTL_REQ_PVT';
8:
9: G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_MTL_REQ_PVT';
10:
11: G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
12: G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
13:
163: -- The date when the materials are required. If provided, the positions of Master Configs
164: -- (for position path based disposition list requirement) are validated against this date.
165: -- p_request_type IN VARCHAR2 Required
166: -- Should be either 'FORECAST' or 'PLANNED'
167: -- x_route_mtl_req_tbl OUT AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl Required
168: -- The Table of records containing the material requirements for the route
169: --
170: -- Version :
171: -- Initial Version 1.0
185: p_item_instance_id IN NUMBER,
186: p_visit_id IN NUMBER := null, -- Added by surrkuma for Service Bulletin, 07-Jun-2011
187: p_requirement_date IN DATE := null,
188: p_request_type IN VARCHAR2,
189: x_route_mtl_req_tbl OUT NOCOPY AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type) IS
190:
191: -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
192: -- Modified the cursor to fetch UC header id too, for getting the affected SB rule later
193: CURSOR get_mc_dtls_csr(c_instance_id IN NUMBER) IS
215: l_mc_version NUMBER;
216: -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
217: -- Added a local variable for UC header id
218: l_uc_header_id NUMBER;
219: l_disp_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
220: l_route_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
221: l_index NUMBER;
222: l_temp_item_id NUMBER;
223: l_temp_ig_id NUMBER;
216: -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
217: -- Added a local variable for UC header id
218: l_uc_header_id NUMBER;
219: l_disp_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
220: l_route_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
221: l_index NUMBER;
222: l_temp_item_id NUMBER;
223: l_temp_ig_id NUMBER;
224: l_found BOOLEAN;
722: ROM.INVENTORY_ITEM_ID,
723: ROM.INVENTORY_ORG_ID,
724: ROM.UOM_CODE,
725: ROM.QUANTITY,
726: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
727: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
728: ITEM_GROUP_ID,
729: ITEM_COMP_DETAIL_ID,
730: POSITION_PATH_ID,
723: ROM.INVENTORY_ORG_ID,
724: ROM.UOM_CODE,
725: ROM.QUANTITY,
726: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
727: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
728: ITEM_GROUP_ID,
729: ITEM_COMP_DETAIL_ID,
730: POSITION_PATH_ID,
731: PP.PATH_POS_COMMON_ID,
742: CURSOR get_item_comp_dtls_csr(c_item_comp_detail_id IN NUMBER,
743: c_quantity IN NUMBER,
744: c_uom IN VARCHAR2) IS
745: SELECT ICD.inventory_item_id, ICD.inventory_master_org_id, ICD.item_group_id,
746: DECODE(ICD.inventory_item_id, null, c_quantity, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(ICD.inventory_item_id, c_uom, c_quantity)) QUANTITY,
747: DECODE(ICD.inventory_item_id, null, c_uom, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(ICD.inventory_item_id, inventory_master_org_id)) UOM
748: FROM AHL_ITEM_COMP_DETAILS ICD
749: WHERE ITEM_COMP_DETAIL_ID = c_item_comp_detail_id;
750:
743: c_quantity IN NUMBER,
744: c_uom IN VARCHAR2) IS
745: SELECT ICD.inventory_item_id, ICD.inventory_master_org_id, ICD.item_group_id,
746: DECODE(ICD.inventory_item_id, null, c_quantity, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(ICD.inventory_item_id, c_uom, c_quantity)) QUANTITY,
747: DECODE(ICD.inventory_item_id, null, c_uom, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(ICD.inventory_item_id, inventory_master_org_id)) UOM
748: FROM AHL_ITEM_COMP_DETAILS ICD
749: WHERE ITEM_COMP_DETAIL_ID = c_item_comp_detail_id;
750:
751: l_mc_route_effectivity_id NUMBER;
1075: ROM.MC_ID,
1076: ROM.POSITION_KEY,
1077: ROM.QUANTITY,
1078: ROM.UOM_CODE,
1079: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
1080: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1081: ROM.ITEM_GROUP_ID
1082: FROM AHL_RT_OPER_MATERIALS ROM
1083: WHERE OBJECT_ID = p_route_id
1076: ROM.POSITION_KEY,
1077: ROM.QUANTITY,
1078: ROM.UOM_CODE,
1079: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
1080: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1081: ROM.ITEM_GROUP_ID
1082: FROM AHL_RT_OPER_MATERIALS ROM
1083: WHERE OBJECT_ID = p_route_id
1084: AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;
1093: TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
1094: INVENTORY_ITEM_ID,
1095: INVENTORY_ORG_ID,
1096: -- Aggregate item quantities across operations when forecasting
1097: SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, --Total Primary Qty
1098: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1099: -- support for Oracle 8
1100: --NULL AS ITEM_GROUP_ID
1101: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1094: INVENTORY_ITEM_ID,
1095: INVENTORY_ORG_ID,
1096: -- Aggregate item quantities across operations when forecasting
1097: SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, --Total Primary Qty
1098: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1099: -- support for Oracle 8
1100: --NULL AS ITEM_GROUP_ID
1101: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1102: FROM AHL_RT_OPER_MATERIALS ROM
1122: SELECT RT_OPER_MATERIAL_ID,
1123: OBJECT_ID AS ROUTE_OPERATION_ID,
1124: INVENTORY_ITEM_ID,
1125: INVENTORY_ORG_ID,
1126: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, -- Primary Qty
1127: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1128: --NULL AS ITEM_GROUP_ID
1129: -- support for Oracle 8
1130: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1123: OBJECT_ID AS ROUTE_OPERATION_ID,
1124: INVENTORY_ITEM_ID,
1125: INVENTORY_ORG_ID,
1126: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, -- Primary Qty
1127: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1128: --NULL AS ITEM_GROUP_ID
1129: -- support for Oracle 8
1130: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1131: FROM AHL_RT_OPER_MATERIALS ROM
1183: TO_NUMBER(NULL) AS POSITION_KEY,
1184: TO_NUMBER(NULL) AS QUANTITY,
1185: NULL AS UOM_CODE,
1186: -- Aggregate item quantities across operations when forecasting
1187: SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS PRIMARY_QUANTITY, /*Total Primary Qty */
1188: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1189: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1190: FROM AHL_RT_OPER_MATERIALS ROM
1191: WHERE OBJECT_ID in ( SELECT RO.operation_id
1184: TO_NUMBER(NULL) AS QUANTITY,
1185: NULL AS UOM_CODE,
1186: -- Aggregate item quantities across operations when forecasting
1187: SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS PRIMARY_QUANTITY, /*Total Primary Qty */
1188: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1189: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1190: FROM AHL_RT_OPER_MATERIALS ROM
1191: WHERE OBJECT_ID in ( SELECT RO.operation_id
1192: FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1246: MC_ID,
1247: POSITION_KEY,
1248: QUANTITY,
1249: UOM_CODE,
1250: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY, /* Primary Qty */
1251: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1252: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1253: FROM AHL_RT_OPER_MATERIALS ROM
1254: WHERE OBJECT_ID in ( SELECT RO.operation_id
1247: POSITION_KEY,
1248: QUANTITY,
1249: UOM_CODE,
1250: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY, /* Primary Qty */
1251: AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1252: TO_NUMBER(NULL) AS ITEM_GROUP_ID
1253: FROM AHL_RT_OPER_MATERIALS ROM
1254: WHERE OBJECT_ID in ( SELECT RO.operation_id
1255: FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
2889: ------------------------------------
2890: -- End Local Function Definitions --
2891: ------------------------------------
2892:
2893: END AHL_LTP_MTL_REQ_PVT;