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