CREATE_VIEW_LEDGER_JNL_LINE_FINAL_VERSION.sql

-- DROPPING VIEW "[VIEW_STAGING_LEDGER_JNL_LINE]"
drop view [finance_landing].[VIEW_STAGING_LEDGER_JNL_LINE];

-- CREATING VIEW "[VIEW_STAGING_LEDGER_JNL_LINE]"
CREATE VIEW finance_landing.[VIEW_STAGING_LEDGER_JNL_LINE]
	AS
	Select
	A.ROW_ID,
	DATA_AREA_ID,
	COMPANY,
	REVERSE_DATE_TS,
	MA.ROW_ID AS MAIN_ACCT_ROW_ID,	-- GRABBING ROW ID FROM MAIN ACCT (inner join)
	BRANCH_ID,
	DEPT_ID,
	TAX_ID,
	PROJ_ID,
	TASK_ID,
	EMP_ID,
	ITM_SALES_TAX_GROUP,
	ITM_WITHOLDING_TAX_GROUP_CODE,
	TAX_EXEMPT_NBR,
	SALES_TAX_GROUP,
	LINE_NBR,
	TRANS_DATE_TS,
	DUE_DATE_TS,
	DOCUMENT_DATE_TS,
	JNL_BATCH_NBR,
	LNE_NBR,
	VOUCHER,
	JNL_TEXT,
	PAYMENT_METHOD,
	PAYMENT_REFERENCE,
	DOCUMENT,
	EXCH_RATE,
	PAYMENT_ID,
	POSTING_PROFILE,
	INVOICE,
	DB_AMT,
	CD_AMT
	from
	(
	select  
		'' as ROW_ID,
		LJL.dataAreaId as DATA_AREA_ID,
		company as COMPANY,
		CASE 
			WHEN TRY_CONVERT(DATETIME, LEFT(LJL.ReverseDate, 19), 120) IS NOT NULL THEN
				TRY_CONVERT(DATETIME, LEFT(LJL.ReverseDate, 19), 120)
			ELSE
				NULL
		END AS REVERSE_DATE_TS,
		-- splitting AcountDisplayValue into Acct_ID, Branch_ID, Dept_ID, TAX_ID, PROJ_ID, TASK_ID AND EMP_ID
		(SELECT
			CASE
				WHEN 
					(ISNUMERIC(FINAL.VALUE) = 1 AND NOT CHARINDEX('.',FINAL.VALUE) > 0 AND	len(FINAL.VALUE) > 4)
					THEN FINAL.VALUE
				ELSE NULL
			END AS ACCT_ID
		FROM 
			(SELECT MAX(CASE WHEN SplitValues.RowNumber = 1 THEN SplitValues.Value END) AS Value
			FROM (
				SELECT value AS Value,
					ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
				FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
				) AS SplitValues
			) AS FINAL)
		AS ACCT_ID,
		(SELECT MAX(CASE WHEN SplitValues.RowNumber = 2 THEN SplitValues.Value END) 
		FROM (
		SELECT value AS Value,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber	-- ANYTHING B4 THE FIRST SPLIT RUN ID
		FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
		) AS SplitValues) AS BRANCH_ID,
		CASE 
			WHEN 
				CHARINDEX(':',(SELECT MAX(CASE WHEN SplitValues.RowNumber = 3 THEN SplitValues.Value END) 
					FROM (
						SELECT value AS Value,
							ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
						FROM STRING_SPLIT(ISNULL(ACCOUNTDISPLAYVALUE, ''), '-')
						) AS SplitValues)) > 0 THEN NULL
			ELSE
				(SELECT MAX(CASE WHEN SplitValues.RowNumber = 3 THEN SplitValues.Value END) 
						FROM (
							SELECT value AS Value,
									ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
							FROM STRING_SPLIT(ISNULL(ACCOUNTDISPLAYVALUE, ''), '-')
						) AS SplitValues)
		END AS DEPT_ID,
		(SELECT MAX(CASE WHEN SplitValues.RowNumber = 4 THEN SplitValues.Value END) 
				FROM (
				SELECT value AS Value,
						ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
				FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
				) AS SplitValues) AS TAX_ID,
		(SELECT MAX(CASE WHEN SplitValues.RowNumber = 5 THEN SplitValues.Value END) 
		FROM (
		SELECT value AS Value,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
		FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
		) AS SplitValues) AS PROJ_ID,
		(SELECT MAX(CASE WHEN SplitValues.RowNumber = 6 THEN SplitValues.Value END) 
		FROM (
		SELECT value AS Value,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
		FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
		) AS SplitValues) AS TASK_ID,
		(SELECT MAX(CASE WHEN SplitValues.RowNumber = 7 THEN SplitValues.Value END) 
		FROM (
		SELECT value AS Value,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
		FROM STRING_SPLIT(ISNULL([ACCOUNTDISPLAYVALUE], ''), '-')
		) AS SplitValues)	AS EMP_ID,
		LJL.ItemSalesTaxGroup AS ITM_SALES_TAX_GROUP,
		LJL.ItemWithholdingTaxGroupCode AS ITM_WITHOLDING_TAX_GROUP_CODE,
		LJL.TaxExemptNumber AS TAX_EXEMPT_NBR,
		LJL.SalesTaxGroup AS SALES_TAX_GROUP,
		LJL.LineNumber as LINE_NBR,
		CASE 
			WHEN TRY_CONVERT(DATETIME, LEFT(LJL.TransDate, 19), 120) IS NOT NULL THEN
				TRY_CONVERT(DATETIME, LEFT(LJL.TransDate, 19), 120)
			ELSE
				NULL
		END AS TRANS_DATE_TS,
		CASE 
			WHEN TRY_CONVERT(DATETIME, LEFT(LJL.DueDate, 19), 120) IS NOT NULL THEN
				TRY_CONVERT(DATETIME, LEFT(LJL.DueDate, 19), 120)
			ELSE
				NULL
		END AS DUE_DATE_TS,
		CASE 
			WHEN TRY_CONVERT(DATETIME, LEFT(LJL.DocumentDate, 19), 120) IS NOT NULL THEN
				TRY_CONVERT(DATETIME, LEFT(LJL.DocumentDate, 19), 120)
			ELSE
				NULL
		END AS DOCUMENT_DATE_TS,
		LJL.JournalBatchNumber AS JNL_BATCH_NBR,
		LJL.LineNumber AS LNE_NBR,
		LJL.Voucher as VOUCHER,
		LJL.Text AS JNL_TEXT,
		LJL.PaymentMethod as PAYMENT_METHOD,
		LJL.PaymentReference as PAYMENT_REFERENCE,
		LJL.Document AS DOCUMENT,
		CASE 
			WHEN TRY_CONVERT(decimal(6, 3), LJL.ExchRate) IS NOT NULL THEN
				TRY_CONVERT(decimal(6, 3), LJL.ExchRate)
			ELSE
				NULL
		END AS EXCH_RATE,
		LJL.PaymentId AS PAYMENT_ID,
		LJL.PostingProfile AS POSTING_PROFILE,
		LJL.Invoice AS INVOICE,
		CASE 
			WHEN TRY_CONVERT(decimal(18, 3), LJL.DebitAmount) IS NOT NULL THEN
				TRY_CONVERT(decimal(18, 3), LJL.DebitAmount)
			ELSE
				NULL
		END AS DB_AMT,
		CASE 
			WHEN TRY_CONVERT(decimal(18, 3), LJL.CreditAmount) IS NOT NULL THEN
				TRY_CONVERT(decimal(18, 3), LJL.CreditAmount)
			ELSE
				NULL
		END AS CD_AMT,
		getdate() as DL_CREATE_DATE,
		GETDATE() as DL_UPD_DATE,
		'DL-ETL' AS DL_CREATE_BY,
		'DL-ETL' AS DL_UPD_BY
from finance_landing.LedgerJournalLines LJL) A
INNER JOIN finance.MAIN_ACCT MA ON A.ACCT_ID = MA.ACCT_ID

-- checking for orphan records in FKs
Select distinct BRANCH_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where BRANCH_ID not in 
(SELECT BRANCH_ID from finance.BRANCH)

Select distinct TAX_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where TAX_ID not in 
(SELECT TAX_ID from finance.tax)

Select distinct PROJ_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where PROJ_ID not in 
(SELECT PROJ_ID from finance.project)

Select distinct TASK_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where TASK_ID not in 
(SELECT TASK_ID from finance.task)

Select distinct EMP_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where EMP_ID not in 
(SELECT EMP_ID from finance.EMPLOYEE)

Select distinct DEPT_ID 
FROM [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where DEPT_ID not in 
(SELECT DEPT_ID from finance.dept)

select distinct MAIN_ACCT_ROW_ID 
from [FINANCE_LANDING].[VIEW_STAGING_LEDGER_JNL_LINE] 
where MAIN_ACCT_ROW_ID not in 
(select distinct row_id from finance.main_acct)

-- insert records to LEDGER_JNL_LINE from view
INSERT INTO FINANCE.LEDGER_JNL_LINE   
(DATA_AREA_ID,COMPANY,MAIN_ACCT_ROW_ID,BRANCH_ID,DEPT_ID,TAX_ID,PROJ_ID,TASK_ID,EMP_ID,TRANS_DATE_TS,DUE_DATE_TS,DOCUMENT_DATE_TS,REVERSE_DATE_TS,JNL_BATCH_NBR,LINE_NBR,VOUCHER,JNL_TEXT,
PAYMENT_METHOD,PAYMENT_REFERENCE,DOCUMENT,EXCH_RATE,PAYMENT_ID,POSTING_PROFILE,SALES_TAX_GROUP,INVOICE,ITM_SALES_TAX_GROUP,ITM_WITHHOLDING_TAX_GROUP_CODE,TAX_EXEMPT_NBR,DR_AMT,CR_AMT,LOADID)
SELECT  DATA_AREA_ID,COMPANY,MAIN_ACCT_ROW_ID,NULLIF(BRANCH_ID,'') AS BRANCH_ID,NULLIF(DEPT_ID, '') AS DEPT_ID,NULLIF(TAX_ID,'') AS TAX_ID,NULLIF(PROJ_ID, '') AS PROJ_ID,NULLIF(TASK_ID, '') as TASK_ID, NULLIF(EMP_ID,'') AS EMP_ID,TRANS_DATE_TS,DUE_DATE_TS,DOCUMENT_DATE_TS,REVERSE_DATE_TS,JNL_BATCH_NBR,LINE_NBR,VOUCHER,JNL_TEXT,
PAYMENT_METHOD,PAYMENT_REFERENCE,DOCUMENT,EXCH_RATE,PAYMENT_ID,POSTING_PROFILE,SALES_TAX_GROUP,INVOICE,ITM_SALES_TAX_GROUP,ITM_WITHOLDING_TAX_GROUP_CODE,TAX_EXEMPT_NBR,DB_AMT,CD_AMT,''
FROM [finance_landing].[VIEW_STAGING_LEDGER_JNL_LINE];

-- view records
select * from finance_landing.[VIEW_STAGING_LEDGER_JNL_LINE]
select * from FINANCE.LEDGER_JNL_LINE
	-- NOTE: any "bad data" or empty recrods classified as NULL