Under Maintenance
By PRajkumar on Nov 16, 2013
Saturday Nov 16, 2013
Tuesday Feb 05, 2013
Oracle External Bank Payment APIs
By PRajkumar on Feb 05, 2013
Here I will be sharing all APIs related to Oracle External Bank Payment.
Item Type
Author
1
2
Puneet Rajkumar
Puneet Rajkumar
Monday Feb 04, 2013
API to Create External Bank Branch
By PRajkumar on Feb 04, 2013
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
By PRajkumar on Feb 02, 2013
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
By PRajkumar on Jan 28, 2013
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
By PRajkumar on Jan 06, 2013
Here I will be sharing all APIs related to Oracle AP Supplier.
Item Type
Author
1
2
Puneet Rajkumar
Puneet Rajkumar
Wednesday Dec 19, 2012
API to Create AP Supplier
By PRajkumar on Dec 19, 2012
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
By PRajkumar on Oct 07, 2012
Here I will be sharing all FUSION OTBI related articles.
Item Type
Author
1
Puneet Rajkumar
Saturday Oct 06, 2012
OTBI vs. OBIA
By PRajkumar on Oct 06, 2012
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)
By PRajkumar on Sep 09, 2012
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
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
By PRajkumar on Aug 18, 2012
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
By PRajkumar on Aug 15, 2012
|
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
|
Trading Community Architecture (TCA)
By PRajkumar on Aug 15, 2012
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities.
Monday Aug 06, 2012
Entity Object Extension in OAF
By PRajkumar on Aug 06, 2012
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
By PRajkumar on Aug 04, 2012
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
Puneet Rajkumar
