1 PACKAGE BODY RCV_DATES_S AS
2 /* $Header: RCVTXDAB.pls 120.0.12010000.2 2008/08/04 08:42:42 rramasam 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.
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
42 ** transaction date from rcv_transactions using the parent_transaction_id.
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 -- 2) Transaction Date must fall within Early/Late receipt date tolerance
75
76 x_progress := '020';
77
78 IF x_trx_type IN ('RECEIVE','MATCH') AND x_line_loc_id IS NOT NULL THEN
79
80 IF NOT (RCV_DATES_S.val_receipt_date_tolerance(x_line_loc_id,
81 x_trx_date)) THEN
82 /* BUG 704593
83 * The following app_error raises an exception, it's wrong for
84 * the scenario when the days_exception_code is only 'warning'.
85 * exception should be raised in the procedure which calls this
86 * function.
87 */
88 --po_message_s.app_error('RCV_ALL_DATE_OUT_OF_RANGE');
89 RETURN (FALSE);
90
91 END IF;
92
93 END IF;
94
95
96 -- 3) Transaction Date must be in an open GL period
97
98 x_progress := '030';
99
100 IF NOT (PO_DATES_S.val_open_period(
101 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
102 x_sob_id,
103 'SQLGL',
104 x_org_id)) THEN
105
106 /* Bug# 2235828 */
107 /* po_message_s.app_error('PO_PO_ENTER_OPEN_GL_DATE'); */
108 po_message_s.app_error('PO_CNL_NO_PERIOD');
109
110 END IF;
111
112
113 -- 4) Transaction Date must be in open accounting period if INV is installed
114
115 x_progress := '040';
116 /* INVCONV BEGIN PBAMB */
117 /*Bug# 1548597 check if for this proess receipt the OPM inventory calendars are open*/
118 /*If PO_GML_DB_COMMON.CHECK_PROCESS_ORG(x_org_id) = 'Y' then
119 Select whse_code,orgn_code
120 into x_whse_code,x_opm_orgn
121 from ic_whse_mst
122 where mtl_organization_id = x_org_id;
123
124 v_retval := GMICCAL.trans_date_validate(
125 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
126 x_opm_orgn,
127 x_whse_code);
128
129 --IF v_retval = -21 THEN /* Fiscal Yr and Fiscal Yr beginning date not found. */
130 -- po_message_s.app_error('INVCAL_FISCALYR_ERR');
131
132 --ELSIF v_retval = -22 THEN /* Period end date and close indicator not found. */
133 -- po_message_s.app_error('INVCAL_PERIOD_ERR');
134
135 -- ELSIF v_retval = -23 THEN /* Date is within a closed Inventory calendar period */
136 -- po_message_s.app_error('INVCAL_CLOSED_PERIOD_ERR');
137
138 --ELSIF v_retval = -24 THEN /* Company Code not found. */
139 -- po_message_s.app_error('INVCAL_INVALIDCO_ERR');
140
141 --ELSIF v_retval = -25 THEN /* Warehouse has been closed for the period */
142 -- po_message_s.app_error('INVCAL_WHSE_CLOSED_ERR');
143
144 --ELSIF v_retval = -26 THEN /* Transaction not passed in as a parameter.*/
145 -- po_message_s.app_error('INVCAL_TRANS_DATE_ERR');
146
147 --ELSIF v_retval = -27 THEN /* Organization code not passed as a parameter.*/
148 -- po_message_s.app_error('INVCAL_INVALIDORGN_ERR');
149
150 --ELSIF v_retval = -28 THEN /* Warehouse code not passed as a parameter.*/
151 -- po_message_s.app_error('INVCAL_WHSEPARM_ERR');
152
153 --ELSIF v_retval = -29 THEN /* Warehouse code is not found. */
154 -- po_message_s.app_error('INVCAL_WHSE_ERR');
155
156 --ELSIF v_retval < -29 THEN /* Log a general message */
157 -- po_message_s.app_error('INVCAL_GENL_ERR');
158 --END IF;
159
160 --else
161
162 IF NOT (PO_DATES_S.val_open_period(
163 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
164 x_sob_id,
165 'INV',
166 x_org_id)) THEN
167
168 po_message_s.app_error('PO_INV_NO_OPEN_PERIOD');
169
170 END IF;
171 --end if;
172
173
174 -- 5) Transaction Date must be in an open PO period
175
176 x_progress := '050';
177
178 IF NOT (PO_DATES_S.val_open_period(
179 inv_le_timezone_pub.get_le_day_for_inv_org(x_trx_date, x_org_id),
180 x_sob_id,
181 'PO',
182 x_org_id)) THEN
183
184 po_message_s.app_error('PO_PO_ENTER_OPEN_GL_DATE');
185
186 END IF;
187
188 /* Bug#3308963 Added the following assignment statement for assigning the value
189 ** of x_trx_date to the global variable after successfull validation of
190 ** Purchasing open period.
191 */
192
193 /* Bug 3622309.
194 * Commenting out the change below since this is causing problems.
195 PO_DATES_S.x_last_txn_date := x_trx_date ;
196 */
197
198
199
200 -- 6) Transaction Date must be > parent transaction date
201
202 x_progress := '060';
203
204 IF (x_trx_type NOT IN ('RECEIVE','UNORDERED') AND
205 x_parent_trx_date is null) THEN
206
207 /* Bug 6957731
208 ** Time stamp of transaction date was not considered during validation.
209 ** Replaced Trunc(transaction_date) with transaction_date.
210 */
211
212 SELECT transaction_date
213 INTO x_parent_trx_date
214 FROM rcv_transactions
215 WHERE transaction_id = x_parent_trx_id;
216
217 END IF;
218
219 x_progress := '070';
220
221 IF x_trx_type NOT IN ('RECEIVE','UNORDERED') THEN
222
223 IF (x_parent_trx_date > x_trx_date) THEN
224
225 po_message_s.app_error('RCV_TRX_ENTER_DT_GT_PARENT_DT');
226
227 END IF;
228
229 END IF;
230
231
232 /* This must be checked at the line level. You cannot validate this here
233 -- 7) Transaction Date must be >= shipped date for internal receipts
234
235 x_progress := '80';
236
237 IF x_trx_type = 'RECEIVE' and x_ship_line_id IS NOT NULL and
238 x_receipt_source_code = 'INTERNAL' THEN
239
240 SELECT trunc(rsh.shipped_date)
241 INTO x_shipped_date
242 FROM rcv_shipment_headers rsh,
243 rcv_shipment_lines rsl
244 WHERE rsh.shipment_header_id = rsl.shipment_header_id
245 AND rsh.organization_id = x_org_id;
246
247 IF (x_trx_date < x_shipped_date) THEN
248
249 po_message_s.app_error('RCV_ERC_SHIP_DATE_GT_RCV_DATE');
250
251 END IF;
252
253 END IF;
254 */
255
256 RETURN(TRUE);
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 po_message_s.sql_error('val_trx_date', x_progress, sqlcode);
261 RAISE;
262
263 END val_trx_date;
264
265 /*===========================================================================
266
267 FUNCTION NAME: val_receipt_date_tolerance()
268
269 ===========================================================================*/
270
271 FUNCTION val_receipt_date_tolerance(x_line_loc_id IN NUMBER,
272 x_receipt_date IN DATE)RETURN BOOLEAN IS
273
274 /*
275 ** Function determines if the receipt date falls within the receipt date
276 ** tolerance window. If it does, the function returns a value of TRUE,
277 ** otherwise it returns a value of FALSE.
278 */
279
280 x_progress VARCHAR2(3) := NULL;
281 x_days_early_receipt_allowed NUMBER;
282 x_days_late_receipt_allowed NUMBER;
283 x_promised_date DATE;
284 x_need_by_date DATE;
285 days_diff NUMBER := 0;
286
287 BEGIN
288
289 x_progress := '010';
290
291 SELECT days_early_receipt_allowed, days_late_receipt_allowed,
292 promised_date, need_by_date
293 INTO x_days_early_receipt_allowed, x_days_late_receipt_allowed,
294 x_promised_date, x_need_by_date
295 FROM po_line_locations
296 WHERE line_location_id = x_line_loc_id;
297
298 days_diff := x_receipt_date -
299
300 nvl(nvl(x_promised_date,x_need_by_date),x_receipt_date);
301
302 IF (days_diff < 0) THEN
303
304 IF x_days_early_receipt_allowed < ABS(days_diff) THEN
305 RETURN (FALSE);
306 ELSE
307 RETURN (TRUE);
308 END IF;
309
310 ELSE
311
312 IF x_days_late_receipt_allowed < days_diff THEN
313 RETURN (FALSE);
314 ELSE
315 RETURN (TRUE);
316 END IF;
317
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 po_message_s.sql_error('val_receipt_date_tolerance', x_progress,sqlcode);
323 RAISE;
324
325 END val_receipt_date_tolerance;
326
327 END RCV_DATES_S;
[Home] [Help]