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