[Home] [Help]
PACKAGE BODY: APPS.OE_INF_POPULATE_PUB
Source
1 PACKAGE BODY OE_INF_POPULATE_PUB AS
2 /* $Header: OEXPIFPB.pls 120.2 2006/02/17 04:25:53 mbhoumik noship $ */
3
4
5 -- Global constant holding the package name
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_INF_POPULATE_PUB';
8
9
10 -- Start of Comments
11 -- API name Populate_Interface
12 -- Type Public
13 --
14 -- Pre-reqs
15 --
16 -- Parameters
17 --
18 -- Version Current version = 1.0
19 -- Initial version = 1.0
20 --
21 -- Notes
22 --
23 -- End of Comments
24
25 PROCEDURE Populate_Interface
26 ( p_api_version_number IN NUMBER
27 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
28 , p_order_number_from IN NUMBER
29 , p_order_source_id IN NUMBER
30 )
31 IS
32 order_number_v number := p_order_number_from;
33 order_source_id_v number := p_order_source_id;
34 old_header_id_v number := 0;
35 l_sold_to_org_id number;
36
37 --
38 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
39 --
40 BEGIN
41
42 IF l_debug_level > 0 THEN
43 oe_debug_pub.add( 'OEXPIFPB: ENTERING POPULATE_INTERFACE API' ) ;
44 END IF;
45
46 -- Select the header_id from the oe_order_headers_all table
47 -- for what ???
48 BEGIN
49
50 SELECT header_id, sold_to_org_id
51 INTO old_header_id_v, l_sold_to_org_id
52 FROM oe_order_headers_all
53 WHERE order_number = order_number_v;
54
55 IF l_debug_level > 0 THEN
56 oe_debug_pub.add( 'OEXPIFPB: HEADER ID => ' || TO_CHAR ( OLD_HEADER_ID_V ) ) ;
57 END IF;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 x_return_status := FND_API.G_RET_STS_ERROR;
62 IF l_debug_level > 0 THEN
63 oe_debug_pub.add( 'OEXPIFPB: EXITING POPULATE_INTERFACE API' ) ;
64 END IF;
65 END;
66
67 IF old_header_id_v is not null THEN
68
69 IF l_debug_level > 0 THEN
70 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_HEADERS_IFACE_ALL' ) ;
71 END IF;
72
73 INSERT INTO oe_headers_iface_all (
74 order_source_id
75 ,orig_sys_document_ref
76 ,change_sequence
77 ,order_source
78 ,org_id
79 ,header_id
80 ,order_number
81 ,version_number
82 ,ordered_date
83 ,order_category
84 ,order_type
85 ,order_type_id
86 ,request_date
87 ,shipment_priority
88 ,shipment_priority_code
89 ,demand_class
90 ,demand_class_code
91 ,price_list
92 ,price_list_id
93 ,tax_exempt_flag
94 ,tax_exempt_number
95 ,tax_exempt_reason
96 ,tax_exempt_reason_code
97 ,conversion_rate
98 ,conversion_rate_date
99 ,partial_shipments_allowed
100 --,remainder_orders_allowed
101 ,ship_tolerance_above
102 ,ship_tolerance_below
103 ,order_date_type_code
104 ,earliest_schedule_limit
105 ,latest_schedule_limit
106 --,invoice_tolerance_above
107 --,invoice_tolerance_below
108 --,related_po_number
109 ,agreement
110 ,agreement_id
111 ,tax_point
112 ,tax_point_code
113 ,customer_po_number
114 ,customer_payment_term
115 ,customer_payment_term_id
116 ,payment_type_code
117 ,payment_amount
118 ,check_number
119 ,credit_card_code
120 ,credit_card_holder_name
121 ,credit_card_number
122 ,credit_card_expiration_date
123 ,credit_card_approval_code
124 ,invoicing_rule
125 ,invoicing_rule_id
126 ,accounting_rule
127 ,accounting_rule_id
128 ,payment_term
129 ,payment_term_id
130 ,shipping_method
131 ,shipping_method_code
132 ,shipping_instructions
133 ,packing_instructions
134 ,freight_carrier_code
135 ,freight_terms
136 ,freight_terms_code
137 ,fob_point
138 ,fob_point_code
139 --,sold_from_org
140 --,sold_from_org_id
141 ,sold_to_org
142 ,sold_to_org_id
143 ,ship_from_org
144 ,ship_from_org_id
145 ,ship_to_org
146 ,ship_to_org_id
147 ,invoice_to_org
148 ,invoice_to_org_id
149 ,deliver_to_org
150 ,deliver_to_org_id
151 ,deliver_to_customer
152 ,deliver_to_customer_number
153 ,sold_to_contact
154 ,sold_to_contact_id
155 ,ship_to_contact
156 ,ship_to_contact_id
157 ,invoice_to_contact
158 ,invoice_to_contact_id
159 ,deliver_to_contact
160 ,deliver_to_contact_id
161 ,creation_date
162 ,created_by
163 ,last_update_date
164 ,last_updated_by
165 ,last_update_login
166 ,program_application_id
167 ,program_id
168 ,program_update_date
169 ,request_id
170 ,context
171 ,attribute1
172 ,attribute2
173 ,attribute3
174 ,attribute4
175 ,attribute5
176 ,attribute6
177 ,attribute7
178 ,attribute8
179 ,attribute9
180 ,attribute10
181 ,attribute11
182 ,attribute12
183 ,attribute13
184 ,attribute14
185 ,attribute15
186 ,global_attribute_category
187 ,global_attribute1
188 ,global_attribute2
189 ,global_attribute3
190 ,global_attribute4
191 ,global_attribute5
192 ,global_attribute6
193 ,global_attribute7
194 ,global_attribute8
195 ,global_attribute9
196 ,global_attribute10
197 ,global_attribute11
198 ,global_attribute12
199 ,global_attribute13
200 ,global_attribute14
201 ,global_attribute15
202 ,global_attribute16
203 ,global_attribute17
204 ,global_attribute18
205 ,global_attribute19
206 ,global_attribute20
207 ,error_flag
208 ,operation_code
209 ,ready_flag
210 ,status_flag
211 ,force_apply_flag
212 ,change_request_code
213 ,submission_datetime
214 ,conversion_type
215 ,conversion_type_code
216 ,transactional_curr
217 ,transactional_curr_code
218 ,salesrep_id
219 ,salesrep
220 ,sales_channel_code
221 ,return_reason_code
222 ,customer_id
223 ,customer_name
224 ,customer_number
225 ,invoice_address1
226 ,invoice_address2
227 ,invoice_address3
228 ,invoice_address4
229 ,invoice_city
230 ,invoice_country
231 ,invoice_county
232 ,invoice_customer
233 ,invoice_customer_number
234 ,invoice_postal_code
235 ,invoice_province_int
236 ,invoice_site
237 ,invoice_site_code
238 ,invoice_state
239 ,invoice_to_contact_first_name
240 ,invoice_to_contact_last_name
241 ,ordered_by_contact_first_name
242 ,ordered_by_contact_last_name
243 ,drop_ship_flag
244 ,header_po_context
245 ,po_attribute_1
246 ,po_attribute_2
247 ,po_attribute_3
248 ,po_attribute_4
249 ,po_attribute_5
250 ,po_attribute_6
251 ,po_attribute_7
252 ,po_attribute_8
253 ,po_attribute_9
254 ,po_attribute_10
255 ,po_attribute_11
256 ,po_attribute_12
257 ,po_attribute_13
258 ,po_attribute_14
259 ,po_attribute_15
260 ,po_revision_date
261 ,ship_to_address1
262 ,ship_to_address2
263 ,ship_to_address3
264 ,ship_to_address4
265 ,ship_to_city
266 ,ship_to_contact_first_name
267 ,ship_to_contact_last_name
268 ,ship_to_county
269 ,ship_to_customer
270 ,ship_to_customer_number
271 ,ship_to_postal_code
272 ,ship_to_province
273 ,ship_to_site_int
274 ,ship_to_state
275 ,ship_to_country
276 ,shipment_priority_code_int
277 ,change_reason
278 ,change_comments
279 ,sold_to_site_use_id
280 ,transaction_phase_code
281 ,expiration_date
282 ,quote_number
283 ,quote_date
284 ,sales_document_name
285 ,user_status_code
286 ,end_customer_id
287 ,end_customer_contact_id
288 ,end_customer_site_use_id
289 ,ib_owner_code --modified for bug 4240715
290 ,ib_current_location_code --modified for bug 4240715
291 ,ib_installed_at_location_code --modified for bug 4240715
292 )
293 select
294 order_source_id_v /* order_source_id number */
295 ,to_char(order_number_v) /* orig_sys_document_ref varchar2(50) */
296 ,'' /* change_sequence varchar2(50) */
297 ,'' /* order_source varchar2(30) */
298 ,org_id --moac /* org_id number */
299 ,'' /* header_id number */
300 ,'' /* order_number number */
301 ,'' /* version_number number */
302 ,sysdate /* ordered_date date */
303 ,'ORDER' /* order_category varchar2(30) */
304 ,'' /* order_type varchar2(30) */
305 ,order_type_id /* order_type_id number */
306 ,sysdate /* request_date date */
307 ,'' /* shipment_priority varchar2(30) */
308 ,shipment_priority_code /* shipment_priority_code varchar2(30) */
309 ,'' /* demand_class varchar2(30) */
310 ,demand_class_code /* demand_class_code varchar2(30) */
311 ,'' /* price_list varchar2(30) */
312 ,price_list_id /* price_list_id number */
313 ,tax_exempt_flag /* tax_exempt_flag varchar2(30) */
314 ,tax_exempt_number /* tax_exempt_number number */
315 ,'' /* tax_exempt_reason varchar2(30) */
316 ,tax_exempt_reason_code /* tax_exempt_reason_code varchar2(30) */
317 ,conversion_rate /* conversion_rate number */
318 ,conversion_rate_date /* conversion_rate_date date */
319 ,partial_shipments_allowed /* partial_shipments_allowed varchar2(1) */
320 --,remainder_orders_allowed /* remainder_orders_allowed varchar2(1) */
321 ,ship_tolerance_above /* ship_tolerance_above number */
322 ,ship_tolerance_below /* ship_tolerance_below number */
323 ,order_date_type_code /* order_date_type_code varchar2(30) */
324 ,earliest_schedule_limit /* earliest_schedule_limit number */
325 ,latest_schedule_limit /* latest_schedule_limit number */
326 --,invoice_tolerance_above /* invoice_tolerance_above number */
327 --,invoice_tolerance_below /* invoice_tolerance_below number */
328 --,related_po_number /* related_po_number varchar2(50) */
329 ,'' /* agreement varchar2(50) */
330 ,'' /* agreement_id number */
331 ,'' /* tax_point varchar2(30) */
332 ,tax_point_code /* tax_point_code varchar2(30) */
333 ,'' /* customer_po_number varchar2(50) */
334 ,'' /* customer_payment_term varchar2(30) */
335 ,'' /* customer_payment_term_id number */
336 ,payment_type_code /* payment_type_code varchar2(30) */
337 ,payment_amount /* payment_amount number */
338 ,check_number /* check_number varchar2(50) */
339 ,credit_card_code /* credit_card_code varchar2(30) */
340 ,credit_card_holder_name /* credit_card_holder_name varchar2(50) */
341 ,credit_card_number /* credit_card_number varchar2(50) */
342 ,credit_card_expiration_date /* credit_card_expiration_date date */
343 ,credit_card_approval_code /* credit_card_approval_code varchar2(50) */
344 ,'' /* invoicing_rule varchar2(30) */
345 ,invoicing_rule_id /* invoicing_rule_id number */
346 ,'' /* accounting_rule varchar2(30) */
347 ,accounting_rule_id /* accounting_rule_id number */
348 ,'' /* payment_term varchar2(30) */
349 ,payment_term_id /* payment_term_id number */
350 ,'' /* shipping_method varchar2(30) */
351 ,shipping_method_code /* shipping_method_code varchar2(30) */
352 ,'' /* shipping_instructions varchar2(240) */
353 ,'' /* packing_instructions varchar2(240) */
354 ,'' /* freight_carrier_code varchar2(30) */
355 ,'' /* freight_terms varchar2(30) */
356 ,freight_terms_code /* freight_terms_code varchar2(30) */
357 ,'' /* fob_point varchar2(30) */
358 ,fob_point_code /* fob_point_code varchar2(30) */
359 ,'' /* sold_from_org varchar2(30) */
363 ,'' /* ship_from_org varchar2(30) */
360 --,sold_from_org_id /* sold_from_org_id number */
361 --,'' /* sold_to_org varchar2(30) */
362 ,sold_to_org_id /* sold_to_org_id number */
364 ,ship_from_org_id /* ship_from_org_id number */
365 ,'' /* ship_to_org varchar2(30) */
366 ,ship_to_org_id /* ship_to_org_id number */
367 ,'' /* invoice_to_org varchar2(30) */
368 ,invoice_to_org_id /* invoice_to_org_id number */
369 ,'' /* deliver_to_org varchar2(30) */
370 ,'' /* deliver_to_org_id number */
371 ,'' /* deliver_to_customer varchar2(30) */
372 ,'' /* deliver_to_customer_number varchar2(30) */
373 ,'' /* sold_to_contact varchar2(30) */
374 ,sold_to_contact_id /* sold_to_contact_id number */
375 ,'' /* ship_to_contact varchar2(30) */
376 ,ship_to_contact_id /* ship_to_contact_id number */
377 ,'' /* invoice_to_contact varchar2(30) */
378 ,invoice_to_contact_id /* invoice_to_contact_id number */
379 ,'' /* deliver_to_contact varchar2(30) */
380 ,deliver_to_contact_id /* deliver_to_contact_id number */
381 ,sysdate /* creation_date date not null */
382 ,-1 /* created_by number not null */
383 ,sysdate /* last_update_date date not null */
384 ,-1 /* last_updated_by number not null */
385 ,0 /* last_update_login number */
386 ,program_application_id /* program_application_id number */
387 ,program_id /* program_id number */
388 ,program_update_date /* program_update_date date */
389 ,'' /* request_id number */
390 ,context /* context varchar2(30) */
391 ,attribute1 /* attribute1 varchar2(240) */
392 ,attribute2 /* attribute2 varchar2(240) */
393 ,attribute3 /* attribute3 varchar2(240) */
394 ,attribute4 /* attribute4 varchar2(240) */
395 ,attribute5 /* attribute5 varchar2(240) */
396 ,attribute6 /* attribute6 varchar2(240) */
397 ,attribute7 /* attribute7 varchar2(240) */
398 ,attribute8 /* attribute8 varchar2(240) */
399 ,attribute9 /* attribute9 varchar2(240) */
400 ,attribute10 /* attribute10 varchar2(240) */
401 ,attribute11 /* attribute11 varchar2(240) */
402 ,attribute12 /* attribute12 varchar2(240) */
403 ,attribute13 /* attribute13 varchar2(240) */
404 ,attribute14 /* attribute14 varchar2(240) */
405 ,attribute15 /* attribute15 varchar2(240) */
406 ,global_attribute_category /* global_attribute_category varchar2(30) */
407 ,global_attribute1 /* global_attribute1 varchar2(240) */
408 ,global_attribute2 /* global_attribute2 varchar2(240) */
409 ,global_attribute3 /* global_attribute3 varchar2(240) */
410 ,global_attribute4 /* global_attribute4 varchar2(240) */
411 ,global_attribute5 /* global_attribute5 varchar2(240) */
412 ,global_attribute6 /* global_attribute6 varchar2(240) */
413 ,global_attribute7 /* global_attribute7 varchar2(240) */
414 ,global_attribute8 /* global_attribute8 varchar2(240) */
415 ,global_attribute9 /* global_attribute9 varchar2(240) */
416 ,global_attribute10 /* global_attribute10 varchar2(240) */
417 ,global_attribute11 /* global_attribute11 varchar2(240) */
418 ,global_attribute12 /* global_attribute12 varchar2(240) */
419 ,global_attribute13 /* global_attribute13 varchar2(240) */
420 ,global_attribute14 /* global_attribute14 varchar2(240) */
421 ,global_attribute15 /* global_attribute15 varchar2(240) */
422 ,global_attribute16 /* global_attribute16 varchar2(240) */
423 ,global_attribute17 /* global_attribute17 varchar2(240) */
424 ,global_attribute18 /* global_attribute18 varchar2(240) */
425 ,global_attribute19 /* global_attribute19 varchar2(240) */
426 ,global_attribute20 /* global_attribute20 varchar2(240) */
427 ,'' /* error_flag varchar2(1) */
428 ,'INSERT' /* operation_code varchar2(30) */
429 ,'' /* ready_flag varchar2(1) */
430 ,'' /* status_flag varchar2(1) */
431 ,'' /* force_apply_flag varchar2(1) */
432 ,'' /* change_request_code varchar2(30) */
433 ,'' /* submission_datetime date */
434 ,'' /* conversion_type varchar2(30) */
435 ,conversion_type_code /* conversion_type_code varchar2(30) */
436 ,'' /* transactional_curr varchar2(30) */
437 ,transactional_curr_code /* transactional_curr_code varchar2(3) */
438 ,salesrep_id /* salesrep_id number */
439 ,'' /* salesrep varchar2(30) */
440 ,sales_channel_code /* sales_channel_code varchar2(30) */
441 ,return_reason_code /* return_reason_code varchar2(30) */
442 ,'' /* customer_id number */
446 ,'' /* invoice_address2 varchar2(35) */
443 ,'' /* customer_name varchar2(30) */
444 ,'' /* customer_number varchar2(30) */
445 ,'' /* invoice_address1 varchar2(35) */
447 ,'' /* invoice_address3 varchar2(35) */
448 ,'' /* invoice_address4 varchar2(35) */
449 ,'' /* invoice_city varchar2(30) */
450 ,'' /* invoice_country varchar2(20) */
451 ,'' /* invoice_county varchar2(25) */
452 ,'' /* invoice_customer varchar2(60) */
453 ,'' /* invoice_customer_number varchar2(30) */
454 ,'' /* invoice_postal_code varchar2(15) */
455 ,'' /* invoice_province_int varchar2(30) */
456 ,'' /* invoice_site varchar2(30) */
457 ,'' /* invoice_site_code varchar2(30) */
458 ,'' /* invoice_state varchar2(30) */
459 ,'' /* invoice_to_contact_first_namevarchar2(30)*/
460 ,'' /* invoice_to_contact_last_name varchar2(30)*/
461 ,'' /* ordered_by_contact_first_namevarchar2(30)*/
462 ,'' /* ordered_by_contact_last_name varchar2(30)*/
463 ,'' /* drop_ship_flag varchar2(1) */
464 ,'' /* header_po_context varchar2(30) */
465 ,'' /* po_attribute_1 varchar2(240) */
466 ,'' /* po_attribute_2 varchar2(240) */
467 ,'' /* po_attribute_3 varchar2(240) */
468 ,'' /* po_attribute_4 varchar2(240) */
469 ,'' /* po_attribute_5 varchar2(240) */
470 ,'' /* po_attribute_6 varchar2(240) */
471 ,'' /* po_attribute_7 varchar2(240) */
472 ,'' /* po_attribute_8 varchar2(240) */
473 ,'' /* po_attribute_9 varchar2(240) */
474 ,'' /* po_attribute_10 varchar2(240) */
475 ,'' /* po_attribute_11 varchar2(240) */
476 ,'' /* po_attribute_12 varchar2(240) */
477 ,'' /* po_attribute_13 varchar2(240) */
478 ,'' /* po_attribute_14 varchar2(240) */
479 ,'' /* po_attribute_15 varchar2(240) */
480 ,'' /* po_revision_date date */
481 ,'' /* ship_to_address_1 varchar2(30) */
482 ,'' /* ship_to_address_2 varchar2(30) */
483 ,'' /* ship_to_address_3 varchar2(30) */
484 ,'' /* ship_to_address_4 varchar2(30) */
485 ,'' /* ship_to_city varchar2(30) */
486 ,'' /* ship_to_contact_first_namevarchar2(30) */
487 ,'' /* ship_to_contact_last_name varchar2(30) */
488 ,'' /* ship_to_county varchar2(30) */
489 ,'' /* ship_to_customer varchar2(30) */
490 ,'' /* ship_to_customer_numbervarchar2(30) */
491 ,'' /* ship_to_postal_code varchar2(30) */
492 ,'' /* ship_to_province varchar2(30) */
493 ,'' /* ship_to_site_int varchar2(30) */
494 ,'' /* ship_to_state varchar2(30) */
495 ,'' /* ship_to_country varchar2(30) */
496 ,'' /* shipment_priority_code_intvarchar2(30) */
497 ,'' /* change_reason varchar2(30) */
498 ,'' /* change_comments varchar2(2000) */
499 ,sold_to_site_use_id /* sold_to_site_use_id number */
500 ,transaction_phase_code /* transaction_phase_code varchar2(30) */
501 ,expiration_date /* expiration_date date */
502 ,quote_number /* quote_number number */
503 ,quote_date /* quote_date date */
504 ,sales_document_name /* sales_document_name varchar2(240) */
505 ,user_status_code /* user_status_code varcahr2(30) */
506 ,end_customer_id /* end_customer_id number */
507 ,end_customer_contact_id /* end_customer_contact_id number */
508 ,end_customer_site_use_id /* end_customer_site_use_id number */
509 ,ib_owner /* ib_owner varchar2(60) */
510 ,ib_current_location /* ib_current_location varchar2(60) */
511 ,ib_installed_at_location /* ib_installed_at_location varchar2(60) */
512 from oe_order_headers_all
513 where order_number = order_number_v;
514
515 IF SQL%ROWCOUNT > 0 THEN
516 IF l_debug_level > 0 THEN
517 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_HEADERS_IFACE_ALL' ) ;
518 END IF;
519 ELSE
520 IF l_debug_level > 0 THEN
521 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_HEADERS_IFACE_ALL' ) ;
522 END IF;
523 return;
524 END IF;
525
526 IF l_debug_level > 0 THEN
527 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_LINES_IFACE_ALL' ) ;
528 END IF;
529
530 INSERT INTO oe_lines_iface_all (
531 order_source_id /* number */
532 , orig_sys_document_ref /* varchar2(50) */
533 , orig_sys_line_ref /* varchar2(50) */
534 , orig_sys_shipment_ref /* varchar2(50) */
538 , shipment_number /* number */
535 , change_sequence /* varchar2(50) */
536 , org_id /* number */
537 , line_number /* number */
539 , line_id /* number */
540 --, invoice_number /* varchar2(20) */
541 , line_type_id /* number */
542 , line_type /* varchar2(30) */
543 , item_type_code /* varchar2(30) */
544 , inventory_item_id /* number */
545 , inventory_item /* varchar2(30) */
546 , link_to_line_ref /* varchar2(50) */
547 , top_model_line_ref /* varchar2(50) */
548 , component_code
549 -- , component_sequence_id
550 -- , sort_order
551 , request_date /* date */
552 , promise_date /* date */
553 , schedule_ship_date /* date */
554 , schedule_arrival_date /* date */
555 , delivery_lead_time /* number */
556 , ordered_quantity /* number */
557 , order_quantity_uom /* varchar2(3) */
558 , ordered_quantity2 /* number OPM */
559 , ordered_quantity_uom2 /* varchar2(3) OPM */
560 , shipping_quantity /* number */
561 , shipping_quantity_uom /* varchar2(3) */
562 , shipped_quantity /* number */
563 , cancelled_quantity /* number */
564 , fulfilled_quantity /* number */
565 , pricing_quantity /* number */
566 , pricing_quantity_uom /* varchar2(3) */
567 --, sold_from_org_id /* number */
568 --, sold_from_org /* varchar2(30) */
569 , sold_to_org_id /* number */
570 , sold_to_org /* varchar2(30) */
571 , ship_from_org_id /* number */
572 , ship_from_org /* varchar2(30) */
573 , ship_to_org_id /* number */
574 , ship_to_org /* varchar2(30) */
575 , deliver_to_org_id /* number */
576 , deliver_to_org /* varchar2(30) */
577 , invoice_to_org_id /* number */
578 , invoice_to_org /* varchar2(30) */
579 , ship_set_id /* number */
580 , ship_set_name /* varchar(30) */
581 , ship_to_address1 /* varchar2(30) */
582 , ship_to_address2 /* varchar2(30) */
583 , ship_to_address3 /* varchar2(30) */
584 , ship_to_address4 /* varchar2(30) */
585 , ship_to_city /* varchar2(30) */
586 , ship_to_county /* varchar2(30) */
587 , ship_to_state /* varchar2(30) */
588 , ship_to_postal_code /* varchar2(30) */
589 , ship_to_country /* varchar2(30) */
590 , ship_to_contact_first_name /* varchar2(30) */
591 , ship_to_contact_last_name /* varchar2(30) */
592 , ship_to_contact_job_title /* varchar2(30) */
593 , ship_to_contact_area_code1 /* varchar2(10) */
594 , ship_to_contact_area_code2 /* varchar2(10) */
595 , ship_to_contact_area_code3 /* varchar2(10) */
596 , ship_to_contact_id /* number */
597 , ship_to_contact /* varchar2(30) */
598 , deliver_to_contact_id /* number */
599 , deliver_to_contact /* varchar2(30) */
600 , invoice_to_contact_id /* number */
601 , invoice_to_contact /* varchar2(30) */
602 , drop_ship_flag /* varchar2(1) */
603 , ship_tolerance_above /* number */
604 , ship_tolerance_below /* number */
605 --, invoice_tolerance_above /* number */
606 --, invoice_tolerance_below /* number */
607 --, cost_type_id /* number */
608 --, cost_type /* varchar2(30) */
609 --, costing_date /* date */
610 --, material_cost /* number */
611 --, material_overhead_cost /* number */
612 --, resource_cost /* number */
613 --, outside_processing_cost /* number */
614 --, overhead_cost /* number */
615 , price_list /* varchar2(30) */
616 , price_list_id /* number */
617 , pricing_date /* date */
618 , unit_list_price /* number */
619 , unit_selling_price /* number */
620 , explosion_date /* date */
621 , calculate_price_flag /* varchar2(1) */
622 , tax_code /* varchar2(50) */
623 , tax /* varchar2(50) */
624 , tax_value /* number */
625 , tax_date /* date */
626 , tax_point_code /* varchar2(30) */
627 , tax_point /* varchar2(30) */
628 , tax_exempt_flag /* varchar2(30) */
629 , tax_exempt_number /* number */
630 , tax_exempt_reason_code /* varchar2(30) */
631 , tax_exempt_reason /* varchar2(30) */
632 , agreement_id /* number */
636 , accounting_rule_id /* number */
633 , agreement /* varchar2(30) */
634 , invoicing_rule_id /* number */
635 , invoicing_rule /* varchar2(30) */
637 , accounting_rule /* varchar2(30) */
638 , payment_term_id /* number */
639 , payment_term /* varchar2(30) */
640 , demand_class /* varchar2(30) */
641 , demand_class_code /* varchar2(30) */
642 , shipment_priority_code /* varchar2(30) */
643 , shipment_priority /* varchar2(30) */
644 , shipping_method_code /* varchar2(30) */
645 , shipping_method /* varchar2(30) */
646 , freight_carrier_code /* varchar2(30) */
647 , freight_terms_code /* varchar2(30) */
648 , freight_terms /* varchar2(30) */
649 , fob_point_code /* varchar2(30) */
650 , fob_point /* varchar2(30) */
651 , reference_type /* varchar2(30) */
652 , reference_header_id /* number */
653 , reference_header /* varchar2(30) */
654 , reference_line_id /* number */
655 , reference_line /* varchar2(30) */
656 , customer_po_number /* varchar2(50) */
657 , customer_line_number /* varchar2(50) */
658 , customer_shipment_number /* varchar2(50) */
659 --, related_po_number /* varchar2(50) */
660 , customer_item_id_type /* varchar2(30) */
661 , customer_item_id /* number */
662 , customer_item_name /* number */
663 -- , customer_item_revision /* varchar2(50) */
664 , customer_item_net_price /* number */
665 , customer_payment_term_id /* number */
666 , customer_payment_term /* varchar2(30) */
667 , demand_bucket_type_code /* varchar2(30) */
668 , schedule_item_detail /* varchar2(30) */
669 , demand_stream /* varchar2(30) */
670 , customer_dock_code /* varchar2(30) */
671 , customer_dock /* varchar2(50) */
672 , customer_job /* varchar2(50) */
673 , customer_production_line /* varchar2(50) */
674 , cust_model_serial_number /* varchar2(50) */
675 --, planning_prod_seq_num /* number */
676 , project_id /* number */
677 , project /* varchar2(30) */
678 , task_id /* number */
679 , task /* varchar2(30) */
680 , end_item_unit_number /* varchar2(30) */
681 , item_revision /* varchar2(3) */
682 --, payment_trx_id /* number */
683 --, payment_trx /* varchar2(30) */
684 , line_po_context /* varchar2(30) */
685 , contract_po_number /* varchar2(150) */
686 , attribute1 /* varchar2(240) */
687 , attribute2 /* varchar2(240) */
688 , attribute3 /* varchar2(240) */
689 , attribute4 /* varchar2(240) */
690 , attribute5 /* varchar2(240) */
691 , attribute6 /* varchar2(240) */
692 , attribute7 /* varchar2(240) */
693 , attribute8 /* varchar2(240) */
694 , attribute9 /* varchar2(240) */
695 , attribute10 /* varchar2(240) */
696 , attribute11 /* varchar2(240) */
697 , attribute12 /* varchar2(240) */
698 , attribute13 /* varchar2(240) */
699 , attribute14 /* varchar2(240) */
700 , attribute15 /* varchar2(240) */
701 , industry_context /* varchar2(30) */
702 , industry_attribute1 /* varchar2(240) */
703 , industry_attribute2 /* varchar2(240) */
704 , industry_attribute3 /* varchar2(240) */
705 , industry_attribute4 /* varchar2(240) */
706 , industry_attribute5 /* varchar2(240) */
707 , industry_attribute6 /* varchar2(240) */
708 , industry_attribute7 /* varchar2(240) */
709 , industry_attribute8 /* varchar2(240) */
710 , industry_attribute9 /* varchar2(240) */
711 , industry_attribute10 /* varchar2(240) */
712 , industry_attribute11 /* varchar2(240) */
713 , industry_attribute12 /* varchar2(240) */
714 , industry_attribute13 /* varchar2(240) */
715 , industry_attribute14 /* varchar2(240) */
716 , industry_attribute15 /* varchar2(240) */
717 , industry_attribute16 /* varchar2(240) */
718 , industry_attribute17 /* varchar2(240) */
719 , industry_attribute18 /* varchar2(240) */
720 , industry_attribute19 /* varchar2(240) */
721 , industry_attribute20 /* varchar2(240) */
722 , industry_attribute21 /* varchar2(240) */
723 , industry_attribute22 /* varchar2(240) */
724 , industry_attribute23 /* varchar2(240) */
725 , industry_attribute24 /* varchar2(240) */
729 , industry_attribute28 /* varchar2(240) */
726 , industry_attribute25 /* varchar2(240) */
727 , industry_attribute26 /* varchar2(240) */
728 , industry_attribute27 /* varchar2(240) */
730 , industry_attribute29 /* varchar2(240) */
731 , industry_attribute30 /* varchar2(240) */
732 , pricing_context /* varchar2(150) */
733 , pricing_attribute1 /* varchar2(240) */
734 , pricing_attribute2 /* varchar2(240) */
735 , pricing_attribute3 /* varchar2(240) */
736 , pricing_attribute4 /* varchar2(240) */
737 , pricing_attribute5 /* varchar2(240) */
738 , pricing_attribute6 /* varchar2(240) */
739 , pricing_attribute7 /* varchar2(240) */
740 , pricing_attribute8 /* varchar2(240) */
741 , pricing_attribute9 /* varchar2(240) */
742 , pricing_attribute10 /* varchar2(240) */
743 , global_attribute_category /* varchar2(30) */
744 , global_attribute1 /* varchar2(240) */
745 , global_attribute2 /* varchar2(240) */
746 , global_attribute3 /* varchar2(240) */
747 , global_attribute4 /* varchar2(240) */
748 , global_attribute5 /* varchar2(240) */
749 , global_attribute6 /* varchar2(240) */
750 , global_attribute7 /* varchar2(240) */
751 , global_attribute8 /* varchar2(240) */
752 , global_attribute9 /* varchar2(240) */
753 , global_attribute10 /* varchar2(240) */
754 , global_attribute11 /* varchar2(240) */
755 , global_attribute12 /* varchar2(240) */
756 , global_attribute13 /* varchar2(240) */
757 , global_attribute14 /* varchar2(240) */
758 , global_attribute15 /* varchar2(240) */
759 , global_attribute16 /* varchar2(240) */
760 , global_attribute17 /* varchar2(240) */
761 , global_attribute18 /* varchar2(240) */
762 , global_attribute19 /* varchar2(240) */
763 , global_attribute20 /* varchar2(240) */
764 , return_attribute1 /* varchar2(240) */
765 , return_attribute2 /* varchar2(240) */
766 , return_attribute3 /* varchar2(240) */
767 , return_attribute4 /* varchar2(240) */
768 , return_attribute5 /* varchar2(240) */
769 , return_attribute6 /* varchar2(240) */
770 , return_attribute7 /* varchar2(240) */
771 , return_attribute8 /* varchar2(240) */
772 , return_attribute9 /* varchar2(240) */
773 , return_attribute10 /* varchar2(240) */
774 , return_attribute11 /* varchar2(240) */
775 , return_attribute12 /* varchar2(240) */
776 , return_attribute13 /* varchar2(240) */
777 , return_attribute14 /* varchar2(240) */
778 , return_attribute15 /* varchar2(240) */
779 , inventory_item_segment_1 /* varchar2(240) */
780 , inventory_item_segment_2 /* varchar2(240) */
781 , inventory_item_segment_3 /* varchar2(240) */
782 , inventory_item_segment_4 /* varchar2(240) */
783 , inventory_item_segment_5 /* varchar2(240) */
784 , inventory_item_segment_6 /* varchar2(240) */
785 , inventory_item_segment_7 /* varchar2(240) */
786 , inventory_item_segment_8 /* varchar2(240) */
787 , inventory_item_segment_9 /* varchar2(240) */
788 , inventory_item_segment_10 /* varchar2(240) */
789 , inventory_item_segment_11 /* varchar2(240) */
790 , inventory_item_segment_12 /* varchar2(240) */
791 , inventory_item_segment_13 /* varchar2(240) */
792 , inventory_item_segment_14 /* varchar2(240) */
793 , inventory_item_segment_15 /* varchar2(240) */
794 , inventory_item_segment_16 /* varchar2(240) */
795 , inventory_item_segment_17 /* varchar2(240) */
796 , inventory_item_segment_18 /* varchar2(240) */
797 , inventory_item_segment_19 /* varchar2(240) */
798 , inventory_item_segment_20 /* varchar2(240) */
799 , operation_code /* varchar2(30) */
800 , error_flag /* varchar2(1) */
801 , status_flag /* varchar2(1) */
802 , change_request_code /* varchar2(30) */
803 , request_id /* number */
804 , created_by /* number not null */
805 , creation_date /* date not null */
806 , last_updated_by /* number not null */
807 , last_update_date /* date not null */
808 , last_update_login /* number */
809 , program_application_id /* number */
810 , program_id /* number */
811 , program_update_date /* date */
812 , split_from_line_id /* number */
813 , cust_production_seq_num /* varchar2(50) */
814 , authorized_to_ship_flag /* varchar2(1) */
815 , veh_cus_item_cum_key_id /* number */
816 , salesrep_id /* number */
817 , return_reason_code /* varchar2(30) */
821 , change_reason /* varchar2(30) */
818 , over_ship_reason_code /* varchar2(30) */
819 , over_ship_resolved_flag /* varchar2(1) */
820 , credit_invoice_line_id /* number */
822 , change_comments /* varchar2(2000) */
823 , user_item_description
824 , end_customer_id /* number */
825 , end_customer_contact_id /* number */
826 , end_customer_site_use_id /* number */
827 , ib_owner_code /* varchar2(60) */ --modified for bug 4240715
828 , ib_current_location_code /* varchar2(60) */ --modified for bug 4240715
829 , ib_installed_at_location_code /* varchar2(60) */ --modified for bug 4240715
830 -- INVCONV
831 , shipping_quantity2 /* number */
832 , shipping_quantity_uom2 /* varchar2(3) */
833 , shipped_quantity2 /* number */
834 , cancelled_quantity2 /* number */
835 , fulfilled_quantity2 /* number */
836
837
838 )
839 select
840 order_source_id_v /* order_source_id number */
841 , to_char(order_number_v) /* orig_sys_document_ref varchar2(50) */
842 , rtrim(to_char(line_number)||'-'||to_char(option_number))
843 /* orig_sys_line_ref varchar2(50) */
844 , '' /* orig_sys_shipment_refvarchar2(50) */
845 , '' /* change_sequence varchar2(50) */
846 , org_id --moac /* org_id number */
847 , line_number /* line_number number */
848 , '' /* shipment_number number */
849 -- Following is inserted only for the purpose of the referece for the child
850 -- level once they are created this should be updated to NULL
851 , line_id /* line_id number */
852 --, '' /* invoice_number varchar2(20) */
853 , line_type_id /* line_type_id number */
854 , '' /* line_type varchar2(30) */
855 , item_type_code /* item_type_code varchar2(30) */
856 , inventory_item_id /* inventory_item_id number */
857 , '' /* inventory_item varchar2(30) */
858 , decode(link_to_line_id, NULL, NULL, get_link_to_line_ref(line_id))
859 /* link_to_line_ref varchar2(50) */
860 , decode(top_model_line_id, NULL, NULL, get_top_model_line_ref(line_id))
861 /* link_to_line_ref varchar2(50) */
862 , component_code
863 -- , component_sequence_id
864 -- , sort_order
865 , request_date /* request_date date */
866 , promise_date /* promise_date date */
867 , schedule_ship_date /* schedule_ship_date date */
868 , schedule_arrival_date /* schedule_arrival_date date */
869 , delivery_lead_time /* delivery_lead_time number */
870 , ordered_quantity /* ordered_quantity number */
871 , order_quantity_uom /* order_quantity_uom varchar2(3) */
872 , ordered_quantity2 /* ordered_quantity number OPM */
873 , ordered_quantity_uom2 /* order_quantity_uom varchar2(3) OPM */
874 , shipping_quantity /* shipping_quantity number */
875 , shipping_quantity_uom /* shipping_quantity_uomvarchar2(3) */
876 , shipped_quantity /* shipped_quantity number */
877 , '' /* cancelled_quantity number */
878 , fulfilled_quantity /* fulfilled_quantity number */
879 , '' /* pricing_quantity number */
880 , '' /* pricing_quantity_uom varchar2(3) */
881 --, sold_from_org_id /* sold_from_org_id number */
882 --, '' /* sold_from_org varchar2(30) */
883 , sold_to_org_id /* sold_to_org_id number */
884 , '' /* sold_to_org varchar2(30) */
885 , ship_from_org_id /* ship_from_org_id number */
886 , '' /* ship_from_org varchar2(30) */
887 , ship_to_org_id /* ship_to_org_id number */
888 , '' /* ship_to_org varchar2(30) */
889 , '' /* deliver_to_org_id number */
890 , '' /* deliver_to_org varchar2(30) */
891 , invoice_to_org_id /* invoice_to_org_id number */
892 , '' /* invoice_to_org varchar2(30) */
893 , '' /* ship_set_id number */
894 , ship_set_id /* ship_set_name varchar2(30) */
895 , '' /* ship_to_address_1 varchar2(30) */
896 , '' /* ship_to_address_2 varchar2(30) */
897 , '' /* ship_to_address_3 varchar2(30) */
898 , '' /* ship_to_address_4 varchar2(30) */
899 , '' /* ship_to_city varchar2(30) */
900 , '' /* ship_to_county varchar2(30) */
901 , '' /* ship_to_state varchar2(30) */
902 , '' /* ship_to_postal_code varchar2(30) */
903 , '' /* ship_to_country varchar2(30) */
904 , '' /* ship_to_contact_first_namevarchar2(30) */
905 , '' /* ship_to_contact_last_name varchar2(30) */
906 , '' /* ship_to_contact_job_title varchar2(30) */
907 , '' /* ship_to_contact_area_code1varchar2(10) */
908 , '' /* ship_to_contact_area_code2varchar2(10) */
912 , deliver_to_contact_id /* deliver_to_contact_idnumber */
909 , '' /* ship_to_contact_area_code3varchar2(10) */
910 , ship_to_contact_id /* ship_to_contact_id number */
911 , '' /* ship_to_contact varchar2(30) */
913 , '' /* deliver_to_contact varchar2(30) */
914 , invoice_to_contact_id /* invoice_to_contact_idnumber */
915 , '' /* invoice_to_contact varchar2(30) */
916 , '' /* drop_ship_flag varchar2(1) */
917 , ship_tolerance_above /* ship_tolerance_above number */
918 , ship_tolerance_below /* ship_tolerance_below number */
919 --, invoice_tolerance_above /* invoice_tolerance_above number */
920 --, invoice_tolerance_below /* invoice_tolerance_below number */
921 --, cost_type_id /* cost_type_id number */
922 --, '' /* cost_type varchar2(30) */
923 --, costing_date /* costing_date date */
924 --, material_cost /* material_cost number */
925 --, material_overhead_cost /* material_overhead_cost number */
926 --, resource_cost /* resource_cost number */
927 --, outside_processing_cost /* outside_processing_cost number */
928 --, overhead_cost /* overhead_cost number */
929 , '' /* price_list varchar2(30) */
930 , price_list_id /* price_list_id number */
931 , pricing_date /* pricing_date date */
932 , nvl(unit_list_price,0) /* unit_list_price number */
933 , nvl(unit_selling_price,0) /* unit_selling_price number */
934 , explosion_date /* date */
935 , 'N' /* calculate_price_flag varchar2(1) */
936 , tax_code /* tax_code varchar2(50) */
937 , '' /* tax varchar2(50) */
938 , '' /* tax_value number */
939 , tax_date /* tax_date date */
940 , tax_point_code /* tax_point_code varchar2(30) */
941 , '' /* tax_point varchar2(30) */
942 , tax_exempt_flag /* tax_exempt_flag varchar2(30) */
943 , tax_exempt_number /* tax_exempt_number number */
944 , tax_exempt_reason_code /* tax_exempt_reason_code varchar2(30) */
945 , '' /* tax_exempt_reason varchar2(30) */
946 , '' /* agreement_id number */
947 , '' /* agreement varchar2(30) */
948 , invoicing_rule_id /* invoicing_rule_id number */
949 , '' /* invoicing_rule varchar2(30) */
950 , accounting_rule_id /* accounting_rule_id number */
951 , '' /* accounting_rule varchar2(30) */
952 , payment_term_id /* payment_term_id number */
953 , '' /* payment_term varchar2(30) */
954 , '' /* demand_class varchar2(30) */
955 , demand_class_code /* demand_class_code varchar2(30) */
956 , shipment_priority_code /* shipment_priority_code varchar2(30) */
957 , '' /* shipment_priority varchar2(30) */
958 , shipping_method_code /* shipping_method_code varchar2(30) */
959 , '' /* shipping_method varchar2(30) */
960 , '' /* freight_carrier_code varchar2(30) */
961 , freight_terms_code /* freight_terms_code varchar2(30) */
962 , '' /* freight_terms varchar2(30) */
963 , fob_point_code /* fob_point_code varchar2(30) */
964 , '' /* fob_point varchar2(30) */
965 , reference_type /* reference_type varchar2(30) */
966 , reference_header_id /* reference_header_id number */
967 , '' /* reference_header varchar2(30) */
968 , reference_line_id /* reference_line_id number */
969 , '' /* reference_line varchar2(30) */
970 , '' /* customer_po_number varchar2(50) */
971 , '' /* customer_line_number varchar2(50) */
972 , '' /* customer_shipment_number varchar2(50) */
973 --, related_po_number /* related_po_number varchar2(50) */
974 , 'INT' --item_identifier_type /* customer_item_id_type varchar2(30) */
975 , ordered_item_id /* customer_item_id number */
976 , ordered_item /* customer_item varchar2(2000) */
977 -- , customer_item_revision /* customer_item_revision varchar2(50) */
978 , '' /* customer_item_net_price number */
979 , '' /* customer_payment_term_id number */
980 , '' /* customer_payment_term varchar2(30) */
981 , demand_bucket_type_code /* demand_bucket_type_code varchar2(30) */
982 , '' /* schedule_item_detail varchar2(30) */
983 , '' /* demand_stream varchar2(30) */
984 , customer_dock_code /* customer_dock_code varchar2(30) */
985 , '' /* customer_dock varchar2(50) */
986 , customer_job /* customer_job varchar2(50) */
987 , customer_production_line /* customer_production_line varchar2(50) */
988 , cust_model_serial_number /* cust_model_serial_number varchar2(50) */
989 --, planning_prod_seq_num /* planning_prod_seq_num number */
990 , project_id /* project_id number */
991 , '' /* project varchar2(30) */
992 , task_id /* task_id number */
996 --, payment_trx_id /* payment_trx_id number */
993 , '' /* task varchar2(30) */
994 , end_item_unit_number /* end_item_unit_number varchar2(30) */
995 , item_revision /* item_revision varchar2(3) */
997 --, '' /* payment_trx varchar2(30) */
998 , '' /* line_po_context varchar2(30) */
999 , '' /* contract_po_number varchar2(150) */
1000 , attribute1 /* attribute1 varchar2(240) */
1001 , attribute2 /* attribute2 varchar2(240) */
1002 , attribute3 /* attribute3 varchar2(240) */
1003 , attribute4 /* attribute4 varchar2(240) */
1004 , attribute5 /* attribute5 varchar2(240) */
1005 , attribute6 /* attribute6 varchar2(240) */
1006 , attribute7 /* attribute7 varchar2(240) */
1007 , attribute8 /* attribute8 varchar2(240) */
1008 , attribute9 /* attribute9 varchar2(240) */
1009 , attribute10 /* attribute10 varchar2(240) */
1010 , attribute11 /* attribute11 varchar2(240) */
1011 , attribute12 /* attribute12 varchar2(240) */
1012 , attribute13 /* attribute13 varchar2(240) */
1013 , attribute14 /* attribute14 varchar2(240) */
1014 , attribute15 /* attribute15 varchar2(240) */
1015 , industry_context /* industry_context varchar2(30) */
1016 , industry_attribute1 /* industry_attribute1 varchar2(240) */
1017 , industry_attribute2 /* industry_attribute2 varchar2(240) */
1018 , industry_attribute3 /* industry_attribute3 varchar2(240) */
1019 , industry_attribute4 /* industry_attribute4 varchar2(240) */
1020 , industry_attribute5 /* industry_attribute5 varchar2(240) */
1021 , industry_attribute6 /* industry_attribute6 varchar2(240) */
1022 , industry_attribute7 /* industry_attribute7 varchar2(240) */
1023 , industry_attribute8 /* industry_attribute8 varchar2(240) */
1024 , industry_attribute9 /* industry_attribute9 varchar2(240) */
1025 , industry_attribute10 /* industry_attribute10 varchar2(240) */
1026 , industry_attribute11 /* industry_attribute11 varchar2(240) */
1027 , industry_attribute12 /* industry_attribute12 varchar2(240) */
1028 , industry_attribute13 /* industry_attribute13 varchar2(240) */
1029 , industry_attribute14 /* industry_attribute14 varchar2(240) */
1030 , industry_attribute15 /* industry_attribute15 varchar2(240) */
1031 , industry_attribute16 /* industry_attribute16 varchar2(240) */
1032 , industry_attribute17 /* industry_attribute17 varchar2(240) */
1033 , industry_attribute18 /* industry_attribute18 varchar2(240) */
1034 , industry_attribute19 /* industry_attribute19 varchar2(240) */
1035 , industry_attribute20 /* industry_attribute20 varchar2(240) */
1036 , industry_attribute21 /* industry_attribute21 varchar2(240) */
1037 , industry_attribute22 /* industry_attribute22 varchar2(240) */
1038 , industry_attribute23 /* industry_attribute23 varchar2(240) */
1039 , industry_attribute24 /* industry_attribute24 varchar2(240) */
1040 , industry_attribute25 /* industry_attribute25 varchar2(240) */
1041 , industry_attribute26 /* industry_attribute26 varchar2(240) */
1042 , industry_attribute27 /* industry_attribute27 varchar2(240) */
1043 , industry_attribute28 /* industry_attribute28 varchar2(240) */
1044 , industry_attribute29 /* industry_attribute29 varchar2(240) */
1045 , industry_attribute30 /* industry_attribute30 varchar2(240) */
1046 , pricing_context /* pricing_context varchar2(150) */
1047 , pricing_attribute1 /* pricing_attribute1 varchar2(240) */
1048 , pricing_attribute2 /* pricing_attribute2 varchar2(240) */
1049 , pricing_attribute3 /* pricing_attribute3 varchar2(240) */
1050 , pricing_attribute4 /* pricing_attribute4 varchar2(240) */
1051 , pricing_attribute5 /* pricing_attribute5 varchar2(240) */
1052 , pricing_attribute6 /* pricing_attribute6 varchar2(240) */
1053 , pricing_attribute7 /* pricing_attribute7 varchar2(240) */
1054 , pricing_attribute8 /* pricing_attribute8 varchar2(240) */
1055 , pricing_attribute9 /* pricing_attribute9 varchar2(240) */
1056 , pricing_attribute10 /* pricing_attribute10 varchar2(240) */
1057 , global_attribute_category /* global_attribute_category varchar2(30) */
1058 , global_attribute1 /* global_attribute1 varchar2(240) */
1059 , global_attribute2 /* global_attribute2 varchar2(240) */
1060 , global_attribute3 /* global_attribute3 varchar2(240) */
1061 , global_attribute4 /* global_attribute4 varchar2(240) */
1062 , global_attribute5 /* global_attribute5 varchar2(240) */
1063 , global_attribute6 /* global_attribute6 varchar2(240) */
1064 , global_attribute7 /* global_attribute7 varchar2(240) */
1065 , global_attribute8 /* global_attribute8 varchar2(240) */
1066 , global_attribute9 /* global_attribute9 varchar2(240) */
1067 , global_attribute10 /* global_attribute10 varchar2(240) */
1068 , global_attribute11 /* global_attribute11 varchar2(240) */
1069 , global_attribute12 /* global_attribute12 varchar2(240) */
1070 , global_attribute13 /* global_attribute13 varchar2(240) */
1071 , global_attribute14 /* global_attribute14 varchar2(240) */
1072 , global_attribute15 /* global_attribute15 varchar2(240) */
1073 , global_attribute16 /* global_attribute16 varchar2(240) */
1074 , global_attribute17 /* global_attribute17 varchar2(240) */
1078 , return_attribute1 /* return_attribute1 varchar2(240) */
1075 , global_attribute18 /* global_attribute18 varchar2(240) */
1076 , global_attribute19 /* global_attribute19 varchar2(240) */
1077 , global_attribute20 /* global_attribute20 varchar2(240) */
1079 , return_attribute2 /* return_attribute2 varchar2(240) */
1080 , return_attribute3 /* return_attribute3 varchar2(240) */
1081 , return_attribute4 /* return_attribute4 varchar2(240) */
1082 , return_attribute5 /* return_attribute5 varchar2(240) */
1083 , return_attribute6 /* return_attribute6 varchar2(240) */
1084 , return_attribute7 /* return_attribute7 varchar2(240) */
1085 , return_attribute8 /* return_attribute8 varchar2(240) */
1086 , return_attribute9 /* return_attribute9 varchar2(240) */
1087 , return_attribute10 /* return_attribute10 varchar2(240) */
1088 , return_attribute11 /* return_attribute11 varchar2(240) */
1089 , return_attribute12 /* return_attribute12 varchar2(240) */
1090 , return_attribute13 /* return_attribute13 varchar2(240) */
1091 , return_attribute14 /* return_attribute14 varchar2(240) */
1092 , return_attribute15 /* return_attribute15 varchar2(240) */
1093 , '' /* inventory_item_segment_1 varchar2(240) */
1094 , '' /* inventory_item_segment_2 varchar2(240) */
1095 , '' /* inventory_item_segment_3 varchar2(240) */
1096 , '' /* inventory_item_segment_4 varchar2(240) */
1097 , '' /* inventory_item_segment_5 varchar2(240) */
1098 , '' /* inventory_item_segment_6 varchar2(240) */
1099 , '' /* inventory_item_segment_7 varchar2(240) */
1100 , '' /* inventory_item_segment_8 varchar2(240) */
1101 , '' /* inventory_item_segment_9 varchar2(240) */
1102 , '' /* inventory_item_segment_10varchar2(240) */
1103 , '' /* inventory_item_segment_11varchar2(240) */
1104 , '' /* inventory_item_segment_12varchar2(240) */
1105 , '' /* inventory_item_segment_13varchar2(240) */
1106 , '' /* inventory_item_segment_14varchar2(240) */
1107 , '' /* inventory_item_segment_15varchar2(240) */
1108 , '' /* inventory_item_segment_16varchar2(240) */
1109 , '' /* inventory_item_segment_17varchar2(240) */
1110 , '' /* inventory_item_segment_18varchar2(240) */
1111 , '' /* inventory_item_segment_19varchar2(240) */
1112 , '' /* inventory_item_segment_20varchar2(240) */
1113 , 'INSERT' /* operation_code varchar2(30) */
1114 , '' /* error_flag varchar2(1) */
1115 , '' /* status_flag varchar2(1) */
1116 , '' /* change_request_code varchar2(30) */
1117 , '' /* request_id number */
1118 , -1 /* created_by number */
1119 , sysdate /* creation_date date */
1120 , -1 /* last_updated_by number */
1121 , sysdate /* last_update_date date */
1122 , 0 /* last_update_login number */
1123 , program_application_id /* program_application_idnumber */
1124 , program_id /* program_id number */
1125 , program_update_date /* program_update_date date */
1126 , split_from_line_id /* split_from_line_id number */
1127 , cust_production_seq_num /* cust_production_seq_num varchar2(50) */
1128 , authorized_to_ship_flag /* authorized_to_ship_flag varchar2(1) */
1129 , veh_cus_item_cum_key_id /* veh_cus_item_cum_key_id number */
1130 , salesrep_id /* salesrep_id number */
1131 , return_reason_code /* return_reason_code varchar2(30) */
1132 , over_ship_reason_code /* over_ship_reason_codevarchar2(30) */
1133 , over_ship_resolved_flag /* over_ship_resolved_flag varchar2(1) */
1134 , '' /* credit_invoice_line_id number */
1135 , '' /* change_reason varchar2(30) */
1136 , '' /* change_comments varchar2(2000) */
1137 , user_item_description
1138 ,end_customer_id /* end_customer_id number */
1139 ,end_customer_contact_id /* end_customer_contact_id number */
1140 ,end_customer_site_use_id /* end_customer_site_use_id number */
1141 ,ib_owner /* ib_owner varchar2(60) */
1142 ,ib_current_location /* ib_current_location varchar2(60) */
1143 ,ib_installed_at_location /* ib_installed_at_location varchar2(60) */
1144 -- INVCONV
1145 , shipping_quantity2 /* number */
1146 , shipping_quantity_uom2 /* varchar2(3) */
1147 , shipped_quantity2 /* number */
1148 , cancelled_quantity2 /* number */
1149 , fulfilled_quantity2 /* number */
1150
1151 from oe_order_lines_all
1152 where header_id = old_header_id_v;
1153
1154 IF SQL%ROWCOUNT > 0 THEN
1155 IF l_debug_level > 0 THEN
1156 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_LINES_IFACE_ALL' ) ;
1157 END IF;
1158 ELSE
1159 IF l_debug_level > 0 THEN
1160 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_LINES_IFACE_ALL' ) ;
1161 END IF;
1162 END IF;
1163
1164 IF l_debug_level > 0 THEN
1165 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_PRICE_ADJS_IFACE_ALL' ) ;
1166 END IF;
1170 PROGRAM_ID /*NUMBER(22)*/
1167
1168 /* Header Level Price Adjustment */
1169 INSERT INTO OE_PRICE_ADJS_IFACE_ALL (
1171 , PROGRAM_UPDATE_DATE /*DATE(7)*/
1172 , REQUEST_ID /*NUMBER(22)*/
1173 , OPERATION_CODE /*VARCHAR2(30)*/
1174 , ERROR_FLAG /*VARCHAR2(1)*/
1175 , STATUS_FLAG /*VARCHAR2(1)*/
1176 , INTERFACE_STATUS /*VARCHAR2(1000)*/
1177 , LIST_HEADER_ID /*NUMBER(22)*/
1178 , LIST_NAME /*VARCHAR2(240)*/
1179 , LIST_LINE_ID /*NUMBER(22)*/
1180 , LIST_LINE_TYPE_CODE /*VARCHAR2(30)*/
1181 , CHARGE_TYPE_CODE /*VARCHAR2(30)*/
1182 , LIST_LINE_NUMBER /*VARCHAR2(30)*/
1183 , MODIFIER_MECHANISM_TYPE_CODE /*VARCHAR2(30)*/
1184 , MODIFIED_FROM /*NUMBER(22)*/
1185 , MODIFIED_TO /*NUMBER(22)*/
1186 , UPDATED_FLAG /*VARCHAR2(1)*/
1187 , UPDATE_ALLOWED /*VARCHAR2(1)*/
1188 , APPLIED_FLAG /*VARCHAR2(1)*/
1189 , CHANGE_REASON_CODE /*VARCHAR2(30)*/
1190 , CHANGE_REASON_TEXT /*VARCHAR2(2000)*/
1191 , OPERAND /*NUMBER(22)*/
1192 , ARITHMETIC_OPERATOR /*VARCHAR2(30)*/
1193 , ADJUSTED_AMOUNT /*NUMBER(22)*/
1194 , PRICING_PHASE_ID /*NUMBER(22)*/
1195 , ORDER_SOURCE_ID /*NUMBER(22)*/
1196 , ORIG_SYS_DOCUMENT_REF /*VARCHAR2(50)*/
1197 , ORIG_SYS_LINE_REF /*VARCHAR2(50)*/
1198 , ORIG_SYS_SHIPMENT_REF /*VARCHAR2(50)*/
1199 , ORIG_SYS_DISCOUNT_REF /*VARCHAR2(50)*/
1200 , CHANGE_SEQUENCE /*VARCHAR2(50)*/
1201 , CHANGE_REQUEST_CODE /*VARCHAR2(30)*/
1202 , ORG_ID /*NUMBER(22)*/
1203 , DISCOUNT_ID /*NUMBER(22)*/
1204 , DISCOUNT_LINE_ID /*NUMBER(22)*/
1205 , DISCOUNT_NAME /*VARCHAR2(240)*/
1206 , PERCENT /*NUMBER(22)*/
1207 , AUTOMATIC_FLAG /*VARCHAR2(1)*/
1208 , CONTEXT /*VARCHAR2(30)*/
1209 , ATTRIBUTE1 /*VARCHAR2(240)*/
1210 , ATTRIBUTE2 /*VARCHAR2(240)*/
1211 , ATTRIBUTE3 /*VARCHAR2(240)*/
1212 , ATTRIBUTE4 /*VARCHAR2(240)*/
1213 , ATTRIBUTE5 /*VARCHAR2(240)*/
1214 , ATTRIBUTE6 /*VARCHAR2(240)*/
1215 , ATTRIBUTE7 /*VARCHAR2(240)*/
1216 , ATTRIBUTE8 /*VARCHAR2(240)*/
1217 , ATTRIBUTE9 /*VARCHAR2(240)*/
1218 , ATTRIBUTE10 /*VARCHAR2(240)*/
1219 , ATTRIBUTE11 /*VARCHAR2(240)*/
1220 , ATTRIBUTE12 /*VARCHAR2(240)*/
1221 , ATTRIBUTE13 /*VARCHAR2(240)*/
1222 , ATTRIBUTE14 /*VARCHAR2(240)*/
1223 , ATTRIBUTE15 /*VARCHAR2(240)*/
1224 , CREATION_DATE /*DATE(7)*/
1225 , CREATED_BY /*NUMBER(22)*/
1226 , LAST_UPDATE_DATE /*DATE(7)*/
1227 , LAST_UPDATED_BY /*NUMBER(22)*/
1228 , LAST_UPDATE_LOGIN /*NUMBER(22)*/
1229 , PROGRAM_APPLICATION_ID /*NUMBER(22)*/
1230 , SOLD_TO_ORG_ID /*NUMBER(22)*/
1231 )
1232 SELECT
1233 '' /*NULL*/
1234 , '' /*NULL*/
1235 , '' /*NULL*/
1236 , 'INSERT' /*NULL Operation*/
1237 , '' /*NULL*/
1238 , '' /*NULL*/
1239 , '' /*NULL*/
1240 , LIST_HEADER_ID /*LIST_HEADER_ID*/
1241 , '' /*LIST_NAME NULL*/
1242 , LIST_LINE_ID /*NULL*/
1243 , LIST_LINE_TYPE_CODE /*NULL*/
1244 , CHARGE_TYPE_CODE /*NULL*/
1245 , LIST_LINE_NO /*VARCHAR2(30)*/
1246 , MODIFIER_MECHANISM_TYPE_CODE /*NULL*/
1247 , MODIFIED_FROM /*NULL*/
1248 , MODIFIED_TO /*NULL*/
1249 , UPDATED_FLAG /*NULL*/
1250 , UPDATE_ALLOWED /*NULL*/
1251 , APPLIED_FLAG /*NULL*/
1252 , CHANGE_REASON_CODE /*NULL*/
1253 , CHANGE_REASON_TEXT /*NULL*/
1257 , PRICING_PHASE_ID /*NULL*/
1254 , OPERAND /*NULL*/
1255 , ARITHMETIC_OPERATOR /*NULL*/
1256 , ADJUSTED_AMOUNT /*NULL*/
1258 , order_source_id_v /*NULL order_source_id*/
1259 , to_char(order_number_v) /*NULL orig_sys_document_ref*/
1260 , '' /*NULL orig_sys_line_ref*/
1261 , '' /*NULL orig_sys_shipment_ref*/
1262 , '1' /*NULL orig_sys_discount_ref*/
1263 , CHANGE_SEQUENCE /*NULL*/
1264 , CHANGE_REASON_CODE /*NULL change_request_code*/
1265 , '' /*NULL*/
1266 , '' /*NULL DISCOUNT_ID*/
1267 , '' /*NULL DISCOUNT_LINE_ID*/
1268 , '' /*NULL*/
1269 , PERCENT /*NULL*/
1270 , AUTOMATIC_FLAG /*NOT NULL*/
1271 , CONTEXT /*NULL*/
1272 , ATTRIBUTE1 /*NULL*/
1273 , ATTRIBUTE2 /*NULL*/
1274 , ATTRIBUTE3 /*NULL*/
1275 , ATTRIBUTE4 /*NULL*/
1276 , ATTRIBUTE5 /*NULL*/
1277 , ATTRIBUTE6 /*NULL*/
1278 , ATTRIBUTE7 /*NULL*/
1279 , ATTRIBUTE8 /*NULL*/
1280 , ATTRIBUTE9 /*NULL*/
1281 , ATTRIBUTE10 /*NULL*/
1282 , ATTRIBUTE11 /*NULL*/
1283 , ATTRIBUTE12 /*NULL*/
1284 , ATTRIBUTE13 /*NULL*/
1285 , ATTRIBUTE14 /*NULL*/
1286 , ATTRIBUTE15 /*NULL*/
1287 , sysdate /*NOT NULL*/
1288 , -1 /*NOT NULL*/
1289 , sysdate /*NOT NULL*/
1290 , -1 /*NOT NULL*/
1291 , 0 /*NULL*/
1292 , '' /*NULL*/
1293 , l_sold_to_org_id /*NULL*/
1294 from oe_price_adjustments
1295 where header_id = old_header_id_v
1296 and line_id is null
1297 and applied_Flag = 'Y';
1298
1299 IF SQL%ROWCOUNT > 0 THEN
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_PRICE_ADJS_IFACE_ALL FOR HEADER' ) ;
1302 END IF;
1303 ELSE
1304 IF l_debug_level > 0 THEN
1305 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_PRICE_ADJS_IFACE_ALL FOR HEADER' ) ;
1306 END IF;
1307 END IF;
1308
1309 IF l_debug_level > 0 THEN
1310 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_PRICE_ADJS_IFACE_ALL FOR LINE' ) ;
1311 END IF;
1312
1313
1314 /* Line Level Payments */
1315
1316 Insert into oe_payments_interface
1317 (
1318 ORDER_SOURCE_ID,
1319 change_sequence,
1320 PAYMENT_TRX_ID,
1321 CREATION_DATE,
1322 CREATED_BY,
1323 LAST_UPDATE_DATE,
1324 LAST_UPDATED_BY,
1325 LAST_UPDATE_LOGIN,
1326 OPERATION_CODE,
1327 REQUEST_ID,
1328 PROGRAM_APPLICATION_ID,
1329 PROGRAM_ID,
1330 PROGRAM_UPDATE_DATE,
1331 CONTEXT,
1332 ATTRIBUTE1,
1333 ATTRIBUTE2,
1334 ATTRIBUTE3,
1335 ATTRIBUTE4,
1336 ATTRIBUTE5,
1337 ATTRIBUTE6,
1338 ATTRIBUTE7,
1339 ATTRIBUTE8,
1340 ATTRIBUTE9,
1341 ATTRIBUTE10,
1342 ATTRIBUTE11,
1343 ATTRIBUTE12,
1344 ATTRIBUTE13,
1345 ATTRIBUTE14,
1346 ATTRIBUTE15,
1347 PAYMENT_TYPE_CODE,
1348 CREDIT_CARD_CODE,
1349 CREDIT_CARD_NUMBER,
1350 CREDIT_CARD_HOLDER_NAME,
1351 CREDIT_CARD_EXPIRATION_DATE,
1352 PREPAID_AMOUNT,
1353 PAYMENT_SET_ID,
1354 RECEIPT_METHOD_ID,
1355 PAYMENT_COLLECTION_EVENT,
1356 CREDIT_CARD_APPROVAL_CODE,
1357 CREDIT_CARD_APPROVAL_DATE,
1358 CHECK_NUMBER,
1359 PAYMENT_AMOUNT,
1360 PAYMENT_NUMBER,
1361 ORIG_SYS_PAYMENT_REF,
1362 ORIG_SYS_DOCUMENT_REF,
1363 ORIG_SYS_LINE_REF,
1364 ORIG_SYS_SHIPMENT_REF,
1365 ORG_ID
1366 )Select
1367 order_source_id_v,
1368 '',
1369 PAYMENT_TRX_ID
1370 ,sysdate
1371 ,-1
1372 ,sysdate
1373 ,-1
1374 ,0
1375 ,'INSERT'
1376 ,''
1377 ,''
1378 ,''
1379 ,''
1380 ,CONTEXT
1381 ,ATTRIBUTE1
1382 ,ATTRIBUTE2
1383 ,ATTRIBUTE3
1384 ,ATTRIBUTE4
1385 ,ATTRIBUTE5
1386 ,ATTRIBUTE6
1387 ,ATTRIBUTE7
1388 ,ATTRIBUTE8
1389 ,ATTRIBUTE9
1390 ,ATTRIBUTE10
1391 ,ATTRIBUTE11
1392 ,ATTRIBUTE12
1393 ,ATTRIBUTE13
1394 ,ATTRIBUTE14
1395 ,ATTRIBUTE15
1396 ,PAYMENT_TYPE_CODE
1397 ,CREDIT_CARD_CODE
1398 ,CREDIT_CARD_NUMBER
1399 ,CREDIT_CARD_HOLDER_NAME
1400 ,CREDIT_CARD_EXPIRATION_DATE
1401 ,PREPAID_AMOUNT
1402 ,PAYMENT_SET_ID
1403 ,RECEIPT_METHOD_ID
1404 ,PAYMENT_COLLECTION_EVENT
1405 ,CREDIT_CARD_APPROVAL_CODE
1409 ,PAYMENT_NUMBER
1406 ,CREDIT_CARD_APPROVAL_DATE
1407 ,CHECK_NUMBER
1408 ,PAYMENT_AMOUNT
1410 ,rtrim(to_char(line_id)||'-'||to_char(payment_number))
1411 ,to_char(order_number_v)
1412 ,get_line_ref_from_line_id(line_id)
1413 ,''
1414 ,''
1415 FROM OE_PAYMENTS
1416 where header_id = old_header_id_v
1417 and line_id is not null;
1418
1419 IF SQL%ROWCOUNT > 0 THEN
1420 IF l_debug_level > 0 THEN
1421 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_PAYMENTS_INTERFACE FOR LINE' ) ;
1422 END IF;
1423 ELSE
1424 IF l_debug_level > 0 THEN
1425 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_PAYMENTS_INTERFACE FOR LINE' ) ;
1426 END IF;
1427 END IF;
1428
1429 /* Header Payments */
1430
1431 Insert into oe_payments_interface
1432 (
1433 ORDER_SOURCE_ID,
1434 change_sequence,
1435 PAYMENT_TRX_ID,
1436 CREATION_DATE,
1437 CREATED_BY,
1438 LAST_UPDATE_DATE,
1439 LAST_UPDATED_BY,
1440 LAST_UPDATE_LOGIN,
1441 OPERATION_CODE,
1442 REQUEST_ID,
1443 PROGRAM_APPLICATION_ID,
1444 PROGRAM_ID,
1445 PROGRAM_UPDATE_DATE,
1446 CONTEXT,
1447 ATTRIBUTE1,
1448 ATTRIBUTE2,
1449 ATTRIBUTE3,
1450 ATTRIBUTE4,
1451 ATTRIBUTE5,
1452 ATTRIBUTE6,
1453 ATTRIBUTE7,
1454 ATTRIBUTE8,
1455 ATTRIBUTE9,
1456 ATTRIBUTE10,
1457 ATTRIBUTE11,
1458 ATTRIBUTE12,
1459 ATTRIBUTE13,
1460 ATTRIBUTE14,
1461 ATTRIBUTE15,
1462 PAYMENT_TYPE_CODE,
1463 CREDIT_CARD_CODE,
1464 CREDIT_CARD_NUMBER,
1465 CREDIT_CARD_HOLDER_NAME,
1466 CREDIT_CARD_EXPIRATION_DATE,
1467 PREPAID_AMOUNT,
1468 PAYMENT_SET_ID,
1469 RECEIPT_METHOD_ID,
1470 PAYMENT_COLLECTION_EVENT,
1471 CREDIT_CARD_APPROVAL_CODE,
1472 CREDIT_CARD_APPROVAL_DATE,
1473 CHECK_NUMBER,
1474 PAYMENT_AMOUNT,
1475 PAYMENT_NUMBER,
1476 ORIG_SYS_PAYMENT_REF,
1477 ORIG_SYS_DOCUMENT_REF,
1478 ORIG_SYS_LINE_REF,
1479 ORIG_SYS_SHIPMENT_REF,
1480 ORG_ID
1481 )Select
1482 order_source_id_v,
1483 '',
1484 PAYMENT_TRX_ID
1485 ,sysdate
1486 ,-1
1487 ,sysdate
1488 ,-1
1489 ,0
1490 ,'INSERT'
1491 ,''
1492 ,''
1493 ,''
1494 ,''
1495 ,CONTEXT
1496 ,ATTRIBUTE1
1497 ,ATTRIBUTE2
1498 ,ATTRIBUTE3
1499 ,ATTRIBUTE4
1500 ,ATTRIBUTE5
1501 ,ATTRIBUTE6
1502 ,ATTRIBUTE7
1503 ,ATTRIBUTE8
1504 ,ATTRIBUTE9
1505 ,ATTRIBUTE10
1506 ,ATTRIBUTE11
1507 ,ATTRIBUTE12
1508 ,ATTRIBUTE13
1509 ,ATTRIBUTE14
1510 ,ATTRIBUTE15
1511 ,PAYMENT_TYPE_CODE
1512 ,CREDIT_CARD_CODE
1513 ,CREDIT_CARD_NUMBER
1514 ,CREDIT_CARD_HOLDER_NAME
1515 ,CREDIT_CARD_EXPIRATION_DATE
1516 ,PREPAID_AMOUNT
1517 ,PAYMENT_SET_ID
1518 ,RECEIPT_METHOD_ID
1519 ,PAYMENT_COLLECTION_EVENT
1520 ,CREDIT_CARD_APPROVAL_CODE
1521 ,CREDIT_CARD_APPROVAL_DATE
1522 ,CHECK_NUMBER
1523 ,PAYMENT_AMOUNT
1524 ,PAYMENT_NUMBER
1525 ,rtrim('-'||to_char(PAYMENT_NUMBER))
1526 ,to_char(order_number_v)
1527 ,get_line_ref_from_line_id(line_id)
1528 ,''
1529 ,''
1530 FROM OE_PAYMENTS
1531 where header_id = old_header_id_v
1532 and line_id is null;
1533
1534 IF SQL%ROWCOUNT > 0 THEN
1535 IF l_debug_level > 0 THEN
1536 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_PAYMENTS_INTERFACE FOR HEADERS' ) ;
1537 END IF;
1538 ELSE
1539 IF l_debug_level > 0 THEN
1540 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_PAYMENTS_INTERFACE FOR HEADERS' ) ;
1541 END IF;
1542 END IF;
1543
1544 /* Line Level Price Adjustment */
1545 INSERT INTO OE_PRICE_ADJS_IFACE_ALL (
1546 PROGRAM_ID /*NUMBER(22)*/
1547 , PROGRAM_UPDATE_DATE /*DATE(7)*/
1548 , REQUEST_ID /*NUMBER(22)*/
1549 , OPERATION_CODE /*VARCHAR2(30)*/
1550 , ERROR_FLAG /*VARCHAR2(1)*/
1551 , STATUS_FLAG /*VARCHAR2(1)*/
1552 , INTERFACE_STATUS /*VARCHAR2(1000)*/
1553 , LIST_HEADER_ID /*NUMBER(22)*/
1554 , LIST_NAME /*VARCHAR2(240)*/
1555 , LIST_LINE_ID /*NUMBER(22)*/
1556 , LIST_LINE_TYPE_CODE /*VARCHAR2(30)*/
1557 , CHARGE_TYPE_CODE /*VARCHAR2(30)*/
1558 , LIST_LINE_NUMBER /*VARCHAR2(30)*/
1559 , MODIFIER_MECHANISM_TYPE_CODE /*VARCHAR2(30)*/
1560 , MODIFIED_FROM /*NUMBER(22)*/
1561 , MODIFIED_TO /*NUMBER(22)*/
1562 , UPDATED_FLAG /*VARCHAR2(1)*/
1563 , UPDATE_ALLOWED /*VARCHAR2(1)*/
1564 , APPLIED_FLAG /*VARCHAR2(1)*/
1565 , CHANGE_REASON_CODE /*VARCHAR2(30)*/
1566 , CHANGE_REASON_TEXT /*VARCHAR2(2000)*/
1567 , OPERAND /*NUMBER(22)*/
1568 , ARITHMETIC_OPERATOR /*VARCHAR2(30)*/
1569 , ADJUSTED_AMOUNT /*NUMBER(22)*/
1570 , PRICING_PHASE_ID /*NUMBER(22)*/
1574 , ORIG_SYS_SHIPMENT_REF /*VARCHAR2(50)*/
1571 , ORDER_SOURCE_ID /*NUMBER(22)*/
1572 , ORIG_SYS_DOCUMENT_REF /*VARCHAR2(50)*/
1573 , ORIG_SYS_LINE_REF /*VARCHAR2(50)*/
1575 , ORIG_SYS_DISCOUNT_REF /*VARCHAR2(50)*/
1576 , CHANGE_SEQUENCE /*VARCHAR2(50)*/
1577 , CHANGE_REQUEST_CODE /*VARCHAR2(30)*/
1578 , ORG_ID /*NUMBER(22)*/
1579 , DISCOUNT_ID /*NUMBER(22)*/
1580 , DISCOUNT_LINE_ID /*NUMBER(22)*/
1581 , DISCOUNT_NAME /*VARCHAR2(240)*/
1582 , PERCENT /*NUMBER(22)*/
1583 , AUTOMATIC_FLAG /*VARCHAR2(1)*/
1584 , CONTEXT /*VARCHAR2(30)*/
1585 , ATTRIBUTE1 /*VARCHAR2(240)*/
1586 , ATTRIBUTE2 /*VARCHAR2(240)*/
1587 , ATTRIBUTE3 /*VARCHAR2(240)*/
1588 , ATTRIBUTE4 /*VARCHAR2(240)*/
1589 , ATTRIBUTE5 /*VARCHAR2(240)*/
1590 , ATTRIBUTE6 /*VARCHAR2(240)*/
1591 , ATTRIBUTE7 /*VARCHAR2(240)*/
1592 , ATTRIBUTE8 /*VARCHAR2(240)*/
1593 , ATTRIBUTE9 /*VARCHAR2(240)*/
1594 , ATTRIBUTE10 /*VARCHAR2(240)*/
1595 , ATTRIBUTE11 /*VARCHAR2(240)*/
1596 , ATTRIBUTE12 /*VARCHAR2(240)*/
1597 , ATTRIBUTE13 /*VARCHAR2(240)*/
1598 , ATTRIBUTE14 /*VARCHAR2(240)*/
1599 , ATTRIBUTE15 /*VARCHAR2(240)*/
1600 , CREATION_DATE /*DATE(7)*/
1601 , CREATED_BY /*NUMBER(22)*/
1602 , LAST_UPDATE_DATE /*DATE(7)*/
1603 , LAST_UPDATED_BY /*NUMBER(22)*/
1604 , LAST_UPDATE_LOGIN /*NUMBER(22)*/
1605 , PROGRAM_APPLICATION_ID /*NUMBER(22)*/
1606 , SOLD_TO_ORG_ID /*NUMBER(22)*/
1607 )
1608 SELECT
1609 '' /*NULL*/
1610 , '' /*NULL*/
1611 , '' /*NULL*/
1612 , 'INSERT' /*NULL Operation*/
1613 , '' /*NULL*/
1614 , '' /*NULL*/
1615 , '' /*NULL*/
1616 , LIST_HEADER_ID /*NULL*/
1617 , '' /*LIST_NAME NULL*/
1618 , LIST_LINE_ID /*NULL*/
1619 , LIST_LINE_TYPE_CODE /*NULL*/
1620 , CHARGE_TYPE_CODE /*VARCHAR2(30)*/
1621 , LIST_LINE_NO /*VARCHAR2(30)*/
1622 , MODIFIER_MECHANISM_TYPE_CODE /*NULL*/
1623 , MODIFIED_FROM /*NULL*/
1624 , MODIFIED_TO /*NULL*/
1625 , UPDATED_FLAG /*NULL*/
1626 , UPDATE_ALLOWED /*NULL*/
1627 , APPLIED_FLAG /*NULL*/
1628 , CHANGE_REASON_CODE /*NULL*/
1629 , CHANGE_REASON_TEXT /*NULL*/
1630 , OPERAND /*NULL*/
1631 , ARITHMETIC_OPERATOR /*NULL*/
1632 , ADJUSTED_AMOUNT /*NULL*/
1633 , PRICING_PHASE_ID /*NULL*/
1634 , order_source_id_v /*NULL order_source_id*/
1635 , to_char(order_number_v) /*NULL orig_sys_document_ref*/
1636 , get_line_ref_from_line_id(line_id) /*NULL orig_sys_line_ref*/
1637 , '' /*NULL orig_sys_shipment_ref*/
1638 , '1' /*NULL orig_sys_discount_ref*/
1639 , CHANGE_SEQUENCE /*NULL*/
1640 , CHANGE_REASON_CODE /*NULL change_request_code*/
1641 , '' /*NULL*/
1642 , DISCOUNT_ID /*NULL*/
1643 , DISCOUNT_LINE_ID /*NULL*/
1644 , '' /*NULL*/
1645 , PERCENT /*NULL*/
1646 , AUTOMATIC_FLAG /*NOT NULL*/
1647 , CONTEXT /*NULL*/
1648 , ATTRIBUTE1 /*NULL*/
1649 , ATTRIBUTE2 /*NULL*/
1650 , ATTRIBUTE3 /*NULL*/
1651 , ATTRIBUTE4 /*NULL*/
1652 , ATTRIBUTE5 /*NULL*/
1653 , ATTRIBUTE6 /*NULL*/
1654 , ATTRIBUTE7 /*NULL*/
1655 , ATTRIBUTE8 /*NULL*/
1656 , ATTRIBUTE9 /*NULL*/
1657 , ATTRIBUTE10 /*NULL*/
1658 , ATTRIBUTE11 /*NULL*/
1659 , ATTRIBUTE12 /*NULL*/
1660 , ATTRIBUTE13 /*NULL*/
1661 , ATTRIBUTE14 /*NULL*/
1662 , ATTRIBUTE15 /*NULL*/
1663 , sysdate /*NOT NULL*/
1664 , -1 /*NOT NULL*/
1665 , sysdate /*NOT NULL*/
1669 , l_sold_to_org_id /*NULL*/
1666 , -1 /*NOT NULL*/
1667 , 0 /*NULL*/
1668 , '' /*NULL*/
1670 from oe_price_adjustments
1671 where header_id = old_header_id_v
1672 and line_id is not null
1673 and applied_Flag = 'Y';
1674
1675 IF SQL%ROWCOUNT > 0 THEN
1676 IF l_debug_level > 0 THEN
1677 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_PRICE_ADJS_INTERFACE FOR LINE' ) ;
1678 END IF;
1679 ELSE
1680 IF l_debug_level > 0 THEN
1681 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_PRICE_ADJS_INTERFACE FOR LINE' ) ;
1682 END IF;
1683 END IF;
1684
1685 IF l_debug_level > 0 THEN
1686 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_CREDITS_IFACE_ALL' ) ;
1687 END IF;
1688 /* Header Level Sales Credits */
1689 INSERT INTO OE_CREDITS_IFACE_ALL (
1690 SALESREP /*VARCHAR2(30)*/
1691 , SALES_CREDIT_TYPE_ID /*NUMBER(22)*/
1692 , SALES_CREDIT_TYPE /*VARCHAR2(30)*/
1693 , QUOTA_FLAG /*VARCHAR2(1)*/
1694 , PERCENT /*NUMBER(22)*/
1695 , CONTEXT /*VARCHAR2(30)*/
1696 , ATTRIBUTE1 /*VARCHAR2(240)*/
1697 , ATTRIBUTE2 /*VARCHAR2(240)*/
1698 , ATTRIBUTE3 /*VARCHAR2(240)*/
1699 , ATTRIBUTE4 /*VARCHAR2(240)*/
1700 , ATTRIBUTE5 /*VARCHAR2(240)*/
1701 , ATTRIBUTE6 /*VARCHAR2(240)*/
1702 , ATTRIBUTE7 /*VARCHAR2(240)*/
1703 , ATTRIBUTE8 /*VARCHAR2(240)*/
1704 , ATTRIBUTE9 /*VARCHAR2(240)*/
1705 , ATTRIBUTE10 /*VARCHAR2(240)*/
1706 , ATTRIBUTE11 /*VARCHAR2(240)*/
1707 , ATTRIBUTE12 /*VARCHAR2(240)*/
1708 , ATTRIBUTE13 /*VARCHAR2(240)*/
1709 , ATTRIBUTE14 /*VARCHAR2(240)*/
1710 , ATTRIBUTE15 /*VARCHAR2(240)*/
1711 , CREATED_BY /*NUMBER(22)*/
1712 , CREATION_DATE /*DATE(7)*/
1713 , LAST_UPDATED_BY /*NUMBER(22)*/
1714 , LAST_UPDATE_DATE /*DATE(7)*/
1715 , LAST_UPDATE_LOGIN /*NUMBER(22)*/
1716 , PROGRAM_APPLICATION_ID /*NUMBER(22)*/
1717 , PROGRAM_ID /*NUMBER(22)*/
1718 , PROGRAM_UPDATE_DATE /*DATE(7)*/
1719 , REQUEST_ID /*NUMBER(22)*/
1720 , OPERATION_CODE /*VARCHAR2(30)*/
1721 , ERROR_FLAG /*VARCHAR2(1)*/
1722 , STATUS_FLAG /*VARCHAR2(1)*/
1723 , INTERFACE_STATUS /*VARCHAR2(1000)*/
1724 , ORDER_SOURCE_ID /*NUMBER(22)*/
1725 , ORIG_SYS_DOCUMENT_REF /*VARCHAR2(50)*/
1726 , ORIG_SYS_LINE_REF /*VARCHAR2(50)*/
1727 , ORIG_SYS_SHIPMENT_REF /*VARCHAR2(50)*/
1728 , ORIG_SYS_CREDIT_REF /*VARCHAR2(50)*/
1729 , CHANGE_SEQUENCE /*VARCHAR2(50)*/
1730 , CHANGE_REQUEST_CODE /*VARCHAR2(30)*/
1731 , ORG_ID /*NUMBER(22)*/
1732 , SALESREP_ID /*NUMBER(22)*/
1733 , SOLD_TO_ORG_ID /*NUMBER(22)*/
1734 )
1735 SELECT
1736 '' /*SALESREP NULL*/
1737 , SALES_CREDIT_TYPE_ID /*SALES_CREDIT_TYPE_ID NULL*/
1738 , '' /*SALES_CREDIT_TYPE NULL*/
1739 , '' /*QUOTA_FLAG NULL*/
1740 , PERCENT /*PERCENT NULL*/
1741 , CONTEXT /*CONTEXT NULL*/
1742 , ATTRIBUTE1 /*ATTRIBUTE1 NULL*/
1743 , ATTRIBUTE2 /*ATTRIBUTE2 NULL*/
1744 , ATTRIBUTE3 /*ATTRIBUTE3 NULL*/
1745 , ATTRIBUTE4 /*ATTRIBUTE4 NULL*/
1746 , ATTRIBUTE5 /*ATTRIBUTE5 NULL*/
1747 , ATTRIBUTE6 /*ATTRIBUTE6 NULL*/
1748 , ATTRIBUTE7 /*ATTRIBUTE7 NULL*/
1749 , ATTRIBUTE8 /*ATTRIBUTE8 NULL*/
1750 , ATTRIBUTE9 /*ATTRIBUTE9 NULL*/
1751 , ATTRIBUTE10 /*ATTRIBUTE10 NULL*/
1752 , ATTRIBUTE11 /*ATTRIBUTE11 NULL*/
1753 , ATTRIBUTE12 /*ATTRIBUTE12 NULL*/
1754 , ATTRIBUTE13 /*ATTRIBUTE13 NULL*/
1755 , ATTRIBUTE14 /*ATTRIBUTE14 NULL*/
1756 , ATTRIBUTE15 /*ATTRIBUTE15 NULL*/
1757 , -1 /*CREATED_BY NOT NULL*/
1758 , sysdate /*CREATION_DATE NOT NULL*/
1759 , -1 /*LAST_UPDATED_BY NOT NULL*/
1760 , sysdate /*LAST_UPDATE_DATE NOT NULL*/
1761 , 0 /*LAST_UPDATE_LOGIN NULL*/
1762 , '' /*PROGRAM_APPLICATION_ NULL*/
1763 , '' /*PROGRAM_ID NULL*/
1764 , '' /*PROGRAM_UPDATE_DATE NULL*/
1765 , '' /*REQUEST_ID NULL*/
1766 , 'INSERT' /*OPERATION_CODE NULL*/
1767 , '' /*ERROR_FLAG NULL*/
1768 , '' /*STATUS_FLAG NULL*/
1769 , '' /*INTERFACE_STATUS NULL*/
1770 , ORDER_SOURCE_ID_V /*ORDER_SOURCE_ID NULL*/
1771 , TO_CHAR(ORDER_NUMBER_V) /*ORIG_SYS_DOCUMENT_RE NULL*/
1772 , '' /*ORIG_SYS_LINE_REF NULL*/
1773 , '' /*ORIG_SYS_SHIPMENT_RE NULL*/
1774 , SALES_CREDIT_ID /*ORIG_SYS_CREDIT_REF NULL*/
1775 , '' /*CHANGE_SEQUENCE NULL*/
1779 , l_sold_to_org_id /*SOLD_TO_ORG_ID NULL*/
1776 , '' /*CHANGE_REQUEST_CODE NULL*/
1777 , '' /*ORG_ID NULL*/
1778 , SALESREP_ID /*SALESREP_ID NULL*/
1780 from oe_sales_credits
1781 where header_id = old_header_id_v
1782 and line_id is null;
1783
1784 IF SQL%ROWCOUNT > 0 THEN
1785 IF l_debug_level > 0 THEN
1786 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_CREDITS_IFACE_ALL FOR HEADER' ) ;
1787 END IF;
1788 ELSE
1789 IF l_debug_level > 0 THEN
1790 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_CREDITS_IFACE_ALL FOR HEADER' ) ;
1791 END IF;
1792 END IF;
1793
1794 IF l_debug_level > 0 THEN
1795 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_CREDITS_IFACE_ALL' ) ;
1796 END IF;
1797
1798 /* Line Level Sales Credits */
1799 INSERT INTO OE_CREDITS_IFACE_ALL (
1800 SALESREP /*VARCHAR2(30)*/
1801 , SALES_CREDIT_TYPE_ID /*NUMBER(22)*/
1802 , SALES_CREDIT_TYPE /*VARCHAR2(30)*/
1803 , QUOTA_FLAG /*VARCHAR2(1)*/
1804 , PERCENT /*NUMBER(22)*/
1805 , CONTEXT /*VARCHAR2(30)*/
1806 , ATTRIBUTE1 /*VARCHAR2(240)*/
1807 , ATTRIBUTE2 /*VARCHAR2(240)*/
1808 , ATTRIBUTE3 /*VARCHAR2(240)*/
1809 , ATTRIBUTE4 /*VARCHAR2(240)*/
1810 , ATTRIBUTE5 /*VARCHAR2(240)*/
1811 , ATTRIBUTE6 /*VARCHAR2(240)*/
1812 , ATTRIBUTE7 /*VARCHAR2(240)*/
1813 , ATTRIBUTE8 /*VARCHAR2(240)*/
1814 , ATTRIBUTE9 /*VARCHAR2(240)*/
1815 , ATTRIBUTE10 /*VARCHAR2(240)*/
1816 , ATTRIBUTE11 /*VARCHAR2(240)*/
1817 , ATTRIBUTE12 /*VARCHAR2(240)*/
1818 , ATTRIBUTE13 /*VARCHAR2(240)*/
1819 , ATTRIBUTE14 /*VARCHAR2(240)*/
1820 , ATTRIBUTE15 /*VARCHAR2(240)*/
1821 , CREATED_BY /*NUMBER(22)*/
1822 , CREATION_DATE /*DATE(7)*/
1823 , LAST_UPDATED_BY /*NUMBER(22)*/
1824 , LAST_UPDATE_DATE /*DATE(7)*/
1825 , LAST_UPDATE_LOGIN /*NUMBER(22)*/
1826 , PROGRAM_APPLICATION_ID /*NUMBER(22)*/
1827 , PROGRAM_ID /*NUMBER(22)*/
1828 , PROGRAM_UPDATE_DATE /*DATE(7)*/
1829 , REQUEST_ID /*NUMBER(22)*/
1830 , OPERATION_CODE /*VARCHAR2(30)*/
1831 , ERROR_FLAG /*VARCHAR2(1)*/
1832 , STATUS_FLAG /*VARCHAR2(1)*/
1833 , INTERFACE_STATUS /*VARCHAR2(1000)*/
1834 , ORDER_SOURCE_ID /*NUMBER(22)*/
1835 , ORIG_SYS_DOCUMENT_REF /*VARCHAR2(50)*/
1836 , ORIG_SYS_LINE_REF /*VARCHAR2(50)*/
1837 , ORIG_SYS_SHIPMENT_REF /*VARCHAR2(50)*/
1838 , ORIG_SYS_CREDIT_REF /*VARCHAR2(50)*/
1839 , CHANGE_SEQUENCE /*VARCHAR2(50)*/
1840 , CHANGE_REQUEST_CODE /*VARCHAR2(30)*/
1841 , ORG_ID /*NUMBER(22)*/
1842 , SALESREP_ID /*NUMBER(22)*/
1843 , SOLD_TO_ORG_ID /*NUMBER(22)*/
1844 )
1845 SELECT
1846 '' /*SALESREP NULL*/
1847 , SALES_CREDIT_TYPE_ID /*SALES_CREDIT_TYPE_ID NULL*/
1848 , '' /*SALES_CREDIT_TYPE NULL*/
1849 , '' /*QUOTA_FLAG NULL*/
1850 , PERCENT /*PERCENT NULL*/
1851 , CONTEXT /*CONTEXT NULL*/
1852 , ATTRIBUTE1 /*ATTRIBUTE1 NULL*/
1853 , ATTRIBUTE2 /*ATTRIBUTE2 NULL*/
1854 , ATTRIBUTE3 /*ATTRIBUTE3 NULL*/
1855 , ATTRIBUTE4 /*ATTRIBUTE4 NULL*/
1856 , ATTRIBUTE5 /*ATTRIBUTE5 NULL*/
1857 , ATTRIBUTE6 /*ATTRIBUTE6 NULL*/
1858 , ATTRIBUTE7 /*ATTRIBUTE7 NULL*/
1859 , ATTRIBUTE8 /*ATTRIBUTE8 NULL*/
1860 , ATTRIBUTE9 /*ATTRIBUTE9 NULL*/
1861 , ATTRIBUTE10 /*ATTRIBUTE10 NULL*/
1862 , ATTRIBUTE11 /*ATTRIBUTE11 NULL*/
1863 , ATTRIBUTE12 /*ATTRIBUTE12 NULL*/
1864 , ATTRIBUTE13 /*ATTRIBUTE13 NULL*/
1865 , ATTRIBUTE14 /*ATTRIBUTE14 NULL*/
1866 , ATTRIBUTE15 /*ATTRIBUTE15 NULL*/
1867 , -1 /*CREATED_BY NOT NULL*/
1868 , sysdate /*CREATION_DATE NOT NULL*/
1869 , -1 /*LAST_UPDATED_BY NOT NULL*/
1870 , sysdate /*LAST_UPDATE_DATE NOT NULL*/
1871 , 0 /*LAST_UPDATE_LOGIN NULL*/
1872 , '' /*PROGRAM_APPLICATION_ NULL*/
1873 , '' /*PROGRAM_ID NULL*/
1874 , '' /*PROGRAM_UPDATE_DATE NULL*/
1875 , '' /*REQUEST_ID NULL*/
1876 , 'INSERT' /*OPERATION_CODE NULL*/
1877 , '' /*ERROR_FLAG NULL*/
1878 , '' /*STATUS_FLAG NULL*/
1879 , '' /*INTERFACE_STATUS NULL*/
1880 , ORDER_SOURCE_ID_V /*ORDER_SOURCE_ID NULL*/
1881 , TO_CHAR(ORDER_NUMBER_V) /*ORIG_SYS_DOCUMENT_RE NULL*/
1882 , get_line_ref_from_line_id(line_id) /*ORIG_SYS_LINE_REF NULL*/
1883 , '' /*ORIG_SYS_SHIPMENT_RE NULL*/
1884 , SALES_CREDIT_ID /*ORIG_SYS_CREDIT_REF NULL*/
1885 , '' /*CHANGE_SEQUENCE NULL*/
1886 , '' /*CHANGE_REQUEST_CODE NULL*/
1890 from oe_sales_credits
1887 , '' /*ORG_ID NULL*/
1888 , SALESREP_ID /*SALESREP_ID NULL*/
1889 , l_sold_to_org_id /*SOLD_TO_ORG_ID NULL*/
1891 where header_id = old_header_id_v
1892 and line_id is not null;
1893
1894 IF SQL%ROWCOUNT > 0 THEN
1895 IF l_debug_level > 0 THEN
1896 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_CREDITS_IFACE_ALL FOR LINES' ) ;
1897 END IF;
1898 ELSE
1899 IF l_debug_level > 0 THEN
1900 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_CREDITS_IFACE_ALL FOR LINES' ) ;
1901 END IF;
1902 END IF;
1903
1904 IF l_debug_level > 0 THEN
1905 oe_debug_pub.add( 'OEXPIFPB: BEFORE INSERT TO OE_LOTSERIALS_IFACE_ALL' ) ;
1906 END IF;
1907
1908 /* Line Level Lots Serial */
1909 INSERT INTO OE_LOTSERIALS_IFACE_ALL (
1910 ORIG_SYS_LINE_REF /*VARCHAR2(50)*/
1911 , ORIG_SYS_SHIPMENT_REF /*VARCHAR2(50)*/
1912 , ORIG_SYS_LOTSERIAL_REF /*VARCHAR2(50)*/
1913 , CHANGE_SEQUENCE /*VARCHAR2(50)*/
1914 , CHANGE_REQUEST_CODE /*VARCHAR2(30)*/
1915 , ORG_ID /*NUMBER(22)*/
1916 , LOT_NUMBER /*NUMBER(80)*/ -- INVCONV
1917 -- , SUBLOT_NUMBER /*VARCHAR2(30)*/ -- OPM 2380194 INVCONV REMOVE
1918 , FROM_SERIAL_NUMBER /*VARCHAR2(30)*/
1919 , TO_SERIAL_NUMBER /*VARCHAR2(30)*/
1920 , QUANTITY /*NUMBER(22)*/
1921 , QUANTITY2 /*NUMBER(22)*/ -- OPM 2380194
1922 , CONTEXT /*VARCHAR2(30)*/
1923 , ATTRIBUTE1 /*VARCHAR2(240)*/
1924 , ATTRIBUTE2 /*VARCHAR2(240)*/
1925 , ATTRIBUTE3 /*VARCHAR2(240)*/
1926 , ATTRIBUTE4 /*VARCHAR2(240)*/
1927 , ATTRIBUTE5 /*VARCHAR2(240)*/
1928 , ATTRIBUTE6 /*VARCHAR2(240)*/
1929 , ATTRIBUTE7 /*VARCHAR2(240)*/
1930 , ATTRIBUTE8 /*VARCHAR2(240)*/
1931 , ATTRIBUTE9 /*VARCHAR2(240)*/
1932 , ATTRIBUTE10 /*VARCHAR2(240)*/
1933 , ATTRIBUTE11 /*VARCHAR2(240)*/
1934 , ATTRIBUTE12 /*VARCHAR2(240)*/
1935 , ATTRIBUTE13 /*VARCHAR2(240)*/
1936 , ATTRIBUTE14 /*VARCHAR2(240)*/
1937 , ATTRIBUTE15 /*VARCHAR2(240)*/
1938 , CREATED_BY /*NUMBER(22)*/
1939 , CREATION_DATE /*DATE(7)*/
1940 , LAST_UPDATED_BY /*NUMBER(22)*/
1941 , LAST_UPDATE_DATE /*DATE(7)*/
1942 , LAST_UPDATE_LOGIN /*NUMBER(22)*/
1943 , PROGRAM_APPLICATION_ID /*NUMBER(22)*/
1944 , PROGRAM_ID /*NUMBER(22)*/
1945 , PROGRAM_UPDATE_DATE /*DATE(7)*/
1946 , REQUEST_ID /*NUMBER(22)*/
1947 , OPERATION_CODE /*VARCHAR2(30)*/
1948 , ERROR_FLAG /*VARCHAR2(1)*/
1949 , STATUS_FLAG /*VARCHAR2(1)*/
1950 , INTERFACE_STATUS /*VARCHAR2(1000)*/
1951 , ORDER_SOURCE_ID /*NUMBER(22)*/
1952 , ORIG_SYS_DOCUMENT_REF /*VARCHAR2(50)*/
1953 , SOLD_TO_ORG_ID /*NUMBER(22)*/
1954 )
1955 Select
1956 get_line_ref_from_line_id(line_id)/*ORIG_SYS_LINE_REF NULL*/
1957 , '' /*ORIG_SYS_SHIPMENT_RE NULL*/
1958 , '' /*ORIG_SYS_LOTSERIAL_R NULL*/
1959 , '' /*CHANGE_SEQUENCE NULL*/
1960 , '' /*CHANGE_REQUEST_CODE NULL*/
1961 , '' /*ORG_ID NULL*/
1962 , LOT_NUMBER /*LOT_NUMBER NULL*/
1963 -- , SUBLOT_NUMBER /*SUBLOT_NUMBER NULL*/ -- OPM 2380194 INVCONV REMOVE
1964 , FROM_SERIAL_NUMBER /*FROM_SERIAL_NUMBER NULL*/
1965 , TO_SERIAL_NUMBER /*TO_SERIAL_NUMBER NULL*/
1966 , QUANTITY /*QUANTITY NULL*/
1967 , QUANTITY2 /*QUANTITY2 NULL*/ -- OPM 2380194
1968 , CONTEXT /*CONTEXT NULL*/
1969 , ATTRIBUTE1 /*ATTRIBUTE1 NULL*/
1970 , ATTRIBUTE2 /*ATTRIBUTE2 NULL*/
1971 , ATTRIBUTE3 /*ATTRIBUTE3 NULL*/
1972 , ATTRIBUTE4 /*ATTRIBUTE4 NULL*/
1973 , ATTRIBUTE5 /*ATTRIBUTE5 NULL*/
1974 , ATTRIBUTE6 /*ATTRIBUTE6 NULL*/
1975 , ATTRIBUTE7 /*ATTRIBUTE7 NULL*/
1976 , ATTRIBUTE8 /*ATTRIBUTE8 NULL*/
1977 , ATTRIBUTE9 /*ATTRIBUTE9 NULL*/
1978 , ATTRIBUTE10 /*ATTRIBUTE10 NULL*/
1979 , ATTRIBUTE11 /*ATTRIBUTE11 NULL*/
1980 , ATTRIBUTE12 /*ATTRIBUTE12 NULL*/
1981 , ATTRIBUTE13 /*ATTRIBUTE13 NULL*/
1982 , ATTRIBUTE14 /*ATTRIBUTE14 NULL*/
1983 , ATTRIBUTE15 /*ATTRIBUTE15 NULL*/
1984 , -1 /*CREATED_BY NOT NULL*/
1985 , sysdate /*CREATION_DATE NOT NULL*/
1986 , -1 /*LAST_UPDATED_BY NOT NULL*/
1987 , sysdate /*LAST_UPDATE_DATE NOT NULL*/
1988 , 0 /*LAST_UPDATE_LOGIN NULL*/
1989 , '' /*PROGRAM_APPLICATION_ NULL*/
1990 , '' /*PROGRAM_ID NULL*/
1991 , '' /*PROGRAM_UPDATE_DATE NULL*/
1992 , '' /*REQUEST_ID NULL*/
1993 , 'INSERT' /*OPERATION_CODE NULL*/
1994 , '' /*ERROR_FLAG NULL*/
1995 , '' /*STATUS_FLAG NULL*/
1999 , l_sold_to_org_id /*L_SOLD_TO_ORG_ID NULL*/
1996 , '' /*INTERFACE_STATUS NULL*/
1997 , ORDER_SOURCE_ID_V /*ORDER_SOURCE_ID NULL*/
1998 , to_char(ORDER_NUMBER_V) /*ORIG_SYS_DOCUMENT_RE NULL*/
2000 From oe_lot_serial_numbers
2001 where line_id in (select line_id from oe_order_lines
2002 where header_id = old_header_id_v);
2003
2004 IF SQL%ROWCOUNT > 0 THEN
2005 IF l_debug_level > 0 THEN
2006 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL INSERT TO OE_LOTSERIALS_IFACE_ALL FOR LINES' ) ;
2007 END IF;
2008 ELSE
2009 IF l_debug_level > 0 THEN
2010 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL INSERT TO OE_LOTSERIALS_IFACE_ALL FOR LINES' ) ;
2011 END IF;
2012 END IF;
2013
2014 IF l_debug_level > 0 THEN
2015 oe_debug_pub.add( 'OEXPIFPB: BEFORE UPDATE OE_LINES_IFACE_ALL' ) ;
2016 END IF;
2017 update oe_lines_iface_all
2018 set line_id = ''
2019 where orig_sys_document_ref = to_char(order_number_v);
2020
2021 IF SQL%ROWCOUNT > 0 THEN
2022 IF l_debug_level > 0 THEN
2023 oe_debug_pub.add( 'OEXPIFPB: AFTER SUCCESSFUL UPDATE OF OE_LINES_IFACE_ALL' ) ;
2024 END IF;
2025 ELSE
2026 IF l_debug_level > 0 THEN
2027 oe_debug_pub.add( 'OEXPIFPB: AFTER UN-SUCCESSFUL UPDATE OF OE_LINES_IFACE_ALL' ) ;
2028 END IF;
2029 END IF;
2030
2031 END IF;
2032
2033 END POPULATE_INTERFACE;
2034
2035 FUNCTION GET_LINK_TO_LINE_REF
2036 ( p_line_id IN NUMBER
2037 )
2038 RETURN VARCHAR2
2039 IS
2040 l_link_to_line_id NUMBER;
2041 l_line_number NUMBER;
2042 l_option_number NUMBER;
2043 --
2044 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2045 --
2046 BEGIN
2047
2048 IF l_debug_level > 0 THEN
2049 oe_debug_pub.add( 'OEXPIFPB: ENTERING GET_LINK_TO_LINE_REF API' ) ;
2050 END IF;
2051
2052 -- dbms_output.put_line('line id =>' || to_char(p_line_id));
2053 SELECT link_to_line_id, line_number, option_number
2054 INTO l_link_to_line_id, l_line_number,l_option_number
2055 FROM oe_order_lines
2056 WHERE line_id = p_line_id;
2057
2058 IF l_debug_level > 0 THEN
2059 oe_debug_pub.add( 'OEXPIFPB: AFTER SELECTING LINK DATA' ) ;
2060 END IF;
2061
2062 IF l_link_to_line_id is null THEN
2063 -- dbms_output.put_line('link to line id is null');
2064 IF l_debug_level > 0 THEN
2065 oe_debug_pub.add( 'OEXPIFPB: LINK LINEID IS NULL' ) ;
2066 END IF;
2067 return null;
2068 ELSIF l_link_to_line_id is not null THEN
2069 IF l_debug_level > 0 THEN
2070 oe_debug_pub.add( 'OEXPIFPB: LINK LINEID IS NOT NULL' ) ;
2071 END IF;
2072 BEGIN
2073 SELECT line_number, option_number
2074 INTO l_line_number, l_option_number
2075 FROM oe_order_lines
2076 WHERE line_id = l_link_to_line_id;
2077 END;
2078
2079 -- dbms_output.put_line('link to line id is not null');
2080 IF l_debug_level > 0 THEN
2081 oe_debug_pub.add( 'OEXPIFPB: BEFORE RETURN LINK DATA' ) ;
2082 END IF;
2083 return to_char(l_line_number) || '-' || to_char(l_option_number);
2084
2085 END IF;
2086
2087 EXCEPTION
2088 WHEN OTHERS THEN
2089 -- dbms_output.put_line('link to line id exception');
2090 IF l_debug_level > 0 THEN
2091 oe_debug_pub.add( 'OEXPIFPB: LINK RETURNS OTHERS EXCEPTION' ) ;
2092 END IF;
2093
2094 return null;
2095
2096 END GET_LINK_TO_LINE_REF;
2097
2098 FUNCTION GET_TOP_MODEL_LINE_REF
2099 ( p_line_id IN NUMBER
2100 )
2101 RETURN VARCHAR2
2102 IS
2103 l_top_model_line_id NUMBER;
2104 l_line_number NUMBER;
2105 l_option_number NUMBER;
2106 --
2107 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2108 --
2109 BEGIN
2110
2111 IF l_debug_level > 0 THEN
2112 oe_debug_pub.add( 'OEXPIFPB: ENTERING GET_TOP_MODEL_LINE_REF API' ) ;
2113 END IF;
2114
2115 SELECT top_model_line_id, line_number, option_number
2116 INTO l_top_model_line_id, l_line_number,l_option_number
2117 FROM oe_order_lines
2118 WHERE line_id = p_line_id;
2119
2120 IF l_top_model_line_id is null THEN
2121 -- dbms_output.put_line('top model is null');
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add( 'OEXPIFPB: TOP MODEL LINEID IS NULL' ) ;
2124 END IF;
2125 return null;
2126 ELSIF l_top_model_line_id is not null THEN
2127 -- dbms_output.put_line('top model is not null');
2128 IF l_debug_level > 0 THEN
2129 oe_debug_pub.add( 'OEXPIFPB: TOP MODEL LINEID IS NOT NULL' ) ;
2130 END IF;
2131 BEGIN
2132 SELECT line_number, option_number
2133 INTO l_line_number, l_option_number
2134 FROM oe_order_lines
2135 WHERE line_id = l_top_model_line_id;
2136 END;
2137
2138 -- dbms_output.put_line('top model is not null before retunr');
2139 IF l_debug_level > 0 THEN
2140 oe_debug_pub.add( 'OEXPIFPB: BEFORE RETURN TOP MODEL DATA' ) ;
2141 END IF;
2142 return to_char(l_line_number) || '-' || to_char(l_option_number);
2143
2144 END IF;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 -- dbms_output.put_line('top model exception');
2149 IF l_debug_level > 0 THEN
2150 oe_debug_pub.add( 'OEXPIFPB: TOP MODEL RETURNS OTHERS EXCEPTION' ) ;
2151 END IF;
2152 return null;
2153
2154 END GET_TOP_MODEL_LINE_REF;
2155
2156 FUNCTION GET_LINE_REF_FROM_LINE_ID
2157 ( p_line_id IN NUMBER
2158 )
2159 RETURN VARCHAR2
2160 IS
2161 l_orig_sys_line_ref VARCHAR2(50);
2162 --
2163 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2164 --
2165 BEGIN
2166
2167 IF l_debug_level > 0 THEN
2168 oe_debug_pub.add( 'OEXPIFPB: ENTERING GET_LINE_REF_FROM_LINE_ID API' ) ;
2169 END IF;
2170 IF l_debug_level > 0 THEN
2171 oe_debug_pub.add( 'P_LINE_ID ==> ' || P_LINE_ID ) ;
2172 END IF;
2173
2174 SELECT orig_sys_line_ref
2175 INTO l_orig_sys_line_ref
2176 FROM oe_lines_iface_all
2177 WHERE line_id = p_line_id;
2178
2179 IF l_debug_level > 0 THEN
2180 oe_debug_pub.add( 'OEXPIFPB: BEFORE RETURN LINE REF' ) ;
2181 END IF;
2182 IF l_debug_level > 0 THEN
2183 oe_debug_pub.add( 'L_ORIG_SYS_LINE_REF ==> ' || L_ORIG_SYS_LINE_REF ) ;
2184 END IF;
2185 return l_orig_sys_line_ref;
2186
2187 EXCEPTION
2188 WHEN OTHERS THEN
2189 IF l_debug_level > 0 THEN
2190 oe_debug_pub.add( 'OEXPIFPB: LINE REF RETURNS OTHERS EXCEPTION' ) ;
2191 END IF;
2192 return null;
2193
2194 END GET_LINE_REF_FROM_LINE_ID;
2195
2196 END OE_INF_POPULATE_PUB;
2197