API to Apply Credit Memo to AR Invoice in Oracle Receivables

/*This script will only Apply CM to Invoice and will not initiate Refund. For Refund you will need to initiate ACTIVITY_APPLICATION which is written in my next post*/

DECLARE

  K_api_version   CONSTANT NUMBER := 1;
  K_init_msg_list CONSTANT VARCHAR2(1) := FND_API.g_false;
  K_comments      CONSTANT ar_receivable_applications.comments%TYPE := 'Test';
  K_commit        CONSTANT VARCHAR2(1) := FND_API.g_false;
  l_acctd_amount_applied_from ar_receivable_applications_all. acctd_amount_applied_from%TYPE;
  l_acctd_amount_applied_to   ar_receivable_applications_all. acctd_amount_applied_to%TYPE;
  l_cm_app_rec                AR_CM_API_PUB.cm_app_rec_type;
  l_msg_count                 NUMBER;
  l_msg_data                  VARCHAR2(2555);
  l_out_rec_application_id    NUMBER;
  l_return_status             VARCHAR2(1);

BEGIN
  /* IMPORTANT: Before calling the API, you need properly initialize org/user/resp information, otherwise the API will fail.
     Please read the following instructions to set these properly.

  1. Setting Org Context
     Oracle Application tables are striped by Org, and you first need to identify what org you are processing in
     The procedure to set Org has changed between R11.5 and R12:

     R11.5: fnd_client_info.set_org_context(&org_id);

     R12: Mo_global.init('AR');
          Mo_global.set_policy_context('S', 204);

  2. User/Responsibility Information
     Before running the API, you need to provide your login credentials using fnd_global.apps_initialize()
     This procedure requires three parameters: UserId, ResponsibilityId, applicationId
     See Step 1. above to get the proper values and use them in the following line of code
  */

  -- Set Org Context
  -- see instructions above to identify what call you need to use, and uncomment that line below.
  -- if you are on R11.5, remove the -- in the following line:
  -- fnd_client_info.set_org_context(&org_id);

  -- if you are on R12, remove the -- in the next 2 lines:
  Mo_global.init('AR');
  Mo_global.set_policy_context('S', 101);

  -- Set User/Resp

  Fnd_Global.apps_initialize(15548, 20678, 222);

  l_cm_app_rec.cm_customer_trx_id          := 67727;
  l_cm_app_rec.cm_trx_number               := null; -- Credit Memo Number
  l_cm_app_rec.inv_customer_trx_id         := 71762;
  l_cm_app_rec.inv_trx_number              := null; -- Invoice Number
  l_cm_app_rec.installment                 := null;
  l_cm_app_rec.applied_payment_schedule_id := null;
  l_cm_app_rec.amount_applied              := 7150;
  l_cm_app_rec.apply_date                  := TRUNC(SYSDATE);
  l_cm_app_rec.gl_date                     := TRUNC(SYSDATE);
  l_cm_app_rec.inv_customer_trx_line_id    := null;
  l_cm_app_rec.inv_line_number             := null;
  l_cm_app_rec.show_closed_invoices        := null;
  l_cm_app_rec.ussgl_transaction_code      := null;
  l_cm_app_rec.attribute_category          := null;
  l_cm_app_rec.attribute1                  := null;
  l_cm_app_rec.attribute2                  := null;
  l_cm_app_rec.attribute3                  := null;
  l_cm_app_rec.attribute4                  := null;
  l_cm_app_rec.attribute5                  := null;
  l_cm_app_rec.attribute6                  := null;
  l_cm_app_rec.attribute7                  := null;
  l_cm_app_rec.attribute8                  := null;
  l_cm_app_rec.attribute9                  := null;
  l_cm_app_rec.attribute10                 := null;
  l_cm_app_rec.attribute11                 := null;
  l_cm_app_rec.attribute12                 := null;
  l_cm_app_rec.attribute13                 := null;
  l_cm_app_rec.attribute14                 := null;
  l_cm_app_rec.attribute15                 := null;
  l_cm_app_rec.global_attribute_category   := null;
  l_cm_app_rec.global_attribute1           := null;
  l_cm_app_rec.global_attribute2           := null;
  l_cm_app_rec.global_attribute3           := null;
  l_cm_app_rec.global_attribute4           := null;
  l_cm_app_rec.global_attribute5           := null;
  l_cm_app_rec.global_attribute6           := null;
  l_cm_app_rec.global_attribute7           := null;
  l_cm_app_rec.global_attribute8           := null;
  l_cm_app_rec.global_attribute9           := null;
  l_cm_app_rec.global_attribute10          := null;
  l_cm_app_rec.global_attribute11          := null;
  l_cm_app_rec.global_attribute12          := null;
  l_cm_app_rec.global_attribute12          := null;
  l_cm_app_rec.global_attribute14          := null;
  l_cm_app_rec.global_attribute15          := null;
  l_cm_app_rec.global_attribute16          := null;
  l_cm_app_rec.global_attribute17          := null;
  l_cm_app_rec.global_attribute18          := null;
  l_cm_app_rec.global_attribute19          := null;
  l_cm_app_rec.global_attribute20          := null;
  l_cm_app_rec.comments                    := K_comments;
  l_cm_app_rec.called_from                 := null;

  ar_cm_api_pub.apply_on_account(p_api_version               => K_api_version,
                                 p_init_msg_list             => K_init_msg_list,
                                 p_commit                    => K_commit,
                                 p_cm_app_rec                => l_cm_app_rec,
                                 x_return_status             => l_return_status,
                                 x_msg_count                 => l_msg_count,
                                 x_msg_data                  => l_msg_data,
                                 x_out_rec_application_id    => l_out_rec_application_id,
                                 x_acctd_amount_applied_from => l_acctd_amount_applied_from,
                                 x_acctd_amount_applied_to   => l_acctd_amount_applied_to);

  COMMIT;
  dbms_output.put_line('return_status: ' || l_return_status);
  dbms_output.put_line('msg_count: ' || l_msg_count);
  dbms_output.put_line('out_rec_application_id: ' ||
                       l_out_rec_application_id);
  dbms_output.put_line('acctd_amount_applied_from: ' ||
                       l_acctd_amount_applied_from);
  dbms_output.put_line('acctd_amount_applied_to: ' ||
                       l_acctd_amount_applied_to);
  IF l_msg_count = 1 THEN
    dbms_output.put_line(l_msg_data);
  ELSIF l_msg_count > 1 THEN
    FOR I IN 1 .. l_msg_count LOOP
      dbms_output.put_line(I || '. ' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.
                                                                            G_FALSE),
                                               1,
                                               255));
    END LOOP;
  END IF;
