AR_PAY_CUST_S

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
PAY_CUST_ACCOUNT_NUMBER PAY_CUST_TYPE PAY_CUST_ACCOUNT_ID PAY_CUST_NPA_NUMBER PAY_CUST_ALT_NAME PAY_CUST_CAT_CODE PAY_CUST_CITY PAY_CUST_COUNTRY PAY_CUST_COUNTY PAY_CUST_FISCAL_CODE PAY_CUST_GLOBAL_ATTR1 PAY_CUST_GLOBAL_ATTR10 PAY_CUST_GLOBAL_ATTR12 PAY_CUST_GLOBAL_ATTR9 PAY_CUST_GLOBAL_ATTR_CAT PAY_CUST_GROUP_TYPE PAY_CUST_GSA_FLAG PAY_CUST_PARTY_ID PAY_CUST_PARTY_NAME PAY_CUST_PARTY_NUMBER PAY_CUST_PARTY_TYPE PAY_CUST_POSTAL_CODE PAY_CUST_PROVINCE PAY_CUST_STATE PAY_CUST_TAX_REFERENCE EVENT_ID LINE_NUMBER LEDGER_ID PAY_CUSTOMER_CLASS_CODE

Name

PAY_CUST_ACCOUNT_NUMBER

PAY_CUST_TYPE

PAY_CUST_ACCOUNT_ID

PAY_CUST_NPA_NUMBER

PAY_CUST_ALT_NAME

PAY_CUST_CAT_CODE

PAY_CUST_CITY

PAY_CUST_COUNTRY

PAY_CUST_COUNTY

PAY_CUST_FISCAL_CODE

PAY_CUST_GLOBAL_ATTR1

PAY_CUST_GLOBAL_ATTR10

PAY_CUST_GLOBAL_ATTR12

PAY_CUST_GLOBAL_ATTR9

PAY_CUST_GLOBAL_ATTR_CAT

PAY_CUST_GROUP_TYPE

PAY_CUST_GSA_FLAG

PAY_CUST_PARTY_ID

PAY_CUST_PARTY_NAME

PAY_CUST_PARTY_NUMBER

PAY_CUST_PARTY_TYPE

PAY_CUST_POSTAL_CODE

PAY_CUST_PROVINCE

PAY_CUST_STATE

PAY_CUST_TAX_REFERENCE

EVENT_ID

LINE_NUMBER

LEDGER_ID

PAY_CUSTOMER_CLASS_CODE

Query

SQL_Statement
SELECT /+INDEX(l ar_xla_lines_extract_n1)/ ca3.account_number pay_cust_account_number , ca3.customer_type pay_cust_type ,ca3.cust_account_id pay_cust_account_id , ca3.npa_number pay_cust_npa_number ,ca3.account_name PAY_CUST_ALT_NAME , p3.category_code PAY_CUST_CAT_CODE , loc.city PAY_CUST_CITY , loc.country PAY_CUST_COUNTRY , loc.county PAY_CUST_COUNTY , p3.jgzz_fiscal_code PAY_CUST_FISCAL_CODE , null PAY_CUST_GLOBAL_ATTR1 , null PAY_CUST_GLOBAL_ATTR10 , null PAY_CUST_GLOBAL_ATTR12 , null PAY_CUST_GLOBAL_ATTR9 , null PAY_CUST_GLOBAL_ATTR_CAT , p3.group_type PAY_CUST_GROUP_TYPE ,p3.gsa_indicator_flag PAY_CUST_GSA_FLAG , ca3.party_id PAY_CUST_PARTY_ID ,p3.party_name PAY_CUST_PARTY_NAME , p3.party_number PAY_CUST_PARTY_NUMBER ,p3.party_type PAY_CUST_PARTY_TYPE , loc.postal_code PAY_CUST_POSTAL_CODE , loc.province PAY_CUST_PROVINCE , loc.state PAY_CUST_STATE , null PAY_CUST_TAX_REFERENCE , l.event_id event_id ,l.line_number line_number ,l.ledger_id ledger_id , ca3.customer_class_code pay_customer_class_code FROM hz_cust_accounts ca3 ,hz_parties p3 ,ar_xla_lines_extract l ,hz_cust_site_uses_all su ,hz_cust_acct_sites_all st ,hz_party_sites p_site ,hz_locations loc WHERE ca3.cust_account_id = l.paying_customer_id AND ca3.party_id = p3.party_id AND su.site_use_id = l.paying_site_use_id AND st.cust_acct_site_id = su.cust_acct_site_id AND p_site.party_site_id = st.party_site_id AND loc.location_id = p_site.location_id AND ((l.event_class_code IN ('INVOICE','DEBIT_MEMO','DEPOSIT', 'GUARANTEE','CHARGEBACK','ADJUSTMENT') AND l.level_flag = 'H') OR (l.event_class_code IN ('CREDIT_MEMO','RECEIPT') AND l.level_flag = 'L' AND l.from_to_flag = 'T'))

SQL_Statement

SELECT /*+INDEX(l ar_xla_lines_extract_n1)*/ ca3.account_number pay_cust_account_number ,

ca3.customer_type pay_cust_type ,ca3.cust_account_id pay_cust_account_id ,

ca3.npa_number pay_cust_npa_number ,ca3.account_name PAY_CUST_ALT_NAME ,

p3.category_code PAY_CUST_CAT_CODE , loc.city PAY_CUST_CITY ,

loc.country PAY_CUST_COUNTRY ,

loc.county PAY_CUST_COUNTY ,

p3.jgzz_fiscal_code PAY_CUST_FISCAL_CODE ,

null PAY_CUST_GLOBAL_ATTR1 ,

null PAY_CUST_GLOBAL_ATTR10 ,

null PAY_CUST_GLOBAL_ATTR12 ,

null PAY_CUST_GLOBAL_ATTR9 ,

null PAY_CUST_GLOBAL_ATTR_CAT ,

p3.group_type PAY_CUST_GROUP_TYPE ,p3.gsa_indicator_flag PAY_CUST_GSA_FLAG ,

ca3.party_id PAY_CUST_PARTY_ID ,p3.party_name PAY_CUST_PARTY_NAME ,

p3.party_number PAY_CUST_PARTY_NUMBER ,p3.party_type PAY_CUST_PARTY_TYPE ,

loc.postal_code PAY_CUST_POSTAL_CODE ,

loc.province PAY_CUST_PROVINCE ,

loc.state PAY_CUST_STATE ,

null PAY_CUST_TAX_REFERENCE ,

l.event_id event_id ,l.line_number line_number ,l.ledger_id ledger_id ,

ca3.customer_class_code pay_customer_class_code

FROM

hz_cust_accounts ca3 ,hz_parties p3 ,ar_xla_lines_extract l

,hz_cust_site_uses_all su

,hz_cust_acct_sites_all st

,hz_party_sites p_site

,hz_locations loc

WHERE ca3.cust_account_id = l.paying_customer_id

AND ca3.party_id = p3.party_id

AND su.site_use_id = l.paying_site_use_id

AND st.cust_acct_site_id = su.cust_acct_site_id

AND p_site.party_site_id = st.party_site_id

AND loc.location_id = p_site.location_id

AND ((l.event_class_code IN ('INVOICE','DEBIT_MEMO','DEPOSIT', 'GUARANTEE','CHARGEBACK','ADJUSTMENT') AND l.level_flag = 'H') OR (l.event_class_code IN ('CREDIT_MEMO','RECEIPT') AND l.level_flag = 'L' AND l.from_to_flag = 'T'))