[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select requisition_line_id, req_line_quantity
from po_req_distributions_all
where distribution_id = pn_req_dist_id;
select quantity
from po_requisition_lines_all
where requisition_line_id = pn_req_line_id;
select line_location_id, quantity_ordered
from po_distributions_all
where po_distribution_id = pn_po_dist_id;
select quantity
from po_line_locations_all
where line_location_id = pn_line_loc_id;
select *
from JAI_RCV_TRANSACTIONS
where transaction_id = cp_transaction_id;
select
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)),
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)) * ln_currency_conv_rate
)
),0) functional_tax_amount ,
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)) / ln_currency_conv_rate,
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100))
)
),0) transaction_tax_amount
into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
from JAI_PO_REQ_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and (
( pn_transaction_dist_id is not null and a.requisition_line_id = r_get_reqn_dist_dtl.requisition_line_id )
or (pn_transaction_dist_id is null and a.requisition_header_id = pn_transaction_header_id)
);
SELECT
NVL(
SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount,
a.tax_amount * ln_currency_conv_rate)
),0) functional_tax_amount,
NVL(
SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount/ln_currency_conv_rate,
a.tax_amount)
),0) transaction_tax_amount
INTO
ln_func_inclu_reco_tax_amt
, ln_trx_inclu_reco_tax_amt
FROM
jai_po_req_line_taxes a
, jai_cmn_taxes_all b
WHERE a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
AND ( ( pn_transaction_dist_id IS NOT NULL
AND
a.requisition_line_id = r_get_reqn_dist_dtl.requisition_line_id )
OR
( pn_transaction_dist_id IS NULL
AND
a.requisition_header_id = pn_transaction_header_id )
);
select
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)),
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)) * ln_currency_conv_rate
)
),0) functional_tax_amount ,
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)) / ln_currency_conv_rate,
a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100))
)
),0) transaction_tax_amount
into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
from JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and (
( pn_transaction_dist_id is not null and a.line_location_id = r_get_po_dist_dtl.line_location_id )
or ( pn_transaction_dist_id is null and a.po_header_id = pn_transaction_header_id)
);
SELECT
NVL(
SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount ,
a.tax_amount * ln_currency_conv_rate)
),0) functional_tax_amount ,
NVL(
SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount/ln_currency_conv_rate,
a.tax_amount)
),0) transaction_tax_amount
INTO
ln_func_inclu_reco_tax_amt
, ln_trx_inclu_reco_tax_amt
FROM
jai_po_taxes a
, jai_cmn_taxes_all b
WHERE a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
AND ( ( pn_transaction_dist_id IS NOT NULL
AND
a.line_location_id = r_get_po_dist_dtl.line_location_id )
OR
( pn_transaction_dist_id IS NULL
AND
a.po_header_id = pn_transaction_header_id )
);
select
/* functional tax amount calc */
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount
* decode(
decode(
r_ja_in_deliver_trx.attribute2, 'Y',
decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
, 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
, 'ADDITIONAL_CVD', 'N'
, jai_constants.tax_type_sh_exc_edu_cess, 'N'
, jai_constants.tax_type_sh_cvd_edu_cess, 'N'
, a.modvat_flag)
, a.modvat_flag
),
'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)),
/* if not INR, then following logic will be applied */
a.tax_amount
* decode(
decode(
r_ja_in_deliver_trx.attribute2, 'Y',
decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
, 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
, 'ADDITIONAL_CVD', 'N'
, jai_constants.tax_type_sh_exc_edu_cess, 'N'
, jai_constants.tax_type_sh_cvd_edu_cess, 'N'
, a.modvat_flag)
, a.modvat_flag
),
'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
)
* ln_currency_conv_rate
)
),0) functional_tax_amount ,
/* transaction tax amount calc */
nvl(
sum(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount
* decode(
decode(
r_ja_in_deliver_trx.attribute2, 'Y',
decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
, 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
, 'ADDITIONAL_CVD', 'N'
, jai_constants.tax_type_sh_exc_edu_cess, 'N'
, jai_constants.tax_type_sh_cvd_edu_cess, 'N'
, a.modvat_flag)
, a.modvat_flag
),
'N', 1,(1- nvl(b.mod_cr_percentage,0)/100))
/ ln_currency_conv_rate,
/* if not INR, then following logic will be applied */
a.tax_amount
* decode(
decode(
r_ja_in_deliver_trx.attribute2, 'Y',
decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
, 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
, 'ADDITIONAL_CVD', 'N'
, jai_constants.tax_type_sh_exc_edu_cess, 'N'
, jai_constants.tax_type_sh_cvd_edu_cess, 'N'
, a.modvat_flag)
, a.modvat_flag
),
'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
)
)
),0) transaction_tax_amount
into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and (
-- ( pn_transaction_dist_id is not null and a.transaction_id = r_ja_in_deliver_trx.tax_transaction_id )
( pn_transaction_dist_id is not null and a.shipment_line_id = r_ja_in_deliver_trx.shipment_line_id )
or ( pn_transaction_dist_id is null and a.shipment_header_id = pn_transaction_header_id)
);
SELECT
/* functional inclusive tax amount calc */
NVL(
SUM(
DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount ,
a.tax_amount * ln_currency_conv_rate)
), 0) functional_tax_amount ,
/* transaction inclusive tax amount calc */
NVL(
SUM(
DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount / ln_currency_conv_rate,
a.tax_amount)
), 0) transaction_tax_amount
INTO
ln_func_inclu_reco_tax_amt
, ln_trx_inclu_reco_tax_amt
FROM
jai_rcv_line_taxes a
, jai_cmn_taxes_all b
WHERE a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
AND ( ( pn_transaction_dist_id IS NOT NULL
-- AND a.transaction_id = r_ja_in_deliver_trx.tax_transaction_id )
AND a.shipment_line_id = r_ja_in_deliver_trx.shipment_line_id )
OR
( pn_transaction_dist_id IS NULL
AND a.shipment_header_id = pn_transaction_header_id)
);
1. Non bonded delivery -- Implemented in the SELECT query for amounts itself using attribute2 of DELIVER transaction
2. UOM Conversion
3. Quantity change between receive and deliver -- Implemented with below apportion code
*/
-- Bug# 6321215
-- Changed pn_transaction_qty to r_ja_in_receive_trx.quantity. This is done because in R12 PA populates amount
-- for quantity column if expenditure_type is not rate enabled. Hence refering to DELIVER quantity to determine the
-- apportion factor
jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'r_ja_in_deliver_trx.quantity='||r_ja_in_deliver_trx.quantity ||
', r_ja_in_receive_trx.quantity='||r_ja_in_receive_trx.quantity ||
', pn_transaction_qty =' || pn_transaction_qty
);
select
nvl(sum(a.base_amount),0) functional_tax_amount,
nvl(sum(a.tax_amount),0) transaction_tax_amount
into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
from JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and nvl(b.mod_cr_percentage, 0) = 0
and (a.invoice_id, a.parent_invoice_distribution_id) =
( select invoice_id, invoice_distribution_id from ap_invoice_distributions_all
where invoice_id = pn_transaction_header_id and distribution_line_number = pn_transaction_dist_id);
SELECT
NVL(SUM(a.base_amount),0) functional_tax_amount,
NVL(SUM(a.tax_amount),0) transaction_tax_amount
INTO
ln_func_inclu_reco_tax_amt
, ln_trx_inclu_reco_tax_amt
FROM
jai_ap_match_inv_taxes a
, jai_cmn_taxes_all b
WHERE a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
AND a.recoverable_flag = 'Y'
AND ( a.invoice_id, a.parent_invoice_distribution_id) =
( SELECT
invoice_id
, invoice_distribution_id
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_transaction_header_id
AND distribution_line_number = pn_transaction_dist_id);
update_interface_costs ( p_transaction_source => p_transaction_source
, p_batch => p_batch
, p_xface_id => p_xface_id
, p_process_flag => lv_process_flag
, p_process_message => lv_process_message
);
procedure update_interface_costs
( p_transaction_source in varchar2
, p_batch in varchar2
, p_xface_id in varchar2
, p_process_flag out nocopy varchar2
, p_process_message out nocopy varchar2
)
is
cursor c_pa_trx_xface_records
is
select *
from pa_transaction_interface_all
where transaction_source = p_transaction_source
and batch_name = p_batch
and transaction_status_code = 'P'
and interface_id = p_xface_id;
begin <>
lv_member_name := 'UPDATE_INTERFACE_COSTS';
,'Before update pa_transaction_interface_all'
);
update pa_transaction_interface_all
set raw_cost = raw_cost + nvl(ln_apportioned_tax_amt,0)
-- commented after talking to PROJECTs DEV team
-- ,receipt_currency_amount = receipt_currency_amount + nvl(ln_apportioned_tax_amt,0)
, denom_raw_cost = denom_raw_cost + nvl(ln_apportioned_tax_amt,0)
, acct_raw_cost = acct_raw_cost + nvl(ln_apportioned_tax_amt,0)
where txn_interface_id = r_po_rcpt.txn_interface_id ;
INSERT INTO pa_transaction_interface_all(
TRANSACTION_SOURCE ,
BATCH_NAME ,
EXPENDITURE_ENDING_DATE ,
EMPLOYEE_NUMBER ,
ORGANIZATION_NAME ,
EXPENDITURE_ITEM_DATE ,
PROJECT_NUMBER ,
TASK_NUMBER ,
EXPENDITURE_TYPE ,
NON_LABOR_RESOURCE ,
NON_LABOR_RESOURCE_ORG_NAME ,
QUANTITY ,
RAW_COST ,
EXPENDITURE_COMMENT ,
TRANSACTION_STATUS_CODE ,
TRANSACTION_REJECTION_CODE ,
EXPENDITURE_ID ,
ORIG_TRANSACTION_REFERENCE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
RAW_COST_RATE ,
INTERFACE_ID ,
UNMATCHED_NEGATIVE_TXN_FLAG ,
EXPENDITURE_ITEM_ID ,
ORG_ID ,
DR_CODE_COMBINATION_ID ,
CR_CODE_COMBINATION_ID ,
CDL_SYSTEM_REFERENCE1 ,
CDL_SYSTEM_REFERENCE2 ,
CDL_SYSTEM_REFERENCE3 ,
GL_DATE ,
BURDENED_COST ,
BURDENED_COST_RATE ,
SYSTEM_LINKAGE ,
TXN_INTERFACE_ID ,
USER_TRANSACTION_SOURCE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
RECEIPT_CURRENCY_AMOUNT ,
RECEIPT_CURRENCY_CODE ,
RECEIPT_EXCHANGE_RATE ,
DENOM_CURRENCY_CODE ,
DENOM_RAW_COST ,
DENOM_BURDENED_COST ,
ACCT_RATE_DATE ,
ACCT_RATE_TYPE ,
ACCT_EXCHANGE_RATE ,
ACCT_RAW_COST ,
ACCT_BURDENED_COST ,
ACCT_EXCHANGE_ROUNDING_LIMIT ,
PROJECT_CURRENCY_CODE ,
PROJECT_RATE_DATE ,
PROJECT_RATE_TYPE ,
PROJECT_EXCHANGE_RATE ,
ORIG_EXP_TXN_REFERENCE1 ,
ORIG_EXP_TXN_REFERENCE2 ,
ORIG_EXP_TXN_REFERENCE3 ,
ORIG_USER_EXP_TXN_REFERENCE ,
VENDOR_NUMBER ,
OVERRIDE_TO_ORGANIZATION_NAME,
REVERSED_ORIG_TXN_REFERENCE ,
BILLABLE_FLAG ,
PERSON_BUSINESS_GROUP_NAME ,
PROJFUNC_CURRENCY_CODE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,
ASSIGNMENT_NAME ,
WORK_TYPE_NAME ,
CDL_SYSTEM_REFERENCE4 ,
ACCRUAL_FLAG ,
PROJECT_ID ,
TASK_ID ,
PERSON_ID ,
ORGANIZATION_ID ,
NON_LABOR_RESOURCE_ORG_ID ,
VENDOR_ID ,
OVERRIDE_TO_ORGANIZATION_ID ,
ASSIGNMENT_ID ,
WORK_TYPE_ID ,
PERSON_BUSINESS_GROUP_ID ,
INVENTORY_ITEM_ID ,
WIP_RESOURCE_ID ,
UNIT_OF_MEASURE
) VALUES (
r_po_rcpt.TRANSACTION_SOURCE , -- 'PO RECEIPT NRTAX', --
r_po_rcpt.BATCH_NAME ,
r_po_rcpt.EXPENDITURE_ENDING_DATE ,
r_po_rcpt.EMPLOYEE_NUMBER ,
r_po_rcpt.ORGANIZATION_NAME ,
r_po_rcpt.EXPENDITURE_ITEM_DATE ,
r_po_rcpt.PROJECT_NUMBER ,
r_po_rcpt.TASK_NUMBER ,
r_po_rcpt.EXPENDITURE_TYPE ,
r_po_rcpt.NON_LABOR_RESOURCE ,
r_po_rcpt.NON_LABOR_RESOURCE_ORG_NAME ,
r_po_rcpt.QUANTITY ,
decode(r_po_rcpt.RAW_COST, null, null, nvl(ln_apportioned_txn_tax_amt,0)),
r_po_rcpt.EXPENDITURE_COMMENT ,
r_po_rcpt.TRANSACTION_STATUS_CODE ,
r_po_rcpt.TRANSACTION_REJECTION_CODE ,
r_po_rcpt.EXPENDITURE_ID ,
r_po_rcpt.ORIG_TRANSACTION_REFERENCE ,
r_po_rcpt.ATTRIBUTE_CATEGORY ,
r_po_rcpt.ATTRIBUTE1 ,
r_po_rcpt.ATTRIBUTE2 ,
r_po_rcpt.ATTRIBUTE3 ,
r_po_rcpt.ATTRIBUTE4 ,
r_po_rcpt.ATTRIBUTE5 ,
r_po_rcpt.ATTRIBUTE6 ,
r_po_rcpt.ATTRIBUTE7 ,
r_po_rcpt.ATTRIBUTE8 ,
r_po_rcpt.ATTRIBUTE9 ,
'INDIA LOCALIZATION' , -- r_po_rcpt.ATTRIBUTE10 ,
r_po_rcpt.RAW_COST_RATE ,
r_po_rcpt.INTERFACE_ID ,
r_po_rcpt.UNMATCHED_NEGATIVE_TXN_FLAG ,
r_po_rcpt.EXPENDITURE_ITEM_ID ,
r_po_rcpt.ORG_ID ,
r_po_rcpt.DR_CODE_COMBINATION_ID ,
r_po_rcpt.CR_CODE_COMBINATION_ID ,
r_po_rcpt.CDL_SYSTEM_REFERENCE1 ,
r_po_rcpt.CDL_SYSTEM_REFERENCE2 ,
r_po_rcpt.CDL_SYSTEM_REFERENCE3 ,
r_po_rcpt.GL_DATE ,
r_po_rcpt.BURDENED_COST ,
r_po_rcpt.BURDENED_COST_RATE ,
r_po_rcpt.SYSTEM_LINKAGE ,
pa_txn_interface_s.nextval ,
r_po_rcpt.USER_TRANSACTION_SOURCE ,
r_po_rcpt.CREATED_BY ,
r_po_rcpt.CREATION_DATE ,
r_po_rcpt.LAST_UPDATED_BY ,
r_po_rcpt.LAST_UPDATE_DATE ,
r_po_rcpt.RECEIPT_CURRENCY_AMOUNT ,
r_po_rcpt.RECEIPT_CURRENCY_CODE ,
r_po_rcpt.RECEIPT_EXCHANGE_RATE ,
r_po_rcpt.DENOM_CURRENCY_CODE ,
decode(r_po_rcpt.DENOM_RAW_COST, null,null, nvl(ln_apportioned_txn_tax_amt,0)),
r_po_rcpt.DENOM_BURDENED_COST ,
r_po_rcpt.ACCT_RATE_DATE ,
r_po_rcpt.ACCT_RATE_TYPE ,
r_po_rcpt.ACCT_EXCHANGE_RATE ,
decode(r_po_rcpt.ACCT_RAW_COST,null, null, nvl(ln_apportioned_func_tax_amt,0)) ,
r_po_rcpt.ACCT_BURDENED_COST ,
r_po_rcpt.ACCT_EXCHANGE_ROUNDING_LIMIT ,
r_po_rcpt.PROJECT_CURRENCY_CODE ,
r_po_rcpt.PROJECT_RATE_DATE ,
r_po_rcpt.PROJECT_RATE_TYPE ,
r_po_rcpt.PROJECT_EXCHANGE_RATE ,
r_po_rcpt.ORIG_EXP_TXN_REFERENCE1 ,
r_po_rcpt.ORIG_EXP_TXN_REFERENCE2 ,
r_po_rcpt.ORIG_EXP_TXN_REFERENCE3 ,
r_po_rcpt.ORIG_USER_EXP_TXN_REFERENCE ,
r_po_rcpt.VENDOR_NUMBER ,
r_po_rcpt.OVERRIDE_TO_ORGANIZATION_NAME,
r_po_rcpt.REVERSED_ORIG_TXN_REFERENCE ,
r_po_rcpt.BILLABLE_FLAG ,
r_po_rcpt.PERSON_BUSINESS_GROUP_NAME ,
r_po_rcpt.PROJFUNC_CURRENCY_CODE ,
r_po_rcpt.PROJFUNC_COST_RATE_TYPE ,
r_po_rcpt.PROJFUNC_COST_RATE_DATE ,
r_po_rcpt.PROJFUNC_COST_EXCHANGE_RATE ,
r_po_rcpt.PROJECT_RAW_COST ,
r_po_rcpt.PROJECT_BURDENED_COST ,
r_po_rcpt.ASSIGNMENT_NAME ,
r_po_rcpt.WORK_TYPE_NAME ,
r_po_rcpt.CDL_SYSTEM_REFERENCE4 ,
r_po_rcpt.ACCRUAL_FLAG ,
r_po_rcpt.PROJECT_ID ,
r_po_rcpt.TASK_ID ,
r_po_rcpt.PERSON_ID ,
r_po_rcpt.ORGANIZATION_ID ,
r_po_rcpt.NON_LABOR_RESOURCE_ORG_ID ,
r_po_rcpt.VENDOR_ID ,
r_po_rcpt.OVERRIDE_TO_ORGANIZATION_ID ,
r_po_rcpt.ASSIGNMENT_ID ,
r_po_rcpt.WORK_TYPE_ID ,
r_po_rcpt.PERSON_BUSINESS_GROUP_ID ,
r_po_rcpt.INVENTORY_ITEM_ID ,
r_po_rcpt.WIP_RESOURCE_ID ,
r_po_rcpt.UNIT_OF_MEASURE
);
, 'Number of rows updated='||sql%rowcount
);
end update_interface_costs;