DECLARE
order_no_ VARCHAR2(100) := 'O123456';
line_no_ VARCHAR2(100) := '4';
rel_no_ VARCHAR2(100) := '1';
line_item_no_ NUMBER := 0;
docum_class_ VARCHAR2(100) := 'ATTACH';
dir_ VARCHAR2(100) := 'OUT_DIR';
file_ist_ Intface_File_Types := Intface_File_Types();
file_name_ VARCHAR2(100);
file_lu_ VARCHAR2(100);
doc_rev_ VARCHAR2(100);
backup_user_ VARCHAR2(20);
temp_user_ VARCHAR2(20) := 'RMAX';
blobfile_ BLOB;
attr_ VARCHAR2(2000);
info_ VARCHAR2(2000);
objid_ VARCHAR2(2000);
objversion_ VARCHAR2(2000);
doc_no_ doc_issue_tab.doc_no%TYPE;
local_path_out_ edm_file_tab.local_path%TYPE;
key_ref_ Doc_Reference_Object_Tab.Key_Ref%TYPE;
rev_exists_ BOOLEAN := FALSE;
doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '1';
doc_type_ edm_file_tab.doc_type%TYPE := 'ORIGINAL';
file_type_ edm_file_tab.file_type%TYPE;
doc_class_ doc_issue_tab.doc_class%TYPE;
title_ doc_title_tab.title%TYPE;
CURSOR revision_exists IS
SELECT di.doc_class, di.doc_no, di.doc_sheet, di.doc_rev
FROM doc_issue_tab di, edm_file_tab ef
WHERE di.doc_class = ef.doc_class
AND di.doc_no = ef.doc_no
AND di.doc_sheet = ef.doc_sheet
AND di.doc_rev = ef.doc_rev
AND ef.doc_type = 'ORIGINAL'
AND di.doc_class = doc_class_
AND ef.user_file_name = file_name_
AND di.doc_rev = doc_rev_;
rev_rec_ revision_exists%ROWTYPE;
PROCEDURE Read_File_To_Blob (
dir_ IN VARCHAR2,
file_ IN VARCHAR2,
blobfile_ IN OUT NOCOPY BLOB )
IS
bytes_to_read_ PLS_INTEGER;
input_file_ UTL_FILE.File_Type;
buf_ RAW(32000);
read_sofar_ PLS_INTEGER := 0;
chunk_size_ CONSTANT PLS_INTEGER := 32000;
BEGIN
input_file_ := UTL_FILE.FOPEN(dir_, file_, 'rb');
Dbms_Lob.Createtemporary(blobfile_, TRUE);
BEGIN
LOOP
UTL_FILE.Get_Raw(input_file_, buf_, chunk_size_);
bytes_to_read_ := LENGTH(buf_) / 2;
Dbms_Lob.Write(blobfile_, bytes_to_read_, read_sofar_ + 1, buf_);
read_sofar_ := read_sofar_ + bytes_to_read_;
-- utl_file raises no_data_found when unable to read
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.Fclose(input_file_);
END Read_File_To_Blob;
PROCEDURE Backup_File (
dir_ IN VARCHAR2,
filename_ IN VARCHAR2 )
IS
src_location_ VARCHAR2(500);
src_filename_ VARCHAR2(500);
dest_location_ VARCHAR2(500);
dest_filename_ VARCHAR2(500);
BEGIN
BEGIN
src_location_ := dir_;
src_filename_ := filename_;
dest_location_ := dir_;
dest_filename_ := 'de03799_backup\' || filename_;
UTL_FILE.FCOPY(src_location_, src_filename_, dest_location_, dest_filename_);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.Fclose(input_file_);
END Read_File_To_Blob;
BEGIN
backup_user_ := Fnd_Session_API.Get_Fnd_User;
Fnd_Session_API.Impersonate_Fnd_User(temp_user_);
file_ist_ := Intface_Server_File_API.File_List(dir_);
FOR i IN 1..file_ist_.count LOOP
Dbms_Output.Put_Line(file_ist_(i).file_name);
file_name_ := file_ist_(i).file_name;
file_lu_ := SUBSTR(file_name_, 1, INSTR(file_name_, '_') - 1);
file_type_ := Edm_Application_API.Get_File_Type(Edm_File_Util_API.Get_File_Extension_(file_name_));
title_ := SUBSTR(file_name_, 1, INSTR(file_name_, '.', -1) -1);
OPEN revision_exists;
FETCH revision_exists INTO rev_rec_;
IF (revision_exists%FOUND) THEN
rev_exists_ := TRUE;
END IF;
CLOSE revision_exists;
IF (rev_exists_) THEN
doc_class_ := rev_rec_.doc_class;
doc_no_ := rev_rec_.doc_no;
doc_sheet_ := rev_rec_.doc_sheet;
doc_rev_ := rev_rec_.doc_rev;
ELSE
Client_sys.Clear_Attr(attr_);
doc_class_ := docum_class_;
doc_no_ := NULL;
doc_sheet_ := NULL;
doc_rev_ := NULL;
Doc_Title_API.Create_New_Document(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
title_,
attr_,
0);
Document_Issue_Access_API.Copy_Access_Template__(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_);
Edm_File_API.Create_File_Reference(local_path_out_,
doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
doc_type_,
file_type_,
dir_,
0,
file_name_);
Edm_File_API.Set_File_State(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
doc_type_,
'StartCheckOut',
local_path_out_);
Client_sys.Clear_Attr(attr_);
Client_sys.Add_To_Attr('DOC_CLASS', doc_class_, attr_);
Client_sys.Add_To_Attr('DOC_NO', doc_no_, attr_);
Client_sys.Add_To_Attr('DOC_SHEET', doc_sheet_, attr_);
Client_sys.Add_To_Attr('DOC_REV', doc_rev_, attr_);
Client_sys.Add_To_Attr('DOC_TYPE', doc_type_, attr_);
Edm_file_Storage_API.New__(info_, objid_, objversion_, attr_, 'DO');
Edm_file_Op_Announce_API.Announce_File_Operation(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
'WRITE');
Read_File_To_Blob(dir_, file_name_, blobfile_);
Edm_File_Storage_API.Write_Blob_Data(objversion_, objid_, blobfile_);
Edm_File_API.Set_File_State(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
doc_type_,
'FinishCheckIn',
local_path_out_);
END IF;
IF (file_lu_ = 'COL') THEN
IF (Customer_Order_Line_API.Exists(order_no_, line_no_, rel_no_, line_item_no_)) THEN
key_ref_ := 'LINE_ITEM_NO=' || line_item_no_ || Client_SYS.text_separator_ ||
'LINE_NO=' || line_no_ || Client_SYS.text_separator_ ||
'ORDER_NO=' || order_no_ || Client_SYS.text_separator_ ||
'REL_NO=' || rel_no_ || Client_SYS.text_separator_;
IF (NOT Doc_Reference_Object_API.Exists(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'CustomerOrderLine', key_ref_)) THEN
Doc_Reference_Object_API.Create_New_Reference(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
'CustomerOrderLine',
key_ref_);
END IF;
END IF;
ELSIF (file_lu_ = 'CUST') THEN
IF (Customer_Order_Line_API.Exists(order_no_, line_no_, rel_no_, line_item_no_)) THEN
key_ref_ := 'ORDER_NO=' || order_no_ || Client_SYS.text_separator_;
IF (NOT Doc_Reference_Object_API.Exists(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'CustomerOrder', key_ref_)) THEN
Doc_Reference_Object_API.Create_New_Reference(doc_class_,
doc_no_,
doc_sheet_,
doc_rev_,
'CustomerOrder',
key_ref_);
END IF;
END IF;
END IF;
IF (NOT rev_exists_) THEN
Doc_Issue_API.Set_Doc_To_Approved__(doc_class_, doc_no_, doc_sheet_, doc_rev_);
Doc_Issue_API.Set_Released__(info_, doc_class_, doc_no_, doc_sheet_, doc_rev_);
Doc_Issue_API.Update_Doc_Resp_Person(doc_class_, doc_no_, doc_sheet_, doc_rev_, Person_Info_API.Get_Id_For_Current_User, '', 'FALSE');
END IF;
Dbms_Output.Put_Line(doc_class_ || '-' || doc_no_ || '-' || doc_sheet_ || '-' || doc_rev_);
END LOOP;
Fnd_Session_API.Reset_Fnd_User;
END;top of page
Related Posts
See All[FndWindowRegistration("YVVDA_COMPANY_ADDR", "YvvdaCompanyAddr", FndWindowRegistrationFlags.HomePage)] [FndDynamicTabPage("frmCompanyAddr...
Example 1: SELECT DISTINCT LISTAGG(t.commission_receiver, ';') WITHIN GROUP (ORDER BY t.commission_receiver) FROM...
FUNCTION Strip_Ora_Error ( sqlerrm_ IN VARCHAR2, sqlcode_ IN NUMBER DEFAULT NULL, strip_ora_only_ IN BOOLEAN DEFAULT...
bottom of page
Komentáře