832: lv_dummy2 varchar2(32);
833: lv_msc_schema varchar2(32);
834: BEGIN
835: /* DBMS_MVIEW.REFRESH ('MSC_ITEM_SO_SR_LEVELS_MV','c');
836: DBMS_MVIEW.REFRESH ('MSC_ITEM_FCST_SR_LEVELS_MV','c');*/
837:
838: IF p_plan_id is NULL THEN
839: lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', lv_dummy1, lv_dummy2, lv_msc_schema);
840:
840:
841: lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_SO_SR_LEVELS';
842: EXECUTE IMMEDIATE lv_sql_stmt;
843:
844: lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_FCST_SR_LEVELS';
845: EXECUTE IMMEDIATE lv_sql_stmt;
846:
847: lv_plan_id := ' ';
848:
847: lv_plan_id := ' ';
848:
849: ELSE
850: DELETE MSC_ITEM_SO_SR_LEVELS where plan_id = p_plan_id;
851: DELETE MSC_ITEM_FCST_SR_LEVELS where plan_id = p_plan_id;
852: lv_plan_id := ' WHERE plan_id = '||p_plan_id||' ';
853: END IF;
854:
855: COMMIT;
927:
928:
929: IF (p_global_forecast > 0 OR p_plan_id is NULL) THEN
930: lv_sql_stmt :=
931: ' INSERT /*+ APPEND */ INTO MSC_ITEM_FCST_SR_LEVELS '
932: ||' (INVENTORY_ITEM_ID ,'
933: ||' SR_INVENTORY_ITEM_ID ,'
934: ||' ORGANIZATION_ID ,'
935: ||' SR_INSTANCE_ID ,'
1001: ||' CUSTOMER_SITE_ID ,'
1002: ||' ZONE_ID ,'
1003: ||' ASSY_DRP_PLANNED ,'
1004: ||' COMP_DRP_PLANNED '
1005: ||' FROM MSC_ITEM_FCST_SR_LEVELS_V '
1006: ||lv_plan_id;
1007:
1008: EXECUTE IMMEDIATE lv_sql_stmt ;
1009: COMMIT;
1007:
1008: EXECUTE IMMEDIATE lv_sql_stmt ;
1009: COMMIT;
1010:
1011: LOG_MESSAGE( 'Inserted records into MSC_ITEM_FCST_SR_LEVELS');
1012: END IF;
1013: p_err_code := G_SUCCESS;
1014: p_err_mesg := 'SUCCESS';
1015: EXCEPTION