[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select records from ap_invoices which is not of type
interest */
CURSOR c1_main_select IS
SELECT api.invoice_id,
api.terms_date,
api.terms_id,
--aid.po_distribution_id,
--aid.rcv_transaction_id,
api.vendor_id,
api.invoice_num,
aps.discount_date,
aps.second_discount_date,
aps.third_discount_date,
api.goods_received_date,
api.invoice_date
FROM
ap_invoices api,
ap_terms apt,
ap_terms_lines apl,
fv_terms_types fvt,
--ap_invoice_distributions aid,
ap_payment_schedules aps
WHERE api.cancelled_amount IS NULL
AND api.invoice_type_lookup_code <> 'INTEREST'
AND ap_invoices_pkg.get_approval_status(
api.invoice_id,
api.invoice_amount,
api.payment_status_flag,
api.invoice_type_lookup_code)='APPROVED'
AND api.wfapproval_status IN ('NOT REQUIRED','MANUALLY APPROVED',
'WFAPPROVED')
AND api.set_of_books_id = v_sob
AND api.payment_status_flag <> 'Y'
AND NOT EXISTS (SELECT 'x' FROM
ap_holds aph
WHERE aph.invoice_id = api.invoice_id
AND aph.release_lookup_code IS NULL)
AND api.terms_id = apt.term_id
AND apt.term_id = fvt.term_id
AND APL.TERM_ID = APT.TERM_ID
AND NVL(apl.due_days,0) > 0
AND terms_type = 'PROMPT PAY'
--AND api.invoice_id = aid.invoice_id
--AND aid.match_status_flag = 'A'
AND 1 = ( SELECT COUNT(*)
FROM ap_payment_schedules aps2
WHERE aps2.invoice_id = api.invoice_id
AND checkrun_id IS NULL) -- modified for bug 5454497
AND 1 = ( SELECT COUNT(*)
FROM ap_terms_lines
WHERE term_id = apt.term_id)
AND api.invoice_id = aps.invoice_id
AND (NOT EXISTS ( SELECT 'x'
FROM fv_inv_selected_duedate fiv
WHERE fiv.invoice_id = api.invoice_id)
or aps.payment_status_flag <> 'Y'); -- added for bug 5454497;
SELECT rcv.transaction_id, aid.rcv_transaction_id,
rcv.parent_transaction_id,
rcv.quantity,
rcv.transaction_type,
rcv.po_line_location_id,
rcv.transaction_date,
rcv.po_header_id,
rcv.shipment_header_id
--pol.quantity_billed
FROM ap_invoice_distributions aid,
rcv_transactions rcv,
po_line_locations pol,
po_distributions po
WHERE aid.invoice_id = p_invoice_id
AND aid.match_status_flag = 'A'
AND po.po_distribution_id = aid.po_distribution_id
AND po.line_location_id = rcv.po_line_location_id
AND po.line_location_id = pol.line_location_id
AND rcv.transaction_type = p_type
AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y' --Bug 7646039
-- AND rcv.transaction_id = NVL(aid.rcv_transaction_id,rcv.transaction_id)
-- and not exists (select 'x' from ap_invoice_distributions aid
-- where aid.rcv_transaction_id = rcv.transaction_id)
ORDER BY rcv.transaction_type,
rcv.transaction_date,
rcv.po_line_location_id;
SELECT DISTINCT transaction_id,transaction_date
FROM rcv_transactions
WHERE
--shipment_header_id=v_shipment_header_id
po_header_id=p_po_header_id
AND transaction_type='ACCEPT'
START WITH transaction_type='RECEIVE'
CONNECT BY parent_transaction_id = PRIOR transaction_id
ORDER BY transaction_date desc;
SELECT 'x'
INTO l_dummy
FROM fv_holiday_dates
WHERE TRUNC(holiday_date) = TRUNC(p_date_out)
AND set_of_books_id = p_sob;
SELECT DISTINCT transaction_id, transaction_date
FROM rcv_transactions
WHERE
--shipment_header_id=p_shipment_header_id
po_header_id=p_po_header_id
AND transaction_type='RECEIVE'
START WITH transaction_type='ACCEPT'
CONNECT BY transaction_id = PRIOR parent_transaction_id
ORDER BY transaction_date desc;
v_invoice_id ap_selected_invoices.invoice_id%TYPE;
v_invoice_num ap_selected_invoices.invoice_num%TYPE;
v_pay_thru_date ap_invoice_selection_criteria.pay_thru_date%TYPE;
-- Delete from Temp table by Org_id
l_operating_unit := MO_GLOBAL.get_current_org_id ;
DELETE FROM fv_inv_selected_duedate_temp
WHERE NVL(set_of_bks_name,-99) = NVL(v_sob_name,-99);
OPEN c1_main_select;
LOOP /* C1_main_select */
<>
FETCH c1_main_select
INTO v_invoice_id,
v_terms_date,
v_terms_id,
--v_po_distribution_id,
--v_rcv_transaction_id,
v_vendor_id,
v_invoice_num,
v_discount_date,
v_second_disc_date,
v_third_disc_date,
v_goods_rec_date,
v_invoice_date;
EXIT WHEN c1_main_select%NOTFOUND;
SELECT due_date
INTO v_org_due_date
FROM ap_payment_schedules
WHERE invoice_id = v_invoice_id;
SELECT due_days,
discount_days,
discount_days_2,
discount_days_3
INTO v_due_days,
v_discount_days,
v_discount_days_2,
v_discount_days_3
FROM AP_TERMS_LINES
WHERE term_id = v_terms_id;
SELECT new_due_date, new_DISCOUNT_DATE,NEW_SECOND_DISC_DATE,NEW_THIRD_DISC_DATE
into v_exists_due_date , v_exists_1_dis_date,v_exists_2_dis_date,v_exists_3_dis_date
FROM fv_inv_selected_duedate fiv
WHERE fiv.invoice_id = v_invoice_id;
SELECT NVL(receipt_acceptance_days,0)
INTO v_receipt_acceptance_days
FROM AP_SYSTEM_PARAMETERS
WHERE set_of_books_id = v_sob;
SELECT constructive_acceptance_days,
invoice_return_days
INTO v_con_acc_days,
v_invoice_return_days
FROM fv_terms_types
WHERE term_id = v_terms_id;
SELECT SUM(quantity_invoiced) INTO v_quantity_billed
FROM ap_invoice_distributions
WHERE invoice_id = v_invoice_id;
SELECT actual_transaction_date
INTO v_transaction_date
FROM fv_rcv_transactions
WHERE transaction_id = v_transaction_id_org;
SELECT NVL(SUM(DECODE(transaction_type ,
'CORRECT',quantity, quantity * -1)),0)
INTO v_correct_quantity
FROM rcv_transactions
WHERE transaction_type IN ('CORRECT','RETURN TO VENDOR')
AND parent_transaction_id = v_transaction_id;
/* selecting latest Qty recevied date using
parent transaction */
IF(v_con_acc_days IS NOT NULL )
AND (v_transaction_type = 'ACCEPT') THEN
--LGOEL: Change where condition for fetching receipt date in 11i
--Cannot use parent_transaction_id directly because that may return the
--'TRANSFER' transaction type
--Fix bug 1425906
OPEN c2_parent_receive(v_po_header_id) ;
SELECT actual_transaction_date
INTO v_rec_transaction_date
FROM fv_rcv_transactions
WHERE transaction_id = v_parent_transaction_id;
SELECT count(*)
INTO v_rcv_trans_count
FROM rcv_transactions acc, rcv_transactions rec
WHERE acc.po_header_id = v_po_header_id
AND acc.transaction_type='ACCEPT'
AND acc.parent_transaction_id = rec.transaction_id
AND rec.transaction_type <> 'RECEIVE';
l_statement := ' SELECT DISTINCT transaction_id,transaction_date
FROM rcv_transactions
WHERE po_header_id = :v_po_header_id
AND transaction_type = ''ACCEPT''
START WITH transaction_type =''RECEIVE''
CONNECT BY parent_transaction_id = PRIOR transaction_id
ORDER BY transaction_date DESC ';
l_statement := ' SELECT DISTINCT acc.transaction_id,acc.transaction_date
FROM rcv_transactions rec, rcv_transactions acc
WHERE rec.po_header_id = :v_po_header_id
AND rec.transaction_type = ''RECEIVE''
AND rec.transaction_id = acc.parent_transaction_id
AND acc.transaction_type = ''ACCEPT''
ORDER BY acc.transaction_date DESC ';
SELECT actual_transaction_date
INTO v_transaction_date
FROM fv_rcv_transactions
WHERE transaction_id = v_transaction_id;
SELECT COUNT(*)
INTO v_tot_inv_retn
FROM fv_invoice_returns
WHERE invoice_id = v_invoice_id;
SELECT COUNT(*)
INTO v_tot_inv_retn
FROM fv_invoice_return_dates
WHERE invoice_returned_date > (original_invoice_received_date
+ v_invoice_return_days)
AND invoice_id = v_invoice_id;
SELECT SUM(invoice_returned_date -
original_invoice_received_date -
v_invoice_return_days)
INTO v_diff_days
FROM fv_invoice_return_dates
WHERE (invoice_returned_date -
original_invoice_received_date)
> v_invoice_return_days
AND invoice_id = v_invoice_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DUE DATE UPDATE FLAG ' || V_DUE_DATE_FLAG);
--FND_FILE.PUT_LINE(FND_FILE.LOG,'due date update flag ' || v_due_date_flag);
INSERT INTO fv_inv_selected_duedate
(INVOICE_ID,
INVOICE_NUM,
TERMS_DATE ,
ORG_DUE_DATE,
NEW_DUE_DATE,
VENDOR_ID,
SET_OF_BKS_NAME,
ORG_DISCOUNT_DATE,
NEW_DISCOUNT_DATE,
ORG_second_disc_date,
new_second_disc_date,
org_third_disc_date,
new_third_disc_date,
transaction_id,
transaction_date,
po_distribution_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
set_of_books_id)
VALUES
(v_invoice_id,
v_invoice_num,
v_terms_date,
v_ORG_DUE_DATE,
DECODE(v_due_date_flag,'Y',v_total_due_date,NULL),
v_vendor_id,
v_sob_name,
DECODE(v_disc_date_flag,'Y',v_DISCOUNT_DATE,NULL),
DECODE(v_disc_date_flag,'Y',v_NEW_DISCOUNT_DATE,NULL),
DECODE(v_2_disc_date_flag,'Y',v_second_DISC_DATE,NULL),
DECODE(v_2_disc_date_flag,'Y',v_NEW_second_DISC_DATE,NULL),
DECODE(v_3_disc_date_flag,'Y',v_third_DISC_DATE,NULL),
DECODE(v_3_disc_date_flag,'Y',v_NEW_third_DISC_DATE,NULL),
v_transaction_id,
v_transaction_date,
v_po_distribution_id,
fnd_global.user_id,
SYSDATE,
SYSDATE,
fnd_global.user_id ,
v_sob);
/* If invoice exist update the duedate,this will happen
if an invoice have more then one distributions */
UPDATE fv_inv_selected_duedate
SET NEW_DUE_DATE = DECODE(v_due_date_flag,'Y',
v_total_due_date,NULL)
WHERE invoice_id = v_invoice_id;
UPDATE ap_payment_schedules
SET due_date = DECODE(v_due_date_flag,'Y', v_total_due_date,
due_date), discount_date = DECODE(v_disc_date_flag,
'Y',v_new_discount_date,discount_date),
second_discount_date = decode(v_2_disc_date_flag,'Y',
v_new_second_disc_date,second_discount_date),
third_discount_date = decode(v_3_disc_date_flag,'Y',
v_new_third_disc_date,third_discount_date),
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE invoice_id = v_invoice_id;
INSERT INTO fv_inv_selected_duedate_temp
(INVOICE_ID,
INVOICE_NUM,
TERMS_DATE ,
ORG_DUE_DATE,
NEW_DUE_DATE,
VENDOR_ID,
SET_OF_BKS_NAME,
ORG_DISCOUNT_DATE,
NEW_DISCOUNT_DATE,
ORG_second_disc_date,
new_second_disc_date,
org_third_disc_date,
new_third_disc_date,
SET_OF_BOOKS_ID)
VALUES
(v_invoice_id,
v_invoice_num,
v_terms_date,
v_ORG_DUE_DATE,
v_total_due_date,
v_vendor_id,
v_sob_name,
v_DISCOUNT_DATE,
v_NEW_DISCOUNT_DATE,
v_second_DISC_DATE,
v_NEW_second_DISC_DATE,
v_third_DISC_DATE,
v_NEW_third_DISC_DATE,
v_sob );
/* If invoice exist update the duedate,this will happen
if an invoice have more then one distributions */
WHEN DUP_VAL_ON_INDEX THEN
UPDATE fv_inv_selected_duedate_temp
SET NEW_DUE_DATE = DECODE(v_due_date_flag,'Y',
v_total_due_date ,NULL)
WHERE invoice_id = v_invoice_id;
END LOOP; /* C1_MAIN_SELECT */
errbuf := 'Update failed in ap_payment_schedules';
errbuf := 'Insert failed in Fv_inv_selected_duedate_temp';
errbuf := 'Main_select' || SQLERRM;
IF (c1_main_select%ISOPEN) THEN
CLOSE c1_main_select;