HPD 4.0

This view has been renamed FEATURE_ATTRIBUTE_VW. The old view returned feature attributes for source data only, but the new view returns feature attributes for source, isolated projects, and vector products. The attributeacronym column has been renamed to acronym. Although the data is queried by featver_id instead of rwf_id, those ids represent the same concept - a feature version id. The featver_id can be obtained from SOURCE_FEATURE_VW or SOURCE_FEATURE_HISTORY_VW for source data, or similar views for isolated projects and vector products

p_project_manager.AddProjectAttribute and p_project_manager.UpdateProjectAttribute

p_project_manager.AddProjectAttribute has been replaced with p_project_manager.set_project_attribute which can add or change attributes. The interfaces are very similar, with v_attributeclass_id replaced with the acronym of the attribute and the v_sequence parameter no longer necessary.

p_project_manager.UpdateProjectAttribute has been replaced with p_project_manager.set_project_attribute which can add or change attributes. UpdateProjectAttribute required that the attributeclass_id be used for new attributes and the projectatt_id be specified for deleted and edited attributes whereas set_project_attribute uses the attribute acronym for ease of use.

Note that only the attributes in the "Project Attributes" window of Project Editor can be set by these APIs.

-- Old way to add an attribute

select attributeclass_id into v_attributeclass_id from attributeclass where acronym = 'PRCOMM'; p_project_manager.AddProjectAttribute(v_project_id, v_attributeclass_id, 'new comments', NULL);

-- New way to add/edit an attribute: p_project_manager.set_project_attribute(v_project_id, 'PRCOMM', 'new comments');

UpdateProjectAttribute example. Note that v_projectatt_id is different for every project and is not the value from the catalogue. The "USRNTS" attribute does not exist in the default HPD catalogue but could be created as a user-defined attribute.

- Old way to add/edit/delete attributes

declare

v_project_id CONSTANT NUMBER := 2;

v_projectatt_id project_attribute.projectatt_id%type;

v_attributeclass_id attributeclass.attributeclass_id%type;

v_new_attributes NEWATTRIBUTES := NEWATTRIBUTES();

v_deleted_attributes HPDNUMBER$TABLE_TYPE := HPDNUMBER$TABLE_TYPE();

v_current_attributes HPDOBJCLS.CURRENTATTRIBUTES := HPDOBJCLS.CURRENTATTRIBUTES();

begin

-- Specify the new attributes.

v_new_attributes.extend(2);

select attributeclass_id into v_attributeclass_id from attributeclass where acronym = 'PRLAST';

v_new_attributes(1) := new_att_rec(v_attributeclass_id, '20170601', NULL);

select attributeclass_id into v_attributeclass_id from attributeclass where acronym = 'USRNTS';

v_new_attributes(2) := new_att_rec(v_attributeclass_id, 'User notes', NULL);

-- Specify the attributes to delete.

select PROJECTATT_ID into v_projectatt_id from project_attribute

where pj_id = v_project_id

and attributeclass_id = (select attributeclass_id from attributeclass where acronym = 'PRCOMM');

v_deleted_attributes.extend(1);

v_deleted_attributes(1) := HPDNUMBER$ROW_TYPE(v_projectatt_id);

-- Specify the attributes to update.

select PROJECTATT_ID into v_projectatt_id from project_attribute where pj_id = v_project_id

and attributeclass_id = (select attributeclass_id from attributeclass where acronym = 'WRKFLD');

v_current_attributes.extend(1);

v_current_attributes(1) := HPDOBJCLS.cur_att_rec(v_projectatt_id, 'Working folder', NULL);

p_project_manager.UpdateProjectAttribute( v_project_id, v_new_attributes, v_deleted_attributes, v_current_attributes);

end;

-- New way to add/edit/delete attributes

declare

v_project_id CONSTANT NUMBER:= 2;

begin

p_project_manager.set_project_attribute(v_project_id, 'PRLAST', '20170601'); -- Last date the project was used

p_project_manager.set_project_attribute(v_project_id, 'USRNTS', 'User notes'); -- A user-defined attribute

p_project_manager.set_project_attribute(v_project_id, 'PRCOMM', NULL); -- Project comments

p_project_manager.set_project_attribute(v_project_id, 'WRKFLD', 'Working folder'); -- The working folder

