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