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