4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
1 package CTO_UTILITY_PK AUTHID CURRENT_USER as
2 /* $Header: CTOUTILS.pls 120.7.12020000.2 2012/07/05 09:41:17 ntungare ship $*/
3 /*----------------------------------------------------------------------------+
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
37 | added a new procedure chk_all_rsv_details.
34 | to be in sync with decisions made for cto-isp page
35 |
36 | Modified on 04-JUN-2002 by Kiran Konada--bugfix 2327972
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
143 It is called by "Match" and "Create_Item" programs.
140 /*--------------------------------------------------------------------------+
141 This function updates table bom_cto_src_orgs with the config_item_id for
142 a given model item.
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 -- Bugfix 13554996: Adding two new parameters: lot_number and organization_id.
213 --
214 -- Procedure: Convert_Uom
215 -- Parameters: from_uom - Uom code to convert from
216 -- to_uom - Uom code to convert to
217 -- quantity - quantity to convert
218 -- item_id - inventory item id
219 -- lot_number - lot number to be used for lot level uom conversions
220 -- organization_id - organization against which the reservation is created
221 -- Description: This procedure will convert quantity from one Uom to another by
222 -- calling an inventory convert uom procedure
223 --
224
225 FUNCTION convert_uom(from_uom IN VARCHAR2,
226 to_uom IN VARCHAR2,
227 quantity IN NUMBER,
228 item_id IN NUMBER DEFAULT NULL,
229 lot_number IN VARCHAR2 DEFAULT NULL,
230 organization_id IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
231
232 -- The following pragma is used to allow convert_uom to be used in a select statement
233 -- WNDS : Write No Database State (does not allow tables to be altered)
234
235 pragma restrict_references (convert_uom, WNDS);
236
237 -- bugfix 1811007 end
238
239
240 --bugfix 1799874 : Added function get_source_document_id to fetch the source_document_id
241 FUNCTION get_source_document_id (pLineId in number) RETURN NUMBER;
242
243
244 --begin bugfix 2001824
245 /*-------------------------------------------------------------------------+
246 bugfix 2001824 : Added function check_rsv_quantity to check if the quantity
247 being unreserved is okay or not.
248 If the qty passed by INV is more than "unshipped" quantity,
249 error out.
250 Parameters : p_order_line_id : order line id
251 p_rsv_quantity : Unreserve Qty
252 +--------------------------------------------------------------------------*/
253 FUNCTION check_rsv_quantity (p_order_line_id IN NUMBER,
254 p_rsv_quantity IN NUMBER ) RETURN BOOLEAN ;
255
256 --end bugfix 2001824
257
258
259 PROCEDURE CREATE_ATTACHMENT(
260 p_item_id IN mtl_system_items.inventory_item_id%type,
261 p_org_id IN mtl_system_items.organization_id%type,
262 p_text IN Long,
266
263 p_desc IN varchar2,
264 p_doc_type In Varchar2,
265 x_return_status OUT NOCOPY varchar2);
267
268 PO_VALIDATION_ORG mtl_system_items.organization_id%type;
269
270
271
272 PROCEDURE GENERATE_BOM_ATTACH_TEXT
273 (p_line_id bom_cto_src_orgs.line_id%type ,
274 x_text in out NOCOPY long,
275 x_return_status out NOCOPY Varchar2
276 );
277
278
279 FUNCTION CHECK_CONFIG_ITEM(
280 p_parent_item_id IN Mtl_system_items.inventory_item_id%type,
281 p_inventory_item_id IN Mtl_system_items.inventory_item_id%type,
282 p_organization_id IN Mtl_system_items.organization_id%type) RETURN Varchar2;
283
284 /*---------------------------------------------------------------------------------------------
285 Procedure : chk_all_rsv_details --bugfix 2327972
286 Description: This procedure gets the different types of reservation done on a line_id (item)
290 x_msg_count out
287 When a reservation exists,It returns success and reservation qunatity, reservation id and type of supply are stored in table of records.
288 Input: p_line_Id in --line_id
289 p_rsv_details out --table of records
291 x_msg_data out
292 x_return_status out -returns 'S' if reservation exists
293 --returns 'F' if there is no reservation
294
295 -----------------------------------------------------------------------------*/
296
297 TYPE r_resv_details IS RECORD(
298 l_reservation_id NUMBER,
299 l_reservation_quantity NUMBER,
300 l_supply_source_type_id NUMBER);
301
302 TYPE t_resv_details IS TABLE OF r_resv_details INDEX BY BINARY_INTEGER;
303
304
305 Procedure chk_all_rsv_details
306 (
307 p_line_id in number ,
308 p_rsv_details out NOCOPY t_resv_details,
309 x_msg_count out NOCOPY number ,
310 x_msg_data out NOCOPY varchar2,
311 x_return_status out NOCOPY varchar2
312 );
313
314
315
316
317
318 FUNCTION isModelMLMO( p_bill_sequence_id in number )
319 return number ;
320
321
322 FUNCTION create_isp_bom
323 (p_item_id IN number,
324 p_org_id IN number)
325 RETURN NUMBER;
326
327 FUNCTION concat_values(
328 p_value1 IN varchar2,
329 p_value2 IN number)
330 RETURN Varchar2;
331
332 procedure copy_cost(
333 p_src_cost_type_id number
334 , p_dest_cost_type_id number
335 , p_config_item_id number
336 , p_organization_id number
337 ) ;
338
339
340 --This procedure checks if pllanning needs to create supply
341 --or CTO can create supply
342 --x_can_create_supply = Y : CTO
343 -- = N : Planning
344 --Calls
345 --1. custom API Check_supply
346 --2. query sourcing org
347 --added by KKONADA
348 PROCEDURE check_cto_can_create_supply (
349 P_config_item_id IN number,
350 P_org_id IN number,
351 x_can_create_supply OUT NOCOPY Varchar2,
352 --p_source_type OUT NOCOPY Varchar2,
353 p_source_type OUT NOCOPY number, --Bugfix 6470516
354 x_return_status OUT NOCOPY varchar2,
355 X_msg_count OUT NOCOPY number,
356 X_msg_data OUT NOCOPY Varchar2,
357 x_sourcing_org OUT NOCOPY NUMBER, --R12 OPM
358 x_message OUT NOCOPY varchar2 --R12 OPM
359
360
361 );
362
363
364
365
366 procedure split_line (
367 p_ato_line_id in number,
368 x_return_status OUT nocopy varchar2,
369 x_msg_count OUT nocopy number,
370 x_msg_data OUT nocopy Varchar2
371 );
372
373
374
375 procedure adjust_bcol_for_split(
376 p_ato_line_id in number ,
377 x_return_status out nocopy varchar2,
378 x_msg_count out nocopy number,
379 x_msg_data out nocopy varchar2
380 );
381
382 procedure adjust_bcol_for_warehouse(
383 p_ato_line_id in number ,
384 x_return_status out nocopy varchar2,
385 x_msg_count out nocopy number,
386 x_msg_data out nocopy varchar2
387 );
388
389
390
391 PROCEDURE Reservation_Exists(
395 X_Msg_Count out nocopy number,
392 Pconfiglineid in number,
393 x_return_status out nocopy varchar2,
394 x_result out nocopy boolean,
396 X_Msg_Data out nocopy varchar2);
397
398
399
400
401
402 procedure copy_bcolgt_bcol( p_ato_line_id in number,
403 x_return_status out NOCOPY varchar2,
404 x_msg_count out NOCOPY number,
405 x_msg_data out NOCOPY varchar2) ;
406
407 procedure copy_bcol_bcolgt( p_ato_line_id in number,
408 x_return_status out NOCOPY varchar2,
409 x_msg_count out NOCOPY number,
410 x_msg_data out NOCOPY varchar2) ;
411
412
413
414 procedure send_notification(
415 P_PROCESS in varchar2
416 ,P_LINE_ID in number
417 ,P_SALES_ORDER_NUM in number
418 ,P_ERROR_MESSAGE in varchar2
419 ,P_TOP_MODEL_NAME in varchar2
420 ,P_TOP_MODEL_LINE_NUM in varchar2
421 ,P_TOP_CONFIG_NAME in varchar2
422 ,P_TOP_CONFIG_LINE_NUM in varchar2
423 ,P_PROBLEM_MODEL in varchar2
424 ,P_PROBLEM_MODEL_LINE_NUM in varchar2
425 ,P_PROBLEM_CONFIG in varchar2
426 ,P_ERROR_ORG in varchar2
427 ,P_NOTIFY_USER in varchar2
428 ,P_REQUEST_ID in varchar2
429 ,P_MFG_REL_DATE in date default null
430 );
431
432 procedure notify_expected_errors ( P_PROCESS in varchar2
433 ,P_LINE_ID in number
434 ,P_SALES_ORDER_NUM in number
435 ,P_TOP_MODEL_NAME in varchar2
436 ,P_TOP_MODEL_LINE_NUM in varchar2
437 ,P_MSG_COUNT in number
438 ,P_NOTIFY_USER in varchar2
439 ,P_REQUEST_ID in varchar2
440 ,P_ERROR_MESSAGE in varchar2 default null
441 ,P_TOP_CONFIG_NAME in varchar2 default null
442 ,P_TOP_CONFIG_LINE_NUM in varchar2 default null
443 ,P_PROBLEM_MODEL in varchar2 default null
444 ,P_PROBLEM_MODEL_LINE_NUM in varchar2 default null
448
445 ,P_PROBLEM_CONFIG in varchar2 default null
446 ,P_ERROR_ORG in varchar2 default null
447 ) ;
449 /* activity hold for create config */
450 PROCEDURE APPLY_CREATE_CONFIG_HOLD( p_line_id in number
451 , p_header_id in number
452 , x_return_status out NOCOPY varchar2
453 , x_msg_count out NOCOPY number
454 , x_msg_data out NOCOPY varchar2) ;
455
456
457
458 procedure handle_expected_error( p_error_type in number
459 , p_inventory_item_id in number
460 , p_organization_id in number
461 , p_line_id in number
462 , p_sales_order_num in number
463 , p_top_model_name in varchar2
464 , p_top_model_line_num in varchar2
465 , p_top_config_name in varchar2 default null
466 , p_top_config_line_num in varchar2 default null
467 , p_msg_count in number
468 , p_planner_code in varchar2
469 , p_request_id in varchar2
470 , p_process in varchar2 ) ;
471
472
473 procedure get_planner_code( p_inventory_item_id in number
474 , p_organization_id in number
475 , x_planner_code out NOCOPY fnd_user.user_name%type ) ;
479
476
477
478 procedure send_oid_notification ;
480
481 Procedure Create_item_attachments(
482 p_ato_line_id in Number,
483 x_return_status out nocopy Varchar2,
484 x_msg_count out nocopy Number,
485 x_msg_data out nocopy Varchar2);
486
487 --
488 -- bugfix 4227993: Added lock_for_match procedure for acquiring user-locks for match
489 --
490 --
491 -- bug 7203643
492 -- changed the hash value variable type to varchar2
493 -- ntungare
494 --
495 PROCEDURE lock_for_match(
496 x_return_status OUT nocopy varchar2,
497 xMsgCount OUT nocopy number,
498 xMsgData OUT nocopy varchar2,
499 x_lock_status OUT nocopy number,
500 x_hash_value OUT nocopy varchar2,
501 p_line_id IN number);
502 --
503 -- bug 7203643
504 -- changed the hash value variable type to varchar2
505 -- ntungare
506 --
507 PROCEDURE release_lock(
508 x_return_status OUT NOCOPY VARCHAR2
509 , x_msg_count OUT NOCOPY NUMBER
510 , x_msg_data OUT NOCOPY VARCHAR2
511 , p_hash_value IN varchar2);
512
513
514
515 -- bugfix 4044709: Created new validation procedure
516 PROCEDURE validate_oe_data ( p_bcol_line_id in bom_cto_order_lines.line_id%type,
517 x_return_status out NOCOPY varchar2);
518
519
520
521 FUNCTION get_cto_item_attachment(p_item_id in number,
522 p_po_val_org_id in number,
523 x_return_status out NOCOPY varchar2)
524 RETURN clob;
525
526
527 -- Added for Cross Docking Project
528
529 TYPE cur_var_type is RECORD (
530 primary_reservation_quantity Number,
531 secondary_reservation_quantity Number,--opm and ireq
532 supply_source_type_id Number);
533
534 TYPE resv_tbl_rec_type is TABLE OF cur_var_type INDEX BY Binary_integer;
535
536 -- The following constants are mimicking demand source type ids
537 -- in inv_reservation_global package for flow qty, ext req interface rec qty,
538 -- int req interface qty. It will be used in get_resv_qty_and_code procedure.
539
540 g_source_type_flow CONSTANT NUMBER := 1000 ;
541 g_source_type_ext_req_if CONSTANT NUMBER := 1001 ;
542 g_source_type_int_req_if CONSTANT NUMBER := 1003 ;
543
544
545 /*******************************************************************************************
546 -- API name : get_resv_qty
547 -- Type : Public
548 -- Pre-reqs : None.
549 -- Function : Given config/ato item Order line id it returns
550 -- the supply details tied to this line in a record structure. Also, it return the
551 total supply qty in primary uom and pass the primary uom code to the calling module.
552 -- Parameters:
553 -- IN : p_order_line_id Expects the config/ato item order line Required
554 --
555 -- OUT : x_rsv_rec Record strcutre with each supply type
556 and supply qty in primary uom
557 x_primary_uom_code Primary uom code of the order line's
558 inventory item id .
559 x_sum_rsv_qty Sum of supply quantities tied to the
560 order line in primary uom.
561 x_return_status Standard error message status
562 x_msg_count Std. error message count in the message stack
563 x_msg_data Std. error message data in the message stack
564 -- Version :
565 --
566 --
567 ******************************************************************************************/
568 PROCEDURE Get_Resv_Qty
569 (
570 p_order_line_id NUMBER,
571 x_rsv_rec OUT NOCOPY CTO_UTILITY_PK.resv_tbl_rec_type,
572 x_primary_uom_code OUT NOCOPY VARCHAR2,
573 x_sum_rsv_qty OUT NOCOPY NUMBER,
574 x_return_status OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2
577 );
578
579
580 END CTO_UTILITY_PK;
[Home] [Help]