3 Replies Latest reply on Sep 7, 2016 4:32 AM by DeepakGupta

    SQL query to list out Purchasing Period Status Operating Unit Wise

    3147086

      Hi EBSFolks,

       

      I need SQL query for EBS R12 to list out  Purchasing Period Status Operating Unit Wise

       

      Anybody has handy query?

       

      We have 26 OUs for client so I dont like the fact of checking each one by one and see if it is opened for next month or now.

       

      Some one please help.

       

      Kind Regards,

      Sagar Sankhe

        • 1. Re: SQL query to list out Purchasing Period Status Operating Unit Wise
          anasazii

          this should get you started at least...I'm not sure how the different OUs would be in the table  since we only have one...

           

          select application_id

            from applsys.fnd_application fa

          where fa.application_short_name = 'PO'

           

          select *

            from gl.gl_period_statuses

          where application_id = :result_from_above_query

          order by start_date desc

           

          Janel

          • 2. Re: SQL query to list out Purchasing Period Status Operating Unit Wise
            Doifode

            hi,

             

            There is one way to get what you want. I have modified @anasaziiquery as per your requirment.

             

            by giving org_id of any one of your 26 orgs you can get desired result. you can also get it for different responsibility by parameterising fa.application_short_name  i.e. :app_short_name

             

            ##----QUERY To GET list out  Purchasing Period Status Operating Unit Wise----

             

            select gl.period_name,gl.closing_status,gl.start_date,gl.end_date,gl.year_start_date,gl.quarter_start_date,gl.period_year,gl.period_num

              from gl_period_statuses gl

            where gl.application_id in (select application_id

              from applsys.fnd_application fa

            where fa.application_short_name = 'PO')

            and gl.ledger_id in (select (select ledger_id from gl_ledgers where name=  mo_utils.Get_Ledger_Name( :org_id )) from dual)

            order by gl.start_date desc;

             

            I HOPE this will solve your problem and help you in your work.

             

            Kindly check and reply.

            • 3. Re: SQL query to list out Purchasing Period Status Operating Unit Wise
              DeepakGupta

              Hi

               

              You can also use below query...

              Select

               

              *

               

              from gl.gl_period_statuses

              where

              application_id = (select application_id

               

              from applsys.fnd_application fa

              where fa.application_short_name = 'PO')

               

              period_name =:period_name


              order by start_date desc

               

              Regards