Cash Bank Book Report Complete Logic in Oracle apps R12.

Cash Bank Book Report Complete Logic in Oracle apps R12

In this post , We will be discuss about the Cash Bank Book Report Complete Logic in Oracle apps R12. I will share the complete logic to developed the cash bank book report in oracle apps r12. I will share the detail tables list used to developed the cash bank book report. Here below is the complete logic about Cash Bank Book Report Complete Logic in Oracle apps R12. 

Hi Friends, In this post , I will share you the logic to create Cash Bank Book report in Oracle apps R12.

Steps to Developed the Cash Bank Book Report Complete Logic in Oracle apps R12

Step1:- You have to use first query for Payments. In the payment the main table will use is ap_checks_All

There will two columns in the report Payment and Receipt.
we will pick all the transactions/Checks which are clear and negotiable,reconciled,RECONCILED UNACCOUNTED,CLEARED BUT UNACCOUNTED.

The amount in ap_checks_all are mostly of +amt which is in payment_side
THE PAYMENT TYPE IN (P,OR NULL,M)

The amount in -ve are goes in receipt side (some time we take refund) so for those amt payment type flag in(R REFUND)

We will use Date parameter between  CHECK_DATE for table ap_checks_all

Other Table will be used in this as below

          ce_bank_accounts cba ,
         ce_bank_acct_uses_all cbu

-----------------------------------------------------------------------------------------------------------------------------------------------
Step2:- We will use Second query as a union with first. We will again use the second query from Payments(Ap_Checks_All)

This Query will Handle the Void Checks

We will  pick checks from Ap_checks_all with Status 'Void' and Data Parameter will work on Check Date

Payment Type similar as Above

Payments column take Amount simply from Ap_Checks_all Table AND Receipt column will be Null.

STATUS=VOIDED

TABLES Will Use
ap_checks_all apc,
          ce_bank_accounts cba ,
          ce_bank_acct_uses_all cbu


Step3:- We will use Third query as a union with first and Second. We will again use the Third query from Payments(AP_PAYMENT_HISTORY_ALL)

In this we use Special Table AP_PAYMENT_HISTORY_ALL

We will simple pick the check of STATUS=VOID

But here in this query we will put Data parameter onVOID_DATE

AND ALSO USE TRANSACTION_TYPE IN  ('PAYMENT CANCELLED','REFUND CANCELLED') OF TABLE AP_PAYMENT_HISTORY_ALL.

AMOUNT WE TAKE IN PAYMENTS and RECEIPTS columns are

RECEIPTS column will take Amount simple from Check Table and Payment Column will Null.

Step4:- This Fourth Query we will use for Those Invoices Which Use Bank GL Account in Invoice Distribution.

---------------------------------------------------------------------------------
FOR THOSE INVOICES WHICH HIT BANK IN DISTRIBUTIONS.

IN THIS WE LINK THE DIST_CODE_COMBINATION ID TO BANK ASSET CODE COMBIANTION ID

TABLES USED ARE
 ap_invoice_distributions_all apid,
        ap_invoices_all api,
        po_vendors POV,
         gl_code_combinations gcc,
         ce_bank_accounts cba 

In this Your Check Status will Always will Cleared.

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

Amount We will Take in Payment and Receipts are

Basically in Invoice Distribution amount is Negative so the amount Which is Negative will go to Payment Column and Positive Amount will go to Receipt column.