[Home] [Help]
PACKAGE BODY: APPS.OZF_PRODUCT_ALLOCATIONS_PKG
Source
1 PACKAGE BODY OZF_PRODUCT_ALLOCATIONS_PKG AS
2 /* $Header: ozftpalb.pls 120.0 2005/06/01 03:16:19 appldev noship $ */
3
4 ---g_version CONSTANT CHAR(80) := '$Header: ozftpalb.pls 120.0 2005/06/01 03:16:19 appldev noship $';
5 G_PKG_NAME CONSTANT VARCHAR2(30):='OZF_PRODUCT_ALLOCATIONS_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftpalb.pls';
7
8 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
9 OZF_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
11
12
13
14 -- ========================================================
15 --
16 -- NAME
17 -- Insert_Row
18 --
19 -- PURPOSE
20 --
21 -- NOTES
22 --
23 -- HISTORY
24 --
25 -- ========================================================
26 PROCEDURE Insert_Row(
27 px_product_allocation_id IN OUT NOCOPY NUMBER,
28 p_allocation_for VARCHAR2,
29 p_allocation_for_id NUMBER,
30 p_fund_id NUMBER,
31 p_item_type VARCHAR2,
32 p_item_id NUMBER,
33 p_selected_flag VARCHAR2,
34 p_target NUMBER,
35 p_lysp_sales NUMBER,
36 p_parent_product_allocation_id NUMBER,
37 px_object_version_number IN OUT NOCOPY NUMBER,
38 p_creation_date DATE,
39 p_created_by NUMBER,
40 p_last_update_date DATE,
41 p_last_updated_by NUMBER,
42 p_last_update_login NUMBER,
43 p_attribute_category VARCHAR2,
44 p_attribute1 VARCHAR2,
45 p_attribute2 VARCHAR2,
46 p_attribute3 VARCHAR2,
47 p_attribute4 VARCHAR2,
48 p_attribute5 VARCHAR2,
49 p_attribute6 VARCHAR2,
50 p_attribute7 VARCHAR2,
51 p_attribute8 VARCHAR2,
52 p_attribute9 VARCHAR2,
53 p_attribute10 VARCHAR2,
54 p_attribute11 VARCHAR2,
55 p_attribute12 VARCHAR2,
56 p_attribute13 VARCHAR2,
57 p_attribute14 VARCHAR2,
58 p_attribute15 VARCHAR2,
59 px_org_id IN OUT NOCOPY NUMBER
60 )
61 IS
62 x_rowid VARCHAR2(30);
63
64
65 BEGIN
66
67 IF (px_org_id IS NULL OR px_org_id = FND_API.G_MISS_NUM) THEN
68 SELECT NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99)
69 INTO px_org_id
70 FROM DUAL;
71 END IF;
72
73 px_object_version_number := nvl(px_object_version_number, 1);
74
75 INSERT INTO ozf_product_allocations(
76 product_allocation_id,
77 allocation_for,
78 allocation_for_id,
79 fund_id,
80 item_type,
81 item_id,
82 selected_flag,
83 target,
84 lysp_sales,
85 parent_product_allocation_id,
86 object_version_number,
87 creation_date,
88 created_by,
89 last_update_date,
90 last_updated_by,
91 last_update_login,
92 attribute_category,
93 attribute1,
94 attribute2,
95 attribute3,
96 attribute4,
97 attribute5,
98 attribute6,
99 attribute7,
100 attribute8,
101 attribute9,
102 attribute10,
103 attribute11,
104 attribute12,
105 attribute13,
106 attribute14,
107 attribute15,
108 org_id
109 )
110 VALUES
111 (
112 DECODE( px_product_allocation_id, FND_API.G_MISS_NUM, NULL, px_product_allocation_id),
113 DECODE( p_allocation_for, FND_API.g_miss_char, NULL, p_allocation_for),
114 DECODE( p_allocation_for_id, FND_API.G_MISS_NUM, NULL, p_allocation_for_id),
115 DECODE( p_fund_id, FND_API.G_MISS_NUM, NULL, p_fund_id),
116 DECODE( p_item_type, FND_API.g_miss_char, NULL, p_item_type),
117 DECODE( p_item_id, FND_API.G_MISS_NUM, NULL, p_item_id),
118 DECODE( p_selected_flag, FND_API.g_miss_char, NULL, p_selected_flag),
119 DECODE( p_target, FND_API.G_MISS_NUM, NULL, p_target),
120 DECODE( p_lysp_sales, FND_API.G_MISS_NUM, NULL, p_lysp_sales),
121 DECODE( p_parent_product_allocation_id, FND_API.G_MISS_NUM, NULL, p_parent_product_allocation_id),
122 px_object_version_number,
123 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
124 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
125 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
126 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
127 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
128 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
129 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
130 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
131 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
132 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
133 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
134 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
135 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
136 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
137 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
138 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
139 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
140 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
141 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
142 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
143 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
144 px_org_id
145 );
146
147 END Insert_Row;
148
149
150
151
152 -- ========================================================
153 --
154 -- NAME
155 -- Update_Row
156 --
157 -- PURPOSE
158 --
159 -- NOTES
160 --
161 -- HISTORY
162 --
163 -- ========================================================
164 PROCEDURE Update_Row(
165 p_product_allocation_id NUMBER,
166 p_allocation_for VARCHAR2,
167 p_allocation_for_id NUMBER,
168 p_fund_id NUMBER,
169 p_item_type VARCHAR2,
170 p_item_id NUMBER,
171 p_selected_flag VARCHAR2,
172 p_target NUMBER,
173 p_lysp_sales NUMBER,
174 p_parent_product_allocation_id NUMBER,
175 p_object_version_number IN NUMBER,
176 p_last_update_date DATE,
177 p_last_updated_by NUMBER,
178 p_last_update_login NUMBER,
179 p_attribute_category VARCHAR2,
180 p_attribute1 VARCHAR2,
181 p_attribute2 VARCHAR2,
182 p_attribute3 VARCHAR2,
183 p_attribute4 VARCHAR2,
184 p_attribute5 VARCHAR2,
185 p_attribute6 VARCHAR2,
186 p_attribute7 VARCHAR2,
187 p_attribute8 VARCHAR2,
188 p_attribute9 VARCHAR2,
189 p_attribute10 VARCHAR2,
190 p_attribute11 VARCHAR2,
191 p_attribute12 VARCHAR2,
192 p_attribute13 VARCHAR2,
193 p_attribute14 VARCHAR2,
194 p_attribute15 VARCHAR2
195 )
196 IS
197 BEGIN
198 Update ozf_product_allocations
199 SET
200 product_allocation_id = DECODE( p_product_allocation_id, null, product_allocation_id, FND_API.G_MISS_NUM, null, p_product_allocation_id),
201 allocation_for = DECODE( p_allocation_for, null, allocation_for, FND_API.g_miss_char, null, p_allocation_for),
202 allocation_for_id = DECODE( p_allocation_for_id, null, allocation_for_id, FND_API.G_MISS_NUM, null, p_allocation_for_id),
203 fund_id = DECODE( p_fund_id, null, fund_id, FND_API.G_MISS_NUM, null, p_fund_id),
204 item_type = DECODE( p_item_type, null, item_type, FND_API.g_miss_char, null, p_item_type),
205 item_id = DECODE( p_item_id, null, item_id, FND_API.G_MISS_NUM, null, p_item_id),
206 selected_flag = DECODE( p_selected_flag, null, selected_flag, FND_API.g_miss_char, null, p_selected_flag),
207 target = DECODE( p_target, null, target, FND_API.G_MISS_NUM, null, p_target),
208 lysp_sales = DECODE( p_lysp_sales, null, lysp_sales, FND_API.G_MISS_NUM, null, p_lysp_sales),
209 parent_product_allocation_id = DECODE(p_parent_product_allocation_id,null,parent_product_allocation_id, FND_API.G_MISS_NUM, null, p_parent_product_allocation_id),
210 object_version_number = nvl(p_object_version_number, object_version_number) + 1 ,
211 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),
212 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
213 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
214 attribute_category = DECODE( p_attribute_category, null, attribute_category, FND_API.g_miss_char, null, p_attribute_category),
215 attribute1 = DECODE( p_attribute1, null, attribute1, FND_API.g_miss_char, null, p_attribute1),
216 attribute2 = DECODE( p_attribute2, null, attribute2, FND_API.g_miss_char, null, p_attribute2),
217 attribute3 = DECODE( p_attribute3, null, attribute3, FND_API.g_miss_char, null, p_attribute3),
218 attribute4 = DECODE( p_attribute4, null, attribute4, FND_API.g_miss_char, null, p_attribute4),
219 attribute5 = DECODE( p_attribute5, null, attribute5, FND_API.g_miss_char, null, p_attribute5),
220 attribute6 = DECODE( p_attribute6, null, attribute6, FND_API.g_miss_char, null, p_attribute6),
221 attribute7 = DECODE( p_attribute7, null, attribute7, FND_API.g_miss_char, null, p_attribute7),
222 attribute8 = DECODE( p_attribute8, null, attribute8, FND_API.g_miss_char, null, p_attribute8),
223 attribute9 = DECODE( p_attribute9, null, attribute9, FND_API.g_miss_char, null, p_attribute9),
224 attribute10 = DECODE( p_attribute10, null, attribute10, FND_API.g_miss_char, null, p_attribute10),
225 attribute11 = DECODE( p_attribute11, null, attribute11, FND_API.g_miss_char, null, p_attribute11),
226 attribute12 = DECODE( p_attribute12, null, attribute12, FND_API.g_miss_char, null, p_attribute12),
227 attribute13 = DECODE( p_attribute13, null, attribute13, FND_API.g_miss_char, null, p_attribute13),
228 attribute14 = DECODE( p_attribute14, null, attribute14, FND_API.g_miss_char, null, p_attribute14),
229 attribute15 = DECODE( p_attribute15, null, attribute15, FND_API.g_miss_char, null, p_attribute15)
230 WHERE product_allocation_id = p_product_allocation_id;
231 -- AND object_version_number = p_object_version_number;
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232
233
234 IF (SQL%NOTFOUND) THEN
236 END IF;
237
238
239 END Update_Row;
240
241
242
243
244 -- ========================================================
245 --
246 -- NAME
247 -- Delete_Row
248 --
249 -- PURPOSE
250 --
251 -- NOTES
252 --
253 -- HISTORY
254 --
255 -- ========================================================
256 PROCEDURE Delete_Row(
257 p_product_allocation_id NUMBER,
258 p_object_version_number NUMBER)
259 IS
260 BEGIN
261 DELETE FROM ozf_product_allocations
262 WHERE product_allocation_id = p_product_allocation_id
263 AND object_version_number = p_object_version_number;
264 If (SQL%NOTFOUND) then
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 End If;
267 END Delete_Row ;
268
269
270
271
272
273 -- ========================================================
274 --
275 -- NAME
276 -- Lock_Row
277 --
278 -- PURPOSE
279 --
280 -- NOTES
281 --
282 -- HISTORY
283 --
284 -- ========================================================
285 PROCEDURE Lock_Row(
286 p_product_allocation_id NUMBER,
287 p_object_version_number NUMBER)
288 IS
289 CURSOR C IS
290 SELECT *
291 FROM ozf_product_allocations
292 WHERE product_allocation_id = p_product_allocation_id
293 AND object_version_number = p_object_version_number
294 FOR UPDATE OF product_allocation_id NOWAIT;
295 Recinfo C%ROWTYPE;
296 BEGIN
297
298 OPEN c;
299 FETCH c INTO Recinfo;
300 IF (c%NOTFOUND) THEN
301 CLOSE c;
302 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
303 RAISE FND_API.g_exc_error;
304 END IF;
305 CLOSE c;
306 END Lock_Row;
307
308
309
310 END Ozf_Product_Allocations_PKG;