Oracle Application TCA | Supplier API | Sample

On October 8, 2015October 8, 2015 By windows7bugsIn Oracle Applications(R12)Leave a comment

Hi guys

I’m posting a sample script for creating suppliers, sites and contacts. I’ve referred multiple sample scripts and believe the below code block is a fine tuned one, however standing refinement at all levels. Please note, I haven’t added the API block for creating banks for suppliers. Will, and update the scripts as I make advancements.

[code language=”sql” gutter=”false”]
/* Formatted on 10/5/2015 11:12:16 AM (QP5 v5.163.1008.3004) */
SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
–For supplier parameters

p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
x_vendor_id NUMBER;
x_party_id NUMBER;
V_MSG_INDEX_OUT NUMBER;

–Site parameters

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
lc_return_status VARCHAR2 (10);
ln_msg_count NUMBER;
lc_msg_data VARCHAR2 (1000);
ln_vendor_site_id NUMBER;
ln_party_site_id NUMBER;
ln_location_id NUMBER;

–Contact parameters

p_vendor_contact_rec apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;

–General exception

local_exception EXCEPTION;
local_failed_at VARCHAR2 (10);

p_vendor_number VARCHAR2(30) := NULL;

BEGIN
–Please note: This API was tested against Release 12 (12.0.6)
–You are warned against undesired results, if tried against unsupported application releases

–Initialize application
–"Master Data" responsibility details
mo_global.init (‘SQLAP’);
fnd_global.apps_initialize (user_id => 1353,
resp_id => 50997,
resp_appl_id => 200);
fnd_global.set_nls_context (‘AMERICAN’);

mo_global.set_policy_context (‘S’, 101);

p_api_version := 1.0;
p_init_msg_list := FND_API.G_TRUE;
p_commit := FND_API.G_TRUE;
p_validation_level := FND_API.G_VALID_LEVEL_FULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
p_vendor_rec.vendor_name := ‘WINDOWS7BUGS BLOG’;
p_vendor_rec.vendor_type_lookup_code := ‘VENDOR’; –Vendor type supplier
p_vendor_rec.SUMMARY_FLAG := ‘N’;
p_vendor_rec.ENABLED_FLAG := ‘Y’;
— p_vendor_rec.women_owned_flag := ‘N’;
— p_vendor_rec.small_business_flag := ‘Y’;

— Supplier MUST have a global level payment method
— So that individual companies can defer the default payment method while sites are created
— I have tried the following @ site levels, didn’t work until at supplier level assigned. You may post corrections with
— Comments section

p_vendor_rec.ext_payee_rec.Exclusive_Pay_Flag:=’N’;
p_vendor_rec.ext_payee_rec.default_pmt_method := ‘CHECK’;

— if the Payable System setup is set automatic numbering for the suppliers (table ->AP_PRODUCT_SETUP Column -> SUPPLIER_NUMBERING_METHOD = ‘AUTOMATIC’)
— You can get the next number from column NEXT_AUTO_SUPPLIER_NUM
— if you are following manual numbering (Alpha Numeric )
— p_vendor_rec.segment1 :=’865′; –(insert non duplicate number, in case if the supplier numbers are not fetched from a sequence, check your setups)

— We do have an automatic numbering for suppliers, hence the below block is used
— If your setups are not as explained above
— Comment from BEING until p_vendor_rec.segment1 := p_vendor_number;

BEGIN
Select NEXT_AUTO_SUPPLIER_NUM into p_vendor_number from AP_PRODUCT_SETUP
where SUPPLIER_NUMBERING_METHOD= ‘AUTOMATIC’;
EXCEPTION
WHEN NO_DATA_FOUND then
local_failed_at := ‘NUMBER’;
RAISE local_exception;
END;

p_vendor_rec.segment1 := p_vendor_number;

x_vendor_id := NULL;
x_party_id := NULL;
apps.ap_vendor_pub_pkg.create_vendor (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_rec,
x_vendor_id,
x_party_id);
DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);
DBMS_OUTPUT.put_line (‘Supplier Number = ‘ || p_vendor_number);
DBMS_OUTPUT.put_line (‘X_VENDOR_ID = ‘ || TO_CHAR (x_vendor_id));
DBMS_OUTPUT.put_line (‘X_PARTY_ID = ‘ || TO_CHAR (x_party_id));
DBMS_OUTPUT.put_line (”);

IF x_return_status <> ‘S’
THEN
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => ‘F’,
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
END LOOP;
END IF;

local_failed_at := ‘SUPPLIER’;
RAISE local_exception;
END IF;

–Create Site
l_vendor_site_rec.vendor_id := x_vendor_id; — 1117549;
l_vendor_site_rec.vendor_site_code := ‘Kuwait’;
l_vendor_site_rec.address_line1 := ‘Office Address line 1’;
l_vendor_site_rec.city := ‘Kuwait’;
l_vendor_site_rec.country := ‘KW’;
l_vendor_site_rec.org_id := 101;

