How to See Deleted Voucher Num in AP ( AP Doc Seq Audit)

http://aporaclepayables.blogspot.com/2016/10/how-to-check-deleted-invoice-document.html

Deleted Invoice Voucher Num

select dsa.SET_OF_BOOKS_ID,aud.doc_sequence_value,
       aud.doc_sequence_id,
       aud.creation_date,
       fu.USER_NAME,
       decode(i.doc_sequence_value, null, 'Deleted', '') status,
       dsc.name category,
       -- v.segment1 vendor_num,
       --hp.party_name,
       i.invoice_num,
       i.invoice_currency_code,
       i.invoice_amount,
       dsa.CATEGORY_CODE
       --l.displayed_field paid
  from --ap_lookup_codes              l,
       ap_doc_sequence_audit        aud,
       fnd_doc_sequence_assignments dsa,
       fnd_doc_sequence_categories  dsc,
       --   hz_parties hp,
       --   ap_suppliers v,
       ap_invoices_all i,
       fnd_user fu
 where 1=1 --aud.doc_sequence_id = :C_sequence_id --Commented
   and aud.doc_sequence_id = i.doc_sequence_id(+)
   and aud.doc_sequence_value = i.doc_sequence_value(+)
 --  and aud.doc_sequence_value between :P_sequence_from and :P_sequence_to -Commented
   and aud.doc_sequence_assignment_id = dsa.doc_sequence_assignment_id
   and dsa.category_code = dsc.code
   and dsa.application_id = dsc.application_id
   and fu.USER_ID=aud.CREATED_BY
  -- and i.party_id = hp.party_id(+)
 --  and hp.party_id = v.party_id(+)
 --   and l.lookup_type(+) = 'INVOICE PAYMENT STATUS'
 --  and l.lookup_code(+) = i.payment_status_flag
   and i.doc_sequence_value is null
   and dsc.NAME like '%Invoices%'
   and dsa.SET_OF_BOOKS_ID = NVL('&Ledger_id',DSA.SET_OF_BOOKS_ID)
 order by aud.doc_sequence_value

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

Deleted Payments Voucher Num

select dsa.SET_OF_BOOKS_ID,aud.doc_sequence_value,
           aud.doc_sequence_id,
           aud.creation_date,
           fu.USER_NAME,
decode(i.doc_sequence_value,null,'Deleted','') status,
            dsc.name category
         --   v.segment1 vendor_num,
            --hp.party_name,
            --i.invoice_num,
            --i.invoice_currency_code,
            --i.invoice_amount,
            --l.displayed_field paid
from   -- ap_lookup_codes l,
            ap_doc_sequence_audit aud,
            fnd_doc_sequence_assignments dsa,
            fnd_doc_sequence_categories dsc,
          --  hz_parties hp,
         --   ap_suppliers v,
            ap_checks_all i,
            FND_User fu
where
-- aud.doc_sequence_id = :C_sequence_id
    aud.doc_sequence_id = i.doc_sequence_id(+)
and     aud.doc_sequence_value =                 i.doc_sequence_value(+)
--and     aud.doc_sequence_value between :P_sequence_from and :P_sequence_to
and     aud.doc_sequence_assignment_id = dsa.doc_sequence_assignment_id
and     dsa.category_code = dsc.code
and     dsa.application_id = dsc.application_id
and aud.CREATED_BY=fu.USER_ID
--and     i.party_id = hp.party_id (+)
--and     hp.party_id = v.party_id(+)
--and     l.lookup_type(+) = 'INVOICE PAYMENT STATUS'
--and     l.lookup_code(+) = i.payment_status_flag
and dsc.NAME like '%Payments%'
and i.DOC_SEQUENCE_VALUE is null
and dsa.SET_OF_BOOKS_ID=NVL('&Ledger_id',dsa.SET_OF_BOOKS_ID)
order by aud.doc_sequence_value

How to Restrict Tax Code on Invoice Line Level & Distribution Level (Site Wise or Location Wise)

http://aporaclepayables.blogspot.com/2016/08/blog-post.html

How to Restrict Tax Code on Invoice Line Level (Site Wise or Location Wise)

