Tuesday, June 30, 2015

Wrapping Utility - Part 2

The DBMS_DDL package wraps a single PL/SQL unit, such as a package specification, package body, function, procedure, type specification, or type body.

It contains WRAP function and the CREATE_WRAPPED procedure.

Example: WRAP Function

If pl/sql unit is small, you can directly embed PL/SQL code into another PL/SQL code to wrap and compile it

Execute below script and you will same result as we have seen in part 1.

declare
   sql_text_t   dbms_sql.varchar2a;
   sql_wrap_t   dbms_sql.varchar2a;
begin
   -- Store the pl/sql code in the array or pl/sql table
   -- Each line of code will go into a new row
   sql_text_t (1) := 'CREATE OR REPLACE FUNCTION get_sysdate RETURN VARCHAR2 AS ';
   sql_text_t (2) := 'BEGIN ';
   sql_text_t (3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
   sql_text_t (4) := 'END get_sysdate;';

   -- now compile and wrap the code
   sql_wrap_t := sys.dbms_ddl.wrap (ddl => sql_text_t,
                                    lb  => 1,
                                    ub  => sql_text_t.count
                                   );

   -- display each line of the wrapped code
   for i in 1 .. sql_wrap_t.count
   loop
      dbms_output.put_line (sql_wrap_t (i));
   end loop;
exception
   when others then
         dbms_output.put_line('Error: '||sqlerrm);
end;
/


Example: CREATE_WRAPPED procedure

Now if the PL/SQL unit is large having several thousand lines, in that case it is better to compile the code into the database first and then execute a PL/SQL code to wrap and compile it. 

Suppose you have a packaged procedure name XXAK_WRAP_TEST_PKG, already compiled in DB.

Describe this package


select text
  from all_source
 where name = 'XXAK_WRAP_TEST_PKG'
   and type = 'PACKAGE BODY'
   and owner = 'APPS';

Now use the CREATE_WRAPPED procedure to wrap this program unit.

Execute the below script to do the job

--
declare
   sql_text_t      dbms_sql.varchar2a;
   v_object_type   varchar2 (40);
   v_object_name   varchar2 (60)      := 'XXAK_WRAP_TEST_PKG';

   cursor c_package_body (v_package_name varchar2)
   is
      select text
        from all_source
       where name = v_package_name
         and type = 'PACKAGE BODY'
         and owner = 'APPS';

   cursor c_procedure (v_procedure varchar2)
   is
      select text
        from all_source
       where name = v_procedure
         and type = 'PROCEDURE'
         and owner = 'APPS';
begin
   begin
      select object_type
        into v_object_type
        from all_objects
       where object_name = v_object_name
         and status = 'VALID';
   exception
      when too_many_rows then
         -- It is recommended to wrap only package body, so that other user can see the specification and use the public part of it
         -- if it is more than one row object is having Specification and Body both
         v_object_type := 'PACKAGE BODY';
      when others then
         dbms_output.put_line('Error while Checking Object Type: '||sqlerrm);
   end;

   if (v_object_type = 'PROCEDURE' or v_object_type = 'FUNCTION') then

      open c_procedure (v_object_name);

      -- get each line of code into each array row, i.e. pl/sql table
      fetch c_procedure
      bulk collect into sql_text_t;

      close c_procedure;

   elsif v_object_type = 'PACKAGE BODY' then

      open c_package_body (v_object_name);

      fetch c_package_body
      bulk collect into sql_text_t;

      close c_package_body;

   end if;

   if sql_text_t.count > 0 then
      -- Code stored in the database does not contain the ddl text, create or replace, we have to add this explicitly
      sql_text_t (1) := 'CREATE OR REPLACE ' || sql_text_t (1);

      -- This will compile the PL/SQL unit again and finally wrap it
      dbms_ddl.create_wrapped (ddl  => sql_text_t,
                                   lb   => 1,
                                   ub   => sql_text_t.count
                                 );
   end if;
   dbms_output.put_line(v_object_name||' is wrapped now.');
exception
   when others then
      dbms_output.put_line('Error while wrapping: '||sqlerrm);
end;
/


Describe the package XXAK_WRAP_TEST_PKG again.


Done! :)

