VIEW: APPS.GL_RECURRING_VALID_PERIODS_V

Source

View Text - Preformatted

SELECT ps.period_name PERIOD_NAME, ps.LEDGER_ID LEDGER_ID, ps.CLOSING_STATUS CLOSING_STATUS, ps.START_DATE START_DATE, ps.END_DATE END_DATE, ps.PERIOD_YEAR PERIOD_YEAR, ps.PERIOD_NUM PERIOD_NUM, rb.RECURRING_BATCH_ID RECURRING_BATCH_ID FROM gl_period_statuses ps, gl_recurring_batches rb WHERE ps.application_id = 101 AND ps.closing_status IN ('O', 'F') AND EXISTS ( SELECT 'Valid header exists' FROM gl_recurring_headers rh WHERE rh.recurring_batch_id = rb.recurring_batch_id AND ((decode( greatest( sysdate, ps.start_date ), sysdate, least( sysdate, ps.end_date ), ps.start_date ) >= nvl( rh.start_date_active, ps.start_date )) OR (nvl( rh.start_date_active, ps.start_date ) BETWEEN ps.start_date and ps.end_date)) AND decode( greatest( sysdate, ps.start_date ), sysdate, least( sysdate, ps.end_date ), ps.start_date ) <= nvl( rh.end_date_active, ps.end_date ))

View Text - HTML Formatted

SELECT PS.PERIOD_NAME PERIOD_NAME
, PS.LEDGER_ID LEDGER_ID
, PS.CLOSING_STATUS CLOSING_STATUS
, PS.START_DATE START_DATE
, PS.END_DATE END_DATE
, PS.PERIOD_YEAR PERIOD_YEAR
, PS.PERIOD_NUM PERIOD_NUM
, RB.RECURRING_BATCH_ID RECURRING_BATCH_ID
FROM GL_PERIOD_STATUSES PS
, GL_RECURRING_BATCHES RB
WHERE PS.APPLICATION_ID = 101
AND PS.CLOSING_STATUS IN ('O'
, 'F')
AND EXISTS ( SELECT 'VALID HEADER EXISTS'
FROM GL_RECURRING_HEADERS RH
WHERE RH.RECURRING_BATCH_ID = RB.RECURRING_BATCH_ID
AND ((DECODE( GREATEST( SYSDATE
, PS.START_DATE )
, SYSDATE
, LEAST( SYSDATE
, PS.END_DATE )
, PS.START_DATE ) >= NVL( RH.START_DATE_ACTIVE
, PS.START_DATE )) OR (NVL( RH.START_DATE_ACTIVE
, PS.START_DATE ) BETWEEN PS.START_DATE
AND PS.END_DATE))
AND DECODE( GREATEST( SYSDATE
, PS.START_DATE )
, SYSDATE
, LEAST( SYSDATE
, PS.END_DATE )
, PS.START_DATE ) <= NVL( RH.END_DATE_ACTIVE
, PS.END_DATE ))