FRM-40735 POST-insert trigger raised unhandeled expression exception ORA-12899 while saving New tax code

http://aporaclepayables.blogspot.com/2016/12/frm-40735-post-insert-trigger-raised.html

On R&D it’s found that Description allows 80 characters only & exceeding from this limit, system prompts the error message which we were facing. 

oracle.apps.iby.exception.PSException: Error: bad data - the instruction status is invalid.

http://aporaclepayables.blogspot.com/2016/11/oracleappsibyexceptionpsexception-error.html

+---------------------------------------------------------------------------+
Payments: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

IBY_FD_PAYMENT_FORMAT: Format Payment Instructions
+---------------------------------------------------------------------------+

Current system time is 14-NOV-2016 12:15:25

+---------------------------------------------------------------------------+

FDExtractAndFormatting->runProgram():TEST
RequestId:(TEST)1721435
Start time:(TEST) Mon Nov 14 12:15:28 PKT 2016
Before calling OraPmt.init()::(TEST)
After calling OraPmt.init()
Before setparams - m_instructionID : null
FDExtractAndFormatting->Setting Parameters
Enter: iby.scheduler.FDExtractAndFormatting.setParameters()
Param name: InstructionReferenceNumber value: 53725
Param name: ReprintFlag, value: N
FDExtractAndFormatting->Set Parameters ...
After setparams - m_instructionID : 53725
Enter: iby.scheduler.FDExtractAndFormatting.lockInstruction()
Executing BEGIN IBY_DISBURSE_UI_API_PUB_PKG.lock_pmt_entity(:1, :2, :3, :4); END;
Successfully executed PL/SQL.
Exit: iby.scheduler.FDExtractAndFormatting.lockInstruction()
After lockInstruction()
Before runmainlogic - m_instructionID : 53725
Enter: iby.scheduler.FDExtractAndFormatting.runMainLogic()
Enter: Mon Nov 14 12:15:29 PKT 2016
Before load FD profile - m_instructionID : 53725
After load FD profile - m_instructionID : 53725
Enter: iby.payment.FundDisbursementProfile.load
Preparing to load profile. instructionID := 53725
Found profile. parsing records.
Done parsing. Instruction records : FundDisbursementProfile{instructionID:=53725,instruction_status:=TERMINATED,instruction process_type:=IMMEDIATE,processing_type:=PRINTED,electronic_processing_channel:=null,transmit_immediate_flag:=N,print_immediate_flag:=N,printer_name:=null,payment_format:=Format{formatCode:=IBY_PAY_CHK_STANDARD_1,formatType:=OUTBOUND_PAYMENT_INSTRUCTION,templateCode:=IBYDC_STD1,defaultOutputType:=null,locale:=en_00,extract:=Extract{extractCode:=IBY_FD_PAYMENT_INSTRUCTION,extractVersion:=1}} ,transmit_configuration:=null ,default_output_type:=null }
Exit: iby.payment.FundDisbursementProfile.load
Payment process profile successfully loaded.
oracle.apps.iby.exception.PSException: Error: bad data - the instruction status is invalid.
 at oracle.apps.iby.scheduler.FDExtractAndFormatting.runMainLogic(FDExtractAndFormatting.java:350)
 at oracle.apps.iby.scheduler.FDExtractAndFormatting.runProgram(FDExtractAndFormatting.java:159)
 at oracle.apps.fnd.cp.request.Run.main(Run.java:156)

Error: bad data - the instruction status is invalid.
An error occurred. Roll back any changes.
Done roll back changes.
Request completed in error.
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
No completion options were requested.

Output file size: 
0

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 14-NOV-2016 12:15:29

+---------------------------------------------------------------------------+

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

This happens when User Creates Payment and in a second he Voids the Payment.

SELECT  *
  FROM   IBY_PAY_INSTRUCTIONS_ALL
 WHERE   payment_instruction_status = 'TERMINATED'
 order by creation_date desc

WHT Tax Code Information Query

http://aporaclepayables.blogspot.com/2016/11/wht-tax-code-information-query.html

Tax Codes

select   cc.NAME, mm.VENDOR_SITE_CODE, vv.TAX_RATE, vv.START_DATE, vv.END_DATE, vv.START_AMOUNT, vv.END_AMOUNT
, cc.DESCRIPTION, cc.ATTRIBUTE1 Section, cc.ATTRIBUTE2 Description
 from AP_AWT_TAX_RATES_ALL vv,  AP_TAX_CODES_ALL cc, po_vendor_sites_all mm
where vv.TAX_NAME=cc.NAME
and vv.ORG_ID=cc.ORG_ID
and mm.VENDOR_SITE_ID=cc.AWT_VENDOR_SITE_ID
and mm.ORG_ID=cc.ORG_ID
and vv.ORG_ID=82
and cc.ENABLED_FLAG='Y'
and vv.RATE_TYPE='STANDARD'
and vv.END_DATE is null
order by cc.NAME

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

