1 PACKAGE BODY cto_auto_procure_pk AS
2 /*$Header: CTOPROCB.pls 120.23.12010000.2 2008/09/05 14:04:42 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTOPROCB.pls |
10 | DESCRIPTION: |
11 | Contain all CTO and WF related APIs for AutoCreate Purchase |
12 | Requisitions. This Package creates the following |
13 | Procedures |
14 | 1. AUTO_CREATE_PUR_REQ_CR |
15 | 2. POPULATE_REQ_INTERFACE |
16 | Functions |
17 | 1. GET_RESERVED_QTY |
18 | 2. GET_NEW_ORDER_QTY |
19 | HISTORY : |
20 | 20-Sep-2001 : RaviKumar V Addepalli Initial version |
21 | |
22 | 28-Nov-2001 : Renga Kannan Modified the workflow status |
23 | |
24 | 17-Jan-2001 : Renga Kannan Modified to get the accouting info |
25 | from the correct organization
26 | |
27 | |
28 | |
29 | 23-Mar-2001 : Renga Kannan Added the whole code part for |
30 | Purchase Rollup
31 |
32 |
33 | 04-16-2002 : Renga Kannan Added Ship_to_location_id in interface table i|
34 |
35 |
36 | 07/09/2002 : Renga Kannan Removed the error report launching code |
37 |
38 | 05/17/2002 : Renga Kannan Added comments for Purchase Price rollup procedure|
39
40
41
42 | Modified on 20-SEP-2002 Sushant Sawant
43 | Fixed bug#2633259
44 | Type error CREATE_AND_APPROVE
45 |
46 | |
47 | |
48 | 27-Nov-2002 : Kundan Sarkar Fix 2503104 Passing user_item_description from |
49 | oe_order_lines_all to po_requisitions_interface |
50 | |
51 |
52 | 12-DEC-2002 Kiran Konada added code for MLSUPPLY feature
53 | added a new parameter to proc populate_req_interface
54 |
55 | Modified on 02-JAN-2003 Sushant Sawant
56 | Fixed bug#2726167
57 | Global Agreements additional parameter
58 |
59 | |
60 | |
61 | 23-Jan-2003 : Kundan Sarkar Fix 2503104 Revert earlier fix and introduce |
62 | dyanamic SQL to avoid compile time dependency of OM fix |
63 | related to USER_ITEM_DESCRIPTION column in OE_ORDER_LINES_ALL |
64 |
65 | 31-JAN-2003 Kiran Konada bugfix 2780392
66 | Addded a IF condition to check for null value passed in
67 | interface_source_line_id value passed to populate_req_interafce
68 |
69 | 12-FEB-2003 Kiran Konada
70 | In populate_req_inetrface proc
71 | moved the sql used to get project_id and task_id INTO a
72 | If block whihc gets executed only when p_interface_source_line_id
73 | is not null
74 |
75 |
76 | 03/06/03 Fixed the bug w.r.t Operating unit in Global Purchase agreement
77 |
78 | 21-May-2003 : Kundan Sarkar Fix 2971582 ( Customer bug 2931808 )
79 | Offset schedule_ship_date by post processing lead time to calculate
80 | need by date
81 |
82 | 30-May-2003 : Kundan Sarkar Fix 2985471 ( Customer bug 2978640 )
83 | Set Org Context
84 | Move Check_hold logic after checking sourcing type so that
85 | Check hold will not be called for MAKE item
86 |
87 |
88 |13-AUG-2003 : Kiran Konada
89 for bug# 3063156
90 propagte fix 3042904 to main
91 | passed the project_id and task_id as parameter of
92 | of populate_req_interface for lower-level buy items
93 | fix related to
94 | correcting spelling mistakes for ONT_SOURCE_ODE
95 | and po_req_requested is fixed by shashi in main already
96 |
97 |
98 |24-SEP-2003 : Kiran Konada
99 | Chnages for patchset-J
100 | with mutiple sources enhancement ,
101 | expected error from query sourcing org has been removed
102 | source_type =66 refers to mutiple sourcing
103 |
104 | statements after call to query org has been modified to look at
105 | source type =66 instead of expected error status
106 |
107 ||03-NOV-2003 Kiran Konada
108 |
109 | Main propagation bug#3140641
110 |
111 | revrting bufix 3042904 (main bug 3063156)with bug#3129117
112 | ie have reverted changes made on |13-AUG-2003
113 | Removed project_id and task_id as parameters
114 | Hence dependency mentioned in 3042904 has been REMOVED
115 | ie following files are not dependent as on 13-AUG-2003
116 | CTOWFAPB.pls
117 | CTOPROCB.pls
118 | CTOSUBSB.pls (only for I customers)
119 |
120 |Jul 29 2004 Kaza
121 | Forward ported ct bugs 3590305 and 3599860.Significant changes
122 | to auto_create_purchase_req_cr. Please refer to the bug texts
123 | for details. In short, take a snapshot of eligible order lines
124 | from oe_order_lines_all into bom_cto_order_lines_temp. Loop
125 | thru the temp 1000 records at a time, lock each line
126 | individually, process and commit.
127 |
128 |Oct 26,2004 Kkonada
129 | bug fix 3871646
130 | Need to remove hard coded schema names, refer to bug for more details
131 |
132 |Jan 28, 2005 Renga Kannan
133 | Front Port Bug Fix : 4068164
134 | Passed item revision information to Purchase req interface
135 | for Non configured ATO items
136 |
137 |Apr 14, 2005 Renga Kannan
138 | Fixed ST bug 4172156
139 | Purchase price rollup batch program should honor load type and
140 | do rollup for ato items in the order.
141 | Also, we have removed the hold check in the batch program as this is not
142 | required .
143 |
144 |
145 |June 16,2005 Kiran Konada
146 | changes for OPM project.
147 | comment string : OPM
148 | --auto_create_pur_req_cr--
149 | oe_lines_cur changes to get sec_qty,sec_uom and grade
150 | call to pop_req_iface passes sec_qty,sec_uom and grade as part of
151 | record structure l_req_input_data
152 | replaced call to query_sourcing_org with check_cto_can_create_supply
153 | Concuurent program will not create supply if custom api check_supply returns
154 | N(going forward)
155 | --populate_req_iface--
156 | sec_qty,sec_uom and grade inserted into po_reqs_iface_all
157 | call INV api to check for porcess org
158 | HARD Dependecnies:
159 | CTOUTILB.check_cto_can_create_supply
160 | INV api INV_GMI_RSV_BRANCH.Process_Branch
161 |
162 |Aug 9th,2005 Kiran Konada
163 | 4545070
164 | Replaced call to OE_ORDER_WF_UTIL.update_flow_status_code with
165 | call to CTO_WORKFLOW_API_PK.display_wf_status
166 |
167 |
168 |Aug 29th,2005 Kiran Konada
169 | bugfix 4545559
170 | changed the insert from po_requisitions_interface to
171 | po_requisitions_interface_all table
172 |
173 |
174 |Sep 22nd,2005 Kiran Konada
175 | Created new local procedure Get_opm_charge_account
176 | calling SLA OPM api to get charge and accrual account id
177 | Dependency: aru#4610085(pack spec and stubbed out pkg body)
178 | GMF_transaction_accounts_PUB.get_accounts
179 | Calling MRP_SOURCING_API_PK.mrp_sourcing to get sourcing
180 | vendor and vendor site id. This API has been there from 11.5.10
181 | Talked to Usha, we dont require a dependent aru for this.
182 |
183 |
184 =============================================================================*/
185
186 -- CTO_AUTO_PROCURE_PK
187 -- following parameters are created for
188 g_pkg_name CONSTANT VARCHAR2(30) := 'CTO_AUTO_PROCURE_PK';
189 gMrpAssignmentSet NUMBER ;
190
191
192 --- Added by Renga for Purchaes price rollup
193
194
195 ---------------------------------------------------------------------------------------------------------
196 ---------------------------------------------------------------------------------------------------------
197
198 -- Forward declaration for local procedures for Purchase price rollup
199 -- Module. All these procedures are private to this package and as of now used
200 -- only in Purchase price rollup module
201 -- To get more details look at the comments in the procedure body
202 -- Created by Renga Kannan on 03/23/01
203
204 ---------------------------------------------------------------------------------------------------------
205 ---------------------------------------------------------------------------------------------------------
206
207 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
208
209 g_pg_level Number;
210 Function config_exists_in_blanket(
211 p_config_item_id IN Number,
212 p_doc_header_id IN Number) return boolean;
213
214 Procedure config_asl_exists(
215 p_vendor_id IN Number,
216 p_vendor_site_id IN Number,
217 p_vendor_list IN PO_AUTOSOURCE_SV.vendor_record_details,
218 x_asl_found OUT NOCOPY Boolean,
219 x_index OUT NOCOPY Number);
220
221 procedure Reduce_vendor_by_ou(
222 p_vendor_details in PO_AUTOSOURCE_SV.vendor_record_details,
223 p_config_item_id in Number,
224 p_line_id in Number,
225 p_mode in Varchar2,
226 x_vendor_details out NOCOPY PO_AUTOSOURCE_SV.vendor_record_details);
227
228
229
230 procedure insert_blanket_line(
231 p_doc_line_id IN Number,
232 p_item_id IN Number,
233 p_item_rev IN Varchar2,
234 p_price IN Number,
235 p_int_header_id IN Number,
236 p_segment1 IN mtl_system_items.segment1%type,
237 p_start_date IN date,
238 p_end_date IN date,
239 x_return_status OUT NOCOPY Varchar2,
240 x_msg_count OUT NOCOPY Number,
241 x_msg_data OUT NOCOPY varchar2);
242
243 procedure insert_blanket_header(
244 p_doc_header_id IN Number,
245 p_batch_id IN OUT NOCOPY Number,
246 x_int_header_id Out NOCOPY Number,
247 x_org_id OUT NOCOPY po_headers_all.org_id%type,
248 x_return_status OUT NOCOPY varchar2,
249 x_msg_count OUT NOCOPY Number,
250 x_msg_data OUT NOCOPY varchar2);
251
252 Procedure Derive_start_end_date(
253 p_item_id IN bom_cto_order_lines.inventory_item_id%type,
254 p_vendor_id IN Number,
255 p_vendor_site_id IN Number,
256 p_assgn_set_id IN Number ,
257 x_start_date OUT NOCOPY date ,
258 x_end_date Out NOCOPY date);
259
260 Procedure empty_ou_global;
261
262 Procedure process_purchase_price(
263 p_config_item_id in Number,
264 p_batch_number in out NOCOPY number,
265 p_group_id in number,
266 p_overwrite_list_price in varchar2,
267 p_line_id in number,
268 p_mode in Varchar2 default 'ORDER',
269 x_oper_unit_list IN OUT NOCOPY cto_auto_procure_pk.oper_unit_tbl,
270 x_return_status out NOCOPY varchar2,
271 x_msg_data out NOCOPY varchar2,
272 x_msg_count out NOCOPY number);
273
274 -- bug fix 3590305/3599860. rkaza. 08/03/2004.
275 PROCEDURE load_lines_into_bcolt(p_sales_order_line_id NUMBER,
276 p_sales_order NUMBER,
277 p_organization_id VARCHAR2,
278 p_offset_days NUMBER,
279 x_return_status out NOCOPY VARCHAR2 );
280
281
282 PROCEDURE update_bcolt_line_status(p_line_id NUMBER,
283 p_status NUMBER,
284 x_return_status out NOCOPY VARCHAR2 );
285
286
287 --OPM
288 --to get charge and accrual account id as per SLA architecture
289 PROCEDURE Get_opm_charge_account(p_interface_source_line_id NUMBER
290 ,p_item_id NUMBER
291 ,p_destination_org_id NUMBER
292 ,p_source_type_code VARCHAR2
293 ,p_operating_unit NUMBER
294 ,x_charge_account_id OUT NOCOPY NUMBER
295 ,x_accrual_account_id OUT NOCOPY NUMBER
296 ,x_return_status OUT NOCOPY VARCHAR2);
297 --------------------------------------------------------------------------------------------------------------
298 --------------------------------------------------------------------------------------------------------------
299
300 -- End of Forward declarations
301
302 --------------------------------------------------------------------------------------------------------------
303 --------------------------------------------------------------------------------------------------------------
304
305
306
307 -- rkaza. Added the procedure for ireq project. 05/02/2005.
308 -- Start of comments
309 -- API name : get_need_by_date
310 -- Type : Public
311 -- Pre-reqs : None.
312 -- Function : Given item id, org id, SSD and source type (1 or 3), it returns
313 -- need by date for the item. Used for external and internal reqs
314 -- Parameters:
315 -- IN : p_source_type IN NUMBER Required
316 -- 1 or 3 (external or internal req).
317 -- p_item_id IN NUMBER Required
318 -- p_org_id IN NUMBER Required
319 -- ship from org id
320 -- Version : Current version 115.20
321 -- Added this description
322 -- Initial version 115.17
323 -- End of comments
324 PROCEDURE get_need_by_date(p_source_type IN NUMBER,
325 p_item_id IN NUMBER,
326 p_org_id IN NUMBER,
327 p_schedule_ship_date IN DATE,
328 x_need_by_date OUT NOCOPY DATE,
329 x_return_status OUT NOCOPY VARCHAR2) IS
330
331 l_offset_days NUMBER;
332
333 BEGIN
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336 if p_source_type = 3 then -- external req
337
338 -- rkaza. Need to consider post process lead time only for
339 -- external reqs.
340
341 -- Bugfix 3077912: Offset Scheduled ship date by post-processing
342 -- lead time to get need_by_date.
343
344 select nvl(postprocessing_lead_time,0) into l_offset_days
345 from mtl_system_items
346 where inventory_item_id = p_item_id
347 and organization_id = p_org_id;
348
349 IF PG_DEBUG <> 0 THEN
350 oe_debug_pub.add('get_need_by_date: l_offset_days=' || l_offset_days, 1);
351 END IF;
352
353 -- rkaza. Need to consider the manufacturing calendar while
354 -- determining need by date
355
356 select CAL.CALENDAR_DATE into x_need_by_date
357 from bom_calendar_dates cal, mtl_parameters mp
358 where mp.organization_id = p_org_id
359 and cal.calendar_code = mp.calendar_code
360 and cal.exception_set_id = mp.calendar_exception_set_id
361 and cal.seq_num = (select cal2.prior_seq_num - nvl(l_offset_days, 0)
362 from bom_calendar_dates cal2
363 where cal2.calendar_code = mp.calendar_code
364 and cal2.exception_set_id = mp.calendar_exception_set_id
365 and cal2.calendar_date= trunc(p_schedule_ship_date)
366 );
367
368 elsif p_source_type = 1 then -- internal req.
369 -- rkaza. For internal reqs, SSD itself is need by date. ISO will
370 -- be created with this date as SAD and in transit time will be
371 -- factored in when calculating ISO's SSD.
372
373 x_need_by_date := p_schedule_ship_date;
374
375 end if;
376
377 IF PG_DEBUG <> 0 THEN
378 oe_debug_pub.add('get_need_by_date: x_need_by_date = '|| x_need_by_date, 1);
379 END IF;
380
381 EXCEPTION
382
383 when FND_API.G_EXC_ERROR THEN
384 IF PG_DEBUG <> 0 THEN
385 oe_debug_pub.add('get_need_by_date: ' || 'expected error: ' || sqlerrm, 1);
386 END IF;
387 x_return_status := FND_API.G_RET_STS_ERROR;
388
389 when FND_API.G_EXC_UNEXPECTED_ERROR then
390 IF PG_DEBUG <> 0 THEN
391 oe_debug_pub.add('get_need_by_date: ' || 'unexpected error: ' || sqlerrm, 1);
392 END IF;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394
395 when others then
396 IF PG_DEBUG <> 0 THEN
397 oe_debug_pub.add('get_need_by_date: ' || 'exception others: ' || sqlerrm, 1);
398 END IF;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400
401 END get_need_by_date;
402
403
404
405 -- rkaza. ireq project. 05/06/2005.
406 -- Added new conc program parameter p_create_req_type that specifies whether
407 -- to create internal or external reqs or both.
408 -- This parameter will have a default null for backward compatibility in case
409 -- of prescheduled program runs without this parameter.
410
411 /**************************************************************************
412 Procedure: AUTO_CREATE_PUR_REQ_CR
413 Parameters: p_sales_order NUMBER -- Sales Order number.
414 p_dummy_field VARCHAR2 -- Dummy field for the Concurrent Request.
415 p_sales_order_line_id NUMBER -- Sales Order Line number.
416 p_organization_id VARCHAR2 -- Ship From Organization ID.
417 current_organization_id NUMBER -- Current Org ID
418 p_offset_days NUMBER -- Offset days.
419 p_create_req_type NUMBER -- specifies whether to create int or ext reqs or both.
420
421 Description: This procedure is called from the concurrent progran to run the
422 AutoCreate Purchase Requisitions.
423 *****************************************************************************/
424 PROCEDURE auto_create_pur_req_cr (
425 errbuf OUT NOCOPY VARCHAR2,
426 retcode OUT NOCOPY VARCHAR2,
427 p_sales_order NUMBER,
428 p_dummy_field VARCHAR2,
429 p_sales_order_line_id NUMBER,
430 p_organization_id VARCHAR2,
431 current_organization_id NUMBER, -- VARCHAR2,
432 p_offset_days NUMBER,
433 p_create_req_type NUMBER default null) AS
434
435 lSourceCode VARCHAR2(100);
436
437
438 /**** Begin Bugfix 3590305 / 3599860 ****/
439 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
440 Changed the architecture to enhance locking mechanism and
441 to improve performance.
442
443 With this fix, we will perform the following:
444 i) Identify the lines to be processed and insert into
445 a global temp table with a PENDING status.
446 ii) Fetch it from this temp table in batch of 1000.
447 iii) Lock the line being processed.
448 At this time, get the line details once again to get the new
449 picture. An order-line could have changed from the time it was
450 populated in the temp table.
451 iv) Process the record.
452 v) COMMIT. Since we are processing this for a batch of 1000
453 from an array, commiting after each record should not cause
454 snapshot errors.
455 vi) Update the status to COMPLETE or ERROR once processing
456 is done. If ERROR, rollback the changes for that record and
457 continue with the next.
458 vii) Before fetching the next batch, close and reopen the cursor
459 from the temp table (only PENDING records will be fetched).
460 This is done to avoid "fetch across commits" (snapshot) problems.
461
462 Note:
463 -----
464 The whole idea of temp table was thought of because, we don't want
465 new eligible records to be picked up everytime we reopened the cursor.
466 Hence, we needed to have a mechanism to mark the records from the
467 first fetch.
468
469 Since the temp table is a global temp table, it will always get
470 refreshed once the session is over.
471
472 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
473
474 cursor eligible_lines_cur is
475 select line_id
476 from bom_cto_order_lines_temp
477 where status = 1
478 order by org_id;
479
480 cursor oe_lines_cur (p_cursor_line_id number) is
481 SELECT oeh.order_number
482 ,oel.line_id
483 ,oel.line_type_id
484 ,oel.org_id
485 ,oel.inventory_item_id
486 ,oel.item_revision
487 ,oel.ordered_quantity
488 ,oel.cancelled_quantity
489 ,oel.order_quantity_uom
490 ,oel.unit_selling_price
491 ,oel.created_by
492 ,oel.ship_from_org_id
493 ,oel.ship_to_org_id
494 ,oel.schedule_ship_date
495 ,oel.request_date
496 ,oel.ordered_quantity2 --opm
497 ,oel.ordered_quantity_uom2 --opm
498 ,oel.preferred_grade --opm
499 from oe_order_lines_all oel,
500 oe_order_headers_all oeh
501 where oel.header_id = oeh.header_id
502 and oel.line_id = p_cursor_line_id
503 FOR UPDATE OF oel.line_id;
504
505 so_line oe_lines_cur%rowtype;
506
507 TYPE line_id_tab_type is table of oe_order_lines_all.line_id%type;
508 line_id_tab line_id_tab_type;
509 c_batch_size number := 1000;
510 tab_index number;
511
512 -- local variables
513 p_po_quantity NUMBER := NULL;
514 l_stmt_num NUMBER;
515 p_dummy VARCHAR2(2000);
516 v_rsv_quantity NUMBER; -- Bugfix 3652509: Removed precision
517 v_sourcing_rule_exists VARCHAR2(100);
518 v_sourcing_org NUMBER;
519 v_source_type NUMBER;
520 v_transit_lead_time NUMBER;
521 v_exp_error_code NUMBER;
522 v_rec_count NUMBER := 0;
523 v_rec_count_noerr NUMBER := 0;
524 conc_status BOOLEAN ;
525 current_error_code VARCHAR2(20) := NULL;
526 v_x_error_msg_count NUMBER;
527 v_x_hold_result_out VARCHAR2(1);
528 v_x_hold_return_status VARCHAR2(1);
529 v_x_error_msg VARCHAR2(150);
530 x_return_status VARCHAR2(1);
531 l_organization_id NUMBER;
532 p_new_order_quantity NUMBER; -- Bugfix 3652509: Removed precision
533 l_res BOOLEAN;
534 l_batch_id NUMBER;
535 v_activity_status_code VARCHAR2(10);
536 l_inv_quantity NUMBER;
537
538 l_request_id NUMBER;
539 l_program_id NUMBER;
540 l_source_document_type_id NUMBER;
541
542 l_active_activity VARCHAR2(8);
543
544 -- Bugfix 2931808: New variables
545 l_need_by_date DATE;
546
547 -- bugfix 2978640 : declare new variables
548 lOperUnit NUMBER := -1;
549 xUserId Number;
550 xRespId Number;
551 xRespApplId Number;
552 x_msg_count Number;
553 x_msg_data Varchar2(1000);
554
555 -- rkaza. 05/06/2005. ireq project.
556 l_req_input_data req_interface_input_data;
557 l_flow_status_code varchar2(30) := 'EXTERNAL_REQ_REQUESTED';
558
559 v_can_create_supply varchar2(1);
560 v_message varchar2(100);
561
562 l_rets number; --bugfix# 4545070
563
564 -- begin the main procedure.
565 BEGIN
566
567 IF PG_DEBUG <> 0 THEN
568 oe_debug_pub.add('auto_create_pur_req_cr: entered ' ,1);
569 END IF;
570
571 -- initialize the program_id and the request_id from the concurrent req
572 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
573 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
574
575 /* bug fix 3590305/3599860 */
576 --
577 -- load all eligible lines into global temp table. We need to load the
578 -- line_id to identify the eligible lines, status is initially set to
579 -- 'PENDING'. Org_id is needed for ordering purpose
580 -- to preserve fix 2985475. Inventory_item_id is a not null column in this
581 -- table but we do not need it in this fix. So setting this field to
582 -- constant 0.
583 -- status code for temp table
584 -- 1 - PENDING
585 -- 2 - COMPLETED
586 -- 3 - ERROR
587 -- 4 - INELIGIBLE
588 --
589
590 savepoint begin_line;
591
592 IF PG_DEBUG <> 0 THEN
593 oe_debug_pub.add('auto_create_pur_req_cr: going to load eligible lines into bom_cto_order_lines_temp ' ,1);
594 END IF;
595
596 -- load eligible lines into bcolt
597 load_lines_into_bcolt(p_sales_order_line_id,
598 p_sales_order,
599 p_organization_id,
600 p_offset_days,
601 x_return_status);
602
603 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
604 oe_debug_pub.add ('Failed to load the lines into GTT.');
605 raise FND_API.G_EXC_ERROR;
606 end if;
607
608 -- set the return status.
609 x_return_status := FND_API.G_RET_STS_SUCCESS ;
610
611 -- Set the return code to success
612 RETCODE := 0;
613
614 lSourceCode := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
615 IF PG_DEBUG <> 0 THEN
616 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'l_source_code = '||lsourcecode,1);
617 END IF;
618
619 -- set the batch_id to the request_id
620 l_batch_id := FND_GLOBAL.CONC_REQUEST_ID;
621
622 -- Log all the input parameters
623 l_stmt_num := 1;
624
625 -- for all the sales order lines (entered, booked )
626 -- Given parameters.
627 IF PG_DEBUG <> 0 THEN
628 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
629
630 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
631
632 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
633
634 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
635
636 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
637
638 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
639
640 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
641
642 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
643 END IF;
644
645
646 /* bug fix 3590305/3599860 */
647
648 -- Fetch eligible line_id from temp table in batches of 1000 into a PL/SQL
649 -- table using cursor eligible_lines
650 -- For each line_id read the corresponding row of oeol with lock. Check if
651 -- the row is still valid. Insert into the interface table
652
653 LOOP
654 open eligible_lines_cur;
655 fetch eligible_lines_cur bulk collect into line_id_tab limit c_batch_size;
656
657 IF PG_DEBUG <> 0 THEN
658 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'records in line_id_tab ' ||to_char(line_id_tab.count),1);
659 END IF;
660 exit when (line_id_tab.count = 0);
661
662 -- Open loop for processing each eligible line.
663 -- Opening the cursor. It selects the eligible oe lines based on the
664 tab_index := line_id_tab.first;
665
666
667 IF PG_DEBUG <> 0 THEN
668 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'starting tab index ' ||to_char(tab_index),1);
669 END IF;
670
671 while tab_index is not null
672 LOOP
673 savepoint begin_line;
674
675 OPEN oe_lines_cur (line_id_tab(tab_index));
676 FETCH oe_lines_cur into so_line;
677
678 if oe_lines_cur%found then
679
680 -- count of the records selected by the cursor
681 v_rec_count := v_rec_count + 1;
682
683 -- Log all the record being processed.
684 -- Processing for
685 IF PG_DEBUG <> 0 THEN
686 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
687
688 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
689
690 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
691
692 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
693 END IF;
694
695
696 -- get the sourcing type of the item in the specified organization.
697 l_stmt_num := 30;
698
699 -- Call the procedure to return the sourcing rule.
700
701
702 --replaced query_socuring_org call (change done as part of OPM project)
703 --check_cto_can_creat_supply is a wrapper over query_socuring_org
704 --and custom api CTO_CUSTOM_SUPPLY_CHECK_PK.Check_Supply.
705 --Enhacement for R12 is, concurrent program should not create supply
706 --if custom api returns 'N'
707
708 CTO_UTILITY_PK.check_cto_can_create_supply (
709 P_config_item_id => so_line.inventory_item_id,
710 P_org_id => so_line.ship_from_org_id,
711 x_can_create_supply => v_can_create_supply,--declare
712 p_source_type => v_source_type,
713 x_sourcing_org => v_sourcing_org,
714 x_return_status =>x_return_status,
715 X_msg_count =>x_Msg_Count,
716 X_msg_data =>x_Msg_Data,
717
718 X_message =>v_message
719 );
720
721
722 /* begin bug fix 3590305 / 3599860 */
723
724 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
725
726 -- rkaza. 05/06/2005. Honoring create req type parameter.
727 -- All the ineligible lines will not be processed further.
728 -- A line is ineligible if it is make or multiple sources
729 -- or (IR but parameter is external) or (ext but parameter is
730 -- internal). Null value will be treated as ext for bwd cmptblty.
731 -- This parameter should have a null value only for prescheduled
732 -- runs. Otherwise, it is a mandatory program parameter.
733
734 IF v_can_create_supply = 'N' or
735 v_source_type in (2, 66) or
736 (p_create_req_type = 2 and v_source_type <> 1) or
737 ((p_create_req_type = 1 or p_create_req_type is null)
738 and v_source_type <> 3) THEN
739
740 IF PG_DEBUG <> 0 THEN
741 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
742 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
743 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
744 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
745 END IF;
746
747 RETCODE := 1;
748 rollback to begin_line;
749 update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
750 goto loop1;
751 END IF;
752
753 ELSE
754 IF PG_DEBUG <> 0 THEN
755 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the sourcing rule. Status = '|| x_return_status,1);
756 END IF;
757 RETCODE := 1;
758 rollback to begin_line;
759 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
760 goto loop1;
761 END IF;
762
763
764 -- Bugfix 3043284: Moved the following check (get_new_order_qty)
765 -- here so that hold check is not performed on unnecessary lines
766 -- check the quantity to be ordered.
767 -- Fix for performance bug 4897231
768 -- Passing the new parameter p_item_id
769
770 p_new_order_quantity := get_new_order_qty (
771 so_line.line_id,
772 so_line.ordered_quantity,
773 nvl(so_line.cancelled_quantity, 0),
774 so_line.inventory_item_id);
775
776 IF nvl(p_new_order_quantity, 0) = 0 THEN
777 -- Should throw this into log even if debug is off.
778 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The new order quantity is zero. Not eligible for req creation.',1);
779
780 rollback to begin_line;
781 update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
782 goto loop1;
783
784 END IF;
785
786
787 /* Start fix 2978640 Set Org context here */
788
789 /*
790 if (lOperUnit <> so_line.org_id ) then
791 IF PG_DEBUG <> 0 THEN
792 oe_debug_pub.add('Setting the Org Context to '||so_line.org_id,1);
793 END IF;
794
795 OE_ORDER_CONTEXT_GRP.Set_Created_By_Context (
796 p_header_id => NULL
797 ,p_line_id => so_line.line_id
798 ,x_orig_user_id => xUserId
799 ,x_orig_resp_id => xRespId
800 ,x_orig_resp_appl_id => xRespApplId
801 ,x_return_status => x_Return_Status
802 ,x_msg_count => x_Msg_Count
803 ,x_msg_data => x_Msg_Data );
804
805 IF x_return_status <> FND_API.G_RET_STS_SUCCESS Then
806 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the set_created_by_context',1);
807 RETCODE := 1;
808 rollback to begin_line;
809 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
810 goto loop1;
811 END IF;
812
813 end if;
814 */
815 lOperUnit := so_line.org_id;
816
817
818 /* Start fix 2978640 Check hold here */
819
820 l_stmt_num := 10;
821
822 -- check for hold on the line.
823
824 /* bugfix 4051282: check for activity hold and generic hold */
825 OE_HOLDS_PUB.Check_Holds(p_api_version => 1.0,
826 p_line_id => to_number(so_line.line_id),
827 p_wf_item => 'OEOL',
828 p_wf_activity => 'CREATE_SUPPLY',
829 x_result_out => v_x_hold_result_out,
830 x_return_status => v_x_hold_return_status,
831 x_msg_count => v_x_error_msg_count,
832 x_msg_data => v_x_error_msg);
833
834 IF (v_x_hold_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
835 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Failed in Check Hold ' || v_x_hold_return_status, 1);
836 RETCODE := 1;
837 rollback to begin_line;
838 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
839 goto loop1;
840
841 ELSE
842
843 IF PG_DEBUG <> 0 THEN
844 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Success in Check Hold ' || v_x_hold_return_status, 1);
845 END IF;
846
847 IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
848
849 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order Line ID ' || to_char(so_line.line_id )|| 'is on HOLD. ' ||v_x_hold_result_out,1);
850 fnd_message.set_name('BOM', 'CTO_ORDER_LINE_ON_HOLD');
851 oe_msg_pub.add;
852
853 RETCODE := 1;
854 -- If the line is at hold we should not process that record
855 -- We should move to the next record.
856 -- Fixed by Renga Kannan on 07/09/2002
857 rollback to begin_line; --bug fix 3590305/3599860
858 update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
859 goto loop1;
860
861 END IF;
862
863 END IF;
864
865 -- rkaza. 05/06/2005. Introduced this procedure to get need by date
866 -- depending on whether the source type is 1 or 3.
867
868 l_stmt_num := 35;
869
870 get_need_by_date(p_source_type => v_source_type,
871 p_item_id => so_line.inventory_item_id,
872 p_org_id => so_line.ship_from_org_id,
873 p_schedule_ship_date => so_line.schedule_ship_date,
874 x_need_by_date => l_need_by_date,
875 x_return_status => x_return_status);
876
877 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
878 IF PG_DEBUG <> 0 THEN
879 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
880 END IF;
881 elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
882 IF PG_DEBUG <> 0 THEN
883 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'expected error in get_need_by_date', 5);
884 END IF;
885 raise fnd_api.g_exc_error;
886 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
887 IF PG_DEBUG <> 0 THEN
888 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'unexpected error in get_need_by_date', 5);
889 END IF;
890 raise fnd_api.g_exc_unexpected_error;
891 END IF;
892
893 IF PG_DEBUG <> 0 THEN
894 oe_debug_pub.add('Need by date: '|| l_need_by_date, 1);
895 ENd IF;
896
897 -- Insert record into the interface table.
898
899 l_stmt_num := 40;
900
901 -- rkaza. 05/06/2005. Passing source information to populate_req
902 l_req_input_data.source_type := v_source_type;
903 l_req_input_data.sourcing_org := v_sourcing_org;
904
905 --opm
906 l_req_input_data.secondary_qty := so_line.ordered_quantity2;
907 l_req_input_data.secondary_uom := so_line.ordered_quantity_uom2;
908 l_req_input_data.grade := so_line.preferred_grade;
909
910
911 -- Call the insert_req;
912 populate_req_interface (
913 'CTO', -- interface source code passed CTO as parameter, kkonada
914 so_line.ship_from_org_id, -- p_destination_org_id
915 so_line.org_id,
916 so_line.created_by, -- created_by
917 -- bugfix 2931808 change schedule_ship_date to l_need_by_date
918 l_need_by_date, -- p_need_by_date
919 p_new_order_quantity,
920 so_line.order_quantity_uom, -- p_order_uom
921 so_line.inventory_item_id, -- p_item_id
922 so_line.item_revision,
923 so_line.line_id, -- p_interface_source_line_id
924 null, -- req-import decides price not so_line.unit_selling_price
925 l_batch_id, -- batch_id,
926 so_line.order_number,
927 l_req_input_data, -- req_interface_input_data
928 x_return_status );
929
930 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
931 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'populate_req_interface failed with status '|| x_return_status,1);
932 RETCODE := 1;
933 rollback to begin_line;
934 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
935 goto loop1;
936 ELSE
937 v_rec_count_noerr := v_rec_count_noerr + 1;
938 IF PG_DEBUG <> 0 THEN
939 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Insert successful.',1);
940 END IF;
941 END IF;
942
943
944 -- update the SO line status if the record is successfully inserted.
945 l_stmt_num := 50;
946
947 -- update the Sales Order Line Status for the line id passed into
948 -- the procedure. if there is any reservation for this line
949 -- Req/PO/Inv. The status will not be changed to PO_REQ_REQUESTED.
950 l_inv_quantity := 0;
951
952 -- get the line document_id
953 l_source_document_type_id := cto_utility_pk.get_source_document_id ( pLineId => so_line.line_id );
954
955 select sum(nvl(reservation_quantity, 0))
956 into l_inv_quantity
957 from mtl_reservations
958 where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
959 and demand_source_line_id = so_line.line_id;
960
961 -- if there is no reservation on the line that meens this line is
962 -- created for the first time and the status should be
963 -- PO_REQ_REQUESTED.
964 IF nvl(l_inv_quantity,0) = 0 THEN
965
966
967 --bugfix# 4545070
968 l_rets := CTO_WORKFLOW_API_PK.display_wf_status( p_order_line_id=>so_line.line_id);
969
970 IF l_rets = 0 THEN
971
972 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in display_wf_status - Stmt_num'||to_char(l_stmt_num),1);
973
974 RETCODE := 1;
975 rollback to begin_line;
976 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
977 goto loop1;
978
979 ELSE
980 IF PG_DEBUG <> 0 THEN
981 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order updated to REQ_REQUESTED.',1);
982 END IF;
983 END IF;
984
985
986 -- the line needs to be updated only if the line is processed
987 -- for the first time and there is no reservation (workflow
988 -- status is at 'CREATE_SUPPLY_ORDER_ELIGIBLE')
989 l_stmt_num := 60;
990
991 -- Added by Renga Kannan on 28-Nov-2001
992 -- We need to update the workflow status only if the
993 -- status is in CREATE_SUPPLY_ORDER_ELIGIBLE
994
995 CTO_WORKFLOW_API_PK.query_wf_activity_status(
996 'OEOL' ,
997 so_line.line_id ,
998 'CREATE_SUPPLY_ORDER_ELIGIBLE',
999 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1000 l_active_activity );
1001
1002 IF l_active_activity = 'NOTIFIED' THEN
1003
1004 l_res := cto_workflow_api_pk.complete_activity(
1005 p_itemtype=>'OEOL',
1006 p_itemkey =>so_line.line_id,
1007 p_activity_name=>'CREATE_SUPPLY_ORDER_ELIGIBLE',
1008 p_result_code=>'RESERVED');
1009 -- (FP 5633040) bugfix 5623360: call with RESERVED instead of COMPLETE.
1010
1011 IF NOT l_res THEN
1012
1013 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
1014
1015 RETCODE := 1;
1016 rollback to begin_line;
1017 update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
1018 goto loop1;
1019
1020 END IF;
1021
1022 END IF;
1023 END IF; -- end if l_inv_quantity is 0
1024
1025
1026 <<loop1>>
1027 null;
1028
1029 END IF; -- if oe_lines_cur%found
1030
1031 close oe_lines_cur; -- Done processing the line. We will reopen cursor with new line_id.
1032
1033 begin
1034 update bom_cto_order_lines_temp
1035 set status = 2 -- set status to completed
1036 where line_id = line_id_tab(tab_index)
1037 and status = 1;
1038 exception
1039 when others then
1040 null;
1041 end;
1042
1043 commit; -- Commit to release locks.
1044
1045 tab_index := line_id_tab.next(tab_index);
1046
1047 end LOOP; -- loop on oe_lines_cur
1048 -- end of array processing
1049
1050 line_id_tab.delete;
1051 close eligible_lines_cur; -- Close the cursor and reopen it to avoid fetch across commits.
1052
1053 end loop; -- loop on eligible_lines_cur
1054
1055
1056 IF PG_DEBUG <> 0 THEN
1057 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1058
1059 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1060
1061 oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1062
1063 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1064
1065 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1066 END IF;
1067
1068
1069 -- The following part of the code
1070 -- is modified by Renga Kannan on 11/12/01
1071 -- In the case of RETCODE = 1 it should complete the batch program with Warning
1072
1073 IF RETCODE = 1 THEN
1074
1075 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
1076
1077 ELSE
1078
1079 RETCODE := 0 ;
1080 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
1081
1082 END IF;
1083
1084
1085 -- removed pg_debug check since messages need to be printed irrespective
1086 -- of debug level.
1087 EXCEPTION
1088 WHEN FND_API.G_EXC_ERROR THEN
1089 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1090 rollback to begin_line; --bug fix 3590305/3599860
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 RETCODE := 2;
1093 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1094
1095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1096 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1097 rollback to begin_line; --bug fix 3590305/3599860
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099 RETCODE := 2;
1100 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1101
1102 WHEN OTHERS THEN
1103 oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1104 rollback to begin_line; --bug fix 3590305/3599860
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1106 RETCODE := 2;
1107 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1108
1109 END auto_create_pur_req_cr;
1110
1111
1112
1113 -- rkaza. 05/09/2005. Introduced this procedure for ireq project. Called from
1114 -- populate_req_interface.
1115 -- Start of comments
1116 -- API name : get_deliver_to_location_id
1117 -- Type : Private
1118 -- Pre-reqs : None.
1119 -- Function : Given org id and source type (1 or 3), it returns
1120 -- deliver to location id. Used for external and internal reqs
1121 -- Parameters:
1122 -- IN : p_org_id IN NUMBER Required
1123 -- p_source_type IN NUMBER Required
1124 -- 1 or 3 (internal or external reqs)
1125 -- Version : Current version 115.87
1126 -- Added this description
1127 -- Initial version 115.84
1128 -- End of comments
1129 PROCEDURE get_deliver_to_location_id(
1130 p_org_id NUMBER,
1131 p_source_type NUMBER,
1132 x_location_id OUT NOCOPY NUMBER,
1133 x_return_status OUT NOCOPY VARCHAR2 ) IS
1134
1135 --Bugfix 7162037
1136 CURSOR delivery_location_cur (l_dest_org_id number) IS
1137 SELECT location_id
1138 FROM hr_locations_all loc
1139 WHERE inventory_organization_id = l_dest_org_id
1140 AND ship_to_site_flag = 'Y'
1141 AND EXISTS ( SELECT 1 FROM po_location_associations_all pla
1142 WHERE loc.location_id = pla.location_id
1143 );
1144
1145 Begin
1146
1147 x_return_status := FND_API.G_RET_STS_SUCCESS;
1148
1149 -- get the location_id from hr_organization_units.
1150
1151 if p_source_type = 1 then
1152
1153 -- for IR's need to validate against po_line_associations also.
1154 -- There is a user setup needed for IR. Refer to PO manuals.
1155
1156 /* Commented as part of bugfix 7162037
1157 select org.location_id into x_location_id
1158 from hr_all_organization_units org,
1159 hr_locations_all loc
1160 where org.organization_id = p_org_id
1161 and org.location_id = loc.location_id
1162 and exists(select 1 from po_location_associations_all pla
1163 where pla.location_id = loc.location_id); */
1164
1165 open delivery_location_cur (p_org_id);
1166 FETCH delivery_location_cur INTO x_location_id;
1167 CLOSE delivery_location_cur;
1168
1169 IF PG_DEBUG <> 0 THEN
1170 oe_debug_pub.add('populate_req_interface: ' || 'Location ID : '||to_Char(x_location_id),1);
1171 END IF;
1172
1173 IF x_location_id IS NULL THEN
1174 RAISE No_Data_Found;
1175 END IF;
1176 --End Bugfix 7162037
1177
1178 elsif p_source_type = 3 then
1179
1180 -- For ER's validating against hr_locations is enough
1181 select org.location_id into x_location_id
1182 from hr_all_organization_units org,
1183 hr_locations_all loc
1184 where org.organization_id = p_org_id
1185 and org.location_id = loc.location_id;
1186
1187 end if;
1188
1189 EXCEPTION
1190
1191 When no_data_found THEN
1192 IF PG_DEBUG <> 0 THEN
1193 oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception No location_id found', 1);
1194 END IF;
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196
1197 when FND_API.G_EXC_ERROR THEN
1198 IF PG_DEBUG <> 0 THEN
1199 oe_debug_pub.add('get_deliver_to_location_id: ' || 'expected error: ' || sqlerrm, 1);
1200 END IF;
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202
1203 when FND_API.G_EXC_UNEXPECTED_ERROR then
1204 IF PG_DEBUG <> 0 THEN
1205 oe_debug_pub.add('get_deliver_to_location_id: ' || 'unexpected error: ' || sqlerrm, 1);
1206 END IF;
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208
1209 when others then
1210 IF PG_DEBUG <> 0 THEN
1211 oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception others: ' || sqlerrm, 1);
1212 END IF;
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214
1215 End get_deliver_to_location_id;
1216
1217
1218
1219 -- rkaza. ireq project. 05/06/2005.
1220 -- Added a new parameter of type p_req_interface_input_data record.
1221 -- This is for passing source_type and source_org.
1222
1223 /**************************************************************************
1224 Procedure: POPULATE_REQ_INTERFACE
1225 Parameters:
1226 p_interface_source_code VARCHAR2 --interafce source code
1227 p_destination_org_id NUMBER -- PO Destination Org ID
1228 p_org_id NUMBER --
1229 p_created_by NUMBER -- Created By for preparor ID
1230 p_need_by_date DATE -- Need by date
1231 p_order_quantity NUMBER -- Order Quantity
1232 p_order_uom VARCHAR2 -- Order Unit Of Measure
1233 p_item_id NUMBER -- Inventory Item Id on the SO line.
1234 p_item_revision VARCHAR2 -- Item Revisionon the SO Line.
1235 p_interface_source_line_id NUMBER -- Interface Source Line ID
1236 p_unit_price NUMBER -- Unit Price on the SO Line.
1237 p_batch_id NUMBER -- Batch ID for the Req-Import
1238 p_order_number VARCHAR2 -- Sales Order Number.
1239 p_req_interface_input_data req_interface_input_data -- a record structure for any other IN parameters
1240 x_return_status OUT VARCHAR2 -- Return Status.
1241
1242 Description: This procedure is called from the concurrent program
1243 and the Workflow to create the records in the
1244 req-interface table based on the line ID passed in to these procedures.
1245 *****************************************************************************/
1246 PROCEDURE populate_req_interface(
1247 p_interface_source_code VARCHAR2,
1248 p_destination_org_id NUMBER,
1249 p_org_id NUMBER,
1250 p_created_by NUMBER,
1251 p_need_by_date DATE,
1252 p_order_quantity NUMBER,
1253 p_order_uom VARCHAR2,
1254 p_item_id NUMBER,
1255 p_item_revision VARCHAR2,
1256 p_interface_source_line_id NUMBER,
1257 p_unit_price NUMBER,
1258 p_batch_id NUMBER,
1259 p_order_number VARCHAR2,
1260 p_req_interface_input_data req_interface_input_data,
1261 x_return_status OUT NOCOPY VARCHAR2 ) IS
1262
1263
1264 -- Define global variables.
1265 l_user_id NUMBER;
1266 l_login_id NUMBER;
1267 l_request_id NUMBER;
1268 l_application_id NUMBER;
1269 l_program_id NUMBER;
1270 l_resp_id NUMBER;
1271 l_org_id NUMBER;
1272 l_system_date DATE := SYSDATE;
1273
1274 -- Initialize local variables
1275 l_intf_source_code VARCHAR2(20) ;
1276 l_authorization_status VARCHAR2(20) := 'APPROVED';
1277 l_source_type_code VARCHAR2(20) := 'VENDOR';
1278 l_dest_type_code VARCHAR2(10) := 'INVENTORY';
1279
1280 -- define local variables
1281 l_stmt_num NUMBER;
1282 l_location_id NUMBER;
1283 p_receiving_account_id NUMBER;
1284 p_preparer_id NUMBER;
1285 v_rsv_quantity NUMBER; -- Bugfix 3652509: Removed precision
1286 v_note_to_buyer VARCHAR2(240);
1287 v_note_to_receiver VARCHAR2(240);
1288
1289 -- 2503104 : declare variable to store user_item_description
1290 l_user_item_desc varchar2(240);
1291
1292 --dfeault value 'Y' for bugfix 3042904
1293 --and bugfix 3129117
1294 l_pegging_flag VARCHAR2(1) := 'Y'; --bug 3042904
1295
1296 -- rkaza. 07/16/2004. bug 3771585.
1297 -- Select material account from mtl_parameters instead of receiving account
1298 -- from rcv_paramters
1299 CURSOR charge_account_cur (i_org_id NUMBER ) IS
1300 SELECT material_account
1301 FROM mtl_parameters
1302 WHERE organization_id = i_org_id;
1303
1304 CURSOR emp_id (i_created_by NUMBER) IS
1305 SELECT employee_id
1306 FROM fnd_user
1307 WHERE user_id = i_created_by;
1308
1309 l_operating_unit Number;
1310
1311 l_project_id oe_order_lines_all.project_id%type;
1312 l_task_id oe_order_lines_all.task_id%type;
1313
1314 --bugfix 4068164
1315 l_item_revision VARCHAR2(3) := null;
1316 l_ato_line_id NUMBER;
1317
1318 -- rkaza. 05/09/2005. ireq project.
1319 l_req_type varchar2(15) := null;
1320 l_sourcing_org number := null;
1321 l_auto_source_flag varchar2(2) := null;
1322
1323 --opm
1324 l_accrual_account_id number;
1325
1326 BEGIN
1327
1328 l_stmt_num := 10;
1329
1330 -- Initialize all the standard variables.
1331 l_user_id := FND_GLOBAL.USER_ID;
1332 l_login_id := FND_GLOBAL.LOGIN_ID;
1333 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1334 l_application_id := FND_GLOBAL.RESP_APPL_ID;
1335 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1336 l_resp_id := FND_GLOBAL.RESP_ID;
1337
1338 l_intf_source_code := p_interface_source_code;
1339
1340 l_stmt_num := 20;
1341
1342 -- rkaza. 05/06/2005. Introduced new procedure to deliver_to_location_id
1343 -- depending on source type.
1344 get_deliver_to_location_id(p_destination_org_id,
1345 p_req_interface_input_data.source_type,
1346 l_location_id,
1347 x_return_status);
1348
1349 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1350 IF PG_DEBUG <> 0 THEN
1351 oe_debug_pub.add('populate_req_interface: ' || 'success from get_deliver_to_location_id' || ' l_location_id = ' || l_location_id, 1);
1352 END IF;
1353 elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1354 IF PG_DEBUG <> 0 THEN
1355 oe_debug_pub.add('populate_req_interface: ' || 'expected error in get_deliver_to_location_id', 1);
1356 END IF;
1357 raise fnd_api.g_exc_error;
1358 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1359 IF PG_DEBUG <> 0 THEN
1360 oe_debug_pub.add('populate_req_interface: ' || 'unexpected error in get_deliver_to_location_id', 1);
1361 END IF;
1362 raise fnd_api.g_exc_unexpected_error;
1363 END IF;
1364
1365
1366
1367
1368 -- get the employee id of the preparer based on the created by.
1369 p_preparer_id := null;
1370
1371 l_stmt_num := 30;
1372 FOR e_id in emp_id (p_created_by) LOOP
1373 p_preparer_id := e_id.employee_id;
1374 IF PG_DEBUG <> 0 THEN
1375 oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID : '||to_char(p_preparer_id),1);
1376 END IF;
1377 exit;
1378 END LOOP;
1379
1380 -- (FP 5633329)bugfix 5514977: If p_prepared_id is null, then get the value from profile ONT_EMP_ID_FOR_SS_ORDERS
1381 ---Profile is obsolete in R12 and it was moved to OE system parameters
1382 ---we are using the operating unit of order line in order to be consistent with OM dropship code
1383 IF p_preparer_id is null and p_interface_source_code in ('CTO','CTO-LOWER LEVEL') THEN
1384 p_preparer_id := oe_sys_parameters.value('ONT_EMP_ID_FOR_SS_ORDERS',p_org_id);
1385
1386 IF PG_DEBUG <> 0 THEN
1387 oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID (empIdforSSOrders): '||to_char(p_preparer_id),1);
1388 END IF;
1389 END IF;
1390
1391
1392 -- get the note to buyer from the fnd_new_messages.
1393 -- bugfix 2701102 : call fnd_message.get_string
1394
1395 v_note_to_buyer := substrb (FND_MESSAGE.get_string ('PO', 'CTO_NOTE_TO_BUYER'), 1, 240);
1396 v_note_to_receiver := substrb (FND_MESSAGE.get_string ('PO', 'CTO_NOTE_TO_RECEIVER'), 1,240);
1397
1398 /**** begin bugfix 2701102 : call fnd_message.get_string instead of the following
1399
1400 v_note_to_buyer := null;
1401
1402 l_stmt_num := 40;
1403 FOR n_buyer in get_message ('CTO_NOTE_TO_BUYER') LOOP
1404 v_note_to_buyer := n_buyer.message_text;
1405 exit;
1406 END LOOP;
1407
1408 -- get the note to receiver from the fnd_new_messages.
1409 v_note_to_receiver := null;
1410
1411 l_stmt_num := 50;
1412 FOR n_receiver in get_message ('CTO_NOTE_TO_RECEIVER') LOOP
1413 v_note_to_receiver := n_receiver.message_text;
1414 exit;
1415 END LOOP;
1416
1417 ******* end bugfix 2701102 */
1418
1419 -- New fix for 2503104
1420 -- IF p_interface_source_line_id is not null THEN --bugfix 2780392
1421 --code need not be called for lower level buy config items as
1422 --no order line information exists and hence
1423 --interface_source_line_id passed is null
1424
1425 --bugfix 3129117
1426 --using p_interface_source code to detremine
1427 --top most line_id
1428 IF p_interface_source_code = 'CTO' THEN
1429 l_stmt_num := 60;
1430 DECLARE
1431 sql_stmt varchar2(2000);
1432 pflag varchar2(1) :='Y';
1433 l_chk_col number;
1434
1435 --start bugfix 3871646
1436 l_result boolean;
1437 l_status varchar2(60);
1438 l_industry varchar2(60);
1439 l_customer_schema varchar2(60);--db length is 30 fnd_oracle_userid.oracle_username
1440 --end bugfix 3871646
1441
1442 BEGIN
1443
1444 --start bugfix 3871646
1445 --refer to bug for more details
1446 l_stmt_num := 70;
1447 l_result := FND_INSTALLATION.GET_APP_INFO
1448 ( APPLICATION_SHORT_NAME=>'ONT',
1449 STATUS =>l_status,
1450 INDUSTRY=>l_industry,
1451 ORACLE_SCHEMA =>l_customer_schema);
1452
1453 IF (l_result) THEN
1454
1455 IF PG_DEBUG <> 0 THEN
1456 oe_debug_pub.add('success after call to FND_INSTALLATION.GET_APP_INFO',1);
1457 oe_debug_pub.add('returned custmer schema name for ONT =>'||l_customer_schema,1);
1458 END IF;
1459 ELSE
1460 null;
1461 IF PG_DEBUG <> 0 THEN
1462 oe_debug_pub.add('FAILED IN call to FND_INSTALLATION.GET_APP_INFO',1);
1463 END IF;
1464
1465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1466
1467 END IF;
1468
1469 --end bugfix 3871646
1470
1471 l_stmt_num := 80;
1472 select count(*) into l_chk_col
1473 from all_tab_columns
1474 where owner = l_customer_schema --bugfix 3871646 --'ONT'
1475 and table_name = 'OE_ORDER_LINES_ALL'
1476 and column_name = 'USER_ITEM_DESCRIPTION';
1477
1478 If l_chk_col > 0 then
1479 sql_stmt := 'SELECT substrb(oel.user_item_description,1,240)'
1480 ||' FROM oe_order_lines_all oel , mtl_system_items msi'
1481 ||' WHERE oel.ship_from_org_id = msi.organization_id'
1482 ||' AND oel.inventory_item_id = msi.inventory_item_id'
1483 ||' AND oel.line_id = :p_interface_source_line_id'
1484 ||' AND msi.organization_id = :p_destination_org_id'
1485 ||' AND msi.allow_item_desc_update_flag = :pflag ';
1486
1487 IF PG_DEBUG <> 0 THEN
1488 oe_debug_pub.add(sql_stmt,1);
1489 oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1490 l_user_item_desc||'-BEFORE EXE IMM',1);
1491 END IF;
1492
1493 l_stmt_num := 90;
1494
1495 EXECUTE IMMEDIATE sql_stmt INTO l_user_item_desc
1496 USING p_interface_source_line_id,p_destination_org_id,pflag;
1497
1498 IF PG_DEBUG <> 0 THEN
1499 oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1500 l_user_item_desc||'-AFTER EXE IMM',1);
1501 oe_debug_pub.add('User Item Description is : ' || l_user_item_desc ,1);
1502 END IF;
1503 else
1504 l_user_item_desc := NULL;
1505
1506 IF PG_DEBUG <> 0 THEN
1507 oe_debug_pub.add('OE_ORDER_LINES_ALL does not have column USER_ITEM_DESCRIPTION , l_chk_col: ' ||
1508 to_char(l_chk_col),1);
1509 END IF;
1510
1511 end if;
1512 EXCEPTION
1513 when NO_DATA_FOUND then --bugfix 3054055: added no_data_found excepn. Also added debug check above.
1514 l_user_item_desc := NULL;
1515
1516 when OTHERS then
1517 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1518 END;
1519
1520
1521
1522 END IF; --bugfix 2780392
1523
1524 --bugfix 3129117
1525 --moved follwoing slect for
1526 --project_id and task_id out of above if block as the
1527 --information is needed both for top-lvel and lower
1528 --level child configurations
1529 --interface source line id passed is always of top-level
1530 --configuration
1531 l_stmt_num := 100;
1532 Begin
1533
1534 Select decode(project_id,-1,NULL,project_id),
1535 decode(task_id,-1,NULL,task_id)
1536 into l_project_id,
1537 l_task_id
1538 from oe_order_lines_all
1539 where line_id = p_interface_source_line_id;
1540 Exception
1541 WHen no_data_found THEN
1542 null;
1543
1544 End;
1545
1546 IF PG_DEBUG <> 0 THEN
1547 oe_debug_pub.add('Project _id and task_id are =>' ||l_project_id || 'and' || l_task_id);
1548 END IF;
1549
1550 --IF p_interface_source_line_id is null THEN --bugfix 3042904
1551
1552 --bugfix 3129117
1553 --checking with interface source code as
1554 --interface source line id passed is always of top-level
1555 --configuration
1556
1557 IF p_interface_source_code = 'CTO-LOWER LEVEL' THEN
1558
1559 --we insert the project_id and task_id
1560 --only when pegging_flag is Y for lower level
1561 l_pegging_flag := 'N';
1562
1563 l_stmt_num := 110;
1564 Begin
1565 SELECT 'Y'
1566 into l_pegging_flag
1567 FROM mtl_system_items_b
1568 WHERE inventory_item_id = p_item_id
1569 AND organization_id = p_destination_org_id
1570 AND end_assembly_pegging_flag IN ('I','X');
1571 Exception
1572 When no_data_found THEN
1573 null;
1574
1575 END;
1576
1577
1578 END IF; --bugfix 3042904
1579
1580
1581
1582 -- Added By Renga Kannan on 03/26/02
1583 -- The receiving org's operating unit is passed as org id
1584 -- This will restrict the po to be created in the receiving org ou
1585
1586 Begin
1587 -- rkaza. 3742393. 08/12/2004.
1588 -- Repalcing org_organization_definitions with
1589 -- inv_organization_info_v
1590 l_stmt_num := 120;
1591 Select operating_unit
1592 into l_operating_unit
1593 from inv_organization_info_v
1594 where organization_id = p_destination_org_id;
1595 Exception when others then
1596 l_operating_unit := null;
1597 End;
1598
1599 -- Begin bugfix 4068164: Populate item revision for revision controlled items.
1600 -- we shall populate this only if the profile INV:Purchasing by Revision is set to Yes (value = 1)
1601
1602 -- rkaza. 05/10/2005. No need to find item rev for int reqs.
1603 -- default valuye is null.
1604 if FND_PROFILE.value('INV_PURCHASING_BY_REVISION') = 1
1605 and p_req_interface_input_data.source_type <> 1 then
1606 l_stmt_num := 130;
1607 select ato_line_id into l_ato_line_id
1608 from oe_order_lines_all
1609 where line_id = p_interface_source_line_id;
1610
1611 if l_ato_line_id = p_interface_source_line_id then
1612 Begin
1613
1614 l_stmt_num := 140;
1615 select max(revision) into l_item_revision
1616 from mtl_item_revisions mir,
1617 mtl_system_items msi
1618 where msi.organization_id = p_destination_org_id
1619 and msi.inventory_item_id = p_item_id
1620 and mir.organization_id = msi.organization_id
1621 and mir.inventory_item_id = msi.inventory_item_id
1622 and mir.effectivity_date = (select max(mir1.effectivity_date)
1623 from mtl_item_revisions mir1
1624 where mir1.organization_id = msi.organization_id
1625 and mir1.inventory_item_id = msi.inventory_item_id
1626 and mir1.effectivity_date <= sysdate )
1627 and msi.revision_qty_control_code = 2 --revision controlled items only
1628 and msi.base_item_id is null -- not preconfig or config
1629 and msi.bom_item_type = 4; --standard item
1630
1631 Exception when others then
1632 IF PG_DEBUG <> 0 THEN
1633 oe_debug_pub.add('Revision not populated because '||SQLERRM);
1634 END IF;
1635 End;
1636 end if;
1637
1638 IF PG_DEBUG <> 0 THEN
1639 oe_debug_pub.add('Item Revision is ' ||l_item_revision);
1640 END IF;
1641 else
1642 If PG_DEBUG <> 0 Then
1643 oe_debug_pub.add('Popluate_req_interface: Inv Purchase by Revision is set to No or Source type is IR',1);
1644 End if;
1645 end if;
1646 -- End bugfix 4068164
1647
1648
1649 -- rkaza. 05/10/2005. ireq project. source_type_code is VENDOR for ext reqs
1650 -- and INVENTORY for int reqs. by default it is VENDOR.
1651 -- Req type is null for ext reqs (default) and 'INTERNAL' for int reqs.
1652 -- default is null for auto source flag and sourcing org.
1653
1654 if p_req_interface_input_data.source_type = 1 then
1655 l_source_type_code := 'INVENTORY';
1656 l_req_type := 'INTERNAL';
1657 l_sourcing_org := p_req_interface_input_data.sourcing_org;
1658 l_auto_source_flag := 'P';
1659 end if;
1660
1661
1662
1663 -- get the receiving account ID for the given receiving Organization ID
1664 p_receiving_account_id := null;
1665
1666 --For process org call OPM api to get charge_account_id and accrual_accountid as per SLA architecure
1667 --for organization other than process organization get charge_account_id as before; accrual info not reqd
1668 --OPM
1669 IF NOT INV_GMI_RSV_BRANCH.Process_Branch (p_organization_id => p_destination_org_id) THEN
1670
1671 -- Modified the paramter to the cursor to pass the shipping org id instead of operatinng unit.
1672 -- this is fixed as part of the bug 2188205
1673
1674 l_stmt_num := 150;
1675 FOR ch_act IN charge_account_cur (p_destination_org_id) LOOP
1676 -- rkaza. 07/16/2004. bug 3771585.
1677 -- Select material account from mtl_parameters instead of receiving
1678 -- account from rcv_paramters
1679 -- p_receiving_account_id := ch_act.receiving_account_id;
1680 p_receiving_account_id := ch_act.material_account;
1681 IF PG_DEBUG <> 0 THEN
1682 oe_debug_pub.add('populate_req_interface: ' || 'Charge Account ID : '||to_char(p_receiving_account_id),1);
1683 END IF;
1684 exit;
1685 END LOOP;
1686
1687
1688 ELSE
1689 --OPM
1690 l_stmt_num := 160;
1691 Get_opm_charge_account( p_interface_source_line_id =>p_interface_source_line_id
1692 ,p_item_id =>p_item_id
1693 ,p_destination_org_id =>p_destination_org_id
1694 ,p_source_type_code =>l_source_type_code
1695 ,p_operating_unit =>l_operating_unit
1696 ,x_charge_account_id =>p_receiving_account_id
1697 ,x_accrual_account_id =>l_accrual_account_id
1698 ,x_return_status =>x_return_status);
1699 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1700
1701 IF PG_DEBUG <> 0 THEN
1702 oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(p_receiving_account_id),1);
1703 oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
1704 END IF;
1705 ELSE
1706 IF PG_DEBUG <> 0 THEN
1707 oe_debug_pub.add('populate_req_interface: ' || 'Get_opm_charge_account RET STATUS : '||x_return_status,1);
1708 END IF;
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711
1712
1713
1714 END IF;
1715
1716
1717 -- insert into the interface table.
1718 l_stmt_num := 20;
1719 -- Insert the record in the interface table
1720 BEGIN
1721 -- rkaza. 05/10/2005. Inserting new parameters...
1722 -- requisition_type, sourcing_org, auto_source_flag
1723
1724 --OPM project, inserting parameters secondary qty,
1725 --secondary uom and grade
1726
1727 --bugfix 4545559 changed the insert from
1728 --po_requisitions_interface to _all table
1729 l_stmt_num := 210;
1730 INSERT INTO po_requisitions_interface_all (
1731 interface_source_code,
1732 destination_organization_id,
1733 deliver_to_location_id,
1734 deliver_to_requestor_id,
1735 need_by_date,
1736 last_updated_by,
1737 last_update_date,
1738 last_update_login,
1739 creation_date,
1740 created_by,
1741 destination_type_code,
1742 quantity,
1743 uom_code,
1744 authorization_status,
1745 preparer_id,
1746 item_id,
1747 item_revision,
1748 batch_id,
1749 charge_account_id,
1750 interface_source_line_id,
1751 source_type_code,
1752 source_organization_id,
1753 unit_price,
1754 note_to_buyer,
1755 note_to_receiver,
1756 org_id,
1757 item_Description, -- 2503104 : Insert user_item_description
1758 project_id,
1759 task_id,
1760 project_accounting_context,
1761 requisition_type,
1762 autosource_flag,
1763 secondary_quantity, --opm case and also for buy in discrete orgs
1764 secondary_uom_code, --opm and also for buy in discrete orgs
1765 preferred_grade, --opm and also for buy in discrete orgs
1766 accrual_account_id --opm
1767 )
1768 VALUES (
1769 l_intf_source_code,
1770 p_destination_org_id, -- ship_from_org_id
1771 l_location_id,
1772 p_preparer_id, --p_deliver_to_requestor_id/employee_id
1773 p_need_by_date,
1774 l_user_id,
1775 l_system_date,
1776 l_login_id,
1777 l_system_date,
1778 p_created_by,
1779 l_dest_type_code,
1780 p_order_quantity,
1781 p_order_uom,
1782 l_authorization_status,
1783 p_preparer_id,
1784 p_item_id,
1785 l_item_revision,
1786 p_batch_id,
1787 p_receiving_account_id,
1788 decode(p_interface_source_code,'CTO',p_interface_source_line_id,null), -- bugfix 3129117
1789 l_source_type_code,
1790 l_sourcing_org,
1791 p_unit_price,
1792 'Supply for the Sales Order :'||p_order_number||', '||v_note_to_buyer,
1793 v_note_to_receiver,
1794 l_operating_unit,
1795 l_user_item_desc, -- 2503104 : user_item_description
1796 decode(l_pegging_flag,'Y',l_project_id,null), -- bug 3129117
1797 decode(l_pegging_flag,'Y',l_task_id,null), -- bug 3129117
1798 decode(l_project_id,-1,null,null,null,'Y'),
1799 l_req_type,
1800 l_auto_source_flag,
1801 p_req_interface_input_data.secondary_qty,
1802 p_req_interface_input_data.secondary_uom,
1803 p_req_interface_input_data.grade,
1804 l_accrual_account_id
1805 );
1806
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 IF PG_DEBUG <> 0 THEN
1810 oe_debug_pub.add('populate_req_interface: ' ||
1811 'insert into the req interface table failed interface_source_line_id'||
1812 to_char(p_interface_source_line_id),1);
1813
1814 oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
1815 to_char(l_stmt_num)||'::'||sqlerrm,1);
1816 END IF;
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 END;
1819
1820
1821 EXCEPTION
1822 WHEN FND_API.G_EXC_ERROR THEN
1823 IF PG_DEBUG <> 0 THEN
1824 oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1825 END IF;
1826 x_return_status := FND_API.G_RET_STS_ERROR;
1827 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1828 IF PG_DEBUG <> 0 THEN
1829 oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1830 END IF;
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832 WHEN OTHERS THEN
1833 IF PG_DEBUG <> 0 THEN
1834 oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::other error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1835 END IF;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1837 END populate_req_interface;
1838
1839
1840
1841
1842
1843
1844 /**************************************************************************
1845 Function : GET_RESERVED_QTY
1846 Parameters : p_line_id NUMBER
1847 Return Value : Number
1848 Description : This procedure is called from the concurrent program to
1849 get the the reserved quantity on the sales Order line.
1850 *****************************************************************************/
1851 FUNCTION get_reserved_qty (
1852 p_line_id NUMBER) RETURN NUMBER IS
1853
1854 -- Define local parameters
1855 v_rsv_quantity NUMBER;
1856 l_stmt_num NUMBER;
1857
1858 BEGIN
1859 l_stmt_num := 10;
1860
1861 -- select the reservation quantities from the reservations tables.
1862 SELECT nvl(SUM(reservation_quantity), 0)
1863 INTO v_rsv_quantity
1864 FROM mtl_reservations
1865 WHERE demand_source_line_id = p_line_id;
1866
1867 Return (v_rsv_quantity);
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 IF PG_DEBUG <> 0 THEN
1871 oe_debug_pub.add('get_reserved_qty: ' || 'GET_RESERVED_QTY::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1872 END IF;
1873 Return (0);
1874 END; -- get_reserved_qty
1875
1876
1877
1878
1879
1880
1881
1882
1883 /**************************************************************************
1884 Function : GET_NEW_ORDER_QTY
1885 Parameters : p_interface_source_line_id NUMBER -- Sales Order Linae ID.
1886 p_order_qty NUMBER -- Sales Order Order_quantity.
1887 p_cancelled_qty NUMBER -- Sales Order Cancelled_quantity.
1888 Return Value : Number
1889 Description : This procedure is called from the concurrent program to
1890 get the the quantity to be reserved for the demand.
1891 *****************************************************************************/
1892 -- Fix for performance bug 4897231
1893 -- To avoid full table scan on po_requisitions_table
1894 -- we need add another where condition for item_id in po_requisitions_interface_all table
1895 -- As most of the calling modules for this API already has item_id,
1896 -- we add a new parameter p_item_id for this procedure
1897 -- This parameter will be used in the where clause of po_requisition_intface table
1898
1899 FUNCTION get_new_order_qty (
1900 p_interface_source_line_id NUMBER,
1901 p_order_qty NUMBER,
1902 p_cancelled_qty NUMBER,
1903 p_item_id NUMBER)
1904 RETURN NUMBER AS
1905
1906 -- initialize all the local parameters.
1907 p_po_quantity NUMBER; -- Bugfix 3652509: Removed precision
1908 l_stmt_num NUMBER;
1909 v_rsv_quantity number := 0; -- Bugfix 3652509: Removed precision
1910
1911 -- Fix for performance bug 4897231
1912 -- To avoid full table scan on po_requisitions_table
1913 -- added another where condition for item_id
1914 -- Po_req_interface table has index on item_id column
1915
1916 CURSOR c1(p_line_id NUMBER,p_inv_item_id number) IS
1917 SELECT Nvl(Sum(quantity),0) qty
1918 FROM po_requisitions_interface_all
1919 WHERE interface_source_line_id = p_line_id
1920 and item_id = p_inv_item_id
1921 AND process_flag is null;
1922
1923 CURSOR c2(p_line_id NUMBER) IS
1924 SELECT nvl(SUM(reservation_quantity), 0) qty
1925 FROM mtl_reservations
1926 WHERE demand_source_line_id = p_interface_source_line_id;
1927
1928 l_quantity_interface po_requisitions_interface_all.quantity%TYPE;
1929 l_open_flow_qty Number := 0;
1930
1931 BEGIN
1932
1933 -- get all the details of the quantity to be ordered.
1934 l_stmt_num := 20;
1935
1936 v_rsv_quantity := 0;
1937 FOR a2 in c2 (p_interface_source_line_id) loop
1938 v_rsv_quantity := a2.qty;
1939 EXIT;
1940 END LOOP;
1941
1942 l_quantity_interface := 0;
1943 FOR a1 in c1( p_interface_source_line_id,p_item_id) loop
1944 l_quantity_interface := a1.qty;
1945 EXIT;
1946 END LOOP;
1947 l_open_flow_qty :=
1948 MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY( p_demand_source_line => to_char(p_interface_source_line_id),
1949 p_demand_source_type => inv_reservation_global.g_source_type_oe,
1950 p_demand_source_delivery => NULL,
1951 p_use_open_quantity => 'Y');
1952
1953
1954 -- Caluculate the Actual Order Quantity from the
1955 -- sales order qty and the reservation qty.
1956 p_po_quantity := nvl(p_order_qty,0) - nvl(v_rsv_quantity,0) - nvl(l_quantity_interface,0)-nvl(l_open_flow_qty,0);
1957
1958 -- Log the quantities.
1959 IF PG_DEBUG <> 0 THEN
1960 oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
1961
1962 oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
1963
1964 oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1965
1966 oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1967
1968 oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
1969
1970 oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
1971 END IF;
1972
1973 RETURN nvl(p_po_quantity, 0);
1974
1975 END get_new_order_qty; -- get_new_order_qty
1976
1977
1978
1979
1980
1981 PROCEDURE check_order_line_status (
1982 p_line_id NUMBER,
1983 p_flow_status OUT NOCOPY VARCHAR2,
1984 p_inv_qty OUT NOCOPY NUMBER,
1985 p_po_qty OUT NOCOPY NUMBER,
1986 p_req_qty OUT NOCOPY NUMBER) as
1987
1988 cursor get_so_line (
1989 p_line_id NUMBER) is
1990 select line_id,
1991 ordered_quantity,
1992 inventory_item_id
1993 from oe_order_lines_all
1994 where line_id = p_line_id;
1995
1996 -- define the local parameters
1997 l_inv_qty NUMBER;
1998 l_po_qty NUMBER;
1999 l_req_qty NUMBER;
2000 l_source_document_type_id NUMBER;
2001
2002 BEGIN
2003 -- get the document ID from
2004 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( p_line_id );
2005
2006 -- get all the lines from the so_lines
2007 -- for the line_id passed into the cursor
2008 FOR so_line in get_so_line ( p_line_id ) LOOP
2009
2010 l_inv_qty := 0;
2011 select nvl(sum(reservation_quantity), 0)
2012 into l_inv_qty
2013 from mtl_reservations
2014 where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
2015 inv_reservation_global.g_source_type_oe )
2016 and demand_source_line_id = so_line.line_id
2017 and supply_source_type_id = inv_reservation_global.g_source_type_inv;
2018
2019 l_po_qty := 0;
2020 select nvl(sum(reservation_quantity), 0)
2021 into l_po_qty
2022 from mtl_reservations
2023 where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
2024 inv_reservation_global.g_source_type_oe )
2025 and demand_source_line_id = so_line.line_id
2026 and supply_source_type_id = inv_reservation_global.g_source_type_po;
2027
2028 l_req_qty := 0;
2029 select nvl(sum(reservation_quantity), 0)
2030 into l_req_qty
2031 from mtl_reservations
2032 where demand_source_type_id = decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
2033 inv_reservation_global.g_source_type_oe )
2034 and demand_source_line_id = so_line.line_id
2035 and supply_source_type_id = inv_reservation_global.g_source_type_req;
2036
2037 -- Check the order_quantity and the inv_rsv_qty to 'PO_RECEIVED'
2038 IF so_line.ordered_quantity = nvl(l_inv_qty,0)
2039 AND nvl(so_line.ordered_quantity, 0) > 0 THEN
2040 p_flow_status := 'PO_RECEIVED';
2041 elsif nvl(l_inv_qty, 0) > 0 THEN
2042 p_flow_status := 'PO_PARTIAL';
2043 elsif nvl(l_inv_qty, 0) = 0 THEN
2044 -- when there is no reservation on inv then
2045 IF nvl(l_po_qty, 0) > 0 THEN
2046 p_flow_status := 'PO_CREATED';
2047 elsif nvl(l_req_qty, 0) > 0 THEN
2048 p_flow_status := 'PO_REQ_CREATED';
2049 ELSE
2050 -- when there is no reservation at all then the order can be in Booked or req-erquested state
2051 -- Fix for performance bug 4897231
2052 -- To avoid full table scan on po_requisitions_table
2053 -- added another where condition for item_id
2054 -- Po_req_interface table has index on item_id column
2055
2056 BEGIN
2057 select 'EXTERNAL_REQ_REQUESTED'
2058 into p_flow_status
2059 from po_requisitions_interface_all
2060 where interface_source_line_id = so_line.line_id
2061 and item_id = so_line.inventory_item_id
2062 and process_flag is null
2063 and rownum =1;
2064 EXCEPTION
2065 WHEN OTHERS THEN
2066 p_flow_status := 'ERROR';
2067 END;
2068 END IF;
2069 END IF;
2070
2071 END LOOP;
2072
2073 END check_order_line_status;
2074
2075
2076
2077
2078 -----------------------------------------------------------------------------------------------------------
2079 -----------------------------------------------------------------------------------------------------------
2080
2081 -- Starting of Purchase price rollup and document creation moudle
2082 -- Create by Renga Kannan on 03/23/01
2083
2084 -----------------------------------------------------------------------------------------------------------
2085 -----------------------------------------------------------------------------------------------------------
2086
2087
2088
2089 -- Modified the code to rollup the price based on po validation org
2090 -- instead of receiving org
2091 -- this is decided by Product management and Development team
2092 /************************************************************************************************************************
2093
2094 This is the main API which will get called by both online and batch program
2095 This will rollup the list price for the Buy configurations, from the components selected in
2096 the order. The list price will be taken from Po Validation org. If the component/Model is not
2097 Defined in the Po validation org, the price will be taken as 0. The rolled up price of the configuration
2098 will be update in Mtl_system_items in po validation org. Apart from Rolling up list price, This procedure
2099 will also Rollup the blanket price from the model blanket and create a new blanket and ASL entries for
2100 configuration items.
2101
2102 Parameter explanations
2103
2104 P_top_model_line_id -- Top ATO model's line id
2105
2106 P_overwrite_list_price -- It can have 'Y'/'N' value. The default value is 'N'.
2107 If this parameter is passed as 'N' the list price of the
2108 configuration will not be overwritten in Po validation org.
2109 Only if the list_price_per_unit is null in po validation org
2110 the rolled up price will be updated.
2111 If this parameter is passed as 'Y', this API will update the
2112 mtl_system_items anyway
2113
2114 P_Called_in_batch -- When the purchase price rollup is done for more than one order
2115 this parameter should be set to 'Y'. If this is done online for a
2116 Single order then it should be 'N'. The default value for this is 'N'.
2117 If this paramter is 'N', the PDOI concurrent program will be
2118 Launched by this API. If it is passed as 'Y' this API will not
2119 Launch the PDOI concurrent program. The calling module will lauch in that
2120 case. IN both cased the records are inserted to PDOI interface tables by
2121 This api only.
2122
2123 p_batch_number -- The default value is null for this. If p_called_in_batch parameter is 'Y'
2124 then the calling application should pass this value. This batch number is
2125 used to populate in PDOI interface tables. If the case on online this API
2126 will generate the batch id thru sequence.
2127
2128 X_oper_unit_list -- This is a out parameter. This is a table of records. This contains all the
2129 Operating units processed by this API. In the case of on line call this output
2130 parameter will not be used by the calling application. The batch calling program
2131 will get this out parameter and uses this to launch the PDOI interface concurrent
2132 Program. The batch calling program will loop thru this table and launch the
2133 concurrent program that many times. While lauching the concurrent program it will
2134 also set the org context to the operating unit specified in this table
2135
2136
2137
2138 *************************************************************************************************************************/
2139
2140 Procedure Create_Purchasing_Doc(
2141 p_config_item_id IN Number,
2142 p_overwrite_list_price IN Varchar2 default 'N',
2143 p_called_in_batch IN Varchar2 default 'N',
2144 p_batch_number IN OUT NOCOPY Number,
2145 p_mode IN Varchar2 Default 'ORDER',
2146 p_ato_line_id IN Number default null,
2147 x_oper_unit_list IN OUT NOCOPY cto_auto_procure_pk.oper_unit_tbl,
2148 x_return_status OUT NOCOPY Varchar2,
2149 x_msg_count OUT NOCOPY Number,
2150 x_msg_data OUT NOCOPY Varchar) is
2151
2152 lStmtNumber Number;
2153 x_rolled_price Number := 0;
2154 i Number;
2155 l_batch_id Number;
2156 l_request_id Number;
2157 l_model_item_id Number;
2158 l_line_id Number;
2159
2160 Type orgs_list_type is table of number;
2161 l_orgs_list orgs_list_type;
2162 /* Get the cursor to get the config items to be rolled up */
2163 x_group_id number;
2164
2165 Cursor buy_configs is
2166 select component_item_id,
2167 line_id
2168 from bom_explosion_temp
2169 where group_id = x_group_id
2170 and configurator_flag = 'Y'
2171 order by plan_level desc; /* Check With Sajani */
2172
2173 l_comp_exists Varchar2(1);
2174
2175 begin
2176 x_return_status := FND_API.G_RET_STS_SUCCESS;
2177 lStmtNumber := 10;
2178 g_pg_level := 3;
2179 IF PG_DEBUG <> 0 THEN
2180 oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2181 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2182 || '******************************',1);
2183
2184 oe_debug_pub.add('Create_Purchasing_Doc: '
2185 || ' CREATING PURCHASING DOCUMENT ',1);
2186 oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2187
2188 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2189 || '******************************',1);
2190 oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2191 END IF;
2192
2193 IF PG_DEBUG <> 0 THEN
2194 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2195 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2196 END IF;
2197 lStmtNumber := 20;
2198 If Pg_Debug <> 0 Then
2199 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create Purchasing Doc: Before calling get_config_details',5);
2200 End if;
2201
2202
2203 l_Comp_exists := 'N';
2204
2205 CTO_TRANSFER_PRICE_PK.Get_Config_details(p_item_id => p_config_item_id,
2206 p_mode_id => 3,
2207 p_line_id => p_ato_line_id,
2208 x_group_id => x_group_id,
2209 x_return_status => x_return_status,
2210 x_msg_count => x_msg_count,
2211 x_msg_data => x_msg_data);
2212
2213 lStmtNumber := 30;
2214 For buy_configs_rec in buy_configs
2215 Loop
2216
2217 l_comp_exists := 'Y';
2218 if PG_DEBUG <> 0 Then
2219 oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2220 ||buy_configs_rec.component_item_id,5);
2221 End if;
2222 lStmtNumber := 40;
2223
2224 process_purchase_price(
2225 p_config_item_id => buy_configs_rec.component_item_id,
2226 p_group_id => x_group_id,
2227 p_batch_number => p_batch_number,
2228 p_overwrite_list_price => p_overwrite_list_price,
2229 p_line_id => buy_configs_rec.line_id,
2230 p_mode => p_mode,
2231 x_oper_unit_list => x_oper_unit_list,
2232 x_return_status => x_return_status,
2233 x_msg_count => x_msg_count,
2234 x_msg_data => x_msg_data);
2235
2236 End Loop;
2237
2238
2239
2240 /* Make a call to to purchase process for the top configuration */
2241
2242 lStmtNumber := 50;
2243 Begin
2244 select line_id
2245 into l_line_id
2246 from bom_explosion_temp
2247 where group_id = x_group_id
2248 and assembly_item_id = p_config_item_id
2249 and component_item_id = (select base_item_id
2250 from mtl_system_items
2251 where inventory_item_id = p_config_item_id
2252 and rownum =1);
2253 l_comp_exists := 'Y';
2254 Exception When no_data_found then
2255 l_comp_exists := 'N';
2256 End;
2257 If PG_DEBUG <> 0 Then
2258 oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2259 ||p_config_item_id,5);
2260 End if;
2261 lStmtNumber := 60;
2262
2263 If l_comp_exists = 'Y' then
2264 Process_Purchase_Price(
2265 p_config_item_id => p_config_item_id,
2266 p_group_id => x_group_id,
2267 p_batch_number => p_batch_number,
2268 p_overwrite_list_price => p_overwrite_list_price,
2269 p_line_id => l_line_id,
2270 p_mode => p_mode,
2271 x_oper_unit_list => x_oper_unit_list,
2272 x_return_status => x_return_status,
2273 x_msg_count => x_msg_count,
2274 x_msg_data => x_msg_data);
2275
2276 -- For each Buy/Drop ship model's call the
2277 -- List price rollup API
2278 -- We will call purchae_price_roll up also for each buy Model
2279
2280 lStmtNumber := 70;
2281 if p_called_in_batch = 'N' then
2282
2283 -- Call this API to launch the concurrent program
2284 -- This API will lauch one PDOI concurrent program per operating unit
2285 -- This will lauch the error report concurrent program also
2286
2287 lStmtNumber := 80;
2288
2289 If PG_DEBUG <> 0 Then
2290 oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Before calling Submit Pdoi',5);
2291 End if;
2292
2293 Submit_pdoi_conc_prog(
2294 p_oper_unit_list => x_oper_unit_list,
2295 p_batch_id => p_batch_number,
2296 x_return_status => x_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data);
2299
2300
2301 end if;
2302
2303 end if; /* l_comp_exits = 'Y' */
2304
2305 IF PG_DEBUG <> 0 THEN
2306 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2307 || '****************************',1);
2308
2309 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2310 oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2311
2312 oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2313 || '****************************',1);
2314 END IF;
2315 g_pg_level := g_pg_level - 3;
2316
2317 exception
2318
2319 when FND_API.G_EXC_UNEXPECTED_ERROR then
2320 IF PG_DEBUG <> 0 THEN
2321 oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::unexp error::'||lStmtNumber||sqlerrm,1);
2322 END IF;
2323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2324 CTO_MSG_PUB.Count_And_Get
2325 (p_msg_count => x_msg_count
2326 ,p_msg_data => x_msg_data
2327 );
2328
2329 when FND_API.G_EXC_ERROR then
2330 IF PG_DEBUG <> 0 THEN
2331 oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::exp error::'||lStmtNumber||sqlerrm,1);
2332 END IF;
2333 x_return_status := FND_API.G_RET_STS_ERROR;
2334 CTO_MSG_PUB.Count_And_Get
2335 (p_msg_count => x_msg_count
2336 ,p_msg_data => x_msg_data);
2337
2338 when others then
2339 IF PG_DEBUG <> 0 THEN
2340 oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::others::'||lStmtNumber||sqlerrm,1);
2341 END IF;
2342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2343 CTO_MSG_PUB.Count_And_Get
2344 (p_msg_count => x_msg_count
2345 ,p_msg_data => x_msg_data
2346 );
2347
2348 end create_purchasing_doc;
2349
2350
2351
2352
2353 /***********************************************************************************************************
2354
2355
2356 This API will rollup the list price for a given buy model from its components. If the component
2357 is not defined in the org the price will be defaulted to zero.
2358
2359
2360 Parameter Description:
2361
2362 P_line_id -- Buy Model's line id
2363
2364 p_org_id -- The organization to rollup the purchase price.
2365
2366 x_rolled_price -- The rolled pice will be returned to the calling api.
2367
2368 x_buy_comps -- This API also returns all the componenets processed for the buy model.
2369 This will be returned as table of records. This out variable will
2370 be used later for purchase price rollup for performance reason.
2371
2372
2373
2374 ************************************************************************************************************/
2375
2376
2377
2378 Procedure Rollup_list_price (
2379 p_config_item_id in Number,
2380 p_group_id in Number,
2381 p_org_id in Number,
2382 x_rolled_price out NOCOPY Number,
2383 x_return_status out NOCOPY varchar2,
2384 x_msg_count out NOCOPY number,
2385 x_msg_data out NOCOPY varchar2) is
2386
2387 lStmtNumber Number;
2388
2389
2390 -- Cursor to get all the components of the buy model
2391
2392 Cursor Purchase_comp is
2393 Select exp.component_quantity comp_qty,
2394 exp.primary_uom_code uom_code,
2395 exp.component_item_id comp_item_id,
2396 msi.primary_uom_code prim_uom_code,
2397 nvl(msi.list_price_per_unit,0) list_price_per_unit
2398 from bom_explosion_temp exp,
2399 mtl_system_items msi
2400 where exp.group_id = p_group_id
2401 and exp.assembly_item_id = p_config_item_id
2402 and exp.component_item_id = msi.inventory_item_id
2403 and msi.organization_id = p_org_id;
2404
2405 Cursor child_configs_cur is
2406 Select exp.component_item_id comp_item_id,
2407 exp.component_quantity comp_qty
2408 from bom_explosion_temp exp
2409 where exp.group_id = p_group_id
2410 and exp.assembly_item_id = p_config_item_id
2411 and exp.configurator_flag = 'Y'
2412 and not exists (select 'X'
2413 from mtl_system_items msi
2414 where msi.inventory_item_id = exp.component_item_id
2415 and msi.organization_id = p_org_id);
2416
2417 l_model_qty Number;
2418 l_model_order_uom Bom_cto_order_lines.order_quantity_uom%type;
2419 l_inventory_item_id Bom_cto_order_lines.inventory_item_id%type;
2420 l_ato_line_id bom_cto_order_lines.ato_line_id%type;
2421 l_prim_qty Number;
2422 l_price_per_unit Number;
2423 l_model_prim_uom Bom_cto_order_lines.order_quantity_uom%type;
2424 l_ratio Number;
2425 l_price Number;
2426 l_prim_uom_qty Number;
2427 i Number := 0;
2428 l_rolled_price Number;
2429 begin
2430
2431 g_pg_level := g_pg_level + 3;
2432 x_return_status := FND_API.G_RET_STS_SUCCESS;
2433 lStmtNumber := 10;
2434
2435 x_rolled_price := 0;
2436 If PG_DEBUG <> 0 Then
2437 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2438 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2439 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2440 End if;
2441
2442 lStmtNumber := 20;
2443 For pur_comp in purchase_comp
2444 Loop
2445
2446 IF PG_DEBUG <> 0 THEN
2447 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' || 'Component item id = '
2448 ||to_char(pur_comp.comp_item_id),2);
2449 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price per unit = '||pur_comp.list_price_per_unit,5);
2450 END IF;
2451
2452 lStmtNumber := 30;
2453
2454 -- Added the price by converting the correct UOM
2455
2456 if nvl( pur_comp.list_price_per_unit,0) <> 0 then
2457
2458 lStmtNumber := 40;
2459 l_prim_uom_qty := CTO_UTILITY_PK.convert_uom(
2460 from_uom => pur_comp.uom_code,
2461 to_uom => pur_comp.prim_uom_code,
2462 quantity => pur_comp.comp_qty,
2463 item_id => pur_comp.comp_item_id);
2464 l_price := l_prim_uom_qty * pur_comp.list_price_per_unit;
2465 If PG_DEBUG <> 0 Then
2466 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Primary UOM quantity = '||l_prim_uom_qty,5);
2467 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price for order qty = '||l_price,5);
2468 End if;
2469 x_rolled_price := x_rolled_price + l_price;
2470 else
2471 IF PG_DEBUG <> 0 THEN
2472 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price is defined as 0',5);
2473 END IF;
2474
2475 end if;
2476
2477 lStmtNumber := 50;
2478 If PG_DEBUG <> 0 Then
2479 oe_debug_pub.add(lpad(' ',g_pg_level)
2480 ||'ROLLUP_LIST_PRICE : Get child configs rollup price, which are not enabled in this org',5);
2481 end if;
2482
2483 For child_configs in child_configs_cur
2484 Loop
2485 If PG_DEBUG <> 0 Then
2486 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Processing Child config = '
2487 ||child_configs.comp_item_id,5);
2488 End if;
2489 lStmtNumber := 60;
2490 Rollup_list_price (
2491 p_config_item_id => child_configs.comp_item_id,
2492 p_group_id => p_group_id,
2493 p_org_id => p_org_id,
2494 x_rolled_price => l_rolled_price,
2495 x_return_status => x_return_status,
2496 x_msg_count => x_msg_count,
2497 x_msg_data => x_msg_data);
2498
2499 lStmtNumber := 70;
2500 x_rolled_price := x_rolled_price + l_rolled_price*child_configs.comp_qty; /* Renga need to add qty */
2501 IF PG_DEBUG <> 0 Then
2502 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Configs rolled up price = '||l_rolled_price,5);
2503 End if;
2504 End Loop;
2505 End Loop;
2506
2507
2508 IF PG_DEBUG <> 0 THEN
2509 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up organization = '||to_char(p_org_id),2);
2510 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up price = '
2511 ||to_char(x_rolled_price),2);
2512 END IF;
2513 g_pg_level := g_pg_level - 3;
2514 exception
2515
2516 when FND_API.G_EXC_UNEXPECTED_ERROR then
2517 IF PG_DEBUG <> 0 THEN
2518 oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::unexp error::'||lStmtNumber||sqlerrm,1);
2519 END IF;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 CTO_MSG_PUB.Count_And_Get
2522 (p_msg_count => x_msg_count
2523 ,p_msg_data => x_msg_data
2524 );
2525 g_pg_level := g_pg_level - 3;
2526
2527 when FND_API.G_EXC_ERROR then
2528 IF PG_DEBUG <> 0 THEN
2529 oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::exp error::'||lStmtNumber||sqlerrm,1);
2530 END IF;
2531 x_return_status := FND_API.G_RET_STS_ERROR;
2532 CTO_MSG_PUB.Count_And_Get
2533 (p_msg_count => x_msg_count
2534 ,p_msg_data => x_msg_data);
2535 g_pg_level := g_pg_level - 3;
2536
2537 when others then
2538 IF PG_DEBUG <> 0 THEN
2539 oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::others::'||lStmtNumber||sqlerrm,1);
2540 END IF;
2541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2542 CTO_MSG_PUB.Count_And_Get
2543 (p_msg_count => x_msg_count
2544 ,p_msg_data => x_msg_data
2545 );
2546 g_pg_level := g_pg_level - 3;
2547
2548 END Rollup_list_price;
2549
2550
2551
2552
2553 --- This procedure will do the rollup based on purchasing documents
2554 --- for all buy configurations
2555
2556 /*********************************************************************************************************************
2557
2558 This API will rollup the purchasing price based on model ASL. It will
2559 also insert records into PDOI tables to create necessary purchasing documents
2560 for configurations item.
2561
2562
2563 ***********************************************************************************************************************/
2564
2565
2566 Procedure Rollup_purchase_price (
2567 p_config_item_id in Number,
2568 p_batch_id in out NOCOPY Number,
2569 p_group_id in Number,
2570 p_mode IN Varchar2 Default 'ORDER',
2571 p_line_id in number,
2572 x_oper_unit_list in out NOCOPY cto_auto_procure_pk.oper_unit_tbl,
2573 x_return_status out NOCOPY varchar2,
2574 x_msg_count out NOCOPY number,
2575 x_msg_data out NOCOPY varchar2) is
2576
2577 lStmtNumber Number;
2578 x_model_vendors PO_AUTOSOURCE_SV.vendor_record_details;
2579 x_config_vendors PO_AUTOSOURCE_SV.vendor_record_details;
2580 l_model_vendors PO_AUTOSOURCE_SV.vendor_record_details;
2581 l_doc_header_id Number;
2582 l_doc_type_code Varchar2(20);
2583 l_doc_line_num Number;
2584 l_doc_line_id Number;
2585 l_vendor_contact_id Number;
2586 -- 4283726 l_vendor_product_num Varchar2(50);
2587 l_vendor_product_num po_approved_supplier_list.primary_vendor_item%type; -- 4283726
2588 l_buyer_id Number;
2589 -- 4283726 l_purchase_uom Varchar2(10);
2590 l_purchase_uom po_asl_attributes.purchasing_unit_of_measure%type; -- 4283726
2591 x_rolled_price Number;
2592 l_doc_return Varchar2(5);
2593 i Number;
2594 x_int_header_id Number;
2595 x_segment1 mtl_system_items.segment1%type;
2596 l_assgn_set_id Number;
2597 x_start_date Date;
2598 x_end_date Date;
2599 l_doc_exists Boolean;
2600 x_index Number;
2601 x_org_id Po_headers_all.org_id%type;
2602 l_model_item_id Number;
2603 l_config_exists Varchar2(1);
2604 l_po_valid_org Number;
2605 l_buy_found Varchar(1);
2606 l_config_creation Number;
2607 begin
2608 x_return_status := FND_API.G_RET_STS_SUCCESS;
2609 l_assgn_set_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
2610 lStmtNumber := 10;
2611
2612 g_pg_level := g_pg_level + 3;
2613 If pg_debug <> 0 then
2614 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Inside Rollup Purchase Price API',1);
2615 End if;
2616 select base_item_id,
2617 config_orgs
2618 into l_model_item_id,
2619 l_config_creation
2620 from mtl_system_items
2621 where inventory_item_id = p_config_item_id
2622 and rownum =1;
2623 IF PG_DEBUG <> 0 THEN
2624 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
2625 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: '||'Model item id = '||l_model_item_id,1);
2626 END IF;
2627
2628
2629 -- Get the vendor and vendor site information from
2630 -- PO. This PO API will return all the vendor,vendor site and
2631 -- Asl id for the model.
2632 lStmtNumber := 20;
2633
2634 lStmtNumber := 30;
2635 PO_AUTOSOURCE_SV.get_all_item_asl(
2636 x_item_id => l_model_item_id,
2637 X_using_organization_id => -1,
2638 x_vendor_details => l_model_vendors,
2639 x_return_status => x_return_status,
2640 x_msg_count => x_msg_count,
2641 x_msg_data => x_msg_data);
2642
2643 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2644 IF PG_DEBUG <> 0 THEN
2645 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
2646 END IF;
2647 raise FND_API.G_EXC_ERROR;
2648
2649 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2650 IF PG_DEBUG <> 0 THEN
2651 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
2652 END IF;
2653 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2654
2655 END IF;
2656
2657 lStmtNumber := 40;
2658 if l_model_vendors.count = 0 then
2659 IF PG_DEBUG <> 0 THEN
2660 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: No ASL defined for model...',5);
2661 END IF;
2662 g_pg_level := g_pg_level - 3;
2663 return;
2664 end if;
2665
2666 IF PG_DEBUG <> 0 THEN
2667 oe_debug_pub.add('ROLLUP_PURCHASE_PRICE: ' || 'Calling get_all_item_asl for config..',1);
2668 END IF;
2669 -- Get all the ASL's Defined for Config item (You may have some
2670 -- in the case of matching)
2671 lstmtNumber := 50;
2672
2673 PO_AUTOSOURCE_SV.get_all_item_asl(
2674 x_item_id => p_config_item_id,
2675 x_using_organization_id => -1,
2676 x_vendor_details => x_config_vendors,
2677 x_return_status => x_return_status,
2678 x_msg_count => x_msg_count,
2679 x_msg_data => x_msg_data);
2680
2681
2682 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2683 IF PG_DEBUG <> 0 THEN
2684 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
2685 END IF;
2686 raise FND_API.G_EXC_ERROR;
2687
2688 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2689 IF PG_DEBUG <> 0 THEN
2690 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
2691 END IF;
2692 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2693
2694 END IF;
2695
2696
2697 IF PG_DEBUG <> 0 THEN
2698 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Started looping....',5);
2699 END IF;
2700 Reduce_vendor_by_ou(
2701 p_vendor_details => l_model_vendors,
2702 p_config_item_id => p_config_item_id,
2703 p_line_id => p_line_id,
2704 p_mode => p_mode,
2705 x_vendor_details => x_model_vendors);
2706 -- x_model_vendors := l_model_vendors;
2707 i := x_model_vendors.first;
2708
2709 if PG_DEBUG <> 0 then
2710 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: BEGIN ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
2711 end if;
2712
2713 -- Start processing each vendor and vendor sites for th model ASL
2714 lStmtNumber := 60;
2715 while (i is not null)
2716 loop
2717 IF PG_DEBUG <> 0 THEN
2718 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
2719 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
2720 ||to_char(x_model_vendors(i).vendor_id),5);
2721
2722 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
2723 ||to_char(x_model_vendors(i).vendor_site_id),1);
2724
2725 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
2726 ||to_char(x_model_vendors(i).asl_id),1);
2727
2728 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Index variable ='||to_char(i),1);
2729 END IF;
2730 -- CAll the custom API with Vendor and Vendor site information
2731 -- If the custom API returns TRUE we should not do anything, It is assumed
2732 -- That the custom API would have taken care of everything. IN this case we will skip this
2733 -- vendor and vendor site and go with the next one.
2734
2735 -- If the custom API returns 'FALSE' we will process this record.
2736 lStmtNumber := 70;
2737 If CTO_CUSTOM_PURCHASE_PRICE_PK.Get_Purchase_price(
2738 p_item_id => p_config_item_id,
2739 p_vendor_id => x_model_vendors(i).vendor_id,
2740 p_vendor_site_id => x_model_vendors(i).vendor_site_id) then
2741 IF PG_DEBUG <> 0 THEN
2742 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Custom API returned value...',1);
2743 END IF;
2744 else
2745
2746 l_purchase_uom := x_model_vendors(i).Purchasing_uom;
2747 l_vendor_product_num := x_model_vendors(i).Vendor_product_num ;
2748
2749
2750 -- Modified by Renga Kannan on 04/16/2002
2751 -- Check if the config has valid asl and blanket for this
2752 -- Vendor and vendor site. If it does not have then
2753 -- We should do rollup for that
2754 lStmtNumber := 80;
2755
2756 config_asl_exists(
2757 p_vendor_id => x_model_vendors(i).vendor_id,
2758 p_vendor_site_id => x_model_vendors(i).vendor_site_id,
2759 p_vendor_list => x_config_vendors,
2760 x_asl_found => l_doc_exists,
2761 x_index => x_index);
2762
2763 if l_doc_exists then
2764 IF PG_DEBUG <> 0 THEN
2765 oe_debug_pub.add(lpad(' ',g_pg_level)
2766 ||'ROLLUP_PURCHASE_PRICE: ASL exists for blanket, checking to see the blanket..',5);
2767 END IF;
2768
2769 l_doc_line_id := null;
2770 l_doc_header_id := null;
2771
2772 lStmtNumber := 90;
2773
2774 PO_AUTOSOURCE_SV.blanket_document_sourcing(
2775 x_item_id => p_config_item_id,
2776 x_vendor_id => x_config_vendors(x_index).vendor_id,
2777 x_vendor_site_id => x_config_vendors(x_index).vendor_site_id,
2778 x_asl_id => x_config_vendors(x_index).asl_id,
2779 x_destination_doc_type => null,
2780 x_organization_id => -1,
2781 x_currency_code => null,
2782 x_item_rev => null,
2783 x_autosource_date => null,
2784 x_document_header_id => l_doc_header_id,
2785 x_document_type_code => l_doc_type_code,
2786 x_document_line_num => l_doc_line_num,
2787 x_document_line_id => l_doc_line_id,
2788 x_vendor_contact_id => l_vendor_contact_id,
2789 x_vendor_product_num => l_vendor_product_num,
2790 x_buyer_id => l_buyer_id,
2791 x_purchasing_uom => l_purchase_uom,
2792 x_multi_org => 'Y',
2793 x_doc_return => l_doc_return,
2794 x_return_status => x_return_status,
2795 x_msg_count => x_msg_count,
2796 x_msg_data => x_msg_data);
2797
2798 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2799 IF PG_DEBUG <> 0 THEN
2800 oe_debug_pub.add(lpad(' ',g_pg_level)
2801 ||'ROLLUP_PURCHASE_PRICE: Expected Error in Blanket_Document_sourcing.',1);
2802 END IF;
2803 raise FND_API.G_EXC_ERROR;
2804
2805 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2806 IF PG_DEBUG <> 0 THEN
2807 oe_debug_pub.add(lpad(' ',g_pg_level)
2808 ||'ROLLUP_PURCHASE_PRICE: UnExpected Error in blanket_document_sourcing.',1);
2809 END IF;
2810 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2811
2812 END IF;
2813
2814 lStmtNumber := 100;
2815 If l_doc_return = 'Y' then
2816 IF PG_DEBUG <> 0 THEN
2817 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Valid Blanket found for config ..',2);
2818 END IF;
2819 l_doc_exists := TRUE;
2820 else
2821 IF PG_DEBUG <> 0 THEN
2822 oe_debug_pub.add(lpad(' ',g_pg_level)
2823 ||'ROLLUP_PURCHASE_PRICE: Valid Blanket not found for this config',2);
2824 END IF;
2825 l_doc_exists := FALSE;
2826 end if;
2827
2828 end if; /* If l_doc_exists */
2829
2830 lStmtNumber := 110;
2831 if l_doc_exists then
2832 IF PG_DEBUG <> 0 THEN
2833 oe_debug_pub.add(lpad(' ',g_pg_level)
2834 ||'ROLLUP_PURCHAE_PRICE: Valid Asl blanket already exists for configuration....',2);
2835
2836 END IF;
2837 elsif x_model_vendors(i).vendor_site_id is null then
2838 IF PG_DEBUG <> 0 THEN
2839 oe_debug_pub.add(lpad(' ',g_pg_level)
2840 ||'ROLLUP_PURCHASE_PRICE: Vendor site id is null need not process..',2);
2841 END IF;
2842 else
2843 l_doc_line_id := null;
2844 l_doc_header_id := null;
2845 lStmtNumber := 120;
2846 PO_AUTOSOURCE_SV.blanket_document_sourcing(
2847 x_item_id => l_model_item_id,
2848 x_vendor_id => x_model_vendors(i).vendor_id,
2849 x_vendor_site_id => x_model_vendors(i).vendor_site_id,
2850 x_asl_id => x_model_vendors(i).asl_id,
2851 x_destination_doc_type => null,
2852 x_organization_id => -1,
2853 x_currency_code => null,
2854 x_item_rev => null,
2855 x_autosource_date => null,
2856 x_document_header_id => l_doc_header_id,
2857 x_document_type_code => l_doc_type_code,
2858 x_document_line_num => l_doc_line_num,
2859 x_document_line_id => l_doc_line_id,
2860 x_vendor_contact_id => l_vendor_contact_id,
2861 x_vendor_product_num => l_vendor_product_num,
2862 x_buyer_id => l_buyer_id,
2863 x_purchasing_uom => l_purchase_uom,
2864 x_multi_org => 'Y',
2865 x_doc_return => l_doc_return,
2866 x_return_status => x_return_status,
2867 x_msg_count => x_msg_count,
2868 x_msg_data => x_msg_data);
2869
2870
2871 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2872 IF PG_DEBUG <> 0 THEN
2873 oe_debug_pub.add(lpad(' ',g_pg_level)
2874 ||'success status false for po_autosource_sv.blanket_document_sourcing...',1);
2875
2876 END IF;
2877
2878 return ;
2879
2880 END IF;
2881 lStmtNumber := 130;
2882
2883 If l_doc_return = 'N' then
2884 IF PG_DEBUG <> 0 THEN
2885 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHAES_PRICE: No blankets returned..',2);
2886 END IF;
2887 else
2888 IF PG_DEBUG <> 0 THEN
2889 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
2890 ||to_char(l_doc_line_id),2);
2891
2892 oe_debug_pub.add(lpad(' ',g_pg_level)
2893 ||'ROLLUP_PURCHASE_PRICE: ROLLUP_PURCHASE_PRICE: Blanket doc header id ='
2894 ||to_char(l_doc_header_id),2);
2895 END IF;
2896
2897 if config_exists_in_blanket(
2898 p_config_item_id => p_config_item_id,
2899 p_doc_header_id => l_doc_header_id)
2900 then
2901
2902 IF PG_DEBUG <> 0 THEN
2903 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE '
2904 || 'Config line already exists in blanket..',2);
2905 END IF;
2906 else
2907 lStmtNumber := 140;
2908 Begin
2909
2910 select 'Y',organization_id
2911 into l_config_exists,l_po_valid_org
2912 from mtl_system_items
2913 where inventory_item_id = p_config_item_id
2914 and organization_id = (select fsp.inventory_organization_id
2915 from financials_system_params_all fsp,
2916 po_headers_all poh
2917 where poh.po_header_id = l_doc_header_id
2918 and fsp.org_id = poh.org_id);
2919 Exception when no_data_found then
2920 l_config_exists := 'N';
2921 if pg_debug <> 0 Then
2922 oe_debug_pub.add(lpad(' ',g_pg_level)
2923 ||'ROLLUP_PURCHASE_PRICE: Config item does not exist in Po validation org. ',5);
2924 End if;
2925 End;
2926
2927 If l_config_exists = 'Y' then
2928 lStmtNumber := 150;
2929 rollup_blanket_price(
2930 p_config_item_id => p_config_item_id,
2931 p_doc_header_id => l_doc_header_id,
2932 p_doc_line_id => l_doc_line_id,
2933 p_group_id => p_group_id,
2934 p_po_valid_org => l_po_valid_org,
2935 x_rolled_price => x_rolled_price,
2936 x_return_status => x_return_status,
2937 x_msg_count => x_msg_count,
2938 x_msg_data => x_msg_data);
2939
2940 oe_debug_pub.add('Rolled up blanket price =
2941 '||x_rolled_price);
2942
2943 lStmtNumber := 160;
2944
2945 insert_blanket_header(
2946 p_doc_header_id => l_doc_header_id,
2947 p_batch_id => p_batch_id,
2948 x_int_header_id => x_int_header_id,
2949 x_org_id => x_org_id,
2950 x_return_status => x_return_status,
2951 x_msg_count => x_msg_count,
2952 x_msg_data => x_msg_data);
2953 lStmtNumber := 170;
2954
2955 select segment1
2956 into x_segment1
2957 from Mtl_system_items
2958 where inventory_item_id = p_config_item_id
2959 and rownum = 1;
2960
2961 IF PG_DEBUG <> 0 THEN
2962 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Item Name ='||x_segment1,2);
2963 END IF;
2964 lStmtNumber := 180;
2965 Derive_start_end_date(
2966 p_item_id => p_config_item_id,
2967 p_vendor_id => x_model_vendors(i).vendor_id,
2968 p_vendor_site_id => x_model_vendors(i).vendor_site_id,
2969 p_assgn_set_id => l_assgn_set_id ,
2970 x_start_date => x_start_date,
2971 x_end_date => x_end_date);
2972 lStmtNumber := 190;
2973 insert_blanket_line(
2974 p_doc_line_id => l_doc_line_id,
2975 p_item_id => p_config_item_id,
2976 p_item_rev => null,
2977 p_price => x_rolled_price,
2978 p_int_header_id => x_int_header_id,
2979 p_segment1 => x_segment1,
2980 p_start_date => x_start_date,
2981 p_end_date => x_end_date,
2982 x_return_status => x_return_status,
2983 x_msg_count => x_msg_count,
2984 x_msg_data => x_msg_data);
2985
2986 -- Record the operating unit in a table
2987 -- This tbale is used for launching the concurrent program
2988
2989 IF PG_DEBUG <> 0 THEN
2990 oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket Operating unit = '
2991 ||to_char(x_org_id),2);
2992 END IF;
2993
2994 -- We should take the OU from Blanket instead of rcv org OU.
2995 lStmtNumber := 200;
2996 if(not x_oper_unit_list.exists(x_org_id)) then
2997
2998 x_oper_unit_list(x_org_id).oper_unit := x_org_id;
2999 -- The following global assignment is added becase Pro*c
3000 -- Cannot pass/receive record of tables.
3001 Cto_auto_procure_pk.G_oper_unit_list(x_org_id).oper_unit := x_org_id;
3002 end if;
3003 end if; /* If l_config_exists = 'Y' */
3004
3005 end if; /* if config_exists_in_blanket */
3006 end if; /* l_doc_return = 'N' */
3007 end if; /* l_doc_exists */
3008 end if; /* CTO_CUSTOM_PURCHASE_PRICE_PK.Get_Purchase_price */
3009 i := x_model_vendors.next(i);
3010
3011 end loop;
3012
3013 if PG_DEBUG <> 0 then
3014 oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3015 oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: END ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3016 end if;
3017
3018 x_return_status := FND_API.G_RET_STS_SUCCESS;
3019 g_pg_level := g_pg_level - 3;
3020
3021 exception
3022
3023 when FND_API.G_EXC_UNEXPECTED_ERROR then
3024 IF PG_DEBUG <> 0 THEN
3025 oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::unexp error::'||lStmtNumber||sqlerrm,1);
3026 END IF;
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028 CTO_MSG_PUB.Count_And_Get
3029 (p_msg_count => x_msg_count
3030 ,p_msg_data => x_msg_data
3031 );
3032
3033 when FND_API.G_EXC_ERROR then
3034 IF PG_DEBUG <> 0 THEN
3035 oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::exp error::'||lStmtNumber||sqlerrm,1);
3036 END IF;
3037 x_return_status := FND_API.G_RET_STS_ERROR;
3038 CTO_MSG_PUB.Count_And_Get
3039 (p_msg_count => x_msg_count
3040 ,p_msg_data => x_msg_data);
3041
3042 when others then
3043 IF PG_DEBUG <> 0 THEN
3044 oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::others::'||lStmtNumber||sqlerrm,1);
3045 END IF;
3046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3047 CTO_MSG_PUB.Count_And_Get
3048 (p_msg_count => x_msg_count
3049 ,p_msg_data => x_msg_data
3050 );
3051
3052 end Rollup_purchase_price;
3053
3054
3055
3056
3057 Procedure config_asl_exists(
3058 p_vendor_id IN Number,
3059 p_vendor_site_id IN Number,
3060 p_vendor_list IN PO_AUTOSOURCE_SV.vendor_record_details,
3061 x_asl_found OUT NOCOPY Boolean,
3062 x_index OUT NOCOPY Number) is
3063
3064 i Number;
3065 begin
3066 x_asl_found := FALSE;
3067 i := p_vendor_list.first;
3068 while (i is not null)
3069 loop
3070
3071 if p_vendor_list(i).vendor_id = p_vendor_id and
3072 p_vendor_list(i).vendor_site_id = p_vendor_site_id
3073 then
3074 x_asl_found := TRUE;
3075 x_index := i;
3076 exit;
3077 end if;
3078 i := p_vendor_list.next(i);
3079 end loop;
3080
3081
3082 end config_asl_exists;
3083
3084
3085 Function config_exists_in_blanket(
3086 p_config_item_id IN Number,
3087 p_doc_header_id IN Number) return boolean is
3088
3089 line_exists varchar2(1) := 'N';
3090 begin
3091
3092 Select 'X'
3093 into line_exists
3094 From po_lines_all pol,
3095 Po_headers_all poh
3096 Where
3097 poh.type_lookup_code = 'BLANKET'
3098 AND poh.approved_flag = 'Y'
3099 AND nvl(poh.closed_code,'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
3100 AND nvl(pol.closed_code, 'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
3101 AND nvl(poh.cancel_flag,'N') = 'N'
3102 AND nvl(poh.frozen_flag,'N') = 'N'
3103 AND trunc(nvl(pol.expiration_date, sysdate + 1)) > trunc(sysdate)
3104 AND nvl(pol.cancel_flag,'N') = 'N'
3105 AND poh.po_header_id = p_doc_header_id
3106 AND pol.po_header_id = poh.po_header_id
3107 AND pol.item_id = p_config_item_id;
3108
3109 return True;
3110
3111 exception
3112 when no_data_found then
3113 return False;
3114 when others then
3115 IF PG_DEBUG <> 0 THEN
3116 oe_debug_pub.add('config_exists_in_blanket: ' || 'When others error occured in sql ..',1);
3117 END IF;
3118
3119 end config_exists_in_blanket;
3120
3121
3122
3123 Procedure rollup_blanket_price(
3124 p_config_item_id in number,
3125 p_doc_header_id in number,
3126 p_doc_line_id in number,
3127 p_group_id in number,
3128 p_po_valid_org in Number,
3129 p_mode IN Varchar2 Default 'ORDER',
3130 x_rolled_price out NOCOPY number,
3131 x_return_status out NOCOPY varchar2,
3132 x_msg_count out NOCOPY number,
3133 x_msg_data out NOCOPY varchar2) is
3134
3135 l_unit_price Number;
3136 l_switch Boolean := TRUE;
3137 l_model_qty Number := 0;
3138 l_ratio Number;
3139 l_prim_uom_qty Number;
3140 l_prim_uom Bom_cto_order_lines.order_quantity_uom%type;
3141 l_po_uom_code po_lines_all.unit_meas_lookup_code%type;
3142 l_po_uom Bom_cto_order_lines.order_quantity_uom%type;
3143 l_conv_qty Number;
3144 l_model_po_uom Bom_cto_order_lines.order_quantity_uom%type;
3145 l_model_order_uom Bom_cto_order_lines.order_quantity_uom%type;
3146 l_po_uom_qty Number;
3147 l_config_item_id Mtl_system_items.inventory_item_id%type;
3148 i Number;
3149 Cursor buy_comps_cur is
3150 select exp.component_item_id comp_item_id,
3151 exp.component_quantity comp_qty,
3152 exp.primary_uom_code uom_code,
3153 exp.configurator_flag config_flag
3154 from bom_explosion_temp exp
3155 where group_id = p_group_id
3156 and assembly_item_id = p_config_item_id;
3157 l_rollup_price Number;
3158
3159 l_base_model_id number;
3160
3161 begin
3162
3163 g_pg_level := g_pg_level + 3;
3164
3165 x_rolled_price := 0;
3166
3167
3168 If p_doc_line_id is not null then
3169
3170 select base_item_id
3171 into l_base_model_id
3172 from mtl_system_items
3173 where inventory_item_id = p_config_item_id
3174 and rownum = 1;
3175
3176 oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3177 oe_debug_pub.add(lpad(' ',g_pg_level) || 'Po doc line id = '||p_doc_line_id,5);
3178
3179 End if;
3180
3181 For buy_comps in buy_comps_cur
3182 Loop
3183
3184 Begin
3185
3186 -- As per PO team and Val if more than one record
3187 -- found for the same item in blanket we will be taking
3188 -- the first row. We are expecting the ct. not to have
3189 -- more than one row for the same item. This will be documented
3190
3191 Select pol.unit_price,
3192 muom.uom_code
3193 into l_unit_price,
3194 l_po_uom
3195 From po_lines_all pol,
3196 Po_headers_all poh,
3197 mtl_units_of_measure muom
3198 Where
3199 poh.type_lookup_code = 'BLANKET'
3200 AND poh.approved_flag = 'Y'
3201 AND nvl(poh.closed_code,'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
3202 AND nvl(pol.closed_code, 'OPEN') NOT IN('FINALLY CLOSED','CLOSED')
3203 AND nvl(poh.cancel_flag,'N') = 'N'
3204 AND nvl(poh.frozen_flag,'N') = 'N'
3205 AND trunc(nvl(pol.expiration_date, sysdate + 1)) > trunc(sysdate)
3206 AND nvl(pol.cancel_flag,'N') = 'N'
3207 AND poh.po_header_id = p_doc_header_id
3208 AND pol.po_header_id = poh.po_header_id
3209 AND pol.item_id = buy_comps.comp_item_id
3210 AND ( (p_doc_line_id is null)
3211 or (buy_comps.comp_item_id <> l_base_model_id)
3212 or (pol.po_line_id = p_doc_line_id)
3213 )
3214 AND muom.unit_of_measure = unit_meas_lookup_code
3215 AND rownum = 1; -- Added by renga Kannan on 04/15/02
3216 l_conv_qty := CTO_UTILITY_PK.convert_uom(
3217 from_uom => buy_comps.uom_code,
3218 to_uom => l_po_uom,
3219 quantity => buy_comps.comp_qty,
3220 item_id => buy_comps.comp_item_id);
3221 l_unit_price := l_unit_price*l_Conv_qty;
3222 Exception when no_data_found then
3223 If buy_comps.config_flag = 'N' then
3224 Begin
3225 select nvl(list_price_per_unit,0),
3226 primary_uom_code
3227 into l_unit_price,
3228 l_po_uom
3229 from mtl_system_items
3230 where inventory_item_id = buy_comps.comp_item_id
3231 and organization_id = p_po_valid_org;
3232 Exception when no_data_found then
3233 l_unit_price := 0;
3234 End;
3235 If l_unit_price <> 0 then
3236 -- Comvert the UOM here
3237 l_conv_qty := CTO_UTILITY_PK.convert_uom(
3238 from_uom => buy_Comps.uom_code,
3239 to_uom => l_po_uom,
3240 quantity => buy_comps.comp_qty,
3241 item_id => buy_comps.comp_item_id);
3242
3243 else
3244 l_conv_qty := 0;
3245 end if;
3246 l_unit_price := l_unit_price * l_conv_qty;
3247 elsif buy_comps.config_flag = 'Y' then
3248 rollup_blanket_price(
3249 p_config_item_id => buy_comps.comp_item_id,
3250 p_doc_header_id => p_doc_header_id,
3251 p_doc_line_id => null,
3252 p_group_id => p_group_id,
3253 p_po_valid_org => p_po_valid_org,
3254 x_rolled_price => l_unit_price,
3255 x_return_status => x_return_status,
3256 x_msg_count => x_msg_count,
3257 x_msg_data => x_msg_data);
3258 l_unit_price := l_unit_price * buy_comps.comp_qty;
3259 end if; /* buy_comps.config_flag = 'N' */
3260 End;
3261 x_rolled_price := nvl(x_rolled_price,0) + l_unit_price;
3262
3263 IF PG_DEBUG <> 0 THEN
3264 oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Item Id = '|| buy_comps.comp_item_id,1);
3265
3266 oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Blanket price ='|| to_char(l_unit_price),1);
3267 END IF;
3268
3269 End loop; /* Buy_comps */
3270
3271 g_pg_level := g_pg_level - 3;
3272
3273 end rollup_blanket_price;
3274
3275
3276
3277
3278 procedure insert_blanket_header(
3279 p_doc_header_id IN Number,
3280 p_batch_id IN OUT NOCOPY Number,
3281 x_int_header_id Out NOCOPY Number,
3282 x_org_id OUT NOCOPY po_headers_all.org_id%type,
3283 x_return_status OUT NOCOPY varchar2,
3284 x_msg_count OUT NOCOPY Number,
3285 x_msg_data OUT NOCOPY varchar2) is
3286 begin
3287
3288 g_pg_level := g_pg_level + 3;
3289
3290 select po_headers_interface_s.nextval
3291 into x_int_header_id
3292 from dual;
3293
3294 If nvl(p_batch_id,-1) = -1 Then
3295 p_batch_id := x_int_header_id;
3296 end if;
3297
3298 IF PG_DEBUG <> 0 THEN
3299 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Interface header id ='||to_char(x_int_header_id),1);
3300 END IF;
3301
3302
3303 Insert into Po_headers_interface(
3304 interface_header_id,
3305 batch_id,
3306 process_code,
3307 action,
3308 org_id,
3309 document_type_code,
3310 document_num,
3311 po_header_id,
3312 currency_code,
3313 rate_type,
3314 rate_date,
3315 rate,
3316 agent_id,
3317 vendor_id,
3318 vendor_site_id,
3319 vendor_contact_id,
3320 ship_to_location_id,
3321 bill_to_location_id,
3322 terms_id,
3323 note_to_vendor,
3324 note_to_receiver,
3325 acceptance_required_flag,
3326 min_release_amount,
3327 frozen_flag,
3328 closed_code,
3329 reply_date,
3330 ussgl_transaction_code,
3331 load_sourcing_rules_flag,
3332 global_agreement_flag ) /* BUG#2726167 populate global_agreement_flag */
3333 select
3334 x_int_header_id,
3335 p_batch_id,
3336 'PENDING',
3337 'UPDATE',
3338 poh.org_id,
3339 poh.type_lookup_code,
3340 poh.segment1,
3341 poh.po_header_id,
3342 poh.currency_code,
3343 poh.rate_type,
3344 poh.rate_date,
3345 poh.rate,
3346 poh.agent_id,
3347 poh.vendor_id,
3348 poh.vendor_site_id,
3349 poh.vendor_contact_id,
3350 poh.ship_to_location_id,
3351 poh.bill_to_location_id,
3352 poh.terms_id,
3353 poh.note_to_vendor,
3354 poh.note_to_receiver,
3355 poh.acceptance_required_flag,
3356 poh.min_release_amount,
3357 poh.frozen_flag,
3358 poh.closed_code,
3359 poh.reply_date,
3360 poh.ussgl_transaction_code,
3361 'Y',
3362 global_agreement_flag /* BUG#2726167 populate global_agreement_flag */
3363 From Po_headers_all poh
3364 where poh.po_header_id = p_doc_header_id;
3365
3366 select org_id
3367 into x_org_id
3368 from po_headers_all
3369 where po_header_id = p_doc_header_id;
3370
3371
3372 IF PG_DEBUG <> 0 THEN
3373 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
3374
3375 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
3376 END IF;
3377
3378 g_pg_level := g_pg_level - 3;
3379
3380 End insert_blanket_header;
3381
3382
3383 procedure insert_blanket_line(
3384 p_doc_line_id IN Number,
3385 p_item_id IN Number,
3386 p_item_rev IN Varchar2,
3387 p_price IN Number,
3388 p_int_header_id IN Number,
3389 p_segment1 IN Mtl_system_items.segment1%type,
3390 p_start_date IN Date,
3391 p_end_date IN Date,
3392 x_return_status OUT NOCOPY Varchar2,
3393 x_msg_count OUT NOCOPY Number,
3394 x_msg_data OUT NOCOPY varchar2) is
3395
3396 l_interface_line_id Number;
3397 l_segment1 Mtl_system_items.segment1%type;
3398
3399 begin
3400
3401 g_pg_level := g_pg_level + 3;
3402
3403 select segment1
3404 into l_segment1
3405 from mtl_system_items
3406 where inventory_item_id = p_item_id
3407 and rownum=1;
3408
3409
3410
3411 IF PG_DEBUG <> 0 THEN
3412 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3413
3414 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3415
3416 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3417 END IF;
3418
3419 -- Bug fix 3589150
3420 -- Done by Renga Kannan on 04/30/04
3421 -- Allow_price_override_flag and not_to_exceed_price should not be
3422 -- copied from model as it will not make any sense. And there
3423 -- no way to derive these fileds also.
3424 Insert into po_lines_interface(
3425 interface_line_id,
3426 interface_header_id,
3427 Line_num,
3428 line_type_id,
3429 item_id,
3430 item, ---- As per beth I am adding this
3431 item_revision,
3432 category_id,
3433 unit_of_measure,
3434 quantity,
3435 -- commited_acount,
3436 min_order_quantity,
3437 max_order_quantity,
3438 unit_price,
3439 negotiated_by_preparer_flag,
3440 un_number_id,
3441 hazard_class_id,
3442 note_to_vendor,
3443 taxable_flag,
3444 tax_name,
3445 --type_1099,
3446 -- terms_id,
3447 price_type,
3448 min_release_amount,
3449 price_break_lookup_code,
3450 ussgl_transaction_code,
3451 closed_date,
3452 tax_code_id,
3453 effective_date,
3454 expiration_date)
3455 select
3456 po_lines_interface_s.nextval,
3457 p_int_header_id,
3458 null,
3459 pol.line_type_id,
3460 p_item_id,
3461 p_segment1,
3462 null,
3463 pol.category_id,
3464 pol.unit_meas_lookup_code,
3465 pol.quantity,
3466 -- pol.commited_amount,
3467 pol.min_order_quantity,
3468 pol.max_order_quantity,
3469 p_price,
3470 decode(pol.negotiated_by_preparer_flag,'X',null,pol.negotiated_by_preparer_flag),
3471 pol.un_number_id,
3472 pol.hazard_class_id,
3473 pol.note_to_vendor,
3474 pol.taxable_flag,
3475 pol.tax_name,
3476 --pol.type_1099,
3477 -- pol.terms_id,
3478 pol.price_type_lookup_code,
3479 pol.min_release_amount,
3480 pol.price_break_lookup_code,
3481 pol.ussgl_transaction_code,
3482 pol.closed_date,
3483 pol.tax_code_id,
3484 decode(poh.start_date,null,p_start_date,poh.start_date),
3485 decode(poh.end_date,null,p_end_date,poh.end_date)
3486 from po_lines_all pol,
3487 po_headers_all poh
3488 where pol.po_line_id =p_doc_line_id
3489 and poh.po_header_id = pol.po_header_id;
3490
3491 IF PG_DEBUG <> 0 THEN
3492 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in lines = '||to_char(sql%rowcount),1);
3493 END IF;
3494
3495 -- Insert the rows from po_line_locations_all
3496
3497 -- Modified by Renga Kannan on 04/16/2002
3498 -- Added ship_to_location_id in the interface table.
3499
3500 Insert into Po_lines_interface(
3501 interface_line_id,
3502 interface_header_id,
3503 line_num,
3504 shipment_num,
3505 shipment_type,
3506 line_type_id,
3507 source_shipment_id,
3508 item_id,
3509 item,
3510 item_revision,
3511 category_id,
3512 unit_of_measure,
3513 quantity,
3514 terms_id,
3515 days_early_receipt_allowed,
3516 days_late_receipt_allowed,
3517 ship_to_organization_id,
3518 ship_to_location_id,
3519 price_discount,
3520 unit_price,
3521 effective_date,
3522 expiration_date,
3523 shipment_attribute_category,
3524 shipment_attribute1,
3525 shipment_attribute2,
3526 shipment_attribute3,
3527 shipment_attribute4,
3528 shipment_attribute5,
3529 shipment_attribute6,
3530 shipment_attribute7,
3531 shipment_attribute8,
3532 shipment_attribute9,
3533 shipment_attribute10,
3534 shipment_attribute11,
3535 shipment_attribute12,
3536 shipment_attribute13,
3537 shipment_attribute14,
3538 shipment_attribute15,
3539 last_update_date)
3540 --price_override) -- Check with Beth
3541 select
3542 po_lines_interface_s.nextval,
3543 p_int_header_id,
3544 null,
3545 poll.shipment_num,
3546 poll.shipment_type,
3547 pol.line_type_id,
3548 poll.source_shipment_id,
3549 p_item_id,
3550 p_segment1,
3551 null,
3552 pol.category_id,
3553 poll.unit_meas_lookup_code,
3554 poll.quantity,
3555 null,
3556 poll.days_early_receipt_allowed,
3557 poll.days_late_receipt_allowed,
3558 poll.ship_to_organization_id,
3559 poll.ship_to_location_id,
3560 poll.price_discount,
3561 p_price*(1-poll.price_discount/100),
3562 poll.start_date,
3563 poll.end_date,
3564 poll.attribute_category,
3565 poll.attribute1,
3566 poll.attribute2,
3567 poll.attribute3,
3568 poll.attribute4,
3569 poll.attribute5,
3570 poll.attribute6,
3571 poll.attribute7,
3572 poll.attribute8,
3573 poll.attribute9,
3574 poll.attribute10,
3575 poll.attribute11,
3576 poll.attribute12,
3577 poll.attribute13,
3578 poll.attribute14,
3579 poll.attribute15,
3580 sysdate
3581 -- p_price*poll.price_discount/100
3582 -- price_discount
3583 from po_line_locations_all poll,
3584 po_lines_all pol
3585 where pol.po_line_id = p_doc_line_id
3586 and pol.po_line_id = poll.po_line_id;
3587
3588 IF PG_DEBUG <> 0 THEN
3589 oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in locations = '||to_char(sql%rowcount),1);
3590 END IF;
3591
3592 g_pg_level := g_pg_level - 3;
3593
3594 End insert_blanket_line;
3595
3596
3597
3598 procedure Reduce_vendor_by_ou(
3599 p_vendor_details in PO_AUTOSOURCE_SV.vendor_record_details,
3600 p_config_item_id in Number,
3601 p_line_id in Number,
3602 p_mode in Varchar2,
3603 x_vendor_details out NOCOPY PO_AUTOSOURCE_SV.vendor_record_details) is
3604
3605 j Number := 0;
3606 l_oper_unit Number;
3607 i Number;
3608 l_assg_set_id Number;
3609 /* The following cursor will selec the vendors and vendor
3610 sites defined in the sourcing rule for the config item
3611 */
3612 Cursor oss_vendor_cur(p_assg_set_id number) is
3613 select vendor_id,
3614 vendor_site_id
3615 from mrp_sources_v
3616 where assignment_set_id = p_assg_set_id
3617 and inventory_item_id = p_config_item_id
3618 and assignment_type in (3,6)
3619 and source_type = 3
3620 and vendor_site_id is not null;
3621
3622
3623 /* The following cursor will get the operatin unit list
3624 of the valid receiving orgs
3625 */
3626
3627 Cursor oper_unit_cur is
3628 select distinct operating_unit organization_id
3629 from inv_organization_info_v
3630 where organization_id in (select organization_id
3631 from bom_cto_src_orgs
3632 where line_id = p_line_id
3633 and organization_type = 3);
3634
3635 l_option_specific varchar2(1);
3636 l_config_creation varchar2(1);
3637
3638
3639 TYPE Num_table is table of number index by binary_integer;
3640 l_oper_unit_list Num_table;
3641 l_vendor_list Num_table;
3642 l_vendor_site_list Num_table;
3643
3644
3645 begin
3646 l_assg_set_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
3647 /*
3648 This API will remove unwanted blankets from rolluping up.
3649 There is a seperate logic exists for this
3650 */
3651
3652 -- The following select statement will get the
3653 -- oss attribute and config creation attribute
3654 -- from mtl system items. Both these attributes
3655 -- in master org level, hence u can get the attribute
3656 -- from any organization.
3657
3658 oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
3659
3660 Select option_specific_sourced
3661 into l_option_specific
3662 from mtl_system_items
3663 where inventory_item_id = p_config_item_id
3664 and rownum<2;
3665
3666 If p_mode = 'ORDER' then
3667 select config_creation
3668 into l_config_creation
3669 from bom_cto_order_lines
3670 where line_id = p_line_id;
3671 End if;
3672
3673 oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
3674 oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
3675 oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
3676
3677 If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
3678 oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
3679
3680 For oper_unit_rec in oper_unit_cur
3681 Loop
3682 l_oper_unit_list(oper_unit_rec.organization_id) := oper_unit_rec.organization_id;
3683 End Loop;
3684 i := p_vendor_details.first;
3685
3686 While (i is not null)
3687 Loop
3688 If p_vendor_details(i).vendor_site_id is not null then
3689
3690 Select Org_id
3691 into l_oper_unit
3692 from po_vendor_sites_all
3693 where vendor_site_id = p_vendor_details(i).vendor_site_id;
3694
3695 End if;
3696
3697 If l_oper_unit_list.exists(l_oper_unit) then
3698 oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is part of the sourcing chain',1);
3699 x_vendor_details(j) := p_vendor_details(i);
3700 j := j + 1;
3701 else
3702 oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is not part of the sourcing chain',1);
3703 End if;
3704 i := p_vendor_details.next(i);
3705 End Loop;
3706 Else
3707 -- Added for MOAC
3708 -- Reducing records which are not having vendor site
3709
3710 If PG_DEBUG <> 0 Then
3711 oe_debug_pub.add('Reduce_vendor_by_ou: cib based on model', 3);
3712 End if;
3713
3714 i := p_vendor_details.first;
3715
3716 while (i is not null)
3717 Loop
3718 if p_vendor_details(i).vendor_site_id is not null then
3719 x_vendor_details(j) := p_vendor_details(i);
3720 j := j + 1;
3721 else
3722 If PG_DEBUG <> 0 Then
3723 oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor='
3724 ||p_vendor_details(i).vendor_id||' as the vendor site is null',3);
3725 End if;
3726 end if;
3727 i := p_vendor_details.next(i);
3728 End loop;
3729 -- End of MOAC change
3730 End if;
3731
3732 If nvl(l_option_specific,3) in (1,2) then
3733 oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors by OSS vendors',1);
3734
3735 For oss_vendor_rec in oss_vendor_cur(l_assg_set_id)
3736 Loop
3737 l_vendor_list(oss_vendor_rec.vendor_id) := oss_vendor_rec.vendor_id;
3738 l_vendor_site_list(oss_vendor_rec.vendor_site_id) := oss_vendor_rec.vendor_site_id;
3739 End Loop;
3740 i := x_vendor_details.first;
3741
3742 While (i is not null)
3743 Loop
3744 If l_vendor_list.exists(x_vendor_details(i).vendor_id)
3745 and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
3746 oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
3747 Else
3748 oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
3749 oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
3750 x_vendor_details.delete(i);
3751 End if;
3752 i := x_vendor_details.next(i);
3753 End Loop;
3754 End if;
3755
3756 end reduce_vendor_by_ou;
3757
3758
3759
3760 Procedure Derive_start_end_date(
3761 p_item_id IN bom_cto_order_lines.inventory_item_id%type,
3762 p_vendor_id IN Number,
3763 p_vendor_site_id IN Number,
3764 p_assgn_set_id IN Number ,
3765 x_start_date OUT NOCOPY date ,
3766 x_end_date Out NOCOPY date) is
3767
3768 l_sourcing_rule_id Number;
3769
3770 begin
3771
3772 -- Added by Renga Kannan on 03/25/02. took the logic from PO code
3773
3774 Begin
3775
3776 SELECT /*+ INDEX(MRP_SR_ASSIGNMENTS MRP_SR_ASSIGNMENTS_N3) */
3777 sourcing_rule_id
3778 INTO l_sourcing_rule_id
3779 FROM mrp_sr_assignments
3780 WHERE inventory_item_id = p_item_id
3781 AND assignment_set_id = p_assgn_set_id
3782 AND sourcing_rule_type = 1
3783 AND assignment_type = 3;
3784
3785 Exception when no_data_found then
3786 l_sourcing_rule_id := null;
3787 end;
3788
3789
3790 -- We will take the sourcing rule effective for sysdate window
3791 -- Added by Renga Kannan on 04/08/02
3792
3793 If l_sourcing_rule_id is not null then
3794 Begin
3795 select msro.effective_date,
3796 msro.disable_date
3797 into x_start_date,
3798 x_end_date
3799 from mrp_sourcing_rules msr,
3800 mrp_sr_receipt_org msro
3801 where msr.sourcing_rule_id = msro.sourcing_rule_id
3802 and msr.sourcing_rule_id = l_sourcing_rule_id
3803 and trunc(sysdate) between trunc(nvl(msro.effective_date,sysdate)) and trunc(nvl(msro.disable_date,sysdate+1));
3804
3805 Exception when no_data_found then
3806 x_start_date := null;
3807 x_end_date := null;
3808 end;
3809 else
3810 x_start_date := null;
3811 x_end_date := null;
3812 end if;
3813
3814 Exception when no_data_found then
3815 x_start_date := null;
3816 x_end_date := null;
3817 end Derive_start_end_date;
3818
3819
3820 /* fp-J: Added several new parameters as part of optional processing project */
3821
3822 PROCEDURE Create_purchase_doc_batch (
3823 errbuf OUT NOCOPY VARCHAR2,
3824 retcode OUT NOCOPY varchar2,
3825 p_sales_order NUMBER,
3826 p_dummy_field VARCHAR2,
3827 p_sales_order_line_id NUMBER,
3828 p_organization_id VARCHAR2,
3829 p_dummy_field1 VARCHAR2,
3830 p_offset_days NUMBER,
3831 p_overwrite_list_price varchar2,
3832 p_config_id NUMBER DEFAULT NULL,
3833 p_dummy_field2 VARCHAR2 DEFAULT NULL,
3834 p_base_model_id NUMBER DEFAULT NULL,
3835 p_created_days_ago NUMBER DEFAULT NULL,
3836 p_load_type NUMBER DEFAULT NULL,
3837 p_upgrade NUMBER DEFAULT 2,
3838 p_perform_rollup NUMBER DEFAULT 1
3839 ) AS
3840
3841
3842
3843
3844 TYPE PProllupCurTyp is REF CURSOR ;
3845 pprollup_cur PProllupCurTyp;
3846
3847 TYPE PProllupOECurTyp is REF CURSOR ;
3848 pprollup_oe_cur PProllupOECurTyp;
3849
3850 -- local variables
3851 lSourceCode VARCHAR2(100);
3852 p_po_quantity NUMBER := NULL;
3853 l_stmt_num NUMBER;
3854 p_dummy VARCHAR2(2000);
3855 v_rsv_quantity NUMBER; -- Bugfix 3652509: Removed precision
3856 v_sourcing_rule_exists VARCHAR2(100);
3857 v_sourcing_org NUMBER;
3858 v_source_type NUMBER;
3859 v_transit_lead_time NUMBER;
3860 v_exp_error_code NUMBER;
3861 v_rec_count NUMBER := 0;
3862 v_rec_count_noerr NUMBER := 0;
3863 conc_status BOOLEAN ;
3864 current_error_code VARCHAR2(20) := NULL;
3865 v_x_error_msg_count NUMBER;
3866 v_x_hold_result_out VARCHAR2(1);
3867 v_x_hold_return_status VARCHAR2(1);
3868 v_x_error_msg VARCHAR2(150);
3869 x_return_status VARCHAR2(1);
3870 p_new_order_quantity NUMBER; -- Bugfix 3652509: Removed precision
3871 l_res BOOLEAN;
3872 l_batch_id NUMBER;
3873 v_activity_status_code VARCHAR2(10);
3874 l_inv_quantity NUMBER;
3875
3876 l_request_id NUMBER;
3877 l_program_id NUMBER;
3878 l_source_document_type_id NUMBER;
3879
3880 l_active_activity VARCHAR2(8);
3881
3882 x_msg_count NUMBER;
3883 x_msg_data VARCHAR2(100);
3884 x_oper_unit_list CTO_AUTO_PROCURE_PK.oper_unit_tbl;
3885 xUserId NUMBER;
3886 xrespid NUMBER;
3887 xrespapplid NUMBER;
3888
3889 ll_line_id NUMBER;
3890 ll_inventory_item_id NUMBER;
3891 l_ato_line_id Number;
3892
3893 err_counter NUMBER := 0;
3894 pass_counter NUMBER := 0;
3895 l_mode Varchar2(100);
3896
3897 -- bug 3782079. rkaza. 08/06/2004
3898 -- Creating a new instance of the itemInfoTblType for loggin passed items
3899 TYPE itemInfo is RECORD (
3900 config_item_id number
3901 );
3902
3903 TYPE itemInfoTblType is table of itemInfo INDEX BY BINARY_INTEGER ;
3904
3905 erroredItems itemInfoTblType;
3906 passedItems itemInfoTblType;
3907
3908 -- Added by Renga Kannan on 03/01/2006 to replace the cursor
3909 -- with dynamic sql
3910
3911
3912 sql_stmt VARCHAR2(5000);
3913 drive_mark NUMBER := 0;
3914
3915 -- End of addition by Renga Kannan
3916
3917 BEGIN
3918
3919 -- set the return status.
3920 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3921
3922 -- Set the return code to success
3923 retcode := 0;
3924
3925 -- Get the ONT source code
3926 lSourceCode := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
3927
3928
3929 -- for all the sales order lines (entered, booked )
3930 -- Given parameters.
3931 IF PG_DEBUG <> 0 THEN
3932 oe_debug_pub.add('Create_purchase_doc_batch: '
3933 || '+---------------------------------------------------------------------------+',1);
3934
3935 oe_debug_pub.add('Create_purchase_doc_batch: '
3936 || '+------------------ Parameters passed into the procedure ------------------+',1);
3937
3938 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949
3950 oe_debug_pub.add('Create_purchase_doc_batch: '
3951 || '+---------------------------------------------------------------------------+',1);
3952 END IF;
3953
3954 drive_mark := 0;
3955 IF (p_upgrade = 2 ) then /* For non-Upgrade */
3956
3957 IF (p_sales_order is null AND p_sales_order_line_id is null AND p_offset_days is null) THEN
3958
3959 /* sales order number or line id or offset is NOT passed */
3960
3961 IF PG_DEBUG <> 0 THEN
3962 oe_debug_pub.add('Create_purchase_doc_batch: '||'Regular cursor.');
3963 END IF;
3964 sql_stmt := 'select distinct msi.inventory_item_id '||
3965 'from mtl_system_items msi '||
3966 'where msi.base_item_id is not null '||
3967 'and msi.bom_item_type = 4 '||
3968 'and msi.replenish_to_order_flag = ''Y'' '||
3969 'and msi.pick_components_flag = ''N'' ';
3970
3971 If p_organization_id is not null Then
3972 drive_mark := drive_mark + 1;
3973 sql_stmt := sql_stmt ||' and msi.organization_id = :p_organization_id';
3974 End if;
3975 If p_config_id is not null then
3976 drive_mark := drive_mark+2;
3977 sql_stmt := sql_stmt ||' and msi.inventory_item_id = :p_config_id';
3978 End if;
3979 If p_base_model_id is not null then
3980 drive_mark := drive_mark+4;
3981 sql_stmt := sql_stmt || ' and msi.base_item_id = :p_base_model_id';
3982 End if;
3983
3984 If p_created_days_ago is not null then
3985 drive_mark := drive_mark + 8;
3986 sql_stmt := sql_stmt || ' and msi.creation_date > trunc(sysdate) - :p_created_days_ago ';
3987 End if;
3988
3989 If p_load_type = 1 then
3990 sql_stmt := sql_stmt || ' and msi.base_item_id is not null '
3991 || ' and msi.auto_created_config_flag = ''Y''';
3992 elsif p_load_type = 2 then
3993 sql_stmt := sql_stmt || ' and msi.base_item_id is not null '
3994 || ' and msi.auto_created_config_flag <> ''Y''';
3995 else
3996 sql_stmt := sql_stmt || ' and msi.base_item_id is not null ';
3997 End if;
3998
3999 If PG_DEBUG <> 0 Then
4000 oe_debug_pub.add('Create_purchasing_doc_batch: Dynamic SqlStmt : '||sql_stmt,1);
4001 End if;
4002 If drive_mark = 1 Then
4003 OPEN pprollup_cur FOR sql_stmt using p_organization_id;
4004 elsif drive_mark = 2 then
4005 OPEN pprollup_cur FOR sql_stmt using p_config_id;
4006 elsif drive_mark = 3 then
4007 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_config_id;
4008 elsif drive_mark = 4 then
4009 OPEN pprollup_cur FOR sql_stmt using p_base_model_id;
4010 elsif drive_mark = 5 then
4011 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_base_model_id;
4012 elsif drive_mark = 6 then
4013 OPEN pprollup_cur FOR sql_stmt using p_config_id,p_base_model_id;
4014 elsif drive_mark = 7 then
4015 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_config_id,p_base_model_id;
4016 elsif drive_mark = 8 then
4017 OPEN pprollup_cur FOR sql_stmt using p_created_days_ago;
4018 elsif drive_mark = 9 then
4019 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_created_days_ago;
4020 elsif drive_mark = 10 then
4021 OPEN pprollup_cur FOR sql_stmt using p_config_id,P_created_days_ago;
4022 elsif drive_mark = 11 then
4023 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_config_id,p_created_days_ago;
4024 elsif drive_mark = 12 then
4025 OPEN pprollup_cur FOR sql_stmt using p_base_model_id,p_created_days_ago;
4026 elsif drive_mark = 13 then
4027 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_base_model_id,p_created_days_ago;
4028 elsif drive_mark = 14 then
4029 OPEN pprollup_cur FOR sql_stmt using p_config_id,p_base_model_id,p_created_days_ago;
4030 elsif drive_mark = 15 then
4031 OPEN pprollup_cur FOR sql_stmt using p_organization_id,p_config_id,p_base_model_id,p_created_days_ago;
4032 else
4033 OPEN pprollup_cur FOR sql_stmt;
4034 end if;
4035
4036 ELSE
4037
4038 /* sales order number or line id or offset is passed */
4039
4040 IF PG_DEBUG <> 0 THEN
4041 oe_debug_pub.add('Create_purchase_doc_batch: '||'OE cursor.');
4042 END IF;
4043
4044 OPEN pprollup_oe_cur FOR
4045 SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
4046 from oe_order_lines_all oel,
4047 oe_order_headers_all oeh,
4048 mtl_system_items msi
4049 where oel.inventory_item_id = msi.inventory_item_id
4050 and oel.ship_from_org_id = msi.organization_id
4051 and oel.header_id = oeh.header_id
4052 and oel.source_type_code = 'INTERNAL' ---- For drop ship bug# 2234858
4053 and msi.bom_item_type = 4
4054 and oel.open_flag = 'Y'
4055 and nvl(oel.cancelled_flag, 'N') = 'N'
4056 and oel.schedule_status_code = 'SCHEDULED'
4057 and oel.ordered_quantity > 0 -- bugfix 3043284: OQ > 0 is the correct condn instead of OQ-CQ
4058 and msi.base_item_id is not null -- 4172156. Added to process only configured ATO items.
4059 --
4060 -- Given a Order Line ID
4061 --
4062 and (p_sales_order_line_id is NULL
4063 or
4064 oel.ato_line_id = p_sales_order_line_id
4065 )--- 4172156. Added condition to pick up ATO item line also.
4066 --
4067 -- Given an Order Number
4068 --
4069 and ((p_sales_order is null)
4070 or
4071 (p_sales_order is not null
4072 and oeh.order_number = p_sales_order))
4073 --
4074 -- Given an Organization
4075 --
4076 and ( p_organization_id is null
4077 or oel.ship_from_org_id = p_organization_id
4078 )
4079 --
4080 -- Given config
4081 --
4082 and (p_config_id is null or
4083 msi.inventory_item_id = p_config_id)
4084 --
4085 -- Given base model
4086 --
4087 and (p_base_model_id is null or
4088 msi.base_item_id = p_base_model_id)
4089 --
4090 -- Given created days ago
4091 --
4092 and (p_created_days_ago is null or
4093 msi.creation_date > trunc(sysdate) - p_created_days_ago)
4094 --
4095 -- Given Offset days
4096 --
4097 and ((p_offset_days is null)
4098 /* Bug 5520934 begin: We need to honour bom calendar in offset days calculation */
4099 -- or (oel.schedule_ship_date <= trunc( sysdate + p_offset_days)))
4100 or (sysdate >= (select cal.calendar_date
4101 from bom_calendar_dates cal,
4102 mtl_parameters mp
4103 where mp.organization_id = oel.ship_from_org_id
4104 and cal.calendar_code = mp.calendar_code
4105 and cal.exception_set_id = mp.calendar_exception_set_id
4106 and cal.seq_num = (select cal2.prior_seq_num - nvl(p_offset_days, 0)
4107 from bom_calendar_dates cal2
4108 where cal2.calendar_code = mp.calendar_code
4109 and cal2.exception_set_id = mp.calendar_exception_set_id
4110 and cal2.calendar_date = trunc(oel.schedule_ship_date)))))
4111 -- end bugfix 5520934
4112 --
4113 -- Given load type
4114 --
4115 and (p_load_type is null or
4116 (p_load_type = 1
4117 and msi.base_item_id is not null
4118 and msi.auto_created_config_flag = 'Y') or
4119 (p_load_type = 2
4120 and msi.base_item_id is not null
4121 and msi.auto_created_config_flag <> 'Y') or
4122 (p_load_type = 3
4123 and msi.base_item_id is not null)
4124 )
4125 --
4126 -- for all the records with the status of REQ-CREATED
4127 --
4128 and (oel.item_type_code = 'CONFIG'
4129 or(oel.line_id=oel.ato_line_id
4130 and oel.item_type_code in ('STANDARD','OPTION')
4131 )
4132 )-- 4172156. Added Condition to pickup ATO item Line also
4133 and msi.replenish_to_order_flag = 'Y'
4134 and oel.ato_line_id is not null -- bugfix 3164399: although item_type_code will restrict
4135 and msi.pick_components_flag = 'N'; -- the criteria, added the ato_line_id for consistency
4136 end if;
4137
4138 elsif (p_upgrade = 1 ) then
4139
4140 IF PG_DEBUG <> 0 THEN
4141 oe_debug_pub.add('Create_purchase_doc_batch: '||'Upgrade');
4142 END IF;
4143
4144 if p_perform_rollup = 2 then
4145 oe_debug_pub.add('Perform Rollup parameter is set to NO.');
4146 RETCODE := 0 ;
4147 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
4148 return;
4149 end if;
4150
4151 OPEN pprollup_cur FOR
4152 select distinct config_item_id
4153 from bom_cto_order_lines_upg
4154 where ato_line_id = line_id -- get only the parent configs
4155 and status = 'MRP_SRC';
4156
4157 end if;
4158
4159
4160 -- initialize the program_id and the request_id from the concurrent request.
4161 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
4162 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
4163
4164 -- update th program_id and requist_id for the lines fetched by the cursor..
4165
4166
4167 -- Log all the input parameters
4168
4169 l_stmt_num := 1;
4170 l_ato_line_id := null;
4171 LOOP
4172
4173 if (p_sales_order is null AND p_sales_order_line_id is null AND p_offset_days is null) then
4174 FETCH pprollup_cur INTO ll_inventory_item_id;
4175 EXIT when pprollup_cur%notfound;
4176 else
4177 FETCH pprollup_oe_cur INTO ll_line_id, ll_inventory_item_id,l_ato_line_id;
4178 EXIT when pprollup_oe_cur%notfound;
4179 end if;
4180
4181 -- 4172156
4182 -- The mode will be set to ORDER for ATO model lines.
4183 -- For ATO items this will be set to PRECONFIG
4184 -- If there is not order specific parameter then this will be set to pre config.
4185
4186 If ll_line_id = l_ato_line_id then
4187 l_mode := 'PRECONFIG';
4188 l_ato_line_id := null; -- We need to have this as null for ATO item lines.
4189 elsif ll_line_id is not null then
4190 l_mode := 'ORDER';
4191 else
4192 l_mode := 'PRECONFIG';
4193 end if;
4194
4195
4196 -- count of the records selected by the cursor
4197 v_rec_count := v_rec_count + 1;
4198
4199 -- Log all the record being processed.
4200 IF PG_DEBUG <> 0 THEN
4201 oe_debug_pub.add('Create_purchase_doc_batch: '
4202 || '+-------- Processing for --------------------------------------------------+',1);
4203
4204 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4206 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4207 oe_debug_pub.add('Create_purchase_doc_batch: '
4208 || '+--------------------------------------------------------------------------+',1);
4209 END IF;
4210
4211 --- 4172156
4212 --- Removed the check hold API call here. Based on the discussion with CTO team, it is decided not to have a
4213 --- hold check in the purchase price rollup batch program. Today, we are not checking for hold for any rollup
4214 --- during auto create config process as well as any of the other optional rollup process.
4215
4216
4217 -- Set a savepoint so that we can rollback to this pt if something goes wrong.
4218 SAVEPOINT pp_rollup;
4219
4220 -- call the purchase doc creation API.
4221 CTO_AUTO_PROCURE_PK.Create_purchasing_doc(
4222 p_config_item_id => ll_inventory_item_id,
4223 p_overwrite_list_price => p_overwrite_list_price,
4224 p_called_in_batch => 'Y',
4225 p_batch_number => l_batch_id,
4226 x_oper_unit_list => x_oper_unit_list,
4227 p_mode => l_mode,
4228 p_ato_line_id => l_ato_line_id,
4229 x_return_status => x_return_status,
4230 x_msg_count => x_msg_count,
4231 x_msg_data => x_msg_data);
4232
4233 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4234 RETCODE := 1;
4235 IF PG_DEBUG <> 0 THEN
4236 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Create_purchasing_doc.',1);
4237 END IF;
4238 err_counter := err_counter + 1;
4239 erroredItems(err_counter).config_item_id := ll_inventory_item_id;
4240 ROLLBACK TO pp_rollup;
4241
4242 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4243 IF PG_DEBUG <> 0 THEN
4244 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Create_purchasing_doc.',1);
4245 END IF;
4246 ROLLBACK TO pp_rollup;
4247 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4248 ELSE
4249 IF PG_DEBUG <> 0 THEN
4250 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Successfully processed.',1);
4251 END IF;
4252 pass_counter := pass_counter + 1;
4253 passedItems(pass_counter).config_item_id := ll_inventory_item_id;
4254 END IF;
4255
4256 << end_loop >>
4257 null;
4258
4259 END LOOP;
4260
4261 if pprollup_cur%ISOPEN then
4262 IF PG_DEBUG <> 0 THEN
4263 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_cur%ROWCOUNT ||' rows');
4264 END IF;
4265
4266 close pprollup_cur;
4267 end if;
4268
4269 if pprollup_oe_cur%ISOPEN then
4270 IF PG_DEBUG <> 0 THEN
4271 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_oe_cur%ROWCOUNT ||' rows');
4272 END IF;
4273 close pprollup_oe_cur;
4274 end if;
4275
4276
4277 -- Print the successfully processed items..
4278 if (pass_counter > 0 AND PG_DEBUG <> 0) then
4279 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4280 oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4281 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4282
4283 for j in 1 .. pass_counter
4284 loop
4285 if ( passedItems(j).config_item_id > 0 ) then
4286
4287 declare
4288 l_pass_config_description varchar2(50);
4289 begin
4290
4291 SELECT substrb(kfv.concatenated_segments,1,35)
4292 INTO l_pass_config_description
4293 FROM mtl_system_items_kfv kfv
4294 WHERE kfv.inventory_item_id = passedItems(j).config_item_id
4295 AND rownum = 1;
4296
4297 oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4298
4299 exception
4300 when OTHERS then
4301 oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4302 end;
4303 end if;
4304 end loop;
4305 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4306 end if;
4307
4308
4309 -- Print the errored records..
4310 if (err_counter > 0 AND PG_DEBUG <> 0) then
4311 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4312 oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4313 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4314
4315 for j in 1 .. err_counter
4316 loop
4317 if ( erroredItems(j).config_item_id > 0 ) then
4318
4319 declare
4320 l_err_config_description varchar2(50);
4321 begin
4322
4323 SELECT substrb(kfv.concatenated_segments,1,35)
4324 INTO l_err_config_description
4325 FROM mtl_system_items_kfv kfv
4326 WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
4327 AND rownum = 1;
4328
4329 oe_debug_pub.add (' '|| j ||'.'||' '|| l_err_config_description ||
4330 '(item id '||erroredItems(j).config_item_id ||')');
4331
4332 exception
4333 when OTHERS then
4334 oe_debug_pub.add ('**Failed to get description for item id '|| erroredItems(j).config_item_id );
4335 end;
4336 end if;
4337 end loop;
4338 oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4339 end if;
4340
4341
4342 -- Launch the concurrent program as needed
4343
4344 IF PG_DEBUG <> 0 THEN
4345 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Calling Submit_pdoi_conc_prog.');
4346 END IF;
4347
4348 Submit_pdoi_conc_prog(
4349 p_oper_unit_list => x_oper_unit_list,
4350 p_batch_id => l_batch_id,
4351 x_return_status => x_return_status,
4352 x_msg_count => x_msg_count,
4353 x_msg_data => x_msg_data);
4354
4355 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4356 IF PG_DEBUG <> 0 THEN
4357 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Submit_pdoi_conc_prog.',1);
4358 END IF;
4359 raise FND_API.G_EXC_ERROR;
4360
4361 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4362 IF PG_DEBUG <> 0 THEN
4363 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Submit_pdoi_conc_prog.',1);
4364 END IF;
4365 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4366 END IF;
4367
4368
4369 IF RETCODE = 1 THEN
4370 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
4371
4372 ELSE
4373 RETCODE := 0 ;
4374 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
4375
4376 END IF;
4377
4378 IF PG_DEBUG <> 0 THEN
4379 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Search for the following string to look at the blanket price rollup process log for an item...');
4380 oe_debug_pub.add('Create_purchase_doc_batch: ' || 'ROLLUP BLANKETS FOR <item id>');
4381 END IF;
4382
4383 COMMIT;
4384
4385 EXCEPTION
4386
4387 when FND_API.G_EXC_UNEXPECTED_ERROR then
4388 IF PG_DEBUG <> 0 THEN
4389 oe_debug_pub.add('Create_purchase_doc_batch:: unexp error::'||l_stmt_num||sqlerrm,1);
4390 END IF;
4391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4392 CTO_MSG_PUB.Count_And_Get
4393 (p_msg_count => x_msg_count
4394 ,p_msg_data => x_msg_data
4395 );
4396 RETCODE := 2 ;
4397 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4398
4399 when FND_API.G_EXC_ERROR then
4400 IF PG_DEBUG <> 0 THEN
4401 oe_debug_pub.add('Create_purchase_doc_batch::exp error::'||l_stmt_num||sqlerrm,1);
4402 END IF;
4403 x_return_status := FND_API.G_RET_STS_ERROR;
4404 CTO_MSG_PUB.Count_And_Get
4405 (p_msg_count => x_msg_count
4406 ,p_msg_data => x_msg_data);
4407 RETCODE := 2 ;
4408 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4409
4410 when others then
4411 IF PG_DEBUG <> 0 THEN
4412 oe_debug_pub.add('Create_purchase_doc_batch::others::'||l_stmt_num||sqlerrm,1);
4413 END IF;
4414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4415 CTO_MSG_PUB.Count_And_Get
4416 (p_msg_count => x_msg_count
4417 ,p_msg_data => x_msg_data
4418 );
4419 RETCODE := 2 ;
4420 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4421
4422 END Create_purchase_doc_batch;
4423
4424
4425
4426 Procedure Submit_pdoi_conc_prog(
4427 p_oper_unit_list In cto_auto_procure_pk.oper_unit_tbl,
4428 p_batch_id In Number,
4429 x_return_status Out NOCOPY Varchar2,
4430 x_msg_count Out NOCOPY Number,
4431 x_msg_data Out NOCOPY Varchar2) Is
4432
4433 i Number;
4434 l_request_id Number;
4435 xuserid Number;
4436 xrespid Number;
4437 xRespApplId Number;
4438 x_org_id Number;
4439 l_release_method Number;
4440 l_rel_method_value Varchar2(30);
4441 Begin
4442
4443 i := p_oper_unit_list.first;
4444
4445 while (i is not null)
4446 loop
4447
4448 --- Launch the concurrent program
4449 oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Launching program for oper unit ='||to_char(p_oper_unit_list(i).oper_unit));
4450 oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Batch id = '||to_char(p_batch_id),1);
4451
4452 l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4453 If l_release_method is null then
4454 l_rel_method_value := null;
4455 oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is null...',1);
4456 elsif l_release_method = 2 then
4457 l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4458 oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4459 elsif l_release_method = 1 then
4460 l_rel_method_value := 'CREATE';
4461 oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4462 elsif l_release_method = 3 then
4463 l_rel_method_value := 'MANUAL';
4464 oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Release Using AutoCreate ...',1);
4465 end if;
4466
4467 -- fnd_client_info.set_org_context(p_oper_unit_list(i).oper_unit);
4468
4469
4470 l_request_id := fnd_request.submit_request(
4471 application => 'PO',
4472 program => 'POXPDOI',
4473 description => '',
4474 start_time => '',
4475 sub_request => false,
4476 argument1 => '',
4477 argument2 => 'Blanket',
4478 argument3 => '',
4479 argument4 => 'N',
4480 argument5 => 'Y',
4481 argument6 => 'Approved',
4482 argument7 => l_rel_method_value,
4483 argument8 => to_char(p_batch_id),
4484 argument9 => p_oper_unit_list(i).oper_unit,
4485 argument10 => 'N' ,
4486 argument11=>'',argument12=>'',argument13=>11,argument14=>'',argument15=>'',
4487 argument16=>'',argument17=>'',argument18=>'',argument19=>'',argument20=>'',
4488 argument21=>'',argument22=>'',argument23=>11,argument24=>'',argument25=>'',
4489 argument26=>'',argument27=>'',argument28=>'',argument29=>'',argument30=>'',
4490 argument31=>'',argument32=>'',argument33=>11,argument34=>'',argument35=>'',
4491 argument36=>'',argument37=>'',argument38=>'',argument39=>'',argument40=>'',
4492 argument41=>'',argument42=>'',argument43=>11,argument44=>'',argument45=>'',
4493 argument46=>'',argument47=>'',argument48=>'',argument49=>'',argument50=>'',
4494 argument51=>'',argument52=>'',argument53=>11,argument54=>'',argument55=>'',
4495 argument56=>'',argument57=>'',argument58=>'',argument59=>'',argument60=>'',
4496 argument61=>'',argument62=>'',argument63=>11,argument64=>'',argument65=>'',
4497 argument66=>'',argument67=>'',argument68=>'',argument69=>'',argument70=>'',
4498 argument71=>'',argument72=>'',argument73=>11,argument74=>'',argument75=>'',
4499 argument76=>'',argument77=>'',argument78=>'',argument79=>'',argument80=>'',
4500 argument81=>'',argument82=>'',argument83=>11,argument84=>'',argument85=>'',
4501 argument86=>'',argument87=>'',argument88=>'',argument89=>'',argument90=>'',
4502 argument91=>'',argument92=>'',argument93=>11,argument94=>'',argument95=>'',
4503 argument96=>'',argument97=>'',argument98=>'',argument99=>'',argument100=>'' ); /* BUG# 2726167 pass additional parameter to PDOI concurrent program */
4504
4505
4506
4507
4508 oe_debug_pub.add('Submit_pdoi_conc_prog: '||'pdoi concurrent request = '||to_char(l_request_id)||' is submitted',1);
4509 i := p_oper_unit_list.next(i);
4510
4511 end loop;
4512
4513 /*-- Launching the error report
4514 If p_oper_unit_list.count <> 0 then
4515 -- 07/09/2002 It is decided not to lauch the error report along
4516 -- With PDOI request. I have removed the call to the error report.
4517 -- For time being I am commenting this call. I will remove the call
4518 -- Before the patchset. Please refer to bug # 2365137 for further information
4519
4520
4521 if p_top_model_line_id is not null then
4522
4523 OE_ORDER_CONTEXT_GRP.Set_Created_By_Context (
4524 p_header_id => NULL
4525 ,p_line_id => p_top_model_line_id
4526 ,x_orig_user_id => xUserId
4527 ,x_orig_resp_id => xRespId
4528 ,x_orig_resp_appl_id => xRespApplId
4529 ,x_return_status => x_Return_Status
4530 ,x_msg_count => x_Msg_Count
4531 ,x_msg_data => x_Msg_Data );
4532 end if;
4533 end if;
4534 */
4535 empty_ou_global;
4536 End;
4537
4538 Procedure empty_ou_global is
4539 i Number;
4540 begin
4541
4542 i := CTO_AUTO_PROCURE_PK.G_oper_unit_list.first;
4543 while i is not null
4544 loop
4545 CTO_AUTO_PROCURE_PK.g_oper_unit_list.delete(i);
4546 i := g_oper_unit_list.next(i);
4547 end loop;
4548
4549 end Empty_ou_global;
4550
4551
4552 Procedure process_purchase_price(
4553 p_config_item_id in Number,
4554 p_batch_number in out NOCOPY number,
4555 p_group_id in number,
4556 p_overwrite_list_price in varchar2,
4557 p_line_id in number,
4558 p_mode in Varchar2 default 'ORDER',
4559 x_oper_unit_list IN OUT NOCOPY cto_auto_procure_pk.oper_unit_tbl,
4560 x_return_status out NOCOPY varchar2,
4561 x_msg_data out NOCOPY varchar2,
4562 x_msg_count out NOCOPY number) is
4563 lStmtNumber Number;
4564 x_rolled_price Number := 0;
4565 i Number;
4566 l_batch_id Number;
4567 l_request_id Number;
4568 l_model_item_id Number;
4569
4570 Type orgs_list_type is table of number;
4571 l_orgs_list orgs_list_type;
4572 l_list_price Number;
4573 l_buy_found Varchar2(1);
4574 l_config_creation number;
4575 Begin
4576
4577 g_pg_level := g_pg_level + 3;
4578 lstmtNumber := 10;
4579 if PG_DEBUG <> 0 Then
4580 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Inside Process Purchase Price',5);
4581 end if;
4582
4583 lstmtNumber := 20;
4584 select distinct nvl(fsp.inventory_organization_id,0)
4585 bulk collect into l_orgs_list
4586 from inv_organization_info_v org,
4587 financials_system_params_all fsp,
4588 mtl_system_items msi
4589 where org.organization_id in (select organization_id
4590 from mtl_system_items_b
4591 where inventory_item_id = p_config_item_id)
4592 and fsp.org_id = org.operating_unit
4593 and msi.inventory_item_id = p_config_item_id
4594 and msi.organization_id = fsp.inventory_organization_id;
4595
4596 select base_item_id
4597 into l_model_item_id
4598 from mtl_system_items
4599 where inventory_item_id = p_config_item_id
4600 and rownum = 1;
4601
4602 If PG_DEBUG <> 0 Then
4603 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of Validation orgs = '||l_orgs_list.count,5);
4604 End if;
4605 lStmtNumber := 30;
4606 If l_orgs_list.count <> 0 Then
4607 for i in l_orgs_list.first..l_orgs_list.last
4608 Loop
4609 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Validation org = '||l_orgs_list(i),5);
4610 lStmtNumber := 40;
4611 Select list_price_per_unit
4612 into l_list_price
4613 from mtl_system_items
4614 where inventory_item_id = p_config_item_id
4615 and organization_id = l_orgs_list(i);
4616
4617 If pg_debug <> 0 Then
4618 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: List price in item master = '||l_list_price,5);
4619 End if;
4620 -- Renga Talk to val regarding the custom hook
4621
4622 x_rolled_price := CTO_CUSTOM_LIST_PRICE_PK.get_list_price(
4623 p_line_id,
4624 l_model_item_id,
4625 l_orgs_list(i));
4626 if x_rolled_price is null then
4627
4628 --- Call the list price API to rollup the list price of
4629 --- this model based on its components selected in OE
4630 If l_list_price is null or p_overwrite_list_price = 'Y' Then
4631 lStmtNumber := 30;
4632 cto_auto_procure_pk.Rollup_list_price(
4633 p_config_item_id => p_config_item_id,
4634 p_group_id => p_group_id,
4635 p_org_id => l_orgs_list(i),
4636 x_rolled_price => x_rolled_price,
4637 x_return_status => x_return_status,
4638 x_msg_count => x_msg_count,
4639 x_msg_data => x_msg_data);
4640
4641 if x_return_status = FND_API.G_RET_STS_ERROR then
4642 IF PG_DEBUG <> 0 THEN
4643 oe_debug_pub.add('Create_Purchasing_Doc: '
4644 || ' Expected error in Rollup_list_procedure',1);
4645 END IF;
4646 raise FND_API.G_EXC_ERROR;
4647 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4648 IF PG_DEBUG <> 0 THEN
4649 oe_debug_pub.add('Create_Purchasing_Doc: '
4650 || ' Unexpected error in rollup_list_price ',1);
4651 END IF;
4652 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4653 end if;
4654 End if; /* l_list_price is null or p_overwrite_list_price = 'Y' */
4655
4656 -- added by Renga Kannan on 10/21/03
4657 -- The list price comupted by the above API is for per qty for order uom
4658 -- We need to get the list price for per qty primary uom
4659
4660 End if;
4661 -- Update the Mtl_system_items with rolled price in po validation org
4662 -- If the p_oerwrite_list_pirce is set to 'N' then only if the list_price is
4663 -- null the rolled up price should be updated.
4664
4665 lStmtNumber := 50;
4666 If l_list_price is null or p_overwrite_list_price = 'Y' Then
4667 If PG_DEBUG <> 0 Then
4668 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Updating item master with list price ',5);
4669 End if;
4670
4671 Update Mtl_system_items
4672 set list_price_per_unit = x_rolled_price
4673 where inventory_item_id = p_config_item_id
4674 and organization_id = l_orgs_list(i)
4675 and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
4676 If pg_debug <> 0 Then
4677 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of rows updated = '||sql%rowcount,1);
4678 End if;
4679 End if; /* l_list_price is null or p_overwrite_list_price = 'Y' */
4680 End Loop;
4681 End if;
4682 lStmtNumber := 60;
4683 select config_orgs
4684 into l_config_creation
4685 from mtl_system_items
4686 where inventory_item_id = l_model_item_id
4687 and rownum=1;
4688
4689 If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4690 Begin
4691 select 'x'
4692 into l_buy_found
4693 from bom_cto_src_orgs
4694 where line_id = p_line_id
4695 and organization_type = 3
4696 and rownum=1;
4697 Exception when no_data_found then
4698 l_buy_found := 'N';
4699 End;
4700 If l_buy_found = 'N' then
4701 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
4702 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
4703 oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
4704 return;
4705 End if;
4706 End if;
4707
4708 Rollup_purchase_price (
4709 p_batch_id => p_batch_number,
4710 p_config_item_id => p_config_item_id,
4711 p_group_id => p_group_id,
4712 p_mode => p_mode,
4713 p_line_id => p_line_id,
4714 x_oper_unit_list => x_oper_unit_list,
4715 x_return_status => x_return_status,
4716 x_msg_count => x_msg_count,
4717 x_msg_data => x_msg_data);
4718
4719 if x_return_status = FND_API.G_RET_STS_ERROR then
4720 IF PG_DEBUG <> 0 THEN
4721 oe_debug_pub.add('Create_Purchasing_Doc: '
4722 || ' Expected error in Rollup_purchase_price procedure',1);
4723 END IF;
4724 raise FND_API.G_EXC_ERROR;
4725 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4726 IF PG_DEBUG <> 0 THEN
4727 oe_debug_pub.add('Create_Purchasing_Doc: '
4728 || ' Unexpected error in rollup_purchase_price procedure',1);
4729 EnD IF;
4730 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4731 end if;
4732
4733 g_pg_level := g_pg_level - 3;
4734
4735 exception
4736
4737 when FND_API.G_EXC_UNEXPECTED_ERROR then
4738 IF PG_DEBUG <> 0 THEN
4739 oe_debug_pub.add('PROCESS_PURCHASE_PRICE:: unexp error::'||lStmtNumber||sqlerrm,1);
4740 END IF;
4741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4742 CTO_MSG_PUB.Count_And_Get
4743 (p_msg_count => x_msg_count
4744 ,p_msg_data => x_msg_data
4745 );
4746 g_pg_level := g_pg_level - 3;
4747
4748 when FND_API.G_EXC_ERROR then
4749 IF PG_DEBUG <> 0 THEN
4750 oe_debug_pub.add('PROCESS_PURCHASE_PRICE::exp error::'||lStmtNumber||sqlerrm,1);
4751 END IF;
4752 x_return_status := FND_API.G_RET_STS_ERROR;
4753 CTO_MSG_PUB.Count_And_Get
4754 (p_msg_count => x_msg_count
4755 ,p_msg_data => x_msg_data);
4756 g_pg_level := g_pg_level - 3;
4757
4758 when others then
4759 IF PG_DEBUG <> 0 THEN
4760 oe_debug_pub.add('PROCESS_PURCHASE_PRICE::others::'||lStmtNumber||sqlerrm,1);
4761 END IF;
4762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4763 CTO_MSG_PUB.Count_And_Get
4764 (p_msg_count => x_msg_count
4765 ,p_msg_data => x_msg_data
4766 );
4767 g_pg_level := g_pg_level - 3;
4768 End Process_purchase_price;
4769
4770
4771
4772 PROCEDURE load_lines_into_bcolt(p_sales_order_line_id NUMBER,
4773 p_sales_order NUMBER,
4774 p_organization_id VARCHAR2,
4775 p_offset_days NUMBER,
4776 x_return_status OUT NOCOPY VARCHAR2) IS
4777 sql_stmt VARCHAR2(5000);
4778 drive_mark NUMBER := 0;
4779 l_organization_id Number;
4780 BEGIN
4781 x_return_status := FND_API.G_RET_STS_SUCCESS;
4782 l_organization_id := p_organization_id;
4783
4784 -- rkaza. ireq project. 05/06/2005.
4785 -- Do not select lines that are in INTERNAL_REQ_REQUESTED status.
4786 -- Added it to the where clause along with PO_REQ_REQUESTED clause
4787 drive_mark := 0;
4788 sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, status, inventory_item_id)'||
4789 'select oel.line_id, oel.org_id, 1, 0 '||
4790 'from oe_order_lines_all oel,'||
4791 ' mtl_system_items msi,'||
4792 ' wf_item_activity_statuses was,'||
4793 ' wf_process_activities WPA ';
4794
4795 sql_stmt := sql_stmt || ' where oel.inventory_item_id = msi.inventory_item_id ' ||
4796 'and oel.ship_from_org_id = msi.organization_id ' ||
4797 'and oel.source_type_code = ''INTERNAL'' '||
4798 'and msi.bom_item_type = 4 ' ||
4799 'and oel.open_flag = ''Y'' '||
4800 'and (oel.cancelled_flag is null ' ||
4801 ' or oel.cancelled_flag = ''N'') '||
4802 'and oel.booked_flag = ''Y'' '||
4803 'and oel.schedule_status_code = ''SCHEDULED'' '||
4804 'and oel.ordered_quantity > 0 ' ||
4805 'and cto_auto_procure_pk.get_reserved_qty(oel.line_id)- nvl(oel.shipped_quantity,0) < oel.ordered_quantity '||
4806 'and nvl (oel.shipped_quantity, 0) = 0 '||
4807 'and oel.flow_status_code not in ( ''EXTERNAL_REQ_REQUESTED'',''INTERNAL_REQ_REQUESTED'') '||
4808 'and msi.replenish_to_order_flag = ''Y'' '||
4809 'and oel.ato_line_id is not null ' ||
4810 'and (oel.item_type_code = ''CONFIG'' '||
4811 ' or (oel.ato_line_id=oel.line_id '||
4812 ' and oel.item_type_code in (''STANDARD'', ''OPTION''))) '||
4813 'and msi.pick_components_flag = ''N'' '||
4814 'and was.item_type = ''OEOL'' '||
4815 'and was.activity_status = ''NOTIFIED'' '||
4816 'and was.item_type = wpa.activity_item_type '||
4817 'and was.process_activity = wpa.instance_id '||
4818 'and wpa.activity_name in '||
4819 ' (''EXECUTECONCPROGAFAS'', ''CREATE_SUPPLY_ORDER_ELIGIBLE'', ''SHIP_LINE'', ''WAIT_FOR_PO_RECEIPT'') ';
4820
4821 /* We want to do an explicit to_char() when order_number or line_id
4822 * parameter is passed because we are driving from OEL->WAS. If we are driving
4823 * from WF tables into OE then to_char() should not be used.
4824 *
4825 * Here, the problem was because of the implicit type conversion that was happening on the WAS side.
4826 * That was preventing the item_key column of the WAS PK index from being used during index access.
4827 * It was effectively using the index only on the item_type column and that is the reason why it was slow.
4828 */
4829
4830 if p_sales_order is null and p_sales_order_line_id is null then
4831 sql_stmt := sql_stmt ||' and was.item_key = oel.line_id ' ;
4832 else
4833 sql_stmt := sql_stmt ||' and was.item_key = to_char(oel.line_id) ' ;
4834 end if;
4835
4836 /* Given an Order Number */
4837 -- Do we really need to validate against mtl_sales_orders and oe_transaction_types_tl ?
4838 -- Will there at all exist any order_number in oe_order_header which shall ever fail this validation ?
4839 if p_sales_order is not null then
4840 drive_mark := drive_mark + 1;
4841 sql_stmt := sql_stmt || ' and oel.header_id in' ||
4842 ' (select oeh.header_id '||
4843 ' from oe_order_headers_all oeh, '||
4844 ' oe_transaction_types_tl oet, '||
4845 ' mtl_sales_orders mso '||
4846 ' where oeh.order_number = to_char( :p_sales_order) '||
4847 ' and oeh.order_type_id = oet.transaction_type_id '||
4848 ' and mso.segment1 = to_char(oeh.order_number) '||
4849 ' and mso.segment2 = oet.name '||
4850 ' and oet.language = (select language_code '||
4851 ' from fnd_languages '||
4852 ' where installed_flag = ''B'') ' ||
4853 ' ) ' ;
4854 end if;
4855
4856 /* Given a Order Line ID */
4857 if p_sales_order_line_id is not null then
4858 drive_mark := drive_mark + 2;
4859 sql_stmt := sql_stmt ||' and oel.line_id in (select oelc.line_id '||
4860 'from oe_order_lines_all oelc '||
4861 'where oelc.ato_line_id = :p_sales_order_line_id '||
4862 'and (oelc.item_type_code = ''CONFIG'' '||
4863 ' or (oelc.item_type_code in (''STANDARD'',''OPTION'') '||
4864 ' and ato_line_id = line_id)) '||
4865 ') ';
4866 end if;
4867
4868 /* Given an Organization */
4869 if p_organization_id is not null then
4870 drive_mark := drive_mark + 4;
4871 sql_stmt := sql_stmt ||' and oel.ship_from_org_id = :l_organization_id ';
4872 end if;
4873
4874 /* Given Offset days */
4875 if p_offset_days is not null then
4876 drive_mark := drive_mark + 8;
4877 --sql_stmt := sql_stmt ||' and oel.schedule_ship_date <= trunc( sysdate + :p_offset_days) ';
4878 /* Bug 5520934 : We need to honour bom_calendar in offset days calculation */
4879 sql_stmt := sql_stmt ||' and sysdate >= (select cal.calendar_date
4880 from bom_calendar_dates cal,
4881 mtl_parameters mp
4882 where mp.organization_id = oel.ship_from_org_id
4883 and cal.calendar_code = mp.calendar_code
4884 and cal.exception_set_id = mp.calendar_exception_set_id
4885 and cal.seq_num = (select cal2.prior_seq_num - nvl(:p_offset_days, 0)
4886 from bom_calendar_dates cal2
4887 where cal2.calendar_code = mp.calendar_code
4888 and cal2.exception_set_id = mp.calendar_exception_set_id
4889 and cal2.calendar_date = trunc(oel.schedule_ship_date))) ';
4890 end if;
4891
4892 IF PG_DEBUG <> 0 THEN
4893 oe_debug_pub.add ('The dyanamic sql generated is');
4894 oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
4895 oe_debug_pub.add (substr(sql_stmt,1501,3000));
4896 oe_debug_pub.add ('The drive_mark is '||drive_mark);
4897 END IF;
4898
4899 /*
4900 Below, we execute the sql statement according to which parameters
4901 we have selected. The drive_mark variable tells us which parameters
4902 we are using, so we are sure to send the right ones to SQL.
4903 */
4904
4905 if (drive_mark = 0) then
4906 -- No (optional) parameter is passed
4907 EXECUTE IMMEDIATE sql_stmt;
4908
4909 elsif (drive_mark = 1) then
4910 -- Only Order_Number is passed
4911 EXECUTE IMMEDIATE sql_stmt USING p_sales_order;
4912
4913 elsif (drive_mark = 2) then
4914 -- Only Line_Id is passed
4915 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id;
4916
4917 elsif (drive_mark = 3) then
4918 -- Order Number and Line_Id is passed
4919 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id;
4920
4921 elsif (drive_mark = 4) then
4922 -- Only Orgn_Id is passed
4923 EXECUTE IMMEDIATE sql_stmt USING l_organization_id;
4924
4925 elsif (drive_mark = 5) then
4926 -- Order_Number and Orgn_Id is passed
4927 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id;
4928
4929 elsif (drive_mark = 6) then
4930 -- Line_id and Orgn_Id is passed
4931 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id;
4932
4933 elsif (drive_mark = 7) then
4934 -- Order_number, Line_Id and Orgn_Id is passed
4935 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id;
4936
4937 elsif (drive_mark = 8) then
4938 -- Offset_Days is passed
4939 EXECUTE IMMEDIATE sql_stmt USING p_offset_days;
4940
4941 elsif (drive_mark = 9) then
4942 -- Order_Number and Offset_Days is passed
4943 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_offset_days;
4944
4945 elsif (drive_mark = 10) then
4946 -- Line_id and Offset_Days is passed
4947 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, p_offset_days;
4948
4949 elsif (drive_mark = 11) then
4950 -- Order_Number, Line_id and Offset_Days is passed
4951 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, p_offset_days;
4952
4953 elsif (drive_mark = 12) then
4954 -- Organization_id and Offset_Days is passed
4955 EXECUTE IMMEDIATE sql_stmt USING l_organization_id, p_offset_days;
4956
4957 elsif (drive_mark = 13) then
4958 -- Order_Number, Organization_id and Offset_Days is passed
4959 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id, p_offset_days;
4960
4961 elsif (drive_mark = 14) then
4962 -- Line_id, Organization_id and Offset_Days is passed
4963 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id, p_offset_days;
4964
4965 elsif (drive_mark = 15) then
4966 -- Order_Number, Line_id, Organization_id and Offset_Days is passed
4967 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id, p_offset_days;
4968
4969 else
4970 oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
4971
4972 end if;
4973
4974
4975 IF PG_DEBUG <> 0 THEN
4976 oe_debug_pub.add('load_lines_into_bcolt: ' || 'no. of records in temp table '||SQL%ROWCOUNT,1);
4977 END IF;
4978
4979 EXCEPTION
4980 WHEN OTHERS THEN
4981 oe_debug_pub.add('load_lines_into_bcolt: ' || 'others excpn::'||sqlerrm,1);
4982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4983
4984
4985 END load_lines_into_bcolt;
4986
4987
4988 PROCEDURE update_bcolt_line_status(p_line_id NUMBER,
4989 p_status NUMBER,
4990 x_return_status OUT NOCOPY VARCHAR2) IS
4991 BEGIN
4992
4993 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4994
4995 update bom_cto_order_lines_temp
4996 set status = p_status
4997 where line_id = p_line_id;
4998
4999 EXCEPTION
5000 WHEN OTHERS THEN
5001 oe_debug_pub.add('update_bcolt_line_status: ' || 'others excpn::'||sqlerrm,1);
5002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5003
5004 END update_bcolt_line_status;
5005
5006
5007 --local procedure
5008 --Get chage and accrual account information from OPM
5009 --OPM
5010 Procedure Get_opm_charge_account(p_interface_source_line_id NUMBER
5011 ,p_item_id NUMBER
5012 ,p_destination_org_id NUMBER
5013 ,p_source_type_code VARCHAR2
5014 ,p_operating_unit NUMBER
5015 ,x_charge_account_id OUT NOCOPY NUMBER
5016 ,x_accrual_account_id OUT NOCOPY NUMBER
5017 ,x_return_status OUT NOCOPY VARCHAR2)
5018 IS
5019 l_source_subinventory VARCHAR2(200) := null;
5020 l_vendor_id NUMBER;
5021 l_vendor_site_id NUMBER;
5022 l_sourcing_rule_id NUMBER;
5023 l_sourcing_org NUMBER;
5024 l_error_message VARCHAR2(200);
5025 l_return_code BOOLEAN;
5026 l_category_id NUMBER;
5027 l_cust_site_id NUMBER;
5028 l_cust_id NUMBER;
5029 l_return_status Varchar2(1);
5030 l_msg_data Varchar2(2000);
5031 l_msg_count number;
5032 l_stmt_num number;
5033
5034
5035 BEGIN
5036
5037
5038 x_return_status := FND_API.G_RET_STS_SUCCESS;
5039
5040 IF p_interface_source_line_id IS NOT NULL THEN
5041 --line id is available only for top level configurations
5042
5043
5044 l_stmt_num := 10;
5045 SELECT end_customer_id,
5046 end_customer_site_use_id
5047 INTO l_cust_id,
5048 l_cust_site_id
5049 FROM oe_order_lines_all
5050 WHERE line_id = p_interface_source_line_id;
5051
5052 IF PG_DEBUG <> 0 THEN
5053 oe_debug_pub.add('Get_opm_charge_account: ' || 'Customer ID : '||to_char(l_cust_id),3);
5054 oe_debug_pub.add('Get_opm_charge_account: ' || 'customer site ID : '||to_char(l_cust_site_id),3);
5055 END IF;
5056
5057
5058
5059
5060 END IF;
5061
5062
5063
5064 l_stmt_num := 20;
5065 SELECT mic.category_id
5066 INTO l_category_id
5067 FROM mtl_item_categories mic,
5068 mtl_default_sets_view mdsv
5069 WHERE mic.inventory_item_id = p_item_id
5070 AND mic.organization_id=p_destination_org_id
5071 AND mic.category_set_id = mdsv.category_set_id
5072 AND mdsv.functional_area_id = 2;
5073
5074 IF PG_DEBUG <> 0 THEN
5075 oe_debug_pub.add('Get_opm_charge_account: ' || 'Category id: '||to_char(l_category_id),5);
5076 END IF;
5077
5078
5079 --added this api after taking to divya reddy(PO)
5080 --All the values being passed are verified by Indira Choudhuri of MRP team
5081 --Dependency: this MRP API exists since 11.5.10 MRP code
5082 --arg_commodity_id value is not used within api as per Indira but
5083 --inorder to be consistent will pass it as done by PO.
5084
5085 l_stmt_num := 30;
5086 IF PG_DEBUG <> 0 THEN
5087 oe_debug_pub.add('Get_opm_charge_account: ' || 'Calling MRP_SOURCING_API_PK.mrp_sourcing',5);
5088 END IF;
5089 --call mrp_sourcing to get vendor and vendor_site_id
5090 l_return_code := MRP_SOURCING_API_PK.mrp_sourcing(
5091 arg_mode =>p_source_type_code,
5092 arg_item_id =>p_item_id,
5093 arg_commodity_id =>l_category_id,
5094 arg_dest_organization_id =>p_destination_org_id,
5095 arg_dest_subinventory =>null,
5096 arg_autosource_date =>sysdate,
5097 arg_vendor_id =>l_vendor_id,
5098 arg_vendor_site_id =>l_vendor_site_id,
5099 arg_source_organization_id =>l_sourcing_org,
5100 arg_source_subinventory =>l_source_subinventory,
5101 arg_sourcing_rule_id =>l_sourcing_rule_id,
5102 arg_error_message =>l_error_message);
5103
5104 IF l_return_code THEN
5105 IF PG_DEBUG <> 0 THEN
5106 oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5107 oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5108 oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5109 END IF;
5110
5111
5112 ELSE
5113 IF PG_DEBUG <> 0 THEN
5114 oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5115 oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5116 --talked to usha arora. This error is not important enough to
5117 --stop process
5118 oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5119 END IF;
5120 END IF;
5121
5122 --updating the global record structure variable
5123 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).organization_id := p_destination_org_id;
5124 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).inventory_item_id := p_item_id;
5125 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).ato_flag := 'Y';
5126 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).operating_unit := p_operating_unit;
5127 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).vendor_id := l_vendor_id;
5128 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).vendor_site_id := l_vendor_site_id;
5129 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).customer_id := l_cust_id;
5130 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).customer_site_id := l_cust_site_id;
5131 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).account_type_code := GMF_transaction_accounts_PUB.G_CHARGE_INV_ACCT;
5132
5133 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).organization_id := p_destination_org_id;
5134 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).inventory_item_id := p_item_id;
5135 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).ato_flag := 'Y';
5136 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).operating_unit := p_operating_unit;
5137 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).vendor_id := l_vendor_id;
5138 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).vendor_site_id := l_vendor_site_id;
5139 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).customer_id := l_cust_id;
5140 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).customer_site_id := l_cust_site_id;
5141 GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).account_type_code := GMF_transaction_accounts_PUB.G_ACCRUAL_ACCT;
5142
5143
5144
5145 l_stmt_num := 40;
5146 IF PG_DEBUG <> 0 THEN
5147 oe_debug_pub.add('Get_opm_charge_account: ' || 'calling GMF_transaction_accounts_PUB.get_accounts',5);
5148
5149 END IF;
5150 --SLA api to get charge and accrual account
5151 --Reference aru#4610085
5152 GMF_transaction_accounts_PUB.get_accounts
5153 (
5154 p_api_version => 1.0
5155 , p_init_msg_list => null
5156 , p_source => 'CTO'
5157 , x_return_status => l_return_status
5158 , x_msg_data => l_msg_data
5159 , x_msg_count => l_msg_count
5160
5161 );
5162
5163 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
5164 --i.e.charge account id is
5165 x_charge_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).target_ccid;
5166
5167 IF x_charge_account_id IS NULL THEN
5168 IF PG_DEBUG <> 0 THEN
5169 oe_debug_pub.add('Get_opm_charge_account: ' || 'charge account id is NULL in opm org',1);
5170 oe_debug_pub.add('Get_opm_charge_account: ' || 'USER SHOULD CHECK THE SETUP',1);
5171 END IF;
5172
5173
5174 END IF;
5175
5176 x_accrual_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).target_ccid;
5177
5178 IF PG_DEBUG <> 0 THEN
5179 oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5180 oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5181 oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5182 END IF;
5183
5184
5185 ELSE
5186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5187 END IF;
5188
5189 EXCEPTION
5190
5191 WHEN FND_API.G_EXC_ERROR THEN
5192 IF PG_DEBUG <> 0 THEN
5193 oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5194 END IF;
5195 x_return_status := FND_API.G_RET_STS_ERROR;
5196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5197 IF PG_DEBUG <> 0 THEN
5198 oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5199 END IF;
5200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5201 WHEN OTHERS THEN
5202 IF PG_DEBUG <> 0 THEN
5203 oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::others error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5204 END IF;
5205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5206
5207 END Get_opm_charge_account;
5208
5209
5210
5211
5212 END cto_auto_procure_pk;
[Home] [Help]