View: OKL_RCPT_FREIGHT_LINES_UV

Product:	OKL - Lease and Finance Management
Description:	This view will fetch the open invoices whose freight and charges remaining are greater than zero.
Implementation/DBA Data:	APPS.OKL_RCPT_FREIGHT_LINES_UV

View Text

SELECT RA_CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, RA_CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, NULL INVOICE_LINE_ID
, NULL INVOICE_LINE_NUMBER
, RA_CUST_TRX.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'FREIGHT' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, PAY_SCH.AMOUNT_DUE_ORIGINAL
, (SELECT SUM(PAY_SCH_B.FREIGHT_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH_B
WHERE PAY_SCH_B.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID) AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND PAY_SCH.STATUS='OP'
AND PAY_SCH.CLASS='INV'
AND PAY_SCH.FREIGHT_REMAINING >0 UNION ALL SELECT RA_CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, RA_CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, NULL INVOICE_LINE_ID
, NULL INVOICE_LINE_NUMBER
, RA_CUST_TRX.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'CHARGES' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, PAY_SCH.AMOUNT_DUE_ORIGINAL
, (SELECT SUM(PAY_SCH_C.RECEIVABLES_CHARGES_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH_C
WHERE PAY_SCH_C.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID) AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND PAY_SCH.STATUS='OP'
AND PAY_SCH.CLASS='INV'
AND PAY_SCH.RECEIVABLES_CHARGES_REMAINING >0

Columns