Custom Report Help
Below is the custom query report that we created which worked fine for my team. However, we were requested by our Finance team to create one with several other pieces of info. We are good on all of it with the exception of the Level 1 and 2 approvers. Can anyone help with what we need to include in our query to get the correct names?
Original Report:
SELECT
paao.NAME AS "Vendor Name",
po.POCUSTOMID AS "PO Number",
poaddfield.UDF_CHAR5 AS "Invoice #",
paao.NAME AS "Vendor Name",
po.TOTALPRICE AS "Total Price",
u.FIRST_NAME AS "Approver",
po.SHIPTO AS "Ship To",
pos.STATUSNAME AS "PO Status",
poaddfield.UDF_CHAR1 AS "Project Code",
poaddfield.UDF_CHAR3 AS "Additional Project/Budget Code",
pogl.GLCODE AS "GL Code",
poaddfield.UDF_CHAR2 AS "Additional GL Code",
po.COMMENTS AS "Shipping Comments"
FROM PurchaseOrder po
LEFT JOIN POStatus pos ON po.STATUSID=pos.STATUSID
LEFT JOIN VendorDefinition pvd ON po.VENDORID=pvd.VENDORID
LEFT JOIN SDOrganization paao ON pvd.VENDORID=paao.ORG_ID
LEFT JOIN PurchaseOrder_Fields poaddfield ON po.PURCHASEORDERID=poaddfield.PURCHASEORDERID
LEFT JOIN PurchaseGLCodes pogl ON po.GLCODE=pogl.GLCODEID
LEFT JOIN PurchaseGLCodes pogl ON po.GLCODE=pogl.GLCODEID
LEFT JOIN PurchaseApprovalDetails pad ON po.PURCHASEORDERID=pad.APPROVALID
LEFT JOIN AAAUser u ON pad.APPROVERID=u.USER_ID
WHERE (pos.STATUSNAME = 'Invoice Received')
ORDER BY 1 NULLS FIRST , 2 NULLS FIRST
What the new report needs to return:
Budget/Project Code ( poaddfield.UDF_CHAR1 AS "Project Code" )
Additional Budget/Project Code ( poaddfield.UDF_CHAR3 AS "Additional Project/Budget Code" )
GL Code ( pogl.GLCODE AS "GL Code" )
PO Number ( po.POCUSTOMID AS "PO Number" )
Vendor ( paao.NAME AS "Vendor Name" )
Invoice # ( poaddfield.UDF_CHAR5 AS "Invoice #")
Total Amount ( po.TOTALPRICE AS "Total Price" )
Remarks
Approver Level 1 ( u.FIRST_NAME AS "Approver")
Approver Level 2
I will need it to pull only PO's with a created date of yesterday (WHERE CREATEDTIME >= <from_yesterday> AND CREATEDTIME >= <to_yesterday>) and I need the columns to display in the order I listed.