PTRFILOAUTOPOSTINGREGULAR_20241113.sql - MINOVA DEMO - Redmine

Feature #2886 » PTRFILOAUTOPOSTINGREGULAR_20241113.sql

Tri Rizqiaty, 11/13/2024 11:23 AM

    
      ??ALTER PROCEDURE [dbo].[PTRFILOAUTOPOSTINGREGULAR]

      AS

      DECLARE @Now14 VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(),'yyyymmddhhnnss')

      DECLARE @Now8 VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(),'yyyymmdd') --'20241007'--dbo.fn_formatdatetime(GETDATE(),'yyyymmdd')

      DECLARE @DatePost VARCHAR(20) = RIGHT(@Now8,2)

      DECLARE @thead 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,

      	[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) SPARSE  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,

      	[InstallmentType] [VARCHAR](4) NULL,

      	[Tenor] [VARCHAR](20) NULL

      )

      INSERT INTO @thead

      SELECT *

      FROM dbo.PTRFINANCEHEAD AS head

      --LEFT JOIN dbo.PTRFINANCEITEM AS item

      --	ON head.DocNo = item.DocNo

      WHERE DocType = 'RG'

      	AND RegularDate = @DatePost

      	AND DocStatus = '2'

      	AND head.StartDate <= @Now8

      	AND head.EndDate >= @Now8

      DECLARE @titem TABLE

      (

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

      	[DocItemID] [INT] NOT NULL,

      	[FiscalYear] [VARCHAR](20) NULL,

      	[FiscalPeriod] [VARCHAR](20) NULL,

      	[CompanyID] [VARCHAR](50) NULL,

      	[DocItemRef] [VARCHAR](20) NULL,

      	[DKFlag] [VARCHAR](20) NULL,

      	[AccountGroup] [VARCHAR](50) NULL,

      	[GLAccountID] [VARCHAR](20) NULL,

      	[Currency] [VARCHAR](20) NULL,

      	[SubLedgerType] [VARCHAR](30) NULL,

      	[SubLedgerID] [VARCHAR](30) NULL,

      	[CostCenter] [VARCHAR](50) NULL,

      	[ProductID] [VARCHAR](30) NULL,

      	[CreateBy] [VARCHAR](50) NULL,

      	[CreateDate] [VARCHAR](14) NULL,

      	[ChangeBy] [VARCHAR](50) NULL,

      	[ChangeDate] [VARCHAR](14) NULL,

      	[AmountDebet] [DECIMAL](30, 0) NULL,

      	[AmountCredit] [DECIMAL](30, 0) NULL,

      	[AmountDebetCompCurr] [DECIMAL](30, 0) NULL,

      	[AmountCreditCompCurr] [DECIMAL](30, 0) NULL,

      	[CompanyCurrency] [VARCHAR](50) NULL,

      	[ExchangeRate] [DECIMAL](30, 0) NULL,

      	[ItemStatus] [VARCHAR](50) NULL,

      	[DueDate] [VARCHAR](50) NULL,

      	[RefDocTr] [VARCHAR](50) NULL,

      	[RefDocTy] [VARCHAR](50) NULL,

      	[RefDocID] [VARCHAR](50) NULL,

      	[Description] [VARCHAR](500) NULL,

      	[BusinessUnit] [VARCHAR](30) NULL

      )

      INSERT INTO @titem

      SELECT item.*

      FROM dbo.PTRFINANCEITEM AS item

      INNER JOIN @thead AS head

      	ON head.DocNo = item.DocNo

      DECLARE @CurrentID VARCHAR(20)

      SELECT DISTINCT @CurrentID = CurrentID

      FROM dbo.PCMBSENTERPRISENUMBERDOC 

      WHERE TableName = 'PTRFINANCEHEAD'

      --SELECT @CurrentID

      DECLARE @LastDocNo VARCHAR(20) = @CurrentID

      DECLARE @c_docno VARCHAR(8)

      DECLARE cur_doc CURSOR FOR SELECT DocNo FROM @thead

      OPEN cur_doc

      FETCH cur_doc INTO @c_docno

      WHILE @@Fetch_Status = 0

      	BEGIN

      	SET @LastDocNo = RIGHT('00000000'+ CONVERT(VARCHAR(8),(@LastDocNo + 1)),8)

      	--SELECT @LastDocNo

      	INSERT INTO PTRFINANCEHEAD

      	SELECT DISTINCT @LastDocNo AS DocNo

      		,CASE WHEN DocTypeTo <> '' THEN DocTypeTo ELSE 'GL' END AS [DocType]

      		,'' AS [DocTypeTo]

      		,@Now8 AS [DocDate]

      		,'9' AS [DocStatus]

      		,[Description]

      		,LEFT(@Now8,4) AS [FiscalYear]

      		,SUBSTRING(@Now8, 5,2) AS [FiscalPeriod]

      		,LEFT(@Now8,6) AS [FiscalPeriodYear]

      		,[CompanyID]

      		,DocNo AS [IntDocRef]

      		,[ExtDocRef]

      		,[BusinessUnit]

      		,@Now8 AS [PostingDate]

      		,[Curency]

      		,'ServicePostRegular' AS [CreateBy]

      		,@Now14 AS [CreateDate]

      		,'ServicePostRegular' AS[ChangeBy]

      		,@Now14 AS[ChangeDate]

      		,[CustomerID]

      		,[VendorID]

      		,[TotalDebet]

      		,[TotalCredit]

      		,[InvoiceNumber]

      		,[FakturNumber]

      		,[VendorInvoiceNumber]

      		,[IntDocTrType]

      		,[Regular]

      		,[CashPlan]

      		,'' AS [StartDate]

      		,'' AS [EndDate]

      		,'' AS [RegularDate]

      		,[RefType]

      		,[RefStatus]

      		,'FIN' AS [TransTypeRef]

      		,'RG' AS [DocTypeRef]

      		,DocNo AS [DocIDRef]

      		,[DocItemRef]

      		,[ProcessID]

      		,[ExternalID]

      		,[CostCenter]

      		,[TotSalesRef]

      		,[TotInvoiceRef]

      		,[BalanceRef]

      		,[DueDate]

      		,[Abbreviation]

      		,[TotAmoPurc]

      		,[CustPONumber]

      		,[FakturFlag]

      		,[AutoFlag]

      		,[StartDatePlan]

      		,[FinishDatePlan]

      		,[StartDateActual]

      		,[FinishDateActual]

      		,[FakturDate]

      		,[InstallmentType]

      		,[Tenor] 

      	FROM @thead

      	WHERE DocNo = @c_docno

      	INSERT INTO PTRFINANCEITEM

      	SELECT DISTINCT @LastDocNo AS [DocNo]

      		,[DocItemID]

      		,LEFT(@Now8,4) AS [FiscalYear]

      		,SUBSTRING(@Now8, 5,2) AS [FiscalPeriod]

      		,[CompanyID]

      		,[DocItemRef]

      		,[DKFlag]

      		,[AccountGroup]

      		,[GLAccountID]

      		,[Currency]

      		,[SubLedgerType]

      		,[SubLedgerID]

      		,[CostCenter]

      		,[ProductID]

      		,'ServicePostRegular' AS[CreateBy]

      		,@Now14 AS [CreateDate]

      		,'ServicePostRegular' AS[ChangeBy]

      		,@Now14 AS [ChangeDate]

      		,[AmountDebet]

      		,[AmountCredit]

      		,[AmountDebetCompCurr]

      		,[AmountCreditCompCurr]

      		,[CompanyCurrency]

      		,[ExchangeRate]

      		,[ItemStatus]

      		,[DueDate]

      		,[RefDocTr]

      		,[RefDocTy]

      		,[RefDocID]

      		,[Description]

      		,[BusinessUnit]

      	FROM @titem

      	WHERE DocNo = @c_docno

      	INSERT INTO PTRFILOPOSTING

      	SELECT DISTINCT @LastDocNo AS [DocNo]

      		,item.[DocItemID]

      		,head.[DocType]

      		,@Now8 AS [DocDate]

      		,'9' AS [DocStatus]

      		,LEFT(@Now8,4) AS [FiscalYear]

      		,SUBSTRING(@Now8, 5,2) AS [FiscalPeriod]

      		,head.[CompanyID]

      		,head.[BusinessUnit]

      		,head.[CostCenter]

      		,item.SubLedgerType AS [SLType]

      		,item.SubLedgerID AS [SLID]

      		,head.[PostingDate]

      		,item.[GLAccountID]

      		,item.[Currency]

      		,[AmountDebet]

      		,[AmountCredit]

      		,[AmountDebetCompCurr]

      		,[AmountCreditCompCurr]

      		,'ServicePostRegular' AS [CreateBy]

      		,@Now14 AS [CreateDate]

      		,'ServicePostRegular' AS[ChangeBy]

      		,@Now14 AS [ChangeDate]

      		,item.[CompanyCurrency]

      		,item.[ItemStatus]

      	FROM @thead AS head

      	LEFT JOIN @titem AS item

      		ON head.DocNo = item.DocNo

      	WHERE head.DocNo = @c_docno

      FETCH cur_doc INTO @c_docno

      END

      CLOSE cur_doc

      DEALLOCATE cur_doc

      UPDATE PCMBSENTERPRISENUMBERDOC SET CurrentID = @LastDocNo

      --SELECT DISTINCT * FROM dbo.PCMBSENTERPRISENUMBERDOC 

      WHERE TableName = 'PTRFINANCEHEAD'

« Previous
1
2
3
4
Next »

(4-4/4)

Project

General

Profile

MINOVA DEMO

Feature #2886 » PTRFILOAUTOPOSTINGREGULAR_20241113.sql