The Jargon Bin

Tags

Home

Details
Category: Blog
Hits: 3172 
IF OBJECT_ID(N 'tempdb..##err') IS NOT NULL BEGIN 
DROP 
  TABLE ##err
  END CREATE TABLE ##err (
  [LogDate] DATETIME2, 
  [ProcessInfo] VARCHAR(255), 
  [Text] VARCHAR(max)
) INSERT INTO ##err
EXEC sp_readerrorlog 
SELECT 
  *, 
  CASE WHEN ISNUMERIC(
    LTRIM(
      RTRIM(
        SUBSTRING(
          TEXT, 
          CHARINDEX('approximately ', TEXT, 0) + 14, 
          CHARINDEX(
            ' seconds', 
            SUBSTRING(
              TEXT, 
              CHARINDEX('approximately ', TEXT, 0) + 14, 
              9999
            ), 
            0
          )
        )
      )
    )
  ) = 1 THEN CAST(
    LTRIM(
      RTRIM(
        SUBSTRING(
          TEXT, 
          CHARINDEX('approximately ', TEXT, 0) + 14, 
          CHARINDEX(
            ' seconds', 
            SUBSTRING(
              TEXT, 
              CHARINDEX('approximately ', TEXT, 0) + 14, 
              9999
            ), 
            0
          )
        )
      )
    ) AS INT
  ) ELSE NULL END AS TimeLeftinSeconds, 
  CASE WHEN ISNUMERIC(
    LTRIM(
      RTRIM(
        SUBSTRING(
          TEXT, 
          CHARINDEX('approximately ', TEXT, 0) + 14, 
          CHARINDEX(
            ' seconds', 
            SUBSTRING(
              TEXT, 
              CHARINDEX('approximately ', TEXT, 0) + 14, 
              9999
            ), 
            0
          )
        )
      )
    )
  ) = 1 THEN CAST(
    LTRIM(
      RTRIM(
        SUBSTRING(
          TEXT, 
          CHARINDEX('approximately ', TEXT, 0) + 14, 
          CHARINDEX(
            ' seconds', 
            SUBSTRING(
              TEXT, 
              CHARINDEX('approximately ', TEXT, 0) + 14, 
              9999
            ), 
            0
          )
        )
      )
    ) AS FLOAT
  ) / 60 ELSE NULL END AS TimeLeftinMinutes 
FROM 
  ##err
ORDER BY 
  Logdate DESC IF OBJECT_ID(N 'tempdb..##err') IS NOT NULL BEGIN 
DROP 
  TABLE ##err
  END
0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 5384

Sometimes, you just can't reset your password.
Sometimes, your company doesn't care why you can't.

Desperate times call for desperate measures;
The following posh script can be run on any domain computer by a domain admin and will reset the password expiration for a given user (the logged-in user by default).
This will simulate the password being reset.
I encapsulated the code in Invoke-Command statements so it will run against a DC, then it forces replication after it's done.

cls
$ls = ($env:LOGONSERVER).TrimStart("\\").ToString() # Get Logon Server
$forestpdc = Invoke-Command -ComputerName $ls -ScriptBlock {Get-ADForest | Select-Object -ExpandProperty RootDomain | Get-ADDomain | Select-Object -Property PDCEmulator}
$localdomainpdc = Invoke-Command -ComputerName $ls -ScriptBlock {Get-ADDomain | Select-Object -Property PDCEmulator}
$sam = $env:UserName.ToString() #change this value to reset another user
$sam2 = Read-Host "User name to reset: [default: $sam]"
if($sam2-eq ""){$sam2=$sam}
$cmd = "Get-ADUser -Filter {SamAccountName -eq ""$sam""}"
$user = Invoke-Command -ComputerName $localdomainpdc.PDCEmulator -ScriptBlock {Get-ADUser -Filter {SamAccountName -eq $Using:sam2}}
Invoke-Command -ComputerName $localdomainpdc.PDCEmulator -ScriptBlock {$uObj = [ADSI]"LDAP://$Using:user"
$uObj.put("pwdLastSet", 0)
$uObj.SetInfo()
$uObj.put("pwdLastSet", -1)
$uObj.SetInfo()}

write-host "Syncing the local domain..."
Invoke-Command -ComputerName $localdomainpdc.PDCEmulator -ScriptBlock {(Get-ADDomainController -Filter *).Name | Foreach-Object {repadmin /syncall $_ (Get-ADDomain).DistinguishedName /e /A | Out-Null}; Start-Sleep 10; Get-ADReplicationPartnerMetadata -Target "$env:userdnsdomain" -Scope Domain | Select-Object Server, LastReplicationSuccess}
if($localdomainpdc.PDCEmulator-ne $forestpdc.PDCEmulator){
write-host "Syncing the forest..."
Invoke-Command -ComputerName $forestpdc.PDCEmulator -ScriptBlock {(Get-ADDomainController -Filter *).Name | Foreach-Object {repadmin /syncall $_ (Get-ADDomain).DistinguishedName /e /A | Out-Null}; Start-Sleep 10; Get-ADReplicationPartnerMetadata -Target "$env:userdnsdomain" -Scope Domain | Select-Object Server, LastReplicationSuccess}
}
$pls = Invoke-Command -ComputerName $localdomainpdc.PDCEmulator -ScriptBlock {(Get-ADUser -Filter {SamAccountName -eq $Using:sam2} -properties PasswordLastSet).PasswordLastSet}
write-host "Sync done..."
write-host "Password last set: $pls"
0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 5032

Despite my initial requests to my management at the time to look at a wonderful ticketing system called IssueTrack which I had deployed at my previous employment, I was overruled and now my company uses a ticketing solution that is installed as an Azure application called Samanage (which was just purchased by SolarWinds). It is an adequate solution, despite it being a little weak in the area of workflow automation and form customization.

One thing that has bothered me since the initial deployment is the multi-step process for assigning newly created users from AD on-prem to the Azure application because we wanted to limit the authorized users to only real "person" accounts. The only other alternative was to all users to sync from Azure AD, which would have created a bit of a mess.

To deal with this, I created a PowerShell script that fetches the members of a group, checks that list against a list of assigned users for the application, then assigns any new users to that application.

Then I created a task scheduler task that runs once every half hour that runs the script to keep everything in sync.

The code for the script, as well as an application that encrypts credentials,  are attached. Keep in mind, when encrypting credentials, you must run the "BuildCreds.exe" application on the machine on which you plan to use them. The ConvertFrom-SecureString command is machine specific.

<<Download Here>>

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4639

Hey All,

Wrote a quick script for Made2Manage to check both which users have more than one user license occupied (total_session_count) and also if a user has a session that is chewing up a bunch of resources (total_screen_count) including what their machine name is (client_name) and which server they are connected to for those of you with more than one application server (server_name)


WITH base 
     AS (SELECT dbo.Fn_decrypt(fcuname) 
                user_name, 
                fcsessionid, 
                fcmodid, 
                fccompno, 
                clienttype, 
                fccompno 
                   company_number, 
                LEFT(compname, Charindex('#', compname) - 1) 
                   server_name, 
                Substring(compname, Charindex('#', compname) + 1, Len(compname)) 
                   client_name 
         FROM   m2msystem.dbo.utscreen 
                LEFT JOIN utulog 
                       ON utscreen.fcsessionid = utulog.fcsessid), 
     unique_sessions 
     AS (SELECT DISTINCT user_name, 
                         fcsessionid 
         FROM   base 
         WHERE  clienttype NOT LIKE '%BCP%'), 
     session_count 
     AS (SELECT user_name, 
                Count(user_name) total_session_count 
         FROM   unique_sessions 
         GROUP  BY user_name), 
     unique_screens 
     AS (SELECT user_name, 
                Count(user_name) total_screen_count 
         FROM   base 
         WHERE  clienttype NOT LIKE '%BCP%' 
         GROUP  BY user_name, 
                   fcsessionid) 
SELECT DISTINCT sc.*, 
                b.company_number, 
                us.total_screen_count, 
                b.server_name, 
                b.client_name 
FROM   session_count sc 
       INNER JOIN unique_screens us 
               ON sc.user_name = us.user_name 
       INNER JOIN base b 
               ON sc.user_name = b.user_name 
ORDER  BY total_session_count DESC, 
          total_screen_count DESC 
/* 
*/ 
 
 
 
0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4473

Hey All,

I have a stored procedure scheduled to run a job every ten minutes that I was doing some testing on.

 

It usually takes about a minute to run, so when I'm testing it out, I like to run it on the 5th minute of every ten minute period to be sure that it doesn't interfere with the jobs run.

To make sure this happens every time, I created a quick snippet to find the current offset needed in a WAITFOR statement so it'll run the next time the time ends in a 5.
This doesn't account for seconds intentionally because I want there to be no delay if the GETDATE() statement returns a value in the 5th minute.

