[Home] [Help]
PACKAGE BODY: APPS.WIP_ATO_JOBS_PRIV
Source
1 PACKAGE BODY WIP_ATO_JOBS_PRIV AS
2 /* $Header: wipvfasb.pls 120.11.12020000.3 2013/01/10 10:56:47 abhissri ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : WIPVFASB.PLS |
9 | |
10 | DESCRIPTION : Package Boody for Autocreate FAS. |
11 | |
12 | Coders : Amit Garg |
13 | |
14 | PURPOSE: Create Discrete Jobs to satisfy sales order demand for |
15 | replenish-to-order items that meet the user-input criteria. |
16 | |
17 | |
18 | PROGRAM SYNOPSIS: |
19 | 1. Update records in mtl_demand that meet criteria with a group_id |
20 | 2. Insert records into wip_entities_interface for mtl_demands records |
21 | marked with group_id |
22 | 3. Read wip_entities_interface records and inform OE of sales order |
23 | lines that have been linked to WIP |
24 | 4. Call mass load routine to create jobs from wip_entities_interface |
25 | records |
26 | 5. Do feedback: |
27 | 1. Update mtl_demand for jobs successfully loaded |
28 | 2. Create records in wip_so_allocations |
29 | 3. Read wip_entities_interface and inform OE of sales order |
30 | lines that should be unlinked from WIP |
31 | 4. Update mtl_demand for jobs that failed load so they can be |
32 | picked up again |
33 | 6. Launch report of what occurred in process |
34 | 7. Delete records from interface table |
35 | |
36 | CALLED BY: Concurrent Program |
37 | |
38 |
39 | Date Fixed by FIX
40 | 06-Feb-2006 Kiran Konada bugfix#4865485
41 | When no orders are loaded AFAS will
42 | complete with warning
43 +===========================================================================*/
44
45 --Global Vars
46 G_PKG_NAME CONSTANT VARCHAR2(30):='WIP_ATO_JOBS_PRIV';
47
48 /*------------------------------------------------------------------+
49 | Local vars |
50 +-------------------------------------------------------------------*/
51 DEBUG_FLAG boolean := true;
52
53 /*------------------------------------------------------------------+
54 | Start WIP_AUTO_CREATE_JOBS |
55 +-------------------------------------------------------------------*/
56
57 /* Flow ER 14595064: Overloading this function CREATE_JOBS. Without retaining
58 this old signature, manual progress order of sales order line will fail
59 because for manual progress order, the call comes from the workflow. The
60 workflow doesn't have the new parameter and will have to be modified.
61 Overloading avoids modifications in the wft file. The workflow will call
62 the procedure without the new parameter whereas the program will call the
63 procedure with the new parameter.
64 */
65 PROCEDURE CREATE_JOBS(
66 ERRBUF OUT NOCOPY VARCHAR2 ,
67 RETCODE OUT NOCOPY VARCHAR2,
68 P_ORDER_NUMBER IN VARCHAR2 ,
69 P_DUMMY_FIELD IN VARCHAR2 ,
70 P_OFFSET_DAYS IN VARCHAR2 ,
71 P_LOAD_TYPE IN VARCHAR2 , --CHANGED
72 P_STATUS_TYPE IN VARCHAR2 , --CHANGED
73 P_ORG_ID IN VARCHAR2 , --CHANGED
74 P_CLASS_CODE IN VARCHAR2 , --CHANGED
75 P_FAILED_REQ_ID IN VARCHAR2 ,
76 P_ORDER_LINE_ID IN VARCHAR2 ,
77 P_BATCH_ID IN VARCHAR2)
78 IS
79
80 BEGIN
81 -- This procedure gets called for manual progress order. Since for these cases, the workflow
82 -- decides whether the line requires a flow schedule or a work order, we need to pass the value
83 -- of the parameter p_create_flow_schedules as 2 (No).
84
85 CREATE_JOBS(ERRBUF,
86 RETCODE,
87 P_ORDER_NUMBER,
88 P_DUMMY_FIELD,
89 P_OFFSET_DAYS,
90 P_LOAD_TYPE,
91 P_STATUS_TYPE,
92 P_ORG_ID,
93 P_CLASS_CODE,
94 P_FAILED_REQ_ID,
95 2,
96 P_ORDER_LINE_ID,
97 P_BATCH_ID);
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 fnd_file.put_line(which => fnd_file.log, buff => 'Failed in parent CREATE_JOBS.');
102 RETCODE := 1;
103
104 END CREATE_JOBS;
105
106 -- Overloaded CREATE_JOBS with the new parameter.
107 PROCEDURE CREATE_JOBS(
108 ERRBUF OUT NOCOPY VARCHAR2 ,
109 RETCODE OUT NOCOPY VARCHAR2 ,
110 P_ORDER_NUMBER IN VARCHAR2 ,
111 P_DUMMY_FIELD IN VARCHAR2 ,
112 P_OFFSET_DAYS IN VARCHAR2 ,
113 P_LOAD_TYPE IN VARCHAR2 ,
114 P_STATUS_TYPE IN VARCHAR2 ,
115 P_ORG_ID IN VARCHAR2 ,
116 P_CLASS_CODE IN VARCHAR2 ,
117 P_FAILED_REQ_ID IN VARCHAR2 ,
118 --Flow ER 14595064
119 P_CREATE_FLOW_SCHEDULES IN NUMBER,
120 P_ORDER_LINE_ID IN VARCHAR2 ,
121 P_BATCH_ID IN VARCHAR2
122 )
123
124
125 IS
126
127 x_return_status VARCHAR2(240);
128 P_API_VERSION NUMBER := 1.0;
129 L_ORDER_NUMBER NUMBER := -1;
130 L_DUMMY_FIELD NUMBER := -1;
131 L_OFFSET_DAYS NUMBER := -10000; -- Bug Fix 5169003. Off set days should defult to -10000 as -1 can be a valid value.
132 L_LOAD_TYPE NUMBER := -1;
133 L_STATUS_TYPE NUMBER := -1;
134 L_ORG_ID NUMBER := -1;
135 L_FAILED_REQ_ID NUMBER := -1;
136 L_ORDER_LINE_ID NUMBER := -1;
137 L_BATCH_ID NUMBER := -1;
138 --Flow ER 14595064
139 L_CREATE_FLOW_SCHEDULES NUMBER := P_CREATE_FLOW_SCHEDULES;
140
141 L_API_VERSION CONSTANT NUMBER := 1.0;
142 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_JOBS';
143
144 x_msg_count NUMBER;
145 x_msg_data VARCHAR2(1000);
146 P_INIT_MSG_LIST VARCHAR2(10) := FND_API.G_FALSE;
147 P_COMMIT VARCHAR2(10) := FND_API.G_FALSE;
148
149 l_all_records_success NUMBER := -1;
150 batch_mode_flag boolean := false;
151
152 log_file VARCHAR2(255);
153 output_file VARCHAR2(255);
154
155 --variables for WIP_logger
156 l_params wip_logger.param_tbl_t;
157 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
158 l_returnStatus VARCHAR2(1);
159 l_audsid NUMBER;
160
161 BEGIN
162 -- Standard Start of API savepoint
163 SAVEPOINT WIP_ATO_JOBS_PRIV;
164
165
166 -- Initialize message list if p_init_msg_list is set to TRUE.
167 IF FND_API.to_Boolean( p_init_msg_list ) THEN
168 FND_MSG_PUB.initialize;
169 END IF;
170
171 -- Initialize API return status to success
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 select userenv('SESSIONID') into l_audsid from dual;
175 -- fnd_file.log('AUDSID of current session: ' || l_audsid);
176
177 fnd_message.set_name('FND', 'CONC-PARAMETERS');
178 fnd_file.put_line(which => fnd_file.log, buff => 'AUDSID of current session: ' || l_audsid);
179
180 IF (fnd_profile.value('MRP_DEBUG') = 'Y') THEN
181 DEBUG_FLAG := TRUE;
182 ELSE
183 DEBUG_FLAG := FALSE;
184 END IF;
185
186 fnd_file.put_line(fnd_file.log, 'l_logLevel :'||l_logLevel ||'; wip_constants.trace_logging:'
187 ||wip_constants.trace_logging);
188 if (l_logLevel <= wip_constants.trace_logging) then
189 fnd_file.put_line(fnd_file.log, 'Logging......');
190 fnd_file.put_line(which => fnd_file.log, buff => 'Order Number: ' || P_ORDER_NUMBER);
191 fnd_file.put_line(which => fnd_file.log, buff => 'Offset Days: ' || P_OFFSET_DAYS);
192 fnd_file.put_line(which => fnd_file.log, buff => 'Organization: ' || P_ORG_ID);
193 fnd_file.put_line(which => fnd_file.log, buff => 'Load Type: ' || P_LOAD_TYPE);
194 fnd_file.put_line(which => fnd_file.log, buff => 'Class Code: ' || P_CLASS_CODE);
195 fnd_file.put_line(which => fnd_file.log, buff => 'Status Type: ' || P_STATUS_TYPE);
196 fnd_file.put_line(which => fnd_file.log, buff => 'Create Flow Schedules: ' || P_CREATE_FLOW_SCHEDULES);
197 fnd_file.put_line(which => fnd_file.log, buff => 'Order Line Number: ' || P_ORDER_LINE_ID);
198
199 l_params(1).paramName := 'P_ORDER_NUMBER ';
200 l_params(1).paramValue := P_ORDER_NUMBER ;
201 l_params(2).paramName := 'P_DUMMY_FIELD ';
202 l_params(2).paramValue := P_DUMMY_FIELD ;
203 l_params(3).paramName := 'P_OFFSET_DAYS ';
204 l_params(3).paramValue := P_OFFSET_DAYS ;
205 l_params(4).paramName := 'P_LOAD_TYPE ';
206 l_params(4).paramValue := P_LOAD_TYPE ;
207 l_params(5).paramName := 'P_STATUS_TYPE ';
208 l_params(5).paramValue := P_STATUS_TYPE ;
209 l_params(6).paramName := 'P_ORG_ID ';
210 l_params(6).paramValue := P_ORG_ID ;
211 l_params(7).paramName := 'P_CLASS_CODE ';
212 l_params(7).paramValue := P_CLASS_CODE ;
213 l_params(8).paramName := 'P_FAILED_REQ_ID';
214 l_params(8).paramValue := P_FAILED_REQ_ID;
215 l_params(9).paramName := 'P_ORDER_LINE_ID';
216 l_params(9).paramValue := P_ORDER_LINE_ID;
217 l_params(10).paramName := 'P_BATCH_ID ';
218 l_params(10).paramValue := P_BATCH_ID ;
219 l_params(11).paramName := 'P_API_VERSION ';
220 l_params(11).paramValue := P_API_VERSION ;
221
222 wip_logger.entryPoint(p_procName => 'WIP_ATO_JOBS_PRIV.CREATE_JOBS',
223 p_params => l_params,
224 x_returnStatus => x_return_status);
225
226 if(x_return_status <> fnd_api.g_ret_sts_success) then
227 raise fnd_api.g_exc_unexpected_error;
228 end if;
229 end if;
230
231
232 wip_logger.log('WIP Autocreate Final Assembly Orders [PL/SQL]-Start', l_returnStatus);
233 wip_logger.log( 'Value of fnd_file.log:'|| fnd_file.log, l_returnStatus);
234 wip_logger.log('Value of fnd_file.output:'|| fnd_file.output, l_returnStatus);
235 fnd_file.get_names(log_file, output_file);
236
237 wip_logger.log('Log File Name:'|| log_file|| '; Output file name:'|| output_file, l_returnStatus);
238 if (l_logLevel <= wip_constants.full_logging) then
242 wip_logger.log('P_LOAD_TYPE = '||P_LOAD_TYPE , l_returnStatus);
239 wip_logger.log('ORDER_NUMBER = '||P_ORDER_NUMBER , l_returnStatus);
240 wip_logger.log('DUMMY_FIELD = '||P_DUMMY_FIELD , l_returnStatus);
241 wip_logger.log('OFFSET_DAYS = '||P_OFFSET_DAYS , l_returnStatus);
243 wip_logger.log('P_STATUS_TYPE = '||P_STATUS_TYPE , l_returnStatus);
244 wip_logger.log('P_ORG_ID = '||P_ORG_ID , l_returnStatus);
245 wip_logger.log('P_CLASS_CODE = '||P_CLASS_CODE , l_returnStatus);
246 wip_logger.log('P_FAILED_REQ_ID = '||P_FAILED_REQ_ID , l_returnStatus);
247 wip_logger.log('ORDER_LINE_ID = '||P_ORDER_LINE_ID , l_returnStatus);
248 wip_logger.log('BATCH_ID = '||P_BATCH_ID , l_returnStatus);
249 wip_logger.log('P_API_VERSION = '||P_API_VERSION , l_returnStatus);
250 wip_logger.log('P_INIT_MSG_LIST = '||P_INIT_MSG_LIST , l_returnStatus);
251 wip_logger.log('P_COMMIT = '||P_COMMIT , l_returnStatus);
252
253 fnd_file.new_line(FND_FIlE.LOG,3); --put new line as separators
254 end if;
255
256 -- Standard call to check for call compatibility.
257 IF NOT FND_API.Compatible_API_Call ( l_api_version,
258 p_api_version,
259 l_api_name,
260 G_PKG_NAME)
261 THEN
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264
265
266 wip_logger.log('************* WIP Autocreate Final Assembly Orders *************', l_returnStatus);
267
268
269
270 /*---------------------------------------------------------------+
271 | VALIDATE parameter values. |
272 | Substitute -1 if no value found. |
273 +---------------------------------------------------------------+*/
274 if (P_ORDER_NUMBER is NOT NULL) THEN
275 L_ORDER_NUMBER := TO_NUMBER(P_ORDER_NUMBER);
276 end if;
277
278 if (P_DUMMY_FIELD is NOT NULL) THEN
279 L_DUMMY_FIELD := TO_NUMBER(P_DUMMY_FIELD);
280 end if;
281
282 if (P_OFFSET_DAYS is NOT NULL) THEN
283 L_OFFSET_DAYS := TO_NUMBER(P_OFFSET_DAYS);
284 end if;
285
286 if (P_LOAD_TYPE is NOT NULL) THEN
287 L_LOAD_TYPE := TO_NUMBER(P_LOAD_TYPE);
288 end if;
289
290 if (P_STATUS_TYPE is NOT NULL) THEN
291 L_STATUS_TYPE := TO_NUMBER(P_STATUS_TYPE);
292 end if;
293
294 if (P_ORG_ID is NOT NULL) THEN
295 L_ORG_ID := TO_NUMBER(P_ORG_ID);
296 end if;
297
298 if (P_FAILED_REQ_ID is NOT NULL) THEN
299 L_FAILED_REQ_ID := TO_NUMBER(P_FAILED_REQ_ID);
300 end if;
301
302 if (P_ORDER_LINE_ID is NOT NULL) THEN
303 L_ORDER_LINE_ID := TO_NUMBER(P_ORDER_LINE_ID);
304 end if;
305
306 if (P_BATCH_ID is NOT NULL) THEN
307 L_BATCH_ID := TO_NUMBER(P_BATCH_ID);
308 end if;
309
310 /* END Validating parameters*/
311
312 wip_logger.log('Parameters Validated.', l_returnStatus);
313
314 if (l_logLevel <= wip_constants.full_logging) then
315 if (L_BATCH_ID = -1) then
316 wip_logger.log( 'No Batch id.', l_returnStatus);
317 else
318 wip_logger.log( 'BATCH STR = '||L_BATCH_ID||'.', l_returnStatus);
319 end if;
320 end if;
321
322 if ( (L_BATCH_ID<>0) and (L_FAILED_REQ_ID = -1) ) then
323 /* NS change */
324 /* if there is no order # or order line id, AND there
325 is a batch_id, then use order_number=-25 as a flag
326 for batch mode. Note that what we really should do
327 is modify the paramptr structure to have a batch_id
328 field, but since we can't do that right now, this is
329 the temporary hack. */
330 l_all_records_success := -25; /* this is batch mode */
331 batch_mode_flag := TRUE;
332 L_failed_req_id := L_BATCH_ID;
333 end if;
334
335 wip_logger.log('Debug Profile Value:'|| fnd_profile.value('MRP_DEBUG') , l_returnStatus);
336
337 if (l_logLevel <= wip_constants.full_logging) then
338
339 /* NS add the fact that it is running in batch mode */
340 if (batch_mode_flag = true) then
341 wip_logger.log('Currently in Batch Mode. Failed_req_id contains the batch_id.', l_returnStatus);
342 end if;
343
344 wip_logger.log('After checks...........', l_returnStatus);
345 wip_logger.log('Dbg:org_id = '|| l_org_id , l_returnStatus);
346 wip_logger.log('Dbg:offset_days = '|| l_offset_days, l_returnStatus);
347
348 wip_logger.log('Dbg:load_type = ' || l_load_type, l_returnStatus);
349 wip_logger.log('Dbg:class_code = ' || P_class_code, l_returnStatus);
350 wip_logger.log('Dbg:status_type = '|| l_status_type, l_returnStatus);
351 wip_logger.log('Dbg:failed_req_id = '|| l_failed_req_id, l_returnStatus);
352 wip_logger.log('Dbg:order_number = ' || l_order_number, l_returnStatus);
353 wip_logger.log('Dbg:order_line_id = '|| l_order_line_id, l_returnStatus);
354 end if;
355
356 fnd_file.put_line(which => fnd_file.log, buff => 'After checks...........');
357 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:org_id = '|| l_org_id);
358 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:offset_days = '|| l_offset_days);
359 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:load_type = ' || l_load_type);
360 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:class_code = ' || P_class_code);
361 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:status_type = '|| l_status_type);
365 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:create_flow_schedules = '|| l_create_flow_schedules);
362 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:failed_req_id = '|| l_failed_req_id);
363 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:order_number = ' || l_order_number);
364 fnd_file.put_line(which => fnd_file.log, buff => 'Dbg:order_line_id = '|| l_order_line_id);
366
367
368 /*--------------------------------------------------------+
369 | Call LOAD_ORDERS, to load orders and do all the work. |
370 +--------------------------------------------------------+*/
371
372 if( LOAD_ORDERS
373 (
374 ERRBUF => ERRBUF,
375 RETCODE => x_return_status,
376 P_ORDER_NUMBER => L_ORDER_NUMBER,
377 p_DUMMY_FIELD => L_DUMMY_FIELD,
378 p_OFFSET_DAYS => L_OFFSET_DAYS,
379 p_LOAD_TYPE => L_LOAD_TYPE,
380 p_STATUS_TYPE => L_STATUS_TYPE,
381 p_ORG_ID => L_ORG_ID,
382 p_CLASS_CODE => P_CLASS_CODE,
383 p_FAILED_REQ_ID => L_FAILED_REQ_ID,
384 p_ORDER_LINE_ID => L_ORDER_LINE_ID,
385 p_BATCH_ID => L_BATCH_ID,
386 p_all_success_ptr => L_all_records_success,
387 --Flow ER 14595064
388 p_create_flow_schedules => l_create_flow_schedules
389 )
390 = false )
391 THEN
392 /*-------------------------------------+
393 | Program completion with problem. |
394 | Handle failure of program |
395 +------------------------------------+*/
396 if (l_logLevel <= wip_constants.full_logging) then
397 wip_logger.log(
398 'Dbg:Exiting Sales Order Loaded w/errors', l_returnStatus);
399 end if;
400 APP_EXCEPTION.RAISE_EXCEPTION;
401
402 else
403 if (l_all_records_success <> -1) then
404 /*--------------------------------------------------------+
405 | Program completion with no major problems |
406 +--------------------------------------------------------+*/
407 if (l_logLevel <= wip_constants.full_logging) then
408 wip_logger.log( 'Dbg:Exiting Sales Order Loaded w/success', l_returnStatus);
409 end if;
410 else
411 if (l_logLevel <= wip_constants.full_logging) then
412 wip_logger.log(
413 'Dbg:Exiting Sales Order Loaded w/warning', l_returnStatus);
414 end if;
415 --Put Warnings in Error Buffer
416 --Log Warnings
417
418 end if;
419 end if;
420
421 -- write to the log file
422 IF (l_logLevel <= wip_constants.trace_logging) THEN
423 wip_logger.exitPoint(p_procName => 'WIP_ATO_JOBS_PRIV.CREATE_JOBS',
424 p_procReturnStatus => x_return_Status,
425 p_msg => 'PROCEDURE COMPLETE.',
426 x_returnStatus => l_returnStatus);
427 END IF;
428
429 RETCODE := x_return_status;
430
431 EXCEPTION
432 WHEN FND_API.G_EXC_ERROR THEN
433 -- ROLLBACK TO SAVEPOINT WIP_ATO_JOBS_PRIV;
434 x_return_status := FND_API.G_RET_STS_ERROR ;
435 GOTO END_program;
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 -- ROLLBACK TO SAVEPOINT WIP_ATO_JOBS_PRIV;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
440 GOTO END_program;
441
442 WHEN OTHERS THEN
443 -- ROLLBACK TO SAVEPOINT WIP_ATO_JOBS_PRIV;
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
445 GOTO END_program;
446
447 <<END_program>>
448 RETCODE := 1;
449 wip_utilities.get_message_stack(p_msg =>ERRBUF);
450 IF(ERRBUF IS NULL) THEN
451 ERRBUF := 'No error message found in the stack';
452 END IF;
453
454 IF (l_logLevel <= wip_constants.trace_logging) THEN
455 wip_logger.log(ERRBUF, l_returnStatus);
456 wip_logger.exitPoint(p_procName =>'WIP_ATO_JOBS.CREATE_JOBS',
457 p_procReturnStatus => x_return_status,
458 p_msg => ERRBUF,
459 x_returnStatus => l_returnStatus);
460 END IF;
461 -- close log file
462 wip_logger.cleanUp(x_returnStatus => l_returnStatus);
463
464 END CREATE_JOBS;
465 /*------------------------------------------------------------------+
466 | End CREATE_JOBS |
467 +-------------------------------------------------------------------*/
468
469
470
471 PROCEDURE WAIT_CONC_PROGRAM(
472 p_request_id in number,
473 errbuf out NOCOPY varchar2,
474 retcode out NOCOPY number)
475 is
476 l_call_status boolean;
477 l_phase varchar2(80);
478 l_status varchar2(80);
479 l_dev_phase varchar2(80);
480 l_dev_status varchar2(80);
481 l_message varchar2(240);
482 l_counter number := 0;
483 BEGIN
484
485 LOOP
486 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
487 ( p_request_id,
488 10,
489 -1,
490 l_phase,
491 l_status,
492 l_dev_phase,
493 l_dev_status,
494 l_message);
495
496 exit when l_call_status=false;
497
498 if (l_dev_phase='COMPLETE') then
499 if (l_dev_status = 'NORMAL') then
500 retcode := -1;
501 elsif (l_dev_status = 'WARNING') then
502 retcode := 1;
506 errbuf := l_message;
503 else
504 retcode := 2;
505 end if;
507 return;
508 end if;
509
510 l_counter := l_counter + 1;
511 exit when l_counter >= 2;
512
513 end loop;
514
515 retcode := 2;
516 return ;
517 END WAIT_CONC_PROGRAM;
518
519
520
521
522
523 /*------------------------------------------------------------------+
524 | Start LOAD_ORDERS |
525 +-------------------------------------------------------------------*/
526 FUNCTION LOAD_ORDERS
527 (
528 ERRBUF OUT NOCOPY VARCHAR2,
529 RETCODE OUT NOCOPY VARCHAR2,
530 P_ORDER_NUMBER IN NUMBER DEFAULT -1,
531 p_DUMMY_FIELD IN NUMBER DEFAULT -1,
532 p_OFFSET_DAYS IN NUMBER DEFAULT -1,
533 p_LOAD_TYPE IN NUMBER DEFAULT -1,
534 p_STATUS_TYPE IN NUMBER DEFAULT -1,
535 p_ORG_ID IN NUMBER DEFAULT -1,
536 p_CLASS_CODE IN VARCHAR2 DEFAULT -1,
537 p_FAILED_REQ_ID IN NUMBER DEFAULT -1,
538 p_ORDER_LINE_ID IN NUMBER DEFAULT -1,
539 p_BATCH_ID IN NUMBER DEFAULT -1,
540 --Flow ER 14595064
541 p_create_flow_schedules IN NUMBER DEFAULT 2,
542 p_all_success_ptr IN OUT NOCOPY NUMBER )
543
544 RETURN boolean
545
546 IS
547
548
549 /*-------------------------------------------------+
550 | Local Variables |
551 +-------------------------------------------------+*/
552
553 precision_profile VARCHAR2(5) := NULL;
554
555 l_message_name varchar2(30);
556 l_message_text varchar2(150);
557 l_order_number NUMBER;
558 l_dummy_field NUMBER;
559 l_offset_days NUMBER;
560 l_load_type NUMBER;
561 l_status_type NUMBER;
562 l_org_id NUMBER;
563 l_class_code VARCHAR2(11);
564 l_failed_req_id NUMBER := P_FAILED_REQ_ID;
565 l_order_line_id NUMBER;
566 l_batch_id NUMBER;
567 --Flow ER 14595064
568 l_create_flow_schedules NUMBER;
569
570 /* OM Variables */
571 l_conc_request_id NUMBER;
572 l_appl_conc_program_id NUMBER;
573 l_program_id NUMBER;
574 l_conc_login_id NUMBER;
575 l_user_id NUMBER;
576 l_wip_group_id NUMBER;
577 l_orders_loaded NUMBER;
578 l_resp_id NUMBER;
579 l_resp_appl_id NUMBER;
580
581 l_status NUMBER;
582 errflg NUMBER := 0;
583 process_rows NUMBER := 0;
584
585 batch_id NUMBER;
586 batch_flag NUMBER := 0;
587 num_error_records NUMBER;
588 wjsi_group_id NUMBER;
589
590 report_status NUMBER := 0;
591
592 L_orders_in_interface number;
593 all_success_ptr NUMBER := p_all_success_ptr;
594 l_result boolean;
595
596 log_file VARCHAR2(255);
597 output_file VARCHAR2(255);
598 desname VARCHAR2(80);
599 destype VARCHAR2(10);
600 desformat VARCHAR2(10);
601
602 --variables for WIP_logger
603 l_params wip_logger.param_tbl_t;
604 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
605 l_returnStatus VARCHAR2(1);
606
607 report_retcode number;
608
609 --Bugfix 11818437
610 l_line_count number := 0;
611
612 BEGIN
613
614 retCode := fnd_api.g_ret_sts_success;
615
616 IF (DEBUG_FLAG) THEN
617 DEBUG_FLAG := TRUE;
618 ELSE
619 DEBUG_FLAG := FALSE;
620 END IF;
621
622
623 if (l_logLevel <= wip_constants.trace_logging) then
624 l_params(1).paramName := 'P_ORDER_NUMBER ';
625 l_params(1).paramValue := P_ORDER_NUMBER ;
626 l_params(2).paramName := 'P_DUMMY_FIELD ';
627 l_params(2).paramValue := P_DUMMY_FIELD ;
628 l_params(3).paramName := 'P_OFFSET_DAYS ';
629 l_params(3).paramValue := P_OFFSET_DAYS ;
630 l_params(4).paramName := 'P_LOAD_TYPE ';
631 l_params(4).paramValue := P_LOAD_TYPE ;
632 l_params(5).paramName := 'P_STATUS_TYPE ';
633 l_params(5).paramValue := P_STATUS_TYPE ;
634 l_params(6).paramName := 'P_ORG_ID ';
635 l_params(6).paramValue := P_ORG_ID ;
636 l_params(7).paramName := 'P_CLASS_CODE ';
637 l_params(7).paramValue := P_CLASS_CODE ;
638 l_params(8).paramName := 'P_FAILED_REQ_ID';
639 l_params(8).paramValue := P_FAILED_REQ_ID;
640 l_params(9).paramName := 'P_ORDER_LINE_ID';
641 l_params(9).paramValue := P_ORDER_LINE_ID;
642 l_params(10).paramName := 'P_BATCH_ID';
643 l_params(10).paramValue := P_BATCH_ID ;
644 l_params(11).paramName := 'p_all_success_ptr';
645 l_params(11).paramValue := p_all_success_ptr ;
646
647 wip_logger.entryPoint(p_procName => 'WIP_ATO_JOBS_PRIV.LOAD_ORDERS',
648 p_params => l_params,
649 x_returnStatus => l_returnStatus);
650
651 if(l_returnStatus <> fnd_api.g_ret_sts_success) then
652 raise fnd_api.g_exc_unexpected_error;
653 end if;
654 end if;
655
656 wip_logger.log('Inside Load_Orders.....', l_returnStatus);
657
658 --Flow ER 14595064
659 fnd_file.put_line(which => fnd_file.log, buff => 'Inside Load_Orders');
660 fnd_file.put_line(which => fnd_file.log, buff => 'Passing Parameters:');
664 fnd_file.put_line(which => fnd_file.log, buff => 'Load Type: ' || p_LOAD_TYPE);
661 fnd_file.put_line(which => fnd_file.log, buff => 'Order Number: ' || P_ORDER_NUMBER);
662 fnd_file.put_line(which => fnd_file.log, buff => 'Offset Days: ' || p_OFFSET_DAYS);
663 fnd_file.put_line(which => fnd_file.log, buff => 'Organization: ' || p_ORG_ID);
665 fnd_file.put_line(which => fnd_file.log, buff => 'Class Code: ' || p_CLASS_CODE);
666 fnd_file.put_line(which => fnd_file.log, buff => 'Status Type: ' || p_status_type);
667 fnd_file.put_line(which => fnd_file.log, buff => 'Create Flow Schedules: ' || p_create_flow_schedules);
668 fnd_file.put_line(which => fnd_file.log, buff => 'Order Line Number: ' || p_order_line_id);
669
670 /*---------------------------------------------------------------+
671 | Print out values in argument stucture if in debug mode |
672 +---------------------------------------------------------------+*/
673 if (l_logLevel <= wip_constants.full_logging) then
674 wip_logger.log('Dbg:org_id = '||p_org_id , l_returnStatus);
675 wip_logger.log('Dbg:offset_days = '||p_offset_days, l_returnStatus);
676
677 wip_logger.log('Dbg:load_type = '||p_load_type, l_returnStatus);
678 wip_logger.log('Dbg:class_code = '||p_class_code, l_returnStatus);
679 wip_logger.log('Dbg:status_type = '||p_status_type, l_returnStatus);
680 wip_logger.log('Dbg:failed_req_id = '||p_failed_req_id, l_returnStatus);
681 wip_logger.log('Dbg:order_number = '||P_ORDER_NUMBER, l_returnStatus);
682 wip_logger.log('Dbg:order_line_id = '||p_order_line_id, l_returnStatus);
683
684 wip_logger.log('Dbg:all_success_rec = '||p_all_success_ptr, l_returnStatus);
685
686 END IF;
687
688 if (p_all_success_ptr = -25) then
689 /* Now we are back to previous version code */
690 p_all_success_ptr := 1; /* i.e. TRUE */
691 batch_id := L_FAILED_REQ_ID;
692 L_FAILED_REQ_ID := -1;
693 wip_logger.log('Setting Batch Flag... All_Success==-25', l_returnStatus);
694 batch_flag:=1;
695 END IF;
696
697 /* As mentioned in CREATE_JOBS, we added a batch_id parameter
698 without being able to change the header file, so we used
699 p_all_success_ptr as a flag (val=-25) if we store a batch_id.
700 In this case, failed_req_id stored out batch_id value, so we
701 take the value out and reset failed_req_id to -1 */
702
703 /*===============================================================+
704 | If failed_request_id is not equal to -1, then this process |
705 | is being run to clean up data from a previously failed |
706 | run of this program. |
707 | |
708 | When cleaning, up skip the sections that marks mtl_demand |
709 | records that need to be loaded and inserts records into |
710 | wip_job_schedule_interface. Just perform the feedback loop. |
711 +===============================================================+*/
712 if (L_FAILED_REQ_ID = -1) then
713
714 if (l_logLevel <= wip_constants.full_logging) then
715 wip_logger.log('Dbg: OM Installed: Enter get_order_lines.', l_returnStatus);
716 END IF;
717
718 l_org_id := p_org_id;
719 l_offset_days := p_offset_days;
720 l_load_type := p_load_type;
721 L_CLASS_CODE := P_CLASS_CODE;
722 l_status_type := p_status_type;
723 l_order_number := p_order_number;
724 l_order_line_id := p_order_line_id;
725
726 l_conc_request_id := fnd_global.conc_request_id;
727 l_appl_conc_program_id := fnd_global.prog_appl_id;
728 l_conc_login_id := fnd_global.conc_login_id;
729 l_user_id := fnd_global.user_id;
730 l_program_id := fnd_global.conc_program_id;
731
732 --to later reset context to original values
733 l_resp_id := FND_GLOBAL.RESP_ID;
734 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
735
736 --Flow ER 14595064
737 l_create_flow_schedules := p_create_flow_schedules;
738
739 wip_logger.log('Before CTO, Fnd_Global values: user_id:' || fnd_global.user_id, l_returnStatus);
740
741 fnd_file.put_line(which => fnd_file.log, buff => 'Calling CTO_WIP_WRAPPER.get_order_lines');
742 --Flow ER 14595064
743 fnd_file.put_line(which => fnd_file.log, buff => 'Passing Parameters:');
744 fnd_file.put_line(which => fnd_file.log, buff => 'Order Number: ' || l_order_number);
745 fnd_file.put_line(which => fnd_file.log, buff => 'Offset Days: ' || l_offset_days);
746 fnd_file.put_line(which => fnd_file.log, buff => 'Organization: ' || l_org_id);
747 fnd_file.put_line(which => fnd_file.log, buff => 'Load Type: ' || l_load_type);
748 fnd_file.put_line(which => fnd_file.log, buff => 'Class Code: ' || l_class_code);
749 fnd_file.put_line(which => fnd_file.log, buff => 'Status Type: ' || l_status_type);
750 fnd_file.put_line(which => fnd_file.log, buff => 'Create Flow Schedules: ' || l_create_flow_schedules);
751 fnd_file.put_line(which => fnd_file.log, buff => 'Order Line Number: ' || l_order_line_id);
752
753
754 l_status := CTO_WIP_WRAPPER.get_order_lines(
755 l_org_id,
756 l_offset_days,
757 l_load_type,
758 l_class_code,
759 l_status_type,
760 l_order_number,
761 l_order_line_id,
762 l_conc_request_id,
763 l_program_id,
767 --Flow ER 14595064
764 l_conc_login_id,
765 l_user_id,
766 l_appl_conc_program_id,
768 l_create_flow_schedules,
769 l_orders_loaded,
770 l_wip_group_id,
771 l_message_name,
772 l_message_text);
773
774 fnd_file.put_line(which => fnd_file.log, buff => 'Return status from CTO_WIP_WRAPPER.get_order_lines:'|| l_status);
775 fnd_file.put_line(which => fnd_file.log, buff => 'Group_Id from CTO_WIP_WRAPPER.get_order_lines: '|| l_wip_group_id);
776
777 --p_wei_group_id := l_wip_group_id;
778 --p_orders_to_load := l_orders_loaded; /* order lines */
779 L_orders_in_interface := l_orders_loaded;
780
781 if (l_logLevel <= wip_constants.full_logging) then
782 wip_logger.log(
783 'Dbg: Exited get_order_lines with status '||l_status, l_returnStatus);
784 wip_logger.log(
785 'Dbg: wei_group_id = '||l_wip_group_id, l_returnStatus);
786 wip_logger.log(
787 'Dbg: Orders Loaded = '||l_orders_loaded, l_returnStatus);
788 END IF;
789
790 if (l_status <> 1) then
791 APP_EXCEPTION.RAISE_EXCEPTION;
792 else
793 if (L_orders_loaded = 0) then
794 --start bugfix 4865485
795 p_all_success_ptr := -1;
796 fnd_file.put_line(which => fnd_file.log, buff => 'L_orders_loaded=> '|| l_orders_loaded);
797 RETCODE := 1;--for warning
798 RETURN false;
799 --end bugfix 4865485
800 null; --goto done;
801 end if;
802 END IF;
803
804
805 /*----------------------------------------------------------+
806 | Call WIP mass interface routine to create discrete |
807 | jobs for records loaded into the interface table. |
808 | |
809 | Calling MASSLOAD PL/SQL Routine |
810 +---------------------------------------------------------+*/
811 fnd_file.put_line(which => fnd_file.log, buff => 'Calling wip_massload_pub.massLoadJobs with group Id: '|| l_wip_group_id);
812
813 wip_massload_pub.massLoadJobs(
814 p_groupID => l_wip_group_id,
815 p_validationLevel => 2,
816 p_commitFlag => 1, --commit in Massload
817 x_returnStatus => retCode,
818 x_errorMsg => errBuf);
819
820 fnd_file.put_line(which => fnd_file.log, buff => 'Returned from wip_massload_pub.massLoadJobs:');
821
822 IF (retCode <> fnd_api.g_ret_sts_success) THEN
823 fnd_file.put_line(which => fnd_file.log, buff => 'Failed in wip_massload_pub.massLoadJobs.');
824
825 if (l_logLevel <= wip_constants.full_logging) then
826 wip_logger.log( 'Dbg:Failed in wip_massload_pub.massLoadJobs', l_returnStatus);
827 END IF;
828
829 -- Bug 9314772.Should not raise exception here so that reservations
830 -- are created for jobs created with warnings.In case when the status
831 -- returned is error, the reservation api will not pick the record to
832 -- create reservation.pdube
833 -- APP_EXCEPTION.RAISE_EXCEPTION;
834 fnd_file.put_line(which => fnd_file.log, buff => 'Failed in wip_massload_pub.massLoadJobs returned with status retCode:'||retCode);
835
836 --Begin Bugfix 11818437
837 --Check if there are any records that got completed either successfully or in warning
838 begin
839 select 1
840 into l_line_count
841 from dual
842 where exists
843 ( select source_line_id
844 from wip_job_schedule_interface
845 where group_id = l_wip_group_id
846 and process_phase = WIP_CONSTANTS.ML_COMPLETE
847 and process_status in (WIP_CONSTANTS.COMPLETED,WIP_CONSTANTS.WARNING)
848 );
849 exception
850 when others then
851 l_line_count := 0;
852 end;
853
854 if l_line_count = 1 then
855 if DEBUG_FLAG then
856 fnd_file.put_line(which => fnd_file.log, buff => 'Some work orders got created. Setting the return code to Warning.');
857 end if;
858 retcode := 1;
859 elsif l_line_count = 0 then
860 --Nothing created a job
861 if DEBUG_FLAG then
862 fnd_file.put_line(which => fnd_file.log, buff => 'No work orders were created. Raising the exception.');
863 end if;
864 APP_EXCEPTION.RAISE_EXCEPTION;
865 end if;
866 --End Bugfix 11818437
867
868 END IF;
869
870 fnd_file.put_line(which => fnd_file.log, buff => 'Returned successfully from wip_massload_pub.massLoadJobs.');
871
872 END IF; /* end of logic skipped when failed_req_id <> -1 */
873
874 /*================================================================+
875 | Do feedback loop. This means: |
876 | - updating MTL_DEMAND for jobs that loaded |
877 | - put jobs on hold where OE changed the order during the |
878 | time THE order load process was running |
879 | - inserting records into WIP_SO_ALLOCATIONS |
880 | - informing OE to unlink any sales order whose job failed |
881 | to load |
882 | - delete records from the interface table |
883 | - setting the supply_group_id in MTL_DEMAND back to null |
884 | |
888 | 'cleanup mode' -- see comment earlier in program for details |
885 | Feedback is executed regardless of failed_request_id value. |
886 | |
887 | If failed_request_id <> 0, the program is being run in |
889 | on what happens in 'cleanup' mode, If this is the case, |
890 | the group_ids used for feedback must be retrieved from |
891 | MTL_DEMAND and WIP_JOB_SCHEDULE_INTERFACE. |
892 +===============================================================+*/
893 if (L_FAILED_REQ_ID <> -1) THEN
894
895 /* OM Installed - No Clean-up Mode Code Yet */
896 if (l_logLevel <= wip_constants.full_logging) then
897 wip_logger.log(
898 'Dbg: OM Installed: No Clean-Up Mode', l_returnStatus);
899 END IF;
900 END IF; /* End of Logic for Clean up Mode */
901
902
903 /* OM Installed */
904 if (l_logLevel <= wip_constants.full_logging) then
905 wip_logger.log( 'Dbg: OM Installed: Enter reserve_work_order.', l_returnStatus);
906 END IF;
907
908 l_status := CTO_WIP_WRAPPER.reserve_wo_to_so(l_wip_group_id,
909 l_message_name,
910 l_message_text);
911
912 if (l_logLevel <= wip_constants.full_logging) then
913 wip_logger.log('Dbg: Exited reserve_wo_to_so with status '||l_status, l_returnStatus);
914 END IF;
915
916 if (l_status <> 1) THEN
917 APP_EXCEPTION.RAISE_EXCEPTION;
918 END IF;
919
920
921 /*-----------------------------------------------------------+
922 | Commit at this point so the report will see the latest |
923 | data. |
924 +-----------------------------------------------------------+*/
925 COMMIT;
926
927 /*--------------------------------------------------------------------+
928 | Check if any records failed to process successfully. If so, pop |
929 | a warning message on the stack and set the all_success flag to |
930 | FALSE, causing the request to return a Warning. |
931 +-------------------------------------------------------------------+*/
932
933 wjsi_group_id := l_wip_group_id;
934
935 SELECT COUNT(*) INTO num_error_records
936 FROM wip_job_schedule_interface
937 WHERE GROUP_ID = wjsi_group_id
938 AND (PROCESS_STATUS <> WCOMPLETED
939 OR PROCESS_PHASE <> WIP_ML_COMPLETE);
940
941 if (num_error_records > 0) THEN
942 all_success_ptr := -1;
943 END IF;
944
945 if (l_logLevel <= wip_constants.full_logging) then
946 wip_logger.log(num_error_records||'records failed to process', l_returnStatus);
947 END IF;
948
949 FND_MESSAGE.SET_NAME('WIP','WIP_WARNING_REPORT');
950 FND_MSG_PUB.Add;
951
952
953 /*--------------------------------------------------------------------+
954 | Call report to write results of load process. Need to run |
955 | SQL*ReportWriter from this conc program so that we can have the |
956 | conc program re-submit itself. That way the user can have orders |
957 | loaded 1 time per week, etc without having to launch the program |
958 | all the time. If we tied conc program and SRW together via a |
959 | a report set they couldn't re-submit themselves as the parameters |
960 | would always be the same (and the report needs to use the group_id|
961 | used by the conc program). And we can't make the group_id a |
962 | defaulted param that is selected from the sequence as then the |
963 | conc program would always use the same group id value when |
964 | re-submitted. |
965 +--------------------------------------------------------------------+*/
966
967 precision_profile := fnd_profile.value('REPORT_QUANTITY_PRECISION');
968 if ( precision_profile = NULL ) then
969 precision_profile := 2;
970 END IF;
971
972 /*---------------------------------------------------+
973 | Run report. |
974 +---------------------------------------------------+*/
975 if (l_logLevel <= wip_constants.full_logging) then
976 wip_logger.log('P_group_id='||l_wip_group_id
977 ||'; P_qty_precision='||precision_profile, l_returnStatus);
978 END IF;
979
980 fnd_file.new_line(FND_FILE.LOG);
981 fnd_file.get_names(log_file, output_file);
982
983 wip_logger.log('Before Report Submit, Fnd_Global values: user_id:' || fnd_global.user_id, l_returnStatus);
984
985 --Since context has been changed in OE_Order_Context_GRP.Set_Created_By_Context
986 --Reset Context back to current
987 FND_GLOBAL.Apps_Initialize
988 (user_id => l_user_id
989 ,resp_id => l_resp_id
990 ,resp_appl_id => l_resp_appl_id);
991 wip_logger.log('Before Report, After context setup; Fnd_Global values: user_id:' || fnd_global.user_id, l_returnStatus);
992
993 fnd_file.put_line(which => fnd_file.log, buff => 'Calling WIPDJATO Report with group id:'|| l_wip_group_id||' and precision_profile:'||precision_profile);
994
995 report_status :=
996 FND_REQUEST.SUBMIT_REQUEST('WIP','WIPDJATO',
997 '',
998 '', false,
999 l_wip_group_id, precision_profile, '', '', '', '', '', '', '', '',
1000 '', '', '', '', '', '', '', '', '', '',
1001 '', '', '', '', '', '', '', '', '', '',
1002 '', '', '', '', '', '', '', '', '', '',
1003 '', '', '', '', '', '', '', '', '', '',
1004 '', '', '', '', '', '', '', '', '', '',
1008 '', '', '', '', '', '', '', '', '', '');
1005 '', '', '', '', '', '', '', '', '', '',
1006 '', '', '', '', '', '', '', '', '', '',
1007 '', '', '', '', '', '', '', '', '', '',
1009
1010 commit;
1011
1012 wip_logger.log('Conc Request no:'||report_status, l_returnStatus);
1013
1014 IF (report_status = 0) THEN
1015 report_retcode := 2;
1016 END IF;
1017
1018 /* if report didn't launch*/
1019 if(report_status = 0 ) then
1020
1021 wip_logger.log(errbuf, l_returnStatus);
1022
1023 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1024 wip_logger.log('==========================================', l_returnStatus);
1025 if (l_logLevel <= wip_constants.full_logging) then
1026 wip_logger.log('Could not execute report.', l_returnStatus);
1027 END IF;
1028 all_success_ptr := -1;
1029
1030 /* if report launched succesfully*/
1031 else
1032
1033 /* wait for report to finish */
1034 WAIT_CONC_PROGRAM(report_status,ERRBUF,report_retcode);
1035
1036 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assemble to Order Massload Report return code : '||report_retcode);
1037 /* report returns with error or waning */
1038 if (report_retcode <> -1 ) then
1039 FND_FILE.PUT_LINE(FND_FILE.LOG,'Report has errored or has a warning');
1040 errbuf := fnd_message.get;
1041 raise FND_API.G_EXC_ERROR ;
1042 else
1043 if (l_logLevel <= wip_constants.full_logging) then
1044 wip_logger.log('Report: Assemble To Order Mass Load Report ran successfully ', l_returnStatus);
1045 wip_logger.log('Check Output and Log file for Conc Request Number:'|| report_status, l_returnStatus);
1046 END IF;
1047 end if;
1048
1049 END IF;
1050
1051
1052 if ( delete_interface_orders(p_wei_group_id => l_wip_group_id) = false )
1053 THEN
1054 if (l_logLevel <= wip_constants.full_logging) then
1055 wip_logger.log(
1056 'Dbg:Failed in delete_interface_orders', l_returnStatus);
1057 END IF;
1058 APP_EXCEPTION.RAISE_EXCEPTION;
1059 END IF;
1060
1061 /*--------------------------------------------------------+
1062 | Program completion with no major problems |
1063 +--------------------------------------------------------+*/
1064 if (l_logLevel <= wip_constants.full_logging) then
1065 /* OM Installed */
1066 wip_logger.log(
1067 'Dbg:Exiting Filter Order Lines w/success', l_returnStatus);
1068 END IF;
1069
1070 if (L_FAILED_REQ_ID = -1) THEN
1071 if (l_orders_loaded = 0) THEN
1072 FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ORDERS_TO_LOAD');
1073 ERRBUF := FND_MESSAGE.GET;
1074 fnd_file.put_line(FND_FILE.OUTPUT,errbuf);
1075 END IF;
1076 if (l_orders_in_interface = 0) THEN
1077 FND_MESSAGE.SET_NAME ('WIP', 'WIP_NO_ORDERS_IN_INTERFACE');
1078 ERRBUF := FND_MESSAGE.GET;
1079 fnd_file.put_line(FND_FILE.OUTPUT,errbuf);
1080 END IF;
1081 END IF;
1082
1083
1084 -- write to the log file
1085 IF (l_logLevel <= wip_constants.trace_logging) THEN
1086 wip_logger.exitPoint(p_procName => 'WIP_ATO_JOBS_PRIV.LOAD_ORDERS',
1087 p_procReturnStatus => l_returnStatus,
1088 p_msg => 'PROCEDURE COMPLETE.',
1089 x_returnStatus => l_returnStatus);
1090 END IF;
1091 -- COMMIT;
1092 return(TRUE);
1093
1094
1095 /*-------------------------------------+
1096 | Program completion with probmlem. |
1097 | Handle failure of program |
1098 +------------------------------------+*/
1099 -- error:
1100 /* end LOAD_ORDERS */
1101
1102 EXCEPTION
1103
1104 WHEN OTHERS THEN
1105 if (l_logLevel <= wip_constants.full_logging) then
1106 wip_logger.log(
1107 'Dbg:Exiting Sales Order Loader w/errors', l_returnStatus);
1108 END IF;
1109 return FALSE;
1110
1111 END LOAD_ORDERS;
1112 /*------------------------------------------------------------------+
1113 | End LOAD_ORDERS |
1114 +-------------------------------------------------------------------*/
1115
1116
1117
1118
1119
1120 /*------------------------------------------------------------------+
1121 | Start DELTE_INTERFACE_ORDERS |
1122 +-------------------------------------------------------------------*/
1123 Function delete_interface_orders(p_wei_group_id NUMBER)
1124 return boolean
1125
1126 IS
1127
1128 wei_group_id NUMBER;
1129 n_undeleted_records NUMBER ;
1130
1131 --variables for WIP_logger
1132 l_params wip_logger.param_tbl_t;
1133 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1134 l_returnStatus VARCHAR2(1);
1135 x_return_status VARCHAR2(255);
1136
1137
1138 BEGIN
1139
1140 IF (DEBUG_FLAG) THEN
1141 DEBUG_FLAG := TRUE;
1142 ELSE
1143 DEBUG_FLAG := FALSE;
1144 END IF;
1145
1146
1147 if (l_logLevel <= wip_constants.trace_logging) then
1148 l_params(1).paramName := 'p_wei_group_id ';
1149 l_params(1).paramValue := p_wei_group_id ;
1150 wip_logger.entryPoint(p_procName => 'WIP_ATO_JOBS_PRIV.DELETE_INTERFACE_ORDERS',
1151 p_params => l_params,
1152 x_returnStatus => x_return_status);
1153
1154 if(x_return_status <> fnd_api.g_ret_sts_success) then
1155 raise fnd_api.g_exc_unexpected_error;
1156 end if;
1157 end if;
1161
1158
1159
1160 wei_group_id := p_wei_group_id;
1162 /*-------------------------------------------+
1163 | If in debug mode, print out parameters |
1164 +-------------------------------------------+*/
1165 if (l_logLevel <= wip_constants.full_logging) then
1166 wip_logger.log(
1167 'Dbg: In delete_interface_orders function', l_returnStatus);
1168 wip_logger.log( 'Dbg:wei_group_id = '||wei_group_id, l_returnStatus);
1169 END IF;
1170
1171 /*
1172 * Clean up the interface table and interface errors table.
1173 * When running in debug mode, we never delete from the errors table,
1174 * and we delete only those interface records which have been successfully
1175 * processed. When running in debug mode, we always delete all records
1176 * for the current group from both tables -- we rely on the WIPDJATO
1177 * report to have communicated any errors.
1178 *
1179 * This compromise between deleting everything and deleting only
1180 * non-problem records while in debug mode hopefully represents the
1181 * final fix for bug 775437 and its predecessors.
1182 */
1183
1184 --Bugfix 10636184: Controlling the delete by MRP profile instead of FND profile
1185 --if (l_logLevel <= wip_constants.full_logging) then
1186 IF (NOT DEBUG_FLAG) THEN
1187 --Bugfix 10636184: If MRP: Debug is OFF, then remove only successfully completed records
1188 --from the interface table.
1189
1190 /* bugfix 4289455 : Remove the records from WIE since we are deleting WJSI record
1191 with process phase and process status COMPLETE else these may remain orphan */
1192 DELETE FROM WIP_INTERFACE_ERRORS
1193 WHERE INTERFACE_ID IN (
1194 SELECT INTERFACE_ID
1195 FROM WIP_JOB_SCHEDULE_INTERFACE
1196 WHERE GROUP_ID = wei_group_id
1197 AND PROCESS_PHASE = WIP_ML_COMPLETE
1198 AND PROCESS_STATUS = WCOMPLETED);
1199
1200 fnd_file.put_line(which => fnd_file.log, buff => 'Deleted from wie:' || sql%rowcount);
1201
1202 DELETE FROM WIP_JOB_SCHEDULE_INTERFACE I
1203 WHERE I.GROUP_ID = wei_group_id
1204 AND I.PROCESS_PHASE = WIP_ML_COMPLETE
1205 AND I.PROCESS_STATUS = WCOMPLETED;
1206
1207 fnd_file.put_line(which => fnd_file.log, buff => 'Deleted from wjsi:' || sql%rowcount);
1208
1209 /* bugfix 4289455 : Remove the record from WJSI if process phase and
1210 process status is COMPLETE. If left as it is, we may encounter bug 2433627.
1211 AND 0 = (SELECT COUNT(*)
1212 FROM WIP_INTERFACE_ERRORS E
1213 WHERE E.INTERFACE_ID = I.INTERFACE_ID) ;
1214 */
1215
1216 else
1217 --Bugfix 10636184: If MRP: Debug is ON, do not remove any records from the interface table.
1218 fnd_file.put_line(which => fnd_file.log, buff => 'MRP debug is ON. Not deleting anything.');
1219 /*
1220 DELETE FROM WIP_INTERFACE_ERRORS
1221 WHERE INTERFACE_ID IN
1222 (SELECT INTERFACE_ID
1223 FROM WIP_JOB_SCHEDULE_INTERFACE
1224 WHERE GROUP_ID = wei_group_id);
1225
1226 DELETE FROM wip_job_schedule_interface wei
1227 WHERE group_id = wei_group_id;
1228 */
1229 null;
1230
1231 END IF;
1232
1233
1234 if (l_logLevel <= wip_constants.full_logging) then
1235 wip_logger.log('Dbg:'||SQL%ROWCOUNT||' records deleted from interface', l_returnStatus);
1236
1237 SELECT COUNT(*) INTO n_undeleted_records
1238 FROM WIP_JOB_SCHEDULE_INTERFACE WHERE GROUP_ID = wei_group_id ;
1239
1240 if(n_undeleted_records <> 0) THEN
1241 wip_logger.log('Dbg:Note: '||n_undeleted_records||' error/unprocessed records remain', l_returnStatus);
1242 wip_logger.log('Dbg:for WIP_JOB_SCHEDULE_INTERFACE.GROUP_ID='||wei_group_id||'.', l_returnStatus);
1243 wip_logger.log('Dbg:Join on INTERFACE_ID with WIP_INTERFACE_ERRORS', l_returnStatus);
1244 wip_logger.log('Dbg:for details.', l_returnStatus);
1245 END IF;
1246
1247 wip_logger.log(
1248 'Dbg:Success in delete_interface_orders', l_returnStatus);
1249 END IF;
1250
1251 -- write to the log file
1252 IF (l_logLevel <= wip_constants.trace_logging) THEN
1253 wip_logger.exitPoint(p_procName => 'WIP_ATO_JOBS_PRIV.DELETE_INTERFACE_ORDERS',
1254 p_procReturnStatus => l_returnStatus,
1255 p_msg => 'PROCEDURE COMPLETE.',
1256 x_returnStatus => l_returnStatus);
1257 END IF;
1258
1259 return(TRUE);
1260
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 if (l_logLevel <= wip_constants.full_logging) then
1265 wip_logger.log( 'Dbg:SQL error in delete_interface_orders', l_returnStatus);
1266 END IF;
1267 return(FALSE);
1268
1269 /* end of delete_interface_orders */
1270
1271 END delete_interface_orders;
1272
1273 /*------------------------------------------------------------------+
1274 | End DELETE_INTERFACE_ORDERS |
1275 +-------------------------------------------------------------------*/
1276
1277
1278
1279 END WIP_ATO_JOBS_PRIV;
1280