Invoices/Payments Posted

SQLs to find out posted Invoices and Payments in last 7 days

------------------------------------------------------------------------------------------
Invoices Posted in Last 7 Days
------------------------------------------------------------------------------------------
SELECT AEH.set_of_books_id        set_of_books_id
      ,GLC.user_je_category_name  journal_category
      ,AEH.accounting_date
      ,ALC.displayed_field        event_type
      ,PV.vendor_name
      ,AI.invoice_num
      ,DECODE(AI.doc_sequence_id, NULL, AI.voucher_num, AI.doc_sequence_value) voucher_num
      ,AEL.ae_line_number         line_num
      ,ALC2.displayed_field       line_type
      ,AEL.code_combination_id    account_ccid
      ,AEL.currency_code
      ,AEL.entered_dr
      ,AEL.entered_cr
      ,AEL.accounted_dr
      ,AEL.accounted_cr
      ,AEH.gl_transfer_flag
      ,( SELECT name
           FROM APPS.hr_all_organization_units
          WHERE organization_id = ai.org_id) org_name
      ,AI.org_id
  FROM APPS.ap_accounting_events_all AAE
      ,APPS.ap_ae_headers_all        AEH
      ,APPS.ap_ae_lines_all          AEL
      ,APPS.po_vendors               PV
      ,APPS.ap_invoices_all          AI
      ,APPS.gl_je_categories         GLC
      ,APPS.ap_lookup_codes          ALC
      ,APPS.ap_lookup_codes          ALC2
WHERE  AAE.accounting_event_id = AEH.accounting_event_id
  AND  AEH.ae_header_id        = AEL.ae_header_id
  AND  AAE.source_id           = AI.invoice_id
  AND  AAE.org_id              = AI.org_id
  AND  AI.vendor_id            = PV.vendor_id
  AND  GLC.je_category_name    = AEH.ae_category
  AND  ALC.lookup_type         = 'EVENT TYPE'
  AND  ALC.lookup_code         = AAE.event_type_code
  AND  ALC2.lookup_type        = 'AE LINE TYPE'
  AND  ALC2.lookup_code        = AEL.ae_line_type_code
  AND  AEH.AE_Category         = 'Purchase Invoices'
  AND  ael.ae_line_type_code   = 'LIABILITY'
  AND  AEH.accounting_date > SYSDATE - 7
  AND  AI.org_id IN (12,34,56,78,90)
ORDER BY aeh.ae_category, AI.org_id, AEH.accounting_date

------------------------------------------------------------------------------------------
Payments Posted in Last 7 Days
------------------------------------------------------------------------------------------

SELECT AEH.set_of_books_id        set_of_books_id
      ,GLC.user_je_category_name  journal_category
      ,AEH.accounting_date
      ,ALC.displayed_field        event_type
      ,PV.vendor_name
      ,AI.invoice_num       
      ,DECODE(AI.doc_sequence_id, NULL, AI.voucher_num, AI.doc_sequence_value) voucher_num
      ,AEL.ae_line_number         line_num
      ,ALC2.displayed_field       line_type
      ,AEL.code_combination_id    account_ccid
      ,AEL.currency_code          
      ,AEL.entered_dr
      ,AEL.entered_cr
      ,AEL.accounted_dr
      ,AEL.accounted_cr
      ,AEH.gl_transfer_flag
      ,( SELECT name
           FROM APPS.hr_all_organization_units
          WHERE organization_id = ai.org_id) org_name
      ,AI.org_id
  FROM APPS.ap_accounting_events_all AAE
      ,APPS.ap_ae_headers_all        AEH
      ,APPS.ap_ae_lines_all          AEL
      ,APPS.po_vendors               PV
      ,APPS.ap_invoices_all          AI
      ,APPS.ap_invoice_payments_all  AIP
      ,APPS.gl_je_categories         GLC
      ,APPS.ap_lookup_codes          ALC
      ,APPS.ap_lookup_codes          ALC2
WHERE  AAE.accounting_event_id = AEH.accounting_event_id
  AND  AEH.ae_header_id        = AEL.ae_header_id
  AND  AAE.source_id           = AIP.check_id
  AND  AIP.invoice_id          = AI.invoice_id
  AND  AIP.accounting_event_id = AEH.accounting_event_id
  AND  AIP.invoice_payment_id  = AEL.source_id
  AND  AAE.org_id              = AI.org_id
  AND  AI.vendor_id            = PV.vendor_id
  AND  GLC.je_category_name    = AEH.ae_category
  AND  ALC.lookup_type         = 'EVENT TYPE'
  AND  ALC.lookup_code         = AAE.event_type_code
  AND  ALC2.lookup_type        = 'AE LINE TYPE'
  AND  ALC2.lookup_code        = AEL.ae_line_type_code
  AND  AEH.AE_Category         = 'Payments'
  AND  ael.ae_line_type_code   = 'LIABILITY'
  AND  AEH.accounting_date > SYSDATE - 7
  AND  AI.org_id IN (12,34,56,78,90)
ORDER BY aeh.ae_category, AI.org_id, AEH.accounting_date, AI.invoice_id

------------------------------------------------------------------------------------------
R11i Payables Transfer To GL Reference Field Mappings [ID 186230.1] 
------------------------------------------------------------------------------------------
For Invoices: 
------------------------------------------------------------------------------------------
                     AP_AE_LINES_ALL  GL_INTERFACE     GL_IMPORT_REFERENCES  GL_JE_LINES
------------------------------------------------------------------------------------------
Vendor Name          reference1       reference21      reference_1           reference_1
Invoice ID           reference2       reference22      reference_2           reference_2
Inv Dist Line Num    reference3       reference23      reference_3           reference_3
Blank                reference4       reference24      reference_4           reference_4
Invoice Number       reference5       reference25      reference_5           reference_5
Category             reference6       reference26      reference_6           reference_6
Set of Books ID      reference7       reference27      reference_7           reference_7
blank                reference8       reference28      reference_8           reference_8
blank                reference9       reference29      reference_9           reference_9
Line Type            reference10      reference30      reference_10          reference_10
------------------------------------------------------------------------------------------ 
For Payments: 
------------------------------------------------------------------------------------------
                     AP_AE_LINES_ALL  GL_INTERFACE     GL_IMPORT_REFERENCES  GL_JE_LINES
------------------------------------------------------------------------------------------
Vendor Name          reference1       reference21      reference_1           reference_1
Invoice ID           reference2       reference22      reference_2           reference_2
Check ID             reference3       reference23      reference_3           reference_3
Check Number         reference4       reference24      reference_4           reference_4
Invoice Number       reference5       reference25      reference_5           reference_5
Category             reference6       reference26      reference_6           reference_6
Set of Books ID      reference7       reference27      reference_7           reference_7
Inv Dist Line Num    reference8       reference28      reference_8           reference_8
Invoice Payment ID   reference9       reference29      reference_9           reference_9
Line Type            reference10      reference30      reference_10          reference_10
------------------------------------------------------------------------------------------

Shell script to load any ldt file

This is a shell script that can load any ldt (FND or ALR standard) file. Just needs DB password and file name parameters and run from the location where the ldt resides.

#
#---------------------------------------------------------
# Shell to Load ldt files
#---------------------------------------------------------
# Parameters
# 1. Apps DB Password
# 2. Loader Data file name ldt
#---------------------------------------------------------
# Syntax
#---------------------------------------------------------
# LOAD_LDT_FILE_SH appsPassword ldt_name
# e.g.
#  LOAD_LDT_FILE_SH appsDB LDT_FILE_TO_LOAD.ldt
#---------------------------------------------------------
#

echo "Current Directory " `pwd`

db_pasw=$1
file_name=$2

file_path=""
prd_name=""
str_len=""
load_cmd=""

echo "============================================================="
is_ldt=`echo $file_name | grep ldt 2>/dev/null | wc -l`

if [ $is_ldt -eq 0 ]
then
  echo "Not Processing File " $file_name 2>/dev/null
  echo "============================================================="
else
  echo "Processing File " $file_name
  echo "============================================================="
  file_path=`grep -i lct $file_name 2>/dev/null | cut -f2 -d '@' | grep -i patch`
  if [ "$file_path" == "" ]
  then
    echo "Not Processing File " $file_name 2>/dev/null
  else
    echo "Start removing OLD log files "
    file_name_arr=( `grep -nicH $file_name *.log | grep -v ":0" | cut -f1 -d ':'` )
    file_count=${#file_name_arr[@]};

    for  ((  f = 0 ;  f < $file_count;  f++  ))
    do
       log_file_name=${file_name_arr[$f]}
       echo "Removing log file "$log_file_name
       rm $log_file_name
    done
    echo "End of removing OLD log files "
    echo "============================================================="
    prd_name=`echo $file_path | cut -f1 -d ':'`

    if [ "$prd_name" == "FND" ]
    then
       prd_name=`echo $FND_TOP`
    fi
    if [ "$prd_name" == "ALR" ]
    then
       prd_name=`echo $ALR_TOP`
    fi
    ## you can add more to above list as needed
    str_len=`expr index $file_path : + 1`
    file_path=`echo $file_path | cut -c$str_len-`
    ctrl_file=$prd_name"/"$file_path

    echo "Control File " $ctrl_file
    echo "============================================================="
    read_ctrl=`cat $ctrl_file | wc -l`
    if [ $read_ctrl -eq 0 ]
    then
     echo "Issue in control file read"
     echo "============================================================="
    else
      echo "Command to Load is "
      echo "FNDLOAD apps/db_pasw 0 Y UPLOAD "$prd_name"/"$file_path $file_name "CUSTOM_MODE=FORCE"
      echo "============================================================="
      echo "Start Load "
      load_output=`FNDLOAD apps"/"$db_pasw 0 Y UPLOAD $prd_name"/"$file_path $file_name CUSTOM_MODE=FORCE`
      echo "End Load"
      echo "============================================================="

      file_name_arr=( `grep -nicH $file_name *.log | grep -v ":0" | cut -f1 -d ':'` )
      file_count=${#file_name_arr[@]};

      for  ((  f = 0 ;  f < $file_count;  f++  ))
      do
         log_file_name=${file_name_arr[$f]}
         ln_error=`grep -icH error $log_file_name | grep -v ":0" | wc -l `
         if [ $ln_error -eq 0 ]
         then
           echo "Load is SUCCESS"
         else
           echo "Load FAILED"
         fi
         echo "============================================================="
         echo "Log file contents are "
         cat $log_file_name
      done
      echo "============================================================="
    fi
  fi
fi

Totally Dynamic in 10g

Sometimes you need to pull record history data from Apps tables for audit purposes. Usually, say for invoices or bank account setups and transactions. You can write simple SQLs such as

SELECT * 
   FROM ap_bank_branches APBB
  WHERE ( APBB.created_by = :user_id AND TRUNC(APBB.creation_date) 
BETWEEN '01-DEC-2009' 
    AND '31-DEC-2009')
     OR  ( APBB.last_updated_by = :user_id AND TRUNC(APBB.last_update_date) 
BETWEEN '01-DEC-2009' 
    AND '31-DEC-2009')
;

and get it done or you can write a totally dynamic SQL generator such as one below.
This one is truly dynamic. FROM is set at runtime, COLUMN list is set at runtime, WHERE clause is also is set runtime.

lv_table_name := 'AP_BANK_BRANCHES';
      ln_user_id    := 1234;
      ld_start_date := SYSDATE-30;
      ld_end_date   := SYSDATE;

      IF lv_table_name IS NOT NULL
      THEN
        fnd_file.new_line(fnd_file.log,1);
        fnd_file.put_line(fnd_file.log,RPAD('-',1000,'-'));
        fnd_file.put_line(fnd_file.log,'Processing Table Name ' || lv_table_name);
        fnd_file.put_line(fnd_file.output,RPAD('-',1000,'-'));
        fnd_file.put_line(fnd_file.output,'Table Name ' || lv_table_name);
        fnd_file.put_line(fnd_file.output,RPAD('-',1000,'-'));

        lv_qry_state := NULL;
        lv_col_list  := NULL;

        -- 
        -- Get only printable columns
        --
        FOR col_rec IN (SELECT LOWER(column_name) column_name
                          FROM dba_tab_columns
                         WHERE data_type IN ('NUMBER','DATE','VARCHAR','VARCHAR2','CHAR')
                           AND table_name = lv_table_name
                         ORDER BY column_id )
        LOOP
         IF lv_col_list IS NULL THEN
           lv_col_list := col_rec.column_name;
         ELSE
           lv_col_list := lv_col_list ||' ,'||col_rec.column_name;
         END IF;
        END LOOP;

        IF lv_col_list IS NULL THEN
          fnd_file.put_line(fnd_file.log,'Column list NULL for ' ||lv_table_name ||', verify if it is a table');
        ELSE
          lv_table_name := LOWER(TRIM(lv_table_name));
          lv_qry_state :='
 SELECT ' || lv_col_list   ||'
   FROM ' || lv_table_name ||'
  WHERE ( ( created_by      = :u1 AND TRUNC(creation_date)    BETWEEN :d1 AND :d2 )
     OR   ( last_updated_by = :u2 AND TRUNC(last_update_date) BETWEEN :d3 AND :d4 )
        )' ;

          fnd_file.put_line(fnd_file.log,'START ' || TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
          fnd_file.put_line(fnd_file.log,lv_qry_state );

          l_theCursor     := dbms_sql.open_cursor;
          dbms_sql.parse(  l_theCursor,  lv_qry_state, dbms_sql.native );

          dbms_sql.bind_variable(l_theCursor, ':u1', ln_user_id);
          dbms_sql.bind_variable(l_theCursor, ':u2', ln_user_id);

          dbms_sql.bind_variable(l_theCursor, ':d1', ld_start_date);
          dbms_sql.bind_variable(l_theCursor, ':d2', ld_end_date);

          dbms_sql.bind_variable(l_theCursor, ':d3', ld_start_date);
          dbms_sql.bind_variable(l_theCursor, ':d4', ld_end_date);

          dbms_sql.describe_columns2 ( l_theCursor, l_numcols, l_columns );

          FOR i IN 1 .. l_numcols LOOP
            BEGIN
              IF l_columns( l_numcols).col_type IN (1,2,9,12,96)
              THEN
                dbms_sql.define_column( l_theCursor, i,
                                        l_columnValue, 2000 );
                l_colCnt := i;
                fnd_file.put(fnd_file.output, '"'||l_columns( l_colCnt).col_name||'",');
              END IF;
            EXCEPTION
              WHEN OTHERS THEN
                fnd_file.put_line(fnd_file.log,'SQLERRM ' || SQLERRM );
                IF ( SQLCODE = -1007 ) THEN
                  EXIT;
                ELSE
                  RAISE;
                END IF;
            END;
          END LOOP;

          fnd_file.put_line(fnd_file.output, ' ');
          dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                                    2000 );

          l_status := dbms_sql.execute(l_theCursor);
          l_cnt    := 0;
          LOOP
            EXIT WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
            l_cnt := l_cnt+1;
            FOR i in 1 .. l_colCnt LOOP
               /*
                TYPECODE_VARCHAR         PLS_INTEGER :=   1;
                TYPECODE_NUMBER          PLS_INTEGER :=   2;
                TYPECODE_VARCHAR2        PLS_INTEGER :=   9;
                TYPECODE_DATE            PLS_INTEGER :=  12;
                TYPECODE_CHAR            PLS_INTEGER :=  96;
                This condition is redundant here as we have taken care
                of printable columns in the column list generation, but
                good to know what type is what number!
               */
               IF l_columns( l_colCnt).col_type IN (1,2,9,12,96)
               THEN
                 dbms_sql.column_value( l_theCursor, i,
                                        l_columnValue );
                 fnd_file.put( fnd_file.output, '"'||NVL(TRIM(SUBSTR(REPLACE(l_columnValue,'"',''),1,2000)),' ')||'",' );
               ELSE
                 fnd_file.put( fnd_file.output, '"'||' '||'",' );
               END IF;
            END LOOP;
            fnd_file.put_line(fnd_file.output,' ');
          END LOOP;

          dbms_sql.close_cursor(l_theCursor);
          fnd_file.new_line(fnd_file.output,2);
          fnd_file.put_line(fnd_file.log,'Record Count ' || l_cnt);
          fnd_file.put_line(fnd_file.log,'END ' || TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
        END IF; -- col list not null
      END IF; -- table not null

You could make it even more dynamic. Create a lookup that holds the table names and loop through the table names.

I have tried this in 10g and it works. Not sure about 9i though.

Cheers.

How to find Personalizations

You have lots of personalizations in your system done by lots of nerds and there is no documentation. How do you figure out whats in without much efforts? Of course you could use export tool from the Functional Administrator and you would get everything under the sun. But what if you just want to know all the personalizations for ORG 1234? So here you go, here is how you do it.

There are only a handful tables where the XML MDS information is stored, and these are the tables

JDR_PATHS
  JDR_COMPONENTS
  JDR_ATTRIBUTES
  JDR_ATTRIBUTES_TRANS

And there is one absolutely useful package JDR_UTILS.

First step is we got to figure out some path names for this ORG 1234.

1. Execute this one first, we get a path id from this to get a qualified name later on

SQL> SELECT path_docid FROM JDR_PATHS WHERE PATH_NAME = '1234';
===> 12345

2. Get the full qualified path name

SQL> SELECT JDR_MDS_INTERNAL.GETDOCUMENTNAME(12345) P FROM DUAL;
===> /oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234

3. Now that we got the path, we can get individual documents

SQL> SET SERVEROUT ON
SQL> EXEC jdr_utils.listDocuments('/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234');

Printing contents of /oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234
/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234/CashAndOtherLinesPG
/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234/DetailsPG


PL/SQL procedure successfully completed.

SQL>

Bingo! and done.
You can do same stuff for responsibilities, functions etc. Just replace the ORG_ID with the appropriate IDs.

Cheers

Unix/Linux Shell Scripting

This short post is about some tips/tricks in Shell Scripting.

1. Invoke PLSQL/SQL from Shell

Shell START

#!/bin/bash

param_one="$1"
param_two="$2"

if [ ${#1} == 0 ]
then
param_one="null"
fi

if [ ${#2} == 0 ]
then
param_two="null"
fi

echo "one " $param_one
echo "two "$param_two

CHECK_THIS=`sqlplus -s apps/apps@appsDB
<< ! 
set term off 
set head off 
set pages 0 
set feedback off 
BEGIN 
  INSERT_LOG(p_mesg1 => $param_one,p_mesg2=>$param_two);
END;
/
!
`
echo $CHECK_THIS
Shell END

.

2. Print new line in Shell Script

echo $@

.

3. Debug Shell Script

Put below line at the start of the script
#!/bin/bash -x

.

4. Find files, example of search for the string "ERROR" in all xml files in the current directory and all sub-directories

find . -name "*.xml" -exec grep "ERROR" '{}' \; -print

.

5. Find and list files

$find . -name "XXFILENAME" -type f -ls

.

6. More to come..

Extending a VO [View Object] in Oracle Apps

A quick post about how-to extend a view object.

Goal : Add "Created By" and "Creation Date" to HR Salary History Standard Page

1. Find the VO of the table in the page, in this case the VO is SalaryHistoryVO.

2. Get the VO xml and VO class files from application middle tier.
For SalaryHistoryVO, the VO xml is at

$JAVA_TOP/oracle/apps/per/saladmin/server/SalaryHistoryVO.xml

Class files, SalaryHistoryVOImpl.class and SalaryHistoryVORowImpl.class
are at same location as VO.xml

3. Create directory structure under myprojects and myclasses to accommodate this VO

JDEV_HOME\myclasses\oracle\apps\per\saladmin\server 
JDEV_HOME\myprojects\oracle\apps\per\saladmin\server

4. Create a new OA Project in JDEV

5. Create an empty business component package, oracle.apps.per.saladmin.server

6. Add the VO.xml to this package. This is the VO.xml

7. Create a new empty business component package, xxx.oracle.apps.per.saladmin.server which would hold the custom VO.

8. Create a new VO [exSalaryHistoryVO] that extends the SalaryHistoryVO

9. Edit the SQL for this new VO, and include created by and creation date columns in the SQL statement, xml shown below

10. Make sure that attribute mappings remain good after adding new columns.

11. Generate both exSalaryHistoryVOImpl and exSalaryHistoryVORowImpl for this new custom VO

12. Change the exSalaryHistoryVOImpl to import oracle.apps.per.saladmin.server.SalaryHistoryVOImpl and extend from SalaryHistoryVOImpl

13. Compile project.

14. Now build substitution to replace SalaryHistoryVO with exSalaryHistoryVO

Substitution Step 1

Substitution Step 2

15. Save the project, open the project.jpx file and make sure the substitution is valid, jpx view below

16. Now load the new VO and corresponding class files to middle tier at
$JAVA_TOP/xxx/oracle/apps/per/saladmin/server/

17. Load the substitution into database

java oracle.jrad.tools.xml.importer.JPXImporter ExtendVO.jpx  -username apps -password appsDB -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db.server.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) "

18. Once these steps are done, navigate to Salary History Page, and add the new columns at desired places using OA Personalization.

OAF [OA Framework Development] Related Stuff

Short note about development related stuff in OA Framework.

1. Find correct JDeveloper Patch for your application

1. Patch 4045639 - 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
2. Patch 4141787 - 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
3. Patch 4573517 - Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
4. Patch 4725670 - 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3

To determine which patch to use, you can simply check the framework 
version in your instance by using http://host:port/OA_HTML/OAInfo.jsp, 
then choose the matched one. If the version is 11.5.10K, please use 
patch 4045639, 11.5.101CU, then use patch 4141787, 11.5.102CU, then 
it should be patch 4573517, 11.5.103CU should be patch 4725670.

OA FWK Dev Guide
https://metalink.oracle.com/metalink/plsql/docs/269138.1.pdf

Search "JDeveloper" on this blog for more posts.

2. JDeveloper Debug Options

-Djbo.debugoutput=silent    --> No Debug
-Djbo.debugoutput=console   --> Debug to Console

.
3. Importing Certificates into JDev, needed if you are connecting to https URLs

C:\JDEV\jdevbin\jdk1.3.1\jre\bin\keytool -import -v -file ecustom.cer -keystore cacerts
Enter keystore password:  changeit
Owner: CN=certification.owner.com
Issuer: CN=certification.owner.com
Serial number: blahblahblahblahblahblahblahblah
Valid from: Mon Oct 06 15:41:50 PDT 2008 until: Thu Oct 04 15:41:50 PDT 2018
Certificate fingerprints:
MD5:  blahblah
SHA1: blahblah
Trust this certificate? [no]:  yes
Certificate was added to keystore

.
4. Import OAF XML Page

java oracle.jrad.tools.xml.importer.XMLImporter OIEAPPROVERLOV.xml -jdk13 -mmddir "$OA_HTML/jrad" -rootdir . -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server.db.com)(PORT=9999))(CONNECT_DATA=(SID=SID)))"

.
5. OA Page Export

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/icx/icatalog/shopping/webui/NonCatalogRequestPG -username apps -password apps -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db.server.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " -rootdir .

.
6. OA Page Personalization Export

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/icx/icatalog/shopping/webui/customizations/site/0/NonCatalogRequestPG -username apps -password apps -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db.server.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " -rootdir .

.
7. List Customizations for OA Page

begin
  jdr_utils.listDocuments('1.xml');
  jdr_utils.LISTCUSTOMIZATIONS('/oracle/apps/icx/icatalog/shopping/webui/ShoppingHomePG');
end;

.
8. OA Page Personalization Import

adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter ./oracle/apps/ap/oie/entry/lines/webui/customizations/site/0/DetailsPG.xml -username apps -password apps -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db.server.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " -rootdir .

.
9. OA Page Debug (Location for SOPLs (System.out.println Statements))

Location
  $ORACLE_HOME/../9iAS/Apache/Jserv/logs/jvm
  Check OACoreGroup.n.stdout [n can be 0,1,2,3 etc]
  Check OACoreGroup.n.stderr [n can be 0,1,2,3 etc]

.
10. Stop/Start Apache Port

1.
cd $COMMON_TOP/admin/scripts
2.
./adapcctl.sh stop
3. Check if http processes still running, if running kill those, then proceed for start
ps -aef | grep -i http | grep -i DBNAME
4. 
./adapcctl.sh start

.
11. Find Java file versions

cd $JAVA_TOP/oracle/apps/icx

1st Way
Execute the following at the unix prompt - from this directory -
 for FILE in `find . -name '*.class' -print`
 do
   echo $FILE
   strings -a $FILE | grep -i header: | cut -c1-78
 done > $HOME/javaver.txt

2nd Way (assuming only one type of files)
XML Versions
 find . -exec grep -i 'Header' {} \; -print > $HOME/xmlver.txt

.

Re-try workflow

If you ship newer version of a PLSQL code which gets invoked from Workflow, and you forget to bounce Workflow Services, then all workflows using this PLSQL code would result in Invalid Package Error.

You can use following sample script to retry such error items.

BEGIN
   fnd_global.apps_initialize (0,0,0); -- Set correct values here
   FOR get_item_rec IN ( SELECT item_type
                               ,item_key
                           FROM apps.wf_item_activity_statuses wia
                          WHERE item_type          = 'REQAPPRV'
                            AND activity_status    = 'ERROR'
                            AND error_message like '%PACK%INVALID%'
                            AND activity_result_code <> '#EXCEPTION'
                            AND begin_date > SYSDATE-15
                            AND end_date IS NULL
                       )
   LOOP
      wf_engine.handleerror (get_item_rec.item_type
                            ,get_item_rec.item_key
                            ,'PO_REQ_APPROVE_SIMPLE'
                            ,'RETRY');
   END LOOP;
   COMMIT;
END;
/

.

With 11510, Application Manager UI facilitates retrying multiple workflow error items in one shot.

Cheers.

Importing Expense Report Attachments into Payables

Is there any way to get expense report attachments into Payables?

Yes there is. You can use standard Oracle Apps routine to copy attachments from one entity to another. You can copy attachments from ER header to Invoice header, ER line to Invoice Dist or copy all attachments from ER header/lines to AP invoice headers. You can also control which type of attachments should be copied.

Sample code below copies header level expense report attachments to the AP Invoice.

BEGIN
 fnd_attached_documents2_pkg.copy_attachments(
  x_from_entity_name  => 'OIE_HEADER_ATTACHMENTS' -- From Expense Report Header
 ,x_from_pk1_value    => '115000'                 -- From Expense Report Header Id
 ,x_to_entity_name    => 'AP_INVOICES'            -- To AP Invoice Header
 ,x_to_pk1_value      => '1085333'                -- To Invoice Id
 ,x_to_category_id    => 1                        -- Category Id for Attachments
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('SQLERR ' || SQLERRM);
 ROLLBACK;
END;

.

Cheers

Who let the dogs out ....when, and for how long..?

.

Okay, thats a catchy title for post, simple SQL to find out who ran the concurrent program and when.

SELECT FCON.concurrent_program_name
      ,FCON.user_concurrent_program_name
      ,FREQ.request_id
      ,FREQ.requested_by
      ,(SELECT FUSE.user_name||' ['||FUSE.description||']'
          FROM fnd_user FUSE 
         WHERE FUSE.user_id = FREQ.requested_by) user_name
      ,TO_CHAR(FREQ.requested_start_date,'DD-MON-RRRR HH24:MI:SS')    req_start_dt
      ,TO_CHAR(FREQ.actual_start_date,'DD-MON-RRRR HH24:MI:SS')       act_start_dt
      ,TO_CHAR(FREQ.actual_completion_date,'DD-MON-RRRR HH24:MI:SS')  act_compl_dt
      ,FREQ.argument_text
  FROM fnd_concurrent_requests FREQ
      ,fnd_concurrent_programs_vl FCON
 WHERE FCON.concurrent_program_id   = FREQ.concurrent_program_id
   AND FCON.concurrent_program_name = 'CONC_PROGRAM_NAME'
 ORDER BY FREQ.request_id DESC

.
Cheers

Find current running SQL for Concurrent Program

This is often times useful, to find out current running SQL of a concurrent program. Helps a lot, if you are analyzing a long running process.

Here is the SQL

SELECT C.sql_text
      ,C.module 
  FROM APPS.fnd_concurrent_requests A
      ,V$SESSION B
      ,V$SQLAREA C 
 WHERE A.oracle_session_id = B.audsid 
   AND B.sql_hash_value    = C.hash_value 
   AND A.request_id        = 6935896

.
Here is another one

SELECT SQLT.hash_value
      ,SQLT.sql_text
      ,VSES.username
      ,VSES.module
      ,VSES.command
  FROM v$sqltext                    SQLT
      ,v$session                    VSES
      ,APPS.fnd_concurrent_requests FCONC
 WHERE SQLT.hash_value         = VSES.sql_hash_value 
   AND FCONC.oracle_session_id = VSES.audsid 
   AND FCONC.request_id        = 6935896
ORDER BY SQLT.piece

.

Cheers

FNDLOAD Post

Ok, I have been avoiding a FNDLOAD only post, but could not resist.

Here are few FNDLOADs that I frequently use.

.
Messages
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct igfapmsg.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=IGF message_name='IGF_AP%'

Concurrent Programs
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct igfcprog.ldt PROGRAM APPLICATION_SHORT_NAME=IGF

Lookups
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct  igflkups.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=IGF

Menus
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct  igsmenus.ldt MENU MENU_NAME=IGS_% APPLICATION_SHORT_NAME=IGS

Descriptive Flex
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct igfdflex.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME='IGF%'

Request Group
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct igfreqgr.ldt REQUEST_GROUP APPLICATION_SHORT_NAME=IGF

Profiles
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct igfprofl.ldt PROFILE APPLICATION_SHORT_NAME=IGF

Attachments
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct igsattch.ldt FND_ATTACHMENT_FUNCTIONS APPLICATION_SHORT_NAME=IGS

Responsibility
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct igsresp.ldt  FND_RESPONSIBILITY APPLICATION_SHORT_NAME=IGS

Request Set
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct REQSET.ldt REQ_SET_LINKS APPLICATION_SHORT_NAME=GL REQUEST_SET_NAME=FNDRSSUB963

Forms Personalizations
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FORMS_PERZ.ldt FND_FORM_CUSTOM_RULES function_name=POXPOEPO

Alerts
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XALERTMIG.ldt ALR_ALERTS APPLICATION_SHORT_NAME=SQLAP ALERT_NAME=AlterName%

Printer-Style-Driver
FNDLOAD apps/appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppinf.lct PRINTER_INFO.ldt FND_PRINTER_INFORMATION PRINTER_TYPE=EFT_PRINTER PRINTER_STYLE=EFT%

Only value sets
FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct test_2.ldt VALUE_SET FLEX_VALUE_SET_NAME=IGF_SRS_AWARD_YEAR

FNDLOAD apps/apps@appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct igfdfvsv.ldt VALUE_SET FLEX_VALUE_SET_NAME='IGF%' APPLICATION_SHORT_NAME=IGF

Partial UPLOAD Examples

Partial Value sets
FNDLOAD apps/apps@appsDB 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct igfcprog.ldt VALUE_SET CUSTOM_MODE=FORCE

Params
FNDLOAD apps/apps@appsDB 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct igfcprog.ldt DESC_FLEX CUSTOM_MODE=FORCE

Descriptive Flex - Specific Context Code/Structure
FNDLOAD apps/appsDB 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct test_flex.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS' APPLICATION_SHORT_NAME='FND' DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE='DFF Context Code%'

.
Cheers

Frequently Used Metalink Notes

.
I would keep this page live.

Note 233205.1 - How To Change The Default Reimbursement Currency For Employee in iExpense
Note 160058.1 - Ext/Pub Customize Pages in iProcurement Creating Descriptive Flexfields (DFF)
Note 286936.1 - POA: DBI Setup Steps for Procurement Intelligence Note 315978.1
Note 316829.1 - No Data Found In Procurement DBI Reports: Missing Security Setup 
Note 286841.1 - POA: Frequently Asked Questions (FAQ) for DBI for Procurement.
Note 286936.1 - POA: DBI Setup Steps for Procurement Intelligence
Note 315978.1 - How To Find Out What DBI and/or OBIS Version Is Applied Into System
Note 316829.1 - No Data Found In Procurement DBI Reports: Missing Security Setup
Note 377320.1 - How to Diagnose a Punchout Issue
Note 555151.1 - How To Diagnose Document Index (Procurement)
Note 290614.1 - How To Get Log Files In iProcurement
Note 343799.1 - Punchout To Oracle Exchange Or Supplier Site Recieve Error Code: 201
Note 356128.1 - How to Run the Punchout TEST Delivered With Oracle Applications?
Note 377320.1 - How to Diagnose a Punchout Issue
Note 249669.1 - How To Collect Apache and Jserv Debugging Details For Applications 11i
Note 293315.1 - 11.5.10 FAQ for Approvals Management (AME) Integration For iProcurement and Purchasing:
Note 402870.1 - How To Diagnose Issues With The Edit Requisition Link In Notifications
Note 429918.1 - How To Remove The "To Supplier" From Requisition Attachment in iProcurement?
Note 1077961.6 - POXPOEPO Fully Receiving And Billing Against The Line Does Not Close PO
Note 105541.1 - Oracle General Ledger 11i Drilldown Features FAQ
Note 108369.1 - GL Drilldown
Note 208756.1 - GL Drilldown
Note 428228.1 - Checks To See If PL/SQL or JRAD Notifications Are Being Used in 11.5.10 Notifications
Note 374339.1 - Notifications Show Error When Clicking Edit Requisition Link
Note 455687.1 - How Do You Set Up A Test Periodic Alert That Sends An Email
Note 406563.1 - Enddated Responsibility Duplicates And Is Still Available
Note 421778.1 - Responsibilities Not Showing Up According to the End Date In List of Available Responsibilities
Note 134168.1 - iProcurement Setup 11i: Oracle Internet Procurement Functional Setup
Note 290614.1 - How To Get Log Files In iProcurement
Note 382592.1 - How To Diagnose A Problem In The Item Catalog
Note 160058.1 - Ext/Pub Customize Pages in iProcurement Creating Descriptive Flexfields (DFF)
Note 339561.1 - In iProcurement When Selecting 'Requisition Status' - 'Notifications' Receive Java Error (Corrupted Personalizations)
Note 340078.1 - How To Call a PL/SQL Stored Procedure from an Application Module and Access the Returned Data from a Java Application
Note 279202.1 - SSLInitFailureException Occurs While Attempting To Download Supplier Punchout Definition
Note 389596.1 - How To Disable SOAP In Applications 11i

Custom Lookups vs Profile Options

Well, this is a no-contest.

Lookups win over Profile Options whichever way you look at. If you want custom decision points in the application, lookups offer much more flexibility than Profile Options.

You can configure any flex-structure around lookups and make it much more versatile. And best part is there is no need of any bounce or cache clearing issue with the lookups.

Of course, when it comes to User controlled options, then there is no alternative to Profiles Options.

Cheers.

Send workflow notification without statring workflow

You can send workflow notifications without starting the actual workflow. Here is how
.

lv_key := TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
ln_notification_id := wf_notification.send (lv_role_name
                                           ,'WF_ITEM_NAME'
                                           ,'WF_NOTIFICATION_NAME'
                                           ,SYSDATE+3
                                           ,'WF_ENGINE.CB'
                                           ,'WF_ITEM_NAME:'||lv_key||':'||TO_CHAR(SYSDATE, 'JSS')
                                           ,NULL
                                           ,NULL );

wf_notification.setattrtext (ln_notification_id,'SUBJECT',lv_subject_txt);
wf_notification.setattrtext (ln_notification_id,'BODY',lv_body_txt);

UPDATE wf_notifications
   SET from_user       = 'System'
      ,from_role       = 'SYSADMIN'
 WHERE notification_id = ln_notification_id;

.
Note the item key synatx.

Cheers.

Workfllow adhoc Role Expiry

I created one adhoc role using wf_directory.createadhocrole and set its expiry to 15 days. Then on 16th day, I tried to create the same role, but the routine failed, cause was "Duplicate Role Name".

I checked WF_ROLES, and I did not find the expired role there. Then I checked WF_LOCAL_ROLES, the role end date was in past, but the role status was still active.

To avoid error, I added following before creating new adhoc role.

 wf_directory.setadhocrolestatus('ADROLE' , 'INACTIVE');
 wf_directory.deleterole('ADROLE' , 'WF_LOCAL_ROLES' , 0);

.

This makes sure the old role is deleted before creating a new one with same name.

Expesne Reports to Invoice - Line Justification

When payables imports expense reports into the invoice, expense report line justification is not carried over. A simple trigger on invoice distribution could solve the problem.

Here is the trigger, note the difference between 1159 and 11510 versions
.

CREATE OR REPLACE TRIGGER copy_ap_expense_rpt_just
--
-- Trigger on Expense Lines table to copy Justification to Item Description 
--
  BEFORE INSERT ON ap_invoice_distributions_all
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  lv_justification VARCHAR2(240);
BEGIN
    --
    -- For 1159
    --
    IF INSERTING THEN
      SELECT EXPRL.justification
        INTO lv_justification
        FROM ap_expense_report_lines_all   EXPRL
            ,ap_expense_report_headers_all EXPRH
            ,ap_invoices_all               APINV
       WHERE EXPRL.report_header_id    = EXPRH.report_header_id
         AND EXPRH.invoice_num         = APINV.invoice_num
         AND APINV.invoice_id          = :NEW.invoice_id
         AND EXPRL.code_combination_id = :NEW.dist_code_combination_id
         AND EXPRL.item_description    = :NEW.description
         AND EXPRL.start_expense_date  = :NEW.start_expense_date
         AND EXPRL.amount              = :NEW.amount;

         :NEW.description := lv_justification;
    END IF;
    --
    -- For 11510
    --
    IF INSERTING THEN
      SELECT EXPRL.justification
        INTO lv_justification
        FROM ap_expense_report_lines_all   EXPRL
            ,ap_expense_report_headers_all EXPRH
            ,ap_invoices_all               APINV
            ,ap_exp_report_dists_all       EXPDIST
       WHERE EXPRL.report_header_id      = EXPRH.report_header_id
         AND EXPRH.invoice_num           = APINV.invoice_num
         AND EXPRL.report_line_id        = EXPDIST.report_line_id
         AND APINV.invoice_id            = :NEW.invoice_id
         AND EXPDIST.code_combination_id = :NEW.dist_code_combination_id
         AND EXPRL.item_description      = :NEW.description
         AND EXPRL.start_expense_date    = :NEW.start_expense_date
         AND EXPRL.amount                = :NEW.amount;

      :NEW.description := lv_justification;
    END IF;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
     NULL; -- Do nothing as this is not a ER Invoice
   WHEN OTHERS THEN
     fnd_log.string(fnd_log.level_exception
                   ,'copy_ap_expense_rpt_just'
                   ,'Exception ' ||SQLERRM);   
END copy_ap_expense_rpt_just;

Some thoughts for Cloning from Production Enviornment

.
Based on my experience, here is what comes to my mind, a MUST have list for cloning Apps Production to test environment apart from regular data scrambling. Note that this is purely from developer perspective.

1. Wipe out all workflow notifications, if you do not want to do it for some 
   reason, at least set the mail status to SENT for ALL workflow notifications.
2. Set a test address for workflow mailer
3. Disable all ALERTS
4. Disable all FTP programs
5. Disable all 3rd party mailing programs
6. Make sure the workflow FROM e-mail address has some sort of environment 
   identification in it.
7. For alerts, you can add a test disclaimer in alert setup.
8. Disable all scheduled application specific programs
9. Remove all Database Links, or point to corresponding test instance

.
Gee..this is really small list, BUT really important. I would be adding more as I learn more.

XML to CSV

Often there is a need to load XML data into database. With the popularity of the Web Services and Java technology, you get lots of these type of requests. Ideally XML is not really good for high volume data.

I had used in past a DOM parser to parse and load XML data into database. It was awfully slow. First a DOM parser need huge memory for bigger files, second the parsing itself is painfully slower. Third, if you are doing a record-by-record insert from XML into database through Java, the process is as good as dead for any data length over 10000 records.

So, I thought of converting XML into a CSV and load that CSV into database using SQL*Loader. This way the process is 1000x times faster. I put together a small java code that uses SAX parser and produces a csv.

Here is the syntax to invoke Java parser

.
java GenericSaxParseToCsv "Feed.xml" "COLUMN_1,COLUMN_2,COLUMN_3,COLUMN_4,COLUMN_5,COLUMN_6,COLUMN_7,COLUMN_8,COLUMN_8,COLUMN_9,COLUMN_10" "123456" "2" > Feed.csv

1. Feed.xml is in the input file
2. COLUMN_1,COLUMN_2,COLUMN_3,COLUMN_4,COLUMN_5,COLUMN_6,COLUMN_7,COLUMN_8,COLUMN_8,COLUMN_9,COLUMN_10
   are the columns in the XML, they would appear in the same sequence in the 
   outputted csv as passed here
3. 123456 - is request id for the whole set
4. 2 
   is number of levels in XML, basically this would control when you want to 
   start your new record in CSV. 
   Passing 2 would create new record in CSV when XML parser encounters COLUMN_1 or 
   COLUMN_2 is any sequence.

And here is the Java Parser
.

//
//    Generic SAX Parser
//    Source http://appswork.blogspot.com/



import java.io.Writer;
import java.io.OutputStreamWriter;
import java.io.File;
import java.io.IOException;

import org.xml.sax.AttributeList;
import org.xml.sax.HandlerBase;
import org.xml.sax.SAXException;

import javax.xml.parsers.SAXParserFactory;
import javax.xml.parsers.SAXParser;

import java.util.StringTokenizer;

public class GenericSaxParseToCsv extends HandlerBase
{

    protected static boolean showVal  = false;
    protected static int nCurrCounter = 1;
    protected static int nLineCounter = 0;
    protected static int nRequestId   = 0;
    protected static int nLevel       = 1;
    protected static int nColumnCount = 0;
    protected static int nCurrentColPos = -1;
    protected static String vDetLevel;

    static private Writer wOut;

    protected static String vColumnHeaders;
    protected static String vColNameValP[][];
    protected static StringTokenizer vColNamesToken;
    protected static String vInputFile;

    public static void main (String argv [])
    {
      // Use the default (non-validating) parser

      vInputFile           = argv[0];
      vColumnHeaders       = argv[1];
      
      try
      {
        nRequestId         = Integer.parseInt(argv[2]);
      }
      catch(Exception e)
      {
      }

      try
      {
        nLevel             = Integer.parseInt(argv[3]);
      }
      catch(Exception e)
      {
      }

      vColNamesToken = new StringTokenizer(vColumnHeaders,",");
      vColNameValP   = new String [2] [vColNamesToken.countTokens()];

      int i = 0;
      while(vColNamesToken.hasMoreTokens())
      {
        vColNameValP[0][i] = vColNamesToken.nextToken().trim();
        vColNameValP[1][i] = "";
        i = i + 1;
      }

      nColumnCount = i;

      SAXParserFactory factory = SAXParserFactory.newInstance();
      try {
          // Set up output stream
          wOut = new OutputStreamWriter (System.out, "UTF8");

          // Parse the input
          SAXParser saxParser = factory.newSAXParser();
          saxParser.parse( new File(vInputFile), new GenericSaxParseToCSV() );

        } catch (Throwable t) {
            t.printStackTrace ();
        }
        System.exit (0);
    }


    public void startDocument ()
    throws SAXException
    {
        showData("Request_ID,"+"LineCounter,"+"DetLevel,"+vColumnHeaders);
        newLine();
        showData("~"+nRequestId+"~"+","+"~"+"STARTREQUEST"+"~"+","+"~"+vInputFile+"~");
        newLine();
        showData("~"+nRequestId+"~"+","+"~"+nLineCounter+"~"+","+"~"+"L0"+"~"+",");
    }

    public void endDocument ()
    throws SAXException
    {
        try {
             printColValues();
             newLine();
             showData("~"+nRequestId+"~"+","+"~"+"ENDREQUEST"+"~"+","+"~"+vInputFile+"~");
             newLine();
             wOut.flush ();
        } catch (IOException e) {
            throw new SAXException ("I/O error", e);
        }
    }

    public void startElement (String name, AttributeList attrs)
    throws SAXException
    {
        for (int i =0; i nCurrCounter)
         {
            printColValues();
            newLine();
            showData("~"+nRequestId+"~"+","+"~"+nLineCounter+"~"+","+"~"+vDetLevel+"~"+",");
            nCurrCounter = nLineCounter;
         }

        if (showVal && !((System.getProperty("line.separator")).equals(s)) && !("\t".equals(s)) && !("\n".equals(s)))
        {
          int ascVal  = s.charAt(0);
          if (ascVal != 10)
            if (nCurrentColPos > -1 )
            {
              vColNameValP[1][nCurrentColPos] = vColNameValP[1][nCurrentColPos] + s;
            }
        }
    }

    private void showData (String s)
    throws SAXException
    {
        try {
            wOut.write (s);
            wOut.flush ();
        } catch (IOException e) {
            throw new SAXException ("I/O error", e);
        }
    }

    // Start a new line
    private void newLine ()
    throws SAXException
    {
        String lineEnd =  System.getProperty("line.separator");
        try {
            wOut.write (lineEnd);
        } catch (IOException e) {
            throw new SAXException ("I/O error", e);
        }
    }
 } // class

.

This java parser would work real cool for simple XML formats for any level of nested elements. It would expect, though non-repeating unique element tags in the XML. And it would not work with XML that has id attributes inside elements.

Cheers.
.

ORA-06502: PL/SQL: numeric or value error: host bind array too small

I was running anon PLSQL block on SQL prompt, and it was printing a large varchar2(32767) variable. And then I got this error. The data length in the varchar2 variable was just 1200, and it was throwing this error.

Turned out the issue is the with the DBMS_OUTPUT.PUT_LINE, it wont accept more than 200 chars to print. So if you have a larger data to be printed using DBMS_OUTPUT.PUT_LINE, then break it down and print 200 chars at a time.

You can use following outputter to break down big variables.

PROCEDURE print_big_text(bigTextInput VARCHAR2)
  IS
    ln_maxChar NUMBER := 200;
    ln_schar   NUMBER;
    bigText    VARCHAR2(32767) := bigTextInput;
  BEGIN
    LOOP
      EXIT WHEN bigText IS NULL;
      ln_schar := INSTR(bigText, ' ', ln_maxChar);
      -- Find first space
      IF ln_schar = 0 THEN
        ln_schar := ln_maxChar + 1;
      END IF;
      -- If no space, just print first 200 
      dbms_output.put_line( SUBSTR(bigText, 1, ln_schar - 1) );
      -- Move text by ln_schar ahead
      bigText := SUBSTR(bigText, ln_schar);
    END LOOP;
  END print_big_text;

Combinations Algo

I wanted to achieve following, produce data under OUT for a given IN.

------------------------------------
IN => OUT
------------------------------------
1  => 1
2  => 1,2, 1+2
3  => 1,2,3, 1+2,1+3,2+3, 1+2+3
------------------------------------

.

This algo has its uses when doing matching sets. For example, you have a set D, and there are three details A, B and C. D could be matched with details in any one way

1. A
2. B
3. C
4. AB
5. BC
6. AC
7. ABC

.
With 10g, achieving this is super-easy. Just one SQL would get you all possible combinations.

SELECT SYS_CONNECT_BY_PATH ( GT.seq_number, ',') col_val
   FROM (SELECT ROWNUM seq_number
           FROM all_objects 
          WHERE ROWNUM <= &p_num_dtls ) GT 
CONNECT BY TO_NUMBER(GT.seq_number) > PRIOR TO_NUMBER(GT.seq_number)

If you need to separate out each element at each level, you can use delimstring_to_table function, search on this blog and you would find it.

Cheers.

XML Publisher Burst - Conditional Emails and Templates

Burst control is much flexible than I thought it was. Here is what I did

There are two templates for same document, and controlled by the filter. Filter specifies two conditions, one if to send email, and second to specify region(or OU); email would be sent only if both conditions are true.

There is one interesting thing here, if I give type as "rtf", the publisher would always pick up the default template. Giving type as "XSL-FO" makes sure you get right template everytime.

Enjoy!

bing,Yahoo! and Google Search Results for my blog

bing, Yahoo! and Google Search Results for my blog

Pictures worth thousand words...

One more ...

So what exactly I have to do, to get bing and Yahoo! to get to my blog?

XML Publisher - Templates/Sample/Control Migration

Short post about command line syntax to migrate XML Publisher Template, Data Sample and Burst Control.


1. Template Migration

 java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=server.db.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME PO \
-LOB_CODE XML_PUB_TEMPLATE_CODE \
-LANGUAGE en \
-TERRITORY CA \
-XDO_FILE_TYPE RTF \
-NLS_LANG UTF8 \
-FILE_NAME XML_PUB_OUTPUT_TEMPLATE.rtf \
-CUSTOM_MODE FORCE

2. Data Sample Migration

 java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=server.db.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " \
-LOB_TYPE XML_SAMPLE \
-APPS_SHORT_NAME PO \
-LOB_CODE XML_PUB_TEMPLATE_CODE \
-LANGUAGE en \
-TERRITORY CA \
-XDO_FILE_TYPE XSL-XML \
-NLS_LANG UTF8 \
-FILE_NAME PO_XML_SAMPLE_DATA.xml \
-CUSTOM_MODE FORCE

3. Burst Control Migration

 java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=server.db.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " \
-LOB_TYPE BURSTING_FILE \
-APPS_SHORT_NAME PO \
-LOB_CODE XML_PUB_TEMPLATE_CODE \
-LANGUAGE en \
-TERRITORY CA \
-XDO_FILE_TYPE XML-BURSTING-FILE \
-NLS_LANG UTF8 \
-FILE_NAME XML_PUB_BURST_CTRL.xml \
-CUSTOM_MODE FORCE

XML Publisher - eMail Format

You would find lots of blogs about how to create and mail PDF using XML Publisher. So I do not want to repeat it. Just want to talk about how to format the e-mail that is sent out. If you have tried to format using HTML tags only, you would have noticed that they are removed when the email is sent out. What you need to do is include the format in CDATA section. Take a look at this

doing this would preserve the HTML formatting. This is how the e-mail would like
--------------------------------------------------------------------------------------
Dear
Please find attached Supplier Report

Regards
XML Publisher Burster
--------------------------------------------------------------------------------------

Cheers

About Me

I am working in IT industry since million years. I started working with world's biggest Oracle Applications company, you guessed right, Oracle. I had been with Oracle for real long time, worked in Product development.Working at Oracle is a great learning experience. During my tenure at Oracle, I worked in different roles, from Application Developer to Project Lead. I had authored numerous Product Requirements documents, Functional Designs, Technical Designs. I had created countless objects (Tables, Sequences, Views, Synonyms, Forms, Reports, OA Pages, Workflows, Business Events, Concurrent Programs, Patches etc etc) and have seen major Apps Release cycles during the entire tenure. I can definitely say I had great time working with Oracle.

And now I work outside Oracle supporting the very products that I was involved in building. My work currently is in Procure-to-Pay side in core modules as well as i-modules (iExpense, iProcurement). It is both fun and great learning to see how the products that one is involved creating in are perceived and used by the application users.

Most of the blog entries that you find on my blog are based on my work at different times. You are free to use the ideas, code presented on my blog as long as you point to this blog.

If they work for you, I would be happy if you leave a note here.

Cheers

Interesting IN clause

This is cool use of IN clause.

SQL>

SQL> CREATE TABLE t (a NUMBER, b NUMBER, c NUMBER, d NUMBER);

Table created.

SQL> DESC t
 Name                            Null?    Type
 ------------------------------- -------- ----
 A                                        NUMBER
 B                                        NUMBER
 C                                        NUMBER
 D                                        NUMBER

SQL> INSERT INTO t VALUES (1,2,3,4);

1 row created.

SQL> INSERT INTO t VALUES (5,6,7,8);

1 row created.

SQL> INSERT INTO t VALUES (1,4,5,6);

1 row created.

SQL> INSERT INTO t VALUES (1,6,7,8);

1 row created.

SQL> SELECT * FROM t WHERE 1 IN (a,b,c,d);

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4
         1          4          5          6
         1          6          7          8

SQL> SELECT * FROM t WHERE 3 IN (a,b,c,d);

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4

SQL> SELECT * FROM t WHERE 5 IN (a,b,c,d);

         A          B          C          D
---------- ---------- ---------- ----------
         5          6          7          8
         1          4          5          6

SQL>

Deferred Approvals for PO/Req

Ever faced this issue?

Approver responds to approval notification and still the document(Requisition or Purchase Order) shows status "In Process"? And the recent approval not recorded?

When this issue shows, chances are that the approver is a "Delegated Approver" means not the original approver. In this case for Delegated Approvers, to avoid context issues, the workflow is set to "Deferred" mode. You have to run Workflow Background Process to move the deferred workflow to next stage.

Refer Metalink note 387489.1 - Requisition Approval In Deferred Status If Notification Is Delegated for more.

External File Upload into Apps

There are indeed a lot of programs/processes that need some external file as input. There are different ways to make this file available in Oracle Apps environment.

1. You can use some FTP UIs like SFTP, which users can login to server and post the files. You can control the folders to which what users have access to. There are obvious drawbacks to this. You need to maintain separate server accounts for users, maintain those and for users they have one more login/password information to track. Again you would need to create the same accounts on test servers also.

2. You can write simple JSP page and expose this JSP function to users

3. Or you can write OA Framework File Upload Page. I feel this approach is simpler.

Let us see how we can do file upload using Framework (11510)
All you need to do is use messageFileUpload bean, see the page XML.




Next is the controller where you do the actual reading and writing of the file.
processFormRequest is where the logic would reside.

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
  super.processFormRequest(pageContext, webBean);
  String event = (String) pageContext.getParameter(EVENT_PARAM);
  OAApplicationModule AM = (OAApplicationModule) pageContext.getApplicationModule(webBean);
  DataObject fileUploadData = (DataObject)pageContext.getNamedDataObject("fileUpload");
  String productName  = pageContext.getParameter("productName");
  String applcsfPath  = pageContext.getProfile("APPLCSF_ABS_PATH");

  /*this is the profile where you can put the actual physical location of the folder
    where you intend to store the files. This is more like a basepath. you can have this
    basepath in the profile and then for each upload function, you can pass different arguments
    say for sub-floders etc.*/

  if ("uploadFile".equals(event))
  {
    if (fileUploadData == null)
    {
      throw new OAException("APP", "NO_FILE_TO_LOAD");
    }

    String uFileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
    BlobDomain byteStream = (BlobDomain)fileUploadData.selectValue(null, uFileName);

    try
    {

      String filePath = applcsfPath+"/";
      System.out.println("filePath  : " + filePath);
      System.out.println("uFileName : " + uFileName);

      FileOutputStream fileOp = new FileOutputStream(filePath+uFileName);
      fileOp.write(byteStream.getBytes(0,(int)byteStream.getLength()));
      fileOp.close();

      fileUploadData = null;
      pageContext.removeNamedDataObject("fileUpload");       

      OAException confirmMessage = new OAException("APP", "FILE_UPLOAD_SUCCESS", null,
                                                   OAException.CONFIRMATION, null);
      pageContext.putDialogMessage(confirmMessage);
   }
   catch(Exception e)
   {
     System.out.println("File Upload Error " + e.toString());
     e.printStackTrace();
     throw new OAException(e.getMessage(), OAException.ERROR);
   }
  }
}

That is all to upload a file, simple and straightforward. You would need following imports apart from the regular ones.

import oracle.cabo.ui.data.DataObject;
import oracle.jbo.domain.BlobDomain;
import java.io.FileOutputStream;

Now, let us say we want to build a download function. There is a download bean, but more about it later. You can expose specific files in httpd.conf making aliases for folders, but this approach does not provide much security.

Here is what I think should be done for download, I have not tried these steps yet, would do that sometime later.

First you need to browse through folder and generate a file-list. Java would be slower to browse if your folder has huge number of files. Better approach is to use a simple shell script that creates a flat file, it can be a one line code such as

grep -ir -v -l 'cantfindthis' $APPLCSF > 1.txt

Then in java code, you can loop through the file (1.txt) and insert the data into a Global Temp table[or the grep output could be redirected directly to java which you can loop through] . Inserting this file list into table provides a good means to have search UI. Next is build search UI and a results tables. Now comes the actual download part. You can add a single-select and a button or link the filename to a submit action. This submit action should can throw open a new page and the file can be streamed out.
Following code snippet is for streaming out from postFormsRequest..
.

  void  throwZip(String pDocList, String pFileName,OAPageContext pageContext)
  {
      //System.out.println("Throwing Docs!");
      URLConnection connURL            = null;
      URL url                          = null;
      InputStream inStream             = null;
      HttpServletResponse httpResponse = null;
      ZipOutputStream outStream        = null;
      DataObject sessionDictionary     = pageContext.getNamedDataObject("_SessionParameters");
      StringTokenizer stURLToken = new StringTokenizer(pDocList,",");
      int urlCount = stURLToken.countTokens();
      int i = 0;
      boolean closeConnection = false;

      while(stURLToken.hasMoreTokens())
      {
        i = i + 1;
        String stURL          = stURLToken.nextToken();
        closeConnection = false;
        try
        {
          if (i == 1)
          {
            httpResponse = (HttpServletResponse)sessionDictionary.selectValue(null, "HttpServletResponse");
            httpResponse.setContentType("application/x-zip-compressed");
            httpResponse.setHeader("Content-Disposition", "Attachment; Filename=" + pFileName);
            outStream = new ZipOutputStream(httpResponse.getOutputStream());
          }
          url  = new URL(stURL);
          connURL  = url.openConnection();
          inStream = connURL.getInputStream();
          outStream.putNextEntry(new ZipEntry(stURL.substring(stURL.lastIndexOf("/"))));
          byte bytes[]                     = new byte[1024];
          for(int bytesRead = inStream.read(bytes); bytesRead > 0;)
          {
           outStream.write(bytes, 0, bytesRead);
           bytesRead = inStream.read(bytes);
           outStream.flush();
          }
          outStream.closeEntry();
        }
        catch(Exception e)
        {
          System.out.println("Exception 1 " + e.toString());
          closeConnection = true;
          throw OAException.wrapperException(e);
        }
        finally
        {
          try
          {
              if (closeConnection || (i==urlCount))
              {
                System.out.println("Closing Connections ");
                if(inStream != null)
                    inStream.close();
                if(outStream != null)
                    outStream.close();
              }
          }
          catch(Exception e)
          {
            System.out.println("Exception 2 " + e.toString());
            throw OAException.wrapperException(e);
          }
        }
      }
  }

.
For standard concurrent requests, FND_WEBFILE.GET_URL generates a temp URL. (Tools --> Copy on the Concurrent Request Log/Output file)

Cheers!

Electronic Payment Processing

Almost all Oracle Apps installations deal with Electronic Vendor Payments with Banks. In general the steps are as below --

1. Payables User/Manager creates a Payment Batch

2. Verifies all payments, removes payments that are too high or too low

3. Then payment is formatted, now at this stage, since most of the format reports are custom programs, a control could be easily set. Once the payment is formatted, the details could be sent to approver via a custom workflow. This way you have tracking of approver trails and any custom logic can be built around in the workflow.

4. Approver can review the batch contents, totals etc and then either reject or approve the payment.

5. Once it is approved by approver, the Payables Manger can confirm the batch.

6. Now comes the part where the format file is encrypted and sent to bank server.

This part could be built to have some sort of fail-safe mechanism. You can have a dedicated FTP program or mailer program whichever way your bank accepts files.

This program can take the payment batch as input and find out the formatted output for the batch. It can encrypt the file based on banks requirement, maintain a custom table where details such as who ran FTP, result of it and file names and locations for encrypted and raw file. You can also maintain login/password information for the bank servers in separate table and use this while doing FTP or mail.

You can also make this FTP program re-runnable in the sense that, you should be able to send the encrypted file for any past payment batches if needed.

There is one more important aspect. If you are using a FTP function, you can ask your bank to create a test folder and one production folder. That way you can put files from your test environment separately from production environment. For the encryption, you can also request two separate keys from the bank, one for test and one for production.

Restrict Payment Types per Responsibility

There was a simple requirement to restrict payment types for Manual Payments for specific responsibilities. This can be achieved using CUSTOM.pll, by changing the actual form code or the simplest option is to use Forms Personalization.

You can create a new record group using custom query and then attach this record group to the LOV of the field. Here are the details

1. First Select Trigger Event 'WHEN-NEW-ITEM-INSTANCE', set Trigger Object as the item whose LOV is to be changed
2. The condition should verify that the record is in insert mode, i.e. a new record. You do not want to change the LOV values for older committed records
3. In the context, set all the responsibilities for which this change is to be reflected
4. Now coming to the actions, select "Builtin" as Type and select "Create Record Group from Query" as "Builtin Type"
5. Put your custom SQL in the Argument
6. Give name to your custom record group
7. In the subsequent action, set the LOV property "Record Group" to your custom record group
8. You can also add more actions to default the value for the item. Default to both description and lookup code.

Cheers!

Using Corporate Logo throughout Oracle Apps

If you want to use your company specific logo throughout Oracle Applications self service pages, then

1. Copy the image on either $OA_HTML or $OA_MEDIA, its better to have a gif image.

2. Set Profile option "Corporate Branding Image for Oracle Applications" at site level e.g. $OA_MEDIA/company.gif

3. Bounce Apache

If the user is not able to see the new image at runtime, ask the user to clear the browser cache.

Not Publishing Attachments

If you are using the attachments in seeded iExpense/iProcurement, you would notice a 'Publish to Catalog' button. If this is enabled, and there is no security, then any user can search existing attachments and can publish to the catalog. Exposing all catalog to everyone can have unwanted implications for the company.

If you want to disable the publishing, just take out all menu items under this menu "Attachments:Publish to catalog Permission Set".

Payment Batch cannot be Confirmed

Strange error today in Payables, could not confirm Payment Batch. The error message was "APP-SQLAP-10373: To Document Number accounts for more payment documents than those which are in the payment batch. Please re-enter a lower To Document Number." As per Metalink Note 265368.1, this is intended functionality.

I checked the payment batch, it had total 173 payments, out of which only 101 were made OK to pay and rest not selected for payment as they were either below zero or zero. Checked the check numbers in the prelim register and in the account setup. Everything looked okay.

Then started search for the source of the error, first figured out whats the message short name, it is AP_PAY_DOC_NOT_IN_BATCH. Found its not used in any of the PLSQL code. So started grep'ing the AP forms, and found a hit in Payment Workbench APXPAWKB.fmb form. Looked at the form routine TEST_OVERFLOW, this one calls database PLSQL AP_CONFIRM_FORM_PKG.test_overflow_checks. Ran the SQLs in this routines, everything was good. Could not figure out why this error was being thrown.

Finally tried to work around this problem. Navigate to Payment Confirm Window where you see the check numbers From /To and buttons Cancel/Confirm/Restart. Selected Restart option, and this time the Payment Batch was confirmed without any issues.

While I did not find a root cause for this issue, this work-around did work.

Catalog Loader

Ever faced this problem?

Catalog loader fails with USR:MSG:POM_CAT_UNKNOWN_SEC Error Message

You have download sample catalog file from Apps and prepared a new one according to the sample. But still it fails.

You cross check the sample and your file, and you spot no differences.

Whats happening??

The metalink note "271354.1 - Bulk Loader Fails with USR:MSG:POM_CAT_UNKNOWN_SEC Error Message" suggest the format is wrong and says there should not exist any blank lines between the heading and its associated data lines.

This sentence "any blank lines between heading and data lines" can be misleading .

Thing is if there are blank lines at the end after all your data, the file would fail upload.
So remove all blank lines from the file and make sure the last record is indeed last record with no blanks.

Cheers!

Enforcing Attachments in iExpense/iProcurement

This is one of the cool things that I have done in quite sometime, customization to enforce attachments for Requisitions and Expense Reports. The code does not really check the contents of the attachments, it just checks if the attachments are present or not.

At first, you might think whats the big deal here, you could just use POR_CUSTOM_PKG and AP_WEB_CUST_DFLEX_PKG packages to check if the FND_ATTACHED_DOCUMENTS table has entries for the corresponding requisition or expense report. The issue with using custom hooks is, you do not have control when they would be invoked. If you use the custom hooks to enforce the check, you wont go anywhere.

You could say, why not put the check in the workflow? Return the document to users if there is no attachment. Would it not be better to prevent submission in the first place if the document does not have attachments? You bet it is much cleaner design.

But just checking this table in the custom hooks does not work at all. Why? Simply because the attachments are not pushed to database unless the requisition or expense report is submitted. And you do not want users to submit these without attachments in the first place. This means you need to check in the database as well as middle tier.


How to solve this then?
This needs playing around with Framework code. First you have to figure out the which page is doing the request submission. Once you figure that out, find out the relevant CO (Controller classes). Then you have to get handle to the FND Attachments VO, and check if the VO has any records. If you do not find any records in here, then check if the attachments are there in database. Doing it this way its going to be a foolproof solution.

The real challenge is to figure out right attachments VO. It took me quite sometime to figure out which VO it was really. Its fun, I can tell you.

Deadlock issue in Purchase Order Approvals

Since we migrated to 11510, we have been facing locking issue during PO Approval. The workflow always errored out with table PO_HEADERS_ALL locking issue. We have had the same customizations in 1159 and 11510.

What changed in 11510 is that all updates to PO_HEADERS_ALL table are moved to autonomous session. And our custom code did not have this change. Once we changed our custom updates to be done in autonomous session, this issue was gone for good.

How did I find out the issue?

I checked through core PO code, and saw that all the updates to PO_HEADERS_ALL are done in autonomous session. Checked that on metalink too, found Note 749923.1 and Note 404774.1 which point to core code updates not being in autonomous mode leading to deadlocks. This was good enough indicator to move the custom updates to PO_HEADERS_ALL in autonomous session.

SOAP Call from Oracle Apps

I was looking for some helper/bases classes from OA Framework to invoke a SOAP Service. Turned out there is no native structures in OA Framework and there are no specific directions. Just create a SOAP client using whatever you want and invoke the Web service.

So here is what I did ...

import java.io.*;

import javax.xml.namespace.QName;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.message.SOAPEnvelope;

import org.w3c.dom.*;

public class AxisClientCall
{
public AxisClientCall()
{
}

  public static void callService()
  {
      try {

             String SOAPUrl      = "https://exactEndPointURL";
             String SOAPAction   = "SOAPAction as Defined in WSDL";
             String operName     = "Operation Name as Defined in WSDL";

             FileInputStream fin = new FileInputStream("SOAPRequest.xml");
             SOAPEnvelope requestEnv = new SOAPEnvelope ((InputStream)fin);

             Service  service = new Service();
             Call     call    = (Call) service.createCall();

             call.setTargetEndpointAddress( new java.net.URL(SOAPUrl) );
             call.setUseSOAPAction(true);
             call.setSOAPActionURI(SOAPAction);
             call.setEncodingStyle(null);
             call.setSOAPVersion(org.apache.axis.soap.SOAPConstants.SOAP11_CONSTANTS);
             call.setProperty(org.apache.axis.AxisEngine.PROP_DOMULTIREFS, Boolean.FALSE);
             call.setProperty(org.apache.axis.client.Call.SEND_TYPE_ATTR, Boolean.FALSE);

             call.setOperationName(new QName("", operName) );

             SOAPEnvelope responseEnv = call.invoke(requestEnv );
             System.out.println(" responseEnv = " + responseEnv);
             if (responseEnv!=null)
             {
               Document  doc  = responseEnv.getAsDocument();
               oracle.xml.parser.v2.XMLElement o = (oracle.xml.parser.v2.XMLElement) doc.getDocumentElement();
               OutputStream out = System.out;
               o.print(out);
             }

     } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.toString());
     }
  }

public static void main(String[] args)
{
  AxisClientCall AxisClientCall = new AxisClientCall();
  AxisClientCall.callService();
}
}

There is good tutorial at http://java.sun.com/javaee/5/docs/tutorial/doc/bnbhr.html
That could be another approach to invoke SOAP service.

Thanks to countless developer folks on the web, the SOAP service call was finally built and tested successfully.

For https based service requests, you would need to import the site security certificate in java's keystore. If it is simple http, then no need to do anything. To make this java program work, I had to use following libraries.

j2ee-1.4.jar
commons-discovery-0.2.jar
axis-wsdl4j-1.2.1.jar
axis-jaxrpc-1.4.jar
axis.jar
axis-ant.jar
commons-logging-1.1.1.jar

Shell Script Error ^ " does not exist.irectory ". ^

I put bunch of XML load statements together in Shell script

adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter ./oracle/apps/ap/oie/entry/lines/webui/customizations/site/0/DetailsPG.xml -username apps -password apps -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=server.db.com)(PORT=9999)) (CONNECT_DATA=(SID=SID))) " -rootdir .

