Untitled
unknown
plain_text
5 months ago
3.6 kB
4
Indexable
with VAR_PO_NUMBER_BC as (SELECT S.AT,S.LABEL_REQUEST_ID FROM ( SELECT WLR.LABEL_REQUEST_ID, NVL('K'||ol.cust_po_number,'') AT FROM WMS_LABEL_REQUESTS WLR, OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH where 1=1 AND OL.HEADER_ID = OH.HEADER_ID AND WLR.SALES_ORDER_HEADER_ID = OH.HEADER_ID AND WLR.SALES_ORDER_LINE_ID = OL.LINE_ID )S), VAR_PO_LINE_BC as (SELECT S.AT,S.LABEL_REQUEST_ID FROM ( SELECT WLR.LABEL_REQUEST_ID, NVL('K'||ol.cust_po_number,'') AT FROM WMS_LABEL_REQUESTS WLR, OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH where 1=1 AND OL.HEADER_ID = OH.HEADER_ID AND WLR.SALES_ORDER_HEADER_ID = OH.HEADER_ID AND WLR.SALES_ORDER_LINE_ID = OL.LINE_ID )S), VAR_CUST_PART_BC as (select val,WLR.LABEL_REQUEST_ID from WMS_LABEL_REQUESTS WLR,(select wlr.label_request_id, 'P'||regexp_substr(NVL(cust.CUSTOMER_ITEM_NUMBER,' '), '^(.+);(.+)$', 1, 1, NULL, 2) val from (SELECT mci.customer_id, MCIX.INVENTORY_ITEM_ID, MCI.CUSTOMER_ITEM_NUMBER, MCIX.PREFERENCE_NUMBER, MCI.CUSTOMER_ITEM_DESC FROM MTL_CUSTOMER_ITEM_XREFS MCIX, MTL_CUSTOMER_ITEMS MCI, (SELECT MCIX.INVENTORY_ITEM_ID, MAX(MCIX.PREFERENCE_NUMBER) RANK FROM MTL_CUSTOMER_ITEM_XREFS MCIX, MTL_CUSTOMER_ITEMS MCI WHERE MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID AND MCI.INACTIVE_FLAG = 'N' AND MCIX.INACTIVE_FLAG = 'N' GROUP BY MCIX.INVENTORY_ITEM_ID) RANK WHERE RANK.INVENTORY_ITEM_ID = MCIX.INVENTORY_ITEM_ID AND MCIX.PREFERENCE_NUMBER = RANK.RANK AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID AND MCI.INACTIVE_FLAG = 'N' AND MCIX.INACTIVE_FLAG = 'N' GROUP BY mci.customer_id, MCIX.INVENTORY_ITEM_ID, MCI.CUSTOMER_ITEM_NUMBER, MCIX.PREFERENCE_NUMBER, MCI.CUSTOMER_ITEM_DESC) CUST, WMS_LABEL_REQUESTS WLR where 1=1 and wlr.inventory_item_id = cust.inventory_item_id(+) ) s), VAR_QTY_BC as (SELECT sq.sq1,sq.label_request_id FROM ( SELECT wlr.label_request_id, to_char(nvl('Q'||wdd.shipped_quantity, '0')) sq1 FROM wms_label_requests wlr, wsh_delivery_details wdd WHERE wlr.DELIVERY_DETAIL_ID = wdd.delivery_detail_id ) sq, wms_label_requests wlr) SELECT VPNB.AT VAR_PO_NUMBER_BC, VPLB.AT VAR_PO_LINE_BC, VCPB.val VAR_CUST_PART_BC, VQB.sq1 VAR_QTY_BC, (select sys_date from (select '9D'||to_char(sysdate,'YYIW')sys_date from dual)) VAR_DC_BC, '4LCN' VAR_COO FROM WMS_LABEL_REQUESTS WLR JOIN VAR_PO_NUMBER_BC VPNB ON WLR.LABEL_REQUEST_ID = VPNB.LABEL_REQUEST_ID JOIN VAR_PO_LINE_BC VPLB ON WLR.LABEL_REQUEST_ID = VPLB.LABEL_REQUEST_ID JOIN VAR_CUST_PART_BC VCPB ON WLR.LABEL_REQUEST_ID = VCPB.LABEL_REQUEST_ID JOIN VAR_QTY_BC VQB ON WLR.LABEL_REQUEST_ID = VQB.LABEL_REQUEST_ID WHERE WLR.LABEL_REQUEST_ID = :WLR_LABEL_REQUEST_ID ;
Editor is loading...
Leave a Comment