1 PACKAGE csi_party_relationships_pvt AUTHID CURRENT_USER AS
2 /* $Header: csivips.pls 120.2.12000000.1 2007/01/16 15:39:59 appldev ship $ */
3
4
5 g_force_expire_flag VARCHAR2(1) := 'N';
6
7
8 /*------------------------------------------------------*/
9 /* Declare the PL/SQL tables used by the validation API */
10 /*------------------------------------------------------*/
11
12 TYPE PARTY_SOURCE_TAB_REC IS RECORD
13 (
14 party_source_table VARCHAR2(30) := FND_API.G_MISS_CHAR
15 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
16 );
17
18 TYPE PARTY_SOURCE_TBL IS TABLE OF PARTY_SOURCE_TAB_REC INDEX BY BINARY_INTEGER;
19 --
20 TYPE PARTY_ID_REC IS RECORD
21 (
22 party_id NUMBER := FND_API.G_MISS_NUM
23 ,party_source_table VARCHAR2(30) := FND_API.G_MISS_CHAR
24 ,contact_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
25 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
26 );
27
28 TYPE PARTY_ID_TBL IS TABLE OF PARTY_ID_REC INDEX BY BINARY_INTEGER;
29 --
30 TYPE CONTACT_REC IS RECORD
31 (
32 contact_party_id NUMBER := FND_API.G_MISS_NUM
33 ,party_source_table VARCHAR2(30) := FND_API.G_MISS_CHAR
34 ,contact_ip_id NUMBER := FND_API.G_MISS_NUM
35 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
36 );
37
38 TYPE CONTACT_TBL IS TABLE OF CONTACT_REC INDEX BY BINARY_INTEGER;
39 --
40 TYPE PARTY_REL_TYPE_REC IS RECORD
41 (
42 rel_type_code VARCHAR2(30) := FND_API.G_MISS_CHAR
43 ,contact_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
44 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
45 );
46
47 TYPE PARTY_REL_TYPE_TBL IS TABLE OF PARTY_REL_TYPE_REC INDEX BY BINARY_INTEGER;
48 --
49 TYPE PARTY_COUNT_REC IS RECORD
50 (
51 party_source_count NUMBER := FND_API.G_MISS_NUM
52 ,party_id_count NUMBER := FND_API.G_MISS_NUM
53 ,contact_id_count NUMBER := FND_API.G_MISS_NUM
54 ,rel_type_count NUMBER := FND_API.G_MISS_NUM
55 );
56 --
57 TYPE INST_PARTY_REC IS RECORD
58 (
59 instance_party_id NUMBER := FND_API.G_MISS_NUM
60 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
61 );
62 TYPE INST_PARTY_TBL IS TABLE OF INST_PARTY_REC INDEX BY BINARY_INTEGER;
63 --
64 TYPE ACCT_REL_TYPE_REC IS RECORD
65 (
66 rel_type_code VARCHAR2(30) := FND_API.G_MISS_CHAR
67 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
68 );
69 TYPE ACCT_REL_TYPE_TBL IS TABLE OF ACCT_REL_TYPE_REC INDEX BY BINARY_INTEGER;
70 --
71 TYPE SITE_USE_REC IS RECORD
72 (
73 Site_use_id NUMBER := FND_API.G_MISS_NUM
74 ,site_use_code VARCHAR2(30) := FND_API.G_MISS_CHAR
75 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
76 );
77 TYPE SITE_USE_TBL IS TABLE OF SITE_USE_REC INDEX BY BINARY_INTEGER;
78 --
79 TYPE ACCOUNT_COUNT_REC IS RECORD
80 (
81 inst_party_count NUMBER := FND_API.G_MISS_NUM
82 ,rel_type_count NUMBER := FND_API.G_MISS_NUM
83 ,site_use_count NUMBER := FND_API.G_MISS_NUM
84 );
85 --
86 TYPE ACCT_ID_REC IS RECORD
87 (
88 account_id NUMBER
89 ,valid_flag VARCHAR2(1)
90 );
91 TYPE ACCT_ID_TBL IS TABLE OF ACCT_ID_REC INDEX BY BINARY_INTEGER;
92 --
93
94 /*----------------------------------------------------------*/
95 /* Procedure name: Initialize_acct_rec */
96 /* Description : This procudure recontructs the record */
97 /* from the history */
98 /*----------------------------------------------------------*/
99
100 PROCEDURE Initialize_acct_rec
101 (
102 x_party_account_rec IN OUT NOCOPY csi_datastructures_pub.party_account_header_rec,
103 p_ip_account_hist_id IN NUMBER ,
104 x_nearest_full_dump IN OUT NOCOPY DATE );
105
106 /*----------------------------------------------------------*/
107 /* Procedure name: Construct_pty_from_hist */
108 /* Description : This procudure recontructs the record */
109 /* from the history */
110 /*----------------------------------------------------------*/
111
112 PROCEDURE Construct_acct_from_hist
113 (
114 x_party_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_header_tbl,
115 p_time_stamp IN DATE ) ;
116
117 /*----------------------------------------------------------*/
118 /* Procedure name: Resolve_id_columns */
119 /* Description : This procudure gets the descriptions for */
120 /* id columns */
121 /*----------------------------------------------------------*/
122
123 PROCEDURE Resolve_id_columns
124 (p_account_header_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_header_tbl);
125
126 /*----------------------------------------------------------*/
127 /* Procedure name: Get_Acct_Column_Values */
128 /* Description : This procudure gets the column values */
129 /* for the Dynamic SQL */
130 /*----------------------------------------------------------*/
131
132 PROCEDURE Get_Acct_Column_Values
133 (
134 p_get_acct_cursor_id IN NUMBER ,
135 x_pty_acct_query_rec OUT NOCOPY csi_datastructures_pub.party_account_header_rec );
136
137 /*----------------------------------------------------------*/
138 /* Procedure name: Define_Pty_Columns */
139 /* Description : This procudure defines the columns */
140 /* for the Dynamic SQL */
141 /*----------------------------------------------------------*/
142
143 PROCEDURE Define_Acct_Columns
144 (
145 p_get_acct_cursor_id IN NUMBER ) ;
146
147
148 /*----------------------------------------------------------*/
149 /* Procedure name: Bind_Acct_variable */
150 /* Description : Procedure used to generate the where */
151 /* cluase for Party relationship */
152 /*----------------------------------------------------------*/
153
154 PROCEDURE Bind_Acct_variable
155 (
156 p_pty_acct_query_rec IN csi_datastructures_pub.party_account_query_rec,
157 p_get_acct_cursor_id IN NUMBER );
158
159
160
161 /*----------------------------------------------------------*/
162 /* Procedure name: Gen_Acct_Where_Clause */
163 /* Description : Procedure used to generate the where */
164 /* cluase for Party relationship */
165 /*----------------------------------------------------------*/
166
167 PROCEDURE Gen_Acct_Where_Clause
168 (
169 p_pty_acct_query_rec IN csi_datastructures_pub.party_account_query_rec
170 ,x_where_clause OUT NOCOPY VARCHAR2 );
171
172
173 /*----------------------------------------------------------*/
174 /* Procedure name: Get_Pty_Column_Values */
175 /* Description : This procudure gets the column values */
176 /* for the Dynamic SQL */
177 /*----------------------------------------------------------*/
178
179 PROCEDURE Get_Pty_Column_Values
180 (
181 p_get_pty_cursor_id IN NUMBER ,
182 x_party_rec OUT NOCOPY csi_datastructures_pub.party_header_rec );
183
184
185 /*----------------------------------------------------------*/
186 /* Procedure name: Define_Pty_Columns */
187 /* Description : This procudure defines the columns */
188 /* for the Dynamic SQL */
189 /*----------------------------------------------------------*/
190
191 PROCEDURE Define_Pty_Columns
192 (
193 p_get_pty_cursor_id IN NUMBER ) ;
194
195 /*----------------------------------------------------------*/
196 /* Procedure name: Bind_Pty_variable */
197 /* Description : Procedure used to generate the where */
198 /* cluase for Party relationship */
199 /*----------------------------------------------------------*/
200
201 PROCEDURE Bind_Pty_variable
202 (
203 p_party_query_rec IN csi_datastructures_pub.party_query_rec,
204 p_cur_get_pty_rel IN NUMBER );
205
206 /*----------------------------------------------------------*/
207 /* Procedure name: Gen_Pty_Where_Clause */
208 /* Description : Procedure used to generate the where */
209 /* cluase for Party relationship */
210 /*----------------------------------------------------------*/
211
212 PROCEDURE Gen_Pty_Where_Clause
213 (
214 p_party_query_rec IN csi_datastructures_pub.party_query_rec
215 ,x_where_clause OUT NOCOPY VARCHAR2 );
216
217 /*----------------------------------------------------------*/
218 /* Procedure name: Construct_pty_from_hist */
219 /* Description : This procudure recontructs the record */
220 /* from the history */
221 /*----------------------------------------------------------*/
222
223 PROCEDURE Initialize_pty_rec
224 (
225 x_party_rec IN OUT NOCOPY csi_datastructures_pub.party_header_rec,
226 p_inst_party_hist_id IN NUMBER ,
227 x_nearest_full_dump IN OUT NOCOPY DATE );
228
229
230 /*----------------------------------------------------------*/
231 /* Procedure name: Construct_pty_from_hist */
232 /* Description : This procudure recontructs the record */
233 /* from the history */
234 /*----------------------------------------------------------*/
235
236 PROCEDURE Construct_pty_from_hist
237 (
238 x_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_header_tbl,
239 p_time_stamp IN DATE ) ;
240
241
242 /*-----------------------------------------------------------*/
243 /* Procedure name: Create_inst_party_realationships */
244 /* Description : Procedure used to create new instance-party */
245 /* relationships */
246 /*-----------------------------------------------------------*/
247
248 PROCEDURE create_inst_party_relationship
249 ( p_api_version IN NUMBER
250 ,p_commit IN VARCHAR2 := fnd_api.g_false
251 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
252 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
253 ,p_party_rec IN OUT NOCOPY csi_datastructures_pub.party_rec
254 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
255 ,x_return_status OUT NOCOPY VARCHAR2
256 ,x_msg_count OUT NOCOPY NUMBER
257 ,x_msg_data OUT NOCOPY VARCHAR2
258 ,p_party_source_tbl IN OUT NOCOPY csi_party_relationships_pvt.party_source_tbl
259 ,p_party_id_tbl IN OUT NOCOPY csi_party_relationships_pvt.party_id_tbl
260 ,p_contact_tbl IN OUT NOCOPY csi_party_relationships_pvt.contact_tbl
261 ,p_party_rel_type_tbl IN OUT NOCOPY csi_party_relationships_pvt.party_rel_type_tbl
262 ,p_party_count_rec IN OUT NOCOPY csi_party_relationships_pvt.party_count_rec
263 ,p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
264 );
265
266
267 /*---------------------------------------------------------*/
268 /* Procedure name: Update_inst_party_relationship */
269 /* Description : Procedure used to update the existing */
270 /* instance -party relationships */
271 /*---------------------------------------------------------*/
272
273
274 PROCEDURE update_inst_party_relationship
275 ( p_api_version IN NUMBER
276 ,p_commit IN VARCHAR2 := fnd_api.g_false
277 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
278 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
279 ,p_party_rec IN OUT NOCOPY csi_datastructures_pub.party_rec
280 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
281 ,x_return_status OUT NOCOPY VARCHAR2
282 ,x_msg_count OUT NOCOPY NUMBER
283 ,x_msg_data OUT NOCOPY VARCHAR2 );
284
285
286 /*---------------------------------------------------------*/
287 /* Procedure name: Expire_inst_party_relationship */
288 /* Description : Procedure used to expire an existing */
289 /* instance -party relationships */
290 /*---------------------------------------------------------*/
291
292 PROCEDURE expire_inst_party_relationship
293 ( p_api_version IN NUMBER
294 ,p_commit IN VARCHAR2 := fnd_api.g_false
295 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
296 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
297 ,p_instance_party_rec IN csi_datastructures_pub.party_rec
298 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
299 ,x_return_status OUT NOCOPY VARCHAR2
300 ,x_msg_count OUT NOCOPY NUMBER
301 ,x_msg_data OUT NOCOPY VARCHAR2 );
302
303 /*-------------------------------------------------------------*/
304 /* Procedure name: Create_inst_party_account */
305 /* Description : Procedure used to create new */
306 /* instance-party account relationships */
307 /*-------------------------------------------------------------*/
308
309 PROCEDURE create_inst_party_account
310 ( p_api_version IN NUMBER
311 ,p_commit IN VARCHAR2 := fnd_api.g_false
312 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
313 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
314 ,p_party_account_rec IN OUT NOCOPY csi_datastructures_pub.party_account_rec
315 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
316 ,x_return_status OUT NOCOPY VARCHAR2
317 ,x_msg_count OUT NOCOPY NUMBER
318 ,x_msg_data OUT NOCOPY VARCHAR2
319 ,p_inst_party_tbl IN OUT NOCOPY csi_party_relationships_pvt.inst_party_tbl
320 ,p_acct_rel_type_tbl IN OUT NOCOPY csi_party_relationships_pvt.acct_rel_type_tbl
321 ,p_site_use_tbl IN OUT NOCOPY csi_party_relationships_pvt.site_use_tbl
322 ,p_account_count_rec IN OUT NOCOPY csi_party_relationships_pvt.account_count_rec
323 ,p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
324 ,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
325 );
326
327 /*--------------------------------------------------------*/
328 /* Procedure name: Update_inst_party_account */
329 /* Description : Procedure used to update the existing */
330 /* instance-party account relationships */
331 /*--------------------------------------------------------*/
332
333 PROCEDURE update_inst_party_account
334 ( p_api_version IN NUMBER
335 ,p_commit IN VARCHAR2 := fnd_api.g_false
336 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
337 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
338 ,p_party_account_rec IN csi_datastructures_pub.party_account_rec
339 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
340 ,p_oks_txn_inst_tbl IN OUT NOCOPY oks_ibint_pub.txn_instance_tbl
341 ,x_return_status OUT NOCOPY VARCHAR2
342 ,x_msg_count OUT NOCOPY NUMBER
343 ,x_msg_data OUT NOCOPY VARCHAR2 );
344
345 /*---------------------------------------------------------*/
346 /* Procedure name: Expire_inst_party_account */
347 /* Description : Procedure used to expire an existing */
348 /* instance-party account relationships */
349 /*---------------------------------------------------------*/
350
351
352 PROCEDURE expire_inst_party_account
353 ( p_api_version IN NUMBER
354 ,p_commit IN VARCHAR2 := fnd_api.g_false
355 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
356 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
357 ,p_party_account_rec IN csi_datastructures_pub.party_account_rec
358 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
359 ,x_return_status OUT NOCOPY VARCHAR2
360 ,x_msg_count OUT NOCOPY NUMBER
361 ,x_msg_data OUT NOCOPY VARCHAR2 );
362
363
364 /*---------------------------------------------------------*/
365 /* Procedure name: get_contact_details */
366 /* Description : Procedure used to get */
367 /* contact details */
368 /*---------------------------------------------------------*/
369
370
371 PROCEDURE get_contact_details
372 (
373 p_api_version IN NUMBER
374 ,p_commit IN VARCHAR2 := fnd_api.g_false
375 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
376 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
377 ,p_contact_party_id IN NUMBER
378 ,p_contact_flag IN VARCHAR2
379 ,p_party_tbl IN VARCHAR2
380 ,x_contact_details OUT NOCOPY csi_datastructures_pub.contact_details_rec
381 ,x_return_status OUT NOCOPY VARCHAR2
382 ,x_msg_count OUT NOCOPY NUMBER
383 ,x_msg_data OUT NOCOPY VARCHAR2 );
384
385
386 /*------------------------------------------------------------*/
387 /* Procedure name: get_inst_party_rel_hist */
388 /* Description : Procedure used to get party relationships */
389 /* from history given a transaction_id */
390 /*------------------------------------------------------------*/
391
392 PROCEDURE get_inst_party_rel_hist
393 ( p_api_version IN NUMBER
394 ,p_commit IN VARCHAR2 := fnd_api.g_false
395 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
396 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
397 ,p_transaction_id IN NUMBER
398 ,x_party_history_tbl OUT NOCOPY csi_datastructures_pub.party_history_tbl
399 ,x_return_status OUT NOCOPY VARCHAR2
400 ,x_msg_count OUT NOCOPY NUMBER
401 ,x_msg_data OUT NOCOPY VARCHAR2
402 );
403
404
405
406 /*------------------------------------------------------------*/
407 /* Procedure name: get_inst_party_account_hist */
408 /* Description : Procedure used to get party account */
409 /* history given a transaction_id */
410 /*------------------------------------------------------------*/
411
412 PROCEDURE get_inst_party_account_hist
413 ( p_api_version IN NUMBER
414 ,p_commit IN VARCHAR2 := fnd_api.g_false
415 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
416 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
417 ,p_transaction_id IN NUMBER
418 ,x_account_history_tbl OUT NOCOPY csi_datastructures_pub.account_history_tbl
419 ,x_return_status OUT NOCOPY VARCHAR2
420 ,x_msg_count OUT NOCOPY NUMBER
421 ,x_msg_data OUT NOCOPY VARCHAR2
422 );
423
424
425
426
427 END csi_party_relationships_pvt ;
428