Thursday 20 September 2018

Auto Apply given Credit memos to the Invoices



CREATE OR REPLACE PACKAGE BODY APPS.XX_CN_CM_APPLICATION_PKG
IS
PROCEDURE MAIN(errbuf OUT VARCHAR2,retcode OUT NUMBER,PR_GL_DATE IN VARCHAR2)
IS
v_count   number(20):=0;
v_date           date :=fnd_date.canonical_to_date(PR_GL_DATE);
v_end_date       date;
v_period_status  varchar2(20);


BEGIN

 Begin
 SELECT ps.end_date "Period End Date" ,
 DECODE(ps.closing_status, 'O','Open' ,
                            'N','Never Opened' ,
                            'F','Future Enterable' ,
                            'C','Closed' ,'Unknown') "Period Status"
 into  v_end_date,v_period_status
 from  gl_period_statuses ps ,
       gl_sets_of_books sob ,
       fnd_application_vl fnd
 where ps.application_id   IN (101)
 and  sob.set_of_books_id       = ps.set_of_books_id
 and  fnd.application_id        = ps.application_id
 and  ps.adjustment_period_flag = 'N'
 and  sob.set_of_books_id       =2021
 and  v_date  BETWEEN TRUNC(ps.start_date) AND TRUNC(ps.end_date);

 Exception
 when no_data_found then
 printlog('No data found while validating gl date passed in parameter');
 end;

--printlog('date passed b '||v_date);
 if v_period_status ='Open' then
 v_date:=v_end_date;
 else
 v_date:=SYSDATE;
 end if;

select count(1)
into v_count
from xx_ar_interface_test sai
where invoice_amount is not null;

 IF
 v_count>0
 THEN
 WITH_AMOUNT(v_date);
 ELSE
 WITHOUT_AMOUNT(v_date);
 END IF;

END;

