AR Customer Upload

Description

Overview of the AR Customer Upload

Purpose

The AR Customer Upload allows users to create Customer Accounts and Sites on a single Excel line while being able to individually default the level (Account or Site) at which the profile class, tax registration, bank account, contact, and attachment details are to be associated.

Specifically:

the ability to default the operating unit and profile class from report parameters when creating new customers or customer sites;
the creation of the Customer Account and Site on a single Excel line while being able to individually default the level (Account or Site) at which the profile class, tax registration, bank account, contact, and attachment details are to be associated;
support for uploading file attachments.

Process Parameters

Parameter	Description
Default Operating Unit	The default operating unit to be used when creating new customers. When creating a new customer in the upload, once you start entering the site level details (Site Number, Name or Country), the Operating Unit will be automatically defaulted to the operating unit name specified in this parameter.
Default Profile Class	The default profile class to be used when creating new customers. When creating a new customer in the upload, the profile class will be defaulted to the profile class name specified in this parameter.
Default Profile Assign. Level	Determines the default level (Account or Site) to which the profile class should be associated with.
Default Tax Reg. Assign. Level	Determines the default level (Account or Site) to which the tax registrations should be associated with.
Default Bank Acct Assign. Level	Determines the default level (Account or Site) to which the bank accounts should be associated with.
Default Contact Assign. Level	Determines the default level (Account or Site) to which the contacts should be associated with.
Default Attachment Assign. Level	Determines the default level (Account or Site) to which the attachments should be associated with.

Upload Excel Columns

The following new columns have been made available in the Upload Excel:

Upload Column	Description
Profile Assignment Level	Determines if the Profile should be linked to the Customer Account or Customer Site.
Tax Reg Assignment Level	Determines if the Tax Registration should be linked to the Customer Account or Customer Site.
Bank Account Assignment Level	Determines if the Bank Account should be linked to the Customer Account or Customer Site.
Contact Assignment Level	Determines if the Contact should be linked to the Customer Account or Customer Site.
Attachment Assignment Level	Determines if the Attachment should be linked to the Customer Account or Customer Site.

By default, these new columns appear in the upload excel template at the start of the columns that pertain to each entity (Profile, Tax Registration, Bank Account, Contact, or Attachment). E.g.

The List of Values for these columns is either ‘Account’ or ‘Site’.
The value of ‘Site’ is only selectable once the user has started to enter some site level details.
The values for these columns will automatically default from the corresponding default report parameter detailed above. However, the default value can be overridden in the upload excel.
If the assignment level columns are hidden in the upload excel, the values specified in the Default Assignment Level report parameters will still be applied by the upload. That is to say: it is not a requirement to have the Assignment Level columns visible in the upload excel template.
If a value is not specified in the assignment level parameters, and no default assignment level is specified in the report parameters, then the upload will automatically determine the level at which the entity should be linked based on the presence of site level data in the upload row. If no site level data is present, then the entity will be linked at the customer account level. If site level data is present, then the entity will be linked at the site level.

Support for File Attachments

The upload now supports the upload of File Attachments.

You can now select an Attachment Type of ‘File’.

When you select an attachment type of File, the attachment content column contains the location (directory path and file name) of the file name to be uploaded on the user’s PC. If you double click on the Attachment Content Column, the user will be prompted to select the file to be upload:

Once selected, the location (directory path and file name) of the file is displayed in the Attachment Content column:

Prior to uploading the upload excel into Oracle, it is first necessary to upload the file attachments. A new ‘Attachments’ button has been added to the ‘Blitz Upload’ tab in the upload excel:

Clicking on the ‘Attachments’ button will upload the File Attachments listed in the ‘Attachment Content’ Column to Oracle.

Once the files to be attached are uploaded, the ‘Attachment File ID’ column will be populated with File Identifier. This is used during the AR Customer Upload process to link the uploaded file to the Customer Account or Site attachment.

Once the files to be attached have been uploaded, the AR Customer Upload excel can be uploaded following the normal process.
Note that it is also possible to update existing file attachments using the AR Customer Upload as well.

Copy

with
q_contacts as
(select
 hcar.cust_account_role_id,
 hcar.cust_account_id,
 hcar.cust_acct_site_id cust_acct_site_id,
 case when hcar.status='A' AND hr.status='A' then 'A' else 'I' end contact_status_code,
 hprel.primary_phone_contact_pt_id pri_phone_contact_point_id,
 hcp.contact_point_id sec_phone_contact_point_id,
 --
 xxen_util.meaning(hpsub.person_pre_name_adjunct,'CONTACT_TITLE',222) contact_prefix,
 hpsub.person_first_name contact_first_name,
 hpsub.person_middle_name contact_middle_name,
 hpsub.person_last_name contact_last_name,
 hpsub.person_name_suffix contact_suffix,
 hpsub.person_title contact_title,
 xxen_util.meaning(case when hcar.status='A' AND hr.status='A' then 'A' else 'I' end,'HZ_CPUI_REGISTRY_STATUS',222) contact_status,
 hprel.email_address contact_email,
 hprel.primary_phone_country_code contact_phone_country_code,
 hprel.primary_phone_area_code contact_phone_area_code,
 hprel.primary_phone_number contact_phone_number,
 hprel.primary_phone_extension contact_phone_extension,
 xxen_util.meaning(hprel.primary_phone_line_type,'PHONE_LINE_TYPE',222) contact_phone_type,
 hcp.phone_country_code secondary_phone_country_code,
 hcp.phone_area_code secondary_phone_area_code,
 hcp.phone_number secondary_phone_number,
 hcp.phone_extension secondary_phone_extension,
 xxen_util.meaning(hcp.phone_line_type,'PHONE_LINE_TYPE',222) secondary_phone_type,
 xxen_util.meaning(hoc.job_title_code,'RESPONSIBILITY',222) contact_job_title_code,
 hoc.job_title contact_job_title,
 hoc.contact_number,
 (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = hl.country) contact_country,
 hl.address1 contact_address_line1,
 hl.address2 contact_address_line2,
 hl.address3 contact_address_line3,
 hl.address4 contact_address_line4,
 hl.city contact_city,
 hl.county contact_county,
 hl.state contact_state,
 hl.postal_code contact_postal_code,
 hl.sales_tax_geocode contact_geography_override,
 hps.mailstop contact_mailstop
 from
 hz_cust_account_roles hcar,
 hz_relationships hr,
 hz_parties hprel,
 hz_parties hpsub,
 hz_parties hpobj,
 hz_org_contacts hoc,
 hz_cust_accounts hca,
 hz_party_sites hps,
 hz_locations hl,
 (select
  hcp.contact_point_id,
  hcp.owner_table_id party_id,
  hcp.phone_country_code,
  hcp.phone_area_code,
  hcp.phone_number,
  hcp.phone_extension,
  hcp.phone_line_type,
  row_number() over (partition by hcp.owner_table_id order by hcp.last_update_date desc,hcp.contact_point_id desc) row_num
  from
  hz_contact_points hcp
  where
  hcp.owner_table_name = 'HZ_PARTIES' and
  hcp.contact_point_type = 'PHONE' and
  hcp.status = 'A' and
  nvl(hcp.primary_flag,'N') != 'Y'
 ) hcp
 where
 hcar.role_type = 'CONTACT' and
 hcar.party_id = hr.party_id and
 hr.party_id = hprel.party_id and
 hr.subject_id = hpsub.party_id and
 hr.object_id = hpobj.party_id and
 hr.relationship_id = hoc.party_relationship_id and
 hcar.cust_account_id = hca.cust_account_id and
 hr.object_id = hca.party_id and
 hprel.party_id = hps.party_id(+) and
 nvl(hps.identifying_address_flag(+),'Y') = 'Y' and
 nvl(hps.status(+),'A') = 'A' and
 hps.location_id = hl.location_id (+) and
 hprel.party_id = hcp.party_id (+) and
 hcp.row_num (+) = 1 and
 ((hcar.status='A' AND hr.status='A') or
  ((hcar.status ='I' OR hr.status='I') AND hpsub.status='A' and hpobj.status= 'A' AND hr.status <> 'M')
 )
),
q_bank_accounts as
(select
 iepa.cust_account_id              cust_account_id,
 iepa.acct_site_use_id             site_use_id,
 ipiua.instrument_payment_use_id,
 iepa.ext_payer_id,
 ieba.ext_bank_account_id          bank_account_id,
 cbbv.bank_name                    bank_name,
 cbbv.bank_number                  bank_number,
 (select ftv.territory_short_name
  from   fnd_territories_vl ftv
  where  ftv.territory_code = cbbv.bank_home_country
 )                                 bank_country,
 cbbv.bank_branch_name             bank_branch_name,
 cbbv.branch_number                bank_branch_number,
 cbbv.bank_branch_type             bank_branch_type,
 cbbv.eft_swift_code               bank_branch_bic,
 ieba.bank_account_name            bank_acct_name,
 ieba.bank_account_num             bank_acct_num,
 ieba.check_digits                 bank_acct_check_digits,
 ieba.currency_code                bank_acct_currency,
 --xxen_util.meaning(ieba0.foreign_payment_use_flag,'YES_NO',0)  bank_allow_foreign_pay,
 ieba.iban                         bank_acct_iban,
 ieba.bank_account_name_alt        bank_acct_name_alt,
 ieba.account_suffix               bank_acct_suffix,
 xxen_util.meaning(ieba.bank_account_type,'BANK_ACCOUNT_TYPE',260)
                                   bank_acct_type,
 ieba.secondary_account_reference  bank_acct_sec_reference,
 ieba.description                  bank_acct_description,
 ieba.contact_name                 bank_acct_contact,
 ieba.contact_phone                bank_acct_contact_phone,
 ieba.contact_fax                  bank_acct_contact_fax,
 ieba.contact_email                bank_acct_contact_email,
 ipiua.start_date                  bank_acct_assignmt_start_date,
 ipiua.end_date                    bank_acct_assignmt_end_date
 from
 iby_external_payers_all iepa,
 iby_pmt_instr_uses_all ipiua,
 iby_ext_bank_accounts ieba,
 ce_bank_branches_v cbbv
 where
 iepa.ext_payer_id = ipiua.ext_pmt_party_id and
 ipiua.instrument_id = ieba.ext_bank_account_id and
 ieba.branch_id = cbbv.branch_party_id and
 iepa.payment_function = 'CUSTOMER_PAYMENT' and
 ipiua.payment_function = 'CUSTOMER_PAYMENT' and
 ipiua.instrument_type = 'BANKACCOUNT'
 -- and sysdate between ipiua.start_date and nvl(ipiua.end_date,sysdate)
),
q_attachments as
(select
 fad.attached_document_id attachment_id,
 fad.entity_name,
 fad.pk1_value entity_id,
 fad.seq_num,
 fdv.datatype_name type,
 fdcav.user_name category,
 fdv.title,
 fdv.description,
 case fdv.datatype_id
 when 1 then (select fdst.short_text from fnd_documents_short_text fdst where fdst.media_id = fdv.media_id)
 when 5 then fdv.url
 when 6 then (select fl.file_name from fnd_lobs fl where fl.file_id = fdv.media_id)
 else null
 end content,
 case when fdv.datatype_id = 6 then fdv.media_id else null end file_id
 from
 fnd_attached_documents fad,
 fnd_documents_vl fdv,
 fnd_doc_categories_active_vl fdcav
 where
 fad.document_id = fdv.document_id and
 fdv.category_id = fdcav.category_id and
 fdv.datatype_id in (1,5,6)
)
--
-- main query starts here
--
select
y.*
from
(
select
null action_,
null status_,
null message_,
null request_id_,
:p_upload_mode upload_mode_,
to_number(null) cust_account_row_id,
to_number(null) cust_acct_site_row_id,
to_number(null) cust_site_use_row_id,
to_number(null) cust_profile_row_id,
to_number(null) cust_profile_amt_row_id,
to_number(null) cust_account_role_row_id,
to_number(null) bank_intsr_pay_use_row_id,
to_number(null) tax_registration_row_id,
to_number(null) attachment_row_id,
:p_created_by_module created_by_module,
:p_dflt_prof_assign_lvl dflt_profile_assign_lvl,
:p_dflt_tax_reg_assign_lvl dflt_tax_reg_assign_lvl,
:p_dflt_bank_acct_assign_lvl dflt_bank_acct_assign_lvl,
:p_dflt_ctct_assign_lvl dflt_ctct_assign_lvl,
:p_dflt_attchmt_assign_lvl dflt_attchmt_assign_lvl,
x.*
from
(
--
-- Q1 Account Level Profile or Account with no sites
--
select /*+ push_pred(ctct) push_pred(ba) push_pred(attchmt) */
--
-- party
--
xxen_util.meaning(hp.party_type,'PARTY_TYPE',222) party_type,
hp.party_name,
hp.party_number registry_id,
hp.known_as alias,
hp.organization_name_phonetic name_pronunciation,
hp.duns_number,
-- party dff
xxen_util.display_flexfield_context(222,'HZ_PARTIES',hp.attribute_category) party_dff_context,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE1',hp.rowid,hp.attribute1) hz_party_attribute1,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE2',hp.rowid,hp.attribute2) hz_party_attribute2,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE3',hp.rowid,hp.attribute3) hz_party_attribute3,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE4',hp.rowid,hp.attribute4) hz_party_attribute4,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE5',hp.rowid,hp.attribute5) hz_party_attribute5,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE6',hp.rowid,hp.attribute6) hz_party_attribute6,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE7',hp.rowid,hp.attribute7) hz_party_attribute7,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE8',hp.rowid,hp.attribute8) hz_party_attribute8,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE9',hp.rowid,hp.attribute9) hz_party_attribute9,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE10',hp.rowid,hp.attribute10) hz_party_attribute10,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE11',hp.rowid,hp.attribute11) hz_party_attribute11,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE12',hp.rowid,hp.attribute12) hz_party_attribute12,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE13',hp.rowid,hp.attribute13) hz_party_attribute13,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE14',hp.rowid,hp.attribute14) hz_party_attribute14,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE15',hp.rowid,hp.attribute15) hz_party_attribute15,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE16',hp.rowid,hp.attribute16) hz_party_attribute16,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE17',hp.rowid,hp.attribute17) hz_party_attribute17,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE18',hp.rowid,hp.attribute18) hz_party_attribute18,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE19',hp.rowid,hp.attribute19) hz_party_attribute19,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE20',hp.rowid,hp.attribute20) hz_party_attribute20,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE21',hp.rowid,hp.attribute21) hz_party_attribute21,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE22',hp.rowid,hp.attribute22) hz_party_attribute22,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE23',hp.rowid,hp.attribute23) hz_party_attribute23,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE24',hp.rowid,hp.attribute24) hz_party_attribute24,
--
-- cust account
--
hca.account_number acct_number,
hca.account_name acct_description,
hca.orig_system_reference acct_orig_sys_ref,
xxen_util.meaning(hca.status,'REGISTRY_STATUS',222) acct_status,
hca.account_established_date acct_established_date,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) acct_classification,
xxen_util.meaning(hca.customer_type,'CUSTOMER_TYPE',222) acct_type,
xxen_util.meaning(hca.sales_channel_code,'SALES_CHANNEL',660) acct_sales_channel,
-- account order management
(select qslhv.name from qp_secu_list_headers_v qslhv where qslhv.list_header_id = hca.price_list_id) acct_price_list,
hca.item_cross_ref_pref acct_item_type_identifier,
xxen_util.meaning(hca.date_type_preference,'REQUEST_DATE_TYPE',660) acct_request_date_type,
xxen_util.meaning(hca.freight_term,'FREIGHT_TERMS',660) acct_freight_terms,
xxen_util.meaning(hca.fob_point,'FOB',222) acct_fob_point,
(select osmv.meaning from oe_ship_methods_v osmv where osmv.lookup_code = hca.ship_via) acct_ship_method,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = hca.warehouse_id) acct_warehouse,
hca.dates_negative_tolerance acct_earliest_schedule_limit,
hca.dates_positive_tolerance acct_latest_schedule_limit,
xxen_util.meaning(hca.invoice_quantity_rule,'INVOICE_BASIS',660) acct_overship_invoice_base,
hca.over_shipment_tolerance acct_over_shipment_tolerance,
hca.under_shipment_tolerance acct_under_shipment_tolerance,
hca.over_return_tolerance acct_over_return_tolerance,
hca.under_return_tolerance acct_under_return_tolerance,
--xxen_util.meaning(hca.cancel_unshipped_lines_flag,'YES_NO',0) acct_cancel_unshipped_lines, /*not in r1213 */
xxen_util.meaning(hca.sched_date_push_flag,'YES_NO',0) acct_push_group_schedule_date,
xxen_util.meaning(hca.arrivalsets_include_lines_flag,'YES_NO',0) acct_lines_in_arrival_sets,
xxen_util.meaning(hca.ship_sets_include_lines_flag,'YES_NO',0) acct_lines_in_ship_sets,
(select
 rm.name receipt_method
 from
 ra_cust_receipt_methods rcrm,
 ar_receipt_methods rm
 where
 rcrm.receipt_method_id = rm.receipt_method_id and
 rcrm.primary_flag = 'Y' and
 trunc(sysdate) between nvl(rcrm.start_date,trunc(sysdate)) and nvl(rcrm.end_date,trunc(sysdate)) and
 rcrm.customer_id = hca.cust_account_id and
 rcrm.site_use_id is null and
 rownum <= 1
) acct_primary_receipt_method,
-- tax
xxen_util.meaning(zptp0.process_for_applicability_flag,'YES_NO',0) acct_allow_tax_applicability,
xxen_util.meaning(zptp0.allow_offset_tax_flag,'YES_NO',0) acct_allow_offset_taxes,
xxen_util.meaning(zptp0.self_assess_flag,'YES_NO',0) acct_self_assessment,
xxen_util.meaning(nvl(zptp0.rounding_level_code,decode(hca.tax_header_level_flag,'Y','HEADER','N','LINE',null)),'ZX_ROUNDING_LEVEL',0) acct_tax_rounding_level,
xxen_util.meaning(nvl(zptp0.rounding_rule_code,hca.tax_rounding_rule),'ZX_ROUNDING_RULE',0) acct_tax_rounding_rule,
xxen_util.meaning(zptp0.inclusive_tax_flag,'YES_NO',0) acct_inclusive_tax,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp0.country_code) acct_tax_reporting_country,
zptp0.registration_type_code acct_tax_reporting_reg_type,
zptp0.rep_registration_number acct_tax_reporting_reg_number,
-- cust account dff
xxen_util.display_flexfield_context(222,'RA_CUSTOMERS_HZ',hca.attribute_category) acct_dff_context,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE1',hca.rowid,hca.attribute1) hz_cust_acct_attribute1,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE2',hca.rowid,hca.attribute2) hz_cust_acct_attribute2,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE3',hca.rowid,hca.attribute3) hz_cust_acct_attribute3,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE4',hca.rowid,hca.attribute4) hz_cust_acct_attribute4,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE5',hca.rowid,hca.attribute5) hz_cust_acct_attribute5,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE6',hca.rowid,hca.attribute6) hz_cust_acct_attribute6,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE7',hca.rowid,hca.attribute7) hz_cust_acct_attribute7,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE8',hca.rowid,hca.attribute8) hz_cust_acct_attribute8,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE9',hca.rowid,hca.attribute9) hz_cust_acct_attribute9,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE10',hca.rowid,hca.attribute10) hz_cust_acct_attribute10,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE11',hca.rowid,hca.attribute11) hz_cust_acct_attribute11,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE12',hca.rowid,hca.attribute12) hz_cust_acct_attribute12,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE13',hca.rowid,hca.attribute13) hz_cust_acct_attribute13,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE14',hca.rowid,hca.attribute14) hz_cust_acct_attribute14,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE15',hca.rowid,hca.attribute15) hz_cust_acct_attribute15,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE16',hca.rowid,hca.attribute16) hz_cust_acct_attribute16,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE17',hca.rowid,hca.attribute17) hz_cust_acct_attribute17,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE18',hca.rowid,hca.attribute18) hz_cust_acct_attribute18,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE19',hca.rowid,hca.attribute19) hz_cust_acct_attribute19,
xxen_util.display_flexfield_value(222,'RA_CUSTOMERS_HZ',hca.attribute_category,'ATTRIBUTE20',hca.rowid,hca.attribute20) hz_cust_acct_attribute20,
--
-- party site
--
hps.party_site_number site_number,
hps.party_site_name site_name,
hps.addressee site_addressee,
xxen_util.meaning(hps.identifying_address_flag,'YES_NO',0) site_identifying_address,
xxen_util.meaning(hps.status,'REGISTRY_STATUS',222) site_status,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = hl.country) country,
hl.address1 address_line1,
hl.address2 address_line2,
hl.address3 address_line3,
hl.address4 address_line4,
hl.city,
hl.county,
hl.state,
hl.postal_code,
--hl.province,
--xxen_util.meaning(hl.address_style,'ADDRESS_STYLE',0) address_style,
hl.sales_tax_geocode geography_code_override,
hz_format_pub.format_address(hl.location_id,null,null,', ') site_address,
--
hcp1.phone_country_code site_phone_country_code,
hcp1.phone_area_code site_phone_area_code,
hcp1.phone_number site_phone_number,
hcp1.phone_extension site_phone_extension,
xxen_util.meaning(hcp1.phone_line_type,'PHONE_LINE_TYPE',222) site_phone_type,
hcp2.email_address site_email_address,
xxen_util.meaning(hcp2.email_format,'EMAIL_FORMAT',222) site_email_format,
xxen_util.meaning(hcp2.contact_point_purpose,'CONTACT_POINT_PURPOSE',222) site_email_purpose,
hcp3.url site_url,
xxen_util.meaning(hcp3.contact_point_purpose,'CONTACT_POINT_PURPOSE_WEB',222) site_url_purpose,
-- party site dff
xxen_util.display_flexfield_context(222,'HZ_PARTY_SITES',hps.attribute_category) site_dff_context,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE1',hps.rowid,hps.attribute1) hz_party_site_attribute1,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE2',hps.rowid,hps.attribute2) hz_party_site_attribute2,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE3',hps.rowid,hps.attribute3) hz_party_site_attribute3,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE4',hps.rowid,hps.attribute4) hz_party_site_attribute4,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE5',hps.rowid,hps.attribute5) hz_party_site_attribute5,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE6',