CREATE OR REPLACE PACKAGE ADS.PKG_WATCHPOINT_INTEGRATION
as
/*
*************************************************************
   PACKAGE: PKG_WATCHPOINT_INTEGRATION
   **********************************************************
   This package contains stored procedures and functions that
   allow the WatchPoint application server to qeury the Xport
   database.
   **********************************************************
   [USED BY] WatchPoint
   **********************************************************
   [By Daniel Weiner - 7/22/2008]
   **********************************************************
   Change Log:
*************************************************************
*/

   TYPE XportSearchResultsCurType IS REF CURSOR ;
   TYPE XportSearchResultsRec IS RECORD(  rnum								   NUMBER,
   													PackageAsset_ID               VARCHAR2(20),
                                          PackageAsset_Name             VARCHAR2(100),
                                          PackageDescription            VARCHAR2(1024),
                                          PackageCreation_Date          DATE,
                                          PackageProduct                VARCHAR2(20),
                                          PackageMetadata_Spec_Version  VARCHAR2(50),
                                          PackageVersion_Major          NUMBER(6,0),
                                          PackageVersion_Minor          NUMBER(6,0),
                                          PackageVerb                   VARCHAR2(10),
                                          PackageProvider_Content_Tier  VARCHAR2(1024),
                                          PackageProvider               VARCHAR2(128),
                                          PackageProvider_ID            VARCHAR2(20),
                                          TitleProvider                 VARCHAR2(128),
                                          TitleProvider_ID              VARCHAR2(20),
                                          TitleProduct                  VARCHAR2(20),
                                          TitleAsset_ID                 VARCHAR2(20),
                                          TitleAsset_Name               VARCHAR2(100),
                                          TitleCreation_Date            DATE,
                                          TitleDescription              VARCHAR2(1024),
                                          TitleVerb                     VARCHAR2(10),
                                          TitleVersion_Major            NUMBER(6,0),
                                          TitleVersion_Minor            NUMBER(6,0),
                                          TitleTitle                    VARCHAR2(128),
                                          TitleSummary_Short            VARCHAR2(256),
                                          TitleDisplay_Run_Time         VARCHAR2(5),
                                          TitleRun_Time                 VARCHAR2(8),
                                          TitleRating                   VARCHAR2(1024),
                                          TitleBilling_ID               VARCHAR2(50),
                                          TitleTitle_Brief              VARCHAR2(19),
                                          TitlePreview_Period           NUMBER(5,0),
                                          TitleProvider_QA_Contact      VARCHAR2(128),
                                          TitleLicensing_Window_Start   VARCHAR2(19),
                                          TitleLicensing_Window_End     VARCHAR2(19),
                                          TitleTitle_Sort_Name          VARCHAR2(22),
                                          TitleSubscriber_View_Limit    VARCHAR2(1024),
                                          TitleYear                     NUMBER(5,0) ,
                                          TitleContract_Name            VARCHAR2(100) ,
                                          TitleBox_Office               NUMBER(20,2) ,
                                          TitleEpisode_Name             VARCHAR2(256) ,
                                          TitleEpisode_ID               VARCHAR2(256) ,
                                          TitleSummary_Medium           VARCHAR2(1024) ,
                                          TitleAdvisories               VARCHAR2(2096) ,
                                          TitleClosed_Captioning        VARCHAR2(2) ,
                                          TitleCountry_of_Origin        VARCHAR2(256) ,
                                          TitleActors                   VARCHAR2(2096) ,
                                          TitleActor_Display            VARCHAR2(1024) ,
                                          TitleWriter_Display           VARCHAR2(1024) ,
                                          TitleDirectors                VARCHAR2(2096) ,
                                          TitleProducers                VARCHAR2(2096) ,
                                          TitleCategory                 VARCHAR2(2096) ,
                                          TitleSeason_Primere           VARCHAR2(2) ,
                                          TitleSeason_Finale            VARCHAR2(2) ,
                                          TitleChapter                  VARCHAR2(2096),
                                          TitleDist_Royalty_Percent   	NUMBER(5,2) ,
                                          TitleDist_Royalty_Minumum   	NUMBER(13,3) ,
                                          TitleDist_Royalty_Flat_Rate 	NUMBER(13,3) ,
                                          TitleSuggested_Price          NUMBER(20,2),
                                          TitleDistrubuter_Name			VARCHAR2(128),
                                          TitleStudio                   VARCHAR2(256) ,
                                          TitleStudio_Royalty_Percent   NUMBER(5,2) ,
                                          TitleStudio_Royalty_Minimum   NUMBER(13,3) ,
                                          TitleStudio_Royalty_Flat_Rate NUMBER(13,3) ,
                                          TitleStudio_Name              VARCHAR2(128) ,
                                          TitleDisplay_As_Last_Chance   NUMBER(4,0),
                                          TitleDisplay_As_New           NUMBER(4,0),
                                          TitleHome_Video_Window        NUMBER(4,0),
                                          TitleAudience                 VARCHAR2(1024) ,
                                          TitleMSORating                VARCHAR2(1024) ,
                                          TitlePropogation_Priority     NUMBER(2,0),
                                          TitleMaximum_Viewing_Length   VARCHAR2(25), --MAX_VIEWING_TIME
                                          TitleGenre                    VARCHAR2(1024),
                                          TitleSummary_Long					VARCHAR2(2096),
                                          MovieProvider                 VARCHAR2(128),
                                          MovieProvider_ID              VARCHAR2(20),
                                          MovieProduct                  VARCHAR2(20),
                                          MovieAsset_ID                 VARCHAR2(20),
                                          MovieAsset_Name               VARCHAR2(100),
                                          MovieCreation_Date            DATE,
                                          MovieDescription              VARCHAR2(1024),
                                          MovieVersion_Major            NUMBER(6,0),
                                          MovieVersion_Minor            NUMBER(6,0),
                                          MovieAudio_Type               VARCHAR2(1024),
                                          MovieHDContent                VARCHAR2(1),
                                          MovieScreen_Format            VARCHAR2(256),
                                          MovieContent_FileSize         NUMBER(11,0),
                                          MovieContent_CheckSum         VARCHAR2(32),
                                          MovieContent                  VARCHAR2(1000),
                                          MovieVerb                     VARCHAR2(10),
                                          MovieLanguages                VARCHAR2(1024),
                                          MovieSubtitle_Languages       VARCHAR2(1024),
                                          MovieDubbed_Languages         VARCHAR2(1024),
                                          MovieCGMS_A                   VARCHAR2(1),
                                          MovieBit_Rate                 VARCHAR2(10),
                                          MovieCopy_Protection          VARCHAR2(2),
                                          MovieCopy_Protection_Verbose  VARCHAR2(2),
                                          MovieEncryption               VARCHAR2(2),
                                          MovieAnalog_ProtectionSystem VARCHAR2(1),
                                          MovieConstrained_Image_Trigger VARCHAR2(1),
                                          MovieEncryption_Mode_Indicator VARCHAR2(1),
                                          MovieViewing_Can_Be_Resumed   VARCHAR2(1),
                                          PosterProvider                VARCHAR2(128),
                                          PosterProvider_ID             VARCHAR2(20),
                                          PosterProduct                 VARCHAR2(20),
                                          PosterAsset_ID                VARCHAR2(20),
                                          PosterAsset_Name              VARCHAR2(100),
                                          PosterCreation_Date           DATE,
                                          PosterDescription             VARCHAR2(1024),
                                          PosterVersion_Major           NUMBER(6,0),
                                          PosterVersion_Minor           NUMBER(6,0),
                                          PosterImage_Aspect_Ratio      VARCHAR2(20),
                                          PosterContent_FileSize        NUMBER(11,0),
                                          PosterContent_CheckSum        VARCHAR2(32),
                                          PosterContent                 VARCHAR2(1000),
                                          PoseterVerb                   VARCHAR2(10),
                                          PreviewProvider               VARCHAR2(128),
                                          PreviewProvider_ID            VARCHAR2(20),
                                          PreviewProduct                VARCHAR2(20),
                                          PreviewAsset_ID               VARCHAR2(20),
                                          PreviewAsset_Name             VARCHAR2(100),
                                          PreviewCreation_Date          DATE,
                                          PreviewDescription            VARCHAR2(1024),
                                          PreviewVersion_Major          NUMBER(6,0),
                                          PreviewVersion_Minor          NUMBER(6,0),
                                          PreviewAudio_Type             VARCHAR2(1024),
                                          PreviewHDContent              VARCHAR2(1),
                                          PreviewScreen_Format          VARCHAR2(128),
                                          PreviewContent_FileSize       NUMBER(11,0),
                                          PreviewContent_CheckSum       VARCHAR2(32),
                                          PreviewContent                VARCHAR2(1000),
                                          PreviewVerb                   VARCHAR2(10),
                                          PreviewLanguages              VARCHAR2(1024),
                                          PreviewSubtitle_Languages     VARCHAR2(1024),
                                          PreviewDubbed_Languages       VARCHAR2(1024),
                                          PreviewBit_Rate               VARCHAR2(10),
                                          PreviewAudience               VARCHAR2(1024),
                                          PreviewMSORating              VARCHAR2(1024),
                                          PreviewRating                 VARCHAR2(1024),
                                          PreviewRun_Time               VARCHAR2(8),
                                          BoxCoverProvider              VARCHAR2(128),
                                          BoxCoverProvider_ID           VARCHAR2(20),
                                          BoxCoverProduct               VARCHAR2(20),
                                          BoxCoverAsset_ID              VARCHAR2(20),
                                          BoxCoverAsset_Name            VARCHAR2(100),
                                          BoxCoverCreation_Date         DATE,
                                          BoxCoverDescription           VARCHAR2(1024),
                                          BoxCoverVersion_Major         NUMBER(6,0),
                                          BoxCoverVersion_Minor         NUMBER(6,0),
                                          BoxCoverImage_Aspect_Ratio    VARCHAR2(20),
                                          BoxCoverContent_FileSize      NUMBER(11,0),
                                          BoxCoverContent_CheckSum      VARCHAR2(32),
                                          BoxCoverContent               VARCHAR2(1000),
                                          BoxCoverVerb                  VARCHAR2(10));

   PROCEDURE XportPackageSearch (AssetID_IN  IN VARCHAR2,
                                    ProviderID_IN        IN VARCHAR2,
                                    TitleBrief_IN        IN VARCHAR2,
                                    LicenseStartDateFrom_IN IN VARCHAR2,
                                    LicenseStartDateto_IN   IN VARCHAR2,
                                    LicenseEndDatefrom_IN   IN VARCHAR2,
                                    LicenseEndDateto_IN     IN VARCHAR2,
                                    ORDERBY_IN           IN VARCHAR2,
                                    ascORdesc				IN VARCHAR2,
                                    ROWNUMSTART_IN       IN NUMBER,
                                    TotalRows_in         IN NUMBER,
                                    searchResults        OUT XportSearchResultsCurType);
   
	PROCEDURE XportPackageSearchCNT (AssetID_IN  IN VARCHAR2,
                                    ProviderID_IN        IN VARCHAR2,
                                    TitleBrief_IN        IN VARCHAR2,
                                    LicenseStartDateFrom_IN IN VARCHAR2,
                                    LicenseStartDateto_IN   IN VARCHAR2,
                                    LicenseEndDatefrom_IN   IN VARCHAR2,
                                    LicenseEndDateto_IN     IN VARCHAR2,
                                    CNT        OUT NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY ADS.PKG_WATCHPOINT_INTEGRATION
AS
      PROCEDURE XportPackageSearch (AssetID_IN  IN VARCHAR2,
                                    ProviderID_IN        IN VARCHAR2,
                                    TitleBrief_IN        IN VARCHAR2,
                                    LicenseStartDateFrom_IN IN VARCHAR2,
                                    LicenseStartDateto_IN   IN VARCHAR2,
                                    LicenseEndDatefrom_IN   IN VARCHAR2,
                                    LicenseEndDateto_IN     IN VARCHAR2,
                                    ORDERBY_IN           IN VARCHAR2,
                                    ascORdesc				IN VARCHAR2,
                                    ROWNUMSTART_IN       IN NUMBER,
                                    TotalRows_IN         IN NUMBER,
                                    searchResults        OUT XportSearchResultsCurType) IS
               AssetID_VAL          VARCHAR2(255);
               ProviderID_VAL       VARCHAR2(255);
               TitleBrief_VAL       VARCHAR2(255);
               ROWNUMSTART_VAL      VARCHAR2(255);
               ROWNUMEND_VAL        VARCHAR2(255);
               ORDERBY_VAL				VARCHAR2(255);
      BEGIN
      	--SET NECESSARY VARIABLES
      	SELECT CASE WHEN AssetID_IN IS NULL THEN NULL ELSE '%'||UPPER(AssetID_IN)||'%' END INTO AssetID_VAL FROM DUAL;
			SELECT CASE WHEN ProviderID_IN IS NULL THEN NULL ELSE '%'||UPPER(ProviderID_IN)||'%' END INTO ProviderID_VAL FROM DUAL;
			SELECT CASE WHEN TitleBrief_IN IS NULL THEN NULL ELSE '%'||UPPER(TitleBrief_IN)||'%' END INTO TitleBrief_VAL FROM DUAL;
			SELECT CAST(ROWNUMSTART_IN AS VARCHAR(20)) INTO ROWNUMSTART_VAL FROM DUAL;
			SELECT CAST((ROWNUMSTART_IN+TotalRows_IN-1) AS VARCHAR(20)) INTO ROWNUMEND_VAL FROM DUAL;
			
			SELECT CASE WHEN ORDERBY_IN IS NULL THEN 'p.PACKAGE_ID ASC' ELSE ORDERBY_IN||' '||(
						CASE WHEN UPPER(ascORdesc) <> 'ASC' THEN 'DESC' ELSE 'ASC' END)||', p.PACKAGE_ID ASC' END INTO ORDERBY_VAL FROM DUAL;
			--RUN THE BLOODY QUERY
         OPEN searchResults FOR '
        	SELECT * FROM
         (SELECT ROWNUM rnum, a.*  FROM(
         SELECT  p.ASSETID                      AS PackageAsset_ID,
                 p.Name                         AS PackageAsset_Name,
                 p.Description                  AS PackageDescription,
                 p.CREATION_DATE                AS PackageCreation_Date,
                 p.App                          AS PackageProduct,
                 p.METADATASPECVERSION          AS PackageMetadata_Spec_Version,
                 TRUNC(p.VERSION) AS PackageVersion_Major,
                 TRUNC((p.VERSION - TRUNC(p.VERSION))*10) AS PackageVersion_Minor,
                 p.VERB                         AS PackageVerb,
                 p.CONTENTTIERNAME              AS PackageProvider_Content_Tier,
                 pro.Name                       AS PackageProvider,
                 pro.UNIQUEPROVIDER_ID          AS PackageProvider_ID,
                 pro.Name                       AS TitleProvider,
                 pro.UNIQUEPROVIDER_ID          AS TitleProvider_ID,
                 p.App                          AS TitleProduct,
                 t.ASSETID                      AS TitleAsset_ID,
                 t.NAME                         AS TitleAsset_Name,
                 t.CREATION_DATE                AS TitleCreation_Date,
                 t.DESCRIPTION                  AS TitleDescription,
                 t.VERB                         AS TitleVerb,
                 TRUNC(t.VERSION) AS TitleVersion_Major,
                 TRUNC((t.VERSION - TRUNC(t.VERSION))*10) AS TitleVersion_Minor,
                 t.TITLE                        AS TitleTitle,
                 t.SUMMARY_SHORT                AS TitleSummary_Short,
                 t.DISPLAY_RUN_TIME             AS TitleDisplay_Run_Time,
                 t.RUN_TIME                     AS TitleRun_Time,
                 t.RATING                       AS TitleRating,
                 t.Provider_Asset_Id            AS TitleBilling_ID,
                 t.TITLE_BRIEF                  AS TitleTitle_Brief,
                 t.PREVIEW_PERIOD               AS TitlePreview_Period,
                 t.QA_CONTACT                   AS TitleProvider_QA_Contact,
                 t.LICENSING_WINDOW_START       AS TitleLicensing_Window_Start,
                 t.LICENSING_WINDOW_END         AS TitleLicensing_Window_End,
                 t.TITLE_SORT_NAME              AS TitleTitle_Sort_Name,
                 t.SUBSCRIBER_VIEW_LIMIT        AS TitleSubscriber_View_Limit,
                 t.YEAR                         AS TitleYear,
                 t.CONTRACT_NAME                AS TitleContract_Name,
                 t.BOX_OFFICE                   AS TitleBox_Office,
                 t.EPISODE_NAME                 AS TitleEpisode_Name,
                 t.EPISODE_ID                   AS TitleEpisode_ID,
                 t.SUMMARY_MEDIUM               AS TitleSummary_Medium,
                 t.ADVISORIES                   AS TitleAdvisories,
                 t.CLOSED_CAPTIONING            AS TitleClosed_Captioning,
                 t.COUNTRY_OF_ORIGIN            AS TitleCountry_of_Origin,
                 t.ACTORS_LIST                  AS TitleActors,
                 t.ACTORS                       AS TitleActor_Display,
                 t.WRITERS                      AS TitleWriter_Display,
                 t.DIRECTORS_LIST               AS TitleDirectors,
                 t.PRODUCERS_LIST               AS TitleProducers,
                 t.CATEGORY                     AS TitleCategory,
                 t.SEASON_PRIMERE               AS TitleSeason_Primere,
                 t.SEASON_FINALE                AS TitleSeason_Finale,
                 t.CHAPTER                      AS TitleChapter,
                 t.DISTRIBUTER_ROYALTY_PERCENT  AS TitleDist_Royalty_Percent,
                 t.DISTRIBUTER_ROYALTY_MINIMUM  AS TitleDist_Royalty_Minumum,
                 t.DISTRIBUTER_ROYALTY_FLAT_RATE AS TitleDist_Royalty_Flat_Rate,
                 t.SUGGESTED_PRICE              AS TitleSuggested_Price,
                 t.DISTRIBUTER_NAME             AS TitleDistrubuter_Name,
                 t.STUDIO                       AS TitleStudio,
                 t.STUDIO_ROYALTY_PERCENT       AS TitleStudio_Royalty_Percent,
                 t.STUDIO_ROYALTY_MINIMUM       AS TitleStudio_Royalty_Minimum,
                 t.STUDIO_ROYALTY_FLAT_RATE     AS TitleStudio_Royalty_Flat_Rate,
                 t.STUDIO_NAME                  AS TitleStudio_Name,
                 t.DISPLAY_AS_LAST_CHANCE       AS TitleDisplay_As_Last_Chance,
                 t.DISPLAY_AS_NEW               AS TitleDisplay_As_New,
                 t.HOME_VIDEO_WINDOW            AS TitleHome_Video_Window,
                 t.AUDIENCE                     AS TitleAudience,
                 t.MSORATING                    AS TitleMSORating,
                 t.PROPOGATION_PRIORITY         AS TitlePropogation_Priority,
                 t.MAX_VIEWING_TIME             AS TitleMaximum_Viewing_Length, --MAX_VIEWING_TIME
                 t.GENRE                        AS TitleGenre,
                 t.SUMMARY_LONG1						AS TitleSummary_Long,
                 pro.Name                       AS MovieProvider,
                 pro.UNIQUEPROVIDER_ID          AS MovieProvider_ID,
                 p.App                          AS MovieProduct,
                 m.ASSETID                      AS MovieAsset_ID,
                 m.NAME                         AS MovieAsset_Name,
                 m.CREATION_DATE                AS MovieCreation_Date,
                 m.DESCRIPTION                  AS MovieDescription,
                 TRUNC(m.VERSION) AS MovieVersion_Major,
                 TRUNC((m.VERSION - TRUNC(m.VERSION))*10) AS MovieVersion_Minor,
                 m.AUDIO_TYPE                   AS MovieAudio_Type,
                 m.HDCONTENT                    AS MovieHDContent,
                 m.SCREEN_FORMAT                AS MovieScreen_Format,
                 m.CONTENT_FILE_SIZE            AS MovieContent_FileSize,
                 m.CHECK_SUM                    AS MovieContent_CheckSum,
                 m.ExportedFileName             AS MovieContent,
                 m.Verb                         AS MovieVerb,
                 m.LANGUAGES                    AS MovieLanguages,
                 m.SUBTITLE_LANGUAGES           AS MovieSubtitle_Languages,
                 m.DUBBED_LANGUAGES             AS MovieDubbed_Languages,
                 m.CGMS_A                       AS MovieCGMS_A,
                 m.BIT_RATE                     AS MovieBit_Rate,
                 m.COPY_PROTECTION              AS MovieCopy_Protection,
                 m.COPY_PROTECTION_VERBOSE      AS MovieCopy_Protection_Verbose,
                 m.ENCRYPTION                   AS MovieEncryption,
                 m.ANALOG_PROTECTION_SYSTEM     AS MovieAnalog_ProtectionSystem,
                 m.CONSTRAINED_IMAGE_TRIGGER    AS MovieConstrained_Image_Trigger,
                 m.ENCRYPTION_MODE_INDICATOR    AS MovieEncryption_Mode_Indicator,
                 m.VIEWING_CAN_BE_RESUMED       AS MovieViewing_Can_Be_Resumed,
                 pro.Name                       AS PosterProvider,
                 pro.UNIQUEPROVIDER_ID          AS PosterProvider_ID,
                 p.App                          AS PosterProduct,
                 post.ASSETID                   AS PosterAsset_ID,
                 post.Name                      AS PosterAsset_Name,
                 post.CREATION_DATE             AS PosterCreation_Date,
                 post.DESCRIPTION               AS PosterDescription,
                 TRUNC(post.VERSION) AS PosterVersion_Major,
                 TRUNC((post.VERSION - TRUNC(post.VERSION))*10) AS PosterVersion_Minor,
                 post.IMAGE_ASPECT_RATIO        AS PosterImage_Aspect_Ratio,
                 post.CONTENT_FILE_SIZE         AS PosterContent_FileSize,
                 post.CHECK_SUM                 AS PosterContent_CheckSum,
                 post.EXPORTEDFILENAME          AS PosterContent,
                 post.VERB                      AS PoseterVerb,
                 pro.Name                       AS PreviewProvider,
                 pro.UNIQUEPROVIDER_ID          AS PreviewProvider_ID,
                 p.App                          AS PreviewProduct,
                 pre.ASSETID                    AS PreviewAsset_ID,
                 pre.NAME                       AS PreviewAsset_Name,
                 pre.CREATION_DATE              AS PreviewCreation_Date,
                 pre.DESCRIPTION                AS PreviewDescription,
                 TRUNC(pre.VERSION) AS PreviewVersion_Major,
                 TRUNC((pre.VERSION - TRUNC(pre.VERSION))*10) AS PreviewVersion_Minor,
                 pre.AUDIO_TYPE                 AS PreviewAudio_Type,
                 pre.HDCONTENT                  AS PreviewHDContent,
                 pre.SCREEN_FORMAT              AS PreviewScreen_Format,
                 pre.CONTENT_FILE_SIZE          AS PreviewContent_FileSize,
                 pre.CHECK_SUM                  AS PreviewContent_CheckSum,
                 pre.ExportedFileName           AS PreviewContent,
                 pre.VERB                       AS PreviewVerb,
                 pre.LANGUAGES                  AS PreviewLanguages,
                 pre.SUBTITLE_LANGUAGES         AS PreviewSubtitle_Languages,
                 pre.DUBBED_LANGUAGES           AS PreviewDubbed_Languages,
                 pre.BIT_RATE                   AS PreviewBit_Rate,
                 pre.AUDIENCE                   AS PreviewAudience,
                 pre.MSORATING                  AS PreviewMSORating,
                 pre.RATING                     AS PreviewRating,
                 pre.RUN_TIME                   AS PreviewRun_Time,
                 pro.Name                       AS BoxCoverProvider,
                 pro.UNIQUEPROVIDER_ID          AS BoxCoverProvider_ID,
                 p.App                          AS BoxCoverProduct,
                 box.ASSETID                    AS BoxCoverAsset_ID,
                 box.Name                       AS BoxCoverAsset_Name,
                 box.CREATION_DATE              AS BoxCoverCreation_Date,
                 box.DESCRIPTION                AS BoxCoverDescription,
                 TRUNC(box.VERSION) AS BoxCoverVersion_Major,
                 TRUNC((box.VERSION  - TRUNC(box.VERSION))*10) AS BoxCoverVersion_Minor,
                 box.IMAGE_ASPECT_RATIO         AS BoxCoverImage_Aspect_Ratio,
                 box.CONTENT_FILE_SIZE          AS BoxCoverContent_FileSize,
                 box.CHECK_SUM                  AS BoxCoverContent_CheckSum,
                 box.ExportedFileName           AS BoxCoverContent,
                 box.VERB                       AS BoxCoverVerb
         FROM    PACKAGES p
         JOIN    Providers pro
         ON(     p.PROVIDER_ID = pro.PROVIDER_ID)
         LEFT JOIN Title t
         ON(     p.Package_Id = t.Package_Id)
         LEFT JOIN Movie m
         ON(     p.Package_Id = m.Package_Id)
         LEFT JOIN Poster post
         ON(     p.Package_Id = post.Package_Id)
         LEFT JOIN Preview pre
         ON(     p.Package_Id = pre.Package_Id)
         LEFT JOIN Boxcover box
         ON(     p.Package_Id = box.Package_Id)
         WHERE   p.ACTIVE = 1
         AND     ( :ASSETID_BV IS NULL
                  OR UPPER(p.ASSETID) LIKE :ASSETID_BV
                  OR UPPER(t.ASSETID) LIKE :ASSETID_BV
                  OR UPPER(m.ASSETID) LIKE :ASSETID_BV	
                  OR UPPER(post.ASSETID) LIKE :ASSETID_BV
                  OR UPPER(pre.ASSETID) LIKE :ASSETID_BV
                  OR UPPER(box.ASSETID) LIKE :ASSETID_BV)
         AND     (:ProviderID_BV IS NULL OR UPPER(pro.UNIQUEPROVIDER_ID) LIKE :ProviderID_BV)
         AND     (:TitleBrief_BV IS NULL OR UPPER(t.TITLE_BRIEF) LIKE :TitleBrief_BV)
         AND     (:LicenseStartDateFrom_BV IS NULL OR t.LICENSING_WINDOW_START >= :LicenseStartDateFrom_BV)
         AND     (:LicenseStartDateTo_BV IS NULL   OR t.LICENSING_WINDOW_START <= :LicenseStartDateTo_BV)
         AND     (:LicenseENDDateFrom_BV IS NULL   OR t.LICENSING_WINDOW_END >= :LicenseENDDateFrom_BV)
         AND     (:LicenseENDDateTo_BV IS NULL     OR t.LICENSING_WINDOW_END <= :LicenseENDDateTo_BV)
         AND		p.METADATASPECVERSION = ''CableLabsVOD1.1''
         ORDER BY '||ORDERBY_VAL||') a  WHERE ROWNUM  <= :ROWNUMEND_BV)
         WHERE rnum >= :ROWNUMSTART_BV'
         USING AssetID_VAL, AssetID_VAL, AssetID_VAL, AssetID_VAL, AssetID_VAL, AssetID_VAL, AssetID_VAL,
         		ProviderID_VAL, ProviderID_VAL,
               TitleBrief_VAL, TitleBrief_VAL,
               LicenseStartDateFrom_IN, LicenseStartDateFrom_IN,
	            LicenseStartDateto_IN, LicenseStartDateto_IN,
	            LicenseEndDatefrom_IN, LicenseEndDatefrom_IN,
	            LicenseEndDateto_IN, LicenseEndDateto_IN,
	            ROWNUMEND_VAL, ROWNUMSTART_VAL; 
         
      END XportPackageSearch;
      
      
      PROCEDURE XportPackageSearchCNT (AssetID_IN  IN VARCHAR2,
                                    ProviderID_IN        IN VARCHAR2,
                                    TitleBrief_IN        IN VARCHAR2,
                                    LicenseStartDateFrom_IN IN VARCHAR2,
                                    LicenseStartDateto_IN   IN VARCHAR2,
                                    LicenseEndDatefrom_IN   IN VARCHAR2,
                                    LicenseEndDateto_IN     IN VARCHAR2,
                                    CNT        OUT NUMBER) IS
					AssetID_VAL          VARCHAR2(255);
               ProviderID_VAL       VARCHAR2(255);
               TitleBrief_VAL       VARCHAR2(255);
		BEGIN
			SELECT CASE WHEN AssetID_IN IS NULL THEN NULL ELSE '%'||UPPER(AssetID_IN)||'%' END INTO AssetID_VAL FROM DUAL;
			SELECT CASE WHEN ProviderID_IN IS NULL THEN NULL ELSE '%'||UPPER(ProviderID_IN)||'%' END INTO ProviderID_VAL FROM DUAL;
			SELECT CASE WHEN TitleBrief_IN IS NULL THEN NULL ELSE '%'||UPPER(TitleBrief_IN)||'%' END INTO TitleBrief_VAL FROM DUAL;
			SELECT  COUNT(*)
			INTO    CNT
         FROM    PACKAGES p
         JOIN    Providers pro
         ON(     p.PROVIDER_ID = pro.PROVIDER_ID)
         LEFT JOIN Title t
         ON(     p.Package_Id = t.Package_Id)
         LEFT JOIN Movie m
         ON(     p.Package_Id = m.Package_Id)
         LEFT JOIN Poster post
         ON(     p.Package_Id = post.Package_Id)
         LEFT JOIN Preview pre
         ON(     p.Package_Id = pre.Package_Id)
         LEFT JOIN Boxcover box
         ON(     p.Package_Id = box.Package_Id)
         WHERE   p.ACTIVE = 1
         AND     ( AssetID_VAL IS NULL 
                  OR UPPER(p.ASSETID) LIKE AssetID_VAL
                  OR UPPER(t.ASSETID) LIKE AssetID_VAL
                  OR UPPER(m.ASSETID) LIKE AssetID_VAL
                  OR UPPER(post.ASSETID) LIKE AssetID_VAL
                  OR UPPER(pre.ASSETID) LIKE AssetID_VAL
                  OR UPPER(box.ASSETID) LIKE AssetID_VAL)
         AND     (ProviderID_VAL IS NULL OR UPPER(pro.UNIQUEPROVIDER_ID) LIKE ProviderID_VAL)
         AND     (TitleBrief_VAL IS NULL OR UPPER(t.TITLE_BRIEF) LIKE TitleBrief_VAL)
         AND     (LicenseStartDateFrom_IN IS NULL OR t.LICENSING_WINDOW_START >= LicenseStartDateFrom_IN)
         AND     (LicenseStartDateto_IN IS NULL   OR t.LICENSING_WINDOW_START <= LicenseStartDateto_IN)
         AND     (LicenseEndDatefrom_IN IS NULL   OR t.LICENSING_WINDOW_END >= LicenseEndDatefrom_IN)
         AND     (LicenseEndDateto_IN IS NULL     OR t.LICENSING_WINDOW_END <= LicenseEndDateto_IN)
         AND		p.METADATASPECVERSION = 'CableLabsVOD1.1';
		END;
      
END;
/
