Hi All,
My company recently updated oracle version and now we need to update all our sql scrips.
Thing is i have an sql to find postal code format errors and now i get an error when running it.
The SQL says and dq_pattern is an invalid identifier.
below is apart of the SQL.
and dq_pattern(hl.POSTAL_CODE) not in ('ddddd')
Any help is appreciated
thank you
Rui
That error is happening because Oracle cannot find the DQ_PATTERN function.
Is it installed in the database? If not, install it.
It is part of a package? If so, reference it as package_name.dq_pattern.
Is it in another schema? If so, reference it as schema_name.dq_pattern or schema_name.package_name.dq_pattern.
To be honest im not a technical person below is the full sql is there away to rewrite the script and replace dq_pattern?
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'ddddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('ddddd')
and hl.country in ('HR','FR','FI','DE','IT','TR','UA','PK','BA','DZ','EE','EG','JO','LK','MA','MC','ME','MN','GR','NP')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'ud_duu, udd_duu, uud_duu, uudd_duu, udu_duu, uudu_duu' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('ud_duu','udd_duu','uud_duu','uudd_duu','udu_duu','uudu_duu')
and hl.country = 'GB'
and hps.PARTY_SITE_NUMBER not in ('59040365')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'dddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('dddd')
and hl.country in ('AM','AT','DK','HU','ZA','CH','BE','BD','BG','CY','GE','GW','LU','MK')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'ddd_dd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('ddd_dd')
and hl.country in ('CZ','SL','SE')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'dddddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('dddddd')
and hl.country in ('IN','RU','BY','KG','KZ')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'dddd_uu' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('dddd_uu')
and hl.country = 'NL'
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'dd-ddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('dd-ddd')
and hl.country = 'PL'
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'ddddddd,ddddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('ddddddd','ddddd')
and hl.country in ('IL')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'uu-dddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('uu-dddd')
and hl.country in ('MD')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'ddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('ddd')
and hl.country in ('MG','PG')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'uuu_dddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('uuu_dddd')
and hl.country in ('MT')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'uu-dddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('uu-dddd')
and hl.country in ('LV')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'uudddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('uudddd')
and hl.country in ('AZ','BN')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
SELECT ( select name
from hr_all_organization_units
where organization_id = hcasa.ORG_ID ) ou,
to_number (hca.ACCOUNT_NUMBER) account_number,
to_number (hp.PARTY_NUMBER) party_number,
to_number (hps.PARTY_SITE_NUMBER) party_site_number,
hp.PARTY_NAME,
hp.tax_reference,
hp.jgzz_fiscal_code,
hl.ADDRESS1,
hl.ADDRESS2,
hl.ADDRESS3,
hl.ADDRESS4,
hl.POSTAL_CODE,
dq_pattern(hl.POSTAL_CODE) current_postal_code_pattern,
'uu-ddddd' valid_postal_code_patterns,
hl.CITY,
hl.PROVINCE,
hl.COUNTY,
hl.COUNTRY,
hl.STATE,
hcasa.creation_date site_creation_date,
( select user_name
from fnd_user
where user_id = hcasa.created_by ) site_created_by,
hcasa.last_update_date site_last_update_date,
( select user_name
from fnd_user
where user_id = hcasa.last_updated_by ) site_last_updated_by
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hp.PARTY_TYPE = 'ORGANIZATION'
AND hp.PARTY_ID = hca.PARTY_ID
AND hca.CUST_ACCOUNT_ID = hcasa.CUST_ACCOUNT_ID
AND NOT ((hp.status = 'I') and (hca.status = 'I'))
AND hcasa.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hps.LOCATION_ID = hl.LOCATION_ID
AND hca.customer_type = 'R'
AND hp.status = 'A'
AND hca.status = 'A'
AND hps.status = 'A'
AND hcasa.status = 'A'
and dq_pattern(hl.POSTAL_CODE) not in ('uu-ddddd')
and hl.country in ('LT')
and hca.account_number not in ('6540888','1374335','57358','58915','3140874','3370379','1374370','57439')
union
select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
from dual
order by 18
Hi Rui,
Sorry, nobody here knows the code of the DQ_PATTERN function, so we can't really give you a rewrite for it. The solutions that I proposed are probably a lot simpler than rewriting your SQL.
Try this:
That should show you the function. You can then get the source for it, paste it into the editor in the database where it is missing, and run it with F9 to create it. Hopefully that will solve it, but it may be more complicated than that if the function has other dependencies.
-John
Hi John
No problem.
I appreciate you taking the time to respond.
I did the F4 and now i get the below.
I will check with my DBA team to see what they can do.
Wish you a great day!
CREATE OR REPLACE FUNCTION APPS.DQ_PATTERN ( input_string IN VARCHAR2)
RETURN varchar2 IS
cnt NUMBER:=1;
length_of_string NUMBER:=1;
return_string VARCHAR2(255):='';
pattern_character VARCHAR2(1):='?';
ascii_value NUMBER:=0;
BEGIN
/*** Data Quality Pattern Analyser from Data Quality Pro (http://www.dataqualitypro.com) /
/ Register for more free tools, tutorials and expert advice, all for free /
/ Just follow this URL: http://www.dataqualitypro.com/data-quality-pro-registration/ ***/
/*** Check if the input string has a value, if it doesn't, just return NULL ***/
IF input_string IS NULL THEN
RETURN NULL;
END IF;
length_of_string:=LENGTH(input_string);
IF length_of_string < 1 THEN
RETURN NULL;
END IF;
WHILE cnt <= length_of_string
/* Go through each character in turn, analysing its ascii pattern from our lookup table */
LOOP
ascii_value := ascii(substr(input_string,cnt,1));
IF ascii_value < 0 OR ascii_value > 255 THEN
pattern_character:= '?';
END IF;
IF ascii_value=0 THEN pattern_character:='z'; END IF;
IF ascii_value=1 THEN pattern_character:='z'; END IF;
IF ascii_value=2 THEN pattern_character:='?'; END IF;
IF ascii_value=3 THEN pattern_character:='?'; END IF;
IF ascii_value=4 THEN pattern_character:='?'; END IF;
IF ascii_value=5 THEN pattern_character:='?'; END IF;
IF ascii_value=6 THEN pattern_character:='?'; END IF;
IF ascii_value=7 THEN pattern_character:='?'; END IF;
IF ascii_value=8 THEN pattern_character:='?'; END IF;
IF ascii_value=9 THEN pattern_character:='t'; END IF;
IF ascii_value=10 THEN pattern_character:='c'; END IF;
IF ascii_value=11 THEN pattern_character:='?'; END IF;
IF ascii_value=12 THEN pattern_character:='?'; END IF;
IF ascii_value=13 THEN pattern_character:='c'; END IF;
IF ascii_value=14 THEN pattern_character:='?'; END IF;
IF ascii_value=15 THEN pattern_character:='?'; END IF;
IF ascii_value=16 THEN pattern_character:='?'; END IF;
IF ascii_value=17 THEN pattern_character:='?'; END IF;
IF ascii_value=18 THEN pattern_character:='?'; END IF;
IF ascii_value=19 THEN pattern_character:='?'; END IF;
IF ascii_value=20 THEN pattern_character:='?'; END IF;
IF ascii_value=21 THEN pattern_character:='?'; END IF;
IF ascii_value=22 THEN pattern_character:='?'; END IF;
IF ascii_value=23 THEN pattern_character:='?'; END IF;
IF ascii_value=24 THEN pattern_character:='?'; END IF;
IF ascii_value=25 THEN pattern_character:='?'; END IF;
IF ascii_value=26 THEN pattern_character:='?'; END IF;
IF ascii_value=27 THEN pattern_character:='?'; END IF;
IF ascii_value=28 THEN pattern_character:='?'; END IF;
IF ascii_value=29 THEN pattern_character:='?'; END IF;
IF ascii_value=30 THEN pattern_character:='?'; END IF;
IF ascii_value=31 THEN pattern_character:='?'; END IF;
IF ascii_value=32 THEN pattern_character:='_'; END IF;
IF ascii_value=33 THEN pattern_character:='p'; END IF;
IF ascii_value=34 THEN pattern_character:='q'; END IF;
IF ascii_value=35 THEN pattern_character:='p'; END IF;
IF ascii_value=36 THEN pattern_character:='m'; END IF;
IF ascii_value=37 THEN pattern_character:='p'; END IF;
IF ascii_value=38 THEN pattern_character:='p'; END IF;
IF ascii_value=39 THEN pattern_character:='q'; END IF;
IF ascii_value=40 THEN pattern_character:='p'; END IF;
IF ascii_value=41 THEN pattern_character:='p'; END IF;
IF ascii_value=42 THEN pattern_character:='p'; END IF;
IF ascii_value=43 THEN pattern_character:='+'; END IF;
IF ascii_value=44 THEN pattern_character:='S'; END IF;
IF ascii_value=45 THEN pattern_character:='-'; END IF;
IF ascii_value=46 THEN pattern_character:='S'; END IF;
IF ascii_value=47 THEN pattern_character:='S'; END IF;
IF ascii_value=48 THEN pattern_character:='d'; END IF;
IF ascii_value=49 THEN pattern_character:='d'; END IF;
IF ascii_value=50 THEN pattern_character:='d'; END IF;
IF ascii_value=51 THEN pattern_character:='d'; END IF;
IF ascii_value=52 THEN pattern_character:='d'; END IF;
IF ascii_value=53 THEN pattern_character:='d'; END IF;
IF ascii_value=54 THEN pattern_character:='d'; END IF;
IF ascii_value=55 THEN pattern_character:='d'; END IF;
IF ascii_value=56 THEN pattern_character:='d'; END IF;
IF ascii_value=57 THEN pattern_character:='d'; END IF;
IF ascii_value=58 THEN pattern_character:='S'; END IF;
IF ascii_value=59 THEN pattern_character:='S'; END IF;
IF ascii_value=60 THEN pattern_character:='p'; END IF;
IF ascii_value=61 THEN pattern_character:='p'; END IF;
IF ascii_value=62 THEN pattern_character:='p'; END IF;
IF ascii_value=63 THEN pattern_character:='p'; END IF;
IF ascii_value=64 THEN pattern_character:='p'; END IF;
IF ascii_value=65 THEN pattern_character:='u'; END IF;
IF ascii_value=66 THEN pattern_character:='u'; END IF;
IF ascii_value=67 THEN pattern_character:='u'; END IF;
IF ascii_value=68 THEN pattern_character:='u'; END IF;
IF ascii_value=69 THEN pattern_character:='u'; END IF;
IF ascii_value=70 THEN pattern_character:='u'; END IF;
IF ascii_value=71 THEN pattern_character:='u'; END IF;
IF ascii_value=72 THEN pattern_character:='u'; END IF;
IF ascii_value=73 THEN pattern_character:='u'; END IF;
IF ascii_value=74 THEN pattern_character:='u'; END IF;
IF ascii_value=75 THEN pattern_character:='u'; END IF;
IF ascii_value=76 THEN pattern_character:='u'; END IF;
IF ascii_value=77 THEN pattern_character:='u'; END IF;
IF ascii_value=78 THEN pattern_character:='u'; END IF;
IF ascii_value=79 THEN pattern_character:='u'; END IF;
IF ascii_value=80 THEN pattern_character:='u'; END IF;
IF ascii_value=81 THEN pattern_character:='u'; END IF;
IF ascii_value=82 THEN pattern_character:='u'; END IF;
IF ascii_value=83 THEN pattern_character:='u'; END IF;
IF ascii_value=84 THEN pattern_character:='u'; END IF;
IF ascii_value=85 THEN pattern_character:='u'; END IF;
IF ascii_value=86 THEN pattern_character:='u'; END IF;
IF ascii_value=87 THEN pattern_character:='u'; END IF;
IF ascii_value=88 THEN pattern_character:='u'; END IF;
IF ascii_value=89 THEN pattern_character:='u'; END IF;
IF ascii_value=90 THEN pattern_character:='u'; END IF;
IF ascii_value=91 THEN pattern_character:='p'; END IF;
IF ascii_value=92 THEN pattern_character:='S'; END IF;
IF ascii_value=93 THEN pattern_character:='p'; END IF;
IF ascii_value=94 THEN pattern_character:='p'; END IF;
IF ascii_value=95 THEN pattern_character:='S'; END IF;
IF ascii_value=96 THEN pattern_character:='q'; END IF;
IF ascii_value=97 THEN pattern_character:='l'; END IF;
IF ascii_value=98 THEN pattern_character:='l'; END IF;
IF ascii_value=99 THEN pattern_character:='l'; END IF;
IF ascii_value=100 THEN pattern_character:='l'; END IF;
IF ascii_value=101 THEN pattern_character:='l'; END IF;
IF ascii_value=102 THEN pattern_character:='l'; END IF;
IF ascii_value=103 THEN pattern_character:='l'; END IF;
IF ascii_value=104 THEN pattern_character:='l'; END IF;
IF ascii_value=105 THEN pattern_character:='l'; END IF;
IF ascii_value=106 THEN pattern_character:='l'; END IF;
IF ascii_value=107 THEN pattern_character:='l'; END IF;
IF ascii_value=108 THEN pattern_character:='l'; END IF;
IF ascii_value=109 THEN pattern_character:='l'; END IF;
IF ascii_value=110 THEN pattern_character:='l'; END IF;
IF ascii_value=111 THEN pattern_character:='l'; END IF;
IF ascii_value=112 THEN pattern_character:='l'; END IF;
IF ascii_value=113 THEN pattern_character:='l'; END IF;
IF ascii_value=114 THEN pattern_character:='l'; END IF;
IF ascii_value=115 THEN pattern_character:='l'; END IF;
IF ascii_value=116 THEN pattern_character:='l'; END IF;
IF ascii_value=117 THEN pattern_character:='l'; END IF;
IF ascii_value=118 THEN pattern_character:='l'; END IF;
IF ascii_value=119 THEN pattern_character:='l'; END IF;
IF ascii_value=120 THEN pattern_character:='l'; END IF;
IF ascii_value=121 THEN pattern_character:='l'; END IF;
IF ascii_value=122 THEN pattern_character:='l'; END IF;
IF ascii_value=123 THEN pattern_character:='p'; END IF;
IF ascii_value=124 THEN pattern_character:='S'; END IF;
IF ascii_value=125 THEN pattern_character:='p'; END IF;
IF ascii_value=126 THEN pattern_character:='p'; END IF;
IF ascii_value=127 THEN pattern_character:='?'; END IF;
IF ascii_value=128 THEN pattern_character:='!'; END IF;
IF ascii_value=129 THEN pattern_character:='!'; END IF;
IF ascii_value=130 THEN pattern_character:='!'; END IF;
IF ascii_value=131 THEN pattern_character:='!'; END IF;
IF ascii_value=132 THEN pattern_character:='!'; END IF;
IF ascii_value=133 THEN pattern_character:='!'; END IF;
IF ascii_value=134 THEN pattern_character:='!'; END IF;
IF ascii_value=135 THEN pattern_character:='!'; END IF;
IF ascii_value=136 THEN pattern_character:='!'; END IF;
IF ascii_value=137 THEN pattern_character:='!'; END IF;
IF ascii_value=138 THEN pattern_character:='!'; END IF;
IF ascii_value=139 THEN pattern_character:='!'; END IF;
IF ascii_value=140 THEN pattern_character:='!'; END IF;
IF ascii_value=141 THEN pattern_character:='!'; END IF;
IF ascii_value=142 THEN pattern_character:='!'; END IF;
IF ascii_value=143 THEN pattern_character:='!'; END IF;
IF ascii_value=144 THEN pattern_character:='!'; END IF;
IF ascii_value=145 THEN pattern_character:='!'; END IF;
IF ascii_value=146 THEN pattern_character:='!'; END IF;
IF ascii_value=147 THEN pattern_character:='!'; END IF;
IF ascii_value=148 THEN pattern_character:='!'; END IF;
IF ascii_value=149 THEN pattern_character:='!'; END IF;
IF ascii_value=150 THEN pattern_character:='!'; END IF;
IF ascii_value=151 THEN pattern_character:='!'; END IF;
IF ascii_value=152 THEN pattern_character:='!'; END IF;
IF ascii_value=153 THEN pattern_character:='!'; END IF;
IF ascii_value=154 THEN pattern_character:='!'; END IF;
IF ascii_value=155 THEN pattern_character:='!'; END IF;
IF ascii_value=156 THEN pattern_character:='!'; END IF;
IF ascii_value=157 THEN pattern_character:='!'; END IF;
IF ascii_value=158 THEN pattern_character:='!'; END IF;
IF ascii_value=159 THEN pattern_character:='!'; END IF;
IF ascii_value=160 THEN pattern_character:='!'; END IF;
IF ascii_value=161 THEN pattern_character:='!'; END IF;
IF ascii_value=162 THEN pattern_character:='!'; END IF;
IF ascii_value=163 THEN pattern_character:='m'; END IF;
IF ascii_value=164 THEN pattern_character:='!'; END IF;
IF ascii_value=165 THEN pattern_character:='!'; END IF;
IF ascii_value=166 THEN pattern_character:='!'; END IF;
IF ascii_value=167 THEN pattern_character:='!'; END IF;
IF ascii_value=168 THEN pattern_character:='!'; END IF;
IF ascii_value=169 THEN pattern_character:='!'; END IF;
IF ascii_value=170 THEN pattern_character:='!'; END IF;
IF ascii_value=171 THEN pattern_character:='!'; END IF;
IF ascii_value=172 THEN pattern_character:='!'; END IF;
IF ascii_value=173 THEN pattern_character:='!'; END IF;
IF ascii_value=174 THEN pattern_character:='!'; END IF;
IF ascii_value=175 THEN pattern_character:='!'; END IF;
IF ascii_value=176 THEN pattern_character:='!'; END IF;
IF ascii_value=177 THEN pattern_character:='!'; END IF;
IF ascii_value=178 THEN pattern_character:='!'; END IF;
IF ascii_value=179 THEN pattern_character:='!'; END IF;
IF ascii_value=180 THEN pattern_character:='!'; END IF;
IF ascii_value=181 THEN pattern_character:='!'; END IF;
IF ascii_value=182 THEN pattern_character:='!'; END IF;
IF ascii_value=183 THEN pattern_character:='!'; END IF;
IF ascii_value=184 THEN pattern_character:='!'; END IF;
IF ascii_value=185 THEN pattern_character:='!'; END IF;
IF ascii_value=186 THEN pattern_character:='!'; END IF;
IF ascii_value=187 THEN pattern_character:='!'; END IF;
IF ascii_value=188 THEN pattern_character:='!'; END IF;
IF ascii_value=189 THEN pattern_character:='!'; END IF;
IF ascii_value=190 THEN pattern_character:='!'; END IF;
IF ascii_value=191 THEN pattern_character:='!'; END IF;
IF ascii_value=192 THEN pattern_character:='!'; END IF;
IF ascii_value=193 THEN pattern_character:='!'; END IF;
IF ascii_value=194 THEN pattern_character:='!'; END IF;
IF ascii_value=195 THEN pattern_character:='!'; END IF;
IF ascii_value=196 THEN pattern_character:='!'; END IF;
IF ascii_value=197 THEN pattern_character:='!'; END IF;
IF ascii_value=198 THEN pattern_character:='!'; END IF;
IF ascii_value=199 THEN pattern_character:='!'; END IF;
IF ascii_value=200 THEN pattern_character:='!'; END IF;
IF ascii_value=201 THEN pattern_character:='!'; END IF;
IF ascii_value=202 THEN pattern_character:='!'; END IF;
IF ascii_value=203 THEN pattern_character:='!'; END IF;
IF ascii_value=204 THEN pattern_character:='!'; END IF;
IF ascii_value=205 THEN pattern_character:='!'; END IF;
IF ascii_value=206 THEN pattern_character:='!'; END IF;
IF ascii_value=207 THEN pattern_character:='!'; END IF;
IF ascii_value=208 THEN pattern_character:='!'; END IF;
IF ascii_value=209 THEN pattern_character:='!'; END IF;
IF ascii_value=210 THEN pattern_character:='!'; END IF;
IF ascii_value=211 THEN pattern_character:='!'; END IF;
IF ascii_value=212 THEN pattern_character:='!'; END IF;
IF ascii_value=213 THEN pattern_character:='!'; END IF;
IF ascii_value=214 THEN pattern_character:='!'; END IF;
IF ascii_value=215 THEN pattern_character:='!'; END IF;
IF ascii_value=216 THEN pattern_character:='!'; END IF;
IF ascii_value=217 THEN pattern_character:='!'; END IF;
IF ascii_value=218 THEN pattern_character:='!'; END IF;
IF ascii_value=219 THEN pattern_character:='!'; END IF;
IF ascii_value=220 THEN pattern_character:='!'; END IF;
IF ascii_value=221 THEN pattern_character:='!'; END IF;
IF ascii_value=222 THEN pattern_character:='!'; END IF;
IF ascii_value=223 THEN pattern_character:='!'; END IF;
IF ascii_value=224 THEN pattern_character:='!'; END IF;
IF ascii_value=225 THEN pattern_character:='!'; END IF;
IF ascii_value=226 THEN pattern_character:='!'; END IF;
IF ascii_value=227 THEN pattern_character:='!'; END IF;
IF ascii_value=228 THEN pattern_character:='!'; END IF;
IF ascii_value=229 THEN pattern_character:='!'; END IF;
IF ascii_value=230 THEN pattern_character:='!'; END IF;
IF ascii_value=231 THEN pattern_character:='!'; END IF;
IF ascii_value=232 THEN pattern_character:='!'; END IF;
IF ascii_value=233 THEN pattern_character:='!'; END IF;
IF ascii_value=234 THEN pattern_character:='!'; END IF;
IF ascii_value=235 THEN pattern_character:='!'; END IF;
IF ascii_value=236 THEN pattern_character:='!'; END IF;
IF ascii_value=237 THEN pattern_character:='!'; END IF;
IF ascii_value=238 THEN pattern_character:='!'; END IF;
IF ascii_value=239 THEN pattern_character:='!'; END IF;
IF ascii_value=240 THEN pattern_character:='!'; END IF;
IF ascii_value=241 THEN pattern_character:='!'; END IF;
IF ascii_value=242 THEN pattern_character:='!'; END IF;
IF ascii_value=243 THEN pattern_character:='!'; END IF;
IF ascii_value=244 THEN pattern_character:='!'; END IF;
IF ascii_value=245 THEN pattern_character:='!'; END IF;
IF ascii_value=246 THEN pattern_character:='!'; END IF;
IF ascii_value=247 THEN pattern_character:='!'; END IF;
IF ascii_value=248 THEN pattern_character:='!'; END IF;
IF ascii_value=249 THEN pattern_character:='!'; END IF;
IF ascii_value=250 THEN pattern_character:='!'; END IF;
IF ascii_value=251 THEN pattern_character:='!'; END IF;
IF ascii_value=252 THEN pattern_character:='!'; END IF;
IF ascii_value=253 THEN pattern_character:='!'; END IF;
IF ascii_value=254 THEN pattern_character:='!'; END IF;
IF ascii_value=255 THEN pattern_character:='!'; END IF;
return_string:=CONCAT(return_string,pattern_character);
cnt:=cnt+1;
END LOOP;
RETURN return_string;
END;
/
Hi John
Adding APPS. in front worked
THANKS Again for your help.