Displaying only Characters and Number in SQL

Sometimes there might be special characters and extended ASCII Characters in the Data and it needs to be removed. Usually presence of extended ASCII Characters can result in Report Output. Below is the simple query to restrict it.

select regexp_replace(description,'[^0-9a-zA-Z]','') str, description from mtl_system_items where segment1 =p_item_code;

set define off;
select regexp_replace('The Social & Sport Contribution Fund','[^0-9a-zA-Z ]','') str from dual;

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;

Query to link two columns in HRMS Table structure

We have a requirement that we need to map the Payroll, Visa and Company. Initially we had Visa and Company Mapping in Table Structure in HRMS. Now we have link the same with Payroll.

We went for some options that is to map the Company name in Payroll but as it was one to many combination we left it.
Next we tried for Lookup but we came to conclusion we can create another column and link the values.

Below is the query with Visa(Values) and company(Row) Mapping that is linking table column and row.

SELECT pur.ROW_LOW_RANGE_OR_NAME company_name
FROM PAY_USER_TABLES_FV put
,PAY_USER_COLUMNS_FV puc
,pay_user_rows_f pur
,pay_user_column_instances_f puci
WHERE put.base_user_table_name = 'XX_TABLE_NAME'
AND puc.base_user_column_name = 'VISA_TYPE'
AND put.user_table_id = puc.user_table_id
AND put.user_table_id = puc.user_table_id
AND pur.user_row_id = puci.user_row_id
AND puc.user_column_id = puci.user_column_id
AND sysdate between pur.effective_start_date and pur.effective_end_date
AND sysdate between puci.effective_start_date and puci.effective_end_date
AND puci.value = p_visa_type;

With the above query as base we have created the below query. We have inputs as p_visa_type and p_payroll_id and we link two columns

Exact	Visa	Payroll
Company-1	Visa Val-1	Payr Val-1
Company-2	Visa Val-2	Payr Val-2

SELECT pur_visa.ROW_LOW_RANGE_OR_NAME company_name, puci_visa.value VISA, puci_payr.value PAYROLL_NAME
FROM PAY_USER_TABLES_FV put
,PAY_USER_COLUMNS_FV puc_visa
,pay_user_rows_f pur_visa
,PAY_USER_COLUMNS_FV puc_payr
,pay_user_rows_f pur_payr
,pay_user_column_instances_f puci_visa
,pay_user_column_instances_f puci_payr
WHERE put.base_user_table_name = 'XX_TABLE_NAME'
AND puc_visa.base_user_column_name = 'VISA_TYPE'
AND puc_payr.base_user_column_name = 'PAYROLL'
AND put.user_table_id = puc_visa.user_table_id
AND put.user_table_id = puc_payr.user_table_id
AND pur_visa.user_row_id = puci_visa.user_row_id
AND pur_payr.user_row_id = puci_payr.user_row_id
AND puc_visa.user_column_id = puci_visa.user_column_id
AND puc_payr.user_column_id = puci_payr.user_column_id
AND sysdate between pur_visa.effective_start_date and pur_visa.effective_end_date
AND sysdate between pur_payr.effective_start_date and pur_payr.effective_end_date
AND sysdate between puci_visa.effective_start_date and puci_visa.effective_end_date
AND sysdate between puci_payr.effective_start_date and puci_payr.effective_end_date
and pur_visa.ROW_LOW_RANGE_OR_NAME = pur_payr.ROW_LOW_RANGE_OR_NAME --Where we link the Values
AND puci.value = p_visa_type
AND puci_payr.value = (select payroll_name from pay_all_payrolls_f where sysdate between effective_start_date and effective_end_date and payroll_id = p_payroll_id);

Oracle query to get hostname, portname and service name

On Executing the below query we can get hostname, portname

select * from v$listener_network;

On Executing the below query we can get service name

select * from v$parameter where name = ‘service_names’

Based on this we can verify or use the details in any Oracle sql client.