8 Replies Latest reply: Oct 3, 2013 4:58 PM by rp0428 RSS

    Pl/SQL procedure

    e61728c3-7289-450c-a4c4-66a79700916a

      The procedure appears to be failing when it hits a Wire transfer (Record #6800).   When it aborts the filename is not getting updated to “xloaded_”<filename> so it keeps attempting to reload the same file and is creating duplicate batches until the filenames are reloaded. 

      LOAD_LOCKBOX_FILE procedure to find out why it’s failing when it hits a Wire Transfer Entry.

      Wire Transfers should import into CODI exactly like a Standard Check imports with the exception of one field called Payment Type (ar_payment_header.payment_type).   It should have a value of ‘Check’ for standard checks and ‘Wire Transfer’ for wire transfers.    There are typically records created in three tables for this process (ar_batch, ar_payment_header, ar_payment_detail).   The Wire Transfer is creating the ar_batch entry (as seen above for batch ID “PYL09252013-1”), but there is no ar_payment_header or ar_payment_detail entry.

      PLEASE HELP ME OUT TO FIGURE WHAT THE PROBLEM IS


      Prodcedure


       

      CREATE OR REPLACE PROCEDURE CO_DEVB.load_lockbox_file

      IS

       

         tmpfile1 utl_file.file_type;

         inline           VARCHAR2 (32767);

         tmpname          VARCHAR2 (500);

         tmpname_no_txt   VARCHAR2 (500);

         xline_ctr        NUMBER;

         v_directory_name VARCHAR2 (100);

         v_directory_path VARCHAR2 (100); -- := '\\lighthouse-1\lockbox_files';

         tmpdir           VARCHAR2 (200);

         --CH3438 ---------------------------------------------------------------

         file_done        BOOLEAN  :=FALSE;

         unproc_files     NUMBER(2);  --number of unprocessed files in folder

         --CH3438 ---------------------------------------------------------------

        

         CURSOR cursor_filename

         IS

            SELECT filename

            FROM ar_dir_list

            WHERE (upper (filename) LIKE '%'

               ||'.TXT')

            AND upper (filename) NOT LIKE 'XLOADED%.TXT'

            ORDER BY filename;

       

         v_sales_year               NUMBER(4);

         v_prev_sales_year          NUMBER(4);

         v_batch_base               VARCHAR2(11);

         v_batch_description        VARCHAR2(50);

         v_created_by               VARCHAR2(30);

         v_batch_hdr_cnt            NUMBER(4)  :=0;

         v_batches_cnt              NUMBER(4)  :=0; 

         v_pymt_batch_exists        NUMBER(1);

         v_tot_payment_amount       NUMBER(12,2);

         v_num_remittances          NUMBER(4);

         v_batch_num_remittances    NUMBER(4);

         v_batch_tot_payment_amount NUMBER(12,2);

         v_billable_account_num     NUMBER(6);

         v_batch_id                 VARCHAR2(20);

         v_batch_type               VARCHAR2(20) :='Payment Batch';

         v_status                   VARCHAR2(2)  :='OP';

         v_hld_six_batch_num        VARCHAR2(3);

         v_hld_six_item_num         VARCHAR2(3);

         v_lkbx_hp_trip_ind         VARCHAR2(1);

         v_lkbx_except_reason       VARCHAR2(4);

         v_hld_four_batch_num       VARCHAR2(3);

         v_hld_four_item_num        VARCHAR2(3);

         v_hld_four_rec_type_9      VARCHAR2(1);

         v_discount_percent         NUMBER(5,2);

         v_discount_amount          NUMBER(14,2);

         v_lockbox_bank             VARCHAR2(2);

         v_bank_id                  VARCHAR2(2);

         v_payment_type_a           VARCHAR2(30) :='ACH';

         v_payment_type_c           VARCHAR2(30) :='Checks';

         v_payment_type_w           VARCHAR2(30) :='Wire';

         v_is_ach                   VARCHAR2(1)  :='N';

        

         v_payment_type             VARCHAR2(30);

         v_proc                     NUMBER(3);

         v_order_num                NUMBER(8);

         v_ar_payment_num           NUMBER(11);

         v_lkbx_dealer_num          NUMBER(6);

         v_payment_class            VARCHAR2(20) :='Customer Payment';

         v_acct_status              VARCHAR(1);

         v_is_harv_plan             VARCHAR(1);

         v_hp_balance               NUMBER(14,2);

         v_previous_yr_balance      VARCHAR(1);

         v_comment_ind              NUMBER(2);

         v_curr_yr_bal_amt          NUMBER(14,2);

         v_prev_yr_bal_amt          NUMBER(14,2);

         v_balance_ind              VARCHAR(1);

         v_check_for_op_payments    NUMBER(4);

         v_prev_yr_amt_apply        NUMBER(14,2);

         v_curr_yr_amt_apply        NUMBER(14,2);

         v_sequence_no              NUMBER(4);

         v_is_split_yrs             VARCHAR2(1);

         v_newfile_name             VARCHAR2(255);

         v_line1                    VARCHAR2(500);

         v_line2                    VARCHAR2(500);

         l_crlf                     VARCHAR2(2) := CHR (13) || CHR (10);

         l_cr                       VARCHAR2(1) := CHR (13);

         l_lf                       VARCHAR2(1) := CHR (10);

         v_msg_lines                VARCHAR2(5000);

         v_no_name                  VARCHAR2(25) :='No Name on detail in file';

         v_acct_msg                 VARCHAR2(25) :=' - VERIFY Acct#/Name.';

         v_name_use                 VARCHAR2(50);

         --

         l_completed VARCHAR2(9)   :='COMPLETED';

         l_no_file   VARCHAR2(8)   :='NO FILES';

         l_sender    VARCHAR2(40)  :='CODI Administrator<codi@channelbio.com>';

         l_subject   VARCHAR2(100) :='Automated Lockbox Processing -- '||l_completed;

         l_email ctl_system_variables.value_str%TYPE;

         l_email_bcc ctl_system_variables.value_str%TYPE;

         l_email_cc ctl_system_variables.value_str%TYPE;

         l_message VARCHAR2(32000);

         l_attach  VARCHAR2(32000);

         l_email_batch_id   VARCHAR2(11); 

         --

         l_rec_ind                VARCHAR2(1);

         l_record_type            VARCHAR2(1);

         l_rec_cnt                NUMBER(4);

         l_lockbox_name           VARCHAR2(10); -- from rec_type 1

         l_lockbox_date_alpha     VARCHAR2(6);

         l_lockbox_creation_date  DATE;

         l_lockbox_batch_num      VARCHAR2(3);

         l_lockbox_batch_item_num VARCHAR2(3);

         l_lkbx_dealer_num        NUMBER(6);

         l_lkbx_comments          VARCHAR2(250);

         l_lkbx_message           VARCHAR2(32000);

         l_lkbx_message2          VARCHAR2(32000);

         l_lkbx_issues            NUMBER(10);

         l_lkbx_header_acct_num   NUMBER(6);

         --

         -- BAI Record Formats -- per document

         --

         l_record_type VARCHAR2(1); -- record type indentifier (1 -- Transmission Header

         --                          5 -- Batch Header

         --                          6 -- Detail Record

         --                          4 -- Overflow record -- detail

         --                          7 -- Batch Total

         --                          8 -- Trailer

         --                          9 -- Trailer

         --

         -- Transmission Header - record type 1

         --

         l_trans_header_rec_type    VARCHAR2(1);

         l_trans_header_priority    VARCHAR2(2);  -- Priority

         l_trans_header_destination VARCHAR2(10); -- Company's account number at sending bank

         l_trans_header_origin      VARCHAR2(10); -- Sending bank's FR/ABA Number

         l_trans_header_date_alpha  VARCHAR2(6);  -- Transmission date (YYMMDD) -- alpha

         l_trans_header_date        DATE;         -- Transmission date (YYMMDD) -- Oracle date format

         l_trans_header_time        VARCHAR2(4);  -- Time -- NOT USED

         l_trans_header_filler      VARCHAR2(47); -- Filler - Blank

         --

         -- Batch Header -- record type 5

         --

         l_batch_header_rec_type    VARCHAR2(1);

         l_batch_header_batch_no    VARCHAR2(3);  -- Zero filled for Customer Payment, 900 for ACH, 800 for Wire

         l_batch_header_item_no     VARCHAR2(3);  -- Zero filled, NOT USED

         l_batch_header_lockbox_no  VARCHAR2(7);  -- Lockbox Number

         l_batch_header_date_alpha  VARCHAR2(6);  -- Deposit date at sending bank (YYMMDD) -- alpha

         l_batch_header_date        DATE;         -- Deposit date at sending bank (YYMMDD) -- Oracle date format

         l_batch_header_destination VARCHAR2(10); -- Company's account number at sending bank

         l_batch_header_origin      VARCHAR2(10); -- The sending bank's FR/ABA number (0TTTTAAAAC)

         l_batch_header_filler      VARCHAR2(40); -- Filler - Blank

         --

         -- Detail Record -- record type 6

         --

         l_batch_detail_rec_type       VARCHAR2(1);

         l_batch_detail_batch_no       VARCHAR2(3);  -- Sequentially assigned number for each batch of items deposited

         l_batch_detail_item_no        VARCHAR2(3);  -- Sequentially assigned number indicating the position of an item within a batch

         l_batch_detail_amount_alpha   VARCHAR2(10); -- Dollar amount of each check deposited -- alpha

         l_batch_detail_amount         NUMBER(10,2); -- Dollar amount of each check deposited -- converted to numeric

         l_batch_detail_route_no_lkbx  VARCHAR2(9);  -- Transit routing number for Lockbox

         l_batch_detail_account_number VARCHAR2(10); -- Remitter's account number parsed from l_batch_detailremitter_id

         l_batch_detail_check_no       VARCHAR2(9);  -- Check number of each check deposited

         l_batch_detail_trp_ind        VARCHAR2(1);  -- Trip or Harvest Plan indicator

         l_batch_detail_chk_date_a     VARCHAR2(6);  -- Check date alpha

         l_batch_detail_chk_date       DATE;         -- Check date Oracle format

         l_batch_detail_name           VARCHAR2(36); -- Name on check

         --

         -- Overflow record -- record type 4

         --

         l_batch_detail_ovfl_rec_type   VARCHAR2(1);

         l_batch_detail_ovfl_batch_no   VARCHAR2(3); -- Overflow batch no -- same as Detail Batch No in record 6

         l_batch_detail_ovfl_item_no    VARCHAR2(3); -- Overflow item no -- same as Detail Item No in record 6

         l_batch_detail_ovfl_rec_type_6 VARCHAR2(1); -- Constant 6 -- indicates that record is associated with previous 6 record

         l_batch_detail_ovfl_seq_no     VARCHAR2(2); -- Sequentially assigned number -- for overflow record

         l_batch_detail_ovfl_rec_type_9 VARCHAR2(1); -- This field is '9' if the record is the last of a series of overflows for a given

                                                     -- check record (detail record 6, otherwise field is '0'

         l_batch_detail_ovfl_cust_no  VARCHAR2(6);   -- Customer Number

         l_batch_detail_ovfl_order_no VARCHAR2(8);   -- Order Number

         l_batch_detail_ovfl_name     VARCHAR2(50);  -- Customer Name

         l_type4_rec_no                NUMBER;       -- counter of type 4 records in type 6 batch

         --

      BEGIN

         xline_ctr := 0;

         tmpname   := ' ';

         SELECT VALUE_STR

         INTO v_directory_name

         FROM ctl_system_variables

         WHERE variable_name ='DIR_LOCKBOX';

         dbms_output.put_line('v_directory_name is '||v_directory_name);

         SELECT DIRECTORY_PATH

         INTO v_directory_path

         FROM dba_directories

         WHERE directory_name = v_directory_name;

         dbms_output.put_line('v_directory_path is '||v_directory_path);

         ar_get_dir_list (v_directory_path);

       

         BEGIN

         -- Check to see how many unprcoessed file exist in the folder

         --CH3438 ---------------------------------------------------------------

           begin

             select count(*)

               into unproc_files

               from ar_dir_list

              where (upper(filename) like '%'

                   ||'.TXT')

                and upper (filename) not like 'XLOADED%.TXT';

           end;    

         --CH3438 ---------------------------------------------------------------

           if unproc_files = 0 then

             --dbms_output.put_line('NO FILES FOUND TO PROCESS');

             BEGIN

               l_sender       := pkg_ctl.get_value_str('Standard Email from');

               l_email        := pkg_ctl.get_value_str('Auto Lkbx Notify');

               l_email_cc     := pkg_ctl.get_value_str('Auto Lkbx Notify cc');

               l_email        := l_email ||';'||l_email_cc;

               l_email_bcc    := pkg_ctl.get_value_str('Auto Lkbx Notify bcc');

               l_subject      :='Automated Lockbox Processing -- '||l_no_file;

               l_lkbx_message :='NO Current Lockbox files were found or the files were previously loaded.'|| chr(13) || chr(10);

               l_message      :=l_lkbx_message;

               pkg_mail.send_email_no_attach(l_sender,l_email,l_email_bcc,l_subject,l_message,' ');

             END;

            

           else

       

             BEGIN

               OPEN cursor_filename;

                 LOOP

       

                   FETCH cursor_filename INTO tmpname;

                   EXIT WHEN cursor_filename%NOTFOUND;

                   -----

                   l_rec_cnt                  :=0;

                   v_batch_hdr_cnt            :=0;

                   v_pymt_batch_exists        :=0;

                   v_tot_payment_amount       :=0;

                   v_num_remittances          :=0;

                   v_batch_num_remittances    :=0;

                   v_batch_tot_payment_amount :=0;

                   v_billable_account_num     :=NULL;

                   l_lkbx_issues              :=0;

                   l_lkbx_header_acct_num     :=0;

                    

                   dbms_output.put_line('Filename === '||tmpname);

                   ----- OPEN FILE TO PROCESS ----

                   tmpfile1 := utl_file.fopen (v_directory_name,tmpname,'r');

                   inline   := NULL;

                    

                   IF utl_file.is_open(tmpfile1) THEN

                     dbms_output.put_line('Processing '||tmpname);

                     v_batch_description :=NULL;

                     v_created_by        :=NULL;

                   END IF;

                    

                   LOOP

                     utl_file.get_line (tmpfile1,inline);

                     --dbms_output.put_line(inline);

                     l_rec_ind := SUBSTR(inline,1,1);

                     ----- Record Type 1 -----

                     IF l_rec_ind = '1' THEN

                       v_sales_year               :=pkg_ctl.get_financial_year;

                       v_prev_sales_year          := v_sales_year -1;

                       v_batch_base               := 'PYL'||TO_CHAR(SYSDATE,'MMDDRRRR');

                       l_lockbox_name             :=SUBSTR(inline,4,10);

                       l_lockbox_date_alpha       :=SUBSTR(inline,24,6);

                       l_lockbox_creation_date    :=to_date(l_lockbox_date_alpha,'YYMMDD');

                       l_rec_cnt                  := l_rec_cnt + 1;

                       l_trans_header_rec_type    :=SUBSTR(inline,1,1);

                       l_trans_header_priority    :=SUBSTR(inline,2,2);

                       l_trans_header_destination :=SUBSTR(inline,4,10);

                       l_trans_header_origin      :=SUBSTR(inline,14,10);

                       l_trans_header_date_alpha  :=SUBSTR(inline,24,6);

                       l_trans_header_date        :=to_date(l_trans_header_date_alpha,'YYMMDD');

                       l_trans_header_time        :=SUBSTR(inline,30,4);

                       --CH3438 ----------------------------------------------------------

                       -------------------------------------------------------------------

                       -- Identify the bank for Batch Header

                       -------------------------------------------------------------------

                       v_lockbox_bank   :=pkg_lockbox.get_lockbox_bank_id(l_lockbox_name);

                       -------------------------------------------------------------------

                       -------------------------------------------------------------------

                     ---- Record Type 5 -----

                     ELSIF l_rec_ind = '5' THEN

                       --dbms_output.put_line(inline);

                       l_rec_cnt                := l_rec_cnt + 1;

                       l_lockbox_batch_num      :=SUBSTR(inline,2,3); -- batch number -- hold this to compare rec_ind 6s to

                       l_lockbox_batch_item_num :=SUBSTR(inline,5,3); -- batch item number

                       v_batch_id               :=NULL;

                       pkg_lockbox.assign_lkbx_batch(v_sales_year,v_batch_base,v_batch_id,v_batch_description,v_created_by);

                       --CH3438 ----------------------------------------------------------

                       -------------------------------------------------------------------

                       -- This is to be added for change of Banks. Identify Banks as Wells Fargo or Northern Trust,

                       -- might also be sending files for awhile.

                       -------------------------------------------------------------------

                       v_batch_description := v_lockbox_bank||'-'||v_batch_description||' '||l_lockbox_batch_num||l_lockbox_batch_item_num;

                       --

                       if l_lockbox_batch_num = '900' then

                          v_is_ach := 'Y';

                       else

                          v_is_ach := 'N';

                       end if;

                             

                       if l_lockbox_batch_num = '800' then

                         l_batch_header_date_alpha  :=SUBSTR(inline,15,6);

                         l_batch_header_date        :=to_date(l_batch_header_date_alpha,'YYMMDD');

                         v_payment_type :='Wire Transfer';

                         v_batch_description := v_batch_description||'-'||v_payment_type_w;

                       else

                         v_payment_type :='Check';

                         l_batch_header_date_alpha  :=l_trans_header_date_alpha;

                         l_batch_header_date        :=l_trans_header_date;

                         v_batch_description := v_batch_description||'-'||v_payment_type_c; 

                       end if;

                      

                       if v_is_ach ='N' then

                        

                         pkg_lockbox.insert_lkbx_ar_batch(v_sales_year, v_batch_id, v_batch_type, v_batch_description,

                                                          l_batch_header_date,   --l_lockbox_creation_date,

                                                          v_created_by, v_status);

                       -------------------------------------------------------------------

                       -------------------------------------------------------------------

                         v_batch_hdr_cnt            :=v_batch_hdr_cnt + 1;

                         l_email_batch_id           :=substr(v_batch_ID,1,11);

                        

                        

                        

                        

                         l_batch_header_rec_type    :=SUBSTR(inline,1,1);

                         l_batch_header_batch_no    :=SUBSTR(inline,2,3);

                         l_batch_header_item_no     :=SUBSTR(inline,5,3);

                         l_batch_header_lockbox_no  :=SUBSTR(inline,8,7);

                         l_batch_header_date_alpha  :=SUBSTR(inline,15,6);

                         l_batch_header_date        :=to_date(l_batch_header_date_alpha,'YYMMDD');

                         l_batch_header_destination :=SUBSTR(inline,21,10);

                         l_batch_header_origin      :=SUBSTR(inline,31,10);

                       end if;

                      

                     ----- Record Type 6 -----

                     ELSIF l_rec_ind = '6' THEN

                       if v_is_ach ='N' then

                         v_sequence_no := 0;

                         l_type4_rec_no := 0;

                         l_batch_detail_rec_type       :=SUBSTR(inline,1,1);

                         l_batch_detail_batch_no       :=SUBSTR(inline,2,3);

                         l_batch_detail_item_no        :=SUBSTR(inline,5,3);

                         l_batch_detail_amount_alpha   :=SUBSTR(inline,8,10);

                         l_batch_detail_amount         :=to_number(l_batch_detail_amount_alpha)*.01;

                         l_batch_detail_route_no_lkbx  :=SUBSTR(inline,18,9);

                         l_batch_detail_account_number :=SUBSTR(inline,27,10);

                         l_batch_detail_check_no       :=rtrim(SUBSTR(inline,37,9));

                         l_batch_detail_trp_ind        :=SUBSTR(inline,46,1);

                         l_batch_detail_chk_date_a     :=SUBSTR(inline,47,6);

                     

                         IF l_batch_detail_chk_date_a = '      ' THEN

                           l_batch_detail_chk_date_a :=l_batch_header_date_alpha;

                         END IF;

       

                         l_batch_detail_chk_date :=to_date(l_batch_detail_chk_date_a,'YYMMDD');

       

                         l_batch_detail_name     :=SUBSTR(inline,53);

                         v_hld_six_batch_num     :=l_batch_detail_batch_no;

                         v_hld_six_item_num      :=l_batch_detail_item_no;

                         l_lockbox_creation_date :=to_date(l_batch_header_date_alpha,'YYMMDD');

                         --- check trip/harvest plan indicator ----

                         v_lkbx_hp_trip_ind   :=NULL;

                         v_lkbx_except_reason :=NULL;

                         pkg_lockbox.check_trip_hp_ind(l_batch_detail_trp_ind,v_lkbx_hp_trip_ind,v_lkbx_except_reason);

                       end if;

                      

                     ----- Record Type 4 -----

                     ELSIF l_rec_ind = '4' THEN

                       if v_is_ach ='N' then

                         l_type4_rec_no := l_type4_rec_no + 1;

                         v_prev_yr_amt_apply        := 0;

                         v_curr_yr_amt_apply        := 0;

                         l_batch_detail_ovfl_rec_type   :=SUBSTR(inline,1,1);

                         l_batch_detail_ovfl_batch_no   :=SUBSTR(inline,2,3);

                         l_batch_detail_ovfl_item_no    :=SUBSTR(inline,5,3);

                         l_batch_detail_ovfl_rec_type_6 :=SUBSTR(inline,8,1);

                         l_batch_detail_ovfl_seq_no     :=SUBSTR(inline,9,2);

                         l_batch_detail_ovfl_rec_type_9 :=SUBSTR(inline,11,1);

                         l_batch_detail_ovfl_cust_no    :=NVL(rtrim(SUBSTR(inline,12,6),' '),NULL);

                         l_batch_detail_ovfl_order_no   :=rtrim(SUBSTR(inline,18,8),' ');

                         l_batch_detail_ovfl_name       :=nvl(rtrim(SUBSTR(inline,26),' '),v_no_name);

       

                         IF l_batch_detail_ovfl_cust_no IS NULL THEN

                           l_batch_detail_ovfl_cust_no := NULL;

                           v_billable_account_num      :=NULL;

                           l_lkbx_dealer_num           :=NULL;

                           v_comment_ind               := 1;

                           l_lkbx_comments             :='No Customer Account# for '||l_batch_detail_ovfl_name||' in LockBox file.';

                           l_lkbx_header_acct_num      :=v_billable_account_num;

                           l_lkbx_issues               :=l_lkbx_issues + 1;                    

                         ELSIF IS_NUMERIC(l_batch_detail_ovfl_cust_no) = 'FALSE' OR pkg_account.get_billable_account(l_batch_detail_ovfl_cust_no) IS NULL

                           THEN

                             l_batch_detail_ovfl_cust_no := NULL;

                             v_billable_account_num      :=NULL;

                             l_lkbx_dealer_num           :=NULL;

                             v_comment_ind               := 1;

                             l_lkbx_comments             :='Customer Account# for '||l_batch_detail_ovfl_name||' not found.';

                             l_lkbx_header_acct_num      :=v_billable_account_num;

                             l_lkbx_issues               :=l_lkbx_issues + 1;

                         ELSE

                           v_billable_account_num := pkg_account.get_billable_account(v_sales_year,l_batch_detail_ovfl_cust_no,v_lkbx_hp_trip_ind);

                           l_lkbx_dealer_num      :=pkg_lockbox.get_lkbx_dealer_num(l_batch_detail_ovfl_cust_no);

                           v_acct_status          :=pkg_lockbox.get_account_status(l_batch_detail_ovfl_cust_no);

                           IF v_acct_status       IS NULL THEN

                             l_lkbx_comments     :=NULL;

                           ELSE

                             l_lkbx_comments :=(l_lkbx_comments||''||'Customer Account# '||l_batch_detail_ovfl_cust_no||' NOT ACTIVE.');

                             l_lkbx_issues   :=l_lkbx_issues + 1;

                           END IF;

                           l_lkbx_header_acct_num :=v_billable_account_num;

                         END IF;

       

                         v_is_harv_plan                := 'N';

       

                         IF v_lkbx_hp_trip_ind          = 'Y' THEN

                           v_is_harv_plan             :=pkg_lockbox.get_harvest_plan_status(v_sales_year,v_billable_account_num);

                           IF NVL(v_is_harv_plan,'N') <> 'Y' THEN

                             v_is_harv_plan          :=pkg_lockbox.get_harvest_plan_status(v_prev_sales_year,v_billable_account_num);

                           END IF;

       

                           IF NVL(v_is_harv_plan,'N') <> 'Y' THEN

                             l_lkbx_comments    :=('Customer Account# '||v_billable_account_num||' not a Harvest Plan account');

                             l_lkbx_issues      :=l_lkbx_issues + 1;

                             v_lkbx_hp_trip_ind := 'N';

                           END IF;

                         END IF;

       

                         v_previous_yr_balance :=pkg_lockbox.does_previous_bal_exists(v_sales_year,v_billable_account_num,v_is_harv_plan);

                         v_prev_yr_bal_amt     :=pkg_lockbox.get_prev_yr_bal_amt(v_prev_sales_year,v_billable_account_num,v_is_harv_plan);

                         v_curr_yr_bal_amt     :=pkg_lockbox.get_curr_yr_bal_amt(v_sales_year,l_batch_detail_ovfl_cust_no);

                         v_prev_yr_amt_apply   :=0;

                         v_curr_yr_amt_apply   :=0;

                         v_is_split_yrs        :='N';

       

                         pkg_lockbox.yr_amt_to_apply(v_sales_year, v_billable_account_num, v_is_harv_plan, l_batch_detail_amount,

                                                     v_prev_yr_bal_amt, v_curr_yr_bal_amt, v_prev_sales_year, v_sales_year,

                                                     v_prev_yr_amt_apply, v_curr_yr_amt_apply, v_is_split_yrs);

       

                         IF v_prev_yr_amt_apply > 0 THEN

                           l_lkbx_comments    :=('Customer Account# '||l_batch_detail_ovfl_cust_no||' has a Previous Yr Balance.');

                         ELSE

                           If l_lkbx_comments IS NULL Then

                             l_lkbx_comments :=NULL;

                           End If;  

                         END IF;

       

                         IF l_batch_detail_trp_ind = 'T' THEN

                           v_discount_percent   :=0;

                           v_discount_amount    :=0;

                         ELSE

                           v_discount_percent   :=0;

                           v_discount_percent    :=pkg_pricing.get_discount_percent(v_sales_year,v_billable_account_num,v_payment_type ,trunc(sysdate));

                           v_discount_amount    :=0;

                           v_discount_amount    :=pkg_lockbox.calc_discount(l_batch_detail_amount,v_discount_percent);

                           v_lkbx_except_reason :=NULL;

                         END IF;

       

                         IF l_batch_detail_ovfl_order_no IS NULL THEN

                           v_order_num                  :=NULL;

                         ELSE

                           v_order_num :=to_number(l_batch_detail_ovfl_order_no);

                         END IF;

       

                         v_lkbx_dealer_num :=l_lkbx_dealer_num;

       

                         if l_batch_detail_trp_ind = 'H' THEN

                           v_lkbx_hp_trip_ind := 'Y';

                         else

                           v_lkbx_hp_trip_ind := v_lkbx_hp_trip_ind;  

                         end if;

                                                                      

                         ----- write header and detail -----

                         ----- insert row into AR_PAYMENT_HEADER -----

                         IF v_sequence_no     = 0 THEN

                           v_ar_payment_num :=get_next.ar_payment_num(v_sales_year);

                             

                           IF l_batch_detail_ovfl_name = v_no_name

                             and l_batch_detail_ovfl_cust_no is not null THEN

                               l_lkbx_comments := v_no_name||v_acct_msg;

                           ELSIF l_batch_detail_ovfl_name = v_no_name

                             and l_batch_detail_ovfl_cust_no is null THEN 

                               l_lkbx_comments := 'No Account # or Name on detail record for '||l_batch_detail_name;

                               --l_lkbx_comments := v_no_name;

                           END IF; 

                              

                           pkg_lockbox.insert_ar_payment_header(v_sales_year, v_ar_payment_num, v_batch_id, v_payment_class, l_batch_detail_ovfl_cust_no,

                                                                v_payment_type, l_batch_detail_check_no, l_batch_detail_amount, TRUNC(sysdate),

                                                                v_created_by, v_status, l_batch_detail_chk_date, l_lkbx_comments);

                         END IF;

       

                         IF v_curr_yr_amt_apply <> 0 OR (v_curr_yr_amt_apply = 0 AND v_prev_yr_amt_apply = 0) THEN

                           v_sequence_no       := v_sequence_no + 1;

                           v_name_use          :=null;

                           if l_type4_rec_no > 1 then

                             v_curr_yr_amt_apply := 0;

                             v_discount_amount := null;

                           end if;

                                

                           if l_batch_detail_ovfl_name = v_no_name

                             and l_batch_detail_ovfl_cust_no is not null then

       

                               begin

                                 v_name_use := null;

                                 select contact_name_lf

                                   into v_name_use

                                   from v_per_contact_info

                                  where contact_num = l_batch_detail_ovfl_cust_no

                                    and contact_type = 'Primary';

                               end;    

                           else

                             v_name_use :=l_batch_detail_ovfl_name;

                           end if;

                             

                           pkg_lockbox.insert_ar_payment_detail(v_sales_year, v_ar_payment_num, v_sequence_no, v_sales_year,

                                                                l_batch_detail_ovfl_cust_no, v_curr_yr_amt_apply, v_name_use,

                                                                v_lkbx_hp_trip_ind, v_lkbx_except_reason, v_billable_account_num,

                                                                v_discount_percent, v_discount_amount, v_order_num);

                         END IF;

       

                         IF v_prev_yr_amt_apply <> 0 THEN

                           v_sequence_no       := v_sequence_no + 1;

                           v_name_use          :=null;

       

                           if l_type4_rec_no > 1 then

                             v_prev_yr_amt_apply := 0;

                             v_discount_amount := null;

                           end if;  

       

                           if l_batch_detail_ovfl_name = v_no_name

                             and l_batch_detail_ovfl_cust_no is not null then

                               begin

                                 v_name_use := null;

                                 select contact_name_lf

                                   into v_name_use

                                   from v_per_contact_info

                                  where contact_num = l_batch_detail_ovfl_cust_no

                                    and contact_type = 'Primary';

                               end;

                           else

                             v_name_use :=l_batch_detail_ovfl_name;

                           end if;

       

                           pkg_lockbox.insert_ar_payment_detail(v_sales_year, v_ar_payment_num, v_sequence_no, v_prev_sales_year,

                                                                l_batch_detail_ovfl_cust_no, v_prev_yr_amt_apply, v_name_use,

                                                                v_lkbx_hp_trip_ind, NULL, v_billable_account_num,0, 0, v_order_num);

                         END IF;

                       end if;

                     ----- Record Type 7 -----

                     ELSIF l_rec_ind = '7' THEN

                       NULL;

                     ----- Record Type 8 -----

                     ELSIF l_rec_ind = '8' THEN

                       NULL;

                     ----- Record Type 9 -----

                     ELSIF l_rec_ind = '9' THEN

                       v_proc := v_proc + 1;

                       --dbms_output.put_line('Last Record in File '||tmpname);

                        

                       utl_file.fclose(tmpfile1);

                       --

                       v_newfile_name :='xloaded_'||tmpname;

       

                       --dbms_output.put_line('New File Name is '||v_newfile_name);   

       

                       utl_file.frename (v_directory_name,tmpname,v_directory_name,v_newfile_name,FALSE);

                     

                       if v_proc < unproc_files then

                         goto next_file;

                       else

                         --dbms_output.put_line('HERE'); 

                         exit;

                       end if;

                             

                     END IF;

                     <<next_file>>

                     null;

                      

                   END LOOP;

       

                   BEGIN

                     l_sender       := pkg_ctl.get_value_str('Standard Email from');

                     l_email        := pkg_ctl.get_value_str('Auto Lkbx Notify');

                     l_email_cc     := pkg_ctl.get_value_str('Auto Lkbx Notify cc');

                     l_email        := l_email ||';'||l_email_cc;

                     l_email_bcc    := pkg_ctl.get_value_str('Auto Lkbx Notify bcc');

                    

                     if v_lockbox_bank = 'NT' then

                       v_line1        :=NULL;

                       v_line2        :=NULL;

                       v_line1        :='Lockbox file '||tmpname||' for Northern Trust was loaded into '||TO_CHAR(v_batch_hdr_cnt)||' batches,';

                       v_line2        :='There are '||l_lkbx_issues||' issues in this file.';

                       l_subject      :='Automated Lockbox Import -- Northern Trust ';

                       --v_msg_lines    :=v_line1||chr(13)||chr(10)||v_line2||chr(13)||chr(10);

                       v_msg_lines    :=v_line1||l_crlf||v_line2||l_crlf;

                      

                       --l_lkbx_message :=l_lkbx_message|| chr(13) || chr(10)||v_msg_lines|| chr(13) || chr(10);

                       l_lkbx_message :=l_lkbx_message||l_crlf||v_msg_lines||l_crlf;

                       

                       --l_message      :=l_lkbx_message|| chr(13) || chr(10);

                       l_message      :=l_lkbx_message||l_crlf;

                      

                       --l_lkbx_message2:=l_message|| chr(13) || chr(10);

                       l_lkbx_message2:=l_crlf||l_message;

                      

                       --pkg_mail.send_email_no_attach(l_sender,l_email,l_email_bcc,l_subject,l_message,' ');

                     elsif v_lockbox_bank = 'WF' then

                       v_line1        :=NULL;

                       v_line2        :=NULL;

                       v_line1        :=chr(10)||'Lockbox file '||tmpname||' for Wells Fargo was loaded into '||TO_CHAR(v_batch_hdr_cnt)||' batches,';

                       v_line2        :='There are '||l_lkbx_issues||' issues in this file.';

                       l_subject      :='Automated Lockbox Import -- Wells Fargo ';

                       --v_msg_lines    :=v_line1||chr(13)||chr(10)||v_line2||chr(13)||chr(10);

                       v_msg_lines    :=v_line1||l_crlf||v_line2||l_crlf;

                      

                       --l_lkbx_message :=l_lkbx_message|| chr(13) || chr(10)||v_msg_lines|| chr(13) || chr(10);

                       l_lkbx_message :=l_lkbx_message||l_crlf||v_msg_lines||l_crlf;

                      

                       --l_message      :=l_lkbx_message|| chr(13) || chr(10);

                       l_message      :=l_lkbx_message||l_crlf;

                      

                       --l_lkbx_message2:=l_message|| chr(13) || chr(10);

                       l_lkbx_message2:=l_crlf||l_message;

                      

                       --pkg_mail.send_email_no_attach(l_sender,l_email,l_email_bcc,l_subject,l_message,' ');

                     elsif v_lockbox_bank = 'XX' then 

                       v_line1        :=NULL;

                       v_line2        :=NULL;

                       v_line1        :=chr(10)||'Lockbox file '||tmpname||' for an Unidentified Bank was loaded into '||TO_CHAR(v_batch_hdr_cnt)||' batches,';

                       v_line2        :='There are '||l_lkbx_issues||' issues in this file.';

                       l_subject      :='Automated Lockbox Import -- Unidentified Bank ';

                       --v_msg_lines    :=v_line1||chr(13)||chr(10)||v_line2||chr(13)||chr(10);

                       v_msg_lines    :=v_line1||l_crlf||v_line2||l_crlf;

                      

                       --l_lkbx_message :=l_lkbx_message|| chr(13) || chr(10)||v_msg_lines|| chr(13) || chr(10);

                       l_lkbx_message :=l_lkbx_message||l_crlf||v_msg_lines||l_crlf;

                      

                       --l_message      :=l_lkbx_message|| chr(13) || chr(10);

                       l_message      :=l_lkbx_message||l_crlf;

                      

                       --l_lkbx_message2:=l_message|| chr(13) || chr(10);

                       l_lkbx_message2:=l_crlf||l_message;

                      

                       --pkg_mail.send_email_no_attach(l_sender,l_email,l_email_bcc,l_subject,l_message,' ');

                     end if;

                     v_batches_cnt := v_batches_cnt + v_batch_hdr_cnt;

                     v_batch_hdr_cnt:=0;

                     l_lkbx_issues :=0;

                     v_msg_lines    :=null;

                      

                 END;

       

                 END LOOP;

                  

                 BEGIN

                   l_sender       := pkg_ctl.get_value_str('Standard Email from');

                   l_email        := pkg_ctl.get_value_str('Auto Lkbx Notify');

                   l_email_cc     := pkg_ctl.get_value_str('Auto Lkbx Notify cc');

                   l_email        := l_email ||';'||l_email_cc;

                   l_email_bcc    := pkg_ctl.get_value_str('Auto Lkbx Notify bcc');

                   l_subject      :='Automated Lockbox Processing -- '||l_completed;

                   l_message      :=l_lkbx_message2||'Batch_IDs start with ' ||l_email_batch_id;

                   pkg_mail.send_email_no_attach(l_sender,l_email,l_email_bcc,l_subject,l_message,' ');

                 END;

             EXCEPTION

               WHEN NO_DATA_FOUND THEN

                 dbms_output.put_line('No more files');       

             END;

           end if;

         END;

       

       

      END load_lockbox_file;

      /

        • 1. Re: Pl/SQL procedure
          rp0428
          The procedure appears to be failing when it hits a Wire transfer (Record #6800).   When it aborts the filename is not getting updated to “xloaded_”<filename> so it keeps attempting to reload the same file and is creating duplicate batches until the filenames are reloaded. 

          If the process aborts then the file was not 'LOADED' so why wouldn't you want to load it?

           

          Also, there is NOTHING in that code that 'updates' the filename anyway whether it aborted or not.

           

           

          • 2. Re: Pl/SQL procedure
            Billy~Verreynne

            The #1 reason why you have problems with this - runtime problems and a problem diagnosing and troubleshooting that, is because the code is junk. It is a monolithic monster. It looks EXACTLY like Cobol code written back in the 70's.

             

            Do you think that using a 40 year old programming style has any relevance in the 21st century? Where records gave way to data sets? Where single process, single user, serialised data, gave way to muti-process, multi-user and concurrent data?

             

            The code also violates a fundamental software engineering principle - modularisation of code.

             

            I would not bother bug hunting in such code. It is a waste of time. It is a waste of resources. Time and resources far better spend in rewriting that garbage into a properly designed and modularised PL/SQL package. That includes instrumentation.

             

            Refactor. Mercilessly.

            • 3. Re: Pl/SQL procedure
              e61728c3-7289-450c-a4c4-66a79700916a

              Hi

               

              You are right its an old code , but I wish I could rewrite the whole logic.

               

              I was asked to fix the issue.

               

              The file name is getting renamed in this part of the code

               

                pkg_lockbox.insert_ar_payment_detail(v_sales_year, v_ar_payment_num, v_sequence_no, v_prev_sales_year,

               

                                                                        l_batch_detail_ovfl_cust_no, v_prev_yr_amt_apply, v_name_use,

               

                                                                        v_lkbx_hp_trip_ind, NULL, v_billable_account_num,0, 0, v_order_num);

               

                                 END IF;

               

                               end if;

               

                             ----- Record Type 7 -----

               

                             ELSIF l_rec_ind = '7' THEN

               

                               NULL;

               

                             ----- Record Type 8 -----

               

                             ELSIF l_rec_ind = '8' THEN

               

                               NULL;

               

                             ----- Record Type 9 -----

               

                             ELSIF l_rec_ind = '9' THEN

               

                               v_proc := v_proc + 1;

               

                               --dbms_output.put_line('Last Record in File '||tmpname);

               

                                

               

                               utl_file.fclose(tmpfile1);

               

                               --

               

                               v_newfile_name :='xloaded_'||tmpname;

               

               

               

                               --dbms_output.put_line('New File Name is '||v_newfile_name);   

               

               

               

                               utl_file.frename (v_directory_name,tmpname,v_directory_name,v_newfile_name,FALSE);

               

                             

               

                               if v_proc < unproc_files then

               

                                 goto next_file;

               

                               else

               

                                 --dbms_output.put_line('HERE'); 

               

                                 exit;

               

                               end if;

               

                                     

               

                             END IF;

               

                             <<next_file>>

               

                             null;

               

                              

               

                           END LOOP;

               

               

               

              Can Anyone help me figure out what exactly might be reason its failing.

               

              Thank you , Sorry if i am not clear enough , as I am new to oracle Forums.

              • 4. Re: Pl/SQL procedure
                rp0428
                You are right its an old code

                Huh? How could I be right about something that I never even said?

                , but I wish I could rewrite the whole logic.

                 

                I was asked to fix the issue.

                Yes - and to fix the issue you are going to have to REWRITE THE WHOLE LOGIC.

                 

                That logic being used has some serious flaws and it makes NO SENSE to try to fix them piecemeal.

                The file name is getting renamed in this part of the code

                               ELSIF l_rec_ind = '9' THEN

                . . .

                                 utl_file.fclose(tmpfile1);

                . . .

                                 utl_file.frename (v_directory_name,tmpname,v_directory_name,v_newfile_name,FALSE);

                EXACTLY! And that is the reason for your problem.

                 

                The file is ONLY being closed and renamed in the section for record type '9'. So if the code blows up and never gets to that section the file will NEVER be renamed.

                 

                That is just ONE indication that the code needs to be refactored to be more modular. Other indications are code like this:

                ELSIF l_rec_ind = '4' THEN

                                 if v_is_ach ='N' then

                There are numerous places in the code where you have IF clauses but no ELSE clause to show what, if anything should be done when the IF condition is not met.

                 

                In that example above what is supposed to happen if 'v_is_ach' is NOT an uppercase 'N'? How would any developer even know what is supposed to happen? That code says it is OK to have a record type 4 that is a 'Y' for ACH. But then you just ignore that record and don't do anything with it.

                 

                I've done ACH processing before for a major bank (likely the same one you are dealing with) and that type of record processing doesn't make any sense to me. For the work I did have a type 4 record and no code to process it or report it as an error/exception would be a SERIOUS audit (and potential LIABILITY) issue.

                 

                At a minimum you should create an object type that wraps the record-related data together so it is easier to identify and manage. The code already identifies the main candidates:

                   -- Transmission Header - record type 1

                   --

                   l_trans_header_rec_type    VARCHAR2(1);

                . . .

                   --

                   -- Batch Header -- record type 5

                   --

                   l_batch_header_rec_type    VARCHAR2(1);

                . . .

                   --

                   -- Detail Record -- record type 6

                   --

                   l_batch_detail_rec_type       VARCHAR2(1);

                . . .

                   --

                   -- Overflow record -- record type 4

                   --

                   l_batch_detail_ovfl_rec_type   VARCHAR2(1);

                . . .

                Each of those should be converted to a user-defined type with the required structure. Then the other code should create and populate instances of those types.

                That could be EASILY done. It took me less than two minutes to create an example type from your code:

                create or replace type lockbox_trans_header as object (

                -- Transmission Header - record type 1

                   l_trans_header_rec_type    VARCHAR2(1),

                   l_trans_header_priority    VARCHAR2(2),  -- Priority

                   l_trans_header_destination VARCHAR2(10), -- Company's account number at sending bank

                   l_trans_header_origin      VARCHAR2(10), -- Sending bank's FR/ABA Number

                   l_trans_header_date_alpha  VARCHAR2(6),  -- Transmission date (YYMMDD) -- alpha

                   l_trans_header_date        DATE,         -- Transmission date (YYMMDD) -- Oracle date format

                   l_trans_header_time        VARCHAR2(4),  -- Time -- NOT USED

                   l_trans_header_filler      VARCHAR2(47) -- Filler - Blank

                )

                /

                The types can be created as SQL types or put into a package spec. That would remove a LOT of the clutter in the current code.

                 

                Your code also makes reference to other record types that are not handled by the code:

                   l_record_type VARCHAR2(1); -- record type indentifier (1 -- Transmission Header

                   --                          5 -- Batch Header

                   --                          6 -- Detail Record

                   --                          4 -- Overflow record -- detail

                   --                          7 -- Batch Total

                   --                          8 -- Trailer

                   --                          9 -- Trailer

                Either ALL of those types need to be handled in the code or you need to add comments saying WHY some of them are NOT handled.

                 

                The code DOES include junk like this:

                              ----- Record Type 7 -----

                               ELSIF l_rec_ind = '7' THEN

                                 NULL;

                               ----- Record Type 8 -----

                               ELSIF l_rec_ind = '8' THEN

                                 NULL;

                I consider that JUNK because there is no EXPLICIT mention as to why you are NOT doing anything if there is actually data for those record types. And there is nothing said about whether those record types are REQUIRED to be in the file (but then ignored) or whether they are optional.

                 

                The BEST thing you could do for this code is:

                 

                1. add comments that explicitly identify the business rules that the code implements

                2. refactor the code to be more modular - use custom types that correspond to the record types expected in the files that are processed

                3. ensure that ALL conditional statements either DO SOMETHING or SAY SOMETHING - that is, that there is an ELSE for every IF, etc.

                4. add appropriate EXCEPTION handlers that either LOG or actually handle exceptions.

                 

                The missing item #4 above is your current problem. There is no exception handler that ensures that a file gets closed and/or renamed if there is a problem. And you never answered my question about why you would want to PRETEND that a file had been processed by renaming it when it actually blew up in the middle of being processed.

                 

                The WORST thing you could do with this code is make a cosmetic change to simply fix the one thing you are currently experiencing.

                 

                A good code reviewer and tester could break that code you posted in several ways very easily. It is poorly written and incomplete. It needs to be rewritten.

                • 5. Re: Pl/SQL procedure
                  e61728c3-7289-450c-a4c4-66a79700916a

                  !

                   

                  Thank you so much for your reply,  I really appreciate your response in detail about the flaws in the whole proc.

                  rp0428 wrote:

                   

                  The missing item #4 above is your current problem. There is no exception handler that ensures that a file gets closed and/or renamed if there is a problem. And you never answered my question about why you would want to PRETEND that a file had been processed by renaming it when it actually blew up in the middle of being processed.

                   

                   

                  I am new to this field, so can you tell me , what kind of exception do I have to use to solve this issue?

                  • 6. Re: Pl/SQL procedure
                    rp0428

                    I am new to this field, so can you tell me , what kind of exception do I have to use to solve this issue?

                    So you just plan to ignore the questions that we ask you in trying to understand the problem?

                     

                    This code the only exception handler there is and all it does is capture a meaningless test that the caller is probably never going to see anyway:

                           EXCEPTION

                             WHEN NO_DATA_FOUND THEN

                               dbms_output.put_line('No more files');       

                           END;

                    You have to make sure that any open file is closed even if an exception occurs.

                    • 7. Re: Pl/SQL procedure
                      e61728c3-7289-450c-a4c4-66a79700916a

                      Hi ,

                      The question you asked was given in the business request, and you are right we do not want to update the file name when its aborted or blew up in the middle. I figure that the file that they are provided with have the one of the check number with bad data, as the data type for that column is number and they provided with characters in it. so it blew up.

                      • 8. Re: Pl/SQL procedure
                        rp0428
                        The question you asked was given in the business request, and you are right we do not want to update the file name when its aborted or blew up in the middle.

                        Then where does this thread stand? Your whole thread began with a complaint that the filename wasn't getting updated on an abort.

                        When it aborts the filename is not getting updated to “xloaded_”<filename> so it keeps attempting to reload the same file and is creating duplicate batches until the filenames are reloaded.

                        But now you say it should NOT be updated. Well, you can't have it both ways.

                        I figure that the file that they are provided with have the one of the check number with bad data, as the data type for that column is number and they provided with characters in it. so it blew up.

                        And that is EXACTLY another reason why the code has to be refactored to be modular.

                         

                        The first part of the code needs to deal with opening and reading a file.

                         

                        The next part needs to parse the file, line by line, into custom object type instances based on the record type.

                         

                        The third part needs to VALIDATE the data in that custom object type and handle any issues with bad or missing data.

                         

                        All of those parts need to do something intelligent when a problem is discovered.

                         

                        The biggest thing missing is documentation about the business rules that the code needs to implement. Those rules need to cover ALL possible issues including missing record types and bad or missing data in a record.

                         

                        If a single record has bad or missing data what should happen? Should the entire file be rejected? What about data from records already processed?

                         

                        If a file is missing a record for a specific, required, record type what should happen? Should the entire file be rejected? What about data from records already processed?

                         

                        As we have tried to tell you several times that code has such serious issues and is so incomplete you can't just put a band-aid on it and expect it to work. It is time to pay the piper. As I have said above in more than one way:

                        The BEST thing you could do for this code is:

                         

                        1. add comments that explicitly identify the business rules that the code implements

                        2. refactor the code to be more modular - use custom types that correspond to the record types expected in the files that are processed

                        3. ensure that ALL conditional statements either DO SOMETHING or SAY SOMETHING - that is, that there is an ELSE for every IF, etc.

                        4. add appropriate EXCEPTION handlers that either LOG or actually handle exceptions.