Skip to Content
author's profile photo Former Member
Former Member

Detail data

Hi All,

I know a certain transaction in the G/L report is from a journal entry and the journal entry could be from journal entry, inventory transaction and marketing documents.

to make my question more clear, here is an example:

1. JE# 12 --> transtype : '24'

1000 cash account Dr. 1000

1410 A/R account Cr. 1000

2. Incoming payment

Document : inv.# 100

JE#10 --> transtype : '13'

Dr. A/R account (1410) 1000

Dr. COGS (1300) 990

CR. VAT (1200) 10

CR. Revenue (1900) 40

Cr. inventory (1200) 40

how to have debit and credit in a certain query or SQL script that calculated from JE#12 and JE#10 ? do I have to make separated columns for both in the query, so there are 2 debits and 2 credits column ? if I create one column either for credit and debit, I am afraid if I make contraact field as condition (define survey variables), I have tried to develop the query for 1 weeks but not success yet. Pls give advice. I appreciate your answer. TIA

John

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Nov 27, 2008 at 03:36 AM

    Hello John,

    If I understand correctly, StoreProcedure with T-SQL can satisfy your requirement. Here is the example to get 2 debit and credit , and you may need change accordingly:

    CREATE PROCEDURE dbo.SP_JE 
    	--input parameters
    	(
    	@JE1Number int = 10,
    	@JE2Number int
    	)	
    AS
    
    BEGIN
    	
    DECLARE @debit_JE1 float  --declare the variable for debit in 1st JE
    DECLARE @credit_JE1 float --declare the variable for credit in 1st JE
    DECLARE @debit_JE2 float  --declare the variable for debit in 1st JE
    DECLARE @credit_JE2 float --declare the variable for credit in 1st JE
    
    SELECT @debit_JE1 = Debit FROM JDT1 WHERE TransId = @JE1Number AND Debit <> 0
    SELECT @credit_JE1 = Credit FROM JDT1 WHERE TransId = @JE1Number AND Credit <> 0 
    
    SELECT @debit_JE2 = Debit FROM JDT1 WHERE TransId = @JE2Number AND Debit <> 0
    SELECT @credit_JE2 = Credit FROM JDT1 WHERE TransId = @JE2Number AND Credit <> 0 
    
    --Ouput the result
    SELECT @debit_JE1 AS Debit1,@credit_JE1 As Credit1,@debit_JE2 As Debit2,@credit_JE2 As Credit2
    
    END

    Kind Regards, Yatsea

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Yatsea,

      Thx for the SP. I could not use the SP in the query editor of SAP B1. Is it possible to have the query only but not T-SQL ?Pls give advice.

      John

      Edited by: Rui Pereira on Dec 23, 2008 4:07 PM

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.