APPS.ICX_REQ_ACCT2 SQL Statements

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

Line: 23

    select charge_account_id
    from icx_cart_line_distributions
    where cart_id = v_cart_id
    and cart_line_id = v_cart_line_id
    order by distribution_id;

Line: 30

    select count(*)
    from gl_sets_of_books gsb,
         financials_system_parameters fsp,
         gl_code_combinations gl
    where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
    and   gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
    and   gl.CODE_COMBINATION_ID = acct_id;

Line: 39

    select cart_line_number
    from icx_shopping_cart_lines
    where cart_id = cartid
    and cart_line_id = cartline_id;

Line: 135

PROCEDURE insert_row(v_cart_line_id IN NUMBER,
		     v_oo_id IN NUMBER,
		     v_cart_id IN NUMBER,
	             v_account_id IN NUMBER default NULL,
		     v_n_segments IN NUMBER default NULL,
	             v_segments IN fnd_flex_ext.SegmentArray,
                     v_account_num IN VARCHAR2 default NULL,
                     v_allocation_type IN VARCHAR2 default NULL,
                     v_allocation_value IN NUMBER default NULL) is


 cursor get_ak_columns is
        select  ltrim(rtrim(d.COLUMN_NAME)) COL_NAME
        from       ak_region_items a,
        ak_attributes b,
        ak_regions c,
        ak_object_attributes d
        where      a.NODE_DISPLAY_FLAG = 'Y'
        and        a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
        and        a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
        and        b.DATA_TYPE = 'VARCHAR2'
        and        c.REGION_APPLICATION_ID = 601
        and        a.REGION_CODE = c.REGION_CODE
        and        a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
        and        c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
        and        a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
        and        a.region_code = 'ICX_CART_LINE_DISTRIBUTIONS_R'
        and        d.COLUMN_NAME like 'CHARGE_ACCOUNT_SEGMENT%'
        order by a.display_sequence;

Line: 166

    l_insert_sql varchar2(8000);

Line: 186

      select max(distribution_num)
      from icx_cart_line_distributions
      where cart_id = v_cart_id
      and cart_line_id = v_cart_line_id
      and nvl(org_id,-9999) = nvl(v_oo_id,-9999);

Line: 193

      select cart_line_number
      from icx_shopping_cart_lines
      where cart_id = v_cart_id
      and cart_line_id = v_cart_line_id;

Line: 242

    l_insert_sql := 'Insert into icx_cart_line_distributions(cart_line_id,
                   distribution_id,distribution_num,charge_account_id,charge_account_num,
                   allocation_type,allocation_value';

Line: 246

     l_insert_sql := l_insert_sql || ' ,last_updated_by,last_update_date,
		   last_update_login, creation_date,created_by,org_id,cart_id';

Line: 251

  select icx_cart_line_distributions_s.nextval into v_distribution_id from sys.dual;

Line: 261

           l_insert_sql :=   l_insert_sql || ',' || prec.COL_NAME;

Line: 270

     l_insert_sql := l_insert_sql || ')';

