Query to get Customer pan number in Oracle Apps r12

Query to get Customer pan number in Oracle Apps r12

Hi friends, we are going to discuss about the sql query to get Customer pan number in Oracle r12. We will share the detail sql query about customer which helps to get the customer details in oracle apps with their pan number related informations. There are two options to maintain the customer pan number in oracle apps. Either we can maintain the customer master or we can maintain under the India Localization module in party registrations. In this post, We will share the both sql queries which helps to get the customer pan number either from the customer master as well from the party registrations under the India localization. This is one of the important query about the customer tax details including pan number in oracle apps r12. Please find below the complete detail about the Query to get Customer pan number in Oracle Apps r12.

Two Options to maintain the Customer Pan number in oracle r12

1. Customer Master Page.

2.Party Registrations under the India Localization.

Query to get Customer pan number in Oracle Apps r12

India Localization sql Query to get Customer pan number from party registrations

Important Tables to get the Customer pan number in Oracle Apps r12

1.JAI_PARTY_REG_LINES

2.JAI_PARTY_REGS

Here below is the detail sql query to get the customer pan number informations in India Localization.

SELECT HZP.PARTY_NAME "Customer Name",

CUST.ACCOUNT_NUMBER "Customer Number",

PARTY_SITE.PARTY_SITE_NUMBER "Customer Site Number",

SHIP.SITE_USE_CODE ,

HZL.ADDRESS1||' '|| HZL.ADDRESS2||' '|| HZL.ADDRESS3||' '|| HZL.POSTAL_CODE||' '|| HZL.CITY||' '||NVL(HZL.STATE,HZL.PROVINCE) "Customer Site Address",

(SELECT MIN (JPRL.REGISTRATION_NUMBER)

FROM apps.JAI_PARTY_REG_LINES JPRL,apps.JAI_PARTY_REGS JPR

WHERE JPRL.PARTY_REG_ID = JPR.PARTY_REG_ID

AND JPR.PARTY_SITE_ID(+) = ACCT.CUST_ACCT_SITE_ID

AND JPR.PARTY_ID = CUST.CUST_ACCOUNT_ID

AND NVL (JPRL.EFFECTIVE_FROM, SYSDATE) = SYSDATE

AND REGISTRATION_TYPE_CODE = 'PAN'

AND JPR.PARTY_TYPE_CODE IN ('THIRD_PARTY', 'THIRD_PARTY_SITE')) "Customer Pan No.",

(SELECT MIN (JPRL.REGISTRATION_NUMBER)

FROM apps.JAI_PARTY_REG_LINES JPRL,apps.JAI_PARTY_REGS JPR

WHERE JPRL.PARTY_REG_ID = JPR.PARTY_REG_ID

AND JPR.PARTY_SITE_ID(+) = ACCT.CUST_ACCT_SITE_ID

AND JPR.PARTY_ID = CUST.CUST_ACCOUNT_ID

AND NVL (JPRL.EFFECTIVE_FROM, SYSDATE) = SYSDATE

AND REGISTRATION_TYPE_CODE = 'GSTIN'

AND JPR.PARTY_TYPE_CODE IN ('THIRD_PARTY', 'THIRD_PARTY_SITE')) "Customer GST No."

FROM apps.HZ_CUST_ACCOUNTS_ALL CUST,

apps.HZ_CUST_ACCT_SITES_ALL ACCT,

apps.HZ_CUST_SITE_USES_ALL SHIP,

apps.HZ_PARTY_SITES PARTY_SITE,

apps.HZ_LOCATIONS HZL,

apps.HZ_PARTIES HZP

WHERE CUST.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID

AND SHIP.ORG_ID = :P_ORG_ID 

AND HZL.LOCATION_ID = PARTY_SITE.LOCATION_ID

AND ACCT.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID

AND ACCT.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID

AND ACCT.ORG_ID = SHIP.ORG_ID

AND CUST.STATUS = 'A'

AND CUST.PARTY_ID = HZP.PARTY_ID

Customer Master sql query to get the customer pan number in oracle apps r12

Here below is the detail sql query to get the customer pan number informations in customer master.

Query to get the Customer Pan number from Headers

Select a1.REGISTRATION_NUMBER FROM zx_registrations A1,ZX_PARTY_TAX_PROFILE A2

WHERE TAX_REGIME_CODE='IN GST'

AND EFFECTIVE_TO IS NULL

AND ROWNUM=1

AND A1.PARTY_TAX_PROFILE_ID=A2.PARTY_TAX_PROFILE_ID

AND A2.party_id=(select party_id from hz_cust_accounts_all where cust_account_id=:P_CUSTOMER_ID)

Query to get the Customer Pan number from Sites

SELECT A1.REGISTRATION_NUMBER FROM zx_registrations A1,ZX_PARTY_TAX_PROFILE A2

WHERE TAX_REGIME_CODE='IN GST'

AND EFFECTIVE_TO IS NULL

AND ROWNUM=1

AND A1.PARTY_TAX_PROFILE_ID=A2.PARTY_TAX_PROFILE_ID

AND A2.party_id=:P_PARTY_SITE_ID

Important Note: Party_id column  in the ZX_PARTY_TAX_PROFILE store different values. 

For Storing Customer Header Tax Information's Party_id column in  ZX_PARTY_TAX_PROFILE

stores Customer Party Id but to Storing Customer Site Tax Information's Party_id column in  ZX_PARTY_TAX_PROFILE stores that Customer Party Id.

Query to get Customer pan number in Oracle Apps r12