[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_ALLOCATION_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_ALLOCATION_PVT AS
2 --$Header: JMFVSKAB.pls 120.29.12020000.3 2012/07/26 09:00:37 abhissri ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME: JMFVSKAB.pls |
10 --| |
11 --| DESCRIPTION: Package body of the Business Object API for the |
12 --| SHIKYU allocations. |
13 --| Allocations are associations between Subcontracting |
14 --| Components and Replenishment Sales Order Lines used |
15 --| to track the Replenishments consumed by the |
16 --| Subcontracting orders when manufacturing OSA items. |
17 --| |
18 --| This package contains procedures/functions to find |
19 --| available replenishments, and to create, decrease, |
20 --| delete allocations. |
21 --| |
22 --| PUBLIC FUNCTIONS/PROCEDURES: |
23 --| Allocate_Quantity |
24 --| Allocate_Quantity |
25 --| Get_Available_Replenishment_So |
26 --| Get_Available_Replenishment_Po |
27 --| Create_New_Replenishment_Po_So |
28 --| Create_New_Replenishment_So |
29 --| Create_New_Allocations |
30 --| Allocate_Prepositioned_Comp |
31 --| Allocate_Syncship_Comp |
32 --| Reduce_Allocations |
33 --| Delete_Allocations |
34 --| Reconcile_Partial_Shipments |
35 --| Reconcile_Closed_Shipments |
36 --| Reconcile_Replen_Excess_Qty |
37 --| |
38 --| PRIVATE FUNCTIONS/PROCEDURES: |
39 --| Get_Replen_So_Attributes |
40 --| Get_Allocation_Attributes |
41 --| Populate_Replenishment |
42 --| Validate_Price |
43 --| Validate_Project_Task_Ref |
44 --| Reduce_One_Allocation |
45 --| Initialize |
46 --| |
47 --| HISTORY: |
48 --| 19-MAY-2005 vchu Created. |
49 --| 07-JUL-2005 vchu Fixed GSCC errors. |
50 --| 01-AUG-2005 vchu Modified the Validate_Project_Task_Ref |
51 --| procedure to validate the project and task |
52 --| reference of a Replenishment Sales Order Line |
53 --| against the JMF_SUBCONTRACT_ORDERS record for |
54 --| the Subcontracting Order instead of the |
55 --| PO_LINES_ALL record. |
56 --| 03-AUG-2005 vchu Added the Initialize procedure to perform API |
57 --| Intialization. |
58 --| 04-AUG-2005 vchu Modified the Get_Available_Replenishment_So |
59 --| procedure: added a where clause condition to |
60 --| c_avail_replen_so_cur to only select the |
61 --| replenishments with remaining quantity > 0. |
62 --| 04-AUG-2005 vchu Added more in-line comments. Removed the |
63 --| commented code because of the removal of the |
64 --| uom parameters from most procedures. |
65 --| 18-AUG-2005 vchu Modified the where clause of the |
66 --| c_avail_replen_so_cur cursor to remove the |
67 --| calls to to_date. |
68 --| 29-SEP-2005 vchu Modified INSERT INTO JMF_SHIKYU_ALLOCATIONS |
69 --| statement in Allocate_Quantity to populate the |
70 --| UOM column with l_primary_uom instead of |
71 --| l_allocated_uom. |
72 --| 12-OCT-2005 vchu Modified the order by clause of the query for |
73 --| the c_avail_replen_po_cur cursor to create |
74 --| allocations in FIFO order of need-by-date, PO |
75 --| Number, Line Number and Shipment Number. |
76 --| 17-OCT-2005 vchu Modified calls to JMF_SHIKYU_ONT_PVT. |
77 --| Process_Replenishment_SO due to a |
78 --| change of signature. Also added the where |
79 --| clause condition "pha.approved_flag = 'Y'" to |
80 --| the query of the c_avail_replen_po_cur cursor |
81 --| in the Get_Available_Replenishment_Po procedure |
82 --| 26-OCT-2005 vchu Replaced dbms_output calls with FND_LOG calls. |
83 --| Also modified the value to populate into the |
84 --| TP_SUPPLIER_ID and TP_SUPPLIER_SITE_ID columns |
85 --| of the JMF_SHIKYU_REPLENISHMENTS table for |
86 --| fixing the wrong value issue of the |
87 --| Manufacturing Partner / MP site as described |
88 --| in bug 4651480. |
89 --| 09-NOV-2005 vchu Modified the query of the c_avail_replen_so_cur |
90 --| to order first by schedule_ship_date. Also |
91 --| modified the logic to set the value of the |
92 --| l_threshold_date local variable in the |
93 --| Get_Available_Replenishment_So procedure, in |
94 --| order to find available replenishment SOs to |
95 --| fulfill Subcontracting Orders with WIP jobs for |
96 --| which the scheduled completion date has passed. |
97 --| Also added additional FND Log messages to the |
98 --| Create_New_Allocations procedure. |
99 --| 18-NOV-2005 vchu Added the condition NVL(cancel_flag, 'N') = 'N' |
100 --| to the where clause of c_avail_replen_po_cur |
101 --| (for PO Header, Line and Line Location levels) |
102 --| in order to filter out the cancelled |
103 --| Replenishment POs. |
104 --| 14-FEB-2006 vchu Bug fix for 4997830: Added open cursor statement|
105 --| for c_subcontract_po_allocations in the |
106 --| Reconcile_Replen_Excess_Qty procedure. |
107 --| 27-MAR-2006 vchu Fixed bug 5090721: Set last_update_date, |
108 --| last_updated_by and last_update_login in the |
109 --| update statements. |
110 --| 21-APR-2006 vchu Modified the Reconcile_Partial_Shipments |
111 --| procedure (for bug 5166092): |
112 --| 1) Modified the INSERT statement for |
113 --| JMF_SHIKYU_REPLENISHMENTS to populate the |
114 --| additional_supply, primary_uom, and the primary |
115 --| uom quantity columns properly. |
116 --| 2) Added an UPDATE statement to update the |
117 --| allocable quantity of the parent Replenishment |
118 --| SO Line after reducing the quantity that was |
119 --| splitted into child SO Lines. |
120 --| 25_APR-2006 rajkrish Bug fix for 5166092: |
121 --| Partial shipments process. Changed the OM |
122 --| cursor fetching the child records. |
123 --| Added more debug log. |
124 --| 29-APR-2006 vchu Added a call to validate_price after calling |
125 --| Create_New_Replenishment_Po_So in |
126 --| Allocate_Syncship_Comp, to make sure that the |
127 --| price of the newly created Replenishment SO |
128 --| Line does match the price of the SHIKYU |
129 --| Component price. These two prices might be |
130 --| different if the sync-ship component of a |
131 --| Subcontracting Order didn't get allocated in |
132 --| the same Interlock run which loaded the |
133 --| Subcontracting Order itself, and the price of |
134 --| the component happened to have been changed on |
135 --| the price list. |
136 --| 02-MAY-2006 vchu Bug 5197415: Added the p_skip_po_replen_creation|
137 --| parameter to Create_New_Allocations and |
138 --| Allocate_Syncship_Comp, in order to give the |
139 --| option of skipping the creation of new |
140 --| Replenishment POs for sync-ship components. |
141 --| The Interlock Concurrent Program would use this |
142 --| option if creation of Replenishment SOs has |
143 --| already failed when trying to create |
144 --| Replenishment SOs for the Replenishment POs |
145 --| that do not yet present in the |
146 --| JMF_SHIKYU_REPLENISHMENTS table. |
147 --| 08-MAY-2006 vchu Modified validate_price to consider the uom |
148 --| column first before the primary uom column of |
149 --| the jmf_shikyu_components table. |
150 --| Also added debug log messages. |
151 --| 09-MAY-2006 vchu Added a call to Validate_Project_Task_Ref after |
152 --| calling Create_New_Replenishment_Po_So in |
153 --| Allocate_Syncship_Comp, to make sure that the |
154 --| project and task reference of the newly created |
155 --| Replenishment SO Line actually matches that of |
156 --| the Subcontracting Order Shipment. |
157 --| 10-MAY-2006 vchu Added a WHEN OTHERS THEN statement to the |
158 --| EXCEPTION block of all the procedures to print |
159 --| out the sqlerrm. Also replaced hardcoding of |
160 --| the 'S' status by FND_API.G_RET_STS_SUCCESS. |
161 --| 11-MAY-2006 vchu Modified Delete_Allocations to only update |
162 --| the allocated_quantity of the corresponding |
163 --| jmf_shikyu_replenishments record if there were |
164 --| indeed allocations being deleted. The wrong |
165 --| condition was used to check for an empty table |
166 --| before. Should check to see if |
167 --| x_deleted_allocations_tbl.FIRST is NULL or not |
168 --| Also modified various queries to get the |
169 --| promised_date from the PO_LINE_LOCATIONS_ALL |
170 --| table if need_by_date is NULL. |
171 --| 25-MAY-2006 rajkrish Added the NOT EXISTS caluse in the |
172 --| INSERT into JMF table in partial_reconcile |
173 --| 13-JUN-2006 vchu Bug fix for 5291292: |
174 --| Modified delete_allocations to reference the |
175 --| replenishment so line of the allocation |
176 --| currently being processed using the |
177 --| replenishment_so_line_id of the records of |
178 --| x_deleted_allocations_tbl from the RETURNING |
179 --| clause of the DELETE statements, instead of |
180 --| using the p_replen_so_line_id parameter. |
181 --| 27-JUN-2006 vchu Fixed the Reconcile_Replen_Excess_Qty procedure:|
182 --| 1) Modified the calculation to get the actual |
183 --| quantity to be reduced because of the excess |
184 --| quantity, which used to cause over deallocation |
185 --| 2) Set the return status to Y as long as the |
186 --| whole quantity to be reduced has been processed.|
187 --| The procedure used to return error if the |
188 --| Subcontracting Order that got deallocated |
189 --| cannot be reallocated because there are no more |
190 --| Replenishment Orders with available quantity. |
191 --| 06-AUG-2006 rajkrish Partial reconciliation changes. 5437721 |
192 --| Replace the child SO cursor with the |
193 --| CONNECT BY clause. |
194 --| 22-AUG-2006 vchu Bug fix for bug 5260244: Truncated the time |
195 --| component of the schedule_ship_date of a |
196 --| Replenishment Sales Order Line before adding |
197 --| the intransit lead time and comparing the sum |
198 --| to the threshold date. This is required for |
199 --| the case where the threshold date happens to |
200 --| fall on the same date as the schedule_ship_date,|
201 --| since Process Order API always defaults the |
202 --| time of the scheduled_ship_date to be 23:59:00. |
203 --| 20-SEP-2006 vchu Bug fix for bug 5510544: Modified the query of |
204 --| the cursor c_avail_replen_so_cur of the |
205 --| Get_Available_Replenishment_So procedure to |
206 --| recognize the already received so lines as |
207 --| available for allocation, in the case where |
208 --| p_arrived_so_lines_only = 'Y', which is |
209 --| typically used by the Adjustments Concurrent |
210 --| Program. |
211 --| 22-NOV-2006 vchu Bug fix for bug 5675563: Replaced the ocurrences|
212 --| of shipping_quantity_uom in the cursor query |
213 --| for c_avail_replen_so_cur in the procedure |
214 --| Get_Available_Replenishment_So with |
215 --| order_quantity_uom, since shipped_quantity is |
216 --| in order_quantity_uom. |
217 --| 04-OCT-2007 kdevadas 12.1 Buy/Sell Subcontracting changes |
218 --| Reference - GBL_BuySell_TDD.doc |
219 --| Reference - GBL_BuySell_FDD.doc |
220 --| 01-MAY-2008 kdevadas Bug 7000413 - In case of errors during |
221 --| allocation, the appropriate message is |
222 --| set and displayed in the request log |
223 --| 18-SEP-2008 rrajkule Bug 7383574-Changed cursor C_child_so_lines_CSR |
224 --| to have inline view to avoid FTS. |
225 --+===========================================================================+
226
227 --=============================================
228 -- CONSTANTS
229 --=============================================
230
231 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || G_PKG_NAME || '.';
232
233 --=============================================
234 -- GLOBAL VARIABLES
235 --=============================================
236
237 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
238
239 --=============================================
240 -- PRIVATE HELPER PROCEDURES/FUNCTIONS
241 --=============================================
242
243 PROCEDURE Get_Replen_So_Attributes
244 ( p_replen_so_line_id IN NUMBER
245 , x_header_id OUT NOCOPY NUMBER
246 , x_allocable_primary_uom_qty OUT NOCOPY NUMBER
247 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
248 , x_uom OUT NOCOPY VARCHAR2
249 , x_primary_uom OUT NOCOPY VARCHAR2
250 , x_replen_so_line_exists OUT NOCOPY VARCHAR2
251 );
252
253 PROCEDURE Get_Allocation_Attributes
254 ( p_subcontract_po_shipment_id IN NUMBER
255 , p_component_id IN NUMBER
256 , p_replen_so_line_id IN NUMBER
257 , x_allocated_qty OUT NOCOPY NUMBER
258 , x_uom OUT NOCOPY VARCHAR2
259 , x_allocation_exists OUT NOCOPY VARCHAR2
260 );
261
262 PROCEDURE Populate_Replenishment
263 ( p_replen_so_line_id IN NUMBER
264 , p_replen_po_shipment_id IN NUMBER
265 , p_component_id IN NUMBER
266 , p_oem_organization_id IN NUMBER
267 , p_tp_organization_id IN NUMBER
268 , p_primary_uom IN VARCHAR2
269 , p_primary_uom_qty IN NUMBER
270 , p_additional_supply IN VARCHAR2
271 );
272
273 FUNCTION Validate_Price
274 ( p_subcontract_po_shipment_id IN NUMBER
275 , p_component_id IN NUMBER
276 , p_replen_so_line_id IN NUMBER
277 )
278 RETURN BOOLEAN;
279
280 FUNCTION Validate_Project_Task_Ref
281 ( p_subcontract_po_shipment_id IN NUMBER
282 --, p_component_id IN NUMBER
283 , p_replen_so_line_id IN NUMBER
284 )
285 RETURN BOOLEAN;
286
287 PROCEDURE Reduce_One_Allocation
288 ( p_subcontract_po_shipment_id IN NUMBER
289 , p_component_id IN NUMBER
290 , p_replen_so_line_id IN NUMBER
291 , p_remain_qty_to_reduce IN NUMBER
292 , p_existing_alloc_qty IN NUMBER
293 , p_alloc_uom IN VARCHAR2
294 , x_reduced_allocations_rec OUT NOCOPY g_allocation_qty_rec_type
295 );
296
297 PROCEDURE Initialize
298 ( p_api_version IN NUMBER
299 , p_input_api_version IN NUMBER
300 , p_api_name IN VARCHAR2
301 , p_init_msg_list IN VARCHAR2
302 , x_return_status OUT NOCOPY VARCHAR2
303 );
304
305 --=============================================
306 -- PUBLIC PROCEDURES/FUNCTIONS
307 --=============================================
308
309 --=============================================================================
310 -- PROCEDURE NAME: Allocate_Quantity
311 -- TYPE : PUBLIC
312 -- PARAMETERS :
313 -- IN:
314 -- p_api_version Standard API parameter
315 -- p_init_msg_list Standard API parameter
316 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
317 -- p_component_id SHIKYU Component Identifier
318 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
319 -- p_primary_uom primary Unit Of Measure code of the
320 -- SHIKYU Component to be allocated
321 -- p_qty_to_allocate Quantity to be allocated in primary UOM
322 -- OUT:
323 -- x_return_status Standard API parameter
324 -- x_msg_count Standard API parameter
325 -- x_msg_data Standard API parameter
326 -- x_qty_allocated Actual allocated quantity
327 --
328 -- DESCRIPTION : Create allocations between the Subcontracting PO Shipment
329 -- and Replenishment SO Line specified by the IN parameters
330 -- for the specified quantity of the SHIKYU Component in
331 -- its primary UOM.
332 --
333 -- EXCEPTIONS :
334 --
335 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
336 --=============================================================================
337 PROCEDURE Allocate_Quantity
338 ( p_api_version IN NUMBER
339 , p_init_msg_list IN VARCHAR2
340 , x_return_status OUT NOCOPY VARCHAR2
341 , x_msg_count OUT NOCOPY NUMBER
342 , x_msg_data OUT NOCOPY VARCHAR2
343 , p_subcontract_po_shipment_id IN NUMBER
344 , p_component_id IN NUMBER
345 , p_replen_so_line_id IN NUMBER
346 , p_primary_uom IN VARCHAR2
347 , p_qty_to_allocate IN NUMBER
348 , x_qty_allocated OUT NOCOPY NUMBER
349 )
350 IS
351
352 l_api_name CONSTANT VARCHAR2(30) := 'Allocate_Quantity';
353 l_api_version CONSTANT NUMBER := 1.0;
354
355 l_qty_to_allocate NUMBER;
356 l_exists VARCHAR2(1) := 'N';
357
358 l_primary_uom JMF_SHIKYU_REPLENISHMENTS.primary_uom%TYPE;
359 l_replen_uom JMF_SHIKYU_REPLENISHMENTS.uom%TYPE;
360 l_replen_allocated_primary_qty
361 JMF_SHIKYU_REPLENISHMENTS.allocated_primary_uom_quantity%TYPE;
362 l_replen_allocable_primary_qty
363 JMF_SHIKYU_REPLENISHMENTS.allocable_primary_uom_quantity%TYPE;
364 l_replen_allocated_qty JMF_SHIKYU_REPLENISHMENTS.allocated_quantity%TYPE;
365 l_replen_allocable_qty JMF_SHIKYU_REPLENISHMENTS.allocable_quantity%TYPE;
366 l_allocated_uom JMF_SHIKYU_ALLOCATIONS.uom%TYPE;
367 l_allocation_qty JMF_SHIKYU_ALLOCATIONS.allocated_quantity%TYPE;
368 l_replen_so_header_id OE_ORDER_LINES_ALL.header_id%TYPE := NULL;
369 l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
370 l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
371 l_message VARCHAR(2000);
372 l_status_flag BOOLEAN;
373
374
375 l_oem_organization_id JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
376 l_tp_organization_id JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
377
378 g_replen_so_line_not_exist EXCEPTION;
379
380 BEGIN
381
382 -- API Initialization
383 Initialize
384 ( p_api_version => l_api_version
385 , p_input_api_version => p_api_version
386 , p_api_name => l_api_name
387 , p_init_msg_list => p_init_msg_list
388 , x_return_status => x_return_status
389 );
390
391 -- TO DO: Call reconcile_replen_so_line_split here! (Do we need this?)
392
393 -- Look for the Replenishment Sales Order Line in the
394 -- JMF_SHIKYU_REPLENISHMENTS and get the related attributes
395 -- by calling a private procedure.
396 Get_Replen_So_Attributes
397 ( p_replen_so_line_id => p_replen_so_line_id
398 , x_header_id => l_replen_so_header_id
399 , x_allocable_primary_uom_qty => l_replen_allocable_primary_qty
400 , x_allocated_primary_uom_qty => l_replen_allocated_primary_qty
401 , x_uom => l_replen_uom
402 , x_primary_uom => l_allocated_uom
403 , x_replen_so_line_exists => l_exists
404 );
405
406 -- If the Sales Order Line does not exist in the JMF_REPLENISHMENTS table yet
407 IF l_exists = 'N'
408 THEN
409 RAISE g_replen_so_line_not_exist;
410 END IF; /* IF l_exists = 'N' */
411
412 l_qty_to_allocate := p_qty_to_allocate;
413 l_primary_uom := p_primary_uom;
414
415 -- If the Replenishment SO Line does not have enough quantity to satisfy
416 -- the requirement specified by the IN parameter p_qty_to_allocate,
417 -- set the quantity to be allocated to the maximum capacity that the
418 -- Replenishment SO Line can accomodate (allocable_primary_uom_quantity)
419 IF l_replen_allocable_primary_qty - l_replen_allocated_primary_qty < l_qty_to_allocate
420 THEN
421 l_qty_to_allocate := l_replen_allocable_primary_qty - l_replen_allocated_primary_qty;
422 END IF; /* IF l_replen_allocable_primary_qty - l_replen_allocated_primary_qty < p_qty_to_allocate */
423
424 IF (g_fnd_debug = 'Y' AND
425 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
426 THEN
427 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
428 , G_MODULE_PREFIX || l_api_name
429 , l_api_name|| ': l_qty_to_allocate = ' || l_qty_to_allocate);
430 END IF;
431
432 -- Look for an allocation record between the Subcontracting Component
433 -- and the Replenishment SO Line specified by the IN parameters,
434 -- and get the already allocated quantity.
435 Get_Allocation_Attributes
436 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
437 , p_component_id => p_component_id
438 , p_replen_so_line_id => p_replen_so_line_id
439 , x_allocated_qty => l_allocation_qty
440 , x_uom => l_allocated_uom
441 , x_allocation_exists => l_exists
442 );
443
444 IF l_primary_uom IS NULL
445 THEN
446
447 IF l_allocated_uom IS NULL
448 THEN
449
450 -- Get the OEM Organization ID
451 JMF_SHIKYU_UTIL.Get_Subcontract_Order_Org_Ids
452 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
453 , x_oem_organization_id => l_oem_organization_id
454 , x_tp_organization_id => l_tp_organization_id
455 );
456
457 l_primary_uom := JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
458 ( p_inventory_item_id => p_component_id
459 , p_organization_id => l_oem_organization_id
460 );
461 ELSE
462 l_primary_uom := l_allocated_uom;
463 END IF; /* IF l_allocated_uom IS NULL */
464
465 END IF; /* IF l_primary_uom IS NULL */
466
467 -- If an allocation record does not already exist between the
468 -- Subcontracting Order Shipment and the Replenishment Order Line
469 IF l_exists = 'N'
470 THEN
471 INSERT INTO jmf_shikyu_allocations
472 ( SUBCONTRACT_PO_SHIPMENT_ID
473 , SHIKYU_COMPONENT_ID
474 , REPLENISHMENT_SO_LINE_ID
475 , ALLOCATED_QUANTITY
476 , UOM
477 , LAST_UPDATE_DATE
478 , LAST_UPDATED_BY
479 , CREATION_DATE
480 , CREATED_BY
481 , LAST_UPDATE_LOGIN
482 )
483 VALUES
484 ( p_subcontract_po_shipment_id
485 , p_component_id
486 , p_replen_so_line_id
487 , l_qty_to_allocate
488 , l_primary_uom
489 , sysdate
490 , FND_GLOBAL.user_id
491 , sysdate
492 , FND_GLOBAL.user_id
493 , FND_GLOBAL.login_id
494 );
495
496 -- If previous allocations have been created between the Subcontracting
497 -- Order Shipment and the Replenishment Order Line
498 ELSE
499 l_allocation_qty := l_allocation_qty + l_qty_to_allocate;
500
501 UPDATE jmf_shikyu_allocations
502 SET allocated_quantity = l_allocation_qty,
503 last_update_date = sysdate,
504 last_updated_by = FND_GLOBAL.user_id,
505 last_update_login = FND_GLOBAL.login_id
506 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
507 AND replenishment_so_line_id = p_replen_so_line_id
508 AND shikyu_component_id = p_component_id;
509
510 END IF; /* IF l_exists = 'N' */
511
512 -- Pass the actual newly allocated quantity back to the caller as
513 -- OUT parameter
514 x_qty_allocated := l_qty_to_allocate;
515
516 /* Bug 7000413 - Start */
517 /* Log the error in the Concurrent Request log if allocation fails */
518 IF x_qty_allocated = 0 THEN
519 BEGIN
520 SELECT segment1
521 INTO l_order_number
522 FROM po_headers_all poh
523 WHERE EXISTS
524 (SELECT 1 FROM po_line_locations_all poll
525 WHERE poll.line_location_id = p_subcontract_po_shipment_id
526 AND poll.po_header_id = poh.po_header_id);
527
528 SELECT segment1
529 INTO l_sub_comp
530 FROM mtl_system_items_b
531 WHERE inventory_item_id = p_component_id
532 AND organization_id = l_tp_organization_id ;
533
534 fnd_message.set_name('JMF','JMF_SHK_ALLOCATION_ERROR');
535 fnd_message.set_token('SUB_ORDER', l_order_number );
536 fnd_message.set_token('SUB_COMP', l_sub_comp);
537 l_message := fnd_message.GET();
538 fnd_file.put_line(fnd_file.LOG, l_message);
539 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
540 EXCEPTION
541 WHEN OTHERS THEN
542 NULL; -- Return null if there is an error in fetching the message
543 END;
544 END IF;
545 /* Bug 7000413 - End */
546
547 -- Add the quantity to be allocated to the total allocated
548 -- quantity of the Replenishment Sales Order Line
549 l_replen_allocated_primary_qty := l_replen_allocated_primary_qty + l_qty_to_allocate;
550
551 -- Convert the allocated quantity of the Replenishment SO Line
552 -- to the UOM of the SO Line, if it is not the Primary UOM, after
553 -- the new allocation
554 IF l_primary_uom <> l_replen_uom
555 THEN
556
557 l_replen_allocated_qty
558 := INV_CONVERT.inv_um_convert
559 ( item_id => p_component_id
560 , precision => 5
561 , from_quantity => l_replen_allocated_primary_qty
562 , from_unit => l_primary_uom
563 , to_unit => l_replen_uom
564 , from_name => null
565 , to_name => null
566 );
567
568 ELSE
569
570 l_replen_allocated_qty := l_replen_allocated_primary_qty;
571
572 END IF; /* IF l_primary_uom <> l_replen_uom */
573
574 -- update allocated_quantity and allocated_primary_uom_quantity
575 -- of the Replenishment SO Line after the new allocation
576 UPDATE JMF_SHIKYU_REPLENISHMENTS
577 SET allocated_primary_uom_quantity = l_replen_allocated_primary_qty,
578 allocated_quantity = l_replen_allocated_qty,
579 last_update_date = sysdate,
580 last_updated_by = FND_GLOBAL.user_id,
581 last_update_login = FND_GLOBAL.login_id
582 WHERE replenishment_so_line_id = p_replen_so_line_id;
583
584 IF (g_fnd_debug = 'Y' AND
585 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
586 THEN
587 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
588 , G_MODULE_PREFIX || l_api_name
589 , l_api_name || ': l_replen_allocated_primary_qty = ' || l_replen_allocated_primary_qty);
590 END IF;
591
592 IF (g_fnd_debug = 'Y' AND
593 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
594 THEN
595 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
596 , G_MODULE_PREFIX || l_api_name || '.end'
597 , l_api_name || ' Exit');
598 END IF;
599
600 EXCEPTION
601 WHEN g_replen_so_line_not_exist THEN
602
603 x_return_status := FND_API.G_RET_STS_ERROR;
604
605 IF g_fnd_debug = 'Y' AND
606 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
607 THEN
608 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
609 , G_MODULE_PREFIX || l_api_name || '.g_replen_so_line_not_exist'
610 , 'Sales Order with ID ' || p_replen_so_line_id ||
611 ' not in JMF_SHIKYU_REPLENISHMENTS table');
612 END IF;
613
614 WHEN OTHERS THEN
615
616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
617
618 IF g_fnd_debug = 'Y' AND
619 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
620 THEN
621
622 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
623 , G_MODULE_PREFIX || l_api_name || '.others_exception'
624 , sqlerrm);
625 END IF;
626
627 END Allocate_Quantity;
628
629 --=============================================================================
630 -- PROCEDURE NAME: Allocate_Quantity
631 -- TYPE : PUBLIC
632 -- PARAMETERS :
633 -- IN:
634 -- p_api_version Standard API parameter
635 -- p_init_msg_list Standard API parameter
636 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
637 -- p_component_id SHIKYU Component Identifier
638 -- p_qty_to_allocate Quantity to be allocated in primary UOM
639 -- p_available_replen_tbl PL/SQL table containing the replenishment
640 -- SO Lines available for allocations to
641 -- the Subcontracting PO shipment specified
642 -- OUT:
643 -- x_return_status Standard API parameter
644 -- x_msg_count Standard API parameter
645 -- x_msg_data Standard API parameter
646 -- x_qty_allocated Actual allocated quantity
647 --
648 -- DESCRIPTION : Create allocations between the Subcontracting PO Shipment
649 -- and the list of available Replenishment SO Lines, specified
650 -- by the IN parameters, for the specified quantity of the
651 -- SHIKYU Component in its primary UOM.
652 -- EXCEPTIONS :
653 --
654 -- CHANGE HISTORY: 26-MAY-05 VCHU Created.
655 --=============================================================================
656
657 PROCEDURE Allocate_Quantity
658 ( p_api_version IN NUMBER
659 , p_init_msg_list IN VARCHAR2
660 , x_return_status OUT NOCOPY VARCHAR2
661 , x_msg_count OUT NOCOPY NUMBER
662 , x_msg_data OUT NOCOPY VARCHAR2
663 , p_subcontract_po_shipment_id IN NUMBER
664 , p_component_id IN NUMBER
665 , p_qty_to_allocate IN NUMBER
666 , p_available_replen_tbl IN g_replen_so_qty_tbl_type
667 , x_qty_allocated OUT NOCOPY NUMBER
668 )
669 IS
670
671 l_api_name CONSTANT VARCHAR2(30) := 'Allocate_Quantity';
672 l_api_version CONSTANT NUMBER := 1.0;
673
674 l_tbl_index NUMBER;
675 l_qty_to_allocate NUMBER;
676 l_remaining_qty NUMBER;
677 l_qty_allocated NUMBER;
678 -- Local variable to hold the individual records with references
679 -- to the Replenishment SO Lines with remaining quantity
680 l_available_replen_rec g_replen_so_qty_rec_type;
681
682 BEGIN
683
684 -- API Initialization
685 Initialize
686 ( p_api_version => l_api_version
687 , p_input_api_version => p_api_version
688 , p_api_name => l_api_name
689 , p_init_msg_list => p_init_msg_list
690 , x_return_status => x_return_status
691 );
692
693 l_tbl_index := p_available_replen_tbl.FIRST;
694 l_remaining_qty := p_qty_to_allocate;
695
696 -- Loop through the PL/SQL table containing references to the
697 -- Replenishment SO Lines with remaining quantity
698 IF p_available_replen_tbl.COUNT > 0
699 THEN
700
701 LOOP
702
703 l_available_replen_rec := p_available_replen_tbl(l_tbl_index);
704
705 -- Determine the quantity to be allocated from the Replenishment
706 -- SO Line represented by the current g_replen_so_qty_rec_type record
707 IF l_available_replen_rec.primary_uom_qty > l_remaining_qty
708 THEN
709 l_qty_to_allocate := l_remaining_qty;
710 ELSE
711 l_qty_to_allocate := l_available_replen_rec.primary_uom_qty;
712 END IF; /* l_available_replen_rec.primary_uom_qty > l_remaining_qty */
713
714 Allocate_Quantity
715 ( p_api_version => 1.0
716 , p_init_msg_list => p_init_msg_list
717 , x_return_status => x_return_status
718 , x_msg_count => x_msg_count
719 , x_msg_data => x_msg_data
720 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
721 , p_component_id => p_component_id
722 , p_replen_so_line_id => l_available_replen_rec.replenishment_so_line_id
723 , p_primary_uom => l_available_replen_rec.primary_uom
724 , p_qty_to_allocate => l_qty_to_allocate
725 , x_qty_allocated => l_qty_allocated
726 );
727
728 l_remaining_qty := l_remaining_qty - l_qty_allocated;
729
730 l_tbl_index := p_available_replen_tbl.next(l_tbl_index);
731 EXIT WHEN l_tbl_index IS NULL OR l_remaining_qty <= 0;
732
733 END LOOP;
734
735 END IF; /* p_available_replen_tbl.COUNT > 0 */
736
737 -- Pass the actual newly allocated quantity (requested qty - remaining qty
738 -- that cannot be allocated) back to the caller as OUT parameter
739 x_qty_allocated := p_qty_to_allocate - l_remaining_qty;
740
741 EXCEPTION
742
743 WHEN OTHERS THEN
744
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746
747 IF g_fnd_debug = 'Y' AND
748 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
749 THEN
750
751 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
752 , G_MODULE_PREFIX || l_api_name || '.others_exception'
753 , sqlerrm);
754 END IF;
755
756 END Allocate_Quantity;
757
758 --=============================================================================
759 -- PROCEDURE NAME: Get_Available_Replenishment_So
760 -- TYPE : PUBLIC
761 -- PARAMETERS :
762 -- IN:
763 -- p_api_version Standard API parameter
764 -- p_init_msg_list Standard API parameter
765 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
766 -- p_component_id SHIKYU Component Identifier
767 -- p_qty Quantity of the specified subcontracting
768 -- component required
769 -- OUT:
770 -- x_return_status Standard API parameter
771 -- x_msg_count Standard API parameter
772 -- x_msg_data Standard API parameter
773 -- x_available_replen_tbl PL/SQL table containing records of
774 -- g_replen_so_qty_rec_type that represents
775 -- the list of Replenishment Sales Order Lines
776 -- with unallocated quantity available to
777 -- fulfill the requirement specified by p_qty
778 -- x_remaining_qty the remaining quantity that cannot be matched
779 -- to any available Replenishment SO Lines
780 -- with remaining quantity.
781 --
782 -- DESCRIPTION : Returns a PL/SQL table (as an OUT parameter) containing the
783 -- containing records of g_replen_so_qty_rec_type that represent the
784 -- list of Replenishment SO Lines that can fulfill the allocation
785 -- requirement for the subcontracting component and the quantity
786 -- specified by the IN parameters. A Replenishment SO Line can
787 -- fulfill the allocation requirement of a Subcontracting Component
788 -- only if the price and also the project/task reference match.
789 --
790 -- EXCEPTIONS :
791 --
792 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
793 -- 04-AUG-05 VCHU Added a where clause condition to
794 -- c_avail_replen_so_cur to only select
795 -- the replenishments with
796 -- allocable_quantity > 0.
797 --=============================================================================
798 PROCEDURE Get_Available_Replenishment_So
799 ( p_api_version IN NUMBER
800 , p_init_msg_list IN VARCHAR2
801 , x_return_status OUT NOCOPY VARCHAR2
802 , x_msg_count OUT NOCOPY NUMBER
803 , x_msg_data OUT NOCOPY VARCHAR2
804 , p_subcontract_po_shipment_id IN NUMBER
805 , p_component_id IN NUMBER
806 , p_qty IN NUMBER
807 , p_include_additional_supply IN VARCHAR2
808 , p_arrived_so_lines_only IN VARCHAR2
809 , x_available_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
810 , x_remaining_qty OUT NOCOPY NUMBER
811 )
812 IS
813
814 l_api_name CONSTANT VARCHAR2(30) := 'Get_Available_Replenishment_So';
815 l_api_version CONSTANT NUMBER := 1.0;
816
817 l_wip_job_start_date WIP_DISCRETE_JOBS.scheduled_start_date%TYPE;
818 l_wip_job_complete_date WIP_DISCRETE_JOBS.scheduled_completion_date%TYPE;
819 l_ship_lead_time MTL_INTERORG_SHIP_METHODS.intransit_time%TYPE;
820
821 l_remaining_qty NUMBER;
822 l_tbl_index NUMBER;
823 l_out_tbl_index NUMBER;
824 -- Date to compare with the expected arrival date of a Replenishment
825 -- SO Line. Either the Start Date or the Completion Date of the WIP
826 -- Job, or SYSDATE (if only already arrived SO Lines should be considered).
827 l_threshold_date DATE;
828
829 l_avail_replen_so_tbl g_replen_so_qty_tbl_type;
830 l_avail_replen_so_rec g_replen_so_qty_rec_type;
831
832 g_no_wip_job_found_exc EXCEPTION;
833 g_no_ship_method_found_exc EXCEPTION;
834
835 l_oem_organization_id mtl_interorg_ship_methods.from_organization_id%TYPE;
836 l_tp_organization_id mtl_interorg_ship_methods.to_organization_id%TYPE;
837 l_message VARCHAR(2000);
838 l_status_flag BOOLEAN;
839 l_tp_organization MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
840 l_oem_organization MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
841
842
843
844
845 -- Cursor to get the available Replenishment SO Lines with remaining
846 -- quantity for the Subcontracting Component specified by the IN
847 -- parameters
848 CURSOR c_avail_replen_so_cur IS
849 SELECT DISTINCT jsr.replenishment_so_line_id,
850 jsr.shikyu_component_id,
851 jsr.allocable_quantity - jsr.allocated_quantity,
852 jsr.uom,
853 jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity,
854 jsr.primary_uom,
855 oola.schedule_ship_date,
856 --Bugfix 14246759: Adding actual_shipment_date
857 oola.actual_shipment_date
858 FROM jmf_shikyu_replenishments jsr,
859 jmf_subcontract_orders jso,
860 oe_order_lines_all oola
861 WHERE jsr.oem_organization_id = jso.oem_organization_id
862 AND jsr.tp_organization_id = jso.tp_organization_id
863 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
864 AND jsr.shikyu_component_id = p_component_id
865 AND jsr.replenishment_so_line_id = oola.line_id
866 --Bugfix 14246759: Adding actual_shipment_date
867 AND ((TRUNC(nvl(oola.actual_shipment_date, oola.schedule_ship_date)) + l_ship_lead_time <=
868 NVL(l_threshold_date, TRUNC(nvl(oola.actual_shipment_date, oola.schedule_ship_date)) + l_ship_lead_time))
869 OR
870 (p_arrived_so_lines_only = 'Y'
871 AND
872 NVL(oola.shipped_quantity, 0) > 0
873 AND
874 jsr.allocated_primary_uom_quantity <
875 INV_CONVERT.inv_um_convert( jsr.shikyu_component_id
876 , 5
877 , oola.shipped_quantity
878 , oola.order_quantity_uom
879 , JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
880 ( jsr.shikyu_component_id
881 , jsr.tp_organization_id)
882 , null
883 , null)))
884 AND jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity > 0
885 AND DECODE(p_include_additional_supply,
886 'Y', NVL(jsr.additional_supply, 'N'),
887 'N')
888 = NVL(jsr.additional_supply, 'N')
889 --Bugfix 14246759: Adding actual_shipment_date
890 ORDER BY nvl(oola.actual_shipment_date, oola.schedule_ship_date),
891 jsr.replenishment_so_line_id;
892
893 -- Cursor to get the WIP Scheduled Start Date and End Date
894 CURSOR c_wip_date_cur IS
895 SELECT wdj.scheduled_start_date,
896 wdj.scheduled_completion_date
897 FROM WIP_DISCRETE_JOBS wdj,
898 JMF_SUBCONTRACT_ORDERS jso
899 WHERE wdj.wip_entity_id = jso.wip_entity_id
900 AND wdj.organization_id = jso.tp_organization_id
901 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
902
903 -- Cursor to get the Shipping Lead Time from the
904 -- Shipping Network from the OEM Org to the TP Org
905 CURSOR c_ship_lead_time_cur IS
906 SELECT NVL(mism.intransit_time, 0)
907 , FROM_organization_id
908 , to_organization_id
909 FROM MTL_INTERORG_SHIP_METHODS mism,
910 JMF_SUBCONTRACT_ORDERS jso
911 WHERE mism.from_organization_id = jso.oem_organization_id
912 AND mism.to_organization_id = jso.tp_organization_id
913 AND mism.default_flag = 1
914 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
915
916 BEGIN
917 -- API Initialization
918 Initialize
919 ( p_api_version => l_api_version
920 , p_input_api_version => p_api_version
921 , p_api_name => l_api_name
922 , p_init_msg_list => p_init_msg_list
923 , x_return_status => x_return_status
924 );
925
926 x_available_replen_tbl.delete;
927
928 l_remaining_qty := p_qty;
929
930 -- Derive l_threshold_date to prepare for opening the c_avail_replen_so_cur
931
932 -- If all Replenishment SO Lines with remaining quantity should be picked up
933 IF NVL(p_arrived_so_lines_only, 'N') = 'N'
934 THEN
935
936 -- Get the scheduled start date and completion date of the WIP job
937 OPEN c_wip_date_cur;
938 FETCH c_wip_date_cur
939 INTO l_wip_job_start_date, l_wip_job_complete_date;
940
941 IF (g_fnd_debug = 'Y' AND
942 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
943 THEN
944 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
945 , G_MODULE_PREFIX || l_api_name
946 , l_api_name || ': l_wip_job_start_date = '|| l_wip_job_start_date
947 || ', l_wip_job_complete_date = ' || l_wip_job_complete_date);
948 END IF;
949
950 IF c_wip_date_cur%NOTFOUND
951 THEN
952
953 CLOSE c_wip_date_cur;
954 RAISE g_no_wip_job_found_exc;
955 END IF; /* IF c_wip_date_cur%NOTFOUND */
956
957 CLOSE c_wip_date_cur;
958
959 -- If the WIP Job has not started yet
960 IF l_wip_job_start_date > SYSDATE
961 THEN
962 l_threshold_date := l_wip_job_start_date;
963 ELSIF l_wip_job_complete_date >= SYSDATE
964 THEN
965 l_threshold_date := l_wip_job_complete_date;
966 ELSE
967 l_threshold_date := null;
968 END IF; /* IF l_wip_job_start_date > SYSDATE */
969
970 -- If only the Replenishment SO Lines that have physically arrived at
971 -- the TP Org should be picked up (which is the case when allocating
972 -- for the case of an over receipt of an OSA item)
973 ELSE
974
975 l_threshold_date := SYSDATE;
976
977 END IF; /* NVL(p_arrived_so_lines_only, 'N') = 'N' */
978
979 IF (g_fnd_debug = 'Y' AND
980 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
981 THEN
982 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
983 , G_MODULE_PREFIX || l_api_name
984 , l_api_name || ': l_threshold_date = '|| l_threshold_date);
985 END IF;
986
987 -- Get the Shipping Lead Time to prepare for opening the
988 -- c_avail_replen_so_cur
989 OPEN c_ship_lead_time_cur;
990 FETCH c_ship_lead_time_cur INTO l_ship_lead_time, l_oem_organization_id, l_tp_organization_id;
991
992 IF (g_fnd_debug = 'Y' AND
993 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
994 THEN
995 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
996 , G_MODULE_PREFIX || l_api_name
997 , l_api_name || ': l_ship_lead_time = ' || l_ship_lead_time);
998 END IF;
999
1000 IF c_ship_lead_time_cur%NOTFOUND
1001 THEN
1002 CLOSE c_ship_lead_time_cur;
1003 /* Bug 7000413 - Start */
1004 /* Log the error in the Concurrent Request log */
1005 BEGIN
1006
1007 IF (g_fnd_debug = 'Y' AND
1008 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1009 THEN
1010 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1011 , G_MODULE_PREFIX || l_api_name
1012 , 'oem_organization_id '||l_oem_organization_id);
1013
1014 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1015 , G_MODULE_PREFIX || l_api_name
1016 , 'tp_organization_id '||l_tp_organization_id);
1017 END IF;
1018
1019 SELECT organization_code INTO l_oem_organization
1020 FROM mtl_parameters mip
1021 WHERE exists
1022 (SELECT 1 FROM jmf_subcontract_orders jso
1023 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
1024 AND jso.oem_organization_id = mip.organization_id);
1025
1026
1027 SELECT organization_code INTO l_tp_organization
1028 FROM mtl_parameters mip
1029 WHERE exists
1030 (SELECT 1 FROM jmf_subcontract_orders jso
1031 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
1032 AND jso.tp_organization_id = mip.organization_id);
1033
1034 fnd_message.set_name('JMF','JMF_SHK_NO_SHIP_METHOD');
1035 fnd_message.set_token('OEM', l_oem_organization);
1036 fnd_message.set_token('MP', l_tp_organization);
1037 l_message := fnd_message.GET();
1038 fnd_file.put_line(fnd_file.LOG, l_message);
1039 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 IF (g_fnd_debug = 'Y' AND
1043 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1044 THEN
1045
1046 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1047 , G_MODULE_PREFIX || l_api_name
1048 , 'Error in set status of ship method '||SQLERRM);
1049 END IF;
1050 NULL; -- Return null if there is an error in fetching the message
1051 END;
1052 /* Bug 7000413 - End */
1053
1054
1055 RAISE g_no_ship_method_found_exc;
1056 END IF; /* IF c_wip_date_cur%NOTFOUND */
1057
1058 CLOSE c_ship_lead_time_cur;
1059
1060 -- Get the Replenishment SO Lines with remaining quantity
1061 OPEN c_avail_replen_so_cur;
1062 FETCH c_avail_replen_so_cur
1063 BULK COLLECT INTO l_avail_replen_so_tbl;
1064
1065 l_tbl_index := l_avail_replen_so_tbl.FIRST;
1066 l_out_tbl_index := 1;
1067
1068 -- Loop through the table containing references to the Replenishment SO Lines
1069 -- with remaining quantity.
1070 -- If that the price and the project/task references of the SO Line match with
1071 -- those of the Subcontracting Component to be allocated to, add that to the
1072 -- PL/SQL table to be passed out (x_available_replen_tbl).
1073 -- The local variable l_remaining_qty is used to keep track of the remaining
1074 -- quantity that has not been matched to an available Replenishment SO Line yet.
1075 IF l_avail_replen_so_tbl.COUNT > 0
1076 THEN
1077
1078 LOOP
1079
1080 l_avail_replen_so_rec := l_avail_replen_so_tbl(l_tbl_index);
1081
1082 IF (g_fnd_debug = 'Y' AND
1083 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1084 THEN
1085 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1086 , G_MODULE_PREFIX || l_api_name
1087 , l_api_name || ': Loop iteration ' || l_out_tbl_index
1088 || ': Replenishment SO Line ID = '
1089 || l_avail_replen_so_rec.replenishment_so_line_id);
1090 END IF;
1091
1092 IF (Validate_Project_Task_Ref
1093 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1094 , p_replen_so_line_id => l_avail_replen_so_rec.replenishment_so_line_id
1095 )
1096 AND
1097 Validate_Price
1098 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1099 , p_component_id => p_component_id
1100 , p_replen_so_line_id => l_avail_replen_so_rec.replenishment_so_line_id
1101 )
1102 )
1103 THEN
1104
1105 IF (g_fnd_debug = 'Y' AND
1106 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1107 THEN
1108 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1109 , G_MODULE_PREFIX || l_api_name
1110 , l_api_name || ': Validation of Price and Project/Task passed');
1111 END IF;
1112
1113 x_available_replen_tbl(l_out_tbl_index) := l_avail_replen_so_rec;
1114 l_out_tbl_index := l_out_tbl_index + 1;
1115 l_remaining_qty := l_remaining_qty - l_avail_replen_so_rec.primary_uom_qty;
1116 END IF; /* IF (Validate_Project_Task_Ref(...) AND Validate_Price(...) */
1117
1118 l_tbl_index := l_avail_replen_so_tbl.next(l_tbl_index);
1119 EXIT WHEN l_tbl_index IS NULL OR l_remaining_qty <= 0;
1120
1121 END LOOP;
1122
1123 END IF; /* IF l_avail_replen_so_tbl.COUNT > 0 */
1124
1125 IF (g_fnd_debug = 'Y' AND
1126 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1127 THEN
1128 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1129 , G_MODULE_PREFIX || l_api_name
1130 , l_api_name || ': l_remaining_qty = '|| l_remaining_qty);
1131 END IF;
1132
1133 IF l_remaining_qty < 0
1134 THEN
1135 l_remaining_qty := 0;
1136 END IF;
1137
1138 x_remaining_qty := l_remaining_qty;
1139
1140 CLOSE c_avail_replen_so_cur;
1141
1142 IF (g_fnd_debug = 'Y' AND
1143 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1144 THEN
1145 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1146 , G_MODULE_PREFIX || l_api_name || '.end'
1147 , l_api_name || ' Exit');
1148 END IF;
1149
1150 EXCEPTION
1151
1152 WHEN g_no_wip_job_found_exc THEN
1153
1154 x_return_status := FND_API.G_RET_STS_ERROR;
1155 IF g_fnd_debug = 'Y' AND
1156 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1157 THEN
1158 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1159 , G_MODULE_PREFIX || l_api_name || '.g_no_wip_job_found_excc'
1160 , 'No WIP Job found for Subcontracting Order Shipment with ID = '
1161 || p_subcontract_po_shipment_id);
1162 END IF;
1163
1164 WHEN g_no_ship_method_found_exc THEN
1165
1166 x_return_status := FND_API.G_RET_STS_ERROR;
1167 IF g_fnd_debug = 'Y' AND
1168 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1169 THEN
1170 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1171 , G_MODULE_PREFIX || l_api_name || '.g_no_ship_method_found_exc'
1172 , 'Cannot get Shipping Lead Time from the Shipping Network from '
1173 || 'the OEM Org to the TP Org');
1174 END IF;
1175
1176 WHEN OTHERS THEN
1177
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1179
1180 IF g_fnd_debug = 'Y' AND
1181 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1182 THEN
1183
1184 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1185 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1186 , sqlerrm);
1187 END IF;
1188
1189 END Get_Available_Replenishment_So;
1190
1191 --=============================================================================
1192 -- PROCEDURE NAME: Get_Available_Replenishment_Po
1193 -- TYPE : PUBLIC
1194 -- PARAMETERS :
1195 -- IN:
1196 -- p_api_version Standard API parameter
1197 -- p_init_msg_list Standard API parameter
1198 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
1199 -- p_component_id SHIKYU Component Identifier
1200 -- p_qty Quantity of the specified subcontracting
1201 -- component required
1202 -- OUT:
1203 -- x_return_status Standard API parameter
1204 -- x_msg_count Standard API parameter
1205 -- x_msg_data Standard API parameter
1206 -- x_available_replen_tbl PL/SQL table containing records of
1207 -- g_replen_po_qty_rec_type that represents
1208 -- the list of Replenishment PO Shipments
1209 -- with remaining quantity that does not
1210 -- correspond to any Replenishment SO Line yet
1211 -- x_remaining_qty the remaining quantity that cannot be matched
1212 -- to any existing Replenishment PO Shipoments
1213 -- with quantity that does not correspond to
1214 -- a Replenishment SO Line.
1215 --
1216 -- DESCRIPTION : Returns a PL/SQL table (as an OUT parameter) containing
1217 -- records of g_replen_po_qty_rec_type that represent the
1218 -- list of Replenishment PO Shipments that can fulfill
1219 -- the allocation requirement (by having corresponding
1220 -- Replenishment SO Lines created) for the subcontracting
1221 -- component and the quantity specified by the IN parameters.
1222 --
1223 -- EXCEPTIONS :
1224 --
1225 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
1226 --=============================================================================
1227 PROCEDURE Get_Available_Replenishment_Po
1228 ( p_api_version IN NUMBER
1229 , p_init_msg_list IN VARCHAR2
1230 , x_return_status OUT NOCOPY VARCHAR2
1231 , x_msg_count OUT NOCOPY NUMBER
1232 , x_msg_data OUT NOCOPY VARCHAR2
1233 , p_subcontract_po_shipment_id IN NUMBER
1234 , p_component_id IN NUMBER
1235 , p_qty IN NUMBER
1236 , x_available_replen_tbl OUT NOCOPY g_replen_po_qty_tbl_type
1237 , x_remaining_qty OUT NOCOPY NUMBER
1238 )
1239 IS
1240
1241 l_api_name CONSTANT VARCHAR2(30) := 'Get_Available_Replenishment_Po';
1242 l_api_version CONSTANT NUMBER := 1.0;
1243
1244 l_primary_uom MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE;
1245 l_replen_po_primary_uom MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE;
1246
1247 l_oem_organization_id JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
1248 l_tp_organization_id JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
1249
1250 l_avail_replen_po_tbl g_replen_po_qty_tbl_type;
1251 l_avail_replen_po_rec g_replen_po_qty_rec_type;
1252
1253 l_tbl_index NUMBER;
1254 l_out_tbl_index NUMBER;
1255 l_remaining_qty NUMBER;
1256 l_replen_po_ordered_qty NUMBER;
1257 l_uom_rate NUMBER;
1258
1259 -- Cursor to get the available Replenishment PO Shipments with
1260 -- remaining quantity that does not correspond to any Replenishment
1261 -- SO Line yet
1262 CURSOR c_avail_replen_po_cur IS
1263 SELECT DISTINCT plla.line_location_id,
1264 pla.item_id,
1265 plla.quantity,
1266 muomv.uom_code,
1267 plla.quantity,
1268 muomv.uom_code,
1269 NVL(plla.need_by_date, plla.promised_date),
1270 pha.segment1,
1271 pla.line_num,
1272 plla.shipment_num
1273 FROM jmf_subcontract_orders jso,
1274 hr_organization_information hoi,
1275 po_line_locations_all plla,
1276 po_lines_all pla,
1277 po_headers_all pha,
1278 mtl_units_of_measure_vl muomv
1279 WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
1280 AND hoi.organization_id = jso.oem_organization_id
1281 AND hoi.org_information_context = 'Customer/Supplier Association'
1282 AND TO_NUMBER(hoi.org_information3) = pha.vendor_id
1283 AND TO_NUMBER(hoi.org_information4) = pha.vendor_site_id
1284 AND plla.ship_to_organization_id = jso.tp_organization_id
1285 AND plla.po_line_id = pla.po_line_id
1286 AND plla.po_header_id = pha.po_header_id
1287 AND pla.item_id = p_component_id
1288 AND pha.approved_flag = 'Y'
1289 AND nvl(plla.approved_flag, 'N') = 'Y' -- Added for bug 13549961
1290 AND plla.shipment_type in ('STANDARD', 'BLANKET') -- Added for bug 13549961
1291 AND NVL(pha.cancel_flag, 'N') = 'N'
1292 AND NVL(pla.cancel_flag, 'N') = 'N'
1293 AND NVL(plla.cancel_flag, 'N') = 'N'
1294 AND plla.unit_meas_lookup_code = muomv.unit_of_measure
1295 AND NOT EXISTS (SELECT jsr.replenishment_so_line_id
1296 FROM jmf_shikyu_replenishments jsr
1297 WHERE jsr.replenishment_po_shipment_id = plla.line_location_id)
1298 ORDER BY NVL(plla.need_by_date, plla.promised_date),
1299 pha.segment1,
1300 pla.line_num,
1301 plla.shipment_num;
1302
1303 BEGIN
1304
1305 -- API Initialization
1306 Initialize
1307 ( p_api_version => l_api_version
1308 , p_input_api_version => p_api_version
1309 , p_api_name => l_api_name
1310 , p_init_msg_list => p_init_msg_list
1311 , x_return_status => x_return_status
1312 );
1313
1314 -- Get the OEM Organization and TP Organization IDs
1315 JMF_SHIKYU_UTIL.Get_Subcontract_Order_Org_Ids
1316 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1317 , x_oem_organization_id => l_oem_organization_id
1318 , x_tp_organization_id => l_tp_organization_id
1319 );
1320
1321 IF (g_fnd_debug = 'Y' AND
1322 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1323 THEN
1324 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1325 , G_MODULE_PREFIX || l_api_name
1326 , l_api_name || ': l_oem_organization_id = ' || l_oem_organization_id
1327 || ', l_tp_organization_id = ' || l_tp_organization_id);
1328 END IF;
1329
1330 -- Get the primaru UOM code of the Subcontracting Component
1331 l_primary_uom := JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
1332 ( p_inventory_item_id => p_component_id
1333 , p_organization_id => l_oem_organization_id
1334 );
1335
1336 IF (g_fnd_debug = 'Y' AND
1337 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1338 THEN
1339 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1340 , G_MODULE_PREFIX || l_api_name
1341 , l_api_name || ': l_primary_uom = ' || l_primary_uom);
1342 END IF;
1343
1344 l_remaining_qty := p_qty;
1345 x_available_replen_tbl.DELETE;
1346
1347 OPEN c_avail_replen_po_cur;
1348 FETCH c_avail_replen_po_cur
1349 BULK COLLECT INTO l_avail_replen_po_tbl;
1350
1351 l_tbl_index := l_avail_replen_po_tbl.FIRST;
1352 l_out_tbl_index := 1;
1353
1354 -- Loop through the PL/SQL table containing the records of g_replen_po_qty_rec_type
1355 -- representing the Replenishment PO Shipments with remaining quantity that does
1356 -- not correspond to any Replenishment SO Line yet
1357 IF l_avail_replen_po_tbl.COUNT > 0
1358 THEN
1359
1360 LOOP
1361
1362 l_uom_rate := NULL;
1363 l_avail_replen_po_rec := l_avail_replen_po_tbl(l_tbl_index);
1364
1365 IF (g_fnd_debug = 'Y' AND
1366 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1367 THEN
1368 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1369 , G_MODULE_PREFIX || l_api_name
1370 , l_api_name || ': Loop iteration: ' || l_tbl_index
1371 || ', l_primary_uom = ' || l_primary_uom
1372 || ', l_avail_replen_po_rec.uom = ' || l_avail_replen_po_rec.uom);
1373 END IF;
1374
1375 -- If the Replenishment PO was not created in the primary UOM of the
1376 -- SHIKYU component, convert the avialblae qty to be in the primary UOM
1377 IF l_primary_uom <> l_avail_replen_po_rec.uom
1378 THEN
1379
1380 l_avail_replen_po_rec.primary_uom := l_primary_uom;
1381 l_avail_replen_po_rec.primary_uom_qty
1382 := INV_CONVERT.inv_um_convert
1383 ( item_id => p_component_id
1384 , precision => 5
1385 , from_quantity => l_avail_replen_po_rec.qty
1386 , from_unit => l_avail_replen_po_rec.uom
1387 , to_unit => l_primary_uom
1388 , from_name => null
1389 , to_name => null
1390 );
1391
1392 END IF; /* IF l_primary_uom <> l_avail_replen_po_rec.uom */
1393
1394 IF (g_fnd_debug = 'Y' AND
1395 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1396 THEN
1397 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1398 , G_MODULE_PREFIX || l_api_name
1399 , l_api_name || ': replenishment_po_shipment_id = '
1400 || l_avail_replen_po_rec.replenishment_po_shipment_id
1401 || ', qty = ' || l_avail_replen_po_rec.qty
1402 || ', uom = ' || l_avail_replen_po_rec.uom);
1403 END IF;
1404
1405 x_available_replen_tbl(l_out_tbl_index) := l_avail_replen_po_rec;
1406 l_out_tbl_index := l_out_tbl_index + 1;
1407 l_remaining_qty := l_remaining_qty - l_avail_replen_po_rec.primary_uom_qty;
1408
1409 l_tbl_index := l_avail_replen_po_tbl.next(l_tbl_index);
1410 EXIT WHEN l_tbl_index IS NULL OR l_remaining_qty <= 0;
1411
1412 END LOOP;
1413
1414 END IF; /* IF l_avail_replen_po_tbl.COUNT > 0 */
1415
1416 IF l_remaining_qty < 0
1417 THEN
1418 x_remaining_qty := 0;
1419 ELSE
1420 x_remaining_qty := l_remaining_qty;
1421 END IF; /* IF l_remaining_qty < 0 */
1422
1423 IF (g_fnd_debug = 'Y' AND
1424 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1425 THEN
1426 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1427 , G_MODULE_PREFIX || l_api_name
1428 , l_api_name || ': x_remaining_qty = ' || x_remaining_qty);
1429 END IF;
1430
1431 CLOSE c_avail_replen_po_cur;
1432
1433 IF (g_fnd_debug = 'Y' AND
1434 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1435 THEN
1436 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1437 , G_MODULE_PREFIX || l_api_name || '.end'
1438 , l_api_name || ' Exit');
1439 END IF;
1440
1441 EXCEPTION
1442
1443 WHEN OTHERS THEN
1444
1445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1446
1447 IF g_fnd_debug = 'Y' AND
1448 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1449 THEN
1450
1451 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1452 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1453 , sqlerrm);
1454 END IF;
1455
1456 END Get_Available_Replenishment_Po;
1457
1458 --=============================================================================
1459 -- PROCEDURE NAME: Create_New_Replenishment_Po_So
1460 -- TYPE : PUBLIC
1461 -- PARAMETERS :
1462 -- IN:
1463 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
1464 -- p_component_id SHIKYU Component Identifier
1465 -- p_uom
1466 -- p_qty Quantity (in primary UOM) of the new
1467 -- Replenishment PO Shipment and Sales Order
1468 -- Line to be created
1469 -- OUT:
1470 -- x_new_replen_po_rec
1471 -- x_new_replen_so_rec
1472 -- DESCRIPTION :
1473 --
1474 -- EXCEPTIONS :
1475 --
1476 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
1477 --=============================================================================
1478 PROCEDURE Create_New_Replenishment_Po_So
1479 ( p_api_version IN NUMBER
1480 , p_init_msg_list IN VARCHAR2
1481 , x_return_status OUT NOCOPY VARCHAR2
1482 , x_msg_count OUT NOCOPY NUMBER
1483 , x_msg_data OUT NOCOPY VARCHAR2
1484 , p_subcontract_po_shipment_id IN NUMBER
1485 , p_component_id IN NUMBER
1486 , p_qty IN NUMBER
1487 , x_new_replen_so_rec OUT NOCOPY g_replen_so_qty_rec_type
1488 )
1489 IS
1490
1491 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Replenishment_Po_So';
1492 l_api_version CONSTANT NUMBER := 1.0;
1493
1494 l_component_uom JMF_SHIKYU_COMPONENTS.uom%TYPE;
1495 l_primary_uom JMF_SHIKYU_COMPONENTS.primary_uom%TYPE;
1496 l_component_price JMF_SHIKYU_COMPONENTS.shikyu_component_price%TYPE;
1497 l_primary_uom_price JMF_SHIKYU_COMPONENTS.primary_uom_price%TYPE;
1498 l_oem_organization_id JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
1499 l_tp_organization_id JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
1500 l_new_replen_so_line_id OE_ORDER_LINES_ALL.line_id%TYPE;
1501 l_new_replen_po_shipment_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
1502 l_new_replen_qty NUMBER;
1503
1504 g_process_replen_po_exc EXCEPTION;
1505 g_process_replen_so_exc EXCEPTION;
1506
1507 BEGIN
1508
1509 -- API Initialization
1510 Initialize
1511 ( p_api_version => l_api_version
1512 , p_input_api_version => p_api_version
1513 , p_api_name => l_api_name
1514 , p_init_msg_list => p_init_msg_list
1515 , x_return_status => x_return_status
1516 );
1517
1518 -- Create a new Replenishment PO with the passed in quantity
1519 JMF_SHIKYU_PO_PVT.Process_Replenishment_PO
1520 ( p_action => 'C'
1521 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1522 , p_quantity => p_qty
1523 , p_item_id => p_component_id
1524 , x_po_line_location_id => l_new_replen_po_shipment_id
1525 , x_return_status => x_return_status
1526 );
1527
1528 IF (g_fnd_debug = 'Y' AND
1529 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1530 THEN
1531 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1532 , G_MODULE_PREFIX || l_api_name
1533 , l_api_name || ': x_return_status from JMF_SHIKYU_PO_PVT.Process_Replenishment_PO = '
1534 || x_return_status
1535 || ', x_po_line_location_id = ' || l_new_replen_po_shipment_id);
1536 END IF;
1537
1538 -- *** vchu: new code 8/18
1539 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1540 THEN
1541
1542 RAISE g_process_replen_po_exc;
1543
1544 END IF;
1545 -- *** vchu end: new code 8/18
1546
1547 IF l_new_replen_po_shipment_id IS NOT NULL
1548 THEN
1549 -- To get component uom and primary uom of the SHIKYU Component
1550 JMF_SHIKYU_UTIL.Get_Shikyu_Component_Price
1551 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1552 , p_shikyu_component_id => p_component_id
1553 , x_component_uom => l_component_uom
1554 , x_component_price => l_component_price
1555 , x_primary_uom => l_primary_uom
1556 , x_primary_uom_price => l_primary_uom_price
1557 );
1558
1559 IF (g_fnd_debug = 'Y' AND
1560 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1561 THEN
1562 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1563 , G_MODULE_PREFIX || l_api_name
1564 , l_api_name || ': l_component_uom = ' || l_component_uom
1565 || ', l_component_price = ' || l_component_price
1566 || ', l_primary_uom = ' || l_primary_uom
1567 || ', l_primary_uom_price = ' || l_primary_uom_price);
1568 END IF;
1569
1570 IF l_component_uom <> l_primary_uom
1571 THEN
1572
1573 l_new_replen_qty := INV_CONVERT.inv_um_convert
1574 ( item_id => p_component_id
1575 , precision => 5
1576 , from_quantity => p_qty
1577 , from_unit => l_primary_uom
1578 , to_unit => l_component_uom
1579 , from_name => null
1580 , to_name => null
1581 );
1582 ELSE
1583
1584 l_new_replen_qty := p_qty;
1585
1586 END IF; /* IF l_component_uom <> l_primary_uom */
1587
1588 -- Get OEM Organization and TP Organization IDs
1589 JMF_SHIKYU_UTIL.Get_Subcontract_Order_Org_Ids
1590 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1591 , x_oem_organization_id => l_oem_organization_id
1592 , x_tp_organization_id => l_tp_organization_id
1593 );
1594
1595 IF (g_fnd_debug = 'Y' AND
1596 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1597 THEN
1598 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1599 , G_MODULE_PREFIX || l_api_name
1600 , l_api_name || ': l_new_replen_qty = ' || l_new_replen_qty);
1601 END IF;
1602
1603 -- Creating a new Replenishment SO Line
1604 JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
1605 ( p_action => 'C'
1606 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1607 , p_quantity => l_new_replen_qty
1608 , p_item_id => p_component_id
1609 , p_replen_po_shipment_id => l_new_replen_po_shipment_id
1610 , p_oem_organization_id => l_oem_organization_id
1611 , p_tp_organization_id => l_tp_organization_id
1612 , x_order_line_id => l_new_replen_so_line_id
1613 , x_return_status => x_return_status
1614 );
1615
1616 IF (g_fnd_debug = 'Y' AND
1617 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1618 THEN
1619 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1620 , G_MODULE_PREFIX || l_api_name
1621 , l_api_name || ': x_return_status from JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO = '
1622 || x_return_status);
1623 END IF;
1624
1625 -- *** vchu: new code 8/18
1626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1627 THEN
1628
1629 RAISE g_process_replen_so_exc;
1630
1631 END IF;
1632 -- *** vchu end: new code 8/18
1633
1634 -- Insert the new Replenishment SO Line into the
1635 -- JMF_SHIKYU_REPLENISHMENTS table
1636 Populate_Replenishment
1637 ( p_replen_so_line_id => l_new_replen_so_line_id
1638 , p_replen_po_shipment_id => l_new_replen_po_shipment_id
1639 , p_component_id => p_component_id
1640 , p_oem_organization_id => l_oem_organization_id
1641 , p_tp_organization_id => l_tp_organization_id
1642 , p_primary_uom => l_primary_uom
1643 , p_primary_uom_qty => p_qty
1644 , p_additional_supply => 'N'
1645 );
1646
1647 -- Set the OUT parameter x_new_replen_so_rec with the uom and quantity
1648 -- information for the newly created Replenishment SO Line
1649 x_new_replen_so_rec.replenishment_so_line_id := l_new_replen_so_line_id;
1650 x_new_replen_so_rec.qty := l_new_replen_qty;
1651 x_new_replen_so_rec.uom := l_component_uom;
1652 x_new_replen_so_rec.primary_uom_qty := p_qty;
1653 x_new_replen_so_rec.primary_uom := l_primary_uom;
1654
1655 END IF;
1656
1657 IF (g_fnd_debug = 'Y' AND
1658 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1659 THEN
1660 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1661 , G_MODULE_PREFIX || l_api_name || '.end'
1662 , l_api_name || ' Exit');
1663 END IF;
1664
1665 EXCEPTION
1666 WHEN g_process_replen_po_exc THEN
1667
1668 IF g_fnd_debug = 'Y' AND
1669 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1670 THEN
1671 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1672 , G_MODULE_PREFIX || l_api_name || '.g_process_replen_po_exc'
1673 , 'Process_Replenishment_PO API returns a status of ' || x_return_status);
1674 END IF;
1675
1676 WHEN g_process_replen_so_exc THEN
1677
1678 IF g_fnd_debug = 'Y' AND
1679 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1680 THEN
1681 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1682 , G_MODULE_PREFIX || l_api_name || '.g_process_replen_so_exc'
1683 , 'Process_Replenishment_SO API returns a status of ' || x_return_status);
1684 END IF;
1685
1686 WHEN OTHERS THEN
1687
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1689
1690 IF g_fnd_debug = 'Y' AND
1691 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1692 THEN
1693
1694 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1695 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1696 , sqlerrm);
1697 END IF;
1698
1699 END Create_New_Replenishment_Po_So;
1700
1701 -- Call in the case of pre-positioned components
1702 --=============================================================================
1703 -- PROCEDURE NAME: Create_New_Replenishment_So
1704 -- TYPE : PUBLIC
1705 -- PARAMETERS :
1706 -- IN:
1707 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
1708 -- p_component_id SHIKYU Component Identifier
1709 -- p_uom
1710 -- p_qty Quantity of the new Replenishment Sales
1711 -- Order Line to be created
1712 -- OUT:
1713 -- x_new_replen_tbl
1714 -- DESCRIPTION :
1715 --
1716 -- EXCEPTIONS :
1717 --
1718 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
1719 --=============================================================================
1720 PROCEDURE Create_New_Replenishment_So
1721 ( p_api_version IN NUMBER
1722 , p_init_msg_list IN VARCHAR2
1723 , x_return_status OUT NOCOPY VARCHAR2
1724 , x_msg_count OUT NOCOPY NUMBER
1725 , x_msg_data OUT NOCOPY VARCHAR2
1726 , p_subcontract_po_shipment_id IN NUMBER
1727 , p_component_id IN NUMBER
1728 , p_qty IN NUMBER
1729 , p_additional_supply IN VARCHAR2
1730 , x_new_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
1731 )
1732 IS
1733
1734 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Replenishment_So';
1735 l_api_version CONSTANT NUMBER := 1.0;
1736
1737 l_component_uom VARCHAR2(3);
1738 l_primary_uom VARCHAR2(3);
1739 l_oem_organization_id NUMBER;
1740 l_tp_organization_id NUMBER;
1741 l_component_uom_qty NUMBER;
1742 l_remaining_qty NUMBER;
1743 l_actual_remaining_qty NUMBER;
1744 l_tbl_index NUMBER;
1745 l_replen_so_tbl_index NUMBER;
1746 l_new_replen_qty NUMBER;
1747 l_new_replen_primary_qty NUMBER;
1748 l_new_order_line_id NUMBER;
1749 l_replen_po_qty NUMBER;
1750 l_replen_po_primary_qty NUMBER;
1751
1752 l_avail_replen_po_tbl g_replen_po_qty_tbl_type;
1753 l_replen_po_rec g_replen_po_qty_rec_type;
1754
1755 g_process_replen_so_exc EXCEPTION;
1756
1757 BEGIN
1758
1759 -- API Initialization
1760 Initialize
1761 ( p_api_version => l_api_version
1762 , p_input_api_version => p_api_version
1763 , p_api_name => l_api_name
1764 , p_init_msg_list => p_init_msg_list
1765 , x_return_status => x_return_status
1766 );
1767
1768 -- To clear the passed in table of type g_replen_so_qty_tbl_type
1769 -- for storing the newly created Replenishment SO Lines
1770 x_new_replen_tbl.delete;
1771
1772 -- TO-DO: Call a UTIL procedure? Or put in BEGIN/EXCEPTION/END block?
1773 SELECT jso.oem_organization_id,
1774 jso.tp_organization_id,
1775 jsc.uom,
1776 jsc.primary_uom
1777 INTO l_oem_organization_id,
1778 l_tp_organization_id,
1779 l_component_uom,
1780 l_primary_uom
1781 FROM jmf_subcontract_orders jso,
1782 jmf_shikyu_components jsc
1783 WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
1784 AND jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1785 AND jsc.shikyu_component_id = p_component_id;
1786
1787 -- Get the PL/SQL table containing records of g_replen_po_qty_rec_type
1788 -- that represents the list of Replenishment PO Shipments with remaining
1789 -- quantity that does not correspond to any Replenishment SO Line yet
1790 Get_Available_Replenishment_Po
1791 ( p_api_version => 1.0
1792 , p_init_msg_list => p_init_msg_list
1793 , x_return_status => x_return_status
1794 , x_msg_count => x_msg_count
1795 , x_msg_data => x_msg_data
1796 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1797 , p_component_id => p_component_id
1798 , p_qty => p_qty
1799 , x_available_replen_tbl => l_avail_replen_po_tbl
1800 , x_remaining_qty => l_remaining_qty
1801 );
1802
1803 l_tbl_index := l_avail_replen_po_tbl.FIRST;
1804 l_actual_remaining_qty := p_qty;
1805 l_replen_so_tbl_index := 1;
1806
1807 -- Loop through the list of available Replenishment PO Shipments
1808 -- and create corresponding Replenishment SO Lines
1809 IF l_avail_replen_po_tbl.COUNT > 0
1810 THEN
1811
1812 LOOP
1813
1814 l_replen_po_rec := l_avail_replen_po_tbl(l_tbl_index);
1815
1816 l_replen_po_primary_qty := l_replen_po_rec.primary_uom_qty;
1817 l_replen_po_qty := l_replen_po_rec.qty;
1818
1819 IF (g_fnd_debug = 'Y' AND
1820 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1821 THEN
1822 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1823 , G_MODULE_PREFIX || l_api_name
1824 , l_api_name || ': Loop Iteration: ' || l_tbl_index
1825 || ': l_replen_po_rec.replenishment_po_shipment_id = '
1826 || l_replen_po_rec.replenishment_po_shipment_id);
1827 END IF;
1828
1829 IF(l_replen_po_rec.uom <> l_component_uom)
1830 THEN
1831
1832 l_new_replen_qty := INV_CONVERT.inv_um_convert
1833 ( item_id => p_component_id
1834 , precision => 5
1835 , from_quantity => l_replen_po_qty
1836 , from_unit => l_replen_po_rec.uom
1837 , to_unit => l_component_uom
1838 , from_name => null
1839 , to_name => null
1840 );
1841
1842 l_new_replen_qty := FLOOR(l_new_replen_qty);
1843
1844 -- Convert the qty of the new Replenishment SO Line to be
1845 -- created back to primary UOM for calculation of the
1846 -- remaining quantity, for which there are no matching
1847 -- Replenishment PO to create a new Replenishment SO Line
1848 -- against
1849 IF(l_component_uom <> l_primary_uom)
1850 THEN
1851
1852 l_new_replen_primary_qty := INV_CONVERT.inv_um_convert
1853 ( item_id => p_component_id
1854 , precision => 5
1855 , from_quantity => l_new_replen_qty
1856 , from_unit => l_component_uom
1857 , to_unit => l_primary_uom
1858 , from_name => null
1859 , to_name => null
1860 );
1861
1862 ELSE
1863
1864 l_new_replen_primary_qty := l_new_replen_qty;
1865
1866 END IF; /* IF(l_component_uom <> l_primary_uom) */
1867
1868 ELSE
1869
1870 l_new_replen_qty := l_replen_po_rec.qty;
1871 l_new_replen_primary_qty := l_replen_po_rec.primary_uom_qty;
1872
1873 END IF; /* IF(l_replen_po_rec.uom <> l_component_uom) */
1874
1875 IF (g_fnd_debug = 'Y' AND
1876 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1877 THEN
1878 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1879 , G_MODULE_PREFIX || l_api_name
1880 , l_api_name || ': Loop Iteration: ' || l_tbl_index
1881 || ', l_component_uom = ' || l_component_uom
1882 || ', l_primary_uom = ' || l_primary_uom
1883 || ', l_replen_po_rec.uom = ' || l_replen_po_rec.uom
1884 || ', l_new_replen_primary_qty = '||l_new_replen_primary_qty);
1885 END IF;
1886
1887 -- Creating a new Replenishment SO Line
1888 JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
1889 ( p_action => 'C'
1890 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
1891 , p_quantity => l_new_replen_primary_qty
1892 , p_item_id => p_component_id
1893 , p_replen_po_shipment_id => l_replen_po_rec.replenishment_po_shipment_id
1894 , p_oem_organization_id => l_oem_organization_id
1895 , p_tp_organization_id => l_tp_organization_id
1896 , x_order_line_id => l_new_order_line_id
1897 , x_return_status => x_return_status
1898 );
1899
1900 -- *** vchu: new code 8/18
1901 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1902 THEN
1903
1904 RAISE g_process_replen_so_exc;
1905
1906 END IF;
1907 -- *** vchu end: new code 8/18
1908
1909 IF (g_fnd_debug = 'Y' AND
1910 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1911 THEN
1912 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1913 , G_MODULE_PREFIX || l_api_name
1914 , l_api_name || ': return status from JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO = '
1915 || x_return_status);
1916 END IF;
1917
1918 IF (g_fnd_debug = 'Y' AND
1919 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1920 THEN
1921 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1922 , G_MODULE_PREFIX || l_api_name
1923 , l_api_name || ': l_replen_po_rec.replenishment_po_shipment_id = '
1924 || l_replen_po_rec.replenishment_po_shipment_id || ', l_new_order_line_id = '
1925 || l_new_order_line_id);
1926 END IF;
1927
1928 -- Insert the new Replenishment SO Line into the
1929 -- JMF_SHIKYU_REPLENISHMENTS table
1930 Populate_Replenishment
1931 ( p_replen_so_line_id => l_new_order_line_id
1932 , p_replen_po_shipment_id => l_replen_po_rec.replenishment_po_shipment_id
1933 , p_component_id => p_component_id
1934 , p_oem_organization_id => l_oem_organization_id
1935 , p_tp_organization_id => l_tp_organization_id
1936 , p_primary_uom => l_primary_uom
1937 , p_primary_uom_qty => l_new_replen_primary_qty
1938 , p_additional_supply => p_additional_supply
1939 );
1940
1941 -- Insert a record into the x_new_replen_tbl to store the SO Line ID and
1942 -- quantity of the newly created Replenishment SO Line
1943 x_new_replen_tbl(l_replen_so_tbl_index).replenishment_so_line_id := l_new_order_line_id;
1944 x_new_replen_tbl(l_replen_so_tbl_index).qty := l_new_replen_qty;
1945 x_new_replen_tbl(l_replen_so_tbl_index).uom := l_component_uom;
1946 x_new_replen_tbl(l_replen_so_tbl_index).primary_uom_qty := l_new_replen_primary_qty;
1947 x_new_replen_tbl(l_replen_so_tbl_index).primary_uom := l_primary_uom;
1948
1949 IF (g_fnd_debug = 'Y' AND
1950 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1951 THEN
1952 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1953 , G_MODULE_PREFIX || l_api_name
1954 , l_api_name || ': x_new_replen_tbl(l_replen_so_tbl_index).qty = '
1955 || x_new_replen_tbl(l_replen_so_tbl_index).qty);
1956 END IF;
1957
1958 IF (g_fnd_debug = 'Y' AND
1959 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1960 THEN
1961 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1962 , G_MODULE_PREFIX || l_api_name
1963 , l_api_name || ': x_new_replen_tbl(l_replen_so_tbl_index).uom = '
1964 || x_new_replen_tbl(l_replen_so_tbl_index).uom);
1965 END IF;
1966
1967 l_replen_so_tbl_index := l_replen_so_tbl_index + 1;
1968
1969 l_actual_remaining_qty := l_actual_remaining_qty - l_new_replen_primary_qty;
1970
1971 l_tbl_index := l_avail_replen_po_tbl.next(l_tbl_index);
1972 EXIT WHEN l_tbl_index IS NULL OR l_actual_remaining_qty <= 0;
1973
1974 END LOOP;
1975
1976 END IF; /* IF l_avail_replen_po_tbl.COUNT > 0 */
1977
1978 IF (g_fnd_debug = 'Y' AND
1979 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
1980 THEN
1981 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1982 , G_MODULE_PREFIX || l_api_name || '.end'
1983 , l_api_name || ' Exit');
1984 END IF;
1985
1986 EXCEPTION
1987 WHEN g_process_replen_so_exc THEN
1988
1989 IF g_fnd_debug = 'Y' AND
1990 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1991 THEN
1992 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1993 , G_MODULE_PREFIX || l_api_name || '.g_process_replen_so_exc'
1994 , 'Process_Replenishment_SO API returns a status of ' || x_return_status);
1995 END IF;
1996
1997 WHEN OTHERS THEN
1998
1999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2000
2001 IF g_fnd_debug = 'Y' AND
2002 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2003 THEN
2004
2005 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2006 , G_MODULE_PREFIX || l_api_name || '.others_exception'
2007 , sqlerrm);
2008 END IF;
2009
2010 END Create_New_Replenishment_So;
2011
2012 --=============================================================================
2013 -- PROCEDURE NAME: Create_New_Allocations
2014 -- TYPE : PUBLIC
2015 -- PARAMETERS :
2016 -- IN:
2017 -- p_api_version Standard API parameter
2018 -- p_init_msg_list Standard API parameter
2019 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
2020 -- p_component_id SHIKYU Component Identifier
2021 -- p_qty Quantity of allocations to be created
2022 -- p_skip_po_replen_creation Skip creation of new Replenishment PO for
2023 -- sync-ship component (in cases where there
2024 -- are not enough available Replenishments)
2025 -- if the value is 'Y'
2026 -- OUT:
2027 -- x_return_status Standard API parameter
2028 -- x_msg_count Standard API parameter
2029 -- x_msg_data Standard API parameter
2030 --
2031 -- DESCRIPTION : Create allocations for the Subcontracting Component and
2032 -- the quantity specified by the IN parmeters. This procedure
2033 -- determines whether the SHIKYU component is Pre-positioned
2034 -- or Sync-ship, and then call the corresponding procedure to
2035 -- create the actual allocations
2036 --
2037 -- EXCEPTIONS :
2038 --
2039 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
2040 --=============================================================================
2041 PROCEDURE Create_New_Allocations
2042 ( p_api_version IN NUMBER
2043 , p_init_msg_list IN VARCHAR2
2044 , x_return_status OUT NOCOPY VARCHAR2
2045 , x_msg_count OUT NOCOPY NUMBER
2046 , x_msg_data OUT NOCOPY VARCHAR2
2047 , p_subcontract_po_shipment_id IN NUMBER
2048 , p_component_id IN NUMBER
2049 , p_qty IN NUMBER
2050 , p_skip_po_replen_creation IN VARCHAR2
2051 )
2052 IS
2053
2054 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Allocations';
2055 l_api_version CONSTANT NUMBER := 1.0;
2056
2057 l_subcontracting_component MTL_SYSTEM_ITEMS.subcontracting_component%TYPE;
2058
2059 g_non_shikyu_component_exc EXCEPTION;
2060
2061 BEGIN
2062
2063 -- API Initialization
2064 Initialize
2065 ( p_api_version => l_api_version
2066 , p_input_api_version => p_api_version
2067 , p_api_name => l_api_name
2068 , p_init_msg_list => p_init_msg_list
2069 , x_return_status => x_return_status
2070 );
2071
2072 IF (g_fnd_debug = 'Y' AND
2073 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2074 THEN
2075 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2076 , G_MODULE_PREFIX || l_api_name
2077 , 'Begin');
2078 END IF;
2079
2080 -- Get the type of the SHIKYU Component, since the allocation logic is
2081 -- different for Pre-positioned and Sync-ship components
2082 SELECT msib.subcontracting_component
2083 INTO l_subcontracting_component
2084 FROM MTL_SYSTEM_ITEMS_B msib,
2085 JMF_SUBCONTRACT_ORDERS jso
2086 WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2087 AND msib.inventory_item_id = p_component_id
2088 AND msib.organization_id = jso.tp_organization_id;
2089
2090 IF (g_fnd_debug = 'Y' AND
2091 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2092 THEN
2093 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2094 , G_MODULE_PREFIX || l_api_name
2095 , l_api_name || ': p_subcontract_po_shipment_id = '|| p_subcontract_po_shipment_id);
2096
2097 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2098 , G_MODULE_PREFIX || l_api_name
2099 , l_api_name || ': p_component_id = '|| p_component_id
2100 || ', p_qty = ' || p_qty );
2101
2102 END IF;
2103
2104 -- If the SHIKYU Component is Pre-Positioned
2105 IF l_subcontracting_component = 1
2106 THEN
2107
2108 IF (g_fnd_debug = 'Y' AND
2109 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2110 THEN
2111 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2112 , G_MODULE_PREFIX || l_api_name
2113 , l_api_name || ': in Pre-Positioned case');
2114 END IF;
2115
2116 Allocate_Prepositioned_Comp
2117 ( p_api_version => 1.0
2118 , p_init_msg_list => p_init_msg_list
2119 , x_return_status => x_return_status
2120 , x_msg_count => x_msg_count
2121 , x_msg_data => x_msg_data
2122 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2123 , p_component_id => p_component_id
2124 , p_qty => p_qty
2125 );
2126
2127 -- If the SHIKYU Component is Sync-ship
2128 ELSIF l_subcontracting_component = 2
2129 THEN
2130
2131 IF (g_fnd_debug = 'Y' AND
2132 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2133 THEN
2134 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2135 , G_MODULE_PREFIX || l_api_name
2136 , l_api_name || ': in Sync-ship case');
2137 END IF;
2138
2139 Allocate_Syncship_Comp
2140 ( p_api_version => 1.0
2141 , p_init_msg_list => p_init_msg_list
2142 , x_return_status => x_return_status
2143 , x_msg_count => x_msg_count
2144 , x_msg_data => x_msg_data
2145 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2146 , p_component_id => p_component_id
2147 , p_qty => p_qty
2148 , p_skip_po_replen_creation => p_skip_po_replen_creation
2149 );
2150
2151 ELSE
2152 -- Raise an exception if the SHIKYU is not Pre-positioned or Sync-ship
2153 RAISE g_non_shikyu_component_exc;
2154
2155 END IF; /* IF l_subcontracting_component = 1 */
2156
2157 IF (g_fnd_debug = 'Y' AND
2158 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2159 THEN
2160 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2161 , G_MODULE_PREFIX || l_api_name || '.end'
2162 , l_api_name || ' Exit');
2163 END IF;
2164
2165 EXCEPTION
2166
2167 WHEN NO_DATA_FOUND THEN
2168
2169 x_return_status := FND_API.G_RET_STS_ERROR;
2170 IF g_fnd_debug = 'Y' AND
2171 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2172 THEN
2173 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
2174 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
2175 , 'Subcontracting Order Shipment ID' || p_subcontract_po_shipment_id ||
2176 ' or Component ID ' || p_component_id || ' does not exist');
2177 END IF;
2178
2179 WHEN g_non_shikyu_component_exc THEN
2180
2181 x_return_status := FND_API.G_RET_STS_ERROR;
2182 IF g_fnd_debug = 'Y' AND
2183 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2184 THEN
2185 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
2186 , G_MODULE_PREFIX || l_api_name || '.g_non_shikyu_component_exc'
2187 , 'The component to allocate is not Sync-ship or Pre-Positioned');
2188 END IF;
2189
2190 WHEN OTHERS THEN
2191
2192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193
2194 IF g_fnd_debug = 'Y' AND
2195 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2196 THEN
2197
2198 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2199 , G_MODULE_PREFIX || l_api_name || '.others_exception'
2200 , sqlerrm);
2201 END IF;
2202
2203 END Create_New_Allocations;
2204
2205 --=============================================================================
2206 -- PROCEDURE NAME: Allocate_Prepositioned_Comp
2207 -- TYPE : PUBLIC
2208 -- PARAMETERS :
2209 -- IN:
2210 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
2211 -- p_component_id SHIKYU Component Identifier
2212 -- p_uom
2213 -- p_qty Quantity to be allocated
2214 -- DESCRIPTION :
2215 --
2216 -- EXCEPTIONS :
2217 --
2218 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
2219 --=============================================================================
2220 PROCEDURE Allocate_Prepositioned_Comp
2221 ( p_api_version IN NUMBER
2222 , p_init_msg_list IN VARCHAR2
2223 , x_return_status OUT NOCOPY VARCHAR2
2224 , x_msg_count OUT NOCOPY NUMBER
2225 , x_msg_data OUT NOCOPY VARCHAR2
2226 , p_subcontract_po_shipment_id IN NUMBER
2227 , p_component_id IN NUMBER
2228 , p_qty IN NUMBER
2229 )
2230 IS
2231
2232 l_api_name CONSTANT VARCHAR2(30) := 'Allocate_Prepositioned_Comp';
2233 l_api_version CONSTANT NUMBER := 1.0;
2234
2235 l_available_replen_tbl g_replen_so_qty_tbl_type;
2236 l_remaining_qty NUMBER;
2237 l_qty_allocated NUMBER;
2238 l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
2239 l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
2240 l_message VARCHAR(2000);
2241 l_status_flag BOOLEAN;
2242 l_tp_organization_id MTL_PARAMETERS.ORGANIZATION_ID%type;
2243
2244
2245 BEGIN
2246
2247 -- API Initialization
2248 Initialize
2249 ( p_api_version => l_api_version
2250 , p_input_api_version => p_api_version
2251 , p_api_name => l_api_name
2252 , p_init_msg_list => p_init_msg_list
2253 , x_return_status => x_return_status
2254 );
2255
2256 Get_Available_Replenishment_So
2257 ( p_api_version => 1.0
2258 , p_init_msg_list => p_init_msg_list
2259 , x_return_status => x_return_status
2260 , x_msg_count => x_msg_count
2261 , x_msg_data => x_msg_data
2262 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2263 , p_component_id => p_component_id
2264 , p_qty => p_qty
2265 , p_include_additional_supply => 'Y'
2266 , p_arrived_so_lines_only => 'N'
2267 , x_available_replen_tbl => l_available_replen_tbl
2268 , x_remaining_qty => l_remaining_qty
2269 );
2270
2271 IF (g_fnd_debug = 'Y' AND
2272 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2273 THEN
2274 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2275 , G_MODULE_PREFIX || l_api_name
2276 , l_api_name || ': l_remaining_qty = ' || l_remaining_qty);
2277 END IF;
2278
2279 -- *** vchu: new code 8/18
2280
2281 l_qty_allocated := 0;
2282
2283 IF l_remaining_qty < p_qty
2284 THEN
2285
2286 Allocate_Quantity
2287 ( p_api_version => 1.0
2288 , p_init_msg_list => p_init_msg_list
2289 , x_return_status => x_return_status
2290 , x_msg_count => x_msg_count
2291 , x_msg_data => x_msg_data
2292 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2293 , p_component_id => p_component_id
2294 , p_qty_to_allocate => p_qty - l_remaining_qty
2295 , p_available_replen_tbl => l_available_replen_tbl
2296 , x_qty_allocated => l_qty_allocated
2297 );
2298
2299 END IF;
2300 -- *** vchu end: new code 8/18
2301
2302 IF (g_fnd_debug = 'Y' AND
2303 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2304 THEN
2305 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2306 , G_MODULE_PREFIX || l_api_name
2307 , l_api_name || ': l_qty_allocated = ' || l_qty_allocated);
2308 END IF;
2309
2310 IF l_qty_allocated < p_qty
2311 THEN
2312
2313 -- Call Create_New_Replenishment_So to find available Replenishment PO Shipments
2314 -- with remaining qty and create corresponding Replenishment SO Lines for that qty
2315 Create_New_Replenishment_So
2316 ( p_api_version => 1.0
2317 , p_init_msg_list => p_init_msg_list
2318 , x_return_status => x_return_status
2319 , x_msg_count => x_msg_count
2320 , x_msg_data => x_msg_data
2321 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2322 , p_component_id => p_component_id
2323 --, p_primary_uom => NULL
2324 , p_qty => l_remaining_qty
2325 , p_additional_supply => 'N'
2326 , x_new_replen_tbl => l_available_replen_tbl
2327 );
2328
2329 IF (g_fnd_debug = 'Y' AND
2330 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2331 THEN
2332 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2333 , G_MODULE_PREFIX || l_api_name
2334 , l_api_name || ': return status from Create_New_Replenishment_So = '
2335 || x_return_status);
2336 END IF;
2337
2338 -- *** vchu: new code 8/18
2339 IF x_return_status = FND_API.G_RET_STS_SUCCESS
2340 THEN
2341 -- Allocate the remaining qty required to the Replenishment SO Lines newly
2342 -- created according to the existing Replenishment PO Shipments with remaining
2343 -- quantity
2344 Allocate_Quantity
2345 ( p_api_version => 1.0
2346 , p_init_msg_list => p_init_msg_list
2347 , x_return_status => x_return_status
2348 , x_msg_count => x_msg_count
2349 , x_msg_data => x_msg_data
2350 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2351 , p_component_id => p_component_id
2352 , p_qty_to_allocate => l_remaining_qty
2353 , p_available_replen_tbl => l_available_replen_tbl
2354 , x_qty_allocated => l_qty_allocated
2355 );
2356 END IF;
2357 -- *** vchu: new code 8/18
2358
2359 END IF;
2360
2361 /* Bug 7000413 - Start */
2362 /* Log the error in the Concurrent Request log if allocation fails */
2363 IF l_qty_allocated = 0 THEN
2364 BEGIN
2365 SELECT segment1
2366 INTO l_order_number
2367 FROM po_headers_all poh
2368 WHERE EXISTS
2369 (SELECT 1 FROM po_line_locations_all poll
2370 WHERE poll.line_location_id = p_subcontract_po_shipment_id
2371 AND poll.po_header_id = poh.po_header_id);
2372
2373 SELECT segment1
2374 INTO l_sub_comp
2375 FROM mtl_system_items_b msi
2376 WHERE inventory_item_id = p_component_id
2377 AND exists
2378 (SELECT 1
2379 FROM jmf_subcontract_orders jso
2380 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
2381 AND jso.tp_organization_id = msi.organization_id );
2382
2383
2384 fnd_message.set_name('JMF','JMF_SHK_ALLOCATION_ERROR');
2385 fnd_message.set_token('SUB_ORDER', l_order_number );
2386 fnd_message.set_token('SUB_COMP', l_sub_comp);
2387 l_message := fnd_message.GET();
2388 fnd_file.put_line(fnd_file.LOG, l_message);
2389 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
2390 EXCEPTION
2391 WHEN OTHERS THEN
2392 NULL; -- Return null if there is an error in fetching the message
2393 END;
2394 END IF;
2395 /* Bug 7000413 - End */
2396
2397
2398
2399
2400 IF (g_fnd_debug = 'Y' AND
2401 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2402 THEN
2403 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2404 , G_MODULE_PREFIX || l_api_name || '.end'
2405 , l_api_name || ' Exit');
2406 END IF;
2407
2408 EXCEPTION
2409
2410 WHEN OTHERS THEN
2411
2412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2413
2414 IF g_fnd_debug = 'Y' AND
2415 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2416 THEN
2417
2418 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2419 , G_MODULE_PREFIX || l_api_name || '.others_exception'
2420 , sqlerrm);
2421 END IF;
2422
2423 END Allocate_Prepositioned_Comp;
2424
2425 --=============================================================================
2426 -- PROCEDURE NAME: Allocate_Syncship_Comp
2427 -- TYPE : PUBLIC
2428 -- PARAMETERS :
2429 -- IN:
2430 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
2431 -- p_component_id SHIKYU Component Identifier
2432 -- p_uom
2433 -- p_qty Quantity to be allocated
2434 -- p_skip_po_replen_creation Skip creation of new Replenishment PO in
2435 -- cases where there are not enough available
2436 -- Replenishments, if the value is 'Y'
2437 -- DESCRIPTION :
2438 --
2439 -- EXCEPTIONS :
2440 --
2441 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
2442 --=============================================================================
2443 PROCEDURE Allocate_Syncship_Comp
2444 ( p_api_version IN NUMBER
2445 , p_init_msg_list IN VARCHAR2
2446 , x_return_status OUT NOCOPY VARCHAR2
2447 , x_msg_count OUT NOCOPY NUMBER
2448 , x_msg_data OUT NOCOPY VARCHAR2
2449 , p_subcontract_po_shipment_id IN NUMBER
2450 , p_component_id IN NUMBER
2451 , p_qty IN NUMBER
2452 , p_skip_po_replen_creation IN VARCHAR2
2453 )
2454 IS
2455
2456 l_api_name CONSTANT VARCHAR2(30) := 'Allocate_Syncship_Comp';
2457 l_api_version CONSTANT NUMBER := 1.0;
2458
2459 l_remaining_qty NUMBER;
2460 l_qty_allocated NUMBER;
2461
2462 l_available_replen_tbl g_replen_so_qty_tbl_type;
2463 l_new_replen_so_rec g_replen_so_qty_rec_type;
2464
2465 BEGIN
2466
2467 -- API Initialization
2468 Initialize
2469 ( p_api_version => l_api_version
2470 , p_input_api_version => p_api_version
2471 , p_api_name => l_api_name
2472 , p_init_msg_list => p_init_msg_list
2473 , x_return_status => x_return_status
2474 );
2475
2476 IF (g_fnd_debug = 'Y' AND
2477 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2478 THEN
2479 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2480 , G_MODULE_PREFIX || l_api_name
2481 , l_api_name || ': p_subcontract_po_shipment_id = ' || p_subcontract_po_shipment_id
2482 || ', p_component_id = ' || p_component_id
2483 || ', p_qty = ' || p_qty
2484 || ', p_skip_po_replen_creation = ' || p_skip_po_replen_creation);
2485 END IF;
2486
2487 Get_Available_Replenishment_So
2488 ( p_api_version => 1.0
2489 , p_init_msg_list => p_init_msg_list
2490 , x_return_status => x_return_status
2491 , x_msg_count => x_msg_count
2492 , x_msg_data => x_msg_data
2493 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2494 , p_component_id => p_component_id
2495 , p_qty => p_qty
2496 , p_include_additional_supply => 'N'
2497 , p_arrived_so_lines_only => 'N'
2498 , x_available_replen_tbl => l_available_replen_tbl
2499 , x_remaining_qty => l_remaining_qty
2500 );
2501
2502 l_qty_allocated := 0;
2503
2504 IF l_remaining_qty < p_qty
2505 THEN
2506
2507 IF (g_fnd_debug = 'Y' AND
2508 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2509 THEN
2510 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2511 , G_MODULE_PREFIX || l_api_name
2512 , l_api_name || ': l_remaining_qty = ' || l_remaining_qty
2513 || ', p_qty = ' || p_qty);
2514 END IF;
2515
2516 Allocate_Quantity
2517 ( p_api_version => 1.0
2518 , p_init_msg_list => p_init_msg_list
2519 , x_return_status => x_return_status
2520 , x_msg_count => x_msg_count
2521 , x_msg_data => x_msg_data
2522 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2523 , p_component_id => p_component_id
2524 -- *** vchu: new code 8/18
2525 , p_qty_to_allocate => p_qty - l_remaining_qty
2526 -- *** vchu end: new code 8/18
2527 , p_available_replen_tbl => l_available_replen_tbl
2528 , x_qty_allocated => l_qty_allocated
2529 );
2530
2531 END IF;
2532
2533 IF (g_fnd_debug = 'Y'
2534 AND FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2535 THEN
2536 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2537 , G_MODULE_PREFIX || l_api_name
2538 , l_api_name || ': p_skip_po_replen_creation = '
2539 || p_skip_po_replen_creation);
2540 END IF;
2541
2542 IF l_qty_allocated < p_qty
2543 AND p_skip_po_replen_creation <> 'Y'
2544 THEN
2545
2546 IF (g_fnd_debug = 'Y' AND
2547 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2548 THEN
2549 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2550 , G_MODULE_PREFIX || l_api_name
2551 , l_api_name || ': l_qty_allocated = ' || l_qty_allocated
2552 || ', p_qty = ' || p_qty);
2553 END IF;
2554
2555 Create_New_Replenishment_Po_So
2556 ( p_api_version => 1.0
2557 , p_init_msg_list => p_init_msg_list
2558 , x_return_status => x_return_status
2559 , x_msg_count => x_msg_count
2560 , x_msg_data => x_msg_data
2561 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2562 , p_component_id => p_component_id
2563 , p_qty => l_remaining_qty
2564 , x_new_replen_so_rec => l_new_replen_so_rec
2565 );
2566
2567 IF (g_fnd_debug = 'Y' AND
2568 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2569 THEN
2570 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2571 , G_MODULE_PREFIX || l_api_name
2572 , l_api_name || ': x_return_status = ' || x_return_status);
2573 END IF;
2574
2575 -- *** vchu: new code 8/18
2576 IF x_return_status = FND_API.G_RET_STS_SUCCESS
2577 AND
2578 Validate_Price
2579 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2580 , p_component_id => p_component_id
2581 , p_replen_so_line_id => l_new_replen_so_rec.replenishment_so_line_id
2582 )
2583 AND
2584 Validate_Project_Task_Ref
2585 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2586 , p_replen_so_line_id => l_new_replen_so_rec.replenishment_so_line_id
2587 )
2588 THEN
2589
2590 Allocate_Quantity
2591 ( p_api_version => 1.0
2592 , p_init_msg_list => p_init_msg_list
2593 , x_return_status => x_return_status
2594 , x_msg_count => x_msg_count
2595 , x_msg_data => x_msg_data
2596 , p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2597 , p_component_id => p_component_id
2598 , p_replen_so_line_id => l_new_replen_so_rec.replenishment_so_line_id
2599 , p_primary_uom => l_new_replen_so_rec.primary_uom
2600 , p_qty_to_allocate => l_remaining_qty
2601 , x_qty_allocated => l_qty_allocated
2602 );
2603
2604 END IF;
2605 -- *** vchu end: new code 8/18
2606
2607 END IF; /* IF l_qty_allocated < p_qty */
2608
2609 IF (g_fnd_debug = 'Y' AND
2610 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2611 THEN
2612 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2613 , G_MODULE_PREFIX || l_api_name || '.end'
2614 , l_api_name || ' Exit');
2615 END IF;
2616
2617 EXCEPTION
2618
2619 WHEN OTHERS THEN
2620
2621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2622
2623 IF g_fnd_debug = 'Y' AND
2624 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2625 THEN
2626
2627 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2628 , G_MODULE_PREFIX || l_api_name || '.others_exception'
2629 , sqlerrm);
2630 END IF;
2631
2632 END Allocate_Syncship_Comp;
2633
2634 --=============================================================================
2635 -- PROCEDURE NAME: Reduce_Allocations
2636 -- TYPE : PUBLIC
2637 -- PARAMETERS :
2638 -- IN:
2639 -- p_api_version Standard API parameter
2640 -- p_init_msg_list Standard API parameter
2641 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
2642 -- p_component_id SHIKYU Component Identifier
2643 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
2644 -- p_qty_to_reduce Quantity to be deallocated
2645 -- OUT:
2646 -- x_return_status Standard API parameter
2647 -- x_msg_count Standard API parameter
2648 -- x_msg_data Standard API parameter
2649 -- x_actual_reduced_qty The actual quantity that was deallocated
2650 -- x_reduced_allocations_tbl PL/SQL Table containing information such
2651 -- as the actual deallocated quantity from
2652 -- the allocations being reduced.
2653 --
2654 -- DESCRIPTION : Decrease allocations between the subcontracting component
2655 -- and the replenishment according to the p_qty_to_decrease.
2656 -- If p_replen_so_line_id is NULL, then decrease allocations
2657 -- in all replenishment lines in FIFO order of the scheduled
2658 -- ship date.
2659 --
2660 -- EXCEPTIONS :
2661 --
2662 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
2663 --=============================================================================
2664 PROCEDURE Reduce_Allocations
2665 ( p_api_version IN NUMBER
2666 , p_init_msg_list IN VARCHAR2
2667 , x_return_status OUT NOCOPY VARCHAR2
2668 , x_msg_count OUT NOCOPY NUMBER
2669 , x_msg_data OUT NOCOPY VARCHAR2
2670 , p_subcontract_po_shipment_id IN NUMBER
2671 , p_component_id IN NUMBER
2672 , p_replen_so_line_id IN NUMBER
2673 , p_qty_to_reduce IN NUMBER
2674 , x_actual_reduced_qty OUT NOCOPY NUMBER
2675 , x_reduced_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
2676 )
2677 IS
2678
2679 l_api_name CONSTANT VARCHAR2(30) := 'Reduce_Allocations';
2680 l_api_version CONSTANT NUMBER := 1.0;
2681
2682 TYPE l_allocation_detail_rec_type IS RECORD
2683 ( subcontract_po_shipment_id NUMBER
2684 , replenishment_so_line_id NUMBER
2685 , component_id NUMBER
2686 , qty NUMBER
2687 , qty_uom VARCHAR2(3)
2688 --Bugfix 14246759: Adding actual_shipment_date
2689 , rso_line_actual_ship_date OE_ORDER_LINES_ALL.ACTUAL_SHIPMENT_DATE%TYPE
2690 , replen_so_line_ship_date OE_ORDER_LINES_ALL.SCHEDULE_SHIP_DATE%TYPE
2691 , replen_so_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE
2692 , replen_so_line_number OE_ORDER_LINES_ALL.LINE_NUMBER%TYPE
2693 );
2694
2695 TYPE l_allocation_detail_tbl_type IS TABLE OF l_allocation_detail_rec_type INDEX BY BINARY_INTEGER;
2696
2697 l_allocations_tbl l_allocation_detail_tbl_type;
2698 l_reduced_allocations_rec g_allocation_qty_rec_type;
2699
2700 l_existing_alloc_qty NUMBER;
2701 l_remain_qty_to_reduce NUMBER;
2702 l_allocations_tbl_index NUMBER;
2703 l_reduced_allocs_tbl_index NUMBER;
2704 l_existing_alloc_uom VARCHAR2(3);
2705
2706 g_no_alloc_found_exc EXCEPTION;
2707
2708 -- Cursor to select the information regarding all allocations for
2709 -- the Subcontracting Component specified by the IN parameters,
2710 -- in FIFO order of the scheduled ship date, SO number and SO line
2711 -- number
2712 CURSOR c_subcontract_po_alloc_cur IS
2713 SELECT DISTINCT jsa.subcontract_po_shipment_id
2714 , oola.line_id
2715 , jsa.shikyu_component_id
2716 , jsa.allocated_quantity
2717 , jsa.uom
2718 --Bugfix 14246759: Adding actual_shipment_date
2719 , oola.actual_shipment_date
2720 , oola.schedule_ship_date
2721 , ooha.order_number
2722 , oola.line_number
2723 FROM JMF_SHIKYU_ALLOCATIONS jsa,
2724 OE_ORDER_LINES_ALL oola,
2725 OE_ORDER_HEADERS_ALL ooha
2726 WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2727 AND jsa.shikyu_component_id = p_component_id
2728 AND oola.line_id = jsa.replenishment_so_line_id
2729 AND ooha.header_id = oola.header_id
2730 --Bugfix 14246759: Adding actual_shipment_date
2731 ORDER BY nvl(oola.actual_shipment_date, oola.schedule_ship_date) DESC,
2732 ooha.order_number DESC,
2733 oola.line_number DESC;
2734
2735 -- Cursor to select the information regarding the allocations between
2736 -- the Subcontracting Component and the Replenishment SO Line specified
2737 -- by the IN parameters
2738 CURSOR c_alloc_cur IS
2739 SELECT jsa.allocated_quantity, jsa.uom
2740 FROM JMF_SHIKYU_ALLOCATIONS jsa
2741 WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2742 AND jsa.replenishment_so_line_id = p_replen_so_line_id;
2743
2744 BEGIN
2745
2746 -- API Initialization
2747 Initialize
2748 ( p_api_version => l_api_version
2749 , p_input_api_version => p_api_version
2750 , p_api_name => l_api_name
2751 , p_init_msg_list => p_init_msg_list
2752 , x_return_status => x_return_status
2753 );
2754
2755 x_reduced_allocations_tbl.DELETE;
2756
2757 IF p_replen_so_line_id IS NULL
2758 THEN
2759
2760 IF (g_fnd_debug = 'Y' AND
2761 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2762 THEN
2763 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2764 , G_MODULE_PREFIX || l_api_name
2765 , l_api_name || ': p_replen_so_line_id IS NULL');
2766 END IF;
2767
2768 OPEN c_subcontract_po_alloc_cur;
2769 FETCH c_subcontract_po_alloc_cur
2770 BULK COLLECT INTO l_allocations_tbl;
2771
2772 IF (g_fnd_debug = 'Y' AND
2773 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2774 THEN
2775 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2776 , G_MODULE_PREFIX || l_api_name
2777 , l_api_name || ': fetched c_subcontract_po_alloc_cur');
2778 END IF;
2779
2780 IF l_allocations_tbl.count <= 0
2781 THEN
2782 RAISE g_no_alloc_found_exc;
2783 END IF; /* IF l_allocations_tbl.count <= 0 */
2784
2785 l_allocations_tbl_index := l_allocations_tbl.FIRST;
2786 l_remain_qty_to_reduce := p_qty_to_reduce;
2787 l_reduced_allocs_tbl_index := 1;
2788
2789 -- To reduce more than one Replenishment SO Line allocated to the
2790 -- Subcontracting Component specified, in FIFO order of the scheduled
2791 -- ship date
2792 -- i.e. reduce from multiple JMF_SHIKYU_ALLOCATIONS record
2793 IF l_allocations_tbl.COUNT > 0
2794 THEN
2795 -- Loop through the PL/SQL table containing records of g_allocation_qty_tbl_type
2796 -- representing the allocations for the specified Subcontracting Component,
2797 -- until the qty to be deallocated is reached or all allocations have been
2798 -- examined.
2799 LOOP
2800
2801 IF (g_fnd_debug = 'Y' AND
2802 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2803 THEN
2804 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2805 , G_MODULE_PREFIX || l_api_name
2806 , l_api_name || ': Loop Iteration: '
2807 || l_allocations_tbl_index);
2808 END IF;
2809
2810 Reduce_One_Allocation
2811 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2812 , p_component_id => p_component_id
2813 , p_replen_so_line_id => l_allocations_tbl(l_allocations_tbl_index).replenishment_so_line_id
2814 , p_remain_qty_to_reduce => l_remain_qty_to_reduce
2815 , p_existing_alloc_qty => l_allocations_tbl(l_allocations_tbl_index).qty
2816 , p_alloc_uom => l_allocations_tbl(l_allocations_tbl_index).qty_uom
2817 , x_reduced_allocations_rec => l_reduced_allocations_rec
2818 );
2819
2820 x_reduced_allocations_tbl(l_reduced_allocs_tbl_index) := l_reduced_allocations_rec;
2821
2822 -- Increment the index for the OUT table to pass out information of the
2823 -- allocations being reduced
2824 l_reduced_allocs_tbl_index := l_reduced_allocs_tbl_index + 1;
2825
2826 -- Update the remaining qty to be reduced
2827 l_remain_qty_to_reduce := l_remain_qty_to_reduce - l_reduced_allocations_rec.qty;
2828
2829 IF (g_fnd_debug = 'Y' AND
2830 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2831 THEN
2832 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2833 , G_MODULE_PREFIX || l_api_name
2834 , l_api_name || ': From Reduce_Allocations: l_remain_qty_to_reduce = '
2835 || l_remain_qty_to_reduce);
2836 END IF;
2837
2838 l_allocations_tbl_index := l_allocations_tbl.next(l_allocations_tbl_index);
2839 EXIT WHEN l_allocations_tbl_index IS NULL OR l_remain_qty_to_reduce <= 0;
2840
2841 END LOOP;
2842 END IF; /* IF l_allocations_tbl.COUNT > 0 */
2843 x_actual_reduced_qty := p_qty_to_reduce - l_remain_qty_to_reduce;
2844
2845 CLOSE c_subcontract_po_alloc_cur;
2846
2847 -- To reduce from the Replenishment SO Line specified
2848 -- i.e. reduce from only one JMF_SHIKYU_ALLOCATIONS record
2849 ELSE
2850
2851 IF (g_fnd_debug = 'Y' AND
2852 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2853 THEN
2854 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2855 , G_MODULE_PREFIX || l_api_name
2856 , 'p_replen_so_line_id:' || p_replen_so_line_id);
2857 END IF;
2858
2859 OPEN c_alloc_cur;
2860 FETCH c_alloc_cur INTO l_existing_alloc_qty, l_existing_alloc_uom;
2861
2862 IF (g_fnd_debug = 'Y' AND
2863 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2864 THEN
2865 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2866 , G_MODULE_PREFIX || l_api_name
2867 , l_api_name || ': fetched c_alloc_cur');
2868 END IF;
2869
2870 IF c_alloc_cur%NOTFOUND
2871 THEN
2872 RAISE g_no_alloc_found_exc;
2873 END IF; /* IF c_alloc_cur%NOTFOUND */
2874
2875 CLOSE c_alloc_cur;
2876
2877 Reduce_One_Allocation
2878 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
2879 , p_component_id => p_component_id
2880 , p_replen_so_line_id => p_replen_so_line_id
2881 , p_remain_qty_to_reduce => p_qty_to_reduce
2882 , p_existing_alloc_qty => l_existing_alloc_qty
2883 , p_alloc_uom => l_existing_alloc_uom
2884 , x_reduced_allocations_rec => l_reduced_allocations_rec
2885 );
2886
2887 x_reduced_allocations_tbl(1) := l_reduced_allocations_rec;
2888
2889 x_actual_reduced_qty := l_reduced_allocations_rec.qty;
2890
2891 END IF; /* IF p_replen_so_line_id IS NULL */
2892
2893 IF (g_fnd_debug = 'Y' AND
2894 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2895 THEN
2896 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2897 , G_MODULE_PREFIX || l_api_name
2898 , l_api_name || ': x_actual_reduced_qty = '|| x_actual_reduced_qty);
2899 END IF;
2900
2901 IF (g_fnd_debug = 'Y' AND
2902 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2903 THEN
2904 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2905 , G_MODULE_PREFIX || l_api_name || '.end'
2906 , l_api_name || ' Exit');
2907 END IF;
2908
2909 EXCEPTION
2910
2911 WHEN g_no_alloc_found_exc THEN
2912
2913 x_return_status := FND_API.G_RET_STS_ERROR;
2914 IF g_fnd_debug = 'Y' AND
2915 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2916 THEN
2917 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
2918 , G_MODULE_PREFIX || l_api_name || '.g_no_alloc_found_exc'
2919 , 'Allocation(s) not found and cannot be reduced');
2920 END IF;
2921
2922 WHEN OTHERS THEN
2923
2924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2925
2926 IF g_fnd_debug = 'Y' AND
2927 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2928 THEN
2929
2930 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2931 , G_MODULE_PREFIX || l_api_name || '.others_exception'
2932 , sqlerrm);
2933 END IF;
2934
2935 END Reduce_Allocations;
2936
2937 --=============================================================================
2938 -- PROCEDURE NAME: Delete_Allocations
2939 -- TYPE : PUBLIC
2940 -- PARAMETERS :
2941 -- IN:
2942 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
2943 -- p_component_id SHIKYU Component Identifier
2944 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
2945 -- OUT:
2946 -- x_deleted_allocations_tbl Table containing information of the deleted
2947 -- allocations
2948 -- DESCRIPTION : Delete allocation for a Subcontracting Order Shipment.
2949 -- If p_component_id is null, all allocations created for the
2950 -- Subcontracting Order Shipment with
2951 -- p_subcontract_po_shipment_id would be deleted. Otherwise,
2952 -- only allocations created for that particular component
2953 -- would be deleted.
2954 -- If both p_component_id and p_replen_so_line_id are not null,
2955 -- only the allocations between the Subcontracting Component
2956 -- and the Replenishment Sales Order Line would be deleted.
2957 -- The p_replen_so_line_id is simply ignored unless
2958 -- p_componenet_id is not null.
2959 -- EXCEPTIONS :
2960 --
2961 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
2962 --=============================================================================
2963 -- Delete All Allocations
2964 PROCEDURE Delete_Allocations
2965 ( p_api_version IN NUMBER
2966 , p_init_msg_list IN VARCHAR2
2967 , x_return_status OUT NOCOPY VARCHAR2
2968 , x_msg_count OUT NOCOPY NUMBER
2969 , x_msg_data OUT NOCOPY VARCHAR2
2970 , p_subcontract_po_shipment_id IN NUMBER
2971 , p_component_id IN NUMBER
2972 , p_replen_so_line_id IN NUMBER
2973 , x_deleted_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
2974 )
2975 IS
2976
2977 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Allocations';
2978 l_api_version CONSTANT NUMBER := 1.0;
2979
2980 l_tbl_index NUMBER;
2981 l_deleted_primary_uom_qty NUMBER;
2982 l_replen_uom VARCHAR2(3);
2983
2984 BEGIN
2985
2986 -- *** TO-DO: Update allocated_quantity and its primary UOM counterparts
2987 --
2988
2989 -- API Initialization
2990 Initialize
2991 ( p_api_version => l_api_version
2992 , p_input_api_version => p_api_version
2993 , p_api_name => l_api_name
2994 , p_init_msg_list => p_init_msg_list
2995 , x_return_status => x_return_status
2996 );
2997
2998 IF p_subcontract_po_shipment_id IS NOT NULL
2999 THEN
3000
3001 IF p_component_id IS NOT NULL
3002 THEN
3003
3004 IF p_replen_so_line_id IS NULL
3005 THEN
3006
3007 DELETE FROM jmf_shikyu_allocations
3008 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3009 AND shikyu_component_id = p_component_id
3010 RETURNING subcontract_po_shipment_id,
3011 replenishment_so_line_id,
3012 shikyu_component_id,
3013 allocated_quantity,
3014 uom
3015 BULK COLLECT INTO x_deleted_allocations_tbl;
3016
3017 ELSE
3018
3019 DELETE FROM jmf_shikyu_allocations
3020 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3021 AND shikyu_component_id = p_component_id
3022 AND replenishment_so_line_id = p_replen_so_line_id
3023 RETURNING subcontract_po_shipment_id,
3024 replenishment_so_line_id,
3025 shikyu_component_id,
3026 allocated_quantity,
3027 uom
3028 BULK COLLECT INTO x_deleted_allocations_tbl;
3029
3030 END IF; /* IF p_replen_so_line_id IS NULL */
3031
3032 ELSE /* IF p_component_id IS NULL */
3033
3034 DELETE FROM jmf_shikyu_allocations
3035 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3036 RETURNING subcontract_po_shipment_id,
3037 replenishment_so_line_id,
3038 shikyu_component_id,
3039 allocated_quantity,
3040 uom
3041 BULK COLLECT INTO x_deleted_allocations_tbl;
3042
3043 END IF; /* IF p_component_id IS NOT NULL */
3044
3045 ELSE
3046
3047 IF p_replen_so_line_id IS NOT NULL
3048 THEN
3049
3050 DELETE FROM jmf_shikyu_allocations
3051 WHERE replenishment_so_line_id = p_replen_so_line_id
3052 RETURNING subcontract_po_shipment_id,
3053 replenishment_so_line_id,
3054 shikyu_component_id,
3055 allocated_quantity,
3056 uom
3057 BULK COLLECT INTO x_deleted_allocations_tbl;
3058
3059 END IF; /* IF p_replen_so_line_id IS NOT NULL */
3060
3061 END IF; /* IF p_subcontract_po_shipment_id IS NOT NULL */
3062
3063 -- This would be NULL if the table is empty
3064 l_tbl_index := x_deleted_allocations_tbl.FIRST;
3065
3066 IF g_fnd_debug = 'Y' AND
3067 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3068 THEN
3069
3070 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3071 , G_MODULE_PREFIX
3072 , '>> ' || G_MODULE_PREFIX || l_api_name
3073 || ': x_deleted_allocations_tbl.FIRST = ' || NVL(TO_CHAR(l_tbl_index), 'NULL'));
3074 END IF;
3075
3076 IF l_tbl_index IS NOT NULL
3077 THEN
3078 LOOP
3079
3080 IF (g_fnd_debug = 'Y' AND
3081 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3082 THEN
3083 --dbms_output.put_line('In Loop : IF x_deleted_allocations_tbl IS NOT NULL AND x_deleted_allocations_tbl.COUNT > 0');
3084 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3085 , G_MODULE_PREFIX || l_api_name
3086 , 'In Loop.'
3087 );
3088 END IF;
3089
3090 -- Get UOM of the Replenishment SO Line
3091 SELECT jsr.uom
3092 INTO l_replen_uom
3093 FROM JMF_SHIKYU_REPLENISHMENTS jsr
3094 WHERE jsr.replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;
3095
3096 IF l_replen_uom <> x_deleted_allocations_tbl(l_tbl_index).qty_uom
3097 THEN
3098
3099 l_deleted_primary_uom_qty := INV_CONVERT.inv_um_convert
3100 ( item_id => x_deleted_allocations_tbl(l_tbl_index).component_id
3101 , precision => 5
3102 , from_quantity => x_deleted_allocations_tbl(l_tbl_index).qty
3103 , from_unit => x_deleted_allocations_tbl(l_tbl_index).qty_uom
3104 , to_unit => l_replen_uom
3105 , from_name => null
3106 , to_name => null
3107 );
3108 ELSE
3109
3110 l_deleted_primary_uom_qty := x_deleted_allocations_tbl(l_tbl_index).qty;
3111
3112 END IF;
3113
3114 UPDATE jmf_shikyu_replenishments
3115 SET allocated_quantity = allocated_quantity - l_deleted_primary_uom_qty,
3116 allocated_primary_uom_quantity
3117 = allocated_primary_uom_quantity - x_deleted_allocations_tbl(l_tbl_index).qty,
3118 last_update_date = sysdate,
3119 last_updated_by = FND_GLOBAL.user_id,
3120 last_update_login = FND_GLOBAL.login_id
3121 WHERE replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;
3122
3123 l_tbl_index := x_deleted_allocations_tbl.next(l_tbl_index);
3124 EXIT WHEN l_tbl_index IS NULL;
3125
3126 END LOOP;
3127 END IF; /* IF l_tbl_index IS NOT NULL */
3128
3129 IF (g_fnd_debug = 'Y' AND
3130 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3131 THEN
3132 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3133 , G_MODULE_PREFIX || l_api_name || '.end'
3134 , l_api_name || ' Exit');
3135 END IF;
3136
3137 EXCEPTION
3138
3139 WHEN OTHERS THEN
3140
3141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3142
3143 IF g_fnd_debug = 'Y' AND
3144 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3145 THEN
3146
3147 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3148 , G_MODULE_PREFIX || l_api_name || '.others_exception'
3149 , sqlerrm);
3150 END IF;
3151
3152 END Delete_Allocations;
3153
3154 --=============================================================================
3155 -- PROCEDURE NAME: Reconcile_Closed_Shipments
3156 -- TYPE : PUBLIC
3157 -- PARAMETERS :
3158 -- IN:
3159 --
3160 -- OUT:
3161 --
3162 -- DESCRIPTION :
3163 -- EXCEPTIONS :
3164 --
3165 -- CHANGE HISTORY: 31-MAY-05 VCHU Created.
3166 --=============================================================================
3167
3168 PROCEDURE Reconcile_Closed_Shipments
3169 ( p_api_version IN NUMBER
3170 , p_init_msg_list IN VARCHAR2
3171 , x_return_status OUT NOCOPY VARCHAR2
3172 , x_msg_count OUT NOCOPY NUMBER
3173 , x_msg_data OUT NOCOPY VARCHAR2
3174 )
3175 IS
3176
3177 l_api_name CONSTANT VARCHAR2(30) := 'Reconcile_Closed_Shipments';
3178 l_api_version CONSTANT NUMBER := 1.0;
3179
3180 -- Cursor to pick up the newly closed and over-shipped Replenishment
3181 -- SO Lines
3182 CURSOR c_over_ship_so_lines_cur IS
3183 SELECT jsr.replenishment_so_line_id,
3184 jsr.shikyu_component_id,
3185 oola.shipped_quantity,
3186 jsr.uom,
3187 oola.ordered_quantity,
3188 jsr.primary_uom,
3189 oola.schedule_ship_date,
3190 --Bugfix 14246759: Adding this because the record structure g_replen_so_qty_rec_type
3191 --has changed.
3192 oola.actual_shipment_date
3193 FROM jmf_shikyu_replenishments jsr,
3194 oe_order_lines_all oola
3195 WHERE jsr.replenishment_so_line_id = oola.line_id
3196 --Bugfix 14078692: Adding nvl.
3197 AND nvl(jsr.status, 'XXX') <> 'CLOSED'
3198 AND nvl(jsr.status,'XXX') <> 'CANCELLED'
3199 --AND oola.open_flag = 'N' --Bugfix 14078692: The line might not be closed.
3200 AND oola.shipped_quantity <> oola.ordered_quantity
3201 AND oola.shipped_quantity <> jsr.allocable_quantity
3202 --Bugfix 14078692: Additional conditions to pick up over-shipped
3203 --and under-shipped SO lines.
3204 AND oola.ordered_quantity = jsr.allocable_quantity
3205 AND (--Under-shipped SO lines.
3206 (oola.ordered_quantity - oola.shipped_quantity - (oola.ordered_quantity * (oola.ship_tolerance_below/100)) <= 0)
3207 OR
3208 --Over-shipped SO lines.
3209 (oola.shipped_quantity > oola.ordered_quantity)
3210 );
3211
3212 --Logic for finding under-shipped SO lines.
3213 --Let the scenario be as follows:
3214 --ordered_quantity = 10; shipped_quantity = 8; under_ship_tolerance = 20%
3215 --For this scenario, OM would fulfill this line without splitting.
3216 --The cursor should pick up the under-shipped line if OM can fulfill the line.
3217 --This would be possible in case [10-8-(10*20/100) = 0] is equal to or less than zero.
3218
3219 --For over-shipped lines, we do not need to consider over-ship tolerances. It's
3220 --sufficient if shipped_quantity > ordered_quantity.
3221
3222 l_closed_so_line_tbl g_replen_so_qty_tbl_type;
3223 l_closed_so_line_rec g_replen_so_qty_rec_type;
3224 l_tbl_index NUMBER;
3225 l_ordered_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
3226 l_ordered_primary_qty NUMBER;
3227
3228 BEGIN
3229
3230 --Debug
3231 IF (g_fnd_debug = 'Y' AND
3232 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3233 THEN
3234 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3235 , G_MODULE_PREFIX || l_api_name
3236 , 'Begin');
3237 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3238 , G_MODULE_PREFIX || l_api_name
3239 , 'l_api_version:' || l_api_version ||
3240 ':p_api_version:' || p_api_version);
3241 END IF;
3242
3243 -- API Initialization
3244 Initialize
3245 ( p_api_version => l_api_version
3246 , p_input_api_version => p_api_version
3247 , p_api_name => l_api_name
3248 , p_init_msg_list => p_init_msg_list
3249 , x_return_status => x_return_status
3250 );
3251
3252 OPEN c_over_ship_so_lines_cur;
3253 FETCH c_over_ship_so_lines_cur
3254 BULK COLLECT INTO l_closed_so_line_tbl;
3255
3256 l_tbl_index := l_closed_so_line_tbl.FIRST;
3257
3258 --Debug
3259 IF (g_fnd_debug = 'Y' AND
3260 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3261 THEN
3262 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3263 , G_MODULE_PREFIX || l_api_name
3264 , 'l_tbl_index:' || l_tbl_index ||
3265 ':Count:' || l_closed_so_line_tbl.COUNT);
3266 END IF;
3267
3268 IF l_closed_so_line_tbl.COUNT > 0
3269 THEN
3270
3271 LOOP
3272
3273 l_closed_so_line_rec := l_closed_so_line_tbl(l_tbl_index);
3274
3275 ---Bugfix 14078692: Creating a savepoint here.
3276 savepoint shipments;
3277
3278 --Debug
3279 IF (g_fnd_debug = 'Y' AND
3280 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3281 THEN
3282 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3283 , G_MODULE_PREFIX || l_api_name
3284 , 'line_id:' || l_closed_so_line_rec.replenishment_so_line_id);
3285 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3286 , G_MODULE_PREFIX || l_api_name
3287 , 'item_id:' || l_closed_so_line_rec.component_id);
3288 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3289 , G_MODULE_PREFIX || l_api_name
3290 , 'shipped_quantity:' || l_closed_so_line_rec.qty);
3291 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3292 , G_MODULE_PREFIX || l_api_name
3293 , 'uom:' || l_closed_so_line_rec.uom);
3294 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3295 , G_MODULE_PREFIX || l_api_name
3296 , 'ordered_quantity:' || l_closed_so_line_rec.primary_uom_qty);
3297 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3298 , G_MODULE_PREFIX || l_api_name
3299 , 'primary_uom:' || l_closed_so_line_rec.primary_uom);
3300 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3301 , G_MODULE_PREFIX || l_api_name
3302 , 'ssd:' || l_closed_so_line_rec.schedule_ship_date);
3303 END IF;
3304
3305 -- Since the primary UOM equivalence of the shipped quantity is not stored
3306 -- anywhere and needs to be calculated, we use the primary_uom_qty field of
3307 -- the l_closed_so_line_rec to store the ordered_quantity instead.
3308 l_ordered_qty := l_closed_so_line_rec.primary_uom_qty;
3309
3310 IF l_closed_so_line_rec.uom <> l_closed_so_line_rec.primary_uom
3311 THEN
3312
3313 l_closed_so_line_rec.primary_uom_qty
3314 := INV_CONVERT.inv_um_convert
3315 ( item_id => l_closed_so_line_rec.component_id
3316 , precision => 5
3317 , from_quantity => l_closed_so_line_rec.qty --shipped_quantity
3318 , from_unit => l_closed_so_line_rec.uom
3319 , to_unit => l_closed_so_line_rec.primary_uom
3320 , from_name => null
3321 , to_name => null
3322 );
3323
3324 l_ordered_primary_qty
3325 := INV_CONVERT.inv_um_convert
3326 ( item_id => l_closed_so_line_rec.component_id
3327 , precision => 5
3328 , from_quantity => l_ordered_qty
3329 , from_unit => l_closed_so_line_rec.uom
3330 , to_unit => l_closed_so_line_rec.primary_uom
3331 , from_name => null
3332 , to_name => null
3333 );
3334
3335 --Bugfix 14078692
3336 ELSE
3337
3338 l_closed_so_line_rec.primary_uom_qty := l_closed_so_line_rec.qty;
3339 l_ordered_primary_qty := l_ordered_qty;
3340
3341 END IF; /* IF l_closed_so_line_rec.uom <> l_closed_so_line_rec.primary_uom */
3342
3343 --Debug
3344 IF (g_fnd_debug = 'Y' AND
3345 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3346 THEN
3347 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3348 , G_MODULE_PREFIX || l_api_name
3349 , 'After Conversions:: l_ordered_qty:' || l_ordered_qty);
3350 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3351 , G_MODULE_PREFIX || l_api_name
3352 , 'After Conversions:: primary_uom_qty:' || l_closed_so_line_rec.primary_uom_qty);
3353 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3354 , G_MODULE_PREFIX || l_api_name
3355 , 'After Conversions:: l_ordered_primary_qty:' || l_ordered_primary_qty);
3356 END IF;
3357
3358 -- Under shipment case: if the SO Line is closed, but the shipped_quantity
3359 -- is less than the ordered_quantity
3360 IF l_ordered_qty > l_closed_so_line_rec.qty
3361 THEN
3362 -- Deallocate based on LIFO order of Need By Date of the Subcontracting
3363 -- Orders already allocated to the current Replenishment SO Line
3364 Reconcile_Replen_Excess_Qty
3365 ( p_api_version => 1.0
3366 , p_init_msg_list => p_init_msg_list
3367 , x_return_status => x_return_status
3368 , x_msg_count => x_msg_count
3369 , x_msg_data => x_msg_data
3370 , p_replen_order_line_id => l_closed_so_line_rec.replenishment_so_line_id
3371 , p_excess_qty => l_ordered_primary_qty - l_closed_so_line_rec.primary_uom_qty
3372 /*
3373 Bugfix 14078692: For undership scenario, l_closed_so_line_rec.primary_uom_qty - l_ordered_primary_qty
3374 would always be negative.
3375 , p_excess_qty => l_closed_so_line_rec.primary_uom_qty - l_ordered_primary_qty
3376 */
3377 );
3378
3379 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3380 THEN
3381 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3382 THEN
3383 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3384 , G_MODULE_PREFIX || l_api_name
3385 , 'Reconcile_Replen_Excess_Qty returned error. Status:' || x_return_status);
3386 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3387 , G_MODULE_PREFIX || l_api_name
3388 , 'Rolling back the processing for line_id:' || l_closed_so_line_rec.replenishment_so_line_id);
3389 END IF;
3390
3391 rollback to shipments;
3392
3393 --If this goto is not used, there might be a situation where JSR is updated
3394 --to proper allocable/allocated_quantities but JSA still has old allocation records
3395 --for the RSO because the above API failed. Hence a goto to skip
3396 --this line and process the next one.
3397 goto main_loop_ends;
3398
3399 ELSE
3400 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3401 THEN
3402 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3403 , G_MODULE_PREFIX || l_api_name
3404 , 'Reconcile_Replen_Excess_Qty returned success. Status:' || x_return_status);
3405 END IF;
3406 END IF;
3407
3408 END IF; /* IF l_ordered_qty > l_closed_so_line_rec.qty */
3409
3410 -- Updating the allocable quantity to the shipped quantity of the SO Line
3411 UPDATE JMF_SHIKYU_REPLENISHMENTS
3412 SET allocable_quantity = l_closed_so_line_rec.qty,
3413 allocable_primary_uom_quantity = l_closed_so_line_rec.primary_uom_qty,
3414 status = 'CLOSED',
3415 last_update_date = sysdate,
3416 last_updated_by = FND_GLOBAL.user_id,
3417 last_update_login = FND_GLOBAL.login_id
3418 WHERE replenishment_so_line_id = l_closed_so_line_rec.replenishment_so_line_id;
3419
3420 --Bugfix 14078692: Committing the successful transaction.
3421 commit;
3422
3423 <<main_loop_ends>>
3424 l_tbl_index := l_closed_so_line_tbl.next(l_tbl_index);
3425 EXIT WHEN l_tbl_index IS NULL;
3426
3427 END LOOP;
3428
3429 END IF; /* IF l_closed_so_line_tbl.COUNT > 0 */
3430
3431 CLOSE c_over_ship_so_lines_cur;
3432
3433 IF (g_fnd_debug = 'Y' AND
3434 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3435 THEN
3436 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3437 , G_MODULE_PREFIX || l_api_name || '.end'
3438 , l_api_name || ' Exit');
3439 END IF;
3440
3441 EXCEPTION
3442
3443 WHEN OTHERS THEN
3444 rollback;
3445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3446
3447 IF g_fnd_debug = 'Y' AND
3448 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3449 THEN
3450
3451 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3452 , G_MODULE_PREFIX || l_api_name || '.others_exception'
3453 , sqlerrm);
3454 END IF;
3455
3456 END Reconcile_Closed_Shipments;
3457
3458 --=============================================================================
3459 -- PROCEDURE NAME: Reconcile_Replen_Excess_Qty
3460 -- TYPE : PUBLIC
3461 -- PARAMETERS :
3462 -- IN:
3463 --
3464 -- OUT:
3465 --
3466 -- DESCRIPTION :
3467 -- EXCEPTIONS :
3468 --
3469 -- CHANGE HISTORY: 31-MAY-05 VCHU Created.
3470 --=============================================================================
3471
3472 PROCEDURE Reconcile_Replen_Excess_Qty
3473 ( p_api_version IN NUMBER
3474 , p_init_msg_list IN VARCHAR2
3475 , x_return_status OUT NOCOPY VARCHAR2
3476 , x_msg_count OUT NOCOPY NUMBER
3477 , x_msg_data OUT NOCOPY VARCHAR2
3478 , p_replen_order_line_id IN NUMBER
3479 , p_excess_qty IN NUMBER
3480 )
3481 IS
3482
3483 l_api_name CONSTANT VARCHAR2(30) := 'Reconcile_Replen_Excess_Qty';
3484 l_api_version CONSTANT NUMBER := 1.0;
3485
3486 TYPE l_allocation_detail_rec_type IS RECORD
3487 ( subcontract_po_shipment_id NUMBER
3488 , replenishment_so_line_id NUMBER
3489 , component_id NUMBER
3490 , qty NUMBER
3491 , qty_uom VARCHAR2(3)
3492 , po_shipment_need_by_date PO_LINE_LOCATIONS_ALL.NEED_BY_DATE%TYPE
3493 , po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE
3494 , po_line_num PO_LINES_ALL.LINE_NUM%TYPE
3495 , po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE
3496 );
3497
3498 TYPE l_allocation_detail_tbl_type IS TABLE OF l_allocation_detail_rec_type INDEX BY BINARY_INTEGER;
3499
3500 l_subcontract_po_shipment_id JMF_SUBCONTRACT_ORDERS.subcontract_po_shipment_id%TYPE;
3501 l_primary_uom JMF_SHIKYU_COMPONENTS.primary_uom%TYPE;
3502 l_component_id JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
3503 l_allocable_qty JMF_SHIKYU_REPLENISHMENTS.allocable_quantity%TYPE;
3504 l_allocable_primary_qty JMF_SHIKYU_REPLENISHMENTS.allocable_primary_uom_quantity%TYPE;
3505 l_allocated_primary_qty JMF_SHIKYU_REPLENISHMENTS.allocated_primary_uom_quantity%TYPE;
3506 l_unallocated_primary_qty NUMBER;
3507 l_shipped_primary_qty NUMBER;
3508 l_shipped_qty OE_ORDER_LINES_ALL.shipped_quantity%TYPE;
3509 l_ordered_uom OE_ORDER_LINES_ALL.order_quantity_uom%TYPE;
3510
3511 l_tbl_index NUMBER;
3512 l_qty_to_reduce NUMBER;
3513 l_actual_reduced_qty NUMBER;
3514 l_remaining_qty_to_reduce NUMBER;
3515
3516 l_reduced_allocations_rec g_allocation_qty_rec_type;
3517 l_reduced_allocations_tbl g_allocation_qty_tbl_type;
3518 l_allocations_tbl l_allocation_detail_tbl_type;
3519 l_allocations_rec l_allocation_detail_rec_type;
3520
3521 g_qty_not_fully_dealloc_exc EXCEPTION;
3522
3523 CURSOR c_subcontract_po_allocations IS
3524 SELECT jsa.subcontract_po_shipment_id,
3525 jsa.replenishment_so_line_id,
3526 jsa.shikyu_component_id,
3527 jsa.allocated_quantity,
3528 jsa.uom,
3529 NVL(plla.need_by_date, plla.promised_date),
3530 pha.segment1,
3531 pla.line_num,
3532 plla.shipment_num
3533 FROM JMF_SHIKYU_ALLOCATIONS jsa,
3534 PO_LINE_LOCATIONS_ALL plla,
3535 PO_LINES_ALL pla,
3536 PO_HEADERS_ALL pha
3537 WHERE jsa.replenishment_so_line_id = p_replen_order_line_id
3538 AND jsa.shikyu_component_id = l_component_id
3539 AND jsa.subcontract_po_shipment_id = plla.line_location_id
3540 AND plla.po_line_id = pla.po_line_id
3541 AND plla.po_header_id = pha.po_header_id
3542 ORDER BY NVL(plla.need_by_date, plla.promised_date) DESC,
3543 pha.segment1 DESC,
3544 pla.line_num DESC,
3545 plla.shipment_num DESC;
3546
3547 BEGIN
3548
3549 IF g_fnd_debug = 'Y' AND
3550 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3551 THEN
3552 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3553 , G_MODULE_PREFIX || l_api_name
3554 , 'Begin');
3555 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3556 , G_MODULE_PREFIX || l_api_name
3557 , 'Parameters passed:: p_replen_order_line_id:' || p_replen_order_line_id ||
3558 ':p_excess_qty:' || p_excess_qty);
3559 END IF;
3560
3561 -- Start API initialization
3562 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE))
3563 THEN
3564 FND_MSG_PUB.initialize;
3565 END IF;
3566
3567 IF NOT FND_API.Compatible_API_Call( l_api_version
3568 , p_api_version
3569 , l_api_name
3570 , G_PKG_NAME
3571 )
3572 THEN
3573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3574 END IF;
3575
3576 x_return_status := FND_API.G_RET_STS_SUCCESS;
3577
3578 -- End API initialization
3579
3580 Select jsr.shikyu_component_id,
3581 jsr.primary_uom,
3582 jsr.allocable_primary_uom_quantity,
3583 jsr.allocated_primary_uom_quantity,
3584 oola.shipped_quantity,
3585 oola.order_quantity_uom
3586 INTO l_component_id,
3587 l_primary_uom,
3588 l_allocable_primary_qty,
3589 l_allocated_primary_qty,
3590 l_shipped_primary_qty,
3591 l_ordered_uom
3592 FROM JMF_SHIKYU_REPLENISHMENTS jsr,
3593 OE_ORDER_LINES_ALL oola
3594 WHERE jsr.REPLENISHMENT_SO_LINE_ID = p_replen_order_line_id
3595 AND jsr.replenishment_so_line_id = oola.line_id;
3596
3597 l_unallocated_primary_qty := l_allocable_primary_qty - l_allocated_primary_qty;
3598
3599 --Debug
3600 IF g_fnd_debug = 'Y' AND
3601 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3602 THEN
3603 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3604 , G_MODULE_PREFIX || l_api_name
3605 , 'l_component_id:' || l_component_id ||
3606 ':l_primary_uom:' || l_primary_uom ||
3607 ':l_ordered_uom:' || l_ordered_uom ||
3608 ':l_shipped_primary_qty:' || l_shipped_primary_qty ||
3609 ':l_allocable_primary_qty:' || l_allocable_primary_qty ||
3610 ':l_allocated_primary_qty:' || l_allocated_primary_qty ||
3611 ':l_unallocated_primary_qty:' || l_unallocated_primary_qty
3612 );
3613 END IF;
3614
3615 -- If the excess quantity <= the remaining unallocated quantity of
3616 -- the Replenishment SO Line, and thus no existing allocations need
3617 -- to be deallocated.
3618 IF p_excess_qty = 0 OR p_excess_qty <= l_unallocated_primary_qty
3619 THEN
3620 --
3621 -- Bug 9883090
3622 -- Instead of returning without any processing, reduce the replenishment
3623 -- from JMF_SHIKYU_REPLENISHMENTS by an appropriate amount.
3624 -- skolluku
3625 --
3626 -- Calculate the new allocable quantity by subtracting the excess quantity
3627 l_allocable_primary_qty := l_allocable_primary_qty - p_excess_qty;
3628 -- Convert the new allocable qty to primary uom if uom of SO Line <> primary uom
3629 IF l_allocable_primary_qty = 0 THEN
3630 l_allocable_qty := 0;
3631 ELSE
3632 IF l_primary_uom = l_ordered_uom
3633 THEN
3634 l_allocable_qty := l_allocable_primary_qty;
3635 ELSE
3636 l_allocable_qty := INV_CONVERT.inv_um_convert
3637 ( item_id => l_component_id
3638 , precision => 5
3639 , from_quantity => l_allocable_primary_qty
3640 , from_unit => l_primary_uom
3641 , to_unit => l_ordered_uom
3642 , from_name => null
3643 , to_name => null
3644 );
3645 END IF;
3646 END IF;
3647
3648 -- Update the allocable qty of the Replensiment SO Line being reconciled, so that
3649 -- it will not be reallocated again to the Subcontracting Order Shipments being
3650 -- deallocated from it in order to reconcile the excess qty
3651 UPDATE JMF_SHIKYU_REPLENISHMENTS
3652 SET allocable_quantity = l_allocable_qty,
3653 allocable_primary_uom_quantity = l_allocable_primary_qty,
3654 last_update_date = sysdate,
3655 last_updated_by = FND_GLOBAL.user_id,
3656 last_update_login = FND_GLOBAL.login_id
3657 WHERE replenishment_so_line_id = p_replen_order_line_id;
3658 RETURN;
3659 END IF;
3660 /*
3661 -- Convert shipped_qty to primary uom if uom of SO Line <> primary uom
3662 IF l_primary_uom = l_ordered_uom
3663 THEN
3664 l_shipped_primary_qty := l_shipped_qty;
3665 ELSE
3666 l_shipped_primary_qty := INV_CONVERT.inv_um_convert
3667 ( item_id => l_component_id
3668 , precision => 5
3669 , from_quantity => l_shipped_qty
3670 , from_unit => l_ordered_uom
3671 , to_unit => l_primary_uom
3672 , from_name => null
3673 , to_name => null
3674 );
3675 END IF;
3676 */
3677
3678 -- Deallocate returned quantity
3679 IF p_excess_qty >= l_allocable_primary_qty
3680 THEN
3681
3682 -- Remove all allocations of the Replenishment SO Line
3683 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
3684 ( P_API_VERSION => 1.0
3685 , P_INIT_MSG_LIST => p_init_msg_list
3686 , X_RETURN_STATUS => x_return_status
3687 , X_MSG_COUNT => x_msg_count
3688 , X_MSG_DATA => x_msg_data
3689 , P_SUBCONTRACT_PO_SHIPMENT_ID => NULL
3690 , P_COMPONENT_ID => NULL
3691 , P_REPLEN_SO_LINE_ID => p_replen_order_line_id
3692 , X_DELETED_ALLOCATIONS_TBL => l_reduced_allocations_tbl
3693 );
3694
3695 -- Remove the Replenishment SO Line from the JMF_SHIKYU_REPLENISHMENTS table,
3696 -- since excess qty = allocable qty, and hence there are no available qty on
3697 -- this Replenishment SO Line anymore
3698 --
3699 -- Bug 9883090
3700 -- Instead of deleting, update the record to set the allocable
3701 -- quantity to 0, so that no new RSO is created for the same PO.
3702 -- skolluku
3703 --
3704 --DELETE FROM jmf_shikyu_replenishments
3705 --WHERE replenishment_so_line_id = p_replen_order_line_id;
3706 UPDATE JMF_SHIKYU_REPLENISHMENTS
3707 SET allocable_quantity = 0,
3708 allocable_primary_uom_quantity = 0,
3709 allocated_quantity = 0,
3710 allocated_primary_uom_quantity = 0,
3711 last_update_date = sysdate,
3712 last_updated_by = FND_GLOBAL.user_id,
3713 last_update_login = FND_GLOBAL.login_id
3714 WHERE replenishment_so_line_id = p_replen_order_line_id;
3715
3716 -- Loop through the table containing the subcontracting orders
3717 -- being deallocated by the Delete_Allocations procedure,
3718 -- and then reallocate them by calling Create_New_Allocations
3719 IF l_reduced_allocations_tbl.COUNT > 0
3720 THEN
3721
3722 l_tbl_index := l_reduced_allocations_tbl.FIRST;
3723
3724 LOOP
3725 l_reduced_allocations_rec := l_reduced_allocations_tbl(l_tbl_index);
3726
3727 Create_New_Allocations
3728 ( p_api_version => 1.0
3729 , p_init_msg_list => p_init_msg_list
3730 , x_return_status => x_return_status
3731 , x_msg_count => x_msg_count
3732 , x_msg_data => x_msg_data
3733 , p_subcontract_po_shipment_id => l_reduced_allocations_rec.subcontract_po_shipment_id
3734 , p_component_id => l_reduced_allocations_rec.component_id
3735 , p_qty => l_reduced_allocations_rec.qty
3736 , p_skip_po_replen_creation => 'N'
3737 );
3738
3739 l_tbl_index := l_reduced_allocations_tbl.next(l_tbl_index);
3740 EXIT WHEN l_tbl_index IS NULL;
3741
3742 END LOOP;
3743
3744 END IF;
3745
3746 ELSE --p_excess_qty < l_allocable_primary_qty
3747
3748 /*
3749 Code would come here in case allocated_quantity > shipped_quantity.
3750 Consider two scenarios:
3751 Scenario Ordered Shipped Allocable Allocated Excess Unallocated
3752 1 10 9 10 8 1 2
3753 2 10 8 10 9 2 1
3754
3755 For scenario 1, since the shipped > allocated, we do not need to change
3756 any allocations. This part would be taken care of by the first if condition:
3757 p_excess_qty <= l_unallocated_primary_qty.
3758
3759 Scenario 2 (shipped < allocated) would be taken care of here.
3760 */
3761
3762 IF g_fnd_debug = 'Y' AND
3763 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3764 THEN
3765 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3766 , G_MODULE_PREFIX || l_api_name
3767 , 'Came into p_excess_qty < l_allocable_primary_qty');
3768 END IF;
3769
3770 -- Calculate the actual allocated quantity that needs to be reduced
3771 l_unallocated_primary_qty := l_allocable_primary_qty - l_allocated_primary_qty;
3772 l_remaining_qty_to_reduce := p_excess_qty - l_unallocated_primary_qty;
3773
3774 -- Calculate the new allocable quantity by subtracting the excess quantity
3775 l_allocable_primary_qty := l_allocable_primary_qty - p_excess_qty;
3776
3777 -- Convert the new allocable qty to primary uom if uom of SO Line <> primary uom
3778 IF l_primary_uom = l_ordered_uom
3779 THEN
3780 l_allocable_qty := l_allocable_primary_qty;
3781 ELSE
3782 l_allocable_qty := INV_CONVERT.inv_um_convert
3783 ( item_id => l_component_id
3784 , precision => 5
3785 , from_quantity => l_allocable_primary_qty
3786 , from_unit => l_primary_uom
3787 , to_unit => l_ordered_uom
3788 , from_name => null
3789 , to_name => null
3790 );
3791 END IF;
3792
3793 --Debug
3794 IF g_fnd_debug = 'Y' AND
3795 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3796 THEN
3797 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3798 , G_MODULE_PREFIX || l_api_name
3799 , 'After calculations::l_unallocated_primary_qty:' || l_unallocated_primary_qty ||
3800 ':l_remaining_qty_to_reduce:' || l_remaining_qty_to_reduce ||
3801 ':l_allocable_primary_qty_new:' || l_allocable_primary_qty ||
3802 ':l_allocable_qty:' || l_allocable_qty
3803 );
3804 END IF;
3805
3806 -- Update the allocable qty of the Replensiment SO Line being reconciled, so that
3807 -- it will not be reallocated again to the Subcontracting Order Shipments being
3808 -- deallocated from it in order to reconcile the excess qty
3809 UPDATE JMF_SHIKYU_REPLENISHMENTS
3810 SET allocable_quantity = l_allocable_qty,
3811 allocable_primary_uom_quantity = l_allocable_primary_qty,
3812 last_update_date = sysdate,
3813 last_updated_by = FND_GLOBAL.user_id,
3814 last_update_login = FND_GLOBAL.login_id
3815 WHERE replenishment_so_line_id = p_replen_order_line_id;
3816
3817 OPEN c_subcontract_po_allocations;
3818
3819 FETCH c_subcontract_po_allocations
3820 BULK COLLECT INTO l_allocations_tbl;
3821
3822 --Debug
3823 IF g_fnd_debug = 'Y' AND
3824 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3825 THEN
3826 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3827 , G_MODULE_PREFIX || l_api_name
3828 , 'Count of SPOs fetched::' || l_allocations_tbl.count
3829 );
3830 END IF;
3831
3832 IF l_allocations_tbl.COUNT > 0
3833 THEN
3834
3835 l_tbl_index := l_allocations_tbl.FIRST;
3836
3837 LOOP
3838
3839 l_allocations_rec := l_allocations_tbl(l_tbl_index);
3840
3841 --Debug
3842 IF g_fnd_debug = 'Y' AND
3843 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3844 THEN
3845 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3846 , G_MODULE_PREFIX || l_api_name
3847 , '****************Printing the fetched SPO******************'
3848 );
3849 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3850 , G_MODULE_PREFIX || l_api_name
3851 , 'SPO_Shipment_Id:' || l_allocations_rec.subcontract_po_shipment_id ||
3852 ':Component_Id:' || l_allocations_rec.component_id ||
3853 ':SPO_Qty:' || l_allocations_rec.qty ||
3854 ':l_remaining_qty_to_reduce:' || l_remaining_qty_to_reduce
3855 );
3856 END IF;
3857
3858 IF l_remaining_qty_to_reduce < l_allocations_rec.qty
3859 THEN
3860 l_qty_to_reduce := l_remaining_qty_to_reduce;
3861 ELSE
3862 l_qty_to_reduce := l_allocations_rec.qty;
3863 END IF;
3864
3865 --Debug
3866 IF g_fnd_debug = 'Y' AND
3867 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3868 THEN
3869 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3870 , G_MODULE_PREFIX || l_api_name
3871 , 'l_qty_to_reduce:' || l_qty_to_reduce
3872 );
3873 END IF;
3874
3875 Reduce_Allocations
3876 ( p_api_version => 1.0
3877 , p_init_msg_list => p_init_msg_list
3878 , x_return_status => x_return_status
3879 , x_msg_count => x_msg_count
3880 , x_msg_data => x_msg_data
3881 , p_subcontract_po_shipment_id => l_allocations_rec.subcontract_po_shipment_id
3882 , p_component_id => l_allocations_rec.component_id
3883 , p_replen_so_line_id => p_replen_order_line_id
3884 , p_qty_to_reduce => l_qty_to_reduce
3885 , x_actual_reduced_qty => l_actual_reduced_qty
3886 , x_reduced_allocations_tbl => l_reduced_allocations_tbl
3887 );
3888
3889 --Debug
3890 IF g_fnd_debug = 'Y' AND
3891 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3892 THEN
3893 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3894 , G_MODULE_PREFIX || l_api_name
3895 , 'After Reduce_Allocations:: Count:' || l_reduced_allocations_tbl.COUNT ||
3896 ':Qty_Reduced:' || l_actual_reduced_qty
3897 );
3898 END IF;
3899
3900 IF l_reduced_allocations_tbl.COUNT > 0
3901 THEN
3902 Create_New_Allocations
3903 ( p_api_version => 1.0
3904 , p_init_msg_list => p_init_msg_list
3905 , x_return_status => x_return_status
3906 , x_msg_count => x_msg_count
3907 , x_msg_data => x_msg_data
3908 , p_subcontract_po_shipment_id => l_reduced_allocations_tbl(1).subcontract_po_shipment_id
3909 , p_component_id => l_reduced_allocations_tbl(1).component_id
3910 , p_qty => l_reduced_allocations_tbl(1).qty
3911 , p_skip_po_replen_creation => 'N'
3912 );
3913
3914 l_remaining_qty_to_reduce := l_remaining_qty_to_reduce - l_qty_to_reduce;
3915
3916 --Debug
3917 IF g_fnd_debug = 'Y' AND
3918 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3919 THEN
3920 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3921 , G_MODULE_PREFIX || l_api_name
3922 , 'Post processing:: l_remaining_qty_to_reduce:' || l_remaining_qty_to_reduce
3923 );
3924 END IF;
3925 END IF;
3926
3927 l_tbl_index := l_allocations_tbl.next(l_tbl_index);
3928 EXIT WHEN l_tbl_index IS NULL OR l_remaining_qty_to_reduce <= 0;
3929
3930 END LOOP;
3931
3932 IF l_remaining_qty_to_reduce > 0
3933 THEN
3934 RAISE g_qty_not_fully_dealloc_exc;
3935 ELSE
3936 x_return_status := FND_API.G_RET_STS_SUCCESS;
3937 END IF;
3938
3939 END IF;
3940 END IF;
3941
3942 IF (g_fnd_debug = 'Y' AND
3943 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3944 THEN
3945 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
3946 , G_MODULE_PREFIX || l_api_name || '.end'
3947 , l_api_name || ' Exit');
3948 END IF;
3949
3950 EXCEPTION
3951 WHEN NO_DATA_FOUND THEN
3952 /*
3953 FND_MSG_PUB.Count_And_Get
3954 ( p_count => x_msg_count
3955 , p_data => x_msg_data
3956 );
3957 */
3958 x_return_status := FND_API.G_RET_STS_ERROR;
3959 IF g_fnd_debug = 'Y' AND
3960 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3961 THEN
3962 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
3963 , G_MODULE_PREFIX || l_api_name || '.NO_DATA_FOUND'
3964 , 'No Data Found - Replenishment Sales Order Line: ' || p_replen_order_line_id);
3965 END IF;
3966
3967 WHEN g_qty_not_fully_dealloc_exc THEN
3968 x_return_status := FND_API.G_RET_STS_ERROR;
3969 IF g_fnd_debug = 'Y' AND
3970 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3971 THEN
3972 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
3973 , G_MODULE_PREFIX || l_api_name || '.g_qty_not_fully_dealloc_exc'
3974 , 'Excess Quantity of Replenishment Sales Order Line ' || p_replen_order_line_id
3975 || ' cannot be fully deallocated');
3976 END IF;
3977
3978 WHEN OTHERS THEN
3979
3980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3981
3982 IF g_fnd_debug = 'Y' AND
3983 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3984 THEN
3985
3986 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3987 , G_MODULE_PREFIX || l_api_name || '.others_exception'
3988 , sqlerrm);
3989 END IF;
3990
3991 END Reconcile_Replen_Excess_Qty;
3992
3993 ---------------------------------------------------------------------
3994 -- PROCEDURE Reconcile_Partial_Shipments
3995 -- Comments: THis api will reconcile the following scenarios
3996 -- 1) SO replenishments partially shipped / line split
3997 -- 2) SO replenishment lines canceled
3998 -----------------------------------------------------------------------
3999 PROCEDURE Reconcile_Partial_Shipments
4000 ( p_api_version IN NUMBER
4001 , p_init_msg_list IN VARCHAR2
4002 , x_return_status OUT NOCOPY VARCHAR2
4003 , x_msg_count OUT NOCOPY NUMBER
4004 , x_msg_data OUT NOCOPY VARCHAR2
4005 , p_from_organization IN NUMBER
4006 , p_to_organization IN NUMBER
4007 )
4008 IS
4009
4010 l_api_name CONSTANT VARCHAR2(30) := 'Reconcile_Partial_Shipments';
4011 l_api_version CONSTANT NUMBER := 1.0;
4012
4013 l_deleted_qty NUMBER;
4014 l_total_qty NUMBER;
4015 l_decreased_qty NUMBER;
4016 l_allocated_quantity NUMBER;
4017 l_parent_so_line_id NUMBER;
4018 l_return_status VARCHAR2(3);
4019 l_msg_count NUMBER;
4020 l_msg_data VARCHAR2(300);
4021 l_header_id NUMBER;
4022 l_primary_uom_qty NUMBER;
4023 l_uom
4024 MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE;
4025 l_primary_uom
4026 MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE;
4027
4028 l_reduced_allocations_tbl
4029 JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;
4030
4031 l_deleted_allocations_tbl
4032 JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;
4033
4034 --Bugfix 14078692
4035 --1. There should be an outer join between jsa and jsr. If a RPO/RSO pair is not allocated to any
4036 --SPO, and the RSO is cancelled, this cursor would not pick up that RSO in absence of an outer join.
4037 --2. Modifying the cursor to take care of RSO quantity changes. The quantity condition is modified to:
4038 --oel.ordered_quantity <> jsr.allocable_quantity.
4039 CURSOR C_SHIKYU_REPLENISHMENT_CSR IS
4040 SELECT jsr.replenishment_so_line_id replenishment_so_line_id
4041 , jsr.replenishment_so_header_id replenishment_so_header_id
4042 , jsr.schedule_ship_date schedule_ship_date
4043 , jsr.replenishment_po_header_id replenishment_po_header_id
4044 , jsr.replenishment_po_line_id replenishment_po_line_id
4045 , jsr.replenishment_po_shipment_id replenishment_po_shipment_id
4046 , jsr.oem_organization_id oem_organization_id
4047 , jsr.tp_organization_id tp_organization_id
4048 , oeh.cancelled_flag oeh_cancelled_flag
4049 , oel.cancelled_flag oel_cancelled_flag
4050 , oel.shipped_quantity oel_shipped_quantity
4051 , oel.ordered_quantity oel_ordered_quantity
4052 , jsr.shikyu_component_id shikyu_component_id
4053 , jsr.ORDERED_QUANTITY jsr_ordered_quantity
4054 , jsr.ALLOCATED_PRIMARY_UOM_QUANTITY allocated_primary_UOM_quantity
4055 --, oel.closed_flag closed_flag
4056 , jsa.subcontract_po_shipment_id subcontract_po_shipment_id
4057 , jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY allocable_primary_UOM_quantity
4058 , jsr.allocable_quantity allocable_quantity
4059 , jsr.allocated_quantity allocated_quantity
4060 FROM JMF_SHIKYU_REPLENISHMENTS jsr
4061 , OE_ORDER_LINES_ALL oel
4062 , OE_ORDER_HEADERS_ALL oeh
4063 , JMF_SHIKYU_ALLOCATIONS jsa
4064 WHERE oeh.header_id = jsr.REPLENISHMENT_SO_HEADER_ID
4065 AND oel.header_id = oeh.header_id
4066 AND oel.line_id = jsr.replenishment_so_line_id
4067 AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
4068 AND jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
4069 --Bugfix 14078692: Picking up only positive quantities.
4070 AND jsr.allocable_quantity > 0
4071 AND ( oeh.cancelled_flag = 'Y' OR
4072 oel.cancelled_flag = 'Y' OR
4073 --Bugfix 14078692: Considering quantity modifications also.
4074 --oel.ordered_quantity < jsr.allocable_quantity );
4075 oel.ordered_quantity <> jsr.allocable_quantity );
4076 -- oel.ordered_quantity < jsr.ordered_quantity
4077
4078
4079 /*Bug 7383574: Changed the cursor to have inline view to avoid FTS*/
4080 CURSOR C_child_so_lines_CSR IS
4081 SELECT line_id
4082 , ordered_quantity
4083 , schedule_ship_date
4084 , header_id
4085 , split_from_line_id
4086 , line_number FROM (
4087 SELECT line_id
4088 , ordered_quantity
4089 , schedule_ship_date
4090 , header_id
4091 , split_from_line_id
4092 , line_number
4093 FROM oe_order_lines_all
4094 WHERE header_id = l_header_id )
4095 CONNECT BY PRIOR line_id = split_from_line_id
4096 START WITH line_id = l_parent_so_line_id;
4097
4098 --originally replaced by rajesh for the partial reconcile bug5166092
4099 -- introduced again for 5437721
4100
4101 /*
4102 CURSOR C_child_so_lines_CSR IS
4103 SELECT line_id
4104 , ordered_quantity
4105 , schedule_ship_date
4106 , header_id
4107 , split_from_line_id
4108 FROM oe_order_lines_all
4109 WHERE header_id = l_header_id
4110 and split_from_line_id = l_parent_so_line_id ;
4111 */
4112 -- commented out for bug 5437721. The CONNECT BY should work
4113
4114
4115 BEGIN
4116
4117 -- In this api the following logic is used ( overall logic )
4118 -- From the JMF replenishments table, select the SO replenishment lines
4119 -- which have been either
4120 -- 1) Split OR
4121 -- 2) Cancel|
4122 -- If split :
4123 -- 1) Reduce the allocations ( if components already allocated)
4124 -- 2) update the JMF replenishments table for the parent SO line rec
4125 -- 3) Insert new records for the split child lines in the
4126 -- replenishments table
4127 -- 4) If in step (1) qty allocations were reduced, re-allocate them
4128 -- invoking the allocation api's
4129
4130 -- IF CANCEL:
4131 -- 1) Reduce the allocations ( if components already allocated)
4132 -- 2) remove the record from the replenishments table
4133 -- 3) If in step (1) qty allocations were reduced, re-allocate them
4134 -- invoking the allocation api's
4135
4136 IF g_fnd_debug = 'Y' AND
4137 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4138 THEN
4139 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4140 ,G_MODULE_PREFIX || l_api_name
4141 ,'Begin');
4142 END IF;
4143
4144 x_return_status := FND_API.G_RET_STS_SUCCESS;
4145
4146 -- API Initialization
4147 /*Initialize
4148 ( p_api_version => l_api_version
4149 , p_input_api_version => p_api_version
4150 , p_api_name => l_api_name
4151 , p_init_msg_list => p_init_msg_list
4152 , x_return_status => x_return_status
4153 );
4154 */
4155
4156 IF g_fnd_debug = 'Y' AND
4157 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4158 THEN
4159 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4160 , G_MODULE_PREFIX || l_api_name
4161 , 'p_from_organization => ' || p_from_organization
4162 );
4163 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4164 , G_MODULE_PREFIX || l_api_name
4165 , 'p_to_organization => ' || p_to_organization
4166 );
4167 END IF;
4168
4169 FOR C_SHIKYU_REPLENISHMENT_rec IN C_SHIKYU_REPLENISHMENT_CSR
4170 LOOP
4171 BEGIN
4172 --Bugfix 14078692: Creating a savepoint. This would enable us to rollback
4173 --the processing only for one failed line.
4174 savepoint qty_change;
4175
4176 l_deleted_qty := 0 ;
4177 l_decreased_qty := 0 ;
4178 l_allocated_quantity := 0 ;
4179 l_parent_so_line_id := NULL ;
4180 l_header_id := NULL ;
4181 l_parent_so_line_id := C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID ;
4182 l_header_id := C_SHIKYU_REPLENISHMENT_rec.replenishment_so_header_id ;
4183
4184 IF g_fnd_debug = 'Y'
4185 THEN
4186 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4187 THEN
4188 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4189 , G_MODULE_PREFIX || l_api_name
4190 , '==============================================='
4191 );
4192 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4193 , G_MODULE_PREFIX || l_api_name
4194 , 'l_parent_so_line_id:' || l_parent_so_line_id
4195 );
4196 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4197 , G_MODULE_PREFIX || l_api_name
4198 , 'l_header_id:' || l_header_id
4199 );
4200 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4201 , G_MODULE_PREFIX || l_api_name
4202 , 'OEL_Cancelled_Flag:' || C_SHIKYU_REPLENISHMENT_rec.oel_cancelled_flag
4203 );
4204 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4205 , G_MODULE_PREFIX || l_api_name
4206 , 'OEH.Cancelled_Flag:' || C_SHIKYU_REPLENISHMENT_rec.oeh_cancelled_flag
4207 );
4208 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4209 , G_MODULE_PREFIX || l_api_name
4210 , 'jsr_ordered_quantity:' || C_SHIKYU_REPLENISHMENT_rec.jsr_ordered_quantity
4211 );
4212 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4213 , G_MODULE_PREFIX || l_api_name
4214 , 'replenishment_po_shipment_id:' || C_SHIKYU_REPLENISHMENT_rec.replenishment_po_shipment_id
4215 );
4216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4217 , G_MODULE_PREFIX || l_api_name
4218 , 'allocated_primary_UOM_quantity:' || C_SHIKYU_REPLENISHMENT_rec.allocated_primary_UOM_quantity
4219 );
4220 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4221 , G_MODULE_PREFIX || l_api_name
4222 , 'allocable_primary_UOM_quantity:' || C_SHIKYU_REPLENISHMENT_rec.allocable_primary_UOM_quantity
4223 );
4224 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4225 , G_MODULE_PREFIX || l_api_name
4226 , 'allocable_quantity:' || C_SHIKYU_REPLENISHMENT_rec.allocable_quantity
4227 );
4228 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4229 , G_MODULE_PREFIX || l_api_name
4230 , 'allocated_quantity:' || C_SHIKYU_REPLENISHMENT_rec.allocated_quantity
4231 );
4232 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4233 , G_MODULE_PREFIX || l_api_name
4234 , 'oel_ordered_quantity:' || C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity
4235 );
4236 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4237 , G_MODULE_PREFIX || l_api_name
4238 , 'oel_shipped_quantity:' || C_SHIKYU_REPLENISHMENT_rec.oel_shipped_quantity
4239 );
4240 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4241 , G_MODULE_PREFIX || l_api_name
4242 , 'subcontract_po_shipment_id:' || C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id);
4243 END IF;
4244 END IF;
4245
4246 --Bugfix 14078692: When the order quantity > allocable quantity.
4247 --This would happen when users manually increase the RSO quantity.
4248 --Adding the quantity increase code before cancelled or quantity decrease
4249 --code because the extra quantity from this increase can be allocated in
4250 --the other two scenarios.
4251 IF C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity >
4252 C_SHIKYU_REPLENISHMENT_rec.allocable_quantity
4253 THEN
4254
4255 --Calculate the primary UOM quantity.
4256 --Update the quantity in JSR.
4257
4258 IF g_fnd_debug = 'Y' AND
4259 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4260 THEN
4261 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4262 , G_MODULE_PREFIX || l_api_name
4263 , 'Ordered_Quantity > Allocable_Quantity');
4264 END IF;
4265
4266 --Get the ordered and Primary UOMs.
4267 SELECT UOM,
4268 PRIMARY_UOM
4269 INTO l_uom,
4270 l_primary_uom
4271 FROM JMF_SHIKYU_REPLENISHMENTS
4272 WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
4273
4274 IF g_fnd_debug = 'Y' AND
4275 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4276 THEN
4277 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4278 , G_MODULE_PREFIX || l_api_name
4279 , 'l_uom:' || l_uom ||
4280 ':l_primary_uom:' || l_primary_uom
4281 );
4282 END IF;
4283
4284 -- Converting the new allocable quantity of the parent SO Line
4285 -- to the primary UOM
4286 IF l_uom <> l_primary_uom
4287 THEN
4288
4289 l_primary_uom_qty
4290 := INV_CONVERT.inv_um_convert
4291 ( item_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4292 , precision => 5
4293 , from_quantity => C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity
4294 , from_unit => l_uom
4295 , to_unit => l_primary_uom
4296 , from_name => null
4297 , to_name => null
4298 );
4299
4300 ELSE
4301
4302 l_primary_uom_qty := C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity;
4303
4304 END IF;
4305
4306 IF g_fnd_debug = 'Y' AND
4307 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4308 THEN
4309 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4310 , G_MODULE_PREFIX || l_api_name
4311 , 'l_primary_uom_qty:' || l_primary_uom_qty ||
4312 ':Old allocable qty:' || C_SHIKYU_REPLENISHMENT_rec.allocable_primary_UOM_quantity );
4313 END IF;
4314
4315 --Updating the JSR with new quantities.
4316 UPDATE JMF_SHIKYU_REPLENISHMENTS
4317 SET allocable_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
4318 allocable_primary_uom_quantity = l_primary_uom_qty,
4319 ordered_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
4320 ordered_primary_uom_quantity = l_primary_uom_qty,
4321 last_update_date = sysdate,
4322 last_updated_by = FND_GLOBAL.user_id,
4323 last_update_login = FND_GLOBAL.login_id
4324 WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
4325
4326 IF g_fnd_debug = 'Y' AND
4327 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4328 THEN
4329 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4330 , G_MODULE_PREFIX || l_api_name
4331 , 'Updated rows:' || sql%rowcount ||
4332 ':for RSO line_id:' || C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4333 );
4334 END IF;
4335
4336 --End order quantity > allocable quantity.
4337
4338 --Bugfix 14078692: Changing the IF to ELSIF.
4339 ELSIF C_SHIKYU_REPLENISHMENT_rec.oel_cancelled_flag = 'Y' OR
4340 C_SHIKYU_REPLENISHMENT_rec.oeh_cancelled_flag = 'Y'
4341 THEN
4342 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4343 THEN
4344 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4345 , 'JMFVSKAB :Invoke JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations '
4346 , C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id);
4347 END IF;
4348
4349 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
4350 ( p_api_version => 1.0
4351 , p_init_msg_list => NULL
4352 , x_return_status => l_return_status
4353 , x_msg_count => l_msg_count
4354 , x_msg_data => l_msg_data
4355 , p_subcontract_po_shipment_id =>
4356 C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
4357 , p_component_id =>
4358 C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4359 , p_replen_so_line_id =>
4360 C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4361 , x_deleted_allocations_tbl => l_deleted_allocations_tbl
4362 );
4363
4364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4365 THEN
4366 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4367 THEN
4368 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4369 , G_MODULE_PREFIX || l_api_name
4370 , 'Delete allocations returned error. Status:' || l_return_status);
4371 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4372 , G_MODULE_PREFIX || l_api_name
4373 , 'Rolling back the processing for line_id:' || C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID);
4374 END IF;
4375
4376 rollback to qty_change;
4377
4378 --If this goto is not used, there might be a situation where JSR is updated
4379 --to allocable/allocated_quantities = 0 but JSA still has allocation records
4380 --for the RSO because delete_allocations API failed. Hence a goto to skip
4381 --this line and process the next one.
4382 goto main_loop_ends;
4383
4384 ELSE
4385 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4386 THEN
4387 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4388 , G_MODULE_PREFIX || l_api_name
4389 , 'Delete allocations returned success. Status:' || l_return_status);
4390 END IF;
4391 END IF;
4392
4393 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4394 THEN
4395 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4396 , 'JMFVSKAB :AFter l_deleted_allocations_tbl.count '
4397 , l_deleted_allocations_tbl.COUNT );
4398 END IF;
4399
4400 -- There should be only one record in the table because
4401 -- the deleteion is occuring for a specifc
4402 -- p_replen_so_line_id, p_component_id, p_subcontract_po_shipment_id
4403
4404 --Comment for bugfix 14078692: This if condition doesn't need to be changed in
4405 --case there are no records in jsa. In that case, delete_allocations would not
4406 --delete any record and count would be = 0.
4407
4408 IF l_deleted_allocations_tbl.COUNT > 0
4409 THEN
4410 l_deleted_qty := l_deleted_allocations_tbl(1).qty ;
4411 ELSE
4412 l_deleted_qty := 0;
4413 END IF; /* IF l_deleted_allocations_tbl.COUNT > 0 */
4414
4415 -- Bug 14978692
4416 -- Instead of deleting, update the record to set the allocable
4417 -- quantity to 0, so that no new RSO is created for the same PO.
4418
4419 --DELETE FROM JMF_SHIKYU_REPLENISHMENTS
4420 --WHERE REPLENISHMENT_SO_LINE_ID =
4421 -- C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4422 --AND REPLENISHMENT_SO_HEADER_ID =
4423 -- C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_header_id ;
4424
4425 UPDATE JMF_SHIKYU_REPLENISHMENTS
4426 SET allocable_quantity = 0,
4427 allocable_primary_uom_quantity = 0,
4428 allocated_quantity = 0,
4429 allocated_primary_uom_quantity = 0,
4430 ordered_quantity = 0,
4431 ordered_primary_uom_quantity = 0,
4432 last_update_date = sysdate,
4433 last_updated_by = FND_GLOBAL.user_id,
4434 last_update_login = FND_GLOBAL.login_id
4435 WHERE replenishment_so_line_id = C_SHIKYU_REPLENISHMENT_REC.replenishment_so_line_id
4436 AND replenishment_so_header_id = C_SHIKYU_REPLENISHMENT_REC.replenishment_so_header_id ;
4437
4438 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4439 THEN
4440 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4441 , 'JMFVSKAB :AFter Updating JMF_SHIKYU_REPLENISHMENTS:'
4442 , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );
4443 END IF;
4444
4445 IF l_deleted_qty > 0
4446 THEN
4447 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4448 THEN
4449 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4450 , 'JMFVSKAB :1Cal JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations '
4451 , l_deleted_qty );
4452 END IF;
4453
4454 Create_New_Allocations
4455 ( p_api_version => 1.0
4456 , p_init_msg_list => NULL
4457 , x_return_status => l_return_status
4458 , x_msg_count => l_msg_count
4459 , x_msg_data => l_msg_data
4460 , p_subcontract_po_shipment_id => C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
4461 , p_component_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4462 , p_qty => l_deleted_qty
4463 , p_skip_po_replen_creation => 'N'
4464 );
4465 END IF; /* l_deleted_qty > 0 */
4466
4467 --Bugfix 14078692: When the order quantity < allocable quantity.
4468 --This can happen in two scenarios:
4469 --1. Line is split.
4470 --2. Order quantity is reduced.
4471
4472 --ELSE ---- partial case
4473 ELSIF C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity <
4474 C_SHIKYU_REPLENISHMENT_rec.allocable_quantity
4475 THEN
4476
4477 -- Calculate the primary UOM qty
4478 -- decrease allocations
4479 -- update the quantity in the JMF replenishments
4480 -- insert record into the JMF replenishment table for the new line (for split line case)
4481 -- create new allocations for the decreased quantity
4482
4483 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4484 THEN
4485 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4486 , 'JMFVSKAB :Before primary UOM calculation '
4487 , l_decreased_qty );
4488 END IF;
4489
4490 -- Get the UOM and primary UOM of the parent Replenishment SO Line.
4491 -- UOM Conversion needs to be for the new allocable quantity of the
4492 -- parent SO Line. Also needs to be done for the ordered and allocable
4493 -- quantities of the child Replenishment SO Lines newly splitted from
4494 -- the parent SO Line.
4495 SELECT UOM,
4496 PRIMARY_UOM
4497 INTO l_uom,
4498 l_primary_uom
4499 FROM JMF_SHIKYU_REPLENISHMENTS
4500 WHERE REPLENISHMENT_SO_LINE_ID =
4501 C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
4502
4503 -- Converting the new allocable quantity of the parent SO Line
4504 -- to the primary UOM
4505 IF l_uom <> l_primary_uom
4506 THEN
4507
4508 l_primary_uom_qty
4509 := INV_CONVERT.inv_um_convert
4510 ( item_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4511 , precision => 5
4512 , from_quantity => C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity
4513 , from_unit => l_uom
4514 , to_unit => l_primary_uom
4515 , from_name => null
4516 , to_name => null
4517 );
4518
4519 ELSE
4520
4521 l_primary_uom_qty := C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity;
4522
4523 END IF;
4524
4525 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4526 THEN
4527 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4528 , G_MODULE_PREFIX || l_api_name
4529 , 'l_primary_uom_qty:' || l_primary_uom_qty ||
4530 ':Old allocable qty:' || C_SHIKYU_REPLENISHMENT_rec.allocable_primary_UOM_quantity );
4531
4532 --FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4533 -- , G_MODULE_PREFIX || l_api_name
4534 -- , 'Reduce_Allocations by:' || C_SHIKYU_REPLENISHMENT_rec.allocable_primary_UOM_quantity -
4535 -- l_primary_uom_qty );
4536 END IF;
4537
4538 --Bugfix 14078692: If there are no allocations, we should not call reduce_allocations.
4539 --This would be a scenario where you have a RPO/RSO pair which is not allocated to any
4540 --SPO. In case you split the line in the RSO, you need to just change the record in JSR.
4541 if C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id is not null
4542 then
4543 JMF_SHIKYU_ALLOCATION_PVT.Reduce_Allocations
4544 ( p_api_version => 1.0
4545 , p_init_msg_list => NULL
4546 , x_return_status => l_return_status
4547 , x_msg_count => l_msg_count
4548 , x_msg_data => l_msg_data
4549 , p_subcontract_po_shipment_id => C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
4550 , p_component_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4551 , p_replen_so_line_id => C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4552 , p_qty_to_reduce => C_SHIKYU_REPLENISHMENT_rec.allocable_primary_UOM_quantity -
4553 NVL(l_primary_uom_qty,0)
4554 , x_reduced_allocations_tbl => l_reduced_allocations_tbl
4555 , x_actual_reduced_qty => l_decreased_qty
4556 );
4557
4558 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4559 THEN
4560 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4561 THEN
4562 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4563 , G_MODULE_PREFIX || l_api_name
4564 , 'Reduce_Allocations returned error. Status:' || l_return_status
4565 );
4566 END IF;
4567
4568 rollback to qty_change;
4569 goto main_loop_ends;
4570
4571 ELSE
4572 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4573 THEN
4574 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4575 , G_MODULE_PREFIX || l_api_name
4576 , 'Reduce_Allocations returned success. Status:' || l_return_status
4577 );
4578 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4579 , G_MODULE_PREFIX || l_api_name
4580 , 'l_decreased_qty => ' || l_decreased_qty);
4581 END IF;
4582 END IF;
4583
4584 end if; --subcontract_po_shipment_id is not null
4585
4586 -- Get the UOM and primary UOM of the parent Replenishment SO Line.
4587 -- Need to update the allocable quantity columns of the parent
4588 -- Replenishment SO Line, since some of this quantity has been
4589 -- splitted into the child SO Lines. This needs to be done in
4590 -- order to prevent over-allocation of the parent SO Line.
4591 UPDATE JMF_SHIKYU_REPLENISHMENTS
4592 SET allocable_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
4593 allocable_primary_uom_quantity = l_primary_uom_qty,
4594 ordered_quantity = C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
4595 ordered_primary_uom_quantity = l_primary_uom_qty,
4596 last_update_date = sysdate,
4597 last_updated_by = FND_GLOBAL.user_id,
4598 last_update_login = FND_GLOBAL.login_id
4599 WHERE REPLENISHMENT_SO_LINE_ID = C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;
4600
4601 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4602 THEN
4603 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4604 , G_MODULE_PREFIX || l_api_name
4605 , 'After UPDATE JMF_SHIKYU_REPLENISHMENTS for line_id :' ||
4606 C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4607 );
4608 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4609 , G_MODULE_PREFIX || l_api_name
4610 , 'Before C_child_so_lines_CSR for parent:' || l_parent_so_line_id
4611 );
4612 END IF;
4613
4614 FOR C_child_so_lines_rec IN C_child_so_lines_CSR
4615 LOOP
4616
4617 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4618 THEN
4619 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4620 , G_MODULE_PREFIX || l_api_name
4621 , 'C_child_so_lines_rec.line_id:' || C_child_so_lines_rec.line_id );
4622 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4623 , G_MODULE_PREFIX || l_api_name
4624 , 'C_child_so_lines_rec.header_id:' || C_child_so_lines_rec.header_id );
4625 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4626 , G_MODULE_PREFIX || l_api_name
4627 , ' C_child_so_lines_rec.split_from_line_id:' || C_child_so_lines_rec.split_from_line_id );
4628 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4629 , G_MODULE_PREFIX || l_api_name
4630 , 'C_child_so_lines_rec.line_number:' || C_child_so_lines_rec.line_number );
4631 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4632 , G_MODULE_PREFIX || l_api_name
4633 , 'C_child_so_lines_rec.ordered_quantity:' || C_child_so_lines_rec.ordered_quantity );
4634 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4635 , G_MODULE_PREFIX || l_api_name
4636 , 'C_child_so_lines_rec.schedule_ship_date:' || C_child_so_lines_rec.schedule_ship_date );
4637 END IF;
4638
4639 -- Converting the ordered quantity of the child Replenishment
4640 -- SO Lines newly splitted from the parent SO Line
4641 IF l_uom <> l_primary_uom
4642 THEN
4643
4644 l_primary_uom_qty
4645 := INV_CONVERT.inv_um_convert
4646 ( item_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4647 , precision => 5
4648 , from_quantity => C_child_so_lines_rec.ordered_quantity
4649 , from_unit => l_uom
4650 , to_unit => l_primary_uom
4651 , from_name => null
4652 , to_name => null
4653 );
4654
4655 ELSE
4656
4657 l_primary_uom_qty := C_child_so_lines_rec.ordered_quantity;
4658
4659 END IF;
4660
4661 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4662 THEN
4663 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4664 , G_MODULE_PREFIX || l_api_name
4665 , 'l_primary_uom_qty:' || l_primary_uom_qty );
4666 END IF;
4667
4668 -- Insert ONLY if the C_child_so_lines_rec.line_id is not already
4669 -- present in the table
4670
4671 INSERT INTO JMF_SHIKYU_REPLENISHMENTS
4672 ( REPLENISHMENT_SO_LINE_ID
4673 , REPLENISHMENT_SO_HEADER_ID
4674 , SCHEDULE_SHIP_DATE
4675 , REPLENISHMENT_PO_HEADER_ID
4676 , REPLENISHMENT_PO_LINE_ID
4677 , REPLENISHMENT_PO_SHIPMENT_ID
4678 , OEM_ORGANIZATION_ID
4679 , TP_ORGANIZATION_ID
4680 , TP_SUPPLIER_ID
4681 , TP_SUPPLIER_SITE_ID
4682 , SHIKYU_COMPONENT_ID
4683 , ORDERED_QUANTITY
4684 , ALLOCATED_QUANTITY
4685 , ALLOCABLE_QUANTITY
4686 , ORDERED_PRIMARY_UOM_QUANTITY
4687 , ALLOCATED_PRIMARY_UOM_QUANTITY
4688 , ALLOCABLE_PRIMARY_UOM_QUANTITY
4689 , UOM
4690 , PRIMARY_UOM
4691 , ADDITIONAL_SUPPLY
4692 , ORG_ID
4693 , LAST_UPDATE_DATE
4694 , LAST_UPDATED_BY
4695 , CREATION_DATE
4696 , CREATED_BY
4697 , LAST_UPDATE_LOGIN
4698 , REQUEST_ID
4699 , PROGRAM_APPLICATION_ID
4700 , PROGRAM_ID
4701 , PROGRAM_UPDATE_DATE
4702 )
4703 SELECT
4704 C_child_so_lines_rec.line_id
4705 , C_child_so_lines_rec.header_id
4706 , C_child_so_lines_rec.schedule_ship_date
4707 , REPLENISHMENT_PO_HEADER_ID
4708 , REPLENISHMENT_PO_LINE_ID
4709 , REPLENISHMENT_PO_SHIPMENT_ID
4710 , OEM_ORGANIZATION_ID
4711 , TP_ORGANIZATION_ID
4712 , TP_SUPPLIER_ID
4713 , TP_SUPPLIER_SITE_ID
4714 , SHIKYU_COMPONENT_ID
4715 , C_child_so_lines_rec.ordered_quantity -- ordered qty
4716 , 0 -- allocated qty
4717 , C_child_so_lines_rec.ordered_quantity -- allocable qty
4718 , l_primary_uom_qty -- ordered qty in primary UOM
4719 , 0 -- allocated qty in primary UOM
4720 , l_primary_uom_qty -- allocable qty in primary UOM
4721 , UOM
4722 , PRIMARY_UOM
4723 , ADDITIONAL_SUPPLY
4724 , ORG_ID
4725 , LAST_UPDATE_DATE
4726 , LAST_UPDATED_BY
4727 , CREATION_DATE
4728 , CREATED_BY
4729 , LAST_UPDATE_LOGIN
4730 , REQUEST_ID
4731 , PROGRAM_APPLICATION_ID
4732 , PROGRAM_ID
4733 , PROGRAM_UPDATE_DATE
4734 FROM JMF_SHIKYU_REPLENISHMENTS jsr
4735 WHERE REPLENISHMENT_SO_LINE_ID =
4736 C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
4737 AND NOT EXISTS (
4738 SELECT jsr1.REPLENISHMENT_SO_LINE_ID
4739 FROM JMF_SHIKYU_REPLENISHMENTS jsr1
4740 WHERE jsr1.REPLENISHMENT_SO_LINE_ID =
4741 C_child_so_lines_rec.line_id );
4742
4743 /*AND REPLENISHMENT_SO_HEADER_ID =
4744 C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_header_ID;*/
4745
4746 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4747 THEN
4748 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4749 , 'JMFVSKAB :after INSERT child = '|| C_child_so_lines_rec.line_id
4750 , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );
4751 END IF;
4752
4753 END LOOP; -- child so line loop
4754
4755 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4756 THEN
4757 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4758 , 'JMFVSKAB :Out of child SO cursor: main REPLENISHMENT_SO_LINE_ID '
4759 , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );
4760 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4761 , 'JMFVSKAB :Out of child SO cursor: main REPLENISHMENT_SO_header_ID '
4762 , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_header_ID );
4763 END IF;
4764
4765 IF l_decreased_qty > 0
4766 THEN
4767 Create_New_Allocations
4768 ( p_api_version => 1.0
4769 , p_init_msg_list => NULL
4770 , x_return_status => l_return_status
4771 , x_msg_count => l_msg_count
4772 , x_msg_data => l_msg_data
4773 , p_subcontract_po_shipment_id => C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
4774 , p_component_id => C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
4775 , p_qty => l_decreased_qty
4776 , p_skip_po_replen_creation => 'N'
4777 );
4778
4779 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4780 THEN
4781 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4782 , 'JMFVSKAB :2after JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations '
4783 , l_decreased_qty );
4784 END IF;
4785
4786 END IF; -- l_decreased_qty
4787
4788 END IF; /* IF C_SHIKYU_REPLENISHMENT_rec.oel_cancelled_flag = 'Y' OR
4789 C_SHIKYU_REPLENISHMENT_rec.oeh_cancelled_flag = 'Y' */
4790 -- cancel or partial
4791 COMMIT;
4792
4793 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE
4794 THEN
4795 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
4796 , 'JMFVSKAB: Partial_reconcile COMMIT inside loop SO line '
4797 , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID);
4798 END IF;
4799
4800
4801 EXCEPTION
4802 WHEN OTHERS THEN
4803 ROLLBACK to qty_change;
4804 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE
4805 THEN
4806 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
4807 , G_MODULE_PREFIX || l_api_name
4808 , 'Exception inner block::' || SQLERRM );
4809 END IF;
4810 END ;
4811
4812 <<main_loop_ends>>
4813 --null;
4814 exit when C_SHIKYU_REPLENISHMENT_CSR%notfound;
4815
4816 END LOOP; -- Main C_SHIKYU_REPLENISHMENT_rec loop
4817
4818 x_return_status := 'S' ;
4819
4820 IF (g_fnd_debug = 'Y' AND
4821 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
4822 THEN
4823 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
4824 , G_MODULE_PREFIX || l_api_name
4825 , 'Exit');
4826 END IF;
4827
4828 EXCEPTION
4829 WHEN FND_API.G_EXC_ERROR THEN
4830 ROLLBACK;
4831 x_return_status := FND_API.G_RET_STS_ERROR;
4832
4833 IF g_fnd_debug = 'Y' AND FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4834 THEN
4835 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
4836 , G_MODULE_PREFIX || l_api_name
4837 , 'ERROR::' || sqlerrm
4838 );
4839 END IF;
4840
4841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4842 ROLLBACK ;
4843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4844
4845 IF g_fnd_debug = 'Y' AND FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4846 THEN
4847 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
4848 , G_MODULE_PREFIX || l_api_name
4849 , 'UNEXPECTED Error::' || sqlerrm
4850 );
4851 END IF;
4852
4853 WHEN OTHERS THEN
4854 ROLLBACK ;
4855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4856
4857 IF g_fnd_debug = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
4858 THEN
4859 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4860 , G_MODULE_PREFIX || l_api_name
4861 , 'OTHERS Exception::' || sqlerrm
4862 );
4863 END IF;
4864
4865 END Reconcile_Partial_Shipments ;
4866
4867 /* Private Helper Functions/Procedures */
4868
4869 --=============================================================================
4870 -- PROCEDURE NAME: Get_Replen_So_Attributes
4871 -- TYPE : PRIVATE
4872 -- PARAMETERS :
4873 --
4874 -- DESCRIPTION :
4875 --
4876 -- EXCEPTIONS :
4877 --
4878 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
4879 --=============================================================================
4880
4881 PROCEDURE Get_Replen_So_Attributes
4882 ( p_replen_so_line_id IN NUMBER
4883 , x_header_id OUT NOCOPY NUMBER
4884 , x_allocable_primary_uom_qty OUT NOCOPY NUMBER
4885 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
4886 , x_uom OUT NOCOPY VARCHAR2
4887 , x_primary_uom OUT NOCOPY VARCHAR2
4888 , x_replen_so_line_exists OUT NOCOPY VARCHAR2
4889 )
4890 IS
4891
4892 BEGIN
4893
4894 x_header_id := NULL;
4895 x_allocable_primary_uom_qty := NULL;
4896 x_allocated_primary_uom_qty := NULL;
4897 x_uom := NULL;
4898 x_replen_so_line_exists := 'N';
4899
4900 SELECT oola.header_id,
4901 jsr.allocable_primary_uom_quantity,
4902 jsr.allocated_primary_uom_quantity,
4903 jsr.uom,
4904 jsr.primary_uom,
4905 'Y'
4906 INTO x_header_id,
4907 x_allocable_primary_uom_qty,
4908 x_allocated_primary_uom_qty,
4909 x_uom,
4910 x_primary_uom,
4911 x_replen_so_line_exists
4912 FROM JMF_SHIKYU_REPLENISHMENTS jsr,
4913 OE_ORDER_LINES_ALL oola
4914 WHERE jsr.replenishment_so_line_id = p_replen_so_line_id
4915 AND jsr.replenishment_so_line_id = oola.line_id;
4916
4917 EXCEPTION
4918 WHEN NO_DATA_FOUND THEN
4919 x_replen_so_line_exists := 'N';
4920
4921 END Get_Replen_So_Attributes;
4922
4923 --=============================================================================
4924 -- PROCEDURE NAME: Get_Allocation_Attributes
4925 -- TYPE : PRIVATE
4926 -- PARAMETERS :
4927 --
4928 -- DESCRIPTION :
4929 --
4930 -- EXCEPTIONS :
4931 --
4932 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
4933 --=============================================================================
4934
4935 PROCEDURE Get_Allocation_Attributes
4936 ( p_subcontract_po_shipment_id IN NUMBER
4937 , p_component_id IN NUMBER
4938 , p_replen_so_line_id IN NUMBER
4939 , x_allocated_qty OUT NOCOPY NUMBER
4940 , x_uom OUT NOCOPY VARCHAR2
4941 , x_allocation_exists OUT NOCOPY VARCHAR2
4942 )
4943 IS
4944
4945 BEGIN
4946
4947 x_allocated_qty := NULL;
4948 x_uom := NULL;
4949 x_allocation_exists := 'N';
4950
4951 SELECT jsa.allocated_quantity,
4952 jsa.uom,
4953 'Y'
4954 INTO x_allocated_qty,
4955 x_uom,
4956 x_allocation_exists
4957 FROM JMF_SHIKYU_ALLOCATIONS jsa
4958 WHERE jsa.replenishment_so_line_id = p_replen_so_line_id
4959 AND jsa.shikyu_component_id = p_component_id
4960 AND jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
4961
4962 EXCEPTION
4963 WHEN NO_DATA_FOUND THEN
4964 x_allocation_exists := 'N';
4965
4966 END Get_Allocation_Attributes;
4967
4968 --=============================================================================
4969 -- PROCEDURE NAME : Populate_Replenishment
4970 -- TYPE : PRIVATE
4971 -- PARAMETERS :
4972 -- IN:
4973 -- p_component_id SHIKYU Component Identifier
4974 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
4975 -- DESCRIPTION :
4976 --
4977 -- EXCEPTIONS :
4978 --
4979 -- CHANGE HISTORY: 25-MAY-05 VCHU Created.
4980 --=============================================================================
4981
4982 PROCEDURE Populate_Replenishment
4983 ( p_replen_so_line_id IN NUMBER
4984 , p_replen_po_shipment_id IN NUMBER
4985 , p_component_id IN NUMBER
4986 , p_oem_organization_id IN NUMBER
4987 , p_tp_organization_id IN NUMBER
4988 , p_primary_uom IN VARCHAR2
4989 , p_primary_uom_qty IN NUMBER
4990 , p_additional_supply IN VARCHAR2
4991 )
4992 IS
4993
4994 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Replenishment';
4995
4996 -- Information from Replenishment SO Line
4997
4998 l_replen_so_header_id OE_ORDER_LINES_ALL.header_id%TYPE;
4999 l_schedule_ship_date OE_ORDER_LINES_ALL.schedule_ship_date%TYPE;
5000 l_ordered_uom OE_ORDER_LINES_ALL.order_quantity_uom%TYPE;
5001 l_ordered_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
5002 l_org_id OE_ORDER_LINES_ALL.org_id%TYPE;
5003
5004 -- Information from Replenishment PO Shipment
5005
5006 l_replen_po_header_id PO_LINE_LOCATIONS_ALL.po_header_id%TYPE;
5007 l_replen_po_line_id PO_LINE_LOCATIONS_ALL.po_line_id%TYPE;
5008 --l_supplier_id PO_HEADERS_ALL.vendor_id%TYPE;
5009 --l_supplier_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
5010
5011 l_tp_supplier_id JMF_SHIKYU_REPLENISHMENTS.tp_supplier_id%TYPE;
5012 l_tp_supplier_site_id JMF_SHIKYU_REPLENISHMENTS.tp_supplier_site_id%TYPE;
5013
5014 -- Information computed for JMF_SHIKYU_REPLENISHMENTS
5015
5016 l_primary_uom_qty JMF_SHIKYU_REPLENISHMENTS.ordered_primary_uom_quantity%TYPE;
5017 l_primary_uom JMF_SHIKYU_REPLENISHMENTS.primary_uom%TYPE;
5018
5019 BEGIN
5020
5021 IF (g_fnd_debug = 'Y' AND
5022 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5023 THEN
5024 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5025 , G_MODULE_PREFIX || l_api_name || '.invoked'
5026 , l_api_name|| ' Entry');
5027 END IF;
5028
5029 -- Getting information from the Replenishment PO Shipment
5030
5031 SELECT plla.po_header_id,
5032 plla.po_line_id
5033 INTO l_replen_po_header_id,
5034 l_replen_po_line_id
5035 FROM PO_LINE_LOCATIONS_ALL plla
5036 WHERE plla.line_location_id = p_replen_po_shipment_id;
5037
5038 IF (g_fnd_debug = 'Y' AND
5039 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5040 THEN
5041 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5042 , G_MODULE_PREFIX || l_api_name
5043 , l_api_name || ': l_replen_po_header_id = '|| l_replen_po_header_id
5044 || ', l_replen_po_line_id = '|| l_replen_po_line_id
5045 || ', p_replen_po_shipment_id = ' || p_replen_po_shipment_id);
5046 END IF;
5047
5048 -- Getting information from the Replenishment SO Line
5049
5050 SELECT oola.header_id,
5051 oola.ordered_quantity,
5052 oola.order_quantity_uom,
5053 oola.schedule_ship_date,
5054 oola.org_id
5055 INTO l_replen_so_header_id,
5056 l_ordered_qty,
5057 l_ordered_uom,
5058 l_schedule_ship_date,
5059 l_org_id
5060 FROM OE_ORDER_LINES_ALL oola
5061 WHERE oola.line_id = p_replen_so_line_id;
5062
5063 IF (g_fnd_debug = 'Y' AND
5064 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5065 THEN
5066 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5067 , G_MODULE_PREFIX || l_api_name
5068 , l_api_name || ': l_replen_so_header_id = '|| l_replen_so_header_id
5069 || ', l_ordered_qty = '|| l_ordered_qty
5070 || ', l_ordered_uom = ' || l_ordered_uom
5071 || ', l_schedule_ship_date = ' || l_schedule_ship_date
5072 || ', l_org_id = ' || l_org_id);
5073 END IF;
5074
5075 -- To get the supplier id and supplier site id associated
5076 -- with the TP Organization
5077 SELECT TO_NUMBER(org_information3),
5078 TO_NUMBER(org_information4)
5079 INTO l_tp_supplier_id,
5080 l_tp_supplier_site_id
5081 FROM hr_organization_information
5082 WHERE organization_id = p_tp_organization_id
5083 AND org_information_context = 'Customer/Supplier Association';
5084
5085 IF (g_fnd_debug = 'Y' AND
5086 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5087 THEN
5088 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5089 , G_MODULE_PREFIX || l_api_name
5090 , l_api_name || ': l_tp_supplier_id = ' || l_tp_supplier_id
5091 || ', l_tp_supplier_site_id = ' || l_tp_supplier_site_id
5092 );
5093 END IF;
5094
5095 IF p_primary_uom IS NULL
5096 THEN
5097
5098 l_primary_uom := JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
5099 ( p_inventory_item_id => p_component_id
5100 , p_organization_id => p_oem_organization_id
5101 );
5102
5103 ELSE
5104
5105 l_primary_uom := p_primary_uom;
5106
5107 END IF; /* F p_primary_uom IS NULL */
5108
5109 IF p_primary_uom_qty IS NULL
5110 THEN
5111
5112 l_primary_uom_qty := INV_CONVERT.inv_um_convert
5113 ( item_id => p_component_id
5114 , precision => 5
5115 , from_quantity => l_ordered_qty
5116 , from_unit => l_ordered_uom
5117 , to_unit => p_primary_uom
5118 , from_name => null
5119 , to_name => null
5120 );
5121
5122 ELSE
5123
5124 l_primary_uom_qty := p_primary_uom_qty;
5125
5126 END IF; /* IF p_primary_uom_qty IS NULL */
5127
5128 /* IF p_additional_supply NOT IN ('Y', 'N')
5129 THEN
5130 p_additional_supply = 'N';
5131 END IF; /* IF p_additional_supply NOT IN ('Y', 'N') */
5132
5133 IF (g_fnd_debug = 'Y' AND
5134 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5135 THEN
5136 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5137 , G_MODULE_PREFIX || l_api_name
5138 , l_api_name || ': l_primary_uom = ' || l_primary_uom
5139 || ', l_primary_uom_qty = ' || l_primary_uom_qty
5140 );
5141 END IF;
5142
5143 INSERT INTO JMF_SHIKYU_REPLENISHMENTS
5144 ( replenishment_so_line_id
5145 , replenishment_so_header_id
5146 , schedule_ship_date
5147 , replenishment_po_header_id
5148 , replenishment_po_line_id
5149 , replenishment_po_shipment_id
5150 , oem_organization_id
5151 , tp_organization_id
5152 , tp_supplier_id
5153 , tp_supplier_site_id
5154 , shikyu_component_id
5155 , ordered_quantity
5156 , allocated_quantity
5157 , allocable_quantity
5158 , ordered_primary_uom_quantity
5159 , allocated_primary_uom_quantity
5160 , allocable_primary_uom_quantity
5161 , uom
5162 , primary_uom
5163 , org_id
5164 , additional_supply
5165 , status
5166 , last_update_date
5167 , last_updated_by
5168 , creation_date
5169 , created_by
5170 , last_update_login
5171 )
5172 VALUES
5173 ( p_replen_so_line_id
5174 , l_replen_so_header_id
5175 , l_schedule_ship_date
5176 , l_replen_po_header_id
5177 , l_replen_po_line_id
5178 , p_replen_po_shipment_id
5179 , p_oem_organization_id
5180 , p_tp_organization_id
5181 , l_tp_supplier_id
5182 , l_tp_supplier_site_id
5183 , p_component_id
5184 , l_ordered_qty
5185 , 0
5186 , l_ordered_qty
5187 , l_primary_uom_qty
5188 , 0
5189 , l_primary_uom_qty
5190 , l_ordered_uom
5191 , l_primary_uom
5192 , l_org_id
5193 , p_additional_supply
5194 , NULL
5195 , SYSDATE
5196 , FND_GLOBAL.user_id
5197 , SYSDATE
5198 , FND_GLOBAL.user_id
5199 , FND_GLOBAL.login_id
5200 );
5201
5202 IF (g_fnd_debug = 'Y' AND
5203 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5204 THEN
5205 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5206 , G_MODULE_PREFIX || l_api_name || '.end'
5207 , l_api_name || ' Exit');
5208 END IF;
5209
5210 END Populate_Replenishment;
5211
5212 --=============================================================================
5213 -- FUNCTION NAME : Validate_Price
5214 -- TYPE : PRIVATE
5215 -- PARAMETERS :
5216 -- IN:
5217 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
5218 -- p_component_id SHIKYU Component Identifier
5219 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
5220 -- RETURN:
5221 --
5222 -- DESCRIPTION :
5223 --
5224 -- EXCEPTIONS :
5225 --
5226 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
5227 --=============================================================================
5228
5229 FUNCTION Validate_Price
5230 ( p_subcontract_po_shipment_id IN NUMBER
5231 , p_component_id IN NUMBER
5232 , p_replen_so_line_id IN NUMBER
5233 )
5234 RETURN BOOLEAN
5235 IS
5236
5237 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Price';
5238
5239 l_count NUMBER := 0;
5240 l_ret_val BOOLEAN := FALSE;
5241
5242 /* 12.1 Buy/Sell Subcontracting changes */
5243 l_oem_org_id number;
5244 l_mp_org_id number;
5245 l_subcontracting_type varchar2(1);
5246
5247 BEGIN
5248
5249 IF (g_fnd_debug = 'Y' AND
5250 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5251 THEN
5252 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5253 , G_MODULE_PREFIX || l_api_name || '.invoked'
5254 , l_api_name|| ' Entry');
5255 END IF;
5256
5257 /* 12.1 Buy/Sell Subcontracting changes */
5258 /* Check if subcomponent price is the same as the shipment price
5259 ONLY for Chargeable Subcontracting; this validation is not
5260 required for a Buy/Sell subcontracting relationship */
5261
5262 SELECT
5263 oem_organization_id, tp_organization_id
5264 INTO
5265 l_oem_org_id, l_mp_org_id
5266 FROM
5267 JMF_SUBCONTRACT_ORDERS
5268 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
5269
5270 l_subcontracting_type := JMF_SHIKYU_GRP.get_subcontracting_type(l_oem_org_id, l_mp_org_id);
5271
5272 IF (g_fnd_debug = 'Y' AND
5273 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5274 THEN
5275 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5276 , G_MODULE_PREFIX || l_api_name
5277 , ' subcontracting_type is '|| l_subcontracting_type );
5278 END IF;
5279
5280 If NVL(l_subcontracting_type, 'B') = 'B'
5281 THEN
5282 return TRUE;
5283 ELSE
5284 BEGIN
5285 SELECT count(*)
5286 INTO l_count
5287 FROM jmf_shikyu_components jsc
5288 WHERE jsc.shikyu_component_id = p_component_id
5289 AND jsc.subcontract_po_shipment_id = p_subcontract_po_shipment_id
5290 AND EXISTS (SELECT 'x'
5291 FROM oe_order_lines_all oola,
5292 oe_order_headers_all ooha
5293 WHERE oola.line_id = p_replen_so_line_id
5294 AND oola.inventory_item_id = p_component_id
5295 AND oola.price_list_id = jsc.price_list_id
5296 AND oola.header_id = ooha.header_id
5297 AND ooha.transactional_curr_code = jsc.currency
5298 AND oola.unit_selling_price
5299 = DECODE(oola.pricing_quantity_uom,
5300 jsc.uom , jsc.shikyu_component_price,
5301 jsc.primary_uom, jsc.primary_uom_price,
5302 -1));
5303
5304 IF (g_fnd_debug = 'Y' AND
5305 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5306 THEN
5307 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5308 , G_MODULE_PREFIX || l_api_name
5309 , l_api_name || ': l_count = '|| l_count
5310 );
5311 END IF;
5312
5313 IF l_count >= 1
5314 THEN
5315 l_ret_val := TRUE;
5316 END IF; /* IF count >= 1*/
5317
5318 RETURN l_ret_val;
5319
5320 END;
5321 END IF;
5322
5323 END Validate_Price;
5324
5325 --=============================================================================
5326 -- FUNCTION NAME : Validate_Project_Task_Ref
5327 -- TYPE : PRIVATE
5328 --
5329 -- PARAMETERS :
5330 -- IN:
5331 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
5332 -- p_component_id SHIKYU Component Identifier
5333 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
5334 -- RETURN:
5335 --
5336 -- DESCRIPTION :
5337 --
5338 -- EXCEPTIONS :
5339 --
5340 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
5341 --=============================================================================
5342
5343 FUNCTION Validate_Project_Task_Ref
5344 ( p_subcontract_po_shipment_id IN NUMBER
5345 --, p_component_id IN NUMBER
5346 , p_replen_so_line_id IN NUMBER
5347 )
5348 RETURN BOOLEAN
5349 IS
5350
5351 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Project_Task_Ref';
5352
5353 l_count NUMBER := 0;
5354 l_ret_val BOOLEAN := FALSE;
5355
5356 BEGIN
5357
5358 IF (g_fnd_debug = 'Y' AND
5359 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5360 THEN
5361 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5362 , G_MODULE_PREFIX || l_api_name || '.invoked'
5363 , l_api_name|| ' Entry');
5364 END IF;
5365
5366 -- project_control_level: Project = 1, Task = 2
5367 SELECT count(*)
5368 INTO l_count
5369 FROM mtl_parameters mtlp,
5370 jmf_subcontract_orders jso
5371 WHERE mtlp.organization_id = jso.tp_organization_id
5372 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
5373 AND ( (mtlp.project_control_level = 1
5374 AND EXISTS (SELECT 'x'
5375 FROM oe_order_lines_all oola
5376 WHERE oola.line_id = p_replen_so_line_id
5377 AND jso.project_id IS NOT NULL
5378 AND jso.project_id = oola.project_id
5379 AND NVL(jso.task_id, -1) = NVL(oola.task_id, -1)))
5380 OR (mtlp.project_control_level = 2
5381 AND EXISTS (SELECT 'x'
5382 FROM oe_order_lines_all oola
5383 WHERE oola.line_id = p_replen_so_line_id
5384 AND jso.project_id IS NOT NULL
5385 AND jso.task_id IS NOT NULL
5386 AND jso.project_id = oola.project_id
5387 AND jso.task_id = oola.task_id))
5388 OR (jso.project_id IS NULL
5389 AND jso.task_id IS NULL
5390 AND EXISTS (SELECT 'x'
5391 FROM oe_order_lines_all oola
5392 WHERE oola.line_id = p_replen_so_line_id
5393 AND oola.project_id IS NULL
5394 AND oola.task_id IS NULL))
5395 );
5396
5397 IF (g_fnd_debug = 'Y' AND
5398 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5399 THEN
5400 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5401 , G_MODULE_PREFIX || l_api_name
5402 , l_api_name || ': l_count = '|| l_count
5403 );
5404 END IF;
5405
5406 IF l_count >= 1
5407 THEN
5408 l_ret_val := TRUE;
5409 END IF; /* IF count >= 1*/
5410
5411 RETURN l_ret_val;
5412
5413 END Validate_Project_Task_Ref;
5414
5415 --=============================================================================
5416 -- PROCEDURE NAME : Reduce_One_Allocation
5417 -- TYPE : PRIVATE
5418 --
5419 -- PARAMETERS :
5420 -- IN:
5421 -- p_subcontract_po_shipment_id Subcontract Order Shipment Identifier
5422 -- p_component_id SHIKYU Component Identifier
5423 -- p_replen_so_line_id Replenishment Sales Order Line Identifier
5424 -- p_remain_qty_to_reduce
5425 -- p_existing_alloc_qty
5426 -- p_alloc_uom
5427 -- x_reduced_allocations_rec
5428 --
5429 -- RETURN:
5430 --
5431 -- DESCRIPTION :
5432 --
5433 -- EXCEPTIONS :
5434 --
5435 -- CHANGE HISTORY: 19-MAY-05 VCHU Created.
5436 --=============================================================================
5437
5438 PROCEDURE Reduce_One_Allocation
5439 ( p_subcontract_po_shipment_id IN NUMBER
5440 , p_component_id IN NUMBER
5441 , p_replen_so_line_id IN NUMBER
5442 , p_remain_qty_to_reduce IN NUMBER
5443 , p_existing_alloc_qty IN NUMBER
5444 , p_alloc_uom IN VARCHAR2
5445 , x_reduced_allocations_rec OUT NOCOPY g_allocation_qty_rec_type
5446 )
5447 IS
5448
5449 l_api_name CONSTANT VARCHAR2(30) := 'Reduce_One_Allocation';
5450
5451 l_remain_qty_to_reduce NUMBER;
5452 l_new_allocated_qty NUMBER;
5453 l_reduce_replen_uom_qty NUMBER;
5454 l_replen_uom VARCHAR2(3);
5455
5456 BEGIN
5457
5458 IF (g_fnd_debug = 'Y' AND
5459 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5460 THEN
5461 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5462 , G_MODULE_PREFIX || l_api_name || '.invoked'
5463 , l_api_name|| ' Entry');
5464 END IF;
5465
5466 IF (g_fnd_debug = 'Y' AND
5467 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5468 THEN
5469 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5470 , G_MODULE_PREFIX || l_api_name
5471 , l_api_name || ': p_subcontract_po_shipment_id = ' || p_subcontract_po_shipment_id
5472 || 'p_component_id = ' || p_component_id
5473 || 'p_replen_so_line_id = ' || p_replen_so_line_id
5474 );
5475 END IF;
5476
5477 l_remain_qty_to_reduce := p_remain_qty_to_reduce;
5478
5479 IF l_remain_qty_to_reduce < p_existing_alloc_qty
5480 THEN
5481
5482 l_new_allocated_qty := p_existing_alloc_qty - l_remain_qty_to_reduce;
5483
5484 IF (g_fnd_debug = 'Y' AND
5485 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5486 THEN
5487 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5488 , G_MODULE_PREFIX || l_api_name
5489 , l_api_name || ': l_remain_qty_to_reduce (' || l_remain_qty_to_reduce
5490 || ') < p_existing_alloc_qty (' || p_existing_alloc_qty
5491 || '), l_new_allocated_qty = ' || l_new_allocated_qty
5492 );
5493 END IF;
5494
5495 UPDATE JMF_SHIKYU_ALLOCATIONS
5496 SET allocated_quantity = l_new_allocated_qty
5497 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5498 AND replenishment_so_line_id = p_replen_so_line_id
5499 RETURNING subcontract_po_shipment_id,
5500 replenishment_so_line_id,
5501 shikyu_component_id,
5502 allocated_quantity,
5503 uom
5504 INTO x_reduced_allocations_rec;
5505
5506 -- Updating the qty field of the OUT bound record parameter
5507 -- x_reduced_allocations_rec to the actual quantity being reduced.
5508 -- The RETURNING statement would only give the previous value of
5509 -- the allocated_quantity column before the update operation.
5510 x_reduced_allocations_rec.qty := l_remain_qty_to_reduce;
5511
5512 IF (g_fnd_debug = 'Y' AND
5513 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5514 THEN
5515 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5516 , G_MODULE_PREFIX || l_api_name
5517 , l_api_name || ': Updated JMF_SHIKYU_ALLOCATIONS table, '
5518 || 'x_reduced_allocations_rec.qty = '
5519 || x_reduced_allocations_rec.qty
5520 );
5521 END IF;
5522
5523 ELSE
5524
5525 l_remain_qty_to_reduce := p_existing_alloc_qty;
5526
5527 IF (g_fnd_debug = 'Y' AND
5528 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5529 THEN
5530 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5531 , G_MODULE_PREFIX || l_api_name
5532 , l_api_name || ': l_remain_qty_to_reduce (' || l_remain_qty_to_reduce
5533 || ') >= p_existing_alloc_qty (' || p_existing_alloc_qty || ')'
5534 );
5535 END IF;
5536
5537 DELETE FROM JMF_SHIKYU_ALLOCATIONS
5538 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5539 AND replenishment_so_line_id = p_replen_so_line_id
5540 RETURNING subcontract_po_shipment_id,
5541 replenishment_so_line_id,
5542 shikyu_component_id,
5543 allocated_quantity,
5544 uom
5545 INTO x_reduced_allocations_rec;
5546 END IF; /* IF l_remain_qty_to_reduce < p_existing_alloc_qty */
5547
5548 -- Get UOM of the Replenishment SO Line
5549 SELECT uom
5550 INTO l_replen_uom
5551 FROM JMF_SHIKYU_REPLENISHMENTS
5552 WHERE replenishment_so_line_id = p_replen_so_line_id;
5553
5554 IF (g_fnd_debug = 'Y' AND
5555 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5556 THEN
5557 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5558 , G_MODULE_PREFIX || l_api_name
5559 , l_api_name || ': l_replen_uom = ' || l_replen_uom
5560 );
5561 END IF;
5562
5563 IF l_replen_uom <> p_alloc_uom
5564 THEN
5565
5566 IF (g_fnd_debug = 'Y' AND
5567 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5568 THEN
5569 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5570 , G_MODULE_PREFIX || l_api_name
5571 , l_api_name || ': l_replen_uom (' || l_replen_uom
5572 || ') <> p_alloc_uom (' || p_alloc_uom || ')'
5573 );
5574 END IF;
5575
5576 l_reduce_replen_uom_qty := INV_CONVERT.inv_um_convert
5577 ( item_id => p_component_id
5578 , precision => 5
5579 , from_quantity => l_remain_qty_to_reduce
5580 , from_unit => p_alloc_uom
5581 , to_unit => l_replen_uom
5582 , from_name => null
5583 , to_name => null
5584 );
5585 ELSE
5586
5587 l_reduce_replen_uom_qty := l_remain_qty_to_reduce;
5588
5589 END IF; /* IF l_replen_uom <> p_alloc_uom */
5590
5591 IF (g_fnd_debug = 'Y' AND
5592 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5593 THEN
5594 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5595 , G_MODULE_PREFIX || l_api_name
5596 , l_api_name|| ': l_replen_uom = ' || l_replen_uom
5597 || ', l_reduce_replen_uom_qty = ' || l_reduce_replen_uom_qty
5598 || ', p_alloc_uom = ' || p_alloc_uom
5599 || ', l_remain_qty_to_reduce = ' || l_remain_qty_to_reduce);
5600 END IF;
5601
5602 -- Update the allocated and allocable quantities (and their primary
5603 -- UOM counterparts) of the Replenishment SO Line that was deallocated
5604 -- from the Subcontracting Component specified by the IN parameters
5605 UPDATE JMF_SHIKYU_REPLENISHMENTS
5606 SET allocated_quantity = allocated_quantity - l_remain_qty_to_reduce,
5607 allocated_primary_uom_quantity = allocated_primary_uom_quantity - l_reduce_replen_uom_qty
5608 WHERE replenishment_so_line_id = p_replen_so_line_id;
5609
5610 IF (g_fnd_debug = 'Y' AND
5611 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5612 THEN
5613 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5614 , G_MODULE_PREFIX || l_api_name || '.end'
5615 , l_api_name || ' Exit');
5616 END IF;
5617
5618 END Reduce_One_Allocation;
5619
5620 --=============================================================================
5621 -- PROCEDURE NAME : Initialize
5622 -- TYPE : PRIVATE
5623 --
5624 -- PARAMETERS :
5625 -- IN:
5626 -- p_api_version
5627 -- p_input_api_version
5628 -- p_api_name
5629 -- p_init_msg_list
5630 -- x_return_status
5631 --
5632 -- RETURN:
5633 --
5634 -- DESCRIPTION :
5635 --
5636 -- EXCEPTIONS :
5637 --
5638 -- CHANGE HISTORY: 16-JUN-05 VCHU Created.
5639 --=============================================================================
5640
5641 PROCEDURE Initialize
5642 ( p_api_version IN NUMBER
5643 , p_input_api_version IN NUMBER
5644 , p_api_name IN VARCHAR2
5645 , p_init_msg_list IN VARCHAR2
5646 , x_return_status OUT NOCOPY VARCHAR2
5647 )
5648 IS
5649
5650 BEGIN
5651
5652 -- FND Logging at the start of the Procedure
5653 IF (g_fnd_debug = 'Y' AND
5654 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5655 THEN
5656 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5657 , G_MODULE_PREFIX || p_api_name || '.invoked'
5658 , G_MODULE_PREFIX || p_api_name|| ' Entry');
5659 END IF;
5660
5661 -- Start API initialization
5662
5663 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
5664 FND_MSG_PUB.initialize;
5665 END IF;
5666
5667 IF NOT FND_API.Compatible_API_Call( p_api_version
5668 , p_input_api_version
5669 , p_api_name
5670 , G_PKG_NAME
5671 )
5672 THEN
5673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5674 END IF;
5675
5676 x_return_status := FND_API.G_RET_STS_SUCCESS;
5677
5678 -- End API initialization
5679
5680 END Initialize;
5681
5682 --=============================================================================
5683 -- PROCEDURE NAME : reconcile_rso_date_changes
5684 -- DESCRIPTION : This method reconciles the rescheduled replenishment SOs.
5685 --=============================================================================
5686 PROCEDURE Reconcile_RSO_Date_Changes
5687 (x_return_status OUT NOCOPY varchar2)
5688 IS
5689
5690 l_api_name CONSTANT VARCHAR2(30) := 'Reconcile_RSO_Date_Changes';
5691
5692 l_final_ship_date DATE;
5693 l_allocation_date DATE;
5694 l_return_status VARCHAR2(3);
5695 l_msg_count NUMBER;
5696 l_msg_data VARCHAR2(300);
5697 l_line_id OE_ORDER_LINES_ALL.line_id%type;
5698 l_cnt NUMBER;
5699 l_deleted_qty NUMBER;
5700 l_deletion_failed NUMBER := 0;
5701 l_deleted_allocations_tbl JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;
5702
5703 cursor c_so_date_changes_cur is
5704 select jsr.replenishment_so_line_id line_id,
5705 jsr.schedule_ship_date jmf_schedule_ship_date,
5706 jsr.oem_organization_id oem_organization_id,
5707 jsr.tp_organization_id tp_organization_id,
5708 oel.schedule_ship_date om_schedule_ship_date
5709 from jmf_shikyu_replenishments jsr,
5710 oe_order_lines_all oel
5711 where oel.line_id = jsr.replenishment_so_line_id
5712 and oel.schedule_ship_date <> jsr.schedule_ship_date
5713 and oel.actual_shipment_date is null --pick up only unshipped lines. Rescheduling can only be done if the line is not shipped though.
5714 and oel.open_flag = 'Y';
5715
5716 cursor c_alloc_cur is
5717 select jsa.shikyu_component_id shikyu_component_id,
5718 jsa.subcontract_po_shipment_id subcontract_po_shipment_id,
5719 jso.wip_entity_id wip_entity_id
5720 from jmf_shikyu_allocations jsa,
5721 jmf_subcontract_orders jso
5722 where jsa.replenishment_so_line_id = l_line_id
5723 and jsa.subcontract_po_shipment_id = jso.subcontract_po_shipment_id;
5724
5725 BEGIN
5726
5727 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5728 then
5729 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5730 ,G_MODULE_PREFIX || l_api_name
5731 ,'Begin');
5732 end if;
5733
5734 x_return_status := FND_API.G_RET_STS_SUCCESS;
5735
5736 for so_date_change_rec in c_so_date_changes_cur loop
5737 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5738 then
5739 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5740 ,G_MODULE_PREFIX || l_api_name
5741 ,'RSO line_id:' || so_date_change_rec.line_id ||
5742 ':OM Sched Ship Date:' || so_date_change_rec.om_schedule_ship_date ||
5743 ':JMF Sched Ship Date:' || so_date_change_rec.jmf_schedule_ship_date ||
5744 ':OEM:' || so_date_change_rec.oem_organization_id ||
5745 ':MP:' || so_date_change_rec.tp_organization_id
5746 );
5747 end if;
5748
5749 l_line_id := so_date_change_rec.line_id;
5750
5751 if so_date_change_rec.om_schedule_ship_date < so_date_change_rec.jmf_schedule_ship_date
5752 then
5753 update jmf_shikyu_replenishments
5754 set schedule_ship_date = so_date_change_rec.om_schedule_ship_date,
5755 last_update_date = sysdate,
5756 last_updated_by = FND_GLOBAL.user_id,
5757 last_update_login = FND_GLOBAL.login_id
5758 where replenishment_so_line_id = l_line_id;
5759
5760 l_cnt := sql%rowcount;
5761
5762 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5763 then
5764 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5765 ,G_MODULE_PREFIX || l_api_name
5766 ,'SO is backward scheduled:: Rows Updated in JSR:' || l_cnt
5767 );
5768 end if;
5769
5770 --do nothing if the SO has been rescheduled backward
5771
5772 else --so_date_change_rec.om_schedule_ship_date > so_date_change_rec.jmf_schedule_ship_date
5773 --we dont need to consider changes to WO because the WO would not have started by now. The SO has not been shipped yet.
5774 --we also do not need to check whether the component has been used in the WO because the SO is not shipped yet.
5775 --check if the shipment (including shipping lead time) would reach in time for job completion. If not, we need to deallocate.
5776 for c_alloc_rec in c_alloc_cur loop
5777
5778 --Creating this savepoint to rollback the processing in case of an error.
5779 savepoint allocations;
5780
5781 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5782 then
5783 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5784 ,G_MODULE_PREFIX || l_api_name
5785 ,'Component_id:' || c_alloc_rec.shikyu_component_id ||
5786 ':subcontract_po_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
5787 ':wip_entity_id:' || c_alloc_rec.wip_entity_id
5788 );
5789 end if;
5790
5791 l_allocation_date := null;
5792 l_allocation_date := JMF_SHIKYU_UTIL.get_allocation_date(p_wip_entity_id => c_alloc_rec.wip_entity_id);
5793
5794 l_final_ship_date := null;
5795 l_final_ship_date := JMF_SHIKYU_UTIL.get_final_ship_date(p_oem_organization => so_date_change_rec.oem_organization_id
5796 , p_tp_organization => so_date_change_rec.tp_organization_id
5797 , p_scheduled_ship_date => so_date_change_rec.om_schedule_ship_date);
5798
5799 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5800 then
5801 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5802 ,G_MODULE_PREFIX || l_api_name
5803 ,'l_allocation_date:' || l_allocation_date ||
5804 ':l_final_ship_date:' || l_final_ship_date
5805 );
5806 end if;
5807
5808 if l_final_ship_date > l_allocation_date
5809 then
5810 --shipment cannot reach the MP in time. So deallocate the SPO, RSO and component combination
5811 JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations(p_api_version => 1.0
5812 , p_init_msg_list => NULL
5813 , x_return_status => l_return_status
5814 , x_msg_count => l_msg_count
5815 , x_msg_data => l_msg_data
5816 , p_subcontract_po_shipment_id => c_alloc_rec.subcontract_po_shipment_id
5817 , p_component_id => c_alloc_rec.shikyu_component_id
5818 , p_replen_so_line_id => l_line_id
5819 , x_deleted_allocations_tbl => l_deleted_allocations_tbl
5820 );
5821
5822 if l_return_status <> FND_API.G_RET_STS_SUCCESS
5823 then
5824 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5825 then
5826 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5827 , G_MODULE_PREFIX || l_api_name
5828 , 'Delete allocations returned error. Status:' || l_return_status);
5829 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5830 , G_MODULE_PREFIX || l_api_name
5831 , 'Rolling back the processing for SCO_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
5832 ':component_id:' || c_alloc_rec.shikyu_component_id ||
5833 ':line_id:' || l_line_id);
5834
5835 end if;
5836
5837 rollback to allocations;
5838
5839 --this variable would control if the JSR record for the SO line needs to be
5840 --updated to the new ship_date. If this condition is not checked, JSR would
5841 --get updated even when the allocations were not deleted. This would cause
5842 --issues because the shipment would not reach in time for the allocated components.
5843 --To Do.
5844 --We also need a mechanism to cause the program to end in warning in this scenario.
5845 l_deletion_failed := 1;
5846 x_return_status := l_return_status;
5847
5848 --Skip processing this SCP shipment and move to the next one.
5849 goto allocations_loop_ends;
5850 else
5851 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5852 then
5853 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5854 , G_MODULE_PREFIX || l_api_name
5855 , 'Delete allocations returned success. Status:' || l_return_status ||
5856 ':for SCO_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
5857 ':component_id:' || c_alloc_rec.shikyu_component_id ||
5858 ':line_id:' || l_line_id);
5859 end if;
5860 end if;
5861
5862 if l_deleted_allocations_tbl.count > 0
5863 then
5864 l_deleted_qty := l_deleted_allocations_tbl(1).qty;
5865 else
5866 l_deleted_qty := 0;
5867 end if;
5868
5869 if g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5870 then
5871 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5872 ,G_MODULE_PREFIX || l_api_name
5873 ,'Rows deleted:' || l_deleted_allocations_tbl.count ||
5874 ':Deleted Quantity:' || l_deleted_qty);
5875 end if;
5876
5877 if l_deleted_qty > 0 then
5878 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations(p_api_version => 1.0
5879 , p_init_msg_list => NULL
5880 , x_return_status => l_return_status
5881 , x_msg_count => l_msg_count
5882 , x_msg_data => l_msg_data
5883 , p_subcontract_po_shipment_id => c_alloc_rec.subcontract_po_shipment_id
5884 , p_component_id => c_alloc_rec.shikyu_component_id
5885 , p_qty => l_deleted_qty --check if this needs to be in puom
5886 , p_skip_po_replen_creation => 'N'
5887 );
5888
5889 if l_return_status <> FND_API.G_RET_STS_SUCCESS
5890 then
5891 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5892 then
5893 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5894 , G_MODULE_PREFIX || l_api_name
5895 , 'Create_New_Allocations returned error. Status:' || l_return_status ||
5896 ':for SCO_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
5897 ':component_id:' || c_alloc_rec.shikyu_component_id);
5898
5899 end if;
5900
5901 x_return_status := l_return_status;
5902
5903 else
5904 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5905 then
5906 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5907 , G_MODULE_PREFIX || l_api_name
5908 , 'Create_New_Allocations returned success. Status:' || l_return_status ||
5909 ':for SCO_shipment_id:' || c_alloc_rec.subcontract_po_shipment_id ||
5910 ':component_id:' || c_alloc_rec.shikyu_component_id);
5911 end if;
5912 end if;
5913 end if; --l_deleted_qty > 0
5914 end if; --l_final_ship_date > l_allocation_date
5915
5916 <<allocations_loop_ends>>
5917 null;
5918 end loop; --c_alloc_cur
5919
5920 if l_deletion_failed = 0 then
5921 update jmf_shikyu_replenishments
5922 set schedule_ship_date = so_date_change_rec.om_schedule_ship_date,
5923 last_update_date = sysdate,
5924 last_updated_by = FND_GLOBAL.user_id,
5925 last_update_login = FND_GLOBAL.login_id
5926 where replenishment_so_line_id = l_line_id;
5927
5928 l_cnt := sql%rowcount;
5929 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
5930 then
5931 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5932 , G_MODULE_PREFIX || l_api_name
5933 , 'l_deletion_failed = 0. Rows updated in JSR::' || l_cnt);
5934 end if;
5935 end if;
5936
5937 end if; --om_schedule_ship_date > jmf_schedule_ship_date
5938 end loop;
5939
5940 exception
5941 WHEN FND_API.G_EXC_ERROR THEN
5942 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_ERROR ');
5943 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
5944
5945 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5946 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5947 , G_MODULE_PREFIX || l_api_name
5948 , 'Expected Error:' || sqlerrm);
5949 END IF;
5950
5951 ROLLBACK;
5952 FND_MSG_PUB.Count_And_Get
5953 ( p_count => l_msg_count
5954 , p_data => l_msg_data
5955 );
5956 x_return_status := FND_API.g_ret_sts_error;
5957
5958 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5959 -- dbms_output.put_line(' EXCEPTION: FND_API.G_EXC_UNEXPECTED_ERROR ');
5960 -- dbms_output.put_line(' SQLERRM : '|| SQLERRM );
5961
5962 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5963 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5964 , G_MODULE_PREFIX || l_api_name
5965 , 'UnExpected Error:' || sqlerrm);
5966 END IF;
5967
5968 ROLLBACK ;
5969 FND_MSG_PUB.Count_And_Get
5970 ( p_count => l_msg_count
5971 , p_data => l_msg_data
5972 );
5973
5974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5975
5976 WHEN OTHERS THEN
5977
5978 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5979 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5980 , G_MODULE_PREFIX || l_api_name
5981 , 'Others:' || sqlerrm);
5982 END IF;
5983
5984 ROLLBACK ;
5985 FND_MSG_PUB.Count_And_Get
5986 ( p_count => l_msg_count
5987 , p_data => l_msg_data
5988 );
5989
5990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5991
5992 end reconcile_rso_date_changes;
5993
5994 END JMF_SHIKYU_ALLOCATION_PVT;