create or replace procedure 
C_WS01_OUTBOUND_WAVEINFO(wave_number IN VARCHAR2, result_string OUT CLOB)
as

v_QTY_EXPECTED      ALLOC_INVN_DTL.QTY_ALLOC%TYPE;
v_WAVE_DESC         SHIP_WAVE_PARM.WAVE_DESC%TYPE;
v_SPCL_INSRT_1      SHIP_WAVE_PARM.SPL_INSTR_CODE_1%TYPE;
v_SPCL_INSRT_2      SHIP_WAVE_PARM.SPL_INSTR_CODE_2%TYPE;
prevFacility		VARCHAR2(16);
v_ErrMsg  			Msg_Log.Msg%TYPE;
V_BUFFER_STRING     CLOB;

BEGIN
	result_string:= NULL;
	V_BUFFER_STRING:= NULL;
	prevFacility:= 'EMPTYSTRING';
	
	--WCS_SORTING_WAVE PART
	V_BUFFER_STRING := 'wave|'||wave_number;
	
	--dbms_output.put_line('Flag 1 '||sysdate);
	SELECT SUM(ORDER_QTY) INTO v_QTY_EXPECTED from ORDER_LINE_ITEM where WAVE_NBR = wave_number AND REFERENCE_ORDER_ID IS NOT NULL 
	AND REFERENCE_LINE_ITEM_ID IS NOT NULL;
	V_BUFFER_STRING := V_BUFFER_STRING||'^'||'quantityExpected|'||v_QTY_EXPECTED;
	
	SELECT DISTINCT WAVE_DESC INTO v_WAVE_DESC FROM SHIP_WAVE_PARM WHERE SHIP_WAVE_NBR = wave_number;
		
	SELECT DISTINCT SPL_INSTR_CODE_1, SPL_INSTR_CODE_2 INTO v_SPCL_INSRT_1, v_SPCL_INSRT_2
		FROM WAVE_PARM WHERE WAVE_NBR = wave_number;
		V_BUFFER_STRING := V_BUFFER_STRING ||'^'||'Description|'||v_WAVE_DESC;
		V_BUFFER_STRING := V_BUFFER_STRING ||'^'||'waveType|'||v_SPCL_INSRT_2;
		V_BUFFER_STRING := V_BUFFER_STRING ||'^'||'idPlanTri|'||v_SPCL_INSRT_1;
		
	--dbms_output.put_line('Flag 2 '||sysdate);
	BEGIN
	INSERT INTO C_TEMP_WM07_WAVE_DATA(TEMP_ORDER_QTY, TEMP_ITEM_ID, TEMP_PARENT_ORDER_ID, TEMP_ITEM_NAME, TEMP_D_FACILITY_ID, TEMP_D_FACILITY_ALIAS_ID, TEMP_FACILITY_NAME, TEMP_WAVE_NBR)
    SELECT SUM(OLIT.ORDER_QTY), OLIT.ITEM_ID, ORDE.PARENT_ORDER_ID, ICBO.ITEM_NAME, ORDE.D_FACILITY_ID, ORDE.D_FACILITY_ALIAS_ID, FACALIAS.FACILITY_NAME,
	  wave_number
      FROM ORDERS ORDE, ORDER_LINE_ITEM OLIT, ITEM_CBO ICBO, FACILITY_ALIAS FACALIAS
     WHERE ORDE.ORDER_ID = OLIT.ORDER_ID
       AND OLIT.WAVE_NBR = wave_number
	   AND ORDE.IS_ORIGINAL_ORDER = '1'
       AND OLIT.REFERENCE_ORDER_ID IS NOT NULL
       AND OLIT.REFERENCE_LINE_ITEM_ID IS NOT NULL
	   AND ICBO.ITEM_ID = OLIT.ITEM_ID
	   AND ORDE.D_FACILITY_ALIAS_ID = FACALIAS.FACILITY_ALIAS_ID
	   GROUP BY OLIT.ITEM_ID, ORDE.PARENT_ORDER_ID, ICBO.ITEM_NAME, ORDE.D_FACILITY_ID, ORDE.D_FACILITY_ALIAS_ID, FACALIAS.FACILITY_NAME;
	exception when no_data_found then 
		result_string := result_string ||'^'||'';
	END;

	dbms_output.put_line('Before of Loop '||sysdate);
	
	BEGIN
		SELECT clobagg('^' || 'identifier1|' || TEMP_D_FACILITY_ALIAS_ID || '^' || 'description|' || TEMP_FACILITY_NAME ||  PER_FACILITY_ORDER) 
		INTO result_string FROM
		(SELECT TEMP_FACILITY_NAME, TEMP_D_FACILITY_ALIAS_ID,
		clobagg('^'||'wmsNum|'||TEMP_WAVE_NBR||TEMP_D_FACILITY_ID||TEMP_ITEM_NAME|| '^'|| 'sortingIdentifier1|'||TEMP_ITEM_NAME||'^'||
		'idProduct|'||TEMP_ITEM_NAME||'^'||'quantity|'||TEMP_ORDER_QTY) PER_FACILITY_ORDER
		FROM   C_TEMP_WM07_WAVE_DATA where TEMP_WAVE_NBR = wave_number
		GROUP BY TEMP_FACILITY_NAME, TEMP_D_FACILITY_ALIAS_ID, TEMP_ITEM_NAME);
	
		exception when no_data_found then 
		result_string := result_string ||'^'||'';
	END;
	
	result_string := V_BUFFER_STRING||result_string;
	DELETE FROM C_TEMP_WM07_WAVE_DATA;
	
	dbms_output.put_line('Out of Loop '||sysdate);
	exception
  when others then
    -- p_rc := 1;
    rollback;
    v_ErrMsg := 'Step' || ' - error during MM16: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;
    RAISE_APPLICATION_ERROR(-20010,
                            'ERAM_MOD Failed ' || CHR(10) ||
                            v_ErrMsg || SQLERRM);
END;
/