Gl Account Code Balance Bifargation

/* Formatted on 2013/07/11 16:06 (Formatter Plus v4.8.8) */
SELECT gjh.je_header_id, gjh.je_category CATEGORY, gjh.je_source SOURCE,
       gjh.period_name period, gjl.je_line_num line_number, gjl.reference_5,
          glcc.segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6 code_combination,
      DECODE (gjh.je_source,
              'Payables', DECODE (gjl.reference_2,
                                  '', aala.accounted_cr,
                                  gjl.accounted_cr
                                  ),
              'Receivables', (SELECT rc.amount
                                FROM ra_cust_trx_line_gl_dist_all rc
                               WHERE rc.cust_trx_line_gl_dist_id =
                                                               gir.reference_3),
              gjl.accounted_cr
              ) enter_credit,
      DECODE (gjh.je_source,
               'Payables', DECODE (gjl.reference_2,
                                  '', aala.accounted_dr,
                                   gjl.accounted_dr
                                  ),
              gjl.accounted_dr
              ) enter_debit,
       DECODE (gjh.je_source,
               'Payables', DECODE (gjl.reference_2,
                                   '', aala.reference5,
                                   (SELECT aia.invoice_num
                                      FROM apps.ap_invoices_all aia
                                     WHERE aia.invoice_id = gjl.reference_2)
                                  ),
               'Receivables', gir.reference_4
              ) invoice_num,RCTA.INTERFACE_HEADER_ATTRIBUTE1 ORDER_NUMBER,
      DECODE (gjh.je_source,
               'Payables', DECODE (gjl.reference_2,
                                   '', aala.reference1,
                                   (SELECT pv.vendor_name
                                      FROM apps.po_vendors pv,
                                           apps.ap_invoices_all aia
                                    WHERE pv.vendor_id = aia.vendor_id
                                       AND aia.invoice_id = gjl.reference_2)
                                  ),
              'Purchasing', (SELECT pv.vendor_name
                                FROM apps.po_vendors pv,
                                     apps.rcv_transactions rt
                              WHERE gjl.reference_5 = rt.transaction_id
                                 AND pv.vendor_id = rt.vendor_id),
               'Receivables', (SELECT rc.customer_name
                                FROM ra_customers rc
                               WHERE rc.customer_id = gir.reference_7)
             ) Customer,
      DECODE (gjh.je_source,
              'Payables', DECODE (gjl.reference_2,
                                   '', (SELECT aia.invoice_date
                                          FROM apps.ap_invoices_all aia
                                        WHERE aia.invoice_id =
                                                              aala.reference2),
                                   (SELECT aia.invoice_date
                                      FROM apps.ap_invoices_all aia
                                    WHERE aia.invoice_id = gjl.reference_2)
                                 ),
              'Receivables', (select rcta.TRX_DATE from ra_customer_trx_all rcta
                   where gir.REFERENCE_4 = rcta.TRX_NUMBER)
              ) inv_date,
