[Home] [Help]
PACKAGE BODY: APPS.OE_CHG_ORDER_PVT
Source
1 PACKAGE BODY OE_CHG_ORDER_PVT AS
2 /* $Header: OEXVCHGB.pls 120.7 2007/11/21 09:55:30 kshashan ship $ */
3
4 -- Start of Comments
5 -- API name OE_CHG_ORDER_PVT
6 -- Type PRIVATE
7 -- Function
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- End of Comments
19
20 PROCEDURE Start_ChangeOrderFlow
21 ( p_itemtype in VARCHAR2
22 , p_itemkey in VARCHAR2
23 )
24 IS
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29 IF l_debug_level > 0 THEN
30 oe_debug_pub.add( 'BEFORE START_CHANGEORDER FLOW' ) ;
31 oe_debug_pub.add( 'ITEM TYPE IS :'|| P_ITEMTYPE ) ;
32 oe_debug_pub.add( 'ITEM KEY IS :' || P_ITEMKEY ) ;
33 END IF;
34
35 WF_ENGINE.StartProcess(p_itemtype, p_itemkey);
36 IF l_debug_level > 0 THEN
37 oe_debug_pub.add( 'AFTER START_CHANGEORDER FLOW' ) ;
38 END IF;
39 EXCEPTION
40 WHEN OTHERS THEN
41 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
42 THEN
43 OE_MSG_PUB.Add_Exc_Msg
44 (G_PKG_NAME
45 , 'Start_ChangeOrderFlow'
46 );
47 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
48 END IF;
49 END Start_ChangeOrderFlow;
50
51 -- This procedure is called by the Notification Window
52 -- to create a Change Order Item Type and start a flow
53 -- for sending a FYI notification to the resolving
54 -- responsibility
55
56 -- This procedure uses Autonomous transaction to commit
57 -- the process of starting a flow for sending FYI. In this
58 -- way, the commit or rollback within this procedure will
59 -- not affect other forms like Error Message form.
60
61 Procedure Create_ChgOrderWorkItem
62 ( p_Workflow_Process IN VARCHAR2
63 ,p_resolving_role IN VARCHAR2
64 ,p_resolving_name IN VARCHAR2
65 ,p_user_text IN VARCHAR2
66 )
67 IS
68 Pragma AUTONOMOUS_TRANSACTION;
69
70 l_debug_file VARCHAR2(240);
71
72 l_process_name VARCHAR2(30);
73 l_chgord_item_type VARCHAR2(30) := 'OECHGORD';
74 l_wf_item_key NUMBER;
75 l_document_body VARCHAR2(150);
76 l_resolving_role_name VARCHAR2(150);
77 l_order_number NUMBER;
78 l_user_name VARCHAR2(255); -- Bug number 6633740
79
80 --
81 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
82 --
83 -- The following sql is not needed sind the value is being passed from
84 -- the form. See bug#3631508
85 -- cursor roles is -- 3051285
86 -- select name
87 -- from wf_roles
88 -- where display_name = p_resolving_role;
89
90 BEGIN
91
92 -- oe_debug_pub.Debug_On;
93 IF l_debug_level > 0 THEN
94 oe_debug_pub.add( 'IN CREATE_CHGORDERWORKITEM' ) ;
95 oe_debug_pub.add( 'P_RESOLVING_NAME->' || p_resolving_name);
96 END IF;
97
98 -- Generate a unique item key to create a flow
99 select oe_wf_key_s.nextval into l_wf_item_key
100 from dual;
101
102 IF l_debug_level > 0 THEN
103 oe_debug_pub.add( 'WF ITEM KEY IS :'|| L_WF_ITEM_KEY ) ;
104 END IF;
105
106 -- retrieve the NAME from DISPLAY_NAME
107 -- The following sql is not needed sind the value is being passed from
108 -- the form. See bug#3631508
109 -- open roles; -- added for 3051285
110 -- fetch roles into l_resolving_role_name;
111 -- close roles;
112
113 /* commented and replaced with above FETCH for 3051285
114 BEGIN -- block and exception handler added for 2166974
115 select name
116 into l_resolving_role_name
117 from wf_roles
118 where display_name = p_resolving_role;
119 EXCEPTION
120 when too_many_rows then null;
121 END;
122 */
123
124 l_process_name := p_Workflow_Process;
125
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'PROCESS NAME IS : ' || L_PROCESS_NAME ) ;
128 oe_debug_pub.add( 'RESOLVING ROLE IS : '|| L_RESOLVING_ROLE_NAME ) ;
129 oe_debug_pub.add( 'USER TEXT IS :' || P_USER_TEXT ) ;
130 END IF;
131
132 -- Create the Change Order Item
133 WF_ENGINE.CreateProcess(l_chgord_item_type,to_char(l_wf_item_key),l_process_name);
134
135 -- Set the Change Order Item Attributes
136
137 /* wf_engine.SetItemAttrText(l_chgord_item_type
138 , l_wf_item_key
139 , 'BOOKING_DOCUMENT'
140 , 'PLSQL:OE_CHG_ORDER_PVT.Generate_PLSQLDoc/' || l_wf_item_key);
141 */
142 wf_engine.SetItemAttrText(l_chgord_item_type
143 , l_wf_item_key
144 , 'USER_TEXT'
145 ,p_user_text);
146
147 -- Set the resolving role for the constraint.
148
149 WF_ENGINE.SetItemAttrText(l_chgord_item_type
150 ,l_wf_item_key
151 ,'RESOLVING_ROLE'
152 ,p_resolving_name);
153
154 -- Retrieve the value of the order number from the global variable
155 l_order_number := OE_CHG_ORDER_PVT.G_ORDER_NUMBER;
156 oe_msg_pub.add('Order Number is: ' || l_order_number);
157
158 -- Set the order number context for the message header
159
160 WF_ENGINE.SetItemAttrNumber(l_chgord_item_type
161 ,l_wf_item_key
162 ,'ORDER_NUMBER'
163 , l_order_number);
164 /*
165 l_document_body := wf_engine.GetItemAttrText(l_chgord_item_type
166 , l_wf_item_key
167 , 'BOOKING_DOCUMENT');
168
169 oe_debug_pub.add('Value of document is :' || l_document_body);
170 */
171
172 -- Bug number 6633740
173 IF l_debug_level > 0 THEN
174 oe_debug_pub.add( 'Assigning From user to workflow '|| FND_GLOBAL.USER_ID ) ;
175 END IF;
176 BEGIN
177 select user_name
178 into l_user_name
179 from fnd_user
180 where user_id = FND_GLOBAL.USER_ID;
181 EXCEPTION
182 WHEN OTHERS THEN
183 l_user_name := null; -- do not set FROM_ROLE then
184 END;
185
186 IF (l_user_name is not NULL) THEN
187 WF_ENGINE.SetItemAttrText( l_chgord_item_type
188 ,l_wf_item_key
189 ,'NOTIFICATION_FROM_ROLE'
190 ,l_user_name);
191 END IF;
192 -- Bug number 6633740
193
194 WF_ENGINE.StartProcess(l_chgord_item_type, to_char(l_wf_item_key));
195
196 -- Make a call to the StartChangeOrderFlow to start the change order process
197 -- Start_ChangeOrderFlow(l_chgord_item_type, to_char(l_wf_item_key));
198
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( 'EXITING CREATE_CHGORDERWORKITEM' ) ;
201 END IF;
202 commit;
203 EXCEPTION
204 WHEN OTHERS THEN
205 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
206 THEN
207 OE_MSG_PUB.Add_Exc_Msg
208 ( G_PKG_NAME
209 , 'Create_ChgOrderWorkItem');
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 --oe_debug_pub.dumpdebug;
214 --oe_debug_pub.Debug_Off;
215
216 END Create_ChgOrderWorkItem;
217
218 -- This procedure is used to update the free form text entered by the
219 -- user in the Notification form and pass it to the PLSQL Document
220 -- buffer
221
222
223 PROCEDURE Update_User_Text(p_user_text in varchar2)
224
225 IS
226
227 --
228 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
229 --
230 BEGIN
231
232 -- Set The global variable to the user text entered in the UI
233 OE_CHG_ORDER_PVT.G_USER_TEXT := p_user_text;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
238 THEN
239 OE_MSG_PUB.Add_Exc_Msg
240 ( G_PKG_NAME
241 , 'Create_ChgOrderWorkItem');
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243 END IF;
244
245
246 END Update_User_Text;
247
248
249 PROCEDURE Update_Order_Number(p_order_number in NUMBER)
250
251 IS
252
253 --
254 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
255 --
256 BEGIN
257
258 -- Set The global variable to the user text entered in the UI
259 OE_CHG_ORDER_PVT.G_ORDER_NUMBER := p_order_number;
260
261 EXCEPTION
262
263 WHEN OTHERS THEN
264 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
265 THEN
266 OE_MSG_PUB.Add_Exc_Msg
267 ( G_PKG_NAME
268 , 'Create_ChgOrderWorkItem');
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF;
271
272 END Update_Order_Number;
273
274
275 -- This procedure is written to create a dynamic message
276 -- text for the notification sent to the resolving responsibility.
277 -- Based on the display type to be used by the end user for viewing
278 -- the notification, the message body will be formatted and displayed.
279
280 PROCEDURE Generate_PLSQLDoc(p_document_id in varchar2,
281 p_display_type in varchar2,
282 p_document in out NOCOPY /* file.sql.39 change */ varchar2,
283 p_document_type in out NOCOPY /* file.sql.39 change */ varchar2)
284 IS
285
286 l_user_text VARCHAR2(250);
287
288 --
289 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
290 --
291 BEGIN
292
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( 'INSIDE GENERATE PLSQL DOC' ) ;
295 END IF;
296 -- Set the document type based on the display type
297 p_document_type := p_display_type;
298
299 -- JPN: Replace this with p_document
300
301
302 l_user_text := wf_engine.GetItemAttrText('OECHGORD'
303 ,p_document_id
304 , 'USER_TEXT');
305 IF l_debug_level > 0 THEN
306 oe_debug_pub.add( 'USER TEXT IS: ' || L_USER_TEXT ) ;
307 END IF;
308
309 -- p_document := OE_CHG_ORDER_PVT.G_USER_TEXT;
310 p_document := l_user_text;
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add( 'VALUE OF THE P_DOCUMENT IS: '|| P_DOCUMENT ) ;
313 END IF;
314
315 -- p_document := 'Update of Line quantity';
316
317 -- Create an HTML text buffer
318 if (p_display_type = 'text/html') then
319
320 -- Build the page body with the data
321 p_document := htf.bold('Change Order Approval for: ') || p_document ;
322 p_document_type := 'text/html';
323
324 return;
325 end if;
326
327 -- Create a plain text buffer
328 if (p_display_type = 'text/plain') then
329
330 p_document := 'Change approval required for: ' || p_document;
331 p_document_type := 'text/plain';
332
333 return;
334 end if;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
339 THEN
340 OE_MSG_PUB.Add_Exc_Msg
341 (G_PKG_NAME
342 , 'Generate_PLSQLDoc'
343 );
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346
347 END Generate_PLSQLDoc;
348
349
350 Procedure RecordLineHist
351 (p_line_id In Number
352 ,p_line_rec In OE_ORDER_PUB.LINE_REC_TYPE
353 := OE_Order_PUB.G_MISS_LINE_REC
354 ,p_hist_type_code In Varchar2
355 ,p_reason_code In varchar2
356 ,p_comments IN Varchar2
357 ,p_audit_flag IN Varchar2 := null
358 ,p_version_flag IN Varchar2 := null
359 ,p_phase_change_flag IN Varchar2 := null
360 ,p_version_number IN NUMBER := null
361 ,p_reason_id IN NUMBER := NULL
362 ,p_wf_activity_code IN Varchar2 := null
363 ,p_wf_result_code IN Varchar2 := null
364 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
365 ) IS
366 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE := OE_Order_PUB.G_MISS_LINE_REC;
367 l_err_text Varchar2(80);
368 l_line_id number := p_line_id;
369 l_index_id number;
370 l_result VARCHAR2(30);
371 l_new_ordered_quantity number;
372 l_latest_can_qty number ;
373
374 --
375 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
376 --
377 BEGIN
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add( 'ENTERING RECORDLINEHIST' , 1 ) ;
380 END IF;
381
382 -- JPN: Added the G_MISS_NUM check before inserting the history record.
383 if (p_line_rec.line_id is not null AND
384 p_line_rec.line_id <> FND_API.G_MISS_NUM) then
385 l_line_id := p_line_rec.line_id;
386 l_line_rec := p_line_rec;
387 else
388 -- query the line record
389 OE_LINE_UTIL.Query_Row (p_line_id => l_line_id ,x_line_rec => l_line_rec);
390 end if;
391 IF p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM AND
392 p_line_rec.ordered_quantity IS NOT NULL THEN
393
394 -- bug 3443676, derive new ordered quantity from global picture
395 OE_Order_Util.Return_Glb_Ent_Index(p_entity_code => 'LINE', p_entity_id => l_line_id, x_index => l_index_id, x_result => l_result, x_return_status => x_return_status);
396
397 IF l_result = FND_API.G_TRUE THEN
398 l_new_ordered_quantity := OE_Order_Util.G_Line_Tbl(l_index_id).ordered_quantity;
399 ELSE
400 l_new_ordered_quantity := p_line_rec.ordered_quantity;
401 END IF;
402
403 IF l_new_ordered_quantity < l_line_rec.ordered_quantity THEN
404 l_latest_can_qty := l_line_rec.ordered_quantity - l_new_ordered_quantity ;
405 END IF;
406 END IF;
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add( 'INSERTING HISTORY FOR LINE ID : '|| TO_CHAR ( P_LINE_ID ) , 5 ) ;
409 END IF;
410 -- OPM 02/JUN/2000 add 3 process attributes
411 BEGIN
412 l_err_text := null;
413 INSERT INTO OE_ORDER_LINES_HISTORY (
414 Line_Id
415 , WF_ACTIVITY_CODE
416 , WF_RESULT_CODE
417 , REASON_CODE
418 , HIST_COMMENTS
419 , HIST_TYPE_CODE
420 , HIST_CREATION_DATE
421 , HIST_CREATED_BY
422 , latest_cancelled_quantity
423 , ACCOUNTING_RULE_ID
424 , ACCOUNTING_RULE_DURATION
425 , ACTUAL_ARRIVAL_DATE
426 , ACTUAL_SHIPMENT_DATE
427 , AGREEMENT_ID
428 , ARRIVAL_SET_ID
429 , ATO_LINE_ID
430 , ATTRIBUTE1
431 , ATTRIBUTE10
432 , ATTRIBUTE11
433 , ATTRIBUTE12
434 , ATTRIBUTE13
435 , ATTRIBUTE14
436 , ATTRIBUTE15
437 , ATTRIBUTE16 --For bug 2184255
438 , ATTRIBUTE17
439 , ATTRIBUTE18
440 , ATTRIBUTE19
441 , ATTRIBUTE2
442 , ATTRIBUTE20
443 , ATTRIBUTE3
444 , ATTRIBUTE4
445 , ATTRIBUTE5
446 , ATTRIBUTE6
447 , ATTRIBUTE7
448 , ATTRIBUTE8
449 , ATTRIBUTE9
450 , AUTO_SELECTED_QUANTITY
451 , AUTHORIZED_TO_SHIP_FLAG
452 , BLANKET_NUMBER
453 , BLANKET_LINE_NUMBER
454 , BLANKET_VERSION_NUMBER
455 , BOOKED_FLAG
456 , CANCELLED_FLAG
460 , COMPONENT_SEQUENCE_ID
457 , CANCELLED_QUANTITY
458 , COMPONENT_CODE
459 , COMPONENT_NUMBER
461 , CONFIG_HEADER_ID
462 , CONFIG_REV_NBR
463 , CONFIG_DISPLAY_SEQUENCE
464 , CONFIGURATION_ID
465 , CONTEXT
466 , CREATED_BY
467 , CREATION_DATE
468 , CREDIT_INVOICE_LINE_ID
469 , CUSTOMER_LINE_NUMBER
470 , CUSTOMER_SHIPMENT_NUMBER
471 , CUSTOMER_ITEM_NET_PRICE
472 , CUSTOMER_PAYMENT_TERM_ID
473 , CUSTOMER_DOCK_CODE
474 , CUSTOMER_JOB
475 , CUSTOMER_PRODUCTION_LINE
476 , CUST_PRODUCTION_SEQ_NUM
477 , CUSTOMER_TRX_LINE_ID
478 , CUST_MODEL_SERIAL_NUMBER
479 , CUST_PO_NUMBER
480 , DELIVERY_LEAD_TIME
481 , DELIVER_TO_CONTACT_ID
482 , DELIVER_TO_ORG_ID
483 , DEMAND_BUCKET_TYPE_CODE
484 , DEMAND_CLASS_CODE
485 , DEP_PLAN_REQUIRED_FLAG
486 , DROP_SHIP_FLAG
487 , EARLIEST_ACCEPTABLE_DATE
488 , END_ITEM_UNIT_NUMBER
489 , EXPLOSION_DATE
490 , FIRST_ACK_CODE
491 , FIRST_ACK_DATE
492 , FOB_POINT_CODE
493 , FREIGHT_CARRIER_CODE
494 , FREIGHT_TERMS_CODE
495 , FULFILLED_QUANTITY
496 , FULFILLED_FLAG
497 , FULFILLMENT_METHOD_CODE
498 , GLOBAL_ATTRIBUTE1
499 , GLOBAL_ATTRIBUTE10
500 , GLOBAL_ATTRIBUTE11
501 , GLOBAL_ATTRIBUTE12
502 , GLOBAL_ATTRIBUTE13
503 , GLOBAL_ATTRIBUTE14
504 , GLOBAL_ATTRIBUTE15
505 , GLOBAL_ATTRIBUTE16
506 , GLOBAL_ATTRIBUTE17
507 , GLOBAL_ATTRIBUTE18
508 , GLOBAL_ATTRIBUTE19
509 , GLOBAL_ATTRIBUTE2
510 , GLOBAL_ATTRIBUTE20
511 , GLOBAL_ATTRIBUTE3
512 , GLOBAL_ATTRIBUTE4
513 , GLOBAL_ATTRIBUTE5
514 , GLOBAL_ATTRIBUTE6
515 , GLOBAL_ATTRIBUTE7
516 , GLOBAL_ATTRIBUTE8
517 , GLOBAL_ATTRIBUTE9
518 , GLOBAL_ATTRIBUTE_CATEGORY
519 , HEADER_ID
520 , INDUSTRY_ATTRIBUTE1
521 , INDUSTRY_ATTRIBUTE10
522 , INDUSTRY_ATTRIBUTE11
523 , INDUSTRY_ATTRIBUTE12
524 , INDUSTRY_ATTRIBUTE13
525 , INDUSTRY_ATTRIBUTE14
526 , INDUSTRY_ATTRIBUTE15
527 , INDUSTRY_ATTRIBUTE16
528 , INDUSTRY_ATTRIBUTE17
529 , INDUSTRY_ATTRIBUTE18
530 , INDUSTRY_ATTRIBUTE19
531 , INDUSTRY_ATTRIBUTE20
532 , INDUSTRY_ATTRIBUTE21
533 , INDUSTRY_ATTRIBUTE22
534 , INDUSTRY_ATTRIBUTE23
535 , INDUSTRY_ATTRIBUTE24
536 , INDUSTRY_ATTRIBUTE25
537 , INDUSTRY_ATTRIBUTE26
538 , INDUSTRY_ATTRIBUTE27
539 , INDUSTRY_ATTRIBUTE28
540 , INDUSTRY_ATTRIBUTE29
541 , INDUSTRY_ATTRIBUTE30
542 , INDUSTRY_ATTRIBUTE2
543 , INDUSTRY_ATTRIBUTE3
544 , INDUSTRY_ATTRIBUTE4
545 , INDUSTRY_ATTRIBUTE5
546 , INDUSTRY_ATTRIBUTE6
547 , INDUSTRY_ATTRIBUTE7
548 , INDUSTRY_ATTRIBUTE8
549 , INDUSTRY_ATTRIBUTE9
550 , INDUSTRY_CONTEXT
551 , INTMED_SHIP_TO_CONTACT_ID
552 , INTMED_SHIP_TO_ORG_ID
553 , INVENTORY_ITEM_ID
554 , INVOICE_INTERFACE_STATUS_CODE
555 , INVOICE_TO_CONTACT_ID
556 , INVOICE_TO_ORG_ID
557 , INVOICED_QUANTITY
558 , INVOICING_RULE_ID
559 , ORDERED_ITEM_ID
560 , ITEM_IDENTIFIER_TYPE
561 , ORDERED_ITEM
562 , ITEM_REVISION
563 , ITEM_TYPE_CODE
564 , LAST_ACK_CODE
565 , LAST_ACK_DATE
566 , LAST_UPDATED_BY
567 , LAST_UPDATE_DATE
568 , LAST_UPDATE_LOGIN
569 , LATEST_ACCEPTABLE_DATE
570 , LINE_CATEGORY_CODE
571 , LINE_NUMBER
572 , LINE_TYPE_ID
573 , LINK_TO_LINE_ID
574 , MODEL_GROUP_NUMBER
575 , MFG_COMPONENT_SEQUENCE_ID
576 , OPEN_FLAG
577 , OPTION_FLAG
578 , OPTION_NUMBER
579 , ORDERED_QUANTITY
580 , ORDERED_QUANTITY2 -- OPM 02/JUN/00
581 , ORDER_QUANTITY_UOM
582 , ORDERED_QUANTITY_UOM2 -- OPM 02/JUN/00
583 --, ORG_ID
584 , ORDER_SOURCE_ID
585 , ORIG_SYS_DOCUMENT_REF
586 , ORIG_SYS_LINE_REF
587 , ORIG_SYS_SHIPMENT_REF
588 , CHANGE_SEQUENCE
589 , OVER_SHIP_REASON_CODE
590 , OVER_SHIP_RESOLVED_FLAG
591 , PAYMENT_TERM_ID
592 , PLANNING_PRIORITY
593 , PREFERRED_GRADE -- OPM 02/JUN/00
594 , PRICE_LIST_ID
595 , PRICE_REQUEST_CODE -- PROMOTIONS SEP/01
596 , PRICING_ATTRIBUTE1
597 , PRICING_ATTRIBUTE10
598 , PRICING_ATTRIBUTE2
599 , PRICING_ATTRIBUTE3
603 , PRICING_ATTRIBUTE7
600 , PRICING_ATTRIBUTE4
601 , PRICING_ATTRIBUTE5
602 , PRICING_ATTRIBUTE6
604 , PRICING_ATTRIBUTE8
605 , PRICING_ATTRIBUTE9
606 , PRICING_CONTEXT
607 , PRICING_DATE
608 , PRICING_QUANTITY
609 , PRICING_QUANTITY_UOM
610 , PROGRAM_APPLICATION_ID
611 , PROGRAM_ID
612 , PROGRAM_UPDATE_DATE
613 , PROJECT_ID
614 , PROMISE_DATE
615 , RE_SOURCE_FLAG
616 , REFERENCE_CUSTOMER_TRX_LINE_ID
617 , REFERENCE_HEADER_ID
618 , REFERENCE_LINE_ID
619 , REFERENCE_TYPE
620 , REQUEST_DATE
621 , REQUEST_ID
622 , RETURN_ATTRIBUTE1
623 , RETURN_ATTRIBUTE10
624 , RETURN_ATTRIBUTE11
625 , RETURN_ATTRIBUTE12
626 , RETURN_ATTRIBUTE13
627 , RETURN_ATTRIBUTE14
628 , RETURN_ATTRIBUTE15
629 , RETURN_ATTRIBUTE2
630 , RETURN_ATTRIBUTE3
631 , RETURN_ATTRIBUTE4
632 , RETURN_ATTRIBUTE5
633 , RETURN_ATTRIBUTE6
634 , RETURN_ATTRIBUTE7
635 , RETURN_ATTRIBUTE8
636 , RETURN_ATTRIBUTE9
637 , RETURN_CONTEXT
638 , RETURN_REASON_CODE
639 , RLA_SCHEDULE_TYPE_CODE
640 , SALESREP_ID
641 , SCHEDULE_ARRIVAL_DATE
642 , SCHEDULE_SHIP_DATE
643 , SCHEDULE_STATUS_CODE
644 , SHIPMENT_NUMBER
645 , SHIPMENT_PRIORITY_CODE
646 , SHIPPED_QUANTITY
647 , SHIPPING_METHOD_CODE
648 , SHIPPING_QUANTITY
649 , SHIPPING_QUANTITY_UOM
650 , SHIP_FROM_ORG_ID
651 , SUBINVENTORY
652 , SHIP_SET_ID
653 , SHIP_TOLERANCE_ABOVE
654 , SHIP_TOLERANCE_BELOW
655 , SHIPPABLE_FLAG
656 , SHIPPING_INTERFACED_FLAG
657 , SHIP_TO_CONTACT_ID
658 , SHIP_TO_ORG_ID
659 , SHIP_MODEL_COMPLETE_FLAG
660 , SOLD_TO_ORG_ID
661 , SOLD_FROM_ORG_ID
662 , SORT_ORDER
663 , SOURCE_DOCUMENT_ID
664 , SOURCE_DOCUMENT_LINE_ID
665 , SOURCE_DOCUMENT_TYPE_ID
666 , SOURCE_TYPE_CODE
667 , SPLIT_FROM_LINE_ID
668 , LINE_SET_ID
669 , SPLIT_BY
670 , model_remnant_flag
671 , TASK_ID
672 , TAX_CODE
673 , TAX_DATE
674 , TAX_EXEMPT_FLAG
675 , TAX_EXEMPT_NUMBER
676 , TAX_EXEMPT_REASON_CODE
677 , TAX_POINT_CODE
678 , TAX_RATE
679 , TAX_VALUE
680 , TOP_MODEL_LINE_ID
681 , UNIT_LIST_PRICE
682 , UNIT_SELLING_PRICE
683 , VISIBLE_DEMAND_FLAG
684 , VEH_CUS_ITEM_CUM_KEY_ID
685 , SHIPPING_INSTRUCTIONS
686 , PACKING_INSTRUCTIONS
687 , SERVICE_TXN_REASON_CODE
688 , SERVICE_TXN_COMMENTS
689 , SERVICE_DURATION
690 , SERVICE_PERIOD
691 , SERVICE_START_DATE
692 , SERVICE_END_DATE
693 , SERVICE_COTERMINATE_FLAG
694 , UNIT_LIST_PERCENT
695 , UNIT_SELLING_PERCENT
696 , UNIT_PERCENT_BASE_PRICE
697 , SERVICE_NUMBER
698 , SERVICE_REFERENCE_LINE_ID
699 , TP_CONTEXT
700 , TP_ATTRIBUTE1
701 , TP_ATTRIBUTE2
702 , TP_ATTRIBUTE3
703 , TP_ATTRIBUTE4
704 , TP_ATTRIBUTE5
705 , TP_ATTRIBUTE6
706 , TP_ATTRIBUTE7
707 , TP_ATTRIBUTE8
708 , TP_ATTRIBUTE9
709 , TP_ATTRIBUTE10
710 , TP_ATTRIBUTE11
711 , TP_ATTRIBUTE12
712 , TP_ATTRIBUTE13
713 , TP_ATTRIBUTE14
714 , TP_ATTRIBUTE15
715 , FLOW_STATUS_CODE
716 , RESPONSIBILITY_ID
717 , ORIGINAL_INVENTORY_ITEM_ID
718 , ORIGINAL_ITEM_IDENTIFIER_TYPE
719 , ORIGINAL_ORDERED_ITEM_ID
720 , ORIGINAL_ORDERED_ITEM
721 , ITEM_RELATIONSHIP_TYPE
722 , ITEM_SUBSTITUTION_TYPE_CODE
723 , LATE_DEMAND_PENALTY_FACTOR
724 , OVERRIDE_ATP_DATE_CODE
725 , USER_ITEM_DESCRIPTION
726 -- QUOTING changes
727 , TRANSACTION_PHASE_CODE
728 , SOURCE_DOCUMENT_VERSION_NUMBER
729 -- QUOTING changes END
730 -- VERSIONING changes
731 , AUDIT_FLAG
732 , VERSION_FLAG
733 , PHASE_CHANGE_FLAG
734 , VERSION_NUMBER
735 , REASON_ID
736 -- VERSIONING changes END
737 , ORIGINAL_LIST_PRICE -- Override List Price
738 --Key Transaction Dates
739 , order_firmed_date
740 , actual_fulfillment_date
741 --recurring charges
742 , charge_periodicity_code
743 --Customer Acceptance
744 , Contingency_id
745 , Revrec_event_code
746 , Revrec_Expiration_days
747 , Accepted_By
748 , Accepted_Quantity
749 , Revrec_comments
753 , Revrec_implicit_flag
750 , Revrec_reference_document
751 , Revrec_signature
752 , Revrec_signature_date
754 )
755 VALUES
756 (
757 l_line_rec.line_id
758 , p_wf_activity_code
759 , P_WF_RESULT_CODE
760 , P_REASON_CODE
761 , P_COMMENTS
762 , P_HIST_TYPE_CODE
763 , sysdate
764 , nvl(FND_GLOBAL.USER_ID, -1)
765 , l_latest_can_qty
766 , l_line_rec.accounting_rule_id
767 , l_line_rec.accounting_rule_duration
768 , l_line_rec.actual_arrival_date
769 , l_line_rec.actual_shipment_date
770 , l_line_rec.agreement_id
771 , l_line_rec.arrival_set_id
772 , l_line_rec.ato_line_id
773 , l_line_rec.attribute1
774 , l_line_rec.attribute10
775 , l_line_rec.attribute11
776 , l_line_rec.attribute12
777 , l_line_rec.attribute13
778 , l_line_rec.attribute14
779 , l_line_rec.attribute15
780 , l_line_rec.attribute16 --For bug 2184255
781 , l_line_rec.attribute17
782 , l_line_rec.attribute18
783 , l_line_rec.attribute19
784 , l_line_rec.attribute2
785 , l_line_rec.attribute20
786 , l_line_rec.attribute3
787 , l_line_rec.attribute4
788 , l_line_rec.attribute5
789 , l_line_rec.attribute6
790 , l_line_rec.attribute7
791 , l_line_rec.attribute8
792 , l_line_rec.attribute9
793 , l_line_rec.auto_selected_quantity
794 , l_line_rec.authorized_to_ship_flag
795 , l_line_rec.blanket_number
796 , l_line_rec.blanket_line_number
797 , l_line_rec.blanket_version_number
798 , l_line_rec.booked_flag
799 , l_line_rec.cancelled_flag
800 , l_line_rec.cancelled_quantity
801 , l_line_rec.component_code
802 , l_line_rec.component_number
803 , l_line_rec.component_sequence_id
804 , l_line_rec.config_header_id
805 , l_line_rec.config_rev_nbr
806 , l_line_rec.config_display_sequence
807 , l_line_rec.configuration_id
808 , l_line_rec.context
809 , l_line_rec.created_by
810 , l_line_rec.creation_date
811 , l_line_rec.credit_invoice_line_id
812 , l_line_rec.customer_line_number
813 , l_line_rec.customer_shipment_number
814 , l_line_rec.customer_item_net_price
815 , l_line_rec.customer_payment_term_id
816 , l_line_rec.customer_dock_code
817 , l_line_rec.customer_job
818 , l_line_rec.customer_production_line
819 , l_line_rec.cust_production_seq_num
820 , l_line_rec.customer_trx_line_id
821 , l_line_rec.cust_model_serial_number
822 , l_line_rec.cust_po_number
823 , l_line_rec.delivery_lead_time
824 , l_line_rec.deliver_to_contact_id
825 , l_line_rec.deliver_to_org_id
826 , l_line_rec.demand_bucket_type_code
827 , l_line_rec.demand_class_code
828 , l_line_rec.dep_plan_required_flag
829 , l_line_rec.drop_ship_flag
830 , l_line_rec.earliest_acceptable_date
831 , l_line_rec.end_item_unit_number
832 , l_line_rec.explosion_date
833 , l_line_rec.first_ack_code
834 , l_line_rec.first_ack_date
835 , l_line_rec.fob_point_code
836 , l_line_rec.freight_carrier_code
837 , l_line_rec.freight_terms_code
838 , l_line_rec.fulfilled_quantity
839 , l_line_rec.fulfilled_flag
840 , l_line_rec.fulfillment_method_code
841 , l_line_rec.global_attribute1
842 , l_line_rec.global_attribute10
843 , l_line_rec.global_attribute11
844 , l_line_rec.global_attribute12
845 , l_line_rec.global_attribute13
846 , l_line_rec.global_attribute14
847 , l_line_rec.global_attribute15
848 , l_line_rec.global_attribute16
849 , l_line_rec.global_attribute17
850 , l_line_rec.global_attribute18
851 , l_line_rec.global_attribute19
852 , l_line_rec.global_attribute2
853 , l_line_rec.global_attribute20
854 , l_line_rec.global_attribute3
855 , l_line_rec.global_attribute4
856 , l_line_rec.global_attribute5
857 , l_line_rec.global_attribute6
858 , l_line_rec.global_attribute7
859 , l_line_rec.global_attribute8
860 , l_line_rec.global_attribute9
861 , l_line_rec.global_attribute_category
862 , l_line_rec.header_id
863 , l_line_rec.industry_attribute1
864 , l_line_rec.industry_attribute10
865 , l_line_rec.industry_attribute11
866 , l_line_rec.industry_attribute12
867 , l_line_rec.industry_attribute13
868 , l_line_rec.industry_attribute14
869 , l_line_rec.industry_attribute15
870 , l_line_rec.industry_attribute16
871 , l_line_rec.industry_attribute17
872 , l_line_rec.industry_attribute18
873 , l_line_rec.industry_attribute19
877 , l_line_rec.industry_attribute23
874 , l_line_rec.industry_attribute20
875 , l_line_rec.industry_attribute21
876 , l_line_rec.industry_attribute22
878 , l_line_rec.industry_attribute24
879 , l_line_rec.industry_attribute25
880 , l_line_rec.industry_attribute26
881 , l_line_rec.industry_attribute27
882 , l_line_rec.industry_attribute28
883 , l_line_rec.industry_attribute29
884 , l_line_rec.industry_attribute30
885 , l_line_rec.industry_attribute2
886 , l_line_rec.industry_attribute3
887 , l_line_rec.industry_attribute4
888 , l_line_rec.industry_attribute5
889 , l_line_rec.industry_attribute6
890 , l_line_rec.industry_attribute7
891 , l_line_rec.industry_attribute8
892 , l_line_rec.industry_attribute9
893 , l_line_rec.industry_context
894 , l_line_rec.intermed_ship_to_contact_id
895 , l_line_rec.intermed_ship_to_org_id
896 , l_line_rec.inventory_item_id
897 , l_line_rec.invoice_interface_status_code
898 , l_line_rec.invoice_to_contact_id
899 , l_line_rec.invoice_to_org_id
900 , l_line_rec.invoiced_quantity
901 , l_line_rec.invoicing_rule_id
902 , l_line_rec.ordered_item_id
903 , l_line_rec.item_identifier_type
904 , l_line_rec.ordered_item
905 , l_line_rec.item_revision
906 , l_line_rec.item_type_code
907 , l_line_rec.last_ack_code
908 , l_line_rec.last_ack_date
909 , l_line_rec.last_updated_by
910 , l_line_rec.last_update_date
911 , l_line_rec.last_update_login
912 , l_line_rec.latest_acceptable_date
913 , l_line_rec.line_category_code
914 , l_line_rec.line_number
915 , l_line_rec.line_type_id
916 , l_line_rec.link_to_line_id
917 , l_line_rec.model_group_number
918 , l_line_rec.mfg_component_sequence_id
919 , l_line_rec.open_flag
920 , l_line_rec.option_flag
921 , l_line_rec.option_number
922 , l_line_rec.ordered_quantity
923 , l_line_rec.ordered_quantity2 -- OPM 02/JUN/00
924 , l_line_rec.order_quantity_uom
925 , l_line_rec.ordered_quantity_uom2 -- OPM 02/JUN/00
926 --, l_line_rec.org_id
927 , l_line_rec.order_source_id
928 , l_line_rec.orig_sys_document_ref
929 , l_line_rec.orig_sys_line_ref
930 , l_line_rec.orig_sys_shipment_ref
931 , l_line_rec.change_sequence
932 , l_line_rec.over_ship_reason_code
933 , l_line_rec.over_ship_resolved_flag
934 , l_line_rec.payment_term_id
935 , l_line_rec.planning_priority
936 , l_line_rec.preferred_grade -- OPM 02/JUN/00
937 , l_line_rec.price_list_id
938 , l_line_rec.price_request_code -- PROMOTIONS SEP/01
939 , l_line_rec.pricing_attribute1
940 , l_line_rec.pricing_attribute10
941 , l_line_rec.pricing_attribute2
942 , l_line_rec.pricing_attribute3
943 , l_line_rec.pricing_attribute4
944 , l_line_rec.pricing_attribute5
945 , l_line_rec.pricing_attribute6
946 , l_line_rec.pricing_attribute7
947 , l_line_rec.pricing_attribute8
948 , l_line_rec.pricing_attribute9
949 , l_line_rec.pricing_context
950 , l_line_rec.pricing_date
951 , l_line_rec.pricing_quantity
952 , l_line_rec.pricing_quantity_uom
953 , l_line_rec.program_application_id
954 , l_line_rec.program_id
955 , l_line_rec.program_update_date
956 , l_line_rec.project_id
957 , l_line_rec.promise_date
958 , l_line_rec.re_source_flag
959 , l_line_rec.reference_customer_trx_line_id
960 , l_line_rec.reference_header_id
961 , l_line_rec.reference_line_id
962 , l_line_rec.reference_type
963 , l_line_rec.request_date
964 , l_line_rec.request_id
965 , l_line_rec.return_attribute1
966 , l_line_rec.return_attribute10
967 , l_line_rec.return_attribute11
968 , l_line_rec.return_attribute12
969 , l_line_rec.return_attribute13
970 , l_line_rec.return_attribute14
971 , l_line_rec.return_attribute15
972 , l_line_rec.return_attribute2
973 , l_line_rec.return_attribute3
974 , l_line_rec.return_attribute4
975 , l_line_rec.return_attribute5
976 , l_line_rec.return_attribute6
977 , l_line_rec.return_attribute7
978 , l_line_rec.return_attribute8
979 , l_line_rec.return_attribute9
980 , l_line_rec.return_context
981 , l_line_rec.return_reason_code
982 , l_line_rec.rla_schedule_type_code
983 , l_line_rec.salesrep_id
984 , l_line_rec.schedule_arrival_date
985 , l_line_rec.schedule_ship_date
986 , l_line_rec.schedule_status_code
987 , l_line_rec.shipment_number
988 , l_line_rec.shipment_priority_code
989 , l_line_rec.shipped_quantity
990 , l_line_rec.shipping_method_code
991 , l_line_rec.shipping_quantity
995 , l_line_rec.ship_set_id
992 , l_line_rec.shipping_quantity_uom
993 , l_line_rec.ship_from_org_id
994 , l_line_rec.subinventory
996 , l_line_rec.ship_tolerance_above
997 , l_line_rec.ship_tolerance_below
998 , l_line_rec.shippable_flag
999 , l_line_rec.shipping_interfaced_flag
1000 , l_line_rec.ship_to_contact_id
1001 , l_line_rec.ship_to_org_id
1002 , l_line_rec.ship_model_complete_flag
1003 , l_line_rec.sold_to_org_id
1004 , l_line_rec.sold_from_org_id
1005 , l_line_rec.sort_order
1006 , l_line_rec.source_document_id
1007 , l_line_rec.source_document_line_id
1008 , l_line_rec.source_document_type_id
1009 , l_line_rec.source_type_code
1010 , l_line_rec.split_from_line_id
1011 , l_line_rec.line_set_id
1012 , l_line_rec.split_by
1013 , l_line_rec.model_remnant_flag
1014 , l_line_rec.task_id
1015 , l_line_rec.tax_code
1016 , l_line_rec.tax_date
1017 , l_line_rec.tax_exempt_flag
1018 , l_line_rec.tax_exempt_number
1019 , l_line_rec.tax_exempt_reason_code
1020 , l_line_rec.tax_point_code
1021 , l_line_rec.tax_rate
1022 , l_line_rec.tax_value
1023 , l_line_rec.top_model_line_id
1024 , l_line_rec.unit_list_price
1025 , l_line_rec.unit_selling_price
1026 , l_line_rec.visible_demand_flag
1027 , l_line_rec.veh_cus_item_cum_key_id
1028 , l_line_rec.shipping_instructions
1029 , l_line_rec.packing_instructions
1030 , l_line_rec.service_txn_reason_code
1031 , l_line_rec.service_txn_comments
1032 , l_line_rec.service_duration
1033 , l_line_rec.service_period
1034 , l_line_rec.service_start_date
1035 , l_line_rec.service_end_date
1036 , l_line_rec.service_coterminate_flag
1037 , l_line_rec.unit_list_percent
1038 , l_line_rec.unit_selling_percent
1039 , l_line_rec.unit_percent_base_price
1040 , l_line_rec.service_number
1041 , l_line_rec.service_reference_line_id
1042 , l_line_rec.tp_context
1043 , l_line_rec.tp_attribute1
1044 , l_line_rec.tp_attribute2
1045 , l_line_rec.tp_attribute3
1046 , l_line_rec.tp_attribute4
1047 , l_line_rec.tp_attribute5
1048 , l_line_rec.tp_attribute6
1049 , l_line_rec.tp_attribute7
1050 , l_line_rec.tp_attribute8
1051 , l_line_rec.tp_attribute9
1052 , l_line_rec.tp_attribute10
1053 , l_line_rec.tp_attribute11
1054 , l_line_rec.tp_attribute12
1055 , l_line_rec.tp_attribute13
1056 , l_line_rec.tp_attribute14
1057 , l_line_rec.tp_attribute15
1058 , l_line_rec.flow_status_code
1059 , nvl(FND_GLOBAL.RESP_ID, -1)
1060 , l_line_rec.original_inventory_item_id
1061 , l_line_rec.original_item_identifier_Type
1062 , l_line_rec.original_ordered_item_id
1063 , l_line_rec.original_ordered_item
1064 , l_line_rec.item_relationship_type
1065 , l_line_rec.item_substitution_type_code
1066 , l_line_rec.late_demand_penalty_factor
1067 , l_line_rec.Override_atp_date_code
1068 , l_line_rec.user_item_description
1069 -- QUOTING changes
1070 , l_line_rec.TRANSACTION_PHASE_CODE
1071 , l_line_rec.SOURCE_DOCUMENT_VERSION_NUMBER
1072 -- QUOTING changes END
1073 -- VERSIONING changes
1074 , p_AUDIT_FLAG
1075 , p_VERSION_FLAG
1076 , p_PHASE_CHANGE_FLAG
1077 , p_VERSION_NUMBER
1078 , p_reason_id
1079 -- VERSIONING changes END
1080 , l_line_rec.ORIGINAL_LIST_PRICE -- Override List Price
1081 --key transaction dates
1082 , l_line_rec.order_firmed_date
1083 , l_line_rec.actual_fulfillment_date
1084 --recurring charges
1085 , l_line_rec.charge_periodicity_code
1086 --Customer Acceptance
1087 , l_line_rec.Contingency_id
1088 , l_line_rec.Revrec_event_code
1089 , l_line_rec.Revrec_Expiration_days
1090 , l_line_rec.Accepted_By
1091 , l_line_rec.Accepted_Quantity
1092 , l_line_rec.Revrec_comments
1093 , l_line_rec.Revrec_reference_document
1094 , l_line_rec.Revrec_signature
1095 , l_line_rec.Revrec_signature_date
1096 , l_line_rec.Revrec_implicit_flag
1097 );
1098
1099 IF l_debug_level > 0 THEN
1100 oe_debug_pub.add( 'SUCCESSFULLY INSERTED LINE HISTORY RECORD' ) ;
1101 END IF;
1102 x_return_status := FND_API.G_RET_STS_SUCCESS;
1103 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
1104 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
1105 -- oe_sales_can_util.G_REQUIRE_REASON := FALSE;
1106 EXCEPTION WHEN OTHERS THEN
1107 l_err_text := substr(SQLERRM,1,74);
1108 IF l_debug_level > 0 THEN
1109 oe_debug_pub.add( 'ERROR:'||L_ERR_TEXT , 5 ) ;
1110 oe_debug_pub.add( 'HIST_TYPE_CODE VALUE:' || P_HIST_TYPE_CODE , 5 ) ;
1111 oe_debug_pub.add( 'IN INNER EXCEPTION' , 5 ) ;
1112 END IF;
1116 -- to fix bug 2295947
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- nocopy analysis
1114 END;
1115 EXCEPTION
1117 WHEN NO_DATA_FOUND THEN
1118 IF l_debug_level > 0 THEN
1119 oe_debug_pub.add( 'AUDIT HISTORY: DID NOT INSERT FOR LINE ID : '||P_LINE_ID , 1 ) ;
1120 oe_debug_pub.add( 'NO DATA FOUND ' , 1 ) ;
1121 END IF;
1122 NULL;
1123 x_return_status := FND_API.G_RET_STS_SUCCESS;
1124 WHEN OTHERS THEN
1125 l_err_text := substr(SQLERRM,1,74);
1126 IF l_debug_level > 0 THEN
1127 oe_debug_pub.add( 'ERROR:'||L_ERR_TEXT , 5 ) ;
1128 oe_debug_pub.add( 'IN OUTER EXCEPTION' , 5 ) ;
1129 oe_debug_pub.add( 'HIST_TYPE_CODE VALUE:' || P_HIST_TYPE_CODE , 5 ) ;
1130 END IF;
1131 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1132 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'RecordLineHist');
1133 END IF;
1134 IF l_debug_level > 0 THEN
1135 oe_debug_pub.add( 'ERROR WHILE INSERTING LINE HISTORY RECORD' ) ;
1136 END IF;
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 END RecordLineHist;
1139
1140 Procedure RecordHeaderHist
1141 (p_header_id In Number
1142 ,p_header_rec In OE_ORDER_PUB.HEADER_REC_TYPE := OE_Order_PUB.G_MISS_HEADER_REC
1143 ,p_hist_type_code In Varchar2
1144 ,p_reason_code In varchar2
1145 ,p_comments IN Varchar2
1146 ,p_audit_flag IN Varchar2 := null
1147 ,p_version_flag IN Varchar2 := null
1148 ,p_phase_change_flag IN Varchar2 := null
1149 ,p_version_number IN NUMBER := null
1150 ,p_reason_id IN NUMBER := NULL
1151 ,p_wf_activity_code IN Varchar2 := null
1152 ,p_wf_result_code IN Varchar2 := null
1153 ,p_changed_attribute IN varchar2 := null
1154 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
1155 ) IS
1156 l_header_rec OE_ORDER_PUB.HEADER_REC_TYPE := OE_Order_PUB.G_MISS_HEADER_REC;
1157 l_credit_card_number varchar2(10);
1158 l_credit_card_code varchar2(10);
1159 l_instrument_id number;
1160 l_credit_card_holder_name varchar2(10);
1161 l_credit_card_expiration_date date;
1162 --
1163 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1164 --
1165 BEGIN
1166
1167 if (p_header_rec.header_id is not null AND
1168 p_header_rec.header_id <> FND_API.G_MISS_NUM) then
1169 l_header_rec := p_header_rec;
1170 else
1171 -- query the header record
1172 l_header_rec := OE_HEADER_UTIL.Query_Row
1173 (p_header_id => p_header_id
1174 );
1175 end if;
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.add( 'RECORDHEADERHIST:INSERT A ROW FOR HEADER ID : ' || TO_CHAR ( P_HEADER_ID ) ) ;
1178 END IF;
1179
1180
1181 -- for credit card orders, only instrument id is stored for credit card number
1182 -- and credit card code, need to set these two values to indicate the column
1183 -- instrument_id stores actual instrument_id, otherwise it stores the
1184 -- card history change id if other card attributes are being changed.
1185 IF l_header_rec.cc_instrument_id IS NOT NULL THEN
1186 -- store the instrument_id in column instruemnt_id
1187 -- if credit_card_number or credit_card_code is updated.
1188 l_instrument_id := l_header_rec.cc_instrument_id;
1189
1190 IF p_changed_attribute IS NOT NULL THEN
1191
1192 IF l_debug_level > 0 THEN
1193 oe_debug_pub.add( 'p_changed_attr is : ' || p_changed_attribute) ;
1194 END IF;
1195
1196 -- store the instrument_id in column instruemnt_id
1197 -- if credit_card_number or credit_card_code is updated.
1198 IF instr(p_changed_attribute, 'CREDIT_CARD_NUMBER') > 0 THEN
1199 l_credit_card_number := '****';
1200 END IF;
1201
1202 IF instr(p_changed_attribute, 'CREDIT_CARD_CODE') > 0 THEN
1203 l_credit_card_code := '****';
1204 END IF;
1205
1206 IF instr(p_changed_attribute, 'CREDIT_CARD_HOLDER_NAME') > 0 THEN
1207 l_credit_card_holder_name := '****';
1208 END IF;
1209
1210 IF instr(p_changed_attribute, 'CREDIT_CARD_EXPIRATION_DATE') > 0 THEN
1211 l_credit_card_expiration_date := sysdate;
1212 END IF;
1213
1214 -- need to store card_history_change_id in column instrument_id
1215 -- if credit_card_expiration_date or credit_card_holder_name is updated
1216
1217 IF l_credit_card_code IS NULL AND l_credit_card_number IS NULL THEN
1218 BEGIN
1219 SELECT max(card_history_change_id)
1220 INTO l_instrument_id
1221 FROM iby_creditcard_h
1222 WHERE instrid = l_header_rec.cc_instrument_id;
1223 EXCEPTION WHEN NO_DATA_FOUND THEN
1224 NULL;
1225 END;
1226
1227 END IF;
1228 ELSIF p_changed_attribute IS NULL THEN
1229 -- none of the credit card attributes has changed.
1230 l_credit_card_number := '****';
1231 l_credit_card_code := '****';
1232 l_credit_card_holder_name := '****';
1233 l_credit_card_expiration_date := sysdate;
1234
1235 END IF;
1236
1237 IF l_debug_level > 0 THEN
1238 oe_debug_pub.add( 'RECORDHEADERHIST:l_instrument_id is : ' || l_instrument_id) ;
1242 oe_debug_pub.add( 'RECORDHEADERHIST:l_credit_card_expiration_date is : ' || l_credit_card_expiration_date) ;
1239 oe_debug_pub.add( 'RECORDHEADERHIST:l_credit_card_code is : ' || l_credit_card_code) ;
1240 oe_debug_pub.add( 'RECORDHEADERHIST:l_credit_card_number is : ' || l_credit_card_number) ;
1241 oe_debug_pub.add( 'RECORDHEADERHIST:l_credit_card_holder_name is : ' || l_credit_card_holder_name) ;
1243 END IF;
1244 END IF;
1245
1246
1247 INSERT INTO OE_ORDER_HEADER_HISTORY
1248 (
1249 HEADER_ID ,
1250 ORG_ID ,
1251 ORDER_TYPE_ID ,
1252 ORDER_NUMBER ,
1253 VERSION_NUMBER ,
1254 EXPIRATION_DATE ,
1255 ORDER_SOURCE_ID ,
1256 SOURCE_DOCUMENT_TYPE_ID ,
1257 ORIG_SYS_DOCUMENT_REF ,
1258 SOURCE_DOCUMENT_ID ,
1259 ORDERED_DATE ,
1260 REQUEST_DATE ,
1261 PRICING_DATE ,
1262 PRICE_REQUEST_CODE , -- PROMOTIONS SEP/01
1263 SHIPMENT_PRIORITY_CODE ,
1264 DEMAND_CLASS_CODE ,
1265 PRICE_LIST_ID ,
1266 TAX_EXEMPT_FLAG ,
1267 TAX_EXEMPT_NUMBER ,
1268 TAX_EXEMPT_REASON_CODE ,
1269 CONVERSION_RATE ,
1270 CONVERSION_TYPE_CODE ,
1271 CONVERSION_RATE_DATE ,
1272 PARTIAL_SHIPMENTS_ALLOWED ,
1273 SHIP_TOLERANCE_ABOVE ,
1274 SHIP_TOLERANCE_BELOW ,
1275 TRANSACTIONAL_CURR_CODE ,
1276 AGREEMENT_ID ,
1277 TAX_POINT_CODE ,
1278 CUST_PO_NUMBER ,
1279 INVOICING_RULE_ID ,
1280 ACCOUNTING_RULE_ID ,
1281 ACCOUNTING_RULE_DURATION ,
1282 PAYMENT_TERM_ID ,
1283 SHIPPING_METHOD_CODE ,
1284 FREIGHT_CARRIER_CODE ,
1285 FOB_POINT_CODE ,
1286 FREIGHT_TERMS_CODE ,
1287 SOLD_FROM_ORG_ID ,
1288 SOLD_TO_ORG_ID ,
1289 SHIP_FROM_ORG_ID ,
1290 SHIP_TO_ORG_ID ,
1291 INVOICE_TO_ORG_ID ,
1292 DELIVER_TO_ORG_ID ,
1293 SOLD_TO_CONTACT_ID ,
1294 SHIP_TO_CONTACT_ID ,
1295 INVOICE_TO_CONTACT_ID ,
1296 DELIVER_TO_CONTACT_ID ,
1297 CREATION_DATE ,
1298 CREATED_BY ,
1299 LAST_UPDATED_BY ,
1300 LAST_UPDATE_DATE ,
1301 LAST_UPDATE_LOGIN ,
1302 PROGRAM_APPLICATION_ID ,
1303 PROGRAM_ID ,
1304 PROGRAM_UPDATE_DATE ,
1305 REQUEST_ID ,
1306 CONTEXT ,
1307 ATTRIBUTE1 ,
1308 ATTRIBUTE2 ,
1309 ATTRIBUTE3 ,
1310 ATTRIBUTE4 ,
1311 ATTRIBUTE5 ,
1312 ATTRIBUTE6 ,
1313 ATTRIBUTE7 ,
1314 ATTRIBUTE8 ,
1315 ATTRIBUTE9 ,
1316 ATTRIBUTE10 ,
1317 ATTRIBUTE11 ,
1318 ATTRIBUTE12 ,
1319 ATTRIBUTE13 ,
1320 ATTRIBUTE14 ,
1321 ATTRIBUTE15 ,
1322 ATTRIBUTE16 , -- for bug 2184255
1323 ATTRIBUTE17 ,
1324 ATTRIBUTE18 ,
1325 ATTRIBUTE19 ,
1326 ATTRIBUTE20 ,
1327 GLOBAL_ATTRIBUTE_CATEGORY ,
1328 GLOBAL_ATTRIBUTE1 ,
1329 GLOBAL_ATTRIBUTE2 ,
1330 GLOBAL_ATTRIBUTE3 ,
1331 GLOBAL_ATTRIBUTE4 ,
1332 GLOBAL_ATTRIBUTE5 ,
1333 GLOBAL_ATTRIBUTE6 ,
1334 GLOBAL_ATTRIBUTE7 ,
1335 GLOBAL_ATTRIBUTE8 ,
1336 GLOBAL_ATTRIBUTE9 ,
1337 GLOBAL_ATTRIBUTE10 ,
1338 GLOBAL_ATTRIBUTE11 ,
1339 GLOBAL_ATTRIBUTE12 ,
1340 GLOBAL_ATTRIBUTE13 ,
1341 GLOBAL_ATTRIBUTE14 ,
1342 GLOBAL_ATTRIBUTE15 ,
1343 GLOBAL_ATTRIBUTE16 ,
1344 GLOBAL_ATTRIBUTE17 ,
1345 GLOBAL_ATTRIBUTE18 ,
1346 GLOBAL_ATTRIBUTE19 ,
1347 GLOBAL_ATTRIBUTE20 ,
1348 CANCELLED_FLAG ,
1349 OPEN_FLAG ,
1350 BOOKED_FLAG ,
1351 SALESREP_ID ,
1352 RETURN_REASON_CODE ,
1353 ORDER_DATE_TYPE_CODE ,
1354 EARLIEST_SCHEDULE_LIMIT ,
1355 LATEST_SCHEDULE_LIMIT ,
1356 PAYMENT_TYPE_CODE ,
1357 PAYMENT_AMOUNT ,
1358 CHECK_NUMBER ,
1359 CREDIT_CARD_NUMBER ,
1360 CREDIT_CARD_CODE ,
1361 CREDIT_CARD_HOLDER_NAME ,
1362 CREDIT_CARD_EXPIRATION_DATE,
1363 /* R12 CC encryption
1364 CREDIT_CARD_CODE ,
1365 CREDIT_CARD_HOLDER_NAME ,
1366 CREDIT_CARD_NUMBER ,
1367 CREDIT_CARD_EXPIRATION_DATE,
1368 CREDIT_CARD_APPROVAL_CODE ,
1369 */
1370 SALES_CHANNEL_CODE ,
1371 FIRST_ACK_CODE ,
1372 FIRST_ACK_DATE ,
1373 LAST_ACK_CODE ,
1374 LAST_ACK_DATE ,
1375 ORDER_CATEGORY_CODE ,
1376 CHANGE_SEQUENCE ,
1377 SHIPPING_INSTRUCTIONS ,
1378 PACKING_INSTRUCTIONS ,
1379 TP_CONTEXT ,
1380 TP_ATTRIBUTE1 ,
1381 TP_ATTRIBUTE2 ,
1385 TP_ATTRIBUTE6 ,
1382 TP_ATTRIBUTE3 ,
1383 TP_ATTRIBUTE4 ,
1384 TP_ATTRIBUTE5 ,
1386 TP_ATTRIBUTE7 ,
1387 TP_ATTRIBUTE8 ,
1388 TP_ATTRIBUTE9 ,
1389 TP_ATTRIBUTE10 ,
1390 TP_ATTRIBUTE11 ,
1391 TP_ATTRIBUTE12 ,
1392 TP_ATTRIBUTE13 ,
1393 TP_ATTRIBUTE14 ,
1394 TP_ATTRIBUTE15 ,
1395 FLOW_STATUS_CODE ,
1396 MARKETING_SOURCE_CODE_ID ,
1397 -- CREDIT_CARD_APPROVAL_DATE ,
1398 UPGRADED_FLAG ,
1399 CUSTOMER_PREFERENCE_SET_CODe,
1400 BOOKED_DATE ,
1401 BLANKET_NUMBER ,
1402 -- QUOTING changes
1403 quote_date,
1404 quote_number,
1405 sales_document_name,
1406 transaction_phase_code,
1407 user_status_code,
1408 draft_submitted_flag,
1409 source_document_version_number,
1410 sold_to_site_use_id,
1411 -- QUOTING changes END
1412 REASON_CODE ,
1413 HIST_COMMENTS ,
1414 HIST_TYPE_CODE ,
1415 HIST_CREATION_DATE ,
1416 HIST_CREATED_BY ,
1417 RESPONSIBILITY_ID ,
1418 --VERSIONING Changes
1419 AUDIT_FLAG ,
1420 VERSION_FLAG,
1421 PHASE_CHANGE_FLAG,
1422 REASON_ID ,
1423 --VERSIONING Changes END
1424 order_firmed_date , -- key transaction dates
1425 instrument_id -- R12 CC Encryption
1426 )
1427 VALUES
1428 (
1429 l_header_rec.header_id ,
1430 l_header_rec.org_id ,
1431 l_header_rec.order_type_id ,
1432 l_header_rec.order_number ,
1433 nvl(p_version_number, l_header_rec.version_number) ,
1434 l_header_rec.expiration_date ,
1435 l_header_rec.order_source_id ,
1436 l_header_rec.source_document_type_id ,
1437 l_header_rec.orig_sys_document_ref ,
1438 l_header_rec.source_document_id ,
1439 l_header_rec.ordered_date ,
1440 l_header_rec.request_date ,
1441 l_header_rec.pricing_date ,
1442 l_header_rec.price_request_code , -- PROMOTIONS SEP/01
1443 l_header_rec.shipment_priority_code ,
1444 l_header_rec.demand_class_code ,
1445 l_header_rec.price_list_id ,
1446 l_header_rec.tax_exempt_flag ,
1447 l_header_rec.tax_exempt_number ,
1448 l_header_rec.tax_exempt_reason_code ,
1449 l_header_rec.conversion_rate ,
1450 l_header_rec.conversion_type_code ,
1451 l_header_rec.conversion_rate_date ,
1452 l_header_rec.partial_shipments_allowed ,
1453 l_header_rec.ship_tolerance_above ,
1454 l_header_rec.ship_tolerance_below ,
1455 l_header_rec.transactional_curr_code ,
1456 l_header_rec.agreement_id ,
1457 l_header_rec.tax_point_code ,
1458 l_header_rec.cust_po_number ,
1459 l_header_rec.invoicing_rule_id ,
1460 l_header_rec.accounting_rule_id ,
1461 l_header_rec.accounting_rule_duration ,
1462 l_header_rec.payment_term_id ,
1463 l_header_rec.shipping_method_code ,
1464 l_header_rec.freight_carrier_code ,
1465 l_header_rec.fob_point_code ,
1466 l_header_rec.freight_terms_code ,
1467 l_header_rec.sold_from_org_id ,
1468 l_header_rec.sold_to_org_id ,
1469 l_header_rec.ship_from_org_id ,
1470 l_header_rec.ship_to_org_id ,
1471 l_header_rec.invoice_to_org_id ,
1472 l_header_rec.deliver_to_org_id ,
1473 l_header_rec.sold_to_contact_id ,
1474 l_header_rec.ship_to_contact_id ,
1475 l_header_rec.invoice_to_contact_id ,
1476 l_header_rec.deliver_to_contact_id ,
1477 l_header_rec.creation_date ,
1478 l_header_rec.created_by ,
1479 l_header_rec.last_updated_by ,
1480 l_header_rec.last_update_date ,
1481 l_header_rec.last_update_login ,
1482 l_header_rec.program_application_id ,
1483 l_header_rec.program_id ,
1484 l_header_rec.program_update_date ,
1485 l_header_rec.request_id ,
1486 l_header_rec.context ,
1487 l_header_rec.attribute1 ,
1488 l_header_rec.attribute2 ,
1489 l_header_rec.attribute3 ,
1490 l_header_rec.attribute4 ,
1491 l_header_rec.attribute5 ,
1492 l_header_rec.attribute6 ,
1493 l_header_rec.attribute7 ,
1494 l_header_rec.attribute8 ,
1495 l_header_rec.attribute9 ,
1496 l_header_rec.attribute10 ,
1497 l_header_rec.attribute11 ,
1498 l_header_rec.attribute12 ,
1499 l_header_rec.attribute13 ,
1500 l_header_rec.attribute14 ,
1501 l_header_rec.attribute15 ,
1502 l_header_rec.attribute16 , -- for bug 2184255
1503 l_header_rec.attribute17 ,
1504 l_header_rec.attribute18 ,
1505 l_header_rec.attribute19 ,
1506 l_header_rec.attribute20 ,
1507 l_header_rec.global_attribute_category ,
1508 l_header_rec.global_attribute1 ,
1509 l_header_rec.global_attribute2 ,
1513 l_header_rec.global_attribute6 ,
1510 l_header_rec.global_attribute3 ,
1511 l_header_rec.global_attribute4 ,
1512 l_header_rec.global_attribute5 ,
1514 l_header_rec.global_attribute7 ,
1515 l_header_rec.global_attribute8 ,
1516 l_header_rec.global_attribute9 ,
1517 l_header_rec.global_attribute10 ,
1518 l_header_rec.global_attribute11 ,
1519 l_header_rec.global_attribute12 ,
1520 l_header_rec.global_attribute13 ,
1521 l_header_rec.global_attribute14 ,
1522 l_header_rec.global_attribute15 ,
1523 l_header_rec.global_attribute16 ,
1524 l_header_rec.global_attribute17 ,
1525 l_header_rec.global_attribute18 ,
1526 l_header_rec.global_attribute19 ,
1527 l_header_rec.global_attribute20 ,
1528 l_header_rec.cancelled_flag ,
1529 l_header_rec.open_flag ,
1530 l_header_rec.booked_flag ,
1531 l_header_rec.salesrep_id ,
1532 l_header_rec.return_reason_code ,
1533 l_header_rec.order_date_type_code ,
1534 l_header_rec.earliest_schedule_limit ,
1535 l_header_rec.latest_schedule_limit ,
1536 l_header_rec.payment_type_code ,
1537 l_header_rec.payment_amount ,
1538 l_header_rec.check_number ,
1539 l_credit_card_number ,
1540 l_credit_card_code ,
1541 l_credit_card_holder_name ,
1542 l_credit_card_expiration_date ,
1543 /*
1544 l_header_rec.credit_card_code ,
1545 l_header_rec.credit_card_holder_name ,
1546 l_header_rec.credit_card_number ,
1547 l_header_rec.credit_card_expiration_date,
1548 l_header_rec.credit_card_approval_code ,
1549 */
1550 l_header_rec.sales_channel_code ,
1551 l_header_rec.first_ack_code ,
1552 l_header_rec.first_ack_date ,
1553 l_header_rec.last_ack_code ,
1554 l_header_rec.last_ack_date ,
1555 l_header_rec.order_category_code ,
1556 l_header_rec.change_sequence ,
1557 l_header_rec.shipping_instructions ,
1558 l_header_rec.packing_instructions ,
1559 l_header_rec.tp_context ,
1560 l_header_rec.tp_attribute1 ,
1561 l_header_rec.tp_attribute2 ,
1562 l_header_rec.tp_attribute3 ,
1563 l_header_rec.tp_attribute4 ,
1564 l_header_rec.tp_attribute5 ,
1565 l_header_rec.tp_attribute6 ,
1566 l_header_rec.tp_attribute7 ,
1567 l_header_rec.tp_attribute8 ,
1568 l_header_rec.tp_attribute9 ,
1569 l_header_rec.tp_attribute10 ,
1570 l_header_rec.tp_attribute11 ,
1571 l_header_rec.tp_attribute12 ,
1572 l_header_rec.tp_attribute13 ,
1573 l_header_rec.tp_attribute14 ,
1574 l_header_rec.tp_attribute15 ,
1575 l_header_rec.flow_status_code ,
1576 l_header_rec.marketing_source_code_id ,
1577 -- l_header_rec.credit_card_approval_date ,
1578 l_header_rec.upgraded_flag ,
1579 l_header_rec.customer_preference_set_code,
1580 l_header_rec.booked_date ,
1581 l_header_rec.blanket_number ,
1582 -- QUOTING changes
1583 l_header_rec.quote_date,
1584 l_header_rec.quote_number,
1585 l_header_rec.sales_document_name,
1586 l_header_rec.transaction_phase_code,
1587 l_header_rec.user_status_code,
1588 l_header_rec.draft_submitted_flag,
1589 l_header_rec.source_document_version_number,
1590 l_header_rec.sold_to_site_use_id,
1591 -- QUOTING changes END
1592 p_reason_code,
1593 p_comments,
1594 P_HIST_TYPE_CODE,
1595 sysdate,
1596 nvl(FND_GLOBAL.USER_ID, -1),
1597 nvl(FND_GLOBAL.RESP_ID, -1),
1598 --VERSIONING Changes
1599 p_AUDIT_FLAG,
1600 p_version_flag,
1601 p_phase_change_flag,
1602 p_reason_id ,
1603 --VERSIONING Changes END
1604 --key transaction dates
1605 l_header_rec.Order_firmed_date,
1606 l_instrument_id
1607
1608 );
1609
1610 x_return_status := FND_API.G_RET_STS_SUCCESS;
1611 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
1612 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
1613 IF l_debug_level > 0 THEN
1614 oe_debug_pub.add( 'EXITING RECORDHEADERHIST' ) ;
1615 END IF;
1616
1617 EXCEPTION
1618 WHEN OTHERS THEN
1619 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1620 FND_MSG_PUB.Add_Exc_Msg
1621 (G_PKG_NAME
1622 ,'RecordHeaderHist');
1623 END IF;
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625 END RecordHeaderHist;
1626
1627 Procedure RecordHSCreditHist
1628 (p_header_scredit_id In Number
1629 ,p_header_scredit_rec In OE_ORDER_PUB.HEADER_SCREDIT_REC_TYPE
1630 := OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC
1631 ,p_hist_type_code In Varchar2
1632 ,p_reason_code In varchar2
1633 ,p_comments IN Varchar2
1634 ,p_audit_flag IN Varchar2 := null
1635 ,p_version_flag IN Varchar2 := null
1636 ,p_phase_change_flag IN Varchar2 := null
1637 ,p_version_number IN NUMBER := null
1638 ,p_reason_id IN NUMBER := NULL
1639 ,p_wf_activity_code IN Varchar2 := null
1643 l_header_scredit_rec OE_ORDER_PUB.HEADER_SCREDIT_REC_TYPE := OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC;
1640 ,p_wf_result_code IN Varchar2 := null
1641 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
1642 ) IS
1644
1645 --
1646 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1647 --
1648 BEGIN
1649
1650 IF l_debug_level > 0 THEN
1651 oe_debug_pub.add( 'ENTERING RECORDHSCREDITHIST' ) ;
1652 END IF;
1653
1654 -- JPN: Added the G_MISS_NUM check before inserting the history record.
1655 if (p_header_scredit_rec.sales_credit_id is not null AND
1656 p_header_scredit_rec.sales_credit_id <> FND_API.G_MISS_NUM) then
1657 l_header_scredit_rec := p_header_scredit_rec;
1658 else
1659 -- query the header record
1660 OE_HEADER_SCREDIT_UTIL.Query_Row
1661 (p_sales_credit_id => p_header_scredit_id,
1662 x_header_scredit_rec => l_header_scredit_rec
1663 );
1664 end if;
1665 IF l_debug_level > 0 THEN
1666 oe_debug_pub.add( 'RECORDHSCREDITHIST:INSERT A ROW FOR P_HEADER_SCREDIT_ID :' || TO_CHAR ( P_HEADER_SCREDIT_ID ) ) ;
1667 END IF;
1668
1669 INSERT INTO OE_SALES_CREDIT_HISTORY
1670 (
1671 SALES_CREDIT_ID ,
1672 CREATION_DATE ,
1673 CREATED_BY ,
1674 LAST_UPDATE_DATE ,
1675 LAST_UPDATED_BY ,
1676 LAST_UPDATE_LOGIN ,
1677 HEADER_ID ,
1678 SALESREP_ID ,
1679 PERCENT ,
1680 LINE_ID ,
1681 CONTEXT ,
1682 ATTRIBUTE1 ,
1683 ATTRIBUTE2 ,
1684 ATTRIBUTE3 ,
1685 ATTRIBUTE4 ,
1686 ATTRIBUTE5 ,
1687 ATTRIBUTE6 ,
1688 ATTRIBUTE7 ,
1689 ATTRIBUTE8 ,
1690 ATTRIBUTE9 ,
1691 ATTRIBUTE10 ,
1692 ATTRIBUTE11 ,
1693 ATTRIBUTE12 ,
1694 ATTRIBUTE13 ,
1695 ATTRIBUTE14 ,
1696 ATTRIBUTE15 ,
1697 DW_UPDATE_ADVICE_FLAG ,
1698 WH_UPDATE_DATE ,
1699 ORIG_SYS_CREDIT_REF ,
1700 SALES_CREDIT_TYPE_ID ,
1701 REASON_CODE ,
1702 HIST_COMMENTS ,
1703 HIST_TYPE_CODE ,
1704 HIST_CREATION_DATE ,
1705 HIST_CREATED_BY ,
1706 RESPONSIBILITY_ID,
1707 --VERSIONING Changes
1708 AUDIT_FLAG,
1709 VERSION_FLAG,
1710 PHASE_CHANGE_FLAG,
1711 VERSION_NUMBER,
1712 REASON_ID
1713 --VERSIONING Changes END
1714 )
1715 VALUES
1716 (
1717 l_header_scredit_rec.SALES_CREDIT_ID ,
1718 l_header_scredit_rec.CREATION_DATE ,
1719 l_header_scredit_rec.CREATED_BY ,
1720 l_header_scredit_rec.LAST_UPDATE_DATE ,
1721 l_header_scredit_rec.LAST_UPDATED_BY ,
1722 l_header_scredit_rec.LAST_UPDATE_LOGIN ,
1723 l_header_scredit_rec.HEADER_ID ,
1724 l_header_scredit_rec.SALESREP_ID ,
1725 l_header_scredit_rec.PERCENT ,
1726 null,
1727 l_header_scredit_rec.CONTEXT ,
1728 l_header_scredit_rec.ATTRIBUTE1 ,
1729 l_header_scredit_rec.ATTRIBUTE2 ,
1730 l_header_scredit_rec.ATTRIBUTE3 ,
1731 l_header_scredit_rec.ATTRIBUTE4 ,
1732 l_header_scredit_rec.ATTRIBUTE5 ,
1733 l_header_scredit_rec.ATTRIBUTE6 ,
1734 l_header_scredit_rec.ATTRIBUTE7 ,
1735 l_header_scredit_rec.ATTRIBUTE8 ,
1736 l_header_scredit_rec.ATTRIBUTE9 ,
1737 l_header_scredit_rec.ATTRIBUTE10 ,
1738 l_header_scredit_rec.ATTRIBUTE11 ,
1739 l_header_scredit_rec.ATTRIBUTE12 ,
1740 l_header_scredit_rec.ATTRIBUTE13 ,
1741 l_header_scredit_rec.ATTRIBUTE14 ,
1742 l_header_scredit_rec.ATTRIBUTE15 ,
1743 l_header_scredit_rec.DW_UPDATE_ADVICE_FLAG ,
1744 l_header_scredit_rec.WH_UPDATE_DATE ,
1745 l_header_scredit_rec.ORIG_SYS_CREDIT_REF ,
1746 l_header_scredit_rec.SALES_CREDIT_TYPE_ID ,
1747 p_reason_code,
1748 p_comments,
1749 P_HIST_TYPE_CODE,
1750 sysdate,
1751 nvl(FND_GLOBAL.USER_ID, -1),
1752 nvl(FND_GLOBAL.RESP_ID, -1),
1753 --VERSIONING Changes
1754 p_AUDIT_FLAG,
1755 p_version_flag,
1756 p_phase_change_flag,
1757 p_version_number,
1758 p_reason_id
1759 --VERSIONING Changes END
1760 );
1761 x_return_status := FND_API.G_RET_STS_SUCCESS;
1762 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
1763 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
1764 IF l_debug_level > 0 THEN
1765 oe_debug_pub.add( 'EXITING RECORDHSCREDITHIST' ) ;
1766 END IF;
1767
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1771 FND_MSG_PUB.Add_Exc_Msg
1772 (G_PKG_NAME
1773 ,'RecordHSCreditHist');
1774 END IF;
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 END RecordHSCreditHist;
1777
1778 Procedure RecordLSCreditHist
1782 ,p_hist_type_code In Varchar2
1779 (p_line_scredit_id In Number
1780 ,p_line_scredit_rec In OE_ORDER_PUB.LINE_SCREDIT_REC_TYPE
1781 := OE_Order_PUB.G_MISS_LINE_SCREDIT_REC
1783 ,p_reason_code In varchar2
1784 ,p_comments IN Varchar2
1785 ,p_audit_flag IN Varchar2 := null
1786 ,p_version_flag IN Varchar2 := null
1787 ,p_phase_change_flag IN Varchar2 := null
1788 ,p_version_number IN NUMBER := null
1789 ,p_reason_id IN NUMBER := NULL
1790 ,p_wf_activity_code IN Varchar2 := null
1791 ,p_wf_result_code IN Varchar2 := null
1792 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
1793 ) IS
1794 l_line_scredit_rec OE_ORDER_PUB.LINE_SCREDIT_REC_TYPE := OE_Order_PUB.G_MISS_LINE_SCREDIT_REC;
1795 --
1796 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1797 --
1798 BEGIN
1799 IF l_debug_level > 0 THEN
1800 oe_debug_pub.add( 'ENTERING RECORDLSCREDITHIST' ) ;
1801 END IF;
1802
1803 -- JPN: Added the G_MISS_NUM check before inserting the history record.
1804 if (p_line_scredit_rec.sales_credit_id is not null AND
1805 p_line_scredit_rec.sales_credit_id <> FND_API.G_MISS_NUM) then
1806 l_line_scredit_rec := p_line_scredit_rec;
1807 else
1808 -- query the header record
1809 OE_LINE_SCREDIT_UTIL.Query_Row
1810 (p_sales_credit_id => p_line_scredit_id,
1811 x_line_scredit_rec => l_line_scredit_rec
1812 );
1813 end if;
1814 IF l_debug_level > 0 THEN
1815 oe_debug_pub.add( 'RECORDLSCREDITHIST:INSERT A ROW FOR P_LINE_SCREDIT_ID :' || TO_CHAR ( P_LINE_SCREDIT_ID ) ) ;
1816 END IF;
1817
1818
1819 INSERT INTO OE_SALES_CREDIT_HISTORY
1820 (
1821 SALES_CREDIT_ID ,
1822 CREATION_DATE ,
1823 CREATED_BY ,
1824 LAST_UPDATE_DATE ,
1825 LAST_UPDATED_BY ,
1826 LAST_UPDATE_LOGIN ,
1827 HEADER_ID ,
1828 SALESREP_ID ,
1829 PERCENT ,
1830 LINE_ID ,
1831 CONTEXT ,
1832 ATTRIBUTE1 ,
1833 ATTRIBUTE2 ,
1834 ATTRIBUTE3 ,
1835 ATTRIBUTE4 ,
1836 ATTRIBUTE5 ,
1837 ATTRIBUTE6 ,
1838 ATTRIBUTE7 ,
1839 ATTRIBUTE8 ,
1840 ATTRIBUTE9 ,
1841 ATTRIBUTE10 ,
1842 ATTRIBUTE11 ,
1843 ATTRIBUTE12 ,
1844 ATTRIBUTE13 ,
1845 ATTRIBUTE14 ,
1846 ATTRIBUTE15 ,
1847 DW_UPDATE_ADVICE_FLAG ,
1848 WH_UPDATE_DATE ,
1849 ORIG_SYS_CREDIT_REF ,
1850 SALES_CREDIT_TYPE_ID ,
1851 REASON_CODE ,
1852 HIST_COMMENTS ,
1853 HIST_TYPE_CODE ,
1854 HIST_CREATION_DATE ,
1855 HIST_CREATED_BY ,
1856 RESPONSIBILITY_ID ,
1857 --VERSIONING Changes
1858 AUDIT_FLAG ,
1859 VERSION_FLAG ,
1860 PHASE_CHANGE_FLAG ,
1861 VERSION_NUMBER,
1862 REASON_ID
1863 --VERSIONING Changes END
1864 )
1865 VALUES
1866 (
1867 l_line_scredit_rec.SALES_CREDIT_ID ,
1868 l_line_scredit_rec.CREATION_DATE ,
1869 l_line_scredit_rec.CREATED_BY ,
1870 l_line_scredit_rec.LAST_UPDATE_DATE ,
1871 l_line_scredit_rec.LAST_UPDATED_BY ,
1872 l_line_scredit_rec.LAST_UPDATE_LOGIN ,
1873 l_line_scredit_rec.HEADER_ID ,
1874 l_line_scredit_rec.SALESREP_ID ,
1875 l_line_scredit_rec.PERCENT ,
1876 l_line_scredit_rec.LINE_ID ,
1877 l_line_scredit_rec.CONTEXT ,
1878 l_line_scredit_rec.ATTRIBUTE1 ,
1879 l_line_scredit_rec.ATTRIBUTE2 ,
1880 l_line_scredit_rec.ATTRIBUTE3 ,
1881 l_line_scredit_rec.ATTRIBUTE4 ,
1882 l_line_scredit_rec.ATTRIBUTE5 ,
1883 l_line_scredit_rec.ATTRIBUTE6 ,
1884 l_line_scredit_rec.ATTRIBUTE7 ,
1885 l_line_scredit_rec.ATTRIBUTE8 ,
1886 l_line_scredit_rec.ATTRIBUTE9 ,
1887 l_line_scredit_rec.ATTRIBUTE10 ,
1888 l_line_scredit_rec.ATTRIBUTE11 ,
1889 l_line_scredit_rec.ATTRIBUTE12 ,
1890 l_line_scredit_rec.ATTRIBUTE13 ,
1891 l_line_scredit_rec.ATTRIBUTE14 ,
1892 l_line_scredit_rec.ATTRIBUTE15 ,
1893 l_line_scredit_rec.DW_UPDATE_ADVICE_FLAG ,
1894 l_line_scredit_rec.WH_UPDATE_DATE ,
1895 l_line_scredit_rec.ORIG_SYS_CREDIT_REF ,
1896 l_line_scredit_rec.SALES_CREDIT_TYPE_ID ,
1897 p_reason_code,
1898 p_comments,
1899 P_HIST_TYPE_CODE ,
1900 sysdate ,
1901 nvl(FND_GLOBAL.USER_ID, -1) ,
1902 nvl(FND_GLOBAL.RESP_ID, -1),
1903 --VERSIONING Changes
1904 p_AUDIT_FLAG,
1905 p_version_flag,
1906 p_phase_change_flag,
1907 p_version_number,
1908 p_reason_id
1909 --VERSIONING Changes END
1910 );
1911
1915 IF l_debug_level > 0 THEN
1912 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
1913 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
1914 x_return_status := FND_API.G_RET_STS_SUCCESS;
1916 oe_debug_pub.add( 'EXITING RECORDLSCREDITHIST' ) ;
1917 END IF;
1918
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1922 FND_MSG_PUB.Add_Exc_Msg
1923 (G_PKG_NAME
1924 ,'RecordLSCreditHist');
1925 END IF;
1926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1927 END RecordLSCreditHist;
1928
1929 Procedure RecordHPAdjHist
1930 (p_header_adj_id In Number
1931 ,p_header_adj_rec In OE_ORDER_PUB.HEADER_ADJ_REC_TYPE
1932 := OE_Order_PUB.G_MISS_HEADER_ADJ_REC
1933 ,p_hist_type_code In Varchar2
1934 ,p_reason_code In varchar2
1935 ,p_comments IN Varchar2
1936 ,p_audit_flag IN Varchar2 := null
1937 ,p_version_flag IN Varchar2 := null
1938 ,p_phase_change_flag IN Varchar2 := null
1939 ,p_version_number IN NUMBER := null
1940 ,p_reason_id IN NUMBER := NULL
1941 ,p_wf_activity_code IN Varchar2 := null
1942 ,p_wf_result_code IN Varchar2 := null
1943 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
1944 ) IS
1945 l_header_adj_rec OE_ORDER_PUB.HEADER_ADJ_REC_TYPE := OE_Order_PUB.G_MISS_HEADER_ADJ_REC;
1946 --
1947 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1948 --
1949 BEGIN
1950
1951 IF l_debug_level > 0 THEN
1952 oe_debug_pub.add( 'ENTERING RECORDHPADJHIST' ) ;
1953 END IF;
1954
1955 if (p_header_adj_rec.price_adjustment_id is not null AND
1956 p_header_adj_rec.price_adjustment_id <> FND_API.G_MISS_NUM) then
1957 l_header_adj_rec := p_header_adj_rec;
1958 else
1959 -- query the header record
1960 OE_HEADER_ADJ_UTIL.Query_Row
1961 (p_price_adjustment_id => p_header_adj_id,
1962 x_header_adj_rec => l_header_adj_rec
1963 );
1964 end if;
1965 IF l_debug_level > 0 THEN
1966 oe_debug_pub.add( 'RECORDHPADJHIST:INSERT A ROW FOR P_HEADER_ADJ_ID :' || TO_CHAR ( P_HEADER_ADJ_ID ) ) ;
1967 END IF;
1968
1969 INSERT INTO OE_PRICE_ADJS_HISTORY
1970 (
1971 PRICE_ADJUSTMENT_ID ,
1972 CREATION_DATE ,
1973 CREATED_BY ,
1974 LAST_UPDATE_DATE ,
1975 LAST_UPDATED_BY ,
1976 LAST_UPDATE_LOGIN ,
1977 PROGRAM_APPLICATION_ID ,
1978 PROGRAM_ID ,
1979 PROGRAM_UPDATE_DATE ,
1980 REQUEST_ID ,
1981 HEADER_ID ,
1982 DISCOUNT_ID ,
1983 DISCOUNT_LINE_ID ,
1984 AUTOMATIC_FLAG ,
1985 PERCENT ,
1986 LINE_ID ,
1987 CONTEXT ,
1988 ATTRIBUTE1 ,
1989 ATTRIBUTE2 ,
1990 ATTRIBUTE3 ,
1991 ATTRIBUTE4 ,
1992 ATTRIBUTE5 ,
1993 ATTRIBUTE6 ,
1994 ATTRIBUTE7 ,
1995 ATTRIBUTE8 ,
1996 ATTRIBUTE9 ,
1997 ATTRIBUTE10 ,
1998 ATTRIBUTE11 ,
1999 ATTRIBUTE12 ,
2000 ATTRIBUTE13 ,
2001 ATTRIBUTE14 ,
2002 ATTRIBUTE15 ,
2003 ORIG_SYS_DISCOUNT_REF ,
2004 LIST_HEADER_ID ,
2005 LIST_LINE_ID ,
2006 LIST_LINE_TYPE_CODE ,
2007 MODIFIED_FROM ,
2008 MODIFIED_TO ,
2009 UPDATE_ALLOWED ,
2010 CHANGE_REASON_CODE ,
2011 CHANGE_REASON_TEXT ,
2012 MODIFIER_MECHANISM_TYPE_CODE,
2013 UPDATED_FLAG ,
2014 APPLIED_FLAG ,
2015 OPERAND ,
2016 ARITHMETIC_OPERATOR ,
2017 COST_ID ,
2018 TAX_CODE ,
2019 TAX_EXEMPT_FLAG ,
2020 TAX_EXEMPT_NUMBER ,
2021 TAX_EXEMPT_REASON_CODE ,
2022 PARENT_ADJUSTMENT_ID ,
2023 INVOICED_FLAG ,
2024 ESTIMATED_FLAG ,
2025 INC_IN_SALES_PERFORMANCE ,
2026 SPLIT_ACTION_CODE ,
2027 ADJUSTED_AMOUNT ,
2028 PRICING_PHASE_ID ,
2029 CHARGE_TYPE_CODE ,
2030 CHARGE_SUBTYPE_CODE ,
2031 RANGE_BREAK_QUANTITY ,
2032 ACCRUAL_CONVERSION_RATE ,
2033 PRICING_GROUP_SEQUENCE ,
2034 ACCRUAL_FLAG ,
2035 LIST_LINE_NO ,
2036 SOURCE_SYSTEM_CODE ,
2037 BENEFIT_QTY ,
2038 BENEFIT_UOM_CODE ,
2039 PRINT_ON_INVOICE_FLAG ,
2040 EXPIRATION_DATE ,
2041 REBATE_TRANSACTION_TYPE_CODE,
2042 REBATE_TRANSACTION_REFERENCE,
2043 REBATE_PAYMENT_SYSTEM_CODE ,
2044 REDEEMED_DATE ,
2045 REDEEMED_FLAG ,
2046 MODIFIER_LEVEL_CODE ,
2047 PRICE_BREAK_TYPE_CODE ,
2048 SUBSTITUTION_ATTRIBUTE ,
2049 PRORATION_TYPE_CODE ,
2050 INCLUDE_ON_RETURNS_FLAG ,
2051 CREDIT_OR_CHARGE_FLAG ,
2055 AC_ATTRIBUTE3 ,
2052 AC_CONTEXT ,
2053 AC_ATTRIBUTE1 ,
2054 AC_ATTRIBUTE2 ,
2056 AC_ATTRIBUTE4 ,
2057 AC_ATTRIBUTE5 ,
2058 AC_ATTRIBUTE6 ,
2059 AC_ATTRIBUTE7 ,
2060 AC_ATTRIBUTE8 ,
2061 AC_ATTRIBUTE9 ,
2062 AC_ATTRIBUTE10 ,
2063 AC_ATTRIBUTE11 ,
2064 AC_ATTRIBUTE12 ,
2065 AC_ATTRIBUTE13 ,
2066 AC_ATTRIBUTE14 ,
2067 AC_ATTRIBUTE15 ,
2068 HIST_TYPE_CODE ,
2069 HIST_CREATION_DATE ,
2070 HIST_CREATED_BY ,
2071 RESPONSIBILITY_ID ,
2072 --VERSIONING Changes
2073 AUDIT_FLAG ,
2074 VERSION_FLAG ,
2075 PHASE_CHANGE_FLAG ,
2076 VERSION_NUMBER ,
2077 REASON_ID
2078 --VERSIONING Changes END
2079 )
2080 VALUES
2081 (
2082 l_header_adj_rec.PRICE_ADJUSTMENT_ID ,
2083 l_header_adj_rec.CREATION_DATE ,
2084 l_header_adj_rec.CREATED_BY ,
2085 l_header_adj_rec.LAST_UPDATE_DATE ,
2086 l_header_adj_rec.LAST_UPDATED_BY ,
2087 l_header_adj_rec.LAST_UPDATE_LOGIN ,
2088 l_header_adj_rec.PROGRAM_APPLICATION_ID ,
2089 l_header_adj_rec.PROGRAM_ID ,
2090 l_header_adj_rec.PROGRAM_UPDATE_DATE ,
2091 l_header_adj_rec.REQUEST_ID ,
2092 l_header_adj_rec.HEADER_ID ,
2093 l_header_adj_rec.DISCOUNT_ID ,
2094 l_header_adj_rec.DISCOUNT_LINE_ID ,
2095 l_header_adj_rec.AUTOMATIC_FLAG ,
2096 l_header_adj_rec.PERCENT ,
2097 null , --l_header_adj_rec.LINE_ID
2098 l_header_adj_rec.CONTEXT ,
2099 l_header_adj_rec.ATTRIBUTE1 ,
2100 l_header_adj_rec.ATTRIBUTE2 ,
2101 l_header_adj_rec.ATTRIBUTE3 ,
2102 l_header_adj_rec.ATTRIBUTE4 ,
2103 l_header_adj_rec.ATTRIBUTE5 ,
2104 l_header_adj_rec.ATTRIBUTE6 ,
2105 l_header_adj_rec.ATTRIBUTE7 ,
2106 l_header_adj_rec.ATTRIBUTE8 ,
2107 l_header_adj_rec.ATTRIBUTE9 ,
2108 l_header_adj_rec.ATTRIBUTE10 ,
2109 l_header_adj_rec.ATTRIBUTE11 ,
2110 l_header_adj_rec.ATTRIBUTE12 ,
2111 l_header_adj_rec.ATTRIBUTE13 ,
2112 l_header_adj_rec.ATTRIBUTE14 ,
2113 l_header_adj_rec.ATTRIBUTE15 ,
2114 l_header_adj_rec.ORIG_SYS_DISCOUNT_REF ,
2115 l_header_adj_rec.LIST_HEADER_ID ,
2116 l_header_adj_rec.LIST_LINE_ID ,
2117 l_header_adj_rec.LIST_LINE_TYPE_CODE ,
2118 l_header_adj_rec.MODIFIED_FROM ,
2119 l_header_adj_rec.MODIFIED_TO ,
2120 l_header_adj_rec.UPDATE_ALLOWED ,
2121 p_reason_code ,
2122 p_comments ,
2123 l_header_adj_rec.MODIFIER_MECHANISM_TYPE_CODE,
2124 l_header_adj_rec.UPDATED_FLAG ,
2125 l_header_adj_rec.APPLIED_FLAG ,
2126 l_header_adj_rec.OPERAND ,
2127 l_header_adj_rec.ARITHMETIC_OPERATOR ,
2128 l_header_adj_rec.COST_ID ,
2129 l_header_adj_rec.TAX_CODE ,
2130 l_header_adj_rec.TAX_EXEMPT_FLAG ,
2131 l_header_adj_rec.TAX_EXEMPT_NUMBER ,
2132 l_header_adj_rec.TAX_EXEMPT_REASON_CODE ,
2133 l_header_adj_rec.PARENT_ADJUSTMENT_ID ,
2134 l_header_adj_rec.INVOICED_FLAG ,
2135 l_header_adj_rec.ESTIMATED_FLAG ,
2136 l_header_adj_rec.INC_IN_SALES_PERFORMANCE ,
2137 l_header_adj_rec.SPLIT_ACTION_CODE ,
2138 l_header_adj_rec.ADJUSTED_AMOUNT ,
2139 l_header_adj_rec.PRICING_PHASE_ID ,
2140 l_header_adj_rec.CHARGE_TYPE_CODE ,
2141 l_header_adj_rec.CHARGE_SUBTYPE_CODE ,
2142 l_header_adj_rec.RANGE_BREAK_QUANTITY ,
2143 l_header_adj_rec.ACCRUAL_CONVERSION_RATE ,
2144 l_header_adj_rec.PRICING_GROUP_SEQUENCE ,
2145 l_header_adj_rec.ACCRUAL_FLAG ,
2146 l_header_adj_rec.LIST_LINE_NO ,
2147 l_header_adj_rec.SOURCE_SYSTEM_CODE ,
2148 l_header_adj_rec.BENEFIT_QTY ,
2149 l_header_adj_rec.BENEFIT_UOM_CODE ,
2150 l_header_adj_rec.PRINT_ON_INVOICE_FLAG ,
2151 l_header_adj_rec.EXPIRATION_DATE ,
2152 l_header_adj_rec.REBATE_TRANSACTION_TYPE_CODE,
2153 l_header_adj_rec.REBATE_TRANSACTION_REFERENCE,
2154 l_header_adj_rec.REBATE_PAYMENT_SYSTEM_CODE ,
2155 l_header_adj_rec.REDEEMED_DATE ,
2156 l_header_adj_rec.REDEEMED_FLAG ,
2157 l_header_adj_rec.MODIFIER_LEVEL_CODE ,
2158 l_header_adj_rec.PRICE_BREAK_TYPE_CODE ,
2159 l_header_adj_rec.SUBSTITUTION_ATTRIBUTE ,
2160 l_header_adj_rec.PRORATION_TYPE_CODE ,
2161 l_header_adj_rec.INCLUDE_ON_RETURNS_FLAG ,
2162 l_header_adj_rec.CREDIT_OR_CHARGE_FLAG ,
2163 l_header_adj_rec.AC_CONTEXT ,
2164 l_header_adj_rec.AC_ATTRIBUTE1 ,
2165 l_header_adj_rec.AC_ATTRIBUTE2 ,
2166 l_header_adj_rec.AC_ATTRIBUTE3 ,
2167 l_header_adj_rec.AC_ATTRIBUTE4 ,
2171 l_header_adj_rec.AC_ATTRIBUTE8 ,
2168 l_header_adj_rec.AC_ATTRIBUTE5 ,
2169 l_header_adj_rec.AC_ATTRIBUTE6 ,
2170 l_header_adj_rec.AC_ATTRIBUTE7 ,
2172 l_header_adj_rec.AC_ATTRIBUTE9 ,
2173 l_header_adj_rec.AC_ATTRIBUTE10 ,
2174 l_header_adj_rec.AC_ATTRIBUTE11 ,
2175 l_header_adj_rec.AC_ATTRIBUTE12 ,
2176 l_header_adj_rec.AC_ATTRIBUTE13 ,
2177 l_header_adj_rec.AC_ATTRIBUTE14 ,
2178 l_header_adj_rec.AC_ATTRIBUTE15 ,
2179 P_HIST_TYPE_CODE,
2180 sysdate,
2181 nvl(FND_GLOBAL.USER_ID, -1) ,
2182 nvl(FND_GLOBAL.RESP_ID, -1) ,
2183 --VERSIONING Changes
2184 p_AUDIT_FLAG,
2185 p_version_flag,
2186 p_phase_change_flag,
2187 p_version_number,
2188 p_reason_id
2189 --VERSIONING Changes END
2190 );
2191
2192 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
2193 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
2194 x_return_status := FND_API.G_RET_STS_SUCCESS;
2195
2196 IF l_debug_level > 0 THEN
2197 oe_debug_pub.add( 'EXITING RECORD HEADER ADJUSTMENTS HISTORY ' , 5 ) ;
2198 END IF;
2199
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2203 FND_MSG_PUB.Add_Exc_Msg
2204 (G_PKG_NAME
2205 ,'RecordHPAdjHist');
2206 END IF;
2207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2208 END RecordHPAdjHist;
2209
2210 Procedure RecordLPAdjHist
2211 (p_line_adj_id In Number
2212 ,p_line_adj_rec In OE_ORDER_PUB.LINE_ADJ_REC_TYPE
2213 := OE_Order_PUB.G_MISS_LINE_ADJ_REC
2214 ,p_hist_type_code In Varchar2
2215 ,p_reason_code In varchar2
2216 ,p_comments IN Varchar2
2217 ,p_audit_flag IN Varchar2 := null
2218 ,p_version_flag IN Varchar2 := null
2219 ,p_phase_change_flag IN Varchar2 := null
2220 ,p_version_number IN NUMBER := null
2221 ,p_reason_id IN NUMBER := NULL
2222 ,p_wf_activity_code IN Varchar2 := null
2223 ,p_wf_result_code IN Varchar2 := null
2224 ,x_return_status Out NOCOPY /* file.sql.39 change */ Varchar2
2225 ) IS
2226 l_line_adj_rec OE_ORDER_PUB.LINE_ADJ_REC_TYPE := OE_Order_PUB.G_MISS_LINE_ADJ_REC;
2227 --
2228 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2229 --
2230 BEGIN
2231
2232 IF l_debug_level > 0 THEN
2233 oe_debug_pub.add( 'ENTERING RECORDLPADJHIST' ) ;
2234 END IF;
2235
2236 -- JPN: Added the G_MISS_NUM check before inserting the history record.
2237 if (p_line_adj_rec.price_adjustment_id is not null AND
2238 p_line_adj_rec.price_adjustment_id <> FND_API.G_MISS_NUM) then
2239 l_line_adj_rec := p_line_adj_rec;
2240 else
2241 -- query the header record
2242 OE_LINE_ADJ_UTIL.Query_Row
2243 (p_price_adjustment_id => p_line_adj_id,
2244 x_line_adj_rec => l_line_adj_rec);
2245 end if;
2246
2247 IF l_debug_level > 0 THEN
2248 oe_debug_pub.add( 'RECORDLPADJHIST:INSERT A ROW FOR P_LINE_ADJ_ID :' || TO_CHAR ( P_LINE_ADJ_ID ) ) ;
2249 END IF;
2250
2251 INSERT INTO OE_PRICE_ADJS_HISTORY
2252 (
2253 PRICE_ADJUSTMENT_ID ,
2254 CREATION_DATE ,
2255 CREATED_BY ,
2256 LAST_UPDATE_DATE ,
2257 LAST_UPDATED_BY ,
2258 LAST_UPDATE_LOGIN ,
2259 PROGRAM_APPLICATION_ID ,
2260 PROGRAM_ID ,
2261 PROGRAM_UPDATE_DATE ,
2262 REQUEST_ID ,
2263 HEADER_ID ,
2264 DISCOUNT_ID ,
2265 DISCOUNT_LINE_ID ,
2266 AUTOMATIC_FLAG ,
2267 PERCENT ,
2268 LINE_ID ,
2269 CONTEXT ,
2270 ATTRIBUTE1 ,
2271 ATTRIBUTE2 ,
2272 ATTRIBUTE3 ,
2273 ATTRIBUTE4 ,
2274 ATTRIBUTE5 ,
2275 ATTRIBUTE6 ,
2276 ATTRIBUTE7 ,
2277 ATTRIBUTE8 ,
2278 ATTRIBUTE9 ,
2279 ATTRIBUTE10 ,
2280 ATTRIBUTE11 ,
2281 ATTRIBUTE12 ,
2282 ATTRIBUTE13 ,
2283 ATTRIBUTE14 ,
2284 ATTRIBUTE15 ,
2285 ORIG_SYS_DISCOUNT_REF ,
2286 LIST_HEADER_ID ,
2287 LIST_LINE_ID ,
2288 LIST_LINE_TYPE_CODE ,
2289 MODIFIED_FROM ,
2290 MODIFIED_TO ,
2291 UPDATE_ALLOWED ,
2292 CHANGE_REASON_CODE ,
2293 CHANGE_REASON_TEXT ,
2294 MODIFIER_MECHANISM_TYPE_CODE,
2295 UPDATED_FLAG ,
2296 APPLIED_FLAG ,
2297 OPERAND ,
2298 ARITHMETIC_OPERATOR ,
2299 COST_ID ,
2300 TAX_CODE ,
2301 TAX_EXEMPT_FLAG ,
2302 TAX_EXEMPT_NUMBER ,
2303 TAX_EXEMPT_REASON_CODE ,
2304 PARENT_ADJUSTMENT_ID ,
2305 INVOICED_FLAG ,
2306 ESTIMATED_FLAG ,
2310 PRICING_PHASE_ID ,
2307 INC_IN_SALES_PERFORMANCE ,
2308 SPLIT_ACTION_CODE ,
2309 ADJUSTED_AMOUNT ,
2311 CHARGE_TYPE_CODE ,
2312 CHARGE_SUBTYPE_CODE ,
2313 RANGE_BREAK_QUANTITY ,
2314 ACCRUAL_CONVERSION_RATE ,
2315 PRICING_GROUP_SEQUENCE ,
2316 ACCRUAL_FLAG ,
2317 LIST_LINE_NO ,
2318 SOURCE_SYSTEM_CODE ,
2319 BENEFIT_QTY ,
2320 BENEFIT_UOM_CODE ,
2321 PRINT_ON_INVOICE_FLAG ,
2322 EXPIRATION_DATE ,
2323 REBATE_TRANSACTION_TYPE_CODE,
2324 REBATE_TRANSACTION_REFERENCE,
2325 REBATE_PAYMENT_SYSTEM_CODE ,
2326 REDEEMED_DATE ,
2327 REDEEMED_FLAG ,
2328 MODIFIER_LEVEL_CODE ,
2329 PRICE_BREAK_TYPE_CODE ,
2330 SUBSTITUTION_ATTRIBUTE ,
2331 PRORATION_TYPE_CODE ,
2332 INCLUDE_ON_RETURNS_FLAG ,
2333 CREDIT_OR_CHARGE_FLAG ,
2334 AC_CONTEXT ,
2335 AC_ATTRIBUTE1 ,
2336 AC_ATTRIBUTE2 ,
2337 AC_ATTRIBUTE3 ,
2338 AC_ATTRIBUTE4 ,
2339 AC_ATTRIBUTE5 ,
2340 AC_ATTRIBUTE6 ,
2341 AC_ATTRIBUTE7 ,
2342 AC_ATTRIBUTE8 ,
2343 AC_ATTRIBUTE9 ,
2344 AC_ATTRIBUTE10 ,
2345 AC_ATTRIBUTE11 ,
2346 AC_ATTRIBUTE12 ,
2347 AC_ATTRIBUTE13 ,
2348 AC_ATTRIBUTE14 ,
2349 AC_ATTRIBUTE15 ,
2350 HIST_TYPE_CODE ,
2351 HIST_CREATION_DATE ,
2352 HIST_CREATED_BY ,
2353 RESPONSIBILITY_ID ,
2354 --VERSIONING Changes
2355 AUDIT_FLAG ,
2356 VERSION_FLAG ,
2357 PHASE_CHANGE_FLAG ,
2358 VERSION_NUMBER ,
2359 REASON_ID ,
2360 --VERSIONING Changes END
2361 -- eBTax Changes
2362 TAX_RATE_ID
2363 )
2364 VALUES
2365 (
2366 l_line_adj_rec.PRICE_ADJUSTMENT_ID ,
2367 l_line_adj_rec.CREATION_DATE ,
2368 l_line_adj_rec.CREATED_BY ,
2369 l_line_adj_rec.LAST_UPDATE_DATE ,
2370 l_line_adj_rec.LAST_UPDATED_BY ,
2371 l_line_adj_rec.LAST_UPDATE_LOGIN ,
2372 l_line_adj_rec.PROGRAM_APPLICATION_ID ,
2373 l_line_adj_rec.PROGRAM_ID ,
2374 l_line_adj_rec.PROGRAM_UPDATE_DATE ,
2375 l_line_adj_rec.REQUEST_ID ,
2376 l_line_adj_rec.HEADER_ID ,
2377 l_line_adj_rec.DISCOUNT_ID ,
2378 l_line_adj_rec.DISCOUNT_LINE_ID ,
2379 l_line_adj_rec.AUTOMATIC_FLAG ,
2380 l_line_adj_rec.PERCENT ,
2381 l_line_adj_rec.LINE_ID ,
2382 l_line_adj_rec.CONTEXT ,
2383 l_line_adj_rec.ATTRIBUTE1 ,
2384 l_line_adj_rec.ATTRIBUTE2 ,
2385 l_line_adj_rec.ATTRIBUTE3 ,
2386 l_line_adj_rec.ATTRIBUTE4 ,
2387 l_line_adj_rec.ATTRIBUTE5 ,
2388 l_line_adj_rec.ATTRIBUTE6 ,
2389 l_line_adj_rec.ATTRIBUTE7 ,
2390 l_line_adj_rec.ATTRIBUTE8 ,
2391 l_line_adj_rec.ATTRIBUTE9 ,
2392 l_line_adj_rec.ATTRIBUTE10 ,
2393 l_line_adj_rec.ATTRIBUTE11 ,
2394 l_line_adj_rec.ATTRIBUTE12 ,
2395 l_line_adj_rec.ATTRIBUTE13 ,
2396 l_line_adj_rec.ATTRIBUTE14 ,
2397 l_line_adj_rec.ATTRIBUTE15 ,
2398 l_line_adj_rec.ORIG_SYS_DISCOUNT_REF ,
2399 l_line_adj_rec.LIST_HEADER_ID ,
2400 l_line_adj_rec.LIST_LINE_ID ,
2401 l_line_adj_rec.LIST_LINE_TYPE_CODE ,
2402 l_line_adj_rec.MODIFIED_FROM ,
2403 l_line_adj_rec.MODIFIED_TO ,
2404 l_line_adj_rec.UPDATE_ALLOWED ,
2405 p_reason_code ,
2406 p_comments ,
2407 l_line_adj_rec.MODIFIER_MECHANISM_TYPE_CODE,
2408 l_line_adj_rec.UPDATED_FLAG ,
2409 l_line_adj_rec.APPLIED_FLAG ,
2410 l_line_adj_rec.OPERAND ,
2411 l_line_adj_rec.ARITHMETIC_OPERATOR ,
2412 l_line_adj_rec.COST_ID ,
2413 l_line_adj_rec.TAX_CODE ,
2414 l_line_adj_rec.TAX_EXEMPT_FLAG ,
2415 l_line_adj_rec.TAX_EXEMPT_NUMBER ,
2416 l_line_adj_rec.TAX_EXEMPT_REASON_CODE ,
2417 l_line_adj_rec.PARENT_ADJUSTMENT_ID ,
2418 l_line_adj_rec.INVOICED_FLAG ,
2419 l_line_adj_rec.ESTIMATED_FLAG ,
2420 l_line_adj_rec.INC_IN_SALES_PERFORMANCE ,
2421 l_line_adj_rec.SPLIT_ACTION_CODE ,
2422 l_line_adj_rec.ADJUSTED_AMOUNT ,
2423 l_line_adj_rec.PRICING_PHASE_ID ,
2424 l_line_adj_rec.CHARGE_TYPE_CODE ,
2425 l_line_adj_rec.CHARGE_SUBTYPE_CODE ,
2426 l_line_adj_rec.RANGE_BREAK_QUANTITY ,
2427 l_line_adj_rec.ACCRUAL_CONVERSION_RATE ,
2428 l_line_adj_rec.PRICING_GROUP_SEQUENCE ,
2429 l_line_adj_rec.ACCRUAL_FLAG ,
2430 l_line_adj_rec.LIST_LINE_NO ,
2431 l_line_adj_rec.SOURCE_SYSTEM_CODE ,
2432 l_line_adj_rec.BENEFIT_QTY ,
2433 l_line_adj_rec.BENEFIT_UOM_CODE ,
2437 l_line_adj_rec.REBATE_TRANSACTION_REFERENCE,
2434 l_line_adj_rec.PRINT_ON_INVOICE_FLAG ,
2435 l_line_adj_rec.EXPIRATION_DATE ,
2436 l_line_adj_rec.REBATE_TRANSACTION_TYPE_CODE,
2438 l_line_adj_rec.REBATE_PAYMENT_SYSTEM_CODE ,
2439 l_line_adj_rec.REDEEMED_DATE ,
2440 l_line_adj_rec.REDEEMED_FLAG ,
2441 l_line_adj_rec.MODIFIER_LEVEL_CODE ,
2442 l_line_adj_rec.PRICE_BREAK_TYPE_CODE ,
2443 l_line_adj_rec.SUBSTITUTION_ATTRIBUTE ,
2444 l_line_adj_rec.PRORATION_TYPE_CODE ,
2445 l_line_adj_rec.INCLUDE_ON_RETURNS_FLAG ,
2446 l_line_adj_rec.CREDIT_OR_CHARGE_FLAG ,
2447 l_line_adj_rec.AC_CONTEXT ,
2448 l_line_adj_rec.AC_ATTRIBUTE1 ,
2449 l_line_adj_rec.AC_ATTRIBUTE2 ,
2450 l_line_adj_rec.AC_ATTRIBUTE3 ,
2451 l_line_adj_rec.AC_ATTRIBUTE4 ,
2452 l_line_adj_rec.AC_ATTRIBUTE5 ,
2453 l_line_adj_rec.AC_ATTRIBUTE6 ,
2454 l_line_adj_rec.AC_ATTRIBUTE7 ,
2455 l_line_adj_rec.AC_ATTRIBUTE8 ,
2456 l_line_adj_rec.AC_ATTRIBUTE9 ,
2457 l_line_adj_rec.AC_ATTRIBUTE10 ,
2458 l_line_adj_rec.AC_ATTRIBUTE11 ,
2459 l_line_adj_rec.AC_ATTRIBUTE12 ,
2460 l_line_adj_rec.AC_ATTRIBUTE13 ,
2461 l_line_adj_rec.AC_ATTRIBUTE14 ,
2462 l_line_adj_rec.AC_ATTRIBUTE15 ,
2463 P_HIST_TYPE_CODE ,
2464 sysdate ,
2465 nvl(FND_GLOBAL.USER_ID, -1) ,
2466 nvl(FND_GLOBAL.RESP_ID, -1) ,
2467 --VERSIONING Changes
2468 p_AUDIT_FLAG,
2469 p_version_flag,
2470 p_phase_change_flag,
2471 p_version_number,
2472 p_reason_id,
2473 --VERSIONING Changes END
2474 -- eBTax Changes
2475 l_line_adj_rec.tax_rate_id
2476 );
2477
2478 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
2479 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
2480 x_return_status := FND_API.G_RET_STS_SUCCESS;
2481 IF l_debug_level > 0 THEN
2482 oe_debug_pub.add( 'EXITING AFTER INSERTING LINE PRICE ADJUSTMENTS HISTORY' , 5 ) ;
2483 END IF;
2484
2485 EXCEPTION
2486 WHEN OTHERS THEN
2487 IF FND_MSG_PUB.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2488 FND_MSG_PUB.Add_Exc_Msg
2489 (G_PKG_NAME
2490 ,'RecordLPAdjHist');
2491 END IF;
2492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2493 END RecordLPAdjHist;
2494
2495 -- Added to fix 2964593
2496 PROCEDURE Reset_Audit_History_Flags IS
2497 BEGIN
2498 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
2499 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
2500 OE_GLOBALS.OE_AUDIT_HISTORY_TBL.delete;
2501 OE_DEBUG_PUB.add('Reason Required Flag has been reset to N',1);
2502 END Reset_Audit_History_Flags;
2503
2504 END OE_CHG_ORDER_PVT;