Sales Order SQL

Query to get Sales Order data
========================


select distinct
ooh.CREATION_DATE ,
Ooh.Order_number  "Sales Order Number",
VERSION_NUMBER  "Sales Order Revision Number  ",
to_char(ORDERED_DATE,'DD-MM-RRRR')  "Entered Date",
(select ORDER_CATEGORY_CODE
from apps.oe_transaction_types_all
where transaction_type_id = ooh.ORDER_TYPE_ID
)  "Order Type",ooh.ORDER_TYPE_ID,
Ooh.Quote_number  "Quote Number",
ooh.TAX_EXEMPT_FLAG  "Order Taxable  ",
(select name from
apps.ra_terms
where Term_id = ooh.PAYMENT_TERM_ID)  "Payment Terms",
ooh.TAX_EXEMPT_NUMBER  "Tax Exempt Id Number",
(select p.FIRST_NAME
 from apps.fnd_user f,
      apps.Per_all_people_f p
where f.EMPLOYEE_ID = p.PERSON_ID
  and f.user_id = ooh.LAST_UPDATED_BY
)  "Last Maintenance - First Name",
(select p.LAST_NAME
 from apps.fnd_user f,
      apps.Per_all_people_f p
where f.EMPLOYEE_ID = p.PERSON_ID
  and f.user_id = ooh.LAST_UPDATED_BY
)  "Last Maintenance",
ooh.LAST_UPDATE_DATE  "Last Maintenance Date",
ool.ORDERED_ITEM  "Order Line Item Number",
ool.ORDERED_QUANTITY  "Total Order Line Item Quantity",
ool.UNIT_SELLING_PRICE  "Order Line Item Unit Price",
Ooh.Transactional_curr_code  "Unit Price Currency Code",
nvl(Ool.Unit_cost,(select ITEM_COST
   from cst_item_costs
  where INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
    and ORGANIZATION_ID = msi.ORGANIZATION_ID
    and COST_TYPE_ID =1) )  "Standard Cost Per Unit",
    (SELECT   Gll.Currency_code
    FROM   Gl_ledgers Gll, Hr_operating_units Hou
    WHERE  Hou.Set_of_books_id = Gll.Ledger_id
       AND Hou.Organization_id = Ooh.Org_id
       AND ROWNUM = 1)   "Standard Cost Currency Code",
ool.ORDER_QUANTITY_UOM  "Order Line UOM",
/*DECODE ( Ool.Unit_list_price,
            0,
            0,
            ROUND ((((Ool.Unit_list_price - Ool.Unit_selling_price)
                  / Ool.Unit_list_price) * 100), 2))  */
Ool.Cust_po_number  "Customer PO",
ool.CUSTOMER_LINE_NUMBER  "Customer PO Line Item Number",
to_char(ool.SCHEDULE_SHIP_DATE,'DD-MM-RRRR')  "Promise Date ",
to_char(ool.PROMISE_DATE,'DD-MM-RRRR')  "Line Item Promise Date ",
to_char(ooh.REQUEST_DATE,'DD-MM-RRRR')  "Customer Required Date ",
decode(SHIPPED_QUANTITY, null,ool.ORDERED_QUANTITY,
            (ool.ORDERED_QUANTITY-SHIPPED_QUANTITY))  "Scheduled Quantity",
SHIPPED_QUANTITY  "Shipped Quantity",
Msi.Segment1  "Part Number",
Msi.Description  "Part Description",
(SELECT   Customer_item_number
            FROM   Mtl_customer_item_xrefs_v
           WHERE   Customer_id = Sold_acct.Cust_account_id
               AND Inventory_item_id = Ool.Inventory_item_id
               AND ROWNUM = 1)   "Customer Part Number",
