[Home] [Help]
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: -- ------------- ------ ---------- ------ ------------- ---------------------------------
438: -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
648: -- 5175803 Performance fix: Included a direct join between the inner and outer queries
649: -- 5201806 : Added l_fi_type_tbl join too. R type records are deleted from pa_forecast_item_details
650: -- without any conditions. So no need to check for their exostence.
651: FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
652: DELETE FROM pa_fi_amount_details fi
653: WHERE fi.forecast_item_id = l_fi_tbl(i)
654: AND( (l_fi_type_tbl(i) = 'R')
655: OR (l_fi_type_tbl(i) <> 'R'
656: AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
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;
722: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
723: END IF;
724:
725: -- Write this info into log table
756:
757: PA_PURGE_PUB.INSERT_PURGE_LOG
758: (
759: p_request_id => p_request_id ,
760: p_table_name => 'PA_FI_AMOUNT_DETAILS',
761: p_rows_deleted => l_rows3,
762: x_return_status => x_return_status ,
763: x_msg_count => x_msg_count,
764: x_msg_data => x_msg_data
773: RAISE FND_API.G_EXC_ERROR;
774: END IF;
775:
776: IF p_debug_mode = 'Y' THEN
777: pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_FI_AMOUNT_DETAILS';
778: pa_debug.write('PA_PURGE_PUB',pa_debug.g_err_stage,l_debug_level3);
779: pa_debug.g_err_stage:= 'Exiting PURGE_FORECAST_ITEMS';
780: pa_debug.write('PA_PURGE_PUB','PA_PURGE_PUB.PURGE_FORECAST_ITEMS :' || pa_debug.g_err_stage,l_debug_level3);
781: pa_debug.reset_curr_function;