DECLARE @wait    AS INT, 
        @current AS INT, 
        @waitfor AS NVARCHAR(10) 

SELECT @current = Cast(RIGHT(Cast(Datepart(mi, Getdate()) AS VARCHAR(2)), 1) AS 
                       INT) 

SELECT @wait = CASE 
                 WHEN @current = 5 THEN 0 
                 WHEN @current < 5 THEN 5 - @current 
                 ELSE 15 - @current 
               END 

SELECT @waitfor = '00:' 
                  + RIGHT('00' + Cast(@wait AS VARCHAR(2)), 2) 
                  + ':00' 

SELECT @waitfor 

WAITFOR delay @waitfor 
-- This could be flattened to  look like this 
/* 
SET @waitfor = '00:' + right('00' + cast(( 
        CASE  
          WHEN (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) = 5 
            THEN 0 
          WHEN (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) < 5 
            THEN 5 - (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) 
          ELSE 15 - (cast(right(cast(datepart(mi, getdate()) AS VARCHAR(2)), 1) AS INT)) 
          END 
        ) AS VARCHAR(2)), 2) + ':00' 
WAITFOR DELAY @waitfor 
*/ 
 
 
 
0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4358

Hi everyone,

This is just a quick script for a scalar function to return the first day of the current week.
With a little modification you could add a parameter so you could pass it a date and have it return the first day of that week.
It's also easy to adjust the numeric value for the days in the event your week starts on Sunday.

Have fun!

CREATE FUNCTION F_wtd () 
returns DATE 
AS 
  BEGIN 
      DECLARE @res AS DATE 

      SELECT TOP 1 @res = Cast(Dateadd(dd, ( CASE Datename(dw, Getdate()) 
                                               WHEN 'Monday' THEN 1 
                                               WHEN 'Tuesday' THEN 2 
                                               WHEN 'Wednesday' THEN 3 
                                               WHEN 'Thursday' THEN 4 
                                               WHEN 'Friday' THEN 5 
                                               WHEN 'Saturday' THEN 6 
                                               WHEN 'Sunday' THEN 7 
                                             END * -1 ), Getdate()) AS DATE) 

      RETURN @res 
  END 

 

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 5343

So, in addition to my life as an IT guy, I also have a strong interest in component level electronics.
My parents had to replace many household electronic items having been victims of my curiosity and a screwdriver.

Today on the healing bench is a very nice piece of kit from Logitec;

The Logitech Smartdock for Skype for Rooms v2.

What the heck is this thing and why did you take it apart.

Good Question. A little while back, Microsoft finally decided to patch a hole in its Skype for Business offering when it came to organizations like the one I work for, who needed a meeting space S4B enabled. Up to this point, there were some previous generation options, made for Lync, which worked, but were old enough to be out of hardware support and only available secondhand, or a rigged together solution with a small PC like a NUC strapped to the back of a large wall mounted TV. I have two issues with the latter; first is that the S4B desktop app doesn't work well as a room system and required some type of keyboard mouse combo left on a table, which someone will inevitably log out, which creates support weight for the IT staff. The second is that if someone needs to bring their laptop in to share, they'd have to start a meeting on the room PC, then join it on their laptop and remember to join without audio, which never happened, and you'd hear a high pitch squealing of feedback from anywhere in the office, which is like the IT siren.
This thing fixes both of those things.
Basically, it's a dock for a Surface Pro 4 (or a Pro 3 if you have a pair of side cutters) with a built-in HDMI capture card, which is then connected to a wall mount TV.
Once the software is installed, the tablet becomes a "Room Control Panel" and the TV is the main output.
It allows users to start scheduled meetings, schedule a meeting, or display content, local or remote, with the touch of a screen.
It makes using the conference room for users so simple, I can't imagine anything they could do to make it any easier. Even displaying content is as easy as plugging in an HDMI cord to your laptop and clicking the "Present" button.
If you have conference rooms and plan to use Skype for Business to connect them, this is the only way to fly.

Cool story Josh, why did you take it apart if it's so awesome?

Two reasons;

First, I have seen many third-party devices made for the Surface Pro. For all of the products I've seen, I haven't ever seen a third party device that uses the Microsoft proprietary Surface dock connection. There are many knocks of mag chargers, but none that use the data bus on the connector. In fact, the only two devices that I can think of that use the data connection are the surface pro 3 and surface pro 4 docks. This uses that data connection and is also third-party. I wanted to know if it was possible that this thing was just a Microsoft Surface dock in a fancy plastic case.

