[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pa_expenditure_items_s.nextval expenditure_item_id
, X_new_exp_id expenditure_id
, i.task_id
, to_number( NULL ) raw_cost
, to_number( NULL ) raw_cost_rate
, decode( copy_mode, 'O',
next_day((X_date-7), /* bug 3693848 : Removed to_date function because X_date is already a date parameter */
to_char(i.expenditure_item_date, 'DAY')),
X_date ) expenditure_item_date
, i.organization_id /*added for bug-2478552*/
, i.non_labor_resource /*added for bug-2478552*/
, i.expenditure_type
, i.system_linkage_function
, decode( copy_mode, 'S', NULL, i.quantity ) quantity
, t.project_id
, t.billable_flag
, i.attribute_category
, i.attribute1
, i.attribute2
, i.attribute3
, i.attribute4
, i.attribute5
, i.attribute6
, i.attribute7
, i.attribute8
, i.attribute9
, i.attribute10
, X_person_id person_id
, job_id
, i.org_id
, i.labor_cost_multiplier_name
, i.receipt_currency_amount
, i.receipt_currency_code
, i.receipt_exchange_rate
, i.denom_currency_code
, i.denom_raw_cost
, i.denom_burdened_cost
, i.acct_currency_code
, i.acct_rate_date
, i.acct_rate_type
, i.acct_exchange_rate
, i.acct_raw_cost
, i.acct_burdened_cost
, i.acct_exchange_rounding_limit
, i.project_currency_code
, i.project_rate_type
, i.project_rate_date
, i.project_exchange_rate
, i.work_type_id
, i.assignment_id
, i.projfunc_currency_code
, i.projfunc_cost_rate_type
, i.projfunc_cost_rate_date
, i.projfunc_cost_exchange_rate
, com.expenditure_comment --bug 3066137
, i.cbs_element_id -- Added for Bug 16387823
FROM
pa_tasks t
, pa_expenditure_items i
, pa_expenditure_comments com --bug 3066137
WHERE
( X_exp_class_code = 'OE'
/** OR i.system_linkage_function = 'ST' ) Bug fix : 2329146 **/
OR i.system_linkage_function IN ('ST','OT','ER','USG','PJ','INV','WIP','BTC') )
/* bug#2794006 added 'PJ','INV','WIP','BTC' */
/* Found during unit testing for bug 2683803. Was not picking up eis whose slf is OT */
AND i.task_id = t.task_id
AND i.expenditure_item_id = com.expenditure_item_id(+) --bug 3066137
AND i.expenditure_id = X_orig_exp_id
AND i.adjusted_expenditure_item_id IS NULL
AND nvl(i.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND i.source_expenditure_item_id IS NULL;
Select Tp_Amt_Type_Code
into l_Tp_Amt_Type_Code
from Pa_Work_Types_Vl
where Work_Type_Id = P_Work_Type_Id;
the same approach here, loadei will insert the defaults for
all IC columns.
*/
-- Begin bug 2678790
l_labor_cost_multiplier_name := Check_lcm(P_lcm_name => ei.labor_cost_multiplier_name,
P_ei_Date => EI.expenditure_item_date);
select decode(EI.system_linkage_function,'BTC',EI.denom_burdened_cost,NULL)
into l_denom_burdened_cost
from dual; /* bug#2794006 */
, P_Update_Emp_Orgs IN VARCHAR2 default null)
IS
num_copied NUMBER := 0;
SELECT
expenditure_id orig_exp_id
, description
, incurred_by_organization_id /* Included for Bug#2366542 */
, nvl( new_inc_by_person, incurred_by_person_id ) person_id
, decode( copy_mode, 'S', NULL,
decode( copy_items, 'Y', control_total_amount, NULL ))
control_total_amount
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, denom_currency_code
, acct_currency_code
, acct_rate_type
, acct_rate_date
, acct_exchange_rate
, person_type -- fix for bug : 3645842
FROM
pa_expenditures
WHERE
expenditure_group = orig_exp_group
AND expenditure_id = nvl( orig_exp_id, expenditure_id )
ORDER BY expenditure_id;
SELECT pa_expenditures_s.nextval
INTO new_exp_id
FROM DUAL ;
If Nvl(P_Update_Emp_Orgs,'Y') = 'Y' Then
ValidateEmp ( EXP.person_id
, exp_ending_date
, outcome );
pa_transactions.InsertExp(
X_expenditure_id => new_exp_id,
X_expend_status => 'WORKING',
X_expend_ending => exp_ending_date,
/** X_expend_class => 'PT', bug fix : 2329146 **/
X_expend_class => X_exp_class_code,
X_inc_by_person => EXP.person_id,
/* X_inc_by_org => org_id, remmed out for bug 2683803 */
/* Used a local variable to hold the inc by org id instead
old the global variable because it get changed when validateemp()
gets called. Part of bug 2683803 enhancement unit testing */
X_inc_by_org => l_Inc_By_Org_id,
X_expend_group => new_exp_group,
X_entered_by_id => X_user,
X_created_by_id => X_user,
X_attribute_category => EXP.attribute_category,
X_attribute1 => EXP.attribute1,
X_attribute2 => EXP.attribute2,
X_attribute3 => EXP.attribute3,
X_attribute4 => EXP.attribute4,
X_attribute5 => EXP.attribute5,
X_attribute6 => EXP.attribute6,
X_attribute7 => EXP.attribute7,
X_attribute8 => EXP.attribute8,
X_attribute9 => EXP.attribute9,
X_attribute10 => EXP.attribute10,
X_description => EXP.description,
X_control_total => EXP.control_total_amount,
X_denom_currency_code => EXP.denom_currency_code,
X_acct_currency_code => EXP.acct_currency_code,
X_acct_rate_type => temp_acct_rate_type, --Bug 14497947 Start
X_acct_rate_date => temp_acct_rate_date,
X_acct_exchange_rate => temp_acct_exchange_rate, --Bug 14497947 End
X_person_type => EXP.person_type,
P_Org_Id => l_Org_Id); -- 12i MOAC changes
SELECT
e.expenditure_class_code,
e.incurred_by_organization_id
INTO
X_exp_class_code,
l_inc_by_org_id
FROM
pa_expenditures e
WHERE
e.expenditure_id = orig_exp_id;
InsertExp Boolean := TRUE ;
InsertBatch Boolean := FALSE ;
SELECT
e.expenditure_id orig_exp_id
, pa_expenditures_s.nextval new_exp_id
, e.expenditure_ending_date
, e.description
, e.incurred_by_person_id person_id
, e.incurred_by_organization_id inc_by_org_id
, e.expenditure_class_code
, e.control_total_amount
, e.attribute_category
, e.attribute1
, e.attribute2
, e.attribute3
, e.attribute4
, e.attribute5
, e.attribute6
, e.attribute7
, e.attribute8
, e.attribute9
, e.attribute10
, e.denom_currency_code
, e.acct_currency_code
, e.acct_rate_type
, e.acct_rate_date
, e.acct_exchange_rate
, e.person_type -- CWK change
FROM
pa_expenditures e
WHERE
e.expenditure_group = X_orig_exp_group ;
select
ei.expenditure_item_id
, ei.net_zero_adjustment_flag
, ei.source_expenditure_item_id
, ei.transferred_from_exp_item_id
, ei.task_id
, ei.transaction_source -- OTC changes
, ei.orig_transaction_reference -- OTC changes
from
pa_expenditure_items_all ei
where
expenditure_id = expend_id ;
select
expenditure_group
, expenditure_ending_date
, system_linkage_function
, control_count
, control_total_amount
, request_id
, program_id
, program_application_id
, transaction_source
from
pa_expenditure_groups
where
expenditure_group = X_orig_exp_group ;
SELECT 1
INTO Dummy
FROM pa_expenditure_groups
WHERE expenditure_group = X_new_exp_group;
InsertExp := TRUE ;
InsertExp := FALSE ;
SELECT project_id
INTO l_project_id
FROM pa_tasks
WHERE task_id = ExpEi.task_id;
SELECT expenditure_item_id
INTO l_BackOutExp_Id
FROM pa_expenditure_items_all
WHERE adjusted_expenditure_item_id = ExpEi.expenditure_item_id;
If ( InsertExp ) and (no_of_items > 0) then
IF X_expgrp_status = 'WORKING' THEN
exp_status := 'SUBMITTED';
pa_transactions.InsertExp(
X_expenditure_id => Exp.new_exp_id,
X_expend_status => exp_status,
X_expend_ending => Exp.expenditure_ending_date ,
X_expend_class => Exp.expenditure_class_code ,
X_inc_by_person => Exp.person_id ,
X_inc_by_org => Exp.inc_by_org_id ,
X_expend_group => X_new_exp_group ,
X_entered_by_id => X_user_id ,
X_created_by_id => X_user_id ,
X_attribute_category => Exp.attribute_category ,
X_attribute1 => Exp.attribute1 ,
X_attribute2 => Exp.attribute2 ,
X_attribute3 => Exp.attribute3 ,
X_attribute4 => Exp.attribute4 ,
X_attribute5 => Exp.attribute5 ,
X_attribute6 => Exp.attribute6 ,
X_attribute7 => Exp.attribute7 ,
X_attribute8 => Exp.attribute8 ,
X_attribute9 => Exp.attribute9 ,
X_attribute10 => Exp.attribute10 ,
X_description => Exp.description ,
X_control_total => Exp.control_total_amount,
X_denom_currency_code => Exp.denom_currency_code ,
X_acct_currency_code => Exp.acct_currency_code ,
X_acct_rate_type => Exp.acct_rate_type ,
X_acct_rate_date => Exp.acct_rate_date ,
X_acct_exchange_rate => Exp.acct_exchange_rate,
X_person_type => Exp.person_type,
P_Org_Id => l_Org_Id); -- CWK change
X_last_update_login => FND_GLOBAL.LOGIN_ID,
X_program_application_id => null,
X_program_id => null,
X_request_id => null);
InsertBatch := TRUE ;
if ((InsertBatch ) AND (X_module <> 'PAXTREPE')) then
OPEN ReverseGroup ;
pa_transactions.InsertExpGroup(
X_expenditure_group => X_new_exp_group ,
X_exp_group_status_code => X_expgrp_status ,
X_ending_date => ExpGroup.expenditure_ending_date ,
X_system_linkage => ExpGroup.system_linkage_function ,
X_created_by => X_user_id ,
X_transaction_source => ExpGroup.transaction_source,
P_Org_Id => l_Org_Id); -- 12i MOAC changes
select
labor_cost_multiplier_name
into
l_lcm_Name
from
pa_labor_cost_multipliers
where
labor_cost_multiplier_name = P_Lcm_Name
and P_Ei_Date Between Start_Date_Active
And End_Date_Active;