[Home] [Help]
2: /* $Header: MSCLWIPB.pls 120.21.12020000.5 2013/02/06 10:04:49 swundapa ship $ */
3:
4: -- v_sub_str VARCHAR2(4000):=NULL;
5: -- c_count NUMBER:= 0;
6: -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
7:
8: -- G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
9: -- To collect SRP Data when this profile is set to Yes neds to be deleted
10: -- v_is_cont_refresh BOOLEAN; -- 2 be changed
69: lv_retcode NUMBER;
70:
71: BEGIN
72:
73: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
74: lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
75: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
76: ELSE
77: lv_tbl:= 'MSC_JOB_OP_RES_INSTANCES';
97: ||' AND ms.PLAN_ID= -1'
98: ||' AND ms.SR_INSTANCE_ID= resi.SR_INSTANCE_ID'
99: ||' AND ms.DISPOSITION_ID= resi.WIP_ENTITY_ID'
100: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
101: ||' AND resi.DELETED_FLAG= '||MSC_UTIL.SYS_YES
102: ||' AND t1.sr_inventory_item_id (+) = resi.equipment_item_id '
103: ||' AND t1.SR_INSTANCE_ID (+) = resi.SR_INSTANCE_ID ';
104:
105:
165: ||' AND ms.PLAN_ID= -1'
166: ||' AND ms.SR_INSTANCE_ID= resi.SR_INSTANCE_ID'
167: ||' AND ms.DISPOSITION_ID= resi.WIP_ENTITY_ID'
168: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
169: ||' AND resi.DELETED_FLAG= '||MSC_UTIL.SYS_NO
170: ||' AND t1.sr_inventory_item_id (+) = resi.equipment_item_id '
171: ||' AND t1.SR_INSTANCE_ID (+) = resi.SR_INSTANCE_ID ';
172:
173: -- ========= Prepare SQL Statement for INSERT ==========
208: ||' :v_current_user,'
209: ||' :v_current_date,'
210: ||' :v_current_user)';
211: --log_debug(lv_cursor_stmt);
212: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
213: OPEN cgen FOR lv_cursor_stmt;
214: IF (cgen%ISOPEN) THEN
215:
216: LOOP
289: WHEN OTHERS THEN
290:
291: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
292:
293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
294: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
295: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
296: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
294: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
295: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
296: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
298:
299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
300: RAISE;
301:
295: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
296: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
298:
299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
300: RAISE;
301:
302: ELSE
303:
300: RAISE;
301:
302: ELSE
303:
304: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
305:
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
307: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
302: ELSE
303:
304: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
305:
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
307: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
309: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
307: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
309: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
311:
312: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
313: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
314: FND_MESSAGE.SET_TOKEN('VALUE',
313: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
314: FND_MESSAGE.SET_TOKEN('VALUE',
315: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
316: MSC_CL_COLLECTION.v_instance_id));
317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
318:
319: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
320: END IF;
321:
315: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
316: MSC_CL_COLLECTION.v_instance_id));
317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
318:
319: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
320: END IF;
321:
322: END;
323: END LOOP;
321:
322: END;
323: END LOOP;
324:
325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OP_RES_INSTANCES = '|| to_char(total_count));
326: END IF; /* cgen%ISOPEN */
327:
328: CLOSE cgen;
329:
330: COMMIT;
331:
332: BEGIN
333:
334: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
335:
336: lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
337:
338: lv_sql_stmt:=
335:
336: lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
337:
338: lv_sql_stmt:=
339: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
340: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RES_INSTANCES'
341: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
342: ||' AND plan_id = -1 '
343: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
336: lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
337:
338: lv_sql_stmt:=
339: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
340: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RES_INSTANCES'
341: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
342: ||' AND plan_id = -1 '
343: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
344:
339: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
340: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RES_INSTANCES'
341: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
342: ||' AND plan_id = -1 '
343: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
344:
345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
346: EXECUTE IMMEDIATE lv_sql_stmt;
347:
341: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
342: ||' AND plan_id = -1 '
343: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
344:
345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
346: EXECUTE IMMEDIATE lv_sql_stmt;
347:
348: COMMIT;
349:
351:
352: EXCEPTION
353: WHEN OTHERS THEN
354:
355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
356: RAISE;
357: END;
358:
359: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
356: RAISE;
357: END;
358:
359: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
360: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
361: lv_retcode,
362: 'MSC_JOB_OP_RES_INSTANCES',
363: MSC_CL_COLLECTION.v_instance_code,
360: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
361: lv_retcode,
362: 'MSC_JOB_OP_RES_INSTANCES',
363: MSC_CL_COLLECTION.v_instance_code,
364: MSC_UTIL.G_WARNING
365: );
366:
367: IF lv_retcode = MSC_UTIL.G_ERROR THEN
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
363: MSC_CL_COLLECTION.v_instance_code,
364: MSC_UTIL.G_WARNING
365: );
366:
367: IF lv_retcode = MSC_UTIL.G_ERROR THEN
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
369: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
370: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
371: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
364: MSC_UTIL.G_WARNING
365: );
366:
367: IF lv_retcode = MSC_UTIL.G_ERROR THEN
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
369: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
370: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
371: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
372: END IF;
366:
367: IF lv_retcode = MSC_UTIL.G_ERROR THEN
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
369: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
370: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
371: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
372: END IF;
373:
374: END IF;
367: IF lv_retcode = MSC_UTIL.G_ERROR THEN
368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
369: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
370: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
371: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
372: END IF;
373:
374: END IF;
375:
377: WHEN OTHERS THEN
378: IF cgen%ISOPEN THEN
379: CLOSE cgen;
380: END IF;
381: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
384: END IF;
385: IF lv_sql_stmt IS NOT NULL THEN
379: CLOSE cgen;
380: END IF;
381: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
382: IF lv_cursor_stmt IS NOT NULL THEN
383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
385: IF lv_sql_stmt IS NOT NULL THEN
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
387: END IF;
382: IF lv_cursor_stmt IS NOT NULL THEN
383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
384: END IF;
385: IF lv_sql_stmt IS NOT NULL THEN
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
389: RAISE;
390: END LOAD_JOB_OP_RES_INSTANCE;
384: END IF;
385: IF lv_sql_stmt IS NOT NULL THEN
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
387: END IF;
388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
389: RAISE;
390: END LOAD_JOB_OP_RES_INSTANCE;
391:
392: --================================================================
433: lv_actual_end_date DATE;
434: lv_Operation_desc VARCHAR2(240) ;
435: BEGIN
436:
437: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
438: lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
439: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
440: ELSE
441: lv_tbl:= 'MSC_JOB_OPERATIONS';
455: ||' AND ms.PLAN_ID= -1'
456: ||' AND ms.SR_INSTANCE_ID= opr.SR_INSTANCE_ID'
457: ||' AND ms.DISPOSITION_ID= opr.WIP_ENTITY_ID'
458: ||' AND ms.ORDER_TYPE IN ( 3, 7,70)'
459: ||' AND opr.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
460:
461: OPEN cgen FOR lv_cursor_stmt;
462:
463: IF (cgen%ISOPEN) THEN
520: ||' AND ms.PLAN_ID= -1'
521: ||' AND ms.SR_INSTANCE_ID= opr.SR_INSTANCE_ID'
522: ||' AND ms.DISPOSITION_ID= opr.WIP_ENTITY_ID'
523: ||' AND ms.ORDER_TYPE IN ( 3, 7,70)'
524: ||' AND opr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
525:
526:
527: -- ========= Prepare SQL Statement for INSERT ==========
528: lv_sql_stmt:=
698: WHEN OTHERS THEN
699:
700: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
701:
702: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
703: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
704: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
705: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
706: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
702: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
703: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
704: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
705: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
706: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
707:
708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
709: RAISE;
710:
704: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
705: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
706: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
707:
708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
709: RAISE;
710:
711: ELSE
712:
709: RAISE;
710:
711: ELSE
712:
713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
714:
715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
711: ELSE
712:
713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
714:
715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
718: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
718: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
720:
721: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
722: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
723: FND_MESSAGE.SET_TOKEN('VALUE',
722: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
723: FND_MESSAGE.SET_TOKEN('VALUE',
724: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
725: MSC_CL_COLLECTION.v_instance_id));
726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
727:
728: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
729: END IF;
730:
724: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
725: MSC_CL_COLLECTION.v_instance_id));
726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
727:
728: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
729: END IF;
730:
731: END;
732:
731: END;
732:
733: END LOOP;
734:
735: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OPERATIONS = '|| total_count);
736: END IF;
737:
738: CLOSE cgen;
739:
740: COMMIT;
741:
742: BEGIN
743:
744: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
745:
746: lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
747:
748: lv_sql_stmt:=
745:
746: lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
747:
748: lv_sql_stmt:=
749: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
750: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATIONS'
751: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
752: ||' AND plan_id = -1 '
753: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
746: lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
747:
748: lv_sql_stmt:=
749: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
750: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATIONS'
751: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
752: ||' AND plan_id = -1 '
753: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
754:
749: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
750: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATIONS'
751: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
752: ||' AND plan_id = -1 '
753: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
754:
755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
756: EXECUTE IMMEDIATE lv_sql_stmt;
757:
751: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
752: ||' AND plan_id = -1 '
753: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
754:
755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
756: EXECUTE IMMEDIATE lv_sql_stmt;
757:
758: COMMIT;
759:
761:
762: EXCEPTION
763: WHEN OTHERS THEN
764:
765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
766: RAISE;
767: END;
768:
769: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
766: RAISE;
767: END;
768:
769: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
770: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
771: lv_retcode,
772: 'MSC_JOB_OPERATIONS',
773: MSC_CL_COLLECTION.v_instance_code,
770: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
771: lv_retcode,
772: 'MSC_JOB_OPERATIONS',
773: MSC_CL_COLLECTION.v_instance_code,
774: MSC_UTIL.G_WARNING
775: );
776:
777: IF lv_retcode = MSC_UTIL.G_ERROR THEN
778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
773: MSC_CL_COLLECTION.v_instance_code,
774: MSC_UTIL.G_WARNING
775: );
776:
777: IF lv_retcode = MSC_UTIL.G_ERROR THEN
778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
779: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
780: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
781: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
774: MSC_UTIL.G_WARNING
775: );
776:
777: IF lv_retcode = MSC_UTIL.G_ERROR THEN
778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
779: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
780: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
781: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
782: END IF;
776:
777: IF lv_retcode = MSC_UTIL.G_ERROR THEN
778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
779: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
780: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
781: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
782: END IF;
783:
784: END IF;
777: IF lv_retcode = MSC_UTIL.G_ERROR THEN
778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
779: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
780: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
781: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
782: END IF;
783:
784: END IF;
785:
786: EXCEPTION
787: WHEN OTHERS THEN
788: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
789:
790: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
792: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
793: END IF;
794: IF lv_sql_stmt IS NOT NULL THEN
788: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
789:
790: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
791: IF lv_cursor_stmt IS NOT NULL THEN
792: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
794: IF lv_sql_stmt IS NOT NULL THEN
795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
796: END IF;
791: IF lv_cursor_stmt IS NOT NULL THEN
792: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
793: END IF;
794: IF lv_sql_stmt IS NOT NULL THEN
795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
798: RAISE;
799: END LOAD_JOB_OP;
793: END IF;
794: IF lv_sql_stmt IS NOT NULL THEN
795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
796: END IF;
797: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
798: RAISE;
799: END LOAD_JOB_OP;
800:
801: --===============================================================
843: lv_retcode NUMBER;
844:
845: BEGIN
846:
847: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
848: lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
849: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
850: ELSE
851: lv_tbl:= 'MSC_JOB_OP_RESOURCES';
866: ||' AND ms.PLAN_ID= -1'
867: ||' AND ms.SR_INSTANCE_ID= res.SR_INSTANCE_ID'
868: ||' AND ms.DISPOSITION_ID= res.WIP_ENTITY_ID'
869: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
870: ||' AND res.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
871:
872: OPEN cgen FOR lv_cursor_stmt;
873:
874: IF (cgen%ISOPEN) THEN
936: ||' AND ms.PLAN_ID= -1'
937: ||' AND ms.SR_INSTANCE_ID= res.SR_INSTANCE_ID'
938: ||' AND ms.DISPOSITION_ID= res.WIP_ENTITY_ID'
939: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
940: ||' AND res.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
941:
942:
943: -- ========= Prepare SQL Statement for INSERT ==========
944: lv_sql_stmt:=
1135: WHEN OTHERS THEN
1136:
1137: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1138:
1139: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1140: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1141: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1142: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1143: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1139: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1140: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1141: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1142: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1143: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1144:
1145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1146: RAISE;
1147:
1141: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1142: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1143: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1144:
1145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1146: RAISE;
1147:
1148: ELSE
1149:
1146: RAISE;
1147:
1148: ELSE
1149:
1150: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1151:
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1153: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1154: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1148: ELSE
1149:
1150: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1151:
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1153: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1154: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1155: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1153: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1154: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1155: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1157:
1158: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1159: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1160: FND_MESSAGE.SET_TOKEN('VALUE',
1159: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1160: FND_MESSAGE.SET_TOKEN('VALUE',
1161: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1162: MSC_CL_COLLECTION.v_instance_id));
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1164:
1165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1166: END IF;
1167:
1161: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1162: MSC_CL_COLLECTION.v_instance_id));
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1164:
1165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1166: END IF;
1167:
1168: END;
1169: END LOOP;
1167:
1168: END;
1169: END LOOP;
1170:
1171: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OP_RESOURCES = '|| total_count);
1172: END IF;
1173:
1174: CLOSE cgen;
1175:
1176: COMMIT;
1177:
1178: BEGIN
1179:
1180: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1181:
1182: lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1183:
1184: lv_sql_stmt:=
1181:
1182: lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1183:
1184: lv_sql_stmt:=
1185: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1186: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RESOURCES'
1187: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1188: ||' AND plan_id = -1 '
1189: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1182: lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1183:
1184: lv_sql_stmt:=
1185: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1186: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RESOURCES'
1187: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1188: ||' AND plan_id = -1 '
1189: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1190:
1185: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1186: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OP_RESOURCES'
1187: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1188: ||' AND plan_id = -1 '
1189: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1190:
1191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1192: EXECUTE IMMEDIATE lv_sql_stmt;
1193:
1187: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1188: ||' AND plan_id = -1 '
1189: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1190:
1191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1192: EXECUTE IMMEDIATE lv_sql_stmt;
1193:
1194: COMMIT;
1195:
1197:
1198: EXCEPTION
1199: WHEN OTHERS THEN
1200:
1201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1202: RAISE;
1203: END;
1204:
1205: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1202: RAISE;
1203: END;
1204:
1205: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1206: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1207: lv_retcode,
1208: 'MSC_JOB_OP_RESOURCES',
1209: MSC_CL_COLLECTION.v_instance_code,
1206: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1207: lv_retcode,
1208: 'MSC_JOB_OP_RESOURCES',
1209: MSC_CL_COLLECTION.v_instance_code,
1210: MSC_UTIL.G_WARNING
1211: );
1212:
1213: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1209: MSC_CL_COLLECTION.v_instance_code,
1210: MSC_UTIL.G_WARNING
1211: );
1212:
1213: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1215: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1216: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1217: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1210: MSC_UTIL.G_WARNING
1211: );
1212:
1213: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1215: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1216: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1217: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1218: END IF;
1212:
1213: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1215: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1216: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1217: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1218: END IF;
1219:
1220: END IF;
1213: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1215: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1216: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1217: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1218: END IF;
1219:
1220: END IF;
1221:
1222: EXCEPTION
1223: WHEN OTHERS THEN
1224: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1225:
1226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1229: END IF;
1230: IF lv_sql_stmt IS NOT NULL THEN
1224: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1225:
1226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1227: IF lv_cursor_stmt IS NOT NULL THEN
1228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1230: IF lv_sql_stmt IS NOT NULL THEN
1231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1232: END IF;
1227: IF lv_cursor_stmt IS NOT NULL THEN
1228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1229: END IF;
1230: IF lv_sql_stmt IS NOT NULL THEN
1231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1234: RAISE;
1235: END LOAD_JOB_OP_RES;
1229: END IF;
1230: IF lv_sql_stmt IS NOT NULL THEN
1231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1232: END IF;
1233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1234: RAISE;
1235: END LOAD_JOB_OP_RES;
1236:
1237: --=========================================================================
1281: lv_retcode NUMBER;
1282:
1283: BEGIN
1284:
1285: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1286: lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1287: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1288: ELSE
1289: lv_tbl:= 'MSC_JOB_REQUIREMENT_OPS';
1310: ||' AND ms.PLAN_ID= -1'
1311: ||' AND ms.SR_INSTANCE_ID= req.SR_INSTANCE_ID'
1312: ||' AND ms.DISPOSITION_ID= req.WIP_ENTITY_ID'
1313: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
1314: ||' AND req.DELETED_FLAG= '||MSC_UTIL.SYS_YES
1315: ||' AND cmp_itm.SR_INVENTORY_ITEM_ID = req.COMPONENT_ITEM_ID'
1316: ||' AND pri_cmp.SR_INVENTORY_ITEM_ID = req.PRIMARY_COMPONENT_ID'
1317: ||' AND src_ptm.SR_INVENTORY_ITEM_ID(+) = req.SOURCE_PHANTOM_ID'
1318: ||' AND cmp_itm.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1393: ||' AND ms.PLAN_ID= -1'
1394: ||' AND ms.SR_INSTANCE_ID= req.SR_INSTANCE_ID'
1395: ||' AND ms.DISPOSITION_ID= req.WIP_ENTITY_ID'
1396: ||' AND ms.ORDER_TYPE IN ( 3, 7)'
1397: ||' AND req.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1398: ||' AND cmp_itm.SR_INVENTORY_ITEM_ID = req.COMPONENT_ITEM_ID'
1399: ||' AND pri_cmp.SR_INVENTORY_ITEM_ID = req.PRIMARY_COMPONENT_ID'
1400: ||' AND src_ptm.SR_INVENTORY_ITEM_ID(+) = req.SOURCE_PHANTOM_ID'
1401: ||' AND cmp_itm.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1583: WHEN OTHERS THEN
1584:
1585: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1586:
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1588: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1589: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1590: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1588: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1589: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1590: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1592:
1593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1594: RAISE;
1595:
1589: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1590: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1592:
1593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1594: RAISE;
1595:
1596: ELSE
1597:
1594: RAISE;
1595:
1596: ELSE
1597:
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1599:
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1596: ELSE
1597:
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1599:
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1603: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1603: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1605:
1606: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1607: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1608: FND_MESSAGE.SET_TOKEN('VALUE',
1607: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1608: FND_MESSAGE.SET_TOKEN('VALUE',
1609: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1610: MSC_CL_COLLECTION.v_instance_id));
1611: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1612:
1613: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1614: END IF;
1615:
1609: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1610: MSC_CL_COLLECTION.v_instance_id));
1611: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1612:
1613: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1614: END IF;
1615:
1616: END;
1617:
1625: COMMIT;
1626:
1627: BEGIN
1628:
1629: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1630:
1631: lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1632:
1633: lv_sql_stmt:=
1630:
1631: lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1632:
1633: lv_sql_stmt:=
1634: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1635: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_REQUIREMENT_OPS'
1636: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1637: ||' AND plan_id = -1 '
1638: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1631: lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1632:
1633: lv_sql_stmt:=
1634: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1635: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_REQUIREMENT_OPS'
1636: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1637: ||' AND plan_id = -1 '
1638: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1639:
1634: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1635: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_REQUIREMENT_OPS'
1636: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1637: ||' AND plan_id = -1 '
1638: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1639:
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1641: EXECUTE IMMEDIATE lv_sql_stmt;
1642:
1636: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1637: ||' AND plan_id = -1 '
1638: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1639:
1640: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1641: EXECUTE IMMEDIATE lv_sql_stmt;
1642:
1643: COMMIT;
1644:
1646:
1647: EXCEPTION
1648: WHEN OTHERS THEN
1649:
1650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1651: RAISE;
1652: END;
1653:
1654: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1651: RAISE;
1652: END;
1653:
1654: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1655: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1656: lv_retcode,
1657: 'MSC_JOB_REQUIREMENT_OPS',
1658: MSC_CL_COLLECTION.v_instance_code,
1655: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1656: lv_retcode,
1657: 'MSC_JOB_REQUIREMENT_OPS',
1658: MSC_CL_COLLECTION.v_instance_code,
1659: MSC_UTIL.G_WARNING
1660: );
1661:
1662: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1663: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1658: MSC_CL_COLLECTION.v_instance_code,
1659: MSC_UTIL.G_WARNING
1660: );
1661:
1662: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1663: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1664: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1665: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1666: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1659: MSC_UTIL.G_WARNING
1660: );
1661:
1662: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1663: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1664: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1665: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1666: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1667: END IF;
1661:
1662: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1663: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1664: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1665: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1666: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1667: END IF;
1668:
1669: END IF;
1662: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1663: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1664: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1665: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1666: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1667: END IF;
1668:
1669: END IF;
1670:
1671: EXCEPTION
1672: WHEN OTHERS THEN
1673: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1674:
1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1678: END IF;
1679: IF lv_sql_stmt IS NOT NULL THEN
1673: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1674:
1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1676: IF lv_cursor_stmt IS NOT NULL THEN
1677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1679: IF lv_sql_stmt IS NOT NULL THEN
1680: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1681: END IF;
1676: IF lv_cursor_stmt IS NOT NULL THEN
1677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1678: END IF;
1679: IF lv_sql_stmt IS NOT NULL THEN
1680: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1683: RAISE;
1684: END LOAD_JOB_REQ_OP;
1678: END IF;
1679: IF lv_sql_stmt IS NOT NULL THEN
1680: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
1681: END IF;
1682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1683: RAISE;
1684: END LOAD_JOB_REQ_OP;
1685: --=================================================================
1686:
1724: lv_retcode NUMBER;
1725:
1726: BEGIN
1727:
1728: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1729: lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
1730: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1731: ELSE
1732: lv_tbl:= 'MSC_JOB_OPERATION_NETWORKS';
1748: ||' AND ms.PLAN_ID= -1'
1749: ||' AND ms.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1750: ||' AND ms.DISPOSITION_ID= nwk.WIP_ENTITY_ID'
1751: ||' AND ms.ORDER_TYPE IN ( 3, 7, 70)' /* ds change change: 70 eam supply */
1752: ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_YES
1753: ||' AND nvl(nwk.DEPENDENCY_TYPE,4) <> 3 '; /* ds change change */
1754:
1755: /* for eam parent child dependencies */
1756: lv_eam_pc_stmt:=
1769: ||' AND ms_to.PLAN_ID= -1'
1770: ||' AND ms_to.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1771: ||' AND ms_to.DISPOSITION_ID= nwk.TO_WIP_ENTITY_ID'
1772: ||' AND ms_to.ORDER_TYPE = 70 '
1773: ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
1774: --||' AND nwk.DEPENDENCY_TYPE = 3 ;
1775:
1776: OPEN cgen FOR lv_cursor_stmt;
1777:
1874: ||' AND ms2.PLAN_ID (+) = -1' /* ds change change */
1875: ||' AND ms2.SR_INSTANCE_ID (+)= nwk.SR_INSTANCE_ID'
1876: ||' AND ms2.DISPOSITION_ID(+)= nwk.TOP_WIP_ENTITY_ID'
1877: ||' AND ms2.ORDER_TYPE(+) = 70'
1878: ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1879: ||' AND nwk.FROM_ITEM_ID = t1.SR_INVENTORY_ITEM_ID(+)' /* ds change change */
1880: ||' AND nwk.sr_instance_id = t1.sr_instance_id(+) '; /* ds change change */
1881:
1882:
2036: WHEN OTHERS THEN
2037:
2038: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2039:
2040: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2041: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2042: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2043: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2040: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2041: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2042: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2043: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2045:
2046: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2047: RAISE;
2048:
2042: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2043: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2045:
2046: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2047: RAISE;
2048:
2049: ELSE
2050:
2047: RAISE;
2048:
2049: ELSE
2050:
2051: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2052:
2053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2054: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2055: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2049: ELSE
2050:
2051: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2052:
2053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2054: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2055: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2056: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2054: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2055: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2056: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2058:
2059: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2060: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2061: FND_MESSAGE.SET_TOKEN('VALUE',
2060: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2061: FND_MESSAGE.SET_TOKEN('VALUE',
2062: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
2063: MSC_CL_COLLECTION.v_instance_id));
2064: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2065:
2066: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2067: END IF;
2068:
2062: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
2063: MSC_CL_COLLECTION.v_instance_id));
2064: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2065:
2066: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2067: END IF;
2068:
2069: END;
2070:
2068:
2069: END;
2070:
2071: END LOOP;
2072: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OPERATION_NETWORKS = '|| total_count);
2073: END IF;
2074:
2075: CLOSE cgen;
2076:
2076:
2077: COMMIT;
2078:
2079: BEGIN
2080: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2081:
2082: lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
2083:
2084: lv_sql_stmt:=
2081:
2082: lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
2083:
2084: lv_sql_stmt:=
2085: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2086: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATION_NETWORKS'
2087: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2088: ||' AND plan_id = -1 '
2089: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2082: lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
2083:
2084: lv_sql_stmt:=
2085: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2086: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATION_NETWORKS'
2087: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2088: ||' AND plan_id = -1 '
2089: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2090:
2085: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2086: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_JOB_OPERATION_NETWORKS'
2087: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2088: ||' AND plan_id = -1 '
2089: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2090:
2091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2092: EXECUTE IMMEDIATE lv_sql_stmt;
2093:
2087: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2088: ||' AND plan_id = -1 '
2089: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2090:
2091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2092: EXECUTE IMMEDIATE lv_sql_stmt;
2093:
2094: COMMIT;
2095:
2097:
2098: EXCEPTION
2099: WHEN OTHERS THEN
2100:
2101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2102: RAISE;
2103: END;
2104:
2105: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2102: RAISE;
2103: END;
2104:
2105: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2106: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2107: lv_retcode,
2108: 'MSC_JOB_OPERATION_NETWORKS',
2109: MSC_CL_COLLECTION.v_instance_code,
2106: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2107: lv_retcode,
2108: 'MSC_JOB_OPERATION_NETWORKS',
2109: MSC_CL_COLLECTION.v_instance_code,
2110: MSC_UTIL.G_WARNING
2111: );
2112:
2113: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2109: MSC_CL_COLLECTION.v_instance_code,
2110: MSC_UTIL.G_WARNING
2111: );
2112:
2113: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2115: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2116: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2117: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2110: MSC_UTIL.G_WARNING
2111: );
2112:
2113: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2115: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2116: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2117: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2118: END IF;
2112:
2113: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2115: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2116: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2117: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2118: END IF;
2119:
2120: END IF;
2113: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2114: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2115: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2116: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2117: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2118: END IF;
2119:
2120: END IF;
2121:
2122: EXCEPTION
2123: WHEN OTHERS THEN
2124: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
2125:
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2129: END IF;
2130: IF lv_sql_stmt IS NOT NULL THEN
2124: IF cgen%ISOPEN THEN CLOSE cgen; END IF;
2125:
2126: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2127: IF lv_cursor_stmt IS NOT NULL THEN
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2130: IF lv_sql_stmt IS NOT NULL THEN
2131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2132: END IF;
2127: IF lv_cursor_stmt IS NOT NULL THEN
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2129: END IF;
2130: IF lv_sql_stmt IS NOT NULL THEN
2131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2134: RAISE;
2135: END LOAD_JOB_OP_NWK;
2129: END IF;
2130: IF lv_sql_stmt IS NOT NULL THEN
2131: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2132: END IF;
2133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2134: RAISE;
2135: END LOAD_JOB_OP_NWK;
2136:
2137: --=========================================================================
2156: msrir.SERIAL_NUMBER,
2157: msrir.RES_INSTANCE_ID
2158: FROM MSC_ST_RESOURCE_INSTANCE_REQS msrir
2159: WHERE msrir.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2160: AND msrir.DELETED_FLAG= MSC_UTIL.SYS_YES;
2161:
2162: lv_DEPARTMENT_ID NUMBER;
2163: lv_RESOURCE_ID NUMBER;
2164: lv_ORGANIZATION_ID NUMBER;
2192: END IF;
2193:
2194: c_count:= 0;
2195: total_count := 0;
2196: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2197: lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2198: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2199: ELSE
2200: lv_tbl:= 'MSC_RESOURCE_INSTANCE_REQS';
2228: ||' AND ms.PLAN_ID= -1'
2229: ||' AND ms.SR_INSTANCE_ID= msrir.SR_INSTANCE_ID'
2230: ||' AND ms.DISPOSITION_ID= msrir.WIP_ENTITY_ID'
2231: ||' AND ms.ORDER_TYPE IN ( 3, 7,70)'
2232: ||' AND msrir.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2233: ||' AND t1.sr_inventory_item_id (+) = msrir.equipment_item_id '
2234: ||' AND t1.SR_INSTANCE_ID (+) = msrir.SR_INSTANCE_ID ';
2235:
2236:
2284: ||' :v_current_user,'
2285: ||' :v_current_date,'
2286: ||' :v_current_user)';
2287:
2288: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2289: BEGIN
2290: lv_sql_ins:=
2291: 'insert into '||lv_tbl
2292: ||' ( PLAN_ID,'
2343: ||' AND ms.PLAN_ID= -1 '
2344: ||' AND ms.SR_INSTANCE_ID= msrir.SR_INSTANCE_ID '
2345: ||' AND ms.DISPOSITION_ID= msrir.WIP_ENTITY_ID '
2346: ||' AND ms.ORDER_TYPE IN ( 3, 7,70) '
2347: ||' AND msrir.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2348: ||' AND t1.sr_inventory_item_id (+) = msrir.equipment_item_id '
2349: ||' AND t1.SR_INSTANCE_ID (+) = msrir.SR_INSTANCE_ID ';
2350:
2351: EXECUTE IMMEDIATE lv_sql_ins
2351: EXECUTE IMMEDIATE lv_sql_ins
2352: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
2353:
2354: COMMIT;
2355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'res instance req loaded');
2356:
2357: EXCEPTION
2358: WHEN OTHERS THEN
2359: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2357: EXCEPTION
2358: WHEN OTHERS THEN
2359: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2360:
2361: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2362: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2363: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2364: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2361: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2362: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2363: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2364: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2366:
2367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2368: RAISE;
2369:
2363: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2364: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2366:
2367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2368: RAISE;
2369:
2370: ELSE
2371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2368: RAISE;
2369:
2370: ELSE
2371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2372: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2372: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2376:
2377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2378:
2379: --If Direct path load results in warning then the processing has to be
2373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2376:
2377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2378:
2379: --If Direct path load results in warning then the processing has to be
2380: --switched back to row by row processing. This will help to identify the
2381: --erroneous record and will also help in processing the rest of the records.
2378:
2379: --If Direct path load results in warning then the processing has to be
2380: --switched back to row by row processing. This will help to identify the
2381: --erroneous record and will also help in processing the rest of the records.
2382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - res instance req');
2383: lb_refresh_failed := TRUE;
2384: END IF;
2385: END;
2386:
2499: WHEN OTHERS THEN
2500:
2501: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2502:
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2504: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2505: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2506: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2507: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2504: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2505: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2506: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2507: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2508:
2509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2510: RAISE;
2511:
2505: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2506: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2507: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2508:
2509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2510: RAISE;
2511:
2512: ELSE
2513:
2510: RAISE;
2511:
2512: ELSE
2513:
2514: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2515:
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2517: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2518: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2512: ELSE
2513:
2514: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2515:
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2517: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2518: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2519: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2517: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2518: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2519: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2521:
2522: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2523:
2524: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2524: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2525: FND_MESSAGE.SET_TOKEN('VALUE',
2526: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
2527: MSC_CL_COLLECTION.v_instance_id));
2528: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2529:
2530: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2531: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
2532: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
2529:
2530: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2531: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
2532: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2534:
2535: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2536: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2537: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
2534:
2535: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2536: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2537: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
2538: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2539:
2540: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RES_INSTANCE_ID));
2539:
2540: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RES_INSTANCE_ID));
2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2544:
2545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2546: END IF;
2547:
2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RES_INSTANCE_ID));
2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2544:
2545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2546: END IF;
2547:
2548: END;
2549:
2556: END IF;
2557:
2558: BEGIN
2559:
2560: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2561:
2562: lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2563: lv_sql_stmt:=
2564: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2560: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2561:
2562: lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2563: lv_sql_stmt:=
2564: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2565: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_INSTANCE_REQS'
2566: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2567: ||' AND plan_id = -1 '
2568: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2561:
2562: lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2563: lv_sql_stmt:=
2564: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2565: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_INSTANCE_REQS'
2566: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2567: ||' AND plan_id = -1 '
2568: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2569:
2564: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2565: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_INSTANCE_REQS'
2566: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2567: ||' AND plan_id = -1 '
2568: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2569:
2570:
2571: EXECUTE IMMEDIATE lv_sql_stmt;
2572:
2576:
2577: EXCEPTION
2578: WHEN OTHERS THEN
2579:
2580: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2581: RAISE;
2582: END;
2583:
2584: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2580: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2581: RAISE;
2582: END;
2583:
2584: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2585: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2586: lv_retcode,
2587: 'MSC_RESOURCE_INSTANCE_REQS',
2588: MSC_CL_COLLECTION.v_instance_code,
2585: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2586: lv_retcode,
2587: 'MSC_RESOURCE_INSTANCE_REQS',
2588: MSC_CL_COLLECTION.v_instance_code,
2589: MSC_UTIL.G_WARNING
2590: );
2591:
2592: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2588: MSC_CL_COLLECTION.v_instance_code,
2589: MSC_UTIL.G_WARNING
2590: );
2591:
2592: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2594: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2595: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2596: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2589: MSC_UTIL.G_WARNING
2590: );
2591:
2592: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2594: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2595: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2596: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2597: END IF;
2591:
2592: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2594: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2595: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2596: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2597: END IF;
2598:
2599: END IF;
2592: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2594: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2595: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2596: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2597: END IF;
2598:
2599: END IF;
2600:
2601: EXCEPTION
2602: WHEN OTHERS THEN
2603: IF res_inst_req%ISOPEN THEN CLOSE res_inst_req; END IF;
2604:
2605: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2607: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2608: END IF;
2609: IF lv_sql_stmt IS NOT NULL THEN
2603: IF res_inst_req%ISOPEN THEN CLOSE res_inst_req; END IF;
2604:
2605: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2606: IF lv_cursor_stmt IS NOT NULL THEN
2607: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2609: IF lv_sql_stmt IS NOT NULL THEN
2610: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2611: END IF;
2606: IF lv_cursor_stmt IS NOT NULL THEN
2607: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2608: END IF;
2609: IF lv_sql_stmt IS NOT NULL THEN
2610: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2613: RAISE;
2614: END LOAD_RES_INST_REQ;
2608: END IF;
2609: IF lv_sql_stmt IS NOT NULL THEN
2610: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2611: END IF;
2612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2613: RAISE;
2614: END LOAD_RES_INST_REQ;
2615:
2616: --===================================================================
2637: FROM MSC_ITEM_ID_LID t1,
2638: MSC_ST_DEMANDS msd
2639: WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2640: AND msd.ORIGINATION_TYPE IN (2,3,4,25,50,70) /* ds change change 50 eam demand */
2641: AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
2642: AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
2643: AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
2644:
2645: lv_DISPOSITION_ID NUMBER;
2734:
2735: c_count:=0;
2736:
2737: -- ========= Prepare the Cursor Statement ==========
2738: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2739: lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
2740: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2741: ELSE
2742: lv_tbl:= 'MSC_DEMANDS';
2753: /* 2201791 - select substr(order_number,1,62) since order_number is
2754: defined as varchar(62) in msc_demands table */
2755:
2756:
2757: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') Then
2758: v_srp_profile_vlaue := 1;
2759:
2760: ELSE
2761: v_srp_profile_vlaue := 0;
2807: || lv_supplies_tbl||' ms,'
2808: ||' MSC_ST_DEMANDS msd'
2809: ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2810: ||' AND msd.ORIGINATION_TYPE IN (2,3,4,25,50)' /* 50 eam demand: ds change change*/
2811: ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2812: ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
2813: ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
2814: ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
2815: ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
2886:
2887: EXCEPTION
2888: WHEN OTHERS THEN
2889:
2890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2892: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2893: END IF;
2894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2888: WHEN OTHERS THEN
2889:
2890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2891: IF lv_sql_stmt IS NOT NULL THEN
2892: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2895:
2896: ROLLBACK WORK TO SAVEPOINT Load_wip_SP;
2890: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2891: IF lv_sql_stmt IS NOT NULL THEN
2892: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
2893: END IF;
2894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2895:
2896: ROLLBACK WORK TO SAVEPOINT Load_wip_SP;
2897: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
2898:
2893: END IF;
2894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2895:
2896: ROLLBACK WORK TO SAVEPOINT Load_wip_SP;
2897: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
2898:
2899: END;
2900: END IF;
2901:
3018: || lv_supplies_tbl||' ms,'
3019: ||' MSC_ST_DEMANDS msd'
3020: ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
3021: ||' AND msd.ORIGINATION_TYPE IN (2,3,4,25,50)' /* 50 eam demand: ds change change*/
3022: ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
3023: ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
3024: ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
3025: ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
3026: ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
3162:
3163: END IF; -- refresh mode
3164:
3165: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
3166: ( lv_DELETED_FLAG<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) OR
3167: ( lv_ORIGINATION_TYPE= 25) THEN
3168:
3169: EXECUTE IMMEDIATE lv_sql_stmt
3170: USING
3210: WHEN OTHERS THEN
3211:
3212: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3213:
3214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3215: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3216: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3217: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3215: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3216: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3217: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3219:
3220: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3221: RAISE;
3222:
3216: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3217: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3219:
3220: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3221: RAISE;
3222:
3223: ELSE
3224: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3220: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3221: RAISE;
3222:
3223: ELSE
3224: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3225:
3226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3227: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3228: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3222:
3223: ELSE
3224: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3225:
3226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3227: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3228: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3229: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3227: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3228: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3229: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3231:
3232: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3233: FND_MESSAGE.SET_TOKEN('COLUMN', 'WIP_ENTITY_ID');
3234: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_WIP_ENTITY_ID));
3231:
3232: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3233: FND_MESSAGE.SET_TOKEN('COLUMN', 'WIP_ENTITY_ID');
3234: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_WIP_ENTITY_ID));
3235: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3236:
3237: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3238: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3239: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3236:
3237: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3238: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3239: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3241:
3242: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3243: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3244: FND_MESSAGE.SET_TOKEN('VALUE',
3243: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3244: FND_MESSAGE.SET_TOKEN('VALUE',
3245: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3246: MSC_CL_COLLECTION.v_instance_id));
3247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3248:
3249: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3250: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
3251: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEMAND_TYPE));
3248:
3249: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3250: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
3251: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEMAND_TYPE));
3252: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3253:
3254: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3255: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
3256: FND_MESSAGE.SET_TOKEN('VALUE',
3255: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
3256: FND_MESSAGE.SET_TOKEN('VALUE',
3257: MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
3258: lv_ORIGINATION_TYPE));
3259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3260:
3261: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3262: END IF;
3263:
3257: MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
3258: lv_ORIGINATION_TYPE));
3259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3260:
3261: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3262: END IF;
3263:
3264: END;
3265:
3279: EXCEPTION
3280: WHEN OTHERS THEN
3281: IF c2%ISOPEN THEN CLOSE c2; END IF;
3282:
3283: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3285: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3286: END IF;
3287: IF lv_sql_stmt IS NOT NULL THEN
3281: IF c2%ISOPEN THEN CLOSE c2; END IF;
3282:
3283: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3284: IF lv_cursor_stmt IS NOT NULL THEN
3285: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3287: IF lv_sql_stmt IS NOT NULL THEN
3288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3289: END IF;
3284: IF lv_cursor_stmt IS NOT NULL THEN
3285: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3286: END IF;
3287: IF lv_sql_stmt IS NOT NULL THEN
3288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3291: RAISE;
3292: END LOAD_WIP_DEMAND;
3286: END IF;
3287: IF lv_sql_stmt IS NOT NULL THEN
3288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
3289: END IF;
3290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3291: RAISE;
3292: END LOAD_WIP_DEMAND;
3293:
3294: --============================================================================
3312: msrr.ORIG_RESOURCE_SEQ_NUM,
3313: msrr.SR_INSTANCE_ID
3314: FROM MSC_ST_RESOURCE_REQUIREMENTS msrr
3315: WHERE msrr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3316: AND msrr.DELETED_FLAG= MSC_UTIL.SYS_YES;
3317:
3318: lv_DEPARTMENT_ID NUMBER;
3319: lv_RESOURCE_ID NUMBER;
3320: lv_ASSEMBLY_ITEM_ID NUMBER;
3385:
3386: c_count:= 0;
3387:
3388: -- ========= Prepare the Cursor Statement ==========
3389: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3390: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
3391: lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
3392: ELSE
3393: lv_tbl:= 'MSC_RESOURCE_REQUIREMENTS';
3464: ||' AND ms.PLAN_ID= -1'
3465: ||' AND ms.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID'
3466: ||' AND ms.DISPOSITION_ID= msrr.WIP_ENTITY_ID'
3467: ||' AND ms.ORDER_TYPE IN ( 3, 7,70)' /* 70 esm suply:ds change change */
3468: ||' AND msrr.DELETED_FLAG= '||MSC_UTIL.SYS_NO
3469: ||' AND miil.SR_INVENTORY_ITEM_ID(+)= msrr.INVENTORY_ITEM_ID'
3470: ||' AND miil.SR_INSTANCE_ID(+)= msrr.SR_INSTANCE_ID'; --Outer join for CMRO
3471:
3472: -- ========= Prepare SQL Statement for INSERT ==========
3593: ||' :v_current_user,'
3594: ||' :v_current_date,'
3595: ||' :v_current_user)';
3596:
3597: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3598: BEGIN
3599: lv_sql_ins:=
3600: 'insert into '||lv_tbl
3601: ||' ( PLAN_ID,'
3726: ||' AND ms.PLAN_ID= -1'
3727: ||' AND ms.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID'
3728: ||' AND ms.DISPOSITION_ID= msrr.WIP_ENTITY_ID'
3729: ||' AND ms.ORDER_TYPE IN ( 3, 7,70)' /* 70 esm suply:ds change change */
3730: ||' AND msrr.DELETED_FLAG= '||MSC_UTIL.SYS_NO
3731: ||' AND miil.SR_INVENTORY_ITEM_ID(+)= msrr.INVENTORY_ITEM_ID'
3732: ||' AND miil.SR_INSTANCE_ID(+)= msrr.SR_INSTANCE_ID';
3733:
3734: EXECUTE IMMEDIATE lv_sql_ins
3734: EXECUTE IMMEDIATE lv_sql_ins
3735: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
3736:
3737: COMMIT;
3738: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'res req loaded');
3739:
3740: EXCEPTION
3741: WHEN OTHERS THEN
3742: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
3740: EXCEPTION
3741: WHEN OTHERS THEN
3742: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
3743:
3744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3745: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3746: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3747: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3748: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3745: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3746: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3747: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3748: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3749:
3750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3751: RAISE;
3752:
3746: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3747: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3748: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3749:
3750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3751: RAISE;
3752:
3753: ELSE
3754: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3751: RAISE;
3752:
3753: ELSE
3754: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3755: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3756: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3757: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3758: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3754: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3755: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3756: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3757: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3758: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3759:
3760: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3761:
3762: --If Direct path load results in warning then the processing has to be
3756: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3757: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3758: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3759:
3760: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3761:
3762: --If Direct path load results in warning then the processing has to be
3763: --switched back to row by row processing. This will help to identify the
3764: --erroneous record and will also help in processing the rest of the records.
3761:
3762: --If Direct path load results in warning then the processing has to be
3763: --switched back to row by row processing. This will help to identify the
3764: --erroneous record and will also help in processing the rest of the records.
3765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - res req');
3766: lb_refresh_failed := TRUE;
3767: END IF;
3768: END;
3769:
4012: WHEN OTHERS THEN
4013:
4014: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
4015:
4016: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4017: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4018: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4019: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4016: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4017: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4018: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4019: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4021:
4022: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4023: RAISE;
4024:
4018: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4019: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4021:
4022: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4023: RAISE;
4024:
4025: ELSE
4026:
4023: RAISE;
4024:
4025: ELSE
4026:
4027: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4028:
4029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4030: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4031: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4025: ELSE
4026:
4027: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4028:
4029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4030: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4031: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4032: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4030: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4031: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
4032: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
4033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4034:
4035: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4036: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4037: FND_MESSAGE.SET_TOKEN('VALUE',
4036: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4037: FND_MESSAGE.SET_TOKEN('VALUE',
4038: MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
4039: MSC_CL_COLLECTION.v_instance_id));
4040: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4041:
4042: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4043: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
4044: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
4041:
4042: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4043: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
4044: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
4045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4046:
4047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4048: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
4049: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
4046:
4047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4048: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
4049: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
4050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4051:
4052: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4053: END IF;
4054:
4048: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
4049: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
4050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4051:
4052: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4053: END IF;
4054:
4055: END;
4056:
4063: END IF;
4064: /*
4065: BEGIN
4066:
4067: IF (((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND
4068: (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) AND
4069: NOT (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4070: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')) THEN
4071:
4064: /*
4065: BEGIN
4066:
4067: IF (((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND
4068: (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) AND
4069: NOT (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4070: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')) THEN
4071:
4072: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4065: BEGIN
4066:
4067: IF (((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND
4068: (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) AND
4069: NOT (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4070: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')) THEN
4071:
4072: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4073:
4066:
4067: IF (((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND
4068: (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) AND
4069: NOT (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4070: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')) THEN
4071:
4072: lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4073:
4074: lv_sql_stmt:=
4075: 'INSERT INTO '||lv_tbl
4076: ||' SELECT * from MSC_RESOURCE_REQUIREMENTS'
4077: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4078: ||' AND plan_id = -1 '
4079: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4080:
4081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4082: EXECUTE IMMEDIATE lv_sql_stmt;
4083:
4077: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4078: ||' AND plan_id = -1 '
4079: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4080:
4081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4082: EXECUTE IMMEDIATE lv_sql_stmt;
4083:
4084: COMMIT;
4085:
4087:
4088: EXCEPTION
4089: WHEN OTHERS THEN
4090:
4091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4092: RAISE;
4093: END;
4094: */
4095: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE_CHARGES; /* ds change */
4093: END;
4094: */
4095: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE_CHARGES; /* ds change */
4096:
4097: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4098: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4099: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')
4100: THEN
4101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'alter temp table for res_req not handled here');
4094: */
4095: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE_CHARGES; /* ds change */
4096:
4097: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4098: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4099: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')
4100: THEN
4101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'alter temp table for res_req not handled here');
4102: ELSE
4095: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE_CHARGES; /* ds change */
4096:
4097: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4098: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4099: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')
4100: THEN
4101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'alter temp table for res_req not handled here');
4102: ELSE
4103: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4097: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4098: IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
4099: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y')
4100: THEN
4101: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'alter temp table for res_req not handled here');
4102: ELSE
4103: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4104: lv_retcode,
4105: 'MSC_RESOURCE_REQUIREMENTS',
4103: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4104: lv_retcode,
4105: 'MSC_RESOURCE_REQUIREMENTS',
4106: MSC_CL_COLLECTION.v_instance_code,
4107: MSC_UTIL.G_WARNING
4108: );
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4111: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4105: 'MSC_RESOURCE_REQUIREMENTS',
4106: MSC_CL_COLLECTION.v_instance_code,
4107: MSC_UTIL.G_WARNING
4108: );
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4111: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4112: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4113: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4106: MSC_CL_COLLECTION.v_instance_code,
4107: MSC_UTIL.G_WARNING
4108: );
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4111: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4112: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4113: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4114: END IF;
4108: );
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4111: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4112: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4113: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4114: END IF;
4115:
4116: END IF;
4109: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4111: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4112: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4113: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4114: END IF;
4115:
4116: END IF;
4117: END IF;
4119: EXCEPTION
4120: WHEN OTHERS THEN
4121: IF c4%ISOPEN THEN CLOSE c4; END IF;
4122:
4123: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4126: END IF;
4127: IF lv_sql_stmt IS NOT NULL THEN
4121: IF c4%ISOPEN THEN CLOSE c4; END IF;
4122:
4123: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4124: IF lv_cursor_stmt IS NOT NULL THEN
4125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4127: IF lv_sql_stmt IS NOT NULL THEN
4128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4129: END IF;
4124: IF lv_cursor_stmt IS NOT NULL THEN
4125: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4126: END IF;
4127: IF lv_sql_stmt IS NOT NULL THEN
4128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4131: RAISE;
4132: END LOAD_RES_REQ;
4126: END IF;
4127: IF lv_sql_stmt IS NOT NULL THEN
4128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
4129: END IF;
4130: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4131: RAISE;
4132: END LOAD_RES_REQ;
4133:
4134: --=============================================================================
4145: lv_temp_res_req_tbl VARCHAR2(30);
4146: lv_sql_stmt VARCHAR2(32767);
4147: lv_where_clause VARCHAR2(2000);
4148: BEGIN
4149: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4150: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) AND
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4146: lv_sql_stmt VARCHAR2(32767);
4147: lv_where_clause VARCHAR2(2000);
4148: BEGIN
4149: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4150: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) AND
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4147: lv_where_clause VARCHAR2(2000);
4148: BEGIN
4149: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4150: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) AND
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4148: BEGIN
4149: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4150: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) AND
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4156: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO))) THEN
4150: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES) AND
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4156: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO))) THEN
4157: -- We do not need to do anything as, we are collecting both WIP and CMRO
4158: -- forecasts and the are there in the staging table.
4151: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) AND
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4156: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO))) THEN
4157: -- We do not need to do anything as, we are collecting both WIP and CMRO
4158: -- forecasts and the are there in the staging table.
4159: -- Or, we need not change any data in msc_resource_requirements.
4152: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS))
4153: OR
4154: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4155: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_NO) AND
4156: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO))) THEN
4157: -- We do not need to do anything as, we are collecting both WIP and CMRO
4158: -- forecasts and the are there in the staging table.
4159: -- Or, we need not change any data in msc_resource_requirements.
4160: RETURN;
4163: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4164: lv_temp_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4165:
4166: lv_sql_stmt :=
4167: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_res_req_tbl
4168: ||' SELECT * FROM '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_REQUIREMENTS '
4169: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4170: ||' AND plan_id = -1 and (';
4171:
4164: lv_temp_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
4165:
4166: lv_sql_stmt :=
4167: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_temp_res_req_tbl
4168: ||' SELECT * FROM '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_REQUIREMENTS '
4169: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4170: ||' AND plan_id = -1 and (';
4171:
4172: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4168: ||' SELECT * FROM '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_RESOURCE_REQUIREMENTS '
4169: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4170: ||' AND plan_id = -1 and (';
4171:
4172: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4173: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4174: END IF;
4175: /*
4176: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4169: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4170: ||' AND plan_id = -1 and (';
4171:
4172: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4173: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4174: END IF;
4175: /*
4176: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4177: OR NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4173: lv_sql_stmt := lv_sql_stmt||' organization_id NOT '||MSC_UTIL.v_in_org_str;
4174: END IF;
4175: /*
4176: IF NOT MSC_CL_COLLECTION.v_is_complete_refresh
4177: OR NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4178: lv_sql_stmt := lv_sql_stmt||' OR (';
4179: END IF;*/
4180:
4181: IF ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4177: OR NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
4178: lv_sql_stmt := lv_sql_stmt||' OR (';
4179: END IF;*/
4180:
4181: IF ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4182: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)) THEN
4183: lv_where_clause :=' NOT ( supply_type =92 ) ';
4184: ELSIF MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES THEN
4185: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4178: lv_sql_stmt := lv_sql_stmt||' OR (';
4179: END IF;*/
4180:
4181: IF ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4182: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)) THEN
4183: lv_where_clause :=' NOT ( supply_type =92 ) ';
4184: ELSIF MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES THEN
4185: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4186: ELSIF MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES THEN
4180:
4181: IF ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4182: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)) THEN
4183: lv_where_clause :=' NOT ( supply_type =92 ) ';
4184: ELSIF MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES THEN
4185: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4186: ELSIF MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES THEN
4187: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4188: END IF;
4182: (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES)) THEN
4183: lv_where_clause :=' NOT ( supply_type =92 ) ';
4184: ELSIF MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES THEN
4185: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4186: ELSIF MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES THEN
4187: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4188: END IF;
4189:
4190: IF lv_where_clause is not NULL and MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES then
4186: ELSIF MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag = MSC_UTIL.SYS_YES THEN
4187: lv_where_clause :=' NOT ( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4188: END IF;
4189:
4190: IF lv_where_clause is not NULL and MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES then
4191: lv_where_clause := lv_where_clause ||' AND supply_type NOT IN (1,3,50,70)';
4192: ELSIF MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES THEN
4193: lv_where_clause :=' supply_type NOT IN (1,3,50,70)';
4194: END IF;
4188: END IF;
4189:
4190: IF lv_where_clause is not NULL and MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES then
4191: lv_where_clause := lv_where_clause ||' AND supply_type NOT IN (1,3,50,70)';
4192: ELSIF MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES THEN
4193: lv_where_clause :=' supply_type NOT IN (1,3,50,70)';
4194: END IF;
4195:
4196: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4192: ELSIF MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES THEN
4193: lv_where_clause :=' supply_type NOT IN (1,3,50,70)';
4194: END IF;
4195:
4196: IF NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS
4197: and lv_where_clause IS NOT NULL THEN
4198: lv_sql_stmt := lv_sql_stmt||' OR ';
4199: END IF;
4200:
4201: IF lv_where_clause IS NOT NULL THEN
4202: lv_sql_stmt := lv_sql_stmt||' ('|| lv_where_clause||') )';
4203: END IF;
4204:
4205: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOAD_ODS_RES_REQ '||lv_sql_stmt);
4206: EXECUTE IMMEDIATE lv_sql_stmt;
4207:
4208: COMMIT;
4209: END IF;
4219: lv_copySQL_variablepart VARCHAR2 (5000);
4220: firstcondn BOOLEAN;
4221:
4222: BEGIN
4223: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4224: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)) OR
4225: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4226: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO)) AND
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4220: firstcondn BOOLEAN;
4221:
4222: BEGIN
4223: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4224: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)) OR
4225: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4226: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO)) AND
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4228: MSC_UTIL.G_ALL_ORGANIZATIONS)) THEN
4221:
4222: BEGIN
4223: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4224: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)) OR
4225: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4226: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO)) AND
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4228: MSC_UTIL.G_ALL_ORGANIZATIONS)) THEN
4229: -- We do not need to do anything as, we are collecting both WIP and CMRO
4222: BEGIN
4223: IF (((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND
4224: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)) OR
4225: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4226: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO)) AND
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4228: MSC_UTIL.G_ALL_ORGANIZATIONS)) THEN
4229: -- We do not need to do anything as, we are collecting both WIP and CMRO
4230: -- forecasts and the are there in the staging table.
4224: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES)) OR
4225: ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND
4226: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO)) AND
4227: (MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4228: MSC_UTIL.G_ALL_ORGANIZATIONS)) THEN
4229: -- We do not need to do anything as, we are collecting both WIP and CMRO
4230: -- forecasts and the are there in the staging table.
4231: -- Or, we need not change any data in msc_resource_requirements.
4232: RETURN;
4241: ||' WHERE sr_instance_id =' ||MSC_CL_COLLECTION.v_instance_id
4242: ||' AND plan_id = -1 ';
4243:
4244:
4245: IF --((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND USAF
4246: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4247:
4248: lv_collected_ordertypes :='70';
4249: END IF;
4242: ||' AND plan_id = -1 ';
4243:
4244:
4245: IF --((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_YES) AND USAF
4246: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4247:
4248: lv_collected_ordertypes :='70';
4249: END IF;
4250: /*ELSIF -- ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND USAF
4246: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4247:
4248: lv_collected_ordertypes :='70';
4249: END IF;
4250: /*ELSIF -- ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND USAF
4251: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4252:
4253: lv_sql_stmt :=lv_sql_stmt
4254: ||' AND supply_type NOT IN (70)'; */
4247:
4248: lv_collected_ordertypes :='70';
4249: END IF;
4250: /*ELSIF -- ((MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag = MSC_UTIL.SYS_NO) AND USAF
4251: (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
4252:
4253: lv_sql_stmt :=lv_sql_stmt
4254: ||' AND supply_type NOT IN (70)'; */
4255:
4255:
4256:
4257: /* start of code to prepare lv_collected_entites_filter2
4258: IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4259: MSC_UTIL.SYS_YES)
4260: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4261: MSC_UTIL.SYS_YES))
4262: THEN
4263: lv_collected_entites_filter2 := '(supply_type =92)';
4257: /* start of code to prepare lv_collected_entites_filter2
4258: IF ( (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4259: MSC_UTIL.SYS_YES)
4260: AND (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4261: MSC_UTIL.SYS_YES))
4262: THEN
4263: lv_collected_entites_filter2 := '(supply_type =92)';
4264: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4265: MSC_UTIL.SYS_NO)
4261: MSC_UTIL.SYS_YES))
4262: THEN
4263: lv_collected_entites_filter2 := '(supply_type =92)';
4264: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4265: MSC_UTIL.SYS_NO)
4266: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4267: MSC_UTIL.SYS_YES))
4268: THEN
4269: Lv_collected_entites_filter2 :=
4263: lv_collected_entites_filter2 := '(supply_type =92)';
4264: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4265: MSC_UTIL.SYS_NO)
4266: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4267: MSC_UTIL.SYS_YES))
4268: THEN
4269: Lv_collected_entites_filter2 :=
4270: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4271: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4268: THEN
4269: Lv_collected_entites_filter2 :=
4270: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4271: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4272: MSC_UTIL.SYS_YES)
4273: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4274: MSC_UTIL.SYS_NO))
4275: THEN
4276: lv_collected_entites_filter2 :=
4270: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =2 ) ';
4271: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4272: MSC_UTIL.SYS_YES)
4273: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4274: MSC_UTIL.SYS_NO))
4275: THEN
4276: lv_collected_entites_filter2 :=
4277: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4278: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4275: THEN
4276: lv_collected_entites_filter2 :=
4277: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4278: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4279: MSC_UTIL.SYS_NO)
4280: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4281: MSC_UTIL.SYS_NO))
4282: THEN
4283: lv_collected_entites_filter2 := NULL;
4277: '( supply_type =92 AND MAINTENANCE_OBJECT_SOURCE =1 ) ';
4278: ELSIF ( (MSC_CL_COLLECTION.v_coll_prec.eam_forecasts_flag =
4279: MSC_UTIL.SYS_NO)
4280: AND (MSC_CL_COLLECTION.v_coll_prec.cmro_forecasts_flag =
4281: MSC_UTIL.SYS_NO))
4282: THEN
4283: lv_collected_entites_filter2 := NULL;
4284: END IF;
4285:
4285:
4286: firstcondn := TRUE;
4287:
4288: IF (NOT MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
4289: MSC_UTIL.G_ALL_ORGANIZATIONS)
4290: THEN
4291: IF (NOT firstcondn)
4292: THEN
4293: lv_copySQL_variablepart := lv_copySQL_variablepart || ' OR ';
4293: lv_copySQL_variablepart := lv_copySQL_variablepart || ' OR ';
4294: END IF;
4295:
4296: lv_copySQL_variablepart :=
4297: '(ORGANIZATION_ID NOT ' || MSC_UTIL.v_in_org_str || ')';
4298: firstcondn := FALSE;
4299: END IF;
4300:
4301: IF NOT ( MSC_CL_COLLECTION.v_is_complete_refresh
4341: || lv_copySQL_variablepart
4342: || ')';
4343: END IF;
4344:
4345: MSC_UTIL.LOG_MSG (
4346: MSC_UTIL.G_D_STATUS,
4347: 'Load ODS resource requirements : lv_copySQL_variablepart -'
4348: || lv_copySQL_variablepart);
4349: MSC_UTIL.LOG_MSG (
4342: || ')';
4343: END IF;
4344:
4345: MSC_UTIL.LOG_MSG (
4346: MSC_UTIL.G_D_STATUS,
4347: 'Load ODS resource requirements : lv_copySQL_variablepart -'
4348: || lv_copySQL_variablepart);
4349: MSC_UTIL.LOG_MSG (
4350: MSC_UTIL.G_D_STATUS,
4345: MSC_UTIL.LOG_MSG (
4346: MSC_UTIL.G_D_STATUS,
4347: 'Load ODS resource requirements : lv_copySQL_variablepart -'
4348: || lv_copySQL_variablepart);
4349: MSC_UTIL.LOG_MSG (
4350: MSC_UTIL.G_D_STATUS,
4351: 'Load ODS resource requirements :sql stmt being executed: ' || lv_sql_stmt);
4352:
4353: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS demand : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4346: MSC_UTIL.G_D_STATUS,
4347: 'Load ODS resource requirements : lv_copySQL_variablepart -'
4348: || lv_copySQL_variablepart);
4349: MSC_UTIL.LOG_MSG (
4350: MSC_UTIL.G_D_STATUS,
4351: 'Load ODS resource requirements :sql stmt being executed: ' || lv_sql_stmt);
4352:
4353: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS demand : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4354: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS demand :sql stmt being executed: ' || lv_sql_stmt);
4349: MSC_UTIL.LOG_MSG (
4350: MSC_UTIL.G_D_STATUS,
4351: 'Load ODS resource requirements :sql stmt being executed: ' || lv_sql_stmt);
4352:
4353: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS demand : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4354: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS demand :sql stmt being executed: ' || lv_sql_stmt);
4355: EXECUTE IMMEDIATE lv_sql_stmt;
4356:
4357: /*
4350: MSC_UTIL.G_D_STATUS,
4351: 'Load ODS resource requirements :sql stmt being executed: ' || lv_sql_stmt);
4352:
4353: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS,'Load ODS demand : lv_copySQL_variablepart -' || lv_copySQL_variablepart);
4354: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS demand :sql stmt being executed: ' || lv_sql_stmt);
4355: EXECUTE IMMEDIATE lv_sql_stmt;
4356:
4357: /*
4358: IF (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS)
4354: MSC_UTIL.LOG_MSG (MSC_UTIL.G_D_STATUS, 'Load ODS demand :sql stmt being executed: ' || lv_sql_stmt);
4355: EXECUTE IMMEDIATE lv_sql_stmt;
4356:
4357: /*
4358: IF (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS)
4359: THEN
4360: null;
4361: ELSIF (MSC_CL_COLLECTION.v_is_complete_refresh) then
4362: lv_sql_stmt :=lv_sql_stmt
4359: THEN
4360: null;
4361: ELSIF (MSC_CL_COLLECTION.v_is_complete_refresh) then
4362: lv_sql_stmt :=lv_sql_stmt
4363: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4364:
4365: END IF;
4366:
4367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4363: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4364:
4365: END IF;
4366:
4367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4368: EXECUTE IMMEDIATE lv_sql_stmt; * /
4369:
4370: COMMIT;
4371: END IF;