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: PRAGMA AUTONOMOUS_TRANSACTION; /*Added for bug 8240551*/

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: PRAGMA AUTONOMOUS_TRANSACTION; /*Added for bug 8240551*/
159: l_insert_clause VARCHAR2(2000);

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

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;

Line 181: IF NOT fnd_api.Compatible_API_Call ( l_api_version

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

Line 186: RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;

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

Line 210: IF p_search_sites = fnd_api.G_TRUE THEN

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

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

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;

Line 236: l_existing_suppliers_only := fnd_api.G_FALSE;

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;

Line 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

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

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

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

Line 258: IF p_search_sites = fnd_api.G_TRUE THEN

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;
257: -- If we are searching for supplier sites then add the join condition to where clause
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

Line 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

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

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

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;

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

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

Line 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

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

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

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

Line 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

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

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

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:

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

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:

Line 337: IF p_search_sites = fnd_api.G_TRUE THEN

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

Line 341: IF p_filter_suppliers = fnd_api.G_FALSE THEN

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
344: l_suppl_where_clause := l_suppl_where_clause ||
345: ' AND NOT EXISTS ( SELECT 1 FROM ego_item_associations eia3 ' ||

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

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
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 ' ||
347: ' AND eia3.pk1_value = aas.vendor_id AND eia3.organization_id = :b_master_org_id )';

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

347: ' AND eia3.pk1_value = aas.vendor_id AND eia3.organization_id = :b_master_org_id )';
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 ' ||

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

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

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

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;