How to run sql query in oracle cloud

Oracle BI Publisher is a powerful reporting tool that allows you to separate data sources (data model) from the data formats (report layout). The BIP engine can format any well-formed XML data, allowing integration with any system that can generate XML. BI Publisher can merge multiple data sources into a single output document.

In this article you can see how easy it is to use BIP to query any record from Fusion HCM Cloud instance. In the SaaS model you don’t have access to SQL tools such as SQL Developer to query the database and hence tools such as BIP can come very handy for troubleshooting issues or data reconciliation where you could write simple SQLs or reports to look at the data from database records. 

Use case: Run a Sql statement against PER_ALL_PEOPLE_F table. 

Navigation: There are two different ways to login to Oracle Fusion HCM BI Publisher

• Method1: If you know the direct URL for BIP then use that, it will be the simplest method to get to BIP. Here are few samples for BIP URLS so that you can derive the URL for your application based on your server details. 
  • https://HXYZ-TEST.bi.us2.oraclecloud.com/analytics/
  • https://bi-aufsn4x0POD.oracleoutsourcing.com/analytics/ (POD – Pod name)
• Method2: You can login to Fusion HCM Application and go to Tools -- Reports and Analytics, click on the Browse Catalog icon. This should open BIP UI in a new window.  

Create a new Data Model

Select the data source as SQL Query

Select the data source as ApplicationDB_HCM and then provide the SQL statement that you want to run

SQL Statement

View results \ Create new Report – If you only want to view the data for issue troubleshooting and all then you don’t really need to create any report and format. You could simply click on view data button and see the results. You can optionally save the data model in a shared folder so other team members can access the data model than having everyone create their own copy. 

Results

Supplier API in R12

Supplier API in R12

The Following API’s are used in oracle apps R12  to upload supplier data :

API Header : POS_VENDOR_PUB_PKG.CREATE_VENDOR ( parameters)

PROCEDURE Create_Vendor(
p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER
);

SCRIPT:

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


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 := '00002359'; --ID

l_vendor_rec.vendor_name := 'ABC'; --Supplier Name

--Optional

l_vendor_rec.match_option:='R' ;

 

pos_vendor_pub_pkg.create_vendor

( 

p_vendor_rec => l_vendor_rec, 

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;

 

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line('vendor_id: '||l_vendor_id);

dbms_output.put_line('party_id: '||l_party_id);

 

END;






 

API Sites: POS_VENDOR_PUB_PKG.CREATE_VENDOR_SITE( parameters)

PROCEDURE Create_Vendor_Site(

p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_vendor_site_id OUT NOCOPY NUMBER,

x_party_site_id OUT NOCOPY NUMBER,

x_location_id OUT NOCOPY NUMBER

);

Script:




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


DECLARE

 

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_return_status     VARCHAR2(10);

l_msg_count     NUMBER;

l_msg_data  VARCHAR2(1000);

l_vendor_site_id    NUMBER;

l_party_site_id     NUMBER;

l_location_id   NUMBER;

 

BEGIN

 

--Required

l_vendor_site_rec.vendor_id :='123456';

l_vendor_site_rec.vendor_site_code := 'xyz';

l_vendor_site_rec.address_line1 := 'A-602';

l_vendor_site_rec.country := 'IN';

l_vendor_site_rec.org_id := '80';

 

--Optional

 

l_vendor_site_rec.purchasing_site_flag:='Y';

l_vendor_site_rec.pay_site_flag :='Y';

l_vendor_site_rec.rfq_only_site_flag :='N';

l_vendor_site_rec.phone := '67615006';

 

pos_vendor_pub_pkg.create_vendor_site

(

p_vendor_site_rec => l_vendor_site_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_site_id => l_vendor_site_id,

x_party_site_id => l_party_site_id,

x_location_id => l_location_id

);

 

COMMIT;

 

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);

dbms_output.put_line('party_site_id: '||l_party_site_id);

dbms_output.put_line('location_id: '||l_location_id);

 

END;






 

Other procedures of this package are:

POS_VENDOR_PUB_PKG.Update_Vendor

POS_VENDOR_PUB_PKG.Validate_Vendor

POS_VENDOR_PUB_PKG.Update_Vendor_Site

POS_VENDOR_PUB_PKG.Validate_Vendor_Site

POS_VENDOR_PUB_PKG.Create_Vendor_Contact