cancel
Showing results for 
Search instead for 
Did you mean: 

Need changes in JE query

Former Member
0 Kudos

Hi Experts

i had created a query for JE

i want GL/ CODE & BP partner name in same Field where i had created it as 2

pls go through my query and help me to make changes

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select JDT1.Line_ID,OJDT.TransId as 'Journal Entry No',OJDT.DueDate as 'Posting Date',JDT1.Ref1 as 'Ref1',JDT1.Ref2 as 'Ref2',JDT1.Ref3Line as 'Ref3',

t.FormatCode as 'G/L Acct/BP Code',t.AcctName as 'G/L Acct/BP Name', t.AcctCode as 'BP/Account Code',JDT1.Debit as 'Debit Amount', JDT1.Credit as 'Credit Amount',

JDT1.LineMemo as 'Remarks',JDT1.Project as 'Project Code',OPRJ.PrjName as 'Project Name',JDT1.ProfitCode as 'Cost Center',OPRC.PrcName as 'Cost Center Name'

from OJDT

  

   inner join JDT1 on OJDT.TransId = JDT1.TransId

   Left Outer join

   (

   Select AcctCode ,AcctName  ,FormatCode from OACT 

   Union all

   Select CardCode ,CardName ,CardFName from OCRD

   ) t on JDT1 .ShortName = t.AcctCode

  

   left outer join OPRJ on JDT1.Project = OPRJ.PrjCode

   left outer join OPRC on JDT1.ProfitCode = OPRC.PrcCode

   

   where t.AcctName  <> 'Net Tax Paybale 0% (Riyadh, MPS, FFU)' and OJDT.BaseRef = @TransId

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Regards

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

select JDT1.Line_ID,OJDT.TransId as 'Journal Entry No',OJDT.DueDate as 'Posting Date',JDT1.Ref1 as 'Ref1',JDT1.Ref2 as 'Ref2',JDT1.Ref3Line as 'Ref3',

t.FormatCode , t.AcctName + t.AcctCode ,JDT1.Debit as 'Debit Amount', JDT1.Credit as 'Credit Amount',

JDT1.LineMemo as 'Remarks',JDT1.Project as 'Project Code',OPRJ.PrjName as 'Project Name',JDT1.ProfitCode as 'Cost Center',OPRC.PrcName as 'Cost Center Name'

from OJDT

 

   inner join JDT1 on OJDT.TransId = JDT1.TransId

   Left Outer join

   (

   Select AcctCode ,AcctName  ,FormatCode from OACT

   Union all

   Select CardCode ,CardName ,CardFName from OCRD

   ) t on JDT1 .ShortName = t.AcctCode

 

   left outer join OPRJ on JDT1.Project = OPRJ.PrjCode

   left outer join OPRC on JDT1.ProfitCode = OPRC.PrcCode

  

   where t.AcctName  <> 'Net Tax Paybale 0% (Riyadh, MPS, FFU)'

Thanks.

Former Member
0 Kudos

Hi Rajan

Thanks for the support

is it possible to appear BP Card code in the Format code Field, now when a BP entry is in JE the format code field is appearing as Null.

Regards

daroy_alvin
Active Participant
0 Kudos

Hi Shahan,

Try

isnull(t.FormatCode,t.AcctCode) as FormatCode

Thanks

Alvin

Former Member
0 Kudos

Hi Alvin

Thanks for the Support, its fine working

Regrds

Former Member
0 Kudos

Hi alvin

i need one more help in this regarding with JE

early we were using je print out via PLD

in which if we select a je and go for printout, it will automatically appers as per the layout set as default

the same way i need it in this query also, when i make a CR and upload it in SAP and make it default

it should automatically give the print preview, now its asking for the Trans no

This is my SP

-------------------------------------------------------------------------------------------------------------------------------------

USE [SSC_Production]

GO

/****** Object:  StoredProcedure [dbo].[TmSp_JournalEntryCRPrintlayout]    Script Date: 04/26/2015 08:44:08 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[TmSp_JournalEntryCRPrintlayout]

@TransId  int

AS 

Begin 

Select JDT1.Line_ID,OJDT.TransId as 'Journal Entry No',OJDT.DueDate as 'Posting Date',JDT1.Ref1 as 'Ref1',JDT1.Ref2 as 'Ref2',JDT1.Ref3Line as 'Ref3',

isnull(t.FormatCode,t.AcctCode) as 'G/L / BP Code', t.AcctName /*+ t.AcctCode*/ AS 'G/L / BP Name',JDT1.Debit as 'Debit Amount', JDT1.Credit as 'Credit Amount',

OJDT.Memo as 'Remarks',JDT1.Project as 'Project Code',OPRJ.PrjName as 'Project Name',JDT1.ProfitCode as 'Cost Center',OPRC.PrcName as 'Cost Center Name'

from OJDT

   inner join JDT1 on OJDT.TransId = JDT1.TransId

      Left Outer join

   (

   Select AcctCode ,AcctName  ,FormatCode from OACT

   Union all

   Select CardCode ,CardName ,CardFName from OCRD

   ) t on JDT1 .ShortName = t.AcctCode

   left outer join OPRJ on JDT1.Project = OPRJ.PrjCode

   left outer join OPRC on JDT1.ProfitCode = OPRC.PrcCode

 

   where t.AcctName  <> 'Net Tax Paybale 0% (Riyadh, MPS, FFU)' and OJDT.TransId = @TransId

  

  

   END

--------------------------------------------------------------------------------------------------------------------------------------

Regards

daroy_alvin
Active Participant
0 Kudos

Hi,

Use Token on your Parameter.

1. Create or Rename your CR Parameter to DocKey@.

2. In CR Select Expert. Add {TmSp_JournalEntryCRPrintlayout;1.Journal Entry No} = {?DocKey@}

3. Re-import your CR as Layout default in JE.

Please see also http://www.pioneerb1.com/wp-content/uploads/2012/04/How-to-work-with-Crystal-Reports-8.8.pdf for more details.

Thanks and Regards,

Alvin

Former Member
0 Kudos

Hi

Thanks for the support, it works fine

Regards

Answers (1)

Answers (1)

daroy_alvin
Active Participant
0 Kudos

Hi.

Use concatenate string to combine two or more strings.

Try:

t.AcctName + ' ' + t.AcctCode as 'G/L Acct/BP Name'

Thanks

Alvin

Former Member
0 Kudos

Thanks Alvin