[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 1756677. Moved out of procedure Insert_Expenditure_And_Group */
/* Bug#2373198 Modified data type from varchar2(20 to reflect as is in table */
/* overtime_expenditure_group varchar2(20); */
SELECT job_id
INTO Job_Id
FROM per_assignments_f a,
pa_expenditures ex
WHERE ex.expenditure_id = X_expenditure_id
AND a.person_id = ex.incurred_by_person_id
AND (a.assignment_type = 'E' OR a.assignment_type = 'C')
AND a.primary_flag = 'Y'
AND trunc(ex.expenditure_ending_date)
BETWEEN a.effective_start_date AND
a.effective_end_date;
* If control comes here, its possible that, the previous select is
* unable to get a job_id because, the expenditure_ending_date falls
* later to the effective_end_date and there's NO assignments for the
* person from then on.
* To handle this situation, the effective_end_date is mapped to the
* next weekending date - to get the job_id.
*/
SELECT job_id
INTO Job_Id
FROM per_assignments_f a,
pa_expenditures ex
WHERE ex.expenditure_id = X_expenditure_id
AND a.person_id = ex.incurred_by_person_id
AND (a.assignment_type = 'E' OR a.assignment_type = 'C')
AND a.primary_flag = 'Y'
AND trunc(ex.expenditure_ending_date)
BETWEEN a.effective_start_date AND
pa_utils.GetWeekEnding(a.effective_end_date);
PROCEDURE Update_Old_Overtime_Item (
Temp_Task IN number,
R_P_User_ID IN number,
R_P_Program_ID IN number,
R_P_Request_ID IN number,
R_P_Program_App_ID IN number,
R_Person_Id IN number,
R_Expenditure_End_Date IN date,
R_Overtime_Exp_Type IN varchar2) IS
X_status number;
(select expenditure_item_id
from pa_expenditure_items i
,pa_expenditures e
where i.system_linkage_function ||'' = 'OT'
and i.expenditure_id = e.expenditure_id
and e.incurred_by_person_id = R_Person_Id
and e.expenditure_ending_date = R_Expenditure_End_Date
and i.task_id = Temp_Task
and i.expenditure_item_date = R_Expenditure_End_Date
and i.expenditure_type = R_Overtime_Exp_Type
and nvl(i.net_zero_adjustment_flag,'N') <> 'Y') LOOP
Pa_Adjustments.SetNetZero(
c.expenditure_item_id
,R_P_User_ID
,0
,X_status);
END Update_Old_Overtime_Item;
(select
PA_EXPENDITURE_ITEMS_S.NEXTVAL X_expenditure_item_id
,i.expenditure_id X_expenditure_id
,i.expenditure_item_date X_expenditure_item_date
,i.task_id X_task_id
,i.expenditure_type X_expenditure_type
,i.non_labor_resource X_non_labor_resource
,i.organization_id X_nl_resource_org_id
,i.quantity* -1 X_quantity
,i.raw_cost* -1 X_raw_cost
,i.raw_cost_rate X_raw_cost_rate
,i.override_to_organization_id X_override_to_org_id
,i.billable_flag X_billable_flag
,i.bill_hold_flag X_bill_hold_flag
,i.orig_transaction_reference X_orig_transaction_ref
,i.transferred_from_exp_item_id X_transferred_from_ei
,i.adjusted_expenditure_item_id X_adj_expend_item_id
,i.attribute_category X_attribute_category
,i.attribute1 X_attribute1
,i.attribute2 X_attribute2
,i.attribute3 X_attribute3
,i.attribute4 X_attribute4
,i.attribute5 X_attribute5
,i.attribute6 X_attribute6
,i.attribute7 X_attribute7
,i.attribute8 X_attribute8
,i.attribute9 X_attribute9
,i.attribute10 X_attribute10
,NULL X_ei_comment
,i.transaction_source X_transaction_source
,i.source_expenditure_item_id X_source_exp_item_id
,i.job_id X_job_id
,i.org_id X_org_id
,i.labor_cost_multiplier_name X_labor_cost_multiplier_name
,NULL X_drccid
,NULL X_crccid
,NULL X_cdlsr1
,NULL X_cdlsr2
,NULL X_cdlsr3
,NULL X_gldate
,i.burden_cost* -1 X_bcost
,i.burden_cost_rate X_bcostrate
,i.system_linkage_function X_etypeclass
,i.burden_sum_dest_run_id X_burden_sum_dest_run_id
,i.cost_ind_compiled_set_id X_burden_Compile_set_id
,i.receipt_currency_amount X_receipt_currency_amount
,i.receipt_currency_code X_receipt_currency_code
,i.receipt_exchange_rate X_receipt_exchange_rate
,i.denom_currency_code X_denom_currency_code
,i.denom_raw_cost* -1 X_denom_raw_cost
,i.denom_burdened_cost* -1 X_denom_burdened_cost
,i.acct_currency_code X_acct_currency_code
,i.acct_rate_date X_acct_rate_date
,i.acct_rate_type X_acct_rate_type
,i.acct_exchange_rate X_acct_exchange_rate
,i.acct_raw_cost* -1 X_acct_raw_cost
,i.acct_burdened_Cost* -1 X_acct_burdened_cost
,i.acct_exchange_rounding_limit X_acct_exchange_rounding_limit
,i.project_currency_code X_project_currency_code
,i.project_rate_date X_project_rate_date
,i.project_rate_type X_project_rate_type
,i.project_exchange_rate X_project_exchange_rate
,i.CC_CROSS_CHARGE_CODE CC_CROSS_CHARGE_CODE
,i.CC_PRVDR_ORGANIZATION_ID CC_PRVDR_ORGANIZATION_ID
,i.CC_RECVR_ORGANIZATION_ID CC_RECVR_ORGANIZATION_ID
,i.DENOM_TP_CURRENCY_CODE DENOM_TP_CURRENCY_CODE
,i.DENOM_TRANSFER_PRICE DENOM_TRANSFER_PRICE
,i.ACCT_TP_RATE_TYPE ACCT_TP_RATE_TYPE
,i.ACCT_TP_RATE_DATE ACCT_TP_RATE_DATE
,i.ACCT_TP_EXCHANGE_RATE ACCT_TP_EXCHANGE_RATE
,i.ACCT_TRANSFER_PRICE ACCT_TRANSFER_PRICE
,i.PROJACCT_TRANSFER_PRICE PROJACCT_TRANSFER_PRICE
,i.CC_MARKUP_BASE_CODE CC_MARKUP_BASE_CODE
,i.TP_BASE_AMOUNT TP_BASE_AMOUNT
,i.CC_CROSS_CHARGE_TYPE CC_CROSS_CHARGE_TYPE
,i.RECVR_ORG_ID RECVR_ORG_ID
,decode(i.CC_CROSS_CHARGE_CODE,'B','N','X') CC_BL_DISTRIBUTED_CODE
,decode(i.CC_CROSS_CHARGE_CODE,'I','N','X') CC_IC_PROCESSED_CODE
,i.TP_IND_COMPILED_SET_ID TP_IND_COMPILED_SET_ID
,i.TP_BILL_RATE TP_BILL_RATE
,i.TP_BILL_MARKUP_PERCENTAGE TP_BILL_MARKUP_PERCENTAGE
,i.TP_SCHEDULE_LINE_PERCENTAGE TP_SCHEDULE_LINE_PERCENTAGE
,i.TP_RULE_PERCENTAGE TP_RULE_PERCENTAGE
,i.project_id X_project_id -- Bugfix:2201207
,i.projfunc_currency_code X_projfunc_currency_code
,i.projfunc_cost_rate_date X_projfunc_cost_rate_date
,i.projfunc_cost_rate_type X_projfunc_cost_rate_type
,i.projfunc_cost_exchange_rate X_projfunc_cost_exchg_rate
,i.assignment_id X_assignment_id
,i.work_type_id X_work_type_id
,i.tp_amt_type_code X_tp_amt_type_code
,i.project_raw_cost* -1 x_project_raw_cost
,i.project_burdened_cost* -1 x_project_burdened_cost
from pa_expenditure_items i
,pa_expenditures e
where i.system_linkage_function ||'' = 'OT'
and i.expenditure_id = e.expenditure_id
and e.incurred_by_person_id = R_Person_Id
and e.expenditure_ending_date = R_Expenditure_End_Date
and i.task_id = Temp_task
and i.expenditure_item_date = R_Expenditure_End_Date
and i.expenditure_type = R_Overtime_Exp_Type
and nvl(i.net_zero_adjustment_flag,'N') <> 'Y') LOOP
i := i + 1;
,0 -- last_update_login
,NULL -- module
,NULL -- calling_process
,i -- Rows
,X_status
,NULL -- gl_flag
);
PROCEDURE Insert_Expenditure_And_Group(
Expenditure_ID IN OUT NOCOPY number,
R_P_User_ID IN number,
R_P_Program_ID IN number,
R_P_Request_ID IN number,
R_P_Program_App_ID IN number,
R_Person_Id IN number,
R_Expenditure_End_Date IN Date,
R_Overtime_Exp_Type IN varchar2,
R_Organization IN number) IS
Cycle_Start_Day number;
SELECT Exp_Cycle_Start_Day_Code
INTO Cycle_Start_Day
FROM PA_Implementations;
select 'PREMIUM - ' || to_char(R_P_Request_ID)
into overtime_expenditure_group
from sys.dual;
Pa_Transactions.InsertExpGroup(
Overtime_Expenditure_Group
,'RELEASED'
,trunc(sysdate) - to_number(to_char(sysdate-Cycle_Start_Day+1,'D')) + 7
,'ST'
,R_P_User_ID
,NULL
,NULL /*6317198*/
,l_org_id /*6317198*/ );
select PA_EXPENDITURES_S.NEXTVAL INTO Expenditure_ID FROM sys.dual;
Pa_Transactions.InsertExp(
X_expenditure_id => Expenditure_ID,
X_expend_status => 'APPROVED',
X_expend_ending => R_Expenditure_End_Date,
X_expend_class => 'PT',
X_inc_by_person => R_Person_Id,
X_inc_by_org => R_Organization,
X_expend_group => Overtime_Expenditure_Group,
X_entered_by_id => R_P_User_ID,
X_created_by_id => R_P_User_ID,
X_attribute_category => NULL,
X_attribute1 => NULL,
X_attribute2 => NULL,
X_attribute3 => NULL,
X_attribute4 => NULL,
X_attribute5 => NULL,
X_attribute6 => NULL,
X_attribute7 => NULL,
X_attribute8 => NULL,
X_attribute9 => NULL,
X_attribute10 => NULL,
X_description => 'System created temporary overtime expenditure',
X_control_total => NULL,
P_Org_Id => l_org_id /*6317198*/ );
END Insert_Expenditure_And_Group;
PROCEDURE Insert_Overtime_Items(
Temp_Existing_Hours IN number,
Temp_Actual_Hours IN number,
Temp_Task IN number,
Temp_LCM IN varchar2,
Expenditure_ID IN OUT NOCOPY number,
R_P_User_ID IN number,
R_P_Program_ID IN number,
R_P_Request_ID IN number,
R_P_Program_App_ID IN number,
R_Person_Id IN number,
R_Expenditure_End_Date IN date,
R_Overtime_Exp_Type IN varchar2,
R_Organization IN number) IS
Any_Data_Flag varchar2(1);
select p.project_id
,p.projfunc_currency_code
from pa_tasks t
,pa_projects p
where t.task_id = Temp_Task
and t.project_id = p.project_id;
Insert_Expenditure_And_Group(
Expenditure_ID,
R_P_User_ID,
R_P_Program_ID,
R_P_Request_ID,
R_P_Program_App_ID,
R_Person_Id,
R_Expenditure_End_Date,
R_Overtime_Exp_Type,
R_Organization);
Update_Old_Overtime_Item(
Temp_Task,
R_P_User_ID,
R_P_Program_ID,
R_P_Request_ID,
R_P_Program_App_ID,
R_Person_Id,
R_Expenditure_End_Date,
R_Overtime_Exp_Type);
select PA_EXPENDITURE_ITEMS_S.NEXTVAL
into X_expenditure_item_id
from sys.dual;
,0 -- last_update_login
,NULL -- module
,NULL -- calling_process
,1 -- rows
,X_status
,NULL -- gl_flag
);
END Insert_Overtime_Items;
SELECT SUM(ITEM2.Quantity),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'1',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'2',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'3',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'4',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'5',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'6',ITEM2.Quantity,0)),
SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
'7',ITEM2.Quantity,0))
INTO Total_Hours, Sunday, Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday
FROM PA_Expenditure_Items ITEM2,
PA_Expenditures EXP2
WHERE EXP2.Incurred_By_Person_ID = R_Person_ID
AND EXP2.Expenditure_Ending_Date = R_Expenditure_End_Date
AND EXP2.Expenditure_Status_Code||'' = 'APPROVED'
AND EXP2.Expenditure_ID = ITEM2.Expenditure_ID
AND ITEM2.Quantity <> 0
AND ITEM2.System_Linkage_Function||'' = 'ST';
select nvl(sum(decode(ITEM.task_id,
OTaskID_Tab(1),ITEM.quantity,
0)),0),
nvl(sum(decode(ITEM.task_id,
OTaskID_Tab(2),ITEM.quantity,
0)),0),
nvl(sum(decode(ITEM.task_id,
OTaskID_Tab(3),ITEM.quantity,
0)),0),
nvl(sum(decode(ITEM.task_id,
OTaskID_Tab(4),ITEM.quantity,
0)),0),
nvl(sum(decode(ITEM.task_id,
OTaskID_Tab(5),ITEM.quantity,
0)),0)
into Existing_Double_Time_Hours,
Existing_Half_Time_Hours,
Existing_Uncomp_Time_Hours,
Existing_Extra_OT_Hours_1,
Existing_Extra_OT_Hours_2
FROM PA_expenditure_items ITEM
,PA_expenditures EXP
WHERE EXP.Incurred_By_Person_Id = R_Person_Id
AND EXP.Expenditure_Ending_Date = R_Expenditure_End_Date
AND ITEM.Expenditure_Id = EXP.Expenditure_Id
AND ITEM.Expenditure_Item_Date = R_Expenditure_End_Date
AND ITEM.System_Linkage_Function ||'' = 'OT';
Insert_Overtime_Items(
Existing_Uncomp_Time_Hours,
Actual_Uncomp_Time_Hours,
OTaskID_Tab(i),
OTaskLCM_Tab(i),
Expenditure_ID,
R_P_User_ID,
R_P_Program_ID,
R_P_Request_ID,
R_P_Program_App_ID,
R_Person_Id,
R_Expenditure_End_Date,
R_Overtime_Exp_Type,
R_Organization);
Insert_Overtime_Items(
Existing_Half_Time_Hours,
Actual_Half_Time_Hours,
OTaskID_Tab(i),
OTaskLCM_Tab(i),
Expenditure_ID,
R_P_User_ID,
R_P_Program_ID,
R_P_Request_ID,
R_P_Program_App_ID,
R_Person_Id,
R_Expenditure_End_Date,
R_Overtime_Exp_Type,
R_Organization);
Insert_Overtime_Items(
Existing_Double_Time_Hours,
Actual_Double_time_Hours,
OTaskID_Tab(i),
OTaskLCM_Tab(i),
Expenditure_ID,
R_P_User_ID,
R_P_Program_ID,
R_P_Request_ID,
R_P_Program_App_ID,
R_Person_Id,
R_Expenditure_End_Date,
R_Overtime_Exp_Type,
R_Organization);
SELECT t.task_id overtime_task_id
, t.labor_cost_multiplier_name overtime_LCM
, t.task_name overtime_task_name
, p.project_currency_code proj_curr_code
, p.projfunc_currency_code projfunc_currency_code
FROM pa_tasks t
, pa_projects p
WHERE t.project_id = p.project_id
AND p.segment1 = 'OT') LOOP
EXIT WHEN x_count >= 5;
INSERT INTO PA_Spawned_Program_Statuses
(
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Request_ID,
Program_ID,
Program_Application_ID,
Program_Update_Date
)
VALUES
(
SYSDATE,
R_P_User_ID,
SYSDATE,
R_P_User_ID,
R_P_Request_ID,
R_P_Program_ID,
R_P_Program_App_ID,
SYSDATE
);