1 PACKAGE BODY ego_item_associations_util AS
2 /* $Header: EGOVIAUB.pls 120.6 2011/06/01 09:24:33 erwu ship $ */
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 PRAGMA AUTONOMOUS_TRANSACTION; /*Added for bug 8240551*/
159 l_insert_clause VARCHAR2(2000);
160 l_select_clause VARCHAR2(2000);
161 l_from_clause VARCHAR2(500);
162 l_where_clause VARCHAR2(2000);
163 l_filter_clause VARCHAR2(2000);
164 l_suppl_insert_clause VARCHAR2(2000);
165 l_suppl_select_clause VARCHAR2(2000);
166 l_suppl_from_clause VARCHAR2(2000);
167 l_suppl_where_clause VARCHAR2(2000);
168 l_rc NUMBER;
169 l_existing_suppliers_only VARCHAR2(1) := fnd_api.G_TRUE;
170 l_search_col_index NUMBER;
171 l_err_msg VARCHAR2 (2000);
172 l_api_name CONSTANT VARCHAR2(30) := 'search_supplier_and_site';
173 l_api_version CONSTANT NUMBER := 1.0;
174 l_dynamic_sql VARCHAR2(32767);
175 l_sec_predicate VARCHAR2(32767);
176 BEGIN
177 -- Standard Start of API savepoint
178 SAVEPOINT search_supplier_and_site_pvt;
179 set_globals();
180 -- Standard call to check for call compatibility.
181 IF NOT fnd_api.Compatible_API_Call ( l_api_version
182 ,p_api_version
183 ,l_api_name
184 ,G_PKG_NAME )
185 THEN
186 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188
189 -- if batch is not null then delete the existing rows in ego_suppliersite_tmp
190 IF p_batch_id IS NULL THEN
191 -- dbms_output.put_line(' Deleting rows');
192 -- EXECUTE IMMEDIATE 'DELETE FROM ego_suppliersite_tmp WHERE batch_id = :b_batch_id' USING p_batch_id;
193 -- else create one batch_id here. This is only for temp tables..so get it from massupdate seq.
194 -- ELSE
195 SELECT ego_massupdate_s.NEXTVAL
196 INTO p_batch_id
197 FROM dual;
198 END IF;
199 -- construct the search result query dynamically
200 -- dbms_output.put_line(' Called search_supplier_and_sites' );
201 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 ';
202 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 ) ';
203 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 ';
204 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 ';
205 l_from_clause := ' FROM ap_suppliers aas, hz_parties hp ';
206 l_suppl_from_clause := ' FROM ap_suppliers aas, hz_parties hp ';
207 l_where_clause := ' WHERE aas.party_id = hp.party_id AND NVL(aas.end_date_active,SYSDATE+1) > SYSDATE ';
208 l_suppl_where_clause := ' WHERE aas.party_id = hp.party_id AND NVL(aas.end_date_active,SYSDATE+1) > SYSDATE ';
209 -- Add site related query if the search site flag is set
210 IF p_search_sites = fnd_api.G_TRUE THEN
211 -- dbms_output.put_line(' search sites is TRUE' );
212 l_insert_clause := l_insert_clause || ', supplier_site_id, supplier_site_name, city, state, country ';
213 l_select_clause := l_select_clause || ', asa.vendor_site_id, asa.vendor_site_code, asa.city, asa.state, asa.country ';
214 l_from_clause := l_from_clause || ', ap_supplier_sites_all asa';
215 END IF;
216 -- Columns needs to be inserted are added. So close the paranthesis
217 l_insert_clause := l_insert_clause || ' ) ';
218 -- Add the search criteria blindly. Since search column values are passed using constants,
219 -- which holds the exact column name with alias just add them to query and add bind values for the
220 -- criteria.
221 IF p_search_cols.FIRST IS NOT NULL THEN
222 l_search_col_index := 1;
223 -- Add criteria with AND condition.
224 WHILE l_search_col_index IS NOT NULL
225 LOOP
226 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
227 -- If there is supplier search criteria then search the sites for existing suppliers.
228 IF ( p_search_cols(l_search_col_index) = G_SUPPLIER_NAME
229 OR p_search_cols(l_search_col_index) = G_SUPPLIER_NUMBER
230 OR p_search_cols(l_search_col_index) = G_DUNS_NUMBER
231 OR p_search_cols(l_search_col_index) = G_TAX_PAYER_ID
232 OR p_search_cols(l_search_col_index) = G_TAX_REGISTRATION_NUMBER )
233 THEN
234 IF p_search_sites = fnd_api.G_TRUE AND l_existing_suppliers_only = fnd_api.G_TRUE
235 THEN
236 l_existing_suppliers_only := fnd_api.G_FALSE;
237 END IF;
238 l_suppl_where_clause := l_suppl_where_clause || ' AND ' || p_search_cols(l_search_col_index) || ' LIKE ' || ' :bv'||l_search_col_index;
239 END IF;
240 l_where_clause := l_where_clause || ' AND ' || p_search_cols(l_search_col_index) || ' LIKE ' || ' :bv'||l_search_col_index;
241 END IF;
242 l_search_col_index := p_search_cols.NEXT(l_search_col_index);
243 END LOOP;
244 END IF;
245 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
246 l_where_clause := l_where_clause ||
247 ' AND EXISTS ( SELECT 1 FROM ego_item_associations eia1 ' ||
248 ' WHERE eia1.inventory_item_id = :b_item_id AND eia1.data_level_id = 43103 ' ||
249 ' AND eia1.pk1_value = aas.vendor_id AND eia1.organization_id = :b_master_org_id ) ';
250 END IF;
251 IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
252 l_where_clause := l_where_clause ||
253 ' AND EXISTS ( SELECT 1 FROM ego_item_associations eia2 ' ||
257 -- If we are searching for supplier sites then add the join condition to where clause
254 ' WHERE eia2.data_level_id = 43104 AND eia2.pk2_value = asa.vendor_site_id ' ||
255 ' AND eia2.inventory_item_id = :b_item_id AND eia2.organization_id = :b_master_org_id )';
256 END IF;
258 IF p_search_sites = fnd_api.G_TRUE THEN
259 l_where_clause := l_where_clause || ' AND asa.vendor_id = aas.vendor_id AND asa.org_id = fnd_profile.value(''ORG_ID'') and nvl(asa.inactive_date,SYSDATE + 1)>SYSDATE '; --bug 11072046 NVL(aas.end_date_active,SYSDATE+1) > SYSDATE
260 END IF;
261 -- If filter rows is set to yes, then check whether any intersections already exists for this supplier site
262 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
263 l_where_clause := l_where_clause ||
264 ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia4 ' ||
265 ' WHERE eia4.inventory_item_id = :b_item_id AND eia4.data_level_id = 43104 ' ||
266 ' AND eia4.pk2_value = asa.vendor_site_id AND eia4.organization_id = :b_master_org_id )';
267 END IF;
268 -- l_where_clause := l_where_clause || ' AND EXISTS ( SELECT 1 FROM ap_supplier_sites_all assa WHERE assa.vendor_id = aas.vendor_id ) ';
269 -- Filter for item-site-org intersection
270 -- This cannot be done, because the mass update org table is not populated.
271 -- IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
272 -- l_where_clause := l_where_clause || ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia5, ' ||
273 -- ' 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 ' ||
274 -- 'AND eia5.organization_id = emot.organization_id_child AND emot.batch_id = :b_batch_id )';
275 -- END IF;
276 -- a_debug(' l_insert_clause ' || l_insert_clause );
277 -- a_debug(' l_select_clause ' || l_select_clause );
278 -- a_debug(' l_from_clause ' || l_from_clause );
279 -- a_debug(' l_where_clause ' || l_where_clause );
280 -- a_debug(' l_filter_clause ' || l_filter_clause );
281
282 -- Construct the stmt by concatenating insert..select..from..where
283 g_search_stmt := l_insert_clause || l_select_clause || l_from_clause || l_where_clause;
284 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
285 g_search_stmt := g_search_stmt || ' AND EXISTS ' ||
286 ' ( ' ||
287 ' SELECT 1 ' ||
288 ' FROM ego_vendor_v evv ' ||
289 ' WHERE evv.vendor_id = aas.vendor_id ' ||
290 ' AND evv.user_id = :b_user_id '||
291 ' )';
292
293 END IF;
294 dbms_sql.parse( g_search_cursor, g_search_stmt, dbms_sql.native );
295 -- Bind the batch id which is the first parameter
296 dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
297 -- Bind the search criteria values
298 IF p_search_criteria.FIRST IS NOT NULL THEN
299 l_search_col_index := p_search_criteria.FIRST;
300 WHILE l_search_col_index IS NOT NULL
301 LOOP
302 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
303 --dbms_output.put_line(' Binding ' || ':bv' || l_search_col_index || ' value ' || p_search_criteria(l_search_col_index));
304 dbms_sql.bind_variable( g_search_cursor, ':bv' || l_search_col_index, p_search_criteria(l_search_col_index) );
305 l_search_col_index := p_search_criteria.NEXT(l_search_col_index);
306 END IF;
307 END LOOP;
308 END IF;
309 -- If there is no supplier search criteria then bind item id for the criteria
310 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
311 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
312 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
313 END IF;
314 IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
315 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
316 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
317 END IF;
318 -- Bind the value for filter criteria of supplier site
319 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
320 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
321 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
322 END IF;
323 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
324 dbms_sql.bind_variable( g_search_cursor, ':b_user_id' , G_USER_ID );
325 END IF;
326
327
328 -- Bind the value for filter condition of item-site-org intersection
329 -- This cannot be done, because the mass update org table is not populated.
330 --IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
331 -- dbms_sql.bind_variable( g_search_cursor, ':b_item_id' , p_inventory_item_id );
332 -- dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
333 --END IF;
334
335 -- Execute the query which will insert the rows in ego_suppliersite_tmp
336 -- Execute only when search sites is TRUE
337 IF p_search_sites = fnd_api.G_TRUE THEN
338 l_rc := dbms_sql.execute( g_search_cursor );
339 END IF;
340
341 IF p_filter_suppliers = fnd_api.G_FALSE THEN
342 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'') and nvl(assa.inactive_date,SYSDATE + 1)>SYSDATE ) ';
343 IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
347 ' AND eia3.pk1_value = aas.vendor_id AND eia3.organization_id = :b_master_org_id )';
344 l_suppl_where_clause := l_suppl_where_clause ||
345 ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia3 ' ||
346 ' WHERE eia3.inventory_item_id = :b_item_id AND eia3.data_level_id = 43103 ' ||
348 END IF;
349 g_search_stmt := l_suppl_insert_clause || l_suppl_select_clause || l_suppl_from_clause || l_suppl_where_clause;
350 -- If filter rows is set to yes, then check whether any intersections already exists for this supplier
351 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE
352 THEN
353 g_search_stmt := g_search_stmt || ' AND EXISTS ' ||
354 ' ( ' ||
355 ' SELECT 1 ' ||
356 ' FROM ego_vendor_v evv ' ||
357 ' WHERE evv.vendor_id = aas.vendor_id ' ||
358 ' AND evv.user_id = :b_user_id '||
359 ' )';
360
361 END IF;
362 dbms_sql.parse( g_search_cursor, g_search_stmt, dbms_sql.native );
363 -- Bind the value for filter criteria of supplier
364 IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN
365 dbms_sql.bind_variable( g_search_cursor, ':b_item_id', p_inventory_item_id);
366 dbms_sql.bind_variable( g_search_cursor, ':b_master_org_id', p_master_org_id);
367 END IF;
368 -- Bind the batch id which is the first parameter
369 dbms_sql.bind_variable( g_search_cursor, ':batch_id' , p_batch_id );
370 IF p_search_cols.FIRST IS NOT NULL THEN
371 l_search_col_index := 1;
372 -- Add criteria with AND condition.
373 WHILE l_search_col_index IS NOT NULL
374 LOOP
375 IF p_search_criteria(l_search_col_index) IS NOT NULL THEN
376 -- If there is supplier search criteria then search the sites for existing suppliers.
377 IF ( p_search_cols(l_search_col_index) = G_SUPPLIER_NAME
378 OR p_search_cols(l_search_col_index) = G_SUPPLIER_NUMBER
379 OR p_search_cols(l_search_col_index) = G_DUNS_NUMBER
380 OR p_search_cols(l_search_col_index) = G_TAX_PAYER_ID
381 OR p_search_cols(l_search_col_index) = G_TAX_REGISTRATION_NUMBER )
382 THEN
383 dbms_sql.bind_variable( g_search_cursor, ':bv' || l_search_col_index, p_search_criteria(l_search_col_index) );
384 END IF;
385 END IF;
386 l_search_col_index := p_search_cols.NEXT(l_search_col_index);
387 END LOOP;
388 END IF;
389 IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
390 dbms_sql.bind_variable( g_search_cursor, ':b_user_id' , G_USER_ID );
391 END IF;
392 l_rc := dbms_sql.execute( g_search_cursor );
393 END IF;
394 COMMIT; /*Added for bug 8240551*/
395 EXCEPTION
396 WHEN OTHERS THEN
397 -- Sometimes the exception trace might be larger then 2000 characters.
398 -- But the complete error message should be less than 2000 characters.
399 -- Since the SQLERRM is a token with in the message, add only 1900
400 -- characters, which should be enough to identify the root cause
401 l_err_msg := 'Unexpected Error Occured: ' || SUBSTR(SQLERRM,1,1900);
402 fnd_message.set_name ('EGO', 'EGO_PLSQL_ERROR');
403 fnd_message.set_token ('PKG_NAME', G_PKG_NAME);
404 fnd_message.set_token ('API_NAME', l_api_name);
405 fnd_message.set_token ('SQL_ERR_MSG', l_err_msg);
406 --ROLLBACK TO search_supplier_and_site_pvt;
407 app_exception.raise_exception;
408 END search_supplier_and_site;
409
410 END ego_item_associations_util;
[Home] [Help]