DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_user_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE;
l_description fnd_documents_tl.description%TYPE;
l_seq_num NUMBER;
lv_short_text VARCHAR2(1000);
lv_bill_address1 VARCHAR2(1000);
lv_bill_address2 VARCHAR2(1000);
lv_bill_city VARCHAR2(1000);
lv_bill_state VARCHAR2(1000);
lv_bill_countryid VARCHAR2(1000);
lv_bill_postalcode VARCHAR2(1000);
l_title VARCHAR2(1000);
l_security_id NUMBER;
l_storage_type VARCHAR2(1000);
l_image_type VARCHAR2(1000);
l_start_date_active DATE;
l_end_date_active DATE;
l_usage_type VARCHAR2(1000);
lv_notes_cnt NUMBER;
lv_bill_company VARCHAR2(1000);
v_long_text CLOB;
v_bfile BFILE;
v_clob CLOB;
l_blob BLOB;
CURSOR l_data IS
SELECT xfad.ATTACHED_DOCUMENT_ID,
xfad.document_id,
xfad.seq_num,
xfad.entity_name,
xfad.pk1_value,
xfad.pk2_value,
xfad.AUTOMATICALLY_ADDED_FLAG,
xfad.PROGRAM_UPDATE_DATE,
xfad.category_id,
(SELECT category_id
FROM FND_DOCUMENT_CATEGORIES_vl fdc
WHERE fdc.user_name = xfd.category_name)
new_category_id,
xfd.category_name,
xfd.datatype_id,
(SELECT datatype_id
FROM FND_DOCUMENT_DATATYPES fdd
WHERE fdd.name = xfd.datatype_name AND LANGUAGE = 'TR')
new_datatype_id,
xfd.security_type,
xfd.security_id,
xfd.publish_flag,
xfd.USAGE_TYPE,
xfd.START_DATE_ACTIVE,
xfd.media_id,
xfd.file_name,
(select po_header_id from po_headers_all where GLOBAL_ATTRIBUTE1=xfad.pk1_value and rownum=1) new_po_header_id,
xfad.status ,
poh.type_lookup_code,
poh.segment1,
poh.creation_date,
po_header_id,
GLOBAL_ATTRIBUTE1
FROM XX_FND_ATTACHED_DOCUMENTS xfad, XX.XX_FND_DOCUMENTS xfd ,po_headers_all poh
WHERE xfd.document_id = xfad.document_id and pk1_value = poh.GLOBAL_ATTRIBUTE1
and xfad.status is null and xfd.status is null ;
BEGIN
FOR n_data IN l_data LOOP
l_media_id :=NULL;
l_document_id := NULL;
l_attached_document_id := NULL;
BEGIN
SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
l_document_id := 1;
END;
BEGIN
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
l_attached_document_id := 1;
END;
DBMS_OUTPUT.PUT_LINE('document_id='||l_document_id);
DBMS_OUTPUT.PUT_LINE('attached_document_id='||l_attached_document_id);
fnd_documents_pkg.insert_row(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => -1,
X_datatype_id => n_data.new_datatype_id,
x_category_id => n_data.new_category_id,
x_security_type => n_data.security_type,
x_security_id => n_data.security_id,
x_publish_flag => n_data.publish_flag,
x_usage_type => n_data.USAGE_TYPE,
X_start_date_active =>n_data.START_DATE_ACTIVE,
x_language => 'TR',
-- x_description => l_description,
x_file_name => n_data.file_name,
--x_title => 'Line level Notes',
x_media_id => l_media_id);
fnd_documents_pkg.insert_tl_row(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => -1,
x_language => 'US'
-- x_description => l_description
);
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 => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => -1,
x_seq_num => n_data.seq_num,
x_entity_name => n_data.entity_name,
x_column1 => NULL,
x_pk1_value => n_data.po_header_id ,
x_pk2_value => n_data.pk2_value,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
X_program_update_date => n_data.program_update_date,
x_automatically_added_flag => n_data.AUTOMATICALLY_ADDED_FLAG,
x_datatype_id => n_data.new_datatype_id,
x_category_id => NVL(n_data.new_category_id,1),
x_security_type => n_data.security_type,
x_publish_flag => n_data.publish_flag,
x_language => 'TR',
-- x_description => l_description,
x_media_id => l_media_id);
DBMS_OUTPUT.PUT_LINE ('Media id '||l_media_id);
-- lv_short_text := 'Test for short text attachment INSERT';
IF n_data.NEW_DATATYPE_ID =2 AND l_media_id is not null THEN
BEGIN
FOR r IN (SELECT *
FROM fnd_documents_long_text@DB_LINK
WHERE media_id = n_data.media_id)
LOOP
INSERT INTO fnd_documents_long_text
VALUES (l_media_id, NULL, r.long_text);
END LOOP;
UPDATE xx.XX_FND_ATTACHED_DOCUMENTS
SET status = 'UPDATE'
WHERE ATTACHED_DOCUMENT_ID = n_data.ATTACHED_DOCUMENT_ID;
UPDATE XX.XX_FND_DOCUMENTS
SET status = 'UPDATE'
WHERE document_id = n_data.document_id;
COMMIT;
END;
ELSIF n_data.NEW_DATATYPE_ID =6 AND l_media_id is not null THEN
INSERT INTO FND_LOBS (file_id,
file_name,
file_content_type,
file_data,
upload_date,
program_name,
language,
oracle_charset,
file_format)
SELECT l_media_id,
file_name,
file_content_type,
file_data,
upload_date,
program_name,
language,
oracle_charset,
file_format
FROM FND_LOBS@DB_LINK
WHERE file_id = n_data.media_id;
UPDATE xx.XX_FND_ATTACHED_DOCUMENTS
SET status = 'UPDATE'
WHERE ATTACHED_DOCUMENT_ID = n_data.ATTACHED_DOCUMENT_ID;
UPDATE XX.XX_FND_DOCUMENTS
SET status = 'UPDATE'
WHERE document_id = n_data.document_id;
COMMIT;
END IF;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
No comments:
Post a Comment