[Home] [Help]
PACKAGE BODY: APPS.IEX_ASSIGN_COLL_LEVEL_PVT
Source
1 PACKAGE BODY IEX_ASSIGN_COLL_LEVEL_PVT AS
2 /* $Header: iexvaclb.pls 120.0.12010000.8 2010/02/16 07:57:30 barathsr ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_ASSIGN_COLL_LEVEL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvaclb.pls';
6 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
7
8 G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
9 G_insert_count NUMBER:= 0;
10 G_update_count NUMBER:= 0;
11
12 l_api_version_number CONSTANT NUMBER := 1.0;
13 PG_DEBUG NUMBER ;
14
15
16 PROCEDURE MAIN_PROCESS(ERRBUF OUT NOCOPY Varchar2,
17 RETCODE OUT NOCOPY Varchar2,
18 p_request_id IN Number) is
19
20 Cursor C_get_CodeIds(p_code varchar2,p_id number) is
21 select distinct ish.score_object_code,ish.score_object_id from iex_score_histories ish, iex_scores sc
22 where ish.score_id = sc.score_id
23 and sc.concurrent_prog_name = 'IEXACLSB'
24 and ish.request_id = p_id
25 --(select max(sh.request_id) from iex_score_histories sh where score_object_code <> 'IEX_INVOICES')
26 and ish.score_object_code = p_code; --<> 'IEX_INVOICES';
27
28
29 Cursor C_get_PartyId_Account(id number) is
30 select party_id from hz_cust_accounts
31 where cust_account_id = id;
32
33
34 Cursor C_get_PartyId_BillTo(id number) is
35 select party_id from hz_cust_accounts where cust_account_id =
36 (select cust_account_id from hz_cust_acct_sites_all where cust_acct_site_id =
37 (select cust_acct_site_id from hz_cust_site_uses_all where site_use_code = 'BILL_TO' and site_use_id = id));
38
39 Cursor C_get_PartyId_Del(id number) is
40 select party_cust_id from iex_delinquencies
41 where delinquency_id = id;
42
43 Cursor C_get_objCode(p_id number) is
44 select shs.score_object_code from iex_score_histories shs where shs.score_history_id = (select max(score_history_id)
45 from iex_score_histories ish, iex_scores sc
46 where ish.score_id = sc.score_id
47 and sc.concurrent_prog_name = 'IEXACLSB'
48 and ish.request_id = p_id
49 and ish.score_object_code <> 'IEX_INVOICES');
50
51 cursor c_level is
52 select Meaning
53 -- into t_level
54 from iex_lookups_v
55 where lookup_type='IEX_RUNNING_LEVEL'
56 and iex_utilities.validate_running_level(lookup_code)='Y';
57
58
59 l_cnt NUMBER:= 0;
60 l_party_id NUMBER;
61 l_insert_count NUMBER:= 0;
62 l_update_count NUMBER:= 0;
63 l_return_status VARCHAR2(10);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(2000);
66 ncnt NUMBER:= 0;
67
68 subtype l_rec is IEX_PARTY_PREF_PUB.level_rec_type;
69 subtype l_tbl is IEX_PARTY_PREF_PUB.level_tbl_type;
70
71 t_level_tbl l_tbl;
72
73 type t_ids is table of number index by binary_integer;
74 type t_codes is table of varchar2(25) index by binary_integer;
75 type t_vrs is table of number index by binary_integer;
76
77 v_score_object_code t_codes;
78 v_score_object_id t_ids;
79 ti_score_object_code t_codes;
80 ti_party_id t_ids;
81 tu_score_object_code t_codes;
82 tu_party_id t_ids;
83 tu_obj_verseion t_vrs;
84 t_level t_codes;--Added for Bug 8839374 16-Feb-2010 barathsr
85
86 l_req_id number;
87 l_objCode varchar2(25);
88 --Begin Bug 8839374 16-Feb-2010 barathsr
89 l_party_level varchar2(1);
90 l_using_del_level varchar2(1);
91 l_using_billto_level varchar2(1);
92 l_using_acc_level varchar2(1);
93 l_using_cust_level varchar2(1);
94 l_return boolean;
95 --End Bug 8839374 16-Feb-2010 barathsr
96
97 BEGIN
98
99 -- Standard Start of API savepoint
100 RETCODE := 0;
101 ERRBUF := null;
102 SAVEPOINT MAIN_PROCESS_PVT;
103
104 select define_party_running_level,using_delinquency_level,using_billto_level,using_account_level,using_customer_level
105 into l_party_level,l_using_del_level,l_using_billto_level,l_using_acc_level,l_using_cust_level
106 from iex_questionnaire_items;
107
108 iex_debug_pub.logmessage (' ASSIGN COLLECTION LEVEL - Starting MAIN PROCESS .....');
109 select max(request_id) into l_req_id from iex_score_histories where score_object_code <> 'IEX_INVOICES';
110
111 --Begin Bug 8839374 16-Feb-2010 barathsr
112 if l_party_level <> 'Y' then
113 if FND_GLOBAL.Conc_Request_Id is not null then
114 l_return := fnd_concurrent.set_completion_status (status => 'WARNING',
115 message => 'Cannot continue as the Override Collections at party level is set to No');
116 end if;
117 fnd_file.put_line(FND_FILE.LOG,'Cannot continue as the Override Collections at party level is set to No....Value->'||l_party_level);
118 return;
119 end if;
120 --End Bug 8839374 16-Feb-2010 barathsr
121
122
123 open C_get_objCode(l_req_id);
124 fetch C_get_objCode into l_objCode;
125 close C_get_objCode;
126
127 OPEN C_get_CodeIds(l_objCode,l_req_id);
128
129 LOOP
130 FETCH C_get_CodeIds
131 BULK COLLECT INTO v_score_object_code, v_score_object_id LIMIT G_Batch_Size;
132
133 ncnt := v_score_object_id.count;
134
135 FOR i In 1..ncnt
136 Loop
137
138 IF v_score_object_code(i) = 'PARTY' and l_using_cust_level='Y' then
139
140 t_level_tbl(i).ObjectCode := 'CUSTOMER';
141 t_level_tbl(i).party_id := v_score_object_id(i);
142
143 ELSIF v_score_object_code(i) = 'IEX_ACCOUNT' and l_using_acc_level='Y' then
144
145 begin
146 Open C_get_PartyId_Account(v_score_object_id(i) );
147 Fetch C_get_PartyId_Account into l_party_id;
148
149 if C_get_PartyId_Account%NOTFOUND then
150 l_party_id := 0;
151 end if;
152 Close C_get_PartyId_Account;
153
154 exception
155 when others then
159 l_party_id := 0;
156 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Account- Exception = ' ||SQLERRM);
157 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Account- ObjCode = ' ||'ACCOUNT');
158 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Account- ObjId = ' ||v_score_object_id(i));
160 end;
161
162 if (l_party_id > 0) then
163
164 t_level_tbl(i).ObjectCode := 'ACCOUNT';
165 t_level_tbl(i).party_id := l_party_id;
166
167 else
168 iex_debug_pub.logmessage('ASSIGN LEVEL Account Party Does Not Exist for Account = '||v_score_object_id(i));
169 end if;
170
171 ELSIF v_score_object_code(i) = 'IEX_DELINQUENCY' and l_using_del_level='Y' then
172
173 begin
174 Open C_get_PartyId_Del(v_score_object_id(i) );
175 Fetch C_get_PartyId_Del into l_party_id;
176
177 if C_get_PartyId_Del%NOTFOUND then
178 l_party_id := 0;
179 end if;
180 Close C_get_PartyId_Del;
181
182 exception
183 when others then
184 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Del- Exception = ' ||SQLERRM);
185 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Del- ObjCode = ' ||'DELINQUENCY');
186 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_Del- ObjId = ' ||v_score_object_id(i));
187 l_party_id := 0;
188 end;
189
190 if (l_party_id > 0) then
191
192 t_level_tbl(i).ObjectCode := 'DELINQUENCY';
193 t_level_tbl(i).party_id := l_party_id;
194
195 else
196 iex_debug_pub.logmessage('ASSIGN LEVEL Delinquency Party Does Not Exist for Deliquency = '||v_score_object_id(i));
197 end if;
198
199 ELSIF v_score_object_code(i) = 'IEX_BILLTO' and l_using_billto_level='Y' then
200
201 begin
202 Open C_get_PartyId_BillTo(v_score_object_id(i) );
203 Fetch C_get_PartyId_BillTo into l_party_id;
204
205 if C_get_PartyId_BillTo%NOTFOUND then
206 l_party_id := 0;
207 end if;
208 Close C_get_PartyId_BillTo;
209
210 exception
211 when others then
212 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_BillTo-Exception = ' ||SQLERRM);
213 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_BillTo-ObjCode = ' ||'BILLTO');
214 iex_debug_pub.logmessage('ASSIGN COLLECTION LEVEL-C_get_PartyId_BillTo-ObjId = ' ||v_score_object_id(i));
215 l_party_id := 0;
216 end;
217
218 if (l_party_id > 0) then
219
220 t_level_tbl(i).ObjectCode := 'BILL_TO';
221 t_level_tbl(i).party_id := l_party_id;
222
223 else
224 iex_debug_pub.logmessage('ASSIGN LEVEL BILLTO Party Does Not Exist for Site Use ID = '||v_score_object_id(i));
225 end if;
226 --Begin Bug 8839374 16-Feb-2010 barathsr
227 ELSE
228 if FND_GLOBAL.Conc_Request_Id is not null then
229 l_return := fnd_concurrent.set_completion_status (status => 'WARNING',
230 message => 'Cannot assign at this level as it is not enabled');
231 end if;
232 fnd_file.put_line(FND_FILE.LOG,'Cannot assign at this level as it is not enabled');
233
234
235 open c_level;
236 fetch c_level bulk collect into t_level;
237 fnd_file.put_line(FND_FILE.LOG,'Enabled levels');
238 if t_level.count>0 then
239 for j in 1..t_level.count loop
240 fnd_file.put_line(FND_FILE.LOG,'Enabled level- '||j||'-'||t_level(j));
241 end loop;
242 end if;
243 return;
244 --End Bug 8839374 16-Feb-2010 barathsr
245
246 END IF;
247
248 End Loop;
249
250
251
252
253
254 if t_level_tbl.count > 0 then
255
256 IEX_PARTY_PREF_PUB.assign_collection_level(
257 P_Api_Version_Number => 1.0,
258 P_Init_Msg_List => FND_API.G_TRUE,
259 P_Commit => FND_API.G_FALSE,
260 X_Return_Status => l_return_status,
261 X_Msg_Count => l_msg_count,
262 X_Msg_Data => l_msg_data,
263 x_Insert_Count => l_insert_count,
264 x_Update_Count => l_update_count,
265 p_level_tbl => t_level_tbl);
266 end if;
267
268 if (l_insert_count > 0) or (l_update_count > 0) then
269 COMMIT;
270 G_insert_count := G_insert_count + l_insert_count;
271 G_update_count := G_update_count + l_update_count;
272 end if;
273
274 EXIT WHEN C_get_CodeIds%NOTFOUND;
275 END LOOP;
276
277 CLOSE C_get_CodeIds;
278
279 iex_debug_pub.logmessage (' ASSIGN COLLECTION LEVEL - Insert Party Count = '||G_insert_count);
280 iex_debug_pub.logmessage (' ASSIGN COLLECTION LEVEL - Update Party Count = '||G_update_count);
281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Parties Assigned Collection Level Created..>> '|| G_insert_count);
282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Parties Assigned Collection Level Updated..>> '|| G_update_count);
283
284 if l_return_status <> 'S' then
285 iex_debug_pub.logmessage('Error from IEX_PARTY_PREF_PUB ..>> '||l_msg_data);
286 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error from IEX_PARTY_PREF_PUB ..>> '||l_msg_data);
287 ERRBUF := ' ASSIGN COLLECTION LEVEL - - Exception = ' ||SQLERRM;
288 RETCODE := -1;
289 end if;
290
291 EXCEPTION
292 when others then
293 iex_debug_pub.logmessage (' ASSIGN COLLECTION LEVEL - - Exception = ' ||SQLERRM);
294 ERRBUF := ' ASSIGN COLLECTION LEVEL - - Exception = ' ||SQLERRM;
295 RETCODE := -1;
296
297 END;
298
299
300
301 END IEX_ASSIGN_COLL_LEVEL_PVT;