1 PACKAGE MSD_DEM_SOP AUTHID DEFINER AS
2 /* $Header: msddemsops.pls 120.15.12020000.3 2012/11/01 09:31:01 kkhatri ship $ */
3
4
5 /*** GLOBAL VARIABLES ***/
6 G_YES NUMBER := MSD_DEM_COMMON_UTILITIES.C_YES;
7 G_NO NUMBER := MSD_DEM_COMMON_UTILITIES.C_NO;
8 G_SCI_BACKLOG NUMBER := 1;
9 G_SCI_OTHER NUMBER := 2;
10 /* MSD DEM Debug Profile Value */
11 C_MSD_DEM_DEBUG VARCHAR2(1) := nvl( fnd_profile.value( 'MSD_DEM_DEBUG_MODE'), 'N');
12 C_MSD_DEM_SCHEMA VARCHAR2(100) := fnd_profile.value( 'MSD_DEM_SCHEMA');
13 C_MSD_DEM_PUSH_TIME VARCHAR2(1) := 'Y';
14 /* ASCP Series Ids */
15 C_MSD_DEM_SOP_ITEM_COST NUMBER := 111;
16
17 -- bug#14694463 kkhatri
18 CS_DEM_MATERIAL VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 1);
19 CS_DEM_PRODUCT VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 2);
20 CS_DEM_RESOURCE VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 3);
21 CS_DEM_WORK_ORDER VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
22
23
24
25 /*** PROCEDURES ***
26 * SET_PLAN_ATTRIBUTES
27 * LOAD_PLAN_DATA
28 * LOAD_PLAN_MEMBERS
29 * POST_DOWNLOAD_HOOK
30 * LOAD_ITEM_COST
31 * WAIT_UNTIL_DOWNLOAD_COMPLETE
32 * COLLECT_SCI_DATA
33 * LAUNCH_SCI_DATA_LOADS
34 * LOAD_PLAN_DATA_WO_PDS
35 * LOAD_PLAN_DATA_WO_ODS
36 */
37
38
39 /*
40 *
41 */
42 PROCEDURE LOAD_PLAN_DATA_WO_PDS (
43 p_plan_id IN NUMBER,
44 p_start_date IN VARCHAR2,
45 p_end_date IN VARCHAR2,
46 p_aggregation_start_date IN VARCHAR2,
47 p_dm_time_level IN NUMBER,
48 p_use_jobs IN NUMBER default 0,
49 p_split_batch_by_org IN NUMBER default 0,
50 p_job_check_interval IN NUMBER default 60,
51 p_jobs_timeout IN NUMBER default 86400);
52
53 /*
54 *
55 */
56 PROCEDURE LOAD_PLAN_DATA_WO_ODS (
57 p_plan_id IN NUMBER,
58 p_start_date IN VARCHAR2,
59 p_end_date IN VARCHAR2,
60 p_aggregation_start_date IN VARCHAR2,
61 p_dm_time_level IN NUMBER,
62 p_use_jobs IN NUMBER default 0,
63 p_split_batch_by_org IN NUMBER default 0,
64 p_job_check_interval IN NUMBER default 60,
65 p_jobs_timeout IN NUMBER default 86400
66 );
67
68
69 PROCEDURE SET_PLAN_ATTRIBUTES (
70 p_member_id IN NUMBER );
71
72
73 /*
74 *
75 */
76 PROCEDURE LOAD_PLAN_DATA (
77 p_member_id IN NUMBER,
78 p_delete_item_pop IN NUMBER default 1,
79 p_delete_dc_site_sc_pop IN NUMBER default 1,
80 p_use_jobs IN NUMBER default 0,
81 p_split_batch_by_org IN NUMBER default 0,
82 p_job_check_interval IN NUMBER default 60,
83 p_jobs_timeout IN NUMBER default 86400
84 );
85
86
87 /*
88 *
89 */
90 PROCEDURE LOAD_PLAN_MEMBERS;
91
92
93 /*
94 *
95 */
96 PROCEDURE POST_DOWNLOAD_HOOK (
97 p_member_id IN NUMBER );
98
99
100 /*
101 * This procedure loads item cost information from planning server ODS
102 * for all DM enabled organizations into the import integration
103 * staging table - BIIO_ITEM_COST
104 */
105 PROCEDURE LOAD_ITEM_COST;
106
107
108 /*
109 * This procedure is called by the Wait step of the Download Plan Scenario Data workflow.
110 *
111 */
112 PROCEDURE WAIT_UNTIL_DOWNLOAD_COMPLETE (
113 p_wait_step_id IN VARCHAR2 DEFAULT '',
114 p_exception_step_id IN VARCHAR2 DEFAULT '');
115
116
117 /*
118 *
119 */
120 PROCEDURE COLLECT_SCI_DATA (
121 errbuf OUT NOCOPY VARCHAR2,
122 retcode OUT NOCOPY VARCHAR2,
123 p_sr_instance_id IN NUMBER,
124 p_collection_group IN VARCHAR2 DEFAULT '-999',
125 p_collection_method IN NUMBER,
126 p_hidden_param1 IN VARCHAR2,
127 p_date_range_type IN NUMBER,
128 p_collection_window IN NUMBER,
129 p_from_date IN VARCHAR2,
130 p_to_date IN VARCHAR2 );
131
132
133 /*
134 *
135 */
136 PROCEDURE LAUNCH_SCI_DATA_LOADS (
137 errbuf OUT NOCOPY VARCHAR2,
138 retcode OUT NOCOPY VARCHAR2,
139 p_sr_instance_id IN NUMBER,
140 p_collection_group IN VARCHAR2 DEFAULT '-999',
141 p_collection_method IN NUMBER,
142 p_date_range_type IN NUMBER,
143 p_collection_window IN NUMBER,
144 p_from_date IN VARCHAR2,
145 p_to_date IN VARCHAR2,
146 p_entity IN NUMBER );
147
148 /*
149 *
150 */
151 PROCEDURE LOAD_COST (
152 errbuf OUT NOCOPY VARCHAR2,
153 retcode OUT NOCOPY VARCHAR2,
154 p_sr_instance_id IN Number,
155 launchDownload IN Number );
156
157 /* procedure to be used for loading series data via db job */
158 PROCEDURE LOAD_SERIES_DATA_BATCH (
159 p_series_id in number,
160 p_plan_id in number,
161 p_dm_time_level in number,
162 p_start_date IN VARCHAR2 DEFAULT NULL ,
163 p_end_date IN VARCHAR2 DEFAULT NULL ,
164 p_data_source_pds IN NUMBER DEFAULT 1,
165 p_filter_org_id IN NUMBER DEFAULT NULL
166 );
167
168 /* Function to get base work order supply ID for a given demand */
169 FUNCTION GET_WO_SUPPLY_ID_FROM_DEMAND(
170 p_plan_id IN NUMBER,
171 p_sr_instance_id IN NUMBER,
172 p_organization_id IN NUMBER,
173 p_demand_id IN NUMBER,
174 p_pegging_id IN NUMBER DEFAULT NULL
175 ) RETURN NUMBER;
176
177 /* Function to get base work order supply ID for a given supply */
178 FUNCTION GET_WO_SUPPLY_ID_FROM_SUPPLY(
179 p_plan_id IN NUMBER,
180 p_sr_instance_id IN NUMBER,
181 p_organization_id IN NUMBER,
182 p_supply_id IN NUMBER,
183 p_pegging_id IN NUMBER DEFAULT NULL
184 ) RETURN NUMBER;
185
186
187 /*
188 * This procedure is used to kill all existing plan data load jobs.
189 */
190 PROCEDURE KILL_JOBS (
191 p_job_name IN VARCHAR2 DEFAULT NULL );
192
193
194 END MSD_DEM_SOP;
[Home] [Help]