eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_MASS_APPLICATIONS_V

Source

View Text - Preformatted


SELECT ps_inv.rowid row_id , DECODE(ps_inv.class,'PMT',ps_inv.cash_receipt_id,-1) cash_receipt_id , ps_inv.customer_trx_id customer_trx_id , TO_NUMBER('') cm_customer_trx_id , ps_inv.LAST_UPDATE_DATE LAST_UPDATE_DATE , ps_inv.LAST_UPDATED_BY LAST_UPDATED_BY , ps_inv.CREATION_DATE CREATION_DATE , ps_inv.CREATED_BY CREATED_BY , ps_inv.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , ps_inv.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , ps_inv.PROGRAM_ID PROGRAM_ID , ps_inv.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , ps_inv.REQUEST_ID REQUEST_ID , DECODE(ps_inv.class,'PMT',ps_inv.trx_number,NULL) receipt_number , 'n' applied_flag , ps_inv.customer_id customer_id , substrb(party.party_name,1,50) customer_name , cust.account_number customer_number , ps_inv.trx_number trx_number , ps_inv.terms_sequence_number installment , TO_NUMBER('') amount_applied , TO_NUMBER('') amount_applied_from , TO_NUMBER('') trans_to_receipt_rate , TO_NUMBER('') discount , ps_inv.discount_taken_earned discounts_earned , ps_inv.discount_taken_unearned discounts_unearned , ps_inv.discount_taken_earned discount_taken_earned , ps_inv.discount_taken_unearned discount_taken_unearned , DECODE(ps_inv.class,'PMT',(SUM(app.amount_applied) * -1),ps_inv.amount_due_remaining) amount_due_remaining , ps_inv.due_date due_date , ps_inv.status status , ps_inv.term_id term_id , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',ps_inv.class) trx_class_name , ps_inv.class trx_class_code , ctt.name trx_type_name , ctt.cust_trx_type_id cust_trx_type_id , ct.trx_date trx_date , su.location location_name , ps_inv.customer_site_use_id bill_to_site_use_id , TO_NUMBER('') days_late , TO_NUMBER('') line_number , TO_NUMBER('') customer_trx_line_id , greatest(ps_inv.trx_date, arp_view_constants.get_apply_date) apply_date /* Bug fix 3400584*/ , arp_view_constants.get_default_gl_date( greatest(ps_inv.gl_date, arp_view_constants.get_receipt_gl_date, DECODE(NVL(fnd_profile.value('AR_APPLICATION_GL_DATE_DEFAULT'), 'INV_REC_DT'),'INV_REC_SYS_DT',sysdate,'INV_REC_DT', ps_inv.gl_date))) gl_date /*3079614*/ , null gl_posted_date , null reversal_gl_date , ps_inv.exchange_rate exchange_rate , ps_inv.invoice_currency_code invoice_currency_code , ps_inv.amount_due_original amount_due_original , ps_inv.amount_in_dispute amount_in_dispute , ps_inv.amount_line_items_original amount_line_items_original , ps_inv.acctd_amount_due_remaining acctd_amount_due_remaining , TO_NUMBER('') acctd_amount_applied_to , TO_NUMBER('') acctd_amount_applied_from , TO_NUMBER('') exchange_gain_loss , ps_inv.discount_remaining discount_remaining , t.calc_discount_on_lines_flag calc_discount_on_lines_flag , t.partial_discount_flag partial_discount_flag , ctt.allow_overapplication_flag allow_overapplication_flag , ctt.natural_application_only_flag natural_application_only_flag , ctt.creation_sign creation_sign , ps_inv.payment_schedule_id applied_payment_schedule_id , ct.default_ussgl_transaction_code ussgl_transaction_code , ct.default_ussgl_trx_code_context ussgl_transaction_code_context , ct.purchase_order purchase_order , ct.doc_sequence_id trx_doc_sequence_id , ct.doc_sequence_value trx_doc_sequence_value , bs.name trx_batch_source_name , ps_inv.amount_adjusted amount_adjusted , ps_inv.amount_adjusted_pending amount_adjusted_pending , ps_inv.amount_line_items_remaining amount_line_items_remaining , ps_inv.freight_original freight_original , ps_inv.freight_remaining freight_remaining , ps_inv.receivables_charges_remaining receivables_charges_remaining , ps_inv.tax_original tax_original , ps_inv.tax_remaining tax_remaining , ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id , to_number(decode(app.applied_payment_schedule_id, null,null,app.receivable_application_id)) receivable_application_id , to_number(decode(app.applied_payment_schedule_id, null,null,app.secondary_application_ref_id)) secondary_application_ref_id , NULL attribute_category , NULL attribute1 , NULL attribute2 , NULL attribute3 , NULL attribute4 , NULL attribute5 , NULL attribute6 , NULL attribute7 , NULL attribute8 , NULL attribute9 , NULL attribute10 , NULL attribute11 , NULL attribute12 , NULL attribute13 , NULL attribute14 , NULL attribute15 , ci.cons_billing_number trx_billing_number , NULL global_attribute_CATEGORY , NULL global_attribute1 , NULL global_attribute2 , NULL global_attribute3 , NULL global_attribute4 , NULL global_attribute5 , NULL global_attribute6 , NULL global_attribute7 , NULL global_attribute8 , NULL global_attribute9 , NULL global_attribute10 , NULL global_attribute11 , NULL global_attribute12 , NULL global_attribute13 , NULL global_attribute14 , NULL global_attribute15 , NULL global_attribute16 , NULL global_attribute17 , NULL global_attribute18 , NULL global_attribute19 , NULL global_attribute20 , NULL comments , ctt.attribute10 transaction_category , NULL receivables_trx_id , NULL rec_activity_name , NULL application_ref_type /*These columns are added for bug 2545431 */ , NULL application_ref_type_meaning , NULL application_ref_id , DECODE(PS_INV.CLASS, 'PMT', decode(app.applied_payment_schedule_id, null, null, app.application_ref_num), ct.ct_reference) application_ref_num , NULL payment_set_id , DECODE(PS_INV.CLASS, 'PMT', decode (app.applied_payment_schedule_id, null, null, app.application_ref_reason), ct.REASON_CODE) application_ref_reason , DECODE(PS_INV.CLASS, 'PMT', ARP_DEDUCTION.GET_TM_ORACLE_REASON(to_number( decode(app.applied_payment_schedule_id, null,null,app.secondary_application_ref_id))), '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) ) application_ref_reason_meaning , DECODE(PS_INV.CLASS, 'PMT', decode(app.applied_payment_schedule_id, null, null, app.customer_reference), ct.customer_reference) customer_reference , DECODE(PS_INV.CLASS, 'PMT', decode(app.applied_payment_schedule_id, null, null, app.customer_reason), NULL) customer_reason , TO_NUMBER(NULL) applied_rec_app_id ,NULL secondary_application_ref_num ,NULL secondary_application_ref_type , app.on_acct_cust_id on_acct_cust_id , app.on_acct_cust_site_use_id on_acct_cust_site_use_id , app.on_acct_po_num on_acct_po_num FROM ar_payment_schedules ps_inv , ra_customer_trx_all ct , ra_cust_trx_types_all ctt , hz_cust_accounts cust , hz_parties party , hz_cust_site_uses_all su , ra_batch_sources_all bs , ar_receivable_applications_all app , ra_terms_b t , ar_cons_inv_all ci WHERE ps_inv.selected_for_receipt_batch_id IS NULL AND ps_inv.reserved_type IS NULL AND ps_inv.reserved_value IS NULL AND ps_inv.class||'' <> 'GUAR' AND ps_inv.customer_trx_id = ct.customer_trx_id(+) AND ps_inv.status = 'OP' AND bs.batch_source_id(+) = ct.batch_source_id AND ps_inv.cust_trx_type_id = ctt.cust_trx_type_id(+) AND ps_inv.customer_id = cust.cust_account_id AND cust.party_id = party.party_id AND ps_inv.customer_site_use_id = su.site_use_id(+) AND ci.cons_inv_id(+) = ps_inv.cons_inv_id AND ps_inv.term_id = t.term_id(+) AND ps_inv.cash_receipt_id = app.cash_receipt_id(+) AND NVL(app.status,'UNAPP') IN ('UNAPP','ACC','OTHER ACC') AND DECODE(app.status,'OTHER ACC',app.applied_payment_schedule_id,-4) = -4 AND DECODE(app.applied_payment_schedule_id,-1,app.display,-4,app.display,'Y') = 'Y' AND ps_inv.org_id = ct.org_id (+) AND ps_inv.org_id = ctt.org_id (+) AND ps_inv.org_id = su.org_id (+) AND ps_inv.org_id = app.org_id (+) AND ps_inv.org_id = ci.org_id (+) AND ct.org_id = bs.org_id (+) GROUP BY DECODE(app.applied_payment_schedule_id,NULL,NULL,app.receivable_application_id) , ps_inv.rowid , DECODE(ps_inv.class,'PMT',ps_inv.cash_receipt_id,-1) , ps_inv.customer_trx_id , ps_inv.LAST_UPDATE_DATE , ps_inv.LAST_UPDATED_BY , ps_inv.CREATION_DATE , ps_inv.CREATED_BY , ps_inv.LAST_UPDATE_LOGIN , ps_inv.PROGRAM_APPLICATION_ID , ps_inv.PROGRAM_ID , ps_inv.PROGRAM_UPDATE_DATE , ps_inv.REQUEST_ID , DECODE(ps_inv.class,'PMT',ps_inv.trx_number,NULL) , ps_inv.customer_id , substrb(party.party_name,1,50) , cust.account_number , ps_inv.trx_number , ps_inv.terms_sequence_number , ps_inv.discount_taken_earned , ps_inv.discount_taken_unearned , ps_inv.discount_taken_earned , ps_inv.discount_taken_unearned , ps_inv.amount_due_remaining , ps_inv.due_date , ps_inv.status , ps_inv.term_id , ps_inv.class , ctt.name , ctt.cust_trx_type_id , ct.trx_date , su.location , ps_inv.customer_site_use_id , greatest(ps_inv.trx_date, arp_view_constants.get_apply_date) /* Bug fix 3400584*/ /*3079614*/ , arp_view_constants.get_default_gl_date( greatest(ps_inv.gl_date, arp_view_constants.get_receipt_gl_date, DECODE(NVL(fnd_profile.value('AR_APPLICATION_GL_DATE_DEFAULT'), 'INV_REC_DT'),'INV_REC_SYS_DT',sysdate,'INV_REC_DT', ps_inv.gl_date))) , ps_inv.exchange_rate , ps_inv.invoice_currency_code , ps_inv.amount_due_original , ps_inv.amount_in_dispute , ps_inv.amount_line_items_original , ps_inv.acctd_amount_due_remaining , ps_inv.discount_remaining , t.calc_discount_on_lines_flag , t.partial_discount_flag , ctt.allow_overapplication_flag , ctt.natural_application_only_flag , ctt.creation_sign , ps_inv.payment_schedule_id , ct.default_ussgl_transaction_code , ct.default_ussgl_trx_code_context , ct.purchase_order , ct.doc_sequence_id , ct.doc_sequence_value , bs.name , ps_inv.amount_adjusted , ps_inv.amount_adjusted_pending , ps_inv.amount_line_items_remaining , ps_inv.freight_original , ps_inv.freight_remaining , ps_inv.receivables_charges_remaining , ps_inv.tax_original , ps_inv.tax_remaining , ps_inv.selected_for_receipt_batch_id , app.applied_payment_schedule_id , to_number(decode(app.applied_payment_schedule_id, null,null,app.secondary_application_ref_id)) , ci.cons_billing_number , ctt.attribute10 , ct.reason_code , ct.ct_reference , ct.customer_reference , decode (app.applied_payment_schedule_id, null, null, app.application_ref_reason) , decode (app.applied_payment_schedule_id, null, null, app.application_ref_num) , decode (app.applied_payment_schedule_id, null, null, app.customer_reference) , decode (app.applied_payment_schedule_id, null, null, app.customer_reason) , app.on_acct_cust_id , app.on_acct_cust_site_use_id , app.on_acct_po_num HAVING DECODE(ps_inv.class,'PMT',(SUM(app.amount_applied) * -1),ps_inv.amount_due_remaining) <> 0

