VIEW: APPS.OKL_RCPT_FREIGHT_LINES_UV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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