Tax Groups

select * from AP_AWT_GROUPS kk, AP_AWT_TAX_RATES_ALL jj, AP_AWT_GROUP_TAXES_ALL xx
where 1=1
and jj.TAX_NAME=xx.TAX_NAME
and kk.GROUP_ID=xx.GROUP_ID
and jj.END_DATE is null
and jj.RATE_TYPE = 'STANDARD'
and xx.TAX_NAME in ('SS0','SS1','SS2','US1')

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

Tax Codes Location & Vendor Site

select tt.NAME, rr.SEGMENT2, oo.VENDOR_SITE_CODE, qq.TAX_RATE
  from AP_TAX_CODES_ALL      tt,
       gl_code_combinations  rr,
       ap_supplier_sites_all oo,
       AP_AWT_TAX_RATES_ALL  qq
 where tt.TAX_CODE_COMBINATION_ID = rr.CODE_COMBINATION_ID
   and tt.AWT_VENDOR_SITE_ID = oo.VENDOR_SITE_ID
   and qq.TAX_NAME = tt.NAME
   and tt.ORG_ID = oo.ORG_ID
   and oo.ORG_ID = 82
   and tt.NAME in ('PS0', 'PS1', 'UP1')

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

select kk.NAME Group_Name,
       kk.DESCRIPTION Group_description,
       jj.TAX_NAME Tax_Code,
       jj.TAX_RATE,
       jj.START_DATE,
       oo.VENDOR_SITE_CODE Site,
       ww.DESCRIPTION Tax_Name_Description,
       ww.ATTRIBUTE1 DFF_Section,
       ww.ATTRIBUTE2 DFF_Description,
       gc.SEGMENT1 Company,
       gc.SEGMENT2 Location,
       gc.SEGMENT4 Account

  from AP_AWT_GROUPS          kk,
       AP_AWT_TAX_RATES_ALL   jj,
       AP_AWT_GROUP_TAXES_ALL xx,
       ap_supplier_sites_all  oo,
       AP_TAX_CODES_ALL       ww,
       gl_code_combinations   gc
 where 1 = 1
   and jj.TAX_NAME = xx.TAX_NAME
   and kk.GROUP_ID = xx.GROUP_ID
   and jj.ORG_ID = xx.ORG_ID
   and ww.NAME = jj.TAX_NAME
   and oo.VENDOR_SITE_ID = ww.AWT_VENDOR_SITE_ID
   and gc.CODE_COMBINATION_ID = ww.TAX_CODE_COMBINATION_ID
   and ww.ORG_ID = jj.ORG_ID
   and xx.ORG_ID = jj.ORG_ID
   and jj.END_DATE is null
   and jj.RATE_TYPE = 'STANDARD'
   and kk.CREATION_DATE > '27-OCT-2016'
   and kk.INACTIVE_DATE is null
 order by jj.ORG_ID, kk.NAME
--and xx.TAX_NAME in ('SS0','SS1','SS2','US1')

To check Bank Account GL_Code_Combination Assigned

http://aporaclepayables.blogspot.com/2016/10/to-check-bank-location.html

AP

select kk.BANK_ACCOUNT_NAME,
       kk.BANK_ACCOUNT_NUM,
       kk.BANK_ACCOUNT_ID,
       kk.AP_USE_ALLOWED_FLAG,
       gc1.SEGMENT2 General_Controls,
       gc2.SEGMENT2 AP_OPTIONS
  from ce_bank_accounts      kk,
       CE_BANK_ACCT_USES_ALL ll,
       CE_GL_ACCOUNTS_CCID   tt,
       gl_code_combinations  gc1,
       gl_code_combinations  gc2
 where kk.BANK_ACCOUNT_ID = ll.BANK_ACCOUNT_ID
   and ll.BANK_ACCT_USE_ID = tt.BANK_ACCT_USE_ID
   and kk.ASSET_CODE_COMBINATION_ID = gc1.CODE_COMBINATION_ID
   and tt.AP_ASSET_CCID = gc2.CODE_COMBINATION_ID
   and gc1.SEGMENT2!=gc2.SEGMENT2

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

AR

select kk.BANK_ACCOUNT_NAME,
       kk.BANK_ACCOUNT_NUM,
       kk.BANK_ACCOUNT_ID,
       kk.AR_USE_ALLOWED_FLAG,
       gc1.SEGMENT2 General_Controls,
       gc2.SEGMENT2 AR_OPTIONS
  from ce_bank_accounts      kk,
       CE_BANK_ACCT_USES_ALL ll,
       CE_GL_ACCOUNTS_CCID   tt,
       gl_code_combinations  gc1,
       gl_code_combinations  gc2
 where kk.BANK_ACCOUNT_ID = ll.BANK_ACCOUNT_ID
   and ll.BANK_ACCT_USE_ID = tt.BANK_ACCT_USE_ID
   and kk.ASSET_CODE_COMBINATION_ID = gc1.CODE_COMBINATION_ID
   and tt.AR_ASSET_CCID = gc2.CODE_COMBINATION_ID
   and gc1.SEGMENT2!=gc2.SEGMENT2

