?
Solved

Date issue

Posted on 2013-06-09
7
Medium Priority
?
232 Views
Last Modified: 2013-06-09
Hi ,

I am Extracting the data from a csv file to the database but year is not capturing properly.Date format used in csv file is dd-mon-yyyy.Can anybody help me.


Regards,
GSK
0
Comment
Question by:gotetioracle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39232733
How are you extracting the data? Can you post (part of) what you've got already?
0
 

Author Comment

by:gotetioracle
ID: 39232792
hi  gerwinjansen

Here is the attachment
BL1.csv
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39232822
Hi, data looks OK to me but how are you getting the data into the database?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gotetioracle
ID: 39232834
Hi gerwinjansen,

For example take very first date in the EDI file it is 01-Jun-2013 but while storing in the database it is storing as 01-jun-0002
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39232837
Yes, I get that, I want to know how you are importing the .csv file into the database? Do you use Toad / sql loader or a script maybe? Can you share what you've got already?
0
 

Author Comment

by:gotetioracle
ID: 39232841
hi gerwinjansen,


PROCEDURE SP_BL_DOWNLOAD
--MODIFIED BY SAMARPITA CHOUDHURY ON 29/05/2013 TO VALIDATE POL,POD & MTY DEPOT
(
  v_FILE_ID IN NUMBER DEFAULT NULL ,
  v_LOCATION_ID IN NUMBER DEFAULT NULL ,
  v_ERRMSG OUT VARCHAR2
)
AS
   v_LINEBUFFER VARCHAR2(4000);
   v_START_PR VARCHAR2(1);
   v_START_cnt VARCHAR2(1);
   V_START_VESSEL VARCHAR2(1);
   v_FIRST_NO NUMBER(10,0);
   v_SEC_NO NUMBER(10,0);
   v_SRCH VARCHAR2(4000);

--------------------------------------  
                  V_VESL_CODE VARCHAR2(50);
                  V_VESSEL_NAME VARCHAR2(100);
                  V_VOYAGE_NO VARCHAR2(100);
                  V_IMO_CODE VARCHAR2(100);
                  V_CALL_SIGN VARCHAR2(100);
                  V_SHIPPING_LINE_CODE VARCHAR2(100);
                  V_PORT_OF_LOADING VARCHAR2(100);
                  V_LOADING_TERMINAL VARCHAR2(100);
                  V_DATE_OF_DEPARTURE VARCHAR2(100);
                  
                  

--------------------------------  
V_VOY_NO VARCHAR2(100);
   V_VESSEL_ID NUMBER;
   V_LINE_ID VARCHAR2(100);
   V_DOWNLOAD_TYPE VARCHAR2(1);
   V_IGM_NO VARCHAR2(100);
   V_IGM_DATE VARCHAR2(100);
   V_VIA_NO VARCHAR2(100);
   
   v_TEMP_CFS_CODE VARCHAR2(15);
   v_sort_item_no VARCHAR2(4000);
   v_sort_item_cnt NUMBER(10,0);

