VIEW: APPS.AR_BILL_TO_SITE_USES_S_V

Source

View Text - Preformatted

SELECT /*+INDEX(l ar_xla_lines_extract_n1)*/ casa1.customer_category_code bill_site_cust_cat_code ,casa1.cust_acct_site_id bill_site_cust_acct_site_id ,casa1.global_attribute1 bill_site_global_attr1 ,casa1.global_attribute2 bill_site_global_attr2 ,casa1.global_attribute3 bill_site_global_attr3 ,casa1.global_attribute4 bill_site_global_attr4 ,casa1.global_attribute5 bill_site_global_attr5 ,casa1.global_attribute6 bill_site_global_attr6 ,casa1.global_attribute7 bill_site_global_attr7 ,casa1.global_attribute8 bill_site_global_attr8 ,casa1.global_attribute9 bill_site_global_attr9 ,casa1.global_attribute_category bill_site_global_attr_cat ,casa1.translated_customer_name bill_site_trnsl_cust_name ,csua1.global_attribute1 bill_uses_global_attr1 ,csua1.global_attribute_category bill_uses_global_attr_cat ,csua1.gl_id_clearing bill_uses_clearing_ccid ,csua1.gl_id_freight bill_uses_freight_ccid ,csua1.gl_id_rec bill_uses_rec_ccid ,csua1.gl_id_rev bill_uses_rev_ccid ,csua1.gl_id_tax bill_uses_tax_ccid ,csua1.gl_id_unbilled bill_uses_unbilled_ccid ,csua1.gl_id_unearned bill_uses_unearned_ccid ,csua1.gsa_indicator bill_uses_gsa_indicator ,csua1.location bill_uses_location ,csua1.site_use_code bill_uses_site_use_code ,csua1.site_use_id bill_uses_site_use_id ,csua1.tax_reference bill_uses_tax_reference ,t1.description bill_territory_description ,t1.name bill_territory_name ,t1.segment1 bill_territory_segment1 ,t1.segment10 bill_territory_segment10 ,t1.segment11 bill_territory_segment11 ,t1.segment12 bill_territory_segment12 ,t1.segment13 bill_territory_segment13 ,t1.segment14 bill_territory_segment14 ,t1.segment15 bill_territory_segment15 ,t1.segment16 bill_territory_segment16 ,t1.segment17 bill_territory_segment17 ,t1.segment18 bill_territory_segment18 ,t1.segment19 bill_territory_segment19 ,t1.segment3 bill_territory_segment2 ,t1.segment19 bill_territory_segment20 ,t1.segment3 bill_territory_segment3 ,t1.segment4 bill_territory_segment4 ,t1.segment5 bill_territory_segment5 ,t1.segment6 bill_territory_segment6 ,t1.segment7 bill_territory_segment7 ,t1.segment8 bill_territory_segment8 ,t1.segment9 bill_territory_segment9 ,t1.territory_id bill_territory_id ,casa1.party_site_id BILL_SITE_PARTY_SITE_ID ,l.event_id event_id ,l.line_number line_number ,l.ledger_id ledger_id FROM hz_cust_site_uses_all csua1 ,hz_cust_acct_sites_all casa1 ,ra_territories t1 ,ar_xla_lines_extract l WHERE csua1.site_use_id = decode(l.event_class_code, 'ADJUSTMENT', nvl(l.drawee_site_use_id, l.bill_site_use_id) , l.bill_site_use_id) AND csua1.cust_acct_site_id = casa1.cust_acct_site_id AND csua1.territory_id = t1.territory_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'))

View Text - HTML Formatted

SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1)*/ CASA1.CUSTOMER_CATEGORY_CODE BILL_SITE_CUST_CAT_CODE
, CASA1.CUST_ACCT_SITE_ID BILL_SITE_CUST_ACCT_SITE_ID
, CASA1.GLOBAL_ATTRIBUTE1 BILL_SITE_GLOBAL_ATTR1
, CASA1.GLOBAL_ATTRIBUTE2 BILL_SITE_GLOBAL_ATTR2
, CASA1.GLOBAL_ATTRIBUTE3 BILL_SITE_GLOBAL_ATTR3
, CASA1.GLOBAL_ATTRIBUTE4 BILL_SITE_GLOBAL_ATTR4
, CASA1.GLOBAL_ATTRIBUTE5 BILL_SITE_GLOBAL_ATTR5
, CASA1.GLOBAL_ATTRIBUTE6 BILL_SITE_GLOBAL_ATTR6
, CASA1.GLOBAL_ATTRIBUTE7 BILL_SITE_GLOBAL_ATTR7
, CASA1.GLOBAL_ATTRIBUTE8 BILL_SITE_GLOBAL_ATTR8
, CASA1.GLOBAL_ATTRIBUTE9 BILL_SITE_GLOBAL_ATTR9
, CASA1.GLOBAL_ATTRIBUTE_CATEGORY BILL_SITE_GLOBAL_ATTR_CAT
, CASA1.TRANSLATED_CUSTOMER_NAME BILL_SITE_TRNSL_CUST_NAME
, CSUA1.GLOBAL_ATTRIBUTE1 BILL_USES_GLOBAL_ATTR1
, CSUA1.GLOBAL_ATTRIBUTE_CATEGORY BILL_USES_GLOBAL_ATTR_CAT
, CSUA1.GL_ID_CLEARING BILL_USES_CLEARING_CCID
, CSUA1.GL_ID_FREIGHT BILL_USES_FREIGHT_CCID
, CSUA1.GL_ID_REC BILL_USES_REC_CCID
, CSUA1.GL_ID_REV BILL_USES_REV_CCID
, CSUA1.GL_ID_TAX BILL_USES_TAX_CCID
, CSUA1.GL_ID_UNBILLED BILL_USES_UNBILLED_CCID
, CSUA1.GL_ID_UNEARNED BILL_USES_UNEARNED_CCID
, CSUA1.GSA_INDICATOR BILL_USES_GSA_INDICATOR
, CSUA1.LOCATION BILL_USES_LOCATION
, CSUA1.SITE_USE_CODE BILL_USES_SITE_USE_CODE
, CSUA1.SITE_USE_ID BILL_USES_SITE_USE_ID
, CSUA1.TAX_REFERENCE BILL_USES_TAX_REFERENCE
, T1.DESCRIPTION BILL_TERRITORY_DESCRIPTION
, T1.NAME BILL_TERRITORY_NAME
, T1.SEGMENT1 BILL_TERRITORY_SEGMENT1
, T1.SEGMENT10 BILL_TERRITORY_SEGMENT10
, T1.SEGMENT11 BILL_TERRITORY_SEGMENT11
, T1.SEGMENT12 BILL_TERRITORY_SEGMENT12
, T1.SEGMENT13 BILL_TERRITORY_SEGMENT13
, T1.SEGMENT14 BILL_TERRITORY_SEGMENT14
, T1.SEGMENT15 BILL_TERRITORY_SEGMENT15
, T1.SEGMENT16 BILL_TERRITORY_SEGMENT16
, T1.SEGMENT17 BILL_TERRITORY_SEGMENT17
, T1.SEGMENT18 BILL_TERRITORY_SEGMENT18
, T1.SEGMENT19 BILL_TERRITORY_SEGMENT19
, T1.SEGMENT3 BILL_TERRITORY_SEGMENT2
, T1.SEGMENT19 BILL_TERRITORY_SEGMENT20
, T1.SEGMENT3 BILL_TERRITORY_SEGMENT3
, T1.SEGMENT4 BILL_TERRITORY_SEGMENT4
, T1.SEGMENT5 BILL_TERRITORY_SEGMENT5
, T1.SEGMENT6 BILL_TERRITORY_SEGMENT6
, T1.SEGMENT7 BILL_TERRITORY_SEGMENT7
, T1.SEGMENT8 BILL_TERRITORY_SEGMENT8
, T1.SEGMENT9 BILL_TERRITORY_SEGMENT9
, T1.TERRITORY_ID BILL_TERRITORY_ID
, CASA1.PARTY_SITE_ID BILL_SITE_PARTY_SITE_ID
, L.EVENT_ID EVENT_ID
, L.LINE_NUMBER LINE_NUMBER
, L.LEDGER_ID LEDGER_ID
FROM HZ_CUST_SITE_USES_ALL CSUA1
, HZ_CUST_ACCT_SITES_ALL CASA1
, RA_TERRITORIES T1
, AR_XLA_LINES_EXTRACT L
WHERE CSUA1.SITE_USE_ID = DECODE(L.EVENT_CLASS_CODE
, 'ADJUSTMENT'
, NVL(L.DRAWEE_SITE_USE_ID
, L.BILL_SITE_USE_ID)
, L.BILL_SITE_USE_ID)
AND CSUA1.CUST_ACCT_SITE_ID = CASA1.CUST_ACCT_SITE_ID
AND CSUA1.TERRITORY_ID = T1.TERRITORY_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'))