SO and PO Discrepancy Report

SOLVED

Before I start designing...  

Is there an existing report to display any changes to a PO that has not been copied to the parent SO?

Thank you!!

Parents
  • 0

    Nope plus there is no recording of changes in PO as there are with SO and SO history.

  • 0 in reply to BigLouie

    I have a question about the SQL query to pull the data for this report.  I need all the data from the PO regardless of whether there is a matching record in SO.  I've put in an additional item into a PO that is not showing up in the report.  Also, I'm missing my "/200-FREIGHT" records, too.

    SELECT DISTINCT

    "SO_SalesOrderDetail"."SalesOrderNo",

    "PO_PurchaseOrderDetail"."PurchaseOrderNo",

    "PO_PurchaseOrderDetail"."ItemCode",

    "PO_PurchaseOrderDetail"."SalesOrderNo",

    "SO_SalesOrderDetail"."ItemCode"

    FROM {oj "PO_PurchaseOrderDetail" "PO_PurchaseOrderDetail"

    JOIN "SO_SalesOrderDetail" "SO_SalesOrderDetail" ON "PO_PurchaseOrderDetail"."ItemCode"="SO_SalesOrderDetail"."ItemCode"}

    WHERE "PO_PurchaseOrderDetail"."SalesOrderNo" = "SO_SalesOrderDetail"."SalesOrderNo"

    ORDER BY "PO_PurchaseOrderDetail"."PurchaseOrderNo"

  • 0 in reply to DPaulTN

    Not a SQL expert, but if you want all records in one file and then the records from the other table where there is a match, you should  use a Left Outer Join.

    Tyler

  • 0 in reply to Tyler Christensen

    OK, down to the small stuff.  Found that I have to get the data in order before it gets joined.  I'm using Sage 100 ERP Advanced V5.00 and Crystal 14.

    Now, this query gives me an error:

    Failed to retrieve data from the database.

    Details: 37000:[ProvideX][ODBC Driver]Expected lexical element not found: <identifier> [Database Vendor Code: 1015 ]

    Thoughts??

    SELECT

    "PO"."PurchaseOrderNo", "PO"."ItemCode", "PO"."ItemType", "PO"."ItemCodeDesc", "PO"."UnitOfMeasure",

    "PO"."SalesOrderNo", "PO"."ExtensionAmt", "SO"."SalesOrderNo", "SO"."ItemCode", "SO"."ItemType",

    "SO"."ItemCodeDesc", "SO"."UnitOfMeasure", "SO"."PurchaseOrderNo", "SO"."UnitPrice","SO"."UnitCost",

    "SO"."ExtensionAmt"

    FROM

    (SELECT "PO_PurchaseOrderDetail"."PurchaseOrderNo",

    "PO_PurchaseOrderDetail"."ItemCode",

    "PO_PurchaseOrderDetail"."ItemType",

    "PO_PurchaseOrderDetail"."ItemCodeDesc",

    "PO_PurchaseOrderDetail"."UnitOfMeasure",

    "PO_PurchaseOrderDetail"."SalesOrderNo",

    "PO_PurchaseOrderDetail"."ExtensionAmt"

    FROM "PO_PurchaseOrderDetail" "PO_PurchaseOrderDetail"

    WHERE "PO_PurchaseOrderDetail"."SalesOrderNo" = 'SO01542') AS "PO"

    LEFT OUTER JOIN

    (SELECT "SO_SalesOrderDetail"."SalesOrderNo",

    "SO_SalesOrderDetail"."ItemCode",

    "SO_SalesOrderDetail"."ItemType",

    "SO_SalesOrderDetail"."ItemCodeDesc",

    "SO_SalesOrderDetail"."UnitOfMeasure",

    "SO_SalesOrderDetail"."PurchaseOrderNo",

    "SO_SalesOrderDetail"."UnitPrice",

    "SO_SalesOrderDetail"."UnitCost",

    "SO_SalesOrderDetail"."ExtensionAmt"

    FROM "SO_SalesOrderDetail" "SO_SalesOrderDetail"

    WHERE "SO_SalesOrderDetail"."SalesOrderNo"='SO01542') AS "SO"

    ON "PO"."ItemCode" = "SO"."ItemCode"

  • 0 in reply to DPaulTN
    verified answer

    I'm going to go about this a different way.  Building SQL queries are a really trying experience.  I am building sub reports and going to join the tables that way.

Reply Children
No Data