PRPTFIJURNALRECAP_20251029.sql - Golf - Redmine

Bug #3796 » PRPTFIJURNALRECAP_20251029.sql

Tri Rizqiaty, 10/29/2025 02:17 PM

    
      ??ALTER PROCEDURE [dbo].[PRPTFIJURNALRECAP]

      (

      	@StartDate VARCHAR(20)

      	,@EndDate VARCHAR(20)

      	,@UserID VARCHAR(150)

      	,@DocStatus VARCHAR(20)

      	,@CompanyID VARCHAR(20)

      	,@BusinessUnit VARCHAR(20)

      	,@DocType VARCHAR(20)

      	,@CreateBy VARCHAR(20)

      	,@VendorID VARCHAR(20)

      	,@CustomerID VARCHAR(20)

      	,@CostCenter VARCHAR(20)

      )

      AS

      --DECLARE @StartDate VARCHAR(20) = '20240101'

      --DECLARE @EndDate VARCHAR(20) = '20240430'

      --DECLARE @UserID VARCHAR(150) = ''

      --DECLARE @DocStatus VARCHAR(20) = ''

      --DECLARE @CompanyID VARCHAR(20) = '1000'

      --DECLARE @BusinessUnit VARCHAR(20) = ''

      --DECLARE @DocType VARCHAR(20) = ''

      --DECLARE @CreateBy VARCHAR (20) = ''

      --DECLARE @VendorID VARCHAR (20) = '00000002'

      --DECLARE @CustomerID VARCHAR (20) = '' ----------CUS0003

      --DECLARE @CostCenter VARCHAR (20) = ''

      DECLARE @TblHead TABLE

          (

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

            [DocType] [VARCHAR](20) NULL ,

            DocTypeTo VARCHAR(50) ,

            [DocDate] [VARCHAR](20) NULL ,

            [DocStatus] [VARCHAR](20) NULL ,

            DocDesc [VARCHAR](500) NULL ,

            [FiscalYear] [VARCHAR](20) NULL ,

            [FiscalPeriod] [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 ,

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

            [TransTypeRef] [VARCHAR](20) NULL ,

            [DocTypeRef] [VARCHAR](20) NULL ,

            [DocIDRef] [VARCHAR](20) NULL ,

            [DocItemRef] [VARCHAR](20) NULL ,

            [ProcessID] [VARCHAR](20) NULL ,

            [ExternalID] [VARCHAR](20) NULL ,

            [CostCenterz] [VARCHAR](20) NULL ,

            REfType VARCHAR(200) ,

            RefStatus VARCHAR(200) ,

            DocStatusDesc VARCHAR(20) ,

            CompanyDescription VARCHAR(250) ,

            BusUnitDesc VARCHAR(250) ,

            CurrencyDescription VARCHAR(250) ,

            CustomerDesc VARCHAR(250) ,

            VendorDesc VARCHAR(250)

          )

      INSERT  INTO @TblHead

              SELECT DISTINCT

                       head.DocNo ,

                      head.DocType ,

                      head.DocTypeTo ,

                      head.DocDate ,

                      head.DocStatus ,

                      head.Description ,

                      head.FiscalYear ,

                      head.FiscalPeriod ,

                      head.CompanyID ,

                      head.IntDocRef ,

                      head.ExtDocRef ,

                      head.BusinessUnit ,

                      head.PostingDate ,

                      head.Curency ,

                      head.CreateBy ,

                      head.CreateDate ,

                      head.ChangeBy ,

                      head.ChangeDate ,

                      head.CustomerID ,

                      head.VendorID ,

                      head.InvoiceNumber ,

                      head.FakturNumber ,

                      head.VendorInvoiceNumber ,

                      head.IntDocTrType ,

                      head.Regular ,

                      head.CashPlan ,

                      head.StartDate ,

                      head.EndDate ,

                      head.RegularDate ,

                      head.RefType ,

                      head.RefStatus ,

                      head.TransTypeRef ,

                      head.DocTypeRef ,

                      head.DocIDRef ,

                      head.DocItemRef ,

                      head.ProcessID ,

                      head.ExternalID ,

                      head.CostCenter ,

                      doc.Description AS DocStatusDesc ,

                      comp.CompanyDescription ,

                      bus.Description AS BusUnitDesc ,

                      curr.CurrencyDescription ,

                      '' AS CustomerDesc, --customer.Description AS CustomerDesc ,

                      vendor.Description AS VendorDesc

              FROM    dbo.PTRFINANCEHEAD AS head

                      LEFT JOIN dbo.PCMBSDOCSTATUS AS doc ON doc.DocStatus = head.DocStatus

                      LEFT JOIN dbo.PCMEPCOMPID AS comp ON head.CompanyID = comp.CompanyID

                      LEFT JOIN dbo.PCMFILOBUSUNIT AS bus ON bus.BuCode = head.BusinessUnit

                      LEFT JOIN dbo.PHRPYCURR AS curr ON curr.Currency = head.Curency

                      LEFT JOIN dbo.PMDCUS0001 AS customer ON customer.CustomerID = head.CustomerID

                      LEFT JOIN dbo.PMDVEN0001 AS vendor ON vendor.VendorID = head.VendorID

              WHERE   head.DocDate >= @StartDate

                      AND head.DocDate <= @EndDate

                      AND ( head.DocStatus = @DocStatus

                            OR @DocStatus = ''

                          )

                      AND ( Head.CreateBy = @UserID

                            OR @UserID = ''

                          )

                      AND ( head.DocType = @DocType

                            OR @DocType = ''

                          )

                      AND ( head.CompanyID = @CompanyID

                            OR @CompanyID = ''

                          )

                      AND ( head.BusinessUnit = @BusinessUnit

                            OR @BusinessUnit = ''

                          )

      					 AND ( head.CreateBy = @CreateBy

                            OR @CreateBy = ''

                          )

      SELECT DISTINCT

              TblHead.* ,

              FiItem.DKFlag ,

              FiItem.AccountGroup ,

              FiItem.GLAccountID ,

              FiItem.SubLedgerType ,

              --FiItem.SubLedgerID ,

      		sldesc.Description as SubLedgerID,

              --FiItem.CostCenter as cc ,

              FiItem.ProductID ,

              FiItem.AmountDebet ,

              FiItem.AmountCredit ,

              FiItem.AmountDebetCompCurr ,

              FiItem.AmountCreditCompCurr ,

              FiItem.CompanyCurrency ,

              FiItem.ItemStatus ,

              FiItem.DueDate ,

              FiItem.RefDocTr ,

              FiItem.RefDocTy ,

              FiItem.RefDocID ,

              FiItem.Description AS FiItemDesc ,

              sl.Description AS SLDesc ,

              product.Description AS ProductDesc ,

              compcurr.CurrencyDescription AS CompCurrDesc ,

              CASE WHEN FiItem.ItemStatus = '0' THEN 'Open'

                   WHEN FiItem.ItemStatus = '1' THEN 'Partial'

                   WHEN FiItem.ItemStatus = '2' THEN 'Closed'

              END AS ItemStatusDesc  --0=Open||1=Partial||2=Closed

              ,

              cc.ObjectDescription AS CostcenterDesc ,

              gl.Description AS GLAccountDesc ,

              dbo.fn_formatdatetime_indonesia(TblHead.PostingDate, 'dd/mm/yyyy') AS PostingDateDesc ,

              dbo.fn_formatdatetime_indonesia(TblHead.DocDate, 'dd/mm/yyyy') AS DocDateDesc,

      		hh.CostCenter as ccc

      FROM    @TblHead AS TblHead

              LEFT JOIN dbo.PTRFINANCEITEM AS FiItem ON TblHead.DocNo = FiItem.DocNo

              LEFT JOIN dbo.PCMFILOSLTYPE AS sl ON sl.SubLedgerType = FiItem.SubLedgerType

              LEFT JOIN dbo.PMDPRD0001 AS product ON product.ProductID = FiItem.ProductID

              LEFT JOIN dbo.PHRPYCURR AS compcurr ON compcurr.Currency = FiItem.CompanyCurrency

              LEFT JOIN dbo.PHROM0001 AS cc ON cc.ObjectClass = 'CC'

                                               AND cc.StartDate <= dbo.fn_formatdatetime(GETDATE(),

                                                                    'yyyymmdd')

                                               AND cc.EndDate >= dbo.fn_formatdatetime(GETDATE(),

                                                                    'yyyymmdd')

                                               AND cc.ObjectID = FiItem.CostCenter

              LEFT JOIN dbo.PCMFILOCOAGL AS gl ON gl.GlAccount = FiItem.GLAccountID

      		LEFT JOIN dbo.PTRFINANCEHEAD AS hh on FiItem.docno = hh.docno

      		LEFT JOIN PMDSL0001 sldesc on FiItem.SubLedgerID=sldesc.SubLedgerID

      		and hh.DocDate >= @StartDate

                      AND hh.DocDate <= @EndDate

      	 WHERE  ( hh.CostCenter = @CostCenter

                            OR @CostCenter = ''

                          )

      					AND

      					(FiItem.SubLedgerID=@VendorID or @VendorID='')

(11-11/12)

Project

General

Profile

Golf

Bug #3796 » PRPTFIJURNALRECAP_20251029.sql