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;
————————————————————————————————————-
  • October 12, 2015 | 16 views