Oracle Applications - Suresh Vaishya

Use of variable with comma seperated value in Oracle SQL Query

2010-09-08T18:13:00.024-04:00

The requirement is to pass comma seperated value to a procedure and to use that variable in the query to extract values. For e.g. variable p_ord_num_list has a value of '90001234, 90001235, 90001236' and we attempt to use this in variable in the query as below

SELECT * FROM oe_order_headers_all WHERE order_number IN p_ord_num_list

The above query completes in error

ORA-01722: invalid number

ORA-06512: at line 13

The above requirement can be achieved in following way

DECLARE

   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';

BEGIN

   FOR i IN (SELECT order_number

             FROM   oe_order_headers_all e

             WHERE  order_number IN (

                       SELECT EXTRACTVALUE (xt.COLUMN_VALUE, 'e')

                       FROM   TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (   '<ord_num><e>'

                                                                    || REPLACE (p_ord_num_list, ',', '</e><e>')

                                                                    || '</e></ord_num>'

                                                                   )

                                                         , '/ord_num/*'

                                                          )

                                                 )

                                    ) xt))

   LOOP

      DBMS_OUTPUT.put_line ('a = ' || i.order_number);

   END LOOP;

END;

The way above statement works is that it first generates the XML tag for each comma seperated value and then extracts values from each element.

The other way to do this is by using regular expression functions as shown below

DECLARE

   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';

BEGIN

   FOR i IN (SELECT * FROM oe_order_headers_all WHERE order_number IN (         

SELECT     TRIM(REGEXP_SUBSTR(p_ord_num_list   , '[^,]+', 1, LEVEL)) item_id

FROM       (SELECT p_ord_num_list    str

            FROM   DUAL)

CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',')) + 1))

   LOOP

      DBMS_OUTPUT.put_line ('a = ' || i.order_number);

   END LOOP;

END;

Create form with Cancel Query option

2010-06-17T16:30:00.001-04:00

Set the form level property Interaction Mode to Blocking or non-Blocking.
When set to non-Blocking, a dialog box appears that contains the following prompt:

Press cancel to end this database operation

When a long running query is executed in forms, this enables the option to interrupt the query by pressing the cancel button. However, this only works for Forms blocks which are based on standard tables or views.
It does not work for blocks based on stored procedures, where the data is returned by a ref cursor or a table of records.

Enable Cancel Query option on Oracle Forms

2010-06-16T08:02:00.001-04:00

Sometimes when we query form with higher data results the forms hangs and leaves us with option to wait until data is displayed or close it forcefully.
Using profile option FND: Enable Cancel Query, the cancel button with message "Press cancel to end this database operation" can be displayed that allows canceling query. The profile option may not support all forms but still you can have this option for most of the oracle provided forms.

ReCompile invalid objects using UTL_RECOMP

2010-06-01T16:37:00.002-04:00

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('APPS');
EXEC UTL_RECOMP.recomp_parallel(4, 'APPS');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'APPS');

Reference: Oracle Base

Disable interactive prompts at SQL*Plus

2010-05-07T12:56:00.000-04:00

Whenever we have ampersand "&" in our script, SQL Plus prompts for a value to be entered. There are several ways to avoid that prompt as discussed below

1) user chr(38) instead of &

SELECT 'I love SQL '||chr(38)||' PLSQL' from dual;

2) Use & at the end just below the single quotes

SELECT 'I love SQL &'||' PLSQL' from dual;

3) SET DEFINE OFF can be use to disable the prompt

SET DEFINE OFF
SELECT 'I love SQL & PLSQL ' from dual; 
SET DEFINE ON

Change SQL prompt oracle SQL*Plus (pre 10g)

2010-05-05T13:05:00.001-04:00

Here we discussed how to change prompt for release 10g and up. Now we will discuss how to achieve same in pre 10g releases

Enter following commands in glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory or execute them one by one at SQL Prompt.

col username new_value username
col dbname new_value dbname
set termout off
SELECT lower(user) username,
       substr(global_name, 1, instr(global_name, '.')-1) dbname
FROM   global_name
/
set termout on
set sqlprompt '&&username@&&dbname> '

Change SQL Prompt in oracle SQL*Plus (10g and up)

2010-05-05T12:37:00.006-04:00

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>'

2) Display database name

set sqlprompt '_connect_identifier_privilege>'

Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"

4) To set the time at sqlprompt

set time on

Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
set time on

Display line number in VI editor in Unix

2010-05-04T13:38:00.002-04:00

Following command can be used to display line numbers

:set number

Following command can be used to hide the line numbers

:set nonumber

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

2010-05-02T18:41:00.002-04:00

Below is an example of DBMS_XMLGEN.getxml to generate XML tags directly out of the query

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
                     ola.ordered_item,
                     ola.ordered_quantity
                FROM ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id 
                 and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
       CURSOR(SELECT ohd.name, ohs.hold_comment
                FROM ont.oe_hold_sources_all ohs,
                     ont.oe_order_holds_all ohld,
                     ont.oe_hold_definitions ohd,
                     ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id
                 AND ola.line_id = ohld.line_id 
                 and ohld.hold_release_id is null
                 AND ohld.hold_source_id = ohs.hold_source_id
                 AND ohs.hold_id =  ohd.hold_id
                 AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL

Output

FNDLOAD - Menu Driven

2010-04-28T21:12:00.011-04:00

Hi friends, before I proceed thanks to everyone reading and supporting the blog.

Here is another new post on FNDLOAD which provides a menu to select the option and execute FNDLOAD Command.
Below are the screenshots of how screen looks
1)Accepts the apps password. Note that the password is hidden and not displayed on the screen.

2)Displays a menu to select an option and Downloads/Uploads a .ldt file

Below is the script

#The script that Displays a Menu and calls FNDLOAD command accordingly. 
#Created by       Date            Version
#Suresh Vaishya   29-Jul-09       1.0
#http://sureshvaishya.blogspot.com

#Suresh Vaishya. Reading apps password outside loop to avoid entering same thing again and again.
echo "Enter APPS Password: "
stty -echo             #Turns echo off
read appspwd
stty echo              #Turns echo on

sel='123456789'
while true
do  
if [ $sel -ne '123456789' ]
then
echo "Press ENTER key to continue"
read key
fi

tput clear
echo "1.  Download Concurrent Program"
echo "2.  Upload   Concurrent Program"
echo "3.  Download Request Group for a program"
echo "4.  Upload   Request Group for  a program"
echo "5.  Download Value Set"
echo "6.  Upload   Value Set"
echo "7.  Download Menu"
echo "8.  Upload   Menu"
echo "9.  Download Descriptive Flexfield Definition"
echo "10. Upload   Descriptive Flexfield Definition"
echo "11.  Download Descriptive Flexfield Definition"
echo "12. Upload   Descriptive Flexfield Definition"
echo "13. Download Lookup Definition and Values"
echo "14. Upload   Lookup Definition and Values"
echo "15. Download Forms Personalization"
echo "16. Upload   Forms Personalization"
echo "17. Download Responsibility"
echo "18. Upload   Responsibility"
echo "q or Q. Quit"

echo "Enter your selection  "
read sel

#echo "You entered $sel"
if [ "$sel" = 'q' ] || [ "$sel" = 'Q' ]
then
exit
fi

if [ $sel -gt 0 ] && [ $sel -lt 19 ]
then
if [ `printf "%d\n" "'$sel"` -gt 57 ] || [ `printf "%d\n" "'$sel"` -lt 49 ] # Suresh Vaishya. Using the ascii value to check valid values
then
echo "You entered $sel"
echo "Invalid selection. Valid value are from 1 to 18."
else
echo "Enter .ldt Name: "
read ldtname

#Suresh Vaishya   Download Concurrent program
if [ $sel -eq 1 ]
then
echo "Enter Application Short Name: "
read applname

echo "Enter Concurrent Program Short Name: "
read cpname

FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname PROGRAM APPLICATION_SHORT_NAME="$applname" CONCURRENT_PROGRAM_NAME="$cpname"

