IPTOR SQL

From PSC IT Wiki
Jump to navigation Jump to search

Search SKU/UPC

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", SROORPPL.OLRDDT as "PO ETA", SROORPPL.OLOQTY as "PO Qty",SROORPPL.OLORNO
	 from HD3800EFPN/WHOLOC
		 left outer join HD3800EFPN/SROORPPL
			 on LOPRDA = SROORPPL.OLPRDC and SROORPPL.OLORDS < 60 and SROORPPL.OLORDS > 10  and SROORPPL.OLSTAT  != 'D'
		 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
	 where WHOLOC.LOPRDA Like '%%' or PJEANP Like '%%' or PGDESC Like '%%'

For Ryan 

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", SROORPPL.OLRDDT as "PO ETA", SROORPPL.OLOQTY as "PO Qty", (PPSALP * 0.85) as "Staff Price"
	 from HD3800EFPN/WHOLOC
		 left outer join HD3800EFPN/SROORPPL
			 on LOPRDA = SROORPPL.OLPRDC and SROORPPL.OLORDS < 60 and SROORPPL.OLORDS > 10  and SROORPPL.OLSTAT  != 'D'
		 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
		left outer join HD3800EFPN/SRBPPS
		   on LOPRDA = PPPRDC and PPPRIL = '10'
	 where WHOLOC.LOPRDA Like '%%' or PJEANP Like '%%' or PGDESC Like '%%'

Customer Route

select ADNUM as "Customer Code", ADNAME as "Full Name", ADDEST as "Route", RTDEW1 as "Monday", RTDEW2 as "Tuesday", RTDEW3 as "Wednesday", RTDEW4 as "Thursday", RTDEW5 as "Friday", NAARHA as "Rep", CMTOPC as "Terms"
	 from HD3800EFPN/SRONAD
		 left outer join HD3800EFPN/SRORTE
			 on ADDEST like RTROUT
		 left outer join HD3800EFPN/SRONAM
			 on ADNUM like NANUM
		 left outer join HD3800EFPN/SROCMA
			 on ADNUM like CMCUNO
	 where ADAD01 = 'Y' and ADDEST <> ' ' and 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

Count Sheets with UPC

select LOLCID as "Location", LOPRDA as "SKU", PGDESC as "SKU Description", PGDSUN as "Unit", SRSTHQ as "On Hand", SRCUSQ + SRPICQ as "Reserved", '_______________' as "Count", PJEANP as "UPC"
	 from HD3800EFPN/WHOLOC
		 inner join HD3800EFPN/SROPRG
			 on LOPRDA = PGPRDC
		 inner join HD3800EFPN/SROSRO
			 on LOPRDA = srprdc
		inner join HD3800EFPN/SROEAN
			 on LOPRDA = SROEAN.PJPRDC
	 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", IHIDAT as "Delivery Date",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 IHIDAT Like '%%' or SXBISH.IHOREF Like '%%'
	 group by IHORNO, IHCUNO, IHOREF, IDPLNO, IDINVN, IDSPNO,IHIDAT
	 order by IHORNO desc



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 "Quality", SRBITR.ITSTUN as "Unit", SRBITR.ITTEXT as "Order Type", SRBITR.ITDATE as "Date", SRBITR.ITORNO as "Order", SRBITR.ITPLNO as "Picking", IHINVN as "Invoice", IHCUNO as "Customer", SRBITR.ITHAND as "Who", SRBITR.ITLINE as "Line", SRBITR.ITREAS as "Reason Code"
	 from HD3800EFPN/SRBITR
		 left outer join HD3800EFPN/SXBISH
			 on SXBISH.IHORNO = SRBITR.ITORNO
		 left outer 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.ITTEXT <> 'Manual average cost change') 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,ITREAS, IHINVN
	 order by SRBITR.ITDATE desc

Inventory Movement Date Range

