Display timestamp date in SQL Developer tool

 Method 1:

ALTER SESSION SET  NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

Method 2:-

Go to Tools --> Preferences --> Database --> NLS --> Date Format --> DD-MON-RR HH:MI:SS

Shell Script for production deployment in Oracle EBS for Reports

 #!/bin/ksh

################################################################################

#

# Shell Script Name : XX_PAID_INVOICES_WITH_DISTRIBUTION.sh

#

# Purpose           : A shell script to upload all components required for

#                      Paid Invoices with distribution Report

#

# Change History    :

#

# Version           Date            Name               Description

# ----------        ------------    ---------------    -------------------------

# 1.0               22-Jun-2023     Simhachalam Devaki           Original Code

#################################################################################

echo "**********************************************"

echo "Program XX_PAID_INVOICES_WITH_DISTRIBUTION.sh "

echo "**********************************************"

HOME_PATH=`pwd`

APPS_USER="APPS"

XX_USER="XX"

echo "Home Path is : $HOME_PATH"

LOG_FILE=$HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION_`date +%d`_`date +%m`_`date +%Y`_`date +%H`_`date +%M`_`date +%S`.log

echo "----------------------------" >> $LOG_FILE

echo >> $LOG_FILE

 

 

# Copy the LDTs to $XX_TOP/bin

 cp $HOME_PATH/XX_PAID_INV_DIS_CP.ldt    $XX_TOP/bin

 cp $HOME_PATH/XX_PAID_INV_DIS_DD.ldt    $XX_TOP/bin

# Copy the LDTs to $XX_TOP/sql

 cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.sql    $XX_TOP/sql

 

# Copy the data definition and RTF to $XX_TOP/reports/US/

cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.xml     $XX_TOP/reports/US

cp $HOME_PATH/XX_PAID_INVOICES_WITH_DISTRIBUTION.rtf    $XX_TOP/reports/US

 echo "Copying of files is completed"

# *******************************************************************

#  Function to Check the validity of login-ids and password.

# *******************************************************************

CHKLOGIN(){

     if sqlplus -s /nolog <<! >/dev/null 2>&1

          WHENEVER SQLERROR EXIT 1;

          CONNECT $1 ;

          

          EXIT;

!

    then

        echo OK

    else

        echo NOK

    fi

}

# *******************************************************************

#  Prompt for APPS Password 

# *******************************************************************

while [ "$APPSID" = "" -o `CHKLOGIN "$APPSID"` = "NOK" ]

do

   if [ "$APPSID" = "" ];then

       echo ""

       echo "Please enter password for apps user in hidden mode: "

       stty -echo

       read -s APPS_PWD

       stty echo

       APPSID=$APPS_USER/$APPS_PWD

   else

       echo " "

       echo "APPS Login Password is not CORRECT"

       APPSID=""

   fi

done

# *******************************************************************

#  Check if CUSTOM Password is Entered else Prompt to get it

# *******************************************************************

while [ "$XXID" = "" -o `CHKLOGIN "$XXID" "DUAL"` = "NOK" ]

do

   if [ "$XXID" = "" ];then

       echo ""

       echo "Please enter password for xxnjt user in hidden mode: "

       stty -echo

       read -s XX_PWD

       stty echo

       XXID=$XX_USER/$XX_PWD

    else

       echo " "

       echo "XX Login Password is not CORRECT"

       XXID=""

   fi

done

# +=================================================================================+

# | GET APPS DBHOST DBPORT and DBSID DETAILS

# +=================================================================================+

while [ "$DBHOST" = ""  ]

do

    if [ "$DBHOST" = "" ];then

        echo "Enter Database Host Name : "

        read DBHOST

        echo "Enter Database Host Name: " $DBHOST

    else

        echo "Database Host Name may not be CORRECT" | tee -a $LOG_FILE

        DBHOST=""

    fi

done

while [ "$DBPORT" = ""  ]

do

    if [ "$DBPORT" = "" ];then

        echo "Enter Database Port : "

        read DBPORT

        echo "Enter Database Port: " $DBPORT

    else

        echo "Database Port entered may not be CORRECT" | tee -a $LOG_FILE

        DBPORT=""

    fi

done

while [ "$DBSID" = ""  ]

do

    if [ "$DBSID" = "" ];then

        echo "Enter Database SID : "

        read DBSID

        echo "Enter Database SID: " $DBSID

    else

        echo "Database SID entered may not be CORRECT" | tee -a $LOG_FILE

        DBSID=""

    fi

done

echo $DBHOST

echo $DBPORT

echo $DBSID

#******************************************************************************

# Uploading the ldt files

#******************************************************************************

#Concurrent program

echo "Registering the concurrent programs"

# -------------------------------------------------------------------------------------

#  Registering Concurrent Program : Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

 if $FND_TOP/bin/FNDLOAD $APPSID O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $HOME_PATH/XX_PAID_INV_DIS_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 then

       echo " "                                                                                                                   >> $LOG_FILE

       echo "Conc Program Paid Invoice with Invoice Distribution Report registered successfully"                                                       >> $LOG_FILE

       echo " "                                                                                                                   >> $LOG_FILE

 else

       echo " "                                                                                                                   >> $LOG_FILE

       echo "Error registering the Concurrent Program Paid Invoice with Invoice Distribution Report. Please correct and rerun."                        >> $LOG_FILE

       exit 1

 fi

# ***********************************************************************************

#  Call SQL Script to create SQL File

# ***********************************************************************************

echo "Creating SQL File "                                           >> $LOG_FILE

echo "Creating SQL File "

if sqlplus -s $APPSID @$XX_TOP/sql/XX_PAID_INVOICES_WITH_DISTRIBUTION.sql       >> $LOG_FILE 2>&1

then

    echo "Installation of the SQL File is successful"                     >> $LOG_FILE

else

    echo "Installation of the SQL File is Unsuccessful in $APPSID"        >> $LOG_FILE

    echo "Please check and rerun"

    echo "Aborting......"

    exit 1

fi

#Data and Template Definition

# -------------------------------------------------------------------------------------

#  Registering Data Definition and Template :  Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

 if $FND_TOP/bin/FNDLOAD $APPSID O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct $HOME_PATH/XX_PAID_INV_DIS_DD.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 then

       echo " "                                                                                                                   >> $LOG_FILE

       echo "Data Definition and Template Paid Invoice with Invoice Distribution Report registered successfully"                                       >> $LOG_FILE

       echo " "                                                                                                                   >> $LOG_FILE

 else

       echo " "                                                                                                                   >> $LOG_FILE

       echo "Error registering the Data Definition and Template Paid Invoice with Invoice Distribution Report. Please correct and rerun."              >> $LOG_FILE

       exit 1

 fi 

#Data source XML

# -------------------------------------------------------------------------------------

#  Upload Data source XML for Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if  java oracle.apps.xdo.oa.util.XDOLoader UPLOAD\

    -DB_USERNAME $APPS_USER\

    -DB_PASSWORD $APPS_PWD\

    -JDBC_CONNECTION $DBHOST:$DBPORT/$DBSID\

    -LOB_TYPE DATA_TEMPLATE \

    -LOB_CODE PAID_INV_DIS\

    -XDO_FILE_TYPE XML \

    -FILE_NAME $XX_TOP/reports/US/XX_PAID_INVOICES_WITH_DISTRIBUTION.xml\

    -APPS_SHORT_NAME XX\

    -LANGUAGE en\

    -TERRITORY US\

    -CUSTOM_MODE FORCE    

        then

           echo "Uploading the XML Data Source Template for Paid Invoice with Invoice Distribution Report is Successful " >> $LOG_FILE

        else

           echo "Uploading the XML Data Source Template for Paid Invoice with Invoice Distribution Report is not successful" >> $LOG_FILE

        exit 1

  fi

  

# RTF Data Template

# -------------------------------------------------------------------------------------

#  Upload RTF Data Template for Paid Invoice with Invoice Distribution Report

# -------------------------------------------------------------------------------------

if  java oracle.apps.xdo.oa.util.XDOLoader UPLOAD\

    -DB_USERNAME $APPS_USER\

    -DB_PASSWORD $APPS_PWD\

    -JDBC_CONNECTION $DBHOST:$DBPORT/$DBSID\

    -LOB_TYPE TEMPLATE\

    -APPS_SHORT_NAME XX\

    -LOB_CODE PAID_INV_DIS\

    -LANGUAGE en\

    -TERRITORY US\

    -XDO_FILE_TYPE RTF\

    -NLS_LANG AMERICAN\

    -FILE_CONTENT_TYPE 'application/rtf'\

    -FILE_NAME $XX_TOP/reports/US/XX_PAID_INVOICES_WITH_DISTRIBUTION.rtf\

    -CUSTOM_MODE FORCE

        then

           echo "Uploading the RTF Template for Paid Invoice with Invoice Distribution Report is Successful " >> $LOG_FILE

        else

           echo "Uploading the RTF Template for Paid Invoice with Invoice Distribution Report is not successful" >> $LOG_FILE           

        exit 1

  fi

  

echo " "

echo "Installation is completed"

echo "Please check installation log file : "$LOG_FILE

echo "installation Completed..." >> $LOG_FILE

echo " "

exit 0

# *******************************************************************

#  End of Script

# ******************************************************************

Package in after report trigger for XML Bursting

 create or replace PACKAGE XXUNV_CPA_ACTIVITY_BURST_PKG

AS

  --

  -- Description: This package contains the triggers for the 'XXUNV CPA Activity Report' XML Report

  --

  -- =================================================================================================

  -- MODIFICATION HISTORY

  --

  -- Date       version      Person            Comments

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

  -- 04/16/2021  1.0        Simhachalam Devaki      Initial Version

  --

  --  =================================================================================================

    --Initializing report parameters

P_CONC_REQUEST_ID       NUMBER;

P_DATE_FROM          DATE;

P_DATE_TO            DATE;

P_VENDOR_NUMBER       NUMBER;

P_EMAIL                 VARCHAR2(240);

FUNCTION beforeReport RETURN BOOLEAN;

FUNCTION afterReport RETURN BOOLEAN;

END XXUNV_CPA_ACTIVITY_BURST_PKG;

create or replace PACKAGE BODY XXUNV_CPA_ACTIVITY_BURST_PKG

AS

  --

  -- Description: This package contains the triggers for the 'XXUNV CPA Activity Report' XML Report

  --

  -- =================================================================================================

  -- MODIFICATION HISTORY

  --

  -- Date       version      Person            Comments

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

  -- 04/16/2021  1.0        Simhachalam Devaki      Initial Version

  --

  --  =================================================================================================

FUNCTION beforeReport RETURN BOOLEAN

IS

l_err_code       NUMBER;

l_err_msg        VARCHAR2(2400);

BEGIN

 --Initializing the concurrent request ID

P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;

RETURN TRUE;

END; -- End of beforeReport Function

FUNCTION afterReport RETURN BOOLEAN

IS

     ln_sub_req            NUMBER;

     lc_cp_description     VARCHAR2(100);

     lc_cp_request_id      CONSTANT  NUMBER := P_CONC_REQUEST_ID;

     lc_email    VARCHAR2(240);

BEGIN

P_EMAIL := TRIM(P_EMAIL);

lc_email := P_EMAIL;

--Fetch the Concurrent Program Name

BEGIN

SELECT fcp.user_concurrent_program_name

  INTO lc_cp_description

  FROM FND_CONCURRENT_REQUESTS    fcr,

   FND_CONCURRENT_PROGRAMS_VL fcp

WHERE  fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.request_id = lc_cp_request_id;

EXCEPTION

WHEN OTHERS THEN

lc_cp_description := NULL;

END;

FND_FILE.PUT_LINE(FND_FILE.LOG,'lc_email: ' || lc_email);

--Call to the standard Oracle Concurrent Request for Bursting

   IF lc_email IS NOT NULL THEN

BEGIN

              ln_sub_req :=  FND_REQUEST.SUBMIT_REQUEST(

                                                        application => 'XDO',             -- application

                                                        program => 'XDOBURSTREP',     -- Program

                                                        description=> lc_cp_description,  -- description

                                                        argument1=>'N' ,

                                                        argument2=> lc_cp_request_id,   -- argument1

                                                        argument3=> 'Yes'                -- argument2

                                                      );

              COMMIT;

IF ln_sub_req <= 0

THEN

                FND_FILE.PUT_LINE(FND_FILE.LOG,'Failed to Submit Bursting XML Publisher Request For CR: ' || lc_cp_request_id);

        ELSE

                FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Bursting XML Publisher Request Request ID: ' || ln_sub_req);

        END IF;

EXCEPTION

        WHEN OTHERS THEN

            FND_FILE.PUT_LINE(FND_FILE.LOG,'Encountered Exception in afterReport Trigger: ' || SUBSTR(SQLERRM,1,100));

        END;

   END IF;

RETURN TRUE;

END;-- End of afterReport Function

END; -- End of Package