[Home] [Help]
PACKAGE BODY: APPS.OE_VERSION_HISTORY_UTIL
Source
1 PACKAGE BODY OE_VERSION_HISTORY_UTIL AS
2 /* $Header: OEXHVERB.pls 120.7 2006/06/12 22:26:15 lkxu noship $ */
3
4 -- This API will be used to create history records for entities.
5 -- 1. oe_order_header_history
6 -- 2. oe_order_lines_history
7 -- 3. oe_price_adjs_history
8 -- 4. oe_sales_credit_history
9 -- It will skip the records which already exists for the specified version.
10 --
11 -- If the API is called just for Phase_Change then p_version_number should
12 -- be sent in as NULL and p_phase_change_flag = 'Y'.
13
14 PROCEDURE Create_Version_History
15 (p_header_id IN NUMBER
16 ,p_version_number IN NUMBER
17 ,p_phase_change_flag IN VARCHAR2
18 ,p_changed_attribute IN VARCHAR2 := NULL
19 ,x_return_status IN OUT NOCOPY VARCHAR2)
20 IS
21 l_api_name VARCHAR2(30) := 'Create_Version_History';
22 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
23 l_version_number NUMBER;
24 l_version_flag VARCHAR2(1);
25 l_instrument_id NUMBER;
26 l_credit_card_number VARCHAR2(10);
27 l_credit_card_code VARCHAR2(10);
28 l_credit_card_holder_name VARCHAR2(10);
29 l_credit_card_expiration_date DATE;
30
31 BEGIN
32 -- Initialize API return status to success
33 x_return_status := FND_API.G_RET_STS_SUCCESS;
34
35 -- If the call is made just to create PHASE_CHANGE history then caller will
36 -- send in p_version_number as NULL.
37 IF p_version_number IS NULL and p_phase_change_flag = 'Y' THEN
38
39 SELECT version_number
40 INTO l_version_number
41 FROM oe_order_headers
42 WHERE header_id = p_header_id;
43
44 l_version_flag := 'N';
45 ELSE
46 l_version_number := p_version_number;
47 l_version_flag := 'Y';
48 END IF;
49
50 -- First Create History Records for Headers
51 l_api_name := 'Create_Header_History';
52
53 -- R12 CC encryption
54 -- replaced the following query to join to base table for performance fix
55 /*
56 BEGIN
57 SELECT ite.instrument_id
58 INTO l_instrument_id
59 FROM oe_payments op,
60 iby_trxn_extensions_v ite
61 WHERE op.header_id = p_header_id
62 AND op.trxn_extension_id = ite.trxn_extension_id;
63 EXCEPTION WHEN NO_DATA_FOUND THEN
64 null;
65 END;
66 */
67
68 BEGIN
69 SELECT u.instrument_id
70 INTO l_instrument_id
71 FROM oe_payments op,
72 iby_pmt_instr_uses_all u,
73 iby_fndcpt_tx_extensions x
74 WHERE op.header_id = p_header_id
75 AND op.payment_type_code = 'CREDIT_CARD'
76 AND nvl(op.payment_collection_event, 'PREPAY') = 'INVOICE'
77 AND op.trxn_extension_id = x.trxn_extension_id
78 AND op.line_id is null
79 AND x.instr_assignment_id = u.instrument_payment_use_id;
80 EXCEPTION WHEN NO_DATA_FOUND THEN
81 null;
82 END;
83
84
85 -- for credit card orders, only instrument id is stored for credit card number
86 -- and credit card code, need to set these two values to indicate the column
87 -- instrument_id stores actual instrument_id, otherwise it stores the
88 -- card history change id if other card attributes are being changed.
89 IF l_instrument_id IS NOT NULL THEN
90 IF p_changed_attribute IS NOT NULL THEN
91
92 IF l_debug_level > 0 THEN
93 oe_debug_pub.add( 'p_changed_attr is : ' || p_changed_attribute) ;
94 END IF;
95
96 -- store the instrument_id in column instruemnt_id
97 -- if credit_card_number or credit_card_code is updated.
98 IF instr(p_changed_attribute, 'CREDIT_CARD_NUMBER') > 0 THEN
99 l_credit_card_number := '****';
100 END IF;
101
102 IF instr(p_changed_attribute, 'CREDIT_CARD_CODE') > 0 THEN
103 l_credit_card_code := '****';
104 END IF;
105
106 IF instr(p_changed_attribute, 'CREDIT_CARD_HOLDER_NAME') > 0 THEN
107 l_credit_card_holder_name := '****';
108 END IF;
109
110 IF instr(p_changed_attribute, 'CREDIT_CARD_EXPIRATION_DATE') > 0 THEN
111 l_credit_card_expiration_date := sysdate;
112 END IF;
113
114 -- need to store card_history_change_id in column instrument_id
115 -- if credit_card_expiration_date or credit_card_holder_name is updated
116
117 IF l_credit_card_code IS NULL AND l_credit_card_number IS NULL THEN
118 BEGIN
119 SELECT max(card_history_change_id)
120 INTO l_instrument_id
121 FROM iby_creditcard_h
122 WHERE instrid = l_instrument_id;
123 EXCEPTION WHEN NO_DATA_FOUND THEN
124 NULL;
125 END;
126 END IF;
127
128 /**
129 ELSIF p_changed_attribute IS NULL THEN
130 -- need to store card_history_change_id in column instrument_id
131 -- if credit_card_expiration_date or credit_card_holder_name is updated
132 BEGIN
133 SELECT max(card_history_change_id)
134 INTO l_instrument_id
135 FROM iby_creditcard_h
136 WHERE instrid = l_instrument_id;
137 EXCEPTION WHEN NO_DATA_FOUND THEN
138 NULL;
139 END;
140 **/
141
142 END IF;
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add( 'RECORDHEADERHIST:l_instrument_id is : ' || l_instrument_id) ;
145 END IF;
146 END IF;
147
148
149 INSERT INTO OE_ORDER_HEADER_HISTORY
150 (
151 HEADER_ID,
152 HIST_CREATION_DATE,
153 ORG_ID,
154 ORDER_TYPE_ID,
155 ORDER_NUMBER,
156 VERSION_NUMBER,
157 EXPIRATION_DATE,
158 ORDER_SOURCE_ID,
159 SOURCE_DOCUMENT_TYPE_ID,
160 ORIG_SYS_DOCUMENT_REF,
161 SOURCE_DOCUMENT_ID,
162 ORDERED_DATE,
163 REQUEST_DATE,
164 PRICING_DATE,
165 SHIPMENT_PRIORITY_CODE,
166 DEMAND_CLASS_CODE,
167 PRICE_LIST_ID,
168 TAX_EXEMPT_FLAG,
169 TAX_EXEMPT_NUMBER,
170 TAX_EXEMPT_REASON_CODE,
171 CONVERSION_RATE,
172 CONVERSION_TYPE_CODE,
173 CONVERSION_RATE_DATE,
174 PARTIAL_SHIPMENTS_ALLOWED,
175 SHIP_TOLERANCE_ABOVE,
176 SHIP_TOLERANCE_BELOW,
177 TRANSACTIONAL_CURR_CODE,
178 AGREEMENT_ID,
179 TAX_POINT_CODE,
180 CUST_PO_NUMBER,
181 INVOICING_RULE_ID,
182 ACCOUNTING_RULE_ID,
183 PAYMENT_TERM_ID,
184 SHIPPING_METHOD_CODE,
185 FREIGHT_CARRIER_CODE,
186 FOB_POINT_CODE,
187 FREIGHT_TERMS_CODE,
188 SOLD_FROM_ORG_ID,
189 SOLD_TO_ORG_ID,
190 SHIP_FROM_ORG_ID,
191 SHIP_TO_ORG_ID,
192 INVOICE_TO_ORG_ID,
193 DELIVER_TO_ORG_ID,
194 SOLD_TO_CONTACT_ID,
195 SHIP_TO_CONTACT_ID,
196 INVOICE_TO_CONTACT_ID,
197 DELIVER_TO_CONTACT_ID,
198 CREATION_DATE,
199 CREATED_BY,
200 LAST_UPDATED_BY,
201 LAST_UPDATE_DATE,
202 LAST_UPDATE_LOGIN,
203 PROGRAM_APPLICATION_ID,
204 PROGRAM_ID,
205 PROGRAM_UPDATE_DATE,
206 REQUEST_ID,
207 CONTEXT,
208 ATTRIBUTE1,
209 ATTRIBUTE2,
210 ATTRIBUTE3,
211 ATTRIBUTE4,
212 ATTRIBUTE5,
213 ATTRIBUTE6,
214 ATTRIBUTE7,
215 ATTRIBUTE8,
216 ATTRIBUTE9,
217 ATTRIBUTE10,
218 ATTRIBUTE11,
219 ATTRIBUTE12,
220 ATTRIBUTE13,
221 ATTRIBUTE14,
222 ATTRIBUTE15,
223 GLOBAL_ATTRIBUTE_CATEGORY,
224 GLOBAL_ATTRIBUTE1,
225 GLOBAL_ATTRIBUTE2,
226 GLOBAL_ATTRIBUTE3,
227 GLOBAL_ATTRIBUTE4,
228 GLOBAL_ATTRIBUTE5,
229 GLOBAL_ATTRIBUTE6,
230 GLOBAL_ATTRIBUTE7,
231 GLOBAL_ATTRIBUTE8,
232 GLOBAL_ATTRIBUTE9,
233 GLOBAL_ATTRIBUTE10,
234 GLOBAL_ATTRIBUTE11,
235 GLOBAL_ATTRIBUTE12,
236 GLOBAL_ATTRIBUTE13,
237 GLOBAL_ATTRIBUTE14,
238 GLOBAL_ATTRIBUTE15,
239 GLOBAL_ATTRIBUTE16,
240 GLOBAL_ATTRIBUTE17,
241 GLOBAL_ATTRIBUTE18,
242 GLOBAL_ATTRIBUTE19,
243 GLOBAL_ATTRIBUTE20,
244 CANCELLED_FLAG,
245 OPEN_FLAG,
246 BOOKED_FLAG,
247 SALESREP_ID,
248 RETURN_REASON_CODE,
249 ORDER_DATE_TYPE_CODE,
250 EARLIEST_SCHEDULE_LIMIT,
251 LATEST_SCHEDULE_LIMIT,
252 PAYMENT_TYPE_CODE,
253 PAYMENT_AMOUNT,
254 CHECK_NUMBER,
255 CREDIT_CARD_NUMBER,
256 CREDIT_CARD_CODE,
257 CREDIT_CARD_HOLDER_NAME,
258 CREDIT_CARD_EXPIRATION_DATE,
259 -- CREDIT_CARD_APPROVAL_CODE,
260 SALES_CHANNEL_CODE,
261 FIRST_ACK_CODE,
262 FIRST_ACK_DATE,
263 LAST_ACK_CODE,
264 LAST_ACK_DATE,
265 ORDER_CATEGORY_CODE,
266 CHANGE_SEQUENCE,
267 SHIPPING_INSTRUCTIONS,
268 PACKING_INSTRUCTIONS,
269 TP_CONTEXT,
270 TP_ATTRIBUTE1,
271 TP_ATTRIBUTE2,
272 TP_ATTRIBUTE3,
273 TP_ATTRIBUTE4,
274 TP_ATTRIBUTE5,
275 TP_ATTRIBUTE6,
276 TP_ATTRIBUTE7,
277 TP_ATTRIBUTE8,
278 TP_ATTRIBUTE9,
279 TP_ATTRIBUTE10,
280 TP_ATTRIBUTE11,
281 TP_ATTRIBUTE12,
282 TP_ATTRIBUTE13,
283 TP_ATTRIBUTE14,
284 TP_ATTRIBUTE15,
285 FLOW_STATUS_CODE,
286 MARKETING_SOURCE_CODE_ID,
287 CREDIT_CARD_APPROVAL_DATE,
288 UPGRADED_FLAG,
289 CUSTOMER_PREFERENCE_SET_CODE,
290 BOOKED_DATE,
291 LOCK_CONTROL,
292 REASON_CODE,
293 HIST_COMMENTS,
294 HIST_TYPE_CODE,
295 HIST_CREATED_BY,
296 RESPONSIBILITY_ID,
297 PRICE_REQUEST_CODE,
298 ACCOUNTING_RULE_DURATION,
299 ATTRIBUTE16,
300 ATTRIBUTE17,
301 ATTRIBUTE18,
302 ATTRIBUTE19,
303 ATTRIBUTE20,
304 ITEM_RELATIONSHIP_TYPE,
305 BLANKET_NUMBER,
306 SALES_DOCUMENT_TYPE_CODE,
307 SOLD_TO_PHONE_ID,
308 QUOTE_NUMBER,
309 SALES_DOCUMENT_NAME,
310 TRANSACTION_PHASE_CODE,
311 USER_STATUS_CODE,
312 DRAFT_SUBMITTED_FLAG,
313 SOURCE_DOCUMENT_VERSION_NUMBER,
314 SOLD_TO_SITE_USE_ID,
315 QUOTE_DATE,
316 SUPPLIER_SIGNATURE,
317 SUPPLIER_SIGNATURE_DATE,
318 CUSTOMER_SIGNATURE,
319 CUSTOMER_SIGNATURE_DATE,
320 AUDIT_FLAG,
321 VERSION_FLAG,
322 PHASE_CHANGE_FLAG,
323 --key Transaction Dates
324 ORDER_FIRMED_DATE,
325 INSTRUMENT_ID
326 )
327 SELECT
328 HEADER_ID,
329 sysdate,
330 ORG_ID,
331 ORDER_TYPE_ID,
332 ORDER_NUMBER,
333 VERSION_NUMBER,
334 EXPIRATION_DATE,
335 ORDER_SOURCE_ID,
336 SOURCE_DOCUMENT_TYPE_ID,
337 ORIG_SYS_DOCUMENT_REF,
338 SOURCE_DOCUMENT_ID,
339 ORDERED_DATE,
340 REQUEST_DATE,
341 PRICING_DATE,
342 SHIPMENT_PRIORITY_CODE,
343 DEMAND_CLASS_CODE,
344 PRICE_LIST_ID,
345 TAX_EXEMPT_FLAG,
346 TAX_EXEMPT_NUMBER,
347 TAX_EXEMPT_REASON_CODE,
348 CONVERSION_RATE,
349 CONVERSION_TYPE_CODE,
350 CONVERSION_RATE_DATE,
351 PARTIAL_SHIPMENTS_ALLOWED,
352 SHIP_TOLERANCE_ABOVE,
353 SHIP_TOLERANCE_BELOW,
354 TRANSACTIONAL_CURR_CODE,
355 AGREEMENT_ID,
356 TAX_POINT_CODE,
357 CUST_PO_NUMBER,
358 INVOICING_RULE_ID,
359 ACCOUNTING_RULE_ID,
360 PAYMENT_TERM_ID,
361 SHIPPING_METHOD_CODE,
362 FREIGHT_CARRIER_CODE,
363 FOB_POINT_CODE,
364 FREIGHT_TERMS_CODE,
365 SOLD_FROM_ORG_ID,
366 SOLD_TO_ORG_ID,
367 SHIP_FROM_ORG_ID,
368 SHIP_TO_ORG_ID,
369 INVOICE_TO_ORG_ID,
370 DELIVER_TO_ORG_ID,
371 SOLD_TO_CONTACT_ID,
372 SHIP_TO_CONTACT_ID,
373 INVOICE_TO_CONTACT_ID,
374 DELIVER_TO_CONTACT_ID,
375 CREATION_DATE,
376 CREATED_BY,
377 LAST_UPDATED_BY,
378 LAST_UPDATE_DATE,
379 LAST_UPDATE_LOGIN,
380 PROGRAM_APPLICATION_ID,
381 PROGRAM_ID,
382 PROGRAM_UPDATE_DATE,
383 REQUEST_ID,
384 CONTEXT,
385 ATTRIBUTE1,
386 ATTRIBUTE2,
387 ATTRIBUTE3,
388 ATTRIBUTE4,
389 ATTRIBUTE5,
390 ATTRIBUTE6,
391 ATTRIBUTE7,
392 ATTRIBUTE8,
393 ATTRIBUTE9,
394 ATTRIBUTE10,
395 ATTRIBUTE11,
396 ATTRIBUTE12,
397 ATTRIBUTE13,
398 ATTRIBUTE14,
399 ATTRIBUTE15,
400 GLOBAL_ATTRIBUTE_CATEGORY,
401 GLOBAL_ATTRIBUTE1,
402 GLOBAL_ATTRIBUTE2,
403 GLOBAL_ATTRIBUTE3,
404 GLOBAL_ATTRIBUTE4,
405 GLOBAL_ATTRIBUTE5,
406 GLOBAL_ATTRIBUTE6,
407 GLOBAL_ATTRIBUTE7,
408 GLOBAL_ATTRIBUTE8,
409 GLOBAL_ATTRIBUTE9,
410 GLOBAL_ATTRIBUTE10,
411 GLOBAL_ATTRIBUTE11,
412 GLOBAL_ATTRIBUTE12,
413 GLOBAL_ATTRIBUTE13,
414 GLOBAL_ATTRIBUTE14,
415 GLOBAL_ATTRIBUTE15,
416 GLOBAL_ATTRIBUTE16,
417 GLOBAL_ATTRIBUTE17,
418 GLOBAL_ATTRIBUTE18,
419 GLOBAL_ATTRIBUTE19,
420 GLOBAL_ATTRIBUTE20,
421 CANCELLED_FLAG,
422 OPEN_FLAG,
423 BOOKED_FLAG,
424 SALESREP_ID,
425 RETURN_REASON_CODE,
426 ORDER_DATE_TYPE_CODE,
427 EARLIEST_SCHEDULE_LIMIT,
428 LATEST_SCHEDULE_LIMIT,
429 PAYMENT_TYPE_CODE,
430 PAYMENT_AMOUNT,
431 CHECK_NUMBER,
432 L_CREDIT_CARD_NUMBER,
433 L_CREDIT_CARD_CODE,
434 L_CREDIT_CARD_HOLDER_NAME,
435 L_CREDIT_CARD_EXPIRATION_DATE,
436 -- CREDIT_CARD_APPROVAL_CODE,
437 SALES_CHANNEL_CODE,
438 FIRST_ACK_CODE,
439 FIRST_ACK_DATE,
440 LAST_ACK_CODE,
441 LAST_ACK_DATE,
442 ORDER_CATEGORY_CODE,
443 CHANGE_SEQUENCE,
444 SHIPPING_INSTRUCTIONS,
445 PACKING_INSTRUCTIONS,
446 TP_CONTEXT,
447 TP_ATTRIBUTE1,
448 TP_ATTRIBUTE2,
449 TP_ATTRIBUTE3,
450 TP_ATTRIBUTE4,
451 TP_ATTRIBUTE5,
452 TP_ATTRIBUTE6,
453 TP_ATTRIBUTE7,
454 TP_ATTRIBUTE8,
455 TP_ATTRIBUTE9,
456 TP_ATTRIBUTE10,
457 TP_ATTRIBUTE11,
458 TP_ATTRIBUTE12,
459 TP_ATTRIBUTE13,
460 TP_ATTRIBUTE14,
461 TP_ATTRIBUTE15,
462 FLOW_STATUS_CODE,
463 MARKETING_SOURCE_CODE_ID,
464 CREDIT_CARD_APPROVAL_DATE,
465 UPGRADED_FLAG,
466 CUSTOMER_PREFERENCE_SET_CODE,
467 BOOKED_DATE,
468 LOCK_CONTROL,
469 NULL, --REASON_CODE,
470 NULL, --HIST_COMMENTS,
471 NULL, --HIST_TYPE_CODE,
472 nvl(FND_GLOBAL.USER_ID, -1), --HIST_CREATED_BY,
473 nvl(FND_GLOBAL.RESP_ID, -1), --RESPONSIBILITY_ID,
474 PRICE_REQUEST_CODE,
475 ACCOUNTING_RULE_DURATION,
476 ATTRIBUTE16,
477 ATTRIBUTE17,
478 ATTRIBUTE18,
479 ATTRIBUTE19,
480 ATTRIBUTE20,
481 NULL, --ITEM_RELATIONSHIP_TYPE,
482 BLANKET_NUMBER,
483 SALES_DOCUMENT_TYPE_CODE,
484 SOLD_TO_PHONE_ID,
485 QUOTE_NUMBER,
486 SALES_DOCUMENT_NAME,
487 TRANSACTION_PHASE_CODE,
488 USER_STATUS_CODE,
489 DRAFT_SUBMITTED_FLAG,
490 SOURCE_DOCUMENT_VERSION_NUMBER,
491 SOLD_TO_SITE_USE_ID,
492 QUOTE_DATE,
493 SUPPLIER_SIGNATURE,
494 SUPPLIER_SIGNATURE_DATE,
495 CUSTOMER_SIGNATURE,
496 CUSTOMER_SIGNATURE_DATE,
497 'N', -- AUDIT_FLAG,
498 l_version_flag, -- VERSION_FLAG,
499 p_phase_change_flag, --PHASE_CHANGE_FLAG
500 --key Transaction Dates
501 order_firmed_date,
502 l_instrument_id
503 FROM OE_ORDER_HEADERS a
504 WHERE a.header_id = p_header_id
505 AND NOT EXISTS (SELECT b.header_id
506 FROM OE_ORDER_HEADER_HISTORY b
507 WHERE a.header_id = b.header_id
508 AND b.version_number = l_version_number
509 AND b.version_flag = l_version_flag);
510 IF l_debug_level > 0 THEN
511 oe_debug_pub.add(' No. of History Records for Header ' ||SQL%ROWCOUNT);
512 END IF;
513
514 -- Create History Records for Lines
515 l_api_name := 'Create_Line_History';
516
517 INSERT INTO oe_order_lines_history
518 (
519 LINE_ID,
520 ORG_ID,
521 HEADER_ID,
522 LINE_TYPE_ID,
523 LINE_NUMBER,
524 REQUEST_DATE,
525 PROMISE_DATE,
526 SCHEDULE_SHIP_DATE,
527 ORDER_QUANTITY_UOM,
528 PRICING_QUANTITY,
529 PRICING_QUANTITY_UOM,
530 CANCELLED_QUANTITY,
531 SHIPPED_QUANTITY,
532 ORDERED_QUANTITY,
533 FULFILLED_QUANTITY,
534 SHIPPING_QUANTITY,
535 SHIPPING_QUANTITY_UOM,
536 DELIVERY_LEAD_TIME,
537 TAX_EXEMPT_FLAG,
538 TAX_EXEMPT_NUMBER,
539 TAX_EXEMPT_REASON_CODE,
540 SHIP_FROM_ORG_ID,
541 SHIP_TO_ORG_ID,
542 INVOICE_TO_ORG_ID,
543 DELIVER_TO_ORG_ID,
544 SHIP_TO_CONTACT_ID,
545 DELIVER_TO_CONTACT_ID,
546 INVOICE_TO_CONTACT_ID,
547 SOLD_FROM_ORG_ID,
548 SOLD_TO_ORG_ID,
549 CUST_PO_NUMBER,
550 SHIP_TOLERANCE_ABOVE,
551 SHIP_TOLERANCE_BELOW,
552 DEMAND_BUCKET_TYPE_CODE,
553 VEH_CUS_ITEM_CUM_KEY_ID,
554 RLA_SCHEDULE_TYPE_CODE,
555 CUSTOMER_DOCK_CODE,
556 CUSTOMER_JOB,
557 CUSTOMER_PRODUCTION_LINE,
558 CUST_MODEL_SERIAL_NUMBER,
559 PROJECT_ID,
560 TASK_ID,
561 INVENTORY_ITEM_ID,
562 TAX_DATE,
563 TAX_CODE,
564 TAX_RATE,
565 DEMAND_CLASS_CODE,
566 PRICE_LIST_ID,
567 PRICING_DATE,
568 SHIPMENT_NUMBER,
569 AGREEMENT_ID,
570 SHIPMENT_PRIORITY_CODE,
571 SHIPPING_METHOD_CODE,
572 FREIGHT_CARRIER_CODE,
573 FREIGHT_TERMS_CODE,
574 FOB_POINT_CODE,
575 TAX_POINT_CODE,
576 PAYMENT_TERM_ID,
577 INVOICING_RULE_ID,
578 ACCOUNTING_RULE_ID,
579 SOURCE_DOCUMENT_TYPE_ID,
580 ORIG_SYS_DOCUMENT_REF,
581 SOURCE_DOCUMENT_ID,
582 ORIG_SYS_LINE_REF,
583 SOURCE_DOCUMENT_LINE_ID,
584 REFERENCE_LINE_ID,
585 REFERENCE_TYPE,
586 REFERENCE_HEADER_ID,
587 ITEM_REVISION,
588 UNIT_SELLING_PRICE,
589 UNIT_LIST_PRICE,
590 TAX_VALUE,
591 CONTEXT,
592 ATTRIBUTE1,
593 ATTRIBUTE2,
594 ATTRIBUTE3,
595 ATTRIBUTE4,
596 ATTRIBUTE5,
597 ATTRIBUTE6,
598 ATTRIBUTE7,
599 ATTRIBUTE8,
600 ATTRIBUTE9,
601 ATTRIBUTE10,
602 ATTRIBUTE11,
603 ATTRIBUTE12,
604 ATTRIBUTE13,
605 ATTRIBUTE14,
606 ATTRIBUTE15,
607 GLOBAL_ATTRIBUTE_CATEGORY,
608 GLOBAL_ATTRIBUTE1,
609 GLOBAL_ATTRIBUTE2,
610 GLOBAL_ATTRIBUTE3,
611 GLOBAL_ATTRIBUTE4,
612 GLOBAL_ATTRIBUTE5,
613 GLOBAL_ATTRIBUTE6,
614 GLOBAL_ATTRIBUTE7,
615 GLOBAL_ATTRIBUTE8,
616 GLOBAL_ATTRIBUTE9,
617 GLOBAL_ATTRIBUTE10,
618 GLOBAL_ATTRIBUTE11,
619 GLOBAL_ATTRIBUTE12,
620 GLOBAL_ATTRIBUTE13,
621 GLOBAL_ATTRIBUTE14,
622 GLOBAL_ATTRIBUTE15,
623 GLOBAL_ATTRIBUTE16,
624 GLOBAL_ATTRIBUTE17,
625 GLOBAL_ATTRIBUTE18,
626 GLOBAL_ATTRIBUTE19,
627 GLOBAL_ATTRIBUTE20,
628 PRICING_CONTEXT,
629 PRICING_ATTRIBUTE1,
630 PRICING_ATTRIBUTE2,
631 PRICING_ATTRIBUTE3,
632 PRICING_ATTRIBUTE4,
633 PRICING_ATTRIBUTE5,
634 PRICING_ATTRIBUTE6,
635 PRICING_ATTRIBUTE7,
636 PRICING_ATTRIBUTE8,
637 PRICING_ATTRIBUTE9,
638 PRICING_ATTRIBUTE10,
639 INDUSTRY_CONTEXT,
640 INDUSTRY_ATTRIBUTE1,
641 INDUSTRY_ATTRIBUTE2,
642 INDUSTRY_ATTRIBUTE3,
643 INDUSTRY_ATTRIBUTE4,
644 INDUSTRY_ATTRIBUTE5,
645 INDUSTRY_ATTRIBUTE6,
646 INDUSTRY_ATTRIBUTE7,
647 INDUSTRY_ATTRIBUTE8,
648 INDUSTRY_ATTRIBUTE9,
649 INDUSTRY_ATTRIBUTE10,
650 INDUSTRY_ATTRIBUTE11,
651 INDUSTRY_ATTRIBUTE13,
652 INDUSTRY_ATTRIBUTE12,
653 INDUSTRY_ATTRIBUTE14,
654 INDUSTRY_ATTRIBUTE15,
655 INDUSTRY_ATTRIBUTE16,
656 INDUSTRY_ATTRIBUTE17,
657 INDUSTRY_ATTRIBUTE18,
658 INDUSTRY_ATTRIBUTE19,
659 INDUSTRY_ATTRIBUTE20,
660 INDUSTRY_ATTRIBUTE21,
661 INDUSTRY_ATTRIBUTE22,
662 INDUSTRY_ATTRIBUTE23,
663 INDUSTRY_ATTRIBUTE24,
664 INDUSTRY_ATTRIBUTE25,
665 INDUSTRY_ATTRIBUTE26,
666 INDUSTRY_ATTRIBUTE27,
667 INDUSTRY_ATTRIBUTE28,
668 INDUSTRY_ATTRIBUTE29,
669 INDUSTRY_ATTRIBUTE30,
670 CREATION_DATE,
671 CREATED_BY,
672 LAST_UPDATE_DATE,
673 LAST_UPDATED_BY,
674 LAST_UPDATE_LOGIN,
675 PROGRAM_APPLICATION_ID,
676 PROGRAM_ID,
677 PROGRAM_UPDATE_DATE,
678 REQUEST_ID,
679 CONFIGURATION_ID,
680 LINK_TO_LINE_ID,
681 COMPONENT_SEQUENCE_ID,
682 COMPONENT_CODE,
683 CONFIG_DISPLAY_SEQUENCE,
684 SORT_ORDER,
685 ITEM_TYPE_CODE,
686 OPTION_NUMBER,
687 OPTION_FLAG,
688 DEP_PLAN_REQUIRED_FLAG,
689 VISIBLE_DEMAND_FLAG,
690 LINE_CATEGORY_CODE,
691 ACTUAL_SHIPMENT_DATE,
692 CUSTOMER_TRX_LINE_ID,
693 RETURN_CONTEXT,
694 RETURN_ATTRIBUTE1,
695 RETURN_ATTRIBUTE2,
696 RETURN_ATTRIBUTE3,
697 RETURN_ATTRIBUTE4,
698 RETURN_ATTRIBUTE5,
699 RETURN_ATTRIBUTE6,
700 RETURN_ATTRIBUTE7,
701 RETURN_ATTRIBUTE8,
702 RETURN_ATTRIBUTE9,
703 RETURN_ATTRIBUTE10,
704 RETURN_ATTRIBUTE11,
705 RETURN_ATTRIBUTE12,
706 RETURN_ATTRIBUTE13,
707 RETURN_ATTRIBUTE14,
708 RETURN_ATTRIBUTE15,
709 INTMED_SHIP_TO_ORG_ID,
710 INTMED_SHIP_TO_CONTACT_ID,
711 ACTUAL_ARRIVAL_DATE,
712 ATO_LINE_ID,
713 AUTO_SELECTED_QUANTITY,
714 COMPONENT_NUMBER,
715 EARLIEST_ACCEPTABLE_DATE,
716 EXPLOSION_DATE,
717 LATEST_ACCEPTABLE_DATE,
718 MODEL_GROUP_NUMBER,
719 SCHEDULE_ARRIVAL_DATE,
720 SHIP_MODEL_COMPLETE_FLAG,
721 SCHEDULE_STATUS_CODE,
722 SOURCE_TYPE_CODE,
723 TOP_MODEL_LINE_ID,
724 BOOKED_FLAG,
725 CANCELLED_FLAG,
726 OPEN_FLAG,
727 LOAD_SEQ_NUMBER,
728 DELIVERY_ID,
729 SALESREP_ID,
730 RETURN_REASON_CODE,
731 WF_ACTIVITY_CODE,
732 WF_RESULT_CODE,
733 REASON_CODE,
734 HIST_COMMENTS,
735 HIST_TYPE_CODE,
736 HIST_CREATION_DATE,
737 HIST_CREATED_BY,
738 CUST_PRODUCTION_SEQ_NUM,
739 AUTHORIZED_TO_SHIP_FLAG,
740 SPLIT_FROM_LINE_ID,
741 OVER_SHIP_REASON_CODE,
742 OVER_SHIP_RESOLVED_FLAG,
743 ITEM_IDENTIFIER_TYPE,
744 ARRIVAL_SET_ID,
745 SHIP_SET_ID,
746 COMMITMENT_ID,
747 SHIPPING_INTERFACED_FLAG,
748 CREDIT_INVOICE_LINE_ID,
749 MFG_COMPONENT_SEQUENCE_ID,
750 TP_CONTEXT,
751 TP_ATTRIBUTE1,
752 TP_ATTRIBUTE2,
753 TP_ATTRIBUTE3,
754 TP_ATTRIBUTE4,
755 TP_ATTRIBUTE5,
756 TP_ATTRIBUTE6,
757 TP_ATTRIBUTE7,
758 TP_ATTRIBUTE8,
759 TP_ATTRIBUTE9,
760 TP_ATTRIBUTE10,
761 TP_ATTRIBUTE11,
762 TP_ATTRIBUTE12,
763 TP_ATTRIBUTE13,
764 TP_ATTRIBUTE14,
765 TP_ATTRIBUTE15,
766 FULFILLMENT_METHOD_CODE,
767 SERVICE_REFERENCE_TYPE_CODE,
768 SERVICE_REFERENCE_LINE_ID,
769 SERVICE_REFERENCE_SYSTEM_ID,
770 INVOICE_INTERFACE_STATUS_CODE,
771 ORDERED_ITEM,
772 ORDERED_ITEM_ID,
773 SERVICE_NUMBER,
774 SERVICED_LINE_ID,
775 SERVICE_DURATION,
776 SERVICE_START_DATE,
777 RE_SOURCE_FLAG,
778 FLOW_STATUS_CODE,
779 SERVICE_END_DATE,
780 SERVICE_COTERMINATE_FLAG,
781 SHIPPABLE_FLAG,
782 ORDER_SOURCE_ID,
783 ORIG_SYS_SHIPMENT_REF,
784 CHANGE_SEQUENCE,
785 DROP_SHIP_FLAG,
786 CUSTOMER_LINE_NUMBER,
787 CUSTOMER_SHIPMENT_NUMBER,
788 CUSTOMER_ITEM_NET_PRICE,
789 CUSTOMER_PAYMENT_TERM_ID,
790 FIRST_ACK_DATE,
791 FIRST_ACK_CODE,
792 LAST_ACK_CODE,
793 LAST_ACK_DATE,
794 PLANNING_PRIORITY,
795 SERVICE_TXN_COMMENTS,
796 SERVICE_PERIOD,
797 UNIT_SELLING_PERCENT,
798 UNIT_LIST_PERCENT,
799 UNIT_PERCENT_BASE_PRICE,
800 MODEL_REMNANT_FLAG,
801 SERVICE_TXN_REASON_CODE,
802 CALCULATE_PRICE_FLAG,
803 ITEM_INPUT,
804 CUSTOMER_ITEM_ID,
805 CUSTOMER_ITEM_REVISION,
806 ITEM_ID,
807 INVOICE_COMPLETE_FLAG,
808 END_ITEM_UNIT_NUMBER,
809 FULFILLED_FLAG,
810 CONFIG_HEADER_ID,
811 CONFIG_REV_NBR,
812 SHIPPING_INSTRUCTIONS,
813 PACKING_INSTRUCTIONS,
814 INVOICED_QUANTITY,
815 REFERENCE_CUSTOMER_TRX_LINE_ID,
816 SPLIT_BY,
817 LINE_SET_ID,
818 REVENUE_AMOUNT,
819 FULFILLMENT_DATE,
820 PREFERRED_GRADE,
821 ORDERED_QUANTITY2,
822 ORDERED_QUANTITY_UOM2,
823 SHIPPED_QUANTITY2,
824 CANCELLED_QUANTITY2,
825 SHIPPING_QUANTITY2,
826 SHIPPING_QUANTITY_UOM2,
827 FULFILLED_QUANTITY2,
828 SUBINVENTORY,
829 RESPONSIBILITY_ID,
830 UNIT_LIST_PRICE_PER_PQTY,
831 UNIT_SELLING_PRICE_PER_PQTY,
832 LATEST_CANCELLED_QUANTITY,
833 PRICE_REQUEST_CODE,
834 LATE_DEMAND_PENALTY_FACTOR,
835 OVERRIDE_ATP_DATE_CODE,
836 ITEM_SUBSTITUTION_TYPE_CODE,
837 ORIGINAL_ITEM_IDENTIFIER_TYPE,
838 ORIGINAL_ORDERED_ITEM,
839 ORIGINAL_ORDERED_ITEM_ID,
840 ORIGINAL_INVENTORY_ITEM_ID,
841 ACCOUNTING_RULE_DURATION,
842 ATTRIBUTE16,
843 ATTRIBUTE17,
844 ATTRIBUTE18,
845 ATTRIBUTE19,
846 ATTRIBUTE20,
847 USER_ITEM_DESCRIPTION,
848 ITEM_RELATIONSHIP_TYPE,
849 BLANKET_NUMBER,
850 BLANKET_LINE_NUMBER,
851 BLANKET_VERSION_NUMBER,
852 VERSION_NUMBER,
853 SALES_DOCUMENT_TYPE_CODE,
854 AUDIT_FLAG,
855 VERSION_FLAG,
856 PHASE_CHANGE_FLAG,
857 TRANSACTION_PHASE_CODE,
858 SOURCE_DOCUMENT_VERSION_NUMBER,
859 --key Transaction Dates
860 ORDER_FIRMED_DATE,
861 CHARGE_PERIODICITY_CODE,
862 ACTUAL_FULFILLMENT_DATE
863 )
864 SELECT
865 LINE_ID,
866 ORG_ID,
867 HEADER_ID,
868 LINE_TYPE_ID,
869 LINE_NUMBER,
870 REQUEST_DATE,
871 PROMISE_DATE,
872 SCHEDULE_SHIP_DATE,
873 ORDER_QUANTITY_UOM,
874 PRICING_QUANTITY,
875 PRICING_QUANTITY_UOM,
876 CANCELLED_QUANTITY,
877 SHIPPED_QUANTITY,
878 ORDERED_QUANTITY,
879 FULFILLED_QUANTITY,
880 SHIPPING_QUANTITY,
881 SHIPPING_QUANTITY_UOM,
882 DELIVERY_LEAD_TIME,
883 TAX_EXEMPT_FLAG,
884 TAX_EXEMPT_NUMBER,
885 TAX_EXEMPT_REASON_CODE,
886 SHIP_FROM_ORG_ID,
887 SHIP_TO_ORG_ID,
888 INVOICE_TO_ORG_ID,
889 DELIVER_TO_ORG_ID,
890 SHIP_TO_CONTACT_ID,
891 DELIVER_TO_CONTACT_ID,
892 INVOICE_TO_CONTACT_ID,
893 SOLD_FROM_ORG_ID,
894 SOLD_TO_ORG_ID,
895 CUST_PO_NUMBER,
896 SHIP_TOLERANCE_ABOVE,
897 SHIP_TOLERANCE_BELOW,
898 DEMAND_BUCKET_TYPE_CODE,
899 VEH_CUS_ITEM_CUM_KEY_ID,
900 RLA_SCHEDULE_TYPE_CODE,
901 CUSTOMER_DOCK_CODE,
902 CUSTOMER_JOB,
903 CUSTOMER_PRODUCTION_LINE,
904 CUST_MODEL_SERIAL_NUMBER,
905 PROJECT_ID,
906 TASK_ID,
907 INVENTORY_ITEM_ID,
908 TAX_DATE,
909 TAX_CODE,
910 TAX_RATE,
911 DEMAND_CLASS_CODE,
912 PRICE_LIST_ID,
913 PRICING_DATE,
914 SHIPMENT_NUMBER,
915 AGREEMENT_ID,
916 SHIPMENT_PRIORITY_CODE,
917 SHIPPING_METHOD_CODE,
918 FREIGHT_CARRIER_CODE,
919 FREIGHT_TERMS_CODE,
920 FOB_POINT_CODE,
921 TAX_POINT_CODE,
922 PAYMENT_TERM_ID,
923 INVOICING_RULE_ID,
924 ACCOUNTING_RULE_ID,
925 SOURCE_DOCUMENT_TYPE_ID,
926 ORIG_SYS_DOCUMENT_REF,
927 SOURCE_DOCUMENT_ID,
928 ORIG_SYS_LINE_REF,
929 SOURCE_DOCUMENT_LINE_ID,
930 REFERENCE_LINE_ID,
931 REFERENCE_TYPE,
932 REFERENCE_HEADER_ID,
933 ITEM_REVISION,
934 UNIT_SELLING_PRICE,
935 UNIT_LIST_PRICE,
936 TAX_VALUE,
937 CONTEXT,
938 ATTRIBUTE1,
939 ATTRIBUTE2,
940 ATTRIBUTE3,
941 ATTRIBUTE4,
942 ATTRIBUTE5,
943 ATTRIBUTE6,
944 ATTRIBUTE7,
945 ATTRIBUTE8,
946 ATTRIBUTE9,
947 ATTRIBUTE10,
948 ATTRIBUTE11,
949 ATTRIBUTE12,
950 ATTRIBUTE13,
951 ATTRIBUTE14,
952 ATTRIBUTE15,
953 GLOBAL_ATTRIBUTE_CATEGORY,
954 GLOBAL_ATTRIBUTE1,
955 GLOBAL_ATTRIBUTE2,
956 GLOBAL_ATTRIBUTE3,
957 GLOBAL_ATTRIBUTE4,
958 GLOBAL_ATTRIBUTE5,
959 GLOBAL_ATTRIBUTE6,
960 GLOBAL_ATTRIBUTE7,
961 GLOBAL_ATTRIBUTE8,
962 GLOBAL_ATTRIBUTE9,
963 GLOBAL_ATTRIBUTE10,
964 GLOBAL_ATTRIBUTE11,
965 GLOBAL_ATTRIBUTE12,
966 GLOBAL_ATTRIBUTE13,
967 GLOBAL_ATTRIBUTE14,
968 GLOBAL_ATTRIBUTE15,
969 GLOBAL_ATTRIBUTE16,
970 GLOBAL_ATTRIBUTE17,
971 GLOBAL_ATTRIBUTE18,
972 GLOBAL_ATTRIBUTE19,
973 GLOBAL_ATTRIBUTE20,
974 PRICING_CONTEXT,
975 PRICING_ATTRIBUTE1,
976 PRICING_ATTRIBUTE2,
977 PRICING_ATTRIBUTE3,
978 PRICING_ATTRIBUTE4,
979 PRICING_ATTRIBUTE5,
980 PRICING_ATTRIBUTE6,
981 PRICING_ATTRIBUTE7,
982 PRICING_ATTRIBUTE8,
983 PRICING_ATTRIBUTE9,
984 PRICING_ATTRIBUTE10,
985 INDUSTRY_CONTEXT,
986 INDUSTRY_ATTRIBUTE1,
987 INDUSTRY_ATTRIBUTE2,
988 INDUSTRY_ATTRIBUTE3,
989 INDUSTRY_ATTRIBUTE4,
990 INDUSTRY_ATTRIBUTE5,
991 INDUSTRY_ATTRIBUTE6,
992 INDUSTRY_ATTRIBUTE7,
993 INDUSTRY_ATTRIBUTE8,
994 INDUSTRY_ATTRIBUTE9,
995 INDUSTRY_ATTRIBUTE10,
996 INDUSTRY_ATTRIBUTE11,
997 INDUSTRY_ATTRIBUTE13,
998 INDUSTRY_ATTRIBUTE12,
999 INDUSTRY_ATTRIBUTE14,
1000 INDUSTRY_ATTRIBUTE15,
1001 INDUSTRY_ATTRIBUTE16,
1002 INDUSTRY_ATTRIBUTE17,
1003 INDUSTRY_ATTRIBUTE18,
1004 INDUSTRY_ATTRIBUTE19,
1005 INDUSTRY_ATTRIBUTE20,
1006 INDUSTRY_ATTRIBUTE21,
1007 INDUSTRY_ATTRIBUTE22,
1008 INDUSTRY_ATTRIBUTE23,
1009 INDUSTRY_ATTRIBUTE24,
1010 INDUSTRY_ATTRIBUTE25,
1011 INDUSTRY_ATTRIBUTE26,
1012 INDUSTRY_ATTRIBUTE27,
1013 INDUSTRY_ATTRIBUTE28,
1014 INDUSTRY_ATTRIBUTE29,
1015 INDUSTRY_ATTRIBUTE30,
1016 CREATION_DATE,
1017 CREATED_BY,
1018 LAST_UPDATE_DATE,
1019 LAST_UPDATED_BY,
1020 LAST_UPDATE_LOGIN,
1021 PROGRAM_APPLICATION_ID,
1022 PROGRAM_ID,
1023 PROGRAM_UPDATE_DATE,
1024 REQUEST_ID,
1025 CONFIGURATION_ID,
1026 LINK_TO_LINE_ID,
1027 COMPONENT_SEQUENCE_ID,
1028 COMPONENT_CODE,
1029 CONFIG_DISPLAY_SEQUENCE,
1030 SORT_ORDER,
1031 ITEM_TYPE_CODE,
1032 OPTION_NUMBER,
1033 OPTION_FLAG,
1034 DEP_PLAN_REQUIRED_FLAG,
1035 VISIBLE_DEMAND_FLAG,
1036 LINE_CATEGORY_CODE,
1037 ACTUAL_SHIPMENT_DATE,
1038 CUSTOMER_TRX_LINE_ID,
1039 RETURN_CONTEXT,
1040 RETURN_ATTRIBUTE1,
1041 RETURN_ATTRIBUTE2,
1042 RETURN_ATTRIBUTE3,
1043 RETURN_ATTRIBUTE4,
1044 RETURN_ATTRIBUTE5,
1045 RETURN_ATTRIBUTE6,
1046 RETURN_ATTRIBUTE7,
1047 RETURN_ATTRIBUTE8,
1048 RETURN_ATTRIBUTE9,
1049 RETURN_ATTRIBUTE10,
1050 RETURN_ATTRIBUTE11,
1051 RETURN_ATTRIBUTE12,
1052 RETURN_ATTRIBUTE13,
1053 RETURN_ATTRIBUTE14,
1054 RETURN_ATTRIBUTE15,
1055 INTMED_SHIP_TO_ORG_ID,
1056 INTMED_SHIP_TO_CONTACT_ID,
1057 ACTUAL_ARRIVAL_DATE,
1058 ATO_LINE_ID,
1059 AUTO_SELECTED_QUANTITY,
1060 COMPONENT_NUMBER,
1061 EARLIEST_ACCEPTABLE_DATE,
1062 EXPLOSION_DATE,
1063 LATEST_ACCEPTABLE_DATE,
1064 MODEL_GROUP_NUMBER,
1065 SCHEDULE_ARRIVAL_DATE,
1066 SHIP_MODEL_COMPLETE_FLAG,
1067 SCHEDULE_STATUS_CODE,
1068 SOURCE_TYPE_CODE,
1069 TOP_MODEL_LINE_ID,
1070 BOOKED_FLAG,
1071 CANCELLED_FLAG,
1072 OPEN_FLAG,
1073 NULL, --LOAD_SEQ_NUMBER,
1074 NULL, --DELIVERY_ID,
1075 SALESREP_ID,
1076 RETURN_REASON_CODE,
1077 NULL, --WF_ACTIVITY_CODE,
1078 NULL, --WF_RESULT_CODE,
1079 NULL, --REASON_CODE,
1080 NULL, --HIST_COMMENTS,
1081 'VERSIONING', --HIST_TYPE_CODE,
1082 sysdate, --HIST_CREATION_DATE,
1083 nvl(FND_GLOBAL.USER_ID, -1), -- HIST_CREATED_BY,
1084 CUST_PRODUCTION_SEQ_NUM,
1085 AUTHORIZED_TO_SHIP_FLAG,
1086 SPLIT_FROM_LINE_ID,
1087 OVER_SHIP_REASON_CODE,
1088 OVER_SHIP_RESOLVED_FLAG,
1089 ITEM_IDENTIFIER_TYPE,
1090 ARRIVAL_SET_ID,
1091 SHIP_SET_ID,
1092 COMMITMENT_ID,
1093 SHIPPING_INTERFACED_FLAG,
1094 CREDIT_INVOICE_LINE_ID,
1095 MFG_COMPONENT_SEQUENCE_ID,
1096 TP_CONTEXT,
1097 TP_ATTRIBUTE1,
1098 TP_ATTRIBUTE2,
1099 TP_ATTRIBUTE3,
1100 TP_ATTRIBUTE4,
1101 TP_ATTRIBUTE5,
1102 TP_ATTRIBUTE6,
1103 TP_ATTRIBUTE7,
1104 TP_ATTRIBUTE8,
1105 TP_ATTRIBUTE9,
1106 TP_ATTRIBUTE10,
1107 TP_ATTRIBUTE11,
1108 TP_ATTRIBUTE12,
1109 TP_ATTRIBUTE13,
1110 TP_ATTRIBUTE14,
1111 TP_ATTRIBUTE15,
1112 FULFILLMENT_METHOD_CODE,
1113 SERVICE_REFERENCE_TYPE_CODE,
1114 SERVICE_REFERENCE_LINE_ID,
1115 SERVICE_REFERENCE_SYSTEM_ID,
1116 INVOICE_INTERFACE_STATUS_CODE,
1117 ORDERED_ITEM,
1118 ORDERED_ITEM_ID,
1119 SERVICE_NUMBER,
1120 NULL, -- SERVICED_LINE_ID,
1121 SERVICE_DURATION,
1122 SERVICE_START_DATE,
1123 RE_SOURCE_FLAG,
1124 FLOW_STATUS_CODE,
1125 SERVICE_END_DATE,
1126 SERVICE_COTERMINATE_FLAG,
1127 SHIPPABLE_FLAG,
1128 ORDER_SOURCE_ID,
1129 ORIG_SYS_SHIPMENT_REF,
1130 CHANGE_SEQUENCE,
1131 DROP_SHIP_FLAG,
1132 CUSTOMER_LINE_NUMBER,
1133 CUSTOMER_SHIPMENT_NUMBER,
1134 CUSTOMER_ITEM_NET_PRICE,
1135 CUSTOMER_PAYMENT_TERM_ID,
1136 FIRST_ACK_DATE,
1137 FIRST_ACK_CODE,
1138 LAST_ACK_CODE,
1139 LAST_ACK_DATE,
1140 PLANNING_PRIORITY,
1141 SERVICE_TXN_COMMENTS,
1142 SERVICE_PERIOD,
1143 UNIT_SELLING_PERCENT,
1144 UNIT_LIST_PERCENT,
1145 UNIT_PERCENT_BASE_PRICE,
1146 MODEL_REMNANT_FLAG,
1147 SERVICE_TXN_REASON_CODE,
1148 CALCULATE_PRICE_FLAG,
1149 NULL, -- ITEM_INPUT,
1150 NULL, -- CUSTOMER_ITEM_ID,
1151 NULL, -- CUSTOMER_ITEM_REVISION,
1152 NULL, -- ITEM_ID ?? check the source
1153 NULL, -- INVOICE_COMPLETE_FLAG ?? check the source
1154 END_ITEM_UNIT_NUMBER,
1155 FULFILLED_FLAG,
1156 CONFIG_HEADER_ID,
1157 CONFIG_REV_NBR,
1158 SHIPPING_INSTRUCTIONS,
1159 PACKING_INSTRUCTIONS,
1160 INVOICED_QUANTITY,
1161 REFERENCE_CUSTOMER_TRX_LINE_ID,
1162 SPLIT_BY,
1163 LINE_SET_ID,
1164 REVENUE_AMOUNT,
1165 FULFILLMENT_DATE,
1166 PREFERRED_GRADE,
1167 ORDERED_QUANTITY2,
1168 ORDERED_QUANTITY_UOM2,
1169 SHIPPED_QUANTITY2,
1170 CANCELLED_QUANTITY2,
1171 SHIPPING_QUANTITY2,
1172 SHIPPING_QUANTITY_UOM2,
1173 FULFILLED_QUANTITY2,
1174 SUBINVENTORY,
1175 nvl(FND_GLOBAL.RESP_ID, -1), --RESPONSIBILITY_ID
1176 UNIT_LIST_PRICE_PER_PQTY,
1177 UNIT_SELLING_PRICE_PER_PQTY,
1178 NULL, -- LATEST_CANCELLED_QUANTITY
1179 PRICE_REQUEST_CODE,
1180 LATE_DEMAND_PENALTY_FACTOR,
1181 OVERRIDE_ATP_DATE_CODE,
1182 ITEM_SUBSTITUTION_TYPE_CODE,
1183 ORIGINAL_ITEM_IDENTIFIER_TYPE,
1184 ORIGINAL_ORDERED_ITEM,
1185 ORIGINAL_ORDERED_ITEM_ID,
1186 ORIGINAL_INVENTORY_ITEM_ID,
1187 ACCOUNTING_RULE_DURATION,
1188 ATTRIBUTE16,
1189 ATTRIBUTE17,
1190 ATTRIBUTE18,
1191 ATTRIBUTE19,
1192 ATTRIBUTE20,
1193 USER_ITEM_DESCRIPTION,
1194 ITEM_RELATIONSHIP_TYPE,
1195 BLANKET_NUMBER,
1196 BLANKET_LINE_NUMBER,
1197 BLANKET_VERSION_NUMBER,
1198 l_version_number, -- VERSION_NUMBER,
1199 SALES_DOCUMENT_TYPE_CODE,
1200 'N', -- AUDIT_FLAG
1201 l_version_flag, -- VERSION_FLAG
1202 p_phase_change_flag, --PHASE_CHANGE_FLAG,
1203 TRANSACTION_PHASE_CODE,
1204 SOURCE_DOCUMENT_VERSION_NUMBER,
1205 --key Transaction Dates
1206 order_firmed_date,
1207 CHARGE_PERIODICITY_CODE,
1208 actual_fulfillment_date
1209 FROM OE_ORDER_LINES_ALL a
1210 WHERE header_id = p_header_id
1211 AND NOT EXISTS (select b.line_id
1212 FROM oe_order_lines_history b
1213 WHERE a.line_id = b.line_id
1214 AND a.header_id = b.header_id
1215 AND b.version_number = l_version_number
1216 AND b.version_flag = l_version_flag);
1217 IF l_debug_level > 0 THEN
1218 oe_debug_pub.add(' No. of History Records for Lines ' ||SQL%ROWCOUNT);
1219 END IF;
1220
1221 -- Create History Records for Adjustments
1222 l_api_name := 'Create_Price_Adjs_History';
1223
1224 INSERT INTO OE_PRICE_ADJS_HISTORY(
1225 PRICE_ADJUSTMENT_ID,
1226 HEADER_ID,
1227 HIST_CREATION_DATE,
1228 CREATION_DATE,
1229 CREATED_BY,
1230 LAST_UPDATE_DATE,
1231 LAST_UPDATED_BY,
1232 LAST_UPDATE_LOGIN,
1233 PROGRAM_APPLICATION_ID,
1234 PROGRAM_ID,
1235 PROGRAM_UPDATE_DATE,
1236 REQUEST_ID,
1237 DISCOUNT_ID,
1238 DISCOUNT_LINE_ID,
1239 AUTOMATIC_FLAG,
1240 PERCENT,
1241 LINE_ID,
1242 CONTEXT,
1243 ATTRIBUTE1,
1244 ATTRIBUTE2,
1245 ATTRIBUTE3,
1246 ATTRIBUTE4,
1247 ATTRIBUTE5,
1248 ATTRIBUTE6,
1249 ATTRIBUTE7,
1250 ATTRIBUTE8,
1251 ATTRIBUTE9,
1252 ATTRIBUTE10,
1253 ATTRIBUTE11,
1254 ATTRIBUTE12,
1255 ATTRIBUTE13,
1256 ATTRIBUTE14,
1257 ATTRIBUTE15,
1258 ORIG_SYS_DISCOUNT_REF,
1259 CHANGE_SEQUENCE,
1260 LIST_HEADER_ID,
1261 LIST_LINE_ID,
1262 LIST_LINE_TYPE_CODE,
1263 MODIFIED_FROM,
1264 MODIFIED_TO,
1265 UPDATE_ALLOWED,
1266 CHANGE_REASON_CODE,
1267 CHANGE_REASON_TEXT,
1268 MODIFIER_MECHANISM_TYPE_CODE,
1269 UPDATED_FLAG,
1270 APPLIED_FLAG,
1271 OPERAND,
1272 ARITHMETIC_OPERATOR,
1273 COST_ID,
1274 TAX_CODE,
1275 TAX_EXEMPT_FLAG,
1276 TAX_EXEMPT_NUMBER,
1277 TAX_EXEMPT_REASON_CODE,
1278 PARENT_ADJUSTMENT_ID,
1279 INVOICED_FLAG,
1280 ESTIMATED_FLAG,
1281 INC_IN_SALES_PERFORMANCE,
1282 SPLIT_ACTION_CODE,
1283 ADJUSTED_AMOUNT,
1284 PRICING_PHASE_ID,
1285 CHARGE_TYPE_CODE,
1286 CHARGE_SUBTYPE_CODE,
1287 RANGE_BREAK_QUANTITY,
1288 ACCRUAL_CONVERSION_RATE,
1289 PRICING_GROUP_SEQUENCE,
1290 ACCRUAL_FLAG,
1291 LIST_LINE_NO,
1292 SOURCE_SYSTEM_CODE,
1293 BENEFIT_QTY,
1294 BENEFIT_UOM_CODE,
1295 PRINT_ON_INVOICE_FLAG,
1296 EXPIRATION_DATE,
1297 REBATE_TRANSACTION_TYPE_CODE,
1298 REBATE_TRANSACTION_REFERENCE,
1299 REBATE_PAYMENT_SYSTEM_CODE,
1300 REDEEMED_DATE,
1301 REDEEMED_FLAG,
1302 MODIFIER_LEVEL_CODE,
1303 PRICE_BREAK_TYPE_CODE,
1304 SUBSTITUTION_ATTRIBUTE,
1305 PRORATION_TYPE_CODE,
1306 INCLUDE_ON_RETURNS_FLAG,
1307 CREDIT_OR_CHARGE_FLAG,
1308 AC_CONTEXT,
1309 AC_ATTRIBUTE1,
1310 AC_ATTRIBUTE2,
1311 AC_ATTRIBUTE3,
1312 AC_ATTRIBUTE4,
1313 AC_ATTRIBUTE5,
1314 AC_ATTRIBUTE6,
1315 AC_ATTRIBUTE7,
1316 AC_ATTRIBUTE8,
1317 AC_ATTRIBUTE9,
1318 AC_ATTRIBUTE10,
1319 AC_ATTRIBUTE11,
1320 AC_ATTRIBUTE12,
1321 AC_ATTRIBUTE13,
1322 AC_ATTRIBUTE14,
1323 AC_ATTRIBUTE15,
1324 LOCK_CONTROL,
1325 HIST_TYPE_CODE,
1326 HIST_CREATED_BY,
1327 RESPONSIBILITY_ID,
1328 VERSION_NUMBER,
1329 AUDIT_FLAG,
1330 VERSION_FLAG,
1331 PHASE_CHANGE_FLAG
1332 )
1333 SELECT
1334 PRICE_ADJUSTMENT_ID,
1335 HEADER_ID,
1336 sysdate,
1337 CREATION_DATE,
1338 CREATED_BY,
1339 LAST_UPDATE_DATE,
1340 LAST_UPDATED_BY,
1341 LAST_UPDATE_LOGIN,
1342 PROGRAM_APPLICATION_ID,
1343 PROGRAM_ID,
1344 PROGRAM_UPDATE_DATE,
1345 REQUEST_ID,
1346 DISCOUNT_ID,
1347 DISCOUNT_LINE_ID,
1348 AUTOMATIC_FLAG,
1349 PERCENT,
1350 LINE_ID,
1351 CONTEXT,
1352 ATTRIBUTE1,
1353 ATTRIBUTE2,
1354 ATTRIBUTE3,
1355 ATTRIBUTE4,
1356 ATTRIBUTE5,
1357 ATTRIBUTE6,
1358 ATTRIBUTE7,
1359 ATTRIBUTE8,
1360 ATTRIBUTE9,
1361 ATTRIBUTE10,
1362 ATTRIBUTE11,
1363 ATTRIBUTE12,
1364 ATTRIBUTE13,
1365 ATTRIBUTE14,
1366 ATTRIBUTE15,
1367 ORIG_SYS_DISCOUNT_REF,
1368 CHANGE_SEQUENCE,
1369 LIST_HEADER_ID,
1370 LIST_LINE_ID,
1371 LIST_LINE_TYPE_CODE,
1372 MODIFIED_FROM,
1373 MODIFIED_TO,
1374 UPDATE_ALLOWED,
1375 CHANGE_REASON_CODE,
1376 CHANGE_REASON_TEXT,
1377 MODIFIER_MECHANISM_TYPE_CODE,
1378 UPDATED_FLAG,
1379 APPLIED_FLAG,
1380 OPERAND,
1381 ARITHMETIC_OPERATOR,
1382 COST_ID,
1383 TAX_CODE,
1384 TAX_EXEMPT_FLAG,
1385 TAX_EXEMPT_NUMBER,
1386 TAX_EXEMPT_REASON_CODE,
1387 PARENT_ADJUSTMENT_ID,
1388 INVOICED_FLAG,
1389 ESTIMATED_FLAG,
1390 INC_IN_SALES_PERFORMANCE,
1391 SPLIT_ACTION_CODE,
1392 ADJUSTED_AMOUNT,
1393 PRICING_PHASE_ID,
1394 CHARGE_TYPE_CODE,
1395 CHARGE_SUBTYPE_CODE,
1396 RANGE_BREAK_QUANTITY,
1397 ACCRUAL_CONVERSION_RATE,
1398 PRICING_GROUP_SEQUENCE,
1399 ACCRUAL_FLAG,
1400 LIST_LINE_NO,
1401 SOURCE_SYSTEM_CODE,
1402 BENEFIT_QTY,
1403 BENEFIT_UOM_CODE,
1404 PRINT_ON_INVOICE_FLAG,
1405 EXPIRATION_DATE,
1406 REBATE_TRANSACTION_TYPE_CODE,
1407 REBATE_TRANSACTION_REFERENCE,
1408 REBATE_PAYMENT_SYSTEM_CODE,
1409 REDEEMED_DATE,
1410 REDEEMED_FLAG,
1411 MODIFIER_LEVEL_CODE,
1412 PRICE_BREAK_TYPE_CODE,
1413 SUBSTITUTION_ATTRIBUTE,
1414 PRORATION_TYPE_CODE,
1415 INCLUDE_ON_RETURNS_FLAG,
1416 CREDIT_OR_CHARGE_FLAG,
1417 AC_CONTEXT,
1418 AC_ATTRIBUTE1,
1419 AC_ATTRIBUTE2,
1420 AC_ATTRIBUTE3,
1421 AC_ATTRIBUTE4,
1422 AC_ATTRIBUTE5,
1423 AC_ATTRIBUTE6,
1424 AC_ATTRIBUTE7,
1425 AC_ATTRIBUTE8,
1426 AC_ATTRIBUTE9,
1427 AC_ATTRIBUTE10,
1428 AC_ATTRIBUTE11,
1429 AC_ATTRIBUTE12,
1430 AC_ATTRIBUTE13,
1431 AC_ATTRIBUTE14,
1432 AC_ATTRIBUTE15,
1433 LOCK_CONTROL,
1434 NULL, --HIST_TYPE_CODE,
1435 nvl(FND_GLOBAL.USER_ID, -1), --HIST_CREATED_BY,
1436 nvl(FND_GLOBAL.RESP_ID, -1), --RESPONSIBILITY_ID,
1437 l_version_number, --VERSION_NUMBER,
1438 'N', --AUDIT_FLAG,
1439 l_version_flag, --VERSION_FLAG,
1440 p_phase_change_flag --PHASE_CHANGE_FLAG
1441 FROM OE_PRICE_ADJUSTMENTS a
1442 WHERE a.header_id = p_header_id
1443 AND NOT EXISTS ( SELECT price_adjustment_id
1444 FROM OE_PRICE_ADJS_HISTORY b
1445 WHERE b.price_adjustment_id = a.price_adjustment_id
1446 AND a.header_id = b.header_id
1447 AND b.version_number = l_version_number
1448 AND b.version_flag = l_version_flag);
1449
1450 IF l_debug_level > 0 THEN
1451 oe_debug_pub.add(' No. of History Records for PrcAdjs ' ||SQL%ROWCOUNT);
1452 END IF;
1453
1454 -- Create History Records for SalesCredits
1455 l_api_name := 'Create_Sales_Credits_History';
1456
1457 INSERT INTO OE_SALES_CREDIT_HISTORY (
1458 SALES_CREDIT_ID,
1459 HEADER_ID,
1460 HIST_CREATION_DATE,
1461 CREATION_DATE,
1462 CREATED_BY,
1463 LAST_UPDATE_DATE,
1464 LAST_UPDATED_BY,
1465 LAST_UPDATE_LOGIN,
1466 SALESREP_ID,
1467 PERCENT,
1468 LINE_ID,
1469 CONTEXT,
1470 ATTRIBUTE1,
1471 ATTRIBUTE2,
1472 ATTRIBUTE3,
1473 ATTRIBUTE4,
1474 ATTRIBUTE5,
1475 ATTRIBUTE6,
1476 ATTRIBUTE7,
1477 ATTRIBUTE8,
1478 ATTRIBUTE9,
1479 ATTRIBUTE10,
1480 ATTRIBUTE11,
1481 ATTRIBUTE12,
1482 ATTRIBUTE13,
1483 ATTRIBUTE14,
1484 ATTRIBUTE15,
1485 DW_UPDATE_ADVICE_FLAG,
1486 WH_UPDATE_DATE,
1487 ORIG_SYS_CREDIT_REF,
1488 SALES_CREDIT_TYPE_ID,
1489 LOCK_CONTROL,
1490 REASON_CODE,
1491 HIST_COMMENTS,
1492 HIST_TYPE_CODE,
1493 HIST_CREATED_BY,
1494 RESPONSIBILITY_ID,
1495 VERSION_NUMBER,
1496 AUDIT_FLAG,
1497 VERSION_FLAG,
1498 PHASE_CHANGE_FLAG
1499 )
1500 SELECT
1501 SALES_CREDIT_ID,
1502 HEADER_ID,
1503 sysdate,
1504 CREATION_DATE,
1505 CREATED_BY,
1506 LAST_UPDATE_DATE,
1507 LAST_UPDATED_BY,
1508 LAST_UPDATE_LOGIN,
1509 SALESREP_ID,
1510 PERCENT,
1511 LINE_ID,
1512 CONTEXT,
1513 ATTRIBUTE1,
1514 ATTRIBUTE2,
1515 ATTRIBUTE3,
1516 ATTRIBUTE4,
1517 ATTRIBUTE5,
1518 ATTRIBUTE6,
1519 ATTRIBUTE7,
1520 ATTRIBUTE8,
1521 ATTRIBUTE9,
1522 ATTRIBUTE10,
1523 ATTRIBUTE11,
1524 ATTRIBUTE12,
1525 ATTRIBUTE13,
1526 ATTRIBUTE14,
1527 ATTRIBUTE15,
1528 DW_UPDATE_ADVICE_FLAG,
1529 WH_UPDATE_DATE,
1530 ORIG_SYS_CREDIT_REF,
1531 SALES_CREDIT_TYPE_ID,
1532 LOCK_CONTROL,
1533 NULL, --REASON_CODE,
1534 NULL, --HIST_COMMENTS,
1535 NULL, --HIST_TYPE_CODE,
1536 nvl(FND_GLOBAL.USER_ID, -1), --HIST_CREATED_BY,
1537 nvl(FND_GLOBAL.RESP_ID, -1), --RESPONSIBILITY_ID,
1538 l_version_number, --VERSION_NUMBER,
1539 'N', --AUDIT_FLAG,
1540 l_version_flag, --VERSION_FLAG,
1541 p_phase_change_flag --PHASE_CHANGE_FLAG,
1542 FROM oe_sales_credits a
1543 WHERE a.header_id = p_header_id
1544 AND NOT EXISTS ( SELECT b.SALES_CREDIT_ID
1545 FROM OE_SALES_CREDIT_HISTORY b
1546 WHERE b.SALES_CREDIT_ID = a.SALES_CREDIT_ID
1547 AND a.header_id = b.header_id
1548 AND b.VERSION_NUMBER = l_version_number
1549 AND b.version_flag = l_version_flag);
1550
1551 IF l_debug_level > 0 THEN
1552 oe_debug_pub.add(' No. of History Records for Sales Credits '||SQL%ROWCOUNT);
1553 END IF;
1554
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557
1558 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1559 THEN
1560 OE_MSG_PUB.Add_Exc_Msg
1561 ( G_PKG_NAME
1562 , l_api_name
1563 );
1564 END IF;
1565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1566
1567 END Create_Version_History;
1568
1569
1570 Function Get_Version_Number
1571 (p_header_id IN NUMBER := NULL,
1572 p_line_id IN NUMBER := NULL,
1573 p_price_adjustment_id IN NUMBER := NULL,
1574 p_sales_credit_id IN NUMBER := NULL) RETURN NUMBER
1575 IS
1576
1577 l_current_version_number NUMBER := NULL;
1578 BEGIN
1579
1580 IF p_price_adjustment_id <> FND_API.G_MISS_NUM THEN
1581
1582 BEGIN
1583 SELECT h.version_number INTO l_current_version_number
1584 FROM OE_ORDER_HEADERS_ALL h, OE_PRICE_ADJUSTMENTS p
1585 WHERE h.header_id = p.header_id
1586 AND p.price_adjustment_id = p_price_adjustment_id;
1587 EXCEPTION WHEN NO_DATA_FOUND THEN
1588 SELECT h.version_number INTO l_current_version_number
1589 FROM OE_ORDER_HEADERS_ALL h, OE_PRICE_ADJS_HISTORY p
1590 WHERE h.header_id = p.header_id
1591 AND p.price_adjustment_id = p_price_adjustment_id
1592 AND rownum = 1;
1593 END;
1594
1595 ELSIF p_sales_credit_id <> FND_API.G_MISS_NUM THEN
1596
1597 BEGIN
1598 SELECT h.version_number INTO l_current_version_number
1599 FROM OE_ORDER_HEADERS_ALL h, OE_SALES_CREDITS s
1600 WHERE h.header_id = s.header_id
1601 AND s.sales_credit_id = p_sales_credit_id;
1602 EXCEPTION WHEN NO_DATA_FOUND THEN
1603 SELECT h.version_number INTO l_current_version_number
1604 FROM OE_ORDER_HEADERS_ALL h, OE_SALES_CREDIT_HISTORY s
1605 WHERE h.header_id = s.header_id
1606 AND s.sales_credit_id = p_sales_credit_id
1607 AND rownum = 1;
1608 END;
1609 ELSIF p_line_id <> FND_API.G_MISS_NUM THEN
1610
1611 BEGIN
1612 SELECT h.version_number INTO l_current_version_number
1613 FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL l
1614 WHERE h.header_id = l.header_id
1615 AND l.line_id = p_line_id;
1616 EXCEPTION WHEN NO_DATA_FOUND THEN
1617 SELECT h.version_number INTO l_current_version_number
1618 FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_HISTORY l
1619 WHERE h.header_id = l.header_id
1620 AND l.line_id = p_line_id
1621 AND rownum = 1;
1622 END;
1623
1624 ELSIF p_header_id <> FND_API.G_MISS_NUM THEN
1625
1626 SELECT version_number INTO l_current_version_number
1627 FROM OE_ORDER_HEADERS_ALL WHERE header_id = p_header_id;
1628
1629 END IF;
1630
1631 RETURN l_current_version_number;
1632
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1636 OE_MSG_PUB.Add_Exc_Msg
1637 ( G_PKG_NAME
1638 , 'Get_Version_Number'
1639 );
1640 END IF;
1641 END Get_Version_Number;
1642
1643 -- Moving Query_Rows from Versioning Util to Version History Util
1644 -- to reduce overall size of Versioning Util
1645
1646 Procedure Get_Transaction_Version(
1647 p_header_id IN NUMBER,
1648 p_version_number IN NUMBER := NULL,
1649 p_phase_change_flag IN VARCHAR2 := NULL,
1650 x_header_rec OUT NOCOPY OE_ORDER_PUB.Header_Rec_Type,
1651 x_header_adj_tbl OUT NOCOPY OE_ORDER_PUB.Header_Adj_Tbl_Type,
1652 x_header_scredit_tbl OUT NOCOPY OE_ORDER_PUB.Header_Scredit_Tbl_Type,
1653 x_line_tbl OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
1654 x_line_adj_tbl OUT NOCOPY OE_ORDER_PUB.Line_Adj_Tbl_Type,
1655 x_line_scredit_tbl OUT NOCOPY OE_ORDER_PUB.Line_Scredit_Tbl_Type,
1656 x_return_status OUT NOCOPY VARCHAR2)
1657 IS
1658 l_version_number NUMBER := p_version_number;
1659 l_current_version_number NUMBER;
1660
1661 BEGIN
1662
1663 IF p_version_number IS NOT NULL THEN
1664 l_current_version_number := Get_Version_Number(p_header_id => p_header_id);
1665
1666 IF l_version_number >= l_current_version_number THEN
1667 l_version_number := NULL;
1668 END IF;
1669 END IF;
1670
1671 G_INTERNAL_QUERY := TRUE;
1672
1673 Query_Row(p_header_id => p_header_id,
1674 p_version_number => l_version_number,
1675 p_phase_change_flag => p_phase_change_flag,
1676 x_header_rec => x_header_rec);
1677
1678 Query_Rows(p_header_id => p_header_id,
1679 p_version_number => l_version_number,
1680 p_phase_change_flag => p_phase_change_flag,
1681 x_header_adj_tbl => x_header_adj_tbl);
1682
1683 Query_Rows(p_header_id => p_header_id,
1684 p_version_number => l_version_number,
1685 p_phase_change_flag => p_phase_change_flag,
1686 x_header_scredit_tbl => x_header_scredit_tbl);
1687
1688 Query_Rows(p_header_id => p_header_id,
1689 p_version_number => l_version_number,
1690 p_phase_change_flag => p_phase_change_flag,
1691 x_line_tbl => x_line_tbl);
1692
1693 Query_Rows(p_header_id => p_header_id,
1694 p_version_number => l_version_number,
1695 p_phase_change_flag => p_phase_change_flag,
1696 x_line_adj_tbl => x_line_adj_tbl);
1697
1698 Query_Rows(p_header_id => p_header_id,
1699 p_version_number => l_version_number,
1700 p_phase_change_flag => p_phase_change_flag,
1701 x_line_scredit_tbl => x_line_scredit_tbl);
1702
1703 G_INTERNAL_QUERY := FALSE;
1704
1705 EXCEPTION
1706 WHEN NO_DATA_FOUND THEN
1707 G_INTERNAL_QUERY := FALSE;
1708 RAISE FND_API.G_EXC_ERROR;
1709
1710 WHEN OTHERS THEN
1711 G_INTERNAL_QUERY := FALSE;
1712
1713 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1714 OE_MSG_PUB.Add_Exc_Msg
1715 ( G_PKG_NAME
1716 , 'Get_Transaction_Version'
1717 );
1718 END IF;
1719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720 END Get_Transaction_Version;
1721
1722 -- Query_Rows with version_number to query from history tables
1723 -- Header Query_Row
1724 PROCEDURE Query_Row
1725 ( p_header_id IN NUMBER,
1726 p_version_number IN NUMBER := NULL,
1727 p_phase_change_flag IN VARCHAR2 := NULL,
1728 x_header_rec IN OUT NOCOPY OE_Order_PUB.Header_Rec_Type
1729 )
1730 IS
1731 l_version_number NUMBER := p_version_number;
1732 l_current_version_number NUMBER;
1733 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1734
1735 BEGIN
1736 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
1737 l_current_version_number := Get_Version_Number(p_header_id => p_header_id);
1738
1739 IF l_version_number >= l_current_version_number THEN
1740 l_version_number := NULL;
1741 END IF;
1742 END IF;
1743
1744 IF l_version_number IS NULL THEN
1745 OE_Header_Util.Query_Row(p_header_id => p_header_id, x_header_rec => x_header_rec);
1746 ELSE
1747
1748 oe_debug_pub.add('Entering OE_VERSIONING_UTIL.QUERY_ROW for header', 1);
1749
1750 SELECT ACCOUNTING_RULE_ID
1751 , ACCOUNTING_RULE_DURATION
1752 , AGREEMENT_ID
1753 , ATTRIBUTE1
1754 , ATTRIBUTE10
1755 , ATTRIBUTE11
1756 , ATTRIBUTE12
1757 , ATTRIBUTE13
1758 , ATTRIBUTE14
1759 , ATTRIBUTE15
1760 , ATTRIBUTE16 --For bug 2184255
1761 , ATTRIBUTE17
1762 , ATTRIBUTE18
1763 , ATTRIBUTE19
1764 , ATTRIBUTE2
1765 , ATTRIBUTE20
1766 , ATTRIBUTE3
1767 , ATTRIBUTE4
1768 , ATTRIBUTE5
1769 , ATTRIBUTE6
1770 , ATTRIBUTE7
1771 , ATTRIBUTE8
1772 , ATTRIBUTE9
1773 , BLANKET_NUMBER
1774 , BOOKED_FLAG
1775 , BOOKED_DATE
1776 , CANCELLED_FLAG
1777 , CONTEXT
1778 , CONVERSION_RATE
1779 , CONVERSION_RATE_DATE
1780 , CONVERSION_TYPE_CODE
1781 , CUSTOMER_PREFERENCE_SET_CODE
1782 , CREATED_BY
1783 , CREATION_DATE
1784 , CUST_PO_NUMBER
1785 , DELIVER_TO_CONTACT_ID
1786 , DELIVER_TO_ORG_ID
1787 , DEMAND_CLASS_CODE
1788 , FIRST_ACK_CODE
1789 , FIRST_ACK_DATE
1790 , EXPIRATION_DATE
1791 , EARLIEST_SCHEDULE_LIMIT
1792 , FOB_POINT_CODE
1793 , FREIGHT_CARRIER_CODE
1794 , FREIGHT_TERMS_CODE
1795 , GLOBAL_ATTRIBUTE1
1796 , GLOBAL_ATTRIBUTE10
1797 , GLOBAL_ATTRIBUTE11
1798 , GLOBAL_ATTRIBUTE12
1799 , GLOBAL_ATTRIBUTE13
1800 , GLOBAL_ATTRIBUTE14
1801 , GLOBAL_ATTRIBUTE15
1802 , GLOBAL_ATTRIBUTE16
1803 , GLOBAL_ATTRIBUTE17
1804 , GLOBAL_ATTRIBUTE18
1805 , GLOBAL_ATTRIBUTE19
1806 , GLOBAL_ATTRIBUTE2
1807 , GLOBAL_ATTRIBUTE20
1808 , GLOBAL_ATTRIBUTE3
1809 , GLOBAL_ATTRIBUTE4
1810 , GLOBAL_ATTRIBUTE5
1811 , GLOBAL_ATTRIBUTE6
1812 , GLOBAL_ATTRIBUTE7
1813 , GLOBAL_ATTRIBUTE8
1814 , GLOBAL_ATTRIBUTE9
1815 , GLOBAL_ATTRIBUTE_CATEGORY
1816 , HEADER_ID
1817 , INVOICE_TO_CONTACT_ID
1818 , INVOICE_TO_ORG_ID
1819 , INVOICING_RULE_ID
1820 , LAST_ACK_CODE
1821 , LAST_ACK_DATE
1822 , LAST_UPDATED_BY
1823 , LAST_UPDATE_DATE
1824 , LAST_UPDATE_LOGIN
1825 , LATEST_SCHEDULE_LIMIT
1826 , OPEN_FLAG
1827 , ORDERED_DATE
1828 , ORDER_DATE_TYPE_CODE
1829 , ORDER_NUMBER
1830 , ORDER_SOURCE_ID
1831 , ORDER_TYPE_ID
1832 , ORDER_CATEGORY_CODE
1833 , ORG_ID
1834 , ORIG_SYS_DOCUMENT_REF
1835 , PARTIAL_SHIPMENTS_ALLOWED
1836 , PAYMENT_TERM_ID
1837 , PRICE_LIST_ID
1838 , PRICE_REQUEST_CODE -- PROMOTIONS SEP/01
1839 , PRICING_DATE
1840 , PROGRAM_APPLICATION_ID
1841 , PROGRAM_ID
1842 , PROGRAM_UPDATE_DATE
1843 , REQUEST_DATE
1844 , REQUEST_ID
1845 , RETURN_REASON_CODE
1846 , SALESREP_ID
1847 , SALES_CHANNEL_CODe
1848 , SHIPMENT_PRIORITY_CODE
1849 , SHIPPING_METHOD_CODE
1850 , SHIP_FROM_ORG_ID
1851 , SHIP_TOLERANCE_ABOVE
1852 , SHIP_TOLERANCE_BELOW
1853 , SHIP_TO_CONTACT_ID
1854 , SHIP_TO_ORG_ID
1855 , SOLD_FROM_ORG_ID
1856 , SOLD_TO_CONTACT_ID
1857 , SOLD_TO_ORG_ID
1858 , SOLD_TO_PHONE_ID
1859 , SOURCE_DOCUMENT_ID
1860 , SOURCE_DOCUMENT_TYPE_ID
1861 , TAX_EXEMPT_FLAG
1862 , TAX_EXEMPT_NUMBER
1863 , TAX_EXEMPT_REASON_CODE
1864 , TAX_POINT_CODE
1865 , TRANSACTIONAL_CURR_CODE
1866 , VERSION_NUMBER
1867 , PAYMENT_TYPE_CODE
1868 , PAYMENT_AMOUNT
1869 , CHECK_NUMBER
1870 , CREDIT_CARD_CODE
1871 , CREDIT_CARD_HOLDER_NAME
1872 , CREDIT_CARD_NUMBER
1873 , CREDIT_CARD_EXPIRATION_DATE
1874 , CREDIT_CARD_APPROVAL_DATE
1875 , CREDIT_CARD_APPROVAL_CODE
1876 , SHIPPING_INSTRUCTIONS
1877 , PACKING_INSTRUCTIONS
1878 , FLOW_STATUS_CODE
1879 , MARKETING_SOURCE_CODE_ID
1880 , NULL --DEFAULT_FULFILLMENT_SET
1881 , NULL --FULFILLMENT_SET_NAME
1882 , NULL --LINE_SET_NAME
1883 , TP_ATTRIBUTE1
1884 , TP_ATTRIBUTE10
1885 , TP_ATTRIBUTE11
1886 , TP_ATTRIBUTE12
1887 , TP_ATTRIBUTE13
1888 , TP_ATTRIBUTE14
1889 , TP_ATTRIBUTE15
1890 , TP_ATTRIBUTE2
1891 , TP_ATTRIBUTE3
1892 , TP_ATTRIBUTE4
1893 , TP_ATTRIBUTE5
1894 , TP_ATTRIBUTE6
1895 , TP_ATTRIBUTE7
1896 , TP_ATTRIBUTE8
1897 , TP_ATTRIBUTE9
1898 , TP_CONTEXT
1899 , NULL --XML_MESSAGE_ID
1900 , upgraded_flag
1901 , LOCK_CONTROL
1902 , quote_date
1903 , quote_number
1904 , sales_document_name
1905 , transaction_phase_code
1906 , user_status_code
1907 , draft_submitted_flag
1908 , source_document_version_number
1909 , sold_to_site_use_id
1910 --key Transaction Dates
1911 , order_firmed_date
1912 INTO x_header_rec.accounting_rule_id
1913 , x_header_rec.accounting_rule_duration
1914 , x_header_rec.agreement_id
1915 , x_header_rec.attribute1
1916 , x_header_rec.attribute10
1917 , x_header_rec.attribute11
1918 , x_header_rec.attribute12
1919 , x_header_rec.attribute13
1920 , x_header_rec.attribute14
1921 , x_header_rec.attribute15
1922 , x_header_rec.attribute16 --For bug 2184255
1923 , x_header_rec.attribute17
1924 , x_header_rec.attribute18
1925 , x_header_rec.attribute19
1926 , x_header_rec.attribute2
1927 , x_header_rec.attribute20
1928 , x_header_rec.attribute3
1929 , x_header_rec.attribute4
1930 , x_header_rec.attribute5
1931 , x_header_rec.attribute6
1932 , x_header_rec.attribute7
1933 , x_header_rec.attribute8
1934 , x_header_rec.attribute9
1935 , x_header_rec.Blanket_Number
1936 , x_header_rec.booked_flag
1937 , x_header_rec.booked_date
1938 , x_header_rec.cancelled_flag
1939 , x_header_rec.context
1940 , x_header_rec.conversion_rate
1941 , x_header_rec.conversion_rate_date
1942 , x_header_rec.conversion_type_code
1943 , x_header_rec.CUSTOMER_PREFERENCE_SET_CODE
1944 , x_header_rec.created_by
1945 , x_header_rec.creation_date
1946 , x_header_rec.cust_po_number
1947 , x_header_rec.deliver_to_contact_id
1948 , x_header_rec.deliver_to_org_id
1949 , x_header_rec.demand_class_code
1950 , x_header_rec.first_ack_code
1951 , x_header_rec.first_ack_date
1952 , x_header_rec.expiration_date
1953 , x_header_rec.earliest_schedule_limit
1954 , x_header_rec.fob_point_code
1955 , x_header_rec.freight_carrier_code
1956 , x_header_rec.freight_terms_code
1957 , x_header_rec.global_attribute1
1958 , x_header_rec.global_attribute10
1959 , x_header_rec.global_attribute11
1960 , x_header_rec.global_attribute12
1961 , x_header_rec.global_attribute13
1962 , x_header_rec.global_attribute14
1963 , x_header_rec.global_attribute15
1964 , x_header_rec.global_attribute16
1965 , x_header_rec.global_attribute17
1966 , x_header_rec.global_attribute18
1967 , x_header_rec.global_attribute19
1968 , x_header_rec.global_attribute2
1969 , x_header_rec.global_attribute20
1970 , x_header_rec.global_attribute3
1971 , x_header_rec.global_attribute4
1972 , x_header_rec.global_attribute5
1973 , x_header_rec.global_attribute6
1974 , x_header_rec.global_attribute7
1975 , x_header_rec.global_attribute8
1976 , x_header_rec.global_attribute9
1977 , x_header_rec.global_attribute_category
1978 , x_header_rec.header_id
1979 , x_header_rec.invoice_to_contact_id
1980 , x_header_rec.invoice_to_org_id
1981 , x_header_rec.invoicing_rule_id
1982 , x_header_rec.last_ack_code
1983 , x_header_rec.last_ack_date
1984 , x_header_rec.last_updated_by
1985 , x_header_rec.last_update_date
1986 , x_header_rec.last_update_login
1987 , x_header_rec.latest_schedule_limit
1988 , x_header_rec.open_flag
1989 , x_header_rec.ordered_date
1990 , x_header_rec.order_date_type_code
1991 , x_header_rec.order_number
1992 , x_header_rec.order_source_id
1993 , x_header_rec.order_type_id
1994 , x_header_rec.order_category_code
1995 , x_header_rec.org_id
1996 , x_header_rec.orig_sys_document_ref
1997 , x_header_rec.partial_shipments_allowed
1998 , x_header_rec.payment_term_id
1999 , x_header_rec.price_list_id
2000 , x_header_rec.price_request_code -- PROMOTIONS SEP/01
2001 , x_header_rec.pricing_date
2002 , x_header_rec.program_application_id
2003 , x_header_rec.program_id
2004 , x_header_rec.program_update_date
2005 , x_header_rec.request_date
2006 , x_header_rec.request_id
2007 , x_header_rec.return_reason_code
2008 , x_header_rec.salesrep_id
2009 , x_header_rec.sales_channel_code
2010 , x_header_rec.shipment_priority_code
2011 , x_header_rec.shipping_method_code
2012 , x_header_rec.ship_from_org_id
2013 , x_header_rec.ship_tolerance_above
2014 , x_header_rec.ship_tolerance_below
2015 , x_header_rec.ship_to_contact_id
2016 , x_header_rec.ship_to_org_id
2017 , x_header_rec.sold_from_org_id
2018 , x_header_rec.sold_to_contact_id
2019 , x_header_rec.sold_to_org_id
2020 , x_header_rec.sold_to_phone_id
2021 , x_header_rec.source_document_id
2022 , x_header_rec.source_document_type_id
2023 , x_header_rec.tax_exempt_flag
2024 , x_header_rec.tax_exempt_number
2025 , x_header_rec.tax_exempt_reason_code
2026 , x_header_rec.tax_point_code
2027 , x_header_rec.transactional_curr_code
2028 , x_header_rec.version_number
2029 , x_header_rec.payment_type_code
2030 , x_header_rec.payment_amount
2031 , x_header_rec.check_number
2032 , x_header_rec.credit_card_code
2033 , x_header_rec.credit_card_holder_name
2034 , x_header_rec.credit_card_number
2035 , x_header_rec.credit_card_expiration_date
2036 , x_header_rec.credit_card_approval_date
2037 , x_header_rec.credit_card_approval_code
2038 , x_header_rec.shipping_instructions
2039 , x_header_rec.packing_instructions
2040 , x_header_rec.flow_status_code
2041 , x_header_rec.marketing_source_code_id
2042 , x_header_rec.default_fulfillment_set
2043 , x_header_rec.fulfillment_set_name
2044 , x_header_rec.line_set_name
2045 , x_header_rec.tp_attribute1
2046 , x_header_rec.tp_attribute10
2047 , x_header_rec.tp_attribute11
2048 , x_header_rec.tp_attribute12
2049 , x_header_rec.tp_attribute13
2050 , x_header_rec.tp_attribute14
2051 , x_header_rec.tp_attribute15
2052 , x_header_rec.tp_attribute2
2053 , x_header_rec.tp_attribute3
2054 , x_header_rec.tp_attribute4
2055 , x_header_rec.tp_attribute5
2056 , x_header_rec.tp_attribute6
2057 , x_header_rec.tp_attribute7
2058 , x_header_rec.tp_attribute8
2059 , x_header_rec.tp_attribute9
2060 , x_header_rec.tp_context
2061 , x_header_rec.xml_message_id
2062 , x_header_rec.upgraded_flag
2063 , x_header_rec.lock_control
2064 , x_header_rec.quote_date
2065 , x_header_rec.quote_number
2066 , x_header_rec.sales_document_name
2067 , x_header_rec.transaction_phase_code
2068 , x_header_rec.user_status_code
2069 , x_header_rec.draft_submitted_flag
2070 , x_header_rec.source_document_version_number
2071 , x_header_rec.sold_to_site_use_id
2072 --key Transaction Dates
2073 , x_header_rec.order_firmed_date
2074 FROM OE_ORDER_HEADER_HISTORY
2075 WHERE HEADER_ID = p_header_id
2076 AND VERSION_NUMBER = l_version_number
2077 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
2078 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
2079 AND VERSION_FLAG = 'Y'));
2080 END IF;
2081
2082 --R12 CC Encryption
2083 --Need to call query card details procedure to get the credit card
2084 --details as the OM tables would not have these values in R12
2085 IF x_header_rec.payment_type_code IS NOT NULL AND
2086 x_header_rec.payment_type_code = 'CREDIT_CARD'
2087 AND x_header_rec.credit_card_number is null THEN
2088 if l_debug_level > 0 then
2089 oe_debug_pub.add('Calling query card details...');
2090 end if;
2091 OE_Header_Util.Query_card_details
2092 ( p_header_id => x_header_rec.header_id,
2093 p_credit_card_code => x_header_rec.credit_card_code,
2094 p_credit_card_holder_name => x_header_rec.credit_card_holder_name,
2095 p_credit_card_number => x_header_rec.credit_card_number,
2096 p_credit_Card_expiration_date => x_header_rec.credit_card_expiration_date,
2097 p_credit_card_approval_code => x_header_rec.credit_card_approval_code,
2098 p_credit_card_approval_Date => x_header_rec.credit_card_approval_date,
2099 p_instrument_security_code => x_header_rec.instrument_security_code,
2100 p_instrument_id => x_header_rec.cc_instrument_id,
2101 p_instrument_assignment_id => x_header_rec.cc_instrument_assignment_id
2102 );
2103 END IF;
2104 --R12 CC Encryption
2105
2106 EXCEPTION
2107 WHEN OTHERS THEN
2108 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2109 OE_MSG_PUB.Add_Exc_Msg
2110 ( G_PKG_NAME
2111 , 'Header Query_Row'
2112 );
2113 END IF;
2114 END;
2115
2116 -- Header Adj Query_Row
2117 PROCEDURE Query_Row
2118 ( p_price_adjustment_id IN NUMBER
2119 , p_version_number IN NUMBER := NULL
2120 , p_phase_change_flag IN VARCHAR2 := NULL
2121 , x_Header_Adj_Rec IN OUT NOCOPY OE_Order_PUB.Header_Adj_Rec_Type
2122 )
2123 IS
2124 l_Header_Adj_Tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2125
2126 l_version_number NUMBER := p_version_number;
2127 l_current_version_number NUMBER;
2128 --
2129 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2130 --
2131 BEGIN
2132 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
2133 l_current_version_number := Get_Version_Number(p_price_adjustment_id => p_price_adjustment_id);
2134
2135 IF l_version_number >= l_current_version_number THEN
2136 l_version_number := NULL;
2137 END IF;
2138 END IF;
2139
2140 IF l_version_number IS NULL THEN
2141 OE_Header_Adj_Util.Query_Row(p_price_adjustment_id => p_price_adjustment_id, x_header_adj_rec => x_header_adj_rec);
2142 ELSE
2143 IF l_debug_level > 0 THEN
2144 oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.QUERY_ROW' , 1 ) ;
2145 END IF;
2146
2147 G_INTERNAL_QUERY := TRUE;
2148 Query_Rows
2149 ( p_price_adjustment_id => p_price_adjustment_id
2150 , p_version_number => l_version_number
2151 , x_Header_Adj_Tbl => l_Header_Adj_Tbl
2152 );
2153 x_Header_Adj_Rec := l_Header_Adj_Tbl(1);
2154 G_INTERNAL_QUERY := FALSE;
2155
2156 IF l_debug_level > 0 THEN
2157 oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.QUERY_ROW' , 1 ) ;
2158 END IF;
2159
2160 END IF;
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2165 OE_MSG_PUB.Add_Exc_Msg
2166 ( G_PKG_NAME
2167 , 'Header Adj Query_Row'
2168 );
2169 END IF;
2170
2171 END;
2172
2173 -- Header Adj Query_Rows
2174 PROCEDURE Query_Rows
2175 ( p_price_adjustment_id IN NUMBER :=
2176 FND_API.G_MISS_NUM
2177 , p_header_id IN NUMBER :=
2178 FND_API.G_MISS_NUM
2179 , p_version_number IN NUMBER := NULL
2180 , p_phase_change_flag IN VARCHAR2 := NULL
2181 , x_Header_Adj_Tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Tbl_Type
2182 )
2183 IS
2184 l_count NUMBER;
2185 l_version_number NUMBER := p_version_number;
2186 l_current_version_number NUMBER;
2187
2188 CURSOR l_Header_Adj_csr_p IS
2189 SELECT ATTRIBUTE1
2190 , ATTRIBUTE10
2191 , ATTRIBUTE11
2192 , ATTRIBUTE12
2193 , ATTRIBUTE13
2194 , ATTRIBUTE14
2195 , ATTRIBUTE15
2196 , ATTRIBUTE2
2197 , ATTRIBUTE3
2198 , ATTRIBUTE4
2199 , ATTRIBUTE5
2200 , ATTRIBUTE6
2201 , ATTRIBUTE7
2202 , ATTRIBUTE8
2203 , ATTRIBUTE9
2204 , AUTOMATIC_FLAG
2205 , CONTEXT
2206 , CREATED_BY
2207 , CREATION_DATE
2208 , DISCOUNT_ID
2209 , DISCOUNT_LINE_ID
2210 , HEADER_ID
2211 , LAST_UPDATED_BY
2212 , LAST_UPDATE_DATE
2213 , LAST_UPDATE_LOGIN
2214 , LINE_ID
2215 , PERCENT
2216 , PRICE_ADJUSTMENT_ID
2217 , PROGRAM_APPLICATION_ID
2218 , PROGRAM_ID
2219 , PROGRAM_UPDATE_DATE
2220 , REQUEST_ID
2221 , LIST_HEADER_ID
2222 , LIST_LINE_ID
2223 , LIST_LINE_TYPE_CODE
2224 , MODIFIER_MECHANISM_TYPE_CODE
2225 , MODIFIED_FROM
2226 , MODIFIED_TO
2227 , UPDATED_FLAG
2228 , UPDATE_ALLOWED
2229 , APPLIED_FLAG
2230 , CHANGE_REASON_CODE
2231 , CHANGE_REASON_TEXT
2232 , operand
2233 , arithmetic_operator
2234 , COST_ID
2235 , TAX_CODE
2236 , TAX_EXEMPT_FLAG
2237 , TAX_EXEMPT_NUMBER
2238 , TAX_EXEMPT_REASON_CODE
2239 , PARENT_ADJUSTMENT_ID
2240 , INVOICED_FLAG
2241 , ESTIMATED_FLAG
2242 , INC_IN_SALES_PERFORMANCE
2243 , SPLIT_ACTION_CODE
2244 , ADJUSTED_AMOUNT
2245 , PRICING_PHASE_ID
2246 , CHARGE_TYPE_CODE
2247 , CHARGE_SUBTYPE_CODE
2248 , list_line_no
2249 , source_system_code
2250 , benefit_qty
2251 , benefit_uom_code
2252 , print_on_invoice_flag
2253 , expiration_date
2254 , rebate_transaction_type_code
2255 , rebate_transaction_reference
2256 , rebate_payment_system_code
2257 , redeemed_date
2258 , redeemed_flag
2259 , accrual_flag
2260 , range_break_quantity
2261 , accrual_conversion_rate
2262 , pricing_group_sequence
2263 , modifier_level_code
2264 , price_break_type_code
2265 , substitution_attribute
2266 , proration_type_code
2267 , CREDIT_OR_CHARGE_FLAG
2268 , INCLUDE_ON_RETURNS_FLAG
2269 , AC_ATTRIBUTE1
2270 , AC_ATTRIBUTE10
2271 , AC_ATTRIBUTE11
2272 , AC_ATTRIBUTE12
2273 , AC_ATTRIBUTE13
2274 , AC_ATTRIBUTE14
2275 , AC_ATTRIBUTE15
2276 , AC_ATTRIBUTE2
2277 , AC_ATTRIBUTE3
2278 , AC_ATTRIBUTE4
2279 , AC_ATTRIBUTE5
2280 , AC_ATTRIBUTE6
2281 , AC_ATTRIBUTE7
2282 , AC_ATTRIBUTE8
2283 , AC_ATTRIBUTE9
2284 , AC_CONTEXT
2285 --uom begin
2286 -- , OPERAND_PER_PQTY
2287 -- , ADJUSTED_AMOUNT_PER_PQTY
2288 --uom end
2289 , LOCK_CONTROL
2290 FROM OE_PRICE_ADJS_HISTORY
2291 WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
2292 AND VERSION_NUMBER = l_version_number
2293 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
2294 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
2295 AND VERSION_FLAG = 'Y'));
2296
2297 CURSOR l_Header_Adj_csr_h IS
2298 SELECT ATTRIBUTE1
2299 , ATTRIBUTE10
2300 , ATTRIBUTE11
2301 , ATTRIBUTE12
2302 , ATTRIBUTE13
2303 , ATTRIBUTE14
2304 , ATTRIBUTE15
2305 , ATTRIBUTE2
2306 , ATTRIBUTE3
2307 , ATTRIBUTE4
2308 , ATTRIBUTE5
2309 , ATTRIBUTE6
2310 , ATTRIBUTE7
2311 , ATTRIBUTE8
2312 , ATTRIBUTE9
2313 , AUTOMATIC_FLAG
2314 , CONTEXT
2315 , CREATED_BY
2316 , CREATION_DATE
2317 , DISCOUNT_ID
2318 , DISCOUNT_LINE_ID
2319 , HEADER_ID
2320 , LAST_UPDATED_BY
2321 , LAST_UPDATE_DATE
2322 , LAST_UPDATE_LOGIN
2323 , LINE_ID
2324 , PERCENT
2325 , PRICE_ADJUSTMENT_ID
2326 , PROGRAM_APPLICATION_ID
2327 , PROGRAM_ID
2328 , PROGRAM_UPDATE_DATE
2329 , REQUEST_ID
2330 , LIST_HEADER_ID
2331 , LIST_LINE_ID
2332 , LIST_LINE_TYPE_CODE
2333 , MODIFIER_MECHANISM_TYPE_CODE
2334 , MODIFIED_FROM
2335 , MODIFIED_TO
2336 , UPDATED_FLAG
2337 , UPDATE_ALLOWED
2338 , APPLIED_FLAG
2339 , CHANGE_REASON_CODE
2340 , CHANGE_REASON_TEXT
2341 , operand
2342 , arithmetic_operator
2343 , COST_ID
2344 , TAX_CODE
2345 , TAX_EXEMPT_FLAG
2346 , TAX_EXEMPT_NUMBER
2347 , TAX_EXEMPT_REASON_CODE
2348 , PARENT_ADJUSTMENT_ID
2349 , INVOICED_FLAG
2350 , ESTIMATED_FLAG
2351 , INC_IN_SALES_PERFORMANCE
2352 , SPLIT_ACTION_CODE
2353 , ADJUSTED_AMOUNT
2354 , PRICING_PHASE_ID
2355 , CHARGE_TYPE_CODE
2356 , CHARGE_SUBTYPE_CODE
2357 , list_line_no
2358 , source_system_code
2359 , benefit_qty
2360 , benefit_uom_code
2361 , print_on_invoice_flag
2362 , expiration_date
2363 , rebate_transaction_type_code
2364 , rebate_transaction_reference
2365 , rebate_payment_system_code
2366 , redeemed_date
2367 , redeemed_flag
2368 , accrual_flag
2369 , range_break_quantity
2370 , accrual_conversion_rate
2371 , pricing_group_sequence
2372 , modifier_level_code
2373 , price_break_type_code
2374 , substitution_attribute
2375 , proration_type_code
2376 , CREDIT_OR_CHARGE_FLAG
2377 , INCLUDE_ON_RETURNS_FLAG
2378 , AC_ATTRIBUTE1
2379 , AC_ATTRIBUTE10
2380 , AC_ATTRIBUTE11
2381 , AC_ATTRIBUTE12
2382 , AC_ATTRIBUTE13
2383 , AC_ATTRIBUTE14
2384 , AC_ATTRIBUTE15
2385 , AC_ATTRIBUTE2
2386 , AC_ATTRIBUTE3
2387 , AC_ATTRIBUTE4
2388 , AC_ATTRIBUTE5
2389 , AC_ATTRIBUTE6
2390 , AC_ATTRIBUTE7
2391 , AC_ATTRIBUTE8
2392 , AC_ATTRIBUTE9
2393 , AC_CONTEXT
2394 --uom begin
2395 -- , OPERAND_PER_PQTY
2396 -- , ADJUSTED_AMOUNT_PER_PQTY
2397 --uom end
2398 , LOCK_CONTROL
2399 FROM OE_PRICE_ADJS_HISTORY
2400 WHERE HEADER_ID = p_header_id AND LINE_ID IS NULL
2401 AND VERSION_NUMBER = l_version_number
2402 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
2403 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
2404 AND VERSION_FLAG = 'Y'));
2405
2406 l_rec l_header_adj_csr_p%ROWTYPE;
2407 l_entity NUMBER;
2408
2409 BEGIN
2410 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
2411 l_current_version_number := Get_Version_Number(p_header_id => p_header_id, p_price_adjustment_id => p_price_adjustment_id);
2412
2413 IF l_version_number >= l_current_version_number THEN
2414 l_version_number := NULL;
2415 END IF;
2416 END IF;
2417
2418 IF l_version_number IS NULL THEN
2419 OE_Header_Adj_Util.Query_Rows(p_price_adjustment_id => p_price_adjustment_id, p_header_id => p_header_id, x_header_adj_tbl => x_header_adj_tbl);
2420 ELSE
2421
2422 IF
2423 (p_price_adjustment_id IS NOT NULL
2424 AND
2425 p_price_adjustment_id <> FND_API.G_MISS_NUM)
2426 AND
2427 (p_header_id IS NOT NULL
2428 AND
2429 p_header_id <> FND_API.G_MISS_NUM)
2430 THEN
2431
2432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2433
2434 END IF;
2435
2436 IF nvl(p_price_adjustment_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
2437 l_entity := 1;
2438 OPEN l_header_adj_csr_p;
2439 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
2440 l_entity := 2;
2441 OPEN l_header_adj_csr_h;
2442 END IF;
2443
2444 -- Loop over fetched records
2445 l_count := 1;
2446 LOOP
2447 IF l_entity = 1 THEN
2448 FETCH l_header_adj_csr_p INTO l_rec;
2449 EXIT WHEN l_header_adj_csr_p%NOTFOUND;
2450 ELSIF l_entity = 2 THEN
2451 FETCH l_header_adj_csr_h INTO l_rec;
2452 EXIT WHEN l_header_adj_csr_h%NOTFOUND;
2453 ELSE
2454 EXIT;
2455 END IF;
2456
2457 x_Header_Adj_tbl(l_count).attribute1 := l_rec.ATTRIBUTE1;
2458 x_Header_Adj_tbl(l_count).attribute10 := l_rec.ATTRIBUTE10;
2459 x_Header_Adj_tbl(l_count).attribute11 := l_rec.ATTRIBUTE11;
2460 x_Header_Adj_tbl(l_count).attribute12 := l_rec.ATTRIBUTE12;
2461 x_Header_Adj_tbl(l_count).attribute13 := l_rec.ATTRIBUTE13;
2462 x_Header_Adj_tbl(l_count).attribute14 := l_rec.ATTRIBUTE14;
2463 x_Header_Adj_tbl(l_count).attribute15 := l_rec.ATTRIBUTE15;
2464 x_Header_Adj_tbl(l_count).attribute2 := l_rec.ATTRIBUTE2;
2465 x_Header_Adj_tbl(l_count).attribute3 := l_rec.ATTRIBUTE3;
2466 x_Header_Adj_tbl(l_count).attribute4 := l_rec.ATTRIBUTE4;
2467 x_Header_Adj_tbl(l_count).attribute5 := l_rec.ATTRIBUTE5;
2468 x_Header_Adj_tbl(l_count).attribute6 := l_rec.ATTRIBUTE6;
2469 x_Header_Adj_tbl(l_count).attribute7 := l_rec.ATTRIBUTE7;
2470 x_Header_Adj_tbl(l_count).attribute8 := l_rec.ATTRIBUTE8;
2471 x_Header_Adj_tbl(l_count).attribute9 := l_rec.ATTRIBUTE9;
2472 x_Header_Adj_tbl(l_count).automatic_flag := l_rec.AUTOMATIC_FLAG;
2473 x_Header_Adj_tbl(l_count).context := l_rec.CONTEXT;
2474 x_Header_Adj_tbl(l_count).created_by := l_rec.CREATED_BY;
2475 x_Header_Adj_tbl(l_count).creation_date := l_rec.CREATION_DATE;
2476 x_Header_Adj_tbl(l_count).discount_id := l_rec.DISCOUNT_ID;
2477 x_Header_Adj_tbl(l_count).discount_line_id := l_rec.DISCOUNT_LINE_ID;
2478 x_Header_Adj_tbl(l_count).header_id := l_rec.HEADER_ID;
2479 x_Header_Adj_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
2480 x_Header_Adj_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
2481 x_Header_Adj_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
2482 x_Header_Adj_tbl(l_count).line_id := l_rec.LINE_ID;
2483 x_Header_Adj_tbl(l_count).percent := l_rec.PERCENT;
2484 x_Header_Adj_tbl(l_count).price_adjustment_id := l_rec.PRICE_ADJUSTMENT_ID;
2485 x_Header_Adj_tbl(l_count).program_application_id := l_rec.PROGRAM_APPLICATION_ID;
2486 x_Header_Adj_tbl(l_count).program_id := l_rec.PROGRAM_ID;
2487 x_Header_Adj_tbl(l_count).program_update_date := l_rec.PROGRAM_UPDATE_DATE;
2488 x_Header_Adj_tbl(l_count).adjusted_amount := l_rec.adjusted_amount;
2489 x_Header_Adj_tbl(l_count).pricing_phase_id := l_rec.pricing_phase_id;
2490 x_Header_Adj_tbl(l_count).list_header_id := l_rec.list_header_id;
2491 x_Header_Adj_tbl(l_count).list_line_id := l_rec.list_line_id;
2492 x_Header_Adj_tbl(l_count).list_line_type_code := l_rec.list_line_type_code;
2493 x_Header_Adj_tbl(l_count).modifier_mechanism_type_code :=
2494 l_rec.modifier_mechanism_type_code;
2495 x_Header_Adj_tbl(l_count).modified_from := l_rec.modified_from;
2496 x_Header_Adj_tbl(l_count).modified_to := l_rec.modified_to;
2497 x_Header_Adj_tbl(l_count).updated_flag := l_rec.updated_flag;
2498 x_Header_Adj_tbl(l_count).update_allowed := l_rec.update_allowed;
2499 x_Header_Adj_tbl(l_count).applied_flag := l_rec.applied_flag;
2500 x_Header_Adj_tbl(l_count).change_reason_code := l_rec.change_reason_code;
2501 x_Header_Adj_tbl(l_count).change_reason_text := l_rec.change_reason_text;
2502 x_Header_Adj_tbl(l_count).operand := l_rec.operand;
2503 x_Header_Adj_tbl(l_count).arithmetic_operator := l_rec.arithmetic_operator;
2504
2505 x_Header_Adj_tbl(l_count).request_id := l_rec.REQUEST_ID;
2506
2507 x_Header_Adj_tbl(l_count).cost_id := l_rec.cost_id;
2508 x_Header_Adj_tbl(l_count).tax_code := l_rec.tax_code;
2509 x_Header_Adj_tbl(l_count).tax_exempt_flag := l_rec.tax_exempt_flag;
2510 x_Header_Adj_tbl(l_count).tax_exempt_number := l_rec.tax_exempt_number;
2511 x_Header_Adj_tbl(l_count).tax_exempt_reason_code := l_rec.tax_exempt_reason_code;
2512 x_Header_Adj_tbl(l_count).parent_adjustment_id := l_rec.parent_adjustment_id;
2513 x_Header_Adj_tbl(l_count).invoiced_flag := l_rec.invoiced_flag;
2514 x_Header_Adj_tbl(l_count).estimated_flag := l_rec.estimated_flag;
2515 x_Header_Adj_tbl(l_count).inc_in_sales_performance := l_rec.inc_in_sales_performance;
2516 x_Header_Adj_tbl(l_count).split_action_code := l_rec.split_action_code;
2517 x_Header_Adj_tbl(l_count).charge_type_code := l_rec.charge_type_code;
2518 x_Header_Adj_tbl(l_count).charge_subtype_code := l_rec.charge_subtype_code;
2519 x_Header_Adj_tbl(l_count).list_line_no := l_rec.list_line_no;
2520 x_Header_Adj_tbl(l_count).source_system_code := l_rec.source_system_code;
2521 x_Header_Adj_tbl(l_count).benefit_qty := l_rec.benefit_qty;
2522 x_Header_Adj_tbl(l_count).benefit_uom_code := l_rec.benefit_uom_code;
2523 x_Header_Adj_tbl(l_count).print_on_invoice_flag := l_rec.print_on_invoice_flag;
2524 x_Header_Adj_tbl(l_count).expiration_date := l_rec.expiration_date;
2525 x_Header_Adj_tbl(l_count).rebate_transaction_type_code := l_rec.rebate_transaction_type_code;
2526 x_Header_Adj_tbl(l_count).rebate_transaction_reference := l_rec.rebate_transaction_reference;
2527 x_Header_Adj_tbl(l_count).rebate_payment_system_code := l_rec.rebate_payment_system_code;
2528 x_Header_Adj_tbl(l_count).redeemed_date := l_rec.redeemed_date;
2529 x_Header_Adj_tbl(l_count).redeemed_flag := l_rec.redeemed_flag;
2530 x_Header_Adj_tbl(l_count).accrual_flag := l_rec.accrual_flag;
2531 x_Header_Adj_tbl(l_count).range_break_quantity := l_rec.range_break_quantity;
2532 x_Header_Adj_tbl(l_count).accrual_conversion_rate := l_rec.accrual_conversion_rate;
2533 x_Header_Adj_tbl(l_count).pricing_group_sequence := l_rec.pricing_group_sequence;
2534 x_Header_Adj_tbl(l_count).modifier_level_code := l_rec.modifier_level_code;
2535 x_Header_Adj_tbl(l_count).price_break_type_code := l_rec.price_break_type_code;
2536 x_Header_Adj_tbl(l_count).substitution_attribute := l_rec.substitution_attribute;
2537 x_Header_Adj_tbl(l_count).proration_type_code := l_rec.proration_type_code;
2538 x_Header_Adj_tbl(l_count).credit_or_charge_flag := l_rec.credit_or_charge_flag;
2539 x_Header_Adj_tbl(l_count).include_on_returns_flag := l_rec.include_on_returns_flag;
2540 x_Header_Adj_tbl(l_count).ac_attribute1 := l_rec.AC_ATTRIBUTE1;
2541 x_Header_Adj_tbl(l_count).ac_attribute10 := l_rec.AC_ATTRIBUTE10;
2542 x_Header_Adj_tbl(l_count).ac_attribute11 := l_rec.AC_ATTRIBUTE11;
2543 x_Header_Adj_tbl(l_count).ac_attribute12 := l_rec.AC_ATTRIBUTE12;
2544 x_Header_Adj_tbl(l_count).ac_attribute13 := l_rec.AC_ATTRIBUTE13;
2545 x_Header_Adj_tbl(l_count).ac_attribute14 := l_rec.AC_ATTRIBUTE14;
2546 x_Header_Adj_tbl(l_count).ac_attribute15 := l_rec.AC_ATTRIBUTE15;
2547 x_Header_Adj_tbl(l_count).ac_attribute2 := l_rec.AC_ATTRIBUTE2;
2548 x_Header_Adj_tbl(l_count).ac_attribute3 := l_rec.AC_ATTRIBUTE3;
2549 x_Header_Adj_tbl(l_count).ac_attribute4 := l_rec.AC_ATTRIBUTE4;
2550 x_Header_Adj_tbl(l_count).ac_attribute5 := l_rec.AC_ATTRIBUTE5;
2551 x_Header_Adj_tbl(l_count).ac_attribute6 := l_rec.AC_ATTRIBUTE6;
2552 x_Header_Adj_tbl(l_count).ac_attribute7 := l_rec.AC_ATTRIBUTE7;
2553 x_Header_Adj_tbl(l_count).ac_attribute8 := l_rec.AC_ATTRIBUTE8;
2554 x_Header_Adj_tbl(l_count).ac_attribute9 := l_rec.AC_ATTRIBUTE9;
2555 x_Header_Adj_tbl(l_count).ac_context := l_rec.AC_CONTEXT;
2556 --uom begin
2557 -- x_Header_Adj_tbl(l_count).OPERAND_PER_PQTY :=l_rec.operand_per_pqty;
2558 -- x_Header_Adj_tbl(l_count).ADJUSTED_AMOUNT_PER_PQTY :=l_rec.adjusted_amount_per_pqty;
2559 --uom end
2560
2561 x_Header_Adj_tbl(l_count).lock_control := l_rec.LOCK_CONTROL;
2562
2563 -- set values for non-DB fields
2564 x_Header_Adj_tbl(l_count).db_flag := FND_API.G_TRUE;
2565 x_Header_Adj_tbl(l_count).operation := FND_API.G_MISS_CHAR;
2566 x_Header_Adj_tbl(l_count).return_status := FND_API.G_MISS_CHAR;
2567
2568 l_count := l_count + 1;
2569 END LOOP;
2570
2571 IF l_entity = 1 THEN
2572 CLOSE l_header_adj_csr_p;
2573 ELSIF l_entity = 2 THEN
2574 CLOSE l_header_adj_csr_h;
2575 END IF;
2576
2577 -- PK sent and no rows found
2578
2579 IF
2580 (p_price_adjustment_id IS NOT NULL
2581 AND
2582 p_price_adjustment_id <> FND_API.G_MISS_NUM)
2583 AND
2584 (x_Header_Adj_tbl.COUNT = 0)
2585 THEN
2586 RAISE NO_DATA_FOUND;
2587 END IF;
2588
2589
2590
2591 END IF;
2592
2593 EXCEPTION
2594
2595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2596
2597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2598
2599 WHEN OTHERS THEN
2600
2601 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2602 OE_MSG_PUB.Add_Exc_Msg
2603 ( G_PKG_NAME
2604 , 'Header Adj Query_Rows'
2605 );
2606 END IF;
2607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2608
2609 END;
2610
2611
2612 -- Header SCredit Query_Row
2613 PROCEDURE Query_Row
2614 ( p_sales_credit_id IN NUMBER,
2615 p_version_number IN NUMBER := NULL,
2616 p_phase_change_flag IN VARCHAR2 := NULL,
2617 x_Header_Scredit_Rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
2618 )
2619 IS
2620 l_version_number NUMBER := p_version_number;
2621 l_current_version_number NUMBER;
2622 x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2623 --
2624 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2625 --
2626 BEGIN
2627 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
2628 l_current_version_number := Get_Version_Number(p_sales_credit_id => p_sales_credit_id);
2629
2630 IF l_version_number >= l_current_version_number THEN
2631 l_version_number := NULL;
2632 END IF;
2633 END IF;
2634
2635 IF l_version_number IS NULL THEN
2636 OE_Header_Scredit_Util.Query_Row(p_sales_credit_id => p_sales_credit_id, x_header_scredit_rec => x_header_scredit_rec);
2637 ELSE
2638 G_INTERNAL_QUERY := TRUE;
2639 Query_Rows
2640 ( p_sales_credit_id => p_sales_credit_id,
2641 p_version_number => l_version_number,
2642 x_Header_Scredit_tbl => x_Header_Scredit_tbl
2643 );
2644 x_Header_Scredit_rec := x_Header_Scredit_tbl(1);
2645 G_INTERNAL_QUERY := FALSE;
2646 END IF;
2647
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2651 OE_MSG_PUB.Add_Exc_Msg
2652 ( G_PKG_NAME
2653 , 'Header Scredit Query_Row'
2654 );
2655 END IF;
2656 END;
2657
2658
2659 -- Header SCredit Query_Rows
2660 PROCEDURE Query_Rows
2661 ( p_sales_credit_id IN NUMBER :=
2662 FND_API.G_MISS_NUM
2663 , p_header_id IN NUMBER :=
2664 FND_API.G_MISS_NUM
2665 , p_version_number IN NUMBER := NULL
2666 , p_phase_change_flag IN VARCHAR2 := NULL
2667 , x_Header_Scredit_tbl IN OUT NOCOPY OE_Order_PUB.Header_Scredit_tbl_Type
2668 )
2669 IS
2670 l_version_number NUMBER := p_version_number;
2671 l_current_version_number NUMBER;
2672 l_count NUMBER;
2673
2674 CURSOR l_Header_Scredit_csr_s IS
2675 SELECT ATTRIBUTE1
2676 , ATTRIBUTE10
2677 , ATTRIBUTE11
2678 , ATTRIBUTE12
2679 , ATTRIBUTE13
2680 , ATTRIBUTE14
2681 , ATTRIBUTE15
2682 , ATTRIBUTE2
2683 , ATTRIBUTE3
2684 , ATTRIBUTE4
2685 , ATTRIBUTE5
2686 , ATTRIBUTE6
2687 , ATTRIBUTE7
2688 , ATTRIBUTE8
2689 , ATTRIBUTE9
2690 , CONTEXT
2691 , CREATED_BY
2692 , CREATION_DATE
2693 , DW_UPDATE_ADVICE_FLAG
2694 , HEADER_ID
2695 , LAST_UPDATED_BY
2696 , LAST_UPDATE_DATE
2697 , LAST_UPDATE_LOGIN
2698 , LINE_ID
2699 , PERCENT
2700 , SALESREP_ID
2701 , sales_credit_type_id
2702 , SALES_CREDIT_ID
2703 , WH_UPDATE_DATE
2704 --SG {
2705 -- , SALES_GROUP_ID
2706 -- , SALES_GROUP_UPDATED
2707 --SG }
2708 , LOCK_CONTROL
2709 FROM OE_SALES_CREDIT_HISTORY
2710 WHERE SALES_CREDIT_ID = p_sales_credit_id
2711 AND VERSION_NUMBER = l_version_number
2712 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
2713 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
2714 AND VERSION_FLAG = 'Y'));
2715
2716 CURSOR l_Header_Scredit_csr_h IS
2717 SELECT ATTRIBUTE1
2718 , ATTRIBUTE10
2719 , ATTRIBUTE11
2720 , ATTRIBUTE12
2721 , ATTRIBUTE13
2722 , ATTRIBUTE14
2723 , ATTRIBUTE15
2724 , ATTRIBUTE2
2725 , ATTRIBUTE3
2726 , ATTRIBUTE4
2727 , ATTRIBUTE5
2728 , ATTRIBUTE6
2729 , ATTRIBUTE7
2730 , ATTRIBUTE8
2731 , ATTRIBUTE9
2732 , CONTEXT
2733 , CREATED_BY
2734 , CREATION_DATE
2735 , DW_UPDATE_ADVICE_FLAG
2736 , HEADER_ID
2737 , LAST_UPDATED_BY
2738 , LAST_UPDATE_DATE
2739 , LAST_UPDATE_LOGIN
2740 , LINE_ID
2741 , PERCENT
2742 , SALESREP_ID
2743 , sales_credit_type_id
2744 , SALES_CREDIT_ID
2745 , WH_UPDATE_DATE
2746 --SG {
2747 -- , SALES_GROUP_ID
2748 -- , SALES_GROUP_UPDATED
2749 --SG }
2750 , LOCK_CONTROL
2751 FROM OE_SALES_CREDIT_HISTORY
2752 WHERE HEADER_ID = p_header_id
2753 AND LINE_ID IS NULL
2754 AND VERSION_NUMBER = l_version_number
2755 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
2756 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
2757 AND VERSION_FLAG = 'Y'));
2758
2759 l_rec l_header_scredit_csr_s%ROWTYPE;
2760 l_entity NUMBER;
2761
2762 BEGIN
2763 IF p_version_number IS NOT NULL AND G_INTERNAL_QUERY = FALSE THEN
2764 l_current_version_number := Get_Version_Number(p_header_id => p_header_id, p_sales_credit_id => p_sales_credit_id);
2765
2766 IF l_version_number >= l_current_version_number THEN
2767 l_version_number := NULL;
2768 END IF;
2769 END IF;
2770
2771 IF l_version_number IS NULL THEN
2772 OE_Header_Scredit_Util.Query_Rows(p_sales_credit_id => p_sales_credit_id, p_header_id => p_header_id, x_header_scredit_tbl => x_header_scredit_tbl);
2773 ELSE
2774
2775 IF
2776 (p_sales_credit_id IS NOT NULL
2777 AND
2778 p_sales_credit_id <> FND_API.G_MISS_NUM)
2779 AND
2780 (p_header_id IS NOT NULL
2781 AND
2782 p_header_id <> FND_API.G_MISS_NUM)
2783 THEN
2784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2785
2786 END IF;
2787
2788 IF nvl(p_sales_credit_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
2789 l_entity := 1;
2790 OPEN l_header_scredit_csr_s;
2791 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
2792 l_entity := 2;
2793 OPEN l_header_scredit_csr_h;
2794 END IF;
2795
2796 -- Loop over fetched records
2797
2798 l_count := 1;
2799
2800 LOOP
2801 IF l_entity = 1 THEN
2802 FETCH l_header_scredit_csr_s INTO l_rec;
2803 EXIT WHEN l_header_scredit_csr_s%NOTFOUND;
2804 ELSIF l_entity = 2 THEN
2805 FETCH l_header_scredit_csr_h INTO l_rec;
2806 EXIT WHEN l_header_scredit_csr_h%NOTFOUND;
2807 ELSE
2808 EXIT;
2809 END IF;
2810
2811 x_header_scredit_tbl(l_count).attribute1 := l_rec.ATTRIBUTE1;
2812 x_header_scredit_tbl(l_count).attribute10 := l_rec.ATTRIBUTE10;
2813 x_header_scredit_tbl(l_count).attribute11 := l_rec.ATTRIBUTE11;
2814 x_header_scredit_tbl(l_count).attribute12 := l_rec.ATTRIBUTE12;
2815 x_header_scredit_tbl(l_count).attribute13 := l_rec.ATTRIBUTE13;
2816 x_header_scredit_tbl(l_count).attribute14 := l_rec.ATTRIBUTE14;
2817 x_header_scredit_tbl(l_count).attribute15 := l_rec.ATTRIBUTE15;
2818 x_header_scredit_tbl(l_count).attribute2 := l_rec.ATTRIBUTE2;
2819 x_header_scredit_tbl(l_count).attribute3 := l_rec.ATTRIBUTE3;
2820 x_header_scredit_tbl(l_count).attribute4 := l_rec.ATTRIBUTE4;
2821 x_header_scredit_tbl(l_count).attribute5 := l_rec.ATTRIBUTE5;
2822 x_header_scredit_tbl(l_count).attribute6 := l_rec.ATTRIBUTE6;
2823 x_header_scredit_tbl(l_count).attribute7 := l_rec.ATTRIBUTE7;
2824 x_header_scredit_tbl(l_count).attribute8 := l_rec.ATTRIBUTE8;
2825 x_header_scredit_tbl(l_count).attribute9 := l_rec.ATTRIBUTE9;
2826 x_header_scredit_tbl(l_count).context := l_rec.CONTEXT;
2827 x_header_scredit_tbl(l_count).created_by := l_rec.CREATED_BY;
2828 x_header_scredit_tbl(l_count).creation_date := l_rec.CREATION_DATE;
2829 x_header_scredit_tbl(l_count).dw_update_advice_flag := l_rec.DW_UPDATE_ADVICE_FLAG;
2830 x_header_scredit_tbl(l_count).header_id := l_rec.HEADER_ID;
2831 x_header_scredit_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
2832 x_header_scredit_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
2833 x_header_scredit_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
2834 x_header_scredit_tbl(l_count).line_id := l_rec.LINE_ID;
2835 x_header_scredit_tbl(l_count).percent := l_rec.PERCENT;
2836 x_header_scredit_tbl(l_count).salesrep_id := l_rec.SALESREP_ID;
2837 x_header_scredit_tbl(l_count).sales_credit_type_id := l_rec.sales_credit_type_id;
2838 x_header_scredit_tbl(l_count).sales_credit_id := l_rec.SALES_CREDIT_ID;
2839 x_header_scredit_tbl(l_count).wh_update_date := l_rec.WH_UPDATE_DATE;
2840 --SG{
2841 -- x_header_scredit_tbl(l_count).sales_group_id := l_rec.sales_group_id;
2842 -- x_header_scredit_tbl(l_count).sales_group_updated:=l_rec.sales_group_updated;
2843 --SG}
2844 x_header_scredit_tbl(l_count).lock_control := l_rec.LOCK_CONTROL;
2845
2846 l_count := l_count + 1;
2847 END LOOP;
2848
2849 IF l_entity = 1 THEN
2850 CLOSE l_header_scredit_csr_s;
2851 ELSIF l_entity = 2 THEN
2852 CLOSE l_header_scredit_csr_h;
2853 END IF;
2854
2855 -- PK sent and no rows found
2856
2857 IF
2858 (p_sales_credit_id IS NOT NULL
2859 AND
2860 p_sales_credit_id <> FND_API.G_MISS_NUM)
2861 AND
2862 (x_Header_Scredit_tbl.COUNT = 0)
2863 THEN
2864 RAISE NO_DATA_FOUND;
2865 END IF;
2866
2867 END IF;
2868
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2872 OE_MSG_PUB.Add_Exc_Msg
2873 ( G_PKG_NAME
2874 , 'Header Scredit Query_Rows'
2875 );
2876 END IF;
2877 END;
2878
2879
2880 -- Line Query_Row
2881 PROCEDURE Query_Row
2882 ( p_line_id IN NUMBER
2883 , p_version_number IN NUMBER := NULL
2884 , p_phase_change_flag IN VARCHAR2 := NULL
2885 , x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
2886 )
2887 IS
2888 l_version_number NUMBER := p_version_number;
2889 l_current_version_number NUMBER;
2890 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2891 BEGIN
2892 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
2893 l_current_version_number := Get_Version_Number(p_line_id => p_line_id);
2894 IF l_version_number >= l_current_version_number THEN
2895 l_version_number := NULL;
2896 END IF;
2897 END IF;
2898
2899 IF l_version_number IS NULL THEN
2900 OE_Line_Util.Query_Row(p_line_id => p_line_id, x_line_rec => x_line_rec);
2901 ELSE
2902
2903 G_INTERNAL_QUERY := TRUE;
2904 Query_Rows
2905 ( p_line_id => p_line_id
2906 , p_version_number => l_version_number
2907 , x_line_tbl => l_line_tbl
2908 );
2909
2910 x_line_rec := l_line_tbl(1);
2911 G_INTERNAL_QUERY := FALSE;
2912
2913 END IF;
2914
2915 EXCEPTION
2916 WHEN OTHERS THEN
2917 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2918 OE_MSG_PUB.Add_Exc_Msg
2919 ( G_PKG_NAME
2920 , 'Line Query_Row'
2921 );
2922 END IF;
2923 END;
2924
2925
2926 -- Line Query_Rows
2927 PROCEDURE Query_Rows
2928 ( p_line_id IN NUMBER :=
2929 FND_API.G_MISS_NUM
2930 , p_header_id IN NUMBER :=
2931 FND_API.G_MISS_NUM
2932 , p_line_set_id IN NUMBER :=
2933 FND_API.G_MISS_NUM
2934 , p_version_number IN NUMBER := NULL
2935 , p_phase_change_flag IN VARCHAR2 := NULL
2936 , x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
2937 )
2938 IS
2939 l_version_number NUMBER := p_version_number;
2940 l_current_version_number NUMBER;
2941
2942 l_org_id NUMBER;
2943 l_count NUMBER;
2944 l_entity NUMBER;
2945
2946 CURSOR l_line_csr_l IS
2947 SELECT ACCOUNTING_RULE_ID
2948 , ACCOUNTING_RULE_DURATION
2949 , ACTUAL_ARRIVAL_DATE
2950 , ACTUAL_SHIPMENT_DATE
2951 , AGREEMENT_ID
2952 , ARRIVAL_SET_ID
2953 , ATO_LINE_ID
2954 , ATTRIBUTE1
2955 , ATTRIBUTE10
2956 , ATTRIBUTE11
2957 , ATTRIBUTE12
2958 , ATTRIBUTE13
2959 , ATTRIBUTE14
2960 , ATTRIBUTE15
2961 , ATTRIBUTE16 --For bug 2184255
2962 , ATTRIBUTE17
2963 , ATTRIBUTE18
2964 , ATTRIBUTE19
2965 , ATTRIBUTE2
2966 , ATTRIBUTE20
2967 , ATTRIBUTE3
2968 , ATTRIBUTE4
2969 , ATTRIBUTE5
2970 , ATTRIBUTE6
2971 , ATTRIBUTE7
2972 , ATTRIBUTE8
2973 , ATTRIBUTE9
2974 , AUTO_SELECTED_QUANTITY
2975 , AUTHORIZED_TO_SHIP_FLAG
2976 , BOOKED_FLAG
2977 , CANCELLED_FLAG
2978 , CANCELLED_QUANTITY
2979 , COMPONENT_CODE
2980 , COMPONENT_NUMBER
2981 , COMPONENT_SEQUENCE_ID
2982 , CONFIG_HEADER_ID
2983 , CONFIG_REV_NBR
2984 , CONFIG_DISPLAY_SEQUENCE
2985 , CONFIGURATION_ID
2986 , CONTEXT
2987
2988 , CREATED_BY
2989 , CREATION_DATE
2990 , CREDIT_INVOICE_LINE_ID
2991 , CUSTOMER_DOCK_CODE
2992 , CUSTOMER_JOB
2993 , CUSTOMER_PRODUCTION_LINE
2994 , CUST_PRODUCTION_SEQ_NUM
2995 , CUSTOMER_TRX_LINE_ID
2996 , CUST_MODEL_SERIAL_NUMBER
2997 , CUST_PO_NUMBER
2998 , CUSTOMER_LINE_NUMBER
2999 , DELIVERY_LEAD_TIME
3000 , DELIVER_TO_CONTACT_ID
3001 , DELIVER_TO_ORG_ID
3002 , DEMAND_BUCKET_TYPE_CODE
3003 , DEMAND_CLASS_CODE
3004 , DEP_PLAN_REQUIRED_FLAG
3005
3006 , EARLIEST_ACCEPTABLE_DATE
3007 , END_ITEM_UNIT_NUMBER
3008 , EXPLOSION_DATE
3009 , FIRST_ACK_CODE
3010 , FIRST_ACK_DATE
3011 , FOB_POINT_CODE
3012 , FREIGHT_CARRIER_CODE
3013 , FREIGHT_TERMS_CODE
3014 , FULFILLED_QUANTITY
3015 , FULFILLED_FLAG
3016 , FULFILLMENT_METHOD_CODE
3017 , FULFILLMENT_DATE
3018 , GLOBAL_ATTRIBUTE1
3019 , GLOBAL_ATTRIBUTE10
3020 , GLOBAL_ATTRIBUTE11
3021 , GLOBAL_ATTRIBUTE12
3022 , GLOBAL_ATTRIBUTE13
3023 , GLOBAL_ATTRIBUTE14
3024 , GLOBAL_ATTRIBUTE15
3025 , GLOBAL_ATTRIBUTE16
3026 , GLOBAL_ATTRIBUTE17
3027 , GLOBAL_ATTRIBUTE18
3028 , GLOBAL_ATTRIBUTE19
3029 , GLOBAL_ATTRIBUTE2
3030 , GLOBAL_ATTRIBUTE20
3031 , GLOBAL_ATTRIBUTE3
3032 , GLOBAL_ATTRIBUTE4
3033 , GLOBAL_ATTRIBUTE5
3034 , GLOBAL_ATTRIBUTE6
3035 , GLOBAL_ATTRIBUTE7
3036 , GLOBAL_ATTRIBUTE8
3037 , GLOBAL_ATTRIBUTE9
3038 , GLOBAL_ATTRIBUTE_CATEGORY
3039 , HEADER_ID
3040 , INDUSTRY_ATTRIBUTE1
3041 , INDUSTRY_ATTRIBUTE10
3042 , INDUSTRY_ATTRIBUTE11
3043 , INDUSTRY_ATTRIBUTE12
3044 , INDUSTRY_ATTRIBUTE13
3045 , INDUSTRY_ATTRIBUTE14
3046 , INDUSTRY_ATTRIBUTE15
3047 , INDUSTRY_ATTRIBUTE16
3048 , INDUSTRY_ATTRIBUTE17
3049 , INDUSTRY_ATTRIBUTE18
3050 , INDUSTRY_ATTRIBUTE19
3051 , INDUSTRY_ATTRIBUTE20
3052 , INDUSTRY_ATTRIBUTE21
3053 , INDUSTRY_ATTRIBUTE22
3054 , INDUSTRY_ATTRIBUTE23
3055 , INDUSTRY_ATTRIBUTE24
3056 , INDUSTRY_ATTRIBUTE25
3057 , INDUSTRY_ATTRIBUTE26
3058 , INDUSTRY_ATTRIBUTE27
3059 , INDUSTRY_ATTRIBUTE28
3060 , INDUSTRY_ATTRIBUTE29
3061 , INDUSTRY_ATTRIBUTE30
3062 , INDUSTRY_ATTRIBUTE2
3063 , INDUSTRY_ATTRIBUTE3
3064 , INDUSTRY_ATTRIBUTE4
3065 , INDUSTRY_ATTRIBUTE5
3066 , INDUSTRY_ATTRIBUTE6
3067 , INDUSTRY_ATTRIBUTE7
3068 , INDUSTRY_ATTRIBUTE8
3069 , INDUSTRY_ATTRIBUTE9
3070 , INDUSTRY_CONTEXT
3071 , INTMED_SHIP_TO_CONTACT_ID
3072 , INTMED_SHIP_TO_ORG_ID
3073 , INVENTORY_ITEM_ID
3074 , INVOICE_INTERFACE_STATUS_CODE
3075
3076
3077
3078 , INVOICE_TO_CONTACT_ID
3079 , INVOICE_TO_ORG_ID
3080 , INVOICED_QUANTITY
3081 , INVOICING_RULE_ID
3082 , ORDERED_ITEM_ID
3083 , ITEM_IDENTIFIER_TYPE
3084 , ORDERED_ITEM
3085 , ITEM_REVISION
3086 , ITEM_TYPE_CODE
3087 , LAST_ACK_CODE
3088 , LAST_ACK_DATE
3089 , LAST_UPDATED_BY
3090 , LAST_UPDATE_DATE
3091 , LAST_UPDATE_LOGIN
3092 , LATEST_ACCEPTABLE_DATE
3093 , LINE_CATEGORY_CODE
3094 , LINE_ID
3095 , LINE_NUMBER
3096 , LINE_TYPE_ID
3097 , LINK_TO_LINE_ID
3098
3099 , MODEL_GROUP_NUMBER
3100 -- , MFG_COMPONENT_SEQUENCE_ID
3101 -- , MFG_LEAD_TIME
3102 , OPEN_FLAG
3103 , OPTION_FLAG
3104 , OPTION_NUMBER
3105 , ORDERED_QUANTITY
3106 , ORDERED_QUANTITY2 --OPM 02/JUN/00
3107 , ORDER_QUANTITY_UOM
3108 , ORDERED_QUANTITY_UOM2 --OPM 02/JUN/00
3109 , ORG_ID
3110 , ORIG_SYS_DOCUMENT_REF
3111 , ORIG_SYS_LINE_REF
3112 , ORIG_SYS_SHIPMENT_REF
3113 , OVER_SHIP_REASON_CODE
3114 , OVER_SHIP_RESOLVED_FLAG
3115 , PAYMENT_TERM_ID
3116 , PLANNING_PRIORITY
3117 , PREFERRED_GRADE --OPM 02/JUN/00
3118 , PRICE_LIST_ID
3119 , PRICE_REQUEST_CODE --PROMOTIONS MAY/01
3120 , PRICING_ATTRIBUTE1
3121 , PRICING_ATTRIBUTE10
3122 , PRICING_ATTRIBUTE2
3123 , PRICING_ATTRIBUTE3
3124 , PRICING_ATTRIBUTE4
3125 , PRICING_ATTRIBUTE5
3126 , PRICING_ATTRIBUTE6
3127 , PRICING_ATTRIBUTE7
3128 , PRICING_ATTRIBUTE8
3129 , PRICING_ATTRIBUTE9
3130 , PRICING_CONTEXT
3131 , PRICING_DATE
3132 , PRICING_QUANTITY
3133 , PRICING_QUANTITY_UOM
3134 , PROGRAM_APPLICATION_ID
3135 , PROGRAM_ID
3136 , PROGRAM_UPDATE_DATE
3137 , PROJECT_ID
3138 , PROMISE_DATE
3139 , RE_SOURCE_FLAG
3140 , REFERENCE_CUSTOMER_TRX_LINE_ID
3141 , REFERENCE_HEADER_ID
3142 , REFERENCE_LINE_ID
3143 , REFERENCE_TYPE
3144
3145 , REQUEST_DATE
3146 , REQUEST_ID
3147 , RETURN_ATTRIBUTE1
3148 , RETURN_ATTRIBUTE10
3149 , RETURN_ATTRIBUTE11
3150 , RETURN_ATTRIBUTE12
3151 , RETURN_ATTRIBUTE13
3152 , RETURN_ATTRIBUTE14
3153 , RETURN_ATTRIBUTE15
3154 , RETURN_ATTRIBUTE2
3155 , RETURN_ATTRIBUTE3
3156 , RETURN_ATTRIBUTE4
3157 , RETURN_ATTRIBUTE5
3158 , RETURN_ATTRIBUTE6
3159 , RETURN_ATTRIBUTE7
3160 , RETURN_ATTRIBUTE8
3161 , RETURN_ATTRIBUTE9
3162 , RETURN_CONTEXT
3163 , RETURN_REASON_CODE
3164 , RLA_SCHEDULE_TYPE_CODE
3165 , SALESREP_ID
3166 , SCHEDULE_ARRIVAL_DATE
3167 , SCHEDULE_SHIP_DATE
3168 , SCHEDULE_STATUS_CODE
3169 , SHIPMENT_NUMBER
3170 , SHIPMENT_PRIORITY_CODE
3171 , SHIPPED_QUANTITY
3172 , SHIPPED_QUANTITY2 -- OPM B1661023 04/02/01
3173 , SHIPPING_METHOD_CODE
3174 , SHIPPING_QUANTITY
3175 , SHIPPING_QUANTITY2 -- OPM B1661023 04/02/01
3176 , SHIPPING_QUANTITY_UOM
3177 , SHIP_FROM_ORG_ID
3178 , SUBINVENTORY
3179 , SHIP_SET_ID
3180 , SHIP_TOLERANCE_ABOVE
3181 , SHIP_TOLERANCE_BELOW
3182 , SHIPPABLE_FLAG
3183 , SHIPPING_INTERFACED_FLAG
3184 , SHIP_TO_CONTACT_ID
3185 , SHIP_TO_ORG_ID
3186 , SHIP_MODEL_COMPLETE_FLAG
3187 , SOLD_TO_ORG_ID
3188 , SOLD_FROM_ORG_ID
3189 , SORT_ORDER
3190 , SOURCE_DOCUMENT_ID
3191 , SOURCE_DOCUMENT_LINE_ID
3192 , SOURCE_DOCUMENT_TYPE_ID
3193 , SOURCE_TYPE_CODE
3194 , SPLIT_FROM_LINE_ID
3195 , LINE_SET_ID
3196 , SPLIT_BY
3197 , MODEL_REMNANT_FLAG
3198 , TASK_ID
3199 , TAX_CODE
3200 , TAX_DATE
3201 , TAX_EXEMPT_FLAG
3202 , TAX_EXEMPT_NUMBER
3203 , TAX_EXEMPT_REASON_CODE
3204 , TAX_POINT_CODE
3205 , TAX_RATE
3206 , TAX_VALUE
3207 , TOP_MODEL_LINE_ID
3208 , UNIT_LIST_PRICE
3209 , UNIT_LIST_PRICE_PER_PQTY
3210 , UNIT_SELLING_PRICE
3211 , UNIT_SELLING_PRICE_PER_PQTY
3212 , VISIBLE_DEMAND_FLAG
3213 , VEH_CUS_ITEM_CUM_KEY_ID
3214 , SHIPPING_INSTRUCTIONS
3215 , PACKING_INSTRUCTIONS
3216 , SERVICE_TXN_REASON_CODE
3217 , SERVICE_TXN_COMMENTS
3218 , SERVICE_DURATION
3219 , SERVICE_PERIOD
3220 , SERVICE_START_DATE
3221 , SERVICE_END_DATE
3222 , SERVICE_COTERMINATE_FLAG
3223 , UNIT_LIST_PERCENT
3224 , UNIT_SELLING_PERCENT
3225 , UNIT_PERCENT_BASE_PRICE
3226 , SERVICE_NUMBER
3227 , SERVICE_REFERENCE_TYPE_CODE
3228 , SERVICE_REFERENCE_LINE_ID
3229 , SERVICE_REFERENCE_SYSTEM_ID
3230 , TP_CONTEXT
3231 , TP_ATTRIBUTE1
3232 , TP_ATTRIBUTE2
3233 , TP_ATTRIBUTE3
3234 , TP_ATTRIBUTE4
3235 , TP_ATTRIBUTE5
3236 , TP_ATTRIBUTE6
3237 , TP_ATTRIBUTE7
3238 , TP_ATTRIBUTE8
3239 , TP_ATTRIBUTE9
3240 , TP_ATTRIBUTE10
3241 , TP_ATTRIBUTE11
3242 , TP_ATTRIBUTE12
3243 , TP_ATTRIBUTE13
3244 , TP_ATTRIBUTE14
3245 , TP_ATTRIBUTE15
3246 , FLOW_STATUS_CODE
3247 -- , MARKETING_SOURCE_CODE_ID
3248 , CALCULATE_PRICE_FLAG
3249 , COMMITMENT_ID
3250 , ORDER_SOURCE_ID -- aksingh
3251 -- , UPGRADED_FLAG
3252 , ORIGINAL_INVENTORY_ITEM_ID
3253 , ORIGINAL_ITEM_IDENTIFIER_TYPE
3254 , ORIGINAL_ORDERED_ITEM_ID
3255 , ORIGINAL_ORDERED_ITEM
3256 , ITEM_RELATIONSHIP_TYPE
3257 , ITEM_SUBSTITUTION_TYPE_CODE
3258 , LATE_DEMAND_PENALTY_FACTOR
3259 , OVERRIDE_ATP_DATE_CODE
3260 -- , FIRM_DEMAND_FLAG
3261 -- , EARLIEST_SHIP_DATE
3262 , USER_ITEM_DESCRIPTION
3263 , BLANKET_NUMBER
3264 , BLANKET_LINE_NUMBER
3265 , BLANKET_VERSION_NUMBER
3266 --MRG B
3267 -- , UNIT_COST
3268 --MRG E
3269 -- , LOCK_CONTROL
3270 , NVL(OPTION_NUMBER, -1) OPN
3271 , NVL(COMPONENT_NUMBER, -1) CPN
3272 , NVL(SERVICE_NUMBER, -1) SVN
3273 , CHANGE_SEQUENCE
3274 --key Transaction Dates
3275 , order_firmed_date
3276 , actual_fulfillment_date
3277 FROM OE_ORDER_LINES_HISTORY
3278 WHERE LINE_ID = p_line_id
3279 AND VERSION_NUMBER = l_version_number
3280 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
3281 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
3282 AND VERSION_FLAG = 'Y'));
3283
3284 CURSOR l_line_csr_h IS
3285 SELECT ACCOUNTING_RULE_ID
3286 , ACCOUNTING_RULE_DURATION
3287 , ACTUAL_ARRIVAL_DATE
3288 , ACTUAL_SHIPMENT_DATE
3289 , AGREEMENT_ID
3290 , ARRIVAL_SET_ID
3291 , ATO_LINE_ID
3292 , ATTRIBUTE1
3293 , ATTRIBUTE10
3294 , ATTRIBUTE11
3295 , ATTRIBUTE12
3296 , ATTRIBUTE13
3297 , ATTRIBUTE14
3298 , ATTRIBUTE15
3299 , ATTRIBUTE16 --For bug 2184255
3300 , ATTRIBUTE17
3301 , ATTRIBUTE18
3302 , ATTRIBUTE19
3303 , ATTRIBUTE2
3304 , ATTRIBUTE20
3305 , ATTRIBUTE3
3306 , ATTRIBUTE4
3307 , ATTRIBUTE5
3308 , ATTRIBUTE6
3309 , ATTRIBUTE7
3310 , ATTRIBUTE8
3311 , ATTRIBUTE9
3312 , AUTO_SELECTED_QUANTITY
3313 , AUTHORIZED_TO_SHIP_FLAG
3314 , BOOKED_FLAG
3315 , CANCELLED_FLAG
3316 , CANCELLED_QUANTITY
3317 , COMPONENT_CODE
3318 , COMPONENT_NUMBER
3319 , COMPONENT_SEQUENCE_ID
3320 , CONFIG_HEADER_ID
3321 , CONFIG_REV_NBR
3322 , CONFIG_DISPLAY_SEQUENCE
3323 , CONFIGURATION_ID
3324 , CONTEXT
3325
3326 , CREATED_BY
3327 , CREATION_DATE
3328 , CREDIT_INVOICE_LINE_ID
3329 , CUSTOMER_DOCK_CODE
3330 , CUSTOMER_JOB
3331 , CUSTOMER_PRODUCTION_LINE
3332 , CUST_PRODUCTION_SEQ_NUM
3333 , CUSTOMER_TRX_LINE_ID
3334 , CUST_MODEL_SERIAL_NUMBER
3335 , CUST_PO_NUMBER
3336 , CUSTOMER_LINE_NUMBER
3337 , DELIVERY_LEAD_TIME
3338 , DELIVER_TO_CONTACT_ID
3339 , DELIVER_TO_ORG_ID
3340 , DEMAND_BUCKET_TYPE_CODE
3341 , DEMAND_CLASS_CODE
3342 , DEP_PLAN_REQUIRED_FLAG
3343
3344 , EARLIEST_ACCEPTABLE_DATE
3345 , END_ITEM_UNIT_NUMBER
3346 , EXPLOSION_DATE
3347 , FIRST_ACK_CODE
3348 , FIRST_ACK_DATE
3349 , FOB_POINT_CODE
3350 , FREIGHT_CARRIER_CODE
3351 , FREIGHT_TERMS_CODE
3352 , FULFILLED_QUANTITY
3353 , FULFILLED_FLAG
3354 , FULFILLMENT_METHOD_CODE
3355 , FULFILLMENT_DATE
3356 , GLOBAL_ATTRIBUTE1
3357 , GLOBAL_ATTRIBUTE10
3358 , GLOBAL_ATTRIBUTE11
3359 , GLOBAL_ATTRIBUTE12
3360 , GLOBAL_ATTRIBUTE13
3361 , GLOBAL_ATTRIBUTE14
3362 , GLOBAL_ATTRIBUTE15
3363 , GLOBAL_ATTRIBUTE16
3364 , GLOBAL_ATTRIBUTE17
3365 , GLOBAL_ATTRIBUTE18
3366 , GLOBAL_ATTRIBUTE19
3367 , GLOBAL_ATTRIBUTE2
3368 , GLOBAL_ATTRIBUTE20
3369 , GLOBAL_ATTRIBUTE3
3370 , GLOBAL_ATTRIBUTE4
3371 , GLOBAL_ATTRIBUTE5
3372 , GLOBAL_ATTRIBUTE6
3373 , GLOBAL_ATTRIBUTE7
3374 , GLOBAL_ATTRIBUTE8
3375 , GLOBAL_ATTRIBUTE9
3376 , GLOBAL_ATTRIBUTE_CATEGORY
3377 , HEADER_ID
3378 , INDUSTRY_ATTRIBUTE1
3379 , INDUSTRY_ATTRIBUTE10
3380 , INDUSTRY_ATTRIBUTE11
3381 , INDUSTRY_ATTRIBUTE12
3382 , INDUSTRY_ATTRIBUTE13
3383 , INDUSTRY_ATTRIBUTE14
3384 , INDUSTRY_ATTRIBUTE15
3385 , INDUSTRY_ATTRIBUTE16
3386 , INDUSTRY_ATTRIBUTE17
3387 , INDUSTRY_ATTRIBUTE18
3388 , INDUSTRY_ATTRIBUTE19
3389 , INDUSTRY_ATTRIBUTE20
3390 , INDUSTRY_ATTRIBUTE21
3391 , INDUSTRY_ATTRIBUTE22
3392 , INDUSTRY_ATTRIBUTE23
3393 , INDUSTRY_ATTRIBUTE24
3394 , INDUSTRY_ATTRIBUTE25
3395 , INDUSTRY_ATTRIBUTE26
3396 , INDUSTRY_ATTRIBUTE27
3397 , INDUSTRY_ATTRIBUTE28
3398 , INDUSTRY_ATTRIBUTE29
3399 , INDUSTRY_ATTRIBUTE30
3400 , INDUSTRY_ATTRIBUTE2
3401 , INDUSTRY_ATTRIBUTE3
3402 , INDUSTRY_ATTRIBUTE4
3403 , INDUSTRY_ATTRIBUTE5
3404 , INDUSTRY_ATTRIBUTE6
3405 , INDUSTRY_ATTRIBUTE7
3406 , INDUSTRY_ATTRIBUTE8
3407 , INDUSTRY_ATTRIBUTE9
3408 , INDUSTRY_CONTEXT
3409 , INTMED_SHIP_TO_CONTACT_ID
3410 , INTMED_SHIP_TO_ORG_ID
3411 , INVENTORY_ITEM_ID
3412 , INVOICE_INTERFACE_STATUS_CODE
3413
3414
3415
3416 , INVOICE_TO_CONTACT_ID
3417 , INVOICE_TO_ORG_ID
3418 , INVOICED_QUANTITY
3419 , INVOICING_RULE_ID
3420 , ORDERED_ITEM_ID
3421 , ITEM_IDENTIFIER_TYPE
3422 , ORDERED_ITEM
3423 , ITEM_REVISION
3424 , ITEM_TYPE_CODE
3425 , LAST_ACK_CODE
3426 , LAST_ACK_DATE
3427 , LAST_UPDATED_BY
3428 , LAST_UPDATE_DATE
3429 , LAST_UPDATE_LOGIN
3430 , LATEST_ACCEPTABLE_DATE
3431 , LINE_CATEGORY_CODE
3432 , LINE_ID
3433 , LINE_NUMBER
3434 , LINE_TYPE_ID
3435 , LINK_TO_LINE_ID
3436
3437 , MODEL_GROUP_NUMBER
3438 -- , MFG_COMPONENT_SEQUENCE_ID
3439 -- , MFG_LEAD_TIME
3440 , OPEN_FLAG
3441 , OPTION_FLAG
3442 , OPTION_NUMBER
3443 , ORDERED_QUANTITY
3444 , ORDERED_QUANTITY2 --OPM 02/JUN/00
3445 , ORDER_QUANTITY_UOM
3446 , ORDERED_QUANTITY_UOM2 --OPM 02/JUN/00
3447 , ORG_ID
3448 , ORIG_SYS_DOCUMENT_REF
3449 , ORIG_SYS_LINE_REF
3450 , ORIG_SYS_SHIPMENT_REF
3451 , OVER_SHIP_REASON_CODE
3452 , OVER_SHIP_RESOLVED_FLAG
3453 , PAYMENT_TERM_ID
3454 , PLANNING_PRIORITY
3455 , PREFERRED_GRADE --OPM 02/JUN/00
3456 , PRICE_LIST_ID
3457 , PRICE_REQUEST_CODE --PROMOTIONS SEP/01
3458 , PRICING_ATTRIBUTE1
3459 , PRICING_ATTRIBUTE10
3460 , PRICING_ATTRIBUTE2
3461 , PRICING_ATTRIBUTE3
3462 , PRICING_ATTRIBUTE4
3463 , PRICING_ATTRIBUTE5
3464 , PRICING_ATTRIBUTE6
3465 , PRICING_ATTRIBUTE7
3466 , PRICING_ATTRIBUTE8
3467 , PRICING_ATTRIBUTE9
3468 , PRICING_CONTEXT
3469 , PRICING_DATE
3470 , PRICING_QUANTITY
3471 , PRICING_QUANTITY_UOM
3472 , PROGRAM_APPLICATION_ID
3473 , PROGRAM_ID
3474 , PROGRAM_UPDATE_DATE
3475 , PROJECT_ID
3476 , PROMISE_DATE
3477 , RE_SOURCE_FLAG
3478 , REFERENCE_CUSTOMER_TRX_LINE_ID
3479 , REFERENCE_HEADER_ID
3480 , REFERENCE_LINE_ID
3481 , REFERENCE_TYPE
3482
3483 , REQUEST_DATE
3484 , REQUEST_ID
3485 , RETURN_ATTRIBUTE1
3486 , RETURN_ATTRIBUTE10
3487 , RETURN_ATTRIBUTE11
3488 , RETURN_ATTRIBUTE12
3489 , RETURN_ATTRIBUTE13
3490 , RETURN_ATTRIBUTE14
3491 , RETURN_ATTRIBUTE15
3492 , RETURN_ATTRIBUTE2
3493 , RETURN_ATTRIBUTE3
3494 , RETURN_ATTRIBUTE4
3495 , RETURN_ATTRIBUTE5
3496 , RETURN_ATTRIBUTE6
3497 , RETURN_ATTRIBUTE7
3498 , RETURN_ATTRIBUTE8
3499 , RETURN_ATTRIBUTE9
3500 , RETURN_CONTEXT
3501 , RETURN_REASON_CODE
3502 , RLA_SCHEDULE_TYPE_CODE
3503 , SALESREP_ID
3504 , SCHEDULE_ARRIVAL_DATE
3505 , SCHEDULE_SHIP_DATE
3506 , SCHEDULE_STATUS_CODE
3507 , SHIPMENT_NUMBER
3508 , SHIPMENT_PRIORITY_CODE
3509 , SHIPPED_QUANTITY
3510 , SHIPPED_QUANTITY2 -- OPM B1661023 04/02/01
3511 , SHIPPING_METHOD_CODE
3512 , SHIPPING_QUANTITY
3513 , SHIPPING_QUANTITY2 -- OPM B1661023 04/02/01
3514 , SHIPPING_QUANTITY_UOM
3515 , SHIP_FROM_ORG_ID
3516 , SUBINVENTORY
3517 , SHIP_SET_ID
3518 , SHIP_TOLERANCE_ABOVE
3519 , SHIP_TOLERANCE_BELOW
3520 , SHIPPABLE_FLAG
3521 , SHIPPING_INTERFACED_FLAG
3522 , SHIP_TO_CONTACT_ID
3523 , SHIP_TO_ORG_ID
3524 , SHIP_MODEL_COMPLETE_FLAG
3525 , SOLD_TO_ORG_ID
3526 , SOLD_FROM_ORG_ID
3527 , SORT_ORDER
3528 , SOURCE_DOCUMENT_ID
3529 , SOURCE_DOCUMENT_LINE_ID
3530 , SOURCE_DOCUMENT_TYPE_ID
3531 , SOURCE_TYPE_CODE
3532 , SPLIT_FROM_LINE_ID
3533 , LINE_SET_ID
3534 , SPLIT_BY
3535 , MODEL_REMNANT_FLAG
3536 , TASK_ID
3537 , TAX_CODE
3538 , TAX_DATE
3539 , TAX_EXEMPT_FLAG
3540 , TAX_EXEMPT_NUMBER
3541 , TAX_EXEMPT_REASON_CODE
3542 , TAX_POINT_CODE
3543 , TAX_RATE
3544 , TAX_VALUE
3545 , TOP_MODEL_LINE_ID
3546 , UNIT_LIST_PRICE
3547 , UNIT_LIST_PRICE_PER_PQTY
3548 , UNIT_SELLING_PRICE
3549 , UNIT_SELLING_PRICE_PER_PQTY
3550 , VISIBLE_DEMAND_FLAG
3551 , VEH_CUS_ITEM_CUM_KEY_ID
3552 , SHIPPING_INSTRUCTIONS
3553 , PACKING_INSTRUCTIONS
3554 , SERVICE_TXN_REASON_CODE
3555 , SERVICE_TXN_COMMENTS
3556 , SERVICE_DURATION
3557 , SERVICE_PERIOD
3558 , SERVICE_START_DATE
3559 , SERVICE_END_DATE
3560 , SERVICE_COTERMINATE_FLAG
3561 , UNIT_LIST_PERCENT
3562 , UNIT_SELLING_PERCENT
3563 , UNIT_PERCENT_BASE_PRICE
3564 , SERVICE_NUMBER
3565 , SERVICE_REFERENCE_TYPE_CODE
3566 , SERVICE_REFERENCE_LINE_ID
3567 , SERVICE_REFERENCE_SYSTEM_ID
3568 , TP_CONTEXT
3569 , TP_ATTRIBUTE1
3570 , TP_ATTRIBUTE2
3571 , TP_ATTRIBUTE3
3572 , TP_ATTRIBUTE4
3573 , TP_ATTRIBUTE5
3574 , TP_ATTRIBUTE6
3575 , TP_ATTRIBUTE7
3576 , TP_ATTRIBUTE8
3577 , TP_ATTRIBUTE9
3578 , TP_ATTRIBUTE10
3579 , TP_ATTRIBUTE11
3580 , TP_ATTRIBUTE12
3581 , TP_ATTRIBUTE13
3582 , TP_ATTRIBUTE14
3583 , TP_ATTRIBUTE15
3584 , FLOW_STATUS_CODE
3585 -- , MARKETING_SOURCE_CODE_ID
3586 , CALCULATE_PRICE_FLAG
3587 , COMMITMENT_ID
3588 , ORDER_SOURCE_ID -- aksingh
3589 -- , upgraded_flag
3590 , ORIGINAL_INVENTORY_ITEM_ID
3591 , ORIGINAL_ITEM_IDENTIFIER_TYPE
3592 , ORIGINAL_ORDERED_ITEM_ID
3593 , ORIGINAL_ORDERED_ITEM
3594 , ITEM_RELATIONSHIP_TYPE
3595 , ITEM_SUBSTITUTION_TYPE_CODE
3596 , LATE_DEMAND_PENALTY_FACTOR
3597 , OVERRIDE_ATP_DATE_CODE
3598 -- , FIRM_DEMAND_FLAG
3599 -- , EARLIEST_SHIP_DATE
3600 , USER_ITEM_DESCRIPTION
3601 , BLANKET_NUMBER
3602 , BLANKET_LINE_NUMBER
3603 , BLANKET_VERSION_NUMBER
3604 --MRG B
3605 -- , UNIT_COST
3606 --MRG E
3607 -- , LOCK_CONTROL
3608 , NVL(OPTION_NUMBER, -1) OPN
3609 , NVL(COMPONENT_NUMBER, -1) CPN
3610 , NVL(SERVICE_NUMBER, -1) SVN
3611 , CHANGE_SEQUENCE
3612 --key Transaction Dates
3613 , order_firmed_date
3614 , actual_fulfillment_date
3615 FROM OE_ORDER_LINES_HISTORY
3616 WHERE HEADER_ID = p_header_id
3617 AND VERSION_NUMBER = l_version_number
3618 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
3619 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
3620 AND VERSION_FLAG = 'Y'))
3621 ORDER BY LINE_NUMBER,SHIPMENT_NUMBER,OPN, CPN, SVN;
3622
3623
3624 CURSOR l_line_csr_s IS
3625 SELECT ACCOUNTING_RULE_ID
3626 , ACCOUNTING_RULE_DURATION
3627 , ACTUAL_ARRIVAL_DATE
3628 , ACTUAL_SHIPMENT_DATE
3629 , AGREEMENT_ID
3630 , ARRIVAL_SET_ID
3631 , ATO_LINE_ID
3632 , ATTRIBUTE1
3633 , ATTRIBUTE10
3634 , ATTRIBUTE11
3635 , ATTRIBUTE12
3636 , ATTRIBUTE13
3637 , ATTRIBUTE14
3638 , ATTRIBUTE15
3639 , ATTRIBUTE16 --For bug 2184255
3640 , ATTRIBUTE17
3641 , ATTRIBUTE18
3642 , ATTRIBUTE19
3643 , ATTRIBUTE2
3644 , ATTRIBUTE20
3645 , ATTRIBUTE3
3646 , ATTRIBUTE4
3647 , ATTRIBUTE5
3648 , ATTRIBUTE6
3649 , ATTRIBUTE7
3650 , ATTRIBUTE8
3651 , ATTRIBUTE9
3652 , AUTO_SELECTED_QUANTITY
3653 , AUTHORIZED_TO_SHIP_FLAG
3654 , BOOKED_FLAG
3655 , CANCELLED_FLAG
3656 , CANCELLED_QUANTITY
3657 , COMPONENT_CODE
3658 , COMPONENT_NUMBER
3659 , COMPONENT_SEQUENCE_ID
3660 , CONFIG_HEADER_ID
3661 , CONFIG_REV_NBR
3662 , CONFIG_DISPLAY_SEQUENCE
3663 , CONFIGURATION_ID
3664 , CONTEXT
3665
3666 , CREATED_BY
3667 , CREATION_DATE
3668 , CREDIT_INVOICE_LINE_ID
3669 , CUSTOMER_DOCK_CODE
3670 , CUSTOMER_JOB
3671 , CUSTOMER_PRODUCTION_LINE
3672 , CUST_PRODUCTION_SEQ_NUM
3673 , CUSTOMER_TRX_LINE_ID
3674 , CUST_MODEL_SERIAL_NUMBER
3675 , CUST_PO_NUMBER
3676 , CUSTOMER_LINE_NUMBER
3677 , DELIVERY_LEAD_TIME
3678 , DELIVER_TO_CONTACT_ID
3679 , DELIVER_TO_ORG_ID
3680 , DEMAND_BUCKET_TYPE_CODE
3681 , DEMAND_CLASS_CODE
3682 , DEP_PLAN_REQUIRED_FLAG
3683
3684 , EARLIEST_ACCEPTABLE_DATE
3685 , END_ITEM_UNIT_NUMBER
3686 , EXPLOSION_DATE
3687 , FIRST_ACK_CODE
3688 , FIRST_ACK_DATE
3689 , FOB_POINT_CODE
3690 , FREIGHT_CARRIER_CODE
3691 , FREIGHT_TERMS_CODE
3692 , FULFILLED_QUANTITY
3693 , FULFILLED_FLAG
3694 , FULFILLMENT_METHOD_CODE
3695 , FULFILLMENT_DATE
3696 , GLOBAL_ATTRIBUTE1
3697 , GLOBAL_ATTRIBUTE10
3698 , GLOBAL_ATTRIBUTE11
3699 , GLOBAL_ATTRIBUTE12
3700 , GLOBAL_ATTRIBUTE13
3701 , GLOBAL_ATTRIBUTE14
3702 , GLOBAL_ATTRIBUTE15
3703 , GLOBAL_ATTRIBUTE16
3704 , GLOBAL_ATTRIBUTE17
3705 , GLOBAL_ATTRIBUTE18
3706 , GLOBAL_ATTRIBUTE19
3707 , GLOBAL_ATTRIBUTE2
3708 , GLOBAL_ATTRIBUTE20
3709 , GLOBAL_ATTRIBUTE3
3710 , GLOBAL_ATTRIBUTE4
3711 , GLOBAL_ATTRIBUTE5
3712 , GLOBAL_ATTRIBUTE6
3713 , GLOBAL_ATTRIBUTE7
3714 , GLOBAL_ATTRIBUTE8
3715 , GLOBAL_ATTRIBUTE9
3716 , GLOBAL_ATTRIBUTE_CATEGORY
3717 , HEADER_ID
3718 , INDUSTRY_ATTRIBUTE1
3719 , INDUSTRY_ATTRIBUTE10
3720 , INDUSTRY_ATTRIBUTE11
3721 , INDUSTRY_ATTRIBUTE12
3722 , INDUSTRY_ATTRIBUTE13
3723 , INDUSTRY_ATTRIBUTE14
3724 , INDUSTRY_ATTRIBUTE15
3725 , INDUSTRY_ATTRIBUTE16
3726 , INDUSTRY_ATTRIBUTE17
3727 , INDUSTRY_ATTRIBUTE18
3728 , INDUSTRY_ATTRIBUTE19
3729 , INDUSTRY_ATTRIBUTE20
3730 , INDUSTRY_ATTRIBUTE21
3731 , INDUSTRY_ATTRIBUTE22
3732 , INDUSTRY_ATTRIBUTE23
3733 , INDUSTRY_ATTRIBUTE24
3734 , INDUSTRY_ATTRIBUTE25
3735 , INDUSTRY_ATTRIBUTE26
3736 , INDUSTRY_ATTRIBUTE27
3737 , INDUSTRY_ATTRIBUTE28
3738 , INDUSTRY_ATTRIBUTE29
3739 , INDUSTRY_ATTRIBUTE30
3740 , INDUSTRY_ATTRIBUTE2
3741 , INDUSTRY_ATTRIBUTE3
3742 , INDUSTRY_ATTRIBUTE4
3743 , INDUSTRY_ATTRIBUTE5
3744 , INDUSTRY_ATTRIBUTE6
3745 , INDUSTRY_ATTRIBUTE7
3746 , INDUSTRY_ATTRIBUTE8
3747 , INDUSTRY_ATTRIBUTE9
3748 , INDUSTRY_CONTEXT
3749 , INTMED_SHIP_TO_CONTACT_ID
3750 , INTMED_SHIP_TO_ORG_ID
3751 , INVENTORY_ITEM_ID
3752 , INVOICE_INTERFACE_STATUS_CODE
3753
3754
3755
3756 , INVOICE_TO_CONTACT_ID
3757 , INVOICE_TO_ORG_ID
3758 , INVOICED_QUANTITY
3759 , INVOICING_RULE_ID
3760 , ORDERED_ITEM_ID
3761 , ITEM_IDENTIFIER_TYPE
3762 , ORDERED_ITEM
3763 , ITEM_REVISION
3764 , ITEM_TYPE_CODE
3765 , LAST_ACK_CODE
3766 , LAST_ACK_DATE
3767 , LAST_UPDATED_BY
3768 , LAST_UPDATE_DATE
3769 , LAST_UPDATE_LOGIN
3770 , LATEST_ACCEPTABLE_DATE
3771 , LINE_CATEGORY_CODE
3772 , LINE_ID
3773 , LINE_NUMBER
3774 , LINE_TYPE_ID
3775 , LINK_TO_LINE_ID
3776
3777 , MODEL_GROUP_NUMBER
3778 -- , MFG_COMPONENT_SEQUENCE_ID
3779 -- , MFG_LEAD_TIME
3780 , OPEN_FLAG
3781 , OPTION_FLAG
3782 , OPTION_NUMBER
3783 , ORDERED_QUANTITY
3784 , ORDERED_QUANTITY2 --OPM 02/JUN/00
3785 , ORDER_QUANTITY_UOM
3786 , ORDERED_QUANTITY_UOM2 --OPM 02/JUN/00
3787 , ORG_ID
3788 , ORIG_SYS_DOCUMENT_REF
3789 , ORIG_SYS_LINE_REF
3790 , ORIG_SYS_SHIPMENT_REF
3791 , OVER_SHIP_REASON_CODE
3792 , OVER_SHIP_RESOLVED_FLAG
3793 , PAYMENT_TERM_ID
3794 , PLANNING_PRIORITY
3795 , PREFERRED_GRADE --OPM 02/JUN/00
3796 , PRICE_LIST_ID
3797 , PRICE_REQUEST_CODE --PROMOTIONS SEP/01
3798 , PRICING_ATTRIBUTE1
3799 , PRICING_ATTRIBUTE10
3800 , PRICING_ATTRIBUTE2
3801 , PRICING_ATTRIBUTE3
3802 , PRICING_ATTRIBUTE4
3803 , PRICING_ATTRIBUTE5
3804 , PRICING_ATTRIBUTE6
3805 , PRICING_ATTRIBUTE7
3806 , PRICING_ATTRIBUTE8
3807 , PRICING_ATTRIBUTE9
3808 , PRICING_CONTEXT
3809 , PRICING_DATE
3810 , PRICING_QUANTITY
3811 , PRICING_QUANTITY_UOM
3812 , PROGRAM_APPLICATION_ID
3813 , PROGRAM_ID
3814 , PROGRAM_UPDATE_DATE
3815 , PROJECT_ID
3816 , PROMISE_DATE
3817 , RE_SOURCE_FLAG
3818 , REFERENCE_CUSTOMER_TRX_LINE_ID
3819 , REFERENCE_HEADER_ID
3820 , REFERENCE_LINE_ID
3821 , REFERENCE_TYPE
3822
3823 , REQUEST_DATE
3824 , REQUEST_ID
3825 , RETURN_ATTRIBUTE1
3826 , RETURN_ATTRIBUTE10
3827 , RETURN_ATTRIBUTE11
3828 , RETURN_ATTRIBUTE12
3829 , RETURN_ATTRIBUTE13
3830 , RETURN_ATTRIBUTE14
3831 , RETURN_ATTRIBUTE15
3832 , RETURN_ATTRIBUTE2
3833 , RETURN_ATTRIBUTE3
3834 , RETURN_ATTRIBUTE4
3835 , RETURN_ATTRIBUTE5
3836 , RETURN_ATTRIBUTE6
3837 , RETURN_ATTRIBUTE7
3838 , RETURN_ATTRIBUTE8
3839 , RETURN_ATTRIBUTE9
3840 , RETURN_CONTEXT
3841 , RETURN_REASON_CODE
3842 , RLA_SCHEDULE_TYPE_CODE
3843 , SALESREP_ID
3844 , SCHEDULE_ARRIVAL_DATE
3845 , SCHEDULE_SHIP_DATE
3846 , SCHEDULE_STATUS_CODE
3847 , SHIPMENT_NUMBER
3848 , SHIPMENT_PRIORITY_CODE
3849 , SHIPPED_QUANTITY
3850 , SHIPPED_QUANTITY2 -- OPM B1661023 04/02/01
3851 , SHIPPING_METHOD_CODE
3852 , SHIPPING_QUANTITY
3853 , SHIPPING_QUANTITY2 -- OPM B1661023 04/02/01
3854 , SHIPPING_QUANTITY_UOM
3855 , SHIP_FROM_ORG_ID
3856 , SUBINVENTORY
3857 , SHIP_SET_ID
3858 , SHIP_TOLERANCE_ABOVE
3859 , SHIP_TOLERANCE_BELOW
3860 , SHIPPABLE_FLAG
3861 , SHIPPING_INTERFACED_FLAG
3862 , SHIP_TO_CONTACT_ID
3863 , SHIP_TO_ORG_ID
3864 , SHIP_MODEL_COMPLETE_FLAG
3865 , SOLD_TO_ORG_ID
3866 , SOLD_FROM_ORG_ID
3867 , SORT_ORDER
3868 , SOURCE_DOCUMENT_ID
3869 , SOURCE_DOCUMENT_LINE_ID
3870 , SOURCE_DOCUMENT_TYPE_ID
3871 , SOURCE_TYPE_CODE
3872 , SPLIT_FROM_LINE_ID
3873 , LINE_SET_ID
3874 , SPLIT_BY
3875 , MODEL_REMNANT_FLAG
3876 , TASK_ID
3877 , TAX_CODE
3878 , TAX_DATE
3879 , TAX_EXEMPT_FLAG
3880 , TAX_EXEMPT_NUMBER
3881 , TAX_EXEMPT_REASON_CODE
3882 , TAX_POINT_CODE
3883 , TAX_RATE
3884 , TAX_VALUE
3885 , TOP_MODEL_LINE_ID
3886 , UNIT_LIST_PRICE
3887 , UNIT_LIST_PRICE_PER_PQTY
3888 , UNIT_SELLING_PRICE
3889 , UNIT_SELLING_PRICE_PER_PQTY
3890 , VISIBLE_DEMAND_FLAG
3891 , VEH_CUS_ITEM_CUM_KEY_ID
3892 , SHIPPING_INSTRUCTIONS
3893 , PACKING_INSTRUCTIONS
3894 , SERVICE_TXN_REASON_CODE
3895 , SERVICE_TXN_COMMENTS
3896 , SERVICE_DURATION
3897 , SERVICE_PERIOD
3898 , SERVICE_START_DATE
3899 , SERVICE_END_DATE
3900 , SERVICE_COTERMINATE_FLAG
3901 , UNIT_LIST_PERCENT
3902 , UNIT_SELLING_PERCENT
3903 , UNIT_PERCENT_BASE_PRICE
3904 , SERVICE_NUMBER
3905 , SERVICE_REFERENCE_TYPE_CODE
3906 , SERVICE_REFERENCE_LINE_ID
3907 , SERVICE_REFERENCE_SYSTEM_ID
3908 , TP_CONTEXT
3909 , TP_ATTRIBUTE1
3910 , TP_ATTRIBUTE2
3911 , TP_ATTRIBUTE3
3912 , TP_ATTRIBUTE4
3913 , TP_ATTRIBUTE5
3914 , TP_ATTRIBUTE6
3915 , TP_ATTRIBUTE7
3916 , TP_ATTRIBUTE8
3917 , TP_ATTRIBUTE9
3918 , TP_ATTRIBUTE10
3919 , TP_ATTRIBUTE11
3920 , TP_ATTRIBUTE12
3921 , TP_ATTRIBUTE13
3922 , TP_ATTRIBUTE14
3923 , TP_ATTRIBUTE15
3924 , FLOW_STATUS_CODE
3925 -- , MARKETING_SOURCE_CODE_ID
3926 , CALCULATE_PRICE_FLAG
3927 , COMMITMENT_ID
3928 , ORDER_SOURCE_ID -- aksingh
3929 -- , upgraded_flag
3930 , ORIGINAL_INVENTORY_ITEM_ID
3931 , ORIGINAL_ITEM_IDENTIFIER_TYPE
3932 , ORIGINAL_ORDERED_ITEM_ID
3933 , ORIGINAL_ORDERED_ITEM
3934 , ITEM_RELATIONSHIP_TYPE
3935 , ITEM_SUBSTITUTION_TYPE_CODE
3936 , LATE_DEMAND_PENALTY_FACTOR
3937 , OVERRIDE_ATP_DATE_CODE
3938 -- , FIRM_DEMAND_FLAG
3939 -- , EARLIEST_SHIP_DATE
3940 , USER_ITEM_DESCRIPTION
3941 , BLANKET_NUMBER
3942 , BLANKET_LINE_NUMBER
3943 , BLANKET_VERSION_NUMBER
3944 --MRG B
3945 -- , UNIT_COST
3946 --MRG E
3947 -- , LOCK_CONTROL
3948 , NVL(OPTION_NUMBER, -1) OPN
3949 , NVL(COMPONENT_NUMBER, -1) CPN
3950 , NVL(SERVICE_NUMBER, -1) SVN
3951 , CHANGE_SEQUENCE
3952 --key Transaction Dates
3953 , order_firmed_date
3954 , actual_fulfillment_date
3955 FROM OE_ORDER_LINES_HISTORY
3956 WHERE LINE_SET_ID = p_line_set_id
3957 AND VERSION_NUMBER = l_version_number
3958 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
3959 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
3960 AND VERSION_FLAG = 'Y'))
3961 ORDER BY LINE_NUMBER,SHIPMENT_NUMBER,OPN, CPN, SVN;
3962
3963 l_rec l_line_csr_l%ROWTYPE;
3964 BEGIN
3965
3966 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
3967 l_current_version_number := Get_Version_Number(p_header_id => p_header_id, p_line_id => p_line_id);
3968
3969 IF l_version_number >= l_current_version_number THEN
3970 l_version_number := NULL;
3971 END IF;
3972 END IF;
3973
3974 IF l_version_number IS NULL THEN
3975 OE_Line_Util.Query_Rows(p_header_id => p_header_id, p_line_id => p_line_id, x_line_tbl => x_line_tbl);
3976 ELSE
3977
3978 IF
3979 (p_line_id IS NOT NULL
3980 AND
3981 p_line_id <> FND_API.G_MISS_NUM)
3982 AND
3983 (p_header_id IS NOT NULL
3984 AND
3985 p_header_id <> FND_API.G_MISS_NUM)
3986 THEN
3987
3988 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3989
3990 END IF;
3991
3992 -----------------------------------------------------------------
3993 -- Fix bug 1275972: Setup the l_entity variable based on the ID
3994 -- variable that is passed.
3995 -----------------------------------------------------------------
3996
3997 IF nvl(p_line_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3998 l_entity := 1;
3999 OPEN l_line_csr_l;
4000 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4001 l_entity := 2;
4002 OPEN l_line_csr_h;
4003 ELSIF nvl(p_line_set_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4004 l_entity := 3;
4005 OPEN l_line_csr_s;
4006 END IF;
4007
4008 l_org_id := OE_GLOBALS.G_ORG_ID;
4009 if l_org_id IS NULL THEN
4010 OE_GLOBALS.Set_Context;
4011 l_org_id := OE_GLOBALS.G_ORG_ID;
4012 end if;
4013
4014 oe_debug_pub.add('Entering '||to_char(l_org_id), 1);
4015
4016
4017 -- Loop over fetched records
4018
4019 l_count := 1;
4020
4021 LOOP
4022
4023 IF l_entity = 1 THEN
4024 FETCH l_line_csr_l INTO l_rec;
4025 EXIT WHEN l_line_csr_l%NOTFOUND;
4026 ELSIF l_entity = 2 THEN
4027 FETCH l_line_csr_h INTO l_rec;
4028 EXIT WHEN l_line_csr_h%NOTFOUND;
4029 ELSIF l_entity = 3 THEN
4030 FETCH l_line_csr_s INTO l_rec;
4031 EXIT WHEN l_line_csr_s%NOTFOUND;
4032 ELSE
4033 EXIT;
4034 END IF;
4035
4036 x_line_tbl(l_count).accounting_rule_id := l_rec.ACCOUNTING_RULE_ID;
4037 x_line_tbl(l_count).accounting_rule_duration := l_rec.ACCOUNTING_RULE_DURATION;
4038 x_line_tbl(l_count).actual_arrival_date := l_rec.ACTUAL_ARRIVAL_DATE;
4039 x_line_tbl(l_count).actual_shipment_date := l_rec.ACTUAL_SHIPMENT_DATE;
4040 x_line_tbl(l_count).agreement_id := l_rec.AGREEMENT_ID;
4041 x_line_tbl(l_count).arrival_Set_id := l_rec.ARRIVAL_SET_ID;
4042 x_line_tbl(l_count).ato_line_id := l_rec.ATO_LINE_ID;
4043 x_line_tbl(l_count).attribute1 := l_rec.ATTRIBUTE1;
4044 x_line_tbl(l_count).attribute10 := l_rec.ATTRIBUTE10;
4045 x_line_tbl(l_count).attribute11 := l_rec.ATTRIBUTE11;
4046 x_line_tbl(l_count).attribute12 := l_rec.ATTRIBUTE12;
4047 x_line_tbl(l_count).attribute13 := l_rec.ATTRIBUTE13;
4048 x_line_tbl(l_count).attribute14 := l_rec.ATTRIBUTE14;
4049 x_line_tbl(l_count).attribute15 := l_rec.ATTRIBUTE15;
4050 x_line_tbl(l_count).attribute16 := l_rec.ATTRIBUTE16; --For bug 2184255
4051 x_line_tbl(l_count).attribute17 := l_rec.ATTRIBUTE17;
4052 x_line_tbl(l_count).attribute18 := l_rec.ATTRIBUTE18;
4053 x_line_tbl(l_count).attribute19 := l_rec.ATTRIBUTE19;
4054 x_line_tbl(l_count).attribute2 := l_rec.ATTRIBUTE2;
4055 x_line_tbl(l_count).attribute20 := l_rec.ATTRIBUTE20;
4056 x_line_tbl(l_count).attribute3 := l_rec.ATTRIBUTE3;
4057 x_line_tbl(l_count).attribute4 := l_rec.ATTRIBUTE4;
4058 x_line_tbl(l_count).attribute5 := l_rec.ATTRIBUTE5;
4059 x_line_tbl(l_count).attribute6 := l_rec.ATTRIBUTE6;
4060 x_line_tbl(l_count).attribute7 := l_rec.ATTRIBUTE7;
4061 x_line_tbl(l_count).attribute8 := l_rec.ATTRIBUTE8;
4062 x_line_tbl(l_count).attribute9 := l_rec.ATTRIBUTE9;
4063 x_line_tbl(l_count).auto_selected_quantity := l_rec.AUTO_SELECTED_QUANTITY;
4064 x_line_tbl(l_count).Authorized_to_ship_flag := l_rec.Authorized_to_ship_flag;
4065 x_line_tbl(l_count).booked_flag := l_rec.booked_flag;
4066 x_line_tbl(l_count).cancelled_flag := l_rec.cancelled_flag;
4067 x_line_tbl(l_count).cancelled_quantity := l_rec.CANCELLED_QUANTITY;
4068 x_line_tbl(l_count).component_code := l_rec.COMPONENT_CODE;
4069 x_line_tbl(l_count).component_number := l_rec.COMPONENT_NUMBER;
4070 x_line_tbl(l_count).component_sequence_id := l_rec.COMPONENT_SEQUENCE_ID;
4071 x_line_tbl(l_count).config_header_id := l_rec.CONFIG_HEADER_ID;
4072 x_line_tbl(l_count).config_rev_nbr := l_rec.CONFIG_REV_NBR;
4073 x_line_tbl(l_count).config_display_sequence := l_rec.CONFIG_DISPLAY_SEQUENCE;
4074 x_line_tbl(l_count).configuration_id := l_rec.CONFIGURATION_ID;
4075 x_line_tbl(l_count).context := l_rec.CONTEXT;
4076
4077 x_line_tbl(l_count).created_by := l_rec.CREATED_BY;
4078 x_line_tbl(l_count).creation_date := l_rec.CREATION_DATE;
4079 x_line_tbl(l_count).credit_invoice_line_id := l_rec.CREDIT_INVOICE_LINE_ID;
4080 x_line_tbl(l_count).customer_dock_code := l_rec.CUSTOMER_DOCK_CODE;
4081 x_line_tbl(l_count).customer_job := l_rec.CUSTOMER_JOB;
4082 x_line_tbl(l_count).customer_production_line := l_rec.CUSTOMER_PRODUCTION_LINE;
4083 x_line_tbl(l_count).cust_production_seq_num := l_rec.CUST_PRODUCTION_SEQ_NUM;
4084 x_line_tbl(l_count).customer_trx_line_id := l_rec.CUSTOMER_TRX_LINE_ID;
4085 x_line_tbl(l_count).cust_model_serial_number := l_rec.CUST_MODEL_SERIAL_NUMBER;
4086 x_line_tbl(l_count).cust_po_number := l_rec.CUST_PO_NUMBER;
4087 x_line_tbl(l_count).customer_line_number := l_rec.CUSTOMER_LINE_NUMBER;
4088 x_line_tbl(l_count).delivery_lead_time := l_rec.DELIVERY_LEAD_TIME;
4089 x_line_tbl(l_count).deliver_to_contact_id := l_rec.DELIVER_TO_CONTACT_ID;
4090 x_line_tbl(l_count).deliver_to_org_id := l_rec.DELIVER_TO_ORG_ID;
4091 x_line_tbl(l_count).demand_bucket_type_code := l_rec.DEMAND_BUCKET_TYPE_CODE;
4092 x_line_tbl(l_count).demand_class_code := l_rec.DEMAND_CLASS_CODE;
4093 x_line_tbl(l_count).dep_plan_required_flag := l_rec.DEP_PLAN_REQUIRED_FLAG;
4094
4095 x_line_tbl(l_count).earliest_acceptable_date := l_rec.EARLIEST_ACCEPTABLE_DATE;
4096 x_line_tbl(l_count).end_item_unit_number := l_rec.END_ITEM_UNIT_NUMBER;
4097 x_line_tbl(l_count).explosion_date := l_rec.EXPLOSION_DATE;
4098 x_line_tbl(l_count).first_ack_code := l_rec.FIRST_ACK_CODE;
4099 x_line_tbl(l_count).first_ack_date := l_rec.FIRST_ACK_DATE;
4100 x_line_tbl(l_count).fob_point_code := l_rec.FOB_POINT_CODE;
4101 x_line_tbl(l_count).freight_carrier_code := l_rec.FREIGHT_CARRIER_CODE;
4102 x_line_tbl(l_count).freight_terms_code := l_rec.FREIGHT_TERMS_CODE;
4103 x_line_tbl(l_count).fulfilled_quantity := l_rec.FULFILLED_QUANTITY;
4104 x_line_tbl(l_count).fulfilled_flag := l_rec.FULFILLED_FLAG;
4105 x_line_tbl(l_count).fulfillment_method_code := l_rec.FULFILLMENT_METHOD_CODE;
4106 x_line_tbl(l_count).fulfillment_date := l_rec.FULFILLMENT_DATE;
4107 x_line_tbl(l_count).global_attribute1 := l_rec.GLOBAL_ATTRIBUTE1;
4108 x_line_tbl(l_count).global_attribute10 := l_rec.GLOBAL_ATTRIBUTE10;
4109 x_line_tbl(l_count).global_attribute11 := l_rec.GLOBAL_ATTRIBUTE11;
4110 x_line_tbl(l_count).global_attribute12 := l_rec.GLOBAL_ATTRIBUTE12;
4111 x_line_tbl(l_count).global_attribute13 := l_rec.GLOBAL_ATTRIBUTE13;
4112 x_line_tbl(l_count).global_attribute14 := l_rec.GLOBAL_ATTRIBUTE14;
4113 x_line_tbl(l_count).global_attribute15 := l_rec.GLOBAL_ATTRIBUTE15;
4114 x_line_tbl(l_count).global_attribute16 := l_rec.GLOBAL_ATTRIBUTE16;
4115 x_line_tbl(l_count).global_attribute17 := l_rec.GLOBAL_ATTRIBUTE17;
4116 x_line_tbl(l_count).global_attribute18 := l_rec.GLOBAL_ATTRIBUTE18;
4117 x_line_tbl(l_count).global_attribute19 := l_rec.GLOBAL_ATTRIBUTE19;
4118 x_line_tbl(l_count).global_attribute2 := l_rec.GLOBAL_ATTRIBUTE2;
4119 x_line_tbl(l_count).global_attribute20 := l_rec.GLOBAL_ATTRIBUTE20;
4120 x_line_tbl(l_count).global_attribute3 := l_rec.GLOBAL_ATTRIBUTE3;
4121 x_line_tbl(l_count).global_attribute4 := l_rec.GLOBAL_ATTRIBUTE4;
4122 x_line_tbl(l_count).global_attribute5 := l_rec.GLOBAL_ATTRIBUTE5;
4123 x_line_tbl(l_count).global_attribute6 := l_rec.GLOBAL_ATTRIBUTE6;
4124 x_line_tbl(l_count).global_attribute7 := l_rec.GLOBAL_ATTRIBUTE7;
4125 x_line_tbl(l_count).global_attribute8 := l_rec.GLOBAL_ATTRIBUTE8;
4126 x_line_tbl(l_count).global_attribute9 := l_rec.GLOBAL_ATTRIBUTE9;
4127 x_line_tbl(l_count).global_attribute_category := l_rec.GLOBAL_ATTRIBUTE_CATEGORY;
4128 x_line_tbl(l_count).header_id := l_rec.HEADER_ID;
4129 x_line_tbl(l_count).industry_attribute1 := l_rec.INDUSTRY_ATTRIBUTE1;
4130 x_line_tbl(l_count).industry_attribute10 := l_rec.INDUSTRY_ATTRIBUTE10;
4131 x_line_tbl(l_count).industry_attribute11 := l_rec.INDUSTRY_ATTRIBUTE11;
4132 x_line_tbl(l_count).industry_attribute12 := l_rec.INDUSTRY_ATTRIBUTE12;
4133 x_line_tbl(l_count).industry_attribute13 := l_rec.INDUSTRY_ATTRIBUTE13;
4134 x_line_tbl(l_count).industry_attribute14 := l_rec.INDUSTRY_ATTRIBUTE14;
4135 x_line_tbl(l_count).industry_attribute15 := l_rec.INDUSTRY_ATTRIBUTE15;
4136 x_line_tbl(l_count).industry_attribute16 := l_rec.INDUSTRY_ATTRIBUTE16;
4137 x_line_tbl(l_count).industry_attribute17 := l_rec.INDUSTRY_ATTRIBUTE17;
4138 x_line_tbl(l_count).industry_attribute18 := l_rec.INDUSTRY_ATTRIBUTE18;
4139 x_line_tbl(l_count).industry_attribute19 := l_rec.INDUSTRY_ATTRIBUTE19;
4140 x_line_tbl(l_count).industry_attribute20 := l_rec.INDUSTRY_ATTRIBUTE20;
4141 x_line_tbl(l_count).industry_attribute21 := l_rec.INDUSTRY_ATTRIBUTE21;
4142 x_line_tbl(l_count).industry_attribute22 := l_rec.INDUSTRY_ATTRIBUTE22;
4143 x_line_tbl(l_count).industry_attribute23:= l_rec.INDUSTRY_ATTRIBUTE23;
4144 x_line_tbl(l_count).industry_attribute24 := l_rec.INDUSTRY_ATTRIBUTE24;
4145 x_line_tbl(l_count).industry_attribute25 := l_rec.INDUSTRY_ATTRIBUTE25;
4146 x_line_tbl(l_count).industry_attribute26 := l_rec.INDUSTRY_ATTRIBUTE26;
4147 x_line_tbl(l_count).industry_attribute27 := l_rec.INDUSTRY_ATTRIBUTE27;
4148 x_line_tbl(l_count).industry_attribute28 := l_rec.INDUSTRY_ATTRIBUTE28;
4149 x_line_tbl(l_count).industry_attribute29 := l_rec.INDUSTRY_ATTRIBUTE29;
4150 x_line_tbl(l_count).industry_attribute30 := l_rec.INDUSTRY_ATTRIBUTE30;
4151 x_line_tbl(l_count).industry_attribute2 := l_rec.INDUSTRY_ATTRIBUTE2;
4152 x_line_tbl(l_count).industry_attribute3 := l_rec.INDUSTRY_ATTRIBUTE3;
4153 x_line_tbl(l_count).industry_attribute4 := l_rec.INDUSTRY_ATTRIBUTE4;
4154 x_line_tbl(l_count).industry_attribute5 := l_rec.INDUSTRY_ATTRIBUTE5;
4155 x_line_tbl(l_count).industry_attribute6 := l_rec.INDUSTRY_ATTRIBUTE6;
4156 x_line_tbl(l_count).industry_attribute7 := l_rec.INDUSTRY_ATTRIBUTE7;
4157 x_line_tbl(l_count).industry_attribute8 := l_rec.INDUSTRY_ATTRIBUTE8;
4158 x_line_tbl(l_count).industry_attribute9 := l_rec.INDUSTRY_ATTRIBUTE9;
4159 x_line_tbl(l_count).industry_context := l_rec.INDUSTRY_CONTEXT;
4160 x_line_tbl(l_count).intermed_ship_to_contact_id := l_rec.INTMED_SHIP_TO_CONTACT_ID;
4161 x_line_tbl(l_count).intermed_ship_to_org_id := l_rec.INTMED_SHIP_TO_ORG_ID;
4162 x_line_tbl(l_count).inventory_item_id := l_rec.INVENTORY_ITEM_ID;
4163 x_line_tbl(l_count).invoice_interface_status_code := l_rec.INVOICE_INTERFACE_STATUS_CODE;
4164
4165 x_line_tbl(l_count).invoice_to_contact_id := l_rec.INVOICE_TO_CONTACT_ID;
4166 x_line_tbl(l_count).invoice_to_org_id := l_rec.INVOICE_TO_ORG_ID;
4167 x_line_tbl(l_count).invoiced_quantity := l_rec.INVOICED_QUANTITY;
4168 x_line_tbl(l_count).invoicing_rule_id := l_rec.INVOICING_RULE_ID;
4169 x_line_tbl(l_count).ordered_item_id := l_rec.ORDERED_ITEM_ID;
4170 x_line_tbl(l_count).item_identifier_type := l_rec.ITEM_IDENTIFIER_TYPE;
4171 x_line_tbl(l_count).ordered_item := l_rec.ORDERED_ITEM;
4172 x_line_tbl(l_count).item_revision := l_rec.ITEM_REVISION;
4173 x_line_tbl(l_count).item_type_code := l_rec.ITEM_TYPE_CODE;
4174 x_line_tbl(l_count).last_ack_code := l_rec.LAST_ACK_CODE;
4175 x_line_tbl(l_count).last_ack_date := l_rec.LAST_ACK_DATE;
4176 x_line_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
4177 x_line_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
4178 x_line_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
4179 x_line_tbl(l_count).latest_acceptable_date := l_rec.LATEST_ACCEPTABLE_DATE;
4180 x_line_tbl(l_count).line_category_code := l_rec.LINE_CATEGORY_CODE;
4181 x_line_tbl(l_count).line_id := l_rec.LINE_ID;
4182 x_line_tbl(l_count).line_number := l_rec.LINE_NUMBER;
4183 x_line_tbl(l_count).line_type_id := l_rec.LINE_TYPE_ID;
4184 x_line_tbl(l_count).link_to_line_id := l_rec.LINK_TO_LINE_ID;
4185
4186 x_line_tbl(l_count).model_group_number := l_rec.MODEL_GROUP_NUMBER;
4187 -- x_line_tbl(l_count).mfg_component_sequence_id := l_rec.MFG_COMPONENT_SEQUENCE_ID;
4188 -- x_line_tbl(l_count).mfg_lead_time := l_rec.MFG_LEAD_TIME;
4189 x_line_tbl(l_count).open_flag := l_rec.open_flag;
4190 x_line_tbl(l_count).option_flag := l_rec.OPTION_FLAG;
4191 x_line_tbl(l_count).option_number := l_rec.OPTION_NUMBER;
4192 x_line_tbl(l_count).ordered_quantity := l_rec.ORDERED_QUANTITY;
4193 x_line_tbl(l_count).ordered_quantity2 := l_rec.ORDERED_QUANTITY2; -- OPM
4194 x_line_tbl(l_count).order_quantity_uom := l_rec.ORDER_QUANTITY_UOM;
4195 x_line_tbl(l_count).ordered_quantity_uom2 := l_rec.ORDERED_QUANTITY_UOM2; -- OPM
4196 x_line_tbl(l_count).org_id := l_rec.ORG_ID;
4197 x_line_tbl(l_count).orig_sys_document_ref := l_rec.ORIG_SYS_DOCUMENT_REF;
4198 x_line_tbl(l_count).orig_sys_line_ref := l_rec.ORIG_SYS_LINE_REF;
4199 x_line_tbl(l_count).orig_sys_shipment_ref := l_rec.ORIG_SYS_SHIPMENT_REF;
4200 x_line_tbl(l_count).over_ship_reason_code := l_rec.OVER_SHIP_REASON_CODE;
4201 x_line_tbl(l_count).over_ship_resolved_flag := l_rec.OVER_SHIP_RESOLVED_FLAG;
4202 x_line_tbl(l_count).source_document_line_id := l_rec.SOURCE_DOCUMENT_LINE_ID;
4203 x_line_tbl(l_count).payment_term_id := l_rec.PAYMENT_TERM_ID;
4204 x_line_tbl(l_count).planning_priority := l_rec.PLANNING_PRIORITY;
4205 x_line_tbl(l_count).preferred_grade := l_rec.PREFERRED_GRADE; -- OPM
4206 x_line_tbl(l_count).price_list_id := l_rec.PRICE_LIST_ID;
4207 x_line_tbl(l_count).price_request_code := l_rec.PRICE_REQUEST_CODE; -- PROMOTIONS SEP/01
4208 x_line_tbl(l_count).pricing_attribute1 := l_rec.PRICING_ATTRIBUTE1;
4209 x_line_tbl(l_count).pricing_attribute10 := l_rec.PRICING_ATTRIBUTE10;
4210 x_line_tbl(l_count).pricing_attribute2 := l_rec.PRICING_ATTRIBUTE2;
4211 x_line_tbl(l_count).pricing_attribute3 := l_rec.PRICING_ATTRIBUTE3;
4212 x_line_tbl(l_count).pricing_attribute4 := l_rec.PRICING_ATTRIBUTE4;
4213 x_line_tbl(l_count).pricing_attribute5 := l_rec.PRICING_ATTRIBUTE5;
4214 x_line_tbl(l_count).pricing_attribute6 := l_rec.PRICING_ATTRIBUTE6;
4215 x_line_tbl(l_count).pricing_attribute7 := l_rec.PRICING_ATTRIBUTE7;
4216 x_line_tbl(l_count).pricing_attribute8 := l_rec.PRICING_ATTRIBUTE8;
4217 x_line_tbl(l_count).pricing_attribute9 := l_rec.PRICING_ATTRIBUTE9;
4218 x_line_tbl(l_count).pricing_context := l_rec.PRICING_CONTEXT;
4219 x_line_tbl(l_count).pricing_date := l_rec.PRICING_DATE;
4220 x_line_tbl(l_count).pricing_quantity := l_rec.PRICING_QUANTITY;
4221 x_line_tbl(l_count).pricing_quantity_uom := l_rec.PRICING_QUANTITY_UOM;
4222 x_line_tbl(l_count).program_application_id := l_rec.PROGRAM_APPLICATION_ID;
4223 x_line_tbl(l_count).program_id := l_rec.PROGRAM_ID;
4224 x_line_tbl(l_count).program_update_date := l_rec.PROGRAM_UPDATE_DATE;
4225 x_line_tbl(l_count).project_id := l_rec.PROJECT_ID;
4226 x_line_tbl(l_count).promise_date := l_rec.PROMISE_DATE;
4227 x_line_tbl(l_count).re_source_flag := l_rec.RE_SOURCE_FLAG;
4228 x_line_tbl(l_count).reference_customer_trx_line_id := l_rec.reference_customer_trx_line_id;
4229 x_line_tbl(l_count).reference_header_id := l_rec.REFERENCE_HEADER_ID;
4230 x_line_tbl(l_count).reference_line_id := l_rec.REFERENCE_LINE_ID;
4231 x_line_tbl(l_count).reference_type := l_rec.REFERENCE_TYPE;
4232
4233 x_line_tbl(l_count).request_date := l_rec.REQUEST_DATE;
4234 x_line_tbl(l_count).request_id := l_rec.REQUEST_ID;
4235 x_line_tbl(l_count).return_attribute1 := l_rec.RETURN_ATTRIBUTE1;
4236 x_line_tbl(l_count).return_attribute10 := l_rec.RETURN_ATTRIBUTE10;
4237 x_line_tbl(l_count).return_attribute11 := l_rec.RETURN_ATTRIBUTE11;
4238 x_line_tbl(l_count).return_attribute12 := l_rec.RETURN_ATTRIBUTE12;
4239 x_line_tbl(l_count).return_attribute13 := l_rec.RETURN_ATTRIBUTE13;
4240 x_line_tbl(l_count).return_attribute14 := l_rec.RETURN_ATTRIBUTE14;
4241 x_line_tbl(l_count).return_attribute15 := l_rec.RETURN_ATTRIBUTE15;
4242 x_line_tbl(l_count).return_attribute2 := l_rec.RETURN_ATTRIBUTE2;
4243 x_line_tbl(l_count).return_attribute3 := l_rec.RETURN_ATTRIBUTE3;
4244 x_line_tbl(l_count).return_attribute4 := l_rec.RETURN_ATTRIBUTE4;
4245 x_line_tbl(l_count).return_attribute5 := l_rec.RETURN_ATTRIBUTE5;
4246 x_line_tbl(l_count).return_attribute6 := l_rec.RETURN_ATTRIBUTE6;
4247 x_line_tbl(l_count).return_attribute7 := l_rec.RETURN_ATTRIBUTE7;
4248 x_line_tbl(l_count).return_attribute8 := l_rec.RETURN_ATTRIBUTE8;
4249 x_line_tbl(l_count).return_attribute9 := l_rec.RETURN_ATTRIBUTE9;
4250 x_line_tbl(l_count).return_context := l_rec.RETURN_CONTEXT;
4251 x_line_tbl(l_count).return_reason_code := l_rec.RETURN_REASON_CODE;
4252 x_line_tbl(l_count).salesrep_id := l_rec.SALESREP_ID;
4253 x_line_tbl(l_count).rla_schedule_type_code := l_rec.RLA_SCHEDULE_TYPE_CODE;
4254 x_line_tbl(l_count).schedule_arrival_date := l_rec.SCHEDULE_ARRIVAL_DATE;
4255 x_line_tbl(l_count).schedule_ship_date := l_rec.SCHEDULE_SHIP_DATE;
4256 x_line_tbl(l_count).schedule_status_code := l_rec.SCHEDULE_STATUS_CODE;
4257 x_line_tbl(l_count).shipment_number := l_rec.SHIPMENT_NUMBER;
4258 x_line_tbl(l_count).shipment_priority_code := l_rec.SHIPMENT_PRIORITY_CODE;
4259 x_line_tbl(l_count).shipped_quantity := l_rec.SHIPPED_QUANTITY;
4260 x_line_tbl(l_count).shipped_quantity2 := l_rec.SHIPPED_QUANTITY2; -- OPM B1661023 04/02/01
4261 x_line_tbl(l_count).shipping_method_code := l_rec.SHIPPING_METHOD_CODE;
4262 x_line_tbl(l_count).shipping_quantity := l_rec.SHIPPING_QUANTITY;
4263 x_line_tbl(l_count).shipping_quantity2 := l_rec.SHIPPING_QUANTITY2; -- OPM B1661023 04/02/01
4264 x_line_tbl(l_count).shipping_quantity_uom := l_rec.SHIPPING_QUANTITY_UOM;
4265 x_line_tbl(l_count).ship_from_org_id := l_rec.SHIP_FROM_ORG_ID;
4266 x_line_tbl(l_count).subinventory := l_rec.SUBINVENTORY;
4267 x_line_tbl(l_count).ship_set_id := l_rec.SHIP_SET_ID;
4268 x_line_tbl(l_count).ship_tolerance_above := l_rec.SHIP_TOLERANCE_ABOVE;
4269 x_line_tbl(l_count).ship_tolerance_below := l_rec.SHIP_TOLERANCE_BELOW;
4270 x_line_tbl(l_count).shippable_flag := l_rec.SHIPPABLE_FLAG;
4271 x_line_tbl(l_count).shipping_interfaced_flag := l_rec.SHIPPING_INTERFACED_FLAG;
4272 x_line_tbl(l_count).ship_to_contact_id := l_rec.SHIP_TO_CONTACT_ID;
4273 x_line_tbl(l_count).ship_to_org_id := l_rec.SHIP_TO_ORG_ID;
4274 x_line_tbl(l_count).ship_model_complete_flag := l_rec.SHIP_MODEL_COMPLETE_FLAG;
4275
4276 x_line_tbl(l_count).sold_to_org_id := l_rec.SOLD_TO_ORG_ID;
4277 x_line_tbl(l_count).sold_from_org_id := l_rec.SOLD_FROM_ORG_ID;
4278 x_line_tbl(l_count).sort_order := l_rec.SORT_ORDER;
4279 x_line_tbl(l_count).source_document_id := l_rec.SOURCE_DOCUMENT_ID;
4280 x_line_tbl(l_count).source_document_line_id := l_rec.SOURCE_DOCUMENT_LINE_ID;
4281 x_line_tbl(l_count).source_document_type_id := l_rec.SOURCE_DOCUMENT_TYPE_ID;
4282 x_line_tbl(l_count).source_type_code := l_rec.SOURCE_TYPE_CODE;
4283 x_line_tbl(l_count).split_from_line_id := l_rec.SPLIT_FROM_LINE_ID;
4284 x_line_tbl(l_count).line_set_id := l_rec.LINE_SET_ID;
4285 x_line_tbl(l_count).split_by := l_rec.SPLIT_BY;
4286 x_line_tbl(l_count).model_remnant_flag := l_rec.MODEL_REMNANT_FLAG;
4287 x_line_tbl(l_count).task_id := l_rec.TASK_ID;
4288 x_line_tbl(l_count).tax_code := l_rec.TAX_CODE;
4289 x_line_tbl(l_count).tax_date := l_rec.TAX_DATE;
4290 x_line_tbl(l_count).tax_exempt_flag := l_rec.TAX_EXEMPT_FLAG;
4291 x_line_tbl(l_count).tax_exempt_number := l_rec.TAX_EXEMPT_NUMBER;
4292 x_line_tbl(l_count).tax_exempt_reason_code := l_rec.TAX_EXEMPT_REASON_CODE;
4293 x_line_tbl(l_count).tax_point_code := l_rec.TAX_POINT_CODE;
4294 x_line_tbl(l_count).tax_rate := l_rec.TAX_RATE;
4295 x_line_tbl(l_count).tax_value := l_rec.TAX_VALUE;
4296 x_line_tbl(l_count).top_model_line_id := l_rec.TOP_MODEL_LINE_ID;
4297 x_line_tbl(l_count).unit_list_price := l_rec.UNIT_LIST_PRICE;
4298 x_line_tbl(l_count).unit_list_price_per_pqty := l_rec.UNIT_LIST_PRICE_PER_PQTY;
4299 x_line_tbl(l_count).unit_selling_price := l_rec.UNIT_SELLING_PRICE;
4300 x_line_tbl(l_count).unit_selling_price_per_pqty := l_rec.UNIT_SELLING_PRICE_PER_PQTY;
4301 x_line_tbl(l_count).visible_demand_flag := l_rec.VISIBLE_DEMAND_FLAG;
4302 x_line_tbl(l_count).veh_cus_item_cum_key_id := l_rec.VEH_CUS_ITEM_CUM_KEY_ID;
4303 x_line_tbl(l_count).shipping_instructions := l_rec.shipping_instructions;
4304 x_line_tbl(l_count).packing_instructions := l_rec.packing_instructions;
4305 x_line_tbl(l_count).service_txn_reason_code := l_rec.service_txn_reason_code;
4306 x_line_tbl(l_count).service_txn_comments := l_rec.service_txn_comments;
4307 x_line_tbl(l_count).service_duration := l_rec.service_duration;
4308 x_line_tbl(l_count).service_period := l_rec.service_period;
4309 x_line_tbl(l_count).service_start_date := l_rec.service_start_date;
4310 x_line_tbl(l_count).service_end_date := l_rec.service_end_date;
4311 x_line_tbl(l_count).service_coterminate_flag := l_rec.service_coterminate_flag;
4312 x_line_tbl(l_count).unit_list_percent := l_rec.unit_list_percent;
4313 x_line_tbl(l_count).unit_selling_percent := l_rec.unit_selling_percent;
4314 x_line_tbl(l_count).unit_percent_base_price := l_rec.unit_percent_base_price;
4315 x_line_tbl(l_count).service_number := l_rec.service_number;
4316 x_line_tbl(l_count).service_reference_type_code := l_rec.service_reference_type_code;
4317 x_line_tbl(l_count).service_reference_line_id:= l_rec.service_reference_line_id;
4318 x_line_tbl(l_count).service_reference_system_id:= l_rec.service_reference_system_id;
4319
4320 x_line_tbl(l_count).tp_context := l_rec.tp_context;
4321 x_line_tbl(l_count).tp_attribute1 := l_rec.tp_attribute1;
4322 x_line_tbl(l_count).tp_attribute2 := l_rec.tp_attribute2;
4323 x_line_tbl(l_count).tp_attribute3 := l_rec.tp_attribute3;
4324 x_line_tbl(l_count).tp_attribute4 := l_rec.tp_attribute4;
4325 x_line_tbl(l_count).tp_attribute5 := l_rec.tp_attribute5;
4326 x_line_tbl(l_count).tp_attribute6 := l_rec.tp_attribute6;
4327 x_line_tbl(l_count).tp_attribute7 := l_rec.tp_attribute7;
4328 x_line_tbl(l_count).tp_attribute8 := l_rec.tp_attribute8;
4329 x_line_tbl(l_count).tp_attribute9 := l_rec.tp_attribute9;
4330 x_line_tbl(l_count).tp_attribute10:= l_rec.tp_attribute10;
4331 x_line_tbl(l_count).tp_attribute11:= l_rec.tp_attribute11;
4332 x_line_tbl(l_count).tp_attribute12:= l_rec.tp_attribute12;
4333 x_line_tbl(l_count).tp_attribute13:= l_rec.tp_attribute13;
4334 x_line_tbl(l_count).tp_attribute14:= l_rec.tp_attribute14;
4335 x_line_tbl(l_count).tp_attribute15:= l_rec.tp_attribute15;
4336 x_line_tbl(l_count).flow_status_code := l_rec.flow_status_code;
4337 -- x_line_tbl(l_count).marketing_source_code_id := l_rec.marketing_source_code_id;
4338 x_line_tbl(l_count).calculate_price_flag := l_rec.calculate_price_flag;
4339 x_line_tbl(l_count).commitment_id := l_rec.commitment_id;
4340 -- oe_debug_pub.add('OEXULINB -aksingh -- assigning order_source_id value ' || l_rec.order_source_id);
4341 x_line_tbl(l_count).order_source_id := l_rec.order_source_id;
4342 -- x_line_tbl(l_count).upgraded_flag := l_rec.upgraded_flag ;
4343 x_line_tbl(l_count).Original_Inventory_Item_Id := l_rec.Original_Inventory_Item_Id ;
4344 x_line_tbl(l_count).Original_item_identifier_Type := l_rec.Original_item_identifier_Type ;
4345 x_line_tbl(l_count).Original_ordered_item_id := l_rec.Original_ordered_item_id ;
4346 x_line_tbl(l_count).Original_ordered_item := l_rec.Original_ordered_item ;
4347 x_line_tbl(l_count).Item_Relationship_Type := l_rec.Item_Relationship_Type ;
4348 x_line_tbl(l_count).Item_substitution_type_code := l_rec.Item_substitution_type_code ;
4349 x_line_tbl(l_count).Late_Demand_Penalty_Factor := l_rec.Late_Demand_Penalty_Factor ;
4350 x_line_tbl(l_count).Override_atp_date_code := l_rec.Override_atp_date_code ;
4351 -- x_line_tbl(l_count).Firm_Demand_Flag := l_rec.Firm_Demand_Flag ;
4352 -- x_line_tbl(l_count).Earliest_ship_Date := l_rec.Earliest_ship_Date ;
4353 x_line_tbl(l_count).user_item_description := l_rec.user_item_description ;
4354
4355 -- Changes for Blanket Orders
4356 x_line_tbl(l_count).Blanket_Number := l_rec.Blanket_Number;
4357 x_line_tbl(l_count).Blanket_Line_Number := l_rec.Blanket_Line_Number;
4358 x_line_tbl(l_count).Blanket_Version_Number := l_rec.Blanket_Version_Number;
4359
4360 -- Adding change sequence
4361
4362 x_line_tbl(l_count).change_sequence := l_rec.change_sequence;
4363 --Key Transaction Dates
4364 x_line_tbl(l_count).order_firmed_date := l_rec.order_firmed_date;
4365 x_line_tbl(l_count).actual_fulfillment_date:= l_rec.actual_fulfillment_date;
4366 --end
4367
4368 -- oe_debug_pub.add('outside margin x_line_tbl',1);
4369 --MRG B
4370 --If OE_FEATURES_PVT.Is_Margin_Avail Then
4371 -- oe_debug_pub.add('inside margin x_line_tbl',1);
4372 --x_line_tbl(l_count).unit_cost:=l_rec.unit_cost;
4373 --End If;
4374 --MRG E
4375 -- x_line_tbl(l_count).lock_control:= l_rec.lock_control;
4376
4377 -- Added for COPY ER 1480867
4378 --l_OPN := l_rec.OPN;
4379 --l_CPN := l_rec.CPN;
4380 --l_SVN := l_rec.SVN;
4381
4382
4383 -- set values for non-DB fields
4384 x_line_tbl(l_count).db_flag := FND_API.G_TRUE;
4385 x_line_tbl(l_count).operation := FND_API.G_MISS_CHAR;
4386 x_line_tbl(l_count).return_status := FND_API.G_MISS_CHAR;
4387
4388 x_line_tbl(l_count).schedule_action_code := FND_API.G_MISS_CHAR;
4389 x_line_tbl(l_count).reserved_quantity := FND_API.G_MISS_NUM;
4390 x_line_tbl(l_count).change_reason := FND_API.G_MISS_CHAR;
4391 x_line_tbl(l_count).change_comments := FND_API.G_MISS_CHAR;
4392 x_line_tbl(l_count).arrival_set := FND_API.G_MISS_CHAR;
4393 x_line_tbl(l_count).ship_set := FND_API.G_MISS_CHAR;
4394 x_line_tbl(l_count).fulfillment_set := FND_API.G_MISS_CHAR;
4395 x_line_tbl(l_count).split_action_code := FND_API.G_MISS_CHAR;
4396 l_count := l_count + 1;
4397
4398 END LOOP;
4399
4400 IF l_entity = 1 THEN
4401 CLOSE l_line_csr_l;
4402 ELSIF l_entity = 2 THEN
4403 CLOSE l_line_csr_h;
4404 ELSIF l_entity = 3 THEN
4405 CLOSE l_line_csr_s;
4406 END IF;
4407
4408 -- PK sent and no rows found
4409
4410 IF
4411 (p_line_id IS NOT NULL
4412 AND
4413 p_line_id <> FND_API.G_MISS_NUM)
4414 AND
4415 (x_line_tbl.COUNT = 0)
4416 THEN
4417 RAISE NO_DATA_FOUND;
4418 END IF;
4419
4420 END IF;
4421
4422 EXCEPTION
4423 WHEN OTHERS THEN
4424 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4425 OE_MSG_PUB.Add_Exc_Msg
4426 ( G_PKG_NAME
4427 , 'Line Query_Rows'
4428 );
4429 END IF;
4430 END;
4431
4432
4433 -- Line Adj Query_Row
4434 PROCEDURE Query_Row
4435 ( p_price_adjustment_id IN NUMBER
4436 , p_version_number IN NUMBER := NULL
4437 , p_phase_change_flag IN VARCHAR2 := NULL
4438 , x_Line_Adj_Rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Rec_Type
4439 )
4440 IS
4441 l_version_number NUMBER := p_version_number;
4442 l_current_version_number NUMBER;
4443 l_Line_Adj_Tbl OE_Order_PUB.Line_Adj_Tbl_Type;
4444 BEGIN
4445 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
4446 l_current_version_number := Get_Version_Number(p_price_adjustment_id => p_price_adjustment_id);
4447 IF l_version_number >= l_current_version_number THEN
4448 l_version_number := NULL;
4449 END IF;
4450 END IF;
4451
4452 IF l_version_number IS NULL THEN
4453 OE_Line_Adj_Util.Query_Row(p_price_adjustment_id => p_price_adjustment_id, x_line_adj_rec => x_line_adj_rec);
4454 ELSE
4455 G_INTERNAL_QUERY := TRUE;
4456 Query_Rows
4457 ( p_price_adjustment_id => p_price_adjustment_id
4458 , p_version_number => l_version_number
4459 , x_Line_Adj_Tbl => l_Line_Adj_Tbl
4460 );
4461 x_Line_Adj_Rec := l_Line_Adj_Tbl(1);
4462
4463 G_INTERNAL_QUERY := FALSE;
4464
4465 END IF;
4466
4467 EXCEPTION
4468 WHEN OTHERS THEN
4469 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4470 OE_MSG_PUB.Add_Exc_Msg
4471 ( G_PKG_NAME
4472 , 'Line Adj Query_Row'
4473 );
4474 END IF;
4475 END;
4476
4477
4478 -- Line Adj Query_Rows
4479 PROCEDURE Query_Rows
4480 ( p_price_adjustment_id IN NUMBER :=
4481 FND_API.G_MISS_NUM
4482 , p_line_id IN NUMBER :=
4483 FND_API.G_MISS_NUM
4484 , p_Header_id IN NUMBER :=
4485 FND_API.G_MISS_NUM
4486 , p_version_number IN NUMBER := NULL
4487 , p_phase_change_flag IN VARCHAR2 := NULL
4488 , x_Line_Adj_Tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Tbl_Type
4489 )
4490 IS
4491 l_version_number NUMBER := p_version_number;
4492 l_current_version_number NUMBER;
4493 l_count NUMBER;
4494 l_entity NUMBER;
4495
4496 CURSOR l_Line_Adj_csr_p IS
4497 SELECT ATTRIBUTE1
4498 , ATTRIBUTE10
4499 , ATTRIBUTE11
4500 , ATTRIBUTE12
4501 , ATTRIBUTE13
4502 , ATTRIBUTE14
4503 , ATTRIBUTE15
4504 , ATTRIBUTE2
4505 , ATTRIBUTE3
4506 , ATTRIBUTE4
4507 , ATTRIBUTE5
4508 , ATTRIBUTE6
4509 , ATTRIBUTE7
4510 , ATTRIBUTE8
4511 , ATTRIBUTE9
4512 , AUTOMATIC_FLAG
4513 , CONTEXT
4514 , CREATED_BY
4515 , CREATION_DATE
4516 , DISCOUNT_ID
4517 , DISCOUNT_LINE_ID
4518 , HEADER_ID
4519 , LAST_UPDATED_BY
4520 , LAST_UPDATE_DATE
4521 , LAST_UPDATE_LOGIN
4522 , LINE_ID
4523 , PERCENT
4524 , PRICE_ADJUSTMENT_ID
4525 , PROGRAM_APPLICATION_ID
4526 , PROGRAM_ID
4527 , PROGRAM_UPDATE_DATE
4528 , REQUEST_ID
4529 , LIST_HEADER_ID
4530 , LIST_LINE_ID
4531 , LIST_LINE_TYPE_CODE
4532 , MODIFIER_MECHANISM_TYPE_CODE
4533 , MODIFIED_FROM
4534 , MODIFIED_TO
4535 , UPDATED_FLAG
4536 , UPDATE_ALLOWED
4537 , APPLIED_FLAG
4538 , CHANGE_REASON_CODE
4539 , CHANGE_REASON_TEXT
4540 , operand
4541 , Arithmetic_operator
4542 , COST_ID
4543 , TAX_CODE
4544 , TAX_EXEMPT_FLAG
4545 , TAX_EXEMPT_NUMBER
4546 , TAX_EXEMPT_REASON_CODE
4547 , PARENT_ADJUSTMENT_ID
4548 , INVOICED_FLAG
4549 , ESTIMATED_FLAG
4550 , INC_IN_SALES_PERFORMANCE
4551 , SPLIT_ACTION_CODE
4552 , ADJUSTED_AMOUNT
4553 , PRICING_PHASE_ID
4554 , CHARGE_TYPE_CODE
4555 , CHARGE_SUBTYPE_CODE
4556 , list_line_no
4557 , source_system_code
4558 , benefit_qty
4559 , benefit_uom_code
4560 , print_on_invoice_flag
4561 , expiration_date
4562 , rebate_transaction_type_code
4563 , rebate_transaction_reference
4564 , rebate_payment_system_code
4565 , redeemed_date
4566 , redeemed_flag
4567 , accrual_flag
4568 , range_break_quantity
4569 , accrual_conversion_rate
4570 , pricing_group_sequence
4571 , modifier_level_code
4572 , price_break_type_code
4573 , substitution_attribute
4574 , proration_type_code
4575 , credit_or_charge_flag
4576 , include_on_returns_flag
4577 , AC_ATTRIBUTE1
4578 , AC_ATTRIBUTE10
4579 , AC_ATTRIBUTE11
4580 , AC_ATTRIBUTE12
4581 , AC_ATTRIBUTE13
4582 , AC_ATTRIBUTE14
4583 , AC_ATTRIBUTE15
4584 , AC_ATTRIBUTE2
4585 , AC_ATTRIBUTE3
4586 , AC_ATTRIBUTE4
4587 , AC_ATTRIBUTE5
4588 , AC_ATTRIBUTE6
4589 , AC_ATTRIBUTE7
4590 , AC_ATTRIBUTE8
4591 , AC_ATTRIBUTE9
4592 , AC_CONTEXT
4593 --uom begin
4594 -- , OPERAND_PER_PQTY
4595 -- , ADJUSTED_AMOUNT_PER_PQTY
4596 --uom end
4597 , LOCK_CONTROL
4598 -- eBTax changes
4599 , TAX_RATE_ID
4600 FROM OE_PRICE_ADJS_HISTORY
4601 WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
4602 and version_number = l_version_number
4603 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
4604 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
4605 AND VERSION_FLAG = 'Y'));
4606
4607 CURSOR l_Line_Adj_csr_l IS
4608 SELECT ATTRIBUTE1
4609 , ATTRIBUTE10
4610 , ATTRIBUTE11
4611 , ATTRIBUTE12
4612 , ATTRIBUTE13
4613 , ATTRIBUTE14
4614 , ATTRIBUTE15
4615 , ATTRIBUTE2
4616 , ATTRIBUTE3
4617 , ATTRIBUTE4
4618 , ATTRIBUTE5
4619 , ATTRIBUTE6
4620 , ATTRIBUTE7
4621 , ATTRIBUTE8
4622 , ATTRIBUTE9
4623 , AUTOMATIC_FLAG
4624 , CONTEXT
4625 , CREATED_BY
4626 , CREATION_DATE
4627 , DISCOUNT_ID
4628 , DISCOUNT_LINE_ID
4629 , HEADER_ID
4630 , LAST_UPDATED_BY
4631 , LAST_UPDATE_DATE
4632 , LAST_UPDATE_LOGIN
4633 , LINE_ID
4634 , PERCENT
4635 , PRICE_ADJUSTMENT_ID
4636 , PROGRAM_APPLICATION_ID
4637 , PROGRAM_ID
4638 , PROGRAM_UPDATE_DATE
4639 , REQUEST_ID
4640 , LIST_HEADER_ID
4641 , LIST_LINE_ID
4642 , LIST_LINE_TYPE_CODE
4643 , MODIFIER_MECHANISM_TYPE_CODE
4644 , MODIFIED_FROM
4645 , MODIFIED_TO
4646 , UPDATED_FLAG
4647 , UPDATE_ALLOWED
4648 , APPLIED_FLAG
4649 , CHANGE_REASON_CODE
4650 , CHANGE_REASON_TEXT
4651 , operand
4652 , Arithmetic_operator
4653 , COST_ID
4654 , TAX_CODE
4655 , TAX_EXEMPT_FLAG
4656 , TAX_EXEMPT_NUMBER
4657 , TAX_EXEMPT_REASON_CODE
4658 , PARENT_ADJUSTMENT_ID
4659 , INVOICED_FLAG
4660 , ESTIMATED_FLAG
4661 , INC_IN_SALES_PERFORMANCE
4662 , SPLIT_ACTION_CODE
4663 , ADJUSTED_AMOUNT
4664 , PRICING_PHASE_ID
4665 , CHARGE_TYPE_CODE
4666 , CHARGE_SUBTYPE_CODE
4667 , list_line_no
4668 , source_system_code
4669 , benefit_qty
4670 , benefit_uom_code
4671 , print_on_invoice_flag
4672 , expiration_date
4673 , rebate_transaction_type_code
4674 , rebate_transaction_reference
4675 , rebate_payment_system_code
4676 , redeemed_date
4677 , redeemed_flag
4678 , accrual_flag
4679 , range_break_quantity
4680 , accrual_conversion_rate
4681 , pricing_group_sequence
4682 , modifier_level_code
4683 , price_break_type_code
4684 , substitution_attribute
4685 , proration_type_code
4686 , credit_or_charge_flag
4687 , include_on_returns_flag
4688 , AC_ATTRIBUTE1
4689 , AC_ATTRIBUTE10
4690 , AC_ATTRIBUTE11
4691 , AC_ATTRIBUTE12
4692 , AC_ATTRIBUTE13
4693 , AC_ATTRIBUTE14
4694 , AC_ATTRIBUTE15
4695 , AC_ATTRIBUTE2
4696 , AC_ATTRIBUTE3
4697 , AC_ATTRIBUTE4
4698 , AC_ATTRIBUTE5
4699 , AC_ATTRIBUTE6
4700 , AC_ATTRIBUTE7
4701 , AC_ATTRIBUTE8
4702 , AC_ATTRIBUTE9
4703 , AC_CONTEXT
4704 --uom begin
4705 -- , OPERAND_PER_PQTY
4706 -- , ADJUSTED_AMOUNT_PER_PQTY
4707 --uom end
4708 , LOCK_CONTROL
4709 -- eBTax changes
4710 , TAX_RATE_ID
4711
4712 FROM OE_PRICE_ADJS_HISTORY
4713 WHERE line_id = p_line_id
4714 and version_number = l_version_number
4715 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
4716 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
4717 AND VERSION_FLAG = 'Y'));
4718
4719 CURSOR l_Line_Adj_csr_h IS
4720 SELECT ATTRIBUTE1
4721 , ATTRIBUTE10
4722 , ATTRIBUTE11
4723 , ATTRIBUTE12
4724 , ATTRIBUTE13
4725 , ATTRIBUTE14
4726 , ATTRIBUTE15
4727 , ATTRIBUTE2
4728 , ATTRIBUTE3
4729 , ATTRIBUTE4
4730 , ATTRIBUTE5
4731 , ATTRIBUTE6
4732 , ATTRIBUTE7
4733 , ATTRIBUTE8
4734 , ATTRIBUTE9
4735 , AUTOMATIC_FLAG
4736 , CONTEXT
4737 , CREATED_BY
4738 , CREATION_DATE
4739 , DISCOUNT_ID
4740 , DISCOUNT_LINE_ID
4741 , HEADER_ID
4742 , LAST_UPDATED_BY
4743 , LAST_UPDATE_DATE
4744 , LAST_UPDATE_LOGIN
4745 , LINE_ID
4746 , PERCENT
4747 , PRICE_ADJUSTMENT_ID
4748 , PROGRAM_APPLICATION_ID
4749 , PROGRAM_ID
4750 , PROGRAM_UPDATE_DATE
4751 , REQUEST_ID
4752 , LIST_HEADER_ID
4753 , LIST_LINE_ID
4754 , LIST_LINE_TYPE_CODE
4755 , MODIFIER_MECHANISM_TYPE_CODE
4756 , MODIFIED_FROM
4757 , MODIFIED_TO
4758 , UPDATED_FLAG
4759 , UPDATE_ALLOWED
4760 , APPLIED_FLAG
4761 , CHANGE_REASON_CODE
4762 , CHANGE_REASON_TEXT
4763 , operand
4764 , Arithmetic_operator
4765 , COST_ID
4766 , TAX_CODE
4767 , TAX_EXEMPT_FLAG
4768 , TAX_EXEMPT_NUMBER
4769 , TAX_EXEMPT_REASON_CODE
4770 , PARENT_ADJUSTMENT_ID
4771 , INVOICED_FLAG
4772 , ESTIMATED_FLAG
4773 , INC_IN_SALES_PERFORMANCE
4774 , SPLIT_ACTION_CODE
4775 , ADJUSTED_AMOUNT
4776 , PRICING_PHASE_ID
4777 , CHARGE_TYPE_CODE
4778 , CHARGE_SUBTYPE_CODE
4779 , list_line_no
4780 , source_system_code
4781 , benefit_qty
4782 , benefit_uom_code
4783 , print_on_invoice_flag
4784 , expiration_date
4785 , rebate_transaction_type_code
4786 , rebate_transaction_reference
4787 , rebate_payment_system_code
4788 , redeemed_date
4789 , redeemed_flag
4790 , accrual_flag
4791 , range_break_quantity
4792 , accrual_conversion_rate
4793 , pricing_group_sequence
4794 , modifier_level_code
4795 , price_break_type_code
4796 , substitution_attribute
4797 , proration_type_code
4798 , credit_or_charge_flag
4799 , include_on_returns_flag
4800 , AC_ATTRIBUTE1
4801 , AC_ATTRIBUTE10
4802 , AC_ATTRIBUTE11
4803 , AC_ATTRIBUTE12
4804 , AC_ATTRIBUTE13
4805 , AC_ATTRIBUTE14
4806 , AC_ATTRIBUTE15
4807 , AC_ATTRIBUTE2
4808 , AC_ATTRIBUTE3
4809 , AC_ATTRIBUTE4
4810 , AC_ATTRIBUTE5
4811 , AC_ATTRIBUTE6
4812 , AC_ATTRIBUTE7
4813 , AC_ATTRIBUTE8
4814 , AC_ATTRIBUTE9
4815 , AC_CONTEXT
4816 --uom begin
4817 -- , OPERAND_PER_PQTY
4818 -- , ADJUSTED_AMOUNT_PER_PQTY
4819 --uom end
4820 , LOCK_CONTROL
4821 -- eBTax changes
4822 , TAX_RATE_ID
4823 FROM OE_PRICE_ADJS_HISTORY
4824 WHERE header_id = p_header_id
4825 and line_id is not null
4826 and version_number = l_version_number
4827 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
4828 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
4829 AND VERSION_FLAG = 'Y'));
4830
4831 l_rec l_line_adj_csr_p%ROWTYPE;
4832
4833 --
4834 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4835 --
4836
4837 BEGIN
4838 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
4839 l_current_version_number := Get_Version_Number(p_header_id => p_header_id, p_line_id => p_line_id, p_price_adjustment_id => p_price_adjustment_id);
4840
4841 IF l_version_number >= l_current_version_number THEN
4842 l_version_number := NULL;
4843 END IF;
4844 END IF;
4845
4846 IF l_version_number IS NULL THEN
4847 --bug3392650 (passing the parameter p_header_id instead of p_line_level_header_id)
4848 OE_Line_Adj_Util.Query_Rows(p_price_adjustment_id => p_price_adjustment_id, p_line_id => p_line_id, p_header_id => p_header_id, x_line_adj_tbl => x_line_adj_tbl);
4849 ELSE
4850
4851 IF nvl(p_price_adjustment_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4852 l_entity := 1;
4853 OPEN l_line_adj_csr_p;
4854 ELSIF nvl(p_line_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4855 l_entity := 2;
4856 OPEN l_line_adj_csr_l;
4857 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4858 l_entity := 3;
4859 OPEN l_line_adj_csr_h;
4860 END IF;
4861
4862 -- Loop over fetched records
4863
4864 l_count := 1;
4865
4866 LOOP
4867
4868 IF l_entity = 1 THEN
4869 FETCH l_line_adj_csr_p INTO l_rec;
4870 EXIT WHEN l_line_adj_csr_p%NOTFOUND;
4871 ELSIF l_entity = 2 THEN
4872 FETCH l_line_adj_csr_l INTO l_rec;
4873 EXIT WHEN l_line_adj_csr_l%NOTFOUND;
4874 ELSIF l_entity = 3 THEN
4875 FETCH l_line_adj_csr_h INTO l_rec;
4876 EXIT WHEN l_line_adj_csr_h%NOTFOUND;
4877 ELSE
4878 EXIT;
4879 END IF;
4880
4881 x_Line_Adj_tbl(l_count).attribute1 := l_rec.ATTRIBUTE1;
4882 x_Line_Adj_tbl(l_count).attribute10 := l_rec.ATTRIBUTE10;
4883 x_Line_Adj_tbl(l_count).attribute11 := l_rec.ATTRIBUTE11;
4884 x_Line_Adj_tbl(l_count).attribute12 := l_rec.ATTRIBUTE12;
4885 x_Line_Adj_tbl(l_count).attribute13 := l_rec.ATTRIBUTE13;
4886 x_Line_Adj_tbl(l_count).attribute14 := l_rec.ATTRIBUTE14;
4887 x_Line_Adj_tbl(l_count).attribute15 := l_rec.ATTRIBUTE15;
4888 x_Line_Adj_tbl(l_count).attribute2 := l_rec.ATTRIBUTE2;
4889 x_Line_Adj_tbl(l_count).attribute3 := l_rec.ATTRIBUTE3;
4890 x_Line_Adj_tbl(l_count).attribute4 := l_rec.ATTRIBUTE4;
4891 x_Line_Adj_tbl(l_count).attribute5 := l_rec.ATTRIBUTE5;
4892 x_Line_Adj_tbl(l_count).attribute6 := l_rec.ATTRIBUTE6;
4893 x_Line_Adj_tbl(l_count).attribute7 := l_rec.ATTRIBUTE7;
4894 x_Line_Adj_tbl(l_count).attribute8 := l_rec.ATTRIBUTE8;
4895 x_Line_Adj_tbl(l_count).attribute9 := l_rec.ATTRIBUTE9;
4896 x_Line_Adj_tbl(l_count).automatic_flag := l_rec.AUTOMATIC_FLAG;
4897 x_Line_Adj_tbl(l_count).context := l_rec.CONTEXT;
4898 x_Line_Adj_tbl(l_count).created_by := l_rec.CREATED_BY;
4899 x_Line_Adj_tbl(l_count).creation_date := l_rec.CREATION_DATE;
4900 x_Line_Adj_tbl(l_count).discount_id := l_rec.DISCOUNT_ID;
4901 x_Line_Adj_tbl(l_count).discount_line_id := l_rec.DISCOUNT_LINE_ID;
4902 x_Line_Adj_tbl(l_count).header_id := l_rec.HEADER_ID;
4903 x_Line_Adj_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
4904 x_Line_Adj_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
4905 x_Line_Adj_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
4906 x_Line_Adj_tbl(l_count).line_id := l_rec.LINE_ID;
4907 x_Line_Adj_tbl(l_count).percent := l_rec.PERCENT;
4908 x_Line_Adj_tbl(l_count).price_adjustment_id := l_rec.PRICE_ADJUSTMENT_ID;
4909 x_Line_Adj_tbl(l_count).program_application_id := l_rec.PROGRAM_APPLICATION_ID;
4910 x_Line_Adj_tbl(l_count).program_id := l_rec.PROGRAM_ID;
4911 x_Line_Adj_tbl(l_count).program_update_date := l_rec.PROGRAM_UPDATE_DATE;
4912 x_Line_Adj_tbl(l_count).request_id := l_rec.REQUEST_ID;
4913 x_Line_Adj_tbl(l_count).list_header_id := l_rec.list_header_id;
4914 x_Line_Adj_tbl(l_count).list_line_id := l_rec.list_line_id;
4915 x_Line_Adj_tbl(l_count).list_line_type_code := l_rec.list_line_type_code;
4916 x_Line_Adj_tbl(l_count).modifier_mechanism_type_code := l_rec.modifier_mechanism_type_code;
4917 x_Line_Adj_tbl(l_count).modified_from := l_rec.modified_from;
4918 x_Line_Adj_tbl(l_count).modified_to := l_rec.modified_to;
4919 x_Line_Adj_tbl(l_count).updated_flag := l_rec.updated_flag;
4920 x_Line_Adj_tbl(l_count).update_allowed := l_rec.update_allowed;
4921 x_Line_Adj_tbl(l_count).applied_flag := l_rec.applied_flag;
4922 x_Line_Adj_tbl(l_count).change_reason_code := l_rec.change_reason_code;
4923 x_Line_Adj_tbl(l_count).change_reason_text := l_rec.change_reason_text;
4924 x_Line_Adj_tbl(l_count).operand := l_rec.operand;
4925 x_Line_Adj_tbl(l_count).arithmetic_operator := l_rec.arithmetic_operator;
4926 x_Line_Adj_tbl(l_count).adjusted_amount := l_rec.adjusted_amount;
4927 x_Line_Adj_tbl(l_count).pricing_phase_id := l_rec.pricing_phase_id;
4928 x_Line_Adj_tbl(l_count).cost_id := l_rec.cost_id;
4929 x_Line_Adj_tbl(l_count).tax_code := l_rec.tax_code;
4930 x_Line_Adj_tbl(l_count).tax_exempt_flag := l_rec.tax_exempt_flag;
4931 x_Line_Adj_tbl(l_count).tax_exempt_number := l_rec.tax_exempt_number;
4932 x_Line_Adj_tbl(l_count).tax_exempt_reason_code := l_rec.tax_exempt_reason_code;
4933 x_Line_Adj_tbl(l_count).parent_adjustment_id := l_rec.parent_adjustment_id;
4934 x_Line_Adj_tbl(l_count).invoiced_flag := l_rec.invoiced_flag;
4935 x_Line_Adj_tbl(l_count).estimated_flag := l_rec.estimated_flag;
4936 x_Line_Adj_tbl(l_count).inc_in_sales_performance := l_rec.inc_in_sales_performance;
4937 x_Line_Adj_tbl(l_count).split_action_code := l_rec.split_action_code;
4938 x_Line_Adj_tbl(l_count).charge_type_code := l_rec.charge_type_code;
4939 x_Line_Adj_tbl(l_count).charge_subtype_code := l_rec.charge_subtype_code;
4940 x_Line_Adj_tbl(l_count).list_line_no := l_rec.list_line_no;
4941 x_Line_Adj_tbl(l_count).source_system_code := l_rec.source_system_code;
4942 x_Line_Adj_tbl(l_count).benefit_qty := l_rec.benefit_qty;
4943 x_Line_Adj_tbl(l_count).benefit_uom_code := l_rec.benefit_uom_code;
4944 x_Line_Adj_tbl(l_count).print_on_invoice_flag := l_rec.print_on_invoice_flag;
4945 x_Line_Adj_tbl(l_count).expiration_date := l_rec.expiration_date;
4946 x_Line_Adj_tbl(l_count).rebate_transaction_type_code := l_rec.rebate_transaction_type_code;
4947 x_Line_Adj_tbl(l_count).rebate_transaction_reference := l_rec.rebate_transaction_reference;
4948 x_Line_Adj_tbl(l_count).rebate_payment_system_code := l_rec.rebate_payment_system_code;
4949 x_Line_Adj_tbl(l_count).redeemed_date := l_rec.redeemed_date;
4950 x_Line_Adj_tbl(l_count).redeemed_flag := l_rec.redeemed_flag;
4951 x_Line_Adj_tbl(l_count).accrual_flag := l_rec.accrual_flag;
4952 x_Line_Adj_tbl(l_count).range_break_quantity := l_rec.range_break_quantity;
4953 x_Line_Adj_tbl(l_count).accrual_conversion_rate := l_rec.accrual_conversion_rate;
4954 x_Line_Adj_tbl(l_count).pricing_group_sequence := l_rec.pricing_group_sequence;
4955 x_Line_Adj_tbl(l_count).modifier_level_code := l_rec.modifier_level_code;
4956 x_Line_Adj_tbl(l_count).price_break_type_code := l_rec.price_break_type_code;
4957 x_Line_Adj_tbl(l_count).substitution_attribute := l_rec.substitution_attribute;
4958 x_Line_Adj_tbl(l_count).proration_type_code := l_rec.proration_type_code;
4959 x_Line_Adj_tbl(l_count).credit_or_charge_flag := l_rec.credit_or_charge_flag;
4960 x_Line_Adj_tbl(l_count).include_on_returns_flag := l_rec.include_on_returns_flag;
4961 x_Line_Adj_tbl(l_count).ac_attribute1 := l_rec.AC_ATTRIBUTE1;
4962 x_Line_Adj_tbl(l_count).ac_attribute10 := l_rec.AC_ATTRIBUTE10;
4963 x_Line_Adj_tbl(l_count).ac_attribute11 := l_rec.AC_ATTRIBUTE11;
4964 x_Line_Adj_tbl(l_count).ac_attribute12 := l_rec.AC_ATTRIBUTE12;
4965 x_Line_Adj_tbl(l_count).ac_attribute13 := l_rec.AC_ATTRIBUTE13;
4966 x_Line_Adj_tbl(l_count).ac_attribute14 := l_rec.AC_ATTRIBUTE14;
4967 x_Line_Adj_tbl(l_count).ac_attribute15 := l_rec.AC_ATTRIBUTE15;
4968 x_Line_Adj_tbl(l_count).ac_attribute2 := l_rec.AC_ATTRIBUTE2;
4969 x_Line_Adj_tbl(l_count).ac_attribute3 := l_rec.AC_ATTRIBUTE3;
4970 x_Line_Adj_tbl(l_count).ac_attribute4 := l_rec.AC_ATTRIBUTE4;
4971 x_Line_Adj_tbl(l_count).ac_attribute5 := l_rec.AC_ATTRIBUTE5;
4972 x_Line_Adj_tbl(l_count).ac_attribute6 := l_rec.AC_ATTRIBUTE6;
4973 x_Line_Adj_tbl(l_count).ac_attribute7 := l_rec.AC_ATTRIBUTE7;
4974 x_Line_Adj_tbl(l_count).ac_attribute8 := l_rec.AC_ATTRIBUTE8;
4975 x_Line_Adj_tbl(l_count).ac_attribute9 := l_rec.AC_ATTRIBUTE9;
4976 x_Line_Adj_tbl(l_count).ac_context := l_rec.AC_CONTEXT;
4977 --uom begin
4978 -- x_Line_Adj_tbl(l_count).operand_per_pqty := l_rec.operand_per_pqty;
4979 -- x_Line_Adj_tbl(l_count).adjusted_amount_per_pqty := l_rec.adjusted_amount_per_pqty;
4980 --uom end
4981 x_Line_Adj_tbl(l_count).lock_control := l_rec.LOCK_CONTROL;
4982
4983 -- set values for non-DB fields
4984 x_Line_Adj_tbl(l_count).db_flag := FND_API.G_TRUE;
4985 x_Line_Adj_tbl(l_count).operation := FND_API.G_MISS_CHAR;
4986 x_Line_Adj_tbl(l_count).return_status := FND_API.G_MISS_CHAR;
4987 -- eBTax changes
4988 x_Line_Adj_tbl(l_count).tax_rate_id := l_rec.tax_rate_id;
4989
4990 l_count := l_count + 1;
4991
4992 END LOOP;
4993
4994 IF l_entity = 1 THEN
4995 CLOSE l_line_adj_csr_p;
4996 ELSIF l_entity = 2 THEN
4997 CLOSE l_line_adj_csr_l;
4998 ELSIF l_entity = 3 THEN
4999 CLOSE l_line_adj_csr_h;
5000 END IF;
5001
5002 -- PK sent and no rows found
5003
5004 IF
5005 (p_price_adjustment_id IS NOT NULL
5006 AND
5007 p_price_adjustment_id <> FND_API.G_MISS_NUM)
5008 AND
5009 (x_Line_Adj_tbl.COUNT = 0)
5010 THEN
5011 RAISE NO_DATA_FOUND;
5012 END IF;
5013
5014 -- Return fetched table
5015
5016 END IF;
5017
5018 EXCEPTION
5019 WHEN OTHERS THEN
5020 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5021 OE_MSG_PUB.Add_Exc_Msg
5022 ( G_PKG_NAME
5023 , 'Line Adj Query_Rows'
5024 );
5025 END IF;
5026 END;
5027
5028
5029 -- Line Scredit Query_Row
5030 PROCEDURE Query_Row
5031 ( p_sales_credit_id IN NUMBER
5032 , p_version_number IN NUMBER := NULL
5033 , p_phase_change_flag IN VARCHAR2 := NULL
5034 , x_Line_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Line_Scredit_Rec_Type
5035 )
5036 IS
5037 l_version_number NUMBER := p_version_number;
5038 l_current_version_number NUMBER;
5039
5040 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
5041 --
5042 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5043 --
5044 BEGIN
5045 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
5046 l_current_version_number := Get_Version_Number(p_sales_credit_id => p_sales_credit_id);
5047
5048 IF l_version_number >= l_current_version_number THEN
5049 l_version_number := NULL;
5050 END IF;
5051 END IF;
5052
5053 IF l_version_number IS NULL THEN
5054 OE_Line_Scredit_Util.Query_Row(p_sales_credit_id => p_sales_credit_id, x_line_scredit_rec => x_line_scredit_rec);
5055 ELSE
5056
5057 IF l_debug_level > 0 THEN
5058 oe_debug_pub.add( 'ENTERING OE_LINE_SCREDIT_UTIL.QUERY_ROW' , 1 ) ;
5059 END IF;
5060
5061 G_INTERNAL_QUERY := TRUE;
5062 Query_Rows
5063 ( p_sales_credit_id => p_sales_credit_id
5064 ,x_Line_Scredit_tbl => l_Line_Scredit_tbl
5065 );
5066 G_INTERNAL_QUERY := FALSE;
5067 x_Line_Scredit_rec := l_Line_Scredit_tbl(1);
5068
5069 END IF;
5070
5071 EXCEPTION
5072 WHEN OTHERS THEN
5073 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5074 OE_MSG_PUB.Add_Exc_Msg
5075 ( G_PKG_NAME
5076 , 'Line Scredit Query_Row'
5077 );
5078 END IF;
5079 END;
5080
5081
5082 -- Line Scredit Query_Rows
5083 PROCEDURE Query_Rows
5084 ( p_sales_credit_id IN NUMBER :=
5085 FND_API.G_MISS_NUM
5086 , p_line_id IN NUMBER :=
5087 FND_API.G_MISS_NUM
5088 , p_header_id IN NUMBER :=
5089 FND_API.G_MISS_NUM
5090 , p_version_number IN NUMBER := NULL
5091 , p_phase_change_flag IN VARCHAR2 := NULL
5092 , x_Line_Scredit_tbl IN OUT NOCOPY OE_Order_PUB.Line_Scredit_Tbl_Type
5093 )
5094 IS
5095 l_version_number NUMBER := p_version_number;
5096 l_current_version_number NUMBER;
5097
5098 CURSOR l_Line_Scredit_csr_s IS
5099 SELECT ATTRIBUTE1
5100 , ATTRIBUTE10
5101 , ATTRIBUTE11
5102 , ATTRIBUTE12
5103 , ATTRIBUTE13
5104 , ATTRIBUTE14
5105 , ATTRIBUTE15
5106 , ATTRIBUTE2
5107 , ATTRIBUTE3
5108 , ATTRIBUTE4
5109 , ATTRIBUTE5
5110 , ATTRIBUTE6
5111 , ATTRIBUTE7
5112 , ATTRIBUTE8
5113 , ATTRIBUTE9
5114 , CONTEXT
5115 , CREATED_BY
5116 , CREATION_DATE
5117 , DW_UPDATE_ADVICE_FLAG
5118 , HEADER_ID
5119 , LAST_UPDATED_BY
5120 , LAST_UPDATE_DATE
5121 , LAST_UPDATE_LOGIN
5122 , LINE_ID
5123 , PERCENT
5124 , SALESREP_ID
5125 , sales_credit_type_id
5126 , SALES_CREDIT_ID
5127 , WH_UPDATE_DATE
5128 , LOCK_CONTROL
5129 FROM OE_SALES_CREDIT_HISTORY
5130 WHERE SALES_CREDIT_ID = p_sales_credit_id
5131 AND VERSION_NUMBER = l_version_number
5132 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
5133 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
5134 AND VERSION_FLAG = 'Y'));
5135
5136 CURSOR l_Line_Scredit_csr_l IS
5137 SELECT ATTRIBUTE1
5138 , ATTRIBUTE10
5139 , ATTRIBUTE11
5140 , ATTRIBUTE12
5141 , ATTRIBUTE13
5142 , ATTRIBUTE14
5143 , ATTRIBUTE15
5144 , ATTRIBUTE2
5145 , ATTRIBUTE3
5146 , ATTRIBUTE4
5147 , ATTRIBUTE5
5148 , ATTRIBUTE6
5149 , ATTRIBUTE7
5150 , ATTRIBUTE8
5151 , ATTRIBUTE9
5152 , CONTEXT
5153 , CREATED_BY
5154 , CREATION_DATE
5155 , DW_UPDATE_ADVICE_FLAG
5156 , HEADER_ID
5157 , LAST_UPDATED_BY
5158 , LAST_UPDATE_DATE
5159 , LAST_UPDATE_LOGIN
5160 , LINE_ID
5161 , PERCENT
5162 , SALESREP_ID
5163 , sales_credit_type_id
5164 , SALES_CREDIT_ID
5165 , WH_UPDATE_DATE
5166 , LOCK_CONTROL
5167 FROM OE_SALES_CREDIT_HISTORY
5168 WHERE LINE_ID = p_line_id
5169 AND VERSION_NUMBER = l_version_number
5170 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
5171 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
5172 AND VERSION_FLAG = 'Y'));
5173
5174 CURSOR l_Line_Scredit_csr_h IS
5175 SELECT ATTRIBUTE1
5176 , ATTRIBUTE10
5177 , ATTRIBUTE11
5178 , ATTRIBUTE12
5179 , ATTRIBUTE13
5180 , ATTRIBUTE14
5181 , ATTRIBUTE15
5182 , ATTRIBUTE2
5183 , ATTRIBUTE3
5184 , ATTRIBUTE4
5185 , ATTRIBUTE5
5186 , ATTRIBUTE6
5187 , ATTRIBUTE7
5188 , ATTRIBUTE8
5189 , ATTRIBUTE9
5190 , CONTEXT
5191 , CREATED_BY
5192 , CREATION_DATE
5193 , DW_UPDATE_ADVICE_FLAG
5194 , HEADER_ID
5195 , LAST_UPDATED_BY
5196 , LAST_UPDATE_DATE
5197 , LAST_UPDATE_LOGIN
5198 , LINE_ID
5199 , PERCENT
5200 , SALESREP_ID
5201 , sales_credit_type_id
5202 , SALES_CREDIT_ID
5203 , WH_UPDATE_DATE
5204 , LOCK_CONTROL
5205 FROM OE_SALES_CREDIT_HISTORY
5206 WHERE HEADER_ID = p_header_id
5207 AND LINE_ID IS NOT NULL
5208 AND VERSION_NUMBER = l_version_number
5209 AND (PHASE_CHANGE_FLAG = p_phase_change_flag
5210 OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'
5211 AND VERSION_FLAG = 'Y'));
5212
5213 l_rec l_line_scredit_csr_s%ROWTYPE;
5214 l_entity NUMBER;
5215 l_count NUMBER;
5216
5217 --
5218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5219 --
5220 BEGIN
5221 IF p_version_number IS NOT NULL AND NOT G_INTERNAL_QUERY THEN
5222 l_current_version_number := Get_Version_Number(p_header_id => p_header_id, p_line_id => p_line_id, p_sales_credit_id => p_sales_credit_id);
5223
5224 IF l_version_number >= l_current_version_number THEN
5225 l_version_number := NULL;
5226 END IF;
5227 END IF;
5228
5229 IF l_version_number IS NULL THEN
5230 OE_Line_Scredit_Util.Query_Rows(p_sales_credit_id => p_sales_credit_id, p_line_id => p_line_id, p_header_id => p_header_id, x_line_scredit_tbl => x_line_scredit_tbl);
5231 ELSE
5232
5233 IF
5234 (p_sales_credit_id IS NOT NULL
5235 AND
5236 p_sales_credit_id <> FND_API.G_MISS_NUM)
5237 AND
5238 (p_line_id IS NOT NULL
5239 AND
5240 p_line_id <> FND_API.G_MISS_NUM)
5241 THEN
5242
5243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5244
5245 END IF;
5246
5247
5248 IF nvl(p_sales_credit_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5249 l_entity := 1;
5250 OPEN l_line_scredit_csr_s;
5251 ELSIF nvl(p_line_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5252 l_entity := 2;
5253 OPEN l_line_scredit_csr_l;
5254 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5255 l_entity := 3;
5256 OPEN l_line_scredit_csr_h;
5257 END IF;
5258
5259 -- Loop over fetched records
5260 l_count := 1;
5261
5262 LOOP
5263 IF l_entity = 1 THEN
5264 FETCH l_line_scredit_csr_s INTO l_rec;
5265 EXIT WHEN l_line_scredit_csr_s%NOTFOUND;
5266 ELSIF l_entity = 2 THEN
5267 FETCH l_line_scredit_csr_l INTO l_rec;
5268 EXIT WHEN l_line_scredit_csr_l%NOTFOUND;
5269 ELSIF l_entity = 3 THEN
5270 FETCH l_line_scredit_csr_h INTO l_rec;
5271 EXIT WHEN l_line_scredit_csr_h%NOTFOUND;
5272 ELSE
5273 EXIT;
5274 END IF;
5275
5276 x_line_scredit_tbl(l_count).attribute1 := l_rec.ATTRIBUTE1;
5277 x_line_scredit_tbl(l_count).attribute10 := l_rec.ATTRIBUTE10;
5278 x_line_scredit_tbl(l_count).attribute11 := l_rec.ATTRIBUTE11;
5279 x_line_scredit_tbl(l_count).attribute12 := l_rec.ATTRIBUTE12;
5280 x_line_scredit_tbl(l_count).attribute13 := l_rec.ATTRIBUTE13;
5281 x_line_scredit_tbl(l_count).attribute14 := l_rec.ATTRIBUTE14;
5282 x_line_scredit_tbl(l_count).attribute15 := l_rec.ATTRIBUTE15;
5283 x_line_scredit_tbl(l_count).attribute2 := l_rec.ATTRIBUTE2;
5284 x_line_scredit_tbl(l_count).attribute3 := l_rec.ATTRIBUTE3;
5285 x_line_scredit_tbl(l_count).attribute4 := l_rec.ATTRIBUTE4;
5286 x_line_scredit_tbl(l_count).attribute5 := l_rec.ATTRIBUTE5;
5287 x_line_scredit_tbl(l_count).attribute6 := l_rec.ATTRIBUTE6;
5288 x_line_scredit_tbl(l_count).attribute7 := l_rec.ATTRIBUTE7;
5289 x_line_scredit_tbl(l_count).attribute8 := l_rec.ATTRIBUTE8;
5290 x_line_scredit_tbl(l_count).attribute9 := l_rec.ATTRIBUTE9;
5291 x_line_scredit_tbl(l_count).context := l_rec.CONTEXT;
5292 x_line_scredit_tbl(l_count).created_by := l_rec.CREATED_BY;
5293 x_line_scredit_tbl(l_count).creation_date := l_rec.CREATION_DATE;
5294 x_line_scredit_tbl(l_count).dw_update_advice_flag := l_rec.DW_UPDATE_ADVICE_FLAG;
5295 x_line_scredit_tbl(l_count).header_id := l_rec.HEADER_ID;
5296 x_line_scredit_tbl(l_count).last_updated_by := l_rec.LAST_UPDATED_BY;
5297 x_line_scredit_tbl(l_count).last_update_date := l_rec.LAST_UPDATE_DATE;
5298 x_line_scredit_tbl(l_count).last_update_login := l_rec.LAST_UPDATE_LOGIN;
5299 x_line_scredit_tbl(l_count).line_id := l_rec.LINE_ID;
5300 x_line_scredit_tbl(l_count).percent := l_rec.PERCENT;
5301 x_line_scredit_tbl(l_count).salesrep_id := l_rec.SALESREP_ID;
5302 x_line_scredit_tbl(l_count).sales_credit_type_id := l_rec.sales_credit_type_id;
5303 x_line_scredit_tbl(l_count).sales_credit_id := l_rec.SALES_CREDIT_ID;
5304 x_line_scredit_tbl(l_count).wh_update_date := l_rec.WH_UPDATE_DATE;
5305 x_line_scredit_tbl(l_count).lock_control := l_rec.LOCK_CONTROL;
5306
5307 l_count := l_count + 1;
5308
5309 END LOOP;
5310
5311 IF l_entity = 1 THEN
5312 CLOSE l_line_scredit_csr_s;
5313 ELSIF l_entity = 2 THEN
5314 CLOSE l_line_scredit_csr_l;
5315 ELSIF l_entity = 3 THEN
5316 CLOSE l_line_scredit_csr_h;
5317 END IF;
5318
5319 -- PK sent and no rows found
5320
5321 IF
5322 (p_sales_credit_id IS NOT NULL
5323 AND
5324 p_sales_credit_id <> FND_API.G_MISS_NUM)
5325 AND
5326 (x_Line_Scredit_tbl.COUNT = 0)
5327 THEN
5328 RAISE NO_DATA_FOUND;
5329 END IF;
5330
5331
5332 -- Return fetched table
5333
5334 IF l_debug_level > 0 THEN
5335 oe_debug_pub.add( 'EXITING OE_LINE_SCREDIT_UTIL.QUERY_ROWS' , 1 ) ;
5336 END IF;
5337
5338
5339 END IF;
5340
5341 EXCEPTION
5342 WHEN OTHERS THEN
5343 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5344 OE_MSG_PUB.Add_Exc_Msg
5345 ( G_PKG_NAME
5346 , 'Line Scredit Query_Row'
5347 );
5348 END IF;
5349 END;
5350
5351
5352 END OE_VERSION_HISTORY_UTIL;