[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_ONT_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_ONT_PVT AS
2 -- $Header: JMFVSKOB.pls 120.30.12020000.2 2012/07/04 07:24:56 ntungare 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;
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;
309 l_Header_price_Att_tbl oe_order_pub.Header_Price_Att_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 --Bugfix 9767052: Adding this variable for debugging purposes.
371 lStmtNo NUMBER;
372
373 l_line_attr_tbl JMF_CUSTOM_HOOK.Line_Attr_Tbl_Type; -- Bug 14150216
374
375 -- Bug 5216720: Should get Project ID and Task ID from the Replenishment
376 -- PO, not the Subcontracting PO
377
378 CURSOR c_project_cur IS
379 SELECT distinct project_id
380 , task_id
381 FROM po_distributions_all
382 WHERE line_location_id = p_replen_po_shipment_id
383 AND project_id IS NOT NULL;
384
385 BEGIN
386
387 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 g_log_enabled := TRUE;
389
390 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
391 , G_PKG_NAME
392 , '>> ' || l_program || ': Start'
393 );
394 END IF;
395
396 IF g_log_enabled AND
397 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
398 THEN
399 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
400 , G_PKG_NAME
401 , '>> ' || l_program
402 || ': p_action = ' || p_action
403 || ', p_subcontract_po_shipment_id = ' || p_subcontract_po_shipment_id
404 || ', p_item_id = ' || p_item_id
405 || ', p_quantity = ' || p_quantity
406 || ', p_replen_po_shipment_id = ' || p_replen_po_shipment_id
407 --Bugfix 9315131: Added debug statements
408 || ', p_oem_organization_id = ' || p_oem_organization_id
409 || ', p_tp_organization_id = ' || p_tp_organization_id
410 );
411 END IF;
412
413 -- Bug 5201694: Should set context to OU specified in the concurrent request,
414 -- not the OU specified in the 'MO: Operating Unit' profile option.
415
416 lStmtNo := 10;
417
418 --l_org_id := FND_PROFILE.VALUE('ORG_ID');
419 l_org_id := mo_global.get_current_org_id;
420
421
422 IF g_log_enabled AND
423 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
424 THEN
425 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
426 , G_PKG_NAME
427 , '>> ' || l_program
428 ||': Org ID from mo_global.get_current_org_id = ' || l_org_id
429 );
430 END IF;
431
432 -- Bug 5209846 : Get the Operating Unit (Org ID) from the Replenishment PO
433 -- Shipment, if mo_global.get_current_org_id returned NULL. This is necessary
434 -- for the Interlock Worker since the Worker Concurrent Program is not MOAC
435 -- enabled (to support more then one OU).
436
437 BEGIN
438
439 lStmtNo := 20;
440
441 IF l_org_id is NULL
442 THEN
443 SELECT org_id
444 INTO l_org_id
445 FROM po_line_locations_all
446 WHERE line_location_id = p_replen_po_shipment_id;
447 END IF;
448
449 IF g_log_enabled AND
450 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
451 THEN
452 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
453 , G_PKG_NAME
454 , '>> ' || l_program ||': Org ID selected from PO_LINE_LOCATIONS_ALL = '
455 || l_org_id
456 );
457 END IF;
458
459 END;
460
461 MO_GLOBAL.set_policy_context('S', l_org_id);
462
463 IF g_log_enabled AND
464 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
465 THEN
466 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
467 , G_PKG_NAME
468 , '>> ' || l_program
469 ||': Org ID from FND_PROFILE = ' || FND_PROFILE.VALUE('ORG_ID')
470 );
471 END IF;
472
473 lStmtNo := 30;
474
475 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
476 INTO l_client_info_org_id
477 FROM DUAL;
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 ||': Org ID from CLIENT_INFO = ' || l_client_info_org_id
486 );
487 END IF;
488
489 IF l_client_info_org_id <> l_org_id
490 THEN
491 fnd_client_info.set_org_context(TO_CHAR(l_org_id));
492
493 IF g_log_enabled AND
494 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
495 THEN
496 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
497 , G_PKG_NAME
498 , '>> ' || l_program
499 || ': Setting the Org Context of CLIENT_INFO to the OU specified for MOAC ('
500 || l_org_id || ')'
501 );
502 END IF;
503
504 END IF;
505
506 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
507 g_log_enabled := TRUE;
508 END IF;
509
510 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
511 l_oe_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
512 l_quantity := p_quantity;
513
514 -- Bugs 5212219: To get the appropriate project and task reference from
515 -- the Replenishment PO
516
517 lStmtNo := 40;
518
519 OPEN c_project_cur;
520 FETCH c_project_cur
521 BULK COLLECT INTO l_project_tbl;
522
523 IF l_project_tbl.COUNT > 1
524 THEN
525
526 RAISE l_too_many_project_task_ref;
527
528 ELSIF l_project_tbl.COUNT = 1
529 THEN
530
531 l_project_id := l_project_tbl(l_project_tbl.FIRST).project_id;
532 l_task_id := l_project_tbl(l_project_tbl.FIRST).task_id;
533
534 ELSE
535
536 l_project_id := NULL;
537 l_task_id := NULL;
538
539 END IF; /* IF l_project_tbl.COUNT > 1 */
540
541 CLOSE c_project_cur;
542
543 IF g_log_enabled AND
544 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
545 THEN
546 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
547 , G_PKG_NAME
548 , '>> ' || l_program || ': Project ID = ' || l_project_id
549 || ', Task ID = ' || l_task_id
550 );
551 END IF;
552
553 lStmtNo := 50;
554
555 -- Get customer id
556 SELECT TO_NUMBER(org_information1)
557 INTO l_header_rec.sold_to_org_id
558 FROM HR_ORGANIZATION_INFORMATION
559 WHERE organization_id = p_tp_organization_id
560 AND org_information_context = 'Customer/Supplier Association';
561
562 IF g_log_enabled AND
563 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
564 THEN
565 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
566 , G_PKG_NAME
567 , '>> ' || l_program
568 ||': Ship To customer id = ' || l_header_rec.sold_to_org_id
569 );
570 END IF;
571
572 lStmtNo := 60;
573
574 -- Bug 4597298
575 -- Remove reference to RA_CUSTOMERS, which have been obsoleted for R12
576 -- The view oe_invoice_to_orgs_v selects HZ_CUST_SITE_USES_ALL.CUST_ACCOUNT_ID
577 -- as customer_id, and HZ_CUST_SITE_USES_ALL.CUST_ACCOUNT_ID is just a foreign
578 -- key to HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID. So it should be safe to just
579 -- remove the join with RA_CUSTOMERS, since the replacement object for getting
580 -- the CUSTOMER_ID of RA_CUSTOMERS is HZ_CUST_ACCOUNTS.
581
582 -- Get Bill-To customer site id
583 -- Bug 5201694
584
585 -- Bug 5649321: Added an additional where clause condition to get only the
586 -- active Bill-To site. This avoids a 'More than one row fetched'
587 -- exception being thrown if there exists any inactive sites.
588
589 SELECT site.site_use_id
590 INTO l_header_rec.invoice_to_org_id
591 FROM hz_cust_site_uses_all site,
592 hz_cust_acct_sites_all acct_site
593 WHERE site.cust_acct_site_id = acct_site.cust_acct_site_id
594 AND site.site_use_code = 'BILL_TO'
595 AND site.org_id = acct_site.org_id
596 AND acct_site.cust_account_id = l_header_rec.sold_to_org_id
597 AND site.status = 'A';
598
599 --Begin Bugfix 10184466
600 lStmtNo := 65;
601
602 SELECT TO_NUMBER(org_information2)
603 INTO l_header_rec.ship_to_org_id
604 FROM HR_ORGANIZATION_INFORMATION
605 WHERE organization_id = p_tp_organization_id
606 AND org_information_context = 'Customer/Supplier Association';
607 --End Bugfix 10184466
608
609 IF g_log_enabled AND
610 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
611 THEN
612 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
613 , G_PKG_NAME
614 , '>> ' || l_program
615 || ': Bill To org id = ' || l_header_rec.invoice_to_org_id
616 || ': Ship To org id = ' || l_header_rec.ship_to_org_id
617 );
618 END IF;
619
620 lStmtNo := 70;
621 -- Get the SHIKYU default order type from the shikyu-enabled Shipping Network
622 -- from the OEM to the TP
623 SELECT shikyu_default_order_type_id
624 INTO l_header_rec.order_type_id
625 FROM mtl_interorg_parameters
626 WHERE from_organization_id = p_oem_organization_id
627 AND to_organization_id = p_tp_organization_id;
628
629 lStmtNo := 80;
630 -- Get the default outbound line type of the SHIKYU default order type
631 SELECT default_outbound_line_type_id
632 INTO l_line_type_id
633 FROM oe_transaction_Types_all
634 WHERE transaction_type_id = l_header_rec.order_type_id;
635
636 IF g_log_enabled AND
637 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
638 THEN
639 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
640 , G_PKG_NAME
641 , '>> ' || l_program
642 || ': SHIKYU Default Order Type = ' || l_header_rec.order_type_id
643 || ', Default Outbound Line Type = ' || l_line_type_id
644 );
645 END IF;
646
647 BEGIN
648 IF p_replen_po_shipment_id IS NOT NULL
649 THEN
650
651 lStmtNo := 90;
652
653 SELECT NVL(need_by_date, promised_date)
654 INTO l_replen_po_need_by_date
655 FROM po_line_locations_all
656 WHERE line_location_id = p_replen_po_shipment_id;
657
658 IF g_log_enabled AND
659 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
660 THEN
661 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
662 , G_PKG_NAME
663 , '>> ' || l_program
664 || ': Replenishment PO Need By Date = ' || l_replen_po_need_by_date
665 );
666 END IF;
667
668 ELSE
669 l_replen_po_need_by_date := NULL;
670 END IF;
671 EXCEPTION
672 WHEN NO_DATA_FOUND THEN
673 l_replen_po_need_by_date := NULL;
674 END;
675
676 lStmtNo := 100;
677
678 Calculate_Ship_Date
679 ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
680 , p_component_item_id => p_item_id
681 , p_oem_organization_id => p_oem_organization_id
682 , p_tp_organization_id => p_tp_organization_id
683 , p_quantity => l_quantity
684 , p_need_by_date => l_replen_po_need_by_date
685 , x_ship_date => l_ship_date
686 );
687
688 IF g_log_enabled AND
689 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
690 THEN
691 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
692 , G_PKG_NAME
693 , '>> ' || l_program || ': Ship date = ' || l_ship_date
694 );
695 END IF;
696
697 l_customer_id := l_header_rec.sold_to_org_id;
698 l_bill_to_id := l_header_rec.invoice_to_org_id;
699 l_order_type_id := l_header_rec.order_type_id;
700 l_header_rec.ship_from_org_id :=
701 p_oem_organization_id;
702
703 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
704 l_header_rec.transaction_phase_code:= 'F';
705
706 -- SO Line record
707 l_line_tbl(1) := l_oe_line_rec;
708 l_line_tbl(1).ordered_quantity := p_quantity;
709 l_line_tbl(1).inventory_item_id := p_item_id;
710 l_line_tbl(1).schedule_ship_Date := l_ship_date;
711 l_line_tbl(1).request_date := l_ship_date;
712 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
713 l_line_tbl(1).line_type_id := l_line_type_id;
714 l_line_tbl(1).project_id := l_project_id;
715 l_line_tbl(1).task_id := l_task_id;
716
717 IF g_log_enabled AND
718 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
719 THEN
720 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
721 , G_PKG_NAME
722 , '>> ' || l_program || ': Parameters passing to Process Order:'
723 );
724 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
725 , G_PKG_NAME
726 , '>> ' || l_program || ': Ordered_quantity = ' || p_quantity
727 || ', Item = ' || p_item_id
728 || ', Ship Date = ' || l_ship_date
729 || ', Line type = ' || l_line_type_id
730 );
731 END IF;
732
733 lStmtNo := 110;
734
735 SELECT primary_uom_code
736 INTO l_primary_uom_code
737 FROM mtl_system_items_b
738 WHERE inventory_item_id = p_item_id
739 AND organization_id = p_oem_organization_id;
740
741 IF g_log_enabled AND
742 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
743 THEN
744 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
745 , G_PKG_NAME
746 , '>> ' || l_program
747 ||': Primary UOM = '|| l_primary_uom_code
748 );
749 END IF;
750
751 IF NVL(p_action,'C') = 'C'
752 THEN
753
754 IF g_log_enabled AND
755 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
756 THEN
757 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
758 , G_PKG_NAME
759 , '>> ' || l_program
760 || ': p_action = ''C'': Getting UOM from Replenishment PO Shipment'
761 );
762 END IF;
763
764 IF p_replen_po_shipment_id IS NOT NULL
765 THEN
766
767 lStmtNo := 120;
768
769 -- Get the UOM and quantity from the Replenishment PO Shipment to
770 -- which the Replenishment SO Line to be created will be connected
771
772 SELECT muomvl.uom_code,
773 plla.quantity
774 INTO l_price_list_uom,
775 l_quantity
776 FROM po_line_locations_all plla,
777 mtl_units_of_measure_vl muomvl
778 WHERE plla.line_location_id = p_replen_po_shipment_id
779 AND plla.unit_meas_lookup_code = muomvl.unit_of_measure;
780
781 ELSE
782
783 -- Assume primary UOM is to be used if no reference to a Replenishment PO is provided
784
785 l_price_list_uom := l_primary_uom_code;
786
787 END IF; /* IF p_replen_po_shipment_id IS NOT NULL */
788
789 IF g_log_enabled AND
790 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
791 THEN
792 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
793 , G_PKG_NAME
794 , '>> ' || l_program
795 || ': l_price_list_uom = ' || l_price_list_uom
796 || ', l_quantity = ' || l_quantity
797 );
798 END IF;
799
800 l_line_tbl(1).visible_demand_flag := 'Y';
801 l_line_tbl(1).order_quantity_uom := l_price_list_uom;
802 l_line_tbl(1).ordered_quantity := l_quantity;
803
804 -- Setting the values of the action_request_tbl in order to book the
805 -- Sales Order
806 l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
807 l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
808
809 END IF; /* IF NVL(p_action,'C') = 'C' */
810
811
812 /* START of Bug 14150216 */
813
814 -- Calling the Custom Hook allowing the user to default
815 -- the DFF attributes at header level.
816 JMF_CUSTOM_HOOK.Default_OE_Hdr_Attr
817 ( p_api_version_number => 1.0
818 , p_init_msg_list => FND_API.G_TRUE
819 , p_x_header_Rec => l_header_rec
820 , x_attribute1 => l_header_rec.attribute1
821 , x_attribute2 => l_header_rec.attribute2
822 , x_attribute3 => l_header_rec.attribute3
823 , x_attribute4 => l_header_rec.attribute4
824 , x_attribute5 => l_header_rec.attribute5
825 , x_attribute6 => l_header_rec.attribute6
826 , x_attribute7 => l_header_rec.attribute7
827 , x_attribute8 => l_header_rec.attribute8
828 , x_attribute9 => l_header_rec.attribute9
829 , x_attribute10 => l_header_rec.attribute10
830 , x_attribute11 => l_header_rec.attribute11
831 , x_attribute12 => l_header_rec.attribute12
832 , x_attribute13 => l_header_rec.attribute13
833 , x_attribute14 => l_header_rec.attribute14
834 , x_attribute15 => l_header_rec.attribute15
835 , x_attribute16 => l_header_rec.attribute16
836 , x_attribute17 => l_header_rec.attribute17
837 , x_attribute18 => l_header_rec.attribute18
838 , x_attribute19 => l_header_rec.attribute19
839 , x_attribute20 => l_header_rec.attribute20
840 , x_return_status => l_return_status
841 , x_msg_count => l_msg_count
842 , x_msg_data => l_msg_data
843 );
844
845 x_return_status := l_return_status;
846
847 IF g_log_enabled
848 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
849 THEN
850 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
851 , G_PKG_NAME
852 , '>> ' || l_program
853 || ': Default_OE_Line_Attr returned ' || l_return_status
854 || ', Sales Order Line ID = ' || l_line_tbl(1).line_id
855 );
856 END IF;
857
858 -- Calling the Custom Hook allowing the user to default
859 -- the DFF attributes at line level.
860 JMF_CUSTOM_HOOK.Default_OE_Line_Attr
861 ( p_api_version_number => 1.0
862 , p_init_msg_list => FND_API.G_TRUE
863 , p_x_line_tbl => l_line_tbl
864 , x_line_attr_tbl => l_line_attr_tbl
865 , x_return_status => l_return_status
866 , x_msg_count => l_msg_count
867 , x_msg_data => l_msg_data
868 );
869
870 l_line_tbl(1).attribute1 := l_line_attr_tbl(1).attribute1;
871 l_line_tbl(1).attribute2 := l_line_attr_tbl(1).attribute2;
872 l_line_tbl(1).attribute3 := l_line_attr_tbl(1).attribute3;
873 l_line_tbl(1).attribute4 := l_line_attr_tbl(1).attribute4;
874 l_line_tbl(1).attribute5 := l_line_attr_tbl(1).attribute5;
875 l_line_tbl(1).attribute6 := l_line_attr_tbl(1).attribute6;
876 l_line_tbl(1).attribute7 := l_line_attr_tbl(1).attribute7;
877 l_line_tbl(1).attribute8 := l_line_attr_tbl(1).attribute8;
878 l_line_tbl(1).attribute9 := l_line_attr_tbl(1).attribute9;
879 l_line_tbl(1).attribute10 := l_line_attr_tbl(1).attribute10;
880 l_line_tbl(1).attribute11 := l_line_attr_tbl(1).attribute11;
881 l_line_tbl(1).attribute12 := l_line_attr_tbl(1).attribute12;
882 l_line_tbl(1).attribute13 := l_line_attr_tbl(1).attribute13;
883 l_line_tbl(1).attribute14 := l_line_attr_tbl(1).attribute14;
884 l_line_tbl(1).attribute15 := l_line_attr_tbl(1).attribute15;
885 l_line_tbl(1).attribute16 := l_line_attr_tbl(1).attribute16;
886 l_line_tbl(1).attribute17 := l_line_attr_tbl(1).attribute17;
887 l_line_tbl(1).attribute18 := l_line_attr_tbl(1).attribute18;
888 l_line_tbl(1).attribute19 := l_line_attr_tbl(1).attribute19;
889 l_line_tbl(1).attribute20 := l_line_attr_tbl(1).attribute20;
890
891 x_return_status := l_return_status;
892
893 IF g_log_enabled
894 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
895 THEN
896 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
897 , G_PKG_NAME
898 , '>> ' || l_program
899 || ': Default_OE_Line_Attr returned ' || l_return_status
900 || ', Sales Order Header ID = ' || l_header_rec.header_id
901 );
902 END IF;
903
904 /* END of Bug 14150216 */
905
906 IF g_log_enabled AND
907 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
908 THEN
909 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
910 , G_PKG_NAME
911 , '>> ' || l_program || ': Calling Process Order, Creating SAVEPOINT before_process_order'
912 );
913 END IF;
914
915 lStmtNo := 130;
916
917 SAVEPOINT before_process_order;
918
919 OE_Order_PVT.Process_order
920 ( p_api_version_number => 1.0
921 , p_init_msg_list => FND_API.G_TRUE
922 , x_return_status => l_return_status
923 , x_msg_count => l_msg_count
924 , x_msg_data => l_msg_data
925 , p_control_rec => l_control_rec
926 , p_x_header_Rec => l_header_rec
927 , p_x_line_tbl => l_line_tbl
928 , p_x_action_request_tbl => l_action_request_tbl
929 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
930 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
931 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
932 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
933 , p_x_Lot_Serial_tbl => l_x_lot_serial_tbl
934 , p_x_Header_price_Att_tbl => l_Header_price_Att_tbl
935 , p_x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
936 , p_x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
937 , p_x_Line_price_Att_tbl => l_Line_price_Att_tbl
938 , p_x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
939 , p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
940 , p_x_header_payment_tbl => l_hdr_payment_tbl
941 , p_x_line_payment_tbl => l_line_payment_tbl
942 );
943
944 x_return_status := l_return_status;
945
946 IF g_log_enabled AND
947 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
948 THEN
949 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
950 , G_PKG_NAME
951 , '>> ' || l_program
952 || ': Process Order returned ' || l_return_status
953 || ', Sales Order Line ID = ' || l_line_tbl(1).line_id
954 );
955 END IF;
956
957 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
958 THEN
959 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_SO_ERR');
960 FND_MSG_PUB.Add;
961 /* Bug 7000413 - Start */
962 /* Log the error in the Concurrent Request log if allocation fails */
963 BEGIN
964
965 lStmtNo := 140;
966
967 SELECT segment1
968 INTO l_order_number
969 FROM po_headers_all poh
970 WHERE EXISTS
971 (SELECT 1 FROM po_line_locations_all poll
972 WHERE poll.line_location_id = p_subcontract_po_shipment_id
973 AND poll.po_header_id = poh.po_header_id);
974
975 lStmtNo := 150;
976
977 SELECT segment1
978 INTO l_sub_comp
979 FROM mtl_system_items_b
980 WHERE inventory_item_id = p_item_id
981 AND organization_id = p_tp_organization_id ;
982
983 fnd_message.set_name('JMF','JMF_SHK_REP_SO_ERROR');
984 fnd_message.set_token('SUB_ORDER', l_order_number );
985 fnd_message.set_token('SUB_COMP', l_sub_comp);
986 l_message := fnd_message.GET();
987 fnd_file.put_line(fnd_file.LOG, l_message);
988 l_status_flag := FND_CONCURRENT.set_completion_status('WARNING',NULL);
989 EXCEPTION
990 WHEN OTHERS THEN
991 NULL; -- Return null if there is an error in fetching the message
992 END;
993 /* Bug 7000413 - End */
994
995
996 END IF;
997
998 lStmtNo := 160;
999 -- To get price list id and price from the Sales Order Line
1000 -- just created by Process_Order API
1001
1002 SELECT unit_selling_price,
1003 price_list_id
1004 INTO l_unit_price,
1005 l_price_list_id
1006 FROM oe_order_lines_all
1007 WHERE line_id = l_line_tbl(1).line_id;
1008
1009 IF l_price_list_id IS NULL
1010 THEN
1011 RAISE l_no_price_list_found;
1012 END IF;
1013
1014 IF g_log_enabled AND
1015 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1016 THEN
1017 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1018 , G_PKG_NAME
1019 , '>> ' || l_program || ': From Sales Order Line ID ' || l_line_tbl(1).line_id
1020 || ': Price List ID = '|| l_price_list_id
1021 || ', Unit Price = ' || l_unit_price
1022 );
1023 END IF;
1024
1025 lStmtNo := 170;
1026
1027 -- Get the currency which the price list is in
1028 SELECT currency_code
1029 INTO l_currency_code
1030 FROM oe_price_lists
1031 WHERE price_list_id = l_price_list_id;
1032
1033 IF g_log_enabled AND
1034 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1035 THEN
1036 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1037 , G_PKG_NAME
1038 , '>> ' || l_program
1039 || ': Price List Currency Code = ' || l_currency_code
1040 );
1041 END IF;
1042
1043 BEGIN
1044
1045 -- Bug 5485115: Remove the dependency on OE_PRICE_LIST_LINES, which is a
1046 -- synonym on QP_PRICE_LIST_LINES_V that has a where clause condition that
1047 -- calls QP_UTIL.Get_Item_Validation_Org, which returns the value of
1048 -- the QP: Item Validation Organization profile option.
1049 -- The following query was rewritten to select from the based table
1050 -- QP_LIST_LINES, instead of the OE_PRICE_LIST_LINES view.
1051
1052 /* Commenting as part of bugfix 9315131. Rewriting this sql.
1053 SELECT qp_price_list_pvt.get_product_uom_code(list_line_id),
1054 operand
1055 INTO l_price_list_uom,
1056 l_component_price
1057 FROM qp_list_lines
1058 WHERE list_header_id = l_price_list_id
1059 AND qp_price_list_pvt.get_inventory_item_id(list_line_id) = p_item_id
1060 AND l_ship_date BETWEEN
1061 NVL(start_date_active, l_ship_date - 1)
1062 AND
1063 NVL(end_date_active, l_ship_date + 1);
1064 */
1065
1066 lStmtNo := 180;
1067
1068 SELECT qp_price_list_pvt.get_product_uom_code(qp.list_line_id),
1069 qp.operand
1070 INTO l_price_list_uom,
1071 l_component_price
1072 FROM qp_list_lines qp
1073 WHERE qp.list_header_id = l_price_list_id
1074 AND EXISTS (SELECT 1
1075 FROM qp_pricing_attributes qpa
1076 WHERE qpa.list_line_id = qp.list_line_id
1077 AND qpa.product_attribute_context = 'ITEM'
1078 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
1079 AND to_number(product_attr_value) = p_item_id
1080 AND ROWNUM = 1)
1081 AND l_ship_date BETWEEN
1082 NVL(start_date_active, l_ship_date - 1)
1083 AND
1084 NVL(end_date_active, l_ship_date + 1);
1085
1086 EXCEPTION
1087 WHEN TOO_MANY_ROWS THEN
1088
1089 IF NVL(p_action,'C') = 'Q'
1090 THEN
1091
1092 -- Update the shikyu component with the price list id and
1093 -- the currency although the pricing UOM and shikyu component
1094 -- price could not be obtained
1095 UPDATE jmf_shikyu_components
1096 SET currency = l_currency_code
1097 , price_list_id = l_price_list_id
1098 , last_update_date = sysdate
1099 , last_updated_by = FND_GLOBAL.user_id
1100 , last_update_login = FND_GLOBAL.login_id
1101 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
1102 AND oem_organization_id = p_oem_organization_id
1103 AND shikyu_component_id = p_item_id;
1104
1105 --Debugging changes for bug 9315131
1106 IF g_log_enabled AND
1107 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1108 THEN
1109 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1110 , G_PKG_NAME
1111 , '>> ' || l_program
1112 || ': New Msg: After updating jmf_shikyu_components for shipment:' || p_subcontract_po_shipment_id
1113 || 'p_item_id:' || p_item_id
1114 );
1115 END IF;
1116
1117 END IF;
1118
1119 RAISE l_too_many_effective_prices;
1120 END;
1121
1122 IF g_log_enabled AND
1123 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1124 THEN
1125 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1126 , G_PKG_NAME
1127 , '>> ' || l_program
1128 || ': Price List UOM = '|| l_price_list_uom
1129 || ', List Price = ' || l_component_price
1130 );
1131 END IF;
1132
1133 IF NVL(p_action,'C') = 'C' AND l_unit_price IS NULL
1134 THEN
1135
1136 IF g_log_enabled AND
1137 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1138 THEN
1139 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1140 , G_PKG_NAME
1141 , '>> ' || l_program
1142 || ': p_action = ''C'' and Unit Price is NULL: Rollback the created Sales Order'
1143 );
1144 END IF;
1145
1146 -- Rollback the creation of the Sales Order if the action
1147 -- is Create, but the unit selling price of the created
1148 -- Sales Order Line is NULL
1149 ROLLBACK to before_process_order;
1150
1151 RAISE l_null_unit_price;
1152
1153 END IF; /* IF NVL(p_action,'C') = 'C' AND l_unit_price IS NULL */
1154
1155 IF NVL(p_action,'C') = 'Q'
1156 THEN
1157
1158 IF g_log_enabled AND
1159 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1160 THEN
1161 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1162 , G_PKG_NAME
1163 , '>> ' || l_program
1164 || ': p_action = ''Q'''
1165 );
1166 END IF;
1167
1168 -- If the price list line currently effective for the component is
1169 -- not defined in the primary uom
1170 IF l_primary_uom_code <> l_price_list_uom
1171 THEN
1172
1173 IF g_log_enabled AND
1174 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1175 THEN
1176 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1177 , G_PKG_NAME
1178 , '>> ' || l_program
1179 || ': Price List UOM <> Primary UOM: '
1180 || 'Getting UOM Conversion Rate from '
1181 || l_primary_uom_code || ' to ' || l_price_list_uom
1182 );
1183 END IF;
1184
1185 lStmtNo := 190;
1186
1187 -- Fix for Bug 4885422: Get the conversion rate between the primary and
1188 -- secondary UOMs, and then get the unit price of the SHIKYU component
1189 -- in the primary UOM, since the current price is defined in secondary UOM.
1190
1191 l_uom_conversion_rate := JMF_SHIKYU_UTIL.Get_Uom_Conversion_Rate
1192 ( P_from_unit => l_price_list_uom
1193 , P_to_unit => l_primary_uom_code
1194 , P_item_id => p_item_id
1195 );
1196
1197 l_unit_price := l_component_price / l_uom_conversion_rate;
1198
1199 IF g_log_enabled
1200 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1201 THEN
1202 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1203 , G_PKG_NAME
1204 , '>> ' || l_program
1205 || ': l_component_price = ' || l_component_price
1206 || ', l_uom_conversion_rate = ' || l_uom_conversion_rate
1207 || ', l_unit_price = ' || l_unit_price
1208 );
1209 END IF;
1210
1211 ELSE
1212
1213 l_unit_price := l_component_price;
1214
1215 END IF; /* l_primary_uom_code <> l_price_list_uom */
1216
1217 ROLLBACK TO before_process_order; -- get_price;
1218
1219 IF p_subcontract_po_shipment_id IS NOT NULL
1220 THEN
1221
1222 IF g_log_enabled AND
1223 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1224 THEN
1225 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1226 , G_PKG_NAME
1227 , '>> ' || l_program || ': Updating JMF_SHIKYU_COMPONENTS table'
1228 );
1229 END IF;
1230
1231 lStmtNo := 200;
1232
1233 UPDATE jmf_shikyu_components
1234 SET primary_uom_price = l_unit_price
1235 , primary_uom = l_primary_uom_code
1236 , uom = l_price_list_uom
1237 , currency = l_currency_code
1238 , price_list_id = l_price_list_id
1239 , shikyu_component_price = l_component_price
1240 , last_update_date = sysdate
1241 , last_updated_by = FND_GLOBAL.user_id
1242 , last_update_login = FND_GLOBAL.login_id
1243 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
1244 AND oem_organization_id = p_oem_organization_id
1245 AND shikyu_component_id = p_item_id;
1246
1247 IF g_log_enabled AND
1248 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1249 THEN
1250 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1251 , G_PKG_NAME
1252 , '>> ' || l_program || ': Updated JMF_SHIKYU_COMPONENTS table'
1253 );
1254 END IF;
1255
1256 END IF; /* IF p_subcontract_po_shipment_id IS NOT NULL */
1257
1258 END IF; /* IF NVL(p_action,'C') = 'Q' */
1259
1260 x_order_line_id := l_line_tbl(1).line_id;
1261
1262 IF g_log_enabled AND
1263 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1264 THEN
1265 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1266 , G_PKG_NAME
1267 , '>> ' || l_program || ': Returning x_order_line_id = ' || x_order_line_id
1268 );
1269 END IF;
1270
1271 IF l_client_info_org_id <> l_org_id
1272 THEN
1273 fnd_client_info.set_org_context(TO_CHAR(l_client_info_org_id));
1274
1275 IF g_log_enabled AND
1276 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1277 THEN
1278 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1279 , G_PKG_NAME
1280 , '>> ' || l_program
1281 || ': Setting the Org Context of CLIENT_INFO back to the original value ('
1282 || l_client_info_org_id || ')'
1283 );
1284 END IF;
1285
1286 END IF;
1287
1288 IF g_log_enabled AND
1289 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1290 THEN
1291 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1292 , G_PKG_NAME
1293 , '>> ' || l_program || ': End'
1294 );
1295 END IF;
1296
1297 EXCEPTION
1298
1299 WHEN l_too_many_effective_prices THEN
1300 x_return_status := FND_API.G_RET_STS_ERROR;
1301
1302 ROLLBACK TO before_process_order;
1303
1304 IF g_log_enabled AND
1305 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1306 THEN
1307
1308 SELECT concatenated_segments
1309 INTO l_item_number
1310 FROM MTL_SYSTEM_ITEMS_VL
1311 WHERE organization_id = p_oem_organization_id
1312 AND inventory_item_id = p_item_id;
1313
1314 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1315 , G_PKG_NAME
1316 , '>> ' || l_program || ': Too many prices effective for item "' || l_item_number || '"');
1317 END IF;
1318
1319 WHEN l_no_price_list_found THEN
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321
1322 --Bugfix 9767052
1323 ROLLBACK TO before_process_order;
1324
1325 IF g_log_enabled AND
1326 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1327 THEN
1328
1329 SELECT concatenated_segments
1330 INTO l_item_number
1331 FROM MTL_SYSTEM_ITEMS_VL
1332 WHERE organization_id = p_oem_organization_id
1333 AND inventory_item_id = p_item_id;
1334
1335 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1336 , G_PKG_NAME
1337 , '>> ' || l_program || ': No effective price list found for item "' || l_item_number || '"');
1338 END IF;
1339
1340 WHEN l_null_unit_price THEN
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342
1343 --Bugfix 9767052
1344 ROLLBACK TO before_process_order;
1345
1346 IF g_log_enabled AND
1347 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1348 THEN
1349
1350 SELECT concatenated_segments
1351 INTO l_item_number
1352 FROM MTL_SYSTEM_ITEMS_VL
1353 WHERE organization_id = p_oem_organization_id
1354 AND inventory_item_id = p_item_id;
1355
1356 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1357 , G_PKG_NAME
1358 , '>> ' || l_program || ': Unit price could not be obtained for '
1359 || l_item_number
1360 || ' from the Price List'
1361 );
1362
1363 IF l_price_list_uom <> l_line_tbl(1).order_quantity_uom
1364 THEN
1365 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1366 , G_PKG_NAME
1367 , '>> ' || l_program || ': Unit of Measure specified for the Replenishment PO ('
1368 || l_line_tbl(1).order_quantity_uom
1369 || ') does not correspond to the Unit of Measure currectly effective ('
1370 || l_price_list_uom
1371 || ') in the Price List'
1372 );
1373 END IF;
1374 END IF;
1375
1376 WHEN l_too_many_project_task_ref THEN
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378
1379 --Bugfix 9767052
1380 ROLLBACK TO before_process_order;
1381
1382 IF g_log_enabled AND
1383 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1384 THEN
1385
1386 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1387 , G_PKG_NAME
1388 , '>> ' || l_program || ': More than one Project and Task reference found'
1389 );
1390 END IF;
1391
1392 WHEN OTHERS THEN
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394
1395 --Bugfix 9767052
1396 ROLLBACK TO before_process_order;
1397
1398 FND_MESSAGE.set_name('JMF', 'JMF_SHK_REPLENISH_SO_ERR');
1399 FND_MSG_PUB.add;
1400
1401 IF g_log_enabled AND
1402 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1403 THEN
1404
1405 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1406 , G_PKG_NAME
1407 , '>> ' || l_program || ': OTHER EXCEPTION at Stmt Number:' || lStmtNo || ': ' || sqlerrm);
1408 END IF;
1409
1410 END Process_Replenishment_SO;
1411
1412 END JMF_SHIKYU_ONT_PVT;