--        /*  DECODE (gjh.je_source,
--                 'Payables', DECODE (gjl.reference_2,
--                                     '', (SELECT aia.invoice_amount
--                                            FROM apps.ap_invoices_all aia
--                                           WHERE aia.invoice_id =
--                                                           aala.reference2),
--                                     (SELECT aia.invoice_amount
--                                        FROM apps.ap_invoices_all aia
--                                       WHERE aia.invoice_id =
--                                                           gjl.reference_2)
--                                    ),
--                 ''
--                ) inv_amt,
--         DECODE (gjh.je_source,
--                 'Payables', DECODE (gjl.reference_2,
--                                     '', aala.reference3,
--                                     gjl.reference_3
--                                    ),
--                 ''
--                ) dist_line_num,
--                             DECODE
--            (gjh.je_source,
--             'Payables', DECODE
--                          (gjl.reference_2,
--                           '', (SELECT aida.line_type_lookup_code
--                                  FROM apps.ap_invoice_distributions_all aida
--                                 WHERE aida.invoice_id = aala.reference2
--                                   AND aala.reference3 =
--                                             aida.distribution_line_number),
--                           (SELECT aida.line_type_lookup_code
--                              FROM apps.ap_invoice_distributions_all aida
--                             WHERE aida.invoice_id = gjl.reference_2
--                               AND gjl.reference_3 =
--                                             aida.distribution_line_number)
--                          ),
--             ''
--            ) line_type,
--         DECODE
--            (gjh.je_source,
--             'Payables', DECODE
--                          (gjl.reference_2,
--                           '', (SELECT DECODE (SIGN (aida.amount),
--                                               '-1', (aida.amount * (-1)),
--                                               ''
--                                              )
--                                  FROM apps.ap_invoice_distributions_all aida
--                                 WHERE aida.invoice_id = aala.reference2
--                                   AND aala.reference3 =
--                                             aida.distribution_line_number),
--                           (SELECT DECODE (SIGN (aida.amount),
--                                           '-1', (aida.amount * (-1)),
--                                           ''
--                                          )
--                              FROM apps.ap_invoice_distributions_all aida
--                             WHERE aida.invoice_id = gjl.reference_2
--                               AND gjl.reference_3 =
--                                             aida.distribution_line_number)
--                          ),
--             ''
--            ) dist_credit,
--        /* DECODE
--              (gjh.je_source,
--               'Payables', (SELECT DECODE (SIGN (aida.amount),
--                                           '-1', '',
--                                           aida.amount
--                                          )
--                              FROM apps.ap_invoice_distributions_all aida
--                             WHERE aida.invoice_id = gjl.reference_2
--                               AND gjl.reference_3 =
--                                             aida.distribution_line_number),
--               ''
--              ) dist_debit,
--         DECODE
--            (gjh.je_source,
--             'Payables', DECODE
--                         (gjl.reference_2,
--                          '', (SELECT ood.organization_name
--                                 FROM apps.rcv_transactions rt,
--                                      apps.rcv_shipment_headers rsh,
--                                      apps.ap_invoice_distributions_all aida,
--                                      apps.org_organization_definitions ood
--                                WHERE rt.transaction_id =
--                                                   aida.rcv_transaction_id
--                                  AND rt.shipment_header_id =
--                                                    rsh.shipment_header_id
--                                  AND aida.invoice_id = aala.reference2
--                                  AND aala.reference3 =
--                                             aida.distribution_line_number
--                                  AND rsh.ship_to_org_id =
--                                                       ood.organization_id),
--                          (SELECT ood.organization_name
--                             FROM apps.rcv_transactions rt,
--                                  apps.rcv_shipment_headers rsh,
--                                  apps.ap_invoice_distributions_all aida,
--                                  apps.org_organization_definitions ood
--                            WHERE rt.transaction_id =
--                                                   aida.rcv_transaction_id
--                              AND rt.shipment_header_id =
--                                                    rsh.shipment_header_id
--                              AND aida.invoice_id = gjl.reference_2
--                              AND gjl.reference_3 =
--                                             aida.distribution_line_number
--                              AND rsh.ship_to_org_id = ood.organization_id)
--                         ),
--             'Purchasing', (SELECT ood.organization_name
--                              FROM apps.org_organization_definitions ood,
--                                   apps.rcv_transactions rt
--                             WHERE rt.organization_id =
--                                                       ood.organization_id
--                               AND gjl.reference_5 = rt.transaction_id),
--             ''
--            ) inv_org1,*/
      DECODE
         (gjh.je_source,
           'Payables', DECODE
              (gjl.reference_2,
              '', (SELECT rsh.receipt_num
                      FROM apps.ap_invoices_all aia,
                           apps.ap_invoice_distributions_all aida,
                           apps.rcv_transactions rt,
                           apps.rcv_shipment_headers rsh
                     WHERE aia.invoice_id = aida.invoice_id
                       AND rt.transaction_id = aida.rcv_transaction_id
                      AND rsh.shipment_header_id = rt.shipment_header_id
                      AND aida.distribution_line_number = aala.reference3
                      AND aia.invoice_id = aala.reference2),
               (SELECT DECODE (aida.rcv_transaction_id,
                               '', (SELECT rsh.receipt_num
                                      FROM apps.rcv_transactions rt,
                                          apps.rcv_shipment_headers rsh,
                                           apps.ap_invoice_distributions_all aida
                                     WHERE rt.transaction_id =
                                                       aida.rcv_transaction_id
                                       AND rt.shipment_header_id =
                                                        rsh.shipment_header_id
                                       AND aida.invoice_id = gjl.reference_2
                                       AND gjl.reference_3 =
                                                 aida.distribution_line_number)
                              )
                  FROM apps.ap_invoice_distributions_all aida
                 WHERE aida.invoice_id = gjl.reference_2
                   AND gjl.reference_3 = aida.distribution_line_number)
             ),
          'Purchasing', (SELECT rsh.receipt_num
                           FROM apps.rcv_shipment_headers rsh,
                                apps.rcv_shipment_lines rsl,
                                apps.rcv_transactions rt
                           WHERE rsh.shipment_header_id =
                                                        rsl.shipment_header_id
                             AND rt.shipment_header_id =
                                                        rsh.shipment_header_id
                            AND rt.shipment_line_id = rsl.shipment_line_id
                             AND gjl.reference_5 = rt.transaction_id),
          ''
         ) grn_number,
       gjl.je_line_num