select g.name name, g.group_id group_id, description

  from ap_awt_groups g

 where exists

 (select 1 from ap_awt_group_taxes t where g.group_id = t.group_id

 and  t.TAX_NAME  in  ( select tc.NAME

 from ap_tax_codes_all tc , ap_supplier_sites_all st

 where  tc.ORG_ID = st.ORG_ID

 and  tc.AWT_VENDOR_SITE_ID =  st.VENDOR_SITE_ID

 and  st.VENDOR_SITE_CODE  =  :INV_SUM_FOLDER.VENDOR_SITE_CODE

 and   tc.ORG_ID = FND_PROFILE.VALUE('ORG_ID')  )

 )

   and nvl(inactive_date, sysdate + 1) > sysdate

 order by name

How to Restrict Tax Code on Invoice Distribution Level (Site Wise or Location Wise)

How to Default Final Post Radio Button In Payables

http://aporaclepayables.blogspot.com/2016/08/how-to-default-final-post-radio-button.html

Form Personalization: Disable Create Accouting > Final Post

http://aporaclepayables.blogspot.com/2016/07/form-personalization-disable-create.html

Please Copy Paste the Below URL in your Browser

http://orclapp.blogspot.com/2013/05/form-personalization-disable-create.html

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

AP Header, Line and Distribution GL Period same (Query Check)

http://aporaclepayables.blogspot.com/2016/07/ap-header-and-distribution-gl-date-same.html

Invoice Header and Distrubution

select pp.LINE_TYPE_LOOKUP_CODE,
       kk.DOC_sequence_value,
       kk.INVOICE_NUM,
       kk.ORG_ID,
       kk.GL_DATE,
       kk.INVOICE_ID,
       kk.Header_period,
       pp.Distribution_period,
       kk.CANCELLED_DATE
  from (select aia.DOC_sequence_value,
               aia.INVOICE_NUM,
               aia.ORG_ID,
               aia.GL_DATE,
               aia.INVOICE_ID, aia.cancelled_date,
               to_char(aia.GL_DATE, 'MON-YY') Header_period
          from ap_invoices_all aia) kk,
       (select aida.LINE_TYPE_LOOKUP_CODE,
               aida.ACCOUNTING_DATE,
               aida.INVOICE_ID,
               to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
          from ap_invoice_distributions_all aida) pp
 where kk.invoice_id = pp.invoice_id
   and Header_period <> Distribution_period
   and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
   and kk.CANCELLED_DATE is null
   and kk.gl_date > '01-JAN-16'

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

Invoice Header, Line and Distrubution

select pp.LINE_TYPE_LOOKUP_CODE,
       kk.DOC_sequence_value,
       kk.INVOICE_NUM,
       kk.ORG_ID,
       kk.GL_DATE,
       kk.INVOICE_ID,
       kk.Header_period,
       ll.Line_period,
       pp.Distribution_period,
       kk.CANCELLED_DATE
  from (select aia.DOC_sequence_value,
               aia.INVOICE_NUM,
               aia.ORG_ID,
               aia.GL_DATE,
               aia.INVOICE_ID,
               aia.cancelled_date,
               to_char(aia.GL_DATE, 'MON-YY') Header_period
          from ap_invoices_all aia) kk,
       (select aida.LINE_TYPE_LOOKUP_CODE,
               aida.ACCOUNTING_DATE,
               aida.INVOICE_ID,
               aida.invoice_line_number,
               to_char(aida.ACCOUNTING_DATE, 'MON-YY') Distribution_period
          from ap_invoice_distributions_all aida) pp,
       (select aila.invoice_id,
               aila.line_number,
               to_char(aila.accounting_DATE, 'MON-YY') Line_period
          from ap_invoice_lines_all aila) ll
 where kk.invoice_id = pp.invoice_id
   and kk.invoice_id = ll.invoice_id
   and ll.invoice_id = pp.invoice_id
   and ll.line_number = pp.invoice_line_number
   and (Header_period <> Distribution_period or
       Header_period <> Line_period or Line_period <> Distribution_period)
   and pp.LINE_TYPE_LOOKUP_CODE in ('ITEM', 'MISCELLANEOUS')
   and kk.CANCELLED_DATE is null
   and kk.gl_date > '01-JAN-16'
   --and kk.org_id = 185

AP: Supplier Site DFF Data Not Saved

http://aporaclepayables.blogspot.com/2016/06/ap-supplier-site-dff-data-not-saved.html

R12 AP: Supplier Site DFF Data Not Saved (Doc ID 1571273.1)