[Home] [Help]
PACKAGE: APPS.OE_HOLDS_PVT
Source
1 PACKAGE OE_Holds_Pvt AS
2 /* $Header: OEXVHLDS.pls 120.2.12010000.4 2008/10/22 14:22:49 kshashan ship $ */
3
4 -- Hold Source
5 TYPE Hold_Source_Rec_Type IS RECORD (
6 HOLD_SOURCE_ID OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
7 , LAST_UPDATE_DATE OE_Hold_Sources_ALL.LAST_UPDATE_DATE%TYPE
8 , LAST_UPDATED_BY OE_Hold_Sources_ALL.LAST_UPDATED_BY%TYPE
9 , CREATION_DATE OE_Hold_Sources_ALL.CREATION_DATE%TYPE
10 , CREATED_BY OE_Hold_Sources_ALL.CREATED_BY%TYPE
11 , LAST_UPDATE_LOGIN OE_Hold_Sources_ALL.LAST_UPDATE_LOGIN%TYPE
12 , PROGRAM_APPLICATION_ID OE_Hold_Sources_ALL.PROGRAM_APPLICATION_ID%TYPE
13 , PROGRAM_ID OE_Hold_Sources_ALL.PROGRAM_ID%TYPE
14 , PROGRAM_UPDATE_DATE OE_Hold_Sources_ALL.PROGRAM_UPDATE_DATE%TYPE
15 , REQUEST_ID OE_Hold_Sources_ALL.REQUEST_ID%TYPE
16 , HOLD_ID OE_Hold_Sources_ALL.HOLD_ID%TYPE
17 , HOLD_ENTITY_CODE OE_Hold_Sources_ALL.HOLD_ENTITY_CODE%TYPE
18 , HOLD_ENTITY_ID OE_Hold_Sources_ALL.HOLD_ENTITY_ID%TYPE
19 , HOLD_UNTIL_DATE OE_Hold_Sources_ALL.HOLD_UNTIL_DATE%TYPE
20 , RELEASED_FLAG OE_Hold_Sources_ALL.RELEASED_FLAG%TYPE
21 , HOLD_COMMENT OE_Hold_Sources_ALL.HOLD_COMMENT%TYPE
22 , CONTEXT OE_Hold_Sources_ALL.CONTEXT%TYPE
23 , ATTRIBUTE1 OE_Hold_Sources_ALL.ATTRIBUTE1%TYPE
24 , ATTRIBUTE2 OE_Hold_Sources_ALL.ATTRIBUTE2%TYPE
25 , ATTRIBUTE3 OE_Hold_Sources_ALL.ATTRIBUTE3%TYPE
26 , ATTRIBUTE4 OE_Hold_Sources_ALL.ATTRIBUTE4%TYPE
27 , ATTRIBUTE5 OE_Hold_Sources_ALL.ATTRIBUTE5%TYPE
28 , ATTRIBUTE6 OE_Hold_Sources_ALL.ATTRIBUTE6%TYPE
29 , ATTRIBUTE7 OE_Hold_Sources_ALL.ATTRIBUTE7%TYPE
30 , ATTRIBUTE8 OE_Hold_Sources_ALL.ATTRIBUTE8%TYPE
31 , ATTRIBUTE9 OE_Hold_Sources_ALL.ATTRIBUTE9%TYPE
32 , ATTRIBUTE10 OE_Hold_Sources_ALL.ATTRIBUTE10%TYPE
33 , ATTRIBUTE11 OE_Hold_Sources_ALL.ATTRIBUTE11%TYPE
34 , ATTRIBUTE12 OE_Hold_Sources_ALL.ATTRIBUTE12%TYPE
35 , ATTRIBUTE13 OE_Hold_Sources_ALL.ATTRIBUTE13%TYPE
36 , ATTRIBUTE14 OE_Hold_Sources_ALL.ATTRIBUTE14%TYPE
37 , ATTRIBUTE15 OE_Hold_Sources_ALL.ATTRIBUTE15%TYPE
38 , ORG_ID OE_Hold_Sources_ALL.ORG_ID%TYPE
39 , HOLD_RELEASE_ID OE_Hold_Sources_ALL.HOLD_RELEASE_ID%TYPE
40 , HOLD_ENTITY_CODE2 OE_Hold_Sources_ALL.HOLD_ENTITY_CODE2%TYPE
41 , HOLD_ENTITY_ID2 OE_Hold_Sources_ALL.HOLD_ENTITY_ID2%TYPE
42 -- Header and line id, in case only put this order or line on hold for
43 -- a specific header or line. Also for line level hold we need to pass
44 -- line id.
45 , HEADER_ID OE_ORDER_HEADERS.HEADER_ID%TYPE
46 , LINE_ID OE_ORDER_LINES.LINE_ID%TYPE
47 );
48
49 -- Hold Release
50 TYPE Hold_Release_Rec_Type IS RECORD
51 ( HOLD_RELEASE_ID OE_Hold_Releases.HOLD_RELEASE_ID%TYPE
52 , CREATION_DATE OE_Hold_Releases.CREATION_DATE%TYPE
53 , CREATED_BY OE_Hold_Releases.CREATED_BY%TYPE
54 , LAST_UPDATE_DATE OE_Hold_Releases.LAST_UPDATE_DATE%TYPE
55 , LAST_UPDATED_BY OE_Hold_Releases.LAST_UPDATED_BY%TYPE
56 , LAST_UPDATE_LOGIN OE_Hold_Releases.LAST_UPDATE_LOGIN%TYPE
57 , PROGRAM_APPLICATION_ID OE_Hold_Releases.PROGRAM_APPLICATION_ID%TYPE
58 , PROGRAM_ID OE_Hold_Releases.PROGRAM_ID%TYPE
59 , PROGRAM_UPDATE_DATE OE_Hold_Releases.PROGRAM_UPDATE_DATE%TYPE
60 , REQUEST_ID OE_Hold_Releases.REQUEST_ID%TYPE
61 , HOLD_SOURCE_ID OE_Hold_Releases.HOLD_SOURCE_ID%TYPE
62 , RELEASE_REASON_CODE OE_Hold_Releases.RELEASE_REASON_CODE%TYPE
63 , RELEASE_COMMENT OE_Hold_Releases.RELEASE_COMMENT%TYPE
64 , CONTEXT OE_Hold_Releases.CONTEXT%TYPE
65 , ORDER_HOLD_ID OE_Hold_Releases.ORDER_HOLD_ID%TYPE
66 , ATTRIBUTE1 OE_Hold_Releases.ATTRIBUTE1%TYPE
67 , ATTRIBUTE2 OE_Hold_Releases.ATTRIBUTE2%TYPE
68 , ATTRIBUTE3 OE_Hold_Releases.ATTRIBUTE3%TYPE
69 , ATTRIBUTE4 OE_Hold_Releases.ATTRIBUTE4%TYPE
70 , ATTRIBUTE5 OE_Hold_Releases.ATTRIBUTE5%TYPE
71 , ATTRIBUTE6 OE_Hold_Releases.ATTRIBUTE6%TYPE
72 , ATTRIBUTE7 OE_Hold_Releases.ATTRIBUTE7%TYPE
73 , ATTRIBUTE8 OE_Hold_Releases.ATTRIBUTE8%TYPE
74 , ATTRIBUTE9 OE_Hold_Releases.ATTRIBUTE9%TYPE
75 , ATTRIBUTE10 OE_Hold_Releases.ATTRIBUTE10%TYPE
76 , ATTRIBUTE11 OE_Hold_Releases.ATTRIBUTE11%TYPE
77 , ATTRIBUTE12 OE_Hold_Releases.ATTRIBUTE12%TYPE
78 , ATTRIBUTE13 OE_Hold_Releases.ATTRIBUTE13%TYPE
79 , ATTRIBUTE14 OE_Hold_Releases.ATTRIBUTE14%TYPE
80 , ATTRIBUTE15 OE_Hold_Releases.ATTRIBUTE15%TYPE
81 );
82
83 G_MISS_HOLD_SOURCE_REC Hold_Source_REC_type;
84
85 G_MISS_HOLD_RELEASE_REC Hold_Release_REC_type;
86
87
88 TYPE Hold_Source_Tbl_Type IS TABLE OF Hold_Source_Rec_Type
89 INDEX BY BINARY_INTEGER;
90
91 -- Header Rec
92 TYPE order_rec_type IS RECORD (
93 Header_Id OE_ORDER_HEADERS.HEADER_ID%TYPE,
94 Line_Id OE_ORDER_LINES.LINE_ID%TYPE
95 );
96
97 TYPE order_tbl_type IS TABLE OF order_rec_type
98 INDEX BY BINARY_INTEGER;
99
100 TYPE operating_units_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --ER#7479609
101
102
103 Procedure Apply_Holds(
104 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
105 DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
106 p_hold_existing_flg IN VARCHAR2 DEFAULT 'Y',
107 p_hold_future_flg IN VARCHAR2 DEFAULT 'Y',
108 x_return_status OUT NOCOPY VARCHAR2,
109
110 x_msg_count OUT NOCOPY NUMBER,
111
112 x_msg_data OUT NOCOPY VARCHAR2 );
113
114
115 Procedure Apply_Holds (
116 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
117 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
118 p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
119 p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE DEFAULT NULL,
120 x_return_status OUT NOCOPY VARCHAR2,
121
122 x_msg_count OUT NOCOPY NUMBER,
123
124 x_msg_data OUT NOCOPY VARCHAR2 );
125
126
127 Procedure Release_Holds (
128 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
129 p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
130 x_return_status OUT NOCOPY VARCHAR2,
131
132 x_msg_count OUT NOCOPY NUMBER,
133
134 x_msg_data OUT NOCOPY VARCHAR2);
135
136
137 Procedure Release_Holds (
138 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
139 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
140 DEFAULT NULL,
141 p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
142 p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE DEFAULT NULL,
143 x_return_status OUT NOCOPY VARCHAR2,
144
145 x_msg_count OUT NOCOPY NUMBER,
146
147 x_msg_data OUT NOCOPY VARCHAR2);
148
149
150
151 procedure delete_holds (
152 p_order_rec IN OE_HOLDS_PVT.order_rec_type,
153 x_return_status OUT NOCOPY VARCHAR2,
154
155 x_msg_count OUT NOCOPY NUMBER,
156
157 x_msg_data OUT NOCOPY VARCHAR2 );
158
159
160 procedure validate (
161 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
162 x_return_status OUT NOCOPY VARCHAR2,
163
164 x_msg_count OUT NOCOPY NUMBER,
165
166 x_msg_data OUT NOCOPY VARCHAR2 );
167
168
169 Procedure Create_Order_Holds(
170 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
171 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
172 x_return_status OUT NOCOPY VARCHAR2,
173
174 x_msg_count OUT NOCOPY NUMBER,
175
176 x_msg_data OUT NOCOPY VARCHAR2);
177
178 function entity_code_value (
179 p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE
180 )
181 return VARCHAR2;
182
183 function entity_id_value (
184 p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE,
185 p_hold_entity_id IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE )
186 return VARCHAR2;
187
188 function user_name (
189 p_user_id IN FND_USER.USER_ID%TYPE )
190 return VARCHAR2;
191
192 function hold_name(
193 p_hold_source_id IN OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE)
194 return varchar2;
195
196 procedure process_apply_holds_lines (
197 p_num_of_records IN NUMBER
198 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
199 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
200 ,p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
201 ,p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
202 ,x_return_status OUT NOCOPY VARCHAR2
203
204 ,x_msg_count OUT NOCOPY NUMBER
205
206 ,x_msg_data OUT NOCOPY VARCHAR2
207
208 );
209
210 procedure process_apply_holds_orders (
211 p_num_of_records IN NUMBER
212 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
213 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
214 ,p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
215 ,p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
216 ,x_return_status OUT NOCOPY VARCHAR2
217
218 ,x_msg_count OUT NOCOPY NUMBER
219
220 ,x_msg_data OUT NOCOPY VARCHAR2
221
222 );
223
224 procedure process_release_holds_lines (
225 p_num_of_records IN NUMBER
226 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
227 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
228 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
229 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
230 ,x_return_status OUT NOCOPY VARCHAR2
231
232 ,x_msg_count OUT NOCOPY NUMBER
233
234 ,x_msg_data OUT NOCOPY VARCHAR2
235
236 );
237
238 procedure process_release_holds_orders (
239 p_num_of_records IN NUMBER
240 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
241 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
242 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
243 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
244 ,x_return_status OUT NOCOPY VARCHAR2
245
246 ,x_msg_count OUT NOCOPY NUMBER
247
248 ,x_msg_data OUT NOCOPY VARCHAR2
249
250 );
251
252 procedure process_create_source(
253 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
254 ,p_hold_existing_flg IN varchar2
255 ,p_hold_future_flg IN varchar2
256 ,p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id --ER#7479609
257 ,x_return_status OUT NOCOPY VARCHAR2
258
259 ,x_msg_count OUT NOCOPY NUMBER
260
261 ,x_msg_data OUT NOCOPY VARCHAR2
262
263 );
264 procedure process_release_source(
265 p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
266 ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
267 ,x_return_status OUT NOCOPY VARCHAR2
268
269 ,x_msg_count OUT NOCOPY NUMBER
270
271 ,x_msg_data OUT NOCOPY VARCHAR2
272
273 );
274 function check_authorization (
275 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
276 p_authorized_action_code IN OE_HOLD_AUTHORIZATIONS.AUTHORIZED_ACTION_CODE%TYPE,
277 p_responsibility_id IN OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE,
278 p_application_id IN OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE,
279 x_return_status OUT NOCOPY VARCHAR2,
280
281 x_msg_count OUT NOCOPY NUMBER,
282
283 x_msg_data OUT NOCOPY VARCHAR2
284
285 )
286 RETURN varchar2;
287 procedure split_hold (
288 p_line_id IN NUMBER,
289 p_split_from_line_id IN NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2,
291
292 x_msg_count OUT NOCOPY NUMBER,
293
294 x_msg_data OUT NOCOPY VARCHAR2
295
296 );
297
298 Procedure release_orders (
299 p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type
300 DEFAULT G_MISS_HOLD_RELEASE_REC,
301 p_order_rec IN OE_HOLDS_PVT.order_rec_type,
302 p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type
303 DEFAULT G_MISS_HOLD_SOURCE_REC,
304 x_return_status OUT NOCOPY VARCHAR2,
305
306 x_msg_count OUT NOCOPY NUMBER,
307
308 x_msg_data OUT NOCOPY VARCHAR2
309
310 );
311 /*Added New Overloaded procedure Apply_holds for WF_HOLDS ER (bug 6449458)*/
312 Procedure Apply_Holds(
313 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
314 p_hold_existing_flg IN VARCHAR2,
315 p_hold_future_flg IN VARCHAR2,
316 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
317 p_wf_item_type IN VARCHAR2 DEFAULT NULL,
318 p_wf_activity_name IN VARCHAR2 DEFAULT NULL,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 x_is_hold_applied OUT NOCOPY BOOLEAN
323 );
324
325 /*Added New overloaded procedure create_order_holds for WF_HOLDS ER (bug 6449458)*/
326 Procedure Create_Order_Holds(
327 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
328 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
329 p_item_type IN VARCHAR2,
330 p_activity_name IN VARCHAR2,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2,
334 x_is_hold_applied OUT NOCOPY BOOLEAN
335 );
336
337 /*Added new overloaded procedure Process_release_holds_lines for ER 1373910 - Progress WF on hold release */
338 procedure process_release_holds_lines (
339 p_num_of_records IN NUMBER
340 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
341 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
342 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
343 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
344 ,p_wf_release_action IN VARCHAR2
345 ,x_return_status OUT NOCOPY VARCHAR2
346 ,x_msg_count OUT NOCOPY NUMBER
347 ,x_msg_data OUT NOCOPY VARCHAR2
348 );
349 /*Added new overloaded procedure Process_release_holds_orders for ER 1373910 - Progress WF on hold release */
350 procedure process_release_holds_orders (
351 p_num_of_records IN NUMBER
352 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
353 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
354 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
355 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
356 ,p_wf_release_action IN VARCHAR2
357 ,x_return_status OUT NOCOPY VARCHAR2
358 ,x_msg_count OUT NOCOPY NUMBER
359 ,x_msg_data OUT NOCOPY VARCHAR2
360 );
361 /*Added new overloaded procedure Process_release_source for ER 1373910 - Progress WF on hold release */
362 procedure process_release_source (
363 p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
364 ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
365 ,p_wf_release_action IN VARCHAR2
366 ,x_return_status OUT NOCOPY VARCHAR2
367 ,x_msg_count OUT NOCOPY NUMBER
368 ,x_msg_data OUT NOCOPY VARCHAR2
369 );
370 END OE_Holds_Pvt;