Enterprise: find Legal Entity, Organization, Company Code ...

SELECT
       xep.name                   "Legal Entity",
       hr_outl.name               "Organization Name",
       hr_outl.organization_id    "Organization ID",
       hr_loc.location_id         "Location ID",
       hr_loc.country             "Country Code",
       hr_loc.location_code       "Location Code",
       glev.flex_segment_value    "Company Code"
  FROM
       xle_entity_profiles            xep,
       xle_registrations              reg,
       hr_operating_units             hou,
       hr_all_organization_units_tl   hr_outl,
       hr_locations_all               hr_loc,
       gl_legal_entities_bsvs         glev
 WHERE   1=1
   AND xep.transacting_entity_flag   =  'Y'
   AND xep.legal_entity_id           =  reg.source_id
   AND reg.source_table              =  'XLE_ENTITY_PROFILES'
   AND reg.identifying_flag          =  'Y'
   AND xep.legal_entity_id           =  hou.default_legal_context_id
   AND reg.location_id               =  hr_loc.location_id
   AND xep.legal_entity_id           =  glev.legal_entity_id
   AND hr_outl.organization_id       =  hou.organization_id
  ORDER BY hr_outl.name

Read More

ENTERPRISE : Where is Organization Info Parked in Oracle EBS.

Here are tables where Organization Data is getting Parked.

• HR_LOCATIONS Stores information about organization locations. Address, Org_id, ship_to / bill_to site, receiving site, etc LOCATION_ID is the primary key on this table. This table is accessed by forms or reports from the following modules; HR, INV (Inventory), PO/REC.
• HR_ORGANIZATION_INFORMATION Stores the information on all organizations defined in the system. It also stores most of the organization classification specific information. The table is populated through the Define Organization form (PERWSDOR). Each organization defined will have 1 row for the organization and 1 row for each organization classification defined on the form. ORG_INFORMATION_ID is the primary key on this table.
• HR_ORGANIZATION_UNITS Stores generic information about the organizations. This is an excellent place to go if you need to see the relationship between org id and Business group id when troubleshooting a multi-org problem. ORGANIZATION_ID is the primary key for this table. BUSINESS_GROUP_ID is a foreign key. This table is used by a variety of org setup and item setup forms.
• MTL_PARAMETERS Stores the set of general default options like GL accounts, locator, lot and serial number controls, costing method, inter-org options, costing org and item master, etc This table is populated by the org parameters form INVSDOIO. It is accessed by a wide variety of forms, reports, user exits, and concurrent programs throughout the manufacturing modules. ORGANIZATION_ID is the primary key. This is the most heavily used Organization table in the manufacturing products.
• RCV_PARAMETERS Stores receiving options for each organization that is setup to receive items. There is 1 row for each receiving organization. This table is populated by the org parameters form, receiving options, or from PO setup receiving options.
• ORG_ACCESS Stores the data on which responsibilities are granted access to the existing organizations. Frequently customers will partially populate the Organization Access form (INVSDORA), and effectively lock themselves out of their organizations. This table stores that information. If all else fails, you can frequently resolve this issue by truncating this table and having the user re-populate the Organization Access form correctly.

Read More

ENT : Query to verify to Ledger Setups - R12

SELECT
   GLV.LEDGER_CATEGORY_CODE,
   GLV.NAME,
   GLV.SHORT_NAME,
   GLV.DESCRIPTION,
   GLV.CHART_OF_ACCOUNTS_ID,
   GLV.PERIOD_SET_NAME                ACCOUNTING_CALENDAR,
   GLV.CURRENCY_CODE,
   GLV.SLA_ACCOUNTING_METHOD_CODE,
   GLV.FIRST_LEDGER_PERIOD_NAME,
   GLV.FUTURE_ENTERABLE_PERIODS_LIMIT,
   GLV.SLA_DESCRIPTION_LANGUAGE,
   A.CONCATENATED_SEGMENTS            ENTERED_CURRENCY,
   --GLV.SLA_ENTERED_CUR_BAL_SUS_CCID,
   B.CONCATENATED_SEGMENTS            RETAINED_EARNING,
   --GLV.RET_EARN_CODE_COMBINATION_ID,
   C.CONCATENATED_SEGMENTS            ROUNDING_ACCOUNT,
   -- GLV.ROUNDING_CODE_COMBINATION_ID ,
   GLV.ALLOW_INTERCOMPANY_POST_FLAG,
   GLV.ENABLE_JE_APPROVAL_FLAG,
   GLV.PERIOD_END_RATE_TYPE,
   E.USER_CONVERSION_TYPE,
   -- GLV.PERIOD_AVERAGE_RATE_TYPE,
   D.CONCATENATED_SEGMENTS            TRANSLATION_ACCOUNT,
   GLV.CUM_TRANS_CODE_COMBINATION_ID    
FROM
   GL_LEDGERS                GLV,
   GL_CODE_COMBINATIONS_KFV  A,
   GL_CODE_COMBINATIONS_KFV  B,
   GL_CODE_COMBINATIONS_KFV  C,
   GL_CODE_COMBINATIONS_KFV  D,
   GL_DAILY_CONVERSION_TYPES E   
WHERE
   E.CONVERSION_TYPE = GLV.PERIOD_AVERAGE_RATE_TYPE     
   AND GLV.SLA_ENTERED_CUR_BAL_SUS_CCID = A.CODE_COMBINATION_ID     
   AND GLV.RET_EARN_CODE_COMBINATION_ID = B.CODE_COMBINATION_ID     
   AND GLV.ROUNDING_CODE_COMBINATION_ID = C.CODE_COMBINATION_ID     
   AND GLV.CUM_TRANS_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID;

Read More

ENT :Query to fetch Ledger, OU, Legal Enity, balancing segment:

SELECT
   hrl.country,
   hroutl_bg.NAME            bg,
   hroutl_bg.organization_id,
   lep.legal_entity_id,
   lep.NAME                  legal_entity,
   hroutl_ou.NAME            ou_name,
   hroutl_ou.organization_id org_id,
   hrl.location_id,
   hrl.location_code,
   glev.FLEX_SEGMENT_VALUE    
FROM
   xle_entity_profiles          lep,
   xle_registrations            reg,
   hr_locations_all             hrl,
   hz_parties                   hzp,
   fnd_territories_vl           ter,
   hr_operating_units           hro,
   hr_all_organization_units_tl hroutl_bg,
   hr_all_organization_units_tl hroutl_ou,
   hr_organization_units        gloperatingunitseo,
   gl_legal_entities_bsvs       glev   
WHERE
   lep.transacting_entity_flag = 'Y'     
   AND lep.party_id = hzp.party_id     
   AND lep.legal_entity_id = reg.source_id     
   AND reg.source_table = 'XLE_ENTITY_PROFILES'     
   AND hrl.location_id = reg.location_id     
   AND reg.identifying_flag = 'Y'     
   AND ter.territory_code = hrl.country     
   AND lep.legal_entity_id = hro.default_legal_context_id     
   AND gloperatingunitseo.organization_id = hro.organization_id     
   AND hroutl_bg.organization_id = hro.business_group_id     
   AND hroutl_ou.organization_id = hro.organization_id     
   AND glev.legal_entity_id = lep.legal_entity_id

© FreeFormatter.com - Brought to you by MrForms. NEQ: 2269075

Read More