AR Aging 4 Bucket with project details - FUSION

WITH  FUNCTION get_outstanding_amount (
      p_payment_schedule_id       IN   NUMBER,
      p_as_of_date                IN   DATE,
      p_invoice_type              IN   VARCHAR2,
      p_amt_due_remaining         IN   NUMBER,
      p_amount_applied            IN   NUMBER,
      p_amount_adjusted           IN   NUMBER,
      p_amount_credited           IN   NUMBER,
      p_amount_in_dispute         IN   NUMBER,
      p_amount_adjusted_pending   IN   NUMBER,
      p_convert_flag              IN   VARCHAR2,
      p_due_date                  IN   DATE,
      p_return_position           IN   NUMBER
   )
      RETURN NUMBER
   IS
      l_inv_type              CHAR (4);
      l_amount_applied_late   NUMBER (24, 2)            := 0;
      l_adjustment_amount     NUMBER (24, 2);
      l_amt_due_remaining     NUMBER (24, 2);
      l_percent_remaining     NUMBER (24, 2);
      l_payment_meaning       ar_lookups.meaning%TYPE;
      l_risk_meaning          ar_lookups.meaning%TYPE;
      l_outstanding_inv       NUMBER (14, 2);
      l_custinv_b0            NUMBER;
      l_custinv_b1            NUMBER;
      l_custinv_b2            NUMBER;
      l_custinv_b3            NUMBER;
      l_bucket_category       VARCHAR2 (10)             := NULL;
      l_bucket_days_from_0    NUMBER;
      l_bucket_days_to_0      NUMBER;
      l_bucket_line_type_0    VARCHAR2 (200);
      l_bucket_days_from_1    NUMBER;
      l_bucket_days_to_1      NUMBER;
      l_bucket_line_type_1    VARCHAR2 (200);
      l_bucket_days_from_2    NUMBER;
      l_bucket_days_to_2      NUMBER;
      l_bucket_line_type_2    VARCHAR2 (200);
      l_bucket_days_from_3    NUMBER;
      l_bucket_days_to_3      NUMBER;
      l_bucket_line_type_3    VARCHAR2 (200);
      l_b0_inv                NUMBER;
      l_b1_inv                NUMBER;
      l_b2_inv                NUMBER;
      l_b3_inv                NUMBER;
   BEGIN
      l_adjustment_amount := 0;
      l_amt_due_remaining := 0;
      l_outstanding_inv := 0;
      l_custinv_b0 := 0;
      l_custinv_b1 := 0;
      l_custinv_b2 := 0;
      l_custinv_b3 := 0;
      --
      l_outstanding_inv := NVL (p_amt_due_remaining, 0);

      -- Get l_payment_meaning
      SELECT meaning
        INTO l_payment_meaning
        FROM ar_lookups
       WHERE lookup_type = 'INV/CM/ADJ' AND lookup_code = 'PMT';

      -- Get l_risk_meaning
      SELECT meaning
        INTO l_risk_meaning
        FROM ar_lookups
       WHERE lookup_type = 'MISC_PHRASES' AND lookup_code = 'RISK';

      -- Get bucket_line_type_0, bucket_days_start_0, bucket_days_to_0
      SELECT lines.days_start, lines.days_to, lines.TYPE
        INTO l_bucket_days_from_0, l_bucket_days_to_0, l_bucket_line_type_0
        FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
       WHERE lines.aging_bucket_id = buckets.aging_bucket_id
         AND NVL (buckets.status, 'A') = 'A'
         AND buckets.aging_type = '4BUCKET'
         AND buckets.bucket_name = 'Standard'
         AND lines.bucket_sequence_num = 0;

      -- Get bucket_line_type_1, bucket_days_start_1, bucket_days_to_1
      SELECT lines.days_start, lines.days_to, lines.TYPE
        INTO l_bucket_days_from_1, l_bucket_days_to_1, l_bucket_line_type_1
        FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
       WHERE lines.aging_bucket_id = buckets.aging_bucket_id
         AND NVL (buckets.status, 'A') = 'A'
         AND buckets.aging_type = '4BUCKET'
         AND buckets.bucket_name = 'Standard'
         AND lines.bucket_sequence_num = 1;

      -- Get bucket_line_type_2, bucket_days_start_2, bucket_days_to_2
      SELECT lines.days_start, lines.days_to, lines.TYPE
        INTO l_bucket_days_from_2, l_bucket_days_to_2, l_bucket_line_type_2
        FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
       WHERE lines.aging_bucket_id = buckets.aging_bucket_id
         AND NVL (buckets.status, 'A') = 'A'
         AND buckets.aging_type = '4BUCKET'
         AND buckets.bucket_name = 'Standard'
         AND lines.bucket_sequence_num = 2;

      -- Get bucket_line_type_3, bucket_days_start_3, bucket_days_to_3
      SELECT lines.days_start, lines.days_to, lines.TYPE
        INTO l_bucket_days_from_3, l_bucket_days_to_3, l_bucket_line_type_3
        FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
       WHERE lines.aging_bucket_id = buckets.aging_bucket_id
         AND NVL (buckets.status, 'A') = 'A'
         AND buckets.aging_type = '4BUCKET'
         AND buckets.bucket_name = 'Standard'
         AND lines.bucket_sequence_num = 3;

      IF (p_invoice_type NOT IN (l_payment_meaning, l_risk_meaning))
      THEN
         IF (p_amount_applied IS NOT NULL)
         THEN
            SELECT NVL
                      (SUM
                          (  DECODE
                                (p_convert_flag,
                                 'Y', (  DECODE
                                            (ps.CLASS,
                                             'CM', DECODE
                                                (ra.application_type,
                                                 'CM', ra.acctd_amount_applied_from,
                                                 ra.acctd_amount_applied_to
                                                ),
                                             ra.acctd_amount_applied_to
                                            )
                                       + NVL (ra.acctd_earned_discount_taken,
                                              0
                                             )
                                       + NVL
                                            (ra.acctd_unearned_discount_taken,
                                             0
                                            )
                                  ),
                                 (  ra.amount_applied
                                  + NVL (ra.earned_discount_taken, 0)
                                  + NVL (ra.unearned_discount_taken, 0)
                                 )
                                )
                           * DECODE (ps.CLASS,
                                     'CM', DECODE (ra.application_type,
                                                   'CM', -1,
                                                   1
                                                  ),
                                     1
                                    )
                          ),
                       0
                      )
              INTO l_amount_applied_late
              FROM ar_receivable_applications_all ra,
                   ar_payment_schedules_all ps
             WHERE (   ra.applied_payment_schedule_id = p_payment_schedule_id
                    OR ra.payment_schedule_id = p_payment_schedule_id
                   )
               AND ra.status || '' = 'APP'
               AND NVL (ra.confirmed_flag, 'Y') = 'Y'
               AND ra.gl_date + 0 > TO_DATE (p_as_of_date, 'DD-MON-YY')
               AND ps.payment_schedule_id = p_payment_schedule_id
                                                                 -- AND   ra.receivable_application_id+0 < :rp_app_max_id
            ;
         END IF;

         IF (p_amount_applied IS NULL)
         THEN
            IF (p_amount_credited IS NOT NULL)
            THEN
               SELECT NVL
                         (SUM
                             (  DECODE
                                   (p_convert_flag,
                                    'Y', (  DECODE
                                               (ps.CLASS,
                                                'CM', DECODE
                                                   (ra.application_type,
                                                    'CM', ra.acctd_amount_applied_from,
                                                    ra.acctd_amount_applied_to
                                                   ),
                                                ra.acctd_amount_applied_to
                                               )
                                          + NVL
                                               (ra.acctd_earned_discount_taken,
                                                0
                                               )
                                          + NVL
                                               (ra.acctd_unearned_discount_taken,
                                                0
                                               )
                                     ),
                                    (  ra.amount_applied
                                     + NVL (ra.earned_discount_taken, 0)
                                     + NVL (ra.unearned_discount_taken, 0)
                                    )
                                   )
                              * DECODE (ps.CLASS,
                                        'CM', DECODE (ra.application_type,
                                                      'CM', -1,
                                                      1
                                                     ),
                                        1
                                       )
                             ),
                          0
                         )
                 INTO l_amount_applied_late
                 FROM ar_receivable_applications_all ra,
                      ar_payment_schedules_all ps
                WHERE (   ra.applied_payment_schedule_id =
                                                         p_payment_schedule_id
                       OR ra.payment_schedule_id = p_payment_schedule_id
                      )
                  AND ra.status || '' = 'APP'
                  AND NVL (ra.confirmed_flag, 'Y') = 'Y'
                  AND ra.gl_date + 0 > p_as_of_date
                  AND ps.payment_schedule_id = p_payment_schedule_id
                                                                    -- AND   ra.receivable_application_id+0 < :rp_app_max_id
               ;
            END IF;
         END IF;

         IF p_amount_adjusted IS NOT NULL
         THEN
            SELECT NVL (SUM (DECODE (p_convert_flag,
                                     'Y', acctd_amount,
                                     amount
                                    )
                            ),
                        0
                       )
              INTO l_adjustment_amount
              FROM ar_adjustments_all
             WHERE gl_date > p_as_of_date
               AND payment_schedule_id = p_payment_schedule_id
               AND status = 'A';

            -- AND     adjustment_id < :rp_adj_max_id;
            l_amt_due_remaining :=
                            l_amt_due_remaining - NVL (l_adjustment_amount, 0);
         END IF;

         l_outstanding_inv := l_outstanding_inv + l_amt_due_remaining;
      END IF;

      -- Get l_b0_inv
      SELECT DECODE (l_bucket_line_type_0,
                     'DISPUTE_ONLY', DECODE (NVL (p_amount_in_dispute, 0),
                                             0, 0,
                                             1
                                            ),
                     'PENDADJ_ONLY', DECODE (NVL (p_amount_adjusted_pending,
                                                  0),
                                             0, 0,
                                             1
                                            ),
                     'DISPUTE_PENDADJ', DECODE
                                    (NVL (p_amount_in_dispute, 0),
                                     0, DECODE
                                             (NVL (p_amount_adjusted_pending,
                                                   0
                                                  ),
                                              0, 0,
                                              1
                                             ),
                                     1
                                    ),
                       DECODE (GREATEST (l_bucket_days_from_0,
                                         CEIL (p_as_of_date - p_due_date)
                                        ),
                               LEAST (l_bucket_days_to_0,
                                      CEIL (p_as_of_date - p_due_date)
                                     ), 1,
                               0
                              )
                     * DECODE (NVL (p_amount_in_dispute, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'DISPUTE_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                     * DECODE (NVL (p_amount_adjusted_pending, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'PENDADJ_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                    )
        INTO l_b0_inv
        FROM DUAL;

      -- Get l_b1_inv
      SELECT DECODE (l_bucket_line_type_1,
                     'DISPUTE_ONLY', DECODE (NVL (p_amount_in_dispute, 0),
                                             0, 0,
                                             1
                                            ),
                     'PENDADJ_ONLY', DECODE (NVL (p_amount_adjusted_pending,
                                                  0),
                                             0, 0,
                                             1
                                            ),
                     'DISPUTE_PENDADJ', DECODE
                                    (NVL (p_amount_in_dispute, 0),
                                     0, DECODE
                                             (NVL (p_amount_adjusted_pending,
                                                   0
                                                  ),
                                              0, 0,
                                              1
                                             ),
                                     1
                                    ),
                       DECODE (GREATEST (l_bucket_days_from_1,
                                         CEIL (p_as_of_date - p_due_date)
                                        ),
                               LEAST (l_bucket_days_to_1,
                                      CEIL (p_as_of_date - p_due_date)
                                     ), 1,
                               0
                              )
                     * DECODE (NVL (p_amount_in_dispute, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'DISPUTE_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                     * DECODE (NVL (p_amount_adjusted_pending, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'PENDADJ_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                    )
        INTO l_b1_inv
        FROM DUAL;

      -- Get l_b2_inv
      SELECT DECODE (l_bucket_line_type_2,
                     'DISPUTE_ONLY', DECODE (NVL (p_amount_in_dispute, 0),
                                             0, 0,
                                             1
                                            ),
                     'PENDADJ_ONLY', DECODE (NVL (p_amount_adjusted_pending,
                                                  0),
                                             0, 0,
                                             1
                                            ),
                     'DISPUTE_PENDADJ', DECODE
                                    (NVL (p_amount_in_dispute, 0),
                                     0, DECODE
                                             (NVL (p_amount_adjusted_pending,
                                                   0
                                                  ),
                                              0, 0,
                                              1
                                             ),
                                     1
                                    ),
                       DECODE (GREATEST (l_bucket_days_from_2,
                                         CEIL (p_as_of_date - p_due_date)
                                        ),
                               LEAST (l_bucket_days_to_2,
                                      CEIL (p_as_of_date - p_due_date)
                                     ), 1,
                               0
                              )
                     * DECODE (NVL (p_amount_in_dispute, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'DISPUTE_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                     * DECODE (NVL (p_amount_adjusted_pending, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'PENDADJ_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                    )
        INTO l_b2_inv
        FROM DUAL;

      -- Get l_b3_inv
      SELECT DECODE (l_bucket_line_type_3,
                     'DISPUTE_ONLY', DECODE (NVL (p_amount_in_dispute, 0),
                                             0, 0,
                                             1
                                            ),
                     'PENDADJ_ONLY', DECODE (NVL (p_amount_adjusted_pending,
                                                  0),
                                             0, 0,
                                             1
                                            ),
                     'DISPUTE_PENDADJ', DECODE
                                    (NVL (p_amount_in_dispute, 0),
                                     0, DECODE
                                             (NVL (p_amount_adjusted_pending,
                                                   0
                                                  ),
                                              0, 0,
                                              1
                                             ),
                                     1
                                    ),
                       DECODE (GREATEST (l_bucket_days_from_3,
                                         CEIL (p_as_of_date - p_due_date)
                                        ),
                               LEAST (l_bucket_days_to_3,
                                      CEIL (p_as_of_date - p_due_date)
                                     ), 1,
                               0
                              )
                     * DECODE (NVL (p_amount_in_dispute, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'DISPUTE_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                     * DECODE (NVL (p_amount_adjusted_pending, 0),
                               0, 1,
                               DECODE (l_bucket_category,
                                       'PENDADJ_ONLY', 0,
                                       'DISPUTE_PENDADJ', 0,
                                       1
                                      )
                              )
                    )
        INTO l_b3_inv
        FROM DUAL;

      --
      IF l_b0_inv != 0
      THEN
         l_custinv_b0 := l_outstanding_inv;
      END IF;

      IF l_b1_inv != 0
      THEN
         l_custinv_b1 := l_outstanding_inv;
      END IF;

      IF l_b2_inv != 0
      THEN
         l_custinv_b2 := l_outstanding_inv;
      END IF;

      IF l_b3_inv != 0
      THEN
         l_custinv_b3 := l_outstanding_inv;
      END IF;

      -- Return value based on passed in position
      IF p_return_position = 1
      THEN
         RETURN l_outstanding_inv;
      ELSIF p_return_position = 2
      THEN
         RETURN l_custinv_b0;
      ELSIF p_return_position = 3
      THEN
         RETURN l_custinv_b1;
      ELSIF p_return_position = 4
      THEN
         RETURN l_custinv_b2;
      ELSIF p_return_position = 5
      THEN
         RETURN l_custinv_b3;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 0;
   END;
SELECT   inv_tab.solution_name,
               inv_tab.invoice_currency_code, inv_tab.function_currency_code,customer_name,invoice_date,
               SUM (inv_tab.amt_due_original) amount_due_original,
               SUM(get_outstanding_amount(inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'N',
                                              inv_tab.due_date,
                                              1
                                             ) ) Outstanding_amt_Inv_Currency
            ,SUM(get_outstanding_amount(inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.acctd_amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'Y',
                                              inv_tab.due_date,
                                              1)) outstand_amt_country_currency
         ,SUM(get_outstanding_amount
                                             (inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.acctd_amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'Y',
                                              inv_tab.due_date,
                                              2
                                             ) ) Current_outstanding
         ,SUM(get_outstanding_amount
                                             (inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.acctd_amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'Y',
                                              inv_tab.due_date,
                                              3
                                             )) outstanding_1_30_days
         ,SUM(get_outstanding_amount
                                             (inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.acctd_amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'Y',
                                              inv_tab.due_date,
                                              4
                                             )) outstanding_31_60_days,
         SUM(get_outstanding_amount
                                             (inv_tab.payment_schedule_id,
                                              :P_ASOF_DATE,
                                              inv_tab.invoice_type,
                                              inv_tab.acctd_amt_due_remaining,
                                              inv_tab.amount_applied,
                                              inv_tab.amount_adjusted,
                                              inv_tab.amount_credited,
                                              inv_tab.amount_in_dispute,
                                              inv_tab.amount_adjusted_pending,
                                              'Y',
                                              inv_tab.due_date,
                                              5
                                             )) outstanding_60_plus_days
          FROM (SELECT         org.NAME solution_name,
                       org.NAME original_solution_name,
                       (SELECT DISTINCT org.NAME
                                   FROM pjf_project_parties proj_dir,
                                        pjf_proj_role_types_tl prt,
                                        per_all_assignments_f paf,
                                        hr_all_organization_units org
                                  WHERE proj_dir.project_id = proj.project_id
                                    AND proj_dir.project_role_id =
                                                           prt.project_role_id
                                    AND prt.project_role_name =
                                                            'Project Director'
                                    --'1000'-- Project Director role type
                                    AND proj_dir.resource_source_id =
                                                                 paf.person_id
                                    AND paf.organization_id =
                                                           org.organization_id
                                    AND paf.assignment_status_type = 'ACTIVE'
                                    AND TRUNC (SYSDATE)
                                           BETWEEN paf.effective_start_date
                                               AND NVL
                                                      (paf.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                                    AND TRUNC (SYSDATE)
                                           BETWEEN proj_dir.start_date_active
                                               AND NVL
                                                     (proj_dir.end_date_active,
                                                      SYSDATE + 1
                                                     ))
                                                       director_solution_name,
                       (SELECT DISTINCT org.NAME
                                   FROM pjf_project_parties proj_dir,
                                        pjf_proj_role_types_tl prt,
                                        per_all_assignments_f paf,
                                        hr_all_organization_units org
                                  WHERE proj_dir.project_id = proj.project_id
                                    AND proj_dir.project_role_id =
                                                           prt.project_role_id
                                    AND prt.project_role_name =
                                                             'Project Manager'
                                    --'1000'-- Project Director role type
                                    AND proj_dir.resource_source_id =
                                                                 paf.person_id
                                    AND paf.organization_id =
                                                           org.organization_id
                                    AND paf.assignment_status_type = 'ACTIVE'
                                    AND TRUNC (SYSDATE)
                                           BETWEEN paf.effective_start_date
                                               AND NVL
                                                      (paf.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                                    AND TRUNC (SYSDATE)
                                           BETWEEN proj_dir.start_date_active
                                               AND NVL
                                                     (proj_dir.end_date_active,
                                                      SYSDATE + 1
                                                     )) manager_solution_name,
                       hca.cust_account_id customer_id,
                       RTRIM (RPAD (hp.party_name, 36)) customer_name,
                       RTRIM (RPAD (hca.account_number, 8)) customer_number,
                       ps.payment_schedule_id payment_schedule_id,
                       ps.trx_number invoice_number, ps.CLASS invoice_class,
                       rctt.NAME invoice_type, ps.due_date due_date,
                       ps.trx_date invoice_date, rtt.NAME term_name,
                       ps.invoice_currency_code invoice_currency_code,
                       gll.currency_code function_currency_code,
                       ps.acctd_amount_due_remaining acctd_amt_due_remaining,
                       ps.amount_due_remaining amt_due_remaining,
                       ROUND
                          ((ps.amount_due_original * NVL (ps.exchange_rate, 1)
                           ),
                           fc.PRECISION
                          ) amt_due_functional_currency,
                       ps.amount_due_original amt_due_original,
                       ps.amount_adjusted amount_adjusted,
                       ps.amount_applied amount_applied,
                       ps.amount_credited amount_credited,
                       ps.amount_in_dispute amount_in_dispute,
                       ps.amount_adjusted_pending amount_adjusted_pending,
                       NVL (ps.exchange_rate, 1) exchange_rate,
                       ps.gl_date gl_date, ps.gl_date_closed gl_date_closed
                       ,n1.text comments
                       , proj.project_id project_id,
                       projt.NAME project_name, proj.segment1 project_number,
                       ps.cash_receipt_id cash_receipt_id, rct.org_id org_id,
                       rct.customer_trx_id
                  FROM ra_customer_trx_all rct,
                       hr_all_organization_units org,
                       pjf_projects_all_b proj,
                       pjf_projects_all_tl projt,
                       ar_payment_schedules_all ps,
                       hz_cust_accounts hca,
                       hz_parties hp,
                       ra_cust_trx_types_all rctt,
                       gl_ledgers gll,
                       ar_notes n1,
                       fnd_currencies fc,
                       ra_terms_tl rtt
                 WHERE org.organization_id = proj.carrying_out_organization_id
                   AND proj.segment1 = rct.interface_header_attribute1
                   AND proj.org_id = rct.org_id
                   AND projt.project_id = proj.project_id
                   AND rct.customer_trx_id = ps.customer_trx_id
                   AND rct.org_id = ps.org_id
                   AND ps.customer_id = hca.cust_account_id
                   AND hca.party_id = hp.party_id
                   AND ps.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
                   and rct.customer_trx_id=n1.customer_trx_id(+)
                    AND rct.set_of_books_id = gll.ledger_id
                   AND (   n1.note_id IS NULL
                        OR n1.note_id =
                              (SELECT MAX (note_id)
                                 FROM ar_notes n2
                                WHERE n2.customer_trx_id = n1.customer_trx_id)
                       )
                   AND gll.currency_code = fc.currency_code
                   AND rct.term_id = rtt.term_id
                   ) inv_tab
         WHERE 1=1   
           AND    (inv_tab.invoice_date) <= :P_ASOF_DATE
                AND (inv_tab.gl_date_closed) >:p_asof_date
      GROUP BY inv_tab.solution_name,
               inv_tab.invoice_currency_code,
               inv_tab.function_currency_code,
               customer_name,invoice_date