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