Upload Meter Reading in Bulk (Enterprise Assets Management)

Place the control file and csv to the /home/oracle/mtr

then run the request "DCS Meter Interface*" integrated in last step which will upload the data provided in csv file using sql loader to the temporary table. Then it will call two other requests 

1) Populate Meter Reading Interface
which will populate the data to interface table

2) Install Base - Counter Readings Open Interface
which will populate the standard table with the data.

Control File

LOAD DATA
        INFILE 'meter_reading.csv'
INSERT
        INTO TABLE dev_ctr_meter_readings
        REPLACE FIELDS TERMINATED BY ','
      TRAILING NULLCOLS
(
   COUNTER_INTERFACE_ID RECNUM,
   BATCH_NAME CONSTANT "XULFI",
   SOURCE_TRANSACTION_DATE,
   PROCESS_STATUS CONSTANT "R",
   VALUE_TIMESTAMP,
   COUNTER_READING,
   SOURCE_TRANSACTION_TYPE_ID CONSTANT 88,
   SOURCE_TRANSACTION_ID CONSTANT 1,
   COUNTER_NAME   
)

Format

COUNTER_NAME SOURCE_TRANSACTION_DATE VALUE_TIMESTAMP COUNTER_READING TEMP RHM-P19-LQ 2-Apr-13 2-Apr-13 120 1

Scripts

-----------------------Create Table ----------------------------

CREATE TABLE DEV_CTR_METER_READINGS
(
  COUNTER_INTERFACE_ID            NUMBER                  NOT NULL,
  BATCH_NAME                             VARCHAR2(30 BYTE) NOT NULL,
  SOURCE_TRANSACTION_DATE     DATE                       NOT NULL,
  PROCESS_STATUS                       VARCHAR2(1 BYTE),
  VALUE_TIMESTAMP                      DATE                      NOT NULL,
  COUNTER_READING                     NUMBER,
  SOURCE_TRANSACTION_TYPE_ID  NUMBER                 NOT NULL,
  SOURCE_TRANSACTION_ID           NUMBER                 NOT NULL,
  COUNTER_NAME                           VARCHAR2(50 BYTE)
)

--------------------Create Prcedure--------------------------

CREATE OR REPLACE PROCEDURE apps.dev_crt_meter_readings (
   errbuf    OUT      VARCHAR2,
   retcode   OUT      VARCHAR2,
   p_user    IN       NUMBER
)
IS
   l_meter_id   NUMBER;

   CURSOR c1
   IS
      SELECT   counter_interface_id, batch_name, source_transaction_date,
               process_status, value_timestamp, counter_reading,
               source_transaction_type_id, source_transaction_id,
               counter_name
          FROM dev_ctr_meter_readings
      ORDER BY counter_interface_id;

   cur          c1%ROWTYPE;
BEGIN
   DELETE FROM csi_ctr_readings_interface;

   COMMIT;

   OPEN c1;

   LOOP
      FETCH c1
       INTO cur;

      EXIT WHEN c1%NOTFOUND;

      BEGIN
         l_meter_id := NULL;

         SELECT meter_id
           INTO l_meter_id
           FROM eam_counters_v
          WHERE meter_name = cur.counter_name;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      IF l_meter_id IS NOT NULL
      THEN
         INSERT INTO csi_ctr_readings_interface
                     (counter_interface_id, batch_name,
                      source_transaction_date, process_status,
                      counter_id, counter_value_id,
                      value_timestamp, counter_reading, last_update_date,
                      last_updated_by, creation_date, created_by,
                      last_update_login, source_transaction_type_id,
                      source_transaction_id, counter_name
                     )
              VALUES (cur.counter_interface_id, cur.batch_name,
                      cur.source_transaction_date, cur.process_status,
                      l_meter_id, csi_counter_readings_s.NEXTVAL,
                      cur.value_timestamp, cur.counter_reading, SYSDATE,
                      p_user, SYSDATE, p_user,
                      0, cur.source_transaction_type_id,
                      cur.source_transaction_id, cur.counter_name
                     );
      END IF;
   END LOOP;

   CLOSE c1;

   COMMIT;
END dev_crt_meter_readings;
/







 

 Shell Script

#!/bin/bash
NOW=$(date +"%d-%b-%Y")
u1=$5
. /opt/TEST/oracle/apps/apps_st/appl/APPSTEST_testing.env
cd /home/oracle/mtr
data_file=$(ls *.csv)
ctl_file=$(ls *.ctl)
sqlldr apps/**** control=$ctl_file data=$data_file
if [ $? -ne 0 ]
then
echo " Error Occured "
exit 1
else mv $data_file /home/oracle/mtr/bkup
echo "Upload Successful"
echo "Moved File: " $data_file
echo "Successfully to Backup Folder "

cd $FND_TOP/bin

CONCSUB APPS/**** EAM "Enterprise Asset Management" AFF.EAM WAIT=N CONCURRENT EAM dev_crt_meter_readings PROGRAM_NAME='"Populate Meter Readings Interface"' $u1

CONCSUB APPS/**** EAM "Enterprise Asset Management" AFF.EAM WAIT=N CONCURRENT CSI CSICTRML PROGRAM_NAME='"Install Base - Counter Readings Open Interface"' XULFI $NOW $NOW Y NULL

exit 0
fi

Save Shell script as RUNREQUEST.prog and integrate as concurrent request