Creating Userhook in HRMS

Create a user hook as same as the seeded hook. Now create the below procedure

cursor c_get_absence(p_absence_attenance_id in number) is

— SELECT max(trunc(hat.creation_date)) creation_date– into l_transaction_creation_date
SELECT trunc(hat.creation_date) creation_date– into l_transaction_creation_date
FROM hr_api_transactions hat,
hr_api_transaction_steps hats
WHERE 1=1
and hats.transaction_id=hat.transaction_id
and hat.transaction_ref_table=’PER_ABSENCE_ATTENDANCES’
and hats.pk1 = p_absence_attendance_id
and hats.information5 = 61; –Restricting leave
BEGIN
if(p_absence_attendance_id is not null)

then
xx_debug(‘Validation passed p_absence_attendance_id no null’); –For Debugging
for loop_absence in c_get_absence(p_absence_attendance_id) loop
if(p_date_start > (loop_absence.CREATION_DATE+7) and p_date_end < add_months(loop_absence.CREATION_DATE, 12)) –Validation Logic
then
dbms_output.put_line(‘Validations are passed’);

xx_debug(‘Validation passed’); –For Debugging
else
xx_debug(‘Fire Validation’|| loop_absence.CREATION_DATE);
HR_UTILITY.SET_MESSAGE (20042, ‘XX_HR_LEAVE_DURATION’);
HR_UTILITY.RAISE_ERROR;
end if ;
end loop;
end if;
END create_person_absence_a;

Writing Validation in SIT, EIT, Leave

For SIT Validation refer to FND_FLEX_PLSQL

For EIT Validation use userhook and CO Extension

For leave refer BG_ABSENCE_DURATION Fast Formula, Userhook and CO Extension

Tip : To Identify the Error and narrow down better enter transactions in both Java Form Pages and OAF Pages

I will write a detailed post once I get time.

Blank Page Issue faced when Upgradation

We have Upgraded the Database and Application after upgradation we are facing this issue in Workflow

Database Upgradation from 10g to  12c Database

Application Upgradation from 12.0.X to  12.2.X

Issue : Final Approver approvers a transaction request where that workflow has reference to DB link then the Issue is reproducible. After approval when user clicks on any notification the page appears to be blank as in the below screenshot. This gets resolved after certain period of time(approx. 15 minutes) or when Bouncing the server.

Root Cause : We have analyzed and tested that this Issue is due to the DB link which is being called in Workflow package at approval.

Note : If the DB link as_persons is called in any workflow the same Issue gets replicated.

Solution/Fix : To Upgrade the database that is causing the Issue.

Feel free to give any suggestions

Intermediate DB Link steps performed – Issue still persists

Objective: To Create Intermediate DB Link steps to check whether Blank Page Issue gets resolved.

Summary: Approach using Intermediate DB Link to check whether the Blank Page Issue gets resolved. After executing the below steps till the Blank Page Issue still occurs.

Instances :

• Base Instance(12c) where we A the Functionality
• It is Intermediate Instance(12c)
• It is the Old 10g Instance

As Mentioned in the Diagram below are the Steps

• Step 1: Creation of Table in C Instance
• Step 2: Creation of DB Link in B Instance
• Step 3: Creation of Synonym in B Instance
• Step 4: Creation of DB Link in A Instance
• Step 5: Creation of Synonym in A Instance
• Step 5a: Creation and Dropping of Temporary DB Link from A to C to overcome Compilation Error
• Step 6: Reference of Synonym to Package
• Step 7: Reference of Synonym to Workflow

Things to be considered

• Ensure that synonym should be as same as the Table name.
• Need to create another DB link to C Instance as per below link or it will give the error

ORA-00980: synonym translation is no longer valid

• http://fprincipe.altervista.org/portale/?q=it/node/98
• Without performing the above step (Step 5a) the package ends in Invalid state.

Steps that are Performed:

1. Creation of Table in C Instance

create table XXXXXX_DBOBJ_C as select * from XX_XXXXXX;

2. Creation of DB Link in B Instance

CREATE DATABASE LINK XXBLANK_C

CONNECT TO apps IDENTIFIED BY apps

USING ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXX) (PORT = XXXX) ) (CONNECT_DATA = (SERVICE_NAME = XXX) (INSTANCE_NAME = XXX) ) )’

3. Creation of Synonym in B Instance

CREATE OR REPLACE  PUBLIC SYNONYM XXXXXX_DBOBJ_C FOR XXXXXX_DBOBJ_C@XXBLANK_C

4. Creation of DB Link in A Instance

CREATE DATABASE LINK XXBLANK_B

CONNECT TO apps IDENTIFIED BY apps

