1 package CTO_UTILITY_PK as
2 /* $Header: CTOUTILS.pls 120.6.12010000.2 2008/08/14 11:30:05 ntungare ship $*/
3 /*----------------------------------------------------------------------------+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
5 | All rights reserved.
6 | Oracle Manufacturing
7 |
8 |FILE NAME : CTOUTILS.pls
9 |
10 |DESCRIPTION : Contains modules to :
11 | 1. Populate temporary tables bom_cto_order_lines and
12 | bom_cto_src_orgs, used for intermediate CTO processing
13 | 2. Update these tables with the config_item_id
14 | 3. Copy sourcing rule assignments from model to config item
15 |
16 |HISTORY : Created on 04-MAY-2000 by Sajani Sheth
17 | 06/18/01 by Renga Kannan
18 | Get_model_sourcing_org API is moved
19 | from CTOATPIB.pls to keep the dependency
20 | minimal.
21 | Modified on 22-JUN-2001 by Shashi Bhaskaran : bugfix 1811007
22 | Added a new function convert_uom for wt/vol
23 | calculation.
24 | Modified on 18-JUL-2001 by Shashi Bhaskaran : bugfix 1799874
25 | Added a new function get_source_document_id
26 |
27 | Modified on 04-NOV-2001 by Shashi Bhaskaran : bugfix 2001894
28 | Added a new function check_rsv_quantity
29 |
30 | Modified on 27-MAR-2002 by Kiran Konada
31 | changed the signature of GENERATE_BOM_ATTACH_TEXT
32 | removed the signature for GENERATE_ROUTING_ATTACH_TEXT
33 | above changes have been made as part of patchset-H
34 | to be in sync with decisions made for cto-isp page
35 |
36 | Modified on 04-JUN-2002 by Kiran Konada--bugfix 2327972
37 | added a new procedure chk_all_rsv_details.
38 | This procedure gets all the types of reservation
39 | (supply). The reservation details are store in
40 | a table of records
41 | record structure = r_resv_details
42 | table structure = t_resv_details
43 |
44 | Modified on 16-SEP-2002 by Sushant Sawant- copied bugfix for 2474865 from G branch
45 | Added procedure isModelMLMO
46 |
47 |
48 |
49 | 16-Jun-2005 Kiran Konada
50 | Changed signaturre of check_cto_can_create_supply
51 | comment string : OPM
52 |
53 | 29-Jun-2005 Renga Kannan
54 | Added a procedure spec for Cross-dock project
55 |
56 +-----------------------------------------------------------------------------*/
57
58 PC_BOM_PROGRAM_ID number := 1100 ;
59 PC_BOM_VALIDATION_ORG number ; /* This global will be set by preconfigure bom process */
60 PC_BOM_CURRENT_ORG number ; /* This global will be set by preconfigure bom process */
61 PC_BOM_BILL_SEQUENCE_ID number ; /* This global will be set by preconfigure bom process */
62 PC_BOM_TOP_BILL_SEQUENCE_ID number ; /* This global will be set by preconfigure bom process */
63
64
65 /* ERROR CODES FOR CTO EXCEPTION NOTIFICATION */
66
67 OPT_DROP_AND_ITEM_CREATED number := 1 ;
68 OPT_DROP_AND_ITEM_NOT_CREATED number := 2 ;
69 EXP_ERROR_AND_ITEM_CREATED number := 3 ;
70 EXP_ERROR_AND_ITEM_NOT_CREATED number := 4 ;
71
72
73
74
75
76 TYPE EXPECTED_ERROR_INFO_TYPE is record (
77 PROCESS varchar2(100)
78 ,LINE_ID number
79 ,SALES_ORDER_NUM number
80 ,ERROR_MESSAGE varchar2(2000)
81 ,TOP_MODEL_NAME varchar2(1000)
82 ,TOP_MODEL_LINE_NUM varchar2(100)
83 ,TOP_CONFIG_NAME varchar2(1000)
84 ,TOP_CONFIG_LINE_NUM varchar2(100)
85 ,PROBLEM_MODEL varchar2(100)
86 ,PROBLEM_MODEL_LINE_NUM varchar2(100)
87 ,PROBLEM_CONFIG varchar2(1000)
88 ,ERROR_ORG varchar2(100)
89 ,ERROR_ORG_ID number
90 ,REQUEST_ID varchar2(100)
91 , NOTIFY_USER varchar2(1000) ) ;
92
93
94 TYPE t_expected_error_info_type IS TABLE OF EXPECTED_ERROR_INFO_TYPE INDEX BY BINARY_INTEGER;
95
96
97 g_t_expected_error_info t_expected_error_info_type ;
98
99
100 /*--------------------------------------------------------------------------+
101 This function identifies the model items for which configuration items need
102 to be created and populates the temporary table bom_cto_src_orgs with all the
103 organizations that each configuration item needs to be created in.
104 +-------------------------------------------------------------------------*/
105 FUNCTION Populate_Src_Orgs( pTopAtoLineId in number,
106 x_return_status out NOCOPY varchar2,
107 x_msg_count out NOCOPY number,
108 x_msg_data out NOCOPY varchar2)
109 RETURN integer;
110
111
112 /*--------------------------------------------------------------------------+
113 This function populates the table bom_cto_src_orgs with all the organizations
114 in which a configuration item needs to be created.
115 The organizations include all potential sourcing orgs, receiving orgs,
116 OE validation org and PO validation org.
117 The line_id, rcv_org_id, organization_id combination is unique.
118 It is called by Populate_Src_Orgs.
119 +-------------------------------------------------------------------------*/
120 FUNCTION Get_All_Item_Orgs( pLineId in number,
121 pModelItemId in number,
122 pRcvOrgId in number,
123 x_return_status out NOCOPY varchar2,
124 x_msg_count out NOCOPY number,
125 x_msg_data out NOCOPY varchar2)
126 RETURN integer;
127
128
129 /*--------------------------------------------------------------------------+
130 This function updates table bom_cto_order_lines with the config_item_id for
131 a given model item.
132 It is called by "Match" and "Create_Item" programs.
133 +-------------------------------------------------------------------------*/
134 FUNCTION Update_Order_Lines(pLineId in number,
135 pModelId in number,
136 pConfigId in number)
137 RETURN integer;
138
139
140 /*--------------------------------------------------------------------------+
141 This function updates table bom_cto_src_orgs with the config_item_id for
142 a given model item.
143 It is called by "Match" and "Create_Item" programs.
144 +-------------------------------------------------------------------------*/
145 FUNCTION Update_Src_Orgs(pLineId in number,
146 pModelId in number,
147 pConfigId in number)
148 RETURN integer;
149
150
151 /*--------------------------------------------------------------------------+
152 This procedure creates sourcing information for a configuration item.
153 It copies the sourcing rule assignment of the model into the configuration
154 item and adds this assignment to the MRP default assignment set.
155 +-------------------------------------------------------------------------*/
156 PROCEDURE Create_Sourcing_Rules(pModelItemId in number,
157 pConfigId in number,
158 pRcvOrgId in number,
159 x_return_status out NOCOPY varchar2,
160 x_msg_count out NOCOPY number,
161 x_msg_data out NOCOPY varchar2);
162
163
164 /*--------------------------------------------------------------------------+
165 This procedure populates information in bom_cto_order_lines table.
166 It assigns plan level, parent ato line id to each record after copying all
167 components related to a specific top level item.
168 +-------------------------------------------------------------------------*/
169 PROCEDURE POPULATE_BCOL
170 ( p_bcol_line_id bom_cto_order_lines.line_id%type,
171 x_return_status out NOCOPY varchar2,
172 x_msg_count out NOCOPY number,
173 x_msg_data out NOCOPY varchar2,
174 p_reschedule in varchar2 default 'N') ;
175
176
177 -- The followin API get_model_sourcing_org is added by Renga Kannan as part of moving this
178 -- API from CTOATPIB.pls to CTOUTILB.pls
179 -- This is added on 06/18/2001
180
181
182 PROCEDURE get_model_sourcing_org(
183 p_inventory_item_id NUMBER
184 , p_organization_id NUMBER
185 , p_sourcing_rule_exists out NOCOPY varchar2
186 , p_sourcing_org out NOCOPY NUMBER
187 , p_source_type out NOCOPY NUMBER --- Added by Renga for BUY MODEL
188 , p_transit_lead_time out NOCOPY NUMBER
189 , x_return_status out NOCOPY varchar2
190 , x_exp_error_code out NOCOPY number
191 , p_line_id in number default null
192 , p_ship_set_name in varchar2 default null
193 ) ;
194
195
196 PROCEDURE query_sourcing_org(
197 p_inventory_item_id NUMBER
198 , p_organization_id NUMBER
199 , p_sourcing_rule_exists out NOCOPY varchar2
200 , p_source_type out NOCOPY NUMBER -- Added by Renga Kannan on 08/21/01
201 , p_sourcing_org out NOCOPY NUMBER
202 , p_transit_lead_time out NOCOPY NUMBER
203 , x_exp_error_code out NOCOPY NUMBER
204 , x_return_status out NOCOPY varchar2
205 );
206
207
208
209 -- bugfix 1811007 begin
210 -- Added a new function convert_uom
211 --
212 -- Procedure: Convert_Uom
213 -- Parameters: from_uom - Uom code to convert from
214 -- to_uom - Uom code to convert to
215 -- quantity - quantity to convert
216 -- item_id - inventory item id
217 -- Description: This procedure will convert quantity from one Uom to another by
218 -- calling an inventory convert uom procedure
219 --
220
221 FUNCTION convert_uom(from_uom IN VARCHAR2,
222 to_uom IN VARCHAR2,
223 quantity IN NUMBER,
224 item_id IN NUMBER DEFAULT NULL) RETURN NUMBER;
225
226 -- The following pragma is used to allow convert_uom to be used in a select statement
227 -- WNDS : Write No Database State (does not allow tables to be altered)
228
229 pragma restrict_references (convert_uom, WNDS);
230
231 -- bugfix 1811007 end
232
233
234 --bugfix 1799874 : Added function get_source_document_id to fetch the source_document_id
235 FUNCTION get_source_document_id (pLineId in number) RETURN NUMBER;
236
237
238 --begin bugfix 2001824
239 /*-------------------------------------------------------------------------+
240 bugfix 2001824 : Added function check_rsv_quantity to check if the quantity
241 being unreserved is okay or not.
242 If the qty passed by INV is more than "unshipped" quantity,
243 error out.
244 Parameters : p_order_line_id : order line id
245 p_rsv_quantity : Unreserve Qty
246 +--------------------------------------------------------------------------*/
247 FUNCTION check_rsv_quantity (p_order_line_id IN NUMBER,
248 p_rsv_quantity IN NUMBER ) RETURN BOOLEAN ;
249
250 --end bugfix 2001824
251
252
253 PROCEDURE CREATE_ATTACHMENT(
254 p_item_id IN mtl_system_items.inventory_item_id%type,
255 p_org_id IN mtl_system_items.organization_id%type,
256 p_text IN Long,
257 p_desc IN varchar2,
258 p_doc_type In Varchar2,
259 x_return_status OUT NOCOPY varchar2);
260
261
262 PO_VALIDATION_ORG mtl_system_items.organization_id%type;
263
264
265
266 PROCEDURE GENERATE_BOM_ATTACH_TEXT
267 (p_line_id bom_cto_src_orgs.line_id%type ,
268 x_text in out NOCOPY long,
269 x_return_status out NOCOPY Varchar2
270 );
271
272
273 FUNCTION CHECK_CONFIG_ITEM(
274 p_parent_item_id IN Mtl_system_items.inventory_item_id%type,
275 p_inventory_item_id IN Mtl_system_items.inventory_item_id%type,
276 p_organization_id IN Mtl_system_items.organization_id%type) RETURN Varchar2;
277
278 /*---------------------------------------------------------------------------------------------
279 Procedure : chk_all_rsv_details --bugfix 2327972
280 Description: This procedure gets the different types of reservation done on a line_id (item)
281 When a reservation exists,It returns success and reservation qunatity, reservation id and type of supply are stored in table of records.
282 Input: p_line_Id in --line_id
283 p_rsv_details out --table of records
284 x_msg_count out
285 x_msg_data out
286 x_return_status out -returns 'S' if reservation exists
287 --returns 'F' if there is no reservation
288
289 -----------------------------------------------------------------------------*/
290
291 TYPE r_resv_details IS RECORD(
292 l_reservation_id NUMBER,
293 l_reservation_quantity NUMBER,
294 l_supply_source_type_id NUMBER);
295
296 TYPE t_resv_details IS TABLE OF r_resv_details INDEX BY BINARY_INTEGER;
297
298
299 Procedure chk_all_rsv_details
300 (
301 p_line_id in number ,
302 p_rsv_details out NOCOPY t_resv_details,
303 x_msg_count out NOCOPY number ,
304 x_msg_data out NOCOPY varchar2,
305 x_return_status out NOCOPY varchar2
306 );
307
308
309
310
311
312 FUNCTION isModelMLMO( p_bill_sequence_id in number )
313 return number ;
314
315
316 FUNCTION create_isp_bom
317 (p_item_id IN number,
318 p_org_id IN number)
319 RETURN NUMBER;
320
321 FUNCTION concat_values(
322 p_value1 IN varchar2,
323 p_value2 IN number)
324 RETURN Varchar2;
325
326 procedure copy_cost(
327 p_src_cost_type_id number
328 , p_dest_cost_type_id number
329 , p_config_item_id number
330 , p_organization_id number
331 ) ;
332
333
334 --This procedure checks if pllanning needs to create supply
335 --or CTO can create supply
336 --x_can_create_supply = Y : CTO
337 -- = N : Planning
338 --Calls
339 --1. custom API Check_supply
340 --2. query sourcing org
341 --added by KKONADA
342 PROCEDURE check_cto_can_create_supply (
343 P_config_item_id IN number,
344 P_org_id IN number,
345 x_can_create_supply OUT NOCOPY Varchar2,
346 --p_source_type OUT NOCOPY Varchar2,
347 p_source_type OUT NOCOPY number, --Bugfix 6470516
348 x_return_status OUT NOCOPY varchar2,
349 X_msg_count OUT NOCOPY number,
350 X_msg_data OUT NOCOPY Varchar2,
351 x_sourcing_org OUT NOCOPY NUMBER, --R12 OPM
352 x_message OUT NOCOPY varchar2 --R12 OPM
353
354
355 );
356
357
358
359
360 procedure split_line (
361 p_ato_line_id in number,
362 x_return_status OUT nocopy varchar2,
363 x_msg_count OUT nocopy number,
364 x_msg_data OUT nocopy Varchar2
368
365 );
366
367
369 procedure adjust_bcol_for_split(
370 p_ato_line_id in number ,
371 x_return_status out nocopy varchar2,
372 x_msg_count out nocopy number,
373 x_msg_data out nocopy varchar2
374 );
375
376 procedure adjust_bcol_for_warehouse(
377 p_ato_line_id in number ,
378 x_return_status out nocopy varchar2,
379 x_msg_count out nocopy number,
380 x_msg_data out nocopy varchar2
381 );
382
383
384
385 PROCEDURE Reservation_Exists(
386 Pconfiglineid in number,
387 x_return_status out nocopy varchar2,
388 x_result out nocopy boolean,
389 X_Msg_Count out nocopy number,
390 X_Msg_Data out nocopy varchar2);
391
392
393
394
395
396 procedure copy_bcolgt_bcol( p_ato_line_id in number,
397 x_return_status out NOCOPY varchar2,
398 x_msg_count out NOCOPY number,
399 x_msg_data out NOCOPY varchar2) ;
400
401 procedure copy_bcol_bcolgt( p_ato_line_id in number,
402 x_return_status out NOCOPY varchar2,
403 x_msg_count out NOCOPY number,
404 x_msg_data out NOCOPY varchar2) ;
405
406
407
408 procedure send_notification(
409 P_PROCESS in varchar2
410 ,P_LINE_ID in number
411 ,P_SALES_ORDER_NUM in number
412 ,P_ERROR_MESSAGE in varchar2
413 ,P_TOP_MODEL_NAME in varchar2
414 ,P_TOP_MODEL_LINE_NUM in varchar2
415 ,P_TOP_CONFIG_NAME in varchar2
416 ,P_TOP_CONFIG_LINE_NUM in varchar2
417 ,P_PROBLEM_MODEL in varchar2
418 ,P_PROBLEM_MODEL_LINE_NUM in varchar2
419 ,P_PROBLEM_CONFIG in varchar2
420 ,P_ERROR_ORG in varchar2
421 ,P_NOTIFY_USER in varchar2
422 ,P_REQUEST_ID in varchar2
423 ,P_MFG_REL_DATE in date default null
424 );
425
426 procedure notify_expected_errors ( P_PROCESS in varchar2
427 ,P_LINE_ID in number
428 ,P_SALES_ORDER_NUM in number
429 ,P_TOP_MODEL_NAME in varchar2
430 ,P_TOP_MODEL_LINE_NUM in varchar2
431 ,P_MSG_COUNT in number
432 ,P_NOTIFY_USER in varchar2
433 ,P_REQUEST_ID in varchar2
434 ,P_ERROR_MESSAGE in varchar2 default null
435 ,P_TOP_CONFIG_NAME in varchar2 default null
436 ,P_TOP_CONFIG_LINE_NUM in varchar2 default null
437 ,P_PROBLEM_MODEL in varchar2 default null
438 ,P_PROBLEM_MODEL_LINE_NUM in varchar2 default null
439 ,P_PROBLEM_CONFIG in varchar2 default null
440 ,P_ERROR_ORG in varchar2 default null
441 ) ;
442
443 /* activity hold for create config */
444 PROCEDURE APPLY_CREATE_CONFIG_HOLD( p_line_id in number
445 , p_header_id in number
446 , x_return_status out NOCOPY varchar2
447 , x_msg_count out NOCOPY number
448 , x_msg_data out NOCOPY varchar2) ;
449
450
451
452 procedure handle_expected_error( p_error_type in number
453 , p_inventory_item_id in number
454 , p_organization_id in number
455 , p_line_id in number
456 , p_sales_order_num in number
457 , p_top_model_name in varchar2
458 , p_top_model_line_num in varchar2
459 , p_top_config_name in varchar2 default null
460 , p_top_config_line_num in varchar2 default null
461 , p_msg_count in number
462 , p_planner_code in varchar2
463 , p_request_id in varchar2
464 , p_process in varchar2 ) ;
465
466
467 procedure get_planner_code( p_inventory_item_id in number
468 , p_organization_id in number
469 , x_planner_code out NOCOPY fnd_user.user_name%type ) ;
470
471
472 procedure send_oid_notification ;
473
474
478 x_msg_count out nocopy Number,
475 Procedure Create_item_attachments(
476 p_ato_line_id in Number,
477 x_return_status out nocopy Varchar2,
479 x_msg_data out nocopy Varchar2);
480
481 --
482 -- bugfix 4227993: Added lock_for_match procedure for acquiring user-locks for match
483 --
484 --
485 -- bug 7203643
486 -- changed the hash value variable type to varchar2
487 -- ntungare
488 --
489 PROCEDURE lock_for_match(
490 x_return_status OUT nocopy varchar2,
491 xMsgCount OUT nocopy number,
492 xMsgData OUT nocopy varchar2,
493 x_lock_status OUT nocopy number,
494 x_hash_value OUT nocopy varchar2,
495 p_line_id IN number);
496 --
497 -- bug 7203643
498 -- changed the hash value variable type to varchar2
499 -- ntungare
500 --
501 PROCEDURE release_lock(
502 x_return_status OUT NOCOPY VARCHAR2
503 , x_msg_count OUT NOCOPY NUMBER
504 , x_msg_data OUT NOCOPY VARCHAR2
505 , p_hash_value IN varchar2);
506
507
508
509 -- bugfix 4044709: Created new validation procedure
510 PROCEDURE validate_oe_data ( p_bcol_line_id in bom_cto_order_lines.line_id%type,
511 x_return_status out NOCOPY varchar2);
512
513
514
515 FUNCTION get_cto_item_attachment(p_item_id in number,
516 p_po_val_org_id in number,
517 x_return_status out NOCOPY varchar2)
518 RETURN clob;
519
520
521 -- Added for Cross Docking Project
522
523 TYPE cur_var_type is RECORD (
524 primary_reservation_quantity Number,
525 secondary_reservation_quantity Number,--opm and ireq
526 supply_source_type_id Number
527 );
528
529 TYPE resv_tbl_rec_type is TABLE OF cur_var_type INDEX BY Binary_integer;
530
531 -- The following constants are mimicking demand source type ids
532 -- in inv_reservation_global package for flow qty, ext req interface rec qty,
533 -- int req interface qty. It will be used in get_resv_qty_and_code procedure.
534
535 g_source_type_flow CONSTANT NUMBER := 1000 ;
536 g_source_type_ext_req_if CONSTANT NUMBER := 1001 ;
537 g_source_type_int_req_if CONSTANT NUMBER := 1003 ;
538
539
540 /*******************************************************************************************
541 -- API name : get_resv_qty
542 -- Type : Public
543 -- Pre-reqs : None.
544 -- Function : Given config/ato item Order line id it returns
545 -- the supply details tied to this line in a record structure. Also, it return the
546 total supply qty in primary uom and pass the primary uom code to the calling module.
547 -- Parameters:
548 -- IN : p_order_line_id Expects the config/ato item order line Required
549 --
550 -- OUT : x_rsv_rec Record strcutre with each supply type
551 and supply qty in primary uom
552 x_primary_uom_code Primary uom code of the order line's
553 inventory item id .
554 x_sum_rsv_qty Sum of supply quantities tied to the
555 order line in primary uom.
556 x_return_status Standard error message status
557 x_msg_count Std. error message count in the message stack
558 x_msg_data Std. error message data in the message stack
559 -- Version :
560 --
561 --
562 ******************************************************************************************/
563 PROCEDURE Get_Resv_Qty
564 (
565 p_order_line_id NUMBER,
566 x_rsv_rec OUT NOCOPY CTO_UTILITY_PK.resv_tbl_rec_type,
570 x_msg_count OUT NOCOPY NUMBER,
567 x_primary_uom_code OUT NOCOPY VARCHAR2,
568 x_sum_rsv_qty OUT NOCOPY NUMBER,
569 x_return_status OUT NOCOPY VARCHAR2,
571 x_msg_data OUT NOCOPY VARCHAR2
572 );
573
574
575 END CTO_UTILITY_PK;
[Home] [Help]