-- /packages/press/sql/press-create.sql -- -- @author sarah@arsdigita.com -- @author stefan@arsdigita.com -- @author ron@arsdigita.com -- -- @created 2000-11-15 -- -- $Id: upgrade-4.0.1-5.0d1.sql,v 1.2 2018/04/04 18:51:56 hectorr Exp $ -- *** PACKAGE PRESS, plsql to create content_item *** create or replace package press as function new ( name in cr_items.name%TYPE, publication_name in cr_press.publication_name%TYPE, publication_link in cr_press.publication_link%TYPE default null, publication_date in cr_press.publication_date%TYPE, publication_date_desc in cr_press.publication_date_desc%TYPE default null, article_link in cr_press.article_link%TYPE default null, article_pages in cr_press.article_pages%TYPE default null, article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, approval_user in cr_press.approval_user%TYPE default null, approval_date in cr_press.approval_date%TYPE default null, approval_ip in cr_press.approval_ip%TYPE default null, release_date in cr_press.release_date%TYPE default null, archive_date in cr_press.archive_date%TYPE default null, package_id in cr_press.package_id%TYPE default null, parent_id in acs_objects.context_id%TYPE default null, item_id in cr_items.item_id%TYPE default null, locale in cr_items.locale%TYPE default null, item_subtype in acs_object_types.object_type%TYPE default 'content_item', content_type in acs_object_types.object_type%TYPE default 'press', title in cr_revisions.title%TYPE default null, description in cr_revisions.description%TYPE default null, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', template_id in press_templates.template_id%TYPE default 1, nls_language in cr_revisions.nls_language%TYPE default null, text in varchar2 default null, relation_tag in cr_child_rels.relation_tag%TYPE default null, is_live_p in varchar2 default 'f', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null ) return cr_press.press_id%TYPE; procedure del ( item_id in cr_items.item_id%TYPE ); procedure make_permanent ( item_id in cr_items.item_id%TYPE ); procedure archive ( item_id in cr_items.item_id%TYPE, archive_date in cr_press.archive_date%TYPE default sysdate ); -- administrative procs procedure approve ( press_id in cr_press.press_id%TYPE, approve_p in varchar2 default 't' ); procedure approve_release( revision_id in cr_revisions.revision_id%TYPE, release_date in cr_press.release_date%TYPE, archive_date in cr_press.archive_date%TYPE ); procedure set_active_revision( revision_id in cr_revisions.revision_id%TYPE ); function is_live ( press_id in cr_press.press_id%TYPE ) return varchar; function status ( press_id in cr_press.press_id%TYPE ) return varchar; end press; / show errors create or replace package body press as function new ( name in cr_items.name%TYPE, publication_name in cr_press.publication_name%TYPE, publication_link in cr_press.publication_link%TYPE default null, publication_date in cr_press.publication_date%TYPE, publication_date_desc in cr_press.publication_date_desc%TYPE default null, article_link in cr_press.article_link%TYPE default null, article_pages in cr_press.article_pages%TYPE default null, article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, approval_user in cr_press.approval_user%TYPE default null, approval_date in cr_press.approval_date%TYPE default null, approval_ip in cr_press.approval_ip%TYPE default null, release_date in cr_press.release_date%TYPE default null, archive_date in cr_press.archive_date%TYPE default null, package_id in cr_press.package_id%TYPE default null, parent_id in acs_objects.context_id%TYPE default null, item_id in cr_items.item_id%TYPE default null, locale in cr_items.locale%TYPE default null, item_subtype in acs_object_types.object_type%TYPE default 'content_item', content_type in acs_object_types.object_type%TYPE default 'press', title in cr_revisions.title%TYPE default null, description in cr_revisions.description%TYPE default null, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', template_id in press_templates.template_id%TYPE default 1, nls_language in cr_revisions.nls_language%TYPE default null, text in varchar2 default null, relation_tag in cr_child_rels.relation_tag%TYPE default null, is_live_p in varchar2 default 'f', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_ip in acs_objects.creation_ip%TYPE default null, creation_user in acs_objects.creation_user%TYPE default null ) return cr_press.press_id%TYPE is v_press_id integer; v_item_id integer; v_revision_id integer; begin v_item_id := content_item.new( name => name, parent_id => parent_id, item_id => item_id, locale => locale, item_subtype => item_subtype, content_type => content_type, mime_type => mime_type, nls_language => nls_language, relation_tag => relation_tag, creation_date => creation_date, creation_ip => creation_ip, creation_user => creation_user ); v_revision_id := content_revision.new( title => title, description => description, mime_type => mime_type, nls_language => nls_language, text => text, item_id => v_item_id, creation_date => creation_date, creation_ip => creation_ip, creation_user => creation_user ); insert into cr_press (press_id, package_id, publication_name, publication_date, publication_date_desc, publication_link, article_link, article_abstract_html_p, article_pages, template_id, approval_user, approval_date, approval_ip, release_date, archive_date) values (v_revision_id, package_id, publication_name, publication_date, publication_date_desc, publication_link, article_link, article_abstract_html_p, article_pages, template_id, approval_user, approval_date, approval_ip, release_date, archive_date); -- make this revision live when immediately approved if is_live_p = 't' then content_item.set_live_revision (v_revision_id); end if; return v_revision_id; end new; -- deletes a press item along with all its revisions procedure del ( item_id in cr_items.item_id%TYPE ) is begin delete from cr_press where press_id in (select revision_id from cr_revisions where item_id = press.del.item_id); content_item.del( item_id => press.del.item_id ); end del; -- make a press item permanent by nulling the archive_date -- this only applies to the currently active revision procedure make_permanent ( item_id in cr_items.item_id%TYPE ) is begin update cr_press set archive_date = null where press_id = content_item.get_live_revision(press.make_permanent.item_id); end make_permanent; -- archive a press item by setting cr_press.release_date to sysdate -- this only applies to the currently active revision procedure archive ( item_id in cr_items.item_id%TYPE, archive_date in cr_press.archive_date%TYPE default sysdate ) is begin update cr_press set archive_date = press.archive.archive_date where press_id = content_item.get_live_revision(press.archive.item_id); end archive; -- approve/unapprove currently active revision procedure approve ( press_id in cr_press.press_id%TYPE, approve_p in varchar2 default 't' ) is v_item_id cr_items.item_id%TYPE; begin if press.approve.approve_p = 't' then -- approve revision content_item.set_live_revision ( revision_id => press_id ); -- set approval_date for revision update cr_press set approval_date = sysdate where press_id = press.approve.press_id; else -- get item_id for revision that is being unapproved select item_id into v_item_id from cr_revisions where revision_id = press_id; -- unapprove revision -- does not mean to knock out active revision -- content_item.unset_live_revision ( -- item_id => v_item_id -- ); -- null approval_date for revision update cr_press set approval_date = null, release_date = null where press_id = press.approve.press_id; end if; end approve; procedure approve_release( revision_id in cr_revisions.revision_id%TYPE, release_date in cr_press.release_date%TYPE, archive_date in cr_press.archive_date%TYPE ) is begin update cr_press set release_date = press.approve_release.release_date, archive_date = press.approve_release.archive_date where press_id = press.approve_release.revision_id; end approve_release; procedure set_active_revision( revision_id in cr_revisions.revision_id%TYPE ) is v_press_item_p char; -- could be used to check if really a 'press' item begin content_item.set_live_revision ( revision_id => press.set_active_revision.revision_id ); end set_active_revision; function is_live ( press_id in cr_press.press_id%TYPE ) return varchar is v_item_id cr_items.item_id%TYPE; begin select item_id into v_item_id from cr_revisions where revision_id = press.is_live.press_id; -- use get_live_revision if content_item.get_live_revision(v_item_id) = press.is_live.press_id then return 't'; else return 'f'; end if; end is_live; -- the status function returns information on the publish or archive status -- it does not make any checks on the order of release_date and archive_date function status ( press_id in cr_press.press_id%TYPE ) return varchar is v_archive_date date; v_release_date date; begin -- populate variables select archive_date, release_date into v_archive_date, v_release_date from cr_press where press_id = press.status.press_id; -- if release_date is not null the item is approved, otherwise it is not -- archive_date can be null if v_release_date is not null then if v_release_date - sysdate > 0 then -- to be published (2 cases) if v_archive_date is null then return 'going live in ' || round(to_char(v_release_date - sysdate),0) || ' days'; else return 'going live in ' || round(to_char(v_release_date - sysdate),1) || ' days' || ', archived in ' || round(to_char(v_archive_date - sysdate),0) || ' days'; end if; else -- already released or even archived (3 cases) if v_archive_date is null then return 'live, permanent'; else if v_archive_date - sysdate > 0 then return 'live, archived in ' || round(to_char(v_archive_date - sysdate),0) || ' days'; else return 'archived'; end if; end if; end if; else return 'unapproved'; end if; end status; end press; / show errors -- *** PACKAGE PRESS_REVISION, plsql to update press items -- press_revision: the basic idea here is to create a new press_revision -- in both, cr_revision and cr_press for each edit, i.e. you can't edit a press revision after -- upload, so that all changes are audited. -- -- plsql to create revisions of press items create or replace package press_revision as function new ( title in cr_revisions.title%TYPE, description in cr_revisions.description%TYPE default null, publish_date in cr_revisions.publish_date%TYPE default sysdate, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', nls_language in cr_revisions.nls_language%TYPE default null, text in varchar2, item_id in cr_items.item_id%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, package_id in cr_press.package_id%TYPE default null, publication_name in cr_press.publication_name%TYPE, publication_link in cr_press.publication_link%TYPE default null, publication_date in cr_press.publication_date%TYPE, publication_date_desc in cr_press.publication_date_desc%TYPE default null, article_link in cr_press.article_link%TYPE default null, article_pages in cr_press.article_pages%TYPE default null, article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, approval_user in cr_press.approval_user%TYPE default null, approval_date in cr_press.approval_date%TYPE default null, approval_ip in cr_press.approval_ip%TYPE default null, release_date in cr_press.release_date%TYPE default sysdate, archive_date in cr_press.archive_date%TYPE default null, template_id in press_templates.template_id%TYPE default 1, make_active_revision_p in varchar2 default 'f' ) return cr_revisions.revision_id%TYPE; procedure del ( revision_id in cr_revisions.revision_id%TYPE ); end press_revision; / show errors create or replace package body press_revision as function new ( title in cr_revisions.title%TYPE, description in cr_revisions.description%TYPE default null, publish_date in cr_revisions.publish_date%TYPE default sysdate, mime_type in cr_revisions.mime_type%TYPE default 'text/plain', nls_language in cr_revisions.nls_language%TYPE default null, text in varchar2, item_id in cr_items.item_id%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, package_id in cr_press.package_id%TYPE default null, publication_name in cr_press.publication_name%TYPE, publication_link in cr_press.publication_link%TYPE default null, publication_date in cr_press.publication_date%TYPE, publication_date_desc in cr_press.publication_date_desc%TYPE default null, article_link in cr_press.article_link%TYPE default null, article_pages in cr_press.article_pages%TYPE default null, article_abstract_html_p in cr_press.article_abstract_html_p%TYPE, approval_user in cr_press.approval_user%TYPE default null, approval_date in cr_press.approval_date%TYPE default null, approval_ip in cr_press.approval_ip%TYPE default null, release_date in cr_press.release_date%TYPE default sysdate, archive_date in cr_press.archive_date%TYPE default null, template_id in press_templates.template_id%TYPE default 1, make_active_revision_p in varchar2 default 'f' ) return cr_revisions.revision_id%TYPE is v_revision_id integer; begin -- create revision v_revision_id := content_revision.new( title => title, description => description, publish_date => publish_date, mime_type => mime_type, nls_language => nls_language, text => text, item_id => item_id, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip ); -- create new press entry to go with new revision insert into cr_press (press_id, package_id, publication_name, publication_date, template_id, publication_date_desc, publication_link, article_link, article_pages, article_abstract_html_p, approval_user, approval_date, approval_ip, release_date, archive_date) values (v_revision_id, package_id, publication_name, publication_date, template_id, publication_date_desc, publication_link, article_link, article_pages, article_abstract_html_p, approval_user, approval_date, approval_ip, release_date, archive_date); -- make active revision if indicated if make_active_revision_p = 't' then press.set_active_revision(v_revision_id); end if; return v_revision_id; end new; procedure del ( revision_id in cr_revisions.revision_id%TYPE ) is begin delete from cr_press where press_id = press_revision.del.revision_id; content_revision.del(press_revision.del.revision_id); end del; end press_revision; / show errors