[Home] [Help]
830: ,p_pk1_value in varchar2 default null
831: ,p_attached_document_id out nocopy
832: fnd_attached_documents.attached_document_id%TYPE
833: ,p_document_id out nocopy
834: fnd_documents.document_id%TYPE
835: ,p_media_id out nocopy
836: fnd_documents_tl.media_id%TYPE
837: ,p_rowid out nocopy varchar2
838: ,p_login_person_id in number) is -- 10/14/97 Changed
832: fnd_attached_documents.attached_document_id%TYPE
833: ,p_document_id out nocopy
834: fnd_documents.document_id%TYPE
835: ,p_media_id out nocopy
836: fnd_documents_tl.media_id%TYPE
837: ,p_rowid out nocopy varchar2
838: ,p_login_person_id in number) is -- 10/14/97 Changed
839:
840: -- [CUSTOMIZE]
837: ,p_rowid out nocopy varchar2
838: ,p_login_person_id in number) is -- 10/14/97 Changed
839:
840: -- [CUSTOMIZE]
841: -- Call fnd_attached_documents_pkg.insert_row api to insert into fnd_documents
842: -- table. If customer uses third party software to store the resume, modify
843: -- the code here.
844:
845: l_rowid varchar2(50) default null;
842: -- table. If customer uses third party software to store the resume, modify
843: -- the code here.
844:
845: l_rowid varchar2(50) default null;
846: l_media_id fnd_documents_tl.media_id%TYPE;
847: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
848: default null;
849: l_document_id fnd_documents.document_id%TYPE default null;
850: l_category_id fnd_document_categories.category_id%TYPE
845: l_rowid varchar2(50) default null;
846: l_media_id fnd_documents_tl.media_id%TYPE;
847: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
848: default null;
849: l_document_id fnd_documents.document_id%TYPE default null;
850: l_category_id fnd_document_categories.category_id%TYPE
851: default null;
852: l_datatype_id fnd_document_datatypes.datatype_id%TYPE default 2;
853: l_language varchar2(30) default 'AMERICAN';
897: select fnd_attached_documents_s.nextval
898: into l_attached_document_id
899: from sys.dual;
900:
901: -- Insert document to fnd_documents_long_text
902: --
903: fnd_attached_documents_pkg.insert_row
904: (x_rowid => l_rowid
905: ,x_attached_document_id => l_attached_document_id
941: ,x_doc_attribute14 => null
942: ,x_doc_attribute15 => null);
943: --
944:
945: -- Now insert into fnd_documents_long_text using the media_id
946: -- generated from the above api call
947: --
948: insert into fnd_documents_long_text
949: (media_id
944:
945: -- Now insert into fnd_documents_long_text using the media_id
946: -- generated from the above api call
947: --
948: insert into fnd_documents_long_text
949: (media_id
950: ,long_text)
951: values
952: (l_media_id
978: ,p_rowid in varchar2
979: ,p_login_person_id in number) is -- 10/14/97 Changed
980:
981: -- [CUSTOMIZE]
982: -- Call fnd_attached_documents_pkg.update_row api to update fnd_documents
983: -- table. If customer uses third party software to store the resume, modify
984: -- the code here.
985:
986: l_rowid varchar2(50);
998: where rowid = p_rowid;
999: --
1000: cursor csr_get_doc(csr_p_document_id in number) is
1001: select *
1002: from fnd_documents
1003: where document_id = csr_p_document_id;
1004: --
1005: cursor csr_get_doc_tl (csr_p_lang in varchar2
1006: ,csr_p_document_id in number) is
1004: --
1005: cursor csr_get_doc_tl (csr_p_lang in varchar2
1006: ,csr_p_document_id in number) is
1007: select *
1008: from fnd_documents_tl
1009: where document_id = csr_p_document_id
1010: and language = csr_p_lang;
1011: --
1012: l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
1108: ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1109: ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1110:
1111:
1112: -- Update document to fnd_attached_documents, fnd_documents,
1113: -- fnd_documents_tl and fnd_documents_long_text
1114: --
1115: fnd_attached_documents_pkg.update_row
1116: (x_rowid => p_rowid
1109: ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1110:
1111:
1112: -- Update document to fnd_attached_documents, fnd_documents,
1113: -- fnd_documents_tl and fnd_documents_long_text
1114: --
1115: fnd_attached_documents_pkg.update_row
1116: (x_rowid => p_rowid
1117: ,x_attached_document_id =>
1179: ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1180: ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1181:
1182: -- Now update the long text table
1183: update fnd_documents_long_text
1184: set long_text = p_attachment_text
1185: where media_id = l_doc_tl_pre_upd.media_id;
1186:
1187: EXCEPTION
1200: ,p_pk1_value in varchar2 default null
1201: ,p_effective_date in varchar2
1202: ,p_attached_document_id out nocopy
1203: fnd_attached_documents.attached_document_id%TYPE
1204: ,p_document_id out nocopy fnd_documents.document_id%TYPE
1205: ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
1206: ,p_rowid out nocopy varchar2
1207: ,p_category_id out nocopy fnd_documents.category_id%type
1208: ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1201: ,p_effective_date in varchar2
1202: ,p_attached_document_id out nocopy
1203: fnd_attached_documents.attached_document_id%TYPE
1204: ,p_document_id out nocopy fnd_documents.document_id%TYPE
1205: ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
1206: ,p_rowid out nocopy varchar2
1207: ,p_category_id out nocopy fnd_documents.category_id%type
1208: ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1209:
1203: fnd_attached_documents.attached_document_id%TYPE
1204: ,p_document_id out nocopy fnd_documents.document_id%TYPE
1205: ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
1206: ,p_rowid out nocopy varchar2
1207: ,p_category_id out nocopy fnd_documents.category_id%type
1208: ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1209:
1210: -- [CUSTOMIZE]
1211: -- Call fnd_attached_documents, fnd_documents_tl and
1207: ,p_category_id out nocopy fnd_documents.category_id%type
1208: ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1209:
1210: -- [CUSTOMIZE]
1211: -- Call fnd_attached_documents, fnd_documents_tl and
1212: -- fnd_documents_long_text tables. If customer uses third party
1213: -- software to store the resumes, modify the code here.
1214:
1215: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1208: ,p_seq_num out nocopy fnd_attached_documents.seq_num%type) is
1209:
1210: -- [CUSTOMIZE]
1211: -- Call fnd_attached_documents, fnd_documents_tl and
1212: -- fnd_documents_long_text tables. If customer uses third party
1213: -- software to store the resumes, modify the code here.
1214:
1215: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1216: default null;
1213: -- software to store the resumes, modify the code here.
1214:
1215: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1216: default null;
1217: l_document_id fnd_documents.document_id%TYPE default null;
1218: l_media_id fnd_documents_tl.media_id%TYPE default null;
1219: l_attachment_text long default null;
1220: l_rowid varchar2(50) default null;
1221: l_category_id fnd_documents.category_id%type default null;
1214:
1215: l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1216: default null;
1217: l_document_id fnd_documents.document_id%TYPE default null;
1218: l_media_id fnd_documents_tl.media_id%TYPE default null;
1219: l_attachment_text long default null;
1220: l_rowid varchar2(50) default null;
1221: l_category_id fnd_documents.category_id%type default null;
1222: l_language varchar2(30) default 'AMERICAN';
1217: l_document_id fnd_documents.document_id%TYPE default null;
1218: l_media_id fnd_documents_tl.media_id%TYPE default null;
1219: l_attachment_text long default null;
1220: l_rowid varchar2(50) default null;
1221: l_category_id fnd_documents.category_id%type default null;
1222: l_language varchar2(30) default 'AMERICAN';
1223: l_seq_num fnd_attached_documents.seq_num%type default null;
1224: l_update_date date default null;
1225:
1232: ------------------------------------------------------------------------------
1233: -- Jan. 11, 2001 Bug Fix 1576603:
1234: -- In closer look at the FND api, the fnd_attached_documents_pkg.insert_row
1235: -- (AFAKAADB.pls) always sets start_date_active and end_date_active to null in
1236: -- call to fnd_documents_pkg.insert_row (AFAKADCB.pls). Therefore, the
1237: -- comparision of p_effective_date to start_date_active and end_date_active in
1238: -- the cursor is futile. Therefore, remove the p_effective_date comparision
1239: -- in the cursor and sort the records by creation date, last_update_date so
1240: -- that the most current resume will be displayed.
1242: cursor csr_attached_documents (csr_p_cat_id in number) is
1243: select fatd.rowid, fatd.attached_document_id, fatd.document_id, fatd.seq_num
1244: ,fd.last_update_date
1245: from fnd_attached_documents fatd
1246: ,fnd_documents fd
1247: where fd.category_id = csr_p_cat_id
1248: and fatd.entity_name = p_entity_name
1249: and fatd.pk1_value = p_pk1_value
1250: and fatd.document_id = fd.document_id
1252: ,fd.last_update_date desc; -- retrieve the one updated the last
1253:
1254: cursor csr_documents_tl (csr_p_document_id in number) is
1255: select media_id
1256: from fnd_documents_tl
1257: where document_id = csr_p_document_id;
1258:
1259: cursor csr_documents_long_text (csr_p_media_id in number) is
1260: select long_text
1257: where document_id = csr_p_document_id;
1258:
1259: cursor csr_documents_long_text (csr_p_media_id in number) is
1260: select long_text
1261: from fnd_documents_long_text
1262: where media_id = csr_p_media_id;
1263:
1264: Begin
1265: --
1269: -- -------------------------------------------------------------------------
1270: -- Retrieving a resume requires 4 steps:
1271: -- 1) Get Category ID.
1272: -- 2) Get the attached_document_id, document_id and other fields from
1273: -- the table join of fnd_attached_documents and fnd_documents. The
1274: -- result set can have more than 1 row and is sorted by descending
1275: -- order of the last_update_date. So, if there are multipe resumes
1276: -- returned (which could be possible because a user in 10SC Person
1277: -- form can add an attachment with the category of 'Resume'. When
1276: -- returned (which could be possible because a user in 10SC Person
1277: -- form can add an attachment with the category of 'Resume'. When
1278: -- that happens, we only want the one which is updated most recently.
1279: -- 3) Use the document_id obtained from the 1st record of step 2 to
1280: -- get the media_id from fnd_documents_tl.
1281: -- 4) Use the media_id from step 3 to obtain the resume text from
1282: -- fnd_documents_long_text.
1283: -- -------------------------------------------------------------------------
1284: --
1278: -- that happens, we only want the one which is updated most recently.
1279: -- 3) Use the document_id obtained from the 1st record of step 2 to
1280: -- get the media_id from fnd_documents_tl.
1281: -- 4) Use the media_id from step 3 to obtain the resume text from
1282: -- fnd_documents_long_text.
1283: -- -------------------------------------------------------------------------
1284: --
1285: -- -------------------------------------------------------------------------
1286: -- 1) Get Category ID.