on 04-23-2015 1:29 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
Hi.
Use concatenate string to combine two or more strings.
Try:
t.AcctName + ' ' + t.AcctCode as 'G/L Acct/BP Name'
Thanks
Alvin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.