l_vendor_site_rec.ext_payee_rec.default_pmt_method := ‘CHECK’;

— ————–
— Optional
— ————–
l_vendor_site_rec.purchasing_site_flag := ‘Y’;
l_vendor_site_rec.pay_site_flag := ‘Y’;
l_vendor_site_rec.rfq_only_site_flag := ‘N’;

pos_vendor_pub_pkg.create_vendor_site (
— ——————————
— Input data elements
— ——————————
p_vendor_site_rec => l_vendor_site_rec,
— ———————————
— Output data elements
— ———————————
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_vendor_site_id => ln_vendor_site_id,
x_party_site_id => ln_party_site_id,
x_location_id => ln_location_id);

IF (lc_return_status <> ‘S’)
THEN
IF ln_msg_count > 1
THEN
FOR i IN 1 .. ln_msg_count
LOOP
DBMS_OUTPUT.put_line (
SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;

local_failed_at := ‘SITE’;
RAISE local_exception;
ELSE
DBMS_OUTPUT.put_line (‘Vendor Site Id: ‘ || ln_vendor_site_id);
DBMS_OUTPUT.put_line (‘Party Site Id: ‘ || ln_party_site_id);
DBMS_OUTPUT.put_line (‘Location Id: ‘ || ln_location_id);
END IF;

–Create Contact

p_api_version := 1.0;
p_init_msg_list := ‘T’;
p_commit := ‘T’;
p_validation_level := FND_API.G_VALID_LEVEL_FULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;

— p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
— DBMS_OUTPUT.put_line (‘po_vendor_contacts_s.NEXTVAL = ‘ || po_vendor_contacts_s.NEXTVAL);

— P_VENDOR_CONTACT_REC.vendor_site_id := ln_vendor_site_id; –OPTIONAL If you want to attach the contact to a particular site
P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := ‘windows7bugs’;
P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := ‘blog’; — Mandatory
P_VENDOR_CONTACT_REC.PHONE := ‘22445566’;
P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := ‘admin@nocom.com.kw’;
P_VENDOR_CONTACT_REC.URL := ‘http://windows7bugs.wordpress.com’;
P_VENDOR_CONTACT_REC.org_id := 101; — Security Organization Id
p_vendor_contact_rec.party_site_id := ln_party_site_id;
— p_vendor_contact_rec.org_party_site_id := 2273595; –optional, system autofills the column with party_site_id used
p_vendor_contact_rec.VENDOR_ID := x_vendor_id;
p_vendor_contact_rec.prefix := ‘MR.’;
x_vendor_contact_id := NULL;
x_per_party_id := NULL;
x_rel_party_id := NULL;
x_rel_id := NULL;
x_org_contact_id := NULL;
x_party_site_id := NULL;
apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_contact_rec,
x_vendor_contact_id,
x_per_party_id,
x_rel_party_id,
x_rel_id,
x_org_contact_id,
x_party_site_id);

