[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'Y'
Into l_invoice_exists
From ap_invoice_distributions aid,
po_distributions pd,
po_line_locations pll
where aid.po_distribution_id = pd.po_distribution_id
and pd.line_location_id = pll.line_location_id
and pll.line_location_id = l_po_shipment_id
and nvl(aid.rcv_transaction_id,-1) =
nvl(DECODE(l_match_option,'P',NULL,p_rcv_txn_id),-1)
and rownum=1;
SELECT 'Y'
INTO l_invoice_exists
FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id = p_po_dist_id
AND nvl(aid.rcv_transaction_id,-1) =
nvl(DECODE(l_match_option,'P',NULL,p_rcv_txn_id),-1)
AND rownum = 1;
SELECT
set_of_books_id,
base_currency_code,
gl_date_from_receipt_flag,
auto_tax_calc_flag -- bug fix 1971188
INTO
l_set_of_books_id,
l_base_currency_code,
l_gl_date_from_rect_flag,
l_auto_tax_calc_flag_sys
FROM ap_system_parameters
WHERE org_id = l_org_id ;
SELECT NVL(fnd1.derive_type,'OTHER'),NVL(fnd2.derive_type,'OTHER')
INTO l_derive_type_rcv_curr,l_derive_type_payment_curr
FROM fnd_currencies fnd1,fnd_currencies fnd2
WHERE fnd1.currency_code = l_rcv_currency_code
AND fnd2.currency_code = l_payment_currency;
select vendor_id,vendor_name
into l_remit_to_supplier_id,l_remit_to_supplier_name
from ap_suppliers
where party_id = l_remit_party_id;
select party_site_id,vendor_site_code
into l_remit_party_site_id,l_remit_to_supplier_site
from ap_supplier_sites_all
where vendor_site_id = l_remit_to_supplier_site_id;
must select value of the flag from vendor site info
Following standar behavior. */
IF l_auto_tax_calc_flag_sys = 'N' THEN
l_auto_tax_calc_flag := 'N';
SELECT invoice_amount
INTO l_invoice_amount
FROM ap_invoices
WHERE invoice_id = l_invoice_id;
p_last_updated_by =>p_user_id,
p_created_by =>p_user_id,
p_payment_priority =>l_payment_priority,
p_batch_id =>l_batch_id,
p_terms_date =>l_terms_date,
p_invoice_amount =>l_amount,
p_amount_for_discount =>l_amount,
p_payment_method =>l_payment_method_code, --4552701
p_invoice_currency =>l_rcv_currency_code,
p_payment_currency =>l_inv_payment_curr,
p_pay_curr_invoice_amount =>nvl(l_pay_curr_invoice_amount,l_amount),
p_payment_cross_rate => nvl(l_pay_cross_rate,1),
p_calling_sequence => curr_calling_sequence);
SELECT
rtxn.vendor_id,
rtxn.vendor_site_id,
rtxn.currency_code,
rtxn.po_line_location_id,
rtxn.transaction_date,
decode (rtxn.currency_conversion_type, null, null,
rtxn.currency_conversion_rate),
rtxn.currency_conversion_date,
rtxn.currency_conversion_type,
rsh.receipt_num,
rsl.unit_of_measure,
nvl( nvl(pll.terms_id,ph.terms_id),pvs.terms_id),
/* Bug fix: 1413309 added the pll.quantity_billed to the clause */
nvl(rtxn.quantity_billed,nvl(pll.quantity_billed,0)),
pl.item_description,
pll.match_option,
pll.org_id,
pl.unit_meas_lookup_code
INTO
p_vendor_id,
p_vendor_site_id,
p_rcv_currency_code,
p_po_shipment_id,
p_rcv_txn_date,
p_rcv_rate,
p_rcv_rate_date,
p_rcv_rate_type,
p_receipt_num,
p_receipt_uom,
p_po_pay_terms_id,
p_quantity_billed,
p_item_description,
p_match_option, --bug2902340
p_org_id,
p_po_uom
FROM
rcv_transactions rtxn,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_headers ph,
po_line_locations pll,
po_lines pl,
po_vendor_sites pvs
WHERE rtxn.transaction_id = p_rcv_txn_id and
rtxn.shipment_line_id = rsl.shipment_line_id and
rsl.shipment_header_id = rsh.shipment_header_id and
rtxn.po_line_location_id = pll.line_location_id and
pll.po_line_id = pl.po_line_id and
pl.po_header_id = ph.po_header_id and
rtxn.vendor_site_id = pvs.vendor_site_id and
--Bug fix:2662505 Consigned Inventory for Supplier Project Impact
--Debit memo should not be created for RTS done on the receipt
--of a shipment,which has the consigned_flag set to Y.
nvl(pll.consigned_flag,'N') <> 'Y';
debug_info := 'Select information from RTS transaction';
SELECT transaction_date
INTO p_rts_txn_date
FROM rcv_transactions
WHERE transaction_id = p_rts_txn_id;
SELECT code_combination_id
INTO p_po_ccid
FROM po_distributions_ap_v
WHERE po_distribution_id = p_po_dist_id;
/* Bug 2226808 select the payment information based on the Alternative (default)
Payment site if it exists, otherwise, select the information based on the
Purchasing site.
Add the following SELECT and use l_default_pay_site_id in the WHERE for the
second SELECT instead of p_vendor_sit_id */
SELECT NVL(pvs.default_pay_site_id,pvs.vendor_site_id)
INTO l_default_pay_site_id
FROM po_vendor_sites pvs
WHERE vendor_site_id = p_vendor_site_id;
SELECT
nvl(pvs.pay_group_lookup_code, pv.pay_group_lookup_code),
pvs.accts_pay_code_combination_id,
pvs.payment_priority,
pvs.terms_date_basis,
pvs.state,
pv.type_1099,
pvs.allow_awt_flag,
pvs.awt_group_id,
pvs.exclude_freight_from_discount,
nvl(pvs.payment_currency_code, pvs.invoice_currency_code),
pvs.auto_tax_calc_flag, -- Bug fix 1971188
pv.party_id,
pvs.party_site_id
INTO
p_pay_group_lookup_code,
p_accts_pay_ccid,
p_payment_priority,
p_terms_date_basis,
p_vendor_income_tax_region,
p_type_1099,
p_allow_awt_flag,
p_awt_group_id,
p_excl_freight_from_disc,
p_payment_currency,
p_auto_tax_calc_flag,
p_party_id,
p_party_site_id
FROM po_vendors pv,
ap_supplier_sites pvs
WHERE pvs.vendor_site_id = l_default_pay_site_id and
pv.vendor_id = pvs.vendor_id;
debug_info := 'Insert into ap_batches';
SELECT ap_batches_s.nextval INTO p_batch_id FROM dual;
SELECT 'Y'
INTO l_invoice_exists
FROM ap_invoice_distributions aid,
rcv_shipment_headers rsh,
rcv_transactions rct,
po_distributions pod
WHERE aid.po_distribution_id = pod.po_distribution_id
AND pod.line_location_id = rct.po_line_location_id
AND rsh.shipment_header_id = rct.shipment_header_id
AND rsh.receipt_num = p_receipt_num
AND rownum = 1;
INSERT INTO ap_batches_all (
batch_id,
batch_name,
batch_date,
invoice_currency_code,
payment_currency_code,
invoice_type_lookup_code,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login)
VALUES (
p_batch_id,
p_receipt_num||'-'||ap_batches_s1.nextval,
trunc(p_rts_txn_date), --Bug 3492081
p_inv_curr,
p_inv_payment_curr,
'DEBIT',
p_user_id,
sysdate,
p_user_id,
sysdate,
p_login_id);
SELECT default_pay_site_id
INTO l_alter_pay_site_id
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id;
select 'y'
into l_valid_pay_site
from po_vendor_sites
where vendor_site_id = l_alter_pay_site_id
and pay_site_flag = 'Y'
and nvl(inactive_date, sysdate +1) > sysdate;
SELECT 'y'
INTO l_valid_pay_site
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id
AND pay_site_flag = 'Y'
AND nvl(inactive_date, sysdate +1) > sysdate;
SELECT p_receipt_num||'-'||AP_INVOICES_S1.nextval
INTO l_invoice_num
FROM dual;
SELECT SEQ.db_sequence_name,
SEQ.doc_sequence_id
INTO l_db_seq_name,
l_db_seq_id
FROM fnd_document_sequences SEQ,
fnd_doc_sequence_assignments SA
WHERE SEQ.doc_sequence_id = SA.doc_sequence_id
AND SA.application_id = 200
AND SA.category_code = 'DBM INV'
AND SA.method_code = 'A'
AND SA.set_of_books_id = p_set_of_books_id
AND trunc(p_rts_txn_date) between
SA.start_date and nvl(SA.end_date, trunc(p_rts_txn_date));
SELECT ap_invoices_s.nextval INTO l_invoice_id FROM dual;
debug_info := 'Inserting row in ap_invoices';
INSERT INTO ap_invoices_all (
org_id,
invoice_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
vendor_id,
invoice_num,
set_of_books_id,
invoice_currency_code,
payment_currency_code,
payment_cross_rate,
invoice_amount,
vendor_site_id,
invoice_date,
source,
invoice_type_lookup_code,
description,
batch_id,
amount_applicable_to_discount,
terms_id,
terms_date,
payment_method_code,
pay_group_lookup_code,
accts_pay_code_combination_id,
payment_status_flag,
base_amount,
exclusive_payment_flag,
goods_received_date,
invoice_received_date,
approved_amount,
exchange_rate,
exchange_rate_type,
exchange_date,
doc_sequence_id,
doc_sequence_value,
doc_category_code,
payment_cross_rate_type,
payment_cross_rate_date,
pay_curr_invoice_amount,
awt_flag,
awt_group_id,
gl_date,
approval_ready_flag, -- Bug 2345472
wfapproval_status, -- Bug 2345472
auto_tax_calc_flag, -- Bug fix : 1971188.
PAYMENT_REASON_CODE,
BANK_CHARGE_BEARER,
DELIVERY_CHANNEL_CODE,
SETTLEMENT_PRIORITY,
external_bank_account_id,
legal_entity_id,
party_id,
party_site_id,
payment_reason_comments, --4874927
remit_to_supplier_name, --Start 7758980
remit_to_supplier_id,
remit_to_supplier_site,
remit_to_supplier_site_id,
relationship_id ) --End 7758980
VALUES (
p_org_id,
l_invoice_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_vendor_id,
l_invoice_num,
p_set_of_books_id,
p_invoice_curr,
p_inv_pay_curr,
nvl(l_pay_cross_rate,1),
l_invoice_amount,
l_vendor_site_id,
trunc(p_rts_txn_date), --Bug 3492081
'RTS',
'DEBIT',
l_inv_desc,
p_batch_id,
l_invoice_amount,
p_terms_id,
l_terms_date,
p_payment_method,
p_pay_group,
p_accts_pay_ccid,
'N',
l_inv_base_amt,
--Bug 5583430. For a debit memo, the pay alone flag should be set to 'N'
--and not being populated based on supplier site.
--p_excl_pay_flag,
'N',
p_rts_txn_date,
sysdate,
0,
p_rcv_rate,
p_rcv_rate_type,
p_rcv_rate_date,
l_db_seq_id,
l_doc_seq_value,
'DBM INV',
l_pay_cross_rate_type,
l_pay_cross_rate_date,
l_pay_curr_invoice_amount,
'N',
p_awt_group_id,
l_inv_gl_date,
'Y', --Bug 2345472
'NOT REQUIRED', --Bug 2345472
p_auto_tax_calc_flag,
p_PAYMENT_REASON_CODE,
p_BANK_CHARGE_BEARER,
p_DELIVERY_CHANNEL_CODE,
p_SETTLEMENT_PRIORITY,
p_external_bank_account_id,
p_le_id,
p_party_id,
p_party_site_id,
p_payment_reason_comments, --4874927
p_remit_to_supplier_name, --Start 7758980
p_remit_to_supplier_id,
p_remit_to_supplier_site,
p_remit_to_supplier_site_id,
p_relationship_id ); --End 7758980
and if tax lines are created then updates invoice_amount
appropriately.
---------------------------------------------------------------------------*/
FUNCTION create_dm_tax (p_invoice_id IN NUMBER,
p_invoice_amount IN NUMBER,
p_error_code OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
l_lines_total ap_invoice_lines_all.amount%type;
SELECT nvl(sum(amount),0),nvl(sum(base_amount),0)
INTO l_lines_total,l_lines_total_base_amount
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
UPDATE ap_invoices
SET invoice_amount = l_lines_total,
base_amount = l_lines_total_base_amount
WHERE invoice_id = p_invoice_id;