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
|