permits

[Jegelewicz]

Shouldn't we have an option of "export" for permit type? Also, rather than mash types together (take, possess, transport), I think it might be better to let us allow multiple types. Just an idea!

[campmlc]

We are hoping to be able to revise permits soon with some external funding. So please pass on more suggestions! Mariel

On Thu, May 18, 2017 at 3:06 PM, Teresa Mayfield notifications@github.com wrote:

Shouldn't we have an option of "export" for permit type? Also, rather than mash types together (take, possess, transport), I think it might be better to let us allow multiple types. Just an idea!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/1139, or mute the thread https://github.com/notifications/unsubscribe-auth/AOH0hEbK3fHaZfRhGNGKpFLdzvFhVqj0ks5r7LLBgaJpZM4Nfy8a .

[Jegelewicz]

OK great! It would be nice to attach a document (PDF of the permit) and instead of remarks, be able to add number of specimens allowed to be taken/transported, give details about where the transport is allowed, and a list of species allowed to be taken/transported under the permit.

[Jegelewicz]

I guess we are going to have to consider renewals and how that affects some of the items I mentioned above. It might make sense to have the data for each renewal.

[dustymc]

This has now been funded, bumping priority.

mash types together

https://arctos.database.museum/info/ctDocumentation.cfm?table=CTPERMIT_TYPE

While normalizing type is certainly "correct" from a data modeling standpoint, I'm a little hesitant to suggest adding that complexity for so few values. Are the current values something like "complete"? (E.g., can anyone see needing dozens/hundreds of new permit types?)

Actually, I'm not sure if the smashing is necessary with our current data. I think we need to define values before anything else. I started a spreadsheet. Anyone can edit; please do.

attach a document

1165

add number of specimens allowed to be taken/transported

Can that be structured? I think it's usually unpredictable free text: "17 sparrows, 5 warthogs, ..."

where the transport is allowed

