APPS.AR_BPA_UTILS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18

	select  distinct ctl.sales_order
	from    ra_customer_trx_lines ctl
	where   ctl.customer_trx_id = p_customer_trx_id
	and	ctl.line_type = 'LINE';

Line: 47

	select  distinct ctl.interface_line_attribute1
	from    ra_customer_trx_lines ctl
	where   ctl.customer_trx_id = p_customer_trx_id
	and	ctl.line_type = 'LINE';

Line: 76

		select  1
		from    ra_customer_trx ctl
		where   ctl.customer_trx_id = p_customer_trx_id
		and ctl.interface_header_context = 'OKS CONTRACTS';

Line: 81

		select  ctl.INVOICED_LINE_ACCTG_LEVEL
		from    ra_customer_trx_lines ctl
		where   ctl.customer_trx_id = p_customer_trx_id
		and	ctl.line_type = 'LINE'
		and ctl.interface_line_context = 'OKS CONTRACTS'
		and ctl.interface_line_attribute9 = 'Service';

Line: 111

	select hzc.name
	from ra_customer_trx trx,
	    hz_customer_profiles hzp,
	    hz_cust_profile_classes hzc
	where trx.customer_trx_id = p_customer_trx_id
	and  trx.bill_to_customer_id = hzp.cust_account_id
	and  trx.bill_to_site_use_id = hzp.site_use_id
	and  hzp.profile_class_id = hzc.profile_class_id;

Line: 121

	select hzc.name
	from ra_customer_trx trx,
	    hz_customer_profiles hzp,
	    hz_cust_profile_classes hzc
	where trx.customer_trx_id = p_customer_trx_id
	and  trx.bill_to_customer_id = hzp.cust_account_id
	and  hzp.site_use_id is null
	and  hzp.profile_class_id = hzc.profile_class_id;

Line: 155

      select cp_site.tax_printing_option
      from   hz_customer_profiles cp_site
      where  cp_site.site_use_id    = p_bill_to_site_use_id
      and    cp_site.cust_account_id    = p_bill_to_customer_id;

Line: 161

      select cp_cust.tax_printing_option
      from   hz_customer_profiles cp_cust
      where  cp_cust.cust_account_id = p_bill_to_customer_id
      and    cp_cust.site_use_id is null;

Line: 167

      SELECT tax_invoice_print from AR_SYSTEM_PARAMETERS;

Line: 201

SELECT 	1
FROM  	ra_customer_trx_lines
WHERE 	customer_trx_id = p_customer_trx_id
AND   	line_type = 'LINE'
and   	(source_data_key1 is not null
	 or source_data_key2 is not null
	 or source_data_key3 is not null
	 or source_data_key4 is not null
	 or source_data_key5 is not null
	);

Line: 229

	SELECT  to_char(round(ctl.tax_rate,4)) line_tax_rate
	from    ra_customer_trx_lines ctl
	where
	ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
	and	ctl.line_type = 'TAX'
	and rownum = 1;

Line: 253

	SELECT  v.tax_rate_name as printed_tax_name
	from    ra_customer_trx_lines ctl,
			zx_rates_vl v
	where
	ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
	and	ctl.line_type = 'TAX'
	and	ctl.vat_tax_id =  v.tax_rate_id(+)
	and rownum = 1;

Line: 279

	SELECT  v.tax_rate_code as tax_code
	from    ra_customer_trx_lines ctl,
			zx_rates_vl v
	where
	ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
	and	ctl.line_type = 'TAX'
	and	ctl.vat_tax_id =  v.tax_rate_id(+)
	and rownum = 1;

Line: 305

	SELECT  lines.customer_trx_line_id
	from    ra_customer_trx_lines lines
	where
	lines.customer_trx_id = p_customer_trx_id
	and	lines.line_type = 'LINE'
	and source_data_key1 = id
	and source_data_key2 = bcl_id
	and rownum = 1;

Line: 315

	SELECT  to_char(round(ctl.tax_rate,4)) line_tax_rate
	from    ra_customer_trx_lines ctl
	where
	ctl.link_to_cust_trx_line_id = id
	and	ctl.line_type = 'TAX'
	and rownum = 1;