local_failed_at := ‘CONTACT’;
RAISE local_exception;
ELSE
DBMS_OUTPUT.put_line (‘X_RETURN_STATUS = ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘X_MSG_COUNT = ‘ || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (‘X_MSG_DATA = ‘ || x_msg_data);
DBMS_OUTPUT.put_line (
‘X_VENDOR_CONTACT_ID = ‘ || TO_CHAR (x_vendor_contact_id));
DBMS_OUTPUT.put_line (‘X_PER_PARTY_ID = ‘ || TO_CHAR (x_per_party_id));
DBMS_OUTPUT.put_line (‘X_REL_PARTY_ID = ‘ || TO_CHAR (x_rel_party_id));
DBMS_OUTPUT.put_line (‘X_REL_ID = ‘ || TO_CHAR (x_rel_id));
DBMS_OUTPUT.put_line (
‘X_ORG_CONTACT_ID = ‘ || TO_CHAR (x_org_contact_id));
DBMS_OUTPUT.put_line (
‘X_PARTY_SITE_ID = ‘ || TO_CHAR (x_party_site_id));
DBMS_OUTPUT.put_line (”);
END IF;

COMMIT;

EXCEPTION
WHEN local_exception
THEN
IF local_failed_at = ‘SUPPLIER’
THEN
DBMS_OUTPUT.put_line (‘API failed at Supplier Creation’);
ELSIF local_failed_at = ‘SITE’
THEN
DBMS_OUTPUT.put_line (‘API failed at Site Creation’);
ELSIF local_failed_at = ‘CONTACT’
THEN
DBMS_OUTPUT.put_line (‘API failed at Contact Creation’);
ELSIF local_failed_at = ‘NUMBER’
THEN
DBMS_OUTPUT.put_line (‘API failed at getting Supplier Number’);
END IF;

ROLLBACK;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK;
END;
[/code]

You can download the .sql file from here

Please post your comments, if you come across issues.

regards,

VirtualBox | SATA vs SCSI

On July 14, 2015 By windows7bugsIn Linux, Oracle Applications(R12), Oracle related, Oracle VirtualBox5 Comments

Recently I tried to build a cloned instance of our production instance over VirtualBox for some emergency issues faced by our inventory module. As this instance was supposed to be only accessed by me, I opted to use my desktop machine for the same. Throughout the last many years I built my own machines, choosing the best available hardware at the time of building them. My current desktop configuration is like following

i7 processor, 16GB memory, 2x1TB 7200 RPM HDD, 2x2TB 5200 RPM HDD, 1x500GB HDD for the OS (Windows 8.1 64Bit)

and throughout the years I built dozens of Virtual Machines using Oracle VirtualBox, mainly for testing un-certified Oracle & other products in a sand-boxed environment, against the crippled VMplayer, VirtualBox’s unrestricted interface supported almost everything I needed from a virtual environment.

So I built my R12 instance, that is around 600GB roughly in size with almost 4.5-5 years of business data, media etc. The following resources were dedicated for the fresh VM

4 processors
10GB memory
40GB fixed size SATA VDI for the Operating System (I used both OEL 5 & OEL 7 64bit)
1.2TB fixed size SATA VDI for the instance files
A dedicated D-Link 10/100MB NIC

Once the instance came online, I removed, cancelled all the scheduled concurrent programs, changed the database level parameters like job_queue_processes etc, however the lag experienced throughout the access attempts remained the same. Sometimes the HTML pages took 5-6 minutes to open, forms based modules took 8-10 minutes to open and timeouts were happening, frustrating me to the most possible levels

That is when I decided to give VMPlayer a try, I converted the existing VDI for the OS as vmdk and created a fresh 850 fixed size vmdk for the instance files and attached the same as SCSI to the VM. Did the complete clone process and to my utter surprise, the login page loaded within a minutes once after the instance was started!

This lead me to do various attempts with the fresh instance, I was able to shutdown the instance much faster, forms were opening faster, though LOVs having more than thousands of items were taking more time than anticipated

Once again, I created another fresh VM with VirtualBox and attached the disks created for VMplayer with it and repeated the tests. Well, I got the same performances from the new VM and somehow I came to a conclusion that, both VirtualBox and VMPlayer provide better I/O for SCSI interfaces compared to plain SATA emulators, ironically, the disks were created over SATA drives!

This difference you may not experience with VMs those are not hosting resource hungry applications like Oracle E-Business Suite. So, if you are attempting what I had described above and notice the differences, please update me with comments section.

regards,

rajesh

Oracle EBS R12 | Inventory | Receipts stuck in pending status

On June 30, 2015 By windows7bugsIn Oracle Applications(R12), Oracle Inventory (R12)Leave a comment

Sometimes the receipts against internal requests could get into trouble with the status pending. While the requester tries to receive the transaction, receipts get created with zero quantities and the internal sales order yet in open status and the quantities are seen in staging location. Please follow the below to delete such pending lines to receive the quantities once again to the targeted sub-inventory

As inventory super user

Transactions -> Receiving -> Transactions Status Summary

Under the tab “Supplier and Internal”

Select the “Destination” tab

Enter the name of the requester name (the name of the user who has created the internal material request to limit the lines fetched) & find the transactions

Delete the lines matching the transaction & save

Go back to receipts and enter the request number once again to receive the materials

regards,

for Windows7bugs

rajesh

Oracle EBS R12, Install/Clone on Oracle/Red Hat Linux 7.x

On June 30, 2015January 10, 2022 By windows7bugsIn Linux, Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle Enterprise LinuxLeave a comment

Hi guys

Last reviewed on: 10th January 2022

Made changes to the body for Oracle Linux 7.9

References: http://www.slideshare.net/tianpan/oracle-ebs-r1213installationlinux64bitpantian

(and dozens of EBS R12 related blogs and oracle community posts)

As far I could recollect, Oracle EBS R12.0.x & R12 12.1.x are not certified yet for Linux 7.x, let it be Red Hat Linux or Oracle enterprise Linux. Does the certification path really make guys like us trying to clone, install EBS R12 over yet to be certified platforms? Well, my case, I always did attempt & succeeded to a certain levels. Starting with Oracle 10g over Windows 7, 8 etc.

This time, my attempt was to clone our existing EBS R12 12.0.6 instance from Red Hat linux Enterprise 5 64Bit to Oracle Enterprise linux 7 64Bit.

I can’t say whether it is a rightful thing for you or for your environment. For me it was a fun project, that I achieved to a maximum satisfactory level.

Environment: Source System

Red Hat Linux 5.11 64Bit, 48GB physical memory, HBA interfaced external storage, single node EBS R12 12.0.6 with Oracle database 10.2.0.3 database

Environment: Target System

1. Oracle VirtualBox VM with 4 processors, 10GB memory, Oracle Enterprise Linux 7.9 with GUI on demand

VDI fixed size of 1.3T holding both application tier & database tier files copied from source system

2. A Desktop machine with i7 Processor, 4.5 TB Storage, 16GB physical RAM for real hardware performance testing

How to?

Definitely, this is not for a production instance, not for a production instance, not for a production instance & REMEMBER, even if you have valid Oracle support, Oracle will NOT support YOU as the platform is NOT certified for 12.0.x or 12.1.x

Oracle Enterprise Linux 7.x is not certified for EBS R12.0.x , hence I was unable to find the pre-requisites install for EBS R12 for the OS. The hacks started from this point onward

Update(10th January 2022): Oracle Linux 7.9 has the “Add-on” repository that allows you to install the pre-requisites package, however meant for later certified versions of Oracle EBS. Hence, stick to Oracle Linux 6 public yum repository based package installation as mentioned below.

(Please make sure that you fully updated your OEL 7.x box prior the attempts listed below)

As root switch the directory to

cd /etc/yum.repos.d

Go to

http://public-yum.oracle.com/ and install the ol6 repo following instructions provided. The instruction might get in to folds, just follow the below.

cd /etc/yum.repos.d/
wget http://yum.oracle.com/public-yum-ol6.repo

Please note public yum repositories will be soon removed, if you are reading this post much later, make sure that you will find relevant sources to install the pre-requisites.

Open the ol6 repo file using gedit/vim and enable the add-on repo. Make sure the OEL7 repo doesn’t have the add-ons enabled. If you miss this, you might end up rebuilding the Linux Server (That’s what I did!)

install pre-requisites for EBS R12 by issuing the following command

yum install oracle-ebs-server-R12-preinstall -y

Once the installation completed, you will notice that you have two new users created

oracle

applmgr

If your cloned instance was having different user names for both application and database, create same usernames and add them to relevant groups

for example applprod to oinstall group and oraprod to dba, oinstall groups (mandatory)

Special note: If you are attempting these hacks for Production instances, sorry buddy, YOU will never be supported by Oracle as they have recently confirmed to me through a private message that they don’t have any plans to certify 12.0.x versions on OEL 6 or 7

Prior you attempt to clone or install the 12.0.6 instance on OEL 6, 7 make sure that you have taken care of few things prior the attempt. There are few OS level files formatting mandatory for Oracle applications, those caused me almost 3 weeks of reading various blog posts, and NOT a single solution to address a weird error associated with “Error: Missing ormi[s]://<host>:<port>” error while shutting down the application services using adstpall.sh script file! And the reason was default formatting of /etc/hosts file by OEL 7 installation!

Few errors you should see when the /etc/hosts file is NOT formatted as per Oracle’s installation notes…

Executing service control script:
 …../admin/scripts/adoacorectl.sh stop
 Timeout specified in context file: 100 second(s) 

script returned:

  ERROR : Timed out( 100000 ): Interrupted Exception
  You are running adoacorectl.sh version 120.13

So the /etc/hosts file must be formatted like below with your OEL/RHEL 6.x & 7.x installations

127.0.0.1 localhost.localdomain localhost 
::1 localhost6.localdomain6 localhost6 
192.168.10.15 erp-prodbak.xyz.com erp-prodbak #Your box 
192.168.10.16 erp-prod.xyz.com erp-prod #Your production server, if at all required

Rest of the network related mandatory elements specified in the reference documents could be ignored, or you may format them as well. If you are going to touch your /etc/resolv.conf file at all, make sure that after changing the content of the resolv.conf, you make it read-only by issuing the following command

chattr +i resolv.conf

else after a reboot, this file be rewritten by the networking admin services

Now let us refer to the /etc/sysctl.conf & /etc/security/limits.conf files, which are setting system level parameters for the application. While I noticed that the previously configured parameters were causing performance degradation once after the clone, I just copied these elements from a 12.1.3 vision instance and replaced to find that, my cloned instance started flying, giving me much better performance than our Production instance that has the latest hardware and 48GB physical memory!

Once again, thoroughly check the below parameters, if something looks not appropriate for your environment, refrain from updating

as a precaution, do a backup for both the above mentioned files

cp /etc/sysctl.conf /etc/sysctl.conf.original 
cp /etc/security/limits.conf /etc/security/limits.conf.original

Now using your favorite editor, replace the content of you sysctl.conf file with following

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.
#
# Use '/sbin/sysctl -a' to list all possible parameters.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

# oracle-ebs-server-R12-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744

# oracle-ebs-server-R12-preinstall setting for kernel.sem is '256 32000 100 142'
kernel.sem = 256 32000 100 142

# oracle-ebs-server-R12-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096

# oracle-ebs-server-R12-preinstall setting for kernel.shmall is 1073741824 on x86_64
# oracle-ebs-server-R12-preinstall setting for kernel.shmall is 2097152 on i386

# oracle-ebs-server-R12-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
# oracle-ebs-server-R12-preinstall setting for kernel.shmmax is 4294967295 on i386
kernel.shmmax = 4398046511104

# oracle-ebs-server-R12-preinstall setting for kernel.msgmni is 2878
kernel.msgmni = 2878

# oracle-ebs-server-R12-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144

# oracle-ebs-server-R12-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304

# oracle-ebs-server-R12-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144

# oracle-ebs-server-R12-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576

# oracle-ebs-server-R12-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576

# oracle-ebs-server-R12-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

Save the file and issue the following command

sysctl -p

Now, change the limits.conf file with following content

# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#            
#
#Where:
# can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
# can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
# can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#                 
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

# * hard nofile 65535
# * soft nofile 4096
# * hard nproc 16384
# * soft nproc 2047


# oracle-ebs-server-R12-preinstall setting for nofile soft limit is 4096
oratest   soft   nofile    4096
appltest   soft   nofile    4096

# oracle-ebs-server-R12-preinstall setting for nofile hard limit is 65536
oratest   hard   nofile    65536
appltest   hard   nofile    65536

# oracle-ebs-server-R12-preinstall setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oratest   soft   nproc    16384
appltest   soft   nproc    16384

# oracle-ebs-server-R12-preinstall setting for nproc hard limit is 16384
oratest   hard   nproc    16384
appltest   hard   nproc    16384

# oracle-ebs-server-R12-preinstall setting for stack soft limit is 10240KB
oratest   soft   stack    10240
appltest   soft   stack    10240

# oracle-ebs-server-R12-preinstall setting for stack hard limit is 32768KB
oratest   hard   stack    32768
appltest   hard   stack    32768


# End of file

Install unzip version 5 from https://oss.oracle.com/el4/unzip/unzip.html

(This is mandatory as 12.0.x doesn’t support unzip versions above 5, and your cloning will fail with error notifying unzip is not supported)

Source the libdb.so.2 library file to /usr/lib from your source system, as the libgdbm.so.4.0.0 soft link hack will not work. If the libdb.so.2 is already a soft link in your source system, move the source file “libgdbm.so.2.0.0” from source to target box and create the soft link.

Now go ahead and enjoy the cloning/installation of your R12 12.0.x, or R12.1.x releases over OEL/RHEL 7.x (CENTOS is GNU release of RHEL, so you may experiment with that distro as well, nothing guaranteed though)

Please be warned, I have read many places that, few modules do not work on Linux 6,7 releases. Hence, the entire exercises you painfully executed may turn futile at later stages, and once again, if you have a live Oracle support, do not attempt such with your Production instance!

Enjoy guys :)

