1 PACKAGE csi_ii_relationships_pvt AS
2 /* $Header: csiviirs.pls 120.2 2008/03/08 01:03:51 fli ship $ */
3 -- start of comments
4 -- PACKAGE name : csi_ii_relationships_pvt
5 -- purpose :
6 -- history :
7 -- note :
8 -- END of comments
9
10 -- default NUMBER of records fetch per call
11 g_default_num_rec_fetch NUMBER := 30;
12
13 /* Start of Cyclic Relationships */
14
15 TYPE instance_rec IS RECORD
16 ( instance_id NUMBER,
17 hop NUMBER );
18
19 TYPE instance_tbl IS TABLE OF instance_rec
20 INDEX BY BINARY_INTEGER ;
21 --
22 TYPE REL_COLOR_REC IS RECORD
23 ( node_id NUMBER,
24 color_code VARCHAR2(1)
25 );
26 TYPE REL_COLOR_TBL IS TABLE OF REL_COLOR_REC INDEX BY BINARY_INTEGER;
27 --
28 PROCEDURE Get_Cyclic_Node
29 ( p_instance_id IN NUMBER,
30 p_cyclic_node OUT NOCOPY NUMBER,
31 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
32 p_stop_at_cyclic IN VARCHAR2 DEFAULT FND_API.G_TRUE,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2 );
36 --
37 PROCEDURE get_rel_for_instance
38 (p_instance_id IN NUMBER,
39 p_time_stamp IN DATE,
40 p_active_relationship_only IN VARCHAR2,
41 x_relationship_tbl IN OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl) ;
42
43 PROCEDURE get_neighbors_for_instance
44 (p_instance_id IN NUMBER ,
45 p_hop IN NUMBER ,
46 p_depth IN NUMBER ,
47 p_time_stamp IN DATE,
48 p_active_relationship_only IN VARCHAR2,
49 x_neighbor_inst_tbl OUT NOCOPY instance_tbl) ;
50
51 PROCEDURE get_cyclic_relationships
52 (
53 p_api_version IN NUMBER,
54 p_commit IN VARCHAR2 := fnd_api.g_false,
55 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
56 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
57 p_instance_id IN NUMBER ,
58 p_depth IN NUMBER,
59 p_time_stamp IN DATE,
60 p_active_relationship_only IN VARCHAR2 := fnd_api.g_false,
61 x_relationship_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2
65 );
66
67 /* End of Cyclic Relationships */
68
69 PROCEDURE get_relationships
70 (
71 p_api_version IN NUMBER,
72 p_commit IN VARCHAR2 := fnd_api.g_false,
73 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
74 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
75 p_relationship_query_rec IN csi_datastructures_pub.relationship_query_rec,
76 p_depth IN NUMBER,
77 p_time_stamp IN DATE,
78 p_active_relationship_only IN VARCHAR2 := fnd_api.g_false,
79 p_recursive_flag IN VARCHAR2 := fnd_api.g_false,
80 x_relationship_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2
84 );
85
86
87 PROCEDURE create_relationship(
88 p_api_version IN NUMBER,
89 p_commit IN VARCHAR2 := fnd_api.g_false,
90 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
91 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
92 p_relationship_tbl IN OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
93 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2
97 );
98
99 PROCEDURE update_relationship
100 (
101 p_api_version IN NUMBER,
102 p_commit IN VARCHAR2 := fnd_api.g_false,
103 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
104 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
105 p_relationship_tbl IN csi_datastructures_pub.ii_relationship_tbl,
106 p_replace_flag IN VARCHAR2 := fnd_api.g_false,
107 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2
111 );
112
113 PROCEDURE expire_relationship
114 (
115 p_api_version IN NUMBER,
116 p_commit IN VARCHAR2 := fnd_api.g_false,
117 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
118 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
119 p_relationship_rec IN csi_datastructures_pub.ii_relationship_rec,
120 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
121 x_instance_id_lst OUT NOCOPY csi_datastructures_pub.id_tbl,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2
125 );
126
127 /*----------------------------------------------------------*/
128 /* Procedure name: Resolve_id_columns */
129 /* Description : This procudure gets the descriptions for */
130 /* id columns */
131 /*----------------------------------------------------------*/
132
133 PROCEDURE Resolve_id_columns
134 (
135 p_rel_history_tbl IN OUT NOCOPY csi_datastructures_pub.relationship_history_tbl
136 );
137
138 /*------------------------------------------------------------*/
139 /* Procedure name: get_inst_relationship_hist */
140 /* Description : Procedure used to get inst relationships */
141 /* from history for a given transaction_id */
142 /*------------------------------------------------------------*/
143
144 PROCEDURE get_inst_relationship_hist
145 (
146 p_api_version IN NUMBER
147 ,p_commit IN VARCHAR2 := fnd_api.g_false
148 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
149 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
150 ,p_transaction_id IN NUMBER
151 ,x_rel_history_tbl OUT NOCOPY csi_datastructures_pub.relationship_history_tbl
152 ,x_return_status OUT NOCOPY VARCHAR2
153 ,x_msg_count OUT NOCOPY NUMBER
154 ,x_msg_data OUT NOCOPY VARCHAR2
155 );
156
157
158 PROCEDURE Get_Next_Level
159 (p_object_id IN NUMBER,
160 p_relationship_id IN NUMBER,
161 p_subject_id IN NUMBER,
162 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
163 p_rel_type_code IN VARCHAR2,
164 p_time_stamp IN DATE,
165 p_active_relationship_only IN VARCHAR2,
166 p_active_instances_only IN VARCHAR2 DEFAULT fnd_api.g_true,
167 p_config_only IN VARCHAR2 DEFAULT fnd_api.g_false -- if true will retrieve instances with config keys
168 );
169 --
170 FUNCTION Parent_of
171 ( p_subject_id IN NUMBER,
172 p_rel_tbl IN csi_datastructures_pub.ii_relationship_tbl
173 ) RETURN NUMBER;
174 --
175
176 PROCEDURE check_for_object
177 ( p_subject_id IN NUMBER,
178 p_object_id IN NUMBER,
179 p_relationship_type_code IN VARCHAR2,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2);
183
184 FUNCTION Is_link_type
185 ( p_instance_id IN NUMBER
186 ) RETURN BOOLEAN;
187
188 FUNCTION relationship_for_link
189 ( p_instance_id IN NUMBER,
190 p_mode IN VARCHAR2,
191 p_relationship_id IN NUMBER
192 ) RETURN BOOLEAN;
193
194 PROCEDURE Get_Children
195 (p_relationship_query_rec IN csi_datastructures_pub.relationship_query_rec,
196 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
197 p_depth IN NUMBER,
198 p_active_relationship_only IN VARCHAR2,
199 p_active_instances_only IN VARCHAR2 DEFAULT fnd_api.g_true, -- should be passed as false only when un-expiring
200 p_config_only IN VARCHAR2 DEFAULT fnd_api.g_false, -- if true will retrieve instances with config keys
201 p_time_stamp IN DATE,
202 p_get_dfs IN VARCHAR2 DEFAULT FND_API.G_TRUE,
203 p_ii_relationship_level_tbl OUT NOCOPY csi_ii_relationships_pvt.ii_relationship_level_tbl,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2
207 );
208 --
209 PROCEDURE Get_Top_Most_Parent
210 ( p_subject_id IN NUMBER,
211 p_rel_type_code IN VARCHAR2,
212 p_object_id OUT NOCOPY NUMBER
213 );
214 PROCEDURE Get_Next_Level
215 (p_object_id IN NUMBER,
216 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl
217 );
218
219 PROCEDURE Get_Children
220 (p_object_id IN NUMBER,
221 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl
222 );
223
224 PROCEDURE Get_Immediate_Parents
225 ( p_subject_id IN NUMBER,
226 p_rel_type_code IN VARCHAR2,
227 p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl
228 );
229
230 TYPE ii_relationship_level_rec IS RECORD
231 (
232 relationship_id NUMBER := FND_API.G_MISS_NUM,
233 current_level NUMBER := FND_API.G_MISS_NUM
234 );
235
236 TYPE ii_relationship_level_tbl IS TABLE OF ii_relationship_level_rec
237 INDEX BY BINARY_INTEGER;
238
239 -- Begin Add Code for Siebel Genesis Project
240 FUNCTION Get_Root_Parent
241 (
242 p_subject_id IN NUMBER,
243 p_rel_type_code IN VARCHAR2,
244 p_object_id IN NUMBER
245 ) RETURN NUMBER;
246 -- End Add Code for Siebel Genesis Project
247
248 END csi_ii_relationships_pvt;