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.OZF_NET_ACCRUAL_ENGINE_PVT dependencies on FND_DSQL

Line 306: FND_DSQL.add_text('(');

302: l_line_index := 1;
303: -- Currently NA qualifiers does not support grouping, each group has only 1 line
304: l_no_lines := 1;
305: --
306: FND_DSQL.add_text('(');
307: --
308: FOR j IN c_qualifiers(i.qualifier_id)
309: LOOP
310: --

Line 329: FND_DSQL.add_text(' INTERSECT ');

325: --
326: IF l_line_index < l_no_lines
327: THEN
328: --
329: FND_DSQL.add_text(' INTERSECT ');
330: l_line_index := l_line_index + 1;
331: --
332: END IF;
333: --

Line 338: FND_DSQL.add_text(')');

334: END IF;
335: --
336: END LOOP; -- c_qualifiers
337: --
338: FND_DSQL.add_text(')');
339: --
340: IF l_group_index < l_no_groups
341: THEN
342: --

Line 343: FND_DSQL.add_text(' UNION ');

339: --
340: IF l_group_index < l_no_groups
341: THEN
342: --
343: FND_DSQL.add_text(' UNION ');
344: l_group_index := l_group_index + 1;
345: --
346: END IF;
347: --

Line 352: -- FND_DSQL.add_text('(SELECT -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');

348: END LOOP; -- c_groups
349: --
350: ELSE
351: --
352: -- FND_DSQL.add_text('(SELECT -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
353: FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
354: --
355: END IF;
356:

Line 353: FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');

349: --
350: ELSE
351: --
352: -- FND_DSQL.add_text('(SELECT -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
353: FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
354: --
355: END IF;
356:
357: IF p_calling_from_den = 'N' OR l_no_query_flag = 'N'

Line 360: x_party_stmt := FND_DSQL.get_text(FALSE);

356:
357: IF p_calling_from_den = 'N' OR l_no_query_flag = 'N'
358: THEN
359: --
360: x_party_stmt := FND_DSQL.get_text(FALSE);
361: --
362: ELSE
363: --
364: x_party_stmt := NULL;

Line 416: FND_DSQL.init;

412: --
413: x_return_status := FND_API.g_ret_sts_success;
414:
415: -- denorm parties
416: FND_DSQL.init;
417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');

Line 417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');

413: x_return_status := FND_API.g_ret_sts_success;
414:
415: -- denorm parties
416: FND_DSQL.init;
417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');

Line 418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');

414:
415: -- denorm parties
416: FND_DSQL.init;
417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');

Line 419: FND_DSQL.add_text('last_update_login,confidential_flag,');

415: -- denorm parties
416: FND_DSQL.init;
417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');

Line 420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');

416: FND_DSQL.init;
417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');

Line 421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');

417: FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');

Line 422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');

418: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);

Line 423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');

419: FND_DSQL.add_text('last_update_login,confidential_flag,');
420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');

Line 424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');

420: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');

Line 425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');

421: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');

Line 426: FND_DSQL.add_bind(p_offer_id);

422: FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');

Line 427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');

423: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');

Line 428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');

424: FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');
432:

Line 429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');

425: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');
432:
433:

Line 430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');

426: FND_DSQL.add_bind(p_offer_id);
427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');
432:
433:
434: ozf_utility_pvt.write_conc_log('-- Refresh_Parties (+)');

Line 431: FND_DSQL.add_text(' FROM (');

427: FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428: FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');
432:
433:
434: ozf_utility_pvt.write_conc_log('-- Refresh_Parties (+)');
435:

Line 436: /* refresh parties would get all the parties for the offer_id and add to FND_DSQL*/

432:
433:
434: ozf_utility_pvt.write_conc_log('-- Refresh_Parties (+)');
435:
436: /* refresh parties would get all the parties for the offer_id and add to FND_DSQL*/
437: refresh_parties(p_offer_id => p_offer_id,
438: p_calling_from_den => 'Y',
439: x_return_status => x_return_status,
440: x_msg_count => x_msg_count,

Line 454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');

450:
451: IF l_stmt_offer IS NOT NULL
452: THEN
453: --
454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');

Line 455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');

451: IF l_stmt_offer IS NOT NULL
452: THEN
453: --
454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);

Line 456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');

452: THEN
453: --
454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');

Line 457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');

453: --
454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');

Line 458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');

454: -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');

Line 459: FND_DSQL.add_bind(p_offer_id);

455: FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');

Line 460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');

456: FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');

Line 461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');

457: FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');
465:

Line 462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');

458: FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');
465:
466: l_denorm_csr := DBMS_SQL.open_cursor;

Line 463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');

459: FND_DSQL.add_bind(p_offer_id);
460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');
465:
466: l_denorm_csr := DBMS_SQL.open_cursor;
467: FND_DSQL.set_cursor(l_denorm_csr);

Line 464: FND_DSQL.add_text(')');

460: FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461: FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');
465:
466: l_denorm_csr := DBMS_SQL.open_cursor;
467: FND_DSQL.set_cursor(l_denorm_csr);
468: l_stmt_debug := FND_DSQL.get_text(TRUE);

Line 467: FND_DSQL.set_cursor(l_denorm_csr);

463: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464: FND_DSQL.add_text(')');
465:
466: l_denorm_csr := DBMS_SQL.open_cursor;
467: FND_DSQL.set_cursor(l_denorm_csr);
468: l_stmt_debug := FND_DSQL.get_text(TRUE);
469: l_stmt_denorm := FND_DSQL.get_text(FALSE);
470: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
471: FND_DSQL.do_binds;

Line 468: l_stmt_debug := FND_DSQL.get_text(TRUE);

464: FND_DSQL.add_text(')');
465:
466: l_denorm_csr := DBMS_SQL.open_cursor;
467: FND_DSQL.set_cursor(l_denorm_csr);
468: l_stmt_debug := FND_DSQL.get_text(TRUE);
469: l_stmt_denorm := FND_DSQL.get_text(FALSE);
470: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
471: FND_DSQL.do_binds;
472: l_ignore := DBMS_SQL.execute(l_denorm_csr);

Line 469: l_stmt_denorm := FND_DSQL.get_text(FALSE);

465:
466: l_denorm_csr := DBMS_SQL.open_cursor;
467: FND_DSQL.set_cursor(l_denorm_csr);
468: l_stmt_debug := FND_DSQL.get_text(TRUE);
469: l_stmt_denorm := FND_DSQL.get_text(FALSE);
470: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
471: FND_DSQL.do_binds;
472: l_ignore := DBMS_SQL.execute(l_denorm_csr);
473: dbms_sql.close_cursor(l_denorm_csr);

Line 471: FND_DSQL.do_binds;

467: FND_DSQL.set_cursor(l_denorm_csr);
468: l_stmt_debug := FND_DSQL.get_text(TRUE);
469: l_stmt_denorm := FND_DSQL.get_text(FALSE);
470: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
471: FND_DSQL.do_binds;
472: l_ignore := DBMS_SQL.execute(l_denorm_csr);
473: dbms_sql.close_cursor(l_denorm_csr);
474: --
475: END IF;

Line 595: FND_DSQL.init;

591: END IF;
592: --
593: END IF;
594:
595: FND_DSQL.init;
596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');

Line 596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');

592: --
593: END IF;
594:
595: FND_DSQL.init;
596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);

Line 597: FND_DSQL.add_text('SELECT inventory_item_id,');

593: END IF;
594:
595: FND_DSQL.init;
596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');

Line 598: FND_DSQL.add_bind(l_product.product_level);

594:
595: FND_DSQL.init;
596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);

Line 599: FND_DSQL.add_text(',');

595: FND_DSQL.init;
596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');

Line 600: FND_DSQL.add_bind(l_discount);

596: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);

Line 601: FND_DSQL.add_text(',');

597: FND_DSQL.add_text('SELECT inventory_item_id,');
598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');

Line 602: FND_DSQL.add_bind(l_discount_type);

598: FND_DSQL.add_bind(l_product.product_level);
599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);

Line 603: FND_DSQL.add_text(',');

599: FND_DSQL.add_text(',');
600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');

Line 604: FND_DSQL.add_bind(l_volume_from);

600: FND_DSQL.add_bind(l_discount);
601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);

