Oracle Apps Technical Stuff: March 2013

Saturday, March 30, 2013

SQL to Delete Duplicate Rows in a Table

DELETE FROM xx_table 
 WHERE ROWID NOT IN (SELECT MIN(ROWID) 
                       FROM xx_table 
                      GROUP BY col1, col2....);

Convert LONG datatypes to LOB's or Query on the Long Data type Columns

CREATE TABLE XX_LOG_STG(C1 NUMBER, C2 LONG)
/
INSERT INTO XX_LOG_STG VALUES (1, 'LONG data to convert to CLOB')
/
-- Insert LONG into LOB column
CREATE TABLE XX_LOB_STG(C1 NUMBER, C2 CLOB);

-- Use TO_LOB function to convert LONG to LOB...
INSERT INTO XX_LOB_STG
SELECT C1, TO_LOB(C2) FROM XX_LOG_STG;

CREATE TABLE XX_DBA_TRIGGERS AS SELECT TRIGGER_NAME, TO_LOB(TRIGGER_BODY) AS TRIGGER_BODY

FROM DBA_TRIGGERS ;

-- Query on the Long Data type Column i.e. Trigger Body as below 

SELECT * FROM XX_DBA_TRIGGERS WHERE UPPER(TRIGGER_BODY) LIKE '%FND_REQUEST%' ;

Tuesday, March 26, 2013

Working with Nested Objects

DECLARE
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_party_merge_obj hz_party_merge_obj;
l_stage NUMBER;
BEGIN
l_stage := 10;
hz_extract_merge_event_pkg.get_party_merge_event_data (p_init_msg_list => 'T'
, p_batch_id => p_merge_batch_id
, p_merge_to_party_id => p_merge_to_party_id
, p_get_merge_detail_flag => 'Y'
, x_party_merge_obj => l_party_merge_obj
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
l_stage := 20;
DBMS_OUTPUT.put_line ('Table Type Count : ' || l_party_merge_obj.merge_detail_objs.COUNT);

IF l_return_status = fnd_api.g_ret_sts_success
THEN
l_stage := 30;

IF l_party_merge_obj.merge_from_party_objs.COUNT > 0
THEN
FOR j IN l_party_merge_obj.merge_from_party_objs.FIRST .. l_party_merge_obj.merge_from_party_objs.LAST
LOOP
IF l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs.COUNT > 0
THEN
FOR k IN
l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs.FIRST .. l_party_merge_obj.merge_from_party_objs
(j).orig_sys_objs.LAST
LOOP
INSERT INTO xxcdh_orig_sys_ref_obj_tbl
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_from_party_objs (j).party_id
, l_party_merge_obj.merge_from_party_objs (j).party_number
, l_party_merge_obj.merge_from_party_objs (j).party_name
, l_party_merge_obj.merge_from_party_objs (j).party_type
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).action_type
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system_ref_id
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).orig_system_reference
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).object_type
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).object_id
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).status
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).reason_code
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).old_orig_system_reference
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).start_date_active
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).end_date_active
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute_category
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute1
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute2
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute3
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute4
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute5
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute6
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute7
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute8
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute9
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute10
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute11
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute12
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute13
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute14
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute15
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute16
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute17
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute18
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute19
, l_party_merge_obj.merge_from_party_objs (j).orig_sys_objs (k).attribute20
);
END LOOP;
END IF;
END LOOP;
END IF;

IF l_party_merge_obj.merge_to_party_obj.orig_sys_objs.COUNT > 0
THEN
FOR i IN
l_party_merge_obj.merge_to_party_obj.orig_sys_objs.FIRST .. l_party_merge_obj.merge_to_party_obj.orig_sys_objs.LAST
LOOP
INSERT INTO xxcdh_party_orig_sys_ref_obj
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_to_party_obj.party_id
, l_party_merge_obj.merge_to_party_obj.party_number
, l_party_merge_obj.merge_to_party_obj.party_name
, l_party_merge_obj.merge_to_party_obj.party_type
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).action_type
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system_ref_id
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).orig_system_reference
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).object_type
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).object_id
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).status
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).reason_code
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).old_orig_system_reference
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).start_date_active
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).end_date_active
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute_category
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute1
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute2
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute3
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute4
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute5
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute6
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute7
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute8
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute9
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute10
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute11
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute12
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute13
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute14
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute15
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute16
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute17
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute18
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute19
, l_party_merge_obj.merge_to_party_obj.orig_sys_objs (i).attribute20
);
END LOOP;
END IF;

IF l_party_merge_obj.merge_detail_objs.COUNT > 0
THEN
FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
LOOP
IF l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs.COUNT > 0
THEN
FOR m IN
l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
(l).from_object_sys_ref_objs.LAST
LOOP
INSERT INTO xxcdh_party_merge_detail_obj1
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_detail_objs (l).object_type
, l_party_merge_obj.merge_detail_objs (l).operation_type
, l_party_merge_obj.merge_detail_objs (l).from_object_id
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).action_type
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system_ref_id
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).object_type
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).status
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).reason_code
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).old_orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).start_date_active
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).end_date_active
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute_category
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute1
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute2
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute3
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute4
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute5
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute6
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute7
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute8
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute9
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute10
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute11
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute12
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute13
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute14
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute15
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute16
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute17
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute18
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute19
, l_party_merge_obj.merge_detail_objs (l).from_object_sys_ref_objs (m).attribute20
);
END LOOP;
END IF;
END LOOP;
END IF;

IF l_party_merge_obj.merge_detail_objs.COUNT > 0
THEN
FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
LOOP
IF l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs.COUNT > 0
THEN
FOR n IN
l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
(l).to_object_sys_ref_objs.LAST
LOOP
INSERT INTO xxcdh_party_merge_detail_obj2
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_detail_objs (l).object_type
, l_party_merge_obj.merge_detail_objs (l).operation_type
, l_party_merge_obj.merge_detail_objs (l).to_object_id
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).action_type
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system_ref_id
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).object_type
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).status
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).reason_code
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).old_orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).start_date_active
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).end_date_active
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute_category
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute1
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute2
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute3
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute4
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute5
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute6
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute7
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute8
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute9
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute10
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute11
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute12
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute13
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute14
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute15
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute16
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute17
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute18
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute19
, l_party_merge_obj.merge_detail_objs (l).to_object_sys_ref_objs (n).attribute20
);
END LOOP;
END IF;
END LOOP;
END IF;

IF l_party_merge_obj.merge_detail_objs.COUNT > 0
THEN
FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
LOOP
IF l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs.COUNT > 0
THEN
FOR o IN
l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
(l).from_parent_obj_sys_ref_objs.LAST
LOOP
INSERT INTO xxcdh_party_merge_detail_obj3
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_detail_objs (l).object_type
, l_party_merge_obj.merge_detail_objs (l).operation_type
, l_party_merge_obj.merge_detail_objs (l).from_parent_object_id
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).action_type
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system_ref_id
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).object_type
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).status
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).reason_code
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).old_orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).start_date_active
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).end_date_active
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute_category
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute1
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute2
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute3
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute4
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute5
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute6
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute7
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute8
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute9
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute10
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute11
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute12
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute13
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute14
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute15
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute16
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute17
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute18
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute19
, l_party_merge_obj.merge_detail_objs (l).from_parent_obj_sys_ref_objs (o).attribute20
);
END LOOP;
END IF;
END LOOP;
END IF;

IF l_party_merge_obj.merge_detail_objs.COUNT > 0
THEN
FOR l IN l_party_merge_obj.merge_detail_objs.FIRST .. l_party_merge_obj.merge_detail_objs.LAST
LOOP
IF l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs.COUNT > 0
THEN
FOR p IN
l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs.FIRST .. l_party_merge_obj.merge_detail_objs
(l).to_parent_obj_sys_ref_objs.LAST
LOOP
INSERT INTO xxcdh_party_merge_detail_obj4
VALUES (l_party_merge_obj.batch_id
, l_party_merge_obj.batch_name
, l_party_merge_obj.merge_type
, l_party_merge_obj.automerge_flag
, l_party_merge_obj.created_by
, l_party_merge_obj.creation_date
, l_party_merge_obj.last_update_login
, l_party_merge_obj.last_update_date
, l_party_merge_obj.last_updated_by
, l_party_merge_obj.merge_detail_objs (l).object_type
, l_party_merge_obj.merge_detail_objs (l).operation_type
, l_party_merge_obj.merge_detail_objs (l).to_parent_object_id
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).action_type
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system_ref_id
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).object_type
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).status
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).reason_code
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).old_orig_system_reference
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).start_date_active
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).end_date_active
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute_category
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute1
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute2
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute3
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute4
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute5
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute6
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute7
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute8
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute9
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute10
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute11
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute12
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute13
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute14
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute15
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute16
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute17
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute18
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute19
, l_party_merge_obj.merge_detail_objs (l).to_parent_obj_sys_ref_objs (p).attribute20
);
END LOOP;
END IF;
END LOOP;
END IF;

COMMIT;
END IF;

IF l_msg_count > 0
THEN
FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := l_msg_data || ' ' || fnd_msg_pub.get (i, 'F');
END LOOP;

DBMS_OUTPUT.put_line ('l_msg_data::' || l_msg_data);
END IF;
EXCEPTION
WHEN OTHERS
THEN
-- null;
DBMS_OUTPUT.put_line (l_stage || ' Error :' || SQLERRM);
END;
/

Tuesday, March 12, 2013

API to Create the Extensible Attributes in TCA for Organization using API HZ_EXTENSIBILITY_PUB

This Scripts explains how to create the extensible attributes for the Organization In TCA Architecture using API HZ_EXTENSIBILITY_PUB.

Front End Navigation :-

Oracle Customer Data Librarian Superuser à Administration à Extension à Select the Extensible Attribute Group name form the LOV as shown in Screen shot

DECLARE
l_org_profile_id NUMBER;
l_user_attr_data_table ego_user_attr_data_table;
l_user_attr_row_table ego_user_attr_row_table;
l_application_id NUMBER;
l_attr_group_type VARCHAR2 (40);
l_attr_group1_name VARCHAR2 (30);
l_start_time DATE;
l_end_time DATE;
x_failed_row_id_list VARCHAR2 (10000);
x_return_status VARCHAR2 (1);
x_errorcode NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
BEGIN
l_org_profile_id := 3203;
l_application_id := 222;
l_attr_group_type := 'HZ_ORG_PROFILES_GROUP';
l_attr_group1_name := 'OS_INFO';

l_user_attr_row_table :=
ego_user_attr_row_table (ego_user_attr_row_obj (1
, NULL
, l_application_id
, l_attr_group_type
, l_attr_group1_name
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ego_user_attrs_data_pvt.g_update_mode
)
);

l_user_attr_data_table :=
ego_user_attr_data_table (ego_user_attr_data_obj (1, 'OS_KEY', NULL, 42787902, NULL, NULL, NULL, NULL)
, ego_user_attr_data_obj (1, 'OS_PARENT_KEY', NULL, 517045, NULL, NULL, NULL, NULL)
, ego_user_attr_data_obj (1
, 'OS_PARENT_NAME'
, 'Century Insurance'
, NULL
, NULL
, NULL
, NULL
, NULL
)
, ego_user_attr_data_obj (1, 'OS_ULT_PARENT_KEY', NULL, 517045, NULL, NULL, NULL, NULL)
, ego_user_attr_data_obj (1
, 'OS_ULT_PARENT_NAME'
, 'Century Insurance'
, NULL
, NULL
, NULL
, NULL
, NULL
)
, ego_user_attr_data_obj (1, 'OS_LATITUDE', 'OS_LATITUDE', NULL, NULL, NULL, NULL, NULL)
, ego_user_attr_data_obj (1, 'OS_LONGITUDE', 'OS_LONGITUDE', NULL, NULL, NULL, NULL, NULL)
, ego_user_attr_data_obj (1
, 'OS_BUSINESS_DESC'
, 'OS_BUSINESS_DESC'
, NULL
, NULL
, NULL
, NULL
, NULL
)
);

hz_extensibility_pub.process_organization_record (p_api_version => 1.0
, p_org_profile_id => l_org_profile_id
, p_attributes_row_table => l_user_attr_row_table
, p_attributes_data_table => l_user_attr_data_table
, p_debug_level => 3
, p_commit => fnd_api.g_true
, x_failed_row_id_list => x_failed_row_id_list
, x_return_status => x_return_status
, x_errorcode => x_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);

DBMS_OUTPUT.put_line ( 'API Status, Return Status is: '
|| x_return_status
|| ', Mesage Count is: '
|| x_msg_count
|| ' and Error Message is: '
|| x_msg_data
);

IF (LENGTH (x_failed_row_id_list) > 0)
THEN
DBMS_OUTPUT.put_line ( 'Details of rows which failed: '
|| x_failed_row_id_list);

DECLARE
l_errors_tbl error_handler.error_tbl_type;
BEGIN
error_handler.get_message_list (l_errors_tbl);

FOR i IN 1 .. l_errors_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'API Error : '
|| l_errors_tbl (i).MESSAGE_TEXT);
DBMS_OUTPUT.put_line ( 'Message Type : '
|| l_errors_tbl (i).MESSAGE_TYPE);
END LOOP;
END;
END IF;

END;

Oracle Apps Technical Stuff