[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2
,x_CI_TRANSACTION_ID IN OUT NOCOPY NUMBER
,p_CI_TYPE_ID IN NUMBER
,p_CI_ID IN NUMBER
,p_CI_IMPACT_ID IN NUMBER
,p_VENDOR_ID IN NUMBER
,p_PO_HEADER_ID IN NUMBER
,p_PO_LINE_ID IN NUMBER
,p_ADJUSTED_TRANSACTION_ID IN NUMBER
,p_CURRENCY_CODE IN VARCHAR2
,p_CHANGE_AMOUNT IN NUMBER
,p_CHANGE_TYPE IN VARCHAR2
,p_CHANGE_DESCRIPTION IN VARCHAR2
,p_CREATED_BY IN NUMBER
,p_CREATION_DATE IN DATE
,p_LAST_UPDATED_BY IN NUMBER
,p_LAST_UPDATE_DATE IN DATE
,p_LAST_UPDATE_LOGIN IN NUMBER
,p_Task_Id IN NUMBER
,p_Resource_List_Mem_Id IN NUMBER
,p_From_Date IN varchar2
,p_To_Date IN varchar2
,p_Estimated_Cost IN NUMBER
,p_Quoted_Cost IN NUMBER
,p_Negotiated_Cost IN NUMBER
,p_Burdened_cost IN NUMBER
,p_Revenue IN NUMBER default NULl
,p_revenue_override_rate in number
,p_audit_history_number in number
,p_current_audit_flag in varchar2
,p_Original_supp_trans_id in number
,p_Source_supp_trans_id in number
,p_Sup_ref_no in number default null
,p_version_type in varchar2 default 'ALL'
,p_ci_status IN VARCHAR2
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type in varchar2 default null
,p_expenditure_org_id in number default null
,p_change_reason_code in varchar2 default null
,p_quote_negotiation_reference in varchar2 default null
,p_need_by_date in varchar2 default null
-- gboomina modified for supplier cost 12.1.3 requirement - end
,x_return_status OUT NOCOPY VARCHAR2
,x_error_msg_code OUT NOCOPY VARCHAR2
)IS
cursor return_rowid is
select rowid
from pa_ci_supplier_details
where ci_transaction_id = x_CI_TRANSACTION_ID;
select pa_ci_supplier_details_s.nextval
from sys.dual;
select prac.total_projfunc_burdened_cost , prac.total_projfunc_revenue, prac.txn_average_bill_rate
from pa_resource_assignments pra, pa_resource_asgn_curr prac
where
pra.budget_version_id = bvId and pra.task_id = p_task_id and
pra.resource_list_member_id = p_Resource_List_Mem_Id and
prac.resource_assignment_id = pra.resource_assignment_id;
select ci_type_id
from pa_control_items
where ci_id = p_ci_id;
select pa_resource_assignments_s.nextval
from sys.dual;
select final_cost from pa_ci_supplier_details
where ci_transaction_id = ci_trans_id;
select budget_version_id
from pa_budget_versions
where ci_id = p_ci_id;
SELECT budget_version_id FROM pa_budget_versions
WHERE ci_id = p_ci_id and
budget_version_id in (select fin_plan_version_id from pa_proj_fp_options where project_id = proj_id and fin_plan_preference_code = 'COST_ONLY');
SELECT budget_version_id FROM pa_budget_versions
WHERE ci_id = p_ci_id and
budget_version_id in (select fin_plan_version_id from pa_proj_fp_options where project_id = proj_id and fin_plan_preference_code = 'REVENUE_ONLY');
select project_id
from pa_control_items
where ci_id = p_ci_id;
select pev.element_version_id,parent_structure_version_id
from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
where pev.project_id=proj_id and pev.project_id=pevs.project_id
and pev.proj_element_id=p_task_id and pev.parent_structure_version_id=pevs.element_version_id
and pevs.CURRENT_FLAG='Y';
select pev.element_version_id,parent_structure_version_id
from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
where pev.project_id=proj_id and pev.project_id=pevs.project_id
and pev.proj_element_id=p_task_id and pev.parent_structure_version_id=pevs.element_version_id
and pevs.CURRENT_WORKING_FLAG='Y';
select resource_assignment_id,
unit_of_measure,
project_assignment_id,
organization_id,
supplier_id,
spread_curve_id,
etc_method_code,
mfc_cost_type_id,
procure_resource_flag,
decode(use_task_schedule_flag,'Y','Y','N') as use_task_schedule_flag,
planning_start_date,
planning_end_date,
schedule_start_date,
schedule_end_date,
sp_fixed_date,
named_role
from pa_resource_assignments
where budget_version_id = bv_Id
and task_id = p_Task_Id
and resource_list_member_id = p_Resource_List_Mem_Id;
select ORGANIZATION_ID
,SPREAD_CURVE_ID
,ETC_METHOD_CODE
,RESOURCE_CLASS_CODE
,RESOURCE_CLASS_FLAG
,RECORD_VERSION_NUMBER
,INCURRED_BY_RES_FLAG
,UNIT_OF_MEASURE
,RESOURCE_TYPE_CODE
from pa_resource_list_members where RESOURCE_LIST_MEMBER_ID = p_Resource_List_Mem_Id;
SELECT
ppfo.use_planning_rates_flag,
ppfo.RES_CLASS_RAW_COST_SCH_ID,
ppfo.COST_RES_CLASS_RATE_SCH_ID,
ppfo.RES_CLASS_BILL_RATE_SCH_ID,
ppfo.REV_RES_CLASS_RATE_SCH_ID
FROM pa_proj_fp_options ppfo,
pa_control_items pci
WHERE pci.ci_id = p_ci_id and
pci.project_id = ppfo.project_id and
NVL(ppfo.Approved_Cost_Plan_Type_Flag ,'N') = 'Y' and
ppfo.Fin_Plan_Option_Level_Code = 'PLAN_TYPE';
select rate from PA_BILL_RATES_ALL
WHERE bill_rate_sch_id = p_rate_sch_id AND
resource_class_code = 'FINANCIAL_ELEMENTS' and
trunc(Nvl(to_date(null,'YYYY.MM.DD'),start_date_active)) between trunc(start_date_active)
and trunc(nvl(end_date_active,Nvl(to_date(null,'YYYY.MM.DD'),start_date_active)));
--defining the table type variables for insert into budget operation
l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
INSERT into pa_ci_supplier_details
( CI_TRANSACTION_ID
,CI_TYPE_ID
,CI_ID
,CI_IMPACT_ID
,VENDOR_ID
,PO_HEADER_ID
,PO_LINE_ID
,ADJUSTED_CI_TRANSACTION_ID
,CURRENCY_CODE
,CHANGE_AMOUNT
,CHANGE_TYPE
,CHANGE_DESCRIPTION
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,FROM_CHANGE_DATE
,TO_CHANGE_DATE
,ESTIMATED_COST
,QUOTED_COST
,NEGOTIATED_COST
,FINAL_COST
,RAW_COST
,BURDENED_COST
,revenue_rate
,revenue_override_rate
,revenue
,total_revenue
,CURRENT_AUDIT_FLAG
,STATUS
,audit_history_number
,original_supp_trans_id
,source_supp_trans_id
,sup_quote_ref_no
-- gboomina modified for supplier cost 12.1.3 requirement - start
,expenditure_type
,expenditure_org_id
,change_reason_code
,quote_negotiation_reference
,need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
) VALUES
( x_CI_TRANSACTION_ID
,l_CI_TYPE_ID
,p_CI_ID
,p_CI_IMPACT_ID
,p_VENDOR_ID
,p_PO_HEADER_ID
,p_PO_LINE_ID
,p_ADJUSTED_TRANSACTION_ID
,p_CURRENCY_CODE
,pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,p_CHANGE_TYPE
,NVL(p_CHANGE_DESCRIPTION,l_CHANGE_DESCRIPTION)
,p_CREATED_BY
,p_CREATION_DATE
,p_LAST_UPDATE_LOGIN
,p_LAST_UPDATE_DATE
,p_LAST_UPDATE_LOGIN
,p_Task_Id
,p_Resource_List_Mem_Id
,to_date(p_From_Date)
,to_date(p_To_Date)
,pa_currency.round_trans_currency_amt
(decode(p_Estimated_Cost,null,0,p_Estimated_Cost),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt
(decode(p_Quoted_Cost,null,0,p_Quoted_Cost),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt
(decode(p_Negotiated_Cost,null,0,p_Negotiated_Cost),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt
(decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
--,pa_currency.round_trans_currency_amt
-- (decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt(p_Burdened_cost,p_CURRENCY_CODE)
,l_revenue_rate
,p_revenue_override_rate
,pa_currency.round_trans_currency_amt
(decode(l_revenue,null,0,l_revenue),p_CURRENCY_CODE)
,pa_currency.round_trans_currency_amt
(decode(l_revenue,null,0,l_revenue),p_CURRENCY_CODE)
,'Y'
,p_version_type
,l_audit_history_number
,l_original_supp_id
,l_source_supp_id
,p_Sup_ref_no
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type
,p_expenditure_org_id
,p_change_reason_code
,p_quote_negotiation_reference
,to_date(p_need_by_date)
-- gboomina modified for supplier cost 12.1.3 requirement - end
);
update pa_ci_supplier_details set CURRENT_AUDIT_FLAG = 'N' where ci_transaction_id = l_source_supp_id;
print_msg('rowid not found raise insert failed');
print_msg('Insert success');
update pa_ci_supplier_details
set burdened_cost = l_burdened_cost,
revenue_rate = l_revenue_rate,
revenue_override_rate = '',
revenue = l_revenue,
total_revenue = l_revenue
where
ci_transaction_id = x_CI_TRANSACTION_ID;
--call update api
--p_struct_elem_version_id and p_task_elem_version_id_tbl
open get_elem_ver_id(l_project_id);
pa_fp_planning_transaction_pub.update_planning_transactions(
p_context => 'BUDGET',
p_struct_elem_version_id => l_struct_elem_version_id,
p_budget_version_id => l_bvId,
p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
p_task_name_tbl => l_task_name_tbl,
p_task_number_tbl => l_task_number_tbl,
p_start_date_tbl => l_in_start_date_tbl,
p_end_date_tbl => l_in_end_date_tbl,
p_planned_people_effort_tbl => l_planned_people_effort_tbl,
p_resource_assignment_id_tbl => l_resource_assignment_id_tbl,
p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
p_assignment_description_tbl => l_assignment_description_tbl,
p_project_assignment_id_tbl => l_project_assignment_id_tbl,
p_resource_alias_tbl => l_planning_resource_alias_tbl,
p_resource_class_flag_tbl => l_resource_class_flag_tbl,
p_resource_class_code_tbl => l_resource_class_code_tbl,
p_resource_class_id_tbl => l_resource_class_id_tbl,
p_res_type_code_tbl => l_res_type_code_tbl,
p_resource_code_tbl => l_resource_code_tbl,
p_resource_name => l_resource_name,
p_project_role_id_tbl => l_project_role_id_tbl,
p_project_role_name_tbl => l_project_role_name_tbl,
p_organization_id_tbl => l_organization_id_tbl,
p_organization_name_tbl => l_organization_name_tbl,
p_fc_res_type_code_tbl => l_fc_res_type_code_tbl,
p_financial_category_code_tbl => l_financial_category_code_tbl,
p_supplier_id_tbl => l_supplier_id_tbl,
p_unit_of_measure_tbl => l_unit_of_measure_tbl,
p_spread_curve_id_tbl => l_spread_curve_id_tbl,
p_etc_method_code_tbl => l_etc_method_code_tbl,
p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
p_procure_resource_flag_tbl => l_procure_resource_flag_tbl,
p_incur_by_resource_code_tbl => l_Incur_by_resource_code_tbl,
p_incur_by_resource_name_tbl => l_incur_by_resource_name_tbl,
p_use_task_schedule_flag_tbl => l_use_task_schedule_flag_tbl,
p_planning_start_date_tbl => l_planning_start_date_tbl,
p_planning_end_date_tbl => l_planning_end_date_tbl,
p_schedule_start_date_tbl => l_schedule_start_date_tbl,
p_schedule_end_date_tbl => l_schedule_end_date_tbl,
p_quantity_tbl => l_total_quantity_tbl,
p_currency_code_tbl => l_currency_code_tbl,
p_txn_currency_override_tbl => l_override_currency_code_tbl,
p_raw_cost_tbl => l_total_raw_cost_tbl,
p_burdened_cost_tbl => l_burdened_cost_tbl,
p_revenue_tbl => l_revenue_tbl,
p_cost_rate_tbl => l_raw_cost_rate_tbl,
p_bill_rate_tbl => l_bill_rate_tbl,
p_bill_rate_override_tbl => l_bill_rate_override_tbl,
p_billable_percent_tbl => l_billable_percent_tbl,
p_cost_rate_override_tbl => l_cost_rate_override_tbl,
p_burdened_rate_tbl => l_burdened_rate_tbl,
p_burdened_rate_override_tbl => l_burdened_rate_override_tbl,
p_sp_fixed_date_tbl => l_sp_fixed_date_tbl,
p_named_role_tbl => l_named_role_tbl,
p_financial_category_name_tbl => l_financial_category_name_tbl,
p_supplier_name_tbl => l_supplier_name_tbl,
p_attribute_category_tbl => l_attribute_category_tbl,
p_attribute1_tbl => l_attribute1_tbl,
p_attribute2_tbl => l_attribute1_tbl,
p_attribute3_tbl => l_attribute1_tbl,
p_attribute4_tbl => l_attribute1_tbl,
p_attribute5_tbl => l_attribute1_tbl,
p_attribute6_tbl => l_attribute1_tbl,
p_attribute7_tbl => l_attribute1_tbl,
p_attribute8_tbl => l_attribute1_tbl,
p_attribute9_tbl => l_attribute1_tbl,
p_attribute10_tbl => l_attribute1_tbl,
p_attribute11_tbl => l_attribute1_tbl,
p_attribute12_tbl => l_attribute1_tbl,
p_attribute13_tbl => l_attribute1_tbl,
p_attribute14_tbl => l_attribute1_tbl,
p_attribute15_tbl => l_attribute1_tbl,
p_attribute16_tbl => l_attribute1_tbl,
p_attribute17_tbl => l_attribute1_tbl,
p_attribute18_tbl => l_attribute1_tbl,
p_attribute19_tbl => l_attribute1_tbl,
p_attribute20_tbl => l_attribute1_tbl,
p_attribute21_tbl => l_attribute1_tbl,
p_attribute22_tbl => l_attribute1_tbl,
p_attribute23_tbl => l_attribute1_tbl,
p_attribute24_tbl => l_attribute1_tbl,
p_attribute25_tbl => l_attribute1_tbl,
p_attribute26_tbl => l_attribute1_tbl,
p_attribute27_tbl => l_attribute1_tbl,
p_attribute28_tbl => l_attribute1_tbl,
p_attribute29_tbl => l_attribute1_tbl,
p_attribute30_tbl => l_attribute1_tbl,
p_scheduled_delay => l_scheduled_delay,
p_distrib_amts => 'Y',
p_direct_expenditure_type_tbl => l_direct_expenditure_type_tbl,
x_return_status => l_return_status,
x_msg_count => l_error_msg_code,
x_msg_data => l_msg_count);
update pa_ci_supplier_details
set change_amount = p_CHANGE_AMOUNT,
burdened_cost = l_burdened_cost,
revenue_rate = l_revenue_rate,
revenue_override_rate = '',
revenue = l_revenue,
total_revenue = l_revenue
where
ci_transaction_id = x_CI_TRANSACTION_ID;
END insert_row;
PROCEDURE update_row
(p_rowid IN VARCHAR2
,p_ci_transaction_id IN NUMBER
,p_CI_TYPE_ID IN NUMBER
,p_CI_ID IN NUMBER
,p_CI_IMPACT_ID IN NUMBER
,p_VENDOR_ID IN NUMBER
,p_PO_HEADER_ID IN NUMBER
,p_PO_LINE_ID IN NUMBER
,p_ADJUSTED_TRANSACTION_ID IN NUMBER
,p_CURRENCY_CODE IN VARCHAR2
,p_CHANGE_AMOUNT IN NUMBER
,p_CHANGE_TYPE IN VARCHAR2
,p_CHANGE_DESCRIPTION IN VARCHAR2
,p_LAST_UPDATED_BY IN NUMBER
,p_LAST_UPDATE_DATE IN DATE
,p_LAST_UPDATE_LOGIN IN NUMBER
,p_Task_Id IN NUMBER
,p_Resource_List_Mem_Id IN NUMBER
,p_From_Date IN varchar2
,p_To_Date IN varchar2
,p_Estimated_Cost IN NUMBER
,p_Quoted_Cost IN NUMBER
,p_Negotiated_Cost IN NUMBER
,p_Burdened_cost IN NUMBER
,p_Revenue IN NUMBER default NULL
,p_revenue_override_rate in number
,p_audit_history_number in number
,p_current_audit_flag in varchar2
,p_Original_supp_trans_id in number
,p_Source_supp_trans_id in number
,p_Sup_ref_no in number default null
,p_version_type in varchar2 default 'ALL'
,p_ci_status IN VARCHAR2 default null
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type in varchar2 default null
,p_expenditure_org_id in number default null
,p_change_reason_code in varchar2 default null
,p_quote_negotiation_reference in varchar2 default null
,p_need_by_date in varchar2 default null
-- gboomina modified for supplier cost 12.1.3 requirement - end
,x_return_status OUT NOCOPY VARCHAR2
,x_error_msg_code OUT NOCOPY VARCHAR2
)IS
cursor cur_audit is
select nvl(audit_history_number,0),original_supp_trans_id
from pa_ci_supplier_details
where ci_transaction_id = p_ci_transaction_id;
select pa_resource_assignments_s.nextval
from sys.dual;
SELECT CI_TYPE_ID
,CI_ID
,CI_IMPACT_ID
,VENDOR_ID
,PO_HEADER_ID
,PO_LINE_ID
,ADJUSTED_CI_TRANSACTION_ID
,CURRENCY_CODE
,CHANGE_AMOUNT
,CHANGE_TYPE
,CHANGE_DESCRIPTION
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,FROM_CHANGE_DATE
,TO_CHANGE_DATE
,ESTIMATED_COST
,QUOTED_COST
,NEGOTIATED_COST
,Burdened_cost
,revenue
,audit_history_number
,ORIGINAL_SUPP_TRANS_ID
,SOURCE_SUPP_TRANS_ID
,SUP_QUOTE_REF_NO
,status
-- gboomina modified for supplier cost 12.1.3 requirement - start
,expenditure_type
,expenditure_org_id
,change_reason_code
,quote_negotiation_reference
,need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
FROM pa_ci_supplier_details
WHERE ci_transaction_id = p_ci_transaction_id
FOR UPDATE OF ci_transaction_id NOWAIT;
select budget_version_id
from pa_budget_versions
where ci_id = p_ci_id;
select project_id
from pa_control_items
where ci_id = p_ci_id;
select resource_assignment_id
from pa_resource_assignments
where budget_version_id = bv_Id
and task_id = p_Task_Id
and resource_list_member_id = p_Resource_List_Mem_Id;
select ORGANIZATION_ID
,SPREAD_CURVE_ID
,ETC_METHOD_CODE
,RESOURCE_CLASS_CODE
,RESOURCE_CLASS_FLAG
,RECORD_VERSION_NUMBER
,INCURRED_BY_RES_FLAG
,UNIT_OF_MEASURE
,RESOURCE_TYPE_CODE
from pa_resource_list_members where RESOURCE_LIST_MEMBER_ID = p_Resource_List_Mem_Id;
SELECT
ppfo.use_planning_rates_flag,
ppfo.RES_CLASS_RAW_COST_SCH_ID,
ppfo.COST_RES_CLASS_RATE_SCH_ID,
ppfo.RES_CLASS_BILL_RATE_SCH_ID,
ppfo.REV_RES_CLASS_RATE_SCH_ID
FROM pa_proj_fp_options ppfo,
pa_control_items pci
WHERE pci.ci_id = p_ci_id and
pci.project_id = ppfo.project_id and
NVL(ppfo.Approved_Cost_Plan_Type_Flag ,'N') = 'Y' and
ppfo.Fin_Plan_Option_Level_Code = 'PLAN_TYPE';
select markup_percentage from PA_BILL_RATES_ALL
WHERE bill_rate_sch_id = p_rate_sch_id AND
resource_class_code = 'FINANCIAL_ELEMENTS' and
trunc(to_date(p_From_Date)) between trunc(start_date_active)
and trunc(nvl(end_date_active,to_date(p_From_Date)));
print_msg('Inside update row.');
/** check if any of the attributes changed then update else donot **/
/* IF Nvl(recinfo.vendor_id,0) <> nvl(p_vendor_id,0) OR
Nvl(recinfo.po_header_id,0) <> nvl(p_po_header_id,0) OR
Nvl(recinfo.po_line_id,0) <> nvl(p_po_line_id,0) OR
Nvl(recinfo.currency_code,'X') <> nvl(p_currency_code,'X') OR
nvl(recinfo.change_type,'X') <> nvl(p_change_type,'X') OR
Nvl(recinfo.change_description,'X') <> nvl(p_change_description,'X') OR
NVL(recinfo.SUP_QUOTE_REF_NO,0) <> NVL(p_ci_status,0)
THEN
*/
If l_debug_mode = 'Y' Then
print_msg('firing update query');
UPDATE pa_ci_supplier_details SET
VENDOR_ID = p_vendor_id
,PO_HEADER_ID = p_po_header_id
,PO_LINE_ID = p_po_line_id
,ADJUSTED_CI_TRANSACTION_ID = p_adjusted_transaction_id
,CURRENCY_CODE = p_currency_code
,CHANGE_AMOUNT = pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,CHANGE_TYPE = p_change_type
,CHANGE_DESCRIPTION = p_change_description
,LAST_UPDATED_BY = p_last_updated_by
,LAST_UPDATE_DATE = p_last_update_date
,LAST_UPDATE_LOGIN = p_last_update_login
,SUP_QUOTE_REF_NO = p_Sup_ref_no
-- gboomina added for supplier cost 12.1.3 - start
,change_reason_code = p_change_reason_code
,quote_negotiation_reference = p_quote_negotiation_reference
,FROM_CHANGE_DATE = to_date(p_From_Date)
,TO_CHANGE_DATE = to_date(p_To_Date)
,expenditure_org_id = p_expenditure_org_id
,need_by_date = to_date(p_need_by_date)
-- gboomina added for supplier cost 12.1.3 - end
-- racheruv added for bug 9840053
,burdened_cost = p_burdened_cost
WHERE ci_transaction_id = p_ci_transaction_id;
print_msg('Update failure:'||x_error_msg_code);
print_msg('firing update query');
insert_row (
x_rowid => l_rowid
,x_ci_transaction_id => l_ci_transaction_id
,p_CI_TYPE_ID => p_ci_type_id
,p_CI_ID => p_CI_ID
,p_CI_IMPACT_ID => recinfo.CI_IMPACT_ID
,p_VENDOR_ID => p_vendor_id
,p_PO_HEADER_ID => p_po_header_id
,p_PO_LINE_ID => p_po_line_id
,p_ADJUSTED_TRANSACTION_ID => p_ADJUSTED_TRANSACTION_ID
,p_CURRENCY_CODE => p_CURRENCY_CODE
,p_CHANGE_AMOUNT => p_CHANGE_AMOUNT
,p_CHANGE_TYPE => p_CHANGE_TYPE
,p_CHANGE_DESCRIPTION => p_CHANGE_DESCRIPTION
,p_CREATED_BY => FND_GLOBAL.login_id
,p_CREATION_DATE => trunc(sysdate)
,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_Task_Id => p_Task_Id
,p_Resource_List_Mem_Id => p_Resource_List_Mem_Id
,p_From_Date => p_From_Date
,p_To_Date => p_To_Date
,p_Estimated_Cost => p_Estimated_Cost
,p_Quoted_Cost => p_Quoted_Cost
,p_Negotiated_Cost => p_Negotiated_Cost
,p_Burdened_cost => p_Burdened_cost
,p_Revenue => p_Revenue
,p_revenue_override_rate => p_revenue_override_rate
,p_audit_history_number => l_audit_version_number
,p_current_audit_flag => p_current_audit_flag
,p_Original_supp_trans_id => l_original_ci_trans_id
,p_Source_supp_trans_id => l_ci_transaction_id
,p_Sup_ref_no => p_Sup_ref_no
,p_version_type => recinfo.status
-- gboomina modified for supplier cost 12.1.3 requirement - start
,p_expenditure_type => p_expenditure_type
,p_expenditure_org_id => p_expenditure_org_id
,p_change_reason_code => p_change_reason_code
,p_quote_negotiation_reference => p_quote_negotiation_reference
,p_need_by_date => p_need_by_date
-- gboomina modified for supplier cost 12.1.3 requirement - end
,p_ci_status => ''
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code );
--nag_test('UPDATE Before assignment tables');
--nag_test('UPDATE NOt found');
--nag_test('UPDATE Budget version id is '||l_bvId);
print_msg('New record needs to be inserted into resource assignments and asgn curr');
INSERT INTO PA_RESOURCE_ASSIGNMENTS(
RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,PROJECT_ASSIGNMENT_ID
,TOTAL_PLAN_REVENUE
,TOTAL_PLAN_RAW_COST
,TOTAL_PLAN_QUANTITY
,RESOURCE_ASSIGNMENT_TYPE
,TOTAL_PROJECT_RAW_COST
,TOTAL_PROJECT_BURDENED_COST
,TOTAL_PROJECT_REVENUE
--RBS_ELEMENT_ID
,PLANNING_START_DATE
,PLANNING_END_DATE
,SPREAD_CURVE_ID
,ETC_METHOD_CODE
,RES_TYPE_CODE
,RESOURCE_CLASS_CODE
,ORGANIZATION_ID
,RECORD_VERSION_NUMBER
,INCURRED_BY_RES_FLAG
,RATE_BASED_FLAG
,RATE_EXP_FUNC_CURR_CODE
--,RATE_EXPENDITURE_ORG_ID
,RESOURCE_CLASS_FLAG
,RESOURCE_RATE_BASED_FLAG
)VALUES
( l_res_asgn_Id
,l_bvId
,l_project_id
,p_Task_Id
,p_Resource_List_Mem_Id
,sysdate
,1319
,sysdate
,1319
,p_LAST_UPDATE_LOGIN
,res_info.UNIT_OF_MEASURE
,'Y'
,-1
,l_revenue
,decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
,decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,'USER_ENTERED'
,decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
,decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,l_revenue
,to_date(p_From_Date,'YYYY/MM/DD')
,to_date(p_To_Date,'YYYY/MM/DD')
,res_info.SPREAD_CURVE_ID
,res_info.ETC_METHOD_CODE
,res_info.RESOURCE_TYPE_CODE
,res_info.RESOURCE_CLASS_CODE
,res_info.ORGANIZATION_ID
,res_info.RECORD_VERSION_NUMBER
,res_info.INCURRED_BY_RES_FLAG
,'N'
,p_CURRENCY_CODE
,res_info.RESOURCE_CLASS_FLAG
,'Y'
);
--nag_test('UPDATE resource assignment id '||l_res_asgn_Id);
update PA_RESOURCE_ASSIGNMENTS SET
LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
,LAST_UPDATED_BY = 1319
,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
,TOTAL_PLAN_REVENUE = l_revenue
,TOTAL_PLAN_RAW_COST = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
,TOTAL_PLAN_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,TOTAL_PLAN_QUANTITY = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
,TOTAL_PROJECT_RAW_COST = decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT)
,TOTAL_PROJECT_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,TOTAL_PROJECT_REVENUE = l_revenue
,RATE_BASED_FLAG = 'N'
,RESOURCE_RATE_BASED_FLAG = 'N'
WHERE RESOURCE_ASSIGNMENT_ID = l_res_asgn_Id;
--nag_test('Done with resource assignment insert');
UPDATE pa_resource_asgn_curr SET
TOTAL_QUANTITY = pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,TXN_AVERAGE_RAW_COST_RATE = 1
,TXN_AVERAGE_BURDEN_COST_RATE = 0.1
,TXN_AVERAGE_BILL_RATE = l_revenue_rate
,TOTAL_TXN_RAW_COST = pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,TOTAL_TXN_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,TOTAL_TXN_REVENUE = l_revenue
,TOTAL_PROJECT_RAW_COST = pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,TOTAL_PROJECT_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,TOTAL_PROJECT_REVENUE = l_revenue
,TOTAL_PROJFUNC_RAW_COST = pa_currency.round_trans_currency_amt
(decode(p_CHANGE_AMOUNT,null,0,p_CHANGE_AMOUNT),p_CURRENCY_CODE)
,TOTAL_PROJFUNC_BURDENED_COST = decode(p_Burdened_cost,null,(p_CHANGE_AMOUNT+(p_CHANGE_AMOUNT*0.1)),p_Burdened_cost)
,TOTAL_PROJFUNC_REVENUE = l_revenue
,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
,LAST_UPDATED_BY = 1319
,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
WHERE RESOURCE_ASSIGNMENT_ID = l_res_asgn_Id;
END update_row;
PROCEDURE delete_row (p_ci_transaction_id in NUMBER)IS
cursor fetch_details is
select TASK_ID, RESOURCE_LIST_MEMBER_ID, ci_id
from pa_ci_supplier_details
where CI_TRANSACTION_ID = p_ci_transaction_id;
select budget_version_id
from pa_budget_versions
where ci_id = p_ci_id;
select pra.RESOURCE_ASSIGNMENT_ID, ppe.ELEMENT_NUMBER, ppe.NAME
from pa_resource_assignments pra, pa_proj_elements ppe, pa_tasks pt
WHERE pra.budget_version_id = p_bvId
and pra.task_id = p_task_id
and pra.RESOURCE_LIST_MEMBER_ID = p_res_id
and pt.task_id = ppe.proj_element_id;
DELETE FROM PA_CI_SUPPLIER_DETAILS
WHERE CI_TRANSACTION_ID = P_CI_TRANSACTION_ID;
print_msg('Delete Success');
--call delete planning element api
open get_budget_version_id(l_ci_id);
pa_fp_planning_transaction_pub.delete_planning_transactions(
p_context => 'BUDGET'
,p_task_or_res => 'ASSIGNMENT'
,p_element_version_id_tbl => l_task_elem_version_id_tbl
,p_task_number_tbl => l_task_number_tbl
,p_task_name_tbl => l_task_name_tbl
,p_resource_assignment_tbl => l_resource_assignment_id_tbl
,p_currency_code_tbl => l_currency_code_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_error_msg_code);
print_msg('Delete Failure');
END delete_row;
PROCEDURE delete_row (x_rowid in VARCHAR2)IS
cursor get_itemid is
select ci_transaction_id
from pa_ci_supplier_details
where rowid = x_rowid;
delete_row (l_ci_transaction_id);
END delete_row;