AR_OPEN_TRX

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
TRX_NUMBER CUSTOMER_ID CUSTOMER_NUMBER CUSTOMER_NAME LOCATION CLASS CLASS_MEANING TRX_TYPE CUST_TRX_TYPE_ID CUST_TRX_TYPE_SEQ_ID INVOICE_CURRENCY_CODE BALANCE_DUE_CURR BALANCE_DUE_CURR_UNFORMATTED BALANCE_DUE_FUNCTIONAL AMOUNT_DUE_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_UNEARNED TRX_EXCHANGE_RATE SET_OF_BOOKS_ID TRX_DATE TRX_GL_DATE TRX_DUE_DATE TERM_ID CALC_DISCOUNT_ON_LINES_FLAG PARTIAL_DISCOUNT_FLAG ALLOW_OVERAPPLICATION_FLAG NATURAL_APPLICATION_ONLY_FLAG CREATION_SIGN PAYMENT_SCHEDULE_ID TERMS_SEQUENCE_NUMBER CUSTOMER_TRX_ID STATUS PURCHASE_ORDER INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE15 TRX_BATCH_SOURCE_NAME AMOUNT_ADJUSTED AMOUNT_ADJUSTED_PENDING AMOUNT_LINE_ITEMS_REMAINING FREIGHT_ORIGINAL FREIGHT_REMAINING RECEIVABLES_CHARGES_REMAINING TAX_ORIGINAL TAX_REMAINING SELECTED_FOR_RECEIPT_BATCH_ID TRX_BILLING_NUMBER BILL_TO_SITE_USE_ID DEFAULT_REFERENCE OPEN_RECEIPT_STATUS OPEN_RECEIPT_STATUS_MEANING CUSTOMER_REFERENCE REASON RECEIVABLE_APPLICATION_ID ACCOUNT_DESCRIPTION CUSTOMER_REASON INTERCOMPANY_FLAG ORG_ID

Name

TRX_NUMBER

CUSTOMER_ID

CUSTOMER_NUMBER

CUSTOMER_NAME

LOCATION

CLASS

CLASS_MEANING

TRX_TYPE

CUST_TRX_TYPE_ID

CUST_TRX_TYPE_SEQ_ID

INVOICE_CURRENCY_CODE

BALANCE_DUE_CURR

BALANCE_DUE_CURR_UNFORMATTED

BALANCE_DUE_FUNCTIONAL

AMOUNT_DUE_ORIGINAL

AMOUNT_LINE_ITEMS_ORIGINAL

DISCOUNT_TAKEN_EARNED

DISCOUNT_TAKEN_UNEARNED

TRX_EXCHANGE_RATE

SET_OF_BOOKS_ID

TRX_DATE

TRX_GL_DATE

TRX_DUE_DATE

TERM_ID

CALC_DISCOUNT_ON_LINES_FLAG

PARTIAL_DISCOUNT_FLAG

ALLOW_OVERAPPLICATION_FLAG

NATURAL_APPLICATION_ONLY_FLAG

CREATION_SIGN

PAYMENT_SCHEDULE_ID

TERMS_SEQUENCE_NUMBER

CUSTOMER_TRX_ID

STATUS

PURCHASE_ORDER

INTERFACE_HEADER_ATTRIBUTE1

INTERFACE_HEADER_ATTRIBUTE2

INTERFACE_HEADER_ATTRIBUTE3

INTERFACE_HEADER_ATTRIBUTE4

INTERFACE_HEADER_ATTRIBUTE5

INTERFACE_HEADER_ATTRIBUTE6

INTERFACE_HEADER_ATTRIBUTE7

INTERFACE_HEADER_ATTRIBUTE8

INTERFACE_HEADER_ATTRIBUTE9

INTERFACE_HEADER_ATTRIBUTE10

INTERFACE_HEADER_ATTRIBUTE11

INTERFACE_HEADER_ATTRIBUTE12

INTERFACE_HEADER_ATTRIBUTE13

INTERFACE_HEADER_ATTRIBUTE14

INTERFACE_HEADER_ATTRIBUTE15

TRX_BATCH_SOURCE_NAME

AMOUNT_ADJUSTED

AMOUNT_ADJUSTED_PENDING

AMOUNT_LINE_ITEMS_REMAINING

FREIGHT_ORIGINAL

FREIGHT_REMAINING

RECEIVABLES_CHARGES_REMAINING

TAX_ORIGINAL

TAX_REMAINING

SELECTED_FOR_RECEIPT_BATCH_ID

TRX_BILLING_NUMBER

BILL_TO_SITE_USE_ID

DEFAULT_REFERENCE

OPEN_RECEIPT_STATUS

OPEN_RECEIPT_STATUS_MEANING

