Oracle

Please select any file to see the markdown.

Input and Output - PLSQL

This pages shows an example of the output generated from analysing PLSQL such as the following (snippet):


    REATE OR REPLACE PACKAGE BODY pa_invoice AS
    --
      FUNCTION fu_next_inv_no RETURN NUMBER IS
      --
        CURSOR c_next_ino IS
          SELECT cc_next_value, cc_increment
          FROM   cg_code_controls
          WHERE  cc_domain = 'INV_SEQ'
          FOR UPDATE OF cc_next_value NOWAIT;
      --
        l_ino         NUMBER;
        l_inc         NUMBER;
        l_found       BOOLEAN;
      --
      BEGIN
      --
        SAVEPOINT sp_next_ino;
        OPEN c_next_ino;
        FETCH c_next_ino INTO l_ino, l_inc;
        l_found := c_next_ino%FOUND;
        CLOSE c_next_ino;
      --
        IF l_found THEN
        --
          UPDATE cg_code_controls
          SET    cc_next_value = cc_next_value + cc_increment
          WHERE  cc_domain = 'INV_SEQ';
        --
        ELSE
        --
          l_ino := NULL;
        --
        END IF;
      --
        RETURN ( l_ino );
      --
      EXCEPTION
      --
        WHEN OTHERS THEN
          ROLLBACK TO sp_next_ino;
          RAISE;
      --
      END fu_next_inv_no;
    --
      PROCEDURE pr_new_invoice
      (
        p_act_id    IN     invoices.act_id     %TYPE,
        p_inv_date  IN     invoices.inv_date   %TYPE,
        p_sent_date IN     invoices.sent_date  %TYPE,
        p_amount    IN     transactions.amount %TYPE,
        p_descr     IN     transactions.descr  %TYPE,
        p_inv_id    IN OUT invoices.id         %TYPE
      ) IS
      --
        l_trn_id transactions.id  %TYPE;
      --
      BEGIN
      --
        IF p_inv_id IS NULL THEN    
        
        --
          p_inv_id := fu_next_inv_no;
        --
        END IF;
      --
        INSERT INTO invoices
        (
          id,
          act_id,
          inv_date,
          paid_date,
          sent_date
        )
        VALUES
        (
          p_inv_id,
          p_act_id,
          p_inv_date,
          NULL,
          p_sent_date
        );
      --
        INSERT INTO transactions
        (
            id,
            ttp_code,
            trans_date,
            act_id,
            inv_id,
            descr,
            print_yn,
            amount
        )
        VALUES
        (
            trn_SEQ.NEXTVAL,
            'INVL',
            p_inv_date,
            p_act_id,
            p_inv_id,
            p_descr,
            'N',
            p_amount
        )
        RETURNING id INTO l_trn_id;
      --
        pr_upd_inv_sub_tot ( p_inv_id, l_trn_id );
        pr_upd_inv_grp_tot ( p_inv_id, l_trn_id );
        pr_upd_inv_vat ( p_inv_id, l_trn_id );
        pr_upd_inv_tot ( p_inv_id, l_trn_id );
      --
      EXCEPTION
      --
        WHEN OTHERS THEN
          ROLLBACK;
          RAISE_APPLICATION_ERROR ( -20001, 'pr_new_invoice:' || sqlerrm );
      --
      END pr_new_invoice;
    --

CRUD Matrix Table Level for pa_invoice.pkb

	Procedures and Functions
Table Name	fu_next_inv_no	pr_new_invoice	pr_upd_inv_sub_tot	pr_upd_inv_grp_tot	pr_upd_inv_vat	pr_upd_inv_tot	pr_add_tsheet	pr_get_invoice	pr_merge_invoices	pr_copy_invoice
CG_CODE_CONTROLS	-RU-	----	----	----	----	----	----	----	--U-	----
INVOICES	----	C---	----	----	----	----	----	----	----	----
TRANSACTIONS	----	C---	C-U-	C-U-	C-U-	C-U-	C---	----	--UD	----
TIMESHEETS	----	----	----	----	----	----	-R--	----	----	----
TRANS_LINKS	----	----	C---	C---	C---	C---	C---	----	---D	----

Note:

C: Create / Insert operation
R: Read / Select operation
U: Update operation
D: Delete operation
-: No operation

The following is an example of a summary page generated for a procudre./functiomn with the PLSQL object.

PLSQL Module Details - pa_invoice.pr_get_invoice

Declaration:


FUNCTION pr_get_invoice ( p_inv_id IN NUMBER ) RETURN CLOB

Inputs

- p_inv_id (Number)

Returns

- CLOB - contains the invoice details in HTML format

Description

This is a function in `pa_invoice` package called `pr_get_invoice`. It accepts an Invoice ID as an input parameter `p_inv_id`, which is a number and returns a CLOB.

The function generates a structured and formatted HTML invoice document related to the given Invoice ID.

The function executes various cursors for retrieving different information related to the invoice:

1. Details about the Account and the Invoice,

2. Details for each line item of the Invoice,

3. Total of the Invoice,

4. VAT information and,

5. Gross total