USING ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXX) (PORT = XXXX) ) (CONNECT_DATA = (SERVICE_NAME = XXX) (INSTANCE_NAME = XXX) ) )’

5. Creation of Synonym in A Instance

CREATE OR REPLACE  PUBLIC SYNONYM XXXXXX_DBOBJ_C FOR XXXXXX_DBOBJ_C@XXBLANK_B

6. Creation of DB to overcome the Package to Invalid state

CREATE DATABASE LINK XXBLANK_C

CONNECT TO apps IDENTIFIED BY apps

USING ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXX) (PORT = XXXX) ) (CONNECT_DATA = (SERVICE_NAME = XXX) (INSTANCE_NAME = XXX) ) )’

7. Once the Package referring the XXXXXX_DBOBJ_C Synonym in A gets compiled then we can drop the DB Link. This is to Ensure that DB Link to be called to 10g using Intermediate DB link

drop database link XXBLANK_C.XXXX

8. Once the above DB Objects are done we are set A the Flow

Result: After Testing the Blank Page appears and so we cannot consider this as a solution. However, we can appreciate the same for an Approach.

Stock Locator API creation in Oracle Apps – Error

A requirement has been given to create Stock Locator by the below navigation

Warehouse Manager Responsibility –> Setup –> Warehouse Configuration –> Warehouses –> Stock Locators

For this requirement I have searched and got the document ID(Doc ID 1905843.1, Doc ID 297598.1) for Sample Script from Oracle and I have started to create. Below is the code

DECLARE
l_msg_data VARCHAR2 (100);
l_msg_count NUMBER;
l_return_status VARCHAR2 (1);
l_locator_id NUMBER;
l_locator_exists VARCHAR2 (1);
-- l_org_id NUMBER := 1884;
l_org_id NUMBER := 221;
-- l_organization_code VARCHAR2(10) := 'W1';
l_organization_code VARCHAR2 (10) := 'A01';
l_sub_code VARCHAR2 (10) := 'A01-WMSDIP';
l_concatenated_segments VARCHAR2 (100) := 'P02-01-001--';
l_user_id NUMBER := 0;
l_resp_id NUMBER := 52436;
l_resp_appl_id NUMBER := 385;
BEGIN
/*
* APPS_INITIALIZE needed because Project/Task Valuesets indirectly use
* profile options
*/
FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_resp_appl_id);

/*
* Create 5 Project Locators Stor.41.1...to BULK.46.5 in Organization 'P1' (id 607)
* and subinventory 'Stores' for the Project 'Pacific Pumps' with Project id 1716
*
*/

FOR i IN 1 .. 2
LOOP
    /*
    * p_concatenated_segments should always be the value that you see in the desktop forms
    * not internal ids
    * If the desktop form shows 'Stor.1.1.Pacific Pumps.' -- the same should be passed to
    * the API
    *
    */
    l_concatenated_segments := 'P02-01-001--';
    FND_MSG_PUB.INITIALIZE;

    /*
    * First create physical locators first and use the returned value of x_inventory_location_id'
    * in the next call
    *
    */

    DBMS_OUTPUT.PUT_LINE ('Trying to create ' || l_concatenated_segments);

    INV_LOC_WMS_PUB.CREATE_LOCATOR (
        x_return_status              => l_return_status,
        x_msg_count                  => l_msg_count,
        x_msg_data                   => l_msg_data,
        x_inventory_location_id      => l_locator_id,
        x_locator_exists             => l_locator_exists,
        p_organization_id            => l_org_id,
        p_organization_code          => l_organization_code,
        p_concatenated_segments      => l_concatenated_segments,
        p_description                => 'Created by Script',
        p_inventory_location_type    => 3,              -- Storage locator
        p_picking_order              => NULL,
        p_location_maximum_units     => NULL,
        p_subinventory_code          => l_sub_code,
        p_location_weight_uom_code   => NULL,
        p_max_weight                 => NULL,
        p_volume_uom_code            => NULL,
        p_max_cubic_area             => NULL,
        p_x_coordinate               => NULL,
        p_y_coordinate               => NULL,
        p_z_coordinate               => NULL,
        p_physical_location_id       => NULL,
        p_pick_uom_code              => NULL,
        p_dimension_uom_code         => NULL,
        p_length                     => NULL,
        p_width                      => NULL,
        p_height                     => NULL,
        p_status_id                  => 1,      -- Default status 'Active'
        p_dropping_order             => NULL);

    DBMS_OUTPUT.PUT_LINE ('Return Status ' || l_return_status);

    IF l_return_status IN ('E', 'U')
    THEN
        DBMS_OUTPUT.PUT_LINE ('# of Errors ' || l_msg_count);

        IF l_msg_count = 1
        THEN
            DBMS_OUTPUT.PUT_LINE ('Error ' || l_msg_data);
        ELSE
            FOR i IN 1 .. l_msg_count
            LOOP
                DBMS_OUTPUT.PUT_LINE (
                    'Error ' || FND_MSG_PUB.GET (i, 'F'));
            END LOOP;
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE ('Locator Id is ' || l_locator_id);
    END IF;