for Windows7bugs

rajesh

Oracle Applications R12 | Disable Purchase Request Attachments

On December 27, 2014 By windows7bugsIn Oracle Applications(R12)Leave a comment

Hello guys

We have an instance, with the database which has grown to almost 1TB in size within last four years of time & the main culprit were the attachments end users uploaded with the purchase requests. Now, the approach to upload documents to database was a wrong wrong wrong choice, however, with what we lived for last many years.

Recently the we have decided to stop uploading the attachments to database, instead start using certain other methods. That’s how we started to find a “method” to disable the attachment functionality. There were not many leads, however we stumbled upon the following thread

http://www.strsoftware.com/blog/disabling-attachment-menu-button-with-forms-personalization

As all we needed were just some kind of leads, browsing through the “Application Developer=> Attachments=>Attachment Functions”

We were able to locate the Name “PO_POXRQERQ”, that refers to Requisitions. All we were supposed to do was the remove all the lines found under “Blocks”, that disables the attachment button available with purchase requisition form! Read more about it from the link provided above.

Hope this information comes handy for few others out there!

Merry X-Mas and very happy new year for everyone!

regards,

Oracle 10g | Get Windows User Details from Active Directory

On December 9, 2014December 18, 2014 By windows7bugsIn Active Directory, Oracle relatedLeave a comment

