[Home] [Help]
PACKAGE: APPS.MSC_UTIL
Source
1 PACKAGE MSC_UTIL AUTHID CURRENT_USER AS
2 /* $Header: MSCUTILS.pls 120.43 2012/06/05 12:20:48 lsindhur ship $ */
3
4 TYPE TblNmTblTyp IS TABLE OF VARCHAR2(30);
5
6 G_MSC_DEBUG VARCHAR2(1) := nvl(FND_PROFILE.Value('MRP_DEBUG'),'N');
7
8 /* Added for bug 12640878 */
9
10 G_ITEMCAT_LEN NUMBER(3) := 250 ;
11
12 -- G_CMRO_EAM_INT_ENABLED VARCHAR2(1) := nvl(FND_PROFILE.Value('MSC_ASCP_IGNORE_CMRO_EAM_WO'),'N'); /* USAF */
13 /* The below profile G_CMRO_EAM_INT_ENABLED is specific for USAF 12.2 code.
14 G_CMRO_EAM_INT_ENABLED = 'Y', implies USAF code will take effect.
15 G_CMRO_EAM_INT_ENABLED = 'N', implies USAF code will be turned OFF.
16 We are reversing the value given in the profile as the profile name changed
17 during the coding and the meaning now is reverse.
18 Hence changing the value internally
19 */
20
21 G_CMRO_EAM_INT_ENABLED VARCHAR2(1) := CASE
22 WHEN nvl(FND_PROFILE.Value('MSC_ASCP_IGNORE_CMRO_EAM_WO'),1) = 1 THEN 'N'
23 ELSE 'Y'
24 END ;
25
26 G_EAM_CMRO_SUP_VER CONSTANT NUMBER := 6; -- USAF Currently supports 12.2 and above
27
28 ----- TESTING FLAG --------------------
29 STATSQL CONSTANT BOOLEAN := FALSE; -- static SQL
30
31 ----- CONSTANTS --------------------------------------------------------
32
33 TASK_COLL CONSTANT NUMBER := 1;
34 TASK_RELEASE CONSTANT NUMBER := 2;
35 TASK_USER_DEFINED CONSTANT NUMBER := -1;
36
37
38 DSCRETE_TYPE CONSTANT NUMBER := 1;
39 SYS_YES CONSTANT NUMBER := 1;
40 SYS_NO CONSTANT NUMBER := 2;
41
42 ASL_SYS_NO CONSTANT NUMBER := 2;
43 ASL_YES_RETAIN_CP CONSTANT NUMBER := 3;
44 ASL_YES CONSTANT NUMBER := 1;
45
46 --MSC_DEBUG CONSTANT VARCHAR2(9) := 'MRP_DEBUG';
47
48 G_NORMAL_COMPLETION CONSTANT NUMBER := 1;
49 G_PENDING_INACTIVE CONSTANT NUMBER := 2;
50 G_OTHERS CONSTANT NUMBER := 3;
51
52 -- agmcont
53 -- constants for continuous collections
54 SYS_INCR CONSTANT NUMBER := 3; -- incr refresh
55 SYS_TGT CONSTANT NUMBER := 4; -- targeted refresh
56
57
58 ----- CONSTANTS FOR SCE -------------------------------------------------
59 NO_USER_COMPANY CONSTANT NUMBER := 1;
60 COMPANY_ONLY CONSTANT NUMBER := 2;
61 USER_AND_COMPANY CONSTANT NUMBER := 3;
62
63 --- PREPLACE CHANGE START ---
64
65 G_COMPLETE CONSTANT NUMBER := 1;
66 G_INCREMENTAL CONSTANT NUMBER := 2;
67 G_PARTIAL CONSTANT NUMBER := 3;
68 G_TARGETED CONSTANT NUMBER := 4;
69 -- agmcont:
70 G_CONT CONSTANT NUMBER := 5;
71
72 -- MRP constants --
73 G_MDS CONSTANT NUMBER := 1;
74 G_MPS CONSTANT NUMBER := 2;
75 G_BOTH CONSTANT NUMBER := 3;
76
77 --- PREPLACE CHANGE END ---
78
79 G_SUCCESS CONSTANT NUMBER := 0;
80 G_WARNING CONSTANT NUMBER := 1;
81 G_ERROR CONSTANT NUMBER := 2;
82
83 G_INS_DISCRETE CONSTANT NUMBER := 1;
84 G_INS_PROCESS CONSTANT NUMBER := 2;
85 G_INS_OTHER CONSTANT NUMBER := 3;
86 G_INS_MIXED CONSTANT NUMBER := 4;
87
88 -- STAGING TABLE STATUS --
89
90 G_ST_EMPTY CONSTANT NUMBER := 0; -- no instance data exists;
91 G_ST_PULLING CONSTANT NUMBER := 1;
92 G_ST_READY CONSTANT NUMBER := 2;
93 G_ST_COLLECTING CONSTANT NUMBER := 3;
94 G_ST_PURGING CONSTANT NUMBER := 4;
95
96
97 G_APPS107 CONSTANT NUMBER := 1;
98 G_APPS110 CONSTANT NUMBER := 2;
99 G_APPS115 CONSTANT NUMBER := 3;
100 G_APPS120 CONSTANT NUMBER := 4;
101 G_APPS121 CONSTANT NUMBER := 5;
102 G_APPS122 CONSTANT NUMBER := 6;
103
104 -- BOM ROUNDING DIRECTION --
105
106 G_ROUND_DOWN CONSTANT NUMBER := 1;
107 G_ROUND_UP CONSTANT NUMBER := 2;
108 G_ROUND_NONE CONSTANT NUMBER := 3;
109
110 -- SCE constants --
111 G_CONF_APS CONSTANT NUMBER := 1;
112 G_CONF_APS_SCE CONSTANT NUMBER := 2;
113 G_CONF_SCE CONSTANT NUMBER := 3;
114
115 -- errors
116 G_ERROR_STACK VARCHAR2(2000);
117
118 G_ALL_ORGANIZATIONS CONSTANT VARCHAR2(6):= '-999';
119
120 v_in_org_str VARCHAR2(32767):='NULL';
121 v_in_all_org_str VARCHAR2(32767):='NULL';
122
123 v_depot_org_str VARCHAR2(32767):='NULL';
124 v_non_depot_org_str VARCHAR2(32767):='NULL';
125
126 v_ext_repair_sup_id_str VARCHAR2(32767):='NULL';
127
128 v_msc_tp_coll_window NUMBER;
129
130
131 -- To collect SRP Data when this profile is set to Yes
132 G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
133
134 -- To collect CMRO Data when this profile is set to Yes
135 G_COLLECT_CMRO_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_CMRO_WO_DEMAND_PS'),'N');
136
137 /* IBUC - COLLECTION CHANGES
138 To collect Install Base under Contracts when for the number of months , set in this profile */
139
140 G_COLLECT_IBUC_DATA NUMBER := NVL(FND_PROFILE.VALUE('MSC_COLL_TIME_WINDOW_IBUC_HISTORY'),12);
141 G_COLLECT_SRP_PH2_ENABLE VARCHAR2(1):= NVL(FND_PROFILE.VALUE('MSC_SRP_PH2_ENABLED'),'N');
142
143 -- SCE Additions --
144 /* SCE Change Starts */
145 G_MSC_CONFIGURATION VARCHAR2(10) := nvl(fnd_profile.value('MSC_X_CONFIGURATION'), G_CONF_APS);
146 /* SCE Change Ends */
147 -- Schemas
148 G_AHL_SCHEMA VARCHAR2(30);
149 G_INV_SCHEMA VARCHAR2(30);
150 G_BOM_SCHEMA VARCHAR2(30);
151 G_PO_SCHEMA VARCHAR2(30);
152 G_WSH_SCHEMA VARCHAR2(30);
153 G_EAM_SCHEMA VARCHAR2(30);
154 G_ONT_SCHEMA VARCHAR2(30);
155 G_MRP_SCHEMA VARCHAR2(30);
156 G_WSM_SCHEMA VARCHAR2(30);
157 G_CSP_SCHEMA VARCHAR2(30);
158 G_WIP_SCHEMA VARCHAR2(30);
159 G_APPS_SCHEMA VARCHAR2(30);
160 G_CSD_SCHEMA VARCHAR2(30);
161 G_MSC_SCHEMA VARCHAR2(30);
162 G_FND_SCHEMA VARCHAR2(30);
163 --
164 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
165 NULL_DATE CONSTANT DATE:= SYSDATE-36500;
166 NULL_VALUE CONSTANT NUMBER:= -23453; -- null value for positive number
167 NULL_CHAR CONSTANT VARCHAR2(6):= '-23453';
168
169 TYPE CollParamREC is RECORD (
170 purge_ods_flag NUMBER,
171 app_supp_cap_flag NUMBER,
172 atp_rules_flag NUMBER,
173 bom_flag NUMBER,
174 bor_flag NUMBER,
175 calendar_flag NUMBER,
176 demand_class_flag NUMBER,
177 item_subst_flag NUMBER,
178 forecast_flag NUMBER,
179 item_flag NUMBER,
180 kpi_bis_flag NUMBER,
181 mds_flag NUMBER,
182 mps_flag NUMBER,
183 oh_flag NUMBER,
184 parameter_flag NUMBER,
185 planner_flag NUMBER,
186 project_flag NUMBER,
187 po_flag NUMBER,
188 reserves_flag NUMBER,
189 resource_nra_flag NUMBER,
190 saf_stock_flag NUMBER,
191 sales_order_flag NUMBER,
192 source_hist_flag NUMBER,
193 po_receipts_flag NUMBER,
194 sourcing_rule_flag NUMBER,
195 sub_inventory_flag NUMBER,
196 tp_customer_flag NUMBER,
197 tp_vendor_flag NUMBER,
198 unit_number_flag NUMBER,
199 uom_flag NUMBER,
200 user_supply_demand_flag NUMBER,
201 wip_flag NUMBER,
202 user_company_flag NUMBER,
203 -- agmcont
204 -- added for continuous collections
205 bom_sn_flag number,
206 bor_sn_flag number,
207 item_sn_flag number,
208 oh_sn_flag number,
209 usup_sn_flag number,
210 udmd_sn_flag number,
211 so_sn_flag number,
212 fcst_sn_flag number,
213 wip_sn_flag number,
214 supcap_sn_flag number,
215 po_sn_flag number,
216 mds_sn_flag number,
217 mps_sn_flag number,
218 nosnap_flag number,
219 /* CP-ACK starts */
220 supplier_response_flag NUMBER,
221 /* CP-ACK ends */
222 /* CP-AUTO */
223 suprep_sn_flag NUMBER,
224 org_group_flag VARCHAR2(30),
225 threshold NUMBER,
226 trip_flag NUMBER,
227 trip_sn_flag NUMBER,
228 ds_mode NUMBER,
229 sales_channel_flag NUMBER,
230 fiscal_calendar_flag NUMBER,
231 CMRO_flag NUMBER,
232 internal_repair_flag NUMBER, -- Repair Orders Bug # 5909379
233 external_repair_flag NUMBER,
234 payback_demand_supply_flag NUMBER,
235 currency_conversion_flag NUMBER, --bug # 6469722
236 delivery_details_flag NUMBER ,
237 ibuc_history_flag NUMBER, /* bug9791058 */
238 notes_attach_flag NUMBER,
239 eAM_info_flag NUMBER, /* USAF*/
240 eAM_forecasts_flag NUMBER,
241 eam_fc_st_date DATE,
242 eam_fc_end_date DATE,
243 cmro_forecasts_flag NUMBER,
244 cmro_fc_st_date DATE,
245 cmro_fc_end_date DATE,
246 cmro_closed_wo NUMBER,
247 ret_fcst_flag NUMBER, -- bug 13839374
248 osp_supply NUMBER
249 );
250
251
252 -- Global Variables for Good Bad Part condition
253 G_PARTCONDN_ITEMTYPEID NUMBER;
254 G_PARTCONDN_GOOD NUMBER;
255 G_PARTCONDN_BAD NUMBER;
256
257 G_CURRENT_SESSION_ID NUMBER;
258 G_PERF_STAT_TRSHLD_TIME NUMBER := 5;
259
260 --Collections profile options
261 G_LVL_FATAL_ERR CONSTANT NUMBER := 1 ;
262 G_LVL_STATUS CONSTANT NUMBER := 2 ;
263 G_LVL_WARNING CONSTANT NUMBER := 4 ;
264 G_LVL_DEBUG_1 CONSTANT NUMBER := 8 ;
265 G_LVL_DEBUG_2 CONSTANT NUMBER := 16 ;
266 G_LVL_DEV CONSTANT NUMBER := 64 ;
267
268 G_LVL_PERFDBG_1 CONSTANT NUMBER := 128 ;
269 G_LVL_PERFDBG_2 CONSTANT NUMBER := 256 ;
270
271
272
273 --old status values are restored for backward compatibility.
274 --avoid using these for any new development.
275 G_D_STATUS CONSTANT NUMBER := 2 ;
276 G_D_DEBUG_1 CONSTANT NUMBER := 8 ;
277 G_D_DEBUG_2 CONSTANT NUMBER := 16 ;
278 G_D_PERFDBG_1 CONSTANT NUMBER := 128 ;
279 G_D_PERFDBG_2 CONSTANT NUMBER := 256 ;
280
281 -- variable for MSC:Coll Err Debug and MSC:Coll Perf Debug profile options
282 G_CL_DEBUG NUMBER := NVL(FND_PROFILE.Value('MSC_COLL_ERR_DEBUG' ), G_LVL_STATUS + G_LVL_WARNING ) --MSC:Coll Err Debug
283 + NVL(FND_PROFILE.Value('MSC_COLL_PERF_DEBUG'), 0) --MSC:Coll Perf Debug profile
284 + G_LVL_FATAL_ERR ; --Manadtory level
285
286 -- NGOEL 11/29/2001, type defined for use in compare_index procedure for passing list of
287 -- columns in the specified index in the order of creation.
288
289 TYPE char30_arr IS TABLE OF varchar2(30);
290
291 --log collection messages based on the profiles set.
292 PROCEDURE LOG_MSG(
293 pType IN NUMBER,
294 buf IN VARCHAR2
295 );
296
297 FUNCTION Check_MSG_Level(pType IN NUMBER) RETURN BOOLEAN;
298
299 PROCEDURE Print_Msg (buf IN VARCHAR2);
300
301
302 PROCEDURE print_top_wait(pElaTime NUMBER DEFAULT 0);
303 PROCEDURE print_cum_stat(pElaTime NUMBER DEFAULT 0);
304 PROCEDURE print_bad_sqls(pElaTime NUMBER DEFAULT 0);
305 PROCEDURE print_pull_params(pINSTANCE_ID IN NUMBER);
306 PROCEDURE print_ods_params(pRECALC_SH IN NUMBER, pPURGE_SH IN NUMBER);
307 PROCEDURE print_trace_file_name(pReqID NUMBER) ;
308
309 --add debug level to the existing debug status.
310 PROCEDURE MSC_SET_DEBUG_LEVEL(pType IN NUMBER);
311
312 -- log messaging if debug is turned on
313 PROCEDURE MSC_DEBUG(buf IN VARCHAR2);
314
315 -- log messaging irrespective of whether debug is turned on or off
316 PROCEDURE MSC_LOG(buf IN VARCHAR2);
317
318 -- out messaging
319 PROCEDURE MSC_OUT(buf IN VARCHAR2);
320
321
322 -- NGOEL 11/29/2001, used to detect changes in partitioned indexes as odf can handle them.
323 -- x_drop_index - If TRUE, means the existing index needs to be dropped before creation.
324 -- x_create_index - If TRUE, means the index needs to be created.
325
326 PROCEDURE COMPARE_INDEX(
327 p_table_name IN VARCHAR2,
328 p_index_name IN VARCHAR2,
329 p_column_list IN MSC_UTIL.char30_arr,
330 x_create_index OUT NOCOPY BOOLEAN,
331 x_partitioned OUT NOCOPY BOOLEAN
332 );
333
334 TYPE DbMessageRec is RECORD(
335 msg_no number ,
336 msg_desc varchar2(2000) ,
337 package_name varchar2(50) ,
338 program_unit varchar2(50)
339 );
340
341
342 TYPE DbMessageTabType IS TABLE OF DbMessageRec
343 INDEX BY BINARY_INTEGER;
344
345 g_dbmessage DbMessageTabType ;
346
347 PROCEDURE CREATE_SNAP_LOG( p_schema in VARCHAR2,
348 p_table in VARCHAR2,
349 p_applsys_schema IN VARCHAR2);
350
351 PROCEDURE CREATE_SNAP_LOG( p_schema in VARCHAR2,
352 p_table in VARCHAR2,
353 p_applsys_schema IN VARCHAR2,
354 p_appl_id in NUMBER);
355
356 PROCEDURE GET_STORAGE_PARAMETERS( p_table_name IN VARCHAR2,
357 p_schema IN VARCHAR2,
358 v_table_space OUT NOCOPY VARCHAR2,
359 v_index_space OUT NOCOPY VARCHAR2,
360 v_storage_clause OUT NOCOPY VARCHAR2);
361
362 FUNCTION CREATE_SNAP (p_schema IN VARCHAR2,
363 p_table IN VARCHAR2,
364 p_object IN VARCHAR2,
365 p_sql_stmt IN VARCHAR2,
366 p_applsys_schema IN VARCHAR2,
367 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
368 p_parallel_degree IN NUMBER DEFAULT 1,
369 p_error IN VARCHAR2 DEFAULT NULL) /*6272589*/
370 RETURN BOOLEAN;
371
372 /*Added the function CREATE_SNAP2 w.r.t bug 12964052 */
373 FUNCTION CREATE_SNAP2(p_schema IN VARCHAR2,
374 p_table IN VARCHAR2,
375 p_object IN VARCHAR2,
376 p_sql_stmt IN VARCHAR2,
377 p_applsys_schema IN VARCHAR2,
378 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
379 p_parallel_degree IN NUMBER DEFAULT 1,
380 p_error IN VARCHAR2 DEFAULT NULL)
381 RETURN BOOLEAN;
382
383
384 /*Added the function CREATE_SNAP2_AS w.r.t bug 12964052 */
385 FUNCTION CREATE_SNAP2_AS(p_table IN VARCHAR2,
386 p_object IN VARCHAR2,
387 p_sql_stmt IN VARCHAR2,
388 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
389 p_parallel_degree IN NUMBER DEFAULT 1,
390 p_error IN VARCHAR2 DEFAULT NULL )
391 RETURN BOOLEAN;
392
393 PROCEDURE CREATE_INDEX (p_schema IN VARCHAR2,
394 p_sql_stmt IN VARCHAR2,
395 p_object IN VARCHAR2,
396 p_applsys_schema IN VARCHAR2);
397
398 PROCEDURE DROP_INDEX (p_schema IN VARCHAR2,
399 p_sql_stmt IN VARCHAR2,
400 p_index IN VARCHAR2,
401 p_table IN VARCHAR2,
402 p_applsys_schema IN VARCHAR2);
403
404 FUNCTION GET_SCHEMA_NAME( p_apps_id IN NUMBER)
405 RETURN VARCHAR2;
406
407 FUNCTION get_vmi_flag(var_plan_id IN NUMBER,
408 var_sr_instance_id IN NUMBER,
409 var_org_id IN NUMBER,
410 var_inventory_item_id IN NUMBER,
411 var_supplier_id IN NUMBER,
412 var_supplier_site_id IN NUMBER) RETURN NUMBER;
413
414 FUNCTION Source_Instance_State(p_dblink varchar2)
415 return boolean;
416
417 /*
418 PROCEDURE debug_message( p_line_no in number ,
419 P_Line_msg in varchar2 ,
420 p_Package_name in varchar2 default null ,
421 P_Program_unit in varchar2 default null ,
422 P_Table_Name in varchar2 default 'DEBUG_DB_MESSAGES' ) ;
423
424 PROCEDURE init_message(P_Table_Name in varchar2 default 'DEBUG_DB_MESSAGES');
425 */
426 PROCEDURE init_dbmessage ;
427
428 PROCEDURE set_dbmessage(p_msg in varchar2 ,
429 p_Package_name in varchar2 default null ,
430 P_Program_unit in varchar2 default null ) ;
431
432 FUNCTION get_dbmessage return
433 DbMessageTabType ;
434
435 FUNCTION MSC_NUMVAL(p_input varchar2)
436 return NUMBER;
437
438 FUNCTION GET_SERVICE_ITEMS_CATSET_ID RETURN NUMBER ;
439
440 FUNCTION is_app_installed(p_product IN NUMBER) RETURN BOOLEAN;
441
442 FUNCTION get_aps_config_level(p_sr_instance_id IN Number, p_dblink IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
443 -- Procedure to Initialize common global variables
444 PROCEDURE initialize_common_globals(pINSTANCE_ID IN NUMBER);
445 FUNCTION mv_exists_in_schema(p_schema_name VARCHAR2, p_MV_name VARCHAR2) RETURN BOOLEAN;
446 -- Procedure to execute any API given as parameter Bug 6469713
447 PROCEDURE EXECUTE_API(ERRBUF OUT NOCOPY VARCHAR2,
448 RETCODE OUT NOCOPY NUMBER,
449 p_package_name IN VARCHAR2,
450 p_proc_name IN VARCHAR2 ,
451 comma_sep_para_list IN VARCHAR2);
452 PROCEDURE DROP_WRONGSCHEMA_MVIEWS ;
453 PROCEDURE DROP_DEPRECATED_MVIEWS ;
454 /* ======== Addition of procedure w.r.t Bug 13602645 by ngaddamp starts here ========== */
455 PROCEDURE DROP_ORPHAN_MVIEW_CONTAINERS ;
456 /* ======== Addition of procedure w.r.t Bug 13602645 by ngaddamp ends here ========== */
457 PROCEDURE DROP_MVIEW_TRIGGERS(mview_owner IN VARCHAR2, mview_name IN VARCHAR2);
458 PROCEDURE DROP_MVIEW_SYNONYMS(mview_owner IN VARCHAR2, mview_name IN VARCHAR2);
459 END MSC_UTIL;