232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
246: msc_supplies.transaction_id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
246: msc_supplies.transaction_id
247: else
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
246: msc_supplies.transaction_id
247: else
248: null
249: end,
250: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
257: SYSDATE,
258: '-1',
259: SYSDATE,
260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:
275: EXCEPTION WHEN others THEN
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:
275: EXCEPTION WHEN others THEN
276: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001002' || ' : ' || SQLERRM;
1160: -- init global variables
1161: g_ErrorCode := '';
1162:
1163:
1164: -- delete records from MSC_SUPPLIES table for the given PlanIdVar, if any
1165: BEGIN
1166: DELETE FROM MSC_SUPPLIES WHERE PLAN_ID=PlanIdVar;
1167: EXCEPTION WHEN others THEN
1168: NULL; -- do nothing
1162:
1163:
1164: -- delete records from MSC_SUPPLIES table for the given PlanIdVar, if any
1165: BEGIN
1166: DELETE FROM MSC_SUPPLIES WHERE PLAN_ID=PlanIdVar;
1167: EXCEPTION WHEN others THEN
1168: NULL; -- do nothing
1169: END;
1170: -- insert new rows
1174: -- CODE GOES HERE
1175: --
1176: -- Fill in data from MSC_INT_ASCP_SUPPLY
1177: --
1178: INSERT INTO MSC_SUPPLIES (PLAN_ID,
1179: TRANSACTION_ID,
1180: ORGANIZATION_ID,
1181: SR_INSTANCE_ID,
1182: INVENTORY_ITEM_ID,
1189: LAST_UPDATE_DATE,
1190: LAST_UPDATED_BY,
1191: CREATION_DATE,
1192: CREATED_BY)
1193: SELECT PlanIdVar, msc_supplies_s.nextval, organizationID,
1194: instanceID, itemID, 1, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
1195: supply, 2, SupplierID,
1196: supplierSiteID,
1197: SYSDATE, '-1', SYSDATE, '-1'
1209: -- CODE GOES HERE
1210: --
1211: -- Fill in data from MSC_INT_ASCP_TRANSPORTATION
1212: --
1213: INSERT INTO MSC_SUPPLIES (
1214: PLAN_ID,
1215: TRANSACTION_ID,
1216: ORGANIZATION_ID,
1217: SR_INSTANCE_ID,
1228: CREATION_DATE,
1229: CREATED_BY)
1230: SELECT
1231: PlanIdVar,
1232: msc_supplies_s.nextval,
1233: toOrgID,
1234: toInstanceID,
1235: itemID,
1236: 80,
1254: -- CODE GOES HERE
1255: --
1256: -- Fill in data from MSC_INT_ASCP_INVENTORY
1257: --
1258: INSERT INTO MSC_SUPPLIES (PLAN_ID,
1259: TRANSACTION_ID,
1260: ORGANIZATION_ID,
1261: SR_INSTANCE_ID,
1262: INVENTORY_ITEM_ID,
1267: LAST_UPDATE_DATE,
1268: LAST_UPDATED_BY,
1269: CREATION_DATE,
1270: CREATED_BY)
1271: SELECT PlanIdVar, msc_supplies_s.nextval, organizationId, instanceId,
1272: itemID, 18, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, onHand, 2,
1273: SYSDATE, '-1', SYSDATE, '-1'
1274: FROM MSC_INT_ASCP_INVENTORY
1275: WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_INVENTORY.CATEGORY='Inventory';
1286: -- to distinguish records but these two ids are not relevant
1287: -- to supplies. As a result if we directly write to the table
1288: -- we will get duplcates. Change the selection base to pick unique rows.
1289: --
1290: INSERT INTO MSC_SUPPLIES (PLAN_ID,
1291: TRANSACTION_ID,
1292: ORGANIZATION_ID,
1293: SR_INSTANCE_ID,
1294: INVENTORY_ITEM_ID,
1299: LAST_UPDATE_DATE,
1300: LAST_UPDATED_BY,
1301: CREATION_DATE,
1302: CREATED_BY)
1303: select PlanIdVar, msc_supplies_s.nextval,
1304: organizationID, instanceID, itemID, 88,
1305: TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, flow,
1306: 2,
1307: SYSDATE, '-1', SYSDATE, '-1'
3167: -- g_ErrorCode := 'ERROR_UNEXPECTED_00024' || ' : ' || status || ' : ' || SQLERRM;
3168: -- raise;
3169: --END;
3170:
3171: -- call procedure #3 -- OUT for SOP - we do not need data from msc_supplies for the SOP case
3172: --BEGIN
3173: --SET_ASCP_SUPPLIES (l_String, PlanIdVar);
3174: --IF (l_String <> 'SUCCESS') THEN
3175: -- processid := -1;