[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_PO_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_PO_PVT AS
2 -- $Header: JMFVSKPB.pls 120.24.12010000.2 2008/09/18 18:56:26 rrajkule 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 l_request_id := FND_REQUEST.submit_request
581 ( application => 'PO'
582 , program => 'POXPOPDOI'
583 , description => ''
584 , start_time => ''
585 , sub_request => false
586 , argument1 => ''
587 , argument2 => 'STANDARD'
588 , argument3 => ''
589 , argument4 => 'N'
590 , argument5 => 'N'
591 , argument6 => 'APPROVED'
592 , argument7 => null
593 , argument8 => l_batch_id
594 , argument9 => l_org_id
595 , argument10 => 'N' );
596
597 -- Need to commit for the concurrent request to PDOI to start immediately
598 COMMIT;
599
600 l_status := FND_CONCURRENT.WAIT_FOR_REQUEST
601 ( request_id => l_request_id
602 , interval => 1
603 , max_wait => 600
604 , phase => l_phase
605 , status => l_con_status
606 , dev_phase => l_dev_phase
607 , dev_status => l_dev_status
608 , message => l_message);
609
610 IF l_dev_phase = 'COMPLETE'
611 THEN
612 IF l_dev_status IN ('NORMAL','WARNING')
613 THEN
614 OPEN c_po;
615 FETCH c_po INTO x_po_line_location_id;
616 IF c_po%NOTFOUND
617 THEN
618 x_return_status := FND_API.G_RET_STS_ERROR;
619
620 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
621 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
622 , G_PKG_NAME
623 , '>> '||l_program||' Error creating PO (1)'
624 );
625 END IF;
626
627 ELSE
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
633 , '>> '||l_program||' x_po_line_location_id = ' || x_po_line_location_id
630 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
631 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
632 , G_PKG_NAME
634 );
635 END IF;
636
637 END IF;
638 CLOSE c_po;
639
640 ELSE
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 IF g_log_enabled THEN
643 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
644 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
645 , G_PKG_NAME
646 , '>> '||l_program||' Error creating PO (2): '|| l_message
647 );
648 END IF;
649 END IF;
650
651 END IF;
652 ELSIF l_dev_phase = 'INACTIVE'
653 THEN
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 IF g_log_enabled THEN
656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
658 , G_PKG_NAME
659 , '>> '||l_program||' Manager Inactive'
660 );
661 END IF ;
662 END IF;
663
664 ELSE
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666 IF g_log_enabled THEN
667 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
668 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
669 , G_PKG_NAME
670 , '>> '||l_program||' Running'
671 );
672 END IF;
673 END IF;
674
675 END IF; --dev_phase
676
677 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
678 THEN
679 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_PO_ERR');
680 FND_MSG_PUB.add;
681
682 /* Bug 7000413 - Start */
683 /* Log the error in the Concurrent Request log if allocation fails */
684 BEGIN
685 SELECT segment1
686 INTO l_order_number
687 FROM po_headers_all poh
688 WHERE EXISTS
689 (SELECT 1 FROM po_line_locations_all poll
690 WHERE poll.line_location_id = l_subcontract_orders_rec.subcontract_po_shipment_id
691 AND poll.po_header_id = poh.po_header_id);
692
693 SELECT segment1
694 INTO l_sub_comp
695 FROM mtl_system_items_b
696 WHERE inventory_item_id = l_item_id
697 AND organization_id = l_subcontract_orders_rec.tp_organization_id;
698
699 fnd_message.set_name('JMF','JMF_SHK_REP_PO_ERROR');
700 fnd_message.set_token('SUB_ORDER', l_order_number );
701 fnd_message.set_token('SUB_COMP', l_sub_comp);
702 l_message := fnd_message.GET();
703 fnd_file.put_line(fnd_file.LOG, l_message);
704 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
705 EXCEPTION
706 WHEN OTHERS THEN
707 NULL; -- Return null if there is an error in fetching the message
708 END;
709 /* Bug 7000413 - End */
710
711 END IF;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 x_return_status := FND_API.G_RET_STS_ERROR;
716 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_PO_ERR');
717 FND_MSG_PUB.add;
718
719 END Process_Replenishment_PO;
720
721 END JMF_SHIKYU_PO_PVT;