[Home] [Help]
1: PACKAGE BODY CSP_ITEM_PL_PARAMS_PVT AS
2: /* $Header: cspvpipb.pls 120.1 2006/01/20 13:55:29 phegde noship $ */
3:
4: --
5: -- Purpose: Insert or update table mtl_item_pl_params based on some conditions
79: l_item_newbuy_edq_factor,
80: l_item_excess_Edq_multiple,
81: l_item_repair_edq_multiple,
82: l_item_newbuy_edq_multiple
83: FROM csp_item_pl_params
84: WHERE organization_id = p_organization_id
85: AND inventory_item_id = p_inventory_item_id;
86:
87: IF ((nvl(l_item_excess_Service_level, nvl(l_excess_service_level, 0)) <> nvl(p_excess_service_level, 0)) OR
92: (nvl(l_item_newbuy_edq_Factor, nvl(l_newbuy_edq_Factor,0)) <> nvl(p_newbuy_edq_factor,0)) OR
93: (nvl(l_item_excess_Edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_excess_edq_multiple, 0)) OR
94: (nvl(l_item_repair_edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_repair_edq_multiple, 0)) OR
95: (nvl(l_item_newbuy_edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_newbuy_edq_multiple, 0))) THEN
96: UPDATE csp_item_pl_params
97: SET excess_service_level = decode(p_excess_service_level, nvl(l_item_excess_Service_level, l_excess_service_level), excess_service_level, p_excess_service_level),
98: repair_service_level = decode(p_repair_service_level, nvl(l_item_repair_Service_level, l_repair_service_level), repair_service_level, p_repair_service_level),
99: newbuy_service_level = decode(p_newbuy_service_level, nvl(l_item_newbuy_service_level, l_newbuy_service_level), newbuy_service_level, p_newbuy_service_level),
100: excess_edq_factor = decode(p_excess_edq_factor, nvl(l_item_excess_edq_factor, l_excess_edq_factor), excess_edq_factor, p_excess_edq_factor),
122: (nvl(l_newbuy_edq_Factor,0) <> nvl(p_newbuy_edq_factor,0)) OR
123: (nvl(l_edq_multiple, 0) <> nvl(p_excess_edq_multiple, 0)) OR
124: (nvl(l_edq_multiple, 0) <> nvl(p_repair_edq_multiple, 0)) OR
125: (nvl(l_edq_multiple, 0) <> nvl(p_newbuy_edq_multiple, 0))) THEN
126: SELECT csp_item_pl_params_s1.nextval
127: INTO l_item_pl_params_s
128: FROM dual;
129:
130: INSERT INTO csp_item_pl_params(ITEM_PL_PARAMS_ID,
126: SELECT csp_item_pl_params_s1.nextval
127: INTO l_item_pl_params_s
128: FROM dual;
129:
130: INSERT INTO csp_item_pl_params(ITEM_PL_PARAMS_ID,
131: ORGANIZATION_ID,
132: INVENTORY_ITEM_ID,
133: CREATED_BY,
134: CREATION_DATE,
172:
173: END;
174:
175:
176: /* MERGE INTO CSP_ITEM_PL_PARAMS cipp
177: USING CSP_PLANNING_PARAMETERS cpp
178: ON (cipp.organization_id = p_organization_id
179: AND cipp.inventory_item_id = p_inventory_item_id)
180: WHEN MATCHED THEN