APPS.ICX_POR_JOB_TRACK_VALIDATION_S SQL Statements

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

Line: 11

procedure update_job_status(p_jobno in number,
                            p_new_status in varchar2,
                            p_loaded_items in number,
                            p_failed_items in number) is
  l_progress varchar2(10) := '000';

Line: 18

  update icx_por_batch_jobs
  set    job_status = p_new_status,
         start_datetime = decode(p_new_status, 'RUNNING', sysdate, start_datetime),
         items_loaded = p_loaded_items,
         items_failed = p_failed_items
  where  job_number = p_jobno;

Line: 28

      Debug('[update_job_status-'||l_progress||'] '||SQLERRM);

Line: 30

            (-20000, 'Exception at icx_por_job_track_validation_s.update_job_status(ErrLoc = ' || l_progress ||') ' ||
             'SQL Error : ' || SQLERRM);

Line: 38

  update icx_por_batch_jobs
  set    job_status = decode(items_failed, 0, 'COMPLETED', 'COMPLETED W/ERRORS'),
         completion_datetime = sysdate
  where  job_number = p_jobno;

Line: 57

  update icx_por_batch_jobs
  set    job_status = 'FAILED',
         completion_datetime = sysdate,
         failure_message = p_error_message
  where  job_number = p_jobno;

Line: 77

procedure InsertError(p_jobno in out number,
                      p_descriptor_key in varchar2,
                      p_message_name in varchar2,
                      p_line_number in number
 ) is
  l_progress varchar2(10) := '000';

Line: 87

    select icx_por_batch_jobs_s.nextval
    into   p_jobno
    from   sys.dual;

Line: 93

  insert into icx_por_failed_line_messages (
    job_number,
    descriptor_key,
    message_name,
    line_number
  ) values (
    p_jobno,
    p_descriptor_key,
    p_message_name,
    p_line_number
  );

Line: 108

    Debug('[InsertError-'||l_progress||'] '||SQLERRM);

Line: 110

            (-20000, 'Exception at icx_por_job_track_validation_s.InsertError(ErrLoc = ' || l_progress ||') ' ||
             'SQL Error : ' || SQLERRM);

Line: 147

      InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_SUPPLIER_REQD',
                  p_line_number);

Line: 154

        InsertError(p_jobno, 'SUPPLIER','ICX_POR_DIFFERENT_SUPPLIER',
                    p_line_number);

Line: 162

          select vendor_id
          into   l_supplier_id
          from   po_vendors
          where  vendor_name = p_supplier
          and    rownum = 1;

Line: 171

            InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_INVALID_SUPPLIER',
                        p_line_number);

Line: 183

      InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_SUPPLIER_PART_REQD',
                  p_line_number);

Line: 185

    elsif (p_action in ('ADD','UPDATE')) then
      begin
        l_progress := '024';

Line: 188

        select 'Y'
        into   l_foo
        from   icx_por_items
        where  a3 = p_supplier_part_num
        and    a1 = p_supplier
        and    rownum = 1;

Line: 198

          InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_DUP_SUPPLIER_PART',
                      p_line_number);

Line: 205

          InsertError(p_jobno, 'SUPPLIER_PART_NUM',
                      'ICX_POR_MAX_SUP_PART_LEN', p_line_number);

Line: 213

          if (p_action = 'UPDATE') then
            l_valid := false;

Line: 215

            InsertError(p_jobno, 'SUPPLIER_PART_NUM',
                        'ICX_POR_PRC_INVALID_SUP_PART', p_line_number);

Line: 223

    if ( p_action in ('ADD', 'UPDATE') AND
	 length(p_supplier_part_num) > 25 ) then
          l_valid := false;

Line: 226

          InsertError(p_jobno, 'SUPPLIER_PART_NUM',
                      'ICX_POR_MAX_SUP_PART_LEN', p_line_number);

Line: 234

      InsertError(p_jobno, 'DESCRIPTION', 'ICX_POR_INVALID_DESCRIPTION',
                  p_line_number);

Line: 246

        select 'Y'
        into   l_foo
        from   icx_unspsc_codes
        where  unspsc_code = p_unspsc
        and    rownum = 1;

Line: 255

          InsertError(p_jobno, 'UNSPSC', 'ICX_POR_INVALID_UNSPSC',
                      p_line_number);

Line: 266

        InsertError(p_jobno, 'LEAD_TIME', 'ICX_POR_INVALID_LEAD_TIME',
                    p_line_number);

Line: 275

        select 'Y'
        into   l_foo
        from   fnd_lookups
        where  lookup_type = 'ICX_CATALOG_AVAILABILITY'
        and    lookup_code = p_availability
        and    rownum = 1;

Line: 285

          InsertError(p_jobno, 'AVAILABILITY', 'ICX_POR_INVALID_AVAILABILITY',
                      p_line_number);

Line: 294

        select 'Y'
        into   l_foo
        from   fnd_lookups
        where  lookup_type = 'ICX_CATALOG_ITEM_TYPE'
        and    lookup_code = p_item_type
        and    rownum = 1;