END LOOP;
END;

When executing I was getting the below Error

Trying to create P02-01-001–
Return Status U
# of Errors 1
Error Could not create locator.

After Searching the Error I found the fix in Oracle Document(Doc ID 1600311.1). So now I have included the fnd_profile.put(‘MFG_ORGANIZATION_ID’,ORGID) ; in my code and when executed the error was not coming.

Below is the full code

DECLARE
l_msg_data VARCHAR2 (100);
l_msg_count NUMBER;
l_return_status VARCHAR2 (1);
l_locator_id NUMBER;
l_locator_exists VARCHAR2 (1);
-- l_org_id NUMBER := 1884;
l_org_id NUMBER := 221;
-- l_organization_code VARCHAR2(10) := 'W1';
l_organization_code VARCHAR2 (10) := 'A01';
l_sub_code VARCHAR2 (10) := 'A01-WMSDIP';
l_concatenated_segments VARCHAR2 (100) := 'P02-01-001--';
l_user_id NUMBER := 0;
l_resp_id NUMBER := 52436;
l_resp_appl_id NUMBER := 385;
BEGIN
/*
* APPS_INITIALIZE needed because Project/Task Valuesets indirectly use
* profile options
*/
FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_resp_appl_id);

/*
* Create 5 Project Locators Stor.41.1...to BULK.46.5 in Organization 'P1' (id 607)
* and subinventory 'Stores' for the Project 'Pacific Pumps' with Project id 1716
*
*/

FOR i IN 1 .. 2
LOOP
    /*
    * p_concatenated_segments should always be the value that you see in the desktop forms
    * not internal ids
    * If the desktop form shows 'Stor.1.1.Pacific Pumps.' -- the same should be passed to
    * the API
    *
    */
    l_concatenated_segments := 'P02-01-001--';
    FND_MSG_PUB.INITIALIZE;
    fnd_profile.put ('MFG_ORGANIZATION_ID', l_org_id); -- Code to Resolve the Error
    /*
    * First create physical locators first and use the returned value of x_inventory_location_id'
    * in the next call
    *
    */

    DBMS_OUTPUT.PUT_LINE ('Trying to create ' || l_concatenated_segments);

    INV_LOC_WMS_PUB.CREATE_LOCATOR (
        x_return_status              => l_return_status,
        x_msg_count                  => l_msg_count,
        x_msg_data                   => l_msg_data,
        x_inventory_location_id      => l_locator_id,
        x_locator_exists             => l_locator_exists,
        p_organization_id            => l_org_id,
        p_organization_code          => l_organization_code,
        p_concatenated_segments      => l_concatenated_segments,
        p_description                => 'Created by Script',
        p_inventory_location_type    => 3,              -- Storage locator
        p_picking_order              => NULL,
        p_location_maximum_units     => NULL,
        p_subinventory_code          => l_sub_code,
        p_location_weight_uom_code   => NULL,
        p_max_weight                 => NULL,
        p_volume_uom_code            => NULL,
        p_max_cubic_area             => NULL,
        p_x_coordinate               => NULL,
        p_y_coordinate               => NULL,
        p_z_coordinate               => NULL,
        p_physical_location_id       => NULL,
        p_pick_uom_code              => NULL,
        p_dimension_uom_code         => NULL,
        p_length                     => NULL,
        p_width                      => NULL,
        p_height                     => NULL,
        p_status_id                  => 1,      -- Default status 'Active'
        p_dropping_order             => NULL);

    DBMS_OUTPUT.PUT_LINE ('Return Status ' || l_return_status);

    IF l_return_status IN ('E', 'U')
    THEN
        DBMS_OUTPUT.PUT_LINE ('# of Errors ' || l_msg_count);

        IF l_msg_count = 1
        THEN
            DBMS_OUTPUT.PUT_LINE ('Error ' || l_msg_data);
        ELSE
            FOR i IN 1 .. l_msg_count
            LOOP
                DBMS_OUTPUT.PUT_LINE (
                    'Error ' || FND_MSG_PUB.GET (i, 'F'));
            END LOOP;
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE ('Locator Id is ' || l_locator_id);
    commit;
    END IF;

END LOOP;
END;