Example of those data? (Isn't that usually somewhat embedded in Issuer? AK can issue permits for AK...)

renewals

http://handbook.arctosdb.org/documentation/permits.html#renewed-date

[dustymc]

buy secure permit storage??

Investigate, report to AWG.

[dustymc]

TACC can provide "private" storage starting at $118/TB/yr.

What are our requirements?

• Do we need HIPAA-level security or is something behind a simple password sufficient?
• Do we need individual passwords, or would one shared account work?
• ????

[ccicero]

I edited Dusty's google doc with my suggestions for permit type values and associated definitions. We need a way to attach >1 permit type to a permit.

Good to hear about 'private' storage option at TACC. What does it mean to have HIPAA-level security, how would that work?

Sounds like something that we might want to bounce to the ASC, but can we draft a brief summary of the different options to send to them?

[ccicero]

Also, there may be other types of permits for cultural collections - Angie?

[dustymc]

way to attach >1 permit type to a permit.

OK, I can get started on that. Shouldn't be TOO complicated given how permits relate to other stuff. That model also allows for zero types, although I can "strongly discourage" that in the interfaces. OK?

What does it mean to have HIPAA-level security, how would that work?

https://www.hhs.gov/hipaa/for-professionals/security/laws-regulations/index.html

Looks like end-to-end encryption and server-side compliance monitoring, but I have no idea how that's implemented. Almost certainly incompatible with shared accounts.

TACC does allow individual accounts to the secure storage, but it would be extra work/maintenance for us. I suspect that would get very complicated when staff changes, and I think it would eliminate any chance of sharing a permit (eg, when material is divided among multiple institutions). I think simpler is better, when/if it's possible.

I think we should develop a general idea of what we need and then schedule a call with TACC for details.

@AJLinn

[AJLinn]

The cultural collections, primarily archaeology, would have land use permits for excavations/surveys that are issued by state, federal, and tribal entities (these would fall under the "collect" permit type in the Google doc). These would most likely be associated at the accession level and they should only be visible by operators of the UAM:Arc collection. Is that possible in this proposed system?

When we loan objects made from marine mammal or other protected species across international borders, we use the same permits as the natural history collections (e.g., CITES, MMPA affidavits, NMFS, etc.). Those would be media associated with Loans and would correspond with "export" and "import" types.

The other kind of non-public media we'd want to have associated with our specimens would be documentation relating to NAGPRA work (Native American Graves Protection and Repatriation Act). This could be correspondence between tribes and the museum where we discuss a repatriation claim, which might involve human remains, funerary objects, sacred objects, objects of cultural patrimony. The way we track this at the object level is specimen record-->loan (data)-->project. The media would be associated with the Project. For example: http://arctos.database.museum/project/central-council-of-the-tlingit-and-haida-indian-tribes-nagpra-consultation-documentation

These wouldn't be permits per se, but important media that document our compliance with a federal regulation. These would need to restricted to operators of that collection as well (UAM:EH and UAM:Arc).

Thoughts?

[ccicero]

Maybe we need something about regulatory act governing the permit. e.g., CITES - permit type = import or export, issuing agency = U.S. Fish and Wildlife Service, no clear place to put that it's a CITES permit except remarks. Could we add a field for Regulation that is a controlled list (not required), e.g.

Convention on International Trade in Endangered Species U.S. Endangered Species Act Migratory Bird Treaty Act Marine Mammal Protection Act Native American Graves Protection and Repatriation Act etc.

Re: permissions, simpler is probably better but how simple? Sounds like cultural collections wouldn't want others to see their permits for land use? Other collections probably feel the same way, e.g,. would UAM:Bird want MVZ:Bird to have access to copies of its permits (probably not)? Is 'by collection' too complex? Something we need to discuss at next AWG meeting.

[dustymc]

land use permits

@ccicero is that the same as "research" (which still makes no sense to me - do we have specimens which permits DON'T allow to be used for research?!?)

associated at the accession level

All permits tie in to transactions (accessions, loans, borrows)

only be visible by operators of the UAM:Arc collection

Permits are currently shared. Please file an Issue if we need to re-address that (or only enter the information which can be shared across Arctos). No permit information is publicly shared - GGBN will receive summarized data only.

media ... restricted to operators of that collection

All Media data objects (eg, organized text fields) are shared.

All Media files (eg, images and PDFs) hosted by Arctos (eg, on https://web.corral.tacc.utexas.edu/) are publicly available.

Arctos Media URI is unrestricted - you can host your own media behind whatever protections you want (eg, your own FTP/web/whatever server which you administer) and link to that from Media - users will end up on your password page (or wherever you send them).

I'm going to split the discussions regarding Arctos hosting restricted Media off into it's own Issue. #1328

[dustymc]

field for Regulation that is a controlled list

Is that a single value? EG are there CITES+ESA+... permits?

Is 'by collection' too complex?

To administer: probably not.

To use: depends. What happens when CollectionA keeps the skulls and sends tissues to CollectionB?

[ccicero]

I don't think land use is necessarily the same as research. For example, for Guatemala we have a Convenio that is a collaborative research agreement between the MVZ and Universidad de San Carlos. In other cases, there may be a land use agreement that allows you to dig on Forest Service lands. Two separate kinds of permissions.

Re: Regulation: No, those are separate acts and thus have separate permits. One accession or loan might have a CITES permit and a ESA permit, but they are issued as separate permits.

[dustymc]

collaborative research agreement between the MVZ and ...

What ACTION does that allow though??

[ccicero]

In Guatemala we have a Convenio which authorizes us to do research in Guatemala as a collaboration with the University there. It's a 5 year agreement that facilitates us getting permits to collect and export, but does not have specific authorizations. So to do work in Guatemala we have the Convenio, collecting permit with specific authorizations, and export permit. I think it's the same in Indonesia where each person first needs to get a permit that authorizes them to do research in the country, then specimens are covered by collecting and export permits.

There are also 'special-use permits' e.g., from USFS, probably the same as a land use permit. https://www.fs.fed.us/working-with-us/contracts-commercial-permits/special-use-permit-application

[dustymc]

Got it - thanks.

Here's where I think we are:

Permit is currently...

UAM@ARCTOSTE> desc permit
 Name                                  Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 PERMIT_ID                             NOT NULL NUMBER
 ISSUED_BY_AGENT_ID                        NOT NULL NUMBER
 ISSUED_DATE                                    DATE
 ISSUED_TO_AGENT_ID                        NOT NULL NUMBER
 RENEWED_DATE                                   DATE
 EXP_DATE                                   DATE
 PERMIT_NUM                                 VARCHAR2(25)
 PERMIT_TYPE                               NOT NULL VARCHAR2(50)
 PERMIT_REMARKS                                 VARCHAR2(300)
 CONTACT_AGENT_ID                               NUMBER

and will become...

UAM@ARCTOSTE> desc permit
 Name                                  Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 PERMIT_ID                             NOT NULL NUMBER
 ISSUED_BY_AGENT_ID                        NOT NULL NUMBER
 ISSUED_DATE                                    DATE
 ISSUED_TO_AGENT_ID                        NOT NULL NUMBER
 RENEWED_DATE                                   DATE
 EXP_DATE                                   DATE
 PERMIT_NUM                                 VARCHAR2(25)
 PERMIT_REMARKS                                 VARCHAR2(300)
 CONTACT_AGENT_ID                               NUMBER
requiring_act NEW FIELD NULLABLE--->new code table with values from ^^ up there

plus...

new table permit_type
---------------------------------------------------
whatverwecallit PKEY
permit_id FKEY-->permit
PERMIT_TYPE-->fkey ctpermit_type               NOT NULL VARCHAR2(50)

Are we all happy with everything else? Should we break Agents out into a table (agent+role) like we've done with transactions, or ?????

Things NOT in this, because I have no idea what to do with them:

add number of specimens allowed to be taken/transported where the transport is allowed

[campmlc]

Can we allow more than one permit contact?

On Nov 14, 2017 3:00 PM, "dustymc" notifications@github.com wrote:

Got it - thanks.

Here's where I think we are:

Permit is currently...

UAM@ARCTOSTE> desc permit Name Null? Type

---

PERMIT_ID NOT NULL NUMBER ISSUED_BY_AGENT_ID NOT NULL NUMBER ISSUED_DATE DATE ISSUED_TO_AGENT_ID NOT NULL NUMBER RENEWED_DATE DATE EXP_DATE DATE PERMIT_NUM VARCHAR2(25) PERMIT_TYPE NOT NULL VARCHAR2(50) PERMIT_REMARKS VARCHAR2(300) CONTACT_AGENT_ID NUMBER

and will become...

UAM@ARCTOSTE> desc permit Name Null? Type

---

PERMIT_ID NOT NULL NUMBER ISSUED_BY_AGENT_ID NOT NULL NUMBER ISSUED_DATE DATE ISSUED_TO_AGENT_ID NOT NULL NUMBER RENEWED_DATE DATE EXP_DATE DATE PERMIT_NUM VARCHAR2(25) PERMIT_REMARKS VARCHAR2(300) CONTACT_AGENT_ID NUMBER requiring_act NEW FIELD NULLABLE--->new code table with values from ^^ up there

plus...

new table permit_type

whatverwecallit PKEY permit_id FKEY-->permit PERMIT_TYPE-->fkey ctpermit_type NOT NULL VARCHAR2(50)

Are we all happy with everything else? Should we break Agents out into a table (agent+role) like we've done with transactions, or ?????

Things NOT in this, because I have no idea what to do with them:

add number of specimens allowed to be taken/transported where the transport is allowed

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/1139#issuecomment-344413473, or mute the thread https://github.com/notifications/unsubscribe-auth/AOH0hKcqXiYQmAaUpDo_WEwpb7aGhaboks5s2g13gaJpZM4Nfy8a .

[dustymc]

Can we allow more than one permit contact?

We can if we

break Agents out into a table (agent+role) like we've done with transactions

If we're going there I think we should probably remove all agents from the permit table - having some roles structurally different would just be a confusing mess. So that would be one more table:

permit_agent
-------------------------------
localkey PKEY
permit_id FKEY-->permit
agent_id FKEY-->agent
agent_role FKEY-->new code table

Note that while I can "strongly encourage" agents to exist in interfaces, that structure cannot require agents.

[ccicero]

I think it makes sense to keep one permit contact as the primary contact for notifications, but be able to have >1 'issued to' agent. For example, our USFWS migratory bird permit is issued to me and Rauri, but I am the primary contact who deals with renewals.

I've also been confused by 'renewed_date' versus 'issued_date.' If a permit expires and is renewed, then a new permit would be created with a new issue date and updated expiration date. Can we be clear about when to use 'renewed' versus 'issued' ???

[campmlc]

Separating out agents to a table with agent roles would be useful. I need to be a permit contact and receive notifications for permits obtained by other agents and divisions who have their own contacts.

On Tue, Nov 14, 2017 at 3:42 PM, dustymc notifications@github.com wrote:

Can we allow more than one permit contact?

We can if we

break Agents out into a table (agent+role) like we've done with transactions

If we're going there I think we should probably remove all agents from the permit table - having some roles structurally different would just be a confusing mess. So that would be one more table:

permit_agent

localkey PKEY permit_id FKEY-->permit agent_id FKEY-->agent agent_role FKEY-->new code table

Note that while I can "strongly encourage" agents to exist in interfaces, that structure cannot require agents.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/1139#issuecomment-344424355, or mute the thread https://github.com/notifications/unsubscribe-auth/AOH0hBzlHQ9mNzAaxcUNpeJ1oFAUoiMcks5s2hdFgaJpZM4Nfy8a .

[ccicero]

agreed, it would be better

[campmlc]

So which do we do - allow more than one notification contact, or have "issued to agent" receive expiration notifications?

On Tue, Nov 14, 2017 at 3:54 PM, Carla Cicero notifications@github.com wrote:

agreed, it would be better

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/1139#issuecomment-344427515, or mute the thread https://github.com/notifications/unsubscribe-auth/AOH0hKO6wpMlZO1HXPX3N2IKtjvQ8K7-ks5s2hofgaJpZM4Nfy8a .

[dustymc]

"issued to agent" receive expiration notifications

That would definitely cause problems.

I have no idea why we have "renewed date" either - I've always thought that would be better as a new permit. It also confuses expiration_date - is that the date the original expired or the date some renewed permit which has since been re-renewed 7 more times would have expired had it not been renewed or ?????????????????? (Only real answer: who knows, that's not in the model, different people probably do different things.)

Revised model:

UAM@ARCTOSTE> desc permit
 Name                                  Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 PERMIT_ID                             NOT NULL NUMBER
 ISSUED_DATE                                    DATE
 RENEWED_DATE                                   DATE
 EXP_DATE                                   DATE
 PERMIT_NUM                                 VARCHAR2(25)
 PERMIT_REMARKS                                 VARCHAR2(300)
requiring_act NEW FIELD NULLABLE--->new code table with values from ^^ up there

new table permit_type
---------------------------------------------------
whatverwecallit PKEY
permit_id FKEY-->permit
PERMIT_TYPE-->fkey ctpermit_type               NOT NULL VARCHAR2(50)

permit_agent
-------------------------------
localkey PKEY
permit_id FKEY-->permit
agent_id FKEY-->agent
agent_role FKEY-->new code table

with ctpermit_agent_role ininitially having....

• issued to
• issued by
• contact

[campmlc]

This looks good to me, as it would allow more than one permit contact agent.

On Tue, Nov 14, 2017 at 4:04 PM, dustymc notifications@github.com wrote:

"issued to agent" receive expiration notifications

That would definitely cause problems.

I have no idea why we have "renewed date" either - I've always thought that would be better as a new permit. It also confuses expiration_date - is that the date the original expired or the date some renewed permit which has since been re-renewed 7 more times would have expired had it not been renewed or ?????????????????? (Only real answer: who knows, that's not in the model, different people probably do different things.)

Revised model:

UAM@ARCTOSTE> desc permit Name Null? Type

---

PERMIT_ID NOT NULL NUMBER ISSUED_DATE DATE RENEWED_DATE DATE EXP_DATE DATE PERMIT_NUM VARCHAR2(25) PERMIT_REMARKS VARCHAR2(300) requiring_act NEW FIELD NULLABLE--->new code table with values from ^^ up there

new table permit_type

whatverwecallit PKEY permit_id FKEY-->permit PERMIT_TYPE-->fkey ctpermit_type NOT NULL VARCHAR2(50)

permit_agent

localkey PKEY permit_id FKEY-->permit agent_id FKEY-->agent agent_role FKEY-->new code table

with ctpermit_agent_role ininitially having....

• issued to
• issued by
• contact

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/1139#issuecomment-344429789, or mute the thread https://github.com/notifications/unsubscribe-auth/AOH0hEY1mcppQ_XLLOxgV7W2svfar0Cpks5s2hxqgaJpZM4Nfy8a .

[AJLinn]

Sorry to jump back in late... I may have mis-labeled the archaeology permit types. Here's a link to the state permit: http://dnr.alaska.gov/parks/oha/archsurv/permitinvestigate.htm It's a permit that allows for: "investigation, excavation, gathering, or removing from the natural state, of any historic, prehistoric, or archaeological resources of the state." Presumably the federal and tribal are similar in wording. @sjshirar might be able to further clarify when it comes time to finalizing the archaeology permits.

[ccicero]

It seems like that archaeology permit is a 'special-use permit' ???

Re: renewed_date, how much data are there and are there any permits now that have both a renewed_date and an issued_date? I vote to get rid of that if we can.

maybe change 'requiring_act' to 'regulatory_act' for clarity?

roles look good to me.

[dustymc]

archaeology permit

Sounds like a "collecting" permit to me, but whatever - that's just a code table value and I don't see any conflicts with the model.

I like 'special-use permit'==>"not any of the others" though - that option might keep folks from picking something random and prevent 900 values each used for one permit and all that.

'requiring_act' to 'regulatory_act'

Much better - thanks!

renewed_date

UAM@ARCTOS> select count(*) from permit;

  COUNT(*)
----------
      1280

1 row selected.

Elapsed: 00:00:00.03
UAM@ARCTOS> select count(*) from permit where issued_date is not null;

  COUNT(*)
----------
      1228

1 row selected.

Elapsed: 00:00:00.03
UAM@ARCTOS> select count(*) from permit where renewed_date is not null;

  COUNT(*)
----------
    34

1 row selected.

Elapsed: 00:00:00.00
UAM@ARCTOS> select count(*) from permit where renewed_date is not null and issued_date is not null;

  COUNT(*)
----------
    33

1 row selected.

[ccicero]

Thanks. I say we review those 33-34 permits with renewed_date and see if we can clean that up, then get rid of renewed_date. Would like to hear what others think though.

Special Use Permit may allow uses other than collecting, e.g., digging :)

