1 PACKAGE MSD_DEM_CTO AUTHID CURRENT_USER AS
2 /* $Header: msddemctos.pls 120.4 2011/01/30 16:28:55 syenamar noship $ */
3
4 /*** GLOBAL VARIABLES ***/
5 G_YES NUMBER := MSD_DEM_COMMON_UTILITIES.C_YES;
6 G_NO NUMBER := MSD_DEM_COMMON_UTILITIES.C_NO;
7
8 /* Session Constants */
9 CS_DEM_SYSDATE DATE := msd_dem_common_utilities.get_demantra_date(sysdate, 1);
10 CS_DEM_CTO_BASE_MODEL VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 1);
11 CS_DEM_CTO_OPTION_CLASS VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 2);
12 CS_DEM_CTO_OPTION VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 3);
13 CS_DEM_LEVEL_ITEM_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'table_label');
14 CS_DEM_LEVEL_ORG_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'table_label');
15 CS_DEM_LEVEL_SITE_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'table_label');
16 CS_DEM_LEVEL_DC_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'table_label');
17 CS_DEM_LEVEL_SC_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'table_label');
18 CS_DEM_LEVEL_ITEM_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'group_table_id'));
19 CS_DEM_LEVEL_ORG_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'group_table_id'));
20 CS_DEM_LEVEL_SITE_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'group_table_id'));
21 CS_DEM_LEVEL_DC_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'group_table_id'));
22 CS_DEM_LEVEL_SC_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'group_table_id'));
23
24
25 /*** PROCEDURES ***
26 *
27 * POPULATE_STAGING_TABLE
28 * COLLECT_MODEL_BOM_COMPONENTS
29 * PURGE_CTO_GL_DATA
30 *
31 *** PROCEDURES ***/
32
33
34 /*
35 * Given the entity name, this procedure runs the query for the entity name.
36 * Usually this procedure will be used to populate the Demantra CTO staging tables.
37 */
38 PROCEDURE POPULATE_STAGING_TABLE (
39 errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY VARCHAR2,
41 p_entity_name IN VARCHAR2,
42 p_sr_instance_id IN NUMBER,
43 p_for_cto IN NUMBER DEFAULT 1);
44
45
46 /*
47 * This procedure populates the table msd_dem_model_bom_components for the base models
48 * available in the sales staging table.
49 */
50 PROCEDURE COLLECT_MODEL_BOM_COMPONENTS (
51 errbuf OUT NOCOPY VARCHAR2,
52 retcode OUT NOCOPY VARCHAR2,
53 p_sr_instance_id IN NUMBER,
54 p_flat_file_load IN NUMBER DEFAULT 2 );
55
56
57 /*
58 * This procedure deletes all data from CTO GL Tables. This should only be run by
59 * an admin user. The user must make sure that the Demantra AS is down before running
60 * the procedure.
61 * The procedure is used when the CTO related profile options have been changed which
62 * result in changes to the bom structure brought into Demantra.
63 *
64 * Parameters -
65 * p_complete_refresh - If 1, then all data from CTO GL tables are deleted
66 * - If 2, do nothing.
67 */
68 PROCEDURE PURGE_CTO_GL_DATA (
69 errbuf OUT NOCOPY VARCHAR2,
70 retcode OUT NOCOPY VARCHAR2,
71 p_complete_refresh IN NUMBER );
72
73
74 END MSD_DEM_CTO;