DBA Data[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;