Line 605: FND_DSQL.add_text(',');

601: FND_DSQL.add_text(',');
602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');

Line 606: FND_DSQL.add_bind(l_volume_to);

602: FND_DSQL.add_bind(l_discount_type);
603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);

Line 607: FND_DSQL.add_text(',');

603: FND_DSQL.add_text(',');
604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);
611: FND_DSQL.add_text(' FROM (');

Line 608: FND_DSQL.add_bind(l_volume_type);

604: FND_DSQL.add_bind(l_volume_from);
605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);
611: FND_DSQL.add_text(' FROM (');
612:

Line 609: FND_DSQL.add_text(',');

605: FND_DSQL.add_text(',');
606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);
611: FND_DSQL.add_text(' FROM (');
612:
613: IF l_product.product_level = 'FAMILY'

Line 610: FND_DSQL.add_bind(l_product.uom_code);

606: FND_DSQL.add_bind(l_volume_to);
607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);
611: FND_DSQL.add_text(' FROM (');
612:
613: IF l_product.product_level = 'FAMILY'
614: THEN

Line 611: FND_DSQL.add_text(' FROM (');

607: FND_DSQL.add_text(',');
608: FND_DSQL.add_bind(l_volume_type);
609: FND_DSQL.add_text(',');
610: FND_DSQL.add_bind(l_product.uom_code);
611: FND_DSQL.add_text(' FROM (');
612:
613: IF l_product.product_level = 'FAMILY'
614: THEN
615: --

Line 620: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');

616: IF l_func_area_id = 11
617: THEN
618: -- Functional Area is PRFA.
619: --
620: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
621: FND_DSQL.add_bind(l_org_id);
622: FND_DSQL.add_text(' AND epdhv.parent_id = ');
623: FND_DSQL.add_bind(l_product.product_id);
624: --

Line 621: FND_DSQL.add_bind(l_org_id);

617: THEN
618: -- Functional Area is PRFA.
619: --
620: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
621: FND_DSQL.add_bind(l_org_id);
622: FND_DSQL.add_text(' AND epdhv.parent_id = ');
623: FND_DSQL.add_bind(l_product.product_id);
624: --
625: ELSE

Line 622: FND_DSQL.add_text(' AND epdhv.parent_id = ');

618: -- Functional Area is PRFA.
619: --
620: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
621: FND_DSQL.add_bind(l_org_id);
622: FND_DSQL.add_text(' AND epdhv.parent_id = ');
623: FND_DSQL.add_bind(l_product.product_id);
624: --
625: ELSE
626: -- Functional Area id OMFA

Line 623: FND_DSQL.add_bind(l_product.product_id);

619: --
620: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
621: FND_DSQL.add_bind(l_org_id);
622: FND_DSQL.add_text(' AND epdhv.parent_id = ');
623: FND_DSQL.add_bind(l_product.product_id);
624: --
625: ELSE
626: -- Functional Area id OMFA
627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

Line 627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

623: FND_DSQL.add_bind(l_product.product_id);
624: --
625: ELSE
626: -- Functional Area id OMFA
627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
628: FND_DSQL.add_bind(l_org_id);
629: FND_DSQL.add_text(' AND category_id = ');
630: FND_DSQL.add_bind(l_product.product_id);
631: --

Line 628: FND_DSQL.add_bind(l_org_id);

624: --
625: ELSE
626: -- Functional Area id OMFA
627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
628: FND_DSQL.add_bind(l_org_id);
629: FND_DSQL.add_text(' AND category_id = ');
630: FND_DSQL.add_bind(l_product.product_id);
631: --
632: END IF;

Line 629: FND_DSQL.add_text(' AND category_id = ');

625: ELSE
626: -- Functional Area id OMFA
627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
628: FND_DSQL.add_bind(l_org_id);
629: FND_DSQL.add_text(' AND category_id = ');
630: FND_DSQL.add_bind(l_product.product_id);
631: --
632: END IF;
633: --

Line 630: FND_DSQL.add_bind(l_product.product_id);