Line: 275

     l_insert_sql := l_insert_sql || ' VALUES (' || v_cart_line_id || ',icx_cart_line_distributions_s.nextval,' || l_dist_num || ','
		  || v_account_id || ',''' || v_account_num || ''',''' || l_alloc_type|| ''','
                  || l_alloc_percent || ',' ||  l_shopper_id || ',sysdate,' || l_shopper_id || ',sysdate,' || l_shopper_id || ',' || v_oo_id || ',' || v_cart_id;

Line: 279

/*sugupta breaking l_insert_sql into two to reduce line length for MRC conversion*/

     l_insert_sql := l_insert_sql || 'values( :cart_line_id, :distribution_id, :distribution_num, :charge_account_id, :charge_account_num, :allocation_type, :allocation_value';

Line: 283

     l_insert_sql := l_insert_sql || ' , :last_updated_by, :last_update_date, :last_update_login, :creation_date, :created_by, :org_id, :cart_id';

Line: 288

             l_insert_sql := l_insert_sql || ',:a' || to_char(l);

Line: 293

     l_insert_sql := l_insert_sql || ')';

Line: 297

     dbms_sql.parse( v_cursor_id, l_insert_sql, DBMS_SQL.native);

Line: 308

     dbms_sql.bind_variable(v_cursor_id, ':last_updated_by', l_shopper_id );

Line: 309

     dbms_sql.bind_variable(v_cursor_id, ':last_update_date', sysdate );

Line: 310

     dbms_sql.bind_variable(v_cursor_id, ':last_update_login', l_shopper_id );

Line: 338

     update icx_cart_line_distributions
     set ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
     VARIANCE_ACCOUNT_ID = v_variance_acct_id,
     BUDGET_ACCOUNT_ID = v_budget_acct_id
     where CART_LINE_ID = v_cart_line_id
     and CART_ID = v_cart_id;

Line: 352

     select substr(l_error_message,12,512) into l_err_mesg from dual;

Line: 366

PROCEDURE update_row(v_cart_line_id IN NUMBER,
		     v_oo_id IN NUMBER,
		     v_cart_id IN NUMBER,
		     v_distribution_id IN NUMBER,
		     v_line_number IN NUMBER,
	             v_account_id IN NUMBER default NULL,
		     v_n_segments IN NUMBER default NULL,
	             v_segments IN fnd_flex_ext.SegmentArray,
                     v_account_num IN VARCHAR2 default NULL,
		     v_allocation_type IN VARCHAR2 default NULL,
		     v_allocation_value IN NUMBER default NULL) is


 cursor get_ak_columns is
        select  ltrim(rtrim(d.COLUMN_NAME)) COL_NAME
        from       ak_region_items a,
        ak_attributes b,
        ak_regions c,
        ak_object_attributes d
        where      a.NODE_DISPLAY_FLAG = 'Y'
        and        a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
        and        a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
        and        b.DATA_TYPE = 'VARCHAR2'
        and        c.REGION_APPLICATION_ID = 601
        and        a.REGION_CODE = c.REGION_CODE
        and        a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
        and        c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
        and        a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
        and        a.region_code = 'ICX_CART_LINE_DISTRIBUTIONS_R'
        and        d.COLUMN_NAME like 'CHARGE_ACCOUNT_SEGMENT%'
        order by a.display_sequence;

Line: 399

    l_insert_sql varchar2(8000);

Line: 418

      select cart_line_number
      from icx_shopping_cart_lines
      where cart_id = v_cart_id
      and cart_line_id = v_cart_line_id;

Line: 445

/*sugupta breaking l_insert_sql into two to reduce line length for MRC conversion*/

    l_insert_sql := 'Update icx_cart_line_distributions
	set last_updated_by = :last_updated_by,
	last_update_login = :last_update_login ,
	last_update_date = :last_update_date,
	allocation_type = decode( :allocation_type, null, allocation_type, :allocation_type),
	allocation_value = decode( :allocation_value,null, allocation_value, :allocation_value)';

Line: 453

    l_insert_sql := l_insert_sql || ' , charge_account_id = decode( :charge_account_id , null, charge_account_id, :charge_account_id),  charge_account_num = decode( :charge_account_num, null, charge_account_num, :charge_account_num)';

Line: 459

	l_insert_sql := l_insert_sql || ', allocation_type = ''' || v_allocation_type || '''';

Line: 462

        l_insert_sql := l_insert_sql || ', allocation_value = ' || v_allocation_value;

Line: 465

        l_insert_sql := l_insert_sql || ', charge_account_id = ' || v_account_id;

Line: 468

        l_insert_sql := l_insert_sql || ', charge_account_num = ''' || v_account_num || '''';

Line: 477

		 l_insert_sql :=   l_insert_sql || ',' || prec.COL_NAME || ' = :a' || to_char(l) ;

Line: 491

     l_insert_sql := l_insert_sql || ' where cart_id = ' || v_cart_id ||
		     ' and cart_line_id = ' || v_cart_line_id || ' and distribution_id = ' || v_distribution_id;

Line: 496

     l_insert_sql := l_insert_sql || ' where cart_id = :cart_id and cart_line_id = :cart_line_id and distribution_id = :distribution_id ';

Line: 501

     dbms_sql.parse( v_cursor_id, l_insert_sql, DBMS_SQL.native);

Line: 512

     dbms_sql.bind_variable(v_cursor_id, ':last_updated_by', l_shopper_id );

Line: 513

     dbms_sql.bind_variable(v_cursor_id, ':last_update_date', sysdate );

Line: 514

     dbms_sql.bind_variable(v_cursor_id, ':last_update_login', l_shopper_id );

Line: 521

     dbms_sql.parse(l_call,l_insert_sql ,dbms_sql.native);

Line: 536

     update icx_cart_line_distributions
     set ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
     VARIANCE_ACCOUNT_ID = v_variance_acct_id,
     BUDGET_ACCOUNT_ID = v_budget_acct_id
     where CART_LINE_ID = v_cart_line_id
     and CART_ID = v_cart_id
     and DISTRIBUTION_ID = v_distribution_id;

Line: 552

     select substr(l_error_message,12,512) into l_err_mesg from dual;

Line: 751

/* Find Account Id based on concatenated segments passin and update the account
 * distribution tables based on account id found.
 * Pass in distribution id to update existing account, and insert a new row
 * if distiribution id is not passed.*/
/* NOTE: this is used when no segments are turned on in AK for display  or
   update, so only update the charge account id and charge account num */
PROCEDURE update_account_num(v_cart_id IN NUMBER,
			 v_cart_line_id IN NUMBER,
                         v_oo_id IN NUMBER,
			 v_account_num IN VARCHAR2,
			 v_distribution_id IN NUMBER default NULL,
			 v_line_number IN NUMBER default NULL,
			 v_allocation_type IN VARCHAR2 default NULL,
			 v_allocation_value IN NUMBER default NULL,
			 v_validate_flag IN VARCHAR2 default 'Y') is

 v_error_message varchar(1000);

Line: 778

         select max(distribution_num)
      from icx_cart_line_distributions
      where cart_id = cartid
      and cart_line_id = cartline_id
      and nvl(org_id, -9999) = nvl(oo_id,-9999);

Line: 785

      SELECT CHART_OF_ACCOUNTS_ID
      FROM gl_sets_of_books,
           financials_system_parameters fsp
      WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;

Line: 791

	 select cart_line_number
      from icx_shopping_cart_lines
      where cart_id = cartid
      and cart_line_id = cartline_id;

Line: 852

      any segment in the insert_row or update_row procedure */
    v_n_segments := 0;

Line: 855

         insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);

Line: 858

         update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);

Line: 878

/* Find Account Id based on table of segments passin and update the account
 * distribution tables based on account id found
 * Pass in distribution id to update existing account, and insert a new row
 * if distiribution id is not passed.*/
PROCEDURE update_account(v_cart_id IN NUMBER,
			 v_cart_line_id IN NUMBER,
                         v_oo_id IN NUMBER,
			 v_segments IN fnd_flex_ext.SegmentArray,
			 v_distribution_id IN NUMBER default NULL,
			 v_line_number IN NUMBER default NULL,
			 v_allocation_type IN VARCHAR2 default NULL,
			 v_allocation_value IN NUMBER default NULL,
                         v_validate_flag IN VARCHAR2 default 'Y') is

 v_error_message varchar(1000);

Line: 903

         select max(distribution_num)
      from icx_cart_line_distributions
      where cart_id = cartid
      and cart_line_id = cartline_id
      and nvl(org_id, -9999) = nvl(oo_id,-9999);

Line: 910

      SELECT CHART_OF_ACCOUNTS_ID
      FROM gl_sets_of_books,
           financials_system_parameters fsp
      WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;

Line: 916

	select cart_line_number
	from icx_shopping_cart_lines
	where cart_id = cartid
	and cart_line_id = cartline_id;

Line: 963

         insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);

Line: 966

         update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);

Line: 1018

        SELECT  default_code_combination_id employee_default_account_id
        from hr_employees_current_v
        where employee_id = v_emp_id;

Line: 1023

     select msi.expense_account
     from mtl_system_items msi,
     icx_shopping_carts isc,
     icx_shopping_cart_lines iscl
     where msi.inventory_item_id(+) = iscl.item_id
       AND     nvl(msi.ORGANIZATION_ID,
                        nvl(isc.DESTINATION_ORGANIZATION_ID,
                            iscl.DESTINATION_ORGANIZATION_ID)) =
                    nvl(isc.DESTINATION_ORGANIZATION_ID,
                        iscl.DESTINATION_ORGANIZATION_ID)
     and iscl.cart_id = isc.cart_id
     and iscl.cart_id = v_cart_id
     and iscl.cart_line_id = v_cart_line_id
     and nvl(isc.org_id,-9999) = nvl(v_oo_id,-9999)
     and nvl(iscl.org_id,-9999) = nvl(v_oo_id,-9999);

Line: 1040

      SELECT CHART_OF_ACCOUNTS_ID
      FROM gl_sets_of_books,
           financials_system_parameters fsp
      WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;

Line: 1046

	select cart_line_number
      from icx_shopping_cart_lines
      where cart_id = v_cart_id
      and cart_line_id = v_cart_line_id
      and nvl(org_id, -9999) = nvl(v_oo_id,-9999);

Line: 1089

          select count(*) into v_exist
          from gl_sets_of_books gsb,
               financials_system_parameters fsp,
               gl_code_combinations gl
          where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
          and   gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
          and   gl.CODE_COMBINATION_ID = v_account_id;

Line: 1137

    insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num);

Line: 1175

        SELECT  default_code_combination_id employee_default_account_id
        from hr_employees_current_v
        where employee_id = v_emp_id;

Line: 1180

     select msi.expense_account
     from mtl_system_items msi,
     icx_shopping_carts isc,
     icx_shopping_cart_lines iscl
     where msi.inventory_item_id(+) = iscl.item_id
       AND     nvl(msi.ORGANIZATION_ID,
                        nvl(isc.DESTINATION_ORGANIZATION_ID,
                            iscl.DESTINATION_ORGANIZATION_ID)) =
                    nvl(isc.DESTINATION_ORGANIZATION_ID,
                        iscl.DESTINATION_ORGANIZATION_ID)
     and iscl.cart_id = isc.cart_id
     and iscl.cart_id = v_cart_id
     and iscl.cart_line_id = v_cart_line_id
     and nvl(isc.org_id,-9999) = nvl(v_oo_id,-9999)
     and nvl(iscl.org_id,-9999) = nvl(v_oo_id,-9999);

Line: 1198

      SELECT CHART_OF_ACCOUNTS_ID
      FROM gl_sets_of_books,
           financials_system_parameters fsp
      WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;

Line: 1204

	select cart_line_number
      from icx_shopping_cart_lines
      where cart_id = v_cart_id
      and cart_line_id = v_cart_line_id;

Line: 1298

PROCEDURE update_account_by_id(v_cart_id IN NUMBER,
			       v_cart_line_id IN NUMBER,
			       v_oo_id IN NUMBER,
                               v_distribution_id IN NUMBER,
			       v_line_number IN NUMBER) is

 v_segments fnd_flex_ext.SegmentArray;

Line: 1311

 SELECT CHART_OF_ACCOUNTS_ID
 FROM gl_sets_of_books,
      financials_system_parameters fsp
 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;

Line: 1321

   select charge_account_id
 from icx_cart_line_distributions
 where cart_id = cartid
 and cart_line_id = cartline_id
 and distribution_id = dist_id
 and nvl(org_id,-9999) = nvl(oo_id,-9999);

Line: 1329

    select cart_line_number
    from icx_shopping_cart_lines
    where cart_id = cartid
    and cart_line_id = cartline_id;

Line: 1368

       update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,
  			        v_line_number,v_account_id,v_n_segments,v_segments,
				v_account_num);