View Text - HTML Formatted

SELECT PS_INV.ROWID ROW_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.CASH_RECEIPT_ID
, -1) CASH_RECEIPT_ID
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, TO_NUMBER('') CM_CUSTOMER_TRX_ID
, PS_INV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS_INV.LAST_UPDATED_BY LAST_UPDATED_BY
, PS_INV.CREATION_DATE CREATION_DATE
, PS_INV.CREATED_BY CREATED_BY
, PS_INV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS_INV.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PS_INV.PROGRAM_ID PROGRAM_ID
, PS_INV.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PS_INV.REQUEST_ID REQUEST_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.TRX_NUMBER
, NULL) RECEIPT_NUMBER
, 'N' APPLIED_FLAG
, PS_INV.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PS_INV.TRX_NUMBER TRX_NUMBER
, PS_INV.TERMS_SEQUENCE_NUMBER INSTALLMENT
, TO_NUMBER('') AMOUNT_APPLIED
, TO_NUMBER('') AMOUNT_APPLIED_FROM
, TO_NUMBER('') TRANS_TO_RECEIPT_RATE
, TO_NUMBER('') DISCOUNT
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNTS_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNTS_UNEARNED
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, DECODE(PS_INV.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, PS_INV.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, PS_INV.DUE_DATE DUE_DATE
, PS_INV.STATUS STATUS
, PS_INV.TERM_ID TERM_ID
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS_INV.CLASS) TRX_CLASS_NAME
, PS_INV.CLASS TRX_CLASS_CODE
, CTT.NAME TRX_TYPE_NAME
, CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CT.TRX_DATE TRX_DATE
, SU.LOCATION LOCATION_NAME
, PS_INV.CUSTOMER_SITE_USE_ID BILL_TO_SITE_USE_ID
, TO_NUMBER('') DAYS_LATE
, TO_NUMBER('') LINE_NUMBER
, TO_NUMBER('') CUSTOMER_TRX_LINE_ID
, GREATEST(PS_INV.TRX_DATE
, ARP_VIEW_CONSTANTS.GET_APPLY_DATE) APPLY_DATE /* BUG FIX 3400584*/
, ARP_VIEW_CONSTANTS.GET_DEFAULT_GL_DATE( GREATEST(PS_INV.GL_DATE
, ARP_VIEW_CONSTANTS.GET_RECEIPT_GL_DATE
, DECODE(NVL(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT')
, 'INV_REC_DT')
, 'INV_REC_SYS_DT'
, SYSDATE
, 'INV_REC_DT'
, PS_INV.GL_DATE))) GL_DATE /*3079614*/
, NULL GL_POSTED_DATE
, NULL REVERSAL_GL_DATE
, PS_INV.EXCHANGE_RATE EXCHANGE_RATE
, PS_INV.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PS_INV.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS_INV.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, TO_NUMBER('') ACCTD_AMOUNT_APPLIED_TO
, TO_NUMBER('') ACCTD_AMOUNT_APPLIED_FROM
, TO_NUMBER('') EXCHANGE_GAIN_LOSS
, PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING
, T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN CREATION_SIGN
, PS_INV.PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID
, CT.DEFAULT_USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, CT.DEFAULT_USSGL_TRX_CODE_CONTEXT USSGL_TRANSACTION_CODE_CONTEXT
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.DOC_SEQUENCE_ID TRX_DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, BS.NAME TRX_BATCH_SOURCE_NAME
, PS_INV.AMOUNT_ADJUSTED AMOUNT_ADJUSTED
, PS_INV.AMOUNT_ADJUSTED_PENDING AMOUNT_ADJUSTED_PENDING
, PS_INV.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, PS_INV.FREIGHT_ORIGINAL FREIGHT_ORIGINAL
, PS_INV.FREIGHT_REMAINING FREIGHT_REMAINING
, PS_INV.RECEIVABLES_CHARGES_REMAINING RECEIVABLES_CHARGES_REMAINING
, PS_INV.TAX_ORIGINAL TAX_ORIGINAL
, PS_INV.TAX_REMAINING TAX_REMAINING
, PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)) RECEIVABLE_APPLICATION_ID
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID)) SECONDARY_APPLICATION_REF_ID
, NULL ATTRIBUTE_CATEGORY
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
, CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER
, NULL GLOBAL_ATTRIBUTE_CATEGORY
, NULL GLOBAL_ATTRIBUTE1
, NULL GLOBAL_ATTRIBUTE2
, NULL GLOBAL_ATTRIBUTE3
, NULL GLOBAL_ATTRIBUTE4
, NULL GLOBAL_ATTRIBUTE5
, NULL GLOBAL_ATTRIBUTE6
, NULL GLOBAL_ATTRIBUTE7
, NULL GLOBAL_ATTRIBUTE8
, NULL GLOBAL_ATTRIBUTE9
, NULL GLOBAL_ATTRIBUTE10
, NULL GLOBAL_ATTRIBUTE11
, NULL GLOBAL_ATTRIBUTE12
, NULL GLOBAL_ATTRIBUTE13
, NULL GLOBAL_ATTRIBUTE14
, NULL GLOBAL_ATTRIBUTE15
, NULL GLOBAL_ATTRIBUTE16
, NULL GLOBAL_ATTRIBUTE17
, NULL GLOBAL_ATTRIBUTE18
, NULL GLOBAL_ATTRIBUTE19
, NULL GLOBAL_ATTRIBUTE20
, NULL COMMENTS
, CTT.ATTRIBUTE10 TRANSACTION_CATEGORY
, NULL RECEIVABLES_TRX_ID
, NULL REC_ACTIVITY_NAME
, NULL APPLICATION_REF_TYPE /*THESE COLUMNS ARE ADDED FOR BUG 2545431 */
, NULL APPLICATION_REF_TYPE_MEANING
, NULL APPLICATION_REF_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, CT.CT_REFERENCE) APPLICATION_REF_NUM
, NULL PAYMENT_SET_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_REASON)
, CT.REASON_CODE) APPLICATION_REF_REASON
, DECODE(PS_INV.CLASS
, 'PMT'
, ARP_DEDUCTION.GET_TM_ORACLE_REASON(TO_NUMBER( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID)))
, '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) ) APPLICATION_REF_REASON_MEANING
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REFERENCE)
, CT.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REASON)
, NULL) CUSTOMER_REASON
, TO_NUMBER(NULL) APPLIED_REC_APP_ID
, NULL SECONDARY_APPLICATION_REF_NUM
, NULL SECONDARY_APPLICATION_REF_TYPE
, APP.ON_ACCT_CUST_ID ON_ACCT_CUST_ID
, APP.ON_ACCT_CUST_SITE_USE_ID ON_ACCT_CUST_SITE_USE_ID
, APP.ON_ACCT_PO_NUM ON_ACCT_PO_NUM
FROM AR_PAYMENT_SCHEDULES PS_INV
, RA_CUSTOMER_TRX_ALL CT
, RA_CUST_TRX_TYPES_ALL CTT
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, RA_BATCH_SOURCES_ALL BS
, AR_RECEIVABLE_APPLICATIONS_ALL APP
, RA_TERMS_B T
, AR_CONS_INV_ALL CI
WHERE PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS_INV.RESERVED_TYPE IS NULL
AND PS_INV.RESERVED_VALUE IS NULL
AND PS_INV.CLASS||'' <> 'GUAR'
AND PS_INV.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND PS_INV.STATUS = 'OP'
AND BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID
AND PS_INV.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND PS_INV.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID
AND PS_INV.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND CI.CONS_INV_ID(+) = PS_INV.CONS_INV_ID
AND PS_INV.TERM_ID = T.TERM_ID(+)
AND PS_INV.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID(+)
AND NVL(APP.STATUS
, 'UNAPP') IN ('UNAPP'
, 'ACC'
, 'OTHER ACC')
AND DECODE(APP.STATUS
, 'OTHER ACC'
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4) = -4
AND DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, APP.DISPLAY
, -4
, APP.DISPLAY
, 'Y') = 'Y'
AND PS_INV.ORG_ID = CT.ORG_ID (+)
AND PS_INV.ORG_ID = CTT.ORG_ID (+)
AND PS_INV.ORG_ID = SU.ORG_ID (+)
AND PS_INV.ORG_ID = APP.ORG_ID (+)
AND PS_INV.ORG_ID = CI.ORG_ID (+)
AND CT.ORG_ID = BS.ORG_ID (+) GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, PS_INV.ROWID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.CASH_RECEIPT_ID
, -1)
, PS_INV.CUSTOMER_TRX_ID
, PS_INV.LAST_UPDATE_DATE
, PS_INV.LAST_UPDATED_BY
, PS_INV.CREATION_DATE
, PS_INV.CREATED_BY
, PS_INV.LAST_UPDATE_LOGIN
, PS_INV.PROGRAM_APPLICATION_ID
, PS_INV.PROGRAM_ID
, PS_INV.PROGRAM_UPDATE_DATE
, PS_INV.REQUEST_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.TRX_NUMBER
, NULL)
, PS_INV.CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST.ACCOUNT_NUMBER
, PS_INV.TRX_NUMBER
, PS_INV.TERMS_SEQUENCE_NUMBER
, PS_INV.DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED
, PS_INV.DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED
, PS_INV.AMOUNT_DUE_REMAINING
, PS_INV.DUE_DATE
, PS_INV.STATUS
, PS_INV.TERM_ID
, PS_INV.CLASS
, CTT.NAME
, CTT.CUST_TRX_TYPE_ID
, CT.TRX_DATE
, SU.LOCATION
, PS_INV.CUSTOMER_SITE_USE_ID
, GREATEST(PS_INV.TRX_DATE
, ARP_VIEW_CONSTANTS.GET_APPLY_DATE) /* BUG FIX 3400584*/ /*3079614*/
, ARP_VIEW_CONSTANTS.GET_DEFAULT_GL_DATE( GREATEST(PS_INV.GL_DATE
, ARP_VIEW_CONSTANTS.GET_RECEIPT_GL_DATE
, DECODE(NVL(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT')
, 'INV_REC_DT')
, 'INV_REC_SYS_DT'
, SYSDATE
, 'INV_REC_DT'
, PS_INV.GL_DATE)))
, PS_INV.EXCHANGE_RATE
, PS_INV.INVOICE_CURRENCY_CODE
, PS_INV.AMOUNT_DUE_ORIGINAL
, PS_INV.AMOUNT_IN_DISPUTE
, PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING
, PS_INV.DISCOUNT_REMAINING
, T.CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN
, PS_INV.PAYMENT_SCHEDULE_ID
, CT.DEFAULT_USSGL_TRANSACTION_CODE
, CT.DEFAULT_USSGL_TRX_CODE_CONTEXT
, CT.PURCHASE_ORDER
, CT.DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE
, BS.NAME
, PS_INV.AMOUNT_ADJUSTED
, PS_INV.AMOUNT_ADJUSTED_PENDING
, PS_INV.AMOUNT_LINE_ITEMS_REMAINING
, PS_INV.FREIGHT_ORIGINAL
, PS_INV.FREIGHT_REMAINING
, PS_INV.RECEIVABLES_CHARGES_REMAINING
, PS_INV.TAX_ORIGINAL
, PS_INV.TAX_REMAINING
, PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID))
, CI.CONS_BILLING_NUMBER
, CTT.ATTRIBUTE10
, CT.REASON_CODE
, CT.CT_REFERENCE
, CT.CUSTOMER_REFERENCE
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_REASON)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REFERENCE)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REASON)
, APP.ON_ACCT_CUST_ID
, APP.ON_ACCT_CUST_SITE_USE_ID
, APP.ON_ACCT_PO_NUM HAVING DECODE(PS_INV.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, PS_INV.AMOUNT_DUE_REMAINING) <> 0