[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJ_NEW_LINES_PKG
Source
1 PACKAGE BODY OZF_OFFER_ADJ_NEW_LINES_PKG as
2 /* $Header: ozftanlb.pls 120.0 2006/03/30 13:49:34 rssharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_OFFER_ADJ_NEW_LINES_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_OFFER_ADJ_NEW_LINES_PKG';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftoanb.pls';
17 ----------------------------------------------------------
18 ---- MEDIA ----
19 ----------------------------------------------------------
20
21 -- ========================================================
22 --
23 -- NAME
24 -- createInsertBody
25 --
26 -- PURPOSE
27 --
28 -- NOTES
29 --
30 -- HISTORY
31 --
32 -- ========================================================
33 PROCEDURE Insert_Row(
34 px_offer_adj_new_line_id IN OUT NOCOPY NUMBER,
35 p_offer_adjustment_id NUMBER,
36 p_volume_from NUMBER,
37 p_volume_to NUMBER,
38 p_volume_type VARCHAR2,
39 p_discount NUMBER,
40 p_discount_type VARCHAR2,
41 p_tier_type VARCHAR2,
42 p_td_discount NUMBER,
43 p_td_discount_type VARCHAR2,
44 p_quantity NUMBER,
45 p_benefit_price_list_line_id NUMBER,
46 p_parent_adj_line_id NUMBER,
47 p_start_date_active DATE,
48 p_end_date_active DATE,
49 p_creation_date DATE,
50 p_created_by NUMBER,
51 p_last_update_date DATE,
52 p_last_updated_by NUMBER,
53 p_last_update_login NUMBER,
54 px_object_version_number IN OUT NOCOPY NUMBER)
55
56 IS
57 x_rowid VARCHAR2(30);
58
59
60 BEGIN
61
62
63 px_object_version_number := 1;
64
65
66 INSERT INTO OZF_OFFER_ADJ_NEW_LINES(
67 offer_adj_new_line_id,
68 offer_adjustment_id,
69 volume_from,
70 volume_to,
71 volume_type,
72 discount,
73 discount_type,
74 tier_type,
75 td_discount,
76 td_discount_type,
77 quantity ,
78 benefit_price_list_line_id,
79 parent_adj_line_id,
80 start_date_active,
81 end_date_active,
82 creation_date,
83 created_by,
84 last_update_date,
85 last_updated_by,
86 last_update_login,
87 object_version_number
88 ) VALUES (
89 DECODE( px_offer_adj_new_line_id, FND_API.g_miss_num, NULL, px_offer_adj_new_line_id)
90 , DECODE( p_offer_adjustment_id, FND_API.g_miss_num, NULL, p_offer_adjustment_id)
91 , DECODE( p_volume_from, FND_API.g_miss_num, NULL, p_volume_from)
92 , DECODE( p_volume_to, FND_API.g_miss_num, NULL, p_volume_to)
93 , DECODE( p_volume_type, FND_API.g_miss_char, NULL, p_volume_type)
94 , DECODE( p_discount, FND_API.g_miss_num, NULL, p_discount)
95 , DECODE( p_discount_type, FND_API.g_miss_char, NULL, p_discount_type)
96 , DECODE( p_tier_type, FND_API.g_miss_char, NULL, p_tier_type)
97 , DECODE( p_td_discount, FND_API.g_miss_num, NULL, p_td_discount)
98 , DECODE( p_td_discount_type, FND_API.g_miss_char, NULL, p_td_discount_type)
99 , DECODE( p_quantity, FND_API.G_MISS_NUM, null,p_quantity)
100 , DECODE( P_benefit_price_list_line_id, FND_API.G_MISS_NUM, null, p_benefit_price_list_line_id)
101 , DECODE( p_parent_adj_line_id , FND_API.G_MISS_NUM, null, p_parent_adj_line_id)
102 , DECODE( p_start_date_active, FND_API.G_MISS_DATE , null, p_start_date_active)
103 , DECODE( p_end_date_active, FND_API.G_MISS_DATE , null, p_end_date_active)
104 , DECODE( p_creation_date, FND_API.g_miss_date, SYSDATE, p_creation_date)
105 , DECODE( p_created_by, FND_API.g_miss_num, FND_GLOBAL.USER_ID, p_created_by)
106 , DECODE( p_last_update_date, FND_API.g_miss_date, SYSDATE, p_last_update_date)
107 , DECODE( p_last_updated_by, FND_API.g_miss_num, FND_GLOBAL.USER_ID, p_last_updated_by)
108 , DECODE( p_last_update_login, FND_API.g_miss_num, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login)
109 , DECODE( px_object_version_number, FND_API.g_miss_num, 1, px_object_version_number)
110 );
111
112 END Insert_Row;
113
114
115 ----------------------------------------------------------
116 ---- MEDIA ----
117 ----------------------------------------------------------
118
119 -- ========================================================
120 --
121 -- NAME
122 -- createUpdateBody
123 --
124 -- PURPOSE
125 --
126 -- NOTES
127 --
128 -- HISTORY
129 --
130 -- ========================================================
131 PROCEDURE Update_Row(
132 p_offer_adj_new_line_id NUMBER,
133 p_offer_adjustment_id NUMBER,
134 p_volume_from NUMBER,
135 p_volume_to NUMBER,
136 p_volume_type VARCHAR2,
137 p_discount NUMBER,
138 p_discount_type VARCHAR2,
139 p_tier_type VARCHAR2,
140 p_td_discount NUMBER,
141 p_td_discount_type VARCHAR2,
142 p_quantity NUMBER,
143 p_benefit_price_list_line_id NUMBER,
144 p_parent_adj_line_id NUMBER,
145 p_start_date_active DATE,
146 p_end_date_active DATE,
147 p_last_update_date DATE,
148 p_last_updated_by NUMBER,
149 p_last_update_login NUMBER,
150 p_object_version_number NUMBER)
151
152 IS
153 BEGIN
154 Update OZF_OFFER_ADJ_NEW_LINES
155 SET
156 offer_adj_new_line_id = DECODE( p_offer_adj_new_line_id, FND_API.g_miss_num, NULL, NULL, offer_adj_new_line_id, p_offer_adj_new_line_id),
157 offer_adjustment_id = DECODE( p_offer_adjustment_id, FND_API.g_miss_num, NULL, NULL, offer_adjustment_id, p_offer_adjustment_id),
158 volume_from = DECODE( p_volume_from, FND_API.g_miss_num, NULL, NULL,volume_from, p_volume_from),
159 volume_to = DECODE( p_volume_to, FND_API.g_miss_num, NULL, NULL, volume_to, p_volume_to),
160 volume_type = DECODE( p_volume_type, FND_API.g_miss_char, NULL, NULL, volume_type, p_volume_type),
161 discount = DECODE( p_discount, FND_API.g_miss_num, NULL, NULL, discount, p_discount),
162 discount_type = DECODE( p_discount_type, FND_API.g_miss_char, NULL, NULL, discount_type, p_discount_type),
163 tier_type = DECODE( p_tier_type, FND_API.g_miss_char, NULL, NULL, tier_type, p_tier_type),
164 td_discount = DECODE(p_td_discount , FND_API.g_miss_num, NULL, NULL, td_discount, p_td_discount),
165 td_discount_type = DECODE(p_td_discount_type, FND_API.g_miss_char, NULL, NULL,td_discount_type, p_td_discount_type),
166 quantity = DECODE(p_quantity , FND_API.G_MISS_NUM, NULL,null, quantity, p_quantity),
167 benefit_price_list_line_id = DECODE(p_benefit_price_list_line_id, FND_API.G_MISS_NUM , NULL, null, benefit_price_list_line_id, p_benefit_price_list_line_id),
168 parent_adj_line_id = DECODE(p_parent_adj_line_id , fnd_api.g_miss_num, NULL, NULL, parent_adj_line_id , p_parent_adj_line_id),
169 start_date_active = DECODE( p_start_date_active, FND_API.G_MISS_DATE , null, null, start_date_active, p_start_date_active),
170 end_date_active = DECODE( p_end_date_active, FND_API.G_MISS_DATE , null, null , end_date_active, p_end_date_active),
171 last_update_date = DECODE( p_last_update_date, to_date(NULL), last_update_date, FND_API.G_MISS_DATE, to_date(null), p_last_update_date),
172 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
173 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
174 object_version_number = nvl(p_object_version_number,0) + 1
175 WHERE OFFER_ADJ_NEW_LINE_ID = p_OFFER_ADJ_NEW_LINE_ID
176 AND object_version_number = p_object_version_number;
177
178 IF (SQL%NOTFOUND) THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181 END Update_Row;
182
183
184 ----------------------------------------------------------
185 ---- MEDIA ----
186 ----------------------------------------------------------
187
188 -- ========================================================
189 --
190 -- NAME
191 -- createDeleteBody
192 --
193 -- PURPOSE
194 --
195 -- NOTES
196 --
197 -- HISTORY
198 --
199 -- ========================================================
200 PROCEDURE Delete_Row(
201 p_OFFER_ADJ_NEW_LINE_ID NUMBER)
202 IS
203 BEGIN
204 DELETE FROM OZF_OFFER_ADJ_NEW_LINES
205 WHERE OFFER_ADJ_NEW_LINE_ID = p_OFFER_ADJ_NEW_LINE_ID;
206 If (SQL%NOTFOUND) then
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 End If;
209 END Delete_Row ;
210
211
212
213 ----------------------------------------------------------
214 ---- MEDIA ----
215 ----------------------------------------------------------
216
217 -- ========================================================
218 --
219 -- NAME
220 -- createLockBody
221 --
222 -- PURPOSE
223 --
224 -- NOTES
225 --
226 -- HISTORY
227 --
228 -- ========================================================
229 PROCEDURE Lock_Row(
230 p_offer_adj_new_line_id NUMBER,
231 p_offer_adjustment_id NUMBER,
232 p_volume_from NUMBER,
233 p_volume_to NUMBER,
234 p_volume_type VARCHAR2,
235 p_discount NUMBER,
236 p_discount_type VARCHAR2,
237 p_tier_type VARCHAR2,
238 p_td_discount NUMBER,
239 p_td_discount_type VARCHAR2,
240 p_quantity NUMBER,
241 p_benefit_price_list_line_id NUMBER,
242 p_parent_adj_line_id NUMBER,
243 p_creation_date DATE,
244 p_created_by NUMBER,
245 p_last_update_date DATE,
246 p_last_updated_by NUMBER,
247 p_last_update_login NUMBER,
248 p_object_version_number NUMBER)
249
250 IS
251 CURSOR C IS
252 SELECT *
253 FROM OZF_OFFER_ADJ_NEW_LINES
254 WHERE OFFER_ADJ_NEW_LINE_ID = p_OFFER_ADJ_NEW_LINE_ID
255 FOR UPDATE of OFFER_ADJ_NEW_LINE_ID NOWAIT;
256 Recinfo C%ROWTYPE;
257 BEGIN
258 OPEN c;
259 FETCH c INTO Recinfo;
260 If (c%NOTFOUND) then
261 CLOSE c;
262 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 END IF;
265 CLOSE C;
266 IF (
267 ( Recinfo.offer_adj_new_line_id = p_offer_adj_new_line_id)
268 AND ( ( Recinfo.offer_adjustment_id = p_offer_adjustment_id)
269 OR ( ( Recinfo.offer_adjustment_id IS NULL )
270 AND ( p_offer_adjustment_id IS NULL )))
271 AND ( ( Recinfo.volume_from = p_volume_from)
272 OR ( ( Recinfo.volume_from IS NULL )
273 AND ( p_volume_from IS NULL )))
274 AND ( ( Recinfo.volume_to = p_volume_to)
275 OR ( ( Recinfo.volume_to IS NULL )
276 AND ( p_volume_to IS NULL )))
277 AND ( ( Recinfo.volume_type = p_volume_type)
278 OR ( ( Recinfo.volume_type IS NULL )
279 AND ( p_volume_type IS NULL )))
280 AND ( ( Recinfo.discount = p_discount)
281 OR ( ( Recinfo.discount IS NULL )
282 AND ( p_discount IS NULL )))
283 AND ( ( Recinfo.discount_type = p_discount_type)
284 OR ( ( Recinfo.discount_type IS NULL )
285 AND ( p_discount_type IS NULL )))
286 AND ( ( Recinfo.tier_type = p_tier_type)
287 OR ( ( Recinfo.tier_type IS NULL )
288 AND ( p_tier_type IS NULL )))
289 AND ( ( Recinfo.td_discount = p_td_discount)
290 OR ( ( Recinfo.td_discount IS NULL )
291 AND ( p_td_discount IS NULL )))
292 AND ( ( Recinfo.td_discount_type = p_td_discount_type)
293 OR ( ( Recinfo.td_discount_type IS NULL )
294 AND ( p_td_discount_type IS NULL )))
295 AND ( ( Recinfo.quantity = p_quantity)
296 OR ( ( Recinfo.quantity IS NULL )
297 AND ( p_quantity IS NULL )))
298 AND ( ( Recinfo.benefit_price_list_line_id = p_benefit_price_list_line_id)
299 OR ( ( Recinfo.benefit_price_list_line_id IS NULL )
300 AND ( p_benefit_price_list_line_id IS NULL )))
301 AND ( ( Recinfo.parent_adj_line_id = p_parent_adj_line_id)
302 OR ( ( Recinfo.parent_adj_line_id IS NULL )
303 AND ( p_parent_adj_line_id IS NULL )))
304 AND ( ( Recinfo.creation_date = p_creation_date)
305 OR ( ( Recinfo.creation_date IS NULL )
306 AND ( p_creation_date IS NULL )))
307 AND ( ( Recinfo.created_by = p_created_by)
308 OR ( ( Recinfo.created_by IS NULL )
312 AND ( p_last_update_date IS NULL )))
309 AND ( p_created_by IS NULL )))
310 AND ( ( Recinfo.last_update_date = p_last_update_date)
311 OR ( ( Recinfo.last_update_date IS NULL )
313 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
314 OR ( ( Recinfo.last_updated_by IS NULL )
315 AND ( p_last_updated_by IS NULL )))
316 AND ( ( Recinfo.last_update_login = p_last_update_login)
317 OR ( ( Recinfo.last_update_login IS NULL )
318 AND ( p_last_update_login IS NULL )))
319 AND ( ( Recinfo.object_version_number = p_object_version_number)
320 OR ( ( Recinfo.object_version_number IS NULL )
321 AND ( p_object_version_number IS NULL )))
322 ) THEN
323 RETURN;
324 ELSE
325 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
326 APP_EXCEPTION.RAISE_EXCEPTION;
327 END IF;
328 END Lock_Row;
329
330 END OZF_OFFER_ADJ_NEW_LINES_PKG;