1 PACKAGE MSD_SPF_COLLECT_HISTORY_DATA AUTHID CURRENT_USER AS
2 /* $Header: msdspfchds.pls 120.8.12020000.3 2012/11/05 09:20:25 kkhatri ship $ */
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 G_SCHEMA VARCHAR2(50) := fnd_profile.value('MSD_DEM_SCHEMA');
8
9 G_INSTALL_BASE_UC_SERIES_ID NUMBER := 114;
10
11 G_CMRO_WO_HIST_SERIES_ID NUMBER := 211;
12 G_CMRO_MTL_USG_SERIES_ID NUMBER := 212;
13 G_CMRO_RES_USG_SERIES_ID NUMBER := 213;
14 G_EAM_MTL_USG_SERIES_ID NUMBER := 214;
15 G_EAM_RES_USG_SERIES_ID NUMBER := 215;
16 G_FLEET_DATA_SERIES_ID NUMBER := 216;
17
18
19 G_CMRO_WO_HIST_TABLE VARCHAR2(30) := 'MSD_SPF_ST_CMRO_WO_HIST';
20 G_CMRO_MTL_USG_TABLE VARCHAR2(30) := 'MSD_SPF_ST_CMRO_MTL_USG';
21 G_CMRO_RES_USG_TABLE VARCHAR2(30) := 'MSD_SPF_ST_CMRO_RES_USG';
22 G_FLEET_DATA_DEST_TABLE VARCHAR2(61) := G_SCHEMA || '.BIIO_SPF_CMRO_FLEET_DATA';
23
24 G_COLL_COMPLETE NUMBER := 1;
25 G_COLL_NETCHANGE NUMBER := 2;
26 C_SPARE_USAGE_HISTORY NUMBER := 201;
27 C_SPARE_SHIPMENT_HISTORY NUMBER := 202;
28 C_SPARE_USAGE_HISTORY_TABLE VARCHAR2(30) := 'MSD_SPF_ST_SPARE_USAGE';
29
30 CS_DEM_SPF_BASE_MODEL VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 1);
31 CS_DEM_SPF_OPTION_CLASS VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 2);
32 CS_DEM_SPF_OPTION VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 3);
33
34 -- bug#14694463 kkhatri
35 CS_DEM_MATERIAL VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 1);
36 CS_DEM_PRODUCT VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 2);
37 CS_DEM_RESOURCE VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 3);
38 CS_DEM_WORK_ORDER VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
39 CS_DEM_WORKORDER_ITEM VARCHAR2(100) := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
40
41
42 CS_DEM_LEVEL_ASSET_GROUP_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'table_label');
43 CS_DEM_LEVEL_CLASS_CODE_LABEL VARCHAR2(255) := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'table_label');
44
45
46 CS_DEM_LEVEL_ASSET_GROUP_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'group_table_id'));
47 CS_DEM_LEVEL_CLASS_CODE_ID NUMBER := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'group_table_id'));
48
49 /*** PROCEDURES ***/
50
51
52 PROCEDURE COLLECT_SPF_DATA (
53 errbuf OUT NOCOPY VARCHAR2,
54 retcode OUT NOCOPY VARCHAR2,
55 p_sr_instance_id IN NUMBER,
56 p_collection_group IN VARCHAR2,
57 p_collection_method IN NUMBER,
58 p_hidden_param1 IN VARCHAR2,
59 p_date_range_type IN NUMBER,
60 p_collection_window IN NUMBER,
61 p_from_date IN VARCHAR2,
62 p_to_date IN VARCHAR2,
63 p_shipment_data IN NUMBER,
64 p_usage_data IN NUMBER,
65 p_install_base IN NUMBER,
66 p_sales_forecast IN NUMBER,
67 p_auto_run_download IN NUMBER,
68 p_check_interval IN NUMBER DEFAULT 60,
69 p_time_out IN NUMBER DEFAULT 86400,
70 p_synchronous IN NUMBER DEFAULT G_NO,
71 p_custom_usage_hist_view IN VARCHAR2 DEFAULT NULL,
72 p_custom_ibuc_hist_view IN VARCHAR2 DEFAULT NULL,
73 p_custom_sales_fcst_view IN VARCHAR2 DEFAULT NULL,
74 p_custom_download_workflow IN VARCHAR2 DEFAULT NULL );
75
76
77 PROCEDURE LAUNCH_DOWNLOAD (
78 errbuf OUT NOCOPY VARCHAR2,
79 retcode OUT NOCOPY VARCHAR2,
80 p_workflow_lkup_code IN VARCHAR2,
81 p_auto_run_download IN NUMBER,
82 p_check_interval IN NUMBER DEFAULT 60,
83 p_time_out IN NUMBER DEFAULT 18000 );
84
85
86 PROCEDURE TRUNCATE_SPF_STAGING (
87 errbuf OUT NOCOPY VARCHAR2,
88 retcode OUT NOCOPY VARCHAR2 );
89
90 PROCEDURE TRUNCATE_SPF_CMRO_STAGING (
91 errbuf OUT NOCOPY VARCHAR2,
92 retcode OUT NOCOPY VARCHAR2 );
93
94 PROCEDURE COLLECT_INSTALL_BASE_UC_DATA (
95 errbuf OUT NOCOPY VARCHAR2,
96 retcode OUT NOCOPY VARCHAR2,
97 p_sr_instance_id IN NUMBER,
98 p_collection_method IN NUMBER,
99 p_from_date IN VARCHAR2,
100 p_to_date IN VARCHAR2,
101 p_custom_ibuc_hist_view IN VARCHAR2 DEFAULT NULL );
102
103
104 PROCEDURE POPULATE_STAGING_TABLE (
105 errbuf OUT NOCOPY VARCHAR2,
106 retcode OUT NOCOPY VARCHAR2,
107 p_entity_name IN VARCHAR2,
108 p_sr_instance_id IN NUMBER,
109 p_key_value_pairs IN VARCHAR2 DEFAULT NULL);
110
111 PROCEDURE DERIVE_SPARE_SUPERSESSIONS (
112 errbuf OUT NOCOPY VARCHAR2,
113 retcode OUT NOCOPY VARCHAR2,
114 p_sr_instance_id IN NUMBER
115 );
116
117 PROCEDURE LOAD_DM_FORECAST_FOR_SPF (
118 errbuf OUT NOCOPY VARCHAR2,
119 retcode OUT NOCOPY VARCHAR2,
120 p_load_sales_fcst IN NUMBER
121 );
122
123 PROCEDURE COLLECT_SPARE_USAGE_HISTORY (
124 errbuf OUT NOCOPY VARCHAR2,
125 retcode OUT NOCOPY VARCHAR2,
126 p_sr_instance_id IN NUMBER,
127 p_collection_method IN NUMBER,
128 p_from_date IN VARCHAR2,
129 p_to_date IN VARCHAR2,
130 p_spare_usg_hist IN NUMBER
131 );
132
133 PROCEDURE COLLECT_SPARE_SHIPMENT_HISTORY (
134 errbuf OUT NOCOPY VARCHAR2,
135 retcode OUT NOCOPY VARCHAR2,
136 p_sr_instance_id IN NUMBER,
137 p_collection_method IN NUMBER,
138 p_from_date IN VARCHAR2,
139 p_to_date IN VARCHAR2
140 );
141
142 PROCEDURE DERIVE_SERVICE_BOM (
143 errbuf OUT NOCOPY VARCHAR2,
144 retcode OUT NOCOPY VARCHAR2,
145 p_sr_instance_id IN NUMBER,
146 p_install_base IN NUMBER,
147 p_from_date IN VARCHAR2 DEFAULT NULL,
148 p_to_date IN VARCHAR2 DEFAULT NULL,
149 p_custom_ibuc_hist_view IN VARCHAR2 DEFAULT NULL,
150 p_eng_profile_app_id IN VARCHAR2 DEFAULT 'ENGINE_PROFILE:141', --engine profile from which forecast lead param is to be used
151 p_preserve_dem_dates IN NUMBER default G_YES, --preserve the population from and to dates for existing spf members in demantra
152 --if yes, the dates will be preserved even if they are out of the allowed (spf_history_periods, lead) date range
153 --this is to minimize deletion of combinations in demantra to improve performance of level profile load
154 p_check_interval IN NUMBER DEFAULT 60,
155 p_time_out IN NUMBER DEFAULT 86400
156 );
157
158
159 PROCEDURE COLLECT_SHIPMENT_DATA_FF (
160 errbuf OUT NOCOPY VARCHAR2,
161 retcode OUT NOCOPY VARCHAR2,
162 p_sr_instance_id IN NUMBER,
163 p_auto_run_download IN NUMBER,
164 p_file_separator IN VARCHAR2,
165 p_control_file_path IN VARCHAR2,
166 p_data_file_path IN VARCHAR2,
167 p_data_file_name IN VARCHAR2,
168 p_check_interval IN NUMBER DEFAULT 60,
169 p_time_out IN NUMBER DEFAULT 86400,
170 p_synchronous IN NUMBER DEFAULT G_NO );
171
172
173 PROCEDURE COLLECT_USAGE_DATA_FF (
174 errbuf OUT NOCOPY VARCHAR2,
175 retcode OUT NOCOPY VARCHAR2,
176 p_sr_instance_id IN NUMBER,
177 p_file_separator IN VARCHAR2,
178 p_control_file_path IN VARCHAR2,
179 p_data_file_path IN VARCHAR2,
180 p_data_file_name IN VARCHAR2,
181 p_auto_run_download IN NUMBER,
182 p_check_interval IN NUMBER DEFAULT 60,
183 p_time_out IN NUMBER DEFAULT 86400,
184 p_synchronous IN NUMBER DEFAULT G_NO );
185
186
187 PROCEDURE USAGE_DATA_PRE_PROCESS (
188 errbuf OUT NOCOPY VARCHAR2,
189 retcode OUT NOCOPY VARCHAR2,
190 p_sr_instance_id IN NUMBER );
191
192 PROCEDURE COLLECT_CMRO_DATA (
193 errbuf OUT NOCOPY VARCHAR2,
194 retcode OUT NOCOPY VARCHAR2,
195 p_sr_instance_id IN NUMBER,
196 p_collection_group IN VARCHAR2,
197 p_collection_method IN NUMBER,
198 p_hidden_param IN VARCHAR2,
199 p_date_range_type IN NUMBER,
200 p_collection_window IN NUMBER,
201 p_from_date IN VARCHAR2,
202 p_to_date IN VARCHAR2,
203 p_auto_run_download IN NUMBER,
204 p_synchronous IN NUMBER DEFAULT G_NO,
205 p_check_interval IN NUMBER DEFAULT 60,
206 p_time_out IN NUMBER DEFAULT 1440
207 );
208
209 PROCEDURE COLLECT_FLEET_DATA (
210 errbuf OUT NOCOPY VARCHAR2,
211 retcode OUT NOCOPY VARCHAR2,
212 p_sr_instance_id IN NUMBER,
213 p_collection_group IN VARCHAR2,
214 p_collection_method IN NUMBER,
215 p_hidden_param IN VARCHAR2,
216 p_date_range_type IN NUMBER,
217 p_hist_collection_window IN NUMBER,
218 p_sim_plan_name IN VARCHAR2,
219 p_fcst_collection_window IN NUMBER,
220 p_from_date IN VARCHAR2,
221 p_to_date IN VARCHAR2,
222 p_auto_run_download IN NUMBER,
223 p_synchronous IN NUMBER DEFAULT G_NO,
224 p_check_interval IN NUMBER DEFAULT 60,
225 p_time_out IN NUMBER DEFAULT 1440
226 );
227
228
229 /* Procedure for collect series data request */
230 PROCEDURE COLLECT_SERIES_DATA_REQ (
231 errbuf OUT NOCOPY VARCHAR2,
232 retcode OUT NOCOPY VARCHAR2,
233 p_series_id IN NUMBER,
234 p_dest_table IN VARCHAR2,
235 p_sr_instance_id IN NUMBER,
236 p_collection_method IN NUMBER,
237 p_from_date IN VARCHAR2,
238 p_to_date IN VARCHAR2,
239 p_sim_plan_name IN VARCHAR2 DEFAULT '');
240
241 PROCEDURE Collect_eAM_Data (
242 errbuf OUT NOCOPY VARCHAR2 ,
243 retcode OUT NOCOPY VARCHAR2 ,
244 p_sr_instance_id IN NUMBER ,
245 p_collection_group IN VARCHAR2 ,
246 p_collection_method IN NUMBER ,
247 p_hidden_param1 IN VARCHAR2 ,
248 p_date_range_type IN NUMBER ,
249 p_collection_window IN NUMBER ,
250 p_from_date IN VARCHAR2 ,
251 p_to_date IN VARCHAR2 ,
252 p_auto_run_download IN NUMBER ,
253 p_synchronous IN NUMBER DEFAULT G_NO ,
254 p_check_interval IN NUMBER DEFAULT 60 ,
255 p_time_out IN NUMBER DEFAULT 1440 );
256
257 PROCEDURE collect_nmp_hist_data (
258 errbuf OUT NOCOPY VARCHAR2,
259 retcode OUT NOCOPY VARCHAR2,
260 p_sr_instance_id IN NUMBER,
261 p_collection_group IN VARCHAR2,
262 p_collection_method IN NUMBER,
263 p_hidden_param1 IN VARCHAR2,
264 p_date_range_type IN NUMBER,
265 p_collection_window IN NUMBER,
266 p_from_date IN VARCHAR2,
267 p_to_date IN VARCHAR2,
268 p_hist_series IN VARCHAR2,
269 p_auto_run_download IN NUMBER ,
270 p_synchronous IN NUMBER DEFAULT G_NO,
271 p_check_interval IN NUMBER DEFAULT 60,
272 p_time_out IN NUMBER DEFAULT 1440);
273
274 Procedure Collect_Source_Data (
275 errbuf OUT NOCOPY VARCHAR2 ,
276 retcode OUT NOCOPY VARCHAR2 ,
277 p_sr_instance_id IN NUMBER ,
278 p_collection_group IN VARCHAR2 ,
279 p_from_date IN VARCHAR2 ,
280 p_to_date IN VARCHAR2 ,
281 p_dest_dblink IN VARCHAR2 ,
282 p_collect_product IN VARCHAR2 ,
283 p_sim_plan_name IN VARCHAR2 DEFAULT '',
284 p_check_interval IN NUMBER DEFAULT 60,
285 p_time_out IN NUMBER DEFAULT 1440);
286
287
288 /*
289 * This procedure deletes all data from SPF GL Tables. This should only be run by
290 * an admin user. The user must make sure that the Demantra AS is down before running
291 * the procedure.
292 * The procedure is used when the SPF related profile options have been changed which
293 * result in changes to the bom structure brought into Demantra.
294 */
295 PROCEDURE PURGE_SPF_GL_DATA (
296 errbuf OUT NOCOPY VARCHAR2,
297 retcode OUT NOCOPY VARCHAR2);
298
299
300
301 END MSD_SPF_COLLECT_HISTORY_DATA;
[Home] [Help]