#!/bin/sh
#
# Author: Jiltin
# Test email for future cronjobs
#
FILENAME="Company_Invalid_credit_card.xls"
source /usr/bin/production.env
if [ -f $FILENAME ]
then
        rm -rf $FILENAME
        echo "Old Data File ($FILENAME) deleted…"
fi
echo $FILENAME
sqlplus -s username/password@productiondb<<EOF
SET echo OFF
set serveroutput on
SET feedback off
SET heading off
SET linesize 221
SET pagesize 0
SET newpage 0

alter session set current_schema=apps;

spool $FILENAME

DECLARE
        p_api_version           NUMBER          :=      1.0;
        p_init_msg_list         VARCHAR2(240)   :=      NULL;
        x_return_status         VARCHAR2(240);
        x_msg_count                       NUMBER;
        x_msg_data                        VARCHAR2(240);
        x_cc_valid                        BOOLEAN;
        x_cc_type                         VARCHAR2(240);
        x_cc_valid_text         VARCHAR2(20);
CURSOR trx_main IS
        select decode(rct.org_id,262,‘Company China Operating Unit’,283,‘Company Europe Operating Unit’,303,‘Company India Operating Unit’,307,‘Company UK Operating Unit’,311,‘Company Australia Operating Unit’,315,‘Company Japan Operating Unit’,319,‘Company Hong Kong Operating Unit’,223,‘Company US Operating Unit’) Operating_unit,
                   rc.customer_name customer,
                   rct.trx_number invoice_number,
                   aps.AMOUNT_DUE_REMAINING,
                   apa.bank_account_name bank_name,
                   apa.bank_account_num credit_card_number,
                   apa.inactive_date credit_validaity_date,
                   acol.NAME collector_name
        from AP_BANK_ACCOUNTS_ALL apa,
                 ra_customer_trx_all rct,
                 ra_customers rc,
                 ar_payment_schedules_all aps,
                 ra_batch_sources_all rbs,
                 AR_CUSTOMER_PROFILES ACP,
                 AR_COLLECTORS ACOL
        where  1=1
        AND    ACP.CUSTOMER_ID = rc.CUSTOMER_ID
        AND    ACP.COLLECTOR_ID = ACOL.COLLECTOR_ID
        AND    ACP.SITE_USE_ID IS NULL
        and    rct.batch_source_id = rbs.batch_source_id
        and    rct.customer_trx_id = aps.customer_trx_id
        and    rct.org_id = aps.org_id
        and    apa.bank_account_id = rct.customer_bank_account_id
        and    rc.customer_id = rct.bill_to_customer_id
        AND    aPS.gl_date_closed  = TO_DATE(‘4712/12/31′, ‘YYYY/MM/DD’)
        AND    aps.selected_for_receipt_batch_id IS NULL
        and    aps.status                     = ‘OP’
        and    rct.attribute9=‘CREDIT CARD’
        AND    RCT.ORG_ID > 202  
        AND    aps.reserved_type             IS NULL
        AND    aps.reserved_value            IS NULL
        and    aps.AMOUNT_DUE_REMAINING > 0
        order by aps.AMOUNT_DUE_REMAINING desc;
BEGIN
 DBMS_OUTPUT.ENABLE (1000000);
 dbms_output.put_line(‘Operating Unit’”chr(9)”‘Invoice Number’”chr(9)”‘Customer Name’”chr(9)”‘Credit Card Number’”chr(9)”‘Validatity Date’”chr(9)”‘Amount_Due_Remaining’”chr(9)”‘Status’”chr(9)”‘Collector’);
 FOR invoice_main IN trx_main            
        LOOP
        iby_cc_validate.ValidateCC ( p_api_version,     p_init_msg_list,invoice_main.credit_card_number,invoice_main.credit_validaity_date,x_return_status,x_msg_count,x_msg_data,      x_cc_valid,     x_cc_type       );
         if x_cc_valid = true then
           x_cc_valid_text := ‘VALID’;
         else
                x_cc_valid_text := ‘NOT VALID’;
                dbms_output.put_line(to_char(invoice_main.Operating_unit)”chr(9)”to_char(invoice_main.invoice_number)”chr(9)”invoice_main.customer”chr(9)”to_char(invoice_main.credit_card_number)”chr(9)”to_char(invoice_main.credit_validaity_date)”chr(9)”to_char(invoice_main.AMOUNT_DUE_REMAINING)”chr(9)”x_cc_valid_text”chr(9)”invoice_main.collector_name);
        end if;
        END LOOP;
EXCEPTION                              
  WHEN OTHERS THEN                          
          DBMS_OUTPUT.PUT_LINE(‘Error Occurred’ ” SQLERRM );  
END;
/

EOF

if [ -f $FILENAME ]
then
        uuencode $FILENAME $FILENAME ‘ mail -s "Company Invalid Credit Cards Attached to Invoices ! Reported on `date` " "accounts_receivables@notesbit.com" -c "support@notesbit.com"
fi