[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_interface_costs. Added the cursors
c_get_non_rec_taxes and c_ja_in_rcv_trx.
6. 09-Jul-2009 mbremkum 8660365 Appropriation factor was calculated using quantity from Base
Project Line. In R12 Projects populates amounts into Quantity.
Hence replaced it with the Delivered Quantity.
7. 15-Apr-2010 Bo Li 9305067 Replace the old attribute_category columns for JAI_RCV_TRANSACTIONS
with new meaningful one
8. 06-Jul-2010 Jia 7242428 Forward ported to R12.2 from R11i bug#7145704
Added the function get_comtmnt_amt
9. 06-Jul-2010 Jia 8805693 Forward ported to R12.2 from R11i bug#8702421 and bug#8438564
Added the procedure update_interface_cost_tax
10. 06-Jul-2010 Jia 9863751 Forward ported to R12.2 from R11i bug#9188112
Added Post_process procedure
11. 04-May-2012 mbremkum 14026359 Description: Non Recoverables Taxes in Receipt are coming as a
positive cost to Projects when Return to Vendor is done on the Receipt
Fix: Set Tax Amount to negative when Transaction Type is 'RETURN TO RECEIVING'
--------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
/** Package level variables used in debug package*/
lv_object_name jai_cmn_debug_contexts.log_context%type := 'JAI_PA_COSTING_PKG';
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.cenvat_costed_flag, 'Y', --Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
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.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
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.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
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.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
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
);
select txn_interface_id, transaction_status_code, expenditure_item_id, cdl_system_reference4, attribute9
from pa_transaction_interface_all
where transaction_source = p_transaction_source
and transaction_status_code in ('I', 'A')
and interface_id = p_xface_id;
select txn_interface_id, transaction_status_code, expenditure_item_id, cdl_system_reference4, attribute9
from pa_transaction_interface_all
where transaction_source = p_transaction_source
and transaction_status_code in ('I', 'A')
and interface_id = p_xface_id
and (attribute10 <> 'INDIA LOCALIZATION' or attribute10 is NULL);
select *
from jai_pa_tax_trxs
where interface_id = p_xface_id
and transaction_source = p_transaction_source
for update of request_id;
update jai_pa_tax_trxs
set pa_addition_flag = 'Y',
expenditure_item_id = r_pa_trx_xface_rec_status.expenditure_item_id
where cdl_system_reference4 = r_pa_trx_xface_rec_status.cdl_system_reference4
and attribute9 = r_pa_trx_xface_rec_status.attribute9
and attribute10 = 'INDIA LOCALIZATION';
delete from pa_transaction_interface_all
where attribute10 = 'INDIA LOCALIZATION'
and transaction_status_code not in ('I', 'A')
and cdl_system_reference4 = r_pa_trx_xface_rec_status.cdl_system_reference4;
update jai_pa_tax_trxs
set request_id = fnd_global.conc_request_id()
where current of c_upd_req_id;
update pa_transaction_interface_all
set transaction_status_code = 'A'
where transaction_source = p_transaction_source
and transaction_status_code = 'I'
and interface_id = p_xface_id;
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;
select
a.tax_id , -- Added by Jia for FP Bug#9863751, fetched Tax ID to uniquely identify a line in jai_pa_tax_trxs
a.vendor_id , /*Added by nprashar for bug # 8691525*/
/* functional tax amount calc */
nvl(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount
* decode(
decode(
cp_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(
cp_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)
)
* cp_curr_conv_rate
),0) functional_tax_amount ,
/* transaction tax amount calc */
nvl(
decode(nvl(a.currency, 'INR'), 'INR',
a.tax_amount
* decode(
decode(
cp_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))
/ cp_curr_conv_rate,
/* if not INR, then following logic will be applied */
a.tax_amount
* decode(
decode(
cp_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
from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
where a.tax_id = b.tax_id
and a.transaction_id = cp_tax_transaction_id;
select *
from jai_rcv_transactions
where transaction_id = cp_transaction_id;
select *
from jai_pa_tax_trxs jait
where pa_addition_flag = 'N'
and expenditure_item_id is NULL
and batch_name = nvl(p_batch, batch_name)
and (request_id <> FND_GLOBAL.CONC_REQUEST_ID() or request_id is NULL)
and not exists
( select 1
from pa_transaction_interface_all pti
where pti.transaction_source = jait.transaction_source
and pti.cdl_system_reference4 = jait.cdl_system_reference4
and pti.attribute9 = jait.attribute9
and pti.transaction_status_code = 'P');
select cdl_system_reference4, attribute9
from jai_pa_tax_trxs
where cdl_system_reference4 = p_transaction_id
and attribute9 = p_tax_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 ;
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
*/
if r_po_rcpt.quantity is not null
and r_po_rcpt.quantity <> 0
and r_ja_in_receive_trx.quantity is not null
and r_ja_in_receive_trx.quantity <> 0
and r_ja_in_receive_trx.quantity <> r_po_rcpt.quantity
then
/*Bug 8660365 - Replaced r_po_rcpt.quantity with r_ja_in_deliver_trx.quantity as Projects update amounts in Quantity in R12*/
ln_apportion_factor := r_ja_in_deliver_trx.quantity / r_ja_in_receive_trx.quantity;
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 ,
0 , --Bug 8623928 - Quantity must be zero for Tax Lines
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 ,
rec_get_non_rec_taxes.vendor_id , --Added by nprashar for bug # 8563187, replaced this value r_po_rcpt.CDL_SYSTEM_REFERENCE1 by Vendor_id
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 ,
rec_get_non_rec_taxes.vendor_id , --Added by nprashar for bug # 8563187, Referring vendor id from table ja_in_receipt_tax_lines 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
);
FND_FILE.put_line(FND_FILE.log, 'Inserting into jai_pa_tax_trxs');
INSERT INTO jai_pa_tax_trxs(
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 ,
PO_NUMBER ,
PO_HEADER_ID ,
PO_LINE_NUM ,
PO_LINE_ID ,
PERSON_TYPE ,
PO_PRICE_TYPE ,
ADJUSTED_EXPENDITURE_ITEM_ID ,
FC_DOCUMENT_TYPE ,
DOCUMENT_TYPE ,
DOCUMENT_DISTRIBUTION_TYPE ,
SI_ASSETS_ADDITION_FLAG ,
CDL_SYSTEM_REFERENCE5 ,
SC_XFER_CODE ,
ADJUSTED_TXN_INTERFACE_ID ,
NET_ZERO_ADJUSTMENT_FLAG ,
PA_ADDITION_FLAG
) 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 ,
0 , /*Bug 8623928 - Quantity must be zero for Tax Lines*/
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 ,
rec_get_non_rec_taxes.tax_id , /*FP Bug#9863751 - Inserted Tax ID into ATTRIBUTE9 to uniquely identify if a record exists*/
'INDIA LOCALIZATION' ,
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 ,
rec_get_non_rec_taxes.vendor_id ,
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 ,
NULL , /*FP Bug#9863751*/
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 ,
rec_get_non_rec_taxes.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 ,
r_po_rcpt.PO_NUMBER ,
r_po_rcpt.PO_HEADER_ID ,
r_po_rcpt.PO_LINE_NUM ,
r_po_rcpt.PO_LINE_ID ,
r_po_rcpt.PERSON_TYPE ,
r_po_rcpt.PO_PRICE_TYPE ,
r_po_rcpt.ADJUSTED_EXPENDITURE_ITEM_ID ,
r_po_rcpt.FC_DOCUMENT_TYPE ,
r_po_rcpt.DOCUMENT_TYPE ,
r_po_rcpt.DOCUMENT_DISTRIBUTION_TYPE ,
r_po_rcpt.SI_ASSETS_ADDITION_FLAG ,
r_po_rcpt.CDL_SYSTEM_REFERENCE5 ,
r_po_rcpt.SC_XFER_CODE ,
r_po_rcpt.ADJUSTED_TXN_INTERFACE_ID ,
r_po_rcpt.NET_ZERO_ADJUSTMENT_FLAG ,
'N'
);
, 'Number of rows updated='||sql%rowcount
);
SELECT pa_txn_interface_s.nextval
INTO ln_txn_interface_id
FROM dual;
FND_FILE.put_line(FND_FILE.log, 'Inserting into pa_transaction_interface_all :' || ln_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 ,
PO_NUMBER ,
PO_HEADER_ID ,
PO_LINE_NUM ,
PO_LINE_ID ,
PERSON_TYPE ,
PO_PRICE_TYPE ,
ADJUSTED_EXPENDITURE_ITEM_ID ,
FC_DOCUMENT_TYPE ,
DOCUMENT_TYPE ,
DOCUMENT_DISTRIBUTION_TYPE ,
SI_ASSETS_ADDITION_FLAG ,
CDL_SYSTEM_REFERENCE5 ,
SC_XFER_CODE ,
ADJUSTED_TXN_INTERFACE_ID ,
NET_ZERO_ADJUSTMENT_FLAG
) VALUES (
R_JAI_PA_TAX_TRXS.TRANSACTION_SOURCE ,
R_JAI_PA_TAX_TRXS.BATCH_NAME ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_ENDING_DATE ,
R_JAI_PA_TAX_TRXS.EMPLOYEE_NUMBER ,
R_JAI_PA_TAX_TRXS.ORGANIZATION_NAME ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_ITEM_DATE ,
R_JAI_PA_TAX_TRXS.PROJECT_NUMBER ,
R_JAI_PA_TAX_TRXS.TASK_NUMBER ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_TYPE ,
R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE ,
R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE_ORG_NAME ,
R_JAI_PA_TAX_TRXS.QUANTITY ,
R_JAI_PA_TAX_TRXS.RAW_COST ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_COMMENT ,
R_JAI_PA_TAX_TRXS.TRANSACTION_STATUS_CODE ,
R_JAI_PA_TAX_TRXS.TRANSACTION_REJECTION_CODE ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_ID ,
FND_GLOBAL.CONC_REQUEST_ID() , /*FP Bug#9863751 - Current Request ID must be populated in ORIG_TRANSACTION_REFERENCE*/
R_JAI_PA_TAX_TRXS.ATTRIBUTE_CATEGORY ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE1 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE2 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE3 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE4 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE5 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE6 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE7 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE8 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE9 ,
R_JAI_PA_TAX_TRXS.ATTRIBUTE10 ,
R_JAI_PA_TAX_TRXS.RAW_COST_RATE ,
R_JAI_PA_TAX_TRXS.INTERFACE_ID ,
R_JAI_PA_TAX_TRXS.UNMATCHED_NEGATIVE_TXN_FLAG ,
R_JAI_PA_TAX_TRXS.EXPENDITURE_ITEM_ID ,
R_JAI_PA_TAX_TRXS.ORG_ID ,
R_JAI_PA_TAX_TRXS.DR_CODE_COMBINATION_ID ,
R_JAI_PA_TAX_TRXS.CR_CODE_COMBINATION_ID ,
R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE1 ,
R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE2 ,
R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE3 ,
R_JAI_PA_TAX_TRXS.GL_DATE ,
R_JAI_PA_TAX_TRXS.BURDENED_COST ,
R_JAI_PA_TAX_TRXS.BURDENED_COST_RATE ,
R_JAI_PA_TAX_TRXS.SYSTEM_LINKAGE ,
ln_txn_interface_id ,
R_JAI_PA_TAX_TRXS.USER_TRANSACTION_SOURCE ,
R_JAI_PA_TAX_TRXS.CREATED_BY ,
R_JAI_PA_TAX_TRXS.CREATION_DATE ,
R_JAI_PA_TAX_TRXS.LAST_UPDATED_BY ,
R_JAI_PA_TAX_TRXS.LAST_UPDATE_DATE ,
R_JAI_PA_TAX_TRXS.RECEIPT_CURRENCY_AMOUNT ,
R_JAI_PA_TAX_TRXS.RECEIPT_CURRENCY_CODE ,
R_JAI_PA_TAX_TRXS.RECEIPT_EXCHANGE_RATE ,
R_JAI_PA_TAX_TRXS.DENOM_CURRENCY_CODE ,
R_JAI_PA_TAX_TRXS.DENOM_RAW_COST ,
R_JAI_PA_TAX_TRXS.DENOM_BURDENED_COST ,
R_JAI_PA_TAX_TRXS.ACCT_RATE_DATE ,
R_JAI_PA_TAX_TRXS.ACCT_RATE_TYPE ,
R_JAI_PA_TAX_TRXS.ACCT_EXCHANGE_RATE ,
R_JAI_PA_TAX_TRXS.ACCT_RAW_COST ,
R_JAI_PA_TAX_TRXS.ACCT_BURDENED_COST ,
R_JAI_PA_TAX_TRXS.ACCT_EXCHANGE_ROUNDING_LIMIT ,
R_JAI_PA_TAX_TRXS.PROJECT_CURRENCY_CODE ,
R_JAI_PA_TAX_TRXS.PROJECT_RATE_DATE ,
R_JAI_PA_TAX_TRXS.PROJECT_RATE_TYPE ,
R_JAI_PA_TAX_TRXS.PROJECT_EXCHANGE_RATE ,
R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE1 ,
R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE2 ,
R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE3 ,
R_JAI_PA_TAX_TRXS.ORIG_USER_EXP_TXN_REFERENCE ,
R_JAI_PA_TAX_TRXS.VENDOR_NUMBER ,
R_JAI_PA_TAX_TRXS.OVERRIDE_TO_ORGANIZATION_NAME,
R_JAI_PA_TAX_TRXS.REVERSED_ORIG_TXN_REFERENCE ,
R_JAI_PA_TAX_TRXS.BILLABLE_FLAG ,
R_JAI_PA_TAX_TRXS.PERSON_BUSINESS_GROUP_NAME ,
R_JAI_PA_TAX_TRXS.PROJFUNC_CURRENCY_CODE ,
R_JAI_PA_TAX_TRXS.PROJFUNC_COST_RATE_TYPE ,
R_JAI_PA_TAX_TRXS.PROJFUNC_COST_RATE_DATE ,
R_JAI_PA_TAX_TRXS.PROJFUNC_COST_EXCHANGE_RATE ,
R_JAI_PA_TAX_TRXS.PROJECT_RAW_COST ,
R_JAI_PA_TAX_TRXS.PROJECT_BURDENED_COST ,
R_JAI_PA_TAX_TRXS.ASSIGNMENT_NAME ,
R_JAI_PA_TAX_TRXS.WORK_TYPE_NAME ,
R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE4 ,
R_JAI_PA_TAX_TRXS.ACCRUAL_FLAG ,
R_JAI_PA_TAX_TRXS.PROJECT_ID ,
R_JAI_PA_TAX_TRXS.TASK_ID ,
R_JAI_PA_TAX_TRXS.PERSON_ID ,
R_JAI_PA_TAX_TRXS.ORGANIZATION_ID ,
R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE_ORG_ID ,
R_JAI_PA_TAX_TRXS.VENDOR_ID ,
R_JAI_PA_TAX_TRXS.OVERRIDE_TO_ORGANIZATION_ID ,
R_JAI_PA_TAX_TRXS.ASSIGNMENT_ID ,
R_JAI_PA_TAX_TRXS.WORK_TYPE_ID ,
R_JAI_PA_TAX_TRXS.PERSON_BUSINESS_GROUP_ID ,
R_JAI_PA_TAX_TRXS.INVENTORY_ITEM_ID ,
R_JAI_PA_TAX_TRXS.WIP_RESOURCE_ID ,
R_JAI_PA_TAX_TRXS.UNIT_OF_MEASURE ,
R_JAI_PA_TAX_TRXS.PO_NUMBER ,
R_JAI_PA_TAX_TRXS.PO_HEADER_ID ,
R_JAI_PA_TAX_TRXS.PO_LINE_NUM ,
R_JAI_PA_TAX_TRXS.PO_LINE_ID ,
R_JAI_PA_TAX_TRXS.PERSON_TYPE ,
R_JAI_PA_TAX_TRXS.PO_PRICE_TYPE ,
R_JAI_PA_TAX_TRXS.ADJUSTED_EXPENDITURE_ITEM_ID ,
R_JAI_PA_TAX_TRXS.FC_DOCUMENT_TYPE ,
R_JAI_PA_TAX_TRXS.DOCUMENT_TYPE ,
R_JAI_PA_TAX_TRXS.DOCUMENT_DISTRIBUTION_TYPE ,
R_JAI_PA_TAX_TRXS.SI_ASSETS_ADDITION_FLAG ,
R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE5 ,
R_JAI_PA_TAX_TRXS.SC_XFER_CODE ,
R_JAI_PA_TAX_TRXS.ADJUSTED_TXN_INTERFACE_ID ,
R_JAI_PA_TAX_TRXS.NET_ZERO_ADJUSTMENT_FLAG
);
end update_interface_costs;
SELECT pda.po_distribution_id,
pda.accrue_on_receipt_flag,
aida.project_id
FROM ap_invoices_all aia,
po_distributions_all pda,
ap_invoice_distributions_all aida
WHERE aia.invoice_id = cp_invoice_id
AND aida.invoice_id = aia.invoice_id
AND pda.po_distribution_id = aida.po_distribution_id
AND aida.distribution_line_number = cp_dist_line_num
AND aida.project_id = cp_project_id;
SELECT a.transaction_id,
a.shipment_header_id,
a.quantity,
b.currency_conversion_rate
FROM jai_rcv_transactions a, rcv_transactions b
WHERE b.po_distribution_id = cp_po_distribution_id
AND ((b.destination_type_code = 'EXPENSE' ) OR
(b.destination_type_code = 'RECEIVING' AND
b.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
AND b.transaction_id = a.transaction_id;
select count(*)
into v_inv_num
from ap_invoice_distributions_all
where po_distribution_id = rec_get_po_dtls.po_distribution_id
and pa_addition_flag not in ('Z','T','E','Y');
PROCEDURE update_interface_cost_tax( p_transaction_id IN NUMBER
, p_tax_type IN VARCHAR2 DEFAULT 'VAT'
, p_process_flag out nocopy varchar2
, p_process_message out nocopy varchar2)
IS
lv_transaction_source pa_transaction_interface.user_transaction_source%TYPE;
SELECT rcv_txn.transaction_id rcv_transaction_id,
po_dist.po_distribution_id po_distribution_id,
po_dist.po_header_id po_header_id,
po_head.segment1 po_num,
nvl(rcv_txn.quantity,0) quantity,
nvl(rcv_sub.ENTERED_DR,0) entered_dr,
nvl(rcv_sub.entered_cr,0) entered_cr,
nvl(rcv_sub.ACCOUNTED_DR,0) accounted_dr,
nvl(rcv_sub.accounted_cr,0) accounted_cr,
nvl(rcv_sub.ENTERED_NR_TAX,0) ENTERED_NR_TAX,
nvl(rcv_sub.ACCOUNTED_NR_TAX,0) ACCOUNTED_NR_TAX,
rcv_sub.code_combination_id dr_cc_id,
rcv_txn.CURRENCY_CODE denom_currency_code ,
rcv_txn.CURRENCY_CONVERSION_DATE ,
rcv_txn.CURRENCY_CONVERSION_TYPE ,
rcv_txn.CURRENCY_CONVERSION_RATE ,
trunc(rcv_txn.TRANSACTION_DATE) GL_Date,
rcv_txn.DESTINATION_TYPE_CODE ,
rcv_sub.pa_addition_flag ,
rcv_txn.transaction_type trx_type,
po_dist.project_id,
po_dist.task_id,
VEND.employee_id employee_id,
po_dist.expenditure_type,
po_dist.EXPENDITURE_ITEM_DATE,
VEND.vendor_id vendor_id,
po_dist.EXPENDITURE_ORGANIZATION_ID expenditure_organization_id,
Decode( nvl(VEND.employee_id, 0),0,NULL, PA_UTILS.GetEmpJobId(VEND.employee_id,po_DIST.Expenditure_Item_Date)) Job_id,
po_line.ITEM_DESCRIPTION description,
po_dist.attribute_category,
po_dist.attribute1,
po_dist.attribute2,
po_dist.attribute3,
po_dist.attribute4,
po_dist.attribute5,
po_dist.attribute6,
po_dist.attribute7,
po_dist.attribute8,
po_dist.attribute9,
po_dist.attribute10,
po_dist.ORG_ID
,rcv_sub.accounting_event_id acct_evt_id
,rcv_sub.accounted_rec_tax
,rcv_sub.entered_rec_tax
from rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub,
po_headers_all po_head,
po_lines_all po_line,
po_distributions_all po_dist,
po_vendors vend
where po_dist.CODE_COMBINATION_ID =rcv_sub.CODE_COMBINATION_ID
AND rcv_sub.ACTUAL_FLAG = 'A'
and rcv_sub.pa_addition_flag = 'Y'
AND po_dist.project_ID IS NOT NULL
AND po_dist.accrue_on_receipt_flag= 'Y'
and rcv_txn.transaction_id = cp_transaction_id
AND rcv_txn.transaction_id=rcv_sub.rcv_transaction_id
AND rcv_txn.po_header_id=po_head.PO_HEADER_ID
AND po_head.po_header_id=po_line.po_header_id
AND po_line.po_line_id=po_dist.po_line_id
AND rcv_txn.PO_DISTRIBUTION_ID=po_dist.po_distribution_id
AND po_head.vendor_id=VEND.Vendor_Id
order by rcv_txn.po_distribution_id;
SELECT tax_id , -- Added by Jia for FP Bug#9863751, fetched Tax ID to uniquely identify a line in jai_pa_tax_trxs
nvl(
decode(
nvl(currency,'INR'),'INR',
decode (modvat_flag, 'Y',0,tax_amount),
decode (modvat_flag, 'Y',0,tax_amount) * cp_curr_conv_rate
)
,0) functional_tax_amount ,
nvl(
decode(
nvl(currency,'INR'),'INR',
decode (modvat_flag, 'Y',0,tax_amount)/nvl(cp_curr_conv_rate,1),
decode (modvat_flag, 'Y',0,tax_amount)
)
,0) transaction_tax_amount
FROM JAI_RCV_LINE_TAXES
where ((cp_tax_type = 'VAT' AND tax_type in ( select tax_type
from jai_regime_tax_types_v
where regime_code = 'VAT'))
OR
(cp_tax_type = 'EXCISE' AND tax_type in (jai_constants.tax_type_excise,
jai_constants.tax_type_exc_additional,
jai_constants.tax_type_exc_other,
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_sh_exc_edu_cess
)))
and transaction_id = cp_transaction_id;
SELECT *
FROM jai_rcv_transactions
WHERE transaction_type = 'DELIVER'
AND parent_transaction_id = cp_transaction_id;
SELECT process_status_flag
FROM jai_rcv_rgm_lines
WHERE transaction_id = cp_transaction_id;
SELECT cenvat_rg_status, cenvat_rg_message
FROM jai_rcv_transactions
WHERE transaction_id = cp_transaction_id;
select *
from jai_pa_tax_trxs
where pa_addition_flag = 'N'
and expenditure_item_id is NULL;
select cdl_system_reference4, attribute9
from jai_pa_tax_trxs
where cdl_system_reference4 = p_transaction_id
and attribute9 = p_tax_id;
SELECT org_id
INTO ln_org_id
FROM pa_implementations;
SELECT pa_utils.getweekending(MAX(expenditure_item_date))
INTO ld_expenditure_ending_date
FROM po_distributions
WHERE po_distribution_id = rec_rcv_receipts.po_distribution_id;
SELECT emp.business_group_id
INTO ln_person_business_group_id
FROM per_all_people_f emp
WHERE emp.person_id = rec_rcv_receipts.employee_id
AND rec_rcv_receipts.EXPENDITURE_ITEM_DATE between trunc(emp.effective_start_date) and
trunc(emp.effective_end_date);
SELECT pa_interface_id_s.nextval
into ln_unique_id
FROM dual;
INSERT INTO jai_pa_tax_trxs(
transaction_source
, user_transaction_source
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, gl_date
, org_id
, unmatched_negative_txn_flag
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, txn_interface_id
, pa_addition_flag
)
VALUES
( lv_transaction_source
,lv_user_transaction_source
,lv_batch_name
,ld_expenditure_ending_date
,rec_rcv_receipts.EXPENDITURE_ITEM_DATE
,rec_rcv_receipts.expenditure_type
,rec_rcv_receipts.quantity
,ln_apportioned_trx_tax_amt
,rec_rcv_receipts.description
,lv_transaction_status_code
,lv_transaction_rejection_code
,-999999 -- Added by Jia for FP Bug#9863751
,ln_interface_id
,rec_rcv_receipts.dr_cc_id
,NULL
,rec_rcv_receipts.vendor_id
,rec_rcv_receipts.po_header_id
,rec_rcv_receipts.po_distribution_id
,rec_rcv_receipts.rcv_transaction_id
,SYSDATE /*rec_rcv_receipts.GL_Date*/
/*Bug 9231374 - Data would be inserted in jai_pa_tax_trxs immediately after VAT or CENVAT is Unclaimed
Hence SYSDATE can be inserted into GL Date. This would ensure that Unclaimed Taxes are accounted in the correct
periods when they are interfaced to Projects*/
,ln_org_id
,'Y'
,ln_apportioned_trx_tax_amt
,rec_rcv_receipts.denom_currency_code
,rec_rcv_receipts.CURRENCY_CONVERSION_DATE
,rec_rcv_receipts.CURRENCY_CONVERSION_TYPE
,rec_rcv_receipts.CURRENCY_CONVERSION_RATE
,ln_apportioned_func_tax_amt
,1
,rec_rcv_receipts.attribute_category
,rec_rcv_receipts.attribute1
,rec_rcv_receipts.attribute2
,rec_rcv_receipts.attribute3
,rec_rcv_receipts.attribute4
,rec_rcv_receipts.attribute5
,rec_rcv_receipts.attribute6
,rec_rcv_receipts.attribute7
,rec_rcv_receipts.attribute8
,rec_get_tax_amount.tax_id -- Added by Jia for FP Bug#9863751
,'INDIA LOCALIZATION'
,rec_rcv_receipts.po_distribution_id
,rec_rcv_receipts.rcv_transaction_id
,rec_rcv_receipts.acct_evt_id
,NULL
,SYSDATE
,-1
,SYSDATE
,-1
,rec_rcv_receipts.employee_id
,rec_rcv_receipts.ORG_ID
,rec_rcv_receipts.project_id
,rec_rcv_receipts.task_id
,rec_rcv_receipts.vendor_id
,rec_rcv_receipts.expenditure_organization_id
,ln_person_business_group_id
,NULL
,'N'
);
END update_interface_cost_tax;