Apex used to call the Oracle JOB scheduler and Java

• Create the html link on apex

• Ajax(Application Process) used to call the DBMS_SCHEDULER then generate the job name

• Ajax(Application Process) used to call the DBMS_SCHEDULER .CREATE_JOB then Execute the job name

Example : 
          select DBMS_SCHEDULER.GENERATE_JOB_NAME('hariharhomes') into jobnames from              dual;

          dbms_scheduler.create_job( job_name=>jobnames, job_type=>'executable',
          job_action=>'/u01/app/oracle/product/10.2.0/ias/Apache/Apache/images/mani.sh',                                enabled=>TRUE );

Report Generation from BI through APEX

• Oracle APEX is user friendly for designing part as well as client side data entry and validation related things.

• Oracle BI Publisher is good for report output; it will give the output files like PDF, XLS etc, Very big advantage in BI Publisher is that can produce multiple output files as a report. With the option of Bursting.

• Bursting will be scheduled with the particular time interval, or can schedule by the time as per the report need.

• Template file is base for required output report. Because output will be produced based on the input template design.

• In APEX, under Shared components there is an option called Report Layouts, that is getting the input template file for report designing.

• That same navigation having the link called Report Queries, which is getting the query from user to generating report based on template and query.

• When the user clicking on the designed button, then the Report file will be generated based on template and query.

• The button option should be Download printable Report Query, and then only that printable option will work.

Create Customize POPUP LOV for APEX

• Javascript Used to Get the Tabular Form Id

          Example :

          var eleTables = document.getElementsByTagName("table");

          for (var i=1;i

          var tId = eleTables[i].id;

          if(tId.substring(0,7)!=''){

          if(tId.substring(0,7)=='report_'){

          var originalId = i;

          var tableIds = eleTables[i].id;

          tableId = tableIds;

          return tableId;

         } } }

• Create the POPUP Image on tabular column

          Example :

          var link = document.createElement('a');

          var image = document.createElement('img');

          var span = document.createElement('span');

          link.setAttribute('href','javascript:genList0_f16_0("'+a1+'","'+filters+'")');

          link.className="dark";

          image.setAttribute('src','/i/list_gray.gif');

• Customize the Window open

          Example :

          w = open("wwv_flow_utilities.gen_popup_list" +

          "?p_filter=" +

         "&p_name=" + escape(filters) + "&p_element_index=" + escape (a1) +

         "&p_form_index=" + escape('0') + "&p_max_elements=" + escape('') +

         "&p_escape_html=" + escape('') + "&p_ok_to_query=" + escape('YES') +

         "&p_flow_id=" + escape(appId) + "&p_page_id=" + escape(pageId) +

         "&p_session_id=" + escape(l_field) + "&p_eval_value=" + escape('') +

         "&p_translation=" + escape('NO') + "&p_lov_checksum=Dynamicchecksum

         "winLov", "Scrollbars=1,resizable=1,width=400,height=450");

         if (w.opener == null) w.opener = self; w.focus();

         Based on Custom POPUP(Head No Column) append the value to Select list(Product) and read             only column(Description and Drawing No)

         

         Here Return value(M2595) also customized then return to Tabular Column.

Dynamically Generate the Checksum id to solve the apex vulnerability problem