Hello guys! 9th December 2014 : Minor update The following exercise will not materialize if you have a middleware sever, as the client information will not be available for your forms. Missed us? :) Recently we were challenged with a very interesting task, and that was to send emails to the requester of a particular activity once after the completion. Usually, we can look into the HR/Payroll enrollments and get the email address for the user without much troubles. Our case was different, our HR/Payroll records are not always up to date and we were forced to capture the Windows login name and get the email addresses straight away from the AD database! As usual, after hours of googling and trying out few scripts, We came up with a tailored script that fetches us the full Name and email address against value derived from

[code language=”sql”]
Select sys_context(‘userenv’,’os_user’) from dual;
[/code]

query Here, we are sharing the scripts with you Complete credits go to the following few & many others http://www.dba-oracle.com/t_packages_dbms_ldap.htm http://oracle-base.com/articles/9i/ldap-from-plsql-9i.php (Our script is a complete rip-off from oracle-base.com article)

[sourcecode language=”sql” gutter=”false” wraplines=”true”]
/* Formatted on 12/9/2014 2:56:10 PM (QP5 v5.163.1008.3004) */
SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
   /*This block commented never worked for us
      — Adjust as necessary.
      —   l_ldap_host    VARCHAR2(256) := ‘server01.tshcomputing.com’;
      — l_ldap_port    VARCHAR2(256) := ‘389’;
      — l_ldap_user    VARCHAR2(256) := ‘cn=orcladmin’;
      — l_ldap_passwd VARCHAR2(256) := ‘password’;
      — l_ldap_base    VARCHAR2(256) := ‘cn=Users,dc=tshcomputing,dc=com’;
   */

   –Active Directory Component Windows 2008 R2

   l_ldap_host     VARCHAR2 (256)
                      := ‘fully qualitifed name of your domain controller’; –eg (abc-efg@xyz.com)
   l_ldap_port     VARCHAR2 (256) := ‘389’;
   l_ldap_user     VARCHAR2 (256) := ‘fully qualified user name’; –eg: paul@xyz.com (make sure paul is a member of Administrators group)
   l_ldap_passwd   VARCHAR2 (256) := ‘password’; — clear text password for user paul@kazema.com
   l_ldap_base     VARCHAR2 (256) := ‘DC=xyz,DC=com’;
   — l_ldap_base     VARCHAR2 (256) := ‘OU=GEN Managers,DC=xyz,DC=com’; –Restrict the query to a particular OU within AD
   –AD 2008 R2

   l_retval        PLS_INTEGER;

   l_session       DBMS_LDAP.session;
   l_attrs         DBMS_LDAP.string_collection;
   l_message       DBMS_LDAP.MESSAGE;
   l_entry         DBMS_LDAP.MESSAGE;
   l_attr_name     VARCHAR2 (256);
   l_ber_element   DBMS_LDAP.ber_element;
   l_vals          DBMS_LDAP.string_collection;
