APPS.FND_WEBATTCH SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 304

     ** Create the callback syntax to update the local fields
     */
     fnd_document_management.set_document_id_html (
        null,
        'FNDATTACH',
        'dmid',
        'dmname',
        l_callback_url);

Line: 358

procedure UpdateAttachment(
	attached_document_id		in varchar2,
	function_name			in varchar2,
	entity_name			in varchar2,
	pk1_value			in varchar2,
	pk2_value			in varchar2	default NULL,
	pk3_value			in varchar2	default NULL,
	pk4_value			in varchar2	default NULL,
	pk5_value			in varchar2	default NULL,
	from_url			in varchar2,
	query_only			in varchar2	default 'N'
)
as
l_attached_document_id	varchar2(16);

Line: 397

		icx_call.decrypt2(UpdateAttachment.attached_document_id);

Line: 398

  l_function_name := icx_call.decrypt2(UpdateAttachment.function_name);

Line: 399

  l_entity_name	:= icx_call.decrypt2(UpdateAttachment.entity_name);

Line: 400

  l_pk1_value := icx_call.decrypt2(UpdateAttachment.pk1_value);

Line: 401

  l_pk2_value := icx_call.decrypt2(UpdateAttachment.pk2_value);

Line: 402

  l_pk3_value := icx_call.decrypt2(UpdateAttachment.pk3_value);

Line: 403

  l_pk4_value := icx_call.decrypt2(UpdateAttachment.pk4_value);

Line: 404

  l_pk5_value := icx_call.decrypt2(UpdateAttachment.pk5_value);

Line: 405

  l_from_url := icx_call.decrypt2(UpdateAttachment.from_url);

Line: 406

  l_query_only := icx_call.decrypt2(UpdateAttachment.query_only);

Line: 422

		'/fnd_webattch.PrintUpdateAttachment?attached_document_id='||
						l_attached_document_id ||
		'&'||'package_name='|| l_packagename ||
		'&'||'function_name='||l_function_name ||
		'&'||'entity_name='||l_entity_name ||
		'&'||'pk1_value='||l_pk1_value ||
		'&'||'pk2_value='||l_pk2_value ||
		'&'||'pk3_value='||l_pk3_value ||
		'&'||'pk4_value='||l_pk4_value ||
		'&'||'pk5_value='||l_pk5_value||
		'&'||'from_url='||l_from_url ||
		'&'||'query_only='||l_query_only,
		cname => 'main_frame',
	     cmarginheight=>'0', cmarginwidth=> '10', cscrolling => 'AUTO',
	     cnoresize => 'NORESIZE', cattributes => 'FRAMEBORDER=NO');

Line: 441

end UpdateAttachment;

Line: 450

procedure DeleteAttachment(
	attached_document_id	in varchar2,
	function_name		in varchar2,
	entity_name		in varchar2,
	pk1_value		in varchar2,
	pk2_value		in varchar2	default NULL,
	pk3_value		in varchar2	default NULL,
	pk4_value		in varchar2	default NULL,
	pk5_value		in varchar2	default NULL,
	from_url		in varchar2,
	query_only		in varchar2	default 'N'
)
as
   cursor delatt_cursor (x_attached_document_id varchar2) is
	select datatype_id,file_name,media_id
	  from fnd_attached_docs_form_vl
	 where attached_document_id =  to_number(x_attached_document_id);

Line: 474

		icx_call.decrypt2(deleteattachment.attached_document_id);

Line: 494

  fnd_attached_documents3_pkg.delete_row ( l_attached_document_id,
					   l_datatype_id, 'Y' );

Line: 499

      DELETE FROM fnd_lobs WHERE file_id = l_media_id;

Line: 514

end DeleteAttachment;

Line: 690

     select dt.file_name, dt.media_id
     from fnd_attached_documents ad, fnd_documents_tl dt
     where  ad.document_id = dt.document_id
     and  ad.attached_document_id = to_number(l_attached_document_id);

Line: 829

 l_last_update_date	date := SYSDATE;

