[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Delete from fv_prompt_pay_temp ;
select currency_code
into l_currency_code
from gl_ledgers_public_v
where ledger_id = v_set_of_books_id ;
Insert Into fv_prompt_pay_temp
( invoice_id ,
pay_invoice_id,
pay_payment_number ,
pay_due_date ,
discount_amount_available ,
invoice_payment_id ,
discount_taken ,
check_date ,
invoice_amount,
invoice_type_lookup_code)
Select
A.invoice_id,
S.invoice_id ,
S.payment_num ,
S.due_date ,
S.discount_amount_available,
P.invoice_payment_id ,
nvl(P.discount_taken,0.00) ,
K.check_date ,
A.INVOICE_AMOUNT ,
A.Invoice_type_lookup_code
FROM
fv_terms_types T,
ap_payment_schedules S,
ap_invoice_payments P,
ap_checks K,
ap_invoices A
WHERE
A.set_of_books_id = v_set_of_books_id
AND A.payment_status_flag = 'Y'
AND T.term_id = A.terms_id
AND T.terms_type = 'PROMPT PAY'
AND S.invoice_id = A.invoice_id
AND S.due_date is not null
AND S.due_date =
( SELECT max(U.due_date)
FROM ap_payment_schedules U
WHERE U.invoice_id = S.invoice_id)
AND P.invoice_id = S.invoice_id
AND P.payment_num = S.payment_num
AND P.invoice_payment_id =
( SELECT I.invoice_payment_id
FROM ap_invoice_payments I,
ap_checks C
WHERE I.invoice_payment_id = P.invoice_payment_id
AND C.check_id = P.check_id
AND C.check_date =
( SELECT max(H.check_date)
FROM ap_checks H
WHERE H.check_id = C.check_id ))
AND K.check_id = P.check_id
AND K.check_date between v_from_date and v_to_date
AND K.void_date is null ;