BEGIN
   — Choose to raise exceptions.
   DBMS_LDAP.USE_EXCEPTION := TRUE;

   — Connect to the LDAP server.
   l_session := DBMS_LDAP.init (hostname => l_ldap_host, portnum => l_ldap_port);

   l_retval :=
      DBMS_LDAP.simple_bind_s (ld       => l_session,
                               dn       => l_ldap_user,
                               passwd   => l_ldap_passwd);

   — l_attrs(1) := ‘*’; — Get all attributes(Complete AD details will be read, use carefully)

   l_attrs (1) := ‘mail’;
   l_attrs (2) := ‘displayName’;

   –Common Attributes you can pass to the AD query
   — l_attrs(1) := ‘sAMAccountName’;
   —   l_attrs(2) := ’employeeNumber’;
   —    l_attrs(3) := ‘displayName’;
   —    l_attrs(4) := ‘description’;
   —    l_attrs(5) := ‘telephoneNumber’;
   —    l_attrs(6) := ‘facsimileTelephoneNumber’;
   —   l_attrs(7) := ‘department’;
   —   l_attrs(8) := ‘company’;
   —    l_attrs(9) := ’employeeID’;
   —   l_attrs(10) := ‘streetAddress’;
   —   l_attrs(11) := ‘mail’;
   —   l_attrs(12) := ‘c’;
   — l_attrs(13) := ‘l’;
   —   l_attrs(14) := ‘postalCode’;

   — retrieve all attributes
   l_retval :=
      DBMS_LDAP.search_s (ld         => l_session,
                          base       => l_ldap_base,
                          scope      => DBMS_LDAP.SCOPE_SUBTREE,
                          —          filter   => ‘objectclass=*’, –All the objects will be read
                          filter     => ‘sAMAccountName=paul’, –Record for User with windows login account as "Paul" will be fetched
                          attrs      => l_attrs,
                          attronly   => 0,
                          res        => l_message);

   IF DBMS_LDAP.count_entries (ld => l_session, msg => l_message) > 0
   THEN
      — Get all the entries returned by our search.
      l_entry := DBMS_LDAP.first_entry (ld => l_session, msg => l_message);

     <<entry_loop>>
      WHILE l_entry IS NOT NULL
      LOOP
         — Get all the attributes for this entry.
         DBMS_OUTPUT.PUT_LINE (‘—————————————‘);
         l_attr_name :=
            DBMS_LDAP.first_attribute (ld          => l_session,
                                       ldapentry   => l_entry,
                                       ber_elem    => l_ber_element);

        <<attributes_loop>>
         WHILE l_attr_name IS NOT NULL
         LOOP
            — Get all the values for this attribute.
            l_vals :=
               DBMS_LDAP.get_values (ld          => l_session,
                                     ldapentry   => l_entry,
                                     attr        => l_attr_name);

           <<values_loop>>
            FOR i IN l_vals.FIRST .. l_vals.LAST
            LOOP
               DBMS_OUTPUT.PUT_LINE (
                     ‘ATTIBUTE_NAME: ‘
                  || l_attr_name
                  || ‘ = ‘
                  || SUBSTR (l_vals (i), 1, 200));
            END LOOP values_loop;

            l_attr_name :=
               DBMS_LDAP.next_attribute (ld          => l_session,
                                         ldapentry   => l_entry,
                                         ber_elem    => l_ber_element);
         END LOOP attibutes_loop;

         l_entry := DBMS_LDAP.next_entry (ld => l_session, msg => l_entry);
      END LOOP entry_loop;
   END IF;

   — Disconnect from the LDAP server.
   l_retval := DBMS_LDAP.unbind_s (ld => l_session);
   DBMS_OUTPUT.PUT_LINE (‘L_RETVAL: ‘ || l_retval);
