Query Inquiries on Journal Entry

[geraldhans]

Hi All

I'm trying to generate report for Items Issued by both Good Issue and A/P Invoice. I did this by joining both OJDT, JDT1, OPCH, PCH1, OIGE and IGE1

However I've issues with the following code since I only want to display both Document that meet the following criteria

• Document that are not Canceled (from both Good Issue and A/P Invoice)
• Document that are have Closed Status (from both Good Issue and A/P Invoice)

I wrote the following query

SELECT DISTINCT CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Date ) ELSE (SELECT T5.U_Dlvr_Date) END AS 'Delivery Date',T1.RefDate AS 'Posting Date', --T1.Memo AS 'Description',
CASE WHEN T1.TransType = '18' THEN (SELECT T4.ItemCode) ELSE (SELECT T6.ItemCode) END AS 'Item Code', 
CASE WHEN T1.TransType = '18' THEN (SELECT T4.Dscription) ELSE (SELECT T6.Dscription) END AS 'Item Name',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_rnd_rmrk) ELSE (SELECT T5.U_rnd_rmrk) END AS 'Remark',
CASE WHEN T1.TransType = '18' THEN (SELECT T4.Quantity) ELSE (SELECT T6.Quantity) END AS 'Quantity',
CASE WHEN T1.TransType = '18' THEN (SELECT CASE WHEN T4.UomCode = 'Manual' THEN 'KG' END AS 'UoM') ELSE (SELECT T6.UomCode) END AS 'UoM', 
--CASE WHEN T1.TransType = '18' THEN (SELECT T4.LineTotal) ELSE (SELECT T6.LineTotal) END AS 'Price',
CASE T1.TransType WHEN 18 THEN 'A/P Invoice' WHEN 30 THEN 'Journal Entry'  WHEN 46 THEN 'Outgoing Payments' WHEN 60 THEN 'Goods Issue' END AS 'Document Source', --T1.BaseRef AS 'Document Source Number', 
CASE WHEN T1.TransType = '18' THEN (SELECT T3.CardName) ELSE (SELECT T5.CardName) END AS 'Supplier Name' ,
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Itm_Src) ELSE (SELECT T5.U_Itm_Src) END AS 'Source', 
CASE WHEN T1.TransType = '18' THEN (SELECT CASE WHEN T3.U_Ref_No IS NULL THEN (SELECT T1.BaseRef) ELSE (SELECT CAST (T3.U_Ref_No AS VARCHAR (1200))) END AS 'Reference Number') ELSE (SELECT CASE WHEN T5.U_Ref_No IS NULL THEN (SELECT T1.BaseRef) ELSE (SELECT CAST (T5.U_Ref_No AS varchar (1200))) END AS 'Reference Number') END AS 'Reference Number', 
CASE WHEN T1.TransType = '18' THEN (SELECT T1.BaseRef) ELSE (SELECT T5.U_Ref_No) END AS 'Reference Number', 
CASE WHEN T1.TransType = '18'  THEN (SELECT T3.U_Itm_Orgn) ELSE (SELECT T5.U_Itm_Orgn) END AS 'Origins', 
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Frm) ELSE (SELECT T5.U_Dlvr_Frm) END AS 'Delivery From',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Dest) ELSE (SELECT T5.U_Dlvr_Dest) END AS 'Destination', 
CASE WHEN T1.TransType = '18' THEN (Select T3.U_Dlvr_Courier) ELSE (SELECT T5.U_Dlvr_Courier) END AS 'Courier',
CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_awb_No) ELSE (SELECT T5.U_Awb_No)END AS 'AwB No', 
--Case WHEN T7.BatchNum IS NULL THEN (SELECT T3.U_Btch_Stck) WHEN T3.U_Btch_Stck IS NULL THEN (SELECT T5.U_Btch_Stck) ELSE (SELECT T7.BatchNum) END AS 'Batch Number' ,
CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) ELSE (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) END AS 'Test Result',
T1.BaseRef AS 'Document Reference Number',
--CASE WHEN T1.TransType = '18' THEN (SELECT T3.CANCELED) ELSE (SELECT t5.CANCELED)END AS 'Document Status', 
CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_Dlvr_Prp AS varchar (1200))) ELSE (SELECT CAST (T5.U_Dlvr_Prp AS varchar (1200))) END AS 'Purpose'
FROM OJDT T1
LEFT JOIN JDT1 T2 ON T1.TransID = T2.TransId
LEFT JOIN OPCH T3 ON T1.BaseRef =  T3.DocNum
LEFT JOIN PCH1 T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN OIGE T5 ON T1.BaseRef = T5.DocNum
LEFT JOIN IGE1 T6 ON T6.DocEntry = T5.DocEntry
LEFT JOIN IBT1 T7 ON T6.ObjType = T7.BaseType and T5.DocEntry = T7.BaseEntry and T6.LineNum = T7.BaseLinNum and T6.ItemCode = T7.ItemCode
LEFT JOIN OPOR T8 ON T7.BatchNum = T8.DocNum
WHERE T2.Account = '610218' AND T1.RefDate BETWEEN '2023-02-01' AND '2023-12-31' AND T3.CANCELED = 'N' OR T3.Canceled = 'C' OR T5.CANCELED = 'N'

However it didn't display what want.

Appreciate your help

Regards

Gerald

[kothandaraman_nagarajan]

Hi,

I don't think there is a option to cancel Goods Issue in SAP B1. Also why do you need join AP invoice with Goods Issue document?

Regards,

Nagarajan

[geraldhans]

Hi Nagarajan

The reason why I join both AP Invoice and Good Issue is I want to track down sample items sent to QC Department for analysis purposes. If you noticed on my query I noted down " T2. Account = '610218'

These account are specifically use for sample purposes. The Finance Department utilize both AP Invoice and Good Issue to issue sample (to keep track). The reason are as follow

• AP Invoice if we buy raw material from suppliers and these items are sent to QC Department for analysis purposes
• Good Issue are use if issue Items from our current stock that are sent to QC Department for analysis purposes

Thank You

Regards
Gerald

[jitin_chawla]

Hi,

There are two different fields in the OPCH and OIGE for the Canceled and the Closed marking. For Canceled, the field is CANCELED and for Closed is DocStatus.

Check by using the below in your query

AND T3.CANCELED = 'N' OR T3.DocStatus = 'C' OR T5.CANCELED = 'N' OR T5.DocStatus = 'C'

Kr,

Jitin

[geraldhans]

Hi Jitin

Many thanks for your help. The query do works, but I have new problem right now since I can't filter based on account specified on my query. The query now takes all data on both AP Invoice and Good Issue instead filtering account based on my query as I want only data that's on 610218 account

Perhaps is there any alternative queries that can help me to resolve my issues?

Thank You

Regards

Gerald