select SRBITR.ITPRDC as "SKU", PGDESC as "Description", LOLCID as "Location", SRBITR.ITQTY as "Quantity", SRBITR.ITSTUN as "Unit", SRBITR.ITTEXT as "Order Type", ITDATE as "Transaction Date", IHIDAT as "Invoice Date", SRBITR.ITORNO as "Order", SRBITR.ITPLNO as "Picking", IHCUNO as "Customer", SRBITR.ITHAND as "Who", SRBITR.ITLINE as "Line", SRBITR.ITREAS as "Reason Code"
	 from HD3800EFPN/SRBITR
		 left outer join HD3800EFPN/SROPRG
			 on ITPRDC = PGPRDC
		 left outer join HD3800EFPN/SXBISH
			 on SXBISH.IHORNO = SRBITR.ITORNO
		 left outer join HD3800EFPN/WHOLOC
			 on ITPRDC = LOPRDA
	 where SRBITR.ITTEXT <> 'Average cost change' and SRBITR.ITTEXT <> 'Rounding diff-cost price calc' and SRBITR.ITTEXT <> 'Manual average cost change' and (ITDATE >= '' and ITDATE <= '')
	 order by 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 as "Shipment #", SDDVSQ as "Sequence", SOORNO as "Order #", SDCUNO as "Cust Code", SDNAME as "Name", SDADR1 as "Address", ULCPAR as "Phone #", OHTOPT as "Terms", '_________________________' as "SIGNATURE"
                from SROSHP
                                inner join SROSHD
                                                on SDSPNO = SHSPNO
                                inner join SROSHO
                                                on SOSPNO = SHSPNO and SOASQN = SDASQN
                                left outer join SROORSHE
                                                on OHORNO = SOORNO and (OHTOPC = 'COD' or OHTOPC = 'C20')
                                left outer join SXBISH
                                                on SOORNO = IHORNO
                                left outer join SROCLL
                                                on CONCAT(RTRIM(SDCUNO), 'CDL') = ULUSER and ULCLNT = '*PHONE'
                where SHSPNO = ''
                order by SHSPNO desc, SDDVSQ

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
		 left outer join HD3800EFPN/SROORPPL
			 on SXBSOL.OlPRDC = SROORPPL.OLPRDC and SROORPPL.OLSTAT  != 'D' and SROORPPL.OLORDS < 60 and SROORPPL.OLORDS > 10
		 inner join HD3800EFPN/SROPRG
			 on SXBSOL.OlPRDC = PGPRDC
	 where SXBSOL.OLORNO = ''  and SXBSOL.OLSTAT  != 'D'  and (SRSTHQ < SXBSOL.OLOQTY or SRCUSQ + SRPICQ > SRSTHQ)
	 group by SXBSOL.OlPRDC, SXBSOL.OLDESC, SXBSOL.OLOQTY, SRSTHQ, SROORPPL.OLRDDT, SROORPPL.OLOQTY, SRCUSQ + SRPICQ
	 order by SXBSOL.OlPRDC, SROORPPL.OLRDDT

John

select OLCUNO as "Cust Code", ADNAME as "Full Name", OLPRDC as "SKU", OLDESC as "SKU Desc", OLOQTY as "Qty Ordered", OLCQTY as "Qty Invoiced", IHIDAT as "Delivery Date", OLORNO as "Order Number", OLPLNO as "Pick Number"
	 from HD3800EFPN/SXBSOL
		 inner join HD3800EFPN/SRONAD
			 on OLCUNO = ADNUM
		 left outer join HD3800EFPN/SXBISH
			 on OLORNO = IHORNO
	 where OLPRDC LIKE '%%' and IHIDAT = '2017'
	 group by OLCUNO, OLPRDC, ADNAME, OLDESC, OLOQTY, OLCQTY, IHIDAT, OLPLNO, OLORNO
	 order by OLCQTY

SKU On Order

