#!/usr/bin/perl

use DBI;

require "/home/j204sc/bin/cgi-lib2.pl";          ## Perl Handling Sub-routines
require "/home/j204sc/bin/currency.pl";          ## Currency Format Sub-routine
require "/home/j204sc/bin/DEFAULT_FONTS";        ## Default Fonts
require "/home/j204sc/bin/ORACLE_CONNECTION";    ## Oracle DB Connection Routine

if ($dbh eq '') { print "$FA Connection to Oracle Failed."; exit; }

#**************************************************************#
#                                                              #
#  Job j204_tb_email2 will create the Batch Email List         #
#                                                              #
#**************************************************************#

$DATE1 = `date`;
chop $DATE1;

print <<ENDOFFILE;

**=====================================================**
** j204_tb_email2:  begin execution                    **
**=====================================================**
$DATE1

ENDOFFILE

###############
#  Section 1  #
###############

open(OUTPUT1, ">/home/j204sc/output/tb.email.ids");

$sql_cmd = "
select distinct 
       a.a_phone_n
     , a.f_fypd     
     , b.a_badge_n
     , b.a_uid_x        
     , b.f_call_i       
  from t204_tb_dtbill       a
     , t204_tb_email_ids    b
 where a.a_badge_n  = b.a_badge_n
   and b.f_call_i   = 'DT'
   and a.f_fypd = ( select f_fypd from genfin_dbo.t740_fypd
                     where to_char(sysdate - 5, 'YYYY/MM/DD')
                           between f_beg_d and f_end_d )
union
select distinct 
       a.a_phone_n
     , a.f_fypd     
     , b.a_badge_n
     , b.a_uid_x        
     , b.f_call_i       
  from t204_tb_dtbill       a
     , t204_tb_email_ids    b
 where a.a_badge_n  = b.a_badge_n
   and b.f_call_i   = 'CC'
   and a.f_fypd = ( select f_fypd from genfin_dbo.t740_fypd
                     where to_char(sysdate - 5, 'YYYY/MM/DD')
                           between f_beg_d and f_end_d )
order by 3, 4, 1
     ";


$stmt = $dbh->prepare("
    $sql_cmd
     ") or DBI::errstr;

$stmt->execute;

print <<ENDOFFILE;
 $sql_cmd
ENDOFFILE

$SP = '                                                                   ';
$SC   = ':';
$TYP  = '2';

$i = 0;

while ( @data = $stmt->fetchrow_array() ) {

$data[0] = substr($data[0]."           ",0,11);
$data[1] = substr($data[1]."      ",0,6);
$data[2] = substr($data[2]."        ",0,8);
$data[3] = substr($data[3]."                             ",0,30);
$data[4] = substr($data[4]."  ",0,2);

printf (OUTPUT1 "%11s%1s%6s%1s%8s%1s%30s%1s%2s\n",$data[0],$SC,$data[1],$SC,$data[2],$SC,$data[3],$SC,$data[4]);


$i = $i + 1;

  }

$stmt->finish;

close(OUTPUT1);

### END OF SECTION


$DATE1 = `date`;
chop $DATE1;

print <<ENDOFFILE;

Number of Records: $i  

*AIX*Successful exit -

**=====================================================**
** j204_tb_email2:  end execution                      **
**=====================================================**
$DATE1

ENDOFFILE

$dbh->disconnect;

exit;
