[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_EFFECTIVITY_PVT
Source
1 PACKAGE BODY GMD_FORMULA_EFFECTIVITY_PVT AS
2 /* $Header: GMDVFMEB.pls 115.12 2002/10/24 21:16:26 santunes noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FORMULA_EFFECTIVITY_PVT';
5
6 /* ============================================= */
7 /* Procedure: */
8 /* Insert_FormulaEffectivity */
9
10 /* DESCRIPTION: */
11 /* This PL/SQL procedure is responsible for */
12 /* inserting a formula effectivity. */
13 /* HISTORY */
14 /* Mohit Kapoor 10-May-2002 Bug 2186284 */
15 /* Modified the insert statement such that the start_date */
16 /* and end_date are inserted without timestamp. */
17 /* ============================================= */
18
19 PROCEDURE Insert_FormulaEffectivity
20 ( p_api_version IN NUMBER ,
21 p_init_msg_list IN varchar2 ,
22 p_commit IN varchar2 ,
23 x_return_status OUT NOCOPY varchar2 ,
24 x_msg_count OUT NOCOPY NUMBER ,
25 x_msg_data OUT NOCOPY VARCHAR2 ,
26 p_formula_effectivity_rec IN fm_form_eff%ROWTYPE
27 )
28 IS
29 /* Local Variables definitions */
30 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULAEFFECTIVITY';
31 l_api_version CONSTANT NUMBER := 1.0;
32
33 BEGIN
34 /* Define Savepoint */
35 SAVEPOINT Insert_FormulaEffectivity_PVT;
36
37 /* Standard Check for API compatibility */
38 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
39 p_api_version ,
40 l_api_name ,
41 G_PKG_NAME )
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45
46 /* Initialize message list if p_init_msg_list is set to TRUE */
47 IF FND_API.to_Boolean( p_init_msg_list ) THEN
48 FND_MSG_PUB.initialize;
49 END IF;
50 /* Initialize API return status to success */
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 /* API Body */
54 /* Later on this insert should be changed to */
55 /* make insert on business view as opposed to tables directly. */
56
57 /* Bug 2186284 Mohit Kapoor */
58 /* Modified p_formula_effectivity_rec.end_date and */
59 /* p_formula_effectivity_rec.start_date to use */
60 /* trunc(p_formula_effectivity_rec.end_date), */
61 /* trunc(p_formula_effectivity_rec.start_date) */
62 INSERT INTO fm_form_eff
63 (fmeff_id, orgn_code,
64 item_id, formula_use,
65 end_date, start_date,
66 inv_min_qty, inv_max_qty,
67 min_qty, max_qty,
68 std_qty, item_um,
69 preference, routing_id,
70 formula_id, cust_id,
71 trans_cnt, text_code,
72 delete_mark, created_by,
73 creation_date, last_update_date,
74 last_updated_by, last_update_login)
75 VALUES
76 (p_formula_effectivity_rec.fmeff_id, p_formula_effectivity_rec.orgn_code,
77 p_formula_effectivity_rec.item_id, p_formula_effectivity_rec.formula_use,
78 TRUNC(p_formula_effectivity_rec.end_date), TRUNC(p_formula_effectivity_rec.start_date),
79 p_formula_effectivity_rec.inv_min_qty, p_formula_effectivity_rec.inv_max_qty,
80 p_formula_effectivity_rec.min_qty, p_formula_effectivity_rec.max_qty,
81 p_formula_effectivity_rec.std_qty, p_formula_effectivity_rec.item_um,
82 p_formula_effectivity_rec.preference, p_formula_effectivity_rec.routing_id,
83 p_formula_effectivity_rec.formula_id, p_formula_effectivity_rec.cust_id,
84 p_formula_effectivity_rec.trans_cnt, p_formula_effectivity_rec.text_code,
85 p_formula_effectivity_rec.delete_mark, p_formula_effectivity_rec.created_by,
86 p_formula_effectivity_rec.creation_date, p_formula_effectivity_rec.last_update_date,
87 p_formula_effectivity_rec.last_updated_by, p_formula_effectivity_rec.last_update_login);
88
89 IF(SQL%ROWCOUNT = 0) THEN
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92
93 /* END API Body */
94
95 /* Check if p_commit is set to TRUE */
96 IF FND_API.To_Boolean( p_commit ) THEN
97 Commit;
98 END IF;
99
100 /* Get the message count and information */
101 FND_MSG_PUB.Count_And_Get (
102 p_count => x_msg_count,
103 p_data => x_msg_data );
104
105
106 EXCEPTION
107 WHEN FND_API.G_EXC_ERROR THEN
108 ROLLBACK to Insert_FormulaEffectivity_PVT;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 FND_MSG_PUB.Count_And_Get (
111 p_count => x_msg_count,
112 p_data => x_msg_data );
113
114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
115 ROLLBACK to Insert_FormulaEffectivity_PVT;
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117 FND_MSG_PUB.Count_And_Get (
118 p_count => x_msg_count,
119 p_data => x_msg_data );
120
121 WHEN OTHERS THEN
122 ROLLBACK to Insert_FormulaEffectivity_PVT;
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 FND_MSG_PUB.Count_And_Get (
125 p_count => x_msg_count,
126 p_data => x_msg_data );
127
128 END Insert_FormulaEffectivity;
129
130
131
132 /* ============================================= */
133 /* Procedure: */
134 /* Update_FormulaEffectivity */
135
136 /* DESCRIPTION: */
137 /* This PL/SQL procedure is responsible for */
138 /* updating a formula effectivity. */
139 /* HISTORY: */
140 /* RajaSekhar 03/02/2002 BUG#2202559 */
141 /* Modified the code to update the effective */
142 /* END_DATE of the frozen formula */
143 /* Mohit Kapoor 10-May-2002 Bug 2186284 */
144 /* Modified the update statement such that the start_date */
145 /* and end_dates are updated without timestamp. */
146 /* K. RajaSekhar Reddy 10/04/2002 BUG#2583665 */
147 /* Modified IF statement to update the formula effectivity record */
148 /* with the existing orgn_code, if it's value is not passed or passed as null. */
149 /* ============================================= */
150
151 PROCEDURE Update_FormulaEffectivity
152 ( p_api_version IN NUMBER ,
153 p_init_msg_list IN VARCHAR2 ,
154 p_commit IN VARCHAR2 ,
155 x_return_status OUT NOCOPY VARCHAR2 ,
156 x_msg_count OUT NOCOPY NUMBER ,
157 x_msg_data OUT NOCOPY VARCHAR2 ,
158 p_formula_effectivity_rec IN fm_form_eff%ROWTYPE
159 )
160 IS
161
162 /* Local Variables definitions */
163 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAEFFECTIVITY';
164 l_api_version CONSTANT NUMBER := 1.0;
165 l_fm_eff_rec fm_form_eff%ROWTYPE;
166
167 l_orgn_code fm_form_eff.orgn_code%TYPE;
168 l_formula_use fm_form_eff.formula_use%TYPE;
169 l_end_date fm_form_eff.end_date%TYPE;
170 l_start_date fm_form_eff.start_date%TYPE;
171 l_inv_min_qty fm_form_eff.inv_min_qty%TYPE;
172 l_inv_max_qty fm_form_eff.inv_max_qty%TYPE;
173 l_min_qty fm_form_eff.min_qty%TYPE;
174 l_max_qty fm_form_eff.max_qty%TYPE;
175 l_std_qty fm_form_eff.std_qty%TYPE;
176 l_item_um fm_form_eff.item_um%TYPE;
177 l_preference fm_form_eff.preference%TYPE;
178
179 /* define cursor */
180 CURSOR get_record(vfmeff_id NUMBER) IS
181 SELECT *
182 FROM fm_form_eff
183 WHERE fmeff_id = vfmeff_id;
184
185 BEGIN
186 /* Define Savepoint */
187 SAVEPOINT Update_FormulaEffectivity_PVT;
188
189 /* Standard Check for API compatibility */
190 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
191 p_api_version ,
192 l_api_name ,
193 G_PKG_NAME )
194 THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 /* Initialize message list if p_init_msg_list is set to TRUE */
199 IF FND_API.to_Boolean( p_init_msg_list ) THEN
200 FND_MSG_PUB.initialize;
201 END IF;
202 /* Initialize API return status to success */
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 /* API body */
206 /* Later on to be changed to update a business view */
207 /* and not a table. */
208
209 /* Certain vaildation to be performed. */
210 OPEN get_record(p_formula_effectivity_rec.fmeff_id);
211 FETCH get_record INTO l_fm_eff_rec;
212
213 /* If any of the required fields are passed in as null set them back to the */
214 /* original value */
215 /* BEGIN BUG#2583665 RajaSekhar */
216 /* = FND_API.G_MISS_CHAR is replaced by IS NULL in the below IF condition */
217 IF (p_formula_effectivity_rec.orgn_code IS NULL) THEN
218 l_orgn_code := l_fm_eff_rec.orgn_code ;
219 ELSE
220 l_orgn_code := p_formula_effectivity_rec.orgn_code;
221 END IF;
222 /* END BUG#2583665*/
223
224 /* Formula use */
225 IF (p_formula_effectivity_rec.formula_use IS NULL) THEN
226 l_formula_use := l_fm_eff_rec.formula_use;
227 ELSE
228 l_formula_use := p_formula_effectivity_rec.formula_use;
229 END IF;
230
231 /* Start date */
232 IF (p_formula_effectivity_rec.start_date IS NULL) THEN
233 l_start_date := l_fm_eff_rec.start_date;
234 ELSE
235 l_start_date := p_formula_effectivity_rec.start_date;
236 END IF;
237
238 IF (p_formula_effectivity_rec.end_date IS NULL) THEN
239 l_end_date := l_fm_eff_rec.end_date;
240 ELSE
241 l_end_date := p_formula_effectivity_rec.end_date;
242 END IF;
243
244 IF (p_formula_effectivity_rec.inv_min_qty IS NULL) THEN
245 l_inv_min_qty := l_fm_eff_rec.inv_min_qty;
246 ELSE
247 l_inv_min_qty := p_formula_effectivity_rec.inv_min_qty;
248 END IF;
249
250 IF (p_formula_effectivity_rec.inv_max_qty IS NULL) THEN
251 l_inv_max_qty := l_fm_eff_rec.inv_max_qty;
252 ELSE
253 l_inv_max_qty := p_formula_effectivity_rec.inv_max_qty;
254 END IF;
255
256 IF (p_formula_effectivity_rec.min_qty IS NULL) THEN
257 l_min_qty := l_fm_eff_rec.min_qty;
258 ELSE
259 l_min_qty := p_formula_effectivity_rec.min_qty;
260 END IF;
261
262 IF (p_formula_effectivity_rec.max_qty IS NULL) THEN
263 l_max_qty := l_fm_eff_rec.max_qty;
264 ELSE
265 l_max_qty := p_formula_effectivity_rec.max_qty;
266 END IF;
267
268 IF (p_formula_effectivity_rec.std_qty IS NULL) THEN
269 l_std_qty := l_fm_eff_rec.std_qty;
270 ELSE
271 l_std_qty := p_formula_effectivity_rec.std_qty;
272 END IF;
273
274 IF (p_formula_effectivity_rec.preference IS NULL) THEN
275 l_preference := l_fm_eff_rec.preference;
276 ELSE
277 l_preference := p_formula_effectivity_rec.preference;
278 END IF;
279
280 IF (p_formula_effectivity_rec.item_um IS NULL) THEN
281 l_item_um := l_fm_eff_rec.item_um;
282 ELSE
283 l_item_um := p_formula_effectivity_rec.item_um;
284 END IF;
285
286 /* Bug 2186284 Mohit Kapoor */
287 /* Modified l_end_date and l_start_date to use */
288 /* TRUNC(l_end_date), TRUNC(l_start_date) */
289 UPDATE fm_form_eff SET
290 orgn_code = l_orgn_code,
291 formula_use = l_formula_use,
292 end_date = TRUNC(l_end_date),
293 start_date = TRUNC(l_start_date),
294 inv_min_qty = l_inv_min_qty,
295 inv_max_qty = l_inv_max_qty,
296 min_qty = l_min_qty,
297 max_qty = l_max_qty,
298 std_qty = l_std_qty,
299 item_um = l_item_um,
300 preference = l_preference,
301 --BEGIN BUG#2202559 RajaSekhar
302 --The columns creation_date and created _by are commented as update
303 --statement should not overwrite them. DECODE is used to update the column
304 --with the database value if it is not passed in PL/SQL record.
305 routing_id = DECODE(p_formula_effectivity_rec.routing_id, NULL, routing_id, p_formula_effectivity_rec.routing_id),
306 cust_id = DECODE(p_formula_effectivity_rec.cust_id, NULL, cust_id, p_formula_effectivity_rec.cust_id),
307 --creation_date = p_formula_effectivity_rec.creation_date,
308 --created_by = p_formula_effectivity_rec.created_by,
309 last_update_date = p_formula_effectivity_rec.last_update_date,
310 last_updated_by = p_formula_effectivity_rec.last_updated_by,
311 delete_mark = DECODE(p_formula_effectivity_rec.delete_mark, NULL, delete_mark, p_formula_effectivity_rec.delete_mark),
312 text_code = DECODE(p_formula_effectivity_rec.text_code, NULL, text_code, p_formula_effectivity_rec.text_code),
313 trans_cnt = DECODE(p_formula_effectivity_rec.trans_cnt, NULL, trans_cnt, p_formula_effectivity_rec.trans_cnt)
314 --END BUG#2202559
315 WHERE
316 fmeff_id = p_formula_effectivity_rec.fmeff_id;
317
318 IF(SQL%ROWCOUNT = 0) THEN
319 RAISE FND_API.G_EXC_ERROR;
320 END IF;
321
322
323 /* End API body */
324
325 /* Check if p_commit is set to TRUE */
326 IF FND_API.To_Boolean( p_commit ) THEN
327 Commit;
328 END IF;
329
330 /* Get the message count and information */
331 FND_MSG_PUB.Count_And_Get (
332 p_count => x_msg_count,
333 p_data => x_msg_data );
334
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 ROLLBACK to Update_FormulaEffectivity_PVT;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 FND_MSG_PUB.Count_And_Get (
341 p_count => x_msg_count,
342 p_data => x_msg_data );
343
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 ROLLBACK to Update_FormulaEffectivity_PVT;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Count_And_Get (
348 p_count => x_msg_count,
349 p_data => x_msg_data );
350
351 WHEN OTHERS THEN
352 ROLLBACK to Update_FormulaEffectivity_PVT;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 FND_MSG_PUB.Count_And_Get (
355 p_count => x_msg_count,
356 p_data => x_msg_data );
357
358 END Update_FormulaEffectivity;
359
360
361 END GMD_FORMULA_EFFECTIVITY_PVT;