[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_RECONCILIAITON_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_RECONCILIAITON_PVT AS
2 -- $Header: JMFVSKRB.pls 120.18.12020000.3 2012/07/26 09:13:38 abhissri ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| FILENAME |
9 --| JMFVSHRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used for SHIKYU Reconciliation purposes |
13 --| |
14 --| PROCEDURES: |
15 --| Process_SHIKYU_Reconciliation |
16 --| |
17 --| FUNCTIONS: |
18 --| |
19 --| HISTORY |
20 --| 05/23/2005 rajkrish Created |
21 --| 03/27/2006 vchu Fixed bug 5090721: Set last_update_date, |
22 --| last_updated_by and last_update_login in the |
23 --| update statements. |
24 --| 05/02/2006 vchu Added the p_skip_po_replen_creation parameter to |
25 --| the calls to Create_New_Allocations, due to a |
26 --| signature change made for fixing Bug 5197415. |
27 --+===========================================================================+
28
29 --=============================================================================
30 -- TYPE DECLARATIONS
31 --=============================================================================
32
33 --=============================================================================
34 -- CONSTANTS
35 --=============================================================================
36 G_PKG_NAME VARCHAR2(50) := 'JMF_SHIKYU_RECONCILIAITON_PVT' ;
37 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'SHIKYU.plsql.'||G_PKG_NAME || '.';
38
39 --=============================================================================
40 -- GLOBAL VARIABLES
41 --=============================================================================
42 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
43
44 --=============================================================================
45 -- PROCEDURES AND FUNCTIONS
46 --=============================================================================e
47
48 ------------------------------------------------------------------------
49 --- Process_quantity_Changes
50 -- Comments: This api will process the Suncontract Order quantity related
51 -- changes
52 -------------------------------------------------------------------------------
53 PROCEDURE Process_Quantity_Changes
54 ( p_SUBCONTRACT_PO_SHIPMENT_ID IN NUMBER
55 , p_SUBCONTRACT_PO_HEADER_ID IN NUMBER
56 , p_SUBCONTRACT_PO_LINE_ID IN NUMBER
57 , p_OLD_NEED_BY_DATE IN DATE
58 , p_UOM IN VARCHAR2
59 , p_CURRENCY IN VARCHAR2
60 , p_OEM_ORGANIZATION_ID IN NUMBER
61 , p_TP_ORGANIZATION_ID IN NUMBER
62 , p_WIP_ENTITY_ID IN NUMBER
63 , p_OSA_ITEM_ID IN NUMBER
64 , p_wip_start_quantity IN NUMBER
65 , p_new_need_by_date IN DATE
66 , p_new_ordered_quantity IN NUMBER
67 , p_old_ordered_quantity IN NUMBER
68 , p_puchasing_UOM IN VARCHAR2
69 ) IS
70
71
72 l_primary_quantity NUMBER;
73 l_return_status VARCHAR2(3);
74 l_component_new_quantity NUMBER ;
75 l_current_allocated_quantity NUMBER ;
76 l_decreased_qty NUMBER;
77 l_msg_count NUMBER ;
78 l_msg_data VARCHAR2(300) ;
79 l_allocation_date DATE;
80
81 CURSOR C_shikyu_components_CSR IS
82 SELECT SUBCONTRACT_PO_SHIPMENT_ID
83 , SHIKYU_COMPONENT_ID
84 , OEM_ORGANIZATION_ID
85 , SHIKYU_COMPONENT_PRICE
86 , PRIMARY_UOM
87 FROM JMF_SHIKYU_COMPONENTS
88 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
89 ;
90
91 l_reduced_allocations_tbl
92 JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;
93
94 BEGIN
95
96 -- The overall logic used in this api:
97 -- Select the subcontract records where the PO shipment order qty
98 -- has been changed from the last time reconcile or interlock pgm
99 -- IF the qty has been increased :
100 -- Invoke the process WIP api to increase the WIP job qty
101 -- 1 select the new increased component qty
102 -- 2 select the current allocated component qty
103 -- calculate the difference between 1 and 2
104 -- create new allocations by invoking the aloocations api
105
106 -- IF QTY has been decreased :
107 -- Invoke the process WIP api to decreased the WIP job qty
108 -- 1 select the new decreased component qty
109 -- 2 select the current allocated component qty
110 -- reduce allocations for the decreased qty
111
112 IF (g_fnd_debug = 'Y')
113 THEN
114 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
115 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
116 , G_MODULE_PREFIX ||
117 'Process_Quantity_Changes.Invoked'
118 , 'Entry');
119 END IF;
120 END IF;
121
122 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
124 , G_MODULE_PREFIX ||
125 'p_SUBCONTRACT_PO_SHIPMENT_ID => '
126 , p_SUBCONTRACT_PO_SHIPMENT_ID);
127 END IF;
128 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
129 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
130 , G_MODULE_PREFIX ||
131 'p_old_ordered_quantity => '
132 , p_old_ordered_quantity );
133 END IF;
134 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
136 , G_MODULE_PREFIX ||
137 'p_new_ordered_quantity => '
138 , p_new_ordered_quantity);
139 END IF;
140
141 -- Process WIP job
142 -- Create new alloacations
143 l_allocation_date := NULL;
144 l_allocation_date := JMF_SHIKYU_UTIL.GET_allocation_date
145 ( p_wip_entity_id => p_wip_entity_id );
146 --dbms_output.put_line(' l_allocation_date => '|| l_allocation_date );
147 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
148 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
149 , G_MODULE_PREFIX ||
150 'l_allocation_date => '
151 , l_allocation_date);
152 END IF;
153
154 FOR C_shikyu_components_rec IN C_shikyu_components_CSR
155 LOOP
156
157 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
159 , G_MODULE_PREFIX ||
160 'rajesh Component ID => '
161 , C_shikyu_components_rec.shikyu_component_id);
162 END IF;
163
164 l_component_new_quantity :=
165 JMF_SHIKYU_WIP_PVT.Get_component_quantity
166 ( p_organization_id => p_tp_ORGANIZATION_ID
167 , p_item_id =>
168 C_shikyu_components_rec.shikyu_component_id
169 , p_SUBCONTRACT_PO_SHIPMENT_ID => p_SUBCONTRACT_PO_SHIPMENT_ID );
170
171 --dbms_output.put_line(' l_component_new_quantity => '|| l_component_new_quantity);
172 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
174 , G_MODULE_PREFIX ||
175 'l_component_new_quantity => '
176 , l_component_new_quantity);
177 END IF;
178
179 l_current_allocated_quantity :=
180 NVL( JMF_SHIKYU_UTIL.GET_subcontract_allocated_qty
181 ( p_SUBCONTRACT_PO_SHIPMENT_ID => p_SUBCONTRACT_PO_SHIPMENT_ID
182 , p_COMPONENT_ID =>
183 C_shikyu_components_rec.shikyu_component_id ),0) ;
184
185 /*dbms_output.put_line(' l_current_allocated_quantity => '||
186 l_current_allocated_quantity );
187 dbms_output.put_line(' l_component_new_quantity => '||
188 l_component_new_quantity);*/
189 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
190 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
191 , G_MODULE_PREFIX ||
192 'l_current_allocated_quantity => '
193 , l_current_allocated_quantity);
194 END IF;
195
196
197 IF l_component_new_quantity > l_current_allocated_quantity
198 THEN
199 --dbms_output.put_line(' Cazll JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations');
200 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
202 , G_MODULE_PREFIX ||
203 'Calling JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations SCO '
204 , p_SUBCONTRACT_PO_SHIPMENT_ID );
205 END IF;
206
207 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations
208 ( p_api_version => 1.0
209 , p_init_msg_list => NULL
210 , x_return_status => l_return_status
211 , x_msg_count => l_msg_count
212 , x_msg_data => l_msg_data
213 , p_subcontract_po_shipment_id => p_SUBCONTRACT_PO_SHIPMENT_ID
214 , p_component_id =>
215 C_shikyu_components_rec.shikyu_component_id
216 , p_qty =>
217 l_component_new_quantity - l_current_allocated_quantity
218 -- p_need_by_date => l_allocation_date
219 , p_skip_po_replen_creation => 'N'
220 );
221
222 --dbms_output.put_line(' Out Create_New_Allocations l_return_status => '||
223 -- l_return_status );
224
225 ELSIF l_component_new_quantity < l_current_allocated_quantity
226 THEN
227 --dbms_output.put_line(' Call JMF_SHIKYU_ALLOCATION_PVT.Reduce_Allocations ');
228 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
229 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
230 , G_MODULE_PREFIX ||
231 'Calling JMF_SHIKYU_ALLOCATION_PVT.Reduce_Allocations '
232 , p_SUBCONTRACT_PO_SHIPMENT_ID );
233 END IF;
234
235 JMF_SHIKYU_ALLOCATION_PVT.Reduce_Allocations
236 ( p_api_version => 1.0
237 , p_init_msg_list => NULL
238 , x_return_status => l_return_status
239 , x_msg_count => l_msg_count
240 , x_msg_data => l_msg_data
241 , p_subcontract_po_shipment_id =>
242 p_SUBCONTRACT_PO_SHIPMENT_ID
243 , p_component_id =>
244 C_shikyu_components_rec.shikyu_component_id
245 , p_replen_so_line_id => NULL
246 , p_qty_to_reduce =>
247 l_current_allocated_quantity - l_component_new_quantity
248 , x_reduced_allocations_tbl => l_reduced_allocations_tbl
249 , x_actual_reduced_qty => l_decreased_qty
250 );
251
252 --dbms_output.put_line(' Out Reduce_Allocations l_return_status => '|| l_return_status);
253
254 l_component_new_quantity := NULL;
255 l_current_allocated_quantity := NULL;
256
257 END IF;
258 --dbms_output.put_line(' Next Loop 1 ');
259 END LOOP;
260
261 --dbms_output.put_line(' OUT OF Loop ');
262
263 IF (g_fnd_debug = 'Y')
264 THEN
265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
266 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
267 , G_MODULE_PREFIX ||
268 'Process_Quantity_Changes. OUT'
269 , 'Entry');
270 END IF;
271 END IF;
272
273 --dbms_output.put_line(' OUT Process_Quantity_Changes ');
274
275 END Process_Quantity_Changes ;
276
277
278 -----------------------------------------------------------
279 --
280 -----------------------------------------------------------
281 PROCEDURE update_replenishment_date
282 ( p_subcontract_po_shipment_id IN NUMBER
283 , p_oem_organization IN NUMBER
284 , p_tp_organization IN NUMBER
285 , p_replen_so_line_id IN NUMBER
286 , p_replen_so_header_id IN NUMBER
287 , p_component_id IN NUMBER
288 , p_new_ship_date IN DATE
289 , p_allocation_date IN DATE
290 ) IS
291
292
293 l_date DATE;
294 l_err_msg_name_tbl po_tbl_varchar30;
295 l_err_msg_text_tbl po_tbl_varchar2000;
296 x_pos_errors POS_ERR_TYPE;
297
298
299 -- OM variables --
300
301 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
302 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
303 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
304 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
305 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
306 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
307 l_hdr_rec OE_Order_PUB.Header_Rec_Type;
308 l_line_rec OE_Order_PUB.Line_Rec_Type;
309 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
310 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
311 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
312 l_x_header_rec OE_Order_PUB.Header_Rec_Type;
313 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
314 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
315 l_x_line_tbl OE_Order_PUB.Line_Tbl_Type;
316 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
317 l_x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
318 l_action_request_tbl OE_Order_PUB.request_tbl_type;
319 l_x_action_request_tbl OE_Order_PUB.request_tbl_type;
320 l_x_lot_serial_tbl OE_Order_PUB.lot_serial_tbl_type;
321 l_hdr_payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
322 l_line_payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
323 l_control_rec oe_globals.control_rec_type;
324 l_return_status Varchar2(30);
325 l_file_val Varchar2(30);
326 x_msg_count number;
327 x_msg_data Varchar2(2000);
328 x_msg_index number;
329
330 -- end OM variables --
331
332
333 --------- PO resch variables ------------------
334
335 l_return_number number ;
336 x_po_msg_count number;
337 x_po_msg_data Varchar2(2000);
338 x_po_msg_index number;
339 l_po_ship_date date;
340 l_api_errors PO_API_ERRORS_REC_TYPE ;
341 l_err_msg_name_tbl po_tbl_varchar30;
342 l_err_msg_text_tbl po_tbl_varchar2000;
343
344 l_repl_po_header_id NUMBER ;
345 l_repl_po_line_id NUMBER ;
346 l_repl_po_shipment_id NUMBER ;
347 --------- END PO variables
348
349 CURSOR C_select_replenishments_CSR
350 IS
351 SELECT REPLENISHMENT_SO_LINE_ID
352 ,REPLENISHMENT_SO_HEADER_ID
353 ,SCHEDULE_SHIP_DATE
354 ,REPLENISHMENT_PO_HEADER_ID
355 ,REPLENISHMENT_PO_LINE_ID
356 ,REPLENISHMENT_PO_SHIPMENT_ID
357 ,OEM_ORGANIZATION_ID
358 ,TP_ORGANIZATION_ID
359 ,SHIKYU_COMPONENT_ID
360 FROM jmf_shikyu_replenishments
361 WHERE REPLENISHMENT_SO_HEADER_ID = p_replen_so_header_id
362 AND REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id ;
363
364
365 CURSOR C_PO_details_CSR IS
366 SELECT poh.segment1
367 , poh.revision_num
368 , pol.po_line_id
369 , pol.line_num
370 , poll.line_location_id
371 , poll.need_by_date
372 , poll.SHIPMENT_NUM
373 FROM po_headers_all poh
374 , po_lines_all pol
375 , po_line_locations_all poll
376 WHERE poh.po_header_id = pol.po_header_id
377 AND pol.po_header_id = poll.po_header_id
378 AND pol.po_line_id = poll.po_line_id
379 AND poll.line_location_id = l_repl_po_shipment_id
380 AND poll.po_header_id = l_repl_po_header_id
381 AND poll.po_line_id = l_repl_po_line_id
382 AND poh.po_header_id = l_repl_po_header_id
383 AND pol.po_line_id = l_repl_po_line_id ;
384
385 BEGIN
386
387 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
389 , G_MODULE_PREFIX ||
390 'INTO update_replenishment_date FOR SCO PO SHIPID: '
391 , p_subcontract_po_shipment_id);
392 END IF;
393
394 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
395 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
396 , G_MODULE_PREFIX ||
397 'p_new_ship_date => '
398 , p_new_ship_date);
399 END IF;
400 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
402 , G_MODULE_PREFIX ||
403 'p_allocation_date => '
404 , p_allocation_date );
405 END IF;
406
407
408
409 FOR C_select_replenishments_REC IN C_select_replenishments_CSR
410 LOOP
411 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
412 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
413 , G_MODULE_PREFIX ||
414 'Start OM rescheduling : REPLENISHMENT_SO_LINE_ID '
415 , C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID);
416 END IF;
417
418
419 l_line_rec := OE_Order_PUB.G_MISS_LINE_REC;
420 l_line_rec.operation := oe_globals.G_OPR_UPDATE;
421
422 l_line_rec.line_id :=
423 C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID;
424 l_line_rec.header_id :=
425 C_select_replenishments_REC.REPLENISHMENT_SO_HEADER_ID ;
426 l_line_rec.schedule_ship_date :=
427 p_new_ship_date ;
428
429 l_line_tbl(1) := l_line_rec;
430
431 oe_debug_pub.add('Before Process_Order',1);
432 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
434 , G_MODULE_PREFIX ||
435 'OE_Order_PVT.Process_order re-scheduling '
436 , 'calling ');
437 END IF;
438
439
440 OE_Order_PVT.Process_order
441 ( p_api_version_number => 1.0
442 , p_init_msg_list => FND_API.G_TRUE
443 , x_return_status => l_return_status
444 , x_msg_count => x_msg_count
445 , x_msg_data => x_msg_data
446 , p_control_rec => l_control_rec
447 -- , p_validation_level => FND_API.G_VALID_LEVEL_NONE
448 , p_x_header_Rec => l_hdr_rec
449 , p_x_line_tbl => l_line_tbl
450 -- , p_line_adj_tbl => l_line_adj_tbl
451 , p_x_action_request_tbl => l_action_request_tbl
452 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
453 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
454 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
455 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
456 , p_x_Lot_Serial_tbl => l_x_lot_serial_tbl
457 ,p_x_Header_price_Att_tbl => l_Header_price_Att_tbl
458 ,p_x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
459 ,p_x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
460 ,p_x_Line_price_Att_tbl => l_Line_price_Att_tbl
461 ,p_x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
462 ,p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
463 , p_x_header_payment_tbl => l_hdr_payment_tbl
464 , p_x_line_payment_tbl => l_line_payment_tbl
465 );
466
467
468
469 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
470 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
471 , G_MODULE_PREFIX ||
472 'OE_Order_PVT.Process_order re-scheduling '
473 , 'out ');
474 END IF;
475
476 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
478 , G_MODULE_PREFIX ||
479 'l_return_status => '|| l_return_status
480 , l_return_status);
481 END IF;
482
483 ------ end OM -----------
484 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
485 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
486 , G_MODULE_PREFIX ||
487 'END OM rescheduling : REPLENISHMENT_SO_LINE_ID '
488 , C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID);
489 END IF;
490
491 --------------------------------------------------------
492 ------------------- PO RESCHEDULING --------------------
493
494 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
496 , G_MODULE_PREFIX ||
497 'rajesh START PO RESCHEDULING '
498 , C_select_replenishments_REC.REPLENISHMENT_PO_SHIPMENT_ID);
499 END IF;
500
501 l_repl_po_line_id := NULL ;
502 l_repl_po_header_id := NULL ;
503 l_repl_po_shipment_id := NULL ;
504
505 l_repl_po_line_id :=
506 C_select_replenishments_REC.replenishment_po_line_id ;
507 l_repl_po_header_id :=
508 C_select_replenishments_REC.replenishment_po_header_id ;
509 l_repl_po_shipment_id :=
510 C_select_replenishments_REC.replenishment_po_shipment_id ;
511
512 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
513 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
514 , G_MODULE_PREFIX ||
515 'l_repl_po_header_id => '
516 , l_repl_po_header_id);
517
518 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
519 , G_MODULE_PREFIX ||
520 'l_repl_po_line_id => '
521 , l_repl_po_line_id);
522
523 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
524 , G_MODULE_PREFIX ||
525 'l_repl_po_shipment_id => '
526 , l_repl_po_shipment_id);
527 END IF;
528
529 FOR C_PO_details_REC IN C_PO_details_CSR
530 LOOP
531 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
532 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
533 , G_MODULE_PREFIX ||
534 'rajesh Into PO Loop for segment1 => '
535 , C_PO_details_REC.segment1);
536
537 END IF;
538 BEGIN
539
540 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
541 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
542 , G_MODULE_PREFIX ||
543 'Calling PO_CHANGE_API1_S for : '
544 , C_PO_details_REC.segment1 );
545 END IF;
546
547 l_return_number := PO_CHANGE_API1_S.update_po
548 (
549 X_PO_NUMBER =>
550 C_PO_details_REC.segment1
551 , X_RELEASE_NUMBER =>
552 NULL
553 , X_REVISION_NUMBER =>
554 NVL(C_PO_details_REC.revision_num,0)
555 , X_LINE_NUMBER =>
556 NVL(C_PO_details_REC.line_num,1)
557 , X_SHIPMENT_NUMBER =>
558 NVL(C_PO_details_REC.shipment_num,1)
559 , NEW_QUANTITY => NULL
560 , NEW_PRICE => NULL
561 , NEW_PROMISED_DATE => NULL
562 , NEW_NEED_BY_DATE => p_allocation_date
563 , LAUNCH_APPROVALS_FLAG => 'Y'
564 , UPDATE_SOURCE => NULL
565 , VERSION => 1.0
566 , X_OVERRIDE_DATE => NULL
567 -- <PO_CHANGE_API FPJ START>
568 , X_API_ERRORS => l_api_errors
569 -- <PO_CHANGE_API FPJ END>
570 , p_BUYER_NAME => null
571 -- <INVCONV R12 START>
572 , p_secondary_quantity => null
573 , p_preferred_grade => null
574 -- <INVCONV R12 END>
575 ) ;
576
577 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
579 , G_MODULE_PREFIX ||
580 'After PO reschedule l_return_number => ' || l_return_number
581 , l_return_number);
582 END IF;
583 END ;
584
585
586 END LOOP ; --PO loop
587
588 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
590 , G_MODULE_PREFIX ||
591 'Out of PO loop '
592 , 'rajesh ');
593 END IF;
594
595
596 END LOOP ; -- main repl loop
597
598 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
599 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
600 , G_MODULE_PREFIX ||
601 'OUT OF main loop '
602 , 'rajesh');
603 END IF;
604
605
606 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
607 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
608 , G_MODULE_PREFIX ||
609 'completed update_replenishment_date'
610 , 'EXit');
611 END IF;
612
613 END update_replenishment_date ;
614
615
616 -----------------------------------------------------------
617 -- FUNCTION : check pick released
618 ------------------------------------------------------------
619 FUNCTION check_pick_released
620 ( p_header_id IN NUMBER
621 , p_line_id IN NUMBER
622 ) RETURN VARCHAR2
623
624 IS
625
626 l_id NUMBER;
627 l_released VARCHAR2(1);
628 l_release_status VARCHAR2(3) ;
629
630 BEGIN
631
632 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
633 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
634 , G_MODULE_PREFIX ||
635 'check_pick_released '
636 , 'ENTRY');
637 END IF;
638
639 l_released := 'N' ;
640 BEGIN
641 SELECT
642 delivery_detail_id
643 , released_status
644 INTO
645 l_id
646 , l_release_status
647 FROM
648 WSH_DELIVERY_DETAILS
649 WHERE source_header_id = p_header_id
650 AND source_line_id = p_line_id
651 AND NVL(released_status,'R') = 'Y' ;
652
653 l_released := 'Y' ;
654
655 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
657 , 'JMFVSKRB: l_id : l_release_status : l_released = '
658 , l_id || l_release_status || l_released );
659 END IF;
660
661
662 EXCEPTION
663 WHEN NO_DATA_FOUND
664 THEN
665 l_released := 'N' ;
666
667 WHEN TOO_MANY_ROWS
668 THEN
669 l_released := 'Y' ;
670
671 END;
672
673
674 RETURN l_released ;
675
676
677 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
678 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
679 , G_MODULE_PREFIX ||
680 'check_pick_released '
681 , 'OUT ');
682 END IF;
683
684 END check_pick_released ;
685
686
687
688 -------------------------------------------------------------
689 --
690 ------------------------------------------------------------
691 FUNCTION check_repl_retain
692 ( p_subcontract_po_shipment_id IN NUMBER
693 , p_oem_organization IN NUMBER
694 , p_tp_organization IN NUMBER
695 , p_replen_so_line_id IN NUMBER
696 , p_replen_so_header_id IN NUMBER
697 , p_component_id IN NUMBER
698 , p_open_flag IN VARCHAR2
699 , p_booked_flag IN VARCHAR2
700 , p_shipped_quantity IN VARCHAR2
701 , p_invoiced_quantity IN VARCHAR2
702 , p_shipping_interfaced_flag IN VARCHAR2
703 , p_cancelled_flag IN VARCHAR2
704 ) RETURN VARCHAR2 IS
705
706 l_check VARCHAR2(1) := 'N' ;
707 l_REPLENISHMENT_PO_LINE_ID NUMBER;
708 L_SUBCONTRACT_PO_SHIPMENT_ID NUMBER ;
709 l_pick_release VARCHAR2(1) := 'N' ;
710
711 BEGIN
712
713 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
715 , G_MODULE_PREFIX ||
716 'into check_repl_retain'
717 , 'Entry');
718 END IF;
719
720 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
722 , 'JMFVSKRB : p_replen_so_line_id => '
723 , p_replen_so_line_id);
724 END IF;
725 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
726 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
727 , 'JMFVSKRB: p_replen_so_header_id => '
728 , p_replen_so_header_id );
729 END IF;
730
731 l_check := NULL ;
732
733 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
734 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
735 , G_MODULE_PREFIX ||
736 'shipped : invcd: ship_intf:cncl : '||
737 p_shipped_quantity || p_invoiced_quantity || p_shipping_interfaced_flag || p_cancelled_flag
738 , 'rajesh');
739 END IF;
740
741 IF NVL(p_open_flag,'N') = 'N' OR
742 NVL( p_shipped_quantity,0) <> 0 OR
743 NVL( p_invoiced_quantity,0) <> 0 OR
744 NVL( p_cancelled_flag,'N') = 'Y'
745 THEN
746 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
747 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
748 , G_MODULE_PREFIX ||
749 'One of the flag issue '
750 , 'INTO IF ');
751 END IF;
752
753 l_check := 'N' ;
754
755 ELSIF NVL( p_shipping_interfaced_flag,'N') = 'Y'
756 THEN
757 l_pick_release := check_pick_released
758 ( p_header_id => p_replen_so_header_id
759 , p_line_id => p_replen_so_line_id
760 ) ;
761
762 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
763 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
764 , 'JMFVSKRB: AFter check_pick_released => '
765 , l_pick_release );
766 END IF;
767
768 IF NVL(l_pick_release,'N') = 'Y'
769 THEN
770 l_check := 'N' ;
771 END IF;
772
773 ELSE
774 l_check := NULL ;
775 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
777 , G_MODULE_PREFIX ||
778 'Into other check as REPL SO is open '
779 , l_check);
780 END IF;
781
782
783 BEGIN
784 SELECT REPLENISHMENT_PO_LINE_ID
785 INTO l_REPLENISHMENT_PO_LINE_ID
786 FROM jmf_shikyu_replenishments
787 WHERE REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id
788 AND REPLENISHMENT_SO_HEADER_ID = p_replen_so_header_id ;
789
790 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
791 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
792 , G_MODULE_PREFIX ||
793 'l_REPLENISHMENT_PO_LINE_ID => '
794 , l_REPLENISHMENT_PO_LINE_ID);
795 END IF;
796
797 l_check := NULL ;
798
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 l_REPLENISHMENT_PO_LINE_ID := NULL ;
802 l_check := 'N' ;
803
804 WHEN TOO_MANY_ROWS THEN
805 l_REPLENISHMENT_PO_LINE_ID := NULL ;
806 l_check := 'N' ;
807
808 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
809 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
810 , G_MODULE_PREFIX ||
811 'Too many repl PO attached '
812 , l_check);
813 END IF;
814
815 END ;
816
817 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
818 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
819 , G_MODULE_PREFIX ||
820 'before sco check l_check => '|| l_check
821 , l_check);
822 END IF;
823
824
825 IF l_check is NULL
826 THEN
827 BEGIN
828 SELECT SUBCONTRACT_PO_SHIPMENT_ID
829 INTO L_SUBCONTRACT_PO_SHIPMENT_ID
830 FROM JMF_SHIKYU_ALLOCATIONS
831 WHERE SUBCONTRACT_PO_SHIPMENT_ID <>
832 p_subcontract_po_shipment_id
833 AND REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id ;
834
835 l_check := 'N' ;
836 EXCEPTION
837 WHEN NO_DATA_FOUND THEN
838 L_SUBCONTRACT_PO_SHIPMENT_ID := NULL ;
839 l_check := 'Y' ;
840
841 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
842 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
843 , G_MODULE_PREFIX ||
844 'only one SCO attached l_check => '
845 , l_check);
846 END IF;
847
848
849 WHEN TOO_MANY_ROWS THEN
850 L_SUBCONTRACT_PO_SHIPMENT_ID := NULL ;
851 l_check := 'N' ;
852 END ;
853 END IF ;
854
855 END IF ;
856
857
858 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
859 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
860 , G_MODULE_PREFIX ||
861 'check_repl_retain l_check => '|| l_check
862 , 'About to return ');
863 END IF;
864
865 RETURN( l_check) ;
866
867 END check_repl_retain ;
868
869 ------------------------------
870 -------------------------------------------------------------------------
871 --- Process_Date_Changes
872 -- Comments: THis api will reconcile for the subcontract PO need by date
873 -- changes
874 --------------------------------------------------------------------------
875 PROCEDURE Process_Date_Changes
876 ( p_SUBCONTRACT_PO_SHIPMENT_ID IN NUMBER
877 , p_SUBCONTRACT_PO_HEADER_ID IN NUMBER
878 , p_SUBCONTRACT_PO_LINE_ID IN NUMBER
879 , p_OLD_NEED_BY_DATE IN DATE
880 , p_UOM IN VARCHAR2
881 , p_CURRENCY IN VARCHAR2
882 , p_OEM_ORGANIZATION_ID IN NUMBER
883 , p_TP_ORGANIZATION_ID IN NUMBER
884 , p_WIP_ENTITY_ID IN NUMBER
885 , p_OSA_ITEM_ID IN NUMBER
886 , p_wip_start_quantity IN NUMBER
887 , p_new_need_by_date IN DATE
888 , p_new_ordered_quantity IN NUMBER
889 , p_old_ordered_quantity IN NUMBER
890 , p_puchasing_UOM IN VARCHAR2
891 ) IS
892
893 l_return_status VARCHAR2(1);
894 l_allocation_date DATE ;
895 l_intransit_days NUMBER ;
896 l_total_allocated_qty NUMBER;
897 l_deleted_qty NUMBER ;
898 l_retain VARCHAR2(1) ;
899 l_component_removed_qty NUMBER ;
900 L_SHIKYU_COMPONENT_ID NUMBER ;
901
902 CURSOR C_NEED_BY_DATE_CSR IS
903 SELECT alc.SUBCONTRACT_PO_SHIPMENT_ID
904 , alc.SHIKYU_COMPONENT_ID
905 , alc.REPLENISHMENT_SO_LINE_ID
906 , alc.ALLOCATED_QUANTITY
907 , alc.UOM
908 , oel.line_id
909 , oel.header_id
910 , oel.schedule_ship_date
911 --Bugfix 14246759: Adding actual_shipment_date
912 , oel.actual_shipment_date
913 , oel.ordered_quantity
914 , oel.shipped_quantity
915 , oel.invoiced_quantity
916 , oel.CANCELLED_FLAG
917 , oel.OPEN_FLAG
918 , oel.BOOKED_FLAG
919 , oel.shipping_interfaced_flag
920 FROM JMF_SHIKYU_ALLOCATIONS alc
921 , OE_ORDER_LINES_ALL oel
922 WHERE alc.SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
923 AND oel.line_id = alc.REPLENISHMENT_SO_LINE_ID
924 AND oel.open_flag = 'Y'
925 AND alc.SHIKYU_COMPONENT_ID = l_SHIKYU_COMPONENT_ID;
926
927 CURSOR C_SHIKYU_components_CSR IS
928 SELECT SUBCONTRACT_PO_SHIPMENT_ID
929 , SHIKYU_COMPONENT_ID
930 , OEM_ORGANIZATION_ID
931 FROM jmf_shikyu_components
932 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
933 order by SHIKYU_COMPONENT_ID ;
934
935 l_deleted_allocations_tbl JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;
936 l_msg_data VARCHAR2(300);
937 l_msg_count NUMBER ;
938 l_final_ship_date DATE;
939 l_reschedule_date DATE;
940 --Bugfix 14246759
941 l_api_name CONSTANT VARCHAR2(30) := 'Process_Date_Changes';
942
943 BEGIN
944
945
946 --- The overall logic used:
947 -- select the PO subcontract records where the PO shipment need by date
948 -- has been changed
949 -- IF the new need_by date has been moved forward:
950 -- Process WIP job to updated the WIP job dates
951
952 -- IF the new need_by date has been moved Backward:
953 -- invoke api - process WIP job api to update the WIP dates
954 -- for each allocations , check if the SO replinishment will
955 -- arrive on time with the lead times
956 -- IF YES: No action
957
958 -- IF NOT:
959 -- remove allocations for those SO replinishments
960 -- create new allocations
961
962 /*dbms_output.put_line(' INTO process date changes ');
963 dbms_output.put_line(' p_SUBCONTRACT_PO_SHIPMENT_ID => '||
964 p_SUBCONTRACT_PO_SHIPMENT_ID );
965 dbms_output.put_line(' p_SUBCONTRACT_PO_HEADER_ID => '||
966 p_SUBCONTRACT_PO_HEADER_ID );
967 dbms_output.put_line(' p_SUBCONTRACT_PO_LINE_ID => '||
968 p_SUBCONTRACT_PO_LINE_ID );
969 dbms_output.put_line(' p_OLD_NEED_BY_DATE => '||
970 p_OLD_NEED_BY_DATE );
971 dbms_output.put_line(' p_UOM => '||
972 p_UOM );
973 dbms_output.put_line(' p_CURRENCY => '||
974 p_CURRENCY );
975 dbms_output.put_line(' p_OEM_ORGANIZATION_ID => '||
976 p_OEM_ORGANIZATION_ID );
977 dbms_output.put_line(' p_TP_ORGANIZATION_ID => '||
978 p_TP_ORGANIZATION_ID );
979 dbms_output.put_line(' p_WIP_ENTITY_ID => '||
980 p_WIP_ENTITY_ID );
981 dbms_output.put_line(' p_OSA_ITEM_ID => '||
982 p_OSA_ITEM_ID );
983 dbms_output.put_line(' p_wip_start_quantity => '||
984 p_wip_start_quantity );
985 dbms_output.put_line(' p_new_need_by_date => '||
986 p_new_need_by_date );
987 dbms_output.put_line(' p_new_ordered_quantity => '||
988 p_new_ordered_quantity );
989 dbms_output.put_line(' p_old_ordered_quantity => '||
990 p_old_ordered_quantity );
991 dbms_output.put_line(' p_puchasing_UOM => '||
992 p_puchasing_UOM ); */
993
994 IF (g_fnd_debug = 'Y')
995 THEN
996 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
997 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
998 , G_MODULE_PREFIX || l_api_name
999 , 'Entry');
1000 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1001 , G_MODULE_PREFIX || l_api_name
1002 , 'p_old_need_by_date SCO => '|| p_old_need_by_date);
1003 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1004 , G_MODULE_PREFIX || l_api_name
1005 , 'p_new_need_by_date SCO => '|| p_new_need_by_date);
1006 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1007 , G_MODULE_PREFIX || l_api_name
1008 , 'p_SUBCONTRACT_PO_SHIPMENT_ID => '|| p_SUBCONTRACT_PO_SHIPMENT_ID);
1009 END IF;
1010 END IF;
1011
1012
1013 --dbms_output.put_line(' Start process date ');
1014 --Need by Date of Subcontracting PO moved forward.
1015 --------------------------------------------------
1016 ----Date in JMF_Subcontracting_orders is moved forward
1017 --WIP job start date is moved forward
1018
1019 IF p_new_need_by_date > p_OLD_NEED_BY_DATE
1020 THEN
1021 --dbms_output.put_line(' p_new_need_by_date > p_OLD_NEED_BY_DATE ');
1022 null;
1023 -- No need to re-allocate in this scenario
1024
1025 ELSIF p_new_need_by_date < p_old_need_by_date
1026 THEN
1027 --dbms_output.put_line(' p_new_need_by_date < p_OLD_NEED_BY_DATE ');
1028 l_allocation_date := null ;
1029 -- get the WIP completion date
1030 l_allocation_date := JMF_SHIKYU_UTIL.GET_allocation_date
1031 ( p_wip_entity_id => p_wip_entity_id );
1032
1033
1034 --dbms_output.put_line(' l_allocation_date => '|| l_allocation_date );
1035 -- rajesh main loop
1036 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1038 , G_MODULE_PREFIX || l_api_name
1039 , 'l_allocation_date => ' || l_allocation_date);
1040 END IF;
1041
1042 FOR C_SHIKYU_components_rec IN C_SHIKYU_components_CSR
1043 LOOP
1044 l_shikyu_component_id := C_SHIKYU_components_rec.SHIKYU_component_id ;
1045
1046 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1048 , G_MODULE_PREFIX || l_api_name
1049 , 'l_shikyu_component_id => '|| l_shikyu_component_id);
1050 END IF;
1051
1052 l_component_removed_qty := NULL ;
1053
1054 FOR C_NEED_BY_DATE_rec IN C_NEED_BY_DATE_CSR
1055 LOOP
1056
1057 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1059 , G_MODULE_PREFIX || l_api_name
1060 , 'RSO_line_id:' || C_NEED_BY_DATE_REC.replenishment_so_line_id ||
1061 ':RSO_sched_ship_date:' || C_NEED_BY_DATE_REC.schedule_ship_date ||
1062 ':RSO_actual_ship_date:' || C_NEED_BY_DATE_REC.actual_shipment_date);
1063 END IF;
1064
1065 l_final_ship_date := NULL ;
1066
1067 --Testing: Do we need to pass actual_ship_date here?
1068 l_final_ship_date := JMF_SHIKYU_UTIL.get_final_ship_date
1069 ( p_oem_organization => p_oem_organization_id
1070 , p_tp_organization => p_tp_organization_id
1071 --Bugfix 14246759: Adding actual_shipment_date
1072 --, p_scheduled_ship_date => C_NEED_BY_DATE_rec.schedule_ship_date
1073 , p_scheduled_ship_date => nvl(C_NEED_BY_DATE_rec.actual_shipment_date,
1074 C_NEED_BY_DATE_rec.schedule_ship_date)
1075 );
1076
1077 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1079 , G_MODULE_PREFIX || l_api_name
1080 , 'l_final_ship_date => '|| l_final_ship_date);
1081 END IF;
1082
1083
1084 IF l_final_ship_date > l_allocation_date
1085 THEN
1086 --dbms_output.put_line(' delete allocations 1');
1087 --dbms_output.put_line(' C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID => '
1088 -- || C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID );
1089
1090 -- Rajesh: reschedule
1091
1092 l_retain := NULL ;
1093
1094 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1096 , G_MODULE_PREFIX || l_api_name
1097 , 'Existing repl will not arrive on time:: SO LINE ID:' ||
1098 C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID );
1099 END IF;
1100
1101 l_retain := check_repl_retain( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1102 , p_oem_organization => p_oem_organization_id
1103 , p_tp_organization => p_tp_organization_id
1104 , p_replen_so_line_id => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1105 , p_replen_so_header_id => C_NEED_BY_DATE_rec.header_id
1106 , p_component_id => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1107 , p_open_flag => C_NEED_BY_DATE_rec.open_flag
1108 , p_booked_flag => C_NEED_BY_DATE_rec.booked_flag
1109 , p_shipped_quantity => C_NEED_BY_DATE_rec.shipped_quantity
1110 , p_invoiced_quantity => C_NEED_BY_DATE_rec.invoiced_quantity
1111 , p_shipping_interfaced_flag => C_NEED_BY_DATE_rec.shipping_interfaced_flag
1112 , p_cancelled_flag => C_NEED_BY_DATE_rec.cancelled_flag
1113 );
1114
1115 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1117 , G_MODULE_PREFIX || l_api_name
1118 , 'l_retain => ' || l_retain );
1119 END IF;
1120
1121 IF NVL(l_retain,'Y') = 'N'
1122 THEN
1123 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1124 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1125 , G_MODULE_PREFIX || l_api_name
1126 , 'Call Delete_Allocations for repl SO LINE ID '||
1127 C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1128 END IF;
1129
1130 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1131 ( p_api_version => 1.0
1132 , p_init_msg_list => NULL
1133 , x_return_status => l_return_status
1134 , x_msg_count => l_msg_count
1135 , x_msg_data => l_msg_data
1136 , p_subcontract_po_shipment_id => C_NEED_BY_DATE_rec.subcontract_po_shipment_id
1137 , p_component_id => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1138 , p_replen_so_line_id => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1139 , x_deleted_allocations_tbl => l_deleted_allocations_tbl
1140 );
1141
1142 l_component_removed_qty := NVL(l_component_removed_qty,0) + C_NEED_BY_DATE_rec.allocated_quantity ;
1143
1144 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1145 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1146 , G_MODULE_PREFIX || l_api_name
1147 , 'C_NEED_BY_DATE_rec.allocated_quantity =>' || C_NEED_BY_DATE_rec.allocated_quantity );
1148 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1149 , G_MODULE_PREFIX || l_api_name
1150 , 'l_component_removed_qty => ' || l_component_removed_qty );
1151 END IF;
1152
1153 -- END IF; ---l_final_ship_date
1154
1155 ELSE
1156 -- Update the existing repl
1157 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1158 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1159 , G_MODULE_PREFIX || l_api_name
1160 , 'Retain Replen = Y. Reschedule RSO line_id:' || C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1161 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1162 , G_MODULE_PREFIX || l_api_name
1163 , 'Calling JMF_SHIKYU_ONT_PVT.Calculate_Ship_Date for:' || l_allocation_date);
1164 END IF;
1165
1166
1167 l_reschedule_date := NULL ;
1168 JMF_SHIKYU_ONT_PVT.Calculate_Ship_Date
1169 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1170 , p_component_item_id => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1171 , p_oem_organization_id => p_oem_organization_id
1172 , p_tp_organization_id => p_tp_organization_id
1173 , p_quantity => C_NEED_BY_DATE_rec.allocated_quantity
1174 , p_need_by_date => l_allocation_date
1175 , x_ship_date => l_reschedule_date );
1176
1177 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1179 , G_MODULE_PREFIX || l_api_name
1180 , 'l_reschedule_date => '|| l_reschedule_date);
1181 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1182 , G_MODULE_PREFIX || l_api_name
1183 , 'Call update_replenishment_date for SO LINE ID:' || C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1184 END IF;
1185
1186 update_replenishment_date
1187 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1188 , p_oem_organization => p_oem_organization_id
1189 , p_tp_organization => p_tp_organization_id
1190 , p_replen_so_line_id => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1191 , p_replen_so_header_id => C_NEED_BY_DATE_rec.header_id
1192 , p_component_id => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1193 , p_new_ship_date => l_reschedule_date
1194 , p_allocation_date => l_allocation_date
1195 );
1196
1197 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1199 , G_MODULE_PREFIX || l_api_name
1200 , 'After update_replenishment_date');
1201 END IF;
1202
1203 END IF; -- end of reschedule IF
1204 --dbms_output.put_line(' Next in loop ');
1205 END IF ; -- l_final_ship_date
1206 END LOOP ;
1207
1208 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1209 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1210 , G_MODULE_PREFIX || l_api_name
1211 , 'Out of components loop rem qty: '|| l_component_removed_qty);
1212 END IF;
1213
1214 IF NVL(l_component_removed_qty,0) > 0
1215 THEN
1216 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1217 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1218 , G_MODULE_PREFIX || l_api_name
1219 , 'Going to call Create new allocations for comp' || l_SHIKYU_component_id );
1220 END IF;
1221
1222 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations
1223 ( p_api_version => 1.0
1224 , p_init_msg_list => NULL
1225 , x_return_status => l_return_status
1226 , x_msg_count => l_msg_count
1227 , x_msg_data => l_msg_data
1228 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1229 , p_component_id => l_SHIKYU_component_id
1230 , p_qty => l_component_removed_qty
1231 , p_skip_po_replen_creation => 'N'
1232 );
1233
1234 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1236 , G_MODULE_PREFIX || l_api_name
1237 , 'After Create new allocations for comp:' || l_return_status );
1238 END IF;
1239 END IF ; -- l_component_removed_qty IF
1240
1241
1242 l_component_removed_qty := NULL ;
1243 l_retain := NULL ;
1244 l_SHIKYU_component_id := NULL ;
1245
1246 END LOOP ; -- Main loop ;
1247 --dbms_output.put_line(' out of loop ');
1248 END IF;
1249
1250
1251 IF (g_fnd_debug = 'Y')
1252 THEN
1253 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1254 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1255 , G_MODULE_PREFIX || l_api_name
1256 , 'Exit');
1257 END IF;
1258 END IF;
1259
1260 --dbms_output.put_line(' out of Process_Date_Changes ');
1261 END Process_Date_Changes ;
1262
1263
1264 --=============================================================================
1265 -- API NAME : Process_SHIKYU_Reconciliation
1266 -- TYPE : PRIVATE
1267 -- PRE-REQS : SHIKYU datamodel and SHIKYU process should exists
1268 -- DESCRIPTION : Process the SHIKYi reconciliation once the shikyu
1269 -- Interlock has been run
1270
1271 -- PARAMETERS :
1272 -- p_api_version REQUIRED. API version
1273 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message list
1274 -- FND_API.G_FALSE to not reset it.
1275 -- If pass NULL, it means FND_API.G_FALSE.
1276 -- p_commit OPTIONAL. FND_API.G_TRUE to have API commit the change
1277 -- FND_API.G_FALSE to not commit the change.
1278 -- Include this if API does DML.
1279 -- If pass NULL, it means FND_API.G_FALSE.
1280 -- p_validation_level OPTIONAL. value between 0 and 100.
1281 -- FND_API.G_VALID_LEVEL_NONE -> 0
1282 -- FND_API.G_VALID_LEVEL_FULL -> 100
1283 -- Public APIs should not have this parameter
1284 -- since it should always be FULL validation.
1285 -- If API perform some level not required by
1286 -- some API caller, this parameter should be
1287 -- included.
1288 -- Product group can define intermediate
1289 -- validation levels.
1290 -- If pass NULL, it means i
1291 -- FND_API.G_VALID_LEVEL_FULL
1292 --
1293 -- x_return_status REQUIRED. Value can be
1294 -- FND_API.G_RET_STS_SUCCESS
1295 -- FND_API.G_RET_STS_ERROR
1296 -- FND_API.G_RET_STS_UNEXP_ERROR
1297 -- x_msg_count REQUIRED. Number of messages on the message list
1298 -- x_msg_data REQUIRED. Return message data if message count is 1
1299 -- p_card_id REQUIRED. Card ID to be deleted.
1300 -- EXCEPTIONS :
1301 --
1302 --=============================================================================
1303 PROCEDURE Process_SHIKYU_Reconciliation
1304 ( p_api_version IN NUMBER
1305 , p_init_msg_list IN VARCHAR2
1306 , p_commit IN VARCHAR2
1307 , p_validation_level IN NUMBER
1308 , x_return_status OUT NOCOPY VARCHAR2
1309 , x_msg_count OUT NOCOPY NUMBER
1310 , x_msg_data OUT NOCOPY VARCHAR2
1311 , P_Operating_unit IN NUMBER
1312 , p_from_organization IN NUMBER
1313 , p_to_organization IN NUMBER
1314 )
1315 IS
1316 l_api_name CONSTANT VARCHAR2(30) := 'Process_SHIKYU_Reconciliation' ;
1317 l_api_version CONSTANT NUMBER := 1.0;
1318 l_deleted_qty NUMBER ;
1319
1320
1321 CURSOR SHIKYU_reconcile_CSR IS
1322 SELECT sco.SUBCONTRACT_PO_SHIPMENT_ID SUBCONTRACT_PO_SHIPMENT_ID
1323 , sco.SUBCONTRACT_PO_HEADER_ID SUBCONTRACT_PO_HEADER_ID
1324 , sco.SUBCONTRACT_PO_LINE_ID SUBCONTRACT_PO_LINE_ID
1325 , trunc(sco.NEED_BY_DATE) sco_NEED_BY_DATE
1326 , sco.UOM UOM
1327 , sco.CURRENCY CURRENCY
1328 , sco.OEM_ORGANIZATION_ID OEM_ORGANIZATION_ID
1329 , sco.TP_ORGANIZATION_ID TP_ORGANIZATION_ID
1330 , sco.WIP_ENTITY_ID WIP_ENTITY_ID
1331 , sco.OSA_ITEM_ID OSA_ITEM_ID
1332 , wdj.start_quantity start_quantity
1333 , NVL(trunc(poll.need_by_date), trunc(poll.promised_date))
1334 pol_need_by_date
1335 , poll.quantity quantity
1336 , sco.quantity old_ordered_quantity
1337 , poll.unit_meas_lookup_code purchasing_uom
1338 , pol.cancel_flag pol_cancel_flag
1339 , poh.cancel_flag poh_cancel_flag
1340 , poll.cancel_flag poll_cancel_flag
1341 , trunc( wdj.SCHEDULED_START_DATE) SCHEDULED_START_DATE
1342 FROM JMF_SUBCONTRACT_ORDERS sco
1343 , po_headers_all poh
1344 , po_lines_all pol
1345 , po_line_locations_all poll
1346 , wip_discrete_jobs wdj
1347 WHERE pol.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
1348 and pol.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
1349 and poll.po_line_id = pol.po_line_id
1350 and poll.line_location_id = sco.SUBCONTRACT_PO_SHIPMENT_ID
1351 and wdj.wip_entity_id = sco.WIP_ENTITY_ID
1352 and sco.interlock_status = 'P'
1353 and ( trunc(sco.NEED_BY_DATE) <>
1354 NVL(trunc(poll.need_by_date), trunc(poll.promised_date)) OR
1355 sco.quantity <> poll.quantity OR
1356 pol.cancel_flag = 'Y' OR
1357 poh.cancel_flag = 'Y' OR
1358 poll.cancel_flag = 'Y' )
1359 and poh.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
1360 and pol.po_header_id = poh.po_header_id
1361 ORDER BY sco.subcontract_po_shipment_id ;
1362
1363 l_SUBCONTRACT_PO_SHIPMENT_ID NUMBER ;
1364
1365 CURSOR C_shikyu_cancel_comp_CSR IS
1366 SELECT SUBCONTRACT_PO_SHIPMENT_ID
1367 , SHIKYU_COMPONENT_ID
1368 , OEM_ORGANIZATION_ID
1369 , SHIKYU_COMPONENT_PRICE
1370 , PRIMARY_UOM
1371 FROM JMF_SHIKYU_COMPONENTS
1372 WHERE SUBCONTRACT_PO_SHIPMENT_ID = l_subcontract_po_shipment_id ;
1373
1374 l_used_quantity NUMBER;
1375
1376 l_deleted_allocations_tbl
1377 JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;
1378 l_msg_data VARCHAR2(300);
1379 l_msg_count NUMBER ;
1380 l_return_status VARCHAR2(3);
1381 l_primary_quantity NUMBER ;
1382
1383 BEGIN
1384
1385 -- First step : calean up invalid data
1386 -- second: reconcile partial transactions
1387 -- Third: reconcile over/under shipments
1388 -- Main api for the reconciliation process
1389 -- select the subcontract records where either DATES or qty has been
1390 -- changed after interlock OR the PO shipment cancelled
1391
1392 -- IF PO shipment cancelled :
1393 -- cancel the WIP job
1394 -- remove all the allocations for the qty that have not been
1395 -- issued to WIP job yet.
1396
1397 -- IF not cancelled, but date or qty changed:
1398
1399 -- process the date changes
1400 -- process the qty changes
1401
1402 -- update the JMF subcontract table with the new values
1403 -- COMMIT
1404
1405 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1406 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1407 , G_MODULE_PREFIX || l_api_name
1408 , 'Begin');
1409 END IF;
1410
1411 -- Start API initialization
1412 FND_MSG_PUB.initialize;
1413
1414 x_return_status := FND_API.G_RET_STS_SUCCESS;
1415 -- End API initialization
1416
1417 -- Select the eligible data for Reconciliation
1418
1419 -- Process the changes due to PO subcontract order NEED BY DATE Only
1420 -- Process the change due to the PO subcontract QTY changes Only
1421 -- Process changes due to both PO subcontract need by date and qty changes
1422
1423 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1424 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1425 , G_MODULE_PREFIX || l_api_name
1426 , 'Calling JMF_SHIKYU_UTIL.clean_invalid_data');
1427 END IF;
1428
1429 JMF_SHIKYU_UTIL.clean_invalid_data ;
1430
1431 COMMIT;
1432
1433 -- dbms_output.put_line(' Calling Reconcile_Partial_Shipments ');
1434 IF (g_fnd_debug = 'Y')
1435 THEN
1436 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1437 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1438 , G_MODULE_PREFIX || l_api_name
1439 , 'Calling Reconcile_Partial_Shipments');
1440 END IF;
1441 END IF;
1442
1443 BEGIN
1444 JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Partial_Shipments
1445 ( p_api_version => 1.0
1446 , p_init_msg_list => 'Y'
1447 , x_return_status => l_return_status
1448 , x_msg_count => l_msg_count
1449 , x_msg_data => l_msg_data
1450 , p_from_organization => p_from_organization
1451 , p_to_organization => p_to_organization
1452 );
1453
1454 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1455 THEN
1456 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1457 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1458 , G_MODULE_PREFIX || l_api_name
1459 , 'Reconcile_Partial_Shipments returned error. Status:' || l_return_status);
1460 END IF;
1461
1462 rollback;
1463 ELSE
1464 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1465 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1466 , G_MODULE_PREFIX || l_api_name
1467 , 'Reconcile_Partial_Shipments returned success. Status:' || l_return_status);
1468 END IF;
1469
1470 COMMIT;
1471 END IF;
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 ROLLBACK;
1476 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1477 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1478 , G_MODULE_PREFIX || l_api_name
1479 , 'JMFVSKRB: OUT OF Reconcile_Partial_Shipments -- EXCEPTION => ' || l_return_status);
1480 END IF;
1481
1482 END ;
1483
1484 IF (g_fnd_debug = 'Y')
1485 THEN
1486 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1488 , G_MODULE_PREFIX || l_api_name
1489 , 'Calling Reconcile_Closed_Shipments');
1490 END IF;
1491 END IF;
1492
1493 JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Closed_Shipments
1494 ( p_api_version => 1.0
1495 , p_init_msg_list => NULL
1496 , x_return_status => l_return_status
1497 , x_msg_count => l_msg_count
1498 , x_msg_data => l_msg_data
1499 );
1500
1501 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1502 THEN
1503 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1504 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1505 , G_MODULE_PREFIX || l_api_name
1506 , 'Reconcile_Partial_Shipments returned error. Status:' || l_return_status);
1507 END IF;
1508 ELSE
1509 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1510 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1511 , G_MODULE_PREFIX || l_api_name
1512 , 'Reconcile_Partial_Shipments returned success. Status:' || l_return_status);
1513 END IF;
1514 END IF;
1515 --End bugfix 14078692.
1516
1517 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1518 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1519 , G_MODULE_PREFIX || l_api_name
1520 , 'Continuing after Reconciling RSOs.');
1521 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1522 , G_MODULE_PREFIX || l_api_name
1523 , 'x_msg_data:' || x_msg_data);
1524 END IF;
1525
1526
1527 l_return_status := NULL;
1528
1529 -- dbms_output.put_line(' About to open cursor SHIKYU_reconcile_CSR');
1530 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1531 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1532 , G_MODULE_PREFIX || l_api_name
1533 , 'About to open cursor SHIKYU_reconcile_CSR.');
1534 END IF;
1535
1536 FOR SHIKYU_reconcile_rec IN SHIKYU_reconcile_CSR
1537 LOOP
1538 BEGIN
1539
1540 l_subcontract_po_shipment_id := SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID;
1541
1542 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1544 , G_MODULE_PREFIX || l_api_name
1545 , '========================================'
1546 );
1547
1548 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1549 , G_MODULE_PREFIX || l_api_name
1550 , 'SPO_SHIPMENT_ID:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID ||
1551 ':SPO_HEADER_ID:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID ||
1552 ':SPO_LINE_ID:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1553 );
1554
1555 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1556 , G_MODULE_PREFIX || l_api_name
1557 , 'SCO_NEED_BY_DATE:' || SHIKYU_reconcile_rec.sco_NEED_BY_DATE ||
1558 ':POL_NEED_BY_DATE:' || SHIKYU_reconcile_rec.pol_need_by_date
1559 );
1560
1561 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1562 , G_MODULE_PREFIX || l_api_name
1563 , 'Cancel Flags:: POH:' || SHIKYU_reconcile_rec.poh_cancel_flag ||
1564 ':POL:' || SHIKYU_reconcile_rec.pol_cancel_flag ||
1565 ':POLL:' || SHIKYU_reconcile_rec.poll_cancel_flag);
1566
1567 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1568 , G_MODULE_PREFIX || l_api_name
1569 , 'OEM:' || SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID ||
1570 ':MP:' || SHIKYU_reconcile_rec.TP_ORGANIZATION_ID ||
1571 ':WE_Id:' || SHIKYU_reconcile_rec.WIP_ENTITY_ID ||
1572 ':WIP_Start_Date:' || SHIKYU_reconcile_rec.SCHEDULED_START_DATE ||
1573 ':WIP_Qty:' || SHIKYU_reconcile_rec.start_quantity ||
1574 ':OSA:' || SHIKYU_reconcile_rec.OSA_ITEM_ID ||
1575 ':Currency:' || SHIKYU_reconcile_rec.CURRENCY
1576 );
1577
1578 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1579 , G_MODULE_PREFIX || l_api_name
1580 , 'SPO_Qty:' || SHIKYU_reconcile_rec.old_ordered_quantity ||
1581 ':SPO_UOM:' || SHIKYU_reconcile_rec.UOM ||
1582 ':POLL_Qty:' || SHIKYU_reconcile_rec.quantity ||
1583 ':POLL_UOM:' || SHIKYU_reconcile_rec.purchasing_uom
1584 );
1585 END IF;
1586
1587 IF SHIKYU_reconcile_rec.pol_cancel_flag = 'Y' OR
1588 SHIKYU_reconcile_rec.poh_cancel_flag = 'Y' OR
1589 SHIKYU_reconcile_rec.poll_cancel_flag = 'Y'
1590 THEN
1591 BEGIN
1592 -- Cancel WIP Jobs
1593 -- Remove all the allocations
1594 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1595 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1596 , G_MODULE_PREFIX || l_api_name
1597 , 'CANCEL for SPO_SHIPMENT_ID:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1598 END IF;
1599
1600 JMF_SHIKYU_WIP_PVT.Process_WIP_Job
1601 ( p_action => 'D'
1602 , p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1603 , p_need_by_date => NULL
1604 , p_quantity => NULL
1605 , x_return_status => l_return_status
1606 );
1607
1608 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1609 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1610 , G_MODULE_PREFIX || l_api_name
1611 , 'Status from Process_WIP_Job:' || l_return_status );
1612 END IF;
1613
1614 --COMMIT;
1615
1616 IF SHIKYU_reconcile_rec.scheduled_start_date > SYSDATE
1617 THEN
1618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1619 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1620 , G_MODULE_PREFIX || l_api_name
1621 , 'Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations.');
1622 END IF;
1623
1624 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1625 ( p_api_version => 1.0
1626 , p_init_msg_list => NULL
1627 , x_return_status => l_return_status
1628 , x_msg_count => l_msg_count
1629 , x_msg_data => l_msg_data
1630 , p_subcontract_po_shipment_id => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1631 , p_component_id => NULL
1632 , p_replen_so_line_id => NULL
1633 , x_deleted_allocations_tbl => l_deleted_allocations_tbl
1634 );
1635
1636 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1637 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1638 , G_MODULE_PREFIX || l_api_name
1639 , 'Delete_Allocations:: Status => '|| l_return_status );
1640 END IF;
1641
1642 ELSE
1643 -- dbms_output.put_line(' Cancel components ');
1644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1645 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1646 , G_MODULE_PREFIX || l_api_name
1647 , 'INTO Cancel components SCO SHIPMENT:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1648 END IF;
1649
1650 l_used_quantity := NULL;
1651
1652 FOR C_shikyu_cancel_comp_rec IN C_shikyu_cancel_comp_CSR
1653 LOOP
1654 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1655 THEN
1656 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1657 , G_MODULE_PREFIX || l_api_name
1658 , 'CURSOR shikyu_cancel_comp_rec.shikyu_component_id => ' || c_shikyu_cancel_comp_rec.shikyu_component_id );
1659 END IF;
1660
1661 l_used_quantity := 0 ;
1662
1663 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1664 THEN
1665 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1666 , G_MODULE_PREFIX || l_api_name
1667 , 'JMF_SHIKYU_UTIL.GET_used_quantity for:' || C_shikyu_cancel_comp_rec.shikyu_component_id );
1668 END IF;
1669
1670 l_used_quantity := JMF_SHIKYU_UTIL.GET_used_quantity
1671 ( p_wip_entity_id => SHIKYU_reconcile_rec.wip_entity_id
1672 , p_shikyu_component_id => C_shikyu_cancel_comp_rec.shikyu_component_id
1673 , p_organization_id => SHIKYU_reconcile_rec.oem_organization_id
1674 );
1675
1676 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1677 THEN
1678 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1679 , G_MODULE_PREFIX || l_api_name
1680 , 'l_used_quantity => ' || l_used_quantity );
1681 END IF;
1682
1683
1684 IF NVL(l_used_quantity,0) <= 0
1685 THEN
1686 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1687 THEN
1688 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1689 , G_MODULE_PREFIX || l_api_name
1690 , 'Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations:' || C_shikyu_cancel_comp_rec.shikyu_component_id );
1691 END IF;
1692
1693 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1694 ( p_api_version => 1.0
1695 , p_init_msg_list => NULL
1696 , x_return_status => l_return_status
1697 , x_msg_count => l_msg_count
1698 , x_msg_data => l_msg_data
1699 , p_subcontract_po_shipment_id => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1700 , p_component_id => C_shikyu_cancel_comp_rec.shikyu_component_id
1701 , p_replen_so_line_id => NULL
1702 , x_deleted_allocations_tbl => l_deleted_allocations_tbl
1703 );
1704 END IF;
1705 END LOOP;
1706 END IF ; -- Greater than start date
1707
1708 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1709 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1710 , G_MODULE_PREFIX || l_api_name
1711 , 'JMFVSKRB: UPDATE and COMMIT for SCO PO CANCEL flow ' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1712 END IF;
1713
1714
1715 UPDATE JMF_SUBCONTRACT_ORDERS
1716 SET interlock_status = 'T'
1717 WHERE SUBCONTRACT_PO_SHIPMENT_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1718 and ( SHIKYU_reconcile_rec.pol_cancel_flag = 'Y' OR
1719 SHIKYU_reconcile_rec.poh_cancel_flag = 'Y' OR
1720 SHIKYU_reconcile_rec.poll_cancel_flag = 'Y' );
1721
1722
1723 COMMIT;
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 BEGIN
1728 ROLLBACK ;
1729
1730 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1731 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1732 , G_MODULE_PREFIX || l_api_name
1733 , 'JMFVSKRB: EXCEPTION 1 in CANCEL FLOW ' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1734 END IF;
1735 END ;
1736 END ;
1737
1738 ELSE -- NOT cancel logic
1739 l_return_status := NULL ;
1740 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1741 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1742 , G_MODULE_PREFIX || l_api_name
1743 , 'INto NON cancel logic::l_return_status => '|| l_return_status );
1744 END IF;
1745 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1746 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1747 , G_MODULE_PREFIX || l_api_name
1748 , 'SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID => ' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID);
1749 END IF;
1750
1751 l_primary_quantity := NULL;
1752 l_primary_quantity := JMF_SHIKYU_UTIL.get_prImary_quantity
1753 ( p_purchasing_UOM => SHIKYU_reconcile_rec.purchasing_UOM
1754 , p_quantity => SHIKYU_reconcile_rec.quantity
1755 , P_inventory_org_id => SHIKYU_reconcile_rec.oem_organization_id
1756 , p_inventory_item_id => SHIKYU_reconcile_rec.OSA_ITEM_ID ) ;
1757
1758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1759 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1760 , G_MODULE_PREFIX || l_api_name
1761 , 'Calling JMF_SHIKYU_WIP_PVT.Process_WIP_Job U for SCO:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1762 END IF;
1763
1764 JMF_SHIKYU_WIP_PVT.Process_WIP_Job
1765 ( p_action => 'U'
1766 , p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1767 , p_need_by_date => SHIKYU_reconcile_rec.pol_need_by_date
1768 , p_quantity => l_primary_quantity
1769 , x_return_status => l_return_status
1770 ) ;
1771
1772 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1774 , G_MODULE_PREFIX || l_api_name
1775 , 'out JMF_SHIKYU_WIP_PVT.Process_WIP_Job with ' || l_return_status );
1776 END IF;
1777
1778 IF l_return_status = 'S'
1779 THEN
1780 -- changes IF
1781 IF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) <> TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1782 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) = ( SHIKYU_reconcile_rec.quantity )
1783 THEN
1784
1785 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1786 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1787 , G_MODULE_PREFIX || l_api_name
1788 , 'Calling Process_Date_Changes:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1789 END IF;
1790
1791 Process_Date_Changes
1792 (p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1793 , p_SUBCONTRACT_PO_HEADER_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1794 , p_SUBCONTRACT_PO_LINE_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1795 , p_OLD_NEED_BY_DATE => SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1796 , p_UOM => SHIKYU_reconcile_rec.UOM
1797 , p_CURRENCY => SHIKYU_reconcile_rec.CURRENCY
1798 , p_OEM_ORGANIZATION_ID => SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1799 , p_TP_ORGANIZATION_ID => SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1800 , p_WIP_ENTITY_ID => SHIKYU_reconcile_rec.WIP_ENTITY_ID
1801 , p_OSA_ITEM_ID => SHIKYU_reconcile_rec.OSA_ITEM_ID
1802 , p_wip_start_quantity => SHIKYU_reconcile_rec.start_quantity
1803 , p_new_need_by_date => SHIKYU_reconcile_rec.pol_need_by_date
1804 , p_new_ordered_quantity => SHIKYU_reconcile_rec.quantity
1805 , p_old_ordered_quantity => SHIKYU_reconcile_rec.old_ordered_quantity
1806 , p_puchasing_UOM => SHIKYU_reconcile_rec.purchasing_uom
1807 );
1808
1809
1810 ELSIF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) = TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1811 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) <> ( SHIKYU_reconcile_rec.quantity )
1812 THEN
1813 -- Process Qty changes
1814
1815 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1816 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1817 , G_MODULE_PREFIX || l_api_name
1818 , 'Calling Process_Quantity_Changes:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1819 END IF;
1820
1821 Process_Quantity_Changes
1822 ( p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1823 , p_SUBCONTRACT_PO_HEADER_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1824 , p_SUBCONTRACT_PO_LINE_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1825 , p_OLD_NEED_BY_DATE => SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1826 , p_UOM => SHIKYU_reconcile_rec.UOM
1827 , p_CURRENCY => SHIKYU_reconcile_rec.CURRENCY
1828 , p_OEM_ORGANIZATION_ID => SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1829 , p_TP_ORGANIZATION_ID => SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1830 , p_WIP_ENTITY_ID => SHIKYU_reconcile_rec.WIP_ENTITY_ID
1831 , p_OSA_ITEM_ID => SHIKYU_reconcile_rec.OSA_ITEM_ID
1832 , p_wip_start_quantity => SHIKYU_reconcile_rec.start_quantity
1833 , p_new_need_by_date => SHIKYU_reconcile_rec.pol_need_by_date
1834 , p_new_ordered_quantity => SHIKYU_reconcile_rec.quantity
1835 , p_old_ordered_quantity => SHIKYU_reconcile_rec.old_ordered_quantity
1836 , p_puchasing_UOM => SHIKYU_reconcile_rec.purchasing_uom
1837 ) ;
1838
1839
1840 ELSIF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) <> TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1841 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) <> ( SHIKYU_reconcile_rec.quantity )
1842 THEN
1843
1844 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1845 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1846 , G_MODULE_PREFIX || l_api_name
1847 , 'Calling Process_Date_Changes:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1848 END IF;
1849
1850 Process_Date_Changes
1851 ( p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1852 , p_SUBCONTRACT_PO_HEADER_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1853 , p_SUBCONTRACT_PO_LINE_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1854 , p_OLD_NEED_BY_DATE => SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1855 , p_UOM => SHIKYU_reconcile_rec.UOM
1856 , p_CURRENCY => SHIKYU_reconcile_rec.CURRENCY
1857 , p_OEM_ORGANIZATION_ID => SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1858 , p_TP_ORGANIZATION_ID => SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1859 , p_WIP_ENTITY_ID => SHIKYU_reconcile_rec.WIP_ENTITY_ID
1860 , p_OSA_ITEM_ID => SHIKYU_reconcile_rec.OSA_ITEM_ID
1861 , p_wip_start_quantity => SHIKYU_reconcile_rec.start_quantity
1862 , p_new_need_by_date => SHIKYU_reconcile_rec.pol_need_by_date
1863 , p_new_ordered_quantity => SHIKYU_reconcile_rec.quantity
1864 , p_old_ordered_quantity => SHIKYU_reconcile_rec.old_ordered_quantity
1865 , p_puchasing_UOM => SHIKYU_reconcile_rec.purchasing_uom
1866 ) ;
1867
1868 Process_Quantity_Changes
1869 ( p_SUBCONTRACT_PO_SHIPMENT_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1870 , p_SUBCONTRACT_PO_HEADER_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1871 , p_SUBCONTRACT_PO_LINE_ID => SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1872 , p_OLD_NEED_BY_DATE => SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1873 , p_UOM => SHIKYU_reconcile_rec.UOM
1874 , p_CURRENCY => SHIKYU_reconcile_rec.CURRENCY
1875 , p_OEM_ORGANIZATION_ID => SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1876 , p_TP_ORGANIZATION_ID => SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1877 , p_WIP_ENTITY_ID => SHIKYU_reconcile_rec.WIP_ENTITY_ID
1878 , p_OSA_ITEM_ID => SHIKYU_reconcile_rec.OSA_ITEM_ID
1879 , p_wip_start_quantity => SHIKYU_reconcile_rec.start_quantity
1880 , p_new_need_by_date => SHIKYU_reconcile_rec.pol_need_by_date
1881 , p_new_ordered_quantity => SHIKYU_reconcile_rec.quantity
1882 , p_old_ordered_quantity => SHIKYU_reconcile_rec.old_ordered_quantity
1883 , p_puchasing_UOM => SHIKYU_reconcile_rec.purchasing_uom
1884 );
1885
1886
1887 END IF; -- changes IF
1888
1889 UPDATE JMF_SUBCONTRACT_ORDERS
1890 SET quantity = SHIKYU_reconcile_rec.quantity
1891 , need_by_date = SHIKYU_reconcile_rec.pol_need_by_date
1892 , last_update_date = sysdate
1893 , last_updated_by = FND_GLOBAL.user_id
1894 , last_update_login = FND_GLOBAL.login_id
1895 WHERE SUBCONTRACT_PO_HEADER_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1896 and SUBCONTRACT_PO_LINE_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1897 and SUBCONTRACT_PO_SHIPMENT_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID ;
1898
1899 COMMIT;
1900
1901 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1902 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1903 , G_MODULE_PREFIX || l_api_name
1904 , 'AFTER UPDATE JMF_SUBCONTRACT_ORDERS:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1905 END IF;
1906
1907 ELSE -- return status
1908 ROLLBACK;
1909 END IF; -- WIP job
1910 END IF; -- soc status
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914 ROLLBACK ;
1915 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1917 ,G_MODULE_PREFIX || l_api_name
1918 ,'MAIN LOOP exception for SCO shipment:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1919 END IF;
1920 END ;
1921
1922 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1923 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1924 , G_MODULE_PREFIX || l_api_name
1925 , 'Done processing SPO_shipment_Id:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1926 END IF;
1927
1928 END LOOP ; -- Main Loop
1929
1930 -- dbms_output.put_line(' Out of Main loop ');
1931
1932 -- Fix 14246759: Reconciling SO schedule ship date changes.
1933 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1934 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1935 , G_MODULE_PREFIX || l_api_name
1936 , '====Reconciling RSO date changes=======');
1937 END IF;
1938
1939 JMF_SHIKYU_ALLOCATION_PVT.reconcile_rso_date_changes(x_return_status => l_return_status);
1940
1941 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1942 THEN
1943 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1944 THEN
1945 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1946 , G_MODULE_PREFIX || l_api_name
1947 , 'reconcile_rso_date_changes returned error. Status:' || l_return_status);
1948 END IF;
1949
1950 x_return_status := l_return_status;
1951
1952 ELSE
1953 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1954 THEN
1955 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1956 , G_MODULE_PREFIX || l_api_name
1957 , 'reconcile_rso_date_changes returned success. Status:' || l_return_status);
1958 END IF;
1959
1960 commit;
1961
1962 END IF;
1963
1964 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1965 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1966 , G_MODULE_PREFIX || l_api_name
1967 , 'End');
1968 END IF;
1969 --dbms_output.put_line(' OUT of Process_SHIKYU_Reconciliation. ');
1970 EXCEPTION
1971 WHEN FND_API.G_EXC_ERROR THEN
1972 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_ERROR ');
1973 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
1974
1975 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1976 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1977 , G_MODULE_PREFIX || l_api_name
1978 , 'Expected Error:' || sqlerrm);
1979 END IF;
1980
1981 ROLLBACK;
1982 FND_MSG_PUB.Count_And_Get
1983 ( p_count => x_msg_count
1984 , p_data => x_msg_data
1985 );
1986 x_return_status := FND_API.g_ret_sts_error;
1987
1988 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1989 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_UNEXPECTED_ERROR ');
1990 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
1991
1992 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1993 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1994 , G_MODULE_PREFIX || l_api_name
1995 , 'UnExpected Error:' || sqlerrm);
1996 END IF;
1997
1998 ROLLBACK ;
1999 FND_MSG_PUB.Count_And_Get
2000 ( p_count => x_msg_count
2001 , p_data => x_msg_data
2002 );
2003
2004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2005
2006 WHEN OTHERS THEN
2007
2008 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2009 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2010 , G_MODULE_PREFIX || l_api_name
2011 , 'Others:' || sqlerrm);
2012 END IF;
2013
2014 ROLLBACK ;
2015 FND_MSG_PUB.Count_And_Get
2016 ( p_count => x_msg_count
2017 , p_data => x_msg_data
2018 );
2019
2020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2021
2022 END Process_SHIKYU_Reconciliation ;
2023
2024 END JMF_SHIKYU_RECONCILIAITON_PVT ;