USE FIXEDASSET
--alter table tbfacadetail
--add ManualItemName varchar(25)
GO
--alter table tbfacadetail
--add TempLaborCost money
GO
ALTER procedure [dbo].[spFA_LoadCADetails]--'PAE-12-10-77595'
	@CANum varchar(30),
	@CASequence varchar(30) =''
as

--declare @CANum varchar(30),
--@CASequence varchar(30)
--set @CANum ='PAE-09-3-1'
--set @CASequence =''

select 
	a.CANum,
	a.CASequence,
	a.POSequence,
	a.RIVSequence,
	a.RIVNum,
	a.ItemID,
	a.UomID,
	a.EmpID,
	a.DeptID,
	b.UnitofMeasure,
	a.Quantity,
	a.UnitCost,
	a.BackOrder,
	a.IsBackOrder,
	a.IsFree,
	isnull(a.RRtype,'1') RRtype,
	isnull(a.caClass,0) caClass,
	a.IsManual,
	a.Specs,
	a.manualItemname,
	a.rrtype,
	A.propnum,
	A.TempLaborcost
from tbFACADetail a
left join tbfaunitofmeasure b on a.UomID = b.UnitofMeasureID
where CANum = @CAnum
--and not rtrim(a.CASequence) = rtrim(@CASequence)

GO


ALTER PROCEDURE [dbo].[spFA_LoadCAHeader]--'1'--,'04/01/2009','01/01/2012'
--FixedAsset..spFA_LoadCAHeader '3','04/06/2009','04/06/2012' 
@Mode varchar(1),
@CADate1 varchar(10) = NULL,
@CADate2 varchar(10) = NULL

AS
IF @Mode = '1'
		BEGIN
			SELECT	
				A.CANum,
				isnull(A.CADate, getdate()) CADate,
				--A.IVNum,
				isnull(A.IVDate, getdate()) IVDate,
				A.DRNum,
				isnull(A.DRDate, getdate()) DRDate,
				A.PONum,
				isnull(H.PODate, getdate()) PODate,
				A.TotalCost,
				isnull(A.Discount,0) Discount,
				A.NetCost,
				A.DeliveryStatusID,
				isnull(D.Delivery,'') Delivery,
				isnull(A.Remarks,'') Remarks,
				isnull(A.WarrantyBDate, getdate()) WarrantyBDate,
				isnull(A.WarrantyEDate, getdate()) WarrantyEDate,
				A.DateReceived,
				isnull(H.SupplierID,'') SupplierID,
				isnull(I.company,'') SupplierName,
				--A.IsWithBackOrder,
				A.CAStatus
				--,isForPARPrint = case when not AM.EmpID is null then 'Y' else 'N' end
				,CD.ManualItemName as requestedItem,
				CD.RRtype
			FROM tbFACAHeader A
				Left Outer Join tbFADeliveryStatus D on A.DeliveryStatusID = D.DeliveryID
				Left Outer Join tbFAPOHeader H on A.POnum = H.PONUm
				Left Outer Join accounting..vend I on A.SupplierID = I.vendorid
				--Left outer join tbFAAssetManager AM on AM.CANum = A.CANum
				left outer join tbFACADetail CD on A.CANum = CD.CANum
			WHERE A.CAStatus in ('0','1') 
			ORDER by A.CADate DESC, A.CANum DESC
		END;

	ELSE IF @Mode = '3'
		BEGIN
			SELECT	
				A.CANum,
				A.CADate,
				--A.IVNum,
				A.IVDate,
				isnull(A.DRNum,'') DRNum,
				isnull(A.DRDate,'') DRDate,
				isnull(A.PONum,'') PONum,
				H.PODate,
				A.TotalCost,
				A.Discount,
				A.NetCost,
				isnull(A.DeliveryStatusID,'') DeliveryStatusID,
				isnull(D.Delivery,'') Delivery,
				isnull(A.Remarks,'') Remarks,
				A.WarrantyBDate,
				A.WarrantyEDate,
				A.DateReceived,
				isnull(H.SupplierID,'') SupplierID,
				isnull(I.SupplierName,'') SupplierName,
				--A.IsWithBackOrder,
				A.CAStatus
				--,isForPARPrint = case when not AM.EmpID is null then 'Y' else 'N' end
				,CD.ManualItemName as requestedItem,
				CD.RRtype
			FROM tbFACAHeader A
				Left Outer Join tbFADeliveryStatus D on A.DeliveryStatusID = D.DeliveryID
				Left Outer Join tbFAPOHeader H on A.POnum = H.PONUm
				Left Outer Join tbfasupplier I on A.SupplierID = I.SupplierID
				--Left outer join tbFAAssetManager AM on AM.CANum = A.CANum
				left outer join tbFACADetail CD on A.CANum = CD.CANum
			WHERE A.CAStatus in ('3')
				and A.Transdate between @CADate1 and @CADate2 + ' 23:59:59.99'
			ORDER by A.CADate DESC, A.CANum DESC
		END;


GO

ALTER PROCEDURE [dbo].[spFA_ReportCA]--'08/01/2012','08/17/2012'
@date1 varchar(10),
@date2 varchar(10)

AS

	Declare @TotalDiscount float,
		@TotalNetCost float

	Set @TotalDiscount = (select sum(discount) from tbfacaheader where cadate between @date1 and @date2 + ' 23:59:59.99')
	Set @TotalNetCost = (select sum(netcost) from tbfacaheader where cadate between @date1 and @date2 + ' 23:59:59.99')

	SELECT  'RR: ' + ch.canum + space(5) + 'RR Date: ' + convert(varchar(10),ch.cadate,101) CAnum,
		
		ch.ponum PONum,
		pd.rivnum RIVNum,
--		case when cd.IsFree = 0 then
--			case when isnull(m.specification,'') <> '' then m.item +  '(' + isnull(m.specification,'') + ')' + ' ' + '"' + pd.specification + '"'
--				else m.item + ' ' + '"' + pd.specification + '"'
--			end
--		else
--			case when isnull(m.specification,'') <> '' then '"FREE" ' + m.item +  '(' + isnull(m.specification,'') + ')' 
--				else '"FREE" ' + m.item
--			end
--		end Item,
		--m.item,
		cd.manualitemname as item, /*RECEIVED ITEM*/
		ch.cadate,
		case when ch.castatus in ('0','1') then 'Pending'
		     when ch.castatus = '2' then 'Cancelled'
		     when ch.castatus = '3' then 'Posted'
		end status,
		cd.quantity,
  		IsNull(cd.UnitCost,0) unitcost,
		case when cd.IsFree = 0 
		      then Isnull (cd.UnitCost,0) * Isnull(cd.Quantity,0)	
		else 0
		end totalcost,
		ch.discount,
		ch.netcost,
		@totaldiscount totaldiscount,
		@totalnetcost totalnetcost,
		ph.pesovalue,
		isnull(s.company,'') supplier

	FROM tbfacaheader ch
		left outer join tbfacadetail cd on ch.canum = cd.canum
		left outer join tbfapodetail pd on cd.posequence = pd.posequence
		left outer join tbfapoheader ph on ch.ponum = ph.ponum
		left outer join accounting..vend s on ch.supplierid = s.vendorid
		left outer join tbfamaster m on cd.itemid = m.itemcode
	WHERE ch.cadate between @date1 and @date2 + ' 23:59:59.99'
	ORDER BY m.item





GO




ALTER PROCEDURE [dbo].[spFA_ReportCAForm]--'PAE-12-9-77509'
@CANum varchar(20)
AS

	SELECT 		
		CH.CANum,
		CH.CADate,
		S.Company,
		CH.PONum,--PH.PONum(isSepPO)
		PH.PODate,
		CH.DRNum,
		CH.DRDate,
		dbo.fnFA_IVSeries(cd.canum) IVNum,
		CH.IVDate,
		CD.ItemID,
		dbo.fnFA_RIVSeries(cd.canum,'2') RIVNum,
		dbo.fnFA_RIVDateSeries(cd.canum) RIVDate,
		CD.ManualItemName,