How to check Uncleared Payments (PLSQL Query)

http://aporaclepayables.blogspot.com/2016/10/how-to-check-uncleared-payments-plsql.html

select aa.ORG_ID, aa.DOC_SEQUENCE_VALUE,sum(aa.AMOUNT), aa.STATUS_LOOKUP_CODE, jj.REVERSAL_FLAG
from ap_checks_all aa, ap_invoice_payments_all jj
where aa.CHECK_ID=jj.CHECK_ID
and jj.REVERSAL_FLAG !='Y'
and aa.CLEARED_DATE is null
and aa.ORG_ID=jj.ORG_ID
and aa.ORG_ID=376
group by aa.ORG_ID, aa.DOC_SEQUENCE_VALUE, aa.STATUS_LOOKUP_CODE, jj.REVERSAL_FLAG

Default Payment Date in AP Payment Workbench

http://aporaclepayables.blogspot.com/2016/10/default-payment-date-in-ap-payment.html

Personalization: 1

Trigger Event: WHEN-NEW-RECORD-INSTANCE
Trigger Object: PAY_SUM_FOLDER
Processing Mode: Not in Enter-Query Mode

Type: Property
Object Type: Item
Target Object: PAY_SUM_FOLDER.CHECK_DATE
Property Name: INITIAL_VALUE
Value: =select to_char(sysdate,'dd-mon-rrrr') from dual "or" =Sysdate

If this Error Occurs from Personalization: 1 then Try the below Personalization: 2
ORA-01403: no data found
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.


Personalization: 2

Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object: PAY_SUM_FOLDER.VENDOR_NUMBER
Condition: :PAY_SUM_FOLDER.CHECK_DATE is null
Processing Mode: Not in Enter-Query Mode

Type: Property
Object Type: Item
Target Object: PAY_SUM_FOLDER.CHECK_DATE
Property Name: VALUE
Value: =select to_char(sysdate,'dd-mon-rrrr') from dual "or" =Sysdate

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)

FRM-40742: Illegal Status Conversion On Record 1: New To Changed (Financial Options AP)

http://aporaclepayables.blogspot.com/2016/06/frm-40742-illegal-status-conversion-on.html

FRM-40742: Illegal Status Conversion On Record 1: New To Changed (Doc ID 1431987.1)

Tax Code link with Invoices Tables (Query)

http://aporaclepayables.blogspot.com/2016/06/tax-code-query.html

select DOC_SEQUENCE_VALUE, tax_name from(
select aia.DOC_SEQUENCE_VALUE,(select vv.TAX_NAME from AP_AWT_GROUP_TAXES_all vv where vv.GROUP_ID=aida.PAY_AWT_GROUP_ID) tax_name
from ap_invoices_all aia, ap_invoice_lines_all aila, ap_invoice_distributions_all aida
--,AP_AWT_GROUP_TAXES_all vv
--,ap_invoices_V aiv
where aia.invoice_id=aila.invoice_id
and aia.invoice_id=aida.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number


--and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
--and vv.ORG_ID=aida.ORG_ID
--and aiv.invoice_id=aia.invoice_id
--and aiv.invoice_id=aila.invoice_id
--and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305  ---249912
and aia.DOC_SEQUENCE_VALUE = 200900972
)

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

select  aia.INVOICE_AMOUNT,--aia.DOC_SEQUENCE_VALUE, vv.tax_name, aida.AMOUNT, ee.TAX_RATE,
 sum(aida.AMOUNT*ee.TAX_RATE/100) WHT
  , aia.INVOICE_AMOUNT- sum(aida.AMOUNT*ee.TAX_RATE/100) Paid
           from ap_invoices_all              aia,
     
                ap_invoice_distributions_all aida,
                AP_AWT_GROUP_TAXES_all vv,
                ap_awt_tax_rates_all  ee
         --,AP_AWT_GROUP_TAXES_all vv
         --,ap_invoices_V aiv
 
        where aia.invoice_id = aida.invoice_id
       
     
               and vv.GROUP_ID=aida.PAY_AWT_GROUP_ID
               and vv.ORG_ID=aia.ORG_ID
               and aia.ORG_ID =aida.ORG_ID
           
               and ee.TAX_NAME = vv.TAX_NAME
               and ee.ORG_ID=vv.ORG_ID
               and ee.END_DATE is null
                 
               --and vv.GROUP_ID(+)=aida.PAY_AWT_GROUP_ID
               --and vv.ORG_ID=aida.ORG_ID
               --and aiv.invoice_id=aia.invoice_id
               --and aiv.invoice_id=aila.invoice_id
               --and aiv.invoice_id=aida.invoice_id
               --and aia.org_id=?
               --and aida.invoice_id=10305  ---249912
            and aia.DOC_SEQUENCE_VALUE =15012198
            group by aia.INVOICE_AMOUNT

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

  select distinct INV, SUM(WHT),INV-sum(WHT)PAID from (select
-- round(abs(sum(vv.AMOUNT*cc.tax_rate/100)),2)
vv.invoice_amount INV,
sum(abs(vv.AMOUNT*cc.TAX_RATE/100))  WHT
--sum(abs(round(round(vv.AMOUNT,2)*cc.TAX_RATE,0)/100)) WHT
from
   (select aa.TAX_RATE, ff.GROUP_ID from ap_awt_group_taxes_all ff,
                                   ap_awt_tax_rates_all   aa
                        where ff.TAX_NAME = aa.TAX_NAME
                        and aa.END_DATE is null
                          and aa.ORG_ID = ff.ORG_ID
                      --    and aa.ORG_ID = :ORG_ID
                   
                          group by ff.GROUP_ID , aa.TAX_RATE
                          ) cc,
                          ( select sum(tt.AMOUNT) Amount, tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE, gg.INVOICE_AMOUNT, tt.org_id, tt.invoice_id
  from ap_invoice_distributions_all tt, ap_invoices_all gg
   where tt.INVOICE_ID = gg.INVOICE_ID
      and tt.org_id = gg.org_id
      --and gg.ORG_ID = :ORG_ID
      group by  tt.PAY_AWT_GROUP_ID, gg.DOC_SEQUENCE_VALUE,gg.INVOICE_AMOUNT ,tt.org_id, tt.invoice_id) vv
      where cc.group_id = vv.pay_awt_group_id
     and vv.DOC_SEQUENCE_VALUE = 16009616
   -- and vv.invoice_id = :INVOICE_ID
     and vv.org_id = 82
    group by   vv.PAY_AWT_GROUP_ID, vv.invoice_amount
)    
group by INV

Site, Location and Code Combination Checking in AP

http://aporaclepayables.blogspot.com/2016/06/for-invoice-header-select-ps.html

For Invoice Header

select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
 from  po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc

where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID

and ps.VENDOR_SITE_CODE='CH'

and gcc.SEGMENT2!=00

/*and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID
and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
*/

For Invoice Distribution

select aia.DOC_SEQUENCE_VALUE,aia.GL_DATE,ps.VENDOR_SITE_CODE, aia.ACCTS_PAY_CODE_COMBINATION_ID,gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT4
 from  po_vendors pv, po_vendor_sites_all ps, ap_invoices_all aia, ap_invoice_distributions_all ai, gl_code_combinations gcc

where pv.VENDOR_ID=ps.VENDOR_ID
and aia.VENDOR_ID=pv.VENDOR_ID
and ps.VENDOR_ID=aia.VENDOR_ID
and ps.VENDOR_SITE_ID=aia.VENDOR_SITE_ID
and ps.ORG_ID=aia.ORG_ID
and ai.INVOICE_ID=aia.INVOICE_ID
and ai.ORG_ID=aia.ORG_ID
and ps.ORG_ID=ai.ORG_ID
--and gcc.CODE_COMBINATION_ID=aia.ACCTS_PAY_CODE_COMBINATION_ID

and ps.VENDOR_SITE_CODE='HO'
and gcc.SEGMENT2!=05
and aia.GL_DATE>'01-JAN-2015'

and gcc.CODE_COMBINATION_ID=ai.DIST_CODE_COMBINATION_ID

--and ps.ACCTS_PAY_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID

Error: This tax registration number is already in use for party type

http://aporaclepayables.blogspot.com/2016/04/error-this-tax-registration-number-is.html

Problem Description

ERROR:This tax registration number is already in use for party type: Third Party and party name: Pearl Continental Hotel for the same period.

When we try to open the Supplier in AP having the same 'Tax Registration number' the above error occurs.

SR Solution

Thanks for your time and confirmation that you are not using Ebtax.

1.The original issue you were having was an error : This tax registration number is already in use for party type. This is caused by an expecting functionality,

At this time on R12 it is NOT allowed to have the same tax registration number for different parties (supplier, customers,,,) hence you need to have and implement a workaround to allow this.

2. For the same reason, every time that you update a party, that error will be returned in case the same tax registration is used for any other party. Based on above, the workaround is suggested in order to be able to update the supplier and to be able to save the data,

3. So for the specific questions you asked:

3.1 What is the Root cause,

