APPS.CSI_ML_PROGRAM_PUB SQL Statements

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

Line: 25

    SELECT csii.inst_interface_id,
           csii.instance_id,
           csii.quantity ,
           csii.location_id ,
           csii.location_type_code
    FROM   csi_instance_interface csii
    WHERE  process_status = 'R'
    AND    source_system_name = nvl(p_source_system_name,source_system_name)
    AND    EXISTS ( SELECT 1 FROM csi_i_asset_interface csiai
                    WHERE  csiai.inst_interface_id = csii.inst_interface_id )
    AND    syncup_family IS NULL;

Line: 47

    UPDATE csi_instance_interface
    SET    syncup_family = NULL
    WHERE source_system_name = nvl(p_source_system_name,source_system_name)
    AND   syncup_family is not null
    AND   process_status = 'R';

Line: 121

              UPDATE csi_i_asset_interface
              SET    fa_sync_flag     =  'N'
              WHERE inst_interface_id =  l_inst_intr_tbl(i_asst)
              AND   fa_sync_flag      =  'Y' ;

Line: 133

         UPDATE csi_instance_interface csii
         SET syncup_family       =  l_syncup_family_seq,
             process_status      =  NVL(l_process_status ,csii.process_status),
             error_text          =  DECODE(l_process_status,'E',l_Asset_Error_text,
                                           error_text)
         WHERE inst_interface_id = l_inst_intr_tbl(i_asst);

Line: 141

      l_inst_intr_tbl.delete;

Line: 142

      l_instance_sync_tbl.delete;

Line: 143

      l_instance_asset_sync_tbl.delete;

Line: 144

      l_fa_asset_sync_tbl.delete;

Line: 179

SELECT inst_interface_id
  FROM csi_instance_interface
 WHERE process_status='P'
  AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 194

    UPDATE csi_instance_interface cii
    SET process_status = 'R'
    WHERE  (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
          OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
    AND   cii.source_system_name = p_source_system_name
    AND   cii.process_Status = 'E';

Line: 213

       DELETE FROM CSI_INSTANCE_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 219

       DELETE FROM CSI_I_PARTY_INTERFACE cipi
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 224

        DELETE FROM CSI_I_ASSET_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 229

       DELETE FROM CSI_IEA_VALUE_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 233

       DELETE FROM CSI_II_RELATION_INTERFACE
        WHERE subject_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 238

       DELETE FROM CSI_II_RELATION_INTERFACE
        WHERE object_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 332

  SELECT inst_interface_id,parallel_worker_id
  FROM csi_instance_interface
  WHERE parallel_worker_id = pc_worker_id
  AND process_status = 'R'
  AND source_system_name = nvl(p_source_system_name,source_system_name);

Line: 339

    SELECT distinct parallel_worker_id
    FROM csi_instance_interface
    WHERE process_status = 'R'
    AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328
    AND parallel_worker_id IS NOT NULL
    AND transaction_identifier IS NULL;

Line: 347

   select serial_number
   from csi_instance_interface
   where source_system_name = nvl(p_source_system_name,source_system_name)
   and   serial_number is not null
   and   process_status = 'R'
   group by serial_number
   having count(*) > 1;

Line: 357

    SELECT distinct parallel_worker_id
    FROM csi_ii_relation_interface
    WHERE process_status = 'R'
    AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328
    AND parallel_worker_id IS NOT NULL
    AND transaction_identifier IS NULL;

Line: 380

  l_insert_count NUMBER :=0;

Line: 381

  l_update_count NUMBER :=0;

Line: 385

SELECT inst_interface_id
  FROM csi_instance_interface
 WHERE process_status='P'
 AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 403

   SELECT COUNT(*)
   INTO   l_old_error
   FROM   csi_instance_interface
   WHERE  process_status='E'
   AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 429

       DELETE FROM CSI_INSTANCE_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;

Line: 435

       DELETE FROM CSI_I_PARTY_INTERFACE cipi
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 440

        DELETE FROM CSI_I_ASSET_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 445

       DELETE FROM CSI_IEA_VALUE_INTERFACE
        WHERE inst_interface_id=inst_intf_id_del(i1);

Line: 449

       DELETE FROM CSI_II_RELATION_INTERFACE
        WHERE subject_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;;

Line: 454

       DELETE FROM CSI_II_RELATION_INTERFACE
        WHERE object_interface_id=inst_intf_id_del(i1)
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;;

Line: 496

    l_srl_tbl.DELETE;

Line: 505

	  UPDATE CSI_INSTANCE_INTERFACE
	  set parallel_worker_id = 1
	  where serial_number = l_srl_tbl(j)
	  and   source_system_name = nvl(p_source_system_name,source_system_name)
	  and   process_status = 'R';

Line: 517

    SELECT ceil(count(1)/l_worker_count)
    INTO x_count
    FROM csi_instance_interface
    WHERE trunc(source_transaction_date) BETWEEN
                nvl(l_txn_from_date,trunc(source_transaction_date)) AND
                nvl(l_txn_to_date,trunc(source_transaction_date))
    AND transaction_identifier IS NULL
    AND process_status = 'R'
    AND source_system_name = nvl(p_source_system_name,source_system_name)
    AND parallel_worker_id = -1;

Line: 533

      UPDATE csi_instance_interface
      SET parallel_worker_id = l_count
      WHERE rownum <= x_count
      AND parallel_worker_id = -1
      AND source_system_name = nvl(p_source_system_name,source_system_name) --Added for bug 3621991
      AND process_status = 'R'; --Added for bug 3621991

Line: 542

      l_inst_id_tbl.delete;

Line: 639

           SELECT COUNT(*)
             INTO l_rel_count
             FROM csi_ii_relation_interface
            WHERE process_status = 'R'
            AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328;

Line: 663

          SELECT ceil(count(1)/l_r_worker_count)
            INTO x_r_count
            FROM csi_ii_relation_interface
           WHERE trunc(source_transaction_date) BETWEEN
                 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
                 nvl(l_txn_to_date,trunc(source_transaction_date))
             AND transaction_identifier IS NULL
             AND process_status = 'R'
             AND source_system_name = nvl(p_source_system_name,source_system_name)
             AND parallel_worker_id = -1;

Line: 676

             UPDATE csi_ii_relation_interface
                SET parallel_worker_id = l_r_count
              WHERE ROWNUM <= x_r_count
                AND parallel_worker_id = -1
                AND source_system_name = nvl(p_source_system_name,source_system_name)
                AND process_status = 'R';

Line: 780

       SELECT COUNT(*)
         INTO l_rel_count
         FROM csi_ii_relation_interface
        WHERE process_status = 'R'
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 794

       SELECT COUNT(*)
         INTO l_valid_count
         FROM csi_ii_relation_interface
        WHERE process_status = 'V'
         AND source_system_name = nvl(p_source_system_name,source_system_name);	--included for bug5949328

Line: 809

       SELECT COUNT(*)
         INTO l_update_count
         FROM csi_ii_relation_interface
        WHERE process_status = 'U'
        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 818

        l_update_count :=0;

Line: 826

       SELECT COUNT(*)
         INTO l_insert_count
         FROM csi_ii_relation_interface
        WHERE process_status = 'I'
         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 835

        l_insert_count :=0;

Line: 838

      FND_File.Put_Line(Fnd_File.LOG,'Value of l_insert_count is: '||l_insert_count);

Line: 843

    AND l_update_count = 0
    AND l_insert_count = 0 )
 THEN
    IF(l_debug_level>1) THEN
    FND_File.Put_Line(Fnd_File.LOG,'Values for l_rel_count l_valid_count l_update_count and l_insert_count are zero');

