http://www.learn-english-online.org/
http://www.rediff.com/getahead/2005/jan/27comm.htm
http://www.dictionary.tamilcube.com/index.aspx
http://www.lanka.info/dictionary/EnglishToSinhala.jsp
http://www.tamilpadi.com/?findme=abolitionist&foo=1&submit.x=46&submit.y=20&submit=submit
Monday, 27 June 2011
Friday, 24 June 2011
CUSTOMER API
2.3. Customer APIs
Prev Chapter 2. Customers Next
2.3. Customer API
2.3.1. Customer APIs
2.3.1.1. Create an 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 := ’ABC Corporation’;
p_organization_rec.created_by_module := ’TCA_EXAMPLE’;
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;
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.
2.3.1.2. 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 := ’300 Oracle Parkway’;
p_location_rec.address2 := ’13th Floor’;
p_location_rec.city := ’Redwood Shores’;
p_location_rec.postal_code := ’94065’;
p_location_rec.state := ’CA’;
p_location_rec.created_by_module := ’TCA_EXAMPLE’;
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
The above API shall create an address record in hz_locations table.
2.3.1.3. 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_V2PU
B.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 := ’TCA_EXAMPLE’;
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;
The above API creates a record in hz_party_sites table.
2.3.1.4. Create a Party Site UseUsing the above party site create a party site use.
DECLARE
p_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_RE
C_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 := ’TCA_EXAMPLE’;
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;
2.3.1.5. Create a Contact Point ( of type Phone)
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_coun t 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 := ’TCA_EXAMPLE’;
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;
2.3.1.6. 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 := ’TCA_EXAMPLE’;
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
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'.
2.3.1.7. 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_PROFILE REC_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 := ’TCA_EXAMPLE’;
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;
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.
2.3.1.8. Create a Cust Account Site using 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;
2.3.1.9. Create a Customer A/C 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 := ’TCA_EXAMPLE’;
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;
2.3.1.10. Other Customer related APIs
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
Copyright © 2000-2005 ConfluentMinds Pvt Ltd. All Rights Reserved. Unauthorized use, duplication or distribution is strictly prohibited. This is the proprietary information of ConfluentMinds Pvt Ltd. Use is subject to license terms.
Prev Home Next
A Techno-functional Guide to Oracle Applications - Supply Chain Management.Your feedback please.
Prev Chapter 2. Customers Next
2.3. Customer API
2.3.1. Customer APIs
2.3.1.1. Create an 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 := ’ABC Corporation’;
p_organization_rec.created_by_module := ’TCA_EXAMPLE’;
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;
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.
2.3.1.2. 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 := ’300 Oracle Parkway’;
p_location_rec.address2 := ’13th Floor’;
p_location_rec.city := ’Redwood Shores’;
p_location_rec.postal_code := ’94065’;
p_location_rec.state := ’CA’;
p_location_rec.created_by_module := ’TCA_EXAMPLE’;
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
The above API shall create an address record in hz_locations table.
2.3.1.3. 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_V2PU
B.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 := ’TCA_EXAMPLE’;
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;
The above API creates a record in hz_party_sites table.
2.3.1.4. Create a Party Site UseUsing the above party site create a party site use.
DECLARE
p_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_RE
C_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 := ’TCA_EXAMPLE’;
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;
2.3.1.5. Create a Contact Point ( of type Phone)
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_coun t 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 := ’TCA_EXAMPLE’;
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;
2.3.1.6. 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 := ’TCA_EXAMPLE’;
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
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'.
2.3.1.7. 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_PROFILE REC_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 := ’TCA_EXAMPLE’;
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;
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.
2.3.1.8. Create a Cust Account Site using 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;
2.3.1.9. Create a Customer A/C 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 := ’TCA_EXAMPLE’;
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;
2.3.1.10. Other Customer related APIs
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
Copyright © 2000-2005 ConfluentMinds Pvt Ltd. All Rights Reserved. Unauthorized use, duplication or distribution is strictly prohibited. This is the proprietary information of ConfluentMinds Pvt Ltd. Use is subject to license terms.
Prev Home Next
A Techno-functional Guide to Oracle Applications - Supply Chain Management.Your feedback please.
usha accenture resume
Usha
Technical Consultant—Oracle Apps
Summary:
• Having 4+ years of IT experience with around 4 years of experience in Oracle Applications 11i & R12.
• Hands-on work experience in Forms, Reports, Data Conversions, XML Publisher and Oracle Workflow.
• Knowledge on Procure to Pay and Order to Cash business cycle.
• Extensively worked on HRMS, INV, PO, AP, OM, AR, GL Modules.
• Having good knowledge on Workflow.
• Extensive experience in Application Development using Oracle SQL, PL\SQL, Forms and Reports and other related Technologies.
• Good exposure on Oracle AIM.
• Good team player with excellent analytical, technical, interpersonal and communication skills.
Education
B.Sc Computer Science from Bharathidasan University, Trichy.
Technical Skills:
ERP Package Oracle apps 11i / R12 (PO/AP/OM/AR/INV/GL/HRMS)
Databases Oracle 8i/9i
Database Tools PL/SQL Developer,TOAD 8.5,9.7,SQL* loader,appLOAD
Programming Languages C, C++,Java
Reporting Tools D2k forms,Reports,Discoverer 10g
Operating Systems Windows XP / 2000 / 98
ORACLE APPLICATIONS PROJECTS:
Project : TEMPO
Environment : Oracle Apps R12 (INV, OM, PO,AR, AP, GL,HRMS)
Role : Technical Consultant
Responsibilities:
• Requirement gathering for custom objects development
• Worked on HRMS BG Absence Duration Fast Formula
• Involved in Item Conversion and Purchase Order Conversion using open interfaces
• Involved in development of custom reports.
• Worked in Employee Payment Method creation and updation using APIs.
• Developed following XML publisher reports.
• Accounts Payable Trial Balance Report
• Employee Offer Letter Report
• Employee Confirmation Letter
• Invoice Register Report
• India Creditor Ledger Report
• Involved in MD70 Preparation for the custom objects.
• Done form personalization to populate “Custom Reference” column in Receipt Form.
Project : CEL
Environment : Oracle Apps 11.5.10.2 (INV, OM, PO,AR, AP, GL)
Role : Techno Functional Consultant
Responsibilities:
• Developed various custom reports in INV, Payables, and Receivables.
• Creating Value sets in various Modules of the application.
• Developed CEL Employee Details Report.
• Developed CEL Project wise Details Report.
• Developed CEL Return to Vendor Invoice details Report.
• Developed CEL GRR Supplierwise Report.
• Customization in CUSTOM.pll to make the process in receipt form to inspect all items.
• Preparation of Technical Design Documents
• Done a Form Personalization to make user should enter the task number for a particular organizations for subinventory transfer in inventory module.
Project : MWC
Environment : Oracle Apps R12 (INV, OM, PO,AR, AP, GL,PA)
Role : Technical Consultant
Responsibilities:
• Developed various reports in INV, Payables, receivables and GL
• Handled various production issues
• Coordinated with Oracle support for the raised SR
• Worked in form customization.
• Worked on Project Supplier Invoice workflow customization
• Done Self Declaration Report
• Preparation of Technical Design Documents
Project : appLOAD
Environment : Oracle 11.5.10.2 (AR,AP,GL,INV,PO,OM,HRMS)
Role : Technical Consultant
Responsibilities:
• Involved in analyzing and testing of Oracle Open Interfaces for data conversion of various modules.
• Involved in analyzing and testing of Oracle Standard APIs for data conversion of various modules.
• Creation of Data templates as per client requirements.
• Involved in creation of data validation and conversion procedures.
• Involved in creation of technical documentations.
• Involved in UAT and production support.
Project : GLOBAL PROTOTYPE INVENTORY REQUISTION SCHEDULING (GPIRS)
Role : Technical Consultant
Environment : Forms and Reports 6i / 10g
Responsibilities:
• Involved in Pre Migration Process.
• Involved in Post Migration Process.
• Involved in Form Customization and Report Development.
• Involved in Identifying And Fixing The Bugs.
• Involved in Technical Document Preparation After Migration.
Technical Consultant—Oracle Apps
Summary:
• Having 4+ years of IT experience with around 4 years of experience in Oracle Applications 11i & R12.
• Hands-on work experience in Forms, Reports, Data Conversions, XML Publisher and Oracle Workflow.
• Knowledge on Procure to Pay and Order to Cash business cycle.
• Extensively worked on HRMS, INV, PO, AP, OM, AR, GL Modules.
• Having good knowledge on Workflow.
• Extensive experience in Application Development using Oracle SQL, PL\SQL, Forms and Reports and other related Technologies.
• Good exposure on Oracle AIM.
• Good team player with excellent analytical, technical, interpersonal and communication skills.
Education
B.Sc Computer Science from Bharathidasan University, Trichy.
Technical Skills:
ERP Package Oracle apps 11i / R12 (PO/AP/OM/AR/INV/GL/HRMS)
Databases Oracle 8i/9i
Database Tools PL/SQL Developer,TOAD 8.5,9.7,SQL* loader,appLOAD
Programming Languages C, C++,Java
Reporting Tools D2k forms,Reports,Discoverer 10g
Operating Systems Windows XP / 2000 / 98
ORACLE APPLICATIONS PROJECTS:
Project : TEMPO
Environment : Oracle Apps R12 (INV, OM, PO,AR, AP, GL,HRMS)
Role : Technical Consultant
Responsibilities:
• Requirement gathering for custom objects development
• Worked on HRMS BG Absence Duration Fast Formula
• Involved in Item Conversion and Purchase Order Conversion using open interfaces
• Involved in development of custom reports.
• Worked in Employee Payment Method creation and updation using APIs.
• Developed following XML publisher reports.
• Accounts Payable Trial Balance Report
• Employee Offer Letter Report
• Employee Confirmation Letter
• Invoice Register Report
• India Creditor Ledger Report
• Involved in MD70 Preparation for the custom objects.
• Done form personalization to populate “Custom Reference” column in Receipt Form.
Project : CEL
Environment : Oracle Apps 11.5.10.2 (INV, OM, PO,AR, AP, GL)
Role : Techno Functional Consultant
Responsibilities:
• Developed various custom reports in INV, Payables, and Receivables.
• Creating Value sets in various Modules of the application.
• Developed CEL Employee Details Report.
• Developed CEL Project wise Details Report.
• Developed CEL Return to Vendor Invoice details Report.
• Developed CEL GRR Supplierwise Report.
• Customization in CUSTOM.pll to make the process in receipt form to inspect all items.
• Preparation of Technical Design Documents
• Done a Form Personalization to make user should enter the task number for a particular organizations for subinventory transfer in inventory module.
Project : MWC
Environment : Oracle Apps R12 (INV, OM, PO,AR, AP, GL,PA)
Role : Technical Consultant
Responsibilities:
• Developed various reports in INV, Payables, receivables and GL
• Handled various production issues
• Coordinated with Oracle support for the raised SR
• Worked in form customization.
• Worked on Project Supplier Invoice workflow customization
• Done Self Declaration Report
• Preparation of Technical Design Documents
Project : appLOAD
Environment : Oracle 11.5.10.2 (AR,AP,GL,INV,PO,OM,HRMS)
Role : Technical Consultant
Responsibilities:
• Involved in analyzing and testing of Oracle Open Interfaces for data conversion of various modules.
• Involved in analyzing and testing of Oracle Standard APIs for data conversion of various modules.
• Creation of Data templates as per client requirements.
• Involved in creation of data validation and conversion procedures.
• Involved in creation of technical documentations.
• Involved in UAT and production support.
Project : GLOBAL PROTOTYPE INVENTORY REQUISTION SCHEDULING (GPIRS)
Role : Technical Consultant
Environment : Forms and Reports 6i / 10g
Responsibilities:
• Involved in Pre Migration Process.
• Involved in Post Migration Process.
• Involved in Form Customization and Report Development.
• Involved in Identifying And Fixing The Bugs.
• Involved in Technical Document Preparation After Migration.
Thursday, 23 June 2011
Workflow Interview Questions
1.Explain about your workflow.
2.workflow is custom or standard.
2.Where to find the workflow error.
3.how to run the workflow in front end and back end.
4.what are base tables in workflow.
5.What are steps to customize the workflow?
6.How do you use attribute values in workflow messages?
7.What is item type and item key in workflow?
8.i am having one workflow notification error.then how to identify that workflow and
how to rectify that issue?
9.In which field to find the workflow name in wf_notifications? context
10.How do you use lookups in workflow?
11.what is lookups?
11.Tell me some workflow standard procedures?
12.What is wf_engine package used for?
13.How many processes can each workflow contain?
14.What is process in workflow?
15.What is the access level in workflow used for?
16.Where to set the access level
17.what is the default parameters in workflow stored procedure?itemtype,itemkey,actid,funcmode,resultout
18.Can you send html code in workflow notification?
WF_ITEM_TYPES
WF_LOOKUPS_TL
WF_NOTIFICATIONS
WF_NOTIFICATION_ATTRIBUTES
WF_MESSAGES
WF_MESSAGES_TL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES_TL
WF_PROCESS_ACTIVITIES
WF_USER_ROLE_ASSIGNMENTS
WF_USER_ROLES
WF_ROLES
WF_ITEMS
WF_ITEM_ATTRIBUTES
WF_ITEM_ATTRIBUTE_VALUES
WF_ITEM_ATTRIBUTES_TL
WF_ACTIVITIES
WF_ACTIVITIES_TL
WF_ACTIVITY_ATTRIBUTES
WF_ACTIVITY_ATTRIBUTES_TL
2.workflow is custom or standard.
2.Where to find the workflow error.
3.how to run the workflow in front end and back end.
4.what are base tables in workflow.
5.What are steps to customize the workflow?
6.How do you use attribute values in workflow messages?
7.What is item type and item key in workflow?
8.i am having one workflow notification error.then how to identify that workflow and
how to rectify that issue?
9.In which field to find the workflow name in wf_notifications? context
10.How do you use lookups in workflow?
11.what is lookups?
11.Tell me some workflow standard procedures?
12.What is wf_engine package used for?
13.How many processes can each workflow contain?
14.What is process in workflow?
15.What is the access level in workflow used for?
16.Where to set the access level
17.what is the default parameters in workflow stored procedure?itemtype,itemkey,actid,funcmode,resultout
18.Can you send html code in workflow notification?
WF_ITEM_TYPES
WF_LOOKUPS_TL
WF_NOTIFICATIONS
WF_NOTIFICATION_ATTRIBUTES
WF_MESSAGES
WF_MESSAGES_TL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES_TL
WF_PROCESS_ACTIVITIES
WF_USER_ROLE_ASSIGNMENTS
WF_USER_ROLES
WF_ROLES
WF_ITEMS
WF_ITEM_ATTRIBUTES
WF_ITEM_ATTRIBUTE_VALUES
WF_ITEM_ATTRIBUTES_TL
WF_ACTIVITIES
WF_ACTIVITIES_TL
WF_ACTIVITY_ATTRIBUTES
WF_ACTIVITY_ATTRIBUTES_TL
Item Interface
CREATE OR REPLACE PROCEDURE apps.xx_items_interface_723 (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
)
AS
v_template_id NUMBER;
v_organization_id Number;
CURSOR c1
IS
SELECT *
FROM xx_items_723;
BEGIN
FOR i IN c1
LOOP
BEGIN
SELECT template_id
INTO v_template_id
FROM mtl_item_templates
WHERE template_id = i.template_id;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_template_id error:' || v_template_id);
IF v_template_id IS NULL
THEN
DBMS_OUTPUT.put_line ('v_template_id:' || v_template_id);
END IF;
END;
Begin
select organization_id
into v_organization_id
From Org_organization_definitions
Where ORGANIZATION_ID=i.ORGANIZATION_ID;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_organization_id errir:' || v_organization_id);
If V_organization_id is Null
Then
DBMS_OUTPUT.put_line ('v_organization_id' || v_organization_id);
END IF;
End;
IF v_template_id IS NOT NULL and V_organization_id IS NOT NULL
AND i.set_process_id = 1
AND i.process_flag = 1
THEN
INSERT INTO mtl_system_items_interface
(process_flag, transaction_type, set_process_id,
organization_id, segment1, description,
template_id
)
VALUES (i.process_flag, i.transaction_type, i.set_process_id,
i.organization_id, i.segment1, i.description,
i.template_id
);
ELSE
DBMS_OUTPUT.put_line ('I.SET_PROCESS_ID' || i.set_process_id);
DBMS_OUTPUT.put_line ('I.PROCESS_FLAG' || i.process_flag);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('FILES LOADED');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('sqlerrm' || SQLERRM);
END;
/
select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);
DECLARE
ERRBUF VARCHAR2(200);
RETCODE VARCHAR2(200);
BEGIN
ERRBUF := NULL;
RETCODE := NULL;
APPS.XX_ITEMS_INTERFACE_723 ( ERRBUF, RETCODE );
COMMIT;
END;
select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);
delete mtl_system_items_interface;
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
)
AS
v_template_id NUMBER;
v_organization_id Number;
CURSOR c1
IS
SELECT *
FROM xx_items_723;
BEGIN
FOR i IN c1
LOOP
BEGIN
SELECT template_id
INTO v_template_id
FROM mtl_item_templates
WHERE template_id = i.template_id;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_template_id error:' || v_template_id);
IF v_template_id IS NULL
THEN
DBMS_OUTPUT.put_line ('v_template_id:' || v_template_id);
END IF;
END;
Begin
select organization_id
into v_organization_id
From Org_organization_definitions
Where ORGANIZATION_ID=i.ORGANIZATION_ID;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_organization_id errir:' || v_organization_id);
If V_organization_id is Null
Then
DBMS_OUTPUT.put_line ('v_organization_id' || v_organization_id);
END IF;
End;
IF v_template_id IS NOT NULL and V_organization_id IS NOT NULL
AND i.set_process_id = 1
AND i.process_flag = 1
THEN
INSERT INTO mtl_system_items_interface
(process_flag, transaction_type, set_process_id,
organization_id, segment1, description,
template_id
)
VALUES (i.process_flag, i.transaction_type, i.set_process_id,
i.organization_id, i.segment1, i.description,
i.template_id
);
ELSE
DBMS_OUTPUT.put_line ('I.SET_PROCESS_ID' || i.set_process_id);
DBMS_OUTPUT.put_line ('I.PROCESS_FLAG' || i.process_flag);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('FILES LOADED');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('sqlerrm' || SQLERRM);
END;
/
select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);
DECLARE
ERRBUF VARCHAR2(200);
RETCODE VARCHAR2(200);
BEGIN
ERRBUF := NULL;
RETCODE := NULL;
APPS.XX_ITEMS_INTERFACE_723 ( ERRBUF, RETCODE );
COMMIT;
END;
select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);
delete mtl_system_items_interface;
Oracle UTL Files
UTL_FILE
The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.
Procedures/Functions
fopen
function fopen returns record
(
location in varchar2 ,
filename in varchar2 ,
open_mode in varchar2 ,
max_linesize in binary_integer default
);
Opens a file. Takes four parameters: location, filename, open_mode and max_linesize
location: must be either an (existing) directory on the server AND be in the utl_file_dir paramter, or
a directory.
open_mode: 'W' for writing access or 'R' for reading access.
get_line
procedure get_line (
file in record ,
buffer out varchar2 ,
len in binary_integer default
);
Reads a line from the opened file.
put_line
procedure put_line (
file in record ,
buffer in varchar2 ,
autoflush in boolean default
);
Writes a line into the opened file.
If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF.
Example
The following two procedures show how to use utl_file to write to and read from a file using PL/SQL. It doesn't do very much let alone something useful, but it can be extended.
In order to use it, make sure the utl_file_dir paramter is set:
select value from v$parameter where name = 'utl_file_dir';
The value returned is actually the path that you must use in the arguments to utl_file_test_read and utl_file_test_write.
utl_file_test_write writes two lines into the file specified with the parameters path and filename.
The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.
Procedures/Functions
fopen
function fopen returns record
(
location in varchar2 ,
filename in varchar2 ,
open_mode in varchar2 ,
max_linesize in binary_integer default
);
Opens a file. Takes four parameters: location, filename, open_mode and max_linesize
location: must be either an (existing) directory on the server AND be in the utl_file_dir paramter, or
a directory.
open_mode: 'W' for writing access or 'R' for reading access.
get_line
procedure get_line (
file in record ,
buffer out varchar2 ,
len in binary_integer default
);
Reads a line from the opened file.
put_line
procedure put_line (
file in record ,
buffer in varchar2 ,
autoflush in boolean default
);
Writes a line into the opened file.
If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF.
Example
The following two procedures show how to use utl_file to write to and read from a file using PL/SQL. It doesn't do very much let alone something useful, but it can be extended.
In order to use it, make sure the utl_file_dir paramter is set:
select value from v$parameter where name = 'utl_file_dir';
The value returned is actually the path that you must use in the arguments to utl_file_test_read and utl_file_test_write.
utl_file_test_write writes two lines into the file specified with the parameters path and filename.
Workflow Run In Backend Script
declare
begin
INV_WF_DEMO.LAUNCH_WORKFLOW ('INV_DEMO',
inv_wf_seq.nextval,
'INV_WF_PROCESS',
391955,
204,
'A2 DOM');
exception
when others then
dbms_output.put_line('Error:' || sqlerrm);
end;
price_list_wf_demo
declare
p_result VARCHAR2(1000);
begin
--price_list_wf_demo.update_reject_status('NEWPLIST',22,33223,p_result);
PRICE_LIST_WF_DEMO.UPDATE_REJECT_STATUS('NEWPLIST',61, 799821,'RUN',p_result);
exception
when others then
dbms_output.put_line('Error:' || sqlerrm);
end;
begin
INV_WF_DEMO.LAUNCH_WORKFLOW ('INV_DEMO',
inv_wf_seq.nextval,
'INV_WF_PROCESS',
391955,
204,
'A2 DOM');
exception
when others then
dbms_output.put_line('Error:' || sqlerrm);
end;
price_list_wf_demo
declare
p_result VARCHAR2(1000);
begin
--price_list_wf_demo.update_reject_status('NEWPLIST',22,33223,p_result);
PRICE_LIST_WF_DEMO.UPDATE_REJECT_STATUS('NEWPLIST',61, 799821,'RUN',p_result);
exception
when others then
dbms_output.put_line('Error:' || sqlerrm);
end;
Deloitte Interview Questions for vishal and sedhu
1.HAVE YOU DEVELOPED ANY CUSTOM FORM?
Yes i have developed more than 10 custom forms for ti cycles using form builder 6i.
2.HOW DO YOU CREATE A CUSTOM FORM?
1. Download the template.fmb from resource folder.
2. Delete the data block,canvas,windows.
3. save the fmb.
4. In that form module property we have the changed first navigation block.
5. move the fmb into au top.
3.FORM REGISTRATION STEPS
1. compile the fmb using syntax:
frmcmp_batch module=form_name.fmb module_type=FORM userid=apps/apps output_file=oracle/appl/po/form_name.fmx
batch=yes compile_all=yes
2. Register the form into Oracle Apps.Application developer.
-> create a form.
-> create a function for form.
-> create a menu for form.
3. finally we can see the form in our Purchasing responsibility.
4.WHAT IS THE FIRING ORDER OF THE TRIGGERS IN FORMS?
form lever --> block lever --> item lever.
5.WHAT ARE THE TYPES OF RELATION IN MASTER-DETAIL FORM RELATIONSHIP?
1.Cascading.
2.Isolated.
3.Non isolated.
6.LET US CONSIDER THERE IS A DATABLOCK CONTAINS A NON-DATABASE ITEM AND A BUTTON.
WHEN THE BUTTON IS PRESSED HOW WOULD YOU BRING THE DATA TO THE FIELD?
Using when_button_pressed trigger we should mention the specified block name.non_database_item_field name.
7.DIFFERENCE BETWEEN KEYNEXTVAL AND WHEN-VALIDATE-ITEM?
1. Keynextval fires before the when_valildate_item.
2. Keynextval fire everytime when u move the next item.
3. Validate_fires when to make changes to text item after the execution of validate item.
8.XML REPORT REGISTRATION STEPS?
9.DIFFERENCE BETWEEN FUNCTION AND PROCEDURE.
--> A function should return a value.
--> A procedure may or may not return a value.
10.WHAT IS MEAN MY AUTONOMOUS_TRANSACTION?
It is a independent transaction. It is used by pl/sql block within the sql statements committing transactions.
11.DIFFERENCE BETWEEN TRUNC AND DELETE.
1. We can use where clause with delete but not with trunc.
2. delete we have to use the trigger. Trunc we cannt use the trigger.
3. Delete is dml command. Trunc is ddl command.
4. cannot rollback the truncate, can rollback the delete datas.
5. Truncate is faster then delete.
12.WHAT ARE THE MODULES YOU HAVE WORKED?
PO,ONT,AP,AR,HRMS,GL.
13.WHAT ARE THE TRANSACTIONS YOU KNOW IN AR MODULE?
credit memo,Invoice
14.WHAT IS MEAN BY CREDIT MEMO?
15.IN A REPORT,I WANT TO PRINT ONLY 5 RECORDS PER PAGE.
In repeating frame property we have the option maximum records per page.
16.TYPES OF DATABASE TRIGGERS.
before,after,insert,update,delete,before row level,after row level.
vishal questions:
-----------------
1) WHAT ARE THE PROBLEMS FACED BY YOU DURING REPORT MIGRATION.
2) WHAT ARE THE DIFFERENT TYPE OF DATA BLOCKS .
1.DATA block 2.control block
3) WHAT ARE THE DIFFERENT BUTTON TRIGGERS AVALIABLE?
4) WHAT ARE THE DIFFERENT REPORT TRIGGER AVAILABLE.
5) WHAT ARE THE DIFFERENT FLOWS AVALABLE IN AR.
6) WHAT ARE THE TABLES AVAILABLE IN AR.
7) HOW TO CREATE A CREDIT MEMO.
8) HAVE YOU DONE GL INTERFACE
9) WHAT ARE THE TRIGGERS IN FORM PERSONALIZATION.
10) DIFFERENCE BETWEEN PRE TEXT ITEM AND POST TEXT ITEM.
11) HOW TO CREATE AN INVOICE IN AP AND POST IT TO GL.
12) WHAT ARE THE DIFFERENT TYPES OF INVOICES IN AR AND HOW TO CREATE
THEM.
Yes i have developed more than 10 custom forms for ti cycles using form builder 6i.
2.HOW DO YOU CREATE A CUSTOM FORM?
1. Download the template.fmb from resource folder.
2. Delete the data block,canvas,windows.
3. save the fmb.
4. In that form module property we have the changed first navigation block.
5. move the fmb into au top.
3.FORM REGISTRATION STEPS
1. compile the fmb using syntax:
frmcmp_batch module=form_name.fmb module_type=FORM userid=apps/apps output_file=oracle/appl/po/form_name.fmx
batch=yes compile_all=yes
2. Register the form into Oracle Apps.Application developer.
-> create a form.
-> create a function for form.
-> create a menu for form.
3. finally we can see the form in our Purchasing responsibility.
4.WHAT IS THE FIRING ORDER OF THE TRIGGERS IN FORMS?
form lever --> block lever --> item lever.
5.WHAT ARE THE TYPES OF RELATION IN MASTER-DETAIL FORM RELATIONSHIP?
1.Cascading.
2.Isolated.
3.Non isolated.
6.LET US CONSIDER THERE IS A DATABLOCK CONTAINS A NON-DATABASE ITEM AND A BUTTON.
WHEN THE BUTTON IS PRESSED HOW WOULD YOU BRING THE DATA TO THE FIELD?
Using when_button_pressed trigger we should mention the specified block name.non_database_item_field name.
7.DIFFERENCE BETWEEN KEYNEXTVAL AND WHEN-VALIDATE-ITEM?
1. Keynextval fires before the when_valildate_item.
2. Keynextval fire everytime when u move the next item.
3. Validate_fires when to make changes to text item after the execution of validate item.
8.XML REPORT REGISTRATION STEPS?
9.DIFFERENCE BETWEEN FUNCTION AND PROCEDURE.
--> A function should return a value.
--> A procedure may or may not return a value.
10.WHAT IS MEAN MY AUTONOMOUS_TRANSACTION?
It is a independent transaction. It is used by pl/sql block within the sql statements committing transactions.
11.DIFFERENCE BETWEEN TRUNC AND DELETE.
1. We can use where clause with delete but not with trunc.
2. delete we have to use the trigger. Trunc we cannt use the trigger.
3. Delete is dml command. Trunc is ddl command.
4. cannot rollback the truncate, can rollback the delete datas.
5. Truncate is faster then delete.
12.WHAT ARE THE MODULES YOU HAVE WORKED?
PO,ONT,AP,AR,HRMS,GL.
13.WHAT ARE THE TRANSACTIONS YOU KNOW IN AR MODULE?
credit memo,Invoice
14.WHAT IS MEAN BY CREDIT MEMO?
15.IN A REPORT,I WANT TO PRINT ONLY 5 RECORDS PER PAGE.
In repeating frame property we have the option maximum records per page.
16.TYPES OF DATABASE TRIGGERS.
before,after,insert,update,delete,before row level,after row level.
vishal questions:
-----------------
1) WHAT ARE THE PROBLEMS FACED BY YOU DURING REPORT MIGRATION.
2) WHAT ARE THE DIFFERENT TYPE OF DATA BLOCKS .
1.DATA block 2.control block
3) WHAT ARE THE DIFFERENT BUTTON TRIGGERS AVALIABLE?
4) WHAT ARE THE DIFFERENT REPORT TRIGGER AVAILABLE.
5) WHAT ARE THE DIFFERENT FLOWS AVALABLE IN AR.
6) WHAT ARE THE TABLES AVAILABLE IN AR.
7) HOW TO CREATE A CREDIT MEMO.
8) HAVE YOU DONE GL INTERFACE
9) WHAT ARE THE TRIGGERS IN FORM PERSONALIZATION.
10) DIFFERENCE BETWEEN PRE TEXT ITEM AND POST TEXT ITEM.
11) HOW TO CREATE AN INVOICE IN AP AND POST IT TO GL.
12) WHAT ARE THE DIFFERENT TYPES OF INVOICES IN AR AND HOW TO CREATE
THEM.
FORM PERSONALIZATION LINKS
form personalization links for lov:
http://erpschools.com/articles/change-displayed-data-in-lov-using-forms-personalization
http://blog.optiosys.com/?p=202
http://bloggingaboutoracleapplications.org/making-a-list-of-values-context-sensitive-using-forms-personalization/
http://www.danilovizzarro.it/2009/04/form-personalization-restrict-the-responsibilities-lov-without-disabling-the-end-date-field-on-the-form-fndscaus/
http://apps2fusion.com/forums/viewtopic.php?f=115&t=3250
http://oracleappstechnicalworld.blogspot.com/2009/05/oracle-apps-changing-lov-query-form.html
http://oraclearea51.com/blog/change-lov-query-through-forms-personalization.html
http://erpschools.com/articles/change-displayed-data-in-lov-using-forms-personalization
http://blog.optiosys.com/?p=202
http://bloggingaboutoracleapplications.org/making-a-list-of-values-context-sensitive-using-forms-personalization/
http://www.danilovizzarro.it/2009/04/form-personalization-restrict-the-responsibilities-lov-without-disabling-the-end-date-field-on-the-form-fndscaus/
http://apps2fusion.com/forums/viewtopic.php?f=115&t=3250
http://oracleappstechnicalworld.blogspot.com/2009/05/oracle-apps-changing-lov-query-form.html
http://oraclearea51.com/blog/change-lov-query-through-forms-personalization.html
friends link
http://appsela.blogspot.com/ -- elango.junior
http://vjcoracleapps.blogspot.com/ -- vishal jacob
http://vjcoracleapps.blogspot.com/ -- vishal jacob
sql loader interview questions
SQL*LOADER:
Websites:
http://www.aboutoracleapps.com/2007/08/sqlloader-qns-what-is-sqlloader-ans.html
http://www.brainbeez.com/index.php?view=article&catid=31%3Aoracle-apps&id=290%3Asql-loader-faq&option=com_content&Itemid=59&limitstart=2
http://freshers-oracleapplications.blogspot.com/2008/09/sqlloader-examples.html
Qns: What is SQL*LOADER
Ans: SQL*LOADER utility is used to transfer data from Flat file to Temporary tables.
For example we Used SQL*Loader mainly in Interface design to load data from flat file to Temporary table.
Qns: What is syntax of Control file?
Ans:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : How will you give position in fixed Files.
Ans: Column Name POSITION(1:2)
Like this.
load data
INFILE
Append/Replace/Insert into TABLE
(
COLUMN1 POSITION(1:2),
COLUMN2 POSITION (3:6),
.
.
.
COLUMN….N
)
Qns :You have 100 records in flat file.if 99 records goes in temp table and One Record fails. Whats will happen to that Record file
Ans: That Record Goes into Bad File generated by SQL*Loader.
Qns: What is difference between Badfile, Logfile and Discard files.
Ans:
• Bad file: Record with error goes into Bad file.
• Log file: History of all records like number of records are loaded, number of record fails validations.
• Discard File: Records which fails in When Clause.
Qns: How will you Give Constant values to SQL*LOADER.
Ans : Column Name CONSTANT ‘AUD’
For Example:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1 CONSTNAT AUD’,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : Whats is syntax of executing SQL*LOADER.
Ans : Sqlldr userid = Control= Data=
Qns : SQL*Loader is auto commit or Not?
Ans: Yes Sql*loader is auto commit . because it end is give message Commit Point reached.
Qns : How will you register SQL*LOADER AS a Concurrent Programs.
Ans :
• Define executable by giving Execution Method as SQL*Loader.
• Define Concurrent Programs
• Attached Concurrent Program to the Request Group of appropriate Responsibility.
Qns: Where you placed Control file on server.
Ans: In Appropriate top bin Folder. For example if Control File is of Oracle Payable module then Control file should be placed in $AP_TOP/bin Folder.
Qns : In Which Mode You Placed Control Files on server.
Ans: ASCII mode
Qns: Whats the Common Error you faced during loading data from flat file to Temporary tables.
Ans: Error like.
• For insert Option your table should be empty.
• Started with “ but not ended with “
Qns : Can you Load data into Multiple tables with the help of SQL*Loaders.
Ans : Yes by giving condition in when clause.
Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (data1, data2)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data (data1 POSITION(1:5), data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5(data1 POSITION(1:5), data2 POSITION(6:15))
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","(addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state)
Can one load data into multiple tables at once?
Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_tableWHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'(region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )
Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify
FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one.
Eg. by having a '#' character in column 1
How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced.
Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
Websites:
http://www.aboutoracleapps.com/2007/08/sqlloader-qns-what-is-sqlloader-ans.html
http://www.brainbeez.com/index.php?view=article&catid=31%3Aoracle-apps&id=290%3Asql-loader-faq&option=com_content&Itemid=59&limitstart=2
http://freshers-oracleapplications.blogspot.com/2008/09/sqlloader-examples.html
Qns: What is SQL*LOADER
Ans: SQL*LOADER utility is used to transfer data from Flat file to Temporary tables.
For example we Used SQL*Loader mainly in Interface design to load data from flat file to Temporary table.
Qns: What is syntax of Control file?
Ans:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : How will you give position in fixed Files.
Ans: Column Name POSITION(1:2)
Like this.
load data
INFILE
Append/Replace/Insert into TABLE
(
COLUMN1 POSITION(1:2),
COLUMN2 POSITION (3:6),
.
.
.
COLUMN….N
)
Qns :You have 100 records in flat file.if 99 records goes in temp table and One Record fails. Whats will happen to that Record file
Ans: That Record Goes into Bad File generated by SQL*Loader.
Qns: What is difference between Badfile, Logfile and Discard files.
Ans:
• Bad file: Record with error goes into Bad file.
• Log file: History of all records like number of records are loaded, number of record fails validations.
• Discard File: Records which fails in When Clause.
Qns: How will you Give Constant values to SQL*LOADER.
Ans : Column Name CONSTANT ‘AUD’
For Example:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1 CONSTNAT AUD’,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : Whats is syntax of executing SQL*LOADER.
Ans : Sqlldr userid =
Qns : SQL*Loader is auto commit or Not?
Ans: Yes Sql*loader is auto commit . because it end is give message Commit Point reached.
Qns : How will you register SQL*LOADER AS a Concurrent Programs.
Ans :
• Define executable by giving Execution Method as SQL*Loader.
• Define Concurrent Programs
• Attached Concurrent Program to the Request Group of appropriate Responsibility.
Qns: Where you placed Control file on server.
Ans: In Appropriate top bin Folder. For example if Control File is of Oracle Payable module then Control file should be placed in $AP_TOP/bin Folder.
Qns : In Which Mode You Placed Control Files on server.
Ans: ASCII mode
Qns: Whats the Common Error you faced during loading data from flat file to Temporary tables.
Ans: Error like.
• For insert Option your table should be empty.
• Started with “ but not ended with “
Qns : Can you Load data into Multiple tables with the help of SQL*Loaders.
Ans : Yes by giving condition in when clause.
Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (data1, data2)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data (data1 POSITION(1:5), data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5(data1 POSITION(1:5), data2 POSITION(6:15))
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","(addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state)
Can one load data into multiple tables at once?
Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_tableWHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'(region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )
Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify
FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one.
Eg. by having a '#' character in column 1
How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced.
Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
Tuesday, 21 June 2011
oracle forms 6i
ORACLE FORMS 6I
• Enterprise application developers need a declarative model-based approach. Oracle Designer and Oracle Forms Developer provide this solution, using Oracle Forms Services as the primary deployment option.
What Is Oracle Forms Developer?
• A productive development environment for Internet business applications
Data entry
Query screens
• It provides a set of tools that enable business developers to easily and quickly construct
sophisticated database forms and
business logic with a minimum of effort.
Oracle Forms Services?
Oracle Forms Services is a component of Oracle9i Application Server for delivering
Oracle Forms Developer applications to the Internet.
Oracle Forms Services uses a three-tier architecture to deploy database applications:
1. The client tier contains the Web browser, where the application is
displayed and used.
2. The middle tier is the application server, where the application logic and
server software reside.
3. The database tier is the database server, where enterprise data is stored.
What is Form Builder?
Form Builder is a powerful development tool for building robust, enterprise-class applications that enable end users to retrieve, enter, modify, and save information in the database.
Form Builder Components
1. Object Navigator – [F3]
2. Property Palette [F4]
3. Layout Editor (or Layout Model) [F2]
4. PL/SQL Editor [F11]
Types of Blocks
In Form Builder there are two main types of blocks:
1. data blocks and
2. control blocks.
1. DATA BLOCKS
• A data block is associated with a specific database table (or
view), a stored procedure, a FROM clause query, or transactional triggers.
• If it is based on a table (or view), the data block can be based on only one base table
2. Control Blocks
• A control block is not associated with a database, and its items do
not relate to any columns within any database table.
• Its items are called control items.
What Is a Window?
A window is a container for all visual objects that make up a Form Builder application.
It is similar to an empty picture frame.
What Is a Canvas?
A canvas is a surface inside a window container on which you place visual objects
such as interface items and graphics.
What Is a Content Canvas?
A content canvas is the base canvas
that occupies the entire content pane of the window in which it displays. The content
canvas is the default canvas type.
Form Builder provides three other types of canvases
which are:
• Stacked canvas
• Toolbar canvas
• Tab canvas
When you create a canvas, you specify its type by setting the Canvas Type property.
The type determines how the canvas is displayed in the window to which it is
assigned.
What Is a Stacked Canvas?
A stacked canvas is displayed on top of, or stacked on, the content canvas assigned to
a window.
What Is a Toolbar Canvas?
A toolbar canvas is a special type of canvas that you can create to hold buttons and
other frequently used GUI elements.
The Three Toolbar Types
• Vertical toolbar: Use a vertical toolbar to position all your tool items
down the left or right hand side of your window.
• Horizontal toolbar: Use a horizontal toolbar to position all your tool
items and controls across the top or bottom of your window.
• MDI toolbar: Use an MDI toolbar to avoid creating more than one
toolbar for a Form Builder application that uses multiple windows.
What Is a Tab Canvas?
A tab canvas is a special type of canvas that enables you to organize and display
related information on separate tabs.
TO DESIGN FORM MODULE USING WIZARD
1. OPEN FORM MODULE
2. SELECT WIZARD OPTION AND CLICK ON OK
3.
CLICK ON NEXT->NEXT
4. TYPE THE TABLE NAME AND CLICK ON REFRESH BUTTON
5. ENTER USER NAME: SCOTT/TIGER@VIS
6.
7. NOW MOVE THE REQUIRED COLUNMS TO DATABASE ITEMS
8. CLICK ON FINISH
9. TO PLACE DATABLOCK ITEMS ON CANVAS
10.
11. CLICK ON NEXT->NEXT BUTTON
12. MOVE THE REQUIRED ITEMS TO BE PLACED ON CANVAS
13. CLICK ON NEXT->NEXT BUTTON
14. ENTER FRAME TITLE
15 CLICK FINISH BUTTON
NOW CLICK ON RUN ICON
DESIGN FORM AS FOLLOWS
TO CREATE CONTROL BLOCK
NOW SELECT MANUAL AND CLICK ON OK
NOW CHANGE NAME OF THE BLOCK
NOW DOUBLE CLICK ON CANAVAS AND PLACE BUTTONS AS FOLLOWS
NOW TO SELECT TRIGGER FOR THE BUTTONS
EXECUTE CODE:
GO_BLOCK('EMP');
EXECUTE_QUERY;
LAST : FIRST:
GO_BLOCK('EMP'); GO_BLOCK('EMP');
LAST_RECORD; FIRST_RECORD;
NEXT:
GO_BLOCK('EMP');
NEXT_RECORD;
PREVIOUS
GO_BLOCK('EMP');
PREVIOUS_RECORD;
EXIT:
EXIT_FORM;
T_EMPNO “POST_TEXT_ITEM”
SELECT ENAME,JOB,SAL,DEPTNO INTO :T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO FROM EMP WHERE EMPNO=:T_EMPNO;
DESIGN FORM AS FOLLOWS
ADD CODE
DECLARE
N NUMBER;
BEGIN
SELECT MAX(EMPNO) INTO N FROM EMP;
IF N IS NULL THEN
:T_EMPNO:=1001;
ELSE
:T_EMPNO:=N+1;
END IF;
END;
SAVE CODE
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES(:T_EMPNO,:T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO);
COMMIT;
CLEAR_FORM;
MODIFY
UPDATE EMP SET ENAME=:T_ENAME,JOB=:T_JOB,SAL=:T_SAL,DEPTNO=:T_DEPTNO WHERE EMPNO=:T_EMPNO;
COMMIT;
CLEAR_FORM;
DELETE
DELETE FROM EMP WHERE EMPNO=:T_EMPNO;
COMMIT;
CLEAR_FORM;
FIND
IF :T_EMPNO IS NULL THEN
:T_EMPNO:=7654;
ELSE
SELECT ENAME,JOB,SAL,DEPTNO INTO :T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO FROM EMP WHERE EMPNO=:T_EMPNO;
END IF;
CLEAR
CLEAR_FORM;
EXIT
EXIT_FORM;
T_JOB “POST_TEXT_ITEM”
if :T_JOB='CLERK' THEN
:T_SAL:=1200;
ELSif :T_JOB='MANAGER' THEN
:T_SAL:=2200;
ELSE
:T_SAL:=3200;
END IF;
LOV
- FOR FINDING RECORDS CREATE AN LOV
- CALLING LOV WHEN YOU CLICK ON FIND BUTTON
DECLARE
N BOOLEAN;
BEGIN
N:=SHOW_LOV('LOV11');
END;
ALERTS
CREATE AN ALERT TO CALL WHEN EVER YOU TRY TO EXIT FROM FORM
- IN EXIT BUTTON WRITE FOLLOWING CODE
DECLARE
N NUMBER;
BEGIN
N:=SHOW_ALERT('ALERT28');
IF N=ALERT_BUTTON1 THEN
EXIT_FORM;
ELSE
GO_BLOCK('BLOCK3');
END IF;
END;
WORKING WITH PROGRAM UNITS
CREATE A PROCEDURE TO CALL WHEN YOU CLICK ON EXIT BUTTON
NOW WRITE FOLLOWING CODE
PROCEDURE CLOSEFORM IS
N NUMBER;
BEGIN
N:=SHOW_ALERT('ALERT28');
IF N=ALERT_BUTTON1 THEN
EXIT_FORM;
ELSE
GO_BLOCK('BLOCK3');
END IF;
END;
- NOW CALL THE PROCEDURE IN “EXIT BUTTON”
CLOSEFORM;
• Enterprise application developers need a declarative model-based approach. Oracle Designer and Oracle Forms Developer provide this solution, using Oracle Forms Services as the primary deployment option.
What Is Oracle Forms Developer?
• A productive development environment for Internet business applications
Data entry
Query screens
• It provides a set of tools that enable business developers to easily and quickly construct
sophisticated database forms and
business logic with a minimum of effort.
Oracle Forms Services?
Oracle Forms Services is a component of Oracle9i Application Server for delivering
Oracle Forms Developer applications to the Internet.
Oracle Forms Services uses a three-tier architecture to deploy database applications:
1. The client tier contains the Web browser, where the application is
displayed and used.
2. The middle tier is the application server, where the application logic and
server software reside.
3. The database tier is the database server, where enterprise data is stored.
What is Form Builder?
Form Builder is a powerful development tool for building robust, enterprise-class applications that enable end users to retrieve, enter, modify, and save information in the database.
Form Builder Components
1. Object Navigator – [F3]
2. Property Palette [F4]
3. Layout Editor (or Layout Model) [F2]
4. PL/SQL Editor [F11]
Types of Blocks
In Form Builder there are two main types of blocks:
1. data blocks and
2. control blocks.
1. DATA BLOCKS
• A data block is associated with a specific database table (or
view), a stored procedure, a FROM clause query, or transactional triggers.
• If it is based on a table (or view), the data block can be based on only one base table
2. Control Blocks
• A control block is not associated with a database, and its items do
not relate to any columns within any database table.
• Its items are called control items.
What Is a Window?
A window is a container for all visual objects that make up a Form Builder application.
It is similar to an empty picture frame.
What Is a Canvas?
A canvas is a surface inside a window container on which you place visual objects
such as interface items and graphics.
What Is a Content Canvas?
A content canvas is the base canvas
that occupies the entire content pane of the window in which it displays. The content
canvas is the default canvas type.
Form Builder provides three other types of canvases
which are:
• Stacked canvas
• Toolbar canvas
• Tab canvas
When you create a canvas, you specify its type by setting the Canvas Type property.
The type determines how the canvas is displayed in the window to which it is
assigned.
What Is a Stacked Canvas?
A stacked canvas is displayed on top of, or stacked on, the content canvas assigned to
a window.
What Is a Toolbar Canvas?
A toolbar canvas is a special type of canvas that you can create to hold buttons and
other frequently used GUI elements.
The Three Toolbar Types
• Vertical toolbar: Use a vertical toolbar to position all your tool items
down the left or right hand side of your window.
• Horizontal toolbar: Use a horizontal toolbar to position all your tool
items and controls across the top or bottom of your window.
• MDI toolbar: Use an MDI toolbar to avoid creating more than one
toolbar for a Form Builder application that uses multiple windows.
What Is a Tab Canvas?
A tab canvas is a special type of canvas that enables you to organize and display
related information on separate tabs.
TO DESIGN FORM MODULE USING WIZARD
1. OPEN FORM MODULE
2. SELECT WIZARD OPTION AND CLICK ON OK
3.
CLICK ON NEXT->NEXT
4. TYPE THE TABLE NAME AND CLICK ON REFRESH BUTTON
5. ENTER USER NAME: SCOTT/TIGER@VIS
6.
7. NOW MOVE THE REQUIRED COLUNMS TO DATABASE ITEMS
8. CLICK ON FINISH
9. TO PLACE DATABLOCK ITEMS ON CANVAS
10.
11. CLICK ON NEXT->NEXT BUTTON
12. MOVE THE REQUIRED ITEMS TO BE PLACED ON CANVAS
13. CLICK ON NEXT->NEXT BUTTON
14. ENTER FRAME TITLE
15 CLICK FINISH BUTTON
NOW CLICK ON RUN ICON
DESIGN FORM AS FOLLOWS
TO CREATE CONTROL BLOCK
NOW SELECT MANUAL AND CLICK ON OK
NOW CHANGE NAME OF THE BLOCK
NOW DOUBLE CLICK ON CANAVAS AND PLACE BUTTONS AS FOLLOWS
NOW TO SELECT TRIGGER FOR THE BUTTONS
EXECUTE CODE:
GO_BLOCK('EMP');
EXECUTE_QUERY;
LAST : FIRST:
GO_BLOCK('EMP'); GO_BLOCK('EMP');
LAST_RECORD; FIRST_RECORD;
NEXT:
GO_BLOCK('EMP');
NEXT_RECORD;
PREVIOUS
GO_BLOCK('EMP');
PREVIOUS_RECORD;
EXIT:
EXIT_FORM;
T_EMPNO “POST_TEXT_ITEM”
SELECT ENAME,JOB,SAL,DEPTNO INTO :T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO FROM EMP WHERE EMPNO=:T_EMPNO;
DESIGN FORM AS FOLLOWS
ADD CODE
DECLARE
N NUMBER;
BEGIN
SELECT MAX(EMPNO) INTO N FROM EMP;
IF N IS NULL THEN
:T_EMPNO:=1001;
ELSE
:T_EMPNO:=N+1;
END IF;
END;
SAVE CODE
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES(:T_EMPNO,:T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO);
COMMIT;
CLEAR_FORM;
MODIFY
UPDATE EMP SET ENAME=:T_ENAME,JOB=:T_JOB,SAL=:T_SAL,DEPTNO=:T_DEPTNO WHERE EMPNO=:T_EMPNO;
COMMIT;
CLEAR_FORM;
DELETE
DELETE FROM EMP WHERE EMPNO=:T_EMPNO;
COMMIT;
CLEAR_FORM;
FIND
IF :T_EMPNO IS NULL THEN
:T_EMPNO:=7654;
ELSE
SELECT ENAME,JOB,SAL,DEPTNO INTO :T_ENAME,:T_JOB,:T_SAL,:T_DEPTNO FROM EMP WHERE EMPNO=:T_EMPNO;
END IF;
CLEAR
CLEAR_FORM;
EXIT
EXIT_FORM;
T_JOB “POST_TEXT_ITEM”
if :T_JOB='CLERK' THEN
:T_SAL:=1200;
ELSif :T_JOB='MANAGER' THEN
:T_SAL:=2200;
ELSE
:T_SAL:=3200;
END IF;
LOV
- FOR FINDING RECORDS CREATE AN LOV
- CALLING LOV WHEN YOU CLICK ON FIND BUTTON
DECLARE
N BOOLEAN;
BEGIN
N:=SHOW_LOV('LOV11');
END;
ALERTS
CREATE AN ALERT TO CALL WHEN EVER YOU TRY TO EXIT FROM FORM
- IN EXIT BUTTON WRITE FOLLOWING CODE
DECLARE
N NUMBER;
BEGIN
N:=SHOW_ALERT('ALERT28');
IF N=ALERT_BUTTON1 THEN
EXIT_FORM;
ELSE
GO_BLOCK('BLOCK3');
END IF;
END;
WORKING WITH PROGRAM UNITS
CREATE A PROCEDURE TO CALL WHEN YOU CLICK ON EXIT BUTTON
NOW WRITE FOLLOWING CODE
PROCEDURE CLOSEFORM IS
N NUMBER;
BEGIN
N:=SHOW_ALERT('ALERT28');
IF N=ALERT_BUTTON1 THEN
EXIT_FORM;
ELSE
GO_BLOCK('BLOCK3');
END IF;
END;
- NOW CALL THE PROCEDURE IN “EXIT BUTTON”
CLOSEFORM;
Subscribe to:
Posts (Atom)