--                /*  (select  cta.TRX_NUMBER  from
--   RA_CUSTOMER_TRX_ALL CTA,
--            RA_CUSTOMER_TRX_LINES_ALL CTL
--            where
--             cta.customer_trx_id = gda.customer_trx_id
--            AND gjl.CODE_COMBINATION_ID = gda.CODE_COMBINATION_ID
--            AND cta.CUSTOMER_TRX_ID = ctl.CUSTOMER_TRX_ID) TRX_NUMBER *?
--            /*   DECODE
--                  (gjh.je_source,
--                   'Payables', (SELECT DECODE
--                                          (aida.rcv_transaction_id,
--                                           '', '',
--                                           (SELECT DISTINCT rsl.line_num
--                                                       FROM apps.rcv_transactions rt,
--                                                            apps.rcv_shipment_headers rsh,
--                                                            apps.ap_invoice_distributions_all aida,
--                                                            apps.rcv_shipment_lines rsl
--                                                      WHERE rt.transaction_id =
--                                                               aida.rcv_transaction_id
--                                                        AND rt.shipment_header_id =
--                                                               rsh.shipment_header_id
--                                                        AND aida.invoice_id =
--                                                                 gjl.reference_2
--                                                        AND gjl.reference_3 =
--                                                               aida.distribution_line_number
--                                                        AND rt.shipment_line_id =
--                                                               rsl.shipment_line_id
--                                                        AND rsh.shipment_header_id =
--                                                               rsl.shipment_header_id)
--                                          )
--                                  FROM apps.ap_invoice_distributions_all aida
--                                 WHERE aida.invoice_id = gjl.reference_2
--                                   AND gjl.reference_3 =
--                                                   aida.distribution_line_number),
--                   'Purchasing', (SELECT rsl.line_num
--                                    FROM apps.rcv_shipment_headers rsh,
--                                         apps.rcv_shipment_lines rsl,
--                                         apps.rcv_transactions rt
--                                   WHERE rsh.shipment_header_id =
--                                                          rsl.shipment_header_id
--                                     AND rt.shipment_header_id =
--                                                          rsh.shipment_header_id
--                                     AND rt.shipment_line_id =
--                                                            rsl.shipment_line_id
--                                     AND gjl.reference_5 = rt.transaction_id),
--                   ''
--                  ) grn_line_no, */
--             /*  DECODE (gjh.je_source,
--                       'Payables', (SELECT rt.transaction_date
--                                      FROM apps.rcv_transactions rt,
--                                           apps.ap_invoice_distributions_all aida
--                                     WHERE rt.transaction_id =
--                                                         aida.rcv_transaction_id
--                                       AND aida.invoice_id = gjl.reference_2
--                                       AND gjl.reference_3 =
--                                                   aida.distribution_line_number),
--                       'Purchasing', (SELECT rt.transaction_date
--                                        FROM apps.rcv_transactions rt
--                                       WHERE gjl.reference_5 = rt.transaction_id),
--                       ''
--                      ) grn_date,
--               DECODE
--                  (gjh.je_source,
--                   'Payables', TO_NUMBER (''),
--                   'Purchasing', (SELECT   rt.po_unit_price
--                                         * rt.quantity
--                                     --quantity is added by bharat on 19.08.2010
--                                    FROM apps.rcv_transactions rt
--                                   WHERE gjl.reference_5 = rt.transaction_id
--                                     AND rt.transaction_type NOT IN
--                                                           ('RETURN TO VENDOR')),
--                                        --THIS MODIFIED BY BHARAT ON 24-08-2011,
--                   ''
--                  ) grn_credit,
--               DECODE (gjh.je_source,
--                       'Payables', TO_NUMBER (gjl.accounted_dr),
--                       'Purchasing', (gjl.accounted_dr),
--                       ''
--                      ) grn_debit,
--  --MODIFIED BHARAT ON 24-08-2011 --grn_debit,-- modified by bharat on 19-08-2011 DECODE (gjh.je_source, 'Payables', '', '') grn_debit,DECODE (gjh.je_source, 'Payables', gjl.ACCOUNTED_DR , '') grn_debit,-- modified by bharat on 19-08-2011 DECODE (gjh.je_source, 'Payables', '', '') grn_debit,
--               DECODE
--                  (gjh.je_source,
--                   'Receivables', DECODE (gjh.je_category,
--                                          'Misc Receipts', (SELECT cr.receipt_number
--                                                              FROM apps.ar_cash_receipts_all cr
--                                                             WHERE gjl.reference_2 =
--                                                                      cr.cash_receipt_id),
--                                          ''
--                                         ),
--                   ''
--                  ) ar_receipt_number
--              DECODE
--                     (gjh.je_source,
--                      'Inventory', (SELECT transaction_source_id
--                                      FROM apps.mtl_material_transactions
--                                     WHERE transaction_id = gjl.reference_3),
--                      ''
--                     ) move_order_number */
--              -- gjh.doc_sequence_value,
--              -- gjh.default_effective_date,
--             --  gjh.actual_flag
FROM   apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_code_combinations glcc,
       apps.gl_import_references gir,
       apps.ap_ae_lines_all aala,
       RA_CUSTOMER_TRX_ALL RCTA
 WHERE gjh.je_header_id = gjl.je_header_id
   AND glcc.code_combination_id = gjl.code_combination_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   --AND gjl.gl_sl_link_id = gir.gl_sl_link_id(+)
   --AND gjl.gl_sl_link_table = gir.gl_sl_link_table(+)
   AND gir.gl_sl_link_id = aala.gl_sl_link_id(+)
    -- AND glcc.segment4 = NVL (:account_seg, glcc.segment6)
    -- AND glcc.segment1 = NVL (:acc_unit, glcc.segment1)
   --  AND TRUNC (gjh.default_effective_date)
         --   BETWEEN NVL (:period_from, TRUNC (gjh.default_effective_date))
               -- AND NVL (:period_to, TRUNC (gjh.default_effective_date))
    -- AND gjh.je_category <> 'Receiving India'
   AND gjh.period_name = NVL (:period_name, gjh.period_name)
   AND glcc.segment1 = '1'
   AND glcc.segment2 = '02'
   AND glcc.segment3 = '00'
   AND glcc.segment4 = '93000003'
   AND glcc.segment5 = '00'
   AND glcc.segment6 = '00'
   AND gjh.je_header_id=25749
   AND RCTA.TRX_NUMBER=gir.reference_4