Friday 22 May 2015

Query to get Customer Order Details


select distinct hz.party_name        Customer_Name,
       hca.account_number   Customer_Number,
       ship_su.location     Ship_to_Location,
       ooh.cust_po_number   Customer_PO,
       ot.name              Order_Type,
       qp.name              Price_List,
       ooh.sales_channel_code,
       ship_loc.address1    ship_to_address1,
       ship_loc.address2    ship_to_address2,
       ship_loc.address3    ship_to_address3,
       ship_loc.address4    ship_to_address4,
        DECODE (ship_loc.city,
                     NULL, NULL,
                     ship_loc.city || ', '
                    )
          || DECODE (ship_loc.state,
                     NULL, ship_loc.province || ', ',
                     ship_loc.state || ', '
                    )
          || DECODE (ship_loc.postal_code,
                     NULL, NULL,
                     ship_loc.postal_code || ', '
                    )
          || DECODE (ship_loc.country, NULL, NULL, ship_loc.country) ship_to_address5,
      bill_su.location     Bill_to_Location    ,
       bill_loc.address1     invoice_to_address1,
          bill_loc.address2  invoice_to_address2,
          bill_loc.address3  invoice_to_address3,
          bill_loc.address4  invoice_to_address4,
             DECODE (bill_loc.city,
                     NULL, NULL,
                     bill_loc.city || ', '
                    )
          || DECODE (bill_loc.state,
                     NULL, bill_loc.province || ', ',
                     bill_loc.state || ', '
                    )
          || DECODE (bill_loc.postal_code,
                     NULL, NULL,
                     bill_loc.postal_code || ', '
                    )
          || DECODE (bill_loc.country, NULL, NULL, bill_loc.country)
                                                          invoice_to_address5              
from hz_cust_acct_sites_all  hcasa,
     hz_cust_site_uses_all   bill_su,
     hz_cust_site_uses_all   ship_su,
     hz_cust_accounts        hca,
     hz_party_sites          hps,
     hz_parties              hz,
     hz_locations            ship_loc,
     hz_locations            bill_loc,
     oe_order_headers_all    ooh,
     oe_transaction_types_tl ot,
     qp_list_headers_tl      qp          
where hz.party_id           = hca.party_id
and ooh.order_type_id       = ot.transaction_type_id
and ooh.price_list_id       = qp.list_header_id(+)  
and hca.cust_account_id     = hcasa.cust_account_id  
and hcasa.cust_acct_site_id = ship_su.cust_acct_site_id
and hz.party_id             = hps.party_id
and hps.location_id         = ship_loc.location_id
and hps.location_id         = bill_loc.location_id
and ooh.invoice_to_org_id   = bill_su.site_use_id(+)
and ooh.ship_to_org_id      =  ship_su.site_use_id(+)
and ooh.sold_to_org_id      = hca.cust_account_id(+)
and ship_su.SITE_USE_CODE   ='SHIP_TO'
and bill_su.SITE_USE_CODE   ='BILL_TO'
and hz.status               = 'A'
and hca.status              = 'A'
and hcasa.status            = 'A'
and ooh.order_number        = :p_order_number