UPDATE ITEM DFF (STOCK LOCATOR) VALUE USING FLAT FILE (LIKE .CSV, TXT)

STEPS

1.CREATE A STAGING TABLE

CREATE TABLE TEST_TABLE (
  organization_code VARCHAR2(10)  NOT NULL,
  item_number       VARCHAR2(80)  NOT NULL,
  stock_location    VARCHAR2(50)  NOT NULL,
  creation_date     DATE          DEFAULT sysdate NULL,
  error_message     VARCHAR2(500) NULL,
  status            VARCHAR2(20)  NULL,
  request_id        NUMBER(35,0)  NULL
)

2 WE HAVE DIFFERENT METHOD FOR UPLOADING THE DATA INTO THE STAGING TABLE  LIKE SQL LOADER,Host etc.
I WILL USE THE HOST METHOD TO UPLOAD THE DATA INTO THE STAGING TABLE FROM FLAT FILE (LIKE .CSV, TXT)
FIRST PLACE THE FLAT  FILE TO THE CUSTOM DIRECTORY I CREATED TWO FOLDER AT SERVER FIRST IS TO PLACE MY FLATE FILE (CUSTOM_TOP/process)
ONCE IT IS PROCESSED I WILL MOVE TO SOME OTHER DIRECTORY (CUSTOM_TOP/archive)  FOR BACK UP

NOW

CREATE A (.ctl) FILE

OPTIONS (SKIP=1)
LOAD DATA
APPEND INTO TABLE  "TEST_TABLE"
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 
(
ORGANIZATION_CODE ,
ITEM_NUMBER       ,
STOCK_LOCATION TERMINATED BY WHITESPACE,
CREATION_DATE   sysdate   ,
ERROR_MESSAGE     ,
STATUS constant 'U',
REQUEST_ID
)
-- SAVE YOUR FILE (TEST.ctl) AND PLACE FILE  TO THE CUSTOM_TOP/bin FOLDER ON SERVER