select OLCUNO as "Cust Code", ADNAME as "Full Name", OLPRDC as "SKU", OLDESC as "SKU Desc", OLOQTY as "Qty Ordered", OLORNO as "Order Number", OLPLNO as "Pick Number", OHOREF as "PO"
	 from HD3800EFPN/SXBSOL
		 inner join HD3800EFPN/SRONAD
			 on OLCUNO = ADNUM
		 left outer join HD3800EFPN/SXBISH
			 on OLORNO = IHORNO
		 left outer join HD3800EFPN/SXBSOH
			 on OLORNO = OHORNO
	 where OLPRDC Like '%%' and OHORDS < 45
	 group by OLCUNO, OLPRDC, ADNAME, OLDESC, OLOQTY, OLCQTY, IHIDAT, OLPLNO, OLORNO,OHOREF
	 order by OLORNO desc

Reason 8

select ITDATE as "Date", ITPRDC as "SKU", PGDESC as "Description", ITQTY as "Qty", ITSTUN as "Unit", ITITCV as "Value", ITUSER as "User", ITREAS as "Reason"
	 from HD3800EFPN/SRBITR 
	 		 inner join HD3800EFPN/SROPRG
			 on ITPRDC = PGPRDC
	 where ITREAS = '8'
	 order by ITDATE desc

Find Pick Number

select IDCUNO, IDINVN, IDORNO, IDPLNO as "Pick List #", IDSPNO, IHOREF
	 from HD3800EFPN/SXBISD
		 inner join HD3800EFPN/SXBISH
			 on IDORNO = IHORNO
	 where SXBISD.IDPLNO = '' or SXBISD.IDORNO = '' or SXBISD.IDINVN = '' or SXBISH.IHOREF Like '%%'

Shipment Print Check

select SOSPNO as "Shipment #", SOORNO as "Order #", SOPLNO as "Pick List #", OHPCOC as "Combined Print?", OHCUNO as "Cust Code", OHNAME
	 from HD3800EFPN/SROSHO
		 left outer join HD3800EFPN/SXBSOH
			 on SOORNO = OHORNO
	 where SOSPNO = ''

Open Orders via Route

select OHORNO as "Order", OHDENO as "Customer", OHDEST as "Route", 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 OHDEST Like '%%' and OHORDS < 11
	 group by OHORNO, OHDENO, OHHAND, OHORDS, OHORDT, OHODAT, OHOREF, SOAVAL, OLDLVD, OHDEST
	 order by OHORNO desc

Amending Marlene

select  SOPLNO as "Pick", OHOREF as "PO", SOORNO as "Order #", SDCUNO as "Customer", SHSPNO as "Shipment #", SHROUT as "Run", ROUND(DECFLOAT(OHNXLI/10)) as "Lines" ,OHOVAL as "Order Value", OHORDS as "Status", OHTOPC as "Terms"
	 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 = 
	 	    YEAR(CURRENT_DATE - 1 DAY)*10000 + 
	    MONTH(CURRENT_DATE - 1 DAY)*100 + 
	    DAY(CURRENT_DATE + 1 DAY))

No Rep Cust List Lea-Ann

select NANUM as "Cust Code", NANAME as "Full Name", NAADR1 as "Address 1", x.ULCPAR as "Phone", LTRIM(y.ULCPAR,'*MAIL=') as "Email", (SELECT SUM(IHOTOT) FROM SXBISH where IHCUNO = NANUM and IHIDAT Like '%2019%') as "Sales Total", CTAGRD
from HD3800EFPN/SRONAM
	     left outer join SROCLL x
              on CONCAT(RTRIM(NANUM), 'CDL') = x.ULUSER and x.ULCLNT = '*PHONE'
	     left outer join SROCLL y
              on CONCAT(RTRIM(NANUM), 'CDL') = y.ULUSER and y.ULCLNT = 'MAIL'
          left outer join SRONOI
          		on NONUM = NANUM
          left outer join SROCTLDG
          		on NOAGRP = CTAGRN
              where NAARHA = 'NOREP'
              order by CTAGRD,NANUM

Latest Accounts Created

select
NANUM "Code",
NANAME "Name",
NASTAT "Status",
NACRDT "Created"
from SRONAM b
where NATYPP !=2
and NACRDT between '20190401' and varchar_format(now(), 'YYYYMMDD')

Michelle CC Info

select NCCUNO as "Cust #", NCCARD as "Type", RIGHT(Cast(NCCRCN as VARCHAR(16)),4) as "CC #", VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(NCCCEX),'YYYYMM')), 'Mon - YYYY') as "Expiry", NCADR1 as "Note 1", NCADR2 as "Note 2", NCADR3 as "Note 3"
	 from SRONCC
	 order by NCCUNO

All Customers Contacts

select NAARHA as "Rep", NAADR4 as "City", NANUM as "Code", NANAME as "Custom Name", NFPHNO as "Phone #1", H.PACONT as "CDL Contact", A.ULCPAR as "Phone CDL", LTRIM(F.ULCPAR, '*MAIL=') as "CDL Email",G.PACONT as "AR Contact",B.ULCPAR as "AR Phone", LTRIM(C.ULCPAR, '*MAIL=') as "AR Email",  I.PACONT as "Recall Contact", LTRIM(D.ULCPAR, '*MAIL=') as "Recall Email", E.ULCPAR as "Recall Phone"
	 from SRONAM
		 left outer join SRONFP
			 on NANUM = NFNANO and NFPHTP = '*PHONE'
		 left outer join SROCLL as A
			 on CONCAT(RTRIM(NANUM), 'CDL') = A.ULUSER and A.ULCLNT = '*PHONE'
		 left outer join SROCLL as F
			 on CONCAT(RTRIM(NANUM), 'CDL') = F.ULUSER and F.ULCLNT = 'MAIL'
		 left outer join SROCLL as B
			 on CONCAT(RTRIM(NANUM), 'AR') = B.ULUSER and B.ULCLNT = '*PHONE'
		 left outer join SROCLL as C
			 on CONCAT(RTRIM(NANUM), 'AR') = C.ULUSER and C.ULCLNT = 'MAIL'
		 left outer join SROCLL as D
			 on CONCAT(RTRIM(NANUM), 'RC') = D.ULUSER and D.ULCLNT = '*PHONE'
		 left outer join SROCLL as E
			 on CONCAT(RTRIM(NANUM), 'RC') = E.ULUSER and E.ULCLNT = 'MAIL'
		 left outer join SROCTP as G
			 on CONCAT(RTRIM(NANUM), 'AR') = G.PACOID
		 left outer join SROCTP as H
			 on CONCAT(RTRIM(NANUM), 'CDL') = H.PACOID
		 left outer join SROCTP as I
			 on CONCAT(RTRIM(NANUM), 'RC') = I.PACOID
	 where NATYPP = 1 and NASTAT <> 'D'
	 group by NAARHA, NAADR4, NANUM, NANAME, NFPHNO, A.ULCPAR, B.ULCPAR, C.ULCPAR, D.ULCPAR, E.ULCPAR, G.PACONT, I.PACONT, H.PACONT,F.ULCPAR
	 order by NANUM

CHEP John

select OLORNO as "Supplier Invoice #", OLSUNO as "Supplier Code", OLORDT as "Order Type", OLPRDC as "SKU", OLDESC as "Desc", OLPQTY as "Quantity", ' | ' as "|", IDCUNO as "Cust Code", OLSORN as "Sales Order #", IDINVN as "Invoice #", IDPRDC as "SKU", IDDESC as "Desc", IDQTY as "Quantity"
	 from HD3800EFPN/SRBPOL
		 inner join HD3800EFPN/SXBISD
			 on OLSORN = IDORNO and OLPRDC = IDPRDC
	 where SRBPOL.OLORNO Like '%%'

Terms

select CMCUNO as "Cust Code", NANAME "Cust Name", CMTOPC as "Term Code", CTTOPT as "Term Desc"
	 from SXBCMA 
	 inner join SRONAM
	 on NANUM = CMCUNO
	 inner join SROCTLSJ
	 on CMTOPC = CTTOPC
	 order by CMCUNO

select CMCUNO as "Cust Code", NANAME "Cust Name", CMTOPC as "Term Code"
	 from SXBCMA 
	 inner join SRONAM
	 on NANUM = CMCUNO
	 where SXBCMA.CMTOPC = 'COD'  or SXBCMA.CMTOPC = 'C30' 
	 order by CMTOPC,CMCUNO

Look Up Cust via CC

select NCCUNO as "Cust #", NANAME as "Cust Name", NCCARD as "Type", NCCCEX as "Expiry"
	 from SRONCC
		 inner join SRONAM
			 on NANUM = NCCUNO
	 where NCCRCN Like '%%'

Customer Notes

select IBNANO as "Cust Code", NANAME as "Cust Name", IBNOID as "Note ID", GTTX70 as "Note"
	 from HD3800EFPN/SRONIB
		 left outer join HD3800EFPN/SRONID
			 on IBNOID = NINOID
		 left outer join HD3800EFPN/SROTGT
			 on NITXKY = GTTXKY
		inner join HD3800EFPN/SRONAM
		on IBNANO = NANUM
			 group by IBNANO, NANAME,IBNOID, GTTX70

Fake Pick List

select LOLCID as "Location", OLDESC as "Description", OLPRDC as "SKU", OLOQTY as "Qty",OLUNIT as "UOM",  '____________________' as "Picked",  '____________________' as "Note", SRSTHQ as "QOH"
	 from HD3800EFPN/SRBSOL
	 		inner join HD3800EFPN/WHOLOC
			on  OLPRDC = LOPRDA
			 left outer join HD3800EFPN/SROSRO
			 on OLPRDC = srprdc
	 where OLORNO = '199870'
	 order by LOLCID

Rosa Food Service List

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 '%C500')
	 order by NONUM

Customer List via Route

select ADNUM, ADNAME, ADDEST, H.PACONT as "CDL Contact", A.ULCPAR as "Phone CDL", LTRIM(F.ULCPAR, '*MAIL=') as "CDL Email"
	 from SRONAD
		 left outer join SRONFP
			 on ADNUM = NFNANO and NFPHTP = '*PHONE'
		 left outer join SROCLL as A
			 on CONCAT(RTRIM(ADNUM), 'CDL') = A.ULUSER and A.ULCLNT = '*PHONE'
		 left outer join SROCLL as F
			 on CONCAT(RTRIM(ADNUM), 'CDL') = F.ULUSER and F.ULCLNT = 'MAIL'
		 left outer join SROCTP as H
			 on CONCAT(RTRIM(ADNUM), 'CDL') = H.PACOID
	 where SRONAD.ADDEST Like '%%' and ADDEST <> '' and ADDEST <> 'PU'
	 group by ADNUM, ADNAME, ADDEST, H.PACONT, A.ULCPAR, F.ULCPAR
	 order by ADNUM

Customer List via Route /w Address

select SRONAD.ADNUM, SRONAD.ADNAME, SRONAD.ADDEST as "Route", D.ADADNO as "Address#", D.ADADR1 as "Address",H.PACONT as "CDL Contact", A.ULCPAR as "Phone CDL", LTRIM(F.ULCPAR, '*MAIL=') as "CDL Email"
	 from SRONAD
		 left outer join SRONFP as S
			 on SRONAD.ADNUM = NFNANO and NFPHTP = '*PHONE'
		 left outer join SROCLL as A
			 on CONCAT(RTRIM(SRONAD.ADNUM), 'CDL') = A.ULUSER and A.ULCLNT = '*PHONE'
		 left outer join SROCLL as F
			 on CONCAT(RTRIM(SRONAD.ADNUM), 'CDL') = F.ULUSER and F.ULCLNT = 'MAIL'
		 left outer join SROCTP as H
			 on CONCAT(RTRIM(SRONAD.ADNUM), 'CDL') = H.PACOID
		left outer join SRONAD as D
			 on SRONAD.ADNUM = D.ADNUM and D.ADAD01 = 'Y'
	 where SRONAD.ADDEST Like '%%' and SRONAD.ADDEST <> '' and SRONAD.ADDEST <> 'PU'
	 group by SRONAD.ADNUM, SRONAD.ADNAME, SRONAD.ADDEST, H.PACONT, A.ULCPAR, F.ULCPAR, D.ADADR1, D.ADADNO
	 order by SRONAD.ADNUM

