Barcode with BI publisher report

Barcode with BI publisher report

Step#1 Local Machine Font Setup

 

Install the font on the local machine where XML template is being developed.

For barcode I have used font C39P24DmTt (V100025_.TTF)

Download the font and place it under windows font directory (C:\WINNT\Fonts)

Step#2 Assign the barcode font to the field in XML template

 

Add a placeholder and change the font of it to C39P24DmTt, place the asterix (*) in prefix and suffix to the barcode field.

 

Step#3 uploading the font on server

 

A.   Font file creation

Login to BI publisher responsibility

Under Administration tab -> Font Files ->Create Font file

Name the font file as C39P24DmTtand upload the TTF file.

 

 

 

B.   Create Font Mapping

Login to BI publisher responsibility

Under Administration tab ->Font Mapping->Create Font Mapping

 

C.   assigning the font to the report template

Login to BI publisher responsibility

Templates -> Open the template of the concurrent program you wanted to put barcode -> Click on the edit configuration on the right corner You will see all the configurable options 

Finally run the report you shall see the barcode is being shown on the field

                          XML Publisher report handling special characters 

XML Publisher report handling special characters 

Problem definition- The standard report runs fine when the output format is "Text" but errors out when it "XML".  The XML is generated partially with error message like “The XML page cannot be displayed or Authentication failed.”

Solution-

Open the underlying RDF file and update the below value as

<?xml version="1.0" encoding="ISO-8859-1"?>

Once changed save the report , migrate it and test the XML output.

                                                      Code 128 Barcode with XML Publisher

STEP#1 XML Tags and template 

 

Create or use existing oracle reports concurrent program. Change the output format under concurrent program definition to XML, run the concurrent program and get the XML tags. Map the RTF based on the XML tags generated.

STEP#2 Getting barcode fonts 

 

For barcode I have used Code 128 from IDAutomation (you can download from their site)

After installing the font on local machine assign one of the IDAutomation font to the field in RTF template (for example an order number and font can be eg: IDAutomationSC128M)

STEP#3 Barcode font reading by handheld or scanner 

 

To make the barcode to be read by scanner, the encoder java code provided by vendor has to be compiled and convert it into a java class file.

A.      Below is the class code save the code in a notepad and rename it as BarcodeUtil.java

B.      Move the class file with EBSO file structure, put it under JAVA_TOP ($JAVA_TOP/oracle/apps/xdo/template/rtf/util)

C.      Compile the Java file with .class file as

$ cd $JAVA_TOP/oracle/apps/xdo/template/rtf/util  

$ javac -g BarcodeUtil.java  This command will generate the class file as BarcodeUtil.class.

Load Customers using TCA API

Load Customers using TCA API

Overview:

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. This document provides information about how to create a customer using TCA API. These APIs utilize the new TCA model, inserting directly to the HZ tables. 

Architecture


Create Organization

DECLARE

p_organization_rec   hz_party_v2pub.organization_rec_type;

x_return_status      VARCHAR2 (2000);

x_msg_count          NUMBER;

x_msg_data           VARCHAR2 (2000);

x_party_id           NUMBER;

x_party_number       VARCHAR2 (2000);

x_profile_id         NUMBER;

BEGIN

p_organization_rec.organization_name := ’erpschools’;

p_organization_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_v2pub.create_organization (‘T’,

p_organization_rec,

x_return_status,

x_msg_count,

x_msg_data,

x_party_id,

x_party_number,

x_profile_id

);

DBMS_OUTPUT.put_line (‘party id ‘ || x_party_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_parties table and one record in hz_organization_profiles table. Similarly you can call hz_party_v2pub.create_person to create a record in the HZ_PARTIES and one record in HZ_PERSON_PROFILES tables. 

Create a Location

DECLARE

p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

x_location_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

BEGIN

p_location_rec.country := ’US’;

p_location_rec.address1 := ’2500 W Higgins Rd’;

p_location_rec.address2 := ’Suite 920′;

p_location_rec.city := ’Thumuluru’;

p_location_rec.postal_code := ’60118′;

p_location_rec.state := ’IL’;

p_location_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_location_v2pub.create_location(

‘T’,

p_location_rec,

x_location_id,

x_return_status,

x_msg_count,

x_msg_data);

dbms_output.put_line(‘location id ‘||x_location_id);

dbms_output.put_line(SubStr(‘x_return_status = ‘||x_return_status,1,255));

dbms_output.put_line(‘x_msg_count = ‘||TO_CHAR(x_msg_count));

dbms_output.put_line(SubStr(‘x_msg_data = ‘||x_msg_data,1,255));

IF x_msg_count >1 THEN

FOR I IN 1..x_msg_count

LOOP

dbms_output.put_line(I||’. ‘||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

END LOOP;

END IF;

END

Note: The above API shall create an address record in hz_locations table.

Create a Party Site:

Use the organization_id and location_id created above and create a party site.

DECLARE

p_party_site_rec      hz_party_site_v2pub.party_site_rec_type;

x_party_site_id       NUMBER;

x_party_site_number   VARCHAR2 (2000);

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

BEGIN

p_party_site_rec.party_id := 1272023;

p_party_site_rec.location_id := 359086;

p_party_site_rec.identifying_address_flag := ’Y';

p_party_site_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site (‘T’,

p_party_site_rec,

x_party_site_id,

x_party_site_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (‘party site id ‘ || x_party_site_id);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_party_sites table.

Create Party Site Use

Use the above party site created

DECLARE

p_party_site_use_rec   hz_party_site_v2pub.party_site_use_rec_type;

x_party_site_use_id    NUMBER;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

BEGIN

p_party_site_use_rec.site_use_type := ’SHIP_TO’;

p_party_site_use_rec.party_site_id := 349327;

p_party_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_party_site_v2pub.create_party_site_use (‘T’,

p_party_site_use_rec,

x_party_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create a Contact Point

DECLARE

p_contact_point_rec   hz_contact_point_v2pub.contact_point_rec_type;

p_edi_rec             hz_contact_point_v2pub.edi_rec_type;

p_email_rec           hz_contact_point_v2pub.email_rec_type;

p_phone_rec           hz_contact_point_v2pub.phone_rec_type;

p_telex_rec           hz_contact_point_v2pub.telex_rec_type;

p_web_rec             hz_contact_point_v2pub.web_rec_type;

x_return_status       VARCHAR2 (2000);

x_msg_count           NUMBER;

x_msg_data            VARCHAR2 (2000);

x_contact_point_id    NUMBER;

BEGIN

p_contact_point_rec.contact_point_type := ’PHONE’;

p_contact_point_rec.owner_table_name := ’HZ_PARTIES’;

p_contact_point_rec.owner_table_id := ’1272023′;

p_contact_point_rec.primary_flag := ’Y';

p_contact_point_rec.contact_point_purpose := ’BUSINESS’;

p_phone_rec.phone_area_code := ’650′;

p_phone_rec.phone_country_code := ’1′;

p_phone_rec.phone_number := ’506-7000′;

p_phone_rec.phone_line_type := ’GEN’;

p_contact_point_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_contact_point_v2pub.create_contact_point (‘T’,

p_contact_point_rec,

p_edi_rec,

p_email_rec,

p_phone_rec,

p_telex_rec,

p_web_rec,

x_contact_point_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create an Org Contact:

DECLARE

p_org_contact_rec   hz_party_contact_v2pub.org_contact_rec_type;

x_org_contact_id    NUMBER;

x_party_rel_id      NUMBER;

x_party_id          NUMBER;

x_party_number      VARCHAR2 (2000);

x_return_status     VARCHAR2 (2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2 (2000);

BEGIN

p_org_contact_rec.department_code := ’ACCOUNTING’;

p_org_contact_rec.job_title := ’ACCOUNTS OFFICER’;

p_org_contact_rec.decision_maker_flag := ’Y';

p_org_contact_rec.job_title_code := ’APC’;

p_org_contact_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_org_contact_rec.party_rel_rec.subject_id := 16077;

p_org_contact_rec.party_rel_rec.subject_type := ’PERSON’;

p_org_contact_rec.party_rel_rec.subject_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.object_id := 1272023;

p_org_contact_rec.party_rel_rec.object_type := ’ORGANIZATION’;

p_org_contact_rec.party_rel_rec.object_table_name := ’HZ_PARTIES’;

p_org_contact_rec.party_rel_rec.relationship_code := ’CONTACT_OF’;

p_org_contact_rec.party_rel_rec.relationship_type := ’CONTACT’;

p_org_contact_rec.party_rel_rec.start_date := SYSDATE;

hz_party_contact_v2pub.create_org_contact (‘T’,

p_org_contact_rec,

x_org_contact_id,

x_party_rel_id,

x_party_id,

x_party_number,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note: The above API creates a record in hz_org_contacts table and one record in hz_relationships table. When a contact is created, a record in hz_parties table gets created with party_type as ‘PARTY_RELATIONSHIP’.

Create a Customer Account:

DECLARE

p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;

p_person_rec             hz_party_v2pub.person_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profilerec_type;

x_cust_account_id        NUMBER;

x_account_number         VARCHAR2 (2000);

x_party_id               NUMBER;

x_party_number           VARCHAR2 (2000);

x_profile_id             NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_account_rec.account_name := ’John”s A/c’;

p_cust_account_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

p_person_rec.person_first_name := ’John’;

p_person_rec.person_last_name := ’Smith’;

hz_cust_account_v2pub.create_cust_account (‘T’,

p_cust_account_rec,

p_person_rec,

p_customer_profile_rec,

‘F’,

x_cust_account_id,

x_account_number,

x_party_id,

x_party_number,

x_profile_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Note:

This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party.

Customer profile record in the HZ_CUSTOMER_PROFILES can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization.

Create a Customer Account Site

Use an existing Party Site

DECLARE

p_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;

x_return_status        VARCHAR2 (2000);

x_msg_count            NUMBER;

x_msg_data             VARCHAR2 (2000);

x_cust_acct_site_id    NUMBER;

BEGIN

p_cust_acct_site_rec.cust_account_id := 3472;

p_cust_acct_site_rec.party_site_id := 1024;

p_cust_acct_site_rec.LANGUAGE := ’US’;

p_cust_acct_site_rec.created_by_module := ’TCA-EXAMPLE’;

hz_cust_account_site_v2pub.create_cust_acct_site (‘T’,

p_cust_acct_site_rec,

x_cust_acct_site_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

Create Customer Account Site Use Code:

DECLARE

p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;

p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;

x_site_use_id            NUMBER;

x_return_status          VARCHAR2 (2000);

x_msg_count              NUMBER;

x_msg_data               VARCHAR2 (2000);

BEGIN

p_cust_site_use_rec.cust_acct_site_id := 3580;

p_cust_site_use_rec.site_use_code := ’INV’;

p_cust_site_use_rec.LOCATION := ’TCA’;

p_cust_site_use_rec.created_by_module := ’ERPSCHOOLS_DEMO’;

hz_cust_account_site_v2pub.create_cust_site_use (‘T’,

p_cust_site_use_rec,

p_customer_profile_rec,

”,

”,

x_site_use_id,

x_return_status,

x_msg_count,

x_msg_data

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line (‘x_msg_count = ‘ || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line (SUBSTR (‘x_msg_data = ‘ || x_msg_data, 1, 255));

IF x_msg_count > 1

THEN

FOR i IN 1 .. x_msg_count

LOOP

DBMS_OUTPUT.put_line

(   i

|| ’. ‘

|| SUBSTR

(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),

1,

255

)

);

END LOOP;

END IF;

END;

More Customer API’s:

Org Contact Role	Hz_party_contact_v2pub.Create_Org_Contact_Role
Relationships	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE
Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB. create_customer_profile
Customer Profile Amount	HZ_CUSTOMER_PROFILE_V2PUB. create_cust_profile_amt
Customer Credit Rating	HZ_PARTY_INFO_V2PUB.create_credit_rating
Sales Person	JTF_RS_SALESREPS_PUB.CREATE_SALESREP
Sales reps Territories	JTF_RS_SRP_TERRITORIES_PUB.CREATE_RS_SRP_TERRITORIES
Customer contacts	HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE
Customer Contact Role	HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility