Oracle Applications -- Uday. S: General

Showing posts with label General. Show all posts

Wednesday, June 25, 2014

Value set(DFF/KFF) Upload with Qualifier and child level data

DECLARE
l_enabled_flag VARCHAR2 (2);
l_summary_flag VARCHAR2 (2);
l_who_type fnd_flex_loader_apis.who_type;
l_user_id NUMBER := fnd_global.user_id;
l_login_id NUMBER := fnd_global.login_id;
l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
l_value_set_value fnd_flex_values.flex_value%TYPE;
l_flex_value_meaning VARCHAR2 (250);
l_description VARCHAR2 (250);
l_child_low VARCHAR2 (240);
l_child_high VARCHAR2 (240);
l_posting_allowed VARCHAR2 (1);
l_budget_allowed VARCHAR2 (1);
BEGIN
l_value_set_name := 'XXXXXXX';
l_value_set_value := '111';
l_flex_value_meaning := '111';
l_description := 'Test1';
l_child_low := '123';
l_child_high := '124';
l_enabled_flag := 'Y';
l_summary_flag := 'Y'; -- enables parent
l_budget_allowed := 'Y'; --QUALIFIER 1
l_posting_allowed := 'Y'; --QUALIFIER 2
l_who_type.created_by := l_user_id;
l_who_type.creation_date := SYSDATE;
l_who_type.last_updated_by := l_user_id;
l_who_type.last_update_date := SYSDATE;
l_who_type.last_update_login := l_login_id;

fnd_flex_loader_apis.
up_value_set_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL,
p_flex_value => l_value_set_value,
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
p_enabled_flag => l_enabled_flag,
p_summary_flag => l_summary_flag,
p_start_date_active => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
p_end_date_active => NULL,
p_parent_flex_value_high => NULL,
p_rollup_flex_value_set_name => NULL,
p_rollup_hierarchy_code => NULL,
p_hierarchy_level => NULL,
p_compiled_value_attributes => 'NN',
p_value_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_attribute31 => NULL,
p_attribute32 => NULL,
p_attribute33 => NULL,
p_attribute34 => NULL,
p_attribute35 => NULL,
p_attribute36 => NULL,
p_attribute37 => NULL,
p_attribute38 => NULL,
p_attribute39 => NULL,
p_attribute40 => NULL,
p_attribute41 => NULL,
p_attribute42 => NULL,
p_attribute43 => NULL,
p_attribute44 => NULL,
p_attribute45 => NULL,
p_attribute46 => NULL,
p_attribute47 => NULL,
p_attribute48 => NULL,
p_attribute49 => NULL,
p_attribute50 => NULL,
p_flex_value_meaning => l_flex_value_meaning,
p_description => l_description);

fnd_flex_loader_apis.
up_val_qual_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL, --
p_flex_value => l_value_set_value,
p_id_flex_application_short_na => 'SQLGL',
p_id_flex_code => 'GL#',
p_segment_attribute_type => 'GL_GLOBAL',
p_value_attribute_type => 'DETAIL_BUDGETING_ALLOWED',
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_assignment_order => NULL,
p_assignment_date => NULL,
p_compiled_value_attribute_val => l_budget_allowed);

fnd_flex_loader_apis.
up_val_qual_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL,
p_flex_value => l_value_set_value,
p_id_flex_application_short_na => 'SQLGL',
p_id_flex_code => 'GL#',
p_segment_attribute_type => 'GL_GLOBAL',
p_value_attribute_type => 'DETAIL_POSTING_ALLOWED',
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_assignment_order => NULL,
p_assignment_date => NULL,
p_compiled_value_attribute_val => l_posting_allowed);

IF l_summary_flag = 'Y'
THEN
fnd_flex_loader_apis.
up_val_norm_hierarchy (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value => l_value_set_value,
p_range_attribute => 'C',
p_child_flex_value_low => l_child_low,
p_child_flex_value_high => l_child_high,
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_start_date_active => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_end_date_active => NULL);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));
END;

Thursday, September 19, 2013

Registering a custom table in Oracle Apps.

CREATE TABLE emp_details

(

eno NUMBER (6) CONSTRAINT eno_pk PRIMARY KEY,

ename VARCHAR2 (15) NOT NULL,

dept VARCHAR2 (15),

jdate DATE,

salary NUMBER,

created_by NUMBER (15),

creation_date DATE,

last_updated_by NUMBER (15),

last_update_date DATE,

Last_update_login VARCHAR2 (50),

attribute1 VARCHAR2 (50),

attribute2 VARCHAR2 (50),

attribute3 VARCHAR2 (50),

attribute4 VARCHAR2 (50),

attribute5 VARCHAR2 (50),

attribute_category VARCHAR2 (100)

);

Grant all on emp_details to Apps;

COMMIT;

EXECUTE AD_DD.Register_Table('SQLGL','emp_details','T',8,10,90);

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','empno',1,'Number',6,'N','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','ename',2,'Varchar2',15,'N','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','dept',3,'Varchar2',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','jdate',4,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','salary',5,'Number',38,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','created_by',6,'Number',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','creation_date',7,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_updated_by',8,'Number',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_update_date',9,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute1',10,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute2',11,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute3',12,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute4',13,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute5',14,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute_category',15,'Varchar2',100,'Y','Y');

COMMIT;

SELECT * FROM emp_details;

for checking in front end

go to appication developer >> applications >> database >> TABLE

Friday, September 17, 2010

Oracle Patches

Tables: AD_APPLIED_PATCHES , AD_BUGS( bug number is the patch number)

Links:
Note: 181665.1 - Release 11i Adpatch Basics
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=181665.1

Note: 457566.1 - Oracle Applications Patching FAQ for Release 11i -- Can I determine ahead of time how a patch will affect my system?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=457566.1

Oracle Applications Maintenance Utilities -- 11i
http://download.oracle.com/docs/cd/B25516_18/current/acrobat/11iadutil.pdf

Oracle Applications Maintenance Utilities -- R12
http://download.oracle.com/docs/cd/B40089_09/current/acrobat/r12adutil.pdf

How can I track my customizations? What happens to my customizations during patching?

You should apply patches first on a test system. Then, review the changes in the test system and identify the best way to re-integrate customizations affected by the patch.

If you have registered your customized files in $APPL_TOP/admin/applcust.txt, AutoPatch reviews the files to determine if any of those files will be replaced during the application of the patch.

Friday, July 23, 2010

FND LOAD Scripts in Oracle

Forms Personalizations:
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt FND_FORM_CUSTOM_RULES function_name="OZF_FNDLVMLU"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt

Form Function:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt FUNCTION FUNCTION_NAME='((func name))'
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt

Lookups :
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ="XXDIS" LOOKUP_TYPE="((lkp name))"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt

Profile:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD @FND:patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt PROFILE PROFILE_NAME="((NAME_OF_profile))" APPLICATION_SHORT_NAME="CANON"

Concurrent Program:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt PROGRAM APPLICATION_SHORT_NAME="XXDIS" CONCURRENT_PROGRAM_NAME="((prg_name))"

Request Group:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt REQUEST_GROUP REQUEST_GROUP_NAME="System Administrator Reports" APPLICATION_SHORT_NAME="FND"

Flexi field:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'

Flexi field ....category/CONTEXT
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE="((FLEX_CONTEXT))"

Valuset :
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt VALUE_SET FLEX_VALUE_SET_NAME="CANON_E379_MERCH_DROPSHIP_WH_VS"
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt

Alert :
-------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt ALR_ALERTS APPLICATION_SHORT_NAME='WSH'
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt

Friday, March 5, 2010

Concatenating Multiple Rows into single row

SELECT customer_product_id,
SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(incident_number, '/') ,
'/',' ,')),3) Concatenated_String
FROM (SELECT cia.incident_number,cia.customer_product_id
,ROW_NUMBER () OVER (PARTITION BY customer_product_id ORDER BY customer_product_id) row#
FROM cs_incidents_all cia
,csi_item_instances cii
WHERE 1 = 1
AND cia.customer_product_id = cii.instance_id
AND cii.serial_number = 'XXXXXX'
AND NOT EXISTS (
SELECT 1
FROM cs_incident_statuses
WHERE NAME IN ('Cancelled', 'Closed')
AND incident_subtype = 'INC'
AND incident_status_id = cia.incident_status_id))
START
WITH ROW#=1
CONNECT
BY PRIOR row# = row#-1 and prior customer_product_id = customer_product_id
GROUP
BY customer_product_id

Wednesday, December 16, 2009

E-Mail using PLSQL

CREATE OR REPLACE PROCEDURE plsql_mail_test IS
c UTL_SMTP.connection;
v_sent_date VARCHAR2 (30);
v_dns_name VARCHAR2 (100);
v_sender VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_recepient VARCHAR2 (100) := 'xxxxx@xxxx.com';
v_subject VARCHAR2 (100) := 'TEST';
v_message VARCHAR2 (100) := NULL;

PROCEDURE send_header (
NAME IN VARCHAR2
,header IN VARCHAR2
) AS
BEGIN
UTL_SMTP.write_data (c, NAME
|| ': '
|| header
|| UTL_TCP.crlf);
END;

BEGIN

SELECT SYSDATE
INTO v_sent_date
FROM DUAL;

c := UTL_SMTP.open_connection ('xxx.xxx.xxx.xxx'); --server ip
UTL_SMTP.helo (c, 'XXXXXX'); --server name
UTL_SMTP.mail (c, v_sender);
UTL_SMTP.rcpt (c, v_recepient);
UTL_SMTP.rcpt (c, 'xxxxx@xxxx.com');
UTL_SMTP.open_data (c);
send_header ('From', v_sender);
send_header ('To', v_recepient);
send_header ('Subject', v_subject);
send_header ('Date', v_sent_date);
UTL_SMTP.write_data (c, UTL_TCP.crlf
|| v_message);
UTL_SMTP.close_data (c);
UTL_SMTP.quit (c);

EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
UTL_SMTP.quit (c);
raise_application_error (-20000
, 'Failed to send mail due to the following error: '
|| SQLERRM);

END plsql_mail_test;
/

Friday, December 11, 2009

Useful apps websites

www.erpschools.com

http://www.oracleappshub.com/

http://www.oracle.com/technology/documentation/applications.html

Oracle Applications -- Uday. S