Monday, September 11, 2017

Oracle E-Business Suite 12.2.7 - Released

E-Business Suite Development is pleased to announce the availability of Oracle E-Business Suite 12.2.7. This latest release, which continues a pattern of ongoing functional and technical innovation, is organized around these key investment drivers:


http://www.oracle.com/us/products/applications/ebs-ga-2017-09-08-3876665.pdf

Thursday, August 31, 2017

Custom Lot Number Generation - Oracle E-Business Inventory


To do this:
1. Select the lot number generation option as "‘User-Defined"
2. Write the code to generate the lot number as per the business need in the procedure
    generate_lot_number
3. This procedure is found in the file "‘INVUDLGS.pls"

4. Package Name : user_pkg_lot , method name : generate_lot_number

Friday, August 25, 2017

Value Sets - Special Value Sets -


One of the frequent issues faced mostly in SQL/PLSQL is based on Date Formats.

Following is one of the code snippet to validate From Date/ To Date  with users using Different Date Formats.


FND PLSQL " DECLARE

l_char  varchar2(25) := :!value ;
l_count NUMBER := 0;

   l_value   DATE :=  TO_DATE(l_char,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'NLS_DATE_LANGUAGE = AMERICAN') ;
 
  BEGIN
  SELECT trunc(l_value - to_date(':$FLEX$.XX_FROM_DATE','YYYY/MM/DD HH24:MI:SS'))
  INTO l_count
  FROM dual;

  IF l_count  >  365

   THEN
fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE') ;
fnd_message.set_token( 'MESSAGE', 'Date Difference Should Not Be Greater Than 365 Days' );
fnd_message.raise_error ;
   ELSIF  l_count <1    THEN
     fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE') ;
     fnd_message.set_token( 'MESSAGE', 'To Date Should Be Greater Than From Date');
     fnd_message.raise_error ;
   END IF;

END;"

Thursday, July 20, 2017

Oracle ERP- Supplier Audit - Important Points



After Supplier Is Updated, Alert On AP_SUPPLIERS Table Is Triggered Twice (Doc ID 1969553.1)

As part of enabling Oracle Alert functionality, the following trigger is created on the AP_SUPPLIERS table:

create trigger TEST_SUPP_UPD_ALERT_AU AFTER UPDATE on AP.AP_SUPPLIERS for each row
begin
insert into XXTEST_SUPP_ALERT_UPD values (:new.VENDOR_ID, :new.LAST_UPDATE_DATE);
end;

When supplier data is updated through the Supplier Master pages, multiple rows are inserted into the table XXTEST_SUPP_ALERT_UPD. The expectation is that only one row would be inserted.

Please explain / provide fix.

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';