[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ship_to_location_id INTO l_ship_to_location_id
FROM
po_line_locations_all
WHERE
line_location_id = p_po_line_location_id;
SELECT
input_tax_classification_code INTO l_tax_classification_code
FROM
zx_lines_det_factors
WHERE
trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND entity_code = p_entity_code;
SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
rts.rowid rcv_txn_rowid,
rts.transaction_id,
rts.po_header_id,
rts.po_release_id,
rts.po_line_id,
rts.po_line_location_id,
rts.po_distribution_id,
rsh.vendor_id,
pvds.segment1 vendor_num,
NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
pvss2.vendor_site_code pay_site_code,
pvss.pay_on_receipt_summary_code, -- default pay site's summary code
rts.shipment_header_id,
/* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
Also changed the alias packing_slip to pack_slip to avoid ambiguous
column error in the order by clause.*/
NVL(rsh.packing_slip, nvl(rsl.packing_slip,rsh.receipt_num)) pack_slip,
rsh.receipt_num,
rts.shipment_line_id,
rts.transaction_date,
rts.amount,
rts.quantity,
nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug: 4409887 */
rts.currency_code,
rts.currency_conversion_type,
/* Note that we must decode currency type because the receiving programs put in
a 1 for the currency rate if base currency is same as PO. Purchasing and
Payables expects that the rate be null if base currency=PO/Invoice currency. */
decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
rts.currency_conversion_date,
NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
pls.item_description,
plls.matching_basis,
decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD') shipment_type,
NVL(rsl.osa_flag,'N') osa_flag, --Shikyu project
plls.match_option, -- 5100177
rts.unit_of_measure, -- 5100177
plls.unit_meas_lookup_code -- 5100177
FROM po_vendor_sites pvss,
po_vendor_sites pvss2,
po_vendors pvds,
--Bugfix 5407632 - Using _all tables instead of views.
po_headers_all phs,
-- po_releases_all prs, /*Bug 5443196*/
po_lines_all pls,
po_line_locations_all plls,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rts
WHERE rts.shipment_header_id = rsh.shipment_header_id
AND rts.po_header_id = phs.po_header_id
--AND rts.po_release_id = prs.po_release_id(+) /*Bug 5443196*/
AND rts.po_line_location_id = plls.line_location_id
AND rts.po_line_id = pls.po_line_id
AND rts.shipment_header_id = rsl.shipment_header_id
AND rts.shipment_line_id = rsl.shipment_line_id
AND phs.vendor_id = pvds.vendor_id
AND phs.vendor_site_id = pvss.vendor_site_id
AND phs.pcard_id is null
AND rsh.receipt_source_code = 'VENDOR'
AND rts.source_document_code = 'PO'
AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED') /*Bug:551612 */ --Bug#6649580
AND rts.transaction_type = X_receipt_event
/* */
AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
AND PHS.PAY_ON_CODE IN ('RECEIPT', 'RECEIPT_AND_USE') /*Bug 5443196*/
/*AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')*/
AND NVL(plls.consigned_flag,'N') <> 'Y'
/* */
AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
AND nvl(rsh.asn_type, ' ') <> 'ASBN'
AND rts.transaction_date <= sysdate - l_aging_period
AND rts.po_release_id IS null -- Bug 5443196
AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
UNION
SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
rts.rowid rcv_txn_rowid,
rts.transaction_id,
rts.po_header_id,
rts.po_release_id,
rts.po_line_id,
rts.po_line_location_id,
rts.po_distribution_id,
rsh.vendor_id,
pvds.segment1 vendor_num,
NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
pvss2.vendor_site_code pay_site_code,
pvss.pay_on_receipt_summary_code, -- default pay site's summary code
rts.shipment_header_id,
/* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
Also changed the alias packing_slip to pack_slip to avoid ambiguous
column error in the order by clause.*/
NVL(rsh.packing_slip, nvl(rsl.packing_slip,rsh.receipt_num)) pack_slip,
rsh.receipt_num,
rts.shipment_line_id,
rts.transaction_date,
rts.amount,
rts.quantity,
nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug: 4409887 */
rts.currency_code,
rts.currency_conversion_type,
/* Note that we must decode currency type because the receiving programs put in
a 1 for the currency rate if base currency is same as PO. Purchasing and
Payables expects that the rate be null if base currency=PO/Invoice currency. */
decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
rts.currency_conversion_date,
NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
pls.item_description,
plls.matching_basis,
decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD') shipment_type,
NVL(rsl.osa_flag,'N') osa_flag, --Shikyu project
plls.match_option, -- 5100177
rts.unit_of_measure, -- 5100177
plls.unit_meas_lookup_code -- 5100177
FROM po_vendor_sites pvss,
po_vendor_sites pvss2,
po_vendors pvds,
--Bugfix 5407632 - Using _all tables instead of views.
po_headers_all phs,
po_releases_all prs,
po_lines_all pls,
po_line_locations_all plls,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rts
WHERE rts.shipment_header_id = rsh.shipment_header_id
AND rts.po_header_id = phs.po_header_id
AND rts.po_release_id = prs.po_release_id
AND rts.po_line_location_id = plls.line_location_id
AND rts.po_line_id = pls.po_line_id
AND rts.shipment_header_id = rsl.shipment_header_id
AND rts.shipment_line_id = rsl.shipment_line_id
AND phs.vendor_id = pvds.vendor_id
AND phs.vendor_site_id = pvss.vendor_site_id
AND phs.pcard_id is null
AND rsh.receipt_source_code = 'VENDOR'
AND rts.source_document_code = 'PO'
AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED') /*Bug:551612 */ --Bug#6649580
AND rts.transaction_type = X_receipt_event
/* */
AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
AND PRS.PAY_ON_CODE IN ('RECEIPT', 'RECEIPT_AND_USE') /*Bug 5443196*/
/*AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')*/
AND NVL(plls.consigned_flag,'N') <> 'Y'
/* */
AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
AND nvl(rsh.asn_type, ' ') <> 'ASBN'
AND rts.transaction_date <= sysdate - l_aging_period
AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
ORDER BY 8,10,21,25,17,14,29,13,30; --Bug 5443196*/
SELECT rts.rowid rcv_txn_rowid,
rts.transaction_id,
rts.po_header_id,
rts.po_release_id,
rts.po_line_id,
rts.po_line_location_id,
rts.po_distribution_id,
rsh.vendor_id,
pvds.segment1 vendor_num,
NVL(pvss.default_pay_site_id,pvss.vendor_site_id) default_pay_site_id,
pvss2.vendor_site_code pay_site_code,
pvss.pay_on_receipt_summary_code, -- default pay site's summary code
rts.shipment_header_id,
/* Bug 3065403 - Taking rsl.packing slip if rsh.packing slip is null.
Also changed the alias packing_slip to pack_slip to avoid ambiguous
column error in the order by clause*/
NVL(rsh.packing_slip,nvl(rsl.packing_slip, rsh.receipt_num)) pack_slip,
rsh.receipt_num,
rts.shipment_line_id,
rts.transaction_date,
rts.amount,
rts.quantity,
nvl(plls.price_override, pls.unit_price) po_unit_price, /* Bug4409887 */
rts.currency_code,
rts.currency_conversion_type,
/* Note that we must decode currency type because the receiving programs put in
a 1 for the currency rate if base currency is same as PO. Purchasing and
Payables expects that the rate be null if base currency=PO/Invoice currency. */
decode (rts.currency_conversion_type,null,null,rts.currency_conversion_rate) currency_conversion_rate,
rts.currency_conversion_date,
NVL(NVL(plls.terms_id, phs.terms_id) , pvss2.terms_id) payment_terms_id,
DECODE(plls.taxable_flag, 'Y', plls.tax_code_id, NULL) tax_code_id,
pls.item_description,
plls.matching_basis,
decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD')shipment_type,
rsl.osa_flag, --Shikyu project
plls.match_option, -- 5100177
rts.unit_of_measure, -- 5100177
plls.unit_meas_lookup_code -- 5100177
FROM po_vendor_sites pvss,
po_vendor_sites pvss2,
po_vendors pvds,
po_headers phs,
po_releases prs,
po_lines pls,
po_line_locations plls,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rts
WHERE rts.shipment_header_id = rsh.shipment_header_id
AND rts.po_header_id = phs.po_header_id
AND rts.po_release_id = prs.po_release_id(+)
AND rts.po_line_location_id = plls.line_location_id
AND rts.po_line_id = pls.po_line_id
AND rts.shipment_header_id = rsl.shipment_header_id
AND rts.shipment_line_id = rsl.shipment_line_id
AND phs.vendor_id = pvds.vendor_id
AND phs.vendor_site_id = pvss.vendor_site_id
AND phs.pcard_id is null
AND rsh.receipt_source_code = 'VENDOR'
AND rts.source_document_code = 'PO'
AND rts.invoice_status_code IN ('PENDING','REJECTED') /*Bug:551612 */
AND rts.transaction_type = X_receipt_event
/* */
AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')
AND decode(nvl(rts.po_release_id, -999), -999, phs.pay_on_code,
prs.pay_on_code) IN ('RECEIPT', 'RECEIPT_AND_USE')
AND NVL(plls.consigned_flag, 'N') <> 'Y'
/* */
AND pvss2.vendor_site_id = NVL(pvss.default_pay_site_id,pvss.vendor_site_id)
AND rsh.shipment_header_id = X_rcv_shipment_header_id
AND nvl(rsh.asn_type, ' ') <> 'ASBN'
AND rts.transaction_date <= sysdate - l_aging_period
AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
ORDER BY
phs.vendor_id,
NVL(pvss.default_pay_site_id,pvss.vendor_site_id),
rts.currency_code,
payment_terms_id,
rts.transaction_date,
pack_slip,
decode(plls.shipment_type,'PREPAYMENT','PREPAYMENT','STANDARD'),
rsh.shipment_header_id, --Bug 6822389
rsl.osa_flag; --Shikyu project
/** this is the group id we insert into the
AP interface table to identify out batch **/
X_group_id VARCHAR2(80);
select org_id
into x_org_id
from po_headers_all
where po_header_id = X_rcv_txns.po_header_id;
select base_currency_code
into X_def_base_currency_code
from ap_system_parameters;
select ap_invoices_interface_s.nextval
into x_curr_invoice_id
from sys.dual;
If so, we would first update the current invoice -- invoice amount, etc.
create payment schedule for the invoice and then
get ready to create a new invoice. ***/
/* Bug 586895 */
/* Bug 2536170 - We consider the transaction date also for
creating new invoice as it determines the conversion rate
between the purchasing currency and invoice currency.But when
the transaction date remaining the same except for the timestamp
we were creating a new invoice. This should not be the case.
So added a trunc on the date comparisons so that all the transactions
that have the same transaction date except for the timestamp will
have a single invoice provided these transactions can be grouped by
the invoice summary level(pay_on_summary_code). Also removed the
AND condition added in fix 1703833 as there will conversion issues
if we don't consider transaction dates for pay sites also.
*/
/* Bug 1703833. If the receipt_date is different, then we create
* multiple invoices even if the pay_on_receipt_summary_code is
* PAY_SITE. Changed the code below to include the condition
* that if transaction_date is not the same and the summary code
* is not PAY_SITE, then go inside the if clause.
*/
/* Bug 2531542 - The logic followed for creating invoices is to
insert records into ap_lines_interface first (distributions)
and then insert the records in ap_invoices_interface(Headers)
so the amount will be the total distribution amount.
For bug fix 1762305 , if the net received quantity is 0 then
distribuitions lines were not inserted. But the records were
inserted for the headers even for the received qty of 0.
Because of this Payables import program was erroring out with
'Atleast one invoice line is needed' error message. So
checking for the distribiution count before inserting the headers
and inserting only if the distribution count is >0. */
/* 3065403 - Changed packing slip to pack slip as the alias name is
changed in the cursor. */
/* R12 Complex Work.
* Compare shipment_types and if they are different then create
* a new invoice. Here we will have Standard for all the other
* shipment_types other than prepayment as we want to group
* them together.
*/
IF (X_curr_vendor_id <> X_rcv_txns.vendor_id) OR
(X_curr_pay_site_id <> X_rcv_txns.default_pay_site_id) OR
(X_curr_currency_code <> X_rcv_txns.currency_code) OR
(X_curr_payment_terms_id <> X_rcv_txns.payment_terms_id) OR
(trunc(X_curr_transaction_date) <> trunc(X_rcv_txns.transaction_date)) OR
(X_curr_packing_slip <> X_rcv_txns.pack_slip AND
X_rcv_txns.pay_on_receipt_summary_code = 'PACKING_SLIP') OR
(X_curr_shipment_header_id <> X_rcv_txns.shipment_header_id AND
X_rcv_txns.pay_on_receipt_summary_code = 'RECEIPT') OR
(X_curr_method_code <> X_payment_method_lookup_code) OR
(X_curr_shipment_type <> x_rcv_txns.shipment_type) OR--Complex Work
(X_curr_osa_flag <> X_rcv_txns.osa_flag) --Shikyu project
THEN
/* 2531542 */
select count(*) into x_dist_count
from ap_invoice_lines_interface
where invoice_id = x_curr_invoice_id;
select base_currency_code
into X_def_base_currency_code
from ap_system_parameters;
update the current one before the new one can be created. ***/
X_progress := '090';
/** update invoice amounts and also running totals.
Also create payment schedules ***/
/*Bug 5382916: Date in the description should be in LE Time zone*/
fnd_message.set_name('PO', 'PO_INV_CR_ERS_INVOICE_DESC');
/* bug 1832024 : we need to insert terms id into the interface table
so that ap get the value */
if (x_curr_inv_process_flag = 'Y') THEN
if (x_dist_count > 0 ) then -- 2531542
asn_debug.put_line('x_curr_pay_site_id='||x_curr_pay_site_id||' and X_rcv_txns.default_pay_site_id='||X_rcv_txns.default_pay_site_id);
insert into AP_INVOICES_INTERFACE
(INVOICE_ID,
INVOICE_NUM,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
INVOICE_DATE,
SOURCE,
DESCRIPTION,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
CREATION_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
GROUP_ID,
ORG_ID, -- Bug#2492041
-- GL_DATE, -- Bug#: 3418406
/* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
INVOICE_TYPE_LOOKUP_CODE, -- Complex Work
CALC_TAX_DURING_IMPORT_FLAG, -- TCA
ADD_TAX_TO_INV_AMT_FLAG -- bug 5499478
) VALUES
(x_curr_invoice_id,
x_curr_invoice_num,
x_curr_vendor_id,
x_curr_pay_site_id,
x_curr_invoice_amount,
x_curr_currency_code,
x_curr_le_transaction_date, --Bug 5205516: INVOICE_DATE in LE Time zone
'ERS', -- debug, needs to change,
x_invoice_description,
x_curr_le_transaction_date, --Bug 5205516: GOODS_RECEIVIED_DATE in LE Time zone
x_curr_le_transaction_date, --Bug 5205516: INVOICE_RECEIVIED_DATE in LE Time zone
sysdate,
x_curr_conversion_rate,
x_curr_conversion_rate_type,
x_curr_conversion_rate_date,
X_curr_payment_terms_id,
x_group_id,
x_org_id,
--inv_le_timezone_pub.get_le_day_for_ou(x_curr_transaction_date, x_org_id),
/* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
x_curr_shipment_type,
'Y',
'Y'
);
update rcv_txns, po_line_locations and po_distributions accordingly *****/
X_progress := '140'; -- receipt_invoices
SELECT MIN(NVL(transaction_type, X_receipt_event))
INTO X_inv_event
FROM rcv_transactions
WHERE shipment_line_id = X_rcv_txns.shipment_line_id
AND po_distribution_id = NVL(X_rcv_txns.po_distribution_id,-1)
AND parent_transaction_id = X_rcv_txns.transaction_id
AND transaction_type = 'DELIVER';
UPDATE rcv_transactions
SET invoice_status_code = DECODE(X_curr_inv_process_flag,'Y','INVOICED','REJECTED'), -- bug 3640106
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.login_id
WHERE transaction_id IN (
SELECT
transaction_id
FROM
rcv_transactions
WHERE
invoice_status_code <> 'INVOICED' AND
transaction_type IN ('RECEIVE','CORRECT','RETURN TO VENDOR')
START WITH transaction_id = X_rcv_txns.transaction_id
CONNECT BY parent_transaction_id = PRIOR transaction_id
);
select count(*) into x_dist_count
from ap_invoice_lines_interface
where invoice_id = x_curr_invoice_id;
insert into AP_INVOICES_INTERFACE
(INVOICE_ID,
INVOICE_NUM,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
INVOICE_DATE,
SOURCE,
DESCRIPTION,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
CREATION_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
GROUP_ID,
ORG_ID, -- Bug#2492041
-- GL_DATE ,/* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
INVOICE_TYPE_LOOKUP_CODE, --COMPLEX WORK
CALC_TAX_DURING_IMPORT_FLAG,
ADD_TAX_TO_INV_AMT_FLAG -- bug 5499478
) VALUES
(x_curr_invoice_id,
x_curr_invoice_num,
x_curr_vendor_id,
x_curr_pay_site_id,
x_curr_invoice_amount,
x_curr_currency_code,
x_curr_le_transaction_date, --Bug 5205516: INVOICE_DATE in LE Time zone
'ERS', -- debug, needs to change,
x_invoice_description,
x_curr_le_transaction_date,--Bug 5205516: GOODS_RECEIVIED_DATE in LE Time zone
x_curr_le_transaction_date,--Bug 5205516: INVOICE_RECEIVIED_DATE in LE Time zone
sysdate,
x_curr_conversion_rate,
x_curr_conversion_rate_type,
x_curr_conversion_rate_date,
X_curr_payment_terms_id,
x_group_id,
x_org_id,
-- inv_le_timezone_pub.get_le_day_for_ou(x_curr_transaction_date, x_org_id),
/* Bug 4735452. Commenting gl_date so that AP determines the same based on GL date basis */
x_curr_shipment_type,
'Y',
'Y'
);
/*** Update running totals ***/
X_progress := '170';
select count(*) into x_dist_count
from ap_invoice_lines_interface
where invoice_id = x_curr_invoice_id;
delete from ap_invoices_interface
where invoice_id=x_curr_invoice_id;
** if x_group_id is not null, then at least one record has been inserted.
** Then we need to run the AP import program
*/
IF (x_group_id is NOT NULL) THEN
FND_PROFILE.GET('USER_ID', l_user_id);
SELECT ap_batches_s.nextval
INTO X_tmp_batch_id
FROM dual;
update the current one before the new one can be created. ***/
-- BUG 612979
IF (gl_currency_api.is_fixed_rate(X_curr_pay_curr_code, X_curr_currency_code, X_curr_transaction_date) = 'Y'
and X_curr_pay_curr_code <> X_curr_currency_code) THEN
X_pay_cross_rate := gl_currency_api.get_rate(X_curr_currency_code,
X_curr_pay_curr_code,
X_curr_transaction_date,
'EMU FIXED');
/*** update the running totals ***/
X_invoice_count := X_invoice_count + 1;
select ap_invoices_interface_s.nextval
into x_curr_invoice_id
from sys.dual;
SELECT po_invoice_num_segment_s.NEXTVAL
INTO x_tmp_sequence_id
FROM SYS.DUAL;
select
transaction_id,
primary_quantity,
primary_unit_of_measure,
source_doc_unit_of_measure,
transaction_type,
parent_transaction_id,
unit_of_measure -- Added for bug 6822594 : To get the transaction uom
from
rcv_transactions
where
invoice_status_code <> 'INVOICED'
start with transaction_id = c_transaction_id
connect by parent_transaction_id = prior transaction_id;
select
item_id
into
X_item_id
from
rcv_shipment_lines
where
shipment_line_id = X_shipment_line_id;
select
transaction_type
into
v_parent_type
from
rcv_transactions
where
transaction_id = v_parent_id;
select
transaction_id,
amount,
transaction_type,
parent_transaction_id
from
rcv_transactions
where
invoice_status_code <> 'INVOICED'
start with transaction_id = c_transaction_id
connect by parent_transaction_id = prior transaction_id;
select
transaction_type
into
v_parent_type
from
rcv_transactions
where
transaction_id = v_parent_id;
SELECT pod.po_distribution_id,
pod.set_of_books_id,
pod.code_combination_id,
DECODE(gcc.account_type, 'A','Y','N') assets_tracking_flag,
NVL(pod.quantity_ordered,0) quantity_remaining,
NVL(pod.amount_ordered,0) amount_remaining,
pod.rate,
pod.rate_date,
pod.variance_account_id,
pod.attribute_category,
pod.attribute1,
pod.attribute2,
pod.attribute3,
pod.attribute4,
pod.attribute5,
pod.attribute6,
pod.attribute7,
pod.attribute8,
pod.attribute9,
pod.attribute10,
pod.attribute11,
pod.attribute12,
pod.attribute13,
pod.attribute14,
pod.attribute15,
pod.project_id, -- the following are PA related columns
pod.task_id,
pod.expenditure_item_date,
pod.expenditure_type,
pod.expenditure_organization_id,
pod.project_accounting_context,
pod.recovery_rate
FROM gl_code_combinations gcc,
po_distributions_ap_v pod
WHERE pod.po_header_id = X_po_header_id
AND pod.po_line_id = X_po_line_id
AND pod.line_location_id = X_po_line_location_id
AND pod.code_combination_id = gcc.code_combination_id
AND pod.po_distribution_id = X_po_distribution_id
ORDER BY pod.distribution_num;
SELECT COUNT(*),
SUM(NVL(quantity_ordered,0)),
SUM(NVL(amount_ordered,0))
/***Amount remaining for each po distribution***/
INTO X_count,
X_sum_order_qty,
X_sum_order_amt
FROM po_distributions
WHERE po_header_id = X_po_header_id
AND po_line_id = X_po_line_id
AND line_location_id = X_po_line_location_id
AND DECODE(X_receipt_event, 'DELIVER', po_distribution_id,1)=
DECODE(X_receipt_event, 'DELIVER', X_po_distribution_id,1);
select org_id
into x_org_id
from po_headers_all
where po_header_id = X_po_header_id;
SELECT NVL(MAX(line_number), 0) + 1
INTO X_line_count
FROM ap_invoice_lines_interface
WHERE invoice_id = x_invoice_id;
select ap_invoice_lines_interface_s.nextval
into x_invoice_line_id
from sys.dual;
SELECT item_id,UNIT_MEAS_LOOKUP_CODE
INTO x_item_id , x_po_uom
FROM po_lines_all
WHERE PO_LINE_ID = X_po_line_id;
insert into ap_invoice_lines_interface
(INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
-- ACCOUNTING_DATE, Bug 2664078
DESCRIPTION,
TAX_CODE_ID,
AMOUNT_INCLUDES_TAX_FLAG,
-- DIST_CODE_COMBINATION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
PO_RELEASE_ID,
QUANTITY_INVOICED,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ACCOUNTING_CONTEXT,
PA_QUANTITY,
PA_ADDITION_FLAG,
UNIT_PRICE,
ASSETS_TRACKING_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MATCH_OPTION,
RCV_TRANSACTION_ID,
RECEIPT_NUMBER,
TAX_CODE_OVERRIDE_FLAG, -- Bug 921579, PO needs to pass 'Y' for this
ORG_ID, -- Bug#2492041
TAX_RECOVERY_RATE, -- Bug 3875677
UNIT_OF_MEAS_LOOKUP_CODE, -- 5100177
SHIP_TO_LOCATION_ID, --Bug: 5125624
TAX_CLASSIFICATION_CODE --Bug: 5125624
) VALUES
(x_invoice_id,
x_invoice_line_id,
x_line_count,
'ITEM',
X_invoiced_amount, --X_curr_amount, for bug 6822594
--x_invoice_date, Bug 2664078
x_item_description,
x_tax_code_id,
NULL,
-- x_po_distributions.code_combination_id,
x_po_header_id,
x_po_line_id,
x_po_line_location_id,
x_pod_distribution_id,
x_po_release_id,
X_invoiced_quantity, -- x_curr_qty,--X_curr_amount, for bug 6822594
x_pod_expenditure_item_date,
x_pod_expenditure_type,
x_pod_expenditure_org_id,
x_pod_proj_accounting_context,
X_invoiced_quantity, -- x_curr_qty, for bug 6822594
'N',
X_invoiced_unit_price, --x_unit_price,bug 6822594
x_pod_assets_tracking_flag,
x_pod_attribute_CATEGORY,
x_pod_attribute1,
x_pod_attribute2,
x_pod_attribute3,
x_pod_attribute4,
x_pod_attribute5,
x_pod_attribute6,
x_pod_attribute7,
x_pod_attribute8,
x_pod_attribute9,
x_pod_attribute10,
x_pod_attribute11,
x_pod_attribute12,
x_pod_attribute13,
x_pod_attribute14,
x_pod_attribute15,
x_match_option,
x_rcv_transaction_id,
x_receipt_num,
'Y', -- bug 921579, PO needs to pass 'Y' for this
x_org_id,
x_pod_recovery_rate,
X_unit_meas_lookup_code, -- 5100177
l_ship_to_location_id,
l_tax_classification_code
);
/**UPDATE CURRENT INVOICE AMOUNT**/
X_progress := '150';
SELECT base_currency_code
INTO l_def_base_currency_code
FROM ap_system_parameters;
SELECT 'USE-' || ap_interface_groups_s.nextval
INTO l_group_id
FROM sys.dual;
ASN_DEBUG.put_line('Bulk Insert into header interface');
ASN_DEBUG.put_line('Bulk Insert into line interface');
ASN_DEBUG.put_line('Insert remaining distributions from pl/sql table'
|| ' to lines interface table');
ASN_DEBUG.put_line('Insert remaining invoice headers');
SELECT ap_batches_s.nextval
INTO l_tmp_batch_id
FROM sys.dual;
PO_INVOICES_SV1.delete_interface_records(
l_return_status,
l_group_id);
PO_INVOICES_SV1.delete_interface_records(
l_return_status,
l_group_id);
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL
INTO x_curr.invoice_id
FROM SYS.DUAL;
INSERT INTO ap_invoices_interface(
invoice_id,
invoice_num,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
invoice_date,
source,
description,
creation_date,
exchange_rate,
exchange_rate_type,
exchange_date,
payment_currency_code,
terms_id,
group_id,
org_id)
SELECT
p_ap_inv_header.invoice_id(i),
p_ap_inv_header.invoice_num(i),
p_ap_inv_header.vendor_id(i),
p_ap_inv_header.vendor_site_id(i),
p_ap_inv_header.invoice_amount(i),
p_ap_inv_header.invoice_currency_code(i),
p_ap_inv_header.invoice_date(i),
p_ap_inv_header.source(i),
p_ap_inv_header.description(i),
p_ap_inv_header.creation_date(i),
p_ap_inv_header.exchange_rate(i),
p_ap_inv_header.exchange_rate_type(i),
p_ap_inv_header.exchange_date(i),
p_ap_inv_header.payment_currency_code(i),
p_ap_inv_header.terms_id(i),
p_ap_inv_header.group_id(i),
p_ap_inv_header.org_id(i)
FROM
sys.dual;
INSERT INTO ap_invoice_lines_interface(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
tax_code_Id,
amount_includes_tax_flag,
--dist_code_combination_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_release_id,
quantity_invoiced,
expenditure_item_date,
expenditure_type,
expenditure_organization_id,
project_accounting_context,
pa_quantity,
pa_addition_flag,
unit_price,
assets_tracking_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
match_option,
tax_code_override_flag,
org_id,
unit_of_meas_lookup_code,
SHIP_TO_LOCATION_ID, --Bug: 5125624
TAX_CLASSIFICATION_CODE --Bug: 5125624
)
SELECT
p_consumption.invoice_id(i),
ap_invoice_lines_interface_s.nextval,
p_consumption.invoice_line_number(i),
'ITEM',
p_consumption.invoice_line_amount(i),
-- p_consumption.creation_date(i), -- bug2786193: use sysdate
sysdate,
p_consumption.item_description(i),
p_consumption.tax_code_id(i),
NULL,
--pod.code_combination_id,
p_consumption.po_header_id(i),
p_consumption.po_line_id(i),
p_consumption.line_location_id(i),
p_consumption.po_distribution_id(i),
p_consumption.po_release_id(i),
p_consumption.quantity_invoiced(i),
pod.expenditure_item_date,
pod.expenditure_type,
pod.expenditure_organization_id,
pod.project_accounting_context,
p_consumption.quantity_invoiced(i),
'N',
p_consumption.unit_price(i),
DECODE(gcc.account_type, 'A','Y','N'),
pod.attribute_category,
pod.attribute1,
pod.attribute2,
pod.attribute3,
pod.attribute4,
pod.attribute5,
pod.attribute6,
pod.attribute7,
pod.attribute8,
pod.attribute9,
pod.attribute10,
pod.attribute11,
pod.attribute12,
pod.attribute13,
pod.attribute14,
pod.attribute15,
'P', -- match option
'Y',
p_consumption.org_id(i),
p_consumption.unit_meas_lookup_code(i), --5100177
l_ship_to_location_id,
l_tax_classification_code
FROM
po_distributions pod,
gl_code_combinations gcc
WHERE
pod.po_distribution_id = p_consumption.po_distribution_id(i)
AND pod.code_combination_id = gcc.code_combination_id;