Query Help

[Former Member]

Dear Experts,

I want to add Offset Acc Name and Drawers Name(Asset,liability, etc..) to below query.

Can you please suggest me,

SELECT

T1.[Account],

T2.[AcctName],

T0.[RefDate],

T0.[DueDate],

T0.[TaxDate],

T1.BaseRef 'Doc No',

(Case when t0.TransType = '15' then 'Delivery'

  when t0.TransType = '16' then 'Returns'

  when t0.TransType = '203' then 'A/R Down Payment'

  when t0.TransType = '13' then 'A/R Invoice'

  when t0.TransType = '165' then 'A/R Correction Invoice'

  when t0.TransType = '166' then 'A/R Correction Invoice Reversal'

  when t0.TransType = '14' then 'A/R Credit Memo'

  when t0.TransType = '132' then 'Correction Invoice'

  when t0.TransType = '20' then 'Goods Receipt PO'

  when t0.TransType = '21' then 'Goods Return'

  when t0.TransType = '204' then 'A/P Down Payment'

  when t0.TransType = '18' then 'A/P Invoice'

  when t0.TransType = '163' then 'A/P Correction Invoice'

  when t0.TransType = '164' then 'A/P Correction Invoice Reversal'

  when t0.TransType = '19' then 'A/P Credit Memo'

  when t0.TransType = '69' then 'Landed Costs'

  when t0.TransType = '24' then 'Incoming Payment'

  when t0.TransType = '25' then 'Deposit'

  when t0.TransType = '46' then 'Vendor Payment'

  when t0.TransType = '57' then 'Checks for Payment'

  when t0.TransType = '76' then 'Postdated Deposit'

  when t0.TransType = '182' then 'BoE Transaction'

  when t0.TransType = '-2' then 'Opening Balance'

  when t0.TransType = '-3' then 'Closing Balance'

  when t0.TransType = '321' then 'Internal Reconciliation'

  when t0.TransType = '30' then 'Journal Entry'

  when t0.TransType = '58' then 'Stock List'

  when t0.TransType = '59' then 'Goods Receipt'

  when t0.TransType = '60' then 'Goods Issue'

  when t0.TransType = '67' then 'Inventory Transfers'

  when t0.TransType = '68' then 'Work Instructions'

  when t0.TransType = '162' then 'Inventory Valuation'

  when t0.TransType = '202' then 'Production Order'

  when t0.TransType = '-1' then 'All Transactions'

end) 'Document Type',

T0.Number,

T1.[TransId] 'Trans No',

T1.[ProfitCode],

T1.Debit,

T1.Credit,

T0.[Memo] 'Remarks',

T1.[ContraAct],

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]

FROM

OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode

[kothandaraman_nagarajan · Accepted solution]

Hi,

1. Do not query OJDT and JDT1 without adding any condtions in where clause. If too many records, your system may be hanged.

Try this:

SELECT
T2.[FormatCode],
T2.[AcctName],
T0.[RefDate],
T0.[DueDate],
T0.[TaxDate],
T1.BaseRef 'Doc No',
(Case when t0.TransType = '15' then 'Delivery'
  when t0.TransType = '16' then 'Returns'
when t0.TransType = '203' then 'A/R Down Payment'
  when t0.TransType = '13' then 'A/R Invoice'
when t0.TransType = '165' then 'A/R Correction Invoice'
when t0.TransType = '166' then 'A/R Correction Invoice Reversal'
when t0.TransType = '14' then 'A/R Credit Memo'
when t0.TransType = '132' then 'Correction Invoice'
when t0.TransType = '20' then 'Goods Receipt PO'
when t0.TransType = '21' then 'Goods Return'
when t0.TransType = '204' then 'A/P Down Payment'
when t0.TransType = '18' then 'A/P Invoice'
when t0.TransType = '163' then 'A/P Correction Invoice'
when t0.TransType = '164' then 'A/P Correction Invoice Reversal'
when t0.TransType = '19' then 'A/P Credit Memo'
when t0.TransType = '69' then 'Landed Costs'
when t0.TransType = '24' then 'Incoming Payment'
when t0.TransType = '25' then 'Deposit'
when t0.TransType = '46' then 'Vendor Payment'
when t0.TransType = '57' then 'Checks for Payment'
when t0.TransType = '76' then 'Postdated Deposit'
when t0.TransType = '182' then 'BoE Transaction'
when t0.TransType = '-2' then 'Opening Balance'
  when t0.TransType = '-3' then 'Closing Balance'
when t0.TransType = '321' then 'Internal Reconciliation'
  when t0.TransType = '30' then 'Journal Entry'
when t0.TransType = '58' then 'Stock List'
  when t0.TransType = '59' then 'Goods Receipt'
when t0.TransType = '60' then 'Goods Issue'
  when t0.TransType = '67' then 'Inventory Transfers'
  when t0.TransType = '68' then 'Work Instructions'
  when t0.TransType = '162' then 'Inventory Valuation'
when t0.TransType = '202' then 'Production Order'
when t0.TransType = '-1' then 'All Transactions'
end) 'Document Type', (Case when t2.GroupMask = '1' then 'Asset'
  when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Equity'when t2.GroupMask = '4' then 'Revenue' when t2.GroupMask = '5' then 'Expenditure' end)'Drawer',
T0.Number,
T1.[TransId] 'Trans No',
T1.[ProfitCode],
T1.Debit,
T1.Credit,
T0.[Memo] 'Remarks',
T1.[ContraAct],

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]
FROM
OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode where T0.[RefDate] between [%0] and [%1]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hello Sir,

