Solved

Date issue

Posted on 2013-06-09
7
226 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
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:Gerwin Jansen
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 37

Expert Comment

by:Gerwin Jansen
ID: 39232822
Hi, data looks OK to me but how are you getting the data into the database?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 37

Expert Comment

by:Gerwin Jansen
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 37

Accepted Solution

by:
Gerwin Jansen earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now