[Home] [Help]
PACKAGE: APPS.MSC_CL_PRE_PROCESS
Source
1 PACKAGE MSC_CL_PRE_PROCESS AS -- specification
2 /* $Header: MSCCLPPS.pls 120.5.12010000.2 2008/07/10 11:10:46 arusubra 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
113 ---- =============Task Number for DP entities ===========
114 TASK_LEVEL_VALUE CONSTANT NUMBER := 20 ;
115 TASK_LEVEL_ASSOCIATION CONSTANT NUMBER := 21 ;
116 TASK_BOOKING_DATA CONSTANT NUMBER := 22 ;
117 TASK_SHIPMENT_DATA CONSTANT NUMBER := 23 ;
118 TASK_MFG_FORECAST CONSTANT NUMBER := 24 ;
119 TASK_PRICE_LIST CONSTANT NUMBER := 25 ;
120 TASK_ITEM_LIST_PRICE CONSTANT NUMBER := 26 ;
121 TASK_CS_DATA CONSTANT NUMBER := 27 ;
122 TASK_CURR_CONV CONSTANT NUMBER := 28 ;
123 TASK_DP_UOM_CONV CONSTANT NUMBER := 29 ;
124 TASK_FISCAL_TIME CONSTANT NUMBER := 30 ;
125 TASK_COMPANY_USERS CONSTANT NUMBER := 31 ;
126 TASK_COMPOSITE_TIME CONSTANT NUMBER := 32 ;
127
128
129 TASK_ALL_COMPLETED CONSTANT NUMBER := 99; -- to communicate the completion of all tasks
130
131 UNRESOVLABLE_ERROR CONSTANT NUMBER := -9999999;
132
133 -- ==================ITEM DEFAULTS =====================
134
135 G_LOT_CONTROL_CODE CONSTANT NUMBER := 2; --No
136 G_ROUNDING_CONTROL_TYPE CONSTANT NUMBER := 2; --No
137 G_IN_SOURCE_PLAN CONSTANT NUMBER := 2; --No
138 G_MRP_PLANNING_CODE CONSTANT NUMBER := 7; --MRP and DRP planned
139 G_MRP_CALCULATE_ATP_FLAG CONSTANT NUMBER := 2; --No
140 G_BUILT_IN_WIP_FLAG CONSTANT NUMBER := 1; --Yes
141 G_PURCHASING_ENABLED_FLAG CONSTANT NUMBER := 2; --No
142 G_PLANNING_MAKE_BUY_CODE CONSTANT NUMBER := 1; --Yes
143 G_INVENTORY_PLANNING_CODE CONSTANT NUMBER := 6;
144 G_REPETITIVE_TYPE CONSTANT NUMBER := 1; --Discrete bug#2399135
145 G_ENGINEERING_ITEM_FLAG CONSTANT NUMBER := 2; --No
146 G_WIP_SUPPLY_TYPE CONSTANT NUMBER := 1; --Push
147 G_BOM_ITEM_TYPE CONSTANT NUMBER := 4; --Standard BOM
148 G_ATO_FORECAST_CONTROL CONSTANT NUMBER := 2; --No
149 G_INVENTORY_ITEM_FLAG CONSTANT NUMBER := 1; --Yes
150 G_INVENTORY_TYPE CONSTANT NUMBER := 1;
151 G_SERIAL_NUMBER_CONTROL_CODE CONSTANT NUMBER := 2; -- SERIAL_NUMBER_FLAG set to NO
152 G_DRP_PLANNED CONSTANT NUMBER := 2; --No
153
154
155 G_END_ASSEMBLY_PEGGING_FLAG CONSTANT VARCHAR2(1) := 'N';
156 G_ATP_COMPONENTS_FLAG CONSTANT VARCHAR2(1) := 'N';--No
157 G_ATP_FLAG CONSTANT VARCHAR2(1) := 'N';--No
158 G_INVENTORY_ASSET_FLAG CONSTANT VARCHAR2(1) := 'Y';
159
160 -- ==================BOM DEFAULTS =====================
161
162 G_ASSEMBLY_TYPE CONSTANT NUMBER:= 1 ;--Manufacturing Bill
163 G_BOM_SCALING_TYPE CONSTANT NUMBER:= NULL;
164 G_ATP_FLAG_BOM CONSTANT NUMBER:= 1;
165 G_ASSEMBLY_QUANTITY CONSTANT NUMBER:= 1 ;
166 G_USAGE_QUANTITY CONSTANT NUMBER:= 1 ;
167 G_COMPONENT_YEILD_FACTOR CONSTANT NUMBER:= 1 ;
168 G_USE_UP_CODE CONSTANT NUMBER:= NULL ;
169 G_OPTIONAL_COMPONENT CONSTANT NUMBER:= NULL ;
170 G_COMPONENT_TYPE CONSTANT NUMBER:= 1 ;
171 G_OPERATION_SEQ_CODE CONSTANT VARCHAR2(10) := '1' ;
172 G_EFFECTIVITY_DATE DATE := SYSDATE;
173 G_PRIORITY CONSTANT NUMBER := 0;
174
175
176 -- ==================DEPT/RESOURCE DEFAULTS =====================
177
178 G_LINE_FLAG CONSTANT VARCHAR2(1) := '2';
179 G_AVAILABLE_24_HOURS_FLAG CONSTANT NUMBER := 1;
180 G_CTP_FLAG CONSTANT NUMBER := 2;
181 G_RESOURCE_TYPE CONSTANT NUMBER := 1;--Machine
182 G_RESOURCE_INCLUDE_FLAG CONSTANT NUMBER := 1;
183 G_AGGREGATED_RESOURCE_FLAG CONSTANT NUMBER := 2;
184
185 -- ==================PROJECT/TASK DEFAULTS =====================
186
187 G_SEIBAN_NUMBER_FLAG CONSTANT NUMBER := 2;
188
189 -- ==================ROUTING DEFAULTS =====================
190
191 G_ROUTING_TYPE CONSTANT NUMBER := 1; --Mfg Routing
192 G_OPTION_DEPENDENT_FLAG CONSTANT NUMBER := 1; --No
193 G_BASIS_TYPE CONSTANT NUMBER := 1;
194 G_PRINCIPAL_FLAG CONSTANT NUMBER := 1;
195 G_CFM_ROUTING_FLAG CONSTANT NUMBER := 2;
196 G_RESOURCE_SEQ_CODE CONSTANT VARCHAR2(10):= '1';
197 G_ALTERNATE_NUMBER CONSTANT NUMBER := 0;
198 G_TRANSITION_TYPE CONSTANT NUMBER := 2; -- Added for OSFM Integration
199 -- ================== DEFAULT for SALES ORDER =====================
200 G_RESERVATION_TYPE CONSTANT NUMBER := 1;
201 G_SO_DEMAND_SOURCE_TYPE CONSTANT NUMBER := 8;
202 G_COMPLETED_QUANTITY CONSTANT NUMBER := 0;
203 G_CTO_FLAG CONSTANT NUMBER := 2;
204 G_AVAILABLE_TO_MRP CONSTANT VARCHAR2(1):= 'Y';
205 G_FORECAST_VISIBLE CONSTANT VARCHAR2(1):= 'Y';
206 G_DEMAND_VISIBLE CONSTANT VARCHAR2(1):= 'Y';
207
208
209 -- ================== DEFAULT for DEMAND =====================
210
211 G_DEMAND_TYPE CONSTANT NUMBER := 1;--Non repetitive for WIP comp demand
212 G_WIP_STATUS_CODE CONSTANT NUMBER := 1;--Unreleased
213 G_BUCKET_TYPE CONSTANT NUMBER := 1;--Days
214 G_DISPOSITION_TYPE CONSTANT NUMBER := 1;
215
216
217 -- ================== MISCELLANEOUS =====================
218
219 G_NEW_REQUEST CONSTANT NUMBER := 0;--new request
220 G_CAL_REQ_DATA CONSTANT NUMBER := 1;--Calendar sub-request
221 G_DP_LV_REQ_DATA CONSTANT NUMBER := 2;--DP Level Values sub-request
222 G_DP_CS_REQ_DATA CONSTANT NUMBER := 3;--DP custom stream sub-request
223 G_ODS_REQ_DATA CONSTANT NUMBER := 4;--ODS Load sub-request
224 G_DP_BOM_DATA CONSTANT NUMBER := 5;--DP BOM collection sub-request
225
226
227 G_COMPANY_ID CONSTANT NUMBER := -1;
228 G_USING_ORG_ID CONSTANT NUMBER := -1;
229 G_CAL_EXCEP_SET_ID CONSTANT NUMBER := -1;
230 G_FOR_INV_ATP_FLAG CONSTANT NUMBER := 0;
231 G_FOR_MPS_RELIEF CONSTANT NUMBER := 0;
232 G_INV_ATP_FLAG CONSTANT NUMBER := 2;
233 G_MPS_RELIEF CONSTANT NUMBER := 2;
234 G_CONSUME_FORECAST CONSTANT NUMBER := 2;
235 G_UPDATE_TYPE CONSTANT NUMBER := 6;
236 G_VENDOR CONSTANT NUMBER := 1;
237 G_CUSTOMER CONSTANT NUMBER := 2;
238 G_ORGANIZATION CONSTANT NUMBER := 3;
239 G_CARRIER CONSTANT NUMBER := 4;
240 G_FORECAST_DESIGNATOR CONSTANT NUMBER := 6;
241 G_RELATIONSHIP_TYPE CONSTANT NUMBER := 2; -- Product Item Substitute
242 G_NO_PLAN_PERCENTAGE CONSTANT NUMBER := 1; -- Profile option choice 1 for profile MSD_PLANNING_PERCENTAGE.
243 G_EXCLUDE_OPTION_CLASS CONSTANT NUMBER := 3; -- Profile option choice 3 for profile MSD_PLANNING_PERCENTAGE.
244
245 -- ================ COMPANY USER ====================
246 G_COMP_USR_YES CONSTANT NUMBER := 2;
247 G_COMP_USR_NO CONSTANT NUMBER := 1;
248
249 -- ================= Procedures ====================
250
251 PROCEDURE LAUNCH_MONITOR( ERRBUF OUT NOCOPY VARCHAR2,
252 RETCODE OUT NOCOPY NUMBER,
253 p_instance_id IN NUMBER,
254 p_timeout IN NUMBER DEFAULT 60,
255 p_batch_size IN NUMBER DEFAULT 1000,
256 p_total_worker_num IN NUMBER DEFAULT 3,
257 p_cal_enabled IN NUMBER DEFAULT SYS_NO,
258 p_dmd_class_enabled IN NUMBER DEFAULT SYS_NO,
259 p_tp_enabled IN NUMBER DEFAULT SYS_NO,
260 p_ctg_enabled IN NUMBER DEFAULT SYS_NO,
261 p_item_cat_enabled IN NUMBER DEFAULT SYS_NO,
262 p_uom_enabled IN NUMBER DEFAULT SYS_NO,
263 p_uom_class_enabled IN NUMBER DEFAULT SYS_NO,
264 p_desig_enabled IN NUMBER DEFAULT SYS_NO,
265 p_project_enabled IN NUMBER DEFAULT SYS_NO,
266 p_item_enabled IN NUMBER DEFAULT SYS_NO,
267 p_sup_cap_enabled IN NUMBER DEFAULT SYS_NO,
268 p_safety_stk_enabled IN NUMBER DEFAULT SYS_NO,
269 p_ship_mthd_enabled IN NUMBER DEFAULT SYS_NO,
270 p_sourcing_enabled IN NUMBER DEFAULT SYS_NO,
271 p_bom_enabled IN NUMBER DEFAULT SYS_NO,
272 p_rtg_enabled IN NUMBER DEFAULT SYS_NO,
273 p_resources_enabled IN NUMBER DEFAULT SYS_NO,
274 p_mat_sup_enabled IN NUMBER DEFAULT SYS_NO,
275 p_mat_dmd_enabled IN NUMBER DEFAULT SYS_NO,
276 p_reserve_enabled IN NUMBER DEFAULT SYS_NO,
277 p_res_dmd_enabled IN NUMBER DEFAULT SYS_NO,
278 p_item_cst_enabled IN NUMBER DEFAULT SYS_NO,
279 p_parent_request_id IN NUMBER DEFAULT -1,
280 p_fiscal_cal_enabled IN NUMBER DEFAULT SYS_NO,
281 p_setup_enabled IN NUMBER DEFAULT SYS_NO,
282 p_link_dummy IN VARCHAR2 DEFAULT NULL,
283 p_item_rollup IN NUMBER DEFAULT SYS_YES,
284 p_level_value_enabled IN NUMBER DEFAULT SYS_NO,
285 p_level_assoc_enabled IN NUMBER DEFAULT SYS_NO,
286 p_booking_enabled IN NUMBER DEFAULT SYS_NO,
287 p_shipment_enabled IN NUMBER DEFAULT SYS_NO,
288 p_mfg_fct_enabled IN NUMBER DEFAULT SYS_NO,
289 p_list_price_enabled IN NUMBER DEFAULT SYS_NO,
290 p_cs_data_enabled IN NUMBER DEFAULT SYS_NO,
291 p_cs_dummy IN VARCHAR2 DEFAULT NULL,
292 p_cs_refresh IN NUMBER DEFAULT SYS_NO,
293 p_curr_conv_enabled IN NUMBER DEFAULT SYS_NO,
294 p_uom_conv_enabled IN NUMBER DEFAULT SYS_NO,
295 p_calling_module IN NUMBER DEFAULT G_APS,
296 p_comp_users_enabled IN NUMBER DEFAULT SYS_NO,
297 p_item_substitute_enabled IN NUMBER DEFAULT SYS_NO,
298 p_planners_enabled IN NUMBER DEFAULT SYS_NO,
299 p_comp_cal_enabled IN NUMBER DEFAULT SYS_NO,
300 p_profile_enabled IN NUMBER DEFAULT SYS_NO,
301 p_cal_assignment_enabled IN NUMBER DEFAULT SYS_NO,
302 p_iro_enabled IN NUMBER DEFAULT SYS_NO,
303 p_ero_enabled IN NUMBER DEFAULT SYS_NO);
304
305
306
307 PROCEDURE LAUNCH_WORKER( ERRBUF OUT NOCOPY VARCHAR2,
308 RETCODE OUT NOCOPY NUMBER,
309 p_monitor_request_id IN NUMBER,
310 p_instance_id IN NUMBER,
311 p_lcid IN NUMBER,
312 p_timeout IN NUMBER,
313 p_batch_size IN NUMBER DEFAULT 1000,
314 p_uom_class_enabled IN NUMBER DEFAULT SYS_NO,
315 p_item_cat_enabled IN NUMBER DEFAULT SYS_NO,
316 p_item_enabled IN NUMBER DEFAULT SYS_NO,
317 p_sup_cap_enabled IN NUMBER DEFAULT SYS_NO,
318 p_safety_stk_enabled IN NUMBER DEFAULT SYS_NO,
319 p_ship_mthd_enabled IN NUMBER DEFAULT SYS_NO,
320 p_sourcing_enabled IN NUMBER DEFAULT SYS_NO,
321 p_bom_enabled IN NUMBER DEFAULT SYS_NO,
322 p_rtg_enabled IN NUMBER DEFAULT SYS_NO,
323 p_resources_enabled IN NUMBER DEFAULT SYS_NO,
324 p_mat_sup_enabled IN NUMBER DEFAULT SYS_NO,
325 p_mat_dmd_enabled IN NUMBER DEFAULT SYS_NO,
326 p_reserve_enabled IN NUMBER DEFAULT SYS_NO,
327 p_res_dmd_enabled IN NUMBER DEFAULT SYS_NO,
328 p_item_cst_enabled IN NUMBER DEFAULT SYS_NO,
329 p_fiscal_cal_enabled IN NUMBER DEFAULT SYS_NO,
330 p_comp_cal_enabled IN NUMBER DEFAULT SYS_NO,
331 p_setup_enabled IN NUMBER DEFAULT SYS_NO,
332 p_item_rollup IN NUMBER DEFAULT SYS_YES,
333 p_level_value_enabled IN NUMBER DEFAULT SYS_NO,
334 p_level_assoc_enabled IN NUMBER DEFAULT SYS_NO,
335 p_booking_enabled IN NUMBER DEFAULT SYS_NO,
336 p_shipment_enabled IN NUMBER DEFAULT SYS_NO,
337 p_mfg_fct_enabled IN NUMBER DEFAULT SYS_NO,
338 p_list_price_enabled IN NUMBER DEFAULT SYS_NO,
339 p_cs_data_enabled IN NUMBER DEFAULT SYS_NO,
340 p_curr_conv_enabled IN NUMBER DEFAULT SYS_NO,
341 p_uom_conv_enabled IN NUMBER DEFAULT SYS_NO,
342 p_comp_users_enabled IN NUMBER DEFAULT SYS_NO,
343 p_item_substitute_enabled IN NUMBER DEFAULT SYS_NO,
344 p_cal_assignment_enabled IN NUMBER DEFAULT SYS_NO,
345 p_iro_enabled IN NUMBER DEFAULT SYS_NO,
346 p_ero_enabled IN NUMBER DEFAULT SYS_NO,
347 p_request_id IN NUMBER DEFAULT -1);
348
349
350 END MSC_CL_PRE_PROCESS;