end;

The HPD_PROJ_ATTRIBUTES_VW view has been changed slightly. The pj_id column is now called project_id. The attributeclass_id column has been replaced with the attribute acronym. The dateval column has been removed. The intval, realval, lstval, and enmval column have all been replaced with the numberval column.

p_registry_manager.AddRegistryAttribute and p_registry_manager.UpdateRegistryAttribute

p_registry_manager.AddRegistryAttribute has been replaced with p_registry_manager.set_registry_attribute which can add or change attributes. The interfaces are very similar, with v_attributeclass_id replaced with the acronym of the attribute and the v_sequence parameter no longer necessary.

p_registry_manager.UpdateRegistryAttribute has been replaced with p_registry_manager.set_registry_attribute which can add or change attributes. UpdateRegistryAttribute required that the attributeclass_id be used for new attributes and the projectatt_id be specified for deleted and edited attributes whereas set_registry_attribute uses the attribute acronym for ease of use.

Note that only the attributes in the "Source Attributes" window of Project Editor can be set by these APIs.

p_registry_manager.set_registry_attribute(v_registry_id, 'DOCFIL', 'C:\sources\doc\doc12345.doc');

declare v_registry_id CONSTANT NUMBER:= 2; begin p_registry_manager.set_registry_attribute(v_registry_id, 'MEDTYP', 1); p_registry_manager.set_registry_attribute(v_registry_id, 'DOCLOC', 2); p_registry_manager.set_registry_attribute(v_registry_id, 'DOCFIL', NULL); p_registry_manager.set_registry_attribute(v_registry_id, 'SRCCOM', 'My comments'); end; /

The HPD_SOURCEREG_ATTRIB_VW view has been changed slightly. The intval, realval, lstval, and enmval columns have all been replaced with the numberval column.

p_encapi.addENCDefinition has been replaced by p_vectorapi.addS57Definition which can make any kind of S-57 product. The parameters are almost the same. AddENCDefinition started with v_ATTFLE and had v_NAME and v_VERSION later in the list. In addS57Definition, the first three parameters are the id, version, and key fields (e.g., "ENC", "3.1", "ENC") from the s57productinfo.xml file, which includes the v_VERSION field from addENCDefinition. All other parameters are unchanged. The return value is now a productversion_id instead of a product_id - you can query the product_id from the productversion_id via the VECTOR_PRODUCT_VIEW view.

p_encapi.addAMLDefinition has been replaced by p_vectorapi.addS57Definition which can make any kind of S-57 product. The parameters are almost the same but are in a different order. In addS57Definition, the first three parameters are the id, version, and key fields from

the s57productinfo.xml file (e.g., "CLB", "2.1", "AML"). The v_INTUSG parameter of addAMLDefinition is the same as v_NAVPUR in addS57Defini tion. The parameters v_NATOCD and v_secpmk were not used and no longer exist. The return value is now a productversion_id instead of a prod uct_id - you can query the product_id from the productversion_id via the VECTOR_PRODUCT_VIEW view.

P_ENCAPI.UPDATEENCDEFINITION, P_ENCAPI.UPDATEAMLDEFINITION, and P_GPEAPI. UPDATEGPEDEFINITION

p_encapi.updateENCDefinition, p_encapi.updateAMLDefinition, and p_gpeapi.updateGPEDefinition have been replaced by p_vectorapi. set_product_attribute for most values, and also p_vectorapi.set_product_geometry for geometry changes and p_vectorapi.set_product_group for setting the product group.

Attributes include NAME, VERSION, PROFILE, ATTFLE, NATFLE, HORDAT, SDATUM, VERDAT, HUNITS, DUNITS, PUNITS, NAVPUR, PRCODE, EDINUM, UPDNUM, COMPSC, DATSET, COMMEN, DSPCMT, and ISUDAT.

When using updateAMLDefinition, the parameter name was essentially the attribute acronym.

exec p_encapi.updateAMLDefinition(&productversion_id, p_COMMEN => 'Comments');

exec p_vectorapi.set_product_attribute(&productversion_id, 'COMMEN', 'Comments');

p_encapi.add_source_extraction has been replaced with two functions: p_vectorapi.add_source_extraction and p_vectorapi.add_source_usage. The new functions handle all types of S-57 and GML products.

