[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_ADJUSTMENT_PROC
Source
1 PACKAGE BODY jmf_shikyu_adjustment_proc AS
2 --$Header: JMFRSKDB.pls 120.26 2006/08/18 11:06:08 shu noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| FILENAME : JMFRSKDB.pls |
9 --| |
10 --| DESCRIPTION: Body file for the private package containing |
11 --| the logic of Component Consumption Adjustments. |
12 --| It includes the main procedures to be invoked |
13 --| by the Consumption Adjusments Concurrent Program. |
14 --| |
15 --| FUNCTION/PROCEDURE: adjustments_manager |
16 --| check_workers_status |
17 --| adjustments_worker |
18 --| adjust_consumption |
19 --| adjust_positive |
20 --| adjust_negative |
21 --| |
22 --| HISTORY: |
23 --| 28-MAY-2005 shu Created. |
24 --| 12-JUL-2005 vchu Fixed GSCC errors (File.Sql.46). |
25 --| Removed the init procedure. |
26 --| 22-JUL-2005 vchu The transaction_id column has been removed |
27 --| from the JMF_SHIKYU_ADJUSTMENTS table since |
28 --| it is redundant of the request_id column. |
29 --| All references to transaction_id have been |
30 --| replaced by request_id in this package. |
31 --| 03-OCT-2005 shu Added calls to JMF_SHIKYU_UTIL.debug_output. |
32 --| 05-OCT-2005 shu Added Open cursor statement before fetch. |
33 --| Replaced the condition cursor%FOUND with |
34 --| cursor%ROWCOUNT > 0. |
35 --| 06-OCT-2005 shu Added x_chr_errbuff,x_chr_retcode parameters |
36 --| to adjustments_worker. |
37 --| 11-OCT-2005 shu Added validation and error handle for |
38 --| adjustments_manager IN parameters |
39 --| 13-OCT-2005 shu added process for the batch_id,request_id |
40 --| 21-OCT-2005 shu fixed index Null Exception in |
41 --| adjustments_manager |
42 --| 14-Nov-2005 shu added debug info for exception handle |
43 --| in adjust_positive and adjust_negative |
44 --| 18-NOV-2005 shu added code for setting request completed with |
45 --| warning if SHIKYU profile is disable |
46 --| 21-NOV-2005 shu added code for setting request completed with |
47 --| warning if exception raised |
48 --| 12-DEC-2005 shu added check_workers_status procedure for |
49 --| checking the status of adjustment workers |
50 --| 12-DEC-2005 vchu Modified the queries in the adjust_negative |
51 --| procedure for getting the WIP consumed qty |
52 --| and total allocated qty for the |
53 --| Subcontracting Component being adjusted |
54 --| 16-JAN-2006 shu using FND_LOG.STRING for logging standard |
55 --| 27-JAN-2006 shu update the message according to seed data file|
56 --| 13-MAR-2006 the remove Commented code |
57 --| 13-MAR-2006 the added code for update the last_update_date |
58 --| column with sysdate. |
59 --| 17-MAR-2006 the fixed code to handle WIP transaction errors |
60 --| 22-MAR-2006 the added code for update the LAST_UPDATED_BY |
61 --| column and column LAST_UPDATE_LOGIN | |
62 --| 21-JUN-2006 nesoni added get_total_adjustments function for |
63 --| getting total adjustments corresponding to |
64 --| poShipmentId and ShikyuComponentId. |
65 --| 22-JUN-2006 the Fixed bug #5234426: keep adjustment records |
66 --| When adjust worker failed. |
67 --| 22-JUN-2006 the Fixed bug #5471813: Set warning message when |
68 --| not enough replenishment so. | |
69 --+===========================================================================+
70
71 --=============================================
72 -- CONSTANTS
73 --=============================================
74 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || g_pkg_name || '.';
75 G_DEFAULT_BATCH_SIZE CONSTANT NUMBER := 100;
76 G_DEFAULT_MAX_WORKERS CONSTANT NUMBER := 1;
77
78 --=============================================
79 -- GLOBAL VARIABLES
80 --=============================================
81 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
82
83 TYPE g_cons_adj_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
84
85 g_not_enough_replen_exc EXCEPTION;
86 g_wip_issued_less_alloc_exc EXCEPTION;
87 g_allocation_exc EXCEPTION;
88
89 --========================================================================
90 -- PROCEDURE : adjustments_manager PUBLIC
91 -- PARAMETERS: x_chr_errbuff varchar out parameter for current program
92 -- x_chr_retcode varchar out parameter for current program
93 -- p_batch_size Number of records in a batch
94 -- p_max_workers Maximum number of workers allowed
95 -- COMMENT : for submit adjustment concurrent manually , the group_id is ignored
96 -- PRE-COND :
97 -- EXCEPTIONS:
98 --========================================================================
99 PROCEDURE adjustments_manager
100 ( x_chr_errbuff OUT NOCOPY VARCHAR2 /*to store error msg*/ --errbuf??
101 , x_chr_retcode OUT NOCOPY VARCHAR2 /*to store return code*/ --retcode ??
102 , p_batch_size IN NUMBER
103 , p_max_workers IN NUMBER
104 )
105 IS
106 l_api_name CONSTANT VARCHAR2(30) := 'adjustments_manager';
107
108 l_p_batch_size NUMBER;
109 l_p_max_workers NUMBER;
110
111 CURSOR c_negative_adj IS
112 SELECT DISTINCT adjustment_id
113 FROM jmf_shikyu_adjustments --JMF_SUBCONTRACT_ORDERS
114 WHERE request_id IS NULL
115 AND batch_id IS NULL
116 AND adjustment < 0
117 --AND group_id = NVL(p_group_id,group_id) --group_id is for future use
118 ORDER BY adjustment_id;
119
120 CURSOR c_positive_adj IS
121 SELECT DISTINCT adjustment_id
122 FROM jmf_shikyu_adjustments --JMF_SUBCONTRACT_ORDERS
123 WHERE request_id IS NULL
124 AND batch_id IS NULL
125 AND adjustment > 0
126 --AND group_id = NVL(p_group_id,group_id) --group_id is for future use
127 ORDER BY adjustment_id;
128
129 l_cons_adj_id_tbl g_cons_adj_id_tbl_type;
130 l_cur_cons_adj_id_index NUMBER;
131
132 l_batch_request_id_tbl g_cons_adj_id_tbl_type;
133 l_cur_batch_id_index NUMBER;
134
135 l_cur_batch_min_adj_id NUMBER;
136 l_cur_batch_max_adj_id NUMBER;
137 l_counter NUMBER;
138
139 l_batch_id NUMBER;
140 l_workers jmf_shikyu_util.g_request_tbl_type;
141 l_adjust_rows NUMBER; -- the number of adjustments records with group_id = NVL(p_group_id,group_id)
142
143 l_request_id NUMBER; --the request id for the worker
144 l_return_status VARCHAR2(1); --FND_API.G_RET_STS_SUCCESS or other status
145
146 l_Manager_return_status VARCHAR2(30); --the status for adjustment manager.
147
148 --for checking SHIKYU enable profile.
149 l_jmf_shk_not_enabled VARCHAR2(240);
150 l_conc_succ BOOLEAN;
151
152 BEGIN
153
154 -- **** for debug information in readonly UT environment.--- begin ****
155 JMF_SHIKYU_RPT_UTIL.debug_output
156 (
157 p_output_to => 'FND_LOG.STRING'
158 ,p_api_name => G_MODULE_PREFIX || l_api_name || '.begin'
159 ,p_message => '========(p_batch_size: ' || p_batch_size ||
160 ' , p_max_workers: ' || p_max_workers || ' ) ========'
161 );
162 -- **** for debug information in readonly UT environment.--- end ****
163
164 --check if the SHIKYU enable profile is set to Yes. if no then return one error and stop.
165 IF (NVL(FND_PROFILE.VALUE('JMF_SHK_CHARGE_BASED_ENABLED'), 'N') = 'N')
166 THEN
167 FND_MESSAGE.SET_NAME('JMF', 'JMF_SHK_NOT_ENABLE');
168 l_jmf_shk_not_enabled := FND_MESSAGE.GET;
169
170 fnd_file.PUT_LINE(fnd_file.LOG, l_jmf_shk_not_enabled);
171
172 IF g_fnd_debug = 'Y' AND
173 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
174 THEN
175 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
176 ,g_module_prefix || l_api_name || '.warning'
177 ,l_jmf_shk_not_enabled);
178 END IF;
179
180 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
181 ,message => l_jmf_shk_not_enabled);
182 /*l_conc_succ := fnd_concurrent.set_completion_status(status => 'ERROR'
183 ,message => l_jmf_shk_not_enabled);*/
184 IF l_conc_succ
185 THEN
186 x_chr_errbuff := 'complete concurrent successfully';
187 x_chr_retcode := 'S';
188 ELSE
189 x_chr_errbuff := l_jmf_shk_not_enabled;
190 x_chr_retcode := 'W';
191 END IF;
192
193 RETURN;
194 END IF;
195
196 -- verify the input parameters
197 IF p_batch_size IS NULL OR p_batch_size <= 0 THEN
198 l_p_batch_size := G_DEFAULT_BATCH_SIZE;
199 ELSE
200 l_p_batch_size := p_batch_size;
201 END IF;
202 IF p_max_workers IS NULL OR p_max_workers <= 0 THEN
203 l_p_max_workers := G_DEFAULT_MAX_WORKERS;
204 ELSE
205 l_p_max_workers := p_max_workers;
206 END IF;
207 -- **** for debug information in readonly UT environment.--- begin ****
208 JMF_SHIKYU_UTIL.debug_output
209 (
210 p_output_to => 'FND_LOG.STRING'
211 ,p_api_name => G_MODULE_PREFIX || l_api_name
212 ,p_message => 'begin, l_p_batch_size:' || l_p_batch_size ||
213 ',l_p_max_workers:' || l_p_max_workers
214 );
215 -- **** for debug information in readonly UT environment.--- end ****
216
217 IF g_fnd_debug = 'Y' AND
218 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
219 THEN
220 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
221 ,g_module_prefix || l_api_name || '.begin'
222 ,NULL);
223 END IF;
224
225 -- get the rows that need to be adjusted
226 SELECT COUNT(adjustment_id)
227 INTO l_adjust_rows
228 FROM jmf_shikyu_adjustments
229 WHERE request_id IS NULL
230 AND batch_id IS NULL
231 AND adjustment <> 0
232 --AND group_id = NVL(p_group_id,group_id) --group_id is for future use
233 ;
234 IF l_adjust_rows = 0
235 THEN
236 --No adjustment row.
237 --fnd_message.set_name('JMF', 'JMF_SHIKYU_ADJ_MGR_NO_DATA');
238 fnd_message.set_name('JMF', 'JMF_RPT_NO_DATA');
239 fnd_msg_pub.add;
240 RETURN;
241 END IF;
242
243 -- Process the negative adjustment data
244 OPEN c_negative_adj;
245 FETCH c_negative_adj BULK COLLECT
246 INTO l_cons_adj_id_tbl;
247
248 --deleted EXIT WHEN c_negative_adj %NOTFOUND; because need to do following steps
249 --IF c_negative_adj %FOUND -- seems although c_negative_adj.ROWCOUNT >0 it is %NOTFOUND
250 IF c_negative_adj%ROWCOUNT > 0
251 THEN
252 -- begin of c_negative_adj %FOUND
253
254 l_cur_cons_adj_id_index := l_cons_adj_id_tbl.FIRST;
255 l_counter := 0;
256
257 LOOP
258
259 IF l_counter = 0
260 THEN
261 --begin of l_counter = 0
262 l_cur_batch_min_adj_id := l_cons_adj_id_tbl(l_cur_cons_adj_id_index);
263 -- Get the next batch_id using the sequence for Consumption Adjustment
264 SELECT jmf_shikyu_adj_batch_s.NEXTVAL
265 INTO l_batch_id
266 FROM dual;
267 END IF; --end of l_counter = 0
268
269 l_counter := l_counter + 1;
270 -- **** for debug information in readonly UT environment.--- begin ****
271 JMF_SHIKYU_RPT_UTIL.debug_output
272 (
273 p_output_to => 'FND_LOG.STRING'
274 ,p_api_name => G_MODULE_PREFIX || l_api_name
275 ,p_message => 'c_negative_adj%ROWCOUNT:' || c_negative_adj%ROWCOUNT ||
276 ' l_counter:' || l_counter || ',l_cur_cons_adj_id_index:' ||
277 l_cur_cons_adj_id_index
278 );
279 -- **** for debug information in readonly UT environment.--- end ****
280
281 IF (l_counter = l_p_batch_size OR l_cons_adj_id_tbl.NEXT(l_cur_cons_adj_id_index) IS NULL)
282 THEN
283
284 l_counter := 0;
285 l_cur_batch_max_adj_id := l_cons_adj_id_tbl(l_cur_cons_adj_id_index);
286
287 UPDATE jmf_shikyu_adjustments
288 SET batch_id = l_batch_id,
289 last_update_date = sysdate,
290 last_updated_by = FND_GLOBAL.user_id,
291 last_update_login = FND_GLOBAL.login_id
292 WHERE adjustment_id >= l_cur_batch_min_adj_id
293 AND adjustment_id <= l_cur_batch_max_adj_id
294 AND request_id IS NULL
295 AND batch_id IS NULL
296 AND adjustment < 0
297 --AND group_id = NVL(p_group_id,group_id) --for group--group_id is for future use
298 ;
299
300 -- Submit concurrent request for a Consumption Adjustments worker, which would
301 -- check if the count of workers has reached max_workers count; if it has, wait
302 -- until a worker finishes and then invoke the worker to process the batch
303
304 jmf_shikyu_util.submit_worker(p_batch_id => l_batch_id
305 ,p_request_count => l_p_max_workers
306 ,p_cp_short_name => 'JMFSKADW'
307 ,p_cp_product_code => 'JMF'
308 ,x_workers => l_workers
309 ,x_request_id => l_request_id
310 ,x_return_status => l_return_status);
311 --post actions after the worker request.
312 x_chr_retcode := l_return_status;
313 x_chr_errbuff := 'Submit_Worker negative request_id: ' || l_request_id || ', Return Status: ' || l_return_status;
314
315 -- **** for debug information in readonly UT environment.--- begin ****
316 JMF_SHIKYU_RPT_UTIL.debug_output
317 (
318 p_output_to => 'FND_LOG.STRING'
319 ,p_api_name => G_MODULE_PREFIX || l_api_name
320 ,p_message => 'c_negative_adj%ROWCOUNT:' || c_negative_adj%ROWCOUNT ||
321 'jmf_shikyu_util.submit_worker, l_return_status:' ||
322 l_return_status || ',l_request_id:' || l_request_id
323 );
324 -- **** for debug information in readonly UT environment.--- end ****
325
326
327 IF l_return_status = fnd_api.g_ret_sts_success
328 THEN
329 /*UPDATE jmf_shikyu_adjustments
330 SET request_id = l_request_id,
331 last_update_date = sysdate,
332 last_updated_by = FND_GLOBAL.user_id,
333 last_update_login = FND_GLOBAL.login_id
334 WHERE batch_id = l_batch_id
335 --AND request_id IS NULL
336 ;*/
337 l_batch_request_id_tbl(l_batch_id) := l_request_id;
338
339 -- **** for debug information in readonly UT environment.--- begin ****
340 JMF_SHIKYU_RPT_UTIL.debug_output
341 (
342 p_output_to => 'FND_LOG.STRING'
343 ,p_api_name => G_MODULE_PREFIX || l_api_name
344 ,p_message => 'Negative Adjustment: l_batch_id: ' || l_batch_id ||
345 ' ,l_batch_request_id_tbl.COUNT: ' || l_batch_request_id_tbl.COUNT ||
346 ' ,l_request_id: ' || l_request_id
347 );
348 -- **** for debug information in readonly UT environment.--- end ****
349 ELSE
350 UPDATE jmf_shikyu_adjustments
351 SET batch_id = NULL,
352 last_update_date = sysdate,
353 last_updated_by = FND_GLOBAL.user_id,
354 last_update_login = FND_GLOBAL.login_id
355 WHERE batch_id = l_batch_id
356 --AND request_id IS NULL
357 ;
358 END IF;
359
360 END IF; --end of (l_counter = l_p_batch_size OR l_cur_cons_adj_id_index IS NULL)
361
362 -- deleted l_cur_cons_adj_id_index := l_cons_adj_id_tbl.next(l_cur_cons_adj_id_index)
363 l_cur_cons_adj_id_index := l_cons_adj_id_tbl.NEXT(l_cur_cons_adj_id_index);
364 EXIT WHEN l_cur_cons_adj_id_index IS NULL;
365
366 END LOOP; --end of loop c_negative_adj
367
368 END IF; -- IF c_negative_adj%ROWCOUNT > 0; end of c_negative_adj %FOUND
369 CLOSE c_negative_adj;
370
371 --Wait for all the workers submitted for the negative adjustmemt data to complete;
372 jmf_shikyu_util.wait_for_all_workers(p_workers => l_workers);
373
374 -- Process the positive adjustment data
375 OPEN c_positive_adj;
376 FETCH c_positive_adj BULK COLLECT
377 INTO l_cons_adj_id_tbl;
378
379 --deleted EXIT WHEN c_positive_adj %NOTFOUND; because need to do following steps
380 --IF c_positive_adj %FOUND -- seems although c_positive_adj.ROWCOUNT >0 it is %NOTFOUND
381 IF c_positive_adj%ROWCOUNT > 0
382 THEN
383 -- begin of c_positive_adj %FOUND
384
385 l_cur_cons_adj_id_index := l_cons_adj_id_tbl.FIRST;
386 l_counter := 0;
387
388 LOOP
389
390 IF l_counter = 0
391 THEN
392 l_cur_batch_min_adj_id := l_cons_adj_id_tbl(l_cur_cons_adj_id_index);
393 -- Get the next batch_id using the sequence for Consumption Adjustment
394 SELECT jmf_shikyu_adj_batch_s.NEXTVAL
395 INTO l_batch_id
396 FROM dual;
397 END IF;
398
399 l_counter := l_counter + 1;
400 -- **** for debug information in readonly UT environment.--- begin ****
401 JMF_SHIKYU_RPT_UTIL.debug_output
402 (
403 p_output_to => 'FND_LOG.STRING'
404 ,p_api_name => G_MODULE_PREFIX || l_api_name
405 ,p_message => 'c_positive_adj%ROWCOUNT:' || c_positive_adj%ROWCOUNT ||
406 ' l_counter:' || l_counter || ',l_cur_cons_adj_id_index:' ||
407 l_cur_cons_adj_id_index
408 );
409 -- **** for debug information in readonly UT environment.--- end ****
410
411 IF (l_counter = l_p_batch_size OR l_cons_adj_id_tbl.NEXT(l_cur_cons_adj_id_index) IS NULL)
412 THEN
413 l_counter := 0;
414 l_cur_batch_max_adj_id := l_cons_adj_id_tbl(l_cur_cons_adj_id_index);
415
416 UPDATE jmf_shikyu_adjustments
417 SET batch_id = l_batch_id,
418 last_update_date = sysdate,
419 last_updated_by = FND_GLOBAL.user_id,
420 last_update_login = FND_GLOBAL.login_id
421 WHERE adjustment_id >= l_cur_batch_min_adj_id
422 AND adjustment_id <= l_cur_batch_max_adj_id
423 AND request_id IS NULL
424 AND batch_id IS NULL
425 AND adjustment > 0
426 --AND group_id = NVL(p_group_id,group_id) --for group --group_id is for future use
427 ;
428
429 -- Submit concurrent request for a Consumption Adjustments worker, which would
430 -- check if the count of workers has reached max_workers count; if it has, wait
431 -- until a worker finishes and then invoke the worker to process the batch
432
433 jmf_shikyu_util.submit_worker(p_batch_id => l_batch_id
434 ,p_request_count => l_p_max_workers
435 ,p_cp_short_name => 'JMFSKADW'
436 ,p_cp_product_code => 'JMF'
437 ,x_workers => l_workers
438 ,x_request_id => l_request_id
439 ,x_return_status => l_return_status);
440
441 --post actions after the worker request.
442 x_chr_retcode := l_return_status;
443 x_chr_errbuff := 'Submit_Worker positive request_id: ' || l_request_id || ', Return Status: ' || l_return_status;
444
445 -- **** for debug information in readonly UT environment.--- begin ****
446 JMF_SHIKYU_RPT_UTIL.debug_output
447 (
448 p_output_to => 'FND_LOG.STRING'
449 ,p_api_name => G_MODULE_PREFIX || l_api_name
450 ,p_message => 'c_positive_adj%ROWCOUNT:' || c_positive_adj%ROWCOUNT ||
451 'jmf_shikyu_util.submit_worker, l_return_status:' ||
452 l_return_status || ',l_request_id:' || l_request_id
453 );
454 -- **** for debug information in readonly UT environment.--- end ****
455
456 IF l_return_status = fnd_api.g_ret_sts_success
457 THEN
458 /*UPDATE jmf_shikyu_adjustments
459 SET request_id = l_request_id,
460 last_update_date = sysdate,
461 last_updated_by = FND_GLOBAL.user_id,
462 last_update_login = FND_GLOBAL.login_id
463 WHERE batch_id = l_batch_id*/
464 --AND request_id IS NULL
465 l_batch_request_id_tbl(l_batch_id) := l_request_id
466 ;
467 -- **** for debug information in readonly UT environment.--- begin ****
468 JMF_SHIKYU_RPT_UTIL.debug_output
469 (
470 p_output_to => 'FND_LOG.STRING'
471 ,p_api_name => G_MODULE_PREFIX || l_api_name
472 ,p_message => 'Positive Adjustment: l_batch_id: ' || l_batch_id ||
473 ' ,l_batch_request_id_tbl.COUNT: ' || l_batch_request_id_tbl.COUNT ||
474 ' ,l_request_id: ' || l_request_id
475 );
476 -- **** for debug information in readonly UT environment.--- end ****
477 ELSE
478 UPDATE jmf_shikyu_adjustments
479 SET batch_id = NULL,
480 last_update_date = sysdate,
481 last_updated_by = FND_GLOBAL.user_id,
482 last_update_login = FND_GLOBAL.login_id
483 WHERE batch_id = l_batch_id
484 --AND request_id IS NULL
485 ;
486 END IF;
487
488 END IF; --end of (l_counter = l_p_batch_size OR l_cur_cons_adj_id_index IS NULL)
489
490 l_cur_cons_adj_id_index := l_cons_adj_id_tbl.NEXT(l_cur_cons_adj_id_index);
491 EXIT WHEN l_cur_cons_adj_id_index IS NULL;
492
493 END LOOP;
494
495 END IF; -- IF c_positive_adj%ROWCOUNT > 0 ;end of c_positive_adj %FOUND
496
497 CLOSE c_positive_adj;
498
499 --20051212 add wait the submit workers, and check the workers' status to set the return manager status.
500 --Wait for all the workers submitted for the positive adjustmemt data to complete;
501 jmf_shikyu_util.wait_for_all_workers(p_workers => l_workers);
502
503 IF g_fnd_debug = 'Y' AND
504 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
505 THEN
506 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
507 ,g_module_prefix || l_api_name || '.end'
508 ,NULL);
509 END IF;
510
511 -- **** for debug information in readonly UT environment.--- begin ****
512 JMF_SHIKYU_RPT_UTIL.debug_output
513 (
514 p_output_to => 'FND_LOG.STRING'
515 ,p_api_name => G_MODULE_PREFIX || l_api_name
516 ,p_message => 'Update jmf_shikyu_adjustments'
517 );
518 JMF_SHIKYU_RPT_UTIL.debug_output
519 (
520 p_output_to => 'FND_LOG.STRING'
521 ,p_api_name => G_MODULE_PREFIX || l_api_name
522 ,p_message => 'l_batch_request_id_tbl.COUNT: ' || l_batch_request_id_tbl.COUNT
523 );
524 -- **** for debug information in readonly UT environment.--- end ****
525
526 l_cur_batch_id_index := l_batch_request_id_tbl.FIRST;
527
528 LOOP
529 EXIT WHEN l_cur_batch_id_index IS NULL;
530
531 -- **** for debug information in readonly UT environment.--- begin ****
532 JMF_SHIKYU_RPT_UTIL.debug_output
533 (
534 p_output_to => 'FND_LOG.STRING'
535 ,p_api_name => G_MODULE_PREFIX || l_api_name
536 ,p_message => 'l_cur_batch_id_index: ' || l_cur_batch_id_index ||
537 ' ,l_batch_request_id_tbl(' || l_cur_batch_id_index || ': ' || l_batch_request_id_tbl(l_cur_batch_id_index) ||
538 ' ,l_request_id: ' || l_request_id
539 );
540 -- **** for debug information in readonly UT environment.--- end ****
541
542 UPDATE jmf_shikyu_adjustments
543 SET request_id = l_batch_request_id_tbl(l_cur_batch_id_index),
544 last_update_date = sysdate,
545 last_updated_by = FND_GLOBAL.user_id,
546 last_update_login = FND_GLOBAL.login_id
547 WHERE batch_id = l_cur_batch_id_index
548 AND request_id IS NULL
549 ;
550 UPDATE jmf_shikyu_adjustments
551 SET request_id = NULL,
552 batch_id = NULL,
553 last_update_date = sysdate,
554 last_updated_by = FND_GLOBAL.user_id,
555 last_update_login = FND_GLOBAL.login_id
556 WHERE batch_id = l_cur_batch_id_index
557 AND request_id = -1
558 ;
559
560 l_cur_batch_id_index := l_batch_request_id_tbl.NEXT(l_cur_batch_id_index);
561
562 END LOOP; --end of loop c_negative_adj
563
564 --20051013 add to update the batch_id that without request rows to be process again.
565 UPDATE jmf_shikyu_adjustments
566 SET batch_id = NULL,
567 last_update_date = sysdate,
568 last_updated_by = FND_GLOBAL.user_id,
569 last_update_login = FND_GLOBAL.login_id
570 WHERE request_id IS NULL
571 AND batch_id IS NOT NULL;
572
573 -- commit the data
574 COMMIT;
575
576 --check the submit workers status, and then set print the worker status into logfile, set the manager status
577 --if one of the worker status FND_API.G_RET_STS_UNEXP_ERROR, then set Warning.
578 check_workers_status(p_workers => l_workers
579 ,x_return_status => l_Manager_return_status);
580
581 IF l_Manager_return_status <> 'NORMAL'
582 THEN
583 -- set the adjustment manager concurrent completed with Warning
584 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
585 ,message => 'Not all Workers complete with NORMAL');
586 END IF;
587
588 -- **** for debug information in readonly UT environment.--- begin ****
589 JMF_SHIKYU_RPT_UTIL.debug_output
590 (
591 p_output_to => 'FND_LOG.STRING'
592 ,p_api_name => G_MODULE_PREFIX || l_api_name || '.end'
593 ,p_message => 'Adjustment Manager End at'
594 || to_char(SYSDATE,'YYYY-MM-DD HH:MM:SS')
595 );
596 -- **** for debug information in readonly UT environment.--- end ****
597
598 EXCEPTION
599 WHEN no_data_found THEN
600 -- **** for debug information in readonly UT environment.--- begin ****
601 JMF_SHIKYU_RPT_UTIL.debug_output
602 (
603 p_output_to => 'FND_LOG.STRING'
604 ,p_api_name => G_MODULE_PREFIX || l_api_name
605 ,p_message => 'no_data_found.EXCEPTION:' || SQLERRM
606 );
607 -- **** for debug information in readonly UT environment.--- end ****
608 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
609 fnd_msg_pub.add;
610
611 -- rollback
612 ROLLBACK;
613
614 WHEN OTHERS THEN
615 -- **** for debug information in readonly UT environment.--- begin ****
616 JMF_SHIKYU_RPT_UTIL.debug_output
617 (
618 p_output_to => 'FND_LOG.STRING'
619 ,p_api_name => G_MODULE_PREFIX || l_api_name
620 ,p_message => ' WHEN OTHERS.EXCEPTION:' || SQLERRM
621 );
622 -- **** for debug information in readonly UT environment.--- end ****
623
624 --fnd_message.set_name('JMF', 'JMF_SHIKYU_ADJ_MGR_ERROR');
625 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
626 fnd_msg_pub.add;
627 RAISE fnd_api.g_exc_unexpected_error;
628
629 -- rollback
630 ROLLBACK;
631
632 END adjustments_manager;
633
634 --========================================================================
635 -- PROCEDURE : check_workers_status PUBLIC
636 -- PARAMETERS: p_workers Identifier of the submitted requests
637 -- x_return_status the status of worker request, if not 'NORMAL'
638 -- COMMENT :
639 -- PRE-COND :
640 -- EXCEPTIONS:
641 --========================================================================
642 PROCEDURE check_workers_status
643 (p_workers IN jmf_shikyu_util.g_request_tbl_type
644 ,x_return_status OUT NOCOPY VARCHAR2
645 )
646 IS
647 l_api_name CONSTANT VARCHAR2(30) := 'check_workers_status';
648
649 l_request_id fnd_concurrent_requests.request_id%TYPE;
650 l_parent_request_id fnd_concurrent_requests.parent_request_id%TYPE;
651
652 CURSOR l_cur_workers_request(
653 lp_parent_request_id fnd_concurrent_requests.parent_request_id%TYPE)
654 IS
655 SELECT request_id
656 FROM fnd_concurrent_requests
657 WHERE parent_request_id = lp_parent_request_id
658 ORDER BY request_id;
659
660 -- for FND_CONCURRENT.get_request_status
661 l_get_request_status BOOLEAN;
662 l_phase VARCHAR2(30);
663 l_status VARCHAR2(30);
664 l_dev_phase VARCHAR2(30);
665 l_dev_status VARCHAR2(30);
666 l_message VARCHAR2(240);
667
668 BEGIN
669 -- **** for debug information in readonly UT environment.--- begin ****
670 JMF_SHIKYU_RPT_UTIL.debug_output
671 (
672 p_output_to => 'FND_LOG.STRING'
673 ,p_api_name => G_MODULE_PREFIX || l_api_name
674 ,p_message => 'Begin'
675 );
676 -- **** for debug information in readonly UT environment.--- end ****
677
678 IF g_fnd_debug = 'Y' AND
679 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
680 THEN
681 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
682 ,g_module_prefix || l_api_name || '.begin'
683 ,NULL);
684 END IF;
685
686 -- check the request status
687 IF p_workers.COUNT >0 THEN
688 l_request_id := p_workers(1);
689
690 SELECT parent_request_id
691 INTO l_parent_request_id
692 FROM fnd_concurrent_requests
693 WHERE request_id = l_request_id;
694 -- **** for debug information in readonly UT environment.--- begin ****
695 JMF_SHIKYU_RPT_UTIL.debug_output
696 (
697 p_output_to => 'FND_LOG.STRING'
698 ,p_api_name => G_MODULE_PREFIX || l_api_name
699 ,p_message => '***-------------Consumption adjustment request:' ||
700 ' l_parent_request_id' || '--------------***.'
701 );
702 -- **** for debug information in readonly UT environment.--- end ****
703
704 -- **** for debug information in readonly UT environment.--- begin ****
705 JMF_SHIKYU_RPT_UTIL.debug_output
706 (
707 p_output_to => 'FND_LOG.STRING'
708 ,p_api_name => G_MODULE_PREFIX || l_api_name
709 ,p_message => '***-------------Begin of worker request information--------------***.'
710 );
711 -- **** for debug information in readonly UT environment.--- end ****
712
713 OPEN l_cur_workers_request(l_parent_request_id);
714 LOOP
715 --find the tp organizations
716 FETCH l_cur_workers_request
717 INTO l_request_id;
718
719 EXIT WHEN l_cur_workers_request%NOTFOUND; -- no more tp organiztions
720
721 l_get_request_status := FND_CONCURRENT.get_request_status(
722 request_id => l_request_id
723 ,appl_shortname => NULL --l_appl_shortname
724 ,program => NULL --l_program
725 ,phase => l_phase
726 ,status => l_status
727 ,dev_phase => l_dev_phase
728 ,dev_status => l_dev_status
729 ,message => l_message);
730
731 IF l_dev_status <> 'NORMAL'
732 THEN
733 x_return_status := l_dev_status;
734 END IF;
735 -- **** for debug information in readonly UT environment.--- begin ****
736 JMF_SHIKYU_RPT_UTIL.debug_output
737 (
738 p_output_to => 'FND_LOG.STRING'
739 ,p_api_name => G_MODULE_PREFIX || l_api_name
740 ,p_message => 'Worker status, request_id:' || l_request_id
741 || ',phase:' || l_phase
742 || ',status:' || l_status
743 || ',dev_phase:' || l_dev_phase
744 || ',dev_status:' || l_dev_status
745 || ',message:' || l_message
746 );
747 -- **** for debug information in readonly UT environment.--- end ****
748
749 END LOOP; --end loop of finding the tp organizations
750 CLOSE l_cur_workers_request;
751
752 -- **** for debug information in readonly UT environment.--- begin ****
753 JMF_SHIKYU_RPT_UTIL.debug_output
754 (
755 p_output_to => 'FND_LOG.STRING'
756 ,p_api_name => G_MODULE_PREFIX || l_api_name
757 ,p_message => '***-------------End of worker request information--------------***.'
758 );
759 -- **** for debug information in readonly UT environment.--- end ****
760
761 ELSE
762 -- **** for debug information in readonly UT environment.--- begin ****
763 JMF_SHIKYU_RPT_UTIL.debug_output
764 (
765 p_output_to => 'FND_LOG.STRING'
766 ,p_api_name => G_MODULE_PREFIX || l_api_name
767 ,p_message => 'No workers submit.'
768 );
769 -- **** for debug information in readonly UT environment.--- end ****
770 END IF;
771
772 IF g_fnd_debug = 'Y' AND
773 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
774 THEN
775 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
776 ,g_module_prefix || l_api_name || '.end'
777 ,NULL);
778 END IF;
779 -- **** for debug information in readonly UT environment.--- begin ****
780 JMF_SHIKYU_RPT_UTIL.debug_output
781 (
782 p_output_to => 'FND_LOG.STRING'
783 ,p_api_name => G_MODULE_PREFIX || l_api_name
784 ,p_message => 'End'
785 );
786 -- **** for debug information in readonly UT environment.--- end ****
787
788 END check_workers_status;
789
790 --========================================================================
791 -- PROCEDURE : adjustments_worker PUBLIC
792 -- PARAMETERS: x_chr_errbuff varchar out parameter for current program
793 -- x_chr_retcode varchar out parameter for current program
794 -- p_batch_id Identifier of the batch of rows to be processed
795 -- COMMENT :
796 -- PRE-COND :
797 -- EXCEPTIONS:
798 --========================================================================
799 PROCEDURE adjustments_worker
800 (
801 x_chr_errbuff OUT NOCOPY VARCHAR2 /*to store error msg*/ --errbuf??
802 ,x_chr_retcode OUT NOCOPY VARCHAR2 /*to store return code*/ --retcode ??
803 ,p_batch_id IN NUMBER
804 )
805 IS
806 l_return_status VARCHAR2(30);
807 l_api_name CONSTANT VARCHAR2(30) := 'adjustments_worker';
808
809 BEGIN
810 -- **** for debug information in readonly UT environment.--- begin ****
811 JMF_SHIKYU_RPT_UTIL.debug_output
812 (
813 p_output_to => 'FND_LOG.STRING'
814 ,p_api_name => G_MODULE_PREFIX || l_api_name
815 ,p_message => 'Begin, p_batch_id:' || p_batch_id
816 );
817 -- **** for debug information in readonly UT environment.--- end ****
818
819 IF g_fnd_debug = 'Y' AND
820 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
821 THEN
822 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
823 ,g_module_prefix || l_api_name || '.begin'
824 ,NULL);
825 END IF;
826
827 -- Invoke Adjust_Consumption which does the processing of the batch
828 adjust_consumption(p_batch_id => p_batch_id
829 ,x_return_status => l_return_status);
830
831 IF g_fnd_debug = 'Y' AND
832 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
833 THEN
834 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
835 ,g_module_prefix || l_api_name || '.end'
836 ,l_return_status);
837 END IF;
838 -- **** for debug information in readonly UT environment.--- begin ****
839 JMF_SHIKYU_RPT_UTIL.debug_output
840 (
841 p_output_to => 'FND_LOG.STRING'
842 ,p_api_name => G_MODULE_PREFIX || l_api_name
843 ,p_message => 'End, p_batch_id:' || p_batch_id
844 || ',x_chr_errbuff:' || x_chr_errbuff
845 || ',x_chr_retcode:' || x_chr_retcode
846 || ',l_return_status:' || l_return_status
847 );
848 -- **** for debug information in readonly UT environment.--- end ****
849
850 END adjustments_worker;
851
852 --========================================================================
853 -- PROCEDURE : adjust_consumption PUBLIC
854 -- PARAMETERS: p_batch_id Identifier of the batch of rows to be processed
855 -- : x_return_status return status
856 -- COMMENT : This is the main procedure to be kicked off by the Consumptioin Adjustments
857 -- Concurrent Program. It sorts the adjustment records in ascending order of
858 -- the adjustment amount and then processes each record by calling either the
859 -- Adjust_Positive or the Adjust_Negative procedure.
860 -- PRE-COND :
861 -- EXCEPTIONS:
862 --========================================================================
863 PROCEDURE adjust_consumption
864 ( p_batch_id IN NUMBER
865 , x_return_status OUT NOCOPY VARCHAR2
866 )
867 IS
868
869 l_api_name CONSTANT VARCHAR2(30) := 'adjust_consumption';
870
871 CURSOR c_adj IS
872 SELECT adjustment_id
873 ,subcontract_po_shipment_id
874 ,shikyu_component_id
875 ,adjustment
876 ,uom
877 FROM jmf_shikyu_adjustments
878 WHERE batch_id = p_batch_id
879 ORDER BY adjustment;
880
881 l_adjustment_id jmf_shikyu_adjustments.adjustment_id%TYPE;
882 l_subcontract_po_shipment_id jmf_shikyu_adjustments.subcontract_po_shipment_id%TYPE;
883 l_shikyu_component_id jmf_shikyu_adjustments.shikyu_component_id%TYPE;
884 l_adjustment jmf_shikyu_adjustments.adjustment%TYPE;
885 l_uom jmf_shikyu_adjustments.uom%TYPE;
886
887 BEGIN
888 -- **** for debug information in readonly UT environment.--- begin ****
889 JMF_SHIKYU_RPT_UTIL.debug_output
890 (
891 p_output_to => 'FND_LOG.STRING'
892 ,p_api_name => G_MODULE_PREFIX || l_api_name
893 ,p_message => 'Begin, p_batch_id:' || p_batch_id
894 );
895 -- **** for debug information in readonly UT environment.--- end ****
896
897 IF g_fnd_debug = 'Y' AND
898 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
899 THEN
900 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
901 ,g_module_prefix || l_api_name || '.begin'
902 ,NULL);
903 END IF;
904
905 OPEN c_adj;
906 LOOP
907
908 FETCH c_adj
909 INTO l_adjustment_id, l_subcontract_po_shipment_id, l_shikyu_component_id, l_adjustment, l_uom;
910
911 EXIT WHEN c_adj%NOTFOUND;
912
913 IF l_adjustment < 0
914 THEN
915 adjust_negative(p_subcontract_po_shipment_id => l_subcontract_po_shipment_id
916 ,p_component_id => l_shikyu_component_id
917 ,p_adjustment_amount => l_adjustment * -1
918 ,p_uom => l_uom
919 ,x_return_status => x_return_status);
920 ELSE
921 adjust_positive(p_subcontract_po_shipment_id => l_subcontract_po_shipment_id
922 ,p_component_id => l_shikyu_component_id
923 ,p_adjustment_amount => l_adjustment
924 ,p_uom => l_uom
925 ,x_return_status => x_return_status);
926 END IF; -- end of l_adjustment < 0
927
928 --need to do ******update the request_id column in jmf_shikyu_adjustment table if the adjustment is successful.
929 --set request_id = ????
930
931 -- **** for debug information in readonly UT environment.--- begin ****
932 JMF_SHIKYU_RPT_UTIL.debug_output
933 (
934 p_output_to => 'FND_LOG.STRING'
935 ,p_api_name => G_MODULE_PREFIX || l_api_name
936 ,p_message => 'x_return_status: ' || x_return_status
937 );
938 -- **** for debug information in readonly UT environment.--- end ****
939 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
940 THEN
941 -- **** for debug information in readonly UT environment.--- begin ****
942 JMF_SHIKYU_RPT_UTIL.debug_output
943 (
944 p_output_to => 'FND_LOG.STRING'
945 ,p_api_name => G_MODULE_PREFIX || l_api_name
946 ,p_message => 'l_adjustment_id: ' || l_adjustment_id
947 );
948 -- **** for debug information in readonly UT environment.--- end ****
949 UPDATE jmf_shikyu_adjustments
950 SET request_id = -1,
951 last_update_date = sysdate,
952 last_updated_by = FND_GLOBAL.user_id,
953 last_update_login = FND_GLOBAL.login_id
954 WHERE adjustment_id = l_adjustment_id;
955 END IF;
956
957 END LOOP;
958 CLOSE c_adj;
959
960 IF g_fnd_debug = 'Y' AND
961 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
962 THEN
963 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
964 ,g_module_prefix || l_api_name || '.end'
965 ,NULL);
966 END IF;
967 -- **** for debug information in readonly UT environment.--- begin ****
968 JMF_SHIKYU_RPT_UTIL.debug_output
969 (
970 p_output_to => 'FND_LOG.STRING'
971 ,p_api_name => G_MODULE_PREFIX || l_api_name
972 ,p_message => 'End, p_batch_id:' || p_batch_id
973 );
974 -- **** for debug information in readonly UT environment.--- end ****
975
976
977 EXCEPTION
978 WHEN no_data_found THEN
979 IF g_fnd_debug = 'Y' AND
980 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
981 THEN
982
983 --Set message name;
984 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
985 fnd_log.MESSAGE(LOG_LEVEL => FND_LOG.LEVEL_EXCEPTION
986 ,MODULE => g_module_prefix || l_api_name ||
987 '.no_data_found'
988 ,POP_MESSAGE => FALSE);
989 END IF;
990 --FND_MSG_PUB.add;
991 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
992 x_return_status := fnd_api.g_ret_sts_error;
993 -- **** for debug information in readonly UT environment.--- begin ****
994 JMF_SHIKYU_RPT_UTIL.debug_output
995 (
996 p_output_to => 'FND_LOG.STRING'
997 ,p_api_name => G_MODULE_PREFIX || l_api_name
998 ,p_message => 'no_data_found.EXCEPTION:' || SQLERRM
999 );
1000 -- **** for debug information in readonly UT environment.--- end ****
1001
1002 WHEN OTHERS THEN
1003 IF g_fnd_debug = 'Y' AND
1004 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1005 THEN
1006 --Set message name;
1007 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
1008 fnd_log.MESSAGE(LOG_LEVEL => FND_LOG.LEVEL_UNEXPECTED
1009 ,MODULE => g_module_prefix || l_api_name || '.others'
1010 ,POP_MESSAGE => FALSE);
1011 END IF;
1012 x_return_status := fnd_api.g_ret_sts_unexp_error;
1013 -- **** for debug information in readonly UT environment.--- begin ****
1014 JMF_SHIKYU_RPT_UTIL.debug_output
1015 (
1016 p_output_to => 'FND_LOG.STRING'
1017 ,p_api_name => G_MODULE_PREFIX || l_api_name
1018 ,p_message => 'WHEN OTHERS.EXCEPTION:' || SQLERRM
1019 );
1020 -- **** for debug information in readonly UT environment.--- end ****
1021
1022 END adjust_consumption;
1023
1024 --========================================================================
1025 -- PROCEDURE : adjust_positive PUBLIC
1026 -- PARAMETERS: p_subcontract_po_shipment_id Unique Identifier of the Subcontracting
1027 -- Order Shipment whose component consumption is to be adjusted.
1028 -- p_component_id p_component_id Identifier of the SHIKYU Component
1029 -- for which the consumption is to be adjusted.
1030 -- p_adjustment_amount Amount to adjust the component consumtion by.
1031 -- p_uom Unit of Measure of the adjustment amount.
1032 -- x_return_status return status.
1033 -- COMMENT : This procedure processes an adjustment record with a positive adjustment amount,
1034 -- meaning that the Manufacturing Partner has over-utilized the SHIKYU Component.
1035 -- PRE-COND :
1036 -- EXCEPTIONS:
1037 --========================================================================
1038 PROCEDURE adjust_positive
1039 ( p_subcontract_po_shipment_id IN NUMBER
1040 , p_component_id IN NUMBER
1041 , p_adjustment_amount IN NUMBER
1042 , p_uom IN VARCHAR2
1043 , x_return_status OUT NOCOPY VARCHAR2
1044 )
1045 IS
1046
1047 l_api_name CONSTANT VARCHAR2(30) := 'adjust_positive';
1048
1049 l_available_replen_so_qty_tbl jmf_shikyu_allocation_pvt.g_replen_so_qty_tbl_type;
1050 l_deallocated_rep_so_qty_tbl jmf_shikyu_allocation_pvt.g_allocation_qty_tbl_type;
1051 l_remaining_qty NUMBER;
1052
1053 l_msg_count NUMBER;
1054 l_msg_data VARCHAR2(240);
1055 l_qty_allocated NUMBER;
1056 l_actual_reduced_qty NUMBER;
1057
1058 l_component mtl_system_items_b_kfv.concatenated_segments%TYPE;
1059 l_po_num po_headers_all.segment1%TYPE;
1060 l_po_line_num po_lines_all.line_num%TYPE;
1061 l_shipment_num po_line_locations.SHIPMENT_NUM%TYPE;
1062 l_allocable_qty NUMBER;
1063
1064 --for message log.
1065 l_jmf_shk_exception VARCHAR2(240);
1066 l_conc_succ BOOLEAN;
1067
1068 BEGIN
1069
1070 -- **** for debug information in readonly UT environment.--- begin ****
1071 JMF_SHIKYU_RPT_UTIL.debug_output
1072 (
1073 p_output_to => 'FND_LOG.STRING'
1074 ,p_api_name => G_MODULE_PREFIX || l_api_name
1075 ,p_message => 'Begin, p_subcontract_po_shipment_id:' || p_subcontract_po_shipment_id
1076 || ',p_component_id:' || p_component_id
1077 || ',p_adjustment_amount:' || p_adjustment_amount
1078 || ',p_uom:' || p_uom
1079 );
1080 -- **** for debug information in readonly UT environment.--- end ****
1081 IF g_fnd_debug = 'Y' AND
1082 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1083 THEN
1084 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
1085 ,g_module_prefix || l_api_name || '.begin'
1086 ,NULL);
1087 END IF;
1088
1089 jmf_shikyu_allocation_pvt.get_available_replenishment_so(p_api_version => 1.0
1090 ,p_init_msg_list => NULL
1091 ,x_return_status => x_return_status
1092 ,x_msg_count => l_msg_count
1093 ,x_msg_data => l_msg_data
1094 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1095 ,p_component_id => p_component_id
1096 --, p_uom => p_uom
1097 ,p_qty => p_adjustment_amount
1098 ,p_include_additional_supply => 'Y'
1099 ,p_arrived_so_lines_only => 'Y' --to search for Replenishment SO Lines for allocations based on the condition that
1100 --the Replenishment SO Line's ship date + in-transit lead time (i.e. expected arrival time) <= SYSDATE.
1101 ,x_available_replen_tbl => l_available_replen_so_qty_tbl
1102 ,x_remaining_qty => l_remaining_qty);
1103 -- **** for debug information in readonly UT environment.--- begin ****
1104 JMF_SHIKYU_RPT_UTIL.debug_output
1105 (
1106 p_output_to => 'FND_LOG.STRING'
1107 ,p_api_name => G_MODULE_PREFIX || l_api_name
1108 ,p_message => 'end of jmf_shikyu_allocation_pvt.get_available_replenishment_so, x_return_status:' || x_return_status
1109 || ',x_remaining_qty:' || l_remaining_qty
1110 || ',x_msg_data:' || l_msg_data
1111 );
1112 -- **** for debug information in readonly UT environment.--- end ****
1113
1114 -- Raise an exception if there is not enough existing replenishments
1115 IF l_remaining_qty > 0
1116 THEN
1117 -- **** for debug information in readonly UT environment.--- begin ****
1118 JMF_SHIKYU_RPT_UTIL.debug_output
1119 (
1120 p_output_to => 'FND_LOG.STRING'
1121 ,p_api_name => G_MODULE_PREFIX || l_api_name
1122 ,p_message => 'RAISE g_not_enough_replen_exc:'
1123 || 'l_remaining_qty:' || l_remaining_qty
1124 );
1125 -- **** for debug information in readonly UT environment.--- end ****
1126 RAISE g_not_enough_replen_exc;
1127 -- Allocate if there is not enough existing replenishments
1128 ELSE
1129 jmf_shikyu_allocation_pvt.allocate_quantity(p_api_version => 1.0
1130 ,p_init_msg_list => NULL
1131 ,x_return_status => x_return_status
1132 ,x_msg_count => l_msg_count
1133 ,x_msg_data => l_msg_data
1134 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1135 ,p_component_id => p_component_id
1136 ,p_qty_to_allocate => p_adjustment_amount
1137 ,p_available_replen_tbl => l_available_replen_so_qty_tbl
1138 ,x_qty_allocated => l_qty_allocated);
1139 -- **** for debug information in readonly UT environment.--- begin ****
1140 JMF_SHIKYU_RPT_UTIL.debug_output
1141 (
1142 p_output_to => 'FND_LOG.STRING'
1143 ,p_api_name => G_MODULE_PREFIX || l_api_name
1144 ,p_message => 'end of jmf_shikyu_allocation_pvt.allocate_quantity, x_return_status:' || x_return_status
1145 || ',x_qty_allocated:' || l_qty_allocated
1146 || ',x_msg_data:' || l_msg_data
1147 );
1148 -- **** for debug information in readonly UT environment.--- end ****
1149
1150 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1151 THEN
1152 -- only when the allocated action is ok, the WIP will issue component, or the data will be in inconsistent
1153 jmf_shikyu_inv_pvt.process_wip_component_issue(p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1154 ,p_quantity => p_adjustment_amount --p_adjustment
1155 ,p_component_id => p_component_id
1156 ,p_uom => p_uom
1157 ,x_return_status => x_return_status);
1158 -- **** for debug information in readonly UT environment.--- begin ****
1159 JMF_SHIKYU_RPT_UTIL.debug_output
1160 (
1161 p_output_to => 'FND_LOG.STRING'
1162 ,p_api_name => G_MODULE_PREFIX || l_api_name
1163 ,p_message => 'end of jmf_shikyu_inv_pvt.process_wip_component_issue, x_return_status:' || x_return_status
1164 || ',p_subcontract_po_shipment_id:' || p_subcontract_po_shipment_id
1165 || ',p_component_id:' || p_component_id
1166 || ',p_quantity:' || p_adjustment_amount
1167 );
1168 -- **** for debug information in readonly UT environment.--- end ****
1169 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1170 THEN
1171 --first reduce the allocation
1172 jmf_shikyu_allocation_pvt.reduce_allocations(p_api_version => 1.0
1173 ,p_init_msg_list => NULL
1174 ,x_return_status => x_return_status
1175 ,x_msg_count => l_msg_count --'x_msg_count'
1176 ,x_msg_data => l_msg_data --x_msg_data
1177 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1178 ,p_component_id => p_component_id
1179 ,p_replen_so_line_id => NULL -- pass in NULL would deallocate in LIFO order of ship date and order
1180 ,p_qty_to_reduce => p_adjustment_amount
1181 ,x_actual_reduced_qty => l_actual_reduced_qty
1182 ,x_reduced_allocations_tbl => l_deallocated_rep_so_qty_tbl);
1183 -- **** for debug information in readonly UT environment.--- begin ****
1184 JMF_SHIKYU_RPT_UTIL.debug_output
1185 (
1186 p_output_to => 'FND_LOG.STRING'
1187 ,p_api_name => G_MODULE_PREFIX || l_api_name
1188 ,p_message => 'end of reduce the allocation, x_return_status:' || x_return_status
1189 || ',x_actual_reduced_qty:' || l_actual_reduced_qty
1190 || ',x_msg_data:' || l_msg_data
1191 );
1192 -- **** for debug information in readonly UT environment.--- end ****
1193 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1194 THEN
1195 -- **** for debug information in readonly UT environment.--- begin ****
1196 JMF_SHIKYU_RPT_UTIL.debug_output
1197 (
1198 p_output_to => 'FND_LOG.STRING'
1199 ,p_api_name => G_MODULE_PREFIX || l_api_name
1200 ,p_message => 'RAISE g_allocation_exc:'
1201 || 'reduce_allocations failed '
1202 || 'when the return status of '
1203 ||'process_wip_component_issue is not Success.'
1204 );
1205 -- **** for debug information in readonly UT environment.--- end ****
1206 -- raise exception
1207 RAISE g_allocation_exc;
1208 END IF; -- end of x_return_status <> FND_API.G_RET_STS_SUCCESS for reduce_allocations
1209 END IF; -- end of x_return_status <> FND_API.G_RET_STS_SUCCESS for process_wip_component_issue
1210 END IF; -- end of x_return_status = FND_API.G_RET_STS_SUCCESS for allocate_quantity
1211 END IF; -- end of l_remaining_qty > 0
1212
1213 IF g_fnd_debug = 'Y' AND
1214 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1215 THEN
1216 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
1217 ,g_module_prefix || l_api_name || '.end'
1218 ,NULL);
1219 END IF;
1220 -- **** for debug information in readonly UT environment.--- begin ****
1221 JMF_SHIKYU_RPT_UTIL.debug_output
1222 (
1223 p_output_to => 'FND_LOG.STRING'
1224 ,p_api_name => G_MODULE_PREFIX || l_api_name
1225 ,p_message => 'End, p_subcontract_po_shipment_id:' || p_subcontract_po_shipment_id
1226 || ',p_component_id:' || p_component_id
1227 || ',p_adjustment_amount:' || p_adjustment_amount
1228 || ',p_uom:' || p_uom
1229 );
1230 -- **** for debug information in readonly UT environment.--- end ****
1231
1232 EXCEPTION
1233 WHEN no_data_found THEN
1234
1235 IF g_fnd_debug = 'Y' AND
1236 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1237 THEN
1238
1239 --Set message name;
1240 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
1241 fnd_log.MESSAGE(LOG_LEVEL => FND_LOG.LEVEL_EXCEPTION
1242 ,MODULE => g_module_prefix || l_api_name ||
1243 '.no_data_found'
1244 ,POP_MESSAGE => FALSE);
1245 --Call FND_LOG.string;
1246 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
1247 ,g_module_prefix || l_api_name || '.no_data_found'
1248 ,'JMF_SHIKYU_ADJ_MGR_ERROR');
1249 END IF;
1250
1251 --fnd_message.set_name('JMF', 'JMF_SHIKYU_ADJ_MGR_ERROR');
1252 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
1253 FND_MSG_PUB.Add;
1254
1255 x_return_status := fnd_api.g_ret_sts_error;
1256 -- **** for debug information in readonly UT environment.--- begin ****
1257 JMF_SHIKYU_RPT_UTIL.debug_output
1258 (
1259 p_output_to => 'FND_LOG.STRING'
1260 ,p_api_name => G_MODULE_PREFIX || l_api_name
1261 ,p_message => 'no_data_found Exception:' || SQLERRM
1262 );
1263 -- **** for debug information in readonly UT environment.--- end ****
1264
1265 WHEN g_not_enough_replen_exc THEN
1266
1267 --Set message name;
1268 fnd_message.set_name('JMF', 'JMF_SHK_POS_ADJ_ERROR');
1269
1270 SELECT ph.segment1 po_number
1271 ,pl.line_num po_line_num
1272 ,pll.shipment_num po_shipment_num
1273 ,item_kfv.concatenated_segments Item_num
1274 INTO l_po_num
1275 ,l_po_line_num
1276 ,l_shipment_num
1277 ,l_component
1278 FROM po_headers_all ph
1279 ,po_lines_all pl
1280 ,po_line_locations_all pll
1281 ,mtl_system_items_b_kfv item_kfv
1282 ,jmf_shikyu_components jsc
1283 WHERE pl.po_line_id = pll.po_line_id
1284 AND ph.po_header_id = pll.po_header_id
1285 AND pll.ship_to_organization_id = item_kfv.organization_id
1286 AND jsc.shikyu_component_id = item_kfv.inventory_item_id
1287 AND jsc.subcontract_po_shipment_id = pll.line_location_id
1288 AND pll.line_location_id = p_subcontract_po_shipment_id
1289 AND item_kfv.inventory_item_id = p_component_id;
1290
1291 l_allocable_qty := p_adjustment_amount - l_remaining_qty;
1292
1293 FND_MESSAGE.SET_TOKEN('COMPONENT', l_component);
1294 FND_MESSAGE.SET_TOKEN('PONUM', l_po_num);
1295 FND_MESSAGE.SET_TOKEN('POLINENUM', l_po_line_num);
1296 FND_MESSAGE.SET_TOKEN('PO_SHIPMENTNUM', l_shipment_num);
1297 FND_MESSAGE.SET_TOKEN('ALOQTY', l_allocable_qty);
1298 FND_MESSAGE.SET_TOKEN('ADJQTY', p_adjustment_amount);
1299
1300 --FND_MSG_PUB.Add;
1301
1302 l_jmf_shk_exception := FND_MESSAGE.GET;
1303
1304 fnd_file.put_line(fnd_file.LOG
1305 ,l_jmf_shk_exception);
1306
1307 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1308 ,message => l_jmf_shk_exception);
1309
1310 -- **** for debug information in readonly UT environment.--- begin ****
1311 JMF_SHIKYU_RPT_UTIL.debug_output
1312 (
1313 p_output_to => 'FND_LOG.STRING'
1314 ,p_api_name => G_MODULE_PREFIX || l_api_name
1315 ,p_message => 'g_not_enough_replen_exc: message: ' || l_jmf_shk_exception
1316 );
1317 -- **** for debug information in readonly UT environment.--- end ****
1318
1319 x_return_status := fnd_api.g_ret_sts_error;
1320
1321 --Call FND_LOG.string;
1322 --Print the FND Error Message 'JMF_SHIKYU_POS_ADJ_ERROR' to the concurrent request log file;
1323 IF g_fnd_debug = 'Y' AND
1324 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1325 THEN
1326 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
1327 ,g_module_prefix || l_api_name || '.g_excep_level'
1328 ,'g_not_enough_replen_exc');
1329 END IF;
1330 -- **** for debug information in readonly UT environment.--- begin ****
1331 JMF_SHIKYU_RPT_UTIL.debug_output
1332 (
1333 p_output_to => 'FND_LOG.STRING'
1334 ,p_api_name => G_MODULE_PREFIX || l_api_name
1335 ,p_message => 'EXCEPTION:g_not_enough_replen_exc ,l_po_num' || l_po_num
1336 || ',l_po_line_num:' || l_po_line_num
1337 || ',l_allocable_qty:' || l_allocable_qty
1338 || ',p_adjustment_amount:' || p_adjustment_amount
1339 || '.SQLERRM:' || SQLERRM
1340 );
1341 -- **** for debug information in readonly UT environment.--- end ****
1342
1343 WHEN OTHERS THEN
1344 --Set message name;
1345 fnd_message.set_name('JMF', 'JMF_SHK_ADJ_MGR_ERROR');
1346 --Call FND_LOG.string;
1347 IF g_fnd_debug = 'Y' AND
1348 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1349 THEN
1350 fnd_log.STRING(FND_LOG.LEVEL_UNEXPECTED
1351 ,g_module_prefix || l_api_name ||
1352 '.JMF_SHIKYU_ADJ_MGR_ERROR'
1353 ,'JMF_SHIKYU_ADJ_MGR_ERROR');
1354 END IF;
1355 x_return_status := fnd_api.g_ret_sts_unexp_error;
1356 -- **** for debug information in readonly UT environment.--- begin ****
1357 JMF_SHIKYU_RPT_UTIL.debug_output
1358 (
1359 p_output_to => 'FND_LOG.STRING'
1360 ,p_api_name => G_MODULE_PREFIX || l_api_name
1361 ,p_message => 'WHEN OTHERS:' || SQLERRM
1362 );
1363 -- **** for debug information in readonly UT environment.--- end ****
1364
1365 END adjust_positive;
1366
1367 --========================================================================
1368 -- PROCEDURE : adjust_negative PUBLIC
1369 -- PARAMETERS: p_subcontract_po_shipment_id Unique Identifier of the Subcontracting
1370 -- Order Shipment whose component consumption is to be adjusted.
1371 -- p_component_id p_component_id Identifier of the SHIKYU Component
1372 -- for which the consumption is to be adjusted.
1373 -- p_adjustment_amount Amount to adjust the component consumtion by.
1374 -- p_uom Unit of Measure of the adjustment amount.
1375 -- x_return_status return status.
1376 -- COMMENT : This procedure processes an adjustment record with a negative adjustment amount,
1377 -- meaning that the Manufacturing Partner has under-utilized the SHIKYU Component.
1378 -- PRE-COND :
1379 -- EXCEPTIONS:
1380 --========================================================================
1381 PROCEDURE adjust_negative
1382 ( p_subcontract_po_shipment_id IN NUMBER
1383 , p_component_id IN NUMBER
1384 , p_adjustment_amount IN NUMBER
1385 , p_uom IN VARCHAR2
1386 , x_return_status OUT NOCOPY VARCHAR2
1387 )
1388 IS
1389
1390 l_api_name CONSTANT VARCHAR2(30) := 'adjust_negative';
1391
1392 l_deallocated_rep_so_qty_tbl jmf_shikyu_allocation_pvt.g_allocation_qty_tbl_type;
1393 l_available_replen_so_qty_tbl jmf_shikyu_allocation_pvt.g_replen_so_qty_tbl_type;
1394
1395 l_qty_allocated NUMBER;
1396 l_actual_reduced_qty NUMBER;
1397 l_wip_consumed_qty NUMBER;
1398 l_wip_consumed_uom VARCHAR2(3);
1399 l_total_allocated_qty NUMBER;
1400 l_total_allocated_uom VARCHAR2(3);
1401
1402 l_msg_count NUMBER;
1403 l_msg_data VARCHAR2(240);
1404
1405 --for message log.
1406 l_jmf_shk_exception VARCHAR2(240);
1407 l_conc_succ BOOLEAN;
1408 l_remaining_qty NUMBER;
1409
1410 BEGIN
1411
1412 -- **** for debug information in readonly UT environment.--- begin ****
1413 JMF_SHIKYU_RPT_UTIL.debug_output
1414 (
1415 p_output_to => 'FND_LOG.STRING'
1416 ,p_api_name => G_MODULE_PREFIX || l_api_name
1417 ,p_message => 'Begin, p_subcontract_po_shipment_id:' || p_subcontract_po_shipment_id
1418 || ',p_component_id:' || p_component_id
1419 || ',p_adjustment_amount:' || p_adjustment_amount
1420 || ',p_uom:' || p_uom
1421 );
1422 -- **** for debug information in readonly UT environment.--- end ****
1423 IF g_fnd_debug = 'Y' AND
1424 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1425 THEN
1426 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
1427 ,g_module_prefix || l_api_name || '.begin'
1428 ,NULL);
1429 END IF;
1430
1431 --get the WIP consumed qty
1432 SELECT wro.quantity_issued
1433 ,jsc.primary_uom
1434 INTO l_wip_consumed_qty
1435 ,l_wip_consumed_uom
1436 FROM wip_requirement_operations wro
1437 ,jmf_subcontract_orders jso
1438 ,jmf_shikyu_components jsc
1439 WHERE wro.wip_entity_id = jso.wip_entity_id
1440 AND wro.organization_id = jso.tp_organization_id
1441 AND wro.inventory_item_id = jsc.shikyu_component_id
1442 AND wro.repetitive_schedule_id IS NULL
1443 AND wro.operation_seq_num = 1
1444 AND jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1445 AND jsc.subcontract_po_shipment_id = p_subcontract_po_shipment_id
1446 AND jsc.shikyu_component_id = p_component_id;
1447
1448 --get the total allocated qty
1449 SELECT SUM(jsa.allocated_quantity) --this meam the jsa.allocated_quantity is under Primary UOM
1450 ,MAX(jsa.uom)
1451 INTO l_total_allocated_qty
1452 ,l_total_allocated_uom
1453 FROM jmf_shikyu_allocations jsa
1454 WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
1455 AND jsa.shikyu_component_id = p_component_id;
1456
1457 IF l_wip_consumed_qty > l_total_allocated_qty
1458 THEN
1459 -- **** for debug information in readonly UT environment.--- begin ****
1460 JMF_SHIKYU_RPT_UTIL.debug_output
1461 (
1462 p_output_to => 'FND_LOG.STRING'
1463 ,p_api_name => G_MODULE_PREFIX || l_api_name
1464 ,p_message => 'RAISE g_wip_issued_less_alloc_exc:'
1465 || 'l_wip_consumed_qty:' || l_wip_consumed_qty
1466 || '> l_total_allocated_qty:' || l_total_allocated_qty
1467 || 'in Primary UOM' || l_wip_consumed_uom
1468 );
1469 -- **** for debug information in readonly UT environment.--- end ****
1470
1471 -- raise exception
1472 RAISE g_wip_issued_less_alloc_exc;
1473 ELSE
1474 --first reduce the allocation
1475 jmf_shikyu_allocation_pvt.reduce_allocations(p_api_version => 1.0
1476 ,p_init_msg_list => NULL
1477 ,x_return_status => x_return_status
1478 ,x_msg_count => l_msg_count --'x_msg_count'
1479 ,x_msg_data => l_msg_data --x_msg_data
1480 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1481 ,p_component_id => p_component_id
1482 ,p_replen_so_line_id => NULL -- pass in NULL would deallocate in LIFO order of ship date and order => p_uom
1483 ,p_qty_to_reduce => p_adjustment_amount
1484 ,x_actual_reduced_qty => l_actual_reduced_qty
1485 ,x_reduced_allocations_tbl => l_deallocated_rep_so_qty_tbl);
1486 -- **** for debug information in readonly UT environment.--- begin ****
1487 JMF_SHIKYU_RPT_UTIL.debug_output
1488 (
1489 p_output_to => 'FND_LOG.STRING'
1490 ,p_api_name => G_MODULE_PREFIX || l_api_name
1491 ,p_message => 'end of reduce the allocation, x_return_status:' || x_return_status
1492 || ',x_actual_reduced_qty:' || l_actual_reduced_qty
1493 || ',x_msg_data:' || l_msg_data
1494 );
1495 -- **** for debug information in readonly UT environment.--- end ****
1496
1497
1498 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1499 THEN
1500 --return component for the WIP
1501 jmf_shikyu_inv_pvt.process_wip_component_return(p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1502 ,p_quantity => l_actual_reduced_qty --not p_adjustment_amount
1503 ,p_component_id => p_component_id
1504 ,p_uom => p_uom
1505 ,x_return_status => x_return_status);
1506 -- **** for debug information in readonly UT environment.--- begin ****
1507 JMF_SHIKYU_RPT_UTIL.debug_output
1508 (
1509 p_output_to => 'FND_LOG.STRING'
1510 ,p_api_name => G_MODULE_PREFIX || l_api_name
1511 ,p_message => 'end of component for the WIP, x_return_status:' || x_return_status
1512 );
1513 -- **** for debug information in readonly UT environment.--- end ****
1514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1515 THEN
1516 jmf_shikyu_allocation_pvt.get_available_replenishment_so(p_api_version => 1.0
1517 ,p_init_msg_list => NULL
1518 ,x_return_status => x_return_status
1519 ,x_msg_count => l_msg_count
1520 ,x_msg_data => l_msg_data
1521 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1522 ,p_component_id => p_component_id
1523 ,p_qty => p_adjustment_amount
1524 ,p_include_additional_supply => 'Y'
1525 ,p_arrived_so_lines_only => 'Y' --to search for Replenishment SO Lines for allocations based on the condition that
1526 --the Replenishment SO Line's ship date + in-transit lead time (i.e. expected arrival time) <= SYSDATE.
1527 ,x_available_replen_tbl => l_available_replen_so_qty_tbl
1528 ,x_remaining_qty => l_remaining_qty);
1529 -- **** for debug information in readonly UT environment.--- begin ****
1530 JMF_SHIKYU_RPT_UTIL.debug_output
1531 (
1532 p_output_to => 'FND_LOG.STRING'
1533 ,p_api_name => G_MODULE_PREFIX || l_api_name
1534 ,p_message => 'end of jmf_shikyu_allocation_pvt.get_available_replenishment_so, x_return_status:' || x_return_status
1535 || ',x_remaining_qty:' || l_remaining_qty
1536 || ',x_msg_data:' || l_msg_data
1537 );
1538 -- **** for debug information in readonly UT environment.--- end ****
1539
1540 -- Raise an exception if there is not enough existing replenishments
1541 IF l_remaining_qty > 0
1542 THEN
1543 -- **** for debug information in readonly UT environment.--- begin ****
1544 JMF_SHIKYU_RPT_UTIL.debug_output
1545 (
1546 p_output_to => 'FND_LOG.STRING'
1547 ,p_api_name => G_MODULE_PREFIX || l_api_name
1548 ,p_message => 'RAISE g_not_enough_replen_exc:'
1549 || 'l_remaining_qty:' || l_remaining_qty
1550 );
1551 -- **** for debug information in readonly UT environment.--- end ****
1552 RAISE g_not_enough_replen_exc;
1553 -- Allocate if there is not enough existing replenishments
1554 ELSE
1555 jmf_shikyu_allocation_pvt.allocate_quantity(p_api_version => 1.0
1556 ,p_init_msg_list => NULL
1557 ,x_return_status => x_return_status
1558 ,x_msg_count => l_msg_count
1559 ,x_msg_data => l_msg_data
1560 ,p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1561 ,p_component_id => p_component_id
1562 ,p_qty_to_allocate => p_adjustment_amount
1563 ,p_available_replen_tbl => l_available_replen_so_qty_tbl
1564 ,x_qty_allocated => l_qty_allocated);
1565 -- **** for debug information in readonly UT environment.--- begin ****
1566 JMF_SHIKYU_RPT_UTIL.debug_output
1567 (
1568 p_output_to => 'FND_LOG.STRING'
1569 ,p_api_name => G_MODULE_PREFIX || l_api_name
1570 ,p_message => 'end of jmf_shikyu_allocation_pvt.allocate_quantity, x_return_status:' || x_return_status
1571 || ',x_qty_allocated:' || l_qty_allocated
1572 || ',x_msg_data:' || l_msg_data
1573 );
1574 -- **** for debug information in readonly UT environment.--- end ****
1575 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1576 THEN
1577 -- **** for debug information in readonly UT environment.--- begin ****
1578 JMF_SHIKYU_RPT_UTIL.debug_output
1579 (
1580 p_output_to => 'FND_LOG.STRING'
1581 ,p_api_name => G_MODULE_PREFIX || l_api_name
1582 ,p_message => 'RAISE g_allocation_exc:'
1583 || 'allocate_quantity failed '
1584 || 'when the return status of '
1585 || 'process_wip_component_return is not Success.'
1586 );
1587 -- **** for debug information in readonly UT environment.--- end ****
1588 -- raise exception
1589 RAISE g_allocation_exc;
1590 END IF; -- end of x_return_status <> FND_API.G_RET_STS_SUCCESS for allocate_quantity
1591 END IF; -- end of l_remaining_qty > 0
1592 END IF; -- end of x_return_status <> FND_API.G_RET_STS_SUCCESS for process_wip_component_return
1593 END IF; -- end of x_return_status = FND_API.G_RET_STS_SUCCESS for reduce_allocations
1594 END IF; --end of l_WIP_consumed_qty > l_total_allocated_qty
1595
1596 IF g_fnd_debug = 'Y' AND
1597 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1598 THEN
1599 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
1600 ,g_module_prefix || l_api_name || '.end'
1601 ,NULL);
1602 END IF;
1603 -- **** for debug information in readonly UT environment.--- begin ****
1604 JMF_SHIKYU_RPT_UTIL.debug_output
1605 (
1606 p_output_to => 'FND_LOG.STRING'
1607 ,p_api_name => G_MODULE_PREFIX || l_api_name
1608 ,p_message => 'End, p_subcontract_po_shipment_id:' || p_subcontract_po_shipment_id
1609 || ',p_component_id:' || p_component_id
1610 || ',p_adjustment_amount:' || p_adjustment_amount
1611 || ',p_uom:' || p_uom
1612 );
1613 -- **** for debug information in readonly UT environment.--- end ****
1614
1615 EXCEPTION
1616 WHEN no_data_found THEN
1617 --Call FND_LOG.string;
1618 IF g_fnd_debug = 'Y' AND
1619 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1620 THEN
1621 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
1622 ,g_module_prefix || l_api_name || '.no_data_found'
1623 ,'NO_DATA_FOUND:' || SQLERRM);
1624 END IF;
1625 x_return_status := fnd_api.g_ret_sts_error;
1626 -- **** for debug information in readonly UT environment.--- begin ****
1627 JMF_SHIKYU_RPT_UTIL.debug_output
1628 (
1629 p_output_to => 'FND_LOG.STRING'
1630 ,p_api_name => G_MODULE_PREFIX || l_api_name
1631 ,p_message => 'NO_DATA.' || SQLERRM
1632 );
1633 -- **** for debug information in readonly UT environment.--- end ****
1634
1635 WHEN g_wip_issued_less_alloc_exc THEN
1636 /*--WIP consumed more than allocated qty, exception log
1637 FND_MESSAGE.SET_NAME('JMF', 'JMF_SHK_WIP_MORE');
1638 l_jmf_shk_exception := FND_MESSAGE.GET;
1639 */
1640 l_jmf_shk_exception := 'Exception: WIP job consumed qty(' || l_wip_consumed_qty ||
1641 ') is more than total allocated qty(' ||
1642 l_total_allocated_qty || '), UOM :'
1643 || l_wip_consumed_uom;
1644 --Call FND_LOG.string;
1645 IF g_fnd_debug = 'Y' AND
1646 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1647 THEN
1648 fnd_log.STRING(FND_LOG.LEVEL_EXCEPTION
1649 ,g_module_prefix || l_api_name ||
1650 '.g_wip_issued_less_alloc_exc'
1651 ,l_jmf_shk_exception);
1652 END IF;
1653
1654 x_return_status := fnd_api.g_ret_sts_error;
1655 -- **** for debug information in readonly UT environment.--- begin ****
1656 JMF_SHIKYU_RPT_UTIL.debug_output
1657 (
1658 p_output_to => 'FND_LOG.STRING'
1659 ,p_api_name => G_MODULE_PREFIX || l_api_name
1660 ,p_message => l_jmf_shk_exception || '.' || SQLERRM
1661 );
1662 -- **** for debug information in readonly UT environment.--- end ****
1663
1664 fnd_file.put_line(fnd_file.LOG
1665 ,l_jmf_shk_exception);
1666
1667 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
1668 ,message => l_jmf_shk_exception);
1669
1670 WHEN OTHERS THEN
1671 --Call FND_LOG.string;
1672 IF g_fnd_debug = 'Y' AND
1673 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1674
1675 THEN
1676 fnd_log.STRING(FND_LOG.LEVEL_UNEXPECTED
1677 ,g_module_prefix || l_api_name || '.unexp_error'
1678 ,'unexpected error' || SQLERRM);
1679 END IF;
1680 x_return_status := fnd_api.g_ret_sts_unexp_error;
1681 -- **** for debug information in readonly UT environment.--- begin ****
1682 JMF_SHIKYU_RPT_UTIL.debug_output
1683 (
1684 p_output_to => 'FND_LOG.STRING'
1685 ,p_api_name => G_MODULE_PREFIX || l_api_name
1686 ,p_message => 'WHEN OTHERS: ' || SQLERRM
1687 );
1688 -- **** for debug information in readonly UT environment.--- end ****
1689
1690 END adjust_negative;
1691
1692 --========================================================================
1693 -- FUNCTION : get_total_adjustments PUBLIC ,
1694 -- PARAMETERS: p_po_shipment_id Subcontracting Purchase Order Shipment Id
1695 -- p_component_id component Id of OSA item
1696 -- COMMENT : Function for getting total adjustments corresponding to
1697 -- poShipmentId and ShikyuComponentId.
1698 -- RETURN : NUMBER Returns total adjusted value
1699 -- PRE-COND :
1700 -- EXCEPTIONS:
1701 --========================================================================
1702 FUNCTION get_total_adjustments
1703 ( p_po_shipment_id IN NUMBER
1704 , p_component_id IN NUMBER
1705 ) RETURN NUMBER IS
1706 l_adjustment_total NUMBER;
1707 BEGIN
1708 SELECT nvl(SUM(adjustment), 0)
1709 INTO l_adjustment_total
1710 FROM jmf_shikyu_adjustments
1711 WHERE subcontract_po_shipment_id = p_po_shipment_id
1712 AND shikyu_component_id = p_component_id
1713 AND request_id IS NOT NULL;
1714
1715 RETURN l_adjustment_total;
1716 EXCEPTION
1717 WHEN no_data_found THEN
1718 RETURN 0;
1719 END;
1720
1721
1722 END jmf_shikyu_adjustment_proc;