and some more bundled together..

This script errored as

^ " does not exist.irectory ". ^

Strange error indeed...

I opened the script file to make sure there are no extra spaces etc. Everything looked fine.
I closed vi and ran it again. Still same error.

Did vi on the script.
This time I noticed [dos] in the vi editor opening line at the end where it shows file name,
number of lines etc when you open it fresh.

That clicked. I had ftp'ed the file from desktop to linux.

Ran DOS2UNIX on the shell and executed script.

And bingo ! the script ran smooth.

My Metalink Bookmarks

OWF H Diagnostics and Solutions

How To Call a PL/SQL Stored Procedure from an Application Module and Access the Returned Data from a Java Application

Troubleshooting JDeveloper setup for Oracle Applications

How To Customise Oracle Workflow Template

How to purge e-mail notifications from the workflow queue so the e-mail is not sent

How to Diagnose a Punchout Issue

How To Reset a Document To Incomplete/Requires Reapproval For Isolated Cases

Bulkload Errors- This Operating Unit (Buyer) Does Not Exist Or Does Not Exist In Your Business Group

Impact of R12 Design in PO Accounting

How Can I Turn Off Workflow Mailer Notifications For Some Events?

Internet Expenses Troubleshooting Guide

Purchasing Encumbrance Accounting Concepts and Process Model

