How to avoid NOT IN in query

I guess the cascading nesting SELECT is more culprit than the NOT.

Without an idea of what is in your bases and what are the relations between them, it will be complicated to give you better advice.

I take it that in your query the first alias should be a instead of vp. If that's correct the query looks like

SELECT * FROM MFS_CM_VISUAL_PAYMENT a
WHERE a.APPROVAL_STATUS = 'A' 
AND (a.PURCHASE_ORDER_ID NOT IN
        (SELECT ISNULL(VPL.DOCUMENT_ID, 0) AS Expr1
        FROM dbo.MFS_CM_VISUAL_PAYMENT AS VP INNER JOIN
        dbo.MFS_CM_VISUAL_PAYMENT_LINE AS VPL ON VP.VISUAL_PAYMENT_ID = VPL.VISUAL_PAYMENT_ID
        WHERE (VP.STATUS <> 'R') AND (VP.IS_VOID = 'N') AND (VPL.DOCUMENT_TYPE = 'PO'))
    )
    AND (a.PURCHASE_ORDER_NBR NOT IN
        (SELECT ISNULL(GRN.SOURCE_DOC_NO, 0) AS Expr1
        FROM    dbo.MFS_INV_GOOD_RECEIPT AS GRN INNER JOIN
        dbo.MFS_INV_GOOD_RECEIPT_ITEM AS GRNI ON GRN.RECEIPT_ID = GRNI.RECEIPT_ID
       WHERE (GRN.STATUS <> 'R') AND (GRN.VOID_IND = 0) AND (GRN.SOURCE_TYPE = 'SUP')))

So to the optimization. Very often the key thing isn't how you write the query but what are the underlying access paths to the data. You didn't provide any info on the indexes so based on the query I'd try adding the following indexes if not already present:

• Index on MFS_CM_VISUAL_PAYMENT_LINE:
  Columns (Document_Id, Document_Type, Visual_Payment_Id)
• Index on MFS_CM_VISUAL_PAYMENT:
  Columns (Visual_Payment_Id, Is_Void, Status)
• Index on MFS_INV_GOOD_RECEIPT:
  Columns (Source_Doc_No, Void_Ind, Source_Type, Status, Receipt_Id)
• Index on MFS_INV_GOOD_RECEIPT_ITEM:
  Columns (Receipt_ID)
• Indexes on MFS_CM_VISUAL_PAYMENT:
  Columns (APPROVAL_STATUS) if the amount of statuses A is very small compared to the number of all rows

Depending on the cardinality of each column the order may need to be changed.

Now what comes to the query, if both of the sub queries can have multiple rows with same returned key I would use NOT EXISTS instead of IN. Also I would eliminate all rows having NULL in the returned keys since you replace all of those with static value 0.

Based on those the query could look something like:

SELECT * 
FROM MFS_CM_VISUAL_PAYMENT a
WHERE a.APPROVAL_STATUS    = 'A' 
AND   a.PURCHASE_ORDER_ID  <> 0 -- Can be removed if 0 cannot exist in the data
AND   a.PURCHASE_ORDER_NBR <> 0 -- Can be removed if 0 cannot exist in the data
AND   NOT EXISTS ( SELECT 1
                   FROM dbo.MFS_CM_VISUAL_PAYMENT AS VP 
                   INNER JOIN dbo.MFS_CM_VISUAL_PAYMENT_LINE AS VPL 
                   ON VP.VISUAL_PAYMENT_ID = VPL.VISUAL_PAYMENT_ID
                   WHERE VP.STATUS       <> 'R' 
                   AND VP.IS_VOID        = 'N'
                   AND VPL.DOCUMENT_TYPE = 'PO'
                   AND VPL.DOCUMENT_ID   IS NOT NULL
                   AND VPL.DOCUMENT_ID   = a.PURCHASE_ORDER_ID)
AND   NOT EXISTS ( SELECT 1
                   FROM dbo.MFS_INV_GOOD_RECEIPT AS GRN 
                   INNER JOIN dbo.MFS_INV_GOOD_RECEIPT_ITEM AS GRNI 
                   ON GRN.RECEIPT_ID = GRNI.RECEIPT_ID
                   WHERE GRN.STATUS      <> 'R' 
                   AND GRN.VOID_IND      = 0
                   AND GRN.SOURCE_TYPE   = 'SUP'
                   AND GRN.SOURCE_DOC_NO IS NOT NULL
                   AND GRN.SOURCE_DOC_NO = a.PURCHASE_ORDER_NBR)

As said the performance will depend highly on the cardinalities, indexes and the access plan SQL server chooses so these are suggestions you can try.