Hi all,
Back in the FoxPro days of M2M, when the table was still named just INMAST, there were several fields present on the item master which are now calculated such as on-hand quantities. To bridge the gap, when they removed the fields the developers did us a solid and renamed the item master INMASTX then created a view as a stand-in for the original INMAST. Inside that view, they didn't just calculate the since removed fields, but they created SQL functions to do the calculations.
Turns out, that those functions have a bunch of useful parameters that are generally unused by the application.
For example, getitemmtdissues has 3 parameters; facility, part number, revision, and date. The one that is the most interesting to me is date. This allows for a host of trend reporting without concern for the accuracy of the data given that you are using a function created by the application maker.
Here is a demonstration of something i've done with these functions:
It's a query that shows your excess and overages of the previous 3 years, have fun !!!
|
SELECT *
,on_hand_qty * [standard cost] AS on_hand_cost ,inspection_qty * [standard cost] AS inspection_cost ,nonnet_qty * [standard cost] AS nonnet_cost ,wip_qty * [standard cost] AS wip_cost ,onorder_qty * [standard cost] AS onorder_cost ,committed_qty * [standard cost] AS committed_cost ,current_mtd_issues_qty * [standard cost] AS current_mtd_issues_cost ,previous_mtd_issues_qty * [standard cost] AS previous_mtd_issues_cost ,second_previous_mtd_issues_qty * [standard cost] AS second_previous_mtd_issues_cost ,current_ytd_issues_qty * [standard cost] AS current_ytd_issues_cost ,previous_ytd_issues_qty * [standard cost] AS previous_ytd_issues_cost ,second_previous_ytd_issues_qty * [standard cost] AS second_previous_ytd_issues_cost ,current_mtd_receipts_qty * [standard cost] AS current_mtd_receipts_cost ,previous_mtd_receipts_qty * [standard cost] AS previous_mtd_receipts_cost ,second_previous_mtd_receipts_qty * [standard cost] AS second_previous_mtd_receipts_cost ,current_ytd_receipts_qty * [standard cost] AS current_ytd_receipts_cost ,previous_ytd_receipts_qty * [standard cost] AS previous_ytd_receipts_cost ,second_previous_ytd_receipts_qty * [standard cost] AS second_previous_ytd_receipts_cost ,intransit_qty * [standard cost] AS intransit_cost ,current_ytd_excess_qty * [standard cost] AS current_ytd_excess_cost ,previous_ytd_excess_qty * [standard cost] AS previous_ytd_excess_cost ,second_previous_ytd_excess_qty * [standard cost] AS second_previous_ytd_excess_cost FROM ( SELECT fpartno AS [part number] ,f2dispmcst AS [vendor purch pri] ,f2labcost AS [cost ref labor cost] ,f2matlcost AS [cost ref matl cost] ,f2ovhdcost AS [cost ref ovrhd cost] ,f2totcost AS [cost ref total cost] ,fabccode AS [abc code] ,favgcost AS [unit average cost] ,fbin1 AS bin ,fbuyer AS planner ,fccadfile1 AS [cad images] ,fccadfile2 AS fccadfile2 ,fccadfile3 AS fccadfile3 ,fcdncfile AS [numerical control file] ,fcjrdict AS jurisdiction ,fclotext AS [extent of control] ,fcnts AS [cycle counts / yr] ,fcomment AS comments ,fcplnclass AS [planning classification] ,fcratedisc AS [rate / discrete] ,fcstperinv AS [conversion factor] ,fcusrchr1 AS [character 1] ,fcusrchr2 AS [character 2] ,fcusrchr3 AS [character 3] ,fdescript AS description ,fdispmcost AS [vendor purchase pr] ,fdrawno AS [drawing number] ,fdrawsize AS [drawing size] ,fdusrdate1 AS [date 1] ,fgroup AS [group code] ,fidims AS dimensions ,fipcsonhd AS [pieces on hand] ,flabcost AS [std labor cost] ,flaplpart AS [advanced planning part] ,flastcost AS [last actual cost] ,flasteoc AS [last eco no] ,flchgpnd AS [chg pndg] ,flconstrnt AS [material constraint] ,flct AS [last cycle count] ,fleadtime AS [lead time] ,flexpreqd AS [expiration date reqd] ,flfanpart AS [forecast part] ,flistaxabl AS taxable ,flocate1 AS location ,fmatlcost AS [std material cost] ,fmeasure AS [unit of measure] ,fmeasure2 AS [vendor u / m] ,fnfanaglvl AS [revision history level] ,fnlndtomfg AS [loaned to manufacturing] ,fnponhand AS [partials on hand] ,fnusrcur1 AS [currency 1] ,fnusrqty1 AS [quantity 1] ,fnweight AS weight ,fovhdcost AS [std overhead cost] ,fprice AS [selling price] ,fprodcl AS [product class] ,freordqty AS [re - order quantity] ,frev AS [part revision] ,frevdt AS [revision date] ,fsafety AS [safety stock] ,fsource AS source ,fstdcost AS [standard cost] ,fstdmemo AS memo ,fyield AS [yield factor] ,itcunit AS [inter - fac transport cost] ,sfac AS facility ,flasteoc AS [last eco number] ,fmeasure AS [u / m] ,frev AS rev ,fmeasure AS [component unit of measure] ,last_issues_on ,last_reciept_on ,on_hand_qty ,inspection_qty ,nonnet_qty ,wip_qty ,onorder_qty ,committed_qty ,current_mtd_issues_qty ,previous_mtd_issues_qty ,second_previous_mtd_issues_qty ,current_ytd_issues_qty ,previous_ytd_issues_qty ,second_previous_ytd_issues_qty ,current_mtd_receipts_qty ,previous_mtd_receipts_qty ,second_previous_mtd_receipts_qty ,current_ytd_receipts_qty ,previous_ytd_receipts_qty ,second_previous_ytd_receipts_qty ,intransit_qty ,current_ytd_receipts_qty - current_ytd_issues_qty AS current_ytd_excess_qty ,previous_ytd_receipts_qty - previous_ytd_issues_qty AS previous_ytd_excess_qty ,second_previous_ytd_receipts_qty - second_previous_ytd_issues_qty AS second_previous_ytd_excess_qty FROM ( SELECT * ,m2mdata01.dbo.getitemonhandquantity(fac, fpartno, frev) AS on_hand_qty ,m2mdata01.dbo.getiteminspectionquantity(fac, fpartno, frev) AS inspection_qty ,m2mdata01.dbo.getitemnonnetquantity(fac, fpartno, frev) AS nonnet_qty ,m2mdata01.dbo.getiteminprocessquantity(fac, fpartno, frev) AS wip_qty ,m2mdata01.dbo.getitemonorderquantity(fac, fpartno, frev) AS onorder_qty ,m2mdata01.dbo.getitemcommittedquantity(fac, fpartno, frev) AS committed_qty ,m2mdata01.dbo.getitemlastissuedate(fac, fpartno, frev) AS last_issues_on ,m2mdata01.dbo.getitemlastreceiptdate(fac, fpartno, frev) AS last_reciept_on ,m2mdata01.dbo.getitemmtdissues(fac, fpartno, frev, GETDATE()) AS current_mtd_issues_qty ,m2mdata01.dbo.getitemmtdissues(fac, fpartno, frev, DATEADD(mm, - 1, GETDATE())) AS previous_mtd_issues_qty ,m2mdata01.dbo.getitemmtdissues(fac, fpartno, frev, DATEADD(mm, - 2, GETDATE())) AS second_previous_mtd_issues_qty ,m2mdata01.dbo.getitemytdissues(fac, fpartno, frev, GETDATE()) AS current_ytd_issues_qty ,m2mdata01.dbo.getitemytdissues(fac, fpartno, frev, DATEADD(yyyy, - 1, GETDATE())) AS previous_ytd_issues_qty ,m2mdata01.dbo.getitemytdissues(fac, fpartno, frev, DATEADD(yyyy, - 2, GETDATE())) AS second_previous_ytd_issues_qty ,m2mdata01.dbo.getitemmtdreceipts(fac, fpartno, frev, GETDATE()) AS current_mtd_receipts_qty ,m2mdata01.dbo.getitemmtdreceipts(fac, fpartno, frev, DATEADD(mm, - 1, GETDATE())) AS previous_mtd_receipts_qty ,m2mdata01.dbo.getitemmtdreceipts(fac, fpartno, frev, DATEADD(mm, - 2, GETDATE())) AS second_previous_mtd_receipts_qty ,m2mdata01.dbo.getitemytdreceipts(fac, fpartno, frev, GETDATE()) AS current_ytd_receipts_qty ,m2mdata01.dbo.getitemytdreceipts(fac, fpartno, frev, DATEADD(yyyy, - 1, GETDATE())) AS previous_ytd_receipts_qty ,m2mdata01.dbo.getitemytdreceipts(fac, fpartno, frev, DATEADD(yyyy, - 2, GETDATE())) AS second_previous_ytd_receipts_qty ,m2mdata01.dbo.getitemintransitquantity(fac, fpartno, frev) AS intransit_qty FROM m2mdata01.dbo.inmastx ) AS a ) AS a |
Facebook Social Comments