[Home] [Help]
PACKAGE: APPS.MSC_ST_UTIL
Source
1 PACKAGE MSC_ST_UTIL AUTHID CURRENT_USER AS
2 /* $Header: MSCSUTLS.pls 120.8 2012/03/07 19:11:22 pstrnad ship $ */
3 -- ================== CONSTANTS =====================
4 SYS_YES CONSTANT NUMBER := 1;
5 SYS_NO CONSTANT NUMBER := 2;
6 v_instance_type NUMBER;
7 v_instance_id NUMBER;
8
9 G_NEW CONSTANT NUMBER := 1;
10 G_IN_PROCESS CONSTANT NUMBER := 2;
11 G_ERROR CONSTANT NUMBER := 3;
12 G_PROPAGATION CONSTANT NUMBER := 4;
13 G_VALID CONSTANT NUMBER := 5;
14 G_WARNING CONSTANT NUMBER := 2;
15 G_SEV_ERROR CONSTANT NUMBER := 1;
16 G_SCE CONSTANT NUMBER := 5;
17 G_SEV3_ERROR CONSTANT NUMBER := 3;
18 G_MULTI_TIER_ENABLE CONSTANT BOOLEAN:= TRUE;
19 -- multi tier is enabled for bug 2939695
20
21 G_INS_DISCRETE CONSTANT NUMBER := 1;
22 G_INS_PROCESS CONSTANT NUMBER := 2;
23 G_INS_OTHER CONSTANT NUMBER := 3;
24 G_INS_MIXED CONSTANT NUMBER := 4;
25
26 G_VENDOR CONSTANT NUMBER := 1;
27 G_CUSTOMER CONSTANT NUMBER := 2;
28 G_ORGANIZATION CONSTANT NUMBER := 3;
29 G_CARRIER CONSTANT NUMBER := 4;
30
31
32 NULL_DATE CONSTANT DATE:= SYSDATE-36500;
33 NULL_VALUE CONSTANT NUMBER:= -23453; -- null value for positive number
34 NULL_CHAR CONSTANT VARCHAR2(6):= '-23453';
35
36 -- =========== Procedures and fUNCTIONS=================
37 FUNCTION LOG_ERROR
38 (p_table_name VARCHAR2,
39 p_instance_code VARCHAR2,
40 p_row CLOB,
41 p_severity NUMBER DEFAULT G_SEV_ERROR,
42 p_error_text OUT NOCOPY VARCHAR2,
43 p_message_text VARCHAR2 DEFAULT NULL,
44 p_batch_id NUMBER DEFAULT NULL,
45 p_where_str VARCHAR2 DEFAULT NULL,
46 p_col_name VARCHAR2 DEFAULT NULL,
47 p_default_value VARCHAR2 DEFAULT NULL,
48 p_debug BOOLEAN DEFAULT FALSE,
49 p_propagated VARCHAR2 DEFAULT 'N')
50 RETURN NUMBER;
51
52 FUNCTION DERIVE_COMPANY_ID
53 (p_table_name VARCHAR2,
54 p_company_name VARCHAR2,
55 p_company_id VARCHAR2,
56 p_instance_code VARCHAR2,
57 p_error_text OUT NOCOPY VARCHAR2,
58 p_default_value NUMBER DEFAULT -1,
59 p_instance_type NUMBER DEFAULT 3 ,
60 p_batch_id NUMBER DEFAULT NULL_VALUE,
61 p_severity NUMBER DEFAULT 0 ,
62 p_message_text VARCHAR2 DEFAULT NULL,
63 p_debug BOOLEAN DEFAULT FALSE,
64 p_row LONG DEFAULT NULL)
65 RETURN NUMBER;
66
67 FUNCTION DERIVE_ITEM_ID
68 (p_table_name VARCHAR2,
69 p_item_col_name VARCHAR2, --item_name
70 p_item_col_id VARCHAR2, --inventory_item_id
71 p_instance_id NUMBER,
72 p_instance_code VARCHAR2,
73 p_error_text OUT NOCOPY VARCHAR2,
74 p_batch_id NUMBER DEFAULT NULL_VALUE,
75 p_severity NUMBER DEFAULT 0,
76 p_message_text VARCHAR2 DEFAULT NULL,
77 p_debug BOOLEAN DEFAULT FALSE,
78 p_row LONG DEFAULT NULL,
79 p_check_org BOOLEAN DEFAULT TRUE)
80 RETURN NUMBER;
81
82 FUNCTION DERIVE_PARTNER_ORG_ID
83 (p_table_name VARCHAR2,
84 p_org_partner_name VARCHAR2,
85 p_cust_account_number VARCHAR2 DEFAULT 0,
86 p_org_partner_id VARCHAR2,
87 p_instance_code VARCHAR2,
88 p_partner_type NUMBER,
89 p_error_text OUT NOCOPY VARCHAR2,
90 p_batch_id NUMBER DEFAULT NULL_VALUE,
91 p_severity NUMBER DEFAULT 0,
92 p_message_text VARCHAR2 DEFAULT NULL,
93 p_debug BOOLEAN DEFAULT FALSE,
94 p_row LONG DEFAULT NULL,
95 p_where_str VARCHAR2 DEFAULT NULL,
96 p_company_name_col BOOLEAN DEFAULT TRUE )
97 RETURN NUMBER;
98
99 FUNCTION DERIVE_PARTNER_SITE_ID
100 (p_table_name VARCHAR2,
101 p_partner_name VARCHAR2,
102 p_partner_site_code VARCHAR2,
103 p_CUST_ACCOUNT_NUMBER VARCHAR2 DEFAULT '0',
104 p_partner_site_id VARCHAR2,
105 p_instance_code VARCHAR2,
106 p_partner_type VARCHAR2,
107 p_error_text OUT NOCOPY VARCHAR2,
108 p_batch_id NUMBER DEFAULT NULL_VALUE,
109 p_severity NUMBER DEFAULT 0,
110 p_message_text VARCHAR2 DEFAULT NULL,
111 p_debug BOOLEAN DEFAULT FALSE,
112 p_row LONG DEFAULT NULL,
113 p_where_str VARCHAR2 DEFAULT NULL)
114 RETURN NUMBER;
115
116 FUNCTION DERIVE_PROJ_TASK_ID
117 (p_table_name VARCHAR2,
118 p_proj_col_name VARCHAR2,
119 p_proj_task_col_id VARCHAR2,
120 p_instance_code VARCHAR2,
121 p_entity_name VARCHAR2,
122 p_error_text OUT NOCOPY VARCHAR2,
123 p_task_col_name VARCHAR2 DEFAULT NULL,
124 p_batch_id NUMBER DEFAULT NULL_VALUE,
125 p_severity NUMBER DEFAULT 0,
126 p_message_text VARCHAR2 DEFAULT NULL,
127 p_debug BOOLEAN DEFAULT FALSE,
128 p_row LONG DEFAULT NULL)
129 RETURN NUMBER;
130
131 FUNCTION VALIDATE_UOM
132 (p_table_name VARCHAR2,
133 p_uom_col_name VARCHAR2, --uom_code
134 p_instance_id NUMBER,
135 p_instance_code VARCHAR2,
136 p_error_text OUT NOCOPY VARCHAR2,
137 p_batch_id NUMBER DEFAULT NULL_VALUE,
138 p_severity NUMBER DEFAULT 0,
139 p_message_text VARCHAR2 DEFAULT NULL,
140 p_debug BOOLEAN DEFAULT FALSE,
141 p_row LONG DEFAULT NULL)
142 RETURN NUMBER;
143
144 FUNCTION VALIDATE_DMD_CLASS
145 (p_table_name VARCHAR2,
146 p_dmd_class_column VARCHAR2, -- demand class column name
147 p_instance_id NUMBER,
148 p_instance_code VARCHAR2,
149 p_error_text OUT NOCOPY VARCHAR2,
150 p_batch_id NUMBER DEFAULT NULL_VALUE,
151 p_severity NUMBER DEFAULT 0,
152 p_message_text VARCHAR2 DEFAULT NULL,
153 p_debug BOOLEAN DEFAULT FALSE,
154 p_row LONG DEFAULT NULL)
155 RETURN NUMBER ;
156
157 FUNCTION GET_ERROR_MESSAGE
158 (p_app_short_name VARCHAR2,
159 p_error_code VARCHAR2,
160 p_message_text OUT NOCOPY VARCHAR2,
161 p_error_text OUT NOCOPY VARCHAR2,
162 p_token1 VARCHAR2 DEFAULT NULL,
163 p_token_value1 VARCHAR2 DEFAULT NULL,
164 p_token2 VARCHAR2 DEFAULT NULL,
165 p_token_value2 VARCHAR2 DEFAULT NULL,
166 p_token3 VARCHAR2 DEFAULT NULL,
167 p_token_value3 VARCHAR2 DEFAULT NULL,
168 p_token4 VARCHAR2 DEFAULT NULL,
169 p_token_value4 VARCHAR2 DEFAULT NULL,
170 p_token5 VARCHAR2 DEFAULT NULL,
171 p_token_value5 VARCHAR2 DEFAULT NULL)
172 RETURN NUMBER;
173
174 FUNCTION DERIVE_BILL_SEQUENCE_ID
175 (p_table_name VARCHAR2,
176 p_bom_col_name VARCHAR2, --bom_name
177 p_bom_col_id VARCHAR2, --bill_sequence_id
178 p_instance_code VARCHAR2,
179 p_error_text OUT NOCOPY VARCHAR2,
180 p_batch_id NUMBER DEFAULT NULL_VALUE,
181 p_severity NUMBER DEFAULT 0,
182 p_message_text VARCHAR2 DEFAULT NULL,
183 p_debug BOOLEAN DEFAULT FALSE,
184 p_row LONG DEFAULT NULL)
185 RETURN NUMBER;
186
187 FUNCTION DERIVE_ROUTING_SEQUENCE_ID
188 (p_table_name VARCHAR2,
189 p_rtg_col_name VARCHAR2, --routing_name
190 p_rtg_col_id VARCHAR2, --routing_sequence_id
191 p_instance_code VARCHAR2,
192 p_error_text OUT NOCOPY VARCHAR2,
193 p_batch_id NUMBER DEFAULT NULL_VALUE,
194 p_severity NUMBER DEFAULT 0,
195 p_message_text VARCHAR2 DEFAULT NULL,
196 p_debug BOOLEAN DEFAULT FALSE,
197 p_row LONG DEFAULT NULL,
198 p_where_str VARCHAR2 DEFAULT NULL,
199 p_item_id VARCHAR2 DEFAULT 'inventory_item_id')
200 RETURN NUMBER;
201
202 FUNCTION SET_PROCESS_FLAG
203 (p_table_name VARCHAR2,
204 p_instance_id NUMBER,
205 p_instance_code VARCHAR2,
206 p_process_flag NUMBER,
207 p_error_text OUT NOCOPY VARCHAR2,
208 p_where_str VARCHAR2 DEFAULT NULL,
209 p_debug BOOLEAN DEFAULT FALSE,
210 p_batch_id NUMBER DEFAULT NULL_VALUE,
211 p_instance_id_col VARCHAR2 DEFAULT 'SR_INSTANCE_ID')
212 RETURN NUMBER;
213
214
215 -- =========== DP Procedures and fUNCTIONS=================
216
217 FUNCTION DERIVE_LEVEL_ID
218 (p_table_name VARCHAR2,
219 p_level_name_col VARCHAR2, --level_name
220 p_level_id_col VARCHAR2, --level_id
221 p_severity NUMBER DEFAULT G_SEV_ERROR,
222 p_message_text VARCHAR2 DEFAULT NULL,
223 p_instance_code VARCHAR2,
224 p_batch_id NUMBER DEFAULT NULL_VALUE,
225 p_error_text OUT NOCOPY VARCHAR2)
226
227 RETURN NUMBER;
228
229 FUNCTION DERIVE_SR_LEVEL_PK
230 (p_table_name VARCHAR2,
231 p_level_val_col VARCHAR2, --level value col name
232 p_level_pk_col VARCHAR2, --level_pk column name
233 p_level_id_col VARCHAR2, --level_id col name
234 p_instance_code VARCHAR2,
235 p_message_text VARCHAR2 DEFAULT NULL,
236 p_batch_id NUMBER DEFAULT NULL_VALUE,
237 p_error_text OUT NOCOPY VARCHAR2)
238 RETURN NUMBER;
239
240 FUNCTION DERIVE_SR_PK
241 (p_table_name VARCHAR2,
242 p_column_name VARCHAR2, --level value col name
243 p_pk_col_name VARCHAR2, --level_pk column name
244 p_level_id VARCHAR2, --level_id
245 p_severity VARCHAR2 DEFAULT G_SEV3_ERROR,
246 p_instance_id NUMBER,
247 p_instance_code VARCHAR2,
248 p_message_text VARCHAR2 DEFAULT NULL,
249 p_batch_id NUMBER DEFAULT NULL_VALUE,
250 p_error_text OUT NOCOPY VARCHAR2)
251 RETURN NUMBER;
252
253
254 FUNCTION DERIVE_LEVEL_PK
255 (p_table_name VARCHAR2,
256 p_level_val_col VARCHAR2, --level value col name
257 p_level_name_col VARCHAR2, --level_name column name
258 p_level_pk_col VARCHAR2, --level_val col name
259 p_severity VARCHAR2 DEFAULT G_SEV3_ERROR,
260 p_instance_code VARCHAR2,
261 p_instance_id NUMBER,
262 p_message_text VARCHAR2,
263 p_batch_id NUMBER DEFAULT NULL_VALUE,
264 p_error_text OUT NOCOPY VARCHAR2)
265 RETURN NUMBER;
266
267 PROCEDURE LOG_MESSAGE
268 (p_error_text IN VARCHAR2);
269
270 FUNCTION GET_INSTANCE_CODE
271 (p_trading_partner_id number)
272 RETURN VARCHAR2;
273
274 PROCEDURE retn_delete_flag
275 (p_syncind IN VARCHAR2,
276 p_return OUT NOCOPY NUMBER);
277
278 PROCEDURE retn_schedule_id
279 (p_schedule_type IN VARCHAR2,
280 p_schid OUT NOCOPY NUMBER);
281
282 PROCEDURE Explode_Composite_Dates
283 (errbuf OUT NOCOPY VARCHAR2,
284 retcode OUT NOCOPY VARCHAR2,
285 p_dest_table IN VARCHAR2,
286 p_instance_id IN NUMBER,
287 p_calendar_type_id IN NUMBER,
288 p_calendar_code IN VARCHAR2,
289 p_seq_num IN NUMBER,
290 p_year IN VARCHAR2,
291 p_year_description IN VARCHAR2,
292 p_year_start_date IN DATE,
293 p_year_end_date IN DATE,
294 p_quarter IN VARCHAR2,
295 p_quarter_description IN VARCHAR2,
296 p_quarter_start_date IN DATE,
297 p_quarter_end_date IN DATE,
298 p_month IN VARCHAR2,
299 p_month_description IN VARCHAR2,
300 p_month_start_date IN DATE,
301 p_month_end_date IN DATE,
302 p_week IN VARCHAR2,
303 p_week_description IN VARCHAR2,
304 p_week_start_date IN DATE,
305 p_week_end_date IN DATE);
306
307
308 FUNCTION ret_code
309 (p_entity_name IN VARCHAR2,
310 p_id IN NUMBER)
311 RETURN VARCHAR2;
312
313 FUNCTION ret_org_code
314 (p_sr_instance_id IN NUMBER,
315 p_organization_id IN NUMBER)
316 RETURN VARCHAR2;
317
318 FUNCTION ret_partner_name
319 (p_partner_id IN NUMBER)
320 RETURN VARCHAR2;
321
322 FUNCTION ret_partner_name
323 (p_instance_id IN NUMBER,
324 p_sr_tp_id IN NUMBER,
325 p_partner_type IN NUMBER)
326 RETURN VARCHAR2;
327
328 FUNCTION ret_partner_site
329 (p_partner_site_id IN NUMBER)
330 RETURN VARCHAR2;
331
332 FUNCTION ret_partner_site
333 (p_instance_id IN NUMBER,
334 p_sr_tp_site_id IN NUMBER,
335 p_partner_type IN NUMBER)
336 RETURN VARCHAR2;
337
338 FUNCTION ret_desig
339 (p_designator_id IN NUMBER)
340 RETURN VARCHAR2;
341
342 FUNCTION ret_sr_instance_code
343 (p_sr_instance_id IN NUMBER)
344 RETURN VARCHAR2;
345
349 p_organization_id IN NUMBER,
346 FUNCTION ret_project_number
347 (p_project_id IN NUMBER ,
348 p_sr_instance_id IN NUMBER ,
350 p_plan_id IN NUMBER)
351 RETURN VARCHAR2;
352
353 FUNCTION ret_task_number
354 (p_project_id IN NUMBER,
355 p_task_id IN NUMBER,
356 p_sr_instance_id IN NUMBER,
357 p_organization_id IN NUMBER,
358 p_plan_id IN NUMBER)
359 RETURN VARCHAR2;
360
361 FUNCTION ret_wip_entity_name
362 (p_wip_entity_id IN NUMBER,
363 p_sr_instance_id IN NUMBER,
364 p_organization_id IN NUMBER,
365 p_plan_id IN NUMBER)
366 RETURN VARCHAR2;
367
368 FUNCTION ret_item_name
369 (p_item_id IN NUMBER,
370 p_sr_instance_id IN NUMBER,
371 p_organization_id IN NUMBER,
372 p_plan_id IN NUMBER)
373 RETURN VARCHAR2;
374
375 PROCEDURE retn_yes_no_value
376 (p_yes_no_code IN VARCHAR2,
377 p_yes_no_value OUT NOCOPY NUMBER);
378
379 -- For MSC_GROUPS and MSC_GROUP_COMPANIES aeroexhange
380 FUNCTION DERIVE_GROUP_ID
381 (p_table_name VARCHAR2,
382 p_grp_col_name VARCHAR2,
383 p_grp_col_id VARCHAR2,
384 p_instance_id NUMBER,
385 p_instance_code VARCHAR2,
386 p_error_text OUT NOCOPY VARCHAR2,
387 p_batch_id NUMBER DEFAULT NULL_VALUE,
388 p_severity NUMBER DEFAULT 0,
389 p_message_text VARCHAR2 DEFAULT NULL,
390 p_debug BOOLEAN DEFAULT FALSE,
391 p_row LONG DEFAULT NULL)
392 RETURN NUMBER;
393
394 -- For evaluating the user logged in for Operator resp.
395 FUNCTION IS_OPERATOR (p_user_id NUMBER)
396 RETURN BOOLEAN ;
397
398 -- Moved the function from pre-processor.
399 FUNCTION GET_MY_COMPANY
400 RETURN VARCHAR2;
401
402 FUNCTION DERIVE_SETUP_SR_LEVEL_PK
403 (p_table_name VARCHAR2,
404 p_level_val_col VARCHAR2, --level value col name
405 p_level_pk_col VARCHAR2, --level_pk column name
406 p_level_id_col VARCHAR2, --level_id col name
407 p_instance_code VARCHAR2,
408 p_instance_id NUMBER,
409 p_message_text VARCHAR2 DEFAULT NULL,
410 p_batch_id NUMBER DEFAULT NULL_VALUE,
411 p_error_text OUT NOCOPY VARCHAR2)
412 RETURN NUMBER;
413
414
415 FUNCTION DERIVE_DEPARTMENT_ID
416 (p_table_name VARCHAR2,
417 p_resource_col_name VARCHAR2, --resource code
418 p_department_col_name VARCHAR2, --department code
419 p_department_col_id VARCHAR2, --department id
420 p_instance_code VARCHAR2,
421 p_error_text OUT NOCOPY VARCHAR2,
422 p_batch_id NUMBER DEFAULT NULL_VALUE,
423 p_severity NUMBER DEFAULT 0,
424 p_message_text VARCHAR2 DEFAULT NULL,
425 p_debug BOOLEAN DEFAULT FALSE,
426 p_row LONG DEFAULT NULL)
427 RETURN NUMBER ;
428
429 FUNCTION DERIVE_RESOURCE_ID
430 (p_table_name VARCHAR2,
431 p_resource_col_name VARCHAR2, --resource code
432 p_department_col_name VARCHAR2, --department code
433 p_resource_col_id VARCHAR2, --resource id
434 p_instance_code VARCHAR2,
435 p_error_text OUT NOCOPY VARCHAR2,
436 p_batch_id NUMBER DEFAULT NULL_VALUE,
437 p_severity NUMBER DEFAULT 0,
438 p_message_text VARCHAR2 DEFAULT NULL,
439 p_debug BOOLEAN DEFAULT FALSE,
440 p_row LONG DEFAULT NULL,
441 p_where_str VARCHAR2 DEFAULT NULL)
442 RETURN NUMBER ;
443
444 FUNCTION DERIVE_OPERATION_SEQUENCE_ID
445 (p_table_name VARCHAR2,
446 p_operation_seq_num VARCHAR2, --OPERATION_SEQ_NUM
447 p_routing_col_name VARCHAR2, --ROUTING_NAME
448 p_sequence_col_id VARCHAR2, --OPERATION_SEQUENCE_ID
449 p_op_effectivity_date VARCHAR2, --operation_effectivity_date
450 p_operation_seq_code VARCHAR2, --operation_seq_code
451 p_routing_sequence_id VARCHAR2, --routing_sequence_id
452 p_instance_code VARCHAR2,
453 p_error_text OUT NOCOPY VARCHAR2,
454 p_batch_id NUMBER DEFAULT NULL_VALUE,
455 p_severity NUMBER DEFAULT 0,
456 p_message_text VARCHAR2 DEFAULT NULL,
457 p_debug BOOLEAN DEFAULT FALSE,
458 p_row LONG DEFAULT NULL)
459 RETURN NUMBER ;
460
461 /*S_OP */
462 FUNCTION CHECK_DP_ENABLED_FLAG
463 (
464 p_MRP_PLANNING_CODE NUMBER,
465 p_PICK_COMPONENT_FLAG VARCHAR2,
466 p_MSD_PLANING_PERCENTAGE NUMBER,
467 p_ATO_FORECAST_CONTROL NUMBER
468 )
469 RETURN NUMBER ;
470 /*S_OP */
471
472 FUNCTION get_stream_name (x varchar2) return varchar2;
473
474 END MSC_ST_UTIL;