1 PACKAGE BODY RCV_DATES_S AS
2 /* $Header: RCVTXDAB.pls 120.2.12020000.2 2012/11/28 08:41:57 xiameng ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_trx_date()
7
8 ===========================================================================*/
9
10 FUNCTION val_trx_date(x_trx_date IN DATE,
11 x_trx_type IN VARCHAR2,
12 x_parent_trx_date IN OUT NOCOPY DATE,
13 x_line_loc_id IN NUMBER,
14 x_ship_line_id IN NUMBER,
15 x_parent_trx_id IN NUMBER,
16 x_sob_id IN NUMBER,
17 x_org_id IN NUMBER,
18 x_receipt_source_code IN VARCHAR2)RETURN BOOLEAN IS
19
20 /*
21 ** Function validates the transaction date:
22 **
23 ** 1) Checks if the transaction date is less than or equal to the system date.
24 **
25 ** 2) If the transaction type is 'RECEIVE' or 'MATCH' for a vendor, then it
26 ** calls val_receipt_date_tolerance to check if the transaction date falls
27 ** within the receipt date tolerance.
28 **
29 ** 3) Calls PO_DATES_S.val_open_period to check if transaction date is in an
30 ** open GL period.
31 **
32 ** 4) Calls PO_DATES_S.val_open_period to first check if inventory is
33 ** installed. If so, it checks that the transaction date is in an open
34 ** inventory period.
35 **
36 ** 5) Calls PO_DATES_S.val_open_period to first check if purchasing is
37 ** installed. If so, it checks that the transaction date is in an open
38 ** purchasing period.
42 ** transaction date from rcv_transactions using the parent_transaction_id.
39 **
40 ** 6) If the transaction type is not 'RECEIVE' or 'UNORDERED' then it checks
41 ** if the parent transaction date is null. If it is then get the parent
43 ** Then check that the parent transaction date is less than or equal to the
44 ** transaction date.
45 **
46 ** 7) If the transaction type is 'RECEIVE' internally, then gets the shipped
47 ** date from rcv_shipment_headers using the shipment_line_id. Then checks
48 ** if the transaction date is greater than or equal to the shipped date.
49 **
50 ** If any one of these checks fail, the function returns a value of FALSE
51 ** along with the appropriate error message. If all are okay, it returns a
52 ** value of TRUE.
53 */
54
55 x_progress VARCHAR2(3) := NULL;
56 x_shipped_date DATE := NULL;
57 x_opm_orgn sy_orgn_mst.orgn_Code%type := NULL;
58 x_whse_code ic_whse_mst.whse_code%type := NULL;
59 v_retval NUMBER := 0;
60
61 BEGIN
62
63 -- 1) Transaction Date must be greater than the system date
64
65 x_progress := '010';
66
67 IF (x_trx_date > sysdate) THEN
68
69 po_message_s.app_error('RCV_TRX_FUTURE_DATE_NA');
70
71 END IF;
72
73
74 -- Bug 15869032 move this part to the end of this function
75 -- Since this part may return false so skip the validation next part
76 -- such as GL peroid
77 -- 2) Transaction Date must fall within Early/Late receipt date tolerance
78
79 -- x_progress := '020';
80
81 -- IF x_trx_type IN ('RECEIVE','MATCH') AND x_line_loc_id IS NOT NULL THEN
82
83 -- IF NOT (RCV_DATES_S.val_receipt_date_tolerance(x_line_loc_id,
84 -- x_trx_date)) THEN
85 /* BUG 704593
86 * The following app_error raises an exception, it's wrong for
87 * the scenario when the days_exception_code is only 'warning'.
88 * exception should be raised in the procedure which calls this
89 * function.
90 */
91 --po_message_s.app_error('RCV_ALL_DATE_OUT_OF_RANGE');
92 -- RETURN (FALSE);
93
94 -- END IF;
95
96 -- END IF;
97 -- Bug 15869032 end
98
99 -- 3) Transaction Date must be in an open GL period
100
101 x_progress := '030';
102
103 IF NOT (PO_DATES_S.val_open_period(
104 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
105 x_sob_id,
106 'SQLGL',
107 x_org_id)) THEN
108
109 /* Bug# 2235828 */
110 /* po_message_s.app_error('PO_PO_ENTER_OPEN_GL_DATE'); */
111 po_message_s.app_error('PO_CNL_NO_PERIOD');
112
113 END IF;
114
115
116 -- 4) Transaction Date must be in open accounting period if INV is installed
117
118 x_progress := '040';
119 /* INVCONV BEGIN PBAMB */
120 /*Bug# 1548597 check if for this proess receipt the OPM inventory calendars are open*/
121 /*If PO_GML_DB_COMMON.CHECK_PROCESS_ORG(x_org_id) = 'Y' then
122 Select whse_code,orgn_code
123 into x_whse_code,x_opm_orgn
124 from ic_whse_mst
125 where mtl_organization_id = x_org_id;
126
127 v_retval := GMICCAL.trans_date_validate(
128 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
129 x_opm_orgn,
130 x_whse_code);
131
132 --IF v_retval = -21 THEN /* Fiscal Yr and Fiscal Yr beginning date not found. */
133 -- po_message_s.app_error('INVCAL_FISCALYR_ERR');
134
135 --ELSIF v_retval = -22 THEN /* Period end date and close indicator not found. */
136 -- po_message_s.app_error('INVCAL_PERIOD_ERR');
137
138 -- ELSIF v_retval = -23 THEN /* Date is within a closed Inventory calendar period */
139 -- po_message_s.app_error('INVCAL_CLOSED_PERIOD_ERR');
140
141 --ELSIF v_retval = -24 THEN /* Company Code not found. */
142 -- po_message_s.app_error('INVCAL_INVALIDCO_ERR');
143
144 --ELSIF v_retval = -25 THEN /* Warehouse has been closed for the period */
145 -- po_message_s.app_error('INVCAL_WHSE_CLOSED_ERR');
146
147 --ELSIF v_retval = -26 THEN /* Transaction not passed in as a parameter.*/
148 -- po_message_s.app_error('INVCAL_TRANS_DATE_ERR');
149
150 --ELSIF v_retval = -27 THEN /* Organization code not passed as a parameter.*/
151 -- po_message_s.app_error('INVCAL_INVALIDORGN_ERR');
152
153 --ELSIF v_retval = -28 THEN /* Warehouse code not passed as a parameter.*/
154 -- po_message_s.app_error('INVCAL_WHSEPARM_ERR');
155
156 --ELSIF v_retval = -29 THEN /* Warehouse code is not found. */
157 -- po_message_s.app_error('INVCAL_WHSE_ERR');
158
159 --ELSIF v_retval < -29 THEN /* Log a general message */
160 -- po_message_s.app_error('INVCAL_GENL_ERR');
161 --END IF;
162
163 --else
164
165 IF NOT (PO_DATES_S.val_open_period(
166 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
167 x_sob_id,
168 'INV',
169 x_org_id)) THEN
170
171 po_message_s.app_error('PO_INV_NO_OPEN_PERIOD');
172
173 END IF;
174 --end if;
175
176
177 -- 5) Transaction Date must be in an open PO period
178
179 x_progress := '050';
180
181 IF NOT (PO_DATES_S.val_open_period(
182 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
183 x_sob_id,
184 'PO',
188
185 x_org_id)) THEN
186
187 po_message_s.app_error('PO_PO_ENTER_OPEN_GL_DATE');
189 END IF;
190
191 /* Bug#3308963 Added the following assignment statement for assigning the value
192 ** of x_trx_date to the global variable after successfull validation of
193 ** Purchasing open period.
194 */
195
196 /* Bug 3622309.
197 * Commenting out the change below since this is causing problems.
198 PO_DATES_S.x_last_txn_date := x_trx_date ;
199 */
200
201
202
203 -- 6) Transaction Date must be > parent transaction date
204
205 x_progress := '060';
206
207 IF (x_trx_type NOT IN ('RECEIVE','UNORDERED') AND
208 x_parent_trx_date is null) THEN
209
210 /* Bug 6957731
211 ** Time stamp of transaction date was not considered during validation.
212 ** Replaced Trunc(transaction_date) with transaction_date.
213 */
214
215 SELECT transaction_date
216 INTO x_parent_trx_date
217 FROM rcv_transactions
218 WHERE transaction_id = x_parent_trx_id;
219
220 END IF;
221
222 x_progress := '070';
223
224 IF x_trx_type NOT IN ('RECEIVE','UNORDERED') THEN
225
226 IF (x_parent_trx_date > x_trx_date) THEN
227
228 po_message_s.app_error('RCV_TRX_ENTER_DT_GT_PARENT_DT');
229
230 END IF;
231
232 END IF;
233
234
235 /* This must be checked at the line level. You cannot validate this here
236 -- 7) Transaction Date must be >= shipped date for internal receipts
237
238 x_progress := '80';
239
240 IF x_trx_type = 'RECEIVE' and x_ship_line_id IS NOT NULL and
241 x_receipt_source_code = 'INTERNAL' THEN
242
243 SELECT trunc(rsh.shipped_date)
244 INTO x_shipped_date
245 FROM rcv_shipment_headers rsh,
246 rcv_shipment_lines rsl
247 WHERE rsh.shipment_header_id = rsl.shipment_header_id
248 AND rsh.organization_id = x_org_id;
249
250 IF (x_trx_date < x_shipped_date) THEN
251
252 po_message_s.app_error('RCV_ERC_SHIP_DATE_GT_RCV_DATE');
253
254 END IF;
255
256 END IF;
257 */
258
259 -- Bug 15869032 moved this part here
260 -- 2) Transaction Date must fall within Early/Late receipt date tolerance
261
262 x_progress := '020';
263
264 IF x_trx_type IN ('RECEIVE','MATCH') AND x_line_loc_id IS NOT NULL THEN
265
266 IF NOT (RCV_DATES_S.val_receipt_date_tolerance(x_line_loc_id,
267 x_trx_date)) THEN
268 /* BUG 704593
269 * The following app_error raises an exception, it's wrong for
270 * the scenario when the days_exception_code is only 'warning'.
271 * exception should be raised in the procedure which calls this
272 * function.
273 */
274 --po_message_s.app_error('RCV_ALL_DATE_OUT_OF_RANGE');
275 RETURN (FALSE);
276
277 END IF;
278
279 END IF;
280
281 RETURN(TRUE);
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 po_message_s.sql_error('val_trx_date', x_progress, sqlcode);
286 RAISE;
287
288 END val_trx_date;
289
290 /*===========================================================================
291
292 FUNCTION NAME: val_receipt_date_tolerance()
293
294 ===========================================================================*/
295
296 FUNCTION val_receipt_date_tolerance(x_line_loc_id IN NUMBER,
297 x_receipt_date IN DATE)RETURN BOOLEAN IS
298
299 /*
300 ** Function determines if the receipt date falls within the receipt date
301 ** tolerance window. If it does, the function returns a value of TRUE,
302 ** otherwise it returns a value of FALSE.
303 */
304
305 x_progress VARCHAR2(3) := NULL;
306 x_days_early_receipt_allowed NUMBER;
307 x_days_late_receipt_allowed NUMBER;
308 x_promised_date DATE;
309 x_need_by_date DATE;
310 days_diff NUMBER := 0;
311
312 BEGIN
313
314 x_progress := '010';
315
316 SELECT days_early_receipt_allowed, days_late_receipt_allowed,
317 promised_date, need_by_date
318 INTO x_days_early_receipt_allowed, x_days_late_receipt_allowed,
319 x_promised_date, x_need_by_date
320 FROM po_line_locations
321 WHERE line_location_id = x_line_loc_id;
322
323 days_diff := x_receipt_date -
324
325 nvl(nvl(x_promised_date,x_need_by_date),x_receipt_date);
326
327 IF (days_diff < 0) THEN
328
329 IF x_days_early_receipt_allowed < ABS(days_diff) THEN
330 RETURN (FALSE);
331 ELSE
332 RETURN (TRUE);
333 END IF;
334
335 ELSE
339 ELSE
336
337 IF x_days_late_receipt_allowed < days_diff THEN
338 RETURN (FALSE);
340 RETURN (TRUE);
341 END IF;
342
343 END IF;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 po_message_s.sql_error('val_receipt_date_tolerance', x_progress,sqlcode);
348 RAISE;
349
350 END val_receipt_date_tolerance;
351
352 END RCV_DATES_S;
[Home] [Help]