eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OZF_CLAIM_UTILITY_PVT

Source


1 PACKAGE BODY OZF_claim_Utility_pvt as
2 /* $Header: ozfvcutb.pls 120.11.12020000.3 2012/08/08 09:53:51 ninarasi ship $ */
3 -- Start of Comments
4 -- Package name     : OZF_claim_Utility_pvt
5 -- Purpose          :
6 -- History          : Fix for Bug#12882998 : THE CLAIMS RULE BASED SETTLEMENT ENGINE
7 --                    PROGRAM IS NOT LOOKING AT THE REMAINING
8 -- NOTE             :
9 --8-Aug-2012  ninarasi         Bug 13498038 - CLAIM CUSTOMER REFERENCE NEEDS TO EXPAND TO 100 CHARACTERS TO BE SAME AS AR
10 -- End of Comments
11 
12 G_PKG_NAME             CONSTANT  VARCHAR2(30) := 'OZF_CLAIM_UTILITY_PVT';
13 
14 G_FILE_NAME            CONSTANT VARCHAR2(12) := 'ozfvcutb.pls';
15 
16 OZF_DEBUG_HIGH_ON     CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
17 OZF_DEBUG_LOW_ON      CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
18 g_bulk_limit  CONSTANT NUMBER := 5000;  -- yzhao: Sep 8,2005 bulk fetch limit. It should get from profile.
19 
20 
21 
22 --   *******************************************************
23 --    Start of Comments
24 --   *******************************************************
25 --   API Name:  Check_Claim_Access
26 --   Type    :  Public
27 --   Pre-Req :
28 --   Parameters:
29 --   IN
30 --       p_api_version_number      IN   NUMBER     Required
31 --       p_init_msg_list           IN   VARCHAR2   Optional  Default = FND_API_G_FALSE
32 --       p_commit                  IN   VARCHAR2   Optional  Default = FND_API.G_FALSE
33 --       p_validation_level        IN   NUMBER     Optional  Default = FND_API.G_VALID_LEVEL_FULL
34 --       P_object_id               IN   NUMBER
35 --       P_object_type             IN   VARCHAR2
36 --       P_user_id                 IN   NUMBER
37 --
38 --   OUT:
39 --       x_return_status           OUT  VARCHAR2
40 --       x_msg_count               OUT  NUMBER
41 --       x_msg_data                OUT  VARCHAR2
42 --       x_access                  OUT  VARCHAR2  F : FULL: User can update any data
43 --                                                R : RESTRICTED : User can only update data other than owner
44 --                                                N : NULL : User has no update priviledge
45 --   Version : Current version 1.0
46 --
47 --   Note: This procedure checks security access to a claim of a user
48 --
49 --   End of Comments
50 --
51 PROCEDURE Check_Claim_access(
52     P_Api_Version_Number         IN   NUMBER,
53     P_Init_Msg_List              IN   VARCHAR2     := FND_API.G_FALSE,
54     p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
55     P_Commit                     IN   VARCHAR2     := FND_API.G_FALSE,
56          P_object_id                  IN   NUMBER,
57     P_object_type                IN   VARCHAR2,
58          P_user_id                    IN   NUMBER,
59 
60     X_Return_Status              OUT NOCOPY  VARCHAR2,
61     X_Msg_Count                  OUT NOCOPY  NUMBER,
62     X_Msg_Data                   OUT NOCOPY  VARCHAR2,
63     x_access                     OUT NOCOPY  VARCHAR2
64     )
65 IS
66 l_api_name                CONSTANT VARCHAR2(30) := 'Check_Claim_access';
67 l_api_version_number      CONSTANT NUMBER   := 1.0;
68 l_full_name               CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
69 l_profile_value           VARCHAR2(30);
70 
71 l_access varchar2(1) :='N';  --  F : FULL: User can update sensitive metric data
72                              --  R : RESTRICTED : User can only update data other than sensitive metric data
73                              --  N : NULL : User is no
74 BEGIN
75    -- Standard Start of API savepoint
76    SAVEPOINT Check_Claim_ACC;
77 
78    IF OZF_DEBUG_HIGH_ON THEN
79       OZF_Utility_PVT.debug_message(l_full_name||': start');
80    END IF;
81 
82    -- Standard call to check for call compatibility.
83    IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
84                                         p_api_version_number,
85                                         l_api_name,
86                                         G_PKG_NAME)
87    THEN
88        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89    END IF;
90 
91    -- Initialize message list if p_init_msg_list is set to TRUE.
92    IF FND_API.to_Boolean( p_init_msg_list )
93    THEN
94        FND_MSG_PUB.initialize;
95    END IF;
96 
97    -- Initialize API return status to SUCCESS
98    x_return_status := FND_API.G_RET_STS_SUCCESS;
99 
100  /*     IF AMS_access_PVT.check_owner(
101    p_object_id         IN  NUMBER,
102    p_object_type       IN  VARCHAR2,
103    p_user_or_role_id   IN  NUMBER,
104    p_user_or_role_type ) OR
105 */
106    -- There is no need to check the owner since owner and group memeber has the same update priviledge.
107    l_access :=AMS_access_PVT.check_update_access(
108       p_object_id         => P_object_id,
109       p_object_type       => P_object_type,
110       p_user_or_role_id   => p_user_id,
111       p_user_or_role_type => 'USER'
112    );
113 
114    IF l_access = 'F' OR AMS_access_PVT.Check_Admin_access(p_resource_id => p_user_id) THEN
115       x_access := 'F';
116    ELSE
117       l_profile_value :=  NVL(fnd_profile.value('OZF_CLAIM_UPDATE_ACCESS'), 'VIEW');
118       IF l_profile_value = 'UPDATE' THEN
119               x_access := 'R';
120       ELSE
121               x_access := 'N';
122       END IF;
123    END IF;
124 
125    IF OZF_DEBUG_HIGH_ON THEN
126       OZF_Utility_PVT.debug_message('user_id='||p_user_id||' update_access is '||l_access);
127       OZF_Utility_PVT.debug_message('claim access is '||x_access);
128    END IF;
129 
130    --
131    -- End of API body
132    --
133    -- Standard check for p_commit
134    IF FND_API.to_Boolean( p_commit )
135    THEN
136        COMMIT WORK;
137    END IF;
138 
139    IF OZF_DEBUG_HIGH_ON THEN
140       OZF_Utility_PVT.debug_message(l_full_name||': end');
141    END IF;
142 
143    -- Standard call to get message count and if count is 1, get message info.
144    FND_MSG_PUB.Count_And_Get
145      (p_count          =>   x_msg_count,
146       p_data           =>   x_msg_data
147    );
148 EXCEPTION
149    WHEN FND_API.G_EXC_ERROR THEN
150     ROLLBACK TO Check_Claim_ACC;
151     x_return_status := FND_API.G_RET_STS_ERROR;
152     -- Standard call to get message count and if count=1, get the message
153     FND_MSG_PUB.Count_And_Get (
154             p_encoded => FND_API.G_FALSE,
155             p_count => x_msg_count,
156             p_data  => x_msg_data
157     );
158    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159     ROLLBACK TO Check_Claim_ACC;
160     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161     -- Standard call to get message count and if count=1, get the message
162     FND_MSG_PUB.Count_And_Get (
163             p_encoded => FND_API.G_FALSE,
164             p_count => x_msg_count,
165             p_data  => x_msg_data
166     );
167    WHEN OTHERS THEN
168     ROLLBACK TO Check_Claim_ACC;
169     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
171        FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CHK_ACS_ERR');
172        FND_MSG_PUB.add;
173     END IF;
174     -- Standard call to get message count and if count=1, get the message
175     FND_MSG_PUB.Count_And_Get (
176             p_encoded => FND_API.G_FALSE,
177             p_count => x_msg_count,
178             p_data  => x_msg_data
179     );
180 End Check_Claim_access;
181 
182 --   *******************************************************
183 --    Start of Comments
184 --   *******************************************************
185 --   API Name:  Normalize_Customer_Reference
186 --   Type    :  Public
187 --   Pre-Req :
188 --   Parameters:
189 --   IN
190 --       p_customer_reference      IN   VARCHAR2
191 --
192 --   OUT:
193 --       x_normalized_reference    OUT  VARCHAR2
194 --   Version : Current version 1.0
195 --
196 --   Note: This procedure normalizes the customer reference number.
197 --
198 --   End of Comments
199 --
200 PROCEDURE  Normalize_Customer_Reference (
201     p_customer_reference       IN   VARCHAR2
202    ,x_normalized_reference     OUT NOCOPY  VARCHAR2
203    )
204 IS
205 
206 l_normalized_reference VARCHAR2(100) := ''; --Fix for bug 13498038
207 l_char                 VARCHAR2(1);
208 
209 BEGIN
210    -- loop over all characters
211    FOR i IN 1..LENGTH(p_customer_reference) LOOP
212       l_char := SUBSTRB(p_customer_reference, i, 1);
213 
214       -- change 'O' and 'o' to '0'
215       IF l_char = 'O' OR l_char = 'o' THEN
216          l_normalized_reference := l_normalized_reference || '0';
217 
218       -- change 'I' and 'l' to '1'
219       ELSIF l_char = 'I' OR l_char = 'l' THEN
220          l_normalized_reference := l_normalized_reference || '1';
221 
222       -- ignore special characters; change characters to upper case
223       ELSIF INSTR(' !"#$%&''()*+,-./:;<>=?@[\]^{|}~', l_char) = 0 THEN
224          l_normalized_reference := l_normalized_reference || UPPER(l_char);
225       END IF;
226    END LOOP;
227 
228    -- remove prefix 'DM'
229    l_normalized_reference := LTRIM(l_normalized_reference, 'DM');
230 
231    -- remove leading '0's
232    l_normalized_reference := LTRIM(l_normalized_reference, '0');
233 
234    x_normalized_reference := l_normalized_reference;
235 END Normalize_Customer_Reference;
236 
237 /*=======================================================================*
238  | Procedure
239  |    Normalize_Credit_Reference
240  |
241  | PURPOSE
242  |    Returns Normalized Credit Reference Number
243  |
244  | NOTES
245  |
246  | HISTORY
247  |    20-JUN-2009  KPATRO  Create.
248  *=======================================================================*/
249 FUNCTION Normalize_Credit_Reference (p_credit_ref  IN  VARCHAR2)
250 RETURN VARCHAR2
251 IS
252 
253 l_credit_ref_norm VARCHAR2(30);
254 
255 BEGIN
256 
257    Normalize_Customer_Reference(
258              p_customer_reference => p_credit_ref
259             ,x_normalized_reference => l_credit_ref_norm
260           );
261 
262    return l_credit_ref_norm;
263 
264 EXCEPTION
265    WHEN OTHERS THEN
266        return NULL;
267 END Normalize_Credit_Reference;
268 
269 /*=======================================================================*
270  | Procedure
271  |    Create_Log
272  |
273  | PURPOSE
274  |    This procedure wll help to audit the number of records
275  |    processed by Rule Based Engine
276  |
277  | NOTES
278  |
279  | HISTORY
280  |    20-JUN-2009  KPATRO  Create.
281  |    19-Aug-2009  KPATRO  Removed the Bulk insert for Bug 8809877
282  |
283  *=======================================================================*/
284 
285 PROCEDURE Create_Log(
286     p_api_version         IN   NUMBER,
287     p_init_msg_list       IN   VARCHAR2     := FND_API.G_FALSE,
288     p_commit              IN  VARCHAR2  := FND_API.g_false,
289     p_validation_level    IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
290     p_exact_match_tbl     IN  ozf_rule_match_tbl_type,
291     p_possible_match_tbl  IN  ozf_rule_match_tbl_type,
292     p_accrual_match_tbl   IN  ozf_accrual_match_tbl_type,
293     x_Return_Status       OUT NOCOPY  VARCHAR2,
294     x_Msg_Count           OUT NOCOPY  NUMBER,
295     x_Msg_Data            OUT NOCOPY  VARCHAR2
296 
297  )
298 IS
299 l_api_name                CONSTANT VARCHAR2(30) := 'Create_Log';
300 l_api_version_number      CONSTANT NUMBER   := 1.0;
301 l_full_name               CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
302 
303 l_msg_count     NUMBER;
304 l_msg_data      VARCHAR2(2000);
305 l_return_status VARCHAR2(10);
306 
307 l_api_version   NUMBER := 1.0;
308 
309 
310 l_exactmatchTbl ozf_rule_match_tbl_type := p_exact_match_tbl;
311 l_possiblematchTbl ozf_rule_match_tbl_type := p_possible_match_tbl;
312 l_accrualmatchTbl ozf_accrual_match_tbl_type := p_accrual_match_tbl;
313 
314 l_exact_match_rec_type  ozf_rule_match_rec_type;
315 l_poss_match_rec_type  ozf_rule_match_rec_type;
316 l_accrual_match_rec_type ozf_accrual_match_rec_type;
317 
318 BEGIN
319 SAVEPOINT CREATE_LOG;
320 
321  IF OZF_DEBUG_HIGH_ON THEN
322       OZF_Utility_PVT.debug_message(l_full_name||': start');
323  END IF;
324 
325 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------ Rule Based Settlement Report --------------------------------_*');
326 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Start Date & Time: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
327 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'End Date & Time: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
329 
330 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
331 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '100% Credit Matches :' || l_exactmatchTbl.count);
332 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Possible Credit Matches :' || l_possiblematchTbl.count);
333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Accrual Matches :' || l_accrualmatchTbl.count);
334 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
335 
336  IF (l_exactmatchTbl.count >0) THEN
337    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '100% Credit Matches ');
338     FOR j IN l_exactmatchTbl.FIRST..l_exactmatchTbl.LAST  LOOP
339         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
340         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Deduction #', 40, ' ') || ': ' || l_exactmatchTbl(j).claim_number);
341         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Credit Memo #', 40, ' ') || ': ' || l_exactmatchTbl(j).credit_memo_number);
342         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Deduction Amount', 40, ' ') || ': ' || l_exactmatchTbl(j).claim_amount ||' ' || l_exactmatchTbl(j).currency_code);
343         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Credit Amount', 40, ' ') || ': ' || l_exactmatchTbl(j).credit_amount || ' '|| l_exactmatchTbl(j).currency_code);
344         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
345 
346         IF l_exactmatchTbl.exists(j) THEN
347         l_exact_match_rec_type := l_exactmatchTbl(j);
348 
349           INSERT INTO OZF_RULE_BASED_LOG
350                (
351                      LOG_ID
352                    , LAST_UPDATE_DATE
353                    , LAST_UPDATED_BY
354                    , CREATION_DATE
355                    , CREATED_BY
356                    , LAST_UPDATE_LOGIN
357                    , REQUEST_ID
358                    , PROGRAM_APPLICATION_ID
359                    , CREATED_FROM
360                    , CLAIM_ID
361                    , QP_LIST_HEADER_ID
362                    , CUSTOMER_TRX_ID
363                    , PROCESSED_MATCH_TYPE
364                   )
365                VALUES
366                (
367                      OZF_RULE_BASED_LOG_S.nextval
368                    , SYSDATE
369                    , NVL(FND_GLOBAL.user_id,-1)
370                    , SYSDATE
371                    , NVL(FND_GLOBAL.user_id,-1)
372                    , NVL(FND_GLOBAL.conc_login_id,-1)
373                    , NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
374                    , NVL(FND_GLOBAL.PROG_APPL_ID,-1)
375                    , 'RULEBASED'
376                    --, l_exactmatchTbl(I).claim_id
377                    , l_exact_match_rec_type.claim_id
378                    , null
379                    --, l_exactmatchTbl(I).customer_trx_id
380                    , l_exact_match_rec_type.customer_trx_id
381                    , 'C'
382                );
383 
384         END IF;
385 
386     END LOOP;
387 END IF;
388 
389 IF (l_possiblematchTbl.count >0) THEN
390   FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Possible Credit Matches');
391     FOR k IN l_possiblematchTbl.FIRST..l_possiblematchTbl.LAST LOOP
392         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
393         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Deduction #', 40, ' ') || ': ' || l_possiblematchTbl(k).claim_number);
394         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Credit Memo #', 40, ' ') || ': ' || l_possiblematchTbl(k).credit_memo_number);
395         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Deduction Amount', 40, ' ') || ': ' || l_possiblematchTbl(k).claim_amount || ' ' || l_possiblematchTbl(k).currency_code);
396         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Credit Amount', 40, ' ') || ': ' || l_possiblematchTbl(k).credit_amount || ' ' || l_possiblematchTbl(k).currency_code);
397         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
398 
399         IF l_possiblematchTbl.exists(k) THEN
400         l_poss_match_rec_type := l_possiblematchTbl(k);
401 
402            INSERT INTO OZF_RULE_BASED_LOG
403                (
404                      LOG_ID
405                    , LAST_UPDATE_DATE
406                    , LAST_UPDATED_BY
407                    , CREATION_DATE
408                    , CREATED_BY
409                    , LAST_UPDATE_LOGIN
410                    , REQUEST_ID
411                    , PROGRAM_APPLICATION_ID
412                    , CREATED_FROM
413                    , CLAIM_ID
414                    , QP_LIST_HEADER_ID
415                    , CUSTOMER_TRX_ID
416                    , PROCESSED_MATCH_TYPE
417                   )
418               VALUES
419                (
420                      OZF_RULE_BASED_LOG_S.nextval
421                    , SYSDATE
422                    , NVL(FND_GLOBAL.user_id,-1)
423                    , SYSDATE
424                    , NVL(FND_GLOBAL.user_id,-1)
425                    , NVL(FND_GLOBAL.conc_login_id,-1)
426                    , NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
427                    , NVL(FND_GLOBAL.PROG_APPL_ID,-1)
428                    , 'RULEBASED'
429                    --, l_possiblematchTbl(J).claim_id
430                    , l_poss_match_rec_type.claim_id
431                    , null
432                    --, l_possiblematchTbl(J).customer_trx_id
433                    , l_poss_match_rec_type.customer_trx_id
434                    , 'P'
435                );
436        END IF;
437     END LOOP;
438 END IF;
439 
440  IF (l_accrualmatchTbl.count >0) THEN
441    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Accrual Matches :');
442     FOR i IN l_accrualmatchTbl.FIRST..l_accrualmatchTbl.LAST LOOP
443         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
444         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Deduction #', 40, ' ') || ': ' || l_accrualmatchTbl(i).claim_number);
445         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Offer Code', 40, ' ') || ': ' || l_accrualmatchTbl(i).Offer_Code);
446         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Claim Amount', 40, ' ') || ': ' || l_accrualmatchTbl(i).claim_amount || ' ' || l_accrualmatchTbl(i).currency_code);
447         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
448         IF l_accrualmatchTbl.exists(i) THEN
449            l_accrual_match_rec_type := l_accrualmatchTbl(i);
450 
451             INSERT INTO OZF_RULE_BASED_LOG
452                        (
453                              LOG_ID
454                            , LAST_UPDATE_DATE
455                            , LAST_UPDATED_BY
456                            , CREATION_DATE
457                            , CREATED_BY
458                            , LAST_UPDATE_LOGIN
459                            , REQUEST_ID
460                            , PROGRAM_APPLICATION_ID
461                            , CREATED_FROM
462                            , CLAIM_ID
463                            , QP_LIST_HEADER_ID
464                            , CUSTOMER_TRX_ID
465                            , PROCESSED_MATCH_TYPE
466                           )
467                        VALUES
468                        (
469                              OZF_RULE_BASED_LOG_S.nextval
470                            , SYSDATE
471                            , NVL(FND_GLOBAL.user_id,-1)
472                            , SYSDATE
473                            , NVL(FND_GLOBAL.user_id,-1)
474                            , NVL(FND_GLOBAL.conc_login_id,-1)
475                            , NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
476                            , NVL(FND_GLOBAL.PROG_APPL_ID,-1)
477                            , 'RULEBASED'
478                            , l_accrual_match_rec_type.claim_id
479                            , l_accrual_match_rec_type.qp_list_header_id
480                            , null
481                            , 'A'
482                        );
483 
484            END IF;
485     END LOOP;
486 
487 END IF;
488 
489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*----------------------------------------------------------------------------------------------*');
493 
494 EXCEPTION
495 
496 WHEN FND_API.G_EXC_ERROR THEN
497     ROLLBACK TO CREATE_LOG;
498     x_return_status := FND_API.G_RET_STS_ERROR;
499     -- Standard call to get message count and if count=1, get the message
500     FND_MSG_PUB.Count_And_Get (
501             p_encoded => FND_API.G_FALSE,
502             p_count => l_msg_count,
503             p_data  => l_msg_data
504     );
505    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506     ROLLBACK TO CREATE_LOG;
507     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508     -- Standard call to get message count and if count=1, get the message
509     FND_MSG_PUB.Count_And_Get (
510             p_encoded => FND_API.G_FALSE,
511             p_count => l_msg_count,
512             p_data  => l_msg_data
513     );
514    WHEN OTHERS THEN
515     ROLLBACK TO CREATE_LOG;
516     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517     IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
518        FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CHK_ACS_ERR');
519        FND_MSG_PUB.add;
520     END IF;
521     -- Standard call to get message count and if count=1, get the message
522     FND_MSG_PUB.Count_And_Get (
523             p_encoded => FND_API.G_FALSE,
524             p_count => l_msg_count,
525             p_data  => l_msg_data
526     );
527 
528 END Create_Log;
529 
530 /*=======================================================================*
531  | Procedure
532  |    Start_Rule_Based_Settlement
533  |
534  | PURPOSE
535  |    This procedure will process the deduction records
536  |    that is eligible by Rule Based Engine.
537  |       If 100% Credit Match found, then deduction offset with CM
538  |       If 100% Match not found and possible match found based on setup
539  |                    it will go to possible queue.
540  |       If 100% Credit Match or possible match not found then it will
541  |       look for PAD number. If found then utilized the unbalance accruals
542  |       and initiate the settlement.
543  |
544  | NOTES
545  |
546  | HISTORY
547  |    20-JUN-2009  KPATRO  Create.
548  |    06-Aug-2009  KPATRO  Changed the Synonyms to base tables
549  |    07-Aug-2009  KPATRO  Need to Consider all the bill to site's of cudtomer
550  |                         creditmemo along with related customer
551  |                         in case of include related customer
552  |    19-Aug-2009  KPATRO  Changed CONTINURE to GOTO for Bug 8809877
553  |    19-Aug-2009  KPATRO  Changed the Customer Reference Information for Bug 8814596
554  |    25-Aug-2009  KPATRO  Fix for Bug 8834586
555  *=======================================================================*/
556 PROCEDURE Start_Rule_Based_Settlement (
557     ERRBUF                           OUT NOCOPY VARCHAR2,
558     RETCODE                          OUT NOCOPY NUMBER,
559     --p_org_id                         IN NUMBER   DEFAULT NULL,
560     p_start_date                     IN VARCHAR2,
561     p_end_date                       IN VARCHAR2,
562     p_pay_to_customer                IN VARCHAR2 := NULL
563  )
564 IS
565 l_api_name      CONSTANT VARCHAR2(30) := 'Start_Rule_Based_Settlement';
566 l_api_version   CONSTANT NUMBER := 1.0;
567 l_full_name     CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
568 l_msg_count     NUMBER;
569 l_msg_data      VARCHAR2(2000);
570 l_return_status VARCHAR2(10);
571 
572 l_claim_rec      OZF_CLAIM_PVT.claim_rec_type;
573 
574 TYPE claimTbl                IS TABLE OF ozf_claims_all.claim_number%TYPE;
575 TYPE amountTbl               IS TABLE OF ozf_claims_all.amount%TYPE;
576 TYPE acctdamountTbl          IS TABLE OF ozf_claims_all.acctd_amount%TYPE;
577 TYPE claimidTbl              IS TABLE OF ozf_claims_all.claim_id%TYPE;
578 TYPE custrefdTbl             IS TABLE OF ozf_claims_all.customer_ref_number%TYPE;
579 TYPE custrefnormTbl          IS TABLE OF ozf_claims_all.customer_ref_normalized%TYPE;
580 TYPE custaccountTbl          IS TABLE OF ozf_claims_all.cust_account_id%TYPE;
581 TYPE custbilltositeTbl       IS TABLE OF ozf_claims_all.cust_billto_acct_site_id%TYPE;
582 TYPE claimobjverTbl          IS TABLE OF ozf_claims_all.object_version_number%TYPE;
583 TYPE curcodeTbl              IS TABLE OF ozf_claims_all.currency_code%TYPE;
584 TYPE padTbl                  IS TABLE OF ozf_claims_all.pre_auth_deduction_number%TYPE;
585 TYPE padnormTbl              IS TABLE OF ozf_claims_all.pre_auth_deduction_normalized%TYPE;
586 TYPE siteUseIdTbl            IS TABLE OF ozf_claims_all.cust_billto_acct_site_id%TYPE;
587 TYPE offerIdTbl              IS TABLE OF ozf_claims_all.offer_id%TYPE;
588 
589 
590 -- For System Parameter Check
591 l_enable_rule_based VARCHAR2(1);
592 l_cre_threshold_type VARCHAR2(50);
593 l_cre_threshold_val  NUMBER;
594 l_cust_name_match_type VARCHAR2(50);
595 
596 
597 -- For Input Parameter from CC Job
598 l_start_date       date;
599 l_end_date         date;
600 l_cust_account_number  NUMBER;
601 
602 -- Get the Default Org
603 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
604 
605 -- Fetch the System Parameter Details
606 CURSOR chk_rule_based_csr  IS
607 SELECT NVL(rule_based, 'F'),
608        NVL(cust_name_match_type,'EXCLUDE_REL_CUST'),
609        credit_matching_thold_type,
610        credit_tolerance_operand
611 FROM   ozf_sys_parameters_all
612 WHERE  org_id = l_org_id;
613 
614 -- Fix for Bug 8834586 : truncating the dates if not passed from cc job.
615 -- Fix for Bug 12882998
616 CURSOR open_ded_csr(p_cust_account_id IN NUMBER,p_start_date IN DATE , p_end_date IN DATE) IS
617 SELECT claim_id,
618        claim_number,
619        amount_remaining,
620        acctd_amount_remaining,
621        customer_ref_number,
622        customer_ref_normalized,
623        cust_account_id,
624        cust_billto_acct_site_id,
625        object_version_number,
626        currency_code,
627        pre_auth_deduction_number,
628        pre_auth_deduction_normalized,
629        cust_billto_acct_site_id,
630        offer_id
631 FROM  ozf_claims_all
632 WHERE
633       status_code = 'OPEN'
634 AND   claim_class = 'DEDUCTION'
635 AND   (customer_ref_number IS NOT NULL
636 OR    pre_auth_deduction_number IS NOT NULL)
637 AND   org_id = l_org_id
638 AND   cust_account_id = nvl(p_cust_account_id,cust_account_id)
639 AND   trunc(creation_date) between nvl(p_start_date, trunc(creation_date)) AND nvl(p_end_date, trunc(creation_date))
640 ORDER BY creation_date ASC;
641 
642 -- Exact Match with Exclude related Customer
643 CURSOR csr_exact_cm_exc_cust(p_cust_account_id IN NUMBER,p_currency_code IN VARCHAR2, p_site_use_id IN NUMBER,
644                                 p_deduction_amount IN NUMBER, p_ref_number IN VARCHAR2) IS
645 SELECT ps.customer_trx_id,
646        ps.trx_number,
647        ps.amount_due_remaining
648 FROM   ar_payment_schedules_all ps,
649        ra_cust_trx_types_all ctt,
650        ra_customer_trx_all ct
651 WHERE
652        ps.class in ('CM') --class = Credit Memo
653  AND   ps.status = 'OP' -- status = Open
654  AND   ps.customer_id = p_cust_account_id
655  AND   ps.invoice_currency_code = p_currency_code --deduction currency code
656  AND   ps.customer_site_use_id = p_site_use_id --deduction site_use_id
657  AND   ctt.type = 'CM'
658  AND   ABS(ps.amount_due_remaining) = p_deduction_amount --deduction amount
659  AND   ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
660  AND   Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
661  AND   ct.customer_trx_id = ps.customer_trx_id
662  AND   ps.org_id = l_org_id
663  AND   rownum = 1 -- for 100% match it should be one
664  ORDER BY ct.creation_date ASC;
665 
666 -- Exact Match with Include related Customer
667 CURSOR csr_exact_cm_rel_cust(p_cust_account_id IN NUMBER,p_claim_id IN NUMBER,p_currency_code IN VARCHAR2,
668                                 p_deduction_amount IN NUMBER, p_ref_number IN VARCHAR2) IS
669 SELECT ps.customer_trx_id,
670        ps.trx_number,
671        ps.amount_due_remaining
672 FROM   ar_payment_schedules_all ps,
673        ra_cust_trx_types_all ctt,
674        ra_customer_trx_all ct,
675 (
676   SELECT SITE.site_use_id site_use_id
677   FROM   HZ_CUST_ACCT_RELATE_ALL REL, HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
678          HZ_CUST_SITE_USES_ALL SITE
679   WHERE
680          REL.status = 'A'
681     AND  REL.cust_account_id = p_cust_account_id --cust_account_id from deduction
682     AND  (REL.relationship_type is NULL OR REL.relationship_type IN ('ALL','Reciprocal','Parent')) --For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
683     AND  REL.related_cust_account_id = ACCT_SITE.cust_account_id
684     AND  ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
685     AND  SITE.SITE_USE_CODE = 'BILL_TO'
686     AND  SITE.status = 'A'
687     AND  REL.org_id = l_org_id
688 UNION
689  SELECT  SITE.site_use_id site_use_id
690  FROM    HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
691          HZ_CUST_SITE_USES_ALL SITE
692  WHERE
693          ACCT_SITE.cust_account_id = p_cust_account_id
694     AND  ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
695     AND  SITE.SITE_USE_CODE = 'BILL_TO'
696     AND  SITE.status = 'A'
697     AND  SITE.org_id = l_org_id
698 ) site_use
699 WHERE
700       ps.class in ('CM') --class = Credit Memo
701  AND  ps.status = 'OP' -- status = Open
702  AND  ps.invoice_currency_code = p_currency_code --deduction currency code
703  AND  ABS(ps.amount_due_remaining) = p_deduction_amount --deduction amount
704  AND  ps.customer_site_use_id = site_use.site_use_id --deduction site_use_id / related customer site_use_id
705  AND  ctt.type = 'CM'
706  AND  ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
707  AND  Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
708  AND  ct.customer_trx_id = ps.customer_trx_id
709  AND  ps.org_id = l_org_id
710  AND  rownum = 1 -- for 100% match it should be one.
711  ORDER BY ct.creation_date ASC;
712 
713 -- Possible Match with Exclude related Customer
714 CURSOR csr_poss_cm_exc_cust(p_cust_account_id IN NUMBER,p_currency_code IN VARCHAR2, p_site_use_id IN NUMBER,
715                                 p_deduction_lower_amount IN NUMBER,p_deduction_upper_amount IN NUMBER,
716                                 p_ref_number IN VARCHAR2) IS
717 SELECT ps.customer_trx_id,
718        ps.trx_number,
719        ps.amount_due_remaining
720 FROM
721        ar_payment_schedules_all ps,
722        ra_cust_trx_types_all ctt,
723        ra_customer_trx_all ct
724 WHERE
725        ps.class in ('CM') --class = Credit Memo
726   AND  ps.status = 'OP' -- status = Open
727   AND  ps.customer_id = p_cust_account_id
728   AND  ps.invoice_currency_code = p_currency_code --deduction currency code
729   AND  ps.customer_site_use_id = p_site_use_id --deduction site_use_id
730   AND  ctt.type = 'CM'
731   AND  ABS(ps.amount_due_remaining) between  p_deduction_lower_amount AND p_deduction_upper_amount --deduction amount
732   AND  ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
733   AND  Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
734   AND  ct.customer_trx_id = ps.customer_trx_id
735   AND  ps.org_id = l_org_id
736   ORDER BY ct.creation_date ASC;
737 
738 -- Possible Match with Include related Customer
739 CURSOR csr_poss_cm_rel_cust(p_cust_account_id IN NUMBER,p_claim_id IN NUMBER,p_currency_code IN VARCHAR2,
740                                 p_deduction_lower_amount IN NUMBER,p_deduction_upper_amount IN NUMBER, p_ref_number IN VARCHAR2) IS
741 SELECT ps.customer_trx_id,
742        ps.trx_number,
743        ps.amount_due_remaining
744 FROM
745        ar_payment_schedules_all ps,
746        ra_cust_trx_types_all ctt,
747        ra_customer_trx_all ct,
748 (
749   SELECT SITE.site_use_id site_use_id
750   FROM   HZ_CUST_ACCT_RELATE_ALL REL,
751          HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
752          HZ_CUST_SITE_USES_ALL SITE
753   WHERE  REL.status = 'A'
754    AND   REL.cust_account_id = p_cust_account_id --cust_account_id from deduction
755    AND   (REL.relationship_type is NULL OR REL.relationship_type IN ('ALL','Reciprocal','Parent')) --For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
756    AND   REL.related_cust_account_id = ACCT_SITE.cust_account_id
757    AND   ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
758    AND   SITE.SITE_USE_CODE = 'BILL_TO'
759    AND   SITE.status = 'A'
760    AND   REL.org_id = l_org_id
761  UNION
762   SELECT SITE.site_use_id site_use_id
763   FROM   HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
764          HZ_CUST_SITE_USES_ALL SITE
765   WHERE  ACCT_SITE.cust_account_id = p_cust_account_id
766    AND   ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
767    AND   SITE.SITE_USE_CODE = 'BILL_TO'
768    AND   SITE.status = 'A'
769    AND   SITE.org_id = l_org_id
770 ) site_use
771 WHERE
772       ps.class in ('CM') --class = Credit Memo
773  AND  ps.status = 'OP' -- status = Open
774  AND  ps.invoice_currency_code = p_currency_code --deduction currency code
775  AND  ABS(ps.amount_due_remaining) BETWEEN  p_deduction_lower_amount AND p_deduction_upper_amount --deduction amount
776  AND  ps.customer_site_use_id = site_use.site_use_id --deduction site_use_id / related customer site_use_id
777  AND  ctt.type = 'CM'
778  AND  ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
779  AND  Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
780  AND  ct.customer_trx_id = ps.customer_trx_id
781  AND  ps.org_id = l_org_id
782  ORDER BY ct.creation_date ASC;
783 
784 
785 CURSOR csr_offer_info(p_ref_number VARCHAR2) IS
786 SELECT qp_list_header_id,offer_code
787 FROM   ozf_offers
788 WHERE  offer_code =p_ref_number;
789 
790 CURSOR csr_claim_line_info (p_claim_id NUMBER) IS
791 SELECT claim_line_id,object_version_number
792 FROM   ozf_claim_lines_all
793 WHERE  claim_id = p_claim_id;
794 
795 CURSOR csr_claim_line_util_info (p_claim_line_id NUMBER) IS
796 SELECT COUNT(*)
797 FROM   ozf_claim_lines_util_all
798 WHERE  claim_line_id = p_claim_line_id;
799 
800 -- For PAD
801 l_offer_code VARCHAR2(30);
802 l_list_header_id NUMBER;
803 l_object_version_number NUMBER;
804 l_claim_line_id NUMBER;
805 l_claim_line_object_version NUMBER;
806 l_funds_util_flt      OZF_Claim_Accrual_PVT.funds_util_flt_type;
807 l_claim_line_tbl      OZF_CLAIM_LINE_PVT.claim_line_tbl_type;
808 l_claim_line_rec      OZF_CLAIM_LINE_PVT.claim_line_rec_type;
809 l_error_index         NUMBER;
810 l_ind NUMBER :=1;
811 l_count_earnings NUMBER := 0;
812 
813 
814 l_trx_id   NUMBER;
815 l_trx_number VARCHAR2(400);
816 l_crditmemo_amount NUMBER;
817 l_reference_number VARCHAR2(30);
818 l_refer_norm_number VARCHAR2(30);
819 
820 l_possible_count NUMBER :=0;
821 l_exactfound_count NUMBER :=0;
822 l_accrual_count NUMBER :=0;
823 
824 l_upper_thres_amount NUMBER :=0;
825 l_lower_thres_amount NUMBER :=0;
826 
827 l_claimTbl               claimTbl;
828 l_amountTbl              amountTbl;
829 l_acctdamountTbl         acctdamountTbl;
830 l_claimidTbl             claimidTbl;
831 l_custrefdTbl            custrefdTbl;
832 l_custrefnormTbl         custrefnormTbl;
833 l_custaccountTbl         custaccountTbl;
834 l_custbilltositeTbl      custbilltositeTbl;
835 l_claimobjverTbl         claimobjverTbl;
836 l_curcodeTbl             curcodeTbl;
837 l_padTbl                 padTbl;
838 l_padnormTbl             padnormTbl;
839 l_siteUseIdTbl           siteUseIdTbl;
840 l_offerIdTbl               offerIdTbl;
841 
842 l_possiblematchTbl      ozf_rule_match_tbl_type;
843 l_exactmatchTbl         ozf_rule_match_tbl_type;
844 l_accrualmatchTbl       ozf_accrual_match_tbl_type;
845 
846 
847 l_count NUMBER :=0;
848 l_rel_cust_account_id NUMBER;
849 l_found  BOOLEAN := false;
850 
851 l_cm_match_found VARCHAR2(1) := 'F';
852 
853 
854 BEGIN
855 
856 SAVEPOINT RuleBased;
857 
858 
859 OZF_Utility_PVT.write_conc_log('*------------------------------ Claims Rule Based Settlement Log ------------------------------*');
860 OZF_Utility_PVT.write_conc_log('Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
861 OZF_Utility_PVT.write_conc_log('*-------------------------------------------------------------------------------------------------*');
862 
863 IF OZF_DEBUG_HIGH_ON THEN
864    FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
865    FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
866    FND_MSG_PUB.Add;
867 END IF;
868 
869 
870 
871  IF OZF_DEBUG_HIGH_ON THEN
872       OZF_Utility_PVT.write_conc_log('Start Rule Based Settlement');
873       OZF_Utility_PVT.write_conc_log('--- Start Parameter List ---');
874       OZF_Utility_PVT.write_conc_log('l_org_id: '          || l_org_id);
875       OZF_Utility_PVT.write_conc_log('p_start_date: '      || p_start_date);
876       OZF_Utility_PVT.write_conc_log('p_end_date: '        || p_end_date);
877       OZF_Utility_PVT.write_conc_log('p_pay_to_customer: ' || p_pay_to_customer);
878       OZF_Utility_PVT.write_conc_log('--- End Parameter List -----');
879 
880 
881    END IF;
882 
883   -- Check For Rule Based Settlemnet setup in System Parameter.
884   OPEN chk_rule_based_csr;
885   FETCH chk_rule_based_csr INTO l_enable_rule_based,l_cust_name_match_type,l_cre_threshold_type,l_cre_threshold_val;
886   CLOSE chk_rule_based_csr;
887 
888    IF OZF_DEBUG_HIGH_ON THEN
889       OZF_Utility_PVT.write_conc_log('Processing Claims for Operating Unit: ' || MO_GLOBAL.get_ou_name(l_org_id));
890       OZF_Utility_PVT.write_conc_log('Rule Based Settlement : '|| l_enable_rule_based);
891       OZF_Utility_PVT.write_conc_log('Customer Name Matching Type : ' || l_cust_name_match_type);
892       OZF_Utility_PVT.write_conc_log('Cr Threshold Type : ' || l_cre_threshold_type);
893       OZF_Utility_PVT.write_conc_log('Cr Threshold Value : ' || l_cre_threshold_val);
894     END IF;
895 
896 
897   IF(l_enable_rule_based = 'F') THEN
898 
899         OZF_Utility_PVT.write_conc_log('Rule Based Flag is disabled in System Parameter page for Operating unit :' ||MO_GLOBAL.get_ou_name(l_org_id));
900         OZF_Utility_PVT.write_conc_log('Please Enable the Rule Based Settlement checkbox to process the deductions');
901 
902   ELSE
903 
904         OZF_Utility_PVT.write_conc_log('Proceed with Rule Based Engine');
905 
906 
907         IF p_start_date IS NOT NULL THEN
908            l_start_date  := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
909         ELSE
910            l_start_date :=NULL;
911         END IF ;
912 
913         IF p_end_date IS NOT NULL THEN
914             l_end_date := to_date(p_end_date,  'YYYY/MM/DD HH24:MI:SS');
915         ELSE
916             l_end_date := NULL;
917         END IF ;
918 
919         IF p_pay_to_customer IS NOT NULL THEN
920             l_cust_account_number      := p_pay_to_customer ;
921         ELSE
922             l_cust_account_number := NULL;
923         END IF ;
924 
925 
926            IF OZF_DEBUG_HIGH_ON THEN
927               OZF_Utility_PVT.write_conc_log('Operating Unit: ' || l_org_id);
928               OZF_Utility_PVT.write_conc_log('Start Date: ' || l_start_date);
929               OZF_Utility_PVT.write_conc_log('End Date: ' || l_end_date);
930               OZF_Utility_PVT.write_conc_log('Customer Account: ' || l_cust_account_number);
931            END IF;
932 
933         OPEN  open_ded_csr (l_cust_account_number,l_start_date,l_end_date);
934         LOOP
935            FETCH open_ded_csr BULK COLLECT INTO l_claimidTbl,l_claimTbl,l_amountTbl, l_acctdamountTbl,
936                                                        l_custrefdTbl,l_custrefnormTbl,l_custaccountTbl,
937                                                        l_custbilltositeTbl,l_claimobjverTbl,l_curcodeTbl,l_padTbl,
938                                                        l_padnormTbl,l_siteUseIdTbl, l_offerIdTbl
939                                                        LIMIT g_bulk_limit;
940 
941           FOR i IN NVL(l_claimidTbl.FIRST, 1) .. NVL(l_claimidTbl.LAST, 0) LOOP
942 
943              l_trx_id := null;
944              l_trx_number := null;
945              l_crditmemo_amount := null;
946              l_reference_number := null;
947              l_refer_norm_number := null;
948 	     l_cm_match_found := 'F';
949 
950 
951 
952             IF OZF_DEBUG_HIGH_ON THEN
953               OZF_Utility_PVT.write_conc_log('-------------------------------------------------');
954               OZF_Utility_PVT.write_conc_log('START : Fetching Claim ' || i);
955               OZF_Utility_PVT.write_conc_log('-------------------------------------------------');
956               OZF_Utility_PVT.write_conc_log('Claim ID: ' || l_claimidTbl(i));
957               OZF_Utility_PVT.write_conc_log('Claim Number: ' || l_claimTbl(i));
958               OZF_Utility_PVT.write_conc_log('Claim Amount: ' || l_amountTbl(i));
959               OZF_Utility_PVT.write_conc_log('Claim Acctd Amount: ' || l_acctdamountTbl(i));
960               OZF_Utility_PVT.write_conc_log('Customer Reference: ' || l_custrefdTbl(i));
961               OZF_Utility_PVT.write_conc_log('Customer Reference Norm: ' || l_custrefnormTbl(i));
962               OZF_Utility_PVT.write_conc_log('Customer Account: ' || l_custaccountTbl(i));
963               OZF_Utility_PVT.write_conc_log('Customer Bill To: ' || l_custbilltositeTbl(i));
964               OZF_Utility_PVT.write_conc_log('Claim Obj Version#: ' || l_claimobjverTbl(i));
965               OZF_Utility_PVT.write_conc_log('Claim Currency Code: ' || l_curcodeTbl(i));
966               OZF_Utility_PVT.write_conc_log('Claim PAD#: ' || l_padTbl(i));
967               OZF_Utility_PVT.write_conc_log('Claim PAD NORM#: ' || l_padnormTbl(i));
968               OZF_Utility_PVT.write_conc_log('Claim Site Used Id: ' || l_siteUseIdTbl(i));
969               OZF_Utility_PVT.write_conc_log('Offer ID: ' || l_offerIdTbl(i));
970             END IF;
971 
972              -- For 100% Credit Match
973              IF(l_cust_name_match_type = 'EXCLUDE_REL_CUST') THEN
974                 OPEN csr_exact_cm_exc_cust(l_custaccountTbl(i),l_curcodeTbl(i),l_siteUseIdTbl(i),l_amountTbl(i),l_custrefnormTbl(i));
975                    FETCH csr_exact_cm_exc_cust INTO l_trx_id,l_trx_number,l_crditmemo_amount;
976                    IF OZF_DEBUG_HIGH_ON THEN
977                         OZF_Utility_PVT.write_conc_log('100% Credit Match - Exclude Related Customer ');
978                         OZF_Utility_PVT.write_conc_log('l_trx_id: ' ||l_trx_id);
979                         OZF_Utility_PVT.write_conc_log('l_trx_number: ' || l_trx_number);
980                         OZF_Utility_PVT.write_conc_log('l_crditmemo_amount: ' || l_crditmemo_amount);
981                    END IF;
982                 CLOSE csr_exact_cm_exc_cust;
983              ELSIF(l_cust_name_match_type = 'INCLUDE_REL_CUST') THEN
984                 OPEN csr_exact_cm_rel_cust(l_custaccountTbl(i),l_claimidTbl(i),l_curcodeTbl(i),l_amountTbl(i),l_custrefnormTbl(i));
985                    FETCH csr_exact_cm_rel_cust INTO l_trx_id,l_trx_number,l_crditmemo_amount;
986                    IF OZF_DEBUG_HIGH_ON THEN
987                         OZF_Utility_PVT.write_conc_log('100% Credit Match - Include Related Customer');
988                         OZF_Utility_PVT.write_conc_log('l_trx_id: ' ||l_trx_id);
989                         OZF_Utility_PVT.write_conc_log('l_trx_number: ' || l_trx_number);
990                         OZF_Utility_PVT.write_conc_log('l_crditmemo_amount: ' || l_crditmemo_amount);
991                    END IF;
992                 CLOSE csr_exact_cm_rel_cust;
993              END IF;
994 
995              IF (l_trx_id IS NOT NULL) THEN
996 
997                   l_claim_rec.claim_id := l_claimidTbl(i);
998                   l_claim_rec.payment_method := 'PREV_OPEN_CREDIT';
999                   l_claim_rec.payment_reference_number := l_trx_number;
1000                   l_claim_rec.payment_reference_id := l_trx_id;
1001                   l_claim_rec.object_version_number := l_claimobjverTbl(i);
1002                   l_claim_rec.status_code := 'CLOSED';
1003                   l_claim_rec.user_status_id := to_number(
1004                                               ozf_utility_pvt.get_default_user_status(
1005                                               p_status_type   => 'OZF_CLAIM_STATUS',
1006                                               p_status_code   => l_claim_rec.status_code));
1007                   l_claim_rec.request_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,-1);
1008                   l_claim_rec.program_id := NVL(FND_GLOBAL.PROG_APPL_ID,-1);
1009                   l_claim_rec.settled_from := 'RULEBASED';
1010 
1011                       OZF_Claim_PVT.Update_Claim(
1012                                  p_api_version           => l_api_version
1013                                 ,p_init_msg_list         => FND_API.g_false
1014                                 ,p_commit                => FND_API.g_false
1015                                 ,p_validation_level      => FND_API.g_valid_level_full
1016                                 ,x_return_status         => l_return_status
1017                                 ,x_msg_data              => l_msg_data
1018                                 ,x_msg_count             => l_msg_count
1019                                 ,p_claim                 => l_claim_rec
1020                                 ,p_event                 => 'UPDATE'
1021                                 ,p_mode                  => 'AUTO'
1022                                 ,x_object_version_number => l_object_version_number
1023                                 );
1024                  OZF_Utility_PVT.write_conc_log('Return Status for 100%: ' || l_return_status);
1025 
1026 		  IF l_return_status = fnd_api.g_ret_sts_success THEN
1027                         l_cm_match_found := 'T';
1028                   ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1029                         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030                   ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1031                         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1032                   END IF;
1033                  l_exactfound_count := l_exactfound_count +1;
1034                  l_exactmatchTbl(l_exactfound_count).claim_id :=  l_claimidTbl(i);
1035                  l_exactmatchTbl(l_exactfound_count).claim_number :=  l_claimTbl(i);
1036                  l_exactmatchTbl(l_exactfound_count).credit_memo_number :=  l_trx_number;
1037                  l_exactmatchTbl(l_exactfound_count).claim_amount :=  l_amountTbl(i);
1038                  l_exactmatchTbl(l_exactfound_count).credit_amount :=  l_crditmemo_amount;
1039                  l_exactmatchTbl(l_exactfound_count).currency_code := l_curcodeTbl(i);
1040                  l_exactmatchTbl(l_exactfound_count).customer_trx_id :=  l_trx_id;
1041 
1042                -- If exact match found then go to next deduction
1043                GOTO END_OF_DEDUCTION;
1044 
1045              END IF;
1046              -- End For 100% Credit Match
1047 
1048              -- For Possible Match
1049               -- Find the threshold
1050              IF l_cre_threshold_type = '%' THEN
1051                 l_upper_thres_amount := l_amountTbl(i) + (l_amountTbl(i) * l_cre_threshold_val / 100);
1052                 l_lower_thres_amount := l_amountTbl(i) - (l_amountTbl(i) * l_cre_threshold_val / 100);
1053              ELSIF l_cre_threshold_type = 'AMT' THEN
1054                 l_upper_thres_amount := l_amountTbl(i) + l_cre_threshold_val;
1055                 l_lower_thres_amount := l_amountTbl(i) - l_cre_threshold_val;
1056              END IF;
1057 
1058 
1059              IF(l_cust_name_match_type = 'EXCLUDE_REL_CUST') THEN
1060                 IF OZF_DEBUG_HIGH_ON THEN
1061                     OZF_Utility_PVT.write_conc_log('Possible Match - Exclude Related Customer');
1062                  END IF;
1063 
1064                 OPEN csr_poss_cm_exc_cust(l_custaccountTbl(i),l_curcodeTbl(i),l_siteUseIdTbl(i),l_lower_thres_amount,l_upper_thres_amount,l_custrefnormTbl(i));
1065                   LOOP
1066                    FETCH csr_poss_cm_exc_cust INTO l_trx_id,l_trx_number,l_crditmemo_amount;
1067                    EXIT WHEN csr_poss_cm_exc_cust%NOTFOUND;
1068                    -- Populate the possible records
1069                    l_possible_count := l_possible_count +1;
1070                    l_possiblematchTbl(l_possible_count).claim_id :=  l_claimidTbl(i);
1071                    l_possiblematchTbl(l_possible_count).claim_number :=  l_claimTbl(i);
1072                    l_possiblematchTbl(l_possible_count).credit_memo_number :=  l_trx_number;
1073                    l_possiblematchTbl(l_possible_count).claim_amount :=  l_amountTbl(i);
1074                    l_possiblematchTbl(l_possible_count).credit_amount :=  l_crditmemo_amount;
1075                    l_possiblematchTbl(l_possible_count).currency_code := l_curcodeTbl(i);
1076                    l_possiblematchTbl(l_possible_count).customer_trx_id :=  l_trx_id;
1077 
1078 		   IF OZF_DEBUG_HIGH_ON THEN
1079                     OZF_Utility_PVT.write_conc_log('Possible Match - l_trx_id :' || l_trx_id);
1080                    END IF;
1081 
1082 		   IF(l_trx_id IS NOT NULL) THEN
1083 		        l_cm_match_found := 'T';
1084 		   END IF;
1085 
1086 		   END LOOP;
1087 
1088                 CLOSE csr_poss_cm_exc_cust;
1089 
1090               ELSIF(l_cust_name_match_type = 'INCLUDE_REL_CUST') THEN
1091                 IF OZF_DEBUG_HIGH_ON THEN
1092                     OZF_Utility_PVT.write_conc_log('Possible Match - Include Related Customer');
1093                 END IF;
1094 
1095 
1096                 OPEN csr_poss_cm_rel_cust(l_custaccountTbl(i),l_claimidTbl(i),l_curcodeTbl(i),l_lower_thres_amount,l_upper_thres_amount,l_custrefnormTbl(i));
1097                   LOOP
1098                    FETCH csr_poss_cm_rel_cust INTO l_trx_id,l_trx_number,l_crditmemo_amount;
1099                    EXIT WHEN csr_poss_cm_rel_cust%NOTFOUND;
1100                    l_possible_count := l_possible_count +1;
1101                    l_possiblematchTbl(l_possible_count).claim_id :=  l_claimidTbl(i);
1102                    l_possiblematchTbl(l_possible_count).claim_number :=  l_claimTbl(i);
1103                    l_possiblematchTbl(l_possible_count).credit_memo_number :=  l_trx_number;
1104                    l_possiblematchTbl(l_possible_count).claim_amount :=  l_amountTbl(i);
1105                    l_possiblematchTbl(l_possible_count).credit_amount :=  l_crditmemo_amount;
1106                    l_possiblematchTbl(l_possible_count).currency_code := l_curcodeTbl(i);
1107                    l_possiblematchTbl(l_possible_count).customer_trx_id :=  l_trx_id;
1108 
1109 		   IF OZF_DEBUG_HIGH_ON THEN
1110                     OZF_Utility_PVT.write_conc_log('Possible Match - l_trx_id :' || l_trx_id);
1111                    END IF;
1112 
1113 		   IF(l_trx_id IS NOT NULL) THEN
1114 		        l_cm_match_found := 'T';
1115 		   END IF;
1116 
1117 		  END LOOP;
1118 
1119                  CLOSE csr_poss_cm_rel_cust;
1120 
1121                 END IF;
1122                 -- If possible match found then go to next deduction
1123 		 IF OZF_DEBUG_HIGH_ON THEN
1124                     OZF_Utility_PVT.write_conc_log('l_possiblematchTbl.count :' || l_possiblematchTbl.count);
1125 		    OZF_Utility_PVT.write_conc_log('l_cm_match_found :' || l_cm_match_found);
1126                 END IF;
1127 
1128 		IF (l_possiblematchTbl.count > 0 AND l_cm_match_found = 'T' )THEN
1129 		   GOTO END_OF_DEDUCTION;
1130                 END IF;
1131 
1132         BEGIN
1133          SAVEPOINT  Update_Claim_From_Association;
1134 
1135         IF (l_padnormTbl(i) IS NOT NULL) THEN
1136 
1137           l_list_header_id := l_offerIdTbl(i);
1138           l_offer_code := l_padnormTbl(i);
1139 
1140             IF OZF_DEBUG_HIGH_ON THEN
1141                  OZF_Utility_PVT.write_conc_log('Offer Found: ' || l_offer_code);
1142                  OZF_Utility_PVT.write_conc_log('Offer ID: ' || l_list_header_id);
1143             END IF;
1144 
1145 
1146            IF l_list_header_id IS NULL THEN
1147              IF OZF_DEBUG_HIGH_ON THEN
1148                  OZF_Utility_PVT.write_conc_log('Invalid PAD Number ' || l_padnormTbl(i));
1149             END IF;
1150 
1151            ELSE
1152 
1153            l_ind :=1;
1154            OPEN csr_claim_line_info(l_claimidTbl(i));
1155             LOOP
1156               FETCH csr_claim_line_info into l_claim_line_id, l_claim_line_object_version;
1157               EXIT when csr_claim_line_info%NOTFOUND;
1158               l_claim_line_tbl(l_ind).claim_line_id := l_claim_line_id;
1159               l_claim_line_tbl(l_ind).object_version_number := l_claim_line_object_version;
1160               l_ind := l_ind +1;
1161             END LOOP;
1162            CLOSE csr_claim_line_info;
1163 
1164 
1165           IF(l_claim_line_tbl.COUNT > 0 ) THEN
1166            -- delete the claim line if there is any
1167            OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
1168                      p_api_version            => l_api_version
1169                     ,p_init_msg_list          => FND_API.g_false
1170                     ,p_commit                 => FND_API.g_false
1171                     ,p_validation_level       => FND_API.g_valid_level_full
1172                     ,x_return_status          => l_return_status
1173                     ,x_msg_count              => l_msg_count
1174                     ,x_msg_data               => l_msg_data
1175                     ,p_claim_line_tbl         => l_claim_line_tbl
1176                     ,p_change_object_version  => FND_API.g_false
1177                     ,x_error_index            => l_error_index
1178                   );
1179                   IF l_return_status = FND_API.g_ret_sts_error THEN
1180                      RAISE FND_API.g_exc_unexpected_error;
1181                   ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1182                      RAISE FND_API.g_exc_unexpected_error;
1183                   END IF;
1184           END IF; -- End of delete claim line
1185 
1186           IF OZF_DEBUG_HIGH_ON THEN
1187                OZF_Utility_PVT.write_conc_log('Claim Line Deleted');
1188           END IF;
1189 
1190           l_claim_line_rec.claim_id              := l_claimidTbl(i);
1191           l_claim_line_rec.activity_type         := 'OFFR';
1192           l_claim_line_rec.activity_id           := l_list_header_id;
1193           l_claim_line_rec.currency_code         := l_curcodeTbl(i);
1194           l_claim_line_rec.amount                := l_amountTbl(i);
1195           l_claim_line_rec.acctd_amount          := l_acctdamountTbl(i);
1196           l_claim_line_rec.claim_currency_amount := l_amountTbl(i);
1197 
1198           -- New claim line creation
1199           OZF_Claim_Line_PVT.Create_Claim_Line(
1200                  p_api_version       => 1.0
1201                , p_init_msg_list     => FND_API.g_false
1202                , p_commit            => FND_API.g_false
1203                , p_validation_level  => FND_API.g_valid_level_full
1204                , x_return_status     => l_return_status
1205                , x_msg_data          => l_msg_data
1206                , x_msg_count         => l_msg_count
1207                , p_claim_line_rec    => l_claim_line_rec
1208                , p_mode              => OZF_CLAIM_UTILITY_PVT.g_auto_mode
1209                , x_claim_line_id     => l_claim_line_id
1210           );
1211           IF l_return_status =  fnd_api.g_ret_sts_error THEN
1212                  RAISE FND_API.g_exc_error;
1213           ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1214                  RAISE FND_API.g_exc_unexpected_error;
1215           END IF;
1216 
1217           IF OZF_DEBUG_HIGH_ON THEN
1218                 OZF_Utility_PVT.write_conc_log('Claim Line Created');
1219                 OZF_Utility_PVT.write_conc_log('New Claim Line Created' || l_claim_line_id);
1220           END IF;
1221 
1222           -- Associate Accruals to Claim Line
1223           OZF_Claim_Accrual_PVT.Asso_Accruals_To_Claim_Line(
1224                          p_api_version         => 1.0
1225                         ,p_init_msg_list       => FND_API.g_false
1226                         ,p_commit              => FND_API.g_false
1227                         ,p_validation_level    => FND_API.G_VALID_LEVEL_FULL
1228                         ,x_return_status       => l_return_status
1229                         ,x_msg_count           => l_msg_count
1230                         ,x_msg_data            => l_msg_data
1231                         ,p_claim_line_id       => l_claim_line_id
1232                       );
1233 
1234               OZF_Utility_PVT.write_conc_log('Return Status for Asso_Accruals_To_Claim_Line: ' || l_return_status);
1235 
1236               IF l_return_status = FND_API.g_ret_sts_error THEN
1237                 RAISE FND_API.g_exc_error;
1238               ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1239                 RAISE FND_API.g_exc_error;
1240               END IF;
1241 
1242 
1243              OPEN csr_claim_line_util_info (l_claim_line_id);
1244               FETCH csr_claim_line_util_info INTO l_count_earnings;
1245              CLOSE csr_claim_line_util_info;
1246 
1247               -- Need to check here add the return status check as yes
1248               IF (l_count_earnings = 0) THEN
1249                 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1250                       FND_MESSAGE.set_name('OZF', 'OZF_EARN_AVAIL_AMT_ZERO');
1251                       FND_MSG_PUB.add;
1252                  END IF;
1253                     ROLLBACK TO Update_Claim_From_Association;
1254               ELSE
1255               -- Initiate the Settlement
1256                   l_claim_rec.claim_id := l_claimidTbl(i);
1257                   l_claim_rec.payment_method := 'CREDIT_MEMO';
1258                   l_claim_rec.object_version_number := l_claimobjverTbl(i);
1259                   l_claim_rec.status_code := 'CLOSED';
1260                   l_claim_rec.user_status_id := to_number(
1261                                               ozf_utility_pvt.get_default_user_status(
1262                                               p_status_type   => 'OZF_CLAIM_STATUS',
1263                                               p_status_code   => l_claim_rec.status_code));
1264                   l_claim_rec.request_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,-1);
1265                   l_claim_rec.program_id := NVL(FND_GLOBAL.PROG_APPL_ID,-1);
1266                   l_claim_rec.settled_from := 'RULEBASED';
1267 
1268                         OZF_Claim_PVT.Update_Claim(
1269                                  p_api_version           => l_api_version
1270                                 ,p_init_msg_list         => FND_API.g_false
1271                                 ,p_commit                => FND_API.g_false
1272                                 ,p_validation_level      => FND_API.g_valid_level_full
1273                                 ,x_return_status         => l_return_status
1274                                 ,x_msg_data              => l_msg_data
1275                                 ,x_msg_count             => l_msg_count
1276                                 ,p_claim                 => l_claim_rec
1277                                 ,p_event                 => 'UPDATE'
1278                                 ,p_mode                  => 'AUTO'
1279                                 ,x_object_version_number => l_object_version_number
1280                                 );
1281                           IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1282                                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283                           ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1284                                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1285                           END IF;
1286 
1287                  END IF; -- End of Association check
1288                      -- Populate the accrual records
1289                       l_accrual_count := l_accrual_count +1;
1290                       l_accrualmatchTbl(l_accrual_count).claim_id  :=  l_claimidTbl(i);
1291                       l_accrualmatchTbl(l_accrual_count).claim_number  :=  l_claimTbl(i);
1292                       l_accrualmatchTbl(l_accrual_count).Offer_Code        :=  l_offer_code;
1293                       l_accrualmatchTbl(l_accrual_count).claim_amount  :=  l_amountTbl(i);
1294                       l_accrualmatchTbl(l_accrual_count).currency_code  :=  l_curcodeTbl(i);
1295                       l_accrualmatchTbl(l_accrual_count).qp_list_header_id :=  l_list_header_id;
1296 
1297           END IF; -- End of Offer Check
1298 
1299         END IF; -- End of PAD check
1300 
1301        EXCEPTION
1302           WHEN FND_API.g_exc_error THEN
1303           ROLLBACK TO Update_Claim_From_Association;
1304           OZF_Utility_PVT.write_conc_log('Expected errors:l_msg_count' || l_msg_count);
1305            IF OZF_DEBUG_HIGH_ON THEN
1306             OZF_UTILITY_PVT.write_conc_log;
1307            ELSE
1308              FOR I IN 1..l_msg_count LOOP
1309                IF I = l_msg_count THEN
1310                 OZF_Utility_PVT.write_conc_log(SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254));
1311                END IF;
1312              END LOOP;
1313            END IF;
1314 
1315          WHEN FND_API.g_exc_unexpected_error THEN
1316           ROLLBACK TO Update_Claim_From_Association;
1317           OZF_Utility_PVT.write_conc_log('Unexpected errors:l_msg_count' || l_msg_count);
1318           IF OZF_DEBUG_HIGH_ON THEN
1319             OZF_UTILITY_PVT.write_conc_log;
1320           ELSE
1321              FOR I IN 1..l_msg_count LOOP
1322                IF I = l_msg_count THEN
1323                 OZF_Utility_PVT.write_conc_log(SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254));
1324                END IF;
1325              END LOOP;
1326           END IF;
1327 
1328           WHEN OTHERS THEN
1329           ROLLBACK TO Update_Claim_From_Association;
1330             IF OZF_DEBUG_HIGH_ON THEN
1331                OZF_Utility_PVT.write_conc_log('Fail For Deduction OTHERS : ' || l_claimTbl(i) || ' - Error Message: ' || SQLERRM);
1332             END IF;
1333 
1334             FND_MSG_PUB.count_and_get(
1335            p_encoded => FND_API.g_false
1336           ,p_count   => l_msg_count
1337           ,p_data    => l_msg_data
1338           );
1339 
1340         END;
1341 
1342     <<END_OF_DEDUCTION>>
1343     NULL;
1344     END LOOP;
1345    EXIT WHEN open_ded_csr%NOTFOUND;
1346   END LOOP;
1347      -- Need to Call the Log procedure
1348         IF OZF_DEBUG_HIGH_ON THEN
1349                 OZF_Utility_PVT.write_conc_log('Credit Count' || l_exactmatchTbl.count);
1350                 OZF_Utility_PVT.write_conc_log('Credit Possible Count' || l_possiblematchTbl.count);
1351                 OZF_Utility_PVT.write_conc_log('Accrual Count' || l_accrualmatchTbl.count);
1352         END IF;
1353 
1354         -- For logging
1355                 Create_Log(
1356                            p_api_version         => 1.0
1357                           ,p_init_msg_list       => FND_API.g_false
1358                           ,p_commit              => FND_API.g_false
1359                           ,p_validation_level    => FND_API.g_valid_level_full
1360                           ,p_exact_match_tbl     => l_exactmatchTbl
1361                           ,p_possible_match_tbl  => l_possiblematchTbl
1362                           ,p_accrual_match_tbl   => l_accrualmatchTbl
1363                           ,x_return_status       => l_return_status
1364                           ,x_msg_count           => l_msg_data
1365                           ,x_msg_data            => l_msg_count
1366                         );
1367 
1368                  IF l_return_status =  fnd_api.g_ret_sts_error THEN
1369                         RAISE FND_API.g_exc_error;
1370                  ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1371                          RAISE FND_API.g_exc_unexpected_error;
1372                  END IF;
1373 
1374      CLOSE open_ded_csr;
1375 
1376 
1377   END IF;
1378 
1379 EXCEPTION
1380  WHEN FND_API.G_EXC_ERROR THEN
1381         ROLLBACK TO RuleBased;
1382         FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1383         OZF_UTILITY_PVT.write_conc_log;
1384         FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1385         FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1386         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Rule Based Engine Failed. ');
1387         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || FND_MSG_PUB.get(FND_MSG_PUB.count_msg, FND_API.g_false));
1388         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1389  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1390         ROLLBACK TO RuleBased;
1391         FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1392         OZF_UTILITY_PVT.write_conc_log;
1393         FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1394         FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1395         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Rule Based Engine Failed. ');
1396         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || FND_MSG_PUB.get(FND_MSG_PUB.count_msg, FND_API.g_false));
1397         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1398 
1399  WHEN OTHERS THEN
1400         ROLLBACK TO RuleBased;
1401         FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1402         IF OZF_DEBUG_HIGH_ON THEN
1403            FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1404            FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1405            FND_MSG_PUB.Add;
1406         END IF;
1407         OZF_UTILITY_PVT.write_conc_log;
1408         FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1409         FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1410         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Rule Based Engine Failed. ');
1411         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || SQLCODE||SQLERRM);
1412         FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1413  END Start_Rule_Based_Settlement;
1414 
1415 
1416 End OZF_claim_Utility_pvt;
1417