[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(item_date) item_date
FROM pa_forecast_items
WHERE resource_id = TmpAsgnDtlRec.resource_id
AND delete_flag = 'N'
AND forecast_item_type = 'U';
TmpErrHdrTab.Delete;
TmpErrHdrTab.DELETE;
TmpErrTab.Delete;
SELECT proj_asgn.assignment_id,
decode(proj_asgn.assignment_type,
'OPEN_ASSIGNMENT', 'OPEN_ASGMT',
'STAFFED_ASSIGNMENT', 'STAFFED_ASGMT',
'STAFFED_ADMIN_ASSIGNMENT', 'STAFFED_ASGMT',
'STAFFED_ASGMT'),
proj_asgn.status_code,proj_asgn.start_date,
proj_asgn.end_date, proj_asgn.source_assignment_id,
proj_asgn.project_id, proj_asgn.resource_id,
proj_asgn.work_type_id,
NVL(proj_asgn.expenditure_org_id,-99)
expenditure_org_id,
proj_asgn.expenditure_organization_id,
proj_asgn.expenditure_type,
proj_asgn.expenditure_type_class,
fcst_tp_amount_type
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.resource_id = lv_resource_id
AND ( (lv_start_date BETWEEN proj_asgn.start_date AND
proj_asgn.end_date)
OR (lv_end_date BETWEEN proj_asgn.start_date AND
proj_asgn.end_date)
OR ( lv_start_date < proj_asgn.start_date AND
lv_end_date > proj_asgn.end_date ));
lv_AssignmentIdTab.delete;
lv_AssignmentIdTab.delete ;
lv_AssignmentTypeTab.delete;
lv_StatusCodeTab.delete ;
lv_StartDateTab.delete ;
lv_EndDateTab.delete ;
lv_SourceAssignmentIDTab.delete;
lv_ProjectIDTab.delete;
lv_ResourceIDTab.delete;
lv_WorkTypeIDTab.delete ;
lv_ExpenditureOrgIDTab.delete ;
lv_ExpenditureOrgnIDTab.delete ;
lv_ExpenditureTypeTab.delete ;
lv_ExpTypeClassTab.delete ;
lv_FcstTpAmountTypeTab.delete;
| Procedure : Delete_Forecast_Item
| Purpose : To delete all FI's for a given assignment_id
| Parameters : p_assignment_id - Input assignment id
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_Forecast_Item (
p_assignment_id IN NUMBER,
p_resource_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added nocopy
x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added nocopy
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865 Added nocopy
TmpAsgnDtlRec PA_FORECAST_GLOB.AsgnDtlRecord;
'PA_FORECASTITEM_PVT.Delete_Forecast_Item');
SELECT count(*) into lv_asg_count
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
Print_message( 'Calling Delete_FI');
Delete_FI( p_assignment_id=>p_assignment_id,
x_return_status=>lv_return_status,
x_msg_count=>x_msg_count,
x_msg_data=> x_msg_data );
print_message('Failed in delete_forecast_item api');
'PA_FORECASTITEM_PVT.Delete_Forecast_Item',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_Forecast_Item;
SELECT assignment_id,assignment_type,
status_code,start_date, end_date,
source_assignment_id, project_id, resource_id,
work_type_id,
NVL(expenditure_org_id,-99) expenditure_org_id,
expenditure_organization_id,
expenditure_type, expenditure_type_class,
fcst_tp_amount_type
INTO x_AsgnDtlRec
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT pr.org_id, pr.carrying_out_organization_id,
pr.work_type_id, pt.project_type_class_code,
pr.project_status_code
INTO x_project_org_id, x_project_orgn_id,
x_work_type_id, x_project_type_class,
x_project_status_code
FROM pa_projects_all pr, pa_project_types_all pt
WHERE pr.project_id = p_project_id
AND pt.org_id = pr.org_id
AND pt.project_type =pr.project_type;
TmpDbFIDtlTab.Delete;
TmpDbFIHdrTab.Delete;
TmpFIDtlInsTab.Delete;
TmpFIDtlUpdTab.Delete;
TmpFIHdrInsTab.Delete;
TmpFIHdrUpdTab.Delete;
TmpScheduleTab.Delete;
TmpFIDayTab.Delete;
TmpFIHdrInsTab.delete; -- Initialize
TmpFIHdrUpdTab.delete; -- Initialize
TmpFIDtlUpdTab.delete; -- Initialize
TmpFIDtlUpdTab.delete; -- Initialize
TmpDBFIHdrTab.delete; -- Initialize
TmpDBFIDtlTab.delete; -- Initialize
Print_message( 'Calling PA_FORECAST_HDR_PKG.Insert_Rows');
PA_FORECAST_HDR_PKG.Insert_Rows(
TmpFIHdrInsTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_HDR_PKG.Update_Rows');
PA_FORECAST_HDR_PKG.Update_Rows(TmpFIHdrUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Insert_Rows');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpFIDtlInsTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Update_Rows');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpFIDtlUpdTab,
lv_return_status, x_msg_count, x_msg_data);
'Calling PA_FORECAST_HDR_PKG.Update_Schedule_Rows');
PA_FORECAST_HDR_PKG.Update_Schedule_Rows( TmpScheduleTab,
lv_return_status,
x_msg_count,
x_msg_data);
SELECT 'Y'
INTO lv_exist_flag
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pa_forecast_items
WHERE assignment_id = p_assignment_id
AND delete_flag = 'N');
TmpFIDayTab.Delete;
TmpFIDayTab.Delete;
TmpFIDayTab.Delete;
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
TmpUpdtab.delete;
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
FROM pa_forecast_items hdr
WHERE assignment_id = p_assignment_id
AND delete_flag = 'N'
AND (trunc(hdr.item_date) < trunc(p_start_date)
OR trunc(hdr.item_date) > trunc(p_end_date))
order by item_date, forecast_item_id ;
TmpUpdTab(j).delete_flag := 'Y';
Calling PA_FORECAST_HDR_PKG.Update_rows');
PA_FORECAST_HDR_PKG.Update_Rows(
TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
forecast_item_id_tab.delete;
amount_type_id_tab.delete;
line_num_tab.delete;
resource_type_code_tab.delete;
person_billable_flag_tab.delete;
item_date_tab.delete;
item_UOM_tab.delete;
item_quantity_tab.delete;
expenditure_org_id_tab.delete;
project_org_id_tab.delete;
PJI_SUMMARIZED_FLAG_tab.delete;
CAPACITY_QUANTITY_tab.delete;
OVERCOMMITMENT_QTY_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
JOB_ID_tab.delete;
PROJECT_ID_tab.delete;
RESOURCE_ID_tab.delete;
EXP_ORGANIZATION_ID_tab.delete;
pvdr_acct_curr_code_tab.delete;
pvdr_acct_amount_tab.delete;
rcvr_acct_curr_code_tab.delete;
rcvr_acct_amount_tab.delete;
proj_currency_code_tab.delete;
proj_amount_tab.delete;
denom_currency_code_tab.delete;
denom_amount_tab.delete;
tp_amount_type_tab.delete;
billable_flag_tab.delete;
forecast_summarized_code_tab.delete;
util_summarized_code_tab.delete;
work_type_id_tab.delete;
resource_util_category_id_tab.delete;
org_util_category_id_tab.delete;
resource_util_weighted_tab.delete;
org_util_weighted_tab.delete;
provisional_flag_tab.delete;
reversed_flag_tab.delete;
net_zero_flag_tab.delete;
reduce_capacity_flag_tab.delete;
line_num_reversed_tab.delete;
TmpRevTab.delete;
TmpUpdTab.delete;
SELECT dtl.forecast_item_id, dtl.amount_type_id,
dtl.line_num, dtl.resource_type_code,
dtl.person_billable_flag, dtl.item_UOM, dtl.item_date,
dtl.PJI_SUMMARIZED_FLAG,
dtl.CAPACITY_QUANTITY,
dtl.OVERCOMMITMENT_QTY,
dtl.OVERPROVISIONAL_QTY,
dtl.OVER_PROV_CONF_QTY,
dtl.CONFIRMED_QTY,
dtl.PROVISIONAL_QTY,
dtl.JOB_ID,
dtl.PROJECT_ID,
dtl.RESOURCE_ID,
dtl.EXPENDITURE_ORGANIZATION_ID,
dtl.item_quantity, dtl.expenditure_org_id,
dtl.project_org_id, dtl.pvdr_acct_curr_code,
dtl.pvdr_acct_amount, dtl.rcvr_acct_curr_code,
dtl.rcvr_acct_amount, dtl.proj_currency_code,
dtl.proj_amount, dtl.denom_currency_code, dtl.denom_amount,
dtl.tp_amount_type, dtl.billable_flag,
dtl.forecast_summarized_code, dtl.util_summarized_code,
dtl.work_type_id, dtl.resource_util_category_id,
dtl.org_util_category_id, dtl.resource_util_weighted,
dtl.org_util_weighted, dtl.provisional_flag,
dtl.reversed_flag, dtl.net_zero_flag,
dtl.reduce_capacity_flag, dtl.line_num_reversed
BULK COLLECT INTO forecast_item_id_tab,amount_type_id_tab,
line_num_tab, resource_type_code_tab,
person_billable_flag_tab, item_UOM_tab, item_date_tab,
PJI_SUMMARIZED_FLAG_tab,
CAPACITY_QUANTITY_tab,
OVERCOMMITMENT_QTY_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
JOB_ID_tab,
PROJECT_ID_tab,
RESOURCE_ID_tab,
EXP_ORGANIZATION_ID_tab,
item_quantity_tab, expenditure_org_id_tab,
project_org_id_tab, pvdr_acct_curr_code_tab,
pvdr_acct_amount_tab, rcvr_acct_curr_code_tab,
rcvr_acct_amount_tab, proj_currency_code_tab,
proj_amount_tab, denom_currency_code_tab, denom_amount_tab,
tp_amount_type_tab, billable_flag_tab,
forecast_summarized_code_tab, util_summarized_code_tab,
work_type_id_tab, resource_util_category_id_tab,
org_util_category_id_tab, resource_util_weighted_tab,
org_util_weighted_tab, provisional_flag_tab,
reversed_flag_tab, net_zero_flag_tab,
reduce_capacity_flag_tab, line_num_reversed_tab
FROM pa_forecast_item_details dtl, pa_forecast_items hdr
WHERE hdr.assignment_id = p_assignment_id
AND hdr.delete_flag = 'N'
AND dtl.forecast_item_id = hdr.forecast_item_id
AND (dtl.item_date < trunc(p_start_date)
OR dtl.item_date > trunc(p_end_date) + 1 - (1/86400))
AND dtl.line_num =
(SELECT max(line_num)
FROM pa_forecast_item_details dtl1
WHERE dtl1.forecast_item_id = hdr.forecast_item_id)
order by dtl.item_date, dtl.forecast_item_id ;
'Calling PA_FORECAST_DTLS_PKG.Insert_Rows');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpRevTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Calling PA_FORECAST_DTLS_PKG.Update_Rows');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
TmpHdrTab.delete;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
FROM pa_forecast_items
WHERE assignment_id = p_assignment_id
AND delete_flag = 'N'
AND item_date BETWEEN trunc(p_start_date) AND
trunc(p_end_date) + 1 - (1/86400)
order by item_date, forecast_item_id ;
TmpHdrTab(j).delete_flag := delete_flag_tab(j);
TmpDtlTab.delete;
forecast_item_id_tab.delete;
amount_type_id_tab.delete;
line_num_tab.delete;
resource_type_code_tab.delete;
person_billable_flag_tab.delete;
item_date_tab.delete;
item_UOM_tab.delete;
item_quantity_tab.delete;
expenditure_org_id_tab.delete;
project_org_id_tab.delete;
PJI_SUMMARIZED_FLAG_tab.delete;
CAPACITY_QUANTITY_tab.delete;
OVERCOMMITMENT_QTY_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
JOB_ID_tab.delete;
PROJECT_ID_tab.delete;
RESOURCE_ID_tab.delete;
EXP_ORGANIZATION_ID_tab.delete;
pvdr_acct_curr_code_tab.delete;
pvdr_acct_amount_tab.delete;
rcvr_acct_curr_code_tab.delete;
rcvr_acct_amount_tab.delete;
proj_currency_code_tab.delete;
proj_amount_tab.delete;
denom_currency_code_tab.delete;
denom_amount_tab.delete;
tp_amount_type_tab.delete;
billable_flag_tab.delete;
forecast_summarized_code_tab.delete;
util_summarized_code_tab.delete;
work_type_id_tab.delete;
resource_util_category_id_tab.delete;
org_util_category_id_tab.delete;
resource_util_weighted_tab.delete;
org_util_weighted_tab.delete;
provisional_flag_tab.delete;
reversed_flag_tab.delete;
net_zero_flag_tab.delete;
reduce_capacity_flag_tab.delete;
line_num_reversed_tab.delete;
SELECT dtl.forecast_item_id, dtl.amount_type_id,
dtl.line_num, dtl.resource_type_code,
dtl.person_billable_flag, dtl.item_UOM, dtl.item_date,
dtl.PJI_SUMMARIZED_FLAG,
dtl.CAPACITY_QUANTITY,
dtl.OVERCOMMITMENT_QTY,
dtl.OVERPROVISIONAL_QTY,
dtl.OVER_PROV_CONF_QTY,
dtl.CONFIRMED_QTY,
dtl.PROVISIONAL_QTY,
dtl.JOB_ID,
dtl.PROJECT_ID,
dtl.RESOURCE_ID,
dtl.EXPENDITURE_ORGANIZATION_ID,
dtl.item_quantity, dtl.expenditure_org_id,
dtl.project_org_id, dtl.pvdr_acct_curr_code,
dtl.pvdr_acct_amount, dtl.rcvr_acct_curr_code,
dtl.rcvr_acct_amount, dtl.proj_currency_code,
dtl.proj_amount, dtl.denom_currency_code,
dtl.denom_amount, dtl.tp_amount_type, dtl.billable_flag,
dtl.forecast_summarized_code, dtl.util_summarized_code,
dtl.work_type_id, dtl.resource_util_category_id,
dtl.org_util_category_id, dtl.resource_util_weighted,
dtl.org_util_weighted, dtl.provisional_flag,
dtl.reversed_flag, dtl.net_zero_flag,
dtl.reduce_capacity_flag, dtl.line_num_reversed
BULK COLLECT INTO forecast_item_id_tab,amount_type_id_tab,
line_num_tab, resource_type_code_tab,
person_billable_flag_tab, item_UOM_tab, item_date_tab,
PJI_SUMMARIZED_FLAG_tab,
CAPACITY_QUANTITY_tab,
OVERCOMMITMENT_QTY_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
JOB_ID_tab,
PROJECT_ID_tab,
RESOURCE_ID_tab,
EXP_ORGANIZATION_ID_tab,
item_quantity_tab, expenditure_org_id_tab,
project_org_id_tab, pvdr_acct_curr_code_tab,
pvdr_acct_amount_tab, rcvr_acct_curr_code_tab,
rcvr_acct_amount_tab, proj_currency_code_tab,
proj_amount_tab, denom_currency_code_tab,
denom_amount_tab, tp_amount_type_tab, billable_flag_tab,
forecast_summarized_code_tab, util_summarized_code_tab,
work_type_id_tab, resource_util_category_id_tab,
org_util_category_id_tab, resource_util_weighted_tab,
org_util_weighted_tab, provisional_flag_tab,
reversed_flag_tab, net_zero_flag_tab,
reduce_capacity_flag_tab, line_num_reversed_tab
FROM pa_forecast_item_details dtl , pa_forecast_items hdr
WHERE hdr.assignment_id = p_assignment_id
AND hdr.delete_flag = 'N'
AND dtl.forecast_item_id = hdr.forecast_item_id
AND dtl.item_date BETWEEN trunc(p_start_date) AND
trunc(p_end_date) + 1 - (1/86400)
AND dtl.line_num =
(SELECT max(line_num)
FROM pa_forecast_item_details dtl1
WHERE dtl1.forecast_item_id = hdr.forecast_item_id)
order by dtl.item_date, dtl.forecast_item_id ;
| i) action_flag component of this tab will be updated
| to indicate the following
| a) N : New record - item_date does not exist
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| ii) forecast_item_id component of this tab will be updated
| to hold the forecast_item_id FOR new record. Same will
| be used FOR detail record
| iii) project_org_id,expenditure_org_id,work_type_id,
| person_billable_flag, tp_amount_type : These values
| are required FOR detail record processing. These are
| also updated IN this tab.
|
| x_FIHdrInsTab - Will RETURN all forecast item records that
| are new
| x_FIHdrUpdTab - Will RETURN all forecast item records that
| are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Hdr_Req(
p_AsgnDtlRec IN PA_FORECAST_GLOB.AsgnDtlRecord,
p_DBHdrTab IN PA_FORECAST_GLOB.FIHdrTabTyp,
p_FIDayTab IN OUT NOCOPY PA_FORECAST_GLOB.FIDayTabTyp, /* 2674619 - Nocopy change */
x_FIHdrInsTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_FIHdrUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_data VARCHAR2(2000) ; -- 4537865
TmpDayTab.Delete;
TmpInsTab.Delete;
TmpUpdTab.Delete;
ld_pvdrpa_startdate_tab.delete;
ld_pvdrpa_enddate_tab.delete;
lv_pvdrpa_name_tab.delete;
ld_pvdrgl_startdate_tab.delete;
ld_pvdrgl_enddate_tab.delete;
lv_pvdrgl_name_tab.delete;
ld_rcvrpa_startdate_tab.delete;
ld_rcvrpa_enddate_tab.delete;
lv_rcvrpa_name_tab.delete;
ld_rcvrgl_startdate_tab.delete;
ld_rcvrgl_enddate_tab.delete;
lv_rcvrgl_name_tab.delete;
TmpUpdTab(u_in).delete_flag := 'Y';
TmpUpdTab(u_in).delete_flag := 'Y';
TmpHdrRec.Delete_Flag := 'N';
' Del_flag:' || TmpHdrRec.delete_flag ||
' Err_flag:' || TmpHdrRec.error_flag ||
' Prv_flag:' || TmpHdrRec.provisional_flag);
x_FIHdrInsTab.Delete;
x_FIHdrUpdTab.Delete;
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| x_FIDtlInsTab - Will RETURN all forecast item detail records
| that are new
| x_FIDtlUpdTab - Will RETURN all forecast item detail records
| that are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Dtl_Req(
p_AsgnDtlRec IN PA_FORECAST_GLOB.AsgnDtlRecord,
p_DBDtlTab IN PA_FORECAST_GLOB.FIDtlTabTyp,
p_FIDayTab IN PA_FORECAST_GLOB.FIDayTabTyp,
x_FIDtlInsTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_FIDtlUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_msg_index_out NUMBER;
TmpInsTab.Delete;
TmpUpdTab.Delete;
TmpDayTab.Delete;
x_FIDtlInsTab.Delete;
x_FIDtlUpdTab.Delete;
select min(resource_effective_start_date),
max(resource_effective_end_date)
from pa_resources_denorm
where resource_id = p_AsgnDtlRec.resource_id;
TmpDBFIDtlTab.delete;
TmpDBFIHdrTab.delete;
TmpFIDtlInsTab.delete;
TmpFIDtlUpdTab.delete;
TmpFIHdrInsTab.delete;
TmpFIHdrUpdTab.delete;
TmpScheduleTab.delete;
TmpFIDayTab.delete;
TmpDumTab.delete;
'Calling Delete_FI');
Delete_FI(
p_assignment_id=>p_AsgnDtlRec.source_assignment_id,
x_return_status=>lv_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data);
TmpFIHdrInsTab.delete; -- Initialize
TmpFIHdrUpdTab.delete; -- Initialize
TmpFIDtlInsTab.delete; -- Initialize
TmpFIDtlUpdTab.delete; -- Initialize
TmpDBFIHdrTab.delete; -- Initialize
TmpDBFIDtlTab.delete; -- Initialize
Print_message( 'Calling PA_FORECAST_HDR_PKG.Insert_Rows');
PA_FORECAST_HDR_PKG.Insert_Rows(TmpFIHdrInsTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_HDR_PKG.Update_Rows');
PA_FORECAST_HDR_PKG.Update_Rows(TmpFIHdrUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Insert_Rows');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpFIDtlInsTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Update_Rows');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpFIDtlUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Calling PA_FORECAST_HDR_PKG.Update_Schedule_Rows');
PA_FORECAST_HDR_PKG.Update_Schedule_Rows(
TmpScheduleTab,
lv_return_status,
x_msg_count,
x_msg_data);
SELECT 'Y'
INTO lv_exist_flag
FROM dual
WHERE exists (SELECT null
FROM pa_forecast_items
WHERE assignment_id = p_assignment_id
AND delete_flag = 'N');
| Procedure : Delete_FI
| Purpose : To delete all the forecast items FOR the given assignment
| Called when source requirement FI is to be deleted(reversed)
| when requirement or assignment record is deleted
| Parameters : p_assignment_id - Input Assignment ID
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI(
p_assignment_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
lv_return_status VARCHAR2(30);
Print_message( 'Entering Delete_FI');
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI');
Delete_FI_Dtl( p_assignment_id=>p_assignment_id,
x_return_status=>lv_return_status,
x_msg_count=>x_msg_count,
x_msg_data=> x_msg_data );
Delete_FI_Hdr( p_assignment_id=>p_assignment_id,
x_return_status=>lv_return_status,
x_msg_count=>x_msg_count,
x_msg_data=> x_msg_data );
'Leaving Delete_FI');
print_message('Failed in Delete FI api');
'PA_FORECASTITEM_PVT.Delete_FI',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI;
| Procedure : Delete_FI_Hdr
| Purpose : To reverse the existing forecast items (Requirement)
| when a resource is identified FOR a requirement
| Parameters : p_assignment_id - Input Assignment ID
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI_Hdr(
p_assignment_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_data varchar2(2000) ; -- 4537865
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
Print_message( 'Entering Delete_FI_Hdr');
TmpUpdTab.delete;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI_Hdr');
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
FROM pa_forecast_items hdr
WHERE hdr.assignment_id = p_assignment_id
AND hdr.delete_flag = 'N'
order by item_date, forecast_item_id ;
'Leaving Delete_FI_Hdr');
TmpUpdTab.Delete;
TmpUpdTab(j).delete_flag := 'Y';
'Calling PA_FORECAST_HDR_PKG.Update_Rows ');
PA_FORECAST_HDR_PKG.Update_Rows(TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Leaving Delete_FI_Hdr');
print_message('Failed in Delete_FI_Hdr api');
'PA_FORECASTITEM_PVT.Delete_FI_Hdr',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI_Hdr;
| Procedure : Delete_FI_Dtl
| Purpose : To reverse the existing forecast items detail(Requirement)
| when a resource is identified FOR a requirement
| Parameters : p_assignment_id - Input Assignment ID
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI_Dtl(
p_assignment_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_data varchar2(2000) ; -- 4537865
Print_message( 'Entering Delete_FI_Dtl');
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI_Dtl');
TmpRevTab.Delete;
TmpUpdTab.Delete;
forecast_item_id_tab.delete;
amount_type_id_tab.delete;
line_num_tab.delete;
resource_type_code_tab.delete;
person_billable_flag_tab.delete;
item_date_tab.delete;
item_UOM_tab.delete;
item_quantity_tab.delete;
expenditure_org_id_tab.delete;
project_org_id_tab.delete;
PJI_SUMMARIZED_FLAG_tab.delete;
CAPACITY_QUANTITY_tab.delete;
OVERCOMMITMENT_QTY_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
JOB_ID_tab.delete;
PROJECT_ID_tab.delete;
RESOURCE_ID_tab.delete;
EXP_ORGANIZATION_ID_tab.delete;
pvdr_acct_curr_code_tab.delete;
pvdr_acct_amount_tab.delete;
rcvr_acct_curr_code_tab.delete;
rcvr_acct_amount_tab.delete;
proj_currency_code_tab.delete;
proj_amount_tab.delete;
denom_currency_code_tab.delete;
denom_amount_tab.delete;
tp_amount_type_tab.delete;
billable_flag_tab.delete;
forecast_summarized_code_tab.delete;
util_summarized_code_tab.delete;
work_type_id_tab.delete;
resource_util_category_id_tab.delete;
org_util_category_id_tab.delete;
resource_util_weighted_tab.delete;
org_util_weighted_tab.delete;
provisional_flag_tab.delete;
reversed_flag_tab.delete;
net_zero_flag_tab.delete;
reduce_capacity_flag_tab.delete;
line_num_reversed_tab.delete;
SELECT dtl.forecast_item_id, dtl.amount_type_id,
dtl.line_num, dtl.resource_type_code,
dtl.person_billable_flag, dtl.item_UOM, dtl.item_date,
dtl.PJI_SUMMARIZED_FLAG,
dtl.CAPACITY_QUANTITY,
dtl.OVERCOMMITMENT_QTY,
dtl.OVERPROVISIONAL_QTY,
dtl.OVER_PROV_CONF_QTY,
dtl.CONFIRMED_QTY,
dtl.PROVISIONAL_QTY,
dtl.JOB_ID,
dtl.PROJECT_ID,
dtl.RESOURCE_ID,
dtl.EXPENDITURE_ORGANIZATION_ID,
dtl.item_quantity, dtl.expenditure_org_id,
dtl.project_org_id, dtl.pvdr_acct_curr_code,
dtl.pvdr_acct_amount, dtl.rcvr_acct_curr_code,
dtl.rcvr_acct_amount, dtl.proj_currency_code,
dtl.proj_amount, dtl.denom_currency_code, dtl.denom_amount,
dtl.tp_amount_type, dtl.billable_flag,
dtl.forecast_summarized_code, dtl.util_summarized_code,
dtl.work_type_id, dtl.resource_util_category_id,
dtl.org_util_category_id, dtl.resource_util_weighted,
dtl.org_util_weighted, dtl.provisional_flag,
dtl.reversed_flag, dtl.net_zero_flag,
dtl.reduce_capacity_flag, dtl.line_num_reversed
BULK COLLECT INTO forecast_item_id_tab,amount_type_id_tab,
line_num_tab, resource_type_code_tab,
person_billable_flag_tab, item_UOM_tab, item_date_tab,
PJI_SUMMARIZED_FLAG_tab,
CAPACITY_QUANTITY_tab,
OVERCOMMITMENT_QTY_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
JOB_ID_tab,
PROJECT_ID_tab,
RESOURCE_ID_tab,
EXP_ORGANIZATION_ID_tab,
item_quantity_tab, expenditure_org_id_tab,
project_org_id_tab, pvdr_acct_curr_code_tab,
pvdr_acct_amount_tab, rcvr_acct_curr_code_tab,
rcvr_acct_amount_tab, proj_currency_code_tab,
proj_amount_tab, denom_currency_code_tab, denom_amount_tab,
tp_amount_type_tab, billable_flag_tab,
forecast_summarized_code_tab, util_summarized_code_tab,
work_type_id_tab, resource_util_category_id_tab,
org_util_category_id_tab, resource_util_weighted_tab,
org_util_weighted_tab, provisional_flag_tab,
reversed_flag_tab, net_zero_flag_tab,
reduce_capacity_flag_tab, line_num_reversed_tab
FROM pa_forecast_item_details dtl, pa_forecast_items hdr
WHERE hdr.assignment_id = p_assignment_id
AND hdr.delete_flag = 'N'
AND dtl.forecast_item_id = hdr.forecast_item_id
AND dtl.line_num =
(SELECT max(line_num)
FROM pa_forecast_item_details dtl1
WHERE dtl1.forecast_item_id = hdr.forecast_item_id AND trunc(dtl1.item_date) = trunc(hdr.item_date) ) -- 4918687 SQL ID 14905526
AND trunc(dtl.item_date) = trunc(hdr.item_date) -- 4918687 SQL ID 14905526
order by dtl.item_date, dtl.forecast_item_id ;
Print_message( 'Leaving Delete_FI_Dtl');
TmpRevTab.Delete;
TmpUpdTab.Delete;
'Calling PA_FORECAST_DTLS_PKG.Insert_Rows ');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpRevTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Calling PA_FORECAST_DTLS_PKG.Update_Rows ');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Leaving Delete_FI_Dtl');
print_message('Failed in Delete FI Dtl api');
'PA_FORECASTITEM_PVT.Delete_FI_Dtl',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI_Dtl;
| Procedure : Delete_FI
| Purpose : To delete all the forecast items FOR the given resource
| Parameters : p_resource_id
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI(
p_resource_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
lv_return_status VARCHAR2(30);
Print_message( 'Entering Delete_FI');
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI');
Delete_FI_Dtl(
p_resource_id => p_resource_id,
x_return_status => lv_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Delete_FI_Hdr(
p_resource_id => p_resource_id,
x_return_status => lv_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Leaving Delete_FI');
print_message('Failed in Delete FI api');
'PA_FORECASTITEM_PVT.Delete_FI',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI;
| Procedure : Delete_FI_Hdr
| Purpose : To reverse the existing forecast items
| Parameters : p_resource_id
| x_return_status
| x_msg_count
| x_msg_data
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI_Hdr(
p_resource_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
forecast_item_id_tab PA_FORECAST_GLOB.NumberTabTyp;
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
Print_message( 'Entering Delete_FI_Hdr');
TmpUpdTab.delete;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI_Hdr');
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab
FROM pa_forecast_items hdr
WHERE hdr.resource_id = p_resource_id
AND hdr.delete_flag = 'N'
AND hdr.forecast_item_type = 'U'
order by item_date, forecast_item_id ;
'Leaving Delete_FI_Hdr');
TmpUpdTab.Delete;
TmpUpdTab(j).delete_flag := 'Y';
'Calling PA_FORECAST_HDR_PKG.Update_Rows ');
PA_FORECAST_HDR_PKG.Update_Rows(TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Leaving Delete_FI_Hdr');
print_message('Failed in Delete_FI_Hdr api');
'PA_FORECASTITEM_PVT.Delete_FI_Hdr',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI_Hdr;
| Procedure : Delete_FI_Dtl
| Purpose : To reverse the existing forecast items detail(Requirement)
| when a resource is identified FOR a requirement
| Parameters : p_assignment_id - Input Assignment ID
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Delete_FI_Dtl(
p_resource_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
forecast_item_id_tab PA_FORECAST_GLOB.NumberTabTyp;
Print_message( 'Entering Delete_FI_Dtl');
PA_DEBUG.Init_err_stack( 'PA_FORECASTITEM_PVT.Delete_FI_Dtl');
TmpRevTab.Delete;
TmpUpdTab.Delete;
forecast_item_id_tab.delete;
amount_type_id_tab.delete;
line_num_tab.delete;
resource_type_code_tab.delete;
person_billable_flag_tab.delete;
item_date_tab.delete;
item_UOM_tab.delete;
item_quantity_tab.delete;
expenditure_org_id_tab.delete;
project_org_id_tab.delete;
PJI_SUMMARIZED_FLAG_tab.delete;
CAPACITY_QUANTITY_tab.delete;
OVERCOMMITMENT_QTY_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
JOB_ID_tab.delete;
PROJECT_ID_tab.delete;
RESOURCE_ID_tab.delete;
EXP_ORGANIZATION_ID_tab.delete;
pvdr_acct_curr_code_tab.delete;
pvdr_acct_amount_tab.delete;
rcvr_acct_curr_code_tab.delete;
rcvr_acct_amount_tab.delete;
proj_currency_code_tab.delete;
proj_amount_tab.delete;
denom_currency_code_tab.delete;
denom_amount_tab.delete;
tp_amount_type_tab.delete;
billable_flag_tab.delete;
forecast_summarized_code_tab.delete;
util_summarized_code_tab.delete;
work_type_id_tab.delete;
resource_util_category_id_tab.delete;
org_util_category_id_tab.delete;
resource_util_weighted_tab.delete;
org_util_weighted_tab.delete;
provisional_flag_tab.delete;
reversed_flag_tab.delete;
net_zero_flag_tab.delete;
reduce_capacity_flag_tab.delete;
line_num_reversed_tab.delete;
SELECT dtl.forecast_item_id, dtl.amount_type_id,
dtl.line_num, dtl.resource_type_code,
dtl.person_billable_flag, dtl.item_UOM, dtl.item_date,
dtl.PJI_SUMMARIZED_FLAG,
dtl.CAPACITY_QUANTITY,
dtl.OVERCOMMITMENT_QTY,
dtl.OVERPROVISIONAL_QTY,
dtl.OVER_PROV_CONF_QTY,
dtl.CONFIRMED_QTY,
dtl.PROVISIONAL_QTY,
dtl.JOB_ID,
dtl.PROJECT_ID,
dtl.RESOURCE_ID,
dtl.EXPENDITURE_ORGANIZATION_ID,
dtl.item_quantity, dtl.expenditure_org_id,
dtl.project_org_id, dtl.pvdr_acct_curr_code,
dtl.pvdr_acct_amount, dtl.rcvr_acct_curr_code,
dtl.rcvr_acct_amount, dtl.proj_currency_code,
dtl.proj_amount, dtl.denom_currency_code, dtl.denom_amount,
dtl.tp_amount_type, dtl.billable_flag,
dtl.forecast_summarized_code, dtl.util_summarized_code,
dtl.work_type_id, dtl.resource_util_category_id,
dtl.org_util_category_id, dtl.resource_util_weighted,
dtl.org_util_weighted, dtl.provisional_flag,
dtl.reversed_flag, dtl.net_zero_flag,
dtl.reduce_capacity_flag, dtl.line_num_reversed
BULK COLLECT INTO forecast_item_id_tab,amount_type_id_tab,
line_num_tab, resource_type_code_tab,
person_billable_flag_tab, item_UOM_tab, item_date_tab,
PJI_SUMMARIZED_FLAG_tab,
CAPACITY_QUANTITY_tab,
OVERCOMMITMENT_QTY_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
JOB_ID_tab,
PROJECT_ID_tab,
RESOURCE_ID_tab,
EXP_ORGANIZATION_ID_tab,
item_quantity_tab, expenditure_org_id_tab,
project_org_id_tab, pvdr_acct_curr_code_tab,
pvdr_acct_amount_tab, rcvr_acct_curr_code_tab,
rcvr_acct_amount_tab, proj_currency_code_tab,
proj_amount_tab, denom_currency_code_tab, denom_amount_tab,
tp_amount_type_tab, billable_flag_tab,
forecast_summarized_code_tab, util_summarized_code_tab,
work_type_id_tab, resource_util_category_id_tab,
org_util_category_id_tab, resource_util_weighted_tab,
org_util_weighted_tab, provisional_flag_tab,
reversed_flag_tab, net_zero_flag_tab,
reduce_capacity_flag_tab, line_num_reversed_tab
FROM pa_forecast_item_details dtl, pa_forecast_items hdr
WHERE hdr.resource_id = p_resource_id
AND hdr.delete_flag = 'N'
AND hdr.forecast_item_type = 'U'
AND dtl.forecast_item_id = hdr.forecast_item_id
AND dtl.line_num =
(SELECT max(line_num)
FROM pa_forecast_item_details dtl1
WHERE dtl1.forecast_item_id = hdr.forecast_item_id AND trunc(dtl1.item_date) = trunc(hdr.item_date) ) -- 4918687 SQL ID 14905571
AND trunc(dtl.item_date) = trunc(hdr.item_date) -- 4918687 SQL ID 14905571
order by dtl.item_date, dtl.forecast_item_id ;
Print_message( 'Leaving Delete_FI_Dtl');
TmpRevTab.Delete;
TmpUpdTab.Delete;
'Calling PA_FORECAST_DTLS_PKG.Insert_Rows ');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpRevTab,
lv_return_status,
x_msg_count,
x_msg_data);
'Calling PA_FORECAST_DTLS_PKG.Update_Rows ');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Leaving Delete_FI_Dtl');
print_message('Failed in Delete FI Dtl api');
'PA_FORECASTITEM_PVT.Delete_FI_Dtl',
p_procedure_name => PA_DEBUG.G_Err_Stack);
END Delete_FI_Dtl;
| i) action_flag component of this tab will be updated
| to indicate the following
| a) N : New record - item_date does not exist
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| ii) forecast_item_id component of this tab will be updated
| to hold the forecast_item_id FOR new record. Same will
| be used FOR detail record
| iii) project_org_id,expenditure_org_id,work_type_id,
| person_billable_flag, tp_amount_type : These values
| are required FOR detail record processing. These are
| also updated IN this tab.
|
| x_FIHdrInsTab - Will RETURN all forecast item records that
| are new
| x_FIHdrUpdTab - Will RETURN all forecast item records that
| are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Hdr_Asg(
p_AsgnDtlRec IN PA_FORECAST_GLOB.AsgnDtlRecord,
p_DBHdrTab IN PA_FORECAST_GLOB.FIHdrTabTyp,
p_FIDayTab IN OUT NOCOPY PA_FORECAST_GLOB.FIDayTabTyp, /* 2674619 - Nocopy change */
x_FIHdrInsTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_FIHdrUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_msg_index_out NUMBER;
TmpDayTab.Delete;
TmpInsTab.Delete;
TmpUpdTab.Delete;
ld_resou_startdate_tab.Delete;
ld_resou_enddate_tab.Delete;
l_resou_tab.Delete;
ld_pvdrpa_startdate_tab.Delete;
ld_pvdrpa_enddate_tab.Delete;
lv_pvdrpa_name_tab.Delete;
ld_pvdrgl_startdate_tab.Delete;
ld_pvdrgl_enddate_tab.Delete;
lv_pvdrgl_name_tab.Delete;
ld_rcvrpa_startdate_tab.Delete;
ld_rcvrpa_enddate_tab.Delete;
lv_rcvrpa_name_tab.Delete;
ld_rcvrgl_startdate_tab.Delete;
ld_rcvrgl_enddate_tab.Delete;
lv_rcvrgl_name_tab.Delete;
ld_orgn_startdate_tab.Delete;
ld_orgn_enddate_tab.Delete;
l_orgn_tab.Delete;
l_jobid_tab.Delete;
lv_WeekDateRange_Tab.Delete;
TmpUpdTab(u_in).delete_flag := 'Y';
TmpUpdTab(u_in).delete_flag := 'Y';
TmpHdrRec.Delete_Flag := 'N';
' Del_flag:' || TmpHdrRec.delete_flag ||
' Err_flag:' || TmpHdrRec.error_flag ||
' Prv_flag:' || TmpHdrRec.provisional_flag);
x_FIHdrInsTab.Delete;
x_FIHdrUpdTab.Delete;
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| x_FIDtlInsTab - Will RETURN all forecast item detail records
| that are new
| x_FIDtlUpdTab - Will RETURN all forecast item detail records
| that are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Dtl_Asg(
p_AsgnDtlRec IN PA_FORECAST_GLOB.AsgnDtlRecord,
p_DBDtlTab IN PA_FORECAST_GLOB.FIDtlTabTyp,
p_FIDayTab IN PA_FORECAST_GLOB.FIDayTabTyp,
x_FIDtlInsTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_FIDtlUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_msg_index_out NUMBER;
TmpDayTab.Delete;
TmpInsTab.Delete;
TmpUpdTab.Delete;
x_FIDtlInsTab.Delete;
x_FIDtlUpdTab.Delete;
x_TmpScheduleTab.delete();
x_TmpScheduleTab.delete();
select min(resource_effective_start_date),
max(resource_effective_end_date)
from pa_resources_denorm
where resource_id = p_resource_id;
TmpScheduleTab.delete;
TmpResScheduleTab.delete;
TmpAsgnScheduleTab.delete;
TmpResFIDayTab.delete;
TmpAsgnFIDayTab.delete;
TmpAvlFIDayTab.delete;
TmpDbFIDtlTab.delete;
TmpDbFIHdrTab.delete;
TmpFIDtlInsTab.delete;
TmpFIDtlUpdTab.delete;
TmpFIHdrInsTab.delete;
TmpFIHdrUpdTab.delete;
TmpScheduleTab.delete(i);
TmpScheduleTab.delete(i);
* so delete the records from schedule tab
**/
IF TmpScheduleTab.COUNT > 0 then
--print_message('before refresh3 :'||TmpScheduleTab.first||'-'||
-- TmpScheduleTab.last||'-'||TmpScheduleTab.count);
print_message('deleteing the sch records');
TmpScheduleTab.delete(i);
/** referesh the plsql table after deleteing records **/
refresh_schedule_tab(TmpScheduleTab,TmpScheduleTab);
print_message('deleteing TmpAsgnScheduleTab.delete');
TmpAsgnScheduleTab.delete(i);
TmpFIHdrInsTab.delete; -- Initialize
TmpFIHdrUpdTab.delete; -- Initialize
TmpFIDtlInsTab.delete; -- Initialize
TmpFIDtlUpdTab.delete; -- Initialize
TmpDBFIHdrTab.delete; -- Initialize
TmpDBFIDtlTab.delete; -- Initialize
Print_message( 'Calling PA_FORECAST_HDR_PKG.Insert_Rows');
PA_FORECAST_HDR_PKG.Insert_Rows(TmpFIHdrInsTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_HDR_PKG.Update_Rows');
PA_FORECAST_HDR_PKG.Update_Rows(TmpFIHdrUpdTab, lv_return_status,
x_msg_count, x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Insert_Rows');
PA_FORECAST_DTLS_PKG.Insert_Rows(TmpFIDtlInsTab, lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_FORECAST_DTLS_PKG.Update_Rows');
PA_FORECAST_DTLS_PKG.Update_Rows(TmpFIDtlUpdTab,
lv_return_status,
x_msg_count,
x_msg_data);
Print_message( 'Calling PA_RESOURCE_PVT.update_res_availability');
PA_RESOURCE_PVT.update_res_availability (
p_resource_id => p_resource_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
x_return_status => lv_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
TmpHdrTab.delete;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
FROM pa_forecast_items
WHERE resource_id = p_resource_id
AND forecast_item_type = 'U'
AND delete_flag = 'N'
/* Commented for bug3998166
AND trunc(item_date) BETWEEN trunc(p_start_date) AND
trunc(p_end_date) */
AND item_date BETWEEN trunc(p_start_date) AND
(trunc(p_end_date)+ 0.99999)
order by item_date, forecast_item_id ;
TmpHdrTab(j).delete_flag := delete_flag_tab(j);
TmpDtlTab.delete;
forecast_item_id_tab.delete;
amount_type_id_tab.delete;
line_num_tab.delete;
resource_type_code_tab.delete;
person_billable_flag_tab.delete;
item_date_tab.delete;
item_UOM_tab.delete;
item_quantity_tab.delete;
expenditure_org_id_tab.delete;
project_org_id_tab.delete;
PJI_SUMMARIZED_FLAG_tab.delete;
CAPACITY_QUANTITY_tab.delete;
OVERCOMMITMENT_QTY_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
JOB_ID_tab.delete;
PROJECT_ID_tab.delete;
RESOURCE_ID_tab.delete;
EXP_ORGANIZATION_ID_tab.delete;
pvdr_acct_curr_code_tab.delete;
pvdr_acct_amount_tab.delete;
rcvr_acct_curr_code_tab.delete;
rcvr_acct_amount_tab.delete;
proj_currency_code_tab.delete;
proj_amount_tab.delete;
denom_currency_code_tab.delete;
denom_amount_tab.delete;
tp_amount_type_tab.delete;
billable_flag_tab.delete;
forecast_summarized_code_tab.delete;
util_summarized_code_tab.delete;
work_type_id_tab.delete;
resource_util_category_id_tab.delete;
org_util_category_id_tab.delete;
resource_util_weighted_tab.delete;
org_util_weighted_tab.delete;
provisional_flag_tab.delete;
reversed_flag_tab.delete;
net_zero_flag_tab.delete;
reduce_capacity_flag_tab.delete;
line_num_reversed_tab.delete;
SELECT dtl.forecast_item_id, dtl.amount_type_id,
dtl.line_num, dtl.resource_type_code,
dtl.person_billable_flag, dtl.item_UOM,
dtl.item_date, dtl.item_quantity,
dtl.PJI_SUMMARIZED_FLAG,
dtl.CAPACITY_QUANTITY,
dtl.OVERCOMMITMENT_QTY,
dtl.OVERPROVISIONAL_QTY,
dtl.OVER_PROV_CONF_QTY,
dtl.CONFIRMED_QTY,
dtl.PROVISIONAL_QTY,
dtl.JOB_ID,
dtl.PROJECT_ID,
dtl.RESOURCE_ID,
dtl.EXPENDITURE_ORGANIZATION_ID,
dtl.expenditure_org_id, dtl.project_org_id,
dtl.pvdr_acct_curr_code, dtl.pvdr_acct_amount,
dtl.rcvr_acct_curr_code, dtl.rcvr_acct_amount,
dtl.proj_currency_code, dtl.proj_amount,
dtl.denom_currency_code, dtl.denom_amount,
dtl.tp_amount_type, dtl.billable_flag,
dtl.forecast_summarized_code, dtl.util_summarized_code,
dtl.work_type_id, dtl.resource_util_category_id,
dtl.org_util_category_id, dtl.resource_util_weighted,
dtl.org_util_weighted, dtl.provisional_flag,
dtl.reversed_flag, dtl.net_zero_flag,
dtl.reduce_capacity_flag, dtl.line_num_reversed
BULK COLLECT INTO forecast_item_id_tab,amount_type_id_tab,
line_num_tab, resource_type_code_tab,
person_billable_flag_tab, item_UOM_tab,
item_date_tab, item_quantity_tab,
PJI_SUMMARIZED_FLAG_tab,
CAPACITY_QUANTITY_tab,
OVERCOMMITMENT_QTY_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
JOB_ID_tab,
PROJECT_ID_tab,
RESOURCE_ID_tab,
EXP_ORGANIZATION_ID_tab,
expenditure_org_id_tab, project_org_id_tab,
pvdr_acct_curr_code_tab, pvdr_acct_amount_tab,
rcvr_acct_curr_code_tab, rcvr_acct_amount_tab,
proj_currency_code_tab, proj_amount_tab,
denom_currency_code_tab, denom_amount_tab,
tp_amount_type_tab, billable_flag_tab,
forecast_summarized_code_tab, util_summarized_code_tab,
work_type_id_tab, resource_util_category_id_tab,
org_util_category_id_tab, resource_util_weighted_tab,
org_util_weighted_tab, provisional_flag_tab,
reversed_flag_tab, net_zero_flag_tab,
reduce_capacity_flag_tab, line_num_reversed_tab
FROM pa_forecast_item_details dtl, pa_forecast_items hdr
WHERE hdr.resource_id = p_resource_id
AND hdr.delete_flag = 'N'
AND hdr.forecast_item_type = 'U'
/***Addeded the following condition to fix the bug : 1913377
* when this api called in process ERROR mode it should pick
* only the header records which are marked as error it should not
* pick all the records
**/
AND ( nvl(g_process_mode,'GENERATE') <> 'ERROR'
OR
(hdr.error_flag = 'Y' AND nvl(g_process_mode,'GENERATE') = 'ERROR')
)
/** end of bug fix ***/
AND hdr.item_date BETWEEN trunc(p_start_date) AND
trunc(p_end_date) -- bug 9032134
AND dtl.forecast_item_id = hdr.forecast_item_id
AND dtl.line_num = (
SELECT max(line_num)
FROM pa_forecast_item_details dtl1
WHERE dtl1.forecast_item_id = hdr.forecast_item_id)
order by hdr.resource_id,dtl.item_date, dtl.forecast_item_id ;
| i) action_flag component of this tab will be updated
| to indicate the following
| a) N : New record - item_date does not exist
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| ii) forecast_item_id component of this tab will be updated
| to hold the forecast_item_id FOR new record. Same will
| be used FOR detail record
| iii) project_org_id,expenditure_org_id,work_type_id,
| person_billable_flag, tp_amount_type : These values
| are required FOR detail record processing. These are
| also updated IN this tab.
| p_DBHdrTab - Holds forecast item records which are
| already existing
|
| x_FIHdrInsTab - Will RETURN all forecast item records that
| are new
| x_FIHdrUpdTab - Will RETURN all forecast item records that
| are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Hdr_Res(
p_resource_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_FIDayTab IN OUT NOCOPY PA_FORECAST_GLOB.FIDayTabTyp, /* 2674619 - Nocopy change */
p_DBHdrTab IN PA_FORECAST_GLOB.FIHDRTabTyp,
x_FIHdrInsTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_FIHdrUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIHdrTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_data varchar2(2000); -- 4537865
TmpDayTab.Delete;
TmpInsTab.Delete;
TmpUpdTab.Delete;
ld_resou_startdate_tab.delete;
ld_resou_enddate_tab.delete;
l_resou_tab.delete;
ld_pvdrpa_startdate_tab.delete;
ld_pvdrpa_enddate_tab.delete;
lv_pvdrpa_name_tab.delete;
ld_pvdrgl_startdate_tab.delete;
ld_pvdrgl_enddate_tab.delete;
lv_pvdrgl_name_tab.delete;
ld_rcvrpa_startdate_tab.delete;
ld_rcvrpa_enddate_tab.delete;
lv_rcvrpa_name_tab.delete;
ld_rcvrgl_startdate_tab.delete;
ld_rcvrgl_enddate_tab.delete;
lv_rcvrgl_name_tab.delete;
ld_orgn_startdate_tab.delete;
ld_orgn_enddate_tab.delete;
l_orgn_tab.delete;
l_jobid_tab.delete;
lv_WeekDateRange_Tab.delete;
TmpUpdTab(u_in).delete_flag := 'Y';
TmpUpdTab(u_in).delete_flag := 'Y';
TmpHdrRec.Delete_Flag := 'N';
' Del_flag:' || TmpHdrRec.delete_flag ||
' Err_flag:' || TmpHdrRec.error_flag ||
' Prv_flag:' || TmpHdrRec.provisional_flag);
| b) DN : Delete AND create new -
| item DATE exists but expenditure OU/
| expenditure organization/expenditure type/
| expenditure type class/ borrowed flag has
| changed.
| Existing record is reversed(deleted) AND new
| record is created
| c) RN : Reverse AND create new -
| Quantity has changed.
| IN header : quantity is updated.
| IN detail :
| IF summarized existing line should be reversed
| AND new line created
| IF not summarized existing line should be
| updated to reflect new quantity
| d) C : No change IN header
| Check FOR any changes IN detail record for
| person_billable_flag, provisional_flag,
| work_type OR resource_type
| x_FIDtlInsTab - Will RETURN all forecast item detail records
| that are new
| x_FIDtlUpdTab - Will RETURN all forecast item detail records
| that are modified
| x_return_status -
| x_msg_count -
| x_msg_data -
+----------------------------------------------------------------------*/
PROCEDURE Build_FI_Dtl_Res(
p_resource_id IN NUMBER,
p_DBDtlTab IN PA_FORECAST_GLOB.FIDtlTabTyp,
p_FIDayTab IN PA_FORECAST_GLOB.FIDayTabTyp,
x_FIDtlInsTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_FIDtlUpdTab OUT NOCOPY PA_FORECAST_GLOB.FIDtlTabTyp, /* 2674619 - Nocopy change */
x_return_status OUT NOCOPY VARCHAR2, -- 4537865
x_msg_count OUT NOCOPY NUMBER, -- 4537865
x_msg_data OUT NOCOPY VARCHAR2) IS -- 4537865
l_msg_index_out NUMBER;
TmpDayTab.Delete;
TmpInsTab.Delete;
TmpUpdTab.Delete;
TmpHdrTab.Delete;
TmpHdrTab.delete;
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_organization_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
BULK COLLECT INTO forecast_item_id_tab, forecast_item_type_tab,
project_org_id_tab, expenditure_org_id_tab,
project_organization_id_tab, expenditure_orgn_id_tab,
project_id_tab, project_type_class_tab, person_id_tab,
resource_id_tab, borrowed_flag_tab, assignment_id_tab,
item_date_tab, item_uom_tab, item_quantity_tab,
pvdr_period_set_name_tab, pvdr_pa_period_name_tab,
pvdr_gl_period_name_tab, rcvr_period_set_name_tab,
rcvr_pa_period_name_tab, rcvr_gl_period_name_tab,
global_exp_period_end_date_tab, expenditure_type_tab,
expenditure_type_class_tab, cost_rejection_code_tab,
rev_rejection_code_tab, tp_rejection_code_tab,
burden_rejection_code_tab, other_rejection_code_tab,
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
FROM pa_forecast_items
WHERE assignment_id = p_AsgnDtlRec.assignment_id
AND delete_flag = 'N'
AND error_flag = 'Y'
/* Modified for bug 3998166
AND trunc(item_date) BETWEEN trunc(p_start_date) AND
trunc(p_end_date) */
AND item_date BETWEEN trunc(p_start_date) AND
(trunc(p_end_date)+ 0.99999)
order by item_date, forecast_item_id ;
TmpErrHdrTab(j).delete_flag := delete_flag_tab(j);
TmpHdrTab.delete;
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND p_start_date IS NOT NULL
AND p_end_date IS NOT NULL
AND EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id =
frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag =
DECODE(p_process_mode,'ERROR','Y',
frcst_itms.error_flag)
AND trunc(frcst_itms.item_date) BETWEEN
trunc(p_start_date) AND
trunc(p_end_date))
UNION
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND p_start_date IS NOT NULL
AND p_end_date IS NULL
AND EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id =
frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag =
DECODE (p_process_mode,'ERROR','Y',
frcst_itms.error_flag)
AND trunc(frcst_itms.item_date) >=
trunc(p_start_date) )
UNION
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND p_start_date IS NULL
AND p_end_date IS NOT NULL
AND EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id =
frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag =
DECODE (p_process_mode,'ERROR','Y',
frcst_itms.error_flag)
AND trunc(frcst_itms.item_date) <=
trunc(p_end_date))
UNION
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND p_start_date IS NULL
AND p_end_date IS NULL
AND EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id =
frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag =
DECODE(p_process_mode,'ERROR','Y',
frcst_itms.error_flag))
UNION
SELECT proj_asgn.assignment_id,'GENERATE'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND EXISTS ( SELECT NULL
FROM pa_schedules psch
WHERE proj_asgn.assignment_id =
psch.assignment_id
AND psch.forecast_txn_generated_flag = 'N');
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND ( EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id = frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag = DECODE(p_process_mode,'ERROR','Y',frcst_itms.error_flag)
AND ( (p_start_date IS NOT NULL AND p_end_date IS NOT NULL AND trunc(frcst_itms.item_date) BETWEEN
trunc(p_start_date) AND trunc(p_end_date))
OR ( p_start_date IS NOT NULL AND p_end_date IS NULL AND trunc(frcst_itms.item_date) >= trunc(p_start_date))
OR ( p_start_date IS NULL AND p_end_date IS NOT NULL AND trunc(frcst_itms.item_date) <= trunc(p_end_date))
OR ( p_start_date IS NULL AND p_end_date IS NULL ) )
)
OR EXISTS ( SELECT NULL
FROM pa_schedules psch
WHERE proj_asgn.assignment_id = psch.assignment_id
AND psch.forecast_txn_generated_flag = 'N')
); */
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn
WHERE proj_asgn.expenditure_organization_id = p_orgz_id
AND proj_asgn.template_flag = 'N'
AND proj_asgn.assignment_type = 'OPEN_ASSIGNMENT'
AND ( EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id = frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag = DECODE(p_process_mode,'ERROR','Y',frcst_itms.error_flag)
AND trunc(frcst_itms.item_date)
BETWEEN trunc(NVL(p_start_date,trunc(frcst_itms.item_date)))
AND trunc(NVL(p_end_date,trunc(frcst_itms.item_date)))
)
OR EXISTS ( SELECT NULL
FROM pa_schedules psch
WHERE proj_asgn.assignment_id = psch.assignment_id
AND psch.forecast_txn_generated_flag = 'N')
)
UNION
-- Query assignment_type in ('STAFFED_ASSIGNMENT','STAFFED_ADMIN_ASSIGNMENT') based on pa_resources_denorm.resource_organization_id
SELECT proj_asgn.assignment_id,'ERROR'l_process_mode,
proj_asgn.start_date,proj_asgn.end_date
FROM pa_project_assignments proj_asgn,
pa_resources_denorm prd
WHERE proj_asgn.template_flag = 'N'
AND proj_asgn.assignment_type IN ('STAFFED_ASSIGNMENT','STAFFED_ADMIN_ASSIGNMENT')
AND proj_asgn.resource_id = prd.resource_id
AND prd.resource_organization_id = p_orgz_id
AND proj_asgn.start_date BETWEEN prd.resource_effective_start_date
and prd.resource_effective_end_date
AND prd.schedulable_flag = 'Y'
AND ( EXISTS ( SELECT null
FROM pa_forecast_items frcst_itms
WHERE proj_asgn.assignment_id = frcst_itms.assignment_id
AND frcst_itms.delete_flag = 'N'
AND frcst_itms.error_flag = DECODE(p_process_mode,'ERROR','Y',frcst_itms.error_flag)
AND trunc(frcst_itms.item_date)
BETWEEN trunc(NVL(p_start_date,trunc(frcst_itms.item_date)))
AND trunc(NVL(p_end_date,trunc(frcst_itms.item_date)))
)
OR EXISTS ( SELECT NULL
FROM pa_schedules psch
WHERE proj_asgn.assignment_id = psch.assignment_id
AND psch.forecast_txn_generated_flag = 'N')
);
l_AssignmentIdTab.delete;
l_StartDateTab.delete;
l_EndDateTab.delete;
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
FROM pa_forecast_items frcst_itms
WHERE frcst_itms.expenditure_organization_id = p_orgz_id
AND frcst_itms.error_flag = 'Y'
AND frcst_itms.forecast_item_type = 'U'
AND frcst_itms.delete_flag = 'N'
AND ( trunc(frcst_itms.item_date) BETWEEN
trunc(p_start_date) AND trunc(p_end_date))
AND p_start_date IS NOT NULL
AND p_end_date IS NOT NULL
UNION
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
FROM pa_forecast_items frcst_itms
WHERE frcst_itms.expenditure_organization_id = p_orgz_id
AND frcst_itms.error_flag = 'Y'
AND frcst_itms.forecast_item_type = 'U'
AND frcst_itms.delete_flag = 'N'
AND p_start_date IS NOT NULL
AND p_end_date IS NULL
AND trunc(frcst_itms.item_date) >= trunc(p_start_date)
UNION
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
FROM pa_forecast_items frcst_itms
WHERE frcst_itms.expenditure_organization_id = p_orgz_id
AND frcst_itms.error_flag = 'Y'
AND frcst_itms.forecast_item_type = 'U'
AND frcst_itms.delete_flag = 'N'
AND p_start_date IS NULL
AND p_end_date IS NOT NULL
AND trunc(frcst_itms.item_date) <= trunc(p_end_date)
UNION
SELECT forecast_item_id, forecast_item_type,
project_org_id , expenditure_org_id,
project_organization_id, expenditure_organization_id ,
project_id, project_type_class, person_id ,
resource_id, borrowed_flag, assignment_id,
item_date, item_uom, item_quantity,
pvdr_period_set_name, pvdr_pa_period_name,
pvdr_gl_period_name, rcvr_period_set_name,
rcvr_pa_period_name, rcvr_gl_period_name,
global_exp_period_end_date, expenditure_type,
expenditure_type_class, cost_rejection_code,
rev_rejection_code, tp_rejection_code,
burden_rejection_code, other_rejection_code,
delete_flag, error_flag, provisional_flag,
JOB_ID,
TP_AMOUNT_TYPE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
asgmt_sys_status_code, capacity_quantity,
overcommitment_quantity, availability_quantity,
overcommitment_flag, availability_flag
FROM pa_forecast_items frcst_itms
WHERE frcst_itms.expenditure_organization_id = p_orgz_id
AND frcst_itms.error_flag = 'Y'
AND frcst_itms.forecast_item_type = 'U'
AND frcst_itms.delete_flag = 'N'
AND p_start_date IS NULL
AND p_end_date IS NULL
order by resource_id, item_date, forecast_item_id ;
delete_flag_tab PA_FORECAST_GLOB.VC1TabTyp;
delete_flag_tab, error_flag_tab, provisional_flag_tab,
JOB_ID_tab,
TP_AMOUNT_TYPE_tab,
OVERPROVISIONAL_QTY_tab,
OVER_PROV_CONF_QTY_tab,
CONFIRMED_QTY_tab,
PROVISIONAL_QTY_tab,
asgmt_sys_status_code_tab, capacity_quantity_tab,
overcommitment_quantity_tab, availability_quantity_tab,
overcommitment_flag_tab, availability_flag_tab
LIMIT 200;
l_forecast_item_hdr_tab(j).delete_flag :=
delete_flag_tab(j);
l_forecast_item_hdr_tab.delete;
forecast_item_id_tab.delete;
forecast_item_type_tab.delete;
project_org_id_tab.delete;
expenditure_org_id_tab.delete;
project_organization_id_tab.delete;
expenditure_orgn_id_tab.delete;
project_id_tab.delete;
project_type_class_tab.delete;
person_id_tab.delete;
resource_id_tab.delete;
borrowed_flag_tab.delete;
assignment_id_tab.delete;
item_date_tab.delete;
item_uom_tab.delete;
item_quantity_tab.delete;
pvdr_period_set_name_tab.delete;
pvdr_pa_period_name_tab.delete;
pvdr_gl_period_name_tab.delete;
rcvr_period_set_name_tab.delete;
rcvr_pa_period_name_tab.delete;
rcvr_gl_period_name_tab.delete;
global_exp_period_end_date_tab.delete;
expenditure_type_tab.delete;
expenditure_type_class_tab.delete;
cost_rejection_code_tab.delete;
rev_rejection_code_tab.delete;
tp_rejection_code_tab.delete;
burden_rejection_code_tab.delete;
other_rejection_code_tab.delete;
delete_flag_tab.delete;
error_flag_tab.delete;
provisional_flag_tab.delete;
JOB_ID_tab.delete;
TP_AMOUNT_TYPE_tab.delete;
OVERPROVISIONAL_QTY_tab.delete;
OVER_PROV_CONF_QTY_tab.delete;
CONFIRMED_QTY_tab.delete;
PROVISIONAL_QTY_tab.delete;
asgmt_sys_status_code_tab.delete;
capacity_quantity_tab.delete;
overcommitment_quantity_tab.delete;
availability_quantity_tab.delete;
overcommitment_flag_tab.delete;
availability_flag_tab.delete;
SELECT billable_flag,
rou.resource_effective_start_date,
NVL(rou.resource_effective_end_date,p_item_date)
BULK COLLECT INTO
l_BillableFlagTab,l_StartDateTab,l_EndDateTab
FROM pa_resources_denorm rou
WHERE rou.person_id= p_person_id
ORDER BY rou.resource_effective_start_date;
SELECT utilization_flag,
rou.resource_effective_start_date,
NVL(rou.resource_effective_end_date,p_item_date)
BULK COLLECT INTO
l_UtilFlagTab,l_StartDateTab,l_EndDateTab
FROM pa_resources_denorm rou
WHERE rou.person_id= p_person_id
ORDER BY rou.resource_effective_start_date;
select start_date, end_date
into ld_start_date, ld_end_date
from pa_project_assignments
where assignment_id = p_requirement_source_id;
select pa_forecast_items_s.NEXTVAL
into li_new_forecast_item_id
from dual;
print_message('Start calling PA_FORECAST_HDR_PKG.Insert_Rows');
PA_FORECAST_HDR_PKG.Insert_Rows(
lt_FIHdrInsTab,
x_return_status,
x_msg_count,
x_msg_data);
print_message('End calling PA_FORECAST_HDR_PKG.Insert_Rows');
print_message('Start calling PA_FORECAST_DTLS_PKG.Insert_Rows');
PA_FORECAST_DTLS_PKG.Insert_Rows(
lt_FIDtlInsTab,
x_return_status,
x_msg_count,
x_msg_data);
print_message('End calling PA_FORECAST_DTLS_PKG.Insert_Rows');