Thursday, 20 September 2018

Auto Apply given receipts to Invoices


CREATE OR REPLACE PACKAGE BODY APPS.XX_RECEIPT_APPLICATION_PKG
IS
PROCEDURE MAIN(
      errbuf                OUT      VARCHAR2,
      retcode               OUT      NUMBER,
      PR_GL_DATE            IN       VARCHAR2)
IS

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_end_date);
if v_period_status ='Open' then
v_date:=v_end_date;
else
v_date:=SYSDATE;
end if;
RECEIPT_APPLICATION(v_date);
END;
PROCEDURE RECEIPT_APPLICATION(PR_GL_DATE VARCHAR2)
IS

 

  CURSOR C1 IS
    SELECT *
      FROM xx_AR_RECEIPT_TEST;
    -- WHERE invoice_number = 'INDOPD0000301341';
   
  l_return_status   varchar2(1);
  l_msg_count       number;
  l_msg_data        varchar2(240);
  p_count           number := 0;
  v_invoice_ps_id   number;
  x_cash_receipt_id number;
  v_apply_date      date:=PR_GL_DATE;
  v_gl_date         date:=PR_GL_DATE;
  x_inv_due_amount  number;
  x_rec_due_amount  number;
  x_err_flag        varchar(1);
  x_err_msg         varchar2(1024);

