IPTOR SQL

Search for SKU with UPC

select PJPRDC, PJUNIT, PJEANP, PGDESC, LPLCID, SRSTHQ
	 from HD3800EFPN/SROEAN
		 inner join HD3800EFPN/SROPRG
			 on PJPRDC = PGPRDC
		 inner join HD3800EFPN/WHOLOP
			 on PJPRDC = LPPRDC
		 inner join HD3800EFPN/SROSRO
			 on PJPRDC = srprdc
	 where PJUNIT = 'EA' and SROEAN.PJEANP Like '%%'

Search for SKU with SKU

select PJPRDC, PJUNIT, PJEANP, PGDESC, LPLCID, SRSTHQ
	 from HD3800EFPN/SROEAN
		 inner join HD3800EFPN/SROPRG
			 on PJPRDC = PGPRDC
		 inner join HD3800EFPN/WHOLOP
			 on PJPRDC = LPPRDC
		 inner join HD3800EFPN/SROSRO
			 on PJPRDC = srprdc
	 where SROEAN.PJUNIT = 'EA' and SROEAN.PJPRDC = ''

Customer Route

select ADNUM, ADNAME, ADDEST
	 from HD3800EFPN/SRONAD 
	 where ADAD01 = 'Y' and ADDEST <> ' ' and SRONAD.ADNUM Like '%%'

List Customers + Account Group

select NONUM, NANAME, NOAGRP, CTAGRD
	 from HD3800EFPN/SRONOI
		 inner join HD3800EFPN/SR15NAM
			 on NONUM = NANUM
	   	inner join HD3800EFPN/SRBCTLDG
	   		on NOAGRP = CTAGRN
	 where (SRONOI.NOAGRP Like '%C%')

Count Sheets

select LPLCID as "Location", LPPRDC as "SKU", PGDESC as "SKU Description", PGDSUN as "Unit", SRSTHQ as "On Hand", SRCUSQ + SRPICQ as "Reserved", '_______________' as "Count"
	 from HD3800EFPN/WHOLOP
		 inner join HD3800EFPN/SROPRG
			 on LPPRDC = PGPRDC
		 inner join HD3800EFPN/SROSRO
			 on LPPRDC = srprdc
	 where WHOLOP.LPLCID Like 'X%'
	 order by LPLCID

Find Credits

select IHCUNO as "Customer", IHORNO as "Order", IDINVN as "Invoice", IHINRL as "Reference", IAINUM as "Request", IHOREF as "PO"
	 from HD3800EFPN/SXBISH
		 left outer join HD3800EFPN/SXBHNA
			 on IHORNO = IANRNO
		join HD3800EFPN/SXBISD
              on IDORNO = IHORNO
	 where SXBISH.IHCUNO Like '%%' or SXBISH.IHORNO = '' or SXBHNA.IAINUM = '' or SXBISH.IHOREF Like '%%' or SXBISH.IHINVN = '' or SXBISH.IHINRL = ''
	 group by IHORNO, IDINVN, IHCUNO, IHINRL, IHOREF, IAINUM

Find Stuff

select IHCUNO as "Customer", IHORNO as "Order", IDPLNO as "Picking", IDINVN as "Invoice", IDSPNO as "Shipment", IHOREF as "PO"
	 from HD3800EFPN/SXBISH
		 left outer join HD3800EFPN/SXBHNA
			 on IHORNO = IANRNO
		 inner join HD3800EFPN/SXBISD
			 on IDORNO = IHORNO
	 where SXBISH.IHCUNO Like '%%' or SXBISH.IHORNO = '' or SXBISD.IDPLNO = '' or SXBISH.IHINVN = '' or SXBISH.IHOREF Like '%%'
	 group by IHORNO, IHCUNO, IHOREF, IDPLNO, IDINVN, IDSPNO

Order Weight

select SOORNO as "Order", SOPLNO as "Picking", SOSPNO as "Shipment", SOAWGT as "Weight", SOAVAL as "Value"
	 from HD3800EFPN/SROSHO
	 where SOORNO = '' or SOPLNO = ''

Inventory Movement

select SRBITR.ITPRDC as "SKU", PGDESC as "Description", SRBITR.ITQTY as "Quality", SRBITR.ITSTUN as "Unit", SRBITR.ITTEXT as "Order Type", SRBITR.ITDATE as "Date", SRBITR.ITORNO as "Order", SRBITR.ITPLNO as "Picking", IHCUNO as "Customer", SRBITR.ITHAND as "Who", SRBITR.ITLINE as "Line"
	 from HD3800EFPN/SRBITR
		 left outer join HD3800EFPN/SXBISH
			 on SXBISH.IHORNO = SRBITR.ITORNO
		 inner join HD3800EFPN/SROPRG
			 on ITPRDC = PGPRDC
	 where (SRBITR.ITTEXT <> 'Average cost change' and SRBITR.ITTEXT <> 'Rounding diff-cost price calc') and SRBITR.ITPRDC = ''
	 group by SRBITR.ITORNO, SRBITR.ITPRDC, SRBITR.ITSTUN, SRBITR.ITQTY, SRBITR.ITTEXT, SRBITR.ITDATE, SRBITR.ITORNO, SRBITR.ITPLNO, SRBITR.ITHAND, SRBITR.ITLINE, IHCUNO, PGDESC
	 order by SRBITR.ITDATE desc

Sales Order View

select OHORNO as "Order", OLPLNO as "Picking", OHDENO as "Customer", OHHAND as "Who", OHORDS as "Status", OHORDT as "Order Type", OHODAT as "Enter Date", OLDLVD as "Delievery Date", SOAVAL as "Value", OHOREF as "Your Order PO"
	 from HD3800EFPN/SXBSOH
		 left outer join HD3800EFPN/SXBSOL
			 on OHORNO = OLORNO
		 left outer join HD3800EFPN/SROSHO
			 on SOORNO = OLORNO
	 where (SXBSOH.OHDENO Like '%%' or SXBSOH.OLPLNO = '' or SXBSOH.OHORNO = '' or OLDLVD Like '%%') and OHORDS < 31
	 group by OHORNO, OLPLNO, OHDENO, OHHAND, OHORDS, OHORDT, OHODAT, OHOREF, SOAVAL
	 order by OHORNO desc

Delivery Sheets

select SHSPNO, SHDDAT, SDDVSQ, SOORNO, SDCUNO, SDNAME, SDADR1, '_________________________' as "SIGNATURE", OHTOPC
	 from SROSHP
		 inner join SROSHD
			 on SDSPNO = SHSPNO
		 inner join SROSHO
			 on SOSPNO = SHSPNO and SOASQN = SDASQN
		 inner join SROORSHE
			 on OHORNO = SOORNO
	 where SROSHP.SHDDAT = ''

Open Order

select OHORNO as "Order", OHDENO as "Customer", OHHAND as "Who", OHORDS as "Status", OHORDT as "Order Type", OHODAT as "Enter Date", OLDLVD as "Delievery Date", SOAVAL as "Value", OHOREF as "Your Order PO"
	 from HD3800EFPN/SXBSOH
		 left outer join HD3800EFPN/SXBSOL
			 on OHORNO = OLORNO
		 left outer join HD3800EFPN/SROSHO
			 on SOORNO = OLORNO
	 where (SXBSOH.OHDENO Like '%%' or OHOREF Like '%%' or OLDLVD Like '%%') and OHORDS < 11
	 group by OHORNO, OHDENO, OHHAND, OHORDS, OHORDT, OHODAT, OHOREF, SOAVAL, OLDLVD
	 order by OHORNO desc