FILENAME=MARK1MARK2 
LOGFILE1=$LOGDIR/$FILENAME.CSV
DB_User=$SQLUSER1/$SQLPWD1@$SQLCONNECTION

echo "Calling .sql file to generate the CSV report"

sqlplus -S $SQLUSER/$SQLPASS <<EOF>>${LOGFILE1}

whenever sqlerror exit failure
whenever os error exit failure
SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SPOOL ${LOGFILE1}
DBMS_OUTPUT.enable(buffer_size => NULL);         	
@${SCRIPTSDIR}/markprocedure.sql
SPOOL OFF
EXIT;
EOF
RSLT=$?
echo ${RSLT}

     
--SET ECHO ON
--SET SERVEROUTPUT ON
--SET LINESIZE 32767
--SET WRAP OFF
--SET PAGESIZE 0
--SET FEEDBACK OFF
--SET TRIMSPOOL ON
--SET TERMOUT OFF
--SPOOL C:\Users\tan0078\Documents\01_myfile_MARKUAT.csv

   
         SELECT '"MESSAGE ID","TYPE CD","DESCRIPTION TEXT","IRF AREA CODE","TRANSACTION PROCESS ID","RECORD CREATE TMS",'||
                '"TRNCR_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||
                '"MARK_SCAC_CD","MARK_SCAC_NM","AAR_UPDATE_DT","EFCTV_DT","DUNS_NBR","EXPIRY_DT","ICC_DOCKET_NBR",'||
                '"TRNSCN_NBR","TYPE_CD","OWNER_MARK_CD","OWNER_SCAC_CD","ROAD_NBR","REPORT_EQPMT_RLS_CD","REPORT_EQPMT_PLCMNT_CD",'||
                '"NMFTA_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||  
                '"SCAC_CD","AAR_UPDATE_DT","TRNCR_NM","ADRS_TXT","CITY_NM","CNTRY_NM","POSTAL_ZIP_CD","TRNSCN_NBR","STPRV_CD",'
        FROM  dual; 
DECLARE

    lv_prev_irf_area_cd           IRF_MSG_TRNSCN_PRCS.irf_area_cd%TYPE;  
                     
    CURSOR cur_mark1details(v_trnscn_prcs_id   IN     TRNCR_TRNSCN_PRCS.trncr_trnscn_prcs_id%TYPE)
    IS     SELECT *
           FROM trncr_trnscn_prcs 
           WHERE trncr_trnscn_prcs_id = v_trnscn_prcs_id;
           
           
   CURSOR cur_mark2details(v_trnscn_prcs_id   IN     NMFTA_TRNSCN_PRCS.nmfta_trnscn_prcs_id%TYPE)
    IS     SELECT *
           FROM nmfta_trnscn_prcs
           WHERE nmfta_trnscn_prcs_id = v_trnscn_prcs_id;             
         
      lv_Row_Mark1Trans   TRNCR_TRNSCN_PRCS%ROWTYPE;        
      lv_NumFoundRows                  NUMBER:=0;
      
      lv_NumMark1Trans    NUMBER(10):=0;
      lv_NumMark2Trans    NUMBER(10):=0; 
      lv_NumMark1Trans1   NUMBER(10):=0; 
      lv_NumMark2Trans1   NUMBER(10):=0; 
      
      lv_NumMark1Recs    NUMBER(10):=0;
      lv_NumMark2Recs    NUMBER(10):=0;

