Oracle applications - Surendranath Subramani: 2016

Monday, November 28, 2016

How to create BPEL flow with Business Rule


In this article we will see how to create simple BPEL process and use Business rule.

Use Case:


Payload has Employee name and Salary. Based on Salary want to return what type of rule Employee can be.

For instance 
if the employee salary is between 0 to 50 then their role would be JUNIOR
if the employee salary is between 50 to 100 then their role would be ANALYST
if the employee salary is greater than 100 then their role would be SENIOR

Below attached video show how to define simple BPEL process with Business rule which satisfies above mentioned use case.


Also attaching source code.

Download source code





Friday, August 5, 2016

Oracle Purchase Requisition (iProcurmenet) Approval notification Header information #HDR - How it works

Published date: 04-Aug-2016

Purpose:
The purpose of this article is to understand how to display custom header information on the workflow notification.

Let us take an example.
In Oracle Purchase Requisition module when requisition is set for approval the approval notification has below 3 header information how only these 3 information’s only displayed.

·         Requisition header description
·         Requisition total
·         Non-recoverable Tax




Ok, will look into the details now.

Open the workflow and go to notification and you will see attribute with the internal name #HDR as shown below.

The attributes with internal name that start with #HDR will be automatically enabled in notification header.
In below screen if you notice requisition description is having internal name #HDR_1


Now that you have understood how it is working I am going to show you simple example with source code attached at end of this article so you can implement at your environment and run the sample workflow to see the result.