--msi.attribute2,MSI.ORGANIZATION_ID,
(SELECT distinct ffvt.description
FROM fnd_descr_flex_col_usage_vl fdfu,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
WHERE fdfu.descriptive_flexfield_name = UPPER ('MTL_SYSTEM_ITEMS')
AND fdfu.application_column_name = UPPER ('ATTRIBUTE2')
AND fdfu.descriptive_flex_context_code = 123
AND fdfu.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
and PARENT_FLEX_VALUE_LOW = msi.attribute1
AND ffvt.language = 'US'
and ffv.flex_value = nvl (msi.ATTRIBUTE2,
                         (select attribute2
                            from mtl_system_items_b
                           where organization_id = 123
                             and inventory_item_id = msi.inventory_item_id
                             ))
                             )  "Product ",
Sold_acct.Account_number  "Sold-to Customer Number",
NVL (Sold_acct.Account_name,bill_party.party_name)  "Sold-to Company Name",
Bill_loc.Address1  "Sold-to Address Line 1",
Bill_loc.Address2  "Sold-to Address Line 2",
Bill_loc.Address3  "Sold-to Address Line 3",
Bill_loc.Address4  "Sold-to Address Line 4",
Bill_loc.City  "Sold-to City",
NVL (Bill_loc.State, Bill_loc.Province)  "Sold-to State/Province",
Bill_loc.Country  "Sold-to Country  ",
Bill_loc.Postal_code  "Sold-to Postal Code",
NULL  "Sold-To Customer Type",
NULL  "Sold-to Contact First Name",
NULL  "Sold-to Contact Last Name",
NULL  "Sold-to Contact Email Address",
NULL  "Sold-to Contact Phone Number",
------------ship to -------------------------
Ship_acct.Account_number  "Ship-To Customer Number",
nvl (Ship_acct.Account_name,ship_party.party_name)  "Ship-to Company Name",
Ship_loc.Address1  "Ship-to Address Line 1",
Ship_loc.Address2  "Ship-to Address Line 2",
Ship_loc.Address3  "Ship-to Address Line 3",
Ship_loc.Address4  "Ship-to Address Line 4   ",
Ship_loc.City  "Ship-to City",
NVL (Ship_loc.State, Ship_loc.Province)  "Ship-to State/Province",
Ship_loc.Country  "Ship-to Country",
Ship_loc.Postal_code  "Ship-to Postal Code",
------------Bill to -------------------------
Bill_acct.Account_number  "Bill-To Customer Number",
nvl(Bill_acct.Account_name,bill_party.party_name)  "Bill-to Company Name",
Bill_loc.Address1  "Bill-to Address Line 1",
Bill_loc.Address2  "Bill-to Address Line 2",
Bill_loc.Address3  "Bill-to Address Line 3",
Bill_loc.Address4  "Bill-to Address Line 4",
Bill_loc.City  "Bill-to City",
NVL (Bill_loc.State, Bill_loc.Province)  "Bill-to State/Province",
Bill_loc.Country  "Bill-to Country",
Bill_loc.Postal_code  "Bill-to Postal Code",
--==modify as per requirement
NULL  "Third Party Account Type",
NULL  "Third Party Account Number",
NULL  "Third Party Company Name",
NULL  "Third Party Address Line 1",
NULL  "Third Party Address Line 2",
NULL  "Third Party Address Line 3",
NULL  "Third Party Address Line 4",
NULL  "Third Party City",
NULL  "Third Party State/Province",
NULL  "Third Party Country",
NULL  "Third Party Postal Code",
NULL  "End Use Customer Number",
NULL  "End Use Company Name",
NULL  "End Use Address Line 1",
NULL  "End Use Address Line 2",
NULL  "End Use Address Line 3",
NULL  "End Use Address Line 4",
NULL  "End Use City",
NULL  "End Use State/Province",
NULL  "End Use Country ",
NULL  "End Use Postal Code",
--==
'ORACLE ERP'  "Source Loc ERP System",
NULL  "Selling Loc Account Number",
Loc.name  "Selling Loc Company Name",
Loc.Address_line_1  "Selling Loc Address Line 1",
Loc.Address_line_2  "Selling Loc Address Line 2",
Loc.Address_line_3  "Selling Loc Address Line 3",
Loc.Town_or_city  "Selling Loc City",
Loc.Region_2  "Selling Loc State/Province",
Loc.Country  "Selling Loc Country  ",
Loc.Postal_code  "Selling Loc Postal Code"
from  apps.oe_order_headers_all ooh,
      apps.Oe_order_lines_all Ool,
      apps.Mtl_system_items Msi,
 (SELECT   Haou.Organization_id,
           Haou.name,
           Hrl.Address_line_1,
           Hrl.Address_line_2,
           Hrl.Address_line_3,
           Hrl.Town_or_city,
           Hrl.Region_2,
           Hrl.Postal_code,
           Hrl.Country,
           Telephone_number_1
 FROM   Hr_all_organization_units Haou, Hr_locations Hrl
WHERE   Hrl.Location_id = Haou.Location_id) Loc,
  ----------------sold to------------       
         Hz_cust_accounts_all Sold_acct,
         --Hz_cust_acct_sites_all Sold_acct_site,
         --Hz_cust_site_uses_all Sold_site_use,
         --Hz_party_sites Sold_site,
         --Hz_locations Sold_loc,
  ------------ship to -------------------------         
         Hz_cust_accounts_all Ship_acct,
         Hz_cust_acct_sites_all Ship_acct_site,
         Hz_cust_site_uses_all Ship_site_use,
         Hz_party_sites Ship_site,
         Hz_parties ship_party,
         Hz_locations Ship_loc,
  ------------Bill to -------------------------       
         Hz_cust_accounts_all Bill_acct,
         Hz_cust_acct_sites_all Bill_acct_site,
         Hz_cust_site_uses_all Bill_site_use,
         Hz_party_sites Bill_site,
         Hz_parties bill_party,
         Hz_locations Bill_loc                       
WHERE Ooh.Header_id = Ool.Header_id
  AND Ool.Inventory_item_id = Msi.Inventory_item_id(+)
  AND Ool.Ship_from_org_id = Msi.Organization_id(+)
  AND Ool.Ship_from_org_id = Loc.Organization_id(+)
  AND Ool.Flow_status_code != 'CANCELLED'
  and ooh.ORDER_TYPE_ID = ''
   ----------------sold to------------
   AND Ool.Sold_to_org_id = Sold_acct.cust_account_id
 --AND Sold_acct.Cust_account_id = Sold_acct_site.Cust_account_id
 --AND Ool.Sold_to_org_id = Sold_site_use.Site_use_id(+)
 --AND Sold_site_use.Cust_acct_site_id = Sold_acct_site.Cust_acct_site_id
 --AND Sold_acct_site.Party_site_id = Sold_site.Party_site_id
 --AND Sold_loc.Location_id = Sold_site.Location_id     
------------ship to -------------------------
 AND Ool.Ship_to_org_id = Ship_site_use.Site_use_id
 AND Ship_site_use.Cust_acct_site_id =
       Ship_acct_site.Cust_acct_site_id
 AND Ship_acct_site.Party_site_id = Ship_site.Party_site_id
 AND Ship_loc.Location_id = Ship_site.Location_id
 AND Ship_acct_site.Cust_account_id = Ship_acct.Cust_account_id
 and Ship_site.party_id = ship_party.party_id
 ------------Bill to -------------------------
 AND Ool.Invoice_to_org_id = Bill_site_use.Site_use_id
 AND Bill_site_use.Cust_acct_site_id =
       Bill_acct_site.Cust_acct_site_id
 AND Bill_acct_site.Party_site_id = Bill_site.Party_site_id
 AND Bill_loc.Location_id = Bill_site.Location_id
 AND Bill_acct_site.Cust_account_id = Bill_acct.Cust_account_id
 and bill_site.party_id = bill_party.party_id
order by 1 desc