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