[Home] [Help]
PACKAGE BODY: APPS.OZF_AUTO_WRITEOFF_PVT
Source
1 PACKAGE BODY OZF_AUTO_WRITEOFF_PVT AS
2 /* $Header: ozfvcwob.pls 120.5 2006/01/30 07:36:23 sshivali ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Auto_Writeoff_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE : This package is created for Bug#:2757570
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_Auto_Writeoff_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvcwob.pls';
19
20 ---------------------------------------------------------------------
21 -- PROCEDURE
22 -- Populate_Auto_Writeoff_Data
23 --
24 -- HISTORY
25 -- aadhawad Create.
26 --
27 ---------------------------------------------------------------------
28 PROCEDURE Populate_Auto_Writeoff_Data(
29 ERRBUF OUT NOCOPY VARCHAR2,
30 RETCODE OUT NOCOPY NUMBER,
31 p_org_id IN NUMBER DEFAULT NULL,
32 p_claim_class IN VARCHAR2,
33 p_cust_account_id IN NUMBER,
34 p_claim_type_id IN NUMBER,
35 p_reason_code_id IN NUMBER
36 )
37 IS
38
39 -- Local Variable Declaration
40 l_retcode NUMBER := 0;
41 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS ;
42 l_msg_count NUMBER;
43 l_msg_Data VARCHAR2(2000);
44 l_object_version NUMBER(9);
45 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
46
47 v_total_writeoff_count NUMBER := 0;
48 v_tot_writeoff_processed NUMBER := 0;
49 v_tot_writeoff_not_processed NUMBER := 0;
50
51 l_claim_class_mean VARCHAR2(80);
52 l_claim_type_name VARCHAR2(80);
53 l_reason_code_name VARCHAR2(80);
54 l_party_name VARCHAR2(240);
55
56 -- Cursor Declaration
57 -- Main Auto Writeoff Cursor
58 CURSOR get_auto_writeoff_claims_csr(p_claim_class IN VARCHAR2
59 ,p_cust_account_id IN NUMBER
60 ,p_claim_type_id IN NUMBER
61 ,p_reason_code_id IN NUMBER) IS
62 SELECT claim_id,
63 object_version_number,
64 claim_number,
65 payment_method,
66 status_code,
67 user_status_id
68 FROM ozf_claims
69 WHERE cust_account_id = NVL(p_cust_account_id , cust_account_id)
70 AND claim_type_id = NVL(p_claim_type_id , claim_type_id)
71 AND reason_code_id = NVL(p_reason_code_id , reason_code_id)
72 AND claim_class = NVL(p_claim_class , claim_class)
73 AND claim_class IN ('DEDUCTION', 'OVERPAYMENT')
74 AND status_code in ('OPEN','COMPLETE')
75 AND write_off_flag = 'T';
76
77 CURSOR get_claim_type_csr(p_claim_type_id IN NUMBER) IS
78 SELECT name
79 FROM ozf_claim_types_all_vl
80 WHERE claim_type_id = p_claim_type_id;
81
82 CURSOR get_reason_code_csr(p_reason_code_id IN NUMBER) IS
83 SELECT name
84 FROM ozf_reason_codes_all_vl
85 WHERE reason_code_id = p_reason_code_id;
86
87 CURSOR get_claim_class_csr(p_claim_class IN VARCHAR2) IS
88 SELECT meaning
89 FROM ozf_lookups
90 WHERE lookup_code = p_claim_class
91 AND lookup_type = 'OZF_CLAIM_CLASS';
92
93 CURSOR get_account_name_csr(p_cust_account_id IN NUMBER) IS
94 SELECT p.party_name
95 FROM hz_parties p
96 , hz_cust_accounts c
97 WHERE p.party_id = c.party_id
98 AND c.cust_account_id = p_cust_account_id;
99
100 --Multiorg Changes
101 CURSOR operating_unit_csr IS
102 SELECT ou.organization_id org_id
103 FROM hr_operating_units ou
104 WHERE mo_global.check_access(ou.organization_id) = 'Y';
105
106 m NUMBER := 0;
107 l_org_id OZF_UTILITY_PVT.operating_units_tbl;
108
109 BEGIN
110 --Multiorg Changes
111 MO_GLOBAL.init('OZF');
112
113 IF p_org_id IS NULL THEN
114 MO_GLOBAL.set_policy_context('M',null);
115 OPEN operating_unit_csr;
116 LOOP
117 FETCH operating_unit_csr into l_org_id(m);
118 m := m + 1;
119 EXIT WHEN operating_unit_csr%NOTFOUND;
120 END LOOP;
121 CLOSE operating_unit_csr;
122 ELSE
123 l_org_id(m) := p_org_id;
124 END IF;
125
126 --Multiorg Changes
127 IF (l_org_id.COUNT > 0) THEN
128 FOR m IN l_org_id.FIRST..l_org_id.LAST LOOP
129 MO_GLOBAL.set_policy_context('S',l_org_id(m));
130 -- Write OU info to OUT file
131 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Operating Unit: ' || MO_GLOBAL.get_ou_name(l_org_id(m)));
132 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '-----------------------------------------------------');
133 -- Write OU info to LOG file
134 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: ' || MO_GLOBAL.get_ou_name(l_org_id(m)));
135 FND_FILE.PUT_LINE(FND_FILE.LOG, '-----------------------------------------------------');
136
137 -- Get meaning for the names
138 IF p_claim_class is not null then
139 open get_claim_class_csr (p_claim_class);
140 fetch get_claim_class_csr into l_claim_class_mean;
141 close get_claim_class_csr;
142 end if;
143
144 IF p_claim_type_id is not null then
145 open get_claim_type_csr (p_claim_type_id);
146 fetch get_claim_type_csr into l_claim_type_name;
147 close get_claim_type_csr;
148 end if;
149
150 IF p_reason_code_id is not null then
151 open get_reason_code_csr (p_reason_code_id);
152 fetch get_reason_code_csr into l_reason_code_name;
153 close get_reason_code_csr;
154 end if;
155
156 IF p_cust_account_id is not null then
157 open get_account_name_csr (p_cust_account_id);
158 fetch get_account_name_csr into l_party_name;
159 close get_account_name_csr;
160 end if;
161
162 -- Write Batch Initialization summary info to LOG file
163 FND_FILE.PUT_LINE(FND_FILE.LOG, '+------------------- AUTO-WRITEOFF PROCESS START SUMMARY -------------------+');
164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Starts on: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
165 FND_FILE.PUT_LINE(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
166 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Parameter1-Claim Class: '||l_claim_class_mean);
167 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Parameter2-Customer : '||l_party_name);
168 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Parameter3-Claim Type : '||l_claim_type_name);
169 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Parameter4-Reason Code: '||l_reason_code_name);
170 FND_FILE.PUT_LINE(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
171
172 -- Write Batch Initialization summary info to OUT file
173 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+------------------- AUTO-WRITEOFF PROCESS START SUMMARY -------------------+');
174 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Started on: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
175 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
176 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Parameter1-Claim Class: '||l_claim_class_mean);
177 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Parameter2-Customer : '||l_party_name);
178 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Parameter3-Claim Type : '||l_claim_type_name);
179 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Parameter4-Reason Code: '||l_reason_code_name);
180 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
181
182 --bug#4940880 Update Payment Method
183 UPDATE ozf_claims
184 SET payment_method = 'WRITE_OFF'
185 WHERE cust_account_id = NVL(p_cust_account_id , cust_account_id)
186 AND claim_type_id = NVL(p_claim_type_id , claim_type_id)
187 AND reason_code_id = NVL(p_reason_code_id , reason_code_id)
188 AND claim_class = NVL(p_claim_class , claim_class)
189 AND claim_class IN ('DEDUCTION', 'OVERPAYMENT')
190 AND status_code in ('OPEN','COMPLETE')
191 AND write_off_flag = 'T';
192
193 -- Main Cursor
194 FOR l_writeoff_rec in get_auto_writeoff_claims_csr(p_claim_class
195 ,p_cust_account_id
196 ,p_claim_type_id
197 ,p_reason_code_id) LOOP
198
199 FND_MSG_PUB.initialize;
200 -- Count total records selected for write off.
201 v_total_writeoff_count := v_total_writeoff_count + 1;
202
203 BEGIN
204 SAVEPOINT Populate_Auto_Writeoff_Data;
205
206 -- Start: Build l_claim_rec.
207 l_claim_rec.claim_id := l_writeoff_rec.claim_id;
208 l_claim_rec.object_version_number := l_writeoff_rec.object_version_number;
209 l_claim_rec.claim_number := l_writeoff_rec.claim_number;
210 l_claim_rec.status_code := l_writeoff_rec.status_code;
211 l_claim_rec.user_status_id := l_writeoff_rec.user_status_id;
212 l_claim_rec.payment_method := l_writeoff_rec.payment_method;
213 -- End: Build l_claim_rec
214
215 --Assign claim status to 'CLOSED'
216 l_claim_rec.status_code := 'CLOSED';
217 l_claim_rec.user_status_id := null;
218 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'STATUS CODE: '||l_claim_rec.status_code);
219
220 --Assign Payment Method to 'WRITE OFF'
221 l_claim_rec.payment_method := 'WRITE_OFF';
222
223 --
224 -- ************************************************************************************
225 --Call OZF_CLAIM_PVT.Update_Claim to change the status to CLOSE.
226 OZF_CLAIM_PVT.Update_Claim (
227 p_api_version => 1.0
228 ,p_init_msg_list => FND_API.g_false
229 ,p_commit => FND_API.g_false
230 ,p_validation_level => FND_API.g_valid_level_full
231 ,x_return_status => l_return_status
232 ,x_msg_data => l_msg_data
233 ,x_msg_count => l_msg_count
234 ,p_claim => l_claim_rec
235 ,p_event => 'UPDATE'
236 ,p_mode => 'AUTO'
237 ,x_object_version_number => l_object_version
238 );
239 IF l_return_status = FND_API.g_ret_sts_error THEN
240 v_tot_writeoff_not_processed := v_tot_writeoff_not_processed + 1;
241 ROLLBACK TO Populate_Auto_Writeoff_Data;
242 --RAISE FND_API.g_exc_error;
243 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Auto Write off Failed for claim Number: '||l_claim_rec.claim_number);
244 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Auto Write off Failed for claim Number: '||l_claim_rec.claim_number);
245 OZF_UTILITY_PVT.write_conc_log;
246 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
247 v_tot_writeoff_not_processed := v_tot_writeoff_not_processed + 1;
248 ROLLBACK TO Populate_Auto_Writeoff_Data;
249 --RAISE FND_API.g_exc_unexpected_error;
250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Auto Write off Failed for claim Number: '||l_claim_rec.claim_number);
251 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Auto Write off Failed for claim Number: '||l_claim_rec.claim_number);
252 OZF_UTILITY_PVT.write_conc_log;
253 ELSE
254 v_tot_writeoff_processed := v_tot_writeoff_processed + 1;
255 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Auto Write off is Successful for claim Number: '||l_claim_rec.claim_number);
256 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Auto Write off is Successful for claim Number: '||l_claim_rec.claim_number);
257 END IF;
258 -- ************************************************************************************
259 EXCEPTION
260 WHEN OTHERS THEN
261 ROLLBACK TO Populate_Auto_Writeoff_Data;
262 END;
263 END LOOP;
264
265 -- Write Batch summary info to LOG file
266 FND_FILE.PUT_LINE(FND_FILE.LOG, '+-------------------- AUTO-WRITEOFF PROCESS END SUMMARY --------------------+');
267 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Ends on: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
268 FND_FILE.PUT_LINE(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Number of Claims Selected for Write offs........: '||v_total_writeoff_count);
270 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Number of Claims Processed for Write offs.......: '||v_tot_writeoff_processed);
271 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Number of Claims NOT Processed for Write offs...: '||v_tot_writeoff_not_processed);
272 FND_FILE.PUT_LINE(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
273
274 -- Write Batch summary info to OUT file
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+-------------------- AUTO-WRITEOFF PROCESS END SUMMARY --------------------+');
276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Job Ended on: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total Number of Claims Selected for Write offs........: '||v_total_writeoff_count);
279 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total Number of Claims Processed for Write offs.......: '||v_tot_writeoff_processed);
280 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total Number of Claims NOT Processed for Write offs...: '||v_tot_writeoff_not_processed);
281 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
282
283 END LOOP;
284 END IF;
285
286 EXCEPTION
287 WHEN FND_API.g_exc_error THEN
288 ROLLBACK TO Populate_Auto_Writeoff_Data;
289 OZF_UTILITY_PVT.write_conc_log;
290 ERRBUF := l_msg_data;
291 RETCODE := 2;
292 WHEN FND_API.g_exc_unexpected_error THEN
293 ROLLBACK TO Populate_Auto_Writeoff_Data;
294 OZF_UTILITY_PVT.write_conc_log;
295 ERRBUF := l_msg_data;
296 RETCODE := 2;
297 WHEN OTHERS THEN
298 ROLLBACK TO Populate_Auto_Writeoff_Data;
299 OZF_UTILITY_PVT.write_conc_log;
300 ERRBUF := substr(sqlerrm, 1, 80);
301 RETCODE := 2;
302
303 End Populate_Auto_Writeoff_Data;
304
305
306 END OZF_AUTO_WRITEOFF_PVT;