VIEW: APPS.ARFV_APP_DISTS_ALC_V

Source

View Text - Preformatted

SELECT /* APPLICATION, EARNED DISCOUNT and UNEARNED DISCOUNT DISTRIBUTIONS */ DISTINCT ROUND(LNK.unrounded_accounted_dr/nvl(CUR.minimum_accountable_unit, power(10, (-1*CUR.precision))) )* nvl(CUR.minimum_accountable_unit, power(10, (-1*CUR.precision))) accounted_dr , ROUND(LNK.unrounded_accounted_cr/nvl(CUR.minimum_accountable_unit, power(10, (-1*CUR.precision))) )* nvl(CUR.minimum_accountable_unit, power(10, (-1*CUR.precision))) accounted_cr , '_LA:DECODE(HED.GL_TRANSFER_STATUS_CODE,''Y'',''Y'',''N''):AR_LOOKUPS:YES/NO:MEANING' , LG.name , ARD.source_id , DECODE(LIN.accounting_class_code , 'EDISC' , '_LA:''EDISC'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING' , 'UNEDISC' , '_LA:''UEDISC'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING' , 'EXCH_GAIN', '_LA:''EXCH_GAIN'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING' , 'EXCH_LOSS', '_LA:''EXCH_LOSS'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING' , '_LA:DECODE(RA.STATUS,''UNAPP'',''UNAPP'',''APP'',''REC'', ''UNID'',''UNID'',''ACC'',''ACC'',NULL):AR_LOOKUPS:ACCOUNT_TYPE:MEANING') , LG.ledger_id , LIN.accounting_class_code FROM XLA_AE_HEADERS HED , GL_LEDGERS_PUBLIC_V LG , FND_CURRENCIES CUR , XLA_AE_LINES LIN , XLA_DISTRIBUTION_LINKS LNK , AR_DISTRIBUTIONS_ALL ARD WHERE HED.application_id = 222 AND HED.ledger_id = LG.ledger_id AND LG.ledger_category_code = 'ALC' AND CUR.currency_code = LG.currency_code AND DECODE(LIN.accounting_class_code , 'EXCH_GAIN' , 'REC' , 'EXCH_LOSS' , 'REC' , 'RECEIVABLE', 'REC' , LIN.accounting_class_code) = ARD.source_type AND LIN.ae_header_id = HED.ae_header_id AND LIN.application_id = 222 AND LNK.ae_header_id = LIN.ae_header_id AND LNK.ae_line_num = LIN.ae_line_num AND LNK.application_id = 222 /* Filter out reversal links */ AND LNK.ae_header_id = LNK.ref_ae_header_id /* Filter out links being reversed by another link */ AND NOT EXISTS (SELECT 'Reversed' FROM XLA_DISTRIBUTION_LINKS LNK2 WHERE LNK2.application_id = 222 AND LNK2.ref_ae_header_id = LNK.ae_header_id AND LNK2.ref_temp_line_num = LNK.temp_line_num) AND LNK.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' /* XLA will create ALC EXCH_GAIN/EXCH_LOSS lines and populate the corresponding REC distribution ID for XLA_DISTRIBUTION_LINKS.source_distribution_id_num_1 */ AND ARD.line_id = LNK.source_distribution_id_num_1 AND ARD.source_table = 'RA' AND ARD.source_type IN ('REC','UNAPP','UNID','ACC','EDISC','UNEDISC') WITH READ ONLY

View Text - HTML Formatted

SELECT /* APPLICATION
, EARNED DISCOUNT
AND UNEARNED DISCOUNT DISTRIBUTIONS */ DISTINCT ROUND(LNK.UNROUNDED_ACCOUNTED_DR/NVL(CUR.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, (-1*CUR.PRECISION))) )* NVL(CUR.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, (-1*CUR.PRECISION))) ACCOUNTED_DR
, ROUND(LNK.UNROUNDED_ACCOUNTED_CR/NVL(CUR.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, (-1*CUR.PRECISION))) )* NVL(CUR.MINIMUM_ACCOUNTABLE_UNIT
, POWER(10
, (-1*CUR.PRECISION))) ACCOUNTED_CR
, '_LA:DECODE(HED.GL_TRANSFER_STATUS_CODE
, ''Y''
, ''Y''
, ''N''):AR_LOOKUPS:YES/NO:MEANING'
, LG.NAME
, ARD.SOURCE_ID
, DECODE(LIN.ACCOUNTING_CLASS_CODE
, 'EDISC'
, '_LA:''EDISC'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING'
, 'UNEDISC'
, '_LA:''UEDISC'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING'
, 'EXCH_GAIN'
, '_LA:''EXCH_GAIN'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING'
, 'EXCH_LOSS'
, '_LA:''EXCH_LOSS'':AR_LOOKUPS:ACCOUNT_TYPE:MEANING'
, '_LA:DECODE(RA.STATUS
, ''UNAPP''
, ''UNAPP''
, ''APP''
, ''REC''
, ''UNID''
, ''UNID''
, ''ACC''
, ''ACC''
, NULL):AR_LOOKUPS:ACCOUNT_TYPE:MEANING')
, LG.LEDGER_ID
, LIN.ACCOUNTING_CLASS_CODE
FROM XLA_AE_HEADERS HED
, GL_LEDGERS_PUBLIC_V LG
, FND_CURRENCIES CUR
, XLA_AE_LINES LIN
, XLA_DISTRIBUTION_LINKS LNK
, AR_DISTRIBUTIONS_ALL ARD
WHERE HED.APPLICATION_ID = 222
AND HED.LEDGER_ID = LG.LEDGER_ID
AND LG.LEDGER_CATEGORY_CODE = 'ALC'
AND CUR.CURRENCY_CODE = LG.CURRENCY_CODE
AND DECODE(LIN.ACCOUNTING_CLASS_CODE
, 'EXCH_GAIN'
, 'REC'
, 'EXCH_LOSS'
, 'REC'
, 'RECEIVABLE'
, 'REC'
, LIN.ACCOUNTING_CLASS_CODE) = ARD.SOURCE_TYPE
AND LIN.AE_HEADER_ID = HED.AE_HEADER_ID
AND LIN.APPLICATION_ID = 222
AND LNK.AE_HEADER_ID = LIN.AE_HEADER_ID
AND LNK.AE_LINE_NUM = LIN.AE_LINE_NUM
AND LNK.APPLICATION_ID = 222 /* FILTER OUT REVERSAL LINKS */
AND LNK.AE_HEADER_ID = LNK.REF_AE_HEADER_ID /* FILTER OUT LINKS BEING REVERSED BY ANOTHER LINK */
AND NOT EXISTS (SELECT 'REVERSED'
FROM XLA_DISTRIBUTION_LINKS LNK2
WHERE LNK2.APPLICATION_ID = 222
AND LNK2.REF_AE_HEADER_ID = LNK.AE_HEADER_ID
AND LNK2.REF_TEMP_LINE_NUM = LNK.TEMP_LINE_NUM)
AND LNK.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL' /* XLA WILL CREATE ALC EXCH_GAIN/EXCH_LOSS LINES
AND POPULATE THE CORRESPONDING REC DISTRIBUTION ID FOR XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1 */
AND ARD.LINE_ID = LNK.SOURCE_DISTRIBUTION_ID_NUM_1
AND ARD.SOURCE_TABLE = 'RA'
AND ARD.SOURCE_TYPE IN ('REC'
, 'UNAPP'
, 'UNID'
, 'ACC'
, 'EDISC'
, 'UNEDISC') WITH READ ONLY