Line: 830

 l_last_updated_by	number;

Line: 844

  l_last_updated_by := l_created_by;

Line: 847

  select fnd_attached_documents_s.nextval
  into l_attached_document_id
  from sys.dual;

Line: 852

  select userenv ('LANG')
  into l_lang
  from dual;

Line: 857

  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		=> l_creation_date		,
	x_created_by		=> l_created_by			,
	x_last_update_date	=> l_last_update_date		,
	x_last_updated_by	=> l_last_updated_by		,
	x_last_update_login	=> NULL				,
	x_seq_num		=> to_number(seq_num)		,
	x_entity_name		=> entity_name			,
	x_column1		=> NULL				,
	x_pk1_value		=> pk1_value			,
	x_pk2_value		=> pk2_value			,
	x_pk3_value		=> pk3_value			,
	x_pk4_value		=> pk4_value			,
	x_pk5_value		=> pk5_value			,
	x_automatically_added_flag	=> 'N'			,
	x_request_id		=> NULL				,
	x_program_application_id	=>NULL			,
	x_program_id		=> NULL				,
	x_program_update_date	=> NULL				,
	x_attribute_category	=> NULL				,
	x_attribute1		=> NULL				,
	x_attribute2		=> NULL				,
	x_attribute3		=> NULL				,
	x_attribute4		=> NULL				,
	x_attribute5		=> NULL				,
	x_attribute6		=> NULL				,
	x_attribute7		=> NULL				,
	x_attribute8		=> NULL				,
	x_attribute9		=> NULL				,
	x_attribute10		=> NULL				,
	x_attribute11		=> NULL				,
	x_attribute12		=> NULL				,
	x_attribute13		=> NULL				,
	x_attribute14		=> NULL				,
	x_attribute15		=> NULL				,
	x_datatype_id		=> to_number(datatype_id)	,
	x_category_id		=> to_number(category_id)	,
	x_security_type		=> 4				,
	x_security_id		=> NULL				,
	x_publish_flag		=> 'Y'				,
	x_image_type		=> NULL				,
	x_storage_type		=> NULL				,
	x_usage_type		=> usage_type			,
	x_language		=> l_lang			,
	x_description		=> document_description		,
	x_file_name		=> l_file_name			,
	x_media_id		=> l_media_id			,
	x_doc_attribute_category	=> NULL			,
	x_doc_attribute1	=> NULL				,
	x_doc_attribute2	=> NULL				,
	x_doc_attribute3	=> NULL				,
	x_doc_attribute4	=> NULL				,
	x_doc_attribute5	=> NULL				,
	x_doc_attribute6	=> NULL				,
	x_doc_attribute7	=> NULL				,
	x_doc_attribute8	=> NULL				,
	x_doc_attribute9	=> NULL				,
	x_doc_attribute10	=> NULL				,
	x_doc_attribute11	=> NULL				,
	x_doc_attribute12	=> NULL				,
	x_doc_attribute13	=> NULL				,
	x_doc_attribute14	=> NULL				,
	x_doc_attribute15	=> NULL				,
	x_create_doc		=> 'N'				,
	x_url			=> url				,
	x_title			=> title
  );

Line: 935

  	INSERT INTO fnd_documents_long_text(
       		media_id,
       		long_text) VALUES (
       		l_media_id,
       		text);

Line: 944

  	INSERT INTO fnd_documents_short_text(
       		media_id,
       		short_text) VALUES (
       		l_media_id,
       		text);

Line: 960

PROCEDURE update_attachment_gfm_wrapper(
        seq_num varchar2,
        category_id varchar2,
        document_description varchar2 DEFAULT NULL,
        text varchar2 DEFAULT NULL,
        file_name varchar2 DEFAULT NULL,
        url varchar2 DEFAULT NULL,
        attached_document_id varchar2 DEFAULT NULL,
        datatype_id varchar2,
        function_name varchar2 DEFAULT NULL,
        entity_name varchar2 DEFAULT NULL,
        pk1_value varchar2 DEFAULT NULL,
        pk2_value varchar2 DEFAULT NULL,
        pk3_value varchar2 DEFAULT NULL,
        pk4_value varchar2 DEFAULT NULL,
        pk5_value varchar2 DEFAULT NULL,
        from_url varchar2 DEFAULT NULL,
        query_only varchar2 DEFAULT NULL,
        dmid       VARCHAR2 DEFAULT NULL,
        dmname     VARCHAR2 DEFAULT NULL,
        package_name varchar2
  ) AS
  l_media_id NUMBER;

