1 PACKAGE BODY OE_HEADER_STATUS_PUB AS
2 /* $Header: OEXPHDSB.pls 120.0 2005/06/01 02:50:05 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_HEADER_STATUS_PUB';
7
8 /*---------------------------------------------
9 PROCEDURE Get_Cancelled_Status (without date)
10
11 This procedure will take a header_id and
12 and check if the Order has been cancelled.
13 If the order has been cancelled, it will return
14 a value of 'Y' in x_result. Otherwise a value
15 of 'N' will be returned.
16 ----------------------------------------------- */
17
18 PROCEDURE Get_Cancelled_Status(
19 p_header_id IN NUMBER,
20 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
21
22 IS
23
24 l_cancel_flag VARCHAR2(1);
25
26 BEGIN
27
28 SELECT cancelled_flag
29 INTO l_cancel_flag
30 FROM oe_order_headers_all
31 WHERE header_id = p_header_id;
32
33 -- we are returning Line_Closed_Status,
34 -- so we return Y when open_flag is N
35 -- and return N when open_flag is Y
36
37 IF l_cancel_flag = 'Y' THEN
38 x_result := 'Y';
39 ELSE
40 x_result := 'N';
41 END IF;
42
43 Exception
44 when others then
45 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
46 THEN
47 OE_MSG_PUB.Add_Exc_Msg
48 (
49 G_PKG_NAME,
50 'Get_Cancelled_Status'
51 );
52 END IF;
53 End Get_Cancelled_Status;
54
55
56
57 /*---------------------------------------------
58 PROCEDURE Get_Cancelled_Status (with date)
59
60 This is the overloaded version, it will
61 not only return the Y/N, but also the date
62 the WF activity happened. If the order is not
63 cancelled, we will return a null result date
64 ----------------------------------------------- */
65
66 PROCEDURE Get_Cancelled_Status(
67 p_header_id IN NUMBER,
68 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
69 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
70
71 IS
72
73 BEGIN
74
75 Get_Cancelled_Status(p_header_id => p_header_id, x_result => x_result);
76 IF x_result = 'Y' THEN
77 SELECT end_date
78 INTO x_result_date
79 FROM wf_item_activity_statuses wias, wf_process_activities wpa
80 WHERE wias.item_type = OE_GLOBALS.G_WFI_HDR
81 AND wias.item_key = to_char(p_header_id)
82 AND wias.process_activity = wpa.instance_id
83 AND wpa.activity_name = 'CLOSE_HEADER';
84 ELSE
85 x_result_date := null;
86 END IF;
87
88
89 Exception
90 when others then
91 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
92 THEN
93 OE_MSG_PUB.Add_Exc_Msg
94 (
95 G_PKG_NAME,
96 'Get_Cancelled_Status'
97 );
98 END IF;
99 END Get_Cancelled_Status;
100
101
102
103 /*---------------------------------------------
104 PROCEDURE Get_Booked_Status (without date)
105
106 This procedure will take a header_id and return
107 a 'Y' if the order has been booked, and return
108 a 'N' if the order has not been booked.
109 ----------------------------------------------- */
110
111 PROCEDURE Get_Booked_Status(
112 p_header_id IN NUMBER,
113 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
114 IS
115 BEGIN
116
117 SELECT nvl(booked_flag, 'N')
118 INTO x_result
119 FROM oe_order_headers_all
120 WHERE header_id = p_header_id;
121
122 Exception
123 when others then
124 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
125 THEN
126 OE_MSG_PUB.Add_Exc_Msg
127 (
128 G_PKG_NAME,
129 'Get_Booked_Status'
130 );
131 END IF;
132 END Get_Booked_Status;
133
134
135 /*---------------------------------------------
136 PROCEDURE Get_Booked_Status (with date)
137
138 This is the overloaded version of Get_Booked_Status
139 (without date). In addition to return a 'Y' or
140 'N' for the booking status, it will also return
141 the date the activity happened. If the activity
142 has not happened, a null date will be returned.
143 ----------------------------------------------- */
144
145 PROCEDURE Get_Booked_Status(
146 p_header_id IN NUMBER,
147 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
148 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
149
150 IS
151 BEGIN
152
153 SELECT nvl(booked_flag, 'N'), booked_date
154 INTO x_result, x_result_date
155 FROM oe_order_headers_all
156 WHERE header_id = p_header_id;
157
158 Exception
159 when others then
160 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
161 THEN
162 OE_MSG_PUB.Add_Exc_Msg
163 (
164 G_PKG_NAME,
165 'Get_Booked_Status'
166 );
167 END IF;
168 END Get_Booked_Status;
169
170
171 /*---------------------------------------------
172 PROCEDURE Get_Closed_Status (without date)
173
174 This procedure will take a header_id and
175 return a 'Y' if the header/order has been
176 closed, and a 'N' if not.
177 ----------------------------------------------- */
178
179 PROCEDURE Get_Closed_Status(
180 p_header_id IN NUMBER,
181 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
182
183 IS
184
185 l_open_flag VARCHAR2(1);
186
187 BEGIN
188
189 SELECT nvl(open_flag, 'Y')
190 INTO l_open_flag
191 FROM oe_order_headers_all
192 WHERE header_id = p_header_id;
193
194 -- we are returning Closed Status,
195 -- so we return Y when open_flag is N
196 -- and return N when open_flag is Y
197
198 IF l_open_flag = 'Y' THEN
199 x_result := 'N';
200 ELSE
201 x_result := 'Y';
202 END IF;
203
204 Exception
205 when others then
206 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
207 THEN
208 OE_MSG_PUB.Add_Exc_Msg
209 (
210 G_PKG_NAME,
211 'Get_Closed_Status'
212 );
213 END IF;
214 END Get_Closed_Status;
215
216
217 /*---------------------------------------------
218 PROCEDURE Get_Closed_Status (with date)
219
220 This is the overloaded version of Get_Closed_Status
221 (without date). In addition to return a 'Y' or 'N' for
222 the header closure status, it will also return the
223 date the activity happened. If the activity hasn't
224 happened, a null result date will be returned.
225 ----------------------------------------------- */
226
227 PROCEDURE Get_Closed_Status(
228 p_header_id IN NUMBER,
229 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
230 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
231
232 IS
233
234 BEGIN
235
236 Get_Closed_Status(p_header_id => p_header_id, x_result => x_result);
237 IF x_result = 'Y' THEN
238 SELECT end_date
239 INTO x_result_date
240 FROM wf_item_activity_statuses wias, wf_process_activities wpa
241 WHERE wias.item_type = OE_GLOBALS.G_WFI_HDR
242 AND wias.item_key = to_char(p_header_id)
243 AND wias.process_activity = wpa.instance_id
244 AND wpa.activity_name = 'CLOSE_HEADER';
245 ELSE
246 x_result_date := null;
247 END IF;
248
249 Exception
250 when others then
251 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
252 THEN
253 OE_MSG_PUB.Add_Exc_Msg
254 (
255 G_PKG_NAME,
256 'Get_Closed_Status'
257 );
258 END IF;
259 END Get_Closed_Status;
260
261
262 END OE_HEADER_STATUS_PUB;
[Home] [Help]