SQLPLUS - Spool Results to a file

One of the useful features of SQL Plus is that it will allow easy manipulation of result rows and columns. Here is a simple script that allows spooling results without header, line breaks, inputs, etc:

SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 400
SET TERMOUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT OFF
SET TRIMS OFF
SET COLSEP "|"
SET CONCAT "."
SET NEWPAGE NONE
SET UNDERLINE OFF

COLUMN USER_ID FORMAT 99999999
COLUMN USER_NAME FORMAT A80
COLUMN LAST_LOGON_DATE FORMAT A12
COLUMN CREATION_DATE FORMAT A12
COLUMN CREATED_BY FORMAT 99999999
COLUMN LAST_UPDATE_DATE FORMAT A12
COLUMN LAST_UPDATED_BY FORMAT 999999
COLUMN LAST_UPDATE_LOGIN FORMAT 99999999
COLUMN START_DATE FORMAT A12
COLUMN DISPLAY_NAME FORMAT A80
COLUMN EMPLOYEE_ID FORMAT 99999999
COLUMN EMAIL_ADDRESS FORMAT A80
COLUMN PERSON_PARTY_ID FORMAT 99999999


SPOOL C:\MY_SQL_RESULTS.TXT;


SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
US.CREATION_DATE, US.CREATED_BY, US.LAST_UPDATE_DATE,
US.LAST_UPDATED_BY, US.LAST_UPDATE_LOGIN, US.START_DATE,
US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.USER_ID BETWEEN 12345 AND 17654;


SPOOL OFF;


Usage:
1. Set column data type and width appropriately by the usage of DEFINE statements
2. Make sure to have the LINESIZE has more than the total required width for all columns (Eg: SET LINESIZE 600)
3. Change the query (SELECT ...) as per the requirement (This is a simple query, while many practical data spool requires multi page SQLs).
4. Make sure to change the output file path correctly (Otherwise it will overwrite the current file - SPOOL C:\MY_SQL_RESULTS.TXT;)

If heading is required in the spool output, please remove the line "SET HEADING OFF".
 

Organization Structures/Hierarchy - Oracle EBusiness Suite - R12 and 11i

Organization Structures/Hierarchy - Oracle EBusiness Suite - R12 and 11i ...

Oracle 11i and R12 support Multi-Org Architecture. In a very basic level it means that within a single implementation instance, you can have multiple Business Groups, Set of Accounting Books, Legal Entities, Operating Units and Inventory Organizations. The transactions (Material and Accounting) between these entities can be tracked and accounted within the EBusiness Suite.

Here are some simple SQLs that can identify the Organization Hierarchy within an instance.

1. Business Groups:
Business groups are the highest level classification within Organization Hierarchy model. If any multinational organization that has entirely different business lines are they are to be dealt separately by the system, multiple business groups can be used. For an example, if General Electric wants to operate Electric, Consumer, Industrial and Financial divisions independently, this structure can be used.

SELECT BUSINESS_GROUP_ID, ORGANIZATION_ID,
   NAME AS BUSINESS_GROUP_NAME, DATE_FROM AS START_DATE,
  LOCATION_ID, SHORT_NAME, LEGISLATION_CODE AS COUNTRY_CODE,
   CURRENCY_CODE, ENABLED_FLAG
FROM APPS.PER_BUSINESS_GROUPS


2. Legal Entities:
Legal Entities are defined for corporate accounting and tax purposes. Typically they align with the registration of companies for statutory, administrative or legal purposes.

SELECT LEGAL_ENTITY_ID, PARTY_ID, LEGAL_ENTITY_IDENTIFIER,
   NAME AS LEGAL_ENTITY_NAME, GEOGRAPHY_ID, TRANSACTING_ENTITY_FLAG,
   EFFECTIVE_FROM AS START_DATE, LE_INFORMATION_CONTEXT AS COUNTRY_CODE, CREATION_DATE
FROM APPS.XLE_ENTITY_PROFILES XLE


3. Operating Units:
Operating Units are possible sub-sections within Legal Entities that are used to define boundaries of responsibilities within transaction based modules. The examples modules are Order Management, Cash Management, Purchasing, Account Receivables, etc.

SELECT BUSINESS_GROUP_ID, ORGANIZATION_ID AD OPERATING_UNIT_ID, NAME AS OPERATING_UNIT_NAME,
   DATE_FROM AS START_DATE, SET_OF_BOOKS_ID, DEFAULT_LEGAL_CONTEXT_ID AS LEGAL_ENTITY_ID
FROM APPS.HR_OPERATING_UNITS
-- WHERE BUSINESS_GROUP_ID = 81
-- WHERE SET_OF_BOOKS_ID = 2023
-- WHERE OPERATING_UNIT = 101
-- WHERE DEFAULT_LEGAL_CONTEXT_ID = 23273


4. Inventory Organizations:
Inventory Organizations are used to track manufacturing and item tracking. Typically an Operating Unit will have multiple inventory organizations and each one will align to a single manufacturing plant. In many cases, it is also possible to have an item validation organization which is a logical Org with all available items within the OU.

SELECT ORGANIZATION_ID AS INV_ORG_ID, BUSINESS_GROUP_ID,
   USER_DEFINITION_ENABLE_DATE AS START_DATE, ORGANIZATION_CODE,
   ORGANIZATION_NAME AS INV_ORG_NAME, SET_OF_BOOKS_ID,
   CHART_OF_ACCOUNTS_ID, INVENTORY_ENABLED_FLAG, OPERATING_UNIT,
   LEGAL_ENTITY
FROM APPS.ORG_ORGANIZATION_DEFINITIONS
-- WHERE BUSINESS_GROUP_ID = 81
-- WHERE CHART_OF_ACCOUNTS_ID = 101
-- WHERE OPERATING_UNIT = 101
-- WHERE LEGAL_ENTITY = 23273


Here is a simple multi-org architecture diagram with different levels and relations: