[Home] [Help]
PACKAGE BODY: APPS.CTO_RESERVE_CONFIG
Source
1 package body CTO_RESERVE_CONFIG as
2 /* $Header: CTORCFGB.pls 120.3 2011/11/14 12:24:23 abhissri ship $ */
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;
84
81 l_msg_count number;
82 l_msg_data varchar2(150);
83 l_serial_number inv_reservation_global.serial_number_tbl_type;
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 --Bugfix 12374440
133 --l_rsv_rec.subinventory_code := NULL;
134 l_rsv_rec.subinventory_code := p_rec_reserve.f_subinventory_code;
135 l_rsv_rec.subinventory_id := NULL;
136 l_rsv_rec.locator_id := NULL;
137 l_rsv_rec.lot_number := NULL;
138 l_rsv_rec.lot_number_id := NULL;
139 l_rsv_rec.pick_slip_number := NULL;
140 l_rsv_rec.lpn_id := NULL;
141 l_rsv_rec.attribute_category := NULL;
142 l_rsv_rec.attribute1 := NULL;
143 l_rsv_rec.attribute2 := NULL;
144 l_rsv_rec.attribute3 := NULL;
145 l_rsv_rec.attribute4 := NULL;
146 l_rsv_rec.attribute5 := NULL;
147 l_rsv_rec.attribute6 := NULL;
148 l_rsv_rec.attribute7 := NULL;
149 l_rsv_rec.attribute8 := NULL;
150 l_rsv_rec.attribute9 := NULL;
151 l_rsv_rec.attribute10 := NULL;
152 l_rsv_rec.attribute11 := NULL;
153 l_rsv_rec.attribute12 := NULL;
154 l_rsv_rec.attribute13 := NULL;
155 l_rsv_rec.attribute14 := NULL;
156 l_rsv_rec.attribute15 := NULL;
157 l_rsv_rec.ship_ready_flag := NULL;
158
159
160 -- Attempt to make a reservation. Right now, we are hard coding
161 -- that no partial reservations are allowed.
162 --bug#4918197 called the api using named notation
163 inv_reservation_pub.create_reservation(p_api_version_number => 1.0,
164 p_init_msg_lst=>fnd_api.g_true,
165 x_return_status=>x_return_status,
166 x_msg_count=>l_msg_count,
167 x_msg_data=>x_msg_txt,
168 p_rsv_rec=>l_rsv_rec,
169 p_serial_number=>l_serial_number,
170 x_serial_number=>l_serial_number,
171 p_partial_reservation_flag=>fnd_api.g_false,
172 p_force_reservation_flag=>fnd_api.g_true, -- test
173 p_validation_flag =>fnd_api.g_true,
174 x_quantity_reserved=>x_rsrv_qty,
175 x_reservation_id=>x_rsrv_id);
176
177 if (x_return_status <> fnd_api.g_ret_sts_success) then
178 -- Reservation Failed --
179 oe_debug_pub.add( ' message from ctorcfgb.pls ' || x_msg_txt ,1 )
180 ;
181 FND_MESSAGE.parse_encoded( x_msg_txt, v_appl_name, v_error_name ) ;
182
183
184 oe_debug_pub.add( ' message from ctorcfgb.pls ' || v_appl_name || ' ' || v_error_name ,1 ) ;
185
186
187 raise RESERVATION_ERROR;
188 end if;
189
190
191 EXCEPTION
192
193 when RESERVATION_ERROR then
194 fnd_msg_pub.count_and_get(p_count =>l_msg_count,
195 p_data => x_msg_txt);
196 --x_msg_name := 'CTO_RESERVATION_ERROR';
197 --x_msg_txt := replace(x_msg_txt, chr(0), ' ');
198
199 if( v_error_name is null ) then
200
201 x_msg_name := 'CTO_RESERVE_ERROR';
202 else
203 x_msg_name := v_error_name ;
204 end if ;
205
206
207 when OTHERS THEN
208 x_msg_txt := 'CTO_RESERVE_CONFIG.reserve_config:' ||
209 to_char(l_stmt_num)
210 || ':' ||
211 substrb(sqlerrm,1,150);
212 --x_msg_name := 'CTO_RESERVATION_ERROR';
213 x_msg_name := 'CTO_RESERVE_ERROR';
214 END;
215
216
217 end CTO_RESERVE_CONFIG;