-- Remove from old commented parts and add on join DataAreaId and Partition for improve queries

select case		when pt.PRODSTATUS = 0 then 'Creato'
				when pt.PRODSTATUS = 1 then 'Stimato'
				when pt.PRODSTATUS = 2 then 'Programmato'
				when pt.PRODSTATUS = 3 then 'Rilasciato'
				when pt.PRODSTATUS = 4 then 'Iniziato'
		end PRODSTATUS,
		pt.PRODID,
		pt.ITEMID,
		pt.NAME,
		cast(round(pt.QTYSCHED, 0) as numeric(10, 0)) QTYSCHED,
		firstdate.DATES FIRSTDATE,
		lastdate.DATES LASTDATE,
		lastdate.OPRNUM LASTSEQ,
		lastdate.WRKCTRID LASTMACC,
		id.INVENTLOCATIONID,
		pt.DLVDATE
from PRODTABLE																	pt
left outer join INVENTDIM														id
	on id.INVENTDIMID = pt.INVENTDIMID
	and id.DATAAREAID = pt.DATAAREAID
	and id.PARTITION = pt.PARTITION
join (	select  pt1.PRODID,
				-- TERMINATO
				case	when sum(DEPOSITEDQTY) >= (pt1.QTYSCHED * count(pr1.RECID)) 
						or sum(GROUPEDPJR.OPRFINISHED) = count(pr1.RECID) 
						or sum(pr1.OPRFINISHED) = count(pr1.RECID) 
				then 2
				-- IN CORSO
			  			when count(mt.RECID) > 0 or max(GROUPEDPJR.OPRFINISHED) > 0 
				then 1 
				-- NON INIZIATO
				else 0 
				end PRODADVANCESTATUS
		from PRODTABLE										pt1
		join PRODROUTE										pr1
			on pr1.PRODID = pt1.PRODID
			and pr1.DATAAREAID = pt1.DATAAREAID
			and pr1.PARTITION = pt1.PARTITION
		join WRKCTRPRODROUTEACTIVITY						wcpra
			on wcpra.OPRPRIORITY = pr1.OPRPRIORITY
			and wcpra.OPRNUM = pr1.OPRNUM
			and wcpra.PRODID = pr1.PRODID
			and wcpra.ROUTEDATAAREAID = pr1.DATAAREAID
			and wcpra.PARTITION = pr1.PARTITION
		join WRKCTRACTIVITYREQUIREMENTSET					wcars
			on wcars.ACTIVITY = wcpra.ACTIVITY
			and wcars.PARTITION = wcpra.PARTITION
		join WRKCTRACTIVITYREQUIREMENT						wcar
			on wcar.ACTIVITYREQUIREMENTSET = wcars.RECID
			and wcar.PARTITION = wcars.PARTITION
		join WRKCTRACTIVITYRESOURCEGROUPREQUIREMENT			wcargr
			on wcargr.ACTIVITYREQUIREMENT = wcar.RECID
			and wcargr.PARTITION = wcar.PARTITION
		join WRKCTRRESOURCEGROUP							wcrg
			on wcrg.RECID = wcargr.RESOURCEGROUP
			and wcrg.WRKCTRID != 'RZZZZ'
			and wcrg.DATAAREAID = pr1.DATAAREAID
			and wcrg.PARTITION = pr1.PARTITION
		left outer join PALDC_PMAX_MESTRANS					mt
			on mt.PRODROUTERECID = pr1.RECID
			and mt.DATAAREAID = pr1.DATAAREAID
			and mt.PARTITION = pr1.PARTITION
		outer apply (	select	sum(pjr.QTYGOOD) DEPOSITEDQTY,
								max(pjr.OPRFINISHED) OPRFINISHED
						from PRODJOURNALROUTE pjr
							where pr1.PRODID = pjr.PRODID
							and pr1.OPRNUM = pjr.OPRNUM
							and pr1.OPRPRIORITY = pjr.OPRPRIORITY
							and pr1.DATAAREAID = pjr.DATAAREAID
							and pr1.PARTITION = pjr.PARTITION
						group by	pjr.PRODID,
									pjr.OPRNUM,
									pjr.OPRPRIORITY) GROUPEDPJR
		group by	pt1.PRODID,
					pt1.QTYSCHED)												asts
	on asts.PRODID = pt.PRODID

outer apply (	select top(1) pjr.TRANSDATE DATES
				from PRODJOURNALROUTE pjr
				join PRODJOURNALTABLE pjt
					on pjt.JOURNALID = pjr.JOURNALID
					and pjt.DATAAREAID = pjr.DATAAREAID
					and pjt.PARTITION = pjr.PARTITION
				where pjr.PRODID = pt.PRODID
				order by pjr.TRANSDATE)									        firstdate

outer apply (	select top(1)	pjr.TRANSDATE DATES,
								pjr.OPRNUM,
								pjr.WRKCTRID
				from PRODJOURNALROUTE pjr
				join PRODJOURNALTABLE pjt
					on pjt.JOURNALID = pjr.JOURNALID
					and pjt.DATAAREAID = pjr.DATAAREAID
					and pjt.PARTITION = pjr.PARTITION
				where pjr.PRODID = pt.PRODID
				order by pjr.TRANSDATE DESC)									lastdate

where PT.PRODID not like '0r%'
and PRODADVANCESTATUS = 2
and PRODSTATUS != 7
and PRODSTATUS != 5
and PT.DATAAREAID = 'PAL'
and PT.PARTITION = 5637144576
order by LASTDATE