1 PACKAGE csi_asset_pvt AS
2 /* $Header: csivaas.pls 120.5 2006/11/17 06:38:09 sumathur noship $ */
3
4 --
5 TYPE LOOKUP_REC IS RECORD
6 (
7 lookup_code VARCHAR2(30) := FND_API.G_MISS_CHAR
8 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
9 );
10
11 TYPE LOOKUP_TBL IS TABLE OF LOOKUP_REC INDEX BY BINARY_INTEGER;
12 --
13 TYPE ASSET_COUNT_REC IS RECORD
14 (
15 asset_count NUMBER := FND_API.G_MISS_NUM
16 ,lookup_count NUMBER := FND_API.G_MISS_NUM
17 ,loc_count NUMBER := FND_API.G_MISS_NUM
18 );
19 --
20 TYPE ASSET_ID_REC IS RECORD
21 (
22 asset_id NUMBER := FND_API.G_MISS_NUM
23 ,asset_book_type VARCHAR2(15) := FND_API.G_MISS_CHAR
24 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
25 );
26
27 TYPE ASSET_ID_TBL IS TABLE OF ASSET_ID_REC INDEX BY BINARY_INTEGER;
28 --
29 TYPE ASSET_LOC_REC IS RECORD
30 (
31 asset_loc_id NUMBER := FND_API.G_MISS_NUM
32 ,valid_flag VARCHAR2(1) := FND_API.G_MISS_CHAR
33 );
34
35 TYPE ASSET_LOC_TBL IS TABLE OF ASSET_LOC_REC INDEX BY BINARY_INTEGER;
36 --
37
38 /*-- These datastructures are added to implement asset sync --*/
39 TYPE instance_asset_sync_rec IS RECORD
40 (
41 instance_id NUMBER := FND_API.G_MISS_NUM,
42 inst_interface_id NUMBER := FND_API.G_MISS_NUM,
43 fa_asset_id NUMBER := FND_API.G_MISS_NUM,
44 fa_location_id NUMBER := FND_API.G_MISS_NUM,
45 inst_asset_quantity NUMBER := FND_API.G_MISS_NUM
46 );
47 TYPE instance_asset_sync_tbl IS TABLE OF instance_asset_sync_rec INDEX BY BINARY_INTEGER;
48
49 TYPE fa_asset_sync_rec IS RECORD
50 (
51 fa_asset_id NUMBER := FND_API.G_MISS_NUM,
52 fa_location_id NUMBER := FND_API.G_MISS_NUM,
53 fa_asset_quantity NUMBER := FND_API.G_MISS_NUM,
54 sync_up_quantity NUMBER := FND_API.G_MISS_NUM
55 );
56 TYPE fa_asset_sync_tbl IS TABLE OF fa_asset_sync_rec INDEX BY BINARY_INTEGER;
57
58 TYPE instance_sync_rec IS RECORD
59 (
60 instance_id NUMBER := FND_API.G_MISS_NUM,
61 inst_interface_id NUMBER := FND_API.G_MISS_NUM,
62 instance_quantity NUMBER := FND_API.G_MISS_NUM,
63 sync_up_quantity NUMBER := FND_API.G_MISS_NUM,
64 vld_status VARCHAR2(1) := FND_API.G_MISS_CHAR,
65 hop NUMBER := FND_API.G_MISS_NUM,
66 location_id NUMBER := FND_API.G_MISS_NUM,
67 location_type_code VARCHAR2(30) := FND_API.G_MISS_CHAR
68 );
69 TYPE instance_sync_tbl IS TABLE OF instance_sync_rec INDEX BY BINARY_INTEGER;
70
71 /*--End These datastructures are added for implement asset sync --*/
72
73 /*----------------------------------------------------------*/
74 /* Procedure name: Initialize_asset_rec */
75 /* Description : This procudure recontructs the record */
76 /* from the history */
77 /*----------------------------------------------------------*/
78
79 PROCEDURE Initialize_asset_rec
80 (
81 x_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_rec,
82 p_inst_asset_hist_id IN NUMBER ,
83 x_nearest_full_dump IN OUT NOCOPY DATE
84 );
85
86 /*----------------------------------------------------------*/
87 /* Procedure name: Construct_asset_from_hist */
88 /* Description : This procudure recontructs the record */
89 /* from the history */
90 /*----------------------------------------------------------*/
91
92 PROCEDURE Construct_asset_from_hist
93 (
94 x_instance_asset_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl,
95 p_time_stamp IN DATE
96 );
97
98 /*----------------------------------------------------------*/
99 /* Procedure name: Get_Asset_Column_Values */
100 /* Description : This procudure gets the column values */
101 /* for the Dynamic SQL */
102 /*----------------------------------------------------------*/
103
104 PROCEDURE Get_Asset_Column_Values
105 (
106 p_get_asset_cursor_id IN NUMBER ,
107 x_inst_asset_rec OUT NOCOPY csi_datastructures_pub.instance_asset_header_rec );
108
109 /*----------------------------------------------------------*/
110 /* Procedure name: Define_Asset_Columns */
111 /* Description : This procudure defines the columns */
112 /* for the Dynamic SQL */
113 /*----------------------------------------------------------*/
114
115 PROCEDURE Define_Asset_Columns
116 (
117 p_get_asset_cursor_id IN NUMBER ) ;
118
119 /*----------------------------------------------------------*/
120 /* Procedure name: Bind_asset_variable */
121 /* Description : Procedure used to generate the where */
122 /* cluase for Party relationship */
123 /*----------------------------------------------------------*/
124
125 PROCEDURE Bind_asset_variable
126 (
127 p_inst_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec,
128 p_get_asset_cursor_id IN NUMBER );
129
130 /*----------------------------------------------------------*/
131 /* Procedure name: Resolve_id_columns */
132 /* Description : This procudure gets the descriptions for */
133 /* id columns */
134 /*----------------------------------------------------------*/
135
136 PROCEDURE Resolve_id_columns
137 ( p_asset_header_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl);
138
139 /*----------------------------------------------------------*/
140 /* Procedure name: Gen_Asset_Where_Clause */
141 /* Description : Procedure used to generate the where */
142 /* cluase for Party relationship */
143 /*----------------------------------------------------------*/
144
145 PROCEDURE Gen_Asset_Where_Clause
146 ( p_inst_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec
147 ,x_where_clause OUT NOCOPY VARCHAR2 );
148
149 /*-------------------------------------------------------*/
150 /* procedure name: Get_instance_assets */
151 /* description : Get information about the assets */
152 /* associated with an item instance. */
153 /*-------------------------------------------------------*/
154
155 PROCEDURE get_instance_assets
156 (
157 p_api_version IN NUMBER
158 ,p_commit IN VARCHAR2 := fnd_api.g_false
159 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
160 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
161 ,p_instance_asset_query_rec IN csi_datastructures_pub.instance_asset_query_rec
162 ,p_resolve_id_columns IN VARCHAR2 := fnd_api.g_false
163 ,p_time_stamp IN DATE
164 ,x_instance_asset_tbl OUT NOCOPY csi_datastructures_pub.instance_asset_header_tbl
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 );
169
170 /*-------------------------------------------------------*/
171 /* procedure name: Create_instance_asset */
172 /* description : Creates new association between an */
173 /* asset and an item instance */
174 /*-------------------------------------------------------*/
175
176 PROCEDURE create_instance_asset
177 (
178 p_api_version IN NUMBER
179 ,p_commit IN VARCHAR2 := fnd_api.g_false
180 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
181 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
182 ,p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec
183 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER
186 ,x_msg_data OUT NOCOPY VARCHAR2
187 ,p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl
188 ,p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec
189 ,p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl
190 ,p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl
191 ,p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
192 );
193
194 /*-------------------------------------------------------*/
195 /* procedure name: Update_instance_asset */
196 /* description : Updates an existing instance-asset */
197 /* association */
198 /*-------------------------------------------------------*/
199
200 PROCEDURE update_instance_asset
201 (
202 p_api_version IN NUMBER
203 ,p_commit IN VARCHAR2 := fnd_api.g_false
204 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
205 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
206 ,p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec
207 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
208 ,x_return_status OUT NOCOPY VARCHAR2
209 ,x_msg_count OUT NOCOPY NUMBER
210 ,x_msg_data OUT NOCOPY VARCHAR2
211 ,p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl
212 ,p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec
213 ,p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl
214 ,p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl
215 );
216
217 /*-------------------------------------------------------*/
218 /* procedure name: get_instance_asset_hist */
219 /* description : Retreives asset history for */
220 /* a given transaction */
221 /*-------------------------------------------------------*/
222
223
224 PROCEDURE get_instance_asset_hist
225 ( p_api_version IN NUMBER
226 ,p_commit IN VARCHAR2 := fnd_api.g_false
227 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
228 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
229 ,p_transaction_id IN NUMBER
230 ,x_ins_asset_hist_tbl OUT NOCOPY csi_datastructures_pub.ins_asset_history_tbl
231 ,x_return_status OUT NOCOPY VARCHAR2
232 ,x_msg_count OUT NOCOPY NUMBER
233 ,x_msg_data OUT NOCOPY VARCHAR2
234 ) ;
235
236 /*-- These procedures are added to implement asset sync --*/
237
238 PROCEDURE asset_syncup_validation
239 ( px_instance_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
240 px_instance_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
241 px_fa_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
242 x_error_msg OUT NOCOPY VARCHAR2,
243 x_return_status OUT NOCOPY VARCHAR2
244 );
245
246 PROCEDURE get_attached_item_instances
247 ( p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2,
249 p_instance_asset_sync_tbl IN CSI_ASSET_PVT.instance_asset_sync_tbl,
250 x_instance_sync_tbl OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_source_system_name IN VARCHAR2 DEFAULT NULL,
255 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
256 );
257
258 PROCEDURE get_attached_asset_links
259
260 ( p_api_version IN NUMBER,
261 p_init_msg_list IN VARCHAR2,
262 p_instance_sync_tbl IN CSI_ASSET_PVT.instance_sync_tbl,
263 x_instance_asset_sync_tbl OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_count OUT NOCOPY NUMBER,
266 x_msg_data OUT NOCOPY VARCHAR2,
267 p_source_system_name IN VARCHAR2 DEFAULT NULL,
268 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
269 );
270
271 PROCEDURE get_fa_asset_details
272 ( p_api_version IN NUMBER,
273 p_init_msg_list IN VARCHAR2,
274 p_instance_asset_sync_tbl IN CSI_ASSET_PVT.instance_asset_sync_tbl,
275 x_fa_asset_sync_tab OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 p_source_system_name IN VARCHAR2 DEFAULT NULL,
283 PROCEDURE Get_syncup_tree
280 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
281 );
282
284 ( px_instance_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_sync_tbl,
285 px_instance_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.instance_asset_sync_tbl,
286 x_fa_asset_sync_tbl IN OUT NOCOPY CSI_ASSET_PVT.fa_asset_sync_tbl,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_error_msg OUT NOCOPY VARCHAR2,
289 p_source_system_name IN VARCHAR2 DEFAULT NULL,
290 p_called_from_grp IN VARCHAR2 DEFAULT fnd_api.g_false
291 );
292 /*-- End These procedures are added to implement asset sync --*/
293
294 PROCEDURE create_instance_assets (
295 p_api_version IN number,
296 p_commit IN varchar2,
297 p_init_msg_list IN varchar2,
298 p_validation_level IN number,
299 p_instance_asset_tbl IN OUT nocopy csi_datastructures_pub.instance_asset_tbl,
300 p_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
301 p_lookup_tbl IN OUT nocopy csi_asset_pvt.lookup_tbl,
302 p_asset_count_rec IN OUT nocopy csi_asset_pvt.asset_count_rec,
303 p_asset_id_tbl IN OUT nocopy csi_asset_pvt.asset_id_tbl,
304 p_asset_loc_tbl IN OUT nocopy csi_asset_pvt.asset_loc_tbl,
305 x_return_status OUT nocopy varchar2,
306 x_msg_count OUT nocopy number,
307 x_msg_data OUT nocopy varchar2);
308 --
309 PROCEDURE set_fa_sync_flag (
310 px_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
311 p_location_id IN NUMBER DEFAULT -9999, -- should be passed only from GRP API (Create)
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_error_msg OUT NOCOPY VARCHAR2);
314 END csi_asset_pvt;