1. This is the query to get all the workorders that are to be procesed

select
 base_id
from
 work_order
into
 :sWorkorderList

where
 type = 'W' and
 lot_id = '1' and
 split_id = '0' and
 sub_id = '0' and
 status in ('F','R')
order by
 base_id

2. Here is the main query to get the material cards for all the workorders found in #1 above  (sWorkorderList)

select
 r.part_id,
 r.workorder_base_id,
 r.workorder_sub_id,
 r.operation_seq_no,
 r.piece_no,
 r.calc_qty,
 r.est_material_cost,
 r.issued_qty,
 r.act_material_cost,
 r.status,
 r.user_1,
 p.description,
 q.quote_id,
 w.status,
 l.supply_base_id,
 o.status
from
 requirement r,
 part p,
 quote_order q,
 work_order w,
 demand_supply_link l,
 purchase_order o
where
 r.workorder_type = 'W' and
 r.workorder_base_id in ('" || sWorkorderList || "') and
 r.workorder_lot_id = '1' and
 r.workorder_split_id = '0' and
 r.subord_wo_sub_id is null and
 r.status != 'X' and
 p.id(+) = r.part_id and
 q.cust_order_id(+) = r.workorder_base_id and
 w.type = r.workorder_type and
 w.base_id = r.workorder_base_id and
 w.lot_id = r.workorder_lot_id and
 w.split_id = r.workorder_split_id and
 w.sub_id = '0' and
 l.demand_type(+) = 'RQ' and
 l.demand_base_ID(+) = r.workorder_base_id and
 l.demand_lot_id(+) = r.workorder_lot_id and
 l.demand_split_id(+) = r.workorder_split_id and
 l.demand_sub_id(+) = r.workorder_sub_id and
 l.demand_seq_no(+) = r.operation_seq_no and
 l.demand_no(+) = r.piece_no and
 l.supply_type(+) = 'PO' and
 o.id(+) = l.supply_base_id
into
 :saParts[nIndex],
 :saWorkorders[nIndex],
 :saSubIDs[nIndex],
 :naSeqNos[nIndex],
 :naPieceNos[nIndex],
 :naRequired[nIndex],
 :naRequiredPrice[nIndex],
 :naIssued[nIndex],
 :naIssuedPrice[nIndex],
 :saStatus[nIndex],
 :saUser1[nIndex],
 :saDescriptions[nIndex],
 :saQuotes[nIndex],
 :saWOStatus[nIndex],
 :saPOs[nIndex],
 :saPOStatus[nIndex]
order by 
 1,2,3,4,5

As you can see in the query above, the material cards and related info is loaded into a group of arrays.

 

3. For each item found in #2 above, we create a search key from the Part ID and the Workorder ID and populate another array

Set saSearch[nIndex] = saParts[nIndex] || saWorkorders[nIndex]

The above search key will be used in step #6 below.

*** the following steps are performed for each workorder ****

4. Find the original quote associated with the workorder

select quote_id from quote_order into :sQuote where cust_order_id = :saUserInput[nIndex]

5. Now get all the parts associated with the quote number found in #4 above

select
 part_id,
 calc_qty
from
 requirement
into
 :sQPart,
 :nQQty
where
 workorder_type = 'Q' and
 workorder_base_id = :sQuote and
 workorder_lot_id in (select workorder_lot_id from quote_line where quote_id = '" || sQuote || "' ) and
 subord_wo_sub_id is null and
 status != 'X' and
 part_id is not null
order by
 part_id

6. Cycle through each part and using the key created in Step #3, try to match based on the part number from the quotation concatenated with the workorder number.  If we find a match then store the quoted qty into the quoted array position where the match was found.

Set nFind = VisArrayFindString(saSearch, sQPart || saUserInput[nIndex] )
If nFind > -1
 Set naQuoted[nFind] = naQuoted[nFind] + nQQty

*** the following steps are performed for each workorder ****

7. Look for the most recent quote revision (if any) for the workorder

select
 id
from
 quote
into
 :sLatestQuoteRev
where
 (id like '" || saQuotes[nIndex] || "-%' or id = :saQuotes[nIndex]) and
 status = 'W'
order by
 create_date desc

8. For each part on a workorder that has a quote revision found in step #7 above attempt to find the matching part on the quote revision.  There are 3 ways to try and find a match for the part number:

i). Exact match on lot 1, split 0 sub id, seq no and piece no.

select
 1,
 calc_qty,
 est_material_cost
from
 requirement
