1 PACKAGE BODY PO_CREATE_ISO AS
2 /* $Header: POXCISOB.pls 120.15.12020000.4 2013/03/29 10:31:57 rkandima ship $*/
3 /*===========================================================================
4 PACKAGE NAME: PO_CREATE_ISO
5
6 DESCRIPTION: Create Internal Sales Orders Concurrent program
7 subroutine.
8 This routine will take Approved requisitions which are
9 to be sourced from Inventory and create corresponding
10 Internal Sales Orders in the OrderImport interface
11 tables of Oracle Order Entry. This routine will be run
12 on a cyclic basis to pick up all requisitions ready for
13 transfer to OrderImport
14
15 CLIENT/SERVER: Server
16
17 LIBRARY NAME NONE
18
19 OWNER:
20
21 PUBLIC FUNCTIONS
22
23 NOTES
24
25 ==============================================================================*/
26
27
28 /*=============================================================================
29 Procedure Name: LOAD_OM_INTERFACE()
30 Desc: Create Internal Sales Orders Subroutine
31 Args: None
32 Reqs:
33 Mods:
34 Algr:
35 Step 1: Set transferred_to_oe_flag to 'I' in po_requisition_lines
36 for all Reqs that are approved and have atleast one line with
37 source_type as INVENTORY.
38 Step 2: Open the Cursor that has all headers with TRANSFERRED_TO_OE_FLAG
39 as I.
40 Step 3: For every operating unit change, get the OPUNIT details.
41 If no detail found, set the transferred_to_oe_flag to 'E'
42 for those headers.
43 Step 4: insert into oe_headers_iface_all from po_requisition_headers
44 Step 5: For each row
45 insert into oe_lines_iface_all from po_requisition_lines
46 Step 6: Update the transferred_to_oe_flag to 'Y' for all 'I' ones and
47 to 'N' for all 'E' ones.
48 Step 7: return
49 ==============================================================================*/
50 PROCEDURE LOAD_OM_INTERFACE(
51 errbuf out NOCOPY varchar2,
52 retcode out NOCOPY number,
53 p_req_header_id number default null)
54 IS
55 l_currency_code VARCHAR2(16);
56 l_ot_id NUMBER;
57 l_os_id NUMBER;
58 l_pr_id NUMBER;
59 l_ac_id NUMBER;
60 l_ir_id NUMBER;
61 l_req_hdr_id NUMBER;
62 l_req_hdr_id_prev NUMBER;
63 l_req_line_id NUMBER;
64 l_op_unit_id NUMBER;
65 l_op_unit_id_prev NUMBER;
66 l_error_flag VARCHAR2(1) ;
67 x_pjm_status VARCHAR2(1) := 'N';
68
69 --Bug 12576879
70 l_req_line_number NUMBER;
71
72 -- Bug 2873877 START
73 l_val_proj_result VARCHAR(1);
74 l_val_proj_error_code VARCHAR2(80);
75 l_dest_type_code PO_REQUISITION_LINES.destination_type_code%TYPE;
76 l_source_org_id PO_REQUISITION_LINES.source_organization_id%TYPE;
77 l_need_by_date PO_REQUISITION_LINES.need_by_date%TYPE;
78 l_project_id PO_REQ_DISTRIBUTIONS.project_id%TYPE;
79 l_task_id PO_REQ_DISTRIBUTIONS.task_id%TYPE;
80 -- Bug 2873877 END
81
82 --<INVCONV R12 START>
83 l_dest_secondary_quantity PO_REQUISITION_LINES.SECONDARY_QUANTITY%TYPE := NULL;
84 l_dest_secondary_unit PO_REQUISITION_LINES.SECONDARY_UNIT_OF_MEASURE%TYPE := NULL;
85 l_item_id PO_REQUISITION_LINES.ITEM_ID%TYPE := NULL;
86 l_source_secondary_quantity PO_REQUISITION_LINES.SECONDARY_QUANTITY%TYPE := NULL;
87 l_source_secondary_uom MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE := NULL;
88 l_source_secondary_unit PO_REQUISITION_LINES.SECONDARY_UNIT_OF_MEASURE%TYPE := NULL;
89 --<INVCONV R12 END>
90
91 l_pjm_ou_id FINANCIALS_SYSTEM_PARAMS_ALL.org_id%TYPE; --< Bug 3265539 >
92
93 -- begin Bug 3249134
94 l_prec_currency_code varchar2(15) := NULL;
95 l_precision number := NULL;
96 l_ext_precision number := NULL;
97 l_min_unit number := NULL;
98 -- end Bug 3249134
99
100 l_customer_id number := NULL; -- Bug 3365408
101 l_sold_to_org_id NUMBER :=NULL;
102 l_prev_sold_to_org_id NUMBER :=NULL;
103 --bug7699084 Defined a table to hold all the req header id's that gets processed,
104 --So that the transferred_to_oe_flag of the line can be quickly processed
105 l_req_hdr_id_tbl po_tbl_number := PO_TBL_NUMBER();
106 tbl_index NUMBER :=0;
107 --bug7699084
108
109 --BUG10037733 variable definitions.
110 l_unit_list_price number;
111 l_unit_selling_price number;
112 l_conversion_date PO_REQ_DISTRIBUTIONS.gl_encumbered_date%TYPE;
113 l_sob_id FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
114 l_conversion_type PO_SYSTEM_PARAMETERS.default_rate_type%TYPE;
115 l_rate NUMBER;
116 l_source_currency_code VARCHAR2(15);
117 l_dest_currency_code VARCHAR2(15);
118 --BUG10037733
119
120 --#bug 12816938 variable definitions.
121 l_org_id PO_REQUISITION_LINES.org_id%TYPE;
122
123 /* Bug# 1644637
124 Added conditions in where clause to check if the Line is INVENTORY sourced
125 and the line is not Cancelled or FINALLY CLOSED.
126 */
127
128 /* bug 16544597 : reverting the changes done as part of 14175712 as they are
129 causing functional regression also modifying query to improve performance*/
130
131 CURSOR REQ_LINES IS
132 select /*+ leading(HDR) index(HDR PO_REQUISITION_HEADERS_N4) USE_NL(HDR
133 LIN PLA)*/
134 nvl(org.operating_unit,-1),
135 hdr.requisition_header_id,
136 lin.requisition_line_id,
137 Nvl((select PLA.CUSTOMER_ID
138 from PO_LOCATION_ASSOCIATIONS_ALL PLA
139 where pla.location_id = lin.deliver_to_location_id
140 AND nvl(PLA.ORG_ID,-1) = nvl(org.operating_unit,-1)
141 AND rownum=1),-1) customer_id --bug 8692047
142 from po_requisition_lines lin,
143 po_requisition_headers hdr,
144 org_organization_definitions org
145 where lin.requisition_header_id = hdr.requisition_header_id
146 and lin.source_organization_id = org.organization_id
147 and hdr.transferred_to_oe_flag = 'I'
148 and lin.source_type_code = 'INVENTORY'
149 and nvl(lin.cancel_flag,'N') = 'N'
150 and nvl(lin.closed_code,'OPEN') <> 'FINALLY CLOSED'
151 order by org.operating_unit,
152 hdr.requisition_header_id,
153 CUSTOMER_ID,
154 lin.line_num;
155
156 BEGIN
157
158 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Begin create internal sales order');
159
160 BEGIN
161
162 /* Select all requisition headers which have been approved since
163 last run (or =req_id)
164 We are setting the transferred_to_oe_flag to N for externally
165 sourced reqs as well
166 so that the program does not have to sort through these rows
167 again next time */
168
169 Fnd_File.Put_Line(FND_FILE.LOG, 'Updating Req Headers');
170
171 IF p_req_header_id is null then
172
173 /* Bug 2630523 Performance of the following SQL will be good if it choose
174 Hash Join. So added the HASH_SJ hint to improve the performance */
175
176 /*UPDATE PO_REQUISITION_HEADERS PRH
177 SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
178 WHERE nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N'
179 AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
180 AND exists (select /*+ HASH_SJ */ /*'At least one inventory sourced line'
181 from po_requisition_lines prl
182 where prh.requisition_header_id =
183 prl.requisition_header_id
184 and prl.source_type_code = 'INVENTORY'
185 and nvl(prl.cancel_flag,'N') = 'N'
186 and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');*/
187
188 /* Bug 7699084 Commented the above sql and introduced the below sql for performance gains
189 Added an Nvl around the transferred_to_oe_flag check*/
190
191 UPDATE po_requisition_headers prh
192 SET prh.transferred_to_oe_flag = 'I'
193 WHERE prh.authorization_status = 'APPROVED'
194 AND prh.requisition_header_id IN (SELECT /*+ index ( PRL po_requisition_lines_f5 ) */
195 DISTINCT prl.requisition_header_id
196 FROM po_requisition_lines prl
197 WHERE Nvl(prl.line_location_id,- 999) = - 999
198 AND prl.source_type_code = 'INVENTORY'
199 AND Nvl(prl.transferred_to_oe_flag,'N') = 'N' --Bug7699084
200 AND Nvl(prl.cancel_flag,'N') = 'N'
201 AND Nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');
202
203 Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
204
205 ELSE
206
207 UPDATE PO_REQUISITION_HEADERS PRH
208 SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
209 WHERE /*nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N' AND Bug 7699084*/
210 PRH.AUTHORIZATION_STATUS = 'APPROVED'
211 AND PRH.REQUISITION_HEADER_ID = p_req_header_id
212 AND exists (select 'At least one inventory sourced line'
213 from po_requisition_lines prl
214 where prh.requisition_header_id =
215 prl.requisition_header_id
216 and prl.source_type_code = 'INVENTORY'
217 and Nvl(prl.transferred_to_oe_flag,'N') = 'N' /*Bug 7699084*/
218 and nvl(prl.cancel_flag,'N') = 'N'
219 and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');
220
221 Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
222
223 END IF;
224
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN /* there are no reqs to process */
227 Fnd_File.Put_Line(FND_FILE.LOG, 'No reqs selected for processing');
228 return;
229 WHEN OTHERS THEN
230 Fnd_File.Put_Line(FND_FILE.LOG, 'Error updating req headers');
231 return;
232 END;
233
234 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
235
236 /* 2034580 - Check if PJM is installed */
237
238 x_pjm_status := po_core_s.get_product_install_status('PJM');
239
240 /* Bug# 1672814
241 We are now initialising the Previous Operating Unit to -99 instead
242 of 0 as we can have Operating Unit with Value of 0 which results in
243 Operating Unit Details not getting Fetched */
244
245 l_op_unit_id_prev := -99;
246 l_req_hdr_id_prev := 0;
247 l_req_line_number := 1; --Bug #12576879
248 l_prev_sold_to_org_id := -1;
249 Open REQ_LINES;
250 LOOP
251 Fnd_File.Put_Line(FND_FILE.LOG, 'Top of Fetch Loop');
252 fetch REQ_LINES into
253 l_op_unit_id,
254 l_req_hdr_id,
255 l_req_line_id, l_sold_to_org_id;
256
257 EXIT WHEN REQ_LINES%NOTFOUND;
258
259 --Bug 7699084 Populating the table with req header id fetched by the cursor
260 tbl_index := tbl_index + 1;
261 l_req_hdr_id_tbl.extend;
262 l_req_hdr_id_tbl(tbl_index) := l_req_hdr_id;
263
264 l_error_flag := 'N';
265
266 IF l_op_unit_id_prev <> l_op_unit_id then
267
268 /* Bug# 1523554 : When the Source organization changes, the
269 Headers must be created in the Source Organization Operating Unit.
270 This was not happening when a requisition had lines from
271 2 different Source Organizations which in Turn had different
272 Operating Units. Now initialising l_req_hdr_id_prev to 0 so that
273 it creates a new header in the Source Organization Operating Unit
274 every time the Source Organization Operating Unit Changes. */
275
276 l_req_hdr_id_prev := 0;
277
278 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Source Operating Unit: ' || to_char(l_op_unit_id));
280
281 GET_OPUNIT_DETAILS(l_op_unit_id,
282 l_error_flag,
283 l_currency_code,
284 l_ot_id,
285 l_pr_id,
286 l_ac_id,
287 l_ir_id);
288
289 IF (l_error_flag = 'Y') then
290 UPDATE PO_REQUISITION_HEADERS
291 SET TRANSFERRED_TO_OE_FLAG = 'E'
292 WHERE REQUISITION_HEADER_ID = l_req_hdr_id
293 and TRANSFERRED_TO_OE_FLAG = 'I';
294 ELSE
295 l_op_unit_id_prev := l_op_unit_id;
296 END IF;
297
298 END IF;
299 IF l_sold_to_org_id <> l_prev_sold_to_org_id then
300 l_prev_sold_to_org_id :=l_sold_to_org_id;
301 l_req_hdr_id_prev := 0;
302 l_req_line_number := 1; --Bug #12576879
303 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
304 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer id: ' || to_char(l_sold_to_org_id));
305
306 END IF; /*--7716682*/
307 IF (l_error_flag <> 'Y') then
308
309 IF(l_req_hdr_id <> l_req_hdr_id_prev) then
310 --Bug 7662103: The order_date_type_code needs to be populated 'ARRIVAL'
311 -- to enable planned IR to get synced wih the scheduled Arrival date in internal sales order
312
313
314 INSERT INTO OE_HEADERS_IFACE_ALL
315 (creation_date,
316 created_by,
317 last_update_date,
318 last_updated_by,
319 last_update_login,
320 orig_sys_document_ref,
321 sold_to_org_id,
322 order_type_id,
323 order_source_id,
324 order_category,
325 ordered_date,
326 transactional_curr_code,
327 request_date,
328 price_list_id,
329 accounting_rule_id,
330 invoicing_rule_id,
331 ship_to_org_id,
332 org_id,
333 ORDER_DATE_TYPE_CODE)
337 SYSDATE,
334 SELECT
335 SYSDATE,
336 RH.CREATED_BY,
338 RH.LAST_UPDATED_BY,
339 RH.LAST_UPDATE_LOGIN,
340 RH.REQUISITION_HEADER_ID, /* Requisition Header Id */
341 PLA.CUSTOMER_ID,
342 l_ot_id,
343 10, -- seeded order source for internal reqs
344 'P',
345 RH.CREATION_DATE,
346 l_currency_code,
347 RL.NEED_BY_DATE,
348 l_pr_id,
349 l_ac_id,
350 l_ir_id,
351 PLA.SITE_USE_ID,
352 decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
353 'ARRIVAL' --Bug 7662103:
354 FROM PO_REQUISITION_HEADERS RH,
355 PO_REQUISITION_LINES RL,
356 PO_LOCATION_ASSOCIATIONS_ALL PLA
357 WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
358 AND RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID
359 AND nvl(PLA.ORG_ID,-1) = l_op_unit_id
360 AND RL.REQUISITION_LINE_ID = l_req_line_id;
361
362 l_req_hdr_id_prev := l_req_hdr_id;
363 -- Bug 13889095 reset the counter when enter a new order
364 l_req_line_number := 1;
365
366 /* Bug # 1653150
367 Added the if to Insert in to OE_ACTIONS_IFACE_ALL only when the Header
368 was inserted. */
369
370 IF SQL%ROWCOUNT>0 then
371
372 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
373 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Header : '||to_char(l_req_hdr_id));
374
375 /* Bug # 1653150
376 Used OE_ACTIONS_IFACE_ALL rather than OE_ACTIONS_INTERFACE as it was
377 inserting with the Org_id of the Destination Org. This resulted in Sales
378 Order not getting Created in a Booked State. */
379
380 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting the customer id');
381
382 -- Bug 3365408: We need to insert the customer id into
383 -- the sold to org column in the actions interface
384 Begin
385 SELECT PLA.CUSTOMER_ID
386 INTO l_customer_id
387 FROM PO_REQUISITION_LINES RL,
388 PO_LOCATION_ASSOCIATIONS_ALL PLA
389 WHERE RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID
390 AND nvl(PLA.ORG_ID,-1) = l_op_unit_id
391 AND RL.REQUISITION_LINE_ID = l_req_line_id;
392 Exception
393 When others then
394 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting the customer id Exception :' || to_char(sqlcode));
395 l_customer_id:= null;
396 End;
397
398 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting the customer id: ' || l_customer_id);
399
400 INSERT INTO OE_ACTIONS_IFACE_ALL
401 (ORDER_SOURCE_ID,
402 ORIG_SYS_DOCUMENT_REF,
403 OPERATION_CODE,
404 ORG_ID,
405 SOLD_TO_ORG_ID) -- Bug 3365408
406 values
407 (10,
408 l_req_hdr_id,
409 'BOOK_ORDER',
410 decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
411 l_customer_id -- Bug 3365408
412 );
413 ELSE
414
415 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
416 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Header not Inserted : '||to_char(l_req_hdr_id));
417
418 END IF;
419
420 END IF; /* end of if it is a new header */
421
422 -- Bug 2873877 START
423 -- The Project and Task on the Internal Req should only be passed
424 -- to the OM Interface if all of the following conditions are met:
425 -- 1. The destination type is Inventory.
426 -- 2. The project and task pass PJM validation in the source org.
427 --
428 -- Otherwise, we will null out the project and task.
429
430 SELECT PRL.destination_type_code,
431 PRL.source_organization_id,
432 PRL.org_id,
433 PRL.need_by_date,
434 PRD.project_id,
435 PRD.task_id,
436 --<INVCONV R12 START>
437 PRL.secondary_quantity,
438 PRL.secondary_unit_of_measure,
439 PRL.item_id
440 --<INVCONV R12 END>
441 INTO l_dest_type_code,
442 l_source_org_id,
443 l_org_id,
444 l_need_by_date,
445 l_project_id,
446 l_task_id,
447 l_dest_secondary_quantity ,
448 l_dest_secondary_unit ,
449 l_item_id
450 FROM po_requisition_lines PRL, po_req_distributions PRD
451 WHERE PRL.requisition_line_id = l_req_line_id
452 AND PRL.requisition_line_id = PRD.requisition_line_id; -- JOIN
453
454 -- Note: Internal lines can only have one distribution. This
455 -- is ensured by the PO Submission Checks.
456
457 IF (l_project_id IS NULL AND l_task_id IS NULL) THEN
458 null; -- Do nothing if project and task are not specified.
459
460 ELSIF (l_dest_type_code <> 'INVENTORY') THEN
464 l_task_id := NULL;
461 -- Check #1: Destination type is not Inventory, so null out
462 -- the project and task.
463 l_project_id := NULL;
465 FND_FILE.put_line(FND_FILE.LOG, 'Destination type is not Inventory - nulling out the project and task.');
466
467 ELSE -- destation type is Inventory
468 -- Check #2: Call the PJM validation API with the source org.
469 --< Bug 3265539 Start >
470 -- The PJM OU is the OU of the inventory org, which is selected
471 -- in the REQ_LINES cursor
472 IF (l_op_unit_id = -1) THEN
473 -- Cannot pass in a -1 to the validation API
474 l_pjm_ou_id := NULL;
475 ELSE
476 l_pjm_ou_id := l_op_unit_id;
477 END IF;
478
479 -- Call PO wrapper procedure to validate the PJM project
480 PO_PROJECT_DETAILS_SV.validate_proj_references_wpr
481 (p_inventory_org_id => l_source_org_id,
482 p_operating_unit => l_pjm_ou_id,
483 p_project_id => l_project_id,
484 p_task_id => l_task_id,
485 p_date1 => l_need_by_date,
486 p_date2 => NULL,
487 p_calling_function => 'POXCISOB',
488 x_error_code => l_val_proj_error_code,
489 x_return_code => l_val_proj_result);
490
491 IF ( l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_failure ) THEN
492 -- PJM validation failed, so null out the project and task.
493 l_project_id := NULL;
494 l_task_id := NULL;
495 FND_FILE.put_line(FND_FILE.LOG, 'PJM validation failed in the source org - nulling out the project and task. Error message follows:');
496 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.get);
497 END IF;
498 --< Bug 3265539 End >
499
500 END IF; -- project and task are null
501 -- Bug 2873877 END
502
503 /* Bug: 1888361 - Modified the code to take care of unit_price. The problem was
504 when the source Org currency is different from destination org currency, In Enter Req
505 form, the unit_price was converted to the destination org's currency price as the Req is
506 always created in Functional currency. But when we populate the interface table, we populate
507 the currency as Source Org currency as the Sales Order has to be in Source Org's currency
508 but the unit price was destination org's currency unit price as the conversion took place
509 already. This was a mismatch when the Sales Order was created. Hence we revert the
510 unit price to the cost price of source org, before we populate the interface table.
511 Hence added a new function get_cst_price and this is called while inserting the unit_price
512 columns in so_oe_lines_iface_all table */
513
514 /* 1906141 -Subinventory was not populated in oe_lines_iface_all.
515 Populating the source subinventory from requisition lines */
516
517 /* Bug 1988404 - Removed the insert of value to schedule_ship_date. If this is
518 populated, when Order Import calls Scheduling that fails if the on hand inventory
519 is not available on that date. We expect MRP to populate the proper date when
520 scheduling is called based on the request_date provided, which is the need_by_date */
521
522 /* 2034580 - Pass project and task id only if PJM is installed for
523 destination type EXPENSE */
524
525 /* Bug2357247 if PJM is installed in shared product do not pass project_id
526 and task_id for destination type EXPENSE */
527
528 --begin bug 3249134, forward port of 3122219
529
530 --Bug 10037733
531 SELECT currency_code
532 INTO l_Source_Currency_Code
533 FROM gl_sets_of_books glsob,
534 org_organization_definitions ood
535 WHERE glsob.set_of_books_id = ood.set_of_books_id
536 AND ood.organization_id = l_source_org_id;
537
538 --get the currency_code functional currency from gl
539 select currency_code
540 into l_dest_currency_code
541 from
542 financials_system_parameters fsp,
543 gl_sets_of_books gl
544 where gl.set_of_books_id = fsp.set_of_books_id
545 --#bug 12816938 add org condition to return single row for multi org.
546 and fsp.org_id = l_org_id;
547
548 /*Find the extended precision
549 of the currency so that the unit price may be rounded off
550 to the same precision*/
551 --bug10037733 round with the source org currency 's extended precision.
552 fnd_currency.get_info (l_Source_Currency_Code,
553 l_precision,
554 l_ext_precision,
555 l_min_unit);
556 /*rounding the value of the unit_selling_price
557 and unit_list_price to extended precision while inserting data into
558 oe_lines_iface_all*/
559
560 --end bug 3249134
561
562 --<INVCONV R12 START>
563 IF l_dest_secondary_quantity IS NOT NULL THEN
564
565 -- get source secondary uom
566 PO_UOM_S.get_secondary_uom( l_item_id,
567 l_source_org_id,
568 l_source_secondary_uom,
569 l_source_secondary_unit);
570
571 IF l_source_secondary_unit IS NOT NULL THEN
572 IF l_source_secondary_unit <> l_dest_secondary_unit THEN
573 PO_UOM_S.uom_convert (l_dest_secondary_quantity,
574 l_dest_secondary_unit,
575 l_item_id,
576 l_source_secondary_unit,
577 l_source_secondary_quantity);
578 ELSE
579 l_source_secondary_quantity := l_dest_secondary_quantity;
580 END IF;
581 ELSE
582 l_source_secondary_quantity := NULL;
583 l_source_secondary_uom := NULL;
584 END IF;
585 ELSE
586 /** No need to derive secondary qty even if item in source org. is dual uom control. Order Import
587 would do the defaulting. **/
588 l_source_secondary_quantity := NULL;
589 l_source_secondary_uom := NULL;
590 END IF;
591 --<INVCONV R12 END>
592
593 /*bug10037733 if the source and destination orgs are of different currencies then
594 convert the unit price to the source orgs currency before populating the
595 order lines interface. */
596
597 IF (NVL(l_Source_Currency_Code,-1) = NVL(l_dest_Currency_Code,-1)) THEN
598 -- if the source and destination org currencies are the same, populate the
599 -- interface directly.
600 SELECT rl.unit_price,
601 rl.unit_price
602 INTO l_unit_selling_price,
603 l_unit_list_price
604 FROM po_requisition_lines rl
605 WHERE requisition_line_id = l_req_line_id;
606
607 ELSE
608 -- if the source and destination org currencies are not same, then populate the
609 -- interface after converting to the source org currency
610 --convert price into source org currency
611 SELECT set_of_books_id
612 INTO l_sob_id
613 FROM financials_system_parameters
614 --#bug 12816938 add org condition to return single row for multi org.
615 WHERE org_id = l_org_id;
616
617 SELECT default_rate_type
618 INTO l_conversion_type
619 FROM po_system_parameters
620 --#bug 12816938 add org condition to return single row for multi org.
621 WHERE org_id = l_org_id;
622
623 --the conversion date is sysdate
624 l_conversion_date := sysdate;
625 l_rate := gl_currency_api.get_closest_rate_sql ( l_sob_id,
626 l_Source_Currency_Code,
627 l_conversion_date,
628 l_conversion_type,
629 30);
630 IF (l_rate < 0 OR l_rate IS NULL) THEN
631 l_rate := 1;
632 END IF;
633
634 SELECT rl.unit_price/l_rate,
635 rl.unit_price/l_rate
636 INTO l_unit_selling_price,
637 l_unit_list_price
638 FROM po_requisition_lines rl
639 WHERE requisition_line_id = l_req_line_id;
640
641
642 END IF; -- source_curr = dest_curr
643 --<Bug10037733 end>
644
645 /* Bug 5280573, Reverted the fix done in Bug # 3365408.
646 OM was validating if the same Customer details were
647 Entered in sold_to_org_id fields of Header and Lines.
648 If there were Lines which had different Customers for
649 headers Order Import was Erroring out. To insert different
650 OM Headers for OU+customer combination is not possible
651 as a Bugfix.
652 As a workaround we are just popuating the same Customer
653 information as the header for the all the sales Order
654 lines for the same OU to by pass OM Validation. */
655
656 INSERT INTO OE_LINES_IFACE_ALL
657 (CREATION_DATE,
658 CREATED_BY,
659 LAST_UPDATE_DATE,
660 LAST_UPDATED_BY,
661 LAST_UPDATE_LOGIN,
662 ORIG_SYS_DOCUMENT_REF,
663 ORIG_SYS_LINE_REF,
664 SOLD_TO_ORG_ID, -- Bug 3365408
665 LINE_NUMBER,
666 ORDER_QUANTITY_UOM,
667 ORDERED_QUANTITY,
668 UNIT_LIST_PRICE,
669 UNIT_SELLING_PRICE,
670 INVENTORY_ITEM_ID,
671 SHIP_FROM_ORG_ID,
672 REQUEST_DATE,
673 ITEM_TYPE_CODE,
674 OPTION_FLAG,
675 ORDER_SOURCE_ID,
676 CALCULATE_PRICE_FLAG,
677 SHIP_TO_ORG_ID,
678 PROJECT_ID,
679 TASK_ID,
680 END_ITEM_UNIT_NUMBER,
681 SUBINVENTORY,
682 ORG_ID,
683 ORDERED_QUANTITY_UOM2,
684 ORDERED_QUANTITY2,
685 PREFERRED_GRADE,
686 SHIPMENT_PRIORITY_CODE
687 ) /* B1548597 OPM */
688 SELECT SYSDATE,
689 RL.CREATED_BY,
690 SYSDATE,
691 RL.LAST_UPDATED_BY,
692 RL.LAST_UPDATE_LOGIN,
693 RH.REQUISITION_HEADER_ID,
694 RL.REQUISITION_LINE_ID,
695 l_customer_id, -- Bug 5280573
696 --RL.LINE_NUM,
697 l_req_line_number, --Bug 12576879 Commented above added the variable to get from counter
698 MUM.UOM_CODE,
699 round(RL.QUANTITY,9),
700 --begin bug 3249134: changed the following values to be rounded
701 --< INVCONV R12 START> umoogala: Added Dest. OrgId and qty parameters
702 decode (fnd_profile.value('PO_CUSTOM_UNIT_PRICE'),'Y',round(l_unit_list_price,l_ext_precision),round(PO_CREATE_ISO.GET_CST_PRICE(RL.ITEM_ID,
703 RL.SOURCE_ORGANIZATION_ID,
704 RL.UNIT_MEAS_LOOKUP_CODE,
705 RL.DESTINATION_ORGANIZATION_ID,
706 RL.QUANTITY),
707 l_ext_precision)), --Bug10037733
708 decode (fnd_profile.value('PO_CUSTOM_UNIT_PRICE'),'Y',round(l_unit_selling_price,l_ext_precision),round(PO_CREATE_ISO.GET_CST_PRICE(RL.ITEM_ID,
709 RL.SOURCE_ORGANIZATION_ID,
710 RL.UNIT_MEAS_LOOKUP_CODE,
711 RL.DESTINATION_ORGANIZATION_ID,
712 RL.QUANTITY),
713 l_ext_precision)), --Bug10037733
714 --end bug 3249134
715 RL.ITEM_ID,
716 RL.SOURCE_ORGANIZATION_ID,
717 RL.NEED_BY_DATE,
718 DECODE(SI.PICK_COMPONENTS_FLAG,
719 'N','STANDARD',
720 'Y','KIT',
721 'STANDARD'),
722 'N',
723 10,
724 'N',
725 LA.SITE_USE_ID,
726 -- Bug 2873877 START
727 -- Only pass project and task for Inventory lines that
728 -- pass the PJM validations in the source org. See above.
729 l_project_id,
730 l_task_id,
731 -- Bug 2873877 END
732 RD.END_ITEM_UNIT_NUMBER,
733 RL.SOURCE_SUBINVENTORY,
734 decode(l_op_unit_id, -1, NULL, l_op_unit_id),
735 l_source_secondary_uom, --<INVCONV R12 START>MUM1.UOM_CODE,
736 round( l_source_secondary_quantity,9), --<INVCONV R12 START>RL.SECONDARY_QUANTITY,
737 decode(si.grade_control_flag,'Y',RL.preferred_grade,NULL) --<INVCONV R12 START> RL.PREFERRED_GRADE
738 , decode(RL.URGENT_FLAG,'Y', fnd_profile.value('POR_URGENT_FLAG_SHIPMENT_PRIORITY_CODE'),null)
739 FROM PO_REQUISITION_LINES RL,
740 PO_REQUISITION_HEADERS RH,
741 PO_REQ_DISTRIBUTIONS RD, --only one distribution allowed!
742 MTL_SYSTEM_ITEMS SI,
743 PO_LOCATION_ASSOCIATIONS_ALL LA,
744 MTL_UNITS_OF_MEASURE MUM
745 --<INVCONV R12 START>,MTL_UNITS_OF_MEASURE MUM1
746 WHERE RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
747 AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
748 AND RL.ITEM_ID = SI.INVENTORY_ITEM_ID
749 AND RL.SOURCE_ORGANIZATION_ID = SI.ORGANIZATION_ID
750 AND RL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE
751 --<INVCONV R12 START>AND RL.SECONDARY_UNIT_OF_MEASURE = MUM1.UNIT_OF_MEASURE(+) /* B1548597 OPM */
752 AND RL.DELIVER_TO_LOCATION_ID = LA.LOCATION_ID
753 AND RL.REQUISITION_LINE_ID = l_req_line_id
754 AND nvl(LA.ORG_ID, -1) = l_op_unit_id;
755
756 l_req_line_number := l_req_line_number+1 ; --Bug #12576879
757
758 END IF; /* END if for l_error_flag <> 'Y' */
759
760 END LOOP;
761 CLOSE REQ_LINES;
762
763 Fnd_File.Put_Line(FND_FILE.LOG, '-----');
764
765 /* Update transferred_to_oe_flag for all rows processed */
766
767 UPDATE PO_REQUISITION_HEADERS
768 SET TRANSFERRED_TO_OE_FLAG =
769 DECODE(TRANSFERRED_TO_OE_FLAG,'I','Y','E','N')
770 WHERE TRANSFERRED_TO_OE_FLAG IN ('I', 'E');
771
772 --Bug7699084 Update the transferred to oe flag of the corresponding line too
773
774 FOR i IN 1..l_req_hdr_id_tbl.Count
775 LOOP
776 UPDATE po_requisition_lines prl
777 SET prl.transferred_to_oe_flag =
778 (SELECT prh.transferred_to_oe_flag
779 FROM po_requisition_headers prh
780 WHERE prh.requisition_header_id = l_req_hdr_id_tbl(i)
781 )
782 WHERE prl.requisition_header_id = l_req_hdr_id_tbl(i)
783 and prl.source_type_code = 'INVENTORY'
784 and nvl(prl.cancel_flag,'N') = 'N'
785 and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED';
786
787 END LOOP;
788
789 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the TRANSFERRED_TO_OE_FLAG of the corresponding lines');
790
791 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating TRANSFERRED_TO_OE_FLAG of '||to_char(SQL%ROWCOUNT)|| ' Requisitions');
792
793 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Create Internal Sales Order');
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unhandled Exception : '||sqlerrm);
798 /* Bug # 1653150
799 Raised a Exception if the process fails for Some reason */
800 raise;
801 END;
802
803 /*-----------------------------------------------------------------------------
804 PROCEDURE NAME: GET_OPUNIT_DETAILS
805 Beginning procedure get_curr_opunit_details
806 This procedure gets the details of the currency_code, order type
807 and the price list details for every operating unit
808 ---------------------------------------------------------------------------*/
809
810 PROCEDURE GET_OPUNIT_DETAILS(l_op_unit_id IN number,
811 l_error_flag IN OUT NOCOPY varchar2,
812 l_currency_code OUT NOCOPY varchar2,
813 l_ot_id out NOCOPY number,
814 l_pr_id out NOCOPY number,
815 l_ac_id out NOCOPY number,
816 l_ir_id out NOCOPY number) IS
817
818 BEGIN
819 /* get currency_code form GL sets of books,This should
820 return one row, if not return error and exit subroutine */
821
822 BEGIN
823
824 Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Currency Code');
825
826 SELECT glsob.CURRENCY_CODE
827 INTO l_currency_code
828 FROM GL_SETS_OF_BOOKS GLSOB,
829 FINANCIALS_SYSTEM_PARAMS_ALL FSP
830 WHERE GLSOB.SET_OF_BOOKS_ID=FSP.SET_OF_BOOKS_ID
831 AND nvl(FSP.org_id,-1) = l_op_unit_id;
832
833 EXCEPTION
834 WHEN NO_DATA_FOUND THEN
835 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CURRENCY_CODE NOT SET');
836 l_error_flag := 'Y';
837 return;
838 WHEN OTHERS THEN
839 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Currency Code retrieval');
840 l_error_flag := 'Y';
841 return;
842
843 END;
844
845 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Currency Code : ' || l_currency_code);
846
847 BEGIN
848
849 Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Order Type');
850
851 SELECT ORDER_TYPE_ID
852 INTO l_ot_id
853 FROM PO_SYSTEM_PARAMETERS_ALL
854 WHERE nvl(ORG_ID,-1) = l_op_unit_id;
855
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 FND_FILE.PUT_LINE(FND_FILE.LOG, 'System Parameters not found');
859 l_error_flag := 'Y';
860 return;
861 WHEN OTHERS THEN
862 Fnd_File.Put_Line(FND_FILE.LOG, 'Error selecting order type');
863 l_error_flag := 'Y';
864 return;
865
866 END;
867
868 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Type ID:'||to_char(l_ot_id));
869
870 BEGIN
871
872 Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Price List from Order Type');
873
874 SELECT PRICE_LIST_ID,
875 ACCOUNTING_RULE_ID,
876 INVOICING_RULE_ID
877 INTO
878 l_pr_id,
879 l_ac_id,
880 l_ir_id
881 FROM OE_TRANSACTION_TYPES_ALL
882 WHERE transaction_type_id = l_ot_id
883 AND nvl(ORG_ID, -1) = l_op_unit_id;
884
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 FND_MESSAGE.SET_NAME('PO', 'PO_CISO_NO_OE_INFO');
888 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No OE Information');
889 -- l_error_flag := 'Y'; Do not throw error if the Order type is not set in Purchasing options
890 return;
891 WHEN OTHERS THEN
892 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error selecting OE Information');
893 l_error_flag := 'Y';
894 return;
895
896 END;
897
898 END;
899
900 --<INVCONV R12 START>
901 --
902 -- OPM INVCONV umoogala Process-Discrete Transfers Enh.
903 -- Added x_dest_organization_id and qty parameters.
904 -- For these transfer, call new routine to get transfer price.
905 --
906 -- 14-Oct-2008 Uday Phadtare Bug 7462235: Changed org_information2
907 -- to org_information3 to fetch Operating Unit Id.
908
909 FUNCTION GET_CST_PRICE(x_item_id IN NUMBER,
910 x_organization_id IN NUMBER,
911 x_unit_of_measure IN VARCHAR2,
912 x_dest_organization_id IN NUMBER,
913 x_quantity IN NUMBER)
914 RETURN NUMBER IS
915
916 x_progress VARCHAR2(3);
917 x_primary_cost NUMBER := NULL;
918 x_primary_uom VARCHAR2(25);
919 x_cost_price NUMBER;
920
921 v_opm_item_id NUMBER := NULL;
922 --<INVCONV R12 START>
923 l_src_org_id BINARY_INTEGER;
924 l_dest_org_id BINARY_INTEGER;
925 l_from_ou BINARY_INTEGER;
926 l_to_ou BINARY_INTEGER;
927
928 l_src_process_enabled_flag VARCHAR(1);
929 l_dest_process_enabled_flag VARCHAR(1);
930 l_transfer_type VARCHAR2(10) := 'INTORD';
931
932 x_unit_price NUMBER := 0;
933 x_unit_price_priuom NUMBER := 0;
934 x_incr_transfer_price NUMBER;
935 x_incr_currency_code VARCHAR2(4);
936 x_currency_code VARCHAR2(4);
937 x_return_status NUMBER;
938 x_msg_data VARCHAR2(4000);
939 x_msg_count NUMBER;
940
941 x_cost_method VARCHAR2(10);
942 x_cost_component_class_id NUMBER;
943 x_cost_analysis_code VARCHAR2(10);
944 x_no_of_rows NUMBER;
945 l_ret_val NUMBER;
946 l_uom_code mtl_material_transactions.transaction_uom%TYPE;
947 --<INVCONV R12 END>
948
949 l_return_status VARCHAR2(10);
950
951
952 BEGIN
953
954 x_progress := '010';
955
956 --<INVCONV R12 START>
957 /*
958 ** Obtain the cost price for the specified
959 ** item and organization. This price is
960 ** in the primary unit of measure.
961 */
962 --============================================================
963 -- OPM INVCONV umoogala 10-Feb-2005
964 -- For process-discrete and vice-versa internal orders
965 -- (within/across OUs), call new transfer_price API and
966 -- stamp it as unit_price.
967 -- For process/process orders, call gmf_cmcommon routine
968 -- to get unit price.
969 -- No change for discrete/discrete orders.
970 --============================================================
971
972 l_src_org_id := x_organization_id;
973 l_dest_org_id := x_dest_organization_id;
974
975 SELECT NVL(src.process_enabled_flag,'N'), NVL(dest.process_enabled_flag,'N')
976 INTO l_src_process_enabled_flag, l_dest_process_enabled_flag
977 FROM mtl_parameters src, mtl_parameters dest
978 WHERE src.organization_id = l_src_org_id
979 AND dest.organization_id = l_dest_org_id;
980
981
982 IF (l_src_process_enabled_flag <> l_dest_process_enabled_flag)
983 OR (l_src_process_enabled_flag = 'Y' AND l_dest_process_enabled_flag = 'Y')
984 THEN
985 -- for process-discrete and vice-versa orders. Call get transfer price API
986 -- for process-process orders. Call get cost API
987
988 IF (l_src_process_enabled_flag = 'Y' AND l_dest_process_enabled_flag = 'N')
989 THEN
990 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVCONV: This is a process-to-discrete internal order transfer');
991 ELSIF (l_src_process_enabled_flag = 'N' AND l_dest_process_enabled_flag = 'Y')
992 THEN
993 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVCONV: This is a discrete-to-process internal order transfer');
994 ELSE
995 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVCONV: This is a process-to-process internal order transfer');
996 END IF;
997
998 -- get the from ou and to ou
999 -- Bug 7462235 - Changed org_information2 to org_information3 to fetch OU Id
1000 SELECT to_number(src.org_information3) src_ou, to_number(dest.org_information3) dest_ou
1001 INTO l_from_ou, l_to_ou
1002 FROM hr_organization_information src, hr_organization_information dest
1003 WHERE src.organization_id = l_src_org_id
1004 AND src.org_information_context = 'Accounting Information'
1005 AND dest.organization_id = l_dest_org_id
1006 AND dest.org_information_context = 'Accounting Information';
1007
1008 IF (l_src_process_enabled_flag = 'Y' AND l_dest_process_enabled_flag = 'Y') AND
1009 (l_from_ou = l_to_ou)
1010 THEN
1011 -- process/process within same OU
1012
1013 l_ret_val := GMF_CMCOMMON.Get_Process_Item_Cost (
1014 1.0
1015 , 'T'
1016 , l_return_status
1017 , x_msg_count
1018 , x_msg_data
1019 , x_item_id
1020 , l_src_org_id
1021 , sysdate
1022 , 1 -- return unit_price
1023 , x_cost_method
1024 , x_cost_component_class_id
1025 , x_cost_analysis_code
1026 , x_unit_price
1027 , x_no_of_rows
1028 );
1029
1030 IF l_ret_val <> 1
1031 THEN
1032 RETURN 0;
1033 END IF;
1034
1035 RETURN x_unit_price;
1036
1037 ELSE
1038
1039 -- process to discrete or descrete to process or process to process across OUs
1040 -- then invoke transfer price API
1041 -- pmarada bug 4687787
1042
1043 SELECT uom_code
1044 INTO l_uom_code
1045 FROM mtl_units_of_measure
1046 WHERE unit_of_measure = x_unit_of_measure
1047 ;
1048
1049 GMF_get_transfer_price_PUB.get_transfer_price (
1050 p_api_version => 1.0
1051 , p_init_msg_list => 'T'
1052
1053 , p_inventory_item_id => x_item_id
1054 , p_transaction_qty => x_quantity
1055 , p_transaction_uom => l_uom_code
1056
1057 , p_transaction_id => NULL
1058 , p_global_procurement_flag => 'N'
1059 , p_drop_ship_flag => 'N'
1060
1061 , p_from_organization_id => l_src_org_id
1062 , p_from_ou => l_from_ou
1063 , p_to_organization_id => l_dest_org_id
1064 , p_to_ou => l_to_ou
1065
1066 , p_transfer_type => 'INTORD'
1067 , p_transfer_source => 'INTREQ'
1068
1069 , x_return_status => l_return_status
1070 , x_msg_data => x_msg_data
1071 , x_msg_count => x_msg_count
1072
1073 , x_transfer_price => x_unit_price
1074 , x_transfer_price_priuom => x_unit_price_priuom
1075 , x_currency_code => x_currency_code
1076 , x_incr_transfer_price => x_incr_transfer_price /* not used */
1077 , x_incr_currency_code => x_incr_currency_code /* not used */
1078 );
1079
1080 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR
1081 x_unit_price IS NULL
1082 THEN
1083 x_unit_price := 0;
1084 END IF;
1085
1086 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVCONV: Transfer Price = ' || x_unit_price);
1087 RETURN x_unit_price;
1088
1089 END IF;
1090 --<INVCONV R12 END>
1091 ELSE
1092
1093 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INVCONV: This is a discrete-discrete internal order transfer');
1094 po_items_sv2.get_item_cost (x_item_id,
1095 x_organization_id,
1096 x_primary_cost);
1097 END IF;
1098
1099 /*
1100 ** If the primary cost is zero there is
1101 ** no need to continue with the conversion.
1102 */
1103
1104 IF (x_primary_cost = 0) THEN
1105
1106 x_cost_price := x_primary_cost;
1107
1108 ELSE
1109
1110 /*
1111 ** Obtain the primary unit of measure
1112 ** for the item.
1113 */
1114
1115 x_progress := '020';
1116
1117 SELECT primary_unit_of_measure
1118 INTO x_primary_uom
1119 FROM mtl_system_items
1120 WHERE inventory_item_id = x_item_id
1121 AND organization_id = x_organization_id;
1122
1123 /*
1124 ** If the primary unit of measure is
1125 ** the same as the unit of measure
1126 ** passed to this procedure then the cost
1127 ** does not have to be converted.
1128 */
1129
1130 IF (x_primary_uom = x_unit_of_measure) THEN
1131
1132 x_cost_price := x_primary_cost;
1133
1134 ELSE
1135
1136 IF (po_uom_sv2.convert_inv_cost(x_item_id,
1137 x_unit_of_measure,
1138 x_primary_uom,
1139 x_primary_cost,
1140 x_cost_price) = TRUE) then
1141
1142 x_cost_price := x_cost_price;
1143
1144 ELSE
1145
1146 x_cost_price := 0;
1147
1148 END IF;
1149
1150 END IF;
1151
1152 END IF;
1153
1154 return(x_cost_price);
1155
1156
1157 EXCEPTION
1158
1159 WHEN OTHERS THEN
1160
1161 x_cost_price := 0;
1162 return(x_cost_price);
1163
1164
1165 END GET_CST_PRICE;
1166
1167 END PO_CREATE_ISO;
[Home] [Help]