SELECT hcasa.org_id,
role_acct.account_number,
hcasa.orig_system_reference,
rel.subject_id,rel.object_id
,party.party_id party_id,
rel_party.party_id rel_party_id,
acct_role.cust_account_id ,
acct_role.cust_acct_site_id ,
party.person_pre_name_adjunct contact_prefix,
substr(party.person_first_name, 1, 40) contact_first_name,
substr(party.person_middle_name, 1, 40) contact_middle_name,
substr(party.person_last_name, 1, 50) contact_last_name,
party.person_name_suffix contact_suffix,
acct_role.status, org_cont.job_title contact_job_title,
org_cont.job_title_code contact_job_title_code,
rel_party.address1 contact_address1,
rel_party.address2 contact_address2,
rel_party.address3 contact_address3,
rel_party.address4 contact_address4,
rel_party.country contact_country,
rel_party.state contact_state,
rel_party.city contact_city,
rel_party.county contact_county,
rel_party.postal_code contact_postal_code
FROM hz_contact_points cont_point,
hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct,
hz_contact_restrictions cont_res,
hz_person_language per_lang,
hz_cust_acct_sites_all hcasa
WHERE acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel_party.party_id = rel.party_id
and cont_point.owner_table_id(+) = rel_party.party_id
and cont_point.contact_point_type(+) = 'EMAIL'
and cont_point.primary_flag(+) = 'Y'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
and party.party_id = per_lang.party_id(+)
and per_lang.native_language(+) = 'Y'
and party.party_id = cont_res.subject_id(+)
and cont_res.subject_table(+) = 'HZ_PARTIES'
and role_acct.cust_account_id = hcasa.cust_account_id
and hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
-- and acct_role.cust_account_id = p_customer_id --3177
-- and acct_role.cust_acct_site_id = p_address_id
-- and hcasa.org_id = 90
Showing posts with label AR. Show all posts
Showing posts with label AR. Show all posts
Friday, March 22, 2013
Tuesday, December 27, 2011
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
);
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
R12 AR Customer Query
SELECT hl.orig_system_reference
,hl.country
,hl.address1
,hl.address2
,hl.address3
,hl.address4
,hl.city
,hl.postal_code
,hl.state
,hl.province
,hl.county
,hl.content_source_type
,hl.actual_content_source actual_content_source
,hps.party_site_number
,hps.identifying_address_flag
,hps.status
,hps.party_site_name
,hps.created_by_module
,hps.actual_content_source site_actual_content_source
,hcasa.orig_system_reference site_orig_system_reference
,hcasa.status acct_site_status
,hp.party_number
,hca.account_number
,hou.NAME operting_unit_name
,bill_to_flag
FROM ar.hz_locations hl
,ar.hz_party_sites hps
,ar.hz_cust_acct_sites_all hcasa
,ar.hz_parties hp
,ar.hz_cust_accounts hca
,hr_operating_units hou
WHERE hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.org_id = hou.organization_id
AND hp.party_type ='ORGANIZATION'
AND hps.status = 'A'
AND hcasa.status = 'A'
AND hp.status = 'A'
AND hca.status = 'A'
R12 AR Customer Query
SELECT hp.party_number
,hp.party_name
,hp.party_type
,hp.orig_system_reference
--,hp.category_code
--,hp.jgzz_fiscal_code taxpayer_id
,hp.status
,hp.tax_name
,hp.person_first_name
,hp.person_middle_name
,hp.person_last_name
,hp.person_title
,hp.country
,hp.address1
,hp.address2
,hp.address3
,hp.address4
,hp.city
,hp.postal_code
,hp.state
,hp.province
,hca.status account_status
,hca.created_by_module
,hp.county
,hca.account_number
,hca.customer_type
,hca.account_established_date
,hca.account_activation_date
,hca.account_name
,hca.tax_code
--,hca.attribute2 -- Added on 24-07-09
,hca.attribute3
,hcpc.profile_class_id
,hcpc.status rec_status
,hp.sic_code_type category_code
,hca.customer_class_code
,hca.orig_system_reference acct_orig_sys_ref
,hp.tax_reference tax_reg_number
,hcpc.NAME profile_class
,hcp.dunning_letters
,hcp.override_terms
,hcp.credit_balance_statements -- Added on 24-07-09
,hcpc.statements -- Added on 24-07-09
,rt.NAME payment_term
,arsc.NAME statement_cycle -- Added on 24-07-09
,arc.NAME collector_name -- Added on 24-07-09
FROM ar.hz_parties hp
,ar.hz_cust_accounts hca
,ar.hz_customer_profiles hcp
,ar.ra_terms_tl rt
,ar.hz_cust_profile_classes hcpc
,ar.ar_statement_cycles arsc
,ar.ar_collectors arc
--,ap_terms_tl att
WHERE hp.party_id = hca.party_id
AND hcp.party_id = hp.party_id
AND hca.cust_account_id = hcp.cust_account_id
AND hcp.site_use_id IS NULL
AND hca.payment_term_id = rt.term_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 hp.status = 'A'
AND hca.status = 'A'
AND hcp.status = 'A'
AND hcpc.status = 'A'
AND hp.party_type = 'ORGANIZATION'
--AND hp.party_name='BARCLAYS BANK PLC'
--and att.term_id(+)=rt.term_id
--and rownum<11
order by 2
Subscribe to:
Posts (Atom)