I am a long-time SQL coder, but a newbie to PERL. Doug"extract.pl" 841 lines, 28112 characters #!/usr/local/pa20_64/bin/perl -w #TURNED ON WARNINGS # # ######################################################### # # $Log: extract.pl,v $ # # Revision 1.6 2010/06/04 mundyda (Doug Mundy) # ECP F20-RM-224 Title: Deductions for Treasury Offset Program (TOP) # # # Revision 1.5 2008/02/28 11:07:56 11:07:56 carusojc (John Caruso) # ECP: Remedy Ticket #181282 Title: Use env variables as in Euro mo +dule # # # Revision 1.4 2005/03/07 11:42:48 11:42:48 lawrenma (Michael A La +wrence) # ECP: F20-IT-009 Title: 8400 Migration # # Environment variable changes required for 8400 # # Revision 1.3 2005/06/27 08:39:44 08:39:44 lawrenma (Michael A La +wrence) # ECP: F20-RN-223 Title: VPIS Extract # # Corrected several bugs identified by Diane in testing # # # Revision 1.2 2005/06/27 08:37:13 08:37:13 lawrenma (Michael A La +wrence) # ECP: F20-RM-223 Title: VPIS Extract # # This change reformatted some of the output records that weren't lini +ng up # correctly and also changed the looping construct # # # Revision 1.1 2004/09/27 10:49:57 10:49:57 coxrir (Richard R Cox) # Initial revision # # ######################################################### "extract.pl" 841 lines, 28112 characters # ######################################################### # use DBI; # this wonderful little perl creates the extract..... ###################################################################### +#### ## a world of perl and oracle wonderment to behold lays before you $ENV{PATH} = "/opt/ap/crp/saves/conus/output/vpis"; ##################### database connection #my $Dbh= DBI->connect("dbi:Oracle:pcrpi","$ENV{SQL_USER}/$ENV{PW}") # or die "Can't connect to Oracle database: $DBI::errstr\n"; my $Dbh= DBI->connect("dbi:Oracle:tcrpi.deca.mil","mike/zygote") or die "Can't connect to Oracle database: $DBI::errstr\n"; print "connected... processing input.\n"; ######################################################## # # Extract the valid payment dates # ######################################################## $completed_flg = undef; $extract_date = undef; $e_date_quoted = undef; $mystat = undef; #Added for Revision 1.6 $sth0=$Dbh->prepare("select completed_flg, extract_date from vpis_extract where completed_flg <> 'Y'"); $sth0->execute(); if ( ! $sth0->execute() ) { my $err = $DBI::errstr; $sth0->finish()}; $sth0->bind_columns(\$completed_flg, \$extract_date); print "extract date: $extract_date\n"; ######################################################## # # Begin loop of all payment dates # ######################################################## while ( $sth0->fetch){ print "extract date was: $extract_date completed_flg was: $ +completed_flg \n"; #my $e_date_quoted = $dbh->quote($extract_date); #my $filename = ">/opt/ap/crp/saves/conus/output/vpis/ +outfile.$extract_date"; my $filename = ">/home/mundyda/outfile.$extract_date"; + #TEST ONLY # my $filename = ">outfile.$extract_date"; open (outfile, $filename ) or die "File failed to initialize. +- 1 Extract failed 1\n"; ######################################################## # # Extract the history payment data # ######################################################## $contract_no = undef; $call_no = undef; $cage_cd = undef; $dunns_plus4 = undef; $check_no = undef; $voucher_no = undef; $invoice_no = undef; $eft_no = undef; $invoice_issue_dt = undef; $payment_dt = undef; $invoice_amt = undef; $check_eft_amt = undef; $discount_amt = undef; $interest_amt = undef; $pay_status = undef; $invoice_recv_dt = undef; $merch_accept_dt = undef; $paying_dssn = undef; $gross_inv_amt = undef; $due_dt = undef; $receiving_bank_id = undef; $adjustment_amount_1 = undef; $adjustment_reason_1 = undef; $adjustment_amount_2 = undef; $adjustment_reason_2 = undef; $account_number = undef; $currency_code = undef; $address_1 = undef; $address_2 = undef; $address_3 = undef; $payee_name = undef; $tin = undef; $vendor_code = undef; $net_due_date = undef; #added for Revision 1.6 $status = undef; #added for Revision 1.6 $space = " "; $sth1=$Dbh->prepare(" select a.contracting_office_text||h.piin as CONTRACT_NO, substr(h.dodaac, 4, 3)||h.piin||h.call_no as CAL +L_NO, b.cage_code as CAGE_CD, b.cec_code as DUNNS_PLUS4, vo.check_no, h.dov_no as VOUCHER_NO, h.invoice_no, vo.eft_trace as EFT_NO, to_char(h.dovi, 'YYYYMMDD') as INVOICE_ISSUE_DT, to_char(h.dov_date, 'YYYYMMDD') as PAYMENT_DT, h.invoice_amt, h.dov_amt as CHECK_EFT_AMT, decode(greatest(discount_due_date, h.dov_date), +discount_due_date, h.discount_amt,0) as DISCOUNT_AMT, h.penalty_amt as INTEREST_AMT, decode(h.status, 90, 1, 60, 6, 2) as PAY_STATUs, to_char(h.doiar, 'YYYYMMDD') as INVOICE_RECV_DT, to_char(h.doras, 'YYYYMMDD') as MERCH_ACCEPT_DT, s.fao_dssn as PAYING_DSSN, (h.invoice_amt+h.adj_invoice_amt+h.adj_invoice_a +mt_2+h.adj_invoice_amt_3+h.adj_invoice_amt_4+h.adj_invoice_amt_5+h.ad +j_i nvoice_amt_6) as GROSS_INV_AMT, to_char(h.dov_date, 'YYYYMMDD') as DUE_DT, f.routing_no as RECEIVING_BANK_ID, h.adj_invoice_amt as ADJUSTMENT_AMOUNT_1, h.reason_code_i as ADJUSTMENT_REASON_1, decode(h.edi_disc_amt, 0,h.adj_invoice_amt_2 + h.adj_invoice_amt +_3 + h.adj_invoice_amt_4 + h.adj_invoice_amt_5 + h.adj_invoice_amt_6, h.edi_disc_amt) as ADJUSTMENT_AMOUNT_2, decode(h.edi_disc_amt, 0,h.reason_code_i_2, 'SWELL') as ADJUSTMENT_REASON_2, f.account_no as ACCOUNT_NUMBER, h.currency_code, h.address_line_1 as ADDRESS_1, h.address_line_2 as ADDRESS_2, h.city|| ',' ||h.state_cd||' '||h.zip_code as AD +DRESS_3, h.company_name as PAYEE_NAME, e.tin, h.vendor_code, h.net_due_date as NET_DUE_DATE, #Added for Revis +ion 1.6 h.status as STATUS #Added for Revision 1.6 from historydb h, voucher vo, cdb c, cont_office a, vdb b, + eft f, vcodedb e, svcuniqdb s where h.dov_no = vo.dov_no and h.dov_date = vo.dov_date and h.piin = c.piin and c.status = 20 and c.contracting_office_id = a.contracting_office_id and c.mail_to_code = b.address_code and c.vendor_code = b.vendor_code and c.vendor_code = f.vendor_code (+) and c.eft_addr_code = f.eft_addr_code (+) and f.status (+) = 40 and h.vendor_code = e.vendor_code and h.dov_date = ? "); $sth1->bind_param( 1, $extract_date);
In reply to Bind param error by doug145
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |