[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_HEADER_PVT
Source
1 PACKAGE BODY GMD_FORMULA_HEADER_PVT AS
2 /* $Header: GMDVFMHB.pls 120.2.12000000.2 2007/02/09 12:35:43 kmotupal 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 BEGIN
52 /* Define Savepoint */
53 SAVEPOINT Insert_FormulaHeader_PVT;
54
55 /* Standard Check for API compatibility */
56 IF NOT FND_API.Compatible_API_Call ( l_api_version
57 ,p_api_version
58 ,l_api_name
59 ,G_PKG_NAME )
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63
64 /* Initialize message list if p_init_msg_list is set to TRUE */
65 IF FND_API.to_Boolean( p_init_msg_list ) THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68
69 /* Initialize API return status to success */
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 /* Insert calls the mls package */
73 /* New changes to include MLS */
74 /* This package inserts into the <entity_b> and <entity_tl> table. */
75 /* <entity_vl> is a view of the <_b> and <_tl> table. */
76 /* currently we have no relationship between the */
77 /* fm_form_mst and vl table. However at a later time fm_form_mst will */
78 /* be a compatible synonym of this view. */
79
80 IF (l_debug = 'Y') THEN
81 gmd_debug.put_line(' ');
82 END IF;
83
84 IF (l_debug = 'Y') THEN
85 gmd_debug.put_line(' In formula header Pvt - About to insert fm id = '
86 ||p_formula_header_rec.formula_id);
87 END IF;
88
89 FM_FORM_MST_MLS.INSERT_ROW(
90 X_ROWID => formula_rowid,
91 X_FORMULA_ID => p_formula_header_rec.formula_id,
92 X_MASTER_FORMULA_ID => p_formula_header_rec.master_formula_id,
93 X_OWNER_ORGANIZATION_ID => p_formula_header_rec.owner_organization_id,
94 X_TOTAL_INPUT_QTY => p_formula_header_rec.total_input_qty,
95 X_TOTAL_OUTPUT_QTY => p_formula_header_rec.total_output_qty,
96 X_YIELD_UOM => p_formula_header_rec.yield_uom,
97 X_FORMULA_STATUS => p_formula_header_rec.formula_status,
98 X_OWNER_ID => p_formula_header_rec.owner_id,
99 X_PROJECT_ID => NULL,
100 X_TEXT_CODE => p_formula_header_rec.text_code,
101 X_DELETE_MARK => p_formula_header_rec.delete_mark,
102 X_FORMULA_NO => p_formula_header_rec.formula_no,
103 X_FORMULA_VERS => p_formula_header_rec.formula_vers,
104 X_FORMULA_TYPE => p_formula_header_rec.formula_type,
105 X_IN_USE => p_formula_header_rec.in_use,
106 X_INACTIVE_IND => p_formula_header_rec.inactive_ind,
107 X_SCALE_TYPE => p_formula_header_rec.scale_type,
108 X_FORMULA_CLASS => p_formula_header_rec.formula_class,
109 X_FMCONTROL_CLASS => p_formula_header_rec.fmcontrol_class,
110 X_ATTRIBUTE_CATEGORY => p_formula_header_rec.attribute_category,
111 X_ATTRIBUTE1 => p_formula_header_rec.attribute1,
112 X_ATTRIBUTE2 => p_formula_header_rec.attribute2,
113 X_ATTRIBUTE3 => p_formula_header_rec.attribute3,
114 X_ATTRIBUTE4 => p_formula_header_rec.attribute4,
115 X_ATTRIBUTE5 => p_formula_header_rec.attribute5,
116 X_ATTRIBUTE6 => p_formula_header_rec.attribute6,
117 X_ATTRIBUTE7 => p_formula_header_rec.attribute7,
118 X_ATTRIBUTE8 => p_formula_header_rec.attribute8,
119 X_ATTRIBUTE9 => p_formula_header_rec.attribute9,
120 X_ATTRIBUTE10 => p_formula_header_rec.attribute10,
121 X_ATTRIBUTE11 => p_formula_header_rec.attribute11,
122 X_ATTRIBUTE12 => p_formula_header_rec.attribute12,
123 X_ATTRIBUTE13 => p_formula_header_rec.attribute13,
124 X_ATTRIBUTE14 => p_formula_header_rec.attribute14,
125 X_ATTRIBUTE15 => p_formula_header_rec.attribute15,
126 X_ATTRIBUTE16 => p_formula_header_rec.attribute16,
127 X_ATTRIBUTE17 => p_formula_header_rec.attribute17,
128 X_ATTRIBUTE18 => p_formula_header_rec.attribute18,
129 X_ATTRIBUTE19 => p_formula_header_rec.attribute19,
130 X_ATTRIBUTE20 => p_formula_header_rec.attribute20,
131 X_ATTRIBUTE21 => p_formula_header_rec.attribute21,
132 X_ATTRIBUTE22 => p_formula_header_rec.attribute22,
133 X_ATTRIBUTE23 => p_formula_header_rec.attribute23,
134 X_ATTRIBUTE24 => p_formula_header_rec.attribute24,
135 X_ATTRIBUTE25 => p_formula_header_rec.attribute25,
136 X_ATTRIBUTE26 => p_formula_header_rec.attribute26,
137 X_ATTRIBUTE27 => p_formula_header_rec.attribute27,
138 X_ATTRIBUTE28 => p_formula_header_rec.attribute28,
139 X_ATTRIBUTE29 => p_formula_header_rec.attribute29,
140 X_ATTRIBUTE30 => p_formula_header_rec.attribute30,
141 X_FORMULA_DESC1 => p_formula_header_rec.formula_desc1,
142 X_FORMULA_DESC2 => p_formula_header_rec.formula_desc2,
143 X_CREATION_DATE => p_formula_header_rec.creation_date,
144 X_CREATED_BY => p_formula_header_rec.created_by,
145 X_LAST_UPDATE_DATE => p_formula_header_rec.last_update_date,
146 X_LAST_UPDATED_BY => p_formula_header_rec.last_updated_by,
147 X_LAST_UPDATE_LOGIN => p_formula_header_rec.last_update_login,
148 -- Bug# 5716318
149 X_AUTO_PRODUCT_CALC => NVL(p_formula_header_rec.auto_product_calc,'N'));
150 /* Test if formula_id is returned */
151 IF (l_debug = 'Y') THEN
152 gmd_debug.put_line(' In formula header Pvt - After fm insert row_id = '
153 ||formula_rowid);
154 END IF;
155
156 /* Check if p_commit is set to TRUE */
157 IF FND_API.To_Boolean( p_commit ) THEN
158 Commit;
159 END IF;
160
161 /* Get the message count and information */
162 FND_MSG_PUB.Count_And_Get (
163 p_count => x_msg_count,
164 p_data => x_msg_data );
165
166
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 ROLLBACK to Insert_FormulaHeader_PVT;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 FND_MSG_PUB.Count_And_Get (
172 p_count => x_msg_count,
173 p_data => x_msg_data );
174
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 ROLLBACK to Insert_FormulaHeader_PVT;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 FND_MSG_PUB.Count_And_Get (
179 p_count => x_msg_count,
180 p_data => x_msg_data );
181
182 WHEN OTHERS THEN
183 ROLLBACK to Insert_FormulaHeader_PVT;
184 fnd_msg_pub.add_exc_msg ('GMD_FORMULA_HEADER_PVT', 'Insert_Formula_Header');
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 FND_MSG_PUB.Count_And_Get (
187 p_count => x_msg_count,
188 p_data => x_msg_data );
189 IF (l_debug = 'Y') THEN
190 gmd_debug.put_line(' In Formula Header Pvt - In OTHERS Exception Section '
191 ||' - '
192 ||x_return_status);
193 END IF;
194
195 END Insert_FormulaHeader;
196
197 /* ======================================================================== */
198 /* Procedure: */
199 /* Update_FormulaHeader */
200 /* */
201 /* DESCRIPTION: */
202 /* This PL/SQL procedure is responsible for updating a formula. */
203 /* ======================================================================== */
204 PROCEDURE Update_FormulaHeader
205 ( p_api_version IN NUMBER
206 ,p_init_msg_list IN VARCHAR2
207 ,p_commit IN VARCHAR2
208 ,x_return_status OUT NOCOPY VARCHAR2
209 ,x_msg_count OUT NOCOPY NUMBER
210 ,x_msg_data OUT NOCOPY VARCHAR2
211 ,p_formula_header_rec IN fm_form_mst%ROWTYPE
212 )
213 IS
214 /* Local Variables definitions */
215 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAHEADER';
216 l_api_version CONSTANT NUMBER := 1.0;
217 l_scale_type fm_form_mst.scale_type%TYPE;
218 l_formula_desc1 fm_form_mst.formula_desc1%TYPE;
219 l_return_val NUMBER := 0 ;
220
221 X_msg_cnt NUMBER;
222 X_msg_dat VARCHAR2(100);
223 X_status VARCHAR2(1);
224 l_product_qty NUMBER;
225 l_ing_qty NUMBER;
226 l_uom VARCHAR2(4);
227
228 BEGIN
229 /* Define Savepoint */
230 SAVEPOINT Update_FormulaHeader_PVT;
231
232 /* Standard Check for API compatibility */
233 IF NOT FND_API.Compatible_API_Call( l_api_version
234 ,p_api_version
235 ,l_api_name
236 ,G_PKG_NAME )
237 THEN
238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239 END IF;
240
241 /* Initialize message list if p_init_msg_list is set to TRUE */
242 IF FND_API.to_Boolean( p_init_msg_list ) THEN
243 FND_MSG_PUB.initialize;
244 END IF;
245
246 /* Initialize API return status to success */
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248
249 /* Call the Update_row procedure for mls */
250 /* To modify to call the approp variables */
251 IF (l_debug = 'Y') THEN
252 gmd_debug.put_line(' In Formula Header Pvt - Before updating fm_form_mst table '
253 ||p_formula_header_rec.formula_id);
254 END IF;
255 FM_FORM_MST_MLS.UPDATE_ROW(
256 X_FORMULA_ID => p_formula_header_rec.formula_id,
257 X_OWNER_ORGANIZATION_ID => p_formula_header_rec.owner_organization_id,
258 X_TEXT_CODE => p_formula_header_rec.text_code,
259 X_DELETE_MARK => p_formula_header_rec.delete_mark,
260 X_TOTAL_INPUT_QTY => p_formula_header_rec.total_input_qty,
261 X_PROJECT_ID => null,
262 X_TOTAL_OUTPUT_QTY => p_formula_header_rec.total_output_qty,
263 X_YIELD_UOM => p_formula_header_rec.yield_uom,
264 X_FORMULA_STATUS => p_formula_header_rec.formula_status,
265 X_OWNER_ID => p_formula_header_rec.owner_id,
266 X_FORMULA_NO => p_formula_header_rec.formula_no,
267 X_FORMULA_VERS => p_formula_header_rec.formula_vers,
268 X_FORMULA_TYPE => p_formula_header_rec.formula_type,
269 X_IN_USE => p_formula_header_rec.in_use,
270 X_INACTIVE_IND => p_formula_header_rec.inactive_ind,
271 X_SCALE_TYPE => p_formula_header_rec.scale_type,
272 X_FORMULA_CLASS => p_formula_header_rec.formula_class,
273 X_FMCONTROL_CLASS => p_formula_header_rec.fmcontrol_class,
274 X_ATTRIBUTE_CATEGORY => p_formula_header_rec.attribute_category,
275 X_ATTRIBUTE1 => p_formula_header_rec.attribute1,
276 X_ATTRIBUTE2 => p_formula_header_rec.attribute2,
277 X_ATTRIBUTE3 => p_formula_header_rec.attribute3,
278 X_ATTRIBUTE4 => p_formula_header_rec.attribute4,
279 X_ATTRIBUTE5 => p_formula_header_rec.attribute5,
280 X_ATTRIBUTE6 => p_formula_header_rec.attribute6,
281 X_ATTRIBUTE7 => p_formula_header_rec.attribute7,
282 X_ATTRIBUTE8 => p_formula_header_rec.attribute8,
283 X_ATTRIBUTE9 => p_formula_header_rec.attribute9,
284 X_ATTRIBUTE10 => p_formula_header_rec.attribute10,
285 X_ATTRIBUTE11 => p_formula_header_rec.attribute11,
286 X_ATTRIBUTE12 => p_formula_header_rec.attribute12,
287 X_ATTRIBUTE13 => p_formula_header_rec.attribute13,
288 X_ATTRIBUTE14 => p_formula_header_rec.attribute14,
289 X_ATTRIBUTE15 => p_formula_header_rec.attribute15,
290 X_ATTRIBUTE16 => p_formula_header_rec.attribute16,
291 X_ATTRIBUTE17 => p_formula_header_rec.attribute17,
292 X_ATTRIBUTE18 => p_formula_header_rec.attribute18,
293 X_ATTRIBUTE19 => p_formula_header_rec.attribute19,
294 X_ATTRIBUTE20 => p_formula_header_rec.attribute20,
295 X_ATTRIBUTE21 => p_formula_header_rec.attribute21,
296 X_ATTRIBUTE22 => p_formula_header_rec.attribute22,
297 X_ATTRIBUTE23 => p_formula_header_rec.attribute23,
298 X_ATTRIBUTE24 => p_formula_header_rec.attribute24,
299 X_ATTRIBUTE25 => p_formula_header_rec.attribute25,
300 X_ATTRIBUTE26 => p_formula_header_rec.attribute26,
301 X_ATTRIBUTE27 => p_formula_header_rec.attribute27,
302 X_ATTRIBUTE28 => p_formula_header_rec.attribute28,
303 X_ATTRIBUTE29 => p_formula_header_rec.attribute29,
304 X_ATTRIBUTE30 => p_formula_header_rec.attribute30,
305 X_FORMULA_DESC1 => p_formula_header_rec.formula_desc1,
306 X_FORMULA_DESC2 => p_formula_header_rec.formula_desc2,
307 X_LAST_UPDATE_DATE => p_formula_header_rec.last_update_date,
308 X_LAST_UPDATED_BY => p_formula_header_rec.last_updated_by,
309 X_LAST_UPDATE_LOGIN => p_formula_header_rec.last_update_login,
310 -- Bug# 5716318
311 X_AUTO_PRODUCT_CALC => p_formula_header_rec.auto_product_calc);
312
313 /* End API body */
314 IF (l_debug = 'Y') THEN
315 gmd_debug.put_line(' In Formula Header Pvt - After updating fm_form_mst table ');
316 END IF;
317
318 /* Check if p_commit is set to TRUE */
319 IF FND_API.To_Boolean( p_commit ) THEN
320 Commit;
321 END IF;
322
323 /* Get the message count and information */
324 FND_MSG_PUB.Count_And_Get (
325 p_count => x_msg_count,
326 p_data => x_msg_data );
327
328 EXCEPTION
329 WHEN FND_API.G_EXC_ERROR THEN
330 ROLLBACK to Update_FormulaHeader_PVT;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 FND_MSG_PUB.Count_And_Get (
333 p_count => x_msg_count,
334 p_data => x_msg_data );
335 IF (l_debug = 'Y') THEN
336 gmd_debug.put_line(' In Formula Header Pvt - In Error Exception Section '
337 ||' - '
338 ||x_return_status);
339 END IF;
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 ROLLBACK to Update_FormulaHeader_PVT;
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343 FND_MSG_PUB.Count_And_Get (
344 p_count => x_msg_count,
345 p_data => x_msg_data );
346 IF (l_debug = 'Y') THEN
347 gmd_debug.put_line(' In Formula Header Pvt - In Unexpected Exception Section '
348 ||' - '
349 ||x_return_status);
350 END IF;
351
352 WHEN OTHERS THEN
353 ROLLBACK to Update_FormulaHeader_PVT;
354 fnd_msg_pub.add_exc_msg ('GMD_FORMULA_HEADER_PVT', 'Update_Formula_Header');
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 FND_MSG_PUB.Count_And_Get (
357 p_count => x_msg_count,
358 p_data => x_msg_data );
359 IF (l_debug = 'Y') THEN
360 gmd_debug.put_line(' In Formula Header Pvt - In Others Exception Section '
361 ||' - '
362 ||x_return_status);
363 END IF;
364
365 END Update_FormulaHeader;
366
367 END GMD_FORMULA_HEADER_PVT;