Saturday Nov 16, 2013

Under Maintenance

Tuesday Feb 05, 2013

Oracle External Bank Payment APIs

Here I will be sharing all APIs related to Oracle External Bank Payment.

Item Type

Author

1

2

API to Create External Bank

API to Create External Bank Branch

Puneet Rajkumar

Puneet Rajkumar

Monday Feb 04, 2013

API to Create External Bank Branch

API - IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH

Example --

 

 -- API to Create External Bank Branch

 

DECLARE
     p_api_version    NUMBER                   := 1.0;
     p_init_msg_list  VARCHAR2(1)          := 'F';
     v_bank_id           NUMBER                   := 530705; 
     x_return_status  VARCHAR2(2000);
     x_msg_count      NUMBER(5);
     x_msg_data        VARCHAR2(2000);
     x_branch_id        NUMBER;
     p_count               NUMBER;

     x_response         iby_fndcpt_common_pub.result_rec_type;
     p_ext_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;

BEGIN
      dbms_output.put_line ('BEFORE BANK BRANCH API');
  
      p_ext_bank_branch_rec.bch_object_version_number  := 1.0;
      p_ext_bank_branch_rec.branch_name        := 'TEST BANK BRANCH';
      p_ext_bank_branch_rec.branch_type          := 'ABA';
      p_ext_bank_branch_rec.bank_party_id      := v_bank_id;
 
     IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
     (    -- -----------------------------
          -- Input data elements
          -- -----------------------------
          p_api_version                  => p_api_version,
          p_init_msg_list                => p_init_msg_list,
          p_ext_bank_branch_rec  => p_ext_bank_branch_rec,
          -- --------------------------------
          -- Output data elements 
          -- --------------------------------
          x_branch_id                     => x_branch_id,
          x_return_status               => x_return_status,
          x_msg_count                   => x_msg_count,
          x_msg_data                     => x_msg_data,
          x_response                      => x_response
      );

     dbms_output.put_line ('x_branch_id = ' || x_branch_id);

     IF (x_msg_count = 1) 
     THEN
              dbms_output.put_line ('x_msg_data ' || x_msg_data);

     ELSIF (x_msg_count > 1)
     THEN
               LOOP 
                     p_count := p_count + 1; 
                     x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

                    IF (x_msg_data IS NULL)
                    THEN
                             EXIT;
                    END IF;

                   dbms_output.put_line ('Message' || p_count || ' ---' || x_msg_data);
               END LOOP; 
     END IF; 
  

     COMMIT;

EXCEPTION
             WHEN OTHERS THEN
                           ROLLBACK;

                           dbms_output.put_line(SQLERRM);
END;
/

SHOW ERR;

 

Saturday Feb 02, 2013

API to Create Bank

API - IBY_EXT_BANKACCT_PUB.create_ext_bank

Example --

 

 -- API to Create Bank


 

DECLARE
   lc_output                VARCHAR2(3000);
   lc_msg_dummy     VARCHAR2(3000);
   lc_return_status     VARCHAR2(3000);
   lc_msg_data           VARCHAR2(3000);

   ln_bank_id             NUMBER;
   ln_msg_count        NUMBER;
   lr_extbank_rec       apps.iby_ext_bankacct_pub.extbank_rec_type;
   lr_response_rec     apps.iby_fndcpt_common_pub.result_rec_type;


BEGIN
   lc_return_status    := '';
   ln_msg_count       := '';
   lc_msg_data          := '';
 
   lr_extbank_rec.bank_name       := 'Test Supp Bank';
   lr_extbank_rec.bank_number   := 'TSB0000001';
   lr_extbank_rec.country_code    := 'IN';

   apps.fnd_msg_pub.delete_msg(NULL);
   apps.fnd_msg_pub.initialize();

   IBY_EXT_BANKACCT_PUB.create_ext_bank
   (     
  -- ------------------------------
           -- Input data elements
           -- ------------------------------
           p_api_version       => 1.0,
           p_init_msg_list     => FND_API.G_TRUE,
           p_ext_bank_rec     => lr_extbank_rec,
           -- --------------------------------
           -- Output data elements
           -- --------------------------------
           x_bank_id            => ln_bank_id,
           x_return_status   => lc_return_status,
           x_msg_count       => ln_msg_count,
           x_msg_data         => lc_msg_data,
           x_response          => lr_response_rec
     );

     lc_output := ' ';

     IF (lc_return_status <> 'S')
     THEN
               FOR i IN 1 .. ln_msg_count
               LOOP
                            apps.fnd_msg_pub.get
                            (       i,
                                    apps.fnd_api.g_false,
                                    lc_msg_data,
                                    lc_msg_dummy
                             );
   
                            lc_output := lc_output ||
                                                  (TO_CHAR (i) ||
                                                                       ': '  ||
                                                   SUBSTR (lc_msg_data, 1, 250));
             END LOOP;

             apps.fnd_file.put_line
            (apps.fnd_file.output, 'Error Occured while Creating Bank: ');

       END IF;
 
     COMMIT;

