Untitled

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
;