Look up DD Order

select IDCUNO as "Customer", IDINVN as "Invoice #", IDORNO as "Order #", IDDDAT as "Dispatch Date", IDPORN as "PO #", IHOREF as "Order Description"
	 from HD3800EFPN/SXBISD 
	 inner join HD3800EFPN/SXBISH
	 on IDORNO = IHORNO
	 where IDORDT = 'DD' and IDCUNO = 'JAC1600' and IDDDAT >= 20190301 and IDDDAT <= 20200608
	 group by IDINVN, IDORNO, IDDDAT, IDPORN, IDCUNO,IHOREF
	 order by IDDDAT

select OLCUNO as "Cust Code", ADNAME as "Full Name", OLPRDC as "SKU", OLDESC as "SKU Desc", OLOQTY as "Qty Ordered", OLCQTY as "Qty Invoiced", IHIDAT as "Delivery Date", OLORNO as "Order Number", OLPLNO as "Pick Number"
	 from HD3800EFPN/SXBSOL
		 inner join HD3800EFPN/SRONAD
			 on OLCUNO = ADNUM
		 left outer join HD3800EFPN/SXBISH
			 on OLORNO = IHORNO
	 where OLPRDC Like '%%' and IHIDAT = '2017'
	 group by OLCUNO, OLPRDC, ADNAME, OLDESC, OLOQTY, OLCQTY, IHIDAT, OLPLNO, OLORNO
	 order by OLCQTY

For Labels

select LOLCID as "Location", LOPRDA as "SKU", PGDESC as "SKU Description",PJEANP as "UPC"
	 from HD3800EFPN/WHOLOC
		 inner join HD3800EFPN/SROPRG
			 on LOPRDA = PGPRDC
			 		 left outer join HD3800EFPN/SROEAN
			 on LOPRDA = SROEAN.PJPRDC
	 where WHOLOC.LOLCID Like 'A%'
	 order by LOLCID

Amending Edit List

select SOPLNO as "Pick", OHOREF as "PO", SOORNO as "Order #", SDCUNO as "Customer", SHSPNO as "Shipment #", SHROUT as "Run", ROUND(DECFLOAT(OHNXLI / 10)) as "Lines", OHORDS as "Status", OHTOPC as "Terms"
                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 = ''
                                order by SOPLNO

CC PrePaid

select distinct SHSPNO as "Shipment #", SHROUT as "Route", SHDDAT as "Delivery Date", IHINVN as "Invoice #", SDDVSQ as "Sequence", SOORNO as "Order #", SDCUNO as "Customer #", SDNAME as "Customer", OHTOPC as "Terms", NCCRCN as "CC #", NCCCEX as "CC Expiry", NCADR1 as "Note 1", NCADR2 as "Note 2", NCADR3 as "Note 3", IHIAIT as "Total Owed for Invoice"
                from SROSHP
                                inner join SROSHD
                                                on SDSPNO = SHSPNO
                                inner join SROSHO
                                                on SOSPNO = SHSPNO and SOASQN = SDASQN
                                inner join SROORSHE
                                                on OHORNO = SOORNO
                                inner join SRONCC
                                                on SDCUNO = NCCUNO
                                left outer join SXBISH
                                                on SOORNO = IHORNO
                where OHTOPC IN ('C30') and SHROUT <> 'P/U' and SHROUT <> 'I' and SROSHP.SHDDAT = ''
                order by SHSPNO desc, SDCUNO

CC PrePaid New

