[Home] [Help]
PACKAGE: APPS.OE_HOLDS_PVT
Source
1 PACKAGE OE_Holds_Pvt AUTHID CURRENT_USER AS
2 /* $Header: OEXVHLDS.pls 120.6.12020000.1 2012/06/26 10:23:00 appldev 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 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
109 x_return_status OUT NOCOPY VARCHAR2,
110
111 x_msg_count OUT NOCOPY NUMBER,
112
113 x_msg_data OUT NOCOPY VARCHAR2 );
114
115
116 Procedure Apply_Holds (
117 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
118 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
119 p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
120 p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE DEFAULT NULL,
121 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
122 x_return_status OUT NOCOPY VARCHAR2,
123
124 x_msg_count OUT NOCOPY NUMBER,
125
126 x_msg_data OUT NOCOPY VARCHAR2 );
127
128
129 Procedure Release_Holds (
130 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
131 p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
132 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
133 x_return_status OUT NOCOPY VARCHAR2,
134
135 x_msg_count OUT NOCOPY NUMBER,
136
137 x_msg_data OUT NOCOPY VARCHAR2);
138
139
140 Procedure Release_Holds (
141 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
142 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
143 DEFAULT NULL,
144 p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
145 p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE DEFAULT NULL,
146 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
147 x_return_status OUT NOCOPY VARCHAR2,
148
149 x_msg_count OUT NOCOPY NUMBER,
150
151 x_msg_data OUT NOCOPY VARCHAR2);
152
153
154
155 procedure delete_holds (
156 p_order_rec IN OE_HOLDS_PVT.order_rec_type,
157 x_return_status OUT NOCOPY VARCHAR2,
158
159 x_msg_count OUT NOCOPY NUMBER,
160
161 x_msg_data OUT NOCOPY VARCHAR2 );
162
163
164 procedure validate (
165 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
166 x_return_status OUT NOCOPY VARCHAR2,
167
168 x_msg_count OUT NOCOPY NUMBER,
169
170 x_msg_data OUT NOCOPY VARCHAR2 );
171
172
173 Procedure Create_Order_Holds(
174 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
175 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
176 x_return_status OUT NOCOPY VARCHAR2,
177
178 x_msg_count OUT NOCOPY NUMBER,
179
180 x_msg_data OUT NOCOPY VARCHAR2);
181
182 function entity_code_value (
183 p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE
184 )
185 return VARCHAR2;
186
187 function entity_id_value (
188 p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE,
189 p_hold_entity_id IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE )
190 return VARCHAR2;
191
192 function user_name (
193 p_user_id IN FND_USER.USER_ID%TYPE )
194 return VARCHAR2;
195
196 function hold_name(
197 p_hold_source_id IN OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE)
198 return varchar2;
199
200 procedure process_apply_holds_lines (
201 p_num_of_records IN NUMBER
202 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
203 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
204 ,p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
205 ,p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
206 ,x_return_status OUT NOCOPY VARCHAR2
207
208 ,x_msg_count OUT NOCOPY NUMBER
209
210 ,x_msg_data OUT NOCOPY VARCHAR2
211
212 );
213
214 procedure process_apply_holds_orders (
215 p_num_of_records IN NUMBER
216 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
217 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
218 ,p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
219 ,p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
220 ,x_return_status OUT NOCOPY VARCHAR2
221
222 ,x_msg_count OUT NOCOPY NUMBER
223
224 ,x_msg_data OUT NOCOPY VARCHAR2
225
226 );
227
228 procedure process_release_holds_lines (
229 p_num_of_records IN NUMBER
230 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
231 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
232 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
233 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
234 ,x_return_status OUT NOCOPY VARCHAR2
235
236 ,x_msg_count OUT NOCOPY NUMBER
237
238 ,x_msg_data OUT NOCOPY VARCHAR2
239
240 );
241
242 procedure process_release_holds_orders (
243 p_num_of_records IN NUMBER
244 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
245 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
246 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
247 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
248 ,x_return_status OUT NOCOPY VARCHAR2
249
250 ,x_msg_count OUT NOCOPY NUMBER
251
252 ,x_msg_data OUT NOCOPY VARCHAR2
253
254 );
255
256 procedure process_create_source(
257 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
258 ,p_hold_existing_flg IN varchar2
259 ,p_hold_future_flg IN varchar2
260 ,p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id --ER#7479609
261 ,x_return_status OUT NOCOPY VARCHAR2
262
263 ,x_msg_count OUT NOCOPY NUMBER
264
265 ,x_msg_data OUT NOCOPY VARCHAR2
266
267 );
268 procedure process_release_source(
269 p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
270 ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
271 ,x_return_status OUT NOCOPY VARCHAR2
272
273 ,x_msg_count OUT NOCOPY NUMBER
274
275 ,x_msg_data OUT NOCOPY VARCHAR2
276
277 );
278 function check_authorization (
279 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
280 p_authorized_action_code IN OE_HOLD_AUTHORIZATIONS.AUTHORIZED_ACTION_CODE%TYPE,
281 p_responsibility_id IN OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE,
282 p_application_id IN OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE,
283 x_return_status OUT NOCOPY VARCHAR2,
284
285 x_msg_count OUT NOCOPY NUMBER,
286
287 x_msg_data OUT NOCOPY VARCHAR2
288
289 )
290 RETURN varchar2;
291 procedure split_hold (
292 p_line_id IN NUMBER,
293 p_split_from_line_id IN NUMBER,
294 x_return_status OUT NOCOPY VARCHAR2,
295
296 x_msg_count OUT NOCOPY NUMBER,
297
298 x_msg_data OUT NOCOPY VARCHAR2
299
300 );
301
302 Procedure release_orders (
303 p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type
304 DEFAULT G_MISS_HOLD_RELEASE_REC,
305 p_order_rec IN OE_HOLDS_PVT.order_rec_type,
306 p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type
307 DEFAULT G_MISS_HOLD_SOURCE_REC,
308 x_return_status OUT NOCOPY VARCHAR2,
309
310 x_msg_count OUT NOCOPY NUMBER,
311
312 x_msg_data OUT NOCOPY VARCHAR2
313
314 );
315 /*Added New Overloaded procedure Apply_holds for WF_HOLDS ER (bug 6449458)*/
316 Procedure Apply_Holds(
317 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
318 p_hold_existing_flg IN VARCHAR2,
319 p_hold_future_flg IN VARCHAR2,
320 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
321 p_wf_item_type IN VARCHAR2 DEFAULT NULL,
322 p_wf_activity_name IN VARCHAR2 DEFAULT NULL,
323 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 x_is_hold_applied OUT NOCOPY BOOLEAN
328 );
329
330 /*Added New overloaded procedure create_order_holds for WF_HOLDS ER (bug 6449458)*/
331 Procedure Create_Order_Holds(
332 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
333 p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
334 p_item_type IN VARCHAR2,
335 p_activity_name IN VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_count OUT NOCOPY NUMBER,
338 x_msg_data OUT NOCOPY VARCHAR2,
339 x_is_hold_applied OUT NOCOPY BOOLEAN
340 );
341
342 /*Added new overloaded procedure Process_release_holds_lines for ER 1373910 - Progress WF on hold release */
343 procedure process_release_holds_lines (
344 p_num_of_records IN NUMBER
345 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
346 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
347 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
348 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
349 ,p_wf_release_action IN VARCHAR2
350 ,x_return_status OUT NOCOPY VARCHAR2
351 ,x_msg_count OUT NOCOPY NUMBER
352 ,x_msg_data OUT NOCOPY VARCHAR2
353 );
354 /*Added new overloaded procedure Process_release_holds_orders for ER 1373910 - Progress WF on hold release */
355 procedure process_release_holds_orders (
356 p_num_of_records IN NUMBER
357 ,p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
358 ,p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
359 ,p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE
360 ,p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
361 ,p_wf_release_action IN VARCHAR2
362 ,x_return_status OUT NOCOPY VARCHAR2
363 ,x_msg_count OUT NOCOPY NUMBER
364 ,x_msg_data OUT NOCOPY VARCHAR2
365 );
366 /*Added new overloaded procedure Process_release_source for ER 1373910 - Progress WF on hold release */
367 procedure process_release_source (
368 p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
369 ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
370 ,p_wf_release_action IN VARCHAR2
371 ,x_return_status OUT NOCOPY VARCHAR2
372 ,x_msg_count OUT NOCOPY NUMBER
373 ,x_msg_data OUT NOCOPY VARCHAR2
374 );
375 END OE_Holds_Pvt;