[Home] [Help]
PACKAGE BODY: APPS.CN_POST_COLLECTION_TAE_PUB
Source
4 --$Header: cnppcolb.pls 120.9.12010000.2 2009/01/29 09:28:21 gmarwah ship $
1 PACKAGE BODY CN_POST_COLLECTION_TAE_PUB AS
2 --$Header: cnppcolb.pls 120.9.12010000.2 2009/01/29 09:28:21 gmarwah ship $
3
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,
52 x_org_id IN NUMBER
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
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 '||
179 --(adjust_status IS NULL OR
180 ' adjust_status <> ''REVERSAL'' ' ||
181 ' AND (adjust_comments IS NULL OR adjust_comments <> ''Created by TAE'') ';
182 END IF;
183
184 fnd_file.put_line(fnd_file.Log, 'Start: collect trans data<<');
185 -- Begin --
186 jty_assign_bulk_pub.collect_trans_data
187 ( p_api_version_number => 1.0,
188 p_init_msg_list => FND_API.G_FALSE,
189 p_source_id => -1001,
190 p_trans_id => -1002,
191 p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM',
192 p_mode => 'DATE EFFECTIVE',
193 p_where => l_where_clause,
194 p_no_of_workers => 1,
195 p_percent_analyzed => 20, -- this value can be either a profile option or a parameter to conc program
199 x_msg_data => x_msg_data,
196 p_request_id => -1, -- request id of the concurrent program
197 x_return_status => x_return_status,
198 x_msg_count => x_msg_count,
200 ERRBUF => errbuf,
201 RETCODE => retcode
202 );
203 fnd_file.put_line(fnd_file.Log, 'End: jty_assign_bulk_pub.collect_trans_data trans data');
204 IF (retcode = 0) THEN
205 fnd_file.put_line(fnd_file.Log, 'Start: get winners<<');
206 jty_assign_bulk_pub.get_winners
207 ( p_api_version_number => 1.0,
208 p_init_msg_list => FND_API.G_FALSE,
209 p_source_id => -1001,
210 p_trans_id => -1002,
211 p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM',
212 p_mode => 'DATE EFFECTIVE',
213 p_percent_analyzed => 20, -- this value can be either a profile option or a parameter to conc program
214 p_worker_id => 1,
215 x_return_status => x_return_status,
216 x_msg_count => x_msg_count,
217 x_msg_data => x_msg_data,
218 ERRBUF => errbuf,
219 RETCODE => retcode
220 );
221 fnd_file.put_line(fnd_file.Log, 'End: get winners<<');
222 END IF;
223 IF retcode <> 0 THEN
224 RAISE fnd_api.g_exc_error;
225 END IF;
226
227 IF retcode=0 THEN
228 fnd_file.put_line(fnd_file.Log, 'Start: Cn : Process trx records<<');
229 CN_PROCESS_TAE_TRX_PUB.Process_Trx_Records(
230
231 p_api_version => p_api_version,
232 p_init_msg_list => p_init_msg_list,
233 p_commit => p_commit,
234 p_validation_level => p_validation_level,
235
236 x_return_status => l_return_status,
237 x_msg_count => l_msg_count,
238 x_msg_data => l_msg_data,
239 p_org_id => l_org_id);
240 fnd_file.put_line(fnd_file.Log, 'End: Cn : Process trx records<<');
241 END IF;
242
243 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
244 RAISE FND_API.G_EXC_ERROR ;
245 END IF;
246
247 -- +
248 -- End of API body.
249 --+
250
251 --+
252 -- Post processing hooks
253 --+
254
255 -- SK Start of post processing hooks
256
257 -- vertical post processing section
258 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
259 'GET_ASSIGNMENTS',
260 'A',
261 'V')
262 THEN
263 cn_post_col_tae_pub_vuhk.get_assignments_post
264 (p_api_version => p_api_version,
265 p_init_msg_list => p_init_msg_list,
266 p_commit => p_commit,
267 p_validation_level => p_validation_level,
268 x_return_status => x_return_status,
269 x_msg_count => x_msg_count,
270 x_msg_data => x_msg_data);
271
272 IF x_return_status = fnd_api.g_ret_sts_error THEN
273 RAISE fnd_api.g_exc_error;
274 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
275 RAISE fnd_api.g_exc_unexpected_error;
276 END IF;
277 END IF;
278
279 -- customer post processing section
280 IF JTF_USR_HKS.Ok_to_Execute('CN_POST_COLLECTION_TAE_PUB',
281 'GET_ASSIGNMENTS',
282 'A',
283 'C')
284 THEN
285 cn_post_col_tae_pub_vuhk.get_assignments_post
286 (p_api_version => p_api_version,
287 p_init_msg_list => p_init_msg_list,
288 p_commit => p_commit,
289 p_validation_level => p_validation_level,
290 x_return_status => x_return_status,
291 x_msg_count => x_msg_count,
292 x_msg_data => x_msg_data);
293
294 IF x_return_status = fnd_api.g_ret_sts_error THEN
295 RAISE fnd_api.g_exc_error;
296 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
297 RAISE fnd_api.g_exc_unexpected_error;
298 END IF;
299 END IF;
300 -- SK End of post processing hooks
301
302 -- Standard check of p_commit.
303 IF FND_API.To_Boolean( p_commit )
304 THEN
305 COMMIT WORK;
306 END IF;
307
308 -- Standard call to get message count and if count is 1, get message info.
309 FND_MSG_PUB.Count_And_Get
310 (p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 ROLLBACK TO get_assignments;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 x_msg_count := l_msg_count;
318 x_msg_data := l_msg_data;
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 ROLLBACK TO get_assignments;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
322 FND_MSG_PUB.Count_And_Get
323 (p_count => x_msg_count,
324 p_data => x_msg_data,
325 p_encoded => fnd_api.g_false
326 );
327 WHEN OTHERS THEN
328 ROLLBACK TO get_assignments;
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
330 IF FND_MSG_PUB.Check_Msg_Level
331 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
332 THEN
333 FND_MSG_PUB.Add_Exc_Msg
334 (G_PKG_NAME, l_api_name);
335 END IF;
336 FND_MSG_PUB.Count_And_Get
337 (p_count => x_msg_count,
338 p_data => x_msg_data,
339 p_encoded => fnd_api.g_false
340 );
341 END get_assignments;
342
343 END CN_POST_COLLECTION_TAE_PUB;