where
 workorder_type = 'Q' and
 workorder_base_id = :sLatestQuoteRev and
 workorder_lot_id = '1' and
 workorder_split_id = '0' and
 workorder_sub_id = :saSubIDs[nIndex] and
 operation_seq_no = :naSeqNos[nIndex] and
 piece_no = :naPieceNos[nIndex] and
 part_id = :saParts[nIndex]
into
 :nDummy,
 :nQuoted,
 :nQuotedPrice

ii). Match on sequence number and part id.

select 
 2,
 calc_qty,
 est_material_cost
from
 requirement
where
 workorder_type = 'Q' and
 workorder_base_id = :sLatestQuoteRev and
 workorder_lot_id is not null and
 workorder_split_id = '0' and
 workorder_sub_id is not null and
 operation_seq_no = :naSeqNos[nIndex] and
 piece_no is not null and
 part_id = :saParts[nIndex]
into
 :nDummy,
 :nQuoted,
 :nQuotedPrice
order by
 workorder_lot_id,
 piece_no

iii). Match on part id.

select
 3,
 calc_qty,
 est_material_cost
from
 requirement
where
 workorder_type = 'Q' and
 workorder_base_id = :sLatestQuoteRev and
 workorder_lot_id is not null and
 workorder_split_id = '0' and
 workorder_sub_id is not null and
 operation_seq_no is not null and
 piece_no is not null and
 part_id = :saParts[nIndex]
into
 :nDummy,
 :nQuoted,
 :nQuotedPrice
order by
 workorder_lot_id,
 piece_no

 
Output File Layout (pipe delimited file)

This may not be an issue since we can output directly to Excel, but here is the layout

Headings:

Part Number|Part Description|Workorder Number|W.O. Status|Sub ID|Operation Seq No|Piece Number|Status|Qty Quoted|Quoted Price|Qty Estimated|Estimated Price|Qty Issued|Issued Price|Qty Variance|Price Variance|P.O. Number|P.O. Status|Mat. User 1|Quote|Quote Matching Code

The output line is built as follows:

saParts[nIndex] || '|' ||
saDescriptions[nIndex] || '|' ||
saWorkorders[nIndex] || '|' ||
saWOStatus[nIndex] || '|' ||
saSubIDs[nIndex] || '|' ||
SalNumberToStrX(naSeqNos[nIndex],0) || '|' ||
SalNumberToStrX(naPieceNos[nIndex],0) || '|' ||
saStatus[nIndex] || '|' ||
SalNumberToStrX(nQuoted,2) || '|' ||
SalNumberToStrX(nQuotedPrice,2) || '|' ||
SalNumberToStrX(naRequired[nIndex],2) || '|' ||
SalNumberToStrX(naRequiredPrice[nIndex],2) || '|' ||
SalNumberToStrX(naIssued[nIndex],2) || '|' ||
SalNumberToStrX(naIssuedPrice[nIndex],2) || '|' ||
SalNumberToStrX(naVariance[nIndex],2) || '|' ||
SalNumberToStrX(naVariancePrice[nIndex],2) || '|' ||
saPOs[nIndex] || '|' ||
saPOStatus[nIndex] || '|' ||
saUser1[nIndex] || '|' ||
sLatestQuoteRev || '|' ||
SalNumberToStrX(nDummy,0)

Notes: 

1. Values are output for nQuoted, nQuotedPrice, nDummy only when a quote revision is found for the workorder in step #7 above.  If there is not quote revision, then the values for these 3 fields is set to zero.

2. The "nDummy" value is an indicator to the user which select method (i, ii, iii) in step #8 above was successful in finding a match.

3. For those parts (requirements) that DO NOT have a part description (in other words blank part numbers), we go to the REQUIREMENT_BINARY table to get the spec data (BITS).  Prior to output any carriage returns and | characters are stripped off.  The | characters were stripped off because the output file format was pipe delimited which precluded us from allowing that character anywhere in the data.  This may not be an issue when we do this in Crystal or BO.

Here is the source code that does this just to give you an idea what it's doing.

If saDescriptions[nIndex] = STRING_Null
 If VMGetRequirementBinary('W', saWorkorders[nIndex], '1', '0', saSubIDs[nIndex],naSeqNos[nIndex],naPieceNos[nIndex], 'D', lsBITS)
  Set saDescriptions[nIndex] = __QOMailTextStripCR( SalStrLeftX(lsBITS,254) )
  Set lsBITS = saDescriptions[nIndex]
  Set saDescriptions[nIndex] = TextStripPipeChar( SalStrLeftX(lsBITS,254) )