Line: 1007

                                        program_name => 'UPDATE_ATTACHMENT');

Line: 1013

  Update_Attachment(seq_num, category_id, document_description, text,
        l_file_name, url, attached_document_id, datatype_id, function_name,
        entity_name, pk1_value, pk2_value, pk3_value, pk4_value, pk5_value,
        l_media_id, l_user_id);

Line: 1041

procedure Update_Attachment (
	seq_num			in varchar2			,
	category_id		in varchar2			,
	document_description	in varchar2			,
	text			in long				,
	file_name		in varchar2			,
	url			in varchar2			,
	attached_document_id	in varchar2			,
	datatype_id		in varchar2			,
	function_name		in varchar2			,
	entity_name		in varchar2			,
	pk1_value		in varchar2			,
	pk2_value		in varchar2			,
	pk3_value		in varchar2			,
	pk4_value		in varchar2			,
	pk5_value		in varchar2			,
	media_id		in number			,
        user_id                 in varchar2
) as
   cursor update_att_cursor (x_attached_document_id varchar2,
			     x_function_name varchar2) is
	select	row_id, document_id, media_id, start_date_active,
		end_date_active, datatype_id
	from 	fnd_attached_docs_form_vl
	where attached_document_id = to_number(x_attached_document_id)
	and   function_name= x_function_name
	and   function_type = 'F';

Line: 1069

  upddatarec	update_att_cursor%ROWTYPE;

Line: 1073

 l_last_update_date	date := SYSDATE;

Line: 1074

 l_last_updated_by	number;

Line: 1078

 l_attached_document_id	varchar2(16):= update_attachment.attached_document_id;

Line: 1079

 l_function_name	varchar2(32):= update_attachment.function_name;

Line: 1085

  open update_att_cursor (l_attached_document_id,l_function_name);

Line: 1086

  fetch update_att_cursor into upddatarec;

Line: 1087

  if update_att_cursor%NOTFOUND then
     close update_att_cursor;

Line: 1097

	l_media_id := update_attachment.media_id;

Line: 1098

	l_file_name := update_attachment.file_name;

Line: 1100

	l_file_name := update_attachment.file_name;

Line: 1103

	l_file_name := update_attachment.file_name;

Line: 1108

  l_last_updated_by := to_number(Update_Attachment.user_id);

Line: 1111

  select USERENV('LANG')
  into l_lang
  from dual;

