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