USE [SVCW]
GO

/****** Object:  View [dbo].[Z_AISLING_ALL_PROJECT_TRANSACTIONS]    Script Date: 2/3/2020 1:14:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER VIEW [dbo].[Z_AISLING_ALL_PROJECT_TRANSACTIONS]
AS
SELECT PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID]
	  ,'Timesheet' as 'Transaction Type'
	  ,ATR.[Journal Entry]
      ,TS.[PATSNO] 'Transaction Number'
      ,TS.[EMPLOYID] 'Master Record'
	  ,EE.LASTNAME as 'Master Record Detail'
	  ,TSH.PACOMM 'Reference'
	  ,'' AS 'Vendor Doc Number'
	  ,'' as 'Submodule Reference'
	  ,'' as 'PO Number'
	  ,'' as 'Receipt Type'
	  ,'' as 'Receipt Paid with Check Number'
	  ,'' as 'Check Paid Date'
      ,TSH.POSTEDDT ' Transaction Date'
      ,TS.[PACOSTCATID] 'Cost Category'
	  ,AC.[Account Number] AS 'COGS Account'
	  ,AC.[Account Description] AS 'COGS ACCT Desc'
      ,TS.[PAQtyQ] 'Quantity'
      ,TS.[PAUnit_of_Measure] 'U of M'
      ,TS.[PAUNITCOST] 'Cost Per Unit'
      ,TS.[PATOTALOVERH] 'Total Overhead'
      ,TS.[PAEXTCOST] 'Extended Cost'
      ,TS.[PATOTCST] 'Total Cost'
	  ,'0' AS 'Total of Invoice'
      ,TS.[PABILRATE] 'Billing Rate Per Unit'
      ,TS.[PAACREV] 'Recognized Revenue'
      ,TS.[PABilled_QtyN] 'Billed Quanitity'
      ,TS.[PA_Billed_Profit_Amount_] 'Billed Profit'
      ,TS.[PABilled_Amount] 'Total Billed'
      ,TS.[PABilled_Freight] 'Billed Frieght'
      ,TS.[PABilled_Discount] 'Billing Discount'
      ,TS.[PABilling_StatusN] 'Billing Status'
  FROM [PAProjects] PH LEFT JOIN
       [Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
	   [PA30101] TS on PH.[Project Number]=TS.[PAPROJNUMBER] LEFT JOIN
	   [PA30100] TSH ON TS.[PATSNO]=TSH.PATSNO LEFT JOIN
	   [UPR00100] EE ON TS.EMPLOYID=EE.EMPLOYID LEFT JOIN
	   [Accounts] AC on TS.PACogs_Idx=AC.[Account Index]LEFT JOIN
	   [AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE TS.[PATSNO]=ATR.[Originating Document Number] AND TS.PACogs_Idx=ATR.[Account Index])
  WHERE TSH.[PATSNO] IS NOT NULL

UNION ALL

SELECT PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID]
	  ,'Equipment Log'  as 'Transaction Type'
	  ,ATR.[Journal Entry]
      ,EQ.[PAEQLOGNO] 'Transaction Number'
      ,EQ.[PAEQUIPTID] 'Master Record'
	  ,EQN.PAEQNME as 'Master Record Detail'
	  ,EQH.PACOMM 'Reference'
	  ,'' AS 'Vendor Doc Number'
	  ,'' as 'Submodule Reference'
	  ,'' as 'PO Number'
	  ,'' as 'Receipt Type'
	  ,'' as 'Receipt Paid with Check Number'
	  ,'' as 'Check Paid Date'
      ,EQH.POSTEDDT ' Transaction Date'
      ,EQ.[PACOSTCATID] 'Cost Category'
	  ,AC.[Account Number] AS 'COGS Account'
	  ,AC.[Account Description] AS 'COGS ACCT Desc'
      ,EQ.[PAQtyQ] 'Quantity'
      ,EQ.[PAUnit_of_Measure] 'U of M'
      ,EQ.[PAUNITCOST] 'Cost Per Unit'
      ,EQ.[PATOTALOVERH] 'Total Overhead'
      ,EQ.[PAEXTCOST] 'Extended Cost'
      ,EQ.[PATOTCST] 'Total Cost'
	  ,'0' AS 'Total of Invoice'
      ,EQ.[PABILRATE] 'Billing Rate Per Unit'
      ,EQ.[PAACREV] 'Recognized Revenue'
      ,EQ.[PABilled_QtyN]'Billed Quanitity'
      ,EQ.[PA_Billed_Profit_Amount_] 'Billed Profit'
      ,EQ.[PABilled_Amount]'Total Billed'
      ,EQ.[PABilled_Freight] 'Billed Frieght'
      ,EQ.[PABilled_Discount] 'Billing Discount'
      ,EQ.[PABilling_StatusN] 'Billing Status'
  FROM [PAProjects] PH LEFT JOIN
       [Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
	   [PA30201] EQ on PH.[Project Number]=EQ.[PAPROJNUMBER] LEFT JOIN
	   [PA30200] EQH ON EQ.[PAEQLOGNO]=EQH.PAEQLOGNO LEFT JOIN
	   [PA00701] EQN ON EQ.PAEQUIPTID=EQN.PAEQUIPTID LEFT JOIN
	   [Accounts] AC on EQ.PACogs_Idx=AC.[Account Index]LEFT JOIN
	   [AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE EQ.[PAEQLOGNO]=ATR.[Originating Document Number] AND EQ.PACogs_Idx=ATR.[Account Index])
  WHERE EQH.[PAEQLOGNO] IS NOT NULL
	   
UNION ALL

SELECT PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID]
      ,'Miscellaneous Log' as 'Transaction Type'
	  ,ATR.[Journal Entry]
      ,ML.[PAMISCLDOCNO]'Transaction Number'
      ,ML.[PSMISCID] 'Master Record'
	  ,NULL as 'Master Record Detail'
	  ,MLH.PACOMM 'Reference'
	  ,'' AS 'Vendor Doc Number'
	  ,'' as 'Submodule Reference'
	  ,'' as 'PO Number'
	  ,'' as 'Receipt Type'
	  ,'' as 'Receipt Paid with Check Number'
	  ,'' as 'Check Paid Date'
       ,ML.[PADT] ' Transaction Date'
      ,ML.[PACOSTCATID] 'Cost Category'
	  ,AC.[Account Number] AS 'COGS Account'
	  ,AC.[Account Description] AS 'COGS ACCT Desc'
      ,ML.[PAQtyQ] 'Quantity'
      ,ML.[PAUnit_of_Measure] 'U of M'
      ,ML.[PAUNITCOST] 'Cost Per Unit'
      ,ML.[PATOTALOVERH] 'Total Overhead'
      ,ML.[PAEXTCOST] 'Extended Cost'
      ,ML.[PATOTCST] 'Total Cost'
	  ,'0' AS 'Total of Invoice'
      ,ML.[PABILRATE] 'Billing Rate Per Unit'
      ,ML.[PAACREV] 'Recognized Revenue'
      ,ML.[PABilled_QtyN]'Billed Quanitity'
      ,ML.[PA_Billed_Profit_Amount_] 'Billed Profit'
      ,ML.[PABilled_Amount]'Total Billed'
      ,ML.[PABilled_Freight] 'Billed Frieght'
      ,ML.[PABilled_Discount] 'Billing Discount'
      ,ML.[PABilling_StatusN] 'Billing Status'
  FROM [PAProjects] PH LEFT JOIN
       [Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
	   [PA30301] ML on PH.[Project Number]=ML.[PAPROJNUMBER]  LEFT JOIN
	   [PA30300] MLH ON ML.PAMISCLDOCNO=MLH.PAMISCLDOCNO  LEFT JOIN
	   [Accounts] AC on ML.PACogs_Idx=AC.[Account Index] LEFT JOIN
	   [AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE ML.[PAMISCLDOCNO]=ATR.[Originating Document Number] AND ML.PACogs_Idx=ATR.[Account Index])
  WHERE MLH.[PAMISCLDOCNO] IS NOT NULL

UNION ALL
SELECT PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID] 
      ,'Purchasing' as 'Transaction Type'
	  ,ATR.[Journal Entry]
       ,RCT.[PAVIDN]'Transaction Number'
      ,RCT.[ITEMNMBR]'Master Record'
	  ,POP.VENDNAME  'Master Record Detail'
      ,POP.REFRENCE 'Reference'
	  ,POP.VNDDOCNM AS 'Vendor Doc Number'
	  ,'' as 'Submodule Reference'
	  ,POL.PONUMBER as 'PO Number'
	  ,CASE POP.POPTYPE
	    WHEN 1 THEN 'Shipment'
		WHEN 2 THEN 'Invoice'
		WHEN 3 THEN 'Shipment/Invoice'
		WHEN 4 THEN 'Return'
		WHEN 5 THEN 'Return w/ Credit'
		WHEN 6 THEN 'IV Return'
		WHEN 7 THEN 'IV Return w/ Credit'
		WHEN 8 THEN 'In-Transit Inventory'
	    END as 'Receipt Type'
	  ,APP.APFRDCNM 'Receipt Paid with Check Number'
	  ,APP.DOCDATE as 'Check Paid Date'
      ,POP.[GLPOSTDT] ' Transaction Date'
      ,RCT.[PACOSTCATID] 'Cost Category'
	  ,AC.[Account Number] AS 'COGS Account'
	  ,AC.[Account Description] AS 'COGS ACCT Desc'
      ,CASE WHEN POP.POPTYPE IN (5,4,6,7)  THEN  RCT.[PABase_Qty]*-1 ELSE RCT.[PABase_Qty]  END AS 'Quantity'
	  ,RCT.[UOMSCHDL] as 'U of M'
      ,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PABase_Unit_Cost]*-1 ELSE RCT.[PABase_Unit_Cost]  END 'UNITCOST'
      ,RCT.[PATOTALOVERH]'Total Overhead'
      ,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PAShipmentExtCost]*-1 ELSE RCT.[PAShipmentExtCost] END AS 'Extended Cost'
	  ,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PAShipmentExtCost]*-1 ELSE RCT.[PAShipmentExtCost]  END 'Total Cost'
	  ,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN POP.[SUBTOTAL]*-1 ELSE POP.[SUBTOTAL]END   AS 'Total of Invoice'
      ,RCT.[PAORIGBILLRATE]'Billing Rate Per Unit'
      ,RCT.[PAORIACCRREV]'Recognized Revenue'
      ,RCT.[PABilled_QtyN]'Billed Quanitity'
      ,RCT.[PA_Billed_Profit_Amount_] 'Billed Profit'
      ,RCT.[PABilled_Amount]'Total Billed'
      ,RCT.[PABilledProfitPercentage] as 'Billed Frieght'
      ,RCT.[PABilled_Discount] 'Billing Discount'
      ,RCT.[PABilling_StatusN]'Billing Status'
   FROM [PAProjects] PH LEFT JOIN
       [Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
       [PA31102] RCT ON PH.[Project Number]=RCT.[PAPROJNUMBER] LEFT JOIN
       [PA31101] RCH ON RCT.[PAVIDN]=RCH.PAVIDN LEFT JOIN
	   [POP30300] POP ON RCT.PAVIDN=POP.POPRCTNM  LEFT JOIN
	   [POP30310] POL ON RCT.PAVIDN=POL.POPRCTNM AND RCT.RCPTLNNM=POL.RCPTLNNM AND RCT.ITEMNMBR=POL.ITEMNMBR LEFT JOIN
	   (SELECT * FROM PM30300 WHERE APTODCNM IN (SELECT TOP 1 (APTODCNM) FROM PM30300 GROUP BY APTODCNM))
	   as APP ON POP.VNDDOCNM =APP.APTODCNM and POP.VENDORID=APP.VENDORID LEFT JOIN
	   --[PM30300] APP ON POP.VNDDOCNM =APP.APTODCNM and POP.VENDORID=APP.VENDORID  LEFT JOIN
	   [Accounts] AC on RCT.PACogs_Idx=AC.[Account Index]LEFT JOIN
	   [AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE RCT.PAVIDN=ATR.[Originating Document Number] AND RCT.PACogs_Idx=ATR.[Account Index] )
  WHERE RCT.[PAVIDN] IS NOT NULL
	   AND POP.[GLPOSTDT] IS NOT NULL
	   and POP.POPRCTNM <> 'RCT100153'

GROUP BY
PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID] 
       ,RCT.[PAVIDN]
      ,RCT.[ITEMNMBR]
	  ,POP.REFRENCE 
	  ,POP.VENDNAME 
      ,RCH.PACOMM 
	  ,POP.VNDDOCNM 
	  ,POL.PONUMBER 
	  ,POP.POPTYPE
	  ,APP.APFRDCNM 
	  ,APP.DOCDATE 
	  ,ATR.[Journal Entry]
      ,POP.[GLPOSTDT]
      ,RCT.[PACOSTCATID]
	  ,RCT.[PATOTALOVERH]
	  ,AC.[Account Number]
	  ,AC.[Account Description]
      ,RCT.[PABase_Qty]
	  ,RCT.[UOMSCHDL]
      ,RCT.[PABase_Unit_Cost]
	  ,RCT.[PAShipmentExtCost]
      ,RCT.[PAORIGBILLRATE]
      ,RCT.[PAORIACCRREV]
      ,RCT.[PABilled_QtyN]
      ,RCT.[PA_Billed_Profit_Amount_] 
      ,RCT.[PABilled_Amount]
      ,RCT.[PABilledProfitPercentage] 
      ,RCT.[PABilled_Discount] 
      ,RCT.[PABilling_StatusN]
	  ,POP.SUBTOTAL

	  UNION ALL

SELECT PH.[Customer Number] 
      ,CUS.[Customer Name]
      ,PH.[Contract Number]
      ,PH.[Contract ID]
      ,PH.[Project ID]
      ,PH.[Project Name]
      ,PH.[Project Number]
      ,PH.[Project Class ID]
      ,PH.[Project Type]
      ,PH.[Accounting Method]
      ,PH.[Status]
      ,PH.[Department]
      ,PH.[Estimator ID]
      ,PH.[Project Manager ID]
      ,PH.[Business Manager ID]
      ,PH.[Location ID]
      ,PH.[Salesperson ID]
      ,'Inventory Transaction' as 'Transaction Type'
	  ,ATR.[Journal Entry]
      ,IV.[PAIV_Document_No]'Transaction Number'
      ,IV.[ITEMNMBR] 'Master Record'
	  ,IT.ITEMDESC as 'Master Record Detail'
	  ,IVH.PACOMM 'Reference'
	  ,'' AS 'Vendor Doc Number'
	  ,'' as 'Submodule Reference'
	  ,'' as 'PO Number'
	  ,'' as 'Receipt Type'
	  ,'' as 'Receipt Paid with Check Number'
	  ,'' as 'Check Paid Date'
       ,IV.[PADT] ' Transaction Date'
      ,IV.[PACOSTCATID] 'Cost Category'
	  ,AC.[Account Number] AS 'COGS Account'
	  ,AC.[Account Description] AS 'COGS ACCT Desc'
      ,IV.[PAQtyQ] 'Quantity'
      ,IV.[PAUnit_of_Measure] 'U of M'
      ,IV.[PAUNITCOST] 'Cost Per Unit'
      ,IV.[PATOTALOVERH] 'Total Overhead'
      ,IV.[PAEXTCOST] 'Extended Cost'
      ,IV.[PATOTCST] 'Total Cost'
	  ,'0' AS 'Total of Invoice'
      ,IV.[PABILRATE] 'Billing Rate Per Unit'
      ,IV.[PAACREV] 'Recognized Revenue'
      ,IV.[PABilled_QtyN]'Billed Quanitity'
      ,IV.[PA_Billed_Profit_Amount_] 'Billed Profit'
      ,IV.[PABilled_Amount]'Total Billed'
      ,IV.[PABilled_Freight] 'Billed Frieght'
      ,IV.[PABilled_Discount] 'Billing Discount'
      ,IV.[PABilling_StatusN] 'Billing Status'
  FROM [PAProjects] PH LEFT JOIN
       [Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
	   [PA30901] IV on PH.[Project Number]=IV.[PAPROJNUMBER]  LEFT JOIN
	   [PA30900] IVH ON IV.PAIV_Document_No=IVH.PAIV_Document_No LEFT JOIN
	   [IV00101] IT	 ON IV.ITEMNMBR=IT.ITEMNMBR LEFT JOIN
	   [Accounts] AC on IV.PACogs_Idx=AC.[Account Index]LEFT JOIN
	   [AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE IV.PAIV_Document_No=ATR.[Originating Document Number] AND IV.PACogs_Idx=ATR.[Account Index] AND ATR.[Description]='Cost of Goods Sold/Expense')
   WHERE IVH.[PAIV_Document_No] IS NOT NULL
GO