Home/ andy.haack/Answers
  1. Asked: September 14, 2021In:

    Query To Find Operating Unit And Corresponding Inventory Organizations Details in Oracle Apps R12

    andy.haack Junior
    This answer was edited.

    You can use the following SQL from the GL Ledgers and Organizations using blitz report SELECT HAOUV.NAME BUSINESS_GROUP , (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0Read more

    You can use the following SQL from the GL Ledgers and Organizations using blitz report

    SELECT HAOUV.NAME BUSINESS_GROUP
, (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET
FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0
WHERE GL.LEDGER_ID = GLSNAV.LEDGER_ID
AND GLSNAV.LEDGER_SET_ID = GL0.LEDGER_ID)
LEDGER_SET
,GL.NAME LEDGER
,XXEN_UTIL.MEANING (GL.LEDGER_CATEGORY_CODE, 'GL_ASF_LEDGER_CATEGORY', 101) LEDGER_CATEGORY
,GL.CURRENCY_CODE CURRENCY
,FIFSV.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNTS_NAME
,HOU.NAME OPERATING_UNIT
,OOD.ORGANIZATION_CODE
,OOD.ORGANIZATION_NAME ORGANIZATION
,FTV.TERRITORY_SHORT_NAME COUNTRY
,XEP.NAME LEGAL_ENTITY
,GL.LEDGER_ID
,FIFSV.ID_FLEX_STRUCTURE_CODE CHART_OF_ACCOUNTS_CODE
,GL.CHART_OF_ACCOUNTS_ID
,OOD.OPERATING_UNIT OPERATING_UNIT_ID
,OOD.ORGANIZATION_ID
FROM GL_LEDGERS GL
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,HR_OPERATING_UNITS HOU
,HR_ALL_ORGANIZATION_UNITS_VL HAOUV
,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_ALL_ORGANIZATION_UNITS HAOU
,HR_LOCATIONS_ALL HLA
,FND_TERRITORIES_VL FTV
,XLE_ENTITY_PROFILES XEP
WHERE NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND GL.OBJECT_TYPE_CODE = 'L'
AND GL.CHART_OF_ACCOUNTS_ID = FIFSV.ID_FLEX_NUM
AND FIFSV.APPLICATION_ID = 101
AND FIFSV.ID_FLEX_CODE = 'GL#'
AND GL.LEDGER_ID = HOU.SET_OF_BOOKS_ID(+)
AND HOU.BUSINESS_GROUP_ID = HAOUV.ORGANIZATION_ID(+)
AND HOU.ORGANIZATION_ID = OOD.OPERATING_UNIT(+)
AND OOD.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND HAOU.LOCATION_ID = HLA.LOCATION_ID(+)
AND HLA.COUNTRY = FTV.TERRITORY_CODE(+)
AND OOD.LEGAL_ENTITY = XEP.LEGAL_ENTITY_ID(+)
ORDER BY CHART_OF_ACCOUNTS_NAME
,LEDGER_SET
,BUSINESS_GROUP
,LEDGER
,CURRENCY
,OPERATING_UNIT
,COUNTRY
,ORGANIZATION_CODE
    See less
    • 0
  2. Asked: December 10, 2022In:

    Query to Get Chart of Account Description in Oracle APPS R12

    andy.haack Junior
    This answer was edited.

    You can use the following query from blitz report FND Key Flexfields: SELECT FAV.APPLICATION_NAME,FIF.ID_FLEX_NAME TITLE ,FIF.DESCRIPTION ,FIF.ID_FLEX_CODE ,FIF.APPLICATION_TABLE_NAME ,FIF.UNIQUE_ID_COLUMN_NAME ,FIF.SET_DEFINING_COLUMN_NAME ,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED ,FRead more

    You can use the following query from blitz report FND Key Flexfields:

    SELECT FAV.APPLICATION_NAME
    ,FIF.ID_FLEX_NAME TITLE
,FIF.DESCRIPTION
,FIF.ID_FLEX_CODE
,FIF.APPLICATION_TABLE_NAME
,FIF.UNIQUE_ID_COLUMN_NAME
,FIF.SET_DEFINING_COLUMN_NAME
,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED
,FIFSV.ID_FLEX_STRUCTURE_CODESTRUCTURE_CODE
,FIFSV.ID_FLEX_STRUCTURE_NAMESTRUCTURE_NAME
,FIFSV.ID_FLEX_NUM
,FIFSV.CONCATENATED_SEGMENT_DELIMITER DELIMITER
,FIFSV.SHORTHAND_PROMPT
,FIFSGV.SEGMENT_NUMSEGMENT_NUMBER
,FIFSGV.SEGMENT_NAME
,FIFSGV.FORM_LEFT_PROMPT WINDOW_PROMPT
,FIFSGV.APPLICATION_COLUMN_NAMECOLUMN_NAME
, (SELECT DISTINCT LISTAGG (XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602), ', ')
WITHIN GROUP (ORDER BY XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602))
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER
, (SELECT DISTINCT LISTAGG (FSAV.SEGMENT_ATTRIBUTE_TYPE, ', ')
WITHIN GROUP (ORDER BY FSAV.SEGMENT_ATTRIBUTE_TYPE)
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER_CODE
,FFVS.FLEX_VALUE_SET_NAME VALUE_SET_NAME
,DECODE (FIFSGV.ENABLED_FLAG, 'Y', 'Y') ENABLED
,DECODE (FIFSGV.DISPLAY_FLAG, 'Y', 'Y') DISPLAYED
,DECODE (FIFSGV.REQUIRED_FLAG, 'Y', 'Y') REQUIRED
,DECODE (FIFSGV.SECURITY_ENABLED_FLAG, 'Y', 'Y') SECURITY_ENABLED
,XXEN_UTIL.MEANING (FFVS.VALIDATION_TYPE, 'SEG_VAL_TYPES', 0) VALIDATION_TYPE
,FFVT.APPLICATION_TABLE_NAME VALIDATION_TABLE
,FFVT.ADDITIONAL_WHERE_CLAUSE WHERE_CLAUSE
,FIFSGV.FLEX_VALUE_SET_ID
FROM FND_APPLICATION_VL FAV
,FND_ID_FLEXS FIF
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,FND_ID_FLEX_SEGMENTS_VL FIFSGV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALIDATION_TABLES FFVT
WHERE FIF.ID_FLEX_CODE = 'GL#'
AND 1 = 1
AND FAV.APPLICATION_ID = FIF.APPLICATION_ID
AND FIF.APPLICATION_ID = FIFSV.APPLICATION_ID(+)
AND FIF.ID_FLEX_CODE = FIFSV.ID_FLEX_CODE(+)
AND FIFSV.APPLICATION_ID = FIFSGV.APPLICATION_ID(+)
AND FIFSV.ID_FLEX_CODE = FIFSGV.ID_FLEX_CODE(+)
AND FIFSV.ID_FLEX_NUM = FIFSGV.ID_FLEX_NUM(+)
AND FIFSGV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID(+)
AND DECODE (FFVS.VALIDATION_TYPE, 'F', FFVS.FLEX_VALUE_SET_ID) = FFVT.FLEX_VALUE_SET_ID(+)
ORDER BY FAV.APPLICATION_NAME
,FIF.ID_FLEX_NAME
,FIFSV.ID_FLEX_STRUCTURE_NAME
,FIFSGV.SEGMENT_NUM
    See less
    • 0