Sales Order Queries

Query to retrieve the header information of the Sales Order form

SELECT
        OOHA.HEADER_ID, OOHA.ORDER_NUMBER, OTT.NAME "ORDER TYPE",
        HP.PARTY_NAME "CUSTOMER", HCA.ACCOUNT_NUMBER "CUSTOMER NUMBER",
        OOHA.ORDERED_DATE "DATE ORDERED", QH.NAME "PRICE LIST",
        OOHA.TRANSACTIONAL_CURR_CODE "CURRENCY",
        OOHA.CUST_PO_NUMBER "CUSTOMER PO",
        OOHA.FREIGHT_CARRIER_CODE "SHIPPING METHOD",
        OOHA.FLOW_STATUS_CODE "STATUS", RTT.NAME "PAYMENT TERMS",
        MP.ORGANIZATION_CODE "WARE HOUSE", OL.MEANING "FREIGHT TERMS",
        OL1.MEANING "SHIPMENT PRIORITY", AL.MEANING "FOB",
        RSA.NAME "SALESPERSON",
        HCSUA.LOCATION
        ','
        HL.ADDRESS2
        ','
        HL.CITY
        ','
        HL.STATE
        ','
        HL.POSTAL_CODE
        ','
        HL.COUNTY "BILL TO LOCATION",
        HCSUA1.LOCATION
        ','
        HL1.ADDRESS2
        ','
        HL1.CITY
        ','
        HL1.STATE
        ','
        HL1.POSTAL_CODE
        ','
        HL1.COUNTY "SHIP TO LOCATION"
FROM
        OE_ORDER_HEADERS_ALL OOHA,
        OE_TRANSACTION_TYPES_TL OTT,
        QP_LIST_HEADERS QH,
        RA_TERMS_TL RTT,
        MTL_PARAMETERS MP,
        RA_SALESREPS_ALL RSA,
        HZ_CUST_ACCOUNTS HCA,
        HZ_PARTIES HP,
        HZ_PARTIES HP1,
        HZ_LOCATIONS HL,
        HZ_LOCATIONS HL1,
        HZ_CUST_ACCT_SITES_ALL HCASA,
        HZ_CUST_ACCT_SITES_ALL HCASA1,
        HZ_CUST_SITE_USES_ALL HCSUA,
        HZ_CUST_SITE_USES_ALL HCSUA1,
        HZ_PARTY_SITES HPS,
        HZ_PARTY_SITES HPS1,
        OE_LOOKUPS OL,
        OE_LOOKUPS OL1,
        AR_LOOKUPS AL
WHERE
        1 = 1
        AND OOHA.ORDER_NUMBER = 10265
        AND OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
        AND OOHA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
        AND OTT.LANGUAGE = USERENV ('LANG')
        AND RTT.LANGUAGE = USERENV ('LANG')
        AND RTT.TERM_ID = OOHA.PAYMENT_TERM_ID
        AND QH.LIST_HEADER_ID = OOHA.PRICE_LIST_ID
        AND MP.ORGANIZATION_ID = OOHA.SHIP_FROM_ORG_ID
        AND OOHA.SALESREP_ID = RSA.SALESREP_ID
        AND HCA.PARTY_ID = HP.PARTY_ID
        AND HCA.PARTY_ID = HP1.PARTY_ID
        AND OOHA.INVOICE_TO_ORG_ID = HCSUA.SITE_USE_ID(+)
        AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID(+)
        AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
        AND HL.LOCATION_ID(+) = HPS.LOCATION_ID
        AND OOHA.SHIP_TO_ORG_ID = HCSUA1.SITE_USE_ID(+)
        AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID(+)
        AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID(+)
        AND HL1.LOCATION_ID(+) = HPS1.LOCATION_ID
        AND OOHA.FREIGHT_TERMS_CODE = OL.LOOKUP_CODE
        AND OOHA.SHIPMENT_PRIORITY_CODE = OL1.LOOKUP_CODE
        AND AL.LOOKUP_CODE = OOHA.FOB_POINT_CODE;

