MinovaEasy

??ALTER PROCEDURE [dbo].[PTRFILOAUTOPOSTING]

AS

DECLARE @Now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(),'yyyymmdd')

--DECLARE @Now VARCHAR(20) = '20240119'

--DECLARE @FINHEAD TABLE (

--	[DocNo] [VARCHAR](20) NOT NULL,

--	[DocType] [VARCHAR](20) NULL,

--	[DocTypeTo] [VARCHAR](20) NULL,

--	[DocDate] [VARCHAR](20) NULL,

--	[DocStatus] [VARCHAR](20) NULL,

--	[Description] [VARCHAR](500) NULL,

--	[FiscalYear] [VARCHAR](20) NULL,

--	[FiscalPeriod] [VARCHAR](20) NULL,

--	[FiscalPeriodYear] [VARCHAR](20) NULL,

--	[CompanyID] [VARCHAR](20) NULL,

--	[IntDocRef] [VARCHAR](100) NULL,

--	[ExtDocRef] [VARCHAR](50) NULL,

--	[BusinessUnit] [VARCHAR](50) NULL,

--	[PostingDate] [VARCHAR](20) NULL,

--	[Curency] [VARCHAR](20) NULL,

--	[CreateBy] [VARCHAR](50) NULL,

--	[CreateDate] [VARCHAR](14) NULL,

--	[ChangeBy] [VARCHAR](50) NULL,

--	[ChangeDate] [VARCHAR](14) NULL,

--	[CustomerID] [VARCHAR](50) NULL,

--	[VendorID] [VARCHAR](50) NULL,

--	[TotalDebet] [DECIMAL](30, 0) NULL,

--	[TotalCredit] [DECIMAL](30, 0) NULL,

--	[InvoiceNumber] [VARCHAR](50) NULL,

--	[FakturNumber] [VARCHAR](50) NULL,

--	[VendorInvoiceNumber] [VARCHAR](50) NULL,

--	[KwitansiNumber] [VARCHAR](24) NULL,

--	[IntDocTrType] [VARCHAR](50) NULL,

--	[Regular] [VARCHAR](50) NULL,

--	[CashPlan] [VARCHAR](50) NULL,

--	[StartDate] [VARCHAR](8) NULL,

--	[EndDate] [VARCHAR](8) NULL,

--	[RegularDate] [VARCHAR](10) NULL,

--	[RefType] [VARCHAR](2) NULL,

--	[RefStatus] [VARCHAR](2) NULL,

--	[TransTypeRef] [VARCHAR](20) NULL,

--	[DocTypeRef] [VARCHAR](20) NULL,

--	[DocIDRef] [VARCHAR](20) NULL,

--	[DocItemRef] [VARCHAR](20) NULL,

--	[ProcessID] [VARCHAR](8) NULL,

--	[ExternalID] [VARCHAR](50) NULL,

--	[CostCenter] [VARCHAR](50) NULL,

--	[TotSalesRef] [DECIMAL](30, 0) NULL,

--	[TotInvoiceRef] [DECIMAL](30, 0) NULL,

--	[BalanceRef] [DECIMAL](30, 0) NULL,

--	[DueDate] [VARCHAR](8) NULL,

--	[Abbreviation] [VARCHAR](500) NULL,

--	[TotAmoPurc] [DECIMAL](18, 2) NULL,

--	[CustPONumber] [VARCHAR](50) NULL,

--	[FakturFlag] [VARCHAR](4) NULL,

--	[AutoFlag] [VARCHAR](20) NULL,

--	[StartDatePlan] [VARCHAR](20) NULL,

--	[FinishDatePlan] [VARCHAR](14) NULL,

--	[StartDateActual] [VARCHAR](30) NULL,

--	[FinishDateActual] [VARCHAR](14) NULL,

--	[FakturDate] [VARCHAR](20) NULL

--)

--INSERT INTO @FINHEAD

--SELECT DISTINCT *

--FROM PTRFINANCEHEAD AS head

--WHERE head.DocStatus = '2'

--	AND head.PostingDate = @now

INSERT INTO PTRFILOPOSTING

SELECT DISTINCT head.DocNo,

				item.DocItemID,

				head.DocType,

				head.DocDate,

				'9' AS DocStatus,

				head.FiscalYear,

				head.FiscalPeriod,

				head.CompanyID,

				head.BusinessUnit,

				head.CostCenter,

				'' AS SLType,

				'' AS SLID,

				head.PostingDate,

				item.GLAccountID,

				item.Currency,

				item.AmountDebet,

				item.AmountCredit,

				item.AmountDebetCompCurr,

				item.AmountCreditCompCurr,

				'ServicePosting' AS CreateBy,

				dbo.fn_formatdatetime(GETDATE(),'yyyymmddhhnnss') AS CreateDate,

				'ServicePosting' AS ChangeBy,

				dbo.fn_formatdatetime(GETDATE(),'yyyymmddhhnnss') AS ChangeDate,

				item.CompanyCurrency,

				item.ItemStatus

				--item.CashActivityType,

				--item.CashActivityDetail		

FROM dbo.PTRFINANCEHEAD AS head

LEFT JOIN dbo.PTRFINANCEITEM AS item

	ON head.DocNo = item.DocNo

WHERE head.PostingDate = @Now

	AND head.DocStatus = '2' --//-- 2=park

UPDATE PTRFINANCEHEAD

SET DocStatus = '9'

--SELECT * FROM PTRFINANCEHEAD

WHERE DocStatus = '2'

	AND PostingDate = @now