Helping with Oracle EBS

Wednesday, August 16, 2017

Unable to connect Oracle using oci8 in wamp php

This is to fix the issues related to 32 Bit wamp server installed on windows 64

I have composed these steps after researching in many blogs/forums for the errors as

unable to load dynamic library php_oci8_12c.dll - the specified procedure could not be found

1. Download
wampserver3.0.6_x86_apache2.4.23_mysql5.7.14_php5.6.25-7.0.10.exe
instantclient-basic-nt-12.2.0.1.0.zip (32 bit, check readme file)

1. Extract instantclient-basic-nt-12.2.0.1.0.zip into any local folder ex: d:\oracle32bitclient
2. Install wampserver3.0.6_x86_apache2.4.23_mysql5.7.14_php5.6.25-7.0.10.exe
3. set path using system->Advanced system settings->environemnt variables->system variables
select path
click new
add d:\oracle32bitclient\instantclient_12_2 (where all DLLs are available)
4. goto command prompt, execute below path to make sure the above path is added in path variable, if not goto 5
echo %path%
5. goto command prompt
set path=%path%;d:\oracle32bitclient\instantclient_12_2
execute below command to see the above path can be seen
echo %path%

5. by default wamp points to version php5.6.25
goto C:\wamp\bin\php\php5.6.25
locate php.ini and open in notepad/textpad to edit
search for "extension_dir"
make sure there is no prefix of ';' and pointed to correct path (this is set by wamp installation)
ex: extension_dir ="c:/wamp/bin/php/php5.6.25/ext/"

in the same php.ini
search for oci8
you may find as below
extension=php_oci8_12c.dll ; Use with Oracle Database 12c Instant Client
wamp installer comments this line by default
so remove ; before this line

save php.ini

6. start wamp server. Once started, on the system tray you can see wamp icon (in green color), click on this (not right click, its left click)
verify below
under "PHP"
verion
is defaults to 5.6.25
PHP extensions
php_oci8_12c -- this is not selected by default
now select this
7. again click on wamp server (if you see it is green, if not green wait until it turns green as it might have restarted for above changes)
8. select restart all services
9. Once wamp server is ready with green you can verify (step 6 again)
10. open command prompt
11. cd c:/wamp/bin/php/php5.6.25/ (for you where php 5.6.25 is located)
12. execute the below
php -v
this should give current php version without any warnings
php --ri oci8
this gives the current statuses of oci8

13. create a simple oci8 test script whether you can connect oracle
make sure you have any local database or remote database connection with below information
server name or localhost
database name, user and password

if it is xe database, create a simple php file as below
make sure user, password, database are correct

<?php
$conn = oci_connect('system','welcome', 'xe');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
echo "connected"
?>

and save as testoci8.php under C:\wamp\www

14. now, open any browser and execute the url
http://localhost/testoci8.php

it should display "connected" without any other information

fatal error call to undefined function oci_connect

15. If you still see any fatal error with undefined oci_connect
copy the below files from instant client location (in this example d:\oracle32bitclient\instantclient_12_2)
oci.dll
oraociei12.dll
oraons.dll

to wamp/bin/apche/apache<version>/bin -- version is your current version

and restart apache and try http://localhost/testoci8.php

(** I have faced this issue during installation on one of my laptops)
I have same configuration as other and wamp, instant client all are same but I could not use oci_connect
not sure why it did not connect, after researing for 3 to 4 day I found this fix in one of the blogs

Friday, May 19, 2017

VO or EO substitution is not affected on the page though the XMLImporter is successful

EO or VO substation import is successful but still the replace is not reflected

Issue: I have substituted PoRequisitionHeaderEO with XXPoRequisitionHeaderEO

and saved the xml in XXPoRequisitionHeaderEO.xml and saved on local folder.

and executed using
java oracle.jrad.tools.xml.importer.XMLImporter $XXCUST_TOP/oracle/apps/icx/por/schema/server/customizations/site/0/XXPoRequisitionHeaderEO.xml -username apps -password dotapps2017 -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$DBHOST)(PORT=$DBPORT))(CONNECT_DATA=(SID=$DBSID)))" -rootdir $CUSTOM_TOP/oaf_personalization/ -rootPackage /

Import successful.

But when I checked on page the EO substitution is not shown.

I have checked below query to see the replace is successful in database. All looks good.

