I have a main report that contains work orders. A subreport has been created to read the purchase orders that are associated with each work order.
The query for the subreport contains a decode statement as follows:
SELECT DISTINCT SA_WORK_ORDER_MATERIAL.WORK_ORDER_NO, SA_WORK_ORDER_MATERIAL.PO_NO, SA_PURCHASE_ORDER.PO_STATUS,
decode(SA_PURCHASE_ORDER.PO_STATUS,
'INVOICED', 'Invoiced Ready to Close',
'RECEIVED', 'WO Not Ready to Close',
'ISSUED', 'WO Not Ready to Close',
'WO Ready to Close') WOStatusResult
FROM SA_WORK_ORDER_MATERIAL INNER JOIN SA_PURCHASE_ORDER ON SA_WORK_ORDER_MATERIAL.PO_NO = SA_PURCHASE_ORDER.PO_NO
WHERE SA_PURCHASE_ORDER.PO_STATUS <> 'CANCELED'
This query returns what I am looking for and I setup a shared variable to link it to the main report.
Whileprintingrecords;
Shared StringVar InvoiceClose;
InvoiceClose := {Command.WOSTATUSRESULT};
In the main report I then have the shared variable:
WhilePrintingrecords;
Shared StringVar InvoiceClose;
InvoiceClose
The problem is that work orders can have multiple purchase orders, each having a different status (received, issued, invoiced, closed) and I don't always get the right value in the shared variable on the main report. It seems like it sometimes randomly picks a status, which I assume is because of the WhilePrintingRecords function. I can't use WhileReadingRecords with a shared variable. I would say 90% of the time it's right. I don't want people to close work orders if there is a purchase order that hasn't been completed.
The purchase order status cannot be written into the main report query because of the complicated structure and association of tables in the database (according to the programmer who wrote the query)
Suggestions?
IP Logged