Purchasing Setup: Encumbrance Accounting

11i: A Guide to Understanding and Implementing SSL for Oracle Applications

TROUBLESHOOTING FOR AUTOCREATE FREQUENTLY ASKED QUESTIONS

Custom Concurrent Host Program Can Not be Executed

Payables Open Interface and Expense Report Import Setup and Usage Guide

General Oracle XML Gateway FAQ

Basic Setup to Check Configuring Punchout in iP

Upgrading to J2SE 1.4.2 with Oracle Applications 11i

How to Register Sample Java Concurrent Program

Email Notification URL Link sent by the OTL Approval Process needs to be disabled

Internet Expenses Knowledge Browser Product Page

Response Emails Are Being Copied And Not Moved To Discard And Process Folders

Oracle Application Framework Development FAQ Release 11i (11.5.10)

SSLInitFailureException Occurs While Attempting To Download Supplier Punchout Definition

Troubleshooting Document For Build Default Approval List Failure

How To Get Log Files In iProcurement

How To Delete the Approve, Reject, and Request More Information Buttons from a Workflow E-mail Notificatin That Requires Response

A SAMPLE JAVA CONCURRENT PROGRAM

Best Practices for Securing the E-Business Suite

Business Flow for Flexfields - Modify existing structure code

Compiling A Flexfield from the Command Line

Cost Based Optimizer (CBO) Overview

DEFINITION OF TERMS RELATING TO THE MSOBA TO MULTI-ORG MIGRATION

Discoverer 10g with Oracle Applications 11i

Discoverer 4i with Oracle Applications 11i

ETRM DIRECT ACCESS DOCUMENT

Enhancing Oracle Applications Concurrent Processing

FAQ (Flexfields)

FAQ - Multiple Organizations Architechure (Multi-Org)

FF: enabling flexfield security

FLEXFIELD: Validate on Server Profile Option

General Ledger Flexfield FAQ

How to Setup the Bulk Loader to Load XML and Tab-Delimited TXT files

How to determine to which Flexfield(s) a Value Set is assigned?

Oracle Applications AD Utilities

Oracle Applications Flexfields Frequently Asked Questions (FAQ)

Oracle Customers Online Manuals and eTRMs

Purpose of MO: Security Profile and MO: Default Operating Unit Profile Options In Applications 11i

Query Handling and Tuning Overview

Rule Based Optimizer is to be Desupported in Oracle10i

SQL Parsing Flow Diagram

Setting Up Multiple Organizations in Oracle HRMS

Starting Oracle Workflow Notification Mailer Will Send Unwanted Notifications To Users

What Is The Functionality Of The Profile Option : Flexfields:Open Key Window

Workflow FAQ - All Versions