type calendar_tab is table of xx_bk_calendars ;

FUNCTION XX_BK_SP_LIST_CALENDAR(p_ProfileType IN VARCHAR2)  RETURN calendar_tab PIPELINED IS

-- declare variables to hold the values from the cursor
lc_Profile_Value VARCHAR2(18);
lc_fnd_profile VARCHAR2(18);
lc_open_quote VARCHAR2(100);

CURSOR cal_cursor IS
SELECT calendarid
,externalsystemid
,calendarcode
,description
,maxdays
,mindays
,mintime
,lastcreationdate
,historydays
FROM xx_bk_calendar;

BEGIN

IF (p_ProfileType = 'ALL') THEN

     FOR cal_rec in cal_cursor
      LOOP
         PIPE ROW (xx_bk_calendars(cal_rec.calendarid
         ,cal_rec.externalsystemid
         ,cal_rec.calendarcode
         ,cal_rec.description
         ,cal_rec.maxdays
         ,cal_rec.mindays
         ,cal_rec.mintime
         ,cal_rec.lastcreationdate
         ,cal_rec.historydays));
         EXIT WHEN cal_cursor%notfound;
      END LOOP;
ELSE IF (p_ProfileType = 'SLOT') THEN
      FOR cal_slot in cal_cursor
      LOOP
       lc_Profile_Value := concat('XX_BK_',cal_slot.calendarcode);
       lc_Profile_Value := rtrim(lc_Profile_Value);
       SELECT fnd_profile.value (lc_Profile_Value) INTO lc_fnd_profile           FROM dual;
       lc_open_quote := 'SELECT ( fnd_profile.value rtrim('''||lc_Profile_Value||''')) INTO lc_fnd_profile FROM dual';

      IF (lc_fnd_profile = 'SLOT') THEN
           PIPE ROW (xx_bk_calendars(cal_slot.calendarid
          ,cal_slot.externalsystemid
          ,cal_slot.calendarcode
          ,cal_slot.description
          ,cal_slot.maxdays
          ,cal_slot.mindays
          ,cal_slot.mintime
          ,cal_slot.lastcreationdate
          ,cal_slot.historydays));
      END IF;
      END LOOP;
ELSE
      FOR cal_full IN cal_cursor
      LOOP
      lc_Profile_Value := concat('XX_BK_',cal_full.calendarcode);
      lc_fnd_profile FROM dual;
      lc_Profile_Value := rtrim(lc_Profile_Value);
      SELECT fnd_profile.value (lc_Profile_Value) INTO lc_fnd_profile FROM dual;
      lc_open_quote := 'SELECT ( fnd_profile.value  rtrim('''||lc_Profile_Value||''')) INTO lc_fnd_profile FROM dual';
      IF (lc_fnd_profile = 'SLOT') OR (lc_fnd_profile = 'FULL') THEN
          PIPE ROW (xx_bk_calendars(cal_full.calendarid
          ,cal_full.externalsystemid
          ,cal_full.calendarcode
          ,cal_full.description
          ,cal_full.maxdays
          ,cal_full.mindays
          ,cal_full.mintime
          ,cal_full.lastcreationdate
          ,cal_full.historydays));
      END IF;
   END LOOP;
END IF;
END IF;
RETURN;
END XX_BK_SP_LIST_CALENDAR;

CREATE type xx_bk_calendars as object (
CALENDARID NUMBER
,EXTERNALSYSTEMID NUMBER
,CALENDARCODE VARCHAR2(12)
,DESCRIPTION VARCHAR2(30)
,MAXDAYS NUMBER
,MINDAYS NUMBER
,MINTIME DATE
,LASTCREATIONDATE DATE
,HISTORYDAYS NUMBER
);