Line: 1116

  fnd_attached_documents_pkg.update_row (
	x_rowid	=> upddatarec.row_id				,
	x_attached_document_id	=> attached_document_id	,
	x_document_id	=> upddatarec.document_id		,
	x_last_update_date	=> l_last_update_date	,
	x_last_updated_by	=> l_last_updated_by	,
	x_last_update_login	=> NULL			,
	x_seq_num	=> to_number(seq_num)		,
	x_entity_name	=> entity_name			,
	x_column1	=> NULL				,
	x_pk1_value	=> pk1_value			,
	x_pk2_value	=> pk2_value			,
	x_pk3_value	=> pk3_value			,
	x_pk4_value	=> pk4_value			,
	x_pk5_value	=> pk5_value			,
	x_automatically_added_flag	=> 'N'		,
	x_request_id	=> NULL				,
	x_program_application_id	=>NULL		,
	x_program_id	=> NULL				,
	x_program_update_date	=> NULL			,
	x_attribute_category	=> NULL			,
	x_attribute1	=> NULL				,
	x_attribute2	=> NULL				,
	x_attribute3	=> NULL				,
	x_attribute4	=> NULL				,
	x_attribute5	=> NULL				,
	x_attribute6	=> NULL				,
	x_attribute7	=> NULL				,
	x_attribute8	=> NULL				,
	x_attribute9	=> NULL				,
	x_attribute10	=> NULL				,
	x_attribute11	=> NULL				,
	x_attribute12	=> NULL				,
	x_attribute13	=> NULL				,
	x_attribute14	=> NULL				,
	x_attribute15	=> NULL				,
	x_datatype_id	=> datatype_id			,
	x_category_id	=> to_number(category_id)	,
	x_security_type	=> 4				,
	x_security_id	=> NULL				,
	x_publish_flag	=> 'Y'				,
	x_image_type	=> NULL				,
	x_storage_type	=> NULL				,
	x_usage_type	=> 'O'				,
	x_start_date_active => upddatarec.start_date_active	,
	x_end_date_active => upddatarec.end_date_active	,
	x_language	=> l_lang		,
	x_description	=> document_description		,
	x_file_name	=> l_file_name			,
	x_media_id	=> l_media_id			,
	x_doc_attribute_category	=> NULL		,
	x_doc_attribute1	=> NULL			,
	x_doc_attribute2	=> NULL			,
	x_doc_attribute3	=> NULL			,
	x_doc_attribute4	=> NULL			,
	x_doc_attribute5	=> NULL			,
	x_doc_attribute6	=> NULL			,
	x_doc_attribute7	=> NULL			,
	x_doc_attribute8	=> NULL			,
	x_doc_attribute9	=> NULL			,
	x_doc_attribute10	=> NULL			,
	x_doc_attribute11	=> NULL			,
	x_doc_attribute12	=> NULL			,
	x_doc_attribute13	=> NULL			,
	x_doc_attribute14	=> NULL			,
	x_doc_attribute15	=> NULL
  );

Line: 1191

     UPDATE fnd_documents_short_text
     set short_text = text
     where media_id = l_media_id;

Line: 1199

     UPDATE fnd_documents_long_text
     set long_text = text
     where media_id = l_media_id;

Line: 1210

    if (update_att_cursor%ISOPEN) then
	close update_att_cursor;

Line: 1213

end Update_Attachment;

Line: 1316

    select SEQ_NUM ,
           CATEGORY_DESCRIPTION,
           DOCUMENT_DESCRIPTION,
           DATATYPE_NAME,
           DATATYPE_ID,
           FILE_NAME,
           USAGE_TYPE,
           USER_ENTITY_NAME,
           MEDIA_ID,
           ATTACHED_DOCUMENT_ID
    from   FND_ATTACHED_DOCS_FORM_VL
    where  FUNCTION_NAME =printsummary.function_name
    and    FUNCTION_TYPE ='F'
    and   (SECURITY_TYPE = 4 OR PUBLISH_FLAG = 'Y')
    and   (ENTITY_NAME= printsummary.entity_name and
	  	PK1_VALUE=printsummary.pk1_value and
            decode(printsummary.pk2_value,null,'*',PK2_VALUE)=
  	    decode(printsummary.pk2_value,null,'*',printsummary.pk2_value) and
            decode(printsummary.pk3_value,null,'*',PK3_VALUE)=
	    decode(printsummary.pk3_value,null,'*',printsummary.pk3_value) and
            decode(printsummary.pk4_value,null,'*',PK4_VALUE)=
	    decode(printsummary.pk4_value,null,'*',printsummary.pk4_value) and
            decode(printsummary.pk5_value,null,'*',PK5_VALUE)=
	    decode(printsummary.pk5_value,null,'*',printsummary.pk5_value))
    order by USER_ENTITY_NAME,SEQ_NUM;

Line: 1362

  l_del_msg := fnd_message.get_string('FND','ATCHMT-DELETE-ATCHMT');

Line: 1370

  htp.p( ' function delete_attachment (del_url) {
      if (confirm('||'"'||l_del_msg||'"'||'))
      {
       	  parent.location=del_url
      }
    }');

