[Home] [Help]
PACKAGE: APPS.MSC_CL_PRE_PROCESS
Source
1 PACKAGE MSC_CL_PRE_PROCESS AUTHID CURRENT_USER AS
2 /* $Header: MSCCLPPS.pls 120.8 2010/01/20 11:34:37 vsiyer ship $ */
3
4 ----- ARRAY DATA TYPE --------------------------------------------------
5
6 TYPE NumTblTyp IS TABLE OF NUMBER;
7
8 SYS_YES CONSTANT NUMBER := 1;
9 SYS_NO CONSTANT NUMBER := 2;
10
11 ----- GLobal Variable -------------------------------------------------
12 v_batch_size NUMBER ;
13 v_debug BOOLEAN;
14 v_current_date DATE ;
15 v_current_user NUMBER ;
16 v_flag NUMBER := SYS_NO;
17 v_instance_code VARCHAR2(3);
18 v_refresh_id NUMBER;
19 v_instance_id NUMBER;
20 v_sql_stmt VARCHAR2(4000);
21 v_instance_type NUMBER;
22
23
24 ----- CONSTANTS --------------------------------------------------------
25
26
27 G_CONC_ERROR CONSTANT NUMBER := 3;
28 G_SUCCESS CONSTANT NUMBER := 0;
29 G_WARNING CONSTANT NUMBER := 1;
30 G_ERROR CONSTANT NUMBER := 2;
31 G_SCE CONSTANT NUMBER := 5;
32
33 G_COLLECTION_PROGRAM CONSTANT NUMBER := 1;
34 G_PULL_PROGRAM CONSTANT NUMBER := 2;
35
36 G_APPS107 CONSTANT NUMBER := 1;
37 G_APPS110 CONSTANT NUMBER := 2;
38 G_APPS115 CONSTANT NUMBER := 3;
39
40 G_INS_DISCRETE CONSTANT NUMBER := 1;
41 G_INS_PROCESS CONSTANT NUMBER := 2;
42 G_INS_OTHER CONSTANT NUMBER := 3;
43 G_INS_MIXED CONSTANT NUMBER := 4;
44
45 G_ST_EMPTY CONSTANT NUMBER := 0; -- no instance data exists;
46 G_ST_PULLING CONSTANT NUMBER := 1;
47 G_ST_READY CONSTANT NUMBER := 2;
48 G_ST_COLLECTING CONSTANT NUMBER := 3;
49 G_ST_PURGING CONSTANT NUMBER := 4;
50 G_ST_PRE_PROCESSING CONSTANT NUMBER := 5;
51
52
53 G_MFG_CAL CONSTANT NUMBER := 2;
54 G_FISCAL_CAL CONSTANT NUMBER := 3;
55 G_COMPOSITE_CAL CONSTANT NUMBER := 4;
56
57
58 -- Calling Module
59 G_APS CONSTANT NUMBER := 1;
60 G_DP CONSTANT NUMBER := 2;
61
62
63 -- NULL VALUE USED IN THE WHERE CLAUSE
64
65 NULL_DATE CONSTANT DATE := SYSDATE-36500;
66 NULL_VALUE CONSTANT NUMBER := -23453; -- null value for positive number
67 NULL_CHAR CONSTANT VARCHAR2(6) := '-23453';
68 HOLD_DATE CONSTANT DATE := SYSDATE +365000 ;
69
70 -- ============ Task Control ================
71
72 PIPE_TIME_OUT CONSTANT NUMBER := 30; -- 30 secs
73 START_TIME DATE;
74
75 -- ================== Worker Status ===================
76
77 OK CONSTANT NUMBER := 1;
78 FAIL CONSTANT NUMBER := 0;
79
80 -- ================== Process Flag ===================
81 G_NEW CONSTANT NUMBER := 1;
82 G_IN_PROCESS CONSTANT NUMBER := 2;
83 G_ERROR_FLG CONSTANT NUMBER := 3;
84 G_PROPAGATION CONSTANT NUMBER := 4;
85 G_VALID CONSTANT NUMBER := 5;
86
87 -- ================== Error Types ===================
88 G_SEV_ERROR CONSTANT NUMBER := 1;
89 G_SEV_WARNING CONSTANT NUMBER := 2;
90 G_SEV3_ERROR CONSTANT NUMBER := 3;
91
92 -- ================= Task Number ====================
93
94 TASK_ITEM CONSTANT NUMBER := 1;
95 TASK_SUPPLIER_CAPACITY CONSTANT NUMBER := 2;
96 TASK_CATEGORIES_SOURCING CONSTANT NUMBER := 3;
97 TASK_DEPT_RESOURCES CONSTANT NUMBER := 4;
98 TASK_MATERIAL_SUPPLY CONSTANT NUMBER := 5;
99 TASK_MATERIAL_DEMAND CONSTANT NUMBER := 6;
100 TASK_SO_DEMAND CONSTANT NUMBER := 7;
101 TASK_BOM_ROUTING CONSTANT NUMBER := 8;
102 TASK_RESOURCE_DEMAND CONSTANT NUMBER := 9;
103 TASK_BIS_PERIODS CONSTANT NUMBER := 10;
104 TASK_UOM_CONV CONSTANT NUMBER := 11;
105 TASK_SAFETY_STOCK CONSTANT NUMBER := 12;
106 TASK_RESERVATION CONSTANT NUMBER := 13;
107 TASK_ITEM_CST CONSTANT NUMBER := 14;
108 TASK_ITEM_SUBSTITUTE CONSTANT NUMBER := 15; -- Product Item Substitute
109 TASK_CAL_ASSIGNMENTS CONSTANT NUMBER := 16;
110 TASK_IRO CONSTANT NUMBER := 17;
111 TASK_ERO CONSTANT NUMBER := 18;
112 TASK_SALES_CHANNEL CONSTANT NUMBER := 19;
113 TASK_FISCAL_CALENDAR CONSTANT NUMBER := 33;
114 -- TASK_VISITS CONSTANT NUMBER := 34; --- CMRO Proj bug 9135694
115 TASK_CMRO CONSTANT NUMBER := 34; --- CMRO Proj bug 9135694
116
117 ---- =============Task Number for DP entities ===========
118 TASK_LEVEL_VALUE CONSTANT NUMBER := 20 ;
119 TASK_LEVEL_ASSOCIATION CONSTANT NUMBER := 21 ;
120 TASK_BOOKING_DATA CONSTANT NUMBER := 22 ;
121 TASK_SHIPMENT_DATA CONSTANT NUMBER := 23 ;
122 TASK_MFG_FORECAST CONSTANT NUMBER := 24 ;
123 TASK_PRICE_LIST CONSTANT NUMBER := 25 ;
124 TASK_ITEM_LIST_PRICE CONSTANT NUMBER := 26 ;
125 TASK_CS_DATA CONSTANT NUMBER := 27 ;
126 TASK_CURR_CONV CONSTANT NUMBER := 28 ;
127 TASK_DP_UOM_CONV CONSTANT NUMBER := 29 ;
128 TASK_FISCAL_TIME CONSTANT NUMBER := 30 ;
129 TASK_COMPANY_USERS CONSTANT NUMBER := 31 ;
130 TASK_COMPOSITE_TIME CONSTANT NUMBER := 32 ;
131
132
133 TASK_ALL_COMPLETED CONSTANT NUMBER := 99; -- to communicate the completion of all tasks
134
135 UNRESOVLABLE_ERROR CONSTANT NUMBER := -9999999;
136
137 -- ==================ITEM DEFAULTS =====================
138
139 G_LOT_CONTROL_CODE CONSTANT NUMBER := 2; --No
140 G_ROUNDING_CONTROL_TYPE CONSTANT NUMBER := 2; --No
141 G_IN_SOURCE_PLAN CONSTANT NUMBER := 2; --No
142 G_MRP_PLANNING_CODE CONSTANT NUMBER := 7; --MRP and DRP planned
143 G_MRP_CALCULATE_ATP_FLAG CONSTANT NUMBER := 2; --No
144 G_BUILT_IN_WIP_FLAG CONSTANT NUMBER := 1; --Yes
145 G_PURCHASING_ENABLED_FLAG CONSTANT NUMBER := 2; --No
146 G_PLANNING_MAKE_BUY_CODE CONSTANT NUMBER := 1; --Yes
147 G_INVENTORY_PLANNING_CODE CONSTANT NUMBER := 6;
148 G_REPETITIVE_TYPE CONSTANT NUMBER := 1; --Discrete bug#2399135
149 G_ENGINEERING_ITEM_FLAG CONSTANT NUMBER := 2; --No
150 G_WIP_SUPPLY_TYPE CONSTANT NUMBER := 1; --Push
151 G_BOM_ITEM_TYPE CONSTANT NUMBER := 4; --Standard BOM
152 G_ATO_FORECAST_CONTROL CONSTANT NUMBER := 2; --No
153 G_INVENTORY_ITEM_FLAG CONSTANT NUMBER := 1; --Yes
154 G_INVENTORY_TYPE CONSTANT NUMBER := 1;
155 G_SERIAL_NUMBER_CONTROL_CODE CONSTANT NUMBER := 2; -- SERIAL_NUMBER_FLAG set to NO
156 G_DRP_PLANNED CONSTANT NUMBER := 2; --No
157
158
159 G_END_ASSEMBLY_PEGGING_FLAG CONSTANT VARCHAR2(1) := 'N';
160 G_ATP_COMPONENTS_FLAG CONSTANT VARCHAR2(1) := 'N';--No
161 G_ATP_FLAG CONSTANT VARCHAR2(1) := 'N';--No
162 G_INVENTORY_ASSET_FLAG CONSTANT VARCHAR2(1) := 'Y';
163
164 -- ==================BOM DEFAULTS =====================
165
166 G_ASSEMBLY_TYPE CONSTANT NUMBER:= 1 ;--Manufacturing Bill
167 G_BOM_SCALING_TYPE CONSTANT NUMBER:= NULL;
168 G_ATP_FLAG_BOM CONSTANT NUMBER:= 1;
169 G_ASSEMBLY_QUANTITY CONSTANT NUMBER:= 1 ;
170 G_USAGE_QUANTITY CONSTANT NUMBER:= 1 ;
171 G_COMPONENT_YEILD_FACTOR CONSTANT NUMBER:= 1 ;
172 G_USE_UP_CODE CONSTANT NUMBER:= NULL ;
173 G_OPTIONAL_COMPONENT CONSTANT NUMBER:= NULL ;
174 G_COMPONENT_TYPE CONSTANT NUMBER:= 1 ;
175 G_OPERATION_SEQ_CODE CONSTANT VARCHAR2(10) := '1' ;
176 G_EFFECTIVITY_DATE DATE := SYSDATE;
177 G_PRIORITY CONSTANT NUMBER := 0;
178
179
180 -- ==================DEPT/RESOURCE DEFAULTS =====================
181
182 G_LINE_FLAG CONSTANT VARCHAR2(1) := '2';
183 G_AVAILABLE_24_HOURS_FLAG CONSTANT NUMBER := 1;
184 G_CTP_FLAG CONSTANT NUMBER := 2;
185 G_RESOURCE_TYPE CONSTANT NUMBER := 1;--Machine
186 G_RESOURCE_INCLUDE_FLAG CONSTANT NUMBER := 1;
187 G_AGGREGATED_RESOURCE_FLAG CONSTANT NUMBER := 2;
188
189 -- ==================PROJECT/TASK DEFAULTS =====================
190
191 G_SEIBAN_NUMBER_FLAG CONSTANT NUMBER := 2;
192
193 -- ==================ROUTING DEFAULTS =====================
194
195 G_ROUTING_TYPE CONSTANT NUMBER := 1; --Mfg Routing
196 G_OPTION_DEPENDENT_FLAG CONSTANT NUMBER := 1; --No
197 G_BASIS_TYPE CONSTANT NUMBER := 1;
198 G_PRINCIPAL_FLAG CONSTANT NUMBER := 1;
199 G_CFM_ROUTING_FLAG CONSTANT NUMBER := 2;
200 G_RESOURCE_SEQ_CODE CONSTANT VARCHAR2(10):= '1';
201 G_ALTERNATE_NUMBER CONSTANT NUMBER := 0;
202 G_TRANSITION_TYPE CONSTANT NUMBER := 2; -- Added for OSFM Integration
203 -- ================== DEFAULT for SALES ORDER =====================
204 G_RESERVATION_TYPE CONSTANT NUMBER := 1;
205 G_SO_DEMAND_SOURCE_TYPE CONSTANT NUMBER := 8;
206 G_COMPLETED_QUANTITY CONSTANT NUMBER := 0;
207 G_CTO_FLAG CONSTANT NUMBER := 2;
208 G_AVAILABLE_TO_MRP CONSTANT VARCHAR2(1):= 'Y';
209 G_FORECAST_VISIBLE CONSTANT VARCHAR2(1):= 'Y';
210 G_DEMAND_VISIBLE CONSTANT VARCHAR2(1):= 'Y';
211
212
213 -- ================== DEFAULT for DEMAND =====================
214
215 G_DEMAND_TYPE CONSTANT NUMBER := 1;--Non repetitive for WIP comp demand
216 G_WIP_STATUS_CODE CONSTANT NUMBER := 1;--Unreleased
217 G_BUCKET_TYPE CONSTANT NUMBER := 1;--Days
218 G_DISPOSITION_TYPE CONSTANT NUMBER := 1;
219
220
221 -- ================== MISCELLANEOUS =====================
222
223 G_NEW_REQUEST CONSTANT NUMBER := 0;--new request
224 G_CAL_REQ_DATA CONSTANT NUMBER := 1;--Calendar sub-request
225 G_DP_LV_REQ_DATA CONSTANT NUMBER := 2;--DP Level Values sub-request
226 G_DP_CS_REQ_DATA CONSTANT NUMBER := 3;--DP custom stream sub-request
227 G_ODS_REQ_DATA CONSTANT NUMBER := 4;--ODS Load sub-request
228 G_DP_BOM_DATA CONSTANT NUMBER := 5;--DP BOM collection sub-request
229
230
231 G_COMPANY_ID CONSTANT NUMBER := -1;
232 G_USING_ORG_ID CONSTANT NUMBER := -1;
233 G_CAL_EXCEP_SET_ID CONSTANT NUMBER := -1;
234 G_FOR_INV_ATP_FLAG CONSTANT NUMBER := 0;
235 G_FOR_MPS_RELIEF CONSTANT NUMBER := 0;
236 G_INV_ATP_FLAG CONSTANT NUMBER := 2;
237 G_MPS_RELIEF CONSTANT NUMBER := 2;
238 G_CONSUME_FORECAST CONSTANT NUMBER := 2;
239 G_UPDATE_TYPE CONSTANT NUMBER := 6;
240 G_VENDOR CONSTANT NUMBER := 1;
241 G_CUSTOMER CONSTANT NUMBER := 2;
242 G_ORGANIZATION CONSTANT NUMBER := 3;
243 G_CARRIER CONSTANT NUMBER := 4;
244 G_FORECAST_DESIGNATOR CONSTANT NUMBER := 6;
245 G_RELATIONSHIP_TYPE CONSTANT NUMBER := 2; -- Product Item Substitute
246 G_NO_PLAN_PERCENTAGE CONSTANT NUMBER := 1; -- Profile option choice 1 for profile MSD_PLANNING_PERCENTAGE.
247 G_EXCLUDE_OPTION_CLASS CONSTANT NUMBER := 3; -- Profile option choice 3 for profile MSD_PLANNING_PERCENTAGE.
248
249 -- ================ COMPANY USER ====================
250 G_COMP_USR_YES CONSTANT NUMBER := 2;
251 G_COMP_USR_NO CONSTANT NUMBER := 1;
252
253 -- ================= Procedures ====================
254
255 PROCEDURE LAUNCH_MONITOR( ERRBUF OUT NOCOPY VARCHAR2,
256 RETCODE OUT NOCOPY NUMBER,
257 p_instance_id IN NUMBER,
258 p_timeout IN NUMBER DEFAULT 60,
259 p_batch_size IN NUMBER DEFAULT 1000,
260 p_total_worker_num IN NUMBER DEFAULT 3,
261 p_cal_enabled IN NUMBER DEFAULT SYS_NO,
262 p_dmd_class_enabled IN NUMBER DEFAULT SYS_NO,
263 p_tp_enabled IN NUMBER DEFAULT SYS_NO,
264 p_ctg_enabled IN NUMBER DEFAULT SYS_NO,
265 p_item_cat_enabled IN NUMBER DEFAULT SYS_NO,
266 p_uom_enabled IN NUMBER DEFAULT SYS_NO,
267 p_uom_class_enabled IN NUMBER DEFAULT SYS_NO,
268 p_desig_enabled IN NUMBER DEFAULT SYS_NO,
269 p_project_enabled IN NUMBER DEFAULT SYS_NO,
270 p_item_enabled IN NUMBER DEFAULT SYS_NO,
271 p_sup_cap_enabled IN NUMBER DEFAULT SYS_NO,
272 p_safety_stk_enabled IN NUMBER DEFAULT SYS_NO,
273 p_ship_mthd_enabled IN NUMBER DEFAULT SYS_NO,
274 p_sourcing_enabled IN NUMBER DEFAULT SYS_NO,
275 p_bom_enabled IN NUMBER DEFAULT SYS_NO,
276 p_rtg_enabled IN NUMBER DEFAULT SYS_NO,
277 p_resources_enabled IN NUMBER DEFAULT SYS_NO,
278 p_mat_sup_enabled IN NUMBER DEFAULT SYS_NO,
279 p_mat_dmd_enabled IN NUMBER DEFAULT SYS_NO,
280 p_reserve_enabled IN NUMBER DEFAULT SYS_NO,
281 p_res_dmd_enabled IN NUMBER DEFAULT SYS_NO,
282 p_item_cst_enabled IN NUMBER DEFAULT SYS_NO,
283 p_parent_request_id IN NUMBER DEFAULT -1,
284 p_fiscal_cal_enabled IN NUMBER DEFAULT SYS_NO,
285 p_setup_enabled IN NUMBER DEFAULT SYS_NO,
286 p_link_dummy IN VARCHAR2 DEFAULT NULL,
287 p_item_rollup IN NUMBER DEFAULT SYS_YES,
288 p_level_value_enabled IN NUMBER DEFAULT SYS_NO,
289 p_level_assoc_enabled IN NUMBER DEFAULT SYS_NO,
290 p_booking_enabled IN NUMBER DEFAULT SYS_NO,
291 p_shipment_enabled IN NUMBER DEFAULT SYS_NO,
292 p_mfg_fct_enabled IN NUMBER DEFAULT SYS_NO,
293 p_list_price_enabled IN NUMBER DEFAULT SYS_NO,
294 p_cs_data_enabled IN NUMBER DEFAULT SYS_NO,
295 p_cs_dummy IN VARCHAR2 DEFAULT NULL,
296 p_cs_refresh IN NUMBER DEFAULT SYS_NO,
297 p_curr_conv_enabled IN NUMBER DEFAULT SYS_NO,
298 p_uom_conv_enabled IN NUMBER DEFAULT SYS_NO,
299 p_calling_module IN NUMBER DEFAULT G_APS,
300 p_comp_users_enabled IN NUMBER DEFAULT SYS_NO,
301 p_item_substitute_enabled IN NUMBER DEFAULT SYS_NO,
302 p_planners_enabled IN NUMBER DEFAULT SYS_NO,
303 p_comp_cal_enabled IN NUMBER DEFAULT SYS_NO,
304 p_profile_enabled IN NUMBER DEFAULT SYS_NO,
305 p_cal_assignment_enabled IN NUMBER DEFAULT SYS_NO,
306 p_iro_enabled IN NUMBER DEFAULT SYS_NO,
307 p_ero_enabled IN NUMBER DEFAULT SYS_NO,
308 p_sales_channel_enabled IN NUMBER DEFAULT SYS_NO, -- added for bug # 7704614
309 p_fiscal_calendar_enabled IN NUMBER DEFAULT SYS_NO,
310 p_CMRO_enabled IN NUMBER DEFAULT SYS_NO); --- CMRO Proj bug 9135694
311
312
313
314 PROCEDURE LAUNCH_WORKER( ERRBUF OUT NOCOPY VARCHAR2,
315 RETCODE OUT NOCOPY NUMBER,
316 p_monitor_request_id IN NUMBER,
317 p_instance_id IN NUMBER,
318 p_lcid IN NUMBER,
319 p_timeout IN NUMBER,
320 p_batch_size IN NUMBER DEFAULT 1000,
321 p_uom_class_enabled IN NUMBER DEFAULT SYS_NO,
322 p_item_cat_enabled IN NUMBER DEFAULT SYS_NO,
323 p_item_enabled IN NUMBER DEFAULT SYS_NO,
324 p_sup_cap_enabled IN NUMBER DEFAULT SYS_NO,
325 p_safety_stk_enabled IN NUMBER DEFAULT SYS_NO,
326 p_ship_mthd_enabled IN NUMBER DEFAULT SYS_NO,
327 p_sourcing_enabled IN NUMBER DEFAULT SYS_NO,
328 p_bom_enabled IN NUMBER DEFAULT SYS_NO,
329 p_rtg_enabled IN NUMBER DEFAULT SYS_NO,
330 p_resources_enabled IN NUMBER DEFAULT SYS_NO,
331 p_mat_sup_enabled IN NUMBER DEFAULT SYS_NO,
332 p_mat_dmd_enabled IN NUMBER DEFAULT SYS_NO,
333 p_reserve_enabled IN NUMBER DEFAULT SYS_NO,
334 p_res_dmd_enabled IN NUMBER DEFAULT SYS_NO,
335 p_item_cst_enabled IN NUMBER DEFAULT SYS_NO,
336 p_fiscal_cal_enabled IN NUMBER DEFAULT SYS_NO,
337 p_comp_cal_enabled IN NUMBER DEFAULT SYS_NO,
338 p_setup_enabled IN NUMBER DEFAULT SYS_NO,
339 p_item_rollup IN NUMBER DEFAULT SYS_YES,
340 p_level_value_enabled IN NUMBER DEFAULT SYS_NO,
341 p_level_assoc_enabled IN NUMBER DEFAULT SYS_NO,
342 p_booking_enabled IN NUMBER DEFAULT SYS_NO,
343 p_shipment_enabled IN NUMBER DEFAULT SYS_NO,
344 p_mfg_fct_enabled IN NUMBER DEFAULT SYS_NO,
345 p_list_price_enabled IN NUMBER DEFAULT SYS_NO,
346 p_cs_data_enabled IN NUMBER DEFAULT SYS_NO,
347 p_curr_conv_enabled IN NUMBER DEFAULT SYS_NO,
348 p_uom_conv_enabled IN NUMBER DEFAULT SYS_NO,
349 p_comp_users_enabled IN NUMBER DEFAULT SYS_NO,
350 p_item_substitute_enabled IN NUMBER DEFAULT SYS_NO,
351 p_cal_assignment_enabled IN NUMBER DEFAULT SYS_NO,
352 p_iro_enabled IN NUMBER DEFAULT SYS_NO,
353 p_ero_enabled IN NUMBER DEFAULT SYS_NO,
354 p_sales_channel_enabled IN NUMBER DEFAULT SYS_NO, -- added for bug # 7704614
355 p_fiscal_calendar_enabled IN NUMBER DEFAULT SYS_NO,
356 p_CMRO_enabled IN NUMBER DEFAULT SYS_NO, --- CMRO Proj bug 9135694
357 p_request_id IN NUMBER DEFAULT -1);
358
359
360 END MSC_CL_PRE_PROCESS;