Archived licenses from FTA orphaned in Myra db

[micheal-w-wells]

Similar to agreement status issue.

[micheal-w-wells]

AR, S, any others we'd need to include in the licensee query in order to hide these from staff and AH @LisaMoore1

[micheal-w-wells]

Here's the original query for this one.

CREATE OR REPLACE VIEW FTA_RANGE_LICENSEE AS
SELECT
       PFU.FOREST_FILE_ID
     , PFU.FILE_STATUS_ST
     , PFU.FILE_TYPE_CODE
     , FTC.DESCRIPTION    AS FILE_TYPE_DESC
     , ORG.ORG_UNIT_CODE
     , ORG.ROLLUP_REGION_CODE
     , NVL(RTN.DISTRICT_ADMN_ZONE,PFU.DISTRICT_ADMIN_ZONE)  AS DISTRICT_ADMIN_ZONE
     , RGZ.ZONE_DESCRIPTION
     , RGZ.CONTACT
     , RGZ.CONTACT_PHONE_NUMBER
     , RGZ.CONTACT_EMAIL_ADDRESS
     , TTM.LEGAL_EFFECTIVE_DT
     , TTM.INITIAL_EXPIRY_DT
     , TTM.CURRENT_EXPIRY_DT
     , FFC.LICENSEE_START_DATE
     , FFC.LICENSEE_END_DATE
FROM      MV_PROV_FOREST_USE    PFU
LEFT JOIN MV_FILE_TYPE_CODE     FTC ON  FTC.FILE_TYPE_CODE               = PFU.FILE_TYPE_CODE
LEFT JOIN MV_RANGE_TENURE       RTN ON  RTN.FOREST_FILE_ID               = PFU.FOREST_FILE_ID
LEFT JOIN MV_TENURE_TERM        TTM ON  TTM.FOREST_FILE_ID               = PFU.FOREST_FILE_ID
LEFT JOIN MV_ORG_UNIT           ORG ON  ORG.ORG_UNIT_NO                  = RTN.ADMIN_FOREST_DISTRICT_NO
LEFT JOIN MV_FOREST_FILE_CLIENT FFC ON  FFC.FOREST_FILE_ID               = PFU.FOREST_FILE_ID
                                    AND FFC.FOREST_FILE_CLIENT_TYPE_CODE = 'A'
LEFT JOIN MV_RANGE_ZONE         RGZ ON  RGZ.ADMIN_FOREST_DISTRICT_NO     = RTN.ADMIN_FOREST_DISTRICT_NO
                                    AND RGZ.RANGE_ZONE_CODE              = NVL(RTN.DISTRICT_ADMN_ZONE,PFU.DISTRICT_ADMIN_ZONE)
WHERE  PFU.FILE_STATUS_ST = 'A'
;

[LisaMoore1]

@micheal-w-wells P (pending) will need some consideration but it is a "before" it is fully active rather than after so a different thing I think.

[micheal-w-wells]

Ok so for the 'deleting' we'll leave those alone. Thanks!

[LisaMoore1]

@micheal-w-wells I assume by "deleting" here you mean no longer visible in the regular work queue or accessible to initiate a plan or amendment. If that's the case all good.

[micheal-w-wells]

That's right yep. FOREST_FILE_CLIENT_TYPE_CODE or FILE_STATUS_ST (for filtering for A, AR, P. etc)?

Hard to tell from the query because its filtering for A's in both.

[LisaMoore1]

@micheal-w-wells Just wondering if the query above is only going to create/replace for things that are 'A' at this point in time. I think we only want it to "create" for A and the P one(s) but we do want the ones that are now AR, RRS etc to get updated (replaced) to that in MyRA. I always have to think these queries over a million times but this one just raises question for me and I can't obviously tell if it will do what we want. I'm going to assume that you are completely all over that and I'm not going to think about it any more unless you tell me to. :)

[micheal-w-wells]

The ORDS folks have been asked to update the endpoint in dev, we can flush out the above scenario you mentioned here @LisaMoore1 once it lands in test this week.

[micheal-w-wells]

/remind test

[reminders[bot]]

@micheal-w-wells we had trouble parsing your reminder. Try:

/remind me [what] [when]

[micheal-w-wells]

/remind me to look for the ORDS update next week

[reminders[bot]]

@micheal-w-wells set a reminder for Mar 27th 2020

[reminders[bot]]

:wave: @micheal-w-wells, look for the ORDS update

[micheal-w-wells]

• [x] see if update is in dev
• [ ] test update interval with someone updating test FTA
• [ ] update import.js to deal with new license types and run import in test