BSP MORB Part IX Free Sample

SELECT DISTINCT ob.OBLIGATION_ID AS "Obligation",
       ob.SECTION_BREADCRUMB AS "Where It Sits",
       ob.LEGAL_CITATION AS "Citation",
       CASE kg.TO_NODE_LABEL
         WHEN 'Actor' THEN 'Who it binds'
         WHEN 'CoveredPerson' THEN 'Who it binds'
         WHEN 'Entity' THEN 'Who it binds'
         WHEN 'RequiredAction' THEN 'What it requires'
         WHEN 'RegulatoryObject' THEN 'What it applies to'
         WHEN 'Qualification' THEN 'Conditions and exceptions'
         WHEN 'GoverningClause' THEN 'Conditions and exceptions'
         WHEN 'ChecklistElement' THEN 'Testable steps'
         WHEN 'EvidenceExpectation' THEN 'Evidence examiners expect'
         WHEN 'Artifact' THEN 'Evidence examiners expect'
         WHEN 'Recipient' THEN 'Provide the evidence to'
         WHEN 'Topic' THEN 'How it is classified'
         WHEN 'FunctionalDomain' THEN 'How it is classified'
         WHEN 'Document' THEN 'Where it lives'
         WHEN 'Section' THEN 'Where it lives'
       END AS "What This Tells You",
       COALESCE(kg.TO_NODE_TEXT,
                CASE WHEN LOWER(kg.TO_NODE_NAME) = 'all'
                     THEN 'all covered persons'
                     ELSE REPLACE(REPLACE(kg.TO_NODE_NAME, '_', ' '),
                                  '-', ' ') END)
         AS "The Answer"
FROM REGULATORY.KNOWLEDGE_GRAPH kg
JOIN REGULATORY.OBLIGATIONS ob ON ob.OBLIGATION_ID = kg.OBLIGATION_ID
WHERE kg.OBLIGATION_ID = 'BSP.MORB.2023.Sec911.a(1).p11.OBL1'
  AND CASE kg.TO_NODE_LABEL
         WHEN 'Actor' THEN 'Who it binds'
         WHEN 'CoveredPerson' THEN 'Who it binds'
         WHEN 'Entity' THEN 'Who it binds'
         WHEN 'RequiredAction' THEN 'What it requires'
         WHEN 'RegulatoryObject' THEN 'What it applies to'
         WHEN 'Qualification' THEN 'Conditions and exceptions'
         WHEN 'GoverningClause' THEN 'Conditions and exceptions'
         WHEN 'ChecklistElement' THEN 'Testable steps'
         WHEN 'EvidenceExpectation' THEN 'Evidence examiners expect'
         WHEN 'Artifact' THEN 'Evidence examiners expect'
         WHEN 'Recipient' THEN 'Provide the evidence to'
         WHEN 'Topic' THEN 'How it is classified'
         WHEN 'FunctionalDomain' THEN 'How it is classified'
         WHEN 'Document' THEN 'Where it lives'
         WHEN 'Section' THEN 'Where it lives'
       END IS NOT NULL
ORDER BY 4, 5

SELECT OBLIGATION_ID AS "Obligation",
       SECTION_BREADCRUMB AS "Where It Sits",
       LEGAL_CITATION AS "Citation",
       OBLIGATION_SUMMARY AS "The Duty In Plain English",
       VERBATIM_TEXT AS "The Regulator's Exact Words",
       ACTOR AS "Who Must Act",
       ACTION_REQUIRED AS "Required Action",
       MODAL_STRENGTH AS "Binding Strength",
       SOURCE_PDF_URL AS "Official Source"
FROM REGULATORY.OBLIGATIONS
ORDER BY 1

SELECT o.OBLIGATION_ID AS "Obligation",
       o.SECTION_BREADCRUMB AS "Where It Sits",
       o.LEGAL_CITATION AS "Citation",
       o.OBLIGATION_SUMMARY AS "Requirement In Plain English",
       o.VERBATIM_TEXT AS "The Regulator's Exact Words",
       o.ACTOR AS "Who Must Act",
       o.ACTION_REQUIRED AS "Required Action",
       o.OBJECT_OF_OBLIGATION AS "Applies To",
       o.COVERED_PERSON_SCOPE AS "Covered Persons",
       o.MODAL_STRENGTH AS "Binding Strength",
       o.FREQUENCY AS "How Often",
       o.CONSEQUENCE AS "If You Do Not Comply",
       o.SEVERITY_HINT AS "Severity",
       o.RECORD:regulatory_intelligence:why_this_obligation_exists::string
         AS "Why It Exists",
       o.RECORD:regulatory_intelligence:interpretation_notes::string
         AS "How To Read It",
       o.RECORD:regulatory_intelligence:implementation_considerations::string
         AS "How Practitioners Implement It",
       o.SOURCE_PDF_URL AS "Official Source"
FROM REGULATORY.OBLIGATIONS o
ORDER BY 1

SELECT q.OBLIGATION_ID AS "Obligation",
       o.SECTION_BREADCRUMB AS "Where It Sits",
       o.LEGAL_CITATION AS "Citation",
       o.OBLIGATION_SUMMARY AS "The Duty",
       REPLACE(q.KIND, '_', ' ') AS "Condition Kind",
       q.TEXT AS "Condition Text",
       q.EFFECT AS "Effect On The Duty"
FROM REGULATORY.OBLIGATION_QUALIFICATIONS q
JOIN REGULATORY.OBLIGATIONS o ON o.OBLIGATION_ID = q.OBLIGATION_ID
ORDER BY q.OBLIGATION_ID, q.SEQ

SELECT o.OBLIGATION_ID AS "Obligation",
       o.SECTION_BREADCRUMB AS "Where It Sits",
       o.LEGAL_CITATION AS "Citation",
       o.OBLIGATION_SUMMARY AS "Requirement",
       o.MODAL_STRENGTH AS "Binding Strength",
       COALESCE(e.ARTIFACT,
                'Documented performance of the duty: ' ||
                o.ACTION_REQUIRED,
                'Documented performance of the duty as written')
         AS "What To Be Able To Show"
FROM REGULATORY.OBLIGATIONS o
LEFT JOIN REGULATORY.OBLIGATION_EVIDENCE_EXPECTATIONS e
  ON e.OBLIGATION_ID = o.OBLIGATION_ID
ORDER BY o.OBLIGATION_ID