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.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
-------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment