[Home] [Help]
PACKAGE BODY: APPS.CN_PREPOSTDETAILS
Source
1 PACKAGE BODY CN_PREPOSTDETAILS AS
2 -- $Header: cntpdetb.pls 115.8 2002/01/28 20:05:02 pkm ship $ --+
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PREPOSTDETAILS';
5 G_LAST_UPDATE_DATE DATE := SYSDATE;
6 G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
7 G_CREATION_DATE DATE := SYSDATE;
8 G_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
9 G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
10 -- ----------------------------------------------------------------------------+
11 --
12 -- Procedure : Get_UID
13 -- Description : Get the sequence number to create a new posting detail
14 --
15 -- ----------------------------------------------------------------------------+
16 PROCEDURE Get_UID( x_posting_detail_id IN OUT NUMBER )
17 IS
18 CURSOR get_id IS
19 SELECT cn_posting_details_s.nextval
20 FROM dual;
21 BEGIN
22 OPEN get_id;
23 FETCH get_id INTO x_posting_detail_id;
24 CLOSE get_id;
25 END Get_UID;
26 -- ----------------------------------------------------------------------------+
27 --
28 -- Procedure : Insert_Record
29 -- Description : Main insert procedure
30 -- Calls :
31 --
32 -- ----------------------------------------------------------------------------+
33 PROCEDURE Insert_Record
34 (x_rowid IN OUT VARCHAR2,
35 x_posting_detail_id IN OUT NUMBER,
36 x_posting_batch_id NUMBER,
37 x_posting_type VARCHAR2,
38 x_trx_type VARCHAR2,
39 x_payee_salesrep_id NUMBER,
40 x_role_id NUMBER,
41 x_incentive_type_code VARCHAR2,
42 x_credit_type_id NUMBER,
43 x_pay_period_id NUMBER,
44 x_amount NUMBER,
45 x_commission_header_id NUMBER,
46 x_commission_line_id NUMBER,
47 x_srp_plan_assign_id NUMBER,
48 x_quota_id NUMBER,
49 x_status VARCHAR2,
50 x_loaded_date DATE,
51 x_processed_date DATE,
52 x_credited_salesrep_id NUMBER,
53 x_processed_period_id NUMBER,
54 x_quota_rule_id NUMBER,
55 x_event_factor NUMBER,
56 x_payment_factor NUMBER,
57 x_quota_factor NUMBER,
58 x_pending_status VARCHAR2,
59 x_input_achieved NUMBER,
60 x_rate_tier_id NUMBER,
61 x_payee_line_id NUMBER,
62 x_cl_status VARCHAR2,
63 x_created_during VARCHAR2,
64 x_commission_rate NUMBER,
65 x_hold_flag VARCHAR2,
66 x_paid_flag VARCHAR2,
67 x_payment_amount NUMBER,
68 x_attribute_category VARCHAR2,
69 x_attribute1 VARCHAR2,
70 x_attribute2 VARCHAR2,
71 x_attribute3 VARCHAR2,
72 x_attribute4 VARCHAR2,
73 x_attribute5 VARCHAR2,
74 x_attribute6 VARCHAR2,
75 x_attribute7 VARCHAR2,
76 x_attribute8 VARCHAR2,
77 x_attribute9 VARCHAR2,
78 x_attribute10 VARCHAR2,
79 x_attribute11 VARCHAR2,
80 x_attribute12 VARCHAR2,
81 x_attribute13 VARCHAR2,
82 x_attribute14 VARCHAR2,
83 x_attribute15 VARCHAR2,
84 x_last_update_date DATE,
85 x_last_updated_by NUMBER,
86 x_last_update_login NUMBER,
87 x_creation_date DATE,
88 x_created_by NUMBER)
89 IS
90 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Record';
91 l_loading_status VARCHAR2(30) := 'CN_INSERTED';
92 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
93 BEGIN
94 --dbms_output.put_line('before get UID ');
95 -- Get Unique ID for posting detail
96 Get_UID( x_posting_detail_id );
97 --dbms_output.put_line('after get UID and ID is '||to_char(x_posting_detail_id));
98
99 -- several fields (including the attributes) might have G_MISS values
100 -- these fields are the hold, paid flag and payment amount(to have defaults)
101 -- and status and loaded date used in update (along with attributes)
102
103 INSERT INTO cn_posting_details
104 (posting_detail_id,
105 posting_batch_id,
106 posting_type,
107 trx_type,
108 payee_salesrep_id,
109 role_id,
110 incentive_type_code,
111 credit_type_id,
112 pay_period_id,
113 amount,
114 commission_header_id,
115 commission_line_id,
116 srp_plan_assign_id,
117 quota_id,
118 status,
119 loaded_date,
120 processed_date,
121 credited_salesrep_id,
122 processed_period_id,
123 quota_rule_id,
124 event_factor,
125 payment_factor,
126 quota_factor,
127 pending_status,
128 input_achieved,
129 rate_tier_id,
130 payee_line_id,
131 cl_status,
132 created_during,
133 commission_rate,
134 hold_flag,
135 paid_flag,
136 payment_amount,
137 attribute_category,
138 attribute1 ,
139 attribute2 ,
140 attribute3 ,
141 attribute4 ,
142 attribute5 ,
143 attribute6 ,
144 attribute7 ,
145 attribute8 ,
146 attribute9 ,
147 attribute10 ,
148 attribute11 ,
149 attribute12 ,
150 attribute13 ,
151 attribute14 ,
152 attribute15 ,
153 last_update_date,
154 last_updated_by,
155 last_update_login,
156 creation_date,
157 created_by)
158 (SELECT
159 x_posting_detail_id,
160 x_posting_batch_id,
161 x_posting_type,
162 x_trx_type,
163 x_payee_salesrep_id,
164 x_role_id,
165 x_incentive_type_code,
166 x_credit_type_id,
167 x_pay_period_id,
168 x_amount,
169 x_commission_header_id,
170 x_commission_line_id,
171 x_srp_plan_assign_id,
172 x_quota_id,
173 decode(x_status, FND_API.G_MISS_CHAR, 'UNLOADED', x_status),
174 decode(x_loaded_date, FND_API.G_MISS_CHAR, null, x_loaded_date),
175 x_processed_date,
176 x_credited_salesrep_id,
177 x_processed_period_id,
178 x_quota_rule_id,
179 x_event_factor,
180 x_payment_factor,
181 x_quota_factor,
182 x_pending_status,
183 x_input_achieved,
184 x_rate_tier_id,
185 x_payee_line_id,
186 x_cl_status,
187 x_created_during,
188 x_commission_rate,
189 decode(x_hold_flag, FND_API.G_MISS_CHAR, 'N', x_hold_flag),
190 decode(x_paid_flag, FND_API.G_MISS_CHAR, 'N', x_paid_flag),
191 decode(x_payment_amount, FND_API.G_MISS_NUM, x_amount, x_payment_amount),
192 decode(x_attribute_category, FND_API.G_MISS_CHAR, null, x_attribute_category),
193 decode(x_attribute1, FND_API.G_MISS_CHAR, null, x_attribute1),
194 decode(x_attribute2, FND_API.G_MISS_CHAR, null, x_attribute2),
195 decode(x_attribute3, FND_API.G_MISS_CHAR, null, x_attribute3),
196 decode(x_attribute4, FND_API.G_MISS_CHAR, null, x_attribute4),
197 decode(x_attribute5, FND_API.G_MISS_CHAR, null, x_attribute5),
198 decode(x_attribute6, FND_API.G_MISS_CHAR, null, x_attribute6),
199 decode(x_attribute7, FND_API.G_MISS_CHAR, null, x_attribute7),
200 decode(x_attribute8, FND_API.G_MISS_CHAR, null, x_attribute8),
201 decode(x_attribute9, FND_API.G_MISS_CHAR, null, x_attribute9),
202 decode(x_attribute10, FND_API.G_MISS_CHAR, null, x_attribute10),
203 decode(x_attribute11, FND_API.G_MISS_CHAR, null, x_attribute11),
204 decode(x_attribute12, FND_API.G_MISS_CHAR, null, x_attribute12),
205 decode(x_attribute13, FND_API.G_MISS_CHAR, null, x_attribute13),
206 decode(x_attribute14, FND_API.G_MISS_CHAR, null, x_attribute14),
207 decode(x_attribute15, FND_API.G_MISS_CHAR, null, x_attribute15),
208 G_LAST_UPDATE_DATE,
209 G_LAST_UPDATED_BY,
210 G_LAST_UPDATE_LOGIN,
211 G_CREATION_DATE,
212 G_CREATED_BY
213 FROM DUAL);
214 END Insert_Record;
215
216 -- ----------------------------------------------------------------------------+
217 --
218 -- Procedure : Lock_Record
219 -- Description : Main lock procedure
220 -- lock db row after form record is changed
221 -- Note : Only called from the form.
222 --
223 -- ----------------------------------------------------------------------------+
224 PROCEDURE Lock_Record
225 (x_rowid VARCHAR2,
226 x_posting_detail_id NUMBER)
227 IS
228 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Record';
229 CURSOR c IS
230 SELECT *
231 FROM cn_posting_details
232 WHERE posting_detail_id = x_posting_detail_id
233 FOR UPDATE OF posting_detail_id NOWAIT;
234 RecInfo c%ROWTYPE;
235 BEGIN
236 OPEN c;
237 FETCH c INTO RecInfo;
238 IF c%NOTFOUND THEN
239 CLOSE c;
240 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 END IF;
243 CLOSE c;
244 IF RecInfo.posting_detail_id = x_posting_detail_id THEN
245 RETURN;
246 ELSE
247 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
248 APP_EXCEPTION.RAISE_EXCEPTION;
249 END IF;
250 END Lock_Record;
251
252 -- ----------------------------------------------------------------------------+
253 --
254 -- Procedure : Update_Record
255 -- Description : Main update procedure to update posting details
256 -- Notes : Only update status and loaded date
257 --
258 -- ----------------------------------------------------------------------------+
259 PROCEDURE Update_Record
260 (x_rowid VARCHAR2,
261 x_posting_detail_id NUMBER,
262 x_status VARCHAR2,
263 x_loaded_date DATE,
264 x_attribute_category VARCHAR2,
265 x_attribute1 VARCHAR2,
266 x_attribute2 VARCHAR2,
267 x_attribute3 VARCHAR2,
268 x_attribute4 VARCHAR2,
269 x_attribute5 VARCHAR2,
270 x_attribute6 VARCHAR2,
271 x_attribute7 VARCHAR2,
272 x_attribute8 VARCHAR2,
273 x_attribute9 VARCHAR2,
274 x_attribute10 VARCHAR2,
275 x_attribute11 VARCHAR2,
276 x_attribute12 VARCHAR2,
277 x_attribute13 VARCHAR2,
278 x_attribute14 VARCHAR2,
279 x_attribute15 VARCHAR2,
280 x_last_update_date DATE,
281 x_last_updated_by NUMBER,
282 x_last_update_login NUMBER)
283 IS
284 l_api_name CONSTANT VARCHAR2(30) := 'Update_Record';
285 CURSOR c is
286 SELECT status,
287 loaded_date,
288 attribute_category,
289 attribute1, attribute2, attribute3, attribute4, attribute5,
290 attribute6, attribute7, attribute8, attribute9, attribute10,
291 attribute11, attribute12, attribute13, attribute14, attribute15
292 FROM cn_posting_details
293 WHERE posting_detail_id = x_posting_detail_id;
294 l_oldrec c%rowtype;
295 BEGIN
296 open c;
297 fetch c into l_oldrec;
298 close c;
299
300 UPDATE cn_posting_details SET
301 status = decode(x_status, FND_API.G_MISS_CHAR,
302 l_oldrec.status, x_status),
303 loaded_date = decode(x_loaded_date, FND_API.G_MISS_DATE,
304 l_oldrec.loaded_date, x_loaded_date),
305 attribute_category = decode(x_attribute_category,
306 FND_API.G_MISS_CHAR,
307 l_oldrec.attribute_category,
308 x_attribute_category),
309 attribute1 = decode(x_attribute1, FND_API.G_MISS_CHAR,
310 l_oldrec.attribute1, x_attribute1),
311 attribute2 = decode(x_attribute2, FND_API.G_MISS_CHAR,
312 l_oldrec.attribute2, x_attribute2),
313 attribute3 = decode(x_attribute3, FND_API.G_MISS_CHAR,
314 l_oldrec.attribute3, x_attribute3),
315 attribute4 = decode(x_attribute4, FND_API.G_MISS_CHAR,
316 l_oldrec.attribute4, x_attribute4),
317 attribute5 = decode(x_attribute5, FND_API.G_MISS_CHAR,
318 l_oldrec.attribute5, x_attribute5),
319 attribute6 = decode(x_attribute6, FND_API.G_MISS_CHAR,
320 l_oldrec.attribute6, x_attribute6),
321 attribute7 = decode(x_attribute7, FND_API.G_MISS_CHAR,
322 l_oldrec.attribute7, x_attribute7),
323 attribute8 = decode(x_attribute8, FND_API.G_MISS_CHAR,
324 l_oldrec.attribute8, x_attribute8),
325 attribute9 = decode(x_attribute9, FND_API.G_MISS_CHAR,
326 l_oldrec.attribute9, x_attribute9),
327 attribute10 = decode(x_attribute10, FND_API.G_MISS_CHAR,
328 l_oldrec.attribute10, x_attribute10),
329 attribute11 = decode(x_attribute11, FND_API.G_MISS_CHAR,
333 attribute13 = decode(x_attribute13, FND_API.G_MISS_CHAR,
330 l_oldrec.attribute11, x_attribute11),
331 attribute12 = decode(x_attribute12, FND_API.G_MISS_CHAR,
332 l_oldrec.attribute12, x_attribute12),
334 l_oldrec.attribute13, x_attribute13),
335 attribute14 = decode(x_attribute14, FND_API.G_MISS_CHAR,
336 l_oldrec.attribute14, x_attribute14),
337 attribute15 = decode(x_attribute15, FND_API.G_MISS_CHAR,
338 l_oldrec.attribute15, x_attribute15),
339 last_update_date = G_LAST_UPDATE_DATE,
340 last_updated_by = G_LAST_UPDATED_BY,
341 last_update_login = G_LAST_UPDATE_LOGIN;
342 END Update_Record;
343 -- ----------------------------------------------------------------------------+
344 --
345 -- Procedure : Begin_Record
346 -- Description : This PRIVATE table handler procedure calls the
347 -- appropriate private procedures depending on the value of
348 -- X_Operation.
349 -- Calls :
350 --
351 -- Notes :
352 --
353 --
354 -- ----------------------------------------------------------------------------+
355 PROCEDURE Begin_Record
356 (x_operation IN VARCHAR2,
357 x_rowid IN OUT VARCHAR2,
358 x_posting_detail_rec IN OUT posting_detail_rec_type,
359 x_program_type IN VARCHAR2)
360 IS
361 l_api_name CONSTANT VARCHAR2(30) := 'Begin_Record';
362 l_api_version CONSTANT NUMBER := 1.0;
363 BEGIN
364 IF x_operation = 'INSERT' THEN
365 --dbms_output.put_line('in API about to call TH ');
366 Insert_Record
367 (x_rowid,
368 x_posting_detail_rec.posting_detail_id,
369 x_posting_detail_rec.posting_batch_id,
370 x_posting_detail_rec.posting_type,
371 x_posting_detail_rec.trx_type,
372 x_posting_detail_rec.payee_salesrep_id,
373 x_posting_detail_rec.role_id,
374 x_posting_detail_rec.incentive_type_code,
375 x_posting_detail_rec.credit_type_id,
376 x_posting_detail_rec.pay_period_id,
377 x_posting_detail_rec.amount,
378 x_posting_detail_rec.commission_header_id,
379 x_posting_detail_rec.commission_line_id,
380 x_posting_detail_rec.srp_plan_assign_id,
381 x_posting_detail_rec.quota_id,
382 x_posting_detail_rec.status,
383 x_posting_detail_rec.loaded_date,
384 x_posting_detail_rec.processed_date,
385 x_posting_detail_rec.credited_salesrep_id,
386 x_posting_detail_rec.processed_period_id,
387 x_posting_detail_rec.quota_rule_id,
388 x_posting_detail_rec.event_factor,
389 x_posting_detail_rec.payment_factor,
390 x_posting_detail_rec.quota_factor,
391 x_posting_detail_rec.pending_status,
392 x_posting_detail_rec.input_achieved,
393 x_posting_detail_rec.rate_tier_id,
394 x_posting_detail_rec.payee_line_id,
395 x_posting_detail_rec.cl_status,
396 x_posting_detail_rec.created_during,
397 x_posting_detail_rec.commission_rate,
398 x_posting_detail_rec.hold_flag,
399 x_posting_detail_rec.paid_flag,
400 x_posting_detail_rec.payment_amount,
401 x_posting_detail_rec.attribute_category,
402 x_posting_detail_rec.attribute1 ,
403 x_posting_detail_rec.attribute2 ,
404 x_posting_detail_rec.attribute3 ,
405 x_posting_detail_rec.attribute4 ,
406 x_posting_detail_rec.attribute5 ,
407 x_posting_detail_rec.attribute6 ,
408 x_posting_detail_rec.attribute7 ,
409 x_posting_detail_rec.attribute8 ,
410 x_posting_detail_rec.attribute9 ,
411 x_posting_detail_rec.attribute10 ,
412 x_posting_detail_rec.attribute11 ,
413 x_posting_detail_rec.attribute12 ,
414 x_posting_detail_rec.attribute13 ,
415 x_posting_detail_rec.attribute14 ,
416 x_posting_detail_rec.attribute15 ,
417 x_posting_detail_rec.last_update_date,
418 x_posting_detail_rec.last_updated_by,
419 x_posting_detail_rec.last_update_login,
420 x_posting_detail_rec.creation_date,
421 x_posting_detail_rec.created_by);
422
423 --dbms_output.put_line('in API after TH ');
424 ELSIF x_operation = 'UPDATE' THEN
425 Update_Record
426 (x_rowid,
427 x_posting_detail_rec.posting_detail_id,
428 x_posting_detail_rec.status,
429 x_posting_detail_rec.loaded_date,
430 x_posting_detail_rec.attribute_category,
431 x_posting_detail_rec.attribute1 ,
432 x_posting_detail_rec.attribute2 ,
433 x_posting_detail_rec.attribute3 ,
434 x_posting_detail_rec.attribute4 ,
435 x_posting_detail_rec.attribute5 ,
436 x_posting_detail_rec.attribute6 ,
437 x_posting_detail_rec.attribute7 ,
438 x_posting_detail_rec.attribute8 ,
439 x_posting_detail_rec.attribute9 ,
440 x_posting_detail_rec.attribute10 ,
441 x_posting_detail_rec.attribute11 ,
442 x_posting_detail_rec.attribute12 ,
443 x_posting_detail_rec.attribute13 ,
444 x_posting_detail_rec.attribute14 ,
445 x_posting_detail_rec.attribute15 ,
446 x_posting_detail_rec.last_update_date,
447 x_posting_detail_rec.last_updated_by,
448 x_posting_detail_rec.last_update_login);
449 ELSIF x_operation = 'LOCK' THEN
450 Lock_Record
451 (x_rowid,
452 x_posting_detail_rec.posting_detail_id);
453 END IF;
454 END Begin_Record;
455 END CN_PREPOSTDETAILS;