1 PACKAGE BODY AHL_WARRANTY_ENTITLEMENTS_PKG AS
2 /* $Header: AHLLENTB.pls 120.0.12020000.2 2012/12/06 22:30:45 sareepar ship $ */
3
4 PROCEDURE Insert_Row (
5 x_row_id IN OUT NOCOPY VARCHAR2,
6 x_warranty_entitlement_id IN NUMBER,
7 x_object_version_number IN NUMBER,
8 x_warranty_contract_id IN NUMBER,
9 x_old_warranty_contract_id IN NUMBER,
10 x_entitlement_status_code IN VARCHAR2,
11 x_warranty_claim_id IN NUMBER,
12 x_po_header_id IN NUMBER,
13 x_sr_incident_id IN NUMBER,
14 x_visit_task_id IN NUMBER,
15 x_workorder_id IN NUMBER,
16 x_osp_order_line_id IN NUMBER,
17 x_warranty_labour_capture IN VARCHAR2,
18 x_warranty_mtl_capture IN VARCHAR2,
19 x_order_claim_amount IN NUMBER,
20 x_attribute_category IN VARCHAR2,
21 x_attribute1 IN VARCHAR2,
22 x_attribute2 IN VARCHAR2,
23 x_attribute3 IN VARCHAR2,
24 x_attribute4 IN VARCHAR2,
25 x_attribute5 IN VARCHAR2,
26 x_attribute6 IN VARCHAR2,
27 x_attribute7 IN VARCHAR2,
28 x_attribute8 IN VARCHAR2,
29 x_attribute9 IN VARCHAR2,
30 x_attribute10 IN VARCHAR2,
31 x_attribute11 IN VARCHAR2,
32 x_attribute12 IN VARCHAR2,
33 x_attribute13 IN VARCHAR2,
34 x_attribute14 IN VARCHAR2,
35 x_attribute15 IN VARCHAR2,
36 x_creation_date IN DATE,
37 x_created_by IN NUMBER,
38 x_last_update_date IN DATE,
39 x_last_updated_by IN NUMBER,
40 x_last_update_login IN NUMBER
41 ) IS
42
43 CURSOR c IS
44 SELECT ROWID
45 FROM ahl_warranty_entitlements
46 WHERE warranty_entitlement_id = x_warranty_entitlement_id;
47
48 BEGIN
49 INSERT INTO ahl_warranty_entitlements (
50 warranty_entitlement_id,
51 object_version_number,
52 warranty_contract_id,
53 old_warranty_contract_id,
54 entitlement_status_code,
55 warranty_claim_id,
56 po_header_id,
57 sr_incident_id,
58 visit_task_id,
59 workorder_id,
60 osp_order_line_id,
61 warranty_labour_capture,
62 warranty_mtl_capture,
63 order_claim_amount,
64 attribute_category,
65 attribute1,
66 attribute2,
67 attribute3,
68 attribute4,
69 attribute5,
70 attribute6,
71 attribute7,
72 attribute8,
73 attribute9,
74 attribute10,
75 attribute11,
76 attribute12,
77 attribute13,
78 attribute14,
79 attribute15,
80 creation_date,
81 created_by,
82 last_update_date,
83 last_updated_by,
84 last_update_login
85 ) VALUES (
86 x_warranty_entitlement_id,
87 x_object_version_number,
88 x_warranty_contract_id,
89 x_old_warranty_contract_id,
90 x_entitlement_status_code,
91 x_warranty_claim_id,
92 x_po_header_id,
93 x_sr_incident_id,
94 x_visit_task_id,
95 x_workorder_id,
96 x_osp_order_line_id,
97 x_warranty_labour_capture,
98 x_warranty_mtl_capture,
99 x_order_claim_amount,
100 x_attribute_category,
101 x_attribute1,
102 x_attribute2,
103 x_attribute3,
104 x_attribute4,
105 x_attribute5,
106 x_attribute6,
107 x_attribute7,
108 x_attribute8,
109 x_attribute9,
110 x_attribute10,
111 x_attribute11,
112 x_attribute12,
113 x_attribute13,
114 x_attribute14,
115 x_attribute15,
116 x_creation_date,
117 x_created_by,
118 x_last_update_date,
119 x_last_updated_by,
120 x_last_update_login
121 );
122
123 OPEN c;
124 FETCH c INTO x_row_id;
125 IF (c%NOTFOUND) THEN
126 CLOSE c;
127 RAISE no_data_found;
128 END IF;
129 CLOSE c;
130
131 END Insert_Row;
132
133 PROCEDURE Lock_Row (
134 x_warranty_entitlement_id IN NUMBER,
135 x_object_version_number IN NUMBER,
136 x_warranty_contract_id IN NUMBER,
137 x_old_warranty_contract_id IN NUMBER,
138 x_entitlement_status_code IN VARCHAR2,
139 x_warranty_claim_id IN NUMBER,
140 x_po_header_id IN NUMBER,
141 x_sr_incident_id IN NUMBER,
142 x_visit_task_id IN NUMBER,
143 x_workorder_id IN NUMBER,
144 x_osp_order_line_id IN NUMBER,
145 x_warranty_labour_capture IN VARCHAR2,
146 x_warranty_mtl_capture IN VARCHAR2,
147 x_order_claim_amount IN NUMBER,
148 x_attribute_category IN VARCHAR2,
149 x_attribute1 IN VARCHAR2,
150 x_attribute2 IN VARCHAR2,
151 x_attribute3 IN VARCHAR2,
152 x_attribute4 IN VARCHAR2,
153 x_attribute5 IN VARCHAR2,
154 x_attribute6 IN VARCHAR2,
155 x_attribute7 IN VARCHAR2,
156 x_attribute8 IN VARCHAR2,
157 x_attribute9 IN VARCHAR2,
158 x_attribute10 IN VARCHAR2,
159 x_attribute11 IN VARCHAR2,
160 x_attribute12 IN VARCHAR2,
161 x_attribute13 IN VARCHAR2,
162 x_attribute14 IN VARCHAR2,
163 x_attribute15 IN VARCHAR2
164 ) IS
165
166 CURSOR c IS
167 SELECT object_version_number,
168 warranty_contract_id,
169 old_warranty_contract_id,
170 entitlement_status_code,
171 warranty_claim_id,
172 po_header_id,
173 sr_incident_id,
174 visit_task_id,
175 workorder_id,
176 osp_order_line_id,
177 warranty_labour_capture,
178 warranty_mtl_capture,
179 order_claim_amount,
180 attribute_category,
181 attribute1,
182 attribute2,
183 attribute3,
184 attribute4,
185 attribute5,
186 attribute6,
187 attribute7,
188 attribute8,
189 attribute9,
190 attribute10,
191 attribute11,
192 attribute12,
193 attribute13,
194 attribute14,
195 attribute15
196 FROM ahl_warranty_entitlements
197 WHERE warranty_entitlement_id = x_warranty_entitlement_id
198 FOR UPDATE OF warranty_entitlement_id NOWAIT;
199
200 recinfo c%ROWTYPE;
201
202 BEGIN
203 OPEN c;
204 FETCH c INTO recinfo;
205 IF (c%NOTFOUND) THEN
206 CLOSE c;
207 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
208 APP_EXCEPTION.Raise_Exception;
209 END IF;
210 CLOSE c;
211
212 IF ( (recinfo.object_version_number = x_object_version_number)
213 AND ((recinfo.warranty_contract_id = x_warranty_contract_id)
214 OR ((recinfo.warranty_contract_id IS NULL) AND (x_warranty_contract_id IS NULL)))
215 AND ((recinfo.old_warranty_contract_id = x_old_warranty_contract_id)
216 OR ((recinfo.old_warranty_contract_id IS NULL) AND (x_old_warranty_contract_id IS NULL)))
217 AND ((recinfo.entitlement_status_code = x_entitlement_status_code)
218 OR ((recinfo.entitlement_status_code IS NULL) AND (x_entitlement_status_code IS NULL)))
219 AND ((recinfo.warranty_claim_id = x_warranty_claim_id)
220 OR ((recinfo.warranty_claim_id IS NULL) AND (x_warranty_claim_id IS NULL)))
221 AND ((recinfo.po_header_id = x_po_header_id)
222 OR ((recinfo.po_header_id IS NULL) AND (x_po_header_id IS NULL)))
223 AND ((recinfo.sr_incident_id = x_sr_incident_id)
224 OR ((recinfo.sr_incident_id IS NULL) AND (x_sr_incident_id IS NULL)))
225 AND ((recinfo.visit_task_id = x_visit_task_id)
226 OR ((recinfo.visit_task_id IS NULL) AND (x_visit_task_id IS NULL)))
227 AND ((recinfo.workorder_id = x_workorder_id)
228 OR ((recinfo.workorder_id IS NULL) AND (x_workorder_id IS NULL)))
229 AND ((recinfo.osp_order_line_id = x_osp_order_line_id)
230 OR ((recinfo.osp_order_line_id IS NULL) AND (x_osp_order_line_id IS NULL)))
231 AND ((recinfo.warranty_labour_capture = x_warranty_labour_capture)
232 OR ((recinfo.warranty_labour_capture IS NULL) AND (x_warranty_labour_capture IS NULL)))
233 AND ((recinfo.warranty_mtl_capture = x_warranty_mtl_capture)
234 OR ((recinfo.warranty_mtl_capture IS NULL) AND (x_warranty_mtl_capture IS NULL)))
235 AND ((recinfo.order_claim_amount = x_order_claim_amount)
236 OR ((recinfo.order_claim_amount IS NULL) AND (x_order_claim_amount IS NULL)))
237 AND ((recinfo.attribute_category = x_attribute_category)
238 OR ((recinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL)))
239 AND ((recinfo.attribute1 = x_attribute1)
240 OR ((recinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL)))
241 AND ((recinfo.attribute2 = x_attribute2)
242 OR ((recinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL)))
243 AND ((recinfo.attribute3 = x_attribute3)
244 OR ((recinfo.attribute3 IS NULL) AND (x_attribute3 IS NULL)))
245 AND ((recinfo.attribute4 = x_attribute4)
246 OR ((recinfo.attribute4 IS NULL) AND (x_attribute4 IS NULL)))
247 AND ((recinfo.attribute5 = x_attribute5)
248 OR ((recinfo.attribute5 IS NULL) AND (x_attribute5 IS NULL)))
249 AND ((recinfo.attribute6 = x_attribute6)
250 OR ((recinfo.attribute6 IS NULL) AND (x_attribute6 IS NULL)))
251 AND ((recinfo.attribute7 = x_attribute7)
252 OR ((recinfo.attribute7 IS NULL) AND (x_attribute7 IS NULL)))
253 AND ((recinfo.attribute8 = x_attribute8)
254 OR ((recinfo.attribute8 IS NULL) AND (x_attribute8 IS NULL)))
255 AND ((recinfo.attribute9 = x_attribute9)
256 OR ((recinfo.attribute9 IS NULL) AND (x_attribute9 IS NULL)))
257 AND ((recinfo.attribute10 = x_attribute10)
258 OR ((recinfo.attribute10 IS NULL) AND (x_attribute10 IS NULL)))
259 AND ((recinfo.attribute11 = x_attribute11)
260 OR ((recinfo.attribute11 IS NULL) AND (x_attribute11 IS NULL)))
261 AND ((recinfo.attribute12 = x_attribute12)
262 OR ((recinfo.attribute12 IS NULL) AND (x_attribute12 IS NULL)))
263 AND ((recinfo.attribute13 = x_attribute13)
264 OR ((recinfo.attribute13 IS NULL) AND (x_attribute13 IS NULL)))
265 AND ((recinfo.attribute14 = x_attribute14)
266 OR ((recinfo.attribute14 IS NULL) AND (x_attribute14 IS NULL)))
267 AND ((recinfo.attribute15 = x_attribute15)
268 OR ((recinfo.attribute15 IS NULL) AND (x_attribute15 IS NULL)))
269 ) THEN
270 NULL;
271 ELSE
272 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
273 APP_EXCEPTION.Raise_Exception;
274 END IF;
275 RETURN;
276 END Lock_Row;
277
278 PROCEDURE Update_Row (
279 x_warranty_entitlement_id IN NUMBER,
280 x_object_version_number IN NUMBER,
281 x_warranty_contract_id IN NUMBER,
282 x_old_warranty_contract_id IN NUMBER,
283 x_entitlement_status_code IN VARCHAR2,
284 x_warranty_claim_id IN NUMBER,
285 x_po_header_id IN NUMBER,
286 x_sr_incident_id IN NUMBER,
287 x_visit_task_id IN NUMBER,
288 x_workorder_id IN NUMBER,
289 x_osp_order_line_id IN NUMBER,
290 x_warranty_labour_capture IN VARCHAR2,
291 x_warranty_mtl_capture IN VARCHAR2,
292 x_order_claim_amount IN NUMBER,
293 x_attribute_category IN VARCHAR2,
294 x_attribute1 IN VARCHAR2,
295 x_attribute2 IN VARCHAR2,
296 x_attribute3 IN VARCHAR2,
297 x_attribute4 IN VARCHAR2,
298 x_attribute5 IN VARCHAR2,
299 x_attribute6 IN VARCHAR2,
300 x_attribute7 IN VARCHAR2,
301 x_attribute8 IN VARCHAR2,
302 x_attribute9 IN VARCHAR2,
303 x_attribute10 IN VARCHAR2,
304 x_attribute11 IN VARCHAR2,
305 x_attribute12 IN VARCHAR2,
306 x_attribute13 IN VARCHAR2,
307 x_attribute14 IN VARCHAR2,
308 x_attribute15 IN VARCHAR2,
309 x_last_update_date IN DATE,
310 x_last_updated_by IN NUMBER,
311 x_last_update_login IN NUMBER
312 ) IS
313 BEGIN
314 UPDATE ahl_warranty_entitlements SET
315 object_version_number = x_object_version_number,
316 warranty_contract_id = x_warranty_contract_id,
317 old_warranty_contract_id = x_old_warranty_contract_id,
318 entitlement_status_code = x_entitlement_status_code,
319 warranty_claim_id = x_warranty_claim_id,
320 po_header_id = x_po_header_id,
321 sr_incident_id = x_sr_incident_id,
322 visit_task_id = x_visit_task_id,
323 workorder_id = x_workorder_id,
324 osp_order_line_id = x_osp_order_line_id,
325 warranty_labour_capture = x_warranty_labour_capture,
326 warranty_mtl_capture = x_warranty_mtl_capture,
327 order_claim_amount = x_order_claim_amount,
328 attribute_category = x_attribute_category,
329 attribute1 = x_attribute1,
330 attribute2 = x_attribute2,
331 attribute3 = x_attribute3,
332 attribute4 = x_attribute4,
333 attribute5 = x_attribute5,
334 attribute6 = x_attribute6,
335 attribute7 = x_attribute7,
336 attribute8 = x_attribute8,
337 attribute9 = x_attribute9,
338 attribute10 = x_attribute10,
339 attribute11 = x_attribute11,
340 attribute12 = x_attribute12,
341 attribute13 = x_attribute13,
342 attribute14 = x_attribute14,
343 attribute15 = x_attribute15,
344 last_update_date = x_last_update_date,
345 last_updated_by = x_last_updated_by,
346 last_update_login = x_last_update_login
347 WHERE warranty_entitlement_id = x_warranty_entitlement_id;
348
349 IF (SQL%NOTFOUND) THEN
350 RAISE NO_DATA_FOUND;
351 END IF;
352 END Update_Row;
353
354 PROCEDURE Delete_Row (
355 x_warranty_entitlement_id IN NUMBER
356 ) IS
357 BEGIN
358 DELETE FROM ahl_warranty_entitlements
359 WHERE warranty_entitlement_id = x_warranty_entitlement_id;
360
361 IF (SQL%NOTFOUND) THEN
362 RAISE NO_DATA_FOUND;
363 END IF;
364 END Delete_Row;
365
366 END AHL_WARRANTY_ENTITLEMENTS_PKG;
[Home] [Help]