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