[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_HEADER_PVT
Source
1 PACKAGE BODY GMD_FORMULA_HEADER_PVT AS
2 /* $Header: GMDVFMHB.pls 120.3.12010000.2 2009/12/04 09:09:58 kannavar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FORMULA_HEADER_PVT';
5
6 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
7 --Forward declaration.
8 FUNCTION set_debug_flag RETURN VARCHAR2;
9 l_debug VARCHAR2(1) := set_debug_flag;
10
11 FUNCTION set_debug_flag RETURN VARCHAR2 IS
12 l_debug VARCHAR2(1):= 'N';
13 BEGIN
14 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
15 l_debug := 'Y';
16 END IF;
17 RETURN l_debug;
18 END set_debug_flag;
19 --Bug 3222090, NSRIVAST 20-FEB-2004, END
20
21 /* ======================================================================== */
22 /* Procedure: */
23 /* Insert_FormulaDetail */
24 /* */
25 /* DESCRIPTION: */
26 /* This PL/SQL procedure is responsible for inserting a formula. */
27 /* ======================================================================== */
28 PROCEDURE Insert_FormulaHeader
29 ( p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2
31 ,p_commit IN VARCHAR2
32 ,x_return_status OUT NOCOPY VARCHAR2
33 ,x_msg_count OUT NOCOPY NUMBER
34 ,x_msg_data OUT NOCOPY VARCHAR2
35 ,p_formula_header_rec IN FM_FORM_MST%ROWTYPE
36 )
37 IS
38 /* Local Variables definitions */
39 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULAHEADER';
40 l_api_version CONSTANT NUMBER := 1.0;
41
42 formula_rowid VARCHAR2(32);
43 v_count NUMBER;
44 X_msg_cnt NUMBER;
45 X_msg_dat VARCHAR2(100);
46 X_status VARCHAR2(1);
47 l_product_qty NUMBER;
48 l_ing_qty NUMBER;
49 l_uom VARCHAR2(4);
50
51 /* Bug No.9077438 - Start */
52 Cursor Cur_fm_sec(vOrgn_id NUMBER) IS
53 select user_ind, responsibility_ind
54 from gmd_security_control
55 where organization_id = vOrgn_id
56 and object_type = 'F';
57
58 Cursor Cur_user_access(vOrgn_id NUMBER) IS
59 select 1, other_organization_id
60 from gmd_security_profiles
61 where organization_id = vOrgn_id
62 and assign_method_ind = 'M'
63 and access_type_ind = 'U'
64 and user_id = fnd_global.user_id;
65
66 Cursor Cur_resp_access(vOrgn_id NUMBER) IS
67 select 1, other_organization_id
68 from gmd_security_profiles
69 where organization_id = vOrgn_id
70 and assign_method_ind = 'M'
71 and access_type_ind = 'U'
72 and responsibility_id = fnd_global.resp_id;
73
74 l_user_ind VARCHAR2(1) := 'N';
75 l_resp_ind VARCHAR2(1) := 'N';
76 l_sec_cnt NUMBER(2) := -1;
77 l_other_orgn_id NUMBER(5);
78 /* Bug No.9077438 - End */
79
80 BEGIN
81 /* Define Savepoint */
82 SAVEPOINT Insert_FormulaHeader_PVT;
83
84 /* Standard Check for API compatibility */
85 IF NOT FND_API.Compatible_API_Call ( l_api_version
86 ,p_api_version
87 ,l_api_name
88 ,G_PKG_NAME )
89 THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 /* Initialize message list if p_init_msg_list is set to TRUE */
94 IF FND_API.to_Boolean( p_init_msg_list ) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 /* Initialize API return status to success */
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 /* Insert calls the mls package */
102 /* New changes to include MLS */
103 /* This package inserts into the <entity_b> and <entity_tl> table. */
104 /* <entity_vl> is a view of the <_b> and <_tl> table. */
105 /* currently we have no relationship between the */
106 /* fm_form_mst and vl table. However at a later time fm_form_mst will */
107 /* be a compatible synonym of this view. */
108
109 IF (l_debug = 'Y') THEN
110 gmd_debug.put_line(' ');
111 END IF;
112
113 /* Bug No.9077438 - Start */
114 begin
115
116 OPEN Cur_fm_sec(p_formula_header_rec.owner_organization_id);
117 FETCH Cur_fm_sec
118 INTO l_user_ind, l_resp_ind;
119 IF (Cur_fm_sec%FOUND) THEN
120 IF l_user_ind = 'Y' THEN
121 l_sec_cnt := -1;
122 l_other_orgn_id := NULL;
123 OPEN Cur_user_access(p_formula_header_rec.owner_organization_id);
124 FETCH Cur_user_access
125 INTO l_sec_cnt, l_other_orgn_id;
126 IF (Cur_user_access%FOUND) THEN
127 IF l_sec_cnt = 1 THEN
128 INSERT INTO GMD_FORMULA_SECURITY
129 (formula_security_id,
130 formula_id,
131 access_type_ind,
132 organization_id,
133 user_id,
134 responsibility_id,
135 other_organization_id,
136 created_by,
137 creation_date,
138 last_update_date,
139 last_updated_by,
140 last_update_login)
141 VALUES
142 (gmd_formula_security_id_s.NEXTVAL,
143 p_formula_header_rec.formula_id,
144 'U',
145 p_formula_header_rec.owner_organization_id,
146 p_formula_header_rec.created_by,
147 NULL,
148 l_other_orgn_id,
149 p_formula_header_rec.created_by,
150 SYSDATE,
151 SYSDATE,
152 p_formula_header_rec.created_by,
153 p_formula_header_rec.last_update_login);
154
155 END IF; --IF l_sec_cnt = 1 THEN
156 END IF; --IF (Cur_user_access%FOUND) THEN
157 CLOSE Cur_user_access;
158 END IF; --IF l_user_ind = 'Y' THEN
159 IF l_resp_ind = 'Y' THEN
160 l_sec_cnt := -1;
161 l_other_orgn_id := NULL;
162 OPEN Cur_resp_access(p_formula_header_rec.owner_organization_id);
163 FETCH Cur_resp_access
164 INTO l_sec_cnt, l_other_orgn_id;
165 IF (Cur_resp_access%FOUND) THEN
166 IF l_sec_cnt = 1 THEN
167 INSERT INTO GMD_FORMULA_SECURITY
168 (formula_security_id,
169 formula_id,
170 access_type_ind,
171 organization_id,
172 user_id,
173 responsibility_id,
174 other_organization_id,
175 created_by,
176 creation_date,
177 last_update_date,
178 last_updated_by,
179 last_update_login)
180 VALUES
181 (gmd_formula_security_id_s.NEXTVAL,
182 p_formula_header_rec.formula_id,
183 'U',
184 p_formula_header_rec.owner_organization_id,
185 NULL,
186 fnd_global.resp_id,
187 l_other_orgn_id,
188 p_formula_header_rec.created_by,
189 SYSDATE,
190 SYSDATE,
191 p_formula_header_rec.created_by,
192 p_formula_header_rec.last_update_login);
193
194 END IF; --IF l_sec_cnt = 1 THEN
195 END IF; --IF (Cur_resp_access%FOUND) THEN
196 CLOSE Cur_resp_access;
197 END IF; --IF l_resp_ind = 'Y' THEN
198 END IF; --IF (Cur_fm_sec%FOUND) THEN
199 CLOSE Cur_fm_sec;
200 EXCEPTION
201 WHEN others then
202 ROLLBACK to Insert_FormulaHeader_PVT;
203 fnd_msg_pub.add_exc_msg('GMD_FORMULA_HEADER_PVT',
204 'Insert_Formula_Header');
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
207 IF (l_debug = 'Y') THEN
208 gmd_debug.put_line(' In Formula Header Pvt - Formula Security OTHERS Exception ' ||
209 ' - ' || x_return_status);
210 END IF;
211 end;
212
213 IF (l_debug = 'Y') THEN
214 gmd_debug.put_line(' In formula header Pvt - Formula Security End');
215 END IF;
216 /* Bug No.9077438 - End */
217
218 IF (l_debug = 'Y') THEN
219 gmd_debug.put_line(' In formula header Pvt - About to insert fm id = '
220 ||p_formula_header_rec.formula_id);
221 END IF;
222
223 FM_FORM_MST_MLS.INSERT_ROW(
224 X_ROWID => formula_rowid,
225 X_FORMULA_ID => p_formula_header_rec.formula_id,
226 X_MASTER_FORMULA_ID => p_formula_header_rec.master_formula_id,
227 X_OWNER_ORGANIZATION_ID => p_formula_header_rec.owner_organization_id,
228 X_TOTAL_INPUT_QTY => p_formula_header_rec.total_input_qty,
229 X_TOTAL_OUTPUT_QTY => p_formula_header_rec.total_output_qty,
230 X_YIELD_UOM => p_formula_header_rec.yield_uom,
231 X_FORMULA_STATUS => p_formula_header_rec.formula_status,
232 X_OWNER_ID => p_formula_header_rec.owner_id,
233 X_PROJECT_ID => NULL,
234 X_TEXT_CODE => p_formula_header_rec.text_code,
235 X_DELETE_MARK => p_formula_header_rec.delete_mark,
236 X_FORMULA_NO => p_formula_header_rec.formula_no,
237 X_FORMULA_VERS => p_formula_header_rec.formula_vers,
238 X_FORMULA_TYPE => p_formula_header_rec.formula_type,
239 X_IN_USE => p_formula_header_rec.in_use,
240 X_INACTIVE_IND => p_formula_header_rec.inactive_ind,
241 X_SCALE_TYPE => p_formula_header_rec.scale_type,
242 X_FORMULA_CLASS => p_formula_header_rec.formula_class,
243 X_FMCONTROL_CLASS => p_formula_header_rec.fmcontrol_class,
244 X_ATTRIBUTE_CATEGORY => p_formula_header_rec.attribute_category,
245 X_ATTRIBUTE1 => p_formula_header_rec.attribute1,
246 X_ATTRIBUTE2 => p_formula_header_rec.attribute2,
247 X_ATTRIBUTE3 => p_formula_header_rec.attribute3,
248 X_ATTRIBUTE4 => p_formula_header_rec.attribute4,
249 X_ATTRIBUTE5 => p_formula_header_rec.attribute5,
250 X_ATTRIBUTE6 => p_formula_header_rec.attribute6,
251 X_ATTRIBUTE7 => p_formula_header_rec.attribute7,
252 X_ATTRIBUTE8 => p_formula_header_rec.attribute8,
253 X_ATTRIBUTE9 => p_formula_header_rec.attribute9,
254 X_ATTRIBUTE10 => p_formula_header_rec.attribute10,
255 X_ATTRIBUTE11 => p_formula_header_rec.attribute11,
256 X_ATTRIBUTE12 => p_formula_header_rec.attribute12,
257 X_ATTRIBUTE13 => p_formula_header_rec.attribute13,
258 X_ATTRIBUTE14 => p_formula_header_rec.attribute14,
259 X_ATTRIBUTE15 => p_formula_header_rec.attribute15,
260 X_ATTRIBUTE16 => p_formula_header_rec.attribute16,
261 X_ATTRIBUTE17 => p_formula_header_rec.attribute17,
262 X_ATTRIBUTE18 => p_formula_header_rec.attribute18,
263 X_ATTRIBUTE19 => p_formula_header_rec.attribute19,
264 X_ATTRIBUTE20 => p_formula_header_rec.attribute20,
265 X_ATTRIBUTE21 => p_formula_header_rec.attribute21,
266 X_ATTRIBUTE22 => p_formula_header_rec.attribute22,
267 X_ATTRIBUTE23 => p_formula_header_rec.attribute23,
268 X_ATTRIBUTE24 => p_formula_header_rec.attribute24,
269 X_ATTRIBUTE25 => p_formula_header_rec.attribute25,
270 X_ATTRIBUTE26 => p_formula_header_rec.attribute26,
271 X_ATTRIBUTE27 => p_formula_header_rec.attribute27,
272 X_ATTRIBUTE28 => p_formula_header_rec.attribute28,
273 X_ATTRIBUTE29 => p_formula_header_rec.attribute29,
274 X_ATTRIBUTE30 => p_formula_header_rec.attribute30,
275 X_FORMULA_DESC1 => p_formula_header_rec.formula_desc1,
276 X_FORMULA_DESC2 => p_formula_header_rec.formula_desc2,
277 X_CREATION_DATE => p_formula_header_rec.creation_date,
278 X_CREATED_BY => p_formula_header_rec.created_by,
279 X_LAST_UPDATE_DATE => p_formula_header_rec.last_update_date,
280 X_LAST_UPDATED_BY => p_formula_header_rec.last_updated_by,
281 X_LAST_UPDATE_LOGIN => p_formula_header_rec.last_update_login,
282 -- Bug# 5716318
283 X_AUTO_PRODUCT_CALC => NVL(p_formula_header_rec.auto_product_calc,'N'));
284 /* Test if formula_id is returned */
285 IF (l_debug = 'Y') THEN
286 gmd_debug.put_line(' In formula header Pvt - After fm insert row_id = '
287 ||formula_rowid);
288 END IF;
289
290 /* Check if p_commit is set to TRUE */
291 IF FND_API.To_Boolean( p_commit ) THEN
292 Commit;
293 END IF;
294
295 /* Get the message count and information */
296 FND_MSG_PUB.Count_And_Get (
297 p_count => x_msg_count,
298 p_data => x_msg_data );
299
300
301 EXCEPTION
302 WHEN FND_API.G_EXC_ERROR THEN
303 ROLLBACK to Insert_FormulaHeader_PVT;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 FND_MSG_PUB.Count_And_Get (
306 p_count => x_msg_count,
307 p_data => x_msg_data );
308
309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
310 ROLLBACK to Insert_FormulaHeader_PVT;
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 FND_MSG_PUB.Count_And_Get (
313 p_count => x_msg_count,
314 p_data => x_msg_data );
315
316 WHEN OTHERS THEN
317 ROLLBACK to Insert_FormulaHeader_PVT;
318 fnd_msg_pub.add_exc_msg ('GMD_FORMULA_HEADER_PVT', 'Insert_Formula_Header');
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 FND_MSG_PUB.Count_And_Get (
321 p_count => x_msg_count,
322 p_data => x_msg_data );
323 IF (l_debug = 'Y') THEN
324 gmd_debug.put_line(' In Formula Header Pvt - In OTHERS Exception Section '
325 ||' - '
326 ||x_return_status);
327 END IF;
328
329 END Insert_FormulaHeader;
330
331 /* ======================================================================== */
332 /* Procedure: */
333 /* Update_FormulaHeader */
334 /* */
335 /* DESCRIPTION: */
336 /* This PL/SQL procedure is responsible for updating a formula. */
337 /* ======================================================================== */
338 PROCEDURE Update_FormulaHeader
339 ( p_api_version IN NUMBER
340 ,p_init_msg_list IN VARCHAR2
341 ,p_commit IN VARCHAR2
342 ,x_return_status OUT NOCOPY VARCHAR2
343 ,x_msg_count OUT NOCOPY NUMBER
344 ,x_msg_data OUT NOCOPY VARCHAR2
345 ,p_formula_header_rec IN fm_form_mst%ROWTYPE
346 )
347 IS
348 /* Local Variables definitions */
349 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAHEADER';
350 l_api_version CONSTANT NUMBER := 1.0;
351 l_scale_type fm_form_mst.scale_type%TYPE;
352 l_formula_desc1 fm_form_mst.formula_desc1%TYPE;
353 l_return_val NUMBER := 0 ;
354
355 X_msg_cnt NUMBER;
356 X_msg_dat VARCHAR2(100);
357 X_status VARCHAR2(1);
358 l_product_qty NUMBER;
359 l_ing_qty NUMBER;
360 l_uom VARCHAR2(4);
361
362 BEGIN
363 /* Define Savepoint */
364 SAVEPOINT Update_FormulaHeader_PVT;
365
366 /* Standard Check for API compatibility */
367 IF NOT FND_API.Compatible_API_Call( l_api_version
368 ,p_api_version
369 ,l_api_name
370 ,G_PKG_NAME )
371 THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 /* Initialize message list if p_init_msg_list is set to TRUE */
376 IF FND_API.to_Boolean( p_init_msg_list ) THEN
377 FND_MSG_PUB.initialize;
378 END IF;
379
380 /* Initialize API return status to success */
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 /* Call the Update_row procedure for mls */
384 /* To modify to call the approp variables */
385 IF (l_debug = 'Y') THEN
386 gmd_debug.put_line(' In Formula Header Pvt - Before updating fm_form_mst table '
387 ||p_formula_header_rec.formula_id);
388 END IF;
389 FM_FORM_MST_MLS.UPDATE_ROW(
390 X_FORMULA_ID => p_formula_header_rec.formula_id,
391 X_OWNER_ORGANIZATION_ID => p_formula_header_rec.owner_organization_id,
392 X_TEXT_CODE => p_formula_header_rec.text_code,
393 X_DELETE_MARK => p_formula_header_rec.delete_mark,
394 X_TOTAL_INPUT_QTY => p_formula_header_rec.total_input_qty,
395 X_PROJECT_ID => null,
396 X_TOTAL_OUTPUT_QTY => p_formula_header_rec.total_output_qty,
397 X_YIELD_UOM => p_formula_header_rec.yield_uom,
398 X_FORMULA_STATUS => p_formula_header_rec.formula_status,
399 X_OWNER_ID => p_formula_header_rec.owner_id,
400 X_FORMULA_NO => p_formula_header_rec.formula_no,
401 X_FORMULA_VERS => p_formula_header_rec.formula_vers,
402 X_FORMULA_TYPE => p_formula_header_rec.formula_type,
403 X_IN_USE => p_formula_header_rec.in_use,
404 X_INACTIVE_IND => p_formula_header_rec.inactive_ind,
405 X_SCALE_TYPE => p_formula_header_rec.scale_type,
406 X_FORMULA_CLASS => p_formula_header_rec.formula_class,
407 X_FMCONTROL_CLASS => p_formula_header_rec.fmcontrol_class,
408 X_ATTRIBUTE_CATEGORY => p_formula_header_rec.attribute_category,
409 X_ATTRIBUTE1 => p_formula_header_rec.attribute1,
410 X_ATTRIBUTE2 => p_formula_header_rec.attribute2,
411 X_ATTRIBUTE3 => p_formula_header_rec.attribute3,
412 X_ATTRIBUTE4 => p_formula_header_rec.attribute4,
413 X_ATTRIBUTE5 => p_formula_header_rec.attribute5,
414 X_ATTRIBUTE6 => p_formula_header_rec.attribute6,
415 X_ATTRIBUTE7 => p_formula_header_rec.attribute7,
416 X_ATTRIBUTE8 => p_formula_header_rec.attribute8,
417 X_ATTRIBUTE9 => p_formula_header_rec.attribute9,
418 X_ATTRIBUTE10 => p_formula_header_rec.attribute10,
419 X_ATTRIBUTE11 => p_formula_header_rec.attribute11,
420 X_ATTRIBUTE12 => p_formula_header_rec.attribute12,
421 X_ATTRIBUTE13 => p_formula_header_rec.attribute13,
422 X_ATTRIBUTE14 => p_formula_header_rec.attribute14,
423 X_ATTRIBUTE15 => p_formula_header_rec.attribute15,
424 X_ATTRIBUTE16 => p_formula_header_rec.attribute16,
425 X_ATTRIBUTE17 => p_formula_header_rec.attribute17,
426 X_ATTRIBUTE18 => p_formula_header_rec.attribute18,
427 X_ATTRIBUTE19 => p_formula_header_rec.attribute19,
428 X_ATTRIBUTE20 => p_formula_header_rec.attribute20,
429 X_ATTRIBUTE21 => p_formula_header_rec.attribute21,
430 X_ATTRIBUTE22 => p_formula_header_rec.attribute22,
431 X_ATTRIBUTE23 => p_formula_header_rec.attribute23,
432 X_ATTRIBUTE24 => p_formula_header_rec.attribute24,
433 X_ATTRIBUTE25 => p_formula_header_rec.attribute25,
434 X_ATTRIBUTE26 => p_formula_header_rec.attribute26,
435 X_ATTRIBUTE27 => p_formula_header_rec.attribute27,
436 X_ATTRIBUTE28 => p_formula_header_rec.attribute28,
437 X_ATTRIBUTE29 => p_formula_header_rec.attribute29,
438 X_ATTRIBUTE30 => p_formula_header_rec.attribute30,
439 X_FORMULA_DESC1 => p_formula_header_rec.formula_desc1,
440 X_FORMULA_DESC2 => p_formula_header_rec.formula_desc2,
441 X_LAST_UPDATE_DATE => p_formula_header_rec.last_update_date,
442 X_LAST_UPDATED_BY => p_formula_header_rec.last_updated_by,
443 X_LAST_UPDATE_LOGIN => p_formula_header_rec.last_update_login,
444 -- Bug# 5716318
445 X_AUTO_PRODUCT_CALC => p_formula_header_rec.auto_product_calc);
446
447 /* End API body */
448 IF (l_debug = 'Y') THEN
449 gmd_debug.put_line(' In Formula Header Pvt - After updating fm_form_mst table ');
450 END IF;
451
452 /* Check if p_commit is set to TRUE */
453 IF FND_API.To_Boolean( p_commit ) THEN
454 Commit;
455 END IF;
456
457 /* Get the message count and information */
458 FND_MSG_PUB.Count_And_Get (
459 p_count => x_msg_count,
460 p_data => x_msg_data );
461
462 EXCEPTION
463 WHEN FND_API.G_EXC_ERROR THEN
464 ROLLBACK to Update_FormulaHeader_PVT;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 FND_MSG_PUB.Count_And_Get (
467 p_count => x_msg_count,
468 p_data => x_msg_data );
469 IF (l_debug = 'Y') THEN
470 gmd_debug.put_line(' In Formula Header Pvt - In Error Exception Section '
471 ||' - '
472 ||x_return_status);
473 END IF;
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
475 ROLLBACK to Update_FormulaHeader_PVT;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 FND_MSG_PUB.Count_And_Get (
478 p_count => x_msg_count,
479 p_data => x_msg_data );
480 IF (l_debug = 'Y') THEN
481 gmd_debug.put_line(' In Formula Header Pvt - In Unexpected Exception Section '
482 ||' - '
483 ||x_return_status);
484 END IF;
485
486 WHEN OTHERS THEN
487 ROLLBACK to Update_FormulaHeader_PVT;
488 fnd_msg_pub.add_exc_msg ('GMD_FORMULA_HEADER_PVT', 'Update_Formula_Header');
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
490 FND_MSG_PUB.Count_And_Get (
491 p_count => x_msg_count,
492 p_data => x_msg_data );
493 IF (l_debug = 'Y') THEN
494 gmd_debug.put_line(' In Formula Header Pvt - In Others Exception Section '
495 ||' - '
496 ||x_return_status);
497 END IF;
498
499 END Update_FormulaHeader;
500
501 END GMD_FORMULA_HEADER_PVT;