[Home] [Help]
PACKAGE: APPS.OE_QUERY
Source
1 PACKAGE OE_QUERY as
2 /* $Header: OEXQRYSS.pls 120.1 2006/11/19 18:00:32 ssurapan noship $ */
3
4 function LINE_TOTAL(
5 ORDER_ROWID IN VARCHAR2
6 , ORDER_LINE_ID IN NUMBER DEFAULT NULL
7 , LINE_TYPE_CODE IN VARCHAR2
8 , ITEM_TYPE_CODE IN VARCHAR2
9 , SERVICE_DURATION IN NUMBER
10 , SERVICEABLE_FLAG IN VARCHAR2
11 , ORDERED_QTY IN NUMBER
12 , CANCELLED_QTY IN NUMBER
13 , SELLING_PRICE IN NUMBER
14 )
15 return NUMBER;
16
17 function SERVICE_TOTAL(
18 P_ROWID IN VARCHAR2
19 , P_LINE_ID IN NUMBER
20 , P_LINES_LINE_ID IN NUMBER
21 , P_ITEM_TYPE_CODE IN VARCHAR2
22 , P_SERVICEABLE_FLAG IN VARCHAR2
23 )
24 return NUMBER;
25
26
27 function SCHEDULE_STATUS(
28 ORDER_LINE_ID IN NUMBER DEFAULT NULL
29 )
30 return VARCHAR2;
31
32 pragma restrict_references( SCHEDULE_STATUS, WNDS); -- ,WNPS);
33
34
35 function SCHEDULE_STATUS(
36 SCHEDULE_STATUS_CODE IN VARCHAR2
37 )
38 return VARCHAR2;
39
40 pragma restrict_references( SCHEDULE_STATUS, WNDS); -- ,WNPS);
41
42
43 function ATO_Indicator( P_Line_Id IN NUMBER,
44 P_Item_Type_Code IN VARCHAR2 )
45 return VARCHAR2;
46
47 function Get_ATO_Indicator( P_Line_Id IN NUMBER,
48 P_Item_Type_Code IN VARCHAR2 )
49 return VARCHAR2;
50
51
52 function RELEASED_QUANTITY(
53 P_LINE_ID IN NUMBER
54 )
55 return NUMBER;
56
57 pragma restrict_references (RELEASED_QUANTITY, WNDS, WNPS);
58
59 function p_line_released_quantity (
60 P_LINE_ID IN NUMBER
61 )
62 return NUMBER;
63
64 pragma restrict_references (p_line_released_quantity, WNDS, WNPS);
65
66
67 function II_RESERVED_QUANTITY(
68 P_LINE_ID IN NUMBER,
69 P_COMPONENT_CODE IN VARCHAR2
70 )
71 return NUMBER;
72
73
74 pragma restrict_references (II_RESERVED_QUANTITY, WNDS, WNPS);
75
76
77 function II_RELEASED_QUANTITY(
78 P_LINE_ID IN NUMBER,
79 P_COMPONENT_CODE IN VARCHAR2
80 )
81 return NUMBER;
82
83 pragma restrict_references (II_RELEASED_QUANTITY, WNDS, WNPS);
84
85 function II_SCHEDULE_STATUS_CODE(
86 P_LINE_ID IN NUMBER,
87 P_COMPONENT_CODE IN VARCHAR2
88 )
89 return VARCHAR2;
90
91 pragma restrict_references (II_SCHEDULE_STATUS_CODE, WNDS, WNPS);
92
93 function RESERVED_QUANTITY(
94 ORDER_LINE_ID IN NUMBER DEFAULT NULL
95 )
96 return NUMBER;
97
98
99 function HOLD(
100 ORDER_LINE_ID IN NUMBER DEFAULT NULL
101 , ORDER_HEADER_ID IN NUMBER DEFAULT NULL
102 )
103 return varchar2;
104
105 ----------------------------------------------------------------------
106 -- SHIPMENT_SCHEDULE_NUMBER is a better function to figure out
107 -- shipment_number because it takes service lines into consideration.
108 -- Should use SHIPMENT_SCHEDULE_NUMBER whenever possible.
109 ----------------------------------------------------------------------
110 function SHIPMENT_NUMBER(
111 ORDER_LINE_ID IN NUMBER DEFAULT NULL
112 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
113 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
114 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
115 )
116 return NUMBER;
117
118 ----------------------------------------------------------------------
119 -- BASE_LINE_NUMBER is a better function to figure out line_number
120 -- because it takes service lines into consideration.
121 -- Should use BASE_LINE_NUMBER whenever possible.
122 ----------------------------------------------------------------------
123 function LINE_NUMBER(
124 ORDER_LINE_ID IN NUMBER DEFAULT NULL
125 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
126 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
127 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
128 )
129 return NUMBER;
130
131
132 function ITEM_CONC_SEG(
133 ITEM_ID IN NUMBER DEFAULT NULL
134 , ORG_ID IN NUMBER DEFAULT NULL
135 )
136 return VARCHAR2;
137
138 function ORDER_TYPE(
139 ID IN NUMBER DEFAULT NULL
140 )
141 return VARCHAR2;
142
143 FUNCTION Configuration_Total
144 (
145 Config_Parent_Line_Id IN NUMBER
146 ) RETURN NUMBER;
147
148 Function Source_Order_number
149 ( P_ORIGINAL_SYSTEM_SOURCE_CODE VARCHAR2,
150 P_ORIGINAL_SYSTEM_REFERENCE VARCHAR2
151 ) return VARCHAR2;
152
153 Function Source_Order_Type
154 ( P_ORIGINAL_SYSTEM_SOURCE_CODE VARCHAR2,
155 P_ORIGINAL_SYSTEM_REFERENCE VARCHAR2
156 ) return VARCHAR2;
157 --Added missing default value Bug#5660501
158 function Supply_Res_Details(
159 P_Line_Id IN NUMBER DEFAULT NULL)
160 return VARCHAR2;
161 -- Added missing default value Bug#5660501
162 function Schedule_Status_Code(
163 Order_Line_Id IN NUMBER DEFAULT NULL)
164 return VARCHAR2;
165
166 function picking_line_schedule_status(
167 P_LINE_ID IN NUMBER DEFAULT NULL
168 )
169 return VARCHAR2;
170
171 function p_line_schedule_status_code(
172 P_LINE_ID IN NUMBER DEFAULT NULL
173 )
174 return VARCHAR2;
175
176
177 pragma restrict_references(Schedule_Status_Code, WNDS, WNPS);
178 pragma restrict_references(Supply_Res_Details, WNDS, WNPS);
179 pragma restrict_references(LINE_TOTAL, WNDS, WNPS);
180 pragma restrict_references(SERVICE_TOTAL, WNDS, WNPS);
181 pragma restrict_references( ATO_INDICATOR, WNDS,WNPS);
182 pragma restrict_references( GET_ATO_INDICATOR, WNDS,WNPS);
183 pragma restrict_references( RESERVED_QUANTITY, WNDS, WNPS);
184 pragma restrict_references( HOLD, WNDS, WNPS);
185 pragma restrict_references( SHIPMENT_NUMBER, WNDS, WNPS);
186 pragma restrict_references( LINE_NUMBER, WNDS, WNPS);
187 pragma restrict_references( ITEM_CONC_SEG, WNDS, WNPS);
188 pragma restrict_references( ORDER_TYPE, WNDS, WNPS);
189 pragma restrict_references( CONFIGURATION_TOTAL, WNDS,RNPS, WNPS);
190 pragma restrict_references( source_order_number, WNDS,RNPS, WNPS);
191 pragma restrict_references( source_order_type, WNDS,RNPS, WNPS);
192 pragma restrict_references( picking_line_schedule_status, WNDS); -- ,WNPS);
193 pragma restrict_references( p_line_schedule_status_code, WNDS,WNPS);
194
195 --
196 -- NAME
197 -- Order_Total
198 --
199 -- ARGUMENT
200 -- Header_Id Header_Id of the order
201 --
202 -- DESCRIPTION
203 -- Returns the pre-tax order total for the order identified
204 -- by Header_Id
205 --
206 FUNCTION Order_Total(Header_Id IN NUMBER) Return NUMBER;
207
208 PRAGMA Restrict_References(Order_Total, WNDS, WNPS);
209
210 FUNCTION Shipment_Total(P_Line_Id IN NUMBER) return NUMBER;
211
212 PRAGMA Restrict_References(Shipment_Total, WNDS, RNPS, WNPS);
213
214
215 --
216 -- Receipt and Accepted Quantity and Dates
217 --
218 -- Give the line ID, these functions will return
219 -- received ( accepted ) quantity or dates from
220 -- MTL_SO_RMA_INTERFACE table.
221 --
222 FUNCTION Received_qty( P_Line_Id NUMBER)
223 RETURN NUMBER;
224 pragma restrict_references (Received_qty, WNDS, WNPS);
225 FUNCTION Accepted_qty( P_Line_Id NUMBER)
226 RETURN NUMBER;
227 pragma restrict_references (Accepted_qty, WNDS, WNPS);
228 FUNCTION Received_Date( P_Line_Id NUMBER,
229 P_S29_DATE DATE)
230 RETURN DATE;
231 pragma restrict_references (Received_Date, WNDS, WNPS);
232 FUNCTION Accepted_Date( P_Line_Id NUMBER,
233 P_S29_DATE DATE)
234 RETURN DATE;
235 pragma restrict_references (Accepted_Date, WNDS, WNPS);
236 FUNCTION GET_TAX_EXEMPT_FLAG(
237 P_Reference_Code VARCHAR2,
238 P_Invoice_Flag VARCHAR2,
239 P_Order_Flag VARCHAR2,
240 P_No_Ref_Flag VARCHAR2,
241 P_Open_Flag VARCHAR2
242 )
243 RETURN VARCHAR2;
244 pragma restrict_references (GET_TAX_EXEMPT_FLAG, WNDS); -- , WNPS);
245 FUNCTION GET_TAX_EXEMPT_REASON( P_Reference_Code VARCHAR2,
246 P_Invoice_reason VARCHAR2,
247 P_Order_reason VARCHAR2,
248 P_No_Ref_reason VARCHAR2,
249 P_Open_Flag VARCHAR2)
250 RETURN VARCHAR2;
251 pragma restrict_references (GET_TAX_EXEMPT_REASON, WNDS); -- , WNPS);
252 FUNCTION GET_PRICE_ADJ_TOTAL(
253 P_Header_Id NUMBER,
254 P_Line_Id NUMBER
255 )
256 RETURN NUMBER;
257 pragma restrict_references (GET_PRICE_ADJ_TOTAL, WNDS, WNPS);
258
259
260 --
261 -- NAME
262 -- Get_Std_Tax_Exemption
263 --
264 PROCEDURE Get_Std_Tax_Exemption(Ship_To_Site_Use_Id IN NUMBER,
265 Invoice_To_customer_id IN NUMBER,
266 Date_Ordered IN DATE,
267 Tax_Exempt_Number OUT NOCOPY VARCHAR2,
268 Tax_Exempt_Reason OUT NOCOPY VARCHAR2);
269 pragma restrict_references (Get_Std_Tax_Exemption, WNDS); -- , WNPS);
270
271 --
272 -- Std_Tax_Exempt_Number
273 --
274 FUNCTION Std_Tax_Exempt_Number(Ship_To_Site_Use_Id IN NUMBER,
275 Invoice_To_customer_id IN NUMBER,
276 Date_Ordered IN DATE)
277 Return VARCHAR2;
278
279 pragma restrict_references (Std_Tax_Exempt_Number, WNDS); -- , WNPS);
280
281 --
282 -- Std_Tax_Exempt_Reason
283 --
284 FUNCTION Std_Tax_Exempt_Reason(Ship_To_Site_Use_Id IN NUMBER,
285 Invoice_To_customer_id IN NUMBER,
286 Date_Ordered IN DATE)
287 Return VARCHAR2;
288
289 pragma restrict_references (Std_Tax_Exempt_Reason, WNDS); -- , WNPS);
290
291
292 FUNCTION line_config_item_exists(X_line_id IN NUMBER) RETURN VARCHAR2;
293
294 PRAGMA restrict_references(line_config_item_exists, WNDS,RNPS,WNPS);
295
296
297 FUNCTION line_released_qty(X_line_id IN NUMBER) RETURN NUMBER;
298
299 PRAGMA restrict_references (line_released_qty, WNDS, RNPS, WNPS);
300
301
302 FUNCTION lot_expiration(X_inventory_item_id IN NUMBER,
303 X_organization_id IN NUMBER,
304 X_lot_number IN VARCHAR2) RETURN DATE;
305
306 PRAGMA restrict_references (lot_expiration, WNDS, RNPS, WNPS);
307
308
309 FUNCTION picking_line_reserved_qty(X_picking_line_id IN NUMBER)
310 RETURN NUMBER;
311
312 PRAGMA restrict_references(picking_line_reserved_qty, WNDS, WNPS, WNPS);
313
314 FUNCTION Open_Backordered_Quantity(X_line_id NUMBER)
315 RETURN NUMBER;
316
317 PRAGMA restrict_references(Open_Backordered_Quantity, WNDS, WNPS);
318
319 FUNCTION picking_line_item_id(X_picking_line_id IN NUMBER) return NUMBER;
320
321 PRAGMA restrict_references(picking_line_item_id, WNDS, WNPS);
322
323 function ATP_Date_Line_Id(
324 P_Session_id In Number,
325 P_Line_Id In Varchar2,
326 P_Inventory_Item_Id In Number)
327 return Date;
328
329 function Available_Quantity_Line_Id(
330 P_Session_id In Number,
331 P_Line_Id In Varchar2,
332 P_Inventory_Item_Id In Number)
333 return Number;
334
335 function Demand_Interface_RowId_Line_Id(
336 P_Session_id In Number,
337 P_Line_Id In Varchar2)
338 return Varchar2;
339
340 pragma restrict_references(ATP_DATE_LINE_ID, WNDS, WNPS);
341 pragma restrict_references(AVAILABLE_QUANTITY_LINE_ID, WNDS, WNPS);
342 pragma restrict_references(DEMAND_INTERFACE_ROWID_LINE_ID, WNDS, WNPS);
343
344 function ATP_Date_Delivery(
345 P_Session_id In Number,
346 P_Delivery In Varchar2)
347 return Date;
348
349 function Available_Quantity_Delivery(
350 P_Session_id In Number,
351 P_Delivery In Varchar2)
352 return Number;
353
354 function Demand_Interface_RowId_Del(
355 P_Session_id In Number,
356 P_Delivery In Varchar2)
357 return Varchar2;
358
359 pragma restrict_references(ATP_DATE_DELIVERY, WNDS, WNPS);
360 pragma restrict_references(AVAILABLE_QUANTITY_DELIVERY, WNDS, WNPS);
361 pragma restrict_references(DEMAND_INTERFACE_ROWID_DEL, WNDS, WNPS);
362
363 function get_organization_name
364 return VARCHAR2;
365
366 pragma restrict_references(GET_ORGANIZATION_NAME, WNDS, WNPS);
367
368 /* The order status is displayed as cancelled or Closed if the order
369 is in the state of Cancelld or Closed and the entry status field
370 will be set to non updatable. For other headers the entry status
371 will display the result for the value in the column s1
372 */
373
374 function get_entry_status_name(p_open_flag in varchar2,
375 p_cancelled_flag in varchar2,
376 p_s1_id in number)
377 return VARCHAR2;
378
379 pragma restrict_references(GET_ENTRY_STATUS_NAME, WNDS, WNPS);
380
381 function OPTION_LINE_NUMBER(
382 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
383 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
384 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
385 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
386 )
387 return NUMBER;
388 pragma restrict_references( OPTION_LINE_NUMBER, WNDS, WNPS);
389
390 function INVOICE_BALANCE(
391 P_CUSTOMER_TRX_ID IN NUMBER
392 )
393 return NUMBER;
394
395 pragma restrict_references( INVOICE_BALANCE, WNDS,WNPS);
396
397 function INVOICE_AMOUNT(
398 P_CUSTOMER_TRX_ID IN NUMBER
402 pragma restrict_references( INVOICE_AMOUNT, WNDS,WNPS);
399 )
400 return NUMBER;
401
403
404 function CYCLE_REQUEST
405 return NUMBER;
406
407 function BASE_LINE_NUMBER(
408 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
409 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
410 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
411 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
412 )
413 return NUMBER;
414
415 pragma restrict_references( BASE_LINE_NUMBER, WNDS, WNPS);
416
417 function SHIPMENT_SCHEDULE_NUMBER(
418 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
419 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
420 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
421 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
422 )
423 return NUMBER;
424
425 pragma restrict_references( SHIPMENT_SCHEDULE_NUMBER, WNDS, WNPS);
426
427 END OE_QUERY;