p_gpeapi.add_source_extraction has been replaced with two functions: p_vectorapi.add_source_extraction and p_vectorapi.add_source_usage. The new functions handle all types of S-57 and GML products.

p_encapi.add_source_extraction(v_product_id, v_usage_name, v_geom);

p_vectorapi.add_source_extraction(v_product_id, v_geom);

p_vectorapi.add_source_usage(v_product_id, v_usage_name);

p_encapi.remove_all_source_extraction has been renamed p_vectorapi.remove_all_source_extraction. All parameters are identical. The new function handles all types of S-57 and GML products.

SOURCE_FEATURE_VW returned distinct foids and object acronyms for every source feature, including old source features. Now SOURCE_FE ATURE_VW returns more information but only for current features and a SOURCE_FEATURE_HISTORY_VW view returns information for current and old objects.

The query "select foid, object_acronym from source_feature_vw" will no longer return obsolete features. If you wish to continue to get information for obsolete features then use the following SQL:

select distinct foid, object_acronym from source_feature_history_vw;

PRODUCT_FEATURE_VW returned distinct foids for product versions, including old features. A new VP_FEATURE_VW view returns more information but only for current features and a new VP_FEATURE_HISTORY_VW view returns information for current and old objects.

-- Old way to get distinct foids:

select foid from product_feature_vw where productversion_id = :id;

-- new way to get distinct foids:

select distinct foid from vp_feature_history_vw where productversion_id = :id;

-- new way to get distinct foids for only current data:

select distinct foid from vp_feature_vw where productversion_id = :id;

PANEL_FEATURE_VW returned distinct foids for product versions, including old features. A new PANEL_FEATURE_VW view returns more information but only for current features and a new PANEL_FEATURE_HISTORY_VW view returns information for current and old objects.

-- Old way to get distinct foids:

select foid from panel_feature_vw where panelver_id = :id;

-- new way to get distinct foids:

select distinct foid from panel_feature_history_vw where panelver_id = :id;

-- new way to get distinct foids for only current data:

select distinct foid from panel_feature_vw where panelver_id = :id;

These views have been removed. They returned current and obsolete versions of source features whose FOIDs were in the TEMPFOIDS temporary table, making them cumbersome to use. The SOURCE_FEATURE_VW and SOURCE_FEATURE_HISTORY_VW views can be used without pre-populating temporary tables and are significantly more efficient for most queries.

In order to minimize changes to existing systems while still getting better performance, you could re-create the views in terms of the new views, with two differences from the previous behaviour: the state column no longer has a "Superseded" option, and the object_type column returns 'Line' or 'Area' instead of 'Line/Area' for line and area features.

create or replace view TMP_SRC_FEATURE_VERSION_INFO AS

select v.foid, v.object_acronym objectclass, v.state, v.initially_created, add_task.timestamp version_created ,del_task.timestamp versioned,

v.attributecertification, attcert_user.username attributecertifier, v.attributecertifieddate,

v.repcertification, repcert_user.username repcertifier, v.repcertifieddate,

v.usage_id, v.featver_id rwf_id, v.spatver_id repusage_id, v.rep_id, v.object_type, add_task.project_id

from source_feature_history_vw v

LEFT OUTER JOIN tasks del_task on (v.del_task_id = del_task.task_id)

LEFT OUTER JOIN hpd_users_vw repcert_user on (v.rep_cert_user_id = repcert_user.user_id),

task_vw add_task, hpd_users_vw attcert_user, tempfoids t

where v.add_task_id = add_task.task_id

and v.attribute_cert_user_id = attcert_user.user_id

and v.foid = t.foid;

create or replace view TMP_SRC_FEATURE_STATE AS

select v.foid, v.featver_id rwf_id, v.usage_id, v.rep_id, v.spatver_id repusage_id, v.state, del_task.timestamp versioned, v.objectclass_id, v.spa_type, v.object_type, v.add_task_id, v.del_task_id

from source_feature_history_vw v

LEFT OUTER JOIN tasks del_task on (v.del_task_id = del_task.task_id),

tempfoids t

where v.foid = t.foid;

