[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_RECONCILIAITON_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_RECONCILIAITON_PVT AS
2 -- $Header: JMFVSKRB.pls 120.18 2006/08/31 02:47:45 rajkrish noship $
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 ;
366 SELECT poh.segment1
363
364
365 CURSOR C_PO_details_CSR IS
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 ;
507 l_repl_po_header_id :=
504
505 l_repl_po_line_id :=
506 C_select_replenishments_REC.replenishment_po_line_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
666
663 WHEN NO_DATA_FOUND
664 THEN
665 l_released := 'N' ;
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
833 AND REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id ;
830 FROM JMF_SHIKYU_ALLOCATIONS
831 WHERE SUBCONTRACT_PO_SHIPMENT_ID <>
832 p_subcontract_po_shipment_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
904 SELECT alc.SUBCONTRACT_PO_SHIPMENT_ID
905 , alc.SHIKYU_COMPONENT_ID
906 , alc.REPLENISHMENT_SO_LINE_ID
907 , alc.ALLOCATED_QUANTITY
908 , alc.UOM
909 , oel.line_id
910 , oel.header_id
911 , oel.schedule_ship_date
912 , oel.ordered_quantity
913 , oel.shipped_quantity
914 , oel.invoiced_quantity
915 , oel.CANCELLED_FLAG
916 , oel.OPEN_FLAG
917 , oel.BOOKED_FLAG
918 , oel.shipping_interfaced_flag
919 FROM JMF_SHIKYU_ALLOCATIONS alc
920 , OE_ORDER_LINES_ALL oel
921 WHERE alc.SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
922 AND oel.line_id = alc.REPLENISHMENT_SO_LINE_ID
923 AND oel.open_flag = 'Y'
924 AND alc.SHIKYU_COMPONENT_ID = l_SHIKYU_COMPONENT_ID ;
925
926 CURSOR C_SHIKYU_components_CSR IS
927 SELECT SUBCONTRACT_PO_SHIPMENT_ID
928 , SHIKYU_COMPONENT_ID
929 , OEM_ORGANIZATION_ID
930 FROM jmf_shikyu_components
931 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
932 order by SHIKYU_COMPONENT_ID ;
933
934 l_deleted_allocations_tbl
935 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
941 BEGIN
942
943
944 --- The overall logic used:
945 -- select the PO subcontract records where the PO shipment need by date
946 -- has been changed
947 -- IF the new need_by date has been moved forward:
948 -- Process WIP job to updated the WIP job dates
949
950 -- IF the new need_by date has been moved Backward:
951 -- invoke api - process WIP job api to update the WIP dates
952 -- for each allocations , check if the SO replinishment will
953 -- arrive on time with the lead times
954 -- IF YES: No action
955
956 -- IF NOT:
957 -- remove allocations for those SO replinishments
958 -- create new allocations
959
960 /*dbms_output.put_line(' INTO process date changes ');
961 dbms_output.put_line(' p_SUBCONTRACT_PO_SHIPMENT_ID => '||
962 p_SUBCONTRACT_PO_SHIPMENT_ID );
963 dbms_output.put_line(' p_SUBCONTRACT_PO_HEADER_ID => '||
964 p_SUBCONTRACT_PO_HEADER_ID );
965 dbms_output.put_line(' p_SUBCONTRACT_PO_LINE_ID => '||
966 p_SUBCONTRACT_PO_LINE_ID );
967 dbms_output.put_line(' p_OLD_NEED_BY_DATE => '||
968 p_OLD_NEED_BY_DATE );
969 dbms_output.put_line(' p_UOM => '||
970 p_UOM );
971 dbms_output.put_line(' p_CURRENCY => '||
972 p_CURRENCY );
973 dbms_output.put_line(' p_OEM_ORGANIZATION_ID => '||
974 p_OEM_ORGANIZATION_ID );
975 dbms_output.put_line(' p_TP_ORGANIZATION_ID => '||
976 p_TP_ORGANIZATION_ID );
977 dbms_output.put_line(' p_WIP_ENTITY_ID => '||
978 p_WIP_ENTITY_ID );
979 dbms_output.put_line(' p_OSA_ITEM_ID => '||
980 p_OSA_ITEM_ID );
984 p_new_need_by_date );
981 dbms_output.put_line(' p_wip_start_quantity => '||
982 p_wip_start_quantity );
983 dbms_output.put_line(' p_new_need_by_date => '||
985 dbms_output.put_line(' p_new_ordered_quantity => '||
986 p_new_ordered_quantity );
987 dbms_output.put_line(' p_old_ordered_quantity => '||
988 p_old_ordered_quantity );
989 dbms_output.put_line(' p_puchasing_UOM => '||
990 p_puchasing_UOM ); */
991
992 IF (g_fnd_debug = 'Y')
993 THEN
994 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
996 , G_MODULE_PREFIX ||
997 'Process_Date_Changes.Invoked'
998 , 'Entry');
999 END IF;
1000 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1002 , G_MODULE_PREFIX ||
1003 'p_old_need_by_date SCO => '|| p_old_need_by_date
1004 , p_old_need_by_date );
1005 END IF;
1006 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1007 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1008 , G_MODULE_PREFIX ||
1009 'p_new_need_by_date SCO => '|| p_new_need_by_date
1010 , p_new_need_by_date );
1011 END IF;
1012 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1014 , G_MODULE_PREFIX ||
1015 'p_SUBCONTRACT_PO_SHIPMENT_ID => '
1016 , p_SUBCONTRACT_PO_SHIPMENT_ID );
1017 END IF;
1018
1019 END IF;
1020
1021
1022 --dbms_output.put_line(' Start process date ');
1023 --Need by Date of Subcontracting PO moved forward.
1024 --------------------------------------------------
1025 ----Date in JMF_Subcontracting_orders is moved forward
1026 --WIP job start date is moved forward
1027
1028
1029 IF p_new_need_by_date > p_OLD_NEED_BY_DATE
1030 THEN
1031 --dbms_output.put_line(' p_new_need_by_date > p_OLD_NEED_BY_DATE ');
1032 null;
1033 -- No need to re-allocate in this scenario
1034
1035 ELSIF p_new_need_by_date < p_old_need_by_date
1036 THEN
1037 --dbms_output.put_line(' p_new_need_by_date < p_OLD_NEED_BY_DATE ');
1038 l_allocation_date := null ;
1039 -- get the WIP completion date
1040 l_allocation_date := JMF_SHIKYU_UTIL.GET_allocation_date
1041 ( p_wip_entity_id => p_wip_entity_id );
1042
1043
1044 --dbms_output.put_line(' l_allocation_date => '|| l_allocation_date );
1045 -- rajesh main loop
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 ||
1049 'rajesh l_allocation_date => '
1050 , l_allocation_date);
1051 END IF;
1052
1053 FOR C_SHIKYU_components_rec IN C_SHIKYU_components_CSR
1054 LOOP
1055 l_shikyu_component_id :=
1056 C_SHIKYU_components_rec.SHIKYU_component_id ;
1057
1058 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1060 , G_MODULE_PREFIX ||
1061 'l_shikyu_component_id => '|| l_shikyu_component_id
1062 , l_shikyu_component_id);
1063 END IF;
1064
1065 l_component_removed_qty := NULL ;
1066
1067 FOR C_NEED_BY_DATE_rec IN C_NEED_BY_DATE_CSR
1068 LOOP
1069 l_final_ship_date := NULL ;
1070
1071 l_final_ship_date := JMF_SHIKYU_UTIL.get_final_ship_date
1072 ( p_oem_organization => p_oem_organization_id
1073 , p_tp_organization => p_tp_organization_id
1074 , p_scheduled_ship_date => 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 ||
1080 'rajesh l_final_ship_date in current repl => '|| l_final_ship_date
1081 , l_final_ship_date);
1082 END IF;
1083
1084
1085 IF l_final_ship_date > l_allocation_date
1086 THEN
1087 --dbms_output.put_line(' delete allocations 1');
1088 --dbms_output.put_line(' C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID => '
1089 -- || C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID );
1090
1091 -- Rajesh: reschedule
1092
1093 l_retain := NULL ;
1094
1095 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1096 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1097 , G_MODULE_PREFIX ||
1098 'existing repl will not arrive on time SO LINE ID: '
1099 , C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID );
1100 END IF;
1101
1102 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1103 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1104 , G_MODULE_PREFIX ||
1105 'Call check_repl_retain for C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID'
1106 , C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID );
1107 END IF;
1108
1109 l_retain :=
1110 check_repl_retain
1111 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1112 , p_oem_organization => p_oem_organization_id
1113 , p_tp_organization => p_tp_organization_id
1114 , p_replen_so_line_id => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1115 , p_replen_so_header_id => C_NEED_BY_DATE_rec.header_id
1116 , p_component_id =>
1117 C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1118 , p_open_flag => C_NEED_BY_DATE_rec.open_flag
1122 , p_shipping_interfaced_flag =>
1119 , p_booked_flag => C_NEED_BY_DATE_rec.booked_flag
1120 , p_shipped_quantity => C_NEED_BY_DATE_rec.shipped_quantity
1121 , p_invoiced_quantity => C_NEED_BY_DATE_rec.invoiced_quantity
1123 C_NEED_BY_DATE_rec.shipping_interfaced_flag
1124 , p_cancelled_flag =>
1125 C_NEED_BY_DATE_rec.cancelled_flag
1126 );
1127
1128 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1130 , G_MODULE_PREFIX ||
1131 'rajesh Out of check_repl_retain => '
1132 , l_retain );
1133 END IF;
1134
1135 IF NVL(l_retain,'Y') = 'N'
1136 THEN
1137 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1138 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1139 , G_MODULE_PREFIX ||
1140 'Call Delete_Allocations for repl SO LINE ID '||
1141 C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1142 , C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1143 END IF;
1144
1145 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1146 ( p_api_version => 1.0
1147 , p_init_msg_list => NULL
1148 , x_return_status => l_return_status
1149 , x_msg_count => l_msg_count
1150 , x_msg_data => l_msg_data
1151 , p_subcontract_po_shipment_id =>
1152 C_NEED_BY_DATE_rec.subcontract_po_shipment_id
1153 , p_component_id =>
1154 C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1155 , p_replen_so_line_id =>
1156 C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1157 , x_deleted_allocations_tbl =>
1158 l_deleted_allocations_tbl
1159 );
1160
1161 l_component_removed_qty := NVL(l_component_removed_qty,0) +
1162 C_NEED_BY_DATE_rec.allocated_quantity ;
1163
1164 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1165 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1166 , G_MODULE_PREFIX ||
1167 'C_NEED_BY_DATE_rec.allocated_quantity => '
1168 , C_NEED_BY_DATE_rec.allocated_quantity );
1169 END IF;
1170 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1171 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1172 , G_MODULE_PREFIX ||
1173 'l_component_removed_qty => '
1174 , l_component_removed_qty );
1175 END IF;
1176
1177 -- END IF; ---l_final_ship_date
1178
1179 ELSE
1180 -- Update the existing repl
1181 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1182 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1183 , G_MODULE_PREFIX ||
1184 'Into the Retain = Y mode reschedule repl '
1185 , C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1186 END IF;
1187 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1188 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1189 , G_MODULE_PREFIX ||
1190 'Calling JMF_SHIKYU_ONT_PVT.Calculate_Ship_Date for '
1191 , l_allocation_date );
1192 END IF;
1193
1194
1195 l_reschedule_date := NULL ;
1196 JMF_SHIKYU_ONT_PVT.Calculate_Ship_Date
1197 ( p_subcontract_po_shipment_id =>
1198 p_subcontract_po_shipment_id
1199 , p_component_item_id =>
1200 C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1201 , p_oem_organization_id =>
1202 p_oem_organization_id
1203 , p_tp_organization_id =>
1204 p_tp_organization_id
1205 , p_quantity =>
1206 C_NEED_BY_DATE_rec.allocated_quantity
1207 , p_need_by_date => l_allocation_date
1208 , x_ship_date => l_reschedule_date );
1209
1210
1211 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1212 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1213 , G_MODULE_PREFIX ||
1214 'rajesh l_reschedule_date => '|| l_reschedule_date
1215 , l_reschedule_date );
1216 END IF;
1217
1218
1219 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1220 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1221 , G_MODULE_PREFIX ||
1222 'Call update_replenishment_date for SO LINE ID '||
1223 C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1224 , C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);
1225 END IF;
1226
1227 update_replenishment_date
1228 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1229 , p_oem_organization => p_oem_organization_id
1230 , p_tp_organization => p_tp_organization_id
1231 , p_replen_so_line_id => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
1232 , p_replen_so_header_id => C_NEED_BY_DATE_rec.header_id
1233 , p_component_id =>
1234 C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
1235 , p_new_ship_date => l_reschedule_date
1236 , p_allocation_date => l_allocation_date
1237 );
1238
1239 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1240 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1241 , G_MODULE_PREFIX ||
1242 'Out of update_replenishment_date '
1243 , 'rajesh');
1244 END IF;
1245
1246 END IF; -- end of reschedule IF
1250 END LOOP ;
1247 --dbms_output.put_line(' Next in loop ');
1248 END IF ; -- l_final_ship_date
1249
1251
1252 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1253 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1254 , G_MODULE_PREFIX ||
1255 'out of components loop rem qty: '|| l_component_removed_qty
1256 , l_component_removed_qty);
1257 END IF;
1258
1259 IF NVL(l_component_removed_qty,0) > 0
1260 THEN
1261 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1262 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1263 , G_MODULE_PREFIX ||
1264 'Going to call Create new allocations for comp'
1265 , l_SHIKYU_component_id );
1266 END IF;
1267
1268 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations
1269 ( p_api_version => 1.0
1270 , p_init_msg_list => NULL
1271 , x_return_status => l_return_status
1272 , x_msg_count => l_msg_count
1273 , x_msg_data => l_msg_data
1274 , p_subcontract_po_shipment_id =>
1275 p_subcontract_po_shipment_id
1276 , p_component_id =>
1277 l_SHIKYU_component_id
1278 , p_qty =>
1279 l_component_removed_qty
1280 , p_skip_po_replen_creation => 'N'
1281 );
1282
1283 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1284 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1285 , G_MODULE_PREFIX ||
1286 'out of Create new allocations for comp'
1287 , l_return_status );
1288 END IF;
1289 END IF ; -- l_component_removed_qty IF
1290
1291
1292 l_component_removed_qty := NULL ;
1293 l_retain := NULL ;
1294 l_SHIKYU_component_id := NULL ;
1295
1296 END LOOP ; -- Main loop ;
1297 --dbms_output.put_line(' out of loop ');
1298 END IF;
1299
1300
1301 IF (g_fnd_debug = 'Y')
1302 THEN
1303 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1305 , G_MODULE_PREFIX ||
1306 'Process_Date_Changes.OUT'
1307 , 'Entry');
1308 END IF;
1309 END IF;
1310
1311 --dbms_output.put_line(' out of Process_Date_Changes ');
1312 END Process_Date_Changes ;
1313
1314
1315 --=============================================================================
1316 -- API NAME : Process_SHIKYU_Reconciliation
1317 -- TYPE : PRIVATE
1318 -- PRE-REQS : SHIKYU datamodel and SHIKYU process should exists
1319 -- DESCRIPTION : Process the SHIKYi reconciliation once the shikyu
1320 -- Interlock has been run
1321
1322 -- PARAMETERS :
1323 -- p_api_version REQUIRED. API version
1324 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message list
1325 -- FND_API.G_FALSE to not reset it.
1326 -- If pass NULL, it means FND_API.G_FALSE.
1327 -- p_commit OPTIONAL. FND_API.G_TRUE to have API commit the change
1328 -- FND_API.G_FALSE to not commit the change.
1329 -- Include this if API does DML.
1330 -- If pass NULL, it means FND_API.G_FALSE.
1331 -- p_validation_level OPTIONAL. value between 0 and 100.
1332 -- FND_API.G_VALID_LEVEL_NONE -> 0
1333 -- FND_API.G_VALID_LEVEL_FULL -> 100
1334 -- Public APIs should not have this parameter
1335 -- since it should always be FULL validation.
1336 -- If API perform some level not required by
1337 -- some API caller, this parameter should be
1338 -- included.
1339 -- Product group can define intermediate
1340 -- validation levels.
1341 -- If pass NULL, it means i
1342 -- FND_API.G_VALID_LEVEL_FULL
1343 --
1344 -- x_return_status REQUIRED. Value can be
1345 -- FND_API.G_RET_STS_SUCCESS
1346 -- FND_API.G_RET_STS_ERROR
1347 -- FND_API.G_RET_STS_UNEXP_ERROR
1348 -- x_msg_count REQUIRED. Number of messages on the message list
1349 -- x_msg_data REQUIRED. Return message data if message count is 1
1350 -- p_card_id REQUIRED. Card ID to be deleted.
1351 -- EXCEPTIONS :
1352 --
1353 --=============================================================================
1354 PROCEDURE Process_SHIKYU_Reconciliation
1355 ( p_api_version IN NUMBER
1356 , p_init_msg_list IN VARCHAR2
1357 , p_commit IN VARCHAR2
1358 , p_validation_level IN NUMBER
1359 , x_return_status OUT NOCOPY VARCHAR2
1360 , x_msg_count OUT NOCOPY NUMBER
1361 , x_msg_data OUT NOCOPY VARCHAR2
1362 , P_Operating_unit IN NUMBER
1363 , p_from_organization IN NUMBER
1364 , p_to_organization IN NUMBER
1365 )
1366 IS
1367 l_api_name CONSTANT VARCHAR2(30) := 'Process_SHIKYU_Reconciliation' ;
1368 l_api_version CONSTANT NUMBER := 1.0;
1369 l_deleted_qty NUMBER ;
1370
1371
1372 CURSOR SHIKYU_reconcile_CSR IS
1373 SELECT sco.SUBCONTRACT_PO_SHIPMENT_ID SUBCONTRACT_PO_SHIPMENT_ID
1374 , sco.SUBCONTRACT_PO_HEADER_ID SUBCONTRACT_PO_HEADER_ID
1378 , sco.CURRENCY CURRENCY
1375 , sco.SUBCONTRACT_PO_LINE_ID SUBCONTRACT_PO_LINE_ID
1376 , trunc(sco.NEED_BY_DATE) sco_NEED_BY_DATE
1377 , sco.UOM UOM
1379 , sco.OEM_ORGANIZATION_ID OEM_ORGANIZATION_ID
1380 , sco.TP_ORGANIZATION_ID TP_ORGANIZATION_ID
1381 , sco.WIP_ENTITY_ID WIP_ENTITY_ID
1382 , sco.OSA_ITEM_ID OSA_ITEM_ID
1383 , wdj.start_quantity start_quantity
1384 , NVL(trunc(poll.need_by_date), trunc(poll.promised_date))
1385 pol_need_by_date
1386 , poll.quantity quantity
1387 , sco.quantity old_ordered_quantity
1388 , poll.unit_meas_lookup_code purchasing_uom
1389 , pol.cancel_flag pol_cancel_flag
1390 , poh.cancel_flag poh_cancel_flag
1391 , poll.cancel_flag poll_cancel_flag
1392 , trunc( wdj.SCHEDULED_START_DATE) SCHEDULED_START_DATE
1393 FROM JMF_SUBCONTRACT_ORDERS sco
1394 , po_headers_all poh
1395 , po_lines_all pol
1396 , po_line_locations_all poll
1397 , wip_discrete_jobs wdj
1398 WHERE pol.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
1399 and pol.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
1400 and poll.po_line_id = pol.po_line_id
1401 and poll.line_location_id = sco.SUBCONTRACT_PO_SHIPMENT_ID
1402 and wdj.wip_entity_id = sco.WIP_ENTITY_ID
1403 and sco.interlock_status = 'P'
1404 and ( trunc(sco.NEED_BY_DATE) <>
1405 NVL(trunc(poll.need_by_date), trunc(poll.promised_date)) OR
1406 sco.quantity <> poll.quantity OR
1407 pol.cancel_flag = 'Y' OR
1408 poh.cancel_flag = 'Y' OR
1409 poll.cancel_flag = 'Y' )
1410 and poh.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
1411 and pol.po_header_id = poh.po_header_id
1412 ORDER BY sco.subcontract_po_shipment_id ;
1413
1414 l_SUBCONTRACT_PO_SHIPMENT_ID NUMBER ;
1415
1416 CURSOR C_shikyu_cancel_comp_CSR IS
1417 SELECT SUBCONTRACT_PO_SHIPMENT_ID
1418 , SHIKYU_COMPONENT_ID
1419 , OEM_ORGANIZATION_ID
1420 , SHIKYU_COMPONENT_PRICE
1421 , PRIMARY_UOM
1422 FROM JMF_SHIKYU_COMPONENTS
1423 WHERE SUBCONTRACT_PO_SHIPMENT_ID = l_subcontract_po_shipment_id ;
1424
1425 l_used_quantity NUMBER;
1426
1427 l_deleted_allocations_tbl
1428 JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;
1429 l_msg_data VARCHAR2(300);
1430 l_msg_count NUMBER ;
1431 l_return_status VARCHAR2(3);
1432 l_primary_quantity NUMBER ;
1433
1434 BEGIN
1435
1436 --- First step : calean up invalid data
1437 -- second: reconcile partial transactions
1438 -- Main api for the reconciliation process
1439 -- select the subcontract records where either DATES or qty has been
1440 --changed after interlock OR the PO shipment cancelled
1441
1442 -- IF PO shipment cancelled :
1443 -- cancel the WIP job
1444 -- remove all the allocations for the qty that have not been
1445 -- issued to WIP job yet.
1446
1447 -- IF not cancelled, but date or qty changed:
1448
1449 -- process the date changes
1450 -- process the qty changes
1451
1452 -- update the JMF subcontract table with the new values
1453 -- COMMIT
1454
1455 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1456 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1457 , 'JMFVSKRB' ||
1458 'Process_SHIKYU_Reconciliation.invoked'
1459 , 'Entry');
1460 END IF;
1461
1462
1463 -- Start API initialization
1464
1465 FND_MSG_PUB.initialize;
1466
1467
1468 x_return_status := FND_API.G_RET_STS_SUCCESS;
1469 -- End API initialization
1470
1471 -- Select the eligible data for Reconciliation
1472
1473 -- Process the changes due to PO subcontract order NEED BY DATE Only
1474 -- Process the change due to the PO subcontract QTY changes Only
1475 -- Process changes due to both PO subcontract need by date and qty changes
1476
1477 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1478 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1479 , 'JMFVSKRB' ||
1480 'calling JMF_SHIKYU_UTIL.clean_invalid_data '
1481 , 'out cleanup' );
1482 END IF;
1483
1484 JMF_SHIKYU_UTIL.clean_invalid_data ;
1485
1486 COMMIT;
1487
1488 -- dbms_output.put_line(' Calling Reconcile_Partial_Shipments ');
1489 IF (g_fnd_debug = 'Y')
1490 THEN
1491 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1493 , 'JMFVSKRB' ||
1494 'calling JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Partial_Shipments'
1495 , 'rajesh');
1496 END IF;
1497 END IF;
1498
1499 BEGIN
1500 JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Partial_Shipments
1501 ( p_api_version => 1.0
1502 , p_init_msg_list => 'Y'
1503 , x_return_status => l_return_status
1504 , x_msg_count => l_msg_count
1505 , x_msg_data => l_msg_data
1506 , p_from_organization => p_from_organization
1507 , p_to_organization => p_to_organization
1508 );
1509
1510 COMMIT;
1511
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 ROLLBACK;
1515 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1516 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1517 , G_MODULE_PREFIX ||
1518 'JMFVSKRB: OUT OF Reconcile_Partial_Shipments -- EXCEPTION => '
1519 , l_return_status);
1523
1520 END IF;
1521
1522 END ;
1524 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1526 , G_MODULE_PREFIX ||
1527 'JMFVSKRB: continue after Reconcile_Partial_Shipments '
1528 , l_return_status);
1529 END IF;
1530
1531 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1532 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1533 , 'x_msg_data :: '|| x_msg_data
1534 , x_msg_data);
1535 END IF;
1536
1537
1538 l_return_status := NULL;
1539
1540 -- dbms_output.put_line(' About to open cursor SHIKYU_reconcile_CSR');
1541 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1542 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1543 , 'About to open cursor SHIKYU_reconcile_CSR'
1544 , 'JMFVSKRB' );
1545 END IF;
1546
1547 FOR SHIKYU_reconcile_rec IN SHIKYU_reconcile_CSR
1548 LOOP
1549 BEGIN
1550 l_subcontract_po_shipment_id :=
1551 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID;
1552
1553 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1554 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1555 , 'JMFVSKRB ' ||
1556 'INTO CURSOR SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID => '
1557 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1558 END IF;
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1562 , 'JMFVSKRB ' ||
1563 'l_subcontract_po_shipment_id => '
1564 , l_subcontract_po_shipment_id);
1565 END IF;
1566
1567 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1569 , 'JMFVSKRB: ' ||
1570 'SHIKYU_reconcile_rec.pol_need_by_date => '
1571 , SHIKYU_reconcile_rec.pol_need_by_date);
1572 END IF;
1573 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1574 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1575 , 'JMFVSKRB: ' ||
1576 'SHIKYU_reconcile_rec.sco_NEED_BY_DATE => '
1577 , SHIKYU_reconcile_rec.sco_NEED_BY_DATE);
1578 END IF;
1579
1580 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1581 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1582 , 'JMFVSKRB: ' ||
1583 'SHIKYU_reconcile_rec.old_ordered_quantity =>'
1584 , SHIKYU_reconcile_rec.old_ordered_quantity);
1585 END IF;
1586
1587 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1588 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1589 , 'JMFVSKRB: ' ||
1590 'SHIKYU_reconcile_rec.quantity => '
1591 , SHIKYU_reconcile_rec.quantity);
1592 END IF;
1593 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1594 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1595 , 'JMFVSKRB: ' ||
1596 'SHIKYU_reconcile_rec.pol_cancel_flag => '
1597 , SHIKYU_reconcile_rec.pol_cancel_flag);
1598 END IF;
1599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1601 , 'JMFVSKRB: ' ||
1602 'SHIKYU_reconcile_rec.poll_cancel_flag => '
1603 , SHIKYU_reconcile_rec.poll_cancel_flag);
1604 END IF;
1605 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1606 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1607 , 'JMFVSKRB: ' ||
1608 'SHIKYU_reconcile_rec.poh_cancel_flag => '
1609 , SHIKYU_reconcile_rec.poh_cancel_flag);
1610 END IF;
1611 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1612 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1613 , 'JMFVSKRB: ' ||
1614 'SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID => '
1615 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID);
1616 END IF;
1617 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1618 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1619 , 'JMFVSKRB: ' ||
1620 'SHIKYU_reconcile_rec.scheduled_start_dat => '
1621 , SHIKYU_reconcile_rec.scheduled_start_date);
1622 END IF;
1623
1624
1625 IF SHIKYU_reconcile_rec.pol_cancel_flag = 'Y' OR
1626 SHIKYU_reconcile_rec.poh_cancel_flag = 'Y' OR
1627 SHIKYU_reconcile_rec.poll_cancel_flag = 'Y'
1628 THEN
1629 BEGIN
1630 -- Cancel WIP Jobs
1631 -- Remove all the allocations
1632 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1633 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1634 , 'JMFVSKRB: ' ||
1635 'INTO CANCEL for SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID '
1636 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1637 END IF;
1638
1639
1640 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1641 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1642 , 'JMFVSKRB: ' ||
1643 'Calling JMF_SHIKYU_WIP_PVT.Process_WIP_Job D for SCO '
1644 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1645 END IF;
1646
1647 JMF_SHIKYU_WIP_PVT.Process_WIP_Job
1648 ( p_action => 'D'
1649 , p_SUBCONTRACT_PO_SHIPMENT_ID =>
1650 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1651 , p_need_by_date => NULL
1652 , p_quantity => NULL
1653 , x_return_status => l_return_status
1657 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1654 ) ;
1655
1656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1658 , 'JMFVSKRB: ' ||
1659 'out JMF_SHIKYU_WIP_PVT.Process_WIP_Job ' ||
1660 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1661 ,' l_return_status => '|| l_return_status );
1662 END IF;
1663
1664 --COMMIT;
1665
1666 IF SHIKYU_reconcile_rec.scheduled_start_date > SYSDATE
1667 THEN
1668 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1669 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1670 , 'JMFVSKRB: ' ||
1671 ' SHIKYU_reconcile_rec.scheduled_start_date => '||
1672 SHIKYU_reconcile_rec.scheduled_start_date
1673 ,' Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations ');
1674 END IF;
1675
1676 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1677 ( p_api_version => 1.0
1678 , p_init_msg_list => NULL
1679 , x_return_status => l_return_status
1680 , x_msg_count => l_msg_count
1681 , x_msg_data => l_msg_data
1682 , p_subcontract_po_shipment_id =>
1683 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1684 , p_component_id => NULL
1685 , p_replen_so_line_id => NULL
1686 , x_deleted_allocations_tbl =>
1687 l_deleted_allocations_tbl
1688 );
1689
1690 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1691 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1692 , 'JMFVSKRB ' ||
1693 'out JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations '
1694 ,' l_return_status => '|| l_return_status );
1695 END IF;
1696
1697 ELSE
1698 -- dbms_output.put_line(' Cancel components ');
1699 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1700 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1701 , 'JMFVSKRB: ' ||
1702 ' INTO Cancel components SCO SHIPMENT '
1703 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1704 END IF;
1705
1706 l_used_quantity := NULL;
1707 FOR C_shikyu_cancel_comp_rec IN C_shikyu_cancel_comp_CSR
1708 LOOP
1709 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1710 THEN
1711 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1712 , 'JMFVSKRB: ' ||
1713 ' CURSOR shikyu_cancel_comp_rec.shikyu_component_id => '
1714 ,c_shikyu_cancel_comp_rec.shikyu_component_id );
1715 END IF;
1716
1717 l_used_quantity := 0 ;
1718
1719 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1720 THEN
1721 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1722 , 'JMFVSKRB: ' ||
1723 'jmf about invoke JMF_SHIKYU_UTIL.GET_used_quantity for '
1724 ,C_shikyu_cancel_comp_rec.shikyu_component_id );
1725 END IF;
1726
1727 l_used_quantity :=
1728 JMF_SHIKYU_UTIL.GET_used_quantity
1729 ( p_wip_entity_id =>
1730 SHIKYU_reconcile_rec.wip_entity_id
1731 , p_shikyu_component_id =>
1732 C_shikyu_cancel_comp_rec.shikyu_component_id
1733 , p_organization_id =>
1734 SHIKYU_reconcile_rec.oem_organization_id
1735 );
1736
1737 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1738 THEN
1739 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1740 , 'JMFVSKRB: ' ||
1741 ' JMF l_used_quantity => '
1742 ,l_used_quantity );
1743 END IF;
1744
1745
1746 IF NVL(l_used_quantity,0) <= 0
1747 THEN
1748 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1749 THEN
1750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1751 , 'JMFVSKRB: ' ||
1752 ' Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations '
1753 ,C_shikyu_cancel_comp_rec.shikyu_component_id );
1754 END IF;
1755
1756 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
1757 ( p_api_version => 1.0
1758 , p_init_msg_list => NULL
1759 , x_return_status => l_return_status
1760 , x_msg_count => l_msg_count
1761 , x_msg_data => l_msg_data
1762 , p_subcontract_po_shipment_id =>
1763 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1764 , p_component_id =>
1765 C_shikyu_cancel_comp_rec.shikyu_component_id
1766 , p_replen_so_line_id => NULL
1767 , x_deleted_allocations_tbl =>
1768 l_deleted_allocations_tbl
1769 );
1770 END IF;
1771 END LOOP;
1772 END IF ; -- Greater than start date
1773
1774 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1775 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1776 , 'JMFVSKRB' ||
1777 'JMFVSKRB: UPDATE and COMMIT for SCO PO CANCEL flow '
1778 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1779 END IF;
1780
1781
1785 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1782 UPDATE JMF_SUBCONTRACT_ORDERS
1783 SET interlock_status = 'T'
1784 WHERE SUBCONTRACT_PO_SHIPMENT_ID =
1786 and ( SHIKYU_reconcile_rec.pol_cancel_flag = 'Y' OR
1787 SHIKYU_reconcile_rec.poh_cancel_flag = 'Y' OR
1788 SHIKYU_reconcile_rec.poll_cancel_flag = 'Y' );
1789
1790
1791 COMMIT;
1792
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 BEGIN
1796 ROLLBACK ;
1797
1798 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1799 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1800 , 'JMFVSKRB' ||
1801 'JMFVSKRB: EXCEPTION 1 in CANCEL FLOW '
1802 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1803 END IF;
1804 END ;
1805
1806
1807 END ;
1808 ELSE -- NOT cancel logic
1809 l_return_status := NULL ;
1810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1812 , 'JMFVSKRB ' ||
1813 'INto NON cancel logic '
1814 ,' l_return_status => '|| l_return_status );
1815 END IF;
1816 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1817 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1818 , 'JMFVSKRB ' ||
1819 'SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID => '
1820 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID);
1821 END IF;
1822
1823 l_primary_quantity := NULL;
1824 l_primary_quantity := JMF_SHIKYU_UTIL.get_prImary_quantity
1825 ( p_purchasing_UOM =>
1826 SHIKYU_reconcile_rec.purchasing_UOM
1827 , p_quantity =>
1828 SHIKYU_reconcile_rec.quantity
1829 , P_inventory_org_id => SHIKYU_reconcile_rec.oem_organization_id
1830 , p_inventory_item_id => SHIKYU_reconcile_rec.OSA_ITEM_ID ) ;
1831
1832
1833 -- Update the WIP jobs and update the JMF table
1834
1835 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1836 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1837 , 'JMFVSKRB ' ||
1838 'Calling JMF_SHIKYU_WIP_PVT.Process_WIP_Job U for SCO '
1839 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1840 END IF;
1841
1842 JMF_SHIKYU_WIP_PVT.Process_WIP_Job
1843 ( p_action => 'U'
1844 , p_SUBCONTRACT_PO_SHIPMENT_ID =>
1845 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1846 , p_need_by_date =>
1847 SHIKYU_reconcile_rec.pol_need_by_date
1848 , p_quantity => l_primary_quantity
1849 , x_return_status => l_return_status
1850 ) ;
1851
1852 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1853 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1854 , 'JMFVSKRB ' ||
1855 'out JMF_SHIKYU_WIP_PVT.Process_WIP_Job with '
1856 ,l_return_status );
1857 END IF;
1858
1859 IF l_return_status = 'S'
1860 THEN
1861 -- changes IF
1862 IF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) <>
1863 TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1864 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) =
1865 ( SHIKYU_reconcile_rec.quantity )
1866 THEN
1867
1868 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1869 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1870 , 'JMFVSKRB ' ||
1871 'Calling Process_Date_Changes '
1872 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1873 END IF;
1874
1875 Process_Date_Changes
1876 ( p_SUBCONTRACT_PO_SHIPMENT_ID =>
1877 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1878 , p_SUBCONTRACT_PO_HEADER_ID =>
1879 SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1880 , p_SUBCONTRACT_PO_LINE_ID =>
1881 SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1882 , p_OLD_NEED_BY_DATE =>
1883 SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1884 , p_UOM =>
1885 SHIKYU_reconcile_rec.UOM
1886 , p_CURRENCY =>
1887 SHIKYU_reconcile_rec.CURRENCY
1888 , p_OEM_ORGANIZATION_ID =>
1889 SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1890 , p_TP_ORGANIZATION_ID =>
1891 SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1892 , p_WIP_ENTITY_ID =>
1893 SHIKYU_reconcile_rec.WIP_ENTITY_ID
1894 , p_OSA_ITEM_ID =>
1895 SHIKYU_reconcile_rec.OSA_ITEM_ID
1896 , p_wip_start_quantity =>
1897 SHIKYU_reconcile_rec.start_quantity
1898 , p_new_need_by_date =>
1899 SHIKYU_reconcile_rec.pol_need_by_date
1900 , p_new_ordered_quantity =>
1901 SHIKYU_reconcile_rec.quantity
1902 , p_old_ordered_quantity =>
1903 SHIKYU_reconcile_rec.old_ordered_quantity
1904 , p_puchasing_UOM =>
1905 SHIKYU_reconcile_rec.purchasing_uom
1906 ) ;
1907
1908
1912 ( SHIKYU_reconcile_rec.quantity )
1909 ELSIF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) =
1910 TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1911 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) <>
1913 THEN
1914 -- Process Qty changes
1915
1916 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1917 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1918 , 'JMFVSKRB ' ||
1919 'Calling Process_Quantity_Changes '
1920 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1921 END IF;
1922
1923 Process_Quantity_Changes
1924 ( p_SUBCONTRACT_PO_SHIPMENT_ID =>
1925 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1926 , p_SUBCONTRACT_PO_HEADER_ID =>
1927 SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1928 , p_SUBCONTRACT_PO_LINE_ID =>
1929 SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1930 , p_OLD_NEED_BY_DATE =>
1931 SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1932 , p_UOM =>
1933 SHIKYU_reconcile_rec.UOM
1934 , p_CURRENCY =>
1935 SHIKYU_reconcile_rec.CURRENCY
1936 , p_OEM_ORGANIZATION_ID =>
1937 SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1938 , p_TP_ORGANIZATION_ID =>
1939 SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1940 , p_WIP_ENTITY_ID =>
1941 SHIKYU_reconcile_rec.WIP_ENTITY_ID
1942 , p_OSA_ITEM_ID =>
1943 SHIKYU_reconcile_rec.OSA_ITEM_ID
1944 , p_wip_start_quantity =>
1945 SHIKYU_reconcile_rec.start_quantity
1946 , p_new_need_by_date =>
1947 SHIKYU_reconcile_rec.pol_need_by_date
1948 , p_new_ordered_quantity =>
1949 SHIKYU_reconcile_rec.quantity
1950 , p_old_ordered_quantity =>
1951 SHIKYU_reconcile_rec.old_ordered_quantity
1952 , p_puchasing_UOM =>
1953 SHIKYU_reconcile_rec.purchasing_uom
1954 ) ;
1955
1956
1957 ELSIF ( TRUNC( SHIKYU_reconcile_rec.pol_need_by_date ) <>
1958 TRUNC(SHIKYU_reconcile_rec.sco_need_by_date) )
1959 AND ( SHIKYU_reconcile_rec.old_ordered_quantity ) <>
1960 ( SHIKYU_reconcile_rec.quantity )
1961 THEN
1962
1963 IF (FND_LOG.LEVEL_PROCEDURE >=
1964 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1965 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1966 , 'JMFVSKRB ' ||
1967 'Calling Process_Date_Changes '
1968 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
1969 END IF;
1970
1971 Process_Date_Changes
1972 ( p_SUBCONTRACT_PO_SHIPMENT_ID =>
1973 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
1974 , p_SUBCONTRACT_PO_HEADER_ID =>
1975 SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
1976 , p_SUBCONTRACT_PO_LINE_ID =>
1977 SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
1978 , p_OLD_NEED_BY_DATE =>
1979 SHIKYU_reconcile_rec.sco_NEED_BY_DATE
1980 , p_UOM =>
1981 SHIKYU_reconcile_rec.UOM
1982 , p_CURRENCY =>
1983 SHIKYU_reconcile_rec.CURRENCY
1984 , p_OEM_ORGANIZATION_ID =>
1985 SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
1986 , p_TP_ORGANIZATION_ID =>
1987 SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
1988 , p_WIP_ENTITY_ID =>
1989 SHIKYU_reconcile_rec.WIP_ENTITY_ID
1990 , p_OSA_ITEM_ID =>
1991 SHIKYU_reconcile_rec.OSA_ITEM_ID
1992 , p_wip_start_quantity =>
1993 SHIKYU_reconcile_rec.start_quantity
1994 , p_new_need_by_date =>
1995 SHIKYU_reconcile_rec.pol_need_by_date
1996 , p_new_ordered_quantity =>
1997 SHIKYU_reconcile_rec.quantity
1998 , p_old_ordered_quantity =>
1999 SHIKYU_reconcile_rec.old_ordered_quantity
2000 , p_puchasing_UOM =>
2001 SHIKYU_reconcile_rec.purchasing_uom
2002 ) ;
2003
2004
2005 Process_Quantity_Changes
2006 ( p_SUBCONTRACT_PO_SHIPMENT_ID =>
2007 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
2008 , p_SUBCONTRACT_PO_HEADER_ID =>
2009 SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
2010 , p_SUBCONTRACT_PO_LINE_ID =>
2011 SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
2012 , p_OLD_NEED_BY_DATE =>
2013 SHIKYU_reconcile_rec.sco_NEED_BY_DATE
2014 , p_UOM =>
2015 SHIKYU_reconcile_rec.UOM
2016 , p_CURRENCY =>
2017 SHIKYU_reconcile_rec.CURRENCY
2018 , p_OEM_ORGANIZATION_ID =>
2019 SHIKYU_reconcile_rec.OEM_ORGANIZATION_ID
2020 , p_TP_ORGANIZATION_ID =>
2021 SHIKYU_reconcile_rec.tp_ORGANIZATION_ID
2022 , p_WIP_ENTITY_ID =>
2023 SHIKYU_reconcile_rec.WIP_ENTITY_ID
2024 , p_OSA_ITEM_ID =>
2025 SHIKYU_reconcile_rec.OSA_ITEM_ID
2026 , p_wip_start_quantity =>
2027 SHIKYU_reconcile_rec.start_quantity
2028 , p_new_need_by_date =>
2029 SHIKYU_reconcile_rec.pol_need_by_date
2030 , p_new_ordered_quantity =>
2031 SHIKYU_reconcile_rec.quantity
2032 , p_old_ordered_quantity =>
2033 SHIKYU_reconcile_rec.old_ordered_quantity
2034 , p_puchasing_UOM =>
2035 SHIKYU_reconcile_rec.purchasing_uom
2036 ) ;
2037
2038
2039 END IF; -- changes IF
2040
2041 IF (FND_LOG.LEVEL_PROCEDURE >=
2042 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2043 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2044 , 'JMFVRKSB: '||
2045 'UPDATE JMF_SUBCONTRACT_ORDERS '
2046 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
2047 END IF;
2048
2049
2050 UPDATE JMF_SUBCONTRACT_ORDERS
2051 SET quantity = SHIKYU_reconcile_rec.quantity
2052 , need_by_date = SHIKYU_reconcile_rec.pol_need_by_date
2053 , last_update_date = sysdate
2054 , last_updated_by = FND_GLOBAL.user_id
2055 , last_update_login = FND_GLOBAL.login_id
2056 WHERE SUBCONTRACT_PO_HEADER_ID =
2057 SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
2058 and SUBCONTRACT_PO_LINE_ID =
2059 SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
2060 and SUBCONTRACT_PO_SHIPMENT_ID =
2061 SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID ;
2062
2063 COMMIT;
2064
2065 IF (FND_LOG.LEVEL_PROCEDURE >=
2066 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2068 , 'JMFVRKSB: '||
2069 'AFTER UPDATE JMF_SUBCONTRACT_ORDERS '
2070 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
2071 END IF;
2072
2073
2074 ELSE -- return status
2075 ROLLBACK;
2076 END IF; -- WIP job
2077 END IF; -- soc status
2078
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 ROLLBACK ;
2082 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2083 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2084 , 'JMFVSKRB: '||
2085 ' MAIN LOOP exception for SCO shipment '
2086 , SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
2087 END IF;
2088 END ;
2089
2090 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2091 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2092 , 'JMFVSKRB: ' ||
2093 ' Done processing SCO shipment => '
2094 ,SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );
2095 END IF;
2096
2097 END LOOP ; -- Main Loop
2098
2099 -- dbms_output.put_line(' Out of Main loop ');
2100
2101 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2102 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2103 , 'JMFVSKRB: Process_SHIKYU_Reconciliation. OUT'
2104 , 'OUT');
2105 END IF;
2106 --dbms_output.put_line(' OUT of Process_SHIKYU_Reconciliation. ');
2107 EXCEPTION
2108 WHEN FND_API.G_EXC_ERROR THEN
2109 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_ERROR ');
2110 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
2111 ROLLBACK;
2112 FND_MSG_PUB.Count_And_Get
2113 ( p_count => x_msg_count
2114 , p_data => x_msg_data
2115 );
2116 x_return_status := FND_API.g_ret_sts_error;
2117
2118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2119 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_UNEXPECTED_ERROR ');
2120 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
2121 ROLLBACK ;
2122 FND_MSG_PUB.Count_And_Get
2123 ( p_count => x_msg_count
2124 , p_data => x_msg_data
2125 );
2126
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2128
2129 WHEN OTHERS THEN
2130 ROLLBACK ;
2131 FND_MSG_PUB.Count_And_Get
2132 ( p_count => x_msg_count
2133 , p_data => x_msg_data
2134 );
2135
2136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2137
2138 IF (g_fnd_debug = 'Y')
2139 THEN
2140 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2141 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2142 , G_MODULE_PREFIX || '.others_exception'
2143 , 'rajesh Exception');
2144 END IF;
2145 END IF;
2146
2147 END Process_SHIKYU_Reconciliation ;
2148
2149 END JMF_SHIKYU_RECONCILIAITON_PVT ;