Javascript - find object position

Determining DOM object position:
It is one of the AJAX actions when you are trying to attach drop down selection to the existing form field.

Script below helps to identify these coordinates.

obj - is DOM object ( for example some <input type=text name=selectme id=selectme>...</input>

We assume that field height is 20 pixels. Function returns array of X,Y representing left lower corner position of the input field.


//---------------------------------------------------------------------------
function findPos(obj) {
var curleft = curtop = 0;
if (obj.offsetParent) {
curleft = obj.offsetLeft
curtop = obj.offsetTop+20;

while (obj = obj.offsetParent) {
curleft += obj.offsetLeft;
// curleft += 1;
curtop += obj.offsetTop;
}
}
return [curleft,curtop];
}

//-----------------------------------------------------

Example: Dynamically build list of US States.

//-------------------------------------------
// This function is called when user keys in state name or abbreviation
// External php script named getStates.php contains query and
// dynamic generation of the drop down selection. See below

function ajaxStates(obj) {

var state;
var s=obj.value;
var url="getStates.php?keys="+encodeURI(s)+"&addr="+encodeURI(obj.name);
executeXMLHttpRequestStates(url,obj);
}
//-------------------------END ---------------------------------


//-----------getStates.php--------------------------------------

session_start();
$sess=session_name().'='.session_id();

require('config.php'); // contains dbConnection
require(SOME_PATH.'/vendors.php');


$aKeys=$_GET['keys'];
$Elem=$_GET['addr'];
$QBEtrans = new vendors();

$records=$QBEtrans->getStates($aKeys)->getRecords();
$cr=count($records)+1;

echo ' ';
echo ' Choose State from the list ';
foreach($records as &$record) {

echo ''. $record['name'].'-'.$record['nick'].' ';

}
echo '';
?>


//-- Query from vendors.php --

#----------------------------------------------------------
protected function pGetStates($key) {

// Get DB Connection from the factory
$dbh = $this->getDBConnection();

$key=$key.'%';

$sql ="select nick,name from states where name like upper(:key) order by name";
$psBindVars = array();
$psBindVars[':key'] =strtoupper($key);


// Prepare starement
$stmt = $dbh->prepare($sql);

$stmt->execute($psBindVars);

// fetch the record
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stmt = null;
$dbh = null;

include_once CLASSPATH.'/ReadOnlyResultSet.php';
return (new ReadOnlyResultSet($records));
}
}

//----------------------------

//----- ReadOnlyResultSet.php -----


class ReadOnlyResultSet {
// This member variable will hold the array of the returned records
private $records;

// transform the PDO::PDOStatement to the array, and assign it
// to the local variable
function __construct($records)
{
if (count($records)>0) {
$this->records = $records;
reset($this->records);
}
}

function getRecords(){
return $this->records;
}

// Receives an instance of the DataObject we're working on
// returns the next element from array
function getNext($dataobject) {
$row = current($this->records);

// Use reflection to fetch the DO's field names
$class = new ReflectionObject($dataobject);
$properties = $class->getProperties();

// Loop through the properties to set them from the current row
for ($i = 0; $i < count($properties); $i++) {
$prop_name = $properties[$i]->getName();
$dataobject->$prop_name = $row[$prop_name];
}
next($this->records);

return $dataobject;
}

// Move the pointer back to the beginning of the result set
function reset() {
$this->records->reset();
}

// Return the number of rows
function rowCount() {
return count($this->records);
}

public function sort($key)
{
for ($i = 0; $i < sizeof($this->records); $i++) {
$sort_values[$i] = $this->records[$i][$key];
}
asort ($sort_values);
reset ($sort_values);
while (list ($arr_key, $arr_val) = each ($sort_values)) {
$sorted_arr[] = $this->records[$arr_key];
}
$this->records = $sorted_arr;
reset($this->records);
}
}
?>

//---------------------------------



I hope this is enough for you to get work started.

UNIX Shell substrings. Cut last substring.

To extract file name from the absolute file path:

Assuming that DIR is something like /u01/oracle/apps/appl/bin/myFile.sh

Then
basename $DIR - gives you the file name. It returns myFile.sh

If you need to return path without the file name :echo $DIR sed 's/`basename $DIR`//'

If you need to return the directory name prior to the file name Then:

echo $DIR | awk -F/ '{print $(NF-1)}'

NF - is built-in AWK variable indicating the total number of fields.
NF-1 - is the field preceeding the last field.
-F - separator indicator - in this case it is /
-f source-file - awk option indicating that source file is found in source-file.


Another built-in variables"

FILENAME - file name which awk is currently reading
FNR - is current record number awk is processing in the current file
NR - is the number of records awk processed so far

