You’re offline. This is a read only version of the page.
I need to add a few things to this sql view and I'm having problems. Any help would be appreciated. I need to ensure I get all payments including ones not tied to PO and I need to capture the line item detail attached to the shipment/invoice by Payment. Here is the view
SELECT PO.PONUMBER PO_Number,
RH.POPRCTNM POP_Receipt_Number,
RH.VCHRNMBR Voucher_Number,
RH.VENDORID Vendor_ID,
coalesce(PM.APFRDCNM,'') Payment_Number,
coalesce(PM.DOCDATE,'1/1/1900') Payment_Date,
coalesce(PM.APFRMAPLYAMT,0) Payment_Amount,
CASE PM.DOCTYPE
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Credit Memo'
WHEN 6 THEN 'Payment'
ELSE 'Not Paid'
END Payment_Type
FROM POP30300 RH
LEFT OUTER JOIN
(SELECT VENDORID, APTVCHNM, APFRDCNM, DOCTYPE,
DOCDATE, APFRMAPLYAMT
FROM PM10200
UNION ALL
SELECT VENDORID, APTVCHNM, APFRDCNM, DOCTYPE,
DOCDATE, APFRMAPLYAMT
FROM PM30300) PM
ON RH.VCHRNMBR = PM.APTVCHNM AND RH.VENDORID = PM.VENDORID
LEFT OUTER JOIN
(SELECT PONUMBER, POPRCTNM, POPTYPE FROM POP10500
WHERE POPTYPE IN (2,3)
GROUP BY PONUMBER, POPRCTNM, POPTYPE) PO
ON PO.POPRCTNM = RH.POPRCTNM
WHERE RH.POPTYPE IN (2,3)
*This post is locked for comments
This query worked great I just have one problem. The apply amount is duplicating the entire check amount per item so when I total on the entire amount I get duplicate numbers of the checks with multiple line items. I need to pull the extended price amount from the line item so the check amount totals properly only on the checks that have duplicate line items. Can you assist?
Mariano, I am new in GP but I would like to practice SQL, How i can get into the database to see the tables, query and sp, Please help me
Just a rephrasing,
SELECT apply.VENDORID,
apply.DOCDATE AS Payment_Date,
apply.VCHRNMBR AS Payment_Voucher,
apply.doctype AS Credit_Type,
apply.apfrdcnm AS Check_Number,
apply.aptvchnm,
apply.aptodcnm AS Invoice_Number,
apply.appldamt AS Applied_Amount,
poIvc.itemnmbr AS Item_Number,
poIvc.ITEMDESC,
poIvc.poptype
FROM
(
SELECT vendorid,
docdate,
vchrnmbr,
CASE doctype
WHEN 6 THEN 'Payment'
WHEN 5 THEN 'Credit'
END AS doctype,
apfrdcnm,
aptvchnm,
APTODCNM,
APPLDAMT
FROM PM30300
UNION ALL
SELECT
vendorid,
docdate,
vchrnmbr,
CASE doctype
WHEN 6 THEN 'Payment'
WHEN 5 THEN 'Credit'
END AS doctype,
apfrdcnm,
aptvchnm,
APTODCNM,
APPLDAMT
FROM PM10200
) AS apply
LEFT OUTER JOIN
(
SELECT a.ponumber,
a.poprctnm,
b.VNDDOCNM,
CASE b.POPTYPE
WHEN 2 THEN 'Invoice'
WHEN 3 THEN 'Shipment/Invoice'
END AS poptype,
a.itemnmbr,
a.itemdesc,
b.vendorid
FROM POP30310 a
INNER JOIN POP30300 b
ON (a.POPRCTNM = b.POPRCTNM) AND
(b.POPTYPE in (2,3))
) AS poIvc
ON (poIvc.vnddocnm = apply.aptodcnm) AND
(poIvc.vendorid = apply.vendorid)
Hello, I would like to enter some SQL but I do know how to get into it to see the tables, please help!!
Mariano I"m receiving errors when I try to get this to work. Perhaps I'm doing something wrong can you incorporate your scripting together with mine and create one script that I can run as i think I missing some syntax somewhere. Thank you
with apply as (
select vendorid, docdate, vchrnmbr, case doctype when 6 then 'Payment' when 5 then 'Credit' end as doctype, apfrdcnm, aptvchnm, APTODCNM, APPLDAMT from pm30300
union all
select vendorid, docdate, vchrnmbr, case doctype when 6 then 'Payment' when 5 then 'Credit' end as doctype, apfrdcnm, aptvchnm, APTODCNM, APPLDAMT from pm10200
),
poIvc as (
select a.ponumber, a.poprctnm, b.VNDDOCNM, case b.POPTYPE when 2 then 'Invoice' when 3 then 'Shipment/Invoice' end as poptype, a.itemnmbr, a.itemdesc, b.vendorid
from pop30310 a inner join pop30300 b on (a.POPRCTNM = b.POPRCTNM) and (b.POPTYPE in (2,3))
)
select apply.vendorid, apply.docdate as payment_date, apply.vchrnmbr as payment_voucher, apply.doctype as credit_type, apply.apfrdcnm as check_number, apply.aptvchnm, apply.aptodcnm as invoice_number,
apply.appldamt as applied_amount, poIvc.itemnmbr as item_number, poIvc.itemdesc, poIvc.poptype
from apply
left outer join poIvc on (poIvc.vnddocnm = apply.aptodcnm) and (poIvc.vendorid = apply.vendorid)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (