0 Replies Latest reply on Jan 26, 2013 6:22 PM by ahmad

    Customer Balance Summary Report

    ahmad
      Dears,
      I have this query for custom report from receipt screen under receivable responsibility, but i need to view the organization code that create the invoice and the org. code that receive it, any help to modify this query ?

      SELECT   mpv.organization_code org_code, gcc.segment2 branch, z.customer_id,
               z.segment2 branch_acc, xba.description, z.customer_number,
               z.customer_name, z.invoice_amt, z.cm_amt, z.receipt_amt, z.balance
          FROM (SELECT         /*main.type,
                               main.trx_type_id,
                               GL_ID_REC,
                               rctt.name transaction_type,
                               rc.customer_number, rc.customer_name,   --main.value,
                               */
                         main.customer_id, gcc.segment2,
                         hca.account_number customer_number,
                         hp.party_name customer_name,
                         --SUM (DECODE (main.TYPE,'Invoice', DECODE (SIGN (main.VALUE),1, main.VALUE,0),0)) invoice_amt,
                         SUM (DECODE (main.TYPE,'Invoice',main.VALUE,0)) invoice_amt,
                         --SUM (DECODE (main.TYPE,'Invoice', DECODE (SIGN (main.VALUE),-1, main.VALUE,0),0)) cm_amt,
                         SUM (DECODE (main.TYPE,'CM_Manual',main.VALUE,0)) cm_amt,
                         SUM (DECODE (main.TYPE, 'Receipt', main.VALUE, 0)) receipt_amt,
                         --SUM (DECODE (main.TYPE,'Invoice', DECODE (SIGN (main.VALUE),1, main.VALUE,0),0))
                         SUM (DECODE (main.TYPE,'Invoice',main.VALUE,0))
                         --+ SUM (DECODE (main.TYPE,'Invoice', DECODE (SIGN (main.VALUE),-1, main.VALUE,0),0))
                         + SUM (DECODE (main.TYPE,'CM_Manual',main.VALUE,0))
                         - SUM (DECODE (main.TYPE, 'Receipt', main.VALUE, 0))balance
                    FROM 
                    -----
                           (SELECT 'Invoice' AS TYPE,
                                   rct.cust_trx_type_id trx_type_id,
                                   rct.bill_to_customer_id customer_id,
                                   SUM (ROUND(DECODE (NVL (rctl.quantity_invoiced,0),0, rctl.quantity_credited,rctl.quantity_invoiced)
                                             * NVL (rctl.unit_selling_price, 0),2)) VALUE
                              FROM ra_customer_trx_all rct,
                                   ra_customer_trx_lines_all rctl
                             WHERE rct.customer_trx_id = rctl.customer_trx_id
                               AND trx_date BETWEEN TO_DATE (:fdate,'RRRR/MM/DD HH24:MI:SS')
                                                AND TO_DATE (:tdate,'RRRR/MM/DD HH24:MI:SS')
                               AND rct.org_id = '102'
       and (rct.bill_to_customer_id = :p_cust_id or  :p_cust_id is null)
                               AND rctl.sales_order IS NOT NULL
                          GROUP BY rct.cust_trx_type_id, rct.bill_to_customer_id
                          -----
                          UNION ALL
                          -----
                            SELECT 'CM_Manual' AS TYPE,
                                   rct.cust_trx_type_id trx_type_id,
                                   rct.bill_to_customer_id customer_id,
                                   SUM (ROUND(DECODE (NVL (rctl.quantity_invoiced,0),0, rctl.quantity_credited,rctl.quantity_invoiced)
                                             * NVL (rctl.unit_selling_price, 0),2)) VALUE
                              FROM ra_customer_trx_all rct,
                                   ra_customer_trx_lines_all rctl
                             WHERE rct.customer_trx_id = rctl.customer_trx_id
                               AND trx_date BETWEEN TO_DATE (:fdate,'RRRR/MM/DD HH24:MI:SS')
                                                AND TO_DATE (:tdate,'RRRR/MM/DD HH24:MI:SS')
                               AND rct.org_id = '102'
       and (rct.bill_to_customer_id = :p_cust_id or  :p_cust_id is null)
                               AND rctl.sales_order IS NULL
                          GROUP BY rct.cust_trx_type_id, rct.bill_to_customer_id
                          -----
                          UNION ALL
                          -----
                            SELECT 'Receipt' AS TYPE,
                                   rcta.cust_trx_type_id trx_type_id,
                                   rcta.ship_to_customer_id customer_id,
                                   SUM (NVL (araa.amount_applied, 0)) VALUE
                              FROM ar_receivable_applications_all araa,
                                   ra_customer_trx_all rcta,
                                   ar_cash_receipts_all acra
                             WHERE araa.status = 'APP'
                               AND araa.display = 'Y'
                               AND araa.applied_customer_trx_id = rcta.customer_trx_id
                               AND acra.receipt_date
                                      BETWEEN TO_DATE (:fdate,'RRRR/MM/DD HH24:MI:SS')
                                          AND TO_DATE (:tdate,'RRRR/MM/DD HH24:MI:SS')
                               AND rcta.org_id = '102'
                             and (rcta.customer_trx_id = :p_cust_id or  :p_cust_id is null)
                               AND araa.cash_receipt_id IS NOT NULL
                               AND araa.cash_receipt_id = acra.cash_receipt_id
                          GROUP BY rcta.cust_trx_type_id, rcta.ship_to_customer_id
                         ) main,
                         hz_cust_accounts hca,
                         hz_parties hp,
                         ra_cust_trx_types_all rctt,
                         gl_code_combinations gcc
                   WHERE main.customer_id =hca.cust_account_id
                     AND hca.party_id=hp.party_id
                     AND main.trx_type_id = rctt.cust_trx_type_id
                     AND gcc.code_combination_id = gl_id_rec
                     AND rctt.org_id = 102
                GROUP BY gcc.segment2,
                         main.customer_id,
                         hca.account_number,
                         hp.party_name) z,
               xx_branch_acc xba,
               mtl_parameters_view mpv,
               gl_code_combinations gcc,
               hr_organization_units_v houv
         WHERE xba.branch_acc = z.segment2
           AND gcc.code_combination_id = mpv.material_account
           AND houv.organization_id = mpv.organization_id
           AND gcc.segment2 = xba.branch_acc
           AND mpv.organization_code BETWEEN NVL (:forg, mpv.organization_code)
                                         AND NVL (:torg, mpv.organization_code)
           AND date_to IS NULL
      ORDER BY mpv.organization_code, z.customer_name