[Home] [Help]
PACKAGE BODY: APPS.IEX_PAYMENTS_BATCH_PUB
Source
1 PACKAGE BODY IEX_PAYMENTS_BATCH_PUB as
2 /* $Header: iexpypbb.pls 120.3 2006/05/30 17:49:23 scherkas noship $ */
3
4 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 G_APP_ID CONSTANT NUMBER := 695;
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_PAYMENTS_BATCH_PUB';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpypbb.pls';
9 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
10 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
11 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
12 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
13 G_START_DATE DATE;
14 G_END_DATE DATE;
15 G_DATES_LOADED BOOLEAN := FALSE;
16
17 procedure debug(p_msg varchar2) is
18 begin
19 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
20 iex_debug_pub.LogMessage(p_msg);
21 END IF;
22 end;
23
24 PROCEDURE IEX_PAYMENTS_CONCUR(
25 ERRBUF OUT NOCOPY VARCHAR2,
26 RETCODE OUT NOCOPY VARCHAR2) IS
27 l_msg_count number;
28 BEGIN
29 debug('IEX_PAYMENTS_CONCUR: Start');
30 PROCESS_PAYMENTS(
31 P_API_VERSION => 1.0,
32 P_INIT_MSG_LIST => FND_API.G_TRUE,
33 P_COMMIT => FND_API.G_TRUE,
34 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
35 X_RETURN_STATUS => RETCODE,
36 X_MSG_COUNT => l_msg_count,
37 X_MSG_DATA => ERRBUF);
38 debug('IEX_PAYMENTS_CONCUR: End');
39 END;
40
41
42 PROCEDURE LOAD_DATES IS
43 CURSOR get_start_date_crs(p_request_id number)
44 IS
45 select max(ACTUAL_START_DATE)
46 from FND_CONC_REQ_SUMMARY_V
47 where PROGRAM_SHORT_NAME = 'IEX_PROCESS_PAYMENTS'
48 and (program_application_id = 695 or program_application_id = 222) -- fixed a perf bug 4930381
49 and request_id <> p_request_id;
50
51 CURSOR get_end_date_crs(p_request_id number)
52 IS
53 select ACTUAL_START_DATE
54 from FND_CONC_REQ_SUMMARY_V
55 where PROGRAM_SHORT_NAME = 'IEX_PROCESS_PAYMENTS'
56 and (program_application_id = 695 or program_application_id = 222) -- fixed a perf bug 4930381
57 and request_id = p_request_id;
58
59 l_date date;
60 BEGIN
61 debug('LOAD_DATES: Start');
62 debug('LOAD_DATES: current G_REQUEST_ID: ' || G_REQUEST_ID);
63 if G_DATES_LOADED = FALSE then
64 debug('LOAD_DATES: Loading dates...');
65
66 OPEN get_start_date_crs(p_request_id => G_REQUEST_ID);
67 debug('LOAD_DATES: get_start_date_crs is opened');
68
69 FETCH get_start_date_crs INTO l_date;
70 if get_start_date_crs%FOUND then
71 G_START_DATE := l_date;
72 debug('LOAD_DATES: G_START_DATE: ' || G_START_DATE);
73 else
74 G_START_DATE := null;
75 debug('LOAD_DATES: G_START_DATE is null');
76 end if;
77 CLOSE get_start_date_crs;
78 debug('LOAD_DATES: get_start_date_crs is closed');
79
80 OPEN get_end_date_crs(p_request_id => G_REQUEST_ID);
81 debug('LOAD_DATES: get_end_date_crs is opened');
82
83 FETCH get_end_date_crs INTO l_date;
84 if get_end_date_crs%FOUND then
85 G_END_DATE := l_date;
86 debug('LOAD_DATES: G_END_DATE: ' || G_END_DATE);
87 else
88 G_START_DATE := null;
89 debug('LOAD_DATES: G_END_DATE is null');
90 end if;
91 CLOSE get_end_date_crs;
92 debug('LOAD_DATES: get_end_date_crs is closed');
93
94 G_DATES_LOADED := TRUE;
95 else
96 debug('LOAD_DATES: Dates are loaded already');
97 debug('LOAD_DATES: G_START_DATE: ' || G_START_DATE);
98 debug('LOAD_DATES: G_END_DATE: ' || G_END_DATE);
99 end if;
100 debug('LOAD_DATES: End');
101 END;
102
103 PROCEDURE PROCESS_PAYMENTS(
104 P_API_VERSION IN NUMBER,
105 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
106 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
107 P_VALIDATION_LEVEL IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
108 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
109 X_MSG_COUNT OUT NOCOPY NUMBER,
110 X_MSG_DATA OUT NOCOPY VARCHAR2)
111 IS
112 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PAYMENTS';
113 l_api_version CONSTANT NUMBER := 1.0;
114 l_return_status VARCHAR2(1);
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(32767);
117 Type refCur is Ref Cursor;
118 l_curs refCur;
119 vSQL varchar2(1000);
120
121 i number := 0;
122 l_inv_tbl IEX_PAYMENTS_BATCH_PUB.CL_INV_TBL_TYPE;
123 l_psa number;
124 l_org number;
125
126 BEGIN
127 debug('PROCESS_PAYMENTS: Start');
128 LOAD_DATES;
129
130 -- Standard start of API savepoint
131 SAVEPOINT PROCESS_PAYMENTS_PVT;
132
133 debug('PROCESS_PAYMENTS: Savepoint is established');
134
135 -- Standard call to check for call compatibility
136 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139
140 -- Initialize message list if p_init_msg_list is set to TRUE
141 IF FND_API.To_Boolean(p_init_msg_list) THEN
142 FND_MSG_PUB.initialize;
143 END IF;
144
145 -- Initialize API return status to success
146 l_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 -- START OF BODY OF API
149
150 debug('PROCESS_PAYMENTS: Start of PROCESS_PAYMENTS body');
151
152 debug('PROCESS_PAYMENTS: Looking for closed payment schedules...');
153 -- get paid and closed payment_schedules
154
155
156 if G_START_DATE is not null then
157 vSQL := 'SELECT PSA.PAYMENT_SCHEDULE_ID ' ||
158 'FROM ' ||
159 'AR_PAYMENT_SCHEDULES PSA, ' ||
160 'IEX_DELINQUENCIES DEL ' ||
161 'WHERE ' ||
162 '(PSA.ACTUAL_DATE_CLOSED BETWEEN trunc(:P_START_DATE) AND trunc(:P_END_DATE)) AND ' ||
163 'PSA.STATUS = ''CL'' AND ' ||
164 'DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID AND ' ||
165 'DEL.STATUS = ''OPEN''';
166
167 debug('PROCESS_PAYMENTS: SQL: ' || vSQL);
168 open l_curs for vSQL
169 using G_START_DATE, G_END_DATE;
170 else
171 vSQL := 'SELECT PSA.PAYMENT_SCHEDULE_ID ' ||
172 'FROM ' ||
173 'AR_PAYMENT_SCHEDULES PSA, ' ||
174 'IEX_DELINQUENCIES DEL ' ||
175 'WHERE ' ||
176 'trunc(PSA.ACTUAL_DATE_CLOSED) <= trunc(:P_END_DATE) AND ' ||
177 'PSA.STATUS = ''CL'' AND ' ||
178 'DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID AND ' ||
179 'DEL.STATUS <> ''CLOSED''';
180
181 debug('PROCESS_PAYMENTS: SQL: ' || vSQL);
182 open l_curs for vSQL
183 using G_END_DATE;
184 end if;
185
186 i := 0;
187 LOOP
188 FETCH l_curs INTO l_psa;
189 EXIT WHEN l_curs%NOTFOUND;
190
191 i := i+1;
192 l_inv_tbl(i) := l_psa;
193 debug('PROCESS_PAYMENTS: loop #' || i);
194 debug('PROCESS_PAYMENTS: Found closed payment schedule with PAYMENT_SCHEDULE_ID = ' || l_inv_tbl(i));
195 END LOOP;
196 CLOSE l_curs;
197 debug('PROCESS_PAYMENTS: l_curs cursor is closed');
198
199 debug('PROCESS_PAYMENTS: table count: ' || i);
200 if i > 0 then
201 debug('PROCESS_PAYMENTS: Calling Close_Delinquencies...');
202 IEX_DELINQUENCY_PUB.Close_Delinquencies(
203 p_api_version => l_api_version,
204 p_init_msg_list => FND_API.G_FALSE, -- passed 'F' to get all debug messages
205 p_payments_tbl => l_inv_tbl,
206 p_security_check => 'N',
207 x_return_status => l_return_status,
208 x_msg_count => l_msg_count,
209 x_msg_data => l_msg_data);
210
211 -- check for errors
212 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
213 debug('PROCESS_PAYMENTS: call Close_Delinquencies failed');
214 ELSE
215 debug('PROCESS_PAYMENTS: call Close_Delinquencies succeeded');
216 END IF;
217 else
218 debug('PROCESS_PAYMENTS: No closed payment_schedules found');
219 end if;
220 debug('PROCESS_PAYMENTS: End of PROCESS_PAYMENTS body');
221 -- END OF BODY OF API
222
223 -- Standard check of p_commit.
224 IF FND_API.To_Boolean( p_commit ) THEN
225 COMMIT WORK;
226 END IF;
227
228 x_return_status := l_return_status;
229 -- Standard call to get message count and if count is 1, get message info
230 FND_MSG_PUB.Count_And_Get(
231 p_encoded => FND_API.G_FALSE,
232 p_count => x_msg_count,
233 p_data => x_msg_data);
234 debug('PROCESS_PAYMENTS: End');
235
236 EXCEPTION
237 WHEN FND_API.G_EXC_ERROR THEN
238 debug('PROCESS_PAYMENTS: In FND_API.G_EXC_ERROR exception');
239 ROLLBACK TO PROCESS_PAYMENTS_PVT;
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 debug('PROCESS_PAYMENTS: In FND_API.G_EXC_UNEXPECTED_ERROR exception');
244 ROLLBACK TO PROCESS_PAYMENTS_PVT;
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
247 WHEN OTHERS THEN
248 debug('PROCESS_PAYMENTS: In OTHERS exception');
249 ROLLBACK TO PROCESS_PAYMENTS_PVT;
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
252 THEN
253 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
254 END IF;
255 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
256
257 END;
258
259 END;