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.
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.
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