1 2 Previous Next 18 Replies Latest reply on Mar 1, 2017 3:43 PM by 3173025 Go to original post
      • 15. Re: Need to schedule the concurrent program which automatically changes the date as per sysdate
        Brajesh Shukla

        Hi,

        You need to write a small host(Unix shell) program to call you sqlldr. Steps are as followed

         

        1. Create one small host program to call the sqlldr control file. Sample I have attached, In that data file name you can put your logic what you were using at value set.

           e.g. your file name is HOSTFILENAME.prog

        2. Change your concurrent program executable to type host and give the execution file name as HOSTFILENAME

        3. Put control file and host file to your APPLICATION_TOP(Application used to register executable)/bin folder

        4. Create Soft link for host file

              ln -s $FND_TOP/bin/fndcpesr $XXAP_TOP/bin/HOSTFILENAME

        5. Schedule your program as you were doing

        #!/bin/bash

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

        #                                   #

        # DESCRIPTION  : Shell programming(HOST)                                        #

        #              :                                                                #

        # PROCESS FLOW : 1)The program to upload AR Transaction Source files            #

        #                  from Oracle directory to the pre staging table               #

        #                                                                               #

        #  Parameters  :  $5 = DATA_FILE_NAME                                           #

        #                 $6 = DIR_PATH                                                 #

        #                 $7 = LOG_PATH                                                 #

        #                 $8 = ARCH_PATH                                                #

        # =====================================================================         #

        # MODIFICATION HISTORY                                                          #

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

        # DATE       |NAME                |DETAILS                                      #

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

        # 06-FEB-2017|                    |Initial Version                              #

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

        #

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

        echo "============================================="

        echo "PROGRAM TO LOAD AR RECEIPTS DATA"

        echo "============================================="

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

        # Standard parameters

        APPS_LOGIN=${1}

        USER_ID=${2}

        USER_NAME=${3}

        REQ_ID=${4}

        # Custom parameters

        DATA_FILE_NAME=${5}

        eval DIR_PATH=${6}

        eval LOG_PATH=${7}

        eval ARCH_PATH=${8}

         

         

        #Display Parameters

        echo Request ID is              $REQ_ID

        echo File Name is               $DATA_FILE_NAME

        echo File Directory Path is     $DIR_PATH

        echo Log  Directory Path is     $LOG_PATH

        echo Archive Directory Path is  $ARCH_PATH

        echo " "

        #

        #echo Changing Directory to IRON_TOP/bin

         

         

        if [ `ls $DIR_PATH|grep $DATA_FILE_NAME|head -1` ];

        then

        echo "Processing the files...."

        else

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

           echo "********************File Not Found**********************"

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

           exit 1

        fi

         

         

        cd $DIR_PATH

         

         

        FILE_NAME=$DATA_FILE_NAME  #Here you can put all your logic to derive file name

         

         

        echo $FILE_NAME

         

         

        if [ -s $DIR_PATH/$FILE_NAME ];

        then

         

         

         

         

        if [ -f $LOG_PATH/$FILE_NAME.bad ]

        then

        rm $LOG_PATH/$FILE_NAME.*

        echo Removing existing bad and log files

        fi

         

         

        sqlldr $1 control=$IRON_TOP/bin/CONTROL_FILE_NAME.ctl data=$FILE_NAME log=$LOG_PATH/$FILE_NAME.log bad=$LOG_PATH/$FILE_NAME.bad silent=\(header,feedback,errors,discards,partitions\)

         

         

        ## Code to check the creation of bad file in sqlloader

            if [ -f $LOG_PATH/$FILE_NAME.bad ]

            then

                echo "Errors encountered during sql*loader operation. Review bad file!! "

                echo Start Deleting the records from pre staging table

                 # Delete the records from pre staging table

                   delete_pre_stg=`sqlplus -s $FCP_LOGIN <<OUEOF

                            set echo off

                            set feedback off

                            set linesize 1000

                            set serveroutput on size 1000000

                            whenever sqlerror exit SQL.SQLCODE rollback

                            begin

         

         

                             DELETE FROM TABLE_NAME

                             WHERE FILE_NAME = $DATA_FILE_NAME;

         

         

                             COMMIT;

         

         

                             dbms_output.put_line(' The records deleted Successfully');

         

         

                            exception

                             when others then

                               dbms_output.put_line('Exception : Unable to delete the records from the table TABLE_NAME - '||SQLERRM);

                            end;

                            /

                          exit

                     OUEOF`

                echo "Delete Status: "$delete_pre_stg

                  #

                echo End Delete

                # Remove the data file

                echo 'Removing the temp data file'

                 rm $FILE_NAME

          echo 'Temp Data file removed'

                echo =================== Log file for details =======================

                cat $LOG_PATH/$FILE_NAME.log

                echo

                echo =================== Start of Bad file for details =======================

                cat $LOG_PATH/$FILE_NAME.bad

                echo =================== End of Bad file for details =========================

                echo please check the errored records and re-run the program after modification of data file.

                exit 2

            else

                echo " Successful completion"

                # Archive the file

                 echo 'Archiving the original data file'

                 ARCH_FILE=$FILE_NAME.`date +%Y%m%d%H%M%S`

          mv $DIR_PATH/$FILE_NAME $ARCH_PATH/$ARCH_FILE

                 echo 'File archived'

            fi

        else

          echo 'file not found: '$DIR_PATH/$DATA_FILE_NAME

          exit 1

        fi

        echo Execution completes

         

         

         

        Regards,

        Brajesh

        • 16. Re: Need to schedule the concurrent program which automatically changes the date as per sysdate
          3173025

          Thanks bro. This helps me. i have created a new procedure with the same parameters and calling my concurrent program here using API

           

          Very thankful for ur logic.

          • 17. Re: Need to schedule the concurrent program which automatically changes the date as per sysdate
            Anaa

            How can you schedule your concurrent program when country parameter need human intervention ?

            I don't understand yet...

            • 18. Re: Need to schedule the concurrent program which automatically changes the date as per sysdate
              3173025

              As of now we r doing only for country - US, just for future reference we have kept other countries.

               

              We will create separate program for each country and schedule that. As the validations/code varies for each country

              1 2 Previous Next