1 PACKAGE XDP_OA_UTIL AUTHID CURRENT_USER AS
2 /* $Header: XDPOAUTS.pls 120.1 2005/06/22 06:41:49 appldev ship $ */
3
4 -- PL/SQL Specification
5 -- Datastructure Definitions
6
7
8 -- API specifications
9
10 -- Start of comments
11 -- API name : Add_WI_toLine
12 -- Type : Public
13 -- Function : Add a workitem to an order line.
14 -- Pre-reqs : None.
15 -- @return The runtime ID of the work item instance
16 --
17 -- Version : Current version 11.5
18 -- Notes :
19 -- This function will add a workitem to
20 -- an order line. It will also create the
21 -- workitem parameter list in the database
22 -- base on the order line information.
23 -- The workitem_instance_id will return at
24 -- the end of the function call.
25 -- End of comments
26 Function Add_WI_toLine(
27 p_line_item_id IN NUMBER,
28 p_workitem_name IN VARCHAR2,
29 p_workitem_version IN VARCHAR2 DEFAULT NULL,
30 p_provisioning_date IN Date default NULL,
31 p_priority IN number Default 100,
32 p_provisioning_seq IN Number Default 0,
33 p_due_date IN Date Default NULL,
34 p_customer_required_date IN DATE Default NULL,
35 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y')
36 RETURN NUMBER;
37
38 -- Start of comments
39 -- API name : Add_WI_toLine
40 -- Type : Public
41 -- Function : Overload function. Add a workitem to an order line.
42 -- Pre-reqs : None.
43 -- @return The runtime ID of the work item instance
44 --
45 -- Version : Current version 11.5
46 -- Notes :
47 -- Overload function.
48 -- This function will add a workitem to
49 -- an order line. It will also create the
50 -- workitem parameter list in the database
51 -- base on the order line information.
52 -- The workitem_instance_id will return at
53 -- the end of the function call.
54 -- End of comments
55 Function Add_WI_toLine(
56 p_line_item_id IN NUMBER,
57 p_workitem_id IN Number,
58 p_provisioning_date IN Date Default NULL,
59 p_priority IN number Default 100,
60 p_provisioning_seq IN Number Default 0,
61 p_due_date IN Date Default NULL,
62 p_customer_required_date IN DATE Default NULL,
63 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y')
64 RETURN NUMBER;
65
66 -- Start of comments
67 -- API name : Add_WI_toLine
68 -- Type : Public
69 -- Function : Overload function. Add a workitem to an order line.
70 -- Pre-reqs : None.
71 -- @return The runtime ID of the work item instance
72 --
73 -- Version : Current version 11.5
74 -- Notes :
75 -- Overload function.
76 -- This function will add a workitem to
77 -- an order line. It will also create the
78 -- workitem parameter list in the database
79 -- base on the order line information.
80 -- The workitem_instance_id will return at
81 -- the end of the function call.It will also call user
82 -- defined validation procedure dynamically.
83 -- End of comments
84 --
85 Function Add_WI_toLine(
86 p_line_item_id IN NUMBER,
87 p_workitem_id IN Number,
88 p_provisioning_date IN Date Default NULL,
89 p_priority IN number Default 100,
90 p_provisioning_seq IN Number Default 0,
91 p_due_date IN Date Default NULL,
92 p_customer_required_date IN DATE Default NULL,
93 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y',
94 x_error_code OUT NOCOPY NUMBER,
95 x_error_message OUT NOCOPY VARCHAR2)
96 RETURN NUMBER;
97
98 -- Start of comments
99 -- API name : Add_WI_toLine
100 -- Type : Public
101 -- Function : Overload function. Add a workitem to an order line.
102 -- Pre-reqs : None.
103 -- @return The runtime ID of the work item instance
104 --
105 -- Version : Current version 11.5
106 -- Notes :
107 -- Overload function.
108 -- This function will add a workitem to
109 -- an order line. It will also create the
110 -- workitem parameter list in the database
111 -- base on the order line information.
112 -- The workitem_instance_id will return at
113 -- the end of the function call.It will also call user
114 -- defined validation procedure dynamically.
115 -- End of comments
116 --
117 Function Add_WI_toLine(
118 p_line_item_id IN NUMBER,
119 p_workitem_name IN VARCHAR2,
120 p_workitem_version IN VARCHAR2 DEFAULT NULL,
121 p_provisioning_date IN Date default NULL,
122 p_priority IN number Default 100,
123 p_provisioning_seq IN Number Default 0,
124 p_due_date IN Date Default NULL,
125 p_customer_required_date IN DATE Default NULL,
126 p_oa_added_flag IN VARCHAR2 DEFAULT 'Y',
127 x_error_code OUT NOCOPY NUMBER,
128 x_error_message OUT NOCOPY VARCHAR2)
129 RETURN NUMBER;
130
131 -- Procedure to dynamically execute Validation procedure
132 --
133 PROCEDURE Validate_Workitem(
134 p_order_id IN NUMBER
135 ,p_line_item_id IN NUMBER
136 ,p_wi_instance_id IN NUMBER
137 ,p_procedure_name IN VARCHAR2
138 ,x_error_code OUT NOCOPY NUMBER
139 ,x_error_message OUT NOCOPY VARCHAR2);
140
141
142
143 -- Start of comments
144 -- API name : Set_Order_Relationships
145 -- Type : Public
146 -- Function : Set Order Realtionships
147 -- Pre-reqs : None.
148 --
149 -- Version : Current version 11.5
150 -- Notes :
151 -- The p_order_relationship argument take the
152 -- following enumerated Constant value:
153 -- XDP_TYPES.IS_PREREQUISITE_OF
154 -- Related order will not get executed
155 -- until the current order is completed.
156 -- If the current order is canceled, the
157 -- related order will also be canceled.
158 -- XDP_TYPES.COMES_BEFORE
159 -- Related order will not get executed
160 -- until the current order is completed
161 -- or the current order is canceled.
162 -- XDP_TYPES.COMES_AFTER
163 -- Current order will not get executed
164 -- until the related order is
165 -- completed or the related order is
166 -- canceled.
167 -- XDP_TYPES.IS_CHILD_OF
168 -- Current order is the child order of
169 -- the related order.
170 --
171 -- End of comments
172 Procedure Set_Order_Relationships(
173 p_curr_sdp_order_id in NUMBER,
174 p_related_sdp_order_id IN NUMBER,
175 p_order_relationship IN BINARY_INTEGER,
176 return_code OUT NOCOPY number,
177 error_description OUT NOCOPY varchar2);
178
179 -- Start of comments
180 -- API name : Set_Workitem_Relationships
181 -- Type : Public
182 -- Function : Set Workitem Realtionships.
183 -- Pre-reqs : None.
184 --
185 -- Version : Current version 11.5
186 -- Notes :
187 -- The p_wi_relationship argument take the
188 -- following enumerated Constant value:
189 -- XDP_TYPES.MERGED_INTO
190 -- Related workitem is merged into the
191 -- current workitem. If the current
192 -- workitem is completed, then the
193 -- related workitem is completed.
194 -- End of comments
195 Procedure Set_Workitem_Relationships(
196 p_curr_wi_instance_id in NUMBER,
197 p_related_wi_instance_id IN NUMBER,
198 p_wi_relationship IN BINARY_INTEGER,
199 return_code OUT NOCOPY number,
200 error_description OUT NOCOPY varchar2);
201
202
203 -- Start of comments
204 -- API name : Get_Order_Header
205 -- Type : Public
206 -- Function : Get the order header information for a giving order
207 -- Pre-reqs : None.
208 --
209 -- Version : Current version 11.5
210 -- Notes :
211 -- End of comments
212 FUNCTION Get_Order_Header(p_sdp_order_id IN NUMBER)
213 return XDP_TYPES.ORDER_HEADER;
214
215
216 -- Start of comments
217 -- API name : Get_Order_Lines
218 -- Type : Public
219 -- Function : Get all the line items for a given order.
220 -- Pre-reqs : None.
221 --
222 -- Version : Current version 11.5
223 -- Notes :
224 -- End of comments
225 FUNCTION Get_Order_Lines( p_sdp_order_id IN NUMBER)
226 return XDP_TYPES.ORDER_LINE_LIST;
227
228 -- Start of comments
229 -- API name : Get_LineRec
230 -- Type : Public
231 -- Function : Get the line item record
232 -- Pre-reqs : None.
233 --
234 -- Version : Current version 11.5
235 -- Notes :
236 -- End of comments
237 FUNCTION Get_LineRec( p_line_item_id IN NUMBER)
238 return XDP_TYPES.LINE_ITEM;
239
240 -- Start of comments
241 -- API name : Get_WorkitemRec
242 -- Type : Public
243 -- Function : Get the work item record
244 -- Pre-reqs : None.
245 --
246 -- Version : Current version 11.5
247 -- Notes :
248 -- End of comments
249 FUNCTION Get_WorkitemRec( p_wi_instance_id IN NUMBER)
250 return XDP_TYPES.workitem_rec ;
251
252 -- Start of comments
253 -- API name : Get_Order_Workitems
254 -- Type : Public
255 -- Function : Get all the work items for a given order.
256 -- Pre-reqs : None.
257 --
258 -- Version : Current version 11.5
259 -- Notes :
260 -- End of comments
261 FUNCTION Get_Order_Workitems( p_sdp_order_id IN NUMBER)
262 return XDP_TYPES.Workitem_List;
263
264 -- Start of comments
265 -- API name : Find_Orders
266 -- Type : Public
267 -- Function : Find orders which meets the user defined searching criteria.
268 -- Pre-reqs : None.
269 --
270 -- Version : Current version 11.5
271 -- Notes :
272 -- Find orders which meets the user defined searching
273 -- criteria. The user can use Most of the commands which are allowed
274 -- in the SQL where clause such as Like, = , substr,etc.., in their
275 -- searching criteria. The User should use the following Macros to
276 -- refer to the order information:
277 -- $ORDER.<Order Header Record Attribute Name>$
278 -- $LINE.<Line item record attribute>$
279 --
280 -- For example, if the user wants to find the orders which have
281 -- status of 'IN PROGRESS' and contains a line item of
282 -- 'JD_CALLERID', he/she can specify the search criteria as:
283 --
284 -- declare
285 -- lv_where varchar2(8000);
286 -- lv_list XDP_TYPES.ORDER_HEADER_LIST;
287 -- lv_ret number;
288 -- lv_str varchar2(800);
289 -- begin
290 --
291 -- lv_where := 'UPPER($ORDER.ORDER_STATUS$) = ''IN PROGRESS'' and '||
292 -- '$LINE.LINE_ITEM_NAME$ = ''JD_CALLERID''';
293 --
294 -- XDP_OA_UTIL.Find_Orders(
295 -- p_where => lv_where,
296 -- p_order_list => lv_list,
297 -- return_code => lv_ret,
298 -- error_description => lv_str);
299 -- END;
300 --
301 -- The user must omit the key word WHERE in the argument p_where.
302 -- In additon, the user should examine the count of out variable
303 -- p_order_list to see if there is any match found.
304 -- End of comments
305 PROCEDURE Find_Orders(
306 p_where IN OUT NOCOPY Varchar2,
307 p_order_list OUT NOCOPY XDP_TYPES.ORDER_HEADER_LIST,
308 return_code OUT NOCOPY number,
309 error_description OUT NOCOPY varchar2);
310
311 -- Start of comments
312 -- API name : FIND_LINES
313 -- Type : Public
314 -- Function : Find line items in an order which meets the searching criteria.
315 -- Pre-reqs : None.
316 --
317 -- Version : Current version 11.5
318 -- Notes :
319 -- Find line item in a given order which meets the user defined searching
320 -- criteria. The user can use most of the commands which are allowed
321 -- in the SQL where clause such as Like, = , substr,etc.., in their
322 -- searching criteria. The User should use the following Macros to
323 -- refer to the order information:
324 -- $LINE.<Line item record attribute>$
325 -- $LINE_PARAM.<Line parameter name>$
326 --
327 -- For example, if the user wants to find out in order 720 if there
328 -- is any line item which has parameter ACTIE_FLAG of value like
329 -- 'TES%' and parameter ENDING_NUMBER of value like 'TES%', he/she
330 -- can specify the search criteria as:
331 --
332 -- declare
333 -- lv_where varchar2(8000);
334 -- lv_list XDP_TYPES.ORDER_LINE_LIST;
335 -- lv_ret number;
336 -- lv_str varchar2(800);
337 -- lv_index number;
338 -- begin
339 --
340 -- lv_where := 'UPPER($LINE_PARAM.ACTIVE_FLAG$) like ''TES%'' and '||
341 -- 'UPPER($LINE_PARAM.ENDING_NUMBER$) like ''TES%''';
342 --
343 -- XDP_OA_UTIL.Find_Lines(
344 -- p_sdp_order_id => 720,
345 -- p_where => lv_where,
346 -- p_order_line_list => lv_list,
347 -- return_code => lv_ret,
348 -- error_description => lv_str);
349 -- END;
350 --
351 -- The user must omit the key word WHERE in the argument p_where.
352 -- In additon, the user should examine the count of out variable
353 -- p_order_line_list to see if there is any match found.
354 --
355 -- End of comments
356 PROCEDURE Find_Lines(
357 p_sdp_order_id IN NUMBER,
358 p_where IN OUT NOCOPY Varchar2,
359 p_order_line_list OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST,
360 return_code OUT NOCOPY number,
361 error_description OUT NOCOPY varchar2);
362
363 -- Start of comments
364 -- API name : Find_Workitems
365 -- Type : Public
366 -- Function : Find Work items in an order which meets the searching criteria.
367 -- Pre-reqs : None.
368 --
369 -- Version : Current version 11.5
370 -- Notes :
371 -- Find Work item in a given order which meets the user defined searching
372 -- criteria. The user can use most of the commands which are allowed
373 -- in the SQL where clause such as Like, = , substr,etc.., in their
374 -- searching criteria. The User should use the following Macros to
375 -- refer to the order information:
376 -- $WORKITEM.<Workitem record attribute>$
377 -- $WI_PARAM.<Workitem parameter name>$
378 --
379 -- For example if the user wants to find out if there is any workitem
380 -- in order 299 with workitem name of 'JD_WORKITEM', and workitem
381 -- parameter ENDING_NUMBER of value '5', and workitem parameter
385 -- declare
382 -- MESSAGE_VERSION of value '1.0', he/she can specify the search
383 -- criteria as:
384 --
386 -- lv_where varchar2(8000);
387 -- lv_list XDP_TYPES.WORKITEM_LIST;
388 -- lv_ret number;
389 -- lv_str varchar2(800);
390 -- lv_index number;
391 -- begin
392 --
393 -- lv_where := 'UPPER($WI_PARAM.ENDING_NUMBER$) = ''5'' and '||
394 -- 'UPPER($WI_PARAM.MESSAGE_VERSION$) = ''1.0''' ||
395 -- ' and $WORKITEM.WORKITEM_NAME$ = ''JD_WORKITEM''';
396 --
397 -- XDP_OA_UTIL.Find_Workitems(
398 -- p_sdp_order_id => 299,
399 -- p_where => lv_where,
400 -- p_workitem_list => lv_list,
401 -- return_code => lv_ret,
402 -- error_description => lv_str);
403 --
404 -- end;
405 --
406 -- Note: The user must omit the key word WHERE in the argument p_where.
407 -- In additon, the user should examine the count of out variable
408 -- p_order_line_list to see if there is any match found.
409 --
410 -- End of comments
411 PROCEDURE Find_Workitems(
412 p_sdp_order_id IN NUMBER,
413 p_where IN OUT NOCOPY Varchar2,
414 p_workitem_list OUT NOCOPY XDP_TYPES.Workitem_LIST,
415 return_code OUT NOCOPY number,
416 error_description OUT NOCOPY varchar2);
417
418 -- Start of comments
419 -- API name : COPY_LINE
420 -- Type : Public
421 -- Function : Copy an existing line item to a order_line_list.
422 -- Pre-reqs : None.
423 --
424 -- Version : Current version 11.5
425 -- Notes :
426 -- This API allows user to copy an existing line item to a
427 -- order_line_list. The user may choose the copy method to be either
428 -- APPEND_TO OR OVERRIDE. The copy result will be returned to the user
429 -- in out argument p_order_line_list and p_line_parameter_list.
430 -- End of comments
431 PROCEDURE Copy_Line(
432 p_src_sdp_order_id IN NUMBER,
433 p_src_line_item_id IN NUMBER,
434 p_copy_mode IN BINARY_INTEGER default XDP_TYPES.APPEND_TO,
435 p_order_line_list IN OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST,
436 p_line_parameter_list IN OUT NOCOPY XDP_TYPES.LINE_PARAM_LIST,
437 return_code OUT NOCOPY NUMBER,
438 error_description OUT NOCOPY VARCHAR2);
439
440 --
441 -- Global variables declared to capture the value of Workitem Name.
442 --
443 g_Workitem_Name VARCHAR2(200):=NULL;
444
445 END XDP_OA_UTIL;