[Home] [Help]
3:
4: /*
5: Procedure : create_fnd_attachment
6: Type : Public
7: Purpose : This procedure first checks for the existance of a record in FND_LOBS for the passed primary_key values.
8: In this case, p_CR_id and p_CR_version_number form the primary key.
9: If a record is found, then only the data in FND_LOBS is deleted, and reinserted with empty_blob.
10: In case match is not found, an additional step is performed to insert data into fnd_documents and fnd_attached_documents
11: also. This is done by calling fnd_attached_documents_pkg.Insert_Row.
5: Procedure : create_fnd_attachment
6: Type : Public
7: Purpose : This procedure first checks for the existance of a record in FND_LOBS for the passed primary_key values.
8: In this case, p_CR_id and p_CR_version_number form the primary key.
9: If a record is found, then only the data in FND_LOBS is deleted, and reinserted with empty_blob.
10: In case match is not found, an additional step is performed to insert data into fnd_documents and fnd_attached_documents
11: also. This is done by calling fnd_attached_documents_pkg.Insert_Row.
12:
13: Parameters Type Required Description and Purpose
11: also. This is done by calling fnd_attached_documents_pkg.Insert_Row.
12:
13: Parameters Type Required Description and Purpose
14: ========== ==== ======== =======================
15: p_doc_category_name VARCHAR YES identifier in FND_LOBS
16: p_entity_name VARCHAR YES identifier in FND_LOBS
17: p_file_name VARCHAR YES Name of the attachment
18: p_file_content_type VARCHAR YES Content type of the attachment
19: p_CR_id VARCHAR YES primary key 1 value (Change req id)
12:
13: Parameters Type Required Description and Purpose
14: ========== ==== ======== =======================
15: p_doc_category_name VARCHAR YES identifier in FND_LOBS
16: p_entity_name VARCHAR YES identifier in FND_LOBS
17: p_file_name VARCHAR YES Name of the attachment
18: p_file_content_type VARCHAR YES Content type of the attachment
19: p_CR_id VARCHAR YES primary key 1 value (Change req id)
20: p_CR_version_number VARCHAR YES primary key 1 value (Version number)
17: p_file_name VARCHAR YES Name of the attachment
18: p_file_content_type VARCHAR YES Content type of the attachment
19: p_CR_id VARCHAR YES primary key 1 value (Change req id)
20: p_CR_version_number VARCHAR YES primary key 1 value (Version number)
21: p_file_id NUMBER NO file_id in FND_LOBS
22: */
23:
24:
25: procedure create_fnd_attachment
25: procedure create_fnd_attachment
26: (
27: p_doc_category_name varchar,
28: p_entity_name varchar,
29: p_file_name fnd_lobs.file_name%type,
30: p_file_content_type fnd_lobs.file_content_type%type,
31: p_CR_id varchar,
32: p_CR_version_number varchar,
33: p_file_id IN OUT nocopy number)
26: (
27: p_doc_category_name varchar,
28: p_entity_name varchar,
29: p_file_name fnd_lobs.file_name%type,
30: p_file_content_type fnd_lobs.file_content_type%type,
31: p_CR_id varchar,
32: p_CR_version_number varchar,
33: p_file_id IN OUT nocopy number)
34: is
44: l_category_id number;
45: l_entity_name varchar2(100);
46:
47: l_doc_category_name varchar2(100);
48: l_file_name fnd_lobs.file_name%type;
49: l_file_content_type fnd_lobs.file_content_type%type;
50: l_call_Insert_Row boolean := False;
51:
52:
45: l_entity_name varchar2(100);
46:
47: l_doc_category_name varchar2(100);
48: l_file_name fnd_lobs.file_name%type;
49: l_file_content_type fnd_lobs.file_content_type%type;
50: l_call_Insert_Row boolean := False;
51:
52:
53: begin
86: /* if p_file_id is returned as Null, it means attachments have not been created for this version of the change request.
87: Next value of X_media_id is derived, and l_call_Insert_Row is set to True to invoke fnd_attached_documents_pkg.Insert_Row to insert
88: new records into fnd_documents and fnd_attached_documents.
89:
90: Otherwise p_file_id conatins the file_id of the existing attachment, which is then deleted and record is inserted only into FND_LOBS.
91: */
92: if p_file_id is null then
93:
94: SELECT fnd_lobs_s.nextval
90: Otherwise p_file_id conatins the file_id of the existing attachment, which is then deleted and record is inserted only into FND_LOBS.
91: */
92: if p_file_id is null then
93:
94: SELECT fnd_lobs_s.nextval
95: INTO X_media_id
96: FROM dual;
97:
98: l_call_Insert_Row := TRUE;
97:
98: l_call_Insert_Row := TRUE;
99: else
100:
101: /* delete the existing attachment from fnd_lobs */
102:
103: delete from fnd_lobs
104: where file_id = p_file_id;
105:
99: else
100:
101: /* delete the existing attachment from fnd_lobs */
102:
103: delete from fnd_lobs
104: where file_id = p_file_id;
105:
106: X_media_id := p_file_id;
107: l_call_Insert_Row := False;
110:
111:
112: pa_debug.debug('300: x_media_id:' || x_media_id || ', l_file_name:' || l_file_name);
113:
114: INSERT INTO fnd_lobs (
115: file_id
116: , File_name
117: , file_content_type
118: , upload_date
135: , null
136: , null
137: , 'binary');
138:
139: pa_debug.debug('400: After Insertion into fnd_lobs ');
140:
141: select fnd_attached_documents_s.nextval
142: into l_attached_document_id
143: from dual;
217: and then checks for existence in fnd_documents and fnd_attached_documents.
218:
219: Parameters Type Required Description and Purpose
220: ========== ==== ======== =======================
221: p_entity_name NUMBER YES identifier in FND_LOBS
222: p_pk1_value NUMBER YES Primary key 1 (Change req id)
223: p_pk2_value NUMBER YES Primary key 2 (Version Number)
224: p_pk3_value NUMBER NO Primary key 3
225: p_pk4_value NUMBER NO Primary key 4
223: p_pk2_value NUMBER YES Primary key 2 (Version Number)
224: p_pk3_value NUMBER NO Primary key 3
225: p_pk4_value NUMBER NO Primary key 4
226: p_pk5_value NUMBER NO Primary key 5
227: p_file_id NUMBER NO file_id in FND_LOBS
228: */
229:
230: procedure get_attachment_file_id
231: (p_entity_name IN varchar2,