| Value |
 Expression: "DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' DECLARE @StartCalendarPeriodLoad varchar(6) = '" + @[User::StartCalendarPeriodLoad] + "'
SELECT
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) + '01'
AS INT) AS CalendarPeriodDate
,CAST(ISNULL(GlTrans.POSTED_DATE, '19000101') AS int) AS PostedDate
, CASE
WHEN SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) > '06' THEN
CAST(CAST(CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) AS INT) + 1 AS VARCHAR(4)) + GlTrans.GL_ACCT_NO AS varchar(29))
ELSE
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + GlTrans.GL_ACCT_NO AS varchar(29))
END AS YearGlAccountNumberAK ,CAST(CAST(GlTrans.CALENDAR_PERIOD as varchar) + GlTrans.JOURNAL_TYPE +
REPLICATE('0',5-LEN(GlTrans.JOURNAL_NO))+CONVERT(VARCHAR,GlTra
DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' DECLARE @StartCalendarPeriodLoad varchar(6) = '200607'
SELECT
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) + '01'
AS INT) AS CalendarPeriodDate
,CAST(ISNULL(GlTrans.POSTED_DATE, '19000101') AS int) AS PostedDate
, CASE
WHEN SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) > '06' THEN
CAST(CAST(CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) AS INT) + 1 AS VARCHAR(4)) + GlTrans.GL_ACCT_NO AS varchar(29))
ELSE
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + GlTrans.GL_ACCT_NO AS varchar(29))
END AS YearGlAccountNumberAK ,CAST(CAST(GlTrans.CALENDAR_PERIOD as varchar) + GlTrans.JOURNAL_TYPE +
REPLICATE('0',5-LEN(GlTrans.JOURNAL_NO))+CONVERT(VARCHAR,GlTrans.JOURNAL_NO) +
REPLICATE('0',5-LEN(GlTrans.LINE_NO))+CONVERT(VARCHAR,GlTrans.LINE_NO) as Varchar(18))
as GlDetailAK
,CAST(SUBSTRING(GlTrans.GL_ACCT_NO,1,16) as varchar(16)) as OrgUnitAK
,CAST(SUBSTRING(GlTrans.GL_ACCT_NO,17,9) as varchar(9)) as GlCodeAK
,GlTrans.AMOUNT as JournalAmount
,CASE
WHEN GlTrans.JOURNAL_TYPE = 'BT' THEN AMOUNT
ELSE 0
END as BudgetAmount
,CASE
WHEN GlTrans.JOURNAL_TYPE = 'CO' THEN AMOUNT
ELSE 0
END as CommitmentAmount
,CASE
WHEN GlTrans.JOURNAL_TYPE = 'EN' THEN AMOUNT
ELSE 0
END as EncumbranceAmount
,CASE
WHEN GlTrans.JOURNAL_TYPE = 'AP' THEN AMOUNT
WHEN GlTrans.JOURNAL_TYPE = 'CC' THEN AMOUNT
WHEN GlTrans.JOURNAL_TYPE = 'JE' THEN AMOUNT
WHEN GlTrans.JOURNAL_TYPE = 'PR' THEN AMOUNT
ELSE 0
END as ActualsAmount
FROM GL_TRANS_FILE GlTrans
WHERE (GlTrans.ISN_GL_TRANS_FILE) IN
(
/* Select changed records from GL Trans File */
SELECT ISN_GL_TRANS_FILE
FROM GL_TRANS_FILE
WHERE (
GL_TRANS_FILE.RowUpdatedOn >= @IncrementalBeginDtTm
AND GL_TRANS_FILE.RowUpdatedOn <= @IncrementalEndDtTm AND GL_TRANS_FILE.CALENDAR_PERIOD >= @StartCalendarPeriodLoad )
) ORDER BY GlDetailAK
|
| Expression |
"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' DECLARE @StartCalendarPeriodLoad varchar(6) = '" + @[User::StartCalendarPeriodLoad] + "'
SELECT
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) + '01'
AS INT) AS CalendarPeriodDate
,CAST(ISNULL(GlTrans.POSTED_DATE, '19000101') AS int) AS PostedDate
, CASE
WHEN SUBSTRING(GlTrans.CALENDAR_PERIOD,5,2) > '06' THEN
CAST(CAST(CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) AS INT) + 1 AS VARCHAR(4)) + GlTrans.GL_ACCT_NO AS varchar(29))
ELSE
CAST(SUBSTRING(GlTrans.CALENDAR_PERIOD,1,4) + GlTrans.GL_ACCT_NO AS varchar(29))
END AS YearGlAccountNumberAK ,CAST(CAST(GlTrans.CALENDAR_PERIOD as varchar) + GlTrans.JOURNAL_TYPE +
REPLICATE('0',5-LEN(GlTrans.JOURNAL_NO))+CONVERT(VARCHAR,GlTra |
Properties
