Commision for agent report -AR/Excel

 /*********************************************************************** 
 *PURPOSE: SQL Query to get Account wise commision for agent report AR *
 *AUTHOR: Samba Siva Chowdary                            *
 ***********************************************************************/   

SELECT CUST_INV.line_amount,
       CUST_INV.revenue_account,
       CUST_INV.customer_account_number,
       CUST_INV.customer_name,
       AgenT_Q.agent,
       AgenT_Q.percentage,
       AgenT_Q.division,
       AgenT_Q.supplier_number,
       Round(CUST_INV.line_amount * ( AgenT_Q.percentage * 0.01 ), 2)
       PERCENTAGE_AMOUNT,
       CUST_INV.trx_number,
       To_char(CUST_INV.trx_date, 'DD/MM/YYYY')                       trx_date,
       CUST_INV.amount_applied,
       CUST_INV.receipt_date
FROM   (SELECT hca.cust_account_id,
               SUM(( trx_dist.amount + Nvl(sgst.sgst_amount, 0)
                     + Nvl(cgst.cgst_amount, 0)
                     + Nvl(igst.igst_amount, 0)
                    -- Add Frieght
                    ))            line_amount,
               gcc.segment2       revenue_account,
               hca.account_number customer_account_number,
               hp.party_name      customer_name,
               rcta.trx_number,
               rcta.trx_date,
               ar_rct.amount_applied,
               ar_rct.receipt_date
        FROM   ra_customer_trx_all rcta,
               ra_cust_trx_line_gl_dist_all trx_dist,
               hz_cust_accounts hca,
               hz_parties hp,
               (SELECT DISTINCT trx_id,
                                trx_line_id,
                                trx_line_number,
                                Abs (unrounded_tax_amt)     igst_amount,
                                tax_rate                    igst_rate,
                                Abs (unrounded_taxable_amt) igst_taxable
                FROM   zx_lines
                WHERE  entity_code = 'TRANSACTIONS'
                       AND tax LIKE '%IGST%'
                       AND application_id = 222) igst,
               (SELECT DISTINCT t = rx_id,
                                trx_line_id,
                                trx_line_number,
                                Abs (unrounded_tax_amt)     cgst_amount,
                                tax_rate                    cgst_rate,
                                Abs (unrounded_taxable_amt) cgst_taxable
                FROM   zx_lines
                WHERE  entity_code = 'TRANSACTIONS'
                       AND tax LIKE '%CGST%'
                       AND application_id = 222) cgst,
               (SELECT DISTINCT trx_id,
                                trx_line_id,
                                trx_line_number,
                                Abs (unrounded_tax_amt)     sgst_amount,
                                tax_rate                    sgst_rate,
                                Abs (unrounded_taxable_amt) sgst_taxable
                FROM   zx_lines
                WHERE  entity_code = 'TRANSACTIONS'
                       AND tax LIKE '%SGST%'
                       AND application_id = 222) sgst,
               gl_code_combinations gcc,
               xla_transaction_entities xte,
               xla_ae_headers xah,
               xla_ae_lines xal,
               xla_distribution_links xdl,
               gl_import_references gir,
               gl_je_headers gjh,
               gl_je_lines gjl,
               (SELECT araa.applied_customer_trx_id,
                       SUM(araa.amount_applied)
                       amount_applied,
                       Listagg(DISTINCT To_char(araa.apply_date, 'DD/MM/YYYY'),
                       ' / ')
                         within GROUP (ORDER BY
                         To_char(araa.apply_date, 'DD/MM/YYYY'))
                       receipt_date
                FROM   ar_cash_receipts_all acra,
                       ar_receivable_applications_all araa
                WHERE  1 = 1
                       --and araa.applied_customer_trx_id = rcta.customer_trx_id
                       AND acra.cash_receipt_id = araa.cash_receipt_id
                --and araa.applied_customer_trx_id=81066
                GROUP  BY araa.applied_customer_trx_id) ar_rct
        WHERE  1 = 1
               AND ar_rct.applied_customer_trx_id = rcta.customer_trx_id
               AND rcta.customer_trx_id = trx_dist.customer_trx_id
               AND rcta.bill_to_customer_id = hca.cust_account_id
               AND rcta.sold_to_party_id = hp.party_id
               AND account_class = 'REV'
               --Tax
               AND trx_dist.customer_trx_id = igst.trx_id(+)
               AND trx_dist.customer_trx_line_id = igst.trx_line_id(+)
               AND trx_dist.customer_trx_id = sgst.trx_id(+)
               AND trx_dist.customer_trx_line_id = sgst.trx_line_id(+)
               AND trx_dist.customer_trx_id = cgst.trx_id(+)
               AND trx_dist.customer_trx_line_id = cgst.trx_line_id(+)
               AND ( hca.account_name IN ( :p_cust )
                      OR Coalesce(:p_cust, NULL) IS NULL )
               AND Trunc(rcta.trx_date) BETWEEN Nvl(:p_inv_from,
                                                Trunc(rcta.trx_date))
                                                AND Nvl(
                                                :p_inv_to, Trunc(rcta.trx_date))
               ----
               AND xte.source_id_int_1 = rcta.customer_trx_id
               AND gcc.code_combination_id = gjl.code_combination_id
               AND xte.entity_code = 'TRANSACTIONS'
               AND xte.application_id = 222
               AND xah.entity_id = xte.entity_id
               AND xal.ae_header_id = xah.ae_header_id
               AND xal.accounting_class_code = 'REVENUE'
               AND xdl.ae_header_id = xah.ae_header_id
               AND xdl.ae_line_num = xal.ae_line_num
               AND xdl.source_distribution_id_num_1 =
                   trx_dist.cust_trx_line_gl_dist_id
               AND xdl.application_id = 222
               AND gir.gl_sl_link_id = xal.gl_sl_link_id
               AND gir.gl_sl_link_table = 'XLAJEL'
               AND gjh.je_header_id = gir.je_header_id
               AND gjl.je_header_id = gjh.je_header_id
               AND gjl.je_line_num = gir.je_line_num
        GROUP  BY hca.cust_account_id,
                  gcc.segment2,
                  hca.account_number,
                  hp.party_name,
                  rcta.trx_number,
                  rcta.trx_date,
                  ar_rct.amount_applied,
                  ar_rct.receipt_date) CUST_INV,
       (SELECT cust_account_id   ACCOUNTZ_ID,
               attribute3        agent,
               attribute_number1 percentage,
               attribute_number3 DIVISION,
               attribute4        Supplier_number
        FROM   hz_cust_accounts
        UNION
        SELECT cust_account_id       ACCOUNTZ_ID,
               attribute7            agent,
               To_number(attribute8) percentage,
               attribute_number4     DIVISION,
               attribute9            Supplier_number
        FROM   hz_cust_accounts) Agent_q
WHERE  CUST_INV.cust_account_id = Agent_q.accountz_id(+)
       AND CUST_INV.revenue_account = Agent_q.division(+)