[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_NAMEACC_PUB
Source
1 Package Body JTF_TERR_NAMEACC_PUB AS
2 /* $Header: jtftrnpb.pls 120.6 2006/04/21 13:15:17 spai ship $ */
3 -- ***************************************************
4 -- GLOBAL VARIABLES
5 -- ***************************************************
6 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_NAMEACC_PUB';
7 G_FILE_NAME CONSTANT VARCHAR2(12):='jtftrnpb.pls';
8 G_NEW_LINE VARCHAR2(02) := FND_GLOBAL.Local_Chr(10);
9 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
10 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
11 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
12 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
13 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
14 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
15 ---------------------------------------------------------
16 -- Start of Comments
17 -- ---------------------------------------------------
18 -- PACKAGE NAME: JTF_TERR_NAMEACC_PUB
19 -- ---------------------------------------------------
20 -- PURPOSE
21 -- This package is a public API for getting winning territory
22 -- resources.
23 --
24 -- Procedures:
25 -- (see below for specification)
26 --
27 -- NOTES
28 --
29 -- HISTORY
30 -- 08/01/00 ARPATEL Created
31 -- 01/07/04 SGKUMAR changed the code to get parent territory from
32 -- JTF_TERR_ALL to JTF_TERR
33 -- End of Comments
34 procedure Set_Winners_tbl
35 ( p_api_version_number IN number,
36 p_init_msg_list IN varchar2 := fnd_api.g_false,
37 p_party_id IN number ,
38 p_party_site_id IN number ,
39 p_asof_date IN date,
40 p_source_id IN number,
41 p_trans_id IN number,
42 p_Resource_Type IN varchar2,
43 p_Role IN varchar2,
44 p_api_mode IN varchar2,
45 x_party_name OUT NOCOPY varchar2,
46 x_session_id OUT NOCOPY number,
47 x_return_status OUT NOCOPY varchar2,
48 x_msg_count OUT NOCOPY number,
49 x_msg_data OUT NOCOPY varchar2
50 )
51 AS
52 l_Terr_Id NUMBER := 0;
53 lP_Init_Msg_List VARCHAR2(2000);
54 lP_resource_type VARCHAR2(60) := NULL;
55 lP_role VARCHAR2(60) := NULL;
56 lX_Return_Status VARCHAR2(1);
57 lX_Msg_Count NUMBER;
58 lX_Msg_Data VARCHAR2(2000);
59 lp_trans_Rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
60 -- JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
61 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type ;
62 -- JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
63 l_api_name CONSTANT VARCHAR2(30) := 'Set_Winners_tbl';
64 l_api_version_number CONSTANT NUMBER := 1.0;
65 l_return_status VARCHAR2(1);
66 l_Counter NUMBER := 0;
67 l_RscCounter NUMBER := 0;
68 l_NumberOfWinners NUMBER ;
69 l_RetCode BOOLEAN;
70 dummy1 VARCHAR2(30);
71 l_state VARCHAR2(60);
72 l_terr_group_name VARCHAR(240) := 'Test';
73 l_role_name VARCHAR2(240);
74 l_num_res_rows NUMBER := 0;
75 BEGIN
76 -- New logging guidelines
77 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
78 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
79 'jtf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl.begin',
80 'Start of the procedure JTF_TERR_NAMEACC_PUB.Set_Winners_tbl');
81 END IF;
82 FND_MSG_PUB.initialize;
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
85 p_api_version_number,
86 l_api_name,
87 G_PKG_NAME)
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91 -- Initialize message list if p_init_msg_list is set to TRUE.
92 IF FND_API.to_Boolean( p_init_msg_list )
93 THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96 -- Debug Message
97 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
98 THEN
99 FND_MESSAGE.Set_Name('JTF', G_PKG_NAME || '_START');
100 FND_MSG_PUB.Add;
101 END IF;
102 -- API body
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 lx_return_status := FND_API.G_RET_STS_SUCCESS;
105 lx_msg_data := null;
106
107 -- Code for party name here...
108 if p_party_id is not null
109 then
110 Select distinct party_name
111 into x_party_name
112 from HZ_PARTIES
113 where party_id = p_party_id;
114 end if;
115 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
117 'jtf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl.begin',
118 'Initializing Input and Output records');
119 END IF;
120 -- Assign input parameters to lp_trans_Rec
121 lp_trans_rec.trans_object_id1 := jtf_terr_number_list(p_party_id);
122 IF p_party_site_id is not null
123 THEN
124 lp_trans_rec.trans_object_id2 := jtf_terr_number_list(p_party_site_id);
125 ELSE
126 lp_trans_rec.trans_object_id2 := jtf_terr_number_list(null);
127 END If;
128 lp_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
129 lp_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
130 lp_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
131 IF p_asof_date is null
132 THEN
133 lp_trans_rec.txn_date := jtf_terr_date_list(null);
134 ELSE
135 lp_trans_rec.txn_date := jtf_terr_date_list(p_asof_date);
136 END IF;
137 --dbms_output.put_line('Resetting global vars ');
138 --Reset the global variables
139 l_RetCode := JTF_TERRITORY_GLOBAL_PUB.Reset;
140 IF p_api_mode = 'CURRENT'
141 THEN
142 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
144 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners',
145 'Calling procedure JTY_ASSIGN_REALTIME_PUB.get_winners in RealTime Mode');
146 END IF;
147 JTY_ASSIGN_REALTIME_PUB.get_winners(
148 P_api_version_number => 1.0,
149 P_init_msg_list => FND_API.G_FALSE,
150 P_source_id => -1001,
151 P_trans_id => -1002,
152 P_mode => 'REAL TIME:LOOKUP',
153 P_param_passing_mechanism => 'PBR',
154 P_program_name => 'SALES/ACCOUNT PROGRAM',
155 P_trans_rec => lp_trans_rec,
156 P_name_value_pair => null,
157 P_resource_type => null,
158 P_role => null,
159 X_return_status => lx_return_status,
160 X_msg_count => lx_msg_count,
161 X_msg_data => lx_msg_data,
162 X_winners_rec => lx_winners_rec);
163 ELSE
164 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
165 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
166 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners',
167 'Calling procedure JTY_ASSIGN_REALTIME_PUB.get_winners in Date EFfective Mode');
168 END IF;
169 JTY_ASSIGN_REALTIME_PUB.get_winners(
170 P_api_version_number => 1.0,
171 P_init_msg_list => FND_API.G_FALSE,
172 P_source_id => -1001,
173 P_trans_id => -1002,
174 P_mode => 'DATE EFFECTIVE:LOOKUP',
175 P_param_passing_mechanism => 'PBR',
176 P_program_name => 'SALES/ACCOUNT PROGRAM',
177 P_trans_rec => lp_trans_rec,
178 P_name_value_pair => NULL,
179 P_resource_type => NULL,
180 P_role => NULL,
181 X_return_status => lx_return_status,
182 X_msg_count => lx_msg_count,
183 X_msg_data => lx_msg_data,
184 X_winners_rec => lx_winners_rec);
185 END IF;
186 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
187 -- debug message
188 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
189 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
190 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners',
191 'JTY_ASSIGN_REALTIME_PUB.get_winners API has failed');
192 END IF;
193 RAISE FND_API.G_EXC_ERROR;
194 END IF;
195 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
196 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
197 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners',
198 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.get_winners');
199 END IF;
200 /*
201 JTF_TERR_ASSIGN_PUB.get_winners
202 ( p_api_version_number => p_api_version_number,
203 p_init_msg_list => p_init_msg_list,
204 p_use_type => 'LOOKUP',
205 p_source_id => -1001, -- -1001 Oracle Sales
206 p_trans_id => -1002, -- -1002 Account
207 p_trans_rec => lp_trans_Rec,
208 p_resource_type => FND_API.G_MISS_CHAR,
209 p_role => FND_API.G_MISS_CHAR,
210 p_top_level_terr_id => FND_API.G_MISS_NUM,
211 p_num_winners => FND_API.G_MISS_NUM,
212 x_return_status => lx_return_status,
213 x_msg_count => lx_msg_count,
214 x_msg_data => lx_msg_data,
215 x_winners_rec => lx_winners_rec
216 );
217 */
218 IF (( lx_winners_rec.terr_id.FIRST is not null) OR (TRUNC(lx_winners_rec.terr_id.FIRST)<>'' ))
219 THEN
220 BEGIN
221 SELECT jtf_terr_results_s.nextval into x_session_id FROM sys.dual;
222 FOR i in lx_winners_rec.terr_id.FIRST..lx_winners_rec.terr_id.LAST
223 LOOP
224 -- add processing to find the territory group name
225 -- assumption that territory group is the parent territory of the winning territory
226 /*
227 SELECT TA.NAME
228 INTO l_terr_group_name
229 FROM JTF_TERR TA,
230 JTF_TERR TA2
231 WHERE
232 TA.TERR_ID = TA2.PARENT_TERRITORY_ID
233 AND TA2.TERR_ID = lx_winners_rec.terr_id(i);
234 */
235 -- Added processing to show role_name 01/28/03
236 if lx_winners_rec.role(i) is not null
237 then
238 SELECT ROLE_NAME
239 INTO l_role_name
240 FROM JTF_RS_ROLES_VL
241 WHERE ROLE_CODE = lx_winners_rec.role(i);
242 end if;
243 l_num_res_rows := l_num_res_rows + 1;
244 --Insert into temporary table here
245 INSERT INTO JTF_TAE_RPT_STAGING_OUT(
246 TRANS_OBJECT_ID,
247 TRANS_DETAIL_OBJECT_ID,
248 TRANS_OBJECT_TYPE_ID,
249 SOURCE_ID,
250 SESSION_ID,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY,
253 CREATED_BY,
254 CREATION_DATE,
255 LAST_UPDATE_LOGIN,
256 TERR_ID,
257 TERR_RANK,
258 RESOURCE_ID,
259 RESOURCE_TYPE,
260 GROUP_ID,
261 ROLE,
262 RESOURCE_NAME,
263 AT_CHAR01,
264 AT_CHAR02,
265 AT_CHAR03,
266 AT_CHAR04,
267 AT_CHAR05,
268 AT_CHAR06,
269 AT_CHAR07
270 ) VALUES (
271 lx_winners_rec.trans_object_id(i),
272 nvl(lx_winners_rec.trans_detail_object_id(i), -1),
273 -1,
274 -1, --p_source_id,
275 x_session_id,
276 SYSDATE,
277 -1,
278 -1,
279 SYSDATE,
280 -1,
281 lx_winners_rec.terr_id(i),
282 lx_winners_rec.absolute_rank(i),
283 lx_winners_rec.resource_id(i),
284 lx_winners_rec.resource_type(i),
285 lx_winners_rec.group_id(i),
286 lx_winners_rec.role(i),
287 lx_winners_rec.resource_name(i),
288 lx_winners_rec.resource_job_title(i),
289 lx_winners_rec.resource_phone(i),
290 lx_winners_rec.resource_email(i),
291 lx_winners_rec.resource_mgr_name(i),
292 lx_winners_rec.resource_mgr_phone(i),
293 l_role_name,
294 lx_winners_rec.resource_mgr_email(i)
295 );
296 end loop;
297 END;
298 END IF;
299 COMMIT;
300 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
301 FND_LOG.string(FND_LOG.LEVEL_EVENT,
302 'jtf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl',
303 'Number of winning resources : ' || l_num_res_rows);
304 END IF;
305 -- Debug Message
306 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
307 THEN
308 FND_MESSAGE.Set_Name('JTF', G_PKG_NAME || '_END');
309 FND_MSG_PUB.Add;
310 END IF;
311 -- Standard call to get message count and if count is 1, get message info.
312 FND_MSG_PUB.Count_And_Get
313 ( p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: End ');
317 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
318 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
319 'jtf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl',
320 'End of the procedure tf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl');
321 END IF;
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN NULL;
324 WHEN OTHERS THEN
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
326
327 IF ( lx_msg_data is null )
328 THEN
329 x_msg_data := SQLCODE || ' : ' || SQLERRM;
330 x_msg_count := 1;
331 ELSE
332 x_msg_data := lx_msg_data;
333 x_msg_count := lx_msg_count;
334
335 END IF;
336
337 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
338 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
339 'jtf.plsql.JTF_TERR_NAMEACC_PUB.Set_Winners_tbl.OTHERS',
340 substr(x_msg_data, 1, 4000));
341 END IF;
342 End Set_Winners_tbl;
343 END JTF_TERR_NAMEACC_PUB;
344