VIEW: APPS.FV_INV_STATUSES_V

Source

View Text - Preformatted

SELECT fct.org_id, hca.cust_account_id customer_id, hp.party_name customer_name, hca.account_number customer_number, rct.customer_trx_id, rct.trx_number, fvt.receivable_type_id, fvc.description, sum(aps.amount_due_remaining) amount_due_remaining, min(trunc(sysdate) - aps.due_date) days_late FROM ra_customer_trx_all rct, hz_cust_accounts hca,hz_parties hp , ar_payment_schedules_all aps, fv_rec_cust_trx_types_all fct, fv_receivable_types fvt, fv_lookup_codes fvc WHERE rct.org_id=fct.org_id and hp.party_id = hca.party_id and aps.customer_trx_id = rct.customer_trx_id and rct.bill_to_customer_id = hca.cust_account_id and rct.cust_trx_type_id = fct.cust_trx_type_id and fct.receivable_type_id = fvt.receivable_type_id and fvt.receivable_type = fvc.lookup_code and fvc.lookup_type = 'RECEIVABLE_TYPE' and trunc(sysdate) > aps.due_date and aps.amount_due_remaining > 0 and rct.created_from not in (select charge_type from fv_finance_charge_controls) group by fct.org_id,hca.cust_account_id , hp.party_name , hca.account_number , rct.customer_trx_id, rct.trx_number, fvc.description, fvt.receivable_type_id

View Text - HTML Formatted

SELECT FCT.ORG_ID
, HCA.CUST_ACCOUNT_ID CUSTOMER_ID
, HP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, RCT.CUSTOMER_TRX_ID
, RCT.TRX_NUMBER
, FVT.RECEIVABLE_TYPE_ID
, FVC.DESCRIPTION
, SUM(APS.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, MIN(TRUNC(SYSDATE) - APS.DUE_DATE) DAYS_LATE
FROM RA_CUSTOMER_TRX_ALL RCT
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, AR_PAYMENT_SCHEDULES_ALL APS
, FV_REC_CUST_TRX_TYPES_ALL FCT
, FV_RECEIVABLE_TYPES FVT
, FV_LOOKUP_CODES FVC
WHERE RCT.ORG_ID=FCT.ORG_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND RCT.CUST_TRX_TYPE_ID = FCT.CUST_TRX_TYPE_ID
AND FCT.RECEIVABLE_TYPE_ID = FVT.RECEIVABLE_TYPE_ID
AND FVT.RECEIVABLE_TYPE = FVC.LOOKUP_CODE
AND FVC.LOOKUP_TYPE = 'RECEIVABLE_TYPE'
AND TRUNC(SYSDATE) > APS.DUE_DATE
AND APS.AMOUNT_DUE_REMAINING > 0
AND RCT.CREATED_FROM NOT IN (SELECT CHARGE_TYPE
FROM FV_FINANCE_CHARGE_CONTROLS) GROUP BY FCT.ORG_ID
, HCA.CUST_ACCOUNT_ID
, HP.PARTY_NAME
, HCA.ACCOUNT_NUMBER
, RCT.CUSTOMER_TRX_ID
, RCT.TRX_NUMBER
, FVC.DESCRIPTION
, FVT.RECEIVABLE_TYPE_ID