General Ledger - Oracle EBS R12: September 2017

https://generalledger-gl.blogspot.com/2017/09/trial-balance-detail-std-query.html

PLSQL Query to Get Trial balance Detail & Expanded

Trial Balance Detail Query

SELECT

glcc.SEGMENT1 || '-' || glcc.SEGMENT2 || '-' || glcc.SEGMENT3 || '-' ||
glcc.SEGMENT4 || '-' || glcc.SEGMENT5 || '-' || glcc.SEGMENT6 || '-' ||
glcc.SEGMENT7 || '-' || glcc.SEGMENT8 Code_Combination,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_DR, 0),

'S',
NVL(BEGIN_BALANCE_DR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0)),

'C',
NVL(BEGIN_BALANCE_DR_BEQ, 0))),

'PJTD',
DECODE(&CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0, 'C', 0),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_DR, 0)),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_DR, 0)),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_DR_BEQ, 0)),
0)))) BEG_BALANCE_DR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_CR, 0),

'S',
NVL(BEGIN_BALANCE_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_CR, 0),
NVL(BEGIN_BALANCE_CR_BEQ, 0)),

'C',
NVL(BEGIN_BALANCE_CR_BEQ, 0))),

'PJTD',
DECODE(&CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0, 'C', 0),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_CR, 0)),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_CR, 0)),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_CR_BEQ, 0)),
0)))) BEG_BALANCE_CR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),

'S',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0)),

'C',
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0))),

'PJTD',
DECODE(&CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0, 'C', 0),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0)),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,

(NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0)),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,

&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
(NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0)),
0)))) BEG_BAL_H,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PERIOD_NET_DR, 0),

'S',
NVL(PERIOD_NET_DR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PERIOD_NET_DR, 0),
NVL(PERIOD_NET_DR_BEQ, 0)),

'C',
NVL(PERIOD_NET_DR_BEQ, 0))),

'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'S',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0),

NVL(PROJECT_TO_DATE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0)),

'C',
NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0))),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,

NVL(PERIOD_NET_DR, 0) + NVL(BEGIN_BALANCE_DR, 0),
0) -

DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0)

+ NVL(BEGIN_BALANCE_DR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) +
NVL(BEGIN_BALANCE_DR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0) - DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0)))) PERIOD_DR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PERIOD_NET_CR, 0),

'S',
NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PERIOD_NET_CR, 0),
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(PERIOD_NET_CR_BEQ, 0))),

'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'S',
NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0),

NVL(PROJECT_TO_DATE_CR_BEQ, 0) +
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0))),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,

NVL(PERIOD_NET_CR, 0) + NVL(BEGIN_BALANCE_CR, 0),
0) -

DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR, 0),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR, 0)

+ NVL(BEGIN_BALANCE_CR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR, 0),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_CR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0) - DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0)))) PERIOD_CR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0),

'S',
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0),

NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(PERIOD_NET_DR_BEQ, 0) - NVL(PERIOD_NET_CR_BEQ, 0))),

'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0)

- NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),

'S',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0)

- NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0)

- NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),

NVL(PROJECT_TO_DATE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0)

- NVL(PROJECT_TO_DATE_CR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PROJECT_TO_DATE_CR_BEQ, 0)

- NVL(PERIOD_NET_CR_BEQ, 0))),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,

NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0)

+ NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0)

- NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0)

- DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0),

DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0)

+ NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0) - DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0) - DECODE(GLB.PERIOD_NAME,
&FIRST_PERIOD_NAME,
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0)))) ACTIVITY_H,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'S',
NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0) +

NVL(PERIOD_NET_DR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0)),

'C',
NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0))),

'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'S',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_DR, 0) +

NVL(PERIOD_NET_DR, 0),
NVL(PROJECT_TO_DATE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0)),

'C',
NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0))),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) + NVL(BEGIN_BALANCE_DR, 0),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) + NVL(BEGIN_BALANCE_DR, 0),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',

DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) +

NVL(BEGIN_BALANCE_DR, 0),
0),
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0),
0)))) END_BALANCE_DR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'S',
NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_CR, 0) +

NVL(PERIOD_NET_CR, 0),
NVL(BEGIN_BALANCE_CR_BEQ, 0) +
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0))),

'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'S',
NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0),

'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_CR, 0) +

NVL(PERIOD_NET_CR, 0),
NVL(PROJECT_TO_DATE_CR_BEQ, 0) +
NVL(PERIOD_NET_CR_BEQ, 0)),

'C',
NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0))),

'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR, 0) + NVL(BEGIN_BALANCE_CR, 0),
0)),

'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR, 0) + NVL(BEGIN_BALANCE_CR, 0),
0)),

'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',

DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR, 0) +

NVL(BEGIN_BALANCE_CR, 0),
0),
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),

'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0)))) END_BALANCE_CR,

DECODE(&AMOUNT_TYPE,
'PTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0),
'S',
NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0),
'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0)),
'C',
NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0) - NVL(PERIOD_NET_CR_BEQ, 0))),
'PJTD',
SUM(DECODE(&CURRENCY_TYPE,
'T',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),
'S',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),
'E',
DECODE(GLB.TRANSLATED_FLAG,
'R',
NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0) -
NVL(PROJECT_TO_DATE_CR, 0) - NVL(PERIOD_NET_CR, 0),
NVL(PROJECT_TO_DATE_DR_BEQ, 0) +
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PROJECT_TO_DATE_CR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0)),
'C',
NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PROJECT_TO_DATE_CR_BEQ, 0) - NVL(PERIOD_NET_CR_BEQ, 0))),
'YTD',
DECODE(&CURRENCY_TYPE,
'T',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
0)),
'S',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
0)),
'E',
SUM(DECODE(GLB.TRANSLATED_FLAG,
'R',
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) - NVL(PERIOD_NET_CR, 0) +
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
0),
DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0))),
'C',
SUM(DECODE(GLB.PERIOD_NAME,
&PERIOD_NAME,
NVL(PERIOD_NET_DR_BEQ, 0) -
NVL(PERIOD_NET_CR_BEQ, 0) +
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0),
0)))) END_BAL_H