echo "LDT File $ldtname created"
#Suresh Vaishya Upload Concurrent program
elif [ $sel -eq 2 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname - CUSTOM_MODE=FORCE
echo "LDT File $ldtname uploaded"
#Suresh Vaisha Download Request Group
elif [ $sel -eq 3 ]
then
echo "Enter Request Group Application Short Name"
read applname

echo "Enter Request Group Name"
read rgname

echo "Enter Program Short Name"
read cpname

FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct $ldtname REQUEST_GROUP REQUEST_GROUP_NAME="$rgname" APPLICATION_SHORT_NAME="$applname" REQUEST_GROUP_UNIT UNIT_NAME="$cpname"
#Suresh Vaishya Upload Request Group
elif [ $sel -eq 4 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct $ldtname
echo "LDT File $ldtname uploaded"
#Suresh Vaishya   download Value Set
elif [ $sel -eq 5 ]
then
echo "Enter Value set Short Name"
read vsname

FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname VALUE_SET FLEX_VALUE_SET_NAME="$vsname"
echo "LDT File $ldtname created"
#Suresh Vaishya Upload Value Set
elif [ $sel -eq 6 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname
echo "LDT File $ldtname uploaded"
#Suresh  Vaishya Download Menu 
elif [ $sel -eq 7 ]
then
echo "Enter Menu Name"
read mname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $ldtname MENU MENU_NAME="$mname"
#Suresh Vaishya Upload Menu 
elif [ $sel -eq 8 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $ldtname
#Suresh    Vaishya Download DFF
elif [ $sel -eq 9 ]
then
echo "Enter Application Name"
read applname
echo "Enter Descriptive Flexfield Name"
read dff
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname DESC_FLEX APPLICATION_SHORT_NAME="$applname" DESCRIPTIVE_FLEXFIELD_NAME="$dff"
#Suresh  Vaishya Upload DFF
elif [ $sel -eq 10 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname
#Suresh Vaishya Download KFF
elif [ $sel -eq 11 ]
then
echo "Enter Application Name"
read applname
echo "Enter Key Flexfield Name"
read dff
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname KEY_FLEX APPLICATION_SHORT_NAME="$applname" DESCRIPTIVE_FLEXFIELD_NAME="$dff"
#Suresh  Vaishya Upload KFF
elif [ $sel -eq 12 ]
then
FNDLOAD apps/$appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $ldtname

#Suresh Vaishay Download Lookup type
elif [ $sel -eq 13 ]
then
echo "Enter Application Name"
read applname
echo "Enter Lookup Type Name"
read lname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct $ldtname FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="$applname" LOOKUP_TYPE="$lname"
#Suresh Vaishya Upload Lookup Type
elif [ $sel -eq 14 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct $ldtname
#Suresh Vaishya Download Forms Personalization
elif [ $sel -eq 15 ]
then
echo "Enter Function Name for the form"
read fname
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct $ldtname FND_FORM_CUSTOM_RULES FUNCTION_NAME="$fname"
#http://sureshvaishya.blogspot.com Upload forms personalization
elif [ $sel -eq 16 ]
then
FNDLOAD apps/$appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct $ldtname
#Suresh Download Responsibility
elif [ $sel -eq 17 ]
then
echo "Enter Responsibility Key"
read rname
FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct $ldtname FND_RESPONSIBILITY RESP_KEY="$rname"
#Suersh Upload Responsibility
elif [ $sel -eq 18 ]
then
FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct $ldtname
fi

fi # ascii if
else
echo "You entered $sel"
echo "Invalid selection. Valid value are from 1 to 18."
fi # number check if
done
# End of Script. http://sureshvaishya.blogspot.com

Colors in Concurrent Request Manager Screen

2010-04-20T11:36:00.000-04:00

On concurrent manager screen normally along with default form color we see Red, Yellow and Green color for Error, Warning and Pending respectively. Recently I came across a profile that would change this normal behavior and hide these colors.

The profile name is FND: Indicator Colors. The profile also affects the behavior of required parameter which is normally displayed in yellow.
Another point to note is that by setting this profile to No only concurrent manager screen is affected and other forms behave normally.

This is just for your information.

How to complete Host(Unix Shell Script) Concurrent Program with Warning

2010-03-29T11:14:00.002-04:00

If we have a concurrent program as of type host(Unix Shell Script), then the program by default either completes Normal or in Error.
If we return from shell script using text exit 0, program completes in normal and if we use any value other value e.g. exit 1, exit 2, exit 10 etc then the program completes in error.
Below is the code that can be used if requirement is to complete it in warning.


sqlplus -s $p_usr_pwd <<-EOF
  set feedback off
  declare
    l_stat Boolean;
    l_session_id Number;
  begin
    fnd_global.initialize(session_id => l_session_id
                        , user_id => fnd_global.user_id
                        , resp_id => fnd_global.resp_id
                        , resp_appl_id => fnd_global.resp_appl_id
                        , security_group_id => null
                        , site_id => null
                        , login_id => null
                        , conc_login_id => null
                        , prog_appl_id => null
                        , conc_program_id => null
                        , conc_request_id => $p_req_id
                        , conc_priority_request => null);
    l_stat := fnd_concurrent.set_completion_status('WARNING','Completed in Warning. Review log for details.');
    commit;
  end;
/
  exit;
EOF

In the example above $p_usr_pwd stores database username/password, $p_req_id stores the request ID for concurrent request that needs to complete in warning.

Related Post:
Concurrent program parameter in Unix Shell Script
Calling SQLPLUS from unix shell script

In a directory in Unix convert Tab Delimeted file to Comma delimeted

2010-03-12T15:16:00.010-05:00

On Request from one of our reader here is a post to convert excel files into a comma delimeted file.

create shell script using following code. The script below will fetch all files with extension xls and convert tabs into comma and create a .csv file.


cd /applmgr/custom/inbound/data
for i in *.xls
do
echo $i
newfile=$i.csv
awk 'BEGIN {
        FS = "\t"
        OFS = ","
     }
     {
        $1 = $1
        for (i = 1; i <= NF; i++) {
           if ($i == "") {
              $i = "null"
           }
        }
        print $0
     }' $i > $newfile
done

SQLLDR - Shell script to load Multiple files in table

2010-03-05T13:20:00.007-05:00

Based on request from one of our reader, here is the post to read file from a directory and then call sqlloader command to load data file into a table.
Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting with sample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code


LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)

Shell Script Code


cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done

3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is


SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

View Barcode in BI Publisher Report when run from Application

2010-02-15T13:48:00.003-05:00

Following steps should be completed in order to register your Barcode font to be viewed when report is run from Application.

1) From XML Publisher responsibility, create a font file in XML administration.
Navigation: XML Publisher Administrator -- Administration -- Font FilesCreate Font File
2) Create a font mapping set. Navigation: XML Publisher Administrator -- Administration -- Font Mapping
3) In the template screen , edit configuration -- FO Processing -- Font Mapping Set , provided the corresponding font mapping set
4) The last important step is to always use Territory column while defining Template. E.g. United States.

Now when report is generated from application, barcode font should be viewed.

Related Post:
Using 3 of 9 Barcodes in XML Publisher
Cannot View Output when previewed from RTF Template

Notify when SO is booked or new line is entered on booked order

2010-02-05T16:33:00.006-05:00

Based on a request from one of our reader, below are the steps on how to notify users when an order is booked or new line is inserted on a booked order.

The Alert below will send notification once a day in the morning at 8:00 am.

1) Define Alert

Query used is


SELECT ooh.order_number
    , ool.line_number||'.'||ool.shipment_number line_number
    , ordered_item, ordered_quantity, ool.flow_Status_code
INTO &order_num, &line_num,&Item_num, &Quantity, &line_Status
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
AND 
( ooh.booked_date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
 OR (ool.creation_Date >= to_date(Sysdate,'DD-MON-RRRR HH24:MI:SS')
   AND ool.creation_date > ooh.booked_date)
)

2) Define Actions
Click on the actions button and then actions Detail button and define message as shown in screenshot. Note that the message type is summary.

3) Define Action Sets
Click on action sets and then action set details and in the members tab enter the action defined in step 2

4) Schedule the Request
Navigate to Request --> Check and submit the alert. Based on the definition of alert it will be scheduled to run.

Kindly let me know if any questions.

Related Link: Introduction to Oracle Alerts

List of Receipts API in Oracle Receivables

2010-01-26T11:48:00.003-05:00

Below is the list of some of the Receipt API's in Oracle Receivables. Receipt APIs provide an extension to existing functionality for creating and manipulating receipts through standard AR Receipts forms and lockboxes.

AR_RECEIPT_API_PUB is the main package that has several procedures to perform different actions

1) AR_RECEIPT_API_PUB.APPLY & AR_RECEIPT_API_PUB.UNAPPLY:
Use this procedure to apply or unaply respectively, the cash receipts from a customer to an invoice, debit memo, or other debit item .
2) AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT & AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT
Use this procedure to apply or unapply a cash receipt on account.
3) AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT & AR_RECEIPT_API_PUB.UNAPPLY_OPEN_RECEIPT
To apply or unapply a cash receipt to another open receipt.
4) AR_RECEIPT_API_PUB.CREATE_CASH:
Procedure to create a single cash receipt for payment received in the form of a check or cash.
6) AR_RECEIPT_API_PUB.REVERSE
Procedure to reverse cash and miscellaneous receipts

In later post I will try to explain in detail how to use each API and the impact of same in application.

For BI Publisher Reports, cannot view Barcodes when previewed output in PDF

2010-01-21T14:58:00.009-05:00

Based on question from one of our reader here are the steps to view the barcode fonts when the report is previewed in PDF format from .rtf file.

The problem here is that the fonts are correctly installed in computer but when output is previewed instead of barcodes actual text is seen. One of the possible reason for this could be that the xdo.cfg file is not configured correctly. Below are the steps

1) Copy Barcode font in C:\Windows\Fonts directory.
2) Copy xdo.cfg in C:\Program Files\Oracle\XML Publisher Desktop\Template Builder for Word\config directory. The sample file is already available when BI Publisher desktop is installed.
3) Right click the font file and go to properties. Copy the .ttf file name. Double click the font file to get the family name.
4) Open the xdo.cfg file and paste as follows (My filename is FRE3OF9X.ttf and font family is Free 3 of 9 Extended)

[font family="Free 3 of 9 Extended" style="normal" weight="normal"]
[truetype path="C:\Windows\fonts\FRE3OF9X.ttf" /]
[/font]
Note: replace brackets([ & ]) with angle brackets as was unable to paste due to HTML restriction in comments. There should be sample already available in the xdo.cfg file.

5) Try your report should work fine and barcodes should be correctly displayed.

Related Post:
Using 3 of 9 Barcodes in XML Publisher
View Barcode in XML Publisher report when run from Application

Keywords: Code 39, Barcode 39, 3 of 9 Barcodes

Types of Purchase Orders

2010-01-19T14:19:00.003-05:00

Different types of purchase order in Oracle
Standard Purchase Order: A standard purchase orders is generally created for one–time purchase of various items. Standard purchase orders is created when we know the details of the goods or services we require, estimated costs, quantities, delivery schedules, and accounting distributions.

Blanket Purchase Agreements: A blanket purchase agreements are created when we know the detail of the goods or services we plan to buy from a specific supplier in a period, but we do not yet know the detail of your delivery schedules. Blanket purchase agreements can be used to specify negotiated prices for items before actually purchasing them. Blanket purchase agreements can be created for a single organization or to be shared by different business units of organization (global agreements).

Blanket Releases: A blanket release is issued against a blanket purchase agreement to
place the actual order (as long as the release is within the blanket agreement effectivity dates).

Contract Purchase Agreements: A contract purchase agreement is an agreement between buyer and a supplier for unspecified goods or services. This agreement may include terms and conditions, amount, and effective dates. Later Standard Purchase orders can be issued based on the agreements decided.

Planned Purchase Orders: A planned purchase order is a long–term agreement committing to buy items or services from a single source. Tentative delivery schedules and all other details like item, price, quantity is specified in planned purchase orders. We can issue scheduled releases against planned purchase order to create standard purchase orders.

Types of Discrete Jobs in WIP

2010-01-13T15:19:00.002-05:00

There are 2 types of discrete Jobs
1) Standard - Standard discrete jobs are used to manufacture a brand new product using Bill of Materials and Routings. E.g. There is a demand in the sales order for an Assembly item which in turn creates a WIP Job. For this case standard discrete job will be created.
2) Non-Standard - Non Standard discrete jobs are mostly used to repair a defective item. E.g. A product is returned by the customer for some defective reason. For this item a non-standard job is created and item is refurbished.

SQL Loader (SQLLDR) in Oracle Applications

2009-12-20T17:01:00.000-05:00

One of the executable type in Oracle applications is SQL*Loader. In this post we will discuss more about how to define, create and use this type of Execution Method.

Execution Method SQL*Loader is used to load data into database table using SQL*Loader. Below are the steps that needs to completed
1) Create Control File and copy it in the TOP/bin directory. E.g. if your custom application top directory is $XX_TOP, then the file should be copied in $XX_TOP/bin directory


OPTIONS (SKIP=1)
LOAD DATA infile '%1'
APPEND INTO TABLE sv_test_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
(
   item_number     "trim(:item_number)"
 , description     "trim(:description)"
 , attribute1      "trim(:attribute1)"
 , process_flag    Constant 'UNPROCESSED'
)

2) Create an executable with execution method as SQL*Loader and use the control file Name as Execution file Name. The Application should same as where the file is copied.

3) Create a concurrent program definition and use the executable created above.
It is optional to have any parameters to the program, but in this case the data file name should be same as control file name except the extension for the data file being .dat
If you notice in the control file definition above I have used %1 as the infile name. This is how parameters are referenced in control file.
So now we can create conc. program with a file name as a parameter(Full file name with path should be entered and this is case sensitive.)

4) Assign this program to the request group and it is ready to be used.

Error in RCV Transactions Interface (Receiving Transaction Processor)

2009-12-06T11:00:00.000-05:00

When trying to use RCV interfaces and ran Receiving transaction Processor import program, the transaction errored out with message "The parameters passed to procedure populate_cost_details are invalid."

In the documentation, the column name LAST_UPDATE_LOGIN is noted as "optional".
However, the columns user_id and login_id in package
inv_wwacst.populate_cost_details are selected from
mtl_material_transactions_temp table in inltpu, which is populated by the
Receiving Interface Manager. These columns are expected to be NOT null and
will error if they are null. The solution is to populate the LAST_UPDATE_LOGIN
with a value (type is Number. Even though Purchasing does not require this
field to be populated, this is required in Inventory when the items are
delivered.

Refer Metalink Note: 99533.1

Keywords: Receiving Open Interface, RVCTP, PO Receipts, populate_cost_details

Sales Order Line Status Flow and Meaning

2009-11-25T14:13:00.010-05:00

Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1) Entered (OM): Order is saved but not booked

2) Booked (OM): Order is Booked.

3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.

The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
The user manually Backorders the entire delivery.

8) Shipped (SE): The delivery line is shipped confirmed.

9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10) Picked (OM): Pick release is complete, both allocations and pick confirm

11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17) Closed (OM): Closed indicates that the line is closed.

18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

Reference: Oracle Metalink

SQL Loader limit number of rows

2009-11-03T09:16:00.002-05:00

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD


OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows


sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Query to Link between Order Management and Account Receivables

2009-11-02T14:53:00.000-05:00

On request I continue to write further on Post Relation between AR Invoice and Sales Order tables and based on the standard assumption, provide a query that can be used to link Invoice and Sales Order.

Query below can be handy


SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND    order_number = :p_order_number

Keywords: Sales Order, AR, OM

Query to find locked objects in Oracle

2009-10-21T09:57:00.003-04:00

Sometimes program takes a little longer time then expected, one of the reason to this could be that the table/object you are trying to manipulate is locked by other program and hence it is waiting for the resource to be released.
Below query can be handy to find the objects that are locked


SELECT   c.owner
       , c.object_name
       , c.object_type
       , b.SID
       , b.serial#
       , b.status
       , b.osuser
       , b.machine
       , b.program
       , b.module
       , b.action
FROM     v$locked_object a
       , v$session b
       , dba_objects c
WHERE    b.SID = a.session_id 
AND a.object_id = c.object_id
ORDER BY module

If you want to forcefully kill any session then it can be done using


alter system kill session 'sid,serial#'

e.g. 
altery system kill session '123,5325'

Restart or Bounce Apache Server

2009-10-01T15:51:00.001-04:00

Following command can be used to bounce apache. An apache bounce is needed to reflect any changes in the self service pages.

To Stop


$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop

To Start


$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Relation between AR Invoice and Sales Order tables

2009-09-08T16:22:00.007-04:00

Many times I have seen a question being asked in the forums about the relationship between AR Invoice and Sales Order.
There are several interface_line_attribute and interface_header_attribute columns in RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL table respectively which are used to map it with other modules.
The relationship is actually mapped using the descriptive flexfield.

Query for following
Application: Receivables
Title: Line Transaction Flexfield

and then in the context field is the listed different modules.

For Sales order search for Order Management or Order Entry and click on segments to see how they are mapped. Screenshots below

Click on the segments button to see column mappings.

The above method can also be used to find mapping of Receivables with other modules like Oracle Projects, Services, Contracts etc.

P.S. Please click on the image to zoom it.

Command/Code to get ASCII value in UNIX

2009-09-07T15:50:00.000-04:00

Found a method to ASCII to character and vice versa in unix and thought of sharing it.
Below is the code.


## Character to ASCII
printf "%d\n" "'A"

## ASCII number to character
awk -v char=65 'BEGIN { printf "%c\n", char; exit }'

Find Table and Column Name with table and column description

2009-09-01T09:34:00.006-04:00

Many times we refer to TRM to get the description or know more about the columns in a table. The query below can be handy to know which tables use the column and get a small description about the table and the column.
Note if there is a custom table created and not registered in oracle apps then those tables and columns will not be listed by the query below.

SELECT fa.application_id
, fa.application_short_name
, fat.application_name
, table_name
, column_name
, ft.description table_description
, fc.description column_description
FROM   fnd_tables ft
, fnd_columns fc
, fnd_application_tl fat
, fnd_application fa
WHERE  ft.table_id = fc.table_id
AND    fc.column_name = :column_name
AND    fat.application_id = ft.application_id
AND    fat.LANGUAGE = USERENV ('LANG')
AND    fa.application_id = fat.application_id

Print Image using Reports 6i using the file path

2009-08-13T11:44:00.008-04:00

To add further to my earlier post Display image using Reports 6i, here we will discuss how to display image when file path is stored in the table. In our example the file directory is a constant directory and only the file name is stored in the table.
The person requested this post had following problem
1) Need to store image in one of the directory and display that using Reports 6i
2) If file does not exists in the directory, the report should complete normal and do not display any image
3) Psuedo code
If file exists then
display image
else
display blank (no image)
end if;

Most of the steps discussed in earlier post are same with few changes as follows
Create a new formula column say CF_URL and write following code in the format trigger


function CF_URLFormula return Char is
 v_handle  utl_file.file_type;
 v_file_dir VARCHAR2(60) := '/u002/app/applmgr/temp/';
begin
-- The UTL_FILE is used to check if the file exists in the directory.
-- If it is unable to open the file then an exception will thrown.
-- We will catch the exception and return null as the final URL.
-- Without this exception for any invalid path, the report will error out 
-- and not work as expected.
 v_handle := utl_file.fopen(v_file_dir, :photo_name, 'R');
 utl_file.fclose(v_handle);
 RETURN v_file_dir||:photo_name;
exception
 when others then
 srw.message(100,SQLERRM);
 return null;
end;

Another change is in the layout. Screenshot below

Related Post:
Display image using Reports 6i
Insert BLOB Image file into Oracle Database Table

Display images in Reports 6i

2009-08-12T19:24:00.004-04:00

On request from one of our reader, Below is the post on how to display and dynamically change image in reports 6i.
Please refer to my post Insert BLOB image file into oracle database table where I have discussed the steps on how to insert a record into BLOB table.
We will use same SV_EMP_TABLE as a reference and using Reports 6i create a report that prints ID Card. For simplicity there are no parameters to the report and it will create ID cards for all the employees for which record exists in the table.
In the data model create following SQL Query

SELECT ID, PHOTO_NAME, PHOTO_RAW ,emp_name
FROM SV_EMP_PHOTO

The next step is to create a layout for the report. Screenshot below

Complete necessary concurrent program setup to run this report from Oracle Apps. Below is the screenshot of the report output.

Related Post:
Insert BLOB Image file into Oracle Database Table

P.S. Click on the image to zoom it.

Insert BLOB image file in oracle database table

2009-08-12T16:37:00.004-04:00

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table


CREATE TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)

Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.


CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   RETURNING   photo_raw
   INTO        l_dest;

   -- lock record
   SELECT     photo_raw
   INTO       l_dest
   FROM       sv_emp_photo
   WHERE      ID = p_id AND photo_name = p_photo_name
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
   -- get length
   l_length := DBMS_LOB.getlength (l_source);
   -- read the file and store in the destination
   DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

   -- update the blob field with destination
   UPDATE sv_emp_photo
   SET photo_raw = l_dest
   WHERE  ID = p_id AND photo_name = p_photo_name;

   -- close file
   DBMS_LOB.fileclose (l_source);
END --sv_load_image;
/

I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database


exec sv_load_image(1,'Pavki','one.jpg')
exec sv_load_image(2,'Suresh','two.jpg')
exec sv_load_image(3,'Rachna','three.jpg')

Following is how data is stored in the database

NVL2 Function in Oracle

2009-08-11T15:48:00.002-04:00

In Oracle, the NVL2 function extends the functionality found in the NVL function. It can be used to substitute a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

    NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.

Oracle Report 6i closes with Error when trying to compile some reports

2009-08-03T15:06:00.007-04:00

Sometimes when we try to compile a standard (complicated) oracle reports using Reports 6i, the report builder closes with following error message
Reports Builder has encountered a problem and needs to close. We are sorry for the inconvenience.

One of the fix is to check the version of oracle report and apply a patch and get Reports/Forms to the latest version.

Download patch from metalink

Set use of VI Commands at the UNIX prompt

2009-08-02T16:42:00.002-04:00

VI commands can be used at the UNIX prompt to retrieve history commands or modify already typed command using VI editor keys. This can be done by executing following command


set -o vi

One of the option is to enter and save this command in .profile file, so that everytime we login in UNIX this command is executed.

Enable Created By Field in the Find Move Order Screen

2009-07-30T10:14:00.002-04:00

To implement the solution, execute the following steps to enable the INV_INVTOMAI_CREATOR function:

A. Define the INV_INVTOMAI_CREATOR function if it does not already exist:

1. Navigate: System Administrator > Application > Function
2. Enter Function Name: INV_INVTOMAI_CREATOR
3. Enter User Function Name: 'Move Orders Creator'
4. Under the Properties tab, enter Type: Subfunction
5. Enter Maintenance Mode support: None
6. Enter Context Dependence: Responsibility
7. Save

B. Assign the function to the Menu you are using:

1. Navigate System Administrator > Application > Menu
2. Query for menu 'INV_MOVE_ORDER'
3. Add a new line to the menu:
Prompt: 'Move Order Creator'
Function: select 'Move Orders Creator' from the LOV
Description: 'Move Orders Creator'
4. Save

The Created By field in the Move Order find form should now be enabled and modifiable. You may need to sign out and back in for the change to take affect.

Reference: Metalink Note: 280131.1

Retrieve IP Address and Host Name usingPL/SQL

2009-07-24T09:54:00.007-04:00

On request from one of our reader.
Using the UTL_INADDR package, a PL/SQL subprogram can determine the host name of the local system or the IP address of a given host name.

E.g. Retrieve the local host name and IP address.


SET serveroutput on
BEGIN
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS('www.oracle.com'));
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME('141.146.9.91')); 
END;

Reference: www.oracle.com

Changing the color of an Item in Forms using Forms Personalization

2009-07-14T13:34:00.011-04:00

On request here are the steps to change the Font colors of an item using Forms Personalization.
I am using Item Screen for an example and displaying Item number in Red Color.
This is how form looks before customization. Note that Item number is displayed in its default color.

Now lets change the color. Select Help->Diagnostics->Custom Code->Personalize to open the forms personalization screen.
At this point of time there is no condition specified and the Item number is displayed in red in all the cases. So the trigger is written in WHEN-NEW_FORMS-INSTANCE

Click on Actions tab and enter as shown in the screen shot

Save your changes and re-open and query for an item

Note the change in the font color of Item Number.

The Foreground color used in above example is r255g0b0, which can be explained as follows

Any Color is made using combination of Red(r), Green(g) and Blue(b) color with a value ranging from 0 to 255. To create a red color give the value of r to maximum 255 and leave g and b to zero.
Similarly green color can be defined as r0g255b0

P.S. Click on the image to zoom it.

Set Profile Option Value using PL/SQL

2009-07-02T11:06:00.004-04:00

On request here is how to set the profile option value using PL/SQL

Function FND_PROFILE.SAVE can be used to set the value of any profile option at any level i.e. Site, Application, Responsibility, User etc.

Below is a sample code of how to use this function


DECLARE
   a   BOOLEAN;
BEGIN
   a := fnd_profile.SAVE ('CONC_REPORT_ACCESS_LEVEL'
                        , 'R'
                        , 'USER'
                        , '22746'
                        , NULL
                        , NULL
                         );

   IF a
   THEN
      DBMS_OUTPUT.put_line ('Success');
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line ('Error');
   END IF;
END;

Profile Option to View Output of Request submitted by other User

2009-07-02T10:53:00.007-04:00

Set the value of profile Concurrent:Report Access Level to Responsibility.
The output submitted by other users can now be viewed from the responsibility through which the request was submitted.
The other option available is User,which restricts the output only to the user who submitted the request.

Load multiple user datafiles into multiple tables using SQL*LOADER

2009-06-01T19:44:00.001-04:00

On request from one of the reader, below are the steps to load multiple tables using multiple datafiles.
Problem Description
-------------------
You have multiple datafiles to be loaded using SQL*Loader. Each of the data
files contains records that must be loaded into one of several tables. The
following is an example that uses the INFILE clause for each datafile and a
WHEN clause for each table to do this.

Records in a datafile that are to be loaded into multiple table can be loaded
based on a check for a value that distinguishes each record. This check can be
on an entire column or on a specific position within the data file.

Solution Description
--------------------
Example:
Datafiles are generated each day that contain multiple employees and the
projects those employees worked on that day. Projects can be worked on from
many locations, and an employee can work on many projects each day. Each week
all the daily files are gathered and loaded into project tables based on
location.
-----------------------Table Create Statements---------------
CREATE TABLE DENVER_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE ORLANDO_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE MISC_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

-------------------Control File - MFILES.CTL------------------
LOAD DATA
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'
APPEND

INTO TABLE DENVER_PRJ
WHEN PROJNO = '101'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE ORLANDO_PRJ
WHEN PROJNO = '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE MISC_PRJ
WHEN PROJNO != '101' AND PROJNO != '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )
--------------------Datafiles: DENVER.DAT---------------------
1011234515
1015432140
1012345620
3032345610
--------------------Datafiles: ORLANDO.DAT--------------------
2021234515
2022345610
4041234510
--------------------------------------------------------------
SQL*Loader will read all the input files together. It will parse each record
and then based on the condition(s) in a WHEN clause will evaluate whether the
record can be loaded into that table. Care should be taken as to how WHEN
conditions are constructed because each record is evaluated against every WHEN
clause and loaded into all tables that match the condition.

Reference: Metalink Note: 1023792.6

Discoverer Query to Display Document Name, Folder, Business Unit etc.

2009-05-15T23:21:00.001-04:00

Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.

 SELECT DISTINCT b.doc_developer_key Document_developer_key
              , b.doc_name document_name
              , a.qs_doc_details worksheet_name
              , c.obj_name folder_name
              , c.obj_developer_key folder_developer_key
              , d.ba_name Business_unit
              , fu.user_name created_by
 FROM           eul4_us.eul4_qpp_stats a
              , eul4_us.eul4_documents b
              , eul4_us.eul4_objs c
              , eul4_us.eul4_bas d
              , eul4_us.eul4_eul_users e
              , eul4_us.eul4_ba_obj_links f
              , fnd_user fu
 WHERE          a.qs_doc_name = b.doc_name
 AND            a.qs_doc_owner = e.eu_username
 AND            b.doc_eu_id = e.eu_id
 AND            c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
 AND            c.obj_id = f.bol_obj_id
 AND            d.ba_id = f.bol_ba_id
 AND            TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)

Using 3 of 9 Barcodes in XML Publisher

2009-05-08T10:10:00.006-04:00

I am using Code 39(3 of 9) Barcode font for my report. Every thing seems to be fine and barcode seems to be displayed correctly, but not recognized by the barcode reader/gun?
For code 39 barcode fonts asterisk(*) is used as a start and end delimeter. So any text which is displayed as barcode should have * at the start and end.

E.g. 12345 should be displayed as *12345*

How to query/find discoverer reports in Oracle using APPS Schema

2009-05-07T22:15:00.004-04:00

The table to store discoverer is stored in the EUL Schema. For e.g. if the name of EUL is EUL4_US then a schema with that name will be created in oracle database. Run following query to know the name of table

 SELECT owner,table_name
 FROM   all_tables
 WHERE  owner = 'EUL4_US' AND table_name LIKE '%DOCUMENT%'

The above query in this case will list table_name EUL4_DOCUMENTS

All the discoverer report names are stored in this table, so

SELECT * FROM EUL4_US.EUL4_DOCUMENTS

should display name, owner etc of discoverer reports

Preserve Timestamp, mode, owner while using copy command in Unix

2009-05-03T23:28:00.007-04:00

In Unix, Sometimes when we need to take a backup of the file we generally add timestamp along with the file and create a backup, however the new file created has the creation date as system date.
This can be avoided using preserve option in unix.
For e.g. I will copy ARXSGPO.rdf from $AR_TOP/reports/US directory. The file signature looks like

-rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf

Now the following files are created using preserve and no preserve option

 cp ARXSGPO.rdf ARXSGPO_np.rdf   # without preserve option
 cp -p ARXSGPO.rdf ARXSGPO_p.rdf # with preserve option

This is how the signature of file looks like

 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf
 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO_p.rdf
 -rw-r--r--  1 applmgr oracle 647168 May  3 23:43 ARXSGPO_np.rdf

It can be noted that ARXSGPO_np.rdf has timestamp as May 3, whereas file ARXSGPO_p.rdf created using preserve mode has timestamp as Apr 21. So using preserve option we can save the original information of the file from where the file is copied.

Sales Order Import using API

2009-04-15T19:58:00.005-04:00

Please note that this is just the sample code and should be used only for reference.
The code below creates a sales order in entered status with one line record. Also the price adjustment is done for the line.

DECLARE
l_api_version_number           NUMBER  := 1;
l_return_status                VARCHAR2 (2000);
l_msg_count                    NUMBER;
l_msg_data                     VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
l_debug_level                  NUMBER  := 1;    -- OM DEBUG LEVEL (MAX 5)
l_org                          NUMBER  := 5283;         -- OPERATING UNIT
l_no_orders                    NUMBER  := 1;              -- NO OF ORDERS
l_user                         NUMBER  := 28573;          -- USER
l_resp                         NUMBER  := 53073;        -- RESPONSIBLILTY
l_appl                         NUMBER  := 660;        -- ORDER MANAGEMENT
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec                   oe_order_pub.header_rec_type;
l_line_tbl                     oe_order_pub.line_tbl_type;
l_action_request_tbl           oe_order_pub.request_tbl_type;
l_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
/*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out               oe_order_pub.header_rec_type;
l_header_val_rec_out           oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out                 oe_order_pub.line_tbl_type;
l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out       oe_order_pub.request_tbl_type;
l_msg_index                    NUMBER;
l_data                         VARCHAR2 (2000);
l_loop_count                   NUMBER;
l_debug_file                   VARCHAR2 (200);
b_return_status                VARCHAR2 (200);
b_msg_count                    NUMBER;
b_msg_data                     VARCHAR2 (2000);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (l_org);
MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org);
mo_global.init('ONT');

/*****************INITIALIZE DEBUG INFO*************************************/
IF (l_debug_level > 0)
THEN
l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
oe_msg_pub.initialize;
END IF;

/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize (l_user, l_resp, l_appl);-- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_type_id := 2159;                                                                          -- domestic return
l_header_rec.sold_to_org_id := 659018;
l_header_rec.ship_to_org_id := 635775;
l_header_rec.invoice_to_org_id := 635776;
l_header_rec.order_source_id := 9;
l_header_rec.booked_flag := 'N';
l_header_rec.price_list_id := 39825;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := '1211314AFA';


/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl (1).operation := oe_globals.g_opr_create;
l_line_tbl (1).inventory_item_id := 826543;
l_line_tbl (1).ordered_quantity := 1;
--l_line_tbl(1).unit_selling_price := 2000; -- The price is done using adjustments
--l_line_tbl(1).unit_list_price := 2000;
l_line_tbl (1).calculate_price_flag := 'Y';
l_line_tbl (1).return_reason_code := 'B2';
--l_line_tbl(1).line_number := 1;
l_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
l_line_adj_tbl (1).operation := oe_globals.g_opr_create;
l_line_adj_tbl (1).list_header_id := 148129;
l_line_adj_tbl (1).list_line_id := 651550;
l_line_adj_tbl (1).change_reason_code := 'MANUAL';
l_line_adj_tbl (1).change_reason_text := 'Manually applied adjustments';
l_line_adj_tbl (1).operand := 2000;
l_line_adj_tbl (1).pricing_phase_id := 2;
l_line_adj_tbl (1).updated_flag := 'Y';
l_line_adj_tbl (1).applied_flag := 'Y';
l_line_adj_tbl (1).line_index := 1;