626: -- Functional Area id OMFA
627: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
628: FND_DSQL.add_bind(l_org_id);
629: FND_DSQL.add_text(' AND category_id = ');
630: FND_DSQL.add_bind(l_product.product_id);
631: --
632: END IF;
633: --
634: ELSIF l_product.product_level = 'PRODUCT'

Line 637: FND_DSQL.add_text('SELECT ');

633: --
634: ELSIF l_product.product_level = 'PRODUCT'
635: THEN
636: --
637: FND_DSQL.add_text('SELECT ');
638: FND_DSQL.add_bind(l_product.product_id);
639: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
640: --
641: END IF;

Line 638: FND_DSQL.add_bind(l_product.product_id);

634: ELSIF l_product.product_level = 'PRODUCT'
635: THEN
636: --
637: FND_DSQL.add_text('SELECT ');
638: FND_DSQL.add_bind(l_product.product_id);
639: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
640: --
641: END IF;
642:

Line 639: FND_DSQL.add_text(' inventory_item_id FROM DUAL');

635: THEN
636: --
637: FND_DSQL.add_text('SELECT ');
638: FND_DSQL.add_bind(l_product.product_id);
639: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
640: --
641: END IF;
642:
643: FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)

Line 660: FND_DSQL.add_text(' MINUS ');

656: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
657: --
658: END IF;
659:
660: FND_DSQL.add_text(' MINUS ');
661:
662: IF l_exclusion.product_level = 'PRODUCT'
663: THEN
664: --

Line 665: FND_DSQL.add_text('SELECT ');

661:
662: IF l_exclusion.product_level = 'PRODUCT'
663: THEN
664: --
665: FND_DSQL.add_text('SELECT ');
666: FND_DSQL.add_bind(l_exclusion.product_id);
667: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
668: --
669: ELSIF l_exclusion.product_level = 'FAMILY'

Line 666: FND_DSQL.add_bind(l_exclusion.product_id);

662: IF l_exclusion.product_level = 'PRODUCT'
663: THEN
664: --
665: FND_DSQL.add_text('SELECT ');
666: FND_DSQL.add_bind(l_exclusion.product_id);
667: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
668: --
669: ELSIF l_exclusion.product_level = 'FAMILY'
670: THEN

Line 667: FND_DSQL.add_text(' inventory_item_id FROM DUAL');

663: THEN
664: --
665: FND_DSQL.add_text('SELECT ');
666: FND_DSQL.add_bind(l_exclusion.product_id);
667: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
668: --
669: ELSIF l_exclusion.product_level = 'FAMILY'
670: THEN
671: --

Line 678: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');

674: IF l_excl_func_area_id = 11
675: THEN
676: -- Functional Area is PRFA.
677: --
678: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
679: FND_DSQL.add_bind(l_org_id);
680: FND_DSQL.add_text(' AND epdhv.parent_id = ');
681: FND_DSQL.add_bind(l_exclusion.product_id);
682: --

Line 679: FND_DSQL.add_bind(l_org_id);

675: THEN
676: -- Functional Area is PRFA.
677: --
678: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
679: FND_DSQL.add_bind(l_org_id);
680: FND_DSQL.add_text(' AND epdhv.parent_id = ');
681: FND_DSQL.add_bind(l_exclusion.product_id);
682: --
683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);

Line 680: FND_DSQL.add_text(' AND epdhv.parent_id = ');

676: -- Functional Area is PRFA.
677: --
678: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
679: FND_DSQL.add_bind(l_org_id);
680: FND_DSQL.add_text(' AND epdhv.parent_id = ');
681: FND_DSQL.add_bind(l_exclusion.product_id);
682: --
683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684: ELSE

Line 681: FND_DSQL.add_bind(l_exclusion.product_id);

677: --
678: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
679: FND_DSQL.add_bind(l_org_id);
680: FND_DSQL.add_text(' AND epdhv.parent_id = ');
681: FND_DSQL.add_bind(l_exclusion.product_id);
682: --
683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684: ELSE
685: -- Functional Area id OMFA

Line 686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

682: --
683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684: ELSE
685: -- Functional Area id OMFA
686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687: FND_DSQL.add_bind(l_org_id);
688: FND_DSQL.add_text(' AND category_id = ');
689: FND_DSQL.add_bind(l_exclusion.product_id);
690: --