END;
/
[/sourcecode]

Try it & let us know the experiences! regards,

Oracle Payroll | R12 | Simple view for employee paid salaries

On June 19, 2014March 17, 2023 By windows7bugsIn Linux, Oracle Applications(R12), UncategorizedLeave a comment

Recently I were requested to build a report by the HR/Payroll team, running which they can generate the salary paid details for employees. Ie, a tabular listing with paid month, and total salary earned, grouped by year factor

I found the request being one of the toughest, as my exposure to Payroll module and base tables was limited almost none, other than knowing the person and assignment tables and views!

Gradually I started going through the custom reports developed by our implementer and restructured few of their custom functions into a best possible view what meets our current requirements. As we are not using customized packages for the salary calculations, you should able to alter the below SQL and create your own with almost no efforts. We hope you will enjoy the solution!

Script for view

CREATE OR REPLACE VIEW XXEMPLOYEE_SALARIES_MONTHLY
AS
SELECT pap.person_id, pap.employee_number,to_char(ppa.date_earned,'Mon-YYYY') earned_month,
TO_NUMBER(to_char(ppa.date_earned,'MM')) MONTH_NUMBER,
TO_NUMBER(to_char(ppa.date_earned,'YYYY')) YEAR_FACTOR,
sum(to_number(prrv.result_value)) PAID_AMOUNT
FROM PAY_ELEMENT_TYPES_F petf
,PAY_INPUT_VALUES_F pivf
,PAY_PAYROLL_ACTIONS ppa
,PAY_ASSIGNMENT_ACTIONS paa
,PAY_RUN_RESULTS prr
,PAY_RUN_RESULT_VALUES prrv
,PER_ALL_ASSIGNMENTS_F paaf
,PER_ALL_PEOPLE_F pap
,PAY_ELEMENT_CLASSIFICATIONS pec
WHERE 1=1
AND pec.classification_id = petf.classification_id
and prrv.input_value_id = pivf.input_value_id
AND CLASSIFICATION_NAME IN ('Earnings','Supplemental Earnings')–Add in more based on your setup
and pivf.name in ('Pay Value')
AND petf.element_type_id = prr.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND petf.business_group_id = 81
AND ppa.business_group_id = pap.business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND SYSDATE BETWEEN TRUNC(petf.effective_start_date) AND TRUNC(petf.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(pap.effective_start_date) AND TRUNC(pap.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = pap.person_id
–and prrv.result_value > '0'
AND paaf.business_group_id = pap.business_group_id
AND pap.business_group_id = 81–double check
GROUP BY pap.person_id,pap.employee_number, to_char(ppa.date_earned,'Mon-YYYY'),to_char(ppa.date_earned,'MM'),to_char(ppa.date_earned,'YYYY')
UNION ALL
SELECT pap.person_id, pap.employee_number,to_char(ppa.date_earned,'Mon-YYYY') earned_month,
TO_NUMBER(to_char(ppa.date_earned,'MM')) MONTH_NUMBER,
TO_NUMBER(to_char(ppa.date_earned,'YYYY')) YEAR_FACTOR,
nvl(sum(to_number(prrv.result_value)),0)*-1 PAID_AMOUNT
FROM PAY_ELEMENT_TYPES_F petf
,PAY_INPUT_VALUES_F pivf
,PAY_PAYROLL_ACTIONS ppa
,PAY_ASSIGNMENT_ACTIONS paa
,PAY_RUN_RESULTS prr
,PAY_RUN_RESULT_VALUES prrv
,PER_ALL_ASSIGNMENTS_F paaf
,PER_ALL_PEOPLE_F pap
,PAY_ELEMENT_CLASSIFICATIONS pec
WHERE 1=1
AND pec.classification_id = petf.classification_id
and prrv.input_value_id = pivf.input_value_id
AND CLASSIFICATION_NAME IN ('Voluntary Deductions','Involuntary Deductions','Social Insurance')–Add in more based on your setup
and pivf.name in ('Pay Value')
AND petf.element_type_id = prr.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND ppa.business_group_id = pap.business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND SYSDATE BETWEEN TRUNC(petf.effective_start_date) AND TRUNC(petf.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(pap.effective_start_date) AND TRUNC(pap.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = pap.person_id
— and prrv.result_value > '0.00'
AND paaf.business_group_id = pap.business_group_id
AND pap.business_group_id = 81–double check
GROUP BY pap.person_id,pap.employee_number, to_char(ppa.date_earned,'Mon-YYYY'),to_char(ppa.date_earned,'MM'),to_char(ppa.date_earned,'YYYY')
order by 2,5,4;

Sample Query

SELECT PERSON_ID, EMPLOYEE_NUMBER,earned_month,year_factor,
SUM(PAID_AMOUNT) PAID_SALARY
FROM XXEMPLOYEE_SALARIES_MONTHLY
WHERE
1=1
AND EMPLOYEE_NUMBER =:P_EMPLOYEE_NUMBER
AND YEAR_FACTOR BETWEEN NVL(:P_START_YEAR,YEAR_FACTOR) AND NVL(:P_END_YEAR,YEAR_FACTOR)
GROUP BY PERSON_ID,EMPLOYEE_NUMBER,earned_month,YEAR_FACTOR, MONTH_NUMBER
ORDER BY YEAR_FACTOR, MONTH_NUMBER

Enjoy another quality post from us guys :)

for Windows7bugs

rajesh

Oracle Applications | Making references to fnd_message package from custom PL/SQL library

On May 22, 2014 By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle related1 Comment

Until recent times I didn’t have a single idea that, fnd_message package was NOT a database, instead PL/SQL library object, through which forms based modules were calling various elements like

fnd_message.set_string

fnd_message.show

One of the main reasons for believing the same was as soon as I type the name of the package, both Toad and SQL Developer identify it as already existing object!

However, few days back I decided to shed my laziness and to revamp the core retailing application I have developed two years back, using Oracle’s public APIs for Order Management & Inventory modules.

Hence, I started designing my own PL/SQL libraries and bit the bullet while trying to make a reference to fnd_message package. The call was returning errors (please refer the below image)

Which forced me to start googling for relevant and dependable pointers towards this particular situation and came across a forum thread where one person who I respect as a “geek” tried to explain why there is a package in the database and within an PL/SQL library with the same “fnd_message”, and gradually giving it up saying “Only oracle could explain why”

Hence I opened up the Template form and try to see which attached PL/SQL library has the listing for fnd_message component calls

and the PL/SQL library “FNDSQL” attached to the TEMPLATE.fmb by default has all the components referenced with the forms modules. Once identified, I attached the same library with my custom library

and the next compilation attempt was successful!

I do hope, this information is useful to few others out there, who are trying to call the fnd_message package references with their own custom PL/SQL libraries.

regards,

rajesh

Install Oracle Database 11g R2 on Windows 8.x, Windows 10

On April 25, 2014October 9, 2015 By windows7bugsIn Oracle related, Windows 10, Windows 84 Comments

Update: 9th October 2015

The installation procedure mentioned below are applicable for Windows 10 also. So enjoy! I just finished installing the 11G R2 64Bit database in my computer running Windows 10 64Bit professional.

I believe if someone is attempting to install Oracle database in a laptop or desktop machine, it is purely for limited development requirements or learning.

A many who are new to Oracle database may not able to install the software on many versions of Microsoft client OS like Windows 7 or 8 until certain levels of hacks are made.

Ignore what “I know everything” geeks screaming across tech forums, asking you questions like “DID YOU READ THE CERTIFICATION MATRIX”

You don’t have the time, neither do I.

Let us install the Oracle database 11g R2 this time on a Windows 8.1 (Update 1) dell laptop with following configuration 8GB memory, 1TB Hdd, Windows 8.1 update 1 64Bit Other oracle software installed

Developer Suite 6i
Developer Suite 10g

Download the installation files from Oracle repositories

There are two files to download, a total of 2GB
Extract the zip files and move the files inside a single folder

Once extracted, you have something like below

Now Switch to the second folder, which reads as “win64_11gR2_database_2of2”
Open up the “components” folder (D:\Oracle11gR2_64Bit\win64_11gR2_database_2of2\database\stage\Components)
There will be four sub-folders within “Components” folder, copy them to D:\Oracle11gR2_64Bit\win64_11gR2_database_1of2\database\stage\Components folder

That’s all. Just start the installation process. Make sure you have changed the virtual memory settings of your box prior the installation (If you already have developer suite 10g, you already changed it) Start the installation by executing “setup.exe” You will get one warning, about not meeting the recommended environment requirements (/me chuckles). We didn’t check the 32Bit version of Oracle Database 11g R2, however the method should be same. Hope you enjoyed another post from us

for Windows7bugs

rajesh

	spg on How to disable Windows Virtual…
	windows7bugs on Oracle 19c \| Install sample sc…
	bluebirdresilientf36… on Oracle 19c \| Install sample sc…
	windows7bugs on How to disable Windows Virtual…
	mbokani on How to disable Windows Virtual…