R12 Oracle Apps: December 2011

Wednesday, December 28, 2011

How to Create OAF Page

Create OAF Hellow Page Screen Shots:

How to Create Customer Contacts API in R12

API'S WHICH ARE USING:-

hz_party_v2pub.create_person
(p_init_msg_list => fnd_api.g_false,
p_person_rec => r_person_rec_type,
x_party_id => ln_party_id,
x_party_number => lc_party_number,
x_profile_id => ln_profile_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => fnd_api.g_false,
p_contact_point_rec => r_contact_point_rec_type,
p_edi_rec => r_edi_rec_type,
p_email_rec => r_email_rec_type,
p_phone_rec => r_phone_rec_type,
p_telex_rec => r_telex_rec_type,
p_web_rec => r_web_rec_type,
x_contact_point_id => ln_contact_point_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_party_contact_v2pub.create_org_contact
(p_init_msg_list => fnd_api.g_false,
p_org_contact_rec => r_org_contact_rec_type,
x_org_contact_id => ln_org_contact_id,
x_party_rel_id => ln_party_rel_id,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_party_contact_v2pub.create_org_contact_role
(p_init_msg_list => fnd_api.g_false,
p_org_contact_role_rec => r_org_contact_role_rec_type,
x_org_contact_role_id => l_org_contact_role_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_location_v2pub.create_location
(p_init_msg_list => fnd_api.g_false,
p_location_rec => r_location_rec_type,
x_location_id => ln_location_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_party_site_v2pub.create_party_site
(p_init_msg_list => fnd_api.g_false,
p_party_site_rec => r_party_site_rec_type,
x_party_site_id => ln_party_site_id,
x_party_site_number => lc_party_site_number,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_cust_account_role_v2pub.create_cust_account_role
(p_init_msg_list => fnd_api.g_false,
p_cust_account_role_rec => r_cust_account_role_rec_type,
x_cust_account_role_id => ln_cust_account_role_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

hz_cust_account_role_v2pub.create_role_responsibility
(p_init_msg_list => fnd_api.g_false,
p_role_responsibility_rec => r_role_responsibility_rec,
x_responsibility_id => ln_responsibility_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

How Create Customers in R12 API

CREATE CUSTOMER:
r_cust_account_rec_type.account_name := cv_party_data.party_name; r_cust_account_rec_type.account_number := cv_party_data.account_number; r_cust_account_rec_type.tax_header_level_flag := 'Y'; r_cust_account_rec_type.status := NVL (cv_party_data.account_status, 'A'); r_cust_account_rec_type.created_by_module := 'HZ_CPUI'; r_cust_account_rec_type.orig_system_reference := cv_party_data.acct_orig_sys_ref; r_customer_profile_rec_type.profile_class_id := cv_party_data.profile_class_id; r_cust_account_rec_type.Customer_class_code := 'PUBLIC SECTOR COMPANIES'; r_customer_profile_rec_type.dunning_letters := cv_party_data.dunning_letters; r_customer_profile_rec_type.override_terms := cv_party_data.override_terms; r_organization_rec_type.organization_name := cv_party_data.party_name; r_organization_rec_type.party_rec.orig_system_reference := cv_party_data.orig_system_reference; r_organization_rec_type.party_rec.category_code := cv_party_data.category_code; r_organization_rec_type.tax_reference := cv_party_data.tax_reg_number; r_organization_rec_type.jgzz_fiscal_code := cv_party_data.taxpayer_id; r_organization_rec_type.party_rec.status := NVL (cv_party_data.status, 'A'); r_customer_profile_rec_type.credit_balance_statements := cv_party_data.credit_balance_statements; r_customer_profile_rec_type.send_statements := cv_party_data.statements; r_customer_profile_rec_type.collector_id := cv_party_data.collector_id; r_customer_profile_rec_type.statement_cycle_id := cv_party_data.statement_cycle_id; r_cust_account_rec_type.global_attribute3 := cv_party_data.attribute2;

r_organization_rec_type.party_rec.party_id := ln_party_id;

fnd_msg_pub.initialize;
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => fnd_api.g_false,
p_cust_account_rec => r_cust_account_rec_type,
p_organization_rec => r_organization_rec_type,
p_customer_profile_rec => r_customer_profile_rec_type,
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => ln_cust_account_id,
x_account_number => lc_account_number,
x_party_id => ln_party_id,
x_party_number => lc_party_number,
x_profile_id => ln_acct_profile_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

CUSTOMER SITES:

1.CREATE LOCATION:-
r_location_rec_type.country := cv_address_data.country;
r_location_rec_type.address1 := cv_address_data.address1;

r_location_rec_type.address2 := cv_address_data.address2;

r_location_rec_type.address3 := cv_address_data.address3;
r_location_rec_type.address4 := cv_address_data.address4;
r_location_rec_type.city := cv_address_data.city;
r_location_rec_type.postal_code := cv_address_data.postal_code;
r_location_rec_type.state := cv_address_data.state;
r_location_rec_type.province := cv_address_data.province;
r_location_rec_type.county := cv_address_data.county;
r_location_rec_type.created_by_module := 'HZ_CPUI';
r_location_rec_type.orig_system_reference := NULL;

hz_location_v2pub.create_location
(p_init_msg_list => fnd_api.g_false,
p_location_rec => r_location_rec_type,
x_location_id => ln_location_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data

2.CREATE PARTY SITE:-

r_party_site_rec_type.identifying_address_flag :=cv_address_data.identifying_address_flag;
r_party_site_rec_type.created_by_module := 'HZ_CPUI'; --HZ_CPUI
r_party_site_rec_type.party_id := p_party_id;
r_party_site_rec_type.party_site_number:= cv_address_data.party_site_number;
r_party_site_rec_type.location_id := ln_location_id;
r_party_site_rec_type.status := 'A';
r_party_site_rec_type.orig_system_reference :=cv_address_data.orig_system_reference;

hz_party_site_v2pub.create_party_site
(p_init_msg_list => fnd_api.g_false,
p_party_site_rec => r_party_site_rec_type,
x_party_site_id => ln_party_site_id,
x_party_site_number => lc_party_site_number,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

3.CREATE CUSTOMER ACCOUNT SITE:-

r_cust_acct_site_rec_type.cust_account_id := p_cust_account_id;
r_cust_acct_site_rec_type.party_site_id := ln_party_site_id;
r_cust_acct_site_rec_type.created_by_module := 'HZ_CPUI';
r_cust_acct_site_rec_type.orig_system_reference :=cv_address_data.site_orig_system_reference;
r_cust_acct_site_rec_type.status := NVL (cv_address_data.status, 'A');
r_cust_acct_site_rec_type.org_id := cv_address_data.org_id;

hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => fnd_api.g_false,
p_cust_acct_site_rec => r_cust_acct_site_rec_type,
x_cust_acct_site_id => ln_cust_acct_site_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);
4.CREATE CUSTOMER SITE USE:-

r_cust_site_use_rec_type.gl_id_rec :=cv_cust_site_use.receivable;--l_site_receivable_cc_id;
r_cust_site_use_rec_type.gl_id_rev :=cv_cust_site_use.revenue;--l_site_revenue_cc_id;
r_cust_site_use_rec_type.gl_id_tax :=cv_cust_site_use.tax;--l_site_tax_cc_id;
r_cust_site_use_rec_type.territory_id:= cv_cust_site_use.territory_id;
r_cust_site_use_rec_type.payment_term_id:=cv_cust_site_use.term_id;
r_cust_site_use_rec_type.primary_salesrep_id:=cv_cust_site_use.salesrep_id;
r_cust_site_use_rec_type.tax_reference := cv_cust_site_use.tax_reference;
r_cust_site_use_rec_type.LOCATION := cv_cust_site_use.LOCATION;
r_cust_site_use_rec_type.created_by_module := 'HZ_CPUI';
r_cust_site_use_rec_type.status := 'A';
r_cust_site_use_rec_type.org_id := cv_cust_site_use.org_id;
r_cust_site_use_rec_type.primary_flag :=cv_cust_site_use.primary_flag;
r_customer_profile_rec_type.profile_class_id := cv_cust_site_use.profile_class_id;
r_customer_profile_rec_type.dunning_letters := cv_cust_site_use.dunning_letters;
r_customer_profile_rec_type.override_terms := cv_cust_site_use.override_terms;

hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => fnd_api.g_false,
p_cust_site_use_rec => r_cust_site_use_rec_type,
p_customer_profile_rec => r_customer_profile_rec_type,
-- Modified by YerraS on 19-Sep-09
p_create_profile => FND_API.G_TRUE,
p_create_profile_amt => FND_API.G_TRUE,
x_site_use_id => ln_site_use_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);

Supplier or Vendor Creation API

Supplier Creation in R12

VENDOR:
l_vendor_rec.segment1 := cv_ven_l.vendor_number;
l_vendor_rec.vendor_name := cv_ven_l.vendor_name;
l_vendor_rec.summary_flag := cv_ven_l.summary_flag;
l_vendor_rec.enabled_flag := cv_ven_l.enabled_flag;
l_vendor_rec.vendor_type_lookup_code := cv_ven_l.vendor_type_lookup_code;
l_vendor_rec.start_date_active := SYSDATE;
l_vendor_rec.customer_num := cv_ven_l.customer_num;
l_vendor_rec.pay_group_lookup_code := cv_ven_l.pay_group_lookup_code;
l_vendor_rec.pay_date_basis_lookup_code := cv_ven_l.pay_date_basis_lookup_code;
l_vendor_rec.invoice_currency_code := cv_ven_l.invoice_currency_code;
l_vendor_rec.terms_id :=cv_ven_l.term_id;
l_vendor_rec.ext_payee_rec.default_pmt_method :=cv_ven_l.payment_method_lookup_code;
l_vendor_rec.invoice_currency_code := cv_ven_l.payment_currency_code;
l_vendor_rec.payment_currency_code := cv_ven_l.payment_currency_code;
l_vendor_rec.invoice_amount_limit := cv_ven_l.invoice_amount_limit;
l_vendor_rec.state_reportable_flag :=trim( cv_ven_l.state_reportable_flag);
l_vendor_rec.attribute1 := cv_ven_l.attribute1;
l_vendor_rec.end_date_active := cv_ven_l.end_date_active;
l_vendor_rec.allow_substitute_receipts_flag := cv_ven_l.allow_substitute_receipts_flag;
l_vendor_rec.allow_unordered_receipts_flag := cv_ven_l.allow_unordered_receipts_flag;
l_vendor_rec.employee_id :=cv_ven_l.employee_id;

fnd_msg_pub.initialize;
ap_vendor_pub_pkg.create_vendor (p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id
);

VENDOR-SITE:

l_vendor_rec_site.vendor_id := cv_ven_site_l.vendor_id;
l_vendor_rec_site.vendor_site_code := cv_ven_site_l.vendor_site_code;
fnd_file.put_line (fnd_file.log, 'Vendor Site Code is: '||cv_ven_site_l.vendor_site_code);
l_vendor_rec_site.vendor_site_code_alt := cv_ven_site_l.vendor_site_code_alt;
l_vendor_rec_site.org_id := cv_ven_site_l.organization_id;
l_vendor_rec_site.purchasing_site_flag := cv_ven_site_l.purchasing_site_flag;
l_vendor_rec_site.rfq_only_site_flag := cv_ven_site_l.rfq_only_site_flag;
l_vendor_rec_site.pay_site_flag := cv_ven_site_l.pay_site_flag;
l_vendor_rec_site.primary_pay_site_flag := cv_ven_site_l.primary_pay_site_flag;
l_vendor_rec_site.address_line1 := cv_ven_site_l.address_line1;
fnd_file.put_line (fnd_file.log, 'Address Line1 is: '||cv_ven_site_l.address_line1);
l_vendor_rec_site.address_line2 := cv_ven_site_l.address_line2;
l_vendor_rec_site.address_line3 := cv_ven_site_l.address_line3;
l_vendor_rec_site.address_line4 := cv_ven_site_l.address_line4;
l_vendor_rec_site.city := cv_ven_site_l.city;
l_vendor_rec_site.state := cv_ven_site_l.state;
l_vendor_rec_site.zip := cv_ven_site_l.zip;
l_vendor_rec_site.country := cv_ven_site_l.country;
l_vendor_rec_site.county := cv_ven_site_l.county;
l_vendor_rec_site.phone := cv_ven_site_l.phone;
l_vendor_rec_site.ext_payee_rec.default_pmt_method := cv_ven_site_l.payment_method_lookup_code;
l_vendor_rec_site.invoice_currency_code := cv_ven_site_l.invoice_currency_code;
l_vendor_rec_site.invoice_currency_code := cv_ven_site_l.payment_currency_code;
l_vendor_rec_site.terms_date_basis := cv_ven_site_l.terms_date_basis;
l_vendor_rec_site.email_address := cv_ven_site_l.email_address;
l_vendor_rec_site.supplier_notif_method := cv_ven_site_l.supplier_notif_method;
l_vendor_rec_site.address_style := cv_ven_site_l.address_style;
l_vendor_rec_site.remittance_email:= cv_ven_site_l.remittance_email;
l_vendor_rec_site.invoice_amount_limit := cv_ven_site_l.invoice_amount_limit;
l_vendor_rec_site.payment_currency_code := cv_ven_site_l.payment_currency_code;
l_vendor_rec_site.pay_group_lookup_code := cv_ven_site_l.pay_group_lookup_code;
l_vendor_rec_site.terms_id := cv_ven_site_l.term_id;
l_vendor_rec_site.pay_date_basis_lookup_code := cv_ven_site_l.pay_date_basis_lookup_code;
l_vendor_rec_site.fax := cv_ven_site_l.fax;
l_vendor_rec_site.area_code := cv_ven_site_l.area_code;
l_vendor_rec_site.prepay_code_combination_id := cv_ven_site_l.prepay_code_combination_id;
l_vendor_rec_site.accts_pay_code_combination_id := cv_ven_site_l.accts_pay_code_combination_id;
fnd_msg_pub.initialize;
ap_vendor_pub_pkg.create_vendor_site (p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_site_rec => l_vendor_rec_site,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);

VENDOR-CONTACTS:

l_vendor_contact_rec.vendor_id := cv_site_con_l.vendor_id;
l_vendor_contact_rec.vendor_site_id := cv_site_con_l.vendor_site_id;
   l_vendor_contact_rec.vendor_site_code := cv_site_con_l.vendor_site_code;
   l_vendor_contact_rec.org_id := cv_site_con_l.org_id;
   l_vendor_contact_rec.org_party_site_id := cv_site_con_l.party_site_id;
   l_vendor_contact_rec.person_title := cv_site_con_l.title;
   l_vendor_contact_rec.person_first_name := cv_site_con_l.first_name;
   l_vendor_contact_rec.person_middle_name := cv_site_con_l.middle_name;
   l_vendor_contact_rec.person_last_name := cv_site_con_l.last_name;
   l_vendor_contact_rec.prefix := cv_site_con_l.prefix;
l_vendor_contact_rec.area_code := cv_site_con_l.area_code;
l_vendor_contact_rec.phone := cv_site_con_l.phone;
l_vendor_contact_rec.contact_name_phonetic := cv_site_con_l.contact_name_alt;
l_vendor_contact_rec.person_first_name_phonetic := cv_site_con_l.first_name_alt;
l_vendor_contact_rec.person_last_name_phonetic := cv_site_con_l.last_name_alt;
l_vendor_contact_rec.email_address := cv_site_con_l.email_address;
l_vendor_contact_rec.url := cv_site_con_l.url;
l_vendor_contact_rec.fax_area_code := cv_site_con_l.fax_area_code;
l_vendor_contact_rec.fax_phone := cv_site_con_l.fax;
fnd_msg_pub.initialize;
ap_vendor_pub_pkg.create_vendor_contact (p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_contact_rec => l_vendor_contact_rec,
x_vendor_contact_id => l_vendor_contact_id,
x_per_party_id => l_per_party_id,
x_rel_party_id => l_rel_party_id,
x_rel_id => l_rel_id,
x_org_contact_id => l_org_contact_id,
x_party_site_id => l_party_site_id
);

else we can use below One.

DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_vendor_contact_rec apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;
v_msg_index_out NUMBER;
BEGIN
p_api_version := 1.0;
p_init_msg_list := 'T';
p_commit := 'T';
p_validation_level := FND_API.G_VALID_LEVEL_FULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
P_VENDOR_CONTACT_REC.vendor_site_id :=1580;
P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := 'M';
P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := 'MANOHAR';
P_VENDOR_CONTACT_REC.PHONE := '9840395136';
P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := 'manohar@gmail.com';
P_VENDOR_CONTACT_REC.URL := 'www.google.com';
P_VENDOR_CONTACT_REC.org_id := 87;
p_vendor_contact_rec.party_site_id := 14653;
p_vendor_contact_rec.org_party_site_id := 14653;
p_vendor_contact_rec.VENDOR_ID := 1228;
x_vendor_contact_id := NULL;
x_per_party_id := NULL;
x_rel_party_id :=NULL;
x_rel_id := NULL;
x_org_contact_id := NULL;
x_party_site_id := NULL;
apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_contact_rec => p_vendor_contact_rec,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => x_per_party_id,
x_rel_party_id => x_rel_party_id,
x_rel_id => x_rel_id,
x_org_contact_id => x_org_contact_id,
x_party_site_id => x_party_site_id
);
DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
DBMS_OUTPUT.put_line ('X_VENDOR_CONTACT_ID = ' || TO_CHAR (x_vendor_contact_id));
DBMS_OUTPUT.put_line ('X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
DBMS_OUTPUT.put_line ('X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));
DBMS_OUTPUT.put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));
DBMS_OUTPUT.put_line ('X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));
DBMS_OUTPUT.put_line ('X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
DBMS_OUTPUT.put_line ('');
COMMIT;

END;

Create Bank Conversion

iby_ext_bankacct_pub.create_ext_bank
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_bank_rec => l_extbank_rec,
x_bank_id => l_bank_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_resp
);

l_extbank_rec.bank_number := l_bank_number;
l_extbank_rec.bank_name := TRIM (cv_stg1.bank_name);
l_extbank_rec.country_code := cv_stg1.country_code;
l_extbank_rec.bank_alt_name:=cv_stg1.bank_name_alt;
l_extbank_rec.bank_number:=cv_stg1.bank_number;

iby_ext_bankacct_pub.create_ext_bank_branch
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_bank_branch_rec => l_extbankbranch_rec,
x_branch_id => l_branch_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_resp
);

l_extbankbranch_rec.bank_party_id := cv_stg1.bank_id;
   l_extbankbranch_rec.branch_name := cv_stg1.bank_branch_name;
   l_extbankbranch_rec.branch_type := cv_stg1.bank_branch_type;
  l_extbankbranch_rec.branch_number:=cv_stg1.branch_number;
  l_extbankbranch_rec.alternate_branch_name:=cv_stg1.branch_name_alt;

hz_location_v2pub.create_location
(p_init_msg_list => 'T',
p_location_rec => l_location_rec,
x_location_id => l_location_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

l_location_rec.country := cv_stg1.country_code;
l_location_rec.address1 := cv_stg1.branch_address ;

l_location_rec.city := cv_stg1.city;
l_location_rec.postal_code := cv_stg1.zip_code;
l_location_rec.created_by_module := 'AMS';

hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

l_party_site_rec.party_id := l_branch_id;
l_party_site_rec.location_id := l_location_id;
l_party_site_rec.identifying_address_flag := 'Y';
l_party_site_rec.created_by_module := 'AMS';

ce_bank_pub.create_bank_acct
(p_init_msg_list => 'T',
p_acct_rec => l_bankacct_rec,
x_acct_id => l_int_bank_acct_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

l_bankacct_rec.bank_id:=cv_stg1.bank_id;
l_bankacct_rec.branch_id := cv_stg1.branch_id;
l_bankacct_rec.account_owner_org_id :=cv_stg1.int_acct_owner_org_id;--24276;-- cv_stg1.int_acct_owner_party_id;
l_bankacct_rec.account_owner_party_id := cv_stg1.int_acct_owner_party_id;
l_bankacct_rec.account_classification := 'INTERNAL';
l_bankacct_rec.bank_account_name := cv_stg1.bank_account_name;
l_bankacct_rec.bank_account_num := cv_stg1.bank_account_num;
l_bankacct_rec.currency := cv_stg1.currency_code;
l_bankacct_rec.acct_type:=cv_stg1.acc_type;
l_bankacct_rec.ap_use_allowed_flag := 'Y';
l_bankacct_rec.ar_use_allowed_flag := 'Y';
l_bankacct_rec.asset_code_combination_id := cv_stg1.gl_cash_account_ccid;
l_bankacct_rec.receipt_multi_currency_flag := cv_stg1.multiple_currency_allowed;
l_bankacct_rec.ap_amount_tolerance := 0;
l_bankacct_rec.ar_amount_tolerance := 0;
l_bankacct_rec.ap_percent_tolerance := 0;
l_bankacct_rec.ar_percent_tolerance := 0;
l_bankacct_rec.ce_amount_tolerance := 0;
l_bankacct_rec.ce_percent_tolerance := 0;
l_bankacct_rec.multi_currency_allowed_flag := 'Y';
l_bankacct_rec.zero_amount_allowed := 'N';
l_bankacct_rec.cash_clearing_ccid := cv_stg1.cash_clearing_account_ccid;
l_bankacct_rec.bank_charges_ccid := cv_stg1.bank_charges_account_ccid;

ce_bank_pub.create_bank_acct_use
(p_init_msg_list => 'T',
p_acct_use_rec => l_bankacct_use_rec_type,
x_acct_use_id => l_acct_use_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

l_bankacct_use_rec_type.bank_account_id :=l_int_bank_acct_id;
l_bankacct_use_rec_type.org_type := 'OU';
l_bankacct_use_rec_type.org_id :=cv_stg1.org_id;
l_bankacct_use_rec_type.ap_use_enable_flag := 'Y';
l_bankacct_use_rec_type.ar_use_enable_flag := 'Y';
l_bankacct_use_rec_type.default_account_flag := 'N';
l_bankacct_use_rec_type.authorized_flag := 'Y';
l_bankacct_use_rec_type.asset_code_combination_id := cv_stg1.gl_cash_account_ccid;
l_bankacct_use_rec_type.bank_charges_ccid := cv_stg1.bank_charges_account_ccid;
l_bankacct_use_rec_type.cash_clearing_ccid := cv_stg1.cash_clearing_account_ccid;

If you are Facing Problem with create bank with the same bank_account_name but different bank_account_name.

You have to apply the patch.

Service Request Number is :1264514.1

Patch 10261393:R12.CE.B

version: 12.1.3

Gl BALANCE

INSERT INTO gl_interface
(ledger_id,
date_created,
actual_flag,
currency_code,
period_name,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
entered_dr,
entered_cr,
user_je_category_name,
user_je_source_name,
status,
accounting_date,
created_by,
code_combination_id,
USER_CURRENCY_CONVERSION_TYPE,
currency_conversion_date
)
VALUES (cv_int.ledger_id,--2021,--cv_int.ledger_id,
SYSDATE,
cv_int.actual_flag,
cv_int.currency_code,
cv_int.period_name,
cv_int.segment1,
cv_int.segment2,
cv_int.segment3,
cv_int.segment4,
cv_int.segment5,
cv_int.segment6,
cv_int.segment7,
cv_int.segment8,
cv_int.closing_balance_dr,
cv_int.closing_balance_cr,
cv_int.user_je_category_name,
cv_int.user_je_source_name,
'POSTED',
cv_int.accounting_date,
fnd_global.user_id,
cv_int.code_combination_id,
cv_int.USER_CURRENCY_CONVERSION_TYPE,
cv_int.currency_conversion_date
);

Tuesday, December 13, 2011

R12 AR Customer Contacts Query

SELECT distinct
hp2.party_name ,
hp1.person_first_name ,
hp1.person_middle_name ,
hp1.person_last_name ,
--hp1.party_name,
hp2.orig_system_reference ,
hp2.person_name_suffix ,
hp2.party_number ,
hp2.party_type ,
hcp.contact_point_type ,
hcp.contact_point_purpose ,
hcp.email_address ,
hcp.telephone_type ,
hcp.owner_table_name ,
hcp.TIME_ZONE ,
hcp.phone_area_code ,
hcp.phone_country_code ,
hcp.phone_number ,
hcp.phone_extension ,
hcp.phone_line_type ,
hcp.telex_number ,
hcp.web_type ,
hcp.url ,
hcp.raw_phone_number ,
hcp.primary_flag ,
hp2.party_number subject_party_number,
hp2.orig_system_reference obj_orig_system_ref,
hr.relationship_code ,
hr.subject_type ,
hr.object_type ,
hr.relationship_type ,
hr.directional_flag ,
--hr.created_by_module ,
hoc.department_code ,
hoc.department ,
--hoc.title ,
hoc.job_title ,
hoc.status ,
hoc.decision_maker_flag ,
--hp1.Person_pre_name_adjunct ,
hca.account_number,
hcr.cust_acct_site_id ,
hcasa.orig_system_reference acct_site_orig_sys_ref,
'ACCOUNT__SITE_LEVEL' level_of_contact ,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.postal_code,
hl.state,
hl.province,
hl.county ,
hl.country,
hps.identifying_address_flag ,
hou.name,hou.organization_id
FROM ar.hz_contact_points hcp ,
ar.hz_parties hp1 ,
ar.hz_parties hp2 ,
ar.hz_relationships hr ,
ar.hz_org_contacts hoc ,
ar.hz_cust_accounts hca,
ar.hz_cust_acct_sites_all hcasa,
ar.hz_cust_account_roles hcr,
ar.hz_party_sites hps,
ar.hz_locations hl,
hr_operating_units hou
WHERE 1 = 1
and hp2.party_id = hca.party_id
and hcr.role_type ='CONTACT'
and hca.cust_account_id = hcr.cust_account_id
and hr.party_id = hcr.party_id
--and hcr.cust_acct_site_id is not null
AND hca.cust_account_id = hcasa.cust_account_id
AND hcr.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcp.owner_table_id(+) = hp1.party_id
AND hcp.owner_table_name(+) = 'HZ_PARTIES'
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp2.party_id
AND hr.relationship_id = hoc.party_relationship_id
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hr.directional_flag = 'F'
AND hoc.status = 'A'
AND hca.status = 'A'
AND hp1.status = 'A'
AND hp2.status = 'A'
AND hr.status = 'A'
AND NVL(hcp.status,'A') = 'A'
AND NVL(hcr.status,'A') = 'A'
AND hcasa.party_site_id = hps.party_site_id
and hps.party_id=hp2.party_id(+)
AND hps.location_id = hl.location_id
--and hcasa.location_id=hl.location_id
AND hcasa.org_id = hou.organization_id
--and hp2.party_name LIKE 'Marks%Spencer Plc.%'
and hps.status='A'
--and hp2.party_number=212414
--and hl.country='US'
--AND hou.name not in ('OBT GLOBAL INC.','ZENSAR THOUGHT DIGITAL LLC','ZENSAR FINLAND')
--AND (length(hl.state)>2 OR hl.state like '.')
--AND length(hl.state)>2
--AND hou.name not in ('OBT GLOBAL INC.','ZENSAR THOUGHT DIGITAL LLC','ZENSAR FINLAND')
--and hp2.party_name='Cancer Research UK Limited'
--order by 1,4; --774
union
select hp.Party_name,
'Contact_Person' person_first_name,
hp.person_name_suffix person_middle_name ,
hp.person_name_suffix person_last_name ,
--hou.name,
hp.orig_system_reference,
hp.person_name_suffix ,
hp.party_number ,
hp.party_type ,
hp.person_name_suffix contact_point_type,

hp.person_name_suffix contact_point_purpose ,
hp.person_name_suffix email_address ,
hp.person_name_suffix telephone_type ,
hp.person_name_suffix owner_table_name ,
acv.per_lang_object_version TIME_ZONE ,
hp.person_name_suffix phone_area_code ,
hp.person_name_suffix phone_country_code ,
hp.person_name_suffix phone_number ,
hp.person_name_suffix phone_extension ,
hp.person_name_suffix phone_line_type ,
hp.person_name_suffix telex_number ,
hp.person_name_suffix web_type ,
hp.person_name_suffix url ,
hp.person_name_suffix raw_phone_number ,
hp.person_name_suffix primary_flag ,

hp.party_number subject_party_number,
hp.orig_system_reference obj_orig_system_ref,

'CONTACT_OF' relationship_code,
'PERSON' subject_type,
'ORGANIZATION' object_type,
'CONTACT' relationship_type,
'F' directional_flag,
hp.person_name_suffix department_code,
hp.person_name_suffix department,
hp.person_name_suffix job_title,
'A' status,
'N' decision_maker_flag,
hca.account_number,
hcasa.cust_acct_site_id,
hcasa.orig_system_reference acct_site_orig_sys_ref,
'ACCOUNT__SITE_LEVEL' level_of_contact ,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.postal_code,
hl.state,
hl.province,
hl.county ,
hl.country,
hpsv.identifying_address_flag ,
hou.name,hou.organization_id
from ar.hz_parties hp,
apps.hz_cust_accounts hca,
ar.hz_cust_acct_sites_all hcasa,
apps.ar_contacts_v acv,
apps.hz_party_sites_v hpsv,
apps.hr_operating_units hou,
ar.hz_locations hl
where hp.party_id=hca.party_id
and hca.cust_account_id= hcasa.cust_account_id
and hcasa.cust_acct_site_id=acv.address_id(+)
and acv.last_name is null
and hcasa.party_site_id=hpsv.party_site_id
and hpsv.party_id= hp.party_id
and hcasa.org_id= hou.organization_id
and hpsv.location_id=hl.location_id
and hp.status='A'
--and acv.status='A'
and hca.status='A'
and hpsv.status='A'

R12 AR Customer Site Query

SELECT distinct hcsua.site_use_code
,hcasa.status
,hcsua.LOCATION
,hcsua.primary_flag
,hcsua.orig_system_reference
,hcsua.tax_reference
,hcsua.status site_use_status
,hcasa.attribute4 primary_per_type
,hcasa.attribute4 cust_acct_status
,hp.party_number
,hca.account_number
,hcsua.created_by_module
,hcasa.attribute4 rec_status
,hcpc.NAME profile_class
,hcasa.attribute4 profile_class_id
,hcp.dunning_letters
,hcp.override_terms
,hcp.credit_balance_statements -- Added on 24-07-09
,hcpc.statements -- Added on 24-07-09
,rt.NAME term_name
,hou.NAME operating_unit
,hp.jgzz_fiscal_code taxpayer_id
,hcasa.attribute4 party_site_number
,hcasa.orig_system_reference site_orig_system_reference
,hcsua.tax_code -- Added on 25-06-09
,arsc.NAME statement_cycle -- Added on 24-07-09
,arc.NAME collector_name -- Added on 24-07-09
,hcasa.attribute4 org_id
,hcasa.attribute4 collector_id
,hcasa.attribute4 statement_cycle_id
,hcasa.attribute4 error_code
,hcasa.attribute4 error_message
,hcasa.attribute4 company
,hcasa.attribute4 payment_term_id
,hcp.account_status profile_acct_status
,gcck1.concatenated_segments Revenue
,gcck2.concatenated_segments Receivable
,gcck3.concatenated_segments Tax
,hp.tax_reference tax_reg_number
,att.name Terms_name
,rtt.name Domestic
,RES.RESOURCE_NAME
,hcsua.territory_id domestic_id
,hcsua.payment_term_id
,hcsua.PRIMARY_SALESREP_ID
FROM
ar.hz_parties hp
,ar.hz_party_sites hps
,ar.hz_cust_accounts hca
,ar.hz_cust_acct_sites_all hcasa
,ar.hz_cust_site_uses_all hcsua
,ar.hz_customer_profiles hcp
,ar.ra_terms_tl rt
,ar.hz_cust_profile_classes hcpc
,hr.hr_all_organization_units hou
,ar.ar_statement_cycles arsc
,ar.ar_collectors arc
,apps.gl_code_combinations_kfv gcck1
,apps.gl_code_combinations_kfv gcck2
,apps.gl_code_combinations_kfv gcck3
,apps.ra_terms_tl att
,apps.ra_territories rtt
,apps.JTF_RS_SALESREPS SR
,apps.JTF_RS_RESOURCE_EXTNS_VL RES
WHERE 1=1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hp.party_type = 'ORGANIZATION'
AND hp.status = 'A'
AND hca.status = 'A'
AND hcasa.status ='A'
AND hcsua.status ='A'
AND hcasa.org_id = hcsua.org_id
AND hcsua.payment_term_id = rt.term_id(+)
AND hcsua.site_use_id = hcp.site_use_id(+)
AND hcpc.profile_class_id(+) = hcp.profile_class_id
AND arsc.statement_cycle_id(+) = hcpc.statement_cycle_id
AND arc.collector_id(+) = hcpc.collector_id
AND hcp.status(+) = 'A'
AND hcpc.status (+) = 'A'
AND hou.organization_id = hcsua.org_id
AND hcsua.site_use_code IN ('BILL_TO', 'SELF_SERVICE_USER')
and hp.party_id=hps.party_id
and hps.party_site_id=hcasa.party_site_id
and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id
and gcck1.code_combination_id(+)=hcsua.gl_id_rev
and gcck2.code_combination_id(+)=hcsua.gl_id_rec
and gcck3.code_combination_id(+)=hcsua.gl_id_tax
and hcsua.payment_term_id=att.term_id(+)
and hcsua.territory_id=rtt.territory_id
AND hcsua.PRIMARY_SALESREP_ID = SR.SALESREP_ID (+)
AND SR.RESOURCE_ID = RES.RESOURCE_ID (+)
--and hp.party_number in (1249,1244,1211,1214,1218,1222,1226)
--AND hca.customer_class_code <> 'INTERCOMPANY';
--and hp.party_name='ALDAR PROPERTIES PJSC'
order by 10

Wednesday, December 28, 2011

Tuesday, December 27, 2011

Tuesday, December 13, 2011