• Ajax(Application Process) used call the Oracle Query of APEX_ITEM.MD5_CHECKSUM
• Java method of Math.random used to get Random no
• Random no used to get the Attribute value from Select list

          Example : 

          thisObj = objs[rand_no];

          if(thisObj){

          checksumId=thisObj.getAttribute('value');

          return checksumId;

• Apex Generate the checksum id per day only one time, I have customized and generated the checksum id per each page refresh.

• Here show the checksum id screenshot,

To put the Company logo in each APEX page

• Go to Shared Components then click the images link and upload the image to application.

• Click the Definition write the image name to logo text field as #WORKSPACE_IMAGES#company_logo.gif

• Go to Page Template and add the LOGO prefix at table

BI Bursting

• One of the very good features of Oracle BI Publisher is its ability to burst and send the reports to multiple users simultaneously.

• Using BI Publisher's bursting feature we can split a single batch report into individual reports to be delivered to multiple destinations. We can apply a different template, output format, delivery method, and locale to each split segment of our report.

• Example implementations include:

1. Invoice generation and delivery based on customer-specific layouts and delivery preference
2. Financial reporting to generate a master report of all cost centers, bursting out individual cost center reports to the appropriate manager
3. Generation of pay slips to all employees based on one extract and delivered via e-mail

Enabling a Report for Bursting

• Prerequisite
• A report defined in BI Publisher. The report data must contain an element by which the report will be split and an element by which the report will be delivered.
• Enabling a report for bursting consists of the following steps:
• Open the report in Edit mode.
• Select Bursting under the report definition.
• Select the Enable Bursting check box.
• Select the Split By and Deliver By elements.

• The Split By element is the data element from the report file that you wish to split the report by. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_NAME.

• The Deliver By element is the data element from the report file by which to determine the delivery method.

• In the invoice example, it is likely that each invoice will have delivery criteria determined by customer; therefore the Deliver By element may be CUSTOMER_ID.

• Select the data source for the delivery XML.

• The delivery XML can be sourced from the same data source as the main data set, or it can be generated from a different data source.
• Enter the SQL query to build the delivery XML. See Defining the Delivery Data Set for details.

• Defining the Delivery Data Set
• Based on the SQL query that we provide on the Bursting criteria page of the Report Editor, BI Publisher will build the delivery XML data set.
• The delivery XML data set contains the information to deliver the burst report appropriately to each recipient. The delivery data in this XML document is used as a mapping table for each Deliver By element. The structure of the delivery XML is as follows:

• Where
• KEY is the Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data. TEMPLATE - is the name of the Layout template to apply. Note that the value is the Layout name (for example, "Invoice"), not the template file name (for example, invoice.rtf).

• TEMPLATE_FORMAT - is the format of the layout template. Valid values are:
• RTF
• PDF
• ETEXT
• XLS_FO

• LOCALE - is the template locale, for example, "en-US".
• OUTPUT_FORMAT - is the output format. Valid values are: for example: pdf, html, excel.
• HTML
• PDF
• RTF
• EXCEL

• DEL_CHANNEL - is the delivery method. Valid values are:
• EMAIL
• FAX
• FILE
• FTP
• PRINT
• WEBDAV

• Delivery parameters by channel. The delivery parameters by channel are defined in the following table:

• Parameter Mapping

Table Partitioning

• Every data’s should be stored in database, because world can’t run without storing the data’s in the database. 

• Machinery world should store data’s in database for future reference and process, so the database will be filled up of data’s. 

• The records will be more and more and the size of the data might be Tera bytes of ranges. These databases are known as Very Large Databases (VLDB). 

• Oracle has provided the feature of table partitioning i.e. we can partition a table according to some criteria.

          For example we have an EMPLOYEE table with the following structure,
          Suppose this table contains millions of records, but all the records belong to four years only                i.e.  2007, 2008, 2009 and 2010. And most of the time we are concerned about only one or two            years i.e. we give queries like the following

          Employee Table Structure 
         
          Column           Data Type 
         
          Emp_id           Number(4)
          Emp_Name     Varchar2(10)
          Salary              Number(10,2)

          select sum(salary) from sales where year=1991;
          select Emp_Name,sum(salary) from sales where year=1992
          Group by Emp_Name;

          Now whenever you give queries like this Oracle will search the whole table. If you partition               this table according to year, then the performance is improve since oracle will scan only a                   single partition instead of whole table.

         CREATING PARTITION TABLES

• To create a partition table gives the following statement

                   Create table employee(emp_id number(4),
                                             emp_name varchar2(10),
                                             salary number(10,2))
                   partition by range (year)
                   partition p1 values less than (2007) tablespace u1,
                   partition p2 values less than (2008) tablespace u2,
                   partition p3 values less than (2009) tablespace u3,
                   partition p4 values less than (2010) tablespace u4,
                   partition p5 values less than (MAXVALUE) tablespace u5;

•  In the above example employee table is created with 5 partitions. Partition p1 will contain          rows  of year 2007 and it will be stored in tablespace u1.

•  Partition p2 will contain rows of year 2008 and it will be stored in tablespace u2. Similarly p3 and p4 have in the respective partitions.
• In the above example if we don’t specify the partition p4 with values less than MAXVALUE,   then we will not be able to insert any row with year above 2010.

• Although not required, we can place partitions in different tablespaces. If we place partitions in different tablespaces then we can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.

          The above example the table is partition by range.

• In Oracle we can partition a table by using the following partition methods,

1.          Range Partitioning
2.          Hash Partitioning
3.          List Partitioning
4.          Composite Partitioning

      ALTERING PARTITION TABLES

• To add a partition, we can add a new partition to the "high" end (the point after the last existing      partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

• For example to add a partition to employee table give the following command

         alter table employee add partition p6 values less than (2008);

• To add a partition to a Hash Partition table give the following command.

         Alter table employee add partition;

• Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.

• To add a partition by user define name and in your specified tablespace give the following command.

          Alter table employee add partition p5 tablespace u5;

• To add a partition to a List partition table give the following command.

          alter table employee add partition emp_name
         values (‘JONES’,’SMITH’);

• Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

Creating the Customer in Oracle Fusion Applications

Creating the Customer in Oracle Fusion Applications

Please find the below setups for Creating the Customer

Step 1: Login as Application User

Step 2: you will be in home page as below

Step 3: we need to navigate to Functional Setup Manager, click on User Name and you will be able to see Setup and Maintenance option it will navigate you to functional setup manger.

Step 4: below is the functional setup manager window, we could able to see all the offerings as provisioned and enabled

 Step 5: we could see Financial’s Offering as provisioned and enabled,  we need to  click on Implementation projects button in the below screenshot.

Step 6: Search for our Project in the search window

Step 7: Click on project name (hyperlink), it will open the project page

Step 8: Search the Task Create the Customer

 Step 9: Below Screen shot we can click on GO to Task Icon

Step10: Below Customer Screen page we can enter the required information for creating the Customer Save and close

How to Prepare the OTBI Reports in Oracle Fusion Financials

How to Prepare the OTBI Reports for Invoice Data

Please find the below setups for Preparing the OTBI Reports

Step 1: Login as Application User

Step 2: you will be in home page as below

 Step 3: we need to navigate to Functional Setup Manager, click on User Name and you will be able to see Setup and Maintenance option it will navigate you to functional setup manger.

Step 4: below is the functional setup manager window, we could able to see all the offerings as provisioned and enabled

 Step 5: we could see Financial’s Offering as provisioned and enabled,  we need to  click on Implementation projects button in the below screenshot.

Step 6: Search for our Project in the search window

 Step 7: Click on project name (hyperlink), it will open the project page

Step 8: Go to the Navigator and click on Reports and Analytics

 Step 9:In the below screen shot we can click on Analysis option it will display the list of Subject areas where we can select and create the OTBI Reports

Step10: In the Below screen we can select the respective Subject area and click create button.Here I am selecting the Payable Invoices -Transnational Real time

The subject area ends with Real time only we can build the OTBI Reports

 Step10:  Below Screen shot we can Expand  Payable Invoices Real Time Transactions and again we can expand the Business Unit and then select the BU and Drag the Right side.

Step11: Similar way we can select the Supplier, Supplier Site and Invoice

Step12 : After selecting the Required data in the Report we can click on next button

  

Step 13:  In the below screen we can enter the Report Name and Report Layout and then click on Next Button

Step 14:After click on next button it will display report in below screen shot and click on next button 3 times

Step 15: Below screen shot we can enter the Report Analysis name and enter the Folder name  and click on Finish Button.

 Step 16: After finishing the Report it will display the below image

Step 17: Expand My folder and again expand the Report which was given at the time report name given and again we can click on Report Analysis name it will display the detail below image. If we click on View the report data will display.

Creating With holding Tax Invoice in Oracle Fusion Applications

Creating With holding Tax Invoice in Oracle Fusion Applications

Please find the below setups for Creating the Customer

Step 1: Login as Application User

Step 2: you will be in home page as below

Step 3: we need to navigate to Functional Setup Manager, click on User Name and you will be able to see Setup and Maintenance option it will navigate you to functional setup manger.

Step 4: below is the functional setup manager window, we could able to see all the offerings as provisioned and enabled

 Step 5: we could see Financial’s Offering as provisioned and enabled,  we need to  click on Implementation projects button in the below screenshot.

Step 6: Search for our Project in the search window

Step 7: Click on project name (hyperlink), it will open the project page

  

Step 8:Creating With holding tax authority as Supplier

Go to Navigator select the Procurement offering and Click on Supplier

Step 9:Click on Create Supplier

Step10 :In the below screen we can enter the required information and click on Create button

Step 11:Select the Supplier Type as Tax Authority

  

Step 12:Supplier Site Address Creation:

Step 13:Supplier Site Creation: when creating the Supplier site we can enter all the required information.

Step 14: Creating With Holding Tax Calendar

Manage Payables Calendar go to Task option

Step 15:Click on the + Symbol for creating the WHT Calendar

 Step 16:Enter the Required information for Creating the WHT Calendar an click on Generate the periods so that system automatically system generate WHT Calendar for 12 periods like Jan to Dec

Step 17:Setup the WHT Options:

Go to Implementation project and search the task With Holding Tax options

Manage Tax Reporting and WHT Options click on Go to Task 

  
Step 18:Enter the Required information for WHT options and then save and close

Step 19:Creating the Tax Codes

Go to Implementation project and search the task Manage Tax Codes and Go to Task

Step 20: For Creating Tax codes enter all the required information and Save and Close 

Step 21:Creating the Tax Group:

Go to Implementation project and search the task Manage With holding Tax Classification and Go to Task

Step 22:Click on + Symbol for creating the Tax Group

Step 23:Enter the Required information and then save and close

Step 24:Assign Tax Group/ Tax Classification to Standard Supplier

Go to Navigator select the Procurement offering and Click on Supplier

Step 25:Click on manage Supplier

Step 25:Supplier Profile option and go to Income Tax options Enable the Check box Use WHT Group and Select the WHT Group in LOV and Save and close the screen

Step 25:Create the Standard Invoice

Go to Navigator select the Payable offering and Click on Invoices

Go to Task Icon and click on Create invoice

Step 26:Enter the Required information for creating the WHT Invoice and Validate it

Step 27:Query the Withholding Tax invoice and Validate it