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)