[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| p_mode INSERT or UPDATE |
| p_code_assignment_id assignment id |
| Return : |
| 'Y' overlap |
| 'N' otherwise |
+------------------------------------------------------*/
FUNCTION exist_overlap_assignment
( p_owner_table_name IN VARCHAR2 DEFAULT NULL,
p_owner_table_id IN NUMBER DEFAULT NULL,
p_class_category IN VARCHAR2 DEFAULT NULL,
p_class_Code IN VARCHAR2 DEFAULT NULL,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_mode IN VARCHAR2,
p_code_assignment_id IN NUMBER DEFAULT NULL)
RETURN VARCHAR2
IS
CURSOR c_insert IS
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_id = p_owner_table_id
AND owner_table_name = p_owner_table_name
AND class_category = p_class_category
AND class_code = p_class_code
AND is_overlap(start_date_active,
end_date_active,
p_start_date_active,
p_end_date_active,
decode(status,'I','EXC','INC'))='Y';
CURSOR c_update IS
SELECT 'Y'
FROM hz_code_assignments a,
hz_code_assignments b
WHERE a.code_assignment_id = p_code_assignment_id
AND a.owner_table_name = b.owner_table_name
AND a.owner_table_id = b.owner_table_id
AND a.class_category = b.class_category
AND a.class_code = b.class_code
AND b.code_assignment_id <> a.code_assignment_id
AND is_overlap(b.start_date_active,
b.end_date_active,
p_start_date_active,
p_end_date_active,
DECODE(b.status,'I','EXC','INC'))='Y';
IF p_mode = 'INSERT' THEN
OPEN c_insert;
FETCH c_insert INTO lres;
IF c_insert%NOTFOUND THEN
lres := 'N';
CLOSE c_insert;
ELSIF p_mode = 'UPDATE' THEN
OPEN c_update;
FETCH c_update INTO lres;
IF c_update%NOTFOUND THEN
lres := 'N';
CLOSE c_update;
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_id = p_owner_table_id
AND owner_table_name = p_owner_table_name
AND class_category = p_class_category
AND class_code = p_class_code
AND TRUNC(sysdate) >= start_date_active
AND TRUNC(sysdate) <= NVL(end_Date_active,sysdate)
AND DECODE(end_date_active,
TRUNC(sysdate) ,NVL(status,'A'),'A') = 'A';
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND end_date_active IS NULL;
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code;
'INSERT',
NULL);
| Name : Update_Code_assignment |
| |
| Description : |
| Wrapper on the top TCA V2 API for |
| Code assignment updation . |
| |
| Parameter : |
| From the record type |
| HZ_CLASSIFCIATION_V2PUB.CODE_ASSIGNEMENT_REC_TYPE |
| p_class_category class_category |
| p_class_code class code |
| p_start_date_active start date of the assignment|
| p_end_date_active end date of the assignment |
| p_primary_flag primary Y or N |
| p_content_source_type origin of the assugnment |
| p_status status |
| p_rank for hierarchy assignment |
| x_object_version_number record vesrion |
| x_code_assignment_id OUT assignment id |
| x_return_status OUT status execution |
| x_msg_count OUT number of error met |
| x_msg_data OUT the error message |
+------------------------------------------------------*/
PROCEDURE Update_Code_assignment
( p_code_assignment_id IN NUMBER,
p_class_category IN VARCHAR2,
p_class_code IN VARCHAR2,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_content_source_type IN VARCHAR2,
p_primary_flag IN VARCHAR2,
p_status IN VARCHAR2,
p_rank IN NUMBER,
x_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
CURSOR cu_code_assig IS
SELECT ROWID,
START_DATE_ACTIVE,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE
FROM hz_code_assignments
WHERE Code_assignment_id = p_code_assignment_id;
l_last_update_date DATE;
arp_standard.debug('Update_Code_assignment(+)');
l_last_update_date;
arp_standard.debug('Last_update_date:'||to_char(l_last_update_date));
FND_MESSAGE.SET_NAME('AR','HZ_API_NONUPDATEABLE_COLUMN');
'UPDATE',
p_code_assignment_id);
hz_classification_v2pub.update_code_assignment(
p_code_assignment_rec => l_code_assignment_rec,
p_object_version_number => x_object_version_number ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
arp_standard.debug('update_code_assignment (-)');
arp_standard.debug('Exception arh_classification_pkg.update_code_assignment:'||x_msg_data);
arp_standard.debug('Exception arh_classification_pkg.update_code_assignment:'||x_msg_data);
arp_standard.debug('OTHER Exception arh_classification_pkg.update_code_assignment:'||
x_msg_data);
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND start_date_active> p_start_date_active;