Tags

, , ,

I used following script to reconcile the statements loaded today.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

DECLARE
   CURSOR c_new
   IS
        SELECT statement_number, org_id
          FROM apps.CE_STATEMENT_HEADERS_ALL
         WHERE TRUNC (creation_date) = TRUNC (SYSDATE)
      GROUP BY statement_number, org_id;

   CURSOR c_main (
      p_s_num     VARCHAR2,
      p_org_id    NUMBER)
   IS
        SELECT COUNT (1) no_txn,
               status,
               statement_number,
               SUM (amount) amount,
               org_id
          FROM apps.CE_STATEMENT_LINES line, apps.CE_STATEMENT_HEADERS_ALL hdr
         WHERE     line.statement_header_id = hdr.statement_header_id
               AND statement_number = p_s_num
               AND org_id = p_org_id
      GROUP BY status, statement_number, org_id
      ORDER BY org_id;
BEGIN
   DBMS_OUTPUT.put_line (‘Statements Loaded today ‘ || TRUNC (SYSDATE));

   FOR j IN c_new
   LOOP
      DBMS_OUTPUT.put_line (‘===========================’);
      DBMS_OUTPUT.put_line (‘Statement Number  ‘ || j.statement_number);
      DBMS_OUTPUT.put_line (‘Organization  ‘ || j.org_id);

      FOR i IN C_main (j.statement_number, j.org_id)
      LOOP
         DBMS_OUTPUT.put_line (‘Count of Checks :- ‘ || i.no_txn);
         DBMS_OUTPUT.put_line (‘Status :- ‘ || i.status);
         DBMS_OUTPUT.put_line (‘Amount :- ‘ || i.amount);
      END LOOP;

      DBMS_OUTPUT.put_line (‘===========================’);
   END LOOP;
END;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Output is something like:

Statements Loaded today 24-SEP-12
===========================
Statement Number  201209240715-WS
Organization  81
Count of Checks :- 6
Status :- RECONCILED
Amount :- 193167.79
===========================
===========================
Statement Number  201209240715-WS
Organization  83
Count of Checks :- 575
Status :- RECONCILED
Amount :- 165341988.98
===========================
===========================
Statement Number  201209240716-WS
Organization  146
Count of Checks :- 48
Status :- RECONCILED
Amount :- 22494
===========================

 

 

 

Advertisements