Wednesday, February 21, 2018

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

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...