#!/cluster/uapp/perl/bin/perl -w
use DBI;
use strict;
use DBAccess;
use DBD::CSV;
use MIME::Lite;
use Net::SMTP;
use Data::Dumper;
use Text::CSV_XS;
my @ret_values =  undef;


##Parameters to connect to the Database here#####

 # Run stored proc
    my $sp = "lv..check_tax_lot";

    $_read_dbi = ###Make Database Connection here###

    my $_read_sth = $_read_dbi->prepare($sp);
    if($DBI::err) {
        handle_error_and_exit("**ERROR preparing $sp: " . $DBI::errstr);
    }
    
    $_read_sth->execute();
    if($DBI::err) {
        handle_error_and_exit( "**ERROR executing $sp: " . $DBI::errstr);
    }
        



while (my $row = $_read_sth->fetchall_arrayref)   
   {

   my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n" });
   print @{$_read_sth->{NAME}};
   while (my $r =  $_read_sth->fetch) 
   {
   	$csv->print (*STDOUT, $r);
        
      }
}


##</code><code>##

 
#####Missing the following line????#####
#### Encumberances to look into: #######

####No Return After the First Header Row???####

AccountSymbolSedolSecurity NameEncumberedEnc. qtyBorrow Acct15140EQJP_55121,"DD UN",2018175,"DU PONT (E.I.) DE NEMOURS","BO data mismatch",275000.0,98027EQJP_54981
15140EQJP_55121,"AFL UN",2026361,"AFLAC INC","BO data mismatch",100000.0,29075EQJP_54301
15140EQJP_55121,"SLM UN",2101967,"SLM CORP","BO data mismatch",180000.0,98002EQJP_54161
15140EQJP_55121,"CMI UN",2240202,"CUMMINS INC","BO data mismatch",100000.0,98059EQJP_53421
15140EQJP_55121,"DOW UN",2278719,"DOW CHEMICAL","BO data mismatch",175000.0,98027EQJP_54981






#####Missing Second Resultset all together only printed the first line below, no headers even printed.:#######
"Position integrity:"


##### In the Third Resultset only the first line printed with no headers, but the output is printed..:#######

"91 - LOAN-IP - Loan in progress inconsistencies:"
"No loan in progress inconsistencies found"

##### In the Fourth Resultset only the first line printed with no headers, but the output is printed..:#######

"Loan IP tax_lot without open order?"
"No missing open orders found."


##### In the Fifth Resultset only the first line is printed, no header row neither the data is printed.#######

"Loan tax_lot without loan account (user_field_6) or ""LV Transaction ID"" (user_field_4):"

##### In the Sixth Resultset only the first line is missing but the header rows and data is printed without a return character at the end of the header row#######


accountsymbolsedolsecurity_namequantity28963EQJP_55441,"2379 TT",6051422,"REALTEK SEMICONDUCTOR CORP",-37000.0
15140EQJP_55121,"2379 TT",6051422,"REALTEK SEMICONDUCTOR CORP",-83379.0
15140EQJP_55121,"1301 TT",6348544,"FORMOSA PLASTICS CORP",-1246101.0
15140EQJP_55121,"1326 TT",6348715,"FORMOSA CHEMICALS & FIBRE",-1223781.0
28963EQJP_55441,"BHP AU",6144690,"BHP BILLITON LTD",-873876.0

##### In the Seventh Resultset only the first line printed with no headers, but the output is printed..:#######

"Encmbered quantity differs from quantity on taxlot:"
"No faulty encumbrances found."




##</code><code>##

 															
################################Result Set 1#################################################3

Encumberances to look into: 

Account Symbol Sedol Security Name Encumbered Enc. qty Borrow Acct 

15140EQJP_55121 AFL UN 2026361 AFLAC INC BO data mismatch 100000 29075EQJP_54301 
15140EQJP_55121 SLM UN 2101967 SLM CORP BO data mismatch 180000 98002EQJP_54161 
15140EQJP_55121 CMI UN 2240202 CUMMINS INC BO data mismatch 100000 98059EQJP_53421 
15140EQJP_55121 DOW UN 2278719 DOW CHEMICAL BO data mismatch 175000 98027EQJP_54981 
15140EQJP_55121 GCI UN 2360304 GANNETT CO BO data mismatch 137300 32847EQJP_55601 
15140EQJP_55121 GCI UN 2360304 GANNETT CO BO data mismatch 2900 32847EQJP_55601 
15140EQJP_55121 GCI UN 2360304 GANNETT CO BO data mismatch 39800 32847EQJP_55601 
15140EQJP_55121 MFC CT 2492519 MANULIFE FINANCIAL CORP BO data mismatch 150000 29075EQJP_54301 

#######################################Result Set 2######################################
 
Position integrity: 

lender_account borrow_account sedol symbol security_name encumbered loaned diff 

15140EQJP_55121 98003EQJP_54181 6497071 000700 KP HANJIN SHIPPING 25000 0 25000 
15140EQJP_55121 98003EQJP_54181 6494997 005880 KP KOREA LINE CORP 8160 0 8160 
15140EQJP_55121 98003EQJP_54181 6405869 011200 KP HYUNDAI MERCHANT MARINE 76030 0 






######################################Result Set 3###################################

91 - LOAN-IP - Loan in progress inconsistencies: 

tax_lot_id lender_account borrow_account sedol symbol security_name order_id block_id encumbered quantity_ordered quantity_confirmed quantity_closed quantity_executed unsent_executions quantity_left diff

No loan in progress inconsistencies found 

########################################Result Set 4##################################

Loan IP tax_lot without open order:

account symbol sedol security_name tax_lot_id security_id position_type_code quantity encumbered_quantity 

No missing open orders found.

######################################Result Set 5##########################################

Loan tax_lot without loan account (user_field_6) or "LV Transaction ID" (user_field_4): 

account symbol sedol security_name Enc. qty Loan Account LV Transaction ID 

15140EQJP_55121 FIC UN 2330299 FAIR ISAAC CORP 1105 [NULL] 





###########################################Result Set 6#######################################

Negative tax_lots on index account (Over-encumbered??): 

account symbol sedol security_name quantity 

28963EQJP_55441 2379 TT 6051422 REALTEK SEMICONDUCTOR CORP -37000 
15140EQJP_55121 2379 TT 6051422 REALTEK SEMICONDUCTOR CORP -83379 

#######################################Result Set 7########################################

Encmbered quantity differs from quantity on taxlot: 

account symbol sedol security_name quantity encumbered_quantity encumbered_type description 

No faulty encumbrances found.