CHECK LIST REPORT

/* Formatted on 12/27/2012 4:56:20 PM (QP5 v5.115.810.9015) */
SELECT   ROWNUM s_no,
         product_code,
         CUST_PO_NUMBER,
         order_number,
         Ship_to_address,
         Bill_to_Address,
         variety,
         DESCRIPTION,
         MC_NO,
         GSM,
         width,
         LENGTH,
         QTY,
         list_price,
         total_discount,
         total_charges,
         (net_price - total_discount) + NVL (total_charges, 0) net_price,
         dealer_name,
         ACCOUNT_NAME,
         ORDER_TYPE,
         PAYMENT_TERM,
         STATUS,
         price_list_name,
         country_or_final_destination,
         currency,
         commission,
         insurance,
         port_of_loading,
         port_of_discharge,
         SHIPPING_METHOD_CODE,
         FREIGHT_TERMS_CODE,
         CREATION_DATE,
         lc_no,
         lc_ref_no,
         LAST_NEGOTIATION_DATE,
         lc_BANK_NAME_BANK_ADDRESS,
         ISS_BRANCH_NAME,
         ISS_BANK_ADDRESS,
         LAST_SHIP_DATE,
         END_DATE,
         amount
  FROM   (  SELECT   DISTINCT
                     oola.ORDERED_ITEM product_code,
                     ooha.CUST_PO_NUMBER,
                     ooha.order_number,
                     (SELECT      hl.ADDRESS1
                               || ' '
                               || hl.ADDRESS2
                               || '  '
                               || hl.ADDRESS3
                               || ' '
                               || hl.CITY
                               || ' '
                               || hl.STATE
                               || ' '
                               || hl.POSTAL_CODE
                                  bill_to
                        FROM   APPS.oe_order_headers_all ooh,
                               APPS.hz_cust_site_uses_all hcsua,
                               APPS.hz_cust_acct_sites_all hcasa,
                               APPS.hz_party_sites hps,
                               APPS.hz_locations hl
                       WHERE   ooh.order_number = ooha.ORDER_NUMBER
                               AND ooh.INVOICE_TO_ORG_ID = hcsua.SITE_USE_ID
                               AND hcasa.CUST_ACCT_SITE_ID =
                                     hcsua.CUST_ACCT_SITE_ID
                               AND hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
                               AND hcsua.SITE_USE_CODE = 'BILL_TO'
                               AND hl.LOCATION_ID = hps.LOCATION_ID)
                        Bill_to_Address,
                     (SELECT      hl.ADDRESS1
                               || ' '
                               || hl.ADDRESS2
                               || '  '
                               || hl.ADDRESS3
                               || ' '
                               || hl.CITY
                               || ' '
                               || hl.STATE
                               || ' '
                               || hl.POSTAL_CODE
                                  bill_to
                        FROM   oe_order_headers_all ooh,
                               APPS.hz_cust_site_uses_all hcsua,
                               APPS.hz_cust_acct_sites_all hcasa,
                               APPS.hz_party_sites hps,
                               APPS.hz_locations hl
                       WHERE   ooh.order_number = ooha.ORDER_NUMBER
                               AND ooh.SHIP_TO_ORG_ID = hcsua.SITE_USE_ID
                               AND hcasa.CUST_ACCT_SITE_ID =
                                     hcsua.CUST_ACCT_SITE_ID
                               AND hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
                               AND hcsua.SITE_USE_CODE = 'SHIP_TO'
                               AND hl.LOCATION_ID = hps.LOCATION_ID)
                        Ship_to_address,
                     SUBSTR (oola.ORDERED_ITEM, 4, 3) variety,
                     msi.description DESCRIPTION,
                     SUBSTR (oola.ORDERED_ITEM, 2, 1) MC_NO,
                     SUBSTR (oola.ORDERED_ITEM, 8, 4) GSM,
                     SUBSTR (oola.ORDERED_ITEM, 12, 4) width,
                     SUBSTR (oola.ORDERED_ITEM, 16, 4) LENGTH,
                     oola.ORDERED_QUANTITY QTY,
                     oola.UNIT_SELLING_PRICE list_price,
                     (SELECT   SUM (OPERAND)
                        FROM   apps.OE_PRICE_ADJUSTMENTS
                       WHERE   line_id = oola.line_id
                               AND LIST_LINE_TYPE_CODE = 'DIS')
                        total_discount,
                     (SELECT   SUM (OPERAND)
                        FROM   apps.OE_PRICE_ADJUSTMENTS
                       WHERE   line_id = oola.line_id
                               AND LIST_LINE_TYPE_CODE = 'SUR')
                        total_charges,
                     (oola.UNIT_SELLING_PRICE) net_price,
                     hp.PARTY_NAME dealer_name,
                     ACCOUNT_NAME,
                     OTTT.NAME ORDER_TYPE,
                     RT.NAME PAYMENT_TERM,
                     OOHA.FLOW_STATUS_CODE STATUS,
                     oplv.name price_list_name,
                     ooha.ATTRIBUTE10 country_or_final_destination,
                     ooha.TRANSACTIONAL_CURR_CODE currency,
                     ooha.ATTRIBUTE6 commission,
                     CASE
                        WHEN opav.CHARGE_TYPE_CODE = 'INSURANCE' THEN 'S'
                        ELSE 'N'
                     END
                        AS insurance,
                     ooha.ATTRIBUTE8 port_of_loading,
                     ooha.ATTRIBUTE9 port_of_discharge,
                     ooha.SHIPPING_METHOD_CODE,
                     ooha.FREIGHT_TERMS_CODE,
                     OOHA.CREATION_DATE,
                     jolh.LC_NUMBER lc_no,
                     jolh.REFERENCE_NUMBER lc_ref_no,
                     jolh.LAST_NEGOTIATION_DATE,
                     jolh.ISS_BANK_NAME || jolh.ISS_BANK_ADDRESS
                        lc_BANK_NAME_BANK_ADDRESS,
                     jolh.ISS_BRANCH_NAME,
                     jolh.ISS_BANK_ADDRESS,
                     jolh.LAST_SHIP_DATE,
                     jolh.END_DATE,
                     jolh.LC_ORIGINAL_AMOUNT amount
              FROM   apps.oe_order_headers_all ooha,
                     apps.oe_order_lines_all oola,
                     apps.OE_PRICE_LISTS_V oplv,
                     apps.OE_PRICE_ADJUSTMENTS opav,
                     apps.hz_parties hp,
                     apps.hz_cust_accounts hca,
                     --                     apps.hz_cust_site_uses_all hcsua,
                     --                     apps.hz_cust_acct_sites_all hcasa,
                     --                     apps.hz_party_sites hps,
                     --                     apps.hz_locations hl,
                     apps.OE_TRANSACTION_TYPES_TL OTTT,
                     APPS.RA_TERMS RT,
                     apps.jai_om_lc_matchings jolm,
                     apps.jai_om_lc_hdrs jolh,
                     APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
                     APPS.mtl_system_items_b msi,
                     apps.fnd_lookup_values FLV
             --    apps.gl_daily_rates_v gdr
             --            apps.wsh_delivery_details wdd,
             --            apps.jai_om_wsh_lines_all jowl
             WHERE   ooha.ORDER_NUMBER BETWEEN NVL (:SALES_ORDER_NO_FORM,
                                                    ooha.ORDER_NUMBER)
                                           AND  NVL (:SALES_ORDER_NO_TO,
                                                     ooha.ORDER_NUMBER)
                     AND TRUNC (OOHA.CREATION_DATE) BETWEEN :FROM_DATE
                                                        AND  :TO_DATE
                     AND ooha.HEADER_ID = oola.HEADER_ID
                     AND oola.PRICE_LIST_ID = oplv.PRICE_LIST_ID
                     AND oola.line_id = opav.line_id
                     AND oola.header_id = opav.header_id
                     AND opav.CHARGE_TYPE_CODE IS NULL
                     --   AND oola.flow_status_code <> 'CANCELLED'
                     AND hp.PARTY_ID = ooha.ATTRIBUTE1
                     AND hca.CUST_ACCOUNT_ID = ooha.SOLD_TO_ORG_ID
                     AND OTTT.TRANSACTION_TYPE_ID = OOHA.ORDER_TYPE_ID
                     AND RT.TERM_ID = OOHA.PAYMENT_TERM_ID
                     AND OOHA.SHIP_FROM_ORG_ID = OOD.ORGANIZATION_ID
                     AND OOD.ORGANIZATION_ID = :ORGANIZATION
                     AND jolm.LC_NUMBER = jolh.LC_NUMBER
                     AND jolm.CUSTOMER_ID = jolh.CUSTOMER_ID
                     AND jolm.ORDER_HEADER_ID = ooha.HEADER_ID
                     AND jolm.ORDER_LINE_ID = oola.LINE_ID
                     AND msi.inventory_item_id = oola.inventory_item_id
                     AND msi.organization_id = oola.ship_from_org_id
                     AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
                     AND FLV.LOOKUP_CODE = UPPER (OTTT.NAME)
                     AND flv.TAG = 'Export'
          GROUP BY   ROWNUM,
                     ooha.ORDER_NUMBER,
                     oola.ORDERED_ITEM,
                     oola.ORDERED_QUANTITY,
                     oplv.name,
                     oola.UNIT_SELLING_PRICE,
                     opav.ADJUSTED_AMOUNT,
                     hp.PARTY_NAME,
                     ACCOUNT_NAME,
                     OTTT.NAME,
                     RT.NAME,
                     OOHA.FLOW_STATUS_CODE,
                     oplv.name,
                     ooha.ATTRIBUTE10,
                     ooha.TRANSACTIONAL_CURR_CODE,
                     ooha.ATTRIBUTE6,
                     opav.CHARGE_TYPE_CODE,
                     ooha.ATTRIBUTE8,
                     ooha.ATTRIBUTE9,
                     ooha.SHIPPING_METHOD_CODE,
                     ooha.FREIGHT_TERMS_CODE,
                     jolh.LC_NUMBER,
                     jolh.REFERENCE_NUMBER,
                     jolh.LAST_NEGOTIATION_DATE,
                     jolh.ISS_BANK_NAME,
                     jolh.ISS_BRANCH_NAME,
                     jolh.ISS_BANK_ADDRESS,
                     jolh.LAST_SHIP_DATE,
                     jolh.END_DATE,
                     ooha.header_id,
                     OOHA.CREATION_DATE,
                     oola.USER_ITEM_DESCRIPTION,
                     ooha.order_number,
                     jolh.LC_ORIGINAL_AMOUNT,
                     oola.header_id,
                     oola.line_id,
                     oola.UNIT_LIST_PRICE,
                     ooha.CUST_PO_NUMBER,
                     msi.description)