Oracle APPS FNDLOAD batch solution for UPLOAD

Check video of alternative GUI ( finally arrived) for FNDLOAD.

With all the examples I've seen on Oracle FNDLOAD, I never ran across some sort of batch solution. I built one and tested on the few objects and it worked just fine. I wasn't able to test all of the objects UPLOAD.

Below is the source and sequence of the actions.

Step 1. Build your list of the objects needed to be migrated.
Follow the file layout below. Keep in mind that there are always 3 fields. (Put 'X' if there is no application associated with the AOL object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces or commas..

# Example of the file layout

CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND

Step 2. Save this file - for example as loadList on the server.

Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_U.sh - this one for UPLOAD


#!/bin/ksh

# . batchFNDLOAD_U loadList appsPassword

output_dir='apps_objects_download_dir'
login=apps/$2

action='UPLOAD'

while read line
do

type=`echo $line cut -f1 -d,`

type=`echo $type tr [a-z] [A-Z]`
object=`echo $line cut -f2 -d,`
app=`echo $line cut -f3 -d,`



case $1 in
HELP)
echo ' Use the following format: MyFNDLOAD [helpRSCP] [D,U] output_file application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookups';
echo ' Example - MyFNDLOAD_U MN D save_as_file_name short_application_name apps_object_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO D save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO U save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U MN U MYMENU FND MENU_SHORT - to upload menu';
;;
RSET)
# FNDLOAD Upload Request Set Syntax
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object.ldt
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt
;;
CP)
# FNDLOAD UploadConcurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt
;;
PO)
# FNDLOAD Upload Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt
;;
VS)
# FNDLOAD Upload Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
VSV)
# FNDLOAD Upload Value Set With Values Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;

RSP)
# FNDLOAD Upload Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
MN)
# FNDLOAD Upload Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt
;;
RG)
# FNDLOAD Upload Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt
;;
LKP)
# FNDLOAD Upload Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt
;;
DFF)
# FNDLOAD Upload Descriptive Flex Field Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
MSG)
# FNDLOAD Upload FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt
;;
USER)
# FNDLOAD Upload APPS User Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
*) ;;
esac


# keep the line below if you wish to overwrite the owner of the object to ANONYMOUS.
# This is not really what Oracle recommends, but just in case you need to know if #this has been done via batched FNDLOAD. Choose appropriate existing USER name.

cat $output_dir/$object.ldt sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
mv xx $output_dir/$object.ldt

done < $1 # the end Step 4. Execute the script : batchFNDLOAD_U loadList appsPassword.


All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that all just created .ldt files correctly downloaded the designated objects.
Every file, which ends with # -- End Entity Definitions --
failed downloading the object definition. Most likely due to incorrect object name provided in the loadList file.

Find Request Group, Responsibility based on concurrent program name,executable name.

Sometimes I ran into task to identify Oracle APPS responsibility or request group to which certain concurrent program is attached. Going through Oracle APPS forms is not really a straight path.

Below are few SQL scripts helping to identify that.

Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))
Find all responsibilities and concurrent program names based on the executable Name


select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id



Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<
Find concurrent Program Names based on Responsibility Key Value

select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))

The Idiot test.

I grabbed this "The Idiot Test" from Bex Huff personal page.

Considering that I'm color blind, it was some sort of challenge. On top of everything it helps to understand the beauty of Oracle FNDLOAD automation and packaging and keep your mind fresh.

Don't get addicted.

Batch Oracle FNDLOAD solution for DOWNLOAD

With all the examples I've seen on Oracle FNDLOAD, I never ran across some sort of packaged solution. I built one and tested on the few objects and it worked just fine.
As an alternative Check the video tour for Migration LogiK(TM) FNDLOAD GUI

Below is the source and sequence of the actions.

Step 1. Build your list of the objects to needed to be migrated.
Follow the layout. Keep in mind that there are always 3 fields. (Put 'X' if there no application associated with the object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces, commas #etc...


CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND

Step 2. Save this file - for example as loadList on the server.

Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_D.sh - this one for DOWNLOAD


#!/bin/ksh


# . batchFNDLOAD_D loadList appsPassword

output_dir='apps_objects_download_dir'
login=apps/$2

action='DOWNLOAD'


while read line
do

type=`echo $line | cut -f1 -d,`

type=`echo $type | tr [a-z] [A-Z]`
object=`echo $line | cut -f2 -d,`
app=`echo $line | cut -f3 -d,`


case $type in
HELP)
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookup TYPEs';
echo ' RSP - responsibility';
echo ' DFF - descriptive flexfield';