Second, and the more interesting one, is that this dock isn't really what makes this system awesome, it's the software, which is made by Microsoft and is freely available, but requires a Skype for Rooms v2 compatible device to work. The "requires a Skype for Rooms v2 compatible device to work" part is not a real limitation, it actually works fine without it, it just won't complete setup unless it's plugged into one during install.

Stay tuned for more regarding how to get the software working without the dock!!!

Let me know if there are any images of something on the doc not pictured here (This is just the highlight reel!)

 

 

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 5053

 

If you're like me, you often run into a situation where Microsoft has been kind enough to solve an issue or add functionality to an obvious gap in a previous version (like the function ISNUMERIC() ), but you are unable to upgrade to the newer SQL version for one of a never-ending list of reasons ("the application only supports SQL 2000", "there's no money in the budget for that", "why fix what isn't broken", "the moon is full", "I ate bad sushi yesterday and i'm just not 'feelin it' today").

At any rate, I needed to use the STRING_SPLIT function available in 2016 forward on a 2008 R2 box, so I wrote code that emulated that table-valued function and can be used interchangeably.

I also added a piece that allows for escaped delimiters using \delimiter\, so you may find utility for this even in a SQL 2016 instance.

Running this script installs the function in MASTER and then creates a synonym in each database as well as MODEL, so any new databases will have it as well.

Enjoy!

USE master 

go 

IF Object_id('string_split') IS NULL 
  EXEC ( 'CREATE FUNCTION String_split () returns @result TABLE (   value VARCHAR(max))   BEGIN       DECLARE @TEMP AS VARCHAR(4)       RETURN   END' ) 

go 

ALTER FUNCTION String_split (@string    VARCHAR(max), 
                             @separator VARCHAR(10)) 
returns @result TABLE ( 
  value VARCHAR(max)) 
AS 
  BEGIN 
      DECLARE @pos    INT = 0, 
              @res    VARCHAR(100), 
              @escape VARCHAR(30), 
              @escomb VARCHAR(max) 

      SET @escape = '\' + @separator + '\' 
      SET @escomb = Replace(@string, @escape, '<escapeddelimiterreplacement>') 

      WHILE Len(@escomb) > 0 
        BEGIN 
            SET @pos = Charindex(@separator, @escomb + @separator, 0) 

            SELECT @res = LEFT(@escomb, @pos), 
                   @escomb = Substring(@escomb, @pos + 1, 100) 

            INSERT @result 
            VALUES (Replace(@res, @separator, '')) 
        END 

      UPDATE @result 
      SET    value = Replace(value, '<escapeddelimiterreplacement>', @escape) 

      RETURN 
  END 

go 

-- make this function available in all databases 
DECLARE @dsql AS NVARCHAR(4000) = 'DECLARE @cnt as int ' 

SELECT @dsql = @dsql + 'USE ' + NAME + ' select @cnt = count(*) from sys.objects where name = ''string_split'' IF @cnt = 0 BEGIN CREATE SYNONYM dbo.string_split FOR master.dbo.string_split END ' 
FROM   sys.databases 
WHERE  NAME NOT IN ( 'master', 'tempdb', 'msdb', 'Resource' ) -- model is omitted to make sure it appears in any new databases created 

EXEC (@dsql) 

 

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4675

This is a public service announcement:

It is critical that when you extract the month from a date with a two digit conversion (between October and December) and put it into a variable, you must define the length of the variable, or it will truncate the second digit.

If you do not, it will return a 1 and not the correct digit value.

Running this code:

DECLARE @ldBegDate AS DATE = '10-09-2018' 
DECLARE @inline_month_no_len AS VARCHAR = Ltrim(Rtrim(Str(Month(@ldBegDate)))) 
DECLARE @inline_month_w_len AS VARCHAR(2) = Ltrim(Rtrim(Str(Month(@ldBegDate)))) 
DECLARE @inline_month_no_trim AS VARCHAR = Cast(Month(@ldBegDate) AS VARCHAR) 
DECLARE @inline_month_no_trim_w_len AS VARCHAR(2) = Cast(Month(@ldBegDate) AS VARCHAR) 
DECLARE @the_right_way AS VARCHAR(2) = Cast(Datepart(mm, @ldBegDate) AS VARCHAR) 