EXCEPTION
          WHEN OTHERS THEN
                        ROLLBACK;

                        dbms_output.put_line(SQLERRM);
END;
/

 

SHOW ERR;

Monday Jan 28, 2013

API to Create AP Supplier Site

API - pos_vendor_pub_pkg.create_vendor_site

 

Example --


 -- API to Create Supplier Site

DECLARE
 l_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
 lc_return_status         VARCHAR2(10);
 ln_msg_count            NUMBER;
 lc_msg_data               VARCHAR2(1000);
 ln_vendor_site_id     NUMBER;
 ln_party_site_id         NUMBER;
 ln_location_id            NUMBER;

BEGIN
         -- ---------------
         -- Required 
         -- ---------------
        l_vendor_site_rec.vendor_id               := 40169;
        l_vendor_site_rec.vendor_site_code  := 'Supplier_Site';
        l_vendor_site_rec.address_line1         := '05 Main Street';
        l_vendor_site_rec.city                           := 'New York';
        l_vendor_site_rec.country                    := 'US';
        l_vendor_site_rec.org_id                      := 202;
 
        -- -------------- 
        -- Optional 
        -- --------------
        l_vendor_site_rec.purchasing_site_flag  :='N';
        l_vendor_site_rec.pay_site_flag                :='N'; 
        l_vendor_site_rec.rfq_only_site_flag       :='N';
 
        pos_vendor_pub_pkg.create_vendor_site
        ( 
              -- ------------------------------
              -- Input data elements
              -- ------------------------------
              p_vendor_site_rec    => l_vendor_site_rec,
              -- ---------------------------------
              -- Output data elements
              -- ---------------------------------
              x_return_status         => lc_return_status,
              x_msg_count             => ln_msg_count,
              x_msg_data                => lc_msg_data,
              x_vendor_site_id      => ln_vendor_site_id,
              x_party_site_id         => ln_party_site_id,
              x_location_id            => ln_location_id
        );
 
        COMMIT;

 EXCEPTION
         WHEN OTHERS THEN 
                       ROLLBACK; 
                       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/
SHOW ERR;

 

Sunday Jan 06, 2013

Oracle AP Supplier APIs

Here I will be sharing all APIs related to Oracle AP Supplier.

Item Type

Author

1

2

API to Create Supplier

API to Create Supplier Site

Puneet Rajkumar

Puneet Rajkumar

Wednesday Dec 19, 2012

API to Create AP Supplier

API - pos_vendor_pub_pkg.create_vendor

Example --

  

 -- API to Create Supplier

DECLARE
   l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;
   l_return_status   VARCHAR2(10);
   l_msg_count       NUMBER;
   l_msg_data         VARCHAR2(1000);
   l_vendor_id        NUMBER;
   l_party_id           NUMBER;

BEGIN
   -- --------------
   -- Required
   -- --------------
   l_vendor_rec.segment1          := '0000235916';
   l_vendor_rec.vendor_name   := 'TEST_SUPP';
 
   -- -------------
   -- Optional
   -- --------------
   l_vendor_rec.match_option  :='R';
 
   pos_vendor_pub_pkg.create_vendor
   (    -- -------------------------
        -- Input Parameters
        -- -------------------------
        p_vendor_rec      => l_vendor_rec,
        -- ----------------------------
        -- Output Parameters
        -- ----------------------------
        x_return_status   => l_return_status,
        x_msg_count       => l_msg_count,
        x_msg_data         => l_msg_data,
        x_vendor_id        => l_vendor_id,
        x_party_id           => l_party_id
   );
 
   COMMIT;
EXCEPTION
      WHEN OTHERS THEN
                   ROLLBACK;
                   DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/
SHOW ERR;

 

Sunday Oct 07, 2012

OTBI Developer Guide

Here I will be sharing all FUSION OTBI related articles.

Item Type

Author

1

OTBI vs OBIA

Puneet Rajkumar

Saturday Oct 06, 2012

OTBI vs. OBIA

 

What are the differences between OTBI and OBIA?

 

OTBI -- Oracle Transactional Business Intelligence

OBIA – Oracle Business Intelligence Applications

 

OBIA  

1. OBIA is the pre-packaged BI Apps that Oracle has provided for several years. It is the data warehouse based Solution

2. It is based on the Universal data warehouse design with different prebuilt adapters that can connect to various source application to bring the     data into the warehouse

3. It allows consolidating the data from various sources to bring them together

4. It provides a library of metrics that help to measure business

5. It provides set of predefined reports and dashboards

6. OBIA works for multiple sources including E-Business Suite, PeopleSoft, JDE, SAP and FUSION Applications

  

OTBI

1. It is a real time BI

2. There is no warehouse or ETL process for OTBI

3. It is a Fusion Apps only

4. OTBI leveraging the advanced technologies from both BI platform and ADF to enable the online BI queries against database directly

5. OTBI does not have prebuilt dashboards and reports like OBIA

 

Note:

Both OTBI and OBIA are available from same metadata repository. Some of the repository objects are shared between OTBI and OBIA. It was designed to allows to have following configuration:

 

OTBI Only

OBIA Only

OTBI and OBIA coexist 

 

Both OTBI and OBIA are accessing Fusion Apps via the ADF

Sunday Sep 09, 2012

Oracle Application in DMZ (Demilitarized Zone)

 Business Needs

Large Organizations want to expose their Oracle Application services outside their private network (HTTP/HTTPS and SSL). Usually these exposures must exist to promote external communication. So they want to separate an external network from directly referencing an internal network

 

Business Challenges

·         Business does not want to compromise with security information

·         Business cannot expose internal domain or internal URL information

 

Business Solution

DMZ is the solution of this problem. In Oracle application we can achieve this by following way –

 

·         Oracle Application consists of fleet nodes (FND_NODES) so first decide which node have to expose to public

·         To expose the node to public use the profile “Node Trust Level”

·         Set node to Public/Private (Normal -> private, External -> public)

·         Set "Responsibility Trust Level" profile to decide whether to expose Application Responsibility to inside or outside firewall

 

 

 

 

Solution Features  

·         Exposed web services can be accessed by both internal and external users

·         Configurable and can be very easily rolled out

·         Internal network and business data is secured from outside traffic

·         Unauthorized access to internal network from outside is prohibited

·         No need for VPN and Secure FTP server

 

Benefits 

·       Large Organizations having Oracle Application can expose their web services like (HTTP/HTTPS and SSL) to the internet without compromise with security information and without exposing their internal domain

 

Possible Week Points 

·         If external firewall is compromised, then external application server is also compromised, exposing an attack on E-Business Suite database

·         There’s nothing to prevent internal users from attacking internal application server, also exposing an attack on E-Business Suite database

 

Reference Links 

·         https://blogs.oracle.com/manojmadhusoodanan/tags/dmz

Saturday Aug 18, 2012

Compare Technical Terms in Oracle Apps with Real Time Example

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

 

Say Puneet is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers. The farmers are referred to as VENDORS / SUPPLIERS in Oracle Applications.

 

Puneet keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.


Puneet gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 KGS of grapes. In Oracle Apps, bags and KGS are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER.

 

Puneet maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

 

Say the fruits have been shipped to the customer Fruit Mart. Puneet now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments.

 

Puneet’s customer, Fruit Mart pays him in installments (partial payments). So Puneet has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Puneet receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Puneet to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

 

Puneet’s fruit business has begun to improve and has attracted more and more customers. As a result, Puneet decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY.

 

Due to increase in customers, Puneet needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Puneet pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

 

At the end of every month, Puneet prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

 

 

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, Oracle has very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

 

 

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has its own functionality trying to serve a business purpose. Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, and Fixed Assets etc.

Wednesday Aug 15, 2012

Oracle AP Invoice APIs

These APIs are handful when you want to do Insert, Update or Delete programmatically for some business requirements (rare cases!)

 

API Name

Procedure Name

Description

AP_AI_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_INVOICES_ALL

update_row

Updates a row in AP_INVOICES_ALL

delete_row

Deletes a row in AP_INVOICES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL

 

 

API Name

Procedure Name

Description

AP_AIL_TABLE_HANDLER_PKG

check_unique

Check the Uniqueness of a Row

insert_row

Inserts a row in AP_INVOICE_LINES_ALL

update_row

Updates a row in AP_INVOICE_LINES_ALL

delete_row

Deletes a row in AP_INVOICE_LINES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL

 

 

API Name

Procedure Name

Description

AP_AID_TABLE_HANDLER_PKG

check_unique

Check the Uniqueness of a Row

insert_row

Inserts a row in AP_INVOICE_DISTRIBUTIONS

update_row

Updates a row in AP_INVOICE_DISTRIBUTIONS

delete_row

Deletes a row in AP_INVOICE_DISTRIBUTIONS

 

 

API Name

Procedure Name

Description

AP_AIP_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_INVOICE_PAYMENTS

update_amounts

Update amounts in AP_INVOICE_PAYMENTS

 

 

API Name

Procedure Name

Description

AP_AC_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_CHECKS_ALL

update_row

Updates a row in AP_CHECKS

delete_row

Deletes a row in AP_CHECKS

update_amounts

Update amounts in AP_CHECKS

 

Trading Community Architecture (TCA)

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. 

 

 

Monday Aug 06, 2012

Entity Object Extension in OAF

In the exercise we have taken “Create Data Entry OAF Page“can be found @

https://blogs.oracle.com/prajkumar/entry/insert_data_oaf_page

 

We are going to extend InsertEO. Our business requirement is to add Validation on Column1. The length of entered data in Column1 should be more than 5 Characters.

 

 

1. FTP this project from application server and open in JDeveloper

 

2. Create a New Entity Object (EO)

Right click on InsertDemo > New > ADF Business Components > Entity Object

 

Name – ExtInsertEO

Package -- prajkumar.oracle.apps.fnd.insertdemo.schema.server

Extends -- prajkumar.oracle.apps.fnd.insertdemo.schema.server.InsertEO

 

 

 

Click on Next - > New from Table

 

 

Include all the attributes of parent EO

 

 

Next -> Next

 

Check Validation Method check box and Finish

 

 

3. Add following code in ExtInsertEOImpl.java

 

import oracle.apps.fnd.framework.OAException;
 
protected void validateEntity()
{
 super.validateEntity();

 String column1Value = getColumn1();

 if (column1Value.length() < 5)
 {
  String message = "Length of String" + " " + column1Value + "is less than 5 characters";
  throw new OAException(message, OAException.INFORMATION);
 }
}

 

4. Substitute your New EO with parent EO

Right click on InsertDemo > Project Properties > Business Components > Substitutions

In Available list select InsertEO and in Substitute list select New EO ExtInsertEO and click on Add and then Ok

 

 

5. After substitution import *.jpx

In our case it will modify InsertDemo.jpx at project location

i.e. -- D:\xxxx\jdevhome\jdev\myclasses

 

Open Command Prompt and go to following location of your project

D:\xxxx\jdevbin\oaext\bin

Use this Import Command to import jpx

 

 

6. Bounce the server

 

7. Verify the substitution has applied properly

Run InsertPG page and click on About this Page link

 

 

 

Expand Business Component References Details

Under that section click on InsertVO which is EO based [it should be ExtInsertEO based]

 

 

 

 

8. Congratulation you have successfully finished. Run Your InsertPG page and Test Your Work

 

 

 

Saturday Aug 04, 2012

End Date Responsibility for Oracle FND User

API - fnd_user_pkg.delresp

Example --

Consider FND_USER = ‘PRAJKUMAR’ having responsibility ‘Application Developer’

 

 

Let us try to END_DATE ‘Application Developer’ Responsibility with fnd_user_pkg.delresp API

-- -----------------------------------------------------------------
-- End Date Responsibility from Oracle FND User
-- -----------------------------------------------------------------
DECLARE
   v_user_name                   VARCHAR2 (100) := 'PRAJKUMAR';
   v_responsibility_name   VARCHAR2 (100) := 'Application Developer';
   v_application_name        VARCHAR2 (100) := NULL;
   v_responsibility_key        VARCHAR2 (100) := NULL;
   v_security_group              VARCHAR2 (100) := NULL;


BEGIN
   SELECT  fa.application_short_name,
                     fr.responsibility_key,
                     frg.security_group_key
    INTO        v_application_name,
                      v_responsibility_key,
                      v_security_group
    FROM      fnd_responsibility       fr,
                     fnd_application            fa,
                     fnd_security_groups  frg,
                     fnd_responsibility_tl   frt 
    WHERE  fr.application_id               = fa.application_id
     AND        fr.data_group_id             = frg.security_group_id
     AND        fr.responsibility_id          = frt.responsibility_id
     AND        frt.LANGUAGE                 = USERENV ('LANG')
     AND        frt.responsibility_name  = v_responsibility_name;

      fnd_user_pkg.delresp
      (    username           => v_user_name,
           resp_app            => v_application_name,
           resp_key             => v_responsibility_key, 
           security_group   => v_security_group 
      );

     COMMIT;

     DBMS_OUTPUT.put_line (   'Responsiblity '
        || v_responsibility_name
        || ' is removed from the user '
        || v_user_name
        || ' Successfully'
        );


EXCEPTION
     WHEN OTHERS THEN
              DBMS_OUTPUT.put_line
               (   'Error encountered while deleting responsibilty from the user and the error is '
                    || SQLERRM
                );
END;
/

After API Run --

 

About

Welcome to My Oracle World Puneet Rajkumar

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today