Line: 304

          InsertError(p_jobno, 'ITEM_TYPE', 'ICX_POR_INVALID_ITEM_TYPE',
                      p_line_number);

Line: 319

      InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_PRC_SUPPLIER_REQD',
                  p_line_number);

Line: 324

        InsertError(p_jobno, 'SUPPLIER','ICX_POR_PRC_DIFF_SUPPLIER',
                    p_line_number);

Line: 332

          select vendor_id
          into   l_supplier_id
          from   po_vendors
          where  vendor_name = p_supplier
          and    rownum = 1;

Line: 341

            InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_PRC_INVALID_SUPPLIER',
                        p_line_number);

Line: 354

      InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_PRC_SUP_PART_REQD',
                  p_line_number);

Line: 359

        select 'Y'
        into   l_foo
        from   icx_por_items
        where  a3 = p_supplier_part_num
        and    a1 = p_supplier
        and    rownum = 1;

Line: 372

          InsertError(p_jobno, 'SUPPLIER_PART_NUM',
                      'ICX_POR_PRC_INVALID_SUP_PART',
                      p_line_number);

Line: 391

      InsertError(p_jobno, 'BUYER', 'ICX_POR_BUYER_REQD',
                  p_line_number);

Line: 402

          select organization_id
          into   l_buyer_id
          from    hr_all_organization_units
          /*where   organization_id = l_buyer_id*/
          where   name = p_buyer
          and     business_group_id = p_business_group_id
          and     rownum = 1;

Line: 415

          InsertError(p_jobno, 'BUYER', 'ICX_POR_INVALID_BUYER',
                      p_line_number);

Line: 426

          select 'Y'
          into l_foo
          from po_vendor_sites_all
          where vendor_id = l_supplier_id
          and vendor_site_code = p_supplier_site
          and rownum = 1;

Line: 437

            InsertError(p_jobno, 'SUPPLIER_SITE','ICX_POR_INVALID_SUPP_SITE',
                        p_line_number);

Line: 444

          select 'Y'
          into l_foo
          from po_vendor_sites_all
          where vendor_id = l_supplier_id
          and vendor_site_code = p_supplier_site
          and org_id = l_buyer_id
          and rownum = 1;

Line: 456

            InsertError(p_jobno, 'SUPPLIER_SITE','ICX_POR_INVALID_SUPP_SITE',
                        p_line_number);

Line: 466

      InsertError(p_jobno, 'UOM', 'ICX_POR_UOM_REQD',
                  p_line_number);

Line: 474

        select 'Y'
        into   l_foo
        from   mtl_units_of_measure
        where  uom_code = p_uom
        and    rownum = 1;

Line: 483

          InsertError(p_jobno, 'UOM', 'ICX_POR_INVALID_UOM',
                      p_line_number);

Line: 493

      InsertError(p_jobno, 'PRICE', 'ICX_POR_PRICE_REQD',
                  p_line_number);

Line: 500

      InsertError(p_jobno, 'PRICE', 'ICX_POR_INVALID_PRICE',
                  p_line_number);

Line: 509

      InsertError(p_jobno, 'CURRENCY_CODE', 'ICX_POR_CURRENCY_REQD',
                  p_line_number);

Line: 514

        select 'Y'
        into   l_foo
        from   fnd_currencies
        where  currency_code = p_currency_code
        and    rownum = 1;

Line: 523

          InsertError(p_jobno, 'CURRENCY_CODE', 'ICX_POR_INVALID_CURRENCY',
                      p_line_number);

Line: 560

    select min(job_number)
    into p_jobno
    from icx_por_batch_jobs
    where job_status='PENDING'
    and host_ip_address = p_host_ip_address;

Line: 572

    select jb.exchange_file_name,
           jb.supplier_id,
           /*pt.vendor_name,*/
           'DEFAULT_SUPPLIER_NAME',
           jb.exchange_operator_name
    into p_exchange_file_name,
         p_supplier_id,
         p_supplier_name,
         p_exchange_operator
    /* from po_vendors pt, */
    from icx_por_batch_jobs jb
    where job_number = p_jobno;

Line: 586

    update icx_por_batch_jobs
    set job_status = 'RUNNING',
        start_datetime = sysdate
    where  job_number = p_jobno;

Line: 612

  select icx_por_batch_jobs_s.nextval
  into   l_jobno
  from   sys.dual;

Line: 617

  insert into icx_por_batch_jobs (
    job_number,
    supplier_id,
    supplier_file_name,
    exchange_file_name,
    items_loaded,
    items_failed,
    job_status,
    submission_datetime,
    start_datetime,
    completion_datetime,
    failure_message,
    host_ip_address,
    exchange_operator_name)
  values (
    l_jobno,
    p_supplier_id,
    p_supplier_file,
    p_exchange_file,
    0,
    0,
    'PENDING',
    sysdate,
    null,
    null,
    null,
    p_host_ip_address,
    p_exchange_operator
  );