Views Reply Votes
Jay de los Reyes
Jay
 2
"Past Due Net" Stored in what table?
1933
10
0 (0 Votes)
Posted: Oct 17, 2015   
Votes
Raymond Chin Kwie Joe
Raymond
 1022
Replied 
To report past due Purchase orders there are already Browses, Queries. The easiest being the Purchase Order browse (5.8), just select qty open > 0 and due date < TODAY. Other reports are MRP reports (23.7, etc) or other purchase order reports. If you really want to program you should start with the manual "File Relations" and "database definitions".
0 (0 Votes)
Replied: Oct 17, 2015
Votes
Jay de los Reyes
Jay
 2
Replied 
Hi Raymond, thanks for the response, I really appreciate it. I tried to use the qty open > 0 and due date is date < todaysdate. When I check the properties for due date, it gives me pod_det:pod_det_due date. And for Quantity open, it gives me table local_variables and field local-var02 which I cannot find. I am trying to create a report in Cyberquery. Do I needt to create a local variable for this or something?
0 (0 Votes)
Replied: Oct 19, 2015
Votes
Raymond Chin Kwie Joe
Raymond
 1022
Replied 
The local variable is calculated in the browse. It is not a table. I would only download real database fields in you query and calculate the other fields. open qty is pod_qty_ord - pod_qty_rcvd. Take into account that the purchase order must be active (pod_status = "blank".)
+1 (1 Vote)
Replied: Oct 19, 2015
Votes
Jay de los Reyes
Jay
 2
Replied 
Hi Raymond, I tried to do that (pod_qty_ord - pod_qty_rcvd) but it doesn't give me the correct value. Here is my scenario; Item number 45007682 in QAD system have 1400 supplier schedule past due (short shipped). I've been trying to create a Cyberquery report to list all items in our QAD with past due qty. I tried using the pod_cum_qty-sch_pcr_qty but it never gives me the right value because some of the part number, the cum.req value was not configured. And then, I tried your suggestion but it gives me 923,300 past due net instead of 1400. I was thinking if is there anyway I can get the "Past Due Net" that the release is generating? Sorry for the many questions, I'm just trying to learn all of this on my own. :)
0 (0 Votes)
Replied: Oct 19, 2015
Votes
Phill Norrey
Phill
 1124
Replied 
Although Raymond is correct, you cannot use the pod_det table because you are using schedules. This means that you have multiple due dates on one line, so you need to drive this from the schedule tables. Either way, QAD does not store "open" quantities in most tables, it is always a calculated field
+1 (1 Vote)
Replied: Oct 19, 2015
Votes
Jay de los Reyes
Jay
 2
Replied 
Hi phill, Im just a newbie with QAD so I'm a little bit lost. I can identify some tables and fields that I want but I've been trying to figure out what specific tables and fields to use to calculate the past due qty. Any help would be greatly appreciated.
0 (0 Votes)
Replied: Oct 21, 2015
Votes
Phill Norrey
Phill
 1124
Replied 
Originally QAD just had regular purchase orders so the details were always held in the pod_det with one record per part/date so it was very easy to see what was past due. However with schedules they are basically an extension of the pod and you will see that the schd_det record holds details for each schedule line (similar to the pod_det) but you can have many schd_det records to one pod_det. You also need to look at the sch_mstr record to see cumulative amounts shipped etc. If you have access to the progress editor and you should have the source code to the standard reports/enquiries from QAD you might want to look at. 5.5.3.8 to see how they calculate the prior qty. That is basically the number you are looking for.
0 (0 Votes)
Replied: Oct 21, 2015
Votes
Jay de los Reyes
Jay
 2
Replied 
Hi Phill thanks for the response. Actually, I understand what you are saying, I really wanted to see the source code of some of the programs just to understand how it works but unfortunately, I don't have access to Progress editor. What I am doing right now is just trial and error with all those oceans of tables :). Thank You
0 (0 Votes)
Replied: Oct 21, 2015
Votes
Phill Norrey
Phill
 1124
Replied 
I am not a programmer but have done a lot of work in this area. Let me check my notes to see if I have anything that might help you
0 (0 Votes)
Replied: Oct 21, 2015
Votes
Jay de los Reyes
Jay
 2
Replied 
Thanks Phill, I really appreciate it.
0 (0 Votes)
Replied: Oct 22, 2015
Forums
All
Forum Help
Help Using Forums