I'm a little bothered by "Act" - CITES is a treaty, Nagoya Protocol could go there too (e.g., for MOUs) and it's not really an act. Is there another word that's more general than 'act' - maybe just 'regulation' is sufficient???

[dustymc]

Special Use Permit

Maybe I just need more coffee, but I think I just got back to my initial reasons for suggesting we DO NOT add that intermediate table for <>1 permit type. I don't think we're issued permits to dig holes - we're issued permits to collect, and hole-digging might be included in/incidental to that. That idea certainly not just cultural - some permits allow the collection of very specific critters in very specific circumstances in very specific places, others are "things not on that list, have fun." I still don't think it matters - you were authorized by the permit to remove linked specimens from "the wild" (or ebay or whatever) or not. I think we have to generalize - there are an infinite number of seldom-reused permit types if we're specific enough - and I see little/no reason to be more specific than "this permit allowed THAT activity with this object" in permit type. (Permit Remarks still brings along the specific data, which varies from permit to permit.)

I think "collecting" permits sort of all come with at least some sort of implicit idea that you're allowed to transport the thing you collect. I don't see that separate activity as necessary.

I'm still not sure what "research" is, and it plus "transport" are the combinations on http://arctos.database.museum/info/ctDocumentation.cfm?table=CTPERMIT_TYPE. Perhaps "research" alone is a 'good' permit type, but combining it with other activities seems to conflict with the definitions above and in the spreadsheet??

