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