Monday 4 March 2019

Query to find AR Open Balance Of a Project in Oracle 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
         begin
            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;
         exception when others then
            l_amount_applied_late:=0;
         end ;  
         END IF;

         IF (p_amount_applied IS NULL)
         THEN
            IF (p_amount_credited IS NOT NULL)
            THEN
            begin
               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
               ;
            exception when others then
            l_amount_applied_late :=0;  
            end;  
            END IF;
         END IF;
            l_amt_due_remaining := l_amount_applied_late;
         IF p_amount_adjusted IS NOT NULL
         THEN
         begin
            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';
         exception when others then
         l_adjustment_amount :=0;     
          end;
            -- 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;

FUNCTION get_invoice_outstanding_amount (
      p_project_id IN NUMBER,
      --p_invoice_number IN VARCHAR2,
      p_as_of_date IN DATE,
      p_convert_flag IN VARCHAR2,
      p_return_position IN NUMBER
   )
      RETURN NUMBER
   IS
      CURSOR c_outstanding_amount
      IS
         SELECT *
           FROM (SELECT 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,
               gsob.currency_code function_currency_code,
               ps.acctd_amount_due_remaining acctd_amt_due_remaining,
               ps.amount_line_items_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, proj.NAME project_name,
               proj.segment1 project_number,
               ps.cash_receipt_id cash_receipt_id, rct.org_id org_id,
               rct.customer_trx_id
          FROM hr_all_organization_units org,
               pjf_projects_all_vl proj,
               ra_customer_trx_all rct,
               ar_payment_schedules_all ps,
               ra_cust_trx_types_all rctt,              
               ra_terms_tl rtt,
               ar_notes n1,
               gl_sets_of_books gsob,
               fnd_currencies fc
         WHERE org.organization_id = proj.carrying_out_organization_id
           AND proj.segment1 = TRIM (LEADING '0' FROM rct.interface_header_attribute1)
           AND rct.interface_header_context = 'PROJECTS INVOICES'
           AND proj.org_id = rct.org_id
           AND rct.interface_header_attribute1 IS NOT NULL
           AND rct.customer_trx_id = ps.customer_trx_id
           AND rct.org_id = ps.org_id          
           AND ps.CUST_TRX_TYPE_SEQ_ID = rctt.CUST_TRX_TYPE_SEQ_ID         
           AND rct.term_id = rtt.term_id
           AND rct.customer_trx_id = n1.customer_trx_id(+)
           AND rct.set_of_books_id = gsob.set_of_books_id
           AND gsob.currency_code = fc.currency_code) a
          WHERE a.project_id = p_project_id           
            AND a.gl_date <= p_as_of_date;

      o_amount   NUMBER := 0;
   BEGIN      
      
       FOR i in c_outstanding_amount
       LOOP
      
       o_amount := o_amount + get_outstanding_amount(i.payment_schedule_id,
                                                      p_as_of_date,
                                                      i.invoice_type,
                                                      i.amount_line_items_remaining,
                                                      i.amount_applied,
                                                      i.amount_adjusted,
                                                      i.amount_credited,
                                                      i.amount_in_dispute,
                                                      i.amount_adjusted_pending,
                                                      p_convert_flag,
                                                      i.due_date,
                                                      p_return_position
                                                     );
      
       END LOOP;

      RETURN o_amount;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 0;
   END;

SELECT PPA.segment1 project_number   
    ,OCTV.name contracty_type
    ,PPA.name project_name
    ,PPA.description
    ,HOU.name organization_name
    ,PPA.project_currency_code
    ,OKH.contract_number
    ,OKL.line_number
    ,ROUND((NVL(get_invoice_outstanding_amount(PPA.project_id
                                   ,:P_AS_OF_DATE
                                   ,'Y'
                                   ,1), 0)), 2) ar_open
    ,PBC.billing_cycle_name
    ,(SELECT PPM.resource_source_name FROM pjf_proj_all_members_v PPM
       WHERE PPM.project_id = PPA.project_id
         AND PPM.project_role_id = 1
         AND :P_AS_OF_DATE BETWEEN PPM.start_date_active AND NVL(PPM.end_date_active, :P_AS_OF_DATE + 1)) project_manager
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
        ,PILD.contract_id, PILD.contract_line_id
    FROM pjb_inv_line_dists PILD
        ,pjb_invoice_headers PIH
   WHERE 1=1
     AND PILD.invoice_id = PIH.invoice_id
     AND PIH.transfer_status_code = 'A'
     AND PIH.gl_date <= :P_AS_OF_DATE
   GROUP BY PILD.contract_id, PILD.contract_line_id
     ) INV
,pjb_bill_plans_vl PBP
,pjf_billing_cycles_vl PBC
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = 'C' -- Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = 'C' -- Current
AND OKH.version_type = 'C' -- Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> 'DRAFT'
AND PPT.project_type NOT IN ('INTERCOMPANY', 'Intercompany')
AND INV.contract_id (+) = OKH.id
and INV.contract_line_id (+) = OKL.id
AND OKL.bill_plan_id    = PBP.bill_plan_id (+)
AND OKL.major_version   = PBP.major_version (+)
AND PBP.billing_cycle_id = PBC.billing_cycle_id (+)

No comments:

Post a Comment