Can we keep permit_type as an exactly-one-value field in table permit? It would somewhat simplify the model (not terribly important either way), and I think it would lead to much more focused data - it would do a better job of explaining what's allowed under a permit.

If "combo" permit are important, they could be entered as two permits which differ only by permit_type (and I could easily add a "clone" button).

Is permit_type not attached to "regulation"? If so, those data should probably be in the same row of the same table - eg, add both to table permit, or move both to new table "permit-type-and-corresponding-regulation."

review those 33-34

Reports/WriteSQL, use the code below. I'm happy to make a spreadsheet, but I'm not sure how paranoid everyone is about these data and don't want to make semi-public anything I shouldn't.

select
  permit_id,
  getPreferredAgentName(ISSUED_BY_AGENT_ID) issuedby,
  ISSUED_DATE,
  getPreferredAgentName(ISSUED_TO_AGENT_ID) issuedto,
  RENEWED_DATE,
  EXP_DATE,
  PERMIT_NUM,
  PERMIT_TYPE,
  PERMIT_REMARKS,
  getPreferredAgentName(CONTACT_AGENT_ID) contact
from permit where renewed_date is not null;

[ccicero]

I think I just got back to my initial reasons for suggesting we DO NOT add that intermediate table for <>1 permit type. ---> I do think we need >1 permit type per permit. What about our USFWS permit that authorizes salvage, collecting, and import/export. I think it's best to keep those types separate, rather than combining them which is what we have now.

