[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
INTO G_ORG_ID
FROM pa_implementations;
SELECT * FROM (
SELECT item.expenditure_item_id expenditure_item_id
,item.cost_distributed_flag cost_distributed_flag
,item.project_id project_id
,item.system_linkage_function system_linkage_function
,exp.incurred_by_person_id incurred_by_person_id
,item.interface_run_id interface_run_id
,'Y' reverse_eligibile_flag
,NULL reverse_error_code
FROM pa_expenditure_items item,
pa_expenditures exp
WHERE exp.expenditure_id = item.expenditure_id
AND exp.expenditure_status_code = 'APPROVED'
AND (item.cost_distributed_flag = 'Y' OR (item.cost_distributed_flag = 'N'
AND item.costing_method IS NOT NULL
AND item.adjustment_type IN('RECALC_RAW', 'COST AND REV RECALC','RECALC_BURDEN'))
)
AND item.system_linkage_function IN ('OT','ST')
AND NVL(item.net_zero_adjustment_flag,'N') <> 'Y'
AND item.costing_method=P_costing_method
AND NVL(item.payroll_accrual_flag,'N')=NVL(P_accrual_only_flag,'N')
AND (P_project_id IS NULL OR item.project_id =P_project_id )
AND NVL(item.override_to_organization_id,exp.incurred_by_organization_id ) = NVL(P_inc_by_org_id,exp.incurred_by_organization_id)
AND exp.incurred_by_person_id = NVL(P_inc_by_person_id,exp.incurred_by_person_id )
AND item.expenditure_item_date BETWEEN P_from_date AND P_to_date
UNION
SELECT
item.expenditure_item_id expenditure_item_id
,item.cost_distributed_flag cost_distributed_flag
,item.project_id project_id
,item.system_linkage_function system_linkage_function
,exp.incurred_by_person_id incurred_by_person_id
,item.interface_run_id interface_run_id
,uncosted_eis_exist(NVL(P_cost_set,item.interface_run_id), NVL(P_inc_by_person_id,exp.incurred_by_person_id)) reverse_eligibile_flag -- returns X if exists
,NULL reverse_error_code
FROM pa_expenditure_items item,
pa_expenditures exp
WHERE exp.expenditure_id = item.expenditure_id
AND exp.expenditure_status_code = 'APPROVED'
AND item.system_linkage_function IN ('ST','OT','PJ','BTC')
AND NVL(item.net_zero_adjustment_flag,'N') <> 'Y'
AND NVL(item.payroll_accrual_flag,'N')='N'
AND item.interface_run_id is not null
AND item.interface_run_id=P_cost_set
AND NVL(item.override_to_organization_id,exp.incurred_by_organization_id )=NVL(P_inc_by_org_id,exp.incurred_by_organization_id)
AND exp.incurred_by_person_id =NVL(P_inc_by_person_id,exp.incurred_by_person_id )
)
ORDER BY incurred_by_person_id asc;
SELECT ei1.expenditure_item_id ,
ei1.adjusted_expenditure_item_id
FROM pa_expenditure_items ei1
WHERE ei1.adjusted_expenditure_item_id= X_expid
AND NVL(ei1.net_zero_adjustment_flag,'N')='Y'
AND ei1.cost_distributed_flag='N';
SELECT ei.expenditure_item_id
FROM pa_expenditure_items ei,
pa_expenditures exp
WHERE ei.expenditure_id = exp.expenditure_id
AND ei.interface_run_id = x_costset_id
AND exp.incurred_by_person_id = x_empid
AND ei.cost_distributed_flag='N';
SELECT EI1.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI1,
PA_EXPENDITURES EXP1
WHERE EXP1.INCURRED_BY_PERSON_ID = X_EMPID
AND EI1.EXPENDITURE_ID = EXP1.EXPENDITURE_ID
AND EI1.INTERFACE_RUN_ID = x_costset_id
AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EXISTS
(
SELECT EI2.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI3,
PA_EXPENDITURE_ITEMS EI2,
PA_EXPENDITURES EXP2
WHERE EI3.interface_run_id = EI1.INTERFACE_RUN_ID
AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) =
SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND EXP2.INCURRED_BY_PERSON_ID = X_EMPID
AND EXP2.EXPENDITURE_ID = EI2.EXPENDITURE_ID
AND EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI2.COSTING_METHOD = 'ACTUAL'
AND EI2.PAYROLL_ACCRUAL_FLAG = 'N'
AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
AND EI2.INTERFACE_RUN_ID > EI1.INTERFACE_RUN_ID
UNION
/* adjustment is not by PAXREVTXN reversal process but from OTL */
SELECT EI4.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI5 ,
PA_EXPENDITURE_ITEMS EI4 ,
PA_EXPENDITURES EXP4
WHERE EI5.interface_run_id = EI1.INTERFACE_RUN_ID
AND EXP4.INCURRED_BY_PERSON_ID = X_EMPID
AND EXP4.EXPENDITURE_ID = EI4.EXPENDITURE_ID
AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1) =
SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND NVL(EI4.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
AND EI4.INTERFACE_RUN_ID IS NULL
AND NOT EXISTS
(SELECT EXPENDITURE_ITEM_ID FROM PA_EXPEND_ITEM_ADJ_ACTIVITIES ADJ
WHERE ADJ.EXPENDITURE_ITEM_ID = EI4.EXPENDITURE_ITEM_ID
AND ADJ.MODULE_CODE = 'PAXREVTXN'
AND EXCEPTION_ACTIVITY_CODE = 'REINITIATE EI TO DIST' )
) ; --Added for Bug 12779047
l_Project_id_tbl.DELETE;
l_Cost_Dist_Flag_tbl.DELETE;
l_Exp_item_id_tbl.DELETE;
l_adj_exp_item_id_tbl.DELETE;
l_NewExp_item_id_tbl.DELETE;
l_SysLinkTab.DELETE;
l_PersonIdTab.DELETE;
l_Interface_run_idTab.DELETE;
l_Reverse_eligibile_flag_Tab.DELETE;
l_Reverse_error_code_Tab.DELETE;
/*Start BUG:12611695 Added logic to insert the record into audit table in case of uncosted transactions exists */
FOR j IN 1 .. l_Exp_item_id_tbl.COUNT
LOOP
IF ((l_PersonIdTab(i) = l_PersonIdTab(j)) AND (l_Interface_run_idTab(i) = l_Interface_run_idTab(j)) AND l_Cost_Dist_Flag_tbl(j) = 'Y' )
THEN
l_Reverse_eligibile_flag_Tab(j) := 'N';
G_err_stage := ' Update expenditure cost columns with cdl amount if CDF =N and marked for recalculation ,etc';
UPDATE pa_expenditure_items ei
SET ei.cost_distributed_flag='Y',
(ei.raw_cost ,
ei.Raw_Cost_Rate,
ei.burden_cost,
ei.burden_cost_rate,
ei.ACCT_raw_cost,
ei.acct_burdened_cost,
ei.denom_raw_cost,
ei.denom_burdened_cost,
ei.project_raw_cost,
ei.project_burdened_cost)
=(
SELECT cdl.amount,
(cdl.denom_raw_cost/DECODE(cdl.Quantity, NULL, 1,0, 1,cdl.Quantity))
,cdl.burdened_cost
,(cdl.denom_burdened_cost/DECODE(cdl.Quantity, NULL,1,0,1,cdl.Quantity))
,cdl.acct_raw_cost
,cdl.acct_burdened_cost
,cdl.denom_raw_cost
,cdl.denom_burdened_cost
,cdl.project_raw_cost
,cdl.project_burdened_cost
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id=ei.expenditure_item_id
AND cdl.line_type='R'
AND NVL(cdl.reversed_flag,'N')='N'
AND cdl.line_num_reversed IS NULL)
WHERE ei.expenditure_item_id = l_Exp_item_id_tbl(i)
AND ei.Cost_distributed_flag='N'
AND ei.adjustment_type is not null
AND ei.costing_method is not null
AND EXISTS (
SELECT 1
FROM pa_cost_distribution_lines cdl2
WHERE cdl2.expenditure_item_id =ei.expenditure_item_id
);
G_err_stage := ' Stage-1 :Calling backoutitem Procedure to reverse selected Costed expenditure Items';
G_err_stage := 'Calling mark_processed_costedset procedure to update transfered_to_prj Flag in Payroll';
write_log(LOG, 'Update status on PA_PAY_PROJ_STATUS_ALL');
UPDATE PA_PAY_PROJ_STATUS_ALL
SET PROJECT_ROLLBACK_STATUS_IND='Y',
LAST_UPDATED_BY=g_user_id
WHERE INTERFACE_RUN_ID=l_Interface_run_idTab(i)
AND PERSON_ID =l_PersonIdTab(i) ;
SELECT
nvl(txs.costed_flag, 'N' ),
nvl(ALLOW_ADJUSTMENTS_FLAG,'Y')
INTO
l_trx_source_costed,
l_ALLOW_ADJUSTMENTS_FLAG
FROM
pa_expenditure_items ei ,
pa_transaction_sources txs
WHERE ei.transaction_source = txs.transaction_source(+)
AND ei.expenditure_item_id = X_expenditure_item_id;
SELECT COUNT(1)
INTO l_count
FROM pa_expenditure_items item,
pa_expenditures exp
WHERE exp.expenditure_id = item.expenditure_id
AND (exp.incurred_by_person_id =P_incurred_by_person_id
AND item.interface_run_id =P_interface_run_id)
AND item.cost_distributed_flag = 'N';
SELECT Count(EI1.EXPENDITURE_ITEM_ID)
INTO l_count2 /*Changed variable from l_count to l_count2 for bug:12915699 */
FROM PA_EXPENDITURE_ITEMS EI1,
PA_EXPENDITURES EXP1
WHERE EXP1.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
AND EI1.EXPENDITURE_ID = EXP1.EXPENDITURE_ID
AND EI1.INTERFACE_RUN_ID = P_interface_run_id
AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EXISTS
(
SELECT EI2.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI3,
PA_EXPENDITURE_ITEMS EI2,
PA_EXPENDITURES EXP2
WHERE EI3.interface_run_id = EI1.INTERFACE_RUN_ID
AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) =
SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND EXP2.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
AND EXP2.EXPENDITURE_ID = EI2.EXPENDITURE_ID
AND EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI2.COSTING_METHOD = 'ACTUAL'
AND EI2.PAYROLL_ACCRUAL_FLAG = 'N'
AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
AND EI2.INTERFACE_RUN_ID > EI1.INTERFACE_RUN_ID
UNION
/* adjustment is not by PAXREVTXN reversal process but from OTL */
SELECT EI4.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI5 ,
PA_EXPENDITURE_ITEMS EI4 ,
PA_EXPENDITURES EXP4
WHERE EI5.interface_run_id = EI1.INTERFACE_RUN_ID
AND EXP4.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
AND EXP4.EXPENDITURE_ID = EI4.EXPENDITURE_ID
AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1) =
SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND NVL(EI4.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
AND EI4.INTERFACE_RUN_ID IS NULL
AND NOT EXISTS
(SELECT EXPENDITURE_ITEM_ID FROM PA_EXPEND_ITEM_ADJ_ACTIVITIES ADJ
WHERE ADJ.EXPENDITURE_ITEM_ID = EI4.EXPENDITURE_ITEM_ID
AND ADJ.MODULE_CODE = 'PAXREVTXN'
AND EXCEPTION_ACTIVITY_CODE = 'REINITIATE EI TO DIST' ) );
( SELECT DISTINCT ps.assignment_action_id ,
src.element_type_id ,
'N' transfered_to_prj
FROM
pa_pay_audit_all aud ,
pay_costing_projects_v src,
pa_pay_proj_status_all ps
WHERE
aud.interface_run_id = p_interface_run_id
AND ps.interface_run_id = aud.interface_run_id
AND ps.person_id = p_person_id
AND src.time_period_id = aud.time_period_id
AND TRUNC(NVL(src.SOURCE_START_DATE,src.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
AND TRUNC(NVL(src.SOURCE_END_DATE,src.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) /*Added:12611695 */
AND src.payroll_id = aud.payroll_id
AND src.assignment_action_id = ps.assignment_action_id
)
LOOP
IF g_debug_mode ='Y' THEN
write_log (LOG,'Mark_processed_Costedset:p_element_type_id =>'||X.element_type_id);
pay_core_utils.update_prj_flag (p_element_type_id => x.element_type_id
,p_assignment_action_id =>x.assignment_action_id
,p_flag => x.transfered_to_prj
,p_status => l_status );
write_log (LOG,'pay_core_utils.update_prj_flag:p_status=>'||l_status);
p_program_update_date pa_cost_distribution_lines.program_update_date%TYPE;
SELECT ITEMS.adjusted_expenditure_item_id,
ITEMS.transferred_from_exp_item_id,
TRN.gl_accounted_flag,
TRN.transaction_source
,ITEMS.expenditure_item_date
,ITEMS.org_id
,NVL(ITEMS.recvr_org_id,ITEMS.ORG_ID)
,ITEMS.system_linkage_function
,ITEMS.expenditure_id
INTO p_parent_adjusted_id,
p_parent_transferred_id,
p_gl_accounted_flag,
p_transaction_source
,l_ei_date
,l_org_id
,l_recvr_org_id
,l_sys_link_function
,l_exp_id
FROM pa_expenditure_items_All ITEMS,
pa_transaction_sources TRN
WHERE ITEMS.transaction_source = TRN.transaction_source (+)
AND ITEMS.expenditure_item_id = X_exp_item_id;
SELECT imp1.set_of_books_id, imp2.set_of_books_id
INTO l_sob_id, l_recvr_sob_id
FROM pa_implementations_all imp1, pa_implementations_all imp2
WHERE imp1.org_id = l_org_id
AND imp2.org_id = l_recvr_org_id;
SELECT max(cdl.line_num)
INTO actual_cdl_line_num
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id = X_exp_item_id and cdl.line_type = 'R';
SELECT amount
, dr_code_combination_id
, cr_code_combination_id
, transfer_status_code
, quantity
, billable_flag
, request_id
, program_application_id
, program_id
, program_update_date
, pa_date
, gl_date
, transferred_date
, transfer_rejection_reason
, line_type
, ind_compiled_set_id
, nvl(burdened_cost,0) + nvl(projfunc_burdened_change,0)
, line_num_reversed
, reversed_flag
, system_reference1
, system_reference2
, system_reference3
, denom_currency_code
, denom_raw_cost
, NVL(denom_burdened_cost,0) + nvl(denom_burdened_change,0)
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, NVL(acct_burdened_cost,0) + nvl(acct_burdened_change,0)
, project_currency_code
, project_rate_date
, project_rate_type
, project_exchange_rate
, project_id
, task_id
, recvr_gl_date
, Projfunc_currency_code
, Projfunc_cost_rate_date
, Projfunc_cost_rate_type
, Projfunc_cost_exchange_rate
, Project_raw_cost
, NVL(Project_burdened_cost,0) + nvl(project_burdened_change,0)
, Work_type_id
, system_reference4
, system_reference5
, decode(si_assets_addition_flag, 'R','T', 'O', 'T', 'Y', 'T', 'N', 'T',si_assets_addition_flag )
, rate_source_code
, costing_method
INTO p_amount,
p_dr_ccid,
p_cr_ccid,
p_transfer_status_code,
p_quantity,
p_billable_flag,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_pa_date,
p_gl_date,
p_transferred_date,
p_transfer_rejection_reason,
p_line_type,
p_ind_complied_set_id,
p_burdened_cost,
p_line_num_reversed,
p_reversed_flag,
p_cdlsr1,
p_cdlsr2,
p_cdlsr3,
p_denom_currency_code,
p_denom_raw_cost,
p_denom_burdened_cost,
p_acct_currency_code,
p_acct_rate_date,
p_acct_rate_type,
p_acct_exchange_rate,
p_acct_raw_cost,
p_acct_burdened_cost,
p_project_currency_code,
p_project_rate_date,
p_project_rate_type,
p_project_exchange_rate,
p_project_id,
p_task_id
, p_recvr_gl_date
, p_Projfunc_currency_code
, p_Projfunc_cost_rate_date
, p_Projfunc_cost_rate_type
, p_Projfunc_cost_exchange_rate
, p_Project_raw_cost
, p_Project_burdened_cost
, p_Work_type_id
, p_cdlsr4
, p_cdlsr5
, l_si_assets_addition_flag
, l_rate_source_code
, l_costing_method
FROM pa_cost_distribution_lines_All
WHERE expenditure_item_id = X_exp_item_id
AND line_num = actual_cdl_line_num;
, x_program_update_date => p_program_update_date
, X_pa_date => l_pa_date
, X_recvr_pa_date => l_recvr_pa_date
, X_gl_date => l_gl_date
, X_transferred_date => NULL
, X_transfer_rejection_reason => NULL
, X_line_type => p_line_type
, X_ind_compiled_set_id => p_ind_complied_set_id
, X_burdened_cost => -p_burdened_cost
, X_line_num_reversed => p_line_num_reversed
, X_reverse_flag => p_reversed_flag
, X_user => X_user
, X_err_code => p_err_code
, X_err_stage => p_err_stage
, X_err_stack => p_err_stack
, X_project_id => p_project_id
, X_task_id => p_task_id
, X_cdlsr1 => p_cdlsr1
, X_cdlsr2 => p_cdlsr2
, X_cdlsr3 => p_cdlsr3
, X_denom_currency_code => p_denom_currency_code
, X_denom_raw_cost => -p_denom_raw_cost
, X_denom_burden_cost => -p_denom_burdened_cost
, X_acct_currency_code => p_acct_currency_code
, X_acct_rate_date => p_acct_rate_date
, X_acct_rate_type => p_acct_rate_type
, X_acct_exchange_rate => p_acct_exchange_rate
, X_acct_raw_cost => -p_acct_raw_cost
, X_acct_burdened_cost => -p_acct_burdened_cost
, X_project_currency_code => p_project_currency_code
, X_project_rate_date => p_project_rate_date
, X_project_rate_type => p_project_rate_type
, X_project_exchange_rate => p_project_exchange_rate
, P_PaPeriodName => P_Pa_Period_Name
, P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
, P_GlPeriodName => P_Gl_Period_Name
, P_RecvrGlDate => l_recvr_gl_date
, P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
, P_Projfunc_currency_code => P_Projfunc_currency_code
, P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
, P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
, P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
, P_Project_Raw_Cost => -P_Project_Raw_Cost
, P_Project_Burdened_Cost => -P_Project_Burdened_Cost
, P_Work_Type_Id => P_Work_Type_Id
, p_cdlsr4 => p_cdlsr4
, p_si_assets_addition_flag => l_si_assets_addition_flag
, p_cdlsr5 => p_cdlsr5
, P_Parent_Line_Num => actual_cdl_line_num
, p_rate_source_code => l_rate_source_code
, p_costing_method => l_costing_method);
UPDATE pa_expenditure_items
SET cost_distributed_flag = 'Y'
WHERE expenditure_item_id = X_backout_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
, cost_burden_distributed_flag
, capital_event_id
, wip_resource_id
, inventory_item_id
, unit_of_measure
, document_header_id
, document_distribution_id
, document_line_number
, document_payment_id
, vendor_id
, document_type
, document_distribution_type
, location_id
, pay_element_type_id
, rate_source_code
, costing_method
, PAYROLL_ACCRUAL_FLAG)
SELECT
X_transfer_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 exp organization
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, 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
, NULL -- raw_cost
, NULL -- raw_cost_rate
, NULL -- BURDEN_cost
, NULL -- burden_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.ADJUSTED_expenditure_item_id -- adjusted_expenditure_item_id
, 'N' -- 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.expenditure_item_id -- tfr from exp item id
, ei.transaction_source -- transaction_source
, 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
, NULL -- receipt_currency_amount
, ei.receipt_currency_code -- receipt_currency_code
, ei.receipt_exchange_rate -- receipt_exchange_rate
, ei.denom_currency_code -- denom_currency_code
, NULL -- denom_raw_cost
, NULL -- 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
, NULL -- acct_raw_cost
, NULL -- 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
, NULL -- 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
, NULL -- acct_transfer_price
, NULL -- projacct_transfer_price
, ei.cc_markup_base_code -- cc_markup_base_code
, NULL -- 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
, NULL -- project raw cost
, NULL -- project burended cost
, ei.project_id
, ei.project_tp_rate_date
, ei.project_tp_rate_type
, ei.project_tp_exchange_rate
, NULL
, 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 */
/* 12.2 payroll intg enhancement */
, ei.location_id /* Bug 12663113 location_id and pay_element_type_id to be copied from parent ei*/
, ei.pay_element_type_id
, null --ei.rate_source_code
, null --ei.costing_method
, NULL --PAYROLL_ACCRUAL_FLAG
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;