/*   v_TEMP_IMP_DOC_NO NUMBER(10,0);
   v_TEMP_GROSS_WT NUMBER(18,3);
   v_TEMP_WT_UNIT VARCHAR2(50);
   v_TEMP_NO_OF_PACKETS NUMBER(10,0);
   v_TEMP_BL_NO VARCHAR2(50);
   v_TEMP_BL_DATE DATE;
   v_TEMP_POL VARCHAR2(10);
   v_TEMP_POD VARCHAR2(10);
   v_TEMP_ITEM_DESCRIPTION VARCHAR2(2000);
   v_TEMP_ITEM_NO VARCHAR2(10);
   v_TEMP_IMPORTER_NAME VARCHAR2(100);
   v_TEMP_IMPORTER_ADDRESS VARCHAR2(500);
   v_TEMP_NOTIFY_PARTY_NAME VARCHAR2(100);
   v_TEMP_NOTIFY_PARTY_ADDRESS VARCHAR2(500);
   v_TEMP_PACKET_TYPE VARCHAR2(50);
   v_TEMP_IMDG_NO VARCHAR2(10);
   v_TEMP_MARKS VARCHAR2(500);
   v_TEMP_MARK_NO VARCHAR2(500);
   v_TEMP_CONT_NO VARCHAR2(20);
   v_TEMP_SEAL_NO VARCHAR2(20);
   v_TEMP_FCL_LCL VARCHAR2(1);
   v_TEMP_STATUS NUMBER(10,0);
   
   v_TIGM_NO VARCHAR2(20);
   v_temp_agent VARCHAR2(20);
   v_temp_iso VARCHAR2(20);
   v_temp_size VARCHAR2(10);
   v_temp_type VARCHAR2(10);
   v_temp_lc VARCHAR2(10);
   v_temp_wt VARCHAR2(20);
   v_temp_no_pkts_cnt VARCHAR2(20);
   */
   v_linenumber NUMBER(10,0);
   v_position NUMBER(10,0);
  /*
   v_temp_voy_no VARCHAR2(100);
   v_temp_vessel_name VARCHAR2(1000);
   v_PARAMETER_CFS_CODE VARCHAR2(100);
   v_REEFER_STATUS VARCHAR2(100);
   v_HAZ_STATUS VARCHAR2(100);
   v_BL_DATE DATE;
   v_BL_DATE_CHAR VARCHAR2(50);
*/
   
 ----
 v_BL_ID number;
v_HBL_NO varchar2(100);
v_HBL_DATE VARCHAR2(100);
v_MBL_NO varchar2(100);
v_MBL_DATE date;
v_BOOK_REF_NO varchar2(100);
v_SHIP_REF_NO varchar2(100);
v_SHIPPER_NAME varchar2(200);
v_SHiPPER_ADDRESS varchar2(500);
v_CONSIGNEE_NAME varchar2(200);
v_CONSIGNEE_ADDRESS varchar2(500);
v_NOTIFY_PARTY_NAME varchar2(200);
v_NOTIFY_PARTY_ADDRESS varchar2(500);
v_ORIGIN varchar2(100);
v_ACCEPT_DATE date;
v_POL varchar2(100);
v_POD varchar2(100);
v_FPD varchar2(100);
v_FPD_DATE date;
v_CARGO_DESCRIPTION varchar2(500);
v_PKG_TYPE varchar2(100);
v_NO_OF_PKGS number;
v_GROSS_WT number;
v_WT_UNIT varchar2(100);
v_VOLUME number;
v_VOLUME_UNIT varchar2(100);
v_MARKS_NUMBER varchar2(500);
v_MTY_DEPOT varchar2(100);
v_UN_NO varchar2(100);
v_IMO_NO varchar2(100);
V_NO_OF_FREE_DYS_DEST NUMBER;
V_DETENTION_CHRGE_PER_DAY NUMBER(18,3);
--ADED BY SAMARPITA CHOUDHURY ON 07/06/2013
V_ITEM_TYPE VARCHAR2(2);
V_CFS_CODE VARCHAR2(20);
V_CARGO_MOVEMENT VARCHAR2(2);
V_CARRIER_CODE VARCHAR2(50);
V_SUB_ITEM_NO VARCHAR2(20);
V_ITEM_NO VARCHAR2(20);
V_TRANSIT_BOND_NO VARCHAR2(20);
V_MODE_OF_TRANSPORT VARCHAR2(1);
 -----  

v_CONT_REF_ID number;
v_CONT_NO varchar2(50);
v_CONT_SIZE varchar2(20);
v_SEAL_NO_1 varchar2(50);
v_ISO_CODE varchar2(50);
v_TARE_WT number;
v_CARGO_WT number;
P_MC_ID NUMBER;
P_ID_POD NUMBER;
P_ID_POL NUMBER;
P_ID_VESSEL_POL NUMBER;

   
   --PRINT '2'
   CURSOR C1
     IS SELECT FILE_LINEDATA
     FROM IGM_FILEDATA
    WHERE FILE_ID = v_FILE_ID
     ORDER BY SEQ_NO;

BEGIN
   v_START_PR := 'N' ;
   v_START_cnt := 'N' ;
   V_START_VESSEL := 'N';
   
   v_sort_item_cnt := 0 ;
   v_SORT_ITEM_NO := '' ;
   v_linenumber := 0 ;
   v_position := 0 ;


    for i in c1
     LOOP
      v_LINEBUFFER := i.file_linedata;
      BEGIN
         v_position := 0 ;

         IF substr(LTRIM(RTRIM(v_LINEBUFFER)),1,9) = 'END-CARGO' THEN
            v_START_PR := 'N' ;
         END IF;

         IF substr(LTRIM(RTRIM(v_LINEBUFFER)),1,10) = 'END-VESSEL' THEN
            v_START_VESSEL := 'N' ;
         END IF;


         IF substr(LTRIM(RTRIM(v_LINEBUFFER)),1,13) = 'END-CONTAINER' THEN
            v_START_CNT := 'N' ;
         END IF;
             
----------------------------------------
         IF v_START_VESSEL = 'Y' THEN
         BEGIN
           
                  v_FIRST_NO := 0;
            v_SEC_NO := instr(v_LINEBUFFER,',',1,1);
            BEGIN      
            V_VESL_CODE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1,  v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;
           
                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,1);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,2);
            BEGIN
            V_VESSEL_NAME := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,2);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,3);
            BEGIN
            V_voyage_no := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,3);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,4);
            BEGIN
            V_IMO_CODE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;
                  
                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,4);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,5);
            BEGIN
            V_CALL_SIGN := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;


                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,5);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,6);
            BEGIN
            V_SHIPPING_LINE_CODE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;
                  
                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,6);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,7);
            BEGIN
            V_PORT_OF_LOADING := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;
           
             IF V_PORT_OF_LOADING IS not NULL AND LENGTH(V_PORT_OF_LOADING) > 0 THEN  
            begin
                   SELECT ID INTO P_ID_VESSEL_POL
                   FROM
                         COUNTRY_PORT_MASTER
                   WHERE UPPER(PCODE) = UPPER(V_PORT_OF_LOADING)
                   AND PTYPE = 'P';

            exception
                when no_data_found then
                     v_ERRMSG := 'INVALID POL FOUND;';
                     RETURN;
                when others then null;
            end;
            END IF;


                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,7);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,8);
            BEGIN
            V_LOADING_TERMINAL := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

                  v_FIRST_NO := instr(v_LINEBUFFER,',',1,8);
            v_SEC_NO := LENGTH(V_LINEBUFFER) ;
            BEGIN
            V_DATE_OF_DEPARTURE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

--                  v_date_of_departure := translate(v_date_of_departure,',','');
                  
                     INSERT INTO  BL_DOWNLOAD_VESSEL
                       ( FILE_ID,
                                    VESL_CODE,
                                    VESSEL_NAME,
                                    VOYAGE_NO,
                                    IMO_CODE,
                                    CALL_SIGN,
                                    SHIPPING_LINE,
                                    POL,
                                    LOAD_TERMINAL,
                                    ATD                                     )
                       VALUES (
                                                   V_FILE_ID,
                                                V_VESL_CODE,
                                                V_VESSEL_NAME,
                                                V_VOYAGE_NO,
                                                V_IMO_CODE,
                                                V_CALL_SIGN,
                                                V_SHIPPING_LINE_CODE ,
                                                V_PORT_OF_LOADING,
                                                V_LOADING_TERMINAL,
                                                to_date(V_DATE_OF_DEPARTURE,'dd-mon-yyyy hh24:mi'));

                       commit;

         END;
         END IF;

---------------------------------------------------------------------------