I don't think we're issued permits to dig holes - we're issued permits to collect, and hole-digging might be included in/incidental to that. ---> Not necessarily. You might need a 'special-use permit' to dig on USFS lands, but they don't have authority to regulate collecting, so you need 1 or more separate collecting permit(s) that specify what you can take, how many, where, etc.

I think "collecting" permits sort of all come with at least some sort of implicit idea that you're allowed to transport the thing you collect. I don't see that separate activity as necessary. ---> True...but transport permits don't necessarily come with collecting authorizations. USDA has three kinds of permits for restricted materials (mostly birds) - two import permits (one for countries with avian influenza, the other for countries that aren't listed as having avian influenza) and then a transport permit which is required for transport of material of foreign origin from one US institution to another.

I'm still not sure what "research" is, ---> As said before, sometimes a research agreement is required to do research in a country (e.g., Guatemala, Indonesia). An MOU under Nagoya might also qualify as a research agreement, i.e., specifies what research can be done with the material.

Can we keep permit_type as an exactly-one-value field in table permit? ---> I think that's what I'm saying. Best to be explicit about the values and keep the different types separate, and be able to apply >1 type to a permit. I don't think we want a combo value.

Is permit_type not attached to "regulation"? ---> Not really. USFWS issues collecting permits, so type = collecting, but there are different types of collecting permits depending on the regulation, e.g., Migratory Birds (MBTA) Threatened/Endangered Species (ESA) CITES

