[Home] [Help]
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
Name |
INVOICE_NUMBER |
INVOICE_ID |
INVOICE_LINE_ID |
INVOICE_LINE_NUMBER |
INVOICE_DATE |
INVOICE_DUE_DATE |
INSTALLMENT |
ORG_ID |
CURRENCY_CODE |
LINE_TYPE |
CUSTOMER_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |