Validating the
Custom(User Entered) Exchange Rate
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”
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
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.
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.
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.
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
Need to create the Below Functions inside the Custom.pll
FUNCTION
GET_LEDGER_CURRENCY (p_set_of_books_id
NUMBER)
GET_LEDGER_CURRENCY (p_set_of_books_id
NUMBER)
RETURN
VARCHAR2
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 : ‘
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: ‘
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,
GET_EXCHANGE_VARIANCE (p_set_of_books_id NUMBER,
p_org_id
number)
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
1 = 1
AND lookup_type = ‘ XXX_AP_GL_CUSTOM_RATE
AND NVL (ENABLED_FLAG, ‘N’) = ‘Y’
AND TRUNC (SYSDATE) BETWEEN TRUNC
(START_DATE_ACTIVE)
(START_DATE_ACTIVE)
AND TRUNC (
NVL
(END_DATE_ACTIVE, SYSDATE))
(END_DATE_ACTIVE, SYSDATE))
AND MEANING = P_ORG_ID
AND DESCRIPTION =
ledger_country_code;
ledger_country_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/*
fnd_message.set_string (
fnd_message.set_string (
‘Variance code not found the
combination : ‘
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: ‘
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,
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
1 = 1
AND FROM_CURRENCY =
P_invoice_currency
P_invoice_currency
AND TO_CURRENCY =
ledger_country_code
ledger_country_code
AND TO_CHAR(CONVERSION_DATE,
‘DD-MON-YYYY’) = to_char(p_CONVERSION_DATE,’DD-MON-YYYY’)
‘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 : ‘
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: ‘
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.
Functions are Created then we need to Write over login in Appropriate Trigger
as per over Requirement.
Sample
Code
Code
IF form_name = ‘APXINWKB’ AND block_name =
‘INV_SUM_FOLDER’ THEN
‘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’));
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
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
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
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
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’));
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
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’));
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_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’);
NAME_IN(‘INV_SUM_FOLDER.RATE_DSP’);
IF
NAME_IN(‘INV_SUM_FOLDER.RATE_DSP’) IS NOT NULL THEN
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
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;
————————————————————————————————————-
Recommended Posts
