[Home] [Help]
PACKAGE BODY: APPS.CN_POST_COLLECTION_TAE_PUB
Source
1 PACKAGE BODY CN_POST_COLLECTION_TAE_PUB AS
2 --$Header: cnppcolb.pls 120.8.12000000.2 2007/08/06 22:50:50 achanda ship $
3
4 --$Header: cnppcolb.pls 120.8.12000000.2 2007/08/06 22:50:50 achanda ship $
5
6 --Global Variables
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_POST_COLLECTION_TAE_PUB';
8 G_LAST_UPDATE_DATE DATE := Sysdate;
9 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
10 G_CREATION_DATE DATE := Sysdate;
11 G_CREATED_BY NUMBER := fnd_global.user_id;
12 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
13
14
15 ---------------------------------------------------------------------------------+
16 -- ** Public Procedures
17 ---------------------------------------------------------------------------------+
18
19 -- Start of comments
20 -- API name : get_assignments
21 -- Type : Public
22 -- Function : This Public API is used to get TAE assignments
23 -- Pre-reqs : None.
24 -- Parameters :
25 -- IN : p_api_version NUMBER Required
26 -- p_init_msg_list VARCHAR2 Optional
27 -- Default = FND_API.G_FALSE
28 -- p_commit VARCHAR2 Optional
29 -- Default = FND_API.G_FALSE
30 -- p_validation_level NUMBER Optional
31 -- Default = FND_API.G_VALID_LEVEL_FULL
32
33 -- OUT : x_return_status VARCHAR2(1)
34 -- x_msg_count NUMBER
35 -- x_msg_data VARCHAR2(2000)
36
37 -- Notes : Note text
38
39 -- End of comments
40
41 PROCEDURE get_assignments
42 ( p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
44 p_commit IN VARCHAR2 := FND_API.G_FALSE,
45 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
46 x_start_period_id IN cn_periods.period_id%TYPE,
47 x_end_period_id IN cn_periods.period_id%TYPE,
48 x_conc_program_id IN NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52 x_org_id IN NUMBER
53 )
54 IS
55
56 l_api_name CONSTANT VARCHAR2(30) := 'get_assignments';
57 l_api_version CONSTANT NUMBER := 1.0;
58
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(2000);
61
62 l_start_date DATE;
63 l_end_date DATE;
64 l_where_clause varchar2(1000);
65
66 l_retcode VARCHAR2(100);
67
68 l_return_status VARCHAR2(30);
69 l_errbuf varchar2(3000);
70 errbuf varchar2(32767);
71 retcode varchar2(260);
72
73 l_source_id NUMBER;
74 l_trans_object_type_id NUMBER;
75 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
76 l_org_id NUMBER;
77 l_version_name VARCHAR2(60);
78
79 BEGIN
80 -- Standard Start of API savepoint
81 SAVEPOINT get_assignments;
82 --+
83 -- Standard call to check for call compatibility.
84 --+
85 IF NOT FND_API.Compatible_API_Call ( l_api_version,
86 p_api_version,
87 l_api_name,
88 G_PKG_NAME )
89 THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean( p_init_msg_list )
95 THEN
96 FND_MSG_PUB.initialize;
97 END IF;
98
99 -- Initialize API return status to success
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 --+
103 -- User hooks
104 --+
105
106 -- customer pre-processing section
107 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
108 'GET_ASSIGNMENTS',
109 'B',
110 'C')
111 THEN
112 cn_post_col_tae_pub_cuhk.get_assignments_pre
113 (p_api_version => p_api_version,
114 p_init_msg_list => p_init_msg_list,
115 p_commit => p_commit,
116 p_validation_level => p_validation_level,
117 x_return_status => x_return_status,
118 x_msg_count => x_msg_count,
119 x_msg_data => x_msg_data);
120
121 IF x_return_status = fnd_api.g_ret_sts_error THEN
122 RAISE fnd_api.g_exc_error;
123 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
124 RAISE fnd_api.g_exc_unexpected_error;
125 END IF;
126 END IF;
127
128 -- vertical industry pre-processing section
129 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
130 'GET_ASSIGNMENTS',
131 'B',
132 'V')
133 THEN
134 cn_post_col_tae_pub_vuhk.get_assignments_pre
135 (p_api_version => p_api_version,
136 p_init_msg_list => p_init_msg_list,
137 p_commit => p_commit,
138 p_validation_level => p_validation_level,
139 x_return_status => x_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data);
142
143 IF x_return_status = fnd_api.g_ret_sts_error THEN
144 RAISE fnd_api.g_exc_error;
145 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
146 RAISE fnd_api.g_exc_unexpected_error;
147 END IF;
148 END IF;
149
150 --+
151 -- API body
152 --+
153
154 apps.FND_MSG_PUB.initialize;
155
156 l_source_id := -1001;
157 l_trans_object_type_id := -1002;
158 l_org_id:=x_org_id;
159 cn_periods_api.set_dates(x_start_period_id, x_end_period_id,l_org_id,l_start_date, l_end_date);
160 l_where_clause :=' WHERE org_id=to_number('''||l_org_id||''') and txn_date between to_date('''||l_start_date|| ''',''dd-mon-yy'')' || ' and to_date('''|| l_end_date || ''',''dd-mon-yy'') ';
161
162 BEGIN
163 select version_name
164 into l_version_name
165 from jty_trans_usg_pgm_sql
166 where source_id = -1001
167 and trans_type_id = -1002
168 and program_name = 'SALES/INCENTIVE COMPENSATION PROGRAM'
169 and enabled_flag = 'Y';
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_version_name := 'OTHER';
173 END;
174
175 IF (l_version_name = 'ORACLE') THEN
176 l_where_clause := l_where_clause || ' ' ||
177 'AND load_status = ''UNLOADED'' ' ||
178 'AND (adjust_status IS NULL OR adjust_status <> ''REVERSAL'') ' ||
179 'AND (adjust_comments IS NULL OR adjust_comments <> ''Created by TAE'') ';
180 END IF;
181
182 fnd_file.put_line(fnd_file.Log, 'Start: collect trans data<<');
183 -- Begin --
184 jty_assign_bulk_pub.collect_trans_data
185 ( p_api_version_number => 1.0,
186 p_init_msg_list => FND_API.G_FALSE,
187 p_source_id => -1001,
188 p_trans_id => -1002,
189 p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM',
190 p_mode => 'DATE EFFECTIVE',
191 p_where => l_where_clause,
192 p_no_of_workers => 1,
193 p_percent_analyzed => 20, -- this value can be either a profile option or a parameter to conc program
194 p_request_id => -1, -- request id of the concurrent program
195 x_return_status => x_return_status,
196 x_msg_count => x_msg_count,
197 x_msg_data => x_msg_data,
198 ERRBUF => errbuf,
199 RETCODE => retcode
200 );
201 fnd_file.put_line(fnd_file.Log, 'End: jty_assign_bulk_pub.collect_trans_data trans data');
202 IF (retcode = 0) THEN
203 fnd_file.put_line(fnd_file.Log, 'Start: get winners<<');
204 jty_assign_bulk_pub.get_winners
205 ( p_api_version_number => 1.0,
206 p_init_msg_list => FND_API.G_FALSE,
207 p_source_id => -1001,
208 p_trans_id => -1002,
209 p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM',
210 p_mode => 'DATE EFFECTIVE',
211 p_percent_analyzed => 20, -- this value can be either a profile option or a parameter to conc program
212 p_worker_id => 1,
213 x_return_status => x_return_status,
214 x_msg_count => x_msg_count,
215 x_msg_data => x_msg_data,
216 ERRBUF => errbuf,
217 RETCODE => retcode
218 );
219 fnd_file.put_line(fnd_file.Log, 'End: get winners<<');
220 END IF;
221 IF retcode <> 0 THEN
222 RAISE fnd_api.g_exc_error;
223 END IF;
224
225 IF retcode=0 THEN
226 fnd_file.put_line(fnd_file.Log, 'Start: Cn : Process trx records<<');
227 CN_PROCESS_TAE_TRX_PUB.Process_Trx_Records(
228
229 p_api_version => p_api_version,
230 p_init_msg_list => p_init_msg_list,
231 p_commit => p_commit,
232 p_validation_level => p_validation_level,
233
234 x_return_status => l_return_status,
235 x_msg_count => l_msg_count,
236 x_msg_data => l_msg_data,
237 p_org_id => l_org_id);
238 fnd_file.put_line(fnd_file.Log, 'End: Cn : Process trx records<<');
239 END IF;
240
241 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
242 RAISE FND_API.G_EXC_ERROR ;
243 END IF;
244
245 -- +
246 -- End of API body.
247 --+
248
249 --+
250 -- Post processing hooks
251 --+
252
253 -- SK Start of post processing hooks
254
255 -- vertical post processing section
256 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
257 'GET_ASSIGNMENTS',
258 'A',
259 'V')
260 THEN
261 cn_post_col_tae_pub_vuhk.get_assignments_post
262 (p_api_version => p_api_version,
263 p_init_msg_list => p_init_msg_list,
264 p_commit => p_commit,
265 p_validation_level => p_validation_level,
266 x_return_status => x_return_status,
267 x_msg_count => x_msg_count,
268 x_msg_data => x_msg_data);
269
270 IF x_return_status = fnd_api.g_ret_sts_error THEN
271 RAISE fnd_api.g_exc_error;
272 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
273 RAISE fnd_api.g_exc_unexpected_error;
274 END IF;
275 END IF;
276
277 -- customer post processing section
278 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
279 'GET_ASSIGNMENTS',
280 'A',
281 'C')
282 THEN
283 cn_post_col_tae_pub_vuhk.get_assignments_post
284 (p_api_version => p_api_version,
285 p_init_msg_list => p_init_msg_list,
286 p_commit => p_commit,
287 p_validation_level => p_validation_level,
288 x_return_status => x_return_status,
289 x_msg_count => x_msg_count,
290 x_msg_data => x_msg_data);
291
292 IF x_return_status = fnd_api.g_ret_sts_error THEN
293 RAISE fnd_api.g_exc_error;
294 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
295 RAISE fnd_api.g_exc_unexpected_error;
296 END IF;
297 END IF;
298 -- SK End of post processing hooks
299
300 -- Standard check of p_commit.
301 IF FND_API.To_Boolean( p_commit )
302 THEN
303 COMMIT WORK;
304 END IF;
305
306 -- Standard call to get message count and if count is 1, get message info.
307 FND_MSG_PUB.Count_And_Get
308 (p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311 EXCEPTION
312 WHEN FND_API.G_EXC_ERROR THEN
313 ROLLBACK TO get_assignments;
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 x_msg_count := l_msg_count;
316 x_msg_data := l_msg_data;
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 ROLLBACK TO get_assignments;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 FND_MSG_PUB.Count_And_Get
321 (p_count => x_msg_count,
322 p_data => x_msg_data,
323 p_encoded => fnd_api.g_false
324 );
325 WHEN OTHERS THEN
326 ROLLBACK TO get_assignments;
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
328 IF FND_MSG_PUB.Check_Msg_Level
329 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
330 THEN
331 FND_MSG_PUB.Add_Exc_Msg
332 (G_PKG_NAME, l_api_name);
333 END IF;
334 FND_MSG_PUB.Count_And_Get
335 (p_count => x_msg_count,
336 p_data => x_msg_data,
337 p_encoded => fnd_api.g_false
338 );
339 END get_assignments;
340
341 END CN_POST_COLLECTION_TAE_PUB;