Saturday, 18 November 2017

FNDLOAD Commands: Alerts

Menu -> Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

FNDLOAD Commands: Value sets / Value Set with Values

Menu -> Value Set

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Value Set with Values

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Messages

Menu -> FND Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Command: Profiles

Menu -> Profiles

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt -WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands for AOL Objects

FNDLOAD Command: Lookups

Menu -> Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Sunday, 29 October 2017

Query to convert Column data into a Row

The following query is very useful for converting the data in a single column into a row so the all the values can be extracted from a table into variables of different name. Using cursor it is difficult to get all the values from single column into variables of different name. 

Example of sample data available - 



Required format into which data needs to be converted -




Query which can be used for above purpose - 


SELECT  MAX(DECODE(level,1,regexp_substr(str,'[^,]+',1,level))) AS val1 ,
 MAX(DECODE(level,2,regexp_substr(str,'[^,]+',1,level))) AS val2 ,
 MAX(DECODE(level,3,regexp_substr(str,'[^,]+',1,level))) AS val3 ,
 MAX(DECODE(Level,4,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val4 ,
 MAX(DECODE(level,5,regexp_substr(str,'[^,]+',1,level))) AS val5 ,
 MAX(DECODE(level,6,regexp_substr(str,'[^,]+',1,level))) AS val6 ,
 MAX(DECODE(level,7,regexp_substr(str,'[^,]+',1,level))) AS val7 ,
 MAX(DECODE(level,8,regexp_substr(str,'[^,]+',1,level))) AS val8 ,
 MAX(DECODE(Level,9,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val9 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val10 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val11 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val12 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val13 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val14 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val15
   INTO l_value_id1 ,
 l_value_id2 ,
 l_value_id3 ,
 l_value_id4 ,
 l_value_id5 ,
 l_value_id6 ,
 l_value_id7 ,
 l_value_id8 ,
 l_value_id9 ,
 l_value_id10 ,
 l_value_id11 ,
 l_value_id12 ,
 l_value_id13 ,
 l_Value_Id14 ,
 l_value_id15
   FROM (
         SELECT Listagg (column_to_convert,',') Within Group (Order By column_to_convert) Str
    FROM Table_Name
   WHERE 1 = 1
   GROUP BY group_by_column
   ) Tmp

   CONNECT BY regexp_substr(str,'[^,]+',1,level) IS NOT NULL;