1 PACKAGE BODY OCM_RECOMMENDATIONS_PUB AS
2 /*$Header: ARCMRECB.pls 120.1 2006/03/23 01:02:46 bsarkar noship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 PROCEDURE debug (
7 p_message_name IN VARCHAR2 ) IS
8 BEGIN
9 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.ARCM_RECOMMENDATIONS_PUB' );
10 END;
11
12 PROCEDURE get_recommendations (
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2,
15 p_commit IN VARCHAR2,
16 p_validation_level IN VARCHAR2,
17 p_credit_request_id IN NUMBER,
18 p_case_folder_id IN NUMBER,
19 p_appealed_flag IN VARCHAR2,
20 p_recommendations_tbl OUT NOCOPY recommendations_tbl,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2 ) IS
24
25 i NUMBER := 1;
26 l_reco_ctr NUMBER := 0;
27
28 CURSOR cRecommendations IS
29 SELECT recommendation_id,
30 case_folder_id,
31 credit_request_id,
32 credit_recommendation,
33 recommendation_value1,
34 recommendation_value2,
35 status,
36 credit_type,
37 recommendation_name,
38 appealed_flag
39 FROM ar_cmgt_cf_recommends
40 WHERE credit_request_id = nvl(p_credit_request_id, credit_request_id)
41 AND nvl(appealed_flag, 'N') = p_appealed_flag
42 AND case_folder_id = nvl(p_case_folder_id, case_folder_id );
43
44 BEGIN
45 IF pg_debug = 'Y'
46 THEN
47 debug ( 'ARCM_RECOMMENDATIONS_PUB.get_recommendations(+)');
48 debug ( 'Credit Request Id : ' || p_credit_request_id );
49 debug ( 'Case Folder Id : ' || p_case_folder_id );
50 debug ( 'Appeal Flag : ' || p_appealed_flag );
51 END IF;
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53 IF p_credit_request_id IS NULL AND p_case_folder_id IS NULL
54 THEN
55 IF pg_debug = 'Y'
56 THEN
57 debug ( 'Both Credit Request and case folder Id is null');
58 END IF;
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 x_msg_data := 'Both Credit Request Id and case Folder Id cannot be null';
61 x_msg_count := 1;
62 return;
63 END IF;
64 For RecommendationsRec IN cRecommendations
65 LOOP
66 p_recommendations_tbl(i).recommendation_id := RecommendationsRec.recommendation_id;
67 p_recommendations_tbl(i).credit_request_id := RecommendationsRec.credit_request_id;
68 p_recommendations_tbl(i).case_folder_id := RecommendationsRec.case_folder_id;
69 p_recommendations_tbl(i).credit_recommendation := RecommendationsRec.credit_recommendation;
70 p_recommendations_tbl(i).recommendation_value1 := RecommendationsRec.recommendation_value1;
71 p_recommendations_tbl(i).recommendation_value2 := RecommendationsRec.recommendation_value2;
72 p_recommendations_tbl(i).status := RecommendationsRec.status;
73 p_recommendations_tbl(i).credit_type := RecommendationsRec.credit_type;
74 p_recommendations_tbl(i).recommendation_name := RecommendationsRec.recommendation_name;
75 p_recommendations_tbl(i).appealed_flag := RecommendationsRec.appealed_flag;
76
77 i := i +1 ;
78 END LOOP;
79
80 l_reco_ctr := p_recommendations_tbl.first;
81 IF l_reco_ctr IS NULL
82 THEN
83 x_return_status := FND_API.G_RET_STS_ERROR;
84 x_msg_data := 'No recommendations Exists';
85 x_msg_count := 1;
86 return;
87 END IF;
88 IF pg_debug = 'Y'
89 THEN
90 debug ( 'ARCM_RECOMMENDATIONS_PUB.get_recommendations(-)');
91 END IF;
92 END;
93
94 PROCEDURE mark_appeal (
95 p_api_version IN NUMBER,
96 p_init_msg_list IN VARCHAR2,
97 p_commit IN VARCHAR2,
98 p_validation_level IN VARCHAR2,
99 p_credit_request_id IN NUMBER,
100 p_case_folder_id IN NUMBER,
101 p_appealing_reco_tbl IN appealing_reco_tbl,
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_msg_count OUT NOCOPY NUMBER,
104 x_msg_data OUT NOCOPY VARCHAR2 ) IS
105
106 l_check_rec_exists NUMBER;
107 l_credit_request_id ar_cmgt_credit_requests.credit_request_id%type;
108 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
109
110 BEGIN
111 IF pg_debug = 'Y'
112 THEN
113 debug ( 'ARCM_RECOMMENDATIONS_PUB.mark_appeal(+)');
114 debug ( 'Credit Request Id : ' || p_credit_request_id );
115 debug ( 'Case Folder Id : ' || p_case_folder_id );
116 END IF;
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118
119 IF p_credit_request_id IS NULL and p_case_folder_id IS NULL
120 THEN
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 x_msg_data := 'Both Credit Request Id and case Folder Id cannot be null';
123 x_msg_count := 1;
124 return;
125 END IF;
126
127 -- check the status of the case folder. It must be closed for appealing.
128 BEGIN
129 SELECT credit_request_id, case_folder_id
130 INTO l_credit_request_id, l_case_folder_id
131 FROM ar_cmgt_case_folders
132 WHERE case_folder_id = nvl(p_case_folder_id, case_folder_id )
133 AND credit_request_id = nvl(p_credit_request_id, credit_request_id)
134 AND type = 'CASE'
135 AND status = 'CLOSED';
136
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 x_msg_data := 'Either Case Folder Does not Exists or Not Closed.';
141 x_msg_count := 1;
142 return;
143 WHEN OTHERS THEN
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 x_msg_data := 'Error while fetching case folder '||sqlerrm;
146 x_msg_count := 1;
147 return;
148
149 END;
150 l_check_rec_exists := p_appealing_reco_tbl.first;
151 --fist updates all records to null
152 BEGIN
153 UPDATE ar_cmgt_cf_recommends
154 SET appealed_flag = NULL
155 WHERE case_folder_id = l_case_folder_id;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 x_msg_data := 'Error while updating Recommendation' ||sqlerrm;
161 x_msg_count := 1;
162 return;
163 END;
164
165 IF l_check_rec_exists >= 1
166 THEN
167 IF pg_debug = 'Y'
168 THEN
169 debug ( 'Record Exists ');
170 END IF;
171
172 FOR i in p_appealing_reco_tbl.first .. p_appealing_reco_tbl.last
173 LOOP
174 IF pg_debug = 'Y'
175 THEN
176 debug ( 'Recommendation Id ' || p_appealing_reco_tbl(i).recommendation_id);
177 END IF;
178 IF p_appealing_reco_tbl(i).recommendation_id IS NOT NULL
179 THEN
180 UPDATE ar_cmgt_cf_recommends
181 SET appealed_flag = 'Y'
182 WHERE recommendation_id = p_appealing_reco_tbl(i).recommendation_id;
183 END IF;
184 END LOOP;
185 ELSE -- table is not pupoltaed. Update all of the recommendations
186 -- first updates all of
187 UPDATE ar_cmgt_cf_recommends
188 SET appealed_flag = 'Y'
189 WHERE case_folder_id = l_case_folder_id;
190
191 END IF;
192 END;
193
194 END OCM_RECOMMENDATIONS_PUB;
[Home] [Help]