Example for VARRAYS and RECORD types in PL/SQL

Here is small code snippet which illustrates the VARRAYS and RECORD types in PL/SQL.

This is just for reference purpose :

----------------------------------------
--   Test Script
----------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF

SPOOL C:\Results.txt

DECLARE

    TYPE users IS RECORD(
            p_invoice_id          NUMBER,
            p_approver           VARCHAR2(250),
            p_status               VARCHAR2(250)
            );
           
    TYPE number_of_users IS VARRAY(50) OF users;
   
    p_inputs    number_of_users  := number_of_users();
   
BEGIN
     p_inputs.extend(3);
   
     p_inputs(1).p_invoice_id :=  1111;
     p_inputs(1).p_approver  := 'AAAAAA';
     p_inputs(1).p_status := 'Active';

     p_inputs(2).p_invoice_id :=  2222;
     p_inputs(2).p_approver  := 'BBBB';
     p_inputs(2).p_status := 'Active';

     p_inputs(3).p_invoice_id :=  3333;
     p_inputs(3).p_approver  := 'CCCC';
     p_inputs(3).p_status := 'Active';

     FOR i in 1..p_inputs.count
     LOOP
    
        DBMS_OUTPUT.PUT_LINE('Test Condition : '|| i );
        DBMS_OUTPUT.PUT_LINE(CHR(13)||CHR(10));
        DBMS_OUTPUT.PUT_LINE('Input Parameters : ');
       
        DBMS_OUTPUT.PUT_LINE('Invoice ID :'||p_inputs(i).p_invoice_id);
        DBMS_OUTPUT.PUT_LINE('Approver :'||p_inputs(i).p_approver);
        DBMS_OUTPUT.PUT_LINE('Status :'||p_inputs(i).p_status);      
      
     END LOOP;  

END;
/
SPOOL OFF

Pass Multiple values to PL/SQL concurrent program

Here is small piece of code snippet which can be used in the select query to fetch the records for the multiple input parameter values entered for the concurrent program : 

Say for an Example : 

We have a field, 'Employee Number' as a parameter to PL/SQL concurrent program. Say, an user enters multiple values for this particular field( Employee Number : 100, 200, 300,400,500) and then concurrent program needs to process records only for the input values. 

Following is sample query to achieve the same : 

 SELECT * FROM employee xx
  WHERE status = 'A'
   AND INSTR(','||NVL(REPLACE(p_employee_number,' ',''),xx.employee_number)||',',',' ||TRIM(xx.employee_number)||',')>0;

This can be modified according to the requirements.

iExpense - Code to fetch Start and End Date field value in iexpense

Here is small code snippet to fetch the Start and End Date field values entered in the front end. This will be useful if there is need to validate the expense lines and check the expense start and end dates. There is standard hook package AP_WEB_CUST_DFLEX_PKG which provides custom procedure CustomValidateLine to validate the expense lines.


Declare
     l_expense_start_date   DATE;
     l_expense_end_date     DATE;

Begin

      l_expense_start_date :=
         fnd_date.canonical_to_date
                 (AP_WEB_DFLEX_PKG.GetCustomFieldValue('Start Date',
                                                        p_custom_fields_array
                                                       )
                 );

      l_expense_end_date :=
          fnd_date.canonical_to_date
                  (AP_WEB_DFLEX_PKG.GetCustomFieldValue('End Date',
                                                         p_custom_fields_array
                                                        )
                  );

End;
/