L GENERATION STEPS FOR EXISTING oracle .rdf with oracle reports developer

 

XML GENERATION STEPS FOR EXISTING oracle .rdf reports (Which are not having xml)

Query the existing oracle reports  in system administrator Responsibility.

Concurrent è Program  è Defineè Press F11è Query the Report  

Concurrent è Program  è Defineè Press F11è Query the Report è Change the Out Put Format To XML. And save it.

 Run the report to generate XML. After Completion  of report go to from SRS window  Click On Diagnostics è View XML (For gathering the XML)

 

Save the XML with extension *.xml. (Use this  XML for creating RTF in BI Publisher )

 

 

Open M.S. Word Application for creating RTF In BI Publisher  Add-Ins  Tool

From the Data Men, Load the Xml Data which is saved on specific Location by existing Oracle Report created by you.

 

It will shows  that the data loaded successfully like following image.

 

Click on Insert menu èField Select appropriate one like following

Finally design the rtf as per your requirement and designing skil.

 

 

First create Data Definition in XML Publisher Administration Responsibility.

 Give the name of data definition sets(as per requirement)

Give the Code (it must be the same as Executable name of report )

Give the Application name as per

Give the Description if necessary

.

Second create the template in xml publisher administrator responsibility

Name as per your requirement

Code should match with data definition code

Application as per report

Data Definition – As u created in 1^st step.

Type – RTF

Default Out Put type == Excel, rtf.pdf.html (as per your requirement)

Description – If anything necessary as per your requirement.

Template File  - Attach the RTF created in BI Publisheer.

Select the Language       - English           

Territory – Your country

Translatable – Enable

 

 

 

Oracle Payable API's API to apply hold on AP invoice in R12 AP_HOLDS_PKG

Oracle Payable API's 

API to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)

Below script will help you apply a hold on AP invoice in oracle apps R12

API: AP_HOLDS_PKG.INSERT_SINGLE_HOLD

Test Instance: R12.1.1

Tables Affected: AP_HOLDS_ALL

Script:

SET serveroutput on;

DECLARE

   x_invoice_id         NUMBER;

   x_hold_lookup_code   VARCHAR2 (200);

   x_hold_type          VARCHAR2 (200);

   x_hold_reason        VARCHAR2 (200);

   x_held_by            NUMBER;

   x_calling_sequence   VARCHAR2 (200);

   v_check_flag         VARCHAR2 (1);

   v_context            VARCHAR2 (10);

   FUNCTION set_context (

      i_user_name   IN   VARCHAR2,

      i_resp_name   IN   VARCHAR2,

      i_org_id      IN   NUMBER

   )

      RETURN VARCHAR2

   IS

      v_user_id        NUMBER;

      v_resp_id        NUMBER;

      v_resp_appl_id   NUMBER;

      v_lang           VARCHAR2 (100);

      v_session_lang   VARCHAR2 (100) := fnd_global.current_language;

      v_return         VARCHAR2 (10)  := 'T';

      v_nls_lang       VARCHAR2 (100);

      v_org_id         NUMBER         := i_org_id;

/* Cursor to get the user id information based on the input user name */

      CURSOR cur_user

      IS

         SELECT user_id

           FROM fnd_user

          WHERE user_name = i_user_name;

/* Cursor to get the responsibility information */

      CURSOR cur_resp

      IS

         SELECT responsibility_id, application_id, LANGUAGE

           FROM fnd_responsibility_tl

          WHERE responsibility_name = i_resp_name;

/* Cursor to get the nls language information for setting the language context */

      CURSOR cur_lang (p_lang_code VARCHAR2)

      IS

         SELECT nls_language

           FROM fnd_languages

          WHERE language_code = p_lang_code;

   BEGIN

      /* To get the user id details */

      OPEN cur_user;

      FETCH cur_user

       INTO v_user_id;

      IF cur_user%NOTFOUND

      THEN

         v_return := 'F';

      END IF;  --IF cur_user%NOTFOUND

      CLOSE cur_user;

      /* To get the responsibility and responsibility application id */

      OPEN cur_resp;

      FETCH cur_resp

       INTO v_resp_id, v_resp_appl_id, v_lang;

      IF cur_resp%NOTFOUND

      THEN

         v_return := 'F';

      END IF;     --IF cur_resp%NOTFOUND

      CLOSE cur_resp;

      /* Setting the oracle applications context for the particular session */

      fnd_global.apps_initialize (user_id           => v_user_id,

                                  resp_id           => v_resp_id,

                                  resp_appl_id      => v_resp_appl_id

                                 );

      /* Setting the org context for the particular session */

      mo_global.set_policy_context ('S', v_org_id);

      /* setting the nls context for the particular session */

      IF v_session_lang != v_lang

      THEN

         OPEN cur_lang (v_lang);

         FETCH cur_lang

          INTO v_nls_lang;

         CLOSE cur_lang;

         fnd_global.set_nls_context (v_nls_lang);

      END IF;                                    --IF v_session_lang != v_lang

      RETURN v_return;

   EXCEPTION

      WHEN OTHERS

      THEN

         RETURN 'F';

   END set_context;

BEGIN

--1. Set applications context if not already set.

   v_context := set_context ('&user', '&resp_name’, '&org_id');

   IF v_context = 'F'

   THEN

      DBMS_OUTPUT.put_line ('Error while setting the context');

   END IF;

   mo_global.init ('SQLAP');

   x_invoice_id := 1425;

   x_hold_lookup_code := 'LINE VARIANCE';

   x_hold_type := 'LINE HOLD REASON';

   x_hold_reason := 'Total of Invoice Lines does not equal Invoice Amount';

   x_held_by := 5;

   x_calling_sequence := NULL;

   AP_HOLDS_PKG.INSERT_SINGLE_HOLD

            (x_invoice_id            => x_invoice_id,

             x_hold_lookup_code      => x_hold_lookup_code,

             x_hold_type             => x_hold_type,

             x_hold_reason           => x_hold_reason,

             x_held_by               => x_held_by,

             x_calling_sequence      => x_calling_sequence

            );

   BEGIN

      SELECT 'Y'

        INTO v_check_flag

        FROM ap_holds_all

       WHERE invoice_id = x_invoice_id AND hold_lookup_code = 'LINE VARIANCE';

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         v_check_flag := 'N';

   END;

   DBMS_OUTPUT.put_line ('Hold Applied (Y/N) : ' || v_check_flag);

END;

Below script will explain you to cancel an invoice through API

This script was tested in R12.1.1 instance.

SET SERVEROUTPUT ON;

DECLARE

v_invoice_id            NUMBER      := 11844;

v_last_updated_by       VARCHAR2(20):= 1961;

v_last_update_login     VARCHAR2(20):= 504106;

v_accounting_date       DATE        := SYSDATE;

v_boolean               BOOLEAN;

v_error_code            VARCHAR2(100);

v_debug_info            VARCHAR2(1000);

v_message_name          VARCHAR2(1000);

v_invoice_amount        NUMBER;

v_base_amount           NUMBER;

v_tax_amount            NUMBER;

v_temp_cancelled_amount NUMBER;

v_cancelled_by          VARCHAR2(1000);

v_cancelled_amount      NUMBER;

v_cancelled_date        DATE;

v_last_update_date      DATE;

v_token                 VARCHAR2(100);

v_orig_prepay_amt       NUMBER;

v_pay_cur_inv_amt       NUMBER;

FUNCTION set_context( i_user_name    IN  VARCHAR2

                      ,i_resp_name    IN  VARCHAR2

                      ,i_org_id       IN  NUMBER)

RETURN VARCHAR2

IS

 BEGIN

  NULL;

  -- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here 

END set_context;

BEGIN 

-- Setting the context ----

   v_context :=

              set_context ('&user', '&responsibility', 108); 

   IF v_context = 'F'

   THEN

      DBMS_OUTPUT.put_line ('Error while setting the context');

   END IF;  

--- context done ------------ 

DBMS_OUTPUT.put_line ('Calling API to check whetehr the Invoice is canellable ' );

v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(

                P_invoice_id       => v_invoice_id,

                P_error_code       => v_error_code,   

                P_debug_info       => v_debug_info,

                P_calling_sequence => NULL);

IF v_boolean

THEN

DBMS_OUTPUT.put_line ('Invoice '||v_invoice_id|| ' is cancellable' );

ELSE

DBMS_OUTPUT.put_line ('Invoice '||v_invoice_id|| ' is not cancellable :'|| v_error_code );

END IF;     

DBMS_OUTPUT.put_line ('Calling API to Cancel Invoice' );

v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE

            (p_invoice_id                 => v_invoice_id,

             p_last_updated_by            => v_last_updated_by,

             p_last_update_login          => v_last_update_login,

             p_accounting_date            => v_accounting_date,

             p_message_name               => v_message_name,

             p_invoice_amount             => v_invoice_amount,

             p_base_amount                => v_base_amount,

             p_temp_cancelled_amount      => v_temp_cancelled_amount,

             p_cancelled_by               => v_cancelled_by,

             p_cancelled_amount           => v_cancelled_amount,

             p_cancelled_date             => v_cancelled_date,

             p_last_update_date           => v_last_update_date,

             p_original_prepayment_amount => v_orig_prepay_amt,

             p_pay_curr_invoice_amount    => v_pay_cur_inv_amt,

             P_Token                      => v_token,

             p_calling_sequence           => NULL

             );

IF v_boolean

THEN

DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice' );

COMMIT;

ELSE

DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice' );

ROLLBACK;

END IF;

END;

API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)

Below script will help you to release hold on AP invoice in oracle apps R12

API: AP_HOLDS_PKG.RELEASE_SINGLE_HOLD

Test Instance: R12.1.1

Tables Affected: AP_HOLDS_ALL

Script:

SET serveroutput on;

DECLARE

   x_invoice_id            NUMBER;

   x_hold_lookup_code      VARCHAR2 (200);

   x_release_lookup_code   VARCHAR2 (200);

   x_held_by               NUMBER;

   x_calling_sequence      VARCHAR2 (200);

   v_context               VARCHAR2 (10);

   v_check_flag            VARCHAR2 (1);

   FUNCTION set_context (

      i_user_name   IN   VARCHAR2,

      i_resp_name   IN   VARCHAR2,

      i_org_id      IN   NUMBER

   )

      RETURN VARCHAR2

   IS

      v_user_id        NUMBER;

      v_resp_id        NUMBER;

      v_resp_appl_id   NUMBER;

      v_lang           VARCHAR2 (100);

      v_session_lang   VARCHAR2 (100) := fnd_global.current_language;

      v_return         VARCHAR2 (10)  := 'T';

      v_nls_lang       VARCHAR2 (100);

      v_org_id         NUMBER         := i_org_id;

/* Cursor to get the user id information based on the input user name */

      CURSOR cur_user

      IS

         SELECT user_id

           FROM fnd_user

          WHERE user_name = i_user_name;

/* Cursor to get the responsibility information */

      CURSOR cur_resp

      IS

         SELECT responsibility_id, application_id, LANGUAGE

           FROM fnd_responsibility_tl

          WHERE responsibility_name = i_resp_name;

/* Cursor to get the nls language information for setting the language context */

      CURSOR cur_lang (p_lang_code VARCHAR2)

      IS

         SELECT nls_language

           FROM fnd_languages

          WHERE language_code = p_lang_code;

   BEGIN

      /* To get the user id details */

      OPEN cur_user;

      FETCH cur_user

       INTO v_user_id;

      IF cur_user%NOTFOUND

      THEN

         v_return := 'F';

      END IF;     --IF cur_user%NOTFOUND

      CLOSE cur_user;

      /* To get the responsibility and responsibility application id */

      OPEN cur_resp;

      FETCH cur_resp

       INTO v_resp_id, v_resp_appl_id, v_lang;

      IF cur_resp%NOTFOUND

      THEN

         v_return := 'F';

      END IF;    --IF cur_resp%NOTFOUND

      CLOSE cur_resp;

      /* Setting the oracle applications context for the particular session */

      fnd_global.apps_initialize (user_id           => v_user_id,

                                  resp_id           => v_resp_id,

                                  resp_appl_id      => v_resp_appl_id

                                 );

      /* Setting the org context for the particular session */

      mo_global.set_policy_context ('S', v_org_id);

      /* setting the nls context for the particular session */

      IF v_session_lang != v_lang

      THEN

         OPEN cur_lang (v_lang);

         FETCH cur_lang

          INTO v_nls_lang;

         CLOSE cur_lang;

         fnd_global.set_nls_context (v_nls_lang);

      END IF;    --IF v_session_lang != v_lang

      RETURN v_return;

   EXCEPTION

      WHEN OTHERS

      THEN

         RETURN 'F';

   END set_context;

BEGIN