Wrapping Utility - Part 1

Wrapping is the process of hiding PL/SQL source code. It helps to protect your source code from others who might misuse it :)

Oracle has provided the WRAP utility to encrypt source code for this purpose. The WRAP utility can be invoked on the operating system, like Windows, command line as it comes with the Oracle client. It can also be invoked within the database using DBMS_DDL package (Part 2)

Normally PL/SQL code, such as a package specification, package body, function, procedure, type specification, or type body is wrapped using the wrap utility given by Oracle. 

Note: It does not wrap PL/SQL content in anonymous blocks or triggers or non-PL/SQL code 
This is a command line tool and syntax is:

wrap iname=input_file [oname=output_file]

input_file is the name of the file which is containing your source code, it can be with extension or without extension.

wrap iname=/mydir/myfile
wrap iname=/mydir/myfile.sql

output_file is the name of the wrapped file that is created. The defaults extension of output file  is .plb.

wrap iname=/mydir/myfile 
>  Output file will be myfile.plb

wrap iname=/mydir/myfile oname=/yourdir/yourfile.out


>Test: Create a test function Get_Sysdate and store it with name Get_sysdate.sql in your hard drive

Here, I have stored it in directory: D:\Wrapping Utility




Open Command Prompt > Start - Run – cmd - Enter

Goto the directory where you have stored your source file

cd  D:\Wrap Utility
run below command to wrap the file
wrap iname=Get_Sysdate

It will wrap the source code and create a new file Get_Sysdate with default extension .plb


Open the file, you can see the wrapped code.

Now compile the wrapped file


Now check the source code in DB

  select *
    from all_source
   where name = 'GET_SYSDATE'
     and owner = 'APPS';



Now execute the function

select get_sysdate
  from dual;



Thursday, June 4, 2015

Expense Report Stuck In 'AP approval process/Block' After Manager Approval

Few days back I got a situation where APEXP workflow failed for manager approval because manager had left the job and related role/user was not valid.

I then updated the workflow attributes with correct manager details and rewind it so it went to new manager and got approved but got stuck in 'AP approval process/Block' activity.

What 'Block' function does?
The Block function lets you pause a process until some external program or manual step completes.
You can not skip this activity.

I checked the ap_expense_report_headers_all table, coumn 'source' had value 'NonValidatedWebExpense' and column workflow_approved_flag had value 'M'

Before going further lets know about source/workflow_approved_flag columns values and meaning

Column: source

‘NonValidatedWebExpense’
Expense report transaction has been inserted into the expense report transaction tables but have not passed the Payables Validation Function in the Server Side Validation Process of the Expense Report Workflow process. This transaction is not viewable in the Oracle Payables Enter Expense Reports Window.

‘WebExpense’
Expense Report transactions has successfully passed the Payables Validation function in the Expense Report Workflow Server Side Validation Process and is viewable in the Oracle Payables Enter Expense Reports Window. Transaction has not completed the Expense Report Workflow yet.

‘XpenseXpress’
If Expense Report was created through the Web Interface (identified by the workflow_approved_flag), expense report transaction has successfully completed the Expense Report Workflow process and is ready to be picked up by Oracle Payables Invoice Import program.

‘SelfService’
Expense Report transaction has successfully completed the expense Report Workflow process and is ready to be picked up by Oracle Payables Invoice Import program.

Column: workflow_approved_flag

NULL
If Expense Report transaction is created from the web (check the source), then expense report has been submitted, but not been manager or payables approved. Expense Report transactions not created from the web will have workflow_approved_flag = NULL.

‘S’
Expense Report transaction has been saved and not yet fully completed or submitted.

‘I’
Implicit save, in OIE.K/R12 there is an implicit save when navigating from one page to another.

‘R’
Expense Report transaction has been Manager rejected.

‘M’
Expense Report transaction has been Manager approved.

‘P’
Expense Report transaction has been Payables approved.

‘A’
Expense Report transaction has been ‘Automatically’ approved, i.e. Manager approved but Payables approval not needed because expense report didn’t need payables to check for receipts or policy.

‘W’
Expense Report has been Withdrawn.

‘Y’

Expense Report transaction has been both Manager and Payables approved.

> Now when I checked the workflow, 'AP Audit Expense Report' process was completed with result 'Audit'. 'Block' activity was waiting this process to complete with result approved/auto approved.

>> Try to rewind the 'AP Audit Expense Report' process, if it does not work check with payables if any approval is pending.

OTL- Employee's Time Card Details

select hts.resource_id                      "Person id"
      ,to_char(hts.start_time,'Mon-DD-YYYY') "Start Date"
      ,to_char(hts.stop_time,'Mon-DD-YYYY')  "End Date"
      ,htb2.measure                          "Hours"
      ,petf.element_name                     "Hours Type"
      ,hta.attribute12                       "Description"
      ,htb.comment_text                      "Comments"
  from hxc_time_building_blocks  htb,
       hxc_time_building_blocks  htb1,
       hxc_time_building_blocks  htb2,
       hxc_time_attribute_usages htau,
       hxc_time_attributes       hta,
       hxc_timecard_summary      hts,
       pay_element_types_f       petf
 where htb1.parent_building_block_id= htb.time_building_block_id
   and htb1.parent_building_block_ovn = htb.object_version_number
   and htb.date_to  = hr_general.end_of_time
   and htb.scope    = 'TIMECARD'
   and htb1.scope   = 'DAY'
   and htb1.date_to = hr_general.end_of_time
   and htb2.parent_building_block_id= htb1.time_building_block_id
   and htb2.parent_building_block_ovn= htb1.object_version_number
   and htb2.scope   = 'DETAIL'
   and htb2.date_to = hr_general.end_of_time
   and htau.time_building_block_id  = htb2.time_building_block_id
   and htau.time_building_block_ovn = htb2.object_version_number
   and htau.time_attribute_id       = hta.time_attribute_id
   and hts.start_time  = htb.start_time
   and hts.resource_id = htb.resource_id
   and to_char(petf.element_type_id) = (substr(hta.attribute_category,11,length(hta.attribute_category)))
   and hts.resource_id                       = :p_person_id
   and to_char(hts.start_time,'DD-MON-YYYY') = :p_start_date
   and hta.attribute_category is not null
   order by htb1.start_time;

-- More Specific with element name

select *
  from (select tt.resource_id                 "Person id"
              ,rt.status_name                 "Status"
              ,tt.start_date                  "Start Date"
              ,tt.stop_date                   "Stop Date"
              ,round (tt.hours_worked, 2)     "Hours"
              ,round (tt.hours_worked, 2) as hours
              ,td.detail_attribute12          "Description"
              ,substr(rt.timecard_comment,40) "Comments"
              ,(select alias_value_name
                  from hxc_alias_values_v
                 where alias_definition_id = 1051 -->>
                   and attribute1 =
                       substr (td.detail_attribute_category,11,length(td.detail_attribute_category)))
                       as ot_type
         from hxc_resource_total_time_v tt,
              hxc_resource_timecards_v rt,
              hxc_timecard_details_v td
        where tt.time_id = rt.timecard_id
          and tt.detail_id = td.detail_timecard_id
          and td.detail_bld_blk_info_type_id = 1
          and tt.resource_id   = :p_person_id  
          and to_char(tt.start_date,'DD-MON-YYYY') = :p_start_date
          and tt.start_date >=
              trunc((last_day (add_months (sysdate, -3)) + 1))) pivot                          (sum (hours) 
             for ot_type 
             in ('01. Half Time Hours (0.5)' "Half Time"
                ,'02. Single Time Hours (1.0)' "Single Time"
                ,'03. Time and a Quarter (1.25)' "Time And a Quarter"
                ,'04. Time and a Half (1.5)' "Time And a Half"
                ,'05. Double Time Hours (2.0)' "Double Time"
                ,'06. Time and three quarters (1.75)' "Time And Three Quarters"
                ,'07. EW - Premium @ 1/3 Third of Time' "Premium Third"
                ,'08. EW - Premium @ 1/2 Half of Time'  "Premium Half"
                ,'09. EW - Premium @ Single'  "Uremium Single"
                ,'10. EW - Unsocial Hours @ 1/5 Fifth of Time' "Unsocial"
               ));