CREATE FUNCTION [dbo].[IF_GetPaymentBizDoc]
-- Replaces fn_GetPaymentBizDoc
	(@numBizDocsPaymentDetId NUMERIC(8),
	@numDomainId INT)
RETURNS TABLE AS
 RETURN

WITH SourceData AS (
	SELECT 
		Amount		= cast(SUM(monAmount) as varchar(50)),
		numCheckNo	= isnull( CAST(numCheckNo AS VARCHAR(50)),'No-Chek'),
		vcReference = isnull(vcReference,'No-Ref'),
		vcMemo		= isnull(vcMemo,'No-Memo' )  
	FROM VIEW_BIZDOCPAYMENT -- no definition in scripts
	WHERE numBizDocsPaymentDetId = @numBizDocsPaymentDetId 
		AND numDomainId = @numDomainId
	GROUP BY numCheckNo, vcReference, vcMemo
)
SELECT Narration = STUFF((
	SELECT ',' + 
			'Ref: ' + vcReference +
			' Memo: ' + vcMemo + 
			' Chek No: ' + numCheckNo + 
			' Amount: ' + Amount 
	FROM SourceData 
	ORDER BY numCheckNo, vcReference, vcMemo 
	FOR XML PATH('')),1,1,'') 

SELECT CAST(ID AS NUMERIC(9))
INTO #Accounts
FROM dbo.SplitIDs(@vcAccountId, ',')

SELECT     
    h.numDomainId,  
    coa.numAccountId,  
	CompanyName = ci.vcCompanyName,  
	dm.numDivisionID,
	0 AS Opening,
    CONVERT(VARCHAR(20), SUM(ISNULL(d.numDebitAmt, 0))) TotalDebit,
    CONVERT(VARCHAR(20), SUM(ISNULL(d.numCreditAmt, 0))) TotalCredit,
    (SUM(ISNULL(d.numDebitAmt, 0)) -  SUM(ISNULL(d.numCreditAmt, 0)))  AS Closing,
    COA.numParntAcntTypeId
	
FROM dbo.General_Journal_Header h 

INNER JOIN dbo.General_Journal_Details d 
	ON h.numJournal_Id = d.numJournalId 

LEFT JOIN dbo.Chart_Of_Accounts coa
	ON d.numChartAcntId = coa.numAccountId 

LEFT JOIN dbo.DivisionMaster dm 

LEFT JOIN dbo.CompanyInfo ci 
	ON dm.numCompanyID = ci.numCompanyId 
	ON d.numCustomerId = dm.numDivisionID 
	
WHERE
    h.numDomainId = @numDomainID 
    AND dm.numDivisionID IS NOT null -- converts dm to IJ
    AND coa.numAccountId IN (SELECT ID FROM #Accounts)

SELECT     
	h.numJournal_Id, 
	h.datEntry_Date, 
	h.varDescription, 
    ISNULL(dbo.VIEW_BIZPAYMENT.Narration,
    h.varDescription) AS BizPayment, 
    d.numTransactionId,
    
    CASE 
		WHEN isnull(h.numReturnID, 0) <> 0 
		THEN ' Chek No: ' + isnull( CAST(CH.numCheckNo AS VARCHAR(50)),'No-Cheq') 
		ELSE  dbo.VIEW_JOURNALCHEQ.Narration END AS CheqNo,
		 
    h.numDomainId, -- ######################## 
    'Biz Doc Id: ' + dbo.OpportunityBizDocs.vcBizDocID AS BizDocID, 
    h.numOppBizDocsId, 
    d.vcReference AS TranRef, 
    d.varDescription AS TranDesc, 
    d.numDebitAmt, -- ############################ 
    d.numCreditAmt, -- ############################### 
    coa.numAccountId, -- ################################# 
    coa.vcAccountName,
    CASE 
		WHEN isnull(h.numCheckHeaderID, 0) <> 0 THEN + 'Checks' 
		WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 0 THEN 'Cash' 
		WHEN isnull(h.numCashCreditCardId, 0) <> 0 AND ccc.bitMoneyOut = 1 AND ccc.bitChargeBack = 1 THEN 'Charge' 
		WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 1 THEN 'Deposit' 
		WHEN isnull(h.numDepositId, 0) <> 0 AND DM.tintDepositePage = 2 THEN 'Receved Pmt' 
--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN ''Receive Amt'' 
--WHEN isnull(h.numBizDocsPaymentDetId, 0) <> 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN ''Vendor Amt'' 
		WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 1 THEN 'BizDocs Invoice' 
		WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) <> 0 AND isnull(h.numBizDocsPaymentDetId, 0) = 0 AND dbo.OpportunityMaster.tintOppType = 2 THEN 'BizDocs Purchase' 
		WHEN isnull(h.numCategoryHDRID, 0) <> 0 THEN 'Time And Expenses' 
		WHEN isnull(h.numBillID, 0) <> 0 THEN 'Bill'
		WHEN isnull(h.numBillPaymentID, 0) <> 0 THEN 'Pay Bill'
		WHEN isnull(h.numReturnID, 0) <> 0 THEN 

			CASE WHEN RH.tintReturnType=1 AND RH.tintReceiveType=1 THEN 'Sales Return Refund'
			WHEN RH.tintReturnType=1 AND RH.tintReceiveType=2 THEN 'Sales Return Credit Memo'
			WHEN RH.tintReturnType=2 AND RH.tintReceiveType=2 THEN 'Purchase Return Credit Memo'
			WHEN RH.tintReturnType=3 AND RH.tintReceiveType=2 THEN 'Credit Memo'
			WHEN RH.tintReturnType=4 AND RH.tintReceiveType=1 THEN 'Refund' END
 
		WHEN isnull(h.numOppId, 0) <> 0 AND isnull(h.numOppBizDocsId, 0) = 0 THEN 'PO Fulfillment'
		WHEN h.numJournal_Id <> 0 THEN 'Journal' END AS TransactionType,

	dbo.CompanyInfo.vcCompanyName AS CompanyName, -- ############################### 
	h.numCheckHeaderID,
	 
	h.numCashCreditCardId, 
	h.numOppId, 
	h.numDepositId, 
	h.numCategoryHDRID, 
	dbo.TimeAndExpense.tintTEType, 
	dbo.TimeAndExpense.numCategory, 
	dbo.TimeAndExpense.dtFromDate, 
	dbo.TimeAndExpense.numUserCntID,
	dbo.DivisionMaster.numDivisionID, -- ################################
	ISNULL(d.bitCleared,0) AS bitCleared,
	ISNULL(d.bitReconcile,0) AS bitReconcile,
	isnull(h.numBillID, 0) AS numBillID,
	ISNULL(h.numBillPaymentID,0) AS numBillPaymentID,
	ISNULL(d.numClassID,0) AS numClassID,
	ISNULL(d.numProjectID,0) AS numProjectID,
	isnull(h.numReturnID, 0) AS numReturnID,
	d.numCurrencyID
	
