1 package body BIS_BIA_DIM_MAP_UTIL AS
2 /*$Header: BISDIMUB.pls 120.1 2006/05/09 13:11:55 aguwalan noship $*/
3
4 g_current_user_id NUMBER := FND_GLOBAL.User_id;
5 g_current_login_id NUMBER := FND_GLOBAL.Login_id;
6
7 /*This API is not been used currently. It was introduced to show countries asscociated to Area in Serach Page
8 Its kept for future use*/
9 /* Bug#5203008 :: Commenting this api since its SQL is being reported in Performance Repository and is not
10 * used anyways
11 */
12 /*FUNCTION GET_COUNTRIES_BY_AREA(p_AREA_CODE VARCHAR2) RETURN VARCHAR2
13 IS
14 CURSOR c_countries (l_AREA_CODE bis_areas_v.AREA_CODE%type)
15 IS
16 select
17 con.name name
18 from BIS_TERRITORY_HIERARCHIES ter,
19 bis_countries_v con
20 where ter.CHILD_TERRITORY_CODE = con.country_code
21 and ter.parent_territory_code = l_AREA_CODE;
22
23 l_countries varchar(4000);
24
25 begin
26
27 for c_countries_rec in c_countries(p_AREA_CODE) loop
28 l_countries := l_countries || c_countries_rec.name || ', ';
29 end loop;
30
31 l_countries := substr(l_countries, 1, length(l_countries) - 2);
32 return l_countries;
33 end;
34 */
35
36 /*This API adds are and country association. THis is been called from UI.
37 Here we do not have validation as all the validations are already been done on frontend part.*/
38
39 PROCEDURE ADD_AREA_COUNTRY_ASSOCIATION (p_area_code VARCHAR2 ,
40 p_country_code VARCHAR2
41 )
42 IS
43 BEGIN
44 INSERT into BIS_TERRITORY_HIERARCHIES (PARENT_TERRITORY_CODE,
45 PARENT_TERRITORY_TYPE,
46 CHILD_TERRITORY_CODE,
47 CHILD_TERRITORY_TYPE,
48 START_DATE_ACTIVE,
49 END_DATE_ACTIVE,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN)
55 values(p_area_code,
56 'AREA',
57 p_country_code,
58 'COUNTRY',
59 sysdate,
60 sysdate,
61 g_current_user_id,
62 sysdate,
63 g_current_user_id,
64 sysdate,
65 g_current_login_id);
66 commit;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 RAISE;
71
72 END ADD_AREA_COUNTRY_ASSOCIATION;
73
74 /* This API is been called from UI to delete are_country association.
75 We need not to do any validation here*/
76
77 PROCEDURE DEL_AREA_COUNTRY_ASSO(p_area_code VARCHAR2 )
78 IS
79 BEGIN
80
81 Delete BIS_TERRITORY_HIERARCHIES where PARENT_TERRITORY_CODE = p_area_code;
82
83 commit;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 RAISE;
88
89 END DEL_AREA_COUNTRY_ASSO;
90
91
92 END BIS_BIA_DIM_MAP_UTIL;