Monday, 12 October 2015

Validating the Custom(User Entered) Exchange Rate

Validating the Custom(User Entered) Exchange Rate

à Need to Enable the setup in the Payable Options for Enabling the Exchange Rate or Functional Amount Columns if Exchange Type is "USER"

Below Is the Navigation to enable :

Setup à Options à Payable Options à Currency à Enable Calculate User Exchange Rate



à Once  the Setup is Enabled then user can have a Provision to Enter the Exchange Rate or Functional Amount columns then the Other one would get auto populated.

à Now for Entered (or) Populated Exchange Rate needs to be Validated with the Standard GL Rate. if Requires based on the Requirement we can maintain a Threshold Value and validate it based on it for which the Custom Exchange Rate is within the Threshold(+/-) or not and  proceed with required action.

Sample Lookup For maintain Threshold


à Once the above Payable Option and Threshold Value is Maintained then we need to Write over logic in the Custom.pll.

à Need to create the Below Functions inside the Custom.pll

FUNCTION GET_LEDGER_CURRENCY (p_set_of_books_id     NUMBER)
RETURN VARCHAR2
IS  
   ledger_country_code   VARCHAR2 (25);
BEGIN
   SELECT CURRENCY_CODE
     INTO ledger_country_code
     FROM gl_ledgers gl
    WHERE gl.ledger_id = p_set_of_books_id;
 
   RETURN ledger_country_code;
 
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_message.set_string (
               'Ledger Currency not found the combination : '
            || 'Ledger Currency :'
            || ledger_country_code);
         fnd_message.error;
         ledger_country_code := NULL;
      WHEN OTHERS
      THEN
         fnd_message.set_string (
               'Error while getting Ledger Currency: '
            || 'Ledger_currency :'
            || ledger_country_code
            || ' '
            || ' Error: '
            || SQLERRM);
         fnd_message.error;
         ledger_country_code := NULL;

  END;   
  ----------------------------------------------------------------------------------------------------------------------

FUNCTION GET_EXCHANGE_VARIANCE (p_set_of_books_id NUMBER,
                                p_org_id number)
   RETURN VARCHAR2
IS
   Variance_code         NUMBER;
   ledger_country_code   VARCHAR2 (25);
   v_Count                 Number ;
BEGIN

   SELECT CURRENCY_CODE
     INTO ledger_country_code
     FROM gl_ledgers gl
    WHERE gl.ledger_id = p_set_of_books_id;

   BEGIN
  
    SELECT tag
       INTO Variance_code
        FROM FND_LOOKUP_VALUES
       WHERE     1 = 1
             AND lookup_type = ' XXX_AP_GL_CUSTOM_RATE
             AND NVL (ENABLED_FLAG, 'N') = 'Y'
             AND TRUNC (SYSDATE) BETWEEN TRUNC (START_DATE_ACTIVE)
                                     AND TRUNC (
                                            NVL (END_DATE_ACTIVE, SYSDATE))
             AND MEANING = P_ORG_ID
             AND DESCRIPTION = ledger_country_code;
            
                
  EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
      /*   fnd_message.set_string (
               'Variance code not found the combination : '
            || 'Org id :'
            || P_ORG_ID
            || 'CURRENCY_CODE :'
            || ledger_country_code);
         fnd_message.error; */
         Variance_code := null;
      WHEN OTHERS
      THEN
         fnd_message.set_string (
               'Error while getting Variance code: '
            || 'Org id :'
            || P_ORG_ID
            || 'CURRENCY_CODE :'
            || ledger_country_code
            || ' '
            || ' Error: '
            || SQLERRM);
         fnd_message.error;
         Variance_code := NULL;
   END;
   RETURN Variance_code;
END;

-----------------------------------------------------------------------------------------------------------------------

FUNCTION GET_GL_RATE (p_set_of_books_id     NUMBER,
                      P_invoice_currency    VARCHAR2,
                      p_CONVERSION_DATE     DATE)
   RETURN VARCHAR2
IS
   GL_RATE               number;
   ledger_country_code   VARCHAR2 (25);
BEGIN
   SELECT CURRENCY_CODE
     INTO ledger_country_code
     FROM gl_ledgers gl
    WHERE gl.ledger_id = p_set_of_books_id;

   BEGIN
      SELECT a.CONVERSION_RATE
        INTO GL_RATE
        FROM gl_daily_rates a
       WHERE     1 = 1
             AND FROM_CURRENCY = P_invoice_currency
             AND TO_CURRENCY = ledger_country_code
             AND TO_CHAR(CONVERSION_DATE, 'DD-MON-YYYY') = to_char(p_CONVERSION_DATE,'DD-MON-YYYY')
             AND CONVERSION_TYPE = 'Corporate';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_message.set_string (
               'GL Rate not found the combination : '
            || 'Date :'
            || p_CONVERSION_DATE
            || ' '
            || 'From Cur :'
            || P_invoice_currency
            || ' '
            || 'To_cur :'
            || ledger_country_code);
         fnd_message.error;
         GL_RATE := NULL;
      WHEN OTHERS
      THEN
         fnd_message.set_string (
               'Error while getting Convertion rate: '
            || 'Date :'
            || p_CONVERSION_DATE
            || ' '
            || 'From Cur :'
            || P_invoice_currency
            || ' '
            || 'To_cur :'
            || ledger_country_code
            || ' '
            || ' Error: '
            || SQLERRM);
         fnd_message.error;
         GL_RATE := NULL;
   END;

   RETURN GL_RATE;
END;     

à Once the above Functions are Created then we need to Write over login in Appropriate Trigger as per over Requirement.

Sample Code

  IF form_name = 'APXINWKB' AND block_name = 'INV_SUM_FOLDER' THEN

                    v_var_exchange_rate := GET_EXCHANGE_VARIANCE (NAME_IN('INV_SUM_FOLDER.SET_OF_BOOKS_ID'),NAME_IN('INV_SUM_FOLDER.ORG_ID'));

      IF  v_var_exchange_rate IS NOT NULL THEN

          IF GET_RECORD_PROPERTY(GET_BLOCK_PROPERTY('INV_SUM_FOLDER', CURRENT_RECORD),'INV_SUM_FOLDER',STATUS) IN ('INSERT','CHANGED') THEN

             IF NAME_IN('INV_SUM_FOLDER.USER_RATE_TYPE') = 'User' AND  NAME_IN('INV_SUM_FOLDER.INVOICE_AMOUNT') IS NOT NULL
                      AND NAME_IN('INV_SUM_FOLDER.BASE_AMOUNT_DSP') IS NOT NULL THEN

                  v_ledger_currency := GET_LEDGER_CURRENCY(NAME_IN('INV_SUM_FOLDER.SET_OF_BOOKS_ID'));

                 IF v_ledger_currency IS NOT NULL AND v_ledger_currency <> NAME_IN('INV_SUM_FOLDER.INVOICE_CURRENCY_CODE') THEN

                                 V_GL_exchange_rate := GET_GL_RATE(NAME_IN('INV_SUM_FOLDER.SET_OF_BOOKS_ID'),NAME_IN('INV_SUM_FOLDER.INVOICE_CURRENCY_CODE'), NAME_IN('INV_SUM_FOLDER.EXCHANGE_DATE'));

                    END IF; 

                      IF  v_var_exchange_rate IS NOT NULL AND v_gl_exchange_rate IS NOT NULL THEN

                            v_cal_exchange_percent :=     ((v_gl_exchange_rate * v_var_exchange_rate )/ 100);

                            v_cal_exchange_gl_Add := v_gl_exchange_rate + v_cal_exchange_percent;

                            v_cal_exchange_gl_Sub := v_gl_exchange_rate - v_cal_exchange_percent;

                            v_Exchange_rate := NAME_IN('INV_SUM_FOLDER.RATE_DSP');

                            IF NAME_IN('INV_SUM_FOLDER.RATE_DSP') IS NOT NULL THEN

                                    IF v_Exchange_rate <= v_cal_exchange_gl_Add AND v_Exchange_rate >= v_cal_exchange_gl_Sub THEN
                                               NULL;
                                                ELSE
                                                fnd_message.set_string (GET_MESSAGE('XXX_AP_EXCHANGE_VARIANCE'));
                                                fnd_message.ERROR;
                                                RAISE FORM_TRIGGER_FAILURE;
                            END IF;
                      END IF;
                 END IF;
             END IF;
          END IF;
      END IF;
  END IF;

-------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment