[Home] [Help]
PACKAGE BODY: APPS.PON_ACTION_HIST_PKG
Source
1 PACKAGE BODY PON_ACTION_HIST_PKG AS
2 -- $Header: PONHISTB.pls 120.0 2005/06/01 18:14:43 appldev noship $
3
4 --========================================================================
5 -- PROCEDURE : InsertRowHandler PRIVATE
6 -- COMMENT : Table handler for PON_ACTION_HISTORY
7 --========================================================================
8
9 PROCEDURE InsertRowHandler( p_OBJECT_ID IN NUMBER,
10 p_OBJECT_ID2 IN NUMBER,
11 p_OBJECT_TYPE_CODE IN VARCHAR2,
12 p_SEQUENCE_NUM IN NUMBER,
13 p_ACTION_TYPE IN VARCHAR2,
14 p_ACTION_USER_ID IN NUMBER,
15 p_ACTION_NOTE IN VARCHAR2,
16 p_ACTION_REASON_CODE IN VARCHAR2
17 )
18
19 IS
20
21 BEGIN
22
23 insert into PON_ACTION_HISTORY( OBJECT_ID,
24 OBJECT_ID2,
25 OBJECT_TYPE_CODE,
26 SEQUENCE_NUM,
27 ACTION_TYPE,
28 ACTION_DATE,
29 ACTION_USER_ID,
30 ACTION_NOTE,
31 ACTION_REASON_CODE)
32 VALUES( p_OBJECT_ID,
33 p_OBJECT_ID2,
34 p_OBJECT_TYPE_CODE,
35 p_SEQUENCE_NUM,
36 p_ACTION_TYPE,
37 sysdate,
38 p_ACTION_USER_ID,
39 p_ACTION_NOTE,
40 p_ACTION_REASON_CODE);
41
42 END InsertRowHandler;
43
44
45 --========================================================================
46 -- PROCEDURE : UpdateRowHandler PRIVATE
47 -- COMMENT : Table handler for PON_ACTION_HISTORY
48 --========================================================================
49
50 PROCEDURE UpdateRowHandler( p_OBJECT_ID IN NUMBER,
51 p_OBJECT_ID2 IN NUMBER,
52 p_OBJECT_TYPE_CODE IN VARCHAR2,
53 p_SEQUENCE_NUM IN NUMBER,
54 p_ACTION_TYPE IN VARCHAR2)
55
56 IS
57
58 BEGIN
59
60
61 UPDATE PON_ACTION_HISTORY
62 SET
63 ACTION_TYPE = p_action_type,
64 ACTION_DATE = sysdate
65 WHERE OBJECT_ID = p_object_id
66 and OBJECT_ID2 = p_object_id2
67 and OBJECT_TYPE_CODE = p_object_type_code
68 and SEQUENCE_NUM = p_sequence_num
69 and ACTION_TYPE is NULL;
70
71
72 END UpdateRowHandler;
73
74 --========================================================================
75 -- PROCEDURE : RecordHistory PUBLIC
76 -- PARAMETERS: p_OBJECT_TYPE_CODE => PON_SPOTBUY for Shopping Cart.
77 -- PON_AUCTIONS for Auctions.
78 -- p_ORDER_ID => For Spot Buy => Order Header Number.
79 -- For Auctions =>
80 -- COMMENT : Logic for determing values to insert into PON_ACTION_HISTORY
81 --========================================================================
82
83 PROCEDURE RecordHistory( p_OBJECT_ID IN NUMBER,
84 p_OBJECT_ID2 IN NUMBER,
85 p_OBJECT_TYPE_CODE IN VARCHAR2,
86 p_ACTION_TYPE IN VARCHAR2,
87 p_ACTION_USER_ID IN NUMBER,
88 p_ACTION_NOTE IN VARCHAR2,
89 p_ACTION_REASON_CODE IN VARCHAR2,
90 p_ACTION_USER_ID_NEXT IN NUMBER,
91 p_CONTINUE IN VARCHAR2)
92 IS
93
94
95 cursor chk_sequence_number(ln_objectid NUMBER, ln_objectid2 NUMBER, lv_type VARCHAR2) is
96 select max(pah.sequence_num)
97 from PON_ACTION_HISTORY PAH
98 where PAH.OBJECT_ID = ln_objectid
99 and (PAH.OBJECT_ID2 = ln_objectid2 OR PAH.OBJECT_ID2 is null)
100 and PAH.OBJECT_TYPE_CODE = lv_type;
101
102 ln_seq_number NUMBER;
103 ln_new_seq_number NUMBER;
104
105 BEGIN
106
107 OPEN chk_sequence_number(p_object_id, p_object_id2, p_object_type_code);
108 FETCH chk_sequence_number into ln_seq_number;
109 CLOSE chk_sequence_number;
110
111 IF (ln_seq_number >= 0) THEN -- Insert into PON_ACTION_HISTORY with the same OBJECT_IDS
112
113 ln_new_seq_number := ln_seq_number + 1;
114
115 ELSE
116
117 ln_seq_number := 0;
118 ln_new_seq_number := 0;
119
120 END IF;
121
122
123 IF (p_continue = 'N') THEN -- We are not going to be creating two rows or updating
124
125 -- Call the RowHandler Procedure to insert a row into the table PON_ACTION_HISTORY
126
127 InsertRowHandler( p_OBJECT_ID => p_object_id,
128 p_OBJECT_ID2 => p_object_id2,
129 p_OBJECT_TYPE_CODE => p_object_type_code,
130 p_SEQUENCE_NUM => ln_new_seq_number,
131 p_ACTION_TYPE => p_action_type,
132 p_ACTION_USER_ID => p_action_user_id,
133 p_ACTION_NOTE => p_action_note,
134 p_ACTION_REASON_CODE => p_action_reason_code);
135
136 ELSIF (p_continue = 'F') THEN -- We are just going to update the existing row.
137
138
139 UpdateRowHandler( p_OBJECT_ID => p_object_id,
140 p_OBJECT_ID2 => p_object_id2,
141 p_OBJECT_TYPE_CODE => p_object_type_code,
142 p_SEQUENCE_NUM => ln_seq_number,
143 p_ACTION_TYPE => p_action_type);
144
145
146 ELSE
147 if (ln_new_seq_number = 0) then
148 -- If the sequence number is 0 then we have two create two rows as this is a new entry
149
150 -- First with the current information
151 InsertRowHandler( p_OBJECT_ID => p_object_id,
152 p_OBJECT_ID2 => p_object_id2,
153 p_OBJECT_TYPE_CODE => p_object_type_code,
154 p_SEQUENCE_NUM => ln_new_seq_number,
155 p_ACTION_TYPE => p_action_type,
156 p_ACTION_USER_ID => p_action_user_id,
157 p_ACTION_NOTE => p_action_note,
158 p_ACTION_REASON_CODE => p_action_reason_code);
159
160
161 -- Second with the future information
162 InsertRowHandler( p_OBJECT_ID => p_object_id,
163 p_OBJECT_ID2 => p_object_id2,
164 p_OBJECT_TYPE_CODE => p_object_type_code,
165 p_SEQUENCE_NUM => 1,
166 p_ACTION_TYPE => NULL,
167 p_ACTION_USER_ID => p_action_user_id_next,
168 p_ACTION_NOTE => p_action_note,
169 p_ACTION_REASON_CODE => p_action_reason_code);
170
171
172 else
173
174 -- since the sequence number is not 0 then we have a create a
175 -- single row and update the existing row
176
177
178 UpdateRowHandler( p_OBJECT_ID => p_object_id,
179 p_OBJECT_ID2 => p_object_id2,
180 p_OBJECT_TYPE_CODE => p_object_type_code,
181 p_SEQUENCE_NUM => ln_seq_number,
182 p_ACTION_TYPE => p_action_type);
183
184
185 InsertRowHandler( p_OBJECT_ID => p_object_id,
186 p_OBJECT_ID2 => p_object_id2,
187 p_OBJECT_TYPE_CODE => p_object_type_code,
188 p_SEQUENCE_NUM => ln_new_seq_number,
189 p_ACTION_TYPE => NULL,
190 p_ACTION_USER_ID => p_action_user_id_next,
191 p_ACTION_NOTE => p_action_note,
192 p_ACTION_REASON_CODE => p_action_reason_code);
193
194
195 end if;
196
197 END IF;
198
199 END RecordHistory;
200
201 END PON_ACTION_HIST_PKG;
202