link between purchase order and AP reserve invoice

Question

How do I get every AP reserve invoice for a purchase order using the SQL version of SAP Business One?

My current query:

SELECT 
  OPOR.CardCode, 
  OPOR.CardName, 
  OPOR.U_ReferenceNo, 
  OPOR.NumAtCard, 
  OPOR.DocNum, 
  OPOR.U_ShipmentTime, 
  OPOR.U_DealType, 
  OPOR.DocDate, 
  OPOR.U_Origin, 
  OPOR.U_DealQuantity, 
  OPOR.U_ContainerNo, 
  POR1.ItemCode, 
  POR1.Dscription, 
  POR1.Price, 
  por1.U_CopperRate, 
  POR1.U_AluminiumRate, 
  POR1.U_IronRate, 
  POR1.U_CopperPer, 
  por1.U_AluminiumPer, 
  POR1.U_LeadPer, 
  POR1.U_IronPer, 
  POR1.U_PlasticPer 

FROM
  OPOR 
  INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry 

WHERE
  OPOR.DocStatus = 'o'

William Brochensque junior · Accepted Answer · 2023-08-18T10:08:52.423

0

You can use sap.erpref.com to get more details about every table on a SAP Business one SQL schema.

The A/P invoice table is OPCH, and the field reserve means it is a A/P reserve invoice.

So your select should be like this:

SELECT 
  OPOR.CardCode,
  MAX(OPOR.CardName), 
  MAX(OPOR.U_ReferenceNo), 
  MAX(OPOR.NumAtCard), 
  MAX(OPOR.DocNum), 
  MAX(OPOR.U_ShipmentTime), 
  MAX(OPOR.U_DealType), 
  MAX(OPOR.DocDate), 
  MAX(OPOR.U_Origin), 
  MAX(OPOR.U_DealQuantity), 
  MAX(OPOR.U_ContainerNo), 
  MAX(POR1.ItemCode), 
  MAX(POR1.Dscription), 
  MAX(POR1.Price), 
  MAX(POR1.U_CopperRate), 
  MAX(POR1.U_AluminiumRate), 
  MAX(POR1.U_IronRate), 
  MAX(POR1.U_CopperPer), 
  MAX(POR1.U_AluminiumPer), 
  MAX(POR1.U_LeadPer), 
  MAX(POR1.U_IronPer), 
  MAX(POR1.U_PlasticPer),
  COUNT(OPCH. DocNum)

FROM
  OPOR 
  LEFT JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry 
  LEFT JOIN OPCH ON OPOR.DocEntry = OPCH.DocEntry

WHERE
  OPOR.DocStatus = 'o'
  AND OPCH.reserve = 'Y'
  
 GROUP BY
    OPOR.CardCode

edited Aug 18 '23 at 10:08

answered Aug 17 '23 at 16:01

William Brochensque junior

2,385
18
35
46

hey williams thanks i want the count of total ap reserve invoices is it possible without grouping? – irtaza malik Aug 18 '23 at 09:22
I altered my answer. Made the select to get the total of ap reserve invoices per CardCode, which I think might make more sense. If not, you can remove the "group by" and it will be total in the entire table (Or if you want to group but without group by, remove the group by and add `distinct` on the OPOR.CardCode). Mark my answer as accepted if it does help you! Thanks. – William Brochensque junior Aug 18 '23 at 10:11
bro i found the solution by over by func now i am getting ap reserve inv count but i dont know i have done something wrong with joins check this query from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry left join PCH1 on PCH1.BaseEntry = POR1.DocEntry LEFT JOIN OPCH ON PCH1.BaseEntry = OPCH.DocEntry – irtaza malik Aug 18 '23 at 10:45
You can use [sap.erpref.com](https://sap.erpref.com) to check the table unique indentifiers. Use left joins, not inner joins. – William Brochensque junior Aug 18 '23 at 13:39

link between purchase order and AP reserve invoice

1 Answers1