1 PACKAGE BODY ego_item_associations_util AS
2 /* $Header: EGOVIAUB.pls 120.3 2007/06/07 04:59:58 grastogi noship $ */
3
4 G_SEARCH_CURSOR NUMBER := dbms_sql.open_cursor;
5 G_SEARCH_STMT LONG;
6 G_PKG_NAME VARCHAR2(30);
7 G_FILE_NAME VARCHAR2(12);
8 G_USER_ID fnd_user.user_id%TYPE;
9 G_PARTY_ID hz_parties.party_id%TYPE;
10 G_LOGIN_ID fnd_user.last_update_login%TYPE;
11 G_SESSION_LANG VARCHAR2(99);
12
13
14 /*
15 -- Start of comments
16 -- API name : set_globals
17 -- Type : Private.
18 -- Function : Sets the global constant values used in this package.
19 -- Pre-reqs : None.
20 -- Version : Initial version 1.0
21 -- Notes : Sets the global constant values used in this package.
22 -- 1. G_USER_ID - user id
23 -- 2. G_SYSDATE - Creation Date and Update Date
24 -- 3. G_LOGIN_ID - Login which is used to create/update.
25 -- End of comments
26 */
27 PROCEDURE set_globals
28 IS
29 BEGIN
30 -- fnd_global.apps_initialize(1068, 431, 24089);
31 --
32 -- file names
33 --
34 G_FILE_NAME := NVL(G_FILE_NAME,'EGOVIAUB.pls');
35 G_PKG_NAME := NVL(G_PKG_NAME,'EGO_ITEM_ASSOCIATONS_UTIL');
36 --
37 -- user values
38 --
39 G_USER_ID := FND_GLOBAL.user_id;
40 G_LOGIN_ID := FND_GLOBAL.login_id;
41 G_SESSION_LANG := USERENV('LANG');
42 BEGIN
43 SELECT party_id
44 INTO G_PARTY_ID
45 FROM ego_user_v
46 WHERE USER_ID = G_USER_ID;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 SELECT party_id, user_id
50 INTO G_PARTY_ID, G_USER_ID
51 FROM ego_user_v
52 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
53 END;
54 END set_globals;
55
56
57 /*
58 -- Start of comments
59 -- API name : is_supplier_contact
60 -- Type : Private.
61 -- Function : Checks whether the party is a supplier contatc or not.
62 -- Pre-reqs : None.
63 -- Version : Initial version 1.0
64 -- Notes : None.
65 -- End of comments
66 */
67 FUNCTION is_supplier_contact(p_party_id IN NUMBER) RETURN VARCHAR2
68 IS
69 l_vendor_contact VARCHAR2(1) := 'F';
70 BEGIN
71 SELECT 'T'
72 INTO l_vendor_contact
73 FROM dual
74 WHERE EXISTS
75 (
76 SELECT 1
77 FROM ap_supplier_contacts ascs
78 WHERE ascs.per_party_id = G_PARTY_ID
79 );
80 RETURN l_vendor_contact;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 l_vendor_contact := 'F';
84 RETURN l_vendor_contact;
85 END is_supplier_contact;
86
87 /*
88 -- Start of comments
89 -- API name : a_debug
90 -- Type : Private.
91 -- Function : Writes the debug message into debug table.
92 -- Pre-reqs : None.
93 -- Version : Initial version 1.0
94 -- Notes : None.
95 -- End of comments
96 PROCEDURE a_debug(p_msg VARCHAR2)
97 IS PRAGMA AUTONOMOUS_TRANSACTION;
98 BEGIN
99 INSERT INTO ARASAN_TEMP
100 VALUES (p_msg);
101 COMMIT;
102 END A_DEBUG;
103 */
104
105
106
107 -- Start of comments
108 -- API name : search_supplier_and_site
109 -- Type : Private.
110 -- Function : Searches the supplier and site for the given criteria
111 -- Pre-reqs :
112 -- i) Search criteria is built using the constants specified in ego_item_associations_util or EgoSearchAssociationAM.
113 -- ii) Search Columns and Criteria has same number of values
114 -- iii) If p_search_sites is fnd_api.G_FALSE, then the criteria
115 -- for those columns should not be passed.
116 -- Will result in SQL exception if passed.
117 -- iv) Atleast one criteria has been specified to search.
118 -- v) Passed organization id should be master organization id. No validation will be done from search. Wrong value
119 -- will result in no rows.
120 -- Parameters :
121 -- IN : p_api_version IN NUMBER Required
122 -- p_batch_id IN OUT NOCOPY NUMBER Required
123 -- p_search_cols IN EGO_VARCHAR_TBL_TYPE Required
124 -- p_search_criteria IN EGO_VARCHAR_TBL_TYPE Required
125 -- p_search_sites IN VARCHAR2 Optional Default fnd_api.G_FALSE
126 -- p_filter_rows IN VARCHAR2 Optional Default fnd_api.G_FALSE
127 -- p_inventory_item_id IN NUMBER Optional Default NULL
128 -- p_master_org_id IN NUMBER Required
129 -- p_search_existing_site_only IN VARCHAR2 Optional Default fnd_api.G_FALSE
130 -- Version : Current version 1.0
131 -- Initial version 1.0
132 -- Notes : p_search_cols contains the search criteria columns. The list of columns are
133 -- defined as constants in ego_item_associations_util.
134 -- p_search_criteria will be corresponding search criteria for the columns.
135 -- Criteria and column names mapped based on index of the tables.
136 -- p_search_sites allows to search/return site results.
137 -- p_filter_rows specifies whether already associated needs to be filtered or not
138 -- p_inventory_item_id is the item id for which the check needs to be done.
139 -- Used only in single item flow. In mass flow, the intersections are cartersians.
140 -- p_master_org_id Master Organization Id in context of which the search needs to be performed
141 -- p_search_existing_site_only Searches only existing sites. Used in item-site-org flow
142 --
143 -- End of comments
144 PROCEDURE search_supplier_and_site
145 (
146 p_api_version IN NUMBER
147 ,p_batch_id IN OUT NOCOPY NUMBER
148 ,p_search_cols IN EGO_VARCHAR_TBL_TYPE
149 ,p_search_criteria IN EGO_VARCHAR_TBL_TYPE
150 ,p_search_sites IN VARCHAR2 := fnd_api.G_FALSE
151 ,p_filter_rows IN VARCHAR2 := fnd_api.G_FALSE
152 ,p_inventory_item_id IN NUMBER := NULL
153 ,p_master_org_id IN NUMBER
154 ,p_search_existing_site_only IN VARCHAR2 := fnd_api.G_FALSE
155 ,p_filter_suppliers IN VARCHAR2 := fnd_api.G_FALSE
156 )
157 IS
158 l_insert_clause VARCHAR2(2000);
159 l_select_clause VARCHAR2(2000);
160 l_from_clause VARCHAR2(500);
161 l_where_clause VARCHAR2(2000);
162 l_filter_clause VARCHAR2(2000);
163 l_suppl_insert_clause VARCHAR2(2000);
164 l_suppl_select_clause VARCHAR2(2000);
165 l_suppl_from_clause VARCHAR2(2000);
166 l_suppl_where_clause VARCHAR2(2000);
167 l_rc NUMBER;
168 l_existing_suppliers_only VARCHAR2(1) := fnd_api.G_TRUE;
169 l_search_col_index NUMBER;
170 l_err_msg VARCHAR2 (2000);
171 l_api_name CONSTANT VARCHAR2(30) := 'search_supplier_and_site';
172 l_api_version CONSTANT NUMBER := 1.0;
173 l_dynamic_sql VARCHAR2(32767);
174 l_sec_predicate VARCHAR2(32767);
175 BEGIN
176 -- Standard Start of API savepoint
177 SAVEPOINT search_supplier_and_site_pvt;
178 set_globals();
179 -- Standard call to check for call compatibility.
180 IF NOT fnd_api.Compatible_API_Call ( l_api_version
181 ,p_api_version
182 ,l_api_name
183 ,G_PKG_NAME )
184 THEN
185 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188 -- if batch is not null then delete the existing rows in ego_suppliersite_tmp
189 IF p_batch_id IS NULL THEN
190 -- dbms_output.put_line(' Deleting rows');
191 -- EXECUTE IMMEDIATE 'DELETE FROM ego_suppliersite_tmp WHERE batch_id = :b_batch_id' USING p_batch_id;
192 -- else create one batch_id here. This is only for temp tables..so get it from massupdate seq.
193 -- ELSE
194 SELECT ego_massupdate_s.NEXTVAL
195 INTO p_batch_id
196 FROM dual;
197 END IF;
198 -- construct the search result query dynamically
199 -- dbms_output.put_line(' Called search_supplier_and_sites' );
200 l_insert_clause := ' INSERT INTO ego_suppliersite_tmp ( created_by, creation_date, last_updated_by, last_update_date, select_flag, batch_id, supplier_id, supplier_number, supplier_name, duns_number, tax_payer_id, tax_registration_num ';
201 l_suppl_insert_clause := ' INSERT INTO ego_suppliersite_tmp ( created_by, creation_date, last_updated_by, last_update_date, select_flag, batch_id, supplier_id, supplier_number, supplier_name, duns_number, tax_payer_id, tax_registration_num ) ';
202 l_select_clause := ' SELECT fnd_global.USER_ID, sysdate, fnd_global.USER_ID, sysdate, ''Y'', :batch_id, aas.vendor_id, aas.segment1, aas.vendor_name, hp.duns_number_c, aas.num_1099, aas.vat_registration_num ';
203 l_suppl_select_clause := ' SELECT fnd_global.USER_ID, sysdate, fnd_global.USER_ID, sysdate, ''Y'', :batch_id, aas.vendor_id, aas.segment1, aas.vendor_name, hp.duns_number_c, aas.num_1099, aas.vat_registration_num ';
204 l_from_clause := ' FROM ap_suppliers aas, hz_parties hp ';
205 l_suppl_from_clause := ' FROM ap_suppliers aas, hz_parties hp ';
206 l_where_clause := ' WHERE aas.party_id = hp.party_id AND NVL(aas.end_date_active,SYSDATE+1) > SYSDATE ';
207 l_suppl_where_clause := ' WHERE aas.party_id = hp.party_id AND NVL(aas.end_date_active,SYSDATE+1) > SYSDATE ';
208 -- Add site related query if the search site flag is set
209 IF p_search_sites = fnd_api.G_TRUE THEN
210 -- dbms_output.put_line(' search sites is TRUE' );
211 l_insert_clause := l_insert_clause || ', supplier_site_id, supplier_site_name, city, state, country ';
212 l_select_clause := l_select_clause || ', asa.vendor_site_id, asa.vendor_site_code, asa.city, asa.state, asa.country ';
213 l_from_clause := l_from_clause || ', ap_supplier_sites_all asa';
214 END IF;
215 -- Columns needs to be inserted are added. So close the paranthesis
216 l_insert_clause := l_insert_clause || ' ) ';
217 -- Add the search criteria blindly. Since search column values are passed using constants,
218 -- which holds the exact column name with alias just add them to query and add bind values for the
219 -- criteria.
220 IF p_search_cols.FIRST IS NOT NULL THEN
221 l_search_col_index := 1;
222 -- Add criteria with AND condition.
223 WHILE l_search_col_index IS NOT NULL
224 LOOP
225 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
226 -- If there is supplier search criteria then search the sites for existing suppliers.
227 IF ( p_search_cols(l_search_col_index) = G_SUPPLIER_NAME
228 OR p_search_cols(l_search_col_index) = G_SUPPLIER_NUMBER
229 OR p_search_cols(l_search_col_index) = G_DUNS_NUMBER
230 OR p_search_cols(l_search_col_index) = G_TAX_PAYER_ID
231 OR p_search_cols(l_search_col_index) = G_TAX_REGISTRATION_NUMBER )
232 THEN
233 IF p_search_sites = fnd_api.G_TRUE AND l_existing_suppliers_only = fnd_api.G_TRUE
234 THEN
235 l_existing_suppliers_only := fnd_api.G_FALSE;
236 END IF;
237 l_suppl_where_clause := l_suppl_where_clause || ' AND ' || p_search_cols(l_search_col_index) || ' LIKE ' || ' :bv'||l_search_col_index;
238 END IF;
239 l_where_clause := l_where_clause || ' AND ' || p_search_cols(l_search_col_index) || ' LIKE ' || ' :bv'||l_search_col_index;
240 END IF;
241 l_search_col_index := p_search_cols.NEXT(l_search_col_index);
242 END LOOP;
243 END IF;
244 IF l_existing_suppliers_only = fnd_api.G_TRUE AND p_search_sites = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
245 l_where_clause := l_where_clause ||
246 ' AND EXISTS ( SELECT 1 FROM ego_item_associations eia1 ' ||
247 ' WHERE eia1.inventory_item_id = :b_item_id AND eia1.data_level_id = 43103 ' ||
248 ' AND eia1.pk1_value = aas.vendor_id AND eia1.organization_id = :b_master_org_id ) ';
249 END IF;
250 IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
251 l_where_clause := l_where_clause ||
252 ' AND EXISTS ( SELECT 1 FROM ego_item_associations eia2 ' ||
253 ' WHERE eia2.data_level_id = 43104 AND eia2.pk2_value = asa.vendor_site_id ' ||
254 ' AND eia2.inventory_item_id = :b_item_id AND eia2.organization_id = :b_master_org_id )';
255 END IF;
256 -- If we are searching for supplier sites then add the join condition to where clause
257 IF p_search_sites = fnd_api.G_TRUE THEN
258 l_where_clause := l_where_clause || ' AND asa.vendor_id = aas.vendor_id AND asa.org_id = fnd_profile.value(''ORG_ID'') ';
259 END IF;
260 -- If filter rows is set to yes, then check whether any intersections already exists for this supplier site
261 IF p_search_sites = fnd_api.G_TRUE AND p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
262 l_where_clause := l_where_clause ||
263 ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia4 ' ||
264 ' WHERE eia4.inventory_item_id = :b_item_id AND eia4.data_level_id = 43104 ' ||
265 ' AND eia4.pk2_value = asa.vendor_site_id AND eia4.organization_id = :b_master_org_id )';
266 END IF;
267 -- l_where_clause := l_where_clause || ' AND EXISTS ( SELECT 1 FROM ap_supplier_sites_all assa WHERE assa.vendor_id = aas.vendor_id ) ';
268 -- Filter for item-site-org intersection
269 -- This cannot be done, because the mass update org table is not populated.
270 -- IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
271 -- l_where_clause := l_where_clause || ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia5, ' ||
272 -- ' ego_massupdate_org_tmp emot WHERE eia5.inventory_item_id = :b_item_id AND eia5.data_level_id = 43105 AND eia5.pk2_value = asa.vendor_site_id ' ||
273 -- 'AND eia5.organization_id = emot.organization_id_child AND emot.batch_id = :b_batch_id )';
274 -- END IF;
275 -- a_debug(' l_insert_clause ' || l_insert_clause );
276 -- a_debug(' l_select_clause ' || l_select_clause );
277 -- a_debug(' l_from_clause ' || l_from_clause );
278 -- a_debug(' l_where_clause ' || l_where_clause );
279 -- a_debug(' l_filter_clause ' || l_filter_clause );
280
281 -- Construct the stmt by concatenating insert..select..from..where
282 g_search_stmt := l_insert_clause || l_select_clause || l_from_clause || l_where_clause;
283 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
284 g_search_stmt := g_search_stmt || ' AND EXISTS ' ||
285 ' ( ' ||
286 ' SELECT 1 ' ||
287 ' FROM ego_vendor_v evv ' ||
288 ' WHERE evv.vendor_id = aas.vendor_id ' ||
289 ' AND evv.user_id = :b_user_id '||
290 ' )';
291
292 END IF;
293 dbms_sql.parse( g_search_cursor, g_search_stmt, dbms_sql.native );
294 -- Bind the batch id which is the first parameter
295 dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
296 -- Bind the search criteria values
297 IF p_search_criteria.FIRST IS NOT NULL THEN
298 l_search_col_index := p_search_criteria.FIRST;
299 WHILE l_search_col_index IS NOT NULL
300 LOOP
301 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
302 --dbms_output.put_line(' Binding ' || ':bv' || l_search_col_index || ' value ' || p_search_criteria(l_search_col_index));
303 dbms_sql.bind_variable( g_search_cursor, ':bv' || l_search_col_index, p_search_criteria(l_search_col_index) );
304 l_search_col_index := p_search_criteria.NEXT(l_search_col_index);
305 END IF;
306 END LOOP;
307 END IF;
308 -- If there is no supplier search criteria then bind item id for the criteria
309 IF l_existing_suppliers_only = fnd_api.G_TRUE AND p_search_sites = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
310 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
311 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
312 END IF;
313 IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
317 -- Bind the value for filter criteria of supplier site
314 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
315 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
316 END IF;
318 IF p_search_sites = fnd_api.G_TRUE AND p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
319 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
320 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
321 END IF;
322 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
323 dbms_sql.bind_variable( g_search_cursor, ':b_user_id' , G_USER_ID );
324 END IF;
325
326
327 -- Bind the value for filter condition of item-site-org intersection
328 -- This cannot be done, because the mass update org table is not populated.
329 --IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
330 -- dbms_sql.bind_variable( g_search_cursor, ':b_item_id' , p_inventory_item_id );
331 -- dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
332 --END IF;
333
334 -- Execute the query which will insert the rows in ego_suppliersite_tmp
335 -- Execute only when search sites is TRUE
336 IF p_search_sites = fnd_api.G_TRUE THEN
337 l_rc := dbms_sql.execute( g_search_cursor );
338 END IF;
339
340 IF p_filter_suppliers = fnd_api.G_FALSE THEN
341 l_suppl_where_clause := l_suppl_where_clause || ' AND EXISTS ( SELECT 1 FROM ap_supplier_sites_all assa WHERE assa.vendor_id = aas.vendor_id AND assa.org_id = fnd_profile.value(''ORG_ID'')) ';
342 IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
343 l_suppl_where_clause := l_suppl_where_clause ||
344 ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia3 ' ||
345 ' WHERE eia3.inventory_item_id = :b_item_id AND eia3.data_level_id = 43103 ' ||
346 ' AND eia3.pk1_value = aas.vendor_id AND eia3.organization_id = :b_master_org_id )';
347 END IF;
348 g_search_stmt := l_suppl_insert_clause || l_suppl_select_clause || l_suppl_from_clause || l_suppl_where_clause;
349 -- If filter rows is set to yes, then check whether any intersections already exists for this supplier
350 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE
351 THEN
352 g_search_stmt := g_search_stmt || ' AND EXISTS ' ||
353 ' ( ' ||
354 ' SELECT 1 ' ||
355 ' FROM ego_vendor_v evv ' ||
356 ' WHERE evv.vendor_id = aas.vendor_id ' ||
357 ' AND evv.user_id = :b_user_id '||
358 ' )';
359
360 END IF;
361 dbms_sql.parse( g_search_cursor, g_search_stmt, dbms_sql.native );
362 -- Bind the value for filter criteria of supplier
363 IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
364 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
365 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
366 END IF;
367 -- Bind the batch id which is the first parameter
368 dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
369 IF p_search_cols.FIRST IS NOT NULL THEN
370 l_search_col_index := 1;
371 -- Add criteria with AND condition.
372 WHILE l_search_col_index IS NOT NULL
373 LOOP
374 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
375 -- If there is supplier search criteria then search the sites for existing suppliers.
376 IF ( p_search_cols(l_search_col_index) = G_SUPPLIER_NAME
377 OR p_search_cols(l_search_col_index) = G_SUPPLIER_NUMBER
378 OR p_search_cols(l_search_col_index) = G_DUNS_NUMBER
379 OR p_search_cols(l_search_col_index) = G_TAX_PAYER_ID
380 OR p_search_cols(l_search_col_index) = G_TAX_REGISTRATION_NUMBER )
381 THEN
382 dbms_sql.bind_variable( g_search_cursor, ':bv' || l_search_col_index, p_search_criteria(l_search_col_index) );
383 END IF;
384 END IF;
385 l_search_col_index := p_search_cols.NEXT(l_search_col_index);
386 END LOOP;
387 END IF;
388 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
389 dbms_sql.bind_variable( g_search_cursor, ':b_user_id' , G_USER_ID );
390 END IF;
391 l_rc := dbms_sql.execute( g_search_cursor );
392 END IF;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 -- Sometimes the exception trace might be larger then 2000 characters.
397 -- But the complete error message should be less than 2000 characters.
398 -- Since the SQLERRM is a token with in the message, add only 1900
399 -- characters, which should be enough to identify the root cause
400 l_err_msg := 'Unexpected Error Occured: ' || SUBSTR(SQLERRM,1,1900);
401 fnd_message.set_name ('EGO', 'EGO_PLSQL_ERROR');
402 fnd_message.set_token ('PKG_NAME', G_PKG_NAME);
403 fnd_message.set_token ('API_NAME', l_api_name);
404 fnd_message.set_token ('SQL_ERR_MSG', l_err_msg);
405 --ROLLBACK TO search_supplier_and_site_pvt;
406 app_exception.raise_exception;
407 END search_supplier_and_site;
408
409 END ego_item_associations_util;
[Home] [Help]