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 = 'Quarter'
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 = 'Quarter'
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 = 'Quarter'
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 = 'Quarter'
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 = 'Quarter'
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 = 'Quarter'
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')
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 = 'QUARTER'
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 = 'QUARTER'
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 = 'QUARTER'
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 = 'QUARTER'
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 = 'QUARTER'
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 = 'QUARTER'
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')
|
|
|