create or replace
PROCEDURE          "TOKEN_PROJECT_COMMENTS"(p_limsdata_seq_in in limsdata_pull_list.limsdata_seq%type)
/*
||   PURPOSE:    To retrieve the comments (footnotes) attached to workorder(s)
||   PARAMETERS:
||   INPUT: LIMSDATA_SEQ
||   OUTPUT: ROWS IN TBL_PROJECT_COMMENTS
||   RETURNED VALUE: NONE
/*

is
   /* retrieve comments/footnotes */
   cursor comments_cur (p_footnote_type_in in footnotes.footnote_type%type := 'P') is
      select *
	    from
		     (
             select lpl.limsdata_seq,
	                'TRUE' as return_record,
			        lpl.project_hbn,
			        nvl(custom_comment,'') as project_comment,
			        substr(footnotes.flags,1,1) as reportable,
			        substr(footnotes.flags,2,1) as print_text,
			        substr(footnotes.flags,3,1) as placement,
			        substr(footnotes.flags,4,1) as epa_flag,
			        footnotes.sort_item as srt1,
			        footnotes.sort_item as srt2,
			        '' as line_def
               from
		            limsdata_pull_list lpl,
			        footnotes
              where length(nvl(predefined,' ')) < 2
                and footnote_type = p_footnote_type_in
                and footnote_id = lpl.project_hbn
                and lpl.limsdata_seq = p_limsdata_seq_in
              union
             select lpl.limsdata_seq,
	                'TRUE' as return_record,
			        lpl.project_hbn,
			        nvl(app_text,'') as project_comment,
			        substr(footnotes.flags,1,1) as reportable,
			        substr(footnotes.flags,2,1) as print_text,
			        substr(footnotes.flags,3,1) as placement,
			        substr(footnotes.flags,4,1) as epa_flag,
			        footnotes.sort_item as srt1,
			        application_text.app_sort as srt2,
			        substr(application_text.flags,1,1) as line_def
               from
		            limsdata_pull_list lpl,
			        footnotes,
			        application_text
              where application_text.app_use = footnotes.predefined
                and footnote_type = p_footnote_type_in
                and footnote_id = lpl.project_hbn
                and lpl.limsdata_seq = p_limsdata_seq_in
			 ) wo_comments
--       WHERE project_comment IS NOT NULL
	order by project_hbn, reportable, srt1, srt2;

   /* records to hold current and previous info */
   comments_rec comments_cur%rowtype;
   old_comments_rec comments_cur%rowtype;

   /* var to hold aggregate comment */
   entire_comment tbl_project_comments.project_comment%type;

   /* var as trigger for new entry */
   old_wo tbl_project_comments.project_hbn%type;
   old_status tbl_project_comments.reportable%type;

begin

   old_wo := 0;
   old_status := '';

   open comments_cur('P');

   fetch comments_cur into comments_rec;
   while comments_cur%found
   loop
      if comments_rec.project_hbn <> old_wo or comments_rec.reportable <> old_status then
         begin
	        if old_wo <> 0 then
               insert into tbl_project_comments
	                         (
                              limsdata_seq,
 			                  return_record,
       			              project_hbn,
			                  project_comment,
			                  reportable,
			                  print_text,
			                  placement,
			                  epa_flag,
                              srt1
					         )
                    values
		                     (
                              old_comments_rec.limsdata_seq,
 			                  old_comments_rec.return_record,
			                  old_wo,
			                  entire_comment,
			                  old_comments_rec.reportable,
			                  old_comments_rec.print_text,
			                  old_comments_rec.placement,
			                  old_comments_rec.epa_flag,
                              old_comments_rec.srt1
					         );
		    end if;
		    old_wo := comments_rec.project_hbn;
			old_status := comments_rec.reportable;
		    entire_comment := comments_rec.project_comment;
		    old_comments_rec := comments_rec;
		 end;
	  else
         if comments_rec.line_def is null or comments_rec.line_def = '' or comments_rec.line_def = '.' then
            entire_comment := entire_comment || chr(13) || chr(10) || comments_rec.project_comment;
         else
		    if comments_rec.line_def = 'P' then
               entire_comment := entire_comment || chr(13) || chr(10) || chr(13) || chr(10) || chr(9) || comments_rec.project_comment;
            else
			   if comments_rec.line_def = '*' then
                  entire_comment := entire_comment || chr(160) ||ltrim(comments_rec.project_comment); -- fix for 1.1.1.1 added CHR(160) = &nbsp;
			   end if;
            end if;
         end if;
	  end if;
      fetch comments_cur into comments_rec;
   end loop;

   if old_wo <> 0 then
      insert into tbl_project_comments
                    (
                     limsdata_seq,
                     return_record,
                     project_hbn,
                     project_comment,
                     reportable,
                     print_text,
                     placement,
                     epa_flag,
                     srt1
                    )
             values
                    (
                     comments_rec.limsdata_seq,
                     comments_rec.return_record,
                     comments_rec.project_hbn,
                     entire_comment,
                     comments_rec.reportable,
                     comments_rec.print_text,
                     comments_rec.placement,
                     comments_rec.epa_flag,
                     comments_rec.srt1
                    );
   end if;

   commit;

   close comments_cur;

exception
  when no_data_found then
    null;
  when others then
    null;
end token_project_comments;