Line: 351

	SELECT  lines.customer_trx_line_id
	from    ra_customer_trx_lines lines
	where
	lines.customer_trx_id = p_customer_trx_id
	and	lines.line_type = 'LINE'
	and source_data_key1 = id
	and source_data_key2 = bcl_id
	and rownum = 1;

Line: 361

	SELECT  v.tax_rate_name as printed_tax_name
	from    ra_customer_trx_lines ctl,
			zx_rates_vl v
	where
	ctl.link_to_cust_trx_line_id = id
	and	ctl.line_type = 'TAX'
	and	ctl.vat_tax_id =  v.tax_rate_id(+)
	and rownum = 1;

Line: 399

	SELECT  lines.customer_trx_line_id
	from    ra_customer_trx_lines lines
	where
	lines.customer_trx_id = p_customer_trx_id
	and	lines.line_type = 'LINE'
	and source_data_key1 = id
	and source_data_key2 = bcl_id
	and rownum = 1;

Line: 409

	SELECT  v.tax_rate_code as tax_code
	from    ra_customer_trx_lines ctl,
			zx_rates_vl v
	where
	ctl.link_to_cust_trx_line_id = id
	and	ctl.line_type = 'TAX'
	and	ctl.vat_tax_id =  v.tax_rate_id(+)
	and rownum = 1;

Line: 447

	SELECT  lines.customer_trx_line_id
	from    ra_customer_trx_lines lines
	where
	lines.customer_trx_id = p_customer_trx_id
	and	lines.line_type = 'LINE'
	and source_data_key1 = id
	and source_data_key2 = bcl_id
	and rownum = 1;

Line: 473

	SELECT
		ctl.line_type,
		nvl(ctl.translated_description,ctl.description) line_description,
		ctl.tax_rate,
	    ctl.vat_tax_id,
	    ctl.tax_exemption_id,
	    ctl.sales_tax_id,
	    ctl.tax_precedence
	from    ra_customer_trx_lines ctl
	where   ctl.customer_trx_line_id = p_customer_trx_line_id;

Line: 521

SELECT  party.person_first_name fname
       ,party.person_last_name lname
FROM hz_cust_account_roles       acct_role,
        hz_relationships            rel,
        hz_parties                  party
WHERE acct_role.cust_account_role_id = id
AND   acct_role.ROLE_TYPE            = 'CONTACT'
AND   acct_role.party_id             = rel.party_id
AND   rel.subject_id                 = party.party_id
AND   rel.SUBJECT_TABLE_NAME         = 'HZ_PARTIES'
AND   rel.OBJECT_TABLE_NAME          = 'HZ_PARTIES'
AND   rel.DIRECTIONAL_FLAG           = 'F';

Line: 556

   select min(decode(cont_point.contact_point_type,'TLX',
         cont_point.telex_number, cont_point.phone_number)) contact_phone
   from  hz_contact_points cont_point,
         hz_cust_account_roles acct_role
   where acct_role.cust_account_role_id = id
     and acct_role.party_id = cont_point.owner_table_id
     and cont_point.owner_table_name = 'HZ_PARTIES'
     and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'GEN';

Line: 582

   select min(decode(cont_point.contact_point_type,'TLX',
         cont_point.telex_number, cont_point.phone_number)) contact_fax
   from  hz_contact_points cont_point,
         hz_cust_account_roles acct_role
   where acct_role.cust_account_role_id = id
     and acct_role.party_id = cont_point.owner_table_id
     and cont_point.owner_table_name = 'HZ_PARTIES'
     and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'FAX';

Line: 617

    select t.tax_type_code,t.compounding_precedence
    from zx_taxes_vl t,zx_rates_vl r
    where t.tax_regime_code = r.tax_regime_code
    and t.tax = r.tax
    and r.tax_rate_id = id;

Line: 624

    select tax_rate_code as tax_code, tax_rate_name as printed_tax_name from zx_rates_vl where tax_rate_id = id;

Line: 627

    select
        decode(nvl(product_id,-999),-999,'CUSTOMER','ITEM') as exemption_type,
        rate_modifier as percent_exempt
    from zx_exemptions
    where tax_exemption_id = id;

Line: 634

    select meaning
    from fnd_lookups
    where lookup_type = 'ZX_TAX_TYPE_CATEGORY'
    and lookup_code = code;

Line: 726

   along with these inserts.
   */
procedure create_dup_areas(
  p_orig_template_id IN NUMBER,
  p_dup_template_id IN NUMBER
) IS


cursor c_org_ca_b IS
  select
    CA.ITEM_LABEL_STYLE,
    CA.ITEM_VALUE_STYLE,
    CA.CONTENT_DISP_PROMPT_STYLE,
    CA.INVOICE_LINE_TYPE,
    CA.AREA_CODE,
    CA.PARENT_AREA_CODE,
    CA.LINE_REGION_FLAG,
    CA.CONTENT_COUNT,
    CA.CONTENT_AREA_RIGHT_SPACE,
    CA.CONTENT_AREA_TOP_SPACE,
    CA.CONTENT_AREA_BOTTOM_SPACE,
    CA.DISPLAY_LEVEL,
    CA.CONTENT_TYPE,
    CA.CONTENT_ORIENTATION,
    CA.CONTENT_STYLE_ID,
    CA.ITEM_ID,
    CA.URL_ID,
    CA.DISPLAY_SEQUENCE,
    CA.CONTENT_AREA_WIDTH,
    CA.CONTENT_AREA_LEFT_SPACE,
    CA.ITEM_COLUMN_WIDTH,
    CA.CONTENT_AREA_ID
   from ar_bpa_content_areas_b CA
where CA.template_id = p_orig_template_id
order by CA.content_area_id;

Line: 767

  1. insert into ca_b, ca_tl
  2. insert into area_items
  */
  FOR cabrec in c_org_ca_b
  LOOP
		    select ar_bpa_content_areas_s.nextval
		    into l_new_ca_id
		    from dual;

Line: 776

			  insert into AR_BPA_CONTENT_AREAS_B (
			    ITEM_LABEL_STYLE,
			    ITEM_VALUE_STYLE,
			    CONTENT_DISP_PROMPT_STYLE,
			    INVOICE_LINE_TYPE,
			    AREA_CODE,
			    PARENT_AREA_CODE,
			    LINE_REGION_FLAG,
			    CONTENT_COUNT,
			    CONTENT_AREA_RIGHT_SPACE,
			    CONTENT_AREA_TOP_SPACE,
			    CONTENT_AREA_BOTTOM_SPACE,
			    CONTENT_AREA_ID,
			    DISPLAY_LEVEL,
			    CONTENT_TYPE,
			    CONTENT_ORIENTATION,
			    TEMPLATE_ID,
			    CONTENT_STYLE_ID,
			    ITEM_ID,
			    URL_ID,
			    DISPLAY_SEQUENCE,
			    CONTENT_AREA_WIDTH,
			    CONTENT_AREA_LEFT_SPACE,
			    CREATION_DATE,
			    CREATED_BY,
			    LAST_UPDATE_DATE,
			    LAST_UPDATED_BY,
			    LAST_UPDATE_LOGIN,
                      ITEM_COLUMN_WIDTH
			  ) values (
			    cabrec.ITEM_LABEL_STYLE,
			    cabrec.ITEM_VALUE_STYLE,
			    cabrec.CONTENT_DISP_PROMPT_STYLE,
			    cabrec.INVOICE_LINE_TYPE,
			    cabrec.AREA_CODE,
			    cabrec.PARENT_AREA_CODE,
			    cabrec.LINE_REGION_FLAG,
			    cabrec.CONTENT_COUNT,
			    cabrec.CONTENT_AREA_RIGHT_SPACE,
			   	cabrec.CONTENT_AREA_TOP_SPACE,
			    cabrec.CONTENT_AREA_BOTTOM_SPACE,
			    l_new_ca_id,
			    cabrec.DISPLAY_LEVEL,
			    cabrec.CONTENT_TYPE,
			    cabrec.CONTENT_ORIENTATION,
			    p_dup_template_id,
			    cabrec.CONTENT_STYLE_ID,
			    cabrec.ITEM_ID,
			    cabrec.URL_ID,
			    cabrec.DISPLAY_SEQUENCE,
			    cabrec.CONTENT_AREA_WIDTH,
			    cabrec.CONTENT_AREA_LEFT_SPACE,
			    sysdate,
			    l_user_id,
			    sysdate,
			    l_user_id,
			    l_user_id,
                      cabrec.ITEM_COLUMN_WIDTH
			 );