BEGIN
  -----------------------------------------
  -- ---- Set the applications context-----
  -----------------------------------------
  begin
  mo_global.set_policy_context('S',gn_org_id);
  end;
  fnd_global.apps_initialize (gn_user_id, gn_resp_id, gn_resp_appl_id);
 
  printlog('USER_ID='||gn_user_id ||','||'RESP_ID=' ||gn_resp_id||','||
                             'APPLICATION_ID='||gn_resp_appl_id);
 
  printlog('Date Passed ='||v_apply_date);
  FOR I IN C1 LOOP
   
    x_err_flag        := 'N';
    x_err_msg         := null;
    v_invoice_ps_id   := null;
    x_cash_receipt_id := null;
    x_inv_due_amount  :=0;
    x_rec_due_amount  :=0;
   
     
     -----------------------------------------------------------------------------------
     ------Validation to check invoice due amount remaining is not eqal to zero-----
    -----------------------------------------------------------------------------------
   
    begin
     SELECT aps.amount_due_remaining
          into x_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.invoice_number
             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 invoice number='||i.invoice_number);
    printlog('No data found while validating invoice due amount for 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 invoice number='||i.invoice_number);
    printlog('Found too many rows while validating invoice due amount for invoice number='||i.invoice_number);
    end;
   
     --------------------------------------------------------------------
      ------Validation to check receipt amount is not eqal to zero-----
    ---------------------------------------------------------------------
   
    begin   
     SELECT -aps.amount_due_remaining
      into x_rec_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.receipt_number
        and aps.status         = 'OP';
    Exception
    when NO_DATA_FOUND then
    x_err_flag :='Y';
    x_err_msg  := ('No data found while validating receipt due amount for receipt number='||i.receipt_number);
    printlog('No data found while validating receipt due amount for receipt number='||i.receipt_number);
    when TOO_MANY_ROWS then
    x_err_flag :='Y';
    x_err_msg  := ('Found too many rows while validating receipt due amount for receipt number='||i.receipt_number);
    printlog('Found too many rows while validating receipt due amount for receipt number='||i.receipt_number);
    end;
 
   
   
   
    IF i.apply_amount>0 and x_inv_due_amount >0 and x_rec_due_amount>0  then
   
      ----------------------------------------------------------------------
      ------Get payment schedule id for applying receipt to the invoice-----
      ---------------------------------------------------------------------- 
    begin
      SELECT APS.Payment_Schedule_Id
        into v_invoice_ps_id
        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- While Validating Invoice matching records not found for the customer number='||i.bill_customer_number
                       ||','||'Invoice number='||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('NO1- While Validating Invoice matching records not found for the customer number='||i.bill_customer_number
                       ||','||'Invoice number='||i.invoice_number||','||'Receipt number='||i.receipt_number);
      when TOO_MANY_ROWS then
        x_err_flag := 'Y';
        x_err_msg  :=('TOO1- While Validating Invoice found Duplicate records for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('TOO1- While Validating Invoice found Duplicate records for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
      when others then
        x_err_flag := 'Y';
        x_err_msg  :=('OT1- While Validating Invoice some error occured for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('OT1- While Validating Invoice some error occured for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
     
    end;
   
      ----------------------------------------------------------------------
      ------Get cash receipt  id for applying receipt to the invoice--------
      ---------------------------------------------------------------------- 
 
    begin
      SELECT aps.cash_receipt_id
        into x_cash_receipt_id
        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.receipt_number
         and aps.status='OP';
    exception
      when NO_DATA_FOUND then
        x_err_flag := 'Y';
        x_err_msg  :=('NO2- While Validating Receipt matching records not found for the customer number='||i.bill_customer_number
                       ||','||'Invoice number='||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('NO2- While Validating Receipt matching records not found for the customer number='||i.bill_customer_number
                       ||','||'Invoice number='||i.invoice_number||','||'Receipt number='||i.receipt_number);
      when TOO_MANY_ROWS then
        x_err_flag := 'Y';
        x_err_msg  :=('TOO2- While Validating Receipt found Duplicate records for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('TOO2- While Validating Receipt found Duplicate records for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
      when others then
        x_err_flag := 'Y';
        x_err_msg  :=('OT2- While Validating Receipt some error occured for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
        printlog('OT2 - While Validating Receipt some error occured for the customer number='||i.bill_customer_number||','||'Invoice_number='
                       ||i.invoice_number||','||'Receipt number='||i.receipt_number);
    end;
   
      ------------------------------------------
      ------applying receipt to the invoice-----
      ------------------------------------------       
    IF x_err_flag = 'N' THEN
     
      AR_RECEIPT_API_PUB.APPLY(p_api_version                 => 1.0,
                               p_init_msg_list               => FND_API.G_TRUE,
                               p_commit                      => FND_API.G_TRUE,
                               p_validation_level            => FND_API.G_VALID_LEVEL_FULL,
                               p_cash_receipt_id             => x_cash_receipt_id,
                               p_apply_date                  => v_apply_date,
                               p_apply_gl_date               => v_gl_date,
                               p_applied_payment_schedule_id => v_invoice_ps_id,
                               p_org_id                      => gn_org_id,
                               p_amount_applied              => i.apply_amount,
                               x_return_status               => l_return_status,
                               x_msg_count                   => l_msg_count,
                               x_msg_data                    => l_msg_data);
   
   
      printlog(' API Status =' || l_return_status);
      printlog('Error Count =' || l_msg_count);
   
      if l_msg_count = 1 Then
        printlog('l_msg_data ' || l_msg_data);
        x_err_flag:='Y';
        x_err_msg := l_msg_data;
      elsif l_msg_count > 1 Then
        x_err_flag:='Y';
        x_err_msg :='Multiple errors during API execution, Please check the log for the errors';
        loop
          p_count    := p_count + 1;
          l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
         
          if l_msg_data is NULL Then
            exit;
          end if;
          printlog('Message ' || p_count || '. ' || l_msg_data);
        end loop;
       
       else 
        printlog('Receipt Number='||i.receipt_number||','||'Invoice Number='||i.invoice_number||','||'apply amount='||i.apply_amount);
      end if;
    END IF;
   else
   x_err_flag :='Y';
   x_err_msg  :=('Apply amount given is 0  for the invoice number '||i.invoice_number||' or remaining due amount is
                  0 or program ran into exception for either invoice or receipt--->Please check log');
   end if;
 
   update xx_ar_receipt_test
   set    error_flag    =x_err_flag,
          error_msg     =x_err_msg
   where  receipt_number=i.receipt_number
   and    invoice_number=i.invoice_number;   
   commit;   
  END LOOP; 

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_RECEIPT_APPLICATION_PKG;

No comments:

Post a Comment