PROCEDURE WITHOUT_AMOUNT(PR_GL_DATE IN varchar2)
 IS

  cursor C1 is
    select *
      from xx_ar_interface_test sai;

  --in Variables----

  v_cm_payment_schedule    number; -- := 101502; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_inv_payment_schedule   number; -- := 101500; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_amount_applied         number; -- := 1000; -- Amount of credit memo to apply to invoice
  v_apply_date             date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
  v_gl_date                date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
  v_ussgl_transaction_code varchar2(1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_null_flex              varchar2(1024); -- null, unless you have flexfield segments to define
  v_customer_trx_line_id   number; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_comments               varchar2(240) := 'Applied automatically';
  v_module_name            varchar2(128) := 'AR'; -- If null,   validation won 't occur
  v_module_version         varchar2(128) := ' 1 '; -- If null, validation won' t occur

  --Out parameters----

  v_out_rec_application_id    number;
  v_acctd_amount_applied_from number;
  v_acctd_amount_applied_to   number;
 
  ----Local Variables---

  v_cm_due_amount  number;
  v_inv_due_amount number;
  v_inv_count      number;
  v_cm_count       number;
  l_org            NUMBER := 101;
  x_err_flag       varchar(1);
  x_err_msg        varchar2(1000);
  x_applied_chk    number;
  cm_due_amount    number;
  inv_due_amount   number;

begin

  --DBMS_APPLICATION_INFO.set_client_info(l_org);
  begin
  mo_global.set_policy_context('S',l_org);
  end;
 
 
 -- printlog('Date passed '||v_apply_date);

  FOR I IN C1 LOOP
 
   
    x_err_flag      := 'N';
    x_err_msg       := NULL;
    v_inv_due_amount:=0;
    v_cm_due_amount :=0;
    v_inv_count     :=0;
    v_cm_count      :=0;
    cm_due_amount   :=0;
    inv_due_amount  :=0;
   
   
    -------------------------------------------------------------------------------
    ------Validation to check invoice due amount remaining is not eqal to zero-----
    -------------------------------------------------------------------------------
    Begin
     SELECT aps.amount_due_remaining
          into inv_due_amount
            from ar_payment_schedules_all aps, hz_cust_accounts hca
           where 1 = 1
             and hca.account_number = i.bill_customer_number
             and aps.customer_id    = hca.cust_account_id
             and aps.trx_number     = i.reference
             and aps.status         ='OP';
    Exception
    when no_data_found then
    x_err_flag :='Y';
    x_err_msg  := ('No data found while validating invoice due amount for reference number='||i.reference);
    printlog('No data found while validating invoice due amount for reference number='||i.reference||'invoice number='||i.invoice_number);
    when too_many_rows then
    x_err_flag :='Y';
    x_err_msg  := ('Found too many rows while validating invoice due amount for reference number='||i.reference);
    printlog('Found too many rows while validating invoice due amount for reference number='||i.reference||'invoice number='||i.invoice_number);
    end;
   
    -----------------------------------------------------------------------------------
    ------Validation to check credit memo due amount remaining is not eqal to zero-----
    -----------------------------------------------------------------------------------
   
    Begin   
     SELECT -aps.amount_due_remaining
      into cm_due_amount
       from ar_payment_schedules_all aps, hz_cust_accounts hca
      where 1 = 1
        and hca.account_number = i.bill_customer_number
        and aps.customer_id    = hca.cust_account_id
        and aps.trx_number     = i.invoice_number
        and aps.status         ='OP';
    Exception
    when No_data_found then
    x_err_flag :='Y';
    x_err_msg  := ('No data found while validating credit memo due amount for invoice number='||i.invoice_number);
    printlog('No data found while validating credit memo due amount for invoice number='||i.invoice_number||'reference number='||i.reference);
    when too_many_rows then
    x_err_flag :='Y';
    x_err_msg  := ('Found too many rows while validating creit memo due amount for invoice number='||i.invoice_number);
    printlog('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number||'reference number='||i.reference);
    end;
 
   
    if inv_due_amount >0 and cm_due_amount >0 then
 
    SELECT count(1)
      into v_cm_count
      from ar_payment_schedules_all aps
     where aps.trx_number = i.invoice_number;
 
    SELECT count(1)
      into v_inv_count
      from ar_payment_schedules_all aps, hz_cust_accounts hca
     where 1 = 1
       and hca.account_number = i.bill_customer_number
       and aps.customer_id    = hca.cust_account_id
       and aps.trx_number     = i.reference;
 
    SELECT count(1)
     into x_applied_chk
      FROM ra_customer_trx_all            rcta1,
           ra_customer_trx_all            rcta2,
           ar_receivable_applications_all araa,
           ar_payment_schedules_all       aps,
           hz_cust_accounts               hca
     WHERE 1 = 1
       and aps.customer_id = hca.cust_account_id
       and rcta2.customer_trx_id = aps.customer_trx_id
       AND rcta1.customer_trx_id = araa.customer_trx_id
       AND rcta2.customer_trx_id = araa.applied_customer_trx_id
       and rcta1.org_id          = l_org
       and rcta1.trx_number      = i.invoice_number
       and rcta2.trx_number      = i.reference;
 
    if x_applied_chk = 0 then
   
      if v_cm_count > 0 and v_inv_count > 0 then
     
          ---------------------------------------------------
          ------Get credit memo  due amount remaining -------
          ---------------------------------------------------
     
        Begin
          SELECT -aps.amount_due_remaining, aps.payment_schedule_id
            into v_cm_due_amount, v_cm_payment_schedule
            from ar_payment_schedules_all aps, hz_cust_accounts hca
           where 1 = 1
             and hca.account_number = i.bill_customer_number
             and aps.customer_id    = hca.cust_account_id
             and aps.trx_number     = i.invoice_number
             and aps.status         = 'OP';
        exception
          when no_data_found then
            x_err_flag := 'Y';
            x_err_msg  := ('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
            printlog('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);                 
          when too_many_rows then
            x_err_flag := 'Y';
            x_err_msg  := ('TOO1- More than one matching record for CM due amount for the credit memo='||i.invoice_number||'and invoice='||i.reference);
            printlog('TOO1- More than one matching record for CM due amount for the credit memo='||i.invoice_number||'and invoice='||i.reference);
        end;
       
           ---------------------------------------------------
           ------Get invoice due amount remaining -----------
          --------------------------------------------------- 
     
        Begin
          SELECT aps.amount_due_remaining, aps.payment_schedule_id
            into v_inv_due_amount, v_inv_payment_schedule
            from ar_payment_schedules_all aps, hz_cust_accounts hca
           where 1 = 1
             and hca.account_number = i.bill_customer_number
             and aps.customer_id    = hca.cust_account_id
             and aps.trx_number     = i.reference
             and aps.status         = 'OP';
        Exception
          when no_data_found then
            x_err_flag := 'Y';
            x_err_msg  := ('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
           printlog('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
          when too_many_rows then
            x_err_flag := 'Y';
            x_err_msg  := ('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);

           printlog('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
        end;
     
        if v_cm_due_amount > v_inv_due_amount then
       
          v_amount_applied := v_inv_due_amount;
        else
          v_amount_applied := v_cm_due_amount;
        end if;
     
        -- v_amount_applied := i.invoice_amount;
     
      else
        printlog('Skiped out Invoices => ' || i.invoice_number);
        x_err_flag :='Y';
        x_err_msg  :=('Either credit memo'||i.invoice_number||' or invoice '||i.reference||' is not found in the database');
      end if;
     
          ---------------------------------------------------
          ------Applying Credit memos to the invoice -------
          ---------------------------------------------------
      if x_err_flag = 'N' then
        arp_process_application.cm_application(p_cm_ps_id                  => v_cm_payment_schedule,
                                               p_invoice_ps_id             => v_inv_payment_schedule,
                                               p_amount_applied            => v_amount_applied,
                                               p_apply_date                => v_apply_date,
                                               p_gl_date                   => v_gl_date,
                                               p_ussgl_transaction_code    => null,
                                               p_attribute_category        => null,
                                               p_attribute1                => null,
                                               p_attribute2                => null,
                                               p_attribute3                => null,
                                               p_attribute4                => null,
                                               p_attribute5                => null,
                                               p_attribute6                => null,
                                               p_attribute7                => null,
                                               p_attribute8                => null,
                                               p_attribute9                => null,
                                               p_attribute10               => null,
                                               p_attribute11               => null,
                                               p_attribute12               => null,
                                               p_attribute13               => null,
                                               p_attribute14               => null,
                                               p_attribute15               => null,
                                               p_global_attribute_category => null,
                                               p_global_attribute1         => null,
                                               p_global_attribute2         => null,
                                               p_global_attribute3         => null,
                                               p_global_attribute4         => null,
                                               p_global_attribute5         => null,
                                               p_global_attribute6         => null,
                                               p_global_attribute7         => null,
                                               p_global_attribute8         => null,
                                               p_global_attribute9         => null,
                                               p_global_attribute10        => null,
                                               p_global_attribute11        => null,
                                               p_global_attribute12        => null,
                                               p_global_attribute13        => null,
                                               p_global_attribute14        => null,
                                               p_global_attribute15        => null,
                                               p_global_attribute16        => null,
                                               p_global_attribute17        => null,
                                               p_global_attribute18        => null,
                                               p_global_attribute19        => null,
                                               p_global_attribute20        => null,
                                               p_customer_trx_line_id      => v_customer_trx_line_id,
                                               p_comments                  => v_comments,
                                               p_module_name               => v_module_name,
                                               p_module_version            => v_module_version,
                                               p_out_rec_application_id    => v_out_rec_application_id,
                                               p_acctd_amount_applied_from => v_acctd_amount_applied_from,
                                               p_acctd_amount_applied_to   => v_acctd_amount_applied_to);
     
        if v_out_rec_application_id is not null then
       
          commit;
          printlog('Credit memo='||i.invoice_Number ||','|| 'Invoice=' || i.reference ||','|| 'CM amount=' ||
          v_cm_due_amount ||','|| 'Invoice amount=' || v_inv_due_amount ||','|| 'Amount applied=' ||
          v_amount_applied);
        else
         
          rollback;
        end if;
      else
      printlog('API Execution is not processed due to validation error'); 
     
      end if;
    else
   
     x_err_flag:='Y';
     x_err_msg :=('Credit memo '||i.invoice_number||'already applied to Same Invoice number : '|| i.reference);
      printlog('Credit memo '||i.invoice_number||'already applied to Same Invoice number : '|| i.reference);
    end if;
   
    else
     x_err_flag:='Y';
     x_err_msg :=('Please check that either credit memo amount or invoice due amount is zero or program ran into exception
                   for either invoice or credit memo--->Please check log');
   
   
   end if;
 
 
   update xx_ar_interface_test
     set    error_flag     =x_err_flag,
            error_msg      =x_err_msg
     where  invoice_number =i.invoice_number
     and    reference      =i.reference ;
     commit;
  end loop;
exception
  when others then
   -- dbms_output.put_line(sqlerrm);
    printlog(sqlerrm);
end;

PROCEDURE WITH_AMOUNT(PR_GL_DATE IN varchar2)
IS

cursor C1 is
    select *
      from xx_ar_interface_test sai;
   
    ----local Variables Declaration-----
   
  v_cm_payment_schedule    number; -- := 101502; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_inv_payment_schedule   number; -- := 101500; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_amount_applied         number; -- := 1000; -- Amount of credit memo to apply to invoice
  v_apply_date             date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
  v_gl_date                date :=PR_GL_DATE;--fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
  v_ussgl_transaction_code varchar2(1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_null_flex              varchar2(1024); -- null, unless you have flexfield segments to define
  v_customer_trx_line_id   number; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_comments               varchar2(240) := 'Applied automatically';
  v_module_name            varchar2(128) := 'AR'; -- If null,   validation won 't occur
  v_module_version         varchar2(128) := ' 1 '; -- If null, validation won' t occur
 

   -----Out parameters--------

  v_out_rec_application_id    number;
  v_acctd_amount_applied_from number;
  v_acctd_amount_applied_to   number;
 
  ------Local Variables-----
 
  v_cm_due_amount          number;
  v_inv_due_amount         number;
  v_inv_count              number;
  v_cm_count               number;
  l_org                    number := 101;
  x_err_flag               varchar(1);
  x_err_msg                varchar2(1000);
  cm_due_amount            number;


begin

  ---DBMS_APPLICATION_INFO.set_client_info(l_org);
  begin
  mo_global.set_policy_context('S',l_org);
  end;
   

  FOR I IN C1 LOOP
 
    x_err_flag      := 'N';
    x_err_msg       := NULL;
    v_inv_due_amount:=0;
    v_cm_due_amount :=0;
    v_inv_count     :=0;
    v_cm_count      :=0;
    cm_due_amount   :=0;
   
     ----------------------------------------------------------------------------------
    ------Validation to check credit memo due amount remaining is not eqal to zero-----
    -----------------------------------------------------------------------------------
   
    Begin   
     SELECT -aps.amount_due_remaining
      into  cm_due_amount
       from ar_payment_schedules_all aps, hz_cust_accounts hca
      where 1 = 1
        and hca.account_number = i.bill_customer_number
        and aps.customer_id    = hca.cust_account_id
        and aps.trx_number     = i.invoice_number
        and aps.status         ='OP';
    Exception
    when no_data_found then
    x_err_flag :='Y';
    x_err_msg  := ('No data found while validating creit memo due amount for invoice number='||i.invoice_number);
    printlog('No data found while validating credit memo due amount for invoice number='||i.invoice_number||'Reference='||i.reference);
    when too_many_rows then
    x_err_flag :='Y';
    x_err_msg  := ('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number);
    printlog('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number||'Reference='||i.reference);
    end;
   
   if i.invoice_amount >0 and cm_due_amount>0 then
 
    SELECT count(1)
      into v_cm_count
      from ar_payment_schedules_all aps
     where aps.trx_number = i.invoice_number;
 
    SELECT count(1)
      into v_inv_count
      from ar_payment_schedules_all aps, hz_cust_accounts hca
     where 1 = 1
       and hca.account_number = i.bill_customer_number
       and aps.customer_id = hca.cust_account_id
       and aps.trx_number = i.reference;
 
    if v_cm_count > 0 and v_inv_count > 0 then
       
          --------------------------------------------------- 
          ------Get credit memo  due amount remaining -------
          ---------------------------------------------------
   
      Begin
        SELECT -aps.amount_due_remaining, aps.payment_schedule_id
          into v_cm_due_amount, v_cm_payment_schedule
          from ar_payment_schedules_all aps, hz_cust_accounts hca
         where 1 = 1
           and hca.account_number = i.bill_customer_number
           and aps.customer_id    = hca.cust_account_id
           and aps.trx_number     = i.invoice_number
           and aps.status         ='OP';
      Exception
      when no_data_found then
            x_err_flag := 'Y';
            x_err_msg  := ('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
            printlog('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);                 
      when too_many_rows then
            x_err_flag := 'Y';
            x_err_msg  := ('TOO1- More than one matching record for CM due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
            printlog('TOO1- More than one matching record for CM due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
      end;
     
         ---------------------------------------------------
          ------Get Incoice due amount remaining -------
          ---------------------------------------------------
   
      Begin
        SELECT aps.amount_due_remaining, aps.payment_schedule_id
          into v_inv_due_amount, v_inv_payment_schedule
          from ar_payment_schedules_all aps, hz_cust_accounts hca
         where 1 = 1
           and hca.account_number = i.bill_customer_number
           and aps.customer_id    = hca.cust_account_id
           and aps.trx_number     = i.reference
           and aps.status         ='OP';
      Exception
         when no_data_found then
            x_err_flag := 'Y';
            x_err_msg  := ('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
         
           printlog('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
          when too_many_rows then
            x_err_flag := 'Y';
            x_err_msg  := ('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
           
           printlog('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference); 
      end;
     
     /* if v_cm_due_amount > v_inv_due_amount then
       
          v_amount_applied := v_inv_due_amount;
        else
          v_amount_applied := v_cm_due_amount;
        end if;*/
         
        if i.invoice_amount>v_inv_due_amount then
        x_err_flag :='Y';
        x_err_msg  :='Invoice amount(CM amount) given is greater than the invoice due amount';
        else       
        v_amount_applied  := i.invoice_amount;
        end if;
    else
      printlog('Skipped out Invoices => ' || i.invoice_number);
      x_err_flag :='Y';
      x_err_msg  :=('Either credit memo'||i.invoice_number||' or invoice '||i.reference||' is not found in the database');
    end if;
   
          ---------------------------------------------------
          ------Applying credit memos to the invoice---------
          ---------------------------------------------------   
   
    if x_err_flag = 'N' then
      arp_process_application.cm_application(p_cm_ps_id                  => v_cm_payment_schedule,
                                             p_invoice_ps_id             => v_inv_payment_schedule,
                                             p_amount_applied            => v_amount_applied,
                                             p_apply_date                => v_apply_date,
                                             p_gl_date                   => v_gl_date,
                                             p_ussgl_transaction_code    => null,
                                             p_attribute_category        => null,
                                             p_attribute1                => null,
                                             p_attribute2                => null,
                                             p_attribute3                => null,
                                             p_attribute4                => null,
                                             p_attribute5                => null,
                                             p_attribute6                => null,
                                             p_attribute7                => null,
                                             p_attribute8                => null,
                                             p_attribute9                => null,
                                             p_attribute10               => null,
                                             p_attribute11               => null,
                                             p_attribute12               => null,
                                             p_attribute13               => null,
                                             p_attribute14               => null,
                                             p_attribute15               => null,
                                             p_global_attribute_category => null,
                                             p_global_attribute1         => null,
                                             p_global_attribute2         => null,
                                             p_global_attribute3         => null,
                                             p_global_attribute4         => null,
                                             p_global_attribute5         => null,
                                             p_global_attribute6         => null,
                                             p_global_attribute7         => null,
                                             p_global_attribute8         => null,
                                             p_global_attribute9         => null,
                                             p_global_attribute10        => null,
                                             p_global_attribute11        => null,
                                             p_global_attribute12        => null,
                                             p_global_attribute13        => null,
                                             p_global_attribute14        => null,
                                             p_global_attribute15        => null,
                                             p_global_attribute16        => null,
                                             p_global_attribute17        => null,
                                             p_global_attribute18        => null,
                                             p_global_attribute19        => null,
                                             p_global_attribute20        => null,
                                             p_customer_trx_line_id      => v_customer_trx_line_id,
                                             p_comments                  => v_comments,
                                             p_module_name               => v_module_name,
                                             p_module_version            => v_module_version,
                                             p_out_rec_application_id    => v_out_rec_application_id,
                                             p_acctd_amount_applied_from => v_acctd_amount_applied_from,
                                             p_acctd_amount_applied_to   => v_acctd_amount_applied_to);
   
      if v_out_rec_application_id is not null then
        --      dbms_output.put_line('Committing.');
        commit;
        printlog('Credit memo='||i.invoice_Number ||','|| 'Invoice=' || i.reference ||','|| 'CM amount=' ||
          v_cm_due_amount ||','|| 'Invoice amount=' || v_inv_due_amount ||','|| 'Amount applied=' ||
          v_amount_applied);
      else
        rollback;
      end if;
     else
      printlog('API Execution is not processed due to validation error');
    end if;
   else
     x_err_flag:='Y';
     x_err_msg :=('Please check that either credit memo amount or invoice due amount is zero or program ran into exception
                   for either invoice or credit memo--->Please check log');
   
   end if;
   update xx_ar_interface_test
     set    error_flag     =x_err_flag,
            error_msg      =x_err_msg
     where  invoice_number =i.invoice_number
     and    reference      =i.reference ;
     commit;
  end loop;
exception
  when others then
    printlog(sqlerrm);
end;

PROCEDURE printlog (p_text_mesg VARCHAR2)
   IS
   BEGIN
      fnd_file.put_line (fnd_file.LOG, p_text_mesg);
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error in "printlog" procedure');
   END;
 
END XX_CN_CM_APPLICATION_PKG;
/

No comments:

Post a Comment