[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*The below cursor will select unassigned time forecast_items whose item date <=purge till date. */
CURSOR Cur_forecast_items IS
SELECT forecast_item_id ,project_id --Added for bug 5870223
FROM pa_forecast_items
WHERE forecast_item_type='U'
AND item_date <= fnd_date.canonical_to_date(p_txn_to_date) /* Bug#2510609 */
ORDER BY project_id; -- Added for bug 5870223
select pa_purge_batches_s.nextval into l_purge_batch_id from dual;
l_forecast_item_id_tab.DELETE;
l_project_id_tab.DELETE; --Added for bug 5870223
/* arpr_log(' Before call to PA_PURGE_UNASGN_FI.Delete_fi'); */
PA_PURGE_UNASGN_FI.Delete_fi(p_forecast_item_id_tab =>l_forecast_item_id_tab,
p_project_id_tab => l_project_id_tab, --Added for bug 5870223
p_archive_flag =>p_archive_flag,
p_purge_batch_id =>l_purge_batch_id,
x_err_stack =>x_err_stack,
x_err_stage =>x_err_stage,
x_err_code =>x_err_code);
Procedure Delete_FI (p_forecast_item_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
p_project_id_tab in PA_PLSQL_DATATYPES.IdTabTyp, --Added for bug 5870223
p_archive_flag in VARCHAR2,
p_purge_batch_id in NUMBER,
x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
l_forecast_item_id Pa_forecast_items.forecast_item_id%TYPE;
l_nos_fi_inserted NUMBER ;
l_nos_fid_inserted NUMBER ;
l_nos_fi_deleted NUMBER ;
l_nos_fid_deleted NUMBER ;
l_nos_fi_amt_inserted NUMBER;
l_nos_fi_amt_deleted NUMBER;
l_nos_fi_inserted :=0;
l_nos_fid_inserted :=0;
l_nos_fi_deleted :=0;
l_nos_fid_deleted :=0;
l_nos_fi_amt_deleted :=0;
l_nos_fi_amt_inserted :=0;
SELECT project_id into l_project_id
from pa_forecast_items
where forecast_item_id=l_forecast_item_id;*/
/* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
IF l_call_commit = 'Y' THEN -- Bug 5870223
IF p_archive_flag='Y' THEN
/* arpr_log('Inserting Records into pa_forecast_items_AR table ') ; */
x_err_stage := 'Inserting Records into pa_forecast_items_AR table for forecast item '||to_char(l_forecast_item_id) ;
INSERT INTO pa_frcst_items_AR
(PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
FORECAST_ITEM_ID,
FORECAST_ITEM_TYPE,
PROJECT_ORG_ID,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ASGMT_SYS_STATUS_CODE,
CAPACITY_QUANTITY,
OVERCOMMITMENT_QUANTITY,
AVAILABILITY_QUANTITY,
OVERCOMMITMENT_FLAG,
AVAILABILITY_FLAG,
TP_AMOUNT_TYPE,
FORECAST_AMT_CALC_FLAG,
COST_TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
REVENUE_TXN_CURRENCY_CODE,
TXN_REVENUE,
TP_TXN_CURRENCY_CODE,
TXN_TRANSFER_PRICE,
PROJECT_CURRENCY_CODE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_RAW_COST,
PROJFUNC_BURDENED_COST,
PROJFUNC_REVENUE,
PROJFUNC_TRANSFER_PRICE,
EXPFUNC_CURRENCY_CODE,
EXPFUNC_RAW_COST,
EXPFUNC_BURDENED_COST,
EXPFUNC_TRANSFER_PRICE,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
JOB_ID)
SELECT p_purge_batch_id,
l_purge_release,
project_id,
Forecast_Item_Id,
Forecast_Item_Type,
Project_Org_Id,
Expenditure_Org_Id,
Expenditure_Organization_Id,
Project_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,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
Asgmt_Sys_Status_Code,
Capacity_Quantity,
Overcommitment_Quantity,
Availability_Quantity,
Overcommitment_Flag,
Availability_Flag,
Tp_Amount_Type,
Forecast_Amt_Calc_Flag,
Cost_Txn_Currency_Code,
Txn_Raw_Cost,
Txn_Burdened_Cost,
Revenue_Txn_Currency_Code,
Txn_Revenue,
Tp_Txn_Currency_Code,
Txn_Transfer_Price,
Project_Currency_Code,
Project_Raw_Cost,
Project_Burdened_Cost,
Project_Revenue,
Project_Transfer_Price,
Projfunc_Currency_Code,
projfunc_Raw_Cost,
Projfunc_Burdened_Cost,
Projfunc_Revenue,
Projfunc_Transfer_Price,
Expfunc_Currency_Code,
Expfunc_Raw_Cost,
Expfunc_Burdened_Cost,
Expfunc_Transfer_Price,
Overprovisional_Qty,
Over_Prov_Conf_Qty,
Confirmed_Qty,
Provisional_Qty,
Job_Id
FROM pa_forecast_items
WHERE forecast_item_id = l_forecast_item_id_tab(K);--l_forecast_item_id; 5870223
/*Increase the value of l_nos_fi_inserted to indicate number of records inserted in forecast_items table.
The value will increase for each loop(forecast item id*/
l_nos_fi_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
/* arpr_log('Inserting Records into pa_forecast_item_DETAILS_AR table ') ; */
x_err_stage := 'Inserting Records into forecast_item_detail table for forecast item '||to_char(l_forecast_item_id) ;
INSERT INTO PA_FRCST_ITEM_DTLS_AR
(PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
FORECAST_ITEM_ID,
AMOUNT_TYPE_ID,
LINE_NUM,
RESOURCE_TYPE_CODE,
PERSON_BILLABLE_FLAG,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
EXPENDITURE_ORG_ID,
PROJECT_ORG_ID,
PVDR_ACCT_CURR_CODE,
PVDR_ACCT_AMOUNT,
RCVR_ACCT_CURR_CODE,
RCVR_ACCT_AMOUNT,
PROJ_CURRENCY_CODE,
PROJ_AMOUNT,
DENOM_CURRENCY_CODE,
DENOM_AMOUNT,
TP_AMOUNT_TYPE,
BILLABLE_FLAG,
FORECAST_SUMMARIZED_CODE,
UTIL_SUMMARIZED_CODE,
WORK_TYPE_ID,
RESOURCE_UTIL_CATEGORY_ID,
ORG_UTIL_CATEGORY_ID,
RESOURCE_UTIL_WEIGHTED,
ORG_UTIL_WEIGHTED,
PROVISIONAL_FLAG,
REVERSED_FLAG,
NET_ZERO_FLAG,
REDUCE_CAPACITY_FLAG,
LINE_NUM_REVERSED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
CAPACITY_QUANTITY,
OVERCOMMITMENT_QTY,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
JOB_ID,
PROJECT_ID,
RESOURCE_ID,
EXPENDITURE_ORGANIZATION_ID,
PJI_SUMMARIZED_FLAG)
SELECT p_purge_batch_id,
l_Purge_Release,
l_Project_Id,
Forecast_Item_Id,
Amount_Type_Id,
Line_Num,
Resource_Type_Code,
Person_Billable_Flag,
Item_Date,
Item_Uom,
Item_Quantity,
Expenditure_Org_Id,
Project_Org_Id,
Pvdr_Acct_Curr_Code,
Pvdr_Acct_Amount,
Rcvr_Acct_Curr_Code,
Rcvr_Acct_Amount,
Proj_Currency_Code,
Proj_Amount,
Denom_Currency_Code,
Denom_Amount,
Tp_Amount_Type,
Billable_Flag,
Forecast_Summarized_Code,
Util_Summarized_Code,
Work_Type_Id,
Resource_Util_Category_Id,
Org_Util_Category_Id,
Resource_Util_Weighted,
Org_Util_Weighted,
Provisional_Flag,
Reversed_Flag,
Net_Zero_Flag,
Reduce_Capacity_Flag,
Line_Num_Reversed,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
CAPACITY_QUANTITY,
OVERCOMMITMENT_QTY,
OVERPROVISIONAL_QTY,
OVER_PROV_CONF_QTY,
CONFIRMED_QTY,
PROVISIONAL_QTY,
JOB_ID,
PROJECT_ID,
RESOURCE_ID,
EXPENDITURE_ORGANIZATION_ID,
PJI_SUMMARIZED_FLAG
FROM PA_forecast_item_details
WHERE forecast_item_id=l_forecast_item_id_tab(K);--l_forecast_item_id; bug 5870223
/*Increase the value of l_nos_fis_inserted to indicate number of records inserted in forecast_items detail table.
The value will increase for each loop(forecast item id*/
l_nos_fid_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
INSERT INTO PA_FI_AMOUNT_DETAILS_AR
(PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
FORECAST_ITEM_ID,
LINE_NUM,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
REVERSED_FLAG,
NET_ZERO_FLAG,
LINE_NUM_REVERSED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COST_TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
REVENUE_TXN_CURRENCY_CODE,
TXN_REVENUE,
TP_TXN_CURRENCY_CODE,
TXN_TRANSFER_PRICE,
PROJECT_CURRENCY_CODE,
PROJECT_COST_RATE_DATE,
PROJECT_COST_RATE_TYPE,
PROJECT_COST_EXCHANGE_RATE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE_RATE_DATE,
PROJECT_REVENUE_RATE_TYPE,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_COST_RATE_DATE,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_COST_EXCHANGE_RATE,
PROJFUNC_RAW_COST,
PROJFUNC_BURDENED_COST,
PROJFUNC_REVENUE,
PROJFUNC_TRANSFER_PRICE,
--PROJFUNC_RATE_DATE,
--PROJFUNC_RATE_TYPE,
--PROJFUNC_EXCHANGE_RATE,
EXPFUNC_CURRENCY_CODE,
EXPFUNC_COST_RATE_DATE,
EXPFUNC_COST_RATE_TYPE,
EXPFUNC_COST_EXCHANGE_RATE,
EXPFUNC_RAW_COST,
EXPFUNC_BURDENED_COST,
EXPFUNC_TP_RATE_DATE,
EXPFUNC_TP_RATE_TYPE,
EXPFUNC_TP_EXCHANGE_RATE,
EXPFUNC_TRANSFER_PRICE)
SELECT p_purge_batch_id,
l_purge_release,
l_project_id,
Forecast_Item_Id,
Line_Num,
Item_Date,
Item_Uom,
Item_Quantity,
Reversed_Flag,
Net_Zero_Flag,
Line_Num_Reversed,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
Cost_Txn_Currency_Code,
Txn_Raw_Cost,
Txn_Burdened_Cost,
Revenue_Txn_Currency_Code,
Txn_Revenue,
Tp_Txn_Currency_Code,
Txn_Transfer_Price,
Project_Currency_Code,
Project_Cost_Rate_Date,
Project_Cost_Rate_Type,
Project_Cost_Exchange_Rate,
Project_Raw_Cost,
Project_Burdened_Cost,
Project_Revenue_Rate_Date,
Project_Revenue_Rate_Type,
Project_Revenue_Exchange_Rate,
Project_Revenue,
Project_Tp_Rate_Date,
Project_Tp_Rate_Type,
Project_Tp_Exchange_Rate,
Project_Transfer_Price,
Projfunc_Currency_Code,
Projfunc_Cost_Rate_Date,
Projfunc_Cost_Rate_Type,
Projfunc_Cost_Exchange_Rate,
Projfunc_Raw_Cost,
Projfunc_Burdened_Cost,
Projfunc_Revenue,
Projfunc_Transfer_Price,
--Projfunc_Rate_Date,
--Projfunc_Rate_Type,
--Projfunc_Exchange_Rate,
Expfunc_Currency_Code,
Expfunc_Cost_Rate_Date,
Expfunc_Cost_Rate_Type,
Expfunc_Cost_Exchange_Rate,
Expfunc_Raw_Cost,
Expfunc_Burdened_Cost,
Expfunc_Tp_Rate_Date,
Expfunc_Tp_Rate_Type,
Expfunc_Tp_Exchange_Rate,
Expfunc_Transfer_Price
FROM PA_FI_AMOUNT_DETAILS Where forecast_item_id=l_forecast_item_id_tab(K); --l_forecast_item_id; Bug 5870223
/*Increase the value of l_nos_fi_amt_inserted to reflct the number of records inserted */
l_nos_fi_amt_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
/*To keep the count of no os records deleted from pa_forecast_items and pa_forecast_item_details, manipulate the
count of l_nos_of fi_deleted and l_nos_fis_deleted. */
/* arpr_log('Deleting Records from pa_fi_amount_details table ') ; */
DELETE PA_FI_AMOUNT_DETAILS
WHERE forecast_item_id = l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
l_nos_fi_amt_deleted := SQL%ROWCOUNT; /* Bug#2510609 */
DELETE PA_FORECAST_ITEM_DETAILS
WHERE forecast_item_id =l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
l_nos_fid_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
DELETE PA_FORECAST_ITEMS
WHERE forecast_item_id=l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
l_nos_fi_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
/*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
The procedure is called once for pa_forecast_items and once for pa_forecast_item_details */
pa_purge.CommitProcess (p_purge_batch_id,
l_project_id,
'PA_FORECAST_ITEMS',
l_nos_fi_inserted,
l_nos_fi_deleted,
x_err_code,
x_err_stack,
x_err_stage
) ;
l_nos_fid_inserted,
l_nos_fid_deleted,
x_err_code,
x_err_stack,
x_err_stage
) ;
l_nos_fi_amt_inserted,
l_nos_fi_amt_deleted,
x_err_code,
x_err_stack,
x_err_stage
) ;
l_forecast_item_id_tab.DELETE;
arpr_log('Error Procedure Name := PA_PURGE_UNASGN_FI.DELETE_FI' );
End delete_fi;
select table_name,
sum(nvl(num_recs_archived,0)) num_recs_archived,
sum(nvl(num_recs_purged,0)) num_recs_purged
from PA_PURGE_PRJ_DETAILS
where purge_batch_id = p_purge_batch_id
group by table_name
order by table_name;
SELECT IMP.Set_Of_Books_ID
INTO l_sob_id
FROM PA_Implementations IMP;
SELECT SUBSTRB(GL.Name, 1, 30)
INTO l_sob_name
FROM GL_Sets_Of_Books GL
WHERE GL.Set_Of_Books_ID = l_sob_id;
SELECT meaning
INTO l_tmp_str
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_01';
SELECT rpad(l_sob_name,30,' ')||lpad(l_tmp_str,75,' ')||sysdate
INTO l_tblock
FROM DUAL;
SELECT meaning
INTO l_tmp_str
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_02';
SELECT lpad(l_tmp_str,66+length(l_tmp_str)/2,' ')
INTO l_tblock
FROM DUAL;
SELECT lpad(meaning,32,' ')
INTO l_tmp_str
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_03';
SELECT lpad(meaning,32,' ')
INTO l_tmp_str
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_04';
SELECT ' '||rpad(meaning,20,' ')
INTO l_tmp_str
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_05';
SELECT rpad(meaning,52,' ')
INTO l_tmp_str2
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_06';
SELECT rpad(meaning,48,' ')
INTO l_tmp_str3
FROM PA_LOOKUPS
WHERE lookup_type = 'UNASSIGNED_PURGE_REPORT'
AND lookup_code = 'PA_R_UNASS_TIME_07';
SELECT l_tmp_str||' '||l_tmp_str2||l_tmp_str3
INTO l_tblock
FROM DUAL;
SELECT ' '||rpad(i.table_name,30,' ')||lpad(i.num_recs_archived,26,' ')||' '||lpad(i.num_recs_purged,24,' ')
INTO
l_tmp_str
FROM DUAL;