Oracle AR transaction Query for EBS R12

 Oracle AR transaction query

SELECT RCT.customer_trx_id,
       RCT.trx_number "TRX NUMBER",
       RCT.trx_date   "TRX DATE",
       RCG.gl_date,
       RCT.purchase_order,
       OOD.organization_id,
       OOD.organization_name,
       RCL.description,
       HCA.account_number,
       HP.party_name,
       RCL.line_type  AR_LINE_TYPE,
       SUM(( Decode(RCT.invoice_currency_code, 'INR', RCG.amount * 1,
                                               RCG.amount * RCT.exchange_rate) )
       )
                      TOTAL_INV_AMOUNT
FROM   org_organization_definitions OOD,
       hz_cust_accounts HCA,
       hz_parties HP,
       ra_customer_trx_all RCT,
       ra_customer_trx_lines_all RCL,
       ra_cust_trx_line_gl_dist_all RCG
WHERE  RCT.customer_trx_id = RCL.customer_trx_id
       AND RCL.customer_trx_line_id = RCG.customer_trx_line_id
       AND RCT.bill_to_customer_id = HCA.cust_account_id
       AND rct.interface_header_context = 'ORDER ENTRY'
       AND rcl.interface_line_context = 'ORDER ENTRY'
       AND HP.party_name = :p_customer_name
       AND RCT.trx_number = '-----------'
       AND HCA.party_id = HP.party_id
       AND To_number(RCT.interface_header_attribute10) = OOD.organization_id (+)
       AND RCT.trx_date BETWEEN :P_FROM_DATE AND :P_TO_DATE
GROUP  BY RCT.customer_trx_id,
          RCT.trx_number,
          RCT.cust_trx_type_id,
          RCT.trx_date,
          RCG.gl_date,
          RCT.customer_trx_id,
          RCT.purchase_order,
          OOD.organization_name,
          RCL.description,
          HCA.account_number,
          OOD.organization_id,
          HP.party_name,
          RCL.line_type;