the root cause is the standard functionality which does not allow the same registration number for different parties:

Please check the following note and Enhancement request for the same:

Customers Workbench: Error 'Tax Registration Number is already in use for party type: Third Party, Party Name: XXX' when creating customer with same tax registration number as suppliers ( Doc ID 1320761.1 )

ER 11930002 : TAX REGISTRATION NUMBER ALREADY IN USE WHEN UPDATING SUPPLIER

3.2 Yes, There is a possibility that happens again for any other supplier. In that case, we should follow and apply again the suggested workaround. Solution is described on ( Doc ID 1135593.1 )

I will attach your company name to the existent ER,

WHT Tax segregation on Location Basis using one Tax Code

http://aporaclepayables.blogspot.com/2016/03/please-download-it-fom-below-link.html

Please Download it fom the Below Link:

https://drive.google.com/open?id=0B30-kT1pIOm_YXcxZkpVdFJubW8

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


-- Note: The report is based on assumpation/requirement that each invoice correspond to one and only one payment. Incase of multiple payment lines (except reversals) report will not work properly and cannot be corrected also due to layout constraint.
-- Use standard report instead in latter case.


SELECT   aia.invoice_id

------------------------ Aggregating multiple invoice distribution lines using SUM/Decode Combination -----------------------------------------------------------------------------

,        SUM (aidl1.amount) dist_amount_ent
,        SUM (nvl (aidl1.base_amount, aidl1.amount)) dist_amount_fnc
,        SUM (decode (aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) dist_tax_amount_ent
,        SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_tax_amount_fnc
,        SUM (aidl1.amount)  - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (aidl.amount))) dist_net_amount_ent
,        SUM (nvl (aidl1.base_amount, aidl1.amount)) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_net_amount_fnc

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
,        atg.NAME inv_dist_tax_group
,        atg.description inv_dist_tax_group_desc
,        aia.invoice_currency_code inv_currency
,        vnd.vendor_name
,vnds.VENDOR_SITE_CODE
,        vnd.segment1 vendor_code
,        vnds.city vendor_city
,        vnd.vat_registration_num vnd_registration_num
,        aia.doc_sequence_value inv_apn_num
,        aca.doc_sequence_value inv_apv_num
,        aca.cleared_date cleared_date
    FROM -- ap_invoice_distributions_all aidl          -- used for tax grouping     --- Marked on 15 September 2010 By Muhammad Raheem (Saad)
                                                                                  -- Distribution line calculating tax on sables basis, that create multiple TAX lines so these lines where sum to make single TAX line
        (SELECT sum(amount) amount,
                 sum(base_amount) base_amount,
                 line_type_lookup_code,
                 PAY_awt_group_id,                  -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
                 reversal_flag,
                 invoice_id,
                 awt_related_id
                 FROM ap_invoice_distributions_all
WHERE ORG_ID=:P_ORG_ID AND NVL(WITHHOLDING_TAX_CODE_ID,-1) NOT IN (                                               --- ADDED ON 28 FEB
SELECT TAX_ID FROM Ap_Tax_Codes_All WHERE  ORG_ID=:P_ORG_ID and  DESCRIPTION LIKE 'GST-SRO98')       --- ADDED ON 28 FEB
                 group by PAY_awt_group_id, reversal_flag, line_type_lookup_code, invoice_id, awt_related_id
                 ) aidl    -- used for tax grouping  
,        ap_invoice_distributions_all aidl1                                                                        -- used for item grouping
,        ap_awt_groups atg
,        ap_invoices_all aia
,        ap_suppliers vnd
,        ap_supplier_sites_all vnds
,        ap_invoice_payments_all aipa
,        ap_checks_all aca
   WHERE
             
-- -------* key join codition *------------
            aia.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
   AND aidl1.ORG_ID= :P_ORG_ID -- ADDED ON 31-JUL-15
   AND aipa.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
   AND aca.org_id=:P_ORG_ID -- ADDED ON 31-JUL-15
   AND aia.org_id=aipa.org_id -- ADDED ON 31-JUL-15
   AND aia.org_id=aca.org_id -- ADDED ON 31-JUL-15
   AND aia.org_id=aidl1.org_id -- ADDED ON 31-JUL-15

   AND      aidl1.invoice_distribution_id = aidl.awt_related_id
-----------------------------------------------------
     AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
     AND aia.invoice_id = aidl.invoice_id
     AND vnd.vendor_id = aia.vendor_id
     AND vnd.vendor_id = vnds.vendor_id
     AND aia.vendor_site_id = vnds.vendor_site_id
     AND aipa.invoice_id = aia.invoice_id
     AND aca.check_id = aipa.check_id
     AND aca.cleared_date IS NOT NULL
--     AND aidl.amount <> '0'                                                                                       -- do not print zero value tax lines
     AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
     AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
     AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
-- -----------Parameters-------------------
     AND aca.cleared_date BETWEEN nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)
     AND atg.NAME BETWEEN nvl (:p_tax_code_from, atg.NAME) AND nvl (:p_tax_code_to, atg.NAME)
--     AND aia.doc_sequence_value = nvl (:p_apn_num, aia.doc_sequence_value)
     AND vnd.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, vnd.vendor_name) AND nvl(:cf_to_vendor_dsp, vnd.vendor_name)
   AND vnds.VENDOR_SITE_CODE=NVL(:P_VENDOR_SITE_CODE,vnds.VENDOR_SITE_CODE)
-- ----------------------------------------------
GROUP BY
         atg.NAME
,        atg.description
,        vnd.vendor_name
,        vnd.segment1
,        vnds.city
,        vnd.vat_registration_num
,        aia.doc_sequence_value
,        aca.doc_sequence_value
,        aia.invoice_currency_code
,        aca.cleared_date
,        aia.invoice_id
,vnds.VENDOR_SITE_CODE
ORDER BY
        inv_dist_tax_group,
       aca.cleared_date,
       aia.doc_sequence_value
ASC;

sorry we couldn't find error in excel

http://aporaclepayables.blogspot.com/2016/02/sorry-we-couldnt-find-excel-error.html

In my case

I Navigate to Excel Options Addins and press go from Excel Addin and uncheck the file creating error.

Remit to Supplier

http://aporaclepayables.blogspot.com/2016/02/q.html

Q.
We are facing an issue in 12.2.4 that in invoice header "Remit to Supplier name" field is blank when we enter invoice but In the previous version of 12.0.6 it was not blank,it shows Supplier Name when we enter invoice.So we are facing some issues so kindly guide us about this issue. 

R.
Starting with 12.1.1 and above, it is intended functionality that Remit to Supplier and site are not mandatory on invoice workbench. 
Hence the fields do not have defaults like in 12.0 

If the invoice requires payment to be made to a third party, user can use the LOV for the field and choose a third party. 
Please note that in Payables Options , Invoices tab the option Allow Remit-To Supplier Override needs to be set up. 

Payment Accounting Processed when create Accounting Program runs without clearing the Payment

http://aporaclepayables.blogspot.com/2016/02/payment-accounting-processed-when.html

R12.2.4 Functionality

Payment Accounting is when Payment Clears 

1.Made the Invoice 
2.Validate the Invoice 
3.Made the Payment 
4.Run the Create Accounting Program (Invoice Accounted to Yes & Payment Accounting to Processed) 
5.Clears the Payment from Cash Management (Payment goes on Accounting Partial) 
6.Run the Create Accounting again (Payment Accounting is Processed) 


R12.0.6 Functionality
Payment Accounting is when Payment Clears 

1.Made the Invoice 
2.Validate the Invoice 
3.Made the Payment 
4.Run the Create Accounting Program (Invoice Accounted to Yes & Payment Accounting to Unprocessed) 
5.Clears the Payment from Cash Management (Payment Accounting is still Unprocessed) 
6.Run the Create Accounting again (Payment Accounting is Processed) 

As you can see above there is a difference in Accounting Status Starts from step 4 in both 12.0.6 and 12.2.4 Functionalities. 
Is this is a Standard Functionality in R12.2.4 that the Payment Accounting is Processed two times One Accounting through Program after Payment and Second Accounting after Clearing?

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

Initially system used to work as below 
R12.0.6 

1- Create and Account for an Invoice 
2- Pay in Full 
3- Run Create Accounting 

In this case, the Payment Created remains in status U/U (unprocessed) in xla_events 

This is modified in the bug 9245156 to work as below 

R12.2.4

1- Create and Account for an Invoice 
2- Pay in Full 
3-Run Create Accounting 

Now system will show the Payment is accounted and stamp Payment Created status N/P 

This mainly to avoid the Payment showing in period close exception report before clearing the Payment. 

This is modified in file apslappb.pls and system is working as intended in current version of application 

This changes are done due to Period Closing Purpose 

Hope this clarifies 

Solution on TEST server for orphan payments

http://aporaclepayables.blogspot.com/2016/02/solution-on-test-server-for-orphan.html

To Delete Check from System:

I was applied patch p10025422_R12.AP.A_R12_GENERIC.zip but before applying the patch I have some changes in /apps/10025422/ap/patch/115/sql/ap_invalid_check_sel.sql . Please check the below query i have comment one line APH.POSTED_FLAG <>''Y''.
After that I had applied /apps/10025422/ap/patch/115/sql/ap_invalid_check_fix.sql and my problem is resolved .

Note : I had applied above action plan on TEST, can I follow the same as on Production.

