Scans all the open customer calls and closes them if all the
invoices associated with the call are closed.

Calling Arguments
‘Close Customer Calls’ is called from the Standard Report
Submission form it does not expect any parameters.

DECLARE
  CURSOR cc_cur IS
  SELECT rowid row_id,
         customer_call_id,
         DECODE(complete_flag,NULL,'N',complete_flag) c_flag
  FROM ar_customer_calls
  WHERE status = 'OPEN';

   CURSOR cct_cur (cc_call_id number) IS
   SELECT customer_trx_id
   FROM ar_customer_call_topics
   WHERE customer_call_id = cc_call_id;

   l_can_close BOOLEAN;
   invoice_bal NUMBER;
   cct_rowcount NUMBER;
   err_msg VARCHAR2(200);
   err_num VARCHAR2(20);
   location VARCHAR2(100);
BEGIN

    FOR cc_rec IN cc_cur
    LOOP
    l_can_close := TRUE;
      cct_rowcount := 0;
        FOR cct_rec IN cct_cur(cc_rec.customer_call_id)
        LOOP
          cct_rowcount := cct_rowcount +1;

          IF cct_rec.customer_trx_id IS NOT NULL   THEN
             SELECT SUM(NVL(amount_due_remaining,0)) INTO invoice_bal
             FROM ar_payment_schedules_all
             WHERE customer_trx_id = cct_rec.customer_trx_id;

            IF  invoice_bal > 0 THEN
                 l_can_close := FALSE;
                 EXIT ;
            END IF;
          END IF;
        END LOOP;
            IF cct_rowcount <= 0 THEN
                    l_can_close := FALSE;
            END IF;
  location := 'While closing open calls';
   IF l_can_close = TRUE  THEN
      arp_ccc_pkg.update_call_cover(
              'CLOSED',
              cc_rec.row_id,
              cc_rec.c_flag);
   END IF;
   END LOOP;
EXCEPTION
WHEN OTHERS THEN
  err_num := to_char(SQLCODE);
  err_msg := SUBSTR(SQLERRM,1,65);
  geae_ar_error_display_prc('ARXCAL04',err_num,err_msg,location);
END;
/

Return to : Oracle Database, SQL, Application, Programming Tips