eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EGO_ITEM_ASSOCIATIONS_UTIL dependencies on FND_API

Line 114: -- iii) If p_search_sites is fnd_api.G_FALSE, then the criteria

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

Line 125: -- p_search_sites IN VARCHAR2 Optional Default fnd_api.G_FALSE

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

Line 126: -- p_filter_rows IN VARCHAR2 Optional Default fnd_api.G_FALSE

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

Line 129: -- p_search_existing_site_only IN VARCHAR2 Optional Default fnd_api.G_FALSE

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.

Line 150: ,p_search_sites IN VARCHAR2 := fnd_api.G_FALSE

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

Line 151: ,p_filter_rows IN VARCHAR2 := fnd_api.G_FALSE

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

Line 154: ,p_search_existing_site_only IN VARCHAR2 := fnd_api.G_FALSE

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);

Line 155: ,p_filter_suppliers 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);

Line 168: l_existing_suppliers_only VARCHAR2(1) := fnd_api.G_TRUE;

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;

Line 180: IF NOT fnd_api.Compatible_API_Call ( l_api_version

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

Line 185: RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;

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

Line 209: IF p_search_sites = fnd_api.G_TRUE THEN

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';

Line 233: IF p_search_sites = fnd_api.G_TRUE AND l_existing_suppliers_only = fnd_api.G_TRUE

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;

Line 235: l_existing_suppliers_only := fnd_api.G_FALSE;

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;

Line 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

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 ) ';

Line 250: IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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 )';

Line 257: IF p_search_sites = fnd_api.G_TRUE THEN

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

Line 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

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 )';

Line 270: -- IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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;

Line 283: IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN

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 ' ||

Line 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

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

Line 313: IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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
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;
317: -- Bind the value for filter criteria of supplier site

Line 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

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;
317: -- Bind the value for filter criteria of supplier site
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

Line 322: IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN

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:

Line 329: --IF p_search_existing_site_only = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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:

Line 336: IF p_search_sites = fnd_api.G_TRUE THEN

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

Line 340: IF p_filter_suppliers = fnd_api.G_FALSE THEN

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 ' ||

Line 342: IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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 )';

Line 350: IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE

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 ' ||

Line 363: IF p_filter_rows = fnd_api.G_TRUE AND p_inventory_item_id IS NOT NULL THEN

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

Line 388: IF is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN

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;