Or, if it is convenient for the object_type column to return 'Line/Area', then in the TMP_SRC_FEATURE_VERSION_INFO and TMP_SRC_FEATU RE_STATE views, replace v.object_type with:

case when v.spa_type in ('A', 'L') then 'Line/Area' else v.object_type end object_type

To get the best performance, the simplest queries, and the most useful results, existing code could use the SOURCE_FEATURE_HISTORY_VW or the SOURCE_FEATURE_VW views directly. As can be seen in the above view definitions, the objectclass column is called object_acronym, the repusage_id column is called spatver_id, the rwf_id column is called featver_id, and the delete_task_id is called del_task_id in the new views. The object_type column returned "Line/Area" for lines and areas, but returns "Line" for lines and "Area" for areas in the new views. The versioned column does not exist in the new views, but the del_task_id column can be used to join to TASK_VW to get the date that the version was made obsolete. The version_created column does not exist in the new views, but the add_task_id column can be used to join to TASK_VW to get the date that the version was created. The users who certified data was listed as a user name and is now listed as a user ID - the user names can be obtained from HPD_USERS_VW.

-- Old way to get the current version of line/area features modified by a project

DELETE FROM TEMPFOIDS;

INSERT INTO TEMPFOIDS (FOID) (SELECT FOID FROM TABLE(P_PROJECT_MANAGER.GETPROJECTFEATURES (:PROJECT_ID)));

SELECT * FROM TMP_SRC_FEATURE_STATE -- or FROM TMP_SRC_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version'

AND OBJECT_TYPE = 'Area/Line';

-- New way to get the FOIDs of line/area features modified by a project in source:

select distinct FOID

from SOURCE_FEATURE_HISTORY_VW fv

where fv.OBJECT_TYPE in ('Area', 'Line')

and

(

fv.add_task_id in (select task_id from task_vw where project_id = :project_id)

or fv.del_task_id in (select task_id from task_vw where project_id = :project_id)

);

-- Old way to get the current certification information for a specific FOID

DELETE FROM TEMPFOIDS;

INSERT INTO TEMPFOIDS (FOID) VALUES (v_foid);

SELECT attributecertification, attributecertifier, attributecertifieddate, repcertification, repcertifier, repcertifieddate

FROM TMP_SRC_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version';

-- New way to get the current certification information for a specific FOID

SELECT attributecertification, attribute_cert_user_id, a.username attributecertifier, attributecertifieddate,

repcertification, rep_cert_user_id, r.username repcertifier, repcertifieddate

FROM SOURCE_FEATURE_VW f, hpd_users_vw a, hpd_users_vw r

WHERE f.FOID = v_FOID

AND f.attribute_cert_user_id = a.user_id (+)

AND f.rep_cert_user_id= r.user_id (+);

These views have been removed. They returned current and obsolete versions of isolated project features whose FOIDs were in the TEMPFOIDS temporary table, making them cumbersome to use. The IP_FEATURE_VW and IP_FEATURE_HISTORY_VW views can be used without pre- populating temporary tables and are significantly more efficient for most queries.

CREATE OR REPLACE VIEW TMP_IP_FEATURE_VERSION_INFO AS

select v.foid, v.object_acronym objectclass, v.state, add_task.timestamp version_created, del_task.timestamp versioned,

v.attributecertification, attcert_user.username attributecertifier, v.attributecertifieddate,

v.repcertification, repcert_user.username repcertifier, v.repcertifieddate,

v.featver_id fav_id, v.spatver_id fsv_id, v.project_id, v.object_type

from IP_FEATURE_HISTORY_VW v

LEFT OUTER JOIN tasks del_task on (v.del_task_id = del_task.task_id)

LEFT OUTER JOIN hpd_users_vw repcert_user on (v.rep_cert_user_id = repcert_user.user_id),

task_vw add_task, hpd_users_vw attcert_user, tempfoids t

where v.add_task_id = add_task.task_id

and v.attribute_cert_user_id = attcert_user.user_id

and v.foid = t.foid;

create or replace view TMP_IP_FEATURE_STATE AS

select v.project_id, v.foid, v.featver_id fav_id, v.spatver_id fsv_id, v.state,

add_task.timestamp version_created, del_task.timestamp versioned, v.object_type

from IP_FEATURE_HISTORY_VW v

