7 Replies Latest reply on Apr 4, 2017 6:50 PM by Beauty_and_dBest

    Completed - Concurrent programs missing?

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.2.4

      Windows Server 2012

       

       

      I run this script to list all completed programs in our PROD instance, but I am surprised no rows where returned.

      Is there missing in this script? Or what could be the reason why no rows is selected?

       

      select

             fcptl.user_concurrent_program_name "Program",

             round(greatest(actual_completion_date - actual_start_date,0)*24*60, 2) "Duration (min)",

             round(greatest(actual_start_date - REQUESTED_START_DATE,0)*24*60, 2) "Wait (min)",

             fcr.request_id "Request ID",

             fcu.user_name "User Name",

             fcqtl.user_concurrent_queue_name "Concurrent Manager",

             actual_start_date "Actual Start", REQUESTED_START_DATE,REQUEST_DATE,

             flvs.meaning "Status",

             fcr.priority "Priority",

             request_limit "Request Limit",

             fcr.argument_text "Parameters"

        from

             fnd_concurrent_programs_tl fcptl,

             fnd_user fcu,

             fnd_concurrent_queues_tl fcqtl,

             fnd_concurrent_processes fcproc,

             fnd_lookups flvs,

             fnd_concurrent_requests fcr

      where

             fcr.phase_code = 'C'

             and fcr.actual_completion_date is not null

             and actual_start_date is not null

             and fcr.requested_by=fcu.user_id

             and fcr.concurrent_program_id =fcptl.concurrent_program_id

             and fcr.program_application_id =fcptl.application_id

             and fcptl.language=userenv('LANG')

             and fcr.controlling_manager  = fcproc.concurrent_process_id

             and fcproc.queue_application_id = fcqtl.application_id

             and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id

             and fcqtl.language=userenv('LANG')

             and flvs.lookup_type = 'CP_STATUS_CODE'

             and fcr.status_code = flvs.lookup_code

      order by

             "Duration (min)" desc,

             "Program" asc;

       

       

      Please help...

       

       

      Kind regards,

      jc

        • 1. Re: Completed - Concurrent programs missing?
          mdtaylor

          Hi jc,

           

          I think the userenv(('LANG') is making it not find rows.  I use the following queries for this (change the language to meet your needs):

           

          SELECT CR.REQUEST_ID "Request", U.USER_NAME "User", CP.USER_CONCURRENT_PROGRAM_NAME "Program", RESPONSIBILITY_NAME "Responsibility",

                 TO_CHAR(CR.REQUESTED_START_DATE,'DD-MON-YY HH24:MI:SS') "Requested Start",

                 TO_CHAR(CR.ACTUAL_START_DATE,'DD-MON-YY HH24:MI:SS') "Actual Start",

                 ROUND((CR.ACTUAL_START_DATE-REQUESTED_START_DATE)*24*60*60,0) "Pending Time (sec)",

                 ROUND((CR.ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60,0) "Duration (sec)",

                 ROUND((CR.ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,2) "Duration (min)",

                 CR.PHASE_CODE "Phase", CR.STATUS_CODE "Status", CR.ARGUMENT_TEXT "Arguments"

          FROM FND_CONCURRENT_REQUESTS CR,

                FND_USER U,

                FND_CONCURRENT_PROGRAMS_TL CP,

                FND_RESPONSIBILITY_TL R

          WHERE CR.REQUESTED_BY = U.USER_ID  --AND CR.CONCURRENT_PROGRAM_ID IN (37361)

             AND CR.PROGRAM_APPLICATION_ID = CP.APPLICATION_ID

             AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID

             AND CP.LANGUAGE='US'

             AND CR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

             AND CR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND R.LANGUAGE='US'

          --  AND TO_CHAR(CR.REQUESTED_START_DATE,'DD-MON-YYYY') BETWEEN '01-APR_2013' and '19-APR_2013'

          ORDER BY ROUND((CR.ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60,0) DESC;

           

          This one show the requests you run the most which can be purged individually:

           

          SELECT CP.USER_CONCURRENT_PROGRAM_NAME "Program", count(*)

          FROM APPLSYS.FND_CONCURRENT_REQUESTS CR,

                APPLSYS.FND_USER U,

                APPLSYS.FND_CONCURRENT_PROGRAMS_TL CP,

                APPLSYS.FND_RESPONSIBILITY_TL R

          WHERE CR.REQUESTED_BY = U.USER_ID

             AND CR.PROGRAM_APPLICATION_ID = CP.APPLICATION_ID

             AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID

             AND CP.LANGUAGE='US'

             AND CR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

             AND CR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND R.LANGUAGE='US'

          --   AND (TO_CHAR(CR.ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI') BETWEEN '15-AUG-2016 09:00' and '15-AUG-2016 22:00')

          GROUP BY CP.USER_CONCURRENT_PROGRAM_NAME

          HAVING COUNT(*) > 1000

          ORDER BY COUNT(*) DESC;

           

          Regards,

          Michael

          • 3. Re: Completed - Concurrent programs missing?
            Beauty_and_dBest

            Hi Michael,

             

            I used this script which I also had in my file.

            Do you think is it good also? Please check if output is correct and meaningful.

            Is it meaningful to sort it by processing time?

            Why is that "Cost Manager" has the largest processing time of 275062.68  (is it in seconds?)  and it almost run for 1 year?

            Are there issues with this program?

             

            Please help...

             

            Kind regards,

             

            SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,         

            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,        

            a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),

            To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,    

            (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,        

            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority FROM   apps.fnd_concurrent_requests a,        

            apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c,         

            apps.fnd_user d  WHERE a.concurrent_program_id= b.concurrent_program_id AND        

            b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id;

             

                         

            USER_CONCURRENT_PROGRAM_NAMEPROCESS_TIMEREQUEST_IDPARENT_REQUEST_IDTO_CHAR(A.REQUEST_DATE,'DD-MON-YYHH24:MI:SS')TO_CHAR(A.ACTUAL_START_DATE,'DD-MON-YYHH24:MI:SS')TO_CHAR(A.ACTUAL_COMPLETION_DATE,'DD-MON-YYHH24:MI:SS')END_TO_ENDLAG_TIMEUSER_NAMEPHASE_CODESTATUS_CODEARGUMENT_TEXTPRIORITY
            Cost Manager275062.6824784294-19/7/2016 17:039/7/2016 17:033/17/2017 17:261650378019SYSADMINCE50
            Publish and Presentation Program663.0524969301-13/14/2017 9:143/14/2017 9:143/14/2017 20:17397907BCSJMBCE24969300, , 1825, FEB-17, NA, Document, 161, , RGRARG, US, US, , GAE_COMPANY_TOTAL, SQLGL, , NOW, , NONE, , FRM, 11246, , FSG_SUBMISSION_FLOW, , null, , , SQLGL, , , , , ,50
            Publish and Presentation Program662.3524969303-13/14/2017 9:153/14/2017 9:153/14/2017 20:173975413BCSJMBCE24969302, , 1825, FEB-17, NA, Document, 162, , RGRARG, US, US, , REE-COMPANY_TOTAL, SQLGL, , NOW, , NONE, , FRM, 11252, , FSG_SUBMISSION_FLOW, , null, , , SQLGL, , , , , ,50
            Publish and Presentation Program661.7824969305-13/14/2017 9:163/14/2017 9:163/14/2017 20:17397169BCSJMBCE24969304, , 1825, FEB-17, NA, Document, 21, , RGRARG, US, US, , BUDGETARY_LISTING_TOTAL_IL, SQLGL, , NOW, , NONE, , FRM, 11698, , FSG_SUBMISSION_FLOW, , null, , 1044, SQLGL, , , , , Y,50
            Publish and Presentation Program659.3224969308-13/14/2017 9:183/14/2017 9:183/14/2017 20:17395667BCSJMBCE24969307, , 1825, DEC-16, NA, Document, 161, , RGRARG, US, US, , GAE_COMPANY_TOTAL, SQLGL, , NOW, , NONE, , FRM, 11246, , FSG_SUBMISSION_FLOW, , null, , , SQLGL, , , , , Y,50
            Publish and Presentation Program658.7724969310-13/14/2017 9:193/14/2017 9:193/14/2017 20:17395337BCSJMBCE24969309, , 1825, DEC-16, NA, Document, 161, , RGRARG, US, US, , GAE_COMPANY_TOTAL, SQLGL, , NOW, , NONE, , FRM, 11246, , FSG_SUBMISSION_FLOW, , null, , , SQLGL, , , , , Y,50
            Publish and Presentation Program657.7724969312-13/14/2017 9:193/14/2017 9:203/14/2017 20:173948014BCSJMBCE24969311, , 1825, DEC-16, NA, Document, 162, , RGRARG, US, US, , REE-COMPANY_TOTAL, SQLGL, , NOW, , NONE, , FRM, 11252, , FSG_SUBMISSION_FLOW, , null, , , SQLGL, , , , , Y,50
            Publish and Presentation Program657.2724969314-13/14/2017 9:203/14/2017 9:203/14/2017 20:17394426BCSJMBCE24969313, , 1825, DEC-16, NA, Document, 21, , RGRARG, US, US, , BUDGETARY_LISTING_TOTAL_IL, SQLGL, , NOW, , NONE, , FRM, 11698, , FSG_SUBMISSION_FLOW, , null, , 1044, SQLGL, , , , , Y,50
            @Tax Preference Report145.6724976958-13/20/2017 8:533/20/2017 8:533/20/2017 11:1887411PPIJSTCC1, 2, 2, 2, 50132, 2015/11/04 00:00:00, 2015/12/20 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            Transaction register145.6724976958-13/20/2017 8:533/20/2017 8:533/20/2017 11:1887411PPIJSTCC1, 2, 2, 2, 50132, 2015/11/04 00:00:00, 2015/12/20 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            @Tax Preference Report143.3524976964-13/20/2017 8:553/20/2017 8:553/20/2017 11:1886021PPIJSTCC1, 2, 2, 2, 50132, 2016/01/01 00:00:00, 2017/03/20 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register143.3524976964-13/20/2017 8:553/20/2017 8:553/20/2017 11:1886021PPIJSTCC1, 2, 2, 2, 50132, 2016/01/01 00:00:00, 2017/03/20 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            @Tax Preference Report142.9824976965-13/20/2017 8:563/20/2017 8:563/20/2017 11:1985790PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register142.9824976965-13/20/2017 8:563/20/2017 8:563/20/2017 11:1985790PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            @Tax Preference Report141.6224976966-13/20/2017 8:573/20/2017 8:573/20/2017 11:1985014PPIJSTCC1, 2, 2, 2, 50132, 2015/05/12 00:00:00, 2015/11/02 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register141.6224976966-13/20/2017 8:573/20/2017 8:573/20/2017 11:1985014PPIJSTCC1, 2, 2, 2, 50132, 2015/05/12 00:00:00, 2015/11/02 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            @Tax Preference Report140.1324976967-13/20/2017 8:583/20/2017 8:583/20/2017 11:1884091PPIJSTCC1, 2, 2, 2, 50132, 2015/11/04 00:00:00, 2015/12/20 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register140.1324976967-13/20/2017 8:583/20/2017 8:583/20/2017 11:1884091PPIJSTCC1, 2, 2, 2, 50132, 2015/11/04 00:00:00, 2015/12/20 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            @Tax Preference Report128.9724977008-13/20/2017 9:313/20/2017 9:313/20/2017 11:4077391PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register128.9724977008-13/20/2017 9:313/20/2017 9:313/20/2017 11:4077391PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            @Tax Preference Report124.2724976952-13/20/2017 8:513/20/2017 8:513/20/2017 10:5574571PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            Transaction register124.2724976952-13/20/2017 8:513/20/2017 8:513/20/2017 10:5574571PPIJSTCC1, 2, 2, 2, 50132, 2015/01/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            @Tax Preference Report124.0524977013-13/20/2017 9:363/20/2017 9:363/20/2017 11:40745815PPIJSTCC1, 2, 2, 2, 50132, 2015/03/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register124.0524977013-13/20/2017 9:363/20/2017 9:363/20/2017 11:40745815PPIJSTCC1, 2, 2, 2, 50132, 2015/03/01 00:00:00, 2015/05/10 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Journals - (132 Char)123.9324977012-13/20/2017 9:353/20/2017 9:353/20/2017 11:3974371DSLPPCC1000, S, U, PHP, 1, MAR-17, , , , JVDSLPP-19-032017 Spreadsheet A 24976982, 13250
            @Tax Preference Report122.524977014-13/20/2017 9:383/20/2017 9:383/20/2017 11:4073566PPIJSTCC1, 2, 2, 2, 50132, 2016/01/10 00:00:00, 2017/03/20 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            Transaction register122.524977014-13/20/2017 9:383/20/2017 9:383/20/2017 11:4073566PPIJSTCC1, 2, 2, 2, 50132, 2016/01/10 00:00:00, 2017/03/20 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            @Tax Preference Report120.5524976955-13/20/2017 8:523/20/2017 8:523/20/2017 10:5272363PPIJSTCC1, 2, 2, 2, 50132, 2015/05/12 00:00:00, 2015/11/02 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            Transaction register120.5524976955-13/20/2017 8:523/20/2017 8:523/20/2017 10:5272363PPIJSTCC1, 2, 2, 2, 50132, 2015/05/12 00:00:00, 2015/11/02 00:00:00, 2, 1, 1, 2, 2, 2, , , , , , , , , 1, 101, , , , ,50
            @Tax Preference Report117.8824977017-13/20/2017 9:413/20/2017 9:413/20/2017 11:3970752PPIJSTCC1, 2, 2, 2, 50132, 2015/03/01 00:00:00, 2015/04/30 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            Transaction register117.8824977017-13/20/2017 9:413/20/2017 9:413/20/2017 11:3970752PPIJSTCC1, 2, 2, 2, 50132, 2015/03/01 00:00:00, 2015/04/30 00:00:00, 2, 1, 1, 2, 2, 2, P0056, P0056, , , , , , , 1, 101, , , , ,50
            • 4. Re: Completed - Concurrent programs missing?
              mdtaylor

              Hi jc,

               

              I do not see any issues with this SQL.  The Cost Manager will always show a large runtime because it is always looking for records to process.

               

              Regards,

              Michael

              • 5. Re: Completed - Concurrent programs missing?
                Beauty_and_dBest

                Thanks Michael,

                 

                But why is that it almost runs for 1 year? started 9/2016 and ended 3/2017?

                Should it be running all the time?

                 

                 

                 

                Kind regards,

                • 6. Re: Completed - Concurrent programs missing?
                  mdtaylor

                  Hi jc,

                   

                  Yes, Cost Manager should be running all of the time.  The same is true for any of the Inventory Interface Managers which Cost Manager is one, and the Planning Manager.

                   

                  Regards,

                  Michael