Wednesday, June 24, 2009

How to Stop Accrual Plans (Leave Balances) from Going Negative


If using the Absence form, you will get a warning message if an employee takes more hours than are accrued - but it's a soft error, and you can just click OK to go through it.

To prohibit the accrual plan from going negative, there are two distinct options: A. If you are using OTL, you can alter OTL behavior B. If you are not using OTL, you will alter the absence API behavior

Option A: If you are using Oracle Time and Labor, you can setup Time Entry Rules that prohibit the accrual plan from going negative. You have to use the US OTL Application Developer Responsibility. Navigation Path: Time Entry Rules -> Define Time Entry Rules.

Option B:
If you are not using OTL, you will alter the absence API behavior using API user hooks.

a) The API that is being used: HR_PERSON_ABSENCE_API

b) If preventing the PTO balance from going negative, the user hook will have to check the boolean parameter called 'p_exceeds_pto_entit_warning'. If this is set to TRUE then raise an error message.

Implementing User Hooks:
1. Choose the API you wish to hook some extra logic to.

SELECT AHK.API_HOOK_ID,
AHK.HOOK_PACKAGE,
AHK.HOOK_PROCEDURE
FROM HR_API_HOOKS AHK,
HR_API_MODULES AHM
WHERE
AHM.MODULE_NAME LIKE 'CREATE_PERSON_ABSENCE'
AND AHM.API_MODULE_TYPE = 'BP' AND
AHK.API_HOOK_TYPE = 'AP'
AND
AHK.API_MODULE_ID=AHM.API_MODULE_ID;

it returns API_HOOK_ID, HOOK_PACKAGE, HOOK_PROCEDURE values
2. Write the PL/SQL procedure that you wish to be called by the hook.

CREATE OR REPLACE PACKAGE APL_NEG_BAL_CHECK AS
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN);
END APL_NEG_BAL_CHECK;


CREATE OR REPLACE Package Body APL_NEG_BAL_CHECK as
PROCEDURE APL_NEG_ACR_BAL_CHK
( P_ABSENCE_ATTENDANCE_TYPE_ID NUMBER
,P_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN)
IS

CURSOR CSR_GET_ABS_CAT IS
SELECT ABSENCE_CATEGORY
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE ABSENCE_ATTENDANCE_TYPE_ID = P_ABSENCE_ATTENDANCE_TYPE_ID;
L_ABS_CATEGORY VARCHAR2(20);

BEGIN

--Get Absence Category
OPEN CSR_GET_ABS_CAT;
FETCH CSR_GET_ABS_CAT INTO L_ABS_CATEGORY;
CLOSE CSR_GET_ABS_CAT;

IF (L_ABS_CATEGORY ='H') AND (P_EXCEEDS_PTO_ENTIT_WARNING = TRUE) THEN
fnd_message.set_name('PER','HR_LOA_EMP_NOT_ENTITLED');
fnd_message.raise_error;
END IF;
END APL_NEG_ACR_BAL_CHK;
END APL_NEG_BAL_CHECK;

3. Register or associate the procedure you have written

Pass the input values like HOOK_ID from Step 1
declare
l_api_hook_call_id number;
l_object_version_number number;
begin
hr_api_hook_call_api.create_api_hook_call
(p_validate => false,
p_effective_date => to_date('01-JUL-1999','DD-MON-YYYY'),
p_api_hook_id => 3870,
p_api_hook_call_type => 'PP',
p_sequence => 3000,
p_enabled_flag => 'Y',
p_call_package => 'APL_NEG_BAL_CHECK',
p_call_procedure => 'APL_NEG_ACR_BAL_CHK',
p_api_hook_call_id => l_api_hook_call_id,
p_object_version_number => l_object_version_number);
end;

check is registration successful or not , run the below script
SELECT * FROM HR_API_HOOK_CALLS WHERE CALL_PACKAGE='NEG_BAL_CHECK'

4. Run the pre-processor program which builds the logic to execute your PL/SQL procedure from the hook.
To run the pre-processor run one of the following commands:
cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql
If it successful below lines will be added to the HR_PERSON_ABSENCE_BK1 API.

if hr_api.call_cus_hooks then
NEG_BAL_CHECK.NEG_ACR_BAL_CHK
(P_EXCEEDS_PTO_ENTIT_WARNING => P_EXCEEDS_PTO_ENTIT_WARNING
);end if;

5. Now you try to apply leave, the Absence page will not allowed you for next page if Accrual balance is Zero.

Thursday, June 4, 2009

APP-FND-01564: ORACLE error 1403 in changepassword

When we Try to change all schemas the Password using FNDCPASS, we had encountered the “SECURITY-UNABLE TO CONNECT TO SYSTEM “ error.
$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE welcome

ERROR:

Related log file(L7187704.log) shows the Error message like below:
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
module:
+---------------------------------------------------------------------------+
Current system time is 04-MAY-2009 16:04:57
+---------------------------------------------------------------------------+
SECURITY-UNABLE TO CONNECT TO SYSTEM
APP-FND-01564: ORACLE error 1403 in changepassword
Cause: changepassword failed due to ORA-01403: no data found.

The SQL statement being executed at the time of the error was: and was executed from the file &ERRFILE.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 04-MAY-2009 16:04:57
+---------------------------------------------------------------------------+
Solution:
Recreating the grants & synonyms will solve the Issue with FNDCPASS

ODX-0251: Unable to find stored variable "SQL_115_AX"

I had faced the below issue ,While running the Payables invoice data Diagnostic test in R12. For that issue we had raised a Service request with Oracle. But no further progress from Oracle Support.
Version Info:

Release = 12.0.4
Patch OD for R12 RUP3 [12.0.3] (6154018) is installed
Patch Oracle Diagnostics Tools, R12.IZU.A.DELTA.4 (6497339) is installed

Issue:
ODX-0060: SQL has not been executed as table or view does not exist (ORA-00942 returned)Error executing element "sql" with the name "SQL_115_AX"Error in the sql statement:
"SELECT event_id FROM AX_EVENTS WHERE application_id = 200 AND event_type like 'NON_CASH%' AND event_field1 = :1 AND NVL(org_id, -99)= :2 UNION SELECT event_id FROM AX_EVENTS WHERE application_id = 200 AND ( event_type like 'CASH%' OR event_type like 'FUTURE%' ) AND event_field1 IN ( SELECT check_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id = :3 ) AND NVL(org_id, -99) = :4" with the bind values:"'38658', '82', '38658', '82'".
SQLERROR - ODX-0251: Unable to find stored variable "SQL_115_AX".ACTION -
If the XML file for this test was delivered by Oracle Support Services, contact the support representative for assistanceXML File Information

XML Engine Version = 3.2.1

File Name = APListXml.xml (File Version = 120.0.12000000.8, File Location = ap/diag, Date = 2008/04/24)

But Finally we find out the resolution. That issue occurred with synonym of AX_EVENTS Table.

Solution:
1. Recreate the grants & synonyms through adadmin & Complie Apps Schema.