Thank you for the information.

Can you please suggest me?

How can bring Offset Acc Name in this query?

I tried below.

Tried to link tables JDT1 and OACT on field JDT1.ContraAct = OACT.AccCode (Left Outer Join)

But not helps me.

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT
T2.[FormatCode],
T2.[AcctName],
T0.[RefDate],
T0.[DueDate],
T0.[TaxDate],
T1.BaseRef 'Doc No',
(Case when t0.TransType = '15' then 'Delivery'
  when t0.TransType = '16' then 'Returns'
when t0.TransType = '203' then 'A/R Down Payment'
  when t0.TransType = '13' then 'A/R Invoice'
when t0.TransType = '165' then 'A/R Correction Invoice'
when t0.TransType = '166' then 'A/R Correction Invoice Reversal'
when t0.TransType = '14' then 'A/R Credit Memo'
when t0.TransType = '132' then 'Correction Invoice'
when t0.TransType = '20' then 'Goods Receipt PO'
when t0.TransType = '21' then 'Goods Return'
when t0.TransType = '204' then 'A/P Down Payment'
when t0.TransType = '18' then 'A/P Invoice'
when t0.TransType = '163' then 'A/P Correction Invoice'
when t0.TransType = '164' then 'A/P Correction Invoice Reversal'
when t0.TransType = '19' then 'A/P Credit Memo'
when t0.TransType = '69' then 'Landed Costs'
when t0.TransType = '24' then 'Incoming Payment'
when t0.TransType = '25' then 'Deposit'
when t0.TransType = '46' then 'Vendor Payment'
when t0.TransType = '57' then 'Checks for Payment'
when t0.TransType = '76' then 'Postdated Deposit'
when t0.TransType = '182' then 'BoE Transaction'
when t0.TransType = '-2' then 'Opening Balance'
  when t0.TransType = '-3' then 'Closing Balance'
when t0.TransType = '321' then 'Internal Reconciliation'
  when t0.TransType = '30' then 'Journal Entry'
when t0.TransType = '58' then 'Stock List'
  when t0.TransType = '59' then 'Goods Receipt'
when t0.TransType = '60' then 'Goods Issue'
  when t0.TransType = '67' then 'Inventory Transfers'
  when t0.TransType = '68' then 'Work Instructions'
  when t0.TransType = '162' then 'Inventory Valuation'
when t0.TransType = '202' then 'Production Order'
when t0.TransType = '-1' then 'All Transactions'
end) 'Document Type', (Case when t2.GroupMask = '1' then 'Asset'
  when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Equity'when t2.GroupMask = '4' then 'Revenue' when t2.GroupMask = '5' then 'Expenditure' end)'Drawer',
T0.Number,
T1.[TransId] 'Trans No',
T1.[ProfitCode],
T1.Debit,
T1.Credit,
T0.[Memo] 'Remarks',
T1.[ContraAct], T2.[AcctName],

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]
FROM
OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode where T0.[RefDate] between [%0] and [%1]

[Former Member · Accepted solution]

Hi Sir,

I checked above query but not giving offset acc name.

Query showing me FormatCode Fields Account Name,

I want ContraAct Fields Account Name in the query Sir.

Thank you for response to my problem,

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT
T2.[FormatCode],
T2.[AcctName],
T0.[RefDate],
T0.[DueDate],
T0.[TaxDate],
T1.BaseRef 'Doc No',
(Case when t0.TransType = '15' then 'Delivery'
  when t0.TransType = '16' then 'Returns'
when t0.TransType = '203' then 'A/R Down Payment'
  when t0.TransType = '13' then 'A/R Invoice'
when t0.TransType = '165' then 'A/R Correction Invoice'
when t0.TransType = '166' then 'A/R Correction Invoice Reversal'
when t0.TransType = '14' then 'A/R Credit Memo'
when t0.TransType = '132' then 'Correction Invoice'
when t0.TransType = '20' then 'Goods Receipt PO'
when t0.TransType = '21' then 'Goods Return'
when t0.TransType = '204' then 'A/P Down Payment'
when t0.TransType = '18' then 'A/P Invoice'
when t0.TransType = '163' then 'A/P Correction Invoice'
when t0.TransType = '164' then 'A/P Correction Invoice Reversal'
when t0.TransType = '19' then 'A/P Credit Memo'
when t0.TransType = '69' then 'Landed Costs'
when t0.TransType = '24' then 'Incoming Payment'
when t0.TransType = '25' then 'Deposit'
when t0.TransType = '46' then 'Vendor Payment'
when t0.TransType = '57' then 'Checks for Payment'
when t0.TransType = '76' then 'Postdated Deposit'
when t0.TransType = '182' then 'BoE Transaction'
when t0.TransType = '-2' then 'Opening Balance'
  when t0.TransType = '-3' then 'Closing Balance'
when t0.TransType = '321' then 'Internal Reconciliation'
  when t0.TransType = '30' then 'Journal Entry'
when t0.TransType = '58' then 'Stock List'
  when t0.TransType = '59' then 'Goods Receipt'
when t0.TransType = '60' then 'Goods Issue'
  when t0.TransType = '67' then 'Inventory Transfers'
  when t0.TransType = '68' then 'Work Instructions'
  when t0.TransType = '162' then 'Inventory Valuation'
when t0.TransType = '202' then 'Production Order'
when t0.TransType = '-1' then 'All Transactions'
end) 'Document Type', (Case when t2.GroupMask = '1' then 'Asset'
  when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Equity'when t2.GroupMask = '4' then 'Revenue' when t2.GroupMask = '5' then 'Expenditure' end)'Drawer',
T0.Number,
T1.[TransId] 'Trans No',
T1.[ProfitCode],
T1.Debit,
T1.Credit,
T0.[Memo] 'Remarks',
T1.[ContraAct],Case When T1.ContraAct=T4.AcctCode

Then T4.AcctName

     Else Case When T1.ContraAct=T3.CardCode

Then T3.CardName

     Else Null End End 'Contra Act Name' ,

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]
FROM
OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode
LEFT JOIN OCRD T3 ON T3.CardCode=T1.ContraAct

     LEFT JOIN OACT T4 ON T4.AcctCode =T1.ContraAct

where T0.[RefDate] between [%0] and [%1]

[Former Member · Accepted solution]

Hi SIr,

Thanks a lot.

Above query works for me!!

Thanks,

[Former Member]

Any Update please

[former_member205766]

Hi

Check the below query

SELECT

T1.[Account],

T2.[AcctName],

T0.[RefDate],

T0.[DueDate],

T0.[TaxDate],

T1.BaseRef 'Doc No',

(Case when t0.TransType = '15' then 'Delivery'

  when t0.TransType = '16' then 'Returns'

  when t0.TransType = '203' then 'A/R Down Payment'

  when t0.TransType = '13' then 'A/R Invoice'

  when t0.TransType = '165' then 'A/R Correction Invoice'

  when t0.TransType = '166' then 'A/R Correction Invoice Reversal'

  when t0.TransType = '14' then 'A/R Credit Memo'

  when t0.TransType = '132' then 'Correction Invoice'

  when t0.TransType = '20' then 'Goods Receipt PO'

  when t0.TransType = '21' then 'Goods Return'

  when t0.TransType = '204' then 'A/P Down Payment'

  when t0.TransType = '18' then 'A/P Invoice'

  when t0.TransType = '163' then 'A/P Correction Invoice'

  when t0.TransType = '164' then 'A/P Correction Invoice Reversal'

  when t0.TransType = '19' then 'A/P Credit Memo'

  when t0.TransType = '69' then 'Landed Costs'

  when t0.TransType = '24' then 'Incoming Payment'

  when t0.TransType = '25' then 'Deposit'

  when t0.TransType = '46' then 'Vendor Payment'

  when t0.TransType = '57' then 'Checks for Payment'

  when t0.TransType = '76' then 'Postdated Deposit'

  when t0.TransType = '182' then 'BoE Transaction'

  when t0.TransType = '-2' then 'Opening Balance'

  when t0.TransType = '-3' then 'Closing Balance'

  when t0.TransType = '321' then 'Internal Reconciliation'

  when t0.TransType = '30' then 'Journal Entry'

  when t0.TransType = '58' then 'Stock List'

  when t0.TransType = '59' then 'Goods Receipt'

  when t0.TransType = '60' then 'Goods Issue'

  when t0.TransType = '67' then 'Inventory Transfers'

  when t0.TransType = '68' then 'Work Instructions'

  when t0.TransType = '162' then 'Inventory Valuation'

  when t0.TransType = '202' then 'Production Order'

  when t0.TransType = '-1' then 'All Transactions'

end) 'Document Type', (Case when t2.GroupMask = '1' then 'Asset'

  when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Equity'when t2.GroupMask = '4' then 'Revenue' when t2.GroupMask = '5' then 'Expenditure' end)'Drawer',

T0.Number,

T1.[TransId] 'Trans No',

T1.[ProfitCode],

T1.Debit,

T1.Credit,

T0.[Memo] 'Remarks',

T1.[ContraAct],

T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]

FROM

OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode

With Regards

Balaji Sampath

[Former Member]

Hi Balaji,

Thanks a lot for helping me.

Your query is correct and its giving result as i want.

I just want to add one more field here and i.e,

Offset Acc Name

Please suggest me how can i do this?

Thanks a lot once again,