;;
RSET)
# FNDLOAD Download Request Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $output_dir/$object.ldt REQ_SET REQUEST_SET_NAME=$object
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt REQ_SET_LINKS REQUEST_SET_NAME=$object
;;
CP)
# FNDLOAD Download Concurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt PROGRAM APPLICATION_SHORT_NAME=$app CONCURRENT_PROGRAM_NAME=$object
;;
PO)
# FNDLOAD Download Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt PROFILE PROFILE_NAME=$object APPLICATION_SHORT_NAME=$app
;;
VS)
# FNDLOAD Download Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET FLEX_VALUE_SET_NAME=$object
;;
VSV)
# FNDLOAD Download Value With Values Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=$object
;;
RSP)
# FNDLOAD Download Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_RESPONSIBILITY RESP_KEY=$object
;;
MN)
# FNDLOAD Download Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt MENU MENU_NAME=$object
;;
RG)
# FNDLOAD Download Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt REQUEST_GROUP REQUEST_GROUP_NAME=$object APPLICATION_SHORT_NAME=$app
;;
LKP)
# FNDLOAD Download Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$app LOOKUP_TYPE=$object
;;
DFF)
# FNDLOAD Download Descriptive Flex Fields Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME=$object
;;
MSG)
# FNDLOAD Download FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$app MESSAGE_NAME=$object
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_USER USER_NAME=$object
;;
*) ;;
esac


done < $1
# the end


Step 4. Execute the script : batchFNDLOAD_D loadList appsPassword.


All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that created .ldt files correctly downloaded the designated objects.
Each file, which ends with
# -- End Entity Definitions --
didn't download the object definition. Most likely due to incorrect object name provided in the FNDLOAD command.

Oracle API to populate Lookup Table from CSV fle.

Oracle API to populate FND Lookup Table from CSV file.

This task can be useful if your business users have a habit to keep their business rules data in MS Excel.

Here all will come together - reading file, split string and calling Oracle APIs.


To be Continued..

Additional function to process MS Excel CSV record.

MS Excel record could contain additional delimiters such as " and have commas inside
the individual cells. MergeStrings function offers additional functionality allowing to process these or any other additional delimiters.
This can be useful when you need more control over the imported data or invoke Oracle APIs to upload these records into Oracle APPS tables.

Below is the example.

/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare

myString Varchar2(20000):='AX,AXX,AXXX,Anne,3333,Merchant,xxxx@yahoo.com,Manager I,18335,DOOM 300,300,"First, Middle Last (Nick)",2434,46678 BEAUMONT FOUNTAIN PLAZA,3050 DOWJON RD,,,Beaumont,TX,77706-7214,TX,"Goor, Stephen J","Duke (Lion)",Full Time >=35 hrs,Exempt,185431,,No,,,'; -- just an example

Type v_type is Table of Varchar2(3000) index by Binary_Integer;

v v_type;

---------------------------------------------------------

Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is


i number:=1;
i_Temp Number :=1;
k Number:=0;


v_Temp V_type;

Begin



while(i_Temp <= v.LAST) Loop


if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else

if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;


Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;

ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;


k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);

if k=0 Then

while ( k=0) Loop

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;

k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);


End Loop;


End If;

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);

v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);

i:=i+1;
i_temp:=i_temp+1;

End If;

End If;

v_Temp(i):=ltrim(v_Temp(i),'"');
v_Temp(i):=rtrim(v_Temp(i),'"');

End Loop;

return v_Temp;

Exception
When NO_DATA_FOUND Then

dbms_output.put_line('String '||v(1) ||' cannot be pro');

return v_temp;

End;


------
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',',
p_enclosed Varchar2 DEfault '"')
Return v_type


is

v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
i_ds Number:=length(p_delimiter);
i_Length Number;
i_countEnclosed Number:=1;

v_debug Number:=0;

i_Exit Number:=0;

Begin

t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;

v_debug :=1;

i_s :=1;
tempString:=substr(t_items,1);
i:=1;

while i_ep > 0
Loop

i_ep:=instr(tempString,p_delimiter,1);

v_debug :=2;

if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,1,i_ep-i_ds);


end if;
i:=i+1;
i_s:=1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;

v_debug :=3;


End Loop;


v:=v_varTab_i;

v_varTab_i:=mergeStrings(v,'"',p_delimiter);


Return v_varTab_i;

Exception
when others then
dbms_output.put_line('GetString '||substr(SQLERRM,1,200));
return v_varTab_i;


End GetStrings;
-------------------------------------------------------------------------------------
Begin

v:=GetStrings(myString,',');

For j in 1..v.LAST Loop

dbms_output.put_line('Element='||v(j));

End Loop;

End;