2 Replies Latest reply: Nov 12, 2013 5:54 AM by Raghuatdell RSS

    Need your inputs in Preparing " Billing Details Report " in oracle Account Receivables

    Raghuatdell

      Hi Folks, I am new to oracle Finance Modules. Can any one please help me to get the Report Query or the related tables to develop " Oracle Billing Details" Report in oracle account receivables. The report should also look at the adjustments and amount on the Invoice level. ( Not on Line level ) Would be great if anyone can help me out to learn. Thanks Folks. Regards Raghu

        • 2. Re: Need your inputs in Preparing " Billing Details Report " in oracle Account Receivables
          Raghuatdell

          Hey Guys I was able to solve this. I developed the report. May be useful to few new bies. SELECT    RCT.customer_trx_id                AS ORACLE_INVOICE_NUMBER , RCT.attribute1                                AS SFM_BILL_NUMBER , RCT.trx_date                                  AS TRANSACTION_DATE , APS.amount_due_original                      AS INVOICE_AMOUNT , APS.AMOUNT_DUE_REMAINING                      AS BALANCE_DUE , APS.amount_adjusted                          AS ADJUSTMENTS , ACR.receipt_date                              AS RECEIPT_DATE , ACR.receipt_number                            AS RECEIPT_NUMBER , ACR.amount                                    AS RECEIPT_AMOUNT , ARM.name                                      AS RECEIPT_METHOD , ACR.attribute1                                AS OMNI_TRANSMITTAL , HCA_BILL.account_name                        AS PAYEE_NAME , substr(HCA_BILL.orig_system_reference,1,4)    AS SFM_PAYEE_NUMBER    , HCA_BILL.account_number                      AS BILL_ACCOUNT_NUM , HCA_SHIP.account_name                        AS SUPPLIER_NAME , substr(HCA_SHIP.orig_system_reference,1,4)    AS SFM_SUPPLIER_CODE , RCT.attribute4                                AS CONS_BILL_NUMBER      , RTT.name                                      AS PAYMENT_TERMS    , HCA_SHIP.account_number                      AS SHIP_ACCOUNT_NUM        , RCT.attribute6                                AS CALC_PERIOD      FROM ra_customer_trx              RCT ,ra_customer_trx_lines        RCTL ,ra_cust_trx_types            RTT ,ar_payment_schedules          APS ,ar_receivable_applications    ARP ,ar_cash_receipts              ACR ,ar_receipt_methods            ARM ,hz_cust_accounts              HCA_BILL ,hz_cust_accounts              HCA_SHIP ,ra_batch_sources              RBS WHERE  RCT.customer_trx_id      = RCTL.customer_trx_id AND RCT.cust_trx_type_id      = RTT.cust_trx_type_id AND RCT.customer_trx_id      = APS.customer_trx_id AND APS.payment_schedule_id  = ARP.applied_payment_schedule_id(+) AND ACR.cash_receipt_id(+)    = ARP.cash_receipt_id AND RCT.complete_flag        = 'Y' AND RCT.receipt_method_id    = ARM.receipt_method_id(+) AND RCT.bill_to_customer_id  = HCA_BILL.cust_account_id AND RCT.ship_to_customer_id  = HCA_SHIP.cust_account_id AND RBS.batch_source_id      = RCT.batch_Source_id AND RCT.trx_date BETWEEN :P_FROM_DATE AND :P_TO_DATE AND rbs.name in ( NVL(:P_INVOICE_SOURCE,'SFM')) ORDER BY  RCT.customer_trx_id   ,RCT.trx_date   ,ACR.receipt_number