release  10 . 5 ;
datawindow(units= 0  timer_interval= 0  color= 1073741824  processing= 1  HTMLDW=no print.printername="" print.documentname="" print.orientation =  0  print.margin.left =  110  print.margin.right =  110  print.margin.top =  96  print.margin.bottom =  96  print.paper.source =  0  print.paper.size =  0  print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.preview.outline=yes hidegrayline=no grid.lines= 0  )
header(height= 120  color="536870912" )
summary(height= 0  color="536870912" )
footer(height= 0  color="536870912" )
detail(height= 80  color="536870912" )
table(column=(type=char( 4000 ) updatewhereclause=yes name=cf_deviation_number_list dbname="CF_DEVIATION_NUMBER_LIST" )
 retrieve="SELECT  getdeviationnumbervic(vic_tabcmp.cmpprog)  cf_deviation_number_list
FROM vic_tabcmp
WHERE vic_tabcmp.cmpdpt = :al_departments" arguments=(("al_departments", number)) )
text(band=header alignment="0" text="Cf Deviation Number List" border="0" color="33554432" x="5" y="4" height="76" width="1847" html.valueishtml="0"  name=cf_deviation_number_list_t visible="1"  font.face="Tahoma" font.height="-12" font.weight="400"  font.family="2" font.pitch="2" font.charset="204" background.mode="2" background.color="1073741824" )
column(band=detail id= 1  alignment="0" tabsequence= 32766  border="0" color="33554432" x="9" y="0" height="76" width="1842" format="[general]" html.valueishtml="0"  name=cf_deviation_number_list visible="1" edit.limit= 0  edit.case=any edit.focusrectangle=no edit.autoselect=no  font.face="Tahoma" font.height="-12" font.weight="400"  font.family="2" font.pitch="2" font.charset="204" background.mode="2" background.color="1073741824" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" pagingmethod= 0  generatedddwframes="1" )
xhtmlgen() cssgen(sessionspecific="0" )
xmlgen(inline="0" )
xsltgen()
jsgen()
export.xml(headgroups="1" includewhitespace="0" metadatatype= 0  savemetadata= 0  )
import.xml()
export.pdf(method= 0  distill.custompostscript="0" xslfop.print="0" )
export.xhtml()

CREATE OR REPLACE FUNCTION getdeviationnumbervic(
p_sample_id IN vic_tabcmp.cmpprog%TYPE,   p_separator IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS

 CURSOR cur_devnum IS
SELECT devnum
FROM vic_tabdev,
  vic_tabdevcmp
WHERE vic_tabdevcmp.cmpprog = p_sample_id
 AND vic_tabdevcmp.devprog = vic_tabdev.devprog
ORDER BY devnum ASC;

v_string VARCHAR2( 3000 ) := NULL;
v_separator VARCHAR2( 10 ) := NULL;
v_count NUMBER( 9 ) :=  0 ;

BEGIN

  -- Define a default separator if the one provided if empty or NULL

  IF LTRIM(RTRIM(nvl(p_separator,   ''))) IS
  NULL THEN
    v_separator := ',';
  ELSE
    v_separator := p_separator;
  END IF;

  -- Browse the deviation numbers
  FOR v_devnum IN cur_devnum
  LOOP

    IF v_devnum.devnum IS NOT NULL THEN
      v_string := v_string || v_devnum.devnum || v_separator || ' ';
    END IF;

    v_count := v_count +  1 ;
  END LOOP;

  IF v_count >  0  THEN

    IF v_string IS NOT NULL THEN

      -- Remove any spaces
      v_string := LTRIM(RTRIM(v_string));

      -- Remove last separator        
      v_string := SUBSTR(v_string,    1 ,   LENGTH(v_string) -LENGTH(v_separator));
    END IF;

    v_string := '*Caused deviations (' || v_count || '): ' || v_string;

  END IF;

  RETURN v_string;

EXCEPTION
WHEN others THEN
  DBMS_OUTPUT.PUT_LINE('Exception in GetDeviationNumberVic: ' || SQLCODE || ' - ' || sqlerrm);
  RAISE;

END getdeviationnumbervic;