1 PACKAGE BODY JMF_SHIKYU_ONT_PVT AS
2 -- $Header: JMFVSKOB.pls 120.27 2008/05/02 10:50:44 kdevadas ship $ --
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFVSKOB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package contains ONT related calls that the Interlock |
13 --| accesses when processing SHIKYU transactions |
14 --| |
15 --| PUBLIC FUNCTIONS/PROCEDURES: |
16 --| Calculate_Ship_Date |
17 --| Process_Replenishment_SO |
18 --| |
19 --| HISTORY |
20 --| 05/09/2005 pseshadr Created |
21 --| 07/08/2005 vchu Fixed GSCC error File.Pkg.21 |
22 --| 09/07/2005 vchu Fixed Bug 4597298: removed reference to |
23 --| RA_CUSTOMERS, which have been obsoleted |
24 --| for R12, in Process_Replenishment_SO. |
25 --| 10/17/2005 vchu Modified signatures for Calculate_Ship_Date |
26 --| and Process_Replenishment_SO to fix an |
27 --| issue with the calculation of scheduled |
28 --| ship date. |
29 --| 10/26/2005 vchu Modified the logic of |
30 --| Process_Replenishment_SO to be dependent on |
31 --| the associated Replenishment PO rather than |
32 --| the Subcontracting PO if the action is 'C' |
33 --| 02/15/2006 vchu Changed the header comments of |
34 --| Calculate_Ship_Date to specify that it has |
35 --| has been made public. |
36 --| 03/23/2006 vchu Fixed bug 5090721: Set last_updated_by and |
37 --| last_update_login in the update statements. |
38 --| 03/24/2006 vchu Fixed bug 4885422: Modified the logic in |
39 --| Process_Replenishment_SO to handle the case |
40 --| where the current price of the SHIKYU |
41 --| Component is defined in a secondary UOM. |
42 --| 03/24/2006 vchu Fixed bug 5090721: Removed commented code. |
43 --| 03/24/2006 vchu Cleaned up indentation and changed the |
44 --| calls to FND_LOG.string to be enclosed in a |
45 --| single IF statement instead of nested ID |
46 --| statements. |
47 --| 04/05/2006 vchu Modified the Price Quoting logic of |
48 --| Process_Replenishment_SO to get the price |
49 --| of the component directly from the price |
50 --| list instead of making a second call to |
51 --| the Process Order API. Also, added |
52 --| exception handling logic to handle the case |
53 --| where there are more than one price |
54 --| effective (more than one price list line) |
55 --| for the component. |
56 --| 04/12/2006 vchu Fixed bug 5154755: Added logic to rollback |
57 --| the newly created Sales Order, if its |
58 --| unit_selling_price is NULL. Also added the |
59 --| logic to update the JMF_SHIKYU_COMPONENTS |
60 --| record to update the price list id and |
61 --| currency even if there are too many |
62 --| effective price list lines. |
63 --| 04/13/2006 vchu Polished up the FND Log Messages. |
64 --| 04/18/2006 rajkrish Fixed bug 5002921: Set entity_code and |
65 --| request_type of the l_action_request_tbl |
66 --| in order to book the Sales Order. |
67 --| 04/21/2006 vchu Removed commented code. |
68 --| 05/03/2006 vchu Fixed bug 5201694: Modified |
69 --| Process_Replenishment_SO to set context to |
70 --| the OU specified in the concurrent request, |
71 --| instead the OU specified in the |
72 --| 'MO: Operating Unit' profile option. |
73 --| 05/05/2006 rajkrish Fixed bug 5209846 : Modified |
74 --| Process_Replenishment_SO to get the Org ID |
75 --| from the Replenishment PO Shipment, if |
76 --| mo_global.get_current_org_id returned NULL. |
77 --| This is crucial for the Interlock Worker |
78 --| since it is not MOAC enabled (in order to |
79 --| support more then one OU). |
80 --| 05/08/2006 vchu Fixed bug 5212219: Get the project and task |
81 --| ID from the distributions of the |
82 --| Replenishment PO Shipment, and pass it to |
83 --| the line level table parameter of the |
84 --| Process Order API. |
85 --| Also fixed a stamping issue of the |
86 --| primary_uom_price column of the |
87 --| JMF_SHIKYU_COMPONENTS table in cases where |
88 --| the value of the uom and primary uom |
89 --| columns are the same primary UOM is active |
90 --| in the price list. This caused an |
91 --| allocation issue for the sync-ship |
92 --| components after an additional price check |
93 --| was added to Create_New_Replenishment_Po_So |
94 --| of the JMF_SHIKYU_ALLOCATION_PVT package. |
95 --| 05/09/2006 vchu Fixed bug 5216720: Modified the |
96 --| c_project_cur cursor to get Project ID and |
97 --| Task ID from the Replenishment PO instead |
98 --| of the Subcontracting PO. |
99 --| 05/11/2006 vchu Modified various queries to get the |
100 --| promised_date from PO_LINE_LOCATIONS_ALL |
101 --| if need_by_date is NULL. |
102 --| 08/24/2006 vchu Fixed bug 5485115: Remove the dependency on |
103 --| OE_PRICE_LIST_LINES in by querying the base |
104 --| table QP_LIST_LINES to get the unit_code |
105 --| and list_price for the specific |
106 --| subcontracting component. |
107 --| 11/08/2006 vchu Fixed bug 5649321: Added an additional |
108 --| where clause condition in the query to get |
109 --| the Bill-to site ID of the customer (in |
110 --| Process_Replenishment_SO), in order to |
111 --| select only the active Bill-To site. |
112 --| 1-May-08 kdevadas Bug 7000413: If Rep SO creation fails, |
113 --| complete the request in warning and log the |
114 --| error in the request log |
115 --+=======================================================================+
116
117 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_ONT_PVT';
118 g_log_enabled BOOLEAN;
119
120 --=============================================
121 -- PROCEDURES AND FUNCTIONS
122 --=============================================
123
124 --========================================================================
125 -- PROCEDURE : Calculate_Ship_Date PUBLIC
126 -- PARAMETERS:
127 -- p_subcontract_po_shipment_id Subcontracting Order Shipment ID
128 -- p_component_item_id SHIKYU Component to be shipped
129 -- p_oem_organization_id OEM Organization
130 -- p_tp_organization_id TP Organization
131 -- p_need_by_date Need By Date of the corresponding
132 -- Replenishment PO Shipment
133 -- x_ship_date Ship Date calculated to meet the
134 -- passed in Need_By_Date
135 -- COMMENT : This procedure computes the scheduled ship date for the component
136 -- based on the WIP start date and item lead times.
137 --========================================================================
138 PROCEDURE Calculate_Ship_Date
139 ( p_subcontract_po_shipment_id IN NUMBER
140 , p_component_item_id IN NUMBER
141 , p_oem_organization_id IN NUMBER
142 , p_tp_organization_id IN NUMBER
143 , p_quantity IN NUMBER
144 , p_need_by_date IN DATE
145 , x_ship_date OUT NOCOPY DATE
146 )
147 IS
148
149 l_program CONSTANT VARCHAR2(30) := 'Calculate_Ship_Date';
150 l_intransit_time NUMBER;
151 l_wip_start_date DATE;
152 l_need_by_date DATE;
153 l_osa_item_id NUMBER;
154
155 CURSOR c_interorg IS
156 SELECT NVL(intransit_time,0)
157 FROM mtl_interorg_ship_methods
158 WHERE from_organization_id = p_oem_organization_id
159 AND to_organization_id = p_tp_organization_id
160 AND default_flag =1;
161
162 BEGIN
163
164 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
165 g_log_enabled := TRUE;
166
167 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
168 , G_PKG_NAME
169 , '>> ' || l_program || ': Start'
170 );
171 END IF;
172
173 OPEN c_interorg;
174 FETCH c_interorg
175 INTO l_intransit_time;
176
177 IF c_interorg%NOTFOUND
178 THEN
179 l_intransit_time :=0;
180 END IF;
181 CLOSE c_interorg;
182
183 IF p_need_by_date IS NULL AND
184 p_subcontract_po_shipment_id IS NOT NULL
185 THEN
186
187 -- Modified this query to get the need by date from the PO Line Location
188 -- directly
189
190 SELECT NVL(plla.need_by_date, plla.promised_date)
191 , jso.osa_item_id
192 INTO
193 l_need_by_date
194 , l_osa_item_id
195 FROM
196 jmf_subcontract_orders jso,
197 po_line_locations_all plla
198 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
199 AND plla.line_location_id = jso.subcontract_po_shipment_id;
200
201 JMF_SHIKYU_WIP_PVT.Compute_Start_Date
202 ( p_need_by_date => l_need_by_date
203 , p_item_id => l_osa_item_id
204 , p_oem_organization => p_oem_organization_id
205 , p_tp_organization => p_tp_organization_id
206 , p_quantity => p_quantity
207 , x_start_date => l_wip_start_date
208 );
209
210 IF g_log_enabled AND
211 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
212 THEN
213 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
214 , G_PKG_NAME
215 , '>> ' || l_program || ': l_wip_start_date = '
216 || l_wip_start_date
217 );
218 END IF;
219
220 x_ship_date := l_wip_start_date - l_intransit_time;
221
222 ELSE
223
224 IF g_log_enabled AND
225 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
226 THEN
227 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
228 , G_PKG_NAME
229 , '>> ' || l_program || ': p_need_by_date = '
230 || p_need_by_date
231 );
232 END IF;
233
234 x_ship_date := p_need_by_date - l_intransit_time;
235
236 END IF; /* p_need_by_date IS NULL AND
237 p_subcontract_po_shipment_id IS NOT NULL */
238
239 IF x_ship_date < sysdate
240 THEN
241 x_ship_date := sysdate;
242 END IF;
243
244 IF g_log_enabled AND
245 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
246 THEN
247 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
248 , G_PKG_NAME
249 , '>> ' || l_program || ': Returning x_ship_date = ' || x_ship_date
250 );
251 END IF;
252
253 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
254 g_log_enabled := TRUE;
255
256 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
257 , G_PKG_NAME
258 , '>> ' || l_program || ': End'
259 );
260 END IF;
261
262 END Calculate_Ship_Date;
263
264 --========================================================================
265 -- PROCEDURE : Process_Replenishment_SO PUBLIC
266 -- PARAMETERS: p_action Action
267 -- 'C'- Create new job
268 -- 'D'- Delete Job
269 -- 'U'- Update Job
270 -- 'Q'- Price Quote
271 -- x_return_status Return Status
272 -- COMMENT : This procedure populates data in the interface table
273 -- and creates a replenishment SO for the subcontracting
274 -- order shipment line
275 --========================================================================
276 PROCEDURE Process_Replenishment_SO
277 ( p_action IN VARCHAR2
278 , p_subcontract_po_shipment_id IN NUMBER
279 , p_quantity IN NUMBER
280 , p_item_id IN NUMBER
281 , p_replen_po_shipment_id IN NUMBER
282 , p_oem_organization_id IN NUMBER
283 , p_tp_organization_id IN NUMBER
284 , x_order_line_id OUT NOCOPY NUMBER
285 , x_return_status OUT NOCOPY VARCHAR2
286 )
287 IS
288
289 TYPE l_project_rec IS RECORD
290 ( project_id NUMBER
291 , task_id NUMBER
292 );
293
294 TYPE l_project_Tabtype IS TABLE of l_project_rec
295 INDEX BY PLS_INTEGER;
296
297 l_program CONSTANT VARCHAR2(30) := 'Process_Replenishment_SO';
298
299 l_oe_header_rec oe_order_pub.Header_Rec_Type;
300 l_oe_line_rec oe_order_pub.Line_Rec_Type;
301
302 l_msg_count number;
303 l_msg_data varchar2(2000);
304 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
305 l_header_rec oe_order_pub.Header_Rec_Type;
309 l_Header_price_Att_tbl oe_order_pub.Header_Price_Att_Tbl_Type;
306 l_header_val_rec oe_order_pub.Header_Val_Rec_Type ;
307 l_Header_Adj_tbl oe_order_pub.Header_Adj_Tbl_Type;
308 l_Header_Adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type;
310 l_Header_Adj_Att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;
311 l_Header_Adj_Assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;
312 l_Header_Scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type;
313 l_Header_Scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type;
314 l_line_tbl oe_order_pub.Line_Tbl_Type;
315 l_line_val_tbl oe_order_pub.Line_Val_Tbl_Type;
316 l_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type;
317 l_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type;
318 l_Line_price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type;
319 l_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type;
320 l_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;
321 l_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type ;
322 l_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type;
323 l_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type;
324 l_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type;
325 l_action_request_tbl oe_order_pub.Request_Tbl_Type;
326 l_ship_date DATE;
327 l_quantity NUMBER;
328 l_primary_uom_code VARCHAR2(3);
329 l_unit_price NUMBER;
330 l_component_price NUMBER;
331 l_price_list_id NUMBER;
332 l_price_list_uom VARCHAR2(3);
333 l_pl_uom_qty NUMBER;
334 l_line_type_id NUMBER;
335 l_currency_code OE_PRICE_LISTS.CURRENCY_CODE%TYPE;
336 l_control_rec oe_globals.control_rec_type;
337 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
338 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
339 l_x_line_tbl OE_Order_PUB.Line_Tbl_Type;
340 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
341 l_x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
342 l_x_action_request_tbl OE_Order_PUB.request_tbl_type;
343 l_x_lot_serial_tbl OE_Order_PUB.lot_serial_tbl_type;
344 l_hdr_payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
345 l_line_payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
346 l_org_id NUMBER;
347 l_pl_count NUMBER;
348 l_customer_id NUMBER;
349 l_bill_to_id NUMBER;
350 l_order_type_id NUMBER;
351 l_replen_po_need_by_date DATE;
352 l_uom_conversion_rate NUMBER;
353 l_item_number VARCHAR2(2000);
354
355 l_no_price_list_found EXCEPTION;
356 l_too_many_effective_prices EXCEPTION;
357 l_null_unit_price EXCEPTION;
358 l_too_many_project_task_ref EXCEPTION;
359
360 l_client_info_org_id NUMBER;
361 l_project_id NUMBER;
362 l_task_id NUMBER;
363
364 l_project_tbl l_project_Tabtype;
365 l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
366 l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
367 l_message VARCHAR(2000);
368 l_status_flag BOOLEAN;
369
370 -- Bug 5216720: Should get Project ID and Task ID from the Replenishment
371 -- PO, not the Subcontracting PO
372
373 CURSOR c_project_cur IS
374 SELECT distinct project_id
375 , task_id
376 FROM po_distributions_all
377 WHERE line_location_id = p_replen_po_shipment_id
378 AND project_id IS NOT NULL;
379
380 BEGIN
381
382 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
383 g_log_enabled := TRUE;
384
385 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
386 , G_PKG_NAME
387 , '>> ' || l_program || ': Start'
388 );
389 END IF;
390
391 IF g_log_enabled AND
392 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
393 THEN
394 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
395 , G_PKG_NAME
396 , '>> ' || l_program
397 || ': p_action = ' || p_action
398 || ', p_subcontract_po_shipment_id = ' || p_subcontract_po_shipment_id
399 || ', p_item_id = ' || p_item_id
400 || ', p_quantity = ' || p_quantity
401 || ', p_replen_po_shipment_id = ' || p_replen_po_shipment_id
402 );
403 END IF;
404
405 -- Bug 5201694: Should set context to OU specified in the concurrent request,
406 -- not the OU specified in the 'MO: Operating Unit' profile option.
407
408 --l_org_id := FND_PROFILE.VALUE('ORG_ID');
409 l_org_id := mo_global.get_current_org_id;
410
411
412 IF g_log_enabled AND
413 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
414 THEN
415 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
416 , G_PKG_NAME
417 , '>> ' || l_program
418 ||': Org ID from mo_global.get_current_org_id = ' || l_org_id
419 );
420 END IF;
421
422 -- Bug 5209846 : Get the Operating Unit (Org ID) from the Replenishment PO
426
423 -- Shipment, if mo_global.get_current_org_id returned NULL. This is necessary
424 -- for the Interlock Worker since the Worker Concurrent Program is not MOAC
425 -- enabled (to support more then one OU).
427 BEGIN
428
429 IF l_org_id is NULL
430 THEN
431 SELECT org_id
432 INTO l_org_id
433 FROM po_line_locations_all
434 WHERE line_location_id = p_replen_po_shipment_id;
435 END IF;
436
437 IF g_log_enabled AND
438 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
439 THEN
440 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
441 , G_PKG_NAME
442 , '>> ' || l_program ||': Org ID selected from PO_LINE_LOCATIONS_ALL = '
443 || l_org_id
444 );
445 END IF;
446
447 END;
448
449 MO_GLOBAL.set_policy_context('S', l_org_id);
450
451 IF g_log_enabled AND
452 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
453 THEN
454 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
455 , G_PKG_NAME
456 , '>> ' || l_program
457 ||': Org ID from FND_PROFILE = ' || FND_PROFILE.VALUE('ORG_ID')
458 );
459 END IF;
460
461 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
462 INTO l_client_info_org_id
463 FROM DUAL;
464
465 IF g_log_enabled AND
466 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
467 THEN
468 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
469 , G_PKG_NAME
470 , '>> ' || l_program
471 ||': Org ID from CLIENT_INFO = ' || l_client_info_org_id
472 );
473 END IF;
474
475 IF l_client_info_org_id <> l_org_id
476 THEN
477 fnd_client_info.set_org_context(TO_CHAR(l_org_id));
478
479 IF g_log_enabled AND
480 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
481 THEN
482 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
483 , G_PKG_NAME
484 , '>> ' || l_program
485 || ': Setting the Org Context of CLIENT_INFO to the OU specified for MOAC ('
486 || l_org_id || ')'
487 );
488 END IF;
489
490 END IF;
491
492 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
493 g_log_enabled := TRUE;
494 END IF;
495
496 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
497 l_oe_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
498 l_quantity := p_quantity;
499
500 -- Bugs 5212219: To get the appropriate project and task reference from
501 -- the Replenishment PO
502
503 OPEN c_project_cur;
504 FETCH c_project_cur
505 BULK COLLECT INTO l_project_tbl;
506
507 IF l_project_tbl.COUNT > 1
508 THEN
509
510 RAISE l_too_many_project_task_ref;
511
512 ELSIF l_project_tbl.COUNT = 1
513 THEN
514
515 l_project_id := l_project_tbl(l_project_tbl.FIRST).project_id;
516 l_task_id := l_project_tbl(l_project_tbl.FIRST).task_id;
517
518 ELSE
519
520 l_project_id := NULL;
521 l_task_id := NULL;
522
523 END IF; /* IF l_project_tbl.COUNT > 1 */
524
525 CLOSE c_project_cur;
526
527 IF g_log_enabled AND
528 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
529 THEN
530 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
531 , G_PKG_NAME
532 , '>> ' || l_program || ': Project ID = ' || l_project_id
533 || ', Task ID = ' || l_task_id
534 );
535 END IF;
536
537 -- Get customer id
538 SELECT TO_NUMBER(org_information1)
539 INTO l_header_rec.sold_to_org_id
540 FROM HR_ORGANIZATION_INFORMATION
541 WHERE organization_id = p_tp_organization_id
542 AND org_information_context = 'Customer/Supplier Association';
543
544 IF g_log_enabled AND
545 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
546 THEN
547 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
548 , G_PKG_NAME
549 , '>> ' || l_program
550 ||': Ship To customer id = ' || l_header_rec.sold_to_org_id
551 );
552 END IF;
553
554 -- Bug 4597298
555 -- Remove reference to RA_CUSTOMERS, which have been obsoleted for R12
556 -- The view oe_invoice_to_orgs_v selects HZ_CUST_SITE_USES_ALL.CUST_ACCOUNT_ID
557 -- as customer_id, and HZ_CUST_SITE_USES_ALL.CUST_ACCOUNT_ID is just a foreign
558 -- key to HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID. So it should be safe to just
559 -- remove the join with RA_CUSTOMERS, since the replacement object for getting
560 -- the CUSTOMER_ID of RA_CUSTOMERS is HZ_CUST_ACCOUNTS.
561
562 -- Get Bill-To customer site id
563 -- Bug 5201694
564
565 -- Bug 5649321: Added an additional where clause condition to get only the
566 -- active Bill-To site. This avoids a 'More than one row fetched'
570 INTO l_header_rec.invoice_to_org_id
567 -- exception being thrown if there exists any inactive sites.
568
569 SELECT site.site_use_id
571 FROM hz_cust_site_uses_all site,
572 hz_cust_acct_sites_all acct_site
573 WHERE site.cust_acct_site_id = acct_site.cust_acct_site_id
574 AND site.site_use_code = 'BILL_TO'
575 AND site.org_id = acct_site.org_id
576 AND acct_site.cust_account_id = l_header_rec.sold_to_org_id
577 AND site.status = 'A';
578
579 IF g_log_enabled AND
580 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
581 THEN
582 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
583 , G_PKG_NAME
584 , '>> ' || l_program
585 || ': Bill To org id = ' || l_header_rec.invoice_to_org_id
586 );
587 END IF;
588
589 -- Get the SHIKYU default order type from the shikyu-enabled Shipping Network
590 -- from the OEM to the TP
591 SELECT shikyu_default_order_type_id
592 INTO l_header_rec.order_type_id
593 FROM mtl_interorg_parameters
594 WHERE from_organization_id = p_oem_organization_id
595 AND to_organization_id = p_tp_organization_id;
596
597 -- Get the default outbound line type of the SHIKYU default order type
598 SELECT default_outbound_line_type_id
599 INTO l_line_type_id
600 FROM oe_transaction_Types_all
601 WHERE transaction_type_id = l_header_rec.order_type_id;
602
603 IF g_log_enabled AND
604 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
605 THEN
606 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
607 , G_PKG_NAME
608 , '>> ' || l_program
609 || ': SHIKYU Default Order Type = ' || l_header_rec.order_type_id
610 || ', Default Outbound Line Type = ' || l_line_type_id
611 );
612 END IF;
613
614 BEGIN
615 IF p_replen_po_shipment_id IS NOT NULL
616 THEN
617 SELECT NVL(need_by_date, promised_date)
618 INTO l_replen_po_need_by_date
619 FROM po_line_locations_all
620 WHERE line_location_id = p_replen_po_shipment_id;
621
622 IF g_log_enabled AND
623 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
624 THEN
625 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
626 , G_PKG_NAME
627 , '>> ' || l_program
628 || ': Replenishment PO Need By Date = ' || l_replen_po_need_by_date
629 );
630 END IF;
631
632 ELSE
633 l_replen_po_need_by_date := NULL;
634 END IF;
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 l_replen_po_need_by_date := NULL;
638 END;
639
640 Calculate_Ship_Date
641 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
642 , p_component_item_id => p_item_id
643 , p_oem_organization_id => p_oem_organization_id
644 , p_tp_organization_id => p_tp_organization_id
645 , p_quantity => l_quantity
646 , p_need_by_date => l_replen_po_need_by_date
647 , x_ship_date => l_ship_date
648 );
649
650 IF g_log_enabled AND
651 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
652 THEN
653 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
654 , G_PKG_NAME
655 , '>> ' || l_program || ': Ship date = ' || l_ship_date
656 );
657 END IF;
658
659 l_customer_id := l_header_rec.sold_to_org_id;
660 l_bill_to_id := l_header_rec.invoice_to_org_id;
661 l_order_type_id := l_header_rec.order_type_id;
662 l_header_rec.ship_from_org_id :=
663 p_oem_organization_id;
664
665 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
666 l_header_rec.transaction_phase_code:= 'F';
667
668 -- SO Line record
669 l_line_tbl(1) := l_oe_line_rec;
670 l_line_tbl(1).ordered_quantity := p_quantity;
671 l_line_tbl(1).inventory_item_id := p_item_id;
672 l_line_tbl(1).schedule_ship_Date := l_ship_date;
673 l_line_tbl(1).request_date := l_ship_date;
674 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
675 l_line_tbl(1).line_type_id := l_line_type_id;
676 l_line_tbl(1).project_id := l_project_id;
677 l_line_tbl(1).task_id := l_task_id;
678
679 IF g_log_enabled AND
680 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
681 THEN
682 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
683 , G_PKG_NAME
684 , '>> ' || l_program || ': Parameters passing to Process Order:'
685 );
686 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
687 , G_PKG_NAME
688 , '>> ' || l_program || ': Ordered_quantity = ' || p_quantity
689 || ', Item = ' || p_item_id
690 || ', Ship Date = ' || l_ship_date
691 || ', Line type = ' || l_line_type_id
692 );
693 END IF;
694
695 SELECT primary_uom_code
696 INTO l_primary_uom_code
697 FROM mtl_system_items_b
698 WHERE inventory_item_id = p_item_id
699 AND organization_id = p_oem_organization_id;
700
701 IF g_log_enabled AND
705 , G_PKG_NAME
702 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
703 THEN
704 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
706 , '>> ' || l_program
707 ||': Primary UOM = '|| l_primary_uom_code
708 );
709 END IF;
710
711 IF NVL(p_action,'C') = 'C'
712 THEN
713
714 IF g_log_enabled AND
715 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
716 THEN
717 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
718 , G_PKG_NAME
719 , '>> ' || l_program
720 || ': p_action = ''C'': Getting UOM from Replenishment PO Shipment'
721 );
722 END IF;
723
724 IF p_replen_po_shipment_id IS NOT NULL
725 THEN
726
727 -- Get the UOM and quantity from the Replenishment PO Shipment to
728 -- which the Replenishment SO Line to be created will be connected
729
730 SELECT muomvl.uom_code,
731 plla.quantity
732 INTO l_price_list_uom,
733 l_quantity
734 FROM po_line_locations_all plla,
735 mtl_units_of_measure_vl muomvl
736 WHERE plla.line_location_id = p_replen_po_shipment_id
737 AND plla.unit_meas_lookup_code = muomvl.unit_of_measure;
738
739 ELSE
740
741 -- Assume primary UOM is to be used if no reference to a Replenishment PO is provided
742
743 l_price_list_uom := l_primary_uom_code;
744
745 END IF; /* IF p_replen_po_shipment_id IS NOT NULL */
746
747 IF g_log_enabled AND
748 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
749 THEN
750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
751 , G_PKG_NAME
752 , '>> ' || l_program
753 || ': l_price_list_uom = ' || l_price_list_uom
754 || ', l_quantity = ' || l_quantity
755 );
756 END IF;
757
758 l_line_tbl(1).visible_demand_flag := 'Y';
759 l_line_tbl(1).order_quantity_uom := l_price_list_uom;
760 l_line_tbl(1).ordered_quantity := l_quantity;
761
762 -- Setting the values of the action_request_tbl in order to book the
763 -- Sales Order
764 l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
765 l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
766
767 END IF; /* IF NVL(p_action,'C') = 'C' */
768
769 IF g_log_enabled AND
770 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
771 THEN
772 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
773 , G_PKG_NAME
774 , '>> ' || l_program || ': Calling Process Order, Creating SAVEPOINT before_process_order'
775 );
776 END IF;
777
778 SAVEPOINT before_process_order;
779
780 OE_Order_PVT.Process_order
781 ( p_api_version_number => 1.0
782 , p_init_msg_list => FND_API.G_TRUE
783 , x_return_status => l_return_status
784 , x_msg_count => l_msg_count
785 , x_msg_data => l_msg_data
786 , p_control_rec => l_control_rec
787 , p_x_header_Rec => l_header_rec
788 , p_x_line_tbl => l_line_tbl
789 , p_x_action_request_tbl => l_action_request_tbl
790 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
791 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
792 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
793 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
794 , p_x_Lot_Serial_tbl => l_x_lot_serial_tbl
795 , p_x_Header_price_Att_tbl => l_Header_price_Att_tbl
796 , p_x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
797 , p_x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
798 , p_x_Line_price_Att_tbl => l_Line_price_Att_tbl
799 , p_x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
800 , p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
801 , p_x_header_payment_tbl => l_hdr_payment_tbl
802 , p_x_line_payment_tbl => l_line_payment_tbl
803 );
804
805 x_return_status := l_return_status;
806
807 IF g_log_enabled AND
808 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
809 THEN
810 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
811 , G_PKG_NAME
812 , '>> ' || l_program
813 || ': Process Order returned ' || l_return_status
814 || ', Sales Order Line ID = ' || l_line_tbl(1).line_id
815 );
816 END IF;
817
818 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
819 THEN
820 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_SO_ERR');
821 FND_MSG_PUB.Add;
822 /* Bug 7000413 - Start */
823 /* Log the error in the Concurrent Request log if allocation fails */
824 BEGIN
825 SELECT segment1
826 INTO l_order_number
827 FROM po_headers_all poh
828 WHERE EXISTS
829 (SELECT 1 FROM po_line_locations_all poll
830 WHERE poll.line_location_id = p_subcontract_po_shipment_id
831 AND poll.po_header_id = poh.po_header_id);
832
833 SELECT segment1
834 INTO l_sub_comp
838
835 FROM mtl_system_items_b
836 WHERE inventory_item_id = p_item_id
837 AND organization_id = p_tp_organization_id ;
839 fnd_message.set_name('JMF','JMF_SHK_REP_SO_ERROR');
840 fnd_message.set_token('SUB_ORDER', l_order_number );
841 fnd_message.set_token('SUB_COMP', l_sub_comp);
842 l_message := fnd_message.GET();
843 fnd_file.put_line(fnd_file.LOG, l_message);
844 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
845 EXCEPTION
846 WHEN OTHERS THEN
847 NULL; -- Return null if there is an error in fetching the message
848 END;
849 /* Bug 7000413 - End */
850
851
852 END IF;
853
854 -- To get price list id and price from the Sales Order Line
855 -- just created by Process_Order API
856
857 SELECT unit_selling_price,
858 price_list_id
859 INTO l_unit_price,
860 l_price_list_id
861 FROM oe_order_lines_all
862 WHERE line_id = l_line_tbl(1).line_id;
863
864 IF l_price_list_id IS NULL
865 THEN
866 RAISE l_no_price_list_found;
867 END IF;
868
869 IF g_log_enabled AND
870 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
871 THEN
872 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
873 , G_PKG_NAME
874 , '>> ' || l_program || ': From Sales Order Line ID ' || l_line_tbl(1).line_id
875 || ': Price List ID = '|| l_price_list_id
876 || ', Unit Price = ' || l_unit_price
877 );
878 END IF;
879
880 -- Get the currency which the price list is in
881 SELECT currency_code
882 INTO l_currency_code
883 FROM oe_price_lists
884 WHERE price_list_id = l_price_list_id;
885
886 IF g_log_enabled AND
887 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
888 THEN
889 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
890 , G_PKG_NAME
891 , '>> ' || l_program
892 || ': Price List Currency Code = ' || l_currency_code
893 );
894 END IF;
895
896 BEGIN
897
898 -- Bug 5485115: Remove the dependency on OE_PRICE_LIST_LINES, which is a
899 -- synonym on QP_PRICE_LIST_LINES_V that has a where clause condition that
900 -- calls QP_UTIL.Get_Item_Validation_Org, which returns the value of
901 -- the QP: Item Validation Organization profile option.
902 -- The following query was rewritten to select from the based table
903 -- QP_LIST_LINES, instead of the OE_PRICE_LIST_LINES view.
904
905 SELECT qp_price_list_pvt.get_product_uom_code(list_line_id),
906 operand
907 INTO l_price_list_uom,
908 l_component_price
909 FROM qp_list_lines
910 WHERE list_header_id = l_price_list_id
911 AND qp_price_list_pvt.get_inventory_item_id(list_line_id) = p_item_id
912 AND l_ship_date BETWEEN
913 NVL(start_date_active, l_ship_date - 1)
914 AND
915 NVL(end_date_active, l_ship_date + 1);
916
917 EXCEPTION
918 WHEN TOO_MANY_ROWS THEN
919
920 IF NVL(p_action,'C') = 'Q'
921 THEN
922
923 -- Update the shikyu component with the price list id and
924 -- the currency although the pricing UOM and shikyu component
925 -- price could not be obtained
926 UPDATE jmf_shikyu_components
927 SET currency = l_currency_code
928 , price_list_id = l_price_list_id
929 , last_update_date = sysdate
930 , last_updated_by = FND_GLOBAL.user_id
931 , last_update_login = FND_GLOBAL.login_id
932 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
933 AND oem_organization_id = p_oem_organization_id
934 AND shikyu_component_id = p_item_id;
935
936 END IF;
937
938 RAISE l_too_many_effective_prices;
939 END;
940
941 IF g_log_enabled AND
942 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
943 THEN
944 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
945 , G_PKG_NAME
946 , '>> ' || l_program
947 || ': Price List UOM = '|| l_price_list_uom
948 || ', List Price = ' || l_component_price
949 );
950 END IF;
951
952 IF NVL(p_action,'C') = 'C' AND l_unit_price IS NULL
953 THEN
954
955 IF g_log_enabled AND
956 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
957 THEN
958 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
959 , G_PKG_NAME
960 , '>> ' || l_program
961 || ': p_action = ''C'' and Unit Price is NULL: Rollback the created Sales Order'
962 );
963 END IF;
964
965 -- Rollback the creation of the Sales Order if the action
966 -- is Create, but the unit selling price of the created
967 -- Sales Order Line is NULL
968 ROLLBACK to before_process_order;
969
970 RAISE l_null_unit_price;
971
972 END IF; /* IF NVL(p_action,'C') = 'C' AND l_unit_price IS NULL */
973
974 IF NVL(p_action,'C') = 'Q'
975 THEN
976
977 IF g_log_enabled AND
978 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
979 THEN
983 || ': p_action = ''Q'''
980 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
981 , G_PKG_NAME
982 , '>> ' || l_program
984 );
985 END IF;
986
987 -- If the price list line currently effective for the component is
988 -- not defined in the primary uom
989 IF l_primary_uom_code <> l_price_list_uom
990 THEN
991
992 IF g_log_enabled AND
993 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
994 THEN
995 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
996 , G_PKG_NAME
997 , '>> ' || l_program
998 || ': Price List UOM <> Primary UOM: '
999 || 'Getting UOM Conversion Rate from '
1000 || l_primary_uom_code || ' to ' || l_price_list_uom
1001 );
1002 END IF;
1003
1004 -- Fix for Bug 4885422: Get the conversion rate between the primary and
1005 -- secondary UOMs, and then get the unit price of the SHIKYU component
1006 -- in the primary UOM, since the current price is defined in secondary UOM.
1007
1008 l_uom_conversion_rate := JMF_SHIKYU_UTIL.Get_Uom_Conversion_Rate
1009 ( P_from_unit => l_price_list_uom
1010 , P_to_unit => l_primary_uom_code
1011 , P_item_id => p_item_id
1012 );
1013
1014 l_unit_price := l_component_price / l_uom_conversion_rate;
1015
1016 IF g_log_enabled
1017 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1018 THEN
1019 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1020 , G_PKG_NAME
1021 , '>> ' || l_program
1022 || ': l_component_price = ' || l_component_price
1023 || ', l_uom_conversion_rate = ' || l_uom_conversion_rate
1024 || ', l_unit_price = ' || l_unit_price
1025 );
1026 END IF;
1027
1028 ELSE
1029
1030 l_unit_price := l_component_price;
1031
1032 END IF; /* l_primary_uom_code <> l_price_list_uom */
1033
1034 ROLLBACK TO before_process_order; -- get_price;
1035
1036 IF p_subcontract_po_shipment_id IS NOT NULL
1037 THEN
1038
1039 IF g_log_enabled AND
1040 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1041 THEN
1042 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1043 , G_PKG_NAME
1044 , '>> ' || l_program || ': Updating JMF_SHIKYU_COMPONENTS table'
1045 );
1046 END IF;
1047
1048 UPDATE jmf_shikyu_components
1049 SET primary_uom_price = l_unit_price
1050 , primary_uom = l_primary_uom_code
1051 , uom = l_price_list_uom
1052 , currency = l_currency_code
1053 , price_list_id = l_price_list_id
1054 , shikyu_component_price = l_component_price
1055 , last_update_date = sysdate
1056 , last_updated_by = FND_GLOBAL.user_id
1057 , last_update_login = FND_GLOBAL.login_id
1058 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
1059 AND oem_organization_id = p_oem_organization_id
1060 AND shikyu_component_id = p_item_id;
1061
1062 IF g_log_enabled AND
1063 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1064 THEN
1065 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1066 , G_PKG_NAME
1067 , '>> ' || l_program || ': Updated JMF_SHIKYU_COMPONENTS table'
1068 );
1069 END IF;
1070
1071 END IF; /* IF p_subcontract_po_shipment_id IS NOT NULL */
1072
1073 END IF; /* IF NVL(p_action,'C') = 'Q' */
1074
1075 x_order_line_id := l_line_tbl(1).line_id;
1076
1077 IF g_log_enabled AND
1078 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1079 THEN
1080 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1081 , G_PKG_NAME
1082 , '>> ' || l_program || ': Returning x_order_line_id = ' || x_order_line_id
1083 );
1084 END IF;
1085
1086 IF l_client_info_org_id <> l_org_id
1087 THEN
1088 fnd_client_info.set_org_context(TO_CHAR(l_client_info_org_id));
1089
1090 IF g_log_enabled AND
1091 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1092 THEN
1093 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1094 , G_PKG_NAME
1095 , '>> ' || l_program
1096 || ': Setting the Org Context of CLIENT_INFO back to the original value ('
1097 || l_client_info_org_id || ')'
1098 );
1099 END IF;
1100
1101 END IF;
1102
1103 IF g_log_enabled AND
1104 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1105 THEN
1106 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1107 , G_PKG_NAME
1108 , '>> ' || l_program || ': End'
1109 );
1110 END IF;
1111
1112 EXCEPTION
1113
1114 WHEN l_too_many_effective_prices THEN
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116
1117 ROLLBACK TO before_process_order;
1118
1119 IF g_log_enabled AND
1120 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1121 THEN
1122
1123 SELECT concatenated_segments
1124 INTO l_item_number
1125 FROM MTL_SYSTEM_ITEMS_VL
1126 WHERE organization_id = p_oem_organization_id
1127 AND inventory_item_id = p_item_id;
1128
1129 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1130 , G_PKG_NAME
1131 , '>> ' || l_program || ': Too many prices effective for item "' || l_item_number || '"');
1132 END IF;
1133
1134 WHEN l_no_price_list_found THEN
1135 x_return_status := FND_API.G_RET_STS_ERROR;
1136
1137 IF g_log_enabled AND
1138 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1139 THEN
1140
1141 SELECT concatenated_segments
1142 INTO l_item_number
1143 FROM MTL_SYSTEM_ITEMS_VL
1144 WHERE organization_id = p_oem_organization_id
1145 AND inventory_item_id = p_item_id;
1146
1147 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1148 , G_PKG_NAME
1149 , '>> ' || l_program || ': No effective price list found for item "' || l_item_number || '"');
1150 END IF;
1151
1152 WHEN l_null_unit_price THEN
1153 x_return_status := FND_API.G_RET_STS_ERROR;
1154
1155 IF g_log_enabled AND
1156 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1157 THEN
1158
1159 SELECT concatenated_segments
1160 INTO l_item_number
1161 FROM MTL_SYSTEM_ITEMS_VL
1162 WHERE organization_id = p_oem_organization_id
1163 AND inventory_item_id = p_item_id;
1164
1165 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1166 , G_PKG_NAME
1167 , '>> ' || l_program || ': Unit price could not be obtained for '
1168 || l_item_number
1169 || ' from the Price List'
1170 );
1171
1172 IF l_price_list_uom <> l_line_tbl(1).order_quantity_uom
1173 THEN
1174 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1175 , G_PKG_NAME
1176 , '>> ' || l_program || ': Unit of Measure specified for the Replenishment PO ('
1177 || l_line_tbl(1).order_quantity_uom
1178 || ') does not correspond to the Unit of Measure currectly effective ('
1179 || l_price_list_uom
1180 || ') in the Price List'
1181 );
1182 END IF;
1183 END IF;
1184
1185 WHEN l_too_many_project_task_ref THEN
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187
1188 IF g_log_enabled AND
1189 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1190 THEN
1191
1192 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1193 , G_PKG_NAME
1194 , '>> ' || l_program || ': More than one Project and Task reference found'
1195 );
1196 END IF;
1197
1198 WHEN OTHERS THEN
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_SO_ERR');
1201 FND_MSG_PUB.add;
1202
1203 IF g_log_enabled AND
1204 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1205 THEN
1206
1207 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1208 , G_PKG_NAME
1209 , '>> ' || l_program || ': OTHER EXCEPTION: ' || sqlerrm);
1210 END IF;
1211
1212 END Process_Replenishment_SO;
1213
1214 END JMF_SHIKYU_ONT_PVT;
[Home] [Help]