Line 687: FND_DSQL.add_bind(l_org_id);

683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684: ELSE
685: -- Functional Area id OMFA
686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687: FND_DSQL.add_bind(l_org_id);
688: FND_DSQL.add_text(' AND category_id = ');
689: FND_DSQL.add_bind(l_exclusion.product_id);
690: --
691: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);

Line 688: FND_DSQL.add_text(' AND category_id = ');

684: ELSE
685: -- Functional Area id OMFA
686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687: FND_DSQL.add_bind(l_org_id);
688: FND_DSQL.add_text(' AND category_id = ');
689: FND_DSQL.add_bind(l_exclusion.product_id);
690: --
691: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
692: END IF;

Line 689: FND_DSQL.add_bind(l_exclusion.product_id);

685: -- Functional Area id OMFA
686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687: FND_DSQL.add_bind(l_org_id);
688: FND_DSQL.add_text(' AND category_id = ');
689: FND_DSQL.add_bind(l_exclusion.product_id);
690: --
691: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
692: END IF;
693: --

Line 698: FND_DSQL.add_text(')');

694: END IF;
695: --
696: END LOOP;
697:
698: FND_DSQL.add_text(')');
699:
700: l_denorm_csr := DBMS_SQL.open_cursor;
701: FND_DSQL.set_cursor(l_denorm_csr);
702: l_stmt_debug := FND_DSQL.get_text(TRUE);

Line 701: FND_DSQL.set_cursor(l_denorm_csr);

697:
698: FND_DSQL.add_text(')');
699:
700: l_denorm_csr := DBMS_SQL.open_cursor;
701: FND_DSQL.set_cursor(l_denorm_csr);
702: l_stmt_debug := FND_DSQL.get_text(TRUE);
703: l_stmt_denorm := FND_DSQL.get_text(FALSE);
704: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
705: FND_DSQL.do_binds;

Line 702: l_stmt_debug := FND_DSQL.get_text(TRUE);

698: FND_DSQL.add_text(')');
699:
700: l_denorm_csr := DBMS_SQL.open_cursor;
701: FND_DSQL.set_cursor(l_denorm_csr);
702: l_stmt_debug := FND_DSQL.get_text(TRUE);
703: l_stmt_denorm := FND_DSQL.get_text(FALSE);
704: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
705: FND_DSQL.do_binds;
706: l_ignore := DBMS_SQL.execute(l_denorm_csr);

Line 703: l_stmt_denorm := FND_DSQL.get_text(FALSE);

699:
700: l_denorm_csr := DBMS_SQL.open_cursor;
701: FND_DSQL.set_cursor(l_denorm_csr);
702: l_stmt_debug := FND_DSQL.get_text(TRUE);
703: l_stmt_denorm := FND_DSQL.get_text(FALSE);
704: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
705: FND_DSQL.do_binds;
706: l_ignore := DBMS_SQL.execute(l_denorm_csr);
707: dbms_sql.close_cursor(l_denorm_csr);

Line 705: FND_DSQL.do_binds;

701: FND_DSQL.set_cursor(l_denorm_csr);
702: l_stmt_debug := FND_DSQL.get_text(TRUE);
703: l_stmt_denorm := FND_DSQL.get_text(FALSE);
704: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
705: FND_DSQL.do_binds;
706: l_ignore := DBMS_SQL.execute(l_denorm_csr);
707: dbms_sql.close_cursor(l_denorm_csr);
708: --
709: END LOOP;

Line 812: FND_DSQL.init;

808: ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
809: --
810: -- END IF;
811:
812: FND_DSQL.init;
813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');

Line 813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');

809: --
810: -- END IF;
811:
812: FND_DSQL.init;
813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);

Line 814: FND_DSQL.add_text('SELECT inventory_item_id,');

810: -- END IF;
811:
812: FND_DSQL.init;
813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');

Line 815: FND_DSQL.add_bind(l_product.product_level);

811:
812: FND_DSQL.init;
813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);

Line 816: FND_DSQL.add_text(',');

812: FND_DSQL.init;
813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');

Line 817: FND_DSQL.add_bind(l_discount.discount);

813: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);