FOR i IN 1 .. l_no_orders   
LOOP                                        
/*****************CALLTO PROCESS ORDER API*********************************/
oe_order_pub.process_order (
p_api_version_number          => l_api_version_number
, p_header_rec                  => l_header_rec
, p_line_tbl                    => l_line_tbl
, p_action_request_tbl          => l_action_request_tbl
, p_line_adj_tbl                => l_line_adj_tbl
-- OUT variables
, x_header_rec                  => l_header_rec_out
, x_header_val_rec              => l_header_val_rec_out
, x_header_adj_tbl              => l_header_adj_tbl_out
, x_header_adj_val_tbl          => l_header_adj_val_tbl_out
, x_header_price_att_tbl        => l_header_price_att_tbl_out
, x_header_adj_att_tbl          => l_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => l_header_scredit_tbl_out
, x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out
, x_line_tbl                    => l_line_tbl_out
, x_line_val_tbl                => l_line_val_tbl_out
, x_line_adj_tbl                => l_line_adj_tbl_out
, x_line_adj_val_tbl            => l_line_adj_val_tbl_out
, x_line_price_att_tbl          => l_line_price_att_tbl_out
, x_line_adj_att_tbl            => l_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => l_line_scredit_tbl_out
, x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out
, x_lot_serial_tbl              => l_lot_serial_tbl_out
, x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out
, x_action_request_tbl          => l_action_request_tbl_out
, x_return_status               => l_return_status
, x_msg_count                   => l_msg_count
, x_msg_data                    => l_msg_data
);

/*****************CHECK RETURN STATUS***********************************/
IF l_return_status = fnd_api.g_ret_sts_success
THEN
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('success');
END IF;

COMMIT;
ELSE
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('failure');
END IF;

ROLLBACK;
END IF;
END LOOP; -- END LOOP
/*****************DISPLAY RETURN STATUS FLAGS******************************/
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('process ORDER ret status IS: ' 
|| l_return_status);
DBMS_OUTPUT.put_line ('process ORDER msg data IS: ' 
|| l_msg_data);
DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' 
|| l_msg_count);
DBMS_OUTPUT.put_line ('header.order_number IS: ' 
|| TO_CHAR (l_header_rec_out.order_number));
DBMS_OUTPUT.put_line ('adjustment.return_status IS: ' 
|| l_line_adj_tbl_out (1).return_status);
DBMS_OUTPUT.put_line ('header.header_id IS: ' 
|| l_header_rec_out.header_id);
DBMS_OUTPUT.put_line ('line.unit_selling_price IS: ' 
|| l_line_tbl_out (1).unit_selling_price);
END IF;

/*****************DISPLAY ERROR MSGS*************************************/
IF (l_debug_level > 0)
THEN
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_data, p_msg_index_out => l_msg_index);
DBMS_OUTPUT.put_line ('message is: ' || l_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index);
END LOOP;
END IF;

IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);
DBMS_OUTPUT.put_line ('Debug Level = ' || TO_CHAR (oe_debug_pub.g_debug_level));
DBMS_OUTPUT.put_line ('Debug File = ' || oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);
DBMS_OUTPUT.put_line ('****************************************************');
oe_debug_pub.debug_off;
END IF;
END;
/

Introduction to Oracle Alerts

2009-04-05T21:58:00.003-04:00

Introduction:
Oracle Alerts is something that can be used to Notify/Alert to one or multiple persons about an activity or change that occurs in the system. The alerts can also be used to call a procedure, run some sql script etc.
There are 2 types of alert
1) Periodic Alert
2) Event Alert

Periodic Alerts:
These alerts are trigger periodically, hourly, daily, weekly, monthly etc based upon how it is setup to be triggered. When alert runs and the condition(SQL Query etc.) in the alerts fetches record, then the events specified in the alert are triggered.
Ex. 1) Daily alert to send notification on the sales order on which credit check hold is applied for a day
2) Hourly alert to send notification on all the concurrent request that completed with error

Event Alerts:
These Alerts are fired/triggered based on some change in data in the database. This is very similar to the triggers written on the table. Unlikely, event alerts can only fire on After Insert or After Update.
Ex. 1) An alert that sends notification when new item is created.

In next post we will discuss steps to create Periodic and Event alert.

Customer Import using API's

2009-03-18T20:11:00.001-04:00

Following API's are used for creating customers
1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created. hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using API hz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using API hz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using API hz_customer_profile_v2pub.create_customer_profile

Concurrent Program Name with Parameter, Value set

2009-03-10T14:31:00.002-04:00

On request here is the query to list concurrent program name with its parameter, values set and default value/type


  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , par.column_seq_num      
      , par.end_user_column_name
      , par.form_left_prompt prompt
      , par.enabled_flag
      , par.required_flag
      , par.display_flag
      , par.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , par.DEFAULT_VALUE
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl par
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = par.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = par.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG')
 ORDER BY par.column_seq_num

Query to get Customer Name, Number and Address

2009-03-06T20:09:00.002-05:00

Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses.


SELECT hp.party_name
     , hp.party_number
     , hca.account_number
     , hca.cust_account_id
     , hp.party_id
     , hps.party_site_id
     , hps.location_id
     , hl.address1
     , hl.address2
     , hl.address3
     , hl.city
     , hl.state
     , hl.country
     , hl.postal_code
     , hcsu.site_use_code
     , hcsu.site_use_id
     , hcsa.bill_to_flag
FROM   hz_parties hp
     , hz_party_sites hps
     , hz_locations hl
     , hz_cust_accounts_all hca
     , hz_cust_acct_sites_all hcsa
     , hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hps.party_id
AND    hps.location_id = hl.location_id
AND    hp.party_id = hca.party_id
AND    hcsa.party_site_id = hps.party_site_id
AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND    hca.cust_account_id = hcsa.cust_account_id
AND    hca.account_number = :customer_number

P.S. The query is not completely tested. Please let me know if you find any problem

Query to List all the responsibilities attached to a User

2009-02-20T09:47:00.002-05:00

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.


select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')

Unable to Retrieve All Rows Message in Discoverer

2009-02-17T11:13:00.007-05:00

Problem: Trying to export report data into excel or when clicking on Tools--> Retrieve all Rows, Not all Rows have been Retrieved. Data may be inaccurate. error message is displayed.

Resolution: By Default in discoverer only 10000 records are set to be retrieved. This needs to be changed to a higher number in order to avoid this problem.
Click on Tools-->Options. Select Query Governer Tab and change the value in Limit retrieved query to 99999 to allow maximum number of records to be fetched.

Keywords: Discoverer 10g

Printing Barcode on a zebra Printer using ZPL

2009-02-10T17:28:00.000-05:00

Here we will discuss on how to automatically print Barcodes on a zebra printer from Oracle concurrent program.
Below are the 3 approaches that we tried and was successful with the third approach.
1) Print Labels on a Zebra printer using BI Publisher
The first approach we tried was to print barcodes using BI(formerly XML) Publisher. The report generates a PDF output and should print directly on a Zebra printer using PASTA driver. For some reason the report was not printing directly on a printer. The PDF opened in window and printed seperately/manually using Windows driver was working fine but not meeting our requirement of automatic printing. Hence we had to try second approach.
2) Print Labels using Zebra Enterprise Connector Solution
The Zebra Enterprise Connector Solution is designed to streamline the printing
process for companies using ERP systems such as Oracle BI Publisher. The Zebra
Enterprise Connector Solution helps to lower middleware costs, overhead, and
pre-printer licensing fees. It can be used with an unlimited number of
Zebra ZPL-II printers without additional per-printer licensing fees. We didnt do much research on this as were looking for an approach with minimal cost. More details on this is available at the website of Zebra
3) Print Lables on Zebra printers using Zebra Programming Language(ZPL)
The second and successful approach was to print labels using ZPL. A text output with all the zebra codes was created and which would ultimately print Barcode images on a printer. A normal regular lp command for printing text is used to print over Zebra Printers.
Below is an example of how to print Barcodes


^XA
^FO40,35^AR,10,10^FDItem :^FS
^FO40,115^AR,10,10^FDItem Description:^FS
^FO40,165^AR,10,10^FDQuantity :^FS
^FO40,245^AR,10,10^FDDiscrete Job# :^FS
^FO40,295^AR,10,10^FDSupply Sub :^FS
^FO40,375^AR,10,10^FDLocator :^FS
^FO40,455^AR,10,10^FDDestination Sub :^FS
^FO40,505^AR,10,10^FDLocator :^FS
^FO40,555^AR,10,10^FDOrder # :^FS
^FO40,635^AR,10,10^FDLine # :^FS
^FO40,715^AR,10,10^FDOrder Type :^FS
^FO40,765^AR,10,10^FDDepartment :^FS^FO305,765^AR,10,10^FDResource Group :^FS
^FO40,845^AR,10,10^FDWork Center :^FS^FO305,845^AR,10,10^FDItem Sequence :^FS
^FO40,925^AR,10,10^FDAsm Item # :^FS^FO305,925^AR,10,10^FDAsm Item Desc :^FS

^FO265,35^BY3^B3N,N,50,Y,Y^FD199395B^FS
^FO265,115^AR,10,10^FDFILTER, LUBE OIL.^FS
^FO265,165^BY3^B3N,N,55,Y,N^FD2^FS
^FO265,245^AR,10,10^FD613467^FS
^FO265,295^BY3^B3N,N,50,Y,N^FDASM^FS
^FO265,375^BY3^B3N,N,50,Y,N^FDASM.REW..^FS
^FO265,555^BY3^B3N,N,50,Y,N^FD1264076^FS
^FO265,635^BY3^B3N,N,55,Y,N^FD1^FS
^FO265,715^AR,10,10^FDMove Order^FS
^FO50,795^AR,10,10^FD6400^FS
^FO375,795^AR,10,10^FD0047^FS
^FO70,875^AR,10,10^FDASMVH12.4.5.A^FS
^FO335,875^AR,10,10^FD3^FS
^FO70,955^AR,10,10^FDL7044GSI-1021598^FS
^FO335,955^AR,10,10^FDP2212J13 L7044 GSI W/ESM & AFR^FS
^XZ

