To check Unaccounted Invoices (Query)

http://aporaclepayables.blogspot.com/2016/07/to-check-unaccounted-invoices-query.html

select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, tt.approval_status_lookup_code
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=80
order by tt.doc_sequence_value desc

-----

begin
mo_global.set_policy_context('S',85);
end;


select distinct tt.doc_sequence_value, tt.invoice_num, tt.gl_date, tt.invoice_amount, --tt.approval_status_lookup_code
DECODE
             (tt.approval_status_lookup_code
                                                  ,
              'NEVER APPROVED', 'Never Validated',
              'NEEDS REAPPROVAL', 'Needs Revalidation',
              'APPROVED', 'Validated',
              'CANCELLED', 'Cancelled',
              'AVAILABLE PREPAYMENT', 'Available Prepayment',
              'AVAILABLE', 'Available'
       
              ) invoice_status
from ap_invoices_V tt, ap_invoice_distributions_all kk
where tt.invoice_id=kk.INVOICE_ID(+)
and tt.posting_flag!='Y'
and
(tt.approval_status_lookup_code in ('CANCELLED')
and kk.DISTRIBUTION_LINE_NUMBER is not null
or tt.approval_status_lookup_code in ('NEVER APPROVED','APPROVED','NEEDS REAPPROVAL')
)
and tt.org_id=85
order by tt.doc_sequence_value desc

--------------------------------------------------------------------------------------------------------------------------

For My Case When Withholding Deduction is at Payment Time

/*
select  *
from   xla_distribution_links  l
where  l.AE_HEADER_ID   =   341206*/

select
/* aeh.ae_header_id , l.ae_header_id ,
l.applied_to_source_id_num_1 , i.invoice_id */
i.invoice_id,
i.invoice_num,  --
i.invoice_date,
i.gl_date, --
i.invoice_amount, --
i.doc_sequence_value APN_NUMBER,  --
i.terms_date APN_DATE, --
i.payment_currency_code,
hsv.vendor_id,
hsv.vendor_name,    --
hsv.vendor_number,  --
'N' gl_transfer_status_code,
null gl_transfer_date,
U.USER_NAME,
ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) STATUS --

from
ap_invoices_all i,
   
HUBPO_SUPPLIERS_V HSV,
fnd_user u
where
i.org_id=  :P_ORG_ID  ---  added IACS( imran )   24-JULY-2015  --


and
(
(
--================= Unposted DIST PRESENT =====================
exists (
select 1
from  ap_invoice_distributions_all  d
where

d.INVOICE_ID  = i.invoice_id and
 d.LINE_TYPE_LOOKUP_CODE != 'AWT' and
 nvl(d.ACCRUAL_POSTED_FLAG , 'N') = 'N'

 )  and
 --=================AND  DIST NOT PRESENT IF CANCELLED INVOICE=====================
  i.INVOICE_ID   not  in
 (
select aia.INVOICE_ID  from  ap_invoices_all  aia
where
 not exists ( select 1 from  ap_invoice_distributions_all  d where d.INVOICE_ID = aia.INVOICE_ID  )
 and    Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
       I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
       I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
       I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
       I.INVOICE_TYPE_LOOKUP_CODE))  =  'Cancelled'
 )
 )
 OR
 --================= OR DIST Not PRESENT =====================
(
  not exists
 (
  select 1 from  ap_invoice_distributions_all  d
  where d.INVOICE_ID = I.INVOICE_ID

 )
 and    Decode(AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
       I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
       I.INVOICE_TYPE_LOOKUP_CODE),'CANCELLED','Cancelled','NEEDS REAPPROVAL','Needs Revalidation','APPROVED','Validated','NEVER APPROVED','Never Validated',AP_INVOICES_PKG.GET_APPROVAL_STATUS( I.INVOICE_ID,
       I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG,
       I.INVOICE_TYPE_LOOKUP_CODE))  !=  'Cancelled'
 )


 )

 and

i.VENDOR_ID      = HSV.VENDOR_ID and
i.vendor_site_id    = HSV.vendor_site_id and

-- i.gl_date between :P_DATE1 and :P_DATE2  AND
-- i.gl_date between trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH') AND LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')) AND
i.gl_date between NVL(:P_DATE1,i.gl_date) and NVL(:P_DATE2,i.gl_date) and
i.gl_date between NVL(trunc(TO_DATE(:P_PERIOD,'MON-RR'),'MONTH'),i.gl_date) AND NVL(LAST_DAY(TO_DATE(:P_PERIOD,'MON-RR')),i.gl_date) AND
hsv.vendor_name=nvl(:P_VENDER_NAME,hsv.vendor_name)
--i.DOC_SEQUENCE_VALUE  =    '16000633'  -- 12
and u.USER_ID=i.CREATED_BY
and U.user_id=NVL(:P_USER_ID,U.USER_ID)
order by  i.doc_sequence_value