VIEW: APPS.IGC_CBC_JE_BATCHES_V

Source

View Text - Preformatted

SELECT B.ROWID ROW_ID , B.CBC_JE_BATCH_ID CBC_JE_BATCH_ID , L.CBC_JE_LINE_NUM CBC_JE_LINE_NUM, B.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , B.CC_HEADER_ID, B.NAME NAME , B.BATCH_STATUS BATCH_STATUS, 'POSTED' SHOW_BATCH_STATUS, B.ACTUAL_FLAG ACTUAL_FLAG , L3.DESCRIPTION SHOW_ACTUAL_FLAG , B.BUDGETARY_CONTROL_STATUS BUDGETARY_CONTROL_STATUS , L2.MEANING SHOW_BC_STATUS , B.APPROVAL_STATUS_CODE APPROVAL_STATUS_CODE , B.DESCRIPTION DESCRIPTION , B.EFFECTIVE_DATE EFFECTIVE_DATE, B.RUNNING_TOTAL_DR RUNNING_TOTAL_DR , B.RUNNING_TOTAL_CR RUNNING_TOTAL_CR , B.CONTEXT CONTEXT , B.LAST_UPDATE_DATE LAST_UPDATE_DATE , B.LAST_UPDATED_BY LAST_UPDATED_BY , B.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , B.CREATION_DATE CREATION_DATE , B.CREATED_BY CREATED_BY , PS.PERIOD_NAME PERIOD_NAME, B.ATTRIBUTE1 ATTRIBUTE1 , B.ATTRIBUTE2 ATTRIBUTE2 , B.ATTRIBUTE3 ATTRIBUTE3 , B.ATTRIBUTE4 ATTRIBUTE4 , B.ATTRIBUTE5 ATTRIBUTE5 , B.ATTRIBUTE6 ATTRIBUTE6 , B.ATTRIBUTE7 ATTRIBUTE7 , B.ATTRIBUTE8 ATTRIBUTE8 , B.ATTRIBUTE9 ATTRIBUTE9 , B.ATTRIBUTE10 ATTRIBUTE10 , PS.START_DATE START_DATE , PS.END_DATE END_DATE , PS.PERIOD_YEAR PERIOD_YEAR , PS.PERIOD_NUM PERIOD_NUM , L.JE_SOURCE USER_JE_SOURCE_NAME, GL.NAME LEDGER_NAME FROM IGC_CBC_JE_LINES L, IGC_CBC_JE_BATCHES B, GL_PERIOD_STATUSES PS, GL_LOOKUPS L3, GL_LOOKUPS L2, GL_LEDGERS GL WHERE B.APPROVAL_STATUS_CODE ='A' AND B.BATCH_STATUS = 'P' AND L2.LOOKUP_TYPE = 'JE_BATCH_BC_STATUS' AND L2.LOOKUP_CODE = B.BUDGETARY_CONTROL_STATUS AND L3.LOOKUP_TYPE = 'BATCH_TYPE' AND L3.LOOKUP_CODE = B.ACTUAL_FLAG AND PS.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID AND /* Added below two conditions during r12 uptake */ GL.LEDGER_ID = PS.SET_OF_BOOKS_ID AND GL.LEDGER_ID = B.SET_OF_BOOKS_ID AND PS.APPLICATION_ID = 101 AND (B.EFFECTIVE_DATE BETWEEN PS.START_DATE AND PS.END_DATE) AND PS.ADJUSTMENT_PERIOD_FLAG <> 'Y' AND B.CBC_JE_BATCH_ID = L.CBC_JE_BATCH_ID AND L.CBC_JE_LINE_NUM IN (SELECT MIN(L.CBC_JE_LINE_NUM) FROM IGC_CBC_JE_LINES L WHERE B.CBC_JE_BATCH_ID = L.CBC_JE_BATCH_ID)

View Text - HTML Formatted

SELECT B.ROWID ROW_ID
, B.CBC_JE_BATCH_ID CBC_JE_BATCH_ID
, L.CBC_JE_LINE_NUM CBC_JE_LINE_NUM
, B.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, B.CC_HEADER_ID
, B.NAME NAME
, B.BATCH_STATUS BATCH_STATUS
, 'POSTED' SHOW_BATCH_STATUS
, B.ACTUAL_FLAG ACTUAL_FLAG
, L3.DESCRIPTION SHOW_ACTUAL_FLAG
, B.BUDGETARY_CONTROL_STATUS BUDGETARY_CONTROL_STATUS
, L2.MEANING SHOW_BC_STATUS
, B.APPROVAL_STATUS_CODE APPROVAL_STATUS_CODE
, B.DESCRIPTION DESCRIPTION
, B.EFFECTIVE_DATE EFFECTIVE_DATE
, B.RUNNING_TOTAL_DR RUNNING_TOTAL_DR
, B.RUNNING_TOTAL_CR RUNNING_TOTAL_CR
, B.CONTEXT CONTEXT
, B.LAST_UPDATE_DATE LAST_UPDATE_DATE
, B.LAST_UPDATED_BY LAST_UPDATED_BY
, B.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, B.CREATION_DATE CREATION_DATE
, B.CREATED_BY CREATED_BY
, PS.PERIOD_NAME PERIOD_NAME
, B.ATTRIBUTE1 ATTRIBUTE1
, B.ATTRIBUTE2 ATTRIBUTE2
, B.ATTRIBUTE3 ATTRIBUTE3
, B.ATTRIBUTE4 ATTRIBUTE4
, B.ATTRIBUTE5 ATTRIBUTE5
, B.ATTRIBUTE6 ATTRIBUTE6
, B.ATTRIBUTE7 ATTRIBUTE7
, B.ATTRIBUTE8 ATTRIBUTE8
, B.ATTRIBUTE9 ATTRIBUTE9
, B.ATTRIBUTE10 ATTRIBUTE10
, PS.START_DATE START_DATE
, PS.END_DATE END_DATE
, PS.PERIOD_YEAR PERIOD_YEAR
, PS.PERIOD_NUM PERIOD_NUM
, L.JE_SOURCE USER_JE_SOURCE_NAME
, GL.NAME LEDGER_NAME
FROM IGC_CBC_JE_LINES L
, IGC_CBC_JE_BATCHES B
, GL_PERIOD_STATUSES PS
, GL_LOOKUPS L3
, GL_LOOKUPS L2
, GL_LEDGERS GL
WHERE B.APPROVAL_STATUS_CODE ='A'
AND B.BATCH_STATUS = 'P'
AND L2.LOOKUP_TYPE = 'JE_BATCH_BC_STATUS'
AND L2.LOOKUP_CODE = B.BUDGETARY_CONTROL_STATUS
AND L3.LOOKUP_TYPE = 'BATCH_TYPE'
AND L3.LOOKUP_CODE = B.ACTUAL_FLAG
AND PS.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND /* ADDED BELOW TWO CONDITIONS DURING R12 UPTAKE */ GL.LEDGER_ID = PS.SET_OF_BOOKS_ID
AND GL.LEDGER_ID = B.SET_OF_BOOKS_ID
AND PS.APPLICATION_ID = 101
AND (B.EFFECTIVE_DATE BETWEEN PS.START_DATE
AND PS.END_DATE)
AND PS.ADJUSTMENT_PERIOD_FLAG <> 'Y'
AND B.CBC_JE_BATCH_ID = L.CBC_JE_BATCH_ID
AND L.CBC_JE_LINE_NUM IN (SELECT MIN(L.CBC_JE_LINE_NUM)
FROM IGC_CBC_JE_LINES L
WHERE B.CBC_JE_BATCH_ID = L.CBC_JE_BATCH_ID)