1 Package Body Pa_Rbs_Elements_Utils AS
2 /* $Header: PARELEUB.pls 120.0 2005/05/30 02:23:48 appldev noship $*/
3
4 -- When checking that parent element exists for element you are working to udpate/add
5 -- Returns 'Y' for exists and 'N' for not exists
6 Function RbsElementExists(
7 P_Element_Id IN Number) Return Varchar2
8
9 Is
10
11 l_dummy Varchar2(1) := 'Y';
12
13 Begin
14
15 Select
16 'Y'
17 Into
18 l_dummy
19 From
20 Pa_Rbs_Elements
21 Where
22 Rbs_Element_Id = P_Element_Id;
23
24
25 Return 'Y';
26
27 Exception
28 When No_Data_Found Then
29 Return 'N';
30 When Others Then
31 Raise;
32
33 End RbsElementExists;
34
35 Function GetRbsElementNameId(
36 P_Resource_Type_Id IN Number,
37 P_Resource_Source_Id IN Number) Return Number
38
39 Is
40
41 l_Rbs_Element_Name_Id Number(15) := Null;
42
43 Begin
44
45 Select
46 Rbs_Element_Name_Id
47 Into
48 l_Rbs_Element_Name_Id
49 From
50 Pa_Rbs_Element_Names_B
51 Where
52 Resource_Type_id = P_Resource_Type_Id
53 And Resource_Source_Id = P_Resource_Source_Id;
54
55 Return l_Rbs_Element_Name_Id;
56
57 Exception
58 When No_Data_Found Then
59 Return Null;
60
61 End GetRbsElementNameId;
62
63 Procedure GetResSourceId(
64 P_Resource_Type_Id IN Number,
65 P_Resource_Source_Code IN Varchar2,
66 X_Resource_Source_Id OUT NOCOPY Number)
67
68 Is
69
70 l_res_type_code Varchar2(30) := Null;
71 l_Dummy_Msg_Data Varchar2(30) := Null;
72 l_Dummy_Status Varchar2(1) := Null;
73
74 Begin
75
76 -- Make sure the type is in user-defined, named_role, revenue_cat
77 l_Res_Type_Code := Pa_Rbs_Elements_Utils.GetResTypeCode(P_Resource_Type_Id);
78
79 If l_Res_Type_Code is Null Then
80
81 Raise No_Data_Found;
82
83 End If;
84
85 --Changes for Bug 3780201: Added PERSON_TYPE in the If check.
86 If l_res_type_code in ('USER_DEFINED','REVENUE_CATEGORY','PERSON_TYPE') Then
87
88 -- A procedure is provided which will create records for these
89 -- resources types and if needed create a record first.
90 If P_Resource_Source_Code is Null then
91
92 Raise No_Data_Found;
93
94 Else
95
96 Pa_Rbs_Mapping.Create_Res_Type_Numeric_Id(
97 P_Resource_Name => P_Resource_Source_Code,
98 P_Resource_Type_Id => P_Resource_Type_Id,
99 X_Resource_Id => X_Resource_Source_Id,
100 X_Return_Status => l_Dummy_Status,
101 X_Msg_Data => l_Dummy_Msg_Data );
102
103 If X_Resource_Source_Id is Null Then
104 Raise No_Data_Found;
105 End If;
106 End If;
107
108 End If;
109
110 Exception
111 When Others Then
112 Raise;
113
114 End GetResSourceId;
115
116 Function GetResTypeCode(
117 P_Res_Type_Id IN Number) Return Varchar2
118
119 Is
120
121 l_Res_Type_Code Varchar2(30) := Null;
122
123 Begin
124
125
126 Select
127 Res_Type_Code
128 Into
129 l_Res_Type_Code
130 From
131 Pa_Res_Types_B
132 Where
133 Res_Type_Id = P_Res_Type_Id;
134
135 Return l_Res_Type_Code;
136
137 Exception
138 When No_Data_Found Then
139 Return Null;
140 When Others Then
141 Raise;
142
143 End GetResTypeCode;
144
145 END Pa_Rbs_Elements_Utils;