Exception handling for Bulk Insert and Bulk update using FORALL

DECLARE
TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
-- SAVE EXCEPTIONS means don't stop if some DELETEs fail.
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp2 WHERE salary > 500000/num_tab(i);
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of DELETE statements that failed: ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

FNDLOAD for Profile

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct filename.ldt PROFILE PROFILE_NAME="profile name" APPLICATION_SHORT_NAME="XXDIS"

UPLOAD
=======
FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct filename.ldt

FNDLOAD script for value set with values

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt VALUE_SET FLEX_VALUE_SET_NAME="valueset name"

UPLOAD
======
FNDLOAD apps/appsdev 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt

PLSQL block with bulk collect and limit

DECLARE
CURSOR get_item_det_cur (
p_in_num_from_itemid IN NUMBER,
p_in_num_to_itemid IN NUMBER
)
IS
SELECT msib.inventory_item_id, msib.organization_id
FROM mtl_system_items_b msib
WHERE msib.segment2 = msib.attribute5
AND msib.organization_id = 81
AND msib.inventory_item_id BETWEEN p_in_num_from_itemid
AND p_in_num_to_itemid;

TYPE item_det_tbl IS TABLE OF get_item_det_cur%ROWTYPE
INDEX BY BINARY_INTEGER;

l_item_det_tbl item_det_tbl;
l_item_id NUMBER;
l_org_id NUMBER;
l_cnt NUMBER := 0;
BEGIN
OPEN get_item_det_cur (1174281, 1174500);

LOOP
l_cnt := l_cnt + 1;
DBMS_OUTPUT.put_line ('l_cnt==>' || l_cnt);

FETCH get_item_det_cur
BULK COLLECT INTO l_item_det_tbl LIMIT 10;

FOR idx IN l_item_det_tbl.FIRST .. l_item_det_tbl.LAST
LOOP
l_item_id := l_item_det_tbl (idx).inventory_item_id;
l_org_id := l_item_det_tbl (idx).organization_id;
DBMS_OUTPUT.put_line ('l_item_id==>' || l_item_id);
DBMS_OUTPUT.put_line ('l_org_id==>' || l_org_id);
END LOOP;
END LOOP;

CLOSE get_item_det_cur;
END;

SQL to find which query is running while in the concurrent request

SELECT vst.sql_text, SYSDATE, fcr.request_id, fcp.concurrent_program_name,
fcr.oracle_process_id traceid, fcr.requested_start_date,
fcr.actual_start_date, fcr.actual_completion_date,
fcr.status_code status,
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE)
- fcr.actual_start_date
)
* 24
* 60,
2
) run_time,
vst.sql_text, vs.SID, vs.serial#, vs.status, fcr.parent_request_id,
fcr.os_process_id
FROM v$sqlarea vst,
v$session vs,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_requests fcr
WHERE vst.address(+) = vs.sql_address
AND vs.process(+) = fcr.os_process_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = --Request ID--;

Concurrent Program Specific SQL Queries

To fetch responsibility assigned to a concurrent program
===========================================================
select resp.responsibility_name
from fnd_responsibility_vl resp, fnd_request_group_units rgu, fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = ;


To fetch parameters assigned to a concurrent program
=====================================================

select cols.end_user_column_name,
cols.form_left_prompt,
fvs.flex_value_set_name,
cols.default_value
from fnd_descr_flex_col_usage_vl cols,
fnd_flex_value_sets fvs,
fnd_concurrent_programs_vl prog
where cols.flex_value_set_id = fvs.flex_value_set_id
AND cols.descriptive_flexfield_name = '$SRS$.' || prog.concurrent_program_name
And prog.user_concurrent_program_name like ;


To fetch details of executables and concurrent programs
===========================================================

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
exe.description "executable description",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where prog.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND prog.user_concurrent_program_name =
AND prog.concurrent_program_name =
AND exe.execution_file_name =

Find a concurrent program's trace file

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

How to make synonym status from invalid to valid

ALTER SYNONYM <> COMPILE;

Address Formatting using HZ_LOCATIONS.LOCATION_ID

HZ_FORMAT_PUB.format_address (
-- input parameters
p_location_id IN NUMBER,
p_style_code IN VARCHAR2,
p_style_format_code IN VARCHAR2,
p_line_break IN VARCHAR2,
p_space_replace IN VARCHAR2,
-- optional context parameters
p_to_language_code IN VARCHAR2,
p_country_name_lang IN VARCHAR2,
p_from_territory_code IN VARCHAR2,
-- output parameters
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_formatted_address OUT NOCOPY VARCHAR2,
x_formatted_lines_cnt OUT NOCOPY NUMBER,
x_formatted_address_tbl OUT NOCOPY string_tbl_type
);
Pass LOCATION_ID to this procedure and it returns formatted address through OUT parameter X_FORMATTED_ADDRESS. You may also pass line break character CHR(10) so that different address lines are returned with carriage return at appropriate places.

To activate audit trail in Oracle for standard tables

