[Home] [Help]
PACKAGE BODY: APPS.OE_REASONS_UTIL
Source
1 PACKAGE BODY OE_Reasons_Util AS
2 /* $Header: OEXURSNB.pls 120.0 2005/06/01 00:52:06 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Reasons_Util';
5
6 /*
7 Valid entity codes matches with OE_GLOBALS.G_ENTITY_%
8 HEADER
9 LINE
10 HEADER_ADJ
11 LINE_ADJ
12 HEADER_SCREDIT
13 LINE_SCREDIT
14 BLANKET_HEADER
15 BLANKET_LINE
16 */
17
18 Procedure Apply_Reason(
19 p_entity_code IN VARCHAR2,
20 p_entity_id IN NUMBER,
21 p_header_id IN NUMBER := NULL,
22 p_version_number IN NUMBER,
23 p_reason_type IN VARCHAR2,
24 p_reason_code IN VARCHAR2,
25 p_reason_comments IN VARCHAR2,
26 x_reason_id OUT NOCOPY NUMBER,
27 x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 l_header_id NUMBER := p_header_id;
30 --
31 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
32 --
33 l_version_number NUMBER := p_version_number;
34 BEGIN
35
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38 IF p_header_id IS NULL THEN
39 IF p_entity_code = 'HEADER' THEN
40 l_header_id := p_entity_id;
41 ELSIF p_entity_code = 'LINE' THEN
42 SELECT header_id INTO l_header_id
43 FROM oe_order_lines_all WHERE line_id = p_entity_id;
44 ELSIF p_entity_code IN ('HEADER_ADJ','LINE_ADJ') THEN
45 SELECT header_id INTO l_header_id
46 FROM oe_price_adjustments WHERE price_adjustment_id = p_entity_id;
47 ELSIF p_entity_code IN ('HEADER_SCREDIT','LINE_SCREDIT') THEN
48 SELECT header_id INTO l_header_id
49 FROM oe_sales_credits WHERE sales_credit_id = p_entity_id;
50 ELSIF p_entity_code = 'BLANKET_HEADER' THEN
51 l_header_id := p_entity_id;
52 ELSIF p_entity_code = 'BLANKET_LINE' THEN
53 SELECT header_id INTO l_header_id
54 FROM oe_blanket_lines_all WHERE line_id = p_entity_id;
55 ELSE
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 END IF;
58 ELSE
59 l_header_id := p_header_id;
60 END IF;
61
62 IF l_debug_level > 0 THEN
63 oe_debug_pub.add('Entering OEXURSNB:Apply_Reason ',1);
64 oe_debug_pub.add('Entity code,id, hdr id, vers, reason_type:'||
65 p_entity_code || ',' || p_entity_id || ',' ||
66 p_header_id || ',' || p_version_number || ',' || p_reason_type,1);
67
68 END IF;
69
70 /*IF p_reason_code IS NULL THEN
71 IF l_debug_level > 0 THEN
72 oe_debug_pub.add('Reason_Code is NULL ',1);
73 END IF;
74
75 RAISE FND_API.G_EXC_ERROR;
76 END IF;
77 */
78 IF p_reason_type IS NULL THEN
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add('Reason_Type is NULL ',1);
81 END IF;
82
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85
86 IF l_version_number IS NULL THEN
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add('Version_Number is NULL! ',1);
89 END IF;
90
91 l_version_number := 0;
92 END IF;
93
94 SELECT OE_REASONS_S.NEXTVAL INTO x_reason_id FROM dual;
95
96 INSERT INTO OE_REASONS
97 (reason_id,
98 entity_code,
99 entity_id,
100 header_id,
101 version_number,
102 reason_type,
103 reason_code,
104 comments,
105 creation_date,
106 created_by,
107 last_updated_by,
108 last_update_date)
109 VALUES
110 (x_reason_id,
111 p_entity_code,
112 p_entity_id,
113 l_header_id,
114 l_version_number,
115 p_reason_type,
116 p_reason_code,
117 p_reason_comments,
118 sysdate,
119 nvl(FND_GLOBAL.USER_ID,-1),
120 nvl(FND_GLOBAL.USER_ID,-1),
121 sysdate);
122
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add('Exiting OEXURSNB:Apply_Reasons ',1);
125 END IF;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130
131 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
132 OE_MSG_PUB.Add_Exc_Msg
133 ( G_PKG_NAME
134 , 'Apply_Reason'
135 );
136 END IF;
137
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END Apply_Reason;
140
141 Procedure Get_Reason(
142 p_reason_id IN NUMBER DEFAULT NULL,
143 p_entity_code IN VARCHAR2 DEFAULT NULL,
144 p_entity_id IN NUMBER DEFAULT NULL,
145 p_version_number IN NUMBER,
146 x_reason_type OUT NOCOPY VARCHAR2,
147 x_reason_code OUT NOCOPY VARCHAR2,
148 x_reason_comments OUT NOCOPY VARCHAR2,
149 x_return_status OUT NOCOPY VARCHAR2
150 )
151 IS
152
153 BEGIN
154
155 IF p_reason_id IS NOT NULL THEN
156
157 SELECT reason_type, reason_code, comments
158 INTO x_reason_type, x_reason_code, x_reason_comments
159 FROM OE_REASONS
160 WHERE REASON_ID = p_reason_id;
161
162 ELSE
163
164 SELECT reason_type, reason_code, comments
165 INTO x_reason_type, x_reason_code, x_reason_comments
166 FROM OE_REASONS
167 WHERE entity_code = p_entity_code
168 AND entity_id = p_entity_id
169 AND version_number = p_version_number;
170
171 END IF;
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 x_return_status := FND_API.G_RET_STS_ERROR;
176 WHEN OTHERS THEN
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178
179 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
180 OE_MSG_PUB.Add_Exc_Msg
181 ( G_PKG_NAME
182 , 'Get_Reason'
183 );
184 END IF;
185
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END Get_Reason;
188
189 END OE_Reasons_Util;