Thursday, April 27, 2017

Oracle SQL to validate non-english characters


We had a requirement to validate non-english characters in the data.

Well, i used standard function regexp_like(column, '[A-Z]')   considering those characters which are not replaced by this function are non-english, to my surprise it is not considering turkish characters.

select REGEXP_replace(UPPER('rn Aıklaması'), '[A-Z]','')Test from dual ;











Alternate Solution for this problem is  using : asciistr Function

If input is equal to asciistr function, then it is non-english.

select count(*) from dual where  'ĞEĞİK' <> asciistr('ĞEĞİK') ;






Wednesday, April 26, 2017

Oracle E-Business Suite : Purchase Order Type Lookup Code


PO_HEADERS_ALL
The following table describes column information for the PO_HEADERS_ALL table.
Column NameNullTypeComments
PO_HEADER_IDNOT NULLNUMBERPrimary key
AGENT_IDNOT NULLNUMBERForeign key: HR_EMPLOYEES
TYPE_LOOKUP_CODENOT NULLVARCHAR2(25)Foreign Key: PO_LOOKUP_ CODES
LAST_UPDATE_DATENOT NULLDATEN/A
LAST_UPDATED_BYNOT NULLNUMBERN/A
SEGMENT1NOT NULLVARCHAR2(20)PO number
SUMMARY_FLAGNOT NULLVARCHAR2(1)N
ENABLED_FLAGNOT NULLVARCHAR2(1)Y
VENDOR_ID NUMBERForeign key: PO_VENDORS
VENDOR_SITE_ID NUMBERForeign key: PO_VENDOR_SITES
TERMS_ID NUMBERN/A
FREIGHT_TERMS_ LOOKUP_CODE VARCHAR2(25)Foreign key: PO_LOOKUP_ CODES
CURRENCY_CODE VARCHAR2(15)N/A
APPROVED_FLAG VARCHAR2(1)Y



select * from PO_LOOKUP_CODES where lookup_type='PO TYPE';