How to use this query?
1 – For Oracle EBS R12 to Oracle Fusion Cloud Data migration, for that run the query, extract the results, then put in on FBDI Oracle template and upload it to oracle fusion.
2 – In case the user asked you for a report of all customers, you can extract the output and share it with end user.
3 – While building oracle reports, you can use it as SQL source code for building your report.
4- for Auditing purpose, run the query and share the results with auditing.
Pay attention to the Customer structure, every customer should have at least one customer account, while customers may have many accounts at the same time.
If your customer has many business lines, you must create one account per one business line in order to have different statement of account for your customer.
For instance, your customer is having a company for communication, a company for travel, a company for hotel services, in this case add three accounts with three different billing address and contracts, then you will be able to extract three statements of accounts for your customers.
SELECT
distinct
party.party_number ,
party.party_name ,
party.duns_number ,
NULL "Taxpayer id",
null "Customer GSA indecator",
cust.ACCOUNT_NUMBER "Account Number",
cust.account_name ,
cust.account_established_date ,
cust.customer_class_code "Customer Class",
cust.customer_class_code ,
acct.party_site_id,
null "Site Source Reference",
party_site.PARTY_SITE_NAME ,
null "Account Address Set",
ship.orig_system_reference "Location Source Reference",
--loc.location_id "Location Source Reference loc_id",
loc.ADDRESS1,
loc.ADDRESS2 ,
loc.city,
loc.STATE ,
loc.province ,
loc.postal_code ,
loc.county ,
loc.country ,
loc.sales_tax_geocode ,
loc.sales_tax_inside_city_limits ,
loc.validated_flag "Identifying Address" ,
acct.cust_acct_site_id ,
ship.site_use_code ,
ship.primary_flag ,
ship.bill_to_site_use_id
FROM
hz_cust_accounts_all cust,
hz_cust_acct_sites_all acct,
hz_cust_site_uses_all ship,
hz_party_sites party_site ,
hz_locations loc,
hz_parties party
WHERE cust.cust_account_id = acct.cust_account_id
AND acct.cust_acct_site_id = ship.cust_acct_site_id
and loc.location_id = party_site.location_id
and acct.party_site_id = party_site.party_site_id
and cust.party_id = party.party_id
AND acct.ORG_ID = ship.ORG_ID
AND cust.status = 'A'
AND ship.SITE_USE_CODE = 'BILL_TO'
AND ship.primary_flag = 'Y'
AND ship.ORG_ID = :P_ORDID -- Your Organization ID
order by party.party_number
Read More:
https://erpscope365.blogspot.com/
https://www.youtube.com/@ERPScope/featured
Leave a Reply