More details on how to write codes using ZPL can be found in the website of zebra
The output of the above code is shown below.

Set the Low and High Date range parameter in the concurrent program

2009-02-02T12:36:00.001-05:00

On request to one of the reader...
Below is the scenario,
The concurrent program has 2 date parameter e.g. Start Date and End date. The requirement is that start date should always be less than end date.

Solution:
1) In the concurrent program definition parameters, select Range as Low for Start Date. See Screenshot below
2) In the concurrent program definition parameters, select Range as high for End Date. See Screenshot below. Note that the value set being used for Start date and End date should be same.

3) Test program entering incorrect value for the parameter.

4) Test again by entering correct value.

Display Negative Number in angle brackets

2009-01-14T17:52:00.006-05:00

We often wonder and write all the codes to display negative numbers in brackets. But this can be easily achieved using a TO_CHAR function with the correct format.

TO_CHAR(number, '9999PR') -
Returns negative value in angle brackets.
Returns positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.

Below is an example to achieve this

select to_char(-133133,'99999999999PR') from dual

In above example the number is displayed in brackets without comma seperator.

Following query can be used to display negative numbers in angular bracket with comma seperator.

select to_char(-133133,'999G999G990D99PR') from dual

Keywords: angle, angular, negative, brackets

ORA-24323: Value not Allowed error in Oracle Reports

2009-01-14T10:33:00.004-05:00

Sometimes while working in Oracle Reports whenever we are trying to modify any query in the data model
ORA-24323: Value not allowed error is raised.

This happens when we are connected to database and for some reason the connection is dropped/disconnected. When we click on file menu, we feel like the connection still exists, but in reality this is not true. To overcome this error, completely close report builder and reopen it.

Happy New Year 2009

2008-12-31T19:58:00.001-05:00

Wish you all a very happy New Year 2009. May all the wishes and dreams come true.

Enhancing 11i/12 Homepage Menu via Firefox and Greasemonkey

2008-12-24T11:36:00.008-05:00

Source: Gareth Robert Blog
Here is something I found very interesting and useful and thought of sharing it with others.

In oracle E-Business suite the Oracle homepage menus is not in an organized manner and hence many times we have to make use of favourite option and for easy access save frequently used ones there.

By Default the home page is displayed as

After the enhancement the menu will be displayed as

Now lets discuss how this can be achieved.
Following things should be installed
1) Firefox
2) GreaseMonkey (Firefox Addon)
3) Install the Script

Once the Add-On is installed a Monkey icon will be displayed in status bar on the right side of firefox. Right click on that and click on Manage User Scripts and add the applications URL.

Many thanks to the Author for sharing this with us.

Wish you all a Merry Christmas and Happy new year

List of Tables for Forms Personalization

2008-12-08T10:25:00.002-05:00

Below is the list of tables that are populated when any Forms Personalization is done


FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

Click here For FNDLOAD on forms personalization

Convert Date into Week Rage

2008-11-22T20:27:00.002-05:00

Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08

Firstly lets see how to get week of the year. Following query can be used to get this

 SELECT to_char(sysdate,'WW') FROM Dual;

Now lets get the week range


SELECT    TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
       || ' to '
       || TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM   Dual;

The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08

API to Create Sales Order(SO) Reservations

2008-11-12T09:57:00.000-05:00

Below is the code that can be used to create SO reservations


DECLARE
   p_rsv                     inv_reservation_global.mtl_reservation_rec_type;
   p_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2(240);
   x_rsv_id                  NUMBER;
   x_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_status                  VARCHAR2(1);
   x_qty                     NUMBER;
BEGIN
--   fnd_global.APPS_Initialize(28270,53073,660);
   dbms_application_info.set_client_info(5283);
   --p_user_id, p_resp_id,  p_resp_appl_id
   --p_rsv.reservation_id            := NULL; -- cannot know
   p_rsv.requirement_date            := Sysdate+2;
   p_rsv.organization_id             := 5343; --mtl_parameters.organization id
   p_rsv.inventory_item_id           := 949729;--mtl_system_items.Inventory_item_id;
   p_rsv.demand_source_type_id       := inv_reservation_global.g_source_type_oe; -- which is 2
   p_rsv.demand_source_name          := NULL;
   p_rsv.demand_source_header_id     := 1334166 ; --mtl_sales_orders.sales_order_id
   p_rsv.demand_source_line_id       := 4912468 ; -- oe_order_lines.line_id
   p_rsv.primary_uom_code            := 'EA';
   p_rsv.primary_uom_id              := NULL;
   p_rsv.reservation_uom_code        := 'EA';
   p_rsv.reservation_uom_id          := NULL;
   p_rsv.reservation_quantity        := 10;
   p_rsv.primary_reservation_quantity := 10;
   p_rsv.supply_source_type_id       := inv_reservation_global.g_source_type_inv;
   
   inv_reservation_pub.create_reservation
   (
        p_api_version_number       =>       1.0
      , x_return_status            =>       x_status
      , x_msg_count                =>       x_msg_count
      , x_msg_data                 =>       x_msg_data
      , p_rsv_rec                  =>       p_rsv
      , p_serial_number            =>       p_dummy_sn
      , x_serial_number            =>       x_dummy_sn
      , x_quantity_reserved        =>       x_qty
      , x_reservation_id           =>       x_rsv_id
   );
   dbms_output.put_line('Return status    = '||x_status);
   dbms_output.put_line('msg count        = '||to_char(x_msg_count));
   dbms_output.put_line('msg data         = '||x_msg_data);
   dbms_output.put_line('Quantity reserved = '||to_char(x_qty));
   dbms_output.put_line('Reservation id   = '||to_char(x_rsv_id));
   IF x_msg_count >=1 THEN
     FOR I IN 1..x_msg_count
     LOOP
       dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
       fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
     END LOOP;
     
   END IF;
COMMIT;
END;
/

Similary we have update_reservations, relieve_reservations, delete_reservations API to respectively update, relieve or delete reservations.

Store SQLPLUS output to a variable in Unix Shell Script

2008-10-13T11:27:00.002-04:00

In response to one of the question asked, here is the solution of how to call sqlplus and store the output in a variable.
In the script below I am connecting to sqlplus through unix and querying fnd_lookup_values table and storing the output of query in a unix variable. This variable can then be later referred in the code using $.


#Suresh Vaishya:The script connects to sqlplus and stores output of a query into a unix variable.
#The output of query is seperated by '~' to identify different record values.

login='apps/apps'
code=`
sqlplus -s $login <set heading off
set feedback off
SELECT lookup_code||'~' FROM fnd_lookup_values WHERE lookup_type ='AGREEMENT LINES' and enabled_flag = 'Y' and Nvl(end_Date_ac
tive,sysdate+1) > sysdate and language='US';
exit
EOF`
echo 'Output of SQL Query is '
echo $code
echo 'End of Script'

The script output is


Output of SQL Query is
BLANKET~ ITEM~ MAIN~ NOTES~ PRICE~ QUOTATION~
End of Script

API to create Item Category

2008-10-06T10:41:00.002-04:00

The item categories can be created using item import when Items are created.
They can also be created using APIs discussed below
Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

In Detail
Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.


INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
  X_CATEGORY_ID  OUT    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER
)

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.


INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_CATEGORY_ID  IN    NUMBER,
  P_PARENT_CATEGORY_ID  IN    NUMBER,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2
)

Following APIs can be used to create/update/delete Item category assignments.


INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_DESCRIPTION  IN    VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

Item Import (Item Conversion)

2008-10-02T10:09:00.004-04:00

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = 'CREATE' or 'UPDATE'
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item's separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert '!'

FNDLOAD using Shell Script

2008-09-23T10:52:00.005-04:00

Whenever we use FNDLOAD at times it becomes difficult to remember the .lct name.
Here I have tried to create a shell script which can be used to DOWNLOAD or UPLOAD concurrent program definitions using FNDLOAD. The shell script prompts for necessary parameter needed to perform specific action.

Shell Script Code
-----------------
Download concurrent program definition using FNDLOAD. Create a shell script FNDCPDOWN using following code.


 echo "Enter APPS Password: "
 stty -echo             #Turns echo off
 read appspwd
 stty echo              #Turns echo on
 
 echo "Enter .ldt Name: "
 read ldtname
 
 echo "Enter Application Short Name: "
 read applname
 
 echo "Enter Concurrent Program Short Name: "
 read cpname
 
 FNDLOAD apps/$appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldtname PROGRAM APPLICATION_SHORT_NAME="$applname"
 CONCURRENT_PROGRAM_NAME="$cpname"
 
 echo "LDT File $ldtname created"

Upload Concurrent Program using FNDLOAD. Create a shell script FNDCPUP using following code.


 echo "Enter APPS Password"
 stty -echo                  #Turns echo off
 read appspwd
 stty echo                   #Turns echo on
 echo 'Enter .ldt file name to upload'
 read ldt_name
 
 FNDLOAD apps/$appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $ldt_name

Test the above created shell script.
For downloading the script will prompt for APPS password, .ldt file name, application name and Concurrent program short name.
For uploading the script will prompt for APPS password and .ldt file name.

Related Post
Create Concurrent Programs using FNDLOAD

Over 10,000 visits in less than 7 months

2008-09-23T09:06:00.000-04:00

Hurrrrrray .. There has been over 10,000 hits from around the world in less than 7 months. Thanks for your regular visit and comments.
Keep visiting. Please feel free to share with me any information/content/topic and I can post that with your name in this blog.

Thanks again.
Suresh

Call Concurrent Program from responsibility Menu

2008-09-03T10:24:00.001-04:00

The concurrent programs can be called in one of the following 2 ways
1) From standard concurrent request by attaching the concurrent program to a request group. The request group name can be found from the responsibility. Query for the request group and attach concurrent program to that request group. Now the program will be available from that responsibility.
2) The other option is to call request directly from the Menu.

To assign a concurrent program to a menu follow the steps. I have taken Import Bills and Routings and import items program as an example.
a) Create a new function of form type and name it as your concurrent program
Bills of Material:

Import Items:

b) In the parameter field enter the request group name(if all programs assigned to the request is to be available) or enter concurrent program name(if only one concurrent program should be available).
Bills of Material:

Import Items:

c) Assign this function to a responsibility menu from which you want to run this concurrent program.

Now go to that responsibility and click on the function. It will directly launch the concurrent program

Completed 50 Posts

2008-09-01T23:15:00.002-04:00

Just noticed that I have posted 50 articles in the blog. Thanks for visiting and appreciating it by your responses/comments. That does gives a lot of encouragement and boost in posting more topic, so keep peeking the site as there is lot more to come.

Any ideas or suggestions are more than welcome. If you have any article that needs to be shared, please feel free to share with me and I can post them in the blog with your name/photo.

Thanks,
Suresh

Increase Session Timeout Time

2008-08-13T17:21:00.001-04:00

The session when kept inactive for certain period gets inactive and hence we need to relogin again.
It sometimes become frustating when working on a test instance and makes us login again and again. This can be avoided by setting a profile option.

Profile "ICX_SESSION_TIMEOUT" can be used to increase the session timeout time.

Query to get Concurrent program name and its parameter

2008-07-11T17:31:00.002-04:00

Below query can be used to get concurrent program name and its parameter


SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , par.end_user_column_name
     , par.form_left_prompt prompt
     , par.enabled_flag
     , par.required_flag
     , par.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl par
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = &conc_prg_name
AND    fcpl.LANGUAGE = 'US'
AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

FNDLOAD in a nutshell

2008-06-30T16:21:00.018-04:00

In the following links I have discussed on how to migrate
1) Concurrent Programs
2) Value sets
from one instance to another using FNDLOAD.

Now I will try to cover several other objects that can be migrated using FNDLOAD.
The syntax for moving any objects using FNDLOAD is almost the same except few changes. Following is the list of .lct files that are used for different objects
1) Concurrent Program --> afcpprog.lct
2) Value Sets --> afffload.lct
3) Menus --> afsload.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups --> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='XXXX' LOOKUP_TYPE='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield --> afffload.lct
Descriptive Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options --> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility --> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups --> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX' REQUEST_GROUP_UNIT UNIT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
9) Menus --> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization --> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='XXXX'
Upload
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

FNDLOAD to create value sets

2008-06-27T18:52:00.004-04:00

Download a Value Set:


FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'

Download Value Set Values:


FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXXX'

Upload a Value Set:


FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt

Related Post: Create Concurrent Program using FNDLOAD

FNDLOAD to create concurrent programs

2008-06-23T18:40:00.006-04:00

The FNDLOAD can be used to create several oracle objects like Menu, Concurrent program, request sets, DFF, Responsibility, Messages, forms personaliztion etc. from one instance to another.
Here I will discuss how to create concurrent program using FNDLOADS

Download the program definition

FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME='XXXX' CONCURRENT_PROGRAM_NAME='CONC_PRG_NAME'

FNDLOAD_CONC_PRG_NAME.ldt is the file where concurrent program information is extracted. This file will be used to create same definition in any other instance.

Upload the Program definition

FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt

Note: Any value set that needs to be created along with concurrent program is downloaded and created automatically by FNDLOAD.

Date List of Value(LOV) for Concurrent Request Parameter

2008-06-10T15:18:00.009-04:00

We are aware that it is not possible to display calendar window for concurrent request parameter. Here I am discussing on an alternative to create Date List of values.
This is addition to my earlier post Calender in Concurrent program.
Create a table value set as shown in the screenshot below.

click on Edit Information and Enter following in the table name field

(SELECT (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
           , (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word 
      FROM DUAL CONNECT BY LEVEL <= 1000)

This is how concurrent program looks when a value is attached to it.

Related Post: Calender in Concurrent program.

Basics of Value Sets in Oracle Applications

2008-06-09T16:13:00.006-04:00

Explained below is the basics of Value Set.

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

None:

* There is no validation done for this type of value set, hence allows user to enter any value.

Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.

Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.

Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.

Special & Pair:

Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.

Translatable Independent & Translatable Dependent:

* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES

Display Distinct Value in Value Set

2008-06-06T16:01:00.004-04:00

Now there are 3 ways to create distinct values value set
1) Write distinct statement in the field where table name is entered, but this has a limitation to the number of characters that can be entered. so if query is long then you cannot make use of this feature
2) Create a view based on your query and use that view in the value set
3) Write your query in such a way that it gives you a distinct value. You can make use of ROWID to meet the requirement.


  ex. SELECT ooh.order_number
      FROM   oe_order_lines_all ool, oe_order_headers_all ooh
      WHERE  ool.header_id = ooh.header_id
      AND    ool.ROWID = (SELECT MAX(ROWID)
                          FROM   oe_order_lines_all oo11
                          WHERE  oo11.header_id = ooh.header_id)

Note that the above query is just an example and needs to be changed as per your need. In the above query I purposely joined lines and header to display duplicate order_numbers and then supressed it by using MAX(ROWID).

Enable/Disable Concurrent program parameter based on Other Parameter

2008-05-29T09:27:00.014-04:00

Based on a request, here is the details on how parameter can be enabled/disabled based another parameter value.
Below is the requirement
There are 2 valuesets parameter. If for parameter 1 user selects Yes then the other paramter should be enabled whereas if No is selected then the parameter should remain disabled. This can be achieved by using a hidden parameter as explained below.

Step1: Need 3 value sets for 3 parameter.
Value set1 = BOM_SRS_YES_NO (Oracle Defined)
Value Set2 = AMS_SRS_CHAR1 (Oracle Defined)
Value Set3 = SV_DEPENDENT_VS (User Defined)

Step2: Create Concurrent program as displayed in the screenshot below
Parameter1: Main Parameter

Parameter2: Hidden PArameter

Parameter3: Dependent Parameter

Step3: Assign concurrent program to a request group and test your program.

The PL/SQL Wrap Utility - Hide source code in Oracle

2008-05-21T11:22:00.009-04:00

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename]

e.g.

 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb

An example of using WRAP


Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
 dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped 
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0

/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

Query to get Customer Related information for a Sales Order

2008-05-20T17:42:00.006-04:00

Here is another handy query to get Customer related information for a sales order.
The query will list SHIP TO and BILL TO Address for a customer.

SELECT ooh.order_number
     , hp_bill.party_name
     , hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
      ||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
      ||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
      ||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
      ||hl_ship.city    ||Decode(hl_ship.state,NULL,'',',')
      ||hl_ship.state   ||Decode(hl_ship.postal_code,'',',')
      ||hl_ship.postal_code ship_to_address
     , hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
      ||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
      ||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
      ||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
      ||hl_bill.city    ||Decode(hl_bill.state,NULL,'',',')
      ||hl_bill.state   ||Decode(hl_bill.postal_code,'',',')
      ||hl_bill.postal_code bill_to_address
     , ooh.transactional_curr_code currency_code
     , mp.organization_code
     , ooh.fob_point_code
     , ooh.freight_terms_code
     , ooh.cust_po_number
FROM   oe_order_headers_all ooh
     , hz_cust_site_uses_all hcs_ship
     , hz_cust_acct_sites_all hca_ship
     , hz_party_sites hps_ship
     , hz_parties hp_ship
     , hz_locations hl_ship
     , hz_cust_site_uses_all hcs_bill
     , hz_cust_acct_sites_all hca_bill
     , hz_party_sites hps_bill
     , hz_parties hp_bill
     , hz_locations hl_bill
     , mtl_parameters mp
WHERE  1 = 1
AND    header_id = :p_header_id
AND    ooh.ship_to_org_id = hcs_ship.site_use_id
AND    hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND    hca_ship.party_site_id = hps_ship.party_site_id
AND    hps_ship.party_id = hp_ship.party_id
AND    hps_ship.location_id = hl_ship.location_id
AND    ooh.invoice_to_org_id = hcs_bill.site_use_id
AND    hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND    hca_bill.party_site_id = hps_bill.party_site_id
AND    hps_bill.party_id = hp_bill.party_id
AND    hps_bill.location_id = hl_bill.location_id
AND    mp.organization_id(+) = ooh.ship_from_org_id

Please do let me know if this queries are helping and I will post more of such queries.

Note: The query is not tested completely. If any problem found with the above query please let me know and I will try to fix them.

Create List of Values (LOV) in Discoverer

2008-05-12T10:28:00.003-04:00

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.
2) Create a custom folder for US States using following query

select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the properties. Click on Item Class and select the Item class created in step 3.

Splitting String using Oracle SQL 9i

2008-05-05T18:14:00.006-04:00

Run following query to split values seperated by comma(,)

 SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1 
                     ) 
            )
         AS token
   FROM ( SELECT ','||:in_string||',' AS txt  FROM dual )
 CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1

Example
-------
If value of in_string is entered as 1234,2,3,45,6,7,7,88,9,346

Output is
TOKEN
-----
1234
2
3
45
6
7
7
88
9
346

Reference:
Tom Kyte's Blog

FAQ: Common Tracing Techniques for Oracle Apps

2008-05-05T14:29:00.008-04:00

1) How to enable trace in the Oracle Application screens / forms?
Below is the navigation to enable trace for forms
Help Menu --> Diagnostics --> Trace(Select appropriate trace as needed)

Most commonly if debugging an error, you may select trace with binds.
When debugging a performance issue, you may consider using trace with binds and waits.

Remember to disable trace as this will keep generating the file.

2) How to enable trace for a concurrent program?
Refer to post Create Trace File for Concurrent Program

3) How to enable trace for a concurrent program INCLUDING bind variables and waits?
The above method can be used to generate a trace file, but this does not includes bind variables and wait times.
Download and review the script, bde_system_event_10046.sql, from Metalink Note 179848.1.
The above script is used to turn on the trace with binds and wait(Level 12).
Run Script and press enter when prompted.
Return to application and submit your concurrent program.
The moment status changes to running, switch to SQL*PLUS and press enter again to turn tracing off.
Important: Note that the tracing is set at global level and any program that runs during time when trace is kept on will be traced. Hence it is very important to immediately turn trace off after your programs status changes to running.

4) How to enable trace for all actions that occur for a user?
Use profile option Initialization SQL Statement - Custom
Set the value at user level

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'4269824.999' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Reference: Metalink Note 296559.1

Related Post:
SQL Trace and TKPROF
Various options with TKPROF
Create Trace File for Concurrent Program

Various options with TKPROF

2008-05-02T14:28:00.005-04:00

The post is in continuation to SQL Trace and TKPROF

Syntax of TKPROF

tkprof [filename1] [filename2] [WAITS] [SORT] [PRINT]
There are few more but these are more commonly used.

Argument Description
filename1 : The trace file name
filename2 : Filename to which TKPROF writes formatted output
WAITS : Flag to record summary for any wait events found in the trace file. Values are YES or NO
SORTS : Sorts traced SQL statements in descending order of specified sort option before listing them into the output file

PRSCNT Number of times parsed.
PRSCPU CPU time spent parsing.
PRSELA Elapsed time spent parsing.
PRSDSK Number of physical reads from disk during parse.
PRSQRY Number of consistent mode block reads during parse.
PRSCU Number of current mode block reads during parse.
PRSMIS Number of library cache misses during parse.
EXECNT Number of executes.
EXECPU CPU time spent executing.
EXEELA Elapsed time spent executing.
EXEDSK Number of physical reads from disk during execute.
EXEDSK Number of physical reads from disk during execute.
EXEQRY Number of consistent mode block reads during execute.
EXECU Number of current mode block reads during execute.
EXEROW Number of rows processed during execute.
EXEMIS Number of library cache misses during execute.
FCHCNT Number of fetches.
FCHCPU CPU time spent fetching.
FCHELA Elapsed time spent fetching.
FCHDSK Number of physical reads from disk during fetch.
FCHQRY Number of consistent mode block reads during fetch.
FCHCU Number of current mode block reads during fetch.
FCHROW Number of rows fetched.

PRINT : Number of SQL statements to be displayed

Example 1
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

tkprof abcd53269.trc abcd53269.txt SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

Example 2
Print the SQL Query taking maximum CPU Usage

tkprof abcd53269.trc abcd53269.txt SORT = (EXECPU,FCHCPU) PRINT = 1

SQL Trace and TKPROF

2008-04-30T14:10:00.007-04:00

Understanding SQL Trace
Times when program is performing poorly, creating and examining a trace file is one of the best way to find what is causing the problem for poor performance.
Following is the list of some of the SQL Trace statictics that are generated in the trace file.

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Commits and Rollback

There are several ways by which you can turn on/off SQL Trace


Turn on
DBMS_SESSION.SET_SQL_TRACE(TRUE);
ALTER SESSION SET SQL_TRACE = TRUE;


Turn off
DBMS_SESSION.SET_SQL_TRACE(FALSE);
ALTER SESSION SET SQL_TRACE = FALSE;

Click here to set SQL Trace on a concurrent program.
The trace file is created in the udump directory.

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

Create Trace File for Concurrent Program

2008-04-21T11:59:00.008-04:00

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) --> Concurrent --> Program --> Define

Query for the concurrent program and check enable trace button.

Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.

select * from v$parameter
where name like '%user_dump_dest%'

The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Click for more info on TKPROF

Oracle Table Rename Syntax

2008-04-18T14:37:00.002-04:00

There is a direct command to rename table.
The syntax is as follows


alter table
   table_name
rename to
   new_table_name;

Note: When the table is renamed the referenced object like PL/SQL, applications etc are not updated. Hence this may make those invalid. However the indexes, constraints etc are updated.

Dependent Value Parameter in Discoverer

2008-04-08T17:00:00.000-04:00

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

Not Required Parameter in Discoverer

2008-04-07T08:40:00.002-04:00

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Password Policy and profile options

2008-04-04T11:37:00.001-04:00

On apps there are a lot of profile options that are useful in making apps passwords difficult to guess, the profiles are

1-Signon password failure limit
2-Signon Password Length
3-Signon Password No Reuse
4-Signon Password Hard to Guess

For the first one it means how many time can I try to access the system using wrong password. It is recommended to change this value to 3. The default value is null.

The second one to allow minimum password length. The default value is 5, it is recommended to make it 6 or 7.

The 3rd profile is for not allowing using same password again for specified number of days.

The default value for 4th profile option is No. Following are the password rules if the value is set to Yes
1) The password contains at least one letter and at least one number.
2) The password does not contain the username.
3) The password does not contain consecutively repeating characters.

Reference: Metalink Note 362663.1

Basic VI Editor Commands

2008-04-01T19:13:00.013-04:00

Cursor Movements

h, j, k, l	Move left, Down, Up, Right
0	Move to First Character of the Line
$	Move to end of Line
w	Move to next word
CTRL + D	Move Page Down
CTRL + U	Move Page Up
G	Move to the End of File
1G or :1	Move to the Top of File
nG	Move to Line number n. (replace n is any integer value)
CTRL + G	Display current Line Number

Find a Text

/text	Find a text in Forward Direction
?text	Find a text in Reverse Direction
n	Repeat the previous Search

Note: text needs to be replaced by the string that needs tobe searched.

Editing, Inserting orDeleting a Text

a	Append after Cursor
A	Append to the end of Line
i	Insert before cursor
I	Insert from the Beginning of the line
o	Insert a new Line in the edit mode
cw	Change word
cc	Change whole line
C	Change text to end of line
dd	Delete Current Line
5dd	Delete current line and next 4
D	Delete from Current Position to the end of line
u	Undo last change
U	Restore Current Line

Copy(yanking) and Paste

yy	Yank a copy of Current Line
p	Insert previously yanked Line

Saving and Exiting

ZZ or :wq	Save and exit file
:q!	Exit without Saving
:w filename1	Save edited file as filename1. Similar to Save as.

Calling SqlPlus From Unix Shell Script

2008-03-29T14:54:00.002-04:00

In this post we will discuss how we can call sqlplus from within a unix shell script. Here is a post on how to read concurrent program parameters from shell script and this is a continuation to that.
The syntax to call a SQLPLUS from shell script is


sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
--  variable declarations
....
....
begin
--sql statements..
....
....
end;
/
exit
EOF

In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign. Any dbms_output.put_line statements are printed in the log file if script is run through concurrent program.

Hide a Button in the form using Forms Personlization

2008-03-27T23:26:00.008-04:00

In one of the forum, there was a requirement to allow bookings only by certain user.
This can be achieved using forms personalization for users on 11.5.10 or higher.
Open the sales Order Form and click on Help->Diagnostics->Custom Code->Personalize to open the forms personlization screen.
(Click on the image to zoom it)

P.S.
In the example above I have hardcoded user name which is not the advisable way.
The better approach could be to create a custom profile and assign a value to the custom profile at user level. Based on the value entered the button can be enabled or disabled.

Note: The same result can also be achieved by using custom.pll

Custom Title Bar

2008-03-25T12:58:00.004-04:00

Profile option "Site Name" can be used to display custom message in the title bar.
Often used in DEV/Test instance to track when the instance was refreshed/cloned.

Read Concurrent Parameters in UNIX shell script

2008-03-25T09:17:00.005-04:00

Found this question in one of the forum and thought of creating a blog for same.
Often when concurrent program is created on a shell script there is a need to extract parameters passed from concurrent program in the shell script.
In Shell Script, $1 is the variable that stores following information
- Concurrent Program Short Name
- Request ID
- Login Information(APPS username and password)
- User Id
- User Name
- Printer Name
- Save output Flag
- Print number of Copies
- List of concurrent program parameters

Following code can be used to extract different values


requestid=`(echo $1 | cut -f2 -d' ' | cut -f2 -d= | tr -d '"' )`   #request_id
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
conc_user_name=`(echo $1 | cut -f5 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #username
prog_param1=`(echo $1 | cut -f9 -d' ' | tr -d '"' )`  #parameter 1 
prog_param2=`(echo $1 | cut -f10 -d' ' | tr -d '"' )`  #parameter 2 
prog_param3=`(echo $1 | cut -f11 -d' ' | tr -d '"' )`  #parameter 3 
prog_param4=`(echo $1 | cut -f12 -d' ' | tr -d '"' )`  #parameter 4 
prog_param5=`(echo $1 | cut -f13 -d' ' | tr -d '"' )`           #parameter 5 
prog_param6=`(echo $1 | cut -f14 -d' ' | tr -d '"' )`           #parameter 6

When connecting to SQLPLUS in shell script, it is always advisable to extract database username and password from parameter($login in our example above) and not hard-code the value in program.

Replace Keyboard with Mouse - Type using Mouse

2008-03-17T20:33:00.002-04:00

Here is something interesting for you.
Replace Keyboard with Mouse and type letters using mouse.
Steps:
Click Start --> Select Run --> Type OSK --> Press OK

A Keyboard will Popup on the screen that can be used for typing. Isn't that interesting and useful sometimes.

External Table in Oracle

2008-03-16T16:01:00.005-04:00

From Version 9i Oracle has come with a new feature facilitating us to query directly from the FLAT File. This is know as external tables.
Here are the simple steps for creating external table
1) Create Directory

create or replace directory sv_ext_dir as '/home/svaishya/external_table'

2) Grant access to Directory

grant read, write on directory sv_ext_dir to apps

The user must have a read and write permission the directory('/home/svaishya/external_table' in this case).
3) Create External Table


CREATE TABLE sv_external_Table
(col1 VARCHAR2(50)
,col2 VARCHAR2(50)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY sv_ext_dir
ACCESS PARAMETERS
( FIELDS TERMINATED BY '|' )
LOCATION ('abc.txt')
)

abc.txt is the name of the file that will be located in the path mentioned in the directory sv_ext_dir

4) Place the file in the Directory Path

abc.txt (used in above create table command) is copied the directory.

5) Thats it, query table to extract data.

SELECT * FROM sv_external_table

Everytime the file is updated with new data the data in the table will be automatically refreshed.

Different Types of Table in Oracle Apps

2008-03-16T15:39:00.011-04:00

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B	The Main base tables
_ALL	Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V	View created on base table. Mostly forms are created based on this views
_TL	Tables that support multi language.
_VL	View created on multi language tables. The view generally uses the base table and _tl table
_F	This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S	sequence related tables
_DFV /_KFV	The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF. Also using this table the values can be queried based on the DFF/KFF name and not attributes column.

See Parameters for the Submitted Request

2008-03-15T09:39:00.004-04:00

A concurrent program is submitted which has many parameters. After the program is submitted I need to map the values with the parameter.
Click "Find Request" -> "View Details...", click on "Parameters" field, the parameters form will popup.

Sometimes you may feel that the forms have gone crazy and the parameters form does not pop up.This is because you are not in the correct responsibility that enable you to launch that request. The parameters form popup only from the responsibility where reports are registered.

View Report Output Submitted by others

2008-03-15T09:10:00.003-04:00

Many times it happens that we need to see output submitted by others. Through system administrator View all concurrent program option we can search for the request but the problem is that the view output button is grayed off. How do we enable this button and see the output.
The answer is simple as profile does a magic here.

Change the Profile Option value for Concurrent:Report Access Level. Default value for the profile is "User", which means View Output is only accessible by the particular user that submitted the request. Set it to "Responsibility", now the View Output button is enabled from responsiblity where the report is registered.