[Home] [Help]
PACKAGE: APPS.CSD_RO_BULLETINS_PVT
Source
1 PACKAGE CSD_RO_BULLETINS_PVT as
2 /* $Header: csdvrobs.pls 120.2.12010000.2 2008/08/08 17:38:15 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_RO_BULLETINS_PVT
5 -- Purpose : Jan-10-2008 rfieldma created
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 -- Default number of records fetch per call
11 G_DEFAULT_NUM_REC_FETCH CONSTANT NUMBER := 30;
12 G_L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
13 G_FREQ_ONE_REPAIR CONSTANT VARCHAR2(10) := 'ONE_REPAIR';
14 G_FREQ_ONE_INSTANCE CONSTANT VARCHAR2(12) := 'ONE_INSTANCE';
15 G_OBJ_VERSION_NUMBER_1 CONSTANT NUMBER := 1;
16 G_SOURCE_TYPE_RULE CONSTANT VARCHAR2(4) := 'RULE';
17
18 -- *******************************************************
19 -- Start of Comments
20 -- -------------------------------------------------------
21 -- Type name:CSD_RO_SC_IDS_TBL_TYPE
22 -- -------------------------------------------------------
23 -- Parameters:
24 --
25 -- Required:
26 -- Defaults:
27 -- History: Jan-16-2008 rfieldma created
28 --
29 -- End of Comments
30 --
31 TYPE CSD_RO_SC_IDS_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
32
33 -- *******************************************************
34 -- Start of Comments
35 -- -------------------------------------------------------
36 -- Record name:RO_BULLETIN_REC_TYPE
37 -- -------------------------------------------------------
38 -- Parameters:
39 -- RO_BULLETIN_ID
40 -- REPAIR_LINE_ID
41 -- BULLETIN_ID
42 -- LAST_VIEWED_DATE
43 -- LAST_VIEWED_BY
44 -- SOURCE_TYPE
45 -- SOURCE_ID
46 -- OBJECT_VERSION_NUMBER
47 -- CREATED_BY
48 -- CREATION_DATE
49 -- LAST_UPDATED_BY
50 -- LAST_UPDATE_DATE
51 -- LAST_UPDATE_LOGIN
52 --
53 -- Required:
54 -- Defaults: FND_API.G_MISS*
55 -- History: Jan-16-2008 rfieldma created
56 --
57 -- End of Comments
58 --
59 TYPE RO_BULLETIN_REC_TYPE IS RECORD
60 (
61 RO_BULLETIN_ID NUMBER := FND_API.G_MISS_NUM
62 ,REPAIR_LINE_ID NUMBER := FND_API.G_MISS_NUM
63 ,BULLETIN_ID NUMBER := FND_API.G_MISS_NUM
64 ,LAST_VIEWED_DATE DATE := FND_API.G_MISS_DATE
65 ,LAST_VIEWED_BY NUMBER := FND_API.G_MISS_NUM
66 ,SOURCE_TYPE VARCHAR2(30) := FND_API.G_MISS_CHAR
67 ,SOURCE_ID NUMBER := FND_API.G_MISS_NUM
68 ,OBJECT_VERSION_NUMBER NUMBER := FND_API.G_MISS_NUM
69 ,CREATED_BY NUMBER := FND_API.G_MISS_NUM
70 ,CREATION_DATE DATE := FND_API.G_MISS_DATE
71 ,LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM
72 ,LAST_UPDATE_DATE DATE := FND_API.G_MISS_DATE
73 ,LAST_UPDATE_LOGIN NUMBER := FND_API.G_MISS_NUM
74 );
75
76 G_MISS_RO_BULLETIN_REC RO_BULLETIN_REC_TYPE;
77 TYPE RO_BULLETIN_TBL_TYPE IS TABLE OF RO_BULLETIN_Rec_Type
78 INDEX BY BINARY_INTEGER;
79 G_MISS_RO_BULLETIN_TBL RO_BULLETIN_TBL_TYPE;
80
81 -- *******************************************************
82 -- Start of Comments
83 -- -------------------------------------------------------
84 -- Record name:TYPE_RO_BULLETIN_PARAMS_REC_TYPE
85 -- -------------------------------------------------------
86 -- This record holds all the params set by the user when running
87 -- the concurrent program to link repair orders to bulletins.
88 -- (LINK_BULLETINS_TO_REPAIRS_CONC_PROG)
89 -- Parameters:
90 -- BULLETIN_TYPE_CODE
91 -- RO_FLOW_STATUS_ID
92 -- RO_INV_ORG_ID
93 -- RO_REPAIR_ORG_ID
94 -- RO_INV_ITEM_ID
95 -- -------------------
96 -- End of Comments
97 -- -------------------
98 TYPE RO_BULLETIN_PARAMS_REC_TYPE IS RECORD
99 (
100 BULLETIN_TYPE_CODE VARCHAR2(30) := NULL
101 ,RO_FLOW_STATUS_ID NUMBER := NULL
102 ,RO_INV_ORG_ID NUMBER := NULL
103 ,RO_REPAIR_ORG_ID NUMBER := NULL
104 ,RO_INV_ITEM_ID NUMBER := NULL
105 );
106
107 -- *******************************************************
108 -- Start of Comments
109 -- *******************************************************
110 -- API Name: CREATE_RO_BULLETIN
111 -- Type : Private
112 -- Pre-Req :
113 -- Parameters:
114 -- IN
115 -- p_api_version_number IN NUMBER Required
116 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
117 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
118 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
119 -- P_RO_BULLETIN_Rec IN CSD_RO_BULLETIN_REC_TYPE Required
120 --
121 -- OUT:
122 -- x_return_status OUT NOCOPY VARCHAR2
123 -- x_msg_count OUT NOCOPY NUMBER
124 -- x_msg_data OUT NOCOPY VARCHAR2
125 -- x_RO_BULLETIN_ID OUT NOCOPY NUMBER
126 -- History: Jan-16-2008 rfieldma created
127 -- -------------------
128 -- End of Comments
129 -- -------------------
130 PROCEDURE CREATE_RO_BULLETIN(
131 p_api_version_number IN NUMBER,
132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
135 p_ro_bulletin_rec IN RO_BULLETIN_Rec_Type /*:= G_MISS_CSD_RO_BULLETIN_REC*/,
136 x_ro_bulletin_id OUT NOCOPY NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2
140 );
141
142 -- *******************************************************
143 -- Start of Comments
144 -- *******************************************************
145 -- API Name: UPDATE_RO_BULLETIN
146 -- Type : Private
147 -- Pre-Req :
148 -- Parameters:
149 -- IN
150 -- p_api_version_number IN NUMBER Required
151 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
152 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
153 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
154 -- P_RO_BULLETIN_Rec IN CSD_RO_BULLETIN_REC_TYPE Required
155 --
156 -- OUT:
157 -- x_return_status OUT NOCOPY VARCHAR2
158 -- x_msg_count OUT NOCOPY NUMBER
159 -- x_msg_data OUT NOCOPY VARCHAR2
160 -- History: Jan-16-2008 rfieldma created
161 -- -------------------
162 -- End of Comments
163 -- -------------------
164 PROCEDURE UPDATE_RO_BULLETIN(
165 p_api_version_number IN NUMBER,
166 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
167 p_commit IN VARCHAR2 := FND_API.G_FALSE,
168 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
169 p_ro_bulletin_rec IN RO_BULLETIN_Rec_Type,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2
173 );
174
175 -- *******************************************************
176 -- Start of Comments
177 -- *******************************************************
178 -- API Name: DELETE_RO_BULLETIN
179 -- Type : Private
180 -- Pre-Req :
181 -- Parameters:
182 -- IN
183 -- p_api_version_number IN NUMBER Required
184 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
185 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
186 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
187 -- p_ro_bulletin_id IN NUMBER Required
188 --
189 -- OUT:
190 -- x_return_status OUT NOCOPY VARCHAR2
191 -- x_msg_count OUT NOCOPY NUMBER
192 -- x_msg_data OUT NOCOPY VARCHAR2
193 -- History: Jan-16-2008 rfieldma created
194 -- -------------------
195 -- End of Comments
196 -- -------------------
197 PROCEDURE DELETE_RO_BULLETIN(
198 p_api_version_number IN NUMBER,
199 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
200 p_commit IN VARCHAR2 := FND_API.G_FALSE,
201 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
202 p_ro_bulletin_id IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2
206 );
207
208 -- *******************************************************
209 -- Start of Comments
210 -- *******************************************************
211 -- API Name: LOCK_RO_BULLETIN
212 -- Type : Private
213 -- Pre-Req :
214 -- Parameters:
215 -- IN
216 -- p_api_version_number IN NUMBER Required
217 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
218 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
219 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
220 -- p_ro_bulletin_rec IN RO_BULLETIN_REC_TYPE Required
221 -- OUT:
222 -- x_return_status OUT NOCOPY VARCHAR2
223 -- x_msg_count OUT NOCOPY NUMBER
224 -- x_msg_data OUT NOCOPY VARCHAR2
225 -- History: Jan-16-2008 rfieldma created
226 -- -------------------
227 -- End of Comments
228 -- -------------------
229 PROCEDURE LOCK_RO_BULLETIN(
230 p_api_version_number IN NUMBER,
231 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
232 p_commit IN VARCHAR2 := FND_API.G_FALSE,
233 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
234 p_ro_bulletin_rec IN RO_BULLETIN_Rec_Type,
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2
238 );
239
240
241 -- *******************************************************
242 -- Start of Comments
243 -- *******************************************************
244 -- API Name: LINK_BULLETINS_TO_RO
245 -- Type : Private
246 -- Pre-Req :
247 -- Parameters:
248 -- IN
249 -- p_api_version_number IN NUMBER Required
250 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
251 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
252 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
253 -- p_repair_line_id IN NUMBER Required
254 -- px_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE Required
255 -- OUT:
256 -- x_return_status OUT NOCOPY VARCHAR2
257 -- x_msg_count OUT NOCOPY NUMBER
258 -- x_msg_data OUT NOCOPY VARCHAR2
259 -- History: Jan-16-2008 rfieldma created
260 -- -------------------
261 -- End of Comments
262 -- -------------------
263 PROCEDURE LINK_BULLETINS_TO_RO(
264 p_api_version_number IN NUMBER,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 p_repair_line_id IN NUMBER,
269 px_ro_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2
273 );
274
275 -- *******************************************************
276 -- Start of Comments
277 -- *******************************************************
278 -- API Name: CREATE_NEW_RO_BULLETIN_LINK
279 -- Type : Private
280 -- Pre-Req :
281 -- Parameters:
282 -- IN
283 -- p_api_version_number IN NUMBER Required
284 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
285 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
286 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
287 -- p_repair_line_id IN NUMBER Required
288 -- p_bulletin_id IN NUMBER Required
289 -- p_rule_id IN NUMBER Required
290 -- px_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE Required
291 -- OUT:
292 -- x_return_status OUT NOCOPY VARCHAR2
293 -- x_msg_count OUT NOCOPY NUMBER
294 -- x_msg_data OUT NOCOPY VARCHAR2
295 -- History: Jan-17-2008 rfieldma created
296 -- -------------------
297 -- End of Comments
298 -- -------------------
299 PROCEDURE CREATE_NEW_RO_BULLETIN_LINK(
300 p_api_version_number IN NUMBER,
301 p_commit IN VARCHAR2,
302 p_init_msg_list IN VARCHAR2,
303 p_validation_level IN NUMBER,
304 p_repair_line_id IN NUMBER,
305 p_bulletin_id IN NUMBER,
306 p_rule_id IN NUMBER,
307 px_ro_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2
311 );
312
313 -- *******************************************************
314 -- Start of Comments
315 -- *******************************************************
316 -- API Name: GET_CSD_REPAIRS_OBJ_VER_NUM
317 -- Type : Private
318 -- Pre-Req :
319 -- Parameters:
320 -- IN
321 -- p_repair_line_id IN NUMBER Required
322 -- OUT:
323 -- NUMBER obj_ver_num
324 -- History: Jan-17-2008 rfieldma created
325 -- -------------------
326 -- End of Comments
327 -- -------------------
328 FUNCTION GET_CSD_REPAIRS_OBJ_VER_NUM(
329 p_repair_line_id IN NUMBER
330 ) RETURN NUMBER;
331
332 /*--------------------------------------------------------------------*/
333 /* procedure name: LINK_BULLETINS_TO_REPAIRS_CP */
334 /* description : Links all active bulletins to all matching repairs */
335 /* */
336 /* STANDARD PARAMETERS */
337 /* In Parameters : */
338 /* */
339 /* Output Parameters: */
340 /* errbuf VARCHAR2 Error message */
341 /* retcode VARCHAR2 Error Code */
342 /* */
343 /* NON-STANDARD PARAMETERS */
344 /* In Parameters */
345 /* p_repair_line_id NUMBER Optional Repair Order Line Id */
346 /* */
347 /* */
348 /*--------------------------------------------------------------------*/
349 PROCEDURE LINK_BULLETINS_TO_REPAIRS_CP (
350 errbuf OUT NOCOPY varchar2,
351 retcode OUT NOCOPY varchar2,
352 --concurrent program parameters go here
353 p_BULLETIN_TYPE_CODE IN VARCHAR2 := NULL,
354 p_RO_FLOW_STATUS_ID IN NUMBER := NULL,
355 p_RO_INV_ORG_ID IN NUMBER := NULL,
356 p_RO_REPAIR_ORG_ID IN NUMBER := NULL,
357 p_RO_INV_ITEM_ID IN NUMBER := NULL);
358
359 /*--------------------------------------------------------------------*/
360 /* procedure name: LINK_BULLETIN_FOR_RULE */
361 /* description : Given a single rule, find all matching repair orders */
362 /* and link them to the given bulletin, if applicable */
363 /* */
364 /* Called from : PROCEDURE LINK_BULLETINS_TO_REPAIRS_CP */
365 /* Input Parm : */
366 /* p_bulletin_rule_id NUMBER Req */
367 /* p_bulletin_id NUMBER Req */
368 /* */
369 /* */
370 /* */
371 /*--------------------------------------------------------------------*/
372 PROCEDURE LINK_BULLETIN_FOR_RULE (
373 p_api_version_number IN NUMBER,
374 p_commit IN VARCHAR2,
375 p_init_msg_list IN VARCHAR2,
376 p_validation_level IN NUMBER,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_count OUT NOCOPY NUMBER,
379 x_msg_data OUT NOCOPY VARCHAR2,
380 p_bulletin_id IN NUMBER := NULL,
381 p_bulletin_rule_id IN NUMBER,
382 p_params IN RO_BULLETIN_PARAMS_REC_TYPE
383 );
384
385
386 /*--------------------------------------------------------------------*/
387 /* procedure name: APPLY_BULLETIN_SCS_TO_RO */
388 /* description : Given set of service codes from a service bulletin */
389 /* mark them as applicable for a repair order */
390 /* */
391 /* Called from : PROCEDURE LINK_BULLETIN_FOR_RULE */
392 /* Input Parm : */
393 /* p_service_codes CSD_RO_SC_IDS_TBL_TYPE Req */
394 /* p_repair_line_id NUMBER Req */
395 /* */
396 /* */
397 /* */
398 /*--------------------------------------------------------------------*/
399 PROCEDURE APPLY_BULLETIN_SCS_TO_RO (
400 p_api_version_number IN NUMBER,
401 p_commit IN VARCHAR2,
402 p_init_msg_list IN VARCHAR2,
403 p_validation_level IN NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2,
407 p_service_codes IN CSD_RO_SC_IDS_TBL_TYPE,
408 p_repair_line_id IN NUMBER
409 );
410
411 END CSD_RO_BULLETINS_PVT; /* package spec ends */