[Home] [Help]
PACKAGE BODY: APPS.CTO_RESERVE_CONFIG
Source
1 package body CTO_RESERVE_CONFIG as
2 /* $Header: CTORCFGB.pls 120.2 2006/01/03 14:32:04 kkonada noship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : BOMRCFGB.pls |
10 | DESCRIPTION : |
11 | This file creates packaged functions that are required |
12 | to make a reservation against Inventory from the |
13 | Create Reservation workflow activity and the |
14 | Match and Reserve menu item. |
15 | |
16 | reserve_config - Attemps to reserve a configuration item |
17 | against available supply in inventory. It is called from |
18 | Create Reservation workflow activity and from Match |
19 | and Reserve menu item. |
20 | |
21 | Currently, only full reservations are supported (no partial) |
22 | and only reservations from Inventory can be made. |
23 | |
24 | HISTORY : |
25 | May 13, 1999 Angela Makalintal Initial Version |
26 | 05/09/2002 Sushant Sawant |
27 | BUGFIX#2367720 |
28 | reserve_config should return |
29 | proper error message |
30 | |
31 | Oct 25 '02 Kundan Sarkar Bugfix 2644849 (2620282 inbranch ) |
32 | Passing bom_revision info to inventory record l_rsv_rec |
33 =============================================================================*/
34
35 /*****************************************************************************
36 Procedure: reserve_config
37 Parameters: p_model_line_id - line id of the top model in oe_order_lines
38 x_rsrv_qty - quantity resved
39 x_rsrv_id - reservation id in MTL_RESERVATIONS
40 x_return_status - 1 if no expected or unexpected errors;
41 - (-1) for user-defined exceptions;
42 - SQLCODE for SQL process errors;
43 x_msg_txt - error message
44 x_msg_name - name of error message
45
46 Description: This procedure attempts to make a reservation for the
47 configuration item in the p_model_line_id. If a reservation
48 is made, it returns the reserved quantity and the reservation
49 id. It returns x_return_status = 1 if the process completes
50 successfully (no unexpected errors). It returns
51 x_return_status = -1 for user-defined exceptions. Otherwise,
52 it returns x_return_status = SQLCODE.
53
54 Currently, the only reservations supported are full
55 reservations from Inventory supply source. Partial
56 reservations are not supported in this version. Therefore
57 x_rsv_qty will always equal OE_ORDER_LINES.ordered_quantity.
58
59 *****************************************************************************/
60
61 procedure reserve_config(
62 p_rec_reserve in rec_reserve,
63 x_rsrv_qty out nocopy number,
64 x_rsrv_id out nocopy number, --Reservation ID in MTL_RESERVATIONS
65 x_return_status out nocopy VARCHAR2,
66 x_msg_txt out nocopy VARCHAR2, /* 70 bytes to hold returned msg */
67 x_msg_name out nocopy VARCHAR2 /* 30 bytes to hold returned name */
68 )
69
70 IS
71
72 l_stmt_num number := 0;
73 v_appl_name varchar2(200) ;
74 v_error_name varchar2(200) ;
75
76
77 RESERVATION_ERROR exception;
78
79 -- These are the variables passed to Inventory's Reservation API.
80 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
81 l_msg_count number;
82 l_msg_data varchar2(150);
83 l_serial_number inv_reservation_global.serial_number_tbl_type;
84
85 BEGIN
86
87 /********************************************************************
88 Populate Inventory Record and prepare to call Reservation API.
89 ********************************************************************/
90
91 l_rsv_rec.reservation_id := NULL;
92 l_rsv_rec.requirement_date := p_rec_reserve.f_ship_date;
93 l_rsv_rec.organization_id := p_rec_reserve.f_mfg_org_id;
94 l_rsv_rec.inventory_item_id := p_rec_reserve.f_item_id;
95
96 -- bugfix 1799874 : if Internal SO, then pass INV_RESERVATION_GLOBAL.g_source_type_internal_ord
97 -- l_rsv_rec.demand_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE;
98
99
100 if (p_rec_reserve.f_source_document_type_id = 10) then
101 oe_debug_pub.add('Source Document Type Id = 10 which means, it is an INTERNAL ORDER', 1);
102 l_rsv_rec.demand_source_type_id :=
103 INV_RESERVATION_GLOBAL.g_source_type_internal_ord;
104 else
105 oe_debug_pub.add('Source Document Type Id = '||p_rec_reserve.f_source_document_type_id, 1);
106 l_rsv_rec.demand_source_type_id :=
107 INV_RESERVATION_GLOBAL.g_source_type_oe;
108 end if;
109
110 l_rsv_rec.demand_source_name := NULL;
111 l_rsv_rec.demand_source_delivery := NULL;
112 l_rsv_rec.demand_source_header_id := p_rec_reserve.f_header_id;
113 l_rsv_rec.demand_source_line_id := p_rec_reserve.f_line_id;
114 l_rsv_rec.primary_uom_code := NULL;
115 l_rsv_rec.primary_uom_id := NULL;
116 l_rsv_rec.reservation_uom_code := p_rec_reserve.f_order_qty_uom;
117 l_rsv_rec.reservation_uom_id := NULL;
118 l_rsv_rec.reservation_quantity := p_rec_reserve.f_quantity;
119 l_rsv_rec.primary_reservation_quantity := NULL;
120 l_rsv_rec.detailed_quantity := NULL;
121 l_rsv_rec.autodetail_group_id := NULL;
122 l_rsv_rec.external_source_code := NULL;
123 l_rsv_rec.external_source_line_id := NULL;
124 l_rsv_rec.supply_source_type_id := p_rec_reserve.f_supply_source;
125 -- INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
126 l_rsv_rec.supply_source_header_id :=
127 p_rec_reserve.f_supply_header_id;
128 l_rsv_rec.supply_source_line_id := NULL;
129 l_rsv_rec.supply_source_name := NULL;
130 l_rsv_rec.supply_source_line_detail := NULL;
131 l_rsv_rec.revision := p_rec_reserve.f_bom_revision; /* 2620282: Psssing bom revisoion info */
132 l_rsv_rec.subinventory_code := NULL;
133 l_rsv_rec.subinventory_id := NULL;
134 l_rsv_rec.locator_id := NULL;
135 l_rsv_rec.lot_number := NULL;
136 l_rsv_rec.lot_number_id := NULL;
137 l_rsv_rec.pick_slip_number := NULL;
138 l_rsv_rec.lpn_id := NULL;
139 l_rsv_rec.attribute_category := NULL;
140 l_rsv_rec.attribute1 := NULL;
141 l_rsv_rec.attribute2 := NULL;
142 l_rsv_rec.attribute3 := NULL;
143 l_rsv_rec.attribute4 := NULL;
144 l_rsv_rec.attribute5 := NULL;
145 l_rsv_rec.attribute6 := NULL;
146 l_rsv_rec.attribute7 := NULL;
147 l_rsv_rec.attribute8 := NULL;
148 l_rsv_rec.attribute9 := NULL;
149 l_rsv_rec.attribute10 := NULL;
150 l_rsv_rec.attribute11 := NULL;
151 l_rsv_rec.attribute12 := NULL;
152 l_rsv_rec.attribute13 := NULL;
153 l_rsv_rec.attribute14 := NULL;
154 l_rsv_rec.attribute15 := NULL;
155 l_rsv_rec.ship_ready_flag := NULL;
156
157
158 -- Attempt to make a reservation. Right now, we are hard coding
159 -- that no partial reservations are allowed.
160 --bug#4918197 called the api using named notation
161 inv_reservation_pub.create_reservation(p_api_version_number => 1.0,
162 p_init_msg_lst=>fnd_api.g_true,
163 x_return_status=>x_return_status,
164 x_msg_count=>l_msg_count,
165 x_msg_data=>x_msg_txt,
166 p_rsv_rec=>l_rsv_rec,
167 p_serial_number=>l_serial_number,
168 x_serial_number=>l_serial_number,
169 p_partial_reservation_flag=>fnd_api.g_false,
170 p_force_reservation_flag=>fnd_api.g_true, -- test
171 p_validation_flag =>fnd_api.g_true,
172 x_quantity_reserved=>x_rsrv_qty,
173 x_reservation_id=>x_rsrv_id);
174
175 if (x_return_status <> fnd_api.g_ret_sts_success) then
176 -- Reservation Failed --
177 oe_debug_pub.add( ' message from ctorcfgb.pls ' || x_msg_txt ,1 )
178 ;
179 FND_MESSAGE.parse_encoded( x_msg_txt, v_appl_name, v_error_name ) ;
180
181
182 oe_debug_pub.add( ' message from ctorcfgb.pls ' || v_appl_name || ' ' || v_error_name ,1 ) ;
183
184
185 raise RESERVATION_ERROR;
186 end if;
187
188
189 EXCEPTION
190
191 when RESERVATION_ERROR then
192 fnd_msg_pub.count_and_get(p_count =>l_msg_count,
193 p_data => x_msg_txt);
194 --x_msg_name := 'CTO_RESERVATION_ERROR';
195 --x_msg_txt := replace(x_msg_txt, chr(0), ' ');
196
197 if( v_error_name is null ) then
198
199 x_msg_name := 'CTO_RESERVE_ERROR';
200 else
201 x_msg_name := v_error_name ;
202 end if ;
203
204
205 when OTHERS THEN
206 x_msg_txt := 'CTO_RESERVE_CONFIG.reserve_config:' ||
207 to_char(l_stmt_num)
208 || ':' ||
209 substrb(sqlerrm,1,150);
210 --x_msg_name := 'CTO_RESERVATION_ERROR';
211 x_msg_name := 'CTO_RESERVE_ERROR';
212 END;
213
214
215 end CTO_RESERVE_CONFIG;