[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM po_requisition_lines_all
WHERE requisition_header_id = p_header_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = pv_unit_of_measure;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_sugg_vendor_name;
SELECT Vendor_Site_Id
FROM Po_Vendor_Sites_All A
WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
AND A.Vendor_Id = p_vendor_id
AND NVL(A.Org_Id, 0) = p_org_id;
SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = cp_org_id;
SELECT *
FROM Po_Requisition_Headers_V
WHERE requisition_header_id = p_header_id;
SELECT *
FROM RCV_TRANSACTIONS
WHERE shipment_header_id = p_header_id
AND shipment_line_id = p_line_id
AND transaction_type = 'RECEIVE';
SELECT *
FROM RCV_SHIPMENT_HEADERS
WHERE shipment_header_id = p_header_id;
SELECT operating_unit, set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_inv_orgn_id;
SELECT * FROM RCV_SHIPMENT_LINES WHERE shipment_line_id = p_line_id;
SELECT lines.cmn_line_id,
lines.header_interface_id,
lines.interface_transaction_id transaction_id,
lines.po_unit_price,
lines.quantity,
lines.item_id,
rtxns.unit_of_measure,
rtxns.vendor_id,
rtxns.vendor_site_id,
rtxns.creation_date,
rtxns.po_header_id, /*rchandan for 5961325*/
poll.price_override,
--lines.CURRENCY_CODE, /*rchandan for 5961325*/
-- rtxns.CURRENCY_CONVERSION_TYPE, /*rchandan for 5961325*/
-- rtxns.CURRENCY_CONVERSION_RATE, /*rchandan for 5961325*/
-- rtxns.CURRENCY_CONVERSION_DATE, /*rchandan for 5961325*/
poll.org_id /*rchandan for 5961325*/
FROM JAI_CMN_LINES lines,
RCV_TRANSACTIONS_INTERFACE rtxns,
po_line_locations_all poll
WHERE lines.interface_transaction_id =
rtxns.interface_transaction_id
AND lines.HEADER_INTERFACE_ID = rtxns.HEADER_INTERFACE_ID
AND lines.po_line_location_id = poll.line_location_id
AND lines.HEADER_INTERFACE_ID = cp_hdr_intf_id
AND lines.CMN_LINE_ID = cp_cmn_line_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = cp_sob_id;
SELECT *
FROM po_headers_all
WHERE po_header_id = cp_header_id;
UPDATE JAI_PO_REQ_LINES
SET Last_Update_Date = SYSDATE,
tax_amount = ln_tax_amount,
total_amount = ln_tax_amount + ln_orig_line_amt,
Last_Updated_By = r_reqn_cur.last_updated_by,
Last_Update_Login = r_reqn_cur.last_update_login
WHERE Requisition_Line_Id = ln_line_id
AND Requisition_Header_Id = ln_header_id;
UPDATE JAI_RCV_LINES
SET Last_Update_Date = SYSDATE,
tax_amount = ln_tax_amount,
Last_Updated_By = r_rcpt_line.last_updated_by,
Last_Update_Login = r_rcpt_line.last_update_login
WHERE shipment_Line_Id = ln_line_id
AND shipment_Header_Id = ln_header_id;
/*UPDATE JAI_RCV_LINES
SET Last_Update_Date = sysdate,
tax_amount = ln_tax_amount ,
Last_Updated_By = r_rcpt_line.last_updated_by,
Last_Update_Login = r_rcpt_line.last_update_login
WHERE shipment_Line_Id = ln_line_id
AND shipment_Header_Id = ln_header_id;*/
PROCEDURE update_rcv_trxs(P_transaction_id IN NUMBER) IS
BEGIN
UPDATE rcv_transactions
SET attribute4 = 'Y', attribute_category = 'India Return to Vendor'
WHERE transaction_id = P_transaction_id;
END update_rcv_trxs;
PROCEDURE update_cmn_lines
(
p_shipment_num IN VARCHAR2,
p_ex_inv_num IN VARCHAR2,
p_ex_inv_date IN date,
p_header_interface_id IN NUMBER DEFAULT NULL, /*bug 8400813*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
) IS
BEGIN
UPDATE jai_cmn_lines
SET excise_inv_number = p_ex_inv_num, excise_inv_date = p_ex_inv_date
WHERE shipment_number = p_shipment_num
AND (p_header_interface_id IS NULL OR /*bug 8400813*/
(p_header_interface_id IS NOT NULL AND header_interface_id = p_header_interface_id));
END update_cmn_lines;
PROCEDURE update_jrcv_flags
(
P_transaction_id IN NUMBER,
p_process_Action VARCHAR2
) IS
lv_process_flag VARCHAR2(1);
UPDATE JAI_RCV_TRANSACTIONS
SET process_status = lv_process_flag,
cenvat_rg_status = lv_process_flag,
cenvat_rg_message = NULL,
process_vat_status = lv_process_flag
WHERE transaction_id = P_transaction_id;
END update_jrcv_flags;
SELECT *
FROM JAI_RCV_LINES
WHERE shipment_header_id = p_shipment_header_id;
lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
update_rcv_trxs(p_transaction_id);
update_jrcv_flags(p_transaction_id, p_process_Action);
update_jrcv_flags(p_transaction_id, p_process_Action);
lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
SELECT * FROM JAI_CMN_TAXES_ALL WHERE tax_id = cp_tax_id;
SELECT rate, currency_code
FROM po_headers_all
WHERE po_header_id = cp_header_id;
FOR j_po_ll_rec IN (SELECT *
FROM JAI_PO_TAXES
WHERE line_location_id = p_line_location_id)
LOOP
OPEN c_tax_codes(j_po_ll_rec.TAX_ID);
INSERT INTO JAI_CMN_DOCUMENT_TAXES
(DOC_TAX_ID,
TAX_LINE_NO,
TAX_ID,
TAX_TYPE,
CURRENCY_CODE,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMT,
FUNC_TAX_AMT,
MODVAT_FLAG,
TAX_CATEGORY_ID,
SOURCE_DOC_TYPE,
SOURCE_DOC_ID,
SOURCE_DOC_LINE_ID,
SOURCE_TABLE_NAME,
ADHOC_FLAG,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6,
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VENDOR_ID)
VALUES
(JAI_CMN_DOCUMENT_TAXES_s.NEXTVAL,
j_po_ll_rec.TAX_LINE_NO,
j_po_ll_rec.TAX_ID,
j_po_ll_rec.TAX_TYPE,
j_po_ll_rec.CURRENCY,
j_po_ll_rec.TAX_RATE,
j_po_ll_rec.QTY_RATE,
j_po_ll_rec.UOM,
ln_tax_Amount,
ln_func_Tax_amt,
j_po_ll_rec.MODVAT_FLAG,
j_po_ll_rec.TAX_CATEGORY_ID,
'ASBN',
p_hdr_intf_id,
p_cmn_line_id,
'JAI_PO_TAXES',
r_taxes.adhoc_flag,
j_po_ll_rec.PRECEDENCE_1,
j_po_ll_rec.PRECEDENCE_2,
j_po_ll_rec.PRECEDENCE_3,
j_po_ll_rec.PRECEDENCE_4,
j_po_ll_rec.PRECEDENCE_5,
j_po_ll_rec.PRECEDENCE_6,
j_po_ll_rec.PRECEDENCE_7,
j_po_ll_rec.PRECEDENCE_8,
j_po_ll_rec.PRECEDENCE_9,
j_po_ll_rec.PRECEDENCE_10,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
j_po_ll_rec.VENDOR_ID);
/*select *
from rcv_transactions_interface
where HEADER_INTERFACE_ID = cp_hdr_intf_id;*/
SELECT rtxn.*,
(SELECT currency_code
FROM po_headers_all
WHERE po_header_id = rtxn.po_header_id) CUR_CODE,
(SELECT quantity
FROM po_line_locations_all
WHERE LINE_LOCATION_ID = rtxn.PO_LINE_LOCATION_ID) TOT_QUANTITY
FROM rcv_transactions_interface rtxn
WHERE rtxn.HEADER_INTERFACE_ID = cp_hdr_intf_id;
SELECT shipment_num
FROM rcv_headers_interface
WHERE HEADER_INTERFACE_ID = cp_hdr_intf_id;
SELECT CURRENCY_CODE
FROM PO_HEADERS
WHERE PO_HEADER_ID = cp_po_header_id;
SELECT *
FROM po_line_locations_all
WHERE line_location_id = cp_line_location_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = pv_unit_of_measure;
SELECT JAI_CMN_LINES_S.NEXTVAL INTO ln_cmn_line_id FROM DUAL;
INSERT INTO JAI_CMN_LINES
(CMN_LINE_ID,
SHIPMENT_NUMBER,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_NUMBER,
LINE_NUM,
SHIPMENT_LINE_NUM,
HEADER_INTERFACE_ID,
SHIPMENT_HEADER_ID,
INTERFACE_TRANSACTION_ID,
SHIPMENT_LINE_ID,
ITEM_ID,
QUANTITY,
EXCISE_INV_NUMBER,
EXCISE_INV_DATE,
CURRENCY_CODE,
PO_UNIT_PRICE,
UOM_CODE) /*bug 8322323*/
VALUES (ln_cmn_line_id,
lv_ship_num,
--r_txns.SHIPMENT_NUM,
r_txns.PO_HEADER_ID,
r_txns.PO_LINE_ID,
r_txns.PO_LINE_LOCATION_ID,
r_txns.DOCUMENT_NUM,
r_txns.DOCUMENT_LINE_NUM,
r_txns.DOCUMENT_SHIPMENT_LINE_NUM,
r_txns.HEADER_INTERFACE_ID,
r_txns.SHIPMENT_HEADER_ID,
r_txns.INTERFACE_TRANSACTION_ID,
r_txns.SHIPMENT_LINE_ID,
r_txns.ITEM_ID,
r_txns.QUANTITY,
p_invoice_num,
p_invoice_date,
r_txns.CUR_CODE,
nvl(r_txns.PO_UNIT_PRICE, r_line_locations.price_override) / ln_uom_conv_factor, /*bug 8322323*/
lv_asbn_uom); /*bug 8322323*/
select shipment_num, asn_type, header_interface_id
from rcv_headers_interface
where group_id = cp_grp_id;
JAI_PO_HOOK_PKG.UPDATE_ASBN_MODE(r_hdr.shipment_num,'PENDING',r_hdr.header_interface_id,lv_errbuf1,lv_retcode1); /*bug 8400813*/
PROCEDURE UPDATE_RCV_TXN
(
P_transaction_id IN NUMBER,
p_invoice_num IN VARCHAR2,
P_invoice_date IN VARCHAR2,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
) IS
lv_attr_categ VARCHAR2(30) := 'India Receipt';
UPDATE RCV_TRANSACTIONS_INTERFACE
SET ATTRIBUTE_CATEGORY = lv_attr_categ,
ATTRIBUTE1 = p_invoice_num,
ATTRIBUTE2 = P_invoice_date
WHERE INTERFACE_TRANSACTION_ID = P_transaction_id;
END UPDATE_RCV_TXN;
SELECT rtl.requisition_line_id, rtl.currency, rtl.tax_amount, nvl(tax.rounding_factor, 0) rnd_factor
FROM JAI_PO_REQ_LINE_TAXES rtl, JAI_CMN_TAXES_ALL tax , po_requisition_lines_all porl
WHERE rtl.requisition_header_id = p_header_id
AND (rtl.requisition_line_id = p_line_id OR p_line_id IS NULL)
AND porl.requisition_line_id = rtl.requisition_line_id
/*Bug 14031501 - Added check on modified_by_agent_flag to exclude tax of the parent line in case Requisition has been split*/
AND
(
porl.modified_by_agent_flag IS NULL
OR
(
porl.modified_by_agent_flag IS NOT NULL
AND
NOT EXISTS (SELECT 1
FROM po_requisition_lines_all iprla
WHERE iprla.requisition_header_id = p_header_id
AND iprla.parent_req_line_id = porl.requisition_line_id)
)
)
AND tax.tax_id = rtl.tax_id
ORDER BY rtl.currency;
SELECT shipment_line_id, currency, SUM(tax_amount) tax_amount
FROM JAI_RCV_LINE_TAXES
WHERE shipment_header_id = p_header_id
AND (shipment_line_id = p_line_id OR p_line_id IS NULL)
GROUP BY shipment_line_id, currency;
SELECT rate, currency_code, cancel_flag, org_id
FROM po_requisition_lines_all
WHERE requisition_line_id = cp_reqn_line_id;
SELECT currency_conversion_rate, currency_code
FROM rcv_transactions
WHERE shipment_line_id = cp_shipment_line_id
AND transaction_type = 'RECEIVE';
SELECT line_location_id, currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES
WHERE po_header_id = p_header_id
AND (line_location_id = p_line_id OR p_line_id IS NULL)
GROUP BY line_location_id, currency;
SELECT rate, currency_code
FROM po_headers
WHERE po_header_id = cp_header_id;
SELECT exchange_rate, currency_code
FROM JAI_AP_MATCH_INV_TAXES
WHERE invoice_id = cp_header_id
And parent_invoice_distribution_id = cp_line_id;
SELECT SUM(amount)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_header_id
AND line_type_lookup_code = 'MISCELLANEOUS';
SELECT currency_code
FROM gl_sets_of_booKs
WHERE set_of_books_id IN
(SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = cp_org_id);
SELECT source_doc_line_id, currency_code, SUM(tax_amt) tax_amount
FROM JAI_CMN_DOCUMENT_TAXES
WHERE source_doc_line_id = cp_cmn_line_id
AND source_doc_type = cp_doc_type
GROUP BY source_doc_line_id, currency_code;
SELECT PHA.rate, PHA.currency_code
FROM PO_HEADERS_ALL PHA
WHERE PHA.PO_HEADER_ID =
(SELECT PO_HEADER_ID
FROM JAI_CMN_LINES
WHERE CMN_LINE_ID = cp_cmn_line_id);
FOR r_shipment_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES
WHERE line_location_id = p_line_id
GROUP BY currency)
LOOP
IF NVL(r_shipment_rec.currency, '$$$') <>
NVL(lv_line_currency, '$$$') THEN
IF ln_conv_rate IS NULL THEN
ln_conv_rate := 1;
FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a,
po_line_locations_all b
WHERE b.po_header_id = p_header_id
AND b.po_release_id = p_line_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND a.line_location_id = b.line_location_id
GROUP BY currency)
LOOP
IF NVL(r_release_rec.currency, '$$$') <>
NVL(lv_line_currency, '$$$') THEN
IF ln_conv_rate IS NULL THEN
ln_conv_rate := 1;
FOR r_po_line_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a
WHERE po_header_id = p_header_id
AND po_line_id = p_line_id
GROUP BY currency)
LOOP
IF NVL(r_po_line_rec.currency, '$$$') <>
NVL(lv_line_currency, '$$$') THEN
IF ln_conv_rate IS NULL THEN
ln_conv_rate := 1;
For r_ap_in_dist in (SELECT currency_code, SUM(tax_amount) tax_amount
FROM JAI_AP_MATCH_INV_TAXES
where invoice_id = p_header_id
And parent_invoice_distribution_id = p_line_id
GROUP BY currency_code)
Loop
OPEN c_conv_rate_inv(p_header_id, p_line_id);
SELECT requisition_line_id, currency, SUM(tax_amount) tax_amount
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_header_id = p_header_id
AND requisition_line_id = p_line_id
GROUP BY requisition_line_id, currency;
SELECT rate, currency_code, cancel_flag, org_id
FROM po_requisition_lines_all
WHERE requisition_line_id = cp_reqn_line_id;
SELECT currency_code
FROM gl_sets_of_booKs
WHERE set_of_books_id IN
(SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = cp_org_id);
SELECT line_location_id
FROM po_line_locations_all
WHERE po_line_id = p_line_id
AND shipment_num = p_shipment_line_num;
SELECT fnd_profile.VALUE(cp_profile_name) FROM DUAL;
PROCEDURE UPDATE_ASBN_MODE
(
p_shipment_num IN VARCHAR2,
p_mode IN VARCHAR2,
p_header_interface_id IN NUMBER DEFAULT NULL, /*bug 8400813*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
) IS
lv_header_id NUMBER;
SELECT header_interface_id
INTO lv_header_id
FROM rcv_headers_interface
WHERE shipment_num = p_shipment_num;
UPDATE rcv_headers_interface
SET PROCESSING_STATUS_CODE = 'IL_PENDING'
WHERE header_interface_id = lv_header_id
AND PROCESSING_STATUS_CODE = 'PENDING';
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE = 'IL_PENDING'
WHERE header_interface_id = lv_header_id
AND PROCESSING_STATUS_CODE = 'PENDING';
UPDATE rcv_headers_interface
SET PROCESSING_STATUS_CODE = 'PENDING'
WHERE header_interface_id = lv_header_id
AND PROCESSING_STATUS_CODE = 'IL_PENDING';
UPDATE RCV_TRANSACTIONS_INTERFACE
SET PROCESSING_STATUS_CODE = 'PENDING'
WHERE header_interface_id = lv_header_id
AND PROCESSING_STATUS_CODE = 'IL_PENDING';
END UPDATE_ASBN_MODE;
SELECT line_location_id, currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES
WHERE po_header_id = p_header_id
AND (line_location_id = p_line_id OR p_line_id IS NULL)
GROUP BY line_location_id, currency;
SELECT a.line_location_id, currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a,
po_line_locations_all b
WHERE a.po_header_id = p_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND a.line_location_id = b.line_location_id
and b.po_release_id is null
--AND (line_location_id = :p_line_id OR :p_line_id IS NULL)
GROUP BY a.line_location_id, a.currency;
SELECT a.line_location_id, currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a,
po_line_locations_all b
WHERE a.po_header_id = p_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND a.line_location_id = b.line_location_id
and b.po_release_id is null
AND (a.po_line_id = p_line_id OR p_line_id IS NULL)
GROUP BY a.line_location_id, a.currency;
SELECT rate, currency_code
FROM po_headers_all
WHERE po_header_id = cp_header_id;
SELECT SUM(amount)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_header_id
AND line_type_lookup_code = 'MISCELLANEOUS';
SELECT currency_code
FROM gl_sets_of_booKs
WHERE set_of_books_id IN
(SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = cp_org_id);
SELECT SUM(tax_amt) tax_amount, currency_code
FROM JAI_CMN_DOCUMENT_TAXES
WHERE source_doc_line_id = cp_cmn_line_id
AND source_doc_type = cp_doc_type
GROUP BY currency_code;
SELECT Sum(Decode(transaction_type,
'RECEIVE',quantity,
'RETURN TO VENDOR',-1*quantity,
'CORRECT',
Decode(parent_transaction_type,'RECEIVE',quantity,
'RETURN TO VENDOR',-1*quantity,0),
0))
FROM jai_rcv_transactions
WHERE shipment_header_id = p_shid
AND shipment_line_id = p_slid;
SELECT Sum(quantity)
FROM jai_rcv_transactions
WHERE shipment_header_id=p_shid
AND shipment_line_id=p_slid
AND transaction_type='RECEIVE';
FOR rec in (select shipment_header_id,shipment_line_id
from rcv_shipment_lines
WHERE po_line_location_id IN (select line_Location_id
from po_line_locations_all
where po_header_id=p_header_id)
AND (po_release_id = p_release_id or p_release_id is null)
)
LOOP
/*bug 9503162*/
ln_net_qty := NULL;
FOR rec in (select distinct invoice_id
from ap_invoice_lines_all
where po_line_location_id IN (select line_Location_id
from po_line_locations_all
where po_header_id=p_header_id)
AND (po_release_id = p_release_id or p_release_id is null)
)
LOOP
ln_tax_amt := ln_tax_amt + gettax('INVOICE', rec.invoice_id,null);
FOR rec in (select shipment_header_id,shipment_line_id from rcv_shipment_lines where po_line_location_id = p_line_id)
LOOP
/*bug 9503162*/
ln_net_qty := NULL;
FOR rec in (select distinct invoice_id from ap_invoice_lines_all where po_line_location_id = p_line_id)
LOOP
ln_tax_amt := ln_tax_amt + gettax('INVOICE', rec.invoice_id,null);
(SELECT po_header_id FROM po_lines_all WHERE from_header_id = p_header_id)
LOOP
ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
(SELECT po_header_id, po_line_id FROM po_lines_all WHERE from_header_id = p_header_id AND from_line_id = p_line_id)
LOOP
ln_tax_amt := ln_tax_amt + gettaxisp('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null);
(SELECT po_header_id FROM po_lines_all WHERE from_header_id = p_header_id)
LOOP
ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
(SELECT po_header_id, po_line_id FROM po_lines_all WHERE po_header_id = p_header_id AND po_line_id = p_line_id)
LOOP
ln_tax_amt := ln_tax_amt + gettaxisp('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null);
(SELECT po_header_id FROM po_lines_all WHERE po_header_id = p_header_id)
LOOP
ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
FOR r_shipment_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES
WHERE line_location_id = p_line_id
GROUP BY currency)
LOOP
OPEN c_conv_rate_rfq(p_header_id);
FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a,
po_line_locations_all b
WHERE b.po_header_id = p_header_id
AND b.po_release_id = p_release_id
AND (p_line_id IS NULL OR b.po_line_id = p_line_id)
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND a.line_location_id = b.line_location_id
GROUP BY currency)
LOOP
OPEN c_conv_rate_rfq(p_header_id);
FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a,
po_line_locations_all b,
po_releases_all c -- added by pramasub #6441185
WHERE b.po_header_id = p_header_id
AND b.po_release_id is not null
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND a.line_location_id = b.line_location_id
AND c.po_release_id = b.po_release_id -- added by pramasub #6441185
AND c.approved_flag = 'Y' -- added by pramasub #6441185
GROUP BY currency)
LOOP
OPEN c_conv_rate_rfq(p_header_id);
FOR r_po_line_rec IN (SELECT currency, SUM(tax_amount) tax_amount
FROM JAI_PO_TAXES a
WHERE po_header_id = p_header_id
AND po_line_id = p_line_id
GROUP BY currency)
LOOP
OPEN c_conv_rate_rfq(p_header_id);
SELECT currency_code
FROM gl_sets_of_booKs
WHERE set_of_books_id IN
(SELECT set_of_books_id
FROM hr_operating_units
WHERE organization_id = pn_org_id);
SELECT
rtl.requisition_line_id
, rtl.currency
, rtl.tax_amount
, NVL(tax.rounding_factor, 0) rnd_factor
FROM
jai_po_req_line_taxes rtl
, jai_cmn_taxes_all tax
, po_requisition_lines_all prla
WHERE rtl.requisition_header_id = pn_header_id
AND (rtl.requisition_line_id = pn_line_id OR pn_line_id IS NULL)
AND prla.requisition_line_id = rtl.requisition_line_id
AND tax.tax_id = rtl.tax_id
/*Bug 14031501 - Added check on modified_by_agent_flag to exclude tax of the parent line in case Requisition has been split*/
AND
(
prla.modified_by_agent_flag IS NULL
OR
(
prla.modified_by_agent_flag IS NOT NULL
AND
NOT EXISTS (SELECT 1
FROM po_requisition_lines_all iprla
WHERE iprla.requisition_header_id = pn_header_id
AND iprla.parent_req_line_id = prla.requisition_line_id)
)
)
AND NVL(tax.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
ORDER BY rtl.currency;
SELECT
rate
, currency_code
, cancel_flag
, org_id
FROM po_requisition_lines_all
WHERE requisition_line_id = cp_reqn_line_id;
SELECT
jrlt.shipment_line_id
, jrlt.currency
, SUM(jrlt.tax_amount) tax_amount
FROM
jai_rcv_line_taxes jrlt
, jai_cmn_taxes_all jcta
WHERE jrlt.shipment_header_id = pn_header_id
AND (jrlt.shipment_line_id = pn_line_id OR pn_line_id IS NULL)
AND jcta.tax_id = jrlt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY
jrlt.shipment_line_id
, jrlt.currency;
SELECT
currency_conversion_rate
, currency_code
FROM rcv_transactions
WHERE shipment_line_id = pn_shipment_line_id
AND transaction_type = 'RECEIVE';
SELECT
jpt.line_location_id
, jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.po_header_id = pn_header_id
AND (jpt.line_location_id = pn_line_id OR pn_line_id IS NULL)
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY
jpt.line_location_id
, jpt.currency;
SELECT
rate
, currency_code
FROM po_headers
WHERE po_header_id = pn_csr_header_id;
SELECT
jamit.currency_code
, SUM(jamit.tax_amount) tax_amount
FROM
jai_ap_match_inv_taxes jamit
, jai_cmn_taxes_all jcta
WHERE jamit.invoice_id = pn_header_id
AND (jamit.parent_invoice_distribution_id = pn_line_id OR pn_line_id IS NULL)
AND jcta.tax_id = jamit.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jamit.currency_code;
SELECT
exchange_rate
, currency_code
FROM jai_ap_match_inv_taxes
WHERE invoice_id = pn_csr_header_id
AND parent_invoice_distribution_id = pn_csr_line_id;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.line_location_id = pn_line_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, po_line_locations_all plla
, jai_cmn_taxes_all jcta
WHERE plla.po_header_id = pn_header_id
AND plla.po_release_id = pn_line_id
AND jpt.po_header_id = plla.po_header_id
AND jpt.po_line_id = plla.po_line_id
AND jpt.line_location_id = plla.line_location_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE po_header_id = pn_header_id
AND po_line_id = pn_line_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jcdt.source_doc_line_id
, jcdt.currency_code
, SUM(jcdt.tax_amt) tax_amount
FROM
jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta
WHERE jcdt.source_doc_line_id = cp_cmn_line_id
AND jcdt.source_doc_type = cp_doc_type
AND jcta.tax_id = jcdt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY
jcdt.source_doc_line_id
, jcdt.currency_code;
SELECT
pha.rate
, pha.currency_code
FROM po_headers_all pha
WHERE pha.po_header_id =
(SELECT po_header_id
FROM jai_cmn_lines
WHERE cmn_line_id = cp_cmn_line_id);
SELECT
jpt.line_location_id
, jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
JAI_PO_TAXES jpt
, jai_cmn_taxes_all jcta
WHERE jpt.po_header_id = pn_header_id
AND (jpt.line_location_id = pn_line_id OR pn_line_id IS NULL)
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.line_location_id, jpt.currency;
SELECT
jpt.line_location_id
, jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
JAI_PO_TAXES jpt
, po_line_locations_all plla
, jai_cmn_taxes_all jcta
WHERE jpt.po_header_id = pn_header_id
AND jpt.po_header_id = plla.po_header_id
AND jpt.po_line_id = plla.po_line_id
AND jpt.line_location_id = plla.line_location_id
AND plla.po_release_id IS NULL
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY
jpt.line_location_id
, jpt.currency;
SELECT
jpt.line_location_id
, jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, po_line_locations_all plla
, jai_cmn_taxes_all jcta
WHERE jpt.po_header_id = pn_header_id
AND jpt.po_header_id = plla.po_header_id
AND jpt.po_line_id = plla.po_line_id
AND jpt.line_location_id = plla.line_location_id
AND plla.po_release_id IS NULL
AND (jpt.po_line_id = pn_line_id OR pn_line_id IS NULL)
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY
jpt.line_location_id
, jpt.currency;
SELECT
rate
, currency_code
FROM po_headers_all
WHERE po_header_id = pn_header_id;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.line_location_id = pn_line_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, po_line_locations_all plla
, jai_cmn_taxes_all jcta
WHERE plla.po_header_id = pn_header_id
AND plla.po_release_id = pn_release_id
AND (pn_line_id IS NULL OR plla.po_line_id = pn_line_id)
AND jpt.po_header_id = plla.po_header_id
AND jpt.po_line_id = plla.po_line_id
AND jpt.line_location_id = plla.line_location_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.po_header_id = pn_header_id
AND jpt.po_line_id = pn_line_id
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
SELECT
jpt.currency
, SUM(jpt.tax_amount) tax_amount
FROM
JAI_PO_TAXES jpt
, po_line_locations_all plla
, po_releases_all pra
, jai_cmn_taxes_all jcta
WHERE plla.po_header_id = pn_header_id
AND plla.po_release_id IS NOT NULL
AND jpt.po_header_id = plla.po_header_id
AND jpt.po_line_id = plla.po_line_id
AND jpt.line_location_id = plla.line_location_id
AND pra.po_release_id = plla.po_release_id
AND pra.approved_flag = 'Y'
AND jcta.tax_id = jpt.tax_id
AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
GROUP BY jpt.currency;
(SELECT po_header_id
FROM po_lines_all
WHERE from_header_id = pn_header_id)
LOOP
ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO',r_po_rec.po_header_id,null,null, pv_inclusive_tax_flag);
FOR r_po_rec IN (SELECT
po_header_id
, po_line_id
FROM po_lines_all
WHERE po_header_id = pn_header_id
AND po_line_id = pn_line_id)
LOOP
ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null,pv_inclusive_tax_flag);
FOR r_po_rec IN (SELECT po_header_id
FROM po_lines_all
WHERE po_header_id = pn_header_id)
LOOP
ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO',r_po_rec.po_header_id,null,null,pv_inclusive_tax_flag);