1 PACKAGE BODY HZ_IMP_DNB_POSTPROC_PKG AS
2 /*$Header: ARHLDBPB.pls 120.6 2005/10/30 04:20:27 appldev noship $*/
3
4 /*
5 This would check if a file has more than zero bytes.
6 If it is more than zero bytes, then returns true.
7 If it is zero bytes, then it will return false.
8 */
9
10 FUNCTION file_populated
11 (p_dir_name IN VARCHAR2,
12 p_file_name IN VARCHAR2,
13 p_operation IN VARCHAR2)
14 RETURN BOOLEAN IS
15
16 l_return_status BOOLEAN;
17 l_f_type UTL_FILE.file_type;
18 l_buffer VARCHAR2(32767);
19
20 BEGIN
21 l_return_status := FALSE;
22 l_f_type := UTL_FILE.fopen(p_dir_name,p_file_name, p_operation,32767);
23 BEGIN
24 UTL_FILE.get_line(l_f_type,l_buffer, 1000);
25 IF (LENGTH(l_buffer) > 0) THEN
26 l_return_status := TRUE;
27 ELSE
28 l_return_status := FALSE;
29 END IF;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 FND_FILE.PUT_LINE(FND_FILE.LOG,'UTL_FILE.GET_LINE - NO_DATA_FOUND');
33 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
34 l_return_status := FALSE;
35 WHEN VALUE_ERROR THEN
36 FND_FILE.PUT_LINE(FND_FILE.LOG,'UTL_FILE.GET_LINE - VALUE_ERROR');
37 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
38 l_return_status := FALSE;
39 END;
40 RETURN l_return_status;
41 EXCEPTION
42 WHEN UTL_FILE.invalid_path THEN
43 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UTL_FILE.FOPEN - invalid path');
44 RAISE FND_API.G_EXC_ERROR;
45 WHEN UTL_FILE.invalid_mode THEN
46 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UTL_FILE.FOPEN - invalid mode');
47 RAISE FND_API.G_EXC_ERROR;
48 WHEN UTL_FILE.invalid_operation THEN
49 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UTL_FILE.FOPEN - invalid operation');
50 -- this only means that file did not even get created
51 -- that is, all the records were successfull
52 l_return_status := FALSE;
53 RETURN l_return_status;
54 WHEN UTL_FILE.invalid_maxlinesize THEN
55 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UTL_FILE.FOPEN - invalid max line size');
56 RAISE FND_API.G_EXC_ERROR;
57 WHEN others THEN
58 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END file_populated;
61
62
63 PROCEDURE POST_PROCESSING (
64 errbuf OUT NOCOPY VARCHAR2,
65 retcode OUT NOCOPY VARCHAR2,
66 p_batchid IN VARCHAR2)IS
67
68 l_return_status VARCHAR2(1);
69 l_msg_count NUMBER;
70 l_tmp NUMBER;
71 l_error_message fnd_new_messages.message_text%TYPE;
72 l_dirname VARCHAR2(4000);
73 l_bad_fname VARCHAR2(400);
74 l_func_ret BOOLEAN;
75
76 BEGIN
77 -- This would have all the post processing steps
78 -- after the DNB adapter popultaing the interface tables.
79 -- flow
80 -- 0. Check if previous stage is sucessfull or not.
81 -- If not sucessfull, then
82 -- CleanUp()
83 -- else --if sucessful
84 -- Call batch activate api()
85 -- Analyze stats()
86 -- end if;
87
88 --Standard start of API savepoint
89 SAVEPOINT dnb_adapter_pp;
90
91 --Initialize API return status to success.
92 l_return_status := FND_API.G_RET_STS_SUCCESS;
93 retcode := 0;
94
95 FND_FILE.PUT_LINE(FND_FILE.LOG, ' bfr calling api batchid:'||p_batchid);
96
97 if (p_batchid is not null) then
98 l_tmp := to_number(p_batchid);
99
100 -- 2. Call batch activate api.
101 HZ_IMP_BATCH_SUMMARY_V2PUB.activate_batch (
102 p_init_msg_list => FND_API.G_TRUE,
103 p_batch_id => p_batchid,
104 x_return_status => l_return_status,
105 x_msg_count => l_msg_count,
106 x_msg_data => l_error_message);
107
108 FND_FILE.PUT_LINE(FND_FILE.LOG, 'batch sumry api ret status:'||l_return_status);
109
110 CASE l_return_status
111 WHEN FND_API.G_RET_STS_ERROR THEN
112 FND_FILE.PUT_LINE(FND_FILE.LOG, 'HZ_IMP_BATCH_SUMMARY_V2PUB.activate_batch errored');
113 RAISE FND_API.G_EXC_ERROR;
114 WHEN FND_API.G_RET_STS_UNEXP_ERROR THEN
115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'HZ_IMP_BATCH_SUMMARY_V2PUB.activate_batch errored unexpectedly');
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 WHEN FND_API.G_RET_STS_SUCCESS THEN
118 retcode := 0;
119 -- check to see if the user has any bad data in bad file.
120 BEGIN
121 SELECT ARGUMENT1, ARGUMENT4 --, ARGUMENT5
122 INTO l_dirname, l_bad_fname
123 FROM FND_CONCURRENT_REQUESTS
124 WHERE PRIORITY_request_id = FND_GLOBAL.CONC_PRIORITY_REQUEST
125 AND program_application_id = FND_GLOBAL.PROG_APPL_ID
126 AND CONCURRENT_PROGRAM_ID =
127 (SELECT CONCURRENT_PROGRAM_ID
128 FROM fnd_concurrent_programs
129 WHERE application_id = FND_GLOBAL.PROG_APPL_ID
130 AND concurrent_program_name = 'ARHLDDNB3');
131
132 FND_FILE.PUT_LINE(FND_FILE.LOG, 'directory name:'||l_dirname);
133 FND_FILE.PUT_LINE(FND_FILE.LOG, 'file name:'||l_bad_fname);
134
135 l_func_ret := file_populated(l_dirname,l_bad_fname, 'r');
136
137 IF l_func_ret THEN
138 FND_FILE.PUT_LINE(FND_FILE.LOG, 'A bad file is generated. Please check and correct the data');
139 retcode := 1;
140 ELSE
141 retcode := 0;
142 END IF;
143 END;
144 END CASE;
145 ELSE
146 retcode := 2;
147 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Id is NULL.Pass a valid batch id');
148 end if;
149
150 EXCEPTION
151 WHEN FND_API.G_EXC_ERROR THEN
152 ROLLBACK TO dnb_adapter_pp;
153 retcode := 1;
154 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Expected Error Occured');
155 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------');
156 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
157 FND_FILE.PUT_LINE(FND_FILE.LOG, 'batchid:'||p_batchid);
158 FND_MSG_PUB.Reset;
159 --FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
160 FOR i IN 1..l_msg_count LOOP
161 --l_error_message := FND_MESSAGE.get;
162 l_error_message := FND_MSG_PUB.Get(
163 p_msg_index => i,
164 p_encoded => FND_API.G_FALSE);
165 errbuf := l_error_message;
166 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_message);
167 END LOOP;
168 FND_FILE.close;
169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
170 ROLLBACK TO dnb_adapter_pp;
171 retcode := 2;
172 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UnExpected Error Occured');
173 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------');
174 FND_FILE.PUT_LINE(FND_FILE.LOG, sqlerrm);
175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'batchid:'||p_batchid);
176 FND_MSG_PUB.Reset;
177 --FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
178 FOR i IN 1..l_msg_count LOOP
179 --l_error_message := FND_MESSAGE.get;
180 l_error_message := FND_MSG_PUB.Get(
181 p_msg_index => i,
182 p_encoded => FND_API.G_FALSE);
183 errbuf := l_error_message;
184 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_message);
185 END LOOP;
186 FND_FILE.close;
187 WHEN OTHERS THEN
188 ROLLBACK TO dnb_adapter_pp;
189 retcode := 2;
190 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
191 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
192 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SQLERRM: ' || SQLERRM);
193 FND_FILE.PUT_LINE(FND_FILE.LOG, 'batchid:'||p_batchid);
194 FND_MSG_PUB.Reset;
195 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
196 l_error_message := FND_MSG_PUB.Get(
197 p_msg_index => i,
198 p_encoded => FND_API.G_FALSE);
199 errbuf := l_error_message;
200 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_message);
201 END LOOP;
202 FND_FILE.close;
203 END POST_PROCESSING;
204
205 END HZ_IMP_DNB_POSTPROC_PKG;