I’m having an issue with the below query with either filtering out rows that are needed, or including extra rows that aren’t desired (Depending on whether or not I use the OR condition below). I am LEFT OUTER JOINing PO_ATTRIBUTE_VALUES as their are some cases where there are some rows in PO_ATTRIBUTE_VALUES that are not in PO_LINES_ARCHIVE_ALL. The issue is in the final WHERE clause where I only return rows from PO_LINES_ARCHIVE_ALL LN with a LAST_UPDATE_DATE within the last two days, OR where the LAST_UPDATE_DATE from PO_ATTRIBUTE_VALUES is within the last two days. If I don’t use the ‘OR` condition then I am filtering out rows that are needed that are in the Outer joined table.

The issue then becomes when I use the OR criteria then the results are returning back additional rows from PO_ATTRIBUTE_VALUES that aren’t joined to PO_LINES_ARCHIVE_ALL (because of the LEFT OUTER JOIN and within the date range). How can I handle this situation?

(SELECT 'LINE_UPDATE' UPDATE_TYPE, LN.PO_HEADER_ID , LN.PO_LINE_ID , LN.LAST_UPDATE_DATE, LN.UNIT_PRICE, LN.VENDOR_PRODUCT_NUM, LN.UOM_CODE, ATTR.MANUFACTURER_PART_NUM  , TLP.MANUFACTURER
 , LN_OLD.LAST_UPDATE_DATE LN_OLD_LAST_UPDATE_DT, ETP.TP_ITEM_NUMBER OLD_MFG_PART,    LN_OLD.MANUFACTURER OLD_MFG     
 , LN_OLD.VENDOR_PRODUCT_NUM OLD_VENDOR_ITEM, 
 LN_OLD.UNIT_PRICE OLD_UNIT_PRICE, LN_OLD.UOM_CODE OLD_UOM_CODE, LN.ITEM_ID, ATTR.CREATION_DATE MFG_CREATION_DATE, ATTR.LAST_UPDATE_DATE MFG_LAST_UPDATE_DATE
FROM PO_LINES_ARCHIVE_ALL LN

LEFT OUTER JOIN EGP_SYSTEM_ITEMS ITM ON ITM.INVENTORY_ITEM_ID = LN.ITEM_ID AND ITM.ORGANIZATION_ID = 300000010136542 --Master Org.
LEFT OUTER JOIN EGP_ITEM_RELATIONSHIPS_B EIR ON EIR.INVENTORY_ITEM_ID = LN.ITEM_ID
                  AND EIR.ITEM_RELATIONSHIP_TYPE = 'MFG_PART_NUM'
LEFT OUTER JOIN EGP_TRADING_PARTNER_ITEMS ETP ON ETP.TP_ITEM_ID = EIR.TP_ITEM_ID
                  AND ETP.TP_TYPE = 'MANUFACTURER'
LEFT OUTER JOIN PO_ATTRIBUTE_VALUES ATTR ON ATTR.PO_HEADER_ID = LN.PO_HEADER_ID AND ATTR.PO_LINE_ID = LN.PO_LINE_ID   AND ATTR.LAST_UPDATE_DATE > SYSDATE - 22 AND ATTR.MANUFACTURER_PART_NUM <> ETP.TP_ITEM_NUMBER
LEFT OUTER JOIN PO_ATTRIBUTE_VALUES_TLP TLP ON TLP.PO_HEADER_ID = LN.PO_HEADER_ID AND TLP.PO_LINE_ID = LN.PO_LINE_ID  AND TLP.LAST_UPDATE_DATE > SYSDATE - 22

LEFT OUTER JOIN PO_LINES_ARCHIVE_ALL LN_OLD ON LN_OLD.PO_HEADER_ID = LN.PO_HEADER_ID AND LN_OLD.PO_LINE_ID = LN.PO_LINE_ID                      

WHERE (LN.LAST_UPDATE_DATE > SYSDATE - 2 )) --OR ATTR.LAST_UPDATE_DATE > SYSDATE - 2))