NFI_TSO_PKG_ddl.sql

CREATE OR REPLACE PACKAGE "NFI_PDS"."NFI_TSO_PKG_STONE" AS
 
  gv_pro_ric constant pro_str_v.pro_nda_id%TYPE := cdapi_ret_pkg.get_pro_fn('RIC', 'ASSET');--1786
  gv_pro_base_asset_id constant pro_str_v.pro_nda_id%TYPE := cdapi_ret_pkg.get_rel_rbdm_fn('ASSET', 'ASSET', 'BASE ASSET'); --32
  gv_pro_pri_asset_id constant pro_str_v.pro_nda_id%TYPE := cdapi_ret_pkg.get_rel_rbdm_fn('ASSET', 'ASSET', 'PRIMARY QUOTE'); --13142
  gv_pro_val_asset_id constant pro_str_v.pro_nda_id%TYPE := cdapi_ret_pkg.get_rel_rbdm_fn('ASSET', 'ASSET', 'VALUATION QUOTE'); --77
  gv_pro_tag_classification constant pro_str_v.pro_nda_id%TYPE := cdapi_ret_pkg.get_pro_fn('TAG CLASSIFICATION', 'ASSET');--78
 
  FUNCTION get_market_nda_id(ric VARCHAR2)
  RETURN NUMBER;
 
  FUNCTION get_default_tso_type(ric VARCHAR2)
  RETURN VARCHAR2;
 
END NFI_TSO_PKG_STONE;
/
CREATE OR REPLACE PACKAGE BODY "NFI_PDS"."NFI_TSO_PKG_STONE" AS
 
  FUNCTION get_market_nda_id(ric VARCHAR2) RETURN NUMBER AS
  lv_issue_nda_id asset.nda_id%TYPE;
  lv_quote__nda_id asset.nda_id%TYPE;
  --primary quote or valuation quote
  lv_mkt_nda_id nts_asset_group.mkt_nda_id%TYPE;
  BEGIN
    SELECT aas.ast_against_nda_id
    INTO lv_issue_nda_id
    FROM identifier ide,
      asset_assoc aas
    WHERE ide.VALUE = ric
     AND ide.pro_nda_id = gv_pro_ric
     AND ide.end_date IS NULL
     AND aas.ast_for_nda_id = ide.ast_for_nda_id
     AND aas.erd_aas_type_nda_id = gv_pro_base_asset_id
     AND aas.end_date IS NULL;
    --query PRIMARY QUOTE first
    SELECT ast_against_nda_id
    INTO lv_quote__nda_id
    FROM asset_assoc
    WHERE ast_for_nda_id = lv_issue_nda_id
     AND erd_aas_type_nda_id = gv_pro_pri_asset_id
     AND end_date IS NULL;
    --if can not find PRIMARY QUOTE, then query VALUATION QUOTE
 
    IF lv_quote__nda_id IS NULL THEN
      SELECT ast_against_nda_id
      INTO lv_quote__nda_id
      FROM asset_assoc
      WHERE ast_for_nda_id = lv_issue_nda_id
       AND erd_aas_type_nda_id = gv_pro_val_asset_id
       AND end_date IS NULL;
    END IF;
 
    IF lv_quote__nda_id IS NULL THEN
      RETURN -1;
    END IF;
 
    SELECT asg.mkt_nda_id
    INTO lv_mkt_nda_id
    FROM classifier_map clm,
      classifier cla,
      nts_asset_group asg
    WHERE clm.ast_for_nda_id = lv_quote__nda_id
     AND clm.erd_cma_type_nda_id = gv_pro_tag_classification
     AND clm.end_date IS NULL
     AND cla.nda_id = clm.cla_against_nda_id
     AND asg.tag = to_number(cla.VALUE);
 
    RETURN lv_mkt_nda_id;
 
  EXCEPTION
  WHEN others THEN
    RETURN -1;
  END get_market_nda_id;
 
  FUNCTION get_default_tso_type(ric VARCHAR2) RETURN VARCHAR2 AS
  lv_mkt_nda_id nts_asset_group.mkt_nda_id%TYPE;
  lv_default_tso_type VARCHAR2(20);
  BEGIN
    SELECT get_market_nda_id(ric) INTO lv_mkt_nda_id FROM DUAL;
   
    SELECT cla.VALUE
    INTO lv_default_tso_type
    FROM classifier_map clm,
      classifier cla
    WHERE clm.mkt_for_nda_id = lv_mkt_nda_id
     AND clm.erd_cma_type_value_d = 'DEFAULT TSO TYPE'
     AND clm.end_date IS NULL
     AND cla.nda_id = clm.cla_against_nda_id
     AND cla.end_date IS NULL;
 
    RETURN lv_default_tso_type;
 
  EXCEPTION
  WHEN others THEN
    RETURN NULL;
  END get_default_tso_type;
 
END NFI_TSO_PKG_STONE;
/