[Home] [Help]
PACKAGE BODY: APPS.JMF_SUBCONTRACT_ORDERS_PVT
Source
1 PACKAGE BODY JMF_SUBCONTRACT_ORDERS_PVT AS
2 -- $Header: JMFVSHKB.pls 120.39 2008/05/05 11:03:15 kdevadas ship $ --
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFVSHKB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Main Package body for SHIKYU Interlock processor |
13 --| FUNCTIONS/PROCEDURE |
14 --| Get_OEM_Tp_Org |
15 --| Validate_Lot_Serial_Control |
16 --| Is_Valid_Location |
17 --| Validate_OSA_Item |
18 --| Verify_Org_Attributes |
19 --| Verify_Shikyu_Attributes |
20 --| Load_Subcontract_Orders |
21 --| Load_Replenishments |
22 --| Load_Shikyu_Components |
23 --| Stamp_Null_Shikyu_Comp_Prices |
24 --| Generate_Batch_id |
25 --| Process_Subcontract_Orders |
26 --| Allocate_Batch |
27 --| Subcontract_Orders_Manager |
28 --| Subcontract_Orders_Worker |
29 --| |
30 --| HISTORY |
31 --| 04/26/2005 pseshadr Created |
32 --| 07/08/2005 vchu Fixed GSCC error File.Pkg.21 |
33 --| 22/09/2005 pseshadr Modified the name of the SHIKYU |
34 --| profile option in the |
35 --| Subcontract_Orders_Manager procedure |
36 --| 22/09/2005 vchu Changed the name of the error message |
37 --| for the case if the profile isn't on |
38 --| 13/10/2005 vchu Modified the c_alloc cursor in the |
39 --| Load_Replenishments procedure for an |
40 --| over allocation issue. |
41 --| 10/17/2005 vchu Modified calls to JMF_SHIKYU_ONT_PVT. |
42 --| Process_Replenishment_SO due to a |
43 --| change of signature. Also modified the |
44 --| c_rep cursor in Load_Replenishments |
45 --| procedure for an issue where multiple |
46 --| replenishment so lines were created for |
47 --| each replensihment po shipment. |
48 --| 10/26/2005 vchu Modified the value to populate into the |
49 --| TP_SUPPLIER_ID and TP_SUPPLIER_SITE_ID |
50 --| columns of the JMF_SHIKYU_REPLENISHMENTS|
51 --| table for fixing the wrong value issue |
52 --| of the Manufacturing Partner / MP site |
53 --| as described in bug 4651480. |
54 --| Also fixed the logic of the LOOP in the |
55 --| Load_Replenishments procedure to skip |
56 --| the current iteration instead of exiting|
57 --| if encountered a replenishment PO for a |
58 --| non-shikyu component. |
59 --| 11/02/2005 vchu Added the condition |
60 --| nvl(cancel_flag, 'N') = 'N' to the |
61 --| where clause of c_rep cursor (for PO |
62 --| Header, Line and Line Location levels) |
63 --| in order to filter out the |
64 --| cancelled Replenishment POs. |
65 --| 11/15/2005 vchu Modified the query of the c_alloc cursor|
66 --| to use required_quantity instead of |
67 --| wro.required_quantity - quantity_issued |
68 --| when selecting subcontracting components|
69 --| still requiring more allocations. |
70 --| 11/18/2005 vchu Removed from Load_Shikyu_Components the |
71 --| validation that fails a Subcontracting |
72 --| Order if the OSA item composes of a |
73 --| regular item. |
74 --| 12/05/2005 vchu Added a filtering condition to the |
75 --| c_comp_cur cursor of the |
76 --| Load_Shikyu_Components procedure to |
77 --| make sure that the components have not |
78 --| been loaded yet for the subcontract |
79 --| order in consideration. |
80 --| 02/08/2006 vchu Bug fix for 4912487: Fixed the FTS |
81 --| issue in Is_Valid_Location by replacing |
82 --| the WHERE EXISTS logic by a join |
83 --| between the hr_locations_all and |
84 --| hr_all_organization_units tables. |
85 --| 03/23/2006 vchu Polished up the FND Log messages. |
86 --| Fixed bug 5090721: set last_updated_by |
87 --| and last_update_login in all update |
88 --| statements. |
89 --| 03/31/2006 vchu Bug fix for 5132505: |
90 --| Added the Stamp_Null_Shikyu_Comp_Prices |
91 --| procedure (and a call to it in |
92 --| Subcontract_Orders_Manager) to pick up |
93 --| the shikyu components loaded with a |
94 --| null price, because of issues with the |
95 --| price list setup. |
96 --| Also removed commented code. |
97 --| 04/03/2006 vchu Set batch_id of the Subcontrating PO |
98 --| failing creation of the WIP job to -1, |
99 --| so that it will be picked up again by |
100 --| the next Interlock run, and thus the |
101 --| processing can be completed if the WIP |
102 --| issue is resolved. |
103 --| 04/17/2006 vchu Modified the format of the FND Log |
104 --| messages. |
105 --| 05/02/2006 vchu Modified the c_alloc cursor to restrict |
106 --| by the Operating Unit specified in the |
107 --| concurrent request parameters, and to |
108 --| order the not yet fully allocated |
109 --| Subcontract Orders by Need By Date, |
110 --| Header Number, Line Number and Shipment |
111 --| Number of the PO. |
112 --| Fixed Bug 5197415: Added the |
113 --| p_skip_po_replen_creation parameter to |
114 --| the calls to Create_New_Allocations. |
115 --| For the call to create new allocations |
116 --| for the Subcontracting Orders not yet |
117 --| fully allocated (those in c_alloc), the |
118 --| decision depends on the |
119 --| replen_so_creation_failed flag, which |
120 --| would be set to 'Y' if the call to |
121 --| Process_Replenishment_SO returned an |
122 --| error status. The other call in |
123 --| Load_Subcontract_Orders passes 'N' for |
124 --| p_skip_po_replen_creation, since only |
125 --| the newly loaded Subcontracting Orders |
126 --| are processed here. |
127 --| 05/08/2006 vchu Bug fixes for 5198838 and 5212219: |
128 --| Modified the c_project_csr cursor and |
129 --| the logic to fetch the project and task |
130 --| info from the Subcontracting Order |
131 --| Distributions. |
132 --| Also fixes a leftover issue from bug |
133 --| 5197415: Added a range logic to the |
134 --| c_alloc cursor of Load_Replenishments, |
135 --| so that the not yet fully allocated |
136 --| Subcontracting Orders that do not |
137 --| belong to that range would not be |
138 --| picked up and redundant Replenishment |
139 --| POs won't be created. |
140 --| 05/09/2006 vchu Modified the INSERT INTO |
141 --| JMF_SUBCONTRACT_ORDERS_TEMP statement |
142 --| to populate the need_by_date as the |
143 --| promised_date from po_line_locations_all|
144 --| table, if need_by_date was null. |
145 --| 05/12/2006 vchu Bug fix for 5212199: Added where clause |
146 --| condition to c_alloc to select only the |
147 --| not fully allocated Subcontracting |
148 --| Orders that have not been cancelled. |
149 --| 05/16/2006 vchu Bug fix for 5222131: Modified |
150 --| Load_Shikyu_Components to load |
151 --| components for any subcontracting order |
152 --| with interlock_status = 'N' but without |
153 --| any existing shikyu components, instead |
154 --| of requiring batch_id = -1. Also, |
155 --| modified Process_Subcontract_Orders to |
156 --| pick up any subcontracting orders with |
157 --| interlock_status = 'N' or 'U', without |
158 --| requiring batch_id = -1. This would |
159 --| allow subcontratcing orders loaded half |
160 --| way in case of a database crash to be |
161 --| picked and recovered in a later |
162 --| Interlock run. |
163 --| 06/13/2006 vchu Fixed bug 5153959: |
164 --| Modified the join statement with |
165 --| mtl_units_of_measure of the select |
166 --| statement for inserting into the |
167 --| JMF_SUBCONTRACT_ORDERS_TEMP temp table |
168 --| (in Load_Subcontract_Orders) to take |
169 --| the unit_meas_lookup_code from the PO |
170 --| Line if that of the PO Line Location |
171 --| was NULL. |
172 --| 06/14/2006 vchu Added a join to po_releases_all table |
173 --| in the select statement for inserting |
174 --| into the JMF_SUBCONTRACT_ORDERS_TEMP |
175 --| temp table, in order to make sure the |
176 --| Blanket Releases are approved, if the |
177 --| shipments are against Blanket Releases. |
178 --| 06/16/2006 rajkrish Fixed the worker batch issue |
179 --| 08/18/2006 vchu Added Taiwan (TW) and Korea (KR) to the |
180 --| list of valid countries in |
181 --| Is_Valid_Location. |
182 --| 08/22/2006 vchu Bug fix for bug 5364037: Added the new |
183 --| Validate_Lot_Serial_Control procedure, |
184 --| and calls to this procedure in |
185 --| Validate_OSA_Item and |
186 --| Verify_Shikyu_Attributes to make sure |
187 --| that the OSA item and the components |
188 --| are not Lot/Serial controlled in the TP |
189 --| Organization. |
190 --| 08/30/2006 vchu Bug fix for 5500896: Interlock was not |
191 --| picking up any Subcontract POs because |
192 --| of PJM POs created with an invalid |
193 --| OEM-TP Shipping Network, and PJM POs |
194 --| with an OSA item that wasn't assigned |
195 --| to the TP Org. An IF statement was |
196 --| added to the Load_Subcontract_Orders |
197 --| procedure to avoid performing the |
198 --| project reference validation and prevent|
199 --| the l_valid_flag from being overwritten |
200 --| if previous validations have already |
201 --| failed the current Subcontract Order. |
202 --| Also added filters to the cursors of |
203 --| the Load_Shikyu_components and |
204 --| Allocate_Batch procedures, in order to |
205 --| avoid processing of Subcontract POs |
206 --| outside of the current OU. |
207 --| This was the issue why the invalid POs |
208 --| in PJM OU was also affecting the DMF |
209 --| POs. |
210 --| Also added FND_LOG calls to print out |
211 --| sqlerrm in cases where an unexpected |
212 --| exception is caught, and polished up |
213 --| some existing FND Log messages. |
214 --| 10/31/2006 vchu Bug fix for 5632012: Modified |
215 --| Load_Shikyu_Components to call the WIP |
216 --| explodeRequirements API in order to |
217 --| explode any phantom BOMs defined in the |
218 --| BOM for the assembly item of the |
219 --| Subcontracting Order. |
220 --| 11/08/2006 vchu Bug fix for 5632012: Added a validation |
221 --| to set the interlock_status of the |
222 --| Subcontracting PO Shipment to 'E', if |
223 --| routings are defined for the |
224 --| corresponding OSA Item in the TP Org. |
225 --| Moreover, removed the logic to set |
226 --| the interlock_status of Subcontract |
227 --| Orders to 'E' only because no price |
228 --| was stamped after calling |
229 --| Process_Replenishment_So to do a price |
230 --| quote. This would enable these POs to |
231 --| have WIP Jobs created and to be seen |
232 --| from the Workbench UI. |
233 --| Also moved the call to |
234 --| Stamp_Null_Shikyu_Comp_Prices before |
235 --| Load_Shikyu_Components, in order to |
236 --| avoid calling OM Process Order API |
237 --| again to get prices for the components |
238 --| that were newly loaded and that failed |
239 --| price quoting the first time, which can |
240 --| seriously hurt the performance. |
241 --| 11/23/2006 vchu Bug fix for 5678387: Modified the |
242 --| queries that select |
243 --| UNIT_MEAS_LOOKUP_CODE from |
244 --| PO_LINE_LOCATIONS_ALL to select the |
245 --| same column from PO_LINES_ALL, if the |
246 --| UOM is not stamped on the line location,|
247 --| which seems to be the case for BPA |
248 --| Release Shipments. |
249 --| Also removed unnecessary logic that |
250 --| gets the next sequence ID for |
251 --| bom_explosion_temp, since we do not |
252 --| call bom explosion API directly anymore.|
253 --| 04-OCT-2007 kdevadas 12.1 Buy/Sell Subcontracting changes |
254 --| Reference - GBL_BuySell_TDD.doc |
255 --| Reference - GBL_BuySell_FDD.doc |
256 --| 01-MAY-2008 kdevadas Bug 7000413 - In case of errors in WIP |
257 --| job creation, the appropriate message is |
258 --| set and displayed in the request log |
259 --+=======================================================================+
260
261 --=============================================
262 -- GLOBALS
263 --=============================================
264
265 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SUBCONTRACT_ORDERS_PVT';
266 g_log_enabled BOOLEAN;
267
268 TYPE g_SubcontractTabTyp IS TABLE OF JMF_SUBCONTRACT_ORDERS_TEMP%ROWTYPE;
269 TYPE g_OsaTabTyp IS TABLE OF JMF_SUBCONTRACT_ORDERS%ROWTYPE;
270 TYPE g_Comp_TabTyp IS TABLE OF JMF_SHIKYU_COMPONENTS%ROWTYPE;
271 TYPE g_oem_tp_rec IS RECORD
272 ( oem_organization_id NUMBER
273 , tp_organization_id NUMBER
274 , vendor_id NUMBER
275 , vendor_site_id NUMBER
276 , status VARCHAR2(1)
277 );
278 TYPE g_oemtp_TabTyp IS TABLE OF g_oem_tp_rec
279 INDEX BY PLS_INTEGER;
280 TYPE g_org_rec IS RECORD
281 ( organization_id NUMBER
282 , status VARCHAR2(1)
283 );
284 TYPE g_org_TabTyp IS TABLE OF g_org_rec
285 INDEX BY PLS_INTEGER;
286 g_oemtp_tbl g_oemtp_TabTyp;
287 g_org_tbl g_org_TabTyp;
288
289 --=============================================
290 -- PROCEDURES AND FUNCTIONS
291 --=============================================
292
293 --========================================================================
294 -- FUNCTION : Get_OEM_Tp_Org PRIVATE
295 -- PARAMETERS: p_organization_id Organization
296 -- p_vendor_id Vendor
297 -- p_vendor_site_id Vendor Site
298 -- COMMENT : This function validates the following:
299 -- The OEM and TP org have relationship defined
300 -- in the shipping networks.
301 -- For the OEM-TP relationship that is defined, the
302 -- Supplier/site should be associated with only one TP org
303 --========================================================================
304 FUNCTION Get_OEM_Tp_Org
305 ( p_organization_id IN NUMBER
306 , p_vendor_id IN NUMBER
307 , p_vendor_site_id IN NUMBER
308 ) RETURN NUMBER
309 IS
310 l_org_tbl g_oemtp_TabTyp;
311 l_current_index PLS_INTEGER;
312 l_org_index PLS_INTEGER;
313 l_tp_org NUMBER;
314 l_program CONSTANT VARCHAR2(30) := 'Get_OEM_Tp_Org';
315
316
317 CURSOR c_oem_tp_cur IS
318 SELECT mip.from_organization_id oem_organization_id
319 , hoi.organization_id tp_organization_id
320 , p_vendor_id vendor_id
321 , p_vendor_site_id vendor_site_id
322 , 'Y' status
323 FROM HR_ORGANIZATION_INFORMATION hoi
324 , mtl_interorg_parameters mip
325 , mtl_parameters mp
326 WHERE mip.to_organization_id = mp.organization_id
327 AND mp.organization_id = hoi.organization_id
328 AND mp.trading_partner_org_flag = 'Y'
329 AND mip.from_organization_id = p_organization_id
330 AND hoi.org_information_context = 'Customer/Supplier Association'
331 AND hoi.org_information3 = p_vendor_id
332 AND hoi.org_information4 = p_vendor_site_id;
333
334 BEGIN
335
336 IF g_log_enabled THEN
337 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
338 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
339 , G_PKG_NAME
340 , '>> ' || l_program || ': Start'
341 );
342 END IF;
343 END IF;
344
345 OPEN c_oem_tp_cur;
346 FETCH c_oem_tp_cur INTO l_org_tbl(l_org_tbl.COUNT);
347 CLOSE c_oem_tp_cur;
348
349 -- Cache the OEM/TP relationship status and OEM/TP organization associations
350 -- if they are defined in the shipping network.
351
352 l_current_index:= g_oemtp_tbl.COUNT;
353
354 -- If there is more than one association defined for OEM/TP org combination,
355 -- mark the status as 'N' and set the tp_org to null.
356
357 IF l_org_tbl.COUNT <> 1
358 THEN
359 g_oemtp_tbl(l_current_index).status := 'N';
360 g_oemtp_tbl(l_current_index).oem_organization_id := p_organization_id;
361 g_oemtp_tbl(l_current_index).tp_organization_id := null;
362 g_oemtp_tbl(l_current_index).vendor_id := p_vendor_id;
363 g_oemtp_tbl(l_current_index).vendor_site_id := p_vendor_site_id;
364 l_tp_org := null;
365
366 FND_MESSAGE.set_name('JMF', 'JMF_SHK_OEM_TP_ASSN_ERROR');
367 FND_MSG_PUB.add;
368 ELSE
369 l_org_index := l_org_tbl.FIRST;
370 g_oemtp_tbl(l_org_index).status := 'Y';
371 g_oemtp_tbl(l_org_index).oem_organization_id := p_organization_id;
372 g_oemtp_tbl(l_org_index).tp_organization_id :=
373 l_org_tbl(l_org_index).tp_organization_id;
374 g_oemtp_tbl(l_org_index).vendor_id := p_vendor_id;
375 g_oemtp_tbl(l_org_index).vendor_site_id := p_vendor_site_id;
376 l_tp_org := l_org_tbl(l_org_index).tp_organization_id;
377
378 END IF;
379
380 IF g_log_enabled THEN
381 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
382 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
383 , G_PKG_NAME
384 , '>> ' || l_program || ': Exit'
385 );
386 END IF;
387 END IF;
388
389 RETURN l_tp_org;
390
391 END Get_OEM_Tp_Org;
392
393 --========================================================================
394 -- PROCEDURE : Validate_Lot_Serial_Control PRIVATE
395 -- PARAMETERS: p_item_id IN NUMBER Item Identifier
396 -- p_organization_id IN NUMBER Inventory Item Identifier
397 -- x_valid_flag OUT NUMBER Return value
398 -- COMMENT : Returns 'Y' if the item represented by the IN parameters is
399 -- not Lot and/or Serial controlled; returns 'N' otherwise.
400 --
401 -- Bug fix for 5364037: Added for Lot/Serial validation.
402 --========================================================================
403 PROCEDURE Validate_Lot_Serial_Control
404 ( p_item_id IN NUMBER
405 , p_organization_id IN NUMBER
406 , x_valid_flag OUT NOCOPY VARCHAR2
407 )
408 IS
409 --=================
410 -- LOCAL VARIABLES
411 --=================
412 l_lot_control_code NUMBER := -1;
413 l_serial_control_code NUMBER := -1;
414
415 l_program CONSTANT VARCHAR2(30) := 'Validate_Lot_Serial_Control';
416
417 BEGIN
418
419 x_valid_flag := 'Y';
420
421 SELECT lot_control_code,
422 serial_number_control_code
423 INTO l_lot_control_code,
424 l_serial_control_code
425 FROM mtl_system_items_b
426 WHERE inventory_item_id = p_item_id
427 AND organization_id = p_organization_id;
428
429 IF l_lot_control_code <> 1 OR l_serial_control_code <> 1
430 THEN
431 x_valid_flag := 'N';
432 END IF;
433
434 IF g_log_enabled AND
435 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
436 THEN
437 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
438 , G_PKG_NAME
439 , '>> ' || G_PKG_NAME || '.' || l_program
440 || ': l_lot_control_code = ' || l_lot_control_code
441 || ', l_serial_control_code = ' || l_serial_control_code
442 || ', x_valid_flag = ' || x_valid_flag
443 );
444 END IF;
445
446 EXCEPTION
447
448 WHEN NO_DATA_FOUND THEN
449 x_valid_flag := 'N';
450
451 IF g_log_enabled AND
452 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
453 THEN
454 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
455 , G_PKG_NAME || 'Get_Lot_Serial_Control_Code.no_data_found'
456 , G_PKG_NAME || '.' || l_program || ': No Data Found'
457 );
458 END IF;
459
460 WHEN OTHERS THEN
461 x_valid_flag := 'N';
462
463 IF g_log_enabled AND
464 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
465 THEN
466 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
467 , G_PKG_NAME || 'Get_Lot_Serial_Control_Code.others_exception'
468 , G_PKG_NAME || '.' || l_program || ': Unexpected Exception'
469 );
470 END IF;
471
472 END Validate_Lot_Serial_Control;
473
474 --========================================================================
475 -- FUNCTION : Is_Valid_Location PRIVATE
476 -- PARAMETERS: p_organization_id Organization
477 -- COMMENT : This function validates if the country associated with
478 -- the OEM organization is Japan.
479 --========================================================================
480 FUNCTION Is_Valid_Location
481 ( p_organization_id IN NUMBER
482 , p_subcontracting_type IN VARCHAR2 -- 12.1 Buy/Sell Subcontracting changes
483 ) RETURN BOOLEAN
484 IS
485 l_count NUMBER;
486 l_program CONSTANT VARCHAR2(30) := 'Is_Valid_Location';
487 BEGIN
488
489 IF g_log_enabled THEN
490 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
491 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
492 , G_PKG_NAME
493 , '>> ' || l_program || ': Start'
494 );
495 END IF;
496 END IF;
497
498 -- Check to see if the country code of the OEM org is Japan.
499 -- Added Taiwan (TW) and Korea (KR) to the list of countries
500
501 -- Bug 4912487: Fixed the FTS issue by replacing the WHERE EXISTS logic
502 -- by a join between the hr_all_organization_units and hr_locations_all
503 -- tables.
504 /* 12.1 Buy/Sell Subcontracting changes */
505 /* No country code validation required for Buy/Sell subcontracting */
506 IF (p_subcontracting_type = 'B') THEN
507 return TRUE;
508 END IF;
509
510
511 SELECT count(*)
512 INTO l_count
513 FROM hr_all_organization_units hou,
514 hr_locations_all hrl
515 WHERE hou.location_id = hrl.location_id
516 AND hou.organization_id = p_organization_id
517 AND hrl.country in ('JP', 'KR', 'TW');
518
519 IF g_log_enabled THEN
520 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
521 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
522 , G_PKG_NAME
523 , '>> ' || l_program || ': Country Code count is for JP = ' || l_count
524 );
525 END IF;
526 END IF;
527
528 IF l_count = 0
529 THEN
530 FND_MESSAGE.set_name('JMF','JMF_SHK_INVALID_LOCATION');
531 FND_MSG_PUB.add;
532 IF g_log_enabled THEN
533 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
535 , G_PKG_NAME
536 , '>> ' || l_program || ': OEM Country is not Japan:'
537 );
538 END IF;
539 END IF;
540 RETURN FALSE;
541 ELSE
542 IF g_log_enabled THEN
543 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
544 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
545 , G_PKG_NAME
546 , '>> ' || l_program || ': Is_Valid_Location will return TRUE'
547 );
548 END IF;
549 END IF;
550
551 RETURN TRUE;
552 END IF;
553
554
555 END Is_Valid_Location;
556
557
558 --========================================================================
559 -- PROCEDURE : Validate_OSA_Item PRIVATE
560 -- PARAMETERS:
561 -- p_item_id Item
562 -- p_organization_id Organization
563 -- p_vendor_id Vendor Id
564 -- p_vendor_site_id Vendor Site
565 -- x_tp_organization_id Tp Orgn
566 -- x_valid_flag Flag to indicate if the item is valid to follow
567 -- the SHIKYU business flow.
568 -- COMMENT : This procedure validates if the OSA item that is being passed
569 -- in is eligible to be processed through the SHIKYU flow.
570 -- The following validations are performed:
571 -- 1. Check if outsourced_assembly attribute for the item is set at OEM org
572 -- 2. Check if the location of the OEM org is valid
573 -- 3. Check if OEM/TP relationship exists and valid
574 -- 4. Check if outsourced_assembly attribute for the item is set at TP org
575 -- 5. Check if consigned flow is enabled at the for the item/supplier/site
576 --========================================================================
577 PROCEDURE Validate_OSA_Item
578 ( p_item_id IN NUMBER
579 , p_organization_id IN NUMBER
580 , p_vendor_id IN NUMBER
581 , p_vendor_site_id IN NUMBER
582 , x_tp_organization_id OUT NOCOPY NUMBER
583 , x_valid_flag OUT NOCOPY VARCHAR2
584 )
585 IS
586 --=================
587 -- LOCAL VARIABLES
588 --=================
589 l_msg_list VARCHAR2(2000);
590 l_msg_data VARCHAR2(2000);
591 l_msg_count NUMBER;
592 l_return_status VARCHAR2(1);
593 l_osa_flag NUMBER;
594 l_comp_flag NUMBER;
595 l_item_id NUMBER;
596 l_organization_id NUMBER;
597 i INTEGER;
598 l_exists BOOLEAN := FALSE;
599 l_consigned_from_supplier_flag
600 PO.PO_ASL_ATTRIBUTES.CONSIGNED_FROM_SUPPLIER_FLAG%TYPE := NULL;
601 l_enable_vmi_flag
602 PO.PO_ASL_ATTRIBUTES.ENABLE_VMI_FLAG%TYPE := NULL;
603 l_last_billing_date DATE := NULL;
604 l_consigned_billing_cycle NUMBER := NULL;
605 l_program CONSTANT VARCHAR2(30) := 'Validate_OSA_Item';
606
607 -- Bug 5364037: Added for Lot/Serial validation
608 l_lot_serial_valid_flag VARCHAR2(1);
609
610 /* 12.1 Buy/Sell subcontracting changes */
611 l_mp_organization_id NUMBER;
612 l_subcontracting_type VARCHAR2(1);
613
614
615 BEGIN
616
617 IF g_log_enabled THEN
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
619 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
620 , G_PKG_NAME
621 , '>> ' || l_program || ': Start'
622 );
623 END IF;
624 END IF;
625
626 l_organization_id := p_organization_id;
627 l_item_id := p_item_id;
628
629
630 -- Check to see if the item has the outsourced_assembly flag turned on
631 -- at the OEM organization.
632
633 JMF_SHIKYU_GRP.Get_Shikyu_Attributes
634 ( p_api_version => 1.0
635 , p_init_msg_list => l_msg_list
636 , x_return_status => l_return_status
637 , x_msg_count => l_msg_count
638 , x_msg_data => l_msg_data
639 , p_organization_id => l_organization_id
640 , p_item_id => l_item_id
641 , x_outsourced_assembly => l_osa_flag
642 , x_subcontracting_component => l_comp_flag
643 );
644
645 IF g_log_enabled THEN
646 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
648 , G_PKG_NAME
649 , '>> ' || l_program || ': OSA flag is ' || l_osa_flag
650 );
651 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
652 , G_PKG_NAME
653 , '>> ' || l_program || ': Component flag is ' || l_comp_flag
654 );
655 END IF;
656 END IF;
657
658 /* 12.1 Buy/Sell subcontracting changes */
659 /* Check to see if the location country code is Japan, Korea or Taiwan
660 only for Chargeable Subcontracting */
661
662 l_mp_organization_id := Get_OEM_Tp_Org
663 ( p_organization_id => p_organization_id
664 , p_vendor_id => p_vendor_id
665 , p_vendor_site_id => p_vendor_site_id
666 );
667 IF g_log_enabled THEN
668 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
670 , G_PKG_NAME
671 , '>> ' || l_program || ' mp org id - ' ||
672 l_mp_organization_id
673 );
674 END IF ;
675 END IF ;
676
677 l_subcontracting_type := JMF_SHIKYU_GRP.get_subcontracting_type
678 (p_oem_org_id => p_organization_id,
679 p_mp_org_id => l_mp_organization_id);
680
681 IF g_log_enabled THEN
682 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
684 , G_PKG_NAME
685 , '>> ' || l_program || ' subcontracting_type is ' ||
686 l_subcontracting_type
687 );
688 END IF ;
689 END IF ;
690
691 IF Is_Valid_Location(p_organization_id, l_subcontracting_type)
692 AND ((l_osa_flag =1) AND (l_return_status = FND_API.G_RET_STS_SUCCESS))
693 THEN
694
695 -- To check if the OEM/TP relationship exists, check the cache to see
696 -- if the OEM/TP combn exists. If it does, get the status from the
697 -- cache. IF the combination does not exist, invoke the function to
698 -- validate the OEM/TP relationship.
699
700 i:= g_oemtp_tbl.FIRST;
701
702 IF g_log_enabled THEN
703 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
704 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
705 , G_PKG_NAME
706 , '>> ' || l_program || ' Index of g_oemtp_tbl is ' || i
707 );
708 END IF;
709 END IF;
710
711 WHILE i <= g_oemtp_tbl.LAST
712 LOOP
713 IF((g_oemtp_tbl(i).oem_organization_id = p_organization_id)
714 AND (g_oemtp_tbl(i).vendor_id = p_vendor_id)
715 AND (g_oemtp_tbl(i).vendor_site_id = p_vendor_site_id))
716 THEN
717 l_exists := TRUE;
718 x_valid_flag := g_oemtp_tbl(i).status;
719 x_tp_organization_id := g_oemtp_tbl(i).tp_organization_id;
720
721 IF g_log_enabled THEN
722 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
724 , G_PKG_NAME
725 , '>> ' || l_program || ': x_valid_flag = '
726 || x_valid_flag
727 );
728 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
729 , G_PKG_NAME
730 , '>> ' || l_program || ': x_tp_organization_id = '
731 || x_tp_organization_id
732 );
733 END IF;
734 END IF;
735
736 EXIT;
737 END IF;
738
739 i := i + 1;
740
741 IF g_log_enabled THEN
742 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
743 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
744 , G_PKG_NAME
745 , '>> ' || l_program || ': index of g_oemtp_tbl is now ' || i
746 );
747 END IF;
748 END IF;
749
750 END LOOP;
751
752 IF NOT(l_exists)
753 THEN
754
755 IF g_log_enabled THEN
756 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
757 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
758 , G_PKG_NAME
759 , '>> ' || l_program || ': Calling Get_OEM-TP_Org with '
760 || 'p_organization_id = ' || p_organization_id
761 || ', p_vendor_id = ' || p_vendor_id
762 || ', p_vendor_site_id = ' || p_vendor_site_id
763 );
764 END IF;
765 END IF;
766
767 x_tp_organization_id := Get_OEM_Tp_Org
768 ( p_organization_id => p_organization_id
769 , p_vendor_id => p_vendor_id
770 , p_vendor_site_id => p_vendor_site_id
771 );
772 END IF;
773 END IF;
774
775 -- If a valid relationship exists between OEM and TP , then check the
776 -- outsourced_assembly attribute of the item at the TP org to ensure
777 -- it is set so that it can follow the SHIKYU flow.
778
779 IF g_log_enabled THEN
780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
781 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
782 , G_PKG_NAME
783 , '>> ' || l_program || ': x_tp_organization_id = '
784 || x_tp_organization_id
785 );
786 END IF;
787 END IF;
788
789 IF x_tp_organization_id IS NULL
790 THEN
791 x_valid_flag := 'N';
792 ELSE
793 JMF_SHIKYU_GRP.Get_Shikyu_Attributes
794 ( p_api_version => 1.0
795 , p_init_msg_list => l_msg_list
796 , x_return_status => l_return_status
797 , x_msg_count => l_msg_count
798 , x_msg_data => l_msg_data
799 , p_organization_id => x_tp_organization_id
800 , p_item_id => l_item_id
801 , x_outsourced_assembly => l_osa_flag
802 , x_subcontracting_component => l_comp_flag
803 );
804
805 IF g_log_enabled THEN
806 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
808 , G_PKG_NAME
809 , '>> ' || l_program || ': OSA flag = ' || l_osa_flag
810 );
811 END IF;
812 END IF;
813
814 IF ((l_osa_flag <>1) OR (l_return_status <> FND_API.G_RET_STS_SUCCESS))
815 THEN
816 x_valid_flag := 'N';
817 ELSE
818
819 -- Check if the Supplier/Supplier Site/Ship to Organization/Item
820 -- combination corresponds to a consigned enabled ASL, if yes, set
821 -- the valid flag to be 'N'
822
823 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
824 ( p_api_version => 1.0
825 , p_init_msg_list => NULL
826 , x_return_status => l_return_status
827 , x_msg_count => l_msg_count
828 , x_msg_data => l_msg_data
829 , p_inventory_item_id => p_item_id
830 , p_vendor_id => p_vendor_id
831 , p_vendor_site_id => p_vendor_site_id
832 , p_using_organization_id => p_organization_id
833 , x_consigned_from_supplier_flag => l_consigned_from_supplier_flag
834 , x_enable_vmi_flag => l_enable_vmi_flag
835 , x_last_billing_date => l_last_billing_date
836 , x_consigned_billing_cycle => l_consigned_billing_cycle
837 );
838
839 IF g_log_enabled THEN
840 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
842 , G_PKG_NAME
843 , '>> ' || l_program || ': Consigned flag = '
844 || l_consigned_from_supplier_flag
845 );
846 END IF;
847 END IF;
848
849 IF l_consigned_from_supplier_flag = 'Y'
850 THEN
851 x_valid_flag := 'N';
852 ELSE
853 x_valid_flag := 'Y';
854 END IF;
855
856 END IF;
857
858 -- Bug 5364037: To validate that the OSA item is not Lot and/or
859 -- Serial controlled in the TP Organization
860
861 IF x_valid_flag = 'Y'
862 THEN
863
864 Validate_Lot_Serial_Control
865 ( p_item_id => p_item_id
866 , p_organization_id => x_tp_organization_id
867 , x_valid_flag => l_lot_serial_valid_flag
868 );
869
870 IF l_lot_serial_valid_flag = 'N'
871 THEN
872 x_valid_flag := 'N';
873 END IF;
874
875 IF g_log_enabled AND
876 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
877 THEN
878 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
879 , G_PKG_NAME
880 , '>> ' || G_PKG_NAME || '.' || l_program
881 || ': x_valid_flag after Lot/Serial validation = ' || x_valid_flag
882 );
883 END IF;
884 END IF;
885
886 END IF;
887
888 IF g_log_enabled THEN
889 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
890 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
891 , G_PKG_NAME
892 , '>> ' || l_program || ': x_valid_flag after Consigned validation = ' || x_valid_flag
893 );
894 END IF;
895 END IF;
896
897 IF x_valid_flag = 'N'
898 THEN
899 FND_MESSAGE.set_name('JMF', 'JMF_SHK_OSA_ATTR_ERR');
900 FND_MSG_PUB.add;
901 END IF;
902
903 IF g_log_enabled THEN
904 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
905 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
906 , G_PKG_NAME
907 , '>> ' || l_program || ': Exit'
908 );
909 END IF;
910 END IF;
911
912 END Validate_OSA_Item;
913
914 --========================================================================
915 -- PROCEDURE : Verify_Org_Attributes PRIVATE
916 -- PARAMETERS:
917 -- p_organization_id Organization
918 -- x_eam_enabled EAM enabled flag
919 -- x_wms_enabled WMS enabled flag
920 -- x_process_enabled Process enabled flag
921 -- COMMENT : This procedure returns the process enabled,WMS,EAM attributes
922 -- for the organization passed in
923 --========================================================================
924 PROCEDURE Verify_Org_Attributes
925 ( p_organization_id IN NUMBER
926 , x_eam_enabled OUT NOCOPY VARCHAR2
927 , x_wms_enabled OUT NOCOPY VARCHAR2
928 , x_process_enabled OUT NOCOPY VARCHAR2
929 )
930 IS
931 l_program CONSTANT VARCHAR2(30) := 'Verify_Org_Attributes';
932 BEGIN
933
934 IF g_log_enabled THEN
935 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
936 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
937 , G_PKG_NAME
938 , '>> ' || l_program || ': Start'
939 );
940 END IF;
941 END IF;
942
943 SELECT NVL(eam_enabled_flag,'N')
944 , wms_enabled_Flag
945 , process_enabled_flag
946 INTO x_eam_enabled
947 , x_wms_enabled
948 , x_process_enabled
949 FROM mtl_parameters
950 WHERE organization_id = p_organization_id;
951
952 -- Cache the attributes and the status for the organization.
953
954 IF x_eam_enabled = 'Y' OR x_wms_enabled ='Y' OR x_process_enabled='Y'
955 THEN
956
957 g_org_tbl(g_org_tbl.COUNT).organization_id := p_organization_id;
958 g_org_tbl(g_org_tbl.COUNT).status := 'N';
959 FND_MESSAGE.set_name('JMF', 'JMF_SHK_ORG_ATTR_ERR');
960 FND_MSG_PUB.add;
961
962 IF g_log_enabled AND
963 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
964 THEN
965 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
966 , G_PKG_NAME
967 , '>> ' || l_program
968 || ': Validation failed for Organization with ID = '
969 || p_organization_id
970 || ': x_eam_enabled = '
971 || x_eam_enabled
972 || ', x_wms_enabled = '
973 || x_wms_enabled
974 || ', x_process_enabled = '
975 || x_process_enabled
976 );
977 END IF;
978
979 ELSE
980
981 g_org_tbl(g_org_tbl.COUNT).organization_id := p_organization_id;
982 g_org_tbl(g_org_tbl.COUNT).status := 'Y';
983
984 IF g_log_enabled AND
985 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
986 THEN
987 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
988 , G_PKG_NAME
989 , '>> ' || l_program
990 || ': Validation passed for Organization with ID = '
991 || p_organization_id
992 );
993 END IF;
994
995 END IF;
996
997 IF g_log_enabled AND
998 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
999 THEN
1000 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1001 , G_PKG_NAME
1002 , '>> ' || l_program
1003 || ': Validation failed for Organization with ID = '
1004 || p_organization_id
1005 || ': x_eam_enabled = '
1006 || x_eam_enabled
1007 || ', x_wms_enabled = '
1008 || x_wms_enabled
1009 || ', x_process_enabled = '
1010 || x_process_enabled
1011 );
1012 END IF;
1013
1014 IF g_log_enabled THEN
1015 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1016 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1017 , G_PKG_NAME
1018 , '>> ' || l_program || ': Exit'
1019 );
1020 END IF;
1021 END IF;
1022
1023 EXCEPTION
1024
1025 WHEN NO_DATA_FOUND THEN
1026 IF g_log_enabled THEN
1027 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1028 FND_LOG.string(FND_LOG.LEVEL_ERROR
1029 , G_PKG_NAME || l_program || '.no_data_found'
1030 , 'Org does not exist');
1031 END IF;
1032 END IF;
1033
1034 WHEN OTHERS THEN
1035 IF g_log_enabled AND
1036 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1037 THEN
1038 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1039 , G_PKG_NAME
1040 , G_PKG_NAME || l_program || ': ' || sqlerrm);
1041 END IF;
1042
1043 END Verify_Org_Attributes;
1044
1045 --========================================================================
1046 -- PROCEDURE : Verify_Shikyu_Attributes PRIVATE
1047 -- PARAMETERS:
1048 -- p_osa_item_id Item
1049 -- p_component_item_id Shikyu Component
1050 -- p_oem_organization_id Organization
1051 -- p_tp_organization_id Tp Organization
1052 -- x_return_status Return STatus
1053 -- COMMENT : This procedure validates if the Shikyu component
1054 -- has the attribute enabled in system items for both OEM and TP Org
1055 --========================================================================
1056 PROCEDURE Verify_Shikyu_Attributes
1057 ( p_osa_item_id IN NUMBER
1058 , p_component_item_id IN NUMBER
1059 , p_oem_organization_id IN NUMBER
1060 , p_tp_organization_id IN NUMBER
1061 , x_return_status OUT NOCOPY VARCHAR2
1062 )
1063 IS
1064 --=================
1065 -- LOCAL VARIABLES
1066 --=================
1067 l_msg_list VARCHAR2(2000);
1068 l_msg_data VARCHAR2(2000);
1069 l_msg_count NUMBER;
1070 l_return_status VARCHAR2(1);
1071 l_osa_flag NUMBER;
1072 l_comp_flag NUMBER;
1073 l_program CONSTANT VARCHAR2(30) := 'Verify_Shikyu_Attributes';
1074 BEGIN
1075
1076 IF g_log_enabled THEN
1077 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1079 , G_PKG_NAME
1080 , '>> ' || l_program || ': Start'
1081 );
1082 END IF;
1083 END IF;
1084
1085 -- Check if the Shikyu component flag is enabled at the OEM orgn
1086
1087 JMF_SHIKYU_GRP.Get_Shikyu_Attributes
1088 ( p_api_version => 1.0
1089 , p_init_msg_list => l_msg_list
1090 , x_return_status => l_return_status
1091 , x_msg_count => l_msg_count
1092 , x_msg_data => l_msg_data
1093 , p_organization_id => p_oem_organization_id
1094 , p_item_id => p_component_item_id
1095 , x_outsourced_assembly => l_osa_flag
1096 , x_subcontracting_component => l_comp_flag
1097 );
1098
1099 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
1100 (l_comp_flag IN (1,2))
1101 THEN
1102 -- Check if the Shikyu component flag is enabled at the TP orgn
1103
1104 JMF_SHIKYU_GRP.Get_Shikyu_Attributes
1105 ( p_api_version => 1.0
1106 , p_init_msg_list => l_msg_list
1107 , x_return_status => l_return_status
1108 , x_msg_count => l_msg_count
1109 , x_msg_data => l_msg_data
1110 , p_organization_id => p_tp_organization_id
1111 , p_item_id => p_component_item_id
1112 , x_outsourced_assembly => l_osa_flag
1113 , x_subcontracting_component => l_comp_flag
1114 );
1115
1116 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
1117 (l_comp_flag IN (1,2))
1118 THEN
1119 x_return_status := FND_API.G_RET_STS_SUCCESS;
1120 ELSE
1121 x_return_status := FND_API.G_RET_STS_ERROR;
1122 FND_MESSAGE.set_name('JMF', 'JMF_SHK_COMP_ATTR_ERR');
1123 FND_MSG_PUB.add;
1124
1125 IF g_log_enabled AND
1126 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1127 THEN
1128 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1129 , G_PKG_NAME
1130 , '>> ' || l_program
1131 || ': Subcontracting Component flag not equals to 1 or 2 '
1132 || 'for component_id = '
1133 || p_component_item_id
1134 || ', tp_organization_id = '
1135 || p_tp_organization_id
1136 );
1137 END IF;
1138
1139 END IF;
1140 ELSE
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 FND_MESSAGE.set_name('JMF', 'JMF_SHK_COMP_ATTR_ERR');
1143 FND_MSG_PUB.add;
1144
1145 IF g_log_enabled AND
1146 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1147 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1148 , G_PKG_NAME
1149 , '>> ' || l_program
1150 || ': Subcontracting Component flag not equals to 1 or 2 '
1151 || 'for component_id = '
1152 || p_component_item_id
1153 || ', oem_organization_id = '
1154 || p_oem_organization_id
1155 );
1156 END IF;
1157
1158 END IF;
1159
1160
1161 IF g_log_enabled THEN
1162 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1163 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1164 , G_PKG_NAME
1165 , '>> ' || l_program || ': Exit'
1166 );
1167 END IF;
1168 END IF;
1169
1170 END Verify_Shikyu_Attributes;
1171
1172
1173 --========================================================================
1174 -- PROCEDURE : Load_Subcontract_Orders PRIVATE
1175 -- PARAMETERS:
1176 -- p_operating_unit Operating Unit
1177 -- p_from_organization From Organization
1178 -- p_to_organization To Organization
1179 -- COMMENT : This procedure loads all the PO Shipment lines
1180 -- that are eligible for processing the SHIKYU business flow.
1181 -- It populates the JMF_SUBCONTRACT_
1182 -- ORDERS table with the Shipment lines of the OSA item .
1183 -- The column interlock_status in JMF_SUBCONTRACT_ORDERS will
1184 -- be updated as follows:
1185 -- 'N' - New , components are not yet loaded
1186 -- 'E' - Error when loading components, hence components are not loaded
1187 -- 'U' - Unprocessed (WIP job failure)
1188 -- 'P' - Processed (flow is complete)
1189 -- 'C' - Closed (after receiving is complete)
1190 --========================================================================
1191 PROCEDURE Load_Subcontract_Orders
1192 ( p_operating_unit IN NUMBER
1193 , p_from_organization IN NUMBER
1194 , p_to_organization IN NUMBER
1195 )
1196 IS
1197 --=================
1198 -- LOCAL VARIABLES
1199 --=================
1200
1201 TYPE l_project_rec IS RECORD
1202 ( project_id NUMBER
1203 , task_id NUMBER
1204 , line_location_id NUMBER
1205 );
1206
1207 TYPE l_project_Tabtyp IS TABLE of l_project_rec
1208 INDEX BY PLS_INTEGER;
1209
1210
1211 l_subcontract_rec g_SubcontractTabTyp;
1212 l_count NUMBER;
1213 l_valid_flag VARCHAR2(1);
1214 l_program CONSTANT VARCHAR2(30) := 'Load_Subcontract_Orders';
1215 l_exists BOOLEAN := FALSE;
1216 l_eam_enabled VARCHAR2(1);
1217 l_wms_enabled VARCHAR2(1);
1218 l_process_enabled VARCHAR2(1);
1219 l_shipment_id NUMBER;
1220 l_curr_index NUMBER;
1221 l_project_tbl l_project_Tabtyp;
1222
1223 --=================
1224 -- CURSORS
1225 --=================
1226
1227 CURSOR c_load_cur IS
1228 SELECT
1229 subcontract_po_shipment_id
1230 , subcontract_po_header_id
1231 , subcontract_po_line_id
1232 , oem_organization_id
1233 , tp_organization_id
1234 , need_by_date
1235 , vendor_id
1236 , vendor_site_id
1237 , uom
1238 , currency
1239 , quantity
1240 , osa_item_id
1241 , osa_item_price
1242 , project_id
1243 , task_id
1244 FROM JMF_SUBCONTRACT_ORDERS_TEMP;
1245
1246 -- Bugs 5198838 and 5212219: Should get the project id for the distributions
1247 -- of the Subcontracting Order Shipment as long as it is NOT NULL.
1248 -- Should not restrict the task id to be NOT NULL as well.
1249 CURSOR c_project_csr IS
1250 SELECT distinct project_id
1251 , task_id
1252 , line_location_id
1253 FROM po_distributions_all
1254 WHERE line_location_id = l_shipment_id
1255 AND project_id IS NOT NULL;
1256 --AND task_id IS NOT NULL;
1257
1258 BEGIN
1259
1260 IF g_log_enabled THEN
1261 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1262 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1263 , G_PKG_NAME
1264 , '>> ' || l_program || ': Start'
1265 );
1266 END IF;
1267 END IF;
1268
1269 --Get all the Subcontract orders that were not processed by the
1270 --Interlock processor.
1271
1272 INSERT INTO JMF_SUBCONTRACT_ORDERS_TEMP
1273 ( subcontract_po_shipment_id
1274 , subcontract_po_header_id
1275 , subcontract_po_line_id
1276 , oem_organization_id
1277 , tp_organization_id
1278 , need_by_date
1279 , vendor_id
1280 , vendor_site_id
1281 , uom
1282 , currency
1283 , quantity
1284 , osa_item_id
1285 , osa_item_price
1286 , project_id
1287 , task_id
1288 )
1289 SELECT /*+ PARALLEL(poll) */
1290 poll.line_location_id
1291 , poll.po_header_id
1292 , poll.po_line_id
1293 , poll.ship_to_organization_id
1294 , null
1295 , nvl(poll.need_by_date, poll.promised_date)
1296 , poh.vendor_id
1297 , poh.vendor_site_id
1298 , muom.uom_code
1299 , poh.currency_code
1300 , poll.quantity
1301 , pol.item_id
1302 , poll.price_override
1303 , pol.project_id
1304 , pol.task_id
1305 FROM
1306 po_line_locations_all poll
1307 , po_headers_all poh
1308 , po_lines_all pol
1309 , mtl_units_of_measure muom
1310 , po_releases_all por
1311 WHERE poll.po_header_id = poh.po_header_id
1312 AND poll.po_line_id = pol.po_line_id
1313 AND poh.po_header_id = pol.po_header_id
1314 AND poll.po_release_id = por.po_release_id (+)
1315 AND NVL(poll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = muom.unit_of_measure
1316 AND ((pol.closed_code = 'OPEN') OR (pol.closed_code IS NULL))
1317 AND poh.approved_flag = 'Y'
1318 AND nvl(poh.cancel_flag, 'N') = 'N'
1319 AND nvl(pol.cancel_flag, 'N') = 'N'
1320 AND nvl(poll.cancel_flag, 'N') = 'N'
1321 AND poll.outsourced_assembly = 1
1322 AND poll.org_id = p_operating_unit
1323 AND DECODE(poll.po_release_id,
1324 NULL, 'Y',
1325 por.approved_flag) = 'Y'
1326 AND poll.ship_to_organization_id
1327 BETWEEN
1328 (NVL(p_from_organization,poll.ship_to_organization_id))
1329 AND
1330 (NVL(p_to_organization,poll.ship_to_organization_id)
1331 )
1332 AND NOT EXISTS
1333 ( SELECT subcontract_po_shipment_id
1334 FROM JMF_SUBCONTRACT_ORDERS jso
1335 WHERE poll.line_location_id = jso.subcontract_po_shipment_id
1336 );
1337
1338 OPEN c_load_cur;
1339 FETCH c_load_cur
1340 BULK COLLECT INTO l_subcontract_rec;
1341 CLOSE c_load_cur;
1342
1343 l_count := l_subcontract_rec.COUNT;
1344
1345
1346 IF l_subcontract_rec.COUNT > 0 THEN
1347 FOR i IN l_subcontract_rec.FIRST..l_subcontract_rec.LAST
1348 LOOP
1349
1350 -- For the shipment lines fetched, check the cache to see if the
1351 -- OEM org(ship_to_organization) is valid. If the orgn does not
1352 -- exists in the cache, invoke Verify_Org_attributes procedure
1353 -- to validate.
1354
1355 IF g_org_tbl.COUNT > 0
1356 THEN
1357 FOR k in g_org_tbl.FIRST .. g_org_tbl.LAST
1358 LOOP
1359 IF(g_org_tbl(k).organization_id = l_subcontract_rec(i).oem_organization_id)
1360 THEN
1361 l_valid_flag := g_org_tbl(k).status;
1362 l_exists := TRUE;
1363 EXIT;
1364 ELSE
1365 l_exists := FALSE;
1366 END IF;
1367
1368 END LOOP;
1369 END IF;
1370
1371 IF NOT(l_exists)
1372 THEN
1373 Verify_Org_Attributes
1374 ( p_organization_id => l_subcontract_rec(i).oem_organization_id
1375 , x_eam_enabled => l_eam_enabled
1376 , x_wms_enabled => l_wms_enabled
1377 , x_process_enabled => l_process_enabled
1378 );
1379 END IF;
1380
1381 IF ((l_eam_enabled = 'N') AND (l_wms_enabled='N') AND (l_process_enabled = 'N'))
1382 OR (l_valid_flag ='Y')
1383 THEN
1384 Validate_OSA_Item
1385 ( p_item_id => l_subcontract_rec(i).osa_item_id
1386 , p_organization_id => l_subcontract_rec(i).oem_organization_id
1387 , p_vendor_id => l_subcontract_rec(i).vendor_id
1388 , p_vendor_site_id => l_subcontract_rec(i).vendor_site_id
1389 , x_tp_organization_id => l_subcontract_rec(i).tp_organization_id
1390 , x_valid_flag => l_valid_flag
1391 );
1392 ELSE
1393 l_valid_flag := 'N';
1394 END IF;
1395
1396 -- Bug 5500896: Validate Project reference only if all the previous validations
1397 -- have passed to avoid resetting of the l_valid_flag from 'N' back to 'Y'
1398
1399 IF l_valid_flag = 'Y'
1400 THEN
1401
1402 -- Check if the shipment line has distributions allocated to different projects
1403 -- and tasks.
1404 -- Since the project and task attributes are stored at distribution level
1405 -- we need to ensure that the shipment line which can have multiple distributions
1406 -- should be allocated to only one project/task. IF the shipment line is allocated
1407 -- to multiple project/task then mark the status as not valid. We will not
1408 -- process these records.
1409
1410 l_shipment_id := l_subcontract_rec(i).subcontract_po_shipment_id;
1411
1412 /*
1413 OPEN c_project_csr;
1414 LOOP
1415 l_curr_index := l_project_tbl.COUNT;
1416 FETCH c_project_csr INTO l_project_tbl(l_curr_index) ;
1417 IF c_project_csr%NOTFOUND
1418 THEN
1419 EXIT;
1420 END IF;
1421 END LOOP;
1422 CLOSE c_project_csr;
1423 */
1424
1425 -- Bugs 5198838 and 5212219: Open the c_project_csr to get the
1426 -- appropriate project and task reference
1427
1428 OPEN c_project_csr;
1429 FETCH c_project_csr
1430 BULK COLLECT INTO l_project_tbl;
1431
1432 IF l_project_tbl.COUNT > 1
1433 THEN
1434 l_valid_flag := 'N';
1435 ELSIF l_project_tbl.COUNT = 1
1436 THEN
1437 l_valid_flag := 'Y';
1438 l_curr_index := l_project_tbl.FIRST;
1439
1440 UPDATE jmf_subcontract_orders_temp
1441 SET project_id = l_project_tbl(l_curr_index).project_id
1442 , task_id = l_project_tbl(l_curr_index).task_id
1443 WHERE subcontract_po_shipment_id = l_shipment_id;
1444 ELSE
1445 -- l_valid_flag := 'Y';
1446 NULL;
1447 END IF;
1448
1449 CLOSE c_project_csr;
1450
1451 END IF; /* IF l_valid_flag = 'Y' */
1452
1453 -- If the shipment line is marked as not valid to be processed, delete
1454 -- from the temp table so that we do not need to load it in JMF_SUBCONTRACT_
1455 -- ORDERS table for processing.
1456
1457 IF l_valid_flag = 'N'
1458 THEN
1459 DELETE FROM JMF_SUBCONTRACT_ORDERS_TEMP
1460 WHERE subcontract_po_shipment_id = l_subcontract_rec(i).subcontract_po_shipment_id;
1461
1462 IF g_log_enabled THEN
1463 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1464 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1465 , G_PKG_NAME
1466 , '>> ' || l_program
1467 || ': l_valid_flag is ''N'': Deleting record with subcontract_po_shipment_id = '
1468 || l_subcontract_rec(i).subcontract_po_shipment_id
1469 || ' from JMF_SUBCONTRACT_ORDERS_TEMP'
1470 );
1471 END IF;
1472 END IF;
1473 ELSE
1474 UPDATE JMF_SUBCONTRACT_ORDERS_TEMP
1475 SET tp_organization_id = l_subcontract_rec(i).tp_organization_id
1476 WHERE subcontract_po_shipment_id = l_subcontract_rec(i).subcontract_po_shipment_id;
1477
1478 END IF;
1479 END LOOP;
1480
1481 -- This will ensure if multiple process of Interlock is run, and these processes
1482 -- happen to pick up the same shipment line, then the load will only
1483 -- pick up the shipment line that is not processed by the other process.
1484 -- The new shipment lines are marked as 'N' which indicates they need to be
1485 -- processed. 'N' is the first step in processing of the shipment lines.
1486
1487 MERGE INTO JMF_SUBCONTRACT_ORDERS jso
1488 USING (SELECT subcontract_po_shipment_id
1489 , subcontract_po_header_id
1490 , subcontract_po_line_id
1491 , oem_organization_id
1492 , tp_organization_id
1493 , osa_item_id
1494 , osa_item_price
1495 , need_by_date
1496 , uom
1497 , currency
1498 , quantity
1499 , project_id
1500 , task_id
1501 FROM JMF_SUBCONTRACT_ORDERS_TEMP ) jsot
1502 ON ( jso.subcontract_po_shipment_id = jsot.subcontract_po_shipment_id)
1503 WHEN NOT MATCHED THEN
1504 INSERT
1505 ( jso.subcontract_po_shipment_id
1506 , jso.subcontract_po_header_id
1507 , jso.subcontract_po_line_id
1508 , jso.oem_organization_id
1509 , jso.tp_organization_id
1510 , jso.osa_item_id
1511 , jso.osa_item_price
1512 , jso.need_by_date
1513 , jso.uom
1514 , jso.currency
1515 , jso.quantity
1516 , jso.batch_id
1517 , jso.project_id
1518 , jso.task_id
1519 , jso.last_update_date
1520 , jso.last_updated_by
1521 , jso.creation_date
1522 , jso.created_by
1523 , jso.last_update_login
1524 , jso.interlock_status
1525 )
1526 VALUES
1527 ( jsot.subcontract_po_shipment_id
1528 , jsot.subcontract_po_header_id
1529 , jsot.subcontract_po_line_id
1530 , jsot.oem_organization_id
1531 , jsot.tp_organization_id
1532 , jsot.osa_item_id
1533 , jsot.osa_item_price
1534 , jsot.need_by_date
1535 , jsot.uom
1536 , jsot.currency
1537 , jsot.quantity
1538 , -1
1539 , jsot.project_id
1540 , jsot.task_id
1541 , sysdate
1542 , FND_GLOBAL.user_id
1543 , sysdate
1544 , FND_GLOBAL.user_id
1545 , FND_GLOBAL.login_id
1546 , 'N'
1547 );
1548
1549 END IF;
1550
1551 -- To reprocess the existing records which are marked as error,
1552 -- update the interlock_status flag to 'N' so that they can be
1553 -- processed in this run.
1554
1555 UPDATE jmf_subcontract_orders
1556 SET interlock_status ='N'
1557 , batch_id = -1
1558 , last_update_date = sysdate
1559 , last_updated_by = FND_GLOBAL.user_id
1560 , last_update_login = FND_GLOBAL.login_id
1561 WHERE interlock_status = 'E'
1562 AND EXISTS
1563 ( SELECT 'X'
1564 FROM jmf_subcontract_orders
1565 WHERE interlock_status = 'E');
1566
1567 IF g_log_enabled THEN
1568 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1569 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1570 , G_PKG_NAME
1571 , '>> ' || l_program || ': Exit'
1572 );
1573 END IF;
1574 END IF;
1575
1576 EXCEPTION
1577
1578 WHEN OTHERS THEN
1579 IF g_log_enabled AND
1580 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1581 THEN
1582 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1583 , G_PKG_NAME
1584 , G_PKG_NAME || l_program || ': ' || sqlerrm);
1585 END IF;
1586
1587 FND_MESSAGE.set_name('JMF', 'JMF_SHK_OSA_LD_ERR');
1588 FND_MSG_PUB.add;
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590
1591 END Load_Subcontract_Orders;
1592
1593 --========================================================================
1594 -- PROCEDURE : Load_Replenishments PRIVATE
1595 -- PARAMETERS:
1596 -- p_operating_unit Operating Unit
1597 -- p_from_organization From Organization
1598 -- p_to_organization To Organization
1599 -- COMMENT : This procedure loads all the PO Shipment lines
1600 -- that are eligible for processing the SHIKYU business flow.
1601 -- It populates the JMF_SUBCONTRACT_
1602 -- ORDERS table with the Shipment lines of the OSA item .
1603 --========================================================================
1604 PROCEDURE Load_Replenishments
1605 ( p_operating_unit IN NUMBER
1606 , p_from_organization IN NUMBER
1607 , p_to_organization IN NUMBER
1608 )
1609 IS
1610
1611 --=================
1612 -- CURSORS
1613 --=================
1614
1615 -- Selects the Replenishment POs with the supplier matching the OEM
1616 -- Organization and the Ship To Organization matching the TP
1617 -- Organization of some SHIKYU enabled shipping network.
1618
1619 -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
1620 -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record
1621
1622 CURSOR c_rep IS
1623 SELECT DISTINCT plla.line_location_id,
1624 hoi.organization_id as oem_organization_id,
1625 plla.ship_to_organization_id as tp_organization_id,
1626 pla.item_id as shikyu_component_id,
1627 msib.subcontracting_component,
1628 plla.quantity,
1629 plla.need_by_date,
1630 NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
1631 pha.reference_num,
1632 pha.segment1,
1633 pla.line_num,
1634 plla.shipment_num
1635 FROM hr_organization_information hoi,
1636 po_line_locations_all plla,
1637 po_lines_all pla,
1638 po_headers_all pha,
1639 mtl_interorg_parameters mip,
1640 mtl_system_items_b msib
1641 WHERE hoi.org_information_context = 'Customer/Supplier Association'
1642 AND TO_NUMBER(hoi.org_information3) = pha.vendor_id
1643 AND TO_NUMBER(hoi.org_information4) = pha.vendor_site_id
1644 AND mip.to_organization_id = plla.ship_to_organization_id
1645 AND mip.from_organization_id = hoi.organization_id
1646 --AND mip.SHIKYU_ENABLED_FLAG = 'Y' /* SHIKYU_ENABLED_FLAG is no longer used*/
1647 AND mip.subcontracting_type in ('B','C') /* 12.1 Buy/Sell Subcontracting Changes */
1648 AND plla.po_line_id = pla.po_line_id
1649 AND plla.po_header_id = pha.po_header_id
1650 AND plla.org_id = p_operating_unit
1651 AND pla.item_id = msib.inventory_item_id
1652 AND hoi.organization_id = msib.organization_id
1653 AND msib.subcontracting_component in (1, 2)
1654 AND pha.approved_flag = 'Y'
1655 AND nvl(pha.cancel_flag, 'N') = 'N'
1656 AND nvl(pla.cancel_flag, 'N') = 'N'
1657 AND nvl(plla.cancel_flag, 'N') = 'N'
1658 AND hoi.organization_id
1659 BETWEEN
1660 (NVL(p_from_organization, hoi.organization_id))
1661 AND
1662 (NVL(p_to_organization, hoi.organization_id)
1663 )
1664 AND NOT EXISTS (SELECT jsr.replenishment_so_line_id
1665 FROM jmf_shikyu_replenishments jsr
1666 WHERE jsr.replenishment_po_shipment_id = plla.line_location_id)
1667 ORDER BY plla.need_by_date,
1668 pha.segment1,
1669 pla.line_num,
1670 plla.shipment_num;
1671
1672 -- Selects the Subcontracting PO Shipments that have been processed and still
1673 -- open, but not yet fully allocated, along with the component needing more
1674 -- allocation, and the required and allocated quantities
1675 /*
1676 CURSOR c_alloc IS
1677 SELECT jsc.subcontract_po_shipment_id
1678 , jsc.shikyu_component_id
1679 , sum(nvl(jsa.allocated_quantity,0))
1680 , max(nvl(wro.required_quantity,0))
1681 FROM jmf_shikyu_allocations jsa
1682 , jmf_shikyu_components jsc
1683 , jmf_subcontract_orders jso
1684 , wip_requirement_operations wro
1685 WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1686 AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1687 AND jsc.shikyu_component_id=jsa.shikyu_component_id(+)
1688 AND jso.interlock_status = 'P'
1689 AND wro.wip_entity_id = jso.wip_entity_id
1690 AND wro.inventory_item_id = jsc.shikyu_component_id
1691 AND wro.organization_id = jso.tp_organization_id
1692 GROUP BY jsc.shikyu_component_id
1693 , jsc.subcontract_po_shipment_id
1694 HAVING sum(nvl(jsa.allocated_quantity,0)) <
1695 avg(nvl(wro.required_quantity,0));
1696 */
1697
1698 CURSOR c_alloc IS
1699 SELECT jsc.subcontract_po_shipment_id
1700 , jsc.shikyu_component_id
1701 , max(TO_NUMBER(pha.segment1))
1702 , max(TO_NUMBER(pla.line_num))
1703 , max(TO_NUMBER(plla.shipment_num))
1704 , max(plla.need_by_date)
1705 , sum(nvl(jsa.allocated_quantity,0))
1706 , max(nvl(wro.required_quantity,0))
1707 , max(jsc.replen_so_creation_failed)
1708 FROM jmf_shikyu_allocations jsa
1709 , jmf_shikyu_components jsc
1710 , jmf_subcontract_orders jso
1711 , wip_requirement_operations wro
1712 , po_line_locations_all plla
1713 , po_lines_all pla
1714 , po_headers_all pha
1715 WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1716 AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1717 AND jsc.shikyu_component_id = jsa.shikyu_component_id(+)
1718 AND jso.interlock_status = 'P'
1719 AND wro.wip_entity_id = jso.wip_entity_id
1720 AND wro.inventory_item_id = jsc.shikyu_component_id
1721 AND wro.organization_id = jso.tp_organization_id
1722 AND plla.line_location_id = jso.subcontract_po_shipment_id
1723 AND plla.po_line_id = pla.po_line_id
1724 AND plla.po_header_id = pha.po_header_id
1725 AND plla.org_id = p_operating_unit
1726 AND nvl(pha.cancel_flag, 'N') = 'N'
1727 AND nvl(pla.cancel_flag, 'N') = 'N'
1728 AND nvl(plla.cancel_flag, 'N') = 'N'
1729 AND jso.oem_organization_id
1730 BETWEEN
1731 NVL(p_from_organization, jso.oem_organization_id)
1732 AND
1733 NVL(p_to_organization, jso.oem_organization_id)
1734 GROUP BY jsc.shikyu_component_id
1735 , jsc.subcontract_po_shipment_id
1736 HAVING sum(nvl(jsa.allocated_quantity,0)) <
1737 avg(nvl(wro.required_quantity,0))
1738 ORDER BY max(plla.need_by_date),
1739 max(TO_NUMBER(pha.segment1)),
1740 max(TO_NUMBER(pla.line_num)),
1741 max(TO_NUMBER(plla.shipment_num));
1742
1743 --=================
1744 -- LOCAL VARIABLES
1745 --=================
1746
1747 l_subcontract_rec g_SubcontractTabTyp;
1748 l_count NUMBER;
1749 l_program CONSTANT VARCHAR2(30) := 'Load_Replenishments';
1750 l_exists BOOLEAN := FALSE;
1751 k INTEGER;
1752 l_line_location_id NUMBER;
1753 l_oem_organization_id NUMBER;
1754 l_tp_organization_id NUMBER;
1755 l_tp_supplier_id NUMBER;
1756 l_tp_supplier_site_id NUMBER;
1757 l_component_id NUMBER;
1758 l_msg_list VARCHAR2(2000);
1759 l_msg_data VARCHAR2(2000);
1760 l_msg_count NUMBER;
1761 l_return_status VARCHAR2(1);
1762 l_osa_flag NUMBER;
1763 l_comp_flag NUMBER;
1764 --l_so_quantity NUMBER;
1765 l_order_header_id NUMBER;
1766 l_order_line_id NUMBER;
1767 l_ship_date DATE;
1768 l_ordered_uom VARCHAR2(3);
1769 l_ordered_quantity NUMBER;
1770 l_primary_uom_qty NUMBER;
1771 l_primary_uom VARCHAR2(3);
1772 l_additional_supply VARCHAR2(1);
1773 l_po_uom PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE;
1774 l_po_quantity NUMBER;
1775 --l_shipment_id NUMBER;
1776 l_osa_shipment_id NUMBER;
1777 l_osa_component_id NUMBER;
1778 l_total_qty NUMBER;
1779 l_allocated_qty NUMBER;
1780 l_qty NUMBER;
1781 l_reference_num VARCHAR2(25);
1782 l_po_need_by_date DATE;
1783 l_po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE;
1784 l_po_line_num PO_LINES.LINE_NUM%TYPE;
1785 l_po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE;
1786 l_subcontracting_component
1787 NUMBER;
1788 l_replen_so_creation_failed
1789 VARCHAR2(1);
1790
1791 BEGIN
1792 IF g_log_enabled THEN
1793 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1794 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1795 , G_PKG_NAME
1796 , '>> ' || l_program || ': Start'
1797 );
1798 END IF;
1799 END IF;
1800
1801 -- Pick up all the replenishments created manually.
1802
1803 OPEN c_rep;
1804 LOOP
1805
1806 <<skip_curr_replen_po>>
1807
1808 FETCH c_rep
1809 INTO l_line_location_id
1810 , l_oem_organization_id
1811 , l_tp_organization_id
1812 , l_component_id
1813 , l_subcontracting_component
1814 , l_po_quantity
1815 , l_po_need_by_date
1816 , l_po_uom
1817 , l_reference_num
1818 , l_po_header_num
1819 , l_po_line_num
1820 , l_po_shipment_num;
1821
1822 IF c_rep%NOTFOUND
1823 THEN
1824 EXIT;
1825 END IF;
1826
1827 IF g_log_enabled THEN
1828 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1829 THEN
1830 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1831 , G_PKG_NAME
1832 , '>> ' || l_program || ': Fetching Replenishment POs not yet loaded');
1833
1834 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1835 , G_PKG_NAME
1836 , '>> ' || l_program
1837 || ': l_line_location_id = ' || l_line_location_id
1838 || ', l_oem_organization_id = ' || l_oem_organization_id
1839 || ', l_tp_organization_id = ' || l_tp_organization_id
1840 || ', l_component_id = ' || l_component_id
1841 || ', l_po_quantity = ' || l_po_quantity
1842 || ', l_po_need_by_date = ' || l_po_need_by_date
1843 || ', l_po_uom = ' || l_po_uom
1844 || ', l_reference_num = ' || l_reference_num
1845 );
1846 END IF;
1847 END IF;
1848
1849 -- Check if component is pre-positioned or sync ship
1850 JMF_SHIKYU_GRP.Get_Shikyu_Attributes
1851 ( p_api_version => 1.0
1852 , p_init_msg_list => l_msg_list
1853 , x_return_status => l_return_status
1854 , x_msg_count => l_msg_count
1855 , x_msg_data => l_msg_data
1856 , p_organization_id => l_tp_organization_id
1857 , p_item_id => l_component_id
1858 , x_outsourced_assembly => l_osa_flag
1859 , x_subcontracting_component => l_comp_flag
1860 );
1861
1862 IF g_log_enabled THEN
1863 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1864 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1865 , G_PKG_NAME
1866 , '>> ' || l_program
1867 || ': After calling Get_Shikyu_Attributes: l_return_status = '
1868 || l_return_status
1869 || ', l_osa_flag = '||l_osa_flag
1870 || ', l_comp_flag = '||l_comp_flag
1871 );
1872 END IF;
1873 END IF;
1874
1875 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
1876 (l_comp_flag = 1) -- Pre-positioned
1877 THEN
1878
1879 l_additional_supply := 'N';
1880
1881 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
1882 (l_comp_flag = 2) -- sync-ship
1883 THEN
1884
1885 -- This indicates that there are PO's that are created but not picked
1886 -- up the Interlock even though they are sync. ship. This could have
1887 -- happened because when interlock was run previously, it did not
1888 -- complete creating the replenishment PO for sync ship
1889
1890 IF l_reference_num IS NULL
1891 THEN
1892 l_additional_supply := 'Y';
1893 ELSE
1894 l_additional_supply := 'N';
1895 END IF;
1896
1897 ELSE
1898
1899 --EXIT;
1900 GOTO skip_curr_replen_po;
1901
1902 END IF;
1903
1904 -- Create replenishment SO for all the replenishment PO's that are
1905 -- created manually.
1906
1907 JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
1908 ( p_action => 'C' --Create
1909 , p_subcontract_po_shipment_id => NULL --l_shipment_id
1910 , p_quantity => l_po_quantity
1911 , p_item_id => l_component_id
1912 , p_replen_po_shipment_id => l_line_location_id
1913 , p_oem_organization_id => l_oem_organization_id
1914 , p_tp_organization_id => l_tp_organization_id
1915 , x_return_status => l_return_status
1916 , x_order_line_id => l_order_line_id
1917 );
1918
1919 IF g_log_enabled AND
1920 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1922 , G_PKG_NAME
1923 , '>> ' || l_program
1924 || ': After calling Process_Replenishment_SO: l_return_status = '
1925 || l_return_status
1926 || ', l_order_line_id: = '
1927 || l_order_line_id
1928 );
1929 END IF;
1930
1931 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1932 THEN
1933
1934 SELECT header_id
1935 , schedule_ship_date
1936 INTO l_order_header_id
1937 , l_ship_date
1938 FROM oe_order_lines_all
1939 WHERE line_id = l_order_line_id;
1940
1941 SELECT primary_uom_code
1942 INTO l_primary_uom
1943 FROM mtl_system_items
1944 WHERE inventory_item_id = l_component_id
1945 AND organization_id = l_tp_organization_id;
1946
1947 l_ordered_uom:= JMF_SHIKYU_UTIL.Get_UOM_Code(l_po_uom);
1948
1949 IF l_primary_uom <> l_ordered_uom
1950 THEN
1951 l_primary_uom_qty := INV_CONVERT.inv_um_convert
1952 ( item_id => l_component_id
1953 , precision => 5
1954 , from_quantity => l_po_quantity
1955 , from_unit => l_ordered_uom
1956 , to_unit => l_primary_uom
1957 , from_name => null
1958 , to_name => null
1959 );
1960 ELSE
1961 l_primary_uom_qty := l_po_quantity;
1962 l_primary_uom := l_ordered_uom;
1963 END IF; /* IF l_primary_uom <> l_ordered_uom */
1964
1965 -- To get the supplier id and supplier site id associated
1966 -- with the TP Organization
1967 SELECT TO_NUMBER(org_information3),
1968 TO_NUMBER(org_information4)
1969 INTO l_tp_supplier_id,
1970 l_tp_supplier_site_id
1971 FROM hr_organization_information
1972 WHERE organization_id = l_tp_organization_id
1973 AND org_information_context = 'Customer/Supplier Association';
1974
1975 INSERT INTO JMF_SHIKYU_REPLENISHMENTS
1976 ( replenishment_so_line_id
1977 , replenishment_so_header_id
1978 , schedule_ship_date
1979 , replenishment_po_header_id
1980 , replenishment_po_line_id
1981 , replenishment_po_shipment_id
1982 , oem_organization_id
1983 , tp_organization_id
1984 , tp_supplier_id
1985 , tp_supplier_site_id
1986 , shikyu_component_id
1987 , ordered_quantity
1988 , ordered_primary_uom_quantity
1989 , uom
1990 , primary_uom
1991 , org_id
1992 , additional_supply
1993 , last_update_date
1994 , last_updated_by
1995 , creation_date
1996 , created_by
1997 , last_update_login
1998 , allocable_quantity
1999 , allocable_primary_uom_quantity
2000 , allocated_quantity
2001 , allocated_primary_uom_quantity
2002 )
2003 SELECT
2004 l_order_line_id
2005 , l_order_header_id
2006 , l_ship_date
2007 , poll.po_header_id
2008 , poll.po_line_id
2009 , poll.line_location_id
2010 , l_oem_organization_id
2011 , l_tp_organization_id
2012 , l_tp_supplier_id
2013 , l_tp_supplier_site_id
2014 , l_component_id
2015 , poll.quantity
2016 , l_primary_uom_qty
2017 , l_ordered_uom
2018 , l_primary_uom
2019 , poll.org_id
2020 , l_additional_supply
2021 , sysdate
2022 , FND_GLOBAL.user_id
2023 , sysdate
2024 , FND_GLOBAL.user_id
2025 , FND_GLOBAL.login_id
2026 , poll.quantity
2027 , l_primary_uom_qty
2028 , 0
2029 , 0
2030 FROM po_line_locations_all poll
2031 WHERE poll.line_location_id = l_line_location_id;
2032
2033 ELSE
2034
2035 IF l_subcontracting_component = 2
2036 THEN
2037
2038 l_osa_shipment_id := TO_NUMBER(SUBSTR(l_reference_num, 1, INSTR(l_reference_num, '-') - 1));
2039
2040 UPDATE jmf_shikyu_components
2041 SET replen_so_creation_failed = 'Y'
2042 , last_update_date = sysdate
2043 , last_updated_by = FND_GLOBAL.user_id
2044 , last_update_login = FND_GLOBAL.login_id
2045 WHERE subcontract_po_shipment_id = l_osa_shipment_id
2046 AND shikyu_component_id = l_component_id;
2047
2048 IF g_log_enabled
2049 AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2050 THEN
2051 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2052 , G_PKG_NAME
2053 , '>> ' || l_program || ': Setting replen_so_creation_failed = ''Y'' '
2054 || 'for subcontract_po_shipment_id = '
2055 || l_osa_shipment_id
2056 || ', shikyu_component_id = '
2057 || l_component_id
2058 );
2059 END IF;
2060
2061 END IF;
2062
2063 END IF; /* IF l_return_status = FND_API.G_RET_STS_SUCCESS */
2064
2065 END LOOP;
2066
2067 IF g_log_enabled THEN
2068 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2069 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2070 , G_PKG_NAME
2071 , '>> ' || l_program || ': Exit'
2072 );
2073 END IF;
2074 END IF;
2075
2076 -- After loading replenishments, auto allocate for all the shipment lines
2077 -- wherein the allocated quantity is less than required quantity.
2078 -- This will prevent unnecessarily creating new replenishments which
2079 -- can be allocated.
2080
2081 OPEN c_alloc;
2082 LOOP
2083 FETCH c_alloc
2084 INTO l_osa_shipment_id
2085 , l_osa_component_id
2086 , l_po_header_num
2087 , l_po_line_num
2088 , l_po_shipment_num
2089 , l_po_need_by_date
2090 , l_allocated_qty
2091 , l_total_qty
2092 , l_replen_so_creation_failed;
2093
2094 IF c_alloc%NOTFOUND
2095 THEN
2096 EXIT;
2097 ELSE
2098
2099 IF g_log_enabled
2100 AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2101 THEN
2102 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2103 , G_PKG_NAME
2104 , '>> ' || l_program || ': Fetching Subcontract Orders not fully allocated');
2105
2106 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2107 , G_PKG_NAME
2108 , '>> ' || l_program
2109 || ': l_osa_shipment_id = ' || l_osa_shipment_id
2110 || ', l_osa_component_id = ' || l_osa_component_id
2111 || ', l_po_header_num = ' || l_po_header_num
2112 || ', l_po_line_num = ' || l_po_line_num
2113 || ', l_po_shipment_num = ' || l_po_shipment_num
2114 || ', l_po_need_by_date = ' || l_po_need_by_date
2115 || ', l_allocated_qty = ' || l_allocated_qty
2116 || ', l_total_qty = ' || l_total_qty
2117 || ', l_replen_so_creation_failed = ' || l_replen_so_creation_failed
2118 );
2119 END IF;
2120
2121 IF l_allocated_qty < l_total_qty
2122 THEN
2123 l_qty := (l_total_qty - l_allocated_qty);
2124
2125 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations
2126 ( p_api_version => 1.0
2127 , p_init_msg_list => null
2128 , x_return_status => l_return_status
2129 , x_msg_count => l_msg_count
2130 , x_msg_data => l_msg_data
2131 , p_subcontract_po_shipment_id => l_osa_shipment_id
2132 , p_component_id => l_osa_component_id
2133 , p_qty => l_qty
2134 , p_skip_po_replen_creation => NVL(l_replen_so_creation_failed, 'N')
2135 );
2136
2137 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2138 THEN
2139
2140 IF g_log_enabled
2141 AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2142 THEN
2143 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2144 , G_PKG_NAME
2145 , '>> ' || l_program || ': Allocation Failed'
2146 );
2147 END IF;
2148
2149 END IF; /* IF l_return_status <> FND_API.G_RET_STS_SUCCESS */
2150
2151 -- Reset the replen_so_creation_failed flag
2152 IF l_replen_so_creation_failed IS NOT NULL
2153 THEN
2154
2155 UPDATE jmf_shikyu_components
2156 SET replen_so_creation_failed = NULL
2157 , last_update_date = sysdate
2158 , last_updated_by = FND_GLOBAL.user_id
2159 , last_update_login = FND_GLOBAL.login_id
2160 WHERE subcontract_po_shipment_id = l_osa_shipment_id
2161 AND shikyu_component_id = l_osa_component_id;
2162
2163 END IF;
2164
2165 END IF; /* IF l_allocated_qty < l_total_qty */
2166 END IF; /* IF c_alloc%NOTFOUND*/
2167
2168 END LOOP;
2169 CLOSE c_alloc;
2170
2171 EXCEPTION
2172
2173 WHEN OTHERS THEN
2174 IF g_log_enabled AND
2175 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2176 THEN
2177 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2178 , G_PKG_NAME
2179 , G_PKG_NAME || l_program || ': ' || sqlerrm);
2180 END IF;
2181
2182 FND_MESSAGE.set_name('JMF', 'JMF_SHIKYU_REPL_LD_ERR');
2183 FND_MSG_PUB.add;
2184
2185 END Load_Replenishments;
2186
2187 --========================================================================
2188 -- PROCEDURE : Load_Shikyu_Components PRIVATE
2189 -- PARAMETERS: p_operating_unit The OU to execute the loading of
2190 -- Subcontracting Order Components in
2191 -- COMMENT : This procedure loads all the components of the subcontracting order
2192 -- based on the OSA shipments that are loaded in JMF_SUBCONTRACT_ORDERS
2193 --========================================================================
2194 PROCEDURE Load_Shikyu_Components
2195 ( p_operating_unit IN NUMBER
2196 )
2197 IS
2198 --=================
2199 -- LOCAL VARIABLES
2200 --=================
2201
2202 -- Bug 5678387: Removed the field group_id, since it is not
2203 -- necessary given that we do not insert into bom_explosion_temp
2204 -- anymore.
2205
2206 TYPE l_osa_rec IS RECORD
2207 ( subcontract_po_shipment_id NUMBER
2208 , osa_item_id NUMBER
2209 , oem_organization_id NUMBER
2210 , tp_organization_id NUMBER
2211 , need_by_date DATE
2212 , quantity NUMBER
2213 , unit_of_measure PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE
2214 , uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2215 , primary_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2216 , primary_uom_quantity NUMBER
2217 , project_id NUMBER
2218 , task_id NUMBER
2219 , status VARCHAR2(1)
2220 );
2221
2222 TYPE l_osa_TabTyp IS TABLE OF l_osa_rec;
2223
2224 l_program CONSTANT VARCHAR2(30) := 'Load_Shikyu_Components';
2225
2226 l_osa_tbl l_osa_TabTyp;
2227 l_errmsg VARCHAR2(2000);
2228 l_error_code NUMBER;
2229 l_curr_index NUMBER;
2230 l_return_status VARCHAR2(1);
2231 l_start_date DATE;
2232 l_order_header_id NUMBER;
2233 l_order_line_id NUMBER;
2234
2235 -- Bug 5364037: Added for Lot/Serial validation
2236 l_lot_serial_valid_flag VARCHAR2(1);
2237
2238 -- Bug 5632012: For supporting OSA Items with Phantom Assemblies
2239 -- as Components
2240 l_comp_tbl system.wip_component_tbl_t;
2241 l_count_seq NUMBER;
2242 l_routing_count NUMBER;
2243
2244 --=================
2245 -- CURSORS
2246 --=================
2247
2248 -- Bug 5500896: Added a where clause condition to filter out the
2249 -- subcontracting orders that do not belong to the operating unit
2250 -- for which the current run was executed in
2251
2252 -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
2253 -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
2254 -- which seems to be the case for PO Release Shipments
2255 -- Also, removed bom_explosion_temp_s.nextval from the select statement
2256 -- since we do not insert into bom_explosion_temp, and this would
2257 -- unnecessarily bump up the sequence number for the ID of
2258 -- bom_explosion_temp
2259
2260 CURSOR c_comp_cur IS
2261 SELECT
2262 jso.subcontract_po_shipment_id
2263 , jso.osa_item_id
2264 , jso.oem_organization_id
2265 , jso.tp_organization_id
2266 , jso.need_by_date
2267 , poll.quantity
2268 , NVL(poll.unit_meas_lookup_code, pla.unit_meas_lookup_code)
2269 , NULL
2270 , NULL
2271 , NULL
2272 , jso.project_id
2273 , jso.task_id
2274 , 'V'
2275 FROM
2276 jmf_subcontract_orders jso
2277 , po_line_locations_all poll
2278 , po_lines_all pla
2279 WHERE poll.line_location_id = jso.subcontract_po_shipment_id
2280 AND pla.po_line_id = poll.po_line_id
2281 AND jso.interlock_status = 'N'
2282 AND poll.org_id = p_operating_unit
2283 AND NOT EXISTS
2284 (SELECT shikyu_component_id
2285 FROM jmf_shikyu_components
2286 WHERE subcontract_po_shipment_id = jso.subcontract_po_shipment_id);
2287
2288 /*
2289 -- Once the BOM is exploded for the OSA item, the components
2290 -- are grouped so that when we load the components in WIP table
2291 -- to create the WIP job, we use the standard mechanism to create
2292 -- requirements by item/operations. Hence we check if there
2293 -- are multiple operation requirements for the same item.
2294 -- If the same item is defined in multiple operations, we
2295 -- log a message and move on to the next shipment.
2296 -- We do not summarize the item at the item/operation level.
2297
2298 CURSOR c_bom_cur(l_group_id NUMBER
2299 ,l_parent_id NUMBER) IS
2300 SELECT
2301 component_item_id shikyu_component_id
2302 , primary_uom_code primary_uom
2303 , sum(component_quantity) quantity
2304 , count(component_item_id) count_seq
2305 FROM
2306 bom_explosion_temp
2307 WHERE group_id = l_group_id
2308 AND assembly_item_id = l_parent_id
2309 AND l_start_date BETWEEN
2310 (effectivity_date) and NVL(disable_date,l_start_date+1)
2311 GROUP BY component_item_id,primary_uom_code;
2312 */
2313
2314 BEGIN
2315
2316 IF g_log_enabled THEN
2317 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2318 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2319 , G_PKG_NAME
2320 , '>> ' || l_program || ': Start'
2321 );
2322 END IF;
2323 END IF;
2324
2325 OPEN c_comp_cur;
2326 FETCH c_comp_cur
2327 BULK COLLECT INTO l_osa_tbl;
2328 CLOSE c_comp_cur;
2329
2330 IF g_log_enabled THEN
2331 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2332 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2333 , G_PKG_NAME
2334 , '>> ' || l_program || ': After opening c_comp_cur, l_osa_tbl.COUNT = ' || l_osa_tbl.COUNT
2335 );
2336 END IF;
2337 END IF;
2338
2339 IF l_osa_tbl.COUNT > 0 THEN
2340 FOR i IN l_osa_tbl.FIRST..l_osa_tbl.LAST
2341 LOOP
2342
2343 IF g_log_enabled AND
2344 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2345 THEN
2346 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2347 , G_PKG_NAME
2348 , '>> ' || l_program || ': Exploding BOM for subcontract_po_shipment_id = '
2349 || l_osa_tbl(i).subcontract_po_shipment_id
2350 || ', osa_item_id = '
2351 || l_osa_tbl(i).osa_item_id
2352 || ', oem_organization_id = '
2353 || l_osa_tbl(i).oem_organization_id
2354 || ', tp_organization_id = '
2355 || l_osa_tbl(i).tp_organization_id
2356 );
2357 END IF;
2358
2359 -- Get the count of routings defined for the OSA Item in the
2360 -- TP Organization
2361 SELECT count(bor.routing_sequence_id)
2362 INTO l_routing_count
2363 FROM bom_operational_routings bor
2364 WHERE bor.organization_id = l_osa_tbl(i).tp_organization_id
2365 AND bor.assembly_item_id = l_osa_tbl(i).osa_item_id;
2366
2367 IF g_log_enabled AND
2368 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2369 THEN
2370 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2371 , G_PKG_NAME
2372 , '>> ' || l_program || ': l_routing_count = '
2373 || l_routing_count
2374 );
2375 END IF;
2376
2377 -- Continue loading SHIKYU Components only if there are no routings
2378 -- defined for the OSA Item in the TP Organization
2379 IF l_routing_count <= 0
2380 THEN
2381
2382 BEGIN
2383
2384 SELECT uom_code
2385 INTO l_osa_tbl(i).uom_code
2386 FROM mtl_units_of_measure_vl
2387 WHERE unit_of_measure = l_osa_tbl(i).unit_of_measure;
2388
2389 l_osa_tbl(i).primary_uom_code := JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
2390 ( l_osa_tbl(i).osa_item_id
2391 , l_osa_tbl(i).tp_organization_id
2392 );
2393
2394 IF l_osa_tbl(i).uom_code <> l_osa_tbl(i).primary_uom_code
2395 THEN
2396
2397 l_osa_tbl(i).primary_uom_quantity
2398 := INV_CONVERT.inv_um_convert
2399 ( item_id => l_osa_tbl(i).osa_item_id
2400 , precision => 5
2401 , from_quantity => l_osa_tbl(i).quantity
2402 , from_unit => l_osa_tbl(i).uom_code
2403 , to_unit => l_osa_tbl(i).primary_uom_code
2404 , from_name => null
2405 , to_name => null
2406 );
2407
2408 ELSE
2409
2410 l_osa_tbl(i).primary_uom_quantity := l_osa_tbl(i).quantity;
2411
2412 END IF;
2413
2414 IF g_log_enabled AND
2415 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2416 THEN
2417 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2418 , G_PKG_NAME
2419 , '>> ' || l_program
2420 || ': Primary UOM = '
2421 || l_osa_tbl(i).primary_uom_quantity
2422 || ', Subcontracting PO UOM = '
2423 || l_osa_tbl(i).quantity
2424 );
2425 END IF;
2426
2427 EXCEPTION
2428 WHEN NO_DATA_FOUND THEN
2429 IF g_log_enabled AND
2430 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2431 THEN
2432 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2433 , G_PKG_NAME
2434 , '>> ' || l_program
2435 || ': NO DATA FOUND when trying to do UOM conversion for Subcontract PO Shipment with ID '
2436 || l_osa_tbl(i).subcontract_po_shipment_id
2437 );
2438 END IF;
2439 END;
2440
2441 -- Compute the start date for the WIP job
2442
2443 JMF_SHIKYU_WIP_PVT.Compute_Start_Date
2444 ( p_need_by_date => l_osa_tbl(i).need_by_date
2445 , p_item_id => l_osa_tbl(i).osa_item_id
2446 , p_oem_organization => l_osa_tbl(i).oem_organization_id
2447 , p_tp_organization => l_osa_tbl(i).tp_organization_id
2448 , p_quantity => l_osa_tbl(i).primary_uom_quantity
2449 , x_start_date => l_start_date
2450 );
2451
2452 IF g_log_enabled THEN
2453 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2454 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2455 , G_PKG_NAME
2456 , '>> ' || l_program || ': WIP Job Start Date = ' || l_start_date
2457 );
2458 END IF;
2459 END IF;
2460
2461 -- Bug 5632012:
2462 -- For the current OSA item being fetched, call the WIP explodeRequirements
2463 -- API (instead of the BOM Explosion API directly) in order to explode the
2464 -- bom to get all the components that are part of the OSA item. This WIP API
2465 -- would consider phantom components and explode multiple levels.
2466
2467 wip_bflProc_priv.explodeRequirements( p_itemID => l_osa_tbl(i).osa_item_id
2468 , p_orgID => l_osa_tbl(i).tp_organization_id
2469 , p_qty => l_osa_tbl(i).primary_uom_quantity
2470 , p_altBomDesig => NULL
2471 , p_altOption => 2
2472 , p_bomRevDate => l_start_date
2473 , p_txnDate => l_start_date
2474 , p_implFlag => 2
2475 , p_projectID => l_osa_tbl(i).project_id
2476 , p_taskID => l_osa_tbl(i).task_id
2477 , p_initMsgList => fnd_api.g_false
2478 , p_endDebug => fnd_api.g_true
2479 , x_compTbl => l_comp_tbl
2480 , x_returnStatus => l_return_status);
2481
2482 IF g_log_enabled THEN
2483 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2484 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2485 , G_PKG_NAME
2486 , '>> ' || l_program || ': After calling wip_bflProc_priv.explodeRequirements: '
2487 || 'Return Status = ' || l_return_status
2488 || ', Comp Tbl Size = ' || l_comp_tbl.COUNT
2489 );
2490 END IF;
2491 END IF;
2492
2493 -- if WIP Explode Requirements erred out
2494 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2495 THEN
2496 -- Error in bom explosion. Mark the shipment line status as error.
2497 -- Skip and move on to the next shipment line. In the next run,
2498 -- the shipment line that is marked as error, will be processed
2499 -- starting from loading the components.
2500
2501 UPDATE JMF_SUBCONTRACT_ORDERS
2502 SET interlock_status = 'E'
2503 , last_update_date = sysdate
2504 , last_updated_by = FND_GLOBAL.user_id
2505 , last_update_login = FND_GLOBAL.login_id
2506 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2507
2508 FND_MESSAGE.set_name('JMF', 'JMF_SHK_INVALID_BOM');
2509 FND_MSG_PUB.add;
2510
2511 IF g_log_enabled THEN
2512 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2513 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2514 , G_PKG_NAME
2515 , '>> ' || l_program
2516 || ': WIP Explode Requirements API failed for organization_id = '
2517 || l_osa_tbl(i).tp_organization_id
2518 || '/ inventory_item_id = ' || l_osa_tbl(i).osa_item_id
2519 );
2520 END IF;
2521 END IF;
2522
2523 l_return_status := NULL;
2524
2525 -- if WIP Explode Requirements completed successfully
2526 ELSE
2527
2528 l_return_status := NULL;
2529
2530 l_curr_index := l_comp_tbl.FIRST;
2531
2532 LOOP
2533 IF g_log_enabled AND
2534 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2535 THEN
2536 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2537 , G_PKG_NAME
2538 , '>> ' || l_program || ': Loading shikyu_component_id = '
2539 || l_comp_tbl(l_curr_index).inventory_item_id
2540 || ', item_name = '
2541 || l_comp_tbl(l_curr_index).item_name
2542 || ', index of BOM Explosion LOOP = '
2543 || l_curr_index
2544 );
2545 END IF;
2546
2547 /* if Component is a Phantom Assembly */
2548 IF l_comp_tbl(l_curr_index).wip_supply_type = 6
2549 THEN
2550
2551 IF g_log_enabled AND
2552 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2553 THEN
2554 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2555 , G_PKG_NAME
2556 , '>> ' || l_program || ': Shikyu Component '
2557 || l_comp_tbl(l_curr_index).item_name
2558 || ' is of Supply Type Phantom and will not be loaded into JMF_SHIKYU_COMPONENTS'
2559 );
2560 END IF;
2561
2562 ELSE /* if Component is not a Phantom Assembly */
2563
2564 -- Checking to make sure that the same component would not be inserted into
2565 -- the jmf_shikyu_components table multiple times. The same component can be
2566 -- an immediate child of the assembly item, or the child of a phantom child
2567 -- (at any level) of the assembly.
2568
2569 IF g_log_enabled AND
2570 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2571 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2572 , G_PKG_NAME
2573 , '>> ' || l_program
2574 || ': Quantity for Shikyu Component '
2575 || l_comp_tbl(l_curr_index).item_name
2576 || ' = '
2577 || l_comp_tbl(l_curr_index).primary_quantity
2578 );
2579 END IF;
2580
2581 l_count_seq := 0;
2582
2583 SELECT COUNT(*)
2584 INTO l_count_seq
2585 FROM jmf_shikyu_components
2586 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
2587 AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
2588
2589 IF g_log_enabled AND
2590 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2591 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2592 , G_PKG_NAME
2593 , '>> ' || l_program
2594 || ': l_count_seq = '
2595 || l_count_seq
2596 || ', Shikyu Component '
2597 || l_comp_tbl(l_curr_index).item_name
2598 || ' already exists in the JMF_SHIKYU_COMPONENTS table'
2599 );
2600 END IF;
2601
2602 IF l_count_seq > 0
2603 THEN
2604
2605 IF g_log_enabled AND
2606 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2607 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2608 , G_PKG_NAME
2609 , '>> ' || l_program
2610 || ': BOM has same item defined in multiple operation sequences for subcontract_po_shipment_id '
2611 || l_osa_tbl(i).subcontract_po_shipment_id
2612 );
2613 END IF;
2614
2615 END IF; /* l_count_seq > 0 */
2616
2617 -- Verify the attributes of the Shikyu components that are to be loaded.
2618
2619 IF g_log_enabled THEN
2620 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2621 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2622 , G_PKG_NAME
2623 , '>> ' || l_program
2624 || ': Call Verify_Shikyu_Attributes for shikyu_component_id = '
2625 || l_comp_tbl(l_curr_index).inventory_item_id);
2626 END IF;
2627 END IF;
2628
2629 Verify_Shikyu_Attributes
2630 ( p_osa_item_id => l_osa_tbl(i).osa_item_id
2631 , p_component_item_id => l_comp_tbl(l_curr_index).inventory_item_id
2632 , p_oem_organization_id => l_osa_tbl(i).oem_organization_id
2633 , p_tp_organization_id => l_osa_tbl(i).tp_organization_id
2634 , x_return_status => l_return_status
2635 );
2636
2637 IF g_log_enabled AND
2638 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2639 THEN
2640 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2641 , G_PKG_NAME
2642 , '>> ' || l_program || ': Return status from Verify_Shikyu_Attributes = '
2643 || l_return_status
2644 || ', for shikyu_component_id = '
2645 || l_comp_tbl(l_curr_index).inventory_item_id
2646 || ', subcontract_po_shipment_id = '
2647 || l_osa_tbl(i).subcontract_po_shipment_id);
2648 END IF;
2649
2650 -- Bug 5364037: To validate that the OSA item is not Lot and/or
2651 -- Serial controlled in the TP Organization
2652
2653 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2654 THEN
2655
2656 Validate_Lot_Serial_Control
2657 ( p_item_id => l_comp_tbl(l_curr_index).inventory_item_id
2658 , p_organization_id => l_osa_tbl(i).tp_organization_id
2659 , x_valid_flag => l_lot_serial_valid_flag
2660 );
2661
2662 IF l_lot_serial_valid_flag = 'N'
2663 THEN
2664 l_return_status := FND_API.G_RET_STS_ERROR;
2665 END IF;
2666
2667 IF g_log_enabled AND
2668 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2669 THEN
2670 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2671 , G_PKG_NAME
2672 , '>> ' || G_PKG_NAME || '.' || l_program
2673 || ': l_return_status after Lot/Serial validation = '
2674 || l_return_status
2675 || ', for shikyu_component_id = '
2676 || l_comp_tbl(l_curr_index).inventory_item_id
2677 || ', subcontract_po_shipment_id = '
2678 || l_osa_tbl(i).subcontract_po_shipment_id
2679 );
2680 END IF;
2681
2682 END IF;
2683
2684 IF g_log_enabled THEN
2685 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2686 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2687 , G_PKG_NAME
2688 , '>> ' || l_program
2689 || ': primary_uom_code for shikyu_component_id '
2690 || l_comp_tbl(l_curr_index).inventory_item_id
2691 || ' = ' || l_comp_tbl(l_curr_index).primary_uom_code
2692 );
2693 END IF;
2694 END IF;
2695
2696 /* if component validation passed */
2697 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
2698 THEN
2699 -- Load the Components table.
2700
2701 IF NVL(l_count_seq, 0) = 0
2702 THEN
2703 INSERT INTO JMF_SHIKYU_COMPONENTS
2704 ( subcontract_po_shipment_id
2705 , shikyu_component_id
2706 , oem_organization_id
2707 , primary_uom
2708 , quantity
2709 , last_update_date
2710 , last_updated_by
2711 , creation_date
2712 , created_by
2713 , last_update_login
2714 , request_id
2715 , program_application_id
2716 , program_id
2717 , program_update_date
2718 )
2719 VALUES
2720 ( l_osa_tbl(i).subcontract_po_shipment_id
2721 , l_comp_tbl(l_curr_index).inventory_item_id
2722 , l_osa_tbl(i).oem_organization_id
2723 , l_comp_tbl(l_curr_index).primary_uom_code
2724 , l_comp_tbl(l_curr_index).primary_quantity
2725 , sysdate
2726 , FND_GLOBAL.user_id
2727 , sysdate
2728 , FND_GLOBAL.user_id
2729 , FND_GLOBAL.login_id
2730 , null
2731 , null
2732 , null
2733 , null
2734 );
2735
2736 IF g_log_enabled AND
2737 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2738 THEN
2739 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2740 , G_PKG_NAME
2741 , '>> ' || l_program
2742 || ': After insert into JMF_SHIKYU_COMPONENTS');
2743 END IF;
2744
2745 ELSE
2746
2747 IF g_log_enabled AND
2748 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2749 THEN
2750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2751 , G_PKG_NAME
2752 , '>> ' || l_program
2753 || ': JMF_SHIKYU_COMPONENTS record with ID = '
2754 || l_comp_tbl(l_curr_index).inventory_item_id
2755 || ' already inserted');
2756 END IF;
2757
2758 UPDATE JMF_SHIKYU_COMPONENTS
2759 SET quantity = quantity + l_comp_tbl(l_curr_index).primary_quantity
2760 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id
2761 AND shikyu_component_id = l_comp_tbl(l_curr_index).inventory_item_id;
2762
2763 END IF; /* IF NVL(l_count_seq, 0) = 0 */
2764
2765 -- Invoke Process Replenishment SO with action of Quote.
2766 -- This will just populate the UOM code and price in
2767 -- the jmf_shikyu_components table without creating the
2768 -- order line. This information is required when creating
2769 -- the replenishment SO for the component.
2770
2771 JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
2772 ( p_action => 'Q' --Quote
2773 , p_subcontract_po_shipment_id =>
2774 l_osa_tbl(i).subcontract_po_shipment_id
2775 , p_quantity => l_comp_tbl(l_curr_index).primary_quantity
2776 , p_item_id => l_comp_tbl(l_curr_index).inventory_item_id
2777 , p_replen_po_shipment_id => null
2778 , p_oem_organization_id => l_osa_tbl(i).oem_organization_id
2779 , p_tp_organization_id => l_osa_tbl(i).tp_organization_id
2780 , x_order_line_id => l_order_line_id
2781 , x_return_status => l_return_status
2782 );
2783
2784 IF g_log_enabled AND
2785 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2786 THEN
2787 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2788 , G_PKG_NAME
2789 , '>> ' || l_program
2790 || ': Process_Replenishment_SO returns '
2791 || l_return_status
2792 || ', l_order_line_id = ' || l_order_line_id);
2793 END IF;
2794
2795 -- If the return status from Process_Replenishment_SO (doing Price Quote)
2796 -- is not success, move on to the next shipment line
2797 /*
2798 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2799 THEN
2800
2801 -- Update the interlock_status of the current Subcontracting Order to 'E',
2802 -- signifying that some errors occurred when loading the components
2803 -- (specifically, shikyu_component_price could not be obtained)
2804 UPDATE JMF_SUBCONTRACT_ORDERS
2805 SET interlock_status = 'E'
2806 , last_update_date = sysdate
2807 , last_updated_by = FND_GLOBAL.user_id
2808 , last_update_login = FND_GLOBAL.login_id
2809 WHERE subcontract_po_shipment_id =
2810 l_osa_tbl(i).subcontract_po_shipment_id;
2811
2812 END IF;
2813 */
2814
2815 ELSE /* if component validation failed */
2816
2817 -- Error in validating attributes. Mark the shipment line status as Invalid.
2818 -- Skip and move on to the next shipment line.
2819
2820 UPDATE JMF_SUBCONTRACT_ORDERS
2821 SET interlock_status = 'E'
2822 , last_update_date = sysdate
2823 , last_updated_by = FND_GLOBAL.user_id
2824 , last_update_login = FND_GLOBAL.login_id
2825 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2826
2827 DELETE FROM jmf_shikyu_components
2828 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2829
2830 IF g_log_enabled AND
2831 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2832 THEN
2833 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2834 , G_PKG_NAME
2835 , '>> ' || l_program || ': Component Validation failed for oem_organization_id = '
2836 || l_osa_tbl(i).oem_organization_id
2837 || ', tp_organization_id = '
2838 || l_osa_tbl(i).tp_organization_id
2839 || ', osa_item_id = '
2840 || l_osa_tbl(i).osa_item_id
2841 || ', subcontract_po_shipment_id = '
2842 || l_osa_tbl(i).subcontract_po_shipment_id);
2843 END IF;
2844
2845 -- Skip current Subcontracting Order (since component validation failed) and
2846 -- move on to the next Order
2847 EXIT;
2848
2849 END IF; /* IF (l_return_status = FND_API.G_RET_STS_SUCCESS) */
2850
2851 END IF; /* IF l_comp_tbl(l_curr_index).wip_supply_type = 6 */
2852
2853 l_curr_index := l_comp_tbl.next(l_curr_index);
2854 EXIT WHEN l_curr_index IS NULL;
2855
2856 END LOOP; /* End of FOR LOOP iterating l_comp_tbl */
2857
2858 END IF; /* IF l_return_status <> FND_API.G_RET_STS_SUCCESS*/
2859
2860 ELSE /* IF l_routing_count <= 0 */
2861
2862 IF g_log_enabled AND
2863 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2864 THEN
2865 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2866 , G_PKG_NAME
2867 , '>> ' || l_program || ': Routing exists for oem_organization_id = '
2868 || l_osa_tbl(i).oem_organization_id
2869 || ', tp_organization_id = '
2870 || l_osa_tbl(i).tp_organization_id
2871 || ', osa_item_id = '
2872 || l_osa_tbl(i).osa_item_id
2873 || ', subcontract_po_shipment_id = '
2874 || l_osa_tbl(i).subcontract_po_shipment_id);
2875
2876 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2877 , G_PKG_NAME
2878 , '>> ' || l_program
2879 || ': Marking the JMF_SUBCONTRACT_ORDERS record with interlock_status ''E''');
2880 END IF;
2881
2882 -- Routings defined for the OSA Item in the TP Organization. Mark the interlock_status
2883 -- of the Subcontract Shipment Line status as 'E' (Error).
2884 -- Skip and move on to the next Subcontract Shipment Line.
2885
2886 UPDATE JMF_SUBCONTRACT_ORDERS
2887 SET interlock_status = 'E'
2888 , last_update_date = sysdate
2889 , last_updated_by = FND_GLOBAL.user_id
2890 , last_update_login = FND_GLOBAL.login_id
2891 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
2892
2893 END IF; /* IF l_routing_count <= 0 */
2894
2895 END LOOP; /* End of FOR LOOP iterating l_osa_tbl */
2896
2897 END IF; /* IF l_osa_tbl.COUNT > 0 */
2898
2899 IF g_log_enabled AND
2900 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2901 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2902 , G_PKG_NAME
2903 , '>> ' || l_program || ': Exit'
2904 );
2905 END IF;
2906
2907 EXCEPTION
2908
2909 WHEN OTHERS THEN
2910 IF g_log_enabled AND
2911 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2912 THEN
2913 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2914 , G_PKG_NAME
2915 , G_PKG_NAME || l_program || ': ' || sqlerrm);
2916 END IF;
2917
2918 FND_MESSAGE.set_name('JMF', 'JMF_SHIKYU_COMP_LD_ERR');
2919 FND_MSG_PUB.add;
2920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2921
2922 END Load_Shikyu_Components;
2923
2924 --========================================================================
2925 -- PROCEDURE : Stamp_Null_Shikyu_Comp_Prices PRIVATE
2926 -- PARAMETERS: None
2927 -- COMMENT : This procedure stamps the SHIKYU Component records with
2928 -- NULL prices because of errors tahat occurred in a previous
2929 -- run
2930 --========================================================================
2931 PROCEDURE Stamp_Null_Shikyu_Comp_Prices
2932 ( p_operating_unit IN NUMBER
2933 )
2934 IS
2935 --=================
2936 -- LOCAL VARIABLES
2937 --=================
2938
2939 l_oem_organization_id
2940 JMF_SUBCONTRACT_ORDERS.oem_organization_id%TYPE;
2941 l_tp_organization_id
2942 JMF_SUBCONTRACT_ORDERS.tp_organization_id%TYPE;
2943 l_subcontract_po_shipment_id
2944 JMF_SHIKYU_COMPONENTS.subcontract_po_shipment_id%TYPE;
2945 l_shikyu_component_id
2946 JMF_SHIKYU_COMPONENTS.shikyu_component_id%TYPE;
2947 l_quantity
2948 JMF_SHIKYU_COMPONENTS.quantity%TYPE;
2949 l_order_line_id
2950 OE_ORDER_LINES_ALL.line_id%TYPE;
2951
2952 l_program CONSTANT VARCHAR2(30) := 'Stamp_Null_Shikyu_Comp_Prices';
2953 l_return_status VARCHAR2(1);
2954
2955 --=================
2956 -- CURSORS
2957 --=================
2958
2959 CURSOR c_comp_cur IS
2960 SELECT
2961 jsc.subcontract_po_shipment_id
2962 , jsc.shikyu_component_id
2963 , jsc.quantity
2964 , jso.oem_organization_id
2965 , jso.tp_organization_id
2966 FROM
2967 jmf_subcontract_orders jso,
2968 jmf_shikyu_components jsc,
2969 po_line_locations_all plla
2970 WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
2971 AND shikyu_component_price IS NULL
2972 AND plla.line_location_id = jso.subcontract_po_shipment_id
2973 AND plla.org_id = p_operating_unit;
2974
2975 BEGIN
2976
2977 IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2978 THEN
2979 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2980 , G_PKG_NAME
2981 , '>> ' || l_program || ': Start'
2982 );
2983 END IF;
2984
2985 IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2986 THEN
2987 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2988 , G_PKG_NAME
2989 , '>> ' || l_program || ': p_operating_unit = ' || p_operating_unit
2990 );
2991 END IF;
2992
2993 OPEN c_comp_cur;
2994 LOOP
2995
2996 FETCH c_comp_cur
2997 INTO l_subcontract_po_shipment_id
2998 , l_shikyu_component_id
2999 , l_quantity
3000 , l_oem_organization_id
3001 , l_tp_organization_id;
3002
3003 IF c_comp_cur%NOTFOUND
3004 THEN
3005 EXIT;
3006 END IF;
3007
3008 -- Invoke Process Replenishment SO with action of Quote.
3009 -- This will just populate the UOM code and price in
3010 -- the jmf_shikyu_components table without creating the
3011 -- order line. This information is required when creating
3012 -- the replenishment SO for the component.
3013
3014 IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3015 THEN
3016 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3017 , G_PKG_NAME
3018 , '>> ' || l_program
3019 || ': Calling Process_Replenishment_SO for '
3020 || 'subcontract_po_shipment_id = ' || l_subcontract_po_shipment_id
3021 || ', shikyu_component_id = ' || l_shikyu_component_id);
3022 END IF;
3023
3024 JMF_SHIKYU_ONT_PVT.Process_Replenishment_SO
3025 ( p_action => 'Q' --Quote
3026 , p_subcontract_po_shipment_id
3027 => l_subcontract_po_shipment_id
3028 , p_quantity => l_quantity
3029 , p_item_id => l_shikyu_component_id
3030 , p_replen_po_shipment_id => null
3031 , p_oem_organization_id => l_oem_organization_id
3032 , p_tp_organization_id => l_tp_organization_id
3033 , x_order_line_id => l_order_line_id
3034 , x_return_status => l_return_status
3035 );
3036
3037 IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3038 THEN
3039 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3040 , G_PKG_NAME
3041 , '>> ' || l_program
3042 || ': Process_Replenishment_SO returns '
3043 || l_return_status
3044 || ', l_order_line_id = ' || l_order_line_id);
3045 END IF;
3046
3047 END LOOP;
3048
3049 CLOSE c_comp_cur;
3050
3051
3052 IF g_log_enabled AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
3053 THEN
3054 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3055 , G_PKG_NAME
3056 , '>> ' || l_program || ': Exit'
3057 );
3058 END IF;
3059
3060 EXCEPTION
3061
3062 WHEN OTHERS THEN
3063 IF g_log_enabled AND
3064 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3065 THEN
3066 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3067 , G_PKG_NAME
3068 , G_PKG_NAME || l_program || ': ' || sqlerrm);
3069 END IF;
3070
3071 FND_MESSAGE.set_name('JMF', 'JMF_SHK_STAMP_COMP_PRICE_ERR');
3072 FND_MSG_PUB.add;
3073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3074
3075 END Stamp_Null_Shikyu_Comp_Prices;
3076
3077 --========================================================================
3078 -- FUNCTION : Generate_Batch_Id PRIVATE
3079 -- PARAMETERS: None
3080 -- RETURNS : NUMBER
3081 -- COMMENT : This function returns the next batch id to be assigned to
3082 -- the records in JMF_SUBCONTRACT_ORDERS
3083 --=========================================================================
3084 FUNCTION generate_batch_id
3085 RETURN NUMBER
3086 IS
3087 l_batch_id NUMBER;
3088 l_program CONSTANT VARCHAR2(30) := 'Generate_Batch_Id';
3089 BEGIN
3090
3091 IF g_log_enabled THEN
3092 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3093 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3094 , G_PKG_NAME
3095 , '>> ' || l_program || ': Start'
3096 );
3097 END IF;
3098 END IF;
3099 -- Generate sequence that will become the new batch id
3100
3101 SELECT jmf_shikyu_batch_s.NEXTVAL
3102 INTO l_batch_id
3103 FROM dual;
3104
3105 IF g_log_enabled THEN
3106 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3107 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3108 , G_PKG_NAME
3109 , '>> ' || l_program || ': Exit'
3110 );
3111 END IF;
3112 END IF;
3113
3114 RETURN l_batch_id;
3115
3116 END generate_batch_id;
3117
3118 --========================================================================
3119 -- PROCEDURE : Process_Subcontract_Orders PRIVATE
3120 -- PARAMETERS: p_batch_id Batch ID
3121 -- COMMENT : This procedure is called by the worker to process all the
3122 -- records in the batch that is loaded in JMF_SUBCONTRACT_ORDERS
3123 -- table.
3124 --========================================================================
3125 PROCEDURE Process_Subcontract_Orders
3126 ( p_batch_id IN NUMBER
3127 )
3128 IS
3129 l_program CONSTANT VARCHAR2(30) := 'Process_Subcontract_Orders';
3130 l_osa_tbl g_OsaTabTyp;
3131 l_comp_tbl g_Comp_TabTyp;
3132 l_quantity NUMBER;
3133 l_return_status VARCHAR2(1);
3134 l_wip_entity_id NUMBER;
3135 l_shipment_id NUMBER;
3136 l_po_uom VARCHAR2(25);
3137 l_primary_uom VARCHAR2(25);
3138 l_po_qty NUMBER;
3139 l_comp_qty NUMBER;
3140 l_msg_data VARCHAR2(2000);
3141 l_msg_count NUMBER;
3142 l_osa_item MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
3143 l_tp_organization MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
3144 l_message VARCHAR(2000);
3145 l_status_flag BOOLEAN;
3146
3147 CURSOR c_osa_rec IS
3148 SELECT *
3149 FROM jmf_subcontract_orders
3150 WHERE batch_id = p_batch_id
3151 AND interlock_status in ('N','U');
3152
3153 CURSOR c_comp_rec IS
3154 SELECT *
3155 FROM jmf_shikyu_components
3156 WHERE subcontract_po_shipment_id = l_shipment_id;
3157
3158 BEGIN
3159
3160 IF g_log_enabled THEN
3161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3162 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3163 , G_PKG_NAME
3164 , '>> ' || l_program || ': Start >>'
3165 );
3166 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3167 , '>> ' || l_program || ': p_batch_id = '
3168 , p_batch_id
3169 );
3170 END IF;
3171 END IF;
3172
3173 OPEN c_osa_rec;
3174 FETCH c_osa_rec
3175 BULK COLLECT INTO l_osa_tbl;
3176 CLOSE c_osa_rec;
3177
3178 FOR i IN l_osa_tbl.FIRST .. l_osa_tbl.LAST
3179 LOOP
3180
3181 IF g_log_enabled AND
3182 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3183 THEN
3184 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3185 , G_PKG_NAME
3186 , '>> ' || l_program || ': Processing Subcontracting Order with '
3187 || 'subcontract_po_shipment_id = '
3188 || l_osa_tbl(i).subcontract_po_shipment_id
3189 || ', osa_item_id = '
3190 || l_osa_tbl(i).osa_item_id
3191 || ', oem_organization_id = '
3192 || l_osa_tbl(i).oem_organization_id
3193 || ', tp_organization_id = '
3194 || l_osa_tbl(i).tp_organization_id
3195 );
3196 END IF;
3197
3198 -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
3199 -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
3200 -- which seems to be the case for PO Release Shipments
3201
3202 SELECT plla.quantity
3203 , NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code)
3204 INTO l_po_qty
3205 , l_po_uom
3206 FROM po_line_locations_all plla
3207 , po_lines_all pla
3208 WHERE plla.line_location_id = l_osa_tbl(i).subcontract_po_shipment_id
3209 AND plla.po_line_id = pla.po_line_id;
3210
3211 l_primary_uom:= JMF_SHIKYU_UTIL.Get_Primary_UOM
3212 (l_osa_tbl(i).osa_item_id
3213 ,l_osa_tbl(i).tp_organization_id);
3214
3215 -- Check if Purchasing UOM is different than the primary UOM of the item
3216 -- IF it is convert to primary UOM.
3217
3218 IF l_po_uom <> l_primary_uom
3219 THEN
3220 l_quantity := INV_CONVERT.inv_um_convert
3221 ( item_id => l_osa_tbl(i).osa_item_id
3222 , precision => 5
3223 , from_quantity => l_po_qty
3224 , from_unit => null
3225 , to_unit => null
3226 , from_name => l_po_uom
3227 , to_name => l_primary_uom
3228 );
3229 ELSE
3230 l_quantity := l_po_qty;
3231 END IF;
3232
3233 -- Create a WIP job at the TP org for the OSA item
3234
3235 JMF_SHIKYU_WIP_PVT.Process_WIP_Job
3236 ( p_action => 'C'
3237 , p_subcontract_po_shipment_id => l_osa_tbl(i).subcontract_po_shipment_id
3238 , p_need_by_date => l_osa_tbl(i).need_by_date
3239 , p_quantity => l_quantity
3240 , x_return_status => l_return_status
3241 );
3242
3243 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3244 THEN
3245 -- If WIP job is created, update with the WIP job id
3246
3247 SELECT wip_entity_id
3248 INTO l_wip_entity_id
3249 FROM JMF_SUBCONTRACT_ORDERS
3250 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
3251 -- Based on the above action, update JSO with interlock_status
3252 UPDATE jmf_subcontract_orders
3253 SET interlock_status ='P'
3254 , last_update_date = sysdate
3255 , last_updated_by = FND_GLOBAL.user_id
3256 , last_update_login = FND_GLOBAL.login_id
3257 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
3258
3259 l_osa_tbl(i).wip_entity_id := l_wip_entity_id;
3260
3261 l_shipment_id := l_osa_tbl(i).subcontract_po_shipment_id;
3262
3263 OPEN c_comp_rec;
3264 FETCH c_comp_rec
3265 BULK COLLECT INTO l_comp_tbl;
3266 CLOSE c_comp_rec;
3267
3268
3269 FOR k IN l_comp_tbl.FIRST .. l_comp_tbl.LAST
3270 LOOP
3271
3272 l_comp_qty := JMF_SHIKYU_WIP_PVT.get_component_quantity
3273 ( p_item_id => l_comp_tbl(k).shikyu_component_id
3274 , p_organization_id => l_osa_tbl(i).tp_organization_id
3275 , p_subcontract_po_shipment_id =>
3276 l_osa_tbl(i).subcontract_po_shipment_id
3277 );
3278 -- Call Process Allocation to allocate
3279 -- based in sync or pre-stock process allocation accordingly.
3280
3281 JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations
3282 ( p_api_version => 1.0
3283 , p_init_msg_list => null
3284 , x_return_status => l_return_status
3285 , x_msg_count => l_msg_count
3286 , x_msg_data => l_msg_data
3287 , p_subcontract_po_shipment_id => l_osa_tbl(i).subcontract_po_shipment_id
3288 , p_component_id => l_comp_tbl(k).shikyu_component_id
3289 , p_qty => l_comp_qty
3290 , p_skip_po_replen_creation => 'N'
3291 );
3292 END LOOP;
3293
3294 ELSE
3295 UPDATE jmf_subcontract_orders
3296 SET interlock_status ='U'
3297 , batch_id = -1
3298 , last_update_date = sysdate
3299 , last_updated_by = FND_GLOBAL.user_id
3300 , last_update_login = FND_GLOBAL.login_id
3301 WHERE subcontract_po_shipment_id = l_osa_tbl(i).subcontract_po_shipment_id;
3302
3303 IF g_log_enabled THEN
3304 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3305 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3306 , G_PKG_NAME
3307 , '>> ' || l_program || ': Process_Wip_Job returned E'
3308 );
3309 END IF;
3310 END IF;
3311
3312 /* Bug 7000413 - Start */
3313 /* Log the error in the Concurrent Request log */
3314 BEGIN
3315
3316 SELECT segment1 INTO l_osa_item
3317 FROM mtl_system_items_b
3318 WHERE inventory_item_id = l_osa_tbl(i).osa_item_id
3319 AND organization_id = l_osa_tbl(i).tp_organization_id ;
3320
3321 SELECT organization_code INTO l_tp_organization
3322 FROM mtl_parameters
3323 WHERE organization_id =l_osa_tbl(i).tp_organization_id ;
3324
3325 fnd_message.set_name('JMF','JMF_SHK_WIP_JOB_ERROR');
3326 fnd_message.set_token('OSA', l_osa_item);
3327 fnd_message.set_token('MP', l_tp_organization);
3328 l_message := fnd_message.GET();
3329 fnd_file.put_line(fnd_file.LOG, l_message);
3330 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
3331 EXCEPTION
3332 WHEN OTHERS THEN
3333 NULL; -- Return null if there is an error in fetching the message
3334 END;
3335 /* Bug 7000413 - End */
3336
3337 END IF;
3338
3339 END LOOP;
3340
3341
3342 EXCEPTION
3343
3344 WHEN OTHERS THEN
3345
3346 IF g_log_enabled AND
3347 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3348 THEN
3349 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3350 , G_PKG_NAME
3351 , G_PKG_NAME || l_program || ': ' || sqlerrm);
3352 END IF;
3353
3354 FND_MESSAGE.set_name('JMF', 'JMF_SHK_INTERLK_PROCESS_ERR');
3355 FND_MSG_PUB.add;
3356
3357 END Process_Subcontract_Orders;
3358
3359 --========================================================================
3360 -- PROCEDURE : Allocate_batch PRIVATE
3361 -- PARAMETERS: p_batch_size Batch size to be processed
3362 -- p_max_workers Maximum no of workers allowed
3363 -- p_operating_unit Operating unit passed in
3364 -- from the concurrent request
3365 -- p_from_organization From OEM Organization
3366 -- p_to_organization To OEM Organization
3367 -- COMMENT : This procedure allocates batches to a set of records
3368 -- that are grouped for processing.
3369 --========================================================================
3370 PROCEDURE Allocate_batch
3371 ( p_batch_size IN NUMBER
3372 , p_max_workers IN NUMBER
3373 , p_operating_unit IN NUMBER
3374 , p_from_organization IN NUMBER
3375 , p_to_organization IN NUMBER
3376 )
3377 IS
3378 --=================
3379 -- LOCAL VARIABLES
3380 --=================
3381
3382 TYPE t_osa_TabTyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
3383 l_osa_tbl t_osa_TabTyp;
3384 l_request_tbl JMF_SHIKYU_UTIL.g_request_tbl_type;
3385 l_batch_size NUMBER;
3386 l_curr_index NUMBER := 0;
3387 l_count NUMBER := 0;
3388 l_batch_id NUMBER;
3389 l_program CONSTANT VARCHAR2(30) := 'Allocate_batch';
3390 l_request_id NUMBER;
3391 l_return_status VARCHAR2(1);
3392
3393 -- Bug 5500896: Modified the cursor query to select only the
3394 -- Subcontracting Orders that belongs to the OU in which the
3395 -- current concurrent request operates, and for which
3396 -- the OEM Organization is in the range passed in from
3397 -- the concurrent request. Also, any cancelled POs would
3398 -- be filtered out.
3399
3400 /*
3401 CURSOR c_proc_batch IS
3402 SELECT jso.subcontract_po_shipment_id
3403 FROM jmf_subcontract_orders jso
3404 WHERE jso.interlock_status IN ('N', 'U');
3405 */
3406
3407 CURSOR c_proc_batch IS
3408 SELECT jso.subcontract_po_shipment_id
3409 FROM jmf_subcontract_orders jso
3410 , po_line_locations_all plla
3411 , po_lines_all pla
3412 , po_headers_all pha
3413 WHERE jso.interlock_status IN ('N', 'U')
3414 AND plla.line_location_id = jso.subcontract_po_shipment_id
3415 AND plla.po_line_id = pla.po_line_id
3416 AND plla.po_header_id = pha.po_header_id
3417 AND plla.org_id = p_operating_unit
3418 AND nvl(pha.cancel_flag, 'N') = 'N'
3419 AND nvl(pla.cancel_flag, 'N') = 'N'
3420 AND nvl(plla.cancel_flag, 'N') = 'N'
3421 AND jso.oem_organization_id
3422 BETWEEN
3423 NVL(p_from_organization, jso.oem_organization_id)
3424 AND
3425 NVL(p_to_organization, jso.oem_organization_id)
3426 ORDER BY jso.subcontract_po_shipment_id;
3427
3428 BEGIN
3429
3430 IF g_log_enabled THEN
3431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3432 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3433 , G_PKG_NAME
3434 , '>> ' || l_program || ': Start'
3435 );
3436 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3437 , G_PKG_NAME
3438 , '>> ' || l_program
3439 || ': p_batch_size = ' || p_batch_size
3440 || ', p_max_worker = ' || p_max_workers
3441 );
3442 END IF;
3443 END IF;
3444
3445 OPEN c_proc_batch;
3446 FETCH c_proc_batch
3447 BULK COLLECT INTO l_osa_tbl;
3448 CLOSE c_proc_batch;
3449
3450 IF g_log_enabled AND
3451 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3452 THEN
3453 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3454 , G_PKG_NAME
3455 , '>> ' || l_program
3456 || ': l_osa_tbl.COUNT = '|| l_osa_tbl.COUNT
3457 );
3458 END IF;
3459
3460 IF l_osa_tbl.COUNT > 0
3461 THEN
3462
3463 l_count := l_osa_tbl.LAST;
3464 l_curr_index := l_osa_tbl.FIRST;
3465
3466
3467 IF (p_batch_size IS NULL) OR (l_count < p_batch_size)
3468 THEN
3469 l_batch_size := l_osa_tbl.LAST;
3470 ELSE
3471 l_batch_size := p_batch_size ;
3472 END IF;
3473
3474 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3475 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3476 , G_PKG_NAME
3477 , '>> ' || l_program
3478 || ': Actual batch size = ' || l_batch_size
3479 );
3480 END IF;
3481
3482 LOOP
3483 l_batch_id := generate_batch_id();
3484
3485 IF g_log_enabled THEN
3486 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3487 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3488 , G_PKG_NAME
3489 , '>> '|| l_program
3490 || ': After calling generate_batch_id: '
3491 || 'current index = ' || l_curr_index
3492 || ', batch_id = ' || l_batch_id
3493 || ', batch size = ' || l_batch_size
3494 );
3495 END IF;
3496 END IF;
3497
3498 FORALL i IN l_curr_index .. l_batch_size
3499 UPDATE jmf_subcontract_orders
3500 SET batch_id = l_batch_id
3501 , last_update_date = sysdate
3502 , last_updated_by = FND_GLOBAL.user_id
3503 , last_update_login = FND_GLOBAL.login_id
3504 WHERE subcontract_po_shipment_id = l_osa_tbl(i);
3505
3506 IF g_log_enabled THEN
3507 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3508 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3509 , G_PKG_NAME
3510 , '>> ' || l_program
3511 || ': After updating JMF_SUBCONTRACT_ORDERS with batch ID '
3512 || l_batch_id
3513 );
3514 END IF;
3515 END IF;
3516
3517 -- Invoke the worker to process the bunch of transactions that
3518 -- are grouped after generating a new batch id.
3519
3520 JMF_SHIKYU_UTIL.Submit_Worker
3521 ( p_batch_id => l_batch_id
3522 , p_request_count => NVL(p_max_workers,1)
3523 , p_cp_short_name => 'JMFSKIWP'
3524 , p_cp_product_code => 'JMF'
3525 , x_workers => l_request_tbl
3526 , x_request_id => l_request_id
3527 , x_return_status => l_return_status
3528 );
3529
3530 IF g_log_enabled THEN
3531 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3532 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3533 , G_PKG_NAME
3534 , '>> ' || l_program || ': After calling submit_worker, '
3535 || 'l_return_status = ' || l_return_status
3536 || ', l_request_id = ' || l_request_id
3537 );
3538 END IF;
3539 END IF;
3540
3541 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3542 THEN
3543 IF g_log_enabled THEN
3544 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3545 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3546 , G_PKG_NAME
3547 , '>> ' || l_program || ': Batch with id ' || l_batch_id
3548 || ' not processed'
3549 );
3550 END IF;
3551 END IF;
3552 END IF;
3553
3554 IF l_batch_size = l_osa_tbl.LAST
3555 THEN
3556 EXIT;
3557 ELSE
3558 l_curr_index := l_batch_size ;
3559 l_batch_size := p_batch_size+l_curr_index;
3560
3561 IF l_count < l_batch_size
3562 THEN
3563 l_batch_size := l_osa_tbl.LAST;
3564 END IF;
3565 END IF;
3566
3567 END LOOP;
3568 END IF;
3569
3570 --- add check for all workers complete
3571
3572 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3573 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3574 , G_PKG_NAME
3575 , '>> ' || l_program || ': After LOOP, waiting for all workers to complete, '
3576 || 'l_request_tbl COUNT = ' || l_request_tbl.COUNT
3577 );
3578 END IF;
3579
3580 jmf_shikyu_util.wait_for_all_workers(p_workers => l_request_tbl );
3581
3582 IF g_log_enabled AND
3583 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3584 THEN
3585 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3586 , G_PKG_NAME
3587 , '>> ' || l_program || ': Exit'
3588 );
3589 END IF;
3590
3591 END Allocate_batch;
3592
3593 --========================================================================
3594 -- PROCEDURE : Subcontract_Orders_Manager PUBLIC
3595 -- PARAMETERS: p_batch_size Batch size to be processed
3596 -- p_max_workers Maximum no of workers allowed
3597 -- p_operating_unit Operating Unit
3598 -- p_from_organization From Organization
3599 -- p_to_organization To Organization
3600 -- p_init_msg_list indicate if msg list needs to be initialized
3601 -- p_validation_level Validation Level
3602 -- COMMENT : The Interlock Concurrent program manager invokes this procedure
3603 -- to process all the Subcontract Orders. This is the main entry
3604 -- point for processing any subcontract records.
3605 --========================================================================
3606 PROCEDURE Subcontract_Orders_Manager
3607 ( p_batch_size IN NUMBER
3608 , p_max_workers IN NUMBER
3609 , p_operating_unit IN NUMBER
3610 , p_from_organization IN NUMBER
3611 , p_to_organization IN NUMBER
3612 , p_init_msg_list IN VARCHAR2
3613 , p_validation_level IN NUMBER
3614 )
3615 IS
3616 l_program CONSTANT VARCHAR2(30) := 'Subcontract_Orders_Manager';
3617 l_msg_count NUMBER;
3618 l_msg_data VARCHAR2(2000);
3619
3620 BEGIN
3621
3622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3623 g_log_enabled := TRUE;
3624 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3625 , 'JMFVSHKB '
3626 , '>> ' || l_program || ': Start >>'
3627 );
3628 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3629 , 'JMFVSHKB p_batch_size => '
3630 , p_batch_size
3631 );
3632 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3633 , ' p_max_workers => '
3634 , p_max_workers
3635 );
3636 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3637 , ' p_operating_unit => '
3638 , p_operating_unit
3639 );
3640 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3641 , ' p_from_organization => '
3642 , p_from_organization
3643 );
3644 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3645 , ' p_to_organization => '
3646 , p_to_organization
3647 );
3648 END IF;
3649
3650 -- Initialize message list if p_init_msg_list is set to TRUE
3651 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE) ) THEN
3652 FND_MSG_PUB.initialize;
3653 END IF;
3654
3655 -- If SHIKYU is not enabled, do not process the records.
3656
3657 IF NVL(FND_PROFILE.value('JMF_SHK_CHARGE_BASED_ENABLED'),'N') = 'Y'
3658 THEN
3659 -- Load the Subcontract orders that are eligible for processing.
3660 Load_Subcontract_Orders
3661 ( p_operating_unit => p_operating_unit
3662 , p_from_organization => p_from_organization
3663 , p_to_organization => p_to_organization
3664 );
3665
3666 -- Moved the call to Stamp_Null_Shikyu_Comp_Prices before the call
3667 -- to Load_Shikyu_Components, in order to avoid calling OM Process
3668 -- Order API again to get prices for the components that were newly
3669 -- inserted into the JMF_SHIKYU_COMPONENTS table and that failed
3670 -- price quoting the first time (typically because of wrong or
3671 -- missing pricing setup), which can seriously hurt the performance.
3672 Stamp_Null_Shikyu_Comp_Prices
3673 ( p_operating_unit => p_operating_unit
3674 );
3675
3676 -- Bug 5500896: Added an actual parameter to pass the
3677 -- operating unit
3678 Load_Shikyu_Components
3679 ( p_operating_unit => p_operating_unit
3680 );
3681 /*
3682 Stamp_Null_Shikyu_Comp_Prices
3683 ( p_operating_unit => p_operating_unit
3684 );
3685 */
3686 Load_Replenishments
3687 ( p_operating_unit => p_operating_unit
3688 , p_from_organization => p_from_organization
3689 , p_to_organization => p_to_organization
3690 );
3691
3692 -- Bug 5500896: Added actual parameters to pass the
3693 -- operating unit and the range of OEM organization id
3694 Allocate_batch
3695 ( p_batch_size => p_batch_size
3696 , p_max_workers => p_max_workers
3697 , p_operating_unit => p_operating_unit
3698 , p_from_organization => p_from_organization
3699 , p_to_organization => p_to_organization
3700 );
3701
3702 ELSE
3703 FND_MESSAGE.Set_Name('JMF', 'JMF_SHK_NOT_ENABLED');
3704 FND_MSG_PUB.Add;
3705 IF g_log_enabled THEN
3706 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3707 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3708 , G_PKG_NAME
3709 , '>> ' || l_program || ': Charge based SHIKYU not enabled >>'
3710 );
3711 END IF;
3712 END IF;
3713
3714 END IF;
3715
3716 EXCEPTION
3717
3718 WHEN FND_API.G_EXC_ERROR THEN
3719 FND_MESSAGE.set_name('JMF', 'JMF_SHK_INTERLK_MGR_ERR');
3720 FND_MSG_PUB.add;
3721 FND_MSG_PUB.Count_And_Get
3722 ( p_count => l_msg_count
3723 , p_data => l_msg_data
3724 );
3725
3726 WHEN OTHERS THEN
3727 IF g_log_enabled AND
3728 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3729 THEN
3730 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3731 , G_PKG_NAME
3732 , G_PKG_NAME || l_program || ': ' || sqlerrm);
3733 END IF;
3734
3735 FND_MESSAGE.set_name('JMF', 'JMF_SHK_INTERLK_MGR_ERR');
3736 FND_MSG_PUB.add;
3737 FND_MSG_PUB.Count_And_Get
3738 ( p_count => l_msg_count
3739 , p_data => l_msg_data
3740 );
3741
3742 END Subcontract_Orders_Manager;
3743
3744 --========================================================================
3745 -- PROCEDURE : Subcontract_Orders_Worker PUBLIC
3746 -- PARAMETERS: p_batch_id Batch Id
3747 -- COMMENT : This procedure is invoked by the Subcontract_Orders_manager.
3748 -- After the batch is assigned by the Manager, the Subcontract
3749 -- Orders Manager process will launch this worker to complete
3750 -- the processing of the Subcontract Orders.
3751 --========================================================================
3752 PROCEDURE Subcontract_Orders_Worker
3753 ( p_batch_id IN NUMBER
3754 )
3755 IS
3756 l_program CONSTANT VARCHAR2(30) := 'Subcontract_Orders_Worker';
3757 BEGIN
3758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3759 g_log_enabled := TRUE;
3760
3761 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3762 , G_PKG_NAME
3763 , '>> ' || l_program || ': Start'
3764 );
3765 END IF;
3766
3767 JMF_SUBCONTRACT_ORDERS_PVT.Process_Subcontract_Orders(p_batch_id);
3768
3769 IF g_log_enabled THEN
3770 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3771 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3772 , G_PKG_NAME
3773 , '>> ' || l_program || ': Exit'
3774 );
3775 END IF;
3776 END IF;
3777
3778 EXCEPTION
3779
3780 WHEN OTHERS THEN
3781 IF g_log_enabled AND
3782 (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3783 THEN
3784 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3785 , G_PKG_NAME
3786 , G_PKG_NAME || l_program || ': ' || sqlerrm);
3787 END IF;
3788
3789 FND_MESSAGE.set_name('JMF', 'JMF_SHK_INTERLK_WRKR_ERR');
3790 FND_MSG_PUB.add;
3791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3792
3793 END Subcontract_Orders_Worker;
3794
3795 END JMF_SUBCONTRACT_ORDERS_PVT;