Shipping Extraction Report

Shipping Extraction Report 

SELECT OOH.ORDER_NUMBER,

       HCA.ACCOUNT_NUMBER PARTY_NUMBER,

       HP.PARTY_NAME,

       OOL.LINE_NUMBER,

       MSI.SEGMENT1 ITEM,

       OOL.ORDERED_QUANTITY,

       OOL.FLOW_STATUS_CODE,

       OOL.REQUEST_DATE,

       (OOL.UNIT_LIST_PRICE * OOL.ORDERED_QUANTITY) TOTAL_SELLING_PRICE,

       MSI.UNIT_VOLUME,

       MSI.UNIT_WEIGHT,

       (MSI.UNIT_VOLUME * OOL.ORDERED_QUANTITY) TOTAL_VOLUME,

       (MSI.UNIT_WEIGHT * OOL.ORDERED_QUANTITY) TOTAL_WEIGHT

  FROM OE_ORDER_HEADERS_ALL OOH,

       OE_ORDER_LINES_ALL OOL,

       HZ_CUST_ACCOUNTS HCA,

       HZ_PARTIES HP,

       MTL_SYSTEM_ITEMS_B MSI

 WHERE OOH.HEADER_ID=OOL.HEADER_ID

   AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID

   AND HCA.PARTY_ID=HP.PARTY_ID

   AND MSI.INVENTORY_ITEM_ID=OOL.INVENTORY_ITEM_ID

   AND MSI.ORGANIZATION_ID = OOH.ORG_ID

   AND HP.PARTY_NAME=NVL(:P_PARTY_NAME,HP.PARTY_NAME)

   AND HCA.ACCOUNT_NUMBER=NVL(:P_ACC_NUM,HCA.ACCOUNT_NUMBER)

   AND OOL.FLOW_STATUS_CODE= NVL(:P_STATUS,OOH.FLOW_STATUS_CODE)

   ORDER BY 1,4