[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete_eff_rows |
| |
| TYPE |
| public |
| |
| DESCRIPTION |
| Input Parameters |
| p_validate - Indicate whether to evaluate if the rows to be deleted |
| are under use in any plan in aps or not |
| Output Parameters |
| errbuf - Standard Conc prgm parameter |
| retcode - Standard Conc prgm parameter |
| HISTORY |
| 18-nov-2002 Abhay Satpute Created |
| |
==========================================================================*/
PROCEDURE delete_eff_rows( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_validate IN NUMBER) IS
l_date_cnt INTEGER := 0 ;
SELECT distinct creation_date
FROM gmp_form_eff
ORDER BY creation_date DESC ;
/* order by desc so that data for last extract is NOT deleted */
BEGIN
IF p_validate = 1 THEN
SELECT a2m_dblink, instance_id
INTO at_msc_dblink , l_instance_id
FROM mrp_ap_apps_instances ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Cannot delete rows for the Current Date --> '||cur_date);
delete_data(errbuf, retcode ,cur_date,p_validate, at_msc_dblink , l_instance_id) ;
END delete_eff_rows;
| delete_data |
| |
| TYPE |
| Private |
| |
| DESCRIPTION |
| Input Parameters |
| p_cur_date - creation date for which rows are being deleted |
| p_validate - whether to evaluate use of these rows in aps plans |
| p_dblink - passed in from calling proc to be used to make qry |
| p_instance_id - used in qry to determine rows of the same instance |
| |
| Output Parameters |
| |
| HISTORY |
| 18-nov-2002 Abhay Satpute Created |
| |
==========================================================================*/
PROCEDURE delete_data ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_cur_date IN DATE ,
p_validate IN NUMBER ,
p_dblink IN VARCHAR2,
p_instance_id IN NUMBER )IS
rows_deleted NUMBER := 1 ;
total_rows_deleted NUMBER := 0 ;
statement1 := 'SELECT 1 '
|| ' FROM dual '
|| ' WHERE EXISTS (SELECT 1 from '
|| ' msc_process_effectivity'||p_dblink||' mpe ,gmp_form_eff ge'
|| ' where (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
|| ' and mpe.sr_instance_id = :p_instance_id '
|| ' and mpe.plan_id = -1 '
|| ' and trunc(ge.creation_date) = :p_cur_date )' ;
statement2 := ' SELECT distinct mp.compile_designator '
|| ' from '
|| ' msc_process_effectivity'||p_dblink||' mpe , '
|| ' msc_plans'||p_dblink||' mp , '
|| ' gmp_form_eff ge'
|| ' where mpe.plan_id = mp.plan_id '
|| ' and mpe.plan_id <> -1 '
|| ' and (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
|| ' and mpe.sr_instance_id = :p_instance_id '
|| ' and trunc(ge.creation_date) = :p_cur_date ' ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows Exist in gmp_form_eff table, cannot delete effectivities for the following Plans --> '||v_plan_name);
statement3 := ' SELECT distinct mp.compile_designator '
|| ' from '
|| ' msc_process_effectivity'||p_dblink||' mpe , '
|| ' msc_plans'||p_dblink||' mp , '
|| ' gmp_form_eff ge'
|| ' where mpe.plan_id = mp.plan_id '
|| ' and mpe.plan_id <> -1 '
|| ' and (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
|| ' and mpe.sr_instance_id = :p_instance_id '
|| ' and trunc(ge.creation_date) = :p_cur_date ' ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Effectivities for the following Plans have been deleted --> '||v_plan_name);
WHILE (rows_deleted >0 )
LOOP
Delete from gmp_form_eff
where creation_date = p_cur_date
and rownum < 501 ;
rows_deleted := SQL%ROWCOUNT ;
total_rows_deleted := total_rows_deleted + rows_deleted ;
IF rows_deleted < 500 THEN
EXIT ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of rows Successfully deleted from collection run on '||to_char(p_cur_date,'DD-MON-YYYY HH24:MI:SS')||'= '||total_rows_deleted);
END delete_data;