1 PACKAGE BODY INV_TRANSACTION_FLOW_PVT AS
2 /* $Header: INVICTFB.pls 120.2 2006/08/14 12:02:01 anthiyag noship $ */
3 -- global variables
4 g_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5 g_version_printed BOOLEAN := FALSE;
6 g_pkg_name VARCHAR2(30):='INV_TRANSACTION_FLOW_PVT';
7 g_miss_date DATE:=to_date(to_char(FND_API.G_MISS_DATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS');
8
9 --procedures
10
11 -- This procedure is used to write logs for debugging.
12 PROCEDURE DEBUG(p_message IN VARCHAR2,
13 p_module IN VARCHAR2 default 'abc',
14 p_level IN VARCHAR2 DEFAULT 9) IS
15 BEGIN
16 IF NOT g_version_printed THEN
17 INV_TRX_UTIL_PUB.TRACE('$Header: INVICTFB.pls 120.2 2006/08/14 12:02:01 anthiyag noship $',g_pkg_name, 9);
18 g_version_printed := TRUE;
19 END IF;
20 INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
21 ,P_MOD => p_module
22 ,p_level => p_level
23 );
24 END; -- DEBUG
25
26 /*=======================================================================================================*/
27
28 FUNCTION Validate_Operating_Unit(P_ORG_ID IN NUMBER) RETURN BOOLEAN IS
29 l_count NUMBER:=0;
30 BEGIN
31 SELECT 1 INTO l_count FROM HR_OPERATING_UNITS
32 WHERE ORGANIZATION_ID=P_ORG_ID
33 AND NVL(DATE_TO,SYSDATE) >= SYSDATE;
34 RETURN TRUE;
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN
37 RETURN FALSE;
38 END;
39
40 /*=======================================================================================================*/
41
42 FUNCTION Validate_Organization(
43 P_ORGANIZATION_ID IN NUMBER,
44 P_ORG_ID IN NUMBER
45 ) RETURN BOOLEAN IS
46 l_count NUMBER:=0;
47 BEGIN
48 SELECT 1 INTO l_count FROM ORG_ORGANIZATION_DEFINITIONS
49 WHERE ORGANIZATION_ID=P_ORGANIZATION_ID
50 AND OPERATING_UNIT=P_ORG_ID
51 AND NVL(DISABLE_DATE,SYSDATE) >= SYSDATE;
52 RETURN TRUE;
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 RETURN FALSE;
56 END;
57
58 /*=======================================================================================================*/
59
60 FUNCTION Validate_Qualifier_Code(P_QUALIFIER_CODE IN NUMBER)RETURN BOOLEAN IS
61 l_count NUMBER:=0;
62 BEGIN
63 IF p_qualifier_code IS NOT NULL THEN
64 SELECT 1 INTO l_count FROM MFG_LOOKUPS
65 WHERE LOOKUP_TYPE='INV_TRANSACTION_FLOW_QUALIFIER'
66 AND LOOKUP_CODE=p_qualifier_code;
67 RETURN TRUE;
68 ELSE
69 RETURN TRUE;
70 END IF;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 RETURN FALSE;
74 END;
75
76 /*=======================================================================================================*/
77
78 FUNCTION Validate_Qualifier_Value(
79 P_QUALIFIER_CODE IN NUMBER,
80 P_QUALIFIER_VALUE_ID IN NUMBER,
81 P_FLOW_TYPE IN NUMBER
82 )RETURN BOOLEAN IS
83 l_count NUMBER := 0;
84 BEGIN
85 IF p_flow_type IS NOT NULL THEN
86 IF p_qualifier_code IS NOT NULL THEN
87 IF p_qualifier_value_id IS NOT NULL THEN
88 IF p_qualifier_code=1 THEN -- Item Categories
89
90 SELECT 1 INTO l_count FROM DUAL
91 WHERE P_QUALIFIER_VALUE_ID IN(
92 SELECT mcv.category_id
93 FROM
94 mtl_categories_v mcv,
95 MTL_CATEGORY_SET_VALID_CATS MCSVC,
96 MTL_CATEGORY_SETS_b mcs
97 WHERE
98 mcs.category_set_id = decode(p_flow_type,1,1,2)
99 and mcs.structure_id = mcv.structure_id
100 and mcs.category_set_id = MCSVC.category_set_id
101 and MCSVC.category_id = mcv.category_id
102 UNION
103 SELECT mcv.category_id
104 FROM mtl_categories_v mcv,
105 MTL_CATEGORY_SETS_b mcs
106 WHERE
107 mcs.category_set_id = decode(p_flow_type,1,1,2)
108 and mcs.structure_id = mcv.structure_id
109 and mcs.default_category_id = mcv.category_id
110 );
111
112 END IF;--p_qualifier_code=1
113
114 IF l_count=1 THEN
115 RETURN TRUE;
116 ELSE
117 RETURN FALSE;
118 END IF;
119
120 ELSE--p_qualifier_value_id IS NULL
121 RETURN FALSE;--When qualifier code is null , qualifier value id cannot be null
122 END IF;--p_qualifier_value_id IS NOT NULL
123
124
125
126 END IF;--p_qualifier_code IS NOT NULL
127 END IF;--p_flow_type IS NOT NULL
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 RETURN FALSE;
131 END;
132
133 /*=======================================================================================================*/
134
135 FUNCTION Validate_New_Accounting_Flag(
136 P_START_ORG_ID IN NUMBER,
137 P_END_ORG_ID IN NUMBER,
138 P_FLOW_TYPE IN NUMBER,
139 P_NEW_ACCOUNTING_FLAG IN VARCHAR2,
140 P_NUM_LINES IN VARCHAR2
141 )RETURN BOOLEAN IS
142 BEGIN
143 -- first validate for single OU flow
144 IF p_start_org_id=p_end_org_id THEN
145 IF p_new_accounting_flag IS NULL OR p_new_accounting_flag='N' THEN
146 RETURN FALSE;
147 ELSE
148 RETURN TRUE;
149 END IF;
150 END IF;
151 -- validate for multi ou setup
152 IF P_FLOW_TYPE=1 THEN -- Shipping
153 IF P_NUM_LINES = 1 THEN
154 RETURN TRUE;
155 ELSIF P_NUM_LINES>1 THEN
156 IF p_new_accounting_flag IS NULL OR p_new_accounting_flag='N' THEN
157 RETURN FALSE;
158 ELSE
159 RETURN TRUE;
160 END IF;
161 ELSIF P_NUM_LINES =0 THEN
162 IF p_new_accounting_flag IS NULL OR p_new_accounting_flag='Y' THEN
163 RETURN FALSE;
164 ELSE
165 RETURN TRUE;
166 END IF;
167 END IF;
168 ELSIF P_FLOW_TYPE=2 THEN -- Procuring
169 IF p_new_accounting_flag IS NULL OR p_new_accounting_flag='N' THEN
170 RETURN FALSE;
171 ELSE
172 RETURN TRUE;
173 END IF;
174 END IF;
175
176 END Validate_New_Accounting_Flag;
177
178 /*=======================================================================================================*/
179 --This function checks if a transaction flow with same attribute
180 --already exists or not
181
182 /*FUNCTION Validate_Header(
183 P_HEADER_ID IN NUMBER,
184 P_START_ORG_ID IN NUMBER,
185 P_END_ORG_ID IN NUMBER,
186 P_FLOW_TYPE IN NUMBER,
187 P_ORGANIZATION_ID IN NUMBER,
188 P_QUALIFIER_CODE IN NUMBER,
189 P_QUALIFIER_VALUE_ID IN NUMBER,
190 P_START_DATE IN DATE,
191 P_END_DATE IN DATE
192 ) RETURN BOOLEAN IS
193
194 l_count NUMBER :=0;
195 BEGIN
196 -- check duplicate
197 BEGIN
198
199 SELECT 1 INTO l_count FROM DUAL
200 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
201 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
202 AND START_ORG_ID=P_START_ORG_ID
203 AND END_ORG_ID=P_END_ORG_ID
204 AND FLOW_TYPE=P_FLOW_TYPE
205 --AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
206 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
207 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
208 AND START_DATE=P_START_DATE
209 AND NVL(END_DATE,SYSDATE)=NVL(P_END_DATE,SYSDATE));
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 -- No duplicate transaction flow exists
213 RETURN TRUE;
214 END;
215 -- if control coming to this place then duplicate transaction flow exists
216 FND_MESSAGE.SET_NAME('INV','INV_DUPLICATE_TRX_FLOW');
217 FND_MSG_PUB.ADD;
218 RETURN FALSE;
219 END Validate_Header;*/
220
221 /*=======================================================================================================*/
222
223 --This procedure validates the start date
224
225 FUNCTION Validate_Start_Date(
226 P_HEADER_ID IN NUMBER,
227 P_START_ORG_ID IN NUMBER,
228 P_END_ORG_ID IN NUMBER,
229 P_FLOW_TYPE IN NUMBER,
230 P_ORGANIZATION_ID IN NUMBER,
231 P_QUALIFIER_CODE IN NUMBER,
232 P_QUALIFIER_VALUE_ID IN NUMBER,
233 P_START_DATE IN DATE,
234 P_REF_DATE IN DATE
235 ) return BOOLEAN IS
236 l_count NUMBER:=0;
237 BEGIN
238 IF g_debug=1 THEN
239 debug('The value of p_start_date '||to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS'),'Validate_Start_Date');
240 debug('The value of p_ref_date '||to_char(p_ref_date,'DD-MON-YYYY HH24:MI:SS'),'Validate_Start_Date');
241 END IF;
242 -- start date should not be less then the sysdate (passed in ref date)
243 IF p_start_date < p_ref_date THEN
244 IF g_debug=1 THEN
245 debug('p_start_date is less than p_ref_date','Validate_Start_Date');
246 END IF;
247 FND_MESSAGE.SET_NAME('INV','INV_INVALID_START_DATE');
248 FND_MSG_PUB.ADD;
249 RETURN FALSE;
250 END IF;
251 /*
252 -- start date should not fall between any other transaction
253 --flow's start and end date with same attributes
254 --overlap check
255 BEGIN
256 -- check if a transaction flow with start date < p_start_date
257 SELECT 1 INTO l_count FROM DUAL
258 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
259 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
260 AND START_ORG_ID=P_START_ORG_ID
261 AND END_ORG_ID=P_END_ORG_ID
262 AND FLOW_TYPE=P_FLOW_TYPE
263 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
264 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
265 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
266 AND START_DATE<=P_START_DATE);
267 -- there is possiblity of overlaping
268 -- proceed to check overlaps
269 IF g_debug=1 THEN
270 debug(' Going to check overlaps','Validate_Start_Date');
271 END IF;
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 -- overlap not possible for p_start_date
275 IF g_debug=1 THEN
276 debug('No overlapping transaction flow exists','Validate_Start_Date');
277 END IF;
278 RETURN TRUE; -- no overlaping transaction flow exists
279 END;
280 --
281 BEGIN
282 --check if a transaction flow with null end date and
283 --start date < p_start_date exists
284 SELECT 1 INTO l_count FROM DUAL
285 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
286 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
287 AND START_ORG_ID=P_START_ORG_ID
288 AND END_ORG_ID=P_END_ORG_ID
289 AND FLOW_TYPE=P_FLOW_TYPE
290 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
291 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
292 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
293 AND START_DATE<=P_START_DATE
294 AND END_DATE IS NULL);
295 -- overlap found
296 --
297 IF g_debug=1 THEN
298 debug('Overlap found for start_date<p_start_date and end_date = null','Validate_Start_Date');
299 END IF;
300 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
301 FND_MSG_PUB.ADD;
302 RETURN FALSE;
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 IF g_debug=1 THEN
306 debug('Finding overlap for end_date not null','Validate_Start_Date');
307 END IF;
308 BEGIN
309 -- check if a transaction flow with not null end date
310 --and start date < p_start_date exists
311 SELECT 1 INTO l_count FROM DUAL
312 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
313 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
314 AND START_ORG_ID=P_START_ORG_ID
315 AND END_ORG_ID=P_END_ORG_ID
316 AND FLOW_TYPE=P_FLOW_TYPE
317 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
318 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
319 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
320 AND START_DATE<=P_START_DATE
321 AND END_DATE>=P_START_DATE );
322 -- overlap found
323 IF g_debug=1 THEN
324 debug('Overlap found for start_date<p_start_date and end_date>p_start_date','Validate_Start_Date');
325 END IF;
326 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
327 FND_MSG_PUB.ADD;
328 RETURN FALSE;
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 -- no overlaping transaction flow exists
332 IF g_debug=1 THEN
333 debug('No Overlap exists','Validate_Start_Date');
334 END IF;
335 RETURN TRUE;
336
337 END;
338 END;
339 */
340 BEGIN
341 SELECT 1 INTO l_count FROM DUAL
342 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
343 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
344 AND START_ORG_ID=P_START_ORG_ID
345 AND END_ORG_ID=P_END_ORG_ID
346 AND FLOW_TYPE=P_FLOW_TYPE
347 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
348 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
349 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
350 AND START_DATE<=P_START_DATE
351 AND NVL(END_DATE,g_miss_date)>=P_START_DATE );
352 IF g_debug=1 THEN
353 debug('Overlap found for start date','Validate_Start_Date');
354 END IF;
355 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
356 FND_MESSAGE.SET_TOKEN('START_END','START_DATE_CAP',TRUE);
357 FND_MSG_PUB.ADD;
358 RETURN FALSE;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 RETURN TRUE;
362 END;
363
364 END Validate_Start_Date;
365
366 /*=======================================================================================================*/
367
368 FUNCTION Validate_End_Date(
369 P_HEADER_ID IN NUMBER,
370 P_START_ORG_ID IN NUMBER,
371 P_END_ORG_ID IN NUMBER,
372 P_FLOW_TYPE IN NUMBER,
373 P_ORGANIZATION_ID IN NUMBER,
374 P_QUALIFIER_CODE IN NUMBER,
375 P_QUALIFIER_VALUE_ID IN NUMBER,
376 P_START_DATE IN DATE,
377 P_END_DATE IN DATE,
378 P_REF_DATE IN DATE
379 ) RETURN BOOLEAN IS
380 l_count NUMBER:=0;
381 BEGIN
382 IF g_debug=1 THEN
383 debug('The value of p_start_date '||to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS'),'Validate_End_Date');
384 debug('The value of p_ref_date '||to_char(p_ref_date,'DD-MON-YYYY HH24:MI:SS'),'Validate_End_Date');
385 END IF;
386 -- end date should be >= p_ref_date
387 IF p_end_date<p_ref_date THEN
388 IF g_debug=1 THEN
389 debug('I am in if when the end date is < p_ref_date','Validate_End_Date');
390 END IF;
391 FND_MESSAGE.SET_NAME('INV','INV_NOT_CUR_END_DATE');
392 FND_MSG_PUB.ADD;
393 RETURN FALSE;
394 END IF;
395 -- end date should not be <= start date
396 IF p_end_date <=p_start_date THEN
397 IF g_debug=1 THEN
398 debug('p_end_date is <= p_ref_date','Validate_End_Date');
399 END IF;
400 FND_MESSAGE.SET_NAME('INV','INV_END_DATE_INVALID');
401 FND_MSG_PUB.ADD;
402 RETURN FALSE;
403 END IF;
404
405 /*
406 --
407 -- end date should not fall between start and end date of any other transaction flow with same attributes
408 -- overlap check
409 IF p_end_date IS NULL THEN
410 -- All transaction flows with same attributes should have end date less then the start date of this transaction
411 BEGIN
412 SELECT 1 INTO l_count FROM DUAL
413 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
414 WHERE START_ORG_ID=P_START_ORG_ID
415 AND HEADER_ID <> NVL(P_HEADER_ID,-999)
416 AND END_ORG_ID=P_END_ORG_ID
417 AND FLOW_TYPE=P_FLOW_TYPE
418 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
419 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
420 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
421 AND END_DATE>=P_START_DATE);
422 IF g_debug=1 THEN
423 debug('Overlap found for end_date>=p_start_date and p_end_date is null','Validate_End_Date');
424 END IF;
425 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
426 FND_MSG_PUB.ADD;
427 RETURN FALSE;
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 -- no overlap for end_date>=p_start_date and p_end_date is null
431 IF g_debug=1 THEN
432 debug('No overlap for end_date>=p_start_date and p_end_date is null','Validate_End_Date');
433 END IF;
434 RETURN TRUE;
435 END;
436 ELSE -- p_end_date is NOT null
437 BEGIN
438 -- If a Inter-company Transaction Flow with same attributes and NULL End Date exists
439 -- Then new Inter-company Transaction Flow can only be
440 -- defined for End Date less then
441 -- the Start Date of existing Inter-company Transaction Flow
442 SELECT 1 INTO l_count FROM DUAL
443 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
444 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
445 AND START_ORG_ID=P_START_ORG_ID
446 AND END_ORG_ID=P_END_ORG_ID
447 AND FLOW_TYPE=P_FLOW_TYPE
448 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
449 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
450 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
451 AND START_DATE<=P_END_DATE
452 AND END_DATE IS NULL);
453 -- overlap found
454 IF g_debug=1 THEN
455 debug('Overlap found for start_date<=p_end_date and end_date is null','Validate_End_Date');
456 END IF;
457 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
458 FND_MSG_PUB.ADD;
459 RETURN FALSE;
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 BEGIN
463 -- End Date should be less than the Start Date of any other Inter-company
464 -- Transaction Flow with same attributes and Start Date greater than the
465 -- Start Date of current Inter-company Transaction Flow
466 SELECT 1 INTO l_count FROM DUAL
467 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
468 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
469 AND START_ORG_ID=P_START_ORG_ID
470 AND END_ORG_ID=P_END_ORG_ID
471 AND FLOW_TYPE=P_FLOW_TYPE
472 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
473 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
474 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
475 AND START_DATE<=P_END_DATE
476 AND START_DATE>=P_START_DATE
477 AND END_DATE IS NOT NULL);
478 -- overlap found
479 IF g_debug=1 THEN
480 debug('Overlap found for start_date<=p_end_date and start_date>=p_start_date','Validate_End_Date');
481 END IF;
482 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
483 FND_MSG_PUB.ADD;
484 RETURN FALSE;
485 EXCEPTION
486 WHEN NO_DATA_FOUND THEN
487 IF g_debug=1 THEN
488 debug('No overlap found','Validate_End_Date');
489 END IF;
490 RETURN TRUE;
491 END;
492 END;
493 -- overlap exists
494 END IF;--p_end_date
495
496 */
497 BEGIN
498 SELECT 1 INTO l_count FROM DUAL
499 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
500 WHERE HEADER_ID <> NVL(P_HEADER_ID,-999)
501 AND START_ORG_ID=P_START_ORG_ID
502 AND END_ORG_ID=P_END_ORG_ID
503 AND FLOW_TYPE=P_FLOW_TYPE
504 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
505 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
506 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
507 AND START_DATE>=P_START_DATE
508 AND START_DATE<=NVL(P_END_DATE,G_MISS_DATE)
509 );
510 -- overlap found
511 IF g_debug=1 THEN
512 debug('Overlap found end date','Validate_End_Date');
513 END IF;
514 FND_MESSAGE.SET_NAME('INV','INV_OVERLAPING_TRX_FLOW');
515 FND_MESSAGE.SET_TOKEN('START_END','END_DATE_CAP',TRUE);
516 FND_MSG_PUB.ADD;
517 RETURN FALSE;
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 IF g_debug=1 THEN
521 debug('No overlap found','Validate_End_Date');
522 END IF;
523 RETURN TRUE;
524 END;
525
526 END Validate_End_Date;
527
528 /*=======================================================================================================*/
529
530 PROCEDURE Gap_Exists(
531 X_START_DATE OUT NOCOPY DATE,
532 X_END_DATE OUT NOCOPY DATE,
533 X_REF_DATE OUT NOCOPY DATE,
534 X_GAP_EXISTS OUT NOCOPY BOOLEAN,
535 X_RETURN_STATUS OUT NOCOPY NUMBER,
536 P_START_ORG_ID IN NUMBER,
537 P_END_ORG_ID IN NUMBER,
538 P_FLOW_TYPE IN NUMBER,
539 P_ORGANIZATION_ID IN NUMBER,
540 P_QUALIFIER_CODE IN NUMBER,
541 P_QUALIFIER_VALUE_ID IN NUMBER
542 )IS
543 CURSOR DATES(p_sysdate DATE) IS
544 SELECT START_DATE,END_DATE
545 FROM MTL_TRANSACTION_FLOW_HEADERS
546 WHERE START_ORG_ID=P_START_ORG_ID
547 AND END_ORG_ID=P_END_ORG_ID
548 AND FLOW_TYPE=P_FLOW_TYPE
549 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
550 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
551 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
552 AND ( END_DATE>=p_sysdate OR END_DATE IS NULL )
553 ORDER BY START_DATE;
554 --
555 l_temp_date DATE;
556 l_diff NUMBER;
557 l_count NUMBER:=0;
558 l_start_date DATE;
559 l_end_date DATE;
560 l_sysdate DATE:=sysdate;
561
562 --
563 BEGIN
564
565 x_gap_exists:=FALSE;
566 -- get trx flow with min start date and end date > sysdate or null
567 BEGIN
568 SELECT MIN(START_DATE)
569 INTO l_start_date
570 FROM MTL_TRANSACTION_FLOW_HEADERS
571 WHERE START_ORG_ID=P_START_ORG_ID
572 AND END_ORG_ID=P_END_ORG_ID
573 AND FLOW_TYPE=P_FLOW_TYPE
574 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
575 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
576 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
577 AND (END_DATE > l_sysdate OR END_DATE IS NULL);
578
579 IF l_start_date>=l_sysdate THEN
580 -- gap exists at present date
581 x_gap_exists:=TRUE;
582 x_ref_date:=l_sysdate;
583 x_start_date:=l_sysdate-(1/(24*60*60)); -- 1 sec is added in the called program
584 x_end_date:=l_start_date;
585 IF g_debug=1 THEN
586 debug('The value of x_start_date is'||x_start_date,'Gap_Exists');
587 debug('The value of x_end_date is'||x_end_date,'Gap_Exists');
588 debug('The value of x_ref_date is'||x_ref_date,'Gap_Exists');
589 END IF;
590 ELSIF l_start_date<l_sysdate THEN
591 -- need to search for gap
592 OPEN dates(l_sysdate);
593 FETCH dates INTO l_start_date,l_temp_date;
594
595 IF g_debug=1 THEN
596 debug('The value of l_start_date in first fetch is'||l_start_date,'Gap_Exists');
597 debug('The value of l_temp_date in first fetch is'||l_temp_date,'Gap_Exists');
598 END IF;
599
600 IF l_temp_date IS NOT NULL THEN
601 -- if end_date of first record is null then gap can not exist for future
602 -- The case of current gap is already handeled in previous block
603 --
604 LOOP
605 FETCH dates INTO l_start_date,l_end_date;
606 EXIT WHEN dates%NOTFOUND OR l_count=1;
607 IF g_debug=1 THEN
608 debug('The value of l_start_date in second fetch is'||l_start_date,'Gap_Exists');
609 debug('The value of l_end_date in second fetch is'||l_end_date,'Gap_Exists');
610 END IF;
611 -- get the difference in seconds between end date of previous transaction flow
612 -- and start date of next trx flow
613 l_diff := trunc(l_start_date-l_temp_date,10)*(24*60*60); -- convert to seconds
614 IF g_debug=1 THEN
615 debug('The value of diff is'||l_diff,'Gap_Exists');
616 END IF;
617 IF l_diff >1 THEN
618 IF g_debug=1 THEN
619 debug('I am in if','Gap_Exists');
620 END IF;
621 x_gap_exists:=TRUE;
622 x_start_date:=l_temp_date;
623 x_end_date:=l_start_date;
624 x_ref_date:=l_sysdate; -- should be latest sysdate
625 l_count:=1;
626 ELSE
627 l_temp_date:=l_end_date;
628 IF g_debug=1 THEN
629 debug('I am in else l_end_date =' ||l_end_date,'Gap_Exists');
630 END IF;
631 END IF;
632 IF l_end_date IS NULL THEN
633 IF g_debug=1 THEN
634 debug('I am here if future end date is null','Gap_Exists');
635 END IF;
636 -- if end_date of any record is null no more record can exists
637 x_return_status:=1;
638 EXIT;
639 END IF;
640 END LOOP;--dates
641 END IF;--l_temp_date is not null
642 CLOSE dates;
643 END IF;
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 -- This case is not possible
647 IF g_debug=1 THEN
648 debug('In no data found'||l_start_date,'Gap_Exists');
649 END IF;
650 NULL;
651
652 END;
653 EXCEPTION
654 WHEN OTHERS THEN
655 IF g_debug=1 THEN
656 debug('When others '||sqlerrm,'Gap_Exists');
657 END IF;
658
659 END Gap_Exists;
660
661
662 /*=======================================================================================================*/
663
664 PROCEDURE Get_Default_Dates(
665 X_START_DATE OUT NOCOPY DATE,
666 X_END_DATE OUT NOCOPY DATE,
667 X_REF_DATE OUT NOCOPY DATE,
668 X_RETURN_CODE OUT NOCOPY NUMBER,
669 P_START_ORG_ID IN NUMBER,
670 P_END_ORG_ID IN NUMBER,
671 P_FLOW_TYPE IN NUMBER,
672 P_ORGANIZATION_ID IN NUMBER,
673 P_QUALIFIER_CODE IN NUMBER,
674 P_QUALIFIER_VALUE_ID IN NUMBER
675 )IS
676 --
677 l_count NUMBER:=0;
678 l_start_date DATE;
679 l_end_date DATE;
680 l_ref_date DATE;
681 l_gap_exists BOOLEAN:=FALSE;
682 l_return_status NUMBER:=0;
683
684 /*
685 L_RETURN_CODE 0=> ERROR
686 1=> NO_TRX - START_DATE=SYSDATE, END_DATE=NULL
687 2=> GAP - START DATE= END DATE, END_DATE= NEXT START DATE
688 3=> NO GAP - START_DATE=MAX END DATE, END DATE= NULL
689 */
690 BEGIN
691
692 IF g_debug=1 THEN
693 debug('Inside Get_Default_Dates','Get_Default_Dates');
694 END IF;
695 x_return_code:=0;
696 -- If a transaction flow with NULL end date exists then no other
697 --transaction flow can be created
698 BEGIN
699 SELECT 1 INTO l_count FROM DUAL
700 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
701 WHERE START_ORG_ID=P_START_ORG_ID
702 AND END_ORG_ID=P_END_ORG_ID
703 AND FLOW_TYPE=P_FLOW_TYPE
704 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
705 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
706 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
707 AND START_DATE<=SYSDATE
708 AND END_DATE IS NULL);
709 x_return_code:=0;
710 IF g_debug=1 THEN
711 debug('Null end date case','Get_Default_Dates');
712 END IF;
713 FND_MESSAGE.SET_NAME('INV','INV_NULL_END_DATE');
714 FND_MSG_PUB.ADD;
715 RETURN;
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 NULL;
719 -- proceed to next section
720 END;
721
722
723 -- if no trx flow with same attributes and end_date greater than sysdate or null exists
724 -- then default the start date to sysdate
725 BEGIN
726 SELECT 1 INTO l_count FROM DUAL
727 WHERE EXISTS (SELECT HEADER_ID FROM MTL_TRANSACTION_FLOW_HEADERS
728 WHERE START_ORG_ID=P_START_ORG_ID
729 AND END_ORG_ID=P_END_ORG_ID
730 AND FLOW_TYPE=P_FLOW_TYPE
731 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
732 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
733 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
734 AND (END_DATE > SYSDATE OR END_DATE IS NULL));
735 -- if a record is found then need to find the first gap
736 -- proceed to next block
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 -- return sysdate as default start date and set p_ref_date to start date
740 IF g_debug=1 THEN
741 debug('No present or future trx flow found','Get_Default_Dates');
742 END IF;
743 X_RETURN_CODE:=1;
744 X_START_DATE:=SYSDATE;
745 X_REF_DATE:=X_START_DATE;
746 IF g_debug=1 THEN
747 debug('X_START_DATE = '||to_char(X_START_DATE,'DD-MON-YYY HH24:MI:SS'),'Get_Default_Dates');
748 debug('X_REF_DATE = '||to_char(X_REF_DATE,'DD-MON-YYY HH24:MI:SS'),'Get_Default_Dates');
749 END IF;
750 RETURN; -- no further processing required
751 END;
752
753 -- some transaction flow are existing
754 -- find the first gap and set the start and end dates
755
756 IF g_debug=1 THEN
757 debug('Calling gap exists','Get_Default_Dates');
758 END IF;
759
760 Gap_Exists(
761 X_START_DATE =>l_start_date,
762 X_END_DATE =>l_end_date,
763 X_REF_DATE =>l_ref_date,
764 X_GAP_EXISTS =>l_gap_exists,
765 X_RETURN_STATUS =>l_return_status,
766 P_START_ORG_ID =>p_start_org_id,
767 P_END_ORG_ID =>p_end_org_id,
768 P_FLOW_TYPE =>p_flow_type,
769 P_ORGANIZATION_ID =>p_organization_id,
770 P_QUALIFIER_CODE =>p_qualifier_code,
771 P_QUALIFIER_VALUE_ID =>p_qualifier_value_id
772 );
773 --A future trxn with null end date exists
774 IF g_debug=1 THEN
775 debug('The value of l_return_status is'||l_return_status,'Get_Default_Dates');
776 END IF;
777 IF l_return_status=1 THEN
778 x_return_code:=0;
779 FND_MESSAGE.SET_NAME('INV','INV_NULL_END_DATE');
780 FND_MSG_PUB.ADD;
781 RETURN;
782 END IF;--no further processing to be done
783 IF g_debug=1 THEN
784 debug('The value of l_start_date is'||l_start_date,'Get_Default_Dates');
785 debug('The value of l_end_date is'||l_end_date,'Get_Default_Dates');
786 END IF;
787
788 IF l_gap_exists THEN
789 x_start_date:=l_start_date;
790 x_end_date:=l_end_date;
791 x_ref_date:=l_ref_date;
792 x_return_code:=2;
793 IF g_debug=1 THEN
794 debug('gap exists is true','Get_Default_Dates');
795 debug('Out parameter set from get default dates'||l_count,'Get_Default_Dates');
796 debug('The value of x_start_date in is'||to_char(x_start_date,'DD-MON-YYYY HH24:MI:SS'),'Get_Default_Dates');
797 debug('The value of x_end_date is'||to_char(x_end_date,'DD-MON-YYYY HH24:MI:SS'),'Get_Default_Dates');
798 debug('The value of x_ref_date is'||to_char(x_ref_date,'DD-MON-YYYY HH24:MI:SS'),'Get_Default_Dates');
799 END IF;
800 ELSE
801 -- set the start date to max of end date
802 debug('gap exists is false','Get_Default_Dates');
803 BEGIN
804 SELECT MAX(END_DATE) INTO L_START_DATE FROM MTL_TRANSACTION_FLOW_HEADERS
805 WHERE START_ORG_ID=P_START_ORG_ID
806 AND END_ORG_ID=P_END_ORG_ID
807 AND FLOW_TYPE=P_FLOW_TYPE
808 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
809 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
810 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
811 AND END_DATE>SYSDATE;
812 x_return_code:=3;
813 x_start_date:=l_start_date+(1/(24*60*60));
814 x_ref_date:=x_start_date;
815 EXCEPTION
816 WHEN NO_DATA_FOUND THEN
817 NULL;
818 END;
819 END IF;
820 debug('Returning from get default dates '||l_count,'Get_Default_Dates');
821 END Get_Default_Dates;
822
823 /*=======================================================================================================*/
824
825 /**
826 * This function will return TRUE if a gap will be created because of the current transaction else false
827 */
828
829 FUNCTION New_Gap_Created(
830 P_START_ORG_ID IN NUMBER,
831 P_END_ORG_ID IN NUMBER,
832 P_FLOW_TYPE IN NUMBER,
833 P_ORGANIZATION_ID IN NUMBER,
834 P_QUALIFIER_CODE IN NUMBER,
835 P_QUALIFIER_VALUE_ID IN NUMBER,
836 P_START_DATE IN DATE,
837 P_END_DATE IN DATE,
838 P_REF_DATE IN DATE
839 ) RETURN BOOLEAN IS
840 l_count NUMBER:=0;
841 BEGIN
842 IF g_debug=1 THEN
843 debug('The value of p_start_date in is'||to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS'),'New_Gap_Created');
844 debug('The value of p_end_date is'||to_char(p_end_date,'DD-MON-YYYY HH24:MI:SS'),'New_Gap_Created');
845 debug('The value of p_ref_date is'||to_char(p_ref_date,'DD-MON-YYYY HH24:MI:SS'),'New_Gap_Created');
846 END IF;
847 IF p_end_date IS NULL THEN -- if end date is null then gap will exist only at start
848 IF p_start_date=p_ref_date THEN
849 -- no gap created
850 RETURN FALSE;
851 ELSIF p_start_date>p_ref_date THEN
852 -- a transaction flow with end date = start_date-1second should exists
853 BEGIN
854 SELECT 1 INTO l_count FROM MTL_TRANSACTION_FLOW_HEADERS
855 WHERE START_ORG_ID=P_START_ORG_ID
856 AND END_ORG_ID=P_END_ORG_ID
857 AND FLOW_TYPE=P_FLOW_TYPE
858 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
859 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
860 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
861 AND END_DATE=P_START_DATE-(1/(24*60*60));
862 -- no gap created
863 RETURN FALSE;
864 EXCEPTION
865 WHEN NO_DATA_FOUND THEN
866 -- gap created
867 FND_MESSAGE.SET_NAME('INV','INV_GAP_CREATED');
868 FND_MSG_PUB.ADD;
869 if g_debug=1 then
870 debug('Gap created for condition p_start_date>p_ref_date','New_Gap_Created');
871 end if;
872 RETURN TRUE;
873
874 END;
875 ELSE -- only possible with update
876 RETURN FALSE;
877 END IF;
878 ELSE -- gap may exists at end also
879 IF p_start_date>p_ref_date THEN
880 BEGIN
881 -- check gap for start date
882 SELECT 1 INTO l_count FROM MTL_TRANSACTION_FLOW_HEADERS
883 WHERE START_ORG_ID=P_START_ORG_ID
884 AND END_ORG_ID=P_END_ORG_ID
885 AND FLOW_TYPE=P_FLOW_TYPE
886 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
887 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
888 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-999)
889 AND END_DATE=P_START_DATE-(1/(24*60*60));
890 EXCEPTION
891 WHEN NO_DATA_FOUND THEN
892 -- gap created
893 FND_MESSAGE.SET_NAME('INV','INV_GAP_CREATED');
894 FND_MSG_PUB.ADD;
895 if g_debug=1 then
896 debug('Gap created for start date when p_start_date>p_ref_date','New_Gap_Created');
897 end if;
898 RETURN TRUE;
899 END;
900 END IF;
901
902 -- check gap for end date
903 BEGIN
904 SELECT 1 INTO l_count FROM MTL_TRANSACTION_FLOW_HEADERS
905 WHERE START_ORG_ID=P_START_ORG_ID
906 AND END_ORG_ID=P_END_ORG_ID
907 AND FLOW_TYPE=P_FLOW_TYPE
908 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
909 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
910 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-9)
911 AND START_DATE>P_END_DATE+(1/(24*60*60))
912 AND ROWNUM=1; -- multiple records are possible
913 -- a gap for end date can occr only if above sql will return a value
914 if g_debug=1 then
915 debug('Condition for gap for end gate satisfied','New_Gap_Created');
916 end if;
917 EXCEPTION
918 WHEN NO_DATA_FOUND THEN
919 -- no gap can exists
920 RETURN FALSE;
921 END;
922 -- if control is coming to this place means that a transaction flow
923 -- with start date greater the end date of current trx flow exists
924 -- we need to check for gap due to end date
925 BEGIN
926 SELECT 1 INTO l_count FROM MTL_TRANSACTION_FLOW_HEADERS
927 WHERE START_ORG_ID=P_START_ORG_ID
928 AND END_ORG_ID=P_END_ORG_ID
929 AND FLOW_TYPE=P_FLOW_TYPE
930 AND NVL(ORGANIZATION_ID,-999)=NVL(P_ORGANIZATION_ID,-999)
931 AND NVL(QUALIFIER_CODE,-999)=NVL(P_QUALIFIER_CODE,-999)
932 AND NVL(QUALIFIER_VALUE_ID,-999)=NVL(P_QUALIFIER_VALUE_ID,-9)
933 AND START_DATE=P_END_DATE+(1/(24*60*60));
934 -- all validation passed
935 -- no gap created
936 RETURN FALSE;
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN
939 -- gap created
940 FND_MESSAGE.SET_NAME('INV','INV_GAP_CREATED');
941 FND_MSG_PUB.ADD;
942 if g_debug=1 then
943 debug('Gap created for condition p_end_date > start_date+1sec','New_Gap_Created');
944 end if;
945 RETURN TRUE;
946 END;
947
948 END IF;
949 END New_Gap_Created;
950
951 /*=======================================================================================================*/
952
953
954 FUNCTION Validate_Inv_Organization_Type(
955 P_FLOW_TYPE IN NUMBER,
956 P_ORGANIZATION_IDS IN TABLE_OF_NUMBERS,
957 P_NEW_ACCOUNTING_FLAG IN VARCHAR2
958 ) RETURN BOOLEAN IS
959
960 l_count NUMBER :=0;
961 l_count_disc NUMBER:=0;
962
963 BEGIN
964 -- VALIDATE EACH ORG
965 FOR l_index IN 1..P_ORGANIZATION_IDS.COUNT
966 LOOP
967 IF p_flow_type=1 THEN -- shipping
968 IF p_new_accounting_flag='Y' THEN
969 -- All orgs should be non process
970 /* ANTHIYAG Bug#5460153 14-Aug-2006 Start */
971 /*
972 BEGIN
973 SELECT 1 INTO l_count FROM MTL_PARAMETERS
974 WHERE ORGANIZATION_ID=P_ORGANIZATION_IDS(l_index)
975 AND PROCESS_ENABLED_FLAG='Y';
976 -- if a record is found then its a failure condition
977 -- process enabled orgs are not supported for global procuring transaction
978 FND_MESSAGE.SET_NAME('INV','INV_PROCESS_ORG_NOT_ALLOWED');
979 FND_MSG_PUB.ADD;
980 RETURN FALSE;
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 NULL;
984 -- success condition
985 END;
986 */
987 NULL;
988 /* ANTHIYAG Bug#5460153 14-Aug-2006 End */
989 ELSE
990 -- Old accounting All orgs should be either process or discrete
991 BEGIN
992 -- get process org
993 SELECT 1 INTO l_count FROM MTL_PARAMETERS
994 WHERE ORGANIZATION_ID=P_ORGANIZATION_IDS(l_index)
995 AND PROCESS_ENABLED_FLAG='Y';
996 -- get discrete org
997 SELECT 1 INTO l_count_disc FROM MTL_PARAMETERS
998 WHERE ORGANIZATION_ID=P_ORGANIZATION_IDS(l_index)
999 AND PROCESS_ENABLED_FLAG<>'Y';
1000 IF l_count=1 and l_count_disc=1 THEN
1001 -- failure condition
1002 -- both falgs can be 1 only if some orgs are process
1003 -- and some orgs are discrete
1004 FND_MESSAGE.SET_NAME('INV','INV_MIXED_ORG_NOT_ALLOWED');--ACTION
1005 FND_MSG_PUB.ADD;
1006 RETURN FALSE;
1007 END IF;
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 NULL;
1011 END;
1012 END IF;
1013 ELSIF p_flow_type=2 THEN-- procuring
1014 /** INVCONV remove the check for process org
1015 BEGIN
1016 SELECT 1 INTO l_count FROM MTL_PARAMETERS
1017 WHERE ORGANIZATION_ID=P_ORGANIZATION_IDS(l_index)
1018 AND PROCESS_ENABLED_FLAG='Y';
1019 -- if a record is found then its a failure condition
1020 -- process enabled orgs are not supported for global procuring transaction
1021 FND_MESSAGE.SET_NAME('INV','INV_PROCESS_ORG_DISALLOWED');--ACTION
1022 FND_MSG_PUB.ADD;
1023 RETURN FALSE;
1024 EXCEPTION
1025 WHEN NO_DATA_FOUND THEN
1026 NULL;
1027 -- success condition
1028 END;
1029 **/
1030 null ;
1031 END IF;-- flow type
1032 END LOOP;
1033 RETURN TRUE;
1034
1035 END Validate_Inv_Organization_Type;
1036
1037 /*=======================================================================================================*/
1038 PROCEDURE Create_Sorted_Table(
1039 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1040 X_SORTED_TABLE OUT NOCOPY TRX_FLOW_LINES_TAB,
1041 P_START_ORG_ID IN NUMBER,
1042 P_END_ORG_ID IN NUMBER,
1043 P_LINES_TABLE IN TRX_FLOW_LINES_TAB
1044 ) IS
1045 l_out_table TRX_FLOW_LINES_TAB;
1046 l_num_recs NUMBER:=p_lines_table.count;
1047 l_line_rec TRX_FLOW_LINE_REC;
1048 BEGIN
1049 IF g_debug=1 THEN
1050 debug('l_num_recs= '||l_num_recs,'Create_Sorted_Table');
1051 END IF;
1052 IF l_num_recs=0 THEN
1053 FND_MESSAGE.SET_NAME('INV','INV_NO_TRX_FLOW_LINE');
1054 FND_MSG_PUB.ADD;
1055 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1056 RETURN ;
1057 END IF;
1058 --clear cache
1059 IF l_out_table.count>0 then
1060 l_out_table.delete;
1061 END IF;
1062 -- get the first record
1063 IF l_num_recs=1 THEN
1064 IF(p_lines_table(1).to_org_id <> p_end_org_id)then
1065 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1066 FND_MESSAGE.SET_NAME('INV','INV_NO_START_NODE');
1067 FND_MSG_PUB.ADD;
1068 RETURN;
1069 ELSE
1070 x_sorted_table:=p_lines_table;
1071 X_RETURN_STATUS:=FND_API.G_RET_STS_SUCCESS;
1072 IF g_debug=1 THEN
1073 debug('Trx flow has only one node'||l_num_recs,'Create_Sorted_Table');
1074 END IF;
1075 END IF;
1076 RETURN;
1077 END IF;
1078 IF l_num_recs>1 THEN
1079 FOR l_index IN 1..l_num_recs
1080 LOOP -- search for record with from_org_id=start_org_id
1081 IF p_lines_table(l_index).from_org_id=p_start_org_id THEN
1082 l_out_table(1):=p_lines_table(l_index);
1083 EXIT;
1084 END IF;
1085 END LOOP;
1086 IF l_out_table.count=0 THEN
1087 -- start node not found
1088 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1089 FND_MESSAGE.SET_NAME('INV','INV_NO_START_NODE');
1090 FND_MSG_PUB.ADD;
1091 RETURN;
1092 END IF;
1093 IF g_debug=1 THEN
1094 debug('Start node found'||l_num_recs,'Create_Sorted_Table');
1095 END IF;
1096 END IF;--l_num_recs>1
1097 -- get the last record
1098 IF l_num_recs>1 THEN
1099 FOR l_index IN 1..l_num_recs
1100 LOOP -- search for record with to_org_id=end_org_id
1101 IF p_lines_table(l_index).to_org_id=p_end_org_id THEN
1102 l_out_table(l_num_recs):=p_lines_table(l_index);
1103 EXIT;
1104 END IF;
1105 END LOOP;
1106 IF l_out_table.count <> 2 THEN
1107 -- end node not found
1108 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1109 FND_MESSAGE.SET_NAME('INV','INV_INCOMPLETE_FLOW');
1110 FND_MSG_PUB.ADD;
1111 RETURN;
1112 END IF;
1113 IF g_debug=1 THEN
1114 debug('End node found'||l_num_recs,'Create_Sorted_Table');
1115 END IF;
1116 END IF;--l_num_recs>1
1117 IF g_debug=1 THEN
1118 debug('Start and End nodes found','Create_Sorted_Table');
1119 END IF;
1120
1121 -- get intermediate connecting nodes
1122 IF l_num_recs>2 THEN
1123 FOR l_index_out IN 2..(l_num_recs-1) -- first and second record already processed
1124 LOOP
1125 IF g_debug=1 THEN
1126 debug('l_index_out='||l_index_out,'Create_Sorted_Table');
1127 END IF;
1128 FOR l_index IN 1..l_num_recs
1129 LOOP
1130 IF ( p_lines_table(l_index).from_org_id=l_out_table(l_index_out-1).to_org_id)
1131 AND ( p_lines_table(l_index).from_organization_id=l_out_table(l_index_out-1).to_organization_id)
1132 THEN
1133 l_out_table(l_index_out):=p_lines_table(l_index);
1134 EXIT;
1135 END IF;
1136 END LOOP;
1137 IF l_out_table.count <> (l_index_out+1) THEN
1138 -- connecting node not found
1139 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1140 FND_MESSAGE.SET_NAME('INV','INV_INCOMPLETE_FLOW'); --ACTION SET TOKEN
1141 FND_MSG_PUB.ADD;
1142 RETURN;
1143 END IF;
1144 END LOOP;--l_index_out IN 2..l_num_recs-1
1145 END IF;
1146 --
1147 -- if only two nodes then validated connecting org/organizations
1148 IF l_num_recs=2 THEN
1149 IF ( l_out_table(1).to_org_id <> l_out_table(2).from_org_id )
1150 OR ( l_out_table(1).to_organization_id <> l_out_table(2).from_organization_id )
1151 THEN
1152 IF g_debug=1 THEN
1153 debug('Cross validation for org/organizations failed','Create_Sorted_Table');
1154 END IF;
1155 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1156 FND_MESSAGE.SET_NAME('INV','INV_INCOMPLETE_FLOW'); --ACTION SET TOKEN
1157 FND_MSG_PUB.ADD;
1158 RETURN;
1159 END IF;
1160 ELSIF l_num_recs>2 THEN -- if more than two nodes then validated connecting org/organizations for last and second last nodes
1161 IF ( l_out_table(l_out_table.count-1).to_org_id <> l_out_table(l_out_table.count).from_org_id )
1162 OR ( l_out_table(l_out_table.count-1).to_organization_id <> l_out_table(l_out_table.count).from_organization_id )
1163 THEN
1164 IF g_debug=1 THEN
1165 debug('Cross validation for org/organizations failed','Create_Sorted_Table');
1166 END IF;
1167 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1168 FND_MESSAGE.SET_NAME('INV','INV_INCOMPLETE_FLOW'); --ACTION SET TOKEN
1169 FND_MSG_PUB.ADD;
1170 RETURN;
1171 END IF;
1172 END IF;
1173
1174 X_RETURN_STATUS:=FND_API.G_RET_STS_SUCCESS;
1175 x_sorted_table:=l_out_table;
1176 IF g_debug=1 THEN
1177 debug('Sorted table created','Create_Sorted_Table');
1178 END IF;
1179 END Create_Sorted_Table;
1180
1181 /*=======================================================================================================*/
1182
1183 FUNCTION Validate_Trx_Flow_Lines(
1184 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB,
1185 P_SHIP_FROM_TO_ORGANIZATION_ID IN NUMBER,
1186 P_FLOW_TYPE IN NUMBER,
1187 P_START_ORG_ID IN NUMBER,
1188 P_END_ORG_ID IN NUMBER,
1189 P_NEW_ACCOUNTING_FLAG IN VARCHAR2
1190 ) RETURN BOOLEAN IS
1191
1192 l_from_orgs_tab TABLE_OF_NUMBERS;
1193 l_to_orgs_tab TABLE_OF_NUMBERS;
1194 l_org_ids TABLE_OF_NUMBERS;
1195 l_count NUMBER;
1196 l_count1 NUMBER;
1197 l_return_status VARCHAR2(3);
1198 from_org_name varchar2(100);
1199 to_org_name varchar2(100);
1200 l_lines_table TRX_FLOW_LINES_TAB;
1201
1202 BEGIN
1203 if g_debug=1 then
1204 debug('Starting validations for lines','Validate_Trx_Flow_Lines');
1205 end if;
1206 Create_Sorted_Table(
1207 X_RETURN_STATUS => l_return_status,
1208 X_SORTED_TABLE => l_lines_table,
1209 P_START_ORG_ID => p_start_org_id,
1210 P_END_ORG_ID => p_end_org_id,
1211 P_LINES_TABLE => p_lines_tab
1212 ) ;
1213 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1214 RETURN FALSE;
1215 END IF;
1216
1217 --See that all the organizations are not null
1218 --except from_organization_id in first line for shipping flow
1219 --and to_organization_id of last line for Procuring flow
1220
1221 IF P_FLOW_TYPE =1 then
1222 IF(l_lines_table.count>1)THEN
1223 FOR l_index IN 2..l_lines_table.count
1224 LOOP
1225 IF(l_lines_table(l_index).from_organization_id IS NULL)THEN
1226 FND_MESSAGE.SET_NAME('INV','INV_INVALID_SETUP');
1227 FND_MSG_PUB.ADD;
1228 RETURN FALSE;
1229 END IF;
1230 END LOOP;
1231 END IF;
1232
1233 FOR l_index IN 1..l_lines_table.count
1234 LOOP
1235
1236 -- For Bug 4428974
1237 -- Added condition of P_NEW_ACCOUNTING_FLAG = 'Y' in the IF statement.
1238 --
1239 IF(l_lines_table(l_index).to_organization_id IS NULL) AND P_NEW_ACCOUNTING_FLAG = 'Y' THEN
1240 FND_MESSAGE.SET_NAME('INV','INV_INVALID_SETUP');
1241 FND_MSG_PUB.ADD;
1242 RETURN FALSE;
1243 END IF;
1244 END LOOP;
1245
1246 ELSIF P_FLOW_TYPE =2 then
1247
1248
1249 if g_debug=1 then
1250 debug('1.1','Validate_Trx_Flow_Lines');
1251 end if;
1252 FOR l_index IN 1..l_lines_table.count
1253 LOOP
1254 if g_debug=1 then
1255 debug('1.1'||l_index,'Validate_Trx_Flow_Lines');
1256 end if;
1257 IF(l_lines_table(l_index).from_organization_id IS NULL)THEN
1258 FND_MESSAGE.SET_NAME('INV','INV_INVALID_SETUP');
1259 FND_MSG_PUB.ADD;
1260 RETURN FALSE;
1261 END IF;
1262 END LOOP;
1263
1264 IF(l_lines_table.count>1)THEN
1265 FOR l_index IN 1..l_lines_table.count-1
1266 LOOP
1267 IF(l_lines_table(l_index).to_organization_id IS NULL)THEN
1268 FND_MESSAGE.SET_NAME('INV','INV_INVALID_SETUP');
1269 FND_MSG_PUB.ADD;
1270 RETURN FALSE;
1271 END IF;
1272 END LOOP;
1273 END IF;
1274
1275 END IF;
1276
1277
1278 --
1279 -- validate to and from operating units
1280 if g_debug=1 then
1281 debug('VALUE OF START_ORG_ID'||p_start_org_id,'Validate_Trx_Flow_Lines');
1282 debug('VALUE OF FROM_ORG_ID'||l_lines_table(1).from_org_id,'Validate_Trx_Flow_Lines');
1283 end if;
1284 IF p_start_org_id <> l_lines_table(1).from_org_id THEN
1285 -- failure
1286 FND_MESSAGE.SET_NAME('INV','INV_INVALID_FROM_OU');
1287 FND_MSG_PUB.ADD;
1288 RETURN FALSE;
1289 END IF;
1290 IF p_end_org_id<> l_lines_table(l_lines_table.count).to_org_id THEN
1291 -- failure
1292 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TO_OU');
1293 FND_MSG_PUB.ADD;
1294 RETURN FALSE;
1295 END IF;
1296 if g_debug=1 then
1297 debug('From/To Org validated','Validate_Trx_Flow_Lines');
1298 end if;
1299 --
1300 -- validate from/to organizations
1301 IF p_flow_type=1 THEN
1302 -- from organization of first line should be equal to ship_from organization
1303 -- it can be null also
1304 IF nvl(p_ship_from_to_organization_id,-999)<>nvl(l_lines_table(1).from_organization_id,-999) THEN
1305 -- failure
1306 FND_MESSAGE.SET_NAME('INV','INV_INVALID_FROM_ORGANIZATION');
1307 FND_MSG_PUB.ADD;
1308 RETURN FALSE;
1309 END IF;
1310 ELSIF p_flow_type=2 THEN
1311 -- to organization of last line should be equal to ship_to organization
1312 -- it can be null also
1313 IF nvl(p_ship_from_to_organization_id,-999)<>nvl(l_lines_table(l_lines_table.count).to_organization_id,-999) THEN
1314 -- failure
1315 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TO_ORGANIZATION');
1316 FND_MSG_PUB.ADD;
1317 RETURN FALSE;
1318 END IF;
1319 END IF;-- flow_type
1320 if g_debug=1 then
1321 debug('From/To Organization validated','Validate_Trx_Flow_Lines');
1322 end if;
1323 --
1324 -- all from/to org/organizations should be valid
1325 FOR l_index IN 1..l_lines_table.count
1326 LOOP
1327 if g_debug=1 then
1328 debug('BEFORE VALIDATING OPERATING UNIT','Validate_Trx_Flow_Lines');
1329 end if;
1330 -- validate orgs
1331 IF NOT Validate_Operating_Unit(l_lines_table(l_index).from_org_id) THEN
1332 FND_MESSAGE.SET_NAME('INV','INV_INVALID_FROM_OU');
1333 FND_MSG_PUB.ADD;
1334 RETURN FALSE;
1335 END IF;
1336 IF NOT Validate_Operating_Unit(l_lines_table(l_index).to_org_id) THEN
1337 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TO_OU');
1338 FND_MSG_PUB.ADD;
1339 RETURN FALSE;
1340 END IF;
1341 -- validate organizations
1342 IF l_index=1 OR l_index=l_lines_table.count THEN -- first or last line organization can be null
1343 IF p_flow_type=1 THEN
1344 IF l_lines_table(l_index).from_organization_id IS NOT NULL THEN
1345 IF NOT Validate_Organization(
1346 P_ORGANIZATION_ID => l_lines_table(l_index).from_organization_id,
1347 P_ORG_ID => l_lines_table(l_index).from_org_id) THEN
1348 FND_MESSAGE.SET_NAME('INV','INV_INVALID_FROM_ORGANIZATION');
1349 FND_MSG_PUB.ADD;
1350 RETURN FALSE;
1351 END IF;
1352 END IF;
1353 ELSIF p_flow_type=2 THEN
1354 IF l_lines_table(l_index).to_organization_id IS NOT NULL THEN
1355 IF NOT Validate_Organization(
1356 P_ORGANIZATION_ID => l_lines_table(l_index).to_organization_id,
1357 P_ORG_ID => l_lines_table(l_index).to_org_id) THEN
1358 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TO_ORGANIZATION');
1359 FND_MSG_PUB.ADD;
1360 RETURN FALSE;
1361 END IF;
1362 END IF;
1363 END IF;
1364 ELSE -- l_index
1365 IF NOT Validate_Organization(
1366 P_ORGANIZATION_ID => l_lines_table(l_index).from_organization_id,
1367 P_ORG_ID => l_lines_table(l_index).from_org_id) THEN
1368 FND_MESSAGE.SET_NAME('INV','INV_INVALID_FROM_ORGANIZATION');
1369 FND_MSG_PUB.ADD;
1370 RETURN FALSE;
1371 END IF;
1372 IF NOT Validate_Organization(
1373 P_ORGANIZATION_ID => l_lines_table(l_index).to_organization_id,
1374 P_ORG_ID => l_lines_table(l_index).to_org_id) THEN
1375 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TO_ORGANIZATION');
1376 FND_MSG_PUB.ADD;
1377 RETURN FALSE;
1378 END IF;
1379 END IF;
1380 END LOOP;--l_lines_table.count
1381 if g_debug=1 then
1382 debug('All OUs and Organizations validated','Validate_Trx_Flow_Lines');
1383 end if;
1384 --
1385 -- no org should come twice in the lines tab
1386 --
1387 IF l_from_orgs_tab.count>0 THEN
1388 l_from_orgs_tab.delete;
1389 END IF;
1390 IF l_to_orgs_tab.count>0 THEN
1391 l_to_orgs_tab.delete;
1392 END IF;
1393 --
1394 FOR l_index IN 1..l_lines_table.count
1395 LOOP
1396 IF l_from_orgs_tab.exists(l_lines_table(l_index).from_org_id) THEN
1397 -- failure
1398 Begin
1399 select name into from_org_name from hr_operating_units
1400 where Organization_id=l_lines_table(l_index).from_org_id;
1401 Exception
1402 WHEN NO_DATA_FOUND THEN
1403 NULL;
1404 End;
1405 FND_MESSAGE.SET_NAME('INV','INV_DUPLICATE_OU');
1406 FND_MESSAGE.SET_TOKEN('FROM_TO','INV_FROM',TRUE);
1407 FND_MESSAGE.SET_TOKEN('OU',from_org_name,TRUE);
1408 FND_MSG_PUB.ADD;
1409 RETURN FALSE;
1410 ELSE
1411 l_from_orgs_tab(l_lines_table(l_index).from_org_id):=1;
1412 END IF;
1413 --
1414 IF l_to_orgs_tab.exists(l_lines_table(l_index).from_org_id) THEN
1415 -- failure
1416 Begin
1417 select name into to_org_name from hr_operating_units
1418 where Organization_id=l_lines_table(l_index).to_org_id;
1419 Exception
1420 WHEN NO_DATA_FOUND THEN
1421 NULL;
1422 End;
1423 FND_MESSAGE.SET_NAME('INV','INV_DUPLICATE_OU');
1424 FND_MESSAGE.SET_TOKEN('FROM_TO','INV_TO',TRUE);
1425 FND_MESSAGE.SET_TOKEN('OU',to_org_name,TRUE);
1426 FND_MSG_PUB.ADD;
1427 RETURN FALSE;
1428 ELSE
1429 l_to_orgs_tab(l_lines_table(l_index).from_org_id):=1;
1430 END IF;
1431 END LOOP;--tab_count
1432 if g_debug=1 then
1433 debug('All OUs validated for duplication','Validate_Trx_Flow_Lines');
1434 end if;
1435 --
1436 -- Validate Organizationf for Type Process/Discrete
1437 --
1438 IF l_org_ids.count>1 THEN
1439 l_org_ids.delete;
1440 END IF;
1441 -- prepare the org_ids tab
1442 IF l_lines_table(1).from_organization_id IS NOT NULL THEN
1443 l_org_ids(1):=l_lines_table(1).from_organization_id;
1444 END IF;
1445 --
1446 IF l_lines_table(l_lines_table.count).to_organization_id IS NOT NULL THEN
1447 l_org_ids(l_lines_table.count+l_org_ids.count):=l_lines_table(l_lines_table.count).to_organization_id;
1448 END IF;
1449 --
1450 FOR l_index IN 2..l_lines_table.count
1451 LOOP
1452 l_org_ids(l_org_ids.count+1):=l_lines_table(l_index).from_organization_id;
1453 END LOOP;
1454 --
1455 IF NOT Validate_Inv_Organization_Type(
1456 P_FLOW_TYPE => p_flow_type,
1457 P_ORGANIZATION_IDS => l_org_ids,
1458 P_NEW_ACCOUNTING_FLAG => p_new_accounting_flag
1459 )
1460 THEN
1461 -- Failure
1462 RETURN FALSE;
1463 END IF;
1464 if g_debug=1 then
1465 debug('All organizations validated for process/discrete type','Validate_Trx_Flow_Lines');
1466 end if;
1467 -- for each line IC Relations should be defined
1468 --
1469 FOR l_index IN 1..l_lines_table.count
1470 LOOP
1471 BEGIN
1472 if g_debug=1 then
1473 debug('The value of from_org_id is'||l_lines_table(l_index).from_org_id,'Validate_Trx_Flow_Lines');
1474 debug('The value of to_org_id is'||l_lines_table(l_index).to_org_id,'Validate_Trx_Flow_Lines');
1475 end if;
1476 SELECT 1 INTO l_count FROM MTL_INTERCOMPANY_PARAMETERS
1477 WHERE SHIP_ORGANIZATION_ID=l_lines_table(l_index).from_org_id
1478 AND SELL_ORGANIZATION_ID=l_lines_table(l_index).to_org_id
1479 AND FLOW_TYPE=p_flow_type;
1480 If l_count=1
1481 Then
1482 If(P_NEW_ACCOUNTING_FLAG ='Y')
1483 Then
1484 Begin
1485 select 1 into l_count1 from dual where exists
1486 (
1487 select ship_organization_id from mtl_intercompany_parameters
1488 where ship_organization_id=l_lines_table(l_index).from_org_id
1489 and sell_organization_id=l_lines_table(l_index).to_org_id
1490 and flow_type=p_flow_type
1491 and
1492 (
1493 intercompany_cogs_account_id is null
1494 or inventory_accrual_account_id is null
1495 or expense_accrual_account_id is null
1496 )
1497 );
1498 EXCEPTION
1499 WHEN NO_DATA_FOUND THEN
1500 NULL;
1501 END;
1502
1503 IF(l_count1=1)THEN
1504 --failure
1505 FND_MESSAGE.SET_NAME('INV','INV_NO_IC_RELATIONS');
1506 FND_MSG_PUB.ADD;
1507 RETURN FALSE;
1508 END IF;
1509 END IF;
1510 END IF;
1511
1512
1513
1514 EXCEPTION
1515 WHEN NO_DATA_FOUND THEN
1516 -- failure
1517 FND_MESSAGE.SET_NAME('INV','INV_NO_IC_RELATIONS');
1518 FND_MSG_PUB.ADD;
1519 RETURN FALSE;
1520 END;
1521 END LOOP;--ic relations
1522 if g_debug=1 then
1523 debug('IC Relations validated for all nodes','Validate_Trx_Flow_Lines');
1524 end if;
1525 --
1526 -- All validations passed
1527 RETURN TRUE;
1528 END Validate_Trx_Flow_Lines;
1529 /*=====================================================================================================*/
1530 PROCEDURE Txn_Flow_Dff ( X_RETURN_STATUS OUT NOCOPY VARCHAR2
1531 ,X_MSG_COUNT OUT NOCOPY NUMBER
1532 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1533 ,X_ENABLED_SEGS OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1534 ,P_CONTEXT IN VARCHAR2
1535 ,P_FLEX_NAME IN VARCHAR2
1536 )
1537 IS
1538 l_context_r fnd_dflex.context_r;
1539 l_contexts_dr fnd_dflex.contexts_dr;
1540 l_dflex_r fnd_dflex.dflex_r;
1541 l_segments_dr fnd_dflex.segments_dr;
1542 l_global_context BINARY_INTEGER;
1543 l_nsegments BINARY_INTEGER;
1544 l_tbl_index NUMBER :=0;
1545 BEGIN
1546 X_RETURN_STATUS := 'S';
1547
1548 if g_debug=1 then
1549 debug('In the Txn flow_dff','Txn_Flow_Dff');
1550 end if;
1551
1552 /*Prepare the DFF definition and context information */
1553
1554 l_dflex_r.application_id := 401;
1555 l_dflex_r.flexfield_name := P_FLEX_NAME;
1556
1557 l_context_r.flexfield := l_dflex_r;
1558 l_context_r.context_code := P_CONTEXT;
1559
1560 /* For a passed context, get all the enabled segments */
1561
1562 fnd_dflex.get_segments( CONTEXT => l_context_r
1563 , segments => l_segments_dr
1564 , enabled_only => TRUE
1565 );
1566
1567 /*From l_segmenst_dr get the number of segments */
1568
1569 l_nsegments := l_segments_dr.nsegments;
1570
1571 --dbms_output.put_line('The value of l_nsegments '||l_segments_dr.nsegments );
1572 if g_debug=1 then
1573 debug('Before populating the table','Txn_Flow_Dff');
1574 debug('The value of l_nsegments '||l_segments_dr.nsegments ,'Txn_Flow_Dff');
1575 end if;
1576
1577 FOR i IN 1..l_nsegments
1578 LOOP
1579 l_tbl_index := to_number(SUBSTR(l_segments_dr.application_column_name(i),INSTR(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9));
1580 X_ENABLED_SEGS(l_tbl_index).COLUMN_NAME := l_segments_dr.application_column_name(i);
1581 X_ENABLED_SEGS(l_tbl_index).COLUMN_TYPE := 'VARCHAR2';
1582 IF l_segments_dr.is_required(i) THEN
1583 X_ENABLED_SEGS(l_tbl_index).REQUIRED := 'TRUE';
1584 ELSE
1585 X_ENABLED_SEGS(l_tbl_index).REQUIRED := 'FALSE';
1586 END IF;
1587
1588 END LOOP;
1589
1590 if g_debug=1 then
1591 debug('Afetr populating the table','Txn_Flow_Dff');
1592 end if;
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 X_RETURN_STATUS := 'E';
1597 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1598
1599 END Txn_Flow_Dff;
1600
1601
1602 /*=======================================================================================================*/
1603 FUNCTION Validate_Dff(P_FLEX_NAME IN VARCHAR2,
1604 P_ATTRIBUTE1 IN VARCHAR2,
1605 P_ATTRIBUTE2 IN VARCHAR2,
1606 P_ATTRIBUTE3 IN VARCHAR2,
1607 P_ATTRIBUTE4 IN VARCHAR2,
1608 P_ATTRIBUTE5 IN VARCHAR2,
1609 P_ATTRIBUTE6 IN VARCHAR2,
1610 P_ATTRIBUTE7 IN VARCHAR2,
1611 P_ATTRIBUTE8 IN VARCHAR2,
1612 P_ATTRIBUTE9 IN VARCHAR2,
1613 P_ATTRIBUTE10 IN VARCHAR2,
1614 P_ATTRIBUTE11 IN VARCHAR2,
1615 P_ATTRIBUTE12 IN VARCHAR2,
1616 P_ATTRIBUTE13 IN VARCHAR2,
1617 P_ATTRIBUTE14 IN VARCHAR2,
1618 P_ATTRIBUTE15 IN VARCHAR2,
1619 P_ATTRIBUTE_CATEGORY IN VARCHAR2
1620 ) RETURN BOOLEAN IS
1621
1622 l_return_status varchar2(1);
1623 l_msg_data varchar2(2000);
1624 l_msg_count number;
1625 l_ENABLED_SEGS inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1626 l_contexts_dr fnd_dflex.contexts_dr;
1627 l_context VARCHAR2(1000);
1628 l_dflex_r fnd_dflex.dflex_r;
1629 l_global_context BINARY_INTEGER;
1630 l_tbl_index number :=0;
1631 l_loop_index NUMBER := 0;
1632 TYPE txn_hdr_dff is TABLE of VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1633 l_txn_hdr_dff txn_hdr_dff;
1634 l_txn_hdr_attr txn_hdr_dff;
1635
1636 USER_ERROR EXCEPTION;
1637 ERRORS_RECEIVED EXCEPTION;
1638 ERROR_SEGMENT VARCHAR2(30);
1639 error_msg VARCHAR2(5000);
1640 s NUMBER;
1641 e NUMBER;
1642
1643 L_INDEX NUMBER;
1644 L_INDEX1 NUMBER;
1645 l_check_valid_seg NUMBER:=0;
1646
1647
1648 BEGIN
1649 if g_debug=1 then
1650 debug('In validate Dff','Validate_Dff');
1651 end if;
1652 l_txn_hdr_attr(1) := p_attribute1;
1653 l_txn_hdr_attr(2) := p_attribute2;
1654 l_txn_hdr_attr(3) := p_attribute3;
1655 l_txn_hdr_attr(4) := p_attribute4;
1656 l_txn_hdr_attr(5) := p_attribute5;
1657 l_txn_hdr_attr(6) := p_attribute6;
1658 l_txn_hdr_attr(7) := p_attribute7;
1659 l_txn_hdr_attr(8) := p_attribute8;
1660 l_txn_hdr_attr(9) := p_attribute9;
1661 l_txn_hdr_attr(10) := p_attribute10;
1662 l_txn_hdr_attr(11) := p_attribute11;
1663 l_txn_hdr_attr(12) := p_attribute12;
1664 l_txn_hdr_attr(13) := p_attribute13;
1665 l_txn_hdr_attr(14) := p_attribute14;
1666 l_txn_hdr_attr(15) := p_attribute15;
1667
1668 if g_debug=1 then
1669 debug('After populating the table','Validate_Dff');
1670 end if;
1671 l_dflex_r.application_id := 401;
1672 l_dflex_r.flexfield_name := P_FLEX_NAME;
1673
1674 /* Get all contexts */
1675
1676 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
1677
1678 /* From the l_contexts_dr, get the position of the global context */
1679 l_global_context := l_contexts_dr.global_context;
1680
1681 if g_debug=1 then
1682 debug('before getting global contexts'||l_global_context,'Validate_Dff');
1683 end if;
1684 /* Using the position get the Global context*/
1685 l_context := l_contexts_dr.context_code(l_global_context);
1686
1687 if g_debug=1 then
1688 debug('after getting global context'||l_context,'Validate_Dff');
1689 end if;
1690
1691 /*For the Global context get all the enabled columns */
1692 if g_debug=1 then
1693 debug('before call to Txn flow_dff','Validate_Dff');
1694 end if;
1695 TXN_FLOW_DFF( X_RETURN_STATUS =>l_return_status
1696 ,X_MSG_COUNT =>l_msg_count
1697 ,X_MSG_DATA =>l_msg_data
1698 ,X_ENABLED_SEGS => l_ENABLED_SEGS
1699 ,P_CONTEXT => l_context
1700 ,P_FLEX_NAME => p_flex_name
1701 );
1702 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1703 RETURN FALSE;
1704 END IF;
1705
1706 if g_debug=1 then
1707 debug('after call to Txn flow_dff','Validate_Dff');
1708 end if;
1709
1710 l_loop_index := l_enabled_segs.first;
1711 while l_loop_index <= l_enabled_segs.last
1712 loop
1713 /* Check if the column is required and input column has been populated */
1714 IF l_enabled_segs(l_loop_index).required ='TRUE' and
1715 NOT l_txn_hdr_attr.exists(TO_NUMBER(substr(l_enabled_segs(l_loop_index).column_name,
1716 instr(l_enabled_segs(l_loop_index).column_name,'ATTRIBUTE')+9))) THEN
1717 FND_MESSAGE.SET_NAME('INV','INV_REQ_SEG_MISS');
1718 FND_MESSAGE.SET_TOKEN('SEGMENT',l_enabled_segs(l_loop_index).column_name,TRUE);
1719 FND_MSG_PUB.ADD;
1720 RETURN FALSE;
1721 END IF;
1722 if g_debug=1 then
1723 debug(' The column is '||l_enabled_segs(l_loop_index).column_name,'Validate_Dff');
1724 end if;
1725
1726 l_tbl_index := l_tbl_index +1;
1727 l_txn_hdr_dff(l_tbl_index) :=l_enabled_segs(l_loop_index).column_name;
1728 fnd_flex_descval.set_column_value(l_enabled_segs(l_loop_index).column_name,
1729 l_txn_hdr_attr(TO_NUMBER(substr(l_enabled_segs(l_loop_index).column_name,
1730 instr(l_enabled_segs(l_loop_index).column_name,'ATTRIBUTE')+9)))
1731 );
1732
1733 l_loop_index := l_enabled_segs.next(l_loop_index);
1734 end loop;
1735 if g_debug=1 then
1736 debug('after validating for global segs','Validate_Dff');
1737 end if;
1738 l_enabled_segs.delete;
1739
1740 /* Call the API to get the segments for the passed Attribute category */
1741
1742 l_context := p_attribute_category;
1743 fnd_flex_descval.set_context_value(l_context);
1744
1745 TXN_FLOW_DFF( X_RETURN_STATUS =>l_return_status
1746 ,X_MSG_COUNT =>l_msg_count
1747 ,X_MSG_DATA =>l_msg_data
1748 ,X_ENABLED_SEGS => l_ENABLED_SEGS
1749 ,P_CONTEXT => l_context
1750 ,P_FLEX_NAME => p_flex_name
1751 );
1752 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1753 RETURN FALSE;
1754 END IF;
1755 if g_debug=1 then
1756 debug(' after 2 call to Txn flow dff','Validate_Dff');
1757 end if;
1758 l_loop_index := 0;
1759
1760 l_loop_index := l_enabled_segs.first;
1761
1762 while l_loop_index <= l_enabled_segs.last
1763 loop
1764 IF l_enabled_segs(l_loop_index).required ='TRUE' and
1765 NOT l_txn_hdr_attr.exists(TO_NUMBER(substr(l_enabled_segs(l_loop_index).column_name,
1766 instr(l_enabled_segs(l_loop_index).column_name,'ATTRIBUTE')+9))) THEN
1767 FND_MESSAGE.SET_NAME('INV','INV_REQ_SEG_MISS');
1768 FND_MESSAGE.SET_TOKEN('SEGMENT',l_enabled_segs(l_loop_index).column_name,TRUE);
1769 FND_MSG_PUB.ADD;
1770 RETURN FALSE;
1771 END IF;
1772 if g_debug=1 then
1773 debug(' The column is for context '||l_enabled_segs(l_loop_index).column_name,'Validate_Dff');
1774 debug(' The l_tbl_index is '||l_tbl_index,'Validate_Dff');
1775 end if;
1776
1777 l_tbl_index := l_tbl_index +1;
1778 l_txn_hdr_dff(l_tbl_index) := l_enabled_segs(l_loop_index).column_name;
1779 fnd_flex_descval.set_column_value(l_enabled_segs(l_loop_index).column_name,
1780 l_txn_hdr_attr(TO_NUMBER(substr(l_enabled_segs(l_loop_index).column_name,
1781 instr(l_enabled_segs(l_loop_index).column_name,'ATTRIBUTE')+9)))
1782 );
1783 l_loop_index := l_enabled_segs.next(l_loop_index);
1784 end loop;
1785
1786 if g_debug=1 then
1787 debug('After validating for user context','Validate_Dff');
1788 end if;
1789
1790 l_index := l_txn_hdr_attr.first;
1791 WHILE l_index <= l_txn_hdr_attr.last
1792 LOOP
1793 IF l_txn_hdr_attr(l_index) is not null THEN
1794 l_index1 := l_txn_hdr_dff.first;
1795
1796 if g_debug=1 then
1797 debug('The value of l_index is '||l_index,'Validate_Dff');
1798 debug('The value of l_index1 is '||l_index1,'Validate_Dff');
1799 end if;
1800 while l_index1 <=l_txn_hdr_dff.last
1801 loop
1802 if g_debug=1 then
1803 debug('The column in enabled segment is '||l_txn_hdr_dff(l_index1),'Validate_Dff');
1804 end if;
1805
1806 IF to_number(substr(l_txn_hdr_dff(l_index1),
1807 instr(l_txn_hdr_dff(l_index1),'ATTRIBUTE')+9)) =l_index THEN
1808 l_check_valid_seg := 1;
1809 EXIT;
1810 END IF;
1811 l_index1 := l_txn_hdr_dff.next(l_index1);
1812 end loop;
1813 IF l_check_valid_seg <>1 THEN
1814 FND_MESSAGE.SET_NAME('INV','INV_WRONG_SEG_POPULATE');
1815 FND_MESSAGE.SET_TOKEN('SEGMENT',l_txn_hdr_attr(l_index),TRUE);
1816 FND_MESSAGE.SET_TOKEN('CONTEXT',P_ATTRIBUTE_CATEGORY,TRUE);
1817 FND_MSG_PUB.ADD;
1818 RETURN FALSE;
1819 END IF;
1820 end if;
1821 l_check_valid_seg := 0;
1822 l_index := l_txn_hdr_attr.next(l_index);
1823 END LOOP;
1824
1825 IF fnd_flex_descval.validate_desccols( appl_short_name => 'INV'
1826 , desc_flex_name => P_FLEX_NAME
1827 , values_or_ids => 'I'
1828 , validation_date => SYSDATE
1829 ) THEN
1830 RETURN TRUE;
1831 if g_debug=1 then
1832 debug('all validations successfull','Validate_Dff');
1833 end if;
1834 ELSE
1835 error_segment := fnd_flex_descval.error_segment;
1836 RAISE errors_received;
1837 END IF;
1838
1839 EXCEPTION
1840 WHEN errors_received THEN
1841 error_msg := fnd_flex_descval.error_message;
1842 s := 1;
1843 e := 200;
1844 WHILE e < 5001 AND SUBSTR(error_msg, s, e) IS NOT NULL LOOP
1845 FND_MESSAGE.SET_NAME('INV','INV_FND_GENERIC_MSG');
1846 FND_MESSAGE.SET_TOKEN('MSG',SUBSTR(error_msg, s, e));
1847 FND_MSG_PUB.ADD;
1848 s := s + 200;
1849 e := e + 200;
1850 END LOOP;
1851 RETURN FALSE;
1852
1853 END;
1854
1855 /*=======================================================================================================*/
1856
1857 PROCEDURE Create_IC_Transaction_Flow(
1858 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1859 X_MSG_COUNT OUT NOCOPY NUMBER,
1860 X_MSG_DATA OUT NOCOPY VARCHAR2,
1861 P_HEADER_ID IN NUMBER,
1862 P_COMMIT IN BOOLEAN DEFAULT FALSE,
1863 P_VALIDATION_LEVEL IN NUMBER,--0=>No Validation,1=>Flow Validation
1864 P_START_ORG_ID IN NUMBER,
1865 P_END_ORG_ID IN NUMBER,
1866 P_FLOW_TYPE IN NUMBER,
1867 P_ORGANIZATION_ID IN NUMBER,
1868 P_QUALIFIER_CODE IN NUMBER,
1869 P_QUALIFIER_VALUE_ID IN NUMBER,
1870 P_ASSET_ITEM_PRICING_OPTION IN NUMBER,
1871 P_EXPENSE_ITEM_PRICING_OPTION IN NUMBER,
1872 P_START_DATE IN DATE,
1873 P_END_DATE IN DATE,
1874 P_NEW_ACCOUNTING_FLAG IN VARCHAR2,
1875 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1876 P_ATTRIBUTE1 IN VARCHAR2,
1877 P_ATTRIBUTE2 IN VARCHAR2,
1878 P_ATTRIBUTE3 IN VARCHAR2,
1879 P_ATTRIBUTE4 IN VARCHAR2,
1880 P_ATTRIBUTE5 IN VARCHAR2,
1881 P_ATTRIBUTE6 IN VARCHAR2,
1882 P_ATTRIBUTE7 IN VARCHAR2,
1883 P_ATTRIBUTE8 IN VARCHAR2,
1884 P_ATTRIBUTE9 IN VARCHAR2,
1885 P_ATTRIBUTE10 IN VARCHAR2,
1886 P_ATTRIBUTE11 IN VARCHAR2,
1887 P_ATTRIBUTE12 IN VARCHAR2,
1888 P_ATTRIBUTE13 IN VARCHAR2,
1889 P_ATTRIBUTE14 IN VARCHAR2,
1890 P_ATTRIBUTE15 IN VARCHAR2,
1891 P_REF_DATE IN DATE,
1892 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB
1893 ) IS
1894 l_row_id NUMBER;
1895 l_header_id NUMBER;
1896 inv_j_installed BOOLEAN;
1897 po_j_installed BOOLEAN;
1898 costing_j_installed BOOLEAN;
1899 om_j_installed BOOLEAN;
1900
1901 BEGIN
1902 SAVEPOINT CREATE_IC_TRX_FLOW_SP;
1903 if g_debug=1 then
1904 debug('Inside Create_IC_Transaction_Flow','Create_IC_Transaction_Flow');
1905 end if;
1906 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1907
1908 --Bug 3439577 fix. Inline branching checks
1909 --for inventory
1910 If (inv_control.get_current_release_level >= INV_Release.Get_J_RELEASE_LEVEL) Then
1911 INV_J_INSTALLED :=TRUE;
1912 if g_debug=1 then
1913 debug('INV J Installed','Create_IC_Transaction_Flow');
1914 end if;
1915 Else
1916 INV_J_INSTALLED :=FALSE;
1917 if g_debug=1 then
1918 debug('INV J not Installed','Create_IC_Transaction_Flow');
1919 end if;
1920 End If;
1921
1922 --for costing
1923 If (CST_VersionCtrl_GRP.GET_CURRENT_RELEASE_LEVEL >=CST_Release_GRP.GET_J_RELEASE_LEVEL )
1924 Then
1925 COSTING_J_INSTALLED :=TRUE;
1926 if g_debug=1 then
1927 debug('CST J Installed','Create_IC_Transaction_Flow');
1928 end if;
1929 Else
1930 COSTING_J_INSTALLED :=FALSE;
1931 if g_debug=1 then
1932 debug('CST J not Installed','Create_IC_Transaction_Flow');
1933 end if;
1934 End If;
1935
1936 --for OM
1937 If (OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' )
1938 Then
1939 OM_J_INSTALLED :=TRUE;
1940 if g_debug=1 then
1941 debug('OM J Installed','Create_IC_Transaction_Flow');
1942 end if;
1943 Else
1944 OM_J_INSTALLED :=FALSE;
1945 if g_debug=1 then
1946 debug('OM J not Installed','Create_IC_Transaction_Flow');
1947 end if;
1948 End IF;
1949
1950 --for PO
1951 If (PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
1952 PO_J_INSTALLED :=TRUE;
1953 if g_debug=1 then
1954 debug('PO J Installed','Create_IC_Transaction_Flow');
1955 end if;
1956 Else
1957 PO_J_INSTALLED :=FALSE;
1958 if g_debug=1 then
1959 debug('PO J not Installed','Create_IC_Transaction_Flow');
1960 end if;
1961 End If;
1962
1963 --Bug 3439577 fix. Inline branching checks
1964
1965 -- validate the input parameters first
1966 IF p_validation_level=1 THEN
1967 if g_debug=1 then
1968 debug('Validation level is 1 = full','Create_IC_Transaction_Flow');
1969 end if;
1970
1971 -- validate operating units
1972 IF NOT Validate_Operating_Unit(p_start_org_id) THEN
1973 FND_MESSAGE.SET_NAME('INV','INV_INVALID_START_ORG');
1974 FND_MSG_PUB.ADD;
1975 RAISE FND_API.G_EXC_ERROR;
1976 END IF;
1977 --
1978 IF NOT Validate_Operating_Unit(p_end_org_id) THEN
1979 FND_MESSAGE.SET_NAME('INV','INV_INVALID_END_ORG');
1980 FND_MSG_PUB.ADD;
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983 if g_debug=1 then
1984 debug('Start/End OU validated','Create_IC_Transaction_Flow');
1985 end if;
1986 -- Validate Flow Type
1987 IF p_flow_type NOT IN (1,2) THEN
1988 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TRX_FLOW_TYPE');
1989 FND_MSG_PUB.ADD;
1990 RAISE FND_API.G_EXC_ERROR;
1991 --Bug 3439577 fix. Inline branching checks
1992 --For Procuring Flow with new accounting (we do not support old accounting for this type),
1993 --you need INV J and Costing J and PO J
1994 --For Shipping Flow with new accounting, you need INV J and Costing J and OM J.
1995 --Shipping Flow with old accounting needs to be
1996 --supported regardless of what patchset of Costing and INV is present
1997 ELSIF p_flow_type = 1 THEN --shipping
1998 IF p_new_accounting_flag IN ('Y','y')
1999 AND NOT(om_j_installed AND inv_j_installed AND costing_j_installed) THEN
2000 FND_MESSAGE.SET_NAME('INV','INV_NO_NEW_ACCT_FLOW');
2001 FND_MSG_PUB.ADD;
2002 RAISE FND_API.G_EXC_ERROR;
2003 END IF;
2004 ELSIF p_flow_type = 2 THEN --procuring
2005 IF NOT(po_j_installed AND inv_j_installed AND costing_j_installed) then
2006 FND_MESSAGE.SET_NAME('INV','INV_PROCURING_FLOW_NOT_ALLOWED');
2007 FND_MSG_PUB.ADD;
2008 RAISE FND_API.G_EXC_ERROR;
2009 END IF;
2010 END IF;
2011 -- validate for single ou setup
2012 IF p_start_org_id=p_end_org_id THEN
2013 IF p_flow_type<> 1 THEN
2014 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TRX_FLOW_TYPE');
2015 FND_MSG_PUB.ADD;
2016 RAISE FND_API.G_EXC_ERROR;
2017 END IF;
2018 END IF;
2019 if g_debug=1 then
2020 debug('Flow type validated','Create_IC_Transaction_Flow');
2021 end if;
2022 -- Validate Organization
2023 IF p_flow_type=1 AND p_organization_id IS NOT NULL THEN
2024 IF NOT Validate_Organization(p_organization_id,p_start_org_id) THEN
2025 FND_MESSAGE.SET_NAME('INV','INV_INVALID_DOC_ORGANIZATION');
2026 FND_MSG_PUB.ADD;
2027 RAISE FND_API.G_EXC_ERROR;
2028 END IF;
2029 ELSIF p_flow_type=2 AND p_organization_id IS NOT NULL THEN
2030 IF NOT Validate_Organization(p_organization_id,p_end_org_id) THEN
2031 FND_MESSAGE.SET_NAME('INV','INV_INVALID_DOC_ORGANIZATION');
2032 FND_MSG_PUB.ADD;
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035 END IF;
2036 if g_debug=1 then
2037 debug('From/To Organization validated','Create_IC_Transaction_Flow');
2038 end if;
2039 -- Validate Qualifier Code
2040 IF NOT Validate_Qualifier_Code(p_qualifier_code) THEN
2041 FND_MESSAGE.SET_NAME('INV','INV_INVALID_QUALIFIER');
2042 FND_MSG_PUB.ADD;
2043 RAISE FND_API.G_EXC_ERROR;
2044 END IF;
2045 -- Validate Qualifier Value
2046 IF NOT Validate_Qualifier_Value(p_qualifier_code,p_qualifier_value_id,p_flow_type) THEN
2047 FND_MESSAGE.SET_NAME('INV','INV_INVALID_QUALIFIER_VALUE');
2048 FND_MSG_PUB.ADD;
2049 RAISE FND_API.G_EXC_ERROR;
2050 END IF;
2051 if g_debug=1 then
2052 debug('Qualifier Code and Value validated','Create_IC_Transaction_Flow');
2053 end if;
2054 -- validate pricing options
2055 IF p_flow_type=1 THEN
2056 IF p_asset_item_pricing_option IS NOT NULL OR p_expense_item_pricing_option IS NOT NULL THEN
2057 FND_MESSAGE.SET_NAME('INV','INV_INVALID_PRICING_OPTION');
2058 FND_MSG_PUB.ADD;
2059 RAISE FND_API.G_EXC_ERROR;
2060 END IF;
2061 ELSE
2062 IF p_asset_item_pricing_option NOT IN(1,2) OR p_expense_item_pricing_option NOT IN (1,2) THEN
2063 FND_MESSAGE.SET_NAME('INV','INV_INVALID_PRICING_OPTION');
2064 FND_MSG_PUB.ADD;
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067 END IF;
2068 if g_debug=1 then
2069 debug('Asset/Expense Item pricing options validated','Create_IC_Transaction_Flow');
2070 end if;
2071 --
2072 -- set warning if gap will be created with this transaction flow
2073 --
2074 IF New_Gap_Created(
2075 P_START_ORG_ID => p_start_org_id,
2076 P_END_ORG_ID => p_end_org_id,
2077 P_FLOW_TYPE => p_flow_type,
2078 P_ORGANIZATION_ID => p_organization_id,
2079 P_QUALIFIER_CODE => p_qualifier_code,
2080 P_QUALIFIER_VALUE_ID => p_qualifier_value_id,
2081 P_START_DATE => p_start_date,
2082 P_END_DATE => p_end_date,
2083 P_REF_DATE => p_ref_date
2084 )THEN
2085 NULL; -- nothing to do message is already set in the called procedure
2086 END IF;
2087 if g_debug=1 then
2088 debug('New gap creation validated','Create_IC_Transaction_Flow');
2089 end if;
2090
2091 END IF; -- validation level=1
2092 -- do all necessary validations before inserting
2093 -- Validate New Accounting Flag
2094 IF NOT Validate_New_Accounting_Flag(
2095 P_START_ORG_ID => p_start_org_id,
2096 P_END_ORG_ID => p_end_org_id,
2097 P_FLOW_TYPE => p_flow_type,
2098 P_NEW_ACCOUNTING_FLAG => p_new_accounting_flag,
2099 P_NUM_LINES => p_lines_tab.count
2100 )
2101 THEN
2102 FND_MESSAGE.SET_NAME('INV','INV_INVALID_NEW_ACCT_FLAG');
2103 FND_MSG_PUB.ADD;
2104 RAISE FND_API.G_EXC_ERROR;
2105 END IF;
2106 if g_debug=1 then
2107 debug('New Accounting Flag validated','Create_IC_Transaction_Flow');
2108 end if;
2109 --
2110 -- Validate Header
2111 --
2112 /*IF NOT INV_TRANSACTION_FLOW_PVT.Validate_Header(
2113 P_HEADER_ID => p_header_id,
2114 P_START_ORG_ID => p_start_org_id,
2115 P_END_ORG_ID => p_end_org_id,
2116 P_FLOW_TYPE => p_flow_type,
2117 P_ORGANIZATION_ID => p_organization_id,
2118 P_QUALIFIER_CODE => p_qualifier_code,
2119 P_QUALIFIER_VALUE_ID => p_qualifier_value_id,
2120 P_START_DATE => p_start_date,
2121 P_END_DATE => p_end_date
2122 )
2123 THEN
2124 RAISE FND_API.G_EXC_ERROR;
2125 END IF;
2126 if g_debug=1 then
2127 debug('Header validated for duplicate','Create_IC_Transaction_Flow');
2128 end if;
2129 --
2130 -- Validate Start Date*/
2131 --
2132 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_Start_Date(
2133 P_HEADER_ID => p_header_id,
2134 P_START_ORG_ID => p_start_org_id,
2135 P_END_ORG_ID => p_end_org_id,
2136 P_FLOW_TYPE => p_flow_type,
2137 P_ORGANIZATION_ID => p_organization_id,
2138 P_QUALIFIER_CODE => p_qualifier_code,
2139 P_QUALIFIER_VALUE_ID => p_qualifier_value_id,
2140 P_START_DATE => p_start_date,
2141 P_REF_DATE => p_ref_date
2142 )
2143 THEN
2144 RAISE FND_API.G_EXC_ERROR;
2145 END IF;
2146 if g_debug=1 then
2147 debug('Start date validated','Create_IC_Transaction_Flow');
2148 end if;
2149 --
2150 -- Validate End Date
2151 --
2152 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_End_Date(
2153 P_HEADER_ID => p_header_id,
2154 P_START_ORG_ID => p_start_org_id,
2155 P_END_ORG_ID => p_end_org_id,
2156 P_FLOW_TYPE => p_flow_type,
2157 P_ORGANIZATION_ID => p_organization_id,
2158 P_QUALIFIER_CODE => p_qualifier_code,
2159 P_QUALIFIER_VALUE_ID => p_qualifier_value_id,
2160 P_START_DATE => p_start_date,
2161 P_END_DATE => p_end_date,
2162 P_REF_DATE => p_ref_date
2163 )
2164 THEN
2165 RAISE FND_API.G_EXC_ERROR;
2166 END IF;
2167 if g_debug=1 then
2168 debug('End date validated','Create_IC_Transaction_Flow');
2169 end if;
2170
2171 -- Before inserting header validate the lines also
2172 -- validate lines only if multi ou setup
2173 IF p_start_org_id <> p_end_org_id THEN
2174 IF NOT Validate_Trx_Flow_Lines(
2175 P_LINES_TAB => p_lines_tab,
2176 P_SHIP_FROM_TO_ORGANIZATION_ID => p_organization_id,
2177 P_FLOW_TYPE => p_flow_type,
2178 P_START_ORG_ID => p_start_org_id,
2179 P_END_ORG_ID => p_end_org_id,
2180 P_NEW_ACCOUNTING_FLAG => p_new_accounting_flag
2181 )
2182 THEN
2183 RAISE FND_API.G_EXC_ERROR;
2184 END IF;
2185 ELSE
2186 -- nodes cannot be created for single ou setups
2187 IF p_lines_tab.count>0 THEN
2188 FND_MESSAGE.SET_NAME('INV','INV_TRX_NODE_NOT_ALLOWED');
2189 FND_MSG_PUB.ADD;
2190 RAISE FND_API.G_EXC_ERROR;
2191 END IF;
2192 END IF;
2193 if g_debug=1 then
2194 debug('All lines validated','Create_IC_Transaction_Flow');
2195 end if;
2196
2197 IF P_VALIDATION_LEVEL =1 THEN
2198
2199 --
2200 --Validate the flex columns for header
2201 --
2202 IF NOT Validate_Dff( P_FLEX_NAME => 'MTL_TXN_FLOW_HEADERS_DFF',
2203 P_ATTRIBUTE1 => p_attribute1,
2204 P_ATTRIBUTE2 => p_attribute2,
2205 P_ATTRIBUTE3 => p_attribute3,
2206 P_ATTRIBUTE4 => p_attribute4,
2207 P_ATTRIBUTE5 => p_attribute5,
2208 P_ATTRIBUTE6 => p_attribute6,
2209 P_ATTRIBUTE7 => p_attribute7,
2210 P_ATTRIBUTE8 => p_attribute8,
2211 P_ATTRIBUTE9 => p_attribute9,
2212 P_ATTRIBUTE10 => p_attribute10,
2213 P_ATTRIBUTE11 => p_attribute11,
2214 P_ATTRIBUTE12 => p_attribute12,
2215 P_ATTRIBUTE13 => p_attribute13,
2216 P_ATTRIBUTE14 => p_attribute14,
2217 P_ATTRIBUTE15 => p_attribute15,
2218 P_ATTRIBUTE_CATEGORY => p_attribute_category
2219 ) THEN
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222 if g_debug=1 then
2223 debug('attribute columns for header validated','Create_IC_Transaction_Flow');
2224 end if;
2225
2226 --
2227 --Validate the flex columns for lines
2228 --
2229 FOR l_index IN 1..p_lines_tab.count
2230 LOOP
2231 IF NOT Validate_Dff( P_FLEX_NAME => 'MTL_TXN_FLOW_LINES_DFF',
2232 P_ATTRIBUTE1 => p_lines_tab(l_index).Attribute1,
2233 P_ATTRIBUTE2 => p_lines_tab(l_index).Attribute2,
2234 P_ATTRIBUTE3 => p_lines_tab(l_index).Attribute3,
2235 P_ATTRIBUTE4 => p_lines_tab(l_index).Attribute4,
2236 P_ATTRIBUTE5 => p_lines_tab(l_index).Attribute5,
2237 P_ATTRIBUTE6 => p_lines_tab(l_index).Attribute6,
2238 P_ATTRIBUTE7 => p_lines_tab(l_index).Attribute7,
2239 P_ATTRIBUTE8 => p_lines_tab(l_index).Attribute8,
2240 P_ATTRIBUTE9 => p_lines_tab(l_index).Attribute9,
2241 P_ATTRIBUTE10 => p_lines_tab(l_index).Attribute10,
2242 P_ATTRIBUTE11 => p_lines_tab(l_index).Attribute11,
2243 P_ATTRIBUTE12 => p_lines_tab(l_index).Attribute12,
2244 P_ATTRIBUTE13 => p_lines_tab(l_index).Attribute13,
2245 P_ATTRIBUTE14 => p_lines_tab(l_index).Attribute14,
2246 P_ATTRIBUTE15 => p_lines_tab(l_index).Attribute15,
2247 P_ATTRIBUTE_CATEGORY => p_lines_tab(l_index).Attribute_Category
2248 ) THEN
2249 RAISE FND_API.G_EXC_ERROR;
2250 END IF;
2251
2252 END LOOP;
2253 if g_debug=1 then
2254 debug('attribute columns for lines validated','Create_IC_Transaction_Flow');
2255 end if;
2256
2257 END IF;
2258 --
2259 -- All data is validated can be inserted to tables now
2260 --
2261 INV_TRANSACTION_FLOW_PVT.INSERT_TRX_FLOW_HEADER(
2262 P_HEADER_ID => p_header_id,
2263 P_START_ORG_ID => P_START_ORG_ID,
2264 P_END_ORG_ID => P_END_ORG_ID,
2265 P_FLOW_TYPE => P_FLOW_TYPE,
2266 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
2267 P_QUALIFIER_CODE => P_QUALIFIER_CODE,
2268 P_QUALIFIER_VALUE_ID => P_QUALIFIER_VALUE_ID,
2269 P_ASSET_ITEM_PRICING_OPTION => P_ASSET_ITEM_PRICING_OPTION,
2270 P_EXPENSE_ITEM_PRICING_OPTION => P_EXPENSE_ITEM_PRICING_OPTION,
2271 P_START_DATE => P_START_DATE,
2272 P_END_DATE => P_END_DATE,
2273 P_NEW_ACCOUNTING_FLAG => P_NEW_ACCOUNTING_FLAG,
2274 P_CREATION_DATE => SYSDATE,
2275 P_CREATED_BY => FND_GLOBAL.USER_ID,
2276 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2277 P_LAST_UPDATE_DATE => SYSDATE,
2278 P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
2279 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2280 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2281 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2282 P_ATTRIBUTE3 => P_ATTRIBUTE3,
2283 P_ATTRIBUTE4 => P_ATTRIBUTE4,
2284 P_ATTRIBUTE5 => P_ATTRIBUTE5,
2285 P_ATTRIBUTE6 => P_ATTRIBUTE6,
2286 P_ATTRIBUTE7 => P_ATTRIBUTE7,
2287 P_ATTRIBUTE8 => P_ATTRIBUTE8,
2288 P_ATTRIBUTE9 => P_ATTRIBUTE9,
2289 P_ATTRIBUTE10 => P_ATTRIBUTE10,
2290 P_ATTRIBUTE11 => P_ATTRIBUTE11,
2291 P_ATTRIBUTE12 => P_ATTRIBUTE12,
2292 P_ATTRIBUTE13 => P_ATTRIBUTE13,
2293 P_ATTRIBUTE14 => P_ATTRIBUTE14,
2294 P_ATTRIBUTE15 => P_ATTRIBUTE15
2295 );
2296 if g_debug=1 then
2297 debug('Header inserted','Create_IC_Transaction_Flow');
2298 end if;
2299 -- insert all the lines
2300 FOR l_index IN 1..p_lines_tab.count
2301 LOOP
2302 INV_TRANSACTION_FLOW_PVT.INSERT_TRX_FLOW_LINES(
2303 P_Header_Id => p_header_id,
2304 P_Line_Number => p_lines_tab(l_index).line_number,
2305 P_From_Org_Id => p_lines_tab(l_index).from_org_id,
2306 P_From_Organization_Id => p_lines_tab(l_index).from_organization_id,
2307 P_To_Org_Id => p_lines_tab(l_index).to_org_id,
2308 P_To_Organization_Id => p_lines_tab(l_index).to_organization_id,
2309 P_Last_Updated_By => FND_GLOBAL.USER_ID,
2310 P_Last_Update_Date => SYSDATE,
2311 P_Creation_Date => SYSDATE,
2312 P_Created_By => FND_GLOBAL.USER_ID,
2313 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
2314 P_Attribute_Category => p_lines_tab(l_index).Attribute_Category,
2315 P_Attribute1 => p_lines_tab(l_index).Attribute1,
2316 P_Attribute2 => p_lines_tab(l_index).Attribute2,
2317 P_Attribute3 => p_lines_tab(l_index).Attribute3,
2318 P_Attribute4 => p_lines_tab(l_index).Attribute4,
2319 P_Attribute5 => p_lines_tab(l_index).Attribute5,
2320 P_Attribute6 => p_lines_tab(l_index).Attribute6,
2321 P_Attribute7 => p_lines_tab(l_index).Attribute7,
2322 P_Attribute8 => p_lines_tab(l_index).Attribute8,
2323 P_Attribute9 => p_lines_tab(l_index).Attribute9,
2324 P_Attribute10 => p_lines_tab(l_index).Attribute10,
2325 P_Attribute11 => p_lines_tab(l_index).Attribute11,
2326 P_Attribute12 => p_lines_tab(l_index).Attribute12,
2327 P_Attribute13 => p_lines_tab(l_index).Attribute13,
2328 P_Attribute14 => p_lines_tab(l_index).Attribute14,
2329 P_Attribute15 => p_lines_tab(l_index).Attribute15
2330 );
2331 END LOOP;
2332 if g_debug=1 then
2333 debug('All lines inserted','Create_IC_Transaction_Flow');
2334 end if;
2335 -- commit the changes if required by caller
2336 IF p_commit THEN
2337 COMMIT;
2338 END IF;
2339
2340 EXCEPTION
2341 WHEN FND_API.G_EXC_ERROR THEN
2342 x_return_status:=FND_API.G_RET_STS_ERROR;
2343 ROLLBACK TO CREATE_IC_TRX_FLOW_SP;
2344 FND_MSG_PUB.COUNT_AND_GET(
2345 P_ENCODED=>'T',
2346 P_COUNT=>X_MSG_COUNT,
2347 P_DATA=>X_MSG_DATA);
2348 WHEN OTHERS THEN
2349 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2350 ROLLBACK TO CREATE_IC_TRX_FLOW_SP;
2351 FND_MSG_PUB.COUNT_AND_GET(
2352 P_ENCODED=>'T',
2353 P_COUNT=>X_MSG_COUNT,
2354 P_DATA=>X_MSG_DATA);
2355 END Create_IC_Transaction_Flow;
2356
2357 /*=======================================================================================================*/
2358
2359 PROCEDURE Update_IC_Transaction_Flow(
2360 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2361 X_MSG_COUNT OUT NOCOPY NUMBER,
2362 X_MSG_DATA OUT NOCOPY VARCHAR2,
2363 P_COMMIT IN BOOLEAN DEFAULT FALSE,
2364 P_HEADER_ID IN NUMBER,
2365 P_START_DATE IN DATE,
2366 P_END_DATE IN DATE,
2367 P_REF_DATE IN DATE,
2368 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
2369 P_ATTRIBUTE1 IN VARCHAR2,
2370 P_ATTRIBUTE2 IN VARCHAR2,
2371 P_ATTRIBUTE3 IN VARCHAR2,
2372 P_ATTRIBUTE4 IN VARCHAR2,
2373 P_ATTRIBUTE5 IN VARCHAR2,
2374 P_ATTRIBUTE6 IN VARCHAR2,
2375 P_ATTRIBUTE7 IN VARCHAR2,
2376 P_ATTRIBUTE8 IN VARCHAR2,
2377 P_ATTRIBUTE9 IN VARCHAR2,
2378 P_ATTRIBUTE10 IN VARCHAR2,
2379 P_ATTRIBUTE11 IN VARCHAR2,
2380 P_ATTRIBUTE12 IN VARCHAR2,
2381 P_ATTRIBUTE13 IN VARCHAR2,
2382 P_ATTRIBUTE14 IN VARCHAR2,
2383 P_ATTRIBUTE15 IN VARCHAR2,
2384 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB
2385 ) IS
2386
2387
2388
2389 l_start_org_id NUMBER;
2390 l_end_org_id NUMBER;
2391 l_flow_type NUMBER;
2392 l_organization_id NUMBER;
2393 l_qualifier_code NUMBER;
2394 l_qualifier_value_id NUMBER;
2395 l_start_date DATE;
2396 l_end_date DATE;
2397 l_updated_start_date DATE;
2398 l_updated_end_date DATE;
2399 l_from_org_id NUMBER;
2400 l_from_organization_id NUMBER;
2401 l_to_org_id NUMBER;
2402 l_to_organization_id NUMBER;
2403 BEGIN
2404 if g_debug=1 then
2405 debug('Starting Update_IC_Transaction_Flow','Update_IC_Transaction_Flow');
2406 end if;
2407 if g_debug=1 then
2408 debug('p_start_date='||p_start_date,'Update_IC_Transaction_Flow');
2409 debug('p_end_date='||p_end_date,'Update_IC_Transaction_Flow');
2410 debug('p_ref_date='||p_ref_date,'Update_IC_Transaction_Flow');
2411 end if;
2412 SAVEPOINT UPDATE_IC_TRX_FLOW_SP;
2413 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2414 -- accuire lock for header
2415 BEGIN
2416 SELECT START_ORG_ID,END_ORG_ID,FLOW_TYPE,ORGANIZATION_ID,
2417 QUALIFIER_CODE,QUALIFIER_VALUE_ID,START_DATE,END_DATE
2418 INTO l_start_org_id,l_end_org_id,l_flow_type,l_organization_id,
2419 l_qualifier_code,l_qualifier_value_id,l_start_date,l_end_date
2420 FROM MTL_TRANSACTION_FLOW_HEADERS
2421 WHERE HEADER_ID=P_HEADER_ID
2422 FOR UPDATE OF START_DATE,END_DATE NOWAIT;
2423 if g_debug=1 then
2424 debug('Lock accuired for header','Update_IC_Transaction_Flow');
2425 end if;
2426 EXCEPTION
2427 WHEN OTHERS THEN
2428 -- unable to accuire lock for update
2429 -- ACTION
2430 if g_debug=1 then
2431 debug('Failed to accuire lock for header','Update_IC_Transaction_Flow');
2432 end if;
2433 FND_MESSAGE.SET_NAME('INV','INV_LOCK_FAILED');
2434 FND_MSG_PUB.ADD;
2435 RAISE FND_API.G_EXC_ERROR;
2436 END;
2437
2438 -- accuire lock for lines
2439 BEGIN
2440 SELECT FROM_ORG_ID,FROM_ORGANIZATION_ID,TO_ORG_ID,TO_ORGANIZATION_ID
2441 INTO l_from_org_id,l_from_organization_id,l_to_org_id,l_to_organization_id
2442 FROM MTL_TRANSACTION_FLOW_LINES
2443 WHERE HEADER_ID=P_HEADER_ID
2444 FOR UPDATE NOWAIT;
2445 if g_debug=1 then
2446 debug('Lock accuired for lines','Update_IC_Transaction_Flow');
2447 end if;
2448 EXCEPTION
2449 WHEN OTHERS THEN
2450 -- unable to accuire lock for update
2451 -- ACTION
2452 if g_debug=1 then
2453 debug('Failed to accuire lock for lines','Update_IC_Transaction_Flow');
2454 end if;
2455 FND_MESSAGE.SET_NAME('INV','INV_LOCK_FAILED');
2456 FND_MSG_PUB.ADD;
2457 RAISE FND_API.G_EXC_ERROR;
2458 END;
2459 IF p_start_date <> l_start_date THEN
2460 -- do the validation
2461 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_Start_Date(
2462 P_HEADER_ID => p_header_id,
2463 P_START_ORG_ID => l_start_org_id,
2464 P_END_ORG_ID => l_end_org_id,
2465 P_FLOW_TYPE => l_flow_type,
2466 P_ORGANIZATION_ID => l_organization_id,
2467 P_QUALIFIER_CODE => l_qualifier_code,
2468 P_QUALIFIER_VALUE_ID => l_qualifier_value_id,
2469 P_START_DATE => p_start_date,
2470 P_REF_DATE => p_ref_date
2471 )
2472 THEN
2473 RAISE FND_API.G_EXC_ERROR;
2474 END IF;
2475 END IF;
2476
2477 IF nvl(p_end_date,sysdate) <> nvl(l_end_date,sysdate) THEN
2478 -- do the validations
2479 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_End_Date(
2480 P_HEADER_ID => p_header_id,
2481 P_START_ORG_ID => l_start_org_id,
2482 P_END_ORG_ID => l_end_org_id,
2483 P_FLOW_TYPE => l_flow_type,
2484 P_ORGANIZATION_ID => l_organization_id,
2485 P_QUALIFIER_CODE => l_qualifier_code,
2486 P_QUALIFIER_VALUE_ID => l_qualifier_value_id,
2487 P_START_DATE => p_start_date,
2488 P_END_DATE => p_end_date,
2489 P_REF_DATE => p_ref_date
2490 )
2491 THEN
2492 RAISE FND_API.G_EXC_ERROR;
2493 END IF;
2494 END IF;
2495 --
2496 --validate the attributes for the header before update
2497
2498 /*IF NOT Validate_Dff ( P_FLEX_NAME => 'MTL_TXN_FLOW_HEADERS_DFF',
2499 P_ATTRIBUTE1 => p_attribute1,
2500 P_ATTRIBUTE2 => p_attribute2,
2501 P_ATTRIBUTE3 => p_attribute3,
2502 P_ATTRIBUTE4 => p_attribute4,
2503 P_ATTRIBUTE5 => p_attribute5,
2504 P_ATTRIBUTE6 => p_attribute6,
2505 P_ATTRIBUTE7 => p_attribute7,
2506 P_ATTRIBUTE8 => p_attribute8,
2507 P_ATTRIBUTE9 => p_attribute9,
2508 P_ATTRIBUTE10 => p_attribute10,
2509 P_ATTRIBUTE11 => p_attribute11,
2510 P_ATTRIBUTE12 => p_attribute12,
2511 P_ATTRIBUTE13 => p_attribute13,
2512 P_ATTRIBUTE14 => p_attribute14,
2513 P_ATTRIBUTE15 => p_attribute15,
2514 P_ATTRIBUTE_CATEGORY => p_attribute_category
2515 ) THEN
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518 if g_debug=1 then
2519 debug('attribute columns for header validated','Update_IC_Transaction_Flow');
2520 end if;
2521
2522 --
2523 --Validate the flex columns for lines
2524 --
2525 FOR l_index IN 1..p_lines_tab.count
2526 LOOP
2527 IF NOT Validate_Dff( P_FLEX_NAME => 'MTL_TXN_FLOW_LINES_DFF',
2528 P_ATTRIBUTE1 => p_lines_tab(l_index).Attribute1,
2529 P_ATTRIBUTE2 => p_lines_tab(l_index).Attribute2,
2530 P_ATTRIBUTE3 => p_lines_tab(l_index).Attribute3,
2531 P_ATTRIBUTE4 => p_lines_tab(l_index).Attribute4,
2532 P_ATTRIBUTE5 => p_lines_tab(l_index).Attribute5,
2533 P_ATTRIBUTE6 => p_lines_tab(l_index).Attribute6,
2534 P_ATTRIBUTE7 => p_lines_tab(l_index).Attribute7,
2535 P_ATTRIBUTE8 => p_lines_tab(l_index).Attribute8,
2536 P_ATTRIBUTE9 => p_lines_tab(l_index).Attribute9,
2537 P_ATTRIBUTE10 => p_lines_tab(l_index).Attribute10,
2538 P_ATTRIBUTE11 => p_lines_tab(l_index).Attribute11,
2539 P_ATTRIBUTE12 => p_lines_tab(l_index).Attribute12,
2540 P_ATTRIBUTE13 => p_lines_tab(l_index).Attribute13,
2541 P_ATTRIBUTE14 => p_lines_tab(l_index).Attribute14,
2542 P_ATTRIBUTE15 => p_lines_tab(l_index).Attribute15,
2543 P_ATTRIBUTE_CATEGORY => p_lines_tab(l_index).Attribute_Category
2544 ) THEN
2545 RAISE FND_API.G_EXC_ERROR;
2546 END IF;
2547
2548 END LOOP;
2549 if g_debug=1 then
2550 debug('attribute columns for lines validated','Update_IC_Transaction_Flow');
2551 end if;*/
2552
2553
2554
2555 --
2556 -- All data is validated can be updated to tables now
2557 --
2558 INV_TRANSACTION_FLOW_PVT.UPDATE_TRX_FLOW_HEADER(
2559 P_HEADER_ID => p_header_id,
2560 P_START_DATE => P_START_DATE,
2561 P_END_DATE => P_END_DATE,
2562 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2563 P_LAST_UPDATE_DATE => SYSDATE,
2564 P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
2565 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2566 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2567 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2568 P_ATTRIBUTE3 => P_ATTRIBUTE3,
2569 P_ATTRIBUTE4 => P_ATTRIBUTE4,
2570 P_ATTRIBUTE5 => P_ATTRIBUTE5,
2571 P_ATTRIBUTE6 => P_ATTRIBUTE6,
2572 P_ATTRIBUTE7 => P_ATTRIBUTE7,
2573 P_ATTRIBUTE8 => P_ATTRIBUTE8,
2574 P_ATTRIBUTE9 => P_ATTRIBUTE9,
2575 P_ATTRIBUTE10 => P_ATTRIBUTE10,
2576 P_ATTRIBUTE11 => P_ATTRIBUTE11,
2577 P_ATTRIBUTE12 => P_ATTRIBUTE12,
2578 P_ATTRIBUTE13 => P_ATTRIBUTE13,
2579 P_ATTRIBUTE14 => P_ATTRIBUTE14,
2580 P_ATTRIBUTE15 => P_ATTRIBUTE15
2581 );
2582 if g_debug=1 then
2583 debug('Header updated','Update_IC_Transaction_Flow');
2584 end if;
2585 -- update all the lines
2586 FOR l_index IN 1..p_lines_tab.count
2587 LOOP
2588 INV_TRANSACTION_FLOW_PVT.UPDATE_TRX_FLOW_LINES(
2589 P_Header_Id => p_header_id,
2590 P_Line_Number => p_lines_tab(l_index).line_number,
2591 P_Last_Updated_By => FND_GLOBAL.USER_ID,
2592 P_Last_Update_Date => SYSDATE,
2593 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
2594 P_Attribute_Category => p_lines_tab(l_index).Attribute_Category,
2595 P_Attribute1 => p_lines_tab(l_index).Attribute1,
2596 P_Attribute2 => p_lines_tab(l_index).Attribute2,
2597 P_Attribute3 => p_lines_tab(l_index).Attribute3,
2598 P_Attribute4 => p_lines_tab(l_index).Attribute4,
2599 P_Attribute5 => p_lines_tab(l_index).Attribute5,
2600 P_Attribute6 => p_lines_tab(l_index).Attribute6,
2601 P_Attribute7 => p_lines_tab(l_index).Attribute7,
2602 P_Attribute8 => p_lines_tab(l_index).Attribute8,
2603 P_Attribute9 => p_lines_tab(l_index).Attribute9,
2604 P_Attribute10 => p_lines_tab(l_index).Attribute10,
2605 P_Attribute11 => p_lines_tab(l_index).Attribute11,
2606 P_Attribute12 => p_lines_tab(l_index).Attribute12,
2607 P_Attribute13 => p_lines_tab(l_index).Attribute13,
2608 P_Attribute14 => p_lines_tab(l_index).Attribute14,
2609 P_Attribute15 => p_lines_tab(l_index).Attribute15
2610 );
2611 END LOOP;
2612 if g_debug=1 then
2613 debug('All lines updated','Update_IC_Transaction_Flow');
2614 end if;
2615
2616 -- commit changes if asked
2617 IF p_commit THEN
2618 commit;
2619 END IF;
2620
2621 EXCEPTION
2622 WHEN FND_API.G_EXC_ERROR THEN
2623 x_return_status:=FND_API.G_RET_STS_ERROR;
2624 ROLLBACK TO UPDATE_IC_TRX_FLOW_SP;
2625 FND_MSG_PUB.COUNT_AND_GET(
2626 P_ENCODED=>'T',
2627 P_COUNT=>X_MSG_COUNT,
2628 P_DATA=>X_MSG_DATA);
2629 WHEN OTHERS THEN
2630 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2631 ROLLBACK TO UPDATE_IC_TRX_FLOW_SP;
2632 FND_MSG_PUB.COUNT_AND_GET(
2633 P_ENCODED=>'T',
2634 P_COUNT=>X_MSG_COUNT,
2635 P_DATA=>X_MSG_DATA);
2636 END;
2637
2638 /*=======================================================================================================*/
2639
2640 PROCEDURE update_ic_txn_flow_hdr
2641 (X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2642 X_MSG_COUNT OUT NOCOPY NUMBER,
2643 X_MSG_DATA OUT NOCOPY VARCHAR2,
2644 P_COMMIT IN BOOLEAN DEFAULT FALSE,
2645 P_HEADER_ID IN NUMBER,
2646 P_START_DATE IN DATE,
2647 P_END_DATE IN DATE,
2648 P_REF_DATE IN DATE,
2649 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
2650 P_ATTRIBUTE1 IN VARCHAR2,
2651 P_ATTRIBUTE2 IN VARCHAR2,
2652 P_ATTRIBUTE3 IN VARCHAR2,
2653 P_ATTRIBUTE4 IN VARCHAR2,
2654 P_ATTRIBUTE5 IN VARCHAR2,
2655 P_ATTRIBUTE6 IN VARCHAR2,
2656 P_ATTRIBUTE7 IN VARCHAR2,
2657 P_ATTRIBUTE8 IN VARCHAR2,
2658 P_ATTRIBUTE9 IN VARCHAR2,
2659 P_ATTRIBUTE10 IN VARCHAR2,
2660 P_ATTRIBUTE11 IN VARCHAR2,
2661 P_ATTRIBUTE12 IN VARCHAR2,
2662 P_ATTRIBUTE13 IN VARCHAR2,
2663 P_ATTRIBUTE14 IN VARCHAR2,
2664 P_ATTRIBUTE15 IN VARCHAR2
2665 ) IS
2666
2667 l_start_org_id NUMBER;
2668 l_end_org_id NUMBER;
2669 l_flow_type NUMBER;
2670 l_organization_id NUMBER;
2671 l_qualifier_code NUMBER;
2672 l_qualifier_value_id NUMBER;
2673 l_start_date DATE;
2674 l_end_date DATE;
2675 l_updated_start_date DATE;
2676 l_updated_end_date DATE;
2677
2678 BEGIN
2679 if g_debug=1 then
2680 debug('Starting Update_IC_Transaction_Flow','Update_IC_Txn_Flow_hdr');
2681 end if;
2682
2683 if g_debug=1 then
2684 debug('p_start_date='||p_start_date,'Update_IC_Txn_Flow_hdr');
2685 debug('p_end_date='||p_end_date,'Update_IC_Txn_Flow_hdr');
2686 debug('p_ref_date='||p_ref_date,'Update_IC_Txn_Flow_hdr');
2687 end if;
2688
2689 SAVEPOINT UPDATE_IC_TRX_FLOW_HDR_SP;
2690
2691 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2692
2693 -- accuire lock for header
2694 BEGIN
2695 SELECT START_ORG_ID,END_ORG_ID,FLOW_TYPE,ORGANIZATION_ID,
2696 QUALIFIER_CODE,QUALIFIER_VALUE_ID,START_DATE,END_DATE
2697 INTO l_start_org_id,l_end_org_id,l_flow_type,l_organization_id,
2698 l_qualifier_code,l_qualifier_value_id,l_start_date,l_end_date
2699 FROM MTL_TRANSACTION_FLOW_HEADERS
2700 WHERE HEADER_ID=P_HEADER_ID
2701 FOR UPDATE OF START_DATE,END_DATE NOWAIT;
2702 if g_debug=1 then
2703 debug('Lock accuired for header','Update_IC_Txn_Flow_hdr');
2704 end if;
2705 EXCEPTION
2706 WHEN OTHERS THEN
2707 -- unable to accuire lock for update
2708 -- ACTION
2709 if g_debug=1 then
2710 debug('Failed to accuire lock for header','Update_IC_Txn_Flow_hdr');
2711 end if;
2712 FND_MESSAGE.SET_NAME('INV','INV_LOCK_FAILED');
2713 FND_MSG_PUB.ADD;
2714 RAISE FND_API.G_EXC_ERROR;
2715 END;
2716
2717 IF p_start_date <> l_start_date THEN
2718 -- do the validation
2719 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_Start_Date(
2720 P_HEADER_ID => p_header_id,
2721 P_START_ORG_ID => l_start_org_id,
2722 P_END_ORG_ID => l_end_org_id,
2723 P_FLOW_TYPE => l_flow_type,
2724 P_ORGANIZATION_ID => l_organization_id,
2725 P_QUALIFIER_CODE => l_qualifier_code,
2726 P_QUALIFIER_VALUE_ID => l_qualifier_value_id,
2727 P_START_DATE => p_start_date,
2728 P_REF_DATE => p_ref_date
2729 )
2730 THEN
2731 RAISE FND_API.G_EXC_ERROR;
2732 END IF;
2733 END IF;
2734
2735 IF nvl(p_end_date,sysdate) <> nvl(l_end_date,sysdate) THEN
2736 -- do the validations
2737 IF NOT INV_TRANSACTION_FLOW_PVT.Validate_End_Date(
2738 P_HEADER_ID => p_header_id,
2739 P_START_ORG_ID => l_start_org_id,
2740 P_END_ORG_ID => l_end_org_id,
2741 P_FLOW_TYPE => l_flow_type,
2742 P_ORGANIZATION_ID => l_organization_id,
2743 P_QUALIFIER_CODE => l_qualifier_code,
2744 P_QUALIFIER_VALUE_ID => l_qualifier_value_id,
2745 P_START_DATE => p_start_date,
2746 P_END_DATE => p_end_date,
2747 P_REF_DATE => p_ref_date
2748 )
2749 THEN
2750 RAISE FND_API.G_EXC_ERROR;
2751 END IF;
2752 END IF;
2753 --
2754 --validate the attributes for the header before update
2755
2756 IF NOT Validate_Dff ( P_FLEX_NAME => 'MTL_TXN_FLOW_HEADERS_DFF',
2757 P_ATTRIBUTE1 => p_attribute1,
2758 P_ATTRIBUTE2 => p_attribute2,
2759 P_ATTRIBUTE3 => p_attribute3,
2760 P_ATTRIBUTE4 => p_attribute4,
2761 P_ATTRIBUTE5 => p_attribute5,
2762 P_ATTRIBUTE6 => p_attribute6,
2763 P_ATTRIBUTE7 => p_attribute7,
2764 P_ATTRIBUTE8 => p_attribute8,
2765 P_ATTRIBUTE9 => p_attribute9,
2766 P_ATTRIBUTE10 => p_attribute10,
2767 P_ATTRIBUTE11 => p_attribute11,
2768 P_ATTRIBUTE12 => p_attribute12,
2769 P_ATTRIBUTE13 => p_attribute13,
2770 P_ATTRIBUTE14 => p_attribute14,
2771 P_ATTRIBUTE15 => p_attribute15,
2772 P_ATTRIBUTE_CATEGORY => p_attribute_category
2773 ) THEN
2774 RAISE FND_API.G_EXC_ERROR;
2775 END IF;
2776 if g_debug=1 then
2777 debug('attribute columns for header validated','Update_IC_Txn_Flow_hdr');
2778 end if;
2779
2780 --
2781 -- All data is validated can be updated to tables now
2782 --
2783 INV_TRANSACTION_FLOW_PVT.update_trx_flow_header
2784 (P_HEADER_ID => p_header_id,
2785 P_START_DATE => P_START_DATE,
2786 P_END_DATE => P_END_DATE,
2787 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2788 P_LAST_UPDATE_DATE => SYSDATE,
2789 P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
2790 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2791 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2792 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2793 P_ATTRIBUTE3 => P_ATTRIBUTE3,
2794 P_ATTRIBUTE4 => P_ATTRIBUTE4,
2795 P_ATTRIBUTE5 => P_ATTRIBUTE5,
2796 P_ATTRIBUTE6 => P_ATTRIBUTE6,
2797 P_ATTRIBUTE7 => P_ATTRIBUTE7,
2798 P_ATTRIBUTE8 => P_ATTRIBUTE8,
2799 P_ATTRIBUTE9 => P_ATTRIBUTE9,
2800 P_ATTRIBUTE10 => P_ATTRIBUTE10,
2801 P_ATTRIBUTE11 => P_ATTRIBUTE11,
2802 P_ATTRIBUTE12 => P_ATTRIBUTE12,
2803 P_ATTRIBUTE13 => P_ATTRIBUTE13,
2804 P_ATTRIBUTE14 => P_ATTRIBUTE14,
2805 P_ATTRIBUTE15 => P_ATTRIBUTE15
2806 );
2807 if g_debug=1 then
2808 debug('Header updated','Update_IC_Txn_Flow_hdr');
2809 end if;
2810
2811 -- commit changes if asked
2812 IF p_commit THEN
2813 commit;
2814 END IF;
2815
2816 EXCEPTION
2817 WHEN FND_API.G_EXC_ERROR THEN
2818 x_return_status:=FND_API.G_RET_STS_ERROR;
2819 ROLLBACK TO UPDATE_IC_TRX_FLOW_HDR_SP;
2820 FND_MSG_PUB.COUNT_AND_GET(
2821 P_ENCODED=>'T',
2822 P_COUNT=>X_MSG_COUNT,
2823 P_DATA=>X_MSG_DATA);
2824 WHEN OTHERS THEN
2825 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2826 ROLLBACK TO UPDATE_IC_TRX_FLOW_HDR_SP;
2827 FND_MSG_PUB.COUNT_AND_GET(
2828 P_ENCODED=>'T',
2829 P_COUNT=>X_MSG_COUNT,
2830 P_DATA=>X_MSG_DATA);
2831 END;
2832
2833 /*=======================================================================================================*/
2834
2835 PROCEDURE Update_IC_Txn_Flow_line(
2836 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2837 X_MSG_COUNT OUT NOCOPY NUMBER,
2838 X_MSG_DATA OUT NOCOPY VARCHAR2,
2839 P_COMMIT IN BOOLEAN DEFAULT FALSE,
2840 P_HEADER_ID IN NUMBER,
2841 P_LINE_NUMBER IN NUMBER,
2842 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
2843 P_ATTRIBUTE1 IN VARCHAR2,
2844 P_ATTRIBUTE2 IN VARCHAR2,
2845 P_ATTRIBUTE3 IN VARCHAR2,
2846 P_ATTRIBUTE4 IN VARCHAR2,
2847 P_ATTRIBUTE5 IN VARCHAR2,
2848 P_ATTRIBUTE6 IN VARCHAR2,
2849 P_ATTRIBUTE7 IN VARCHAR2,
2850 P_ATTRIBUTE8 IN VARCHAR2,
2851 P_ATTRIBUTE9 IN VARCHAR2,
2852 P_ATTRIBUTE10 IN VARCHAR2,
2853 P_ATTRIBUTE11 IN VARCHAR2,
2854 P_ATTRIBUTE12 IN VARCHAR2,
2855 P_ATTRIBUTE13 IN VARCHAR2,
2856 P_ATTRIBUTE14 IN VARCHAR2,
2857 P_ATTRIBUTE15 IN VARCHAR2
2858 ) IS
2859 l_from_org_id NUMBER;
2860 BEGIN
2861 if g_debug=1 then
2862 debug('Starting Update_IC_Txn_Flow_line','Update_IC_Transaction_Flow');
2863 end if;
2864
2865
2866 SAVEPOINT UPDATE_IC_TRX_FLOW_LINE_SP;
2867 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2868
2869 -- accuire lock for lines
2870 BEGIN
2871 SELECT FROM_ORG_ID
2872 INTO l_from_org_id
2873 FROM MTL_TRANSACTION_FLOW_LINES
2874 WHERE HEADER_ID=p_header_id
2875 AND line_number=p_line_number
2876 FOR UPDATE NOWAIT;
2877 if g_debug=1 then
2878 debug('Lock accuired for lines','Update_IC_Txn_Flow_line');
2879 end if;
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 -- unable to accuire lock for update
2883 -- ACTION
2884 if g_debug=1 then
2885 debug('Failed to accuire lock for lines','Update_IC_Txn_Flow_line');
2886 end if;
2887 FND_MESSAGE.SET_NAME('INV','INV_LOCK_FAILED');
2888 FND_MSG_PUB.ADD;
2889 RAISE FND_API.G_EXC_ERROR;
2890 END;
2891
2892 --
2893 --Validate the flex columns for lines
2894 --
2895 IF NOT Validate_Dff( P_FLEX_NAME => 'MTL_TXN_FLOW_LINES_DFF',
2896 P_ATTRIBUTE1 => p_Attribute1,
2897 P_ATTRIBUTE2 => p_Attribute2,
2898 P_ATTRIBUTE3 => p_Attribute3,
2899 P_ATTRIBUTE4 => p_Attribute4,
2900 P_ATTRIBUTE5 => p_Attribute5,
2901 P_ATTRIBUTE6 => p_Attribute6,
2902 P_ATTRIBUTE7 => p_Attribute7,
2903 P_ATTRIBUTE8 => p_Attribute8,
2904 P_ATTRIBUTE9 => p_Attribute9,
2905 P_ATTRIBUTE10 => p_Attribute10,
2906 P_ATTRIBUTE11 => p_Attribute11,
2907 P_ATTRIBUTE12 => p_Attribute12,
2908 P_ATTRIBUTE13 => p_Attribute13,
2909 P_ATTRIBUTE14 => p_Attribute14,
2910 P_ATTRIBUTE15 => p_Attribute15,
2911 P_ATTRIBUTE_CATEGORY => p_Attribute_Category
2912 ) THEN
2913 RAISE FND_API.G_EXC_ERROR;
2914 END IF;
2915
2916 if g_debug=1 then
2917 debug('attribute columns for lines validated','Update_IC_Txn_Flow_line');
2918 end if;
2919
2920 INV_TRANSACTION_FLOW_PVT.UPDATE_TRX_FLOW_LINES(
2921 P_Header_Id => p_header_id,
2922 P_Line_Number => p_line_number,
2923 P_Last_Updated_By => FND_GLOBAL.USER_ID,
2924 P_Last_Update_Date => SYSDATE,
2925 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
2926 P_Attribute_Category => p_Attribute_Category,
2927 P_Attribute1 => p_Attribute1,
2928 P_Attribute2 => p_Attribute2,
2929 P_Attribute3 => p_Attribute3,
2930 P_Attribute4 => p_Attribute4,
2931 P_Attribute5 => p_Attribute5,
2932 P_Attribute6 => p_Attribute6,
2933 P_Attribute7 => p_Attribute7,
2934 P_Attribute8 => p_Attribute8,
2935 P_Attribute9 => p_Attribute9,
2936 P_Attribute10 => p_Attribute10,
2937 P_Attribute11 => p_Attribute11,
2938 P_Attribute12 => p_Attribute12,
2939 P_Attribute13 => p_Attribute13,
2940 P_Attribute14 => p_Attribute14,
2941 P_Attribute15 => p_Attribute15
2942 );
2943 if g_debug=1 then
2944 debug('All lines updated','Update_IC_Txn_Flow_line');
2945 end if;
2946
2947 -- commit changes if asked
2948 IF p_commit THEN
2949 commit;
2950 END IF;
2951
2952 EXCEPTION
2953 WHEN FND_API.G_EXC_ERROR THEN
2954 x_return_status:=FND_API.G_RET_STS_ERROR;
2955 ROLLBACK TO UPDATE_IC_TRX_FLOW_LINE_SP;
2956 FND_MSG_PUB.COUNT_AND_GET(
2957 P_ENCODED=>'T',
2958 P_COUNT=>X_MSG_COUNT,
2959 P_DATA=>X_MSG_DATA);
2960 WHEN OTHERS THEN
2961 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2962 ROLLBACK TO UPDATE_IC_TRX_FLOW_LINE_SP;
2963 FND_MSG_PUB.COUNT_AND_GET(
2964 P_ENCODED=>'T',
2965 P_COUNT=>X_MSG_COUNT,
2966 P_DATA=>X_MSG_DATA);
2967 END;
2968
2969 /*=======================================================================================================*/
2970
2971 -- This is a Table Handler for the header block
2972 --It will insert a row into the mtl_transaction_flow_headers
2973
2974 PROCEDURE Insert_Trx_Flow_Header (
2975 P_Header_Id IN NUMBER,
2976 P_Start_Org_Id IN NUMBER,
2977 P_End_Org_Id IN NUMBER,
2978 P_Last_Update_Date IN DATE,
2979 P_Last_Updated_By IN NUMBER,
2980 P_Creation_Date IN DATE,
2981 P_Created_By IN NUMBER,
2982 P_Last_Update_Login IN NUMBER,
2983 P_Flow_Type IN NUMBER,
2984 P_Organization_Id IN NUMBER,
2985 P_Qualifier_Code IN NUMBER,
2986 P_Qualifier_Value_Id IN NUMBER,
2987 P_Asset_Item_Pricing_Option IN NUMBER,
2988 P_Expense_Item_Pricing_Option IN NUMBER,
2989 P_Start_Date IN DATE,
2990 P_End_Date IN DATE,
2991 P_New_Accounting_Flag IN VARCHAR2,
2992 P_Attribute_Category IN VARCHAR2,
2993 P_Attribute1 IN VARCHAR2,
2994 P_Attribute2 IN VARCHAR2,
2995 P_Attribute3 IN VARCHAR2,
2996 P_Attribute4 IN VARCHAR2,
2997 P_Attribute5 IN VARCHAR2,
2998 P_Attribute6 IN VARCHAR2,
2999 P_Attribute7 IN VARCHAR2,
3000 P_Attribute8 IN VARCHAR2,
3001 P_Attribute9 IN VARCHAR2,
3002 P_Attribute10 IN VARCHAR2,
3003 P_Attribute11 IN VARCHAR2,
3004 P_Attribute12 IN VARCHAR2,
3005 P_Attribute13 IN VARCHAR2,
3006 P_Attribute14 IN VARCHAR2,
3007 P_Attribute15 IN VARCHAR2
3008 ) IS
3009 BEGIN
3010
3011 insert into mtl_transaction_flow_headers
3012 (
3013 header_id,
3014 start_org_id,
3015 end_org_id,
3016 last_update_date,
3017 last_updated_by,
3018 creation_date,
3019 created_by,
3020 last_update_login,
3021 flow_type,
3022 organization_id,
3023 qualifier_code,
3024 qualifier_value_id,
3025 asset_item_pricing_option,
3026 expense_item_pricing_option,
3027 start_date,
3028 end_date,
3029 new_accounting_flag,
3030 attribute_category,
3031 attribute1,
3032 attribute2,
3033 attribute3,
3034 attribute4,
3035 attribute5,
3036 attribute6,
3037 attribute7,
3038 attribute8,
3039 attribute9,
3040 attribute10,
3041 attribute11,
3042 attribute12,
3043 attribute13,
3044 attribute14,
3045 attribute15
3046 )
3047
3048 VALUES (
3049 P_Header_Id,
3050 P_Start_Org_Id,
3051 P_End_Org_Id,
3052 P_Last_Update_Date,
3053 P_Last_Updated_By,
3054 P_Creation_Date,
3055 P_Created_By,
3056 P_Last_Update_Login,
3057 P_Flow_Type,
3058 P_Organization_Id,
3059 P_Qualifier_Code,
3060 P_Qualifier_Value_Id,
3061 P_Asset_Item_Pricing_Option,
3062 P_Expense_Item_Pricing_Option,
3063 P_Start_Date,
3064 P_End_Date,
3065 P_New_Accounting_Flag,
3066 P_Attribute_Category,
3067 P_Attribute1,
3068 P_Attribute2,
3069 P_Attribute3,
3070 P_Attribute4,
3071 P_Attribute5,
3072 P_Attribute6,
3073 P_Attribute7,
3074 P_Attribute8,
3075 P_Attribute9,
3076 P_Attribute10,
3077 P_Attribute11,
3078 P_Attribute12,
3079 P_Attribute13,
3080 P_Attribute14,
3081 P_Attribute15
3082 );
3083
3084 END Insert_Trx_Flow_Header;
3085
3086 /*=======================================================================================================*/
3087 PROCEDURE Update_Trx_Flow_Header(
3088 P_Header_Id IN NUMBER,
3089 P_Last_Update_Date IN DATE,
3090 P_Last_Updated_By IN NUMBER,
3091 P_Last_Update_Login IN NUMBER,
3092 P_Start_Date IN DATE,
3093 P_End_Date IN DATE,
3094 P_Attribute_Category IN VARCHAR2,
3095 P_Attribute1 IN VARCHAR2,
3096 P_Attribute2 IN VARCHAR2,
3097 P_Attribute3 IN VARCHAR2,
3098 P_Attribute4 IN VARCHAR2,
3099 P_Attribute5 IN VARCHAR2,
3100 P_Attribute6 IN VARCHAR2,
3101 P_Attribute7 IN VARCHAR2,
3102 P_Attribute8 IN VARCHAR2,
3103 P_Attribute9 IN VARCHAR2,
3104 P_Attribute10 IN VARCHAR2,
3105 P_Attribute11 IN VARCHAR2,
3106 P_Attribute12 IN VARCHAR2,
3107 P_Attribute13 IN VARCHAR2,
3108 P_Attribute14 IN VARCHAR2,
3109 P_Attribute15 IN VARCHAR2
3110 ) IS
3111
3112 BEGIN
3113 if (g_debug=1) then
3114 debug('Inside UPDATE trx flow header','Update_Trx_Flow_Header');
3115 end if;
3116 Update MTL_TRANSACTION_FLOW_HEADERS
3117 SET
3118
3119 START_DATE = P_Start_Date,
3120 END_DATE = P_End_Date,
3121 LAST_UPDATED_BY = P_Last_Updated_By,
3122 LAST_UPDATE_LOGIN = P_Last_Update_Login,
3123 LAST_UPDATE_DATE = P_Last_Update_Date,
3124 ATTRIBUTE_CATEGORY = P_Attribute_Category ,
3125 ATTRIBUTE1 = P_Attribute1,
3126 ATTRIBUTE2 = P_Attribute2,
3127 ATTRIBUTE3 = P_Attribute3,
3128 ATTRIBUTE4 = P_Attribute4,
3129 ATTRIBUTE5 = P_Attribute5,
3130 ATTRIBUTE6 = P_Attribute6,
3131 ATTRIBUTE7 = P_Attribute7,
3132 ATTRIBUTE8 = P_Attribute8,
3133 ATTRIBUTE9 = P_Attribute9,
3134 ATTRIBUTE10 = P_Attribute10,
3135 ATTRIBUTE11 = P_Attribute11,
3136 ATTRIBUTE12 = P_Attribute12,
3137 ATTRIBUTE13 = P_Attribute13,
3138 ATTRIBUTE14 = P_Attribute14,
3139 ATTRIBUTE15 = P_Attribute15
3140 WHERE HEADER_ID = P_HEADER_ID;
3141
3142 END Update_Trx_Flow_Header;
3143 /*=======================================================================================================*/
3144
3145 -- This is a Table Handler for the header block
3146 --It will lock a row for update for the mtl_transaction_flow_headers
3147
3148 PROCEDURE Lock_Trx_Flow_Header (
3149 P_Header_Id IN NUMBER,
3150 P_Start_Org_Id IN NUMBER,
3151 P_End_Org_Id IN NUMBER,
3152 P_Last_Update_Date IN DATE,
3153 P_Last_Updated_By IN NUMBER,
3154 P_Creation_Date IN DATE,
3155 P_Created_By IN NUMBER,
3156 P_Last_Update_Login IN NUMBER,
3157 P_Flow_Type IN NUMBER,
3158 P_Organization_Id IN NUMBER,
3159 P_Qualifier_Code IN NUMBER,
3160 P_Qualifier_Value_Id IN NUMBER,
3161 P_Asset_Item_Pricing_Option IN NUMBER,
3162 P_Expense_Item_Pricing_Option IN NUMBER,
3163 P_Start_Date IN DATE,
3164 P_End_Date IN DATE,
3165 P_New_Accounting_Flag IN VARCHAR2,
3166 P_Attribute_Category IN VARCHAR2,
3167 P_Attribute1 IN VARCHAR2,
3168 P_Attribute2 IN VARCHAR2,
3169 P_Attribute3 IN VARCHAR2,
3170 P_Attribute4 IN VARCHAR2,
3171 P_Attribute5 IN VARCHAR2,
3172 P_Attribute6 IN VARCHAR2,
3173 P_Attribute7 IN VARCHAR2,
3174 P_Attribute8 IN VARCHAR2,
3175 P_Attribute9 IN VARCHAR2,
3176 P_Attribute10 IN VARCHAR2,
3177 P_Attribute11 IN VARCHAR2,
3178 P_Attribute12 IN VARCHAR2,
3179 P_Attribute13 IN VARCHAR2,
3180 P_Attribute14 IN VARCHAR2,
3181 P_Attribute15 IN VARCHAR2
3182 ) IS
3183
3184
3185 CURSOR C IS
3186 SELECT *
3187 FROM MTL_TRANSACTION_FLOW_HEADERS
3188 WHERE header_id=p_header_id
3189 FOR UPDATE of Header_Id NOWAIT;
3190
3191 Recinfo C%ROWTYPE;
3192
3193 BEGIN
3194 if (g_debug=1) then
3195 debug('Inside locl trx flow header','Lock_Trx_Flow_Header');
3196 end if;
3197 OPEN C;
3198 FETCH C INTO Recinfo;
3199 if (C%NOTFOUND) then
3200 CLOSE C;
3201 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
3202 FND_MSG_PUB.ADD;
3203 APP_EXCEPTION.Raise_Exception;
3204 end if;
3205 CLOSE C;
3206
3207 if (
3208 (Recinfo.header_id = P_Header_Id)
3209 AND (Recinfo.start_org_id = P_Start_Org_Id)
3210 AND (Recinfo.end_org_id = P_End_Org_Id)
3211 AND (Recinfo.flow_type = P_Flow_Type)
3212 AND ( (Recinfo.organization_id = P_Organization_Id)
3213 OR ( (Recinfo.organization_id IS NULL)
3214 AND (P_Organization_Id IS NULL)))
3215 AND ( (Recinfo.qualifier_code = P_Qualifier_Code)
3216 OR ( (Recinfo.qualifier_code IS NULL)
3217 AND (P_Qualifier_Code IS NULL)))
3218 AND ( (Recinfo.qualifier_value_id = P_Qualifier_Value_Id)
3219 OR ( (Recinfo.qualifier_value_id IS NULL)
3220 AND (P_Qualifier_Value_Id IS NULL)))
3221 AND ( (Recinfo.asset_item_pricing_option = P_Asset_Item_Pricing_Option)
3222 OR ( (Recinfo.asset_item_pricing_option IS NULL)
3223 AND (P_Asset_Item_Pricing_Option IS NULL)))
3224 AND ( (Recinfo.expense_item_pricing_option = P_Expense_Item_Pricing_Option)
3225 OR ( (Recinfo.Expense_item_pricing_option IS NULL)
3226 AND (P_Expense_Item_Pricing_Option IS NULL)))
3227 AND (Recinfo.start_date= P_Start_Date)
3228 AND ( (Recinfo.end_date = P_End_Date)
3229 OR ( (Recinfo.end_date IS NULL)
3230 AND (P_End_Date IS NULL)))
3231 AND ( (Recinfo.new_accounting_flag = P_New_Accounting_Flag)
3232 OR ( (Recinfo.new_accounting_flag IS NULL)
3233 AND (P_New_Accounting_Flag IS NULL)))
3234 AND ( (Recinfo.attribute_category = P_Attribute_Category)
3235 OR ( (Recinfo.attribute_category IS NULL)
3236 AND (P_Attribute_Category IS NULL)))
3237 AND ( (Recinfo.attribute1 = P_Attribute1)
3238 OR ( (Recinfo.attribute1 IS NULL)
3239 AND (P_Attribute1 IS NULL)))
3240 AND ( (Recinfo.attribute2 = P_Attribute2)
3241 OR ( (Recinfo.attribute2 IS NULL)
3242 AND (P_Attribute2 IS NULL)))
3243 AND ( (Recinfo.attribute3 = P_Attribute3)
3244 OR ( (Recinfo.attribute3 IS NULL)
3245 AND (P_Attribute3 IS NULL)))
3246 AND ( (Recinfo.attribute4 = P_Attribute4)
3247 OR ( (Recinfo.attribute4 IS NULL)
3248 AND (P_Attribute4 IS NULL)))
3249 AND ( (Recinfo.attribute5 = P_Attribute5)
3250 OR ( (Recinfo.attribute5 IS NULL)
3251 AND (P_Attribute5 IS NULL)))
3252 AND ( (Recinfo.attribute6 = P_Attribute6)
3253 OR ( (Recinfo.attribute6 IS NULL)
3254 AND (P_Attribute6 IS NULL)))
3255 AND ( (Recinfo.attribute7 = P_Attribute7)
3256 OR ( (Recinfo.attribute7 IS NULL)
3257 AND (P_Attribute7 IS NULL)))
3258 AND ( (Recinfo.attribute8 = P_Attribute8)
3259 OR ( (Recinfo.attribute8 IS NULL)
3260 AND (P_Attribute8 IS NULL)))
3261 AND ( (Recinfo.attribute9 = P_Attribute9)
3262 OR ( (Recinfo.attribute9 IS NULL)
3263 AND (P_Attribute9 IS NULL)))
3264 AND ( (Recinfo.attribute10 = P_Attribute10)
3265 OR ( (Recinfo.attribute10 IS NULL)
3266 AND (P_Attribute10 IS NULL)))
3267 AND ( (Recinfo.attribute11 = P_Attribute11)
3268 OR ( (Recinfo.attribute11 IS NULL)
3269 AND (P_Attribute11 IS NULL)))
3270 AND ( (Recinfo.attribute12 = P_Attribute12)
3271 OR ( (Recinfo.attribute12 IS NULL)
3272 AND (P_Attribute12 IS NULL)))
3273 AND ( (Recinfo.attribute13 = P_Attribute13)
3274 OR ( (Recinfo.attribute13 IS NULL)
3275 AND (P_Attribute13 IS NULL)))
3276 AND ( (Recinfo.attribute14 = P_Attribute14)
3277 OR ( (Recinfo.attribute14 IS NULL)
3278 AND (P_Attribute14 IS NULL)))
3279 AND ( (Recinfo.attribute15 = P_Attribute15)
3280 OR ( (Recinfo.attribute15 IS NULL)
3281 AND (P_Attribute15 IS NULL)))
3282
3283
3284 ) then
3285 return;
3286 else
3287 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3288 FND_MSG_PUB.ADD;
3289 APP_EXCEPTION.Raise_Exception;
3290 end if;
3291 END Lock_Trx_Flow_Header;
3292
3293 /*=======================================================================================================*/
3294
3295
3296
3297
3298 -- This is a Table Handler for the header block
3299 --It will insert a row into the mtl_transaction_flow_lines
3300 --Since Update is not allowed at lines block there are no table handlers
3301 --for update and lock for the lines block
3302
3303 PROCEDURE Insert_Trx_Flow_Lines (
3304 P_Header_Id IN NUMBER,
3305 P_Line_Number IN NUMBER,
3306 P_From_Org_Id IN NUMBER,
3307 P_From_Organization_Id IN NUMBER,
3308 P_To_Org_Id IN NUMBER,
3309 P_To_Organization_Id IN NUMBER,
3310 P_Last_Updated_By IN NUMBER,
3311 P_Last_Update_Date IN DATE,
3312 P_Creation_Date IN DATE,
3313 P_Created_By IN NUMBER,
3314 P_Last_Update_Login IN NUMBER,
3315 P_Attribute_Category IN VARCHAR2,
3316 P_Attribute1 IN VARCHAR2,
3317 P_Attribute2 IN VARCHAR2,
3318 P_Attribute3 IN VARCHAR2,
3319 P_Attribute4 IN VARCHAR2,
3320 P_Attribute5 IN VARCHAR2,
3321 P_Attribute6 IN VARCHAR2,
3322 P_Attribute7 IN VARCHAR2,
3323 P_Attribute8 IN VARCHAR2,
3324 P_Attribute9 IN VARCHAR2,
3325 P_Attribute10 IN VARCHAR2,
3326 P_Attribute11 IN VARCHAR2,
3327 P_Attribute12 IN VARCHAR2,
3328 P_Attribute13 IN VARCHAR2,
3329 P_Attribute14 IN VARCHAR2,
3330 P_Attribute15 IN VARCHAR2
3331 ) IS
3332
3333 BEGIN
3334
3335 insert into mtl_transaction_flow_lines
3336 (
3337 header_id,
3338 line_number,
3339 from_org_id,
3340 from_organization_id,
3341 to_org_id,
3342 to_organization_id,
3343 last_updated_by,
3344 last_update_date,
3345 creation_date,
3346 created_by,
3347 last_update_login,
3348 attribute_category,
3349 attribute1,
3350 attribute2,
3351 attribute3,
3352 attribute4,
3353 attribute5,
3354 attribute6,
3355 attribute7,
3356 attribute8,
3357 attribute9,
3358 attribute10,
3359 attribute11,
3360 attribute12,
3361 attribute13,
3362 attribute14,
3363 attribute15
3364 )
3365
3366 VALUES (
3367
3368 P_Header_Id,
3369 P_Line_Number,
3370 P_From_Org_Id,
3371 P_From_Organization_Id,
3372 P_To_Org_Id,
3373 P_To_Organization_Id,
3374 P_Last_Updated_By,
3375 P_Last_Update_Date,
3376 P_Creation_Date,
3377 P_Created_By,
3378 P_Last_Update_Login,
3379 P_Attribute_Category,
3380 P_Attribute1,
3381 P_Attribute2,
3382 P_Attribute3,
3383 P_Attribute4,
3384 P_Attribute5,
3385 P_Attribute6,
3386 P_Attribute7,
3387 P_Attribute8,
3388 P_Attribute9,
3389 P_Attribute10,
3390 P_Attribute11,
3391 P_Attribute12,
3392 P_Attribute13,
3393 P_Attribute14,
3394 P_Attribute15
3395
3396 );
3397
3398 END Insert_Trx_Flow_Lines;
3399
3400
3401 /*=======================================================================================================*/
3402
3403 PROCEDURE Update_Trx_Flow_Lines (
3404 P_Header_Id IN NUMBER,
3405 P_Line_Number IN NUMBER,
3406 P_Last_Update_Date IN DATE,
3407 P_Last_Updated_By IN NUMBER,
3408 P_Last_Update_Login IN NUMBER,
3409 P_Attribute_Category IN VARCHAR2,
3410 P_Attribute1 IN VARCHAR2,
3411 P_Attribute2 IN VARCHAR2,
3412 P_Attribute3 IN VARCHAR2,
3413 P_Attribute4 IN VARCHAR2,
3414 P_Attribute5 IN VARCHAR2,
3415 P_Attribute6 IN VARCHAR2,
3416 P_Attribute7 IN VARCHAR2,
3417 P_Attribute8 IN VARCHAR2,
3418 P_Attribute9 IN VARCHAR2,
3419 P_Attribute10 IN VARCHAR2,
3420 P_Attribute11 IN VARCHAR2,
3421 P_Attribute12 IN VARCHAR2,
3422 P_Attribute13 IN VARCHAR2,
3423 P_Attribute14 IN VARCHAR2,
3424 P_Attribute15 IN VARCHAR2
3425 ) IS
3426
3427 BEGIN
3428 if (g_debug=1) then
3429 debug('Inside UPDATE trx flow lines','Update_Trx_Flow_Lines');
3430 end if;
3431 Update MTL_TRANSACTION_FLOW_LINES
3432 SET
3433 LAST_UPDATED_BY = P_Last_Updated_By,
3434 LAST_UPDATE_LOGIN = P_Last_Update_Login,
3435 LAST_UPDATE_DATE = P_Last_Update_Date,
3436 ATTRIBUTE_CATEGORY = P_Attribute_Category ,
3437 ATTRIBUTE1 = P_Attribute1,
3438 ATTRIBUTE2 = P_Attribute2,
3439 ATTRIBUTE3 = P_Attribute3,
3440 ATTRIBUTE4 = P_Attribute4,
3441 ATTRIBUTE5 = P_Attribute5,
3442 ATTRIBUTE6 = P_Attribute6,
3443 ATTRIBUTE7 = P_Attribute7,
3444 ATTRIBUTE8 = P_Attribute8,
3445 ATTRIBUTE9 = P_Attribute9,
3446 ATTRIBUTE10 = P_Attribute10,
3447 ATTRIBUTE11 = P_Attribute11,
3448 ATTRIBUTE12 = P_Attribute12,
3449 ATTRIBUTE13 = P_Attribute13,
3450 ATTRIBUTE14 = P_Attribute14,
3451 ATTRIBUTE15 = P_Attribute15
3452 WHERE HEADER_ID = P_HEADER_ID
3453 AND LINE_NUMBER= P_LINE_NUMBER;
3454
3455 END Update_Trx_Flow_Lines;
3456 /*=======================================================================================================*/
3457 PROCEDURE Lock_Trx_Flow_Lines (
3458 P_Header_Id IN NUMBER,
3459 P_Line_Number IN NUMBER,
3460 P_From_Org_Id IN NUMBER,
3461 P_From_Organization_Id IN NUMBER,
3462 P_To_Org_Id IN NUMBER,
3463 P_To_Organization_Id IN NUMBER,
3464 P_Last_Updated_By IN NUMBER,
3465 P_Last_Update_Date IN DATE,
3466 P_Creation_Date IN DATE,
3467 P_Created_By IN NUMBER,
3468 P_Last_Update_Login IN NUMBER,
3469 P_Attribute_Category IN VARCHAR2,
3470 P_Attribute1 IN VARCHAR2,
3471 P_Attribute2 IN VARCHAR2,
3472 P_Attribute3 IN VARCHAR2,
3473 P_Attribute4 IN VARCHAR2,
3474 P_Attribute5 IN VARCHAR2,
3475 P_Attribute6 IN VARCHAR2,
3476 P_Attribute7 IN VARCHAR2,
3477 P_Attribute8 IN VARCHAR2,
3478 P_Attribute9 IN VARCHAR2,
3479 P_Attribute10 IN VARCHAR2,
3480 P_Attribute11 IN VARCHAR2,
3481 P_Attribute12 IN VARCHAR2,
3482 P_Attribute13 IN VARCHAR2,
3483 P_Attribute14 IN VARCHAR2,
3484 P_Attribute15 IN VARCHAR2
3485 ) IS
3486
3487
3488 CURSOR C IS
3489 SELECT *
3490 FROM MTL_TRANSACTION_FLOW_LINES
3491 WHERE header_id = P_Header_Id
3492 and Line_Number=P_Line_Number
3493 FOR UPDATE of Header_Id NOWAIT;
3494
3495 Recinfo C%ROWTYPE;
3496
3497 BEGIN
3498
3499 OPEN C;
3500 FETCH C INTO Recinfo;
3501 if (C%NOTFOUND) then
3502 CLOSE C;
3503 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
3504 FND_MSG_PUB.ADD;
3505 APP_EXCEPTION.Raise_Exception;
3506 end if;
3507 CLOSE C;
3508
3509 if (
3510 (Recinfo.header_id = P_Header_Id)
3511 AND (Recinfo.line_number = P_Line_Number)
3512 AND (Recinfo.from_org_id = P_from_Org_Id)
3513 AND ( (Recinfo.from_organization_id = P_From_Organization_Id)
3514 OR ( (Recinfo.from_organization_id IS NULL)
3515 AND (P_From_Organization_Id IS NULL)))
3516 AND (Recinfo.to_org_id = P_To_Org_Id)
3517 AND ( (Recinfo.to_organization_id = P_To_Organization_Id)
3518 OR ( (Recinfo.to_organization_id IS NULL)
3519 AND (P_To_Organization_Id IS NULL)))
3520 AND ( (Recinfo.attribute_category = P_Attribute_Category)
3521 OR ( (Recinfo.attribute_category IS NULL)
3522 AND (P_Attribute_Category IS NULL)))
3523 AND ( (Recinfo.attribute1 = P_Attribute1)
3524 OR ( (Recinfo.attribute1 IS NULL)
3525 AND (P_Attribute1 IS NULL)))
3526 AND ( (Recinfo.attribute2 = P_Attribute2)
3527 OR ( (Recinfo.attribute2 IS NULL)
3528 AND (P_Attribute2 IS NULL)))
3529 AND ( (Recinfo.attribute3 = P_Attribute3)
3530 OR ( (Recinfo.attribute3 IS NULL)
3531 AND (P_Attribute3 IS NULL)))
3532 AND ( (Recinfo.attribute4 = P_Attribute4)
3533 OR ( (Recinfo.attribute4 IS NULL)
3534 AND (P_Attribute4 IS NULL)))
3535 AND ( (Recinfo.attribute5 = P_Attribute5)
3536 OR ( (Recinfo.attribute5 IS NULL)
3537 AND (P_Attribute5 IS NULL)))
3538 AND ( (Recinfo.attribute6 = P_Attribute6)
3539 OR ( (Recinfo.attribute6 IS NULL)
3540 AND (P_Attribute6 IS NULL)))
3541 AND ( (Recinfo.attribute7 = P_Attribute7)
3542 OR ( (Recinfo.attribute7 IS NULL)
3543 AND (P_Attribute7 IS NULL)))
3544 AND ( (Recinfo.attribute8 = P_Attribute8)
3545 OR ( (Recinfo.attribute8 IS NULL)
3546 AND (P_Attribute8 IS NULL)))
3547 AND ( (Recinfo.attribute9 = P_Attribute9)
3548 OR ( (Recinfo.attribute9 IS NULL)
3549 AND (P_Attribute9 IS NULL)))
3550 AND ( (Recinfo.attribute10 = P_Attribute10)
3551 OR ( (Recinfo.attribute10 IS NULL)
3552 AND (P_Attribute10 IS NULL)))
3553 AND ( (Recinfo.attribute11 = P_Attribute11)
3554 OR ( (Recinfo.attribute11 IS NULL)
3555 AND (P_Attribute11 IS NULL)))
3556 AND ( (Recinfo.attribute12 = P_Attribute12)
3557 OR ( (Recinfo.attribute12 IS NULL)
3558 AND (P_Attribute12 IS NULL)))
3559 AND ( (Recinfo.attribute13 = P_Attribute13)
3560 OR ( (Recinfo.attribute13 IS NULL)
3561 AND (P_Attribute13 IS NULL)))
3562 AND ( (Recinfo.attribute14 = P_Attribute14)
3563 OR ( (Recinfo.attribute14 IS NULL)
3564 AND (P_Attribute14 IS NULL)))
3565 AND ( (Recinfo.attribute15 = P_Attribute15)
3566 OR ( (Recinfo.attribute15 IS NULL)
3567 AND (P_Attribute15 IS NULL)))
3568
3569
3570 ) then
3571 return;
3572 else
3573 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3574 FND_MSG_PUB.ADD;
3575 APP_EXCEPTION.Raise_Exception;
3576 end if;
3577 END Lock_Trx_Flow_Lines;
3578
3579 /*=======================================================================================================*/
3580
3581
3582 END;-- INV_TRANSACTION_FLOW_PVT
3583
[Home] [Help]