CREATE OR REPLACE PROCEDURE public.sp_suggestedorderexport(var_fromdate varchar, var_todate varchar, var_filename varchar)
	LANGUAGE plpgsql
AS $$	
declare
	var_SystemAutoReceive int;
	var_UserAutoReceive int;	
BEGIN	
	var_SystemAutoReceive:= 8;
	var_UserAutoReceive:= 256;
		
	execute 'DROP TABLE IF EXISTS ' || var_filename;
	execute 'CREATE TEMP TABLE ' || var_filename || ' AS 	
	SELECT 
		ipo.inventorypurchaseorderid ordernumber,
		ipo.inventorysalesorderid salesordernumber,
		Case When ipo.createdfromorder = 0 THEN NULL ELSE ipo.createdfromorder END originalordernumber,
		s.storenumber storenumber, 		    	
		ipo.orderdate date, 		
		ipo.vendorname vendor,
		v.vendornumber vendornumber,
		ipo.orderdate dateplaced,
		ipo.dispatchedddate dateshipped,
		ipo.receivedate datereceived,
		ipo.applieddate dateapplied,
		iso.entrydate datecreated,
		iso.coveruntildate coveruntil,
		CASE WHEN (((ipo.autostatus & ' || var_SystemAutoReceive || ') = ' || var_SystemAutoReceive || ') OR
				((ipo.autostatus & ' || var_UserAutoReceive || ') = ' || var_UserAutoReceive || '))
			THEN ''TRUE'' ELSE ''FALSE'' END autoreceived,
		CASE WHEN (ipo.receivedby = ''Receipt Advice'')  
            THEN ''TRUE'' ELSE ''FALSE'' END receiptadvice,
		CASE WHEN ipo.istatus_typ = 1 THEN
			''Placed'' 
		ELSE 
			CASE WHEN ipo.istatus_typ = 4 THEN
				''Received'' 
			ELSE 
				ipo.status
		END END orderstatus,
		CASE WHEN iso.suggestedorderingstatus = 0 THEN
			''Usage Ordering''
		ELSE 
			CASE WHEN iso.suggestedorderingstatus = 1 THEN
				''Suggested Values Used'' 
			ELSE 
				CASE WHEN iso.suggestedorderingstatus = 2 THEN
					''Suggested Values Auto Cleared'' 
				ELSE 
					CASE WHEN iso.suggestedorderingstatus = 3 THEN 
						''Values Manually Cleared'' 
					ELSE 
						''''
		END END END END suggestedstatus,
    ROUND(iso.forecastrange, 2) forecastrange,
    CASE WHEN ipo.dispatched = 1 
		THEN ipo.invoicenumber
		ELSE '''' 
    END distributorinvoicenumber,
    iso.createdby,
    iso.supplyorderauthorizedby placedby,
    CASE WHEN ipo.istatus_typ = 4 THEN ipo.receivedby ELSE NULL END receivedby,
    CASE WHEN ipo.istatus_typ = 6 THEN ipo.lastupdatedby ELSE NULL END canceledby,
	CASE WHEN ipol.vendorinventoryitemcode IS NULL THEN isol.vendorinventoryitemcode ELSE ipol.vendorinventoryitemcode END AS itemcode,
	CASE WHEN ipol.inventoryitemdescription IS NULL THEN isol.description ELSE ipol.inventoryitemdescription END AS description,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isol.forecastquantity, 2) ELSE NULL END AS forecastusage,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isol.safetystock / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS safetystock,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isoliq.onhandqty / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS onhand,
	CASE WHEN isoliq.issuggesteditem = 1 AND isoliq.onhandqtyoverridevalue IS NOT NULL 
		THEN ROUND(isoliq.onhandqtyoverridevalue / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS onhandusedincalculation,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isoliq.onorderqty / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS onorder,
	CASE WHEN isoliq.issuggesteditem = 1 THEN isoliq.suggestedqty ELSE NULL END AS suggestedqty,
	CASE WHEN ipol.orderedquantity IS NULL THEN 0 ELSE ipol.orderedquantity END AS orderedqty,
	CASE WHEN ipo.receivedshippingnotification = 1 THEN ipol.vendorshippedquantity END AS shippedqty,
	ipol.deliveredquantity AS receivedqty,
	ipol.originalreceivedqty AS originalreceivedqty,
	isoliq.issuggesteditem, 
	isoliq.defaultorderquantity,
	isoliq.remainingallocationqty,
	CASE WHEN ipol.processingtime = 0 THEN NULL ELSE ipol.processingtime END AS processingtime,
	ipo.lastupdatedate
	FROM inventorypurchaseorder ipo 
	LEFT JOIN inventorypurchaseorderline ipol ON ipol.inventorypurchaseorderid = ipo.inventorypurchaseorderid
 	JOIN inventorysalesorder iso  ON ipo.inventorysalesorderid = iso.inventorysalesorderid
  	JOIN store s  ON ipo.storeid = s.storeid
  	JOIN vendor v  ON v.vendorid = ipo.vendorid
	LEFT JOIN inventorysalesorderline isol ON isol.inventorysalesorderid = iso.inventorysalesorderid AND ipol.vendorinventoryitemcode = isol.vendorinventoryitemcode
	LEFT JOIN inventorysalesorderlineitemquantity isoliq ON isoliq.inventorysalesorderlineid = isol.inventorysalesorderlineid
	LEFT JOIN inventoryitem ii ON ii.inventoryitemid = ipol.inventoryitemid
	LEFT JOIN unitofmeasureconversion uomc ON uomc.fromunitofmeasureid = ii.purchaseunitid AND uomc.tounitofmeasureid = ii.baseinventoryunitofmeasureid
	WHERE ipo.lastupdatedate >= CONVERT(TIMESTAMP, ''' || var_fromDate || ''') AND ipo.lastupdatedate <= CONVERT(TIMESTAMP, ''' || var_todate || ''')
	
	UNION
	
	SELECT 
	ipo.inventorypurchaseorderid ordernumber,
	ipo.inventorysalesorderid salesordernumber,
	CASE WHEN ipo.createdfromorder = 0 THEN NULL ELSE ipo.createdfromorder END originalordernumber,
	s.storenumber storenumber, 		    	
	ipo.orderdate date, 		
	ipo.vendorname vendor,
	v.vendornumber vendornumber,
	ipo.orderdate dateplaced,
	ipo.dispatchedddate dateshipped,
	ipo.receivedate datereceived,
	ipo.applieddate dateapplied,
	iso.entrydate datecreated,
	iso.coveruntildate coveruntil,
		CASE WHEN (((ipo.autostatus & ' || var_SystemAutoReceive || ') = ' || var_SystemAutoReceive || ') OR
			((ipo.autostatus & ' || var_UserAutoReceive || ') = ' || var_UserAutoReceive || '))
		THEN ''TRUE'' ELSE ''FALSE'' END autoreceived,
	CASE WHEN (ipo.receivedby = ''Receipt Advice'')  
           THEN ''TRUE'' ELSE ''FALSE'' END receiptadvice,
		CASE WHEN ipo.istatus_typ = 1 THEN
		''Placed'' 
	ELSE 
		CASE WHEN ipo.istatus_typ = 4 THEN
			''Received'' 
			ELSE 
			ipo.status
	END END orderstatus,
	CASE WHEN iso.suggestedorderingstatus = 0 THEN
		''Usage Ordering''
	ELSE 
		CASE WHEN iso.suggestedorderingstatus = 1 THEN
			''Suggested Values Used'' 
		ELSE 
			CASE WHEN iso.suggestedorderingstatus = 2 THEN
				''Suggested Values Auto Cleared'' 
			ELSE 
				CASE WHEN iso.suggestedorderingstatus = 3 THEN 
					''Values Manually Cleared'' 
				ELSE 
					''''
	END END END END suggestedstatus,
    ROUND(iso.forecastrange, 2) forecastrange,
    CASE WHEN ipo.dispatched = 1 
		THEN ipo.invoicenumber
		ELSE '''' 
    END distributorinvoicenumber,
    iso.createdby,
    iso.supplyorderauthorizedby placedby,
    CASE WHEN ipo.istatus_typ = 4 THEN ipo.receivedby ELSE NULL END receivedby,
    CASE WHEN ipo.istatus_typ = 6 THEN ipo.lastupdatedby ELSE NULL END canceledby,
	CASE WHEN ipol.vendorinventoryitemcode IS NULL THEN isol.vendorinventoryitemcode ELSE ipol.vendorinventoryitemcode END AS itemcode,
	CASE WHEN ipol.inventoryitemdescription IS NULL THEN isol.description ELSE ipol.inventoryitemdescription END AS description,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isol.ForecastQuantity, 2) ELSE NULL END AS forecastusage,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isol.safetystock / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS safetystock,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isoliq.onhandqty / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS onhand,
	CASE WHEN isoliq.issuggesteditem = 1 AND isoliq.onhandqtyoverridevalue IS NOT NULL 
		THEN ROUND(isoliq.onhandqtyoverridevalue / ISNULL(uomc.conversionrate,1),2) ELSE NULL END AS onhandusedincalculation,
	CASE WHEN isoliq.issuggesteditem = 1 THEN ROUND(isoliq.OnOrderQty / ISNULL(uomc.ConversionRate,1),2) ELSE NULL END AS onorder,
	CASE WHEN isoliq.issuggesteditem = 1 THEN isoliq.suggestedqty ELSE NULL END AS suggestedqty,
	CASE WHEN ipol.orderedquantity IS NULL THEN 0 ELSE ipol.orderedquantity END AS orderedqty,
	CASE WHEN ipo.receivedshippingnotification = 1 THEN ipol.vendorshippedquantity END AS shippedqty,
	ipol.deliveredquantity AS receivedqty,
	ipol.originalreceivedqty AS originalreceivedqty,
	isoliq.issuggesteditem, 
	isoliq.defaultorderquantity,
	isoliq.remainingallocationqty,
	CASE WHEN ipol.processingtime = 0 THEN NULL ELSE ipol.processingtime END AS processingtime,
	ipo.lastupdatedate
	FROM inventorysalesorder iso	
	LEFT JOIN inventorysalesorderline isol 
		ON isol.inventorysalesorderid = iso.inventorysalesorderid
	LEFT JOIN inventorypurchaseorder ipo 
		ON ipo.inventorysalesorderid = iso.inventorysalesorderid AND ipo.orderdate >= CONVERT(TIMESTAMP, ''' || var_fromDate || ''') AND ipo.orderdate <= CONVERT(TIMESTAMP, ''' || var_todate || ''')
	JOIN store s  
		ON ipo.storeid = s.storeid 
	JOIN vendor v  
		ON v.vendorid = ipo.vendorid		
	LEFT JOIN inventorypurchaseorderline ipol 
		ON ipol.inventorypurchaseorderid = ipo.inventorypurchaseorderid AND ipol.vendorinventoryitemcode = isol.vendorinventoryitemcode
	LEFT JOIN inventorysalesorderlineitemquantity isoliq 
		ON isoliq.inventorysalesorderlineid = isol.inventorysalesorderlineid
	LEFT JOIN inventoryitem ii 
		ON ii.inventoryitemid = COALESCE(ipol.inventoryitemid, isol.itemid)
	LEFT JOIN unitofmeasureconversion uomc 
		ON uomc.fromunitofmeasureid = ii.purchaseunitid AND uomc.tounitofmeasureid = ii.baseinventoryunitofmeasureid
	WHERE ipo.lastupdatedate >= CONVERT(TIMESTAMP, ''' || var_fromDate || ''') AND ipo.lastupdatedate <= CONVERT(TIMESTAMP, ''' || var_todate || ''');';
end;
$$;
