Untitled

ALTER SESSION SET CURRENT_SCHEMA=apm
/
------------
-- APM PACKAGES
------------
------------
-- pkg_lkp_contracting_entity
------------
CREATE OR REPLACE PACKAGE pkg_lkp_contracting_entity
AS
PROCEDURE check_ri
(
p_contracting_entity_id syn_contracting_entity.contracting_entity_id%TYPE
);
END pkg_lkp_contracting_entity;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_contracting_entity
AS
PROCEDURE check_ri
(
p_contracting_entity_id syn_contracting_entity.contracting_entity_id%TYPE
)
AS
v_contracting_entity_id syn_contracting_entity.contracting_entity_id%TYPE;
BEGIN
IF (p_contracting_entity_id IS NOT NULL) THEN
BEGIN
SELECT contracting_entity_id INTO v_contracting_entity_id FROM syn_contracting_entity WHERE contracting_entity_id = p_contracting_entity_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_contracting_entity_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_contracting_entity;
/

------------
-- pkg_lkp_country
------------
CREATE OR REPLACE PACKAGE pkg_lkp_country
AS
PROCEDURE check_ri
(
p_country_id syn_country.country_id%TYPE
);
END pkg_lkp_country;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_country
AS
PROCEDURE check_ri
(
p_country_id syn_country.country_id%TYPE
)
AS
v_country_id syn_country.country_id%TYPE;
BEGIN
IF (p_country_id IS NOT NULL) THEN
BEGIN
SELECT country_id INTO v_country_id FROM syn_country WHERE country_id = p_country_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_country_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_country;
/

------------
-- pkg_lkp_regions_tbl
------------
CREATE OR REPLACE PACKAGE pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_country_id syn_regions_tbl.country_id%TYPE
);
END pkg_lkp_regions_tbl;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_country_id syn_regions_tbl.country_id%TYPE
)
AS
v_country_id syn_regions_tbl.country_id%TYPE;
BEGIN
IF (p_country_id IS NOT NULL) THEN
BEGIN
SELECT country_id INTO v_country_id FROM syn_regions_tbl WHERE country_id = p_country_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_country_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_regions_tbl;
/

------------
-- pkg_lkp_portfolio
------------
CREATE OR REPLACE PACKAGE pkg_lkp_portfolio
AS
PROCEDURE check_ri
(
p_portfolio_id syn_portfolio.portfolio_id%TYPE
);
END pkg_lkp_portfolio;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_portfolio
AS
PROCEDURE check_ri
(
p_portfolio_id syn_portfolio.portfolio_id%TYPE
)
AS
v_portfolio_id syn_portfolio.portfolio_id%TYPE;
BEGIN
IF (p_portfolio_id IS NOT NULL) THEN
BEGIN
SELECT portfolio_id INTO v_portfolio_id FROM syn_portfolio WHERE portfolio_id = p_portfolio_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_portfolio_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_portfolio;
/

------------
-- pkg_lkp_account
------------
CREATE OR REPLACE PACKAGE pkg_lkp_account
AS
PROCEDURE check_ri
(
p_account_id syn_account.account_id%TYPE
);
END pkg_lkp_account;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_account
AS
PROCEDURE check_ri
(
p_account_id syn_account.account_id%TYPE
)
AS
v_account_id syn_account.account_id%TYPE;
BEGIN
IF (p_account_id IS NOT NULL) THEN
BEGIN
SELECT account_id INTO v_account_id FROM syn_account WHERE account_id = p_account_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_account_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_account;
/

------------
-- pkg_lkp_regions_tbl
------------
CREATE OR REPLACE PACKAGE pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_country_id syn_regions_tbl.country_id%TYPE
);
END pkg_lkp_regions_tbl;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_country_id syn_regions_tbl.country_id%TYPE
)
AS
v_country_id syn_regions_tbl.country_id%TYPE;
BEGIN
IF (p_country_id IS NOT NULL) THEN
BEGIN
SELECT country_id INTO v_country_id FROM syn_regions_tbl WHERE country_id = p_country_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_country_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_regions_tbl;
/
CREATE OR REPLACE PACKAGE pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_region_cd syn_regions_tbl.region_cd%TYPE
);
END pkg_lkp_regions_tbl;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_regions_tbl
AS
PROCEDURE check_ri
(
p_region_cd syn_regions_tbl.region_cd%TYPE
)
AS
v_region_cd syn_regions_tbl.region_cd%TYPE;
BEGIN
IF (p_region_cd IS NOT NULL) THEN
BEGIN
SELECT region_cd INTO v_region_cd FROM syn_regions_tbl WHERE region_cd = p_region_cd;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_region_cd ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_regions_tbl;
/

------------
-- pkg_lkp_contact
------------
CREATE OR REPLACE PACKAGE pkg_lkp_contact
AS
PROCEDURE check_ri
(
p_enterprise_contact_id syn_contact.enterprise_contact_id%TYPE
);
END pkg_lkp_contact;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_contact
AS
PROCEDURE check_ri
(
p_enterprise_contact_id syn_contact.enterprise_contact_id%TYPE
)
AS
v_enterprise_contact_id syn_contact.enterprise_contact_id%TYPE;
BEGIN
IF (p_enterprise_contact_id IS NOT NULL) THEN
BEGIN
SELECT enterprise_contact_id INTO v_enterprise_contact_id FROM syn_contact WHERE enterprise_contact_id = p_enterprise_contact_id;
EXCEPTION
  WHEN no_data_found THEN
 RAISE_APPLICATION_ERROR(-20999, 'Primary key value  '|| p_enterprise_contact_id ||' not found!');
END;
END IF;
END check_RI;
END pkg_lkp_contact;
/