END;

API to Close Credit Memo and Generate Refund through AP invoice in Oracle Receivables

DECLARE
  lc_inv                   NUMBER;
  lb_complete              BOOLEAN;
  lc_phase                 VARCHAR2(100);
  lc_status                VARCHAR2(100);
  lc_dev_phase             VARCHAR2(100);
  lc_dev_status            VARCHAR2(100);
  lc_message               VARCHAR2(100);
  v_cm_payment_schedule    number; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_inv_payment_schedule   number; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_amount_applied         number; -- Amount of credit memo to apply to invoice
  v_apply_date             date := TRUNC(SYSDATE);
  v_gl_date                date := TRUNC(SYSDATE);
  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);
  v_module_name            varchar2(128); -- := 'XXCREDIT_APPLICATION.APPLY_CREDIT_MEMO'; -- If null, validation won't occur
  v_module_version         varchar2(128) := '1'; -- If null, validation won't occur
  lc_msg_txt               varchar2(1000);
  --
  -- Output
  --
  v_out_rec_application_id    number;
  v_acctd_amount_applied_from number;
  v_acctd_amount_applied_to   number;
  v_receivable_trx_id         NUMBER;
  ln_org_id                   NUMBER := 101;
  lc_trx                      number;

  l_return_status VARCHAR2(10);
  l_msg_count     NUMBER;
  l_msg_data      VARCHAR2(1000);
  p_count         NUMBER;

  l_application_ref_id   NUMBER;
  l_application_ref_num  VARCHAR2(30);
  l_application_ref_type VARCHAR2(30);

  p_credit_memo_trx_id     NUMBER;
  p_receipt_application_id NUMBER;

BEGIN

  v_amount_applied     := 2850; /*Amount pending for Credit Memo*/
  p_credit_memo_trx_id := 67727;
  v_receivable_trx_id  := 1158; /*AR_RECEIVABLES_TRX_ALL.RECEIVABLES_TRX_ID*/

  -- if you are on R12, remove the -- in the next 2 lines:
  Mo_global.init('AR');
  Mo_global.set_policy_context('S', 101);

  -- Set User/Resp        

  Fnd_Global.apps_initialize(15548, 20678, 222);

  dbms_output.put_line('Calling Credit Memo Acitvity Application API.. ');

  Ar_cm_application_pub.activity_application(p_api_version                 => 1.0,
                                             p_init_msg_list               => FND_API.G_TRUE,
                                             p_customer_trx_id             => p_credit_memo_trx_id,
                                             p_amount_applied              => v_amount_applied,
                                             p_applied_payment_schedule_id => -8,
                                             p_receivables_trx_id          => v_receivable_trx_id,
                                             p_apply_date                  => v_apply_date,
                                             p_apply_gl_date               => v_gl_date,
                                             x_return_status               => l_return_status,
                                             x_msg_count                   => l_msg_count,
                                             x_msg_data                    => l_msg_data,
                                             p_application_ref_type        => l_application_ref_type,
                                             p_application_ref_id          => l_application_ref_id,
                                             p_application_ref_num         => l_application_ref_num,
                                             p_receivable_application_id   => p_receipt_application_id);

  IF (l_return_status <> 'S') THEN

    dbms_output.put_line(' Error in Creating Employee Type Supplier..');

    IF (l_msg_count = 1) THEN
      dbms_output.put_line('x_msg_data ' || l_msg_data);
 
    ELSIF (l_msg_count > 1) THEN
      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;
   
        dbms_output.put_line(' Error Message' || p_count || ' ---' ||
                             l_msg_data);
      END LOOP;
    END IF;

  ELSE

    dbms_output.put_line('Successfully Applied with Receivable Application Id..' ||
                         p_receipt_application_id);

  END IF;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error while Appyling Credit Memo to Invoice - ' ||
                         SQLERRM);
END;

Oracle Applications - Function for Amount in Words for Indian Rupees (INR)

FUNCTION Get_Amount_In_Words_INR(p_number IN NUMBER)
    RETURN VARCHAR2
 AS
    TYPE myarray IS TABLE OF VARCHAR2 (255);

    l_str      myarray
       := myarray (' Thousand ',
                   ' Lakh ',
                   ' Crore ',
                   ' Arab ',
                   ' Kharab ',
                   ' Neel ',
                   ' Padam ',
                   ' Shankh '
                  );
    l_num      VARCHAR2 (50)   DEFAULT TRUNC (p_number);
    l_return   VARCHAR2 (4000);
   
 BEGIN

    IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
    THEN
       l_return :=
          TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
                   'Jsp');
    END IF;

    l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);

    FOR i IN 1 .. l_str.COUNT
    LOOP
       EXIT WHEN l_num IS NULL;

       IF (SUBSTR (l_num, LENGTH (l_num) - 1, 2) <> 0)
       THEN
          l_return :=
                TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 1, 2), 'J'),
                         'Jsp'
                        )
             || l_str (i)
             || l_return;
       END IF;

       l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
    END LOOP;

    IF TO_CHAR (P_NUMBER) LIKE '%.%'
    THEN
       l_num := SUBSTR (ROUND (P_NUMBER, 2), INSTR (P_NUMBER, '.') + 1);

       IF (LENGTH (SUBSTR (ROUND (P_NUMBER, 2), INSTR (P_NUMBER, '.') + 1))) =   1
       THEN
          l_num := TO_NUMBER (TO_CHAR (l_num) || '0');
       END IF;

       IF l_num > 0
       THEN
          l_return :=
                l_return
             || ' Rupees And '
             || TO_CHAR (TO_DATE (l_num, 'J'), 'Jsp')
             || ' Paise';
       END IF;
     
    ELSE
   
    l_return :=
                l_return
             || ' Rupees';
    END IF;

    RETURN (l_return||' only');
 END Get_Amount_In_Words_INR;