[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Encumbrance Exists'
FROM jai_encum_tax_details
WHERE document_type = cp_document_type
AND distribution_id = cp_distribution_id;
SELECT line_location_id
FROM po_encumbrance_gt
WHERE distribution_id = cp_distribution_id;
SELECT po_line_id
FROM po_line_locations_all
WHERE line_location_id = cp_line_location_id;
SELECT poh.currency_code, poh.rate,poh.po_header_id
FROM po_headers_all poh, po_lines_all pol
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = cp_po_line_id;
SELECT distinct(line_id)
FROM po_encumbrance_gt
WHERE distribution_id = cp_distribution_id;
SELECT currency_code, rate,requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = cp_req_line_id;
SELECT *
FROM jai_encum_tax_details
WHERE document_type = cp_document_type
AND distribution_id = cp_distribution_id;
SELECT nvl(sum(po_tax.tax_amount * DECODE(tax_code.modifiable_flag,
nvl(po_tax.modvat_flag,'Y'),
((100-nvl(tax_code.mod_cr_percentage,0))/100),1 )*
DECODE(po_tax.currency,'INR',1/nvl(poh.rate,1),1) ),0)
INTO ln_nr_tot_tax
FROM JAI_PO_TAXES PO_TAX,
JAI_CMN_TAXES_ALL TAX_CODE,
PO_HEADERS_ALL POH,
PO_LINE_LOCATIONS_ALL POLL
WHERE po_tax.line_location_id = poll.line_location_id
AND poll.po_header_id = poh.po_header_id
AND po_tax.tax_id = tax_code.tax_id
AND poll.line_location_id = ln_line_location_id
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N';
SELECT NVL(SUM(po_tax.tax_amount * DECODE(tax_code.modifiable_flag,
NVL(po_tax.modvat_flag,'Y'), 1,
(NVL(tax_code.mod_cr_percentage,0)/100))*
DECODE( po_tax.currency, 'INR', 1/NVL(poh.rate,1), 1) ), 0)
INTO ln_rec_tot_inclu_tax
FROM jai_po_taxes po_tax
, jai_cmn_taxes_all tax_code
, po_headers_all poh
, po_line_locations_all poll
WHERE po_tax.line_location_id = poll.line_location_id
AND poll.po_header_id = poh.po_header_id
AND po_tax.tax_id = tax_code.tax_id
AND poll.line_location_id = ln_line_location_id
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
SELECT nvl(round(sum(po_tax.tax_amount * DECODE(tax_code.modifiable_flag,
nvl(po_tax.modvat_flag,'Y'),((100-nvl(tax_code.mod_cr_percentage,0))/100),1 )*
DECODE(po_tax.currency,'INR',1/nvl(poh.rate,1),1)*
( dist.quantity_ordered / poll.quantity )),2),0)
INTO p_nr_tax_tbl(i)
FROM JAI_PO_TAXES PO_TAX,
JAI_CMN_TAXES_ALL TAX_CODE,
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL DIST,
PO_LINE_LOCATIONS_ALL POLL
WHERE po_tax.line_location_id = dist.line_location_id
AND dist.line_location_id = poll.line_location_id
AND dist.po_header_id = poh.po_header_id
AND po_tax.tax_id = tax_code.tax_id
AND dist.po_distribution_id = p_dist_id_tbl(i)
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N';
SELECT NVL(ROUND(SUM(po_tax.tax_amount * DECODE( tax_code.modifiable_flag,
NVL(po_tax.modvat_flag,'Y'), 1, (NVL(tax_code.mod_cr_percentage,0)/100))*
DECODE( po_tax.currency, 'INR',1/NVL(poh.rate,1), 1)*
(dist.quantity_ordered/poll.quantity)), 2), 0)
INTO ln_rec_tax_inclu
FROM jai_po_taxes po_tax
, jai_cmn_taxes_all tax_code
, po_headers_all poh
, po_distributions_all dist
, po_line_locations_all poll
WHERE po_tax.line_location_id = dist.line_location_id
AND dist.line_location_id = poll.line_location_id
AND dist.po_header_id = poh.po_header_id
AND po_tax.tax_id = tax_code.tax_id
AND dist.po_distribution_id = p_dist_id_tbl(i)
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
Modified all the select statements which derive the non recoverable tax to fetch the amounts in document currency*/
ELSIF p_dist_type_tbl(i) IN ('REQUISITION') THEN
lv_doc_type := 'REQ';
SELECT nvl(sum(req_tax.tax_amount * DECODE(tax_code.modifiable_flag,
nvl(req_tax.modvat_flag,'Y'),((100-nvl(tax_code.mod_cr_percentage,0))/100),1 )*
DECODE(req_tax.currency,'INR',1/nvl(lines.rate, 1), 1 ) ),0)
INTO ln_nr_tot_tax
FROM JAI_PO_REQ_LINE_TAXES Req_TAX,
JAI_CMN_TAXES_ALL TAX_CODE,
PO_REQUISITION_LINES_ALL lines
WHERE req_tax.requisition_line_id = lines.requisition_line_id
AND req_tax.requisition_header_id = lines.requisition_header_id
AND req_tax.tax_id = tax_code.tax_id
AND lines.requisition_line_id = ln_req_line_id
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N';
SELECT NVL(SUM(req_tax.tax_amount * DECODE( tax_code.modifiable_flag,
NVL(req_tax.modvat_flag,'Y'), 1, (NVL(tax_code.mod_cr_percentage,0)/100))*
DECODE( req_tax.currency, 'INR',1/nvl(lines.rate, 1), 1 ) ), 0)
INTO ln_rec_tot_inclu_tax
FROM jai_po_req_line_taxes req_tax
, jai_cmn_taxes_all tax_code
, po_requisition_lines_all lines
WHERE req_tax.requisition_line_id = lines.requisition_line_id
AND req_tax.requisition_header_id = lines.requisition_header_id
AND req_tax.tax_id = tax_code.tax_id
AND lines.requisition_line_id = ln_req_line_id
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
SELECT nvl(round(sum(req_tax.tax_amount * DECODE(tax_code.modifiable_flag,
nvl(req_tax.modvat_flag,'Y'),((100-nvl(tax_code.mod_cr_percentage,0))/100),1 )*
DECODE(req_tax.currency,'INR',1/nvl(lines.rate, 1), 1 ) *
(dist.req_line_quantity/lines.quantity)),2),0)
INTO p_nr_tax_tbl(i)
FROM JAI_PO_REQ_LINE_TAXES Req_TAX,
JAI_CMN_TAXES_ALL TAX_CODE,
PO_REQ_DISTRIBUTIONS_ALL dist,
PO_REQUISITION_LINES_ALL lines
WHERE req_tax.requisition_line_id = lines.requisition_line_id
AND req_tax.requisition_header_id = lines.requisition_header_id
AND lines.requisition_line_id = dist.requisition_line_id
AND req_tax.tax_id = tax_code.tax_id
AND dist.distribution_id = p_dist_id_tbl(i)
AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N';
SELECT NVL(ROUND(SUM(req_tax.tax_amount * DECODE( tax_code.modifiable_flag,
NVL(req_tax.modvat_flag,'Y'), 1, (NVL(tax_code.mod_cr_percentage,0)/100))*
DECODE( req_tax.currency, 'INR',1/nvl(lines.rate, 1), 1 ) *
(dist.req_line_quantity/lines.quantity)), 2), 0)
INTO ln_rec_tax_inclu
FROM jai_po_req_line_taxes req_tax
, jai_cmn_taxes_all tax_code
, po_req_distributions_all dist
, po_requisition_lines_all lines
WHERE req_tax.requisition_line_id = lines.requisition_line_id
AND req_tax.requisition_header_id = lines.requisition_header_id
AND lines.requisition_line_id = dist.requisition_line_id
AND req_tax.tax_id = tax_code.tax_id
AND dist.distribution_id = p_dist_id_tbl(i)
AND nvl(tax_code.inclusive_tax_flag, 'N') = 'Y';
/*If record exists then update it with the tax amount and status*/
UPDATE jai_encum_tax_details
SET nr_tax_amount = ln_doc_curr_tax_amt,
func_nr_tax_amount = ln_func_curr_tax_amt, /*10421803 - Replaced p_nr_tax_tbl with ln_func_curr_tax_amt*/
status = DECODE(p_action,PO_CONSTANTS_SV.RESERVE,'RESERVED',
PO_CONSTANTS_SV.CANCEL ,'CANCELLED',
'FINAL CLOSE' ,'CLOSED'),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE document_type = lv_doc_type
AND distribution_id = p_dist_id_tbl(i);
INSERT INTO
jai_encum_tax_details( DOCUMENT_TYPE ,
DOC_HEADER_ID ,
DOC_LINE_ID ,
LINE_LOCATION_ID ,
DISTRIBUTION_ID ,
CURRENCY_CODE ,
NR_TAX_AMOUNT ,
FUNC_NR_TAX_AMOUNT ,
STATUS ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER
)
VALUES( lv_doc_type ,
ln_doc_header_id ,
ln_doc_line_id ,
ln_line_location_id ,
p_dist_id_tbl(i) ,
lv_doc_currency_code ,
ln_doc_curr_tax_amt ,
ln_func_curr_tax_amt ,
DECODE(p_action,PO_CONSTANTS_SV.RESERVE,'RESERVED' ,PO_CONSTANTS_SV.CANCEL ,'CANCELLED','FINAL CLOSE' ,'CLOSED') ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
NULL
);
SELECT nr_tax_amount
FROM jai_encum_tax_details
WHERE document_type = p_source_doc_type
AND distribution_id = p_source_doc_id;
SELECT nvl(sum(nr_tax_amount),0)
FROM jai_encum_tax_details
WHERE document_type = p_source_doc_type
AND doc_line_id = p_source_doc_id;
INSERT INTO jai_encum_tax_rvrsl_dtls( ACCT_TXN_ID ,
SOURCE_DOC_ID ,
ACCT_SOURCE ,
SOURCE_DOC_TYPE ,
DOC_NR_TAX ,
DOC_REC_TAX ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER )
VALUES ( p_acct_txn_id ,
p_source_doc_id ,
p_acct_source ,
p_source_doc_type ,
p_nr_tax_amount ,
0 ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
fnd_global.login_id ,
NULL
);