[Home] [Help]
PACKAGE BODY: APPS.CN_PREPOST_PVT
Source
1 PACKAGE BODY CN_PREPOST_PVT AS
2 -- $Header: cnvpostb.pls 120.1 2005/08/08 09:57:36 ymao noship $
3
4 -- Posting_Status Values
5 C_POSTED CONSTANT VARCHAR2(30) := 'POSTED';
6 C_UNPOSTED CONSTANT VARCHAR2(30) := 'UNPOSTED';
7 C_REVERTED CONSTANT VARCHAR2(30) := 'REVERTED';
8 -- Default Posting Detail.Status Value
9 C_UNLOADED CONSTANT VARCHAR2(30) := 'UNLOADED';
10 -- Incentive Type Codes
11 C_COMMISSION CONSTANT VARCHAR2(30) := 'COMMISSION';
12 C_BONUS CONSTANT VARCHAR2(30) := 'BONUS';
13 C_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL';
14 -- Posting Types
15 C_NON_REC CONSTANT VARCHAR2(30) := 'NON_REC';
16 C_REC CONSTANT VARCHAR2(30) := 'REC';
17 C_TO_REC CONSTANT VARCHAR2(30) := 'TO_REC';
18 C_EXPENSE CONSTANT VARCHAR2(30) := 'EXPENSE';
19 -- Trx Types
20 C_COMM_NREC CONSTANT VARCHAR2(30) := 'COMM_NREC';
21 C_COMM_REC CONSTANT VARCHAR2(30) := 'COMM_REC';
22 C_COMM_TO_REC CONSTANT VARCHAR2(30) := 'COMM_TO_REC';
23 C_BONUS_REC CONSTANT VARCHAR2(30) := 'BONUS_REC';
24 C_BONUS_TO_REC CONSTANT VARCHAR2(30) := 'BONUS_TO_REC';
25 C_MANUAL_EXPENSE CONSTANT VARCHAR2(30) := 'MANUAL_EXPENSE';
26 -- Designates creation by a system process
27 C_SYS_POST_PROCESS CONSTANT VARCHAR2(30) := 'SYS_PREPOST_PROCESS';
28
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PREPOST_PVT';
30 G_ROWID VARCHAR2(30) := NULL;
31 G_PROGRAM_TYPE VARCHAR2(30);
32 -- ============================================================================
33 -- Procedure : Initialize_Batch()
34 -- Description : This PUBLIC procedure is used to get the ID of the Posting
35 -- Batch. Each session may have one batch ID. If this procedure
36 -- is called and one does not exist, then a batch ID must be
37 -- initialized privately.
38 -- The batch name is derived from date, session ID.
39 -- Calls :
40 -- ============================================================================
41 PROCEDURE Initialize_Batch
42 ( p_api_version IN NUMBER ,
43 p_init_msg_list IN VARCHAR2 ,
44 p_commit IN VARCHAR2 ,
45 p_validation_level IN NUMBER ,
46 x_return_status OUT NOCOPY VARCHAR2 ,
47 x_msg_count OUT NOCOPY NUMBER ,
48 x_msg_data OUT NOCOPY VARCHAR2 ,
49 x_loading_status OUT NOCOPY VARCHAR2 ,
50 p_loading_status IN VARCHAR2 ,
51 p_posting_batch_rec IN OUT NOCOPY CN_PREPOSTBATCHES.posting_batch_rec_type,
52 x_status OUT NOCOPY VARCHAR2
53 )
54 IS
55 l_api_name CONSTANT VARCHAR2(30) := 'Initialize_Batch';
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_count NUMBER;
58 BEGIN
59 -- Standard Start of API savepoint
60 SAVEPOINT Initialize_Batch;
61 -- Standard call to check for call compatibility.
62 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
63 p_api_version ,
64 l_api_name ,
65 G_PKG_NAME ) THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list ) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72 -- Initialize API return status to success
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74 x_loading_status := 'CN_INSERTED';
75 -- API body
76 -- Check a global batch ID exists for the session
77 -- Do nothing if global batch ID already exists
78 -- Create batch if global batch ID does not exist for session
79 IF (CN_PREPOSTBATCHES.G_BATCH_ID is null) THEN
80 -- Get next batch sequence ID
81 CN_PREPOSTBATCHES.Get_UID( p_posting_batch_rec.posting_batch_id );
82 -- Return and Set global batch ID
83 CN_PREPOSTBATCHES.G_BATCH_ID := p_posting_batch_rec.posting_batch_id;
84 ELSE
85 x_status := 'BATCH ALREADY EXISTS';
86 -- Validate the batch is not loaded
87 IF p_posting_batch_rec.load_status = 'LOADED' THEN
88 x_status := 'BATCH IS ALREADY LOADED';
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91 END IF;
92 -- End of API body.
93 -- Standard check of p_commit.
94 IF FND_API.To_Boolean( p_commit ) THEN
95 COMMIT WORK;
96 END IF;
97 -- Standard call to get message count and if count is 1, get message info.
98 FND_MSG_PUB.Count_And_Get
99 ( p_count => x_msg_count ,
100 p_data => x_msg_data
101 );
102 EXCEPTION
103 WHEN FND_API.G_EXC_ERROR THEN
104 ROLLBACK TO Initialize_Batch;
105 x_return_status := FND_API.G_RET_STS_ERROR ;
106 FND_MSG_PUB.Count_And_Get
107 ( p_count => x_msg_count ,
108 p_data => x_msg_data
109 );
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111 ROLLBACK TO Initialize_Batch;
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
113 FND_MSG_PUB.Count_And_Get
114 ( p_count => x_msg_count ,
115 p_data => x_msg_data
116 );
117 WHEN OTHERS THEN
118 ROLLBACK TO Initialize_Batch;
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
120 IF FND_MSG_PUB.Check_Msg_Level
121 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
122 FND_MSG_PUB.Add_Exc_Msg
123 ( G_PKG_NAME,
124 l_api_name);
125 END IF;
126 FND_MSG_PUB.Count_And_Get
127 ( p_count => x_msg_count,
128 p_data => x_msg_data
129 );
130 END Initialize_Batch;
131 -- ============================================================================
132 -- Procedure : Terminate_Batch
133 -- Description : This PRIVATE procedure is used to terminate the current
134 -- batch for a session.
135 -- This is done by setting CN_PREPOSTBATCHES.G_BATCH_ID and
136 -- G_BATCH_NAME to NULL.
137 -- Calls :
138 -- ============================================================================
139 PROCEDURE Terminate_Batch IS
140 BEGIN
141 -- Set CN_PREPOSTBATCHES.G_BATCH_ID to NULL
142 CN_PREPOSTBATCHES.G_BATCH_ID := NULL;
143 END Terminate_Batch;
144 -- ============================================================================
145 -- Procedure : Validate_PrePostDetails
146 -- Description : This procedure is used to validate the parameters that
147 -- have been passed to create a posting detail.
148 -- This procedure assumes that information gathered from
149 -- CN_COMMISSION_LINES is already valid.
150 -- Note : Procedure has been overloaded to accommodate a
151 -- commission_line_id and a posting detail record type.
152 -- Calls :
153 -- CAN YOU REVERT A NON COMMISSION LINE BASED TRX?
154 -- ============================================================================
155 PROCEDURE Validate_PrePostDetails
156 ( x_return_status OUT NOCOPY VARCHAR2 ,
157 x_msg_count OUT NOCOPY NUMBER ,
158 x_msg_data OUT NOCOPY VARCHAR2 ,
159 p_create_mode IN VARCHAR2 := 'NEW' ,
160 p_posting_detail_rec IN CN_PREPOSTDETAILS.posting_detail_rec_type ,
161 p_loading_status IN VARCHAR2 ,
162 x_loading_status OUT NOCOPY VARCHAR2 ,
163 x_status OUT NOCOPY VARCHAR2
164 )
165 IS
166 l_api_name CONSTANT VARCHAR2(30) := 'Validate_PrePostDetails';
167 l_cl_status VARCHAR2(30);
168 l_posting_status VARCHAR2(30);
169 CURSOR get_comm_line IS
170 SELECT status, NVL(posting_status, C_UNPOSTED)
171 FROM cn_commission_lines
172 WHERE commission_line_id = p_posting_detail_rec.commission_line_id;
173 BEGIN
174 -- Initialize API return status to success
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176 -- Check for null parameters
177 IF ( (cn_api.chk_null_num_para
178 (p_num_para => p_posting_detail_rec.commission_line_id,
179 p_obj_name => 'Commission Line ID',
180 p_loading_status => x_loading_status,
181 x_loading_status => x_loading_status) ) = FND_API.G_TRUE ) THEN
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184 -- 1. Validate commission line exists
185 OPEN get_comm_line;
186 FETCH get_comm_line INTO l_cl_status, l_posting_status;
187 IF get_comm_line%ROWCOUNT = 0 THEN
188 x_status := 'COMMISSION LINE DOES NOT EXIST';
189 RAISE FND_API.G_EXC_ERROR;
190 ELSIF get_comm_line%ROWCOUNT = 1 THEN
191 x_status := 'A COMMISSION LINE EXISTS';
192 END IF;
193 CLOSE get_comm_line;
194 -- 2. Check commission line has been calculated
195 IF l_cl_status <> 'CALC' THEN
196 x_status := 'COMMISSION LINE IS NOT OF STATUS CALC';
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199 -- 3. Check p_create_mode valid choices
200 IF p_create_mode NOT IN ('NEW', 'REVERT') THEN
201 x_status := 'INVALID CREATE MODE';
202 RAISE FND_API.G_EXC_ERROR;
203 ELSE
204 x_status := 'CREATE MODE EXISTS';
205 END IF;
206 -- 4. Check commission line is not posted or reverted
207 -- previously for a "New" transaction
208 IF (l_posting_status in (C_POSTED, C_REVERTED) AND p_create_mode = 'NEW') THEN
209 x_status := 'CANNOT CREATE NEW FOR COMMISSION LINE HAS BEEN POSTED OR REVERTED.';
210 RAISE FND_API.G_EXC_ERROR;
211 END IF;
212 -- 5. Check commission line is not posted or reverted
213 -- previously for a "Revert" transaction
214 IF (l_posting_status in (C_UNPOSTED, C_REVERTED) AND p_create_mode = 'REVERT') THEN
215 x_status := 'CANNOT CREATE REVERT FOR COMMISSION LINE HAS BEEN POSTED OR REVERTED ALREADY.';
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218 -- End of Validate Posting
219 -- Standard call to get message count and if count is 1, get message info.
220 FND_MSG_PUB.Count_And_Get
221 ( p_count => x_msg_count ,
222 p_data => x_msg_data ,
223 p_encoded => FND_API.G_FALSE
224 );
225 EXCEPTION
226 WHEN FND_API.G_EXC_ERROR THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 x_loading_status := 'UNEXPECTED_ERR';
231 WHEN OTHERS THEN
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 x_loading_status := 'UNEXPECTED_ERR';
234 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
235 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
236 END IF;
237 END Validate_PrePostDetails;
238
239 -- ============================================================================
240 -- Procedure : Create_From_CommLine
241 -- Description : This PUBLIC procedure is used to create posting batches
242 -- and details from a commission line id.
243 -- NEW create mode will update commission line
244 -- Calls : Validate_From_CommLines()
245 -- ============================================================================
246 PROCEDURE Create_From_CommLine
247 (p_api_version IN NUMBER ,
248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
249 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
251 x_return_status OUT NOCOPY VARCHAR2 ,
252 x_msg_count OUT NOCOPY NUMBER ,
253 x_msg_data OUT NOCOPY VARCHAR2 ,
254 p_create_mode IN VARCHAR2 := 'NEW' ,
255 p_commission_line_id IN NUMBER) IS
256
257 l_api_name CONSTANT VARCHAR2(30) := 'Create_From_CommLine';
258 l_api_version CONSTANT NUMBER := 1.0;
259 l_loading_status VARCHAR2(4000);
260 l_status VARCHAR2(4000);
261 l_pmt_trans_rec CN_PMT_TRANS_PKG.pmt_trans_rec_type;
262 l_posting_batch_rec CN_PREPOSTBATCHES.posting_batch_rec_type;
263 l_profile_value VARCHAR2(1);
264
265 CURSOR get_comm_line_rec IS
266 (SELECT CN_API.G_MISS_ID payment_transaction_id,
267 CN_PREPOSTBATCHES.G_BATCH_ID posting_batch_id,
268 --C_EXPENSE posting_type,
269
270 cl.credited_salesrep_id,
271 cl.credited_salesrep_id payee_salesrep_id,
272 cl.quota_id,
273 cl.pay_period_id,
274 pe.incentive_type_code,
275 cl.credit_type_id,
276 NULL, -- payrun_id
277 nvl(cl.commission_amount,0) amount,
278 nvl(cl.commission_amount,0) payment_amount, -- default
279 'N' hold_flag, -- default N
280 'N' paid_flag, -- default N
281 'N' waive_flag, -- default N
282 'N' recoverable_flag, -- default N
283 cl.commission_header_id,
284 cl.commission_line_id,
285 null, -- pay_element_type_id
286 cl.srp_plan_assign_id,
287 cl.processed_date,
288 cl.processed_period_id,
289 cl.quota_rule_id,
290 cl.event_factor,
291 cl.payment_factor,
292 cl.quota_factor,
293 cl.input_achieved,
294 cl.rate_tier_id,
295 cl.payee_line_id,
296 cl.commission_rate,
297 cl.trx_type,
298 cl.role_id,
299 pe.expense_account_id expense_ccid,
300 pe.liability_account_id liability_ccid,
301 NULL, --cl.attribute_category,
302 NULL, --cl.attribute1,
303 null, --cl.attribute2,
304 null, --cl.attribute3,
305 null, --cl.attribute4,
306 null, --cl.attribute5,
307 null, --cl.attribute6,
308 null, --cl.attribute7,
309 null, --cl.attribute8,
310 null, --cl.attribute9,
311 null, --cl.attribute10,
312 null, --cl.attribute11,
313 null, --cl.attribute12,
314 null, --cl.attribute13,
315 null, --cl.attribute14,
316 null, --cl.attribute15
317 cl.org_id,
318 0
319
320
321 /*C_UNLOADED status, --default UNLOADED
322 FND_API.G_MISS_DATE loaded_date,
323 cl.pending_status,
324 cl.status cl_status,
325 cl.created_during,
326 FND_GLOBAL.USER_ID created_by,
327 SYSDATE creation_date,
328 FND_GLOBAL.LOGIN_ID last_update_login,
329 SYSDATE last_update_date,
330 FND_GLOBAL.USER_ID last_updated_by*/
331 FROM cn_commission_lines_all cl,
332 -- cn_srp_plan_assigns srcp,
333 --cn_srp_payee_assigns srpa, removed for payee assigns bug #2495614
334 -- cn_role_plans rcp,
335 cn_quotas_all pe
336 WHERE cl.commission_line_id = p_commission_line_id
337 AND cl.quota_id = pe.quota_id
338 AND cl.srp_payee_assign_id IS NULL)
339 UNION --this is added for assign payees for fixing bug#2495614
340 (SELECT CN_API.G_MISS_ID payment_transaction_id,
341 CN_PREPOSTBATCHES.G_BATCH_ID posting_batch_id,
342 payee.payee_id credited_salesrep_id,
343 payee.payee_id payee_salesrep_id,
344 cl.quota_id,
345 cl.pay_period_id,
346 pe.incentive_type_code,
347 cl.credit_type_id,
348 NULL, -- payrun_id
349 nvl(cl.commission_amount,0) amount,
350 nvl(cl.commission_amount,0) payment_amount, -- default
351 'N' hold_flag, -- default N
352 'N' paid_flag, -- default N
353 'N' waive_flag, -- default N
354 'N' recoverable_flag, -- default N
355 cl.commission_header_id,
356 cl.commission_line_id,
357 null, -- pay_element_type_id
358 cl.srp_plan_assign_id,
359 cl.processed_date,
360 cl.processed_period_id,
361 cl.quota_rule_id,
362 cl.event_factor,
363 cl.payment_factor,
364 cl.quota_factor,
365 cl.input_achieved,
366 cl.rate_tier_id,
367 cl.payee_line_id,
368 cl.commission_rate,
369 cl.trx_type,
370 54,--cl.role_id
371 pe.expense_account_id expense_ccid,
372 pe.liability_account_id liability_ccid,
373 NULL, --cl.attribute_category,
374 NULL, --cl.attribute1,
375 null, --cl.attribute2,
376 null, --cl.attribute3,
377 null, --cl.attribute4,
378 null, --cl.attribute5,
379 null, --cl.attribute6,
380 null, --cl.attribute7,
381 null, --cl.attribute8,
382 null, --cl.attribute9,
383 null, --cl.attribute10,
384 null, --cl.attribute11,
385 null, --cl.attribute12,
386 null, --cl.attribute13,
387 null, --cl.attribute14,
388 null, --cl.attribute15
389 cl.org_id,
390 0
391 FROM cn_commission_lines_all cl,
392 -- cn_srp_plan_assigns srcp,
393 cn_srp_payee_assigns_all payee,
394 -- cn_role_plans rcp,
395 cn_quotas_all pe
396 WHERE cl.commission_line_id = p_commission_line_id
397 AND cl.quota_id = pe.quota_id
398 AND cl.srp_payee_assign_id IS NOT NULL
399 AND payee.srp_payee_assign_id = cl.srp_payee_assign_id);
400
401
402 -- AND cl.srp_plan_assign_id = srcp.srp_plan_assign_id
403 -- AND cl.srp_payee_assign_id = srpa.srp_payee_assign_id (+) removed for bug #2495614
404 -- AND srcp.role_id = cl.role_id
405 -- AND srcp.salesrep_id = cl.credited_salesrep_id
406 -- AND cl.processed_date BETWEEN srcp.start_date AND Nvl(srcp.end_date, cl.processed_date)
407 -- AND srcp.role_plan_id = rcp.role_plan_id (+)
408
409 BEGIN
410 -- Standard Start of API savepoint
411 SAVEPOINT Create_From_CommLine;
412 -- Standard call to check for call compatibility.
413 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
414 p_api_version ,
415 l_api_name ,
416 G_PKG_NAME ) THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END IF;
419 -- Initialize message list if p_init_msg_list is set to TRUE.
420 IF FND_API.to_Boolean( p_init_msg_list ) THEN
421 FND_MSG_PUB.initialize;
422 END IF;
423 -- Initialize API return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425 l_loading_status := 'CN_INSERTED';
426
427 -- API body
428 -- Validate incoming parameters
429
430 l_profile_value := fnd_profile.value('CN_PAY_BY_TRANSACTION');
431
432 If l_profile_value = 'Y' THEN
433
434
435 IF l_posting_batch_rec.posting_batch_id IS NULL THEN
436 -- Initialize batch is necessary since the batch ID is null
437 Initialize_Batch
438 (p_api_version => p_api_version,
439 p_init_msg_list => p_init_msg_list,
440 p_commit => p_commit,
441 p_validation_level => p_validation_level,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 x_loading_status => l_loading_status,
446 p_loading_status => l_loading_status,
447 p_posting_batch_rec => l_posting_batch_rec,
448 x_status => l_status
449 );
450 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453 END IF;
454
455 -- Build Payment Record record from Commission Line
456 OPEN get_comm_line_rec;
457 FETCH get_comm_line_rec INTO l_pmt_trans_rec;
458 --dbms_output.put_line('after fetch, status is '||l_status);
459 IF get_comm_line_rec%ROWCOUNT <> 1 THEN
460 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_COMMISSION_LINE');
461 FND_MESSAGE.SET_TOKEN('COMMISSION_LINE_ID',
462 TO_CHAR(p_commission_line_id));
463 FND_MSG_PUB.ADD;
464 l_loading_status := 'CN_INVALID_COMMISSION_LINE';
465 l_status := l_loading_status;
466 RAISE FND_API.G_EXC_ERROR;
467 END IF;
468 CLOSE get_comm_line_rec;
469
470 -- Calculate a negative amount for revert transactions
471
472 IF p_create_mode = 'REVERT' THEN
473 l_pmt_trans_rec.amount := l_pmt_trans_rec.amount * -1;
474 l_pmt_trans_rec.payment_amount := 0 - l_pmt_trans_rec.payment_amount;
475 END IF;
476
477 -- if nothing changes, delete the reversal created before this run of posting process
478 -- clku, 5/13/2002, commented this part out because the create_payment_worksheet method
479 -- takes care of deleting and creating NEW Payment Transaction already.
480
481 /*IF (p_create_mode = 'NEW') THEN
482 DELETE FROM cn_pmt_trans
483 WHERE posting_type = l_pmt_trans_rec.posting_type
484 AND payee_salesrep_id = l_pmt_trans_rec.payee_salesrep_id
485 AND role_id = l_pmt_trans_rec.role_id
486 AND credit_type_id = l_pmt_trans_rec.credit_type_id
487 AND pay_period_id = l_pmt_trans_rec.pay_period_id
488 AND amount = (0 - l_pmt_trans_rec.amount)
489 AND commission_header_id = l_pmt_trans_rec.commission_header_id
490 AND srp_plan_assign_id = l_pmt_trans_rec.srp_plan_assign_id
491 AND quota_id = l_pmt_trans_rec.quota_id
492 AND status = l_pmt_trans_rec.status
493 AND credited_salesrep_id = l_pmt_trans_rec.credited_salesrep_id
494 AND paid_flag = l_pmt_trans_rec.paid_flag
495 AND ROWNUM = 1;
496
497 -- if something changes, keep the reversal and create a new posting line
498 IF (SQL%notfound) THEN
499 CN_PMT_TRANS_PKG.Begin_Record
500 (x_pmt_trans_rec => l_pmt_trans_rec);
501 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504 END IF;
505 ELSE*/
506
507 -- insert record into CN_PAYMENT_TRANSACTIONS
508 CN_PMT_TRANS_PKG.Insert_Record(l_pmt_trans_rec);
509 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512 --END IF;
513
514 -- if NEW then Update commission line posting status to POSTED
515 /* IF p_create_mode = 'NEW' THEN
516 UPDATE cn_commission_lines
517 SET posting_status = C_POSTED
518 WHERE commission_line_id = p_commission_line_id;*/
519 -- REMOVE COMMENT WHEN UPDATED WITH COMM LINE API
520 -- IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
521 -- RAISE FND_API.G_EXC_ERROR;
522 -- END IF;
523 -- if REVERT then Update commission line posting status to REVERTED
524 IF p_create_mode = 'REVERT' THEN
525 UPDATE cn_commission_lines_all
526 SET posting_status = C_REVERTED
527 WHERE commission_line_id = p_commission_line_id;
528 END IF;
529 END IF;
530 -- End of API body.
531 -- Standard check of p_commit.
532 IF FND_API.To_Boolean( p_commit ) THEN
533 COMMIT WORK;
534 END IF;
535 -- Standard call to get message count and if count is 1, get message info
536 FND_MSG_PUB.Count_And_Get
537 ( p_count => x_msg_count ,
538 p_data => x_msg_data
539 );
540 EXCEPTION
541 WHEN FND_API.G_EXC_ERROR THEN
542 --dbms_output.put_line('EXP '||sqlerrm);
543 ROLLBACK TO Create_From_CommLine;
544 x_return_status := FND_API.G_RET_STS_ERROR ;
545 FND_MSG_PUB.Count_And_Get
546 ( p_count => x_msg_count ,
547 p_data => x_msg_data
548 );
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 --dbms_output.put_line('UNEXP '||sqlerrm);
551 ROLLBACK TO Create_From_CommLine;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553 FND_MSG_PUB.Count_And_Get
554 ( p_count => x_msg_count ,
555 p_data => x_msg_data
556 );
557 WHEN OTHERS THEN
558 --dbms_output.put_line('OTHERS '||sqlerrm);
559 ROLLBACK TO Create_From_CommLine;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
561 IF FND_MSG_PUB.Check_Msg_Level
562 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
563 FND_MSG_PUB.Add_Exc_Msg
564 ( G_PKG_NAME ,
565 l_api_name);
566 END IF;
567 FND_MSG_PUB.Count_And_Get
568 ( p_count => x_msg_count ,
569 p_data => x_msg_data
570 );
571 END Create_From_CommLine;
572
573
574 -- ============================================================================
575 -- Procedure : Create_PrePostDetails
576 -- Description : This PUBLIC procedure creates posting trx from a table of
577 -- posting details.
578 -- Specifically it is used to create posting details derived
579 -- from a single "paid" payment worksheet e.g., called by
580 -- PrePost_PayWorksheets().
581 -- These posting details are not derived from commission
582 -- lines. The source of posting is a posting_detail_rec_tbl.
583 -- Assumes batch has already been created.
584 -- Calls : Get_Batch_ID()
585 -- Validate_PrePostDetails()
586 -- CN_PREPOSTDETAILS.Begin_Record()
587 -- Note : This procedure assumes that CN_PREPOSTBATCHES.G_BATCH_ID
588 -- has already been set via the Initialize_Batch()
589 -- public procedure.
590 -- ============================================================================
591 PROCEDURE Create_PrePostDetails
592 ( p_api_version IN NUMBER ,
593 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
594 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
595 p_validation_level IN NUMBER :=
596 FND_API.G_VALID_LEVEL_FULL ,
597 x_return_status OUT NOCOPY VARCHAR2 ,
598 x_msg_count OUT NOCOPY NUMBER ,
599 x_msg_data OUT NOCOPY VARCHAR2 ,
600 p_posting_detail_rec_tbl IN OUT NOCOPY CN_PREPOSTDETAILS.posting_detail_rec_tbl_type
601 )
602 IS
603 l_api_name CONSTANT VARCHAR2(30) := 'Create_PrePostDetails';
604 l_api_version CONSTANT NUMBER := 1.0;
605 l_loading_status VARCHAR2(30);
606 l_status VARCHAR2(30);
607 BEGIN
608 -- Standard Start of API savepoint
609 SAVEPOINT Create_PrePostDetails;
610 -- Standard call to check for call compatibility.
611 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
612 p_api_version ,
613 l_api_name ,
614 G_PKG_NAME ) THEN
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617 -- Initialize message list if p_init_msg_list is set to TRUE.
618 IF FND_API.to_Boolean( p_init_msg_list ) THEN
619 FND_MSG_PUB.initialize;
620 END IF;
621 -- Initialize API return status to success
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623 l_loading_status := 'CN_INSERTED';
624 -- API body
625 -- Process a set of posting details
626 FOR i IN 1 .. p_posting_detail_rec_tbl.COUNT LOOP
627 SAVEPOINT Create_PayWorksheets_Loop;
628 -- Validate incoming parameters
629 /*Validate_PrePostDetails(
630 x_return_status => x_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 p_loading_status => l_loading_status,
634 x_loading_status => l_loading_status,
635 p_posting_detail_rec => p_posting_detail_rec_tbl(i),
636 x_status => l_status
637 );
638 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
639 RAISE FND_API.G_EXC_ERROR;
640 END IF;*/
641
642 -- Create Posting Detail through table handlers
643 CN_PREPOSTDETAILS.Begin_Record(
644 x_operation => 'INSERT',
645 x_rowid => G_ROWID,
646 x_posting_detail_rec => p_posting_detail_rec_tbl(i),
647 x_program_type => G_PROGRAM_TYPE
648 );
649 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652 l_status := 'CREATED POSTING DETAIL '||TO_CHAR(i);
653 END LOOP;
654 -- End of API body.
655 -- Standard check of p_commit.
656 IF FND_API.To_Boolean( p_commit ) THEN
657 COMMIT WORK;
658 END IF;
659 -- Standard call to get message count and if count is 1, get message info.
660 FND_MSG_PUB.Count_And_Get
661 ( p_count => x_msg_count ,
662 p_data => x_msg_data
663 );
664 EXCEPTION --create posting detail
665 WHEN FND_API.G_EXC_ERROR THEN
666 ROLLBACK TO Create_PrePostDetails_Loop;
667 x_return_status := FND_API.G_RET_STS_ERROR ;
668 FND_MSG_PUB.Count_And_Get
669 ( p_count => x_msg_count ,
670 p_data => x_msg_data
671 );
672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
673 ROLLBACK TO Create_PrePostDetails;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
675 FND_MSG_PUB.Count_And_Get
676 ( p_count => x_msg_count ,
677 p_data => x_msg_data
678 );
679 WHEN OTHERS THEN
680 ROLLBACK TO Create_PrePostDetails;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
682 IF FND_MSG_PUB.Check_Msg_Level
683 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
684 FND_MSG_PUB.Add_Exc_Msg
685 ( G_PKG_NAME ,
686 l_api_name);
687 END IF;
688 FND_MSG_PUB.Count_And_Get
689 ( p_count => x_msg_count ,
690 p_data => x_msg_data
691 );
692 END Create_PrePostDetails;
693
694 -- ============================================================================
695 -- Procedure : PrePost_PayWorksheets
696 -- Description : This PUBLIC procedure creates processes the posting of
697 -- all "paid" worksheets between a posting batch date range
698 -- Calls : Create_PrePostDetails()
699 -- ============================================================================
700 PROCEDURE PrePost_PayWorksheets
701 ( p_api_version IN NUMBER ,
702 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
703 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
704 p_validation_level IN NUMBER :=
705 FND_API.G_VALID_LEVEL_FULL ,
706 x_return_status OUT NOCOPY VARCHAR2 ,
707 x_msg_count OUT NOCOPY NUMBER ,
708 x_msg_data OUT NOCOPY VARCHAR2
709 )
710 IS
711 l_api_name CONSTANT VARCHAR2(30) := 'PrePost_PayWorksheets';
712 l_api_version CONSTANT NUMBER := 1.0;
713 l_loading_status VARCHAR2(30);
714 l_status VARCHAR2(30);
715 l_pdet_rec_tbl CN_PREPOSTDETAILS.posting_detail_rec_tbl_type;
716 l_tbl_empty CN_PREPOSTDETAILS.posting_detail_rec_tbl_type;
717 i NUMBER;
718
719 -- Unposted worksheets belonging to PAID payruns are retrieved by
720 -- posting batch start and end dates.
721
722 CURSOR get_pay_ws IS
723 SELECT ws.PAYMENT_WORKSHEET_ID ,
724 -- ws.PAYRUN_ID ,
725 ws.SALESREP_ID , -- payee_salesrep_id
726 ws.ROLE_ID ,
727 ws.CREDIT_TYPE_ID ,
728 pr.PAY_DATE , -- posted_date
729 pr.PAY_PERIOD_ID ,
730 ws.COMM_NREC COMM_NREC,
731 ws.DRAW_PAID COMM_REC, -- comm rec
732 ws.COMM_DRAW COMM_TO_REC, -- to recov w pp
733 ws.REG_BONUS_REC BONUS_REC,
734 ws.REG_BONUS_TO_REC BONUS_TO_REC
735 -- ws.POSTING_STATUS
736 FROM cn_payment_worksheets ws,
737 cn_payruns pr,
738 cn_posting_batches pb,
739 cn_period_statuses pp
740 WHERE pr.payrun_id = ws.payrun_id
741 and pr.pay_period_id = pp.period_id
742 AND pp.start_date BETWEEN pb.start_date AND pb.end_date
743 AND ( pr.status = 'PAID'
744 OR pr.status = 'PAID_WITH_RETURNS')
745 AND ws.posting_status = C_UNPOSTED
746 AND pb.load_status = C_UNLOADED
747 AND pb.posting_batch_id = CN_PREPOSTBATCHES.G_BATCH_ID;
748
749 --Manual Expense cursor declaration
750 CURSOR get_ws_manual_exp ( v_payment_worksheet_id IN NUMBER ) IS
751 SELECT wsb.quota_id ,
752 wsb.srp_plan_assign_id ,
753 wsb.amount
754 FROM cn_worksheet_bonuses wsb
755 WHERE wsb.payment_worksheet_id = v_payment_worksheet_id;
756
757 -- Cursor used to pick up payruns and update their posting statuses STATUS to
758 -- POSTED if all the corresponding payment worksheets are posted successfully
759 cursor get_posted_payruns is
760 select pr.payrun_id
761 from cn_payruns pr,
762 cn_period_statuses ps,
763 cn_posting_batches pb
764 where pr.pay_period_id = ps.period_id
765 and (pr.status = 'PAID' or pr.status = 'PAID_WITH_RETURNS')
766 and pb.load_status = C_UNLOADED
767 and pb.posting_batch_id = CN_PREPOSTBATCHES.G_BATCH_ID
768 and ps.start_date between pb.start_date and pb.end_date
769 and not exists ( select 1
770 from cn_payment_worksheets ws
771 where ws.payrun_id = pr.payrun_id
772 and ws.posting_status = C_UNPOSTED
773 );
774 BEGIN
775 -- Standard Start of API savepoint
776 SAVEPOINT PrePost_PayWorksheets;
777 -- Standard call to check for call compatibility.
778 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
779 p_api_version ,
780 l_api_name ,
781 G_PKG_NAME ) THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784 -- Initialize message list if p_init_msg_list is set to TRUE.
785 IF FND_API.to_Boolean( p_init_msg_list ) THEN
786 FND_MSG_PUB.initialize;
787 END IF;
788 -- Initialize API return status to success
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790 -- API body
791
792 -- Validate a CN_PREPOSTBATCHES.G_BATCH_ID exists
793 -- The assumption is that the batch already exists and with parameters
794 -- to drive posting.
795
796 IF CN_PREPOSTBATCHES.G_BATCH_ID is NULL THEN
797 FND_MESSAGE.SET_NAME('CN', 'CN_POSTING_BATCH_UNINITIALIZED');
798 --FND_MESSAGE.SET_TOKEN('COMMISSION_LINE_ID', TO_CHAR(p_commission_line_id));
799 FND_MSG_PUB.ADD;
800 l_loading_status := 'CN_POSTING_BATCH_UNINITIALIZED';
801 l_status := 'EARLY EXIT BECAUSE POSTING BATCH NOT INITIALIZED';
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804 -- Get payment worksheets eligible for posting
805 -- OPEN get_pay_ws;
806 FOR l_pay_ws_rec IN get_pay_ws LOOP
807 --dbms_output.put_line('Loop depth '||i);
808 SAVEPOINT PrePost_PayWorksheets_Loop;
809 l_pdet_rec_tbl := l_tbl_empty;
810 l_loading_status := 'CN_INSERTED'; -- Set worksheet as properly inserted
811 i := 1;
812 -- ===================================================================
813 -- Payment worksheets have 6 types of posting transactions which may
814 -- be derived outside of commission lines.These include the following:
815 -- COMMISSION Incentive Type
816 -- 1. COMM NRec (Adjust to Pay)
817 -- 2. COMM Rec (only commission incentive types have PPs)
818 -- 3. COMM To Recover
819 -- BONUS Incentive Type
820 -- 4. BONUS Rec
821 -- 5. BONUS To Recover (will recover from comm if possible)
822 -- MANUAL Incentive Type
823 -- 6. MANUAL Expense e.g., Earned and should not be categorized as
824 -- "NRec" in the worksheet
825 -- (e.g., Manual incentive type from CN_WORKSHEET_BONUSES
826 -- thru a join from CN_WORKSHEETS)
827 -- Identify each posting trx type from the many buckets in the worksheet
828 -- and loop thru build posting details to match and committing a
829 -- worksheet at a time.
830 -- ===================================================================
831 -- 1. COMM NRec (Adjust to Pay)
832 IF l_pay_ws_rec.COMM_NREC <> 0 THEN
833 l_pdet_rec_tbl(i).posting_type := C_NON_REC;
834 l_pdet_rec_tbl(i).trx_type := C_COMM_NREC;
835 l_pdet_rec_tbl(i).incentive_type_code := C_COMMISSION;
836 l_pdet_rec_tbl(i).amount := l_pay_ws_rec.COMM_NREC;
837 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
838 l_pdet_rec_tbl(i).status := C_UNLOADED;
839 l_pdet_rec_tbl(i).loaded_date := NULL;
840 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
841 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
842 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
843 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
844 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
845 l_pdet_rec_tbl(i).creation_date := SYSDATE;
846 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
847 l_status := 'COMM Non Rec '||TO_CHAR(i)||' built';
848 i := i + 1;
849 END IF;
850
851 -- 2. COMM Rec (only commission incentive types have PPs)
852
853 IF l_pay_ws_rec.COMM_REC <> 0 THEN
854 l_pdet_rec_tbl(i).posting_type := C_REC;
855 l_pdet_rec_tbl(i).trx_type := C_COMM_REC;
856 l_pdet_rec_tbl(i).incentive_type_code := C_COMMISSION;
857 l_pdet_rec_tbl(i).amount := l_pay_ws_rec.COMM_REC;
858 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
859 l_pdet_rec_tbl(i).status := C_UNLOADED;
860 l_pdet_rec_tbl(i).loaded_date := NULL;
861 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
862 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
863 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
864 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
865 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
866 l_pdet_rec_tbl(i).creation_date := SYSDATE;
867 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
868 l_status := 'COMM Rec '||TO_CHAR(i)||' built';
869 i := i + 1;
870 END IF;
871
872 -- 3. COMM To Recover
873
874 IF l_pay_ws_rec.COMM_TO_REC <> 0 THEN
875 l_pdet_rec_tbl(i).posting_type := C_TO_REC;
876 l_pdet_rec_tbl(i).trx_type := C_COMM_TO_REC;
877 l_pdet_rec_tbl(i).incentive_type_code := C_COMMISSION;
878 l_pdet_rec_tbl(i).amount := l_pay_ws_rec.COMM_TO_REC;
879 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
880 l_pdet_rec_tbl(i).status := C_UNLOADED;
881 l_pdet_rec_tbl(i).loaded_date := NULL;
882 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
883 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
884 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
885 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
886 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
887 l_pdet_rec_tbl(i).creation_date := SYSDATE;
888 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
889 l_status := 'COMM To Recover '||TO_CHAR(i)||' built';
890 i := i + 1;
891 END IF;
892
893 -- 4. BONUS Rec
894
895 IF l_pay_ws_rec.BONUS_REC <> 0 THEN
896 l_pdet_rec_tbl(i).posting_type := C_REC;
897 l_pdet_rec_tbl(i).trx_type := C_BONUS_REC;
898 l_pdet_rec_tbl(i).incentive_type_code := C_BONUS;
899 l_pdet_rec_tbl(i).amount := l_pay_ws_rec.BONUS_REC;
900 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
901 l_pdet_rec_tbl(i).status := C_UNLOADED;
902 l_pdet_rec_tbl(i).loaded_date := NULL;
903 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
904 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
905 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
906 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
907 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
908 l_pdet_rec_tbl(i).creation_date := SYSDATE;
909 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
910 l_status := 'BONUS Rec '||TO_CHAR(i)||' built';
911 i := i + 1;
912 END IF;
913
914 -- 5. BONUS To Recover (will recover from comm if possible)
915
916 IF l_pay_ws_rec.BONUS_TO_REC <> 0 THEN
917 l_pdet_rec_tbl(i).posting_type := C_TO_REC;
918 l_pdet_rec_tbl(i).trx_type := C_BONUS_TO_REC;
919 l_pdet_rec_tbl(i).incentive_type_code := C_BONUS;
920 l_pdet_rec_tbl(i).amount := l_pay_ws_rec.BONUS_TO_REC;
921 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
922 l_pdet_rec_tbl(i).status := C_UNLOADED;
923 l_pdet_rec_tbl(i).loaded_date := NULL;
924 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
925 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
926 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
927 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
928 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
929 l_pdet_rec_tbl(i).creation_date := SYSDATE;
930 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
931 l_status := 'BONUS To Rec '||TO_CHAR(i)||' built';
932 i := i + 1;
933 END IF;
934
935 -- 6. MANUAL Expense e.g., Earned and should not be categorized as
936 -- "NRec" in the worksheet
937 -- (e.g., Manual incentive type from CN_WORKSHEET_BONUSES
938 -- thru a join from CN_WORKSHEETS)
939
940 FOR l_manual_exp_rec IN get_ws_manual_exp(l_pay_ws_rec.payment_worksheet_id) LOOP
941 IF l_manual_exp_rec.AMOUNT <> 0 THEN
942 l_pdet_rec_tbl(i).posting_type := C_EXPENSE;
943 l_pdet_rec_tbl(i).trx_type := C_MANUAL_EXPENSE;
944 l_pdet_rec_tbl(i).incentive_type_code := C_MANUAL;
945 l_pdet_rec_tbl(i).amount := l_manual_exp_rec.amount;
946 l_pdet_rec_tbl(i).srp_plan_assign_id := l_manual_exp_rec.srp_plan_assign_id;
947 l_pdet_rec_tbl(i).quota_id := l_manual_exp_rec.quota_id;
948 l_pdet_rec_tbl(i).posting_batch_id := CN_PREPOSTBATCHES.G_BATCH_ID;
949 l_pdet_rec_tbl(i).status := C_UNLOADED;
950 l_pdet_rec_tbl(i).loaded_date := NULL;
951 l_pdet_rec_tbl(i).payee_salesrep_id := l_pay_ws_rec.salesrep_id;
952 l_pdet_rec_tbl(i).credited_salesrep_id := l_pay_ws_rec.salesrep_id;
953 l_pdet_rec_tbl(i).role_id := l_pay_ws_rec.role_id;
954 l_pdet_rec_tbl(i).credit_type_id := l_pay_ws_rec.credit_type_id;
955 l_pdet_rec_tbl(i).pay_period_id := l_pay_ws_rec.pay_period_id;
956 l_pdet_rec_tbl(i).creation_date := SYSDATE;
957 l_pdet_rec_tbl(i).created_by := FND_GLOBAL.USER_ID;
958 l_status := 'MANUAL Expense '||TO_CHAR(i)||' built';
959 i := i + 1;
960 END IF;
961 END LOOP;
962
963 -- Create Posting Detail through API
964 Create_PrePostDetails(
965 p_api_version => p_api_version,
966 p_init_msg_list => p_init_msg_list,
967 p_commit => p_commit,
968 p_validation_level => p_validation_level,
969 x_return_status => x_return_status,
970 x_msg_count => x_msg_count,
971 x_msg_data => x_msg_data,
972 p_posting_detail_rec_tbl => l_pdet_rec_tbl
973 );
974 --dbms_output.put_line('It is here');
975 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
976 l_status := 'Error in creation of posting detail for worksheet';
977 RAISE FND_API.G_EXC_ERROR;
978 END IF;
979
980
981 -- Update posting status of POSTED worksheet
982 -- Replace with table api
983
984 --UPDATE cn_payment_worksheets
985 --SET posting_status = C_POSTED
986 --WHERE payment_worksheet_id = l_pay_ws_rec.payment_worksheet_id;
987
988 cn_payment_worksheets_pkg.update_record
989 (
990 x_payment_worksheet_id => l_pay_ws_rec.payment_worksheet_id,
991 x_posting_status => c_posted,
992 x_last_update_date => SYSDATE,
993 x_last_updated_by => FND_GLOBAL.USER_ID,
994 x_last_update_login => FND_GLOBAL.LOGIN_ID);
995
996
997 l_status := 'Posting Detail created for WS';
998
999 END LOOP; -- Get next payment worksheet
1000
1001 -- Test if all worksheets of the payrun are posted and
1002 -- if so update the posting status of the pay run.
1003 -- You may want to make this independent of the l_pay_ws_rec loop
1004 -- and select pay runs based on the batch start and end dates.
1005
1006 -- declare cursor to select from cn_payruns
1007 -- where pr.pay_period_id = pp.pay_period_id
1008 -- and pp.start_date between pb.start_date and pb.end_date
1009 -- (DON'T FORGET TO MAKE SAME CHANGE FOR l_pay_ws_rec)
1010
1011 -- declare cursor to count number of not posted worksheets
1012
1013 -- for each payrun loop
1014 -- check # of not posted worksheets
1015 -- if 0 then
1016 -- update payrun's posting status
1017 -- end if
1018 -- end loop
1019
1020 for l_posted_payrun in get_posted_payruns loop
1021 null;
1022 /*
1023 cn_payruns_pkg.update_record(x_payrun_id => l_posted_payrun.payrun_id,
1024 x_status => C_POSTED,
1025 x_last_updated_by => FND_GLOBAL.USER_ID,
1026 x_last_update_date => SYSDATE,
1027 x_last_update_login => FND_GLOBAL.LOGIN_ID);
1028 */
1029 end loop;
1030
1031 -- End of API body.
1032 -- Standard check of p_commit.
1033 IF FND_API.To_Boolean( p_commit ) THEN
1034 COMMIT WORK;
1035 END IF;
1036 -- Standard call to get message count and if count is 1, get message info.
1037 FND_MSG_PUB.Count_And_Get
1038 ( p_count => x_msg_count ,
1039 p_data => x_msg_data
1040 );
1041 EXCEPTION -- post payws
1042 WHEN FND_API.G_EXC_ERROR THEN
1043 ROLLBACK TO PrePost_PayWorksheets;
1044 x_return_status := FND_API.G_RET_STS_ERROR ;
1045 FND_MSG_PUB.Count_And_Get
1046 ( p_count => x_msg_count ,
1047 p_data => x_msg_data
1048 );
1049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1050 ROLLBACK TO PrePost_PayWorksheets;
1051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1052 FND_MSG_PUB.Count_And_Get
1053 ( p_count => x_msg_count ,
1054 p_data => x_msg_data
1055 );
1056 WHEN OTHERS THEN
1057 ROLLBACK TO PrePost_PayWorksheets;
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1059 IF FND_MSG_PUB.Check_Msg_Level
1060 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1061 FND_MSG_PUB.Add_Exc_Msg
1062 ( G_PKG_NAME ,
1063 l_api_name);
1064 END IF;
1065 FND_MSG_PUB.Count_And_Get
1066 ( p_count => x_msg_count ,
1067 p_data => x_msg_data
1068 );
1069 END PrePost_PayWorksheets;
1070 END CN_PREPOST_PVT;