CREATE OR REPLACE PROCEDURE inventory.sp_suggestedorderexport_test(var_fromdate varchar, var_todate varchar)
    LANGUAGE plpgsql
AS $$
declare
    var_SystemAutoReceive int;
    var_UserAutoReceive int;
BEGIN
    var_SystemAutoReceive:= 8;
    var_UserAutoReceive:= 256;

    execute 'DROP TABLE IF EXISTS suggested_order_test_table';
    execute 'CREATE TABLE suggested_order_test_table 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,
    ipol.inventorypurchaseorderlineid AS lineid
    FROM inform.public.inventorypurchaseorder ipo
	LEFT JOIN inform.public.inventorypurchaseorderline ipol ON ipol.inventorypurchaseorderid = ipo.inventorypurchaseorderid
 	JOIN inform.public.inventorysalesorder iso  ON ipo.inventorysalesorderid = iso.inventorysalesorderid
  	JOIN https://protect-eu.mimecast.com/s/D4JjC82gNCz1Yglinz2Tc?domain=inform.public.store s  ON ipo.storeid = s.storeid
  	JOIN inform.public.vendor v  ON v.vendorid = ipo.vendorid
	LEFT JOIN inform.public.inventorysalesorderline isol ON isol.inventorysalesorderid = iso.inventorysalesorderid AND ipol.vendorinventoryitemcode = isol.vendorinventoryitemcode
	LEFT JOIN inform.public.inventorysalesorderlineitemquantity isoliq ON isoliq.inventorysalesorderlineid = isol.inventorysalesorderlineid
	LEFT JOIN inform.public.inventoryitem ii ON ii.inventoryitemid = ipol.inventoryitemid
	LEFT JOIN inform.public.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,
    ipol.inventorypurchaseorderlineid AS lineid
    FROM inform.public.inventorysalesorder iso
	LEFT JOIN inform.public.inventorysalesorderline isol
		ON isol.inventorysalesorderid = iso.inventorysalesorderid
	LEFT JOIN inform.public.inventorypurchaseorder ipo
		ON ipo.inventorysalesorderid = iso.inventorysalesorderid AND ipo.orderdate >= CONVERT(TIMESTAMP, ''' || var_fromDate || ''') AND ipo.orderdate <= CONVERT(TIMESTAMP, ''' || var_todate || ''')
	JOIN https://protect-eu.mimecast.com/s/D4JjC82gNCz1Yglinz2Tc?domain=inform.public.store s
		ON ipo.storeid = s.storeid
	JOIN inform.public.vendor v
		ON v.vendorid = ipo.vendorid
	LEFT JOIN inform.public.inventorypurchaseorderline ipol
		ON ipol.inventorypurchaseorderid = ipo.inventorypurchaseorderid AND ipol.vendorinventoryitemcode = isol.vendorinventoryitemcode
	LEFT JOIN inform.public.inventorysalesorderlineitemquantity isoliq
		ON isoliq.inventorysalesorderlineid = isol.inventorysalesorderlineid
	LEFT JOIN inform.public.inventoryitem ii
		ON ii.inventoryitemid = COALESCE(ipol.inventoryitemid, isol.itemid)
	LEFT JOIN inform.public.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
$$;

CALL inventory.sp_suggestedorderexport_test('2022-11-04T00:30:00.083424', '2022-11-04T16:30:00.083424');

select count(*) from suggested_order_test_table where lineid is null;