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!!
Nope plus there is no recording of changes in PO as there are with SO and SO history.
Nope plus there is no recording of changes in PO as there are with SO and SO history.
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"
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
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"
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.
*Community Hub is the new name for Sage City