[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_PO_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_PO_PVT AS
2 -- $Header: JMFVSKPB.pls 120.25.12020000.2 2013/01/18 07:12:17 ntungare ship $ --
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFVSKPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package contains PO related calls that the Interlock |
13 --| accesses when processing SHIKYU transactions |
14 --| HISTORY |
15 --| 05/09/2005 pseshadr Created |
16 --| 13/10/2005 vchu Modified the Process_Replenishment_PO |
17 --| procedure for the following: |
18 --| 1) Select the location_id of the TP |
19 --| organization and insert into the |
20 --| ship_to_location_id column of the |
21 --| PO_HEADERS_INTERFACE table. |
22 --| 2) Modified the wait logic after |
23 --| kicking off the concurrent request |
24 --| for PDOI. |
25 --| 11/11/2005 vchu Modified the Process_Replenishment_PO |
26 --| to insert creation_date, batch_id and |
27 --| process_code to po_headers_interface |
28 --| table. Also modified the call to |
29 --| FND_REQUEST.submit_request to include |
30 --| the generated batch_id as parameter. |
31 --| 12/23/2005 vchu Modified the Process_Replenishment_PO |
32 --| procedure to get the need_by_date from |
33 --| the po_line_locations in order to |
34 --| handle changes of the need by date of |
35 --| the Subcontract PO, since Reconciliation|
36 --| conc prg would consider the old date if |
37 --| taking the need_by_date from the |
38 --| JMF_SUBCONTRACT_ORDERS table. |
39 --| 12/27/2005 vchu Added a COMMIT statement after calling |
40 --| FND_REQUEST.submit_request in the |
41 --| Process_Replenishment_PO procedure in |
42 --| order for the child request to start |
43 --| immediately. |
44 --| 12/27/2005 vchu Modified the c_po cursor to get the |
45 --| line_location_id of the newly created |
46 --| PO Shipment with the reference_num |
47 --| being the concatenation of the |
48 --| subcontract_po_shipment_id and item_id |
49 --| 01/19/2005 vchu Changed the maximum wait time for the |
50 --| concurrent request to PDOI to be 10 |
51 --| minutes. WAIT_FOR_REQUEST would not |
52 --| wait for the max wait time if the |
53 --| concurrent request completes sooner, |
54 --| which should be the normal case. |
55 --| 02/08/2006 vchu Bug fix for 4912497: Modified the query |
56 --| to get the currency by joining the |
57 --| gl_sets_of_books table with |
58 --| hr_organization_information, instead of |
59 --| the org_organization_definitions view, |
60 --| which has introduced Full Table Scan on |
61 --| FND_PRODUCT_GROUPS and GL_LEDGERS. |
62 --| 02/16/2006 vchu Bug fix for 4997572: Changed the |
63 --| stamping logic of the reference_num |
64 --| column in the PO interface tables in |
65 --| order to account for the cases where |
66 --| more than one Replenishment POs were |
67 --| created for a particular shikyu |
68 --| component of the subcontracting order, |
69 --| typically date or quantity changes of |
70 --| SHIKYU Reconciliation. |
71 --| 03/03/2006 vchu Bug fix for 4912497: Modified |
72 --| Process_Replenishment_PO to get the |
73 --| max(line_location_id) before kicking |
74 --| off PDOI, in order to speed up the |
75 --| query to get back the line_location_id |
76 --| for the Replenishment PO. |
77 --| (SQL ID 14833933 and 16439305) |
78 --| Also removed commented code. |
79 --| 05/03/2006 vchu Fixed bug 5201694: Modified |
80 --| Process_Replenishment_PO to set context |
81 --| to the OU specified in the concurrent |
82 --| request instead the OU specified in the |
83 --| 'MO: Operating Unit' profile option. |
84 --| 05/09/2006 vchu Bug fix for 5212219: Populate project |
85 --| id and task id into the |
86 --| po_distributions_interface table |
87 --| in order for the corresponding Sales |
88 --| Order to pick up. |
89 --| 05/11/2006 vchu Modified the query for getting the |
90 --| need_by_date of the Subcontracting |
91 --| Order Shipment in |
92 --| Process_Replenishment_PO to get the |
93 --| promised_date if need_by_date is NULL. |
94 --| 01-MAY-2008 kdevadas Bug 7000413 - In case of errors during |
95 --| rep PO creation, the appropriate message |
96 --| is set and displayed in the request log |
97 --| 18-SEP-2008 rrajkule Bug 7383584 - Changed cursor c_PO to |
98 --| add one extra where clause to avoid FTS. |
99 --+=======================================================================+
100
101 --=============================================
102 -- CONSTANTS
103 --=============================================
104 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_PO_PVT';
105 g_log_enabled BOOLEAN;
106
107 --===================
108 -- PROCEDURES AND FUNCTIONS
109 --===================
110
111
112 --========================================================================
113 -- PROCEDURE : Process_Replenishment_PO PUBLIC
114 -- PARAMETERS: p_action Action
115 -- 'C'- Create new job
116 -- 'D'- Delete Job
117 -- 'U'- Update Job
118 -- p_subcontract_po_shipment_id OSA Shipment Line
119 -- p_quantity Replenishment Quantity
120 -- p_item_id Component
121 -- x_return_status Return Status
122 -- COMMENT : This procedure populates data in the interface table
123 -- and creates a replenishment PO for the subcontracting
124 -- order shipment line
125 --========================================================================
126 PROCEDURE Process_Replenishment_PO
127 ( p_action IN VARCHAR2
128 , p_subcontract_po_shipment_id IN NUMBER
129 , p_quantity IN NUMBER
130 , p_item_id IN NUMBER
131 , x_po_line_location_id OUT NOCOPY NUMBER
132 , x_return_status OUT NOCOPY VARCHAR2
133 )
134 IS
135 l_subcontract_orders_rec JMF_SUBCONTRACT_ORDERS%ROWTYPE;
136 l_quantity NUMBER;
137 l_interface_header_id NUMBER;
138 l_interface_line_id NUMBER;
139 l_document_number NUMBER;
140 l_vendor_id NUMBER;
141 l_vendor_site_id NUMBER;
142 l_agent_id NUMBER;
143 l_po_num_code VARCHAR2(30);
144 l_need_by_date DATE;
145 l_request_id NUMBER;
146 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
147 l_price NUMBER;
148 l_item_id NUMBER;
149 l_po_header_id NUMBER;
150 l_err_count NUMBER;
151 l_program CONSTANT VARCHAR2(30) := 'Process_Replenishment_PO';
152 l_phase VARCHAR2(80);
153 l_status BOOLEAN;
154 l_dev_phase VARCHAR2(80);
155 l_con_status VARCHAR2(80);
156 l_dev_status VARCHAR2(80);
157 l_message VARCHAR2(240);
158 l_org_id NUMBER;
159 l_currency_code VARCHAR2(25);
160 l_ship_to_location_id NUMBER;
161 l_max_wait NUMBER;
162 l_batch_id NUMBER;
163 l_max_line_location_id NUMBER;
164 l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
165 l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
166 l_status_flag BOOLEAN;
167
168 CURSOR c_rec IS
169 SELECT *
170 FROM jmf_subcontract_orders
171 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
172
173 -- Bug fix for 4997572
174 -- Changed the query to get the line_location_id of the Replenishment PO Shipment
175 -- created by PDOI, since the logic to stamp the reference_num and line_reference_num
176 -- has been changed in order to account for the cases where more than one
177 -- Replenishment POs were created for a particular shikyu component of the
178 -- subcontracting order, typically SHIKYU Reconciliation. The reference numbers
179 -- are now stamped with the subcontract po shipment id, concatenated with
180 -- interface_header_id for reference_num, and interface line id for line_reference_num
181
182 CURSOR c_po IS
183 SELECT poll.line_location_id
184 FROM po_line_locations_all poll
185 , po_headers_all poh
186 , po_lines_all pol
187 WHERE poll.po_header_id = poh.po_header_id
188 AND poll.po_line_id = pol.po_line_id
189 AND pol.line_reference_num = p_subcontract_po_shipment_id || '-' || l_interface_line_id
190 AND poh.reference_num = p_subcontract_po_shipment_id || '-' || l_interface_header_id
191 AND poh.agent_id = l_agent_id /*Bug 7383584 : Added extra where clause to stop FTS on po_headers_all table*/
192 AND poll.line_location_id > l_max_line_location_id;
193
194 BEGIN
195
196 -- Bug 5201694: Should set context to the OU specified in the concurrent request,
197 -- not the OU specified in the 'MO: Operating Unit' profile option.
198
199 --l_org_id := FND_PROFILE.VALUE('ORG_ID');
200 l_org_id := mo_global.get_current_org_id;
201
202 -- MO_GLOBAL.Init('PO');
203 MO_GLOBAL.set_policy_context('S',l_org_id);
204
205 IF g_log_enabled THEN
206 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
207
208 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
209 , G_PKG_NAME
210 , G_PKG_NAME || ': l_org_id = ' || l_org_id
211 );
212
213 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
214 , G_PKG_NAME
215 , G_PKG_NAME || ': p_action = ' || p_action
216 || ', p_subcontract_po_shipment_id = ' || p_subcontract_po_shipment_id
217 || ', p_quantity = ' || p_quantity
218 || ', p_item_id = ' || p_item_id
219 );
220 END IF;
221 END IF;
222
223 OPEN c_rec;
224 FETCH c_rec INTO l_subcontract_orders_rec;
225 CLOSE c_rec;
226
227 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
228 g_log_enabled := TRUE;
229 END IF;
230
231 l_quantity := p_quantity;
232 l_item_id := p_item_id;
233
234 SELECT NVL(primary_uom_price,0)/NVL(quantity,1)
235 INTO l_price
236 FROM jmf_shikyu_components
237 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
238 AND shikyu_component_id = l_item_id;
239
240 IF g_log_enabled THEN
241 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
243 , G_PKG_NAME
244 , G_PKG_NAME || ': l_price = ' || l_price
245 );
246 END IF;
247 END IF;
248
249 SELECT
250 org_id
251 INTO
252 l_org_id
253 FROM po_line_locations_all
254 WHERE line_location_id = l_subcontract_orders_rec.subcontract_po_shipment_id;
255
256 SELECT
257 user_defined_po_num_code
258 INTO
259 l_po_num_code
260 FROM
261 po_system_parameters_all
262 WHERE org_id = l_org_id;
263
264 SELECT to_number(hoi.org_information3)
265 , to_number(hoi.org_information4)
266 , po_headers_interface_s.nextval
267 , po_lines_interface_s.nextval
268 INTO l_vendor_id
269 , l_vendor_site_id
270 , l_interface_header_id
271 , l_interface_line_id
272 FROM HR_ORGANIZATION_INFORMATION hoi
273 WHERE hoi.organization_id = l_subcontract_orders_rec.oem_organization_id
274 AND hoi.org_information_context = 'Customer/Supplier Association';
275
276 IF g_log_enabled THEN
277 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
278 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
279 , G_PKG_NAME
280 , G_PKG_NAME || ': l_vendor_id = ' || l_vendor_id
281 || ': l_vendor_site_id = ' || l_vendor_site_id
282 || ': l_interface_header_id = ' || l_interface_header_id
283 || ': l_interface_line_id = ' || l_interface_line_id);
284 END IF;
285 END IF;
286
287 /*
288 SELECT glb.currency_code
289 INTO l_currency_code
290 FROM org_organization_definitions ood
291 , gl_sets_of_books glb
292 WHERE ood.set_of_books_id = glb.set_of_books_id
293 AND ood.organization_id = l_subcontract_orders_rec.oem_organization_id;
294 */
295
296 -- Bug 4912497: Modified this query to get the currency from the current
297 -- set of books by joining with the hr_organization_information table,
298 -- instead of the org_organization_definitions view, which has introduced
299 -- the FTS on FND_PRODUCT_GROUPS and GL_LEDGERS
300
301 SELECT glb.currency_code
302 INTO l_currency_code
303 FROM hr_organization_information hoi
304 , gl_sets_of_books glb
305 WHERE hoi.organization_id = l_subcontract_orders_rec.oem_organization_id
306 AND org_information_context = 'Accounting Information'
307 AND TO_NUMBER(hoi.org_information1) = glb.set_of_books_id;
308
309 IF g_log_enabled THEN
310 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
311 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
312 , G_PKG_NAME
313 , G_PKG_NAME || ': l_currency_code = ' || l_currency_code);
314 END IF;
315 END IF;
316
317 IF l_po_num_code <> 'AUTOMATIC'
318 THEN
319 l_document_number := l_interface_header_id;
320 ELSE
321 l_document_number := NULL;
322 END IF;
323
324 IF g_log_enabled THEN
325 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
326 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
327 , G_PKG_NAME
328 , G_PKG_NAME || ': l_document_number = ' || l_document_number
329 );
330 END IF;
331 END IF;
332
333 SELECT employee_id
334 INTO l_agent_id
335 FROM fnd_user
336 WHERE user_id = l_user_id;
337
338 IF g_log_enabled THEN
339 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
341 , G_PKG_NAME
342 , G_PKG_NAME || ': l_agent_id = ' || l_agent_id
343 );
344 END IF;
345 END IF;
346
347 -- To get the actual need_by_date from po_line_locations_all table
348 -- since the it might be changed after the intial creation of the
349 -- Subcontract PO
350
351 SELECT NVL(need_by_date, promised_date)
352 INTO l_subcontract_orders_rec.need_by_date
353 FROM po_line_locations_all
354 WHERE line_location_id = l_subcontract_orders_rec.subcontract_po_shipment_id;
355
356 IF g_log_enabled THEN
357 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
359 , G_PKG_NAME
360 , G_PKG_NAME || ': l_subcontract_orders_rec.need_by_date = '
361 || l_subcontract_orders_rec.need_by_date
362 );
363 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
364 , G_PKG_NAME
365 , G_PKG_NAME || ': quantity = '
366 || l_quantity
367 );
368 END IF;
369 END IF;
370
371 -- The need by date on the Replenishment PO is the same as the
372 -- planned start date for the OSA item.
373
374 JMF_SHIKYU_WIP_PVT.Compute_Start_Date
375 ( p_need_by_date => l_subcontract_orders_rec.need_by_date
376 , p_item_id => l_subcontract_orders_rec.osa_item_id
377 , p_oem_organization => l_subcontract_orders_rec.oem_organization_id
378 , p_tp_organization => l_subcontract_orders_rec.tp_organization_id
379 , p_quantity => l_quantity
380 , x_start_date => l_need_by_date
381 );
382
383 IF g_log_enabled THEN
384 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
385 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
386 , G_PKG_NAME
387 , G_PKG_NAME || ': l_quantity = ' || l_quantity || ', l_need_by_date = ' || l_need_by_date
388 );
389 END IF;
390 END IF;
391
392 l_ship_to_location_id := NULL;
393
394 BEGIN
395 SELECT location_id
396 INTO l_ship_to_location_id
397 FROM hr_all_organization_units
398 WHERE organization_id = l_subcontract_orders_rec.tp_organization_id;
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 l_ship_to_location_id := NULL;
402 END;
403
404 IF g_log_enabled THEN
405 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
406 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
407 , G_PKG_NAME
408 , G_PKG_NAME || ': l_ship_to_location_id = ' || l_ship_to_location_id);
409 END IF;
410 END IF;
411
412 l_batch_id := PO_PDOI_UTL.get_next_batch_id;
413
414 IF g_log_enabled THEN
415 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
416 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
417 , G_PKG_NAME
418 , G_PKG_NAME || ': l_batch_id = ' || l_batch_id);
419 END IF;
420 END IF;
421
422 -- Bug fix for 4997572
423 -- Stamp the reference_num with the concatenation of the subcontract po
424 -- shipment id and interface header id instead of shikyu component id
425
426 INSERT INTO po_headers_interface
427 ( interface_header_id
428 , action
429 , document_type_code
430 , document_num
431 , vendor_id
432 , vendor_site_id
433 , agent_id
434 , reference_num
435 , currency_code
436 , ship_to_location_id
437 , batch_id
438 , process_code
439 , approval_status
440 , approved_date
441 , last_update_date
442 , last_updated_by
443 , last_update_login
444 , creation_date
445 , created_by
446 )
447 VALUES
448 ( l_interface_header_id
449 , 'ORIGINAL'
450 , 'STANDARD'
451 , l_document_number
452 , l_vendor_id
453 , l_vendor_site_id
454 , l_agent_id
455 , p_subcontract_po_shipment_id || '-' || l_interface_header_id
456 , l_currency_code
457 , l_ship_to_location_id
458 , l_batch_id
459 , 'PENDING'
460 , 'APPROVED'
461 , sysdate
462 , sysdate
463 , 1
464 , 1
465 , sysdate
466 , 1
467 );
468
469 IF g_log_enabled THEN
470 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
472 , G_PKG_NAME
473 , G_PKG_NAME || ': Inserted into po_headers_interface');
474 END IF;
475 END IF;
476
477 -- Bug fix for 4997572
478 -- Stamp the reference_num with the concatenation of the subcontract po
479 -- shipment id and interface line id instead of shikyu component id
480
481 INSERT INTO po_lines_interface
482 ( interface_header_id
483 , interface_line_id
484 , line_num
485 , item_id
486 , quantity
487 , need_by_date
488 , promised_date
489 , unit_price
490 , days_early_receipt_allowed
491 , days_late_receipt_allowed
492 , qty_rcv_tolerance
493 , allow_substitute_receipts_flag
494 , receiving_routing_id
495 , organization_id
496 , ship_to_organization_id
497 , line_reference_num
498 , last_update_date
499 , last_updated_by
500 , last_update_login
501 , creation_date
502 , created_by
503 , INVOICE_CLOSE_TOLERANCE
504 )
505 VALUES
506 ( l_interface_header_id
507 , l_interface_line_id
508 , 1
509 , l_item_id
510 , l_quantity
511 , l_need_by_date
512 , l_need_by_date
513 , l_price
514 , 100
515 , 100
516 , 200
517 , 'N'
518 , 3
519 , l_subcontract_orders_rec.tp_organization_id
520 , l_subcontract_orders_rec.tp_organization_id
521 --, l_interface_header_id
522 , p_subcontract_po_shipment_id || '-' || l_interface_line_id
523 , sysdate
524 , 1
525 , 1
526 , sysdate
527 , 1
528 , 100
529 );
530
531 IF g_log_enabled THEN
532 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
533 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
534 , G_PKG_NAME
535 , G_PKG_NAME || ': Inserted into po_lines_interface');
536 END IF;
537 END IF;
538
539 INSERT INTO po_distributions_interface
540 ( interface_header_id
541 , interface_line_id
542 , interface_distribution_id
543 , quantity_ordered
544 , project_id
545 , task_id
546 , last_update_date
547 , last_updated_by
548 , last_update_login
549 , creation_date
550 , created_by
551 )
552 VALUES
553 ( l_interface_header_id
554 , l_interface_line_id
555 , PO_DISTRIBUTIONS_INTERFACE_S.nextval
556 , l_quantity
557 , l_subcontract_orders_rec.project_id
558 , l_subcontract_orders_rec.task_id
559 , sysdate
560 , 1
561 , 1
562 , sysdate
563 , 1
564 );
565
566 IF g_log_enabled THEN
567 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
569 , G_PKG_NAME
570 , G_PKG_NAME || ': Inserted into po_distributions_interface');
571 END IF;
572 END IF;
573
574 -- To get the max line_location_id before calling submit_request
575 -- for better performance
576 SELECT max(line_location_id)
577 INTO l_max_line_location_id
578 FROM po_line_locations_all;
579
580 --
581 -- Bug 16181882
582 -- Passing all the 100 Arguments to avoid error
583 -- while launching the program
584 --
585 l_request_id := FND_REQUEST.submit_request
586 ( application => 'PO'
587 , program => 'POXPOPDOI'
588 , description => ''
589 , start_time => ''
590 , sub_request => false
591 , argument1 => ''
592 , argument2 => 'STANDARD'
593 , argument3 => ''
594 , argument4 => 'N'
595 , argument5 => 'N'
596 , argument6 => 'APPROVED'
597 , argument7 => null
598 , argument8 => l_batch_id
599 , argument9 => l_org_id
600 , argument10 => 'N'
601 , argument11 => ''
602 , argument12 => ''
603 , argument13 => ''
604 , argument14 => ''
605 , argument15 => ''
606 , argument16 => ''
607 , argument17 => ''
608 , argument18 => ''
609 , argument19 => ''
610 , argument20 => ''
611 , argument21 => ''
612 , argument22 => ''
613 , argument23 => ''
614 , argument24 => ''
615 , argument25 => ''
616 , argument26 => ''
617 , argument27 => ''
618 , argument28 => ''
619 , argument29 => ''
620 , argument30 => ''
621 , argument31 => ''
622 , argument32 => ''
623 , argument33 => ''
624 , argument34 => ''
625 , argument35 => ''
626 , argument36 => ''
627 , argument37 => ''
628 , argument38 => ''
629 , argument39 => ''
630 , argument40 => ''
631 , argument41 => ''
632 , argument42 => ''
633 , argument43 => ''
634 , argument44 => ''
635 , argument45 => ''
636 , argument46 => ''
637 , argument47 => ''
638 , argument48 => ''
639 , argument49 => ''
640 , argument50 => ''
641 , argument51 => ''
642 , argument52 => ''
643 , argument53 => ''
644 , argument54 => ''
645 , argument55 => ''
646 , argument56 => ''
647 , argument57 => ''
648 , argument58 => ''
649 , argument59 => ''
650 , argument60 => ''
651 , argument61 => ''
652 , argument62 => ''
653 , argument63 => ''
654 , argument64 => ''
655 , argument65 => ''
656 , argument66 => ''
657 , argument67 => ''
658 , argument68 => ''
659 , argument69 => ''
660 , argument70 => ''
661 , argument71 => ''
662 , argument72 => ''
663 , argument73 => ''
664 , argument74 => ''
665 , argument75 => ''
666 , argument76 => ''
667 , argument77 => ''
668 , argument78 => ''
669 , argument79 => ''
670 , argument80 => ''
671 , argument81 => ''
672 , argument82 => ''
673 , argument83 => ''
674 , argument84 => ''
675 , argument85 => ''
676 , argument86 => ''
677 , argument87 => ''
678 , argument88 => ''
679 , argument89 => ''
680 , argument90 => ''
681 , argument91 => ''
682 , argument92 => ''
683 , argument93 => ''
684 , argument94 => ''
685 , argument95 => ''
686 , argument96 => ''
687 , argument97 => ''
688 , argument98 => ''
689 , argument99 => ''
690 , argument100 => '');
691
692 -- Need to commit for the concurrent request to PDOI to start immediately
693 COMMIT;
694
695 l_status := FND_CONCURRENT.WAIT_FOR_REQUEST
696 ( request_id => l_request_id
697 , interval => 1
698 , max_wait => 600
699 , phase => l_phase
700 , status => l_con_status
701 , dev_phase => l_dev_phase
702 , dev_status => l_dev_status
703 , message => l_message);
704
705 IF l_dev_phase = 'COMPLETE'
706 THEN
707 IF l_dev_status IN ('NORMAL','WARNING')
708 THEN
709 OPEN c_po;
710 FETCH c_po INTO x_po_line_location_id;
711 IF c_po%NOTFOUND
712 THEN
713 x_return_status := FND_API.G_RET_STS_ERROR;
714
715 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
717 , G_PKG_NAME
718 , '>> '||l_program||' Error creating PO (1)'
719 );
720 END IF;
721
722 ELSE
723 x_return_status := FND_API.G_RET_STS_SUCCESS;
724
725 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
726 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
727 , G_PKG_NAME
728 , '>> '||l_program||' x_po_line_location_id = ' || x_po_line_location_id
729 );
730 END IF;
731
732 END IF;
733 CLOSE c_po;
734
735 ELSE
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 IF g_log_enabled THEN
738 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
739 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
740 , G_PKG_NAME
741 , '>> '||l_program||' Error creating PO (2): '|| l_message
742 );
743 END IF;
744 END IF;
745
746 END IF;
747 ELSIF l_dev_phase = 'INACTIVE'
748 THEN
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 IF g_log_enabled THEN
751 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
752 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
753 , G_PKG_NAME
754 , '>> '||l_program||' Manager Inactive'
755 );
756 END IF ;
757 END IF;
758
759 ELSE
760 x_return_status := FND_API.G_RET_STS_SUCCESS;
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||' Running'
766 );
767 END IF;
768 END IF;
769
770 END IF; --dev_phase
771
772 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
773 THEN
774 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_PO_ERR');
775 FND_MSG_PUB.add;
776
777 /* Bug 7000413 - Start */
778 /* Log the error in the Concurrent Request log if allocation fails */
779 BEGIN
780 SELECT segment1
781 INTO l_order_number
782 FROM po_headers_all poh
783 WHERE EXISTS
784 (SELECT 1 FROM po_line_locations_all poll
785 WHERE poll.line_location_id = l_subcontract_orders_rec.subcontract_po_shipment_id
786 AND poll.po_header_id = poh.po_header_id);
787
788 SELECT segment1
789 INTO l_sub_comp
790 FROM mtl_system_items_b
791 WHERE inventory_item_id = l_item_id
792 AND organization_id = l_subcontract_orders_rec.tp_organization_id;
793
794 fnd_message.set_name('JMF','JMF_SHK_REP_PO_ERROR');
795 fnd_message.set_token('SUB_ORDER', l_order_number );
796 fnd_message.set_token('SUB_COMP', l_sub_comp);
797 l_message := fnd_message.GET();
798 fnd_file.put_line(fnd_file.LOG, l_message);
799 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
800 EXCEPTION
801 WHEN OTHERS THEN
802 NULL; -- Return null if there is an error in fetching the message
803 END;
804 /* Bug 7000413 - End */
805
806 END IF;
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_PO_ERR');
812 FND_MSG_PUB.add;
813
814 END Process_Replenishment_PO;
815
816 END JMF_SHIKYU_PO_PVT;