Line: 836

		  insert into AR_BPA_CONTENT_AREAS_TL (
		    CONTENT_AREA_ID,
		    CONTENT_AREA_NAME,
		    CONTENT_DISPLAY_PROMPT,
		    CREATED_BY,
		    CREATION_DATE,
		    LAST_UPDATED_BY,
		    LAST_UPDATE_DATE,
		    LAST_UPDATE_LOGIN,
		    LANGUAGE,
		    SOURCE_LANG
		  ) select
		    l_new_ca_id,
		    CONTENT_AREA_NAME,
		    CONTENT_DISPLAY_PROMPT,
		    l_user_id,
		    sysdate,
		    l_user_id,
		    sysdate,
		    l_user_id,
		    LANGUAGE,
		    SOURCE_LANG
		  from AR_BPA_CONTENT_AREAS_TL
		  where CONTENT_AREA_ID = cabrec.CONTENT_AREA_ID;

Line: 862

		/* insert item_id of original template into ar_bpa_area_items_map table  */
		insert into ar_bpa_area_items
		( area_item_id,
		  template_id,
			parent_area_code,
			display_level,
			secondary_app_id,
			item_id,
			display_sequence,
		    data_source_id,
		    flexfield_item_flag,
		  created_by,
		  creation_date,
		  last_updated_by,
		  last_update_date,
		  last_update_login)
		select ar_bpa_area_items_s.nextval,
			  p_dup_template_id,
		    ca.parent_area_code,
		    ca.display_level,
		    decode(item.seeded_application_id,222,-1, item.seeded_application_id),
		    ca.item_id,
		    ca.display_sequence,
		    item.data_source_id,
		    item.flexfield_item_flag,
		    l_user_id,
		    sysdate,
		    l_user_id,
		    sysdate,
		    l_user_id
		from ar_bpa_content_areas_b ca , ar_bpa_items_vl item
		where ca.template_id = p_orig_template_id
		and ca.item_id is not null
		and ca.item_id = item.item_id
		;

Line: 903

procedure DELETE_FLEXFIELD_ITEMS (
  P_DATASRC_APP_ID in NUMBER
) is
begin
  delete from AR_BPA_ITEMS_TL
  where item_id in (select item_id from ar_bpa_items_b
                               where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID);

Line: 911

  delete from AR_BPA_ITEMS_B
  where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID;

Line: 914

end DELETE_FLEXFIELD_ITEMS;

Line: 916

procedure UPDATE_VIEW_ITEM (
  P_ITEM_ID in NUMBER default null,
  P_ITEM_CODE in VARCHAR2,
  P_DISPLAY_LEVEL in VARCHAR2,
  P_DATA_SOURCE_ID in NUMBER,
  P_DISPLAY_ENABLED_FLAG in VARCHAR2,
  P_SEEDED_APPLICATION_ID in NUMBER,
  P_DATA_TYPE in VARCHAR2,
  P_COLUMN_NAME in VARCHAR2,
  P_ITEM_NAME in VARCHAR2,
  P_DISPLAY_PROMPT in VARCHAR2,
  P_ITEM_DESCRIPTION in VARCHAR2,
  P_FLEXFIELD_ITEM_FLAG in VARCHAR2,
  P_AMOUNT_ITEM_FLAG IN VARCHAR2,
  P_ASSIGNMENT_ENABLED_FLAG IN VARCHAR2,
  P_DISPLAYED_MULTI_LEVEL_FLAG  IN VARCHAR2,
  P_TAX_ITEM_FLAG in VARCHAR2,
  P_TOTALS_ENABLED_FLAG in VARCHAR2,
  P_LINK_ENABLED_FLAG in VARCHAR2,
  P_ITEM_TYPE in VARCHAR2
) is
      row_id             varchar2(64);