PRINT '@ldBegDate = ' + Cast(@ldBegDate AS VARCHAR(max)) 
PRINT '@inline_month_no_len = ' + @inline_month_no_len 
PRINT '@inline_month_w_len = '  + @inline_month_w_len 
PRINT '@inline_month_no_trim = ' + @inline_month_no_trim 
PRINT '@inline_month_no_trim_w_len = ' + @inline_month_no_trim_w_len 
PRINT '@the_right_way = ' + @the_right_way 
PRINT 'No Variable = ' + Ltrim(Rtrim(Str(Month(@ldBegDate)))) 

 

Produces this output.

@ldBegDate = 2018-10-09
@inline_month_no_len = 1
@inline_month_w_len = 10
@inline_month_no_trim = 1
@inline_month_no_trim_w_len = 10
@the_right_way = 10
No Variable = 10

You've been warned.

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4382

I've got a secret. Don't tell anyone.

I still use the command line for everything; I only use PowerShell when I can't figure out how to do something with a batch script.

Seriously, don't tell anyone.

Keep it real; I'm not the only one.

One of the things that has always bothered me about the MOVE command is that when moving a whole directory, if it encounters a problem with one of the files, the whole thing fails.

I wrote a little application to deal with that. I'm using it to move my SQL transaction logs and backups from one location to another.

Basically, it gets a list of files from a path (including UNC) and moves them one at a time and just keeps going if it hits a file it has trouble with.

Just download it!

JustMoveIt.exe (Download)

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 4677

If you’ve been in IT long enough, chances are that you’ve had to deal with disabling an account or changing a password without knowing if it's running a service somewhere.

I’ve always struggled with this, and have employed a few different methods:

  1. Buy lunch for a few of your admin friends and have them help you check all of the critical servers and services before making the change.
  2. Turn it off and pray see what happens

Both of these options stink.

I’ve spent quite a bit of time on the Google and Bing machines looking for someone who had a simpler and cleaner solution to this problem to no avail.

Given that I've caused dealt with this at multiple jobs, it's surprising that no one else admitted guilt has posted a solution to this problem.


So here it is (source and an executable version below)….

 

cls ##clear the screen
Write-Host "Starting Service Credential Check..."
$GetDesktopPath = [Environment]::GetFolderPath("Desktop") ## get the path to save the files
Write-Host "Saving files to "  $GetDesktopPath  "..."

$name = Read-Host -prompt 'Run for domain (blank = current domain)...'
$myDomain = if ($name -eq ""){((Get-WmiObject Win32_ComputerSystem).Domain)} elseif ($name -eq $null){((Get-WmiObject Win32_ComputerSystem).Domain)} else {$name}
$ldapaddress = "LDAP://" + $myDomain

$ADSearch = New-Object System.DirectoryServices.DirectorySearcher
$ADSearch.SearchRoot = $ldapaddress.ToString()
$ADSearch.SearchScope = "Subtree" 
$ADSearch.PageSize = 8000 

$ADSearch.Filter = "(objectCategory=Computer)"
$colResults = $ADSearch.FindAll()
$computers = $colResults.GetDirectoryEntry()
$servers = $computers | Where-Object {$_.operatingSystem -Like "*Server*"} #| select -last 50
$servers2 = $servers.dNSHostName | where { Test-Connection -ComputerName $_ -Count 1 -Quiet }

$services = gwmi win32_service -computer $servers2 | select SystemName,DisplayName,StartName,State,StartMode | Sort-Object StartName,SystemName,DisplayName

$services | ConvertTo-HTML -Property SystemName,DisplayName,StartName,State,StartMode,InstallDate > ([Environment]::GetFolderPath("Desktop")+"\all_services.html")

$services | Where-Object {$_.StartName -NotLike "*NT Authority*" -and $_.StartName -NotLike "*NT Service*" -and $_.StartName -NotLike "*locals*"} | ConvertTo-HTML -Property SystemName,DisplayName,StartName,State,StartMode,InstallDate > ([Environment]::GetFolderPath("Desktop")+"\nonsystem_services.xls")


Write-Host "The files have been saved..."
Write-Host "Exiting..."

 

Source Code and Executable http://jargonbin.com/downloads/ScanServiceCreds.zip

 

0
0
0
s2sdefault


powered by social2s

Details
Category: Blog
Hits: 5388

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
0
0
0
s2sdefault


powered by social2s