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