CUSTOMER_REFERENCE

REASON

RECEIVABLE_APPLICATION_ID

ACCOUNT_DESCRIPTION

CUSTOMER_REASON

INTERCOMPANY_FLAG

ORG_ID

Query

SQL_Statement
SELECT PS.TRX_NUMBER TRX_NUMBER , PS.CUSTOMER_ID CUSTOMER_ID , CST.ACCOUNT_NUMBER CUSTOMER_NUMBER , SUBSTRB(CST_PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , SU.LOCATION LOCATION , PS.CLASS CLASS , DECODE(PS.CUSTOMER_ID ,-1,NULL,-3,NULL,-4,NULL,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS)) CLASS_MEANING , CTT.NAME TRX_TYPE , CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , CTT.CUST_TRX_TYPE_SEQ_ID CUST_TRX_TYPE_SEQ_ID , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , SUBSTR(DECODE(SIGN(PS.CUSTOMER_ID) , -1,NULL, TO_CHAR(PS.AMOUNT_DUE_REMAINING ,FND_CURRENCY.GET_FORMAT_MASK( DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID), -1,'USD' , PS.INVOICE_CURRENCY_CODE) ,30))),1,30) BALANCE_DUE_CURR , PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED , PS.ACCTD_AMOUNT_DUE_REMAINING BALANCE_DUE_FUNCTIONAL , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL , PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED , PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED , PS.EXCHANGE_RATE TRX_EXCHANGE_RATE , CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, CT.TRX_DATE TRX_DATE , PS.GL_DATE TRX_GL_DATE , trunc(DECODE(SIGN(PS.CUSTOMER_ID), -1,fnd_date.canonical_to_date(NULL), PS.DUE_DATE)) TRX_DUE_DATE , PS.TERM_ID TERM_ID , T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG , T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.ALLOW_OVERAPPLICATION_FLAG) ALLOW_OVERAPPLICATION_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.NATURAL_APPLICATION_ONLY_FLAG) NATURAL_APPLICATION_ONLY_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'P',CTT.CREATION_SIGN) CREATION_SIGN , PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , TO_NUMBER(DECODE( SIGN(PS.PAYMENT_SCHEDULE_ID), -1,NULL , PS.TERMS_SEQUENCE_NUMBER)) TERMS_SEQUENCE_NUMBER , CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.STATUS STATUS , CT.PURCHASE_ORDER , CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1 , CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2 , CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3 , CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4 , CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5 , CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6 , CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7 , CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8 , CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9 , CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10 , CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11 , CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12 , CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13 , CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14 , CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15 , BS.NAME TRX_BATCH_SOURCE_NAME , NVL(PS.AMOUNT_ADJUSTED,0) AMOUNT_ADJUSTED , NVL(PS.AMOUNT_ADJUSTED_PENDING,0) AMOUNT_ADJUSTED_PENDING , NVL(PS.AMOUNT_LINE_ITEMS_REMAINING,0) AMOUNT_LINE_ITEMS_REMAINING , NVL(PS.FREIGHT_ORIGINAL,0) FREIGHT_ORIGINAL , NVL(PS.FREIGHT_REMAINING,0) FREIGHT_REMAINING , NVL(PS.RECEIVABLES_CHARGES_REMAINING,0) RECEIVABLES_CHARGES_REMAINING , NVL(PS.TAX_ORIGINAL,0) TAX_ORIGINAL , NVL(PS.TAX_REMAINING,0) TAX_REMAINING , PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID , CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER , CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID , BS.DEFAULT_REFERENCE , NULL OPEN_RECEIPT_STATUS , NULL OPEN_RECEIPT_STATUS_MEANING, CT.CUSTOMER_REFERENCE , DECODE(PS.CLASS,'CM', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON',CT.REASON_CODE) ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON',CT.REASON_CODE)) REASON , TO_NUMBER(NULL) RECEIVABLE_APPLICATION_ID , CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION , NULL CUSTOMER_REASON, CT.INTERCOMPANY_FLAG, PS.ORG_ID ORG_ID FROM RA_CUSTOMER_TRX_ALL CT , /* Modified for Cross BU - barathsr / RA_CUST_TRX_TYPES_ALL CTT , / <Fusion v1:SetID:pthapliy> changed to _ALL / HZ_CUST_SITE_USES_ALL SU , / <Fusion v1:TCA SetID:pthapliy> changed to _ALL / RA_BATCH_SOURCES_ALL BS , / <Fusion v1:SetID:pthapliy> changed to _ALL / /, AR_LOOKUPS LU Not used -Get lookup meaning used instead. / HZ_CUST_ACCOUNTS CST , HZ_PARTIES CST_PARTY , RA_TERMS T , AR_PAYMENT_SCHEDULES_ALL PS ,/ Modified for Cross BU - barathsr / AR_CONS_INV_ALL CI / Modified for Cross BU - barathsr / WHERE PS.CLASS IN ('CB','CM','DEP','DM','INV','BR') / 20-APR-2000 J Rautiainen BR Implementation / /AND DECODE(arp_view_constants.get_ps_selected_in_batch, null, 1, PS.selected_for_receipt_batch_id ) = nvl(PS.selected_for_receipt_batch_id,1)/ / Commented below line for Bug 4685431 AND PS.RESERVED_TYPE IS NULL /* 29-APR-2000 J Rautiainen BR Implementation / AND PS.RESERVED_VALUE IS NULL / 29-APR-2000 J Rautiainen BR Implementation / AND DECODE(PS.PAYMENT_SCHEDULE_ID,-4,ARPT_SQL_FUNC_UTIL.check_iclaim_installed,'T') = 'T' AND PS.PAYMENT_SCHEDULE_ID NOT IN ( -5,-2,-7) / snambiar receipt chargeback / AND T.TERM_ID(+) = PS.TERM_ID AND CT.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID AND BS.BATCH_SOURCE_SEQ_ID (+) = CT.BATCH_SOURCE_SEQ_ID AND CTT.CUST_TRX_TYPE_SEQ_ID(+) = PS.CUST_TRX_TYPE_SEQ_ID AND CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND CST.PARTY_ID = CST_PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID /AND PS.CLASS = LU.LOOKUP_CODE Not used -Get lookup meaning used instead./ AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL /AND LU.LOOKUP_TYPE = 'INV/CM' Not used -Get lookup meaning used instead./ AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID/ bug 7010433: commented below 2 conditions. AND SU.set_id = FND_SETID_UTILITY.GetSetID('HZ_CUSTOMER_ACCOUNT_SITE', 'BU', mo_global.get_current_org_id) <Fusion v1:TCA SetID:pthapliy> added check for SET_ID AND CT.trx_date BETWEEN SU.start_date and SU.end_date <Fusion v1:TCA Date Effectivity:pthapliy> Added date effectivity check. */

SQL_Statement

SELECT

PS.TRX_NUMBER TRX_NUMBER ,

PS.CUSTOMER_ID CUSTOMER_ID ,

CST.ACCOUNT_NUMBER CUSTOMER_NUMBER ,

SUBSTRB(CST_PARTY.PARTY_NAME,1,50) CUSTOMER_NAME ,

SU.LOCATION LOCATION ,

PS.CLASS CLASS ,

DECODE(PS.CUSTOMER_ID ,-1,NULL,-3,NULL,-4,NULL,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS)) CLASS_MEANING ,

CTT.NAME TRX_TYPE ,

CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID ,

CTT.CUST_TRX_TYPE_SEQ_ID CUST_TRX_TYPE_SEQ_ID ,

PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE ,

SUBSTR(DECODE(SIGN(PS.CUSTOMER_ID) , -1,NULL, TO_CHAR(PS.AMOUNT_DUE_REMAINING ,FND_CURRENCY.GET_FORMAT_MASK( DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID), -1,'USD' , PS.INVOICE_CURRENCY_CODE) ,30))),1,30) BALANCE_DUE_CURR ,

PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED ,

PS.ACCTD_AMOUNT_DUE_REMAINING BALANCE_DUE_FUNCTIONAL ,

PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL ,

PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL ,

PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED ,

PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED ,

PS.EXCHANGE_RATE TRX_EXCHANGE_RATE ,

CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,

CT.TRX_DATE TRX_DATE ,

PS.GL_DATE TRX_GL_DATE ,

trunc(DECODE(SIGN(PS.CUSTOMER_ID), -1,fnd_date.canonical_to_date(NULL), PS.DUE_DATE)) TRX_DUE_DATE ,

PS.TERM_ID TERM_ID ,

T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG ,

T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG ,

DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.ALLOW_OVERAPPLICATION_FLAG) ALLOW_OVERAPPLICATION_FLAG ,

DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.NATURAL_APPLICATION_ONLY_FLAG) NATURAL_APPLICATION_ONLY_FLAG ,

DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'P',CTT.CREATION_SIGN) CREATION_SIGN ,

PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID ,

TO_NUMBER(DECODE( SIGN(PS.PAYMENT_SCHEDULE_ID), -1,NULL , PS.TERMS_SEQUENCE_NUMBER)) TERMS_SEQUENCE_NUMBER ,

CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID ,

PS.STATUS STATUS ,

CT.PURCHASE_ORDER ,

CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1 , CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2 ,

CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3 , CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4 ,

CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5 , CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6 ,

CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7 , CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8 ,

CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9 , CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10 ,

CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11 , CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12 ,

CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13 , CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14 ,

CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15 ,

BS.NAME TRX_BATCH_SOURCE_NAME ,

NVL(PS.AMOUNT_ADJUSTED,0) AMOUNT_ADJUSTED ,

NVL(PS.AMOUNT_ADJUSTED_PENDING,0) AMOUNT_ADJUSTED_PENDING ,

NVL(PS.AMOUNT_LINE_ITEMS_REMAINING,0) AMOUNT_LINE_ITEMS_REMAINING ,

NVL(PS.FREIGHT_ORIGINAL,0) FREIGHT_ORIGINAL ,

NVL(PS.FREIGHT_REMAINING,0) FREIGHT_REMAINING ,

NVL(PS.RECEIVABLES_CHARGES_REMAINING,0) RECEIVABLES_CHARGES_REMAINING ,

NVL(PS.TAX_ORIGINAL,0) TAX_ORIGINAL ,

NVL(PS.TAX_REMAINING,0) TAX_REMAINING ,

PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID ,

CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER ,

CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID ,

BS.DEFAULT_REFERENCE ,

NULL OPEN_RECEIPT_STATUS ,

NULL OPEN_RECEIPT_STATUS_MEANING,

CT.CUSTOMER_REFERENCE ,

DECODE(PS.CLASS,'CM', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON',CT.REASON_CODE) ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON',CT.REASON_CODE)) REASON ,

TO_NUMBER(NULL) RECEIVABLE_APPLICATION_ID ,

CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION ,

NULL CUSTOMER_REASON,

CT.INTERCOMPANY_FLAG,

PS.ORG_ID ORG_ID

FROM

RA_CUSTOMER_TRX_ALL CT , /* Modified for Cross BU - barathsr */

RA_CUST_TRX_TYPES_ALL CTT , /* <Fusion v1:SetID:pthapliy> changed to _ALL */

HZ_CUST_SITE_USES_ALL SU , /* <Fusion v1:TCA SetID:pthapliy> changed to _ALL */

RA_BATCH_SOURCES_ALL BS , /* <Fusion v1:SetID:pthapliy> changed to _ALL */

/*, AR_LOOKUPS LU Not used -Get lookup meaning used instead. */

HZ_CUST_ACCOUNTS CST ,

HZ_PARTIES CST_PARTY ,

RA_TERMS T ,

AR_PAYMENT_SCHEDULES_ALL PS ,/* Modified for Cross BU - barathsr */

AR_CONS_INV_ALL CI /* Modified for Cross BU - barathsr */

WHERE PS.CLASS IN ('CB','CM','DEP','DM','INV','BR') /* 20-APR-2000 J Rautiainen BR Implementation */

/*AND DECODE(arp_view_constants.get_ps_selected_in_batch,

null, 1,

PS.selected_for_receipt_batch_id ) = nvl(PS.selected_for_receipt_batch_id,1)*/

/* Commented below line for Bug 4685431

AND PS.RESERVED_TYPE IS NULL /* 29-APR-2000 J Rautiainen BR Implementation */

AND PS.RESERVED_VALUE IS NULL /* 29-APR-2000 J Rautiainen BR Implementation */

AND DECODE(PS.PAYMENT_SCHEDULE_ID,-4,ARPT_SQL_FUNC_UTIL.check_iclaim_installed,'T') = 'T'

AND PS.PAYMENT_SCHEDULE_ID NOT IN ( -5,-2,-7) /* snambiar receipt chargeback */

AND T.TERM_ID(+) = PS.TERM_ID

AND CT.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID

AND BS.BATCH_SOURCE_SEQ_ID (+) = CT.BATCH_SOURCE_SEQ_ID

AND CTT.CUST_TRX_TYPE_SEQ_ID(+) = PS.CUST_TRX_TYPE_SEQ_ID

AND CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID

AND CST.PARTY_ID = CST_PARTY.PARTY_ID(+)

AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID

/*AND PS.CLASS = LU.LOOKUP_CODE Not used -Get lookup meaning used instead.*/

AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL

/*AND LU.LOOKUP_TYPE = 'INV/CM' Not used -Get lookup meaning used instead.*/

AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID/* bug 7010433: commented below 2 conditions.

AND SU.set_id = FND_SETID_UTILITY.GetSetID('HZ_CUSTOMER_ACCOUNT_SITE', 'BU', mo_global.get_current_org_id) <Fusion v1:TCA SetID:pthapliy> added check for SET_ID

AND CT.trx_date BETWEEN SU.start_date and SU.end_date <Fusion v1:TCA Date Effectivity:pthapliy> Added date effectivity check. */