BEGIN


    SELECT COUNT(*)
    INTO  lv_NumFoundRows
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd IN('MARK1','MARK2')
    ORDER BY b.irf_area_cd;
    
    SELECT COUNT(*)
    INTO  lv_NumMark1Recs
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd ='MARK1'
    ORDER BY b.irf_area_cd;
    
    SELECT COUNT(*)
    INTO  lv_NumMark2Recs
    FROM irf_msg a
    JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
    WHERE b.irf_area_cd ='MARK2'
    ORDER BY b.irf_area_cd;
    
       
    IF lv_NumFoundRows <> 0 THEN
    
        FOR rec IN (SELECT a.msg_id
                      ,a.type_cd
                      ,a.dscrpt_txt
                      ,b.irf_area_cd
                      ,b.trnscn_prcs_id
                      ,b.record_create_tms
                       FROM irf_msg a
                      JOIN irf_msg_trnscn_prcs b ON b.msg_id = a.msg_id
                      WHERE b.irf_area_cd IN('MARK1','MARK2')
                      ORDER BY b.irf_area_cd  
                     )
       LOOP
            DBMS_OUTPUT.enable(buffer_size => NULL);
            DBMS_OUTPUT.put_line(TO_CHAR(rec.msg_id)||','||rec.type_cd||','||'"'||rec.dscrpt_txt||'"'||','||rec.irf_area_cd||','||TO_CHAR(rec.trnscn_prcs_id)||','||
                                  TO_CHAR(rec.record_create_tms,'MM/DD/RRRR HH24:MI:SS'));
                                    
           IF rec.irf_area_cd = 'MARK1' THEN
            
                -- Passing each transaction obtained for an area code to retrieve MARK1's details
               FOR lv_Row_Mark1Trans IN cur_mark1details(rec.trnscn_prcs_id)
               LOOP
               DBMS_OUTPUT.put(','||TO_CHAR(lv_Row_Mark1Trans.trncr_trnscn_prcs_id)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.record_create_tms, 'MM/DD/RRRR HH24:MI:SS')||','||
                                       TO_CHAR(lv_Row_Mark1Trans.record_update_tms, 'MM/DD/RRRR HH24:MI:SS')||','|| 
                                               lv_Row_Mark1Trans.create_user_id||','||
                                       TO_CHAR(lv_Row_Mark1Trans.mark_scac_cd)||','||
                                               lv_Row_Mark1Trans.mark_scac_nm||','||
                                               lv_Row_Mark1Trans.aar_update_dt||','||
                                               lv_Row_Mark1Trans.efctv_dt||','||
                                       TO_CHAR(lv_Row_Mark1Trans.duns_nbr)||','||
                                               lv_Row_Mark1Trans.expiry_dt||','||
                                       '="'||TO_CHAR(lv_Row_Mark1Trans.icc_docket_nbr)||'"'||','||
                                       '="'||TO_CHAR(lv_Row_Mark1Trans.trnscn_nbr)||'"'||','||
                                       TO_CHAR(lv_Row_Mark1Trans.type_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.owner_mark_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.owner_scac_cd)||','||
                                       '="'||lv_Row_Mark1Trans.road_nbr||'"'||','||
                                       TO_CHAR(lv_Row_Mark1Trans.report_eqpmt_rls_cd)||','||
                                       TO_CHAR(lv_Row_Mark1Trans.report_eqpmt_plcmnt_cd)
                                 );
               DBMS_OUTPUT.put_line('          '); 
               lv_NumMark1Trans:=cur_mark1details%ROWCOUNT;
               lv_NumMark1Trans1:=lv_NumMark1Trans1+lv_NumMark1Trans;
                
               END LOOP; 
               
           ELSIF rec.irf_area_cd = 'MARK2' THEN
           
              --DBMS_OUTPUT.put_line('"MESSAGE ID","TYPE CD","DESCRIPTION TEXT","IRF AREA CODE","TRANSACTION PROCESS ID","RECORD CREATE TMS",'||
                --                      '"NMFTA_TRNSCN_PRCS_ID","RECORD_CREATE_TMS","RECORD_UPDATE_TMS","CREATE_USER_ID","UPDATE_USER_ID",'||  
                  --                    '"SCAC_CD","AAR_UPDATE_DT","TRNCR_NM","ADRS_TXT","CITY_NM","CNTRY_NM","POSTAL_ZIP_CD","TRNSCN_NBR","STPRV_CD",');
               
               DBMS_OUTPUT.enable(buffer_size => NULL);
              --DBMS_OUTPUT.put_line(TO_CHAR(rec.msg_id) ||','||TO_CHAR(rec.type_cd)||','||TO_CHAR(rec.dscrpt_txt)||','||TO_CHAR(rec.irf_area_cd)||','||
                --                     TO_CHAR(rec.trnscn_prcs_id)||','||TO_CHAR(rec.record_create_tms,'MM/DD/RRRR HH24:MI:SS')||','
                  --                   );
              FOR lv_Row_Mark2Trans IN cur_mark2details(rec.trnscn_prcs_id)
              LOOP 
                DBMS_OUTPUT.put(','||TO_CHAR(lv_Row_Mark2Trans.nmfta_trnscn_prcs_id)||','||
                                        TO_CHAR(lv_Row_Mark2Trans.record_create_tms,'MM/DD/RRRR HH24:MI:SS')||','||
                                        TO_CHAR(lv_Row_Mark2Trans.record_update_tms,'MM/DD/RRRR HH24:MI:SS')||','||
                                                lv_Row_Mark2Trans.create_user_id||','||
                                                lv_Row_Mark2Trans.update_user_id||','||
                                        TO_CHAR(lv_Row_Mark2Trans.scac_cd)||','||
                                                lv_Row_Mark2Trans.aar_update_dt||','||
                                                lv_Row_Mark2Trans.trncr_nm||','||
                                        '"'||lv_Row_Mark2Trans.adrs_txt||'"'||','||
                                        '"'||lv_Row_Mark2Trans.city_nm||'"'||','||
                                        '"'||lv_Row_Mark2Trans.cntry_nm||'"'||','||
                                        '"'||lv_Row_Mark2Trans.postal_zip_cd||'"'||','||
                                        '="'||lv_Row_Mark2Trans.trnscn_nbr||'"'||','||
                                        '="'||lv_Row_Mark2Trans.stprv_cd||'"'
                                    );                                              
               DBMS_OUTPUT.put_line('          ');
               lv_NumMark2Trans:=cur_mark2details%ROWCOUNT;
               lv_NumMark2Trans1:=lv_NumMark2Trans1+lv_NumMark2Trans;
              END LOOP;
               
         END IF;       
       
       END LOOP;
    ELSE 
        RAISE NO_DATA_FOUND;
    END IF;
              
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('          '); 
             DBMS_OUTPUT.put_line('SUMMARY DETAILS');
             DBMS_OUTPUT.put_line('======================================================================'); 
             
             DBMS_OUTPUT.put_line('Number of Mark1 master records: ' ||lv_NumMark1Recs);
                    
             IF lv_NumMark1Trans1=0 THEN   
               DBMS_OUTPUT.put_line('There are no transaction details for any of the transaction number against Mark1');
             ELSE
                DBMS_OUTPUT.put_line('Number of records with Mark1 transaction details: '||lv_NumMark1Trans1);
             END IF;  
                 
                 
             DBMS_OUTPUT.put_line ('Number of Mark2 master records: ' ||lv_NumMark2Recs);    
                           
              IF lv_NumMark2Trans1=0 THEN   
                DBMS_OUTPUT.put_line('There are no transaction details for any of the transaction number against Mark2');
              ELSE
                DBMS_OUTPUT.put_line('Number of records with Mark2 transaction details: '||lv_NumMark2Trans1);
              END IF;  
            
            
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.put_line('There is no data or area codes based on the join condition....');
        
     WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(SQLCODE||':'||SQLERRM);
    
    
END;



--SPOOL OFF

Calling .sql file to generate the CSV report
0