[Home] [Help]
PACKAGE: APPS.OE_SERVICE_UTIL
Source
1 Package OE_SERVICE_UTIL AUTHID CURRENT_USER As
2 /* $Header: OEXUSVCS.pls 120.0.12010000.1 2008/07/25 07:57:52 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_SERVICE_UTIL';
5 G_Header_Rec OE_Order_Pub.Header_Rec_Type :=
6 OE_Order_PUB.G_MISS_HEADER_REC;
7 G_old_header_rec OE_Order_PUB.Header_Rec_Type :=
8 OE_Order_PUB.G_MISS_HEADER_REC;
9 G_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type :=
10 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL;
11 G_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type :=
12 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL;
13 G_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type :=
14 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
15 G_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type :=
16 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
17 G_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
18 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
19 G_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
20 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
21 G_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
22 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
23 G_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
24 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
25 G_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type :=
26 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL;
27 G_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type :=
28 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL;
29 G_line_tbl OE_Order_PUB.Line_Tbl_Type :=
30 OE_Order_PUB.G_MISS_LINE_TBL;
31 G_old_line_tbl OE_Order_PUB.Line_Tbl_Type :=
32 OE_Order_PUB.G_MISS_LINE_TBL;
33 G_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type :=
34 OE_Order_PUB.G_MISS_LINE_ADJ_TBL;
35 G_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type :=
36 OE_Order_PUB.G_MISS_LINE_ADJ_TBL;
37 G_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type :=
38 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL;
39 G_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type :=
40 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL;
41 G_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
42 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL;
43 G_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
44 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL;
45 G_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
46 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
47 G_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
48 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
49 G_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type :=
50 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL;
51 G_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type :=
52 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL;
53 G_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type :=
54 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL;
55 G_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type :=
56 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL;
57 G_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
58 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
59 G_old_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
60 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
61 G_action_request_tbl OE_Order_PUB.request_tbl_type :=
62 OE_Order_PUB.g_miss_request_tbl;
63
64 -- Following Table will be used for the Records Group for Customer Product
65 -- lchen add reference_number, current_serial_number to fix bug 1529961 4/5/01
66 Type srv_cust_prod_rec IS RECORD(
67 customer_product_id NUMBER,
68 product VARCHAR2(2000),
69 product_description VARCHAR2(240),
70 reference_number NUMBER,
71 current_serial_number VARCHAR2(30));
72 l_srv_cust_prod_rec srv_cust_prod_rec;
73 Type srv_cust_prod_tbl IS TABLE OF srv_cust_prod_rec
74 Index By Binary_Integer;
75 l_srv_cust_prod_tbl srv_cust_prod_tbl;
76
77 -- Following Table will be used for the Records Group for Available Services
78 Type srv_item_id_tbl IS TABLE OF OE_ITEMS_V.INVENTORY_ITEM_ID%TYPE
79 Index By Binary_Integer;
80 l_srv_tbl srv_item_id_tbl;
81 Type srv_items_rec IS RECORD(
82 Item VARCHAR2(50),
83 Item_Id NUMBER,
84 Item_Description VARCHAR2(240),
85 Inventory_Item_Id NUMBER,
86 Inventory_Item VARCHAR2(40),
87 Item_Identifier_Type VARCHAR2(25));
88 l_srv_rec srv_items_rec;
89 Type t_service_rec IS RECORD(
90 Product_Item_Id NUMBER,
91 Customer_Id NUMBER,
92 Product_Revision VARCHAR2(20),
93 Request_Date DATE);
94 l_service_rec t_service_rec;
95
96
97 Procedure Notify_OC
98 ( p_api_version_number IN NUMBER
99 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
100 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
101 , p_control_rec IN OE_GLOBALS.Control_Rec_Type :=
102 OE_GLOBALS.G_MISS_CONTROL_REC
103 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
104 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
105 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
106 , p_header_rec IN OE_Order_PUB.Header_Rec_Type :=
107 OE_Order_PUB.G_MISS_HEADER_REC
108 , p_old_header_rec IN OE_Order_PUB.Header_Rec_Type :=
109 OE_Order_PUB.G_MISS_HEADER_REC
110 , p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
111 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL
112 , p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
113 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL
114 , p_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
115 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL
116 , p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
117 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL
118 , p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
119 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL
120 , p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
121 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL
122 , p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
123 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
124 , p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
125 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
126 , p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
127 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL
128 , p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
129 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL
130 , p_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
131 OE_Order_PUB.G_MISS_LINE_TBL
132 , p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
133 OE_Order_PUB.G_MISS_LINE_TBL
134 , p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
135 OE_Order_PUB.G_MISS_LINE_ADJ_TBL
136 , p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
137 OE_Order_PUB.G_MISS_LINE_ADJ_TBL
138 , p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
139 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL
140 , p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
141 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL
142 , p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
143 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL
144 , p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
145 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL
146 , p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
147 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
148 , p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
149 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
150 , p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
151 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL
152 , p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
153 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL
154 , p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
155 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL
156 , p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
157 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL
158 , p_Lot_Serial_val_tbl IN OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
159 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL
160 , p_old_Lot_Serial_val_tbl IN OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
161 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL
162 , p_action_request_tbl IN OE_Order_PUB.request_tbl_type :=
163 OE_Order_PUB.g_miss_request_tbl
164 );
165
166 -- Procedure to Check for the availability of the CRM APIS
167
168 Procedure Check_Proc
169 (
170 p_procedure_name IN varchar2,
171 x_return_status OUT NOCOPY /* file.sql.39 change */ varchar2
172 );
173
174
175 -- Procedure : Get_Service_Duration
176 --
177
178 PROCEDURE Get_Service_Duration
179 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
180 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
181 );
182
183 -- Procedure : Get_Service_Attribute
184 --
185
186 PROCEDURE Get_Service_Attribute
187 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
188 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
189 );
190
191 -- Procedure : Get_Service_Duration Overloaded for Form
192 --
193
194 PROCEDURE Get_Service_Duration
195 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
196 , p_line_rec IN OE_OE_FORM_LINE.Line_Rec_Type
197 , x_line_rec OUT NOCOPY /* file.sql.39 change */ OE_OE_FORM_LINE.Line_Rec_Type
198 );
199
200
201 -- Procedure : Get_Service_Attribute overloaded for Form
202 --
203
204 PROCEDURE Get_Service_Attribute
205 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
206 , p_line_rec IN OE_OE_FORM_LINE.Line_Rec_Type
207 , x_line_rec OUT NOCOPY /* file.sql.39 change */ OE_OE_FORM_LINE.Line_Rec_Type
208 );
209
210 PROCEDURE Get_Service_Ref_Line_Id
211 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
212 , p_order_number IN NUMBER
213 , p_line_number IN NUMBER
214 , p_shipment_number IN NUMBER
215 , p_option_number IN NUMBER
216 , x_reference_line_id OUT NOCOPY /* file.sql.39 change */ NUMBER
217 );
218
219 PROCEDURE Get_Service_Ref_System_Id
220 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
221 , p_system_number IN VARCHAR2
222 , p_customer_id IN NUMBER
223 , x_reference_system_id OUT NOCOPY /* file.sql.39 change */ NUMBER
224 );
225
226 PROCEDURE Get_Service_Ref_System_Name
227 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
228 , p_reference_system_id IN NUMBER
229 , p_customer_id IN NUMBER
230 , x_system_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
231 );
232
233 PROCEDURE Get_Service_Ref_Cust_Product
234 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
235 , p_reference_line_id IN NUMBER
236 , p_customer_id IN NUMBER
237 , x_cust_product OUT NOCOPY /* file.sql.39 change */ VARCHAR2
238 );
239
240 PROCEDURE Get_Cust_Product_Line_ID
241 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
242 , p_reference_line_id IN NUMBER
243 , p_customer_id IN NUMBER
244 , x_cust_product_line_id OUT NOCOPY /* file.sql.39 change */ NUMBER
245 );
246
247
248 PROCEDURE Get_Cust_Prod_RG
249 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
250 , p_customer_id IN NUMBER
251 , x_srv_cust_prod_tbl OUT NOCOPY /* file.sql.39 change */ OE_SERVICE_UTIL.SRV_CUST_PROD_TBL
252 );
253
254 PROCEDURE Get_Avail_Service_RG
255 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
256 , p_service_rec IN OE_SERVICE_UTIL.T_SERVICE_REC
257 , x_srv_cust_prod_tbl OUT NOCOPY /* file.sql.39 change */ OE_SERVICE_UTIL.SRV_ITEM_ID_TBL
258 );
259
260 PROCEDURE Retrieve_OC_Messages;
261
262 PROCEDURE Val_Item_Change( p_application_id IN NUMBER,
263 p_entity_short_name in VARCHAR2,
264 p_validation_entity_short_name in VARCHAR2,
265 p_validation_tmplt_short_name in VARCHAR2,
266 p_record_set_tmplt_short_name in VARCHAR2,
267 p_scope in VARCHAR2,
268 p_result OUT NOCOPY /* file.sql.39 change */ NUMBER );
269
270 PROCEDURE Update_Service_Lines
271 ( p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
272 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
273 );
274
275
276 /* added for bug 1533658 */
277 Procedure CASCADE_CHANGES
278 ( p_parent_line_id IN NUMBER,
279 p_request_rec IN OE_Order_Pub.Request_Rec_Type,
280 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
281 );
282
283 /* Added for 1799820 */
284
285 FUNCTION IB_ACTIVE RETURN BOOLEAN;
286
287 -- For bug 2247331
288 PROCEDURE Update_Service_Option_Numbers
289 ( p_top_model_line_id NUMBER );
290
291
292 END OE_SERVICE_UTIL;
293