[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pa_projects_s.nextval
FROM sys.dual;
SELECT FC.Currency_Code, imp.org_id, imp.exp_start_org_id, imp.exp_org_structure_version_id
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS GB,
PA_IMPLEMENTATIONS 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;
/* SELECT r.distribution_rule
FROM pa_project_type_distributions d, pa_distribution_rules r
WHERE d.distribution_rule = r.distribution_rule
AND project_type = p_project_type
AND default_flag = 'Y';
select distribution_rule
from pa_project_type_distributions
where project_type = p_project_type
and default_flag = 'Y';
select adv_action_set_id, multi_currency_billing_flag, default_rate_type
,retn_accounting_flag, competence_match_wt, availability_match_wt, job_level_match_wt
from pa_implementations;
Select Default_Resource_List_Id,
pa_resource_list_assignments_s.nextval
from pa_project_types
where project_type = p_project_type;
pa_locations_pkg.INSERT_ROW(
p_CITY => l_city_name,
p_REGION => l_region_name,
p_COUNTRY_CODE => l_country_code,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_ROWID => x_rowid,
X_LOCATION_ID => l_location_id);
, 'INSERT' -- X_insert_update_mode
, 'SELF_SERVICE' -- X_calling_module
, null -- X_project_id
, NULL -- X_task_id
/*, p_organization_id -- X_old_value --no change Commented for bug 2981386 */
, NULL /* Added for bug 2981386 */
, p_organization_id -- X_new_value
, p_project_type -- X_project_type
, null --
, null
, l_public_sector_flag -- X_public_sector_flag
, NULL -- X_task_manager_person_id
, NULL -- X_service_type
, NULL -- X_task_start_date
, NULL -- X_task_end_date
, FND_GLOBAL.USER_ID -- X_entered_by_user_id
, null -- 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_pm_project_code
, null -- X_pm_project_reference
, NULL -- X_pm_task_reference
, 'Y' -- X_functional_security_flag
, l_warnings_only_flag --bug3134205
, x_err_code -- X_err_code
, x_err_stage -- X_err_stage
, x_err_stack); -- X_err_stack
select meaning
into t_project_type_class_code
from pa_project_types pt ,pa_lookups lps
where pt.project_type = p_project_type
and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
and lps.lookup_code(+) = pt.project_type_class_code;
PA_PROJECTS_PKG.INSERT_ROW(
X_Rowid => l_row_id
,X_Project_Id => l_template_id
,X_Name => p_project_name
,X_Segment1 => p_project_number
,X_Last_Update_Date => SYSDATE
,X_Last_Updated_By => FND_GLOBAL.USER_ID
,X_Creation_Date => SYSDATE
,X_Created_By => FND_GLOBAL.USER_ID
,X_Last_Update_Login => FND_GLOBAL.LOGIN_ID
,X_Project_Type => p_project_type
,X_Carrying_Out_Organization_Id => p_organization_id
,X_Public_Sector_Flag => NVL( l_public_sector_flag, 'N' )
,X_Project_Status_Code => l_Status_code
,X_Description => p_description
,X_Start_Date => null
,X_Completion_Date => null
,X_Closed_Date => null
,X_Distribution_Rule => l_distribution_rule
,X_Labor_Invoice_Format_Id => l_labor_invoice_format_id
,X_NL_Invoice_Format_Id => l_non_labor_invoice_format_id
,X_Retention_Invoice_Format_Id => null
,X_Retention_Percentage => null
,X_Billing_Offset => l_billing_offset
,X_Billing_Cycle_Id => l_billing_cycle_id
,X_Labor_Std_Bill_Rate_Schdl => l_labor_std_bill_rate_schdl
,X_Labor_Bill_Rate_Org_Id => l_labor_bill_rate_org_id
,X_Labor_Schedule_Fixed_Date => null
,X_Labor_Schedule_Discount => null
,X_NL_Std_Bill_Rate_Schdl => l_nl_std_bill_rate_schdl
,X_NL_Bill_Rate_Org_Id => l_non_labor_bill_rate_org_id
,X_NL_Schedule_Fixed_Date => null
,X_NL_Schedule_Discount => null
,X_Limit_To_Txn_Controls_Flag => 'N'
,X_Project_Level_Funding_Flag => null --as in forms
,X_Invoice_Comment => null
,X_Unbilled_Receivable_Dr => null
,X_Unearned_Revenue_Cr => null
,X_Summary_Flag => 'N'
,X_Enabled_Flag => 'Y'
,X_Segment2 => null
,X_Segment3 => null
,X_Segment4 => null
,X_Segment5 => null
,X_Segment6 => null
,X_Segment7 => null
,X_Segment8 => null
,X_Segment9 => null
,X_Segment10 => null
,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_Cost_Ind_Rate_Sch_Id => l_cost_ind_rate_sch_id
,X_Rev_Ind_Rate_Sch_Id => l_rev_ind_rate_sch_id
,X_Inv_Ind_Rate_Sch_Id => l_inv_ind_rate_sch_id
,X_Cost_Ind_Sch_Fixed_Date => null
,X_Rev_Ind_Sch_Fixed_Date => null
,X_Inv_Ind_Sch_Fixed_Date => null
,X_Labor_Sch_Type => l_labor_sch_type
,X_Non_Labor_Sch_Type => l_non_labor_sch_type
,X_Template_Flag => 'Y'
,X_Verification_Date => null
,X_Created_From_Project_Id => null --l_template_id
,X_Template_Start_Date => l_effective_from_date
,X_Template_End_Date => l_effective_to_date
,X_Project_Currency_Code => l_cur_currency.currency_code
,X_Allow_Cross_Charge_Flag => 'N'
,X_Project_Rate_Date => null
,X_Project_Rate_Type => l_rate_type2
,X_Output_Tax_Code => null
,X_Retention_Tax_Code => null
,X_CC_Process_Labor_Flag => 'N'
,X_Labor_Tp_Schedule_Id => null
,X_Labor_Tp_Fixed_Date => null
,X_CC_Process_NL_Flag => 'N'
,X_Nl_Tp_Schedule_Id => null
,X_Nl_Tp_Fixed_Date => null
,X_CC_Tax_Task_Id => null
,x_bill_job_group_id => l_bill_job_group_id
,x_cost_job_group_id => l_cost_job_group_id
,x_role_list_id => l_role_list_id
,x_work_type_id => l_work_type_id
,x_calendar_id => l_calendar_id
,x_location_id => l_location_id
,x_probability_member_id => null
,x_project_value => null
,x_expected_approval_date => null
,x_team_template_id => null
,x_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id
,x_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id
,x_competence_match_wt => l_competence_match_wt
,x_availability_match_wt => l_availability_match_wt
,x_job_level_match_wt => l_job_level_match_wt
,x_enable_automated_search => 'N'
,x_search_min_availability => 100
,x_search_org_hier_id => l_cur_currency.exp_org_structure_version_id
,x_search_starting_org_id => l_cur_currency.exp_start_org_id
,x_search_country_code => null
,x_min_cand_score_reqd_for_nom => 100
,x_non_lab_std_bill_rt_sch_id => l_non_lab_std_bill_rt_sch_id
,x_invproc_currency_type => 'PROJFUNC_CURRENCY'
,x_revproc_currency_code => l_cur_currency.currency_code
,x_project_bil_rate_date_code => l_rate_date_code
,x_project_bil_rate_type => l_rate_type
,x_project_bil_rate_date => null
,x_project_bil_exchange_rate => null
,x_projfunc_currency_code => l_cur_currency.currency_code
,x_projfunc_bil_rate_date_code => l_rate_date_code
,x_projfunc_bil_rate_type => l_rate_type
,x_projfunc_bil_rate_date => null
,x_projfunc_bil_exchange_rate => null
,x_funding_rate_date_code => l_rate_date_code
,x_funding_rate_type => l_rate_type
,x_funding_rate_date => null
,x_funding_exchange_rate => null
,x_baseline_funding_flag => l_baseline_funding_flag
,x_projfunc_cost_rate_type => l_rate_type2
,x_projfunc_cost_rate_date => null
,x_multi_currency_billing_flag => l_mcb_flag
,x_inv_by_bill_trans_curr_flag => 'N'
,x_assign_precedes_task => 'N'
,x_split_cost_from_wokplan_flag => 'Y' --Default the workplan str is split from costing
,x_split_cost_from_bill_flag => 'N'
,x_adv_action_set_id => l_adv_action_set_id
,x_start_adv_action_set_flag => 'Y'
,x_priority_code => null
,x_retn_billing_inv_format_id => null
,x_retn_accounting_flag => l_retn_accounting_flag
-- anlee
-- patchset K changes
,x_revaluate_funding_flag => l_revaluate_funding_flag
,x_include_gains_losses_flag => l_include_gains_losses_flag
-- msundare
, x_security_level => p_security_level
, x_labor_disc_reason_code => null
, x_non_labor_disc_reason_code => null
-- End of changes
-- anlee
-- Project Long Name changes
, x_long_name => p_long_name
-- End of changes
--PA L changes 2872708
,x_asset_allocation_method => l_asset_allocation_method
,x_capital_event_processing => l_capital_event_processing
,x_cint_rate_sch_id => l_cint_rate_sch_id
,x_cint_eligible_flag => 'Y'
,x_cint_stop_date => null
--End PA L changes 2872708
,x_record_version_number => 1
, X_BTC_COST_BASE_REV_CODE => l_BTC_COST_BASE_REV_CODE --bug 2755727
--FP_M Changes. Tracking Bug 3279981
, x_revtrans_currency_type => null -- 4363092 for MOAC changes
,x_en_top_task_customer_flag => 'N'
,x_en_top_task_inv_mth_flag => 'N'
,x_revenue_accrual_method =>
substr(l_distribution_rule, 1, instr(l_distribution_rule,'/')-1)
,x_invoice_method =>
substr(l_distribution_rule, instr(l_distribution_rule,'/')+1)
,x_projfunc_attr_for_ar_flag => 'N'
,x_sys_program_flag => 'N'
,x_allow_multi_program_rollup => 'N'
,x_proj_req_res_format_id =>NULL
,x_proj_asgmt_res_format_id =>NULL
,x_org_id => p_operating_unit_id -- 4363092 MOAC changes
,x_date_eff_funds_flag =>nvl(l_date_eff_funds_flag,'N') --sunkalya:federal changes Bug#5511353
,x_ar_rec_notify_flag => l_ar_rec_notify_flag -- 7508661 : EnC
,x_auto_release_pwp_inv => l_auto_release_pwp_inv -- 7508661 : EnC
);
SELECT option_code
FROM pa_options;
,p_action => 'INSERT'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT lookup_code, meaning
FROM pa_lookups
WHERE lookup_type = 'OVERRIDE FIELD'
AND lookup_code = 'SEGMENT1'
UNION
SELECT lookup_code, meaning
FROM pa_lookups
WHERE lookup_type = 'OVERRIDE FIELD'
AND lookup_code = 'NAME'
ORDER BY 1 DESC;
INSERT INTO pa_resource_list_assignments (
resource_list_assignment_id,
resource_list_id,
project_id,
resource_list_changed_flag,
resource_list_accumulated_flag,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login )
Values (
x_rl_asgmt_id,
x_def_res_list_id,
l_template_id,
'N',
'N',
nvl(x_user_id,-1),
trunc(sysdate),
trunc(sysdate),
nvl(x_user_id,-1),
nvl(x_login_id,-1));
Insert into pa_resource_list_uses (
resource_list_assignment_id,
use_code,
default_flag,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login )
values (
x_rl_asgmt_id,
x_def_use_code,
x_def_flag,
nvl(x_user_id,-1),
trunc(sysdate),
trunc(sysdate),
nvl(x_user_id,-1),
nvl(x_login_id ,-1));
PA_TASKS_PKG.Insert_Row(
X_Rowid => l_row_id,
X_task_id => l_task_id,
X_Project_id => l_template_id,
X_Task_Number => '1',
X_Creation_Date => sysdate,
X_Created_By => FND_GLOBAL.USER_ID,
X_Last_Update_Date => sysdate,
X_last_Updated_By => FND_GLOBAL.USER_ID,
X_Last_Update_login => FND_GLOBAL.LOGIN_ID,
X_Task_Name => 'Task 1',
X_Long_Task_Name => 'Task 1',
X_Top_Task_Id => null,
X_Wbs_level => 1,
X_ready_to_Bill_flag => 'Y',
X_Ready_To_Distribute_Flag => 'Y',
X_parent_task_id => null,
X_Description => 'Task 1',
X_carrying_out_organization_id => p_organization_id,
X_Service_Type_code => l_service_type_code,
X_Task_Manager_Person_id => null,
X_chargeable_Flag => 'Y',
X_Billable_flag => l_billable_flag,
X_limit_to_Txn_controls_flag => 'N',
X_Start_Date => null,
X_Completion_Date => null,
X_Address_Id => null, --w_address_id, -Since no customer is created so far
X_Labor_Bill_Rate_org_id => l_labor_bill_rate_org_id, -- :project_folder.Labor_Bill_Rate_Org_Id,
X_Labor_Std_Bill_Rate_Schdl => l_labor_std_bill_rate_schdl, --:project_folder.Labor_Std_Bill_Rate_Schdl,
X_Labor_Schedule_Fixed_Date => null,
X_Labor_Schedule_Discount => null,
X_Non_Labor_Bill_Rate_Org_Id => l_non_labor_bill_rate_org_id, --:project_folder.Non_Labor_Bill_Rate_Org_Id,
X_NL_Std_Bill_Rate_Schdl => l_nl_std_bill_rate_schdl, --:project_folder.Non_Labor_Std_Bill_Rate_Schdl,
X_Nl_Schedule_Fixed_Date => null,
X_Non_Labor_Schedule_Discount => null,
X_Labor_Cost_Multiplier_Name => null,
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_Cost_Ind_Rate_Sch_Id => l_Cost_Ind_Rate_Sch_Id,
X_Rev_ind_rate_sch_id => l_Rev_Ind_Rate_Sch_Id,
X_Inv_Ind_rate_sch_id => l_Inv_Ind_Rate_Sch_Id,
X_Cost_ind_sch_fixed_date => null,
X_Rev_Ind_sch_fixed_date => null,
X_Inv_Ind_sch_fixed_date => null,
X_Labor_Sch_Type => l_Labor_Sch_Type,
X_Non_Labor_Sch_Type => l_Non_Labor_Sch_Type,
X_Allow_Cross_Charge_Flag => 'N',
X_Project_Rate_Date => null,
X_Project_Rate_Type => l_rate_type2,
X_cc_process_labor_flag => 'N',
X_Labor_tp_schedule_id => null,
X_Labor_tp_fixed_date => null,
X_cc_process_nl_flag => 'N',
X_nl_tp_schedule_id => null,
X_nl_tp_fixed_date => null,
X_receive_project_invoice_flag => 'N',
X_work_type_id => l_work_type_id,
X_TASKFUNC_COST_RATE_TYPE => l_rate_type2,
X_TASKFUNC_COST_RATE_DATE => null,
X_NON_LAB_STD_BILL_RT_SCH_ID => l_non_lab_std_bill_rt_sch_id,
X_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id,
X_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id,
X_labor_disc_reason_code => null,
X_non_labor_disc_reason_code => null,
--PA L 2872708
x_retirement_cost_flag => 'N',
x_cint_eligible_flag => 'Y',
x_cint_stop_date => null
--PA L 2872708
);
PROCEDURE Update_Project_Template(
p_api_version IN NUMBER :=1.0,
p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
p_commit IN VARCHAR2 :=FND_API.G_FALSE,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_project_id IN NUMBER,
p_project_number IN VARCHAR2 := 'JUNK_CHARS',
p_project_name IN VARCHAR2 := 'JUNK_CHARS',
p_project_type IN VARCHAR2 := 'JUNK_CHARS',
p_organization_id IN NUMBER := -9999,
p_effective_from_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
p_effective_to_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
p_description IN VARCHAR2 := 'JUNK_CHARS',
p_security_level IN NUMBER := 0,
-- anlee
-- Project Long Name changes
p_long_name IN VARCHAR2 DEFAULT NULL,
-- End of changes
p_record_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'Update_Project_Template';
SELECT rowid, project_type, carrying_out_organization_id, public_sector_flag, segment1, location_id,
Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date, distribution_rule,
--bug 3068781
multi_currency_billing_flag,projfunc_currency_code,
PROJFUNC_BIL_RATE_TYPE, PROJECT_BIL_RATE_TYPE, FUNDING_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE_CODE, PROJECT_BIL_RATE_DATE_CODE, FUNDING_RATE_DATE_CODE,
BTC_COST_BASE_REV_CODE
--bug 3068781
--bug 4308335
,cc_process_labor_flag
,cc_process_nl_flag
,labor_tp_schedule_id
,nl_tp_schedule_id
,labor_tp_fixed_date
,nl_tp_fixed_date
--bug 4308335
,nvl(date_eff_funds_consumption,'N') --federal bug#5511353
,enable_top_task_customer_flag --federal bug#5511353
,ar_rec_notify_flag -- 7508661 : EnC
,auto_release_pwp_inv -- 7508661 : EnC
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date
FROM pa_tasks
WHERE project_id = p_project_id;
SELECT project_type_class_code
FROM pa_project_types_all
WHERE project_type = p_project_type;
SELECT billable_capitalizable_flag
FROM pa_work_types_vl
WHERE work_type_id = c_work_type_id;
/* SELECT r.distribution_rule
FROM pa_project_type_distributions d, pa_distribution_rules r
WHERE d.distribution_rule = r.distribution_rule
AND project_type = p_project_type
AND default_flag = 'Y';
select distribution_rule
from pa_project_type_distributions
where project_type = p_project_type
and default_flag = 'Y';
pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template');
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template begin');
savepoint Update_Project_Template;
DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
WHERE PROJECT_ID = P_PROJECT_ID;
/* The following validation is not performed in forms during a project/template update
So we dont need to do this here
IF p_organization_id <> l_old_organization_id
THEN
--Organization Location Validations
pa_location_utils.Get_ORG_Location_Details
(p_organization_id => p_organization_id,
x_country_name => l_country_name,
x_city => l_city_name,
x_region => l_region_name,
x_country_code => l_country_code,
x_return_status => l_return_status,
x_error_message_code => l_error_message_code);
pa_locations_pkg.INSERT_ROW(
p_CITY => l_city_name,
p_REGION => l_region_name,
p_COUNTRY_CODE => l_country_code,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_ROWID => x_rowid,
X_LOCATION_ID => l_location_id);
, 'INSERT' -- X_insert_update_mode
, 'SELF_SERVICE' -- X_calling_module
, p_project_id -- X_project_id
, NULL -- X_task_id
, l_old_organization_id -- X_old_value
, p_organization_id -- X_new_value
, p_project_type -- X_project_type
, null -- x_start_date
, null
, l_public_sector_flag -- X_public_sector_flag
, NULL -- X_task_manager_person_id
, NULL -- X_service_type
, NULL -- X_task_start_date
, NULL -- X_task_end_date
, FND_GLOBAL.USER_ID -- X_entered_by_user_id
, null -- 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_pm_project_code
, null -- X_pm_project_reference
, NULL -- X_pm_task_reference
, 'Y' -- X_functional_security_flag
, l_warnings_only_flag --bug3134205
, x_err_code -- X_err_code
, x_err_stage -- X_err_stage
, x_err_stack); -- X_err_stack
select meaning
into l_project_type_class_code
from pa_project_types pt
, pa_lookups lps
where pt.project_type = p_project_type
and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
and lps.lookup_code(+) = pt.project_type_class_code;
CURSOR cur_job_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_job_bill_rate_schedule_id ;
CURSOR cur_emp_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_emp_bill_rate_schedule_id ;
CURSOR cur_nl_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_non_lab_std_bill_rt_sch_id ;
CURSOR cur_impl IS SELECT default_rate_type FROM pa_implementations;
UPDATE pa_project_customers
SET inv_rate_type = x_default_rate_type
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET SEGMENT1 = p_project_number,
NAME = p_project_name,
description = p_description,
PROJECT_TYPE = p_project_type,
carrying_out_organization_id = p_organization_id,
TEMPLATE_START_DATE_ACTIVE = l_effective_from_date,
TEMPLATE_END_DATE_ACTIVE = l_effective_to_date,
-- not done in project forms PROJECT_STATUS_CODE = l_Status_code,
LABOR_INVOICE_FORMAT_ID = l_labor_invoice_format_id,
NON_LABOR_INVOICE_FORMAT_ID = l_non_labor_invoice_format_id,
BILLING_OFFSET = l_billing_offset,
NON_LABOR_STD_BILL_RATE_SCHDL = l_nl_std_bill_rate_schdl,
NON_LABOR_BILL_RATE_ORG_ID = l_non_labor_bill_rate_org_id,
Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
COST_IND_RATE_SCH_ID = l_cost_ind_rate_sch_id,
REV_IND_RATE_SCH_ID = l_rev_ind_rate_sch_id,
REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date1,
INV_IND_RATE_SCH_ID = l_inv_ind_rate_sch_id,
INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date1,
LABOR_SCH_TYPE = l_labor_sch_type,
NON_LABOR_SCH_TYPE = l_non_labor_sch_type,
BILLING_CYCLE_ID = l_billing_cycle_id,
BILL_JOB_GROUP_ID = l_bill_job_group_id,
COST_JOB_GROUP_ID = l_cost_job_group_id,
ROLE_LIST_ID = l_role_list_id,
WORK_TYPE_ID = l_work_type_id,
JOB_BILL_RATE_SCHEDULE_ID = l_job_bill_rate_schedule_id,
EMP_BILL_RATE_SCHEDULE_ID = l_emp_bill_rate_schedule_id,
labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id,
labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
labor_bill_rate_org_id = l_labor_bill_rate_org_id,
cc_process_labor_flag = l_cc_process_labor_flag,
cc_process_nl_flag = l_cc_process_nl_flag,
labor_tp_schedule_id = l_labor_tp_schedule_id ,
nl_tp_schedule_id = l_nl_tp_schedule_id ,
labor_tp_fixed_date = l_labor_tp_fixed_date ,
nl_tp_fixed_date = l_nl_tp_fixed_date ,
location_id = l_location_id,
distribution_rule = l_distribution_rule,
-- anlee
-- patchset K changes
revaluate_funding_flag = l_revaluate_funding_flag,
include_gains_losses_flag = l_include_gains_losses_flag,
-- End of changes
--PA K Project Access Changes
security_level = p_security_level,
-- anlee
-- Project Long Name changes
long_name = p_long_name,
-- End of changes
--bug 3068781
multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,l_multi_currency_billing_flag2 ),
PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
--bug 3068781
--
--PA L 2872708
asset_allocation_method = l_asset_allocation_method,
CAPITAL_EVENT_PROCESSING = l_CAPITAL_EVENT_PROCESSING,
CINT_RATE_SCH_ID = l_CINT_RATE_SCH_ID,
--PA L 2872708
record_version_number = NVL( record_version_number, 1 ) + 1,
--sunkalya.federal changes. Bug#5511353
date_eff_funds_consumption = nvl(l_date_eff_funds_consumption,'N')
--sunkalya.federal changes. Bug#5511353
WHERE project_id = p_project_id;
UPDATE pa_project_customers
SET
CUSTOMER_BILL_SPLIT = NULL
WHERE
PROJECT_ID = p_project_id;
UPDATE pa_project_customers SET customer_bill_split = 100
WHERE customer_id = hghst_ctr_cust_id AND project_id = p_project_id;
UPDATE pa_project_customers SET customer_bill_split = 0
WHERE customer_id <> hghst_ctr_cust_id AND project_id = p_project_id;
UPDATE pa_tasks
SET work_type_id = l_work_type_id,
billable_flag = l_billable_flag,
emp_bill_rate_schedule_id = l_emp_bill_rate_schedule_id,
job_bill_rate_schedule_id = l_job_bill_rate_schedule_id,
labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
--bug 2101726
labor_sch_type = l_labor_sch_type,
service_type_code = l_service_type_code,
cost_ind_rate_sch_id = l_cost_ind_rate_sch_id,
non_labor_sch_type = l_non_labor_sch_type,
non_labor_bill_rate_org_id = l_non_labor_bill_rate_org_id,
non_labor_std_bill_rate_schdl = l_nl_std_bill_rate_schdl,
Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
rev_ind_rate_sch_id = l_rev_ind_rate_sch_id,
REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date2,
INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date2,
inv_ind_rate_sch_id = l_inv_ind_rate_sch_id,
labor_bill_rate_org_id = l_labor_bill_rate_org_id,
labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id
WHERE project_id = p_project_id;
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template END');
rollback to Update_Project_Template;
rollback to Update_Project_Template;
p_procedure_name => 'Update_Project_Template',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Update_Project_Template;
p_procedure_name => 'Update_Project_Template',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Project_Template;
PROCEDURE Delete_Project_Template(
p_api_version IN NUMBER :=1.0,
p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
p_commit IN VARCHAR2 :=FND_API.G_FALSE,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_project_id IN NUMBER,
p_record_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Template';
pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template');
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template begin');
savepoint Delete_Project_Template;
Pa_Project_Core.Delete_Project (
x_project_id => p_Project_id,
x_err_code => l_err_code ,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template END');
rollback to Delete_Project_Template;
rollback to Delete_Project_Template;
p_procedure_name => 'Delete_Project_Template',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Delete_Project_Template;
p_procedure_name => 'Delete_Project_Template',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Project_Template;
SELECT 'x'
FROM pa_project_options
WHERE project_id = p_project_id
AND option_code = p_option_code;
INSERT INTO pa_project_options
(
project_id,
option_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
record_version_number
)
VALUES ( p_project_id,
p_option_code,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID ,
1
);
PROCEDURE Delete_Project_Options(
p_api_version IN NUMBER :=1.0,
p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
p_commit IN VARCHAR2 :=FND_API.G_FALSE,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_project_id IN NUMBER,
p_option_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Options';
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'PA_OPTIONS_SS'
AND lookup_code = p_option_code
AND language = userenv('LANG'); -- Bug 5643345: Added the environment language condition.
SELECT allow_override_enabled_flag
FROM PA_OPTIONS
WHERE option_code = p_option_code;
pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options');
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options begin');
savepoint Delete_Project_Options;
p_msg_name => 'PA_ALL_NO_UPDATE_RECORD');*/
DELETE FROM pa_project_options WHERE project_id = p_project_id AND option_code = p_option_code;
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options END');
rollback to Delete_Project_Options;
rollback to Delete_Project_Options;
p_procedure_name => 'Delete_Project_Options',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Delete_Project_Options;
p_procedure_name => 'Delete_Project_Options',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Project_Options;
SELECT 'x'
FROM pa_project_copy_overrides
WHERE project_id = p_project_id
AND field_name = p_field_name
AND sort_order = p_sort_order;
SELECT 'x'
FROM pa_project_copy_overrides
WHERE project_id = p_project_id
AND sort_order = p_sort_order;
SELECT 'X'
FROM pa_project_copy_overrides
WHERE project_id = p_project_id
AND field_name = p_field_name;
SELECT meaning
INTO l_field_name_meaning
FROM fnd_lookup_values
WHERE lookup_type = 'OVERRIDE FIELD'
AND lookup_code = p_field_name;
SELECT 'x'
INTO l_dummy_char
FROM PA_QUICK_ENTRY_SPECS_SS_V
WHERE FIELD_NAME = p_field_name
AND LIMITING_VALUE = p_limiting_value;
SELECT project_type_id
FROM pa_project_types ppt, pa_projects pp
WHERE project_id = p_project_id
AND pp.project_type = ppt.project_type;
SELECT 'X'
INTO l_dummy_char
FROM pa_valid_categories_v pvc ,
pa_lookups pl
WHERE pvc.object_type = 'PA_PROJECTS'
AND pvc.object_type_id = l_project_type_id
AND 'CLASSIFICATION' = pl.lookup_code
AND pl.lookup_type = 'OVERRIDE FIELD'
AND pvc.class_category = p_limiting_value ;
INSERT INTO pa_project_copy_overrides
( PROJECT_ID ,
FIELD_NAME ,
DISPLAY_NAME ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
LIMITING_VALUE ,
SORT_ORDER ,
MANDATORY_FLAG ,
RECORD_VERSION_NUMBER
)
VALUES ( p_project_id ,
p_field_name ,
p_prompt ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.LOGIN_ID ,
p_limiting_value ,
p_sort_order ,
p_required_flag ,
1
);
PROCEDURE Update_Quick_Entry_Field(
p_api_version IN NUMBER :=1.0,
p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
p_commit IN VARCHAR2 :=FND_API.G_FALSE,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_project_id IN NUMBER ,
p_row_id IN VARCHAR2,
p_sort_order IN NUMBER ,
p_field_name IN VARCHAR2 := 'JUNK_CHARS',
p_limiting_value IN VARCHAR2 := 'JUNK_CHARS',
p_prompt IN VARCHAR2 ,
p_required_flag IN VARCHAR2 := 'N',
p_record_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'Update_Quick_Entry_Field';
SELECT 'x'
FROM pa_project_copy_overrides
WHERE rowid <> p_row_id
AND project_id = p_project_id
AND sort_order = p_sort_order;
SELECT 'X'
FROM pa_project_copy_overrides
WHERE rowid <> p_row_id
AND project_id = p_project_id
AND field_name = p_field_name;
pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field');
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field begin');
savepoint Update_Quick_Entry_Field;
SELECT meaning
INTO l_field_name_meaning
FROM fnd_lookup_values
WHERE lookup_type = 'OVERRIDE FIELD'
AND lookup_code = p_field_name;
SELECT 'x'
INTO l_dummy_char
FROM PA_QUICK_ENTRY_SPECS_SS_V
WHERE FIELD_NAME = p_field_name
AND LIMITING_VALUE = p_limiting_value;
SELECT project_type_id
FROM pa_project_types ppt, pa_projects pp
WHERE project_id = p_project_id
AND pp.project_type = ppt.project_type;
SELECT 'X'
INTO l_dummy_char
FROM pa_valid_categories_v pvc ,
pa_lookups pl
WHERE pvc.object_type = 'PA_PROJECTS'
AND pvc.object_type_id = l_project_type_id
AND 'CLASSIFICATION' = pl.lookup_code
AND pl.lookup_type = 'OVERRIDE FIELD'
AND pvc.class_category = p_limiting_value ;
UPDATE pa_project_copy_overrides
SET FIELD_NAME = p_field_name ,
DISPLAY_NAME = p_prompt ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID ,
LIMITING_VALUE = p_limiting_value ,
SORT_ORDER = p_sort_order ,
MANDATORY_FLAG = p_required_flag ,
RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 1 ) + 1
WHERE rowid = p_row_id;
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field END');
rollback to Update_Quick_Entry_Field;
rollback to Update_Quick_Entry_Field;
p_procedure_name => 'Update_Quick_Entry_Field',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Update_Quick_Entry_Field;
p_procedure_name => 'Update_Quick_Entry_Field',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Quick_Entry_Field;
PROCEDURE Delete_Quick_Entry_Field(
p_api_version IN NUMBER :=1.0,
p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
p_commit IN VARCHAR2 :=FND_API.G_FALSE,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
p_calling_module IN VARCHAR2 :='SELF_SERVICE',
p_debug_mode IN VARCHAR2 :='N',
p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_project_id IN NUMBER ,
p_row_id IN VARCHAR2,
p_record_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'Delete_Quick_Entry_Field';
SELECT field_name
FROM pa_project_copy_overrides
WHERE rowid = p_row_id;
pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field');
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field begin');
savepoint Delete_Quick_Entry_Field;
DELETE FROM pa_project_copy_overrides
WHERE rowid = p_row_id;
pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field END');
rollback to Delete_Quick_Entry_Field;
rollback to Delete_Quick_Entry_Field;
p_procedure_name => 'Delete_Quick_Entry_Field',
p_error_text => SUBSTRB(SQLERRM,1,240));
rollback to Delete_Quick_Entry_Field;
p_procedure_name => 'Delete_Quick_Entry_Field',
p_error_text => SUBSTRB(SQLERRM,1,240));
END Delete_Quick_Entry_Field;
SELECT lk1.lookup_code forms_opt_code, lk2.lookup_code ss_opt_code, lk1.meaning
FROM fnd_lookup_values lk1, fnd_lookup_values lk2
WHERE lk1.lookup_type = 'PA_OPTIONS'
AND lk2.lookup_type = 'PA_OPTIONS_SS'
AND lk1.meaning = lk2.meaning
;
SELECT 'x'
FROM pa_project_options
WHERE project_id = p_project_id
AND option_code = c_option_code;
INSERT INTO pa_project_options
(
project_id,
option_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
record_version_number
)
VALUES ( p_project_id,
l_option_code,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID ,
1
);
DELETE FROM pa_project_options WHERE project_id = p_project_id and option_code = l_option_code ;
END IF; --<< l_insert_flag >>