CREATE THE HOST FILE (DON'T GIVE ANY EXTENSTION ) WHICH WILL PULL DATA FROM FLAT FILE AND INSERT INTO  THE STAGING TABLE USING(.CTL) FILE

SOURCE_FILE=`echo $1 | awk '{print $9}'| sed -e "s/\"//g"`              #Source File
SOURCE_DIR="CUSTOM_TOP/process"
DEST_DIR="CUSTOM_TOP/archive"
apps="USER/PWD"
echo "#################################################################################"
DATAFILE=$CUSTOM_TOP/process/$SOURCE_FILE  #
BACKUPFILE=$CUSTOM_TOP/archive/`date +$SOURCE_FILE-%Y%m%d%H%M%S`
CTLFILE=$CUSTOM_TOP/bin/CTL FILE NAME    #TEST.ctl
LOGFILE=$CUSTOM_TOP/log/$SOURCE_FILE.log
BADFILE=$CUSTOM_TOP/log/$SOURCE_FILE.bad
DISFILE=$CUSTOM_TOP/log/$SOURCE_FILE.dis
echo "#################################################################################"
##echo "Parameter9 :"$SOURCE_FILE
##echo "DATAFILE :"$DATAFILE
##echo "BACKUPFILE :"$BACKUPFILE
##echo "CTLFILE :"$CTLFILE
echo "#################################################################################"
sqlload silent=header silent=feedback userid=$USER control=$CTLFILE  data=$DATAFILE  bad=$BADFILE  log=$LOGFILE  discard=$DISFILE
mv "$CUSTOM_TOP/process/$SOURCE_FILE" "$CUSTOM_TOP/archive/`date +$SOURCE_FILE-%Y%m%d%H%M%S`"

--SAVE YOUR FILE (TEST_HOST) AND PLACE FILE  TO THE CUSTOM_TOP/bin FOLDER ON SERVER

Register a program(data_upload) select execution method as host and check  your data is loading into the staging table form flat file or not!!


NOW CREATE THE PROCEDURE TO VAIDATE THE DATA AND THEN UPDATE THE DFF FIELD

CREATE OR REPLACE PROCEDURE UPDATE_DFF(errbuf  OUT      VARCHAR2
                                       ,retcode    OUT      NUMBER
                                       ,p_req_id IN NUMBER)
AS



CURSOR CUR_UPDATE_DFF
IS
SELECT a.ORGANIZATION_CODE
,a.ITEM_NUMBER
,a.STOCK_LOCATION
,(SELECT ORGANIZATION_ID
 FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE=a.ORGANIZATION_CODE) ORGANIZATION_ID
,a.STATUS
,a.REQUEST_ID
,a.ERROR_MESSAGE
FROM TEST_TABLE a;

v_ErrorText VARCHAR2(200):=SQLERRM;
v_error_code NUMBER:=SQLCODE ;
vattribute1  MTL_SYSTEM_ITEMS_B.attribute1%TYPE  ;

p_update EXCEPTION;   --define exception

v_ORGANIZATION_ID NUMBER(10);
V_ITEM_NUMBER VARCHAR2(40);
v_ORGANIZATION_code NUMBER(10);
v_error_status VARCHAR2(40);

v_request_id2 NUMBER:=p_req_id;

rec CUR_UPDATE_DFF%ROWTYPE;

BEGIN -- print the data into the output file

  fnd_file.put_line (fnd_file.output,'            TEST TO CHECK Update Item DFF (Stock Locator)               ');
  fnd_file.put_line (fnd_file.output,'');
  fnd_file.put_line (fnd_file.output,'Run Date          : '|| TO_CHAR(SYSDATE,'DD-MON-RRRR HH:MI:SS'));
  fnd_file.put_line (fnd_file.output,'');
  fnd_file.put_line (fnd_file.output,'-----------------------------------------------------------------------------------------------------------------------------------------------------------------');
  fnd_file.put_line (fnd_file.output,'ORG_CODE'||'        '||'STOCK_LOCATION'||'                 '||'ITEM_NUMBER                   '||'                 '||'ERROR_MESSAGE');
  fnd_file.put_line (fnd_file.output,'-----------------------------------------------------------------------------------------------------------------------------------------------------------------');

  for rec in CUR_UPDATE_DFF
LOOP
  IF v_request_id2= rec.REQUEST_ID THEN --check the request id 
    BEGIN
                BEGIN
                     SELECT ORGANIZATION_CODE INTO v_ORGANIZATION_CODE
                     FROM   ORG_ORGANIZATION_DEFINITIONS
                     WHERE ORGANIZATION_CODE=rec.ORGANIZATION_CODE;
                     EXCEPTION
                       WHEN No_Data_Found THEN
                        fnd_file.put_line (fnd_file.LOG,rec.ORGANIZATION_CODE||' - '||'Organization Code does not exist');
                END;

                BEGIN
                     SELECT SEGMENT1|| '-' ||SEGMENT2|| '-' ||SEGMENT3,organization_id ,
                    attribute1 INTO V_ITEM_NUMBER,v_ORGANIZATION_ID,vattribute1
                     FROM MTL_SYSTEM_ITEMS_B
                     WHERE  organization_id=rec.ORGANIZATION_ID
                     AND SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3 =rec.ITEM_NUMBER;
                     EXCEPTION
                       WHEN No_Data_Found THEN
                      fnd_file.put_line (fnd_file.LOG,rec.ITEM_NUMBER ||' - ' ||'Item Number combination does not exist');
                END;

      IF (v_organization_id =rec.organization_id AND  V_ITEM_NUMBER=rec.ITEM_NUMBER )

     THEN
     IF  vattribute1=rec.STOCK_LOCATION
     THEN
       fnd_file.put_line (fnd_file.Log,'Duplicate record found!!!!'||rec.STOCK_LOCATION||'-'||'for the Item -'||rec.ITEM_NUMBER);
       END IF ;

     BEGIN
      UPDATE mtl_system_items_b
        SET attribute1=rec.STOCK_LOCATION
          WHERE organization_id=v_ORGANIZATION_ID
             AND SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3 =rec.ITEM_NUMBER;

              COMMIT;


      END;

      BEGIN

       UPDATE TEST_TABLE
      SET status='P' ,    --Process record
      ERROR_MESSAGE='Completed Succesfully'
      WHERE REQUEST_ID = v_request_id2
      AND ORGANIZATION_CODE=rec.ORGANIZATION_CODE AND ITEM_NUMBER=rec.ITEM_NUMBER;
      COMMIT;
      END;

      ELSE

        RAISE p_update;

     END IF;
     fnd_file.put_line (fnd_file.output,rec.ORGANIZATION_CODE||'             '||rec.STOCK_LOCATION||'                    '||rec.ITEM_NUMBER               ||'                    '||'Completed Succesfully');
     EXCEPTION
     WHEN p_update THEN
 UPDATE TEST_TABLE
       SET ERROR_MESSAGE='Organization Code OR Item Number combination does not exist',status='E'  --Exception
       WHERE ORGANIZATION_CODE NOT IN (SELECT ORGANIZATION_CODE  FROM   ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE=rec.ORGANIZATION_CODE )
       AND ITEM_NUMBER NOT IN (SELECT SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3  FROM mtl_system_items_b)
       AND REQUEST_ID = v_request_id2 ;
       COMMIT;
       fnd_file.put_line (fnd_file.output,rec.ORGANIZATION_CODE||'             '||rec.STOCK_LOCATION||'                    '||rec.ITEM_NUMBER               ||'                    '||'Organization Code OR Item Number combination does not exist');
    END;
 END IF;

 BEGIN
       UPDATE TEST_TABLE
       SET ERROR_MESSAGE='Organization Code OR Item Number combination does not exist',status='E'
       WHERE  REQUEST_ID = v_request_id2
       AND status='U';
       COMMIT  ;
 END;

end loop;

END UPDATE_DFF;

REGISTER A CONCURRENT PROGRAM (UPDATE_DFF)  FOR THE UPPER PROCEDURE AND CHECK WHETHER ITS UPDATING DFF FIELD OR NOT

NOW I CREATE ONE PACKAGE WHICH WILL SUBMIT BOTH THE PROGRAM SEQUENTIALLY
1.FOR INSERTING DATA INTO STAGING TABLE
 2.UPDATE DFF VALUE

--PACKAGE SPECIFICATION
CREATE OR REPLACE package UPDATE_DFF_PKG
 as
 procedure UPLOADATA_UPDATE_DFF(errbuf       OUT      VARCHAR2
                                ,retcode      OUT      NUMBER
                               ,p_file in VARCHAR2);


 END UPDATE_DFF_PKG;

 -- PACKAGE BODY 

 CREATE OR REPLACE package body UPDATE_DFF_PKG
  as
procedure UPLOADATA_UPDATE_DFF(errbuf       OUT      VARCHAR2
                               ,retcode      OUT      NUMBER
                              ,p_file in varchar2)
 AS

     v_request_id    NUMBER :=0;
       v_request_id1   NUMBER :=0;
     v_record_id     NUMBER;
     v_req_status   BOOLEAN;
     v_phase        VARCHAR2 (200);
     v_status       VARCHAR2 (200);
     v_dev_phase    VARCHAR2 (200);
     v_dev_status   VARCHAR2 (200);
     v_message      VARCHAR2 (500);
     v_file VARCHAR2(30):=p_file;

BEGIN

     BEGIN                                                       
        v_request_id := fnd_request.submit_request ('CUSTOM'        -- APPPLICATION
                                                ,'data_upload'   -- PROGRAM
                                                ,NULL                       -- DESCRIPTION
                                                ,NULL                       -- START TIME
                                                ,FALSE
                                                ,v_file                      -- File Name(Source File)
                                                  );
        COMMIT;
        fnd_file.put_line (fnd_file.LOG, 'loading data into staging table successfully');
        -- +--------+
        IF v_request_id > 0
           THEN
        -- +--------+
            BEGIN
              LOOP
          -- +--------+
              v_req_status :=
                 fnd_concurrent.get_request_status

                                                 (request_id          => v_request_id,
                                                  appl_shortname      => 'data_upload',
                                                  program             => 'data_upload',
                                                  phase               => v_phase,
                                                  status              => v_status,
                                                  dev_phase           => v_dev_phase,
                                                  dev_status          => v_dev_status,
                                                  MESSAGE             => v_message
                                                 );

              IF UPPER (v_dev_phase) = 'COMPLETE'
              THEN
              EXIT;
              END IF;
              END LOOP;
           -- +--------+
                 EXCEPTION
           WHEN OTHERS
           THEN
              fnd_file.put_line
                 (fnd_file.LOG,
                     'Error while checking "loading data into  staging table " status '
                  || SQLERRM
                 );
            END;
        -- +--------+
        END IF;
        -- +--------+

          fnd_file.put_line (fnd_file.LOG,
                           '"loading data into staging table" status : '
                        || v_dev_phase
                        || ' '
                        || v_dev_status
                       );
      COMMIT;

     EXCEPTION
     WHEN OTHERS
     THEN
        fnd_file.put_line (fnd_file.LOG,'Unexpected Error occured while running "loading data into staging table" *****  '|| ' - '|| SQLERRM);
    END;

    BEGIN
    UPDATE TEST_TABLE
    SET REQUEST_ID=v_request_id
    WHERE REQUEST_ID IS NULL;
      COMMIT;
    END;


    BEGIN
      v_request_id1 := FND_REQUEST.SUBMIT_REQUEST('CUSTOM'        -- APPPLICATION
                                                ,'UPDATE_DFF'   -- PROGRAM
                                                ,NULL                       -- DESCRIPTION
                                                ,NULL                       -- START TIME
                                                ,FALSE
                                                ,v_request_id
                                                );
      COMMIT;
    --  fnd_file.put_line (fnd_file.LOG, 'UPDATE DATA SUCESSFULLY');
      -- +--------+
      IF v_request_id1 > 0
         THEN
        -- +--------+
        BEGIN
         LOOP
          -- +--------+
              v_req_status :=
                 fnd_concurrent.get_request_status

                                                 (request_id          => v_request_id1,
                                                  appl_shortname      => 'CUSTOM',
                                                  program             => 'UPDATE_DFF',
                                                  phase               => v_phase,
                                                  status              => v_status,
                                                  dev_phase           => v_dev_phase,
                                                  dev_status          => v_dev_status,
                                                  MESSAGE             => v_message
                                                 );

              IF UPPER (v_dev_phase) = 'COMPLETE'
              THEN
              EXIT;
              END IF;
          END LOOP;
          -- +--------+
                 EXCEPTION
           WHEN OTHERS
           THEN
              fnd_file.put_line
                 (fnd_file.LOG,
                     'Error while checking " Updating data in the table " status '
                  || SQLERRM
                 );
        END;
      -- +--------+
      END IF;
      -- +--------+
          fnd_file.put_line (fnd_file.LOG,
                           '"updating data into base table" status : '
                        || v_dev_phase
                        || ' '
                        || v_dev_status
                       );
      COMMIT;

     EXCEPTION
     WHEN OTHERS
     THEN
        fnd_file.put_line (fnd_file.LOG,'Unexpected Error occured while running " Updating the data in the table" *****  '|| ' - '|| SQLERRM);
    END;
END  UPLOADATA_UPDATE_DFF;
END UPDATE_DFF_PKG;

Register the program for the package and run program from SRS windw and check