'CREATE TABLE AP_TEMP_DRIVER_10025422 AS
 SELECT AC.CHECK_ID,
 AC.CHECK_NUMBER,
 AC.AMOUNT,
 AC.STATUS_LOOKUP_CODE,
 APH.ACCOUNTING_EVENT_ID,
 APH.POSTED_FLAG,
 AC.VENDOR_ID,
 AC.VENDOR_SITE_ID,
 AC.PAYMENT_ID,
 AC.ORG_ID,
 AC.PAYMENT_DOCUMENT_ID,
 ''Y'' PROCESS_FLAG
 FROM AP_CHECKS_ALL AC,
 AP_PAYMENT_HISTORY_ALL APH
 WHERE AC.PAYMENT_ID IS NULL
 AND AC.STATUS_LOOKUP_CODE NOT IN (''OVERFLOW'',''SET UP'',''SPOILED'')
 AND AC.CHECK_ID=APH.CHECK_ID
 AND NVL(APH.HISTORICAL_FLAG,''N'') <> ''Y''
 --AND APH.POSTED_FLAG <>''Y''
 AND NOT EXISTS (SELECT 1
 FROM AP_INVOICE_PAYMENTS_ALL AIP
 WHERE AIP.CHECK_ID = AC.CHECK_ID)
AND EXISTS (SELECT 1
 FROM AP_INVOICE_PAYMENTS_ALL AIP1,
 AP_CHECKS_ALL AC1
 WHERE AIP1.CHECK_ID = AC1.CHECK_ID
 AND AC.ORG_ID = AC1.ORG_ID
 AND ((AC.VENDOR_ID = AC1.VENDOR_ID
 AND AC.VENDOR_SITE_ID = AC1.VENDOR_SITE_ID)
 OR AC.PAYMENT_DOCUMENT_ID =AC1.PAYMENT_DOCUMENT_ID)
 AND AC.CHECK_NUMBER = AC1.CHECK_NUMBER ) ';


cd /apps/10025422/ap/patch/115/sql
Please follow the instruction on README.txt for patch 10025422

connect apps/apps
run apgdfals.pls  
run apgdfalb.pls   

For 12.2.4
Apply GDF infrastructure patch 21816876:R12.AP.C to get the latest data fix package files (apgdfals.pls and apgdfalb.pls). 

run ap_invalid_check_sel.sql

--before fix the effected transaction please run beow command on production server