--============================================================================================

         IF v_START_PR = 'Y' THEN
         BEGIN
            v_FIRST_NO := 0;
            v_SEC_NO := instr(v_LINEBUFFER,',',1,1);
            BEGIN
            v_HBL_NO := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO , v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,1);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,2);                  
            BEGIN                  
            --v_HBL_DATE := TO_DATE(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 8))),'DD-MON-YYYY');
                  v_HBL_DATE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,2);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,3);
               BEGIN
               v_MBL_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,3);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,4);
               BEGIN
               v_MBL_DATE := TO_DATE(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 8))),'DD-MON-YYYY') ;
                     --v_MBL_DATE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;



            v_FIRST_NO := instr(v_LINEBUFFER,',',1,4);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,5);
               BEGIN
               v_BOOK_REF_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,5);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,6);
               BEGIN
               v_SHIP_REF_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,6);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,7);
               BEGIN
               v_SHIPPER_NAME := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))),'') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,7);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,8);
               BEGIN
               v_SHiPPER_ADDRESS := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,8);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,9);
               BEGIN
               v_SHiPPER_ADDRESS := V_SHIPPER_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,9);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,10);
               BEGIN
               v_SHiPPER_ADDRESS := V_SHIPPER_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,10);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,11);
               BEGIN
               v_CONSIGNEE_NAME := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,11);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,12);
               BEGIN
               v_CONSIGNEE_ADDRESS := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,12);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,13);
               BEGIN
               v_CONSIGNEE_ADDRESS := V_CONSIGNEE_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,13);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,14);
               BEGIN
               v_CONSIGNEE_ADDRESS := V_CONSIGNEE_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,14);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,15);
               BEGIN
               v_NOTIFY_PARTY_NAME := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,15);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,16);
               BEGIN
               v_NOTIFY_PARTY_ADDRESS := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,16);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,17);
               BEGIN
               v_NOTIFY_PARTY_ADDRESS := V_NOTIFY_PARTY_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,17);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,18);
               BEGIN
               v_NOTIFY_PARTY_ADDRESS := V_NOTIFY_PARTY_ADDRESS ||';'|| NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,18);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,19);
               BEGIN
               v_ORIGIN := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,19);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,20);
               BEGIN
               v_ACCEPT_DATE := TO_DATE(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 8))),'DD-MON-YYYY') ;
                     --v_ACCEPT_DATE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,20);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,21);
               BEGIN
               v_POL := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
               
                   
            IF v_POL IS not NULL AND LENGTH(v_POL) > 0 THEN  
            begin
                   SELECT ID INTO P_ID_POL
                   FROM
                         COUNTRY_PORT_MASTER
                   WHERE UPPER(PCODE) = UPPER(v_POL)
                   AND PTYPE = 'P';

            exception
                when no_data_found then
                     v_ERRMSG := 'VALID POL NOT FOUND FOR HBL NO:' ||  v_HBL_NO || ';' ;
                     RETURN;
                when others then null;
            end;
            END IF;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,21);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,22);
               BEGIN
               v_POD := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
             
            IF v_POD IS not NULL AND LENGTH(v_POD) > 0 THEN
            begin
                   SELECT ID INTO P_ID_POD
                   FROM COUNTRY_PORT_MASTER
                   WHERE UPPER(PCODE) = UPPER(v_POD)
                   AND PTYPE = 'P';

            exception
                when no_data_found then
                     v_ERRMSG := 'VALID POD NOT FOUND FOR HBL NO:' ||  v_HBL_NO || ';' ;
                     RETURN;
                WHEN OTHERS THEN NULL;
            end;
            END IF;
           
       
            IF v_POL IS not NULL AND LENGTH(v_POL) > 0 AND v_POD IS not NULL AND LENGTH(v_POD) > 0  THEN
                BEGIN
                    IF v_POL = v_POD THEN
                     BEGIN
                        v_ERRMSG := 'POL & POD SHOULD BE DIFFERENT;' ;
                        RETURN;
                     END;
                    END IF;
                END;
            END IF;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,22);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,23);
               BEGIN
               v_FPD := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,23);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,24);
               BEGIN
               v_FPD_DATE := TO_DATE(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 8))),'DD-MON-YYYY') ;
                     --v_FPD_DATE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,24);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,25);
               BEGIN
               v_CARGO_DESCRIPTION := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
                    
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,25);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,26);
               BEGIN
               v_PKG_TYPE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,26);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,27);
               BEGIN
               v_NO_OF_PKGS := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,27);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,28);
               BEGIN
               v_GROSS_WT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,28);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,29);
               BEGIN
               v_WT_UNIT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,29);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,30);
               BEGIN
               v_VOLUME := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,30);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,31);
               BEGIN
               v_VOLUME_UNIT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,31);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,32);
               BEGIN
               v_MARKS_NUMBER := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,32);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,33);
               BEGIN
               v_MTY_DEPOT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            IF v_MTY_DEPOT IS not NULL AND LENGTH(v_MTY_DEPOT) > 0 THEN
            begin
                  IF v_POD IS NULL OR LENGTH(v_POD) = 0 THEN
                    begin
                      v_ERRMSG := 'POD NOT FOUND TO VALIDATE MTY DEPOT FOR HBL NO:' ||  v_HBL_NO || ';' ;
                      RETURN;
                    END;
                  END IF;
                   
                   SELECT MC_ID INTO P_MC_ID
                   FROM MTY_CFS_LOC_MASTER
                   WHERE PORT_ID = P_ID_POD
                   AND UPPER(MC_CODE) = UPPER(v_MTY_DEPOT)
                   AND MC_TYPE = 'M';

            exception
                when no_data_found then
                     v_ERRMSG := 'MTY DEPOT NOT FOUND AGAINST THE POD FOR HBL NO:' ||  v_HBL_NO || ';' ;
                     RETURN;
                WHEN OTHERS THEN NULL;
            end;
            END IF;
                    

            v_FIRST_NO := instr(v_LINEBUFFER,',',1,33);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,34);
               BEGIN
               v_UN_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


            v_FIRST_NO := instr(v_LINEBUFFER,',',1,34);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,35);
               BEGIN
               v_IMO_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

             
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,35);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,36);
               BEGIN
               V_NO_OF_FREE_DYS_DEST := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,36);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,37);
               BEGIN
               V_DETENTION_CHRGE_PER_DAY := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
               
            --ADDED BY SAMARPITA CHOUDHURY ON 07/06/2013
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,37);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,38);
               BEGIN
               V_ITEM_TYPE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
           
             IF V_ITEM_TYPE IS NOT NULL AND LENGTH(V_ITEM_TYPE) > 0 THEN
                IF V_ITEM_TYPE NOT IN ('GC','OT','UB') THEN
                       v_ERRMSG := 'INVALID ITEM TYPE;';
                       RETURN;
                END IF;
             END IF;
             
                       
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,38);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,39);
               BEGIN
               V_CFS_CODE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
            ----validation for cfs code
             IF V_CFS_CODE IS not NULL AND LENGTH(V_CFS_CODE) > 0 THEN
            begin
                                   
                   SELECT MC_ID INTO P_MC_ID
                   FROM MTY_CFS_LOC_MASTER
                   WHERE UPPER(MC_CODE) = UPPER(V_CFS_CODE)
                   AND MC_TYPE = 'C';

            exception
                when no_data_found then
                     v_ERRMSG := 'CFS CODE NOT FOUND;';
                     RETURN;
                WHEN OTHERS THEN NULL;
            end;
            END IF;
            ----end
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,39);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,40);
               BEGIN
               V_CARGO_MOVEMENT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
             IF V_CARGO_MOVEMENT IS NOT NULL AND LENGTH(V_CARGO_MOVEMENT) > 0 THEN
                IF V_CARGO_MOVEMENT NOT IN ('TI','LC','TC') THEN
                       v_ERRMSG := 'INVALID CARGO MOVEMENT;';
                       RETURN;
                END IF;
             END IF;
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,40);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,41);
               BEGIN
               V_CARRIER_CODE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,41);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,42);
               BEGIN
               V_SUB_ITEM_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,42);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,43);
               BEGIN
               V_ITEM_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,43);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,44);
               BEGIN
               V_TRANSIT_BOND_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
               
            v_FIRST_NO := instr(v_LINEBUFFER,',',1,44);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,45);
               BEGIN
               V_MODE_OF_TRANSPORT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), '') ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
               
            IF V_MODE_OF_TRANSPORT IS NOT NULL AND LENGTH(V_MODE_OF_TRANSPORT) > 0 THEN
                IF V_MODE_OF_TRANSPORT NOT IN ('T','R','S') THEN
                       v_ERRMSG := 'INVALID MODE OF TRANSPORT;' ;
                       RETURN;
                END IF;
             END IF;
            --END OF ADDITION
           

               SELECT NVL(MAX(bl_id), 0)
                 INTO v_bl_id
                 FROM  BL_DOWNLOAD_CARGO ;
                         
               v_bl_id := NVL(v_bl_id, 0) + 1 ;


               DECLARE
                  v_TEMPCOUNTS NUMBER(10,0);

               BEGIN
                  v_TEMPCOUNTS := 0 ;
                         
                  SELECT COUNT(*)
                    INTO v_TEMPCOUNTS
                    FROM  BL_DOWNLOAD_CARGO
                   WHERE  hbl_no = v_hbl_no;
                                       
                  ------------- DULICACY VALIDATION
                  IF v_TEMPCOUNTS = 0  OR v_TEMPCOUNTS IS NULL THEN

                  BEGIN
                     INSERT INTO  BL_DOWNLOAD_CARGO

                       ( FILE_ID,
                                          BL_ID,
                                          HBL_NO,
                                          HBL_DATE,
                                          MBL_NO,
                                          MBL_DATE,
                                          BOOK_REF_NO,
                                          SHIP_REF_NO,
                                          SHIPPER_NAME,
                                          SHPPER_ADDRESS,
                                          CONSIGNEE_NAME,
                                          CONSIGNEE_ADDRESS,
                                          NOTIFY_PARTY_NAME,
                                          NOTIFY_PARTY_ADDRESS,
                                          ORIGIN,
                                          ACCEPT_DATE,
                                          POL,
                                          POD,
                                          FPD,
                                          FPD_DATE,
                                          CARGO_DESCRIPTION,
                                          PKG_TYPE,
                                          NO_OF_PKGS,
                                          GROSS_WT,
                                          WT_UNIT,
                                          VOLUME,
                                          VOLUME_UNIT,
                                          MARKS_NUMBER,
                                          MTY_DEPOT,
                                          UN_NO,
                                          IMO_NO,
                            NO_OF_FREE_DYS_DEST,
                            DETENTION_CHRGE_PER_DAY,
                            ITEM_TYPE,
                            CFS_CODE,
                            CARGO_MOVEMENT,
                            CARRIER_CODE,
                            SUB_ITEM_NO,
                            ITEM_NO,
                            TRANSIT_BOND_NO,
                            MODE_OF_TRANSPORT)
                       VALUES (v_FILE_ID,
                                          v_BL_ID,
                                          v_HBL_NO,
                                          TO_DATE(v_HBL_DATE,'dd-mon-yyyy'),
                                          v_MBL_NO,
                                          v_MBL_DATE,
                                          v_BOOK_REF_NO,
                                          v_SHIP_REF_NO,
                                          v_SHIPPER_NAME,
                                          v_SHiPPER_ADDRESS,
                                          v_CONSIGNEE_NAME,
                                          v_CONSIGNEE_ADDRESS,
                                          v_NOTIFY_PARTY_NAME,
                                          v_NOTIFY_PARTY_ADDRESS,
                                          v_ORIGIN,
                                          v_ACCEPT_DATE,
                                          v_POL,
                                          v_POD,
                                          v_FPD,
                                          v_FPD_DATE,
                                          v_CARGO_DESCRIPTION,
                                          v_PKG_TYPE,
                                          v_NO_OF_PKGS,
                                          v_GROSS_WT,
                                          v_WT_UNIT,
                                          v_VOLUME,
                                          v_VOLUME_UNIT,
                                          v_MARKS_NUMBER,
                                          v_MTY_DEPOT,
                                          v_UN_NO,
                                          v_IMO_NO,
                            V_NO_OF_FREE_DYS_DEST,
                            V_DETENTION_CHRGE_PER_DAY,
                            V_ITEM_TYPE,
                            V_CFS_CODE,
                            V_CARGO_MOVEMENT,
                            V_CARRIER_CODE,
                            V_SUB_ITEM_NO,
                            V_ITEM_NO,
                            V_TRANSIT_BOND_NO,
                            V_MODE_OF_TRANSPORT      
                       );

                       commit;

                  END;
                  END IF;
               END;

           
                  
                  END;
            END IF;