LEFT OUTER JOIN tasks del_task on (v.del_task_id = del_task.task_id),

task_vw add_task, tempfoids t

where v.add_task_id = add_task.task_id

and v.foid = t.foid;

Or, if it is convenient for the object_type column to return 'Line/Area', then in the TMP_IP_FEATURE_VERSION_INFO and TMP_IP_FEATURE_ STATE views, replace v.object_type with:

case when v.spa_type in ('A', 'L') then 'Line/Area' else v.object_type end object_type

To get the best performance, the simplest queries, and the most useful results, existing code could use the IP_FEATURE_HISTORY_VW or the I P_FEATURE_VW views directly. As can be seen in the above view definitions, the objectclass column is called object_acronym, the fsv_id column is called spatver_id, and the fav_id column is called featver_id in the new views. The object_type column returned "Line/Area" for lines and areas, but returns "Line" for lines and "Area" for areas in the new views. The versioned column does not exist in the new views, but the del_task_id column can be used to join to TASK_VW to get the date that the version was made obsolete. The version_created column does not exist in the new views, but the add_task_id column can be used to join to TASK_VW to get the date that the version was created. The users who certified data was listed as a user name and is now listed as a user ID - the user names can be obtained from HPD_USERS_VW.

-- Old way to get the current certification information for a specific FOID

DELETE FROM TEMPFOIDS;

INSERT INTO TEMPFOIDS (FOID) VALUES (v_foid);

SELECT attributecertification, attributecertifier, attributecertifieddate, repcertification, repcertifier, repcertifieddate

FROM TMP_IP_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version';

-- New way to get the current certification information for a specific FOID

SELECT attributecertification, attribute_cert_user_id, a.username attributecertifier, attributecertifieddate,

repcertification, rep_cert_user_id, r.username repcertifier, repcertifieddate

FROM IP_FEATURE_VW f, hpd_users_vw a, hpd_users_vw r

WHERE f.FOID = v_FOID

AND f.attribute_cert_user_id = a.user_id (+)

AND f.rep_cert_user_id= r.user_id (+);

These views have been removed. They returned current and obsolete versions of vector product features whose FOIDs were in the TEMPFOIDS temporary table, making them cumbersome to use. The VP_FEATURE_VW and VP_FEATURE_HISTORY_VW views can be used without pre- populating temporary tables and are significantly more efficient for most queries.

In order to minimize changes to existing systems while still getting better performance, you could re-create the views in terms of the new views, with some differences from the previous behaviour: the state column no longer has a "Superseded" option, the object_type column returns 'Line' or 'Area' instead of 'Line/Area' for line and area features, and the internal id "productobject_id" has been replaced with two internal ids featver_id and spatver_id which can be used in other APIs.

CREATE OR REPLACE VIEW TMP_VP_FEATURE_VERSION_INFO AS

select v.productversion_id, v.foid, v.object_acronym objectclass, v.state,

add_task.timestamp version_created, del_task.timestamp versioned,

v.attributecertification, attcert_user.username attributecertifier, v.attributecertifieddate,

v.repcertification, repcert_user.username repcertifier, v.repcertifieddate,

v.featver_id, v.spatver_id, add_task.project_id, v.object_type, v.SOURCE_SPATVER_ID source_repusage_id

from VP_FEATURE_HISTORY_VW v

LEFT OUTER JOIN tasks del_task on (v.del_task_id = del_task.task_id)

LEFT OUTER JOIN hpd_users_vw repcert_user on (v.rep_cert_user_id = repcert_user.user_id),

task_vw add_task, hpd_users_vw attcert_user, tempfoids t

where v.add_task_id = add_task.task_id

and v.attribute_cert_user_id = attcert_user.user_id

and v.foid = t.foid;

create or replace view TMP_IP_FEATURE_STATE AS

select v.productversion_id, v.foid, v.featver_id, v.spatver_id, v.state, v.add_task_id, v.del_task_id delete_task_id,

v.object_type, v.objectclass_id, v.SOURCE_SPATVER_ID source_repusage_id

from VP_FEATURE_HISTORY_VW v, tempfoids t

where v.foid = t.foid;

Or, if it is convenient for the object_type column to return 'Line/Area', then in the TMP_IP_FEATURE_VERSION_INFO and TMP_IP_FEATURE_ STATE views, replace v.object_type with:

case when v.spa_type in ('A', 'L') then 'Line/Area' else v.object_type end object_type

To get the best performance, the simplest queries, and the most useful results, existing code could use the VP_FEATURE_HISTORY_VW or the VP_FEATURE_VW views directly. As can be seen in the above view definitions, the objectclass column is called object_acronym, and the product object_id column is now two ids called spatver_id and featver_id in the new views. The object_type column returned "Line/Area" for lines and areas, but returns "Line" for lines and "Area" for areas in the new views. The versioned column does not exist in the new views, but the del_task_i column can be used to join to TASK_VW to get the date that the version was made obsolete. The version_created column does not exist in the new views, but the add_task_id column can be used to join to TASK_VW to get the date that the version was created. The users who certified data was listed as a user name and is now listed as a user ID - the user names can be obtained from HPD_USERS_VW.

-- Old way to get the current version of line/area features modified by a project

DELETE FROM TEMPFOIDS;

insert into TEMPFOIDS (foid) (SELECT foid FROM table (p_project_manager.getProjectVectorFeatures(:project_id, :productversion_id)));

SELECT * FROM TMP_VP_FEATURE_STATE -- or FROM TMP_VP_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version'

AND OBJECT_TYPE = 'Area/Line';

-- New way to get the current version of line/area features modified by a project

SELECT * FROM VP_FEATURE_HISTORY_VW v, TASK_VW t

WHERE v.ADD_TASK_ID = t.TASK_ID

AND t.PROJECT_ID = :PROJECT_ID

AND v.STATE = 'Latest Version'

AND v.PRODUCTVERSION_ID = :productversion_id

AND v.OBJECT_TYPE in ('Area', 'Line');

-- Another new way to get the current version of line/area features modified by a project

SELECT * FROM VP_FEATURE_VW v, TASK_VW t

WHERE v.ADD_TASK_ID = t.TASK_ID

AND t.PROJECT_ID = :PROJECT_ID

AND v.PRODUCTVERSION_ID = :productversion_id

AND v.OBJECT_TYPE in ('Area', 'Line');

-- Old way to get the current certification information for a specific FOID

DELETE FROM TEMPFOIDS;

INSERT INTO TEMPFOIDS (FOID) VALUES (v_foid);

SELECT attributecertification, attributecertifier, attributecertifieddate, repcertification, repcertifier, repcertifieddate

FROM TMP_VP_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version';

-- New way to get the current certification information for a specific FOID

SELECT attributecertification, attribute_cert_user_id, a.username attributecertifier, attributecertifieddate,

repcertification, rep_cert_user_id, r.username repcertifier, repcertifieddate

FROM VP_FEATURE_VW f, hpd_users_vw a, hpd_users_vw r

WHERE f.FOID = v_FOID

AND f.attribute_cert_user_id = a.user_id (+)

AND f.rep_cert_user_id= r.user_id (+);

These views have been removed. They returned current and obsolete versions of paper product features whose FOIDs were in the TEMPFOIDS t emporary table, making them cumbersome to use. The PANEL_FEATURE_VW and PANEL_FEATURE_HISTORY_VW views can be used without pre-populating temporary tables and are significantly more efficient for most queries.

To migrate to the PANEL_FEATURE_HISTORY_VW or the PANEL_FEATURE_VW views, the objectclass column is now called object_acronym, and the prodfeatver_id column is now two ids called spatver_id and featver_id. The object_type column returned "Line/Area" for lines and areas, but returns "Line" for lines and "Area" for areas in the new views. The versioned column does not exist in the new views, but the del_task_id colu mn can be used to join to TASK_VW to get the date that the version was made obsolete. The version_created column does not exist in the new views, but the add_task_id column can be used to join to TASK_VW to get the date that the version was created. The users who certified data was listed as a user name and is now listed as a user ID - the user names can be obtained from HPD_USERS_VW. The state column no longer has a "Superseded" value and is considered another type of "Obsolete Version".

-- Old way to get the current version of line/area features modified by a project

DELETE FROM TEMPFOIDS;

insert into TEMPFOIDS (foid) (SELECT foid FROM table (p_project_manager.getProjectPaperFeatures(:project_id, :chartver_id)));