--		case when isnull(m.itemname,'') = '' then CD.ManualItemName 
--		else 
--			CASE WHEN CD.IsFree = 0 then
--					CASE WHEN isnull(M.itemdesc,'') <> '' then M.itemname +  '(' + isnull(M.itemdesc,'') + ')' + ' ' + '"' + pd.specification + '"'
--						Else M.itemname + ' ' + '"' + pd.specification + '"'
--					End
--				--ELSE
--				--	CASE WHEN isnull(M.itemdesc,'') <> '' then '"FREE" ' + M.itemname +  '(' + isnull(M.itemdesc,'') + ')' 
--				--		Else '"FREE" ' + M.itemname
--				--	End
--			END 
--		end as Item,
		CD.Quantity,
		U.UnitofMeasure,
		CD.UnitCost,
		isnull(CH.Discount,0) Discount,
		Case When CD.IsFree = 0 then CD.Quantity * CD.UnitCost
			Else 0
		End [Amount],
		D.Delivery,
		isnull(CH.Remarks,'') Remark,
		dbo.[fnFA_GetAssignatories]('3') as RECEIVINGOFFICER,
		dbo.[fnFA_GetAssignatories]('2') as ValidatingOfficer,
		dbo.fnFA_RequisitionerSeries(cd.canum) Requisitioner,
		--dbo.fnFA_GetRequisitionerFromInventory(CH.PONum) Requisitioner,
		CD.IsFree,
		dbo.[fnFA_GetAssignatories]('1') as PropertyCustodian,
		dbo.[fnFA_GetAssignatories]('4') as NotedBy,
		S.company as Supplier,
		isnull(CH.VatPerc,0) VatPerc,
		isnull(CH.VAT,0) VAT,
		CH.WarrantyBDate as WarrantyStartDate,
		CH.WarrantyEDate as WarrantyEndDate
	FROM tbfacadetail CD
		left outer join tbfacaheader CH on CD.CAnum = CH.CANum
		left outer join tbfapoheader PH on CH.PONum = PH.PONum
		left outer join tbfapodetail PD on CD.POSequence = PD.POSequence
		left outer join tbfadeliverystatus D on CH.DeliveryStatusID = D.DeliveryID
		left outer join accounting..vend S on CH.SupplierID = S.vendorid
		left outer join inventory..tbinvmaster M on M.ItemID = CD.itemid
		left outer join tbfaunitofmeasure U on CD.UomID = U.UnitofMeasureID
		left outer join Payroll..tbcmaster pm on CH.ReceivingUserID = PM.Empnum
		left outer join Payroll..tbcmaster PM1 on CH.ValidatingUserID = PM1.Empnum


	WHERE CD.CANum = @CANum

	ORDER BY CD.IsFree--, M.Item
	/*Declare @RIVNum varchar(20),
		@RIVDate varchar(10),
		@Requisitioner varchar(30),
		@IVNum varchar(10),
		@RIVNum_All varchar(50),
		@IVNum_All varchar(50),
		@RIVDate_All varchar(50),
		@Requisitioner_All varchar(254)*/

	/*--~~@Requisitioner_All~~--
	DECLARE authors_cursor CURSOR FOR
	Select Case IsNull(max(pm.MiddleName),'')
	              When '' Then IsNull(max(PM.FirstName),'') + ' ' + IsNull(max(PM.LastName),'')
	              Else IsNull(max(PM.FirstName),'') + ' ' + Left(IsNull(max(PM.MiddleName),''),1) + '. '   + IsNull(max(PM.LastName),'')
	        End Requisitioner
	From tbfarivdetail rd
		left outer join tbfacadetail cd on rd.rivsequence = cd.rivsequence
		left outer join tbfarivheader rh on rd.rivnum = rh.rivnum
		left outer join Payroll..tbcmaster pm on rh.requisitionerid = pm.empnum
	Where cd.canum = @canum and cd.isfree = 0
	Group by rd.rivnum
	Order by max(pm.LastName)
	OPEN authors_cursor
	Set @Requisitioner_All = ''
	FETCH NEXT FROM authors_cursor Into @Requisitioner
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Set @Requisitioner_All = @Requisitioner_All + @Requisitioner + char(13) + char(13) + char(13)
		FETCH NEXT FROM authors_cursor Into @Requisitioner
	
	END
	CLOSE authors_cursor
	DEALLOCATE authors_cursor
	--~~@Requisitioner_All~~--*/


	--~~@RIVNum_All~~--
	/*DECLARE authors_cursor CURSOR FOR
	Select  isnull(PD.RIVNum,'') 
	From tbFAPODetail PD
		Left Outer Join tbFACADetail CD on PD.POSequence = CD.POSequence
	Where CD.CANum = @CANum
		and PD.IsFree = 0
	Group By PD.RIVNum
	OPEN authors_cursor
	Set @RIVNum_All = ''
	FETCH NEXT FROM authors_cursor Into @RIVNum
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Set @RIVNum_All = @RIVNum_All + @RIVNum + ' - '
		FETCH NEXT FROM authors_cursor Into @RIVNum
	
	END
	CLOSE authors_cursor
	DEALLOCATE authors_cursor*/
	--~~@RIVNum_All~~--
	
	/*--~~@RIVDate_All~~--
	DECLARE authors_cursor CURSOR FOR
	Select max(convert(varchar(10),rh.rivdate,101))
			From tbfarivdetail rd
				left outer join tbfacadetail cd on rd.rivsequence = cd.rivsequence
				left outer join tbfarivheader rh on rd.rivnum = rh.rivnum
			Where cd.canum =  @CANum and cd.isfree = 0
	Group by rd.rivnum
	OPEN authors_cursor
	Set @RIVDate_All = ''
	FETCH NEXT FROM authors_cursor Into @RIVDate
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Set @RIVDate_All = @RIVDate_All + @RIVDate + ' - '
		FETCH NEXT FROM authors_cursor Into @RIVDate
	
	END
	CLOSE authors_cursor
	DEALLOCATE authors_cursor
	--~~@RIVDate_All~~--*/
	
	--~~@IVNum_All~~--
	/*DECLARE authors_cursor CURSOR FOR
	Select  isnull(IVNum,'') 
	From tbFAIVNum
	Where CANum = @CANum
	OPEN authors_cursor
	Set @IVNum_All = ''
	FETCH NEXT FROM authors_cursor Into @IVNum
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Set @IVNum_All = @IVNum_All + @IVNum + ' - '
		FETCH NEXT FROM authors_cursor Into @IVNum
	
	END
	CLOSE authors_cursor
	DEALLOCATE authors_cursor*/
	--~~@IVNum_All~~--
		