--========================================================================            
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
         IF v_START_cnt = 'Y' THEN
         DECLARE
            v_I NUMBER(10,0);

         BEGIN
            v_FIRST_NO := 0;
            v_SEC_NO := instr(v_LINEBUFFER,',',1,1);
            BEGIN
            v_HBL_NO := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO , v_SEC_NO - v_FIRST_NO - 1))) ;
            EXCEPTION
            WHEN OTHERS THEN NULL;
            END;

           v_FIRST_NO := instr(v_LINEBUFFER,',',1,1);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,2);
               BEGIN
               v_CONT_NO := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


           v_FIRST_NO := instr(v_LINEBUFFER,',',1,2);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,3);
               BEGIN
               v_CONT_SIZE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


           v_FIRST_NO := instr(v_LINEBUFFER,',',1,3);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,4);
               BEGIN
               v_SEAL_NO_1 := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;
                    
           v_FIRST_NO := instr(v_LINEBUFFER,',',1,4);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,5);
               BEGIN
               v_ISO_CODE := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

           v_FIRST_NO := instr(v_LINEBUFFER,',',1,5);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,6);
               BEGIN
               v_TARE_WT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

           v_FIRST_NO := instr(v_LINEBUFFER,',',1,6);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,7);
               BEGIN
               v_CARGO_WT := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

           v_FIRST_NO := instr(v_LINEBUFFER,',',1,7);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,8);
               BEGIN
               v_VOLUME := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;


           v_FIRST_NO := instr(v_LINEBUFFER,',',1,8);
            v_SEC_NO := instr(v_LINEBUFFER,',',1,9);
               BEGIN
               v_NO_OF_PKGS := NVL(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, v_SEC_NO - v_FIRST_NO - 1))), 0) ;
               EXCEPTION
               WHEN OTHERS THEN NULL;
               END;

                     INSERT INTO  BL_DOWNLOAD_CONTAINER
                       (HBL_NO,
                                    CONT_REF_ID,
                                    CONT_NO,
                                    CONT_SIZE,
                                    SEAL_NO_1,
                                    ISO_CODE,
                                    TARE_WT,
                                    CARGO_WT,
                                    VOLUME,
                                    NO_OF_PKGS)
                       VALUES ( V_HBL_NO,
                                                CONT_REF_ID.NEXTVAL,
                                                V_CONT_NO,
                                                V_CONT_SIZE,
                                                V_SEAL_NO_1,
                                                V_ISO_CODE,
                                                V_TARE_WT,
                                                V_CARGO_WT,
                                                V_VOLUME,
                                                V_NO_OF_PKGS
                                  );
                     commit;

         END;
         END IF;

--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

             
         IF SUBSTR(LTRIM(RTRIM(v_LINEBUFFER)),1,5) = 'CARGO' THEN
            v_START_PR := 'Y' ;
            v_START_VESSEL := 'N' ;
         END IF;
         IF substr(LTRIM(RTRIM(v_LINEBUFFER)),1,9) = 'CONTAINER' THEN
            v_START_cnt := 'Y' ;
            v_START_PR := 'N' ;
            v_START_VESSEL := 'N' ;
         END IF;
             IF substr(LTRIM(RTRIM(v_LINEBUFFER)),1,6) = 'VESSEL' THEN
            v_START_VESSEL := 'Y' ;
         END IF;
             
      END;

   END LOOP;

   UPDATE  BL_DOWNLOAD_CONTAINER
      SET BL_ID = ( SELECT MAX(BL_DOWNLOAD_CARGO.BL_ID)
                        FROM  BL_DOWNLOAD_CARGO
                         WHERE  BL_DOWNLOAD_CARGO.HBL_NO =  BL_DOWNLOAD_CONTAINER.HBL_NO )
      WHERE ( BL_ID IS NULL
     OR NVL(BL_ID, 0) = '0' );

     commit;



RETURN;

   pkg_process.iGM_transfer(v_via_no,
                            v_IGM_NO,
                            v_LOCATION_ID,
                            v_VESSEL_ID);

END ;

-----------------------------
0
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 2000 total points
ID: 39232890
Looks like your date formatter part is not getting enough characters:

v_MBL_DATE := TO_DATE(LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 8))),'DD-MON-YYYY') ;
--v_MBL_DATE := LTRIM(RTRIM(SUBSTR(v_LINEBUFFER, v_FIRST_NO + 1, 11))) ;

Something was changed here, and "01-Jun-2013" does not fit into 8 characters, if you have 8 characters, you get "01-Jun-2" with will result in the year 2 instead of 2013.

Change to 11 iso 8 and see if that'll work for you. There maybe more occurrences in your code. Check for those as well.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question