Line: 947

    AR_BPA_ITEMS_PKG.UPDATE_ROW (
		X_AMOUNT_ITEM_FLAG         => P_AMOUNT_ITEM_FLAG,
		X_ASSIGNMENT_ENABLED_FLAG  => P_ASSIGNMENT_ENABLED_FLAG,
		X_DATA_SOURCE_ID           => l_DATA_SOURCE_ID,
		X_DISPLAY_ENABLED_FLAG     => P_DISPLAY_ENABLED_FLAG,
		X_DISPLAY_LEVEL            => P_DISPLAY_LEVEL,
		X_DISPLAY_PROMPT           => P_DISPLAY_PROMPT,
		X_ITEM_CODE                => P_ITEM_CODE,
		X_ITEM_DESCRIPTION         => P_ITEM_DESCRIPTION,
		X_ITEM_ID                  => P_ITEM_ID,
		X_ITEM_IMAGE_FILENAME      => null,
		X_ITEM_MESSAGE_NAME        => null,
		X_ITEM_NAME                => P_ITEM_NAME,
		X_ITEM_SOURCE              => 'P',
		X_ITEM_TEXT_VALUE          => null,
		X_ITEM_TYPE                => P_ITEM_TYPE,
		X_URL_ID                   => null,
        X_LAST_UPDATE_DATE => sysdate,
        X_LAST_UPDATED_BY => 0,
        X_LAST_UPDATE_LOGIN => 0,
		X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
		X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
		X_TAX_ITEM_FLAG			   => P_TAX_ITEM_FLAG,
		X_TOTALS_ENABLED_FLAG	   => P_TOTALS_ENABLED_FLAG,
		X_LINK_ENABLED_FLAG		   => P_LINK_ENABLED_FLAG,
		X_DATA_TYPE 			   => P_DATA_TYPE,
		X_COLUMN_NAME 			   => P_COLUMN_NAME,
		X_URLCFG_ENABLED_FLAG	   => null,
		X_FLEXFIELD_ITEM_FLAG	   => P_FLEXFIELD_ITEM_FLAG);

Line: 978

       	   select ar_bpa_items_s.nextval
       	   INTO item_id
       	   from dual;

Line: 981

           AR_BPA_ITEMS_PKG.INSERT_ROW (
                 X_ROWID => row_id,
				X_AMOUNT_ITEM_FLAG         => P_AMOUNT_ITEM_FLAG,
				X_ASSIGNMENT_ENABLED_FLAG  => P_ASSIGNMENT_ENABLED_FLAG,
				X_DATA_SOURCE_ID           => l_DATA_SOURCE_ID,
				X_DISPLAY_ENABLED_FLAG     => P_DISPLAY_ENABLED_FLAG,
				X_DISPLAY_LEVEL            => P_DISPLAY_LEVEL,
				X_DISPLAY_PROMPT           => P_DISPLAY_PROMPT,
				X_ITEM_CODE                => P_ITEM_CODE,
				X_ITEM_DESCRIPTION         => P_ITEM_DESCRIPTION,
				X_ITEM_ID                  => item_id,
				X_ITEM_IMAGE_FILENAME      => null,
				X_ITEM_MESSAGE_NAME        => null,
				X_ITEM_NAME                => P_ITEM_NAME,
				X_ITEM_SOURCE              => 'P',
				X_ITEM_TEXT_VALUE          => null,
				X_ITEM_TYPE                => P_ITEM_TYPE,
				X_URL_ID                   => null,
                X_CREATION_DATE => sysdate,
                X_CREATED_BY => 0,
                X_LAST_UPDATE_DATE => sysdate,
                X_LAST_UPDATED_BY => 0,
                X_LAST_UPDATE_LOGIN => 0,
				X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
				X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
				X_TAX_ITEM_FLAG			   => P_TAX_ITEM_FLAG,
				X_TOTALS_ENABLED_FLAG	   => P_TOTALS_ENABLED_FLAG,
				X_LINK_ENABLED_FLAG		   => P_LINK_ENABLED_FLAG,
				X_DATA_TYPE 			   => P_DATA_TYPE,
				X_COLUMN_NAME 			   => P_COLUMN_NAME,
				X_URLCFG_ENABLED_FLAG	   => null,
				X_FLEXFIELD_ITEM_FLAG	   => P_FLEXFIELD_ITEM_FLAG);

Line: 1013

end UPDATE_VIEW_ITEM;

Line: 1015

procedure DELETE_VIEW_ITEM (
  P_ITEM_ID in NUMBER
) is
begin
	 AR_BPA_ITEMS_PKG.DELETE_ROW(
	  	X_ITEM_ID => P_ITEM_ID );

Line: 1021

end DELETE_VIEW_ITEM;