SELECT * FROM TMP_PPR_FEATURE_STATE -- or FROM TMP_PPR_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version'

AND OBJECT_TYPE = 'Area/Line';

-- New way to get the current version of line/area features modified by a project

SELECT * FROM PANEL_FEATURE_HISTORY_VW v, TASK_VW t, CHART_SHEET_PANEL_VW csp

WHERE v.add_task_id = t.task_id

AND t.PROJECT_ID = :PROJECT_ID

AND v.panelver_id = csp.panelver_id

AND csp.chartver_id = :chartver_id

AND v.STATE = 'Latest Version'

AND v.OBJECT_TYPE in ('Area', 'Line');

-- Another new way to get the current version of line/area features modified by a project

SELECT * FROM PPR_FEATURE_VW v, TASK_VW t, CHART_SHEET_PANEL_VW csp

WHERE v.add_task_id = t.task_id

AND t.PROJECT_ID = :PROJECT_ID

AND v.panelver_id = csp.panelver_id

AND csp.chartver_id = :chartver_id

AND v.OBJECT_TYPE in ('Area', 'Line');

-- Old way to get the current certification information for a specific FOID

DELETE FROM TEMPFOIDS;

INSERT INTO TEMPFOIDS (FOID) VALUES (v_foid);

SELECT attributecertification, attributecertifier, attributecertifieddate, repcertification, repcertifier, repcertifieddate

FROM TMP_PPR_FEATURE_VERSION_INFO

WHERE STATE = 'Latest Version';

-- New way to get the current certification information for a specific FOID

SELECT attributecertification, attribute_cert_user_id, a.username attributecertifier, attributecertifieddate,

repcertification, rep_cert_user_id, r.username repcertifier, repcertifieddate

FROM PANEL_FEATURE_VW f, hpd_users_vw a, hpd_users_vw r

WHERE f.FOID = v_FOID

AND f.attribute_cert_user_id = a.user_id (+)

AND f.rep_cert_user_id= r.user_id (+);

This pipelined function has been removed. It returned attributes for any feature-versions specified in TEMPFEATUREVERSION. The same (and more) information can be obtained from the FEATURE_ATTRIBUTE_VW view by querying by featver_id, which was called rwf_id (for source data) in previous versions of the API. Or, continue to store featver_id in the FAV_ID field of TEMPFEATUREVERSION and join TEMPFEATUREV ERSION to the FEATURE_ATTRIBUTE_VW view. UNKNOWN values were returned as "UNKNOWN" by P_HPDSOURCEAPI.G ETVERSIONA TTRIBUTES, but FEATURE_ATTRIBUTE_VW returns NULL and has a column to indicate that it is unknown. UNDEFINED values were not returned by P_HPDSOURCEAPI.GETVERSIONATTRIBUTES, but FEATURE_ATTRIBUTE_VW returns NULL and has a column to indicate that it is undefined.

This example hides undefined values and converts unknown values to the string "UNKNOWN" to replicate the functionality of the pipelined function.

-- setup

delete TEMPFEATUREVERSION;

insert into TEMPFEATUREVERSION(FAV_ID) (SELECT FEATVER_ID FROM ...);

-- query

select acronym, case when unknown = 'Y' then TO_CLOB('UNKNOWN') else data end att_value, type

from FEATURE_ATTRIBUTE_VW sv, TEMPFEATUREVERSION t

where sv.featver_id = t.fav_id

and sv.undefined = 'N'

order by sv.featver_id, sv.acronym;

These pipelined functions have been removed. They returned current/obsolete attributes for the specific feature version id or returned the current attributes by foid. The same (and more) information can be obtained from FEATURE_ATTRIBUTE_VW by directly querying by foid or by featver_ id (a.k.a. rwf_id). UNKNOWN values were returned as "UNKNOWN" but FEATURE_ATTRIBUTE_VW returns NULL and has a column to indicate that it is unknown. UNDEFINED values were not returned by these functions, but FEATURE_ATTRIBUTE_VW returns NULL and has a column to indicate that it is undefined.

These examples hide undefined values and convert unknown values to the string "UNKNOWN" to replicate the functionality of the pipelined functions.

-- P_QUERYSOURCE.GETATTRIBUTES example

