[Home] [Help]
PACKAGE BODY: APPS.OKE_BILLING_EVENT_PUB
Source
1 PACKAGE BODY OKE_BILLING_EVENT_PUB AS
2 /* $Header: OKEPDBLB.pls 120.0.12020000.2 2013/02/22 06:38:25 ansraj noship $ */
3
4 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
5
6
7
8 -- GLOBAL MESSAGE CONSTANTS
9
10 G_FND_APP CONSTANT VARCHAR2(200) := OKE_API.G_FND_APP;
11
12 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKE_API.G_FORM_UNABLE_TO_RESERVE_REC;
13 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_FORM_RECORD_DELETED;
14
15 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKE_API.G_FORM_RECORD_CHANGED;
16
17 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_RECORD_LOGICALLY_DELETED;
18 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_REQUIRED_VALUE;
19 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_INVALID_VALUE;
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_COL_NAME_TOKEN;
21 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_PARENT_TABLE_TOKEN;
22 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_CHILD_TABLE_TOKEN;
23
24 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKE_NO_PARENT_RECORD';
25 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
26
27 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
28 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
29 G_VIEW CONSTANT VARCHAR2(200) := 'OKE_K_LINES_V';
30
31 G_EXCEPTION_HALT_VALIDATION exception;
32
33
34
35 Function validate_billing_attributes(p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type )
36 RETURN VARCHAR2 IS
37
38 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
39 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
40
41
42 PROCEDURE Validate_Header_ID (X_Return_Status OUT NOCOPY VARCHAR2,
43 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
44
45 L_Value VARCHAR2(1) := 'N';
46
47 CURSOR C (P_ID NUMBER) IS
48 SELECT 'X'
49 FROM okc_k_headers_b
50 WHERE ID = P_ID;
51
52 Begin
53
54 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
55
56 IF p_k_billing_event_rec.K_Header_ID IS NOT null THEN
57
58
59 OPEN C ( p_k_billing_event_rec.K_Header_ID);
60 FETCH C INTO L_Value;
61 CLOSE C;
62
63 IF L_Value <> 'X' THEN
64
65 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
66 p_msg_name => g_invalid_value,
67 p_token1 => g_col_name_token,
68 p_token1_value => 'K_Header_ID');
69 -- notify caller of an error
70 X_Return_Status := OKE_API.G_RET_STS_ERROR;
71
72 -- halt validation
73 RAISE G_EXCEPTION_HALT_VALIDATION;
74
75 END IF;
76
77 End If;
78
79
80 EXCEPTION
81 WHEN G_EXCEPTION_HALT_VALIDATION THEN
82
83 NULL; -- Even failed, continue validate other attributes
84
85 WHEN OTHERS THEN
86
87 -- store SQL error message on message stack
88 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
89 p_msg_name => g_unexpected_error,
90 p_token1 => g_sqlcode_token,
91 p_token1_value=> sqlcode,
92 p_token2 => g_sqlerrm_token,
93 p_token2_value=> sqlerrm);
94 -- notify caller of an error as UNEXPETED error
95 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
96
97 End Validate_Header_ID;
98
99 PROCEDURE Validate_Line_ID (X_Return_Status OUT NOCOPY VARCHAR2,
100 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
101
102 L_Value VARCHAR2(1) := 'N';
103
104 CURSOR C (P_ID NUMBER) IS
105 SELECT 'X'
106 FROM okc_k_lines_b
107 WHERE ID = P_ID;
108
109
110 Begin
111
112 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
113
114 IF p_k_billing_event_rec.K_Line_ID IS NOT null THEN
115
116 OPEN C ( p_k_billing_event_rec.K_Line_ID);
117 FETCH C INTO L_Value;
118 CLOSE C;
119
120
121
122 IF L_Value <> 'X' THEN
123
124 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
125 p_msg_name => g_invalid_value,
126 p_token1 => g_col_name_token,
127 p_token1_value => 'K_Line_ID');
128 -- notify caller of an error
129 X_Return_Status := OKE_API.G_RET_STS_ERROR;
130
131 -- halt validation
132 RAISE G_EXCEPTION_HALT_VALIDATION;
133
134 END IF;
135
136 End If;
137
138 IF p_k_billing_event_rec.Bill_Line_Id IS NOT null THEN
139
140 OPEN C ( p_k_billing_event_rec.Bill_Line_Id);
141 FETCH C INTO L_Value;
142 CLOSE C;
143
144
145
146 IF L_Value <> 'X' THEN
147
148 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
149 p_msg_name => g_invalid_value,
150 p_token1 => g_col_name_token,
151 p_token1_value => 'Bill_Line_Id');
152 -- notify caller of an error
153 X_Return_Status := OKE_API.G_RET_STS_ERROR;
154
155 -- halt validation
156 RAISE G_EXCEPTION_HALT_VALIDATION;
157
158 END IF;
159
160 End If;
161
162 EXCEPTION
163 WHEN G_EXCEPTION_HALT_VALIDATION THEN
164
165 NULL; -- Even failed, continue validate other attributes
166
167 WHEN OTHERS THEN
168
169 -- store SQL error message on message stack
170 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
171 p_msg_name => g_unexpected_error,
172 p_token1 => g_sqlcode_token,
173 p_token1_value=> sqlcode,
174 p_token2 => g_sqlerrm_token,
175 p_token2_value=> sqlerrm);
176 -- notify caller of an error as UNEXPETED error
177 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
178
179 End Validate_Line_ID;
180
181
182
183 PROCEDURE Validate_Deliverable_Id(X_Return_Status OUT NOCOPY VARCHAR2,
184 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
185 L_Value VARCHAR2(1) := 'N';
186
187 CURSOR C (P_LINE_ID NUMBER, P_DEL_ID NUMBER ) IS
188 SELECT 'X'
189 FROM oke_k_deliverables_b
190 WHERE Deliverable_ID = P_DEL_ID
191 AND K_Line_ID = P_LINE_ID
192 AND Nvl(ready_to_bill,'N')='Y' ;
193
194 Begin
195
196 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
197
198 IF p_k_billing_event_rec.Deliverable_ID IS NOT null THEN
199
200 OPEN C ( p_k_billing_event_rec.K_Line_ID, p_k_billing_event_rec.Deliverable_ID );
201 FETCH C INTO L_Value;
202 CLOSE C;
203
204
205
206 IF L_Value <> 'X' THEN
207
208 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
209 p_msg_name => g_invalid_value,
210 p_token1 => g_col_name_token,
211 p_token1_value => 'Deliverable_ID');
212 -- notify caller of an error
213 X_Return_Status := OKE_API.G_RET_STS_ERROR;
214
215 -- halt validation
216 RAISE G_EXCEPTION_HALT_VALIDATION;
217
218 END IF;
219 END IF;
220
221
222 EXCEPTION
223 WHEN G_EXCEPTION_HALT_VALIDATION THEN
224
225 NULL; -- Even failed, continue validate other attributes
226
227 WHEN OTHERS THEN
228
229 -- store SQL error message on message stack
230 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
231 p_msg_name => g_unexpected_error,
232 p_token1 => g_sqlcode_token,
233 p_token1_value=> sqlcode,
234 p_token2 => g_sqlerrm_token,
235 p_token2_value=> sqlerrm);
236 -- notify caller of an error as UNEXPETED error
237 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
238
239 End Validate_Deliverable_Id;
240
241 PROCEDURE Validate_Project_ID (X_Return_Status OUT NOCOPY VARCHAR2,
242 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
243
244 L_Line_Id Number;
245 L_Sequence Number := 1;
246 L_Max_Sequence Number;
247 L_Project_Id NUMBER;
248 L_Task_Id NUMBER;
249 L_Parent_Id NUMBER;
250 L_Value VARCHAR2(1) := 'N';
251
252 CURSOR Header_C ( P_Header_Id Number ) IS
253 SELECT Project_ID
254 FROM oke_k_headers
255 WHERE K_Header_Id = P_Header_Id;
256
257 CURSOR Top_C IS
258 SELECT PROJECT_ID, TASK_ID, PARENT_LINE_ID
259 FROM oke_k_lines
260 WHERE K_Line_Id = L_Line_Id;
261
262 CURSOR Sub_C (P_Line_Id NUMBER, P_Sequence NUMBER) IS
263 SELECT PROJECT_ID, TASK_ID
264 FROM OKE_K_LINES
265 WHERE K_LINE_ID = (SELECT CLE_ID_ASCENDANT FROM OKC_ANCESTRYS WHERE CLE_ID = P_LINE_ID AND LEVEL_SEQUENCE = P_SEQUENCE)
266 AND PROJECT_ID IS NOT NULL;
267
268 CURSOR C (P_ID NUMBER, P_ID1 NUMBER, P_ID2 NUMBER) IS
269 SELECT 'X'
270 FROM DUAL
271 WHERE P_ID IN (
272 SELECT P.Project_ID
273 FROM pa_projects_all p
274 WHERE p.Project_ID IN (SELECT To_Number(sub_project_id)
275 FROM pa_fin_structures_links_v
276 START WITH parent_project_id = P_ID1 AND (parent_task_id IN (SELECT Task_ID FROM pa_tasks WHERE Top_Task_ID = P_ID2) or P_ID2 IS NULL)
277 CONNECT BY parent_project_id = PRIOR sub_project_id)
278 UNION
279 SELECT Project_ID
280 FROM pa_projects_all
281 WHERE Project_ID = P_ID1);
282
283 CURSOR Seq_C (P_ID NUMBER ) IS
284 SELECT MAX(LEVEL_SEQUENCE)
285 FROM OKC_ANCESTRYS
286 WHERE CLE_ID = P_ID;
287
288 Begin
289
290 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
291
292 IF p_k_billing_event_rec.bill_Project_ID IS NOT null THEN
293
294 L_Line_Id := p_k_billing_event_rec.K_Line_ID;
295
296 OPEN Top_C;
297 FETCH Top_C INTO L_Project_Id, L_Task_Id, L_Parent_Id;
298 CLOSE Top_C;
299
300 IF L_Project_Id IS NULL THEN
301 IF L_Parent_Id IS NULL THEN
302
303 OPEN Header_C (p_k_billing_event_rec.K_Header_ID);
304 FETCH Header_C INTO L_Project_Id;
305 CLOSE Header_C;
306
307 ELSE
308
309 OPEN Seq_C ( L_Line_ID );
310 FETCH Seq_C INTO L_Max_Sequence;
311 CLOSE Seq_C;
312
313 FOR L_Sequence IN 1 ..L_Max_Sequence LOOP
314 OPEN Sub_C( L_Line_Id, L_Sequence);
315 Fetch Sub_C INTO L_Project_Id, L_Task_Id;
316 CLOSE Sub_C;
317
318 EXIT WHEN L_Project_Id IS NOT NULL;
319
320 END LOOP;
321
322
323
324 IF L_Project_Id IS NULL THEN
325 OPEN Header_C ( p_k_billing_event_rec.K_Header_ID );
326 FETCH Header_C INTO L_Project_Id;
327 CLOSE Header_C;
328 END IF;
329
330
331 END IF;
332
333 END IF;
334
335 OPEN C ( p_k_billing_event_rec.BILL_Project_ID, L_Project_ID, L_Task_ID);
336 FETCH C INTO L_Value;
337 CLOSE C;
338
339
340
341 IF L_Value <> 'X' THEN
342
343 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
344 p_msg_name => g_invalid_value,
345 p_token1 => g_col_name_token,
346 p_token1_value => 'Project_ID');
347 -- notify caller of an error
348 X_Return_Status := OKE_API.G_RET_STS_ERROR;
349
350 -- halt validation
351 RAISE G_EXCEPTION_HALT_VALIDATION;
352
353 END IF;
354
355 END IF;
356
357
358 EXCEPTION
359 WHEN G_EXCEPTION_HALT_VALIDATION THEN
360
361 NULL; -- Even failed, continue validate other attributes
362
363 WHEN OTHERS THEN
364
365 -- store SQL error message on message stack
366 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
367 p_msg_name => g_unexpected_error,
368 p_token1 => g_sqlcode_token,
369 p_token1_value=> sqlcode,
370 p_token2 => g_sqlerrm_token,
371 p_token2_value=> sqlerrm);
372 -- notify caller of an error as UNEXPETED error
373 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
374
375 End Validate_Project_ID;
376
377 PROCEDURE Validate_Task_ID (X_Return_Status OUT NOCOPY VARCHAR2,
378 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
379 L_Value VARCHAR2(1) := 'N';
380
381 CURSOR C (P_ID1 NUMBER, P_ID2 NUMBER) IS
382 SELECT 'X'
383 FROM pa_tasks
384 WHERE Task_ID = P_ID1
385 AND Project_ID = P_ID2;
386
387 Begin
388
389 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
390
391 IF p_k_billing_event_rec.BILL_TASK_ID IS NOT NULL THEN
392
393 IF p_k_billing_event_rec.BILL_Project_ID IS NULL OR p_k_billing_event_rec.BILL_Project_ID=OKE_API.G_MISS_NUM THEN
394
395 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
396 p_msg_name => g_required_value,
397 p_token1 => g_col_name_token,
398 p_token1_value => 'Project_ID');
399 -- notify caller of an error
400 X_Return_Status := OKE_API.G_RET_STS_ERROR;
401
402 -- halt validation
403 RAISE G_EXCEPTION_HALT_VALIDATION;
404
405 ELSE
406
407 OPEN C ( p_k_billing_event_rec.bill_Task_ID, p_k_billing_event_rec.bill_Project_ID );
408 FETCH C INTO L_Value;
409 CLOSE C;
410
411 IF L_Value <> 'X' THEN
412
413 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
414 p_msg_name => g_invalid_value,
415 p_token1 => g_col_name_token,
416 p_token1_value => 'Task_ID');
417 -- notify caller of an error
418 X_Return_Status := OKE_API.G_RET_STS_ERROR;
419
420 -- halt validation
421 RAISE G_EXCEPTION_HALT_VALIDATION;
422
423 END IF;
424
425 END IF;
426
427 END IF;
428
429 EXCEPTION
430 WHEN G_EXCEPTION_HALT_VALIDATION THEN
431
432 NULL; -- Even failed, continue validate other attributes
433
434 WHEN OTHERS THEN
435
436 -- store SQL error message on message stack
437 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
438 p_msg_name => g_unexpected_error,
439 p_token1 => g_sqlcode_token,
440 p_token1_value=> sqlcode,
441 p_token2 => g_sqlerrm_token,
442 p_token2_value=> sqlerrm);
443 -- notify caller of an error as UNEXPETED error
444 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
445
446 End Validate_Task_ID;
447
448 PROCEDURE Validate_BILL_EVENT_TYPE (X_Return_Status OUT NOCOPY VARCHAR2,
449 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
450 L_Value VARCHAR2(1) := 'N';
451
452 CURSOR C (p_event_type varchar2) IS
453 SELECT 'X'
454 from PA_EVENT_types WHERE EVENT_TYPE_CLASSIFICATION='MANUAL' and
455 sysdate between start_date_active and nvl(end_date_active,sysdate+1)
456 AND event_type=p_event_type;
457
458
459
460 Begin
461
462 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
463
464 IF p_k_billing_event_rec.BILL_EVENT_TYPE IS NOT NULL THEN
465
466 OPEN C ( p_k_billing_event_rec.BILL_EVENT_TYPE );
467 FETCH C INTO L_Value;
468 CLOSE C;
469
470 IF L_Value <> 'X' THEN
471
472 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
473 p_msg_name => g_invalid_value,
474 p_token1 => g_col_name_token,
475 p_token1_value => 'BILL_EVENT_TYPE');
476 -- notify caller of an error
477 X_Return_Status := OKE_API.G_RET_STS_ERROR;
478
479 -- halt validation
480 RAISE G_EXCEPTION_HALT_VALIDATION;
481
482 END IF;
483
484
485
486 END IF;
487
488 EXCEPTION
489 WHEN G_EXCEPTION_HALT_VALIDATION THEN
490
491 NULL; -- Even failed, continue validate other attributes
492
493 WHEN OTHERS THEN
494
495 -- store SQL error message on message stack
496 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
497 p_msg_name => g_unexpected_error,
498 p_token1 => g_sqlcode_token,
499 p_token1_value=> sqlcode,
500 p_token2 => g_sqlerrm_token,
501 p_token2_value=> sqlerrm);
502 -- notify caller of an error as UNEXPETED error
503 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
504
505 End Validate_BILL_EVENT_TYPE;
506
507 PROCEDURE Validate_item (X_Return_Status OUT NOCOPY VARCHAR2,
508 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
509 L_Value VARCHAR2(1) := 'N';
510
511 CURSOR C (p_id NUMBER,p_inv_org number) IS
512 SELECT 'X' from mtl_item_flexfields where organization_id= p_inv_org
513 and invoiceable_item_flag='Y' AND inventory_item_id= p_id;
514
515 CURSOR inv_org IS
516 SELECT Inv_Organization_ID
517 FROM okc_k_headers_b WHERE id= p_k_billing_event_rec.k_header_id;
518
519 l_Inv_Organization_ID NUMBER;
520 Begin
521
522 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
523
524 IF p_k_billing_event_rec.Bill_Item_Id IS NOT NULL THEN
525
526 OPEN inv_org ;
527 FETCH inv_org INTO l_Inv_Organization_ID;
528 CLOSE inv_org;
529
530 OPEN C ( p_k_billing_event_rec.Bill_Item_Id,l_Inv_Organization_ID );
531 FETCH C INTO L_Value;
532 CLOSE C;
533
534
535 IF L_Value <> 'X' THEN
536
537 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
538 p_msg_name => g_invalid_value,
539 p_token1 => g_col_name_token,
540 p_token1_value => 'Bill_Item_Id');
541 -- notify caller of an error
542 X_Return_Status := OKE_API.G_RET_STS_ERROR;
543
544 -- halt validation
545 RAISE G_EXCEPTION_HALT_VALIDATION;
546
547 END IF;
548
549
550
551 END IF;
552
553 EXCEPTION
554 WHEN G_EXCEPTION_HALT_VALIDATION THEN
555
556 NULL; -- Even failed, continue validate other attributes
557
558 WHEN OTHERS THEN
559
560 -- store SQL error message on message stack
561 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
562 p_msg_name => g_unexpected_error,
563 p_token1 => g_sqlcode_token,
564 p_token1_value=> sqlcode,
565 p_token2 => g_sqlerrm_token,
566 p_token2_value=> sqlerrm);
567 -- notify caller of an error as UNEXPETED error
568 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
569
570 End Validate_item;
571
572 PROCEDURE Validate_CHANGE_REQUEST_ID (X_Return_Status OUT NOCOPY VARCHAR2,
573 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
574 L_Value VARCHAR2(1) := 'N';
575
576 CURSOR C (p_id NUMBER,P_HEADEr_ID NUMBER) IS
577 SELECT 'X' FROM oke_chg_requests_v WHERE k_header_id=P_HEADER_ID
578 AND chg_status_type_code='COMPLETED' AND chg_request_id=p_id ;
579
580
581 Begin
582
583 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
584
585 IF p_k_billing_event_rec.BILL_CHG_REQ_ID IS NOT null THEN
586
587
588 OPEN C ( p_k_billing_event_rec.BILL_CHG_REQ_ID,p_k_billing_event_rec.k_header_id );
589 FETCH C INTO L_Value;
590 CLOSE C;
591
592
593 IF L_Value <> 'X' THEN
594
595 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
596 p_msg_name => g_invalid_value,
597 p_token1 => g_col_name_token,
598 p_token1_value => 'BILL_CHG_REQ_ID');
599 -- notify caller of an error
600 X_Return_Status := OKE_API.G_RET_STS_ERROR;
601
602 -- halt validation
603 RAISE G_EXCEPTION_HALT_VALIDATION;
604
605 END IF;
606
607
608
609 END IF;
610
611 EXCEPTION
612 WHEN G_EXCEPTION_HALT_VALIDATION THEN
613
614 NULL; -- Even failed, continue validate other attributes
615
616 WHEN OTHERS THEN
617
618 -- store SQL error message on message stack
619 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
620 p_msg_name => g_unexpected_error,
621 p_token1 => g_sqlcode_token,
622 p_token1_value=> sqlcode,
623 p_token2 => g_sqlerrm_token,
624 p_token2_value=> sqlerrm);
625 -- notify caller of an error as UNEXPETED error
626 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
627
628 End Validate_CHANGE_REQUEST_ID;
629
630 PROCEDURE Validate_bill_org (X_Return_Status OUT NOCOPY VARCHAR2,
631 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
632 L_Value VARCHAR2(1) := 'N';
633
634 CURSOR C (p_id NUMBER,p_bill_ou number) IS
635 SELECT 'X' from pa_all_organizations paorg,hr_organization_units hrorg
636 WHERE paorg.organization_id=hrorg.organization_id and nvl(paorg.org_id,-99)=nvl(p_bill_ou,-99)
637 and sysdate between hrorg.date_from and nvl(hrorg.date_to,sysdate+1) AND hrorg.organization_id=p_id ;
638
639
640 CURSOR bill_ou IS
641 SELECT org_id
642 FROM pa_projects_all
643 WHERE project_id = p_k_billing_event_rec.bill_project_id;
644
645
646
647 l_bill_ou NUMBER;
648 Begin
649
650 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
651
652 IF p_k_billing_event_rec.BILL_ORGANIZATION_ID IS NOT NULL THEN
653
654 OPEN bill_ou ;
655 FETCH bill_ou INTO l_bill_ou;
656 CLOSE bill_ou;
657
658 OPEN C (p_k_billing_event_rec.BILL_ORGANIZATION_ID,l_bill_ou );
659 FETCH C INTO L_Value;
660 CLOSE C;
661
662
663 IF L_Value <> 'X' THEN
664
665 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
666 p_msg_name => g_invalid_value,
667 p_token1 => g_col_name_token,
668 p_token1_value => 'BILL_ORGANIZATION_ID');
669 -- notify caller of an error
670 X_Return_Status := OKE_API.G_RET_STS_ERROR;
671
672 -- halt validation
673 RAISE G_EXCEPTION_HALT_VALIDATION;
674
675 END IF;
676
677
678
679 END IF;
680
681 EXCEPTION
682 WHEN G_EXCEPTION_HALT_VALIDATION THEN
683
684 NULL; -- Even failed, continue validate other attributes
685
686 WHEN OTHERS THEN
687
688 -- store SQL error message on message stack
689 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
690 p_msg_name => g_unexpected_error,
691 p_token1 => g_sqlcode_token,
692 p_token1_value=> sqlcode,
693 p_token2 => g_sqlerrm_token,
694 p_token2_value=> sqlerrm);
695 -- notify caller of an error as UNEXPETED error
696 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
697
698 End Validate_bill_org;
699
700 PROCEDURE Validate_bill_fund_ref (X_Return_Status OUT NOCOPY VARCHAR2,
701 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
702 L_Value VARCHAR2(1) := 'N';
703
704 CURSOR C IS
705 select 'X'
706 from oke_k_fund_allocations where object_id=p_k_billing_event_rec.k_header_id
707 and (k_line_id is null or k_line_id= p_k_billing_event_rec.bill_line_id)
708 and nvl(project_id,-1)=nvl(p_k_billing_event_rec.bill_project_id,-1)
709 and (task_id is null or task_id = p_k_billing_event_rec.bill_task_id )
710 AND reference1=Nvl(p_k_billing_event_rec.BILL_FUND_REF1,reference1)
711 AND reference2=Nvl(p_k_billing_event_rec.BILL_FUND_REF2,reference2)
712 AND reference3=Nvl(p_k_billing_event_rec.BILL_FUND_REF3 ,reference3);
713
714
715 Begin
716
717 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
718
719 IF p_k_billing_event_rec.BILL_FUND_REF1 IS NOT null OR p_k_billing_event_rec.BILL_FUND_REF2 IS NOT null OR p_k_billing_event_rec.BILL_FUND_REF3 IS NOT null THEN
720
721
722 OPEN C ;
723 FETCH C INTO L_Value;
724 CLOSE C;
725
726
727 IF L_Value <> 'X' THEN
728
729 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
730 p_msg_name => g_invalid_value,
731 p_token1 => g_col_name_token,
732 p_token1_value => 'BILL_FUND_REF1/REF2/REF3');
733 -- notify caller of an error
734 X_Return_Status := OKE_API.G_RET_STS_ERROR;
735
736 -- halt validation
737 RAISE G_EXCEPTION_HALT_VALIDATION;
738
739 END IF;
740
741
742
743 END IF;
744
745 EXCEPTION
746 WHEN G_EXCEPTION_HALT_VALIDATION THEN
747
748 NULL; -- Even failed, continue validate other attributes
749
750 WHEN OTHERS THEN
751
752 -- store SQL error message on message stack
753 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
754 p_msg_name => g_unexpected_error,
755 p_token1 => g_sqlcode_token,
756 p_token1_value=> sqlcode,
757 p_token2 => g_sqlerrm_token,
758 p_token2_value=> sqlerrm);
759 -- notify caller of an error as UNEXPETED error
760 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
761
762 End Validate_bill_fund_ref;
763
764 PROCEDURE Validate_currency (X_Return_Status OUT NOCOPY VARCHAR2,
765 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type) IS
766 L_Value VARCHAR2(1) := 'N';
767
768
769
770 CURSOR multi_currency_billing_project(C_Project_Id NUMBER) IS
771 SELECT multi_currency_billing_flag
772 FROM pa_projects_all
773 WHERE project_id = C_Project_Id;
774
775 CURSOR bill_currency(C_header_Id NUMBER) IS
776 SELECT currency_code
777 FROM okc_k_headers_b
778 WHERE id = C_header_Id;
779
780 CURSOR RateType ( p_BILL_RATE_TYPE varchar2) is
781 SELECT 'X'
782 FROM gl_daily_conversion_types
783 WHERE conversion_type =p_BILL_RATE_TYPE;
784
785 CURSOR get_cur_code(p_cur_code varchar2) is
786 SELECT 'X' FROM FND_CURRENCIES_VL WHERE CURRENCY_FLAG = 'Y' AND CURRENCY_CODE= p_cur_code;
787
788 L_Multi_Currency varchar2(2);
789 l_bill_currency VARCHAR2(30);
790 Begin
791
792 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
793
794
795
796 OPEN multi_currency_billing_project(p_k_billing_event_rec.bill_project_id);
797 FETCH multi_currency_billing_project INTO L_Multi_Currency;
798 CLOSE multi_currency_billing_project;
799
800
801
802 IF ( p_k_billing_event_rec.BILL_CURRENCY_CODE IS NULL OR p_k_billing_event_rec.BILL_CURRENCY_CODE=OKE_API.G_MISS_CHAR ) THEN
803 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
804 p_msg_name =>G_REQUIRED_VALUE,
805 p_token1 =>G_COL_NAME_TOKEN,
806 p_token1_value =>'BILL_CURRENCY_CODE');
807 x_return_status := OKE_API.G_RET_STS_ERROR;
808 END IF ;
809
810
811
812 OPEN bill_currency(p_k_billing_event_rec.k_header_id) ;
813 FETCH bill_currency INTO l_bill_currency;
814 CLOSE bill_currency;
815
816 IF Nvl(L_Multi_Currency,'N')= 'N' THEN
817 IF ( p_k_billing_event_rec.BILL_CURRENCY_CODE IS NOT null ) THEN
818 IF l_bill_currency <> p_k_billing_event_rec.BILL_CURRENCY_CODE THEN
819 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
820 p_msg_name =>G_INVALID_VALUE,
821 p_token1 =>G_COL_NAME_TOKEN,
822 p_token1_value =>'BILL_CURRENCY_CODE');
823 x_return_status := OKE_API.G_RET_STS_ERROR;
824
825 END IF;
826 END IF;
827 END IF;
828
829
830 IF ( p_k_billing_event_rec.BILL_CURRENCY_CODE IS NOT null ) THEN
831
832
833
834 OPEN get_cur_code(p_k_billing_event_rec.BILL_CURRENCY_CODE);
835 FETCH get_cur_code INTO l_value;
836 CLOSE get_cur_code;
837
838 IF l_value <>'X' THEN
839 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
840 p_msg_name =>G_INVALID_VALUE,
841 p_token1 =>G_COL_NAME_TOKEN,
842 p_token1_value =>'BILL_CURRENCY_CODE');
843 x_return_status := OKE_API.G_RET_STS_ERROR;
844
845 END IF;
846
847
848 IF l_bill_currency <> p_k_billing_event_rec.BILL_CURRENCY_CODE THEN
849
850
851
852 IF ( p_k_billing_event_rec.BILL_RATE_TYPE IS NULL OR p_k_billing_event_rec.BILL_RATE_TYPE=OKE_API.G_MISS_CHAR ) THEN
853 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
854 p_msg_name =>G_REQUIRED_VALUE,
855 p_token1 =>G_COL_NAME_TOKEN,
856 p_token1_value =>'BILL_RATE_TYPE');
857 x_return_status := OKE_API.G_RET_STS_ERROR;
858 END IF ;
859
860 IF ( p_k_billing_event_rec.BILL_RATE_DATE IS NULL OR p_k_billing_event_rec.BILL_RATE_DATE=OKE_API.G_MISS_DATE ) THEN
861 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
862 p_msg_name =>G_REQUIRED_VALUE,
863 p_token1 =>G_COL_NAME_TOKEN,
864 p_token1_value =>'BILL_RATE_DATE');
865 x_return_status := OKE_API.G_RET_STS_ERROR;
866 END IF ;
867
868 IF Upper(p_k_billing_event_rec.BILL_RATE_TYPE) = 'USER' AND p_k_billing_event_rec.BILL_EXCHANGE_RATE IS null THEN
869 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
870 p_msg_name =>G_REQUIRED_VALUE,
871 p_token1 =>G_COL_NAME_TOKEN,
872 p_token1_value =>'BILL_EXCHANGE_RATE');
873 x_return_status := OKE_API.G_RET_STS_ERROR;
874 END IF;
875
876 IF p_k_billing_event_rec.BILL_RATE_TYPE IS NOT NULL THEN
877
878 Open RateType ( p_k_billing_event_rec.BILL_RATE_TYPE );
879 Fetch RateType Into L_Value;
880 Close RateType;
881
882 IF L_VALUE<>'X' THEN
883
884 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
885 p_msg_name =>G_INVALID_VALUE,
886 p_token1 =>G_COL_NAME_TOKEN,
887 p_token1_value =>'BILL_RATE_TYPE');
888
889 x_return_status := OKE_API.G_RET_STS_ERROR;
890
891 END IF;
892
893 END IF;
894
895 ELSE
896 IF p_k_billing_event_rec.BILL_RATE_TYPE IS NOT NULL THEN
897 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
898 p_msg_name =>G_INVALID_VALUE,
899 p_token1 =>G_COL_NAME_TOKEN,
900 p_token1_value =>'BILL_RATE_TYPE');
901 END IF;
902
903 IF p_k_billing_event_rec.BILL_RATE_DATE IS NOT NULL THEN
904 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
905 p_msg_name =>G_INVALID_VALUE,
906 p_token1 =>G_COL_NAME_TOKEN,
907 p_token1_value =>'BILL_RATE_DATE');
908 END IF;
909
910 IF p_k_billing_event_rec.BILL_EXCHANGE_RATE IS NOT NULL THEN
911 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
912 p_msg_name =>G_INVALID_VALUE,
913 p_token1 =>G_COL_NAME_TOKEN,
914 p_token1_value =>'BILL_EXCHANGE_RATE');
915 END IF;
916 END IF;
917
918 END IF;
919
920
921
922
923 EXCEPTION
924 WHEN G_EXCEPTION_HALT_VALIDATION THEN
925
926 NULL; -- Even failed, continue validate other attributes
927
928 WHEN OTHERS THEN
929
930 -- store SQL error message on message stack
931 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
932 p_msg_name => g_unexpected_error,
933 p_token1 => g_sqlcode_token,
934 p_token1_value=> sqlcode,
935 p_token2 => g_sqlerrm_token,
936 p_token2_value=> sqlerrm);
937 -- notify caller of an error as UNEXPETED error
938 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
939
940 End Validate_currency;
941
942
943
944 BEGIN
945
946 --validate mandatory fields
947 IF ( p_k_billing_event_rec.k_header_id IS NULL ) THEN
948 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
949 p_msg_name =>G_REQUIRED_VALUE,
950 p_token1 =>G_COL_NAME_TOKEN,
951 p_token1_value =>'CONTRACT_HEADER_ID');
952 x_return_status := OKE_API.G_RET_STS_ERROR;
953 END IF ;
954
955 IF ( p_k_billing_event_rec.K_LINE_ID IS NULL ) THEN
956 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
957 p_msg_name =>G_REQUIRED_VALUE,
958 p_token1 =>G_COL_NAME_TOKEN,
959 p_token1_value =>'CONTRACT_LINE_ID');
960 x_return_status := OKE_API.G_RET_STS_ERROR;
961 END IF ;
962
963 IF ( p_k_billing_event_rec.DELIVERABLE_ID IS NULL ) THEN
964 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
965 p_msg_name =>G_REQUIRED_VALUE,
966 p_token1 =>G_COL_NAME_TOKEN,
967 p_token1_value =>'CONTRACT_DELIVERABLE_ID');
968 x_return_status := OKE_API.G_RET_STS_ERROR;
969 END IF ;
970
971 IF ( p_k_billing_event_rec.BILL_EVENT_TYPE IS NULL ) THEN
972 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
973 p_msg_name =>G_REQUIRED_VALUE,
974 p_token1 =>G_COL_NAME_TOKEN,
975 p_token1_value =>'BILL_EVENT_TYPE');
976 x_return_status := OKE_API.G_RET_STS_ERROR;
977 END IF ;
978
979
980 Validate_Header_ID( x_return_status => l_return_status,
981 p_k_billing_event_rec => p_k_billing_event_rec);
982
983 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
984 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
985 x_return_status := l_return_status;
986 End If;
987 End If;
988
989
990 Validate_Line_ID( x_return_status => l_return_status,
991 p_k_billing_event_rec => p_k_billing_event_rec);
992
993 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
994 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
995 x_return_status := l_return_status;
996 End If;
997 End If;
998
999 Validate_Deliverable_Id( x_return_status => l_return_status,
1000 p_k_billing_event_rec => p_k_billing_event_rec);
1001
1002 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1003 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1004 x_return_status := l_return_status;
1005 End If;
1006 End If;
1007
1008 Validate_Project_ID( x_return_status => l_return_status,
1009 p_k_billing_event_rec => p_k_billing_event_rec);
1010
1011 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1012 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1013 x_return_status := l_return_status;
1014 End If;
1015 End If;
1016
1017 Validate_Task_ID ( x_return_status => l_return_status,
1018 p_k_billing_event_rec => p_k_billing_event_rec);
1019
1020 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1021 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1022 x_return_status := l_return_status;
1023 End If;
1024 End If;
1025
1026 Validate_BILL_EVENT_TYPE ( x_return_status => l_return_status,
1027 p_k_billing_event_rec => p_k_billing_event_rec);
1028
1029 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1030 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1031 x_return_status := l_return_status;
1032 End If;
1033 End If;
1034
1035 Validate_item ( x_return_status => l_return_status,
1036 p_k_billing_event_rec => p_k_billing_event_rec);
1037
1038 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1039 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1040 x_return_status := l_return_status;
1041 End If;
1042 End If;
1043
1044 Validate_CHANGE_REQUEST_ID( x_return_status => l_return_status,
1045 p_k_billing_event_rec => p_k_billing_event_rec);
1046
1047 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1048 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1049 x_return_status := l_return_status;
1050 End If;
1051 End If;
1052
1053 Validate_bill_org ( x_return_status => l_return_status,
1054 p_k_billing_event_rec => p_k_billing_event_rec);
1055
1056 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1057 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1058 x_return_status := l_return_status;
1059 End If;
1060 End If;
1061
1062 Validate_bill_fund_ref( x_return_status => l_return_status,
1063 p_k_billing_event_rec => p_k_billing_event_rec);
1064
1065 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1066 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1067 x_return_status := l_return_status;
1068 End If;
1069 End If;
1070
1071 Validate_currency ( x_return_status => l_return_status,
1072 p_k_billing_event_rec => p_k_billing_event_rec);
1073
1074 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1075 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1076 x_return_status := l_return_status;
1077 End If;
1078 End If;
1079
1080 return (x_return_status);
1081
1082 exception
1083 WHEN OTHERS THEN
1084 -- store SQL error message on message stack
1085 OKE_API.SET_MESSAGE(
1086 p_app_name =>g_app_name,
1087 p_msg_name =>G_UNEXPECTED_ERROR,
1088 p_token1 =>G_SQLCODE_TOKEN,
1089 p_token1_value =>SQLCODE,
1090 p_token2 =>G_SQLERRM_TOKEN,
1091 p_token2_value =>SQLERRM);
1092
1093 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1094
1095 RETURN(x_return_status);
1096
1097 END validate_billing_attributes;
1098
1099 PROCEDURE Recalculate_price(p_k_billing_event_rec IN k_billing_event_rec_type,
1100 l_bill_unit_price OUT nocopy NUMBER,
1101 l_REVENUE_AMOUNT OUT nocopy NUMBER,
1102 l_exchange_rate OUT nocopy number ) IS
1103 l_bill_currency VARCHAR2(40);
1104 ConvAmt number;
1105 Numerator number;
1106 Denom number;
1107 l_price NUMBER;
1108 l_price1 NUMBER;
1109 CURSOR get_unit_price is
1110 Select Unit_Price
1111 From oke_k_deliverables_vl
1112 Where Deliverable_Id = p_k_billing_event_rec.deliverable_id;
1113
1114
1115 BEGIN
1116
1117
1118 SELECT currency_code INTO l_bill_currency
1119 FROM okc_k_headers_b
1120 WHERE id = p_k_billing_event_rec.k_header_id;
1121
1122
1123 --default price
1124 OPEN get_unit_price;
1125 FETCH get_unit_price INTO l_price;
1126 CLOSE get_unit_price;
1127
1128
1129 IF Nvl(l_bill_currency,p_k_billing_event_rec.BILL_CURRENCY_CODE)<>p_k_billing_event_rec.BILL_CURRENCY_CODE THEN
1130
1131
1132 IF(Upper(p_k_billing_event_rec.BILL_RATE_TYPE)<>'USER') then
1133 GL_CURRENCY_API.Convert_Amount
1134 ( X_From_Currency => l_bill_currency
1135 , X_To_Currency => p_k_billing_event_rec.BILL_CURRENCY_CODE
1136 , X_Conversion_Date => p_k_billing_event_rec.BILL_RATE_DATE
1137 , X_Conversion_Type => p_k_billing_event_rec.BILL_RATE_TYPE
1138 , X_Amount => l_price
1139 , X_Converted_Amount => ConvAmt
1140 , X_Denominator => Denom
1141 , X_Numerator => Numerator
1142 , X_Rate => l_exchange_rate
1143 );
1144
1145 ELSE
1146 l_exchange_rate:=p_k_billing_event_rec.bill_exchange_rate ;
1147
1148 END IF;
1149
1150 END IF;
1151
1152 if p_k_billing_event_rec.BILL_UNIT_PRICE IS NOT NULL THEN
1153 l_bill_unit_price:= p_k_billing_event_rec.BILL_UNIT_PRICE;
1154 else
1155 l_bill_unit_price:= l_price * Nvl(l_exchange_rate,1);
1156 END IF;
1157
1158
1159 IF p_k_billing_event_rec.REVENUE_AMOUNT IS null THEN
1160 l_REVENUE_AMOUNT:= l_bill_unit_price*Nvl(p_k_billing_event_rec.bill_quantity,0);
1161 ELSE
1162 l_REVENUE_AMOUNT :=p_k_billing_event_rec.REVENUE_AMOUNT;
1163 END IF;
1164
1165
1166
1167 END Recalculate_price;
1168
1169 PROCEDURE create_k_billing_event(p_api_version IN NUMBER,
1170 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1171 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type,
1172 x_k_billing_event_rec OUT NOCOPY OKE_BILLING_EVENT_PUB.k_billing_event_rec_type,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2
1176 )
1177 IS
1178 l_event_id NUMBER;
1179 l_api_name CONSTANT VARCHAR2(30) := 'create_k_billing_event';
1180 l_api_version CONSTANT NUMBER := 1.0;
1181 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1182 l_k_billing_event_rec OKE_BILLING_EVENT_PUB.k_billing_event_rec_type;
1183 l_bill_unit_price NUMBER;
1184 l_REVENUE_AMOUNT NUMBER;
1185 l_exchange_rate NUMBER;
1186
1187 FUNCTION null_out_defaults(
1188 p_k_billing_event_rec IN k_billing_event_rec_type) RETURN k_billing_event_rec_type IS
1189
1190 l_k_billing_event_rec k_billing_event_rec_type := p_k_billing_event_rec;
1191
1192 Cursor Dlv_C ( P_Deliverable_Id Number ) Is
1193 Select Project_Id, K_Line_Id, Inventory_Org_id, Item_Id, Currency_Code, Quantity, Unit_Price, Description
1194 From oke_k_deliverables_vl
1195 Where Deliverable_Id = P_Deliverable_Id;
1196
1197 CURSOR ProjNum ( C_Project_ID NUMBER ) IS
1198 SELECT segment1
1199 , org_id
1200 , carrying_out_organization_id
1201 , project_level_funding_flag
1202 FROM pa_projects_all
1203 WHERE project_id = C_Project_ID;
1204
1205
1206 CURSOR ItemNum ( C_Org_ID NUMBER , C_Item_ID NUMBER ) IS
1207 SELECT item_number
1208 , description
1209 FROM mtl_item_flexfields
1210 WHERE organization_id = C_Org_ID
1211 AND inventory_item_id = C_Item_ID;
1212
1213
1214 Cursor Qty_C ( P_Deliverable_Id Number ) Is
1215 Select Nvl(Sum( Bill_Quantity ), 0)
1216 From oke_k_billing_events
1217 Where Deliverable_Id = P_Deliverable_Id;
1218
1219 CURSOR BillLading ( C_Deliverable_ID NUMBER ) IS
1220 SELECT di.sequence_number
1221 FROM wsh_document_instances di
1222 , wsh_delivery_legs dl
1223 , wsh_delivery_details dd
1224 , wsh_delivery_assignments da
1225 WHERE dd.source_code = 'OKE'
1226 AND dd.source_line_id = C_Deliverable_ID
1227 AND da.delivery_detail_id = dd.delivery_detail_id
1228 AND dl.delivery_id = da.delivery_id
1229 AND di.entity_id = dl.delivery_leg_id
1230 AND di.entity_name = 'WSH_DELIVERY_LEGS'
1231 AND di.status <> 'CANCELED';
1232
1233
1234 Cursor Can_qty_c(p_deliverable_id number) is
1235 select nvl(sum(bill_quantity),0)
1236 from oke_k_billing_events e
1237 where Deliverable_Id = P_Deliverable_Id
1238 and exists ( Select 'Y'
1239 from pa_events p,pa_draft_invoice_items i
1240 where p.event_id=e.pa_event_id
1241 --and i.draft_inv_line_num_credited is null
1242 and p.event_num=i.event_num
1243 and p.project_id=i.project_id
1244 and nvl(p.task_id,0)=nvl(i.task_id,0)
1245 and exists( select 'X' from pa_draft_invoices_all iv
1246 where iv.project_id=i.project_id and iv.draft_invoice_num=i.draft_invoice_num and iv.canceled_flag='Y')
1247 and pa_events_pkg.is_event_billed(p.project_id,p.task_id,p.event_num,p.Bill_trans_bill_amount)='N');
1248
1249 L_Can_qty Number;
1250
1251
1252 L_Project_Id Number;
1253 L_Line_Id Number;
1254 L_Inventory_Org_Id Number;
1255 L_item_Id Number;
1256 L_Currency_Code Varchar2(30);
1257 L_Quantity Number;
1258 L_Billed_Quantity Number;
1259 L_Bill_quantity Number;
1260 L_Project_Num Varchar2(30);
1261 L_Item_Num Varchar2(80);
1262 L_Item_Description Varchar2(2000);
1263 L_Org_Id Number;
1264 L_Project_Funding_Level Varchar2(1);
1265 L_OU_Id Number;
1266 L_Org_Name Varchar2(240);
1267 L_Curr_Code Varchar2(30);
1268 L_Round_Unit Number;
1269 L_Conversion_Type Varchar2(150);
1270 L_Bill_Lading WSH_DOCUMENT_INSTANCES.SEQUENCE_NUMBER%TYPE;
1271 L_Unit_Price Number;
1272 L_Amount Number;
1273 L_Revenue_Amount Number;
1274 L_Line_Num Varchar2(150);
1275 L_Deliverable_Description VARCHAR2(240);
1276
1277
1278 BEGIN
1279
1280 IF l_k_billing_event_rec.deliverable_id<> OKE_API.G_MISS_NUM then
1281 Open Dlv_C ( l_k_billing_event_rec.deliverable_id );
1282 Fetch Dlv_C Into L_Project_Id, L_Line_Id, L_Inventory_Org_Id, L_Item_Id, L_Currency_Code, L_Quantity, L_Unit_Price, L_Deliverable_Description;
1283 Close Dlv_C;
1284
1285 -- ITEM_NUMBER, ITEM_ID, ITEM_DESCRIPTION
1286
1287 Open ItemNum ( L_Inventory_Org_Id, L_Item_Id );
1288 fetch ItemNum Into L_Item_Num, L_Item_Description;
1289 Close ItemNum;
1290
1291
1292 END IF;
1293
1294 IF l_project_id IS NOT NULL then
1295 Open ProjNum ( L_Project_Id );
1296 Fetch ProjNum Into L_Project_Num, L_OU_Id, L_Org_Id, L_Project_Funding_Level;
1297 Close ProjNum;
1298 END IF;
1299
1300
1301 -- BILL_QUANTITY
1302 IF l_k_billing_event_rec.deliverable_id<> OKE_API.G_MISS_NUM then
1303 Open Qty_C ( l_k_billing_event_rec.deliverable_id );
1304 Fetch Qty_C Into L_Billed_Quantity;
1305 Close Qty_C;
1306
1307 open Can_qty_c ( l_k_billing_event_rec.deliverable_id );
1308 fetch Can_qty_c into l_can_qty;
1309 close can_qty_c;
1310
1311 L_Bill_Quantity := L_Quantity - L_Billed_Quantity + l_can_qty;
1312
1313 If L_Bill_Quantity < 0 Then
1314 L_Bill_Quantity := 0;
1315 End If;
1316
1317
1318 -- BILL OF LADING
1319
1320 Open BillLading ( l_k_billing_event_rec.deliverable_id );
1321 Fetch BillLading Into L_Bill_Lading;
1322 Close BillLading;
1323
1324 END IF;
1325
1326 IF l_k_billing_event_rec.BILLING_EVENT_ID = OKE_API.G_MISS_NUM THEN
1327 l_k_billing_event_rec.BILLING_EVENT_ID := NULL;
1328 END IF;
1329
1330 IF l_k_billing_event_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
1331 l_k_billing_event_rec.CREATION_DATE := sysdate;
1332 END IF;
1333
1334 IF l_k_billing_event_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
1335 l_k_billing_event_rec.CREATED_BY := Fnd_Profile.Value('USER_ID');
1336 END IF;
1337
1338 IF l_k_billing_event_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
1339 l_k_billing_event_rec.LAST_UPDATE_DATE := sysdate;
1340 END IF;
1341
1342 IF l_k_billing_event_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
1343 l_k_billing_event_rec.LAST_UPDATED_BY := Fnd_Profile.Value('USER_ID');
1344
1345 END IF;
1346
1347 IF l_k_billing_event_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
1348 l_k_billing_event_rec.LAST_UPDATE_LOGIN := Fnd_Profile.Value('LOGIN_ID');
1349 END IF;
1350
1351 IF l_k_billing_event_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
1352 l_k_billing_event_rec.K_HEADER_ID := NULL;
1353 END IF;
1354
1355 IF l_k_billing_event_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
1356 l_k_billing_event_rec.K_LINE_ID := L_Line_Id;
1357 END IF;
1358
1359 IF l_k_billing_event_rec.DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
1360 l_k_billing_event_rec.DELIVERABLE_ID:= NULL;
1361 END IF;
1362
1363 IF l_k_billing_event_rec.BILL_EVENT_DATE = OKE_API.G_MISS_DATE THEN
1364 l_k_billing_event_rec.BILL_EVENT_DATE := sysdate;
1365 END IF;
1366
1367 IF l_k_billing_event_rec.BILL_EVENT_TYPE = OKE_API.G_MISS_CHAR THEN
1368 l_k_billing_event_rec.BILL_EVENT_TYPE := NULL;
1369 END IF;
1370
1371 IF l_k_billing_event_rec.PA_EVENT_ID = OKE_API.G_MISS_NUM THEN
1372 l_k_billing_event_rec.PA_EVENT_ID := NULL;
1373 END IF;
1374
1375 IF l_k_billing_event_rec.BILL_ITEM_ID = OKE_API.G_MISS_NUM THEN
1376 l_k_billing_event_rec.BILL_ITEM_ID := L_Item_Id;
1377 END IF;
1378
1379 IF l_k_billing_event_rec.BILL_LINE_ID = OKE_API.G_MISS_NUM THEN
1380 l_k_billing_event_rec.BILL_LINE_ID := L_Line_Id;
1381 END IF;
1382
1383 IF l_k_billing_event_rec.BILL_CHG_REQ_ID = OKE_API.G_MISS_NUM THEN
1384 l_k_billing_event_rec.BILL_CHG_REQ_ID := NULL;
1385 END IF;
1386
1387 IF l_k_billing_event_rec.BILL_PROJECT_ID = OKE_API.G_MISS_NUM THEN
1388 l_k_billing_event_rec.BILL_PROJECT_ID := L_Project_Id;
1389 END IF;
1390
1391 IF l_k_billing_event_rec.BILL_TASK_ID = OKE_API.G_MISS_NUM THEN
1392 l_k_billing_event_rec.BILL_TASK_ID := NULL;
1393 END IF;
1394
1395 IF l_k_billing_event_rec.BILL_ORGANIZATION_ID = OKE_API.G_MISS_NUM THEN
1396 l_k_billing_event_rec.BILL_ORGANIZATION_ID := L_Org_Id;
1397 END IF;
1398
1399 IF l_k_billing_event_rec.BILL_FUND_REF1 = OKE_API.G_MISS_CHAR THEN
1400 l_k_billing_event_rec.BILL_FUND_REF1 := NULL;
1401 END IF;
1402
1403 IF l_k_billing_event_rec.BILL_FUND_REF2 = OKE_API.G_MISS_CHAR THEN
1404 l_k_billing_event_rec.BILL_FUND_REF2 := NULL;
1405 END IF;
1406
1407 IF l_k_billing_event_rec.BILL_FUND_REF3 = OKE_API.G_MISS_CHAR THEN
1408 l_k_billing_event_rec.BILL_FUND_REF3 := NULL;
1409 END IF;
1410
1411 IF l_k_billing_event_rec.BILL_BILL_OF_LADING = OKE_API.G_MISS_CHAR THEN
1412 l_k_billing_event_rec.BILL_BILL_OF_LADING := L_Bill_Lading;
1413 END IF;
1414
1415 IF l_k_billing_event_rec.BILL_SERIAL_NUM = OKE_API.G_MISS_CHAR THEN
1416 l_k_billing_event_rec.BILL_SERIAL_NUM:= NULL;
1417 END IF;
1418
1419 IF l_k_billing_event_rec.BILL_CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
1420 l_k_billing_event_rec.BILL_CURRENCY_CODE:= L_Currency_Code;
1421 END IF;
1422
1423 IF l_k_billing_event_rec.BILL_RATE_TYPE = OKE_API.G_MISS_CHAR THEN
1424 l_k_billing_event_rec.BILL_RATE_TYPE:= NULL;
1425 END IF;
1426
1427 IF l_k_billing_event_rec.BILL_RATE_DATE = OKE_API.G_MISS_DATE THEN
1428 l_k_billing_event_rec.BILL_RATE_DATE:= NULL;
1429 END IF;
1430
1431 IF l_k_billing_event_rec.BILL_EXCHANGE_RATE = OKE_API.G_MISS_NUM THEN
1432 l_k_billing_event_rec.BILL_EXCHANGE_RATE:= NULL;
1433 END IF;
1434
1435 IF l_k_billing_event_rec.BILL_DESCRIPTION = OKE_API.G_MISS_CHAR THEN
1436
1437 -- Default description
1438
1439 IF l_deliverable_description IS NOT NULL THEN
1440 l_k_billing_event_rec.BILL_DESCRIPTION:=l_deliverable_description ;
1441 ELSE
1442 l_k_billing_event_rec.BILL_DESCRIPTION:=L_Item_Description ;
1443 END IF;
1444
1445 END IF;
1446
1447 IF l_k_billing_event_rec.BILL_QUANTITY = OKE_API.G_MISS_NUM THEN
1448 l_k_billing_event_rec.BILL_QUANTITY:= L_Bill_Quantity;
1449 END IF;
1450
1451 IF l_k_billing_event_rec.BILL_UNIT_PRICE = OKE_API.G_MISS_NUM THEN
1452 l_k_billing_event_rec.BILL_UNIT_PRICE:= null;
1453 END IF;
1454
1455 IF l_k_billing_event_rec.REVENUE_AMOUNT = OKE_API.G_MISS_NUM THEN
1456 l_k_billing_event_rec.REVENUE_AMOUNT:=null ;
1457 END IF;
1458
1459 IF l_k_billing_event_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
1460 l_k_billing_event_rec.ATTRIBUTE_CATEGORY:= NULL;
1461 END IF;
1462
1463 IF l_k_billing_event_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
1464 l_k_billing_event_rec.ATTRIBUTE1:= NULL;
1465 END IF;
1466
1467 IF l_k_billing_event_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
1468 l_k_billing_event_rec.ATTRIBUTE2:= NULL;
1469 END IF;
1470 IF l_k_billing_event_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
1471 l_k_billing_event_rec.ATTRIBUTE3:= NULL;
1472 END IF;
1473 IF l_k_billing_event_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
1474 l_k_billing_event_rec.ATTRIBUTE4:= NULL;
1475 END IF;
1476
1477 IF l_k_billing_event_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
1478 l_k_billing_event_rec.ATTRIBUTE5:= NULL;
1479 END IF;
1480
1481 IF l_k_billing_event_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
1482 l_k_billing_event_rec.ATTRIBUTE6:= NULL;
1483 END IF;
1484
1485 IF l_k_billing_event_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
1486 l_k_billing_event_rec.ATTRIBUTE7:= NULL;
1487 END IF;
1488
1489 IF l_k_billing_event_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
1490 l_k_billing_event_rec.ATTRIBUTE8:= NULL;
1491 END IF;
1492
1493 IF l_k_billing_event_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
1494 l_k_billing_event_rec.ATTRIBUTE9:= NULL;
1495 END IF;
1496
1497 IF l_k_billing_event_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
1498 l_k_billing_event_rec.ATTRIBUTE10:= NULL;
1499 END IF;
1500
1501 IF l_k_billing_event_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
1502 l_k_billing_event_rec.ATTRIBUTE11:= NULL;
1503 END IF;
1504
1505 IF l_k_billing_event_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
1506 l_k_billing_event_rec.ATTRIBUTE12:= NULL;
1507 END IF;
1508
1509 IF l_k_billing_event_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
1510 l_k_billing_event_rec.ATTRIBUTE13:= NULL;
1511 END IF;
1512
1513 IF l_k_billing_event_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
1514 l_k_billing_event_rec.ATTRIBUTE14:= NULL;
1515 END IF;
1516
1517 IF l_k_billing_event_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
1518 l_k_billing_event_rec.ATTRIBUTE15:= NULL;
1519 END IF;
1520
1521 IF l_k_billing_event_rec.INITIATED_FLAG = OKE_API.G_MISS_CHAR THEN
1522 l_k_billing_event_rec.INITIATED_FLAG:= NULL;
1523 END IF;
1524
1525
1526
1527 RETURN(l_k_billing_event_rec);
1528
1529 END null_out_defaults;
1530
1531
1532 BEGIN
1533 -- call START_ACTIVITY to create savepoint, check compatibility
1534 -- and initialize message list
1535 l_return_status := OKE_API.START_ACTIVITY(
1536 p_api_name => l_api_name,
1537 p_pkg_name => g_pkg_name,
1538 p_init_msg_list => p_init_msg_list,
1539 l_api_version => l_api_version,
1540 p_api_version => p_api_version,
1541 p_api_type => g_api_type,
1542 x_return_status => x_return_status);
1543
1544 -- Check if activity started successfully
1545 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1546 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1547 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1548 raise OKE_API.G_EXCEPTION_ERROR;
1549 End If;
1550
1551 l_k_billing_event_rec := null_out_defaults(p_k_billing_event_rec);
1552
1553
1554 ---validations
1555 l_return_status:=validate_billing_attributes(p_k_billing_event_rec => l_k_billing_event_rec
1556 );
1557
1558 If ( l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1559 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1560 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1561 raise OKE_API.G_EXCEPTION_ERROR;
1562 End If;
1563
1564 Recalculate_price(l_k_billing_event_rec,l_bill_unit_price,l_REVENUE_AMOUNT,l_exchange_rate);
1565 l_k_billing_event_rec.bill_unit_price:= l_bill_unit_price;
1566 l_k_billing_event_rec.REVENUE_AMOUNT:= l_REVENUE_AMOUNT;
1567 l_k_billing_event_rec.BILL_EXCHANGE_RATE:= l_exchange_rate;
1568
1569 Select oke_k_billing_events_s.nextval into l_event_id from dual;
1570 l_k_billing_event_rec.BILLING_EVENT_ID:= l_event_id;
1571 OKE_DELIVERABLE_BILLING_PVT.Insert_Billing_Info
1572 ( P_Deliverable_Id => l_k_billing_event_rec.DELIVERABLE_ID
1573 , P_Billing_Event_Id => l_k_billing_event_rec.BILLING_EVENT_ID
1574 , P_Pa_Event_Id => Null -- not populated at this moment
1575 , P_K_Header_Id => l_k_billing_event_rec.K_HEADER_ID
1576 , P_K_Line_Id => l_k_billing_event_rec.K_LINE_ID
1577 , P_Bill_Event_Type => l_k_billing_event_rec.BILL_EVENT_TYPE
1578 , P_Bill_Event_Date => l_k_billing_event_rec.BILL_EVENT_DATE
1579 , P_Bill_Item_Id => l_k_billing_event_rec.BILL_ITEM_ID
1580 , P_Bill_Line_Id => l_k_billing_event_rec.BILL_LINE_ID
1581 , P_Bill_Chg_Req_Id => l_k_billing_event_rec.BILL_CHG_REQ_ID
1582 , P_Bill_Project_Id => l_k_billing_event_rec.BILL_PROJECT_ID
1583 , P_Bill_Task_Id => l_k_billing_event_rec.BILL_TASK_ID
1584 , P_Bill_Organization_Id => l_k_billing_event_rec.BILL_ORGANIZATION_ID
1585 , P_Bill_Fund_Ref1 => l_k_billing_event_rec.BILL_FUND_REF1
1586 , P_Bill_Fund_Ref2 => l_k_billing_event_rec.BILL_FUND_REF2
1587 , P_Bill_Fund_Ref3 => l_k_billing_event_rec.BILL_FUND_REF3
1588 , P_Bill_Bill_Of_Lading => l_k_billing_event_rec.BILL_BILL_OF_LADING
1589 , P_Bill_Serial_Num => l_k_billing_event_rec.BILL_SERIAL_NUM
1590 , P_Bill_Currency_Code => l_k_billing_event_rec.BILL_CURRENCY_CODE
1591 , P_Bill_Rate_Type => l_k_billing_event_rec.BILL_RATE_TYPE
1592 , P_Bill_Rate_Date => l_k_billing_event_rec.BILL_RATE_DATE
1593 , P_Bill_Exchange_Rate => l_k_billing_event_rec.BILL_EXCHANGE_RATE
1594 , P_Bill_Description => l_k_billing_event_rec.BILL_DESCRIPTION
1595 , P_Bill_Quantity => l_k_billing_event_rec.BILL_QUANTITY
1596 , P_Bill_Unit_Price => l_k_billing_event_rec.BILL_UNIT_PRICE
1597 , P_Revenue_Amount => l_k_billing_event_rec.REVENUE_AMOUNT
1598 , P_created_By => Fnd_Profile.Value('USER_ID')
1599 , P_Creation_Date => SYSDATE
1600 , P_Last_Updated_By => Fnd_Profile.Value('USER_ID')
1601 , P_Last_Update_Login => Fnd_Profile.Value('LOGIN_ID')
1602 , P_Last_Update_Date => SYSDATE);
1603
1604 x_k_billing_event_rec:=l_k_billing_event_rec;
1605
1606 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data);
1608
1609 EXCEPTION
1610 when OKE_API.G_EXCEPTION_ERROR then
1611 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1612 p_api_name => l_api_name,
1613 p_pkg_name => g_pkg_name,
1614 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1615 x_msg_count => x_msg_count,
1616 x_msg_data => x_msg_data,
1617 p_api_type => g_api_type);
1618
1619 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1620 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1621 p_api_name => l_api_name,
1622 p_pkg_name => g_pkg_name,
1623 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1624 x_msg_count => x_msg_count,
1625 x_msg_data => x_msg_data,
1626 p_api_type => g_api_type);
1627
1628 when OTHERS then
1629 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1630 p_api_name => l_api_name,
1631 p_pkg_name => g_pkg_name,
1632 p_exc_name => 'OTHERS',
1633 x_msg_count => x_msg_count,
1634 x_msg_data => x_msg_data,
1635 p_api_type => g_api_type);
1636
1637 END create_k_billing_event;
1638
1639
1640 ----------------------------------
1641 -- FUNCTION populate_new_record --
1642 ----------------------------------
1643 FUNCTION populate_new_record (
1644 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type,
1645 x_k_billing_event_rec OUT NOCOPY OKE_BILLING_EVENT_PUB.k_billing_event_rec_type
1646 ) RETURN VARCHAR2 IS
1647
1648
1649 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1650
1651 CURSOR bill_evnt_csr is
1652 SELECT
1653 billing_event_id ,
1654 creation_date ,
1655 created_by ,
1656 last_update_date ,
1657 last_updated_by ,
1658 last_update_login ,
1659 k_header_id ,
1660 k_line_id ,
1661 deliverable_id ,
1662 bill_event_date ,
1663 bill_event_type ,
1664 pa_event_id ,
1665 bill_item_id ,
1666 bill_line_id ,
1667 bill_chg_req_id ,
1668 bill_project_id ,
1669 bill_task_id ,
1670 bill_organization_id ,
1671 bill_fund_ref1 ,
1672 bill_fund_ref2 ,
1673 bill_fund_ref3 ,
1674 bill_bill_of_lading ,
1675 bill_serial_num ,
1676 bill_currency_code ,
1677 bill_rate_type ,
1678 bill_rate_date ,
1679 bill_exchange_rate ,
1680 bill_description ,
1681 bill_quantity ,
1682 bill_unit_price ,
1683 revenue_amount ,
1684 attribute_category ,
1685 attribute1 ,
1686 attribute2 ,
1687 attribute3 ,
1688 attribute4 ,
1689 attribute5 ,
1690 attribute6 ,
1691 attribute7 ,
1692 attribute8 ,
1693 attribute9 ,
1694 attribute10 ,
1695 attribute11 ,
1696 attribute12 ,
1697 attribute13 ,
1698 attribute14 ,
1699 attribute15 ,
1700 initiated_flag
1701 from oke_k_billing_events
1702 where Billing_Event_Id = p_k_billing_event_rec.billing_event_id;
1703 l_bill_currency VARCHAR2(40);
1704
1705 l_k_billing_event_rec OKE_BILLING_EVENT_PUB.k_billing_event_rec_type;
1706 l_no_data_found boolean:= TRUE;
1707
1708 BEGIN
1709
1710
1711 x_k_billing_event_rec := p_k_billing_event_rec;
1712
1713
1714 OPEN bill_evnt_csr;
1715 FETCH bill_evnt_csr INTO
1716 l_k_billing_event_rec.billing_event_id ,
1717 l_k_billing_event_rec.creation_date ,
1718 l_k_billing_event_rec.created_by ,
1719 l_k_billing_event_rec.last_update_date ,
1720 l_k_billing_event_rec.last_updated_by ,
1721 l_k_billing_event_rec.last_update_login ,
1722 l_k_billing_event_rec.k_header_id ,
1723 l_k_billing_event_rec.k_line_id ,
1724 l_k_billing_event_rec.deliverable_id ,
1725 l_k_billing_event_rec.bill_event_date ,
1726 l_k_billing_event_rec.bill_event_type ,
1727 l_k_billing_event_rec.pa_event_id ,
1728 l_k_billing_event_rec.bill_item_id ,
1729 l_k_billing_event_rec.bill_line_id ,
1730 l_k_billing_event_rec.bill_chg_req_id ,
1731 l_k_billing_event_rec.bill_project_id ,
1732 l_k_billing_event_rec.bill_task_id ,
1733 l_k_billing_event_rec.bill_organization_id ,
1734 l_k_billing_event_rec.bill_fund_ref1 ,
1735 l_k_billing_event_rec.bill_fund_ref2 ,
1736 l_k_billing_event_rec.bill_fund_ref3 ,
1737 l_k_billing_event_rec.bill_bill_of_lading ,
1738 l_k_billing_event_rec.bill_serial_num ,
1739 l_k_billing_event_rec.bill_currency_code ,
1740 l_k_billing_event_rec.bill_rate_type ,
1741 l_k_billing_event_rec.bill_rate_date ,
1742 l_k_billing_event_rec.bill_exchange_rate ,
1743 l_k_billing_event_rec.bill_description ,
1744 l_k_billing_event_rec.bill_quantity ,
1745 l_k_billing_event_rec.bill_unit_price ,
1746 l_k_billing_event_rec.revenue_amount ,
1747 l_k_billing_event_rec.attribute_category ,
1748 l_k_billing_event_rec.attribute1 ,
1749 l_k_billing_event_rec.attribute2 ,
1750 l_k_billing_event_rec.attribute3 ,
1751 l_k_billing_event_rec.attribute4 ,
1752 l_k_billing_event_rec.attribute5 ,
1753 l_k_billing_event_rec.attribute6 ,
1754 l_k_billing_event_rec.attribute7 ,
1755 l_k_billing_event_rec.attribute8 ,
1756 l_k_billing_event_rec.attribute9 ,
1757 l_k_billing_event_rec.attribute10 ,
1758 l_k_billing_event_rec.attribute11 ,
1759 l_k_billing_event_rec.attribute12 ,
1760 l_k_billing_event_rec.attribute13 ,
1761 l_k_billing_event_rec.attribute14 ,
1762 l_k_billing_event_rec.attribute15 ,
1763 l_k_billing_event_rec.initiated_flag ;
1764 l_no_data_found := bill_evnt_csr%NOTFOUND;
1765 CLOSE bill_evnt_csr;
1766
1767 IF l_no_data_found THEN
1768 OKE_API.SET_MESSAGE(p_app_name =>g_app_name,
1769 p_msg_name =>G_REQUIRED_VALUE,
1770 p_token1 =>G_COL_NAME_TOKEN,
1771 p_token1_value =>'billing_event_id');
1772 l_return_status := OKE_API.G_RET_STS_ERROR;
1773
1774 else
1775
1776
1777
1778 IF X_k_billing_event_rec.BILLING_EVENT_ID = OKE_API.G_MISS_num THEN
1779 X_k_billing_event_rec.BILLING_EVENT_ID := l_k_billing_event_rec.BILLING_EVENT_ID;
1780 END IF;
1781
1782
1783
1784 IF X_k_billing_event_rec.BILL_PROJECT_ID = OKE_API.G_MISS_NUM THEN
1785 X_k_billing_event_rec.BILL_PROJECT_ID := l_k_billing_event_rec.BILL_PROJECT_ID;
1786 END IF;
1787
1788 IF X_k_billing_event_rec.BILL_TASK_ID = OKE_API.G_MISS_NUM THEN
1789 X_k_billing_event_rec.BILL_TASK_ID := l_k_billing_event_rec.BILL_TASK_ID;
1790 END IF;
1791
1792 IF X_k_billing_event_rec.BILL_ITEM_ID = OKE_API.G_MISS_NUM THEN
1793 X_k_billing_event_rec.BILL_ITEM_ID := l_k_billing_event_rec.BILL_ITEM_ID;
1794 END IF;
1795
1796 IF X_k_billing_event_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
1797 X_k_billing_event_rec.K_HEADER_ID := l_k_billing_event_rec.K_HEADER_ID;
1798 END IF;
1799
1800 IF X_k_billing_event_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
1801 X_k_billing_event_rec.K_LINE_ID := l_k_billing_event_rec.K_LINE_ID;
1802 END IF;
1803
1804 IF X_k_billing_event_rec.DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
1805 X_k_billing_event_rec.DELIVERABLE_ID := l_k_billing_event_rec.DELIVERABLE_ID;
1806 END IF;
1807
1808 IF X_k_billing_event_rec.BILL_EVENT_DATE = OKE_API.G_MISS_DATE THEN
1809 X_k_billing_event_rec.BILL_EVENT_DATE := l_k_billing_event_rec.BILL_EVENT_DATE;
1810 END IF;
1811
1812 IF X_k_billing_event_rec.BILL_EVENT_TYPE = OKE_API.G_MISS_CHAR THEN
1813 X_k_billing_event_rec.BILL_EVENT_TYPE := l_k_billing_event_rec.BILL_EVENT_TYPE;
1814 END IF;
1815
1816
1817
1818 IF X_k_billing_event_rec.PA_EVENT_ID = OKE_API.G_MISS_NUM THEN
1819 X_k_billing_event_rec.PA_EVENT_ID := l_k_billing_event_rec.PA_EVENT_ID;
1820 END IF;
1821
1822 IF X_k_billing_event_rec.BILL_LINE_ID = OKE_API.G_MISS_NUM THEN
1823 X_k_billing_event_rec.BILL_LINE_ID := l_k_billing_event_rec.BILL_LINE_ID;
1824 END IF;
1825
1826
1827 IF X_k_billing_event_rec.BILL_CHG_REQ_ID = OKE_API.G_MISS_NUM THEN
1828 X_k_billing_event_rec.BILL_CHG_REQ_ID := l_k_billing_event_rec.BILL_CHG_REQ_ID;
1829 END IF;
1830
1831
1832
1833 IF X_k_billing_event_rec.BILL_ORGANIZATION_ID = OKE_API.G_MISS_NUM THEN
1834 X_k_billing_event_rec.BILL_ORGANIZATION_ID := l_k_billing_event_rec.BILL_ORGANIZATION_ID;
1835 END IF;
1836
1837
1838 IF X_k_billing_event_rec.BILL_FUND_REF1 = OKE_API.G_MISS_CHAR THEN
1839 X_k_billing_event_rec.BILL_FUND_REF1 := l_k_billing_event_rec.BILL_FUND_REF1;
1840 END IF;
1841
1842 IF X_k_billing_event_rec.BILL_FUND_REF2 = OKE_API.G_MISS_CHAR THEN
1843 X_k_billing_event_rec.BILL_FUND_REF2 := l_k_billing_event_rec.BILL_FUND_REF2;
1844 END IF;
1845
1846
1847 IF X_k_billing_event_rec.BILL_FUND_REF3 = OKE_API.G_MISS_CHAR THEN
1848 X_k_billing_event_rec.BILL_FUND_REF3 := l_k_billing_event_rec.BILL_FUND_REF3;
1849 END IF;
1850
1851
1852
1853 IF X_k_billing_event_rec.BILL_BILL_OF_LADING = OKE_API.G_MISS_CHAR THEN
1854 X_k_billing_event_rec.BILL_BILL_OF_LADING := l_k_billing_event_rec.BILL_BILL_OF_LADING;
1855 END IF;
1856
1857
1858
1859 IF X_k_billing_event_rec.BILL_SERIAL_NUM = OKE_API.G_MISS_CHAR THEN
1860 X_k_billing_event_rec.BILL_SERIAL_NUM := l_k_billing_event_rec.BILL_SERIAL_NUM;
1861 END IF;
1862
1863
1864 IF X_k_billing_event_rec.BILL_CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
1865 X_k_billing_event_rec.BILL_CURRENCY_CODE := l_k_billing_event_rec.BILL_CURRENCY_CODE;
1866 END IF;
1867
1868 SELECT currency_code INTO l_bill_currency
1869 FROM okc_k_headers_b
1870 WHERE id = x_k_billing_event_rec.k_header_id;
1871
1872
1873
1874 IF X_k_billing_event_rec.BILL_RATE_TYPE = OKE_API.G_MISS_CHAR THEN
1875 IF l_bill_currency<> X_k_billing_event_rec.BILL_CURRENCY_CODE then
1876 X_k_billing_event_rec.BILL_RATE_TYPE := l_k_billing_event_rec.BILL_RATE_TYPE;
1877 ELSE
1878 X_k_billing_event_rec.BILL_RATE_TYPE :=NULL;
1879 END IF;
1880 END IF;
1881
1882
1883
1884 IF X_k_billing_event_rec.BILL_RATE_DATE = OKE_API.G_MISS_DATE THEN
1885
1886 IF l_bill_currency<> X_k_billing_event_rec.BILL_CURRENCY_CODE then
1887 X_k_billing_event_rec.BILL_RATE_DATE := l_k_billing_event_rec.BILL_RATE_DATE;
1888 ELSE
1889 X_k_billing_event_rec.BILL_RATE_DATE :=NULL;
1890 END IF;
1891
1892 END IF;
1893
1894
1895 IF X_k_billing_event_rec.BILL_EXCHANGE_RATE = OKE_API.G_MISS_NUM THEN
1896
1897 IF l_bill_currency<> X_k_billing_event_rec.BILL_CURRENCY_CODE then
1898 X_k_billing_event_rec.BILL_EXCHANGE_RATE := l_k_billing_event_rec.BILL_EXCHANGE_RATE;
1899 ELSE
1900 X_k_billing_event_rec.BILL_EXCHANGE_RATE :=NULL;
1901 END IF;
1902
1903 END IF;
1904
1905 IF l_bill_currency = X_k_billing_event_rec.BILL_CURRENCY_CODE THEN
1906 X_k_billing_event_rec.BILL_RATE_TYPE:=NULL;
1907 X_k_billing_event_rec.BILL_RATE_DATE :=NULL;
1908 X_k_billing_event_rec.BILL_EXCHANGE_RATE :=NULL;
1909 END IF;
1910
1911 IF X_k_billing_event_rec.BILL_DESCRIPTION = OKE_API.G_MISS_CHAR THEN
1912 X_k_billing_event_rec.BILL_DESCRIPTION := l_k_billing_event_rec.BILL_DESCRIPTION;
1913 END IF;
1914
1915
1916 IF X_k_billing_event_rec.BILL_QUANTITY = OKE_API.G_MISS_NUM THEN
1917 X_k_billing_event_rec.BILL_QUANTITY := l_k_billing_event_rec.BILL_QUANTITY;
1918 END IF;
1919
1920 IF X_k_billing_event_rec.BILL_UNIT_PRICE = OKE_API.G_MISS_NUM THEN
1921 X_k_billing_event_rec.BILL_UNIT_PRICE := null;
1922 END IF;
1923
1924 IF X_k_billing_event_rec.REVENUE_AMOUNT = OKE_API.G_MISS_NUM THEN
1925 X_k_billing_event_rec.REVENUE_AMOUNT := l_k_billing_event_rec.REVENUE_AMOUNT;
1926 END IF;
1927
1928
1929 IF X_k_billing_event_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
1930 X_k_billing_event_rec.CREATED_BY := l_k_billing_event_rec.CREATED_BY;
1931 END IF;
1932
1933 IF X_k_billing_event_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
1934 X_k_billing_event_rec.CREATION_DATE := l_k_billing_event_rec.CREATION_DATE;
1935 END IF;
1936
1937 IF X_k_billing_event_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
1938 X_k_billing_event_rec.LAST_UPDATED_BY := l_k_billing_event_rec.LAST_UPDATED_BY;
1939 END IF;
1940
1941 IF X_k_billing_event_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
1942 X_k_billing_event_rec.LAST_UPDATE_LOGIN := l_k_billing_event_rec.LAST_UPDATE_LOGIN;
1943 END IF;
1944
1945 IF X_k_billing_event_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
1946 X_k_billing_event_rec.LAST_UPDATE_DATE := l_k_billing_event_rec.LAST_UPDATE_DATE;
1947 END IF;
1948
1949 IF X_k_billing_event_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
1950 X_k_billing_event_rec.ATTRIBUTE_CATEGORY := l_k_billing_event_rec.ATTRIBUTE_CATEGORY;
1951 END IF;
1952
1953 IF X_k_billing_event_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
1954 X_k_billing_event_rec.ATTRIBUTE1 := l_k_billing_event_rec.ATTRIBUTE1;
1955 END IF;
1956
1957 IF X_k_billing_event_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
1958 X_k_billing_event_rec.ATTRIBUTE2 := l_k_billing_event_rec.ATTRIBUTE2;
1959 END IF;
1960
1961 IF X_k_billing_event_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
1962 X_k_billing_event_rec.ATTRIBUTE3 := l_k_billing_event_rec.ATTRIBUTE3;
1963 END IF;
1964
1965 IF X_k_billing_event_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
1966 X_k_billing_event_rec.ATTRIBUTE4 := l_k_billing_event_rec.ATTRIBUTE4;
1967 END IF;
1968
1969 IF X_k_billing_event_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
1970 X_k_billing_event_rec.ATTRIBUTE5 := l_k_billing_event_rec.ATTRIBUTE5;
1971 END IF;
1972
1973 IF X_k_billing_event_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
1974 X_k_billing_event_rec.ATTRIBUTE6 := l_k_billing_event_rec.ATTRIBUTE6;
1975 END IF;
1976
1977 IF X_k_billing_event_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
1978 X_k_billing_event_rec.ATTRIBUTE7 := l_k_billing_event_rec.ATTRIBUTE7;
1979 END IF;
1980
1981 IF X_k_billing_event_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
1982 X_k_billing_event_rec.ATTRIBUTE8 := l_k_billing_event_rec.ATTRIBUTE8;
1983 END IF;
1984
1985 IF X_k_billing_event_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
1986 X_k_billing_event_rec.ATTRIBUTE9 := l_k_billing_event_rec.ATTRIBUTE9;
1987 END IF;
1988
1989 IF X_k_billing_event_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
1990 X_k_billing_event_rec.ATTRIBUTE10 := l_k_billing_event_rec.ATTRIBUTE10;
1991 END IF;
1992
1993 IF X_k_billing_event_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
1994 X_k_billing_event_rec.ATTRIBUTE11 := l_k_billing_event_rec.ATTRIBUTE11;
1995 END IF;
1996
1997 IF X_k_billing_event_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
1998 X_k_billing_event_rec.ATTRIBUTE12 := l_k_billing_event_rec.ATTRIBUTE12;
1999 END IF;
2000
2001 IF X_k_billing_event_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
2002 X_k_billing_event_rec.ATTRIBUTE13 := l_k_billing_event_rec.ATTRIBUTE13;
2003 END IF;
2004
2005 IF X_k_billing_event_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
2006 X_k_billing_event_rec.ATTRIBUTE14 := l_k_billing_event_rec.ATTRIBUTE14;
2007 END IF;
2008
2009 IF X_k_billing_event_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
2010 X_k_billing_event_rec.ATTRIBUTE15 := l_k_billing_event_rec.ATTRIBUTE15;
2011 END IF;
2012
2013 END IF;
2014
2015 RETURN(l_return_status);
2016
2017 END populate_new_record;
2018
2019
2020 PROCEDURE update_k_billing_event(p_api_version IN NUMBER,
2021 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2022 p_k_billing_event_rec IN OKE_BILLING_EVENT_PUB.k_billing_event_rec_type,
2023 x_return_status OUT NOCOPY VARCHAR2,
2024 x_msg_count OUT NOCOPY NUMBER,
2025 x_msg_data OUT NOCOPY VARCHAR2
2026 )
2027 IS
2028
2029 l_event_id NUMBER;
2030 l_api_name CONSTANT VARCHAR2(30) := 'update_k_billing_event';
2031 l_api_version CONSTANT NUMBER := 1.0;
2032 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2033 l_k_billing_event_rec OKE_BILLING_EVENT_PUB.k_billing_event_rec_type;
2034 l_bill_unit_price NUMBER;
2035 l_REVENUE_AMOUNT NUMBER;
2036 l_exchange_rate NUMBER;
2037 BEGIN
2038 -- call START_ACTIVITY to create savepoint, check compatibility
2039 -- and initialize message list
2040 l_return_status := OKE_API.START_ACTIVITY(
2041 p_api_name => l_api_name,
2042 p_pkg_name => g_pkg_name,
2043 p_init_msg_list => p_init_msg_list,
2044 l_api_version => l_api_version,
2045 p_api_version => p_api_version,
2046 p_api_type => g_api_type,
2047 x_return_status => x_return_status);
2048
2049 -- Check if activity started successfully
2050 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2051 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2052 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2053 raise OKE_API.G_EXCEPTION_ERROR;
2054 End If;
2055
2056 ---populate the
2057 l_return_status:= populate_new_record(p_k_billing_event_rec => p_k_billing_event_rec ,
2058 x_k_billing_event_rec => l_k_billing_event_rec) ;
2059 ---validations
2060
2061
2062 If ( l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2063 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2064 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2065 raise OKE_API.G_EXCEPTION_ERROR;
2066 End If;
2067
2068 l_return_status:=validate_billing_attributes(p_k_billing_event_rec => l_k_billing_event_rec);
2069
2070
2071 If ( l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2072 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2073 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2074 raise OKE_API.G_EXCEPTION_ERROR;
2075 End If;
2076
2077
2078 Recalculate_price(l_k_billing_event_rec,l_bill_unit_price,l_REVENUE_AMOUNT,l_exchange_rate);
2079 l_k_billing_event_rec.bill_unit_price:= l_bill_unit_price;
2080 l_k_billing_event_rec.REVENUE_AMOUNT:= l_REVENUE_AMOUNT;
2081 l_k_billing_event_rec.BILL_EXCHANGE_RATE:= l_exchange_rate;
2082
2083
2084
2085 OKE_DELIVERABLE_BILLING_PVT.Update_Billing_Info
2086 ( P_Deliverable_ID => l_k_billing_event_rec.DELIVERABLE_ID
2087 , P_Billing_Event_ID => l_k_billing_event_rec.BILLING_EVENT_ID
2088 , P_Bill_Event_Type => l_k_billing_event_rec.BILL_EVENT_TYPE
2089 , P_Bill_Event_Date => l_k_billing_event_rec.BILL_EVENT_DATE
2090 , P_Bill_Project_ID => l_k_billing_event_rec.BILL_PROJECT_ID
2091 , P_Bill_Task_ID => l_k_billing_event_rec.BILL_TASK_ID
2092 , P_Bill_Org_ID => l_k_billing_event_rec.BILL_ORGANIZATION_ID
2093 , P_Bill_Line_ID => l_k_billing_event_rec.BILL_LINE_ID
2094 , P_Bill_Chg_Req_ID => l_k_billing_event_rec.BILL_CHG_REQ_ID
2095 , P_Bill_Item_ID => l_k_billing_event_rec.BILL_ITEM_ID
2096 , P_Bill_Description => l_k_billing_event_rec.BILL_DESCRIPTION
2097 , P_Bill_Unit_Price => l_k_billing_event_rec.BILL_UNIT_PRICE
2098 , P_Bill_Quantity => l_k_billing_event_rec.BILL_QUANTITY
2099 , P_Bill_Currency_Code => l_k_billing_event_rec.BILL_CURRENCY_CODE
2100 , P_Bill_Rate_Type => l_k_billing_event_rec.BILL_RATE_TYPE
2101 , P_Bill_Rate_Date => l_k_billing_event_rec.BILL_RATE_DATE
2102 , P_Bill_Exchange_Rate => l_k_billing_event_rec.BILL_EXCHANGE_RATE
2103 , P_Revenue_Amount => l_k_billing_event_rec.REVENUE_AMOUNT
2104 , P_Bill_Of_Lading => l_k_billing_event_rec.BILL_BILL_OF_LADING
2105 , P_Bill_Serial_Num =>l_k_billing_event_rec.BILL_SERIAL_NUM
2106 , P_Bill_Fund_Ref1 => l_k_billing_event_rec.BILL_FUND_REF1
2107 , P_Bill_Fund_Ref2 => l_k_billing_event_rec.BILL_FUND_REF2
2108 , P_Bill_Fund_Ref3 => l_k_billing_event_rec.BILL_FUND_REF3
2109 , P_LAST_UPDATED_BY => FND_PROFILE.VALUE('USER_ID')
2110 , P_LAST_UPDATE_LOGIN => FND_PROFILE.VALUE('LOGIN_ID')
2111 , P_LAST_UPDATE_DATE => SYSDATE
2112 );
2113
2114
2115
2116 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2117 x_msg_data => x_msg_data);
2118
2119 EXCEPTION
2120 when OKE_API.G_EXCEPTION_ERROR then
2121 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2122 p_api_name => l_api_name,
2123 p_pkg_name => g_pkg_name,
2124 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2125 x_msg_count => x_msg_count,
2126 x_msg_data => x_msg_data,
2127 p_api_type => g_api_type);
2128
2129 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2130 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2131 p_api_name => l_api_name,
2132 p_pkg_name => g_pkg_name,
2133 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2134 x_msg_count => x_msg_count,
2135 x_msg_data => x_msg_data,
2136 p_api_type => g_api_type);
2137
2138 when OTHERS then
2139 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2140 p_api_name => l_api_name,
2141 p_pkg_name => g_pkg_name,
2142 p_exc_name => 'OTHERS',
2143 x_msg_count => x_msg_count,
2144 x_msg_data => x_msg_data,
2145 p_api_type => g_api_type);
2146
2147
2148 END update_k_billing_event;
2149
2150
2151 PROCEDURE delete_k_billing_event(p_api_version IN NUMBER,
2152 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2153 p_billing_event_id IN NUMBER,
2154 x_return_status OUT NOCOPY VARCHAR2,
2155 x_msg_count OUT NOCOPY NUMBER,
2156 x_msg_data OUT NOCOPY VARCHAR2
2157 )
2158 IS
2159 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_BILLING_EVENT';
2160 l_api_version CONSTANT NUMBER := 1.0;
2161 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2162 l_msg_count NUMBER;
2163 l_msg_data VARCHAR2(2000);
2164
2165 L_sts_code VARCHAR2(30);
2166
2167 CURSOR cur_get_event_info (p_BILLING_EVENT_ID NUMBER)
2168 IS
2169 SELECT PA_EVENT_ID
2170 FROM oke_k_billing_events
2171 WHERE BILLING_EVENT_ID = p_BILLING_EVENT_ID;
2172 l_pa_event_id NUMBER;
2173
2174
2175 CURSOR EventNum(p_PA_EVENT_ID NUMBER) IS
2176 SELECT event_num
2177 , project_id
2178 , task_id
2179 , rowid
2180 , bill_amount
2181 FROM pa_events
2182 WHERE event_id = p_PA_EVENT_ID;
2183
2184 L_task_ID NUMBER;
2185 L_Event_Num NUMBER;
2186 L_Project_ID NUMBER;
2187 L_RowID VARCHAR2(18);
2188 L_Bill_Amount NUMBER;
2189
2190
2191 BEGIN
2192
2193 -- call START_ACTIVITY to create savepoint, check compatibility
2194 -- and initialize message list
2195 l_return_status := OKE_API.START_ACTIVITY(
2196 p_api_name => l_api_name,
2197 p_pkg_name => g_pkg_name,
2198 p_init_msg_list => p_init_msg_list,
2199 l_api_version => l_api_version,
2200 p_api_version => p_api_version,
2201 p_api_type => g_api_type,
2202 x_return_status => x_return_status);
2203
2204 -- Check if activity started successfully
2205 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2206 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2207 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2208 raise OKE_API.G_EXCEPTION_ERROR;
2209 End If;
2210
2211 -- Validate Event id
2212 OPEN cur_get_event_info(p_billing_event_id);
2213 FETCH cur_get_event_info INTO l_pa_event_id;
2214 IF cur_get_event_info%NOTFOUND THEN
2215 CLOSE cur_get_event_info;
2216 OKE_API.SET_MESSAGE
2217 (p_app_name => g_app_name,
2218 p_msg_name => g_invalid_value,
2219 p_token1 => g_col_name_token,
2220 p_token1_value => 'Billing_Event_Id');
2221
2222 raise OKE_API.G_EXCEPTION_ERROR;
2223 END IF;
2224 CLOSE cur_get_event_info;
2225
2226 IF l_pa_event_id IS NOT NULL THEN
2227
2228 OPEN EventNum(l_pa_event_id);
2229 FETCH EventNum INTO L_Event_Num, L_Project_ID, L_task_ID, L_RowID, L_Bill_Amount;
2230 CLOSE EventNum;
2231
2232 IF L_RowID IS NOT NULL THEN
2233 IF ( PA_EVENTS_PKG.Is_Event_Billed
2234 ( L_Project_ID
2235 , L_Task_ID
2236 , L_Event_Num
2237 , L_Bill_Amount ) = 'Y')
2238 THEN
2239 OKE_API.SET_MESSAGE
2240 (p_app_name => g_app_name,
2241 p_msg_name => 'OKE_BILL_EVENT_PROCESSED_DEL'
2242 );
2243 raise OKE_API.G_EXCEPTION_ERROR;
2244 END IF; -- PA_EVENTS_PKG.Is_Event_Billed
2245 END IF; -- L_RowID is not null
2246 END IF; -- l_pa_event_id is not null
2247
2248 -- delete billing info
2249 OKE_DELIVERABLE_BILLING_PVT.Delete_Billing_Info (
2250 P_Billing_Event_ID => p_billing_event_id
2251 );
2252
2253 -- delete billing info
2254 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2255 x_msg_data => x_msg_data);
2256
2257 EXCEPTION
2258 when OKE_API.G_EXCEPTION_ERROR then
2259 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2260 p_api_name => l_api_name,
2261 p_pkg_name => g_pkg_name,
2262 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2263 x_msg_count => x_msg_count,
2264 x_msg_data => x_msg_data,
2265 p_api_type => g_api_type);
2266
2267 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2268 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2269 p_api_name => l_api_name,
2270 p_pkg_name => g_pkg_name,
2271 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2272 x_msg_count => x_msg_count,
2273 x_msg_data => x_msg_data,
2274 p_api_type => g_api_type);
2275
2276 when OTHERS then
2277 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2278 p_api_name => l_api_name,
2279 p_pkg_name => g_pkg_name,
2280 p_exc_name => 'OTHERS',
2281 x_msg_count => x_msg_count,
2282 x_msg_data => x_msg_data,
2283 p_api_type => g_api_type);
2284 END delete_k_billing_event;
2285
2286
2287 END OKE_BILLING_EVENT_PUB;