Have you checked our new Android App!!! Please download and learn oracle at ease

October 5, 2016

SQL Script for getting the details of Inventory Org to Business Group

bishnupc / / 3
Below Script will help you to get the Inventory Org to Business Group details with Legal Entity, Operating Unit, Ledger, Period details, Inventory Validation org and Purchase Validation org for the Operating unit

This script is in the context of Oracle R12

-------------------
select mp.organization_code org_code,
       org.organization_id org_id,
       org.name org_name,
       hl.location_id,
       hl.location_code,
       hl.address_line_1,
       hl.address_line_2,
       hl.address_line_3,
       hl.town_or_city,
       hl.country,
       hl.postal_code,
       ou.organization_id ou_id,
       ou.name OU,
       le.legal_entity_id le_id,
       le.name LE,
       gl.ledger_id,
       gl.name primary_ledger,
       gl.currency_code,
       bg.name bg,
       (select organization_code
          from apps.mtl_parameters
         where organization_id =
               (select parameter_value
                  from apps.OE_SYS_PARAMETERS_ALL
                 where parameter_code = 'MASTER_ORGANIZATION_ID'
                   and org_id = ou.organization_id)) IVO,
       (select organization_code
          from apps.mtl_parameters
         where organization_id =
               (select inventory_organization_id
                  from AP.FINANCIALS_SYSTEM_PARAMS_ALL#
                 where org_id = ou.organization_id)) PVO,
       (select period_name || ' : ' || open_flag
          from apps.ORG_ACCT_PERIODS
         where period_start_date <= trunc(sysdate)
           and schedule_close_date >= trunc(sysdate)
           and organization_id = mp.organization_id) inv_period,
       (select period_name || ' : ' || show_status
          from apps.GL_PERIOD_STATUSES_V
         where start_date <= trunc(sysdate)
           and end_date >= trunc(sysdate)
           and ledger_id = gl.ledger_id
           and application_id = 101) gl_ledger_period,
       (select period_name || ' : ' || show_status
          from apps.GL_PERIOD_STATUSES_V
         where start_date <= trunc(sysdate)
           and end_date >= trunc(sysdate)
           and ledger_id = gl.ledger_id
           and application_id = 200) AP_period,
       (select period_name || ' : ' || show_status
          from apps.GL_PERIOD_STATUSES_V
         where start_date <= trunc(sysdate)
           and end_date >= trunc(sysdate)
           and ledger_id = gl.ledger_id
           and application_id = 222) AR_period,
       (select period_name || ' : ' || show_status
          from apps.GL_PERIOD_STATUSES_V
         where start_date <= trunc(sysdate)
           and end_date >= trunc(sysdate)
           and ledger_id = gl.ledger_id
           and application_id = 201) PO_period

  from apps.XLE_ENTITY_PROFILES         le,
       apps.HR_ALL_ORGANIZATION_UNITS   ou,
       apps.HR_ALL_ORGANIZATION_UNITS   org,
       apps.HR_ALL_ORGANIZATION_UNITS   bg,
       apps.mtl_parameters              mp,
       apps.GL_LEDGERS                  gl,
       apps.HR_ORGANIZATION_INFORMATION ouinfo,
       apps.HR_ORGANIZATION_INFORMATION orginfo,
       apps.hr_locations                hl
 where mp.organization_id = org.organization_id
   and org.organization_id = orginfo.organization_id
   and org.location_id = hl.location_id
   and orginfo.org_information_context = 'Accounting Information'
   and orginfo.org_information3 = ou.organization_id
   and orginfo.org_information1 = gl.ledger_id
   and orginfo.org_information2 = le.legal_entity_id
   and ou.organization_id = ouinfo.organization_id
   and ouinfo.org_information_context = 'Operating Unit Information'
   and ouinfo.org_information2 = le.legal_entity_id
   and ouinfo.org_information3 = gl.ledger_id
   and bg.organization_id = ou.business_group_id
   and mp.organization_code in ('V1');
-------------------------

3 comments:

  1. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle SCM training, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle SCM training. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/


    ReplyDelete
  2. Can you let me know how a lpn and delivery are associated in context of customer order shipping. I need the tables and joins. What is perant_lpn_id and how it is releated

    ReplyDelete
  3. And 카지노 사이트 if you enjoy each enjoying in} playing cards in a reside poker site and enjoying in} traditional on line casino video games, then Ignition is probably one of the best online on line casino for you. No bonus code is necessary for this 150% deposit match up to as} $3,000 for poker and on line casino video games. Most online playing sites will absolutely permit you to play free on line casino video games first. This is to check out the on line casino to see if you like greatest way|the method in which} the video games play, or simply, to see if you like the look of it. This additionally be|may additionally be|can be} a great way|a good way|an effective way} to follow and determine the foundations of a recreation.

    ReplyDelete

Copyright © . Oracle Apps (SCM) . All Rights Reserved
Oracle Apps (SCM) is an online knowledge sharing blog which index online free tutorials, blogs and other sources, to get easier and accessible manner. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. All trademarks, trade names, service marks, copyrighted work, logos referenced herein belong to their respective owners/companies. If any of the posting is violating copyright and you want us to remove that content. Please contact Here