Currently, the only way to know that it's a CITES permit is to put that in the remarks field.

I tried your SQL query and all of the dates are coming out as "00:00.0" ???

Would it help to have a permit-focused meeting to talk about all of this?

[dustymc]

OK, thanks, I think that puts us back to the model in https://github.com/ArctosDB/arctos/issues/1139#issuecomment-344429789 (with some minor vocabulary changes).

Yes, a (very quick, I think) meeting might be useful just to make sure we're all on the same page before I start ripping things apart.

I'm still not quite getting the relationship between permit type and regulation. Say USFWS issues you a permit to collect (or any permit_type action) a whooping crane. They are (let's pretend if they're not) covered by all of MBTA, ESA, and CITES (and probably about 10 more). Do you get one permit that covers the three (or many) regulations, or a bunch of distinct permits, or ???? "Regulation" should be very handy for reporting, I don't want to muddy that or paint ourselves into a corner.

You should be seeing dates which contain a midnight timestamp (because CF and Oracle don't quite speak the same language). Try this

select
  permit_id,
  getPreferredAgentName(ISSUED_BY_AGENT_ID) issuedby,
  to_char(ISSUED_DATE,'yyyy-mm-dd') ISSUED_DATE,
  getPreferredAgentName(ISSUED_TO_AGENT_ID) issuedto,
  to_char(RENEWED_DATE,'yyyy-mm-dd') RENEWED_DATE,
  to_char(EXP_DATE,'yyyy-mm-dd') EXP_DATE,
  PERMIT_NUM,
  PERMIT_TYPE,
  PERMIT_REMARKS,
  getPreferredAgentName(CONTACT_AGENT_ID) contact
from permit where renewed_date is not null

[ccicero]

Say USFWS issues you a permit to collect (or any permit_type action) a whooping crane. They are (let's pretend if they're not) covered by all of MBTA, ESA, and CITES (and probably about 10 more). Do you get one permit that covers the three (or many) regulations, or a bunch of distinct permits, or ???? "

---> MBTA is issued by the Migratory Bird Permits office of USFWS. Marine Mammal Protection Act permits could potentially be combined with ESA permits (see application form https://www.fws.gov/forms/3-200-43.pdf). ESA and CITES could also potentially be combined into one permit (see application form https://www.fws.gov/forms/3-200-37.pdf). Maybe it would help to look at the USFWS permit site https://www.fws.gov/permits/ApplicationMain.html - you can see that within each one of those laws or treaties, there are multiple possible application forms depending on what you want do to. I think we need to keep those separate and be able to have >1 regulation per permit. That way, you could do a report on CITES permits, or ESA permits, or MMPA permits, or permits that cover some combination like CITES/ESA or MMPA/ESA etc.

That SQL query worked, thanks. It looks like we could just create new permits for those and then get rid of renewed date.

Should we schedule a permit-focused meeting and invite the broader Arctos group? Best for me after Dec 1 because of IMLS grant deadline.

[dustymc]

I would like to have this done, or at least well under construction, before then, in accordance with our GGBN schedule.

I'll throw in an emphatic "yes" vote for ditching renewed_date, not that my vote should much count here.

I don't think my interpretation of fed permit organization will be very useful!

have >1 regulation per permit

That is my current take as well, and it does not fit in the model I've previously laid out.

you could do a report on CITES permits, or ESA permits, or MMPA permits, or permits that cover some combination like CITES/ESA or MMPA/ESA

Yes, I think this will be the big functional change here.

SO, here's the model which I think does everything we've discussed.

permit
 ----------------------------------------------------------------- 
 PERMIT_ID           NOT NULL PKEY
 ISSUED_DATE             NOT NULL DATE
 EXP_DATE                   NOT NULL DATE
 PERMIT_NUM              NOT NULL VARCHAR2(25)
 PERMIT_REMARKS    VARCHAR2(4000)

new table permit_type
---------------------------------------------------
permit_type_id             NOT NULL PKEY
permit_id                      NOT NULL FKEK(permit)
PERMIT_TYPE               NOT NULL FKEY(ctpermit_type)
regulation                     FKEY(ctpermit_regulation)

permit_agent
-------------------------------
permit_agent_id            NOT NULL PKEY
permit_id                       NOT NULL FKEY(permit)
agent_id                        NOT NULL FKEY(agent)
agent_role                     NOT NULL FKEY(ctpermit_agent_role)

I added the new code table initial values to https://docs.google.com/spreadsheets/d/1UIUxdoS96tdcZMH-dRCKl48T1QKCOktLj5kHtUIxGx8/edit#gid=0 As code table values they're easy to change - all that really matters is that the initial values can hold current data (and I'm sure they can).

Permits with a renewed_date will be duplicated as a new permit; both the "source" and "renewed" permits will get an explanatory remark. @Jegelewicz does that sufficiently address https://github.com/ArctosDB/arctos/issues/1139#issuecomment-302550199 ?

Here's a VERY quick-n-dirty mockup of a form which works for this model.

[dustymc]

AWG Meeting:

• get rid of renewed_date: yes

• forms will "require" "issued to" (but model cannot)

• forms will "require" "issued by" (but model cannot)

• forms will "require" permit type (but model cannot)

• permit type is uncontrolled string - "no number" is a valid value

• change label of "permit number" to "permit identifier"

• Add permit_name (NULL) varchar uncontrolled

• make sure these agent roles appear in agent info

[campmlc]

AWG:

-PERMIT_NUM -PERMIT_IDENTIFIER (so it reflects different types of identifying systems), required -Required fields: issued date, exp date, permit identifier, permit type, agent issued by, issued to

[dustymc]

I need a migration pathway for permit_type - from http://arctos.database.museum/info/ctDocumentation.cfm?table=CTPERMIT_TYPE to https://docs.google.com/spreadsheets/d/1UIUxdoS96tdcZMH-dRCKl48T1QKCOktLj5kHtUIxGx8/edit#gid=0

other --> no permit types? Does a 'nothing very useful to say' option mean we DO NOT want to "require" permit type after all?? import --> no change take/possess -->collect research --> no change take/possess, research -->collect + research take/possess, transport -->collect + transport transfer of property --> ??? Import?? Export?? Transport??? NULL?? transport --> no change

If I don't hear back soon-ish I'll drop type for the mystery permits and leave remarks.

@ccicero I think the values I need help with are mostly MVZ permits

@jtgiermakowski can you clarify the need for permit_name

[ccicero]

I'm looking at the list of permits that are 'transfer of property' and think that we need another permit type for those. Most of them are USFWS confiscations where they transfer property to the institution once they have done their legal work. I don't see that fitting under any of the other permit types. Basically, the permit authorizes the institution/collection to possess the specimens.

[ccicero]

one option might be to combine 'transport' and 'transfer of property' into a new permit type 'transfer'. The USDA transport permit is really a transfer permit. In looking at the 'transport' permits, many of those are actually export permits. There are a lot of those. Can you put transport and transfer of property permits into a google doc and we can go through and provide a migration path for each one based on what's noted in remarks?

[dustymc]

https://docs.google.com/spreadsheets/d/1XnZ4Jt3SViLYkvDGSrF8A45I7mbMTEfLnBWKpuiBTuY/edit?usp=sharing

create table temp_pmt_tfrpt as
select
  permit_id,
  getPreferredAgentName(ISSUED_BY_AGENT_ID) ISSUED_BY_AGENT,
  getPreferredAgentName(ISSUED_TO_AGENT_ID) ISSUED_TO_AGENT,
  getPreferredAgentName(CONTACT_AGENT_ID) CONTACT_AGENT,
  ISSUED_DATE,
  RENEWED_DATE,
  EXP_DATE,
  PERMIT_NUM,
  PERMIT_TYPE,
  PERMIT_REMARKS
from
  permit
where
  permit_type in ('transport','take/possess, transport','transfer of property')
;

[ccicero]

see updated google doc for definitions - changed 'transport' to 'transfer' and updated the definition.

[ccicero]

I went through the google doc for transport + transfer of property and did my best guess as to new permit type for migration. Cells in orange are uncertain. Most are NMFS - not sure if those cover collect, import, and/or export. Also unclear if permit to Ted Papenfuss for expedition dated 11 Aug 2007 (subpermit to T.J. Papenfuss under Dr. Gabriela Parra Olea for collecting Plethodontid salamanders) is collect + export. @atrox10

[ccicero]

It would be good if someone else reviewed this to see if they concur with my proposed new types for these transport + transfer of property permits.

[dustymc]

Thanks!! I'm going to have code ready for prod and will need to do something fairly soon (early/mid next week??). If there's no resolution by then or you're not comfortable with something or you just want the old data, I can stuff current permit type into remarks and we can revisit this later. Better to have it all cleaned up of course, but not the end of the world if that doesn't happen.

[AJLinn]

I just added seven more regulations, 2 for wildlife and 5 for cultural and paleontological collections. The rest looks good to me.

[dustymc]

Here's the current intended permit type migration path.

Current values and definitions for permit type are:

• collect: Authorization to collect and possess specimens or their parts (e.g., blood, feather samples). NOTE: Same as "take/possess"
• export: Authorization to export specimens or their parts from one country to another.
• import: Authorization to import specimens or their parts from one country to another.
• research: Authorization to conduct research within a permitted jurisdiction. Usually also requires additional permits (e.g., collect, export). Examples: U.S. Forest Service research permit; Memorandum of Understanding; Convenio.
• salvage: Authorization to pick up and possess dead animals.
• transfer: Authorization to transfer specimens from one institution to another within the same country. Example: USDA Transport Permit for restricted materials between US institutions; USFWS authorization to transfer confiscated materials.

And the migration path:

• Where permit_id is in https://docs.google.com/spreadsheets/d/1XnZ4Jt3SViLYkvDGSrF8A45I7mbMTEfLnBWKpuiBTuY/edit#gid=2122230195, and permit_type_should_be contains ONLY a "+"-separated list of permits, use permit_type_should_be values (Or should I ignore "?" and use what's left?? @ccicero )

for whatever's left:

• other --> DELETE, append to permit_remark 'original permit type given as "other"'
• import -->import
• take/possess-->collect
• research-->research
• take/possess, research-->collect + research
• take/possess, transport-->collect + transfer
• transfer of property-->transfer
• transport-->transfer

The main permit forms in TEST should be essentially functional at this point. Have a look around, let me know if anything is wonky.

I think the "permit_regulation" values (https://docs.google.com/spreadsheets/d/1UIUxdoS96tdcZMH-dRCKl48T1QKCOktLj5kHtUIxGx8/edit#gid=0) are overly cryptic in the forms.

"Convention on International Trade in Endangered Species of Wild Fauna and Flora" seems overly verbose as well (and everybody knows what CITES means anyway). I have no suggestions, but this looks potentially problematic.

[AJLinn]

I think the "permit_regulation" values (https://docs.google.com/spreadsheets/d/1UIUxdoS96tdcZMH-dRCKl48T1QKCOktLj5kHtUIxGx8/edit#gid=0) are overly cryptic in the forms.

screen shot 2017-12-07 at 10 15 43 am

"Convention on International Trade in Endangered Species of Wild Fauna and Flora" seems overly verbose as well (and everybody knows what CITES means anyway). I have no suggestions, but this looks potentially problematic.

Agreed - some acronyms are pretty easy to understand (most everyone will know MMPA or CITES) but I'm cool with the full name being listed on the dropdown list.

[ccicero]

I think that as long as there is a 'help' link from 'Regulation' that shows the full name of the regulation, it will be ok.

@dustymc: migration path looks ok to me except for 'take/possess, transport' - use the suggested permit type in https://goo.gl/DSHzRo. Should not be 'collect + transfer' as noted above. Some state permits are really just collecting permits that also allow disposition in an institution in another state, so they were listed as 'take/possess, transport' but are just 'collect.' Other such permits are 'collect + export' - depends on the permit, so best to go with values in the spreadsheet.

Keep remarks for all permits.

I emailed Link and Ted about the ones that I had as "?"

[dustymc]

@ccicero I will do what's in https://goo.gl/DSHzRo first and THEN the "for whatever's left" steps to anything not in the spreadsheet. The spreadsheet will take precedence. If all "take/possess, transport" (or whatever) permits are covered in the spreadsheet, then nothing else will happen.

I can APPEND old permit_type (and/or whatever) to permit_remarks. I won't remove any existing remarks (or anything else).

There is a link to the regulation code table (http://arctos-test.tacc.utexas.edu/info/ctDocumentation.cfm?table=CTPERMIT_REGULATION) on the new edit form.

[ccicero]

Can you please put the 'other' permits in that spreadsheet (or another one, whatever's easiest) and I'll go through those as well. They should not be deleted.

Looking at the test form - has that not been updated to the new mockup?