Version : 13000

DB : PGSQL / MSSQL

OUTPUT:

SELECT po.POCUSTOMID AS "PO Number",
max(po.PONAME) AS "PO Name",
ad.ORG_NAME AS "Account",
longtodate(max(po.DATEORDERED)) AS "Created On",
(li.name) "Approver Level",
(aaa.FIRST_NAME) "Approver Name",
(pos1.STATUSNAME) "Approval Status",
max(pos.STATUSNAME) AS "PO Status",
poaddfield.UDF_CHAR1 AS "Legal Entity" 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 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 product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceType rtype ON productType.RESOURCETYPEID=rtype.RESOURCETYPEID
LEFT JOIN ResourceCategory rCategory ON productType.RESOURCECATEGORYID=rCategory.RESOURCECATEGORYID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN purchase_approvallevel pol ON po.PURCHASEORDERID=pol.entityid
LEFT JOIN approvallevel li ON pol.levelid=li.ID
LEFT JOIN approvalstatusdefinition pos1 ON li.STATUS_ID=pos1.STATUSID
LEFT JOIN approvaldetails apd ON li.ID=apd.approval_level_id
LEFT JOIN AaaUser aaa ON apd.APPROVERID=aaa.USER_ID
Left JOIN POAccMapping poacc ON po.PURCHASEORDERID=poacc.PURCHASEORDERID  
left JOIN AccountDefinition ad ON poacc.ACCOUNTID=ad.ORG_ID
LEFT JOIN PurchaseOrder_Fields poaddfield ON po.PURCHASEORDERID=poaddfield.PURCHASEORDERID GROUP BY  po.purchaseorderid, po.POCUSTOMID, ad.ORG_NAME, aaa.FIRST_NAME, li.name, pos1.STATUSNAME, poaddfield.UDF_CHAR1
ORDER BY po.purchaseorderid, po.POCUSTOMID ASC