[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
NOT_SELECTED CONSTANT NUMBER(1) := 0;
SELECTED CONSTANT NUMBER(2) := 99;
v_delete_purchase_order BOOLEAN := FALSE;
v_delete_requisition BOOLEAN := FALSE;
SELECT RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
FROM msc_hz_ui_lines
WHERE ORDER_TYPE = nvl(arg_order_type,NOT_SELECTED)
AND query_id = arg_query_id
GROUP BY RELATION_GROUP,ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID;
SELECT RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,ROUND( sum(QTY_BUCKET1+QTY_BUCKET2), 6) q_2,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3), 6) q_3,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4), 6) q_4,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5), 6) q_5,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6), 6) q_6,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7), 6) q_7,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8), 6) q_8,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9), 6) q_9,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10), 6) q_10,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11), 6) q_11,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11
+QTY_BUCKET12), 6) q_12,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5
+QTY_BUCKET6+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10
+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13), 6) q_13,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14), 6) q_14,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15), 6) q_15,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16), 6) q_16,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17), 6) q_17,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18), 6) q_18,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18
+QTY_BUCKET19), 6) q_19,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6
+QTY_BUCKET7+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12
+QTY_BUCKET13+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18
+QTY_BUCKET19+QTY_BUCKET20), 6) q_20,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21), 6) q_21,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22), 6) q_22,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23), 6) q_23,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24), 6) q_24,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25), 6) q_25,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26), 6) q_26,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27), 6) q_27,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28), 6) q_28,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29), 6) q_29,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30), 6) q_30,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31), 6) q_31,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
+QTY_BUCKET32), 6) q_32,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
+QTY_BUCKET32+QTY_BUCKET33), 6) q_33,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
+QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34), 6) q_34,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
+QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34+QTY_BUCKET35), 6) q_35,
ROUND( sum(QTY_BUCKET1+QTY_BUCKET2+QTY_BUCKET3+QTY_BUCKET4+QTY_BUCKET5+QTY_BUCKET6+QTY_BUCKET7
+QTY_BUCKET8+QTY_BUCKET9+QTY_BUCKET10+QTY_BUCKET11+QTY_BUCKET12+QTY_BUCKET13
+QTY_BUCKET14+QTY_BUCKET15+QTY_BUCKET16+QTY_BUCKET17+QTY_BUCKET18+QTY_BUCKET19
+QTY_BUCKET20+QTY_BUCKET21+QTY_BUCKET22+QTY_BUCKET23+QTY_BUCKET24+QTY_BUCKET25
+QTY_BUCKET26+QTY_BUCKET27+QTY_BUCKET28+QTY_BUCKET29+QTY_BUCKET30+QTY_BUCKET31
+QTY_BUCKET32+QTY_BUCKET33+QTY_BUCKET34+QTY_BUCKET35+QTY_BUCKET36), 6) q_36
FROM msc_hz_ui_lines
WHERE ORDER_TYPE = nvl(arg_order_type,NOT_SELECTED)
AND query_id = arg_query_id
GROUP BY RELATION_GROUP, ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID ;
SELECT named_set, show_graph, category_name,
decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
decode(summary_asn,'Y',ASN,NOT_SELECTED ),
decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
NVL(summary_display_weeks,NOT_SELECTED),
NVL(summary_display_periods,NOT_SELECTED)
, NVL(shift_days, 0)
, decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
, decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
FROM msc_workbench_display_options
WHERE public_flag = 'Y'
ORDER BY named_set ASC
;
SELECT named_set, show_graph, category_name,
decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
decode(summary_asn,'Y',ASN,NOT_SELECTED ),
decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
NVL(summary_display_weeks,NOT_SELECTED),
NVL(summary_display_periods,NOT_SELECTED)
, NVL(shift_days, 0)
, decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
, decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
FROM msc_workbench_display_options
WHERE NVL(public_flag, 'N') <> 'Y'
AND NVL(default_set, 'N') <> 'Y'
ORDER BY named_set ASC
;
SELECT named_set, show_graph, category_name,
decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
decode(summary_asn,'Y',ASN,NOT_SELECTED ),
decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
NVL(summary_display_weeks,NOT_SELECTED),
NVL(summary_display_periods,NOT_SELECTED)
, NVL(shift_days, 0)
, decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
, decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
INTO v_pref, v_graphtype, v_category, v_sales_forecast,
v_order_forecast, v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
v_requisition, v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss,v_o_pab,
v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
v_o_wip,v_o_replenishment,v_o_req,v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
v_o_run_tot_supply,v_o_run_tot_demand,
prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
period_bucket_count
, v_shift_days
, v_net_forecast
, v_total_supply
FROM msc_workbench_display_options
WHERE rtrim(ltrim(named_set)) = rtrim(ltrim(v_pref_name))
AND rownum < 2
AND ( SCE_USER_ID = FND_GLOBAL.user_id
OR PUBLIC_FLAG = 'Y' )
;
SELECT named_set, show_graph, category_name,
decode(summary_seller_forecast,'Y',SALES_FORECAST,NOT_SELECTED ),
decode(summary_forecast,'Y',ORDER_FORECAST_CST,NOT_SELECTED ),
decode(summary_allocated_supply,'Y',SUPPLY_COMMIT,NOT_SELECTED ),
decode(summary_returns_forecast,'Y',G_RETURNS_FORECAST,NOT_SELECTED ), --bug#6893383
decode(summary_def_outbound_shipment,'Y',G_DEFECTIVE_OUTBOUND_SHIPMENT,NOT_SELECTED ),--bug#6893383
decode(summary_hist_sales,'Y',HISTORICAL_SALES,NOT_SELECTED ),
decode(summary_sell_thro_fcst,'Y',SELL_THRU_FORECAST,NOT_SELECTED ),
decode(summary_suppcap,'Y',NEGOTIATED_CAPACITY,NOT_SELECTED ),
decode(summary_ss,'Y',SAFETY_STOCK,NOT_SELECTED ),
decode(summary_pab,'Y',PROJ_AVAIL_BAL,NOT_SELECTED ),
decode(summary_projected_ss,'Y',PROJ_SAFETY_STOCK,NOT_SELECTED ),
decode(summary_allocated_onhand,'Y',ALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unallocated_onhand,'Y',UNALLOCATED_ONHAND,NOT_SELECTED ),
decode(summary_unalct_prjt_avl_bal,'Y',PROJ_UNALOC_AVL_BAL,NOT_SELECTED ),
decode(summary_alcat_prjt_avl_bal,'Y',PROJ_ALLOC_AVL_BAL,NOT_SELECTED ),
decode(summary_po,'Y',PURCHASE_ORDER,NOT_SELECTED ),
decode(summary_sales_orders,'Y',SALES_ORDER,NOT_SELECTED ),
decode(summary_asn,'Y',ASN,NOT_SELECTED ),
decode(summary_receiving,'Y',SHIPMENT_RECEIPT,NOT_SELECTED ),
decode(summary_transit,'Y',INTRANSIT,NOT_SELECTED ),
decode(summary_wip,'Y',WORK_ORDER,NOT_SELECTED ),
decode(summary_po_ack,'Y',PO_ACK,NOT_SELECTED ),
decode(summary_replenishment,'Y',REPLENISHMENT,NOT_SELECTED ),
decode(summary_req,'Y',REQUISITION,NOT_SELECTED ),
decode(summary_purchase_plan, 'Y' , PO_FROM_PLAN, NOT_SELECTED),
decode(summary_release_plan, 'Y', RELEASED_PLAN, NOT_SELECTED),
decode(summary_plan, 'Y', PLANNED_ORDER, NOT_SELECTED),
decode(running_total_supply,'Y',RUN_TOT_SUPPLY,NOT_SELECTED ),
decode(running_total_demand,'Y',RUN_TOT_DEMAND,NOT_SELECTED ),
ORDER_SELLER_FORECAST,ORDER_FORECAST,ORDER_ALLOCATED_SUPPLY,
RETURNS_FORECAST, DEF_OUTBOUND_SHIPMENT, --bug#6893383
ORDER_HIST_SALES,ORDER_SELL_THRO_FCST,ORDER_SUPPCAP,ORDER_SS, ORDER_PAB,
ORDER_PROJECTED_SS,ORDER_ALLOCATED_ONHAND,ORDER_UNALLOCATED_ONHAND,
ORDER_UNALCT_PRJT_AVL_BAL,ORDER_ALCAT_PRJT_AVL_BAL,ORDER_PO, ORDER_WIP, ORDER_PO_ACK,
ORDER_SALES_ORDERS,ORDER_ASN,ORDER_RECEIVING,ORDER_TRANSIT,
ORDER_WIP,ORDER_REPLENISHMENT,ORDER_REQ,ORDER_PURCHASE_PLAN,ORDER_RELEASE_PLAN, ORDER_PLAN,
ORDER_RUNNING_TOTAL_SUPPLY,ORDER_RUNNING_TOTAL_DEMAND,
NVL(prod_sum_level,ITEM_AGG),NVL(org_sum_level,COMPANY_AGG),
NVL(org_sum_level_tp,COMPANY_AGG),NVL(summary_display_days,NOT_SELECTED),
NVL(summary_display_weeks,NOT_SELECTED),
NVL(summary_display_periods,NOT_SELECTED)
, NVL(shift_days, 0)
, decode(net_forecast, 'Y', SELECTED, NOT_SELECTED)
, decode(total_supply, 'Y', SELECTED, NOT_SELECTED)
INTO v_pref, v_graphtype, v_category,v_sales_forecast,
v_order_forecast, v_supply_commit,v_returns_forecast,v_def_outbound_shipment, -- bug#6893383
v_hist_sales,v_sell_thru_fcst, v_negotiated_capacity, v_safety_stock,v_proj_avail_bal,
v_proj_safety_stock,v_alloc_onhand, v_unalloc_onhand, v_proj_unalloc_avl_bal,
v_proj_alloc_avl_bal,v_purchase_order, v_sales_order,v_asn,
v_shipment_receipt, v_intransit,v_work_order, v_po_ack, v_replenishment,
v_requisition,v_po_from_plan, v_released_plan, v_planned_order, v_run_tot_supply, v_run_tot_demand,
v_o_seller_forecast,v_o_forecast,v_o_supply_commit,
v_o_returns_forecast,v_o_def_outbound_shipment, -- bug#6893383
v_o_hist_sales,v_o_sell_thro_fcst,v_o_negcap,v_o_ss, v_o_pab,
v_o_projected_ss,v_o_alct_onhand,v_o_unalct_onhand,
v_o_unalct_prjt_avl_bal,v_o_alcat_prjt_avl_bal,v_o_po, v_o_work_order, v_o_po_ack,
v_o_sales_orders,v_o_asn,v_o_receiving,v_o_transit,
v_o_wip,v_o_replenishment,v_o_req,v_o_po_from_plan, v_o_released_plan, v_o_planned_order,
v_o_run_tot_supply,v_o_run_tot_demand,
prod_agg,myco_agg,tpco_agg,daily_bucket_count,weekly_bucket_count,
period_bucket_count
, v_shift_days
, v_net_forecast
, v_total_supply
FROM msc_workbench_display_options
WHERE upper(default_set) = 'Y'
AND rownum < 2
AND SCE_USER_ID = FND_GLOBAL.user_id ;
select week_start_date into p_start_date
from msc_cal_week_start_dates
where calendar_code = v_cal_code
and exception_set_id = -1
and week_start_date <= SYSDATE + v_shift_days
and next_date > SYSDATE + v_shift_days;
select period_start_date into p_start_date
from msc_period_start_dates
where calendar_code = v_cal_code
and exception_set_id = -1
and period_start_date <= SYSDATE + v_shift_days
and next_date > SYSDATE + v_shift_days;
SELECT
day.calendar_date
BULK COLLECT INTO
daily_bucket_dates
FROM msc_calendar_dates day, msc_cal_week_start_dates week
WHERE day.calendar_code = v_cal_code
and day.exception_set_id = -1
and day.calendar_date >= p_start_date
and day.calendar_date < week.next_date
and week.calendar_code = v_cal_code
and week.exception_set_id = -1
and week.week_start_date <= p_start_date + daily_bucket_count - 1
and week.next_date > p_start_date + daily_bucket_count - 1
order by day.calendar_date asc;
SELECT
day.calendar_date
BULK COLLECT INTO
daily_bucket_dates
FROM msc_calendar_dates day, msc_period_start_dates month
WHERE day.calendar_code = v_cal_code
and day.exception_set_id = -1
and day.calendar_date >= p_start_date
and day.calendar_date < month.next_date
and month.calendar_code = v_cal_code
and month.exception_set_id = -1
and month.period_start_date <= p_start_date + daily_bucket_count - 1
and month.next_date > p_start_date + daily_bucket_count - 1
order by day.calendar_date asc;
SELECT
calendar_date
BULK COLLECT INTO
daily_bucket_dates
FROM msc_calendar_dates
WHERE calendar_code = v_cal_code
and exception_set_id = -1
and calendar_date >= p_start_date
and calendar_date < p_start_date + daily_bucket_count
order by calendar_date asc;
SELECT
week.week_start_date
BULK COLLECT INTO
weekly_bucket_dates
FROM msc_cal_week_start_dates week, msc_period_start_dates month
WHERE week.calendar_code = v_cal_code
and week.exception_set_id = -1
and week.week_start_date >= v_temp_date
and week.week_start_date < month.next_date
and month.calendar_code = v_cal_code
and month.exception_set_id = -1
and month.period_start_date <= v_temp_date + 7*weekly_bucket_count
and month.next_date > v_temp_date + 7*weekly_bucket_count
order by week.week_start_date asc;
SELECT week.week_start_date into v_temp_bucket_start_date
from msc_cal_week_start_dates week
where week.calendar_code = v_cal_code
and week.exception_set_id = -1
and week.week_start_date <= p_start_date
and week.next_date > p_start_date
order by week.week_start_date asc;
SELECT
week.week_start_date
BULK COLLECT INTO
weekly_bucket_dates
FROM msc_cal_week_start_dates week, msc_period_start_dates month
WHERE week.calendar_code = v_cal_code
and week.exception_set_id = -1
and week.next_date > v_temp_bucket_start_date
and month.calendar_code = v_cal_code
and month.exception_set_id = -1
and month.period_start_date < v_temp_bucket_start_date + 7*weekly_bucket_count
and month.next_date >= v_temp_bucket_start_date + 7*weekly_bucket_count
and week.week_start_date < month.next_date
order by week.week_start_date asc;
SELECT
week_start_date
BULK COLLECT INTO
weekly_bucket_dates
FROM msc_cal_week_start_dates
WHERE calendar_code = v_cal_code
and exception_set_id = -1
and week_start_date > v_temp_date
and week_start_date <= v_temp_date + 7*weekly_bucket_count
order by week_start_date asc;
SELECT
week_start_date
BULK COLLECT INTO
weekly_bucket_dates
FROM msc_cal_week_start_dates
WHERE calendar_code = v_cal_code
and exception_set_id = -1
and next_date > p_start_date
and next_date <= p_start_date + 7*weekly_bucket_count
order by week_start_date asc;
select month.period_start_date into v_month_bkt_start_date
from msc_period_start_dates month
where month.calendar_code = v_cal_code
and month.exception_set_id = -1
and month.period_start_date <= p_start_date
and month.next_date > p_start_date
order by month.period_start_date asc;
select period_start_date
BULK COLLECT INTO
monthly_bucket_dates
from msc_period_start_dates
where calendar_code = v_cal_code
and exception_set_id = -1
and level <= period_bucket_count
start with period_start_date <= v_month_bkt_start_date
and next_date > v_month_bkt_start_date
connect by (
PRIOR calendar_code = calendar_code
and PRIOR exception_set_id = exception_set_id
and PRIOR sr_instance_id = sr_instance_id
and PRIOR next_date = period_start_date
and PRIOR period_start_date < period_start_date
)
order by period_start_date asc;
select next_date into v_last_bkt_date
from msc_period_start_dates
where calendar_code = v_cal_code
and exception_set_id = -1
and period_start_date = var_dates(counter);
l_sel_count := ' SELECT COUNT(distinct customer_site_id), COUNT(distinct supplier_site_id) ';
l_sel_cust_site := ' SELECT distinct customer_site_id, customer_id ';
l_sel_sup_site := ' SELECT distinct supplier_site_id, supplier_id ';
* This is required to insert into the headers table.
*/
PROCEDURE set_date_variables IS
k INTEGER := 0;
* This function is called when inserting records into the lines table
*
* @param the order type.
* @return the order rank
*/
FUNCTION get_order_rank (arg_order_type IN NUMBER) RETURN NUMBER
IS
BEGIN
if arg_order_type = SALES_FORECAST then
return v_o_seller_forecast;
is select 1
from msc_sup_dem_update_security_v a --,
--msc_sup_dem_entries_ui_v b
where --a.transaction_id = b.transaction_id
a.customer_id = previous_rec.customer_id
and a.customer_site_id = previous_rec.cust_site_id
and a.supplier_id = previous_rec.supplier_id
and a.supplier_site_id = previous_rec.supp_site_id
and a.publisher_order_type = var_order(arg_pos)
and a.inventory_item_id = var_item_id(arg_pos);
select sys_context('MSC','COMPANY_ID') into v_viewer_id from dual;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id= activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and customer_site_id = activity_rec.cust_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and supplier_id = activity_rec.supplier_id
and supplier_site_id = activity_rec.supp_site_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and customer_id = activity_rec.customer_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_base_qty
from msc_sup_dem_entries_ui_v
where base_item_id = activity_rec.item_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
select nvl(sum(decode (sys_context('MSC','COMPANY_ID'), publisher_id,primary_quantity,
customer_id, tp_quantity,
supplier_id, tp_quantity, quantity)),0) into l_config_qty
from msc_sup_dem_entries_ui_v
where inventory_item_id = activity_rec.item_id
and supplier_id = activity_rec.supplier_id
and publisher_order_type = activity_rec.order_type
and key_date < p_start_date;
SELECT distinct inventory_item_id
FROM msc_item_categories
where category_name = arg_category_name
and category_set_id = FND_PROFILE.VALUE('MSCX_CP_HZ_CATEGORY_SET');
select msc_form_query_s.nextval into l_query_id from dual;
INSERT INTO msc_form_query
(
QUERY_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
DATE1,DATE2,DATE3,DATE4,DATE5,DATE6,DATE7,DATE8,NUMBER1,NUMBER2,NUMBER3,NUMBER4,NUMBER5,
NUMBER6,NUMBER7,NUMBER8,NUMBER9,NUMBER10,NUMBER11,NUMBER12,NUMBER13,NUMBER14,NUMBER15,
REQUEST_ID,CHAR1,CHAR2,CHAR3,CHAR4,CHAR5,CHAR6,CHAR7,CHAR8,CHAR9,PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,PROGRAM_ID,NUMBER16,CHAR10,CHAR11,CHAR12,CHAR13,CHAR14,CHAR15 )
VALUES
(
l_query_id,sysdate,fnd_profile.value('USER_ID'),sysdate,fnd_profile.value('USER_ID'),null,
null,null,null,null,null,null,null,null, l_item_id,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null
);
l_statement := 'SELECT nvl(';
l_statement := l_statement || ' and inventory_item_id in (select number1 from msc_form_query where query_id = ' || l_query_id || ')';
* 2. select for each item if an order forecast is present where the user company
* is not the doc owner.
* 3. check if corresponding supply commit is present.
* 4. if not add an editable row with 0 as the value for all columns
* and user company as the publisher of the doc.
* 5. set editable flag to editable.
*/
PROCEDURE add_companion_row(arg_query_id IN NUMBER) IS
v_name VARCHAR2(250);
SELECT a.relation_group,a.order_relation_group,a.inventory_item_id,a.item_name,a.item_description,
a.supplier_id,a.customer_id,a.supplier_site_id,a.customer_site_id,
a.supplier_name,a.customer_name,a.supplier_org_code,a.customer_org_code,
a.order_type_rank,a.uom,a.cust_item,a.sup_item,a.cust_item_desc,
a.sup_item_desc,a.tp_uom,a.owner_item,a.owner_item_desc
BULK COLLECT INTO
var_relation, var_order_relation,var_item_id, var_item_name, var_item_name_desc,
var_supplier_id, var_customer_id, var_supplier_site_id,
var_customer_site_id, var_supplier, var_customer, var_supplier_org,
var_customer_org, var_order_rank, var_uom, var_cust_item, var_sup_item,
var_cust_item_desc, var_sup_item_desc, var_tp_uom, var_owner_item,
var_owner_item_desc
FROM msc_hz_ui_lines a
WHERE a.query_id = arg_query_id
AND a.order_type = SUPPLY_COMMIT
AND a.customer_id = sys_context('MSC','COMPANY_ID')
AND a.bucket_type <> 0
AND ( period_bucket_count > 0
OR ( weekly_bucket_count > 0 and a.bucket_type <> MONTH_BUCKET ) -- cannot be month
OR ( daily_bucket_count > 0 and a.bucket_type not in (MONTH_BUCKET, WEEK_BUCKET) )
)
AND not exists
(select * from msc_sup_dem_entries b
where b.publisher_order_type = ORDER_FORECAST_CST
and b.inventory_item_id = a.inventory_item_id
and b.supplier_id = a.supplier_id
and b.customer_id = a.customer_id
-- make companion row to the site level
and b.supplier_site_id = a.supplier_site_id
and b.customer_site_id = a.customer_site_id
and b.publisher_name = a.customer_name
and b.key_date >= p_start_date
and b.key_date <= v_last_bkt_date
)
ORDER BY a.item_name,a.relation_group ;
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,
INVENTORY_ITEM_ID,SUP_ITEM,CUST_ITEM,SUP_ITEM_DESC,CUST_ITEM_DESC,
OWNER_ITEM,OWNER_ITEM_DESC,TP_UOM,UOM_CODE,
QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG,BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,
next_item,unbucketed_qty)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
sys_context('MSC','COMPANY_NAME'),var_customer_org(i), var_item_name(i),
var_item_name_desc(i),v_o_forecast, ORDER_FORECAST_CST, v_name,
var_tp_uom(i), var_supplier(i), var_customer(i), var_supplier_org(i),
var_customer_org(i),var_supplier_id(i),var_customer_id(i),
var_supplier_site_id(i),var_customer_site_id(i), var_item_id(i),
var_sup_item(i),var_cust_item(i),var_sup_item_desc(i),var_cust_item_desc(i),
var_owner_item(i), var_owner_item_desc(i),var_uom(i), var_tp_uom(i),
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
0,decode(period_bucket_count,0,
(decode(weekly_bucket_count,0,DAY_BUCKET,WEEK_BUCKET)),
MONTH_BUCKET),
var_customer_id(i),var_customer_site_id(i),v_next_item,0) ;
SELECT a.relation_group,a.order_relation_group,a.inventory_item_id,a.item_name,a.item_description,
a.supplier_id,a.customer_id,a.supplier_site_id,a.customer_site_id,
a.supplier_name,a.customer_name,a.supplier_org_code,a.customer_org_code,
a.order_type_rank,a.uom,a.cust_item,a.sup_item,a.cust_item_desc,
a.sup_item_desc,a.tp_uom,a.owner_item,a.owner_item_desc
BULK COLLECT INTO
var_relation, var_order_relation,var_item_id, var_item_name, var_item_name_desc,
var_supplier_id, var_customer_id, var_supplier_site_id,
var_customer_site_id, var_supplier, var_customer, var_supplier_org,
var_customer_org, var_order_rank, var_uom, var_cust_item, var_sup_item,
var_cust_item_desc, var_sup_item_desc, var_tp_uom, var_owner_item,
var_owner_item_desc
FROM msc_hz_ui_lines a
WHERE a.query_id = arg_query_id
AND a.order_type = ORDER_FORECAST_CST
AND a.supplier_id = sys_context('MSC','COMPANY_ID')
AND a.bucket_type <> 0
AND ( period_bucket_count > 0
OR ( weekly_bucket_count > 0 and a.bucket_type <> MONTH_BUCKET ) -- cannot be month
OR ( daily_bucket_count > 0 and a.bucket_type not in (MONTH_BUCKET, WEEK_BUCKET) )
)
AND not exists
(select * from msc_sup_dem_entries b
where b.publisher_order_type = SUPPLY_COMMIT
and b.inventory_item_id = a.inventory_item_id
and b.supplier_id = a.supplier_id
and b.customer_id = a.customer_id
-- make companion row to the site level
and b.supplier_site_id = a.supplier_site_id
and b.customer_site_id = a.customer_site_id
and b.publisher_name = a.supplier_name
and b.key_date >= p_start_date
and b.key_date <= v_last_bkt_date
)
ORDER BY a.item_name,a.relation_group ;
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,
INVENTORY_ITEM_ID,SUP_ITEM,CUST_ITEM,SUP_ITEM_DESC,CUST_ITEM_DESC,
OWNER_ITEM,OWNER_ITEM_DESC,TP_UOM,UOM_CODE,
QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG,BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,
next_item, unbucketed_qty)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
sys_context('MSC','COMPANY_NAME'),var_supplier_org(i), var_item_name(i),
var_item_name_desc(i),v_o_supply_commit, SUPPLY_COMMIT, v_name,
var_tp_uom(i),var_supplier(i),var_customer(i),var_supplier_org(i),
var_customer_org(i),var_supplier_id(i),var_customer_id(i),
var_supplier_site_id(i),var_customer_site_id(i), var_item_id(i),
var_sup_item(i), var_cust_item(i),var_sup_item_desc(i),var_cust_item_desc(i),
var_owner_item(i),var_owner_item_desc(i),var_uom(i), var_tp_uom(i),
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
0,decode(period_bucket_count,0,
(decode(weekly_bucket_count,0,DAY_BUCKET,WEEK_BUCKET)),
MONTH_BUCKET),
var_supplier_id(i),var_supplier_site_id(i),v_next_item,0) ;
SELECT a.inventory_item_id, a.line_id
BULK COLLECT INTO var_item_id, var_line_id
FROM msc_hz_ui_lines a
WHERE a.query_id = arg_query_id
ORDER BY item_name, order_relation_group, order_type_rank ;
UPDATE msc_hz_ui_lines
SET next_item = var_next_item(i)
WHERE query_id = arg_query_id
AND line_id = var_line_id(i);
* the foll procedure inserts the total into the temp tables.
* NOTE: currently this is not being used on the UI.
*
* @param the order type.
*/
PROCEDURE insert_total(v_order IN NUMBER) IS
v_name VARCHAR2(250);
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
var_item_name_desc(i),0,v_order ,v_name,var_uom(i),
var_supplier(i),var_customer(i),var_supplier_org(i),
var_customer_org(i),var_supplier_id(i),var_customer_id(i),
var_supplier_site_id(i),var_customer_site_id(i),var_item_id(i),
var_qty_nobkt(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),
var_qty1(i),var_qty2(i),var_qty3(i),var_qty4(i),var_qty5(i),
var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),var_qty10(i),
var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),var_qty15(i),
var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),var_qty20(i),
var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),var_qty25(i),
var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),var_qty30(i),
var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
arg_err_msg := arg_err_msg || ' insert total ' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_total', SQLERRM);
UPDATE msc_hz_ui_lines
SET UNBUCKETED_QTY = -UNBUCKETED_QTY,
QTY_BUCKET1 = -QTY_BUCKET1,
QTY_BUCKET2 = -QTY_BUCKET2,
QTY_BUCKET3 = -QTY_BUCKET3,
QTY_BUCKET4 = -QTY_BUCKET4,
QTY_BUCKET5 = -QTY_BUCKET5,
QTY_BUCKET6 = -QTY_BUCKET6,
QTY_BUCKET7 = -QTY_BUCKET7,
QTY_BUCKET8 = -QTY_BUCKET8,
QTY_BUCKET9 = -QTY_BUCKET9,
QTY_BUCKET10 = -QTY_BUCKET10,
QTY_BUCKET11 = -QTY_BUCKET11,
QTY_BUCKET12 = -QTY_BUCKET12,
QTY_BUCKET13 = -QTY_BUCKET13,
QTY_BUCKET14 = -QTY_BUCKET14,
QTY_BUCKET15 = -QTY_BUCKET15,
QTY_BUCKET16 = -QTY_BUCKET16,
QTY_BUCKET17 = -QTY_BUCKET17,
QTY_BUCKET18 = -QTY_BUCKET18,
QTY_BUCKET19 = -QTY_BUCKET19,
QTY_BUCKET20 = -QTY_BUCKET20,
QTY_BUCKET21 = -QTY_BUCKET21,
QTY_BUCKET22 = -QTY_BUCKET22,
QTY_BUCKET23 = -QTY_BUCKET23,
QTY_BUCKET24 = -QTY_BUCKET24,
QTY_BUCKET25 = -QTY_BUCKET25,
QTY_BUCKET26 = -QTY_BUCKET26,
QTY_BUCKET27 = -QTY_BUCKET27,
QTY_BUCKET28 = -QTY_BUCKET28,
QTY_BUCKET29 = -QTY_BUCKET29,
QTY_BUCKET30 = -QTY_BUCKET30,
QTY_BUCKET31 = -QTY_BUCKET31,
QTY_BUCKET32 = -QTY_BUCKET32,
QTY_BUCKET33 = -QTY_BUCKET33,
QTY_BUCKET34 = -QTY_BUCKET34,
QTY_BUCKET35 = -QTY_BUCKET35,
QTY_BUCKET36 = -QTY_BUCKET36
WHERE order_type = p_order_type
AND query_id = arg_query_id;
PROCEDURE delete_order_type(arg_query_id NUMBER, p_order_type NUMBER)
IS
BEGIN
DELETE FROM msc_hz_ui_lines
WHERE order_type = p_order_type
AND query_id = arg_query_id;
PROCEDURE insert_net_forecast(arg_query_id NUMBER)
IS
CURSOR c_net_forecast
IS
SELECT ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID,
ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
FROM msc_hz_ui_lines
WHERE ORDER_TYPE IN (ORDER_FORECAST_CST, REQUISITION,PURCHASE_ORDER)
AND query_id = arg_query_id
GROUP BY ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID
HAVING sum(decode(order_type, ORDER_FORECAST_CST, 1, 0)) > 0;
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,'NA',nvl(var_order_relation(i),'NA'),
nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
var_item_name_desc(i),39,-1,v_calculation_name,var_uom(i),var_supplier(i),
var_customer(i),var_supplier_org(i),var_customer_org(i),
var_supplier_id(i),var_customer_id(i),'NA',
'NA',var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
END insert_net_forecast;
select quantity into default_ss
from msc_sup_dem_entries_ui_v
where nvl(base_item_id,inventory_item_id) = var_item_id(i)
and publisher_order_type = 7
and publisher_id = var_pub_id(i)
and publisher_site_id = var_pub_site_id(i)
and key_date < p_start_date
and rownum < 2
order by key_date asc;
select quantity into default_pab
from msc_sup_dem_entries_ui_v
where nvl(base_item_id,inventory_item_id) = var_item_id(i)
and publisher_order_type = 8
and publisher_id = var_pub_id(i)
and publisher_site_id = var_pub_site_id(i)
and key_date < p_start_date
and rownum < 2
order by key_date asc;
PROCEDURE insert_total_supply(arg_query_id NUMBER)
IS
CURSOR c_total_supply
IS
SELECT ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID,
ROUND( sum(UNBUCKETED_QTY), 6) q_0,ROUND( sum(QTY_BUCKET1), 6) q_1,
ROUND( sum(QTY_BUCKET2), 6) q_2,ROUND( sum(QTY_BUCKET3), 6) q_3, ROUND( sum(QTY_BUCKET4), 6) q_4,
ROUND( sum(QTY_BUCKET5), 6) q_5,ROUND( sum(QTY_BUCKET6), 6) q_6,ROUND( sum(QTY_BUCKET7), 6) q_7,
ROUND( sum(QTY_BUCKET8), 6) q_8,ROUND( sum(QTY_BUCKET9), 6) q_9,ROUND( sum(QTY_BUCKET10), 6) q_10,
ROUND( sum(QTY_BUCKET11), 6) q_11,ROUND( sum(QTY_BUCKET12), 6) q_12,ROUND( sum(QTY_BUCKET13), 6) q_13,
ROUND( sum(QTY_BUCKET14), 6) q_14,ROUND( sum(QTY_BUCKET15), 6) q_15,ROUND( sum(QTY_BUCKET16), 6) q_16,
ROUND( sum(QTY_BUCKET17), 6) q_17,ROUND( sum(QTY_BUCKET18), 6) q_18,ROUND( sum(QTY_BUCKET19), 6) q_19,
ROUND( sum(QTY_BUCKET20), 6) q_20,ROUND( sum(QTY_BUCKET21), 6) q_21,ROUND( sum(QTY_BUCKET22), 6) q_22,
ROUND( sum(QTY_BUCKET23), 6) q_23,ROUND( sum(QTY_BUCKET24), 6) q_24,ROUND( sum(QTY_BUCKET25), 6) q_25,
ROUND( sum(QTY_BUCKET26), 6) q_26,ROUND( sum(QTY_BUCKET27), 6) q_27,ROUND( sum(QTY_BUCKET28), 6) q_28,
ROUND( sum(QTY_BUCKET29), 6) q_29,ROUND( sum(QTY_BUCKET30), 6) q_30,ROUND( sum(QTY_BUCKET31), 6) q_31,
ROUND( sum(QTY_BUCKET32), 6) q_32,ROUND( sum(QTY_BUCKET33), 6) q_33,ROUND( sum(QTY_BUCKET34), 6) q_34,
ROUND( sum(QTY_BUCKET35), 6) q_35,ROUND( sum(QTY_BUCKET36), 6) q_36
FROM msc_hz_ui_lines
WHERE ORDER_TYPE IN (SUPPLY_COMMIT,SALES_ORDER)
AND query_id = arg_query_id
GROUP BY ORDER_RELATION_GROUP, FROM_COMPANY_NAME,FROM_ORG_CODE,ITEM_NAME,ITEM_DESCRIPTION,
SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,UOM,
SUPPLIER_ID,CUSTOMER_ID,INVENTORY_ITEM_ID;
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,'NA',nvl(var_order_relation(i),'NA'),
nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
var_item_name_desc(i),41,-1,v_calculation_name,var_uom(i),var_supplier(i),
var_customer(i),var_supplier_org(i),var_customer_org(i),
var_supplier_id(i),var_customer_id(i),'NA',
'NA',var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
END insert_total_supply;
* the foll procedure inserts the running total row into the temp tables.
* NOTE: currently this is being used on the UI for
* only order forecast and supply commit
*
* @param the order type.
*/
PROCEDURE insert_running_total(v_order_type IN NUMBER) IS
v_name VARCHAR2(250);
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,ORDER_TYPE_RANK,ORDER_TYPE,ORDER_TYPE_DESC,
UOM,SUPPLIER_NAME,CUSTOMER_NAME,SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,
SUPPLIER_ID,CUSTOMER_ID,SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,
UNBUCKETED_QTY,QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,
QTY_BUCKET5,QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,
QTY_BUCKET10,QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,
QTY_BUCKET15,QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,
QTY_BUCKET20,QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,
QTY_BUCKET25,QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,
QTY_BUCKET30,QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,
QTY_BUCKET35,QTY_BUCKET36,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,
OLD_QTY36,EDITABLE_FLAG)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,nvl(var_relation(i),'NA'),nvl(var_order_relation(i),'NA'),
nvl(var_from_co_name(i),'NA'),var_from_org_name(i),var_item_name(i),
var_item_name_desc(i),40,-1,v_name,var_uom(i),var_supplier(i),
var_customer(i),var_supplier_org(i),var_customer_org(i),
var_supplier_id(i),var_customer_id(i),var_supplier_site_id(i),
var_customer_site_id(i),var_item_id(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),var_qty1(i),var_qty2(i),
var_qty3(i),var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),
var_qty8(i),var_qty9(i),var_qty10(i),var_qty11(i),var_qty12(i),
var_qty13(i),var_qty14(i),var_qty15(i),var_qty16(i),var_qty17(i),
var_qty18(i),var_qty19(i),var_qty20(i),var_qty21(i),var_qty22(i),
var_qty23(i),var_qty24(i),var_qty25(i),var_qty26(i),var_qty27(i),
var_qty28(i),var_qty29(i),var_qty30(i),var_qty31(i),var_qty32(i),
var_qty33(i),var_qty34(i),var_qty35(i),var_qty36(i),1) ;
arg_err_msg := arg_err_msg || ' insert running total ' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module || 'insert_running_total', SQLERRM);
* insert the buckets into the msc_hz_ui_headers table.
*/
BEGIN
arg_next_link := 'N';
IF v_requisition = NOT_SELECTED THEN
v_delete_requisition := TRUE;
IF v_purchase_order = NOT_SELECTED THEN
v_delete_purchase_order := TRUE;
SELECT msc_x_hz_ui_query_id_s.nextval INTO arg_query_id FROM Dual;
INSERT INTO msc_hz_ui_headers
(QUERY_ID,NO_OF_BUCKETS,PROD_SUM_LEVEL, ORG_SUM_LEVEL, ORG_SUM_LEVEL_TP,
BUCKET1,BUCKET2,BUCKET3,BUCKET4,BUCKET5,BUCKET6,BUCKET7,BUCKET8,BUCKET9,
BUCKET10,BUCKET11,BUCKET12,BUCKET13,BUCKET14,BUCKET15,BUCKET16,BUCKET17,
BUCKET18,BUCKET19,BUCKET20,BUCKET21,BUCKET22,BUCKET23,BUCKET24,BUCKET25,
BUCKET26,BUCKET27,BUCKET28,BUCKET29,BUCKET30,BUCKET31,BUCKET32,BUCKET33,
BUCKET34,BUCKET35,BUCKET36,UNDATED_BUCKET_FLAG,USER_PREFERENCE,GRAPH_TYPE,
GRAPH_TITLE,LAST_BUCKET)
VALUES
(arg_query_id,g_num_of_buckets,prod_agg, myco_agg, tpco_agg,
var_dates(1),var_dates(2),var_dates(3),var_dates(4),var_dates(5),
var_dates(6),var_dates(7),var_dates(8),var_dates(9),var_dates(10),
var_dates(11),var_dates(12),var_dates(13),var_dates(14),var_dates(15),
var_dates(16),var_dates(17),var_dates(18),var_dates(19),var_dates(20),
var_dates(21),var_dates(22),var_dates(23),var_dates(24),var_dates(25),
var_dates(26),var_dates(27),var_dates(28),var_dates(29),var_dates(30),
var_dates(31),var_dates(32),var_dates(33),var_dates(34),var_dates(35),
var_dates(36),'Y',v_pref,v_graphtype,v_graphtitle,v_last_bkt_date ) ;
SELECT nvl(no_of_buckets,NOT_SELECTED)
INTO record_cnt
FROM msc_hz_ui_headers
WHERE query_id = arg_query_id ;
var_relation.delete;
var_order_relation.delete;
var_relation.delete(i);
var_order_relation.delete(i);
var_item_name.delete(i);
var_item_name.delete(i);
var_supplier_id.delete(i);
var_customer_id.delete(i);
var_order.delete(i);
INSERT INTO msc_hz_ui_lines
(LINE_ID,QUERY_ID,RELATION_GROUP,ORDER_RELATION_GROUP,FROM_COMPANY_NAME,FROM_ORG_CODE,
ITEM_NAME,ITEM_DESCRIPTION,SUPPLIER_NAME,CUSTOMER_NAME,
SUPPLIER_ORG_CODE,CUSTOMER_ORG_CODE,ORDER_TYPE_RANK,
ORDER_TYPE,ORDER_TYPE_DESC,shipping_control,UOM,SUPPLIER_ID,CUSTOMER_ID,
SUPPLIER_SITE_ID,CUSTOMER_SITE_ID,INVENTORY_ITEM_ID,PAST_DUE_QTY, UNBUCKETED_QTY,
QTY_BUCKET1,QTY_BUCKET2,QTY_BUCKET3,QTY_BUCKET4,QTY_BUCKET5,
QTY_BUCKET6,QTY_BUCKET7,QTY_BUCKET8,QTY_BUCKET9,QTY_BUCKET10,
QTY_BUCKET11,QTY_BUCKET12,QTY_BUCKET13,QTY_BUCKET14,QTY_BUCKET15,
QTY_BUCKET16,QTY_BUCKET17,QTY_BUCKET18,QTY_BUCKET19,QTY_BUCKET20,
QTY_BUCKET21,QTY_BUCKET22,QTY_BUCKET23,QTY_BUCKET24,QTY_BUCKET25,
QTY_BUCKET26,QTY_BUCKET27,QTY_BUCKET28,QTY_BUCKET29,QTY_BUCKET30,
QTY_BUCKET31,QTY_BUCKET32,QTY_BUCKET33,QTY_BUCKET34,QTY_BUCKET35,
QTY_BUCKET36,EDITABLE_FLAG,OLD_QTY1,OLD_QTY2,OLD_QTY3,OLD_QTY4,
OLD_QTY5,OLD_QTY6,OLD_QTY7,OLD_QTY8,OLD_QTY9,OLD_QTY10,OLD_QTY11,
OLD_QTY12,OLD_QTY13,OLD_QTY14,OLD_QTY15,OLD_QTY16,OLD_QTY17,
OLD_QTY18,OLD_QTY19,OLD_QTY20,OLD_QTY21,OLD_QTY22,OLD_QTY23,
OLD_QTY24,OLD_QTY25,OLD_QTY26,OLD_QTY27,OLD_QTY28,OLD_QTY29,
OLD_QTY30,OLD_QTY31,OLD_QTY32,OLD_QTY33,OLD_QTY34,OLD_QTY35,OLD_QTY36,
BUCKET_TYPE,PUBLISHER_ID,PUBLISHER_SITE_ID,NEXT_ITEM,SUP_ITEM)
VALUES
(msc_x_hz_ui_line_id_s.nextval,arg_query_id,var_relation(i),var_order_relation(i),
var_from_co_name(i),var_from_org_name(i),var_item_name(i),
var_item_name_desc(i),var_supplier(i),var_customer(i),
var_supplier_org(i),var_customer_org(i),var_order_rank(i),
var_order(i),var_order_desc(i),var_ship_ctrl(i),var_uom(i),var_supplier_id(i),
var_customer_id(i),var_supplier_site_id(i),var_customer_site_id(i),
var_item_id(i),var_past_due_qty(i),var_qty_nobkt(i),var_qty1(i),var_qty2(i),var_qty3(i),
var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),
var_qty10(i),var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),
var_qty15(i),var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),
var_qty20(i),var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),
var_qty25(i),var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),
var_qty30(i),var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),
var_qty35(i),var_qty36(i),var_edit_flag(i),var_qty1(i),var_qty2(i),var_qty3(i),
var_qty4(i),var_qty5(i),var_qty6(i),var_qty7(i),var_qty8(i),var_qty9(i),
var_qty10(i),var_qty11(i),var_qty12(i),var_qty13(i),var_qty14(i),
var_qty15(i),var_qty16(i),var_qty17(i),var_qty18(i),var_qty19(i),
var_qty20(i),var_qty21(i),var_qty22(i),var_qty23(i),var_qty24(i),
var_qty25(i),var_qty26(i),var_qty27(i),var_qty28(i),var_qty29(i),
var_qty30(i),var_qty31(i),var_qty32(i),var_qty33(i),var_qty34(i),
var_qty35(i),var_qty36(i),var_bkt_type(i),var_pub_id(i),var_pub_site_id(i),
var_next_item(i),var_sup_item(i));
arg_err_msg := arg_err_msg || ' insert records ' || SQLERRM ;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Insert record '||SQLERRM);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
* Update other reqd fields.
* update owner item, sup item, cust item, and uom for
* all the editable records.
*/
if myco_agg = ORG_AGG and tpco_agg = ORG_AGG THEN
BEGIN
SELECT line_id, customer_name,supplier_name,inventory_item_id,
order_type,from_company_name
BULK COLLECT INTO v_line_id, v_cust_name, v_sup_name,
v_item_id,v_order,v_pub_name
FROM msc_hz_ui_lines
WHERE editable_flag = 0
AND query_id = arg_query_id -- this is required to avoid the comp row disappearing act.
OR order_type in (ORDER_FORECAST_CST, SUPPLY_COMMIT) ;
SELECT owner_item_name,supplier_item_name,customer_item_name,
uom_code,nvl(tp_uom_code, uom_code) ,owner_item_description,
supplier_item_description,customer_item_description
INTO v_owner_item,v_sup_item,v_cust_item,v_uom_code,v_tp_uom,
v_owner_item_desc,v_sup_item_desc,v_cust_item_desc
FROM msc_sup_dem_entries_ui_v
WHERE inventory_item_id = v_item_id(i)
AND publisher_order_type = v_order(i)
AND publisher_name = v_pub_name(i)
AND customer_name = v_cust_name(i)
AND supplier_name = v_sup_name(i)
AND ROWNUM < 2 ;
UPDATE msc_hz_ui_lines
SET owner_item = var_owner_item(k),
cust_item = var_cust_item(k),
sup_item = var_sup_item(k),
owner_item_desc = var_owner_item_desc(k),
sup_item_desc = var_sup_item_desc(k),
cust_item_desc = var_cust_item_desc(k),
tp_uom = var_tp_uom(k),
uom_code = var_uom_code(k)
WHERE query_id = arg_query_id
AND line_id = var_line_id(k);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
* and both orders are not present then insert a record with
* the tp as the publisher, and the qty_bucket(X) value
* defaulted to the companion rows values
* but the old_qty(X) is still 0.
*/
BEGIN
add_companion_row(arg_query_id);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
CUSTOMER_ORG_CODE = NULL
WHERE query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null
WHERE FROM_COMPANY_NAME = v_user_company
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET SUPPLIER_ORG_CODE = null
WHERE SUPPLIER_NAME = v_user_company -- user is the supplier
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET CUSTOMER_ORG_CODE = null
WHERE CUSTOMER_NAME = v_user_company -- user is the customer
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null
WHERE FROM_COMPANY_NAME <> v_user_company
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET SUPPLIER_ORG_CODE = null
WHERE CUSTOMER_NAME = v_user_company -- user is the customer
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET CUSTOMER_ORG_CODE = null
WHERE SUPPLIER_NAME = v_user_company -- user is the customer
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
CUSTOMER_ORG_CODE = NULL
WHERE SUPPLIER_NAME <> v_user_company and CUSTOMER_NAME <> v_user_company ;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null,SUPPLIER_ORG_CODE = NULL,
CUSTOMER_ORG_CODE = NULL
WHERE SUPPLIER_NAME <> v_user_company
AND CUSTOMER_NAME <> v_user_company
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET FROM_COMPANY_NAME = null, FROM_ORG_CODE = null
WHERE FROM_COMPANY_NAME <> v_user_company
AND (SUPPLIER_NAME = v_user_company OR CUSTOMER_NAME = v_user_company )
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET customer_name = FND_MESSAGE.GET_STRING('MSC','MSC_X_HZ_TP_ALL'),
customer_org_code = NULL
--,customer_site_id = NULL
WHERE SUPPLIER_NAME = v_user_company -- user is supplier
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET SUPPLIER_NAME = FND_MESSAGE.GET_STRING('MSC','MSC_X_HZ_TP_ALL'),
SUPPLIER_ORG_CODE = NULL
WHERE CUSTOMER_NAME = v_user_company -- user is customer
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET supplier_org_code = null
WHERE SUPPLIER_NAME = v_user_company -- user is supplier
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET customer_org_code = null
WHERE CUSTOMER_NAME = v_user_company -- user is customer
AND query_id = arg_query_id ;
UPDATE msc_hz_ui_lines
SET FROM_ORG_CODE = null
WHERE FROM_COMPANY_NAME <> v_user_company
AND query_id = arg_query_id ;
arg_err_msg := arg_err_msg || ' update ' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Update '||SQLERRM);
arg_err_msg := arg_err_msg || ' update-2' || SQLERRM;
FND_LOG.STRING(FND_LOG.LEVEL_ERROR,module||'populate_bucketed_quantity','Update '||SQLERRM);
* select from the temp table and insert into temp table
* for running total demad/supply
* current total + running_total of prev bucket
* select the same as below then loop through
* curr bucket qty (var_qty) := running_total + curr_total
*/
-- NOW HANDLE RUNNING TOTAL.
BEGIN
if v_run_tot_demand > 0 then
-- open the cursor pass the query id and the order types where clause.
-- bulk insert
insert_running_total(ORDER_FORECAST_CST);
insert_running_total(SUPPLY_COMMIT);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
insert_net_forecast(arg_query_id);
IF v_delete_requisition = TRUE THEN
v_requisition := NOT_SELECTED;
delete_order_type(arg_query_id, REQUISITION);
IF v_delete_purchase_order = TRUE THEN
v_purchase_order := NOT_SELECTED;
delete_order_type(arg_query_id, PURCHASE_ORDER);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
insert_total_supply(arg_query_id);
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
UPDATE msc_hz_ui_headers
SET undated_bucket_flag = 'Y'
WHERE query_id = arg_query_id;
UPDATE msc_hz_ui_headers
SET undated_bucket_flag = 'N'
WHERE query_id = arg_query_id;
SELECT count(*)
INTO arg_num_rowset
FROM msc_hz_ui_lines
WHERE query_id = arg_query_id
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_DEMAND')
AND order_type_desc <> fnd_message.get_string('MSC','MSC_X_HZ_RUN_SUPPLY')
;
SELECT meaning INTO v_name
FROM fnd_lookup_values
WHERE lookup_type = v_lookup_type
AND lookup_code = nvl(v_lookup_code,-1)
AND language = userenv('lang');