Html tags along with CSS are used for invoice formatting (like, font size, style, color etc.). Finally, the invoice `sen_date` is updated to the current date for the given invoice id.

CRUD Matrix

Table	Coulmn	CRUD
INVOICES	SENT_DATE	--Y-
INVOICES	ID	-Y--
INVOICES	INV_DATE	-Y--
ACCOUNTS	ID	-Y--
ACCOUNTS	NAME	-Y--
ACCOUNTS	ADDRESS	-Y--
TRANSACTIONS	TTP_CODE	-Y--
TRANSACTIONS	DESCR	-Y--
TRANSACTIONS	AMOUNT	-Y--

Calls

- pa_constants.fu_get_vat_rate() : For calculating VAT - UPDATE invoices: For updating the sent_date of the invoice after the invoice document has been created.

Dependencies - Tables

Depends on Tables: `Accounts`, `Invoices`, `Transactions`.

Dependencies - Modules

Depends on function: `fu_get_vat_rate()` in `pa_constants` package.

Oracle Scheduler Jobs Input

BEGIN
   DBMS_SCHEDULER.create_job ( 
      job_name        => 'EXPORT_300',
      job_type        => 'PLSQL_BLOCK',
      job_action      => '
BEGIN
   pa_exp_300.pr_main ( p_run_date => TRUNC (SYSDATE-1) );
   COMMIT;
END;
',
      start_date      => TO_TIMESTAMP( 'null' ),
      repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=15;',
      schedule_name   => 'null',
      enabled         => FALSE,
      comments        => 'null' );
END;
/

BEGIN
   DBMS_SCHEDULER.create_job ( 
      job_name        => 'LOAD_102',
      job_type        => 'PLSQL_BLOCK',
      job_action      => '
BEGIN
   pa_load_102.pr_main ( p_run_date => TRUNC (SYSDATE-1) );
   COMMIT;
END;
',
      start_date      => TO_TIMESTAMP( 'null' ),
      repeat_interval => 'FREQ=DAILY; BYHOUR=6,10,14,18,20; BYMINUTE=15;',
      schedule_name   => 'null',
      enabled         => FALSE,
      comments        => 'null' );
END;
/

BEGIN
   DBMS_SCHEDULER.create_job ( 
      job_name        => 'LOAD_105',
      job_type        => 'PLSQL_BLOCK',
      job_action      => '
BEGIN
   pa_load_105.pr_main;
   COMMIT;
END;
',
      start_date      => TO_TIMESTAMP( 'null' ),
      repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=30;',
      schedule_name   => 'null',
      enabled         => FALSE,
      comments        => 'null' );
END;
/

BEGIN
   DBMS_SCHEDULER.create_job ( 
      job_name        => 'LOAD_120',
      job_type        => 'PLSQL_BLOCK',
      job_action      => '
BEGIN
   pa_load_120.pr_main;
   COMMIT;
END;
',
      start_date      => TO_TIMESTAMP( 'null' ),
      repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN,WED; BYHOUR=8; BYMINUTE=00;',
      schedule_name   => 'null',
      enabled         => FALSE,
      comments        => 'null' );
END;
/

BEGIN
   DBMS_SCHEDULER.create_job ( 
      job_name        => 'SEND_INVOICES',
      job_type        => 'PLSQL_BLOCK',
      job_action      => '
BEGIN
   pa_ivoices.send_invoices;
   COMMIT;
END;
',
      start_date      => TO_TIMESTAMP( 'null' ),
      repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=2; BYMINUTE=00;',
      schedule_name   => 'null',
      enabled         => FALSE,
      comments        => 'null' );
END;
/

Scheduler Daily Jobs - Gantt Chart

Job Name

Scheduler Daily Jobs - Clock Dials

Table Details Example

The following is an example of a summary page generated from the basic table details.

Table Details - INVOICES

Description

Initially populated from schema/table/comment. This is added to by an AI generated summary.

The INVOICES table is a key table that stores details of invoices. It has relational links to a number of core tables, and is accessed by svereal modules. The data is created via a call to a module in PA_INVOICE.

Size & Usage

Number of Rows:	1,823 (as at {DATE})
Child Tables:	{list of tables that have this as a parent table}
Parents Tables:	{list of tables that have this as a child table}
Organisation:	{Partitioning details}

Structure

Column	Type	Mandatory	Default	FK Parent
ID	NUMBER(6)	Y	Y
ACT_ID	NUMBER(6)	Y	Y	Y
INV_DATE	DATE	Y	Y
PAID_DATE	DATE	Y	Y
SENT_DATE	DATE	Y	Y
PRINT_IMAGE	LONG	Y	Y

The following is an example of a summary information from related PLSQL objects.

CRUD

The modules that CRUD this table are as follows:

Modules	INSERT (C)	SELECT (R)	UPDATE (U)	DELETE (D)
PA_INVOICE	C	R	U	D
PA_TRANSACTIONS		R

The following is an example of a summary information from related Scheduler scripts, cross referencing with PLSQL objects.

Schedule Jobs

Job	Timing	SELECT (C)	SELECT (R)	UPDATE (U)	DELETE (D)
SEND_INVOICES	Every Friday at 2AM	C	R	U	-