[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(burden_cost_flag,'N')
INTO X_proj_bcost_flag
FROM pa_projects_all proj,
pa_project_types_all ptype
WHERE proj.project_type = ptype.project_type
AND nvl(proj.org_id, -99) = nvl(ptype.org_id,-99)
AND project_id = p_Project_id ;
SELECT 'Y'
INTO l_billable
FROM pa_projects_all pp,
pa_tasks t,
pa_project_types_all pt /** Bug fix 2262118 **/
WHERE t.task_id = p_task_id
AND pp.project_id = t.project_id
AND pp.project_type = pt.project_type
AND pt.PROJECT_TYPE_CLASS_CODE in ('CAPITAL','CONTRACT')
-- start 12i MOAC changes
-- AND nvl(pp.org_id ,-99) = nvl(pt.org_id ,-99) ;
SELECT
min(i.expenditure_item_id),
-- SST Change: If X_reversed_orig_txn_reference is not NULL
-- then we already have the reversing item's
-- orig_transaction_reference, otherwise get the
-- reversing item's orig_transaction_reference
decode(X_reversed_orig_txn_reference,NULL,
i.orig_transaction_reference,
X_reversed_orig_txn_reference) orig_txn_reference
FROM
pa_transaction_sources ts
, pa_expenditure_items i
, pa_expenditures e
WHERE
i.expenditure_item_date = X_item_date
AND i.expenditure_type ||'' = X_exp_type
AND i.system_linkage_function ||'' = X_system_linkage_function
AND i.task_id = X_task_id
AND ( i.quantity * -1 ) = X_quantity
AND i.transaction_source = ts.transaction_source
AND ( ( ts.costed_flag = 'Y'
AND ( i.denom_raw_cost * -1) = pa_currency.round_trans_currency_amt(X_denom_raw_cost,X_denom_currency_code))
OR ( ts.costed_flag = 'N' ) )
AND ( ( ts.gl_accounted_flag = 'Y'
AND ( i.acct_raw_cost * -1) =
pa_currency.round_currency_amt(X_acct_raw_cost))
OR ( ts.gl_accounted_flag = 'N' ) )
AND i.expenditure_id = e.expenditure_id
AND ( ( e.incurred_by_person_id = X_person_id )
OR
( ( e.incurred_by_person_id IS NULL ) AND
( e.incurred_by_organization_id = X_org_id ) ) )
AND nvl( i.organization_id, -1 ) = nvl( X_nl_org_id, -1 )
AND nvl( i.non_labor_resource, 'DUMMY' ) =
nvl( X_nl_resource, 'DUMMY' )
AND i.transaction_source ||'' = X_trx_source
AND nvl( i.net_zero_adjustment_flag, 'N' ) = 'N'
AND pa_adjustments.ei_adjusted_in_cache(i.expenditure_item_id) = 'N'
-- SSt changes: If we have the orig_txn_ref of the reversing item,
-- then the item we find must have the matching orig_txn_reference.
-- If we don't have the orig_txn_reference of the reversing item,
-- then the item we find does not need to satisfy the following
-- condition
AND (i.orig_transaction_reference = X_reversed_orig_txn_reference OR
X_reversed_orig_txn_reference IS NULL)
GROUP BY decode(X_reversed_orig_txn_reference,NULL,
i.orig_transaction_reference,
X_reversed_orig_txn_reference);
DELETE FROM pa_expenditure_comments
WHERE
expenditure_item_id = X_exp_item_id;
UPDATE pa_expenditure_comments
SET
expenditure_comment = X_new_comment
, last_update_date = sysdate
, last_updated_by = X_user
, last_update_login = X_login
WHERE
expenditure_item_id = X_exp_item_id;
SELECT count(*)
INTO dummy
FROM sys.dual
WHERE EXISTS
( SELECT NULL
FROM pa_expenditure_comments
WHERE expenditure_item_id = X_exp_item_id);
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date
, rejection_code)
VALUES (
X_exp_item_id -- expenditure_item_id
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, sysdate -- activity_date
, X_adj_activity -- exception_activity_code
, X_module -- module_code
, X_who_req_id -- request_id
, X_who_prog_app_id -- program_application_id
, X_who_prog_id -- program_id
, X_who_prog_upd_date -- program_update_date
, X_rejection_code ); -- rejection_code
UPDATE pa_expenditure_items_all ei
SET
ei.net_zero_adjustment_flag = 'Y'
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = X_exp_item_id;
INSERT INTO pa_expenditure_items_all(
expenditure_item_id
, task_id
, expenditure_type
, system_linkage_function
, expenditure_item_date
, expenditure_id
, override_to_organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, quantity
, revenue_distributed_flag
, bill_hold_flag
, billable_flag
, bill_rate_multiplier
, cost_distributed_flag
, raw_cost
, raw_cost_rate
, burden_cost
, burden_cost_rate
, cost_ind_compiled_set_id
, non_labor_resource
, organization_id
, adjusted_expenditure_item_id
, net_zero_adjustment_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, transferred_from_exp_item_id
, transaction_source
, orig_transaction_reference
, source_expenditure_item_id
, job_id
, org_id
, labor_cost_multiplier_name
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, acct_currency_code
, 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
, cc_cross_charge_code
, cc_prvdr_organization_id
, cc_recvr_organization_id
, cc_rejection_code
, denom_tp_currency_code
, denom_transfer_price
, acct_tp_rate_type
, acct_tp_rate_date
, acct_tp_exchange_rate
, acct_transfer_price
, projacct_transfer_price
, cc_markup_base_code
, tp_base_amount
, cc_cross_charge_type
, recvr_org_id
, cc_bl_distributed_code
, cc_ic_processed_code
, tp_ind_compiled_set_id
, tp_bill_rate
, tp_bill_markup_percentage
, tp_schedule_line_percentage
, tp_rule_percentage
, cost_job_id
, tp_job_id
, prov_proj_bill_job_id
, assignment_id
, work_type_id
, projfunc_currency_code
, projfunc_cost_rate_date
, projfunc_cost_rate_type
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
, project_id
, project_tp_rate_date
, project_tp_rate_type
, project_tp_exchange_rate
, project_transfer_price
, tp_amt_type_code
/* inserting cost_burden_distributed_flag for 2661921 */
, cost_burden_distributed_flag
, capital_event_id
, wip_resource_id
, inventory_item_id
, unit_of_measure
/* R12 Changes - Start */
, document_header_id
, document_distribution_id
, document_line_number
, document_payment_id
, vendor_id
, document_type
, document_distribution_type )
/* R12 Changes - End */
SELECT
X_backout_id -- expenditure_item_id
, ei.task_id -- task_id
, ei.expenditure_type -- expenditure_type
, ei.system_linkage_function -- system_linkage_function
, ei.expenditure_item_date -- expenditure_item_date
, nvl( X_expenditure_id,
ei.expenditure_id ) -- expenditure_id
, ei.override_to_organization_id -- override exp organization
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, (0 - ei.quantity) -- quantity
, 'N' -- revenue_distributed_flag
, ei.bill_hold_flag -- bill_hold_flag
, ei.billable_flag -- billable_flag
, ei.bill_rate_multiplier -- bill_rate_multiplier
, 'N' -- cost_distributed_flag
, (0 - ei.raw_cost) -- raw_cost
, ei.raw_cost_rate -- raw_cost_rate
, (0 - ei.burden_cost) -- raw_cost
, ei.burden_cost_rate -- raw_cost_rate
, ei.cost_ind_compiled_set_id -- cost_ind_compiled_set_id
, ei.non_labor_resource -- non_labor_resource
, ei.organization_id -- organization_id
, ei.expenditure_item_id -- adjusted_expenditure_item_id
, 'Y' -- net_zero_adjustment_flag
, ei.attribute_category -- attribute_category
, ei.attribute1 -- attribute1
, ei.attribute2 -- attribute2
, ei.attribute3 -- attribute3
, ei.attribute4 -- attribute4
, ei.attribute5 -- attribute5
, ei.attribute6 -- attribute6
, ei.attribute7 -- attribute7
, ei.attribute8 -- attribute8
, ei.attribute9 -- attribute9
, ei.attribute10 -- attribute10
, ei.transferred_from_exp_item_id -- tfr from exp item id
, ei.transaction_source -- transaction_source
, decode(ei.transaction_source,'PTE TIME',NULL,
decode(ei.transaction_source,'PTE EXPENSE',NULL,
decode(ei.transaction_source,'ORACLE TIME AND LABOR',NULL,
decode(ei.transaction_source,'Oracle Self Service Time',NULL,
ei.orig_transaction_reference)))) -- orig_transaction_reference
, ei.source_expenditure_item_id -- source_expenditure_item_id
, ei.job_id -- job_id
, ei.org_id -- org_id
, ei.labor_cost_multiplier_name -- labor_cost_multiplier_name
, (0 - ei.receipt_currency_amount) -- receipt_currency_amount
, ei.receipt_currency_code -- receipt_currency_code
, ei.receipt_exchange_rate -- receipt_exchange_rate
, ei.denom_currency_code -- denom_currency_code
, (0 - ei.denom_raw_cost) -- denom_raw_cost
, (0 - ei.denom_burdened_cost) -- denom_burdened_cost
, ei.acct_currency_code -- acct_currency_code
, ei.acct_rate_date -- acct_rate_date
, ei.acct_rate_type -- acct_rate_type
, ei.acct_exchange_rate -- acct_exchange_rate
, (0 - ei.acct_raw_cost) -- acct_raw_cost
, (0 - ei.acct_burdened_cost) -- acct_burdened_cost
, ei.acct_exchange_rounding_limit -- acct_exchange_rounding_limit
, ei.project_currency_code -- project_currency_code
, ei.project_rate_date -- project_rate_date
, ei.project_rate_type -- project_rate_type
, ei.project_exchange_rate -- project_exchange_rate
, ei.cc_cross_charge_code -- cc_cross_charge_code
, ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
, ei.cc_recvr_organization_id -- cc_recvr_organization_id
, ei.cc_rejection_code -- cc_rejection_code
, ei.denom_tp_currency_code -- denom_tp_currency_code
, (0 - ei.denom_transfer_price) -- denom_transfer_price
, ei.acct_tp_rate_type -- acct_tp_rate_type
, ei.acct_tp_rate_date -- acct_tp_rate_date
, ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
, (0 - ei.acct_transfer_price) -- acct_transfer_price
, (0 - ei.projacct_transfer_price) -- projacct_transfer_price
, ei.cc_markup_base_code -- cc_markup_base_code
, (0 - ei.tp_base_amount) -- tp_base_amount
, ei.cc_cross_charge_type -- cc_cross_charge_type
, ei.recvr_org_id -- recvr_org_id
, ei.cc_bl_distributed_code -- cc_bl_distributed_code
, ei.cc_ic_processed_code -- cc_ic_processed_code
, ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
, ei.tp_bill_rate -- tp_bill_rate
, ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
, ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
, ei.tp_rule_percentage -- tp_rule_percentage
, ei.cost_job_id -- cost_job_id
, ei.tp_job_id -- tp_job_id
, ei.prov_proj_bill_job_id -- prov_proj_bill_job_id
, ei.assignment_id
, ei.work_type_id
, ei.projfunc_currency_code
, ei.projfunc_cost_rate_date
, ei.projfunc_cost_rate_type
, ei.projfunc_cost_exchange_rate
, (0 - ei.project_raw_cost) -- project raw cost
, (0 - ei.project_burdened_cost) -- project burended cost
, ei.project_id
, ei.project_tp_rate_date
, ei.project_tp_rate_type
, ei.project_tp_exchange_rate
, (0 - ei.project_transfer_price)
, ei.tp_amt_type_code
/* inserting cost_burden_distributed_flag for 2661921 */
, decode(ei.cost_ind_compiled_set_id,null,'X','N')
, capital_event_id
, wip_resource_id
, inventory_item_id
, unit_of_measure
/* R12 Changes - Start */
, ei.document_header_id
, ei.document_distribution_id
, ei.document_line_number
, ei.document_payment_id
, ei.vendor_id ei_vendor_id
, ei.document_type
, ei.document_distribution_type
/* R12 Changes - End */
FROM
pa_expenditure_items_all ei
WHERE
ei.expenditure_item_id = X_exp_item_id;
SELECT
ec.expenditure_comment
INTO
item_comment
FROM
pa_expenditure_comments ec
WHERE
ec.expenditure_item_id = X_exp_item_id;
IF ( G_update_mrc_data = 'Y' ) THEN
BackOutMrcItem(X_exp_item_id =>X_exp_item_id,
X_backout_id => X_backout_id,
X_adj_activity => X_adj_activity,
X_module => X_module,
X_user => X_user,
X_login => X_login,
X_status => temp_status);
INSERT into pa_mc_exp_items_all(
SET_OF_BOOKS_ID,
EXPENDITURE_ITEM_ID,
RAW_COST,
RAW_COST_RATE,
BURDEN_COST,
BURDEN_COST_RATE,
NET_ZERO_ADJUSTMENT_FLAG,
TRANSFERRED_FROM_EXP_ITEM_ID,
PRC_ASSIGNMENT_ID,
CURRENCY_CODE,
COST_EXCHANGE_RATE,
COST_CONVERSION_DATE,
COST_RATE_TYPE,
TRANSFER_PRICE,
TP_EXCHANGE_RATE,
TP_CONVERSION_DATE,
TP_RATE_TYPE)
SELECT SET_OF_BOOKS_ID,
X_backout_id,
-1*RAW_COST,
RAW_COST_RATE,
-1*BURDEN_COST,
BURDEN_COST_RATE,
'Y', -- net_zero_adjustment_flag
NULL,-- Transferred_from_expenditure_item_id
PRC_ASSIGNMENT_ID,
CURRENCY_CODE,
COST_EXCHANGE_RATE,
COST_CONVERSION_DATE,
COST_RATE_TYPE,
-1*TRANSFER_PRICE,
TP_EXCHANGE_RATE,
TP_CONVERSION_DATE,
TP_RATE_TYPE
FROM pa_mc_exp_items_all
WHERE expenditure_item_id = X_exp_item_id;
SELECT
ei.expenditure_item_id
FROM
pa_expenditure_items_all ei
WHERE
ei.source_expenditure_item_id = X_source_exp_item_id
AND nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y';
Update pa_expenditure_items_all ei
set ei.source_expenditure_item_id =
(select ei1.expenditure_item_id
from pa_expenditure_items_all ei1
where ei1.adjusted_expenditure_item_id =
X_source_exp_item_id)
where ei.adjusted_expenditure_item_id = eachRec.expenditure_item_id;
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
revenue_distributed_flag = 'N'
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all ei
SET
ei.revenue_distributed_flag = 'N'
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all eia
SET
eia.revenue_distributed_flag = 'N'
, eia.last_update_date = sysdate
, eia.last_updated_by = X_user
, eia.last_update_login = X_login
WHERE
eia.expenditure_item_id = AdjustsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cost_distributed_flag = 'N'
, revenue_distributed_flag = 'N'
, denom_raw_cost = NULL
, raw_cost_rate = NULL
, acct_raw_cost = NULL
, raw_cost = NULL
/* Added denom_currency_code for Bug#2291180 */
, denom_currency_code = pa_adjustments.get_denom_curr_code(transaction_source,expenditure_type,denom_currency_code,acct_currency_code,system_linkage_function)
, denom_burdened_cost = NULL
, acct_burdened_cost = NULL
, burden_cost_rate = NULL
, burden_cost = NULL
/* Begin Burdening Changes - PA.L */
, adjustment_type = 'RECALC_RAW'
/* End Burdening Changes - PA.L */
, project_raw_cost = NULL
, project_burdened_cost = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
SELECT
nvl( txs.costed_flag, 'N' )
, nvl(txs.gl_accounted_flag,'N')
, nvl(txs.allow_burden_flag, 'N')
, ei.system_linkage_function INTO
trx_source_costed
, trx_source_accounted
, trx_source_burden
, system_linkage
FROM
pa_expenditure_items_all ei
, pa_transaction_sources txs
WHERE
ei.transaction_source = txs.transaction_source(+)
AND ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
/* Reverted: Added for bug #2027985
decode( system_linkage, 'VI', ei.cost_distributed_flag,
'ER', ei.cost_distributed_flag,
decode( trx_source_costed, 'N', 'N', ei.cost_distributed_flag ) )
End of fix for #2027985 */
, ei.revenue_distributed_flag = 'N'
, ei.denom_raw_cost =
decode( system_linkage, 'VI', ei.denom_raw_cost,
'ER', ei.denom_raw_cost,
decode( trx_source_costed, 'N', NULL, ei.denom_raw_cost ) )
, ei.raw_cost_rate =
decode( system_linkage, 'VI', ei.raw_cost_rate,
'ER', ei.raw_cost_rate,
decode( trx_source_costed, 'N', NULL, ei.raw_cost_rate ) )
, ei.acct_raw_cost =
decode( system_linkage, 'VI', ei.acct_raw_cost,
'ER', ei.acct_raw_cost, /* Bug 5191357 */
decode( trx_source_accounted, 'N', NULL, ei.acct_raw_cost) )
, ei.raw_cost = NULL
, ei.denom_burdened_cost = decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.denom_burdened_cost, NULL),decode(getprojburdenflag(ei.project_id),'N',NULL,ei.denom_burdened_cost) ) /*Added for bug:7157616*/
/* Added denom_currency_code for Bug#2291180 */
, ei.denom_currency_code = pa_adjustments.get_denom_curr_code(ei.transaction_source,ei.expenditure_type, ei.denom_currency_code, ei.acct_currency_code, ei.system_linkage_function)
, ei.acct_burdened_cost = NULL
, ei.burden_cost_rate = NULL
, ei.burden_cost = NULL
/* Begin Burdening Changes - PA.L */
, ei.adjustment_type = 'RECALC_RAW'
/* End Burdening Changes - PA.L */
, ei.project_raw_cost = NULL
, ei.project_burdened_cost = NULL
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
/* Reverted : Added for bug #2027985
decode( system_linkage, 'VI', ei.cost_distributed_flag,
'ER', ei.cost_distributed_flag,
decode( trx_source_costed, 'N', 'N', ei.cost_distributed_flag ) )
End of fix for #2027985 */
, ei.revenue_distributed_flag = 'N'
, ei.denom_raw_cost =
decode( system_linkage, 'VI', ei.denom_raw_cost,
'ER', ei.denom_raw_cost,
decode( trx_source_costed, 'N', NULL, ei.denom_raw_cost ) )
, ei.raw_cost_rate =
decode( system_linkage, 'VI', ei.raw_cost_rate,
'ER', ei.raw_cost_rate,
decode( trx_source_costed, 'N', NULL, ei.raw_cost_rate ) )
, ei.acct_raw_cost =
decode( system_linkage, 'VI', ei.acct_raw_cost,
'ER', ei.acct_raw_cost,
decode( trx_source_accounted, 'N', NULL, ei.acct_raw_cost ) )
, ei.raw_cost = NULL
, ei.denom_burdened_cost =
decode( trx_source_burden, 'N', NULL, decode(getprojburdenflag(ei.project_id),'N',NULL,ei.denom_burdened_cost) ) /*Added for bug:7157616*/
/* Added denom_currency_code for Bug#2291180 */
, ei.denom_currency_code = pa_adjustments.get_denom_curr_code(ei.transaction_source,ei.expenditure_type, ei.denom_currency_code, ei.acct_currency_code, ei.system_linkage_function)
, ei.acct_burdened_cost = NULL
, ei.burden_cost_rate = NULL
, ei.burden_cost = NULL
, ei.project_burdened_cost = NULL
, ei.project_raw_cost = NULL
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = AdjustsIdTab(i);
SELECT
nvl(txs.allow_burden_flag, 'N'),
ei.system_linkage_function
INTO
trx_source_burden,
system_linkage
FROM
pa_expenditure_items_all ei
, pa_transaction_sources txs
WHERE
ei.transaction_source = txs.transaction_source(+)
AND ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
, ei.denom_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.denom_burdened_cost, NULL), ei.denom_burdened_cost )
, ei.acct_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.acct_burdened_cost ,NULL),ei.acct_burdened_cost)
, ei.burden_cost_rate =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC',ei.burden_cost_rate ,NULL),ei.burden_cost_rate)
, ei.burden_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC',ei.burden_cost,NULL),ei.burden_cost)
, ei.project_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC',ei.project_burdened_cost,NULL),ei.project_burdened_cost)
/* Begin Burdening Changes - PA.L */
, ei.adjustment_type = decode(ei.adjustment_type, 'RECALC_RAW', ei.adjustment_type
, decode(ei.system_linkage_function, 'BTC', 'RECALC_RAW', 'RECALC_BURDEN'))
/* End Burdening Changes - PA.L */
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all eia
SET
eia.cost_distributed_flag = 'N'
, eia.denom_burdened_cost =
decode( trx_source_burden, 'N', NULL, eia.denom_burdened_cost )
, eia.acct_burdened_cost = NULL
, eia.burden_cost_rate = NULL
, eia.burden_cost = NULL
, eia.project_burdened_cost = NULL
, eia.last_update_date = sysdate
, eia.last_updated_by = X_user
, eia.last_update_login = X_login
WHERE
eia.expenditure_item_id = AdjustsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cost_distributed_flag = 'N'
, revenue_distributed_flag = 'N'
, denom_raw_cost = NULL
, acct_raw_cost = NULL
, raw_cost = NULL
, raw_cost_rate = NULL
/* Added denom_currency_code for Bug#2291180 */
, denom_currency_code = pa_adjustments.get_denom_curr_code(transaction_source,expenditure_type,denom_currency_code,acct_currency_code,system_linkage_function)
, denom_burdened_cost = NULL
, acct_burdened_cost = NULL
, burden_cost_rate = NULL
, burden_cost = NULL
/* Begin Burdening Changes - PA.L */
, adjustment_type = 'RECALC_RAW'
/* End Burdening Changes - PA.L */
, project_raw_cost = NULL
, project_burdened_cost = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
SELECT
nvl( txs.costed_flag, 'N' )
, nvl(txs.gl_accounted_flag,'N')
, nvl(txs.allow_burden_flag, 'N')
, ei.system_linkage_function
INTO
trx_source_costed
, trx_source_accounted
, trx_source_burden
, system_linkage
FROM
pa_expenditure_items_all ei
, pa_transaction_sources txs
WHERE
ei.transaction_source = txs.transaction_source(+)
AND ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
/* Reverted: Added for bug #2027985
decode( system_linkage, 'VI', ei.cost_distributed_flag,
'ER', ei.cost_distributed_flag,
decode( trx_source_costed, 'N', 'N', ei.cost_distributed_flag ) )
End of fix for #2027985 */
, ei.revenue_distributed_flag = 'N'
, ei.denom_raw_cost =
decode( system_linkage, 'VI', ei.denom_raw_cost,
'ER', ei.denom_raw_cost,
decode( trx_source_costed, 'N', NULL, ei.denom_raw_cost ) )
, ei.raw_cost_rate =
decode( system_linkage, 'VI', ei.raw_cost_rate,
'ER', ei.raw_cost_rate,
decode( trx_source_costed, 'N', NULL, ei.raw_cost_rate ) )
, ei.acct_raw_cost =
decode( system_linkage, 'VI', ei.acct_raw_cost,
'ER', ei.acct_raw_cost,
decode( trx_source_accounted, 'N', NULL, ei.acct_raw_cost ) )
, ei.raw_cost = NULL
, ei.denom_burdened_cost =decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.denom_burdened_cost, NULL)
,decode(getprojburdenflag(ei.project_id),'N',NULL,ei.denom_burdened_cost)) /*added for bug:7157616*/
/* Added denom_currency_code for Bug#2291180 */
, ei.denom_currency_code = pa_adjustments.get_denom_curr_code(ei.transaction_source,ei.expenditure_type, ei.denom_currency_code, ei.acct_currency_code, ei.system_linkage_function)
, ei.acct_burdened_cost = NULL
, ei.burden_cost_rate = NULL
, ei.burden_cost = NULL
/* Begin Burdening Changes - PA.L */
, ei.adjustment_type = 'RECALC_RAW'
/* End Burdening Changes - PA.L */
, ei.project_raw_cost = null
, ei.project_burdened_cost = null
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag ='N'
/* Reverted: Added for bug #2027985
decode( system_linkage, 'VI', ei.cost_distributed_flag,
'ER', ei.cost_distributed_flag,
decode( trx_source_costed, 'N', 'N', ei.cost_distributed_flag ) )
End of fix for #2027985 */
, ei.revenue_distributed_flag = 'N'
, ei.denom_raw_cost =
decode( system_linkage, 'VI', ei.denom_raw_cost,
'ER', ei.denom_raw_cost,
decode( trx_source_costed, 'N', NULL, ei.denom_raw_cost ) )
, ei.raw_cost_rate =
decode( system_linkage, 'VI', ei.raw_cost_rate,
'ER', ei.raw_cost_rate,
decode( trx_source_costed, 'N', NULL, ei.raw_cost_rate ) )
, ei.acct_raw_cost =
decode( system_linkage, 'VI', ei.acct_raw_cost,
'ER', ei.acct_raw_cost,
decode( trx_source_accounted, 'N', NULL, ei.acct_raw_cost ) )
, ei.denom_burdened_cost =
decode( trx_source_burden, 'N', NULL, decode(getprojburdenflag(ei.project_id),'N',NULL,ei.denom_burdened_cost) ) /*added for bug:7157616*/
/* Added denom_currency_code for Bug#2291180 */
, ei.denom_currency_code = pa_adjustments.get_denom_curr_code(ei.transaction_source,ei.expenditure_type, ei.denom_currency_code, ei.acct_currency_code, ei.system_linkage_function)
, ei.acct_burdened_cost = NULL
, ei.burden_cost_rate = NULL
, ei.burden_cost = NULL
, ei.raw_cost = NULL
, ei.project_burdened_cost = null
, ei.project_raw_cost = null
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = AdjustsIdTab(i);
SELECT
nvl(txs.allow_burden_flag, 'N'),
ei.system_linkage_function
INTO
trx_source_burden,
system_linkage
FROM
pa_expenditure_items_all ei
, pa_transaction_sources txs
WHERE
ei.transaction_source = txs.transaction_source(+)
AND ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
, ei.revenue_distributed_flag = 'N'
, ei.denom_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.denom_burdened_cost, NULL), ei.denom_burdened_cost )
, ei.acct_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.acct_burdened_cost,NULL), ei.acct_burdened_cost )
, ei.burden_cost_rate =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC', ei.burden_cost_rate , NULL), ei.burden_cost_rate )
, ei.burden_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC',ei.burden_cost , NULL ),ei.burden_cost )
, ei.project_burdened_cost =
decode( trx_source_burden, 'N',
decode(system_linkage, 'BTC',ei.project_burdened_cost , NULL ),ei.project_burdened_cost )
/* Begin Burdening Changes - PA.L */
, ei.adjustment_type = decode(ei.adjustment_type, 'RECALC_RAW', ei.adjustment_type
, decode(ei.system_linkage_function, 'BTC', 'RECALC_BURDEN'))
/* End Burdening Changes - PA.L */
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all eia
SET
eia.cost_distributed_flag = 'N'
, eia.denom_burdened_cost =
decode( trx_source_burden, 'N', NULL, eia.denom_burdened_cost )
, eia.acct_burdened_cost = NULL
, eia.burden_cost_rate = NULL
, eia.burden_cost = NULL
, eia.project_burdened_cost = NULL
, eia.last_update_date = sysdate
, eia.last_updated_by = X_user
, eia.last_update_login = X_login
WHERE
eia.expenditure_item_id = AdjustsIdTab(i);
select expenditure_item_id
from pa_expenditure_items_all peia
where exists( select null
from pa_cost_distribution_lines_all cdl
where cdl.expenditure_item_id = peia.expenditure_item_id)
start with expenditure_item_id = X_expenditure_item_id
connect by prior transferred_from_exp_item_id = expenditure_item_id ;
select system_reference2 into v_system_reference2
from pa_cost_distribution_lines_all
where expenditure_item_id = rec.expenditure_item_id
and line_num_reversed is null
and reversed_flag is null
and line_type = 'R';
SELECT CANCELLED_DATE,
CANCELLED_BY
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id
FOR UPDATE OF INVOICE_ID NOWAIT;
UPDATE pa_expenditure_items_all ei
SET
ei.bill_hold_flag = X_hold
, ei.last_updated_by = X_user
, ei.last_update_date = sysdate
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all eia
SET
eia.bill_hold_flag = X_hold
, eia.last_updated_by = X_user
, eia.last_update_date = sysdate
, eia.last_update_login = X_login
WHERE
eia.expenditure_item_id = AdjustsIdTab(i);
* then update EI with cost_distributed_flag = 'N' so that
* costing process will generate reverse and new cdls
* IF change in work type NOT results in chagne in billablity
* then create new and reverse cdls with transfer status_code = 'G'
* and Util summarize_flag = 'N' and update EI with cost_distributed_flag = 'Y'
* so that the cdls are not picked up by summarized programm
* If PA: Transaction Billability derived from Work Type = NO
* work type and Billabiltiy are two independent process so
* then create new and reverse cdls with transfer status_code = 'G'
* and Util summarize_flag = 'N'
* To change the billability of the transaction user has to run the
* Reclass Billable / Non Billable adjustments seperately
**/
PROCEDURE work_type_adjustment
( ItemsIdTab IN pa_utils.IdTabTyp
--, AdjustsIdTab IN pa_utils.IdTabTyp
, p_billable IN VARCHAR2
, p_work_type_id IN NUMBER
, p_adj_activity IN VARCHAR2
, p_user IN NUMBER
, p_login IN NUMBER
, p_module IN VARCHAR2
, p_rows IN BINARY_INTEGER
, p_TpAmtTypCodeTab IN pa_utils.Char30TabTyp
, p_dest_tp_amt_type_code IN VARCHAR2
, x_status OUT NOCOPY NUMBER )
IS
l_temp_status NUMBER DEFAULT NULL;
print_message('get_denom_curr_code: ' || 'inside cdl_creation api before SELECT ');
SELECT
ei.transaction_source,
tr.gl_accounted_flag,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
tr.system_linkage_function,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
INTO
l_transaction_source,
l_gl_accounted_flag,
l_denom_currency_code,
l_acct_currency_code,
l_acct_rate_date,
l_acct_rate_type,
l_acct_exchange_rate,
l_project_currency_code,
l_project_rate_date,
l_project_rate_type,
l_project_exchange_rate,
l_system_linkage_function,
l_projfunc_currency_code,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_projfunc_cost_exchg_rate,
l_work_type_id
FROM pa_expenditure_items_all ei,
pa_transaction_sources tr
WHERE tr.transaction_source(+) = ei.transaction_source
AND expenditure_item_id = p_exp_item_id;
print_message('get_denom_curr_code: ' || 'inside cdl_creation api after SELECT ');
print_message ('get_denom_curr_code: ' || 'before select exp_item ='||ItemsIdTab(i));
SELECT EI.work_type_id
,nvl(tr.gl_accounted_flag,'N')
,pt.PROJECT_TYPE_CLASS_CODE
,nvl(ei.billable_flag ,'N')
INTO l_old_work_type_id
,l_gl_accounted_flag
,l_proj_type_class_code
,l_old_billability
FROM pa_expenditure_items_all EI
,pa_transaction_sources tr
,pa_project_types_all pt /** Bug fix 2262118 **/
,pa_projects_all pp
-- ,pa_tasks t /* Bug 3457922 */
WHERE tr.transaction_source(+) = ei.transaction_source
AND ei.expenditure_item_id = ItemsIdTab(i)
-- AND t.task_id = ei.task_id /* Bug 3457922 */
-- AND pp.project_id = t.project_id /* Bug 3457922 */
AND pp.project_id = ei.project_id /* Added : Bug 3457922 */
AND pp.project_type = pt.project_type
-- start 12i MOAC changes
-- AND nvl(pp.org_id ,-99) = nvl(pt.org_id ,-99) ;
SELECT nvl(BILLABLE_CAPITALIZABLE_FLAG,'N')
INTO l_billable_cap_flag
FROM pa_work_types_b -- bug 4668802 changed from pa_work_types_v to pa_work_types_b
WHERE work_type_id = p_work_type_id
AND trunc(sysdate) between start_date_active and
nvl(end_date_active,sysdate);
print_message ('get_denom_curr_code: ' || 'after select l_old_work_type_id ['||l_old_work_type_id||
']l_gl_accounted_flag['||l_gl_accounted_flag||']l_old_billability['||l_old_billability||']' );
** then EI will never be updated with the billablity change . in order to fix this
** donot derive old billabity based on worktype
l_old_billability := PA_UTILS4.get_trxn_work_billabilty
(p_work_type_id => l_old_work_type_id
,p_tc_extn_bill_flag => NULL );
print_message('get_denom_curr_code: ' || 'calling update of EI');
UPDATE pa_expenditure_items_all ei
SET ei.work_type_id = p_work_type_id
,ei.cost_distributed_flag =
decode(l_change_in_billable,
'Y', decode(l_gl_accounted_flag,
'N','N',
'Y',decode(l_reverse_cdl_status
,'S',decode(nvl(ei.cost_distributed_flag,'N'),'Y','Y','N')
,'N')
),
'N',decode(l_reverse_cdl_status,'S',
decode(nvl(ei.cost_distributed_flag,'N'),'Y','Y','N'),'N'))
,ei.revenue_distributed_flag = 'N'
,ei.billable_flag = decode(l_change_in_billable,'Y',l_new_billability,ei.billable_flag)
,ei.last_updated_by = p_user
,ei.last_update_date = sysdate
,ei.last_update_login = p_login
,ei.tp_amt_type_code = PA_UTILS4.get_tp_amt_type_code
(p_work_type_id)
,cc_bl_distributed_code = DECODE(p_dest_tp_amt_type_code, p_TpAmtTypCodeTab(i),
cc_bl_distributed_code,
decode(cc_bl_distributed_code,'X','X','N'))
,cc_ic_processed_code = DECODE(p_dest_tp_amt_type_code, p_TpAmtTypCodeTab(i),
cc_ic_processed_code,
decode(cc_ic_processed_code,'X','X','N'))
/* Begin Burdening Changes - PA.L */
,ei.adjustment_type = NULL
/* End Burdening Changes - PA.L */
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
print_message('get_denom_curr_code: ' || 'Num of rows updated ['||sql%rowcount||
']with work type ['||p_work_type_id||']' );
UPDATE pa_expenditure_items_all ei
SET ei.billable_flag = X_billable
, ei.revenue_distributed_flag = 'N'
, ei.cost_distributed_flag = 'N'
/* Begin Burdening Changes - PA.L */
, ei.adjustment_type = NULL
/* End Burdening Changes - PA.L */
, ei.last_updated_by = X_user
, ei.last_update_date = sysdate
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
SELECT ei.transaction_source,
tr.gl_accounted_flag,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
tr.system_linkage_function,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
INTO p_transaction_source,
p_gl_accounted_flag,
p_denom_currency_code,
p_acct_currency_code,
p_acct_rate_date,
p_acct_rate_type,
p_acct_exchange_rate,
p_project_currency_code,
p_project_rate_date,
p_project_rate_type,
p_project_exchange_rate,
p_system_linkage_function,
l_projfunc_currency_code,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_projfunc_cost_exchg_rate,
l_work_type_id
FROM pa_expenditure_items_all ei,
pa_transaction_sources tr
WHERE tr.transaction_source = ei.transaction_source
AND expenditure_item_id = ItemsIdTab(i) ;
UPDATE pa_expenditure_items_all
-- end 12i MOAC changes
SET cost_distributed_flag = 'Y'
WHERE expenditure_item_id = ItemsIdTab(i);
UPDATE pa_expenditure_items_all eia
SET
eia.billable_flag = X_billable
, eia.revenue_distributed_flag = 'N'
, eia.cost_distributed_flag = 'N'
, eia.last_updated_by = X_user
, eia.last_update_date = sysdate
, eia.last_update_login = X_login
WHERE
eia.expenditure_item_id = AdjustsIdTab(i);
SELECT ei.transaction_source,
tr.gl_accounted_flag,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
INTO p_transaction_source,
p_gl_accounted_flag,
p_denom_currency_code,
p_acct_currency_code,
p_acct_rate_date,
p_acct_rate_type,
p_acct_exchange_rate,
p_project_currency_code,
p_project_rate_date,
p_project_rate_type,
p_project_exchange_rate,
l_projfunc_currency_code,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_projfunc_cost_exchg_rate,
l_work_type_id
FROM pa_expenditure_items_all ei,
pa_transaction_sources tr
WHERE tr.transaction_source = ei.transaction_source
AND expenditure_item_id = AdjustsIdTab(i) ;
UPDATE pa_expenditure_items_all
-- end 12i MOAC changes
SET cost_distributed_flag = 'Y'
WHERE expenditure_item_id = AdjustsIdTab(i);
select cost_rate_flag
into l_cost_rate_flag
from pa_expenditure_types
where expenditure_type = l_exp_type;
SELECT
ec.expenditure_comment
INTO
item_comment
FROM
pa_expenditure_comments ec
WHERE
ec.expenditure_item_id = X_exp_item_id;
select
transaction_source,
expenditure_type,
denom_currency_code,
acct_currency_code,
system_linkage_function,
denom_raw_cost,
expenditure_id ,
expenditure_item_date
into
l_transaction_source,
l_exp_type,
l_denom_cur_code,
l_acct_cur_code,
l_sys_link_func,
l_denom_raw_cost,
l_expenditure_id,
l_expenditure_item_date
from
pa_expenditure_items_all
where
expenditure_item_id = X_exp_item_id;
select
Incurred_by_person_id
into
l_person_id
from
pa_expenditures_all
where expenditure_id = l_expenditure_id;
print_message('get_denom_curr_code: ' || 'calling insert into EI');
INSERT INTO pa_expenditure_items_all(
expenditure_item_id
, task_id
, expenditure_type
, system_linkage_function
, expenditure_item_date
, expenditure_id
, override_to_organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, quantity
, revenue_distributed_flag
, bill_hold_flag
, billable_flag
, bill_rate_multiplier
, cost_distributed_flag
, raw_cost
, raw_cost_rate
, burden_cost
, burden_cost_rate /*1765164*/
, non_labor_resource
, organization_id
, transferred_from_exp_item_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, transaction_source
, orig_transaction_reference /* Bug 2373450 */
, job_id
, org_id
, labor_cost_multiplier_name
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code
, project_rate_type
, project_rate_date
, project_exchange_rate
, cost_ind_compiled_set_id /* added for bug 1765164 */
/* For split, all the CC attributes are calculated again, following the
revenue/billing model */
/* , cc_cross_charge_code
, cc_prvdr_organization_id
, cc_recvr_organization_id
, cc_rejection_code
, denom_tp_currency_code
, denom_transfer_price
, acct_tp_rate_type
, acct_tp_rate_date
, acct_tp_exchange_rate
, acct_transfer_price
, projacct_transfer_price
, cc_markup_base_code
, tp_base_amount
, cc_cross_charge_type
, recvr_org_id
, cc_bl_distributed_code
, cc_ic_processed_code
, tp_ind_compiled_set_id
, tp_bill_rate
, tp_bill_markup_percentage
, tp_schedule_line_percentage
, tp_rule_percentage */
, recvr_org_id /*Bug# 2028917*/
, assignment_id
, work_type_id
, projfunc_currency_code
, projfunc_cost_rate_type
, projfunc_cost_rate_date
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
/** , project_tp_rate_type
, project_tp_rate_date
, project_tp_exchange_rate
, project_transfer_price **/
, project_id
, tp_amt_type_code /** added for bug 3117718 **/
, inventory_item_id -- Bug 4320459
, unit_of_measure -- Bug 4320459
/* R12 Changes - Start */
, document_header_id
, document_distribution_id
, document_line_number
, document_payment_id
, vendor_id
, document_type
, document_distribution_type
/* R12 Changes - End */
)
SELECT
new_item_id -- expenditure_item_id
, ei.task_id -- task_id
, ei.expenditure_type -- expenditure_type
, ei.system_linkage_function -- system_linkage_function
, ei.expenditure_item_date -- expenditure_item_date
, ei.expenditure_id -- expenditure_id
, ei.override_to_organization_id -- override_to_organization_id
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, item_qty -- quantity
, 'N' -- revenue_distributed_flag
, item_hold_flag -- bill_hold_flag
, item_bill_flag -- billable_flag
, ei.bill_rate_multiplier -- bill_rate_multiplier
, 'N' -- cost_distributed_flag
, item_raw_cost -- raw_cost
, ei.raw_cost_rate -- raw_cost_rate
, item_burden_cost -- burden_cost
, ei.burden_cost_rate -- burden_cost_rate /*1765164*/
, ei.non_labor_resource -- non_labor_resource
, ei.organization_id -- organization_id
, ei.expenditure_item_id -- adjusted_expenditure_item_id
, ei.attribute_category -- attribute_category
, ei.attribute1 -- attribute1
, ei.attribute2 -- attribute2
, ei.attribute3 -- attribute3
, ei.attribute4 -- attribute4
, ei.attribute5 -- attribute5
, ei.attribute6 -- attribute6
, ei.attribute7 -- attribute7
, ei.attribute8 -- attribute8
, ei.attribute9 -- attribute9
, ei.attribute10 -- attribute10
, ei.transaction_source -- transaction_source
, decode(ei.transaction_source,'PTE TIME',NULL,
decode(ei.transaction_source,'PTE EXPENSE',NULL,
decode(ei.transaction_source,'ORACLE TIME AND LABOR',NULL,
decode(ei.transaction_source,'Oracle Self Service Time',NULL,
ei.orig_transaction_reference)))) orig_transaction_reference /* Bug2373450 */
, ei.job_id -- job_id
, ei.org_id -- org_id
, ei.labor_cost_multiplier_name -- labor_cost_multiplier_name
, item_receipt_curr_amt -- receipt currency amount
, ei.receipt_currency_code -- receipt currency code
, ei.receipt_exchange_rate -- receipt exchange rate
/***, ei.denom_currency_code -- denomination currency code **/
, l_denom_currency_code -- denomiation currency code EFC bug2259454 changes
, item_denom_raw_cost -- denomination raw cost
, item_denom_burdened_cost -- denomination burdened cost
, ei.acct_currency_code -- accounting currency code
, ei.acct_rate_date -- accounting rate date
, ei.acct_rate_type -- accounting rate type
, ei.acct_exchange_rate -- accounting exchange rate
, item_acct_raw_cost -- accounting raw cost
, item_acct_burdened_cost -- accounting burdened cost
, ei.acct_exchange_rounding_limit -- accounting exchange rounding limit
, ei.project_currency_code -- project currency code
, ei.project_rate_type -- project rate type
, ei.project_rate_date -- project rate date
, ei.project_exchange_rate -- accounting exchange rate
, ei.cost_ind_compiled_set_id -- cost compiled set id added for 1765164
/* , ei.cc_cross_charge_code -- cc_cross_charge_code
, ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
, ei.cc_recvr_organization_id -- cc_recvr_organization_id
, ei.cc_rejection_code -- cc_rejection_code
, ei.denom_tp_currency_code -- denom_tp_currency_code
, ei.denom_transfer_price -- denom_transfer_price
, ei.acct_tp_rate_type -- acct_tp_rate_type
, ei.acct_tp_rate_date -- acct_tp_rate_date
, ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
, ei.acct_transfer_price -- acct_transfer_price
, ei.projacct_transfer_price -- projacct_transfer_price
, ei.cc_markup_base_code -- cc_markup_base_code
, ei.tp_base_amount -- tp_base_amount
, ei.cc_cross_charge_type -- cc_cross_charge_type
, ei.recvr_org_id -- recvr_org_id
, ei.cc_bl_distributed_code -- cc_bl_distributed_code
, ei.cc_ic_processed_code -- cc_ic_processed_code
, ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
, ei.tp_bill_rate -- tp_bill_rate
, ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
, ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
, ei.tp_rule_percentage -- tp_rule_percentage */
, ei.recvr_org_id -- recvr_org_id /*Bug# 2028917*/
, assignment_id
, work_type_id
, projfunc_currency_code
, projfunc_cost_rate_type
, projfunc_cost_rate_date
, projfunc_cost_exchange_rate
, item_project_raw_cost
, item_project_burdened_cost
/* , project_tp_rate_type
, project_tp_rate_date
, project_tp_exchange_rate
, project_transfer_price **/
, project_id
, ei.tp_amt_type_code /** added for bug 3117718 **/
, ei.inventory_item_id -- Bug 4320459
, ei.unit_of_measure -- Bug 4320459
/* R12 Changes - Start */
, ei.document_header_id
, ei.document_distribution_id
, ei.document_line_number
, ei.document_payment_id
, ei.vendor_id
, ei.document_type
, ei.document_distribution_type
/* R12 Changes - End */
FROM
pa_expenditure_items_all ei
WHERE
ei.expenditure_item_id = X_exp_item_id;
print_message('get_denom_curr_code: ' || 'Num of rows inserted in split['||sql%rowcount||']' );
/* changing the cursor select below for the raw_cost and burden_cost
to make it null always. The costing program would fill in the raw cost
with the acct_raw_cost for VI and ER when the curr codes are same */
CURSOR Items ( X_expenditure_item_id IN NUMBER ) IS
SELECT
ei.billable_flag
, ei.bill_hold_flag
, NULL raw_cost
, decode(ei.system_linkage_function, 'VI',
ei2.raw_cost_rate,
decode(ts.costed_flag,
'Y', ei2.raw_cost_rate,
NULL)) raw_cost_rate
, NULL burden_cost
/*, decode( ei.system_linkage_function, 'VI',
ei.override_to_organization_id, NULL ) override_to_org */
/* Modified For Bug2235662 */
, decode(ei.system_linkage_function,
'VI',ei.override_to_organization_id,
'ER',decode(ei.transaction_source,
null,null,
ei.override_to_organization_id),
null) override_to_org
, ei.organization_id nl_resource_org_id
, ei.transaction_source
, ei.vendor_id vendor_id /* R12 Changes - removed reference to cdl.system_reference1 */
, ei.attribute_category
, ei.attribute1
, ei.attribute2
, ei.attribute3
, ei.attribute4
, ei.attribute5
, ei.attribute6
, ei.attribute7
, ei.attribute8
, ei.attribute9
, ei.attribute10
, ec.expenditure_comment
, decode(ei.system_linkage_function, 'VI',
NVL(ei.override_to_organization_id,e.incurred_by_organization_id), /*Modified for bug 7422380*/
e.incurred_by_organization_id ) inc_by_org_id
, e.incurred_by_person_id inc_by_person_id
, ei.expenditure_item_date
, decode(ei.transaction_source,'PTE TIME',NULL,
decode(ei.transaction_source,'PTE EXPENSE',NULL,
decode(ei.transaction_source,'ORACLE TIME AND LABOR',NULL,
decode(ei.transaction_source,'Oracle Self Service Time',NULL,
ei.orig_transaction_reference)))) orig_transaction_reference
, ei.expenditure_type
, ei.system_linkage_function
, ei.non_labor_resource
, ei.quantity
, e.expenditure_id
, ei.expenditure_item_id transferred_from_exp_item_id
, ei.adjusted_expenditure_item_id expenditure_item_id
, ei.job_id
, ei.org_id
, ei.burden_sum_dest_run_id
, ei.receipt_currency_amount
, ei.receipt_currency_code
, ei.receipt_exchange_rate
, ei.denom_currency_code
, decode(ei.system_linkage_function, 'VI', ei2.denom_raw_cost,
'ER', ei2.denom_raw_cost,decode( ts.costed_flag, 'Y',
ei2.denom_raw_cost, NULL)) denom_raw_cost
, decode( ts.allow_burden_flag, 'Y',decode(getprojburdenflag(ei.project_id),'N',NULL,
ei2.denom_burdened_cost), NULL) denom_burdened_cost /*Bug# 5874347*/
, ei.acct_currency_code
, decode(ei.system_linkage_function,'VI',ei2.acct_rate_date,
'ER',ei2.acct_rate_date, decode(ts.gl_accounted_flag,'Y',
ei2.acct_rate_date,decode(ei.acct_rate_type,'User',ei.acct_rate_date,NULL))) acct_rate_date --Bug#3787213
, decode(ei.system_linkage_function,'VI',ei2.acct_rate_type,
'ER',ei2.acct_rate_type, decode(ts.gl_accounted_flag,'Y',
ei2.acct_rate_type,decode(ei.acct_rate_type,'User',ei.acct_rate_type,NULL))) acct_rate_type --Bug#3787213
, decode(ei.system_linkage_function,'VI',ei2.acct_exchange_rate,
'ER',ei2.acct_exchange_rate, decode(ts.gl_accounted_flag,'Y',
ei2.acct_exchange_rate,decode(ei.acct_rate_type,'User',ei.acct_exchange_rate,NULL))) acct_exchange_rate --Bug#3787213
, decode(ei.system_linkage_function, 'VI', ei2.acct_raw_cost,
'ER', ei2.acct_raw_cost, decode( ts.gl_accounted_flag, 'Y',
ei2.acct_raw_cost, NULL)) acct_raw_cost
, ei.acct_exchange_rounding_limit
, decode(ei.system_linkage_function, 'VI','Y', 'ER','Y',
nvl(ts.gl_accounted_flag,'N')) gl_accounted_flag
/* , ei.cc_cross_charge_code -- cc_cross_charge_code
, ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
, ei.cc_recvr_organization_id -- cc_recvr_organization_id
, ei.cc_rejection_code -- cc_rejection_code
, ei.denom_tp_currency_code -- denom_tp_currency_code
, ei.denom_transfer_price -- denom_transfer_price
, ei.acct_tp_rate_type -- acct_tp_rate_type
, ei.acct_tp_rate_date -- acct_tp_rate_date
, ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
, ei.acct_transfer_price -- acct_transfer_price
, ei.projacct_transfer_price -- projacct_transfer_price
, ei.cc_markup_base_code -- cc_markup_base_code
, ei.tp_base_amount -- tp_base_amount
, ei.cc_cross_charge_type -- cc_cross_charge_type
, ei.recvr_org_id -- recvr_org_id
, ei.cc_bl_distributed_code -- cc_bl_distributed_code
, ei.cc_ic_processed_code -- cc_ic_processed_code
, ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
, ei.tp_bill_rate -- tp_bill_rate
, ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
, ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
, ei.tp_rule_percentage -- tp_rule_percentage
*/
, NULL project_raw_cost
, NULL project_burdened_cost
, ei.assignment_id
, ei.work_type_id
, ei.project_rate_type
, ei.project_rate_date
, ei.project_exchange_rate
, e.person_type -- Fix for bug : 3681318
, decode(ei.system_linkage_function,'BTC',ei.acct_burdened_cost,null) acct_burdened_cost /* bug 3669152 */
, ei.project_currency_code --Start of Bug#3787213. 5 Columns added
, ei.projfunc_currency_code
, ei.projfunc_cost_rate_type
, ei.projfunc_cost_rate_date
, ei.projfunc_cost_exchange_rate --End of Bug#3787213.
, ei.inventory_item_id -- Bug 4320459
, ei.unit_of_measure -- Bug 4320459
/* R12 Changes -Start */
, ei.document_header_id
, ei.document_distribution_id
, ei.document_line_number
, ei.document_payment_id
, ei.vendor_id ei_vendor_id
, ei.document_type
, ei.document_distribution_type
/* R12 Changes - End */
FROM
pa_expenditures_all e
, pa_expenditure_items_all ei
, pa_expenditure_items_all ei2
, pa_transaction_sources ts
, pa_expenditure_comments ec
/* R12 changes - Removed table pa_cost_distribution_lines */
WHERE
e.expenditure_id = ei.expenditure_id
AND ei.expenditure_item_id = ei2.expenditure_item_id
/* R12 Changes - Removed join conditions for pa_cost_distribution_lines table */
AND ei2.transaction_source = ts.transaction_source (+)
AND ei.expenditure_item_id = X_expenditure_item_id
AND ec.expenditure_item_id (+)= ei.expenditure_item_id ;
select cost_rate_flag
into l_cost_rate_flag
from pa_expenditure_types
where expenditure_type = l_exp_type;
SELECT decode(project_currency_code,NULL,projfunc_currency_code,project_currency_code)
INTO l_project_currency_code
FROM pa_projects_all
WHERE project_id = x_dest_prj_id;
SELECT w.name
INTO l_work_type_name
FROM pa_work_types_v w
WHERE w.work_type_id = eirec.work_type_id;
select decode(getprojburdenflag(X_dest_prj_id), -- Bug#Bug6031129
'N',null,EiRec.denom_burdened_cost)
into l_denom_burdened_cost
from dual;
SELECT
ei.expenditure_item_id
, eia.expenditure_item_id adj_expenditure_item_id
FROM
pa_expenditures_all e
, pa_expenditure_items_all ei
, pa_expenditure_items_all eia
WHERE
ei.expenditure_item_id = eia.adjusted_expenditure_item_id (+)
AND ei.expenditure_id = e.expenditure_id
AND ei.task_id IN
( SELECT
t.task_id
FROM
pa_tasks t
WHERE
t.project_id = X_project_id
AND t.task_id = nvl( X_task_id, t.task_id )
)
AND ( X_inc_by_person_id IS NULL
OR e.incurred_by_person_id = X_inc_by_person_id )
AND ( X_inc_by_org_id IS NULL
OR e.incurred_by_organization_id = X_inc_by_org_id
OR ( e.incurred_by_organization_id IS NULL
AND ei.override_to_organization_id = X_inc_by_org_id ))
AND ( X_vendor_id IS NULL
OR X_vendor_id = ei.vendor_id) /* R12 changes - Removed reference t ocdl.system_reference1 */
AND ( X_system_linkage IS NULL
OR ei.system_linkage_function = X_system_linkage )
/* commented the following lines for mfg changes and added the one line above
OR EXISTS
( SELECT NULL
FROM
pa_expenditure_types et
WHERE
et.expenditure_type = ei.expenditure_type
AND ei.system_linkage_function = X_system_linkage ))
*/
AND ei.expenditure_type =
nvl( X_expenditure_type, ei.expenditure_type )
AND ( X_nl_resource_org_id IS NULL
OR ei.organization_id = X_nl_resource_org_id )
AND ( X_nl_resource IS NULL
OR ei.non_labor_resource = X_nl_resource )
AND ( X_hold_flag IS NULL
OR ei.bill_hold_flag = decode( X_hold_flag, 'B',
decode( ei.bill_hold_flag, 'N', 'Z', ei.bill_hold_flag ),
ei.bill_hold_flag ) )
AND ei.expenditure_item_date BETWEEN
nvl( X_ei_date_low, ei.expenditure_item_date )
AND nvl( X_ei_date_high, ei.expenditure_item_date )
AND ei.adjusted_expenditure_item_id IS NULL
AND ( X_bill_status IS NULL
OR
( X_bill_status = 'U'
AND ei.project_id IS NULL
AND NOT EXISTS (
SELECT NULL
FROM
pa_proj_invoice_details_view idv
WHERE
idv.project_id+0 = X_project_id
AND idv.expenditure_item_id =
ei.expenditure_item_id ) )
OR
( X_bill_status IN ( 'P', 'R', 'B' )
AND EXISTS (
SELECT
NULL
FROM
pa_draft_invoices i
, pa_draft_invoice_items ii
WHERE
i.project_id+0 = X_project_id
AND i.project_id = ii.project_id
AND i.draft_invoice_num = ii.draft_invoice_num
AND nvl(ii.event_task_id, -1) =
nvl(ei.event_task_id, -1)
AND ii.project_id = ei.project_id
AND ii.event_num = ei.event_num
AND nvl( i.released_by_person_id, -1 ) =
decode( X_bill_status, 'P', -1,
'R', i.released_by_person_id,
'B', nvl( i.released_by_person_id, -1), -2)))
)
AND ( 1 = 2
OR ( X_adj_action = 'CHANGE WORK TYPE ATTRIBUTE' /** proj currency changes **/
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.work_type_id <> p_dest_work_type_id
AND e.expenditure_status_code = 'APPROVED'
)
OR
( X_adj_action = 'PROJECT OR TASK CHANGE'
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.task_id <> X_dest_task_id
AND e.expenditure_status_code = 'APPROVED'
AND ei.source_expenditure_item_id IS NULL
)
OR
( X_adj_action = 'BILLING HOLD'
AND ei.bill_hold_flag in ( 'N', 'O' ) )
OR
( X_adj_action = 'BILLING HOLD RELEASE'
AND ei.bill_hold_flag in ( 'Y', 'O' ) )
OR
( X_adj_action = 'BILLABLE RECLASS'
AND ei.billable_flag <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'NON-BILLABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'CAPITALIZABLE RECLASS'
AND ei.billable_flag <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND exists (select 1
From pa_tasks t
Where t.task_id = ei.task_id
And t.retirement_cost_flag = 'N')
)
OR
( X_adj_action = 'NON-CAPITALIZABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND exists (select 1
From pa_tasks t
Where t.task_id = ei.task_id
And t.retirement_cost_flag = 'N')
)
OR
( X_adj_action = 'REVENUE RECALC'
AND ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'ONE-TIME BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'Y' ) )
OR
( X_adj_action = 'COST AND REV RECALC'
AND ( ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
OR ( ei.cost_distributed_flag||'' = 'Y'
OR ei.denom_raw_cost IS NOT NULL ) )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND ei.source_expenditure_item_id IS NULL
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'CAPITAL COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' )
OR
( X_adj_action = 'RAW COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' )
OR
( X_adj_action = 'INDIRECT COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' ) )
FOR UPDATE OF ei.expenditure_item_id, eia.expenditure_item_id NOWAIT;
SELECT
ei.expenditure_item_id
, eia.expenditure_item_id adj_expenditure_item_id
FROM
pa_expenditures_all e
, pa_expenditure_items_all ei
, pa_expenditure_items_all eia
WHERE
ei.expenditure_item_id = eia.adjusted_expenditure_item_id (+)
AND ei.expenditure_id = e.expenditure_id
AND ei.expenditure_item_id = X_expenditure_item_id
AND ( 1 = 2
OR X_adj_action = 'EXP COMMENT CHANGE'
OR (X_adj_action = 'CHANGE WORK TYPE ATTRIBUTE' /** proj currency changes **/
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.work_type_id <> p_dest_work_type_id
AND e.expenditure_status_code = 'APPROVED'
)
OR
( X_adj_action = 'PROJECT OR TASK CHANGE'
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.task_id <> X_dest_task_id
AND e.expenditure_status_code = 'APPROVED'
AND ei.source_expenditure_item_id IS NULL
)
OR
( X_adj_action = 'BILLING HOLD'
AND ei.bill_hold_flag in ( 'N', 'O' ) )
OR
( X_adj_action = 'BILLING HOLD RELEASE'
AND ei.bill_hold_flag in ( 'Y', 'O' ) )
OR
( X_adj_action = 'BILLABLE RECLASS'
AND ei.billable_flag <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'NON-BILLABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'CAPITALIZABLE RECLASS'
AND ei.billable_flag <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND exists (select 1
From pa_tasks t
Where t.task_id = ei.task_id
And t.retirement_cost_flag = 'N')
)
OR
( X_adj_action = 'NON-CAPITALIZABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND exists (select 1
From pa_tasks t
Where t.task_id = ei.task_id
And t.retirement_cost_flag = 'N')
)
OR
( X_adj_action = 'REVENUE RECALC'
AND ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'ONE-TIME BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'Y' ) )
OR
( X_adj_action = 'COST AND REV RECALC'
AND ( ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
OR ( ei.cost_distributed_flag||'' = 'Y'
OR ei.denom_raw_cost IS NOT NULL ) )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND ei.source_expenditure_item_id IS NULL
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'CAPITAL COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' )
OR
( X_adj_action = 'RAW COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' )
OR
( X_adj_action = 'INDIRECT COST RECALC'
AND ei.cost_distributed_flag||'' = 'Y' ) )
FOR UPDATE OF ei.expenditure_item_id, eia.expenditure_item_id NOWAIT;
SELECT
ei.expenditure_item_id
, eia.expenditure_item_id adj_expenditure_item_id
FROM
pa_cust_rev_dist_lines r
, pa_expenditure_items_all ei
, pa_expenditure_items_all eia
WHERE
ei.expenditure_item_id = eia.adjusted_expenditure_item_id (+)
AND ei.adjusted_expenditure_item_id IS NULL
AND r.project_id = X_project_id
AND r.draft_invoice_num = X_inv_num
AND r.draft_invoice_item_line_num =
nvl( X_inv_line_num, r.draft_invoice_item_line_num )
AND r.expenditure_item_id = ei.expenditure_item_id
AND ( 1 = 2
OR ( X_adj_action = 'CHANGE WORK TYPE ATTRIBUTE' /** proj currency changes **/
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.work_type_id <> p_dest_work_type_id
)
OR
( X_adj_action = 'PROJECT OR TASK CHANGE'
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.task_id <> X_dest_task_id
AND ei.source_expenditure_item_id IS NULL
)
OR
( X_adj_action = 'BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'O' ) )
OR
( X_adj_action = 'BILLING HOLD RELEASE'
AND ei.bill_hold_flag IN ( 'Y', 'O' ) )
OR
( X_adj_action = 'NON-BILLABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'REVENUE RECALC'
AND ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'ONE-TIME BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'Y' ) )
OR
( X_adj_action = 'COST AND REV RECALC'
AND ( ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
OR ( ei.cost_distributed_flag||'' = 'Y'
OR ei.denom_raw_cost IS NOT NULL ) )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND ei.source_expenditure_item_id IS NULL
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
) )
FOR UPDATE OF ei.expenditure_item_id, eia.expenditure_item_id NOWAIT;
SELECT
ei.expenditure_item_id
, eia.expenditure_item_id adj_expenditure_item_id
FROM
pa_draft_invoice_items ii
, pa_expenditure_items_all ei
, pa_expenditure_items_all eia
WHERE
/* Bug # 3457873
ei.task_id IN (
SELECT task_id
FROM pa_tasks
WHERE project_id = X_project_id )
*/
ei.project_id = X_project_id /* Bug # 3457873 */
AND ei.expenditure_item_id = eia.adjusted_expenditure_item_id (+)
AND ei.adjusted_expenditure_item_id IS NULL
AND ii.project_id = X_project_id
AND ii.draft_invoice_num = X_inv_num
AND ii.line_num = nvl( X_inv_line_num, ii.line_num )
AND ii.project_id = ei.project_id
AND nvl( ii.event_task_id, -1 ) = nvl( ei.event_task_id, -1 )
AND ii.event_num = ei.event_num
AND ( 1 = 2
OR ( X_adj_action = 'CHANGE WORK TYPE ATTRIBUTE' /** proj currency changes **/
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.work_type_id <> p_dest_work_type_id
)
OR
( X_adj_action = 'BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'O' ) )
OR
( X_adj_action = 'BILLING HOLD RELEASE'
AND ei.bill_hold_flag IN ( 'Y', 'O' ) )
OR
( X_adj_action = 'NON-BILLABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'REVENUE RECALC'
AND ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'ONE-TIME BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'Y' ) )
OR
( X_adj_action = 'COST AND REV RECALC'
AND ( ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
OR ( ei.cost_distributed_flag||'' = 'Y'
OR ei.denom_raw_cost IS NOT NULL ) )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND ei.source_expenditure_item_id IS NULL
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'N'
)
OR
( X_adj_action = 'PROJECT OR TASK CHANGE'
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.task_id <> X_dest_task_id
AND ei.source_expenditure_item_id IS NULL
) )
FOR UPDATE OF ei.expenditure_item_id, eia.expenditure_item_id NOWAIT;
SELECT
ei.expenditure_item_id
, eia.expenditure_item_id adj_expenditure_item_id
FROM
pa_expenditure_items_all ei
, pa_expenditure_items_all eia
WHERE
ei.expenditure_item_id = eia.adjusted_expenditure_item_id (+)
AND ei.expenditure_item_id = X_expenditure_item_id
AND ( 1 = 2
OR X_adj_action = 'EXP COMMENT CHANGE'
OR ( X_adj_action = 'CHANGE WORK TYPE ATTRIBUTE' /** proj currency changes **/
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.work_type_id <> p_dest_work_type_id
)
OR
( X_adj_action = 'BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'O' ) )
OR
( X_adj_action = 'BILLING HOLD RELEASE'
AND ei.bill_hold_flag IN ( 'Y', 'O' ) )
OR
( X_adj_action = 'NON-BILLABLE RECLASS'
AND ei.billable_flag = 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'REVENUE RECALC'
AND ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
)
OR
( X_adj_action = 'ONE-TIME BILLING HOLD'
AND ei.bill_hold_flag IN ( 'N', 'Y' ) )
OR
( X_adj_action = 'COST AND REV RECALC'
AND ( ei.revenue_distributed_flag||'' IN ( 'Y', 'P' )
OR ( ei.cost_distributed_flag||'' = 'Y'
OR ei.denom_raw_cost IS NOT NULL ) )
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND nvl( eia.converted_flag, 'N' ) <> 'Y'
AND ei.source_expenditure_item_id IS NULL
AND nvl( ei.net_zero_adjustment_flag, 'N') <> 'Y'
)
OR
( X_adj_action = 'PROJECT OR TASK CHANGE'
AND nvl( ei.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND nvl( ei.converted_flag, 'N' ) <> 'Y'
AND ei.task_id <> X_dest_task_id
AND ei.source_expenditure_item_id IS NULL
) )
FOR UPDATE OF ei.expenditure_item_id, eia.expenditure_item_id NOWAIT;
SELECT expenditure_item_id adj
INTO adj_ei
FROM pa_expenditure_items_all
WHERE adjusted_expenditure_item_id = X_expenditure_item_id;
SELECT expenditure_item_id adj
INTO adj_ei
FROM pa_expenditure_items_all
WHERE adjusted_expenditure_item_id = X_expenditure_item_id;
select_clause VARCHAR2(2000);
reprocess_cc_select VARCHAR2(2000);
select_clause := 'SELECT'
|| ' ei.expenditure_item_id'
|| ',ei.org_id'
|| ',ei.project_id'
|| ',ei.net_zero_adjustment_flag'
|| ',ei.converted_flag'
|| ',e.expenditure_status_code'
|| ',ei.burden_sum_dest_run_id'
|| ',ei.document_header_id'
|| ',ei.transaction_source'
|| ',ei.document_type'
|| ',ei.document_distribution_type'
|| ',ei.document_payment_id'
|| ',ei.source_expenditure_item_id'
|| ',ei.system_linkage_function'
|| ',ei.acct_currency_code'
|| ',ei.denom_currency_code'
|| ',ei.denom_tp_currency_code'
|| ',ei.projfunc_currency_code'
|| ',ei.cc_cross_charge_code'
|| ',ei.adjusted_expenditure_item_id'
|| ',ei.orig_transaction_reference'
|| ',ei.expenditure_item_date'
|| ',ei.billable_flag'
|| ',ei.task_id'
|| ',ei.expenditure_type'
|| ',ei.vendor_id'
|| ',nvl(ei.override_to_organization_id, e.incurred_by_organization_id)'
|| ',e.incurred_by_person_id'
|| ',ei.document_line_number'
|| ',ei.document_distribution_id'
|| ',ei.project_currency_code'
|| ',ei.tp_amt_type_code'
|| ',ei.organization_id';
|| ' SELECT adl.expenditure_item_id'
|| ' FROM gms_award_distributions adl'
|| ' WHERE adl.adl_line_num = 1'
|| ' AND adl.document_type = ''EXP'''
|| ' AND adl.adl_status = ''A'''
|| ' AND adl.project_id = :project_id'
|| ' AND adl.award_id = :award_id )';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_expenditure_types et'
|| ' WHERE et.expenditure_type = ei.expenditure_type'
|| ' AND et.expenditure_category = :expenditure_catg)';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code <> ''CAPITAL'')'
|| ' AND ei.billable_flag = :billable_flag';
|| ' OR EXISTS (SELECT 1'
|| ' FROM pa_cust_rev_dist_lines_all r'
|| ' WHERE r.expenditure_item_id = ei.expenditure_item_id'
|| ' AND r.draft_invoice_num is not null'
|| ' HAVING sum(nvl(r.bill_trans_bill_amount, 0)) <> 0'
|| ' GROUP BY r.expenditure_item_id))';
|| ' AND NOT EXISTS (SELECT 1'
|| ' FROM pa_cust_rev_dist_lines_all r'
|| ' WHERE r.expenditure_item_id = ei.expenditure_item_id'
|| ' AND r.draft_invoice_num is not null'
|| ' HAVING sum(nvl(r.bill_trans_bill_amount, 0)) <> 0'
|| ' GROUP BY r.expenditure_item_id))';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code <> ''CAPITAL'')'
|| ' AND ei.revenue_distributed_flag = :revenue_distributed_flag';
' ( select apdist.invoice_distribution_id' ||
' from ap_invoice_distributions_all apdist' ||
' where apdist.invoice_id = :invoice_id' ||
' and apdist.invoice_line_number = :invoice_line_number' ||
' union all' ||
' select apdist1.invoice_distribution_id' ||
' from ap_invoice_distributions_all apdist1' ||
' where apdist1.charge_Applicable_to_dist_id in' ||
' ( select apdist2.invoice_distribution_id' ||
' from ap_invoice_distributions_all apdist2' ||
' where apdist2.invoice_id = :invoice_id' ||
' and apdist2.invoice_line_number = :invoice_line_number)' ||
' and apdist1.line_type_lookup_code in (''NONREC_TAX'', ''TIPV'', ''TERV'', ''TRV''))'; /* Bug 5403294 */
' (select rcvtxn.transaction_id' ||
' from rcv_shipment_headers rcvhead' ||
' , rcv_transactions rcvtxn' ||
' where rcvhead.shipment_header_id =' ||
' rcvtxn.shipment_header_id' ||
' and rcvhead.receipt_num = :receipt_number)';
' (select invoice_payment_id' ||
' from ap_invoice_payments_all' ||
' where check_id = :check_id) ';
|| ' AND EXISTS (select NULL'
|| ' from pa_cost_distribution_lines cdl1'
|| ' where cdl1.expenditure_item_id = ei.expenditure_item_id'
|| ' and cdl1.line_num = 1'
|| ' and NVL(cdl1.reversed_flag,''N'') <> ''Y'')'
|| ' AND ei.net_zero_adjustment_flag = ''N'''
|| ' AND ei.transferred_from_exp_item_id IS NULL'
|| ' AND pa_adjustments.is_orphaned_src_sys_reversal(ei.document_distribution_id,ei.transaction_source) = ''Y'''; /* 4901129 */
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code = ''CAPITAL'')'
|| ' AND ei.billable_flag = :capitalizable_flag';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code = ''CAPITAL'')'
|| ' AND 1 = (SELECT 1'
|| ' FROM pa_tasks t'
|| ' WHERE t.task_id = ei.task_id'
|| ' AND t.retirement_cost_flag = ''N'')'
|| ' AND ei.revenue_distributed_flag = :grouped_cip_flag';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code = ''CAPITAL'')'
|| ' AND 1 = (SELECT 1'
|| ' FROM pa_tasks t'
|| ' WHERE t.task_id = ei.task_id'
|| ' AND t.retirement_cost_flag = ''Y'')'
|| ' AND ei.revenue_distributed_flag = :grouped_rwip_flag';
where_clause := where_clause || ' AND 1 = (SELECT 1'
|| ' FROM pa_project_types_all pt'
|| ' , pa_projects_all p'
|| ' WHERE p.project_type = pt.project_type'
|| ' AND p.org_id = pt.org_id'
|| ' AND p.project_id = ei.project_id'
|| ' AND pt.project_type_class_code = ''CAPITAL'')'
|| ' AND 1 = (SELECT 1'
|| ' FROM pa_tasks t'
|| ' WHERE t.task_id = ei.task_id'
|| ' AND t.billable_flag = ''N'''
|| ' AND t.retirement_cost_flag = ''N'')';
|| ' SELECT null'
|| ' FROM pa_capital_events'
|| ' WHERE capital_event_number = :capital_event_number'
|| ' AND project_id = :project_id';
v_reserve_rec := ' FOR UPDATE OF ei.expenditure_item_id NOWAIT';
, select_clause || from_clause || where_clause -- || v_reserve_rec - Commenting for bug 5501593
, dbms_sql.v7);
SELECT pt.project_type_class_code
, p.project_status_code
INTO v_project_type_class_code
, l_project_status_code
FROM pa_project_types_all pt
, pa_projects_all p
WHERE p.project_type = pt.project_type
AND p.org_id = pt.org_id
AND p.project_id = l_project_id;
SELECT tr.allow_adjustments_flag
, tr.gl_accounted_flag
INTO l_allow_adjustments_flag
, l_gl_accounted
FROM pa_transaction_sources tr
WHERE tr.transaction_source = l_transaction_source;
/* Bug 5501593 - Checking if the expenditure can be locked for update or not */
DECLARE
l_dummy VARCHAR2(1);
SELECT NULL
INTO l_dummy
FROM pa_expenditure_items_all
WHERE expenditure_item_id = v_expenditure_item_id
FOR UPDATE OF expenditure_item_id NOWAIT;
select billable_capitalizable_flag
into l_billable_capitalizable_flag
from pa_work_types_b
where work_type_id = p_dest_work_type_id;
select start_date, completion_date
from pa_projects_all
where project_id = X_dest_prj_id;
select start_date, completion_date
from pa_tasks
where task_id = X_dest_task_id;
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cost_distributed_flag = 'N'
, revenue_distributed_flag = 'N'
, acct_rate_type = X_acct_rate_type
, acct_rate_date = X_acct_rate_date
, acct_exchange_rate = X_acct_exchange_rate
, project_rate_type = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_type,project_rate_type )
, project_rate_date = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_date, project_rate_date )
, project_exchange_rate = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_exchange_rate,project_exchange_rate )
, acct_raw_cost = NULL
, acct_burdened_cost = NULL
, project_raw_cost = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code, NULL, project_raw_cost )
, project_burdened_cost = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code, NULL,project_burdened_cost )
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
, projfunc_cost_rate_type = DECODE(p_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_type,projfunc_cost_rate_type )
, projfunc_cost_rate_date = DECODE(p_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_date, projfunc_cost_rate_date )
, projfunc_cost_exchange_rate = DECODE(p_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_acct_exchange_rate,projfunc_cost_exchange_rate )
, raw_cost = DECODE(p_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code, NULL, raw_cost )
, burden_cost = DECODE(p_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code, NULL,burden_cost )
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
, ei.revenue_distributed_flag = 'N'
, acct_rate_type = X_acct_rate_type
, acct_rate_date = X_acct_rate_date
, acct_exchange_rate = l_acct_exchange_rate
, project_rate_type = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_type, project_rate_type )
, project_rate_date = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_date, project_rate_date )
, project_exchange_rate = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_exchange_rate , project_exchange_rate )
, acct_raw_cost = NULL
, acct_burdened_cost = NULL
, project_raw_cost = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code, NULL, project_raw_cost )
, project_burdened_cost = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code, NULL, project_burdened_cost )
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
, projfunc_cost_rate_type = DECODE(ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_type,projfunc_cost_rate_type )
, projfunc_cost_rate_date = DECODE(ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_rate_date, projfunc_cost_rate_date )
, projfunc_cost_exchange_rate = DECODE(ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_acct_exchange_rate,projfunc_cost_exchange_rate )
, raw_cost = DECODE(ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code, NULL, raw_cost )
, burden_cost = DECODE(ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code, NULL,burden_cost )
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cost_distributed_flag = 'N'
, revenue_distributed_flag = 'N'
, projfunc_cost_rate_type = X_projfunc_cost_rate_type
, projfunc_cost_rate_date = X_projfunc_cost_rate_date
, projfunc_cost_exchange_rate =X_projfunc_cost_exchg_rate
, raw_cost = NULL
, burden_cost = NULL
, acct_rate_type = DECODE(X_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_projfunc_cost_rate_type, acct_rate_type )
, acct_rate_date = DECODE(X_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_projfunc_cost_rate_date , acct_rate_date )
, acct_exchange_rate = DECODE(X_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_projfunc_cost_exchg_rate , acct_exchange_rate )
, acct_raw_cost = DECODE(X_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
NULL, acct_raw_cost )
, acct_burdened_cost = DECODE(X_projfunc_currency_code,
pa_multi_currency.G_accounting_currency_code,
NULL,acct_burdened_cost )
/** added for project currency changes **/
, project_rate_type = DECODE(x_projfunc_currency_code,project_currency_code,
x_projfunc_cost_rate_type, project_rate_type )
, project_rate_date = DECODE(x_projfunc_currency_code,project_currency_code,
X_projfunc_cost_rate_date , project_rate_date )
, project_exchange_rate = DECODE(x_projfunc_currency_code,project_currency_code,
p_projfunc_cost_exchg_rate , project_exchange_rate )
, project_raw_cost = DECODE(x_projfunc_currency_code,project_currency_code,
NULL ,project_raw_cost)
, project_burdened_cost = DECODE(x_projfunc_currency_code,project_currency_code,
NULL,project_burdened_cost)
/** end of changes **/
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
, ei.revenue_distributed_flag = 'N'
, projfunc_cost_rate_type = p_projfunc_cost_rate_type
, projfunc_cost_rate_date = p_projfunc_cost_rate_date
, projfunc_cost_exchange_rate = l_projfunc_cost_exchg_rate
, raw_cost = NULL
, burden_cost = NULL
, acct_rate_type = DECODE(p_ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
p_projfunc_cost_rate_type, acct_rate_type )
, acct_rate_date = DECODE(p_ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
p_projfunc_cost_rate_date , acct_rate_date )
, acct_exchange_rate = DECODE(p_ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
l_projfunc_cost_exchg_rate , acct_exchange_rate )
, acct_raw_cost = DECODE(p_ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
NULL, acct_raw_cost )
, acct_burdened_cost = DECODE(p_ProjFuncCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
NULL,acct_burdened_cost )
/** added for project currency changes **/
, project_rate_type = DECODE(p_ProjFuncCurrCodeTab(i), project_currency_code,
p_projfunc_cost_rate_type, project_rate_type )
, project_rate_date = DECODE(p_ProjFuncCurrCodeTab(i), project_currency_code,
p_projfunc_cost_rate_date , project_rate_date )
, project_exchange_rate = DECODE(p_ProjFuncCurrCodeTab(i), project_currency_code,
l_projfunc_cost_exchg_rate , project_exchange_rate )
, project_raw_cost = DECODE(p_ProjFuncCurrCodeTab(i), project_currency_code,
NULL ,project_raw_cost)
, project_burdened_cost = DECODE(p_ProjFuncCurrCodeTab(i), project_currency_code,
NULL,project_burdened_cost)
/** end of changes **/
, ei.last_update_date = sysdate
, ei.last_updated_by = p_user
, ei.last_update_login = p_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
print_message('get_denom_curr_code: ' || 'Num of rows update for eis ='||sql%rowcount);
/** This api update the EI table when project curenncy attributes are changed **/
PROCEDURE ChangeProjAttributes(ItemsIdTab IN pa_utils.IdTabTyp
, X_adjust_level IN VARCHAR2
, X_user IN NUMBER
, X_login IN NUMBER
, X_module IN VARCHAR2
, X_project_rate_type IN VARCHAR2
, X_project_rate_date IN DATE
, X_project_exchange_rate IN NUMBER
, DenomCurrCodeTab IN pa_utils.Char15TabTyp
, ProjCurrCodeTab IN pa_utils.Char15TabTyp
, rows IN NUMBER
, X_num_processed OUT NOCOPY NUMBER
, X_num_rejected OUT NOCOPY NUMBER
, X_status OUT NOCOPY NUMBER ) IS
temp_status NUMBER DEFAULT NULL;
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cost_distributed_flag = 'N'
, revenue_distributed_flag = 'N'
, project_rate_type = X_project_rate_type
, project_rate_date = X_project_rate_date
, project_exchange_rate = X_project_exchange_rate
, acct_rate_type = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_project_rate_type, acct_rate_type )
, acct_rate_date = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_project_rate_date, acct_rate_date )
, acct_exchange_rate = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
X_project_exchange_rate , acct_exchange_rate )
, project_raw_cost = NULL
, project_burdened_cost = NULL
, acct_raw_cost = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
NULL, acct_raw_cost )
, acct_burdened_cost = DECODE(X_project_currency_code,
pa_multi_currency.G_accounting_currency_code,
NULL,acct_burdened_cost )
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all ei
SET
ei.cost_distributed_flag = 'N'
, ei.revenue_distributed_flag = 'N'
, project_rate_type = X_project_rate_type
, project_rate_date = X_project_rate_date
, project_exchange_rate = l_project_exchange_rate
, acct_rate_type = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_project_rate_type, acct_rate_type )
, acct_rate_date = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_project_rate_date , acct_rate_date )
, acct_exchange_rate = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
X_project_exchange_rate , acct_exchange_rate )
, project_raw_cost = NULL
, project_burdened_cost = NULL
, acct_raw_cost = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
NULL, acct_raw_cost )
, acct_burdened_cost = DECODE(ProjCurrCodeTab(i),
pa_multi_currency.G_accounting_currency_code,
NULL,acct_burdened_cost )
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cc_cross_charge_code =DECODE(X_adjust_level,'I', X_cc_code, l_cc_code)
, cc_cross_charge_type = DECODE(X_adjust_level,'I',X_cc_type, l_cc_type)
, cc_bl_distributed_code = DECODE(X_adjust_level,'I',X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level,'I',X_ic_proc_code, l_ic_proc_code)
, cc_prvdr_organization_id = DECODE(X_adjust_level,'I',X_prvdr_orgnzn_id, l_PrvdrOrganizationId)
, cc_recvr_organization_id = DECODE(X_adjust_level,'I',X_recvr_orgnzn_id, l_RecvrOrganizationId)
, denom_tp_currency_code = NULL
, acct_tp_rate_type = NULL
, acct_tp_rate_date = NULL
, acct_tp_exchange_rate = NULL
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, projacct_transfer_price = NULL
, cc_markup_base_code= NULL
, tp_base_amount = NULL
, tp_ind_compiled_set_id = NULL
, tp_bill_rate = NULL
, tp_bill_markup_percentage = NULL
, tp_schedule_line_percentage = NULL
, tp_rule_percentage = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
, PROJECT_TP_RATE_DATE = null
, PROJECT_TP_RATE_TYPE = null
, PROJECT_TP_EXCHANGE_RATE = null
, PROJECT_TRANSFER_PRICE = null
, PROJFUNC_TP_RATE_DATE = null
, PROJFUNC_TP_RATE_TYPE = null
, PROJFUNC_TP_EXCHANGE_RATE = null
, PROJFUNC_TRANSFER_PRICE = null
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cc_cross_charge_code =DECODE(X_adjust_level,'I', X_cc_code, l_cc_code)
, cc_cross_charge_type = DECODE(X_adjust_level,'I',X_cc_type, l_cc_type)
, cc_bl_distributed_code = DECODE(X_adjust_level,'I',X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level,'I',X_ic_proc_code, l_ic_proc_code)
, cc_prvdr_organization_id = DECODE(X_adjust_level,'I',X_prvdr_orgnzn_id, l_PrvdrOrganizationId)
, cc_recvr_organization_id = DECODE(X_adjust_level,'I',X_recvr_orgnzn_id, l_RecvrOrganizationId)
, denom_tp_currency_code = NULL
, acct_tp_rate_type = NULL
, acct_tp_rate_date = NULL
, acct_tp_exchange_rate = NULL
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, projacct_transfer_price = NULL
, cc_markup_base_code= NULL
, tp_base_amount = NULL
, tp_ind_compiled_set_id = NULL
, tp_bill_rate = NULL
, tp_bill_markup_percentage = NULL
, tp_schedule_line_percentage = NULL
, tp_rule_percentage = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
, PROJECT_TP_RATE_DATE = null
, PROJECT_TP_RATE_TYPE = null
, PROJECT_TP_EXCHANGE_RATE = null
, PROJECT_TRANSFER_PRICE = null
, PROJFUNC_TP_RATE_DATE = null
, PROJFUNC_TP_RATE_TYPE = null
, PROJFUNC_TP_EXCHANGE_RATE = null
, PROJFUNC_TRANSFER_PRICE = null
WHERE
expenditure_item_id = ItemsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cc_cross_charge_code = 'N'
, cc_bl_distributed_code = DECODE(X_adjust_level,'I',X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level,'I',X_ic_proc_code, l_ic_proc_code)
, denom_tp_currency_code = NULL
, acct_tp_rate_type = NULL
, acct_tp_rate_date = NULL
, acct_tp_exchange_rate = NULL
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, projacct_transfer_price = NULL
, cc_markup_base_code= NULL
, tp_base_amount = NULL
, tp_ind_compiled_set_id = NULL
, tp_bill_rate = NULL
, tp_bill_markup_percentage = NULL
, tp_schedule_line_percentage = NULL
, tp_rule_percentage = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
, PROJECT_TP_RATE_DATE = null
, PROJECT_TP_RATE_TYPE = null
, PROJECT_TP_EXCHANGE_RATE = null
, PROJECT_TRANSFER_PRICE = null
, PROJFUNC_TP_RATE_DATE = null
, PROJFUNC_TP_RATE_TYPE = null
, PROJFUNC_TP_EXCHANGE_RATE = null
, PROJFUNC_TRANSFER_PRICE = null
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cc_cross_charge_code = 'N'
, cc_bl_distributed_code = DECODE(X_adjust_level,'I',X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level,'I',X_ic_proc_code, l_ic_proc_code)
, denom_tp_currency_code = NULL
, acct_tp_rate_type = NULL
, acct_tp_rate_date = NULL
, acct_tp_exchange_rate = NULL
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, projacct_transfer_price = NULL
, cc_markup_base_code= NULL
, tp_base_amount = NULL
, tp_ind_compiled_set_id = NULL
, tp_bill_rate = NULL
, tp_bill_markup_percentage = NULL
, tp_schedule_line_percentage = NULL
, tp_rule_percentage = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
, PROJECT_TP_RATE_DATE = null
, PROJECT_TP_RATE_TYPE = null
, PROJECT_TP_EXCHANGE_RATE = null
, PROJECT_TRANSFER_PRICE = null
, PROJFUNC_TP_RATE_DATE = null
, PROJFUNC_TP_RATE_TYPE = null
, PROJFUNC_TP_EXCHANGE_RATE = null
, PROJFUNC_TRANSFER_PRICE = null
WHERE
expenditure_item_id = ItemsIdTab(i);
SELECT
expenditure_item_id
FROM
pa_expenditure_items_all
WHERE
source_expenditure_item_id = X_expenditure_item_id;
UPDATE pa_expenditure_items_all
SET
cc_bl_distributed_code = DECODE(X_adjust_level,'I',X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level,'I',X_ic_proc_code, l_ic_proc_code)
, acct_tp_rate_type = X_acct_tp_rate_type
, acct_tp_rate_date = X_acct_tp_rate_date
, acct_tp_exchange_rate = l_acct_exchange_rate
--- , PROJECT_TP_RATE_DATE = p_PROJECT_TP_COST_RATE_DATE
--- , PROJECT_TP_RATE_TYPE = p_PROJECT_TP_COST_RATE_TYPE
--- , PROJECT_TP_EXCHANGE_RATE = p_PROJECT_TP_COST_EXCHG_RATE
, PROJECT_TRANSFER_PRICE = NULL
, PROJFUNC_TRANSFER_PRICE = NULL
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
WHERE
expenditure_item_id = eachRec.expenditure_item_id;
UPDATE pa_expenditure_items_all ei
SET
cc_bl_distributed_code = DECODE(X_adjust_level, 'I', X_bl_dist_code, l_bl_dist_code)
, cc_ic_processed_code = DECODE(X_adjust_level, 'I',X_ic_proc_code, l_ic_proc_code)
, acct_tp_rate_type = X_acct_tp_rate_type
, acct_tp_rate_date = X_acct_tp_rate_date
, acct_tp_exchange_rate = l_acct_exchange_rate
, denom_transfer_price = NULL
, acct_transfer_price = NULL
, last_updated_by = X_user
, last_update_date = sysdate
, last_update_login = X_login
---, PROJECT_TP_RATE_DATE = p_PROJECT_TP_COST_RATE_DATE
---, PROJECT_TP_RATE_TYPE = p_PROJECT_TP_COST_RATE_TYPE
---, PROJECT_TP_EXCHANGE_RATE = p_PROJECT_TP_COST_EXCHG_RATE
, PROJECT_TRANSFER_PRICE = NULL
, PROJFUNC_TRANSFER_PRICE = NULL
WHERE
ei.expenditure_item_id = ItemsIdTab(i);
SELECT allow_adjustments_flag, predefined_flag
FROM pa_transaction_sources
WHERE transaction_source = p_transaction_source;
SELECT net_zero_adjustment_flag_1,
expenditure_item_date_1,
net_zero_adjustment_flag_2,
expenditure_item_date_2,
net_zero_adjustment_flag_3,
expenditure_item_date_3,
net_zero_adjustment_flag_4,
expenditure_item_date_4,
net_zero_adjustment_flag_5,
expenditure_item_date_5,
net_zero_adjustment_flag_6,
expenditure_item_date_6,
net_zero_adjustment_flag_7,
expenditure_item_date_7
FROM PA_EI_DENORM
WHERE denorm_id = p_orig_transaction_reference;
SELECT gl_accounted_flag, costed_flag
INTO l_gl_accounted_flag, l_costed_flag /* added costed flag bug 3142879 */
FROM pa_transaction_sources
WHERE transaction_source = p_transaction_source;
SELECT cost_rate_flag
INTO l_cost_rate_flag
FROM pa_expenditure_types
WHERE expenditure_type = p_exp_type;
SELECT IMP.SET_OF_BOOKS_ID
, GL.CHART_OF_ACCOUNTS_ID
FROM GL_LEDGERS_PUBLIC_V GL
, PA_IMPLEMENTATIONS IMP
WHERE GL.LEDGER_ID = IMP.SET_OF_BOOKS_ID;
SELECT VENDOR_ID
FROM PO_VENDORS_AP_V
WHERE ACTIVE_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
AND EMPLOYEE_ID = p_emp_id;
SELECT DEFAULT_CODE_COMB_ID
FROM PER_ASSIGNMENTS_F
WHERE PERSON_ID = p_emp_id
AND SET_OF_BOOKS_ID = p_sob_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(EFFECTIVE_START_DATE)
AND NVL(TRUNC(EFFECTIVE_END_DATE), TRUNC(SYSDATE));
SELECT WEB_PARAMETER_ID
FROM AP_INVOICE_DISTRIBUTIONS
WHERE INVOICE_DISTRIBUTION_ID = p_invoice_distribution_id;
SELECT DR_CODE_COMBINATION_ID, GL_DATE
FROM PA_COST_DISTRIBUTION_LINES
WHERE EXPENDITURE_ITEM_ID = ( SELECT EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS
WHERE TRANSFERRED_FROM_EXP_ITEM_ID IS NULL
START WITH EXPENDITURE_ITEM_ID = p_expenditure_item_id
CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID = EXPENDITURE_ITEM_ID)
AND TRANSFER_STATUS_CODE = 'V';
/* Update cache */
G_ORG_ID := NVL(p_org_id, G_ORG_ID);
SELECT LIABILITY_POST_LOOKUP_CODE
FROM AP_SYSTEM_PARAMETERS;
SELECT B.POOLED_FLAG
FROM CE_BANK_ACCOUNTS B
, AP_CHECKS_ALL C
, AP_INVOICE_PAYMENTS_ALL A
WHERE A.INVOICE_PAYMENT_ID = p_invoice_payment_id
AND A.CHECK_ID = C.CHECK_ID
AND C.CE_BANK_ACCT_USE_ID = B.BANK_ACCOUNT_ID;
SELECT nvl(historical_flag, 'N')
INTO l_historical_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_invoice_distribution_id;
So it has to be derived using the cursor and the cache has to be updated */
OPEN c_get_ap_offset_method;
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT displayed_field
FROM po_lookup_codes
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT po.po_header_id
, po.segment1
, po.creation_date
, po_line.line_num
, po_dist.distribution_num
, po_dist.distribution_type
FROM po_headers po
, po_lines po_line
, po_distributions po_dist
WHERE po.po_header_id = po_line.po_header_id
AND po_line.po_line_id = po_dist.po_line_id
AND po_dist.po_distribution_id = p_po_distribution_id;
SELECT rcv.receipt_num
, rcvtxn.transaction_date
, rcvtxn.transaction_type
FROM rcv_shipment_headers rcv
, rcv_transactions rcvtxn
WHERE rcv.shipment_header_id = rcvtxn.shipment_header_id
AND rcvtxn.transaction_id = p_rcv_transaction_id;
SELECT ap.invoice_type_lookup_code
, ap.invoice_num
, ap.invoice_date
FROM ap_invoices ap
WHERE ap.invoice_id = p_invoice_id;
SELECT LINE_LOCATION_ID
FROM PO_DISTRIBUTIONS
WHERE PO_DISTRIBUTION_ID = p_po_distribution_id;
SELECT NVL(charge_applicable_to_dist_id,NVL(related_id, invoice_distribution_id))
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_invoice_distribution_id;
SELECT invoice_line_number
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_parent_distribution_id;
SELECT DECODE(count(*),0,'N','Y')
FROM pa_expenditure_items_all ei /* Bug 5561597 */
WHERE ( ei.document_header_id, ei.document_distribution_id) IN
( SELECT apdist2.invoice_id, apdist2.invoice_distribution_id /* Bug 5561597 */
FROM ap_invoice_distributions_all apdist1, /* Bug 5561597 */
ap_invoice_distributions_all apdist2 /* Bug 5561597 */
WHERE p_document_distribution_id = apdist1.invoice_distribution_id
AND apdist1.reversal_flag = 'Y'
AND apdist1.parent_reversal_id = apdist2.invoice_distribution_id
AND apdist2.old_distribution_id IS NOT NULL
AND p_transaction_source IN ('AP VARIANCE','AP INVOICE'
,'AP DISCOUNTS','INTERCOMPANY_AP_INVOICES','INTERPROJECT_AP_INVOICES'
,'AP NRTAX','AP EXPENSE','AP ERV') /* Bug 5235354 */
UNION ALL
SELECT rcv2.po_header_id, rcv2.transaction_id
FROM rcv_transactions rcv1
, rcv_transactions rcv2
WHERE rcv1.transaction_id = p_document_distribution_id
AND rcv1.transaction_type in ('RETURN TO RECEIVING','RETURN TO VENDOR','CORRECT')
AND rcv1.parent_transaction_id = rcv2.transaction_id
AND p_transaction_source IN ('PO RECEIPT','PO RECEIPT NRTAX',
'PO RECEIPT NRTAX PRICE ADJ','PO RECEIPT PRICE ADJ'))
AND ( ei.net_zero_adjustment_flag = 'Y'
OR EXISTS ( SELECT NULL
FROM pa_cost_distribution_lines_all cdl2
WHERE cdl2.expenditure_item_id = ei.expenditure_item_id
AND cdl2.line_num = 1
AND cdl2.reversed_flag = 'Y'));
SELECT COUNT(*)
FROM gl_ledgers_v gl,
pa_implementations imp
WHERE imp.set_of_books_id = gl.ledger_id
AND (EXISTS
(SELECT NULL
FROM gl_secondary_ledger_rships_v sl,
xla_subledger_options_v xso
WHERE sl.primary_ledger_id = gl.ledger_id
AND sl.currency_code <> gl.currency_code
AND sl.relationship_enabled_flag = 'Y'
AND xso.application_id = 275
AND xso.enabled_flag = 'Y'
AND xso.ledger_id = sl.ledger_id)
OR EXISTS
(SELECT NULL
FROM gl_alc_ledger_rships_v alc
WHERE alc.primary_ledger_id = gl.ledger_id
AND alc.currency_code <> gl.currency_code
AND alc.application_id = 275
AND relationship_enabled_flag = 'Y'));
So it has to be derived using the cursor and the cache has to be updated */
OPEN c_rep_curr_or_sec_ledger;
SELECT poll.accrue_on_receipt_flag
FROM po_line_locations_all poll
, po_distributions_all pod
, ap_invoice_distributions_all aid
WHERE poll.line_location_id = pod.line_location_id
AND pod.po_distribution_id = aid.po_distribution_id
AND aid.invoice_distribution_id = p_invoice_distribution_id;
SELECT NULL
INTO l_dummy
FROM ap_invoice_distributions_all dist
WHERE dist.invoice_distribution_id = p_invoice_distribution_id
AND EXISTS (
SELECT NULL
FROM ap_invoice_distributions_all ppdist
WHERE ppdist.invoice_distribution_id = dist.charge_applicable_to_dist_id
AND ppdist.line_type_lookup_code = 'PREPAY'
);