Query to retrieve the line information of the Sales Order form

SELECT
        OOLA.LINE_NUMBER "LINE NUMBER", OOLA.ORDERED_ITEM "ORDERED ITEM",
        OOLA.ORDERED_QUANTITY "QTY", OOLA.ORDER_QUANTITY_UOM "UOM",
        OOLA.UNIT_SELLING_PRICE "UNIT SELLING PRICE",
        OOLA.CANCELLED_QUANTITY "QTY CANCELLED",
        OOLA.SHIPPED_QUANTITY "QTY SHIPPED", OOLA.TAX_CODE "TAX CODE",
        OTT.NAME "LINE TYPE",
        DECODE (OPA.LINE_ID,
                    NULL,
                    DECODE (OPA.CREDIT_OR_CHARGE_FLAG,
                               'C', (-1) * OPA.OPERAND,
                               OPA.OPERAND
                                ),
                    DECODE (OPA.CREDIT_OR_CHARGE_FLAG,
                                 'C', DECODE (OPA.ARITHMETIC_OPERATOR,
                                                    'LUMPSUM',
                                                     (-1) * (OPA.OPERAND),
                                                     (-1)* ( OOLA.ORDERED_QUANTITY* OPA.ADJUSTED_AMOUNT)
                                ),
                   DECODE (OPA.ARITHMETIC_OPERATOR,
                                'LUMPSUM',
                                OPA.OPERAND,
                                (OOLA.ORDERED_QUANTITY * OPA.ADJUSTED_AMOUNT)
                                )
                         )
                 ) "LINE_CHARGES",
        OL.MEANING "CALCULATE PRICE FLAG", OOLA.PRICING_QUANTITY,
        OOLA.UNIT_SELLING_PRICE, OOLA.UNIT_LIST_PRICE, OOLA.TAX_VALUE,
        (OOLA.SHIPPED_QUANTITY) * (OOLA.UNIT_SELLING_PRICE) "LINE TOTAL"
        ((OOLA.SHIPPED_QUANTITY) * (OOLA.UNIT_SELLING_PRICE)
        )
        + (DECODE (OPA.LINE_ID,
        NULL, DECODE (OPA.CREDIT_OR_CHARGE_FLAG,
        'C', (-1) * OPA.OPERAND,
        OPA.OPERAND
        ),
        DECODE (OPA.CREDIT_OR_CHARGE_FLAG,
        'C', DECODE (OPA.ARITHMETIC_OPERATOR,
        'LUMPSUM', (-1) * (OPA.OPERAND),
        (-1)
        * ( OOLA.ORDERED_QUANTITY
        * OPA.ADJUSTED_AMOUNT
        )
        ),
        DECODE (OPA.ARITHMETIC_OPERATOR,
        'LUMPSUM', OPA.OPERAND,
        (OOLA.ORDERED_QUANTITY * OPA.ADJUSTED_AMOUNT
        )
        )
        )
        )
        ) "ORDER TOTAL"
FROM
        OE_ORDER_LINES_ALL OOLA,
        OE_TRANSACTION_TYPES_TL OTT,
        OE_PRICE_ADJUSTMENTS OPA,
        OE_ORDER_HEADERS_ALL OOHA,
        OE_LOOKUPS OL
WHERE
        1 = 1
        AND OOLA.LINE_TYPE_ID = OTT.TRANSACTION_TYPE_ID
        AND OPA.HEADER_ID = OOHA.HEADER_ID
        AND OPA.LINE_ID = OOLA.LINE_ID(+)
        AND OPA.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
        AND OPA.APPLIED_FLAG = 'Y'
        AND OTT.LANGUAGE = USERENV ('LANG')
        AND OOLA.HEADER_ID = 1547
        AND OL.LOOKUP_TYPE = 'CALCULATE_PRICE_FLAG'
        AND OOLA.CALCULATE_PRICE_FLAG = OL.LOOKUP_CODE;