[Home] [Help]
PACKAGE: APPS.OE_UPDATE_ACK_UTIL
Source
1 PACKAGE OE_Update_Ack_Util AUTHID CURRENT_USER AS
2 /* $Header: OEXUACKS.pls 120.3.12020000.2 2013/01/21 05:29:04 kadiraju ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'OE_Update_Ack_Util';
5
6 TYPE T_DATE IS TABLE OF DATE;
7 TYPE T_NUM IS TABLE OF NUMBER;
8 TYPE T_V240 IS TABLE OF VARCHAR(240);
9 TYPE T_V60 IS TABLE OF VARCHAR(60);
10 TYPE T_V30 IS TABLE OF VARCHAR(30);
11 TYPE T_V40 IS TABLE OF VARCHAR(40);
12 TYPE T_V80 IS TABLE OF VARCHAR(80);
13 TYPE T_V330 IS TABLE OF VARCHAR(330);
14 TYPE T_V3 IS TABLE OF VARCHAR(3);
15 TYPE T_V1 IS TABLE OF VARCHAR(1);
16 TYPE T_V50 IS TABLE OF VARCHAR(50);
17 TYPE T_V150 IS TABLE OF VARCHAR(150);
18 TYPE T_V1000 IS TABLE OF VARCHAR(1000);
19 TYPE T_V2000 IS TABLE OF VARCHAR(2000);
20 TYPE T_V360 IS TABLE OF VARCHAR(360);
21
22 TYPE Line_Rec_Type IS RECORD
23 (line_id T_NUM := T_NUM(),
24 header_id T_NUM := T_NUM(),
25 ship_to_org_id T_NUM := T_NUM(),
26 invoice_to_org_id T_NUM := T_NUM(),
27 invoice_to_contact_id T_NUM := T_NUM(),
28 ship_from_org_id T_NUM := T_NUM(),
29 agreement_id T_NUM := T_NUM(),
30 price_list_id T_NUM := T_NUM(),
31 arrival_set_id T_NUM := T_NUM(),
32 accounting_rule_id T_NUM := T_NUM(),
33 fulfillment_set_id T_NUM := T_NUM(),
34 inventory_item_id T_NUM := T_NUM(),
35 invoice_set_id T_NUM := T_NUM(),
36 invoicing_rule_id T_NUM := T_NUM(),
37 line_type_id T_NUM := T_NUM(),
38 order_source_id T_NUM := T_NUM(),
39 payment_term_id T_NUM := T_NUM(),
40 project_id T_NUM := T_NUM(),
41 salesrep_id T_NUM := T_NUM(),
42 ship_set_id T_NUM := T_NUM(),
43 ship_to_contact_id T_NUM := T_NUM(),
44 task_id T_NUM := T_NUM(),
45 ship_to_address1 T_V240 := T_V240(),
46 ship_to_address2 T_V240 := T_V240(),
47 ship_to_address3 T_V240 := T_V240(),
48 ship_to_address4 T_V240 := T_V240(),
49 ship_to_country T_V60 := T_V60(),
50 ship_to_state T_V60 := T_V60(),
51 ship_to_postal_code T_V60 := T_V60(),
52 ship_to_city T_V60 := T_V60(),
53 ship_to_org T_V240 := T_V240(),
54 ship_from_address_1 T_V240 := T_V240(),
55 ship_from_address_2 T_V240 := T_V240(),
56 ship_from_address_3 T_V240 := T_V240(),
57 ship_from_city T_V60 := T_V60(),
58 ship_from_postal_code T_V60 := T_V60(),
59 ship_from_country T_V60 := T_V60(),
60 ship_from_org T_V240 := T_V240(),
61 invoice_to_org T_V240 := T_V240(),
62 invoice_city T_V60 := T_V60(),
63 invoice_to_address1 T_V240 := T_V240(),
64 invoice_to_address2 T_V240 := T_V240(),
65 invoice_to_address3 T_V240 := T_V240(),
66 invoice_to_address4 T_V240 := T_V240(),
67 invoice_to_state T_V60 := T_V60(),
68 invoice_to_postal_code T_V60 := T_V60(),
69 invoice_address_code T_V40 := T_V40(),
70 invoice_to_country T_V60 := T_V60(),
71 agreement T_V240 := T_V240(),
72 price_list T_V240 := T_V240(),
73 arrival_set_name T_V30 := T_V30(),
74 accounting_rule T_V30 := T_V30(),
75 fob_point T_V240 := T_V240(),
76 freight_terms T_V80 := T_V80(),
77 fulfillment_set_name T_V30 := T_V30(),
78 inventory_item T_V240 := T_V240(),
79 invoice_set_name T_V30 := T_V30(),
80 invoicing_rule T_V30 := T_V30(),
81 line_type T_V30 := T_V30(),
82 payment_term T_V30 := T_V30(),
83 project T_V30 := T_V30(),
84 salesrep T_V240 := T_V240(),
85 ship_set_name T_V30 := T_V30(),
86 ship_to_contact T_V330 := T_V330(),
87 ship_to_contact_first_name T_V150 := T_V150(),
88 ship_to_contact_last_name T_V150 := T_V150(),
89 shipping_method T_V80 := T_V80(),
90 fob_point_code T_V30 := T_V30(),
91 freight_terms_code T_V30 := T_V30(),
92 shipping_method_code T_V30 := T_V30(),
93 tax_code T_V30 := T_V30(),
94 tax_point_code T_V30 := T_V30(),
95 tax_exempt_reason_code T_V30 := T_V30(),
96 task T_V30 := T_V30(),
97 first_ack_code T_V3 := T_V3(),
98 accounting_rule_duration T_NUM := T_NUM(),
99 actual_arrival_date T_DATE := T_DATE(),
100 actual_shipment_date T_DATE := T_DATE(),
101 ato_line_id T_NUM := T_NUM(),
102 authorized_to_ship_flag T_V1 := T_V1(),
103 booked_flag T_V1 := T_V1(),
104 cancelled_flag T_V1 := T_V1(),
105 cancelled_quantity T_NUM := T_NUM(),
106 change_sequence T_V50 := T_V50(),
107 component_code T_V1000 := T_V1000(),
108 component_number T_NUM := T_NUM(),
109 component_sequence_id T_NUM := T_NUM(),
110 config_display_sequence T_NUM := T_NUM(),
111 configuration_id T_NUM := T_NUM(),
112 top_model_line_id T_NUM := T_NUM(),
113 created_by T_NUM := T_NUM(),
114 creation_date T_DATE := T_DATE(),
115 cust_model_serial_number T_V50 := T_V50(),
116 cust_po_number T_V50 := T_V50(),
117 cust_production_seq_num T_V50 := T_V50(),
118 customer_dock_code T_V50 := T_V50(),
119 ordered_item T_V50 := T_V50(),
120 ordered_item_id T_NUM := T_NUM(), /* Bug # 4761560 */
121 customer_job T_V50 := T_V50(),
122 customer_production_line T_V50 := T_V50(),
123 customer_trx_line_id T_NUM := T_NUM(),
124 deliver_to_contact_id T_NUM := T_NUM(),
125 deliver_to_org_id T_NUM := T_NUM(),
126 delivery_lead_time T_NUM := T_NUM(),
127 demand_bucket_type_code T_V30 := T_V30(),
128 demand_class_code T_V30 := T_V30(),
129 dep_plan_required_flag T_V1 := T_V1(),
130 earliest_acceptable_date T_DATE := T_DATE(),
131 explosion_date T_DATE := T_DATE(),
132 freight_carrier_code T_V30 := T_V30(),
133 fulfilled_quantity T_NUM := T_NUM(),
134 item_revision T_V3 := T_V3(),
135 item_identifier_type T_V30 := T_V30(),
136 context T_V30 := T_V30(),
137 first_ack_date T_DATE := T_DATE(),
138 last_ack_code T_V30 := T_V30(),
139 last_ack_date T_DATE := T_DATE(),
140 last_update_date T_DATE := T_DATE(),
141 last_update_login T_NUM := T_NUM(),
142 last_updated_by T_NUM := T_NUM(),
143 latest_acceptable_date T_DATE := T_DATE(),
144 line_category_code T_V30 := T_V30(),
145 line_number T_NUM := T_NUM(),
146 link_to_line_id T_NUM := T_NUM(),
147 model_group_number T_NUM := T_NUM(),
148 open_flag T_V1 := T_V1(),
149 operation_code T_V30 := T_V30(),
150 option_flag T_V1 := T_V1(),
151 option_number T_NUM := T_NUM(),
152 order_quantity_uom T_V3 := T_V3(),
153 ordered_quantity T_NUM := T_NUM(),
154 org_id T_NUM := T_NUM(),
155 orig_sys_document_ref T_V50 := T_V50(),
156 orig_sys_line_ref T_V50 := T_V50(),
157 orig_sys_shipment_ref T_V50 := T_V50(),
158 over_ship_reason_code T_V30 := T_V30(),
159 over_ship_resolved_flag T_V1 := T_V1(),
160 pricing_date T_DATE := T_DATE(),
161 pricing_quantity T_NUM := T_NUM(),
162 pricing_quantity_uom T_V3 := T_V3(),
163 program_application_id T_NUM := T_NUM(),
164 program_id T_NUM := T_NUM(),
165 program_update_date T_DATE := T_DATE(),
166 promise_date T_DATE := T_DATE(),
167 reference_header_id T_NUM := T_NUM(),
168 reference_line_id T_NUM := T_NUM(),
169 reference_type T_V30 := T_V30(),
170 request_date T_DATE := T_DATE(),
171 request_id T_NUM := T_NUM(),
172 reserved_quantity T_NUM := T_NUM(),
173 return_reason_code T_V30 := T_V30(),
174 rla_schedule_type_code T_V30 := T_V30(),
175 schedule_arrival_date T_DATE := T_DATE(),
176 schedule_ship_date T_DATE := T_DATE(),
177 schedule_status_code T_V30 := T_V30(),
178 ship_model_complete_flag T_V1 := T_V1(),
179 ship_tolerance_above T_NUM := T_NUM(),
180 ship_tolerance_below T_NUM := T_NUM(),
181 shipment_number T_NUM := T_NUM(),
182 shipment_priority_code T_V30 := T_V30(),
183 shipped_quantity T_NUM := T_NUM(),
184 SHIPPING_QUANTITY T_NUM := T_NUM(),
185 SHIPPING_QUANTITY_UOM T_V3 := T_V3(),
186 SOLD_TO_ORG_ID T_NUM := T_NUM(),
187 SORT_ORDER T_V2000 := T_V2000(),
188 SOURCE_DOCUMENT_ID T_NUM := T_NUM(),
189 SOURCE_DOCUMENT_LINE_ID T_NUM := T_NUM(),
190 SOURCE_DOCUMENT_TYPE_ID T_NUM := T_NUM(),
191 SOURCE_TYPE_CODE T_V30 := T_V30(),
192 SPLIT_FROM_LINE_ID T_NUM := T_NUM(),
193 TAX_DATE T_DATE := T_DATE(),
194 TAX_EXEMPT_FLAG T_V1 := T_V1(),
195 TAX_EXEMPT_NUMBER T_V80 := T_V80(),
196 TAX_RATE T_NUM := T_NUM(),
197 TAX_VALUE T_NUM := T_NUM(),
198 UNIT_LIST_PRICE T_NUM := T_NUM(),
199 UNIT_SELLING_PRICE T_NUM := T_NUM(),
200 VEH_CUS_ITEM_CUM_KEY_ID T_NUM := T_NUM(),
201 VISIBLE_DEMAND_FLAG T_V1 := T_V1(),
202 split_from_line_ref T_V50 := T_V50(),
203 split_from_shipment_ref T_V50 := T_V50(),
204 SHIP_TO_EDI_LOCATION_CODE T_V40 := T_V40(),
205 Service_Txn_Reason_Code T_V30 := T_V30(),
206 Service_Txn_Comments T_V2000 := T_V2000(),
207 Service_Duration T_NUM := T_NUM(),
208 Service_Start_Date T_DATE := T_DATE(),
209 Service_End_Date T_DATE := T_DATE(),
210 Service_Coterminate_Flag T_V1 := T_V1(),
211 Service_Number T_NUM := T_NUM(),
212 Service_Period T_V3 := T_V3(),
213 Service_Reference_Type_Code T_V30 := T_V30(),
214 service_Reference_Line_Id T_NUM := T_NUM(),
215 Service_Reference_System_Id T_NUM := T_NUM(),
216 Credit_Invoice_Line_Id T_NUM := T_NUM(),
217 Bill_to_Edi_Location_Code T_V40 := T_V40(),
218 ship_from_edi_location_code T_V40 := T_V40(),
219 SHIP_FROM_ADDRESS_ID T_NUM := T_NUM(),
220 SHIP_TO_ADDRESS_ID T_NUM := T_NUM(),
221 SHIP_TO_ADDRESS_CODE T_V240 := T_V240(),
222 service_reference_line T_V50 := T_V50(),
223 service_reference_order T_V50 := T_V50(),
224 service_reference_system T_V50 := T_V50(),
225 customer_line_number T_V50 := T_V50(),
226 user_item_description T_V2000 := T_V2000(),
227 acknowledgment_type T_V30 := T_V30(),
228 blanket_number T_NUM := T_NUM(),
229 blanket_line_number T_NUM := T_NUM(),
230 original_inventory_item_id T_NUM := T_NUM(),
231 original_ordered_item_id T_NUM := T_NUM(),
232 original_ordered_item T_V2000 := T_V2000(),
233 original_item_identifier_type T_V30 := T_V30(),
234 item_relationship_type T_NUM := T_NUM(),
235 attribute1 T_V240 := T_V240(),
236 attribute10 T_V240 := T_V240(),
237 attribute11 T_V240 := T_V240(),
238 attribute12 T_V240 := T_V240(),
239 attribute13 T_V240 := T_V240(),
240 attribute14 T_V240 := T_V240(),
241 attribute15 T_V240 := T_V240(),
242 attribute16 T_V240 := T_V240(),
243 attribute17 T_V240 := T_V240(),
244 attribute18 T_V240 := T_V240(),
245 attribute19 T_V240 := T_V240(),
246 attribute2 T_V240 := T_V240(),
247 attribute20 T_V240 := T_V240(),
248 attribute3 T_V240 := T_V240(),
252 attribute7 T_V240 := T_V240(),
249 attribute4 T_V240 := T_V240(),
250 attribute5 T_V240 := T_V240(),
251 attribute6 T_V240 := T_V240(),
253 attribute8 T_V240 := T_V240(),
254 attribute9 T_V240 := T_V240(),
255 global_attribute1 T_V240 := T_V240(),
256 global_attribute10 T_V240 := T_V240(),
257 global_attribute11 T_V240 := T_V240(),
258 global_attribute12 T_V240 := T_V240(),
259 global_attribute13 T_V240 := T_V240(),
260 global_attribute14 T_V240 := T_V240(),
261 global_attribute15 T_V240 := T_V240(),
262 global_attribute16 T_V240 := T_V240(),
263 global_attribute17 T_V240 := T_V240(),
264 global_attribute18 T_V240 := T_V240(),
265 global_attribute19 T_V240 := T_V240(),
266 global_attribute2 T_V240 := T_V240(),
267 global_attribute20 T_V240 := T_V240(),
268 global_attribute3 T_V240 := T_V240(),
269 global_attribute4 T_V240 := T_V240(),
270 global_attribute5 T_V240 := T_V240(),
271 global_attribute6 T_V240 := T_V240(),
272 global_attribute7 T_V240 := T_V240(),
273 global_attribute8 T_V240 := T_V240(),
274 global_attribute9 T_V240 := T_V240(),
275 global_attribute_category T_V30 := T_V30(),
276 industry_attribute1 T_V240 := T_V240(),
277 industry_attribute10 T_V240 := T_V240(),
278 industry_attribute11 T_V240 := T_V240(),
279 industry_attribute12 T_V240 := T_V240(),
280 industry_attribute13 T_V240 := T_V240(),
281 industry_attribute14 T_V240 := T_V240(),
282 industry_attribute15 T_V240 := T_V240(),
283 industry_attribute16 T_V240 := T_V240(),
284 industry_attribute17 T_V240 := T_V240(),
285 industry_attribute18 T_V240 := T_V240(),
286 industry_attribute19 T_V240 := T_V240(),
287 industry_attribute20 T_V240 := T_V240(),
288 industry_attribute21 T_V240 := T_V240(),
289 industry_attribute22 T_V240 := T_V240(),
290 industry_attribute23 T_V240 := T_V240(),
291 industry_attribute24 T_V240 := T_V240(),
292 industry_attribute25 T_V240 := T_V240(),
293 industry_attribute26 T_V240 := T_V240(),
294 industry_attribute27 T_V240 := T_V240(),
295 industry_attribute28 T_V240 := T_V240(),
296 industry_attribute29 T_V240 := T_V240(),
297 industry_attribute30 T_V240 := T_V240(),
298 industry_attribute2 T_V240 := T_V240(),
299 industry_attribute3 T_V240 := T_V240(),
300 industry_attribute4 T_V240 := T_V240(),
301 industry_attribute5 T_V240 := T_V240(),
302 industry_attribute6 T_V240 := T_V240(),
303 industry_attribute7 T_V240 := T_V240(),
304 industry_attribute8 T_V240 := T_V240(),
305 industry_attribute9 T_V240 := T_V240(),
306 industry_context T_V30 := T_V30(),
307 TP_CONTEXT T_V30 := T_V30(),
308 TP_ATTRIBUTE1 T_V240 := T_V240(),
309 TP_ATTRIBUTE2 T_V240 := T_V240(),
310 TP_ATTRIBUTE3 T_V240 := T_V240(),
311 TP_ATTRIBUTE4 T_V240 := T_V240(),
312 TP_ATTRIBUTE5 T_V240 := T_V240(),
313 TP_ATTRIBUTE6 T_V240 := T_V240(),
314 TP_ATTRIBUTE7 T_V240 := T_V240(),
315 TP_ATTRIBUTE8 T_V240 := T_V240(),
316 TP_ATTRIBUTE9 T_V240 := T_V240(),
317 TP_ATTRIBUTE10 T_V240 := T_V240(),
318 TP_ATTRIBUTE11 T_V240 := T_V240(),
319 TP_ATTRIBUTE12 T_V240 := T_V240(),
320 TP_ATTRIBUTE13 T_V240 := T_V240(),
321 TP_ATTRIBUTE14 T_V240 := T_V240(),
322 TP_ATTRIBUTE15 T_V240 := T_V240(),
323 pricing_attribute1 T_V240 := T_V240(),
324 pricing_attribute10 T_V240 := T_V240(),
325 pricing_attribute2 T_V240 := T_V240(),
326 pricing_attribute3 T_V240 := T_V240(),
327 pricing_attribute4 T_V240 := T_V240(),
328 pricing_attribute5 T_V240 := T_V240(),
329 pricing_attribute6 T_V240 := T_V240(),
330 pricing_attribute7 T_V240 := T_V240(),
331 pricing_attribute8 T_V240 := T_V240(),
332 pricing_attribute9 T_V240 := T_V240(),
333 pricing_context T_V240 := T_V240(),
334 return_attribute1 T_V240 := T_V240(),
335 return_attribute10 T_V240 := T_V240(),
336 return_attribute11 T_V240 := T_V240(),
337 return_attribute12 T_V240 := T_V240(),
338 return_attribute13 T_V240 := T_V240(),
339 return_attribute14 T_V240 := T_V240(),
340 return_attribute15 T_V240 := T_V240(),
341 return_attribute2 T_V240 := T_V240(),
342 return_attribute3 T_V240 := T_V240(),
343 return_attribute4 T_V240 := T_V240(),
344 return_attribute5 T_V240 := T_V240(),
345 return_attribute6 T_V240 := T_V240(),
346 return_attribute7 T_V240 := T_V240(),
347 return_attribute8 T_V240 := T_V240(),
348 return_attribute9 T_V240 := T_V240(),
349 return_context T_V30 := T_V30(),
350 error_flag T_V1 := T_V1(),
351 customer_shipment_number T_V50 := T_V50(),
352 end_customer_id T_NUM := T_NUM(),
353 end_customer_contact_id T_NUM := T_NUM(),
354 end_customer_site_use_id T_NUM := T_NUM(),
355 ib_owner T_V60 := T_V60(),
356 ib_current_location T_V60 := T_V60(),
357 ib_installed_at_location T_V60 := T_V60(),
358 charge_periodicity_code T_V3 := T_V3() ,
359 end_customer_name T_V360 := T_V360(),
360 end_customer_number T_V50 := T_V50(),
361 end_customer_contact T_V360 := T_V360(),
362 end_customer_address1 T_V240 := T_V240(),
363 end_customer_address2 T_V240 := T_V240(),
364 end_customer_address3 T_V240 := T_V240(),
365 end_customer_address4 T_V240 := T_V240(),
366 end_customer_location T_V240 := T_V240(),
367 end_customer_city T_V60 := T_V60(),
368 end_customer_state T_V60 := T_V60(),
369 end_customer_postal_code T_V60 := T_V60(),
370 end_customer_country T_V60 := T_V60()
371 --Sol_ord_er #16014165 start
372 ,SERVICE_BILL_PROFILE_ID T_NUM := T_NUM(),
373 SERVICE_COV_TEMPLATE_ID T_NUM := T_NUM(),
374 SERVICE_SUBS_TEMPLATE_ID T_NUM := T_NUM(),
375 SERVICE_BILL_OPTION_CODE T_V240 := T_V240(),
376 SERVICE_FIRST_PERIOD_AMOUNT T_NUM := T_NUM(),
377 SUBSCRIPTION_ENABLE_FLAG T_V1 := T_V1(),
378 SERVICE_FIRST_PERIOD_ENDDATE T_DATE :=T_DATE()
379 --Sol_ord_er #16014165 end
380 );
381
382
383 PROCEDURE Update_Header_Ack_First(
384 p_header_id IN NUMBER
385 ,p_ack_code IN VARCHAR2
386 );
387
388 PROCEDURE Update_Header_Ack_Last(
389 p_header_id IN NUMBER
390 ,p_ack_code IN VARCHAR2
391 );
392
393 PROCEDURE Update_Line_Ack_First(
394 p_header_id IN NUMBER
395 ,p_line_id IN NUMBER
396 ,p_ack_code IN VARCHAR2
397 );
398
399 PROCEDURE Update_Line_Ack_Last(
400 p_header_id IN NUMBER
401 ,p_line_id IN NUMBER
402 ,p_ack_code IN VARCHAR2
403 );
404
405 Procedure Derive_Ack_Values
406 (p_itemtype In Varchar2,
407 p_itemkey In Varchar2,
408 p_actid In Number,
409 p_funcmode In Varchar2,
410 p_x_result In Out Nocopy Varchar2
411 );
412
413 Procedure Raise_Derive_Ack_Data_event
414 (p_transaction_type In Varchar2,
415 p_header_id In Number,
416 p_org_id In Number,
417 p_orig_sys_document_ref In Varchar2,
418 p_change_sequence In Varchar2,
419 p_sold_to_org_id In Number,
420 p_order_number In Number,
421 p_xml_msg_id In Number Default Null,
422 p_order_type_id In Number Default Null,
423 p_order_source_id In Number Default Null,
424 p_transaction_subtype In Varchar2 Default Null,
425 x_return_status Out Nocopy Varchar2
426 );
427
428 Procedure Oe_Edi_Selector
429 ( p_itemtype in varchar2,
430 p_itemkey in varchar2,
431 p_actid in number,
432 p_funcmode in varchar2,
433 p_x_result in out Nocopy varchar2
434 );
435
436 Procedure write_ack_data_values
437 (p_header_id In Number,
438 p_transaction_type In Varchar2,
439 x_return_status Out Nocopy Varchar2
440 );
441
442
443 END OE_Update_Ack_Util;