Query to get customer tax registration details in R12

Query to get customer tax registration details in R12
oracle apps r12 tax tables
customer tax registration number in oracle apps r12 query
oracle apps customer tax id query

--Customer Account Tax Registration Details

SELECT party_name,
       account_number,
       ps.party_site_number,
       (SELECT LISTAGG (al.meaning, ', ') WITHIN GROUP (ORDER BY al.meaning)
          FROM apps.hz_cust_acct_sites_all s,
               apps.hz_cust_site_uses_all  u,
               apps.ar_lookups             al
         WHERE     s.cust_acct_site_id = hcas.cust_acct_site_id
               AND u.cust_acct_site_id = s.cust_acct_site_id
               AND u.status = 'A'
               AND al.lookup_type = 'SITE_USE_CODE'
               AND al.lookup_code = u.site_use_code)
          purposes,
       DECODE (ps.status,  'A', 'Active',  'I', 'Inactive',  ps.status)
          AS site_status,
       hcas.ORG_ID,
       ou.name                   AS operating_unit,
       terr.territory_short_name AS country_name,
          lc.address1
       || DECODE (lc.address2, NULL, '', CHR (10))
       || lc.address2
       || DECODE (lc.address3, NULL, '', CHR (10))
       || lc.address3
       || DECODE (lc.address4, NULL, '', CHR (10))
       || lc.address4
       || DECODE (lc.city, NULL, '', CHR (10))
       || lc.city
       || DECODE (lc.state, NULL, '', ',')
       || lc.state
       || DECODE (lc.postal_code, '', ',')
       || lc.postal_code
          address,
       qrs.registration_number,
       qrs.tax_regime_code,
       qrs.tax,
       qrs.registration_status_code,
       qrs.effective_from,
       qrs.effective_to
  FROM apps.hz_cust_acct_sites_all hcas,
       apps.hz_party_sites         ps,
       apps.hz_cust_accounts_all   hca,
       apps.hz_locations           lc,
       apps.fnd_territories_vl     terr,
       apps.hr_operating_units     ou,
       apps.hz_parties             hp,
       (SELECT zr.registration_number,
               zr.tax_regime_code,
               zr.tax,
               zr.registration_status_code,
               zr.effective_from,
               zr.effective_to,
               zptp.party_id,
               zptp.party_tax_profile_id
          FROM apps.zx_registrations zr, apps.zx_party_tax_profile zptp
         WHERE zr.party_tax_profile_id = zptp.party_tax_profile_id) qrs
 WHERE     hp.party_id = hca.party_id
       AND hp.party_id = ps.party_id
       AND hca.cust_account_id = hcas.cust_account_id(+)
       AND ps.party_site_id(+) = hcas.party_site_id
       AND hcas.party_site_id = ps.party_site_id
       AND ps.location_id = lc.location_id
       AND terr.territory_code = lc.country
       AND hcas.org_id = ou.organization_id
       AND hca.status = 'A'
       AND hcas.status = 'A'
       AND qrs.party_id(+) = hcas.party_site_id
       -- AND hca.account_number = p_cust_acct_num
       AND hcas.org_id IN (123);

Related posts:

• PTD and YTD balances query in Oracle R12
• Sales order line status query in Oracle apps
• Query to find all Expense report entry delegations
• Query to find vacation rules in Oracle R12
• View accounting query in oracle apps R12
• Query to get AR invoice SLA and GL details
• Accounting entries not transferred to the General Ledger
• Query to get price list details in Oracle EBS
• Query to find all APIs of Oracle Apps modules
• Oracle R12 shipping status query
• R12 AR cash receipt SLA and GL details query
• Oracle apps AR Applications and SLA details
• Oracle EBS query to get AP Invoice SLA Details
• Oracle EBS query to get AR Invoice SLA Details
• Oracle AR Remit to address query
• Oracle Apps iExpense credit card details query
• E-Business suite tax rates and accounts query
• Query to get customer tax registration details in R12
• Query to get Sales order and lines details in Oracle Apps
• Purchase Requisition Vertex debug xml query in Oracle EBS
• Purchase Order Vertex debug xml query in Oracle EBS
• AP Invoice Vertex debug xml query in Oracle EBS
• AR Invoice Vertex debug xml query in Oracle EBS
• Query to find AP invoice workflow item key in oracle apps
• Query to find serial number material transactions details
• Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
• Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
• Query to find Sales Orders that are shipped but not invoiced
• Query to get Customer Bank Account Details
• Query to get description of GL Code Combination
• Query to find Purchase Order and Invoice Details
• Query to find AP Invoices matched with receipts
• Query to find Supplier Bank Details
• Query to find Credit Memos on an AP invoice
• Query to find the AP invoices that are applied to multiple Purchase Orders
• AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
• Query to find shipset and fulfilment set

Upload your own post and refer it anywhere anytime: