[Home] [Help]
2891: -- API name : PA_ADVERTISEMENTS_PURGE
2892: -- Type : Public
2893: -- Pre-reqs : None
2894: -- Function : Main purge procedure for Purging records from Advertisements related tables:
2895: -- PA_ACTION_SETS,
2896: -- PA_ACTION_SET_LINES,
2897: -- PA_ACTION_SET_LINE_COND
2898: -- PA_ACTION_SET_LINE_AUD
2899:
2907: x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2908: x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
2909:
2910: l_assignment_id pa_forecast_items.assignment_id%TYPE;
2911: l_action_set_id pa_action_sets.action_set_id%TYPE;
2912: l_nos_action_sets_inserted NUMBER;
2913: l_nos_set_lines_inserted NUMBER;
2914: l_nos_lines_cond_inserted NUMBER;
2915: l_nos_lines_aud_inserted NUMBER;
2918: l_nos_lines_cond_deleted NUMBER;
2919: l_nos_lines_aud_deleted NUMBER;
2920:
2921: CURSOR Cur_action_sets (x_assignment_id IN pa_project_assignments.assignment_id%TYPE) IS
2922: SELECT action_set_id From Pa_action_sets
2923: WHERE action_set_type_code = 'ADVERTISEMENT'
2924: AND object_type ='OPEN_ASSIGNMENT'
2925: AND object_id=x_assignment_id;
2926:
2991: END IF;
2992:
2993:
2994: IF p_archive_flag='Y' THEN
2995: pa_debug.debug('Inserting Records into PA_ACTION_SETS_AR table ') ;
2996: x_err_stage := 'Inserting Records into P_ACTION_SETS_AR table for assignment id '||to_char(l_assignment_id);
2997:
2998: INSERT INTO PA_ACTION_SETS_AR
2999: (PURGE_BATCH_ID,
2994: IF p_archive_flag='Y' THEN
2995: pa_debug.debug('Inserting Records into PA_ACTION_SETS_AR table ') ;
2996: x_err_stage := 'Inserting Records into P_ACTION_SETS_AR table for assignment id '||to_char(l_assignment_id);
2997:
2998: INSERT INTO PA_ACTION_SETS_AR
2999: (PURGE_BATCH_ID,
3000: PURGE_RELEASE,
3001: PURGE_PROJECT_ID,
3002: ACTION_SET_ID,
3072: Creation_Date,
3073: Last_Update_Date,
3074: Last_Updated_By,
3075: Last_Update_Login
3076: From pa_action_sets where action_set_id=l_action_set_id;
3077:
3078: /*Increase the value of l_nos_action_sets_inserted to indicate number of records inserted in action_sets_ar table.
3079: The value will increase for each loop*/
3080:
3300: WHERE action_set_id=l_action_set_id;
3301:
3302: l_nos_set_lines_deleted := l_nos_set_lines_deleted + SQL%ROWCOUNT;
3303:
3304: pa_debug.debug('Deleting Records from PA_ACTION_SETS table ') ;
3305: x_err_stage := 'Deleting Records from PA_ACTION_SETS for id for lines of action set id'||to_char(l_action_set_id) ;
3306:
3307: DELETE PA_ACTION_SETS
3308: WHERE action_set_id=l_action_set_id;
3301:
3302: l_nos_set_lines_deleted := l_nos_set_lines_deleted + SQL%ROWCOUNT;
3303:
3304: pa_debug.debug('Deleting Records from PA_ACTION_SETS table ') ;
3305: x_err_stage := 'Deleting Records from PA_ACTION_SETS for id for lines of action set id'||to_char(l_action_set_id) ;
3306:
3307: DELETE PA_ACTION_SETS
3308: WHERE action_set_id=l_action_set_id;
3309:
3303:
3304: pa_debug.debug('Deleting Records from PA_ACTION_SETS table ') ;
3305: x_err_stage := 'Deleting Records from PA_ACTION_SETS for id for lines of action set id'||to_char(l_action_set_id) ;
3306:
3307: DELETE PA_ACTION_SETS
3308: WHERE action_set_id=l_action_set_id;
3309:
3310: l_nos_action_sets_deleted := l_nos_action_sets_deleted + SQL%ROWCOUNT;
3311:
3317: END LOOP;
3318:
3319: pa_purge.CommitProcess(p_purge_batch_id,
3320: p_project_id,
3321: 'PA_ACTION_SETS',
3322: l_nos_action_sets_inserted,
3323: l_nos_action_sets_deleted,
3324: x_err_code,
3325: x_err_stack,