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