Invoice comments report

Are you willing to share any ideas on how to create a report since the comments are stored in 2 places.

Ruma,

   We needed to pull some comments for a SN for Contract labor.  Here is the entire code.  You can see where we pulled some comments out of A/P.  Hope this helps:

SELECT

 'PO' DOCUMENT_TYPE,
 MMDIST.DIST_COMPANY COMPANY,
 MMDIST.ACCT_UNIT AU,
 GLNAMES.DESCRIPTION AU_DEPT_NAME,
 MMDIST.ACCOUNT ACCT,
 APVENMAST.vendor_sname VENDOR_NAME,
 trim(poline.vendor) VENDOR_NUMBER,
 TRIM(polinesrc.PO_NUMBER) DOCUMENT_NUMBER,
 mmdist.POSTED_DATE DOCUMENT_DATE,
 POLINE.QUANTITY*POLINE.VBUY_UNIT_CST  EXTENDED_AMT,
 BUYER.R_NAME INPUT_PERSON,
 l_hpli.R_NAME COMMENTS_TYPE,
 CASE
   WHEN INSTR( l_hpli.OBJECT, CHR(10),96,1)>0
    THEN
     SUBSTR(l_hpli.OBJECT,96,INSTR( l_hpli.OBJECT, CHR(10),96,1)-96)
  ||
  SUBSTR(l_hpli.OBJECT,INSTR( l_hpli.OBJECT, CHR(10),96,1)+1,INSTR( l_hpli.OBJECT, CHR(10),96,2)-(INSTR( l_hpli.OBJECT, CHR(10),96,1)+1))
 
   ELSE
    SUBSTR(l_hpli.OBJECT,96,50)
   END COMMENTS,
 mmdist.LINE_SEQ LINE,
 POLINE.ITEM ITEM,
 POLINE.description ITEM_DESC, 
 polinesrc.SOURCE_DOC_N REQ_NUMBER,
 requester.R_NAME REQUESTER,
 POLINE.QUANTITY QUANTITY,
 POLINE.VBUY_UNIT_CST UNIT_COST
 
  FROM LAWSON.MMDIST
 left outer join lawson.poline ON
   mmdist.COMPANY = poline.COMPANY
   AND mmdist.SYSTEM_CD = 'PO'
   AND mmdist.LOCATION = poline.LOCATION
   AND mmdist.DOC_NUMBER = POLINE.PO_NUMBER
   AND mmdist.DOC_NBR_NUM = poline.PO_RELEASE
   AND mmdist.PO_CODE = poline.PO_CODE
   AND mmdist.LINE_NBR = poline.LINE_NBR

 
 inner join LAWSON.POLINESRC
   ON poline.COMPANY = polinesrc.COMPANY
   AND poline.PO_NUMBER = polinesrc.PO_NUMBER
   AND poline.PO_RELEASE = polinesrc.PO_RELEASE
   AND poline.PO_CODE = polinesrc.PO_CODE
   AND poline.LINE_NBR = polinesrc.LINE_NBR
  
 left outer join lawson.purchorder
 ON mmdist.COMPANY = purchorder.COMPANY
 AND mmdist.SYSTEM_CD = 'PO'
 AND mmdist.LOCATION = ' '
 AND mmdist.DOC_NUMBER = purchorder.PO_NUMBER
 AND mmdist.PO_CODE = purchorder.PO_CODE
 AND mmdist.DOC_NBR_NUM = purchorder.PO_RELEASE
  
 left outer join lawson.requester
 ON polinesrc.REQUESTER = requester.REQUESTER
 
 left outer join lawson.buyer
 ON poline.BUYER_CODE = buyer.BUYER_CODE
 
  left outer join LAWSON.APVENMAST
  ON poline.vendor = apvenmast.vendor
 
  left outer join lawson.l_hpcr
  ON purchorder.L_INDEX = l_hpcr.L_INDEX  
 
  left outer join lawson.l_dpcr
  ON purchorder.L_INDEX = l_dpcr.L_INDEX
 
  left outer join lawson.l_hpli
  ON poline.L_INDEX = l_hpli.L_INDEX
 
  left outer join lawson.l_dpli
  ON poline.L_INDEX = l_dpli.L_INDEX  
    
  left outer join lawson.glnames
  ON mmdist.DIST_COMPANY = glnames.COMPANY
  AND mmdist.ACCT_UNIT = glnames.ACCT_UNIT
     
   WHERE mmdist.system_cd = 'PO'
   AND MMDIST.DIST_COMPANY >= 200
   AND MMDIST.DIST_COMPANY <= 250
   AND EXTENDED_AMT >= 5000
   AND (MMDIST.ACCOUNT= 7752)
   

   UNION
  
  
   SELECT
  
'INVOICE',
apdistrib.DIST_COMPANY,
apdistrib.DIS_ACCT_UNIT,
glnames.DESCRIPTION,
apdistrib.DIS_ACCOUNT,
apvenmast.VENDOR_SNAME,
trim(apdistrib.VENDOR),
trim(apdistrib.invoice),
apinvoice.DISTRIB_DATE,
apdistrib.ORIG_BASE_AMT,
APINVOICE.OPERATOR,
l_hapi.R_NAME,
CASE
 WHEN
  INSTR( l_hapi.OBJECT, CHR(10),96,1)>0
  THEN
   SUBSTR(l_hapi.OBJECT,96,INSTR( l_hapi.OBJECT, CHR(10),96,1)-96)
     ||
   SUBSTR(l_hapi.OBJECT,INSTR( l_hapi.OBJECT, CHR(10),96,1)+1,INSTR( l_hapi.OBJECT, CHR(10),96,2)-(INSTR( l_hapi.OBJECT, CHR(10),96,1)+1))
     ||
   SUBSTR(l_hapi.OBJECT,INSTR( l_hapi.OBJECT, CHR(10),96,2)+1,INSTR( l_hapi.OBJECT, CHR(10),96,3)-INSTR( l_hapi.OBJECT, CHR(10),96,2+1))
  ELSE
   SUBSTR(l_hapi.OBJECT,96,50)
   END COMMENTS,
0,
' ',
' ',
0,
' ',
0,
0

FROM lawson.apdistrib

left outer join lawson.apvenmast
ON apdistrib.VENDOR = apvenmast.vendor

left outer join lawson.apinvoice
ON apdistrib.company = apinvoice.company
AND apdistrib.vendor = apinvoice.vendor
AND apdistrib.invoice = apinvoice.invoice
AND apdistrib.suffix = apinvoice.suffix
AND apdistrib.cancel_seq = apinvoice.cancel_seq

inner join lawson.apcompany
ON apcompany.COMPANY = apinvoice.COMPANY

left outer join lawson.l_hapi
ON apinvoice.L_INDEX = l_hapi.L_INDEX

left outer join lawson.glnames
ON apdistrib.DIST_COMPANY = glnames.COMPANY
AND apdistrib.DIS_ACCT_UNIT = glnames.ACCT_UNIT

WHERE apdistrib.glt_obj_id <> 0
AND apdistrib.DIST_COMPANY >= 200
AND apdistrib.DIST_COMPANY <= 250
AND TRIM(apinvoice.HANDLING_CODE) = 'EXP'
AND apdistrib.ORIG_BASE_AMT >= 5000
AND (apdistrib.DIS_ACCOUNT = 7752)