[Home] [Help]
429: --
430: -- PROCEDURE
431: -- PURGE_FORECAST_ITEMS
432: -- PURPOSE
433: -- This API purges unused forecast item data from the 3 tables pa_forecast_items ,pa_forecast_item_details
434: -- and pa_fi_amount_details
435: --
436: -- Parameter Name In/Out Data Type Null? Default Value Description
437: -- ------------- ------ ---------- ------ ------------- ---------------------------------
482:
483:
484: CURSOR c_get_forecast_item_ids IS
485: SELECT forecast_item_id, forecast_item_type
486: FROM pa_forecast_items
487: WHERE delete_flag='Y';
488:
489: -- Commented because of GSCC Error File.Sql.47
490: -- CURSOR c_table_exists(l_table_name IN VARCHAR2) IS
683: -- Included a direct join between the inner and outer queries
684: -- 5201806 : Added l_fi_type_tbl join too. R type records are deleted from pa_forecast_item_details
685: -- without any conditions. So no need to check for their exostence.
686: FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
687: DELETE FROM pa_forecast_items fi
688: WHERE fi.forecast_item_id = l_fi_tbl(i)
689: AND( (forecast_item_type = 'R')
690: OR (forecast_item_type <> 'R'
691: AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
713: END LOOP;
714: CLOSE c_get_forecast_item_ids;
715:
716: IF p_debug_mode = 'Y' THEN
717: pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_items - ' || l_rows1 ;
718: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
719: pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_item_details - ' || l_rows2;
720: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
721: pa_debug.g_err_stage:= 'No. of rows deleted from pa_fi_amount_details - ' ||l_rows3;
725: -- Write this info into log table
726: PA_PURGE_PUB.INSERT_PURGE_LOG
727: (
728: p_request_id => p_request_id ,
729: p_table_name => 'PA_FORECAST_ITEMS' ,
730: p_rows_deleted => l_rows1 ,
731: x_return_status => x_return_status ,
732: x_msg_count => x_msg_count,
733: x_msg_data => x_msg_data