[Home] [Help]
PACKAGE BODY: APPS.IEX_PARTY_PREF_PUB
Source
1 PACKAGE BODY IEX_PARTY_PREF_PUB AS
2 /* $Header: iexphppb.pls 120.0.12010000.9 2009/08/12 15:05:10 ehuh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_PARTY_PREF_PUB';
5 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
6
7 PROCEDURE assign_collection_level
8 (
9 P_Api_Version_Number IN NUMBER DEFAULT 1.0,
10 P_Init_Msg_List IN VARCHAR2 DEFAULT NULL,
11 P_Commit IN VARCHAR2 DEFAULT NULL,
12 X_Return_Status OUT NOCOPY VARCHAR2,
13 X_Msg_Count OUT NOCOPY NUMBER,
14 X_Msg_Data OUT NOCOPY VARCHAR2,
15 X_Insert_Count OUT NOCOPY NUMBER,
16 X_Update_Count OUT NOCOPY NUMBER,
17 p_level_tbl IN level_tbl_type) AS
18
19 l_result VARCHAR2(10);
20 l_error_msg VARCHAR2(2000);
21 l_return_status VARCHAR2(20);
22 l_msg_count NUMBER;
23 l_msg_data VARCHAR2(2000);
24 l_api_name VARCHAR2(100) := 'assign_collection_level' ;
25 l_api_version_number CONSTANT NUMBER :=1.0;
26 l_object_version_number NUMBER;
27 l_store_acct NUMBER :=0;
28 l_store_billto NUMBER :=0;
29 l_store_party NUMBER :=0;
30 l_pref_id NUMBER :=0;
31 l_party_preference_id NUMBER;
32 j NUMBER;
33 k NUMBER;
34 m NUMBER;
35
36 I_level_tbl level_tbl_type;
37 U_level_tbl ulevel_tbl;
38 --R_level_tbl ulevel_tbl;
39
40 type U_level_party_id_t is table of hz_party_preferences.party_id%type index by binary_integer;
41 type U_level_objectcode_t is table of hz_party_preferences.value_varchar2%type index by binary_integer;
42 type U_level_version_t is table of hz_party_preferences.object_version_number%type index by binary_integer;
43 type U_level_pref_id_t is table of hz_party_preferences.party_preference_id%type index by binary_integer;
44
45 U_level_partyids U_level_party_id_t;
46 U_level_objectcodes U_level_objectcode_t;
47 U_level_versions U_level_version_t;
48 R_level_prefids U_level_pref_id_t;
49
50 type I_level_party_id_t is table of hz_party_preferences.party_id%type index by binary_integer;
51 type I_level_objectcode_t is table of hz_party_preferences.value_varchar2%type index by binary_integer;
52
53 I_level_partyids I_level_party_id_t;
54 I_level_objectcodes I_level_objectcode_t;
55
56 CURSOR object_version_cur(c_party_id IN NUMBER) IS
57 SELECT party_preference_id, object_version_number FROM hz_party_preferences
58 WHERE party_id = c_party_id
59 AND category = 'COLLECTIONS LEVEL'
60 AND preference_code = 'PARTY_ID'
61 AND module = 'COLLECTIONS';
62
63
64 BEGIN
65
66 SAVEPOINT IEX_PARTY_PREF_PUB_START;
67
68 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
69 p_api_version_number,
70 l_api_name,
71 G_PKG_NAME) THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 IF FND_API.to_Boolean( p_init_msg_list ) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 x_return_status := 'S';
80
81
82 iex_debug_pub.logmessage (' IEX_PARTY_PREF_PUB.assign_collection_level - Input Count = ' ||p_level_tbl.count);
83
84 j := 0;
85 k := 0;
86 m := 0;
87
88 For i In 1..p_level_tbl.count
89 Loop
90
91 if p_level_tbl(i).ObjectCode IS NULL then
92 begin
93 Open object_version_cur(p_level_tbl(i).party_id);
94 Fetch object_version_cur INTO l_pref_id,l_object_version_number;
95 if object_version_cur%NOTFOUND then
96 l_object_version_number := 1;
97 l_pref_id := 0;
98 end if;
99 Close object_version_cur;
100 exception
101 when others then
102 l_pref_id := 0;
103 l_object_version_number := 1;
104 end;
105 if l_pref_id > 0 then
106 m := m + 1;
107 R_level_prefids(m) := l_pref_id;
108 end if;
109 end if;
110
111 if p_level_tbl(i).ObjectCode in ('CUSTOMER','ACCOUNT','BILL_TO','DELINQUENCY') then
112
113 begin
114 Open object_version_cur(p_level_tbl(i).party_id);
115 Fetch object_version_cur INTO l_pref_id,l_object_version_number;
116 if object_version_cur%NOTFOUND then
117 l_object_version_number := 1;
118 l_pref_id := 0;
119 end if;
120 Close object_version_cur;
121 exception
122 when others then
123 l_pref_id := 0;
124 l_object_version_number := 1;
125 end;
126
127 if l_pref_id > 0 then
128 j := j + 1;
129 U_level_tbl(j).party_id := p_level_tbl(i).party_id;
130 U_level_tbl(j).ObjectCode := p_level_tbl(i).ObjectCode;
131 U_level_tbl(j).version := l_object_version_number + 1;
132 U_level_partyids(j) := p_level_tbl(i).party_id;
133 U_level_ObjectCodes(j) := p_level_tbl(i).ObjectCode;
134 U_level_versions(j) := l_object_version_number + 1;
135 else
136 k := k + 1;
137 I_level_tbl(k).party_id := p_level_tbl(i).party_id;
138 I_level_tbl(k).ObjectCode := p_level_tbl(i).ObjectCode;
139 I_level_partyids(k) := p_level_tbl(i).party_id;
140 I_level_ObjectCodes(k) := p_level_tbl(i).ObjectCode;
141 end if;
142
143 End if;
144 End Loop;
145
146 begin
147 If R_level_prefids.count > 0 then
148
149 FORALL m IN 1..R_level_prefids.count
150 DELETE from hz_party_preferences
151 where party_preference_id = R_level_prefids(m)
152 and module = 'COLLECTIONS'
153 and category = 'COLLECTIONS LEVEL'
154 and preference_code = 'PARTY_ID';
155
156 End if;
157
158 x_return_status := 'S';
159 x_update_count := R_level_prefids.count;
160
161 exception
162 when others then
163 iex_debug_pub.logmessage (' IEX_PARTY_PREF_PUB.assign_collection_level - deleting - Exception = ' ||SQLERRM);
164 x_return_status := 'E';
165 x_msg_data := SQLERRM||'Deleting Party preferences..';
166 x_update_count := 0;
167 RETURN;
168 end;
169
170 begin
171 If U_level_tbl.count > 0 then
172
173 FORALL m IN 1..U_level_tbl.count
174 UPDATE hz_party_preferences
175 set
176 --value_varchar2 = U_level_tbl(m).ObjectCode
177 --,object_version_number = U_level_tbl(m).version
178 value_varchar2 = U_level_ObjectCodes(m)
179 ,object_version_number = U_level_versions(m)
180 ,last_updated_by = G_user_id
181 ,last_update_date = sysdate
182 ,last_update_login = G_user_id
183 --where party_id = U_level_tbl(m).party_id
184 where party_id = U_level_partyids(m)
185 and module = 'COLLECTIONS'
186 and category = 'COLLECTIONS LEVEL'
187 and preference_code = 'PARTY_ID';
188
189 End if;
190
191 x_return_status := 'S';
192 x_update_count := U_level_tbl.count;
193
194 exception
195 when others then
196 iex_debug_pub.logmessage (' IEX_PARTY_PREF_PUB.assign_collection_level - Updating - Exception = ' ||SQLERRM);
197 x_return_status := 'E';
198 x_msg_data := SQLERRM||'Updating Party ';
199 x_update_count := 0;
200 RETURN;
201 end;
202
203 begin
204 If I_level_tbl.count > 0 then
205
206 FORALL n IN 1..I_level_tbl.count
207
208
209 INSERT into HZ_PARTY_PREFERENCES (
210 PARTY_PREFERENCE_ID,
211 PARTY_ID,
212 MODULE,
213 CATEGORY,
214 PREFERENCE_CODE,
215 VALUE_VARCHAR2,
216 VALUE_NUMBER,
217 VALUE_DATE,
218 VALUE_NAME,
219 ADDITIONAL_VALUE1,
220 ADDITIONAL_VALUE2,
221 ADDITIONAL_VALUE3,
222 ADDITIONAL_VALUE4,
223 ADDITIONAL_VALUE5,
224 OBJECT_VERSION_NUMBER,
225 CREATED_BY,
226 CREATION_DATE,
227 LAST_UPDATED_BY,
228 LAST_UPDATE_DATE,
229 LAST_UPDATE_LOGIN
230 )
231 VALUES (
232 hz_party_preferences_s.nextval,
233 --I_level_tbl(n).party_id,
234 I_level_partyids(n),
235 'COLLECTIONS',
236 'COLLECTIONS LEVEL',
237 'PARTY_ID',
238 --I_level_tbl(n).ObjectCode,
239 I_level_ObjectCodes(n),
240 null,
241 null,
242 null,
243 null,
244 null,
245 null,
246 null,
247 null,
248 1,
249 G_user_id,
250 sysdate,
251 G_user_id,
252 sysdate,
253 G_user_id);
254 End if;
255
256 x_return_status := 'S';
257 x_insert_count := I_level_tbl.count;
258
259 exception
260 when others then
261 iex_debug_pub.logmessage (' IEX_PARTY_PREF_PUB.assign_collection_level - Inserting - Exception = ' ||SQLERRM);
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 x_msg_data := SQLERRM||'Inserting Party ';
264 x_insert_count := 0;
265 RETURN;
266 end;
267
268 IF FND_API.to_Boolean( p_commit ) THEN
269 COMMIT WORK;
270 END IF;
271
272 END assign_collection_level;
273
274 END IEX_PARTY_PREF_PUB;