SELECT
    po.POCUSTOMID AS "PO Number",
    MAX(po.PONAME) AS "PO Name",
    MAX(po.TERMS) AS "PO Terms",
    LONGTODATE(MAX(po.DATEORDERED)) AS "Ordered Date",
    MAX(pos.STATUSNAME) AS "PO Status",
    MAX(poa.FIRST_NAME) AS "Purchase Requester",
    MAX(po.SHIPTO) AS "Ship To",
    MAX(paao.NAME) AS "Vendor Name",
    MAX(inde.INVOICEID) AS "Invoice Number",
    LONGTODATE(MAX(inde.DATERECEIVED)) AS "Invoice Date",
    LONGTODATE(MAX(pdet.PAYMENTDATE)) AS "PaymentDate",
    MAX(pdet.AMOUNTPAID) AS "Amount Paid",
    MAX(pxt.PRICE) AS "Item Price",
    MAX(po.TOTALPRICE) AS "Total Price",
    MAX(po.SALESTAX) AS "Sales Tax",
    MAX(po.SHIPPINGPRICE) AS "Shipping Price",
    MAX(pxt.QUANTITYORDERED) AS "Quantity of Items",
    cd.COMPONENTNAME AS "Item",
    MAX(cd.PARTNO) AS "Item Part Number",
    MAX(po.REMARKS) AS "Remarks",
    MAX(productType.COMPONENTTYPENAME) AS "Product Type"
FROM PurchaseOrder po
LEFT JOIN POStatus pos
    ON po.STATUSID = pos.STATUSID
LEFT JOIN SDUser por
    ON po.REQUESTERID = por.USERID
LEFT JOIN AaaUser poa
    ON por.USERID = poa.USER_ID
LEFT JOIN VendorDefinition pvd
    ON po.VENDORID = pvd.VENDORID
LEFT JOIN SDOrganization paao
    ON pvd.VENDORID = paao.ORG_ID
LEFT JOIN PurchaseOrderItem pxt
    ON po.PURCHASEORDERID = pxt.PURCHASEORDERID
LEFT JOIN PurchaseLot plot
    ON pxt.PURCHASEORDERITEMID = plot.PURCHASEORDERITEMID
LEFT JOIN Resources resource
    ON plot.PURCHASELOTID = resource.PURCHASELOTID
LEFT JOIN ComponentDefinition cd
    ON pxt.COMPONENTID = cd.COMPONENTID
LEFT JOIN InvoiceDetails inde
    ON po.PURCHASEORDERID = inde.PURCHASEORDERID
LEFT JOIN ComponentType productType
    ON cd.COMPONENTTYPEID = productType.COMPONENTTYPEID
LEFT JOIN ResourceOwner rOwner
    ON resource.RESOURCEID = rOwner.RESOURCEID
LEFT JOIN ResourceAssociation rToAsset
    ON rOwner.RESOURCEOWNERID = rToAsset.RESOURCEOWNERID
LEFT JOIN DepartmentDefinition deptDef
    ON rOwner.DEPTID = deptDef.DEPTID
LEFT JOIN ResourceLocation resLocation
    ON resource.RESOURCEID = resLocation.RESOURCEID
LEFT JOIN SiteDefinition siteDef
    ON resLocation.SITEID = siteDef.SITEID
LEFT JOIN SDOrganization aaov
    ON siteDef.SITEID = aaov.ORG_ID
LEFT JOIN PaymentDetails pdet
    ON po.PURCHASEORDERID = pdet.PURCHASEORDERID
WHERE 
 po.DATEORDERED >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2025-06-30 00:00:00') * 1000 AS BIGINT)
  AND po.DATEORDERED <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2026-06-30 00:00:00') * 1000 AS BIGINT)
GROUP BY
    po.POCUSTOMID,
    cd.COMPONENTNAME ORDER BY 1;