View: FV_INV_STATUSES_V

Product:	FV - Federal Financials
Description:	Stores invoice status; includes all late invoices
Implementation/DBA Data:	APPS.FV_INV_STATUSES_V

View Text

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

Columns