How to query key flexfields

It is quite common that in Oracle Applications you need to write queries listing specific account combinations. The problem with that is that the account combinations are stored as their ids and you have to go to the GL_CODE_COMBINATIONS table to find the specific segments and concatenate them one by one.
Easy to do but always waste of time. Moreover, each EBS install has a different number of segments and query written for one client needs to be rewritten for another.
Well, not any more. There are Oracle APIs that allow you to get the complete combination as a string by passing the CCID

Here is the syntax:

fnd_flex_ext.get_segs('SQLGL', 'GL#', chart_of_accounts_id, code_combination_id)

Now, if you are on release 12 and want to have the concatenated combination descriptions you can also run this:

xla_oa_functions_pkg.get_ccid_description (chart_of_accounts_id, code_combination_id)

Here is a sample query:

select
fnd_flex_ext.get_segs( 'SQLGL',
'GL#',
(select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where name='Vision Operations (USA)'),
code_combination_id) account,
xla_oa_functions_pkg.get_ccid_description(
(select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where name='Vision Operations (USA)'),
Code_combination_id) description
from GL_JE_LINES


The fnd_flex_ext.get_segs call is universal and will work for any key flexfield. Here is the syntax for HR position hierarchies:


FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id)


and here is a sample query:


select
FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id) position_definition
from per_position_definitions

How to find item cost

Another common issue when writing queries is getting an item cost.


Here are the APIs to use in queries:


For discrete manufacturing:


cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)


here is a sample query:


select segment1 item,
cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL) cost
from mtl_system_items_b

Oracle Application Help Line: Email the output of a concurrent program as Attachment

http://erpschools.com/Apps/oracle-applications/Articles/Sysadmin-and-AOL/Email-the-output-of-a-concurrent-program-as-Attachment/index.aspx

Email the output of a concurrent program as Attachment

OA Frame Work

Following are the Steps to Start With OAF :-

1)Download the latest version of JDeveloper (OAJdev.zip) which is patched for OA Framework from metalink.(We used 9.0.3 version of JDeveloper)
2) Unzip the accompanying zip file to a directory of your choice (see **NOTE below) on your client machine, which creates the following directory structure under your :
jdevbin\
jdevdoc\
jdevhome\
**NOTE: The installation directory to which you are unzipping this file must NOT contain spaces.E.g. Do NOT unzip to C:\Program Files\jdeveloper.
3. For convenient access to startup the IDE, create a desktop shortcut
to the JDeveloper executable located at:
\jdevbin\jdev\bin\jdevw.exe
Do NOT launch JDeveloper at this time. Proceed to Step 3 below.
4. To setup and test your development environment access the chapter on
'Setting Up Your Development Environment' in the Oracle Applications
Framework Developer's Guide from the following location:
\jdevdoc\WebHelp\devguide\gs\gs_setup.htm
Follow the instructions and complete all of the steps under the section titled:
'You are Customer, Consultant or Support Representative'
To ensure your setup is correct, you should complete the verification
steps described under 'Task 6: Test your Setup'.

Steps to Kill Concurrent Program

1)SELECT * FROM DBA_DDL_LOCKS WHERE NAME LIKE'PACKAGE NAME ON WHICH CONC
PGM IS RUNNING%';
-- TAKE SESSION_ID AS SID
2) SELECT * FROM V$SESSION WHERE SID=----;
--SELECT SID AND SERIAL# FROM THE ABOVE
3)ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Steps to Register Shell Script as a concurrent program

Steps to Register Shell Script as a concurrent program

step 1:
=======
Place the .prog script under the bin directory for your
applications top directory.

For example, call the script ERPS_DEMO.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called ERPS_DEMO.prog use this:

ln -s $FND_TOP/bin/fndcpesr XXINVPDF

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
'Host'. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use ERPS_DEMO

step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#
#
# ========
# History
# ========
# Version 1 shivmohan 19-sep-2008 Created for knoworacle.com users
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5
SourceDirectory=$6
TargetDirectory=$7
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : “
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”
then
echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo “File Successfully copied from source to destination”
exit 0
fi
echo “****************************************************************”

cd $AP_TOP/bin

chmod 777 XXINVPDF.sh

transfer it in binary mode
--------------------------------------

COMMANDS

ls ----for list of files

cd $AP_TOP ---for changing the directory

ls *.pdf ---will shows list of PDF files only

cat invpdf.prog ---shows the content of the file

$ ls -------


vi --view the contant

:wq ---save&quit

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

1.BINARY mode fiel transfer

echo $AP_TOP/bin ----we will get the path to place the shell script
place the file in the APtop/bin... in BINARY mode

2..go to the putty and

cd $AP_TOP/bin and run the following link

chmod 755 XXINVPDF.prog ---for giving the permissions

ln -s $FND_TOP/bin/fndcpesr XXINVPDF ---for linking


3..RUN THE scripts tp upload the conc progrm

XXAP_SR40_EDI_AUTO_ATTACH_INVOICE_CONC_PRG_REG_1.sql

XXAP_SR40_EDI_AUTO_ATTACH_INVOICE_CONC_PRG_REG_2.sql

4.place the files "XX_SR40_REQ.ldt" and "XX_SR40_REQ_LINK.ldt" & XX_SR40_APPL_INSTALL.sh in $AP_TOP/bin in ASCII mode

5. give the permissions to "XX_SR40_APPL_INSTALL.sh"

6. copy the content into vi editior and run the shell sciprt

as XX_SR40_APPL_INSTALL.sh

7.attch teh requeset to the request group "DX AP Super Visior Reports" by placing the LDT "XX_SR40_APSUPER_RG.ldt".

8. RUN THE BELOW UPLOAD COMMAND

FNDLOAD apps/dba_0907 O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_SR40_APSUPER_RG.ldt



---------------------EDI vendors query------------

SELECT DISTINCT SOURCE,VENDOR_ID FROM AP_INVOICES_ALL WHERE SOURCE IN ('EDI UNBALANCED DATA','EDI UNBALANCED PML',
'EDI UNBALANCED SYSMEX','EDI UNBALANCED SIEMENS','EDI UNBALANCED VWR')

----------invoice number fetching---------

select substr(file_name,instr(file_name,'_',1,1)+1,instr(file_name,'_',1,2)-instr(file_name,'_',1,1)-1) INNAME from fnd_lobs where trunc(sysdate)=trunc(upload_date)

select instr(file_name,'_',1,1) from fnd_lobs where trunc(sysdate)=trunc(upload_date)

select instr(file_name,'_',1,2)- instr(file_name,'_',1,1) from fnd_lobs where trunc(sysdate)=trunc(upload_date)




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


REQUEST GROUP DX AP Super Visior Reports

Request Set EDI Auto Attach Request Set