FROM dbo.General_Journal_Header h -- KEEP 

INNER JOIN dbo.General_Journal_Details d -- KEEP 
	ON h.numJournal_Id = d.numJournalId 

INNER JOIN dbo.Chart_Of_Accounts coa -- KEEP
	ON d.numChartAcntId = coa.numAccountId 
	
LEFT OUTER JOIN dbo.TimeAndExpense 
	ON h.numCategoryHDRID = dbo.TimeAndExpense.numCategoryHDRID 
	
LEFT OUTER JOIN dbo.DivisionMaster -- KEEP 

LEFT OUTER JOIN dbo.CompanyInfo -- KEEP
	ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId 
	ON d.numCustomerId = dbo.DivisionMaster.numDivisionID 
	
LEFT OUTER JOIN dbo.OpportunityMaster 
	ON h.numOppId = dbo.OpportunityMaster.numOppId 

LEFT OUTER JOIN dbo.CashCreditCardDetails ccc
	ON h.numCashCreditCardId = ccc.numCashCreditId 

LEFT OUTER JOIN dbo.OpportunityBizDocs 
	ON h.numOppBizDocsId = dbo.OpportunityBizDocs.numOppBizDocsId 

LEFT OUTER JOIN dbo.VIEW_JOURNALCHEQ 
	ON (h.numCheckHeaderID = dbo.VIEW_JOURNALCHEQ.numCheckHeaderID 
	or (h.numBillPaymentID=dbo.VIEW_JOURNALCHEQ.numReferenceID and dbo.VIEW_JOURNALCHEQ.tintReferenceType=8))

LEFT OUTER JOIN dbo.VIEW_BIZPAYMENT 
	ON h.numBizDocsPaymentDetId = dbo.VIEW_BIZPAYMENT.numBizDocsPaymentDetId 

LEFT OUTER JOIN dbo.DepositMaster DM 
	ON DM.numDepositId = h.numDepositId  

LEFT OUTER JOIN dbo.ReturnHeader RH 
	ON RH.numReturnHeaderID = h.numReturnID 

LEFT OUTER JOIN dbo.CheckHeader CH 
	ON CH.numReferenceID = RH.numReturnHeaderID AND CH.tintReferenceType=10

LEFT OUTER JOIN dbo.DivisionMaster -- KEEP 

LEFT OUTER JOIN dbo.CompanyInfo -- KEEP
	ON dbo.DivisionMaster.numCompanyID = dbo.CompanyInfo.numCompanyId 
	ON d.numCustomerId = dbo.DivisionMaster.numDivisionID 

    AND dm.numDivisionID IS NOT null -- converts dm to IJ