Tuesday 30 September 2014

How to programmatically apply hold on AP Invoices in 11i,R12

Below procedure script will help you apply a hold on AP invoice in oracle apps 11i,R12.
This procedure can be registered as a Concurrent Executable and Concurrent Program.

API: AP_HOLDS_PKG.INSERT_SINGLE_HOLD


  /*+===============================================================*
   * PROCEDURE                                                                                                   *             *                                                                                                                    *
   *  assign_invhold_prc                                                                                         *
   *                                                                                                                    *
   * DESCRIPTION                                                                                                 *
   * ===========                                                                                               *
   *  The following procedure is called by the concurrent program is used to apply            *
   *  Invoice Hold                                                                                                  *
   *  PARAMETERS                                                                                                 *
   * ==========                                                                                                  *
   * NAME                TYPE     DESCRIPTION                                                               *
   * -----------------------------------------------------------------------------------*
   * retcode              OUT      Standard Conc Program parameter                                   *
   * errbuf               OUT      Standard Conc Program parameter                                     *
   * p_invoice_hold       IN       Invoice Hold Type                                                         *
   * p_inv_lookup         IN       Invoice Type(STANDARD,MIXED etc.)                                *
   * ------------------------------------------------------------------------------------*
   * CALLED BY                                                                                                       *
   * Concurrent Program                                                                                           *
   *+=================================================================*/
 PROCEDURE assign_invhold_prc (errbuf         OUT VARCHAR2,
                            retcode        OUT VARCHAR2,
                            p_invoice_hold IN VARCHAR2,
                            p_inv_lookup   IN VARCHAR2)
   IS
      --Declare local variables
   lv_hold_type          ap_hold_codes_v.hold_type%TYPE DEFAULT NULL;
   lv_lookup_type        ap_lookup_codes.lookup_type%TYPE := 'HOLD CODE';
   lv_hold_lookup_code   ap_hold_codes_v.hold_lookup_code%TYPE := p_invoice_hold;
   lv_hold_reason        ap_hold_codes_v.description%TYPE;
   lv_invoice_id         PLS_INTEGER;
   lv_invtype_lkpcode   ap_invoices_all.invoice_type_lookup_code%TYPE := p_inv_lookup;
   lv_fnd_user           fnd_user.user_id%TYPE := fnd_profile.VALUE ('USER_ID');
   lv_org_id             ap_invoices_all.org_id%TYPE := fnd_profile.value('ORG_ID');
   lv_sql_count          NUMBER :=0;

      -- Cursor to derive invoices to be placed on hold
      CURSOR cur_inv
      IS
         SELECT invoice_id, invoice_num
           FROM (SELECT ai.invoice_id invoice_id, ai.invoice_num invoice_num
                   FROM ap_invoices_all ai
                  WHERE 1 = 1
                    AND ai.org_id = lv_org_id
                      AND ai.invoice_type_lookup_code = lv_invtype_lkpcode
                    AND ai.payment_status_flag IN ('N','P')
             WHERE NOT EXISTS (
                   SELECT 1
                     FROM ap_holds_all
                    WHERE hold_lookup_code = lv_hold_lookup_code
                      AND org_id = lv_org_id
                      AND invoice_id = ai.invoice_id);

   BEGIN
      -- Derive Hold Type Code
      BEGIN
         SELECT ahc.hold_type, ahc.hold_lookup_code, ahc.description
           INTO lv_hold_type, lv_hold_lookup_code, lv_hold_reason
           FROM ap_lookup_codes alc, ap_hold_codes ahc
          WHERE alc.lookup_type = lv_lookup_type
            AND alc.lookup_code = lv_hold_lookup_code
            AND UPPER (ahc.hold_lookup_code) = UPPER (alc.lookup_code);
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG,
                               'Error deriving hold lookup -' || SQLERRM
                              );
      END;

      fnd_file.put_line
               (fnd_file.output,
                'Following Invoices are put on ' || p_invoice_hold || ' Hold:'
               );
      fnd_file.put_line (fnd_file.output,
                         '                                  ');
      fnd_file.put_line (fnd_file.output,
                         'Invoice Number    |  Invoice ID   ');
      fnd_file.put_line (fnd_file.output,
                         '------------------ ---------------');

--Loop begins
      FOR inv_rec IN cur_inv
      LOOP
         lv_sql_count := lv_sql_count+1;
--API to apply hold
         ap_holds_pkg.insert_single_hold
                                  (x_invoice_id            => inv_rec.invoice_id,
                                   x_hold_lookup_code      => lv_hold_lookup_code,
                                   x_hold_type             => lv_hold_type,
                                   x_hold_reason           => lv_hold_reason,
                                   x_held_by               => lv_fnd_user,
                                   x_calling_sequence      => NULL
                                  );
         fnd_file.put_line (fnd_file.output,
                               RPAD (inv_rec.invoice_num, 18, ' ')
                            || '|  '
                            || RPAD (inv_rec.invoice_id, 15, ' ')
                           );
      END LOOP;

         IF lv_sql_count = 0 THEN
           fnd_file.put_line (fnd_file.output,'  *******No Data Found*******');
         END IF;
--Loop Ends
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF (SQLCODE <> -20001)
         THEN
            fnd_message.set_name ('SQLAP', 'AP_DEBUG');
            fnd_message.set_token ('ERROR', SQLERRM);
         END IF;

         fnd_file.put_line (fnd_file.LOG,
                            'Error in prc: assign_invhold_prc -' || SQLERRM
                           );
         app_exception.raise_exception;
   END assign_invhold_prc;

Note: Use ap_holds_pkg.release_single_hold to release hold by passing required parameters like invoice_id, hold_lookup_code

Friday 26 September 2014

Viewing General Ledger (GL) Daily Currency Conversion Rates

NOTE: This SQL works in version 11.5.10.2, your version might be different!

Many companies like to have "one version of the truth" (I say "like to" as we all know how hard this is to actually achieve!). One of the ways this can be achieved is a standardised set of currency conversion rates across an organisation.

The SQL in this blog post gives you a quick and easy report showing the currency conversion rates currently being used in the GL.


If your company is using Oracle Finance software (i.e. General Ledger) and the currency conversion rates are being loaded into the system (either automatically or manually) then it makes sense to publish this information out so that other parts of the company can use it.

The SQL below will show currency conversion rates (or a specified type) between two currencies and between two dates with the latest date conversion rate entered at the top.

/* Formatted on 9/26/2014 4:44:43 PM (QP5 v5.115.810.9015) */
  SELECT   FROM_CURRENCY,
           TO_CURRENCY,
           TO_CHAR (CONVERSION_DATE, 'DD-MON-YYYY') COVERSION_DATE,
           SHOW_CONVERSION_RATE,
           SHOW_INVERSE_CON_RATE
    FROM   GL_DAILY_RATES_V
   WHERE       status_code != 'D'
           AND (FROM_CURRENCY = :FROM_CURRENCY)
           AND (TO_CURRENCY = :TO_CURRENCY)
           AND (CONVERSION_DATE >= TO_DATE (:START_DATE, 'DD-MON-YYYY')
                AND CONVERSION_DATE < TO_DATE (:END_DATE, 'DD-MON-YYYY') + 1)
           AND (USER_CONVERSION_TYPE = :USER_CONVERSION_TYPE)
ORDER BY   from_currency,
           to_currency,
           conversion_date DESC,
           user_conversion_type

In order to run this SQL you need to specify five parameters;

FROM_CURRENCY: The currency you wish to convert from (i.e. EUR)
TO_CURRENCY: The currency code you wish to convert to (i.e. GBP)
START_DATE: This is the first date you want to see in the report in the format DD-MON-YYYY
END_DATE: This is the last date you want to see in the report in the format DD-MON-YYYY
USER_CONVERSION_TYPE: This will be dependant on your system, I'd recommend you look in the GL_DAILY_RATES_V view and find out the values used at your site and then plug one of those in.

Forms Personalization to add a Web Link in the Tools menu


1) Login to your responsibility

2) Go to the Forms you need to personalize


3) Open personalization forms :  Help/Diagnostics/Custom Code/personalize




4) Enter sequence as ’1′ and in description enter ‘Init Web URL menu’




5) In condition tab, enter trigger event as ‘WHEN-NEW-FORM-INSTANCE’


\

6) Click on ‘Actions’ tab. Enter sequence as ’1′ and Type = ‘Menu’.



7) Choose Menu Entry (any of the special menu entries). Eg. Special 12

Enter menu label ( eg: Web URL )

8 ) In ‘Enabled in Blocks’ field you can choose for which blocks the special menu needs to be enabled or leave blank to be enabled for all the blocks of the forms





9) Go back to the main block where you entered ‘Init Web URL menu’.


10) Enter a new record with sequence as ’2′ and in description enter ‘Launch URL’

 


11)In Conditions tab, enter trigger event as ‘SPECIAL12′ 






12) Click on Actions tab. Enter sequence as ’1′ and Type = ‘Builtin’.

13) Choose ‘Launch a URL’ for Builtin Type



13) Enter the argument as your URL ( eg : http://appselangovan.blogspot.in) and click ‘Apply now’ button.



14 ) Save .

15) Exit form , logout , login and go to your Forms again.


16) Go to Tools menu. The new special menu ( Web URL ) entry will be displayed.
Click to launch URL.