[Home] [Help]
PACKAGE BODY: APPS.CS_COST_DETAILS_PKG
Source
1 PACKAGE BODY CS_COST_DETAILS_PKG AS
2 /* $Header: csxcstb.pls 120.2 2008/01/18 07:01:10 bkanimoz noship $ */
3
4 L_API_NAME_FULL CONSTANT VARCHAR2(255):='CS_COST_DETAILS_PKG';
5 L_LOG_MODULE CONSTANT VARCHAR2(255):='csxcstb.pls'|| L_API_NAME_FULL ||'.';
6
7
8 /*====================================
9 Procedure Insert Row
10 ======================================
11 */
12 PROCEDURE Insert_Row
13 (
14
15 p_incident_id NUMBER,
16 p_estimate_detail_id NUMBER,
17 p_transaction_type_id NUMBER,
18 p_txn_billing_type_id NUMBER,
19 p_inventory_item_id NUMBER,
20 p_quantity NUMBER,
21 p_unit_cost NUMBER,
22 p_extended_cost NUMBER,
23 p_override_ext_cost_flag VARCHAR2,
24 p_transaction_date DATE,
25 p_source_id NUMBER,
26 p_source_code VARCHAR2,
27 p_unit_of_measure_code VARCHAR2,
28 p_currency_code VARCHAR2,
29 p_org_id NUMBER,
30 p_inventory_org_id NUMBER,
31 p_attribute1 VARCHAR2,
32 p_attribute2 VARCHAR2,
33 p_attribute3 VARCHAR2,
34 p_attribute4 VARCHAR2,
35 p_attribute5 VARCHAR2,
36 p_attribute6 VARCHAR2,
37 p_attribute7 VARCHAR2,
38 p_attribute8 VARCHAR2,
39 p_attribute9 VARCHAR2,
40 p_attribute10 VARCHAR2,
41 p_attribute11 VARCHAR2,
42 p_attribute12 VARCHAR2,
43 p_attribute13 VARCHAR2,
44 p_attribute14 VARCHAR2,
45 p_attribute15 VARCHAR2,
46 p_last_update_date DATE,
47 p_last_updated_by NUMBER,
48 p_last_update_login NUMBER,
49 p_created_by NUMBER,
50 p_creation_date DATE,
51 x_object_version_number OUT NOCOPY NUMBER,
52 x_cost_id OUT NOCOPY NUMBER
53 ) IS
54
55 l_cost_id NUMBER;
56
57 BEGIN
58
59
60
61 --get the cost_id from the sequence
62 SELECT cs_cost_details_s.nextval
63 INTO l_cost_id
64 FROM DUAL;
65
66
67 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
68 FND_LOG.String
69 (FND_LOG.level_procedure ,
70 L_LOG_MODULE ||'',
71 'Before Inserting into the cost table. l_cost_id: '||l_cost_id
72 );
73 END IF;
74
75
76 --insert the data into cost table
77 INSERT
78 INTO cs_cost_details
79 (
80 cost_id ,
81 incident_id ,
82 estimate_detail_id ,
83 transaction_type_id ,
84 txn_billing_type_id ,
85 inventory_item_id ,
86 quantity ,
87 unit_cost ,
88 extended_cost ,
89 override_ext_cost_flag ,
90 transaction_date ,
91 source_id ,
92 source_code ,
93 unit_of_measure_code ,
94 currency_code ,
95 org_id ,
96 inventory_org_id ,
97 attribute1 ,
98 attribute2 ,
99 attribute3 ,
100 attribute4 ,
101 attribute5 ,
102 attribute6 ,
103 attribute7 ,
104 attribute8 ,
105 attribute9 ,
106 attribute10 ,
107 attribute11 ,
108 attribute12 ,
109 attribute13 ,
110 attribute14 ,
111 attribute15 ,
112 last_update_date ,
113 last_updated_by ,
114 last_update_login ,
115 created_by ,
116 creation_date ,
117 object_version_number
118 )
119 VALUES
120 (
121 l_cost_id ,
122 p_incident_id ,
123 p_estimate_detail_id ,
124 p_transaction_type_id ,
125 p_txn_billing_type_id ,
126 p_inventory_item_id ,
127 p_quantity ,
128 p_unit_cost ,
129 p_extended_cost ,
130 p_override_ext_cost_flag ,
131 p_transaction_date ,
132 p_source_id ,
133 p_source_code ,
134 p_unit_of_measure_code ,
135 p_currency_code ,
136 p_org_id ,
137 p_inventory_org_id ,
138 p_attribute1 ,
139 p_attribute2 ,
140 p_attribute3 ,
141 p_attribute4 ,
142 p_attribute5 ,
143 p_attribute6 ,
144 p_attribute7 ,
145 p_attribute8 ,
146 p_attribute9 ,
147 p_attribute10 ,
148 p_attribute11 ,
149 p_attribute12 ,
150 p_attribute13 ,
151 p_attribute14 ,
152 p_attribute15 ,
153 p_last_update_date ,
154 p_last_updated_by ,
155 p_last_update_login ,
156 p_created_by ,
157 p_creation_date ,
158 1
159 )
160 ;
161
162 --assign the values to the out variables
163 X_OBJECT_VERSION_NUMBER :=1;
164
165 x_cost_id := l_Cost_id;
166
167 --commit the work
168 COMMIT;
169
170
171 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
172 FND_LOG.String
173 (FND_LOG.level_procedure ,
174 L_LOG_MODULE ||'',
175 'After Inserting into the cost table. l_cost_id: '||l_cost_id
176 );
177 END IF;
178
179
180 END Insert_Row;
181
182
183
184 /*=========================================
185 Procedure Update Row
186 ===========================================
187 */
188
189 PROCEDURE Update_Row
190 (
191
192 p_cost_id NUMBER,
193 p_incident_id NUMBER,
194 p_estimate_detail_id NUMBER,
195 p_transaction_type_id NUMBER,
196 p_txn_billing_type_id NUMBER,
197 p_inventory_item_id NUMBER,
198 p_quantity NUMBER,
199 p_unit_cost NUMBER,
200 p_extended_cost NUMBER,
201 p_override_ext_cost_flag VARCHAR2,
202 p_transaction_date DATE,
203 p_source_id NUMBER,
204 p_source_code VARCHAR2,
205 p_unit_of_measure_code VARCHAR2,
206 p_currency_code VARCHAR2,
207 p_org_id NUMBER,
208 p_inventory_org_id NUMBER,
209 p_attribute1 VARCHAR2,
210 p_attribute2 VARCHAR2,
211 p_attribute3 VARCHAR2,
212 p_attribute4 VARCHAR2,
213 p_attribute5 VARCHAR2,
214 p_attribute6 VARCHAR2,
215 p_attribute7 VARCHAR2,
216 p_attribute8 VARCHAR2,
217 p_attribute9 VARCHAR2,
218 p_attribute10 VARCHAR2,
219 p_attribute11 VARCHAR2,
220 p_attribute12 VARCHAR2,
221 p_attribute13 VARCHAR2,
222 p_attribute14 VARCHAR2,
223 p_attribute15 VARCHAR2,
224 p_last_update_date DATE,
225 p_last_updated_by NUMBER,
226 p_last_update_login NUMBER,
227 p_created_by NUMBER,
228 p_creation_date DATE,
229 x_object_version_number IN OUT NOCOPY NUMBER
230
231 ) IS
232
233 CURSOR C2 IS
234 SELECT OBJECT_VERSION_NUMBER
235 FROM CS_cost_details
236 WHERE cost_id = p_cost_id;
237
238 l_cost_id NUMBER;
239
240 CURSOR get_cost_id IS
241 SELECT cost_id
242 FROM cs_cost_details
243 WHERE estimate_detail_id =p_estimate_detail_id;
244
245
246 BEGIN
247 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
248 FND_LOG.String
249 (FND_LOG.level_procedure ,
250 L_LOG_MODULE ||'',
251 'Before Updating the cost table.p_cost_id: '||p_cost_id
252 );
253 END IF;
254
255 OPEN C2;
256 FETCH C2
257 INTO X_OBJECT_VERSION_NUMBER;
258 CLOSE C2;
259
260 l_cost_id := p_cost_id;
261
262 IF p_estimate_detail_id IS NOT NULL THEN
263
264 OPEN get_cost_id;
265 FETCH get_cost_id
266 INTO l_cost_id;
267 CLOSE get_cost_id;
268
269 END IF;
270
271
272
273 UPDATE cs_cost_details
274 SET cost_id = decode(l_cost_id,FND_API.G_MISS_NUM,cost_id,l_cost_id),
275 incident_id = decode(p_incident_id,FND_API.G_MISS_NUM,incident_id,p_incident_id),
276 estimate_detail_id = decode(p_estimate_detail_id,FND_API.G_MISS_NUM,estimate_detail_id,p_estimate_detail_id),
277 transaction_type_id = decode(p_transaction_type_id,FND_API.G_MISS_NUM ,transaction_type_id,p_transaction_type_id),
278 txn_billing_type_id = decode(p_txn_billing_type_id,FND_API.G_MISS_NUM ,txn_billing_type_id ,p_txn_billing_type_id),
279 inventory_item_id = decode(p_inventory_item_id,FND_API.G_MISS_NUM ,inventory_item_id,p_inventory_item_id),
280 quantity = decode(p_quantity,FND_API.G_MISS_NUM,quantity,p_quantity),
281 unit_cost = decode(p_unit_cost,FND_API.G_MISS_NUM ,unit_cost,p_unit_cost),
282 extended_cost = decode(p_extended_cost ,FND_API.G_MISS_NUM ,extended_cost,p_extended_cost),
283 override_ext_cost_flag = decode(p_override_ext_cost_flag,FND_API.G_MISS_CHAR,override_ext_cost_flag,p_override_ext_cost_flag),
284 transaction_date = decode(p_transaction_date, FND_API.G_MISS_DATE,transaction_date,p_transaction_date),
285 source_id = decode(p_source_id,FND_API.G_MISS_NUM,source_id,p_source_id),
286 source_code = decode(p_source_code,FND_API.G_MISS_CHAR,source_code,p_source_code),
287 unit_of_measure_code= decode(p_unit_of_measure_code ,FND_API.G_MISS_CHAR,unit_of_measure_code,p_unit_of_measure_code),
288 currency_code = decode(p_currency_code,FND_API.G_MISS_CHAR,currency_code,p_currency_code),
289 org_id = decode(p_org_id,FND_API.G_MISS_NUM,org_id,p_org_id),
290 inventory_org_id = decode(p_inventory_org_id,FND_API.G_MISS_NUM,inventory_org_id,p_inventory_org_id),
291 attribute1 = decode(p_attribute1 , FND_API.G_MISS_CHAR ,attribute1,p_attribute1),
292 attribute2 = decode(p_attribute2 , FND_API.G_MISS_CHAR ,attribute2,p_attribute2),
293 attribute3 = decode(p_attribute3 , FND_API.G_MISS_CHAR ,attribute3,p_attribute3),
294 attribute4 = decode(p_attribute4 , FND_API.G_MISS_CHAR ,attribute4,p_attribute4),
295 attribute5 = decode(p_attribute5 , FND_API.G_MISS_CHAR ,attribute5,p_attribute5),
296 attribute6 = decode(p_attribute6 , FND_API.G_MISS_CHAR ,attribute6,p_attribute6),
297 attribute7 = decode(p_attribute7 , FND_API.G_MISS_CHAR ,attribute7,p_attribute7),
298 attribute8 = decode(p_attribute8 , FND_API.G_MISS_CHAR ,attribute8,p_attribute8),
299 attribute9 = decode(p_attribute9 , FND_API.G_MISS_CHAR ,attribute9,p_attribute9),
300 attribute10 = decode(p_attribute10 , FND_API.G_MISS_CHAR ,attribute10,p_attribute10),
301 attribute11 = decode(p_attribute11 , FND_API.G_MISS_CHAR ,attribute11,p_attribute11),
302 attribute12 = decode(p_attribute12 , FND_API.G_MISS_CHAR ,attribute12,p_attribute12),
303 attribute13 = decode(p_attribute13 , FND_API.G_MISS_CHAR ,attribute13,p_attribute13),
304 attribute14 = decode(p_attribute14 , FND_API.G_MISS_CHAR ,attribute14,p_attribute14),
305 attribute15 = decode(p_attribute15 , FND_API.G_MISS_CHAR ,attribute15,p_attribute15),
306 last_update_date = decode(p_last_update_date, FND_API.G_MISS_CHAR ,last_update_date,p_last_update_date),
307 last_updated_by = decode(p_last_updated_by , FND_API.G_MISS_CHAR,last_updated_by,p_last_updated_by),
308 last_update_login = decode(p_last_update_login, FND_API.G_MISS_CHAR,last_update_login,p_last_update_login),
309 --created_by = decode(p_created_by , FND_API.G_MISS_CHAR,created_by,p_created_by),
310 --creation_date = decode(p_creation_date , FND_API.G_MISS_DATE ,creation_date,p_creation_date),
311 object_version_number = X_OBJECT_VERSION_NUMBER+1
312 WHERE cost_id =l_cost_id;
313
314 IF (SQL%NOTFOUND) THEN
315 RAISE NO_DATA_FOUND;
316 END IF;
317
318 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER+1;
319
320 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
321 FND_LOG.String
322 (FND_LOG.level_procedure ,
323 L_LOG_MODULE ||'',
324 'After Updating the cost table. x_object_version_number: '||x_object_version_number
325 ) ;
326 END IF;
327
328
329 END Update_Row;
330
331
332 /*=========================================
333 Procedure Delete Row
334 ===========================================
335 */
336
340 ) IS
337 PROCEDURE Delete_Row
338 (
339 p_cost_id NUMBER
341
342 BEGIN
343
344 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
345 FND_LOG.String
346 (FND_LOG.level_procedure ,
347 L_LOG_MODULE ||'',
348 'Before Deleting the cost record . p_cost_id '||p_cost_id
349 );
350 END IF;
351
352 DELETE FROM cs_cost_details
353 WHERE cost_id=p_cost_id;
354
355 IF (SQL%NOTFOUND) THEN
356 RAISE NO_DATA_FOUND;
357 END IF;
358
359 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
360 FND_LOG.String
361 (FND_LOG.level_procedure ,
362 L_LOG_MODULE ||'',
363 'After Deleting the cost record . p_cost_id : '||p_cost_id
364 );
365 END IF;
366
367 END Delete_Row;
368
369
370 END CS_COST_DETAILS_PKG;
371