1 PACKAGE BODY PO_DATES_S AS
2 /* $Header: POXCODAB.pls 115.9 2004/05/26 00:34:59 spangulu ship $*/
3 /*===========================================================================
4
5 FUNCTION NAME: val_open_period()
6
7 ===========================================================================*/
8
9 FUNCTION val_open_period(x_trx_date IN DATE,
10 x_sob_id IN NUMBER,
11 x_app_name IN VARCHAR2,
12 x_org_id IN NUMBER) RETURN BOOLEAN IS
13
14 /*
15 ** Function checks if inventory or purchasing is installed. If not, returns
16 ** 'TRUE'. If so, checks if date is in an open inventory or purchasing
17 ** period by calling get_closing_status. For GL, always calls
18 ** get_closing_status to check if date is in an open GL period. Returns
19 ** TRUE if the period is open, FALSE otherwise.
20 */
21
22 x_progress VARCHAR2(3) := NULL;
23
24 BEGIN
25 x_progress := '000';
26
27 /* bao - cache values to reduce the number of select statements */
28 /* Bug 3647086: forward port fix; removed caching logic of the closed
29 * status for po, inv, and gl; caching for these values
30 * is not working correctly in some cases.
31 * Kept caching for install status and app id, which are working fine.
32 */
33
34 IF (x_app_name = 'PO') THEN
35
36 IF (PO_DATES_S.x_po_install_status is NULL) THEN
37 /* derive and cache po_install_status */
38 PO_DATES_S.x_po_install_status := PO_CORE_S.get_product_install_status(x_app_name);
39 END IF;
40
41 IF (PO_DATES_S.x_po_install_status = 'I') THEN
42
43 IF (PO_DATES_S.x_po_app_id IS NULL) THEN
44 /* derive and cache app_id */
45 PO_DATES_S.x_po_app_id := get_app_id(x_app_name);
46 END IF;
47
48 -- Bug 3647806: Remove caching of po_closed_status
49 IF (PO_DATES_S.get_closing_status( x_trx_date => x_trx_date
50 , x_sob_id => x_sob_id
51 , x_app_id => PO_DATES_S.x_po_app_id
52 )
53 NOT IN ('O', 'F'))
54 THEN
55 RETURN(FALSE);
56 END IF;
57
58 END IF; /* x_po_install_status = 'I' */
59
60 ELSIF (x_app_name = 'INV') THEN
61
62 IF (PO_DATES_S.x_inv_install_status is NULL) THEN
63 /* derive and cache inv_install_status */
64 PO_DATES_S.x_inv_install_status := PO_CORE_S.get_product_install_status(x_app_name);
65 END IF;
66
67 IF (PO_DATES_S.x_inv_install_status = 'I') THEN
68
69 -- Bug 3647806: Store inv_app_id in x_inv_app_id
70 -- Also, changed get_app_id('SQLGL') to get_app_id('INV'), which is correct for inv
71 IF (PO_DATES_S.x_inv_app_id is NULL) THEN
72 PO_DATES_S.x_inv_app_id := get_app_id('INV');
73 END IF;
74
75 -- Bug 3647806: Remove caching of inv_closed_status
76 IF (PO_DATES_S.get_acct_period_status( x_trx_date => x_trx_date
77 , x_sob_id => x_sob_id
78 , x_app_id => PO_DATES_S.x_inv_app_id
79 , x_org_id => x_org_id
80 )
81 NOT IN ('O', 'F'))
82 THEN
83 RETURN(FALSE);
84 END IF;
85
86 END IF; /* x_inv_install_status = 'I' */
87
88 ELSE
89
90
91 /* Bug 3647806: Also cached gl install status. */
92 IF (x_app_name = 'SQLGL') THEN
93
94 IF (PO_DATES_S.x_sqlgl_install_status is NULL) THEN
95 /* derive and cache gl_install_status */
96 PO_DATES_S.x_sqlgl_install_status := PO_CORE_S.get_product_install_status(x_app_name);
97 END IF;
98
99 END IF;
100
101 IF ((PO_DATES_S.x_sqlgl_install_status = 'I') OR
102 (PO_CORE_S.get_product_install_status(x_app_name) = 'I')) THEN
103
104 IF (PO_DATES_S.x_sqlgl_app_id is NULL) THEN
105 /* derive and cache gl_app_id in x_sqlgl_app_id */
106 PO_DATES_S.x_sqlgl_app_id := get_app_id('SQLGL');
107 END IF;
108
109 -- Bug 3647806: Remove caching of gl_closed_status
110 IF (PO_DATES_S.get_closing_status( x_trx_date => x_trx_date
111 , x_sob_id => x_sob_id
112 , x_app_id => PO_DATES_S.x_sqlgl_app_id
113 )
114 NOT IN ('O', 'F'))
115 THEN
116 RETURN(FALSE);
117 END IF;
118
119 END IF; /* get_product_install_status = 'I' */
120
121 END IF;
122
123 RETURN(TRUE);
124
125 EXCEPTION
126
127 WHEN OTHERS THEN
128 po_message_s.sql_error('val_open_period', x_progress, sqlcode);
129 RAISE;
130
131 END val_open_period;
132
133 /*===========================================================================
134
135 FUNCTION NAME: get_app_id()
136
137 ===========================================================================*/
138
139 FUNCTION get_app_id(x_app_name IN VARCHAR2) RETURN NUMBER IS
140
141 /*
142 ** Function determines the application id using the application short name
143 ** passed in.
144 */
145
146 x_progress VARCHAR2(3) := NULL;
147 x_app_id NUMBER := NULL;
148
149 BEGIN
150 x_progress := '010';
151
152 SELECT application_id
153 INTO x_app_id
154 FROM fnd_application
155 WHERE application_short_name = x_app_name ;
156
157 RETURN(x_app_id);
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 po_message_s.sql_error('get_app_id', x_progress, sqlcode);
162 RAISE;
163
164 END get_app_id;
165 /*===========================================================================
166
167 FUNCTION NAME: get_closing_status()
168
169 ===========================================================================*/
170
171 FUNCTION get_closing_status(x_trx_date IN DATE,
172 x_sob_id IN NUMBER,
173 x_app_id IN NUMBER) RETURN VARCHAR2 IS
174
175 /*
176 ** Function determines the application id using the application short name
177 ** passed in. It returns period closing status.
178 */
179
180 x_progress VARCHAR2(3) := NULL;
181 x_closing_status VARCHAR2(1) := NULL;
182
183 BEGIN
184
185 x_progress := '010';
186
187 SELECT ps.closing_status
188 INTO x_closing_status
189 FROM gl_period_statuses ps
190 WHERE ps.application_id = x_app_id
191 AND ps.adjustment_period_flag = 'N'
192 AND ps.set_of_books_id = x_sob_id
193 AND trunc(x_trx_date) BETWEEN trunc(nvl(ps.start_date, x_trx_date))
194 AND trunc(nvl(ps.end_date, x_trx_date))
195 AND ps.adjustment_period_flag = 'N';
196
197 RETURN(x_closing_status);
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND then
201 po_message_s.app_error('PO_PO_ENTER_OPEN_GL_DATE');
202 RAISE;
203 WHEN TOO_MANY_ROWS then
204 po_message_s.app_error(''); --bad data in gl_period_statuses
205 RAISE;
206 WHEN OTHERS THEN
207 po_message_s.sql_error('get_closing_status', x_progress, sqlcode);
208 RAISE;
209
210 END get_closing_status;
211
212 /*===========================================================================
213
214 FUNCTION NAME: get_acct_period_status()
215
216 ===========================================================================*/
217
218 FUNCTION get_acct_period_status(x_trx_date IN DATE,
219 x_sob_id IN NUMBER,
220 x_app_id IN NUMBER,
221 x_org_id IN NUMBER) RETURN VARCHAR2 IS
222
223 /*
224 ** Function returns accounting period closing status.
225 */
226
227 x_progress VARCHAR2(3) := NULL;
228 x_closing_status VARCHAR2(1) := NULL;
229 x_open_flag VARCHAR2(1) := NULL;
230
231 BEGIN
232
233 x_progress := '010';
234
235 /* SELECT glps.closing_status
236 INTO x_closing_status
237 FROM org_acct_periods oap,
238 gl_period_statuses glps,
239 gl_periods glp
240 WHERE oap.organization_id = x_org_id
241 AND oap.period_set_name = glp.period_set_name
242 AND oap.period_name = glp.period_name -- Bug 873654
243 AND glp.period_name = glps.period_name
244 AND (trunc(x_trx_date)
245 BETWEEN trunc(oap.period_start_date) AND
246 trunc(nvl(oap.period_close_date, oap.schedule_close_date)))
247 AND oap.open_flag = 'Y'
248 AND (trunc(x_trx_date)
249 BETWEEN trunc(glps.start_date) AND trunc(glps.end_date))
250 AND glps.set_of_books_id = x_sob_id
251 AND glps.application_id = x_app_id
252 AND glps.adjustment_period_flag = 'N';
253 */
254 SELECT oap.open_flag
255 INTO x_open_flag
256 FROM org_acct_periods oap
257 WHERE oap.organization_id = x_org_id
258 AND (trunc(x_trx_date)
259 BETWEEN trunc(oap.period_start_date) AND
260 trunc(nvl(oap.period_close_date, oap.schedule_close_date)))
261 AND oap.open_flag = 'Y';
262
263 if (x_open_flag = 'Y') then
264 x_closing_status := 'O';
265 end if;
266
267 RETURN(x_closing_status);
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND then
271 po_message_s.app_error('PO_INV_NO_OPEN_PERIOD');
272 RAISE;
273 WHEN TOO_MANY_ROWS then
274 po_message_s.app_error('PO_INV_MUL_PERIODS');
275 RAISE;
276 WHEN OTHERS THEN
277 po_message_s.sql_error('get_acct_period_status', x_progress, sqlcode);
278 RAISE;
279
280 END get_acct_period_status;
281
282 END PO_DATES_S;
[Home] [Help]