I'm trying to create a view for SOP order lines. It needs to show all Item Numbers from SOP10200 in one column but another column that returns the kit item when it exists - i.e. shows the same Item Number if the component sequence is 0, but if the component sequence is greater than 0 (kit components), it shows the item number with the same line sequence and component sequence of 0. Does someone know how to do this? Any help would be appreciated.
An example is the below where HDWR-DCD-00001 is a kit and WATCH is a regular sales inventory item.
| SOPTYPE |
SOPNUMBE |
LNITMSEQ |
CMPNTSEQ |
ITEMNMBR |
Returned Item Number |
| 2 |
ORDM1010 |
16384 |
0 |
HDWR-DCD-0001 |
HDWR-DCD-0001 |
| 2 |
ORDM1010 |
16384 |
16384 |
ITCT-CIR-CD85 |
HDWR-DCD-0001 |
| 2 |
ORDM1010 |
16384 |
32768 |
RESR-TRR-68KM |
HDWR-DCD-0001 |
| 2 |
ORDM1010 |
16384 |
49152 |
RMTL-CAP-10MF |
HDWR-DCD-0001 |
| 2 |
ORDM1003 |
16384 |
0 |
WATCH |
WATCH |
This is what I have so far. Instead of 'kit component' for ITEM NUMBER, I need it to return the actual kit item number like the above:
select
B.BACHNUMB AS 'BATCH NUMBER',
B.DOCDATE AS 'ORDER DATE',
B.SHIPMTHD AS 'SHIPPING METHOD',
A.SOPTYPE as 'SALES ORDER TYPE',
B.DOCID AS 'DOCUMENT ID',
A.SOPNUMBE as 'SALES ORDER NUMBER',
B.CUSTNMBR AS 'CUSTOMER NUMBER',
B.CUSTNAME AS 'CUSTOMER NAME',
B.SHIPTONAME AS 'SHIP TO NAME',
A.ITEMNMBR as 'ITEM NUMBER SEARCH',
A.ITEMDESC AS 'ITEM DESCRIPTION',
(select CASE
WHEN A.CMPNTSEQ > '0' THEN 'Kit Component'
ELSE A.ITEMNMBR
END as CMPNTSEQ) AS 'ITEM NUMBER',
A.UOFM AS 'UNIT OF MEASURE',
A.LOCNCODE AS 'SITE',
A.QUANTITY AS 'QTY ORDERED',
A.QTYREMAI AS 'QTY REMAINING',
A.ATYALLOC AS 'QTY ALLOCATED',
A.QTYTBAOR as 'QTY BACKORDERED',
A.QTYFULFI AS 'QTY FULFILLED',
A.UNITPRCE AS 'UNIT PRICE',
CASE C.ITEMTYPE
WHEN 3 THEN 'Kit Header'
WHEN 1 THEN 'Sales Inventory'
ELSE 'Non-Inventory'
END 'ITEM TYPE',
A.LNITMSEQ AS 'LINE SEQ',
(select CASE
WHEN A.CMPNTSEQ > '0' THEN 'Kit Component'
ELSE ''
END as CMPNTSEQ) AS 'KIT ITEM',
CASE D.DELETE1
WHEN 0 THEN 'Hold'
Else ''
END 'PROCESS HOLD',
(SELECT COUNT(*) FROM SOP10200 z WHERE z.SOPTYPE = a.SOPTYPE AND z.SOPNUMBE = a.SOPNUMBE) AS LineCount
FROM SOP10200 A
inner join SOP10100 B ON A.SOPTYPE = B.SOPTYPE AND A.SOPNUMBE = B.SOPNUMBE
inner join IV00101 C ON A.ITEMNMBR = C.ITEMNMBR
LEFT OUTER JOIN SOP10104 D ON A.SOPNUMBE = D.SOPNUMBE
where A.SOPTYPE = 2 and A.QTYREMAI > 0 AND B.VOIDSTTS = 0