--1. Set applications context if not already set.

   v_context := set_context ('&user', '&respname', '&org_id');

   IF v_context = 'F'

   THEN

      DBMS_OUTPUT.put_line ('Error while setting the context');

   END IF;

   mo_global.init ('SQLAP');

   x_invoice_id := 1425;

   x_hold_lookup_code := 'LINE VARIANCE';

   x_release_lookup_code := 'VARIANCE CORRECTED';

   x_held_by := 5;

   x_calling_sequence := NULL;

   ap_holds_pkg.release_single_hold

         (x_invoice_id               => x_invoice_id,

          x_hold_lookup_code         => x_hold_lookup_code,

          x_release_lookup_code      => x_release_lookup_code,

          x_held_by                  => x_held_by,

          x_calling_sequence         => x_calling_sequence

         );

   BEGIN

      SELECT 'Y'

        INTO v_check_flag

        FROM ap_holds_all

       WHERE invoice_id = x_invoice_id

         AND release_lookup_code = 'VARIANCE CORRECTED';

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         v_check_flag := 'N';

   END;

   DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);

END;

Credit Memo Creation via API ( ar_credit_memo_api_pub.create_request )

Below Script can be used to create a Credit memo against an Invoice. This script was tested in 11i instance.

SET serveroutput ON;

DECLARE

-- This script was tested in 11i instance --

   v_return_status   VARCHAR2 (1);

   p_count           NUMBER;

   v_msg_count       NUMBER;

   v_msg_data        VARCHAR2 (2000);

   v_request_id      NUMBER;

   v_context         VARCHAR2 (2);

FUNCTION set_context( i_user_name    IN  VARCHAR2

                       ,i_resp_name    IN  VARCHAR2

                       ,i_org_id       IN  NUMBER)

  RETURN VARCHAR2

  IS

  BEGIN

  NULL;

      -- Inorder to reduce the content of the post I moved the implementation part of this function to another    post and it is available here 

END set_context;

BEGIN

-- Setting the context ----

   v_context :=

              set_context ('&user', '&responsibility', 2038);

   IF v_context = 'F'

   THEN

      DBMS_OUTPUT.put_line ('Error while setting the context');

   END IF;

   DBMS_OUTPUT.put_line ('2');

--- context done ------------

   DBMS_OUTPUT.put_line ('Invoking Credit Memo Creation api');

   ar_credit_memo_api_pub.create_request

       (

        -- standard api parameters

        p_api_version                     => 1.0,

        p_init_msg_list                   => fnd_api.g_false,

        p_commit                          => fnd_api.g_false,

        x_return_status                   => v_return_status,

        x_msg_count                       => v_msg_count,

        x_msg_data                        => v_msg_data,

        x_request_id                      => v_request_id,

            -- credit memo request parameters  

        p_customer_trx_id                 => &inv_cust_trx_id,

        p_line_credit_flag                => 'N',

        p_line_amount                     => &return_amount,

        p_tax_amount                      => 0,

        p_cm_reason_code                  => 'RETURN',

        p_skip_workflow_flag              => 'Y',

        p_batch_source_name               => '&batch_source_name',

        p_credit_method_installments      => NULL,

        p_credit_method_rules             => NULL

        );

   DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);

   IF v_msg_count = 1

   THEN

      DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);

   ELSIF v_msg_count > 1

   THEN

      LOOP

         p_count := p_count + 1;

         v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF v_msg_data IS NULL

         THEN

            EXIT;

         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || v_msg_data);

      END LOOP;

   END IF;

END;

API to release hold for oracle Payable Invoice R12.1.3, the (Hold Name = Matching required), 

Hold release name = Holds Quick Released

CREATE OR REPLACE procedure APPS.XXSANTOSH_RELEASE_AP_HOLD

as

--##############################################################################--

--#                                                                            #--

--#   Procedure Name : XXSANTOSH_RELEASE_AP_HOLD                               #--

--#                                                                            #--

--#   Description    : It will release the hold from ap invoice                #--

--#                     1. NEED TO PASS THE USER NAME                          #--

--#                     2. NEED TO PASS THE RESPONSIBILITY NAME               #--

--#                     3.ORGANIZATION ID                                     #--

--#                     4. delete from ap.ap_invoice_lines_interface           #--

--#                     5. delete from ap.ap_invoices_interface                #--

--#                                                                            #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      08-AUG-2016             #--

--#                                                                            #--

--##############################################################################--

   x_invoice_id            NUMBER;

   x_hold_lookup_code      VARCHAR2 (200);

   x_release_lookup_code   VARCHAR2 (200);

   x_held_by               NUMBER;

   x_calling_sequence      VARCHAR2 (200);

   v_context               VARCHAR2 (10);

   v_check_flag            VARCHAR2 (1);

   FUNCTION set_context (

      i_user_name   IN   VARCHAR2,

      i_resp_name   IN   VARCHAR2,

      i_org_id      IN   NUMBER

   )

      RETURN VARCHAR2

   IS

      v_user_id        NUMBER;

      v_resp_id        NUMBER;

      v_resp_appl_id   NUMBER;

      v_lang           VARCHAR2 (100);

      v_session_lang   VARCHAR2 (100) := fnd_global.current_language;

      v_return         VARCHAR2 (10)  := 'T';

      v_nls_lang       VARCHAR2 (100);

      v_org_id         NUMBER         := i_org_id;

/* Cursor to get the user id information based on the input user name */

      CURSOR cur_user

      IS

         SELECT user_id

           FROM fnd_user

          WHERE user_name = i_user_name;  ---------------Pass the user name

/* Cursor to get the responsibility information */

      CURSOR cur_resp

      IS

         SELECT responsibility_id, application_id, LANGUAGE

           FROM fnd_responsibility_tl

          WHERE responsibility_name = i_resp_name;

/* Cursor to get the nls language information for setting the language context */

      CURSOR cur_lang (p_lang_code VARCHAR2)

      IS

         SELECT nls_language

           FROM fnd_languages

          WHERE language_code = p_lang_code;

   BEGIN

      /* To get the user id details */

      OPEN cur_user;

      FETCH cur_user

       INTO v_user_id;

      IF cur_user%NOTFOUND

      THEN

         v_return := 'F';

      END IF;     --IF cur_user%NOTFOUND

      CLOSE cur_user;

      /* To get the responsibility and responsibility application id */

      OPEN cur_resp;

      FETCH cur_resp

       INTO v_resp_id, v_resp_appl_id, v_lang;

      IF cur_resp%NOTFOUND

      THEN

         v_return := 'F';

      END IF;    --IF cur_resp%NOTFOUND

      CLOSE cur_resp;

      /* Setting the oracle applications context for the particular session */

      fnd_global.apps_initialize (user_id           => v_user_id,

                                  resp_id           => v_resp_id,

                                  resp_appl_id      => v_resp_appl_id

                                 );

      /* Setting the org context for the particular session */

      mo_global.set_policy_context ('S', v_org_id);

      /* setting the nls context for the particular session */

      IF v_session_lang != v_lang

      THEN

         OPEN cur_lang (v_lang);

         FETCH cur_lang

          INTO v_nls_lang;

         CLOSE cur_lang;

         fnd_global.set_nls_context (v_nls_lang);

      END IF;    --IF v_session_lang != v_lang

      RETURN v_return;

   EXCEPTION

      WHEN OTHERS

      THEN

         RETURN 'F';

   END set_context;

BEGIN

--1. Set applications context if not already set.

   v_context := set_context ('&user', '&respname', '&org_id');

   IF v_context = 'F'

   THEN

      DBMS_OUTPUT.put_line ('Error while setting the context');

   END IF;

   mo_global.init ('SQLAP');

   x_invoice_id := 908627;     ------------Pass the Invoice ID

   x_hold_lookup_code := 'PO REQUIRED';  ----------Pass the hold lookup code

   x_release_lookup_code := 'HOLDS QUICK RELEASED';  --------Pass the release code

   x_held_by := 5;

   x_calling_sequence := NULL;

   ap_holds_pkg.release_single_hold

         (x_invoice_id               => x_invoice_id,

          x_hold_lookup_code         => x_hold_lookup_code,

          x_release_lookup_code      => x_release_lookup_code,

          x_held_by                  => x_held_by,

          x_calling_sequence         => x_calling_sequence

         );

   BEGIN

      SELECT 'Y'

        INTO v_check_flag

        FROM ap_holds_all

       WHERE invoice_id = x_invoice_id

         AND release_lookup_code = 'HOLDS QUICK RELEASED';

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         v_check_flag := 'N';

   END;

   DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);

END;

/