DECLARE l_app_id NUMBER; l_fnd_audit_groups NUMBER; l_table_id1 NUMBER; l_table_id2 NUMBER; l_col_ven_id NUMBER; l_col_inv_amt_lmt NUMBER; l_col_ven_nm NUMBER; l_col_bnk_uses_id NUMBER; l_col_bnk_id NUMBER; l_col_bnk_vndr NUMBER; l_col_bnk_site NUMBER; l_col_bnk_org NUMBER; l_schema_id NUMBER; l_oracle_id NUMBER; l_cnt_id NUMBER; l_audit_group_app_id NUMBER; l_col_ven_id1 NUMBER; l_col_ven_id2 NUMBER; l_col_ven_id3 NUMBER; l_num_request_id NUMBER; l_num_user_id NUMBER; l_num_appl_id NUMBER; l_num_resp_id NUMBER; BEGIN/*Enable audit for PO user updating FND_AUDIT_SCHEMAS table for PO username. */
SELECT DECODE(install_group_num, 0, 900, 899 + NVL(install_group_num,1) ) INTO l_schema_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT oracle_id INTO l_oracle_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT application_id INTO l_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT application_id INTO l_audit_group_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT COUNT(oracle_id) INTO l_cnt_id FROM fnd_audit_schemas WHERE oracle_id = l_oracle_id;
IF l_cnt_id <> 0 THENUPDATE fnd_audit_schemas SET state = 'R',last_update_date = SYSDATE,last_updated_by = -1,last_update_login = -1WHERE oracle_id = l_oracle_id ;
DBMS_OUTPUT.PUT_LINE('No Of Records Updated in fnd_audit_schemas : ' SQL%ROWCOUNT ); ELSE INSERT INTO fnd_audit_schemas(schema_id,oracle_id,application_id, state,last_update_date,last_updated_by,last_update_login,created_by,creation_date) VALUES (l_schema_id,l_oracle_id,l_app_id,'R',SYSDATE,-1,-1,-1,SYSDATE);DBMS_OUTPUT.PUT_LINE('No Of Records Inserted in fnd_audit_schemas : ' SQL%ROWCOUNT ); END IF; SELECT audit_group_id INTO l_fnd_audit_groups FROM fnd_audit_groups WHERE group_name = 'Test Audit';
UPDATE fnd_audit_groups set STATE = 'R' where group_name = 'Test Audit';
/*Getting table_id for PO_VENDOR_SITES_ALL table*/ SELECT table_id INTO l_table_id1 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_app_id;
SELECT table_id INTO l_table_id2 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_audit_group_app_id;
/*Insert records into FND_AUDIT_TABLES table*/ INSERT INTO fnd_audit_tables(audit_group_id, audit_group_app_id, table_app_id, table_id, state, last_update_date, last_updated_by, last_update_login, created_by, creation_date) VALUES (l_fnd_audit_groups, l_audit_group_app_id, l_app_id, l_table_id1, 'R', SYSDATE, -1, -1, -1, SYSDATE); DBMS_OUTPUT.put_line ('No of records Inserted into FND_AUDIT_TABLES table : ' SQL%ROWCOUNT); /*Selecting column ID for columns */
SELECT column_id INTO l_col_ven_id3 FROM fnd_columns WHERE column_name = 'VENDOR_SITE_ID' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id1 FROM fnd_columns WHERE column_name = 'PAYMENT_METHOD_LOOKUP_CODE' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id2 FROM fnd_columns WHERE column_name = 'PRIMARY_FLAG' AND application_id = l_audit_group_app_id AND table_id = l_table_id2 ;
/*Insert records into FND_AUDIT_COLUMNS table for AP_BANK_ACCOUNTS_ALL*/
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_app_id, l_table_id1, l_col_ven_id3, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_app_id, l_table_id1, l_col_ven_id1, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_audit_group_app_id, l_table_id2, l_col_ven_id2, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
BEGINSELECT application_id,responsibility_idINTO l_num_appl_id,l_num_resp_idFROM fnd_responsibility_vlWHERE responsibility_name = 'System Administrator';
EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the Responsibility details :' SQLERRM);END;
BEGINSELECT user_id INTO l_num_user_idFROM fnd_userWHERE user_name = 'FIN_ADMIN';EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the user details :' SQLERRM);END;
fnd_global.apps_initialize(user_id => l_num_user_id,resp_id => l_num_resp_id,resp_appl_id => l_num_appl_id,security_group_id => 0);
l_num_request_id := Fnd_Request.submit_request (application => 'FND',program => 'FNDATUPD');
COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLCODE '-' SQLERRM); ROLLBACK;END;/

Kill a database session for a particular object

selectc.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine

fromv$locked_object a ,v$session b,dba_objects c

whereb.sid = a.session_id

anda.object_id = c.object_id;
ALTER SYSTEM KILL SESSION ',';

LDT generation for Business EVENTS and SUBSCRIPTIONS

Download
adjava oracle.apps.fnd.wf.WFXLoad -d username password host_of_database:port:sid thin US .wfx EVENT/SUBSCRIPTIONS
Upload
adjava oracle.apps.fnd.wf.WFXLoad -u username password host_of_database:port:sid thin US .wfx EVENT/SUBSCRIPTIONS

Calling a webservice from forms

http://www.oracle.com/technology/obe/obe_as_10g/deploy/callws_fromforms/forms_webservice.htm

OAF links

http://www.tier1inc.com/blog.php