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