Line: 1549

		||'.UpdateAttachment?attached_document_id='||
           	icx_call.encrypt2(to_char(atlstrec.attached_document_id))
           	||'&'||'function_name='||icx_call.encrypt2(function_name)
           	||'&'||'entity_name='||icx_call.encrypt2(entity_name)
           	||'&'||'pk1_value='||icx_call.encrypt2(pk1_value)
           	||'&'||'pk2_value='||icx_call.encrypt2(pk2_value)
           	||'&'||'pk3_value='||icx_call.encrypt2(pk3_value)
           	||'&'||'pk4_value='||icx_call.encrypt2(pk4_value)
           	||'&'||'pk5_value='||icx_call.encrypt2(pk5_value)
           	||'&'||'from_url='|| icx_call.encrypt2
					(replace(from_url,'&','%26'))
           	||'&'||'query_only='||icx_call.encrypt2(query_only),
         	htf.img2( '/OA_MEDIA/FNDIEDIT.gif', calign => 'CENTER',
            	calt => '''Edit Attachment''',
            	cattributes => 'border=yes width=17 height=16'),
            	cattributes=>'target="_top"')
	  ||'');

Line: 1570

		htf.anchor2('javascript:delete_attachment('''
		|| owa_util.get_owa_service_path
		||'fnd_webattch.DeleteAttachment?attached_document_id='
		||icx_call.encrypt2(to_char(atlstrec.attached_document_id))
           	||'&'||'function_name='||icx_call.encrypt2(function_name)
           	||'&'||'entity_name='||icx_call.encrypt2(entity_name)
           	||'&'||'pk1_value='||icx_call.encrypt2(pk1_value)
           	||'&'||'pk2_value='||icx_call.encrypt2(pk2_value)
           	||'&'||'pk4_value='||icx_call.encrypt2(pk4_value)
           	||'&'||'pk5_value='||icx_call.encrypt2(pk5_value)
           	||'&'||'from_url='||
               	 icx_call.encrypt2(replace(from_url,'&','%26'))
           	||'&'||'query_only='||icx_call.encrypt2(query_only)||''')',
                htf.img2( '/OA_MEDIA/FNDIDELR.gif',
                   calign => 'CENTER', calt => '''Delete Attachment''',
                   cattributes => 'border=yes width=17 height=16'))
	  ||'');

Line: 1754

  select fd.datatype_id
  into l_datatype_id
  from fnd_documents fd, fnd_attached_documents fad
  where fd.document_id = fad.document_id
   and  fad.attached_document_id = to_number(l_attached_document_id);

Line: 1762

     select  fdfv.seq_num, fdfv.category_description,
             fdfv.document_description, fdlt.long_text
     into  l_seq_num,l_category_description,l_document_description,l_text
     from  fnd_attached_docs_form_vl fdfv, fnd_documents_long_text fdlt
     where fdfv.media_id = fdlt.media_id
      and  fdfv.attached_document_id = to_number(l_attached_document_id)
      and  fdfv.function_name = l_function_name
      and  fdfv.function_type = 'F';

Line: 1771

     select  fdfv.seq_num, fdfv.category_description,
             fdfv.document_description, fdst.short_text
     into  l_seq_num,l_category_description,l_document_description,l_text
     from  fnd_attached_docs_form_vl fdfv, fnd_documents_short_text fdst
     where fdfv.media_id = fdst.media_id
      and  fdfv.attached_document_id = to_number(l_attached_document_id)
      and  fdfv.function_name = l_function_name
      and  fdfv.function_type = 'F';

Line: 1832

procedure PrintUpdateAttachment (
        package_name            in varchar2     default 'FND_WEBATTCH',
        attached_document_id    in varchar2,
        function_name           in varchar2,
        entity_name             in varchar2,
        pk1_value               in varchar2,
        pk2_value               in varchar2     default NULL,
        pk3_value               in varchar2     default NULL,
        pk4_value               in varchar2     default NULL,
        pk5_value               in varchar2     default NULL,
        from_url                in varchar2,
        query_only      	in varchar2     default 'N'
) as
  cursor att_doc_cursor is
    select	seq_num,
   		category_description,
   		document_description,
   		datatype_id,
   		media_id,
   		file_name
    from fnd_attached_docs_form_vl
    where attached_document_id = PrintUpdateAttachment.attached_document_id;

Line: 1856

    select user_name,category_id,default_datatype_name,default_datatype_id
    from fnd_doc_categories_active_vl
    where category_id in
       (select fdcu.category_id
        from fnd_doc_category_usages fdcu, fnd_attachment_functions af
   	where af.attachment_function_id = fdcu.attachment_function_id
   	 and  af.function_name = PrintUpdateAttachment.function_name
         and  af.function_type = 'F'
   	 and  fdcu.enabled_flag = 'Y')
	order by user_name;

Line: 1919

       document.UPDATE_ATCHMT.submit();

Line: 1952

								||'for update');

Line: 1974

   upload_action := 'fnd_webattch.update_attachment_gfm_wrapper';

Line: 1976

   htp.formOpen( curl => upload_action, cattributes=>'NAME="UPDATE_ATCHMT"',
                cmethod => 'POST',cenctype=> 'multipart/form-data');

Line: 2018

   htp.formSelectOpen( cname => 'category_id' );

Line: 2024

	   htp.p(''||dc.user_name);

Line: 2031

   htp.formSelectClose;

Line: 2060

      select short_text
        into document_short_text
      from fnd_documents_short_text
      where media_id = x_media_id;

Line: 2070

      select long_text
        into document_long_text
      from fnd_documents_long_text
      where media_id = x_media_id;

Line: 2117

	|| PrintUpdateAttachment.package_name ||';'||to_char(access_id)||';');

Line: 2150

         fnd_message.get_string('FND','FND-WEBATCH-UPDATE-ATTACHMENT'));

Line: 2153

         ''||

end PrintUpdateAttachment;

	select user_name,category_id,default_datatype_name,default_datatype_id
	from fnd_doc_categories_active_vl
	where category_id in
	 (select fdcu.category_id
	  from fnd_doc_category_usages fdcu, fnd_attachment_functions af
	  where af.attachment_function_id = fdcu.attachment_function_id
	   and     af.function_name = PrintAddAttachment.function_name
           and     af.function_type = 'F'
	   and     fdcu.enabled_flag = 'Y')
	order by user_name;

    select NVL(max(seq_num),0) + 10
      from fnd_attached_documents
     where entity_name = PrintAddAttachment.entity_name
     and   pk1_value   = PrintAddAttachment.pk1_value
     and decode(PrintAddAttachment.pk2_value,null,'*',PK2_VALUE) =
    decode(PrintAddAttachment.pk2_value,null,'*',PrintAddAttachment.pk2_value)
     and decode(PrintAddAttachment.pk3_value,null,'*',PK3_VALUE)=
    decode(PrintAddAttachment.pk3_value,null,'*',PrintAddAttachment.pk3_value)
     and decode(PrintAddAttachment.pk4_value,null,'*',PK4_VALUE)=
    decode(PrintAddAttachment.pk4_value,null,'*', PrintAddAttachment.pk4_value)
     and decode(PrintAddAttachment.pk5_value,null,'*',PK5_VALUE)=
    decode(PrintAddAttachment.pk5_value,null,'*',PrintAddAttachment.pk5_value);

     htp.formSelectOpen( cname => 'category_id' );

     htp.formSelectClose;

     ** Create the callback syntax to update the local fields
     */
     fnd_document_management.set_document_id_html (
        null,
        'FNDATTACH',
        'dmid',
        'dmname',
        l_callback_url);

  ** Update FND_TEMP_FILE_PARAMETERS
  */
  IF (p_file_id IS NOT NULL) THEN
        UPDATE  fnd_temp_file_parameters
        SET     FILE_PARAMETERS = p_document_id
        WHERE   FILE_ID = p_file_id;

  insert into fnd_temp_file_parameters (file_id, file_parameters)
  values (l_file_id, null);