Line 818: FND_DSQL.add_text(',');

814: FND_DSQL.add_text('SELECT inventory_item_id,');
815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');

Line 819: FND_DSQL.add_bind(l_discount.discount_type);

815: FND_DSQL.add_bind(l_product.product_level);
816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);

Line 820: FND_DSQL.add_text(',');

816: FND_DSQL.add_text(',');
817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');

Line 821: FND_DSQL.add_bind(l_discount.volume_from);

817: FND_DSQL.add_bind(l_discount.discount);
818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);

Line 822: FND_DSQL.add_text(',');

818: FND_DSQL.add_text(',');
819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');

Line 823: FND_DSQL.add_bind(l_discount.volume_to);

819: FND_DSQL.add_bind(l_discount.discount_type);
820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);

Line 824: FND_DSQL.add_text(',');

820: FND_DSQL.add_text(',');
821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);
828: FND_DSQL.add_text(' FROM (');

Line 825: FND_DSQL.add_bind(l_discount.volume_type);

821: FND_DSQL.add_bind(l_discount.volume_from);
822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);
828: FND_DSQL.add_text(' FROM (');
829:

Line 826: FND_DSQL.add_text(',');

822: FND_DSQL.add_text(',');
823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);
828: FND_DSQL.add_text(' FROM (');
829:
830: IF l_product.product_level = 'FAMILY'

Line 827: FND_DSQL.add_bind(l_product.uom_code);

823: FND_DSQL.add_bind(l_discount.volume_to);
824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);
828: FND_DSQL.add_text(' FROM (');
829:
830: IF l_product.product_level = 'FAMILY'
831: THEN

Line 828: FND_DSQL.add_text(' FROM (');

824: FND_DSQL.add_text(',');
825: FND_DSQL.add_bind(l_discount.volume_type);
826: FND_DSQL.add_text(',');
827: FND_DSQL.add_bind(l_product.uom_code);
828: FND_DSQL.add_text(' FROM (');
829:
830: IF l_product.product_level = 'FAMILY'
831: THEN
832: --

Line 837: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');

833: IF l_func_area_id = 11
834: THEN
835: -- Functional Area is PRFA.
836: --
837: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
838: FND_DSQL.add_bind(l_org_id);
839: FND_DSQL.add_text(' AND epdhv.parent_id = ');
840: FND_DSQL.add_bind(l_product.product_id);
841: --

Line 838: FND_DSQL.add_bind(l_org_id);

834: THEN
835: -- Functional Area is PRFA.
836: --
837: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
838: FND_DSQL.add_bind(l_org_id);
839: FND_DSQL.add_text(' AND epdhv.parent_id = ');
840: FND_DSQL.add_bind(l_product.product_id);
841: --
842: ELSE

Line 839: FND_DSQL.add_text(' AND epdhv.parent_id = ');

835: -- Functional Area is PRFA.
836: --
837: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
838: FND_DSQL.add_bind(l_org_id);
839: FND_DSQL.add_text(' AND epdhv.parent_id = ');
840: FND_DSQL.add_bind(l_product.product_id);
841: --
842: ELSE
843: -- Functional Area id OMFA

Line 840: FND_DSQL.add_bind(l_product.product_id);

836: --
837: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
838: FND_DSQL.add_bind(l_org_id);
839: FND_DSQL.add_text(' AND epdhv.parent_id = ');
840: FND_DSQL.add_bind(l_product.product_id);
841: --
842: ELSE
843: -- Functional Area id OMFA
844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

Line 844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

840: FND_DSQL.add_bind(l_product.product_id);
841: --
842: ELSE
843: -- Functional Area id OMFA
844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
845: FND_DSQL.add_bind(l_org_id);
846: FND_DSQL.add_text(' AND category_id = ');
847: FND_DSQL.add_bind(l_product.product_id);
848: --

Line 845: FND_DSQL.add_bind(l_org_id);

841: --
842: ELSE
843: -- Functional Area id OMFA
844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
845: FND_DSQL.add_bind(l_org_id);
846: FND_DSQL.add_text(' AND category_id = ');
847: FND_DSQL.add_bind(l_product.product_id);
848: --
849: END IF;