GO

ALTER PROCEDURE [dbo].[spFA_SaveCADetail]

@CANum varchar(20),
@CASequence varchar(10),
@POSequence varchar(10),
@RIVSequence varchar(10),
@ItemID varchar(20),
@UomID varchar(10),
@Quantity float,
@UnitCost float,
--@IsPosted varchar(1),
@IsFree varchar(1),
@BackOrder float,
@IsBackOrder varchar(1),
@RRType varchar(1) = '',
@IsManual varchar(1)= '',
@Specs varchar(150)='',
@PropNum varchar(30) = '',
@PoNum varchar(30) = '',
@Requisitioner varchar(15) = '',
@Department varchar(15)='',
@CAClass varchar(5)='',
@ManualItemName varchar(25)='',
@TempLaborCost money = 0

AS
DECLARE @ErrorCode int
	SET @ErrorCode = 0


	INSERT INTO tbFACADetail	
			(CANum,
			CASequence,
			POSequence,
			RIVSequence,
			ItemID,
			UomID,
			Quantity,
			UnitCost,
			IsFree,
			BackOrder,
			IsBackOrder,
			RRType,
			IsManual,
			Specs,
			PropNum,
			PONum,
			EmpID,
			DeptID,
			CAClass,
			ManualItemName,
			TempLaborCost)
	VALUES			
			(@CANum,
			@CASequence,
			@POSequence,
			@RIVSequence,
			@ItemID,
			@UomID,
			@Quantity,
			@UnitCost,
			--@IsPosted,
			@IsFree,
			@BackOrder,
			@IsBackOrder,
			@RRType,
			@IsManual,
			@Specs,
			@PropNum,
			@PONum,
			@Requisitioner,
			@Department,
			@CAClass,
			@ManualItemName,
			@TempLaborCost);
	SET @ErrorCode = @@ERROR
	
	IF @ErrorCode <> 0
	  RETURN @ErrorCode

	RETURN @ErrorCode