select * from JDR_ATTRIBUTES
where att_comp_docid in (select comp_docid from JDR_COMPONENTS where COMP_DOCID in (select path_docid from JDR_PATHS where path_name like '%PoRequisitionHeaderEO%'
));

The issue: The xml file that I saved was XXPoRequisitionHeaderEO.xml which is not correct
It should be XXPoRequisitionHeaderEO.xml

Because, when we run XMLImporter it saves file with the reference as below
/oracle/apps/icx/por/schema/server/customizations/site/0/PoRequisitionHeaderEO

but if we use the file name as XXPoRequisitionHeaderEO, the above will have PoRequisitionHeaderEO at the end. When we open page Oracle looks for site level customization which is not exists in seeded top. so the replace will not be affected.

So file name does matter when we substitute EO or VO.

Wednesday, April 02, 2014

Form personalization - Number

Numeric validation in form personalization

Trigegr event: WHEN-VALIDATE-RECORD

Trigger object : ACCOUNTS

DECODE(TRANSLATE(:ACCOUNTS.BANK_ACCOUNT_NUM,'1234567890','0000000000'),LPAD('0',LENGTH(:ACCOUNTS.BANK_ACCOUNT_NUM),'0'),'NUMBER','NOT NUMBER')='NOT NUMBER'

ex: if 33123-33 is entered, then decode convers the code as 00000-00 and LPAD function returns 0000000 , hense mismatchin and not Number.

Creating softlink and registering Host concurrent program

1. Create a host file in required TOP

Ex: $XXGL_TOP/bin/XXGL_EMAIL.prog (the extension can be any thing)

2. Create a softlink using the below command (execute this command in the same path $XXGL_TOP/bin)

ln -s $FND_TOP/bin/fndcpesr XXGL_EMAIL.prog

3. Then use the below command

XXGL_EMAIL XXGL_EMAIL.prog

4. Register the executable with XXGL_EMAIL and concurrent program and run.

How to find Oracle Apps password

Oralce apps password is stored in a file called wdbsvr.app on application server

in the below path

$IAS_ORACLE_HOME/Apache/modplsql/cfg

Generally this is not accessible by with your unix access. Only administrator can access.

Position Based Approval Hierarchy in Purchasing

Position Based Approval Hierarchy in Purchasing explained in detail

http://erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Position-Based-Approval-Hierarchy-in-Purchasing/index.aspx

Query to get HR Orgnization Grant Parent

Here is the query to get grant parent of organization

SELECT OSE.organization_id_child,OU.organization_id,OU.name, OU.type
      FROM   (SELECT POSE.organization_id_child,
                      POSE.organization_id_parent
             FROM per_org_structure_elements POSE
             WHERE POSE.org_structure_version_id = (SELECT OSV.org_structure_version_id
                                                     FROM   per_org_structure_versions OSV,
                                                        per_organization_structures OS
                                                     WHERE SYSDATE BETWEEN OSV.date_from AND NVL (OSV.date_to,SYSDATE + 1)
                                                     AND OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
                                                     AND UPPER(OS.name)               = 'XX You Company' -------------------------------------------------------------
                                                     AND OS.primary_structure_flag    = 'Y' ))OSE,
                   hr_all_organization_units OU
      WHERE   OSE.organization_id_parent      = OU.organization_id
    -- AND     UPPER(OU.name)                  = 'GROUP 2'
      AND     OU.type = 'AGP'
      CONNECT BY PRIOR organization_id_parent = organization_id_child
      START WITH organization_id_child = (select paa.organization_id
      from
                   per_all_people_F pea
                   ,per_all_assignments_f paa
where pea.person_id=paa.person_id
      and pea.person_id=:p_person_id
      and sysdate between pea.effective_start_date and NVL(pea.effective_end_date,sysdate+1)
      and sysdate between paa.effective_start_date and NVL(paa.effective_end_date,sysdate+1)
      and paa.assignment_type='E')

SQL Query to get Oracle HR Organization Hierarchy

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115 -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child

SQL Query to get Oracle HR Organization Hierarchy

Another work around for ieframe.dll error

If you are unable to uncheck the below option
Enable memory protection to help mitigate online attacks

goto c:\Program Files\Internet Explorer

right click on iexplore and select run as and administrator

then you should be able to uncheck Enable memory protection to help mitigate online attacks

if this does not work:
========================
Open internet browser
Goto tools --> Click Manage addons

Disable addons under Sun Microsystems

and restart the internet explorer and see

If that does not work

diable all the addons and try