[Home] [Help]
40: l_dev_status,
41: l_message);
42:
43: IF l_call_status=FALSE THEN
44: msc_St_util.log_message( l_message);
45: RETURN SYS_NO;
46: END IF;
47:
48: IF l_dev_phase NOT IN ( 'PENDING','RUNNING') THEN
139: ||' AND batch_id = :p_batch_id'
140: ||' AND partner_type IN (2,3)';
141:
142: IF lv_debug THEN
143: msc_st_util.log_message(lv_sql_stmt);
144: END IF;
145:
146: EXECUTE IMMEDIATE lv_sql_stmt
147: USING p_instance_id,
196: ||' AND mstp.partner_type=3 ';
197:
198:
199: IF lv_debug THEN
200: msc_st_util.log_message(lv_sql_stmt);
201: END IF;
202:
203: EXECUTE IMMEDIATE lv_sql_stmt
204: USING p_instance_id,
212: ROLLBACK;
213:
214: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_ORG_CUST '||'('
215: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
216: msc_st_util.log_message(lv_error_text);
217: ERRBUF := lv_error_text;
218: RETCODE := G_WARNING;
219:
220: END LOAD_ORG_CUST;
319: ||' AND mssi.batch_id = :p_batch_id'
320: ||' AND mssi.sr_instance_id = :p_instance_id';
321:
322: IF lv_debug THEN
323: msc_st_util.log_message(lv_sql_stmt);
324: END IF;
325:
326: EXECUTE IMMEDIATE lv_sql_stmt
327: USING p_instance_id,
372: ||' AND mssi.sr_instance_id = :p_instance_id';
373:
374:
375: IF lv_debug THEN
376: msc_st_util.log_message(lv_sql_stmt);
377: END IF;
378:
379:
380: EXECUTE IMMEDIATE lv_sql_stmt
441: ||' AND mssi.batch_id = :p_batch_id'
442: ||' AND mssi.sr_instance_id = :p_instance_id';
443:
444: IF lv_debug THEN
445: msc_st_util.log_message(lv_sql_stmt);
446: END IF;
447:
448: EXECUTE IMMEDIATE lv_sql_stmt
449: USING p_instance_id,
711: ||' and par.instance_id = msi.sr_instance_id ) ';
712:
713:
714: IF lv_debug THEN
715: msc_st_util.log_message(lv_sql_stmt);
716: END IF;
717:
718: EXECUTE IMMEDIATE lv_sql_stmt
719: USING p_instance_id,
728: ROLLBACK;
729:
730: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_ITEMS '||'('
731: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
732: msc_st_util.log_message(SQLERRM);
733: msc_st_util.log_message(lv_error_text);
734: ERRBUF := lv_error_text;
735: RETCODE := G_WARNING;
736:
729:
730: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_ITEMS '||'('
731: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
732: msc_st_util.log_message(SQLERRM);
733: msc_st_util.log_message(lv_error_text);
734: ERRBUF := lv_error_text;
735: RETCODE := G_WARNING;
736:
737: END LOAD_ITEMS;
855: ||' AND msi.plan_id = -1';
856:
857:
858: IF lv_debug THEN
859: msc_st_util.log_message(lv_sql_stmt);
860: END IF;
861: EXECUTE IMMEDIATE lv_sql_stmt
862: USING p_instance_id,
863: p_batch_id,
968: ||' AND msi.plan_id = -1';
969:
970:
971: IF lv_debug THEN
972: msc_st_util.log_message(lv_sql_stmt);
973: END IF;
974:
975: EXECUTE IMMEDIATE lv_sql_stmt
976: USING p_instance_id,
985: ROLLBACK;
986:
987: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_CATEGORY '||'('
988: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
989: msc_st_util.log_message(lv_error_text);
990: ERRBUF := lv_error_text;
991: RETCODE := G_WARNING;
992:
993: END LOAD_CATEGORY;
1057: ||' AND sr_instance_id = :p_instance_id'
1058: ||' AND batch_id = :p_batch_id';
1059:
1060: IF lv_debug THEN
1061: msc_st_util.log_message(lv_sql_stmt);
1062: END IF;
1063: EXECUTE IMMEDIATE lv_sql_stmt
1064: USING p_instance_id,
1065: p_batch_id;
1119: ||' AND process_flag = '||G_VALID
1120: ||' AND batch_id = :p_batch_id';
1121:
1122: IF lv_debug THEN
1123: msc_st_util.log_message(lv_sql_stmt);
1124: END IF;
1125:
1126: EXECUTE IMMEDIATE lv_sql_stmt
1127: USING p_instance_id,
1134: ROLLBACK;
1135:
1136: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_SITE '||'('
1137: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1138: msc_st_util.log_message(lv_error_text);
1139: ERRBUF := lv_error_text;
1140: RETCODE := G_WARNING;
1141:
1142: END LOAD_SITE;
1217: ||' AND batch_id = :p_batch_id';
1218:
1219:
1220: IF lv_debug THEN
1221: msc_st_util.log_message(lv_sql_stmt);
1222: END IF;
1223:
1224: EXECUTE IMMEDIATE lv_sql_stmt
1225: USING p_instance_id,
1345: ||' AND process_flag = '||G_VALID
1346: ||' AND batch_id = :p_batch_id';
1347:
1348: IF lv_debug THEN
1349: msc_st_util.log_message(lv_sql_stmt);
1350: END IF;
1351:
1352: EXECUTE IMMEDIATE lv_sql_stmt
1353: USING lv_all_dcs_pk, --Renamed 'lv_sr_level_pk' to 'lv_all_dcs_pk'.
1359: ROLLBACK;
1360:
1361: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_DEMAND_CLASS '||'('
1362: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1363: msc_st_util.log_message(lv_error_text);
1364: ERRBUF := lv_error_text;
1365: RETCODE := G_WARNING;
1366:
1367: END LOAD_DEMAND_CLASS;
1464: WHERE rowid = lb_rowid(j);
1465:
1466:
1467: -- Error out the records where level_name is invalid
1468: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1469: (p_app_short_name => 'MSC',
1470: p_error_code => 'MSC_PP_INVALID_VALUE',
1471: p_message_text => lv_message_text,
1472: p_error_text => lv_error_text,
1477: RAISE ex_logging_err;
1478: END IF;
1479:
1480: -- Derive LEVEL_ID from msd_levels
1481: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
1482: (p_table_name => 'MSD_ST_LEVEL_VALUES',
1483: p_level_name_col => 'LEVEL_NAME',
1484: p_level_id_col => 'LEVEL_ID',
1485: p_severity => G_SEV_ERROR,
1504:
1505: IF (lv_instance_type = G_INS_OTHER) THEN
1506:
1507: -- Error out the records where level_name is invalid
1508: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1509: (p_app_short_name => 'MSC',
1510: p_error_code => 'MSC_PP_INVALID_VALUE',
1511: p_message_text => lv_message_text,
1512: p_error_text => lv_error_text,
1529: ||' AND batch_id = :lv_batch_id'
1530: ||' AND sr_instance_code = :p_instance_code';
1531:
1532: IF lv_debug THEN
1533: msc_st_util.log_message(lv_sql_stmt);
1534: END IF;
1535: EXECUTE IMMEDIATE lv_sql_stmt
1536: USING lv_batch_id,
1537: p_instance_code;
1538:
1539: END IF;
1540:
1541: -- Error out the record if level value is NULL
1542: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1543: (p_app_short_name => 'MSC',
1544: p_error_code => 'MSC_PP_COL_VAL_NULL',
1545: p_message_text => lv_message_text,
1546: p_error_text => lv_error_text,
1562: ||' AND process_flag = '||G_IN_PROCESS
1563: ||' AND sr_instance_code = :p_instance_code';
1564:
1565: IF lv_debug THEN
1566: msc_st_util.log_message(lv_sql_stmt);
1567: END IF;
1568: EXECUTE IMMEDIATE lv_sql_stmt
1569: USING lv_batch_id,
1570: p_instance_code;
1575: IF (lv_instance_type <> G_INS_OTHER) THEN
1576:
1577: -- Now derive sr_level_pk from the ASCP tables if Level Values are loaded forERP data.
1578: v_sql_stmt := 05;
1579: lv_return := MSC_ST_UTIL.DERIVE_SETUP_SR_LEVEL_PK
1580: (p_table_name => 'MSD_ST_LEVEL_VALUES',
1581: p_level_val_col => 'LEVEL_VALUE',
1582: p_level_pk_col => 'SR_LEVEL_PK',
1583: p_level_id_col => 'LEVEL_ID',
1663: END IF; --IF (lv_instance_type <> G_INS_OTHER) THEN
1664:
1665: -- Now derive sr_level_pk from the msd_local_id_setup
1666: v_sql_stmt := 08;
1667: lv_return := MSC_ST_UTIL.DERIVE_SR_LEVEL_PK
1668: (p_table_name => 'MSD_ST_LEVEL_VALUES',
1669: p_level_val_col => 'LEVEL_VALUE',
1670: p_level_pk_col => 'SR_LEVEL_PK',
1671: p_level_id_col => 'LEVEL_ID',
1747:
1748: CLOSE c2;
1749:
1750: -- Set the process flag as Valid and populate instance_id
1751: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
1752: (p_table_name => 'MSD_ST_LEVEL_VALUES',
1753: p_instance_id => p_instance_id,
1754: p_instance_code => p_instance_code,
1755: p_process_flag => G_VALID,
1762: END IF;
1763:
1764: -- Inserting all the errored out records into MSC_ERRORS
1765:
1766: lv_return := MSC_ST_UTIL.LOG_ERROR
1767: (p_table_name => 'MSD_ST_LEVEL_VALUES',
1768: p_instance_code => p_instance_code,
1769: p_row => lv_column_names,
1770: p_severity => G_SEV_ERROR,
1772: p_message_text => NULL,
1773: p_batch_id => lv_batch_id);
1774:
1775: IF(lv_return <> 0) THEN
1776: msc_st_util.log_message(lv_error_text);
1777: END IF;
1778:
1779:
1780: LOAD_LEVEL_ORG_ASSCNS ( p_instance_code,
1791: ROLLBACK;
1792:
1793: ERRBUF := lv_error_text;
1794: RETCODE := G_WARNING;
1795: msc_st_util.log_message(lv_error_text);
1796:
1797: WHEN OTHERS THEN
1798: ROLLBACK;
1799:
1798: ROLLBACK;
1799:
1800: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_LEVEL_VALUE '||'('
1801: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1802: msc_st_util.log_message(lv_error_text);
1803: ERRBUF := lv_error_text;
1804: RETCODE := G_WARNING;
1805:
1806: END LOAD_LEVEL_VALUE ;
1869: WHERE rowid = lb_rowid(j);
1870:
1871:
1872: -- Error out the records where level_name is invalid
1873: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1874: (p_app_short_name => 'MSC',
1875: p_error_code => 'MSC_PP_INVALID_VALUE',
1876: p_message_text => lv_message_text,
1877: p_error_text => lv_error_text,
1882: RAISE ex_logging_err;
1883: END IF;
1884:
1885: -- Derive LEVEL_ID from msd_levels
1886: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
1887: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
1888: p_level_name_col => 'LEVEL_NAME',
1889: p_level_id_col => 'LEVEL_ID',
1890: p_severity => G_SEV_ERROR,
1897: RAISE ex_logging_err;
1898: END IF;
1899:
1900: -- Error out the records where parent_level_name is invalid
1901: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1902: (p_app_short_name => 'MSC',
1903: p_error_code => 'MSC_PP_INVALID_VALUE',
1904: p_message_text => lv_message_text,
1905: p_error_text => lv_error_text,
1910: RAISE ex_logging_err;
1911: END IF;
1912:
1913: -- Derive PARENT_LEVEL_ID from msd_levels
1914: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
1915: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
1916: p_level_name_col => 'PARENT_LEVEL_NAME',
1917: p_level_id_col => 'PARENT_LEVEL_ID',
1918: p_severity => G_SEV_ERROR,
1926: END IF;
1927:
1928: -- set the message, sr_level_value is invalid
1929:
1930: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1931: (p_app_short_name => 'MSC',
1932: p_error_code => 'MSC_PP_INVALID_VALUE',
1933: p_message_text => lv_message_text,
1934: p_error_text => lv_error_text,
1940: END IF;
1941:
1942: -- Derive SR_LEVEL_PK from msd_level_value or msd_st_level_values
1943:
1944: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
1945: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
1946: p_level_val_col => 'SR_LEVEL_VALUE',
1947: p_level_name_col => 'LEVEL_NAME',
1948: p_level_pk_col => 'SR_LEVEL_PK',
1957: END IF;
1958:
1959: -- set the message, sr_parent_level_value is invalid
1960:
1961: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1962: (p_app_short_name => 'MSC',
1963: p_error_code => 'MSC_PP_INVALID_VALUE',
1964: p_message_text => lv_message_text,
1965: p_error_text => lv_error_text,
1971: END IF;
1972:
1973: -- Derive SR_PARENT_LEVEL_PK from msd_level_values or msd_st_level_values
1974:
1975: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
1976: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
1977: p_level_val_col => 'SR_PARENT_LEVEL_VALUE',
1978: p_level_name_col => 'PARENT_LEVEL_NAME',
1979: p_level_pk_col => 'SR_PARENT_LEVEL_PK',
1989: -- Validate whether the child and parent level exist in msd_level_hierarchies
1990: -- This can exist under any hierarhcy
1991: -- Set the message,
1992:
1993: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1994: (p_app_short_name => 'MSC',
1995: p_error_code => 'MSD_PP_ASSOC_INVALID',
1996: p_message_text => lv_message_text,
1997: p_error_text => lv_error_text,
2019: ||' AND batch_id = :lv_batch_id'
2020: ||' AND sr_instance_code = :p_instance_code';
2021:
2022: IF lv_debug THEN
2023: msc_st_util.log_message(lv_sql_stmt);
2024: END IF;
2025:
2026: EXECUTE IMMEDIATE lv_sql_stmt
2027: USING lv_batch_id,
2041: RAISE ex_logging_err;
2042: END IF;
2043:
2044: -- Set the process flag as Valid and populate instance_id
2045: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
2046: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
2047: p_instance_id => p_instance_id,
2048: p_instance_code => p_instance_code,
2049: p_process_flag => G_VALID,
2056: END IF;
2057:
2058: -- Inserting all the errored out records into MSC_ERRORS:
2059:
2060: lv_return := MSC_ST_UTIL.LOG_ERROR
2061: (p_table_name => 'MSD_ST_LEVEL_ASSOCIATIONS',
2062: p_instance_code => p_instance_code,
2063: p_row => lv_column_names,
2064: p_severity => G_SEV_ERROR,
2066: p_message_text => NULL,
2067: p_batch_id => lv_batch_id);
2068:
2069: IF(lv_return <> 0) THEN
2070: msc_st_util.log_message(lv_error_text);
2071: END IF;
2072: COMMIT;
2073:
2074: EXCEPTION
2076: ROLLBACK;
2077:
2078: ERRBUF := lv_error_text;
2079: RETCODE := G_WARNING;
2080: msc_st_util.log_message(lv_error_text);
2081:
2082: WHEN OTHERS THEN
2083: ROLLBACK;
2084:
2083: ROLLBACK;
2084:
2085: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_LEVEL_ASSOC'||'('
2086: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2087: msc_st_util.log_message(lv_error_text);
2088: ERRBUF := lv_error_text;
2089: RETCODE := G_WARNING;
2090:
2091: END LOAD_LEVEL_ASSOC ;
2388:
2389: IF (lv_instance_type = G_INS_OTHER) THEN
2390:
2391: -- Set the message to validate customer-ship to location combination
2392: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2393: (p_app_short_name => 'MSC',
2394: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
2395: p_message_text => lv_message_text,
2396: p_error_text => lv_error_text,
2425:
2426: END IF;
2427:
2428: -- Set the message
2429: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2430: (p_app_short_name => 'MSC',
2431: p_error_code => 'MSC_PP_INVALID_VALUE',
2432: p_message_text => lv_message_text,
2433: p_error_text => lv_error_text,
2439: END IF;
2440:
2441: -- Derive sr_item_pk from msd_st_level_values and msd_level_values
2442:
2443: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2444: (p_table_name => 'MSD_ST_BOOKING_DATA',
2445: p_column_name => 'ITEM',
2446: p_pk_col_name => 'SR_ITEM_PK',
2447: p_level_id => 1,
2467: ||' AND batch_id = :lv_batch_id'
2468: ||' AND sr_instance_code = :p_instance_code';
2469:
2470: IF lv_debug THEN
2471: msc_st_util.log_message(lv_sql_stmt);
2472: END IF;
2473:
2474: EXECUTE IMMEDIATE lv_sql_stmt
2475: USING lv_batch_id,
2475: USING lv_batch_id,
2476: p_instance_code ;
2477:
2478: -- Set the message
2479: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2480: (p_app_short_name => 'MSC',
2481: p_error_code => 'MSC_PP_INVALID_VALUE',
2482: p_message_text => lv_message_text,
2483: p_error_text => lv_error_text,
2489: END IF;
2490:
2491: -- Derive SR_ORIGINAL_ITEM_PK from msd_st_level_values and msd_level_values
2492:
2493: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2494: (p_table_name => 'MSD_ST_BOOKING_DATA',
2495: p_column_name => 'ORIGINAL_ITEM',
2496: p_pk_col_name => 'SR_ORIGINAL_ITEM_PK',
2497: p_level_id => 1,
2506: RAISE ex_logging_err;
2507: END IF;
2508:
2509: -- Set the message
2510: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2511: (p_app_short_name => 'MSC',
2512: p_error_code => 'MSC_PP_INVALID_VALUE',
2513: p_message_text => lv_message_text,
2514: p_error_text => lv_error_text,
2520: END IF;
2521:
2522: -- Derive sr_inv_org_pk from msd_st_level_values and msd_level_values
2523:
2524: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2525: (p_table_name => 'MSD_ST_BOOKING_DATA',
2526: p_column_name => 'INV_ORG',
2527: p_pk_col_name => 'SR_INV_ORG_PK',
2528: p_level_id => 7,
2538: END IF;
2539:
2540: IF (lv_instance_type = G_INS_OTHER) THEN
2541:
2542: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2543: (p_app_short_name => 'MSC',
2544: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
2545: p_message_text => lv_message_text,
2546: p_error_text => lv_error_text,
2587: END IF;
2588:
2589:
2590: -- Set the message
2591: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2592: (p_app_short_name => 'MSC',
2593: p_error_code => 'MSC_PP_INVALID_VALUE',
2594: p_message_text => lv_message_text,
2595: p_error_text => lv_error_text,
2601: END IF;
2602:
2603: -- Derive sr_customer_pk from msd_st_level_values and msd_level_values
2604:
2605: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2606: (p_table_name => 'MSD_ST_BOOKING_DATA',
2607: p_column_name => 'CUSTOMER',
2608: p_pk_col_name => 'SR_CUSTOMER_PK',
2609: p_level_id => 15,
2617: RAISE ex_logging_err;
2618: END IF;
2619:
2620: -- Set the message
2621: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2622: (p_app_short_name => 'MSC',
2623: p_error_code => 'MSC_PP_INVALID_VALUE',
2624: p_message_text => lv_message_text,
2625: p_error_text => lv_error_text,
2630: RAISE ex_logging_err;
2631: END IF;
2632: -- Derive sr_ship_to_loc_pk from msd_st_level_values and msd_level_values
2633:
2634: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2635: (p_table_name => 'MSD_ST_BOOKING_DATA',
2636: p_column_name => 'SHIP_TO_LOC',
2637: p_pk_col_name => 'SR_SHIP_TO_LOC_PK',
2638: p_level_id => 11,
2649:
2650: IF (lv_instance_type <> G_INS_OTHER) THEN
2651:
2652: -- Set the message to validate customer-ship to location combination for ERP Data
2653: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2654: (p_app_short_name => 'MSC',
2655: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
2656: p_message_text => lv_message_text,
2657: p_error_text => lv_error_text,
2695: END IF;
2696:
2697:
2698: -- Set the message
2699: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2700: (p_app_short_name => 'MSC',
2701: p_error_code => 'MSC_PP_INVALID_VALUE',
2702: p_message_text => lv_message_text,
2703: p_error_text => lv_error_text,
2709: END IF;
2710:
2711: -- Derive sr_sales_channel_pk from msd_st_level_values and msd_level_values
2712:
2713: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2714: (p_table_name => 'MSD_ST_BOOKING_DATA',
2715: p_column_name => 'SALES_CHANNEL',
2716: p_pk_col_name => 'SR_SALES_CHANNEL_PK',
2717: p_level_id => 27,
2726: RAISE ex_logging_err;
2727: END IF;
2728:
2729: -- Set the message
2730: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2731: (p_app_short_name => 'MSC',
2732: p_error_code => 'MSC_PP_INVALID_VALUE',
2733: p_message_text => lv_message_text,
2734: p_error_text => lv_error_text,
2739: RAISE ex_logging_err;
2740: END IF;
2741: -- Derive sr_sales_rep_pk from msd_st_level_values and msd_level_values
2742:
2743: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2744: (p_table_name => 'MSD_ST_BOOKING_DATA',
2745: p_column_name => 'SALES_REP',
2746: p_pk_col_name => 'SR_SALES_REP_PK',
2747: p_level_id => 18,
2757: END IF;
2758:
2759: -- Demand Class changes for Booking Data starts
2760: -- Set the message
2761: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2762: (p_app_short_name => 'MSC',
2763: p_error_code => 'MSC_PP_INVALID_VALUE',
2764: p_message_text => lv_message_text,
2765: p_error_text => lv_error_text,
2771: END IF;
2772:
2773: -- Derive sr_sales_channel_pk from msd_st_level_values and msd_level_values
2774:
2775: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2776: (p_table_name => 'MSD_ST_BOOKING_DATA',
2777: p_column_name => 'DEMAND_CLASS_LVL_VAL',
2778: p_pk_col_name => 'SR_DEMAND_CLASS_PK',
2779: p_level_id => 34,
2790:
2791: -- Demand Class changes for Booking Data ends
2792:
2793: -- Set the message
2794: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2795: (p_app_short_name => 'MSC',
2796: p_error_code => 'MSC_PP_INVALID_VALUE',
2797: p_message_text => lv_message_text,
2798: p_error_text => lv_error_text,
2804: END IF;
2805:
2806: -- Derive sr_parent_item_pk from msd_st_level_values and msd_level_values
2807:
2808: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
2809: (p_table_name => 'MSD_ST_BOOKING_DATA',
2810: p_column_name => 'PARENT_ITEM',
2811: p_pk_col_name => 'SR_PARENT_ITEM_PK',
2812: p_level_id => 1,
2823:
2824:
2825: -- set the message, USER_DEFINED1 is invalid
2826:
2827: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2828: (p_app_short_name => 'MSC',
2829: p_error_code => 'MSC_PP_INVALID_VALUE',
2830: p_message_text => lv_message_text,
2831: p_error_text => lv_error_text,
2837: END IF;
2838:
2839: -- Derive SR_USER_DEFINED1_LVL_PK
2840:
2841: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
2842: (p_table_name => 'MSD_ST_BOOKING_DATA',
2843: p_level_val_col => 'USER_DEFINED1',
2844: p_level_name_col => 'USER_DEFINED_LEVEL1',
2845: p_level_pk_col => 'SR_USER_DEFINED1_PK',
2854: END IF;
2855:
2856: -- set the message, USER_DEFINED2 is invalid
2857:
2858: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2859: (p_app_short_name => 'MSC',
2860: p_error_code => 'MSC_PP_INVALID_VALUE',
2861: p_message_text => lv_message_text,
2862: p_error_text => lv_error_text,
2868: END IF;
2869:
2870: -- Derive SR_USER_DEFINED2_LVL_PK
2871:
2872: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
2873: (p_table_name => 'MSD_ST_BOOKING_DATA',
2874: p_level_val_col => 'USER_DEFINED2',
2875: p_level_name_col => 'USER_DEFINED_LEVEL2',
2876: p_level_pk_col => 'SR_USER_DEFINED2_PK',
2884: RAISE ex_logging_err;
2885: END IF;
2886:
2887: -- Set the message
2888: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2889: (p_app_short_name => 'MSC',
2890: p_error_code => 'MSC_PP_COL_VAL_NULL',
2891: p_message_text => lv_message_text,
2892: p_error_text => lv_error_text,
2909: ||' AND batch_id = :lv_batch_id'
2910: ||' AND sr_instance_code = :p_instance_code';
2911:
2912: IF lv_debug THEN
2913: msc_st_util.log_message(lv_sql_stmt);
2914: END IF;
2915:
2916: EXECUTE IMMEDIATE lv_sql_stmt
2917: USING lv_batch_id,
2930: RAISE ex_logging_err;
2931: END IF;
2932:
2933: -- Set the process flag as Valid and populate instance_id
2934: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
2935: (p_table_name => 'MSD_ST_BOOKING_DATA',
2936: p_instance_id => p_instance_id,
2937: p_instance_code => p_instance_code,
2938: p_process_flag => G_VALID,
2945: END IF;
2946:
2947: -- Inserting all the errored out records into MSC_ERRORS:
2948:
2949: lv_return := MSC_ST_UTIL.LOG_ERROR
2950: (p_table_name => 'MSD_ST_BOOKING_DATA',
2951: p_instance_code => p_instance_code,
2952: p_row => lv_column_names,
2953: p_severity => G_SEV_ERROR,
2955: p_message_text => NULL,
2956: p_batch_id => lv_batch_id);
2957:
2958: IF(lv_return <> 0) THEN
2959: msc_st_util.log_message(lv_error_text);
2960: END IF;
2961: COMMIT;
2962:
2963: EXCEPTION
2965: ROLLBACK;
2966:
2967: ERRBUF := lv_error_text;
2968: RETCODE := G_WARNING;
2969: msc_st_util.log_message(lv_error_text);
2970:
2971: WHEN OTHERS THEN
2972: ROLLBACK;
2973:
2972: ROLLBACK;
2973:
2974: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_BOOKING_DATA '||'('
2975: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2976: msc_st_util.log_message(lv_error_text);
2977: ERRBUF := lv_error_text;
2978: RETCODE := G_WARNING;
2979:
2980: END LOAD_BOOKING_DATA ;
3277:
3278: IF (lv_instance_type = G_INS_OTHER) THEN
3279:
3280: -- Set the message to validate customer-ship to location combination
3281: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3282: (p_app_short_name => 'MSC',
3283: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
3284: p_message_text => lv_message_text,
3285: p_error_text => lv_error_text,
3315: END IF;
3316:
3317:
3318: -- Set the message
3319: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3320: (p_app_short_name => 'MSC',
3321: p_error_code => 'MSC_PP_INVALID_VALUE',
3322: p_message_text => lv_message_text,
3323: p_error_text => lv_error_text,
3329: END IF;
3330:
3331: -- Derive sr_item_pk from msd_st_level_values and msd_level_values
3332:
3333: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3334: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3335: p_column_name => 'ITEM',
3336: p_pk_col_name => 'SR_ITEM_PK',
3337: p_level_id => 1,
3358: ||' AND batch_id = :lv_batch_id'
3359: ||' AND sr_instance_code = :p_instance_code';
3360:
3361: IF lv_debug THEN
3362: msc_st_util.log_message(lv_sql_stmt);
3363: END IF;
3364:
3365: EXECUTE IMMEDIATE lv_sql_stmt
3366: USING lv_batch_id,
3367: p_instance_code ;
3368:
3369:
3370: -- Set the message
3371: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3372: (p_app_short_name => 'MSC',
3373: p_error_code => 'MSC_PP_INVALID_VALUE',
3374: p_message_text => lv_message_text,
3375: p_error_text => lv_error_text,
3381: END IF;
3382:
3383: -- Derive sr_original_item_pk from msd_st_level_values and msd_level_values
3384:
3385: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3386: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3387: p_column_name => 'ORIGINAL_ITEM',
3388: p_pk_col_name => 'SR_ORIGINAL_ITEM_PK',
3389: p_level_id => 1,
3398: RAISE ex_logging_err;
3399: END IF;
3400:
3401: -- Set the message
3402: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3403: (p_app_short_name => 'MSC',
3404: p_error_code => 'MSC_PP_INVALID_VALUE',
3405: p_message_text => lv_message_text,
3406: p_error_text => lv_error_text,
3412: END IF;
3413:
3414: -- Derive sr_inv_org_pk from msd_st_level_values and msd_level_values
3415:
3416: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3417: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3418: p_column_name => 'INV_ORG',
3419: p_pk_col_name => 'SR_INV_ORG_PK',
3420: p_level_id => 7,
3430: END IF;
3431:
3432: IF (lv_instance_type = G_INS_OTHER) THEN
3433:
3434: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3435: (p_app_short_name => 'MSC',
3436: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
3437: p_message_text => lv_message_text,
3438: p_error_text => lv_error_text,
3478:
3479: END IF;
3480:
3481: -- Set the message
3482: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3483: (p_app_short_name => 'MSC',
3484: p_error_code => 'MSC_PP_INVALID_VALUE',
3485: p_message_text => lv_message_text,
3486: p_error_text => lv_error_text,
3492: END IF;
3493:
3494: -- Derive sr_customer_pk from msd_st_level_values and msd_level_values
3495:
3496: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3497: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3498: p_column_name => 'CUSTOMER',
3499: p_pk_col_name => 'SR_CUSTOMER_PK',
3500: p_level_id => 15,
3508: RAISE ex_logging_err;
3509: END IF;
3510:
3511: -- Set the message
3512: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3513: (p_app_short_name => 'MSC',
3514: p_error_code => 'MSC_PP_INVALID_VALUE',
3515: p_message_text => lv_message_text,
3516: p_error_text => lv_error_text,
3522: END IF;
3523:
3524: -- Derive sr_ship_to_loc_pk from msd_st_level_values and msd_level_values
3525:
3526: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3527: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3528: p_column_name => 'SHIP_TO_LOC',
3529: p_pk_col_name => 'SR_SHIP_TO_LOC_PK',
3530: p_level_id => 11,
3541:
3542: IF (lv_instance_type <> G_INS_OTHER) THEN
3543:
3544: -- Set the message to validate customer-ship to location combination for ERP data
3545: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3546: (p_app_short_name => 'MSC',
3547: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
3548: p_message_text => lv_message_text,
3549: p_error_text => lv_error_text,
3584:
3585: END IF;
3586:
3587: -- Set the message
3588: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3589: (p_app_short_name => 'MSC',
3590: p_error_code => 'MSC_PP_INVALID_VALUE',
3591: p_message_text => lv_message_text,
3592: p_error_text => lv_error_text,
3598: END IF;
3599:
3600: -- Derive sr_sales_channel_pk from msd_st_level_values and msd_level_values
3601:
3602: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3603: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3604: p_column_name => 'SALES_CHANNEL',
3605: p_pk_col_name => 'SR_SALES_CHANNEL_PK',
3606: p_level_id => 27,
3615: RAISE ex_logging_err;
3616: END IF;
3617:
3618: -- Set the message
3619: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3620: (p_app_short_name => 'MSC',
3621: p_error_code => 'MSC_PP_INVALID_VALUE',
3622: p_message_text => lv_message_text,
3623: p_error_text => lv_error_text,
3628: RAISE ex_logging_err;
3629: END IF;
3630: -- Derive sr_sales_rep_pk from msd_st_level_values and msd_level_values
3631:
3632: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3633: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3634: p_column_name => 'SALES_REP',
3635: p_pk_col_name => 'SR_SALES_REP_PK',
3636: p_level_id => 18,
3646: END IF;
3647:
3648: -- Demand Class changes for Shipment Data starts
3649: -- Set the message
3650: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3651: (p_app_short_name => 'MSC',
3652: p_error_code => 'MSC_PP_INVALID_VALUE',
3653: p_message_text => lv_message_text,
3654: p_error_text => lv_error_text,
3659: RAISE ex_logging_err;
3660: END IF;
3661: -- Derive sr_sales_rep_pk from msd_st_level_values and msd_level_values
3662:
3663: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3664: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3665: p_column_name => 'DEMAND_CLASS_LVL_VAL',
3666: p_pk_col_name => 'SR_DEMAND_CLASS_PK',
3667: p_level_id => 34,
3677: END IF;
3678:
3679: -- Demand Class changes for Shipment Data ends
3680: -- Set the message
3681: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3682: (p_app_short_name => 'MSC',
3683: p_error_code => 'MSC_PP_INVALID_VALUE',
3684: p_message_text => lv_message_text,
3685: p_error_text => lv_error_text,
3690: RAISE ex_logging_err;
3691: END IF;
3692: -- Derive sr_sales_rep_pk from msd_st_level_values and msd_level_values
3693:
3694: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
3695: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3696: p_column_name => 'PARENT_ITEM',
3697: p_pk_col_name => 'SR_PARENT_ITEM_PK',
3698: p_level_id => 1,
3711:
3712:
3713: -- set the message, USER_DEFINED1 is invalid
3714:
3715: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3716: (p_app_short_name => 'MSC',
3717: p_error_code => 'MSC_PP_INVALID_VALUE',
3718: p_message_text => lv_message_text,
3719: p_error_text => lv_error_text,
3725: END IF;
3726:
3727: -- Derive SR_USER_DEFINED1_LVL_PK
3728:
3729: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
3730: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3731: p_level_val_col => 'USER_DEFINED1',
3732: p_level_name_col => 'USER_DEFINED_LEVEL1',
3733: p_level_pk_col => 'SR_USER_DEFINED1_PK',
3742: END IF;
3743:
3744: -- set the message, USER_DEFINED2 is invalid
3745:
3746: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3747: (p_app_short_name => 'MSC',
3748: p_error_code => 'MSC_PP_INVALID_VALUE',
3749: p_message_text => lv_message_text,
3750: p_error_text => lv_error_text,
3756: END IF;
3757:
3758: -- Derive SR_USER_DEFINED2_LVL_PK
3759:
3760: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
3761: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3762: p_level_val_col => 'USER_DEFINED2',
3763: p_level_name_col => 'USER_DEFINED_LEVEL2',
3764: p_level_pk_col => 'SR_USER_DEFINED2_PK',
3771: IF lv_return <> 0 THEN
3772: RAISE ex_logging_err;
3773: END IF;
3774: -- Set the message
3775: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3776: (p_app_short_name => 'MSC',
3777: p_error_code => 'MSC_PP_COL_VAL_NULL',
3778: p_message_text => lv_message_text,
3779: p_error_text => lv_error_text,
3796: ||' AND batch_id = :lv_batch_id'
3797: ||' AND sr_instance_code = :p_instance_code';
3798:
3799: IF lv_debug THEN
3800: msc_st_util.log_message(lv_sql_stmt);
3801: END IF;
3802:
3803: EXECUTE IMMEDIATE lv_sql_stmt
3804: USING lv_batch_id,
3818: RAISE ex_logging_err;
3819: END IF;
3820:
3821: -- Set the process flag as Valid and populate instance_id
3822: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
3823: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3824: p_instance_id => p_instance_id,
3825: p_instance_code => p_instance_code,
3826: p_process_flag => G_VALID,
3833: END IF;
3834:
3835: -- Inserting all the errored out records into MSC_ERRORS:
3836:
3837: lv_return := MSC_ST_UTIL.LOG_ERROR
3838: (p_table_name => 'MSD_ST_SHIPMENT_DATA',
3839: p_instance_code => p_instance_code,
3840: p_row => lv_column_names,
3841: p_severity => G_SEV_ERROR,
3843: p_message_text => NULL,
3844: p_batch_id => lv_batch_id);
3845:
3846: IF(lv_return <> 0) THEN
3847: msc_st_util.log_message(lv_error_text);
3848: END IF;
3849: COMMIT;
3850:
3851: EXCEPTION
3853: ROLLBACK;
3854:
3855: ERRBUF := lv_error_text;
3856: RETCODE := G_WARNING;
3857: msc_st_util.log_message(lv_error_text);
3858:
3859: WHEN OTHERS THEN
3860: ROLLBACK;
3861:
3860: ROLLBACK;
3861:
3862: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_SHIPMENT_DATA '||'('
3863: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3864: msc_st_util.log_message(lv_error_text);
3865: ERRBUF := lv_error_text;
3866: RETCODE := G_WARNING;
3867:
3868: END LOAD_SHIPMENT_DATA ;
4159: Bug3749959 */
4160: -- Changes for Bug 3749959, ENDS.
4161:
4162: -- Set the message
4163: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4164: (p_app_short_name => 'MSC',
4165: p_error_code => 'MSC_PP_INVALID_VALUE',
4166: p_message_text => lv_message_text,
4167: p_error_text => lv_error_text,
4173: END IF;
4174:
4175: -- Derive sr_item_pk from msd_st_level_values and msd_level_values
4176:
4177: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4178: (p_table_name => 'MSD_ST_MFG_FORECAST',
4179: p_column_name => 'ITEM',
4180: p_pk_col_name => 'SR_ITEM_PK',
4181: p_level_id => 1,
4188: IF lv_return <> 0 THEN
4189: RAISE ex_logging_err;
4190: END IF;
4191: -- Set the message
4192: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4193: (p_app_short_name => 'MSC',
4194: p_error_code => 'MSC_PP_INVALID_VALUE',
4195: p_message_text => lv_message_text,
4196: p_error_text => lv_error_text,
4202: END IF;
4203:
4204: -- Derive sr_inv_org_pk from msd_st_level_values and msd_level_values
4205:
4206: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4207: (p_table_name => 'MSD_ST_MFG_FORECAST',
4208: p_column_name => 'INV_ORG',
4209: p_pk_col_name => 'SR_INV_ORG_PK',
4210: p_level_id => 7,
4218: RAISE ex_logging_err;
4219: END IF;
4220:
4221: -- Set the message
4222: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4223: (p_app_short_name => 'MSC',
4224: p_error_code => 'MSC_PP_INVALID_VALUE',
4225: p_message_text => lv_message_text,
4226: p_error_text => lv_error_text,
4232: END IF;
4233:
4234: -- Derive sr_customer_pk from msd_st_level_values and msd_level_values
4235:
4236: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4237: (p_table_name => 'MSD_ST_MFG_FORECAST',
4238: p_column_name => 'CUSTOMER',
4239: p_pk_col_name => 'SR_CUSTOMER_PK',
4240: p_level_id => 15,
4248: RAISE ex_logging_err;
4249: END IF;
4250:
4251: -- Set the message
4252: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4253: (p_app_short_name => 'MSC',
4254: p_error_code => 'MSC_PP_INVALID_VALUE',
4255: p_message_text => lv_message_text,
4256: p_error_text => lv_error_text,
4261: RAISE ex_logging_err;
4262: END IF;
4263: -- Derive sr_ship_to_loc_pk from msd_st_level_values and msd_level_values
4264:
4265: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4266: (p_table_name => 'MSD_ST_MFG_FORECAST',
4267: p_column_name => 'SHIP_TO_LOC',
4268: p_pk_col_name => 'SR_SHIP_TO_LOC_PK',
4269: p_level_id => 11,
4277: RAISE ex_logging_err;
4278: END IF;
4279:
4280: -- Set the message
4281: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4282: (p_app_short_name => 'MSC',
4283: p_error_code => 'MSC_PP_INVALID_VALUE',
4284: p_message_text => lv_message_text,
4285: p_error_text => lv_error_text,
4291: END IF;
4292:
4293: -- Derive sr_sales_channel_pk from msd_st_level_values and msd_level_values
4294:
4295: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4296: (p_table_name => 'MSD_ST_MFG_FORECAST',
4297: p_column_name => 'SALES_CHANNEL',
4298: p_pk_col_name => 'SR_SALES_CHANNEL_PK',
4299: p_level_id => 27,
4308: END IF;
4309:
4310: -- Demand Class Changes for Mfg Forecast starts
4311: -- Set the message
4312: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4313: (p_app_short_name => 'MSC',
4314: p_error_code => 'MSC_PP_INVALID_VALUE',
4315: p_message_text => lv_message_text,
4316: p_error_text => lv_error_text,
4322: END IF;
4323:
4324: -- Derive sr_sales_channel_pk from msd_st_level_values and msd_level_values
4325:
4326: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
4327: (p_table_name => 'MSD_ST_MFG_FORECAST',
4328: p_column_name => 'DEMAND_CLASS_LVL_VAL',
4329: p_pk_col_name => 'SR_DEMAND_CLASS_PK',
4330: p_level_id => 34,
4341: -- Demand Class Changes for Mfg Forecast ends
4342:
4343: -- set the message, USER_DEFINED1 is invalid
4344:
4345: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4346: (p_app_short_name => 'MSC',
4347: p_error_code => 'MSC_PP_INVALID_VALUE',
4348: p_message_text => lv_message_text,
4349: p_error_text => lv_error_text,
4355: END IF;
4356:
4357: -- Derive SR_USER_DEFINED2_LVL_PK
4358:
4359: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
4360: (p_table_name => 'MSD_ST_MFG_FORECAST',
4361: p_level_val_col => 'USER_DEFINED1',
4362: p_level_name_col => 'USER_DEFINED_LEVEL1',
4363: p_level_pk_col => 'SR_USER_DEFINED1_PK',
4372: END IF;
4373:
4374: -- set the message, USER_DEFINED2 is invalid
4375:
4376: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4377: (p_app_short_name => 'MSC',
4378: p_error_code => 'MSC_PP_INVALID_VALUE',
4379: p_message_text => lv_message_text,
4380: p_error_text => lv_error_text,
4386: END IF;
4387:
4388: -- Derive SR_USER_DEFINED2_LVL_PK
4389:
4390: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
4391: (p_table_name => 'MSD_ST_MFG_FORECAST',
4392: p_level_val_col => 'USER_DEFINED2',
4393: p_level_name_col => 'USER_DEFINED_LEVEL2',
4394: p_level_pk_col => 'SR_USER_DEFINED2_PK',
4402: RAISE ex_logging_err;
4403: END IF;
4404:
4405: -- Set the message
4406: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4407: (p_app_short_name => 'MSC',
4408: p_error_code => 'MSC_PP_COL_VAL_NULL',
4409: p_message_text => lv_message_text,
4410: p_error_text => lv_error_text,
4427: ||' AND process_flag = '||G_IN_PROCESS
4428: ||' AND sr_instance_code = :p_instance_code';
4429:
4430: IF lv_debug THEN
4431: msc_st_util.log_message(lv_sql_stmt);
4432: END IF;
4433: EXECUTE IMMEDIATE lv_sql_stmt
4434: USING lv_batch_id,
4435: p_instance_code;
4434: USING lv_batch_id,
4435: p_instance_code;
4436:
4437: -- Set the message
4438: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4439: (p_app_short_name => 'MSC',
4440: p_error_code => 'MSC_PP_COL_VAL_NULL',
4441: p_message_text => lv_message_text,
4442: p_error_text => lv_error_text,
4457: ||' AND batch_id = :lv_batch_id'
4458: ||' AND sr_instance_code = :p_instance_code';
4459:
4460: IF lv_debug THEN
4461: msc_st_util.log_message(lv_sql_stmt);
4462: END IF;
4463:
4464: EXECUTE IMMEDIATE lv_sql_stmt
4465: USING lv_batch_id,
4465: USING lv_batch_id,
4466: p_instance_code ;
4467:
4468: -- Set the message
4469: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4470: (p_app_short_name => 'MSC',
4471: p_error_code => 'MSC_PP_COL_VAL_NULL',
4472: p_message_text => lv_message_text,
4473: p_error_text => lv_error_text,
4488: ||' AND batch_id = :lv_batch_id'
4489: ||' AND sr_instance_code = :p_instance_code';
4490:
4491: IF lv_debug THEN
4492: msc_st_util.log_message(lv_sql_stmt);
4493: END IF;
4494: EXECUTE IMMEDIATE lv_sql_stmt
4495: USING lv_batch_id,
4496: p_instance_code;
4506: ||' AND batch_id = :lv_batch_id'
4507: ||' AND sr_instance_code = :p_instance_code';
4508:
4509: IF lv_debug THEN
4510: msc_st_util.log_message(lv_sql_stmt);
4511: END IF;
4512:
4513: EXECUTE IMMEDIATE lv_sql_stmt
4514: USING lv_batch_id,
4525: ||' AND batch_id = :lv_batch_id'
4526: ||' AND sr_instance_code = :p_instance_code';
4527:
4528: IF lv_debug THEN
4529: msc_st_util.log_message(lv_sql_stmt);
4530: END IF;
4531:
4532: EXECUTE IMMEDIATE lv_sql_stmt
4533: USING lv_batch_id,
4539:
4540:
4541: IF (lv_instance_type = G_INS_OTHER) THEN
4542:
4543: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4544: (p_app_short_name => 'MSC',
4545: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
4546: p_message_text => lv_message_text,
4547: p_error_text => lv_error_text,
4600: RAISE ex_logging_err;
4601: END IF;
4602:
4603: -- Set the process flag as Valid and populate instance_id
4604: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
4605: (p_table_name => 'MSD_ST_MFG_FORECAST',
4606: p_instance_id => p_instance_id,
4607: p_instance_code => p_instance_code,
4608: p_process_flag => G_VALID,
4616:
4617:
4618: -- Inserting all the errored out records into MSC_ERRORS:
4619:
4620: lv_return := MSC_ST_UTIL.LOG_ERROR
4621: (p_table_name => 'MSD_ST_MFG_FORECAST',
4622: p_instance_code => p_instance_code,
4623: p_row => lv_column_names,
4624: p_severity => G_SEV_ERROR,
4626: p_message_text => NULL,
4627: p_batch_id => lv_batch_id);
4628:
4629: IF(lv_return <> 0) THEN
4630: msc_st_util.log_message(lv_error_text);
4631: END IF;
4632: COMMIT;
4633:
4634: EXCEPTION
4636: ROLLBACK;
4637:
4638: ERRBUF := lv_error_text;
4639: RETCODE := G_WARNING;
4640: msc_st_util.log_message(lv_error_text);
4641:
4642: WHEN OTHERS THEN
4643: ROLLBACK;
4644:
4643: ROLLBACK;
4644:
4645: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_MFG_FORECAST '||'('
4646: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
4647: msc_st_util.log_message(lv_error_text);
4648: ERRBUF := lv_error_text;
4649: RETCODE := G_WARNING;
4650:
4651: END LOAD_MFG_FORECAST ;
4762: ||' AND DECODE(SIGN(mps.next_date- mps.period_start_date),1,'
4763: ||' (mps.next_date-1),'||'NULL'||')' ;
4764:
4765: IF lv_debug THEN
4766: msc_st_util.log_message(lv_sql_stmt);
4767: END IF;
4768: EXECUTE IMMEDIATE lv_sql_stmt
4769: USING p_instance_id,
4770: p_calendar_code;
4771:
4772: IF lv_debug THEN
4773: OPEN C;
4774: FETCH C into lv_cal_end_date,lv_cal_start_date;
4775: msc_st_util.log_message('The Year Start Date is '||lv_cal_start_date||' and the Year End Date is '||lv_cal_end_date||' for the calendar with calendar code '||p_calendar_code);
4776: CLOSE C;
4777: END IF;
4778:
4779: EXCEPTION
4781: ROLLBACK;
4782:
4783: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_MFG_TIME '||'('
4784: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
4785: msc_st_util.log_message(lv_error_text);
4786: ERRBUF := lv_error_text;
4787: RETCODE := G_WARNING;
4788:
4789: END LOAD_MFG_TIME;
4870: -- Check for the valid calendar_type - 3 or 4 (G_FISCAL_CAL or G_COMPOSITE_CAL).
4871:
4872: -- set the message
4873:
4874: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4875: (p_app_short_name => 'MSC',
4876: p_error_code => 'MSC_PP_INVALID_VALUE',
4877: p_message_text => lv_message_text,
4878: p_error_text => lv_error_text,
4892: ||' AND process_flag = '||G_IN_PROCESS
4893: ||' AND sr_instance_code = :p_instance_code';
4894:
4895: IF lv_debug THEN
4896: msc_st_util.log_message(lv_sql_stmt);
4897: END IF;
4898: EXECUTE IMMEDIATE lv_sql_stmt
4899: USING p_instance_code;
4900:
4918: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
4919: ||' AND rownum <= '||lv_batch_size;
4920:
4921: IF lv_debug THEN
4922: msc_st_util.log_message(lv_sql_stmt);
4923: END IF;
4924:
4925: EXECUTE IMMEDIATE lv_sql_stmt
4926: USING lv_batch_id,
4945:
4946:
4947: -- set the message
4948:
4949: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
4950: (p_app_short_name => 'MSC',
4951: p_error_code => 'MSC_PP_COL_VAL_NULL',
4952: p_message_text => lv_message_text,
4953: p_error_text => lv_error_text,
4994: ||' AND batch_id = :lv_batch_id'
4995: ||' AND sr_instance_code = :p_instance_code';
4996:
4997: IF lv_debug THEN
4998: msc_st_util.log_message(lv_sql_stmt);
4999: END IF;
5000:
5001: EXECUTE IMMEDIATE lv_sql_stmt
5002: USING lv_batch_id,
5003: p_instance_code;
5004:
5005: -- set the message
5006:
5007: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5008: (p_app_short_name => 'MSC',
5009: p_error_code => 'MSC_PP_END_GT_ST_DATE',
5010: p_message_text => lv_message_text,
5011: p_error_text => lv_error_text );
5030: ||' AND batch_id = :lv_batch_id'
5031: ||' AND sr_instance_code = :p_instance_code';
5032:
5033: IF lv_debug THEN
5034: msc_st_util.log_message(lv_sql_stmt);
5035: END IF;
5036: EXECUTE IMMEDIATE lv_sql_stmt
5037: USING lv_batch_id,
5038: p_instance_code;
5049: IF NVL(lv_return,0) <> 0 THEN
5050: RAISE ex_logging_err;
5051: END IF;
5052: -- Set the process flag as Valid and populate instance_id
5053: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
5054: (p_table_name => 'MSD_ST_TIME',
5055: p_instance_id => p_instance_id,
5056: p_instance_code => p_instance_code,
5057: p_process_flag => G_VALID,
5064: END IF;
5065:
5066: -- Inserting all the errored out records into MSC_ERRORS:
5067:
5068: lv_return := MSC_ST_UTIL.LOG_ERROR
5069: (p_table_name => 'MSD_ST_TIME',
5070: p_instance_code => p_instance_code,
5071: p_row => lv_column_names,
5072: p_severity => G_SEV_ERROR,
5096: ||' AND instance = '||p_instance_id ;
5097:
5098:
5099: IF lv_debug THEN
5100: msc_st_util.log_message(lv_sql_stmt);
5101: END IF;
5102:
5103: EXECUTE IMMEDIATE lv_sql_stmt
5104: INTO lv_from_date,
5106: USING rec1.calendar_code;
5107:
5108:
5109: IF lv_debug THEN
5110: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5111: msc_st_util.log_message('FROM DATE'||lv_from_date);
5112: msc_st_util.log_message('TO DATE '||lv_to_date);
5113: END IF;
5114:
5107:
5108:
5109: IF lv_debug THEN
5110: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5111: msc_st_util.log_message('FROM DATE'||lv_from_date);
5112: msc_st_util.log_message('TO DATE '||lv_to_date);
5113: END IF;
5114:
5115:
5108:
5109: IF lv_debug THEN
5110: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5111: msc_st_util.log_message('FROM DATE'||lv_from_date);
5112: msc_st_util.log_message('TO DATE '||lv_to_date);
5113: END IF;
5114:
5115:
5116: v_sql_stmt := 08;
5134: ' and instance= '||p_instance_id||
5135: ' order by month_start_date';
5136:
5137: IF lv_debug THEN
5138: msc_st_util.log_message(lv_sql_stmt);
5139: END IF;
5140:
5141: OPEN Fiscal_Month_Cur FOR lv_sql_stmt ;
5142:
5191: ||' AND calendar_type = '||G_FISCAL_CAL
5192: ||' AND sr_instance_code = :lv_instance_code ';
5193:
5194: IF lv_debug THEN
5195: msc_st_util.log_message(lv_sql_stmt);
5196: END IF;
5197: EXECUTE IMMEDIATE lv_sql_stmt
5198: USING rec1.calendar_code,
5199: p_instance_code;
5206: ROLLBACK;
5207:
5208: ERRBUF := lv_error_text;
5209: RETCODE := G_WARNING;
5210: msc_st_util.log_message(lv_error_text);
5211:
5212: WHEN OTHERS THEN
5213: ROLLBACK;
5214:
5213: ROLLBACK;
5214:
5215: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_FISCAL_TIME '||'('
5216: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
5217: msc_st_util.log_message(lv_error_text);
5218: ERRBUF := lv_error_text;
5219: RETCODE := G_WARNING;
5220:
5221: END LOAD_FISCAL_TIME ;
5318: -- Check for the valid calendar_type value - 3 or 4 ( G_FISCAL_CAL ,G_COMPOSITE_CAL).
5319:
5320: -- set the message
5321:
5322: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5323: (p_app_short_name => 'MSC',
5324: p_error_code => 'MSC_PP_INVALID_VALUE',
5325: p_message_text => lv_message_text,
5326: p_error_text => lv_error_text,
5340: ||' AND process_flag = '||G_IN_PROCESS
5341: ||' AND sr_instance_code = :p_instance_code';
5342:
5343: IF lv_debug THEN
5344: msc_st_util.log_message(lv_sql_stmt);
5345: END IF;
5346: EXECUTE IMMEDIATE lv_sql_stmt
5347: USING p_instance_code;
5348:
5365: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
5366: ||' AND rownum <= '||lv_batch_size;
5367:
5368: IF lv_debug THEN
5369: msc_st_util.log_message(lv_sql_stmt);
5370: END IF;
5371:
5372: EXECUTE IMMEDIATE lv_sql_stmt
5373: USING lv_batch_id,
5392:
5393:
5394: -- set the message
5395:
5396: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5397: (p_app_short_name => 'MSC',
5398: p_error_code => 'MSC_PP_COL_VAL_NULL',
5399: p_message_text => lv_message_text,
5400: p_error_text => lv_error_text,
5451: ||' AND batch_id = :lv_batch_id'
5452: ||' AND sr_instance_code = :p_instance_code';
5453:
5454: IF lv_debug THEN
5455: msc_st_util.log_message(lv_sql_stmt);
5456: END IF;
5457:
5458: EXECUTE IMMEDIATE lv_sql_stmt
5459: USING lv_batch_id,
5464:
5465:
5466: -- set the message
5467:
5468: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5469: (p_app_short_name => 'MSC',
5470: p_error_code => 'MSC_PP_END_GT_ST_DATE',
5471: p_message_text => lv_message_text,
5472: p_error_text => lv_error_text );
5492: ||' AND batch_id = :lv_batch_id'
5493: ||' AND sr_instance_code = :p_instance_code';
5494:
5495: IF lv_debug THEN
5496: msc_st_util.log_message(lv_sql_stmt);
5497: END IF;
5498: EXECUTE IMMEDIATE lv_sql_stmt
5499: USING lv_batch_id,
5500: p_instance_code;
5517:
5518:
5519:
5520: -- Set the process flag as Valid and populate instance_id
5521: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
5522: (p_table_name => 'MSD_ST_TIME',
5523: p_instance_id => p_instance_id,
5524: p_instance_code => p_instance_code,
5525: p_process_flag => G_VALID,
5534:
5535:
5536: -- Inserting all the errored out records into MSC_ERRORS:
5537:
5538: lv_return := MSC_ST_UTIL.LOG_ERROR
5539: (p_table_name => 'MSD_ST_TIME',
5540: p_instance_code => p_instance_code,
5541: p_row => lv_column_names,
5542: p_severity => G_SEV_ERROR,
5566: ||' AND instance = '||p_instance_id ;
5567:
5568:
5569: IF lv_debug THEN
5570: msc_st_util.log_message(lv_sql_stmt);
5571: END IF;
5572:
5573: EXECUTE IMMEDIATE lv_sql_stmt
5574: INTO lv_from_date,
5576: USING rec1.calendar_code;
5577:
5578:
5579: IF lv_debug THEN
5580: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5581: msc_st_util.log_message('FROM DATE'||lv_from_date);
5582: msc_st_util.log_message('TO DATE '||lv_to_date);
5583: END IF;
5584:
5577:
5578:
5579: IF lv_debug THEN
5580: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5581: msc_st_util.log_message('FROM DATE'||lv_from_date);
5582: msc_st_util.log_message('TO DATE '||lv_to_date);
5583: END IF;
5584:
5585:
5578:
5579: IF lv_debug THEN
5580: msc_st_util.log_message( 'calendar'||rec1.calendar_code);
5581: msc_st_util.log_message('FROM DATE'||lv_from_date);
5582: msc_st_util.log_message('TO DATE '||lv_to_date);
5583: END IF;
5584:
5585:
5586: v_sql_stmt := 08;
5608: ' and instance= '||p_instance_id||
5609: ' order by week_start_date';
5610:
5611: IF lv_debug THEN
5612: msc_st_util.log_message(lv_sql_stmt);
5613: END IF;
5614:
5615: OPEN Composite_Week_Cur FOR lv_sql_stmt ;
5616:
5635: lv_WEEK_END_DATE;
5636:
5637: EXIT WHEN Composite_Week_Cur%NOTFOUND;
5638:
5639: MSC_ST_UTIL.Explode_Composite_Dates( errbuf => errbuf,
5640: retcode => retcode,
5641: p_dest_table => 'MSD_ST_TIME',
5642: p_instance_id => p_instance_id,
5643: p_calendar_type_id => G_COMPOSITE_CAL,
5672: ||' AND calendar_type = '||G_COMPOSITE_CAL
5673: ||' AND sr_instance_code = :lv_instance_code ';
5674:
5675: IF lv_debug THEN
5676: msc_st_util.log_message(lv_sql_stmt);
5677: END IF;
5678: EXECUTE IMMEDIATE lv_sql_stmt
5679: USING rec1.calendar_code,
5680: p_instance_code;
5687: ROLLBACK;
5688:
5689: ERRBUF := lv_error_text;
5690: RETCODE := G_WARNING;
5691: msc_st_util.log_message(lv_error_text);
5692:
5693: WHEN OTHERS THEN
5694: ROLLBACK;
5695:
5694: ROLLBACK;
5695:
5696: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_COMPOSITE_TIME '||'('
5697: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
5698: msc_st_util.log_message(lv_error_text);
5699: ERRBUF := lv_error_text;
5700: RETCODE := G_WARNING;
5701:
5702: END LOAD_COMPOSITE_TIME ;
5762: WHERE rowid = lb_rowid(j);
5763:
5764: -- set the message
5765:
5766: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5767: (p_app_short_name => 'MSC',
5768: p_error_code => 'MSC_PP_COL_VAL_NULL',
5769: p_message_text => lv_message_text,
5770: p_error_text => lv_error_text,
5793: ||' AND batch_id = :lv_batch_id'
5794: ||' AND sr_instance_code = :p_instance_code';
5795:
5796: IF lv_debug THEN
5797: msc_st_util.log_message(lv_sql_stmt);
5798: END IF;
5799: EXECUTE IMMEDIATE lv_sql_stmt
5800: USING lv_batch_id,
5801: p_instance_code;
5813: RAISE ex_logging_err;
5814: END IF;
5815:
5816: -- Set the process flag as Valid and populate instance_id
5817: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
5818: (p_table_name => 'MSD_ST_CURRENCY_CONVERSIONS',
5819: p_instance_id => p_instance_id,
5820: p_instance_code => p_instance_code,
5821: p_process_flag => G_VALID,
5828: END IF;
5829:
5830: -- Inserting all the errored out records into MSC_ERRORS:
5831:
5832: lv_return := MSC_ST_UTIL.LOG_ERROR
5833: (p_table_name => 'MSD_ST_CURRENCY_CONVERSIONS',
5834: p_instance_code => p_instance_code,
5835: p_row => lv_column_names,
5836: p_severity => G_SEV_ERROR,
5838: p_message_text => NULL,
5839: p_batch_id => lv_batch_id);
5840:
5841: IF(lv_return <> 0) THEN
5842: msc_st_util.log_message(lv_error_text);
5843: END IF;
5844: COMMIT;
5845:
5846: EXCEPTION
5848: ROLLBACK;
5849:
5850: ERRBUF := lv_error_text;
5851: RETCODE := G_WARNING;
5852: msc_st_util.log_message(lv_error_text);
5853:
5854: WHEN OTHERS THEN
5855: ROLLBACK;
5856:
5855: ROLLBACK;
5856:
5857: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_CURRENCY_CONV '||'('
5858: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
5859: msc_st_util.log_message(lv_error_text);
5860: ERRBUF := lv_error_text;
5861: RETCODE := G_WARNING;
5862:
5863: END LOAD_CURRENCY_CONV ;
5971: ||' AND batch_id = :lv_batch_id'
5972: ||' AND sr_instance_code = :p_instance_code';
5973:
5974: IF lv_debug THEN
5975: msc_st_util.log_message(lv_sql_stmt);
5976: END IF;
5977: EXECUTE IMMEDIATE lv_sql_stmt
5978: USING lv_batch_id,
5979: p_instance_code;
5980:
5981:
5982: -- Set the message
5983:
5984: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
5985: (p_app_short_name => 'MSC',
5986: p_error_code => 'MSC_PP_INVALID_VALUE',
5987: p_message_text => lv_message_text,
5988: p_error_text => lv_error_text,
6006: ||' AND process_flag = '||G_IN_PROCESS
6007: ||' AND sr_instance_code = :p_instance_code';
6008:
6009: IF lv_debug THEN
6010: msc_st_util.log_message(lv_sql_stmt);
6011: END IF;
6012: EXECUTE IMMEDIATE lv_sql_stmt
6013: USING lv_batch_id,
6014: p_instance_code;
6025: AND sr_instance_code = p_instance_code;
6026: */
6027:
6028: -- Set the message
6029: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6030: (p_app_short_name => 'MSC',
6031: p_error_code => 'MSC_PP_COL_VAL_NULL',
6032: p_message_text => lv_message_text,
6033: p_error_text => lv_error_text,
6049: ||' AND process_flag = '||G_IN_PROCESS
6050: ||' AND sr_instance_code = :p_instance_code';
6051:
6052: IF lv_debug THEN
6053: msc_st_util.log_message(lv_sql_stmt);
6054: END IF;
6055: EXECUTE IMMEDIATE lv_sql_stmt
6056: USING lv_batch_id,
6057: p_instance_code;
6060:
6061:
6062: -- Set the message
6063:
6064: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6065: (p_app_short_name => 'MSC',
6066: p_error_code => 'MSC_PP_INVALID_VALUE',
6067: p_message_text => lv_message_text,
6068: p_error_text => lv_error_text,
6075:
6076: -- Derive the level_id , error out if invalid level name
6077: --attribute_2 (prd_level_id)
6078:
6079: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6080: (p_table_name => 'MSD_ST_CS_DATA',
6081: p_level_name_col => 'ATTRIBUTE_2_VAL',
6082: p_level_id_col => 'ATTRIBUTE_2',
6083: p_severity => G_SEV3_ERROR ,
6091: END IF;
6092:
6093: -- set the message, attribute_4 is invalid
6094:
6095: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6096: (p_app_short_name => 'MSC',
6097: p_error_code => 'MSC_PP_INVALID_VALUE',
6098: p_message_text => lv_message_text,
6099: p_error_text => lv_error_text,
6106:
6107: -- Derive SR_LEVEL_PK - attribute_3 from msd_level_values
6108: -- or msd_st_level_values
6109:
6110: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6111: (p_table_name => 'MSD_ST_CS_DATA',
6112: p_level_val_col => 'ATTRIBUTE_4',
6113: p_level_name_col => 'ATTRIBUTE_2_VAL',
6114: p_level_pk_col => 'ATTRIBUTE_3',
6123: END IF;
6124:
6125: -- Set the message
6126:
6127: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6128: (p_app_short_name => 'MSC',
6129: p_error_code => 'MSC_PP_INVALID_VALUE',
6130: p_message_text => lv_message_text,
6131: p_error_text => lv_error_text,
6138:
6139: -- Derive the level_id , error out if invalid level name
6140: -- Attribute_6 (geo_level_id)
6141:
6142: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6143: (p_table_name => 'MSD_ST_CS_DATA',
6144: p_level_name_col => 'ATTRIBUTE_6_VAL',
6145: p_level_id_col => 'ATTRIBUTE_6',
6146: p_severity => G_SEV3_ERROR ,
6154: END IF;
6155:
6156: -- set the message, attribute_8 is invalid
6157:
6158: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6159: (p_app_short_name => 'MSC',
6160: p_error_code => 'MSC_PP_INVALID_VALUE',
6161: p_message_text => lv_message_text,
6162: p_error_text => lv_error_text,
6169:
6170: -- Derive SR_LEVEL_PK - attribute_7 from msd_level_values
6171: -- or msd_st_level_values
6172:
6173: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6174: (p_table_name => 'MSD_ST_CS_DATA',
6175: p_level_val_col => 'ATTRIBUTE_8',
6176: p_level_name_col => 'ATTRIBUTE_6_VAL',
6177: p_level_pk_col => 'ATTRIBUTE_7',
6186: END IF;
6187:
6188:
6189: -- Set the message
6190: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6191: (p_app_short_name => 'MSC',
6192: p_error_code => 'MSC_PP_INVALID_VALUE',
6193: p_message_text => lv_message_text,
6194: p_error_text => lv_error_text,
6201:
6202: -- Derive the level_id , error out if invalid level name
6203: -- attribute_10 (org_level_id)
6204:
6205: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6206: (p_table_name => 'MSD_ST_CS_DATA',
6207: p_level_name_col => 'ATTRIBUTE_10_VAL',
6208: p_level_id_col => 'ATTRIBUTE_10',
6209: p_severity => G_SEV3_ERROR ,
6217: END IF;
6218:
6219: -- set the message, attribute_12 is invalid
6220:
6221: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6222: (p_app_short_name => 'MSC',
6223: p_error_code => 'MSC_PP_INVALID_VALUE',
6224: p_message_text => lv_message_text,
6225: p_error_text => lv_error_text,
6232:
6233: -- Derive SR_LEVEL_PK - attribute_11 from msd_level_values
6234: -- or msd_st_level_values
6235:
6236: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6237: (p_table_name => 'MSD_ST_CS_DATA',
6238: p_level_val_col => 'ATTRIBUTE_12',
6239: p_level_name_col => 'ATTRIBUTE_10_VAL',
6240: p_level_pk_col => 'ATTRIBUTE_11',
6248: RAISE ex_logging_err;
6249: END IF;
6250:
6251: /* -- Set the message
6252: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6253: (p_app_short_name => 'MSC',
6254: p_error_code => 'MSC_PP_INVALID_VALUE',
6255: p_message_text => lv_message_text,
6256: p_error_text => lv_error_text,
6263:
6264: -- Derive the level_id , error out if invalid level name
6265: --attribute_14 (cus_level_id)
6266:
6267: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6268: (p_table_name => 'MSD_ST_CS_DATA',
6269: p_level_name_col => 'ATTRIBUTE_14_VAL',
6270: p_level_id_col => 'ATTRIBUTE_14',
6271: p_severity => G_SEV3_ERROR ,
6288: ||' AND batch_id = :lv_batch_id'
6289: ||' AND sr_instance_code = :p_instance_code';
6290:
6291: IF lv_debug THEN
6292: msc_st_util.log_message(lv_sql_stmt);
6293: END IF;
6294:
6295: EXECUTE IMMEDIATE lv_sql_stmt
6296: USING lv_batch_id,
6298:
6299:
6300: -- set the message, attribute_16 is invalid
6301:
6302: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6303: (p_app_short_name => 'MSC',
6304: p_error_code => 'MSC_PP_INVALID_VALUE',
6305: p_message_text => lv_message_text,
6306: p_error_text => lv_error_text,
6313:
6314: -- Derive SR_LEVEL_PK - attribute_15 from msd_level_values
6315: -- or msd_st_level_values
6316:
6317: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6318: (p_table_name => 'MSD_ST_CS_DATA',
6319: p_level_val_col => 'ATTRIBUTE_16',
6320: p_level_name_col => 'ATTRIBUTE_14_VAL',
6321: p_level_pk_col => 'ATTRIBUTE_15',
6331:
6332:
6333:
6334: -- Set the message
6335: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6336: (p_app_short_name => 'MSC',
6337: p_error_code => 'MSC_PP_INVALID_VALUE',
6338: p_message_text => lv_message_text,
6339: p_error_text => lv_error_text,
6346:
6347: -- Derive the level_id , error out if invalid level name
6348: --attribute_18 (rep_level_id)
6349:
6350: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6351: (p_table_name => 'MSD_ST_CS_DATA',
6352: p_level_name_col => 'ATTRIBUTE_18_VAL',
6353: p_level_id_col => 'ATTRIBUTE_18',
6354: p_severity => G_SEV3_ERROR ,
6363:
6364:
6365: -- set the message, attribute_20 is invalid
6366:
6367: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6368: (p_app_short_name => 'MSC',
6369: p_error_code => 'MSC_PP_INVALID_VALUE',
6370: p_message_text => lv_message_text,
6371: p_error_text => lv_error_text,
6378:
6379: -- Derive SR_LEVEL_PK - attribute_19 from msd_level_values
6380: -- or msd_st_level_values
6381:
6382: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6383: (p_table_name => 'MSD_ST_CS_DATA',
6384: p_level_val_col => 'ATTRIBUTE_20',
6385: p_level_name_col => 'ATTRIBUTE_18_VAL',
6386: p_level_pk_col => 'ATTRIBUTE_19',
6394: RAISE ex_logging_err;
6395: END IF;
6396:
6397: -- Set the message
6398: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6399: (p_app_short_name => 'MSC',
6400: p_error_code => 'MSC_PP_INVALID_VALUE',
6401: p_message_text => lv_message_text,
6402: p_error_text => lv_error_text,
6410:
6411: -- Derive the level_id , error out if invalid level name
6412: --attribute_22 (chn_level_id)
6413:
6414: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6415: (p_table_name => 'MSD_ST_CS_DATA',
6416: p_level_name_col => 'ATTRIBUTE_22_VAL',
6417: p_level_id_col => 'ATTRIBUTE_22',
6418: p_severity => G_SEV3_ERROR ,
6426: END IF;
6427:
6428: -- set the message, attribute_24 is invalid
6429:
6430: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6431: (p_app_short_name => 'MSC',
6432: p_error_code => 'MSC_PP_INVALID_VALUE',
6433: p_message_text => lv_message_text,
6434: p_error_text => lv_error_text,
6441:
6442: -- Derive SR_LEVEL_PK - attribute_23 from msd_level_values
6443: -- or msd_st_level_values
6444:
6445: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6446: (p_table_name => 'MSD_ST_CS_DATA',
6447: p_level_val_col => 'ATTRIBUTE_24',
6448: p_level_name_col => 'ATTRIBUTE_22_VAL',
6449: p_level_pk_col => 'ATTRIBUTE_23',
6457: RAISE ex_logging_err;
6458: END IF;
6459:
6460: -- Set the message
6461: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6462: (p_app_short_name => 'MSC',
6463: p_error_code => 'MSC_PP_INVALID_VALUE',
6464: p_message_text => lv_message_text,
6465: p_error_text => lv_error_text,
6473:
6474: -- Derive the level_id , error out if invalid level name
6475: --attribute_26 (ud1_level_id)
6476:
6477: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6478: (p_table_name => 'MSD_ST_CS_DATA',
6479: p_level_name_col => 'ATTRIBUTE_26_VAL',
6480: p_level_id_col => 'ATTRIBUTE_26',
6481: p_severity => G_SEV3_ERROR ,
6489: END IF;
6490:
6491: -- set the message, attribute_28 is invalid
6492:
6493: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6494: (p_app_short_name => 'MSC',
6495: p_error_code => 'MSC_PP_INVALID_VALUE',
6496: p_message_text => lv_message_text,
6497: p_error_text => lv_error_text,
6504:
6505: -- Derive SR_LEVEL_PK - attribute_27 from msd_level_values
6506: -- or msd_st_level_values
6507:
6508: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6509: (p_table_name => 'MSD_ST_CS_DATA',
6510: p_level_val_col => 'ATTRIBUTE_28',
6511: p_level_name_col => 'ATTRIBUTE_26_VAL',
6512: p_level_pk_col => 'ATTRIBUTE_27',
6520: RAISE ex_logging_err;
6521: END IF;
6522:
6523: -- Set the message
6524: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6525: (p_app_short_name => 'MSC',
6526: p_error_code => 'MSC_PP_INVALID_VALUE',
6527: p_message_text => lv_message_text,
6528: p_error_text => lv_error_text,
6535:
6536: -- Derive the level_id , error out if invalid level name
6537: --attribute_30 (ud2_level_id)
6538:
6539: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6540: (p_table_name => 'MSD_ST_CS_DATA',
6541: p_level_name_col => 'ATTRIBUTE_30_VAL',
6542: p_level_id_col => 'ATTRIBUTE_30',
6543: p_severity => G_SEV3_ERROR,
6551: END IF;
6552:
6553: -- set the message, attribute_32 is invalid
6554:
6555: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6556: (p_app_short_name => 'MSC',
6557: p_error_code => 'MSC_PP_INVALID_VALUE',
6558: p_message_text => lv_message_text,
6559: p_error_text => lv_error_text,
6566:
6567: -- Derive SR_LEVEL_PK - attribute_31 from msd_level_values
6568: -- or msd_st_level_values
6569:
6570: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6571: (p_table_name => 'MSD_ST_CS_DATA',
6572: p_level_val_col => 'ATTRIBUTE_32',
6573: p_level_name_col => 'ATTRIBUTE_30_VAL',
6574: p_level_pk_col => 'ATTRIBUTE_31',
6583: END IF;
6584:
6585: -- Demand Class Changes for Custom Stream data starts
6586: -- Set the message
6587: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6588: (p_app_short_name => 'MSC',
6589: p_error_code => 'MSC_PP_INVALID_VALUE',
6590: p_message_text => lv_message_text,
6591: p_error_text => lv_error_text,
6598:
6599: -- Derive the level_id , error out if invalid level name
6600: -- attribute_50 (demand_class_level_id)
6601:
6602: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6603: (p_table_name => 'MSD_ST_CS_DATA',
6604: p_level_name_col => 'ATTRIBUTE_50_VAL',
6605: p_level_id_col => 'ATTRIBUTE_50',
6606: p_severity => G_SEV3_ERROR ,
6614: END IF;
6615:
6616: -- set the message, attribute_52 is invalid
6617:
6618: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6619: (p_app_short_name => 'MSC',
6620: p_error_code => 'MSC_PP_INVALID_VALUE',
6621: p_message_text => lv_message_text,
6622: p_error_text => lv_error_text,
6629:
6630: -- Derive SR_LEVEL_PK - attribute_51 from msd_level_values
6631: -- or msd_st_level_values
6632:
6633: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6634: (p_table_name => 'MSD_ST_CS_DATA',
6635: p_level_val_col => 'ATTRIBUTE_52',
6636: p_level_name_col => 'ATTRIBUTE_50_VAL',
6637: p_level_pk_col => 'ATTRIBUTE_51',
6654:
6655:
6656: IF (lv_instance_type = G_INS_OTHER) THEN
6657:
6658: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6659: (p_app_short_name => 'MSC',
6660: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
6661: p_message_text => lv_message_text,
6662: p_error_text => lv_error_text,
6730: RAISE ex_logging_err;
6731: END IF;
6732:
6733: -- Set the process flag as Valid and populate instance_id
6734: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
6735: (p_table_name => 'MSD_ST_CS_DATA',
6736: p_instance_id => p_instance_id,
6737: p_instance_code => p_instance_code,
6738: p_process_flag => G_VALID,
6745: END IF;
6746:
6747: -- Inserting all the errored out records into MSC_ERRORS:
6748:
6749: lv_return := MSC_ST_UTIL.LOG_ERROR
6750: (p_table_name => 'MSD_ST_CS_DATA',
6751: p_instance_code => p_instance_code,
6752: p_row => lv_column_names,
6753: p_severity => G_SEV_ERROR,
6755: p_message_text => NULL,
6756: p_batch_id => lv_batch_id);
6757:
6758: IF(lv_return <> 0) THEN
6759: msc_st_util.log_message(lv_error_text);
6760: END IF;
6761: COMMIT;
6762:
6763: EXCEPTION
6765: ROLLBACK;
6766:
6767: ERRBUF := lv_error_text;
6768: RETCODE := G_WARNING;
6769: msc_st_util.log_message(lv_error_text);
6770:
6771: WHEN OTHERS THEN
6772: ROLLBACK;
6773:
6772: ROLLBACK;
6773:
6774: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_CS_DATA'||'('
6775: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
6776: msc_st_util.log_message(lv_error_text);
6777: ERRBUF := lv_error_text;
6778: RETCODE := G_WARNING;
6779:
6780: END LOAD_CS_DATA;
6862:
6863:
6864: -- Set the message
6865:
6866: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6867: (p_app_short_name => 'MSC',
6868: p_error_code => 'MSC_PP_INVALID_VALUE',
6869: p_message_text => lv_message_text,
6870: p_error_text => lv_error_text,
6877:
6878: -- Derive the org_level_id , error out if invalid level name
6879:
6880:
6881: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6882: (p_table_name => 'MSD_ST_PRICE_LIST',
6883: p_level_name_col => 'ORGANIZATION_LVL',
6884: p_level_id_col => 'ORGANIZATION_LVL_ID',
6885: p_severity => G_SEV_ERROR, -- Against Bug#2413920
6893: END IF;
6894:
6895: -- set the message, ORGANIZATION_LVL is invalid
6896:
6897: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6898: (p_app_short_name => 'MSC',
6899: p_error_code => 'MSC_PP_INVALID_VALUE',
6900: p_message_text => lv_message_text,
6901: p_error_text => lv_error_text,
6907: END IF;
6908:
6909: -- Derive SR_ORGANIZATION_LVL_PK
6910:
6911: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6912: (p_table_name => 'MSD_ST_PRICE_LIST',
6913: p_level_val_col => 'SR_ORGANIZATION_LVL_VAL',
6914: p_level_name_col => 'ORGANIZATION_LVL',
6915: p_level_pk_col => 'SR_ORGANIZATION_LVL_PK',
6926:
6927:
6928: -- Set the message
6929:
6930: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6931: (p_app_short_name => 'MSC',
6932: p_error_code => 'MSC_PP_INVALID_VALUE',
6933: p_message_text => lv_message_text,
6934: p_error_text => lv_error_text,
6941:
6942: -- Derive the PRODUCT_LVL_ID, error out if invalid level name
6943:
6944:
6945: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
6946: (p_table_name => 'MSD_ST_PRICE_LIST',
6947: p_level_name_col => 'PRODUCT_LVL',
6948: p_level_id_col => 'PRODUCT_LVL_ID',
6949: p_severity => G_SEV_ERROR, -- Against Bug#2413920
6957: END IF;
6958:
6959: -- set the message, PRODUCT_LVL is invalid
6960:
6961: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6962: (p_app_short_name => 'MSC',
6963: p_error_code => 'MSC_PP_INVALID_VALUE',
6964: p_message_text => lv_message_text,
6965: p_error_text => lv_error_text,
6971: END IF;
6972:
6973: -- Derive SR_PRODUCT_LVL_PK
6974:
6975: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
6976: (p_table_name => 'MSD_ST_PRICE_LIST',
6977: p_level_val_col => 'SR_PRODUCT_LVL_VAL',
6978: p_level_name_col => 'PRODUCT_LVL',
6979: p_level_pk_col => 'SR_PRODUCT_LVL_PK',
6990:
6991:
6992: -- Set the message
6993:
6994: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
6995: (p_app_short_name => 'MSC',
6996: p_error_code => 'MSC_PP_INVALID_VALUE',
6997: p_message_text => lv_message_text,
6998: p_error_text => lv_error_text,
7005:
7006: -- Derive the SALESCHANNEL_LVL_ID, error out if invalid level name
7007:
7008:
7009: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7010: (p_table_name => 'MSD_ST_PRICE_LIST',
7011: p_level_name_col => 'SALESCHANNEL_LVL',
7012: p_level_id_col => 'SALESCHANNEL_LVL_ID',
7013: p_severity => G_SEV_ERROR, -- Against Bug#2413920
7021: END IF;
7022:
7023: -- set the message, SALESCHANNEL_LVL is invalid
7024:
7025: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7026: (p_app_short_name => 'MSC',
7027: p_error_code => 'MSC_PP_INVALID_VALUE',
7028: p_message_text => lv_message_text,
7029: p_error_text => lv_error_text,
7035: END IF;
7036:
7037: -- Derive SR_SALESCHANNEL_LVL_PK
7038:
7039: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7040: (p_table_name => 'MSD_ST_PRICE_LIST',
7041: p_level_val_col => 'SR_SALESCHANNEL_LVL_VAL',
7042: p_level_name_col => 'SALESCHANNEL_LVL',
7043: p_level_pk_col => 'SR_SALESCHANNEL_LVL_PK',
7053: END IF;
7054:
7055: -- Set the message
7056:
7057: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7058: (p_app_short_name => 'MSC',
7059: p_error_code => 'MSC_PP_INVALID_VALUE',
7060: p_message_text => lv_message_text,
7061: p_error_text => lv_error_text,
7068:
7069: -- Derive the SALESREP_LVL_ID, error out if invalid level name
7070:
7071:
7072: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7073: (p_table_name => 'MSD_ST_PRICE_LIST',
7074: p_level_name_col => 'SALES_REP_LVL',
7075: p_level_id_col => 'SALES_REP_LVL_ID',
7076: p_severity => G_SEV_ERROR, -- Against Bug#2413920
7084: END IF;
7085:
7086: -- set the message, SALESREP_LVL is invalid
7087:
7088: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7089: (p_app_short_name => 'MSC',
7090: p_error_code => 'MSC_PP_INVALID_VALUE',
7091: p_message_text => lv_message_text,
7092: p_error_text => lv_error_text,
7098: END IF;
7099:
7100: -- Derive SR_SALESREP_LVL_PK
7101:
7102: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7103: (p_table_name => 'MSD_ST_PRICE_LIST',
7104: p_level_val_col => 'SR_SALES_REP_LVL_VAL',
7105: p_level_name_col => 'SALES_REP_LVL',
7106: p_level_pk_col => 'SR_SALES_REP_LVL_PK',
7116: END IF;
7117:
7118: -- Set the message
7119:
7120: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7121: (p_app_short_name => 'MSC',
7122: p_error_code => 'MSC_PP_INVALID_VALUE',
7123: p_message_text => lv_message_text,
7124: p_error_text => lv_error_text,
7130: END IF;
7131:
7132: -- Derive the GEOGRAPHY_LVL_ID, error out if invalid level name
7133:
7134: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7135: (p_table_name => 'MSD_ST_PRICE_LIST',
7136: p_level_name_col => 'GEOGRAPHY_LVL',
7137: p_level_id_col => 'GEOGRAPHY_LVL_ID',
7138: p_severity => G_SEV_ERROR, -- Against Bug#2413920
7146: END IF;
7147:
7148: -- set the message, GEOGRAPHY_LVL is invalid
7149:
7150: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7151: (p_app_short_name => 'MSC',
7152: p_error_code => 'MSC_PP_INVALID_VALUE',
7153: p_message_text => lv_message_text,
7154: p_error_text => lv_error_text,
7160: END IF;
7161:
7162: -- Derive SR_GEOGRAPHY_LVL_PK
7163:
7164: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7165: (p_table_name => 'MSD_ST_PRICE_LIST',
7166: p_level_val_col => 'SR_GEOGRAPHY_LVL_VAL',
7167: p_level_name_col => 'GEOGRAPHY_LVL',
7168: p_level_pk_col => 'SR_GEOGRAPHY_LVL_PK',
7178: END IF;
7179:
7180: -- Set the message
7181:
7182: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7183: (p_app_short_name => 'MSC',
7184: p_error_code => 'MSC_PP_INVALID_VALUE',
7185: p_message_text => lv_message_text,
7186: p_error_text => lv_error_text,
7192: END IF;
7193:
7194: -- Derive the USER_DEFINED1_LVL_ID, error out if invalid level name
7195:
7196: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7197: (p_table_name => 'MSD_ST_PRICE_LIST',
7198: p_level_name_col => 'USER_DEFINED1_LVL',
7199: p_level_id_col => 'USER_DEFINED1_LVL_ID',
7200: p_severity => G_SEV3_ERROR ,
7209:
7210:
7211: -- set the message, USER_DEFINED1_LVL is invalid
7212:
7213: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7214: (p_app_short_name => 'MSC',
7215: p_error_code => 'MSC_PP_INVALID_VALUE',
7216: p_message_text => lv_message_text,
7217: p_error_text => lv_error_text,
7223: END IF;
7224:
7225: -- Derive SR_USER_DEFINED1_LVL_PK
7226:
7227: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7228: (p_table_name => 'MSD_ST_PRICE_LIST',
7229: p_level_val_col => 'SR_USER_DEFINED1_LVL_VAL',
7230: p_level_name_col => 'USER_DEFINED1_LVL',
7231: p_level_pk_col => 'SR_USER_DEFINED1_LVL_PK',
7240: END IF;
7241:
7242: -- Set the message
7243:
7244: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7245: (p_app_short_name => 'MSC',
7246: p_error_code => 'MSC_PP_INVALID_VALUE',
7247: p_message_text => lv_message_text,
7248: p_error_text => lv_error_text,
7254: END IF;
7255:
7256: -- Derive the USER_DEFINED2_LVL_ID, error out if invalid level name
7257:
7258: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7259: (p_table_name => 'MSD_ST_PRICE_LIST',
7260: p_level_name_col => 'USER_DEFINED2_LVL',
7261: p_level_id_col => 'USER_DEFINED2_LVL_ID',
7262: p_severity => G_SEV3_ERROR ,
7271:
7272:
7273: -- set the message, USER_DEFINED2_LVL is invalid
7274:
7275: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7276: (p_app_short_name => 'MSC',
7277: p_error_code => 'MSC_PP_INVALID_VALUE',
7278: p_message_text => lv_message_text,
7279: p_error_text => lv_error_text,
7285: END IF;
7286:
7287: -- Derive SR_USER_DEFINED2_LVL_PK
7288:
7289: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7290: (p_table_name => 'MSD_ST_PRICE_LIST',
7291: p_level_val_col => 'SR_USER_DEFINED2_LVL_VAL',
7292: p_level_name_col => 'USER_DEFINED2_LVL',
7293: p_level_pk_col => 'SR_USER_DEFINED2_LVL_PK',
7303:
7304:
7305: -- Set the message
7306:
7307: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7308: (p_app_short_name => 'MSC',
7309: p_error_code => 'MSC_PP_INVALID_VALUE',
7310: p_message_text => lv_message_text,
7311: p_error_text => lv_error_text,
7317: END IF;
7318:
7319: -- Derive the DEMAND_CLASS_LVL_ID, error out if invalid level name
7320:
7321: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
7322: (p_table_name => 'MSD_ST_PRICE_LIST',
7323: p_level_name_col => 'DEMAND_CLASS_LVL',
7324: p_level_id_col => 'DEMAND_CLASS_LVL_ID',
7325: p_severity => G_SEV3_ERROR, -- Against Bug#2413920
7333: END IF;
7334:
7335: -- set the message, DEMAND_CLASS_LVL is invalid
7336:
7337: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7338: (p_app_short_name => 'MSC',
7339: p_error_code => 'MSC_PP_INVALID_VALUE',
7340: p_message_text => lv_message_text,
7341: p_error_text => lv_error_text,
7347: END IF;
7348:
7349: -- Derive SR_DEMAND_CLASS_LVL_PK
7350:
7351: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
7352: (p_table_name => 'MSD_ST_PRICE_LIST',
7353: p_level_val_col => 'SR_DEMAND_CLASS_LVL_VAL',
7354: p_level_name_col => 'DEMAND_CLASS_LVL',
7355: p_level_pk_col => 'SR_DEMAND_CLASS_LVL_PK',
7366:
7367: -- Price List UOM Change starts
7368: -- Set the message
7369: /* Comment Start :The code below has been commented against Bug# 3796659 */
7370: /* lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7371: (p_app_short_name => 'MSC',
7372: p_error_code => 'MSC_PP_INVALID_VALUE',
7373: p_message_text => lv_message_text,
7374: p_error_text => lv_error_text,
7407: CLOSE instance_type;
7408:
7409: IF (lv_instance_type = G_INS_OTHER) THEN
7410:
7411: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7412: (p_app_short_name => 'MSC',
7413: p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
7414: p_message_text => lv_message_text,
7415: p_error_text => lv_error_text,
7470: RAISE ex_logging_err;
7471: END IF;
7472:
7473: -- Set the process flag as Valid and populate instance_id
7474: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
7475: (p_table_name => 'MSD_ST_PRICE_LIST',
7476: p_instance_id => p_instance_id,
7477: p_instance_code => p_instance_code,
7478: p_process_flag => G_VALID,
7485: END IF;
7486:
7487: -- Inserting all the errored out records into MSC_ERRORS:
7488:
7489: lv_return := MSC_ST_UTIL.LOG_ERROR
7490: (p_table_name => 'MSD_ST_PRICE_LIST',
7491: p_instance_code => p_instance_code,
7492: p_row => lv_column_names,
7493: p_severity => G_SEV_ERROR,
7495: p_message_text => NULL,
7496: p_batch_id => lv_batch_id);
7497:
7498: IF(lv_return <> 0) THEN
7499: msc_st_util.log_message(lv_error_text);
7500: END IF;
7501: COMMIT;
7502:
7503: EXCEPTION
7505: ROLLBACK;
7506:
7507: ERRBUF := lv_error_text;
7508: RETCODE := G_WARNING;
7509: msc_st_util.log_message(lv_error_text);
7510:
7511: WHEN OTHERS THEN
7512: ROLLBACK;
7513:
7512: ROLLBACK;
7513:
7514: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_PRICE_LIST'||'('
7515: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
7516: msc_st_util.log_message(lv_error_text);
7517: ERRBUF := lv_error_text;
7518: RETCODE := G_WARNING;
7519:
7520: END LOAD_PRICE_LIST;
7582: WHERE rowid = lb_rowid(j);
7583:
7584: -- set the message
7585:
7586: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7587: (p_app_short_name => 'MSC',
7588: p_error_code => 'MSC_PP_COL_VAL_NULL',
7589: p_message_text => lv_message_text,
7590: p_error_text => lv_error_text,
7622: ||' AND batch_id = :lv_batch_id'
7623: ||' AND sr_instance_code = :p_instance_code';
7624:
7625: IF lv_debug THEN
7626: msc_st_util.log_message(lv_sql_stmt);
7627: END IF;
7628: EXECUTE IMMEDIATE lv_sql_stmt
7629: USING lv_batch_id,
7630: p_instance_code;
7630: p_instance_code;
7631:
7632:
7633: -- Set the message
7634: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7635: (p_app_short_name => 'MSC',
7636: p_error_code => 'MSC_PP_INVALID_VALUE',
7637: p_message_text => lv_message_text,
7638: p_error_text => lv_error_text,
7644: END IF;
7645:
7646: -- Derive sr_item_pk from msd_st_level_values and msd_level_values
7647:
7648: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
7649: (p_table_name => 'MSD_ST_UOM_CONVERSIONS',
7650: p_column_name => 'ITEM',
7651: p_pk_col_name => 'SR_ITEM_PK',
7652: p_instance_code => p_instance_code,
7675: RAISE ex_logging_err;
7676: END IF;
7677:
7678: -- Set the process flag as Valid and populate instance_id
7679: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
7680: (p_table_name => 'MSD_ST_UOM_CONVERSIONS',
7681: p_instance_id => p_instance_id,
7682: p_instance_code => p_instance_code,
7683: p_process_flag => G_VALID,
7690: END IF;
7691:
7692: -- Inserting all the errored out records into MSC_ERRORS:
7693:
7694: lv_return := MSC_ST_UTIL.LOG_ERROR
7695: (p_table_name => 'MSD_ST_UOM_CONVERSIONS',
7696: p_instance_code => p_instance_code,
7697: p_row => lv_column_names,
7698: p_severity => G_SEV_ERROR,
7700: p_message_text => NULL,
7701: p_batch_id => lv_batch_id);
7702:
7703: IF(lv_return <> 0) THEN
7704: msc_st_util.log_message(lv_error_text);
7705: END IF;
7706: COMMIT;
7707:
7708: EXCEPTION
7710: ROLLBACK;
7711:
7712: ERRBUF := lv_error_text;
7713: RETCODE := G_WARNING;
7714: msc_st_util.log_message(lv_error_text);
7715:
7716: WHEN OTHERS THEN
7717: ROLLBACK;
7718:
7717: ROLLBACK;
7718:
7719: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_UOM_CONV '||'('
7720: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
7721: msc_st_util.log_message(lv_error_text);
7722: ERRBUF := lv_error_text;
7723: RETCODE := G_WARNING;
7724:
7725: END LOAD_UOM_CONV ;
7784: WHERE rowid = lb_rowid(j);
7785:
7786: -- set the message
7787:
7788: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7789: (p_app_short_name => 'MSC',
7790: p_error_code => 'MSC_PP_COL_VAL_NULL',
7791: p_message_text => lv_message_text,
7792: p_error_text => lv_error_text,
7809: ||' AND process_flag = '||G_IN_PROCESS
7810: ||' AND sr_instance_code = :p_instance_code';
7811:
7812: IF lv_debug THEN
7813: msc_st_util.log_message(lv_sql_stmt);
7814: END IF;
7815: EXECUTE IMMEDIATE lv_sql_stmt
7816: USING p_instance_code;
7817:
7815: EXECUTE IMMEDIATE lv_sql_stmt
7816: USING p_instance_code;
7817:
7818: -- Set the process flag as Valid and populate instance_id
7819: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
7820: (p_table_name => 'MSD_ST_SETUP_PARAMETERS',
7821: p_instance_id => p_instance_id,
7822: p_instance_code => p_instance_code,
7823: p_process_flag => G_VALID,
7860: AND parameter_value = c_rec.parameter_value;
7861:
7862: EXCEPTION
7863: WHEN OTHERS THEN
7864: msc_st_util.log_message(SQLERRM);
7865: END ;
7866: END LOOP;
7867:
7868: -- Inserting all the errored out records into MSC_ERRORS:
7866: END LOOP;
7867:
7868: -- Inserting all the errored out records into MSC_ERRORS:
7869:
7870: lv_return := MSC_ST_UTIL.LOG_ERROR
7871: (p_table_name => 'MSD_ST_SETUP_PARAMETERS',
7872: p_instance_code => p_instance_code,
7873: p_row => lv_column_names,
7874: p_severity => G_SEV_ERROR,
7875: p_error_text => lv_error_text,
7876: p_message_text => NULL);
7877:
7878: IF(lv_return <> 0) THEN
7879: msc_st_util.log_message(lv_error_text);
7880: END IF;
7881: COMMIT;
7882:
7883: EXCEPTION
7885: ROLLBACK;
7886:
7887: ERRBUF := lv_error_text;
7888: RETCODE := G_WARNING;
7889: msc_st_util.log_message(lv_error_text);
7890:
7891: WHEN OTHERS THEN
7892: ROLLBACK;
7893:
7892: ROLLBACK;
7893:
7894: lv_error_text := substr('MSD_CL_PRE_PROCESS.LOAD_SETUP_PARAMETER '||'('
7895: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
7896: msc_st_util.log_message(lv_error_text);
7897: ERRBUF := lv_error_text;
7898: RETCODE := G_WARNING;
7899:
7900: END LOAD_SETUP_PARAMETER ;
7958: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
7959: ||' AND rownum <= '||lv_batch_size;
7960:
7961: IF lv_debug THEN
7962: msc_st_util.log_message(lv_sql_stmt);
7963: END IF;
7964:
7965: EXECUTE IMMEDIATE lv_sql_stmt
7966: USING lv_batch_id,
7983: WHERE rowid = lb_rowid(j);
7984:
7985:
7986: -- Set the message
7987: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7988: (p_app_short_name => 'MSC',
7989: p_error_code => 'MSC_PP_INVALID_VALUE',
7990: p_message_text => lv_message_text,
7991: p_error_text => lv_error_text,
7997: END IF;
7998:
7999: -- Derive INVENTORY_ITEM_ID from msd_st_level_values and msd_level_values
8000:
8001: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
8002: (p_table_name => 'MSD_ST_ITEM_RELATIONSHIPS',
8003: p_column_name => 'INVENTORY_ITEM',
8004: p_pk_col_name => 'INVENTORY_ITEM_ID',
8005: p_level_id => 1,
8014: RAISE ex_logging_err;
8015: END IF;
8016:
8017: -- Set the message
8018: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8019: (p_app_short_name => 'MSC',
8020: p_error_code => 'MSC_PP_INVALID_VALUE',
8021: p_message_text => lv_message_text,
8022: p_error_text => lv_error_text,
8028: END IF;
8029:
8030: -- Derive RELATED_ITEM_ID from msd_st_level_values and msd_level_values
8031:
8032: lv_return := MSC_ST_UTIL.DERIVE_SR_PK
8033: (p_table_name => 'MSD_ST_ITEM_RELATIONSHIPS',
8034: p_column_name => 'RELATED_ITEM',
8035: p_pk_col_name => 'RELATED_ITEM_ID',
8036: p_level_id => 1,
8053: ||' AND t1.batch_id = :lv_batch_id'
8054: ||' AND t1.sr_instance_code = :p_instance_code';
8055:
8056: IF lv_debug THEN
8057: msc_st_util.log_message(lv_sql_stmt);
8058: END IF;
8059:
8060: EXECUTE IMMEDIATE lv_sql_stmt
8061: USING lv_batch_id,
8077: ||' AND t1.batch_id = :lv_batch_id'
8078: ||' AND t1.sr_instance_code = :p_instance_code';
8079:
8080: IF lv_debug THEN
8081: msc_st_util.log_message(lv_sql_stmt);
8082: END IF;
8083:
8084: EXECUTE IMMEDIATE lv_sql_stmt
8085: USING lv_batch_id,
8085: USING lv_batch_id,
8086: p_instance_code ;
8087:
8088: -- Set the message
8089: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8090: (p_app_short_name => 'MSC',
8091: p_error_code => 'MSC_PP_COL_VAL_NULL',
8092: p_message_text => lv_message_text,
8093: p_error_text => lv_error_text,
8110: ||' AND batch_id = :lv_batch_id'
8111: ||' AND sr_instance_code = :p_instance_code';
8112:
8113: IF lv_debug THEN
8114: msc_st_util.log_message(lv_sql_stmt);
8115: END IF;
8116:
8117: EXECUTE IMMEDIATE lv_sql_stmt
8118: USING lv_batch_id,
8133: RAISE ex_logging_err;
8134: END IF;
8135:
8136: -- Set the process flag as Valid and populate instance_id
8137: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
8138: (p_table_name => 'MSD_ST_ITEM_RELATIONSHIPS',
8139: p_instance_id => p_instance_id,
8140: p_instance_code => p_instance_code,
8141: p_process_flag => G_VALID,
8148: END IF;
8149:
8150: -- Inserting all the errored out records into MSC_ERRORS:
8151:
8152: lv_return := MSC_ST_UTIL.LOG_ERROR
8153: (p_table_name => 'MSD_ST_ITEM_RELATIONSHIPS',
8154: p_instance_code => p_instance_code,
8155: p_row => lv_column_names,
8156: p_severity => G_SEV_ERROR,
8168: EXCEPTION
8169: WHEN ex_logging_err THEN
8170: ROLLBACK;
8171:
8172: msc_st_util.log_message(lv_error_text);
8173:
8174: WHEN OTHERS THEN
8175: ROLLBACK;
8176:
8175: ROLLBACK;
8176:
8177: lv_error_text := substr('LOAD_ITEM_RELATIONSHIP '||'('
8178: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
8179: msc_st_util.log_message(lv_error_text);
8180:
8181: END LOAD_ITEM_RELATIONSHIP;
8182:
8183: /*==========================================================================+
8246: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
8247: ||' AND rownum <= '||lv_batch_size;
8248:
8249: IF lv_debug THEN
8250: msc_st_util.log_message(lv_sql_stmt);
8251: END IF;
8252:
8253: EXECUTE IMMEDIATE lv_sql_stmt
8254: USING lv_batch_id,
8271: WHERE rowid = lb_rowid(j);
8272:
8273:
8274: -- Set the message LEVEL_NAME is invalid
8275: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8276: (p_app_short_name => 'MSC',
8277: p_error_code => 'MSC_PP_INVALID_VALUE',
8278: p_message_text => lv_message_text,
8279: p_error_text => lv_error_text,
8284: RAISE ex_logging_err;
8285: END IF;
8286:
8287: -- Derive the level_id , error out if invalid level name
8288: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
8289: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8290: p_level_name_col => 'LEVEL_NAME',
8291: p_level_id_col => 'LEVEL_ID',
8292: p_severity => G_SEV_ERROR ,
8296: p_error_text => lv_error_text);
8297:
8298:
8299: -- Set the message ORG_LEVEL_NAME is invalid
8300: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8301: (p_app_short_name => 'MSC',
8302: p_error_code => 'MSC_PP_INVALID_VALUE',
8303: p_message_text => lv_message_text,
8304: p_error_text => lv_error_text,
8309: RAISE ex_logging_err;
8310: END IF;
8311:
8312: -- Derive the level_id , error out if invalid org level name
8313: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_ID
8314: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8315: p_level_name_col => 'ORG_LEVEL_NAME',
8316: p_level_id_col => 'ORG_LEVEL_ID',
8317: p_severity => G_SEV_ERROR ,
8321: p_error_text => lv_error_text);
8322:
8323:
8324: -- Set the message
8325: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8326: (p_app_short_name => 'MSC',
8327: p_error_code => 'MSC_PP_INVALID_VALUE',
8328: p_message_text => lv_message_text,
8329: p_error_text => lv_error_text,
8364:
8365: END IF;
8366:
8367: -- set the message, LEVEL_VALUE is invalid
8368: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8369: (p_app_short_name => 'MSC',
8370: p_error_code => 'MSC_PP_INVALID_VALUE',
8371: p_message_text => lv_message_text,
8372: p_error_text => lv_error_text,
8378: END IF;
8379:
8380: -- Derive SR_LEVEL_PK from msd_level_values or msd_st_level_values
8381:
8382: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
8383: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8384: p_level_val_col => 'LEVEL_VALUE',
8385: p_level_name_col => 'LEVEL_NAME',
8386: p_level_pk_col => 'SR_LEVEL_PK',
8394: RAISE ex_logging_err;
8395: END IF;
8396:
8397: -- set the message, ORG_LEVEL_VALUE is invalid
8398: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
8399: (p_app_short_name => 'MSC',
8400: p_error_code => 'MSC_PP_INVALID_VALUE',
8401: p_message_text => lv_message_text,
8402: p_error_text => lv_error_text,
8408: END IF;
8409:
8410: -- Derive ORG_SR_LEVEL_PK from msd_level_values or msd_st_level_values
8411:
8412: lv_return := MSC_ST_UTIL.DERIVE_LEVEL_PK
8413: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8414: p_level_val_col => 'ORG_LEVEL_VALUE',
8415: p_level_name_col => 'ORG_LEVEL_NAME',
8416: p_level_pk_col => 'ORG_SR_LEVEL_PK',
8437: RAISE ex_logging_err;
8438: END IF;
8439:
8440: -- Set the process flag as Valid and populate instance_id
8441: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
8442: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8443: p_instance_id => p_instance_id,
8444: p_instance_code => p_instance_code,
8445: p_process_flag => G_VALID,
8452: END IF;
8453:
8454: -- Inserting all the errored out records into MSC_ERRORS:
8455:
8456: lv_return := MSC_ST_UTIL.LOG_ERROR
8457: (p_table_name => 'MSD_ST_LEVEL_ORG_ASSCNS',
8458: p_instance_code => p_instance_code,
8459: p_row => lv_column_names,
8460: p_severity => G_SEV_ERROR,
8474: EXCEPTION
8475: WHEN ex_logging_err THEN
8476: ROLLBACK;
8477:
8478: msc_st_util.log_message(lv_error_text);
8479:
8480: WHEN OTHERS THEN
8481: ROLLBACK;
8482:
8481: ROLLBACK;
8482:
8483: lv_error_text := substr('LOAD_LEVEL_ORG_ASSCNS '||'('
8484: ||v_sql_stmt||')'|| SQLERRM, 1, 240);
8485: msc_st_util.log_message(lv_error_text);
8486:
8487: END LOAD_LEVEL_ORG_ASSCNS;
8488:
8489: PROCEDURE LAUNCH_DELETE_DUPLICATES (ERRBUF OUT NOCOPY VARCHAR2,
8498: where m2.level_id= m1.level_id and m2.instance = m1.instance
8499: and m2.sr_level_pk = m1.sr_level_pk )
8500: and m1.instance=p_instance_id ;
8501:
8502: msc_st_util.log_message('****no of row deleted*****' || SQL%ROWCOUNT);
8503:
8504: commit ;
8505:
8506: v_sql_stmt := 02;
8515: and m2.SR_PARENT_LEVEL_PK = m1.SR_PARENT_LEVEL_PK
8516: )
8517: and m1.instance=p_instance_id ;
8518:
8519: msc_st_util.log_message('****no of row deleted*****' || SQL%ROWCOUNT);
8520:
8521: commit ;
8522: EXCEPTION
8523: WHEN OTHERS THEN
8523: WHEN OTHERS THEN
8524: ROLLBACK;
8525:
8526: lv_error_text := substr('LAUNCH_DELETE_DUPLICATES '||'('||v_sql_stmt||')'|| SQLERRM, 1, 240);
8527: msc_st_util.log_message(lv_error_text);
8528: END LAUNCH_DELETE_DUPLICATES;
8529:
8530:
8531: