CREATE OR REPLACE package body APPS.xxcust_position_upload_pkg
as
procedure validate_position
is
cursor c1
is
select rowid row_id, new_position_name from xxcust_position_upload;
temp_position_id number;
begin
for i in c1
loop
temp_position_id := null;
begin
select position_id
into temp_position_id
from hr_all_positions_f y
where 1 = 1
and sysdate between effective_start_date
and effective_end_date
and name = i.new_position_name;
exception
when others
then
update xxcust_position_upload
set valid_flag = 'INVALID'
where rowid = i.row_id;
end;
if temp_position_id is null
then
update xxcust_position_upload
set valid_flag = 'INVALID'
where rowid = i.row_id;
else
update xxcust_position_upload
set new_position_id = temp_position_id
where rowid = i.row_id;
update xxcust_position_upload
set valid_flag = 'VALID'
where rowid = i.row_id;
end if;
end loop;
exception
when others
then
null;
end validate_position;
procedure validate_attahement
is
cursor c1
is
select cpos.new_position_id, cpos.new_position_name
from fnd_attached_documents x,
hr_all_positions_f y,
fnd_documents fd_l,
fnd_documents_tl fdt_l,
fnd_lobs fl_l,
xxcust_position_upload cpos
where entity_name = 'PER_POSITIONS'
and y.position_id = to_number (x.pk1_value)
and y.position_id = cpos.new_position_id
and fd_l.document_id = x.document_id
and fdt_l.document_id = fd_l.document_id
and fdt_l.language = 'US'
and fd_l.media_id = fl_l.file_id
and sysdate between effective_start_date
and effective_end_date;
cursor c2
is
select position_id, position_name
from fnd_attached_documents x,
(select distinct position_id, position_name
from xxcust_position_upload) y,
fnd_documents fd_l,
fnd_documents_tl fdt_l,
fnd_lobs fl_l
where entity_name = 'PER_POSITIONS'
and y.position_id = to_number (x.pk1_value)
-- and y.position_id = 28772
and fd_l.document_id = x.document_id
and fdt_l.document_id = fd_l.document_id
and fdt_l.language = 'US'
and fd_l.media_id = fl_l.file_id
group by position_id, position_name
having count (position_id) > 1;
begin
for i in c1
loop
begin
update xxcust_position_upload
set error_flag = 'E',
error_message =
'Attachement Already Exists For This Position'
where new_position_id = i.new_position_id;
exception
when others
then
null;
end;
end loop;
for j in c2
loop
begin
update xxcust_position_upload
set error_flag = 'E',
error_message =
'More Than One Attachement is available for the Parent Position'
where position_id = j.position_id;
exception
when others
then
null;
end;
end loop;
exception
when others
then
null;
end validate_attahement;
procedure attache_position_doc
is
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_file_id number;
l_media_id number;
l_seq_num number;
cursor c1
is
select fl_l.file_id,
fl_l.file_name,
fl_l.file_content_type,
fl_l.file_data,
fl_l.upload_date,
fl_l.expiration_date,
fl_l.program_name,
fl_l.program_tag,
fl_l.language,
fl_l.oracle_charset,
fl_l.file_format,
usage_type,
publish_flag,
x.category_id,
datatype_id,
fd_l.security_id,
fd_l.security_type,
description,
title,
y.position_id,
y.position_name,
y.new_position_id
from fnd_attached_documents x,
(select distinct position_id,
position_name,
new_position_id,
valid_flag,
error_flag,
processed_flag
from xxcust_position_upload) y,
fnd_documents fd_l,
fnd_documents_tl fdt_l,
fnd_lobs fl_l
where entity_name = 'PER_POSITIONS'
and y.position_id = to_number (x.pk1_value)
-- and y.position_id = 28772
and fd_l.document_id = x.document_id
and fdt_l.document_id = fd_l.document_id
and fdt_l.language = 'US'
and fd_l.media_id = fl_l.file_id
and nvl (error_flag, 'A') <> 'E'
and nvl (processed_flag, 'A') <> 'P'
and valid_flag = 'VALID'
and rownum <= 2;
begin
for r1 in c1
loop
fnd_global.apps_initialize (user_id, responsibility_id, applicaiton_id);
begin
select fnd_lobs_s.nextval into l_file_id from dual;
select fnd_documents_s.nextval into l_document_id from dual;
select fnd_attached_documents_s.nextval
into l_attached_document_id
from dual;
select nvl (max (seq_num), 0) + 10
into l_seq_num
from fnd_attached_documents
where pk1_value = r1.new_position_id
and entity_name = 'PER_POSITIONS';
insert into fnd_lobs (file_id,
file_name,
file_content_type,
file_data,
upload_date,
expiration_date,
program_name,
program_tag,
language,
oracle_charset,
file_format)
values (l_file_id,
r1.file_name,
r1.file_content_type,
r1.file_data,
sysdate,
r1.expiration_date,
r1.program_name,
r1.program_tag,
r1.language,
r1.oracle_charset,
r1.file_format);
fnd_documents_pkg.insert_row (
x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => sysdate,
x_created_by => fnd_profile.value ('USER_ID'),
x_last_update_date => sysdate,
x_last_updated_by => fnd_profile.value ('USER_ID'),
x_last_update_login => fnd_profile.value ('LOGIN_ID'),
x_datatype_id => r1.datatype_id,
x_category_id => r1.category_id,
x_security_type => r1.security_type,
x_publish_flag => r1.publish_flag,
x_usage_type => r1.usage_type,
x_language => 'US',
x_description => r1.description,
x_file_name => r1.file_name,
x_media_id => l_file_id);
fnd_documents_pkg.insert_tl_row (
x_document_id => l_document_id,
x_creation_date => sysdate,
x_created_by => fnd_profile.value ('USER_ID'),
x_last_update_date => sysdate,
x_last_updated_by => fnd_profile.value ('USER_ID'),
x_last_update_login => fnd_profile.value ('LOGIN_ID'),
x_language => 'US',
x_description => r1.description--, x_file_name => l_filename
--, x_media_id => l_media_id
);
fnd_attached_documents_pkg.insert_row (
x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => sysdate,
x_created_by => fnd_profile.value ('USER_ID'),
x_last_update_date => sysdate,
x_last_updated_by => fnd_profile.value ('USER_ID'),
x_last_update_login => fnd_profile.value ('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => 'PER_POSITIONS',
x_column1 => null,
x_pk1_value => r1.new_position_id,
x_pk2_value => null,
x_pk3_value => null,
x_pk4_value => null,
x_pk5_value => null,
x_automatically_added_flag => 'N',
x_datatype_id => r1.datatype_id,
x_category_id => r1.category_id,
x_security_type => r1.security_type,
x_publish_flag => 'Y',
x_language => 'US',
x_description => r1.description,
x_file_name => r1.file_name,
x_media_id => l_file_id,
x_title => r1.title);
update xxcust_position_upload
set processed_flag = 'P', processed_date = sysdate
where new_position_id = r1.new_position_id;
exception
when others
then
update xxcust_position_upload
set processed_flag = 'E', processed_date = sysdate
where new_position_id = r1.new_position_id;
end;
end loop;
end attache_position_doc;
end xxcust_position_upload_pkg;
/
No comments:
Post a Comment