API/Script to UNAPPLY ON-ACCOUNT Receipts in Oracle Apps R12 Receivables

PROCEDURE ar_unapply_on_acct_receipt(p_cash_receipt_id NUMBER) IS
   l_return_status VARCHAR2(1);
   l_msg_count NUMBER;
   l_msg_data VARCHAR2(240);
   l_cash_receipt_id NUMBER;
   p_count number := 0;
BEGIN
    -- 1) Set the applications context
    mo_global.init('AR');
    mo_global.set_policy_context('S','204');

   AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT
   ( 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,
     x_return_status => l_return_status,
     x_msg_count => l_msg_count,
     x_msg_data => l_msg_data,
     p_cash_receipt_id => p_cash_receipt_id,
     P_reversal_gl_date => TRUNC(SYSDATE));

    -- 3) Review the API output
    dbms_output.put_line('Status ' || l_return_status);
    dbms_output.put_line('Message count ' || l_msg_count);

    if l_msg_count = 1 Then
       dbms_output.put_line('l_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('Message ' || p_count ||'. '||l_msg_data);
       end loop;
    end if;
END ar_unapply_on_acct_receipt;

Common Utility Procedure for FND_FLIE.LOG and FND_FILE.OUT

PROCEDURE pr_print_debug (p_message IN VARCHAR2, p_write_to IN NUMBER) IS
BEGIN
  -- 1 = LOG, 2 = OUTFILE, 3 = both LOG and OUTFILE
 
  IF p_write_to = 1 THEN
    apps.fnd_file.put_line (apps.fnd_file.log, p_message);
  ELSIF p_write_to = 2 THEN
apps.fnd_file.put_line (apps.fnd_file.output, p_message);
  ELSE
apps.fnd_file.put_line (apps.fnd_file.log, p_message);
apps.fnd_file.put_line (apps.fnd_file.output, p_message);
  END IF;
 
  EXCEPTION
  WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.log, p_Message);
    fnd_file.put_line(fnd_file.log, 'Unable to open/write to the log file with the following error');
    fnd_file.put_line(fnd_file.log,substr(SQLERRM,1,250));
END pr_print_debug;

Script/Procedure to submit Invoice Approval Workflow program - FND_REQUEST.SUBMIT_REQUEST

PROCEDURE pr_approve_invoice(errbuf    OUT VARCHAR2,
                          retcode      OUT VARCHAR2,
                          p_invoice_id IN NUMBER,
                          p_request_id OUT NUMBER) IS
  CURSOR app_inv_cur IS
    SELECT DISTINCT aia.invoice_num inv_number, aia.org_id
      FROM ap_invoices_all aia
     WHERE 1 = 1
       AND aia.invoice_id = p_invoice_id
       AND aia.wfapproval_status = 'REQUIRED';

  --'VLAIDATED');
  l_sub_request_id NUMBER := NULL;
  l_resp_id        NUMBER; --PAYABLES Manager
  l_app_id NUMBER; --Account payables
  v_request_completed BOOLEAN;
  v_request_id     NUMBER;
  v_phase          VARCHAR2(80) := NULL;
  v_status         VARCHAR2(80) := NULL;
  v_dev_phase      VARCHAR2(30) := NULL;
  v_dev_status     VARCHAR2(30) := NULL;
  v_message        VARCHAR2(240);
  l_ou_name VARCHAR2(200);
  l_org_id NUMBER := 101;
  l_ou_count NUMBER;
 
BEGIN
 
  FOR app_inv_rec IN app_inv_cur LOOP
   
    BEGIN
     
      SELECT DISTINCT fr.responsibility_id, frx.application_id
        INTO l_resp_id, l_app_id
        FROM apps.fnd_responsibility frx, apps.fnd_responsibility_tl fr
       WHERE fr.responsibility_id = frx.responsibility_id
         AND UPPER(fr.responsibility_name) = UPPER('Payables Manager');
   
      -- fnd_client_info.set_org_context (85);
      IF gn_user_id IS NOT NULL AND l_resp_id IS NOT NULL AND l_app_id IS NOT NULL THEN
       
         pr_print_debug('Initializing AP responsibility and application',1);
       
          --mo_global.init ('AP');
          mo_global.set_policy_context ('S', 101);
         
                 
         apps.fnd_global.apps_initialize(gn_user_id, l_resp_id, l_app_id);
       
         pr_print_debug('Submitting Invoice Approval Workflow.. ',1);
       
         fnd_request.set_org_id('101');                
       
         l_sub_request_id :=
         apps.fnd_request.submit_request('SQLAP',
                                                            'APXIAWRE',
                                                            'Invoice Approval Workflow',
                                                            NULL,
                                                            FALSE,
                                                            NULL,
                                                            app_inv_rec.inv_number,
                                                            NULL,
                                                            NULL);
        COMMIT;
     
        LOOP
          v_request_completed := apps.fnd_concurrent.wait_for_request(l_sub_request_id -- Request ID
                                                                     ,
                                                                      20
                                                                      -- Time Interval
                                                                     ,
                                                                      0
                                                                      -- Total Time to wait
                                                                     ,
                                                                      v_phase
                                                                      -- Phase displyed on screen
                                                                     ,
                                                                      v_status -- Status displayed on screen
                                                                     ,
                                                                      v_dev_phase
                                                                      -- Phase available for developer
                                                                     ,
                                                                      v_dev_status -- Status available for developer
                                                                     ,
                                                                      v_message
                                                                      -- Execution Message
                                                                      );
          EXIT WHEN v_request_completed;
        END LOOP;
      END IF;
   
      pr_print_debug('Invoice Approval Workflow completed with request id = ' ||
                           l_sub_request_id,1);
                         
      p_request_id := l_sub_request_id;                          
   
    EXCEPTION
      WHEN OTHERS THEN
        pr_print_debug('Error in submiting Invoice Approval Workflow Program: ' ||
                             SQLERRM,1);
        errbuf := 'Error in submiting Invoice Approval Workflow Program: '||SQLERRM;
        retcode := 2;
    END;
  END LOOP;
END pr_approve_invoice;

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;

GL Account Analysis Query for Multiple sources

SELECT  xga.period_name,
                    xga.ledger_name,
                    xga.ledger_id,
                    xga.account,
                    xga.project,
                    xga.resource_org,
                    xga.work_location,
                    xga.client_vertical,
                    xga.product,
                    xga.entity,
                    xga.inter_entity,
                    xga.spare1,
                    xga.spare2,
                    xga.combinations,
                    xga.je_source,
                    xga.je_category,
                    pv.vendor_name party_name,
                    pv.segment1 party_number,
                    NULL transaction_type,
                    xal.currency_code transaction_currency,
                    xte.transaction_number,
                    TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') transaction_date,
                    xga.gl_date,
                    gir.subledger_doc_sequence_value subledger_doc_no,
                    xga.doc_sequence_value,
                    xal.description transaction_description,
                    xal.currency_code entered_currency,
                    NVL (xal.entered_dr, 0) entered_dr,
                    NVL (xal.entered_cr, 0) entered_cr,
                    xga.ledger_currency accounted_currency,
                    NVL (xal.accounted_dr, 0) accounted_dr,
                    NVL (xal.accounted_cr, 0) accounted_cr,
                    xga.code_combination_id
            FROM    gl_code_combinations xga,
                    gl_import_references gir,
                    xla_ae_lines xal,
                    xla_ae_headers xah,
                    xla_events xe,
                    xla.xla_transaction_entities xte,
                    po_vendors pv
            WHERE   xga.je_batch_id = gir.je_batch_id
            AND xga.je_header_id = gir.je_header_id
            AND xal.ledger_id = xah.ledger_id
            AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
            AND xga.je_header_id = gir.je_header_id
            AND xga.je_line_num = gir.je_line_num
            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.gl_sl_link_table = xal.gl_sl_link_table
            AND xal.ae_header_id = xah.ae_header_id
            AND xal.application_id = xah.application_id
            AND xah.event_id = xe.event_id
            AND xah.application_id = xe.application_id
            AND xe.entity_id = xte.entity_id
            AND xe.application_id = xte.application_id
            AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
            AND xal.party_id = pv.vendor_id(+)
            AND UPPER (xga.je_source) in ( 'PAYABLES','RECEIVABLES','ASSETS')