Customer Invoices SQL
Query to get Customer Invoice data
===========================
SELECT distinct
rct.CREATION_DATE,
Rct.Trx_number "Invoice Number",
Rctl.Line_number "Invoice Line Number",
Rct.Trx_date "Invoice Date",
rctl.SALES_ORDER "Sales Order Number",
--rctl.INTERFACE_LINE_ATTRIBUTE3 so_line_id,rctl.inventory_item_id,rctl.warehouse_id,
rctl.SALES_ORDER_DATE "Sales Order Date",
rct.SHIP_DATE_ACTUAL "Ship Date",
Ool.Request_date "Customer Required Date",
Ool.Promise_date "Promise Date",
rct.PURCHASE_ORDER "Customer Purchase Order Number",
rctl.QUANTITY_INVOICED "Quantity Sold",
rctl.UOM_CODE "Quantity Unit of Measure",
rctl.UNIT_SELLING_PRICE "Unit Price",
DECODE (
Ool.Unit_list_price,
0,
0,
ROUND (
( ( (Ool.Unit_list_price - Ool.Unit_selling_price)
/ Ool.Unit_list_price)
* 100),
2
)
) "Discount Percent",
nvl((rctl.Unit_selling_price * rctl.QUANTITY_INVOICED),rctl.extended_amount)"Sales Amount ",
rct.INVOICE_CURRENCY_CODE "Sales Amount 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",
(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) ) * rctl.QUANTITY_INVOICED) "Extended Cost Amount",
(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 = rct.Org_id
AND ROWNUM = 1) "Standard Cost Currency Code",
(select Name from ra_terms where Term_id = rct.term_id) "Payment Terms ",
(SELECT Discount_percent
FROM Ra_terms_lines_discounts
WHERE Term_id = Rct.Term_id AND Sequence_num = 1 AND ROWNUM = 1) "Payment Discount",
nvl(INTERFACE_LINE_ATTRIBUTE2,Ool.Return_reason_code) "Reason ",
(select distinct Ooh.Quote_number
from Oe_order_headers_all Ooh,
Oe_order_lines_all Ool
where Ooh.Header_id = Ool.Header_id) "Quote Number",
(select distinct Ooh.Quote_date
from Oe_order_headers_all Ooh,
Oe_order_lines_all Ool
where Ooh.Header_id = Ool.Header_id) "Quote Date",
'N' "Proprietary Secured Data Flag",
NVL ( (SELECT Attribute1
FROM Mtl_system_items_b
WHERE Inventory_item_id = Ool.Inventory_item_id
and organization_id = rctl.warehouse_id),'N') "Compliance",
Msi.Segment1 "Part Number",
Msi.Description "Part Description",
msi.unit_length "Part Length ",
NULL "Custom Part Flag",
(SELECT Customer_item_number
FROM Mtl_customer_item_xrefs_v
WHERE Customer_id = bill_acct.Cust_account_id
AND Inventory_item_id = Ool.Inventory_item_id
AND ROWNUM = 1) "Customer Part Number",
(SELECT Mc.Concatenated_segments
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_code'
AND ROWNUM = 1) "H_Code",
(SELECT Mc.Segment1
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 1",
(SELECT Mc.Segment2
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 2",
(SELECT Mc.Segment3
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 3",
(SELECT Mc.Segment4
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 4",
(SELECT Mc.Segment5
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 5",
(SELECT Mc.Segment6
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 6",
NULL "Product Category Level 7",
(SELECT Name
FROM Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1
) "Salesperson Name",
(select ppf.email_address
FROM Per_all_people_f ppf,
Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE ppf.Person_id = Rsr.Person_id
and rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1 ) "Salesperson Email Address",
(select ppf.WORK_TELEPHONE
FROM Per_all_people_f ppf,
Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE ppf.Person_id = Rsr.Person_id
and rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1) "Salesperson Phone Number",
NULL "Salesperson Territory",
NULL "Salesperson Manager Name",
NVL (Ship_acct_site.Attribute10, Bill_acct_site.Attribute10) "Territory Region",
NULL "Sales Force Name",
------------ship to -------------------------
Ship_acct.Account_number "Ship-To Customer Number",
nvl(Ship_acct.Account_name,(select party_name from hz_parties where party_id = Ship_acct.party_id)) "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.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",
NULL "Agent Number",
------------Bill to -------------------------
Bill_acct.Account_number "Bill-To Customer Number",
nvl(Bill_acct.Account_name,(select party_name from hz_parties where party_id = Bill_acct.party_id)) "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.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",
----------------sold to------------
Sold_acct.Account_number "Sold-To Customer Number",
Sold_acct.Account_name "Sold-to Company Name",
Sold_loc.Address1 "Sold-to Address Line 1",
Sold_loc.Address2 "Sold-to Address Line 2",
Sold_loc.Address3 "Sold-to Address Line 3",
Sold_loc.City "Sold-to City",
NVL (Sold_loc.State, Sold_loc.Province) "Sold-to State/Province",
Sold_loc.Country "Sold-to Country",
Sold_loc.Postal_code "Sold-to Postal Code",
--====== modify as per requirements===
NULL "Sold-to Contact First Name",
NULL "Sold-to Contact Last Name",
NULL "Sold-to Contact Email Address",
NULL "Sold-to Contact Phone Number",
NULL "Sold-To Customer Type",
----------------end cust------------
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 City",
NULL "End Use State/Province",
NULL "End Use Country",
NULL "End Use Postal Code",
NULL "Selling Loc Account Number",
--====== modify as per requirements===
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",
'ORACLE ERP' "Source Loc ERP System ",
NULL "Ship-from Loc Account Number",
loc.name "Ship-from Loc Company Name",
Loc.Address_line_1 "Ship-from Loc Address Line 1",
Loc.Address_line_2 "Ship-from Loc Address Line 2",
Loc.Address_line_3 "Ship-from Loc Address Line 3",
Loc.Town_or_city "Ship-from Loc City",
Loc.Region_2 "Ship-from Loc State/Province",
Loc.Country "Ship-from Loc Country ",
Loc.Postal_code "Ship-from Loc Postal Code"
FROM Ra_customer_trx_all Rct,
Ra_customer_trx_lines_all Rctl,
--Oe_order_headers_all Ooh,
Oe_order_lines_all Ool,
Mtl_system_items Msi,
--Ra_terms Rt,
(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 ,
------------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_locations Bill_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_locations Ship_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
WHERE Rct.Customer_trx_id = Rctl.Customer_trx_id
--AND Rt.Term_id = rct.term_id
and rctl.INTERFACE_LINE_ATTRIBUTE6 = to_char(ool.line_id(+))
AND rctl.warehouse_id = Loc.Organization_id(+)
AND Ool.Inventory_item_id = Msi.Inventory_item_id(+)
AND Ool.Ship_from_org_id = Msi.Organization_id(+)
------------Bill to -------------------------
AND rct.BILL_TO_SITE_USE_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
------------ship to -------------------------
AND rct.SHIP_TO_SITE_USE_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
----------------sold to------------
AND nvl(rct.SOLD_TO_SITE_USE_ID,rct.SHIP_TO_SITE_USE_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
AND Sold_acct_site.Cust_account_id = Sold_acct.Cust_account_id
AND Sold_acct.Cust_account_id = rct.SOLD_TO_CUSTOMER_ID
order by 1
===========================
SELECT distinct
rct.CREATION_DATE,
Rct.Trx_number "Invoice Number",
Rctl.Line_number "Invoice Line Number",
Rct.Trx_date "Invoice Date",
rctl.SALES_ORDER "Sales Order Number",
--rctl.INTERFACE_LINE_ATTRIBUTE3 so_line_id,rctl.inventory_item_id,rctl.warehouse_id,
rctl.SALES_ORDER_DATE "Sales Order Date",
rct.SHIP_DATE_ACTUAL "Ship Date",
Ool.Request_date "Customer Required Date",
Ool.Promise_date "Promise Date",
rct.PURCHASE_ORDER "Customer Purchase Order Number",
rctl.QUANTITY_INVOICED "Quantity Sold",
rctl.UOM_CODE "Quantity Unit of Measure",
rctl.UNIT_SELLING_PRICE "Unit Price",
DECODE (
Ool.Unit_list_price,
0,
0,
ROUND (
( ( (Ool.Unit_list_price - Ool.Unit_selling_price)
/ Ool.Unit_list_price)
* 100),
2
)
) "Discount Percent",
nvl((rctl.Unit_selling_price * rctl.QUANTITY_INVOICED),rctl.extended_amount)"Sales Amount ",
rct.INVOICE_CURRENCY_CODE "Sales Amount 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",
(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) ) * rctl.QUANTITY_INVOICED) "Extended Cost Amount",
(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 = rct.Org_id
AND ROWNUM = 1) "Standard Cost Currency Code",
(select Name from ra_terms where Term_id = rct.term_id) "Payment Terms ",
(SELECT Discount_percent
FROM Ra_terms_lines_discounts
WHERE Term_id = Rct.Term_id AND Sequence_num = 1 AND ROWNUM = 1) "Payment Discount",
nvl(INTERFACE_LINE_ATTRIBUTE2,Ool.Return_reason_code) "Reason ",
(select distinct Ooh.Quote_number
from Oe_order_headers_all Ooh,
Oe_order_lines_all Ool
where Ooh.Header_id = Ool.Header_id) "Quote Number",
(select distinct Ooh.Quote_date
from Oe_order_headers_all Ooh,
Oe_order_lines_all Ool
where Ooh.Header_id = Ool.Header_id) "Quote Date",
'N' "Proprietary Secured Data Flag",
NVL ( (SELECT Attribute1
FROM Mtl_system_items_b
WHERE Inventory_item_id = Ool.Inventory_item_id
and organization_id = rctl.warehouse_id),'N') "Compliance",
Msi.Segment1 "Part Number",
Msi.Description "Part Description",
msi.unit_length "Part Length ",
NULL "Custom Part Flag",
(SELECT Customer_item_number
FROM Mtl_customer_item_xrefs_v
WHERE Customer_id = bill_acct.Cust_account_id
AND Inventory_item_id = Ool.Inventory_item_id
AND ROWNUM = 1) "Customer Part Number",
(SELECT Mc.Concatenated_segments
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_code'
AND ROWNUM = 1) "H_Code",
(SELECT Mc.Segment1
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 1",
(SELECT Mc.Segment2
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 2",
(SELECT Mc.Segment3
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 3",
(SELECT Mc.Segment4
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 4",
(SELECT Mc.Segment5
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 5",
(SELECT Mc.Segment6
FROM Mtl_item_categories Mic,
Mtl_categories_b_kfv Mc,
Mtl_category_sets_tl Mcst
WHERE 1 = 1
AND Mic.Inventory_item_id = Msi.Inventory_item_id
AND Mic.Organization_id = Msi.Organization_id
AND Mic.Category_id = Mc.Category_id
AND Mic.Category_set_id = Mcst.Category_set_id
AND Mcst.Language = USERENV ('LANG')
--AND mcst.category_set_name = 'XX_OE Category Set'
AND ROWNUM = 1) "Product Category Level 6",
NULL "Product Category Level 7",
(SELECT Name
FROM Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1
) "Salesperson Name",
(select ppf.email_address
FROM Per_all_people_f ppf,
Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE ppf.Person_id = Rsr.Person_id
and rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1 ) "Salesperson Email Address",
(select ppf.WORK_TELEPHONE
FROM Per_all_people_f ppf,
Ra_salesreps_all Rsr,
apps.RA_CUST_TRX_LINE_SALESREPS_ALL rcs
WHERE ppf.Person_id = Rsr.Person_id
and rsr.Salesrep_id = rcs.Salesrep_id
and rcs.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
AND ROWNUM = 1) "Salesperson Phone Number",
NULL "Salesperson Territory",
NULL "Salesperson Manager Name",
NVL (Ship_acct_site.Attribute10, Bill_acct_site.Attribute10) "Territory Region",
NULL "Sales Force Name",
------------ship to -------------------------
Ship_acct.Account_number "Ship-To Customer Number",
nvl(Ship_acct.Account_name,(select party_name from hz_parties where party_id = Ship_acct.party_id)) "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.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",
NULL "Agent Number",
------------Bill to -------------------------
Bill_acct.Account_number "Bill-To Customer Number",
nvl(Bill_acct.Account_name,(select party_name from hz_parties where party_id = Bill_acct.party_id)) "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.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",
----------------sold to------------
Sold_acct.Account_number "Sold-To Customer Number",
Sold_acct.Account_name "Sold-to Company Name",
Sold_loc.Address1 "Sold-to Address Line 1",
Sold_loc.Address2 "Sold-to Address Line 2",
Sold_loc.Address3 "Sold-to Address Line 3",
Sold_loc.City "Sold-to City",
NVL (Sold_loc.State, Sold_loc.Province) "Sold-to State/Province",
Sold_loc.Country "Sold-to Country",
Sold_loc.Postal_code "Sold-to Postal Code",
--====== modify as per requirements===
NULL "Sold-to Contact First Name",
NULL "Sold-to Contact Last Name",
NULL "Sold-to Contact Email Address",
NULL "Sold-to Contact Phone Number",
NULL "Sold-To Customer Type",
----------------end cust------------
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 City",
NULL "End Use State/Province",
NULL "End Use Country",
NULL "End Use Postal Code",
NULL "Selling Loc Account Number",
--====== modify as per requirements===
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",
'ORACLE ERP' "Source Loc ERP System ",
NULL "Ship-from Loc Account Number",
loc.name "Ship-from Loc Company Name",
Loc.Address_line_1 "Ship-from Loc Address Line 1",
Loc.Address_line_2 "Ship-from Loc Address Line 2",
Loc.Address_line_3 "Ship-from Loc Address Line 3",
Loc.Town_or_city "Ship-from Loc City",
Loc.Region_2 "Ship-from Loc State/Province",
Loc.Country "Ship-from Loc Country ",
Loc.Postal_code "Ship-from Loc Postal Code"
FROM Ra_customer_trx_all Rct,
Ra_customer_trx_lines_all Rctl,
--Oe_order_headers_all Ooh,
Oe_order_lines_all Ool,
Mtl_system_items Msi,
--Ra_terms Rt,
(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 ,
------------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_locations Bill_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_locations Ship_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
WHERE Rct.Customer_trx_id = Rctl.Customer_trx_id
--AND Rt.Term_id = rct.term_id
and rctl.INTERFACE_LINE_ATTRIBUTE6 = to_char(ool.line_id(+))
AND rctl.warehouse_id = Loc.Organization_id(+)
AND Ool.Inventory_item_id = Msi.Inventory_item_id(+)
AND Ool.Ship_from_org_id = Msi.Organization_id(+)
------------Bill to -------------------------
AND rct.BILL_TO_SITE_USE_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
------------ship to -------------------------
AND rct.SHIP_TO_SITE_USE_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
----------------sold to------------
AND nvl(rct.SOLD_TO_SITE_USE_ID,rct.SHIP_TO_SITE_USE_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
AND Sold_acct_site.Cust_account_id = Sold_acct.Cust_account_id
AND Sold_acct.Cust_account_id = rct.SOLD_TO_CUSTOMER_ID
order by 1
Comments
Post a Comment