Step: 1
a.       Create simple workflow
b.      Create attribute (internal name should be #HDR_1)
c.       Move the attribute to message (Send Message)
d.      Execute the workflow
a.       I have created package and plsql block to execute the workflow which is also attached at the end of the article.





You will find the Header description available at the header portion of the workflow notification.



Code can be downloaded using below link



Wednesday, July 6, 2016

Oracle workflow add additional button on notification

Purpose:

In this article we will see how to add additional buttons on the workflow notification.

This can be achieved using workflow customization.


Steps involved:

a. Take back of existing workflow
b. Create new lookup
c. Attached lookup to notification (result type)
d. Save the workflow
e. Deploy changes








Example:

Since you now have understood how custom buttons are displayed.
To make it even better have created simple WFT for you to understand.

The script contains
1. Package creation (Click here to download)
2. Workflow (Click here to download)
3. Run Workflow (Click here to download)



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE PACKAGE XX_TEST_WF_PKG
AS

   PROCEDURE LAUNCH_WORKFLOW (itemtype   IN VARCHAR2,
                              itemkey    IN VARCHAR2,
                              process    IN VARCHAR2);
END XX_TEST_WF_PKG;
/

CREATE OR REPLACE PACKAGE BODY XX_TEST_WF_PKG
AS

   PROCEDURE LAUNCH_WORKFLOW (itemtype   IN VARCHAR2,
                              itemkey    IN VARCHAR2,
                              process    IN VARCHAR2)
   AS
      l_edit_req_url   VARCHAR2 (1000);
   BEGIN
      WF_ENGINE.Threshold := -1;

      WF_ENGINE.CREATEPROCESS (itemtype, itemkey, process);
      wf_engine.setitemattrtext (itemtype   => itemtype,
                                 itemkey    => itemkey,
Download script to view complete code



Thanks for visiting my blog.

Monday, May 30, 2016

Enable paperclip (attachment) icon on core forms in Oracle application

In this article we will see how to enable paperclip icon  or in other words attachment icon on core form.


Go to Application developer >> Attachments >> Attachment Function.
Query the form/Function and enable "Include in Indicator"


Sunday, May 15, 2016

BI/XML Publisher Installation issue

Purpose:


In this article we will discuss the common issue you face during BI/XML publisher installation.

Unable to install:


Anytime during the install, if you find any problem you can get to the issue by looking at the log file generated on your machine.


C:\Users\<<&username>>\appdata\Local\Temp
TemplateBuilder.log
TemplateBuilderInstaller.log

&username = Substitute with your user name created on your machine. 


Access Denied:


When you open RTF file you will get access denied error when trying to import xml file or while you run the report in preview mode.



To fix the problem open the document as admin.



Unable to preview:

While you try to preview the report locally on your machine you would see issue in running the report. It could be because of space issue.

To fix the problem extended the space of the xml publisher by using below step.
  1. Open office word with admin privilege
  2. Go to 'Add-Ins' >> Tools >> Option 
  3. Priview tab >> Java option >> change to -Xmx512M
  4. Under Java Home >> make sure java version is set properly.




Unable to see add-ins:


After installing publisher, some time you will not see 'Add-In' tab on the office word.
If that occur you need to add TemplateBuilder.dot manually in your add-in.

How to create add-in is available in other blogs. Please search in google to add TemplateBuilder.dot

TemplateBuilder.dot is available under below location

C:\Users\<<username>>\appdata\Roaming\Microsoft\Word\STARTUP

Saturday, May 14, 2016

Credit Card transaction import into Oracle Internet Expense (IE)

Purpose:
In this article we will see how to import credit card transaction sent by bank into Oracle Internet Expense application.

You need to work with bank to determine the format of the file.
There are 2 options available, either go with custom format or industry standard file format.

Let’s see the formats in detail:

Custom format:
You can define your own control file and load the data using sql loader utility. This process would take time as it involves defining the format, have bank follow the format, testing, etc.

Industry standard means:
Oracle has built-in credit card loader program that are accepted by most banks. The list of oracle loader program are given at the end of this article.

Things to note:
There is a loader program and loader & validate program. The difference between these 2 are discussed below:

Loader
Loader & validate
Just load the transaction in AP_CREDIT_CARD_TRXNS_ALL table
Will load transaction in AP_CREDIT_CARD_TRXNS_ALL table and perform validate.
Validation program need to run separately to validate the transaction
No need to run validate program as the transaction was already validated at the time of load
Loader program use sql loader utility to load the data
Java program is used to load and validate.
Easy to customize
As it use Java program is it difficult to customize



Demonstration on how to load the credit card data and import into IE:

Assumption: all the setup required for credit card transaction to load are setup properly. 
To know more about the setup please visit my article: 

http://oracleappssuren.blogspot.com/2014/12/credit-process-applicable-for-r12-setup.html

  1. Import the file sent by bank and run loader program. 
  2. For testing purpose i am going to run below insert statement 
  3. Run 'Credit Card Transactions Validation Program'.
  4. Import credit card transaction by login into IE.
  5. Create new expense report
  6. Submit IE.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
INSERT INTO AP_CREDIT_CARD_TRXNS_ALL (TRX_ID,
                                      VALIDATE_CODE,
                                      payment_flag,
                                      CARD_PROGRAM_ID,
                                      EXPENSED_AMOUNT,
                                      CARD_NUMBER,
                                      card_id,
                                      REFERENCE_NUMBER,
                                      TRANSACTION_TYPE,
                                      TRANSACTION_DATE,
                                      TRANSACTION_AMOUNT,
                                      DEBIT_FLAG,
                                      BILLED_DATE,
                                      BILLED_AMOUNT,
                                      BILLED_DECIMAL,
                                      BILLED_CURRENCY_CODE,
                                      POSTED_DATE,
                                      POSTED_AMOUNT,
                                      POSTED_DECIMAL,
                                      POSTED_CURRENCY_CODE,
                                      CURRENCY_CONVERSION_EXPONENT,
                                      CURRENCY_CONVERSION_RATE,
                                      MIS_INDUSTRY_CODE,
                                      SIC_CODE,
                                      MERCHANT_REFERENCE,
                                      MERCHANT_NAME1,
                                      MERCHANT_NAME2,
                                      MERCHANT_ADDRESS1,
                                      MERCHANT_ADDRESS2,
                                      MERCHANT_CITY,
                                      MERCHANT_PROVINCE_STATE,
                                      MERCHANT_POSTAL_CODE,
                                      MERCHANT_COUNTRY,
                                      TOTAL_TAX,
                                      LOCAL_TAX,
                                      org_id,
                                      CREATION_DATE)
     VALUES (ap_credit_card_trxns_s1.NEXTVAL,
             'N',
             'N',
             10001,
             0,
             NULL,
             149391,                                 -- card number: 123456789
             '154132314259592SAUG06-5.6',
             '06',
             SYSDATE,
             1,
             '+',
             SYSDATE,
             1,
             2,
             'USD',
             SYSDATE,
             1,
             2,
             'USD',
             2,
             1,
             'FE',
             NULL,
             '00000000000',
             'ABC CARD FEE',
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             'US',
             0,
             0,
             83,
             SYSDATE);



Login to IE and create IE.
You will see if there are pending transaction to import.


You will see last 4 digit of your 16 digit credit card in the drop down. 
If you got multiple credit card then you can pick the credit card and import the transactions.


By default after importing the transactions will be categorized as Business (if you setup as both pay in the card program).










Credit Cards

Credit Card Transactions Loader Preformat program converts US Bank Visa transaction data into the correct format for loading into the AP_CREDIT_CARD_TRXNS_ALL table. This program is a prerequisite for US Bank Visa data only.

US Bank Visa Transaction Loader program imports US Bank Visa transaction data into the AP_CREDIT_CARD_TRXNS_ALL table. You must run the Credit Card Transactions Loader Preformat program on US Bank Visa data first before running this program.

Bank of America Visa Transaction Loader program imports Bank of America Visa TS2 format transaction data into the AP_CREDIT_CARD_TRXNS_ALL table. You must run the Credit Card Transactions Loader Preformat program on Bank of America Visa data first before running this program.

Diner's Club Transaction Loader program imports Diner’s Club transaction data into the AP_CREDIT_CARD_TRXNS_ALL table.

American Express Transaction Loader program imports American Express transaction data into the AP_CREDIT_CARD_TRXNS_ALL table.

Create Credit Card Issuer Invoice creates invoices for your credit card issuers in Oracle Payables. You only use this program if your company uses the Company Pay reimbursement policy.

Credit Card Outstanding Transactions Management (Details) program and report generates detailed information about the outstanding transactions of a particular credit card program.

Credit Card Outstanding Transactions Summary Report prints summary transaction information of a particular credit card for one or more employees.

Credit Card Outstanding Transactions Management (Aging) program and report generates aging information on outstanding transactions of a particular credit card for one or more employees, and generates notifications to employees and managers.

MasterCard CDF Transaction Loader and Validation Program imports the MasterCard transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. This program is for CDF version 2.0.

MasterCard CDF3 Transaction Loader and Validation Program imports the MasterCard transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. The program also imports detail transaction data. This program is for CDF version 3.0.

Diner’s Club Transaction Loader and Validation Program imports the Diner’s Club transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

US Bank Visa Transaction Loader and Validation Program imports the US Bank Visa transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

Visa VCF4 Transaction Loader and Validation Program imports Visa transaction data directly from Visa into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. The program also imports detail transaction data. This program is for Visa VCF3.0 and VCF4.0 formats.

American Express Transaction Loader and Validation Program imports the American Express transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

Credit Card Transactions Validation Program validates a card issuer’s credit card transaction data that is loaded into the AP_CREDIT_CARD_TRXNS_ALL table.

Credit Card Transactions Inactive Employees Process generates the Inform Manager of Inactive Employee Transactions workflow process.
When employees are terminated and the system date is past the Final Process date, Internet Expenses will not process terminated employee credit card transactions. Specifically, the Credit Card Transactions Inactive Employees Process program, and the Credit Card Outstanding Transactions programs will not process transactions of terminated employees. See: Terminating Employees. For Human Resources shared installs, this check is not applicable.

Credit Card Historical Transactions Management Program program deactivates unused credit card transactions and categorizes them as Historical. Once deactivated, the transactions are only viewable by users from the credit card transactions history page.

Credit Card Transactions Deactivation Report provides details of credit transactions that were affected by the Credit Card Historical Transactions Management program.



Thanks for visiting my blog!!!

Wednesday, May 4, 2016

An error occurred while attempting to receive the output arguments of the remote procedure. Function ncrorou returned error code 3010. The Applications File Server process may have exited abnormally. Please contact your system administrator

Problem description:

When I run BI/XML publisher report in Oracle EBS I get below error


An error occurred while attempting to receive the output arguments of the remote procedure. Function ncrorou returned error code 3010. The Applications File Server process may have exited abnormally. Please contact your system administrator

Reason:

There are possibility couple of reasons:
a.      The report size would be large (>1GB). Try running the report to generate limited records.
b.      Work filesytem was full, releasing space by deleting older files resolved issue.


Tuesday, April 5, 2016

PL/SQL Record type/ Table type IN OUT example

In pl/sql if you are technical analyst working with record type/table type would be little difficult.

So in this blog we will see how to define table type with IN and OUT put as most of the Oracle standard API are using table type with IN OUT parameter.

Create package with type xx_test_rec and make table type xx_test_tbl from xx_test_rec.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE OR REPLACE PACKAGE xx_type_test
AS
   TYPE xx_test_rec IS RECORD
   (
      p_id              NUMBER,
      req_num           VARCHAR2 (100),
      x_return_status   VARCHAR2 (2000),
      x_error_code      VARCHAR2 (2000),
      x_msg_data        VARCHAR2 (2000)
   );

   TYPE xx_test_tbl IS TABLE OF xx_test_rec
      INDEX BY BINARY_INTEGER;

   PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl);
END xx_type_test;

Create package body with input/output parameter. Loop input parameter and extract data from the input parameter, process the value and can assign value to output parameter.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/* Formatted on 4/5/2016 8:26:13 PM (QP5 v5.287) */
CREATE OR REPLACE PACKAGE BODY xx_type_test
AS
   PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl)
   AS
      l_req_tab   xx_test_tbl;
      l_req_rec   xx_test_rec;
      I           NUMBER;
   BEGIN
      l_req_tab := p_req_rec;


      I := l_req_tab.FIRST;

      WHILE I IS NOT NULL
      LOOP
         l_req_rec := l_req_tab (I);

         DBMS_OUTPUT.put_line ('output New 2.1 ' || l_req_rec.p_id);

         l_req_rec.x_return_status := 'S';
         l_req_rec.p_id := l_req_rec.p_id - 1;
         DBMS_OUTPUT.put_line ('output New 2.2 ' || l_req_rec.p_id);

         p_req_rec (I) := l_req_rec;

         I := l_req_tab.NEXT (I);
      END LOOP;
   END xx_proc;
END xx_type_test;




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE
   l_req_tab_in    xx_type_test.xx_test_tbl;
   l_req_tab_out   xx_type_test.xx_test_tbl;
   I               NUMBER;
BEGIN
   FOR i IN 1 .. 2
   LOOP
      l_req_tab_in (i).p_id := 3 + i;
   END LOOP;


   xx_type_test.xx_proc (l_req_tab_in);

   l_req_tab_out := l_req_tab_in;
   I := l_req_tab_out.FIRST;

   WHILE I IS NOT NULL
   LOOP
      DBMS_OUTPUT.put_line ('tax ' || l_req_tab_out (i).x_return_status);
      DBMS_OUTPUT.put_line ('id ' || l_req_tab_out (i).p_id);
      I := l_req_tab_out.NEXT (I);
   END LOOP;
END;

Monday, March 28, 2016

Oracle Catalog Purchase Requisition (PR)


Objective:

In this article we will go over
a. what is purchase category
b. what is the purpose
c. Create and configure category with PR.


Purchase category is used as consolidation for buyers to purchase the goods/services with less trouble while creating purchase requisition akka PR.

 

For example: if buyer want to procure Seat for VAN (ITEM NUMBER# 35156) in an automobile industry, it will be hard for buyer to memorize item number so in those case purchasing category helps.


Buyer can browser the category instead of item.

So in this example Automobile --> Van (Seat) can be browsed and buyer can pick items under VAN category and catalog purchase requisition can be created.

1) To define item category:

1. Go to Application developer responsibility, Application, Flexfield, Key, Segment
2. Query Flex field title: Item Categories
3. Define new structure
4. Define segments (you can add one or more segments as per client specification)

Pic: a

Pic: b

2) To define category set:

After defining new category structure, create category sets. You can define at master level or organization level so when category is assigned to item it can be either assigned to item at master organization Vs organization level.

a. Go to Inventory responsibility, setup, Item, Categories, Category sets
b. Provide name, description and other fields.
c. Create new category codes refer to pic:d.



pic: c






Pic: d

3) Assign items to Category:

Once category codes are defined it can be assigned to items. There are 2 ways to do.
1. In Category sets definition form you use assign option.
2. Categories can be assigned to item from ITEM definition form (see pic: e)

 
Pic: e


4) Default category assignment:

Once the category definition is completed it can be associated to application. So that other categories is not interrupted. For example: When you create catalog purchase requisition only the category defaulted to iprocurement module will be listed.
 
You can do that by going to Purchasing Super User, Setup, Items, Categories, Default Category sets.























Pic: f


5) Category Hierarchy:

The category hierarchy helps requesters browse for catalog items.
Browsing categories can be either a parent or child to another category, but cannot contain any items.


To define Category Hierarchy:

a. Go to iProcurement Catalog Administrator
b. Go to "Schema" tab, Category Hierarchy



Pic: g



6) Category mapping:

When purchase category is defined it is automatically mapping to shopping category. If they are not mapped make sure it is mapped so it can be shopped from catalog requisition.

To define Category mapping:

a. Go to iProcurement Catalog Administrator
b. Go to "Schema" tab, Category Mapping
c. Query the purchasing category and map to shopping category. If they are not mapped to shopping category it cannot be shopped.


Pic: h


7) Store:

So far we have seen category setups. Now if you need to refer the category to online shopping content. Catalog Administrators partition local catalog content into local content zones, define punchout or transparent punchout content zones, and then assign these content zones to stores.


Pic: I














Pic: J























Pic: K

8) Final result:

Go to iprocurement, store (catalog request), shopping categories, you can browse the categories and then followed by item and complete requisition process.



Pic: L


Thanks for reading my blog