select acronym, case when unknown = 'Y' then TO_CLOB('UNKNOWN') else data end att_value, type

from FEATURE_ATTRIBUTE_VW

where featver_id in (

select featver_id from SOURCE_FEATURE_VW where foid = v_foid)

and undefined = 'N';

-- P_QUERYSOURCE.GETVERSIONATTRIBUTES example

select acronym, case when unknown = 'Y' then TO_CLOB('UNKNOWN') else data end att_value, type

from FEATURE_ATTRIBUTE_VW

where featver_id = v_rwf_id

and undefined = 'N';

This pipelined function has been removed. The FEATURE_ATTRIBUTE_VW view can be used to get the same information. The pipelined function returned data for features in the temporary table tempfeatureversion whereas the view can be queried by featver_id directly. The featver_id can be obtained from IP_FEATURE_VW or IP_FEATURE_HISTORY_VW for isolated project data, or similar views for HPD source, paper products, and vector products. The pipelined function returned records containing fav_id, acronym, att_value, and type whereas the view has the same information in columns named featver_id, acronym, data, and type.

This piplelined function has been removed. The FEATURE_ATTRIBUTE_VW view can be used to get the same information. The pipelined function returned data for features in the temporary table tempfeatureversion whereas the view can be queried by featver_id directly. The featver_id can be obtained from VP_FEATURE_VW or VP_FEATURE_HISTORY_VW for vector product data, or similar views for isolated projects, paper products, and HPD source data. The pipelined function returned records containing fav_id, acronym, att_value, and type whereas the view has the same information in columns named featver_id, acronym, data, and type.

This piplelined function has been removed. The FEATURE_ATTRIBUTE_VW view can be used to get the same information. The pipelined function returned data for features in the temporary table tempfeatureversion whereas the view can be queried by featver_id directly. The featver_id can be obtained from PANEL_FEATURE_VW or PANEL_FEATURE_HISTORY_VW for paper product data, or similar views for isolated projects, vector products, and HPD source data. The pipelined function returned records containing fav_id, acronym, att_value, and type whereas the view has the same information in columns named featver_id, acronym, data, and type.

These pipelined functions have been removed. They returned current information about the spatial versions queried by FOID or by rep_id. The same (and more) information can be obtained from SOURCE_FEATURE_VW by querying by FOID or by spatver_id.

These examples hide collections and rename columns to replicate the functionality of the pipelined functions.

-- P_QUERYSOURCE.getFeatureUsage example

select sv.foid, sv.object_acronym acronym, e.timestamp modified, sv.usage_name usage, sv.representation_certification status

from SOURCE_FEATURE_VW sv, EDIT_VW e

where sv.edit_id = e.edit_id

and spa_type is not null -- ignore collections

and sv.foid = v_foid;

-- P_QUERYSOURCE.getFeatureVersionUsage example

select sv.foid, sv.object_acronym acronym, e.timestamp modified, sv.usage_name usage, sv.representation_certification status

from SOURCE_FEATURE_VW sv, EDIT_VW e

where sv.edit_id = e.edit_id

and spa_type is not null -- ignore collections

and sv.spatver_id = v_spatver_id;

These pipelined functions have been removed. They returned information about the spatial representation (a.k.a. geometry) queried by FOID+usage or by rep_id. The same (and more) information can be obtained from FEATURE_GEOMETRY_VW by querying by rep_id. A rep_id can be obtained by FOID and usage, or by other criteria using SOURCE_FEATURE_VW.

-- P_QUERYSOURCE.getGeomFoid example

select geom, usage_name

from FEATURE_GEOMETRY_VW g, SOURCE_FEATURE_VW f

where g.rep_id = f.rep_id

and f.foid = v_foid

and f.usage_id = v_usage_id;

-- P_QUERYSOURCE.getGeomRepIdexample

select geom, usage_name

from FEATURE_GEOMETRY_VW g

where g.rep_id = v_rep_id;

This pipelined function has been removed. It returned information about the spatial representation (a.k.a. geometry) queried by the fsv_id column of the tempfeatureversion temporary table. The same (and more) information can be obtained from FEATURE_GEOMETRY_VW by querying by rep_id. A rep_id can be obtained by foid and usage, or other criteria using SOURCE_FEATURE_VW.