CREATE OR REPLACE FUNCTION de_TempTMApplicationData (pnCaseKey INTEGER DEFAULT NULL)  RETURN CPAType_TempTMAppliData_Tbl  PIPELINED AS
  RELEASE_5_3_3848 CONSTANT BOOLEAN := FALSE;
  -----------------------------------------------------------------------------------
  -- Warning the order of columns is important because we do a PIPE ROW(t(i))
  -- ie. without declaring a TempTMApplicationData_Rec
  -----------------------------------------------------------------------------------

  CURSOR Cur IS
    SELECT CPAType_TempTMAppliData_Rec(
           T.CaseKey,
           T.ALPHABET,
           T.INTENT_TO_USE,
           T.ALLOWED_TO_LAPSE,
           T.WATCHING,
           T.IN_USE,
           T.RESERVE,
           T.RECLASSIFICATION,
           T.CHANGE_OF_ADDRESS,
           T.ALLOCATED,
           T.RENEWAL_IF_USED,
		   T.NUMBER_OF_FIGURES,
           T.COMMENTS_FOR_RENEWAL,
           T.DESCRIPTION,
           T.ORIGIN,
           T.LOGO_THUMBNAIL,
           T.LOGO,
           T.GOODSANDSERVICESINENGLISH,
           T.GOODSANDSERVICESINENGLISH4000,
           T.GOODSANDSERVICES,
           T.GOODSANDSERVICES4000,
           T.IRCaseKey,
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT FilingDate
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END ,--IRFilingDate,
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT FilingNumber
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END ,--IRFilingNumber,
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT RegistrationDate
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END ,--IRRegistrationDate,
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT PublicationDate
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END ,--IRPublicationDate,
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT NotificationDate
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END, --IRNotificationDate
           CASE
             WHEN T.IRCaseKey IS NOT NULL THEN
               (SELECT FirstUseDate
                  FROM TempDataExtCaseEvent
                 WHERE CaseKey= T.IRCaseKey)
           END --FirstUseDate
           )
      FROM
      (
    SELECT CaseKey,
           (SELECT ValuePickList          FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_ALPHAB                AND ParentKey=Cases.CaseKey) ALPHABET,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_INTENT_TO_USE         AND ParentKey=Cases.CaseKey) INTENT_TO_USE,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_ALLOWED_TO_LAPSE      AND ParentKey=Cases.CaseKey) ALLOWED_TO_LAPSE,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_WATCHING              AND ParentKey=Cases.CaseKey) WATCHING,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_IN_USE                AND ParentKey=Cases.CaseKey) IN_USE,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_RESERVE               AND ParentKey=Cases.CaseKey) RESERVE,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_RECLASSIFICATION      AND ParentKey=Cases.CaseKey) RECLASSIFICATION,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_CHANGE_OF_ADDRESS     AND ParentKey=Cases.CaseKey) CHANGE_OF_ADDRESS,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_ALLOCATED             AND ParentKey=Cases.CaseKey) ALLOCATED,
           (SELECT NVL(ValueBoolean, '0') FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_RENEWAL_IF_USED       AND ParentKey=Cases.CaseKey) RENEWAL_IF_USED,
           (SELECT ValueInteger 	      FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_NBRDRAWING            AND ParentKey=Cases.CaseKey) NUMBER_OF_FIGURES,
		   (SELECT ValueString            FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_COMMENTS_FOR_RENEWAL  AND ParentKey=Cases.CaseKey) COMMENTS_FOR_RENEWAL,
           (SELECT ValueString            FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_DESCRIPTION           AND ParentKey=Cases.CaseKey) DESCRIPTION,
           (SELECT ValueString            FROM GlobalAttribute WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND AttributeKey= CPAmemotechConstants.ATTRIBUTE_ORIGIN                AND ParentKey=Cases.CaseKey) ORIGIN,
           (SELECT GlobalMediaKey         FROM GlobalMedia     WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND MediaTypeKey= CPAmemotechConstants.GLOBALMEDIA_LOGO_THUMBNAIL      AND ParentKey=Cases.CaseKey AND ROWNUM=1) LOGO_THUMBNAIL,
           (SELECT GlobalMediaKey         FROM GlobalMedia     WHERE ParentTableName=CPAMemotechConstants.TABLE_CASES AND MediaTypeKey= CPAmemotechConstants.GLOBALMEDIA_LOGO                AND ParentKey=Cases.CaseKey AND ROWNUM=1) LOGO,
           CASE
           WHEN Cases.ClassificationType IS NULL THEN
             (SELECT CPAmemotechTools.BlobToClob(LongValue)
                FROM globalmedialob, GlobalMedia
               WHERE GlobalMedia.GlobalMediaKey = GlobalMediaLob.GlobalMediaKey
                 AND GlobalMedia.ParentTableName = CPAMemotechConstants.TABLE_CASES
                 AND GlobalMedia.MediaTypeKey = CPAmemotechConstants.GLOBALMEDIA_GOODSANDSERVICES
                 AND GlobalMedia.LanguageKey = CPAmemotechConstants.LANGUAGE_ENGLISH
                 AND GlobalMedia.ParentKey = Cases.CaseKey
                 AND ROWNUM=1)
           ELSE
             NULL
           END GOODSANDSERVICESINENGLISH,
           CASE
           WHEN Cases.ClassificationType IS NULL THEN
             (SELECT DBMS_LOB.SUBSTR(CPAmemotechTools.BlobToClob(LongValue),4000,1)
                FROM globalmedialob, GlobalMedia
               WHERE GlobalMedia.GlobalMediaKey = GlobalMediaLob.GlobalMediaKey
                 AND GlobalMedia.ParentTableName = CPAMemotechConstants.TABLE_CASES
                 AND GlobalMedia.MediaTypeKey = CPAmemotechConstants.GLOBALMEDIA_GOODSANDSERVICES
                 AND GlobalMedia.LanguageKey = CPAmemotechConstants.LANGUAGE_ENGLISH
                 AND GlobalMedia.ParentKey = Cases.CaseKey
                 AND ROWNUM=1)
           ELSE
            NULL
           END GOODSANDSERVICESINENGLISH4000,
           CASE
           WHEN Cases.ClassificationType IS NULL THEN
            (SELECT CPAmemotechTools.BlobToClob(LongValue)
               FROM globalmedialob, GlobalMedia
              WHERE GlobalMedia.GlobalMediaKey = GlobalMediaLob.GlobalMediaKey
                AND GlobalMedia.ParentTableName = CPAMemotechConstants.TABLE_CASES
                AND GlobalMedia.MediaTypeKey = CPAmemotechConstants.GLOBALMEDIA_GOODSANDSERVICES
                AND GlobalMedia.LanguageKey IS NULL
                AND GlobalMedia.ParentKey = Cases.CaseKey
                AND ROWNUM=1)
           ELSE
            NULL
           END GOODSANDSERVICES,
           CASE
           WHEN Cases.ClassificationType IS NULL THEN
            (SELECT DBMS_LOB.SUBSTR(CPAmemotechTools.BlobToClob(LongValue),4000,1)
               FROM globalmedialob, GlobalMedia
              WHERE GlobalMedia.GlobalMediaKey = GlobalMediaLob.GlobalMediaKey
                AND GlobalMedia.ParentTableName = CPAMemotechConstants.TABLE_CASES
                AND GlobalMedia.MediaTypeKey = CPAmemotechConstants.GLOBALMEDIA_GOODSANDSERVICES
                AND GlobalMedia.LanguageKey IS NULL
                AND GlobalMedia.ParentKey = Cases.CaseKey
                AND ROWNUM=1)
           ELSE
            NULL
           END GOODSANDSERVICES4000,
           CASE
             WHEN TableFilingType.RegionalProcedure= CPAmemotechConstants.REGIONALPROCEDURE_IR AND Cases.CountryKey= CPAmemotechConstants.COUNTRY_WO THEN
               Cases.CaseKey
             WHEN TableFilingType.RegionalProcedure= CPAmemotechConstants.REGIONALPROCEDURE_IR THEN
               (SELECT ProcCase.CaseKey
                  FROM Cases ProcCase, CaseCountry
                 WHERE ProcCase.CountryKey= CPAmemotechConstants.COUNTRY_WO
                   AND ProcCase.PropertyTypeKey= CPAmemotechConstants.PROPERTYTYPE_TRADEMARK
                   AND ProcCase.CaseTypeKey= CPAmemotechConstants.CASETYPE_PROPERTY
                   AND ProcCase.CaseKey= CaseCountry.CaseKey
                   AND CaseCountry.LocalCaseKey= Cases.CaseKey)
           END IRCaseKey
      FROM TableFilingType, Cases
     WHERE TableFilingType.FilingTypeKey= Cases.FilingTypeKey
       AND Cases.PropertyTypeKey= CPAmemotechConstants.PROPERTYTYPE_TRADEMARK
       AND Cases.CaseTypeKey= CPAmemotechConstants.CASETYPE_PROPERTY
       AND NVL(pnCaseKey, Cases.CaseKey) = Cases.CaseKey
     ) T;

  tblMain CPAType_TempTMAppliData_Tbl;
BEGIN
  OPEN Cur;
  LOOP
    FETCH Cur BULK COLLECT INTO tblMain LIMIT DataExtractorManagement.BULK_LIMIT;
    FOR nLoop IN 1..tblMain.COUNT LOOP
      PIPE ROW(tblMain(nLoop));
    END LOOP;
    EXIT WHEN CUR%NOTFOUND;
  END LOOP;
  CLOSE Cur;
  RETURN;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE PROGRAM_ERROR;
END;
/