[Home] [Help]
2235: retcode OUT NOCOPY NUMBER,
2236: p_plan_id IN NUMBER) IS
2237: CURSOR cur_queries IS
2238: SELECT plq.query_Id,pq.query_type
2239: FROM msc_plan_queries plq,
2240: msc_personal_queries pq
2241: WHERE plq.plan_id = p_plan_Id
2242: AND plq.query_id = pq.query_id;
2243: --AND pq.query_type = 9;
2330: retcode OUT NOCOPY NUMBER,
2331: p_plan_id IN NUMBER) IS
2332: CURSOR cur_worklists IS
2333: SELECT plq.query_Id,QUERY_TYPE
2334: FROM msc_plan_queries plq,
2335: msc_personal_queries pq
2336: WHERE plq.plan_id = p_plan_Id
2337: AND plq.query_id = pq.query_id
2338: AND pq.query_type = 10;
3470: l_return_code:=l_return_code||' Error getting file id for file name'|| filename||' '||SQLERRM;
3471: END;
3472:
3473: BEGIN
3474: select count(1) into count_row1 from msc_plan_queries
3475: where plan_id=l_plan_id
3476: and query_id=l_query_id;
3477: EXCEPTION
3478: WHEN NO_DATA_FOUND THEN
3478: WHEN NO_DATA_FOUND THEN
3479: count_row1:=0;
3480: WHEN OTHERS THEN
3481: count_row1:=null;
3482: l_return_code:=l_return_code||' Error getting file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3483: END;
3484: If count_row1=1 then
3485: BEGIN
3486: update msc_plan_queries
3482: l_return_code:=l_return_code||' Error getting file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3483: END;
3484: If count_row1=1 then
3485: BEGIN
3486: update msc_plan_queries
3487: set file_id=NULL,
3488: LAST_UPDATED_BY=fnd_global.user_id,
3489: LAST_UPDATE_DATE =SYSDATE,
3490: LAST_UPDATE_LOGIN=fnd_global.user_id
3492: and query_id=l_query_id;
3493: EXCEPTION
3494: WHEN OTHERS THEN
3495: -- wsh_id:=-123;
3496: l_return_code:=l_return_code||' Error updating file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3497: END;
3498: end if;
3499:
3500: END IF;
3551: l_return_code:=l_return_code||' Error getting file id for file name After file export '|| filename||' '||SQLERRM;
3552: END;
3553: IF wsh_id>0 then
3554: BEGIN
3555: select count(1) into count_row1 from msc_plan_queries
3556: where plan_id=l_plan_id
3557: and query_id=l_query_id;
3558: EXCEPTION
3559: WHEN NO_DATA_FOUND THEN
3559: WHEN NO_DATA_FOUND THEN
3560: count_row1:=0;
3561: WHEN OTHERS THEN
3562: count_row1:=null;
3563: l_return_code:=l_return_code||' Error getting file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3564: END;
3565: If count_row1=1 then
3566: BEGIN
3567: update msc_plan_queries
3563: l_return_code:=l_return_code||' Error getting file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3564: END;
3565: If count_row1=1 then
3566: BEGIN
3567: update msc_plan_queries
3568: set file_id=wsh_id,
3569: LAST_UPDATED_BY=fnd_global.user_id,
3570: LAST_UPDATE_DATE =SYSDATE,
3571: LAST_UPDATE_LOGIN=fnd_global.user_id
3573: and query_id=l_query_id;
3574: EXCEPTION
3575: WHEN OTHERS THEN
3576: wsh_id:=-123;
3577: l_return_code:=l_return_code||' Error updating file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3578: END;
3579: elsif count_row1=0 then
3580: BEGIN
3581: INSERT INTO msc_plan_queries
3577: l_return_code:=l_return_code||' Error updating file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3578: END;
3579: elsif count_row1=0 then
3580: BEGIN
3581: INSERT INTO msc_plan_queries
3582: (PLAN_ID,
3583: QUERY_ID,
3584: LAST_UPDATE_DATE,
3585: LAST_UPDATED_BY ,
3597: wsh_id);
3598: EXCEPTION
3599: WHEN OTHERS THEN
3600: wsh_id:=-123;
3601: l_return_code:=l_return_code||' Error Inserting record in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3602: END;
3603: end if;
3604: end if;
3605: end if;
3618: retcode OUT NOCOPY NUMBER,
3619: p_plan_id IN NUMBER) IS
3620: CURSOR cur_worksheets IS
3621: SELECT plq.query_Id,QUERY_TYPE,pq.query_name
3622: FROM msc_plan_queries plq,
3623: msc_personal_queries pq
3624: WHERE plq.plan_id = p_plan_Id
3625: AND plq.query_id = pq.query_id
3626: AND pq.query_type = 11;