FROM GL_BALANCES GLB,

GL_CODE_COMBINATIONS GLCC,

GL_LEDGERS GLL,

GL_LEDGER_SET_ASSIGNMENTS ASG,

GL_LEDGER_RELATIONSHIPS LR

WHERE

GLB.ACTUAL_FLAG = 'A'

AND GLB.CURRENCY_CODE = &ENTERED_CURRENCY

AND GLB.PERIOD_NAME IN (&PERIOD_NAME,
DECODE(&AMOUNT_TYPE,
'PTD',
&PERIOD_NAME,

'PJTD',
&PERIOD_NAME,

'YTD',
&FIRST_PERIOD_NAME))

AND GLB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID

--AND GLCC.CHART_OF_ACCOUNTS_ID = &STRUCT_NUM

AND GLCC.SUMMARY_FLAG = 'N'

AND GLCC.TEMPLATE_ID IS NULL

AND GLL.LEDGER_ID = &LEDGER_ID

AND ASG.LEDGER_SET_ID(+) = GLL.LEDGER_ID

AND LR.TARGET_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID)

AND LR.SOURCE_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID)

AND LR.TARGET_CURRENCY_CODE = &LEDGER_CURRENCY

AND LR.SOURCE_LEDGER_ID = GLB.LEDGER_ID

AND LR.TARGET_LEDGER_ID = GLB.LEDGER_ID

group by glcc.SEGMENT1,
glcc.SEGMENT2,
glcc.SEGMENT3,
glcc.SEGMENT4,
glcc.SEGMENT5,
glcc.SEGMENT6,
glcc.SEGMENT7,
glcc.SEGMENT8

order by glcc.SEGMENT4

#############################################################################

Trial Balance Expanded STD Query

select

/*cc.SEGMENT1, cc.SEGMENT2, cc.SEGMENT3,*/
cc.SEGMENT4, /*cc.SEGMENT5, cc.SEGMENT6, cc.SEGMENT7, cc.SEGMENT8,*/

decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0))))

BEGIN_DR,

decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0))))

BEGIN_CR,

decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name, &PERIOD_NAME, nvl(bl.period_net_dr, 0), 0)),

'S',
sum(decode(bl.period_name, &PERIOD_NAME, nvl(bl.period_net_dr, 0), 0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr_beq, 0),
0))))

PER_DR,

decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name, &PERIOD_NAME, nvl(bl.period_net_cr, 0), 0)),

'S',
sum(decode(bl.period_name, &PERIOD_NAME, nvl(bl.period_net_cr, 0), 0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr_beq, 0),
0))))

PER_CR,

decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0))))

+ decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0)),

'S',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr_beq, 0),
0))))

END_DR,

decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0))))

+ decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0)),

'S',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr_beq, 0),
0))))

END_CR,

decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

1,

sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

1,

decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0))))

+ decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0)),

'S',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_dr_beq, 0),
0))))

- (decode(&CURRENCY_TYPE,

'T',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'S',
decode(sign(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),

-1,

abs(sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))),
0)

+ sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0))

- sum(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr, 0),
0),

decode(sign(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),

-1,

abs(decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_dr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)),
0)

+ decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0)

- decode(bl.period_name,
&FIRST_PERIOD_NAME,
nvl(bl.begin_balance_cr_beq, 0),
0))))

+ decode(&CURRENCY_TYPE,

'T',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0)),

'S',
sum(decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0)),

'E',
sum(decode(bl.translated_flag,

'R',
decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr, 0),
0),

decode(bl.period_name,
&PERIOD_NAME,
nvl(bl.period_net_cr_beq, 0),
0)))))

NET,

max(LR.TARGET_LEDGER_name) LEDGER_NAME_H

FROM gl_balances BL,

gl_code_combinations CC,

GL_LEDGERS GLL,

GL_LEDGER_SET_ASSIGNMENTS ASG,

GL_LEDGER_RELATIONSHIPS LR

WHERE bl.currency_code = &ENTERED_CURRENCY

AND bl.period_name in (&FIRST_PERIOD_NAME, &PERIOD_NAME)

AND bl.actual_flag = 'A'

AND cc.template_id is null

AND CC.SUMMARY_FLAG = 'N'

--AND cc.chart_of_accounts_id = &STRUCT_NUM

AND cc.code_combination_id = bl.code_combination_id

AND GLL.LEDGER_ID = &LEDGER_ID

AND ASG.LEDGER_SET_ID(+) = GLL.LEDGER_ID

AND LR.TARGET_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID)

AND LR.SOURCE_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID)

AND LR.TARGET_CURRENCY_CODE = &LEDGER_CURRENCY

AND LR.SOURCE_LEDGER_ID = BL.LEDGER_ID

AND LR.TARGET_LEDGER_ID = BL.LEDGER_ID

and cc.SEGMENT4 between NVL(&FROM_ACCOUNT, cc.SEGMENT4) and
NVL(&TO_ACCOUNT, cc.SEGMENT4)

group by /*cc.SEGMENT1, cc.SEGMENT2, cc.SEGMENT3,*/ cc.SEGMENT4 /*, cc.SEGMENT5, cc.SEGMENT6, cc.SEGMENT7, cc.SEGMENT8*/

order by cc.SEGMENT4

General Ledger - Oracle EBS R12

Wednesday 27 September 2017

Trial balance Detail & Expanded STD Query - Oracle EBS R12