Line 846: FND_DSQL.add_text(' AND category_id = ');

842: ELSE
843: -- Functional Area id OMFA
844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
845: FND_DSQL.add_bind(l_org_id);
846: FND_DSQL.add_text(' AND category_id = ');
847: FND_DSQL.add_bind(l_product.product_id);
848: --
849: END IF;
850: --

Line 847: FND_DSQL.add_bind(l_product.product_id);

843: -- Functional Area id OMFA
844: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
845: FND_DSQL.add_bind(l_org_id);
846: FND_DSQL.add_text(' AND category_id = ');
847: FND_DSQL.add_bind(l_product.product_id);
848: --
849: END IF;
850: --
851: ELSIF l_product.product_level = 'PRODUCT'

Line 854: FND_DSQL.add_text('SELECT ');

850: --
851: ELSIF l_product.product_level = 'PRODUCT'
852: THEN
853: --
854: FND_DSQL.add_text('SELECT ');
855: FND_DSQL.add_bind(l_product.product_id);
856: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
857: --
858: END IF;

Line 855: FND_DSQL.add_bind(l_product.product_id);

851: ELSIF l_product.product_level = 'PRODUCT'
852: THEN
853: --
854: FND_DSQL.add_text('SELECT ');
855: FND_DSQL.add_bind(l_product.product_id);
856: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
857: --
858: END IF;
859:

Line 856: FND_DSQL.add_text(' inventory_item_id FROM DUAL');

852: THEN
853: --
854: FND_DSQL.add_text('SELECT ');
855: FND_DSQL.add_bind(l_product.product_id);
856: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
857: --
858: END IF;
859:
860: --amitamku - Added for Bug fix 13510229

Line 879: FND_DSQL.add_text(' MINUS ');

875: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
876: --
877: END IF;
878:
879: FND_DSQL.add_text(' MINUS ');
880:
881: IF l_exclusion.product_level = 'PRODUCT'
882: THEN
883: --

Line 884: FND_DSQL.add_text('SELECT ');

880:
881: IF l_exclusion.product_level = 'PRODUCT'
882: THEN
883: --
884: FND_DSQL.add_text('SELECT ');
885: FND_DSQL.add_bind(l_exclusion.product_id);
886: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
887: --
888: ELSIF l_exclusion.product_level = 'FAMILY'

Line 885: FND_DSQL.add_bind(l_exclusion.product_id);

881: IF l_exclusion.product_level = 'PRODUCT'
882: THEN
883: --
884: FND_DSQL.add_text('SELECT ');
885: FND_DSQL.add_bind(l_exclusion.product_id);
886: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
887: --
888: ELSIF l_exclusion.product_level = 'FAMILY'
889: THEN

Line 886: FND_DSQL.add_text(' inventory_item_id FROM DUAL');

882: THEN
883: --
884: FND_DSQL.add_text('SELECT ');
885: FND_DSQL.add_bind(l_exclusion.product_id);
886: FND_DSQL.add_text(' inventory_item_id FROM DUAL');
887: --
888: ELSIF l_exclusion.product_level = 'FAMILY'
889: THEN
890: --

Line 896: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');

892: IF l_excl_func_area_id = 11
893: THEN
894: -- Functional Area is PRFA.
895: --
896: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
897: FND_DSQL.add_bind(l_org_id);
898: FND_DSQL.add_text(' AND epdhv.parent_id = ');
899: FND_DSQL.add_bind(l_exclusion.product_id);
900: --

Line 897: FND_DSQL.add_bind(l_org_id);

893: THEN
894: -- Functional Area is PRFA.
895: --
896: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
897: FND_DSQL.add_bind(l_org_id);
898: FND_DSQL.add_text(' AND epdhv.parent_id = ');
899: FND_DSQL.add_bind(l_exclusion.product_id);
900: --
901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);

Line 898: FND_DSQL.add_text(' AND epdhv.parent_id = ');

894: -- Functional Area is PRFA.
895: --
896: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
897: FND_DSQL.add_bind(l_org_id);
898: FND_DSQL.add_text(' AND epdhv.parent_id = ');
899: FND_DSQL.add_bind(l_exclusion.product_id);
900: --
901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902: ELSE

Line 899: FND_DSQL.add_bind(l_exclusion.product_id);

895: --
896: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
897: FND_DSQL.add_bind(l_org_id);
898: FND_DSQL.add_text(' AND epdhv.parent_id = ');
899: FND_DSQL.add_bind(l_exclusion.product_id);
900: --
901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902: ELSE
903: -- Functional Area id OMFA

Line 904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');

900: --
901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902: ELSE
903: -- Functional Area id OMFA
904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905: FND_DSQL.add_bind(l_org_id);
906: FND_DSQL.add_text(' AND category_id = ');
907: FND_DSQL.add_bind(l_exclusion.product_id);
908: --

Line 905: FND_DSQL.add_bind(l_org_id);

901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902: ELSE
903: -- Functional Area id OMFA
904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905: FND_DSQL.add_bind(l_org_id);
906: FND_DSQL.add_text(' AND category_id = ');
907: FND_DSQL.add_bind(l_exclusion.product_id);
908: --
909: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);

Line 906: FND_DSQL.add_text(' AND category_id = ');

902: ELSE
903: -- Functional Area id OMFA
904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905: FND_DSQL.add_bind(l_org_id);
906: FND_DSQL.add_text(' AND category_id = ');
907: FND_DSQL.add_bind(l_exclusion.product_id);
908: --
909: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
910: END IF;

Line 907: FND_DSQL.add_bind(l_exclusion.product_id);

903: -- Functional Area id OMFA
904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905: FND_DSQL.add_bind(l_org_id);
906: FND_DSQL.add_text(' AND category_id = ');
907: FND_DSQL.add_bind(l_exclusion.product_id);
908: --
909: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
910: END IF;
911: --

Line 917: FND_DSQL.add_text(')');

913: --
914: END LOOP;
915: --amitamku - End of - Added for Bug fix 13510229
916:
917: FND_DSQL.add_text(')');
918:
919: l_denorm_csr := DBMS_SQL.open_cursor;
920: FND_DSQL.set_cursor(l_denorm_csr);
921: l_stmt_debug := FND_DSQL.get_text(TRUE);

Line 920: FND_DSQL.set_cursor(l_denorm_csr);

916:
917: FND_DSQL.add_text(')');
918:
919: l_denorm_csr := DBMS_SQL.open_cursor;
920: FND_DSQL.set_cursor(l_denorm_csr);
921: l_stmt_debug := FND_DSQL.get_text(TRUE);
922: l_stmt_denorm := FND_DSQL.get_text(FALSE);
923: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
924: FND_DSQL.do_binds;

Line 921: l_stmt_debug := FND_DSQL.get_text(TRUE);

917: FND_DSQL.add_text(')');
918:
919: l_denorm_csr := DBMS_SQL.open_cursor;
920: FND_DSQL.set_cursor(l_denorm_csr);
921: l_stmt_debug := FND_DSQL.get_text(TRUE);
922: l_stmt_denorm := FND_DSQL.get_text(FALSE);
923: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
924: FND_DSQL.do_binds;
925: l_ignore := DBMS_SQL.execute(l_denorm_csr);

Line 922: l_stmt_denorm := FND_DSQL.get_text(FALSE);

918:
919: l_denorm_csr := DBMS_SQL.open_cursor;
920: FND_DSQL.set_cursor(l_denorm_csr);
921: l_stmt_debug := FND_DSQL.get_text(TRUE);
922: l_stmt_denorm := FND_DSQL.get_text(FALSE);
923: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
924: FND_DSQL.do_binds;
925: l_ignore := DBMS_SQL.execute(l_denorm_csr);
926: dbms_sql.close_cursor(l_denorm_csr);

Line 924: FND_DSQL.do_binds;

920: FND_DSQL.set_cursor(l_denorm_csr);
921: l_stmt_debug := FND_DSQL.get_text(TRUE);
922: l_stmt_denorm := FND_DSQL.get_text(FALSE);
923: DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
924: FND_DSQL.do_binds;
925: l_ignore := DBMS_SQL.execute(l_denorm_csr);
926: dbms_sql.close_cursor(l_denorm_csr);
927:
928: