VIEW: APPS.BIS_YEARS_V

Source

View Text - Preformatted

SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME  ID
, GL.PERIOD_NAME                                VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(SOB.SET_OF_BOOKS_ID)
, 'SET OF BOOKS'
FROM GL_PERIODS GL
   , GL_SETS_OF_BOOKS SOB
  WHERE GL.PERIOD_TYPE = 'Year'
 AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
UNION
SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME  ID
, GL.PERIOD_NAME                                VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(ORG.ORGANIZATION_ID)
, 'ORGANIZATION'
FROM GL_PERIODS GL
   , ORG_ORGANIZATION_DEFINITIONS ORG
   , GL_SETS_OF_BOOKS SOB
 WHERE GL.PERIOD_TYPE = 'Year'
 AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
 AND SOB.SET_OF_BOOKS_ID = ORG.SET_OF_BOOKS_ID
UNION
SELECT DISTINCT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME  ID
, GL.PERIOD_NAME                                VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(SOB.LEGAL_ENTITY_ID)
, 'LEGAL ENTITY'
 FROM GL_PERIODS GL,
      GL_LEDGER_LE_V SOB
 WHERE GL.PERIOD_TYPE = 'Year'
 AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
 AND SOB.LEGAL_ENTITY_ID IS NOT NULL
UNION
SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME  ID
, GL.PERIOD_NAME                                VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(LEFP.LEGAL_ENTITY_ID)
, 'OPERATING UNIT'
 FROM GL_PERIODS GL
    , HR_OPERATING_UNITS OU
    , GL_LEDGER_LE_V SOB
    , XLE_FIRSTPARTY_INFORMATION_V LEFP
 WHERE GL.PERIOD_TYPE = 'Year'
 AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
 AND LEFP.LEGAL_ENTITY_ID = SOB.LEGAL_ENTITY_ID
 AND TO_CHAR(SOB.LEDGER_ID) = OU.SET_OF_BOOKS_ID
UNION
SELECT a.PERIOD_SET_NAME||'+'||a.period_name  ID
, a.period_name                               VALUE
, a.PERIOD_SET_NAME
, a.period_name
, a.START_DATE
, a.END_DATE
, a.PERIOD_YEAR
, d.orgn_code
, 'OPM COMPANY'
FROM  gl_periods a
    , gl_sets_of_books b
    , gl_plcy_mst c
    , sy_orgn_mst d
WHERE a.period_set_name = b.period_set_name
AND   a.period_type = 'Year'
AND   b.set_of_books_id = c.sob_id
AND   c.co_code	= d.co_code
UNION
SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME  ID
, GL.PERIOD_NAME                                VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, '-1'
, 'TOTAL_ORGANIZATIONS'
FROM GL_PERIODS GL
   , GL_SETS_OF_BOOKS SOB
 WHERE GL.PERIOD_TYPE = 'Year'
 AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
 AND TO_CHAR(SOB.SET_OF_BOOKS_ID) =  FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')

View Text - HTML Formatted

SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME ID
, GL.PERIOD_NAME VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(SOB.SET_OF_BOOKS_ID)
, 'SET OF BOOKS' FROM GL_PERIODS GL
, GL_SETS_OF_BOOKS SOB
WHERE GL.PERIOD_TYPE = 'YEAR'
AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME UNION SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME ID
, GL.PERIOD_NAME VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(ORG.ORGANIZATION_ID)
, 'ORGANIZATION' FROM GL_PERIODS GL
, ORG_ORGANIZATION_DEFINITIONS ORG
, GL_SETS_OF_BOOKS SOB
WHERE GL.PERIOD_TYPE = 'YEAR'
AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
AND SOB.SET_OF_BOOKS_ID = ORG.SET_OF_BOOKS_ID UNION SELECT DISTINCT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME ID
, GL.PERIOD_NAME VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(SOB.LEGAL_ENTITY_ID)
, 'LEGAL ENTITY'
FROM GL_PERIODS GL
, GL_LEDGER_LE_V SOB
WHERE GL.PERIOD_TYPE = 'YEAR'
AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
AND SOB.LEGAL_ENTITY_ID IS NOT NULL UNION SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME ID
, GL.PERIOD_NAME VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, TO_CHAR(LEFP.LEGAL_ENTITY_ID)
, 'OPERATING UNIT'
FROM GL_PERIODS GL
, HR_OPERATING_UNITS OU
, GL_LEDGER_LE_V SOB
, XLE_FIRSTPARTY_INFORMATION_V LEFP
WHERE GL.PERIOD_TYPE = 'YEAR'
AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
AND LEFP.LEGAL_ENTITY_ID = SOB.LEGAL_ENTITY_ID
AND TO_CHAR(SOB.LEDGER_ID) = OU.SET_OF_BOOKS_ID UNION SELECT A.PERIOD_SET_NAME||'+'||A.PERIOD_NAME ID
, A.PERIOD_NAME VALUE
, A.PERIOD_SET_NAME
, A.PERIOD_NAME
, A.START_DATE
, A.END_DATE
, A.PERIOD_YEAR
, D.ORGN_CODE
, 'OPM COMPANY' FROM GL_PERIODS A
, GL_SETS_OF_BOOKS B
, GL_PLCY_MST C
, SY_ORGN_MST D WHERE A.PERIOD_SET_NAME = B.PERIOD_SET_NAME AND A.PERIOD_TYPE = 'YEAR' AND B.SET_OF_BOOKS_ID = C.SOB_ID AND C.CO_CODE = D.CO_CODE UNION SELECT GL.PERIOD_SET_NAME||'+'||GL.PERIOD_NAME ID
, GL.PERIOD_NAME VALUE
, GL.PERIOD_SET_NAME
, GL.PERIOD_NAME
, GL.START_DATE
, GL.END_DATE
, GL.PERIOD_YEAR
, '-1'
, 'TOTAL_ORGANIZATIONS' FROM GL_PERIODS GL
, GL_SETS_OF_BOOKS SOB
WHERE GL.PERIOD_TYPE = 'YEAR'
AND GL.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
AND TO_CHAR(SOB.SET_OF_BOOKS_ID) = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')