Sales Order Report

Sales Order Report

SELECT HP.PARTY_NAME,

       OOH.ORDER_NUMBER,

       OTT.NAME,

       OOL.LINE_NUMBER,

       MSI.SEGMENT1 ITEM,

       MSI.DESCRIPTION,

       OOL.ORDERED_QUANTITY,

       OOL.UNIT_LIST_PRICE,       

       (OOL.ORDERED_QUANTITY * OOL.UNIT_LIST_PRICE) SALE_PRICE

  FROM OE_ORDER_HEADERS_ALL OOH,

       OE_ORDER_LINES_ALL OOL,

       OE_TRANSACTION_TYPES_TL OTT ,

       HZ_PARTIES HP,

       HZ_CUST_ACCOUNTS HCA ,

       MTL_SYSTEM_ITEMS_B MSI

 WHERE OOH.HEADER_ID=OOL.HEADER_ID

   AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_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 MSI.ORGANIZATION_ID=NVL(:P_ORG_ID,MSI.ORGANIZATION_ID)

   AND OTT.NAME=NVL(:P_TYPE,OTT.NAME)

   ORDER BY 4