/*Update process_flag in AP_TEMP_DRIVER_10025422 to 'N'
   for transactions that should not be operated on*

run ap_invalid_check_fix.sql

Note: Do not need to apply patch you just follow the above action plan.

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

To Void Check without any Accounting:

Please Download the script from the below link

https://drive.google.com/open?id=0B30-kT1pIOm_dGJUOFRFNFpzNmM

Please execute the script:ap_void_check_mark_np_no_aip.sql in the cloned instance. 

Pass the check_id : 

Detailed steps: 
Verify whether Payment Voided and update us with the results 

1.Apply the fix:ap_void_check_mark_np_no_aip.sql .The fix script will do the following 

a.The Payment Cancellation event will be generated for the missing check_id in ap_invoice_payments which is existed in ap_payment_history. 

b.The XLA_EVENTS status will be set to N/P for the Cancellation event. 

c.The posted_flags in ap_payment_history_all,ap_invoice_payments_all,ap_invoice_distributions set to Y 

d.Finally,the status of the check will be set to "Voided" 

2.Retest the issue 

3.Migrate the solution to the appropriate environments 

How to Check orphan Payments through SQL Query

http://aporaclepayables.blogspot.com/2016/02/how-to-check-orphan-payments-through.html

select check_id, check_number from ap_checks_all where check_id
not in
(select check_id from ap_invoice_payments_all)

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

select aa.check_id, aa.check_number, aa.BANK_ACCOUNT_NAME, aa.AMOUNT, aa.CLEARED_DATE, aa.CHECK_DATE, aa.CREATED_BY from ap_checks_all aa where check_id
not in
(select check_id from ap_invoice_payments_all)

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

 SELECT AC.CHECK_ID,
            AC.CHECK_NUMBER,
      AC.AMOUNT
       FROM AP_CHECKS_ALL AC,
            AP_PAYMENT_HISTORY_ALL APH
      WHERE AC.PAYMENT_ID IS NULL
        AND AC.STATUS_LOOKUP_CODE NOT IN ('OVERFLOW','SET UP','SPOILED')
        AND AC.CHECK_ID=APH.CHECK_ID
        AND NVL(APH.HISTORICAL_FLAG,'N') <> 'Y'
        --AND APH.POSTED_FLAG <>'Y'
        AND NOT EXISTS (SELECT 1
                          FROM AP_INVOICE_PAYMENTS_ALL AIP
                         WHERE AIP.CHECK_ID = AC.CHECK_ID)
        AND EXISTS  (SELECT 1
                       FROM AP_INVOICE_PAYMENTS_ALL AIP1,
                AP_CHECKS_ALL AC1
                      WHERE AIP1.CHECK_ID = AC1.CHECK_ID
                        AND AC.ORG_ID = AC1.ORG_ID
                        AND ((AC.VENDOR_ID = AC1.VENDOR_ID
                        AND AC.VENDOR_SITE_ID = AC1.VENDOR_SITE_ID)
                         OR AC.PAYMENT_DOCUMENT_ID =AC1.PAYMENT_DOCUMENT_ID)
                        AND AC.CHECK_NUMBER = AC1.CHECK_NUMBER )

Excel Report Completed with Warning with below Error

http://aporaclepayables.blogspot.com/2016/02/error-payables-version-12.html

Error

+---------------------------------------------------------------------------+
Payables: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

APGSTDTL:  GST Detail Report
+---------------------------------------------------------------------------+

Current system time is 17-FEB-2016 13:30:50

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
P_DATE1='2015/01/01 00:00:00'
P_DATE2='2016/02/17 00:00:00'
P_ORG_ID='82'
------------

Forcing NLS_NUMERIC_CHARACTERS to: '.,' for XDO processing

 APPLLCSP Environment Variable set to :

 Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.UTF8

'.,'

Enter Password: 
REP-0004: Warning: Unable to open user preference file.

Report Builder: Release 10.1.2.3.0 - Production on Wed Feb 17 13:30:52 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
Executing request completion options...

Output file size: 
887739

+--------- 1) POST-PROCESSING  ---------+
Beginning post-processing of request 1502577 on node TEMPTEST at 17-FEB-2016 13:31:15.
-- Publishing output

 
Post-processing of request 1502577 failed at 17-FEB-2016 13:31:15 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
 
CONC-POST-PROCESSING RESULTS

-- PUBLISH: 
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException: apps/test/tmp/xdo1U4VhAgf2j021716_1331157785.fo (No such file or directory)
+---------------------------------------+


Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 17-FEB-2016 13:31:15

+---------------------------------------------------------------------------+

Solution

Set the Temporary Directory in XML Publisher Administration

In my case: /apps/test/tmp

Bank Setup

http://aporaclepayables.blogspot.com/2016/02/bank-setup.html

Please Download the file from Below URL

https://drive.google.com/open?id=0B30-kT1pIOm_S0hrVnlDNkVCNFk

Payment Uncleared is on “No Accounting Needed”

 http://aporaclepayables.blogspot.com/2016/02/payment-uncleared-is-on-no-accounting.html

Please review the below Document from URL

https://drive.google.com/open?id=0B30-kT1pIOm_Z0ppTnVFZmRFSmM

How to Open DFF on Supplier Site incl Personalization on DFF

http://aporaclepayables.blogspot.com/2016/02/how-to-open-dff-on-supplier-site.html

Read only for Approval Segment at Site Level
Global Data Elements|Bank Name|Bank Branch|Bank A/C Number|Sort Code|Approved($RO$)

All Access at Responsibility Level
Global Data Elements|Bank Name|Bank Branch|Bank A/C Number|Sort Code|Approved

How to give Supplier Inquiry Access to User

http://aporaclepayables.blogspot.com/2016/02/how-to-give-supplier-inquiry-access-to.html

       Prompt                             Submneu                               Function            Description
Supplier Inquiry   PN_SUPPLIERS_RO_SUBMENU   PN View Suppliers   Suppliers Inquiry

EVENT_STATUS_CODE and PROCESS_STATUS_CODE in XLA_EVENTS

http://aporaclepayables.blogspot.com/2016/02/eventstatuscode-and-processstatuscode.html

EVENT_STATUS_CODE Event type code	Meaning
I	Incomplete
N	No action
P	Processed
U	Unprocessed

PROCESS_STATUS_CODE Processing status code	Meaning
D	Draft
E	Error
I	Incomplete
P	Processed
R	Related event in error
U	Unprocessed

Draft : Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.

You can create accounting on this transaction again and again, which will delete the old journal entries and create new ones. You can’t transfer these journal entries to GL.

xla_events.process_status_code = D
xla_events.event_status_code = U
xla_ae_headers.accounting_entry_status_code = D

Final : Final will create journal entries, which can be transferred to GL. Once it is finally accounted you can’t run create accounting on the particular transaction (specifically on that event).

Run Transfer Journal Entries to GL program

xla_events.process_status_code = P
xla_events.event_status_code = P
xla_ae_headers.accounting_entry_status_code = F

Final Post: Final Post will create journal entries in final mode, transfer them to GL and post them.

xla_ae_headers.accounting_entry_status_code = F
xla_ae_headers.transfer_status_code = Y
xla_events.process_status_code = P
xla_events.event_status_code = P