[Home] [Help]
PACKAGE BODY: APPS.IBU_SR_UTIL
Source
1 Package body IBU_SR_UTIL AS
2 /* $Header: ibusrusb.pls 120.14 2009/02/17 12:22:30 mkundali ship $ */
3 /*============================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | History |
9 | May-30-2002 klou, Initial |
10 | This package contains all utility functions for SR |
11 | module. |
12 | 115.2 03-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY hint |
13 | to enable pass by reference. |
14 | 120.14 13-FEB-2009 mkundali added for 12.1.2 enhancement bug8245975 |
15 +============================================================================*/
16
17
18 g_file_name VARCHAR2(32) := 'ibusrusb.pls';
19
20 /*
21 * Procedure: check_nlslength
22 * This procedure takes an array of string, and an array of the max byte that
23 * is corresponding to the array of string. It checks the max byte with length().
24 *
25 */
26 PROCEDURE check_nlslength
27 ( p_string IN JTF_VARCHAR2_TABLE_2000 := NULL,
28 p_max_byte IN JTF_NUMBER_TABLE := NULL,
29 x_out_array OUT NOCOPY JTF_VARCHAR2_TABLE_100,
30 x_return_status OUT NOCOPY VARCHAR2
31 )
32 IS
33 l_length NUMBER := 0;
34 l_out_array G_VARCHAR_100_TYPE;
35
36 BEGIN
37
38 For i in p_string.FIRST..p_string.LAST Loop
39 l_out_array(i) := 'S';
40 l_length := 0;
41 If p_string(i) Is Not Null Then
42 select lengthb(p_string(i)) into l_length from dual;
43
44 If l_length > p_max_byte(i) Then
45 l_out_array(i) := 'F';
46 End If;
47 End If;
48 End Loop;
49 copy_out_array(l_out_array, x_out_array);
50 x_return_status := 'S';
51 EXCEPTION
52 WHEN OTHERS THEN
53 -- x_return_status := sqlerrm;
54 x_return_status := 'F';
55 END check_nlslength;
56
57
58 PROCEDURE copy_out_array(
59 t IN G_VARCHAR_100_TYPE,
60 a0 OUT NOCOPY JTF_VARCHAR2_TABLE_100
61 ) as
62
63 ddindx binary_integer;
64 indx binary_integer;
65 BEGIN
66 if t is null or t.count = 0 then
67 a0 := JTF_VARCHAR2_TABLE_100();
68 else
69 a0 := JTF_VARCHAR2_TABLE_100();
70 if t.count > 0 then
71
72 a0.extend(t.count);
73 ddindx := t.first;
74 indx := 1;
75 while true loop
76 a0(indx) := t(ddindx);
77 indx := indx+1;
78 if t.last =ddindx then
79 exit;
80 end if;
81 ddindx := t.next(ddindx);
82 end loop;
83 end if;
84 end if;
85
86 END copy_out_array;
87
88 -- added the below procedures for isupport porject----------------------------------
89 PROCEDURE call_create_org_contact( p_user_id IN NUMBER,
90 p_Subject_Id IN NUMBER,
91 p_Object_Id IN SYSTEM.ibu_num_tbl_type,
92 p_Subject_Table_name IN VARCHAR2,
93 p_Object_Table_name IN VARCHAR2,
94 p_Subject_Type IN VARCHAR2,
95 p_Object_Type IN VARCHAR2,
96 p_Relationship_Code IN VARCHAR2,
97 p_Relationship_Type IN VARCHAR2,
98 p_Start_Date IN DATE,
99 p_End_Date IN DATE,
100 p_Status IN VARCHAR2,
101 p_created_by_module IN VARCHAR2,
102 p_application_id IN NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2
106 )
107 AS
108
109 l_org_contact_rec_v2 HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type := CSC_RESPONSE_CENTER_PKG_V2.GET_ORG_CONTACT_REC_TYPE;
110 l_party_rel_rec_v2 HZ_RELATIONSHIP_V2PUB.relationship_rec_type := CSC_RESPONSE_CENTER_PKG_V2.GET_RELATIONSHIP_REC_TYPE;
111 l_party_rec_v2 HZ_PARTY_V2PUB.PARTY_REC_TYPE := CSC_RESPONSE_CENTER_PKG_V2.GET_PARTY_REC_TYPE;
112 l_true VARCHAR2(5) := CSC_CORE_UTILS_PVT.G_TRUE;
113 l_org_contact_id NUMBER;
114 l_party_id NUMBER;
115 l_party_id2 NUMBER;
116 l_party_number VARCHAR2(30);
117 l_Party_Relationship_Id NUMBER;
118 l_return_status VARCHAR2(10) ;
119 x_status VARCHAR2(10) ;
120 x_status1 VARCHAR2(10) ;
121 x_party_id NUMBER;
122 x_party_id1 NUMBER;
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(2000);
125 x_insert_status VARCHAR2(10) ;
126 i NUMBER;
127 l_user_id_type SYSTEM.IBU_NUM_TBL_TYPE := SYSTEM.IBU_NUM_TBL_TYPE();
128 l_party_id_type SYSTEM.IBU_NUM_TBL_TYPE := SYSTEM.IBU_NUM_TBL_TYPE();
129 l_flag_type SYSTEM.IBU_VAR_3_TBL_TYPE := SYSTEM.IBU_VAR_3_TBL_TYPE();
130 l_operation_type SYSTEM.IBU_VAR_3_TBL_TYPE := SYSTEM.IBU_VAR_3_TBL_TYPE();
131 --x_email_status VARCHAR2(10);
132 BEGIN
133
134 FOR i IN p_Object_Id.FIRST .. p_Object_Id.LAST LOOP
135 l_user_id_type.EXTEND;
136 l_party_id_type.EXTEND;
137 l_flag_type.EXTEND;
138 l_operation_type.EXTEND;
139
140 check_relationship(p_Subject_Id,
141 p_Object_Id(i),
142 x_party_id1,
143 x_status);
144
145 IF x_status = 'E' THEN
146
147 l_Party_Rel_Rec_v2.Subject_Id := p_Subject_Id;
148 l_Party_Rel_Rec_v2.Object_Id := p_Object_Id(i);
149 l_Party_Rel_Rec_v2.Subject_Table_name := p_Subject_Table_name;
150 l_Party_Rel_Rec_v2.Object_Table_name := p_Object_Table_name;
151 l_Party_Rel_Rec_v2.Subject_Type := p_Subject_Type;
152 l_Party_Rel_Rec_v2.Object_Type := p_Object_Type;
153 l_Party_Rel_Rec_v2.Relationship_Code := p_Relationship_Code;
154 l_Party_Rel_Rec_v2.Relationship_Type := p_Relationship_Type;
155 l_Party_Rel_Rec_v2.Start_Date := p_Start_Date;
156 l_Party_Rel_Rec_v2.End_Date := p_End_Date;
157 l_Party_Rel_Rec_v2.Status := p_Status;
158 l_Party_Rel_Rec_v2.created_by_module := p_created_by_module;
159 l_Party_Rel_Rec_v2.application_id := p_application_id;
160 l_org_contact_rec_v2.party_rel_rec := l_party_rel_rec_v2;
161 l_org_contact_rec_v2.created_by_module := p_created_by_module;
162 l_org_contact_rec_v2.application_id := p_application_id;
163
164 HZ_PARTY_CONTACT_V2PUB.create_org_contact(
165 p_init_msg_list => l_true,
166 p_org_contact_rec => l_org_contact_rec_V2,
167 x_org_contact_id => l_org_contact_id,
168 x_party_rel_id => l_party_relationship_id,
169 x_party_id => l_party_id,
170 x_party_number => l_party_number,
171 x_return_status => l_return_status,
172 x_msg_count => l_msg_count,
173 x_msg_data => l_msg_data
174 );
175
176 x_return_status := l_return_status;
177 x_msg_count := l_msg_count;
178 x_msg_data := l_msg_data;
179 IF x_return_status = 'S' THEN
180
181 l_user_id_type(i) := p_user_id;
182 l_party_id_type(i) := x_party_id1;
183 -- l_flag_type(i) := 'Y';
184 l_operation_type(i) := 'A';
185
186 create_ibu_multi_parties (p_user_id,
187 l_party_id,
188 'Y',
189 'N',
190 NULL,
191 p_user_id,
192 SYSDATE,
193 p_user_id,
194 SYSDATE,
195 p_user_id,
196 x_insert_status
197 );
198
199 END IF;
200
201 ELSE
202 x_return_status := 'S';
203 l_user_id_type(i) := p_user_id;
204 l_party_id_type(i) := x_party_id1;
205 l_flag_type(i) := 'Y';
206 l_operation_type(i) := 'A';
207
208 create_ibu_multi_parties (p_user_id ,
209 x_party_id1 ,
210 'Y',
211 'N',
212 NULL,
213 p_user_id,
214 SYSDATE,
215 p_user_id,
216 SYSDATE,
217 p_user_id,
218 x_insert_status
219 );
220
221
222
223 END IF;
224
225 END LOOP;
226 IBU_MULTIPARTY_PUB.send_email_notification(l_user_id_type,
227 l_party_id_type,
228 l_operation_type
229 );
230
231 EXCEPTION
232 WHEN OTHERS THEN
233
234 x_return_status:= 'E';
235 x_insert_status := 'E';
236
237 END call_create_org_contact;
238
239 PROCEDURE check_relationship(p_Subject_Id IN NUMBER,
240 p_Object_Id IN NUMBER,
241 x_party_id OUT NOCOPY NUMBER,
242 x_status OUT NOCOPY VARCHAR2
243 )
244
245 AS
246
247 BEGIN
248 SELECT /*+ index(HZ_RELATIONSHIPS_N2) */ party_id
249 INTO x_party_id
250 FROM hz_relationships
251 WHERE object_id = p_Object_Id
252 AND object_type='ORGANIZATION'
253 AND subject_id = p_Subject_Id
254 AND subject_type ='PERSON'
255 AND relationship_code ='CONTACT_OF';
256
257 x_status := 'S';
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 x_status := 'E';
262 x_party_id := 0;
263 WHEN OTHERS THEN
264 raise;
265 END check_relationship;
266
267
268
269 PROCEDURE create_ibu_multi_parties(p_user_id IN NUMBER,
270 p_party_id IN NUMBER,
271 p_enable_flag IN VARCHAR2,
272 p_current_party IN VARCHAR2,
273 p_end_date IN DATE,
274 p_created_by IN NUMBER,
275 p_creation_date IN DATE,
276 p_last_updated_by IN NUMBER,
277 p_last_update_date IN DATE,
278 p_last_update_login IN NUMBER,
279 x_insert_status OUT NOCOPY VARCHAR2
280 )
281
282 AS
283
284 BEGIN
285
286 INSERT INTO ibu_multi_parties(USER_ID,
287 PARTY_ID,
288 ENABLE_FLAG,
289 CURRENT_PARTY,
290 END_DATE,
291 CREATED_BY,
292 CREATION_DATE,
293 LAST_UPDATED_BY,
294 LAST_UPDATE_DATE,
295 LAST_UPDATE_LOGIN)
296 VALUES (p_user_id,
297 p_party_id,
298 p_enable_flag,
299 p_current_party,
300 p_end_date,
301 p_created_by,
302 p_creation_date,
303 p_last_updated_by,
304 p_last_update_date,
305 p_last_update_login);
306
307 x_insert_status := 'S';
308
309 EXCEPTION
310 WHEN others THEN
311 x_insert_status := 'E';
312 END create_ibu_multi_parties;
313
314
315 PROCEDURE update_ibu_multi_parties(p_user_id IN NUMBER,
316 p_loggedin_user_id IN NUMBER,
317 p_party_id IN SYSTEM.IBU_NUM_TBL_TYPE,
318 p_enable_flag IN SYSTEM.IBU_VAR_3_TBL_TYPE,
319 x_update_status OUT NOCOPY VARCHAR2
320 )
321 AS
322 i NUMBER;
323 j NUMBER;
324 l_party_id1 NUMBER;
325 l_flag VARCHAR2(1);
326 l_temp VARCHAR2(1);
327 BEGIN
328 FOR i IN p_party_id.FIRST .. p_party_id.LAST LOOP
329 select ENABLE_FLAG into l_temp from ibu_multi_parties where party_id=p_party_id(i) and user_id=p_user_id;
330 l_party_id1 := p_party_id(i);
331 l_flag := p_enable_flag(i);
332 IF l_temp <> l_flag THEN
333 UPDATE ibu_multi_parties
334 SET ENABLE_FLAG = l_flag,last_update_date = SYSDATE,last_updated_by = p_loggedin_user_id,last_update_login = p_loggedin_user_id
335 WHERE USER_ID = p_user_id
336 AND PARTY_ID = l_party_id1;
337 END IF;
338 END LOOP;
339 x_update_status := 'S';
340 EXCEPTION
341 WHEN OTHERS THEN
342 x_update_status := 'E';
343
344 END update_ibu_multi_parties;
345
346 /*added for 12.1.2 enhancement bug8245975*/
347 function return_primary(party_site_id IN NUMBER) return varchar2 is
348 l_phone varchar2(100);
349 CURSOR C1 IS SELECT phone_number FROM hz_contact_points where owner_table_id = party_site_id and owner_table_name = 'HZ_PARTY_SITES' and contact_point_type = 'PHONE' order by primary_flag desc,creation_date asc;
350 BEGIN
351 FOR R IN C1
352 LOOP
353 l_phone:= R.phone_number;
354 if (l_phone is NOT NULL ) THEN
355 return(l_phone);
356 end if;
357 END LOOP;
358 return(l_phone);
359 end;
360
361
362
363
364 -----------------------------------------------------------------------------------
365 END IBU_SR_UTIL; -- Package Specification IBU_SR_UTIL