[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_last_updated_by Number;
G_last_update_login Number;
G_last_update_date Date;
select project_raw_cost,
project_currency_code
from pa_expenditure_items_all where expenditure_item_id=p_expenditure_item_id;
Select
tp_rate_override,
tp_currency_override,
tp_calc_base_code_override,
tp_percent_applied_override
into
l_tp_rate_ovrd,
l_tp_currency_ovrd,
l_tp_calc_base_code_ovrd,
l_tp_percent_applied_ovrd
from
pa_project_assignments
where
assignment_id = p_assignment_id(i)
and p_expenditure_item_date(i) between
start_date and end_date ;
SELECT conversion_type
INTO l_rate_type1
FROM gl_daily_conversion_types
WHERE (user_conversion_type = l_rate_type
OR conversion_type = l_rate_type);
SELECT project_bil_rate_date_code,
project_bil_rate_type,
project_bil_rate_date,
project_bil_exchange_rate,
projfunc_bil_rate_date_code,
projfunc_bil_rate_type,
projfunc_bil_rate_date,
nvl(multi_currency_billing_flag,'N'),
projfunc_bil_exchange_rate
INTO l_project_bil_rate_date_code,
l_project_bil_rate_type,
l_project_bil_rate_date,
l_project_bil_exchange_rate,
l_projfunc_bil_rate_date_code,
l_projfunc_bil_rate_type,
l_projfunc_bil_rate_date,
l_multi_currency_billing_flag,
l_projfunc_bil_exchange_rate
FROM pa_projects_all
WHERE project_id = p_project_id(i);
G_last_update_login := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1);
G_last_updated_by := G_created_by;
G_last_update_date := G_sysdate ;
SELECT org_information2 legal_entity_id
FROM hr_organization_information
WHERE organization_id = p_org_id
AND org_information_context = 'Operating Unit Information';
select legal_entity_id
from pa_implementations_all
where org_id = p_org_id;*/
select business_group_id
from hr_operating_units
where organization_id = p_org_id;
select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
EXP_ORG_STRUCTURE_VERSION_ID
from pa_implementations;
select org_id,business_group_id,cc_default_rate_type,cc_default_rate_date_code,
EXP_ORG_STRUCTURE_VERSION_ID
from pa_implementations_all
where org_id = l_org_id;
SELECT FC.Currency_Code
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS GB,
PA_IMPLEMENTATIONS_ALL IMP
WHERE FC.Currency_Code = DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID
AND IMP.Org_Id = l_org_id;
pa_debug.G_Err_Stage := 'Insert Schedule Line into Lookup';
Insert_Schedule_Line_Into_Lkp(
p_prvdr_organization_id(i),
p_recvr_org_id (i),
p_recvr_organization_id(i),
p_tp_schedule_id (i),
l_tp_schedule_line_id,
p_labor_non_labor_flag(i),
--Start Added for devdrop2
p_tp_amt_type_code(i),
--End Added for devdrop2
l_start_date_active,
l_end_date_active,
p_sort_order, -- added for bug 5753774
x_error_code(i)
);
select tp_schedule_line_id
from PA_CC_TP_SCHEDULE_LINE_LKP
where
tp_schedule_id = p_tp_schedule_id
and prvdr_organization_id = p_prvdr_organization_id
and recvr_organization_id = p_recvr_organization_id
and prvdr_org_id = G_prvdr_org_id
and recvr_org_id = p_recvr_org_id
and labor_flag = p_labor_flag
and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
/* and trunc(G_processed_thru_date) between Commented for 3118101 */
and trunc(p_expenditure_item_date) between /* Added for 3118101 */
trunc(start_date_active) and
/* trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
select tp_schedule_line_id,sort_order, -- Added for bug 5753774
Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
nl_tp_rule_id) rule_id,
Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
nl_percentage_applied) percentage_applied
,start_date_active
,end_date_active
from pa_cc_tp_schedule_lines
where tp_schedule_id = p_tp_schedule_id
and prvdr_organization_id = l_prvdr_organization_id
and recvr_organization_id = l_recvr_organization_id
and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
/* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
/* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
select a.tp_schedule_line_id,sort_order, -- Added for bug 5753774
Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
a.nl_tp_rule_id) rule_id,
Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
a.nl_percentage_applied) percentage_applied,
a.start_date_active,
a.end_date_active
from pa_cc_tp_schedule_lines a,
pa_org_hierarchy_denorm b,
pa_org_hierarchy_denorm c
where a.tp_schedule_id = p_tp_schedule_id
and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
and a.RECVR_ORGANIZATION_ID = c.PARENT_ORGANIZATION_ID
and c.CHILD_ORGANIZATION_ID = l_recvr_organization_id
and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
and c.org_hierarchy_version_id = G_prj_org_struct_ver_id
and b.pa_org_use_type = 'TP_SCHEDULE'
and c.pa_org_use_type = 'TP_SCHEDULE'
and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
/* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
/* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
order by b.parent_level desc , c.PARENT_LEVEL desc;
select tp_schedule_line_id,sort_order, -- Added for bug 5753774
Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
nl_tp_rule_id) rule_id,
Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
nl_percentage_applied) percentage_applied
,start_date_active
,end_date_active
from pa_cc_tp_schedule_lines a,
pa_org_hierarchy_denorm b
where a.tp_schedule_id = p_tp_schedule_id
and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
and a.recvr_organization_id = l_recvr_organization_id
and b.pa_org_use_type = 'TP_SCHEDULE'
and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
/* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
/* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
order by b.parent_level desc;
select a.tp_schedule_line_id,sort_order, -- Added for Bug 5753774
Decode(p_labor_non_labor_flag,'Y',a.labor_tp_rule_id,
a.nl_tp_rule_id) rule_id,
Decode(p_labor_non_labor_flag,'Y',a.labor_percentage_applied,
a.nl_percentage_applied) percentage_applied
,a.start_date_active
,a.end_date_active
from pa_cc_tp_schedule_lines a,
pa_org_hierarchy_denorm b
where a.tp_schedule_id = p_tp_schedule_id
and a.PRVDR_ORGANIZATION_ID = b.PARENT_ORGANIZATION_ID
and b.CHILD_ORGANIZATION_ID = l_prvdr_organization_id
and a.recvr_organization_id is null
and b.org_hierarchy_version_id = G_exp_org_struct_ver_id
and b.pa_org_use_type = 'TP_SCHEDULE'
and decode( nvl(tp_amt_type_code,'COST_REVENUE'),
'COST_REVENUE',nvl(p_tp_amt_type_code,'COST_REVENUE'),
tp_amt_type_code) = nvl(p_tp_amt_type_code,'COST_REVENUE')
and ((p_labor_non_labor_flag='Y' and a.labor_tp_rule_id is not null)
OR (p_labor_non_labor_flag='N' and a.nl_tp_rule_id is not null))
/* and trunc(G_processed_thru_date) between trunc(a.start_date_active) Commented for 3118101 */
and trunc(p_expenditure_item_date) between trunc(a.start_date_active) /* Added for 3118101 */
/* and trunc(NVL(a.end_date_active,G_processed_thru_date)) Commented for 3118101 */
and trunc(NVL(a.end_date_active,p_expenditure_item_date)) /* Added for 3118101 */
order by b.parent_level desc;
select tp_schedule_line_id,sort_order, --Added for bug 5753774
Decode(p_labor_non_labor_flag,'Y',labor_tp_rule_id,
nl_tp_rule_id) rule_id,
Decode(p_labor_non_labor_flag,'Y',labor_percentage_applied,
nl_percentage_applied) percentage_applied
,start_date_active
,end_date_active
from pa_cc_tp_schedule_lines
where tp_schedule_id = p_tp_schedule_id
and default_flag ='Y'
and ((p_labor_non_labor_flag='Y' and labor_tp_rule_id is not null)
OR (p_labor_non_labor_flag='N' and nl_tp_rule_id is not null))
/* and trunc(G_processed_thru_date) between trunc(start_date_active) Commented for 3118101 */
and trunc(p_expenditure_item_date) between trunc(start_date_active) /* Added for 3118101 */
/* and trunc(NVL(end_date_active,G_processed_thru_date)); Commented for 3118101 */
select PROJ_ORG_STRUCTURE_VERSION_ID into G_prj_org_struct_ver_id
from pa_implementations_all
where org_id = nvl(G_prev_rcvr_org_id,-1); /* For Bug 5900371. Modified nvl(org_id,-1) to org_id as Org_id is mandatory in R12 and hence can never be Null */
PROCEDURE Insert_Schedule_Line_Into_Lkp(
p_prvdr_organization_id IN Number,
p_recvr_org_id IN Number,
p_recvr_organization_id IN Number,
p_tp_schedule_id IN Number,
p_tp_schedule_line_id IN Number,
p_labor_flag IN Varchar2,
--Start Added for devdrop2
p_tp_amt_type_code IN Varchar2,
--End Added for devdrop2
p_start_date_active IN Date,
p_end_date_active IN Date,
p_sort_order IN Number, -- Added for bug 5753774
x_error_code IN OUT NOCOPY Varchar2 /*File.sql.39*/
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
pa_debug.Set_err_stack ('Insert_Schedule_Line_Into_Lkp');
pa_debug.G_Err_Stage := 'Starting Insert_Schedule_Line_Into_Lkp';
INSERT INTO
PA_CC_TP_SCHEDULE_LINE_LKP
(tp_schedule_id,
tp_schedule_line_id,
prvdr_org_id,
prvdr_organization_id,
recvr_org_id,
recvr_organization_id,
labor_flag,
tp_amt_type_code,
start_date_active,
sort_order, -- Added for bug 5753774
creation_date,
created_by,
last_update_date,
last_updated_by,
end_date_active,
last_update_login)
VALUES
(p_tp_schedule_id,
p_tp_schedule_line_id,
G_prvdr_org_id,
p_prvdr_organization_id,
p_recvr_org_id,
p_recvr_organization_id,
p_labor_flag,
p_tp_amt_type_code,
p_start_date_active,
p_sort_order, -- Added for bug 5753774
G_creation_date,
G_created_by,
G_last_update_date,
G_last_updated_by,
p_end_date_active,
G_last_update_login);
pa_debug.G_Err_Stage := 'Exitting Insert_Schedule_Line_Into_Lkp';
END Insert_Schedule_Line_Into_Lkp;
select
Decode (p_labor_flag ,'Y', labor_tp_rule_id,nl_tp_rule_id ),
Decode (p_labor_flag,'Y',labor_percentage_applied,nl_percentage_applied)
from pa_cc_tp_schedule_lines
where tp_schedule_line_id = p_tp_schedule_line_id;
select calc_method_code,markup_calc_base_code,percentage_applied,schedule_id
from pa_cc_tp_rules
where tp_rule_id = l_tp_rule_id;
select project_raw_cost,
project_currency_code,
project_burdened_cost,
burden_cost,
acct_burdened_cost,
/* Added for bug 2697945 */
bill_trans_raw_revenue,
bill_trans_currency_code,
/* Added for bug 2820252 */
bill_trans_adjusted_revenue
from pa_expenditure_items_all where expenditure_item_id=p_expenditure_item_id;
SELECT 1 INTO l_check_line
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM pa_cust_rev_dist_lines_all
WHERE expenditure_item_id = p_expenditure_item_id(i)
UNION ALL
SELECT 1
FROM pa_cc_dist_lines_all
WHERE expenditure_item_id = p_expenditure_item_id(i));
select decode(p_sys_linkage_function(i),'BTC',6,2)
into l_sl_function
from dual;
SELECT DECODE(nvl(COST_RATE_FLAG,'N'),'N',1,NULL)
INTO l_raw_cost_rate
FROM PA_EXPENDITURE_TYPES
where EXPENDITURE_TYPE = p_expenditure_type(i);
SELECT cost_ind_compiled_set_id
INTO l_compiled_set_id
FROM pa_expenditure_items_all
WHERE expenditure_item_id = p_expenditure_item_id(i);
select NVL(OVR_COST_IND_RATE_SCH_ID, COST_IND_RATE_SCH_ID)
into l_rate_schedule_id(i)
from pa_tasks
where task_id in
( select task_id
from pa_expenditure_items_all
where expenditure_item_id = p_expenditure_item_id(i)
);
select type.burden_cost_flag,type.burden_amt_display_method
from pa_projects_all proj ,pa_project_types_all type
where proj.project_id = p_project_id
and proj.project_type = type.project_type
and proj.org_id = type.org_id; /** Added this condition while making changes for Org Forecasting **/
c_start_date pa_project_assignments.start_date%TYPE) IS SELECT
FI.forecast_item_id,
FI.forecast_item_type,
FI.EXPENDITURE_ORG_ID,
FI.EXPENDITURE_ORGANIZATION_ID,
FI.PROJECT_ORG_ID,
FI.PROJECT_ORGANIZATION_ID,
FI.PROJECT_ID,
FI.PROJECT_TYPE_CLASS,
FI.PERSON_ID,
FI.RESOURCE_ID,
FI.ASSIGNMENT_ID,
FI.ITEM_DATE,
FI.ITEM_UOM,
FI.PVDR_PA_PERIOD_NAME,
FI.RCVR_PA_PERIOD_NAME,
FI.EXPENDITURE_TYPE,
FI.EXPENDITURE_TYPE_CLASS,
FI.Tp_Amount_Type,
FI.Delete_Flag
FROM
Pa_Forecast_Items FI
WHERE FI.Assignment_id = c_assignment_id
AND FI.Error_Flag = 'N'
AND FI.Delete_Flag = 'N'
AND FI.Item_Date = c_start_date;
SELECT
FCST.JOB_COST_RATE_SCHEDULE_ID,
EXP.Expenditure_CATEGORY,
PERIODS.End_Date
FROM
Pa_periods_all PERIODS,
Pa_forecasting_options_all Fcst,
Pa_expenditure_types Exp
WHERE
Exp.Expenditure_type = C_EXPENDITURE_TYPE
AND PERIODS.PERIOD_NAME = C_PVDR_PA_PERIOD_NAME
AND PERIODS.ORG_ID = C_EXPENDITURE_ORG_ID
AND FCST.ORG_ID = C_PROJECT_ORG_ID;
SELECT Project_Type,
DISTRIBUTION_RULE,
BILL_JOB_GROUP_ID,
COST_JOB_GROUP_ID,
JOB_BILL_RATE_SCHEDULE_ID,
EMP_BILL_RATE_SCHEDULE_ID,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_DATE,
PROJECT_RATE_TYPE,
PROJECT_BIL_RATE_DATE_CODE,
PROJECT_BIL_RATE_TYPE,
PROJECT_BIL_RATE_DATE,
PROJECT_BIL_EXCHANGE_RATE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_COST_RATE_DATE,
PROJFUNC_BIL_RATE_DATE_CODE,
PROJFUNC_BIL_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE,
PROJFUNC_BIL_EXCHANGE_RATE,
LABOR_TP_SCHEDULE_ID,
LABOR_TP_FIXED_DATE,
LABOR_SCHEDULE_DISCOUNT,
NVL(ASSIGN_PRECEDES_TASK, 'N'),
LABOR_BILL_RATE_ORG_ID,
LABOR_STD_BILL_RATE_SCHDL,
LABOR_SCHEDULE_FIXED_DATE,
LABOR_SCH_TYPE
FROM Pa_Projects_All P
WHERE P.Project_Id = c_project_id;
SELECT Fcst_Job_Id,
Fcst_Job_Group_Id,
Project_Role_Id,
ASSIGNMENT_TYPE,
STATUS_CODE
FROM
PA_PROJECT_ASSIGNMENTS PA
WHERE PA.Assignment_id= c_assignment_id;
l_fi_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
l_fi_id_tab.delete;
l_fi_item_type_tab.delete;
l_fi_exp_orgid_tab.delete;
l_fi_exp_organizationid_tab.delete;
l_fi_proj_orgid_tab.delete;
l_fi_proj_organizationid_tab.delete;
l_fi_projid_tab.delete;
l_fi_proj_type_class_tab.delete;
l_fi_personid_tab.delete;
l_fi_resid_tab.delete;
l_fi_asgid_tab.delete;
l_fi_date_tab.delete;
l_fi_uom_tab.delete;
l_fi_pvdr_papd_tab.delete;
l_fi_rcvr_papd_tab.delete;
l_fi_exptype_tab.delete;
l_fi_exptypeclass_tab.delete;
l_fi_amount_type_tab.delete;
l_fi_delete_flag_tab.delete;
l_cc_taskid_tab.delete;
l_cc_expitemid_tab.delete;
l_cc_transsource_tab.delete;
l_cc_NLOrgzid_tab.delete;
l_cc_prvdreid_tab.delete;
l_cc_recvreid_tab.delete;
lx_cc_status_tab.delete;
lx_cc_type_tab.delete;
lx_cc_code_tab.delete;
lx_cc_prvdr_orgzid_tab.delete;
lx_cc_recvr_orgzid_tab.delete;
lx_cc_recvr_orgid_tab.delete;
lx_cc_prvdr_orgid_tab.delete;
l_fi_delete_flag_tab;
l_projfunc_rev_rt_dt_code_tab.delete;
l_projfunc_rev_rt_date_tab.delete;
l_projfunc_rev_rt_type_tab.delete;
l_projfunc_rev_exch_rt_tab.delete;
l_projfunc_cst_rt_date_tab.delete;
l_projfunc_cst_rt_type_tab.delete;
l_project_rev_rt_dt_code_tab.delete;
l_project_rev_rt_date_tab.delete;
l_project_rev_rt_type_tab.delete;
l_project_rev_exch_rt_tab.delete;
l_project_cst_rt_date_tab.delete;
l_project_cst_rt_type_tab.delete;
l_tp_pa_date.delete;
SELECT pa_role_job_bg_utils.get_job_id(PR.project_role_id),
--PR.DEFAULT_JOB_ID,
PJ.JOB_GROUP_ID
INTO
l_asg_fcst_job_id,
l_asg_fcst_job_group_id
FROM PA_PROJECT_ROLE_TYPES_B PR,
PER_JOBS PJ
WHERE
PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
-- PJ.JOB_ID = PR.DEFAULT_JOB_ID;
l_qty_tab.delete;
l_tp_asgid.delete;
l_tp_exp_category.delete;
l_tp_labor_nl_flag.delete;
l_tp_taskid.delete;
l_tp_scheduleid.delete;
l_prj_curr_code_tab.delete;
l_prjfunc_curr_code_tab.delete;
l_tp_rev_distributed_flag.delete;
l_tp_compute_flag.delete;
l_tp_fixed_date.delete;
l_tp_quantity_tab.delete;
l_asg_fcst_jobid_tab.delete;
l_tp_nl_resource.delete;
l_tp_nl_resource_orgzid.delete;
lx_proj_tp_rate_type.delete;
lx_proj_tp_rate_date.delete;
lx_proj_tp_exchange_rate.delete;
lx_proj_tp_amt.delete;
lx_projfunc_tp_rate_type.delete;
lx_projfunc_tp_rate_date.delete;
lx_projfunc_tp_exchange_rate.delete;
lx_projfunc_tp_amt.delete;
lx_denom_tp_currcode.delete;
lx_denom_tp_amt.delete;
lx_expfunc_tp_rate_type.delete;
lx_expfunc_tp_rate_date.delete;
lx_expfunc_tp_exchange_rate.delete;
lx_expfunc_tp_amt.delete;
lx_cc_markup_basecode.delete;
lx_tp_ind_compiled_setid.delete;
lx_tp_bill_rate.delete;
lx_tp_base_amount.delete;
lx_tp_bill_markup_percent.delete;
lx_tp_sch_line_percent.delete;
lx_tp_rule_percent.delete;
lx_tp_job_id.delete;
lx_tp_error_code.delete;
lx_asg_precedes_task_tab.delete; -- Added for bug 3255061