How to find the line number of an item on a Microsoft Dynamics GP document - Part 1
One of the questions that most often come up in newsgroups and at client sites is, "how can I find the line number of an item on a *document*?", you can replace the word *document* for anything from a sales order, sales invoice all the way to a purchase order, purchase receipt, or invoicing invoice -- and pretty much any other thing you can think of.
The bottom line is, while Dynamics GP creates a line sequence number for every item entered in a scrolling window -- click here to see my past article on line sequence numbers and scrolling windows -- it is not very good at aiding users and/or developers in tracking the true ordinal value (1, 2, 3,.., n) of an item within the set of items on a document. So, I decided to give you a push by creating several T-SQL scripts that will help you identify these ordinal values. You can then take these same scripts and convert it into a SQL Server UDFs or stored procedures and call them from VBA or Dexterity if needed. I will also provide an example on these two techniques in Part 2 of this series.
So here are the scripts:
SOPLineItemSequenceWorkHistory.SQL
This is just a sample of the results produced by the query -- note the row number column for each partition by SOP Type and SOP Number:
POPPOLineItemSequenceWorkHistory.SQL
This is just a sample of the results produced by the query -- note the row number column for each partition by PO Type and PO Number:
POPRCLineItemSequenceWorkHistory.SQL
This is just a sample of the results produced by the query -- note the row number column for each partition by Receipt Number:
IVCLineItemWorkHistory.SQL
This is just a sample of the results produced by the query -- note the row number column for each partition by IVC Type and IVC Number:
NOTE: I am a big fan of SQL Server Common Table Expressions (CTE) and use those as often as I can to simplify my queries and avoid the use of subqueries. In addition, they tend to produce better query execution plans which translate into performance gains.
Please stay tuned! Part 2 of this series will show you how to take advantage of these queries from Dexterity and VBA.
Related Articles
Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers - On this blog, click here.
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
The bottom line is, while Dynamics GP creates a line sequence number for every item entered in a scrolling window -- click here to see my past article on line sequence numbers and scrolling windows -- it is not very good at aiding users and/or developers in tracking the true ordinal value (1, 2, 3,.., n) of an item within the set of items on a document. So, I decided to give you a push by creating several T-SQL scripts that will help you identify these ordinal values. You can then take these same scripts and convert it into a SQL Server UDFs or stored procedures and call them from VBA or Dexterity if needed. I will also provide an example on these two techniques in Part 2 of this series.
So here are the scripts:
SOPLineItemSequenceWorkHistory.SQL
-- Created by Mariano Gomez, MVP
WITH SOPCTE (SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ) AS (
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP10200
UNION ALL
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP30300
)
SELECT SOPTYPE
, SOPNUMBE
, ITEMNMBR
, LNITMSEQ
, ROW_NUMBER() OVER(PARTITION BY SOPNUMBE, SOPTYPE ORDER BY SOPNUMBE, SOPTYPE, LNITMSEQ ASC) AS ROWNUMBER
FROM SOPCTE;
This is just a sample of the results produced by the query -- note the row number column for each partition by SOP Type and SOP Number:
SOPTYPE SOPNUMBE ITEMNMBR LNITMSEQ ROWNUMBER
------- --------------------- ------------------------------- ----------- --------------------
3 INVPS1004 PHON-ATT-53BL 32768 1
3 INVPS1004 FAXX-HLP-5433 49152 2
3 INVPS1005 HDWR-PNL-0001 32768 1
3 INVPS1006 ANSW-PAN-1450 16384 1
3 INVPS1007 TRAN-STR-N394 16384 1
3 INVS3000 3-C2924A 16384 1
3 INVS3000 3-D2657A 32768 2
3 INVS3000 3-D2659A 49152 3
3 INVS3000 5-STDLABOR 65536 4
3 INVS3000 5-STDLABOR 81920 5
3 INVS3000 5-TVLLABOR 98304 6
3 INVS3000 5-OVTLABOR 114688 7
3 INVS3000 5-FEE 131072 8
3 INVS3000 5-FEE 147456 9
3 INVS3001 3-C2924A 16384 1
3 INVS3001 3-D2657A 32768 2
3 INVS3001 3-D2659A 49152 3
3 INVS3001 5-STDLABOR 65536 4
3 INVS3001 5-STDLABOR 81920 5
3 INVS3001 5-TVLLABOR 98304 6
3 INVS3001 5-OVTLABOR 114688 7
3 INVS3001 5-FEE 131072 8
3 INVS3001 5-FEE 147456 9
3 INVS3002 3-C2924A 16384 1
3 INVS3002 3-D2657A 32768 2
3 INVS3002 3-D2659A 49152 3
3 INVS3002 5-STDLABOR 65536 4
3 INVS3002 5-STDLABOR 81920 5
3 INVS3002 5-TVLLABOR 98304 6
3 INVS3002 5-OVTLABOR 114688 7
3 INVS3002 5-FEE 131072 8
3 INVS3002 5-FEE 147456 9
POPPOLineItemSequenceWorkHistory.SQL
-- Created by Mariano Gomez, MVP
WITH POPPOCTE (POTYPE, PONUMBER, ITEMNMBR, ORD) AS (
SELECT POTYPE, PONUMBER, ITEMNMBR, ORD FROM POP10110
UNION ALL
SELECT POTYPE, PONUMBER, ITEMNMBR, ORD FROM POP30110
)
SELECT POTYPE
, PONUMBER
, ITEMNMBR
, ORD
, ROW_NUMBER() OVER(PARTITION BY PONUMBER, POTYPE ORDER BY PONUMBER, POTYPE, ORD ASC) AS ROWNUMBER
FROM POPPOCTE;
This is just a sample of the results produced by the query -- note the row number column for each partition by PO Type and PO Number:
POTYPE PONUMBER ITEMNMBR ORD ROWNUMBER
------ ----------------- ------------------------------- ----------- --------------------
1 PO1006 PHON-ATT-53BL 16384 1
1 PO1006 PHON-ATT-53BK 32768 2
1 PO1007 HDWR-LDS-0001 16384 1
1 PO1007 HDWR-RNG-0001 32768 2
1 PO1007 HDWR-PRO-4862 49152 3
1 PO1008 HDWR-T1I-0001 16384 1
1 PO1008 HDWR-TPS-0001 32768 2
1 PO1008 HDWR-PRO-4866 49152 3
1 PO1009 HDWR-ACC-0100 16384 1
1 PO1009 FAXX-CAN-9800 32768 2
1 PO1012 ACCS-CRD-25BK 16384 1
1 PO1013 ACCS-HDS-2EAR 16384 1
1 PO1014 PHON-GTE-3458 16384 1
1 PO1015 FAXX-SLK-2100 16384 1
1 PO1016 ACCS-RST-DXWH 16384 1
1 PO2000 ANSW-PAN-1450 16384 1
1 PO2000 PHON-ATT-53RD 32768 2
POPRCLineItemSequenceWorkHistory.SQL
-- Created by Mariano Gomez, MVP
WITH POPRCCTE (POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM) AS (
SELECT POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM FROM POP10310
UNION ALL
SELECT POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM FROM POP30310
)
SELECT POPRCTNM
, PONUMBER
, ITEMNMBR
, RCPTLNNM
, ROW_NUMBER() OVER(PARTITION BY POPRCTNM ORDER BY POPRCTNM, RCPTLNNM ASC) AS ROWNUMBER
FROM POPRCCTE;
This is just a sample of the results produced by the query -- note the row number column for each partition by Receipt Number:
POPRCTNM PONUMBER ITEMNMBR RCPTLNNM ROWNUMBER
----------------- ----------------- ------------------------------- ----------- --------------------
RCT1000 PO1001 WIRE-MCD-0001 16384 1
RCT1000 PO1001 WIRE-SCD-0001 32768 2
RCT1001 PO1002 PHON-GTE-5043 16384 1
RCT1001 PO1002 PHON-GTE-3458 32768 2
RCT1002 PO1003 HDWR-TPS-0001 16384 1
RCT1003 PO1004 HDWR-SWM-0100 16384 1
RCT1003 PO1004 HDWR-SRG-0001 32768 2
RCT1004 PO1005 ACCS-HDS-1EAR 16384 1
RCT1004 PO1005 ACCS-CRD-25BK 32768 2
RCT1005 PO1001 WIRE-MCD-0001 16384 1
RCT1005 PO1001 WIRE-SCD-0001 32768 2
RCT1006 PO1002 PHON-GTE-5043 16384 1
RCT1006 PO1002 PHON-GTE-3458 32768 2
RCT1007 PO0996 PHON-ATT-53RD 16384 1
RCT1007 PO0996 ANSW-PAN-1450 32768 2
RCT1008 PO0998 PHON-ATT-53BL 16384 1
RCT1009 PO0999 ACCS-CRD-12WH 16384 1
RCT1009 PO0999 ANSW-PAN-1450 32768 2
RCT1010 PO0996 PHON-ATT-53RD 16384 1
RCT1010 PO0996 ANSW-PAN-1450 32768 2
RCT1011 PO1009 FAXX-CAN-9800 16384 1
RCT1011 PO1009 HDWR-ACC-0100 32768 2
RCT1011 PO1012 ACCS-CRD-25BK 49152 3
IVCLineItemWorkHistory.SQL
-- Created by Mariano Gomez, MVP
WITH IVCCTE (DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ) AS (
SELECT DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ FROM IVC10101
UNION ALL
SELECT DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ FROM IVC30102
)
SELECT DOCTYPE
, INVCNMBR
, ITEMNMBR
, LNITMSEQ
, ROW_NUMBER() OVER(PARTITION BY DOCTYPE, INVCNMBR ORDER BY DOCTYPE, INVCNMBR, LNITMSEQ ASC) AS ROWNUMBER
FROM IVCCTE;
This is just a sample of the results produced by the query -- note the row number column for each partition by IVC Type and IVC Number:
DOCTYPE INVCNMBR ITEMNMBR LNITMSEQ ROWNUMBER
------- --------------------- ------------------------------- ----------- --------------------
1 IVC01 PHON-ATT-53WH 16384 1
1 IVC02 PHON-ATT-53RD 16384 1
1 IVC03 PHON-ATT-53BK 16384 1
1 IVC04 FAXX-RIC-060E 32768 1
1 IVC05 PHON-PAN-3155 16384 1
1 IVC06 HDWR-CAB-0001 32768 1
1 IVC07 ANSW-PAN-1450 16384 1
1 IVC08 ACCS-RST-DXWH 16384 1
1 IVC09 ACCS-RST-DXBK 16384 1
1 IVC10 ACCS-CRD-12WH 16384 1
1 IVC11 PHON-PAN-3155 49152 1
1 IVC12 ANSW-PAN-1450 16384 1
1 IVC15 HDWR-SRG-0001 16384 1
1 IVC21 24X IDE 16384 1
1 IVC21 32X IDE 32768 2
1 IVC22 ACCS-CRD-12WH 16384 1
1 IVC22 BOT100G 32768 2
1 IVC23 40X IDE 49152 1
(18 row(s) affected)
NOTE: I am a big fan of SQL Server Common Table Expressions (CTE) and use those as often as I can to simplify my queries and avoid the use of subqueries. In addition, they tend to produce better query execution plans which translate into performance gains.
Please stay tuned! Part 2 of this series will show you how to take advantage of these queries from Dexterity and VBA.
Related Articles
Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers - On this blog, click here.
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments