IPTOR SQL

From PSC IT Wiki
Revision as of 19:40, 15 November 2017 by Lance (talk | contribs) (→‎Possible OS)
Jump to navigation Jump to search

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 LOLCID as "Location", LOPRDA as "SKU", PGDESC as "SKU Description", PGDSUN as "Unit", SRSTHQ as "On Hand", SRCUSQ + SRPICQ as "Reserved", '_______________' as "Count"
	 from HD3800EFPN/WHOLOC
		 inner join HD3800EFPN/SROPRG
			 on LOPRDA = PGPRDC
		 inner join HD3800EFPN/SROSRO
			 on LOPRDA = srprdc
	 where WHOLOC.LOLCID Like 'A%'
	 order by LOLCID

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



select IHCUNO as "Customer", IHORNO as "Order", IDPLNO as "Picking", IDINVN as "Invoice", 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

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", LOLCID as "Location", SRBITR.ITQTY as "QoH", SRBITR.ITSTUN as "Unit", SRBITR.ITTEXT as "Order Type", SRBITR.ITDATE as "Date", SRBITR.ITORNO as "Order", SRBITR.ITPLNO as "Picking", IHCUNO as "Customer", ITUSER as "Who",SRBITR.ITHAND as "Assigned", SRBITR.ITLINE as "Line", SRBITR.ITREAS as "Reason Code"
	 from HD3800EFPN/SRBITR
		 left outer join HD3800EFPN/SXBISH
			 on SXBISH.IHORNO = SRBITR.ITORNO
		 inner join HD3800EFPN/SROPRG
			 on ITPRDC = PGPRDC
		 left outer join HD3800EFPN/WHOLOC
			 on ITPRDC = LOPRDA
	 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, LOLCID,ITUSER,ITREAS
	 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 = '') and OHORDS < 11
	 group by OHORNO, OHDENO, OHHAND, OHORDS, OHORDT, OHODAT, OHOREF, SOAVAL, OLDLVD
	 order by OHORNO desc

Find SKU for Ronnie

select LOPRDA as "SKU", PGDESC as "SKU Description", PGDSUN as "Unit", LOLCID as "Location", SRSTHQ as "On Hand", SRCUSQ + SRPICQ as "Reserved", PJEANP as "UPC", PGMSUP as "Vendor", PGRESP as "Buyer", PUCURR as "Pur Curr", PJCONV as "Conv Factor", PJGWGT as "Gross Weight", PIAPP1 as "Act Pur Prc", PGLPCO as "Last Pur Cost", PGSTCO as "Standard Cost", PGAPCO as "Avg Pur Cost", PPSALP as "P10 Price"
	 from HD3800EFPN/WHOLOC
		 left outer join HD3800EFPN/SROPRG
			 on LOPRDA = PGPRDC
		 left outer join HD3800EFPN/SROSRO
			 on LOPRDA = srprdc
		 left outer join HD3800EFPN/SROEAN
			 on LOPRDA = SROEAN.PJPRDC
		 inner join HD3800EFPN/SXBPPU
			 on LOPRDA = SXBPPU.PUPRDC
		 inner join HD3800EFPN/SXBPRU
			 on LOPRDA = SXBPRU.PJPRDC
		 inner join HD3800EFPN/SROPPS
			 on LOPRDA = PPPRDC
			 inner join HD3800EFPN/SROPPP 
			 on LOPRDA = PIPRDC
	 where WHOLOC.LOPRDA = '' and SROPPS.PPPRIL = '10'
	 group by LOPRDA, PGDESC, PGDSUN, LOLCID, SRSTHQ, PJEANP, PGMSUP, PGRESP, PUCURR, PJCONV, PGLPCO, PGSTCO, PGAPCO, SRCUSQ, SRPICQ, PJGWGT, PPSALP,PIAPP1,PGLPCO
	 order by PUCURR, PJCONV

Inventory Movement for Ronnie

select SRBITR.ITPRDC as "SKU", PGDESC as "Description", LOLCID as "Location", SRBITR.ITQTY as "QoH", SRBITR.ITSTUN as "Unit", SRBITR.ITTEXT as "Order Type", SRBITR.ITDATE as "Date", SRBITR.ITORNO as "Order", SRBITR.ITPLNO as "Picking", IHCUNO as "Customer", ITUSER as "Who",SRBITR.ITHAND as "Assigned", SRBITR.ITLINE as "Line", SRBITR.ITREAS as "Reason Code"
	 from HD3800EFPN/SRBITR
		 left outer join HD3800EFPN/SXBISH
			 on SXBISH.IHORNO = SRBITR.ITORNO
		 inner join HD3800EFPN/SROPRG
			 on ITPRDC = PGPRDC
		 left outer join HD3800EFPN/WHOLOC
			 on ITPRDC = LOPRDA
	 where SRBITR.ITPRDC = '06005'
	 group by SRBITR.ITORNO, SRBITR.ITPRDC, SRBITR.ITSTUN, SRBITR.ITQTY, SRBITR.ITTEXT, SRBITR.ITDATE, SRBITR.ITORNO, SRBITR.ITPLNO, SRBITR.ITHAND, SRBITR.ITLINE, IHCUNO, PGDESC, LOLCID,ITUSER,ITREAS
	 order by SRBITR.ITDATE desc

Possible OS

select SXBSOL.OlPRDC as "SKU", SXBSOL.OLDESC as "Desc", SXBSOL.OLOQTY as "Order Qty", SRSTHQ as "On Hand Qty", SRCUSQ + SRPICQ as "Reserved", SROORPPL.OLRDDT as "ETA on PO", SROORPPL.OLOQTY as "Qty on PO"
	 from HD3800EFPN/SXBSOL
		 inner join HD3800EFPN/SROSRO
			 on SXBSOL.OlPRDC = SRPRDC
		 inner join HD3800EFPN/SROORPPL
			 on SXBSOL.OlPRDC = SROORPPL.OLPRDC
		 inner join HD3800EFPN/SROPRG
			 on SXBSOL.OlPRDC = PGPRDC
	 where SXBSOL.OLORNO = '' and SRSTHQ < SXBSOL.OLOQTY and SROORPPL.OLORDS < 60
	 group by SXBSOL.OlPRDC, SXBSOL.OLDESC, SXBSOL.OLOQTY, SRSTHQ, SROORPPL.OLRDDT, SROORPPL.OLOQTY,SRCUSQ + SRPICQ
	 order by SXBSOL.OlPRDC, SROORPPL.OLRDDT
Retrieved from "https://psc.zeropoke.net/index.php?title=IPTOR_SQL&oldid=775"