[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 28-APR-2004 EPASQUIN Updated the pkg to use PA_RBS_ELEMENTS table
** instead of PA_PROJ_ELEM_VER_RBS.
*/
PROCEDURE derive_parameters(
p_project_id NUMBER
,p_calendar_type VARCHAR2
,p_calendar_id NUMBER
,p_time_id NUMBER
,p_wbs_element_id NUMBER
,p_rbs_element_id NUMBER
,p_commitment_flag VARCHAR2
,p_time_flag VARCHAR2
,x_start_date OUT NOCOPY DATE
,x_end_date OUT NOCOPY DATE
,x_task_id OUT NOCOPY NUMBER
,x_rev_categ_code OUT NOCOPY VARCHAR2
,x_event_type_id OUT NOCOPY NUMBER
,x_event_type OUT NOCOPY VARCHAR2
,x_inventory_item_ids OUT NOCOPY VARCHAR2
,x_org_id OUT NOCOPY NUMBER
,x_expenditure_category_id OUT NOCOPY NUMBER
,x_expenditure_type_id OUT NOCOPY NUMBER
,x_item_category_id OUT NOCOPY NUMBER
,x_job_id OUT NOCOPY NUMBER
,x_person_type_id OUT NOCOPY NUMBER
,x_person_id OUT NOCOPY NUMBER
,x_non_labor_resource_id OUT NOCOPY NUMBER
,x_bom_equipment_resource_id OUT NOCOPY NUMBER
,x_bom_labor_resource_id OUT NOCOPY NUMBER
,x_vendor_id OUT NOCOPY NUMBER
,x_resource_class_id OUT NOCOPY NUMBER
,x_resource_class_code OUT NOCOPY VARCHAR2
,x_person_type OUT NOCOPY VARCHAR2
,x_expenditure_type OUT NOCOPY VARCHAR2
,x_prg_project_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) AS
inv_item_id_list number_nestedtb;
if ITD then all commitment transaction should select.
If Non Commitment Transaction
if PTD then all transaction within the the start and end reporting period.
if ITD then all the transaction which are before end reporting period. */
x_start_date := TO_DATE('01-01-1950','DD/MM/YYYY');
SELECT project_id
INTO x_prg_project_id
FROM pa_proj_elements
WHERE proj_element_id = p_wbs_element_id ;
SELECT start_date, end_date
INTO l_start_date, l_end_date
FROM
(
SELECT start_date, end_date
FROM pji_time_ent_period_v per
,pji_time_rpt_struct rpt
WHERE 1=1
AND 'E' = p_calendar_type
AND per.ent_period_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 256
UNION ALL
SELECT start_date, end_date
FROM pji_time_cal_period_V per
,pji_time_cal_rpt_struct rpt
WHERE 1=1
AND per.calendar_id = p_calendar_id
AND 'E' <> p_calendar_type
AND per.cal_period_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 256
AND rpt.calendar_id = p_calendar_id
);
SELECT start_date, end_date
INTO l_start_date, l_end_date
FROM
(
SELECT start_date, end_date
FROM pji_time_ent_QTR_v per
,pji_time_rpt_struct rpt
WHERE 1=1
AND 'E' = p_calendar_type
AND per.ent_qtr_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 512
UNION ALL
SELECT start_date, end_date
FROM pji_time_cal_qtr_V per
,pji_time_cal_rpt_struct rpt
WHERE 1=1
AND per.calendar_id = p_calendar_id
AND 'E' <> p_calendar_type
AND per.cal_qtr_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 512
AND rpt.calendar_id = p_calendar_id
);
SELECT start_date, end_date
INTO l_start_date, l_end_date
FROM
(
SELECT start_date, end_date
FROM pji_time_ent_year_v per
,pji_time_rpt_struct rpt
WHERE 1=1
AND 'E' = p_calendar_type
AND per.ent_year_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 128
UNION ALL
SELECT start_date, end_date
FROM pji_time_cal_year_V per
,pji_time_cal_rpt_struct rpt
WHERE 1=1
AND per.calendar_id = p_calendar_id
AND 'E' <> p_calendar_type
AND per.cal_year_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 512
AND rpt.calendar_id = p_calendar_id
);
SELECT start_date, closed_date --ACTUAL_FINISH_DATE, ACTUAL_START_DATE,
INTO x_start_date, x_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT start_date, end_date
INTO x_start_date, x_end_date
FROM
(
SELECT start_date, end_date
FROM
pji_time_ent_period_v per
, pji_time_rpt_struct rpt
WHERE 1=1
AND 'E' = p_calendar_type
AND per.ent_period_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 256
UNION ALL
SELECT start_date, end_date
FROM
pji_time_cal_period_V per
, pji_time_cal_rpt_struct rpt
WHERE 1=1
AND per.calendar_id = p_calendar_id
AND 'E' <> p_calendar_type
AND per.cal_period_id = rpt.time_id
AND rpt.report_date = TO_DATE(p_time_id,'j')
AND rpt.record_type_id = 256
AND rpt.calendar_id = p_calendar_id
);
SELECT object_type
INTO l_object_type
FROM pa_proj_elements
WHERE proj_element_id = p_wbs_element_id;
SELECT
rbs.event_type_id
,et.event_type
,rbs.organization_id
,rbs.inventory_item_id
,rbs.expenditure_category_id
,rbs.expenditure_type_id
,expt.expenditure_type
,rbs.item_category_id
,rbs.job_id
,rbs.person_type_id
,rbs.person_id
,rbs.non_labor_resource_id
,rbs.bom_equipment_id
,rbs.bom_labor_id
,rbs.supplier_id
,rbs.resource_class_id
,rc.resource_class_code
--,rbsn.resourcetype
,et.revenue_category_code
INTO
x_event_type_id
,x_event_type
,x_org_id
,inv_item_id
,x_expenditure_category_id
,x_expenditure_type_id
,x_expenditure_type
,x_item_category_id
,x_job_id
,x_person_type_id
,x_person_id
,x_non_labor_resource_id
,x_bom_equipment_resource_id
,x_bom_labor_resource_id
,x_vendor_id
,x_resource_class_id
,x_resource_class_code
--,x_resourcetype
,x_rev_categ_code
FROM
PA_RBS_ELEMENTS rbs
,pa_event_types et
,pa_resource_classes_b rc
,pa_expenditure_types expt
--,PA_RBS_ELEMENT_NAMES_B rbsn
WHERE 1=1
AND rbs.rbs_element_id = p_rbs_element_id
AND rbs.resource_class_id = rc.resource_class_id (+) -- Added outer joing for bug 3848087
-- we want to obtain the rbs records regardless of event_type_id or expenditure_type_id
AND rbs.event_type_id = et.event_type_id (+)
AND rbs.expenditure_type_id = expt.expenditure_type_id (+)
--AND rbs.element_version_id = rbsn.rbs_element_id
;
SELECT system_person_type
INTO x_person_type
FROM per_person_types
WHERE person_type_id = x_person_type_id;
SELECT cat.inventory_item_id
BULK COLLECT INTO inv_item_id_list
FROM
pa_resource_classes_b cls
, pa_plan_res_defaults def
, mtl_item_categories cat
WHERE 1=1
AND cls.resource_class_id = def.object_id
AND cls.resource_class_code = 'MATERIAL_ITEMS'
AND def.object_type = 'CLASS'
AND cat.organization_id = def.item_master_id
AND cat.category_set_id = def.item_category_set_id
AND category_id = x_item_category_id
;
SELECT object_type
INTO l_object_type
FROM pa_proj_elements
WHERE proj_element_id = p_wbs_element_id;
SELECT chargeable_flag, top_task_id
INTO l_chargeable_flag, l_top_task_id
FROM pa_tasks
WHERE task_id = p_wbs_element_id;