Line: 853

      SELECT COUNT(*)
      INTO   l_new_error
      FROM   csi_instance_interface
      WHERE  process_status='E'
       AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 916

      SELECT ceil(count(1)/l_r_worker_count)
        INTO x_r_count
        FROM csi_ii_relation_interface
       WHERE trunc(source_transaction_date) BETWEEN
             nvl(l_txn_from_date,trunc(source_transaction_date)) AND
             nvl(l_txn_to_date,trunc(source_transaction_date))
        AND transaction_identifier IS NULL
        AND process_status = 'R'
        AND source_system_name = nvl(p_source_system_name,source_system_name)
        AND parallel_worker_id = -1;

Line: 935

        UPDATE csi_ii_relation_interface
           SET parallel_worker_id = l_r_count
         WHERE ROWNUM <= x_r_count
           AND parallel_worker_id = -1
           AND source_system_name = nvl(p_source_system_name,source_system_name)
           AND process_status = 'R';

Line: 1018

       SELECT COUNT(DISTINCT(parallel_worker_id))
         INTO l_r_worker_count
         FROM csi_ii_relation_interface
        WHERE process_status='V'
         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328

Line: 1028

        FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1036

                             ,'UPDATE'
                             ,l_count
                             ,p_txn_from_date
                             ,p_txn_to_date
                             ,p_source_system_name
                             );

Line: 1043

        FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1067

   ELSIF l_update_count <> 0
   THEN
       SELECT COUNT(DISTINCT(parallel_worker_id))
         INTO l_r_worker_count
         FROM csi_ii_relation_interface
        WHERE process_status='U'
         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;

Line: 1079

        FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_update_count <> 0: ');

Line: 1080

        FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1088

                          ,'RE-UPDATE'
                          ,l_count
                          ,p_txn_from_date
                          ,p_txn_to_date
                          ,p_source_system_name
                          );

Line: 1095

        FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1112

        req_data:=to_char(l_update_count);

Line: 1119

   ELSIF l_insert_count <> 0
   THEN
        SELECT COUNT(DISTINCT(parallel_worker_id))
          INTO l_r_worker_count
          FROM csi_ii_relation_interface
         WHERE process_status='I'
         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;

Line: 1131

        FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_insert_count <> 0: ');

Line: 1132

        FND_File.Put_Line(Fnd_File.LOG,'Start time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1140

                            ,'INSERT'
                            ,l_count
                            ,p_txn_from_date
                            ,p_txn_to_date
                            ,p_source_system_name
                            );

Line: 1147

        FND_File.Put_Line(Fnd_File.LOG,'End time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));

Line: 1164

        req_data:=to_char(l_insert_count);