select distinct SHDDAT as "Delivery Date", SHROUT as "Route", IHINVN as "Invoice #", SOORNO as "Order #", SDCUNO as "Customer #", SDNAME as "Customer", OHTOPC as "Terms", NOAGRP as "Account Group"
	 from SROSHP
		 inner join SROSHD
			 on SDSPNO = SHSPNO
		 inner join SROSHO
			 on SOSPNO = SHSPNO and SOASQN = SDASQN
		 inner join SROORSHE
			 on OHORNO = SOORNO
		 inner join SRONOI
			 on SDCUNO = NONUM
		 left outer join SXBISH
			 on SOORNO = IHORNO
	 where (OHTOPC IN ('C30') or NOAGRP='C600') and SROSHP.SHDDAT = ''
	 order by SHDDAT desc,SHROUT desc

List for AR/Tanya

select SHDDAT as "Dispatch Date", SDDVSQ as "Del Seq", SOORNO as "Order #", SDCUNO as "Customer #", SDNAME as "Customer Name", OHTOPC as "Terms", SHDESC as "Route"
	 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 = ''
	 order by SHSPNO desc, SDDVSQ

Location Labels

select LOLCID as "Location", LOPRDA as "SKU", PGDESC as "SKU Description", PJEANP as "UPC"
	 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
	 where WHOLOC.LOLCID Like '%%' or LOPRDA Like '%%' or PGDESC Like '%%'
	 order by LOLCID

OLD Build DB - Webapp

select PJEANP as "UPC", PGPRDC as "SKU", PGDESC as "Description", LOLCID as "Location"
	 from HD3800EFPN/SROPRG
		 left join HD3800EFPN/SROEAN
			 on PGPRDC = SROEAN.PJPRDC
		 left join HD3800EFPN/WHOLOC
			 on PGPRDC = LOPRDA
	 where (PGSTAT <> 'D' and PGPRDC Not Like '%C%' and PGPCA1 Not Like 'SPC%' and PGPCA1 !='') OR PGPCA1 = 'SPC02'
	 order by PGCDAT desc

Build DB - Webapp

select PJEANP as "UPC", PGPRDC as "SKU", PGDESC as "Description", LOLCID as "Location", PGPVER as "CaseSize", PGDRNR as "EachSize",PGPCA5 as "EachType",PGUFA1 as "TiHi", PGUFN1  as "MinSell", PGUFN2 as "MinRec"
	 from HD3800EFPN/SROPRG
		 left outer join HD3800EFPN/SROEAN
			 on PGPRDC = SROEAN.PJPRDC
		 left outer join HD3800EFPN/WHOLOC
			 on PGPRDC = LOPRDA
	 where (PGSTAT <> 'D' and PGPCA1 !='')
	 order by PGCDAT desc

Pricelist 160 Content

select PPPRDC as "SKU", PJEANP as "UPC", PGDESC as "Description", PPUNIT as "Unit", PPSALP as "Price"
	 from HD3800EFPN/SROPPS AS P
		 left outer join HD3800EFPN/SROPRG as I
			 on PPPRDC = PGPRDC
		 left outer join HD3800EFPN/SROEAN
			 on PPPRDC = SROEAN.PJPRDC
	 where PPPRIL = '160' and PGSTAT <> 'D' and (P.PPTODT is NULL or P.PPTODT < 1)
	 order by PPPRDC

Restriction Group 160

select GIPRDC as "SKU", PJEANP as "UPC", PGDESC as "Description", PPUNIT as "Unit", PPSALP as "Price"
	 from HD3800EFPN/SROIRGI join SROPRG
			 on GIPRDC = PGPRDC join SROEAN
			 on GIPRDC = PJPRDC left join SROPPS
			 on GIPRDC = PPPRDC and PPPRIL = '160' and PPTODT < 1
	 WHERE GIIRGP = '160' and PGSTAT <> 'D